/// <summary> /// 添加到放款历史 /// </summary> /// <param name="orderNumberStr"></param> /// <returns></returns> private static (bool, string) AddToPaymentHistory(string orderNumberStr, SuperDataBase.Model.DBSandbox db) { string F_LC_Payment = SuperDataBase.Model.TableFieldCache.GetFieldList(typeof(Model.Model.LC_Payment)).Select(x => x.Name).Where((y) => { if (!y.Equals("id", StringComparison.OrdinalIgnoreCase)) { return(true); } return(false); }).ToList().ListToString(); sql = new SuperDataBase.Vo.SqlVO($"INSERT INTO {nameof(Model.Model.LC_Payment_History)} ({F_LC_Payment}) SELECT {F_LC_Payment} FROM {nameof(Model.Model.LC_Payment)} where OrderNumber in ({orderNumberStr})"); ids = db.Exec(sql); if (!ids.flag) { return(false, ids.errormsg); } if (!ids.ExecOk()) { return(false, "更新上缴历史失败!"); } return(true, string.Empty); }
/// <summary> /// 上缴流程 /// </summary> /// <param name="myuservo"></param> /// <param name="orderlist"></param> private static (bool, string) TurnedPayment(UserLoginVO myuservo, List <Model.Model.LC_Payment> paymentlist, SuperDataBase.Model.DBSandbox db) { //检测是否有不是上缴货款的数据 var f = paymentlist.Where(x => x.StartLogisticsUID.Equals(x.LocationLogisticsUID, StringComparison.OrdinalIgnoreCase)).ToList(); if (f.Count > 0) { return(false, "当前操作存在非上缴货款项,操作失败!"); } //更新到上缴人 sql = new SuperDataBase.Vo.SqlVO($@"update a set LocationLogisticsIndex=(select top 1 ID from {nameof(Model.Model.LC_History)} where state in ({GlobalBLL.OrderStateEnum.已发货.EnumToInt()},{OrderStateEnum.已到收货地可提货.EnumToInt()}) and logisticsID=b.beginUID order by id asc) LocationLogisticsUID=b.beginUID , a.LastOperationTime=@LastOperationTime,a.LastOperatorsUID='{myuservo.uid}',a.LastState=0 FROM {nameof(Model.Model.LC_Payment)} a,{nameof(Model.Model.LC_History)} b where a.OrderNumber in ({paymentlist.Select(x => $"'{x.OrderNumber}'").ToList().ListToString()}) and a.OrderNumber=b.OrderID and b.State = {GlobalBLL.OrderStateEnum.订单完成.EnumToInt()} and LocationLogisticsUID=@LocationLogisticsUID;", new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@LocationLogisticsUID", myuservo.uid), new System.Data.SqlClient.SqlParameter("@LastOperationTime", DateTime.Now), //new System.Data.SqlClient.SqlParameter("@PaidTime",DateTime.Now) }); ids = db.Exec(sql); if (!ids.flag) { return(false, ids.errormsg); } if (!ids.ExecOk()) { return(false, "更新邀请人失败!"); } var atp = AddToPaymentHistory(paymentlist.Select(x => $"'{x.OrderNumber}'").ToList().ListToString(), db); if (!atp.Item1) { return(false, atp.Item2); } return(true, string.Empty); }
/// <summary> /// 获取放货数据 /// </summary> /// <param name="myuservo"></param> /// <param name="page"></param> /// <param name="num"></param> /// <returns></returns> public static (bool, string, object) GetLendersList(UserLoginVO myuservo, int page, int num) { //获取订单列表 sql = new SuperDataBase.Vo.SqlVO($@"SELECT a.OrderNumber, b.Phone FROM {nameof(Model.Model.LC_Payment)} a, {nameof(Model.Model.LC_User)} b, {nameof(Model.Model.LC_Customer)} c WHERE a.OrderNumber = c.OrderID AND b.Phone = c.FHPhone AND a.LastState = '1' AND a.StartLogisticsUID = a.LocationLogisticsUID AND a.LocationLogisticsUID = '{myuservo.uid}'"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "啥都没有", new object[] { }); } List <(string ordernumber, string phone)> orderList = new List <(string ordernumber, string phone)>(); for (var i = 0; i < ids.dt.Rows.Count; i++) { var row = ids.dt.Rows[i]; orderList.Add((row[0].ConvertData(), row[1].ConvertData())); } sql = new SuperDataBase.Vo.SqlVO($@" SELECT d.FHPhone, max(d.Consignee) as 'Consignee', max(d.Consignor) as 'Consignor', max(d.DischargeTime) as DischargeTime, COUNT(d.OrderID) as 'num', SUM( d.GReceivables + d.Freight + d.OtherExpenses ) AS 'allamount' FROM {nameof(Model.Model.LC_Customer)} d WHERE OrderID IN({orderList.Select(x=>$"'{x.ordernumber}'").ToList().ListToString()}) GROUP BY d.FHPhone,d.DischargeTime;"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, string.Empty, new object[] { }); } List <object> obj_list = new List <object>(); for (var i = 0; i < ids.dt.Rows.Count; i++) { var v = ids.dt.Rows[i]; obj_list.Add(new { FHPhone = v["FHPhone"], Consignee = v["Consignee"], Consignor = v["Consignor"], num = v["num"], allamount = v["allamount"], OrderList = orderList.Where(x => x.phone.Equals(v["FHPhone"].ConvertData(), StringComparison.OrdinalIgnoreCase)).Select(y => y.ordernumber).ToList(), DischargeTime = v["DischargeTime"] }); } return(true, string.Empty, obj_list); }
public static (bool, string, object) Yesterdaybalance(UserLoginVO myuservo, DateTime fristtime, DateTime lasttime, DateTime fristToday, DateTime lastToday) { //昨日收款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(Total) as 'Yesterdaybalance' from LC_Customer where State=6 and DischargeTime BETWEEN '" + fristtime + "' and '" + lasttime + "' and logisticsID='" + myuservo.uid + "'"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal Yesterdaybalance = ids.dt.Rows[0]["Yesterdaybalance"].ConvertData <decimal>(); //昨日放款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(PaymentAllAmount) as 'Loan' from LC_Payment_History where CreateTime between '" + fristtime + "' and '" + lasttime + "' and LastState=2"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal Loan = ids.dt.Rows[0]["Loan"].ConvertData <decimal>(); //昨日上缴款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(PaymentAllAmount) as 'Paid' from LC_Payment_History where CreateTime between '" + fristtime + "' and '" + lasttime + "' and LastState=0"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal Paid = ids.dt.Rows[0]["Paid"].ConvertData <decimal>(); //今天收款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(Total) as 'CollectionToday' from LC_Customer where State=6 and DischargeTime BETWEEN '" + fristToday + "' and '" + lastToday + "' and logisticsID='" + myuservo.uid + "'"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal CollectionToday = ids.dt.Rows[0]["CollectionToday"].ConvertData <decimal>(); //今天支付 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(Total) as 'PaymentToday' from LC_Customer where State=6 and DischargeTime BETWEEN '" + fristToday + "' and '" + lastToday + "' and logisticsID='" + myuservo.uid + "'"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal PaymentToday = ids.dt.Rows[0]["PaymentToday"].ConvertData <decimal>(); //今天回收代收款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(PaymentAllAmount) as 'RecoveryFund' from LC_Payment_History where CreateTime between '" + fristToday + "' and '" + lastToday + "'and LastState=1"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal RecoveryFund = ids.dt.Rows[0]["RecoveryFund"].ConvertData <decimal>(); //昨天支付 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(Total) as 'PaymentYesterday' from LC_Customer where State=6 and DischargeTime BETWEEN '" + fristtime + "' and '" + lasttime + "' and logisticsID='" + myuservo.uid + "'"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal PaymentYesterday = ids.dt.Rows[0]["PaymentYesterday"].ConvertData <decimal>(); //昨天回收代收款 sql = new SuperDataBase.Vo.SqlVO(@"select Sum(PaymentAllAmount) as 'YesterdayRecoveryFund' from LC_Payment_History where CreateTime between '" + fristtime + "' and '" + lasttime + "'and LastState=1"); ids = db.Read(sql); if (!ids.flag) { return(false, ids.errormsg, null); } if (!ids.ReadIsOk()) { return(true, "没有任何数据!", new object[] { }); } decimal YesterdayRecoveryFund = ids.dt.Rows[0]["YesterdayRecoveryFund"].ConvertData <decimal>(); return(true, string.Empty, new { Yesterdaybalance, //昨天收款 Loan, //昨天付款 Paid, //上缴款 CollectionToday, //今天收款 PaymentToday, //放货款 PaymentYesterday, //昨天放货款 RecoveryFund, //回收款 YesterdayRecoveryFund, //昨天回收款 Yesterdaymoney = Yesterdaybalance - (Loan + Paid), //昨日余额 PayToday = PaymentToday + RecoveryFund, //今天支付 PaymentYesterdays = PaymentYesterday + YesterdayRecoveryFund, //昨天支付 }); }