Beispiel #1
0
        /// <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));
            }
        }
Beispiel #2
0
        /// <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());
        }
Beispiel #3
0
        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());
        }
Beispiel #4
0
        /// <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);
        }
Beispiel #5
0
 /// <summary>
 /// 更新一条数据
 /// </summary>
 public bool Update(JMP.MDL.CoSettlementDeveloperOverview model)
 {
     return(dal.Update(model));
 }
Beispiel #6
0
 /// <summary>
 /// 增加一条数据
 /// </summary>
 public int Add(JMP.MDL.CoSettlementDeveloperOverview model)
 {
     return(dal.Add(model));
 }
Beispiel #7
0
        /// <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);
            }
        }
Beispiel #8
0
        /// <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);
            }
        }