246 lines
11 KiB
JavaScript
246 lines
11 KiB
JavaScript
const Service = require('egg').Service;
|
||
class Query extends Service {
|
||
async gettime(dateString){
|
||
var regex = /(\d{4})年(\d{1,2})月(\d{1,2})日 (\d{1,2}):(\d{1,2}):(\d{1,2})/;
|
||
var match = dateString.match(regex);
|
||
var year = parseInt(match[1]);
|
||
var month = parseInt(match[2]) - 1; // 月份从0开始计算,所以要减1
|
||
var day = parseInt(match[3]);
|
||
var hour = parseInt(match[4]);
|
||
var minute = parseInt(match[5]);
|
||
var second = parseInt(match[6]);
|
||
|
||
var timestamp = new Date(year, month, day, hour, minute, second).getTime();
|
||
return timestamp;
|
||
}
|
||
async jhList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select OPERATERECORDS,sum(GOODSCOUNT) as GOODSCOUNT,count(*) as ordercount,GRABNUM FROM yj_wmsorder where PICKINGFLAG = 3 and FLAG=0 and DISPLAYFLAG=0 and WAREHOUSEID=${wareHouseId} and GRABNUM like '%B%' group by GRABNUM` );
|
||
let list = [];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
let tmp = item.OPERATERECORDS.split("|");
|
||
results[ii].time = -1;
|
||
delete results[ii].OPERATERECORDS;
|
||
tmp = tmp.filter(value => value !== null && value !== undefined && value !== '');
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('拣货')>-1){
|
||
results[ii].username = item2[2];
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
if(results[ii].username){
|
||
list.push(results[ii]);
|
||
}
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return b.time-a.time;
|
||
})
|
||
return jhList.slice(0,5);
|
||
}
|
||
async fhList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select OPERATERECORDS,sum(GOODSCOUNT) as GOODSCOUNT,count(*) as ordercount,GRABNUM FROM yj_wmsorder where PLSENDFLAG = 1 and FLAG=0 and DISPLAYFLAG=0 and WAREHOUSEID=${wareHouseId} and GRABNUM like '%B%' group by GRABNUM` );
|
||
let list = [];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
const tmp = item.OPERATERECORDS.split("|");
|
||
results[ii].time = -1;
|
||
delete results[ii].OPERATERECORDS;
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('发货')>-1){
|
||
results[ii].username = item2[2];
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
if(results[ii].username){
|
||
list.push(results[ii]);
|
||
}
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return b.time-a.time;
|
||
})
|
||
return jhList.slice(0,5);
|
||
}
|
||
async getGzts(wareHouseId){
|
||
const gzt = await this.app.mysql.get('dblocal').select('ye4s_gzt', {where:{wareHouseId:wareHouseId},orders: [['username','desc']]});
|
||
const gztList = gzt.map(item2=>{
|
||
let tmp = {
|
||
userid:item2.userid,
|
||
name:item2.name,
|
||
ordercount:item2.ordercount,
|
||
grabcount:item2.grabcount,
|
||
workers:item2.workers.split("|").filter(item => item),
|
||
grabnum:item2.grabnum,
|
||
};
|
||
return tmp;
|
||
})
|
||
let currentTime = Math.round(new Date() / 1000)-3600;
|
||
const WAREHOUSE = await this.app.mysql.get('dblocal').get("ye4s_warehouse",{WAREHOUSEID:wareHouseId});
|
||
const gztWorkers = await this.app.mysql.get('dblocal').query(`
|
||
SELECT
|
||
userId,
|
||
SUM(dabaos) as ordercount,
|
||
COUNT(*) as grabcount
|
||
FROM
|
||
ye4s_workers_works
|
||
WHERE
|
||
WAREHOUSEID = ${wareHouseId}
|
||
AND workDate =${WAREHOUSE.workDate}
|
||
GROUP BY
|
||
userId;
|
||
`);
|
||
const gztWorkers2 = await this.app.mysql.get('dblocal').query(`
|
||
SELECT
|
||
userId,
|
||
SUM(dabaos) as ordercount,
|
||
COUNT(*) as grabcount
|
||
FROM
|
||
ye4s_workers_works
|
||
WHERE
|
||
WAREHOUSEID = ${wareHouseId}
|
||
AND workDate =${WAREHOUSE.workDate}
|
||
AND createtime >${currentTime}
|
||
GROUP BY
|
||
userId;
|
||
`);
|
||
let list = gztList.map(item => {
|
||
for (let i = 0; i < gztWorkers.length; i++) {
|
||
if (gztWorkers[i].userId.toString() === item.userid) { // 修改此处条件判断
|
||
item.grabcount = gztWorkers[i].grabcount;
|
||
item.ordercount = gztWorkers[i].ordercount;
|
||
}
|
||
}
|
||
for (let i = 0; i < gztWorkers2.length; i++) {
|
||
if (gztWorkers2[i].userId.toString() === item.userid) { // 修改此处条件判断
|
||
item.grabcount2 = gztWorkers2[i].grabcount; // 修改此处赋值
|
||
item.ordercount2 = gztWorkers2[i].ordercount; // 修改此处赋值
|
||
}
|
||
}
|
||
if (item.grabcount > 0 || item.grabnum !== '' || item.ordercount > 0 || item.workers.length > 0) {
|
||
return item;
|
||
}
|
||
});
|
||
list = list.filter(n => n);
|
||
const gztLists = list.sort((a,b)=>{
|
||
return b.ordercount-a.ordercount;
|
||
})
|
||
return gztLists;
|
||
}
|
||
async getwarehouseName(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').get("yj_warehouse",{id:wareHouseId});
|
||
return results.NAME;
|
||
}
|
||
async dfhList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select OPERATERECORDS,GRABNUM FROM yj_wmsorder where PLSENDFLAG = 0 and FLAG=0 and PICKINGFLAG = 3 and DISPLAYFLAG=0 and WAREHOUSEID=${wareHouseId} and GRABNUM like '%B%' group by GRABNUM` );
|
||
let list = [];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
const tmp = item.OPERATERECORDS.split("|");
|
||
results[ii].time = -1;
|
||
delete results[ii].OPERATERECORDS;
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('拣货')>-1){
|
||
results[ii].username = item2[2];
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
if(results[ii].username){
|
||
list.push(results[ii]);
|
||
}
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return a.time-b.time;
|
||
})
|
||
return jhList;
|
||
}
|
||
async ddbList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select OPERATERECORDS,GRABNUM FROM yj_wmsorder where PLSENDFLAG = 1 and FLAG=0 and WAREHOUSEID=${wareHouseId} and DISPLAYFLAG=0 and GRABNUM like '%B%' and OPERATERECORDS not like '%打包%' group by GRABNUM` );
|
||
let list = [];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
const tmp = item.OPERATERECORDS.split("|");
|
||
results[ii].time = -1;
|
||
delete results[ii].OPERATERECORDS;
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('发货')>-1){
|
||
results[ii].username = item2[2];
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
if(results[ii].username){
|
||
list.push(results[ii]);
|
||
}
|
||
}
|
||
const GRABNUMs = list.map((item)=>{
|
||
return `'${item.GRABNUM}'`;
|
||
});
|
||
const query_string = GRABNUMs.join(",");
|
||
let has_results = [];
|
||
if(query_string){
|
||
has_results = await this.app.mysql.get('dblocal').query(`select GRABNUM from ye4s_workers_works where grabnum in (${query_string}) and dabaos>0`);
|
||
}
|
||
for (var ii = 0; ii < list.length; ii++) {
|
||
for (var iii = 0; iii < has_results.length; iii++) {
|
||
if (list[ii]&&list[ii].GRABNUM === has_results[iii].GRABNUM) {
|
||
list.splice(ii, 1); // 从 list 中删除索引为 ii 的元素
|
||
ii--; // 调整 ii 的值,因为数组长度减少了一个
|
||
}
|
||
}
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return a.time-b.time;
|
||
})
|
||
return jhList;
|
||
}
|
||
async djhList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select OPERATERECORDS,GRABNUM FROM yj_wmsorder where PICKINGFLAG = 0 and FLAG=0 and WAREHOUSEID=${wareHouseId} and DISPLAYFLAG=0 and GRABNUM like '%B%' and OPERATERECORDS like '%领取快递面单%' group by GRABNUM` );
|
||
let list = [];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
const tmp = item.OPERATERECORDS.split("|");
|
||
results[ii].time = -1;
|
||
delete results[ii].OPERATERECORDS;
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('领取快递面单')>-1){
|
||
results[ii].username = item2[2];
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
if(results[ii].username){
|
||
list.push(results[ii]);
|
||
}
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return a.time-b.time;
|
||
})
|
||
return jhList;
|
||
}
|
||
async dllList(wareHouseId){
|
||
let results = await this.app.mysql.get('dbwms').query(`select a.GRABNUM as GRABNUM,a.OPERATERECORDS as OPERATERECORDS,CAST(b.CREATIONDATE as SIGNED) as time FROM yj_wmsorder a left join yj_grabprint b on a.GRABNUM = b.GRABNUM and a.WAREHOUSEID=b.WAREHOUSEID and a.POSTCODE=b.POSTCODE where a.PRINTTIMES=1 and a.PICKINGFLAG=0 and a.WAREHOUSEID=${wareHouseId} and a.GRABNUM like '%B%' and a.DISPLAYFLAG=0 and a.OPERATERECORDS not like '%领取快递面单%' group by a.GRABNUM` );
|
||
let list =[];
|
||
for(var ii=0;ii<results.length;ii++){
|
||
const item = results[ii];
|
||
if(!results[ii].time){
|
||
results[ii].time = 0;
|
||
const tmp = item.OPERATERECORDS.split("|");
|
||
for(var i=0;i<tmp.length;i++){
|
||
const item2 = tmp[i].split(";");
|
||
if(item2 && item2[0].indexOf('打印')>-1){
|
||
results[ii].time = await this.gettime(item2[1]);
|
||
}
|
||
}
|
||
}
|
||
delete results[ii].OPERATERECORDS;
|
||
list.push(results[ii]);
|
||
}
|
||
const jhList = list.sort((a,b)=>{
|
||
return a.time-b.time;
|
||
})
|
||
return jhList;
|
||
}
|
||
}
|
||
module.exports = Query; |