mrp_qry_psimonth.pas

一个MRPII系统源代码版本

        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 4)';
  Executesql(AdoQry_Tmp,SqlText,1);

  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''其它出库'','
        +'''汇总'','
        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 5)';
  Executesql(AdoQry_Tmp,SqlText,1);
  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''期末结存'','
        +'''汇总'','
        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 6)';
   Executesql(AdoQry_Tmp,SqlText,1);

   //更新汇总
   //主生产计划
   X:=12;
   Y:=1;
  for I:=1 to 12 do
  begin
     Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*12+88)+'%!';
      Case I Of
        1..9:UpdateField:='['+Inttostr(I)+'月]';
        10..31:UpdateField:='['+Inttostr(I)+'月]';
      end;
    SqlText:=' Select Falg,Sum('+UpdateField+') As MpsQty ' //'+UpdateField+',
            +' From #tmpdayMpsR  '
            +' 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 #tmpdayMpsR '
              +' 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 #tmpdayMpsR 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 #tmpdayMpsR '
              +' 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:=' Select Falg,Sum(rpz) As MpsQty '  //rpz,
            +' From #tmpdayMpsR  where falg<>6'
            +' Group By falg';   //,rpz
    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 #tmpdayMpsR '
              +' Set rpz='''+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 #tmpdayMpsR '
          +' Set  rpz=MonthTotalQty/'+inttostr(12)+' where falg<>6'  ;
  Executesql(AdoQry_Tmp,sqltext,1);}



{  Executesql(AdoQry_Main,'select * from #tmpdayMpsR 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:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemFlag') Then
      AdoQry_Main.Fields[I].Origin:=''
    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;
                                  //_____________________}
  SelectFromSQL:=' select * from PSIYear ';
  ConditionUserDefine:=' Years='''+MpsYear+''' ';
  OrderByFields:=' ItemCode,falg'  ;
  getdata;
  DbGridEh.FrozenCols:=4;
  DbTxt_ItemFlag.DataField:='ItemFlag';
  DbTxt_MonthQty.DataField:='MonthTotalQty';
end;

procedure TFrm_Mrp_Qry_PsiMonth.FormClose(Sender: TObject;
  var Action: TCloseAction);
var
  SqlText:String;
begin
  inherited;
{  Try
    SqlText:='Drop Table #TmpOblongdayMpsR,#tmpdayMpsR';
    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_PsiMonth.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:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMpsR'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemFlag') Then
      AdoQry_Main.Fields[I].Origin:=''
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('MonthTotalqty') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMpsR'
  end;}
  inherited;
end;

procedure TFrm_Mrp_Qry_PsiMonth.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_PsiMonth:=Nil;
end;

procedure TFrm_Mrp_Qry_PsiMonth.Act_LookExecute(Sender: TObject);
begin
  Frm_Mrp_Qry_PsiMonth_D:=TFrm_Mrp_Qry_PsiMonth_D.Create(Application);
  Frm_Mrp_Qry_PsiMonth_D.GetConnect(AdoQry_Main);
  Frm_Mrp_Qry_PsiMonth_D.ShowModal;
  Frm_Mrp_Qry_PsiMonth_D.Release;
end;

procedure TFrm_Mrp_Qry_PsiMonth.FormCreate(Sender: TObject);
begin
  inherited;
//  Frm_Public_Show:=TFrm_Public_Show.Create(Application);
end;

procedure TFrm_Mrp_Qry_PsiMonth.DBGridEhGetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if AdoQry_Main<> nil then
    if  (AdoQry_Main.fieldbyname('falg1').asinteger mod 2)=0 then
    begin
      Background:=$00F9D1C6;
      afont.Color:=clblack;
    end;

end;

procedure TFrm_Mrp_Qry_PsiMonth.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_PsiMonth.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 #DzbzxR',1);
  except
  end;
  SqlText:=' Create Table #DzbzxR( xsbz varchAr(20)  Null ,'
          +'   Datetimes integer  Null,'
          +'   Qty float(12) Null)';
  Executesql(AdoQry_Tmp,SqlText,1);
  for I:=1 to 12 do
  begin
    SqlText:=' Select xsbz,'+'['+inttostr(I)+'月] as aa'+' From PSIYear '
            +' where xsbz='''+AdoQry_Main.fieldbyname('xsbz').asstring+''' '
            +' and  ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+''' '
            +' And Years='''+MpsYear+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.clear;
    AdoQry_Tmp.SQL.Text := Sqltext;
    AdoQry_Tmp.Open;
    if  not AdoQry_Tmp.Eof then
    begin
      SqlText := ' Insert #DzbzxR(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_GraphPsiMonth=nil then
  //begin
    Frm_Mrp_Qry_GraphPsiMonth:=TFrm_Mrp_Qry_GraphPsiMonth.Create(Self);
    Frm_Mrp_Qry_GraphPsiMonth.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
    Frm_Mrp_Qry_GraphPsiMonth.InitForm(AdoQry_Main.Connection,True);
   // Frm_Mrp_Qry_GraphPsiMonth.setconditionHint(Frm_Mrp_Qry_GraphAnalyzerCapacity.lbl_Condition,condition)
//  end
//  else Frm_Mrp_Qry_GraphPsiMonth.Show;
  end;
end;



procedure TFrm_Mrp_Qry_PsiMonth.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 #DzbzxR1',1);
  except
  end;
  SqlText:=' Create Table #DzbzxR1(xsbz varchAr(20)  Null ,'
          +'   Datetimes integer  Null,'
          +'   Qty float(12) Null)';
  Executesql(AdoQry_Tmp,SqlText,1);
  for I:=1 to 12 do
  begin
    SqlText:=' Select xsbz,'+'['+inttostr(I)+'月] as aa'+' From PSIYear '
            +' where  '
            +' ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+''' '
            +' and Years='''+MpsYear+'''';
    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 #DzbzxR1(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_GraphPsiMonth=nil then
  //begin
    Frm_Mrp_Qry_GraphPsiMonth1:=TFrm_Mrp_Qry_GraphPsiMonth1.Create(Self);
    Frm_Mrp_Qry_GraphPsiMonth1.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
    Frm_Mrp_Qry_GraphPsiMonth1.InitForm(AdoQry_Main.Connection,True);
   // Frm_Mrp_Qry_GraphPsiMonth.setconditionHint(Frm_Mrp_Qry_GraphAnalyzerCapacity.lbl_Condition,condition)
//  end
//  else Frm_Mrp_Qry_GraphPsiMonth.Show;
  end;
end;


end.