Query，QueryRun, Group By,多表联接汇总
void runWrkCtr()
{
NJ_WIP _NJ_WIP;
ACT_tmpProductionWIPMaterial _tmpWIP;
Int I, ctr = 0;
ProdTable _ProdTable;
str tmpstr;
#Macrolib.AviFiles
SysOperationProgress simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, "@SYS76835",7000);
Query Qry = new Query();
QueryBuildDataSource QBSNJ_WIP,QBSProdTable;
QUeryBuildRange QbrWrkId,QbrProdId;
QueryRun Qr;
;
element.lock();
_LogID = NJ_FormLog::NewLog(this.form().name(),TimeNow());
StartTime = TimeNow();
TabPage1.setFocus();
simpleprogress.setText(strfmt("%1","Production Order is processing..."));
If (IsDelay)
While Select _ProdTable Where _ProdTable.ProdStatus < ProdStatus::ReportedFinished
&& _ProdTable.ProdStatus >= ProdStatus::StartedUp
{
Sleep(DelayTime);
simpleProgress.incCount();
}
Select Count(recid) From _ProdTable Where _ProdTable.ProdStatus < ProdStatus::ReportedFinished
&& _ProdTable.ProdStatus >= ProdStatus::StartedUp;
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, "@SYS76835",_ProdTable.Recid);
simpleProgress.incCount(1000);
simpleprogress.setText(strfmt("%1","WIP Running"));
Delete_From ACT_tmpProductionWIPMaterial;
TabPage1.setFocus();
QBSNJ_WIP = qry.addDataSource(tablenum(NJ_WIP));
QBSNJ_WIP.orderMode(OrderMode::GroupBy);
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,Qty),selectionField::Sum);
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,QtySec),selectionField::Sum);
/* QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,WrkCtrID));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,ItemId));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,InventSizeId));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,ConfigId));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,InventcolorId));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,UnitId));
QBSNJ_WIP.addSelectionField(FieldNUm(NJ_WIP,UnitIdSec)); */
//QBSNJ_WIP.fetchMode(1);
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,WrkCtrId));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,ItemID));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,InventSizeId));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,ConfigId));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,InventcolorId));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,UnitId));
QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,UnitIdSec));
//QBSNJ_WIP.addSortField(FieldNUm(NJ_WIP,ProdId));
Qr = new QueryRun(qry);
While Select ProdId,ACT_ProdCancelId,SalesId, CustAccount, DlvDate From _ProdTable Where _ProdTable.ProdStatus < ProdStatus::ReportedFinished
&& _ProdTable.ProdStatus >= ProdStatus::StartedUp
{
If (cbxCheckPO.value() == 0)
NJ_WIP::Update_ProdId(_ProdTable.ProdId);
/* While Select Sum(Qty), Sum(QtySec), WrkCtrID, ItemId, InventSizeId, ConfigId, InventcolorId, UnitId, UnitIdSec From _NJ_WIP
Group By WrkCtrID, ItemId, InventSizeId, ConfigId, InventcolorId, UnitId, UnitIdSec
Where _NJ_WIP.ProdId == _ProdTable.ProdId
&& _NJ_WIP.WrkCtrId == edtWrkCtrId.text() */
/*
&& _NJ_WIP.WrkCtrId != "WC-PL"
&& _NJ_WIP.WrkCtrId != "CPOSB"
&& _NJ_WIP.WrkCtrId != "DC-XX"
*/
tmpstr = edtWrkCtrId.text();
qr.query().dataSourceTable(tablenum(NJ_WIP)).clearRanges();
qr.query().dataSourceTable(tablenum(NJ_WIP)).addRange(fieldnum(NJ_WIP,WrkCtrId)).value(tmpstr);
qr.query().dataSourceTable(tablenum(NJ_WIP)).addRange(fieldnum(NJ_WIP,ProdId)).value(_ProdTable.ProdId);
qr.reset();
while (Qr.next())
{
_NJ_WIP = qr.get(tablenum(NJ_WIP));
If (_NJ_WIP.Qty != 0)
{
If (cbxCancel.checked())
{
If (_ProdTable.ACT_ProdCancelId != '')
{
ACT_tmpProductionWIPMaterial.clear();
ACT_tmpProductionWIPMaterial.ProdId = _ProdTable.ProdId;
ACT_tmpProductionWIPMaterial.refMaterials = "Cancelled";
ACT_tmpProductionWIPMaterial.SalesId = _ProdTable.SalesId;
ACT_tmpProductionWIPMaterial.CustAccount = _ProdTable.CustAccount;
ACT_tmpProductionWIPMaterial.insert();
element.checkSum(_ProdTable.ProdId);
continue;
}
}
else
If (_ProdTable.ACT_ProdCancelId)
continue;
ACT_tmpProductionWIPMaterial.clear();
ACT_tmpProductionWIPMaterial.ItemId = _NJ_WIP.ItemId;
ACT_tmpProductionWIPMaterial.ConfigId = _NJ_WIP.ConfigId;
ACT_tmpProductionWIPMaterial.InventSizeId = _NJ_WIP.InventSizeId;
ACT_tmpProductionWIPMaterial.InventColorId = _NJ_WIP.InventColorId;
ACT_tmpProductionWIPMaterial.ACT_QtySec = _NJ_WIP.QtySec;
ACT_tmpProductionWIPMaterial.WrkCtrId = _NJ_WIP.WrkCtrId;
ACT_tmpProductionWIPMaterial.ProdId = _ProdTable.ProdId;
ACT_tmpProductionWIPMaterial.Qty = _NJ_WIP.Qty;
ACT_tmpProductionWIPMaterial.refMaterials = _ProdTable.ACT_ProdCancelId;
ACT_tmpProductionWIPMaterial.UnitID = _NJ_WIP.UnitID;
ACT_tmpProductionWIPMaterial.UnitIDSec = _NJ_WIP.UnitIDSec;
ACT_tmpProductionWIPMaterial.SalesId = _ProdTable.SalesId;
ACT_tmpProductionWIPMaterial.CustAccount = _ProdTable.CustAccount;
ACT_tmpProductionWIPMaterial.PODeliveryDate = _ProdTable.DlvDate;
If (BOMCostGroup::find(InventTable::find(_NJ_WIP.ItemId).CostGroupId).ACT_JewelMaterial == ACT_JewelMaterial::Metal)
{
If (_NJ_WIP.Qty > 0.0009)
{
ACT_tmpProductionWIPMaterial.Qty = decRound(_NJ_WIP.Qty,3);
ACT_tmpProductionWIPMaterial.insert();
element.checkSum(_ProdTable.ProdId);
}
}
else
ACT_tmpProductionWIPMaterial.insert();
element.checkSum(_ProdTable.ProdId);
}
}
ctr++;
simpleProgress.incCount();
// sleep(1000);
}
If (IsDelay)
Sleep(18000);
If (cbxInTransit.checked())
Delete_From ACT_tmpProductionWIPMaterial Where ACT_tmpProductionWIPMaterial.CostPrice != 0;
NJ_FormLog::UpdateLog(_LogID,edtWrkCtrId.text());
ACT_tmpProductionWIPMaterial_ds.executeQuery();
InTransit_ds.executeQuery();
// Select ACT_tmpProductionWIPMaterial Where ACT_tmpProductionWIPMaterial.WrkCtrId == edtWrkCtrId.text();
edtTime.text(strfmt("%1 seconds with %2 Production Order(s) executed", timenow()-startTime,ctr));
element.unlock();
}
