370 lines
13 KiB
ObjectPascal
370 lines
13 KiB
ObjectPascal
unit MaterialData;
|
||
|
||
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
|
||
TMaterialData = class(TwItem)
|
||
private
|
||
{ Private declarations }
|
||
FOrdNum: string;
|
||
FOrdCode: string;
|
||
FStandard: string;
|
||
FKind: string;
|
||
FStatus: string;
|
||
FDescriptions: string;
|
||
FInQty: string;
|
||
FOutQty: string;
|
||
FStockQty: string;
|
||
FUnitt: string;
|
||
FOrdDate: string;
|
||
FCustName: string;
|
||
FMatType: string;
|
||
|
||
FqInMatList: TList;
|
||
protected
|
||
{ Protected declarations }
|
||
public
|
||
{ Public declarations }
|
||
property qInMatList: TList read FqInMatList write FqInMatList;
|
||
|
||
constructor Create(AOwner: TObject);// override;
|
||
destructor Destroy; override;
|
||
class function xAddSqlMark(velStr: string): string;
|
||
class function xAddSqlDoubleMark(velStr: String): String;
|
||
class function xGetStrSqlMatDataToPurPlanList(AQuery: TFDQuery; stock, stDate, enDate: string): TList;
|
||
class function xGetStrSqlMaterialDataList(AQuery: TFDQuery; kind, cust, stock, tKind, stDate, enDate: string): TList;
|
||
class function LoadFromQuery(AQuery: TFDQuery): TList;
|
||
class function LoadFromSingleQuery(AQuery: TFDQuery): TList;
|
||
class function LoadFromStringQuery(AQuery: TFDQuery): string;
|
||
class function LoadFromStringListQuery(AQuery: TFDQuery; colName: string): TStrings;
|
||
|
||
class function xGetStrSumStockQty(AQuery: TFDQuery; code, mtype: string): string;
|
||
class function xGetStrsQueryToAnswer(AQuery: TFDQuery; colName: string): TStrings;
|
||
published
|
||
{ Published declarations }
|
||
property OrdNum: string read FOrdNum write FOrdNum;
|
||
property OrdCode: string read FOrdCode write FOrdCode;
|
||
property Standard: string read FStandard write FStandard;
|
||
property Kind: string read FKind write FKind;
|
||
property Status: string read FStatus write FStatus;
|
||
property Descriptions: string read FDescriptions write FDescriptions;
|
||
property InQty: string read FInQty write FInQty;
|
||
property OutQty: string read FOutQty write FOutQty;
|
||
property StockQty: string read FStockQty write FStockQty;
|
||
property Unitt: string read FUnitt write FUnitt;
|
||
property OrdDate: string read FOrdDate write FOrdDate;
|
||
property CustName: string read FCustName write FCustName;
|
||
property MatType: string read FMatType write FMatType;
|
||
end;
|
||
|
||
implementation
|
||
|
||
uses
|
||
MaterialDataPurPlanR;
|
||
|
||
{ TMaterialData }
|
||
|
||
constructor TMaterialData.Create(AOwner: TObject);
|
||
begin
|
||
FqInMatList := TList.Create;
|
||
end;
|
||
|
||
destructor TMaterialData.Destroy;
|
||
var
|
||
i: Integer;
|
||
tmpObj: TObject;
|
||
begin
|
||
if FqInMatList <> nil then begin
|
||
i := 0;
|
||
while i < FqInMatList.Count do begin
|
||
tmpObj := FqInMatList.Items[i];
|
||
if tmpObj <> nil then tmpObj.Free;
|
||
tmpObj := nil;
|
||
FqInMatList.Delete(i);
|
||
end;
|
||
FqInMatList.Free; FqInMatList:=nil;
|
||
end;
|
||
|
||
inherited;
|
||
end;
|
||
|
||
class function TMaterialData.LoadFromQuery(AQuery: TFDQuery): TList;
|
||
var
|
||
i, idx, nidx: Integer;
|
||
valName, fieName, obidVal: string;
|
||
curStock, tmpStock: TMaterialData;
|
||
curRel: TMaterialDataPurPlanR;
|
||
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
|
||
tmpStock := TMaterialData(Result[i]);
|
||
if SameText(obidVal, tmpStock.OBID) then begin
|
||
idx := i;
|
||
Break;
|
||
end;
|
||
end;
|
||
|
||
if idx = -1 then begin
|
||
curStock := TMaterialData.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 curStock.OBID := valName
|
||
else if SameText(fieName, 'Name') then curStock.Name := valName
|
||
else if SameText(fieName, 'wClassName') then curStock.wClassName := valName
|
||
else if SameText(fieName, 'CreateDate') then curStock.CreateDate := valName
|
||
else if SameText(fieName, 'CreateTime') then curStock.CreateTime := valName
|
||
else if SameText(fieName, 'CreatorID') then curStock.CreatorID := valName
|
||
else if SameText(fieName, 'IsValid') then curStock.IsValid := valName
|
||
else if SameText(fieName, 'LcStatus') then curStock.LcStatus := valName
|
||
else if SameText(fieName, 'OrdNum') then curStock.OrdNum := valName
|
||
else if SameText(fieName, 'OrdCode') then curStock.OrdCode := valName
|
||
else if SameText(fieName, 'Standard') then curStock.Standard := valName
|
||
else if SameText(fieName, 'Kind') then curStock.Kind := valName
|
||
else if SameText(fieName, 'Status') then curStock.Status := valName
|
||
else if SameText(fieName, 'Descriptions') then curStock.Descriptions := valName
|
||
else if SameText(fieName, 'InQty') then curStock.InQty := valName
|
||
else if SameText(fieName, 'OutQty') then curStock.OutQty := valName
|
||
else if SameText(fieName, 'StockQty') then curStock.StockQty := valName
|
||
else if SameText(fieName, 'Unitt') then curStock.Unitt := valName
|
||
else if SameText(fieName, 'OrdDate') then curStock.OrdDate := valName
|
||
else if SameText(fieName, 'CustName') then curStock.CustName := valName
|
||
else if SameText(fieName, 'MatType') then curStock.MatType := valName;
|
||
end;
|
||
Result.Add(curStock);
|
||
end else begin
|
||
curStock := TMaterialData(Result.Items[idx]);
|
||
end;
|
||
|
||
curRel := TMaterialDataPurPlanR.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 curRel.OBID := valName
|
||
else if SameText(fieName, 'Name') then curRel.Name := valName
|
||
else if SameText(fieName, 'wClassName') then curRel.wClassName := valName
|
||
else if SameText(fieName, 'CreateDate') then curRel.CreateDate := valName
|
||
else if SameText(fieName, 'CreateTime') then curRel.CreateTime := valName
|
||
else if SameText(fieName, 'CreatorID') then curRel.CreatorID := valName
|
||
else if SameText(fieName, 'IsValid') then curRel.IsValid := valName
|
||
else if SameText(fieName, 'Revision') then curRel.Revision := valName
|
||
else if SameText(fieName, 'LOBID') then curRel.LOBID := valName
|
||
else if SameText(fieName, 'ROBID') then curRel.ROBID := valName
|
||
else if SameText(fieName, 'LClassName') then curRel.LClassName := valName
|
||
else if SameText(fieName, 'RClassName') then curRel.RClassName := valName
|
||
else if SameText(fieName, 'OrdDate') then curRel.OrdDate := valName
|
||
else if SameText(fieName, 'Qty') then curRel.Qty := valName
|
||
else if SameText(fieName, 'Unitt') then curRel.Unitt := valName
|
||
else if SameText(fieName, 'Kind') then curRel.Kind := valName;
|
||
end;
|
||
curStock.qInMatList.Add(curRel);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TMaterialData.LoadFromSingleQuery(AQuery: TFDQuery): TList;
|
||
var
|
||
curMat: TMaterialData;
|
||
begin
|
||
Result := TList.Create;
|
||
while not AQuery.Eof do begin
|
||
curMat := TMaterialData.Create(nil);
|
||
curMat.OBID := AQuery.FieldByName('OBID').AsString;
|
||
curMat.Name := AQuery.FieldByName('Name').AsString;
|
||
curMat.wClassName := AQuery.FieldByName('wClassName').AsString;
|
||
curMat.CreateDate := AQuery.FieldByName('CreateDate').AsString;
|
||
curMat.CreateTime := FormatDateTime('hh:nn:ss', AQuery.FieldByName('CreateTime').AsDateTime);
|
||
curMat.CreatorID := AQuery.FieldByName('CreatorID').AsString;
|
||
curMat.IsValid := AQuery.FieldByName('IsValid').AsString;
|
||
curMat.LcStatus := AQuery.FieldByName('LcStatus').AsString;
|
||
|
||
curMat.OrdNum := AQuery.FieldByName('OrdNum').AsString;
|
||
curMat.OrdCode := AQuery.FieldByName('OrdCode').AsString;
|
||
curMat.Standard := AQuery.FieldByName('Standard').AsString;
|
||
curMat.Kind := AQuery.FieldByName('Kind').AsString;
|
||
curMat.Status := AQuery.FieldByName('Status').AsString;
|
||
curMat.Descriptions := AQuery.FieldByName('Descriptions').AsString;
|
||
curMat.InQty := AQuery.FieldByName('InQty').AsString;
|
||
curMat.OutQty := AQuery.FieldByName('OutQty').AsString;
|
||
curMat.StockQty := AQuery.FieldByName('StockQty').AsString;
|
||
curMat.Unitt := AQuery.FieldByName('Unitt').AsString;
|
||
curMat.OrdDate := AQuery.FieldByName('OrdDate').AsString;
|
||
curMat.CustName := AQuery.FieldByName('CustName').AsString;
|
||
curMat.MatType := AQuery.FieldByName('MatType').AsString;
|
||
Result.Add(curMat);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TMaterialData.LoadFromStringListQuery(
|
||
AQuery: TFDQuery; colName: string): TStrings;
|
||
var
|
||
idx: Integer;
|
||
tmpStr: string;
|
||
begin
|
||
Result := TStringList.Create;
|
||
while not AQuery.Eof do begin
|
||
tmpStr := AQuery.FieldByName(colName).AsString;
|
||
idx := Result.IndexOf(tmpStr);
|
||
if idx = -1 then Result.Add(tmpStr);
|
||
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TMaterialData.LoadFromStringQuery(AQuery: TFDQuery): string;
|
||
begin
|
||
Result := '';
|
||
while not AQuery.Eof do begin
|
||
Result := AQuery.FieldByName('balance').AsString;
|
||
AQuery.Next;
|
||
end;
|
||
end;
|
||
|
||
class function TMaterialData.xAddSqlDoubleMark(velStr: String): String;
|
||
begin
|
||
Result := '';
|
||
Result := '"' + velStr + '"';
|
||
end;
|
||
|
||
class function TMaterialData.xAddSqlMark(velStr: string): string;
|
||
begin
|
||
Result := '';
|
||
Result := '''' + velStr + '''';
|
||
end;
|
||
|
||
class function TMaterialData.xGetStrSqlMatDataToPurPlanList(AQuery: TFDQuery;
|
||
stock, stDate, enDate: string): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT obj0.*, obj1.* FROM "MaterialData"obj0, "MaterialDataPurPlanR"obj1, "PurchasePlan"obj2';
|
||
sql := sql + ' WHERE obj0."IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(stock)) > 0 then sql := sql + ' AND obj0."Name"=' + xAddSqlMark(stock);
|
||
sql := sql + ' AND obj1."IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' AND obj1."LOBID"=obj0."OBID"';
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND obj1."OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND obj1."OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
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('xGetStrSqlMatDataToPurPlanList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromQuery(AQuery);
|
||
end;
|
||
|
||
class function TMaterialData.xGetStrSqlMaterialDataList(AQuery: TFDQuery; kind, cust, stock, tKind,
|
||
stDate, enDate: string): TList;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT * FROM "MaterialData"';
|
||
sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(kind)) > 0 then sql := sql + ' AND "Kind"=' + xAddSqlMark(kind);
|
||
if Length(Trim(cust)) > 0 then sql := sql + ' AND "CustName"=' + xAddSqlMark(cust);
|
||
if Length(Trim(stock)) > 0 then sql := sql + ' AND "OrdCode"=' + xAddSqlMark(stock);
|
||
if Length(Trim(tKind)) > 0 then sql := sql + ' AND "MatType"=' + xAddSqlMark(tKind);
|
||
if Length(Trim(stDate)) > 0 then begin
|
||
sql := sql + ' AND "OrdDate">=' + xAddSqlMark(stDate);
|
||
sql := sql + ' AND "OrdDate"<=' + xAddSqlMark(enDate);
|
||
end;
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrSqlMaterialDataList <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromSingleQuery(AQuery);
|
||
end;
|
||
|
||
class function TMaterialData.xGetStrsQueryToAnswer(AQuery: TFDQuery;
|
||
colName: string): TStrings;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := nil;
|
||
sql := 'SELECT ' + xAddSqlDoubleMark(colName) + ' FROM "MaterialData"';
|
||
sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
sql := sql + ' Group By ' + xAddSqlDoubleMark(colName);
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrsQueryToAnswer <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromStringListQuery(AQuery, colName);
|
||
end;
|
||
|
||
class function TMaterialData.xGetStrSumStockQty(AQuery: TFDQuery;
|
||
code, mtype: string): string;
|
||
var
|
||
sql: string;
|
||
begin
|
||
Result := '';
|
||
|
||
{ 20251103_<33><5F><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
||
sql := 'SELECT SUM("InQty") - SUM("OutQty") AS balance FROM "MaterialData"';
|
||
sql := sql + ' WHERE "IsValid"=' + xAddSqlMark('t');
|
||
if Length(Trim(code)) > 0 then sql := sql + ' AND "OrdCode"=' + xAddSqlMark(code);
|
||
if Length(Trim(mtype)) > 0 then sql := sql + ' AND "MatType"=' + xAddSqlMark(mtype);
|
||
}
|
||
|
||
sql := 'SELECT * FROM "stock_summary_material"';
|
||
sql := sql + ' WHERE "item"=' + xAddSqlMark(code);
|
||
sql := sql + ' AND "mat_type"=' + xAddSqlMark(mtype);
|
||
|
||
try
|
||
AQuery.Close;
|
||
AQuery.SQL.Text := sql;
|
||
AQuery.Open;
|
||
except
|
||
on E: Exception do ShowMessage('xGetStrSumStockQty <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD> <20><><EFBFBD><EFBFBD>: ' + E.Message);
|
||
end;
|
||
Result := LoadFromStringQuery(AQuery);
|
||
end;
|
||
|
||
end.
|
||
|