/// <summary>
/// 得到一个对象实体
/// </summary>
/// <param name="batchnumber">批次号</param>
/// <returns></returns>
public JMP.MDL.jmp_BankPlaymoney GetBankPlaymoneyModel(string batchnumber)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select b_tradestate ");
strSql.Append(" from jmp_BankPlaymoney ");
strSql.Append(" where b_batchnumber=@batchnumber");
SqlParameter[] parameters =
{
new SqlParameter("@batchnumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = batchnumber;
JMP.MDL.jmp_BankPlaymoney model = new JMP.MDL.jmp_BankPlaymoney();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["b_tradestate"].ToString() != "")
{
model.b_tradestate = int.Parse(ds.Tables[0].Rows[0]["b_tradestate"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(JMP.MDL.jmp_BankPlaymoney model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jmp_BankPlaymoney set ");
strSql.Append(" b_payfashion = @b_payfashion , ");
strSql.Append(" b_remark = @b_remark , ");
strSql.Append(" b_payforanotherId = @b_payforanotherId , ");
strSql.Append(" b_batchnumber = @b_batchnumber , ");
strSql.Append(" b_number = @b_number , ");
strSql.Append(" b_tradeno = @b_tradeno , ");
strSql.Append(" b_tradestate = @b_tradestate , ");
strSql.Append(" b_date = @b_date , ");
strSql.Append(" b_bankid = @b_bankid , ");
strSql.Append(" b_money = @b_money , ");
strSql.Append(" b_paydate = @b_paydate ");
strSql.Append(" where b_id=@b_id ");
SqlParameter[] parameters =
{
new SqlParameter("@b_id", SqlDbType.Int, 4),
new SqlParameter("@b_payfashion", SqlDbType.Int, 4),
new SqlParameter("@b_remark", SqlDbType.NVarChar, 1000),
new SqlParameter("@b_payforanotherId", SqlDbType.Int, 4),
new SqlParameter("@b_batchnumber", SqlDbType.NVarChar, 50),
new SqlParameter("@b_number", SqlDbType.NVarChar, 50),
new SqlParameter("@b_tradeno", SqlDbType.NVarChar, 50),
new SqlParameter("@b_tradestate", SqlDbType.Int, 4),
new SqlParameter("@b_date", SqlDbType.DateTime),
new SqlParameter("@b_bankid", SqlDbType.Int, 4),
new SqlParameter("@b_money", SqlDbType.Decimal, 9),
new SqlParameter("@b_paydate", SqlDbType.DateTime)
};
parameters[0].Value = model.b_id;
parameters[1].Value = model.b_payfashion;
parameters[2].Value = model.b_remark;
parameters[3].Value = model.b_payforanotherId;
parameters[4].Value = model.b_batchnumber;
parameters[5].Value = model.b_number;
parameters[6].Value = model.b_tradeno;
parameters[7].Value = model.b_tradestate;
parameters[8].Value = model.b_date;
parameters[9].Value = model.b_bankid;
parameters[10].Value = model.b_money;
parameters[11].Value = model.b_paydate;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <JMP.MDL.jmp_BankPlaymoney> DataTableToList(DataTable dt)
{
List <JMP.MDL.jmp_BankPlaymoney> modelList = new List <JMP.MDL.jmp_BankPlaymoney>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
JMP.MDL.jmp_BankPlaymoney model;
for (int n = 0; n < rowsCount; n++)
{
model = new JMP.MDL.jmp_BankPlaymoney();
if (dt.Rows[n]["b_id"].ToString() != "")
{
model.b_id = int.Parse(dt.Rows[n]["b_id"].ToString());
}
if (dt.Rows[n]["b_payfashion"].ToString() != "")
{
model.b_payfashion = int.Parse(dt.Rows[n]["b_payfashion"].ToString());
}
model.b_remark = dt.Rows[n]["b_remark"].ToString();
if (dt.Rows[n]["b_payforanotherId"].ToString() != "")
{
model.b_payforanotherId = int.Parse(dt.Rows[n]["b_payforanotherId"].ToString());
}
model.b_batchnumber = dt.Rows[n]["b_batchnumber"].ToString();
model.b_number = dt.Rows[n]["b_number"].ToString();
model.b_tradeno = dt.Rows[n]["b_tradeno"].ToString();
if (dt.Rows[n]["b_tradestate"].ToString() != "")
{
model.b_tradestate = int.Parse(dt.Rows[n]["b_tradestate"].ToString());
}
if (dt.Rows[n]["b_date"].ToString() != "")
{
model.b_date = DateTime.Parse(dt.Rows[n]["b_date"].ToString());
}
if (dt.Rows[n]["b_bankid"].ToString() != "")
{
model.b_bankid = int.Parse(dt.Rows[n]["b_bankid"].ToString());
}
if (dt.Rows[n]["b_money"].ToString() != "")
{
model.b_money = decimal.Parse(dt.Rows[n]["b_money"].ToString());
}
if (dt.Rows[n]["b_paydate"].ToString() != "")
{
model.b_paydate = DateTime.Parse(dt.Rows[n]["b_paydate"].ToString());
}
modelList.Add(model);
}
}
return(modelList);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(JMP.MDL.jmp_BankPlaymoney model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_BankPlaymoney(");
strSql.Append("b_payfashion,b_remark,b_payforanotherId,b_batchnumber,b_number,b_tradeno,b_tradestate,b_date,b_bankid,b_money,b_paydate");
strSql.Append(") values (");
strSql.Append("@b_payfashion,@b_remark,@b_payforanotherId,@b_batchnumber,@b_number,@b_tradeno,@b_tradestate,@b_date,@b_bankid,@b_money,@b_paydate");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@b_payfashion", SqlDbType.Int, 4),
new SqlParameter("@b_remark", SqlDbType.NVarChar, 1000),
new SqlParameter("@b_payforanotherId", SqlDbType.Int, 4),
new SqlParameter("@b_batchnumber", SqlDbType.NVarChar, 50),
new SqlParameter("@b_number", SqlDbType.NVarChar, 50),
new SqlParameter("@b_tradeno", SqlDbType.NVarChar, 50),
new SqlParameter("@b_tradestate", SqlDbType.Int, 4),
new SqlParameter("@b_date", SqlDbType.DateTime),
new SqlParameter("@b_bankid", SqlDbType.Int, 4),
new SqlParameter("@b_money", SqlDbType.Decimal, 9),
new SqlParameter("@b_paydate", SqlDbType.DateTime)
};
parameters[0].Value = model.b_payfashion;
parameters[1].Value = model.b_remark;
parameters[2].Value = model.b_payforanotherId;
parameters[3].Value = model.b_batchnumber;
parameters[4].Value = model.b_number;
parameters[5].Value = model.b_tradeno;
parameters[6].Value = model.b_tradestate;
parameters[7].Value = model.b_date;
parameters[8].Value = model.b_bankid;
parameters[9].Value = model.b_money;
parameters[10].Value = model.b_paydate;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 提现信息列表展示
/// </summary>
/// <returns></returns>
public ActionResult PaymentList()
{
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 types = string.IsNullOrEmpty(Request["s_type"]) ? "1" : Request["s_type"];
string searchKey = string.IsNullOrEmpty(Request["s_key"]) ? "" : Request["s_key"];
string stime = string.IsNullOrEmpty(Request["s_begin"]) ? DateTime.Now.ToString("yyyy-MM-dd") : Request["s_begin"];
string etime = string.IsNullOrEmpty(Request["s_end"]) ? DateTime.Now.ToString("yyyy-MM-dd") : Request["s_end"];
string b_tradestate = string.IsNullOrEmpty(Request["check"]) ? "" : Request["check"]; //交易状态
string payfashion = string.IsNullOrEmpty(Request["payfashion"]) ? "" : Request["payfashion"]; //付款方式
#region 组装查询语句
string where = "where c.u_id=" + UserInfo.UserId + " ";
string orderby = " order by b_id desc";
if (!string.IsNullOrEmpty(types.ToString()))
{
if (!string.IsNullOrEmpty(searchKey))
{
if (types == "0")
{
where += string.Format(" and b_batchnumber like '%{0}%'", searchKey);
}
else if (types == "1")
{
where += string.Format(" and b_number like '%{0}%'", searchKey);
}
else if (types == "2")
{
where += string.Format(" and b_tradeno like '%{0}%'", searchKey);
}
else if (types == "3")
{
where += string.Format(" and b.u_name like '%{0}%'", searchKey);
}
}
}
if (!string.IsNullOrEmpty(stime) && !string.IsNullOrEmpty(etime))
{
where += " and b_date >='" + stime + " 00:00:00' and b_date<='" + etime + " 23:59:59' ";
}
if (!string.IsNullOrEmpty(b_tradestate))
{
where += string.Format(" and a.b_tradestate={0}", b_tradestate);
}
if (!string.IsNullOrEmpty(payfashion))
{
where += string.Format(" and a.b_payfashion={0}", payfashion);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"select * from
(select a.*,b.u_bankname,b.u_banknumber,b.u_name ,c.u_realname,c.u_phone from jmp_BankPlaymoney a left join jmp_userbank b on a.b_bankid=b.u_id left join jmp_user c on b.u_userid=c.u_id {0} ) a
left join (select p_state,p_batchnumber from jmp_pays group by p_batchnumber,p_state) b on a.b_batchnumber=b.p_batchnumber
", where);
JMP.BLL.jmp_BankPlaymoney bankpbll = new JMP.BLL.jmp_BankPlaymoney();
List <JMP.MDL.jmp_BankPlaymoney> bankplist = new List <JMP.MDL.jmp_BankPlaymoney>();
bankplist = bankpbll.GetLists(sql.ToString(), orderby, pageIndexs, PageSize, out pageCount);
#endregion
#region 合计组装查询语句
JMP.MDL.jmp_BankPlaymoney model = new JMP.MDL.jmp_BankPlaymoney();
string countsql = string.Format(@"select SUM(b_money) as b_money from
(select a.*,b.u_bankname,b.u_banknumber,b.u_name ,c.u_realname,c.u_phone from jmp_BankPlaymoney a left join jmp_userbank b on a.b_bankid=b.u_id left join jmp_user c on b.u_userid=c.u_id {0} ) a
left join (select p_state,p_batchnumber from jmp_pays group by p_batchnumber,p_state) b on a.b_batchnumber=b.p_batchnumber
", where);
if (bankplist.Count > 0)
{
DataTable dt = bankpbll.SelectSQL(countsql);
model = dt.Rows.Count > 0 ? JMP.TOOL.MdlList.ToModel <JMP.MDL.jmp_BankPlaymoney>(dt) : new JMP.MDL.jmp_BankPlaymoney();
}
#endregion
StringBuilder sqlinfo = new StringBuilder();
sqlinfo.AppendFormat(@";WITH a AS(
select a.*,b.SettlementDay
from [dbo].[jmp_pays] a
left join [dx_total].[dbo].[CoSettlementDeveloperOverview] b on a.p_bill_id=b.Id and a.p_userid={0}
), b as(
select a.Id,(TotalAmount-ServiceFee-PortFee) as KFZIncome,
isnull(SUM(b.p_money),0) as p_moneys
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 and b.p_userid={0} group by a.Id,TotalAmount,ServiceFee,PortFee
)
select * from a
left join b on a.p_bill_id=b.Id", UserInfo.UserId);
JMP.BLL.jmp_pays bllinfo = new JMP.BLL.jmp_pays();
List <JMP.MDL.jmp_pays> listinfo = new List <JMP.MDL.jmp_pays>();
DataTable dts = bllinfo.SelectList(sqlinfo.ToString());
listinfo = JMP.TOOL.MdlList.ToList <JMP.MDL.jmp_pays>(dts);
ViewBag.colist = listinfo;
ViewBag.show_fields = types;
ViewBag.pageIndexs = pageIndexs;
ViewBag.PageSize = PageSize;
ViewBag.pageCount = pageCount;
ViewBag.list = bankplist;
ViewBag.model = model;
ViewBag.scheck = b_tradestate;
ViewBag.stime = stime;
ViewBag.etime = etime;
ViewBag.payfashion = payfashion;
ViewBag.searchname = searchKey;
return(View());
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(JMP.MDL.jmp_BankPlaymoney model)
{
return(dal.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(JMP.MDL.jmp_BankPlaymoney model)
{
return(dal.Add(model));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public JMP.MDL.jmp_BankPlaymoney GetModel(int b_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select b_id, b_payfashion, b_remark, b_payforanotherId, b_batchnumber, b_number, b_tradeno, b_tradestate, b_date, b_bankid, b_money, b_paydate ");
strSql.Append(" from jmp_BankPlaymoney ");
strSql.Append(" where b_id=@b_id");
SqlParameter[] parameters =
{
new SqlParameter("@b_id", SqlDbType.Int, 4)
};
parameters[0].Value = b_id;
JMP.MDL.jmp_BankPlaymoney model = new JMP.MDL.jmp_BankPlaymoney();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["b_id"].ToString() != "")
{
model.b_id = int.Parse(ds.Tables[0].Rows[0]["b_id"].ToString());
}
if (ds.Tables[0].Rows[0]["b_payfashion"].ToString() != "")
{
model.b_payfashion = int.Parse(ds.Tables[0].Rows[0]["b_payfashion"].ToString());
}
model.b_remark = ds.Tables[0].Rows[0]["b_remark"].ToString();
if (ds.Tables[0].Rows[0]["b_payforanotherId"].ToString() != "")
{
model.b_payforanotherId = int.Parse(ds.Tables[0].Rows[0]["b_payforanotherId"].ToString());
}
model.b_batchnumber = ds.Tables[0].Rows[0]["b_batchnumber"].ToString();
model.b_number = ds.Tables[0].Rows[0]["b_number"].ToString();
model.b_tradeno = ds.Tables[0].Rows[0]["b_tradeno"].ToString();
if (ds.Tables[0].Rows[0]["b_tradestate"].ToString() != "")
{
model.b_tradestate = int.Parse(ds.Tables[0].Rows[0]["b_tradestate"].ToString());
}
if (ds.Tables[0].Rows[0]["b_date"].ToString() != "")
{
model.b_date = DateTime.Parse(ds.Tables[0].Rows[0]["b_date"].ToString());
}
if (ds.Tables[0].Rows[0]["b_bankid"].ToString() != "")
{
model.b_bankid = int.Parse(ds.Tables[0].Rows[0]["b_bankid"].ToString());
}
if (ds.Tables[0].Rows[0]["b_money"].ToString() != "")
{
model.b_money = decimal.Parse(ds.Tables[0].Rows[0]["b_money"].ToString());
}
if (ds.Tables[0].Rows[0]["b_paydate"].ToString() != "")
{
model.b_paydate = DateTime.Parse(ds.Tables[0].Rows[0]["b_paydate"].ToString());
}
return(model);
}
else
{
return(null);
}
}
