📄 mrp_qry_psidate.pas
字号:
{ SqlText:=' Update #TmpOblongdayMps '
+' Set '+UpdateField+'=isnull('+UpdateField1+',0)+isnull('+UpdateField+',0) '
+' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
+' And falg=6 ';
AdoQuery:=TAdoQuery.Create(nil);
AdoQuery.Connection :=DbConnect;
AdoQuery.Close;
AdoQuery.Prepared;
AdoQuery.SQL.Text:=SqlText;
AdoQuery.ExecSQL;
end;
AdoQry_Tmp.next;
Y:=Y+1;
Application.ProcessMessages;
end;
SqlText:='Select ';
For I:=1 To 31 Do
begin
// SqlText:=SqlText+' Sum(#TmpOblongdayMps.['+IntToStr(I)+'日]) As ['+IntToStr(I)+'日] ,'
SqlText:=SqlText+' #TmpOblongdayMps.['+IntToStr(I)+'日] As ['+IntToStr(I)+'日] ,' // Sum(MonthTotalQty) As
end;
SqlText:=SqlText+' #TmpOblongdayMps.xsbz,#TmpOblongdayMps.ItemCode, #TmpOblongdayMps.MonthTotalQty ,rpz,falg,falg1,Item.ItemName,Uom.UomName, '
+' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag '
+' into #tmpdayMps '
+' From #TmpOblongdayMps '
+' left join Item on #TmpOblongdayMps.ItemCode=Item.ItemCode '
+' left join Uom on Item.UomCode=Uom.UomCode '
// +' Group By #TmpOblongdayMps.ItemCode,Item.ItemName,Uom.UomName,#TmpOblongdayMps.MpsMonth,Item.ItemCode+'''+' '+'''+Item.ItemName '
+' Order By #TmpOblongdayMps.ItemCode ,#TmpOblongdayMps.falg '; // ,#TmpOblongdayMps.ItemCode
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''主生产计划'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 1)';
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''生产入库'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 2)';
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''其他入库'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 3)';
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''销售出库'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 4)';
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''其它出库'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 5)';
Executesql(AdoQry_Tmp,SqlText,1);
SqlText:=' Insert #tmpdayMps '
+' (xsbz,ItemFlag,ItemCode,falg1,falg) '
+' Values('
+'''期末结存'','
+'''汇总'','
+''' 汇总'','
+''''+inttostr(0)+''','
+' 6)';
Executesql(AdoQry_Tmp,SqlText,1);
//更新汇总
//主生产计划
X:=31;
Y:=1;
for I:=1 to 31 do
begin
Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*30+70)+'%!';
Case I Of
1..9:UpdateField:='['+Inttostr(I)+'日]';
10..31:UpdateField:='['+Inttostr(I)+'日]';
end;
SqlText:=' Select Falg,Sum('+UpdateField+') As MpsQty ' //,'+UpdateField+'
+' From #tmpdayMps '
+' Group By falg '; //,'+UpdateField+'
AdoQry_Tmp.Close;
AdoQry_Tmp.Prepared;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.Open;
While Not AdoQry_Tmp.Eof Do
begin
Application.ProcessMessages; //_____________________
SqlText:=' Update #tmpdayMps '
+' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
+' Where falg1=0 '
+' And falg='''+AdoQry_Tmp.fieldbyname('falg').AsString+'''';
AdoQuery:=TAdoQuery.Create(nil);
AdoQuery.Connection :=DbConnect;
AdoQuery.Close;
AdoQuery.Prepared;
AdoQuery.SQL.Text:=SqlText;
AdoQuery.ExecSQL;
AdoQry_Tmp.Next;
end;
Y:=Y+1;
Application.ProcessMessages;
end;
SqlText:=' Select Falg,Sum(MonthTotalQty) As MpsQty ' //MonthTotalQty,
+' From #tmpdayMps where falg<>6 '
+' Group By falg '; //,MonthTotalQty
AdoQry_Tmp.Close;
AdoQry_Tmp.Prepared;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.Open;
While Not AdoQry_Tmp.Eof Do
begin
Application.ProcessMessages; //_____________________
SqlText:=' Update #tmpdayMps '
+' Set MonthTotalQty='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
+' Where falg1=0 '
+' And falg='''+AdoQry_Tmp.fieldbyname('falg').AsString+'''';
AdoQuery:=TAdoQuery.Create(nil);
AdoQuery.Connection :=DbConnect;
AdoQuery.Close;
AdoQuery.Prepared;
AdoQuery.SQL.Text:=SqlText;
AdoQuery.ExecSQL;
AdoQry_Tmp.Next;
end;
//---------
SqlText:=' Update #tmpdayMps '
+' Set rpz=MonthTotalQty/'+inttostr(sjdate)+' where falg<>6' ;
Executesql(AdoQry_Tmp,sqltext,1); }
SelectFromSQL:=' select * from PsiMonth ';
ConditionUserDefine:=' Months='''+MpsMonth+''' ';
OrderByFields:=' ItemCode,falg' ;
getdata;
// Executesql(AdoQry_Main,'select * from PsiMonth where Months='''+MpsMonth+''' Order By ItemCode,falg',0);
{ DataSource.DataSet:=AdoQry_Main;
J:=6;
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:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemFlag') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else
AdoQry_Main.Fields[I].Origin:='';
If pos('日',AdoQry_Main.Fields[I].fieldName)<>0 Then
begin
DBGridEh.Columns.Add;
DBGridEh.Columns[J].Width:=50;
DBGridEh.Columns[J].FieldName:=AdoQry_Main.Fields[I].fieldName;
DBGridEh.Columns[J].Title.Caption:=AdoQry_Main.Fields[I].fieldName;
DBGridEh.Columns[J].Title.Alignment:=TaCenter;
J:=J+1;
end;
end;
Frm_Public_Show.Visible:=False;
Application.ProcessMessages; //_____________________
DbGridEh.FrozenCols:=4;
DbTxt_ItemFlag.DataField:='ItemFlag';
DbTxt_MonthQty.DataField:='MonthTotalQty'; }
end;
procedure TFrm_Mrp_Qry_PsiDate.FormClose(Sender: TObject;
var Action: TCloseAction);
var
SqlText:String;
begin
inherited;
{ Try
SqlText:='Drop Table #TmpOblongdayMps,#tmpdayMps';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=SqlText;
AdoQry_Tmp.ExecSQL;
Except
end;}
AdoQry_Tmp.Close;
AdoQry_Main.Close;
try
Frm_PubLic_Show.Close;
except
end;
end;
procedure TFrm_Mrp_Qry_PsiDate.Act_FilterExecute(Sender: TObject);
var
I:Integer;
begin
{ For I:=0 To AdoQry_Main.FieldCount -1 Do
begin
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:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemFlag') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('MonthTotalqty') Then
AdoQry_Main.Fields[I].Origin:='PsiMonth'
end;}
inherited;
end;
procedure TFrm_Mrp_Qry_PsiDate.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Mrp_Qry_PsiDate:=Nil;
end;
procedure TFrm_Mrp_Qry_PsiDate.Act_LookExecute(Sender: TObject);
begin
Frm_Mrp_Qry_PsiDate_D:=TFrm_Mrp_Qry_PsiDate_D.Create(Application);
Frm_Mrp_Qry_PsiDate_D.GetConnect(AdoQry_Main);
Frm_Mrp_Qry_PsiDate_D.ShowModal;
Frm_Mrp_Qry_PsiDate_D.Release;
end;
procedure TFrm_Mrp_Qry_PsiDate.FormCreate(Sender: TObject);
begin
inherited;
Frm_Public_Show:=TFrm_Public_Show.Create(Application);
end;
procedure TFrm_Mrp_Qry_PsiDate.DBGridEhGetCellParams(Sender: TObject;
Column: TColumnEh; AFont: TFont; var Background: TColor;
State: TGridDrawState);
begin
inherited;
if (AdoQry_Main.fieldbyname('falg1').asinteger mod 2)=0 then
begin
Background:=$00F9D1C6;
afont.Color:=clblack;
end;
end;
procedure TFrm_Mrp_Qry_PsiDate.Button1Click(Sender: TObject);
begin
inherited;
if AdoQry_Main.Active = True then
Qry_Item(Dbconnect,AdoQry_Main.fieldbyname('ItemCode').AsString);
DBGridEh.SetFocus;
end;
procedure TFrm_Mrp_Qry_PsiDate.Button2Click(Sender: TObject);
var
SqlText:String;
I:Integer;
AdoQry:TAdoQuery;
begin
inherited;
if AdoQry_Main.Active = True then
begin
try
Executesql(AdoQry_Tmp,'drop table #Dzbzx',1);
except
end;
SqlText:=' Create Table #Dzbzx( xsbz varchAr(20) Null ,'
+' Datetimes integer Null,'
+' Qty float(12) Null)';
Executesql(AdoQry_Tmp,SqlText,1);
for I:=1 to 31 do
begin
SqlText:=' Select xsbz,'+'['+inttostr(I)+'日] as aa'+' From PsiMonth '
+' where xsbz='''+AdoQry_Main.fieldbyname('xsbz').asstring+''' '
+' and ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+''' '
+' And Months='''+MpsMonth+'''';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.clear;
AdoQry_Tmp.SQL.Text := Sqltext;
AdoQry_Tmp.Open;
if not AdoQry_Tmp.Eof then
begin
SqlText := ' Insert #Dzbzx(xsbz,Datetimes,Qty) '
+' Values '
+' ('''+AdoQry_Tmp.fieldbyname('xsbz').asstring+''', '
+ inttostr(I)+','
+' '''+AdoQry_Tmp.fieldbyname('aa').asstring+''')';
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection :=DbConnect;
AdoQry.Close;
AdoQry.SQL.clear;
AdoQry.SQL.Text := SqlText;
AdoQry.ExecSQL;
end;
end;
conditionHint2:=conditionHint1+'\产销存标识：'+AdoQry_Main.fieldbyname('xsbz').asstring+'\物料标志：'+AdoQry_Main.fieldbyname('ItemFlag').asstring;
// if Frm_Mrp_Qry_GraphPsiDate=nil then
//begin
Frm_Mrp_Qry_GraphPsiDate:=TFrm_Mrp_Qry_GraphPsiDate.Create(Self);
Frm_Mrp_Qry_GraphPsiDate.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
Frm_Mrp_Qry_GraphPsiDate.InitForm(AdoQry_Main.Connection,True);
// Frm_Mrp_Qry_GraphPsiDate.setconditionHint(Frm_Mrp_Qry_GraphAnalyzerCapacity.lbl_Condition,condition)
// end
// else Frm_Mrp_Qry_GraphPsiDate.Show;
end;
end;
procedure TFrm_Mrp_Qry_PsiDate.Button3Click(Sender: TObject);
var
SqlText:String;
I:Integer;
AdoQry:TAdoQuery;
begin
inherited;
if AdoQry_Main.Active = True then
begin
try
Executesql(AdoQry_Tmp,'drop table #Dzbzx1',1);
except
end;
SqlText:=' Create Table #Dzbzx1(xsbz varchAr(20) Null ,'
+' Datetimes integer Null,'
+' Qty float(12) Null)';
Executesql(AdoQry_Tmp,SqlText,1);
for I:=1 to 31 do
begin
SqlText:=' Select xsbz,'+'['+inttostr(I)+'日] as aa'+' From PsiMonth '
+' where '
+' ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+''' '
+' And Months='''+MpsMonth+''' ';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.clear;
AdoQry_Tmp.SQL.Text := Sqltext;
AdoQry_Tmp.Open;
AdoQry_Tmp.First;
while not AdoQry_Tmp.Eof do
begin
SqlText := ' Insert #Dzbzx1(xsbz,Datetimes,Qty) '
+' Values '
+' ('''+AdoQry_Tmp.fieldbyname('xsbz').asstring+''', '
+ inttostr(I)+','
+' '''+AdoQry_Tmp.fieldbyname('aa').asstring+''')';
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection :=DbConnect;
AdoQry.Close;
AdoQry.SQL.clear;
AdoQry.SQL.Text := SqlText;
AdoQry.ExecSQL;
AdoQry_Tmp.next;
end;
end;
conditionHint2:=conditionHint1+'\物料标志：'+AdoQry_Main.fieldbyname('ItemFlag').asstring;
// if Frm_Mrp_Qry_GraphPsiDate=nil then
//begin
Frm_Mrp_Qry_GraphPsiDate1:=TFrm_Mrp_Qry_GraphPsiDate1.Create(Self);
Frm_Mrp_Qry_GraphPsiDate1.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
Frm_Mrp_Qry_GraphPsiDate1.InitForm(AdoQry_Main.Connection,True);
// Frm_Mrp_Qry_GraphPsiDate.setconditionHint(Frm_Mrp_Qry_GraphAnalyzerCapacity.lbl_Condition,condition)
// end
// else Frm_Mrp_Qry_GraphPsiDate.Show;
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -