/**
|
* EC_SeckillBusinessDAL.cs
|
*
|
* 功 能： 特价业务数据访问实现
|
* 类 名： EC_SeckillBusinessDAL
|
*
|
* Ver 变更日期 负责人 变更内容
|
* ───────────────────────────────────
|
* V0.01 2013-4-3 16:03 吴辉 初版
|
* V0.02 2013-4-24 9:51 吴崎均 变更支付方法实现
|
* V0.03 2013-4-25 13:45 吴崎均 增加归并缓冲金额查看账户信息方法
|
|
*
|
*
|
*
|
*
|
*
|
*/
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Model;
|
using CY.IBaseDAL;
|
using System.Transactions;
|
using CY.Infrastructure.Common;
|
using CY.Model.Pay;
|
|
namespace CY.SQLDAL
|
{
|
public class Pay_PaymentAccountDAL : IPay_PaymentAccountDAL
|
{
|
|
private Database _dataBase = null;
|
|
public Pay_PaymentAccountDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
public Pay_PaymentAccountDAL(Database database)
|
{
|
_dataBase = database;
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter(){Direction=ParameterDirection.ReturnValue,SqlDbType = SqlDbType.Int},
|
new SqlParameter("@MemberId",trueModel.MemberId),
|
new SqlParameter("@LevelRule",trueModel.LevelRule),
|
new SqlParameter("@PayPwd",trueModel.PayPwd),
|
new SqlParameter("@UseStatus",trueModel.UseStatus),
|
new SqlParameter("@PayLevel",trueModel.PayLevel),
|
new SqlParameter("@PayMoney",trueModel.PayMoney),
|
new SqlParameter("@CreditLine",trueModel.CreditLine),
|
new SqlParameter("@SurplusCredit",trueModel.SurplusCredit),
|
new SqlParameter("@SurplusMoney",trueModel.SurplusMoney),
|
new SqlParameter("@LockMoney",trueModel.LockMoney),
|
new SqlParameter("@LockScore",trueModel.LockScore),
|
new SqlParameter("@LockCredit",trueModel.LockCredit),
|
new SqlParameter("@Score",trueModel.Score),
|
new SqlParameter("@OrderCommissionMoney",trueModel.OrderCommissionMoney),
|
new SqlParameter("@PromotionMoney",trueModel.PromotionMoney),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark) ,
|
new SqlParameter("@ProxyMoney",trueModel.ProxyMoney) ,
|
new SqlParameter("@ProxyMoneyCash",trueModel.ProxyMoneyCash) ,
|
new SqlParameter("@ProxyMoneyAudit",trueModel.ProxyMoneyAudit)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_PaymentAccount_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
trueModel.Keyid = (int)sqlParms[0].Value;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@MemberId",trueModel.MemberId),
|
new SqlParameter("@LevelRule",trueModel.LevelRule),
|
new SqlParameter("@PayPwd",trueModel.PayPwd),
|
new SqlParameter("@UseStatus",trueModel.UseStatus),
|
new SqlParameter("@PayLevel",trueModel.PayLevel),
|
new SqlParameter("@PayMoney",trueModel.PayMoney),
|
new SqlParameter("@CreditLine",trueModel.CreditLine),
|
new SqlParameter("@SurplusCredit",trueModel.SurplusCredit),
|
new SqlParameter("@SurplusMoney",trueModel.SurplusMoney),
|
new SqlParameter("@LockMoney",trueModel.LockMoney),
|
new SqlParameter("@LockScore",trueModel.LockScore),
|
new SqlParameter("@LockCredit",trueModel.LockCredit),
|
new SqlParameter("@Score",trueModel.Score),
|
new SqlParameter("@OrderCommissionMoney",trueModel.OrderCommissionMoney),
|
new SqlParameter("@PromotionMoney",trueModel.PromotionMoney),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark) ,
|
new SqlParameter("@ProxyMoney",trueModel.ProxyMoney) ,
|
new SqlParameter("@ProxyMoneyCash",trueModel.ProxyMoneyCash) ,
|
new SqlParameter("@ProxyMoneyAudit",trueModel.ProxyMoneyAudit)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_PaymentAccount_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_PaymentAccount_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_PaymentAccount> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_PaymentAccount> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 根据编号获取实体信息
|
/// </summary>
|
/// <param name="MemberId">会员编号</param>
|
/// <returns></returns>
|
public Pay_PaymentAccount SelectModleByMemberId(Guid MemberId)
|
{
|
if (MemberId == null)
|
return null;//错误数据返会空
|
|
IList<Pay_PaymentAccount> result = _dataBase.SelectModel<Pay_PaymentAccount>("*", "Pay_PaymentAccount", " MemberId = '" + MemberId + "'") as IList<Pay_PaymentAccount>;//执行查询
|
return (null == result || result.Count == 0) ? null : result[0];//返回结果
|
}
|
|
/// <summary>
|
/// 支付公共接口
|
/// </summary>
|
/// <param name="m_Pay_Request">支付申请</param>
|
/// <returns></returns>
|
public bool Pay(CY.Model.Pay.Pay_Request m_Pay_Request)
|
{
|
|
if (m_Pay_Request == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@payerid",m_Pay_Request.Payerid),
|
new SqlParameter("@payeeid",m_Pay_Request.Payeeid),
|
new SqlParameter("@paymoney",m_Pay_Request.Paymoney),
|
new SqlParameter("@subject",m_Pay_Request.Subject){SqlDbType=SqlDbType.VarChar,Size=200},
|
new SqlParameter("@balanceAccount",m_Pay_Request.BalanceAccount){SqlDbType=SqlDbType.Int,Size=4},
|
new SqlParameter("@returnState", m_Pay_Request.State){SqlDbType=System.Data.SqlDbType.Int, Direction=ParameterDirection.Output},
|
new SqlParameter(){ParameterName="@usedCredit",Direction=ParameterDirection.InputOutput,SqlDbType=SqlDbType.Money,Size=8}
|
};
|
try
|
{
|
if (m_Pay_Request.UsedCredit.HasValue)
|
{
|
sqlParms[6].Value = (m_Pay_Request.UsedCredit ?? (-1).ToDecimal2()).Value;
|
}
|
_dataBase.Query("sp_Pay", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
m_Pay_Request.State = MyConvert.ConvertToInt32(sqlParms[5].Value);
|
m_Pay_Request.UsedCredit = MyConvert.ConvertToDecimal(sqlParms[6].Value);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return m_Pay_Request.State.HasValue && 0 < m_Pay_Request.State.Value;
|
}
|
|
/// <summary>
|
/// 归并用户缓冲金额并查看账户信息
|
/// </summary>
|
/// <param name="menberId">会员编号</param>
|
/// <returns></returns>
|
public Pay_PaymentAccount MergeBufferMoneyByMemberId(Guid menberId)
|
{
|
using (IDataReader reader = _dataBase.QueryDataReader("sp_Pay_MergeMoney", CommandType.StoredProcedure,
|
new SqlParameter()
|
{
|
ParameterName = "@memberId",
|
Value = menberId,
|
SqlDbType = SqlDbType.UniqueIdentifier
|
}))
|
{
|
List<Pay_PaymentAccount> result = _dataBase.ReadDataToModel<Pay_PaymentAccount>(reader);
|
return null == result || result.Count < 1 ? null : result[0];
|
}
|
}
|
|
/// <summary>
|
/// 充值
|
/// </summary>
|
/// <param name="m_Pay_PaymentAccount"></param>
|
/// <param name="m_Pay_Request"></param>
|
/// <returns></returns>
|
public bool RechargeSucces(Pay_PaymentAccount m_Pay_PaymentAccount, CY.Model.Pay.Pay_Request m_Pay_Request, Pay_AccountedRecord m_Pay_AccountedRecord)
|
{
|
try
|
{
|
Pay_AccountedRecordDAL dal_Pay_AccountedRecordDAL = new Pay_AccountedRecordDAL(_dataBase);
|
bool result = true;
|
using (TransactionScope t_TransactionScope = new TransactionScope())
|
{
|
if (result)
|
{
|
result = this.UpdateModel(m_Pay_PaymentAccount);
|
if (result)
|
{
|
result = this.Pay(m_Pay_Request);
|
if (result)
|
{
|
result = dal_Pay_AccountedRecordDAL.UpdateModel(m_Pay_AccountedRecord);
|
if (result)
|
{
|
t_TransactionScope.Complete();
|
}
|
}
|
}
|
}
|
}
|
|
if (result)
|
{
|
m_Pay_PaymentAccount = MergeBufferMoneyByMemberId(m_Pay_PaymentAccount.MemberId);
|
SendPoOrMoToMember(m_Pay_PaymentAccount.MemberId, 86, m_Pay_AccountedRecord.AccountingMoney);
|
}
|
if (result)
|
{
|
decimal? CrditMoney = m_Pay_PaymentAccount.CreditLine - m_Pay_PaymentAccount.SurplusCredit;
|
decimal? TradMoney = m_Pay_PaymentAccount.SurplusMoney - CrditMoney;
|
if (TradMoney >= 0)
|
{
|
TradMoney = CrditMoney;
|
}
|
else
|
{
|
TradMoney = m_Pay_PaymentAccount.SurplusMoney;
|
}
|
if (TradMoney > 0)
|
{
|
CY.Model.Pay.Pay_Request m_Pay_Request_critdit = new Model.Pay.Pay_Request();
|
m_Pay_Request_critdit.Payeeid = m_Pay_Request.Payerid;
|
m_Pay_Request_critdit.Payerid = m_Pay_Request.Payeeid;
|
m_Pay_Request_critdit.Paymoney = TradMoney ?? 0;
|
m_Pay_Request_critdit.Subject = "授信返款";
|
m_Pay_Request_critdit.BalanceAccount = 14;
|
m_Pay_Request_critdit.UsedCredit = -1;
|
this.Pay(m_Pay_Request_critdit);
|
}
|
}
|
if (result)
|
{
|
decimal? OrderMoney = m_Pay_PaymentAccount.OrderCommissionMoney;
|
decimal? TradMoney = m_Pay_PaymentAccount.SurplusMoney - OrderMoney;
|
if (TradMoney >= 0)
|
{
|
TradMoney = OrderMoney;
|
|
if (TradMoney > 0)
|
{
|
CY.Model.Pay.Pay_Request m_Pay_Request_order = new Model.Pay.Pay_Request();
|
m_Pay_Request_order.Payeeid = m_Pay_Request.Payerid;
|
m_Pay_Request_order.Payerid = m_Pay_Request.Payeeid;
|
m_Pay_Request_order.Paymoney = TradMoney ?? 0;
|
m_Pay_Request_order.Subject = "支付拖欠网站接单佣金";
|
m_Pay_Request_order.BalanceAccount = 10;
|
m_Pay_Request_order.UsedCredit = -1;
|
this.Pay(m_Pay_Request_order);
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@payerid",m_Pay_Request_order.Payerid)
|
};
|
_dataBase.Query("sp_Order_Recharge", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
}
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 线下充值
|
/// </summary>
|
/// <param name="m_Pay_PaymentAccount"></param>
|
/// <param name="m_Pay_Request"></param>
|
/// <returns></returns>
|
public bool RechargeSuccesUnLine(Pay_PaymentAccount m_Pay_PaymentAccount, CY.Model.Pay.Pay_Request m_Pay_Request)
|
{
|
try
|
{
|
Pay_AccountedRecordDAL dal_Pay_AccountedRecordDAL = new Pay_AccountedRecordDAL(_dataBase);
|
bool result = true;
|
using (TransactionScope t_TransactionScope = new TransactionScope())
|
{
|
if (result)
|
{
|
result = this.UpdateModel(m_Pay_PaymentAccount);
|
if (result)
|
{
|
result = this.Pay(m_Pay_Request);
|
if (result)
|
{
|
t_TransactionScope.Complete();
|
}
|
}
|
}
|
}
|
|
if (result)
|
{
|
m_Pay_PaymentAccount = MergeBufferMoneyByMemberId(m_Pay_PaymentAccount.MemberId);
|
SendPoOrMoToMember(m_Pay_PaymentAccount.MemberId, 86, m_Pay_Request.Paymoney);
|
}
|
if (result)
|
{
|
decimal? CrditMoney = m_Pay_PaymentAccount.CreditLine - m_Pay_PaymentAccount.SurplusCredit;
|
decimal? TradMoney = m_Pay_PaymentAccount.SurplusMoney - CrditMoney;
|
if (TradMoney >= 0)
|
{
|
TradMoney = CrditMoney;
|
}
|
else
|
{
|
TradMoney = m_Pay_PaymentAccount.SurplusMoney;
|
}
|
if (TradMoney > 0)
|
{
|
CY.Model.Pay.Pay_Request m_Pay_Request_critdit = new Model.Pay.Pay_Request();
|
m_Pay_Request_critdit.Payeeid = m_Pay_Request.Payerid;
|
m_Pay_Request_critdit.Payerid = m_Pay_Request.Payeeid;
|
m_Pay_Request_critdit.Paymoney = TradMoney ?? 0;
|
m_Pay_Request_critdit.Subject = "授信还款";
|
m_Pay_Request_critdit.BalanceAccount = 14;
|
m_Pay_Request_critdit.UsedCredit = -1;
|
this.Pay(m_Pay_Request_critdit);
|
}
|
}
|
if (result)
|
{
|
decimal? OrderMoney = m_Pay_PaymentAccount.OrderCommissionMoney;
|
decimal? TradMoney = m_Pay_PaymentAccount.SurplusMoney - OrderMoney;
|
if (TradMoney >= 0)
|
{
|
TradMoney = OrderMoney;
|
|
if (TradMoney > 0)
|
{
|
CY.Model.Pay.Pay_Request m_Pay_Request_order = new Model.Pay.Pay_Request();
|
m_Pay_Request_order.Payeeid = m_Pay_Request.Payerid;
|
m_Pay_Request_order.Payerid = m_Pay_Request.Payeeid;
|
m_Pay_Request_order.Paymoney = TradMoney ?? 0;
|
m_Pay_Request_order.Subject = "支付拖欠网站接单佣金";
|
m_Pay_Request_order.BalanceAccount = 10;
|
m_Pay_Request_order.UsedCredit = -1;
|
this.Pay(m_Pay_Request_order);
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@payerid",m_Pay_Request_order.Payerid)
|
};
|
_dataBase.Query("sp_Order_Recharge", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
}
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 提现转账
|
/// </summary>
|
/// <param name="m_Pay_PaymentAccount"></param>
|
/// <param name="m_Pay_Request"></param>
|
/// <returns></returns>
|
public bool CashSucces(Pay_PaymentAccount m_Pay_PaymentAccount, CY.Model.Pay.Pay_Request m_Pay_Request, Pay_PromotionRecord m_Pay_PromotionRecord)
|
{
|
try
|
{
|
Pay_PromotionRecordDAL dal_Pay_PromotionRecordDAL = new Pay_PromotionRecordDAL(_dataBase);
|
bool result = true;
|
using (TransactionScope t_TransactionScope = new TransactionScope())
|
{
|
if (result)
|
{
|
result = this.Pay(m_Pay_Request);
|
if (result)
|
{
|
result = dal_Pay_PromotionRecordDAL.InserModel(m_Pay_PromotionRecord);
|
if (result)
|
{
|
result = this.UpdateModel(m_Pay_PaymentAccount);
|
if (result)
|
{
|
t_TransactionScope.Complete();
|
}
|
}
|
}
|
}
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 给会员增加积分或现金(推广信息)
|
/// <para>36 买家会员注册费</para>
|
/// <para>393 个人网店注册费</para>
|
/// <para>89 印刷厂商注册费</para>
|
/// <para>391 登录</para>
|
/// <para>38 发布印刷需求</para>
|
/// <para>292 发布需求信息</para>
|
/// <para>86 账户充值</para>
|
/// <para>87 印刷订单</para>
|
/// <para>88 纸张订单</para>
|
/// <para>90 杂志广告费</para>
|
/// <para>392 网站广告费</para>
|
/// <para>91 管理软件费</para>
|
/// <para>92 办公家具订单</para>
|
///<para> 93 办公电脑设备订单</para>
|
/// <para>94 办公文具订单</para>
|
/// <para>95 其他商城订单</para>
|
/// <para>96 发布求职简历</para>
|
/// <para>290 发布秒杀业务</para>
|
/// <para>291 发布特价信息</para>
|
/// </summary>
|
/// <param name="Member_id">使用人会员编号</param>
|
/// <param name="SourceTypeId">推广来源类型编号</param>
|
/// <param name="Money"></param>
|
/// <returns></returns>
|
public bool SendPoOrMoToMember(Guid Member_id, int? SourceTypeId, decimal? Money)
|
{
|
try
|
{
|
EC_MemberExtendDAL dal_EC_MemberExtendDAL = new EC_MemberExtendDAL(_dataBase);
|
Pay_PaymentAccountDAL dal_Pay_PaymentAccountDAL = new Pay_PaymentAccountDAL(_dataBase);
|
Promotion_IncomeRuleDAL dal_Promotion_IncomeRuleDAL = new Promotion_IncomeRuleDAL(_dataBase);
|
Pay_PromotionRecordDAL dal_Pay_PromotionRecordDAL = new Pay_PromotionRecordDAL(_dataBase);
|
Pay_ScoreRecordDAL dal_Pay_ScoreRecordDAL = new Pay_ScoreRecordDAL(_dataBase);
|
Sys_DictionaryDAL dal_Sys_DictionaryDAL = new Sys_DictionaryDAL(_dataBase);
|
EC_ProxyMemberDAL dal_EC_ProxyMemberDAL = new EC_ProxyMemberDAL(_dataBase);
|
|
//获取积分配置信息
|
Model.Promotion_IncomeRule m_Promotion_IncomeRule = dal_Promotion_IncomeRuleDAL.GetModelBySourceTypeId(SourceTypeId);
|
if (m_Promotion_IncomeRule == null || m_Promotion_IncomeRule.Keyid == null)
|
return false; //如果不存在就跳出
|
|
//为了方便计算，无倍数即声明金额为1
|
if (m_Promotion_IncomeRule.IsRatio)
|
Money = Money.ToDecimal2();
|
else
|
Money = 1;
|
DateTime nowTime = DateTime.Now;
|
|
#region 使用人
|
|
//获取使用人
|
Model.EC_MemberExtend m_member = dal_EC_MemberExtendDAL.GetModelByMemberId(Member_id);
|
Model.Pay_PaymentAccount m_Member_Account = new Pay_PaymentAccount();
|
Pay_PromotionRecord m_Pay_PromotionRecord_money = new Pay_PromotionRecord();
|
Pay_PromotionRecord m_Pay_PromotionRecord_score = new Pay_PromotionRecord();
|
Pay_ScoreRecord m_Pay_ScoreRecord = new Pay_ScoreRecord();
|
decimal? m_PromotionMoney = 0;
|
int? m_Score = 0;
|
if (m_member != null)
|
{
|
m_Member_Account = dal_Pay_PaymentAccountDAL.SelectModleByMemberId(Member_id);
|
if (m_Member_Account != null && m_Member_Account.Keyid > 0)
|
{
|
//给使用人增加积分和金额
|
m_Score = (m_Promotion_IncomeRule.PopularizeScore * Money).ToInt32();
|
m_PromotionMoney = (m_Promotion_IncomeRule.PopularizeNoteMoney * Money).ToDecimal2();
|
|
//如果金额大于0 则增加金额记录
|
if ((m_Promotion_IncomeRule.PopularizeNoteMoney * Money).ToDecimal2() > 0)
|
{
|
m_Pay_PromotionRecord_money.PayId = m_Member_Account.Keyid;
|
m_Pay_PromotionRecord_money.Remark = m_Member_Account.MemberId.ToString2();
|
m_Pay_PromotionRecord_money.ResidualMoney = m_Member_Account.PromotionMoney + m_PromotionMoney;
|
m_Pay_PromotionRecord_money.TradingMoney = m_PromotionMoney;
|
m_Pay_PromotionRecord_money.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
switch (m_Promotion_IncomeRule.SourceType.Name)
|
{
|
case "注册":
|
m_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(注册奖励)";
|
break;
|
case "登录":
|
m_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
case "发布印刷需求":
|
m_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
case "发布求职简历":
|
m_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
default:
|
m_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name;
|
break;
|
}
|
|
m_Pay_PromotionRecord_money.TradingState = -1;//已完成
|
m_Pay_PromotionRecord_money.TradingType = 1;//收入
|
m_Pay_PromotionRecord_money.CreateTime = nowTime;
|
m_Pay_PromotionRecord_money.IsProxy = false;
|
m_Pay_PromotionRecord_money.SendMemberId = Member_id;
|
m_Pay_PromotionRecord_money.GetMemberId = m_member.Keyid;
|
m_Pay_PromotionRecord_money.DataType = "奖励";
|
m_Pay_PromotionRecord_money.RewardType = "推广金";
|
}
|
//如果积分大于0 则增加积分记录
|
if ((m_Promotion_IncomeRule.PopularizeScore * Money).ToInt32() > 0)
|
{
|
m_Pay_ScoreRecord.PayId = m_Member_Account.Keyid;
|
m_Pay_ScoreRecord.Remark = m_Member_Account.MemberId.ToString2();
|
m_Pay_ScoreRecord.ResidualScore = m_Member_Account.Score + m_Score;
|
m_Pay_ScoreRecord.TradingScore = m_Score;
|
m_Pay_ScoreRecord.TradingName = m_Promotion_IncomeRule.SourceType.Name + (m_Promotion_IncomeRule.SourceType.Name == "登录" ? "(每日奖励)" : "");
|
m_Pay_ScoreRecord.TradingState = -1;//已完成
|
m_Pay_ScoreRecord.TradingType = 1;//收入
|
m_Pay_ScoreRecord.CreateTime = nowTime;
|
|
|
m_Pay_PromotionRecord_score.PayId = m_Member_Account.Keyid;
|
m_Pay_PromotionRecord_score.Remark = m_Member_Account.MemberId.ToString2();
|
m_Pay_PromotionRecord_score.ResidualMoney = m_Member_Account.Score + m_Score;
|
m_Pay_PromotionRecord_score.TradingMoney = m_Score;
|
m_Pay_PromotionRecord_score.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
switch (m_Promotion_IncomeRule.SourceType.Name)
|
{
|
case "注册":
|
m_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(注册奖励)";
|
break;
|
case "登录":
|
m_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
case "发布印刷需求":
|
m_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
case "发布求职简历":
|
m_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(每日奖励)";
|
break;
|
default:
|
m_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name;
|
break;
|
}
|
|
m_Pay_PromotionRecord_score.TradingState = -1;//已完成
|
m_Pay_PromotionRecord_score.TradingType = 1;//收入
|
m_Pay_PromotionRecord_score.CreateTime = nowTime;
|
m_Pay_PromotionRecord_score.IsProxy = false;
|
m_Pay_PromotionRecord_score.SendMemberId = Member_id;
|
m_Pay_PromotionRecord_score.GetMemberId = Member_id;
|
m_Pay_PromotionRecord_score.DataType = "奖励";
|
m_Pay_PromotionRecord_score.RewardType = "积分";
|
}
|
}
|
}
|
#endregion
|
|
#region 推荐人
|
|
//获取推荐人
|
Model.EC_MemberExtend Re_member = dal_EC_MemberExtendDAL.GetModelByInviter(m_member.Inviter);
|
Model.Pay_PaymentAccount Re_Member_Account = new Pay_PaymentAccount();
|
Pay_PromotionRecord Re_Pay_PromotionRecord_money = new Pay_PromotionRecord();
|
Pay_PromotionRecord Re_Pay_PromotionRecord_score = new Pay_PromotionRecord();
|
Pay_ScoreRecord Re_Pay_ScoreRecord = new Pay_ScoreRecord();
|
decimal? Re_PromotionMoney = 0;
|
int? Re_Score = 0;
|
|
//如果推荐人存在 就给推荐人增加金额
|
if (Re_member != null && m_member.Keyid != Re_member.Keyid)
|
{
|
Re_Member_Account = dal_Pay_PaymentAccountDAL.SelectModleByMemberId(Re_member.Keyid);
|
if (Re_Member_Account != null && Re_Member_Account.Keyid > 0)
|
{
|
Re_Score = (m_Promotion_IncomeRule.SuperiorScore * Money).ToInt32();
|
Re_PromotionMoney = (m_Promotion_IncomeRule.SuperiorNoteMoney * Money).ToDecimal2();
|
|
//如果金额大于0 则增加金额记录
|
if ((m_Promotion_IncomeRule.SuperiorNoteMoney * Money).ToDecimal2() > 0)
|
{
|
Re_Pay_PromotionRecord_money.PayId = Re_Member_Account.Keyid;
|
Re_Pay_PromotionRecord_money.Remark = m_Member_Account.MemberId.ToString2();
|
Re_Pay_PromotionRecord_money.ResidualMoney = Re_Member_Account.PromotionMoney + Re_PromotionMoney;
|
Re_Pay_PromotionRecord_money.TradingMoney = Re_PromotionMoney;
|
Re_Pay_PromotionRecord_money.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
Re_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(推广奖励)";
|
Re_Pay_PromotionRecord_money.TradingState = -1;//已完成
|
Re_Pay_PromotionRecord_money.TradingType = 1;//收入
|
Re_Pay_PromotionRecord_money.CreateTime = nowTime;
|
Re_Pay_PromotionRecord_money.IsProxy = false;
|
Re_Pay_PromotionRecord_money.SendMemberId = Member_id;
|
Re_Pay_PromotionRecord_money.GetMemberId = Re_member.Keyid;
|
Re_Pay_PromotionRecord_money.DataType = "推广";
|
Re_Pay_PromotionRecord_money.RewardType = "推广金";
|
}
|
|
//如果积分大于0 则增加积分记录
|
if ((m_Promotion_IncomeRule.SuperiorScore * Money).ToDecimal2() > 0)
|
{
|
Re_Pay_ScoreRecord.PayId = Re_Member_Account.Keyid;
|
Re_Pay_ScoreRecord.Remark = m_Member_Account.MemberId.ToString2();
|
Re_Pay_ScoreRecord.ResidualScore = Re_Member_Account.Score + Re_Score;
|
Re_Pay_ScoreRecord.TradingScore = Re_Score;
|
Re_Pay_ScoreRecord.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
Re_Pay_ScoreRecord.TradingState = -1;//已完成
|
Re_Pay_ScoreRecord.TradingType = 1;//收入
|
Re_Pay_ScoreRecord.CreateTime = nowTime;
|
|
|
Re_Pay_PromotionRecord_score.PayId = Re_Member_Account.Keyid;
|
Re_Pay_PromotionRecord_score.Remark = m_Member_Account.MemberId.ToString2();
|
Re_Pay_PromotionRecord_score.ResidualMoney = Re_Member_Account.Score + Re_Score;
|
Re_Pay_PromotionRecord_score.TradingMoney = Re_Score;
|
Re_Pay_PromotionRecord_score.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
Re_Pay_PromotionRecord_score.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(推广奖励)";
|
Re_Pay_PromotionRecord_score.TradingState = -1;//已完成
|
Re_Pay_PromotionRecord_score.TradingType = 1;//收入
|
Re_Pay_PromotionRecord_score.CreateTime = nowTime;
|
Re_Pay_PromotionRecord_score.IsProxy = false;
|
Re_Pay_PromotionRecord_score.SendMemberId = Member_id;
|
Re_Pay_PromotionRecord_score.GetMemberId = Re_member.Keyid;
|
Re_Pay_PromotionRecord_score.DataType = "推广";
|
Re_Pay_PromotionRecord_score.RewardType = "积分";
|
}
|
}
|
}
|
|
#endregion
|
|
#region 市级代理
|
|
//获取市级代理
|
Model.EC_MemberBasic m_city_member = dal_EC_ProxyMemberDAL.GetCityProxyMember(Member_id);
|
Model.Pay_PaymentAccount m_city_Member_Account = new Pay_PaymentAccount();
|
Pay_PromotionRecord m_city_Pay_PromotionRecord_money = new Pay_PromotionRecord();
|
Pay_PromotionRecord m_city_Pay_PromotionRecord_score = new Pay_PromotionRecord();
|
decimal? m_city_ProxyMoney = 0;
|
decimal? m_city_ProxyMoneyCash = 0;
|
decimal? m_city_ProxyMoneyAudit = 0;
|
if (m_city_member != null && m_member.Keyid != m_city_member.MemberId)
|
{
|
m_city_Member_Account = dal_Pay_PaymentAccountDAL.SelectModleByMemberId(m_city_member.MemberId);
|
if (m_city_Member_Account != null && m_city_Member_Account.Keyid > 0)
|
{
|
//给市级代理增加金额
|
m_city_ProxyMoney = (m_Promotion_IncomeRule.DeputyCityRevenue * Money).ToDecimal2();
|
m_city_ProxyMoneyCash = (m_Promotion_IncomeRule.DeputyCityRevenue * Money).ToDecimal2() * (0.7).ToDecimal2();
|
m_city_ProxyMoneyAudit = (m_Promotion_IncomeRule.DeputyCityRevenue * Money).ToDecimal2() * (0.3).ToDecimal2();
|
//如果金额大于0 则增加金额记录
|
if ((m_Promotion_IncomeRule.DeputyCityRevenue * Money).ToDecimal2() > 0)
|
{
|
m_city_Pay_PromotionRecord_money.PayId = m_city_Member_Account.Keyid;
|
m_city_Pay_PromotionRecord_money.Remark = m_Member_Account.MemberId.ToString2();
|
m_city_Pay_PromotionRecord_money.ResidualMoney = m_city_Member_Account.ProxyMoney + m_city_ProxyMoney;
|
m_city_Pay_PromotionRecord_money.TradingMoney = m_city_ProxyMoney;
|
m_city_Pay_PromotionRecord_money.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
m_city_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(市级代理奖励)";
|
m_city_Pay_PromotionRecord_money.TradingState = -1;//已完成
|
m_city_Pay_PromotionRecord_money.TradingType = 1;//收入
|
m_city_Pay_PromotionRecord_money.CreateTime = nowTime;
|
m_city_Pay_PromotionRecord_money.IsProxy = true;
|
m_city_Pay_PromotionRecord_money.SendMemberId = Member_id;
|
m_city_Pay_PromotionRecord_money.GetMemberId = m_city_member.MemberId;
|
m_city_Pay_PromotionRecord_money.DataType = "代理";
|
m_city_Pay_PromotionRecord_money.RewardType = "代理金";
|
}
|
}
|
}
|
|
#endregion
|
|
#region 区县代理
|
|
//获取区县代理
|
Model.EC_MemberBasic m_country_member = dal_EC_ProxyMemberDAL.GetCountryProxyMember(Member_id);
|
Model.Pay_PaymentAccount m_country_Member_Account = new Pay_PaymentAccount();
|
Pay_PromotionRecord m_country_Pay_PromotionRecord_money = new Pay_PromotionRecord();
|
Pay_PromotionRecord m_country_Pay_PromotionRecord_score = new Pay_PromotionRecord();
|
decimal? m_country_ProxyMoney = 0;
|
decimal? m_country_ProxyMoneyCash = 0;
|
decimal? m_country_ProxyMoneyAudit = 0;
|
if (m_country_member != null && m_member.Keyid != m_country_member.MemberId)
|
{
|
m_country_Member_Account = dal_Pay_PaymentAccountDAL.SelectModleByMemberId(m_country_member.MemberId);
|
if (m_country_Member_Account != null && m_country_Member_Account.Keyid > 0)
|
{
|
//给区县代理增加金额
|
m_country_ProxyMoney = (m_Promotion_IncomeRule.DeputyDistrictRevenue * Money).ToDecimal2();
|
m_country_ProxyMoneyCash = (m_Promotion_IncomeRule.DeputyDistrictRevenue * Money).ToDecimal2() * (0.7).ToDecimal2();
|
m_country_ProxyMoneyAudit = (m_Promotion_IncomeRule.DeputyDistrictRevenue * Money).ToDecimal2() * (0.3).ToDecimal2();
|
|
//如果金额大于0 则增加金额记录
|
if ((m_Promotion_IncomeRule.DeputyDistrictRevenue * Money).ToDecimal2() > 0)
|
{
|
m_country_Pay_PromotionRecord_money.PayId = m_country_Member_Account.Keyid;
|
m_country_Pay_PromotionRecord_money.Remark = m_Member_Account.MemberId.ToString2();
|
m_country_Pay_PromotionRecord_money.ResidualMoney = m_country_Member_Account.ProxyMoney + m_country_ProxyMoney;
|
m_country_Pay_PromotionRecord_money.TradingMoney = m_country_ProxyMoney;
|
m_country_Pay_PromotionRecord_money.TradingName = m_Promotion_IncomeRule.SourceType.Name;
|
m_country_Pay_PromotionRecord_money.TardingRemark = m_Promotion_IncomeRule.SourceType.Name + "(区县代理奖励)";
|
m_country_Pay_PromotionRecord_money.TradingState = -1;//已完成
|
m_country_Pay_PromotionRecord_money.TradingType = 1;//收入
|
m_country_Pay_PromotionRecord_money.CreateTime = nowTime;
|
m_country_Pay_PromotionRecord_money.IsProxy = true;
|
m_country_Pay_PromotionRecord_money.SendMemberId = Member_id;
|
m_country_Pay_PromotionRecord_money.GetMemberId = m_country_member.MemberId;
|
m_country_Pay_PromotionRecord_money.DataType = "代理";
|
m_country_Pay_PromotionRecord_money.RewardType = "代理金";
|
}
|
}
|
}
|
#endregion
|
|
#region 执行事务
|
|
bool result = true;
|
using (TransactionScope t_TransactionScope = new TransactionScope())
|
{
|
|
if (result)
|
{
|
if (m_Pay_PromotionRecord_money.PayId == m_Member_Account.Keyid && m_Pay_PromotionRecord_money.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(m_Pay_PromotionRecord_money);
|
if (result)
|
{
|
if (m_Pay_PromotionRecord_score.PayId == m_Member_Account.Keyid && m_Pay_PromotionRecord_score.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(m_Pay_PromotionRecord_score);
|
}
|
if (result)
|
{
|
if (m_Pay_ScoreRecord.PayId == m_Member_Account.Keyid && m_Pay_ScoreRecord.PayId > 0)
|
result = dal_Pay_ScoreRecordDAL.InserModel(m_Pay_ScoreRecord);
|
}
|
if (result)
|
{
|
result = dal_Pay_PaymentAccountDAL.PayPaymentAccountPromotion(m_Member_Account.Keyid, m_PromotionMoney, m_Score, 0, 0, 0);
|
}
|
}
|
|
|
|
if (result)
|
{
|
if (Re_Pay_PromotionRecord_money.PayId == Re_Member_Account.Keyid && Re_Pay_PromotionRecord_money.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(Re_Pay_PromotionRecord_money);
|
if (result)
|
{
|
if (Re_Pay_PromotionRecord_score.PayId == Re_Member_Account.Keyid && Re_Pay_PromotionRecord_score.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(Re_Pay_PromotionRecord_score);
|
}
|
if (result)
|
{
|
if (Re_Pay_ScoreRecord.PayId == Re_Member_Account.Keyid && Re_Pay_ScoreRecord.PayId > 0)
|
result = dal_Pay_ScoreRecordDAL.InserModel(Re_Pay_ScoreRecord);
|
}
|
if (result && Re_Member_Account != null && Re_Member_Account.Keyid > 0)
|
{
|
result = dal_Pay_PaymentAccountDAL.PayPaymentAccountPromotion(Re_Member_Account.Keyid, Re_PromotionMoney, Re_Score, 0, 0, 0);
|
}
|
}
|
|
|
|
if (result)
|
{
|
if (m_city_Member_Account.MemberId != m_Member_Account.MemberId)
|
{
|
if (Re_Member_Account != null && m_city_Member_Account.MemberId != m_Member_Account.MemberId)
|
{
|
if (m_city_Pay_PromotionRecord_money.PayId == m_city_Member_Account.Keyid && m_city_Pay_PromotionRecord_money.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(m_city_Pay_PromotionRecord_money);
|
|
if (result && m_city_Member_Account != null && m_city_Member_Account.Keyid > 0)
|
{
|
result = dal_Pay_PaymentAccountDAL.PayPaymentAccountPromotion(m_city_Member_Account.Keyid, 0, 0, m_city_ProxyMoney, m_city_ProxyMoneyCash, m_city_ProxyMoneyAudit);
|
}
|
}
|
}
|
}
|
|
|
if (result)
|
{
|
if (m_country_Member_Account.MemberId != m_Member_Account.MemberId)
|
{
|
if (Re_Member_Account != null && m_country_Member_Account.MemberId != m_Member_Account.MemberId)
|
{
|
if (m_country_Pay_PromotionRecord_money.PayId == m_country_Member_Account.Keyid && m_country_Pay_PromotionRecord_money.PayId > 0)
|
result = dal_Pay_PromotionRecordDAL.InserModel(m_country_Pay_PromotionRecord_money);
|
|
if (result && m_country_Member_Account != null && m_country_Member_Account.Keyid > 0)
|
{
|
result = dal_Pay_PaymentAccountDAL.PayPaymentAccountPromotion(m_country_Member_Account.Keyid, 0, 0, m_country_ProxyMoney, m_country_ProxyMoneyCash, m_country_ProxyMoneyAudit);
|
}
|
}
|
}
|
}
|
|
if (result)
|
{
|
t_TransactionScope.Complete();
|
}
|
|
}
|
return result;
|
|
#endregion
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据类型和金额获取积分
|
/// <para>36 注册</para>
|
/// <para>38 发布印刷需求</para>
|
/// <para>292 发布需求信息</para>
|
/// <para>86 账户充值</para>
|
/// <para>87 印刷订单</para>
|
/// <para>88 纸张订单</para>
|
/// <para>89 印刷厂商注册费</para>
|
/// <para>90 杂志广告费</para>
|
/// <para>91 管理软件费</para>
|
/// <para>92 办公家具订单</para>
|
///<para> 93 办公电脑设备订单</para>
|
/// <para>94 办公文具订单</para>
|
/// <para>95 其他商城订单</para>
|
/// <para>96 发布求职简历</para>
|
/// <para>290 发布秒杀业务</para>
|
/// <para>291 发布特价信息</para>
|
/// </summary>
|
/// <param name="SourceTypeId"></param>
|
/// <param name="Money"></param>
|
/// <returns></returns>
|
public int? GetScoreBySourceTypeId(int? SourceTypeId, decimal? Money)
|
{
|
try
|
{
|
Promotion_IncomeRuleDAL dal_Promotion_IncomeRuleDAL = new Promotion_IncomeRuleDAL();
|
Sys_DictionaryDAL dal_Sys_DictionaryDAL = new Sys_DictionaryDAL();
|
|
//获取积分配置信息
|
Model.Promotion_IncomeRule m_Promotion_IncomeRule = dal_Promotion_IncomeRuleDAL.GetModelBySourceTypeId(SourceTypeId);
|
|
int? result = 0;
|
|
if (m_Promotion_IncomeRule == null || m_Promotion_IncomeRule.Keyid == null)
|
return 0; //如果不存在就跳出
|
|
//为了方便计算，无倍数即声明金额为1
|
if (m_Promotion_IncomeRule.IsRatio)
|
Money = Money.ToDecimal2();
|
else
|
Money = 1;
|
|
result = (Money * m_Promotion_IncomeRule.PopularizeScore).Value.ToString("0").ToInt32();
|
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 获取上周积分排名
|
/// </summary>
|
/// <param name="rows">条数</param>
|
/// <returns></returns>
|
public IEnumerable<Pay_PaymentAccount> SelectModleByRows(int? rows)
|
{
|
try
|
{
|
if (rows == null)
|
return null;//错误数据返会空
|
|
IList<Pay_PaymentAccount> result = _dataBase.SelectModel<Pay_PaymentAccount>(" top(" + rows + ") b.Name as MemberName,sum(c.TradingScore) as LastWeekScore ", " Pay_PaymentAccount as a left join EC_MemberBasic as b on a.MemberId = b.MemberId left join Pay_ScoreRecord as c on a.Keyid = c.PayId and c.CreateTime >='" + GetLastSunday().AddDays(-6) + "' and c.CreateTime <= '" + GetLastSunday().AddDays(1) + "'", " 1= 1 and (b.MemberType='个人网店' or b.MemberType='买家会员' or b.MemberType='印刷厂商') group by b.Name,b.MemberId order by sum(c.ResidualScore) DESC") as IList<Pay_PaymentAccount>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 获取上周周末日期
|
/// </summary>
|
/// <returns></returns>
|
public DateTime GetLastSunday()
|
{
|
DateTime? time = DateTime.Now.ToString("yyyy-MM-dd").ToDateTime2();
|
if (time.Value.DayOfWeek == DayOfWeek.Sunday)
|
{
|
time = time.Value.AddDays(-2);
|
}
|
else
|
time = time.Value.AddDays(-1);
|
while (time.Value.DayOfWeek != DayOfWeek.Sunday)
|
{
|
time = time.Value.AddDays(-1);
|
}
|
return time.Value;
|
}
|
|
/// <summary>
|
/// 根据时间查询当前人的账户统计信息
|
/// </summary>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="memberId"></param>
|
/// <returns></returns>
|
public PaymentAccountQueryModel GetPaymentAccountQueryResult(string startDate, string endDate, int currentPayId)
|
{
|
PaymentAccountQueryModel result = new PaymentAccountQueryModel();
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
sqlStr = @"
|
SELECT CASE WHEN TradingType=1 THEN SUM(TradingScore) END AS AllTradingScore,
|
CASE WHEN TradingType=-1 THEN SUM(TradingScore) END AS UsedTradingScore
|
FROM Pay_ScoreRecord
|
WHERE TradingState=-1 AND PayId=@PayId ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>='" + startDate + "'";
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<='" + endDate + "'";
|
}
|
|
sqlStr += " GROUP BY TradingType ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["AllTradingScore"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["AllTradingScore"].ToString()))
|
result.AllScore = dt.Rows[0]["AllTradingScore"].ToInt32().Value;
|
}
|
if (dt.Rows[0]["UsedTradingScore"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedTradingScore"].ToString()))
|
result.UsedScore = dt.Rows[0]["UsedTradingScore"].ToInt32().Value;
|
}
|
}
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @" SELECT CASE WHEN TradingType=1 THEN SUM(TradingMoney) END AS AllTradingMoney,
|
CASE WHEN TradingType=-1 THEN SUM(TradingMoney) END AS UsedTradingMoney
|
FROM Pay_PromotionRecord
|
WHERE TradingState=-1 AND PayId=@PayId ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>='" + startDate + "'";
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<='" + endDate + "'";
|
}
|
sqlStr += " GROUP BY TradingType ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["AllTradingMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["AllTradingMoney"].ToString()))
|
result.AllPromotionMoney = dt.Rows[0]["AllTradingMoney"].ToDecimal2().Value;
|
}
|
if (dt.Rows[0]["UsedTradingMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedTradingMoney"].ToString()))
|
result.UsedPromotionMoney = dt.Rows[0]["UsedTradingMoney"].ToDecimal2().Value;
|
}
|
}
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(TradingMoney) AS UsedCommissionMoney FROM dbo.Pay_IncExpRecord WHERE TradingType=-1 AND PayId=@PayId AND (BalanceAccount=3 OR BalanceAccount=10) ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>='" + startDate + "'";
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<='" + endDate + "'";
|
}
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["UsedCommissionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedCommissionMoney"].ToString()))
|
result.UsedCommissionMoney = dt.Rows[0]["UsedCommissionMoney"].ToDecimal2().Value;
|
}
|
}
|
decimal surplusCommissionMoney = 0;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(OwedAmount) AS SurplusCommissionMoney FROM dbo.Pay_OwedMoney WHERE IsEffective=1 AND Payid=@PayId ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>='" + startDate + "'";
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<='" + endDate + "'";
|
}
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["SurplusCommissionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["SurplusCommissionMoney"].ToString()))
|
surplusCommissionMoney = dt.Rows[0]["SurplusCommissionMoney"].ToDecimal2().Value;
|
}
|
}
|
result.AllCommissionMoney = result.UsedCommissionMoney + surplusCommissionMoney;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(TradingMoney) AS PayMoneyByPrint FROM dbo.Pay_IncExpRecord
|
WHERE TradingType=-1 AND PayId=@PayId AND (BalanceAccount=4 OR BalanceAccount=5) ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>='" + startDate + "'";
|
}
|
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<='" + endDate + "'";
|
}
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["PayMoneyByPrint"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["PayMoneyByPrint"].ToString()))
|
result.PayMoneyByPrint = dt.Rows[0]["PayMoneyByPrint"].ToDecimal2().Value;
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 获取管理员当前账户信息
|
/// </summary>
|
/// <returns></returns>
|
public PaymentAccountModelByAdmin GetPaymentAccountModelByAdmin(int currentPayId, decimal commission)
|
{
|
PaymentAccountModelByAdmin result = new PaymentAccountModelByAdmin();
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal allCredit = 0;
|
decimal returnedCredit = 0;
|
sqlStr = @"SELECT SUM(TradingMoney) AS AllCredit FROM dbo.Pay_IncExpRecord
|
WHERE TradingType=-1 AND PayId=@PayId AND BalanceAccount=9";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["AllCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["AllCredit"].ToString()))
|
allCredit = dt.Rows[0]["AllCredit"].ToDecimal2().Value;
|
}
|
}
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(TradingMoney) AS ReturnedCredit FROM dbo.Pay_IncExpRecord
|
WHERE TradingType=1 AND PayeeId=@PayId AND BalanceAccount=14";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["ReturnedCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["ReturnedCredit"].ToString()))
|
returnedCredit = dt.Rows[0]["ReturnedCredit"].ToDecimal2().Value;
|
}
|
}
|
result.NoReturnCredit = allCredit - returnedCredit;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT TOP 1 ResidualMoney
|
FROM Pay_IncExpRecord
|
WHERE (TradingType=-1 AND PayId=@PayId) OR (TradingType=1 AND PayeeId=@PayId)
|
ORDER BY CreateTime DESC,Keyid DESC";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["ResidualMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["ResidualMoney"].ToString()))
|
result.AllHaveMoney = dt.Rows[0]["ResidualMoney"].ToDecimal2().Value;
|
}
|
}
|
// decimal noAcceptMoney = 0;
|
// dt = null;
|
// pars = new List<SqlParameter>();
|
// par = null;
|
// sqlStr = @"SELECT SUM(SumPrice) as SumPrice
|
// FROM dbo.EC_OrderBasic
|
// WHERE OrderState=1
|
// AND OrderTypeId<1";
|
// dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, null);
|
// if (dt != null && dt.Rows.Count > 0)
|
// {
|
// if (dt.Rows[0]["SumPrice"] != null)
|
// {
|
// if (!string.IsNullOrEmpty(dt.Rows[0]["SumPrice"].ToString()))
|
// {
|
// noAcceptMoney = dt.Rows[0]["SumPrice"].ToDecimal2().Value;
|
// }
|
// }
|
// }
|
// decimal canHaveCommissionMoney = noAcceptMoney * commission;
|
decimal owedAmountAll = 0;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(OwedAmount) AS OwedAmount
|
FROM dbo.Pay_OwedMoney WHERE IsEffective=1";
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, null);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["OwedAmount"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["OwedAmount"].ToString()))
|
{
|
owedAmountAll = dt.Rows[0]["OwedAmount"].ToDecimal2().Value;
|
}
|
}
|
}
|
result.CanHaveMoney = owedAmountAll;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT (SUM(b.OnlineMoney+b.UsedCredit)) AS CanPayMoney
|
FROM dbo.EC_OrderBasic a INNER JOIN Pay_TradingSingle b
|
ON a.Keyid=b.OrderId INNER JOIN dbo.EC_MemberBasic c ON b.PayerId=c.MemberId
|
INNER JOIN dbo.Pay_PaymentAccount d ON d.MemberId=c.MemberId
|
INNER JOIN Pay_CreditLineLevelRule e ON e.Keyid=d.LevelRule
|
WHERE a.OrderState NOT IN (0,-1) AND (b.OnlineMoney+b.UsedCredit>0)
|
AND OrderTypeId<1";
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, null);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["CanPayMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["CanPayMoney"].ToString()))
|
{
|
result.CanPayMoney = dt.Rows[0]["CanPayMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
decimal totalCommission = 0;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(TradingMoney) AS TotalCommission
|
FROM dbo.Pay_IncExpRecord
|
WHERE TradingType=1 AND PayeeId=@PayId AND BalanceAccount=3";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalCommission"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalCommission"].ToString()))
|
{
|
totalCommission = dt.Rows[0]["TotalCommission"].ToDecimal2().Value;
|
}
|
}
|
}
|
decimal totalPayment = 0;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(PayMoney) as TotalPayMoney FROM dbo.EC_PaymentRecord";
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, null);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalPayMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalPayMoney"].ToString()))
|
{
|
totalPayment = dt.Rows[0]["TotalPayMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
result.TotalIncome = totalCommission + totalPayment;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(TradingMoney) AS TotalReturn
|
FROM dbo.Pay_IncExpRecord
|
WHERE TradingType=-1 AND PayId=@PayId AND BalanceAccount=1
|
";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalReturn"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalReturn"].ToString()))
|
{
|
result.TotalExpend = dt.Rows[0]["TotalReturn"].ToDecimal2().Value;
|
}
|
}
|
}
|
return result;
|
}
|
|
|
|
/// <summary>
|
/// 计算累计已收佣金
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
private decimal GetTotalCommission(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
|
decimal totalCommission = 0;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalCommission
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=1 AND a.PayeeId=@PayId AND a.BalanceAccount=3 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalCommission"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalCommission"].ToString()))
|
{
|
totalCommission = dt.Rows[0]["TotalCommission"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalCommission;
|
}
|
|
/// <summary>
|
/// 计算累计已还授信
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
private decimal GetTotalReturnCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal totalReturnCredit = 0;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalReturnCredit
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=-1 AND a.PayeeId=@PayeeId AND a.BalanceAccount=14 ";
|
par = new SqlParameter("@PayeeId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalReturnCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalReturnCredit"].ToString()))
|
{
|
totalReturnCredit = dt.Rows[0]["TotalReturnCredit"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalReturnCredit;
|
|
}
|
|
/// <summary>
|
/// 计算累计已还交易佣金
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
private decimal GetTotalReturnCommission(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal totalReturnCommission = 0;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalReturnCommission
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=1 AND a.PayeeId=@PayId AND a.BalanceAccount=10 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalReturnCommission"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalReturnCommission"].ToString()))
|
{
|
totalReturnCommission = dt.Rows[0]["TotalReturnCommission"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalReturnCommission;
|
|
}
|
|
/// <summary>
|
/// 总其他收入（比如广告等）
|
/// </summary>
|
/// <returns></returns>
|
private decimal GetAllIncomeByorther(string payType, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal totalPayment = 0;
|
|
sqlStr = @"SELECT SUM(a.PayMoney) as TotalPayMoney FROM dbo.EC_PaymentRecord a INNER JOIN EC_MemberBasic b ON a.MemberId=b.MemberId where 1=1 and a.PayMoney>0";
|
if (!string.IsNullOrEmpty(payType))
|
{
|
sqlStr += " and a.PayType=@PayType";
|
par = new SqlParameter("@PayType", payType);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.LastUpdateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.LastUpdateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and b.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and b.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and b.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalPayMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalPayMoney"].ToString()))
|
{
|
totalPayment = dt.Rows[0]["TotalPayMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalPayment;
|
}
|
|
/// <summary>
|
/// 计算使用授信
|
/// </summary>
|
/// <returns></returns>
|
private decimal GetUseCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal totalUseCredit = 0;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalUseCredit
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=-1 AND a.PayId=@PayId AND a.BalanceAccount=9 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalUseCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalUseCredit"].ToString()))
|
{
|
totalUseCredit = dt.Rows[0]["TotalUseCredit"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalUseCredit;
|
}
|
|
/// <summary>
|
/// 计算累计会员充值
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public decimal GetMemberRecharge(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
decimal totalMemberRecharge = 0;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalMemberRecharge
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=-1 AND a.PayId=@PayId AND a.BalanceAccount=0 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalMemberRecharge"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalMemberRecharge"].ToString()))
|
{
|
totalMemberRecharge = dt.Rows[0]["TotalMemberRecharge"].ToDecimal2().Value;
|
}
|
}
|
}
|
|
return totalMemberRecharge;
|
}
|
|
/// <summary>
|
/// 计算累计会员提现
|
/// </summary>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
private decimal GetWithdrawMoney(string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
|
decimal totalWithdrawMoney = 0;
|
|
sqlStr = @"SELECT SUM(Money) AS WithdrawMoney
|
FROM dbo.Pay_CashApply a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.State=2 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["WithdrawMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["WithdrawMoney"].ToString()))
|
{
|
totalWithdrawMoney = dt.Rows[0]["WithdrawMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
return totalWithdrawMoney;
|
}
|
|
/// <summary>
|
/// 计算累计会员余额
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public decimal GetSurplusMoney(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
|
decimal surplusMoney = 0;
|
|
IList<int> payIdList = new List<int>();
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT a.Keyid
|
FROM dbo.Pay_PaymentAccount a INNER JOIN dbo.EC_MemberBasic b
|
ON a.MemberId=b.MemberId
|
WHERE a.Keyid<>@PayId ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and b.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and b.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and b.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["Keyid"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["Keyid"].ToString()))
|
{
|
payIdList.Add(dt.Rows[0]["Keyid"].ToString().ToInt32().Value);
|
}
|
}
|
}
|
if (payIdList.Count > 0)
|
{
|
bool hasRcord = false;
|
foreach (int payid in payIdList)
|
{
|
hasRcord = false;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT TOP 1 ResidualMoney
|
FROM dbo.Pay_IncExpRecord
|
WHERE (TradingType=1 AND PayeeId=@PayId) OR (TradingType=-1 AND PayId=@PayId) ";
|
par = new SqlParameter("@PayId", payid);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
sqlStr += " ORDER BY CreateTime DESC,Keyid DESC ";
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["ResidualMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["ResidualMoney"].ToString()))
|
{
|
hasRcord = true;
|
surplusMoney += dt.Rows[0]["ResidualMoney"].ToString().ToDecimal2().Value;
|
}
|
}
|
}
|
if (!hasRcord)
|
{
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SurplusMoney FROM dbo.Pay_PaymentAccount WHERE Keyid=@PayId ";
|
par = new SqlParameter("@PayId", payid);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and LastUpdateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and LastUpdateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["SurplusMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["SurplusMoney"].ToString()))
|
{
|
surplusMoney += dt.Rows[0]["SurplusMoney"].ToString().ToDecimal2().Value;
|
}
|
}
|
}
|
}
|
}
|
}
|
return surplusMoney;
|
}
|
|
/// <summary>
|
/// 计算累计授信
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public decimal GetAllCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
|
decimal allCredit = 0;
|
sqlStr = @"select SUM(a.TradingMoney) as allCredit
|
FROM dbo.Pay_CreditRecard a inner join Pay_PaymentAccount b on a.PayId=b.KeyId INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId
|
WHERE a.PayId<>@PayId";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["allCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["allCredit"].ToString()))
|
{
|
allCredit += dt.Rows[0]["allCredit"].ToString().ToDecimal2().Value;
|
}
|
}
|
}
|
|
return allCredit;
|
}
|
|
/// <summary>
|
/// 计算授信余额
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public decimal GetHasCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
|
decimal surplusCredit = 0;
|
|
//累计授信
|
decimal allCredit = GetAllCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
decimal totalUseCredit = 0;
|
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS TotalUseCredit
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=-1 AND a.PayId=@PayId AND a.BalanceAccount=9 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["TotalUseCredit"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["TotalUseCredit"].ToString()))
|
{
|
totalUseCredit += dt.Rows[0]["TotalUseCredit"].ToString().ToDecimal2().Value;
|
}
|
}
|
}
|
surplusCredit = allCredit - totalUseCredit;
|
return surplusCredit;
|
}
|
|
/// <summary>
|
/// 获取期初授信
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public decimal GetCreditByBefore(int currentPayId, string startDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
|
decimal creditByBefore = 0;
|
|
if (string.IsNullOrEmpty(startDate))
|
{
|
return creditByBefore;
|
}
|
|
sqlStr = @"select SUM(a.TradingMoney) as allCreditByBefore
|
FROM dbo.Pay_CreditRecard a inner join Pay_PaymentAccount b on a.PayId=b.KeyId INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId
|
WHERE a.PayId<>@PayId";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime<@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["allCreditByBefore"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["allCreditByBefore"].ToString()))
|
{
|
creditByBefore += dt.Rows[0]["allCreditByBefore"].ToString().ToDecimal2().Value;
|
}
|
}
|
}
|
|
return creditByBefore;
|
}
|
|
/// <summary>
|
/// 查询管理员的统计信息
|
/// </summary>
|
/// <param name="currentPayId"></param>
|
/// <param name="?"></param>
|
/// <returns></returns>
|
public PaymentAccountQueryModelByAdmin GetPaymentAccountQueryModelByAdmin(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
PaymentAccountQueryModelByAdmin result = new PaymentAccountQueryModelByAdmin();
|
string sqlStr = string.Empty;
|
IList<SqlParameter> pars = new List<SqlParameter>();
|
DataTable dt = null;
|
SqlParameter par = null;
|
#region 计算总收入
|
|
//计算累计已收交易佣金
|
result.AllIncomeByCommission = GetTotalCommission(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//已还授信
|
result.AllIncomeByReturnCredit = GetTotalReturnCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//已还佣金
|
result.AllIncomeByReturnCommission = GetTotalReturnCommission(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//累计厂商注册费
|
result.AllIncomeByRegsiter = GetAllIncomeByorther("印刷厂商注册费", startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//累计网店押金
|
result.AllIncomeByDeposit = GetAllIncomeByorther("个人网店押金", startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//累计厂商续费
|
result.AllIncomeByDepositByRenewal = GetAllIncomeByorther("印刷厂商续费", startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//累计网站广告
|
result.AllIncomeByWebAd = GetAllIncomeByorther("网站广告费", startDate, endDate, selectProvince, selectCity, selectCounty);
|
|
//累计杂志广告
|
result.AllIncomeByNoteAd = GetAllIncomeByorther("杂志广告费", startDate, endDate, selectCity, selectProvince, selectCounty);
|
|
//累计管理软件
|
result.AllIncomeByManageSoftware = GetAllIncomeByorther("管理软件费", startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 计算总支出(使用授信)
|
result.AllExpend = GetUseCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 会员充值
|
result.RechargeMoney = GetMemberRecharge(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 会员提现
|
result.WithdrawMoney = GetWithdrawMoney(startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 会员余额
|
result.SurplusMoney = GetSurplusMoney(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 累计授信
|
result.AllCredit = GetAllCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 累计使用授信
|
result.CreditUsed = GetUseCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 累计授信余额
|
result.HasCredit = GetHasCredit(currentPayId, startDate, endDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
#region 累计期初授信
|
result.CreditByBefore = GetCreditByBefore(currentPayId, startDate, selectProvince, selectCity, selectCounty);
|
#endregion
|
|
#region 累计积分
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.TradingScore) AS AllScore
|
FROM dbo.Pay_ScoreRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingState=-1 AND a.TradingType=1 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["AllScore"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["AllScore"].ToString()))
|
{
|
result.AllScore = dt.Rows[0]["AllScore"].ToString().ToInt32().Value;
|
}
|
}
|
}
|
#endregion
|
#region 兑换积分
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.TradingScore) AS UsedScore
|
FROM dbo.Pay_ScoreRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingState=-1 AND a.TradingType=-1 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["UsedScore"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedScore"].ToString()))
|
{
|
result.UsedScore = dt.Rows[0]["UsedScore"].ToString().ToInt32().Value;
|
}
|
}
|
}
|
#endregion
|
|
#region 累计推广佣金
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS AllPromotionMoney
|
FROM dbo.Pay_PromotionRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingState=-1 AND a.TradingType=1 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["AllPromotionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["AllPromotionMoney"].ToString()))
|
{
|
result.AllPromotionMoney = dt.Rows[0]["AllPromotionMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
#endregion
|
#region 兑换佣金
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS UsedPromotionMoney
|
FROM dbo.Pay_PromotionRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingState=-1 AND a.TradingType=-1 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["UsedPromotionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedPromotionMoney"].ToString()))
|
{
|
result.UsedPromotionMoney = dt.Rows[0]["UsedPromotionMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
#endregion
|
#region 已付佣金
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.TradingMoney) AS UsedCommissionMoney
|
FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.TradingType=1 AND a.PayeeId=@PayId AND (a.BalanceAccount=3 OR a.BalanceAccount=10) ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["UsedCommissionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["UsedCommissionMoney"].ToString()))
|
{
|
result.UsedCommissionMoney = dt.Rows[0]["UsedCommissionMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
#endregion
|
#region 未付佣金
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(a.OwedAmount) AS SurplusCommissionMoney
|
FROM dbo.Pay_OwedMoney a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.IsEffective=1 ";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["SurplusCommissionMoney"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["SurplusCommissionMoney"].ToString()))
|
{
|
result.SurplusCommissionMoney = dt.Rows[0]["SurplusCommissionMoney"].ToDecimal2().Value;
|
}
|
}
|
}
|
#endregion
|
// #region 交易返点
|
// dt = null;
|
// pars = new List<SqlParameter>();
|
// par = null;
|
// sqlStr = @"SELECT SUM(a.TradingMoney) AS AllPayReturn
|
// FROM dbo.Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
// ON a.PayeeId=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
// WHERE a.TradingType=-1 AND a.PayId=@PayId AND a.BalanceAccount=1 ";
|
// par = new SqlParameter("@PayId", currentPayId);
|
// pars.Add(par);
|
// if (!string.IsNullOrEmpty(startDate))
|
// {
|
// sqlStr += " and a.CreateTime>=@startDate ";
|
// par = new SqlParameter("@startDate", startDate);
|
// pars.Add(par);
|
// }
|
// if (!string.IsNullOrEmpty(endDate))
|
// {
|
// sqlStr += " and a.CreateTime<=@endDate ";
|
// par = new SqlParameter("@endDate", endDate);
|
// pars.Add(par);
|
// }
|
// if (!string.IsNullOrEmpty(selectProvince))
|
// {
|
// sqlStr += " and c.Province=@Province ";
|
// par = new SqlParameter("@Province", selectProvince);
|
// pars.Add(par);
|
// }
|
|
// if (!string.IsNullOrEmpty(selectCity))
|
// {
|
// sqlStr += " and c.City=@City ";
|
// par = new SqlParameter("@City", selectCity);
|
// pars.Add(par);
|
// }
|
|
// if (!string.IsNullOrEmpty(selectCounty))
|
// {
|
// sqlStr += " and c.County=@County ";
|
// par = new SqlParameter("@County", selectCounty);
|
// pars.Add(par);
|
// }
|
// dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
// if (dt != null && dt.Rows.Count > 0)
|
// {
|
// if (dt.Rows[0]["AllPayReturn"] != null)
|
// {
|
// if (!string.IsNullOrEmpty(dt.Rows[0]["AllPayReturn"].ToString()))
|
// {
|
// result.AllPayReturn = dt.Rows[0]["AllPayReturn"].ToDecimal2().Value;
|
// }
|
// }
|
// }
|
// #endregion
|
#region 印刷交易额
|
dt = null;
|
pars = new List<SqlParameter>();
|
par = null;
|
sqlStr = @"SELECT SUM(b.OnlineMoney+b.UsedCredit) AS PayMoneyByPrint
|
FROM dbo.EC_OrderBasic a INNER JOIN dbo.Pay_TradingSingle b
|
ON a.Keyid=b.OrderId INNER JOIN dbo.EC_MemberBasic c ON b.PayerId=c.MemberId
|
WHERE a.OrderState=-1 AND a.OrderTypeId<1 AND (b.OnlineMoney+b.UsedCredit)>0 ";
|
par = new SqlParameter("@PayId", currentPayId);
|
pars.Add(par);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
sqlStr += " and a.CreateTime>=@startDate ";
|
par = new SqlParameter("@startDate", startDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
sqlStr += " and a.CreateTime<@endDate ";
|
par = new SqlParameter("@endDate", endDate);
|
pars.Add(par);
|
}
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
sqlStr += " and c.Province=@Province ";
|
par = new SqlParameter("@Province", selectProvince);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
sqlStr += " and c.City=@City ";
|
par = new SqlParameter("@City", selectCity);
|
pars.Add(par);
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
sqlStr += " and c.County=@County ";
|
par = new SqlParameter("@County", selectCounty);
|
pars.Add(par);
|
}
|
dt = _dataBase.QueryDataTable(sqlStr, CommandType.Text, pars.ToArray<SqlParameter>());
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["PayMoneyByPrint"] != null)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[0]["PayMoneyByPrint"].ToString()))
|
{
|
result.PayMoneyByPrint = dt.Rows[0]["PayMoneyByPrint"].ToDecimal2().Value;
|
}
|
}
|
}
|
#endregion
|
return result;
|
}
|
|
/// <summary>
|
/// 推广更新账户信息
|
/// </summary>
|
/// <param name="keyId"></param>
|
/// <param name="PromotionMoney"></param>
|
/// <param name="Score"></param>
|
/// <param name="ProxyMoney"></param>
|
/// <param name="ProxyMoneyCash"></param>
|
/// <param name="ProxyMoneyAudit"></param>
|
/// <returns></returns>
|
public bool PayPaymentAccountPromotion(int? keyId, decimal? PromotionMoney, int? Score, decimal? ProxyMoney, decimal? ProxyMoneyCash, decimal? ProxyMoneyAudit)
|
{
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@keyId",keyId),
|
new SqlParameter("@PromotionMoney",PromotionMoney),
|
new SqlParameter("@Score",Score),
|
new SqlParameter("@ProxyMoney",ProxyMoney),
|
new SqlParameter("@ProxyMoneyCash",ProxyMoneyCash),
|
new SqlParameter("@ProxyMoneyAudit",ProxyMoneyAudit),
|
new SqlParameter("@returnState",0){SqlDbType=System.Data.SqlDbType.Int, Direction=ParameterDirection.Output}
|
};
|
try
|
{
|
_dataBase.Query("sp_PayPaymentAccountPromotion", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
return 0 < (int)(sqlParms[6].Value);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
}
|
}
|
