📄 mrp_qry_oblongmps1_c.pas
字号:
unit Mrp_Qry_OblongMps1_C;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Condition, Db, AdODB, StdCtrls;
Type
TFrm_Mrp_Qry_OblongMps1_C = Class(TFrm_Base_Condition)
Edit1: TEdit;
Label1: TLabel;
procedure FormDestroy(Sender: TObject);
procedure btn_okClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure FormShow(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Frm_Mrp_Qry_OblongMps1_C: TFrm_Mrp_Qry_OblongMps1_C;
implementation
uses Public_Show, Mrp_Qry_OblongMps1, Sys_Global, Base_Common;
{$R *.DFM}
procedure TFrm_Mrp_Qry_OblongMps1_C.FormDestroy(
Sender: TObject);
begin
inherited;
Frm_Mrp_Qry_OblongMps1_C:=nil;
Frm_Public_Show.free;
end;
procedure TFrm_Mrp_Qry_OblongMps1_C.btn_okClick(
Sender: TObject);
var
I,J,x,y:Integer;
SqlText,SqlText1,SqlText2,SqlText3:String;
s,UpdateField1,aa:String;
begin
if length(Edit1.text)<>4 Then
begin
DispInfo('年份输入错误，格式为‘YYYY’！',3);
Edit1.SetFocus;
abort;
end;
Try
strtoint(Edit1.Text);
Except
DispInfo('年份输入错误，格式为‘YYYY’！',3);
Edit1.SetFocus;
abort;
end;
Frm_Mrp_Qry_OblongMps1.DbTxt_ItemFlag.DataField := '';
Application.ProcessMessages;
inherited;
Frm_Public_Show.Lbl_Show.Caption:='正在统计数据，请稍等......';
Frm_Public_Show.Visible:=True; //________________
Application.ProcessMessages;
Try
SqlText:=' Create Table #TmPmps ( '
+' ItemCode varChAr(20) Null,'
+' MpsQty varChAr(20) Null,'
+' MpsMonth varChAr(7) Null,';
For I:=1 To 11 Do
begin
SqlText:=SqlText+'['+IntToStr(I)+'月] Float(12) Null ,';
end;
SqlText:=SqlText+'['+IntToStr(12)+'月] Float(12) Null )';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
Except
Sqltext:=' Drop Table #TmPmps ';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
SqlText:=' Create Table #TmPmps ( '
+' ItemCode varChAr(20) Null,'
+' MpsQty varChAr(20) Null,'
+' MpsMonth varChAr(7) Null,';
For I:=1 To 11 Do
begin
SqlText:=SqlText+'['+IntToStr(I)+'月] Float(12) Null ,';
end;
SqlText:=SqlText+'['+IntToStr(12)+'月] Float(12) Null )';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
end;
Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......1%!';
Application.ProcessMessages; //________________
SQlText:=' Select ItemCode,left(MpsMonth,4) as MpsMonth,Sum(MpsQty) As MpsQty '
+' From Mps '
+' where Left(MpsMonth,4)='''+EDit1.text+''' '
+' Group By ItemCode,left(MpsMonth,4)';
With Frm_Mrp_Qry_OblongMps1 Do
begin
AdoQry_Main.Close;
AdoQry_Main.SQL.Text:=SqlText;
AdoQry_Main.Open;
X:=AdoQry_Main.RecordCount;
Y:=1;
While Not AdoQry_Main.Eof Do
begin
Application.ProcessMessages;
Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*40+1)+'%!';
Y:=Y+1;
Application.ProcessMessages; //_____________________
SqlText:=' Insert #TmPmps '
+' (MpsMonth,ItemCode,MpsQty) '
+' Values('
+''''+AdoQry_Main.fieldbyname('MpsMonth').AsString+''','
+''''+AdoQry_Main.fieldbyname('ItemCode').AsString+''','
+''''+FloatToStr(AdoQry_Main.fieldbyname('MpsQty').AsFloat)+''')';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
AdoQry_Main.Next;
end;
SqlText:=' Select ItemCode,MpsMonth as MpsMonth,Sum(MpsQty) As MpsQty '
+' From Mps '
+' where Left(MpsMonth,4)='''+EDit1.text+''''
+' Group By ItemCode,MpsMonth';
AdoQry_Main.Close;
AdoQry_Main.Prepared;
AdoQry_Main.SQL.Text:=SqlText;
AdoQry_Main.Open;
Y:=1;
X:=AdoQry_Main.RecordCount;
While Not AdoQry_Main.Eof Do
begin
Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*59+41)+'%!';
Application.ProcessMessages; //_____________________
Y:=Y+1;
s:=Copy(AdoQry_Main.fieldbyname('MpsMonth').AsString,6,7);
Case StrToInt(S) Of
1..12:begin
UpdateField1:='['+Inttostr(StrToInt(s))+'月]';
end;
end;
SqlText:=' Update #TmPmps '
+' Set '+UpdateField1+'='''+FloatToStr(AdoQry_Main.fieldbyname('MpsQty').AsFloat)+''''
+' Where ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').AsString+''''
+' And MpsMonth=left('''+AdoQry_Main.fieldbyname('MpsMonth').AsString+''',4)';
AdoQry_Tmp.Close;
AdoQry_Tmp.Prepared;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
AdoQry_Main.Next;
end;
//--------------
SqlText1:='Select ';
For I:=1 To 12 Do
begin
SqlText1:=SqlText1+' sum(['+IntToStr(I)+'月]) As ['+IntToStr(I)+'月] ,'
end;
SqlText1:=SqlText1+'MpsMonth from #TmPmps group by MpsMonth';
AdoQry_Tmp.Close;
AdoQry_Tmp.Prepared;
AdoQry_Tmp.SQL.Text:=SqlText1;
AdoQry_Tmp.open;
SqlText3:= 'Values(';
For I:=1 To 11 Do
begin
SqlText3:=SqlText3+''''+AdoQry_Tmp.fieldbyname(IntToStr(I)+'月').ASString+''',';
end;
SqlText3:=SqlText3+''''+AdoQry_Tmp.fieldbyname(IntToStr(12)+'月').ASString+''',';
sqlText3:=Sqltext3+''''+edit1.text+''',';
SqlText2:='insert #TmPmps ('; //
For I:=1 To 12 Do
begin
SqlText2:=SqlText2+'['+IntToStr(I)+'月] ,'
end;
SqlText2:=SqlText2+ 'MpsMonth ,MpsQty ) '+SqlText3+ '''合计：'''+' )';
AdoQry_Tmp.Close;
AdoQry_Tmp.Prepared;
AdoQry_Tmp.SQL.Text:=SqlText2;
AdoQry_Tmp.ExecSQL ;
//--------
SqlText:='Select ';
For I:=1 To 12 Do
begin
SqlText:=SqlText+' #TmPmps.['+IntToStr(I)+'月] As ['+IntToStr(I)+'月] ,'
end;
SqlText:=SqlText+' #TmPmps.ItemCode,#TmPmps.MpsQty,Item.ItemName,Uom.UomName,'
+' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag '
+' From #TmPmps '
+'left outer Join Item On #TmPmps.ItemCode=Item.ItemCode '
+' Left outer Join Uom On Item.UomCode=Uom.UomCode '
+' Where #TmPmps.MpsMonth='''+edit1.text+''' ';
AdoQry_Main.Close;
AdoQry_Main.Prepared;
AdoQry_Main.SQL.Text:=SqlText;
AdoQry_Main.Open;
DataSource.DataSet:=AdoQry_Main;
J:=5;
For I:=0 To AdoQry_Main.FieldCount -1 Do
begin
Application.ProcessMessages; //_____________________
If AdoQry_Main.Fields[I].DataType=FtFloat Then
Tfloatfield(AdoQry_Main.Fields[I]).DisplayFormat:='#,##0';
If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemCode') Then
AdoQry_Main.Fields[I].Origin:='#TmPmps'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
AdoQry_Main.Fields[I].Origin:='Item'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
AdoQry_Main.Fields[I].Origin:='Uom'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemFlag') Then
AdoQry_Main.Fields[I].Origin:=''
Else
AdoQry_Main.Fields[I].Origin:='#TmPmps';
If pos('月',AdoQry_Main.Fields[I].DisplayName)<>0 Then
begin
DBGridEh.Columns.Add;
DBGridEh.Columns[J].Width:=50;
DBGridEh.Columns[J].FieldName:=AdoQry_Main.Fields[I].DisplayName;
DBGridEh.Columns[J].Title.Caption:=AdoQry_Main.Fields[I].DisplayName;
DBGridEh.Columns[J].Title.Alignment:=TaCenter;
J:=J+1;
end;
end;
Frm_Public_Show.Visible:=False;
Application.ProcessMessages;
DbGridEh.FrozenCols:=2;
DbTxt_ItemFlag.DataField:='ItemFlag';
end;
conditionHint:='年份:'+ Edit1.Text;
Frm_Mrp_Qry_OblongMps1.conditionHint1:=conditionHint;
SetFormParam('','','','','','');
Condition:='none';
self.ModalResult:=mrOk;
end;
procedure TFrm_Mrp_Qry_OblongMps1_C.FormCreate(Sender: TObject);
begin
inherited;
Edit1.Text:=FormatDateTime('YYYY',date);
Frm_Public_Show:=TFrm_Public_Show.Create(self);
end;
procedure TFrm_Mrp_Qry_OblongMps1_C.FormShow(Sender: TObject);
begin
inherited;
Edit1.setfocus;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -