// import * as sql from "mssql";
|
import { Post } from "egg-shell-decorators";
|
import BaseController from "../../baseController";
|
import { vSaleOrderList } from "../../../entity/outbound/stat/vSaleOrderList";
|
import { vSaleOrderListClient } from "../../../entity/outbound/stat/vSaleOrderListClient";
|
import { vSaleOrderPickingWorkloadStatistics } from "../../../entity/outbound/stat/vSaleOrderPickingWorkloadStatistics";
|
// import { SaleOrder } from "../../../entity/outbound/sale/saleOrder";
|
// import { SaleOrderPicking } from "../../../entity/outbound/manufacture/saleOrderPicking";
|
// import { vSaleOrderList } from "../../../entity/outbound/stat/vSaleOrderList";
|
// import { vPurchaseEnterListProduct } from "../../../entity/inbound/stat/vPurchaseEnterListProduct";
|
/**
|
* 收货统计-到货明细统计
|
*/
|
export default class VsaleOrderController extends BaseController {
|
// #region 获取出库订单线形图信息
|
@Post()
|
public async getvSaleOrderLine() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let storage_Id = body.storage_Id;
|
let consignor_Id = body.consignor_Id;
|
let beginTime = body.beginTime;
|
let endTime = body.endTime;
|
let userProduct_Id = body.userProduct_Id;
|
let provider_Id = body.provider_Id;
|
let where = "and _B.UserProduct_Id=" + userProduct_Id;
|
if (storage_Id) {
|
where += " and _B.storage_Id=" + storage_Id + "";
|
}
|
if (beginTime && endTime) {
|
where += " and _B.auditDate >= '" + beginTime + "' And _B.auditDate <= '" + endTime + " 23:59:59'";
|
}
|
if (provider_Id) {
|
where += " and _B.Provider_Id=" + provider_Id + "";
|
}
|
if (consignor_Id) {
|
where += "and _B.Consignor_Id=" + consignor_Id;
|
}
|
try {
|
let sql =
|
"SELECT CONVERT(BIGINT,DATEDIFF(SECOND,'1970-01-01',T.EveryDay))*1000 AS [Millisecond],T.everyDay,T.totalQuantityOrder,T.grandTotal,T.tolNum FROM( SELECT CONVERT(VARCHAR(100), [_A].PK_日期, 23) AS EveryDay, SUM(ISNULL([_B].TotalQuantityOrder, 0)) AS TotalQuantityOrder, SUM(ISNULL([_B].GrandTotal, 0)) AS GrandTotal, COUNT(*) AS TolNum FROM dbo.Sts_DimDate _A LEFT JOIN dbo.Sale_Order _B ON CONVERT(DATE, [_A].PK_日期) = CONVERT(DATE,[_B].AuditDate) WHERE _B.StatusText != '新建' " +
|
where +
|
" AND[_A].PK_日期 BETWEEN DATEADD(YEAR,-2,GETDATE()) AND GETDATE() GROUP BY[_A].PK_日期 ) T";
|
|
let productList = await this.dbRead.query(sql);
|
|
// let productList = await this.dbRead
|
// .createQueryBuilder(vPurchaseEnterListProduct, "t")
|
// .select( ["quantity", "productName"])
|
// .where(where)
|
// .getRawMany();
|
let seriesList: any = [];
|
for (let item of productList) {
|
let series = {
|
everyDay: item.everyDay,
|
totalQuantityOrder: item.totalQuantityOrder,
|
grandTotal: item.grandTotal,
|
tolNum: item.tolNum
|
};
|
seriesList.push(series);
|
}
|
|
this.info.result = true;
|
this.info.msg = "获取图标成功";
|
this.info.data = seriesList;
|
ctx.body = this.info;
|
return;
|
} catch (error) {
|
this.info.result = false;
|
this.info.msg = error.message;
|
ctx.body = this.info;
|
return;
|
}
|
}
|
|
// #endregion
|
|
// #region 获取出库量前5名货主柱状图或饼形图信息
|
@Post()
|
public async getvSaleStatistics() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let type = body.type;
|
let typename = body.typename;
|
let storage_Id = body.storage_Id;
|
let consignor_Id = body.consignor_Id;
|
let beginTime = body.beginTime;
|
let endTime = body.endTime;
|
let userProduct_Id = body.userProduct_Id;
|
let provider_Id = body.provider_Id;
|
let where: any = {};
|
where.userProduct_Id = userProduct_Id;
|
if (storage_Id) {
|
where.storage_Id = storage_Id;
|
}
|
if (provider_Id) {
|
where.provider_Id = provider_Id;
|
}
|
if (consignor_Id) {
|
where.consignor_Id = consignor_Id;
|
}
|
try {
|
let productList;
|
let datalist;
|
// let sql = "";
|
if (type === "物料统计") {
|
if (beginTime && endTime) {
|
productList = await this.dbRead
|
.createQueryBuilder(vSaleOrderList, "t")
|
.select(["quantityOuted", "productName"])
|
.where(where)
|
.andWhere("SendDate>=:beginTime and SendDate<=:endTime", {
|
beginTime: beginTime,
|
endTime: endTime
|
})
|
.getRawMany();
|
} else {
|
productList = await this.dbRead.createQueryBuilder(vSaleOrderList, "t").select(["quantityOuted", "productName"]).where(where).getRawMany();
|
}
|
|
// sql =
|
// "SELECT SUM(QuantityOuted) AS quantityOuted,MAX(ProductName) AS productName FROM vSale_OrderList WHERE QuantityOuted IS NOT NULL AND QuantityOuted >0 " +
|
// where +
|
// " GROUP BY Product_Id,ProductCode order by QuantityOuted desc";
|
datalist = productList.slice(0, 5);
|
} else {
|
if (typename === "人员拣货统计") {
|
// sql =
|
// "SELECT TOP 5 user_Id,MAX(UserTrueName) AS userTrueName ,SUM(TotalQuanity) AS totalQuanity FROM vSale_OrderPicking_WorkloadStatistics where TotalQuanity IS NOT NULL and TotalQuanity <>0 " +
|
// where +
|
// " GROUP BY USER_ID ORDER BY TotalQuanity desc;";
|
if (beginTime && endTime) {
|
productList = await this.dbRead
|
.createQueryBuilder(vSaleOrderPickingWorkloadStatistics, "t")
|
.select(["UserTrueName", "totalQuanity"])
|
.where(where)
|
.andWhere("EndDate>=:beginTime and EndDate<=:endTime", {
|
beginTime: beginTime,
|
endTime: endTime
|
})
|
.getRawMany();
|
} else {
|
productList = await this.dbRead
|
.createQueryBuilder(vSaleOrderPickingWorkloadStatistics, "t")
|
.select(["userTrueName", "totalQuanity"])
|
.where(where)
|
.getRawMany();
|
}
|
} else {
|
if (beginTime && endTime) {
|
productList = await this.dbRead
|
.createQueryBuilder(vSaleOrderListClient, "t")
|
.select(["clientShortName", "quantityOrder"])
|
.where(where)
|
.andWhere("ApplyDate>=:beginTime and ApplyDate<=:endTime", {
|
beginTime: beginTime,
|
endTime: endTime
|
})
|
.getRawMany();
|
} else {
|
productList = await this.dbRead
|
.createQueryBuilder(vSaleOrderListClient, "t")
|
.select(["clientShortName", "quantityOrder"])
|
.where(where)
|
.getRawMany();
|
}
|
// sql =
|
// "SELECT TOP 5 MAX(ClientShortName) AS clientShortName,SUM(QuantityOrder) AS quantityOrder FROM vSale_OrderList_Client where ClientShortName IS NOT NULL " +
|
// where +
|
// " GROUP BY ClientShortName ORDER BY QuantityOrder desc";
|
}
|
datalist = productList.slice(0, 10);
|
}
|
|
let datainfo: any = {};
|
let category = [];
|
let seriesList: any = [];
|
if (type === "物料统计") {
|
for (let item of datalist) {
|
category.push(item.productName);
|
seriesList.push(item.quantityOuted);
|
}
|
let series = [
|
{
|
name: "入库量",
|
type: "bar",
|
data: seriesList
|
}
|
];
|
datainfo = {
|
category: category,
|
series: series
|
};
|
} else {
|
if (typename === "人员拣货统计") {
|
for (let item of datalist) {
|
let series = {
|
name: item.userTrueName,
|
value: item.totalQuanity
|
};
|
seriesList.push(series);
|
}
|
datainfo = {
|
seriesList: seriesList
|
};
|
} else {
|
for (let item of datalist) {
|
let series = {
|
name: item.clientShortName,
|
value: item.quantityOrder
|
};
|
seriesList.push(series);
|
}
|
datainfo = {
|
seriesList: seriesList
|
};
|
}
|
}
|
this.info.result = true;
|
this.info.msg = "获取图标成功";
|
this.info.data = datainfo;
|
ctx.body = this.info;
|
return;
|
} catch (error) {
|
this.info.result = false;
|
this.info.msg = error.message;
|
ctx.body = this.info;
|
return;
|
}
|
}
|
|
// #endregion
|
|
// #region 获取出库订单数量信息
|
/**
|
/ 获取出库订单数量信息
|
*/
|
@Post()
|
public async getSaleOrderQuantity() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
// let userInfo = await ctx.helper.userInfo();
|
let storage_Id = body.storage_Id;
|
let consignor_Id = body.consignor_Id;
|
let beginTime = body.beginTime;
|
let endTime = body.endTime;
|
let userProduct_Id = body.userProduct_Id;
|
let provider_Id = body.provider_Id;
|
let where = "UserProduct_Id=" + userProduct_Id;
|
if (storage_Id) {
|
where += " and storage_Id=" + storage_Id + "";
|
}
|
if (beginTime && endTime) {
|
where += " and ApplyDate >= '" + beginTime + "' And ApplyDate <= '" + endTime + " 23:59:59'";
|
}
|
if (provider_Id) {
|
where += " and Provider_Id=" + provider_Id + "";
|
}
|
if (consignor_Id) {
|
where += "and Consignor_Id=" + consignor_Id;
|
}
|
try {
|
// 出库订单物料数
|
let sql = "select sum(totalQuantityOrder) as sum from Sale_Order where " + where + " ";
|
let quantityOrderCount = await this.dbRead.query(sql);
|
|
// let quantityOrderCount = await this.dbRead
|
// .createQueryBuilder(SaleOrder, "t")
|
// .select("sum(totalQuantityOrder)", "sum")
|
// .where(where)
|
// .andWhere("auditDate>=:beginTime and auditDate<=:endTime", {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// })
|
// .getRawOne();
|
|
// 已拣货下架
|
sql = "select sum(totalQuanity) as sum from Sale_OrderPicking where " + where + " ";
|
let pickingQuanityCount = await this.dbRead.query(sql);
|
|
// let pickingQuanityCount = await this.dbRead
|
// .createQueryBuilder(SaleOrderPicking, "t")
|
// .select("sum(totalQuanity)", "sum")
|
// .where(where)
|
// .andWhere("EndDate>=:beginTime and EndDate<=:endTime", {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// })
|
// .getRawOne();
|
|
// 打包数量
|
sql = "select sum(PackageQuantity) as sum from Sale_Outer M where " + where + " ";
|
let packQuanity = await this.dbRead.query(sql);
|
|
// let packQuanity = await this.dbRead
|
// .createQueryBuilder(SaleOrder, "t")
|
// .select("sum(totalQuantityOuted)", "sum") // --packageQuantity 原net 使用packageQuantity字段，现在没有这个字段待确认
|
// .where(where)
|
// .andWhere("CreateDate>=:beginTime and CreateDate<=:endTime ", {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// })
|
// .getRawOne();
|
|
// 已发货物料数
|
sql = "select sum(QuantityOuted) as sum from vSale_OrderList M where " + where + " ";
|
let sendQuanity = await this.dbRead.query(sql);
|
|
// let sendQuanity = await this.dbRead
|
// .createQueryBuilder(vSaleOrderList, "t")
|
// .select("sum(quantityOuted)", "sum")
|
// .where(where)
|
// .andWhere(
|
// "SendDate>=:beginTime and SendDate<=:endTime ", // and EXISTS (SELECT 1 FROM Sale_Order L WHERE M.OrderCode=L.OrderCode AND L.storage_Id=:storage_Id)
|
// {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// }
|
// )
|
// .getRawOne();
|
|
// 订单数
|
sql = "select count(*) as sum from Sale_Order where " + where + " ";
|
let orderCount = await this.dbRead.query(sql);
|
|
// let orderCount = await this.dbRead
|
// .createQueryBuilder(SaleOrder, "t")
|
// .select("count(*)", "sum")
|
// .where(where)
|
// .andWhere("auditDate>=:beginTime and auditDate<=:endTime", {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// })
|
// .getRawOne();
|
|
// 已发货订单数量
|
sql = "select count(*) as sum from Sale_Order where " + where + " ";
|
let sendOrderCount = await this.dbRead.query(sql);
|
|
// let sendOrderCount = await this.dbRead
|
// .createQueryBuilder(SaleOrder, "t")
|
// .select("count(*)", "sum")
|
// .where(where)
|
// .andWhere("auditDate>=:beginTime and auditDate<=:endTime", {
|
// beginTime: beginTime,
|
// endTime: endTime
|
// })
|
// .getRawOne();
|
|
this.info.result = true;
|
this.info.msg = "获取成功";
|
quantityOrderCount = quantityOrderCount[0].sum;
|
pickingQuanityCount = pickingQuanityCount[0].sum;
|
packQuanity = packQuanity[0].sum;
|
sendQuanity = sendQuanity[0].sum;
|
orderCount = orderCount[0].sum;
|
sendOrderCount = sendOrderCount[0].sum;
|
this.info.data = {
|
quantityOrderCount: quantityOrderCount,
|
pickingQuanityCount: pickingQuanityCount,
|
packQuanityCount: packQuanity,
|
sendQuanityCount: sendQuanity,
|
orderCount: orderCount,
|
sendOrderCount: sendOrderCount
|
};
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "获取数据时出错：" + ex.message;
|
}
|
ctx.body = this.info;
|
return;
|
}
|
// #endregion
|
|
//#region 获取拣货人信息
|
/**
|
* 获取拣货人信息
|
*/
|
@Post()
|
public async getvSaleOrderPickingWorkloadSelect() {
|
let { ctx } = this;
|
try {
|
let sql =
|
"SELECT user_Id,MAX(UserTrueName) AS userTrueName ,SUM(TotalQuanity) AS totalQuanity FROM vSale_OrderPicking_WorkloadStatistics where TotalQuanity IS NOT NULL and TotalQuanity <>0 GROUP BY USER_ID ORDER BY TotalQuanity desc";
|
let dataList = await this.dbRead.query(sql);
|
this.info.result = true;
|
this.info.data = dataList;
|
} catch (e) {
|
this.info.result = false;
|
this.info.msg = e.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
}
|
