mrp_qry_oblongmps.pas

一个MRPII系统源代码版本

      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.next;
      y:=y+1;
    end;
// ===chb  end;

 { SQlText:=' Select ItemCode,Sum(MpsQty) As MpsQty,MpsMonth,MpsDate '
          +' From Mps where Mpsstatus<4'
          +' Group By ItemCode,MpsMonth,MpsDate ';
  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 #TmpOblongdayMps '
            +' (MpsMonth,ItemCode,MonthTotalQty,MpsDate) '
            +' Values('
            +''''+AdoQry_Main.fieldbyname('MpsMonth').AsString+''','
            +''''+AdoQry_Main.fieldbyname('ItemCode').AsString+''','
            +''''+FloatToStr(AdoQry_Main.fieldbyname('MpsQty').AsFloat)+''','
            +''''+DateTimeToStr(AdoQry_Main.fieldbyname('MpsDate').AsDateTime)+''')';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
    AdoQry_Main.Next;
  end;
  SqlText:=' Select ItemCode,MpsMonth,MpsDate,Sum(MpsQty) As MpsQty '
          +' From Mps where Mpsstatus<4'
          +' Group By ItemCode,MpsDate,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(FormatDateTime('yyyy.mm.dd',AdoQry_Main.fieldbyname('MpsDate').AsDateTime),9,10);
    Case StrToInt(S) Of
      1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
    end;
    SqlText:=' Update #TmpOblongdayMps '
            +' Set '+UpdateField+'='''+FloatToStr(AdoQry_Main.fieldbyname('MpsQty').AsFloat)+''''
            +' Where ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').AsString+''''
            +' And MpsDate='''+DateTimeToStr(AdoQry_Main.fieldbyname('MpsDate').AsDateTime)+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
    AdoQry_Main.Next;
  end;
  Frm_Public_Show.Visible:=False;
//---------------------------------------------------------------------\\
  SQlText:=' Select Sum(isnull(MpsQty,0)) As MpsQty,datepArt(dd,MpsDate) as dateth '
          +' into #tmptable '
          +' From Mps '
          +' Group By MpsDate ';
   AdoQry_Main.Close;
  AdoQry_Main.Prepared;
  AdoQry_Main.SQL.Text:=SqlText;
  AdoQry_Main.ExecSQL;
  SQlText:=' Select Sum(isnull(MpsQty,0)) As MpsQty,datet=convert(varchAr,dateth)+''日'' '
          +' into #tmp'
          +' From #tmptable '
          +' Group By dateth ';
   AdoQry_Main.Close;
  AdoQry_Main.Prepared;
  AdoQry_Main.SQL.Text:=SqlText;
//  showmessage(sqltext);
  AdoQry_Main.execsql;
   SqlText:=' Insert into #TmpOblongdayMps '
            +' (MpsMonth,ItemCode,MonthTotalQty,MpsDate) '
            +' Values('
            +''''+'日累计'+''','
            +'null'+','
            +'null'+','
            +'null'+')';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  Y:=1;
//  X:=AdoQry_Main.RecordCount;
for i:=1 to 31 do
  begin
  //  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*59+41)+'%!';
    Application.ProcessMessages;                                    //_____________________
    Y:=Y+1;
      UpdateField:=Inttostr(i)+'日';
      SQlText:=' Select  MpsQty '
          +' From #tmp '
          +' where datet='+quotedstr(updatefield);
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.open;

    SqlText:=' Update #TmpOblongdayMps '
            +' Set ['+UpdateField+']='''+FloatToStr(AdoQry_tmp.fieldbyname('MpsQty').AsFloat)+''''
            +' Where MpsMonth='''+'日累计'+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  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.MpsMonth,#TmpOblongdayMps.ItemCode, #TmpOblongdayMps.MonthTotalQty ,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.MpsMonth ,#TmpOblongdayMps.ItemCode';       //  ,#TmpOblongdayMps.ItemCode
  AdoQry_Main.Close;
  AdoQry_Main.Prepared;
  AdoQry_Main.SQL.Text:=SqlText;
  AdoQry_Main.execsql;
  Executesql(AdoQry_Main,'select * from #tmpdayMps',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:='#tmpdayMps'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMps'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMps'
    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;                                    //_____________________
 DbGridEh.FrozenCols:=4;
 DbTxt_Month.DataField:='MpsMonth';
 DbTxt_ItemFlag.DataField:='ItemFlag';
 DbTxt_MonthQty.DataField:='MonthTotalQty';
end;

procedure TFrm_Mrp_Qry_OblongMps.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_OblongMps.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:='#tmpdayMps'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('ItemName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMps'
    Else If UpperCase(AdoQry_Main.Fields[I].DisplayName)=UpperCase('UomName') Then
      AdoQry_Main.Fields[I].Origin:='#tmpdayMps'
    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:='#tmpdayMps'
  end;
  inherited;
end;

procedure TFrm_Mrp_Qry_OblongMps.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_OblongMps:=Nil;
end;

procedure TFrm_Mrp_Qry_OblongMps.Act_LookExecute(Sender: TObject);
begin
  Frm_Mrp_Qry_OblongMps_D:=TFrm_Mrp_Qry_OblongMps_D.Create(Application);
  Frm_Mrp_Qry_OblongMps_D.GetConnect(AdoQry_Main);
  Frm_Mrp_Qry_OblongMps_D.ShowModal;
  Frm_Mrp_Qry_OblongMps_D.Release;
end;

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

end.