public void ExportOrderCostTotal(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <OrderCostTotalReport> > {
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "storename":
sbCommon.Append($" {item.andorop} PlatformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
default:
sbCommon.Append($" {item.andorop} {item.key} {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
res = DataBaseHelper <OrderCostTotalReport> .GetFromProcedure(_uowProvider, res, pageReq, true, "EXECUTE OrderCostStore_sp @whereSql", whereSql);
Response.Headers.Add("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("OrderCostTotalReport.xlsx"));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";
Response.Body.Write(ExcelHelper <OrderCostTotalReport> .NpoiListToExcel(res.data));
Response.Body.Flush();
Response.Body.Close();
}
public GridPage <List <SkuSaleQueryReport> > SkuSaleQuery(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <SkuSaleQueryReport> > {
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "storename":
sbCommon.Append($" {item.andorop} PlatformUserName in ({item.value}) ");
break;
case "sku":
sbCommon.Append($" {item.andorop} PcrProductSku {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
default:
sbCommon.Append($" {item.andorop} {item.key} {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
res = DataBaseHelper <SkuSaleQueryReport> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE SkuSaleQuery_sp @whereSql", whereSql);
return(res);
}
public GridPage <List <ECHeadTripLine> > GetECHeadTripLinesPage(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECHeadTripLine> >()
{
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
StringBuilder sbLoandate = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "addtime":
if (item.binaryop == "gt")
{
sbCommon.Append($" and t1.AddTime >= '{item.value}'");
}
if (item.binaryop == "lt")
{
sbCommon.Append($" and t1.AddTime <= '{item.value}'");
}
break;
case "itemsku":
sbCommon.Append($" and t4.PcrProductSku {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "storename":
sbCommon.Append($" and t1.PlatformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "towarehouse":
sbCommon.Append($" and ToWarehouse {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "warehouse":
sbCommon.Append($" and Warehouse {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "ordercode":
sbCommon.Append($" and t1.Ordercode {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "companyname":
sbCommon.Append($" and company {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
res = DataBaseHelper <ECHeadTripLine> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE ShipBatckQuery_sp @whereSql", whereSql);
return(res);
}
public void ExportFinancialStatement(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <CwAccountQueryReport> > {
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
StringBuilder sbLoandate = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "loandate":
list.Add(item);
break;
case "status":
sbCommon.Append($" and '{item.value}' = CASE WHEN t1.Status = 0 THEN '已废弃' WHEN t1.Status = 1 THEN '付款未完成' " +
$"WHEN t1.Status = 2 THEN '待发货审核' WHEN t1.Status = 3 THEN '待发货' WHEN t1.Status = 4 THEN '已发货' " +
$"WHEN t1.Status = 5 THEN '冻结中' WHEN t1.Status = 6 THEN '缺货' WHEN t1.Status = 7 THEN '问题件' " +
$"WHEN t1.Status = 8 THEN '未付款' END ");
break;
case "ordertype":
sbCommon.Append($" and '{item.value}'= CASE WHEN orderType = 'sale' THEN '正常销售订单' WHEN orderType = 'resend' " +
$"THEN '重发订单' WHEN orderType = 'line' AND LEFT(t1.refno, 1) NOT IN('Y', 'H', 'S', 'A') " +
$"THEN '线下订单' WHEN orderType = 'line' AND LEFT(t1.refno, 1) IN('Y', 'H', 'S', 'A') " +
$"THEN '营销订单' END ");
break;
case "storename":
sbCommon.Append($" and platformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
default:
sbCommon.Append($" and {item.key} {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
res = DataBaseHelper <CwAccountQueryReport> .GetFromProcedure(_uowProvider, res, pageReq, true, "EXECUTE CwAccountQuery_sp @whereSql", whereSql);
//将已经解码的字符再次进行编码.
Response.Headers.Add("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("FinancialStatementReport.xlsx"));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";
Response.Body.Write(ExcelHelper <CwAccountQueryReport> .NpoiListToExcel(res.data.OrderBy(a => a.RefNo).ThenByDescending(a => a.Amountpaid).ToList()));
Response.Body.Flush();
Response.Body.Close();
}
public GridPage <List <CwAccountQueryReport> > GetFinancialStatement(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <CwAccountQueryReport> > {
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
StringBuilder sbLoandate = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "loandate":
list.Add(item);
break;
case "status":
sbCommon.Append($" and '{item.value}' = CASE WHEN t1.Status = 0 THEN '已废弃' WHEN t1.Status = 1 THEN '付款未完成' " +
$"WHEN t1.Status = 2 THEN '待发货审核' WHEN t1.Status = 3 THEN '待发货' WHEN t1.Status = 4 THEN '已发货' " +
$"WHEN t1.Status = 5 THEN '冻结中' WHEN t1.Status = 6 THEN '缺货' WHEN t1.Status = 7 THEN '问题件' " +
$"WHEN t1.Status = 8 THEN '未付款' END ");
break;
case "ordertype":
sbCommon.Append($" and '{item.value}'= CASE WHEN orderType = 'sale' THEN '正常销售订单' WHEN orderType = 'resend' " +
$"THEN '重发订单' WHEN orderType = 'line' AND LEFT(t1.refno, 1) NOT IN('Y', 'H', 'S', 'A') " +
$"THEN '线下订单' WHEN orderType = 'line' AND LEFT(t1.refno, 1) IN('Y', 'H', 'S', 'A') " +
$"THEN '营销订单' END ");
break;
case "storename":
sbCommon.Append($" and platformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
default:
sbCommon.Append($" and {item.key} {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
res = DataBaseHelper <CwAccountQueryReport> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE CwAccountQuery_sp @whereSql", whereSql);
res.data = res.data.OrderBy(a => a.RefNo).ThenByDescending(a => a.Amountpaid).ToList();
return(res);
}
public GridPage <List <OrderCostTotalReport> > OrderCostTotal(DatetimePointPageReq pageReq, bool total = false)
{
var res = new GridPage <List <OrderCostTotalReport> > {
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "storename":
sbCommon.Append($" {item.andorop} PlatformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
default:
sbCommon.Append($" {item.andorop} {item.key} {Operate.GetSqlOperate(item.binaryop)} '{item.value}' ");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
if (total)
{
res = DataBaseHelper <OrderCostTotalReport> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE OrderCostPlateform_sp @whereSql", whereSql);
}
else
{
res = DataBaseHelper <OrderCostTotalReport> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE OrderCostStore_sp @whereSql", whereSql);
}
return(res);
}
public void ExportECHeadTripLines(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECHeadTripLine> >()
{
code = ResCode.Success
};
StringBuilder sbCommon = new StringBuilder();
StringBuilder sbLoandate = new StringBuilder();
List <FilterNode> list = new List <FilterNode>();
foreach (var item in pageReq.query)
{
if (item.value != null)
{
switch (item.key.ToLower())
{
case "addtime":
if (item.binaryop == "gt")
{
sbCommon.Append($" and t1.AddTime >= '{item.value}'");
}
if (item.binaryop == "lt")
{
sbCommon.Append($" and t1.AddTime <= '{item.value}'");
}
break;
case "itemsku":
sbCommon.Append($" and t4.PcrProductSku {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "storename":
sbCommon.Append($" and t1.PlatformUserName {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "towarehouse":
sbCommon.Append($" and ToWarehouse {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "warehouse":
sbCommon.Append($" and Warehouse {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "ordercode":
sbCommon.Append($" and t1.Ordercode {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
case "companyname":
sbCommon.Append($" and company {Operate.GetSqlOperate(item.binaryop)} '{item.value}'");
break;
}
}
}
var whereSql = new SqlParameter("@whereSql", sbCommon.ToString());
pageReq.query = list;
res = DataBaseHelper <ECHeadTripLine> .GetFromProcedure(_uowProvider, res, pageReq, true, "EXECUTE ShipBatckQuery_sp @whereSql", whereSql);
//将已经解码的字符再次进行编码.
Response.Headers.Add("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("FinancialStatementReport.xlsx"));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";
Response.Body.Write(ExcelHelper <ECHeadTripLine> .NpoiListToExcel(res.data.OrderBy(a => a.AddTime).ThenByDescending(a => a.AddTime).ToList()));
Response.Body.Flush();
Response.Body.Close();
}
