public DataTable TransferMonthly(string centerCode, string start, string end)
{
DataTable serDt = null;
try
{
StringBuilder sql = new StringBuilder();
O_CustomerOrder o_CustomerOrder = new O_CustomerOrder()
{
CenterCodeIn = centerCode
};
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T1.PayDate, 120) AS [PayDate] ");
sql.AppendLine(",SUM(ISNULL(T1.UseAccount, 0) + ISNULL(T1.UseAlipayAmount, 0)) TotalCharge ");
sql.AppendLine("FROM O_CustomerOrder T1 ");
sql.AppendLine("LEFT JOIN B_BussCenter T2 ");
sql.AppendLine("ON T1.CenterCode = T2.CenterCode ");
sql.AppendLine($"WHERE T1.PayDate >= '{start}' ");
sql.AppendLine($"AND T1.PayDate < '{end}' ");
sql.AppendLine("AND T1.PayState = 1 ");
sql.AppendLine("AND T1.OrderState = 0 ");
sql.AppendLine("AND T1.OrderType IN ( 0, 3, 4 ) ");
sql.AppendLine("AND T1.UserCode NOT IN ( 'CCCCC', 'BBBBB', 'DDDDD', 'HHHHH', 'RJFSU', ");
sql.AppendLine("'YYYYY', 'APWHW', 'BUUBXA' ) ");
sql.AppendLine("*[T1]*{0} ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T1.PayDate, 120) ");
sql.AppendLine("ORDER BY PayDate; ");
serDt = ServiceConfig.GetInstance().GetOperation().CusQuery(sql.ToString(), o_CustomerOrder);
}
catch (Exception ex)
{
throw ex;
}
return(serDt);
}
public DataTable GenerationBuyMonthly(string currency, string start, string end)
{
DataTable serDT = null;
try
{
StringBuilder sql = new StringBuilder();
O_CustomerOrder o_CustomerOrder = new O_CustomerOrder()
{
CurrencyIn = currency
};
sql.AppendLine("SELECT * ");
sql.AppendLine("FROM ( ");
sql.AppendLine("SELECT '总数' AS Currency ");
sql.AppendLine(",T1.Dates ");
sql.AppendLine(",T1.TotalCharge - ISNULL(T2.ChargeAmt, 0) ");
sql.AppendLine("- ISNULL(T3.AlipayFee, 0) TotalPurchaseCharge ");
sql.AppendLine("FROM ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T1.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(T1.TotalCharge, 0)) TotalCharge ");
sql.AppendLine("FROM O_CustomerOrder T1 ");
sql.AppendLine("LEFT JOIN O_PurchaseOrder T2 ");
sql.AppendLine("ON T1.PayOrderCode = T2.PayOrderCode ");
sql.AppendLine($"WHERE T1.PayDate >= '{start}' ");
sql.AppendLine($"AND T1.PayDate < '{end}' ");
sql.AppendLine("AND T1.OrderType = 6 ");
sql.AppendLine("AND T1.PayState = 1 ");
sql.AppendLine("AND T2.DelState = 0 ");
sql.AppendLine("AND T2.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T1.PayDate, 120) ");
sql.AppendLine(") T1 ");
sql.AppendLine("LEFT JOIN ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T2.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(T1.ReturnFee, 0)) ChargeAmt ");
sql.AppendLine("FROM O_PurchaseOrder T2 ");
sql.AppendLine("LEFT JOIN O_PurchaseFeeReturn T1 ");
sql.AppendLine("ON T1.PurchaseOrderCode = T2.PurchaseOrderCode ");
sql.AppendLine($"WHERE T2.PayDate >= '{start}' ");
sql.AppendLine($"AND T2.PayDate < '{end}' ");
sql.AppendLine("AND T1.DelState = 0 ");
sql.AppendLine("AND T1.AllowReturn = 1 ");
sql.AppendLine("AND T1.ReturnState = 1 ");
sql.AppendLine("AND T2.DelState = 0 ");
sql.AppendLine("AND T2.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T2.PayDate, 120) ");
sql.AppendLine(") T2 ");
sql.AppendLine("ON T1.Dates = T2.Dates ");
sql.AppendLine(" ");
sql.AppendLine("LEFT JOIN ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T2.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(AccountFee, 0) + ISNULL(AlipayFee, ");
sql.AppendLine("0)) AlipayFee ");
sql.AppendLine("FROM O_PurchaseOrder T2 ");
sql.AppendLine("LEFT JOIN O_FeeReturn T1 on T2.PayOrderCode=T1.BindCode ");
sql.AppendLine($"WHERE T2.PayDate >= '{start}' ");
sql.AppendLine($"AND T2.PayDate < '{end}' ");
sql.AppendLine("AND T1.PayState = 1 ");
sql.AppendLine("AND T1.FeeReturnType = 1 ");
sql.AppendLine("AND T1.Remark LIKE '采购订单%' ");
sql.AppendLine("AND T2.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T2.PayDate, 120) ");
sql.AppendLine(") T3 ");
sql.AppendLine("ON T1.Dates = T3.Dates ");
sql.AppendLine("UNION ");
sql.AppendLine("SELECT isnull(T1.Currency,'代采') Currency ");
sql.AppendLine(",T1.Dates ");
sql.AppendLine(",T1.TotalCharge - ISNULL(T2.ChargeAmt, 0) ");
sql.AppendLine("- ISNULL(T3.AlipayFee, 0) TotalPurchaseCharge ");
sql.AppendLine("FROM ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T1.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(T1.TotalCharge, 0)) TotalCharge ");
sql.AppendLine(",isnull(T2.Currency,'代采') Currency ");
sql.AppendLine("FROM O_CustomerOrder T1 ");
sql.AppendLine("LEFT JOIN O_PurchaseOrder T2 ");
sql.AppendLine("ON T1.PayOrderCode = T2.PayOrderCode ");
sql.AppendLine($"WHERE T1.PayDate >= '{start}' ");
sql.AppendLine($"AND T1.PayDate < '{end}' ");
sql.AppendLine("AND T1.OrderType = 6 ");
sql.AppendLine("AND T1.PayState = 1 ");
sql.AppendLine("AND T2.DelState = 0 ");
sql.AppendLine("AND T1.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T1.PayDate, 120) ");
sql.AppendLine(",T2.Currency ");
sql.AppendLine(") T1 ");
sql.AppendLine("LEFT JOIN ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T2.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(T1.ReturnFee, 0)) ChargeAmt ");
sql.AppendLine(",isnull(T2.Currency,'代采') Currency ");
sql.AppendLine("FROM O_PurchaseOrder T2 ");
sql.AppendLine("LEFT JOIN O_PurchaseFeeReturn T1 ");
sql.AppendLine("ON T1.PurchaseOrderCode = T2.PurchaseOrderCode ");
sql.AppendLine($"WHERE T2.PayDate >= '{start}' ");
sql.AppendLine($"AND T2.PayDate < '{end}' ");
sql.AppendLine("AND T1.DelState = 0 ");
sql.AppendLine("AND T1.AllowReturn = 1 ");
sql.AppendLine("AND T1.ReturnState = 1 ");
sql.AppendLine("AND T2.DelState = 0 ");
sql.AppendLine("AND T2.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T2.PayDate, 120) ");
sql.AppendLine(",T2.Currency ");
sql.AppendLine(") T2 ");
sql.AppendLine("ON T1.Dates = T2.Dates ");
sql.AppendLine("AND T1.Currency = T2.Currency ");
sql.AppendLine("LEFT JOIN ( ");
sql.AppendLine("SELECT CONVERT(NVARCHAR(7), T2.PayDate, 120) Dates ");
sql.AppendLine(",SUM(ISNULL(T1.AccountFee, 0) ");
sql.AppendLine("+ ISNULL(T1.AlipayFee, 0)) AlipayFee ");
sql.AppendLine(",isnull(T2.Currency,'代采') Currency ");
sql.AppendLine("FROM O_PurchaseOrder T2 ");
sql.AppendLine("LEFT JOIN O_FeeReturn T1 ");
sql.AppendLine("ON T1.BindCode = T2.PayOrderCode ");
sql.AppendLine($"WHERE T2.PayDate >= '{start}' ");
sql.AppendLine($"AND T2.PayDate < '{end}' ");
sql.AppendLine("AND T1.PayState = 1 ");
sql.AppendLine("AND T1.FeeReturnType = 1 ");
sql.AppendLine("AND T1.Remark LIKE '采购订单%' ");
sql.AppendLine("AND T2.UserCode NOT IN ( 'CCCCC', 'BBBBB', ");
sql.AppendLine("'DDDDD', 'HHHHH', ");
sql.AppendLine("'RJFSU', 'YYYYY', ");
sql.AppendLine("'APWHW', 'BUUBXA' ) ");
sql.AppendLine("GROUP BY CONVERT(NVARCHAR(7), T2.PayDate, 120) ");
sql.AppendLine(",T2.Currency ");
sql.AppendLine(") T3 ");
sql.AppendLine("ON T1.Dates = T3.Dates ");
sql.AppendLine("AND T1.Currency=T3.Currency ");
sql.AppendLine(") T ");
sql.AppendLine("WHERE 1 = 1 ");
sql.AppendLine("*[T]*{0} ");
sql.AppendLine("ORDER BY [T].[Dates]; ");
serDT = ServiceConfig.GetInstance().GetOperation().CusQuery(sql.ToString(), out string checkSql, o_CustomerOrder);
Console.WriteLine(checkSql);
}
catch (Exception ex)
{
throw ex;
}
return(serDT);
}
