/// <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, //昨天支付
});
}
