/// <summary> /// 增加一条数据 /// </summary> public int Add(JMP.MDL.CoSettlementDeveloperOverview model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into CoSettlementDeveloperOverview("); strSql.Append("BpPushMoneyRatio,AgentPushMoney,AgentPushMoneyRatio,PortFee,CostFee,DeveloperId,DeveloperName,SettlementDay,CreatedOn,TotalAmount,ServiceFee,ServiceFeeRatio,BpPushMoney"); strSql.Append(") values ("); strSql.Append("@BpPushMoneyRatio,@AgentPushMoney,@AgentPushMoneyRatio,@PortFee,@CostFee,@DeveloperId,@DeveloperName,@SettlementDay,@CreatedOn,@TotalAmount,@ServiceFee,@ServiceFeeRatio,@BpPushMoney"); strSql.Append(") "); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@BpPushMoneyRatio", SqlDbType.Decimal, 5), new SqlParameter("@AgentPushMoney", SqlDbType.Decimal, 9), new SqlParameter("@AgentPushMoneyRatio", SqlDbType.Decimal, 5), new SqlParameter("@PortFee", SqlDbType.Decimal, 9), new SqlParameter("@CostFee", SqlDbType.Decimal, 9), new SqlParameter("@DeveloperId", SqlDbType.Int, 4), new SqlParameter("@DeveloperName", SqlDbType.NVarChar, 50), new SqlParameter("@SettlementDay", SqlDbType.DateTime), new SqlParameter("@CreatedOn", SqlDbType.DateTime), new SqlParameter("@TotalAmount", SqlDbType.Decimal, 9), new SqlParameter("@ServiceFee", SqlDbType.Decimal, 9), new SqlParameter("@ServiceFeeRatio", SqlDbType.Decimal, 5), new SqlParameter("@BpPushMoney", SqlDbType.Decimal, 9) }; parameters[0].Value = model.BpPushMoneyRatio; parameters[1].Value = model.AgentPushMoney; parameters[2].Value = model.AgentPushMoneyRatio; parameters[3].Value = model.PortFee; parameters[4].Value = model.CostFee; parameters[5].Value = model.DeveloperId; parameters[6].Value = model.DeveloperName; parameters[7].Value = model.SettlementDay; parameters[8].Value = model.CreatedOn; parameters[9].Value = model.TotalAmount; parameters[10].Value = model.ServiceFee; parameters[11].Value = model.ServiceFeeRatio; parameters[12].Value = model.BpPushMoney; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 账单管理 /// </summary> /// <returns></returns> public ActionResult BillList() { #region 获取用户实名认证状态信息 JMP.BLL.jmp_user sm_bll = new JMP.BLL.jmp_user(); JMP.MDL.jmp_user sm_model = new JMP.MDL.jmp_user(); int u_ids = UserInfo.Uid; //查询登录信息 sm_model = sm_bll.GetModel(u_ids); ViewBag.auditstate = sm_model.u_auditstate; ViewBag.linkEmail = sm_model.u_email; #endregion int pageCount = 0; int pageIndexs = string.IsNullOrEmpty(Request["curr"]) ? 1 : Int32.Parse(Request["curr"]); //当前页 int PageSize = string.IsNullOrEmpty(Request["psize"]) ? 20 : Int32.Parse(Request["psize"]); //每页显示数量 string stime = string.IsNullOrEmpty(Request["stime"]) ? DateTime.Now.AddDays(-8).ToString("yyyy-MM-dd") : Request["stime"]; //开始时间 string etime = string.IsNullOrEmpty(Request["etime"]) ? DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") : Request["etime"]; //结束时间 //首页跳转标识 int num = string.IsNullOrEmpty(Request["start"]) ? -1 : int.Parse(Request["start"]); switch (num) { case 2: stime = DateTime.Now.ToString("yyyy-MM-01"); etime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"); break; case 3: stime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-01"); etime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1).ToString("yyyy-MM-dd"); break; } #region 组装查询语句 string where = "where DeveloperId='" + UserInfo.UserId + "'"; if (!string.IsNullOrEmpty(stime) && !string.IsNullOrEmpty(etime)) { where += " and SettlementDay >='" + stime + "' and SettlementDay<='" + etime + "' "; } string orderby = "order by SettlementDay desc"; string sql = string.Format(@"select a.Id,DeveloperId,CreatedOn,SettlementDay,TotalAmount,ServiceFee,PortFee,(TotalAmount-ServiceFee-PortFee) as KFZIncome,isnull(SUM(b.p_money),0) as p_money,ISNULL((OriginalTotalAmount-TotalAmount),0.0000) AS RefundAmount from dx_total.dbo.[CoSettlementDeveloperOverview] as a left join (select * from dx_base.dbo.jmp_pays a,dx_base.dbo.jmp_BankPlaymoney b where a.p_batchnumber=b.b_batchnumber and a.p_state!=-1 and b.b_tradestate!=4 ) as b on a.Id=b.p_bill_id {0} group by a.Id,DeveloperId,CreatedOn,SettlementDay,TotalAmount,ServiceFee,PortFee,OriginalTotalAmount", where); List <JMP.MDL.CoSettlementDeveloperOverview> list = new List <JMP.MDL.CoSettlementDeveloperOverview>(); JMP.MDL.CoSettlementDeveloperOverview model = new JMP.MDL.CoSettlementDeveloperOverview(); list = bll_CoSDO.GetLists(sql, orderby, pageIndexs, PageSize, out pageCount); #endregion #region 合计组装查询语句 string countsql = string.Format(@"select ISNULL(SUM(TotalAmount),0) as TotalAmount,isnull(SUM(ServiceFee),0) as ServiceFee,isnull(SUM(PortFee),0) as PortFee, isnull(SUM(TotalAmount)-SUM(ServiceFee)-SUM(PortFee),0) as KFZIncome,ISNULL(SUM(p_money),0) as p_money, ISNULL(SUM(OriginalTotalAmount)-SUM(TotalAmount),0) as RefundAmount from ( select a.Id,a.SettlementDay,TotalAmount,ServiceFee,PortFee,OriginalTotalAmount,ISNULL(SUM(b.p_money),0) as p_money from dx_total.dbo.CoSettlementDeveloperOverview as a left join (select * from dx_base.dbo.jmp_pays a,dx_base.dbo.jmp_BankPlaymoney b where a.p_batchnumber=b.b_batchnumber and a.p_state!=-1 and b.b_tradestate!=4 ) as b on a.Id=b.p_bill_id {0} group by a.Id,a.SettlementDay,TotalAmount,ServiceFee,PortFee,OriginalTotalAmount ) a", where); if (list.Count > 0) { DataTable dt = bll_CoSDO.SelectSum(countsql); model = dt.Rows.Count > 0 ? JMP.TOOL.MdlList.ToModel <JMP.MDL.CoSettlementDeveloperOverview>(dt) : new JMP.MDL.CoSettlementDeveloperOverview(); } #endregion ViewBag.pageIndexs = pageIndexs; ViewBag.PageSize = PageSize; ViewBag.pageCount = pageCount; ViewBag.stime = stime; ViewBag.etime = etime; ViewBag.list = list; ViewBag.model = model; return(View()); }
public ActionResult pays_single() { JMP.MDL.CoSettlementDeveloperOverview comodeT1 = new JMP.MDL.CoSettlementDeveloperOverview(); JMP.MDL.CoSettlementDeveloperOverview comodeT2 = new JMP.MDL.CoSettlementDeveloperOverview(); //开发者选中的账单ID string payid = string.IsNullOrEmpty(Request["payid"]) ? "" : Request["payid"]; ViewBag.payid = payid; string sqlstr = string.Format(@";WITH O AS( select * from CoSettlementDeveloperOverview where id in({0}) and DeveloperId={1} ),bll as( select p_bill_id,isnull(SUM(p_money),0) as p_money from dx_base.dbo.jmp_pays a, dx_base.dbo.jmp_BankPlaymoney b where a.p_batchnumber=b.b_batchnumber and a.p_state!=-1 and b.b_tradestate!=4 and p_bill_id in({0}) and p_userid={1} group by p_bill_id ) select (isnull(SUM(TotalAmount),0)-isnull(SUM(ServiceFee),0)-isnull(SUM(PortFee),0)-isnull(SUM(bll.p_money),0)) as ketiMoney from O left join bll on bll.p_bill_id=O.Id", payid, UserInfo.UserId); //查询选中数据 DataTable dt = bll_CoSDO.SelectSum(sqlstr); comodeT1 = dt.Rows.Count > 0 ? JMP.TOOL.MdlList.ToModel <JMP.MDL.CoSettlementDeveloperOverview>(dt) : new JMP.MDL.CoSettlementDeveloperOverview(); ViewBag.comodeT1 = comodeT1; //开发者账单总金额与冻结金额 string sqlstrSum = string.Format(@"; WITH O AS( select * from dx_total.dbo.CoSettlementDeveloperOverview where DeveloperId={0} ),bll as( select p_bill_id,isnull(SUM(p_money),0) as p_money from dx_base.dbo.jmp_pays a, dx_base.dbo.jmp_BankPlaymoney b where a.p_batchnumber=b.b_batchnumber and a.p_state!=-1 and b.b_tradestate!=4 and p_userid={0} group by p_bill_id ),users as ( select u_id,ISNULL(FrozenMoney,0) as FrozenMoney from dx_base.dbo.jmp_user where u_id={0} ),kt as ( select DeveloperId, (isnull(SUM(TotalAmount),0)-isnull(SUM(ServiceFee),0)-isnull(SUM(PortFee),0)-isnull(SUM(bll.p_money),0)) as ketiMoney from O left join bll on bll.p_bill_id=O.Id group by DeveloperId ) select FrozenMoney,ketiMoney from users inner join kt on users.u_id=kt.DeveloperId", UserInfo.UserId); DataTable dt2 = bll_CoSDO.SelectSum(sqlstrSum); comodeT2 = dt2.Rows.Count > 0 ? JMP.TOOL.MdlList.ToModel <JMP.MDL.CoSettlementDeveloperOverview>(dt2) : new JMP.MDL.CoSettlementDeveloperOverview(); ViewBag.comodeT2 = comodeT2; JMP.BLL.jmp_userbank ubkBll = new JMP.BLL.jmp_userbank(); List <JMP.MDL.jmp_userbank> ubklist = new List <JMP.MDL.jmp_userbank>(); //查询开发者绑定的银行卡信息(审核通过并未被冻结的) ubklist = ubkBll.GetModelList("u_userid=" + UserInfo.UserId + " and u_state=1 and u_freeze=0"); ViewBag.ubklist = ubklist; return(View()); }
/// <summary> /// 获得数据列表 /// </summary> public List <JMP.MDL.CoSettlementDeveloperOverview> DataTableToList(DataTable dt) { List <JMP.MDL.CoSettlementDeveloperOverview> modelList = new List <JMP.MDL.CoSettlementDeveloperOverview>(); int rowsCount = dt.Rows.Count; if (rowsCount > 0) { JMP.MDL.CoSettlementDeveloperOverview model; for (int n = 0; n < rowsCount; n++) { model = new JMP.MDL.CoSettlementDeveloperOverview(); if (dt.Rows[n]["Id"].ToString() != "") { model.Id = int.Parse(dt.Rows[n]["Id"].ToString()); } if (dt.Rows[n]["BpPushMoneyRatio"].ToString() != "") { model.BpPushMoneyRatio = decimal.Parse(dt.Rows[n]["BpPushMoneyRatio"].ToString()); } if (dt.Rows[n]["AgentPushMoney"].ToString() != "") { model.AgentPushMoney = decimal.Parse(dt.Rows[n]["AgentPushMoney"].ToString()); } if (dt.Rows[n]["AgentPushMoneyRatio"].ToString() != "") { model.AgentPushMoneyRatio = decimal.Parse(dt.Rows[n]["AgentPushMoneyRatio"].ToString()); } if (dt.Rows[n]["PortFee"].ToString() != "") { model.PortFee = decimal.Parse(dt.Rows[n]["PortFee"].ToString()); } if (dt.Rows[n]["CostFee"].ToString() != "") { model.CostFee = decimal.Parse(dt.Rows[n]["CostFee"].ToString()); } if (dt.Rows[n]["DeveloperId"].ToString() != "") { model.DeveloperId = int.Parse(dt.Rows[n]["DeveloperId"].ToString()); } model.DeveloperName = dt.Rows[n]["DeveloperName"].ToString(); if (dt.Rows[n]["SettlementDay"].ToString() != "") { model.SettlementDay = DateTime.Parse(dt.Rows[n]["SettlementDay"].ToString()); } if (dt.Rows[n]["CreatedOn"].ToString() != "") { model.CreatedOn = DateTime.Parse(dt.Rows[n]["CreatedOn"].ToString()); } if (dt.Rows[n]["TotalAmount"].ToString() != "") { model.TotalAmount = decimal.Parse(dt.Rows[n]["TotalAmount"].ToString()); } if (dt.Rows[n]["ServiceFee"].ToString() != "") { model.ServiceFee = decimal.Parse(dt.Rows[n]["ServiceFee"].ToString()); } if (dt.Rows[n]["ServiceFeeRatio"].ToString() != "") { model.ServiceFeeRatio = decimal.Parse(dt.Rows[n]["ServiceFeeRatio"].ToString()); } if (dt.Rows[n]["BpPushMoney"].ToString() != "") { model.BpPushMoney = decimal.Parse(dt.Rows[n]["BpPushMoney"].ToString()); } modelList.Add(model); } } return(modelList); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(JMP.MDL.CoSettlementDeveloperOverview model) { return(dal.Update(model)); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(JMP.MDL.CoSettlementDeveloperOverview model) { return(dal.Add(model)); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(JMP.MDL.CoSettlementDeveloperOverview model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update CoSettlementDeveloperOverview set "); strSql.Append(" BpPushMoneyRatio = @BpPushMoneyRatio , "); strSql.Append(" AgentPushMoney = @AgentPushMoney , "); strSql.Append(" AgentPushMoneyRatio = @AgentPushMoneyRatio , "); strSql.Append(" PortFee = @PortFee , "); strSql.Append(" CostFee = @CostFee , "); strSql.Append(" DeveloperId = @DeveloperId , "); strSql.Append(" DeveloperName = @DeveloperName , "); strSql.Append(" SettlementDay = @SettlementDay , "); strSql.Append(" CreatedOn = @CreatedOn , "); strSql.Append(" TotalAmount = @TotalAmount , "); strSql.Append(" ServiceFee = @ServiceFee , "); strSql.Append(" ServiceFeeRatio = @ServiceFeeRatio , "); strSql.Append(" BpPushMoney = @BpPushMoney "); strSql.Append(" where Id=@Id "); SqlParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int, 4), new SqlParameter("@BpPushMoneyRatio", SqlDbType.Decimal, 5), new SqlParameter("@AgentPushMoney", SqlDbType.Decimal, 9), new SqlParameter("@AgentPushMoneyRatio", SqlDbType.Decimal, 5), new SqlParameter("@PortFee", SqlDbType.Decimal, 9), new SqlParameter("@CostFee", SqlDbType.Decimal, 9), new SqlParameter("@DeveloperId", SqlDbType.Int, 4), new SqlParameter("@DeveloperName", SqlDbType.NVarChar, 50), new SqlParameter("@SettlementDay", SqlDbType.DateTime), new SqlParameter("@CreatedOn", SqlDbType.DateTime), new SqlParameter("@TotalAmount", SqlDbType.Decimal, 9), new SqlParameter("@ServiceFee", SqlDbType.Decimal, 9), new SqlParameter("@ServiceFeeRatio", SqlDbType.Decimal, 5), new SqlParameter("@BpPushMoney", SqlDbType.Decimal, 9) }; parameters[0].Value = model.Id; parameters[1].Value = model.BpPushMoneyRatio; parameters[2].Value = model.AgentPushMoney; parameters[3].Value = model.AgentPushMoneyRatio; parameters[4].Value = model.PortFee; parameters[5].Value = model.CostFee; parameters[6].Value = model.DeveloperId; parameters[7].Value = model.DeveloperName; parameters[8].Value = model.SettlementDay; parameters[9].Value = model.CreatedOn; parameters[10].Value = model.TotalAmount; parameters[11].Value = model.ServiceFee; parameters[12].Value = model.ServiceFeeRatio; parameters[13].Value = model.BpPushMoney; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/// <summary> /// 得到一个对象实体 /// </summary> public JMP.MDL.CoSettlementDeveloperOverview GetModel(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id, BpPushMoneyRatio, AgentPushMoney, AgentPushMoneyRatio, PortFee, CostFee, DeveloperId, DeveloperName, SettlementDay, CreatedOn, TotalAmount, ServiceFee, ServiceFeeRatio, BpPushMoney "); strSql.Append(" from CoSettlementDeveloperOverview "); strSql.Append(" where Id=@Id"); SqlParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int, 4) }; parameters[0].Value = Id; JMP.MDL.CoSettlementDeveloperOverview model = new JMP.MDL.CoSettlementDeveloperOverview(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["Id"].ToString() != "") { model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString()); } if (ds.Tables[0].Rows[0]["BpPushMoneyRatio"].ToString() != "") { model.BpPushMoneyRatio = decimal.Parse(ds.Tables[0].Rows[0]["BpPushMoneyRatio"].ToString()); } if (ds.Tables[0].Rows[0]["AgentPushMoney"].ToString() != "") { model.AgentPushMoney = decimal.Parse(ds.Tables[0].Rows[0]["AgentPushMoney"].ToString()); } if (ds.Tables[0].Rows[0]["AgentPushMoneyRatio"].ToString() != "") { model.AgentPushMoneyRatio = decimal.Parse(ds.Tables[0].Rows[0]["AgentPushMoneyRatio"].ToString()); } if (ds.Tables[0].Rows[0]["PortFee"].ToString() != "") { model.PortFee = decimal.Parse(ds.Tables[0].Rows[0]["PortFee"].ToString()); } if (ds.Tables[0].Rows[0]["CostFee"].ToString() != "") { model.CostFee = decimal.Parse(ds.Tables[0].Rows[0]["CostFee"].ToString()); } if (ds.Tables[0].Rows[0]["DeveloperId"].ToString() != "") { model.DeveloperId = int.Parse(ds.Tables[0].Rows[0]["DeveloperId"].ToString()); } model.DeveloperName = ds.Tables[0].Rows[0]["DeveloperName"].ToString(); if (ds.Tables[0].Rows[0]["SettlementDay"].ToString() != "") { model.SettlementDay = DateTime.Parse(ds.Tables[0].Rows[0]["SettlementDay"].ToString()); } if (ds.Tables[0].Rows[0]["CreatedOn"].ToString() != "") { model.CreatedOn = DateTime.Parse(ds.Tables[0].Rows[0]["CreatedOn"].ToString()); } if (ds.Tables[0].Rows[0]["TotalAmount"].ToString() != "") { model.TotalAmount = decimal.Parse(ds.Tables[0].Rows[0]["TotalAmount"].ToString()); } if (ds.Tables[0].Rows[0]["ServiceFee"].ToString() != "") { model.ServiceFee = decimal.Parse(ds.Tables[0].Rows[0]["ServiceFee"].ToString()); } if (ds.Tables[0].Rows[0]["ServiceFeeRatio"].ToString() != "") { model.ServiceFeeRatio = decimal.Parse(ds.Tables[0].Rows[0]["ServiceFeeRatio"].ToString()); } if (ds.Tables[0].Rows[0]["BpPushMoney"].ToString() != "") { model.BpPushMoney = decimal.Parse(ds.Tables[0].Rows[0]["BpPushMoney"].ToString()); } return(model); } else { return(null); } }