public GridPage <List <DingNew> > GetNews(DatetimePointPageReq pageReq, int?id)
{
var res = new GridPage <List <DingNew> >()
{
code = ResCode.Success
};
if (id != null)
{
FilterNode node = new FilterNode();
node.andorop = "and";
node.binaryop = "eq";
node.key = "DingClassify.Id";
node.value = id;
if (pageReq == null)
{
pageReq = new DatetimePointPageReq();
}
pageReq.query.Add(node);
res = DataBaseHelper <DingNew> .GetList(_uowProvider, res, pageReq, x => x.Include(a => a.DingClassify));
}
else
{
//all
res = DataBaseHelper <DingNew> .GetList(_uowProvider, res, pageReq, x => x.Include(a => a.DingClassify));
}
return(res);
}
public GridPage <DingNew> GetNew(int id)
{
var res = new GridPage <DingNew>()
{
code = ResCode.Success
};
var result = new GridPage <List <DingNew> >()
{
code = ResCode.Success
};
FilterNode node = new FilterNode();
node.andorop = "and";
node.binaryop = "eq";
node.key = "Id";
node.value = id;
var pageReq = new DatetimePointPageReq();
pageReq.query.Add(node);
result = DataBaseHelper <DingNew> .GetList(_uowProvider, result, pageReq, x => x.Include(a => a.DingClassify));
if (result.data.Count > 0)
{
res.data = result.data[0];
}
return(res);
}
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 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 <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 GridPage <List <BnsAmazonReport> > GetAmazonList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <BnsAmazonReport> >()
{
code = ResCode.Success
};
return(DataBaseHelper <BnsAmazonReport> .GetList(_uowProvider, res, pageReq));
}
public GridPage <List <BnsPaypalTransactionDetail> > GetPaypalList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <BnsPaypalTransactionDetail> >()
{
code = ResCode.Success
};
return(DataBaseHelper <BnsPaypalTransactionDetail> .GetList(_uowProvider, res, pageReq, x => x.Include(a => a.BnsPaypalTransactionDetailsCartInfos)));
}
public GridPage <List <BnsSendDeliverdToEc> > GetDeliverList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <BnsSendDeliverdToEc> >()
{
code = ResCode.Success
};
return(DataBaseHelper <BnsSendDeliverdToEc> .GetList(_uowProvider, res, pageReq));
}
public GridPage <List <ECRepeatCust> > GetRepeatCustList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECRepeatCust> >()
{
code = ResCode.Success
};
return(DataBaseHelper <ECRepeatCust> .GetList(_uowProvider, res, pageReq));
}
public GridPage <List <ECAliexpressLoaninfo> > GetAliLoanList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECAliexpressLoaninfo> >()
{
code = ResCode.Success
};
return(DataBaseHelper <ECAliexpressLoaninfo> .GetList(_uowProvider, res, pageReq));
}
public GridPage <List <ECShipBatch> > GetList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECShipBatch> >()
{
code = ResCode.Success
};
try
{
using (var uow = _uowProvider.CreateUnitOfWork())
{
var repository = uow.GetRepository <ECShipBatch>();
if (pageReq == null)
{
res.data = repository.GetPage(0, 50).ToList();
return(res);
}
else
{
if (pageReq.pageSize == 0)
{
pageReq.pageSize = 1;
}
if (pageReq.pageNum == 0)
{
pageReq.pageNum = 1;
}
}
int startRow = (pageReq.pageNum - 1) * pageReq.pageSize;
Filter <ECShipBatch> filter = new Filter <ECShipBatch>(null);
if (pageReq.query.Count > 0)
{
var fuc = FilterHelper <ECShipBatch> .GetExpression(pageReq.query, "OmsReceivingPage");
filter = new Repository.Filter <ECShipBatch>(fuc);
}
OrderBy <ECShipBatch> orderBy = new OrderBy <ECShipBatch>(null);
if (pageReq.order != null)
{
orderBy = new Repository.OrderBy <ECShipBatch>(pageReq.order.columnName, pageReq.order.reverse);
}
res.totalCount = repository.Query(filter.Expression, orderBy.Expression).Count();
res.data = repository.QueryPage(startRow, pageReq.pageSize, filter.Expression, orderBy.Expression,
x =>
x.Include(a => a.PackingInfo)
.Include(a => a.ProductInfo)).ToList();
}
}
catch (Exception ex)
{
res.code = ResCode.ServerError;
res.msg = ex.Message;
}
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 <UsUiceNomalSkuQtyReport> > GetList(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <UsUiceNomalSkuQtyReport> >()
{
code = ResCode.Success
};
res = DataBaseHelper <UsUiceNomalSkuQtyReport> .GetFromProcedure(_uowProvider, res, pageReq, false, "EXECUTE UsUiceNomalSkuQty_sp");
return(res);
}
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 void ExportRepeatCust(DatetimePointPageReq pageReq)
{
var res = new GridPage <List <ECRepeatCust> >()
{
code = ResCode.Success
};
res = DataBaseHelper <ECRepeatCust> .GetList(_uowProvider, res, pageReq, null, true);
Response.Headers.Add("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("RepeatCust.xlsx"));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";
Response.Body.Write(ExcelHelper <ECRepeatCust> .NpoiListToExcel(res.data));
Response.Body.Flush();
Response.Body.Close();
}
public GridPage <List <DingClassify> > GetTypes(DatetimePointPageReq pageReq, int classId)
{
FilterNode node = new FilterNode();
node.andorop = "and";
node.binaryop = "eq";
node.key = "DingClass.Id";
node.value = classId;
pageReq.query.Add(node);
var res = new GridPage <List <DingClassify> >()
{
code = ResCode.Success
};
return(DataBaseHelper <DingClassify> .GetList(_uowProvider, res, pageReq));
}
public ActionResult <PageDataRes <SingleSalesAnalysis> > GetPage(DatetimePointPageReq req)
{
var res = new PageDataRes <SingleSalesAnalysis>()
{
code = ResCode.Success
};
if (req != null)
{
using (var uow = _uowProvider.CreateUnitOfWork())
{
var repository = uow.GetCustomRepository <ISingleSalesAnlysisRepository>();
var page = repository.GetPage(req.datetimePoint, req.pageNum, req.pageSize, FilterNode.ListToString(req.query));
res = PageMapper <SingleSalesAnalysis> .ToPageDateRes(page);
}
}
else
{
res.code = ResCode.NoValidate;
res.msg = ResMsg.ParameterIsNull;
}
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 GridPage <List <BnsAmazonReportDetail> > GetAmazonDetailList(DatetimePointPageReq pageReq, int?id)
{
if (id != null)
{
FilterNode node = new FilterNode();
node.andorop = "and";
node.binaryop = "eq";
node.key = "BnsAmazonReport.Id";
node.value = id;
if (pageReq == null)
{
pageReq = new DatetimePointPageReq();
}
pageReq.query.Add(node);
}
var res = new GridPage <List <BnsAmazonReportDetail> >()
{
code = ResCode.Success
};
res = DataBaseHelper <BnsAmazonReportDetail> .GetList(_uowProvider, res, pageReq);
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();
}
public static GridPage <List <T> > GetFromProcedure(IUowProvider _uowProvider, GridPage <List <T> > res, DatetimePointPageReq pageReq, bool getAll, string procedure, params SqlParameter[] sqlParameters)
{
try
{
using (var uow = _uowProvider.CreateUnitOfWork())
{
var repository = uow.GetRepository <T>();
if (pageReq == null)
{
res.data = repository.GetPage(0, 50).ToList();
return(res);
}
else
{
if (pageReq.pageSize == 0)
{
pageReq.pageSize = 50;
}
if (pageReq.pageNum == 0)
{
pageReq.pageNum = 1;
}
}
var resAll = repository.FromProcedure(procedure, sqlParameters);
if (pageReq.query.Count > 0)
{
var fuc = FilterHelper <T> .GetExpression(pageReq.query, "commonProcedure");
resAll = resAll.Where(fuc);
}
OrderBy <T> orderBy = new OrderBy <T>(null);
if (pageReq.order != null)
{
orderBy = new OrderBy <T>(pageReq.order.columnName, pageReq.order.reverse);
resAll = orderBy.Expression(resAll);
}
res.data = resAll.ToList();
res.totalCount = res.data.Count();
if (!getAll)
{
res.data = res.data.Skip((pageReq.pageNum - 1) * pageReq.pageSize).Take(pageReq.pageSize).ToList();
}
}
}
catch (Exception ex)
{
res.code = ResCode.ServerError;
res.msg = ex.Message;
}
return(res);
}
public static GridPage <List <T> > GetList(IUowProvider _uowProvider, GridPage <List <T> > res, DatetimePointPageReq pageReq, Func <IQueryable <T>, IQueryable <T> > includes = null, bool getAll = false)
{
try
{
using (var uow = _uowProvider.CreateUnitOfWork())
{
var repository = uow.GetRepository <T>();
if (pageReq == null)
{
res.data = repository.GetPage(0, 50).ToList();
return(res);
}
else
{
if (pageReq.pageSize == 0)
{
pageReq.pageSize = 50;
}
if (pageReq.pageNum == 0)
{
pageReq.pageNum = 1;
}
}
int startRow = (pageReq.pageNum - 1) * pageReq.pageSize;
Filter <T> filter = new Filter <T>(null);
if (pageReq.query.Count > 0)
{
var fuc = FilterHelper <T> .GetExpression(pageReq.query, "common");
filter = new Filter <T>(fuc);
}
OrderBy <T> orderBy = new OrderBy <T>(null);
if (pageReq.order != null)
{
orderBy = new OrderBy <T>(pageReq.order.columnName, pageReq.order.reverse);
}
if (getAll)
{
res.data = repository.Query(filter.Expression, orderBy.Expression).ToList();
}
else
{
res.data = repository.QueryPage(startRow, pageReq.pageSize, filter.Expression, orderBy.Expression, includes).ToList();
}
res.totalCount = repository.Query(filter.Expression, orderBy.Expression).Count();
}
}
catch (Exception ex)
{
res.code = ResCode.ServerError;
res.msg = ex.Message;
}
return(res);
}
