/** * 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; } /// /// 新增 /// /// /// public bool InserModel(Infrastructure.Domain.IAggregateRoot model) { Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount; if (trueModel == null) { return false; } IList sqlParms = new List() { 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()); trueModel.Keyid = (int)sqlParms[0].Value; } catch (Exception ex) { throw ex; } return true; } /// /// 修改 /// /// /// public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model) { Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount; if (trueModel == null) { return false; } IList sqlParms = new List() { 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()); } catch (Exception ex) { throw ex; } return true; } /// /// 删除 /// /// /// public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model) { Model.Pay_PaymentAccount trueModel = model as Model.Pay_PaymentAccount; if (trueModel == null) { return false; } IList sqlParms = new List() { new SqlParameter("@Keyid",trueModel.Keyid) }; try { _dataBase.Query("sp_Pay_PaymentAccount_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray()); } catch (Exception ex) { throw ex; } return true; } /// /// 分页查询 /// /// /// /// public IEnumerable SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination) { throw new NotImplementedException(); } /// /// 单个查询 /// /// /// public IEnumerable SelectAllModel(Infrastructure.Query.Query query) { throw new NotImplementedException(); } /// /// 根据编号获取实体信息 /// /// 会员编号 /// public Pay_PaymentAccount SelectModleByMemberId(Guid MemberId) { if (MemberId == null) return null;//错误数据返会空 IList result = _dataBase.SelectModel("*", "Pay_PaymentAccount", " MemberId = '" + MemberId + "'") as IList;//执行查询 return (null == result || result.Count == 0) ? null : result[0];//返回结果 } /// /// 支付公共接口 /// /// 支付申请 /// public bool Pay(CY.Model.Pay.Pay_Request m_Pay_Request) { if (m_Pay_Request == null) { return false; } IList sqlParms = new List() { 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()); 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; } /// /// 归并用户缓冲金额并查看账户信息 /// /// 会员编号 /// 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 result = _dataBase.ReadDataToModel(reader); return null == result || result.Count < 1 ? null : result[0]; } } /// /// 充值 /// /// /// /// 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 sqlParms = new List() { new SqlParameter("@payerid",m_Pay_Request_order.Payerid) }; _dataBase.Query("sp_Order_Recharge", CommandType.StoredProcedure, sqlParms.ToArray()); } } } return result; } catch (Exception ex) { throw ex; } } /// /// 线下充值 /// /// /// /// 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 sqlParms = new List() { new SqlParameter("@payerid",m_Pay_Request_order.Payerid) }; _dataBase.Query("sp_Order_Recharge", CommandType.StoredProcedure, sqlParms.ToArray()); } } } return result; } catch (Exception ex) { throw ex; } } /// /// 提现转账 /// /// /// /// 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; } } /// /// 给会员增加积分或现金(推广信息) /// 36 买家会员注册费 /// 393 个人网店注册费 /// 89 印刷厂商注册费 /// 391 登录 /// 38 发布印刷需求 /// 292 发布需求信息 /// 86 账户充值 /// 87 印刷订单 /// 88 纸张订单 /// 90 杂志广告费 /// 392 网站广告费 /// 91 管理软件费 /// 92 办公家具订单 /// 93 办公电脑设备订单 /// 94 办公文具订单 /// 95 其他商城订单 /// 96 发布求职简历 /// 290 发布秒杀业务 /// 291 发布特价信息 /// /// 使用人会员编号 /// 推广来源类型编号 /// /// 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; } } /// /// 根据类型和金额获取积分 /// 36 注册 /// 38 发布印刷需求 /// 292 发布需求信息 /// 86 账户充值 /// 87 印刷订单 /// 88 纸张订单 /// 89 印刷厂商注册费 /// 90 杂志广告费 /// 91 管理软件费 /// 92 办公家具订单 /// 93 办公电脑设备订单 /// 94 办公文具订单 /// 95 其他商城订单 /// 96 发布求职简历 /// 290 发布秒杀业务 /// 291 发布特价信息 /// /// /// /// 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; } } /// /// 获取上周积分排名 /// /// 条数 /// public IEnumerable SelectModleByRows(int? rows) { try { if (rows == null) return null;//错误数据返会空 IList result = _dataBase.SelectModel(" 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;//执行查询 return result;//返回结果 } catch (Exception ex) { throw ex; } } /// /// 获取上周周末日期 /// /// 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; } /// /// 根据时间查询当前人的账户统计信息 /// /// /// /// /// public PaymentAccountQueryModel GetPaymentAccountQueryResult(string startDate, string endDate, int currentPayId) { PaymentAccountQueryModel result = new PaymentAccountQueryModel(); string sqlStr = string.Empty; IList pars = new List(); 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()); 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(); 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()); 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(); 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()); 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(); 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()); 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(); 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()); 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; } /// /// 获取管理员当前账户信息 /// /// public PaymentAccountModelByAdmin GetPaymentAccountModelByAdmin(int currentPayId, decimal commission) { PaymentAccountModelByAdmin result = new PaymentAccountModelByAdmin(); string sqlStr = string.Empty; IList pars = new List(); 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()); 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(); 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()); 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(); 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()); 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(); // 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(); 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(); 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(); 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()); 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(); 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(); 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()); 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; } /// /// 计算累计已收佣金 /// /// /// /// /// /// /// /// private decimal GetTotalCommission(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 计算累计已还授信 /// /// /// /// /// /// /// /// private decimal GetTotalReturnCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 计算累计已还交易佣金 /// /// /// /// /// /// /// /// private decimal GetTotalReturnCommission(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 总其他收入（比如广告等） /// /// private decimal GetAllIncomeByorther(string payType, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 计算使用授信 /// /// private decimal GetUseCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 计算累计会员充值 /// /// /// /// /// /// /// /// public decimal GetMemberRecharge(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); 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()); 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; } /// /// 计算累计会员提现 /// /// /// /// /// /// /// private decimal GetWithdrawMoney(string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); DataTable dt = null; SqlParameter par = null; dt = null; pars = new List(); 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()); 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; } /// /// 计算累计会员余额 /// /// /// /// /// /// /// /// public decimal GetSurplusMoney(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); DataTable dt = null; SqlParameter par = null; dt = null; pars = new List(); par = null; decimal surplusMoney = 0; IList payIdList = new List(); dt = null; pars = new List(); 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()); 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(); 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()); 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(); 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()); 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; } /// /// 计算累计授信 /// /// /// /// /// /// /// /// public decimal GetAllCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); DataTable dt = null; SqlParameter par = null; dt = null; pars = new List(); 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()); 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; } /// /// 计算授信余额 /// /// /// /// /// /// /// /// public decimal GetHasCredit(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); DataTable dt = null; SqlParameter par = null; dt = null; pars = new List(); 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()); 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; } /// /// 获取期初授信 /// /// /// /// /// /// /// public decimal GetCreditByBefore(int currentPayId, string startDate, string selectProvince, string selectCity, string selectCounty) { string sqlStr = string.Empty; IList pars = new List(); DataTable dt = null; SqlParameter par = null; dt = null; pars = new List(); 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()); 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; } /// /// 查询管理员的统计信息 /// /// /// /// public PaymentAccountQueryModelByAdmin GetPaymentAccountQueryModelByAdmin(int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty) { PaymentAccountQueryModelByAdmin result = new PaymentAccountQueryModelByAdmin(); string sqlStr = string.Empty; IList pars = new List(); 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(); 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()); 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(); 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()); 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(); 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()); 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(); 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()); 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(); 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()); 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(); 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()); 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(); // 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()); // 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(); 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()); 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; } /// /// 推广更新账户信息 /// /// /// /// /// /// /// /// public bool PayPaymentAccountPromotion(int? keyId, decimal? PromotionMoney, int? Score, decimal? ProxyMoney, decimal? ProxyMoneyCash, decimal? ProxyMoneyAudit) { IList sqlParms = new List() { 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()); return 0 < (int)(sqlParms[6].Value); } catch (Exception ex) { throw ex; } } } }