mrp_qry_psidate.pas

一个MRPII系统源代码版本

            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''其他入库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 3)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;}
//销售出库
{    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvOutBill  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;

    if AdoQry_tmp.Eof then
    begin
      SqlText:=' Insert #TmpOblongdayMps '
            +' (xsbz,ItemCode,falg1,falg) '
            +' Values('
            +'''销售出库'','
            +''''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
            +''''+inttostr(y)+''','
            +' 4)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
    end
    else
    begin
      SqlText:=' Insert #TmpOblongdayMps '
              +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
              +' Select '
              +' ''销售出库'','
              +' ItemCode,'
              +'Sum(InvBillQty) as MpsQty ,'
              +''''+inttostr(y)+''','
              +' 4 from #TmpInvOutBill'
              +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
              +' Group By ItemCode  ';
        AdoQuery:=TAdoQuery.Create(nil);
        AdoQuery.Connection :=DbConnect;
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL;
    end;}
    {while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMps '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''销售出库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 4)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;}
 //其它出库
{    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvOutBillT  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    if AdoQry_tmp.Eof then
    begin
      SqlText:=' Insert #TmpOblongdayMps '
            +' (xsbz,ItemCode,falg1,falg) '
            +' Values('
            +'''其它出库'','
            +''''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
            +''''+inttostr(y)+''','
            +' 5)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
    end
    else
    begin
      SqlText:=' Insert #TmpOblongdayMps '
              +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
              +' Select '
              +' ''其它出库'','
              +' ItemCode,'
              +'Sum(InvBillQty) as MpsQty ,'
              +''''+inttostr(y)+''','
              +' 5 from #TmpInvOutBillT'
              +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
              +' Group By ItemCode  ';
        AdoQuery:=TAdoQuery.Create(nil);
        AdoQuery.Connection :=DbConnect;
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL;
    end;}
    {while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMps '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''其它出库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 5)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;}
 //期末结存

{    sqltext:=' insert #tmpOblongdayMps '
          +' (xsbz,ItemCode,falg1,falg) '
          +' Values('
          +'''期末结存'','
          +''''+tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
          +''''+inttostr(y)+''','
          +' 6)';
    AdoQuery:=tAdoQuery.Create(nil);
    AdoQuery.Connection :=dbconnect;
    AdoQuery.Close;
    AdoQuery.sql.text:=sqltext;
    AdoQuery.execsql;

    Tmp_AdoQuery.Next;
    Application.ProcessMessages;
    y:=y+1;
  end;
    //主生产计划
    SqlText:=' Select ItemCode,datepArt(dd,MpsDate) as Dates,Sum(MpsQty) As MpsQty '
            +' From #TmPmps  '
            +' Group By ItemCode,datepArt(dd,MpsDate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;                                    //_____________________
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And falg=1';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;
//生产入库
    SqlText:=' Select ItemCode,datepArt(dd,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvInBill  '
            +' Group By ItemCode,datepArt(dd,InvBilldate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;                                    //_____________________
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And falg=2';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;
//其他入库记录
    SqlText:=' Select ItemCode,datepArt(dd,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvInBillT  '
            +' Group By ItemCode,datepArt(dd,InvBilldate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;                                    //_____________________
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And falg=3';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;
//销售出库
    SqlText:=' Select ItemCode,datepArt(dd,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvOutBill  '
            +' Group By ItemCode,datepArt(dd,InvBilldate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And falg=4';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;
 //其它出库
     SqlText:=' Select ItemCode,datepArt(dd,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvOutBillT  '
            +' Group By ItemCode,datepArt(dd,InvBilldate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And falg=5';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;
//----计算输入月份的期初数         #jc

  SqlText:='  select InvMonth,ItemCode, sum(InvLmQty+InvBlnCQty) as jc  into #jc '
          +' from InvMonthSum '
          +'  where InvMonth=convert(chAr(7),convert(Datetime,'''+dates+''')-1,102)'
          +' and exists(select * from #TmPmps where  InvMonthSum.ItemCode=#TmPmps.ItemCode) '
          +' group by ItemCode ,InvMonth '
//----计算每日发生数量#rfssl             sum(   ) as InvBillQty
         +' select t1.ItemCode,t1.InvBilldate, t1.InvBillqty  into #rfssl'
         +' from '
         +' (select InvBillMonth,ItemCode,InvBilldate,InvBillqty from #TmpInvInBill '
         +' union all'
         +' select InvBillMonth,ItemCode,InvBilldate,InvBillqty  from #TmpInvInBillT '
         +' union all '
         +' select InvBillMonth,ItemCode,InvBilldate,-(InvBillqty) from #TmpInvOutBill'
         +' union all'
         +' select InvBillMonth,ItemCode,InvBilldate,-(InvBillqty) from #TmpInvOutBillT) t1';
     //    +' group by t1.ItemCode,t1.InvBilldate ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
    SqlText:=' Select ItemCode,datepArt(dd,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #rfssl  '
            +' Group By ItemCode,datepArt(dd,InvBilldate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;
      s:=AdoQry_Tmp.fieldbyname('Dates').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' 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;
      AdoQry_Tmp.Next;
    end;
//每日加上期初数量
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.clear;
    AdoQry_Tmp.SQL.Text := 'select * from #jc';
    AdoQry_Tmp.Open;
    AdoQry_Tmp.First;
    I:=1;
    while not AdoQry_Tmp.Eof  do
    begin
      if i=1 then
      begin
        Case I Of
          1..9:UpdateField:='['+Inttostr(i)+'日]';
          10..31:UpdateField:='['+Inttostr(i)+'日]';
        end;
          SqlText:=' Update #TmpOblongdayMps '
                  +' Set '+UpdateField+'='+'isnull('+UpdateField+','+'0'+')'+'+'+''+floattostr(AdoQry_Tmp.fieldbyname('jc').Asfloat)+''
                  +' 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;
    end;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.sql.clear;
    AdoQry_Tmp.SQL.Text:='select distinct ItemCode from #TmpOblongdayMps';
    AdoQry_Tmp.Open;
    x:=AdoQry_tmp.RecordCount;
    y:=1;
    while not AdoQry_Tmp.Eof  do
    begin
      Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*40+30)+'%!';
      i:=2;
      for i:=2 to 31 do
      begin
        Case I Of
          1..9:UpdateField:='['+Inttostr(i)+'日]';
          10..31:UpdateField:='['+Inttostr(i)+'日]';
        end;
        Case I Of
          1..9:UpdateField1:='['+Inttostr(i-1)+'日]';
          10..31:UpdateField1:='['+Inttostr(i-1)+'日]';
        end;}
        {SqlText:=' Update #TmpOblongdayMps '
                +' Set '+UpdateField+'='+'isnull('+UpdateField1+',0)'+'+'+'isnull('+UpdateField+',0)'+''
                +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
                +' And falg=6';}