743 lines
27 KiB
ObjectPascal
743 lines
27 KiB
ObjectPascal
unit OrderMst;
|
||
|
||
interface
|
||
|
||
uses
|
||
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
|
||
wItem,
|
||
FireDAC.Stan.Intf,
|
||
FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
|
||
FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
|
||
FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt,
|
||
FireDAC.Phys.PGDef, FireDAC.VCLUI.Wait, FireDAC.Comp.UI, FireDAC.Phys.PG,
|
||
Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client;
|
||
|
||
type
|
||
TOrderMst = class(TwItem)
|
||
private
|
||
{ Private declarations }
|
||
FOrdNum: string;
|
||
FOrdDate: string;
|
||
FCustName: string;
|
||
FConnPerson: string;
|
||
FDeliveryDate: string;
|
||
FWarehouse: string;
|
||
FQty: string;
|
||
FUnitt: string;
|
||
FUnitPrice: string;
|
||
FAmount: string;
|
||
FDescriptions: string;
|
||
FProcStatus: string;
|
||
FProcDesc: string;
|
||
FProcName: string;
|
||
|
||
FProcCost: string;
|
||
FOutInfo: string;
|
||
|
||
FOrdCode: string;
|
||
FMatKind: string;
|
||
FManufact: string;
|
||
FEtcAmo: string;
|
||
FPurDate: string;
|
||
FPurCust: string;
|
||
FPurUnitPrice: string;
|
||
FPurQty: string;
|
||
FPurAmo: string;
|
||
FPurDesc: string;
|
||
FIsAddInfo: string;
|
||
FProdCode: string;
|
||
|
||
FOutDay: string;
|
||
FTaxDay: string;
|
||
FInAmoDay: string;
|
||
FScheDay: string;
|
||
|
||
FqSubList: TList;
|
||
FqPlanList: TList;
|
||
protected
|
||
{ Protected declarations }
|
||
public
|
||
{ Public declarations }
|
||
property qSubList: TList read FqSubList write FqSubList;
|
||
property qPlanList: TList read FqPlanList write FqPlanList;
|
||
|
||
constructor Create(AOwner: TObject);// override;
|
||
destructor Destroy; override;
|
||
class function xAddSqlMark(velStr: string): string;
|
||
class function xGetStrSqlOrderToSubList(AQuery: TFDQuery; cust, status, stDate, enDate: string): TList;
|
||
class function xGetStrSqlOrderList(AQuery: TFDQuery; cust, status, stDate, enDate: string): TList;
|
||
class function LoadFromQuery(AQuery: TFDQuery): TList;
|
||
class function xGetObjsProdScheOrderList(AQuery: TFDQuery; scheStatus, stDate, enDate: string): TList;
|
||
class function LoadFromSingleQuery(AQuery: TFDQuery): TList;
|
||
function xGetObjsSubOrderList(AQuery: TFDQuery): TList;
|
||
function LoadFromQueryToSubOrder(AQuery: TFDQuery): TList;
|
||
|
||
class function xGetOrderMstLastNumber(AQuery: TFDQuery; stDate, enDate, column: string): string;
|
||
class function xGetOrderMstAllCount(AQuery: TFDQuery): string;
|
||
class function LoadFromStringQuery(AQuery: TFDQuery; column: string): TStrings;
|
||
|
||
procedure xGetObjsProductPlanList(AQuery: TFDQuery);
|
||
function LoadFromQueryToProductPlan(AQuery: TFDQuery): TList;
|
||
|
||
procedure xFreeAllList(var objList: TList);
|
||
class function xGetStrsDistinctCustName(AQuery: TFDQuery): TStrings;
|
||
|
||
class function xGetStrsCustNameBySumQty(AQuery: TFDQuery; cust, stDate, enDate: string): TStrings;
|
||
class function xGetStrsProdNameBySumQty(AQuery: TFDQuery; prod, stDate, enDate: string): TStrings;
|
||
class function LoadFromStringsQuery(AQuery: TFDQuery; columns: TStrings): TStrings;
|
||
|
||
class function xGetSameStrOrdCode(AQuery: TFDQuery; codeName: string): Boolean;
|
||
published
|
||
{ Published declarations }
|
||
property OrdNum: string read FOrdNum write FOrdNum;
|
||
property OrdDate: string read FOrdDate write FOrdDate;
|
||
property CustName: string read FCustName write FCustName;
|
||
property ConnPerson: string read FConnPerson write FConnPerson;
|
||
property DeliveryDate: string read FDeliveryDate write FDeliveryDate;
|
||
property Warehouse: string read FWarehouse write FWarehouse;
|
||
property Qty: string read FQty write FQty;
|
||
property Unitt: string read FUnitt write FUnitt;
|
||
property UnitPrice: string read FUnitPrice write FUnitPrice;
|
||
property Amount: string read FAmount write FAmount;
|
||
property Descriptions: string read FDescriptions write FDescriptions;
|
||
property ProcStatus: string read FProcStatus write FProcStatus;
|
||
property ProcDesc: string read FProcDesc write FProcDesc;
|
||
property ProcName: string read FProcName write FProcName;
|
||
|
||
property ProcCost: string read FProcCost write FProcCost;
|
||
property OutInfo: string read FOutInfo write FOutInfo;
|
||
|
||
property OrdCode: string read FOrdCode write FOrdCode;
|
||
property MatKind: string read FMatKind write FMatKind;
|
||
property Manufact: string read FManufact write FManufact;
|
||
property EtcAmo: string read FEtcAmo write FEtcAmo;
|
||
property PurDate: string read FPurDate write FPurDate;
|
||
property PurCust: string read FPurCust write FPurCust;
|
||
property PurUnitPrice: string read FPurUnitPrice write FPurUnitPrice;
|
||
property PurQty: string read FPurQty write FPurQty;
|
||
property PurAmo: string read FPurAmo write FPurAmo;
|
||
property PurDesc: string read FPurDesc write FPurDesc;
|
||
property IsAddInfo: string read FIsAddInfo write FIsAddInfo;
|
||
property ProdCode: string read FProdCode write FProdCode;
|
||
|
||
property OutDay: string read FOutDay write FOutDay;
|
||
property TaxDay: string read FTaxDay write FTaxDay;
|
||
property InAmoDay: string read FInAmoDay write FInAmoDay;
|
||
property ScheDay: string read FScheDay write FScheDay;
|
||
end;
|
||
|
||
implementation
|
||
|
||
uses
|
||
SubOrder, ProductPlan;
|
||
|
||
{ TOrderMst }
|
||
|
||
constructor TOrderMst.Create(AOwner: TObject);
|
||
begin
|
||
FPurDesc := '';
|
||
|
||
FqSubList := TList.Create;
|
||
FqPlanList := TList.Create;
|
||
end;
|
||
|
||
destructor TOrderMst.Destroy;
|
||
var
|
||
i: Integer;
|
||
tmpObj: TObject;
|
||
begin
|
||
if FqSubList <> nil then xFreeAllList(FqSubList);
|
||
if FqPlanList <> nil then xFreeAllList(FqPlanList);
|
||
|
||
inherited;
|
||
end;
|
||
|
||
class function TOrderMst.LoadFromQuery(AQuery: TFDQuery): TList;
|
||
var
|
||
i, idx, nidx: Integer;
|
||
valName, fieName, obidVal: string;
|
||
curOrd, tmpOrd: TOrderMst;
|
||
curSub: TSubOrder;
|
||
begin
|
||
Result := TList.Create;
|
||
|
||
AQuery.First;
|
||
//<2F>ߺ<EFBFBD> <20>÷<EFBFBD> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> ã<><C3A3>..
|
||
nidx := -1;
|
||
for i := 0 to AQuery.FieldCount-1 do begin
|
||
fieName := AQuery.Fields[i].FieldName;
|
||
if Pos('_1',fieName) > 0 then begin
|
||
nidx := i;
|
||
Break;
|
||
end;
|
||
end;
|
||
|
||
AQuery.First;
|
||
while not AQuery.Eof do begin
|
||
obidVal := AQuery.FieldByName('OBID').AsString;
|
||
//<2F>ߺ<EFBFBD> <20><>ü ã<><C3A3>..
|
||
idx := -1;
|
||
for i := 0 to Result.Count - 1 do begin
|
||
tmpOrd := TOrderMst(Result[i]);
|
||
if SameText(obidVal, tmpOrd.OBID) then begin
|
||
idx := i;
|
||
Break;
|
||
end;
|
||
end;
|
||
|
||
if idx = -1 then begin
|
||
curOrd := TOrderMst.Create(nil);
|
||
for i := 0 to nidx-1 do begin
|
||
fieName := AQuery.Fields[i].FieldName;
|
||
valName := AQuery.Fields[i].AsString;
|
||
if SameText(fieName, 'OBID') then curOrd.OBID := valName
|
||
else if SameText(fieName, 'Name') then curOrd.Name := valName
|
||
else if SameText(fieName, 'wClassName') then curOrd.wClassName := valName
|
||
else if SameText(fieName, 'CreateDate') then curOrd.CreateDate := valName
|
||
else if SameText(fieName, 'CreateTime') then curOrd.CreateTime := valName
|
||
else if SameText(fieName, 'CreatorID') then curOrd.CreatorID := valName
|
||
else if SameText(fieName, 'IsValid') then curOrd.IsValid := valName
|
||
else if SameText(fieName, 'LcStatus') then curOrd.LcStatus := valName
|
||
|
||
else if SameText(fieName, 'OrdNum') then curOrd.OrdNum := valName
|
||
else if SameText(fieName, 'OrdDate') then curOrd.OrdDate := valName
|
||
else if SameText(fieName, 'CustName') then curOrd.CustName := valName
|
||
else if SameText(fieName, 'ConnPerson') then curOrd.ConnPerson := valName
|
||
else if SameText(fieName, 'DeliveryDate') then curOrd.DeliveryDate := valName
|
||
else if SameText(fieName, 'Warehouse') then curOrd.Warehouse := valName
|
||
else if SameText(fieName, 'Qty') then curOrd.Qty := valName
|
||
else if SameText(fieName, 'Unitt') then curOrd.Unitt := valName
|
||
else if SameText(fieName, 'UnitPrice') then curOrd.UnitPrice := valName
|
||
else if SameText(fieName, 'Amount') then curOrd.Amount := valName
|
||
else if SameText(fieName, 'Descriptions') then curOrd.Descriptions := valName
|
||
else if SameText(fieName, 'ProcStatus') then curOrd.ProcStatus := valName
|
||
else if SameText(fieName, 'ScheDay') then curOrd.ScheDay := valName
|
||
else if SameText(fieName, 'ProcName') then curOrd.ProcName := valName
|
||
else if SameText(fieName, 'ProcDesc') then curOrd.ProcDesc := valName
|
||
|
||
else if SameText(fieName, 'ProcCost') then curOrd.ProcCost := valName
|
||
else if SameText(fieName, 'OutInfo') then curOrd.OutInfo := valName
|
||
else if SameText(fieName, 'OutDay') then curOrd.OutDay := valName
|
||
else if SameText(fieName, 'TaxDay') then curOrd.TaxDay := valName
|
||
else if SameText(fieName, 'InAmoDay') then curOrd.InAmoDay := valName
|
||
|
||
else if SameText(fieName, 'OrdCode') then curOrd.OrdCode := valName
|
||
else if SameText(fieName, 'MatKind') then curOrd.MatKind := valName
|
||
else if SameText(fieName, 'Manufact') then curOrd.Manufact := valName
|
||
else if SameText(fieName, 'EtcAmo') then curOrd.EtcAmo := valName
|
||
else if SameText(fieName, 'PurDate') then curOrd.PurDate := valName
|
||
else if SameText(fieName, 'PurCust') then curOrd.PurCust := valName
|
||
else if SameText(fieName, 'PurUnitPrice') then curOrd.PurUnitPrice := valName
|
||
else if SameText(fieName, 'PurQty') then curOrd.PurQty := valName
|
||
else if SameText(fieName, 'PurAmo') then curOrd.PurAmo := valName
|
||
else if SameText(fieName, 'PurDesc') then curOrd.PurDesc := valName
|
||
else if SameText(fieName, 'IsAddInfo') then curOrd.IsAddInfo := valName
|
||
else if SameText(fieName, 'ProdCode') then curOrd.ProdCode := valName;
|
||
end;
|
||
Result.Add(curOrd);
|
||
end else begin
|
||
curOrd := TOrderMst(Result.Items[idx]);
|
||
end;
|
||
|
||
curSub := TSubOrder.Create(nil);
|
||
for i := nidx to AQuery.FieldCount-1 do begin
|
||
fieName := StringReplace(AQuery.Fields[i].FieldName, '_1', '', [rfReplaceAll]);
|
||
valName := AQuery.Fields[i].AsString;
|
||
if SameText(fieName, 'OBID') then curSub.OBID := valName
|
||
else if SameText(fieName, 'Name') then curSub.Name := valName
|
||
else if SameText(fieName, 'wClassName') then curSub.wClassName := valName
|
||
else if SameText(fieName, 'CreateDate') then curSub.CreateDate := valName
|
||
else if SameText(fieName, 'CreateTime') then curSub.CreateTime := valName
|
||
else if SameText(fieName, 'CreatorID') then curSub.CreatorID := valName
|
||
else if SameText(fieName, 'IsValid') then curSub.IsValid := valName
|
||
else if SameText(fieName, 'LcStatus') then curSub.LcStatus := valName
|
||
|
||
else if SameText(fieName, 'OrdNum') then curSub.OrdNum := valName
|
||
else if SameText(fieName, 'OrdCode') then curSub.OrdCode := valName
|
||
else if SameText(fieName, 'Qty') then curSub.Qty := valName
|
||
else if SameText(fieName, 'Unitt') then curSub.Unitt := valName
|
||
else if SameText(fieName, 'UnitPrice') then curSub.UnitPrice := valName
|
||
else if SameText(fieName, 'Amount') then curSub.Amount := valName
|
||
else if SameText(fieName, 'OrdDate') then curSub.OrdDate := valName
|
||
else if SameText(fieName, 'MatKind') then curSub.MatKind := valName
|
||
else if SameText(fieName, 'Manufact') then curSub.Manufact := valName;
|
||
end;
|
||
curOrd.qSubList.Add(curSub);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
function TOrderMst.LoadFromQueryToProductPlan(AQuery: TFDQuery): TList;
|
||
var
|
||
curPlan: TProductPlan;
|
||
begin
|
||
Result := TList.Create;
|
||
while not AQuery.Eof do begin
|
||
curPlan := TProductPlan.Create(nil);
|
||
curPlan.OBID := AQuery.FieldByName('OBID').AsString;
|
||
curPlan.Name := AQuery.FieldByName('Name').AsString;
|
||
curPlan.wClassName := AQuery.FieldByName('wClassName').AsString;
|
||
curPlan.CreateDate := AQuery.FieldByName('CreateDate').AsString;
|
||
curPlan.CreateTime := FormatDateTime('hh:nn:ss', AQuery.FieldByName('CreateTime').AsDateTime);
|
||
curPlan.CreatorID := AQuery.FieldByName('CreatorID').AsString;
|
||
curPlan.IsValid := AQuery.FieldByName('IsValid').AsString;
|
||
curPlan.LcStatus := AQuery.FieldByName('LcStatus').AsString;
|
||
|
||
curPlan.OrdNum := AQuery.FieldByName('OrdNum').AsString;
|
||
curPlan.OrdCode := AQuery.FieldByName('OrdCode').AsString;
|
||
curPlan.ConnPerson := AQuery.FieldByName('ConnPerson').AsString;
|
||
curPlan.Descriptions := AQuery.FieldByName('Descriptions').AsString;
|
||
curPlan.MachName := AQuery.FieldByName('MachName').AsString;
|
||
curPlan.PlanDate := AQuery.FieldByName('PlanDate').AsString;
|
||
curPlan.StartMachDate := AQuery.FieldByName('StartMachDate').AsString;
|
||
curPlan.FinMachDate := AQuery.FieldByName('FinMachDate').AsString;
|
||
Result.Add(curPlan);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
function TOrderMst.LoadFromQueryToSubOrder(AQuery: TFDQuery): TList;
|
||
var
|
||
curSub: TSubOrder;
|
||
begin
|
||
while not AQuery.Eof do begin
|
||
curSub := TSubOrder.Create(nil);
|
||
curSub.OBID := AQuery.FieldByName('OBID').AsString;
|
||
curSub.Name := AQuery.FieldByName('Name').AsString;
|
||
curSub.wClassName := AQuery.FieldByName('wClassName').AsString;
|
||
curSub.CreateDate := AQuery.FieldByName('CreateDate').AsString;
|
||
curSub.CreateTime := FormatDateTime('hh:nn:ss', AQuery.FieldByName('CreateTime').AsDateTime);
|
||
curSub.CreatorID := AQuery.FieldByName('CreatorID').AsString;
|
||
curSub.IsValid := AQuery.FieldByName('IsValid').AsString;
|
||
curSub.LcStatus := AQuery.FieldByName('LcStatus').AsString;
|
||
|
||
curSub.OrdNum := AQuery.FieldByName('OrdNum').AsString;
|
||
curSub.OrdCode := AQuery.FieldByName('OrdCode').AsString;
|
||
curSub.Qty := AQuery.FieldByName('Qty').AsString;
|
||
curSub.Unitt := AQuery.FieldByName('Unitt').AsString;
|
||
curSub.UnitPrice := AQuery.FieldByName('UnitPrice').AsString;
|
||
curSub.Amount := AQuery.FieldByName('Amount').AsString;
|
||
curSub.OrdDate := AQuery.FieldByName('OrdDate').AsString;
|
||
curSub.MatKind := AQuery.FieldByName('MatKind').AsString;
|
||
curSub.Manufact := AQuery.FieldByName('Manufact').AsString;
|
||
qSubList.Add(curSub);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TOrderMst.LoadFromSingleQuery(AQuery: TFDQuery): TList;
|
||
var
|
||
curOrd: TOrderMst;
|
||
begin
|
||
Result := TList.Create;
|
||
while not AQuery.Eof do begin
|
||
curOrd := TOrderMst.Create(nil);
|
||
curOrd.OBID := AQuery.FieldByName('OBID').AsString;
|
||
curOrd.Name := AQuery.FieldByName('Name').AsString;
|
||
curOrd.wClassName := AQuery.FieldByName('wClassName').AsString;
|
||
curOrd.CreateDate := AQuery.FieldByName('CreateDate').AsString;
|
||
curOrd.CreateTime := FormatDateTime('hh:nn:ss', AQuery.FieldByName('CreateTime').AsDateTime);
|
||
curOrd.CreatorID := AQuery.FieldByName('CreatorID').AsString;
|
||
curOrd.IsValid := AQuery.FieldByName('IsValid').AsString;
|
||
curOrd.LcStatus := AQuery.FieldByName('LcStatus').AsString;
|
||
|
||
curOrd.OrdNum := AQuery.FieldByName('OrdNum').AsString;
|
||
curOrd.OrdDate := AQuery.FieldByName('OrdDate').AsString;
|
||
curOrd.CustName := AQuery.FieldByName('CustName').AsString;
|
||
curOrd.ConnPerson := AQuery.FieldByName('ConnPerson').AsString;
|
||
curOrd.DeliveryDate := AQuery.FieldByName('DeliveryDate').AsString;
|
||
curOrd.Warehouse := AQuery.FieldByName('Warehouse').AsString;
|
||
curOrd.Qty := AQuery.FieldByName('Qty').AsString;
|
||
curOrd.Unitt := AQuery.FieldByName('Unitt').AsString;
|
||
curOrd.UnitPrice := AQuery.FieldByName('UnitPrice').AsString;
|
||
curOrd.Amount := AQuery.FieldByName('Amount').AsString;
|
||
curOrd.Descriptions := AQuery.FieldByName('Descriptions').AsString;
|
||
curOrd.ProcStatus := AQuery.FieldByName('ProcStatus').AsString;
|
||
curOrd.ScheDay := AQuery.FieldByName('ScheDay').AsString;
|
||
curOrd.ProcDesc := AQuery.FieldByName('ProcDesc').AsString;
|
||
curOrd.ProcName := AQuery.FieldByName('ProcName').AsString;
|
||
|
||
curOrd.ProcCost := AQuery.FieldByName('ProcCost').AsString;
|
||
curOrd.OutInfo := AQuery.FieldByName('OutInfo').AsString;
|
||
curOrd.OutDay := AQuery.FieldByName('OutDay').AsString;
|
||
curOrd.TaxDay := AQuery.FieldByName('TaxDay').AsString;
|
||
curOrd.InAmoDay := AQuery.FieldByName('InAmoDay').AsString;
|
||
|
||
curOrd.OrdCode := AQuery.FieldByName('OrdCode').AsString;
|
||
curOrd.MatKind := AQuery.FieldByName('MatKind').AsString;
|
||
curOrd.Manufact := AQuery.FieldByName('Manufact').AsString;
|
||
curOrd.EtcAmo := AQuery.FieldByName('EtcAmo').AsString;
|
||
curOrd.PurDate := AQuery.FieldByName('PurDate').AsString;
|
||
curOrd.PurCust := AQuery.FieldByName('PurCust').AsString;
|
||
curOrd.PurUnitPrice := AQuery.FieldByName('PurUnitPrice').AsString;
|
||
curOrd.PurQty := AQuery.FieldByName('PurQty').AsString;
|
||
curOrd.PurAmo := AQuery.FieldByName('PurAmo').AsString;
|
||
curOrd.PurDesc := AQuery.FieldByName('PurDesc').AsString;
|
||
curOrd.IsAddInfo := AQuery.FieldByName('IsAddInfo').AsString;
|
||
curOrd.ProdCode := AQuery.FieldByName('ProdCode').AsString;
|
||
Result.Add(curOrd);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TOrderMst.LoadFromStringsQuery(AQuery: TFDQuery;
|
||
columns: TStrings): TStrings;
|
||
var
|
||
i: Integer;
|
||
valStr, sumval: string;
|
||
begin
|
||
Result := TStringList.Create;
|
||
AQuery.First;
|
||
while not AQuery.Eof do begin
|
||
sumval := '';
|
||
for i := 0 to columns.Count-1 do begin
|
||
valStr := AQuery.FieldByName(columns.Strings[i]).AsString;
|
||
if i = 0 then sumval := valStr
|
||
else sumval := sumval + ';' + valStr;
|
||
end;
|
||
Result.Add(sumval);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TOrderMst.LoadFromStringQuery(AQuery: TFDQuery;
|
||
column: string): TStrings;
|
||
var
|
||
valStr: string;
|
||
begin
|
||
Result := TStringList.Create;
|
||
while not AQuery.Eof do begin
|
||
valStr := AQuery.FieldByName(column).AsString;
|
||
Result.Add(valStr);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TOrderMst.xAddSqlMark(velStr: string): string;
|
||
begin
|
||
Result := '';
|
||
Result := '''' + velStr + '''';
|
||
end;
|
||
|
||
procedure TOrderMst.xFreeAllList(var objList: TList);
|
||
var
|
||
i: Integer;
|
||
tmpObj: TObject;
|
||
begin
|
||
if objList <> nil then begin
|
||
i := 0;
|
||
while i < objList.Count do begin
|
||
tmpObj := objList.Items[i];
|
||
if tmpObj <> nil then tmpObj.Free;
|
||
tmpObj := nil;
|
||
objList.Delete(i);
|
||
end;
|
||
objList.Free; objList:=nil;
|
||
end;
|
||
end;
|
||
|
||
class function TOrderMst.xGetObjsProdScheOrderList(AQuery: TFDQuery; scheStatus,
|
||
stDate, enDate: string): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT * FROM "OrderMst"';
|
||
sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(scheStatus)) > 0 then sql := sql + ' AND "ProcStatus"=' + xAddSqlMark(scheStatus);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND "ScheDay">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND "ScheDay"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetObjsProdScheOrderList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromSingleQuery(AQuery);
|
||
end;
|
||
|
||
procedure TOrderMst.xGetObjsProductPlanList(AQuery: TFDQuery);
|
||
var
|
||
sql: string;
|
||
begin
|
||
sql := 'SELECT obj2.* FROM "OrderMst"obj0, "OrderMstProdPlanR"obj1, "ProductPlan"obj2';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj0."OBID"=' + xAddSqlMark(OBID);
|
||
sql := sql + ' AND obj1."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj1."LOBID"=obj0."OBID"';
|
||
sql := sql + ' AND obj2."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj2."OBID"=obj1."ROBID"';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetObjsProductPlanList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
if FqPlanList <> nil then xFreeAllList(FqPlanList);
|
||
FqPlanList := LoadFromQueryToProductPlan(AQuery);
|
||
end;
|
||
|
||
function TOrderMst.xGetObjsSubOrderList(AQuery: TFDQuery): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT obj2.* FROM "OrderMst"obj0, "OrderMstSubR"obj1, "SubOrder"obj2';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj0."OBID"=' + xAddSqlMark(OBID);
|
||
sql := sql + ' AND obj1."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj1."LOBID"=obj0."OBID"';
|
||
sql := sql + ' AND obj2."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj2."OBID"=obj1."ROBID"';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetObjsSubOrderList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromQueryToSubOrder(AQuery);
|
||
end;
|
||
|
||
class function TOrderMst.xGetOrderMstAllCount(AQuery: TFDQuery): string;
|
||
var
|
||
sql, column: string;
|
||
resStrs: TStrings;
|
||
begin
|
||
Result := '';
|
||
sql := 'SELECT COUNT(*) FROM "OrderMst"';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetOrderMstAllCount <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
|
||
column := 'COUNT';
|
||
resStrs := LoadFromStringQuery(AQuery, column);
|
||
if resStrs.Count > 0 then begin
|
||
Result := resStrs.Strings[0];
|
||
end;
|
||
resStrs.Free;
|
||
end;
|
||
|
||
class function TOrderMst.xGetOrderMstLastNumber(AQuery: TFDQuery; stDate, enDate, column: string): string;
|
||
var
|
||
sql: string;
|
||
resStrs: TStrings;
|
||
begin
|
||
Result := '';
|
||
sql := 'SELECT COUNT(*) FROM "OrderMst"';
|
||
//sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' WHERE';
|
||
sql := sql + ' "OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND "OrdDate"<=' + xAddSqlMark(enDate);
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetOrderMstLastNumber <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
resStrs := LoadFromStringQuery(AQuery, column);
|
||
if resStrs.Count > 0 then begin
|
||
Result := resStrs.Strings[0];
|
||
end;
|
||
resStrs.Free;
|
||
end;
|
||
|
||
class function TOrderMst.xGetSameStrOrdCode(AQuery: TFDQuery;
|
||
codeName: string): Boolean;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := False;
|
||
sql := 'SELECT obj0.* FROM "OrderMst"obj0';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(codeName)) > 0 then sql := sql + ' AND obj0."OrdCode"=' + xAddSqlMark(codeName);
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetSameStrOrdCode <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
|
||
Result := AQuery.RecordCount > 0;
|
||
end;
|
||
|
||
class function TOrderMst.xGetStrsCustNameBySumQty(AQuery: TFDQuery; cust, stDate, enDate: string): TStrings;
|
||
var
|
||
sql: string;
|
||
columns: TStrings;
|
||
begin
|
||
sql := 'SELECT "CustName" AS name,';
|
||
sql := sql + ' TO_CHAR(DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate"), ' + xAddSqlMark('YYYY-MM') + ') AS mon,';
|
||
// sql := sql + ' TO_CHAR("OrdDate", ' + xAddSqlMark('FMMon') + ') || ' + xAddSqlMark('<27><>') + ' AS montext,';
|
||
// <20><>ǥ<EFBFBD><C7A5>: date_trunc <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <20>Ļ<EFBFBD> (GROUP BY <20><><EFBFBD><EFBFBD>)
|
||
sql := sql + ' TO_CHAR(DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate"), '
|
||
+ xAddSqlMark('FMMM') + ') || ' + xAddSqlMark('<27><>') + ' AS montext,';
|
||
|
||
sql := sql + ' SUM("Amount") AS sumqty';
|
||
sql := sql + ' FROM "OrderMst"';
|
||
sql := sql + ' WHERE "IsValid" =' + xAddSqlMark('t');
|
||
if Length(cust) > 0 then sql := sql + ' AND "CustName" =' + xAddSqlMark(cust);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND "OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND "OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
|
||
sql := sql + ' GROUP BY "CustName", DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate")';
|
||
// sql := sql + ' DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate")';
|
||
sql := sql + ' ORDER BY "CustName", mon';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrsCustNameBySumQty <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
columns := TStringList.Create;
|
||
columns.Add('name'); columns.Add('mon'); columns.Add('montext'); columns.Add('sumqty');
|
||
|
||
Result := LoadFromStringsQuery(AQuery, columns);
|
||
columns.Free;
|
||
end;
|
||
|
||
class function TOrderMst.xGetStrsDistinctCustName(AQuery: TFDQuery): TStrings;
|
||
var
|
||
sql, column: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT DISTINCT "CustName" FROM "OrderMst"';
|
||
sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrsDistinctCustName <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
column := 'CustName';
|
||
|
||
Result := LoadFromStringQuery(AQuery, column);
|
||
end;
|
||
|
||
class function TOrderMst.xGetStrsProdNameBySumQty(AQuery: TFDQuery; prod,
|
||
stDate, enDate: string): TStrings;
|
||
var
|
||
sql: string;
|
||
columns: TStrings;
|
||
begin
|
||
sql := 'SELECT "Name" AS name,';
|
||
sql := sql + ' TO_CHAR(DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate"), ' + xAddSqlMark('YYYY-MM') + ') AS mon,';
|
||
// <20><>ǥ<EFBFBD><C7A5>: date_trunc <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <20>Ļ<EFBFBD> (GROUP BY <20><><EFBFBD><EFBFBD>)
|
||
sql := sql + ' TO_CHAR(DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate"), '
|
||
+ xAddSqlMark('FMMM') + ') || ' + xAddSqlMark('<27><>') + ' AS montext,';
|
||
|
||
sql := sql + ' SUM("Amount") AS sumqty';
|
||
sql := sql + ' FROM "OrderMst"';
|
||
sql := sql + ' WHERE "IsValid" =' + xAddSqlMark('t');
|
||
if Length(prod) > 0 then sql := sql + ' AND "Name" =' + xAddSqlMark(prod);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND "OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND "OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
|
||
sql := sql + ' GROUP BY "Name", DATE_TRUNC(' + xAddSqlMark('month') + ', "OrdDate")';
|
||
sql := sql + ' ORDER BY "Name", mon';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrsProdNameBySumQty <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
columns := TStringList.Create;
|
||
columns.Add('name'); columns.Add('mon'); columns.Add('montext'); columns.Add('sumqty');
|
||
|
||
Result := LoadFromStringsQuery(AQuery, columns);
|
||
columns.Free;
|
||
end;
|
||
|
||
class function TOrderMst.xGetStrSqlOrderList(AQuery: TFDQuery; cust, status,
|
||
stDate, enDate: string): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT obj0.* FROM "OrderMst"obj0';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(cust)) > 0 then sql := sql + ' AND obj0."CustName"=' + xAddSqlMark(cust);
|
||
if Length(Trim(status)) > 0 then sql := sql + ' AND obj0."LcStatus"=' + xAddSqlMark(status);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND obj0."OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND obj0."OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
//sql := sql + ' ORDER BY obj0."OrdDate" DESC';
|
||
// sql := sql + ' ORDER BY split_part(obj0."OrdCode", ''-'', 2)::integer DESC';
|
||
sql := sql + ' ORDER BY obj0."OrdNum" DESC';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrSqlOrderList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromSingleQuery(AQuery);
|
||
end;
|
||
|
||
class function TOrderMst.xGetStrSqlOrderToSubList(AQuery: TFDQuery; cust,
|
||
status, stDate, enDate: string): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT obj0.*, obj2.* FROM "OrderMst"obj0, "OrderMstSubR"obj1, "SubOrder"obj2';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(cust)) > 0 then sql := sql + ' AND obj0."CustName"=' + xAddSqlMark(cust);
|
||
if Length(Trim(status)) > 0 then sql := sql + ' AND obj0."LcStatus"=' + xAddSqlMark(status);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND obj0."OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND obj0."OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
sql := sql + ' AND obj1."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj1."LOBID"=obj0."OBID"';
|
||
sql := sql + ' AND obj2."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj2."OBID"=obj1."ROBID"';
|
||
sql := sql + ' ORDER BY obj0."OrdDate" DESC';
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrSqlOrderToSubList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromQuery(AQuery);
|
||
end;
|
||
|
||
end.
|
||
|