/// <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);
}
}