mrp_qry_psimonth.pas

一个MRPII系统源代码版本

      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (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;
    while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (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 #tmpOblongdayMpsR '
          +' (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(mm,MpsDate) as Months,Sum(MpsQty) As MpsQty '
            +' From #TmPmpsR  '
            +' Group By ItemCode,datepArt(mm,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('Months').Asstring;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
        10..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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(mm,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvInBillR  '
            +' Group By ItemCode,datepArt(mm,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..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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(mm,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvInBillTR  '
            +' Group By ItemCode,datepArt(mm,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..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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(mm,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvOutBillR  '
            +' Group By ItemCode,datepArt(mm,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..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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(mm,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #TmpInvOutBillTR  '
            +' Group By ItemCode,datepArt(mm,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..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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;
//----计算输入年份的期初数         #jcR

  SqlText:='  select InvMonth,ItemCode, sum(InvLmQty+InvBlnCQty) as jc  into #jcR '
          +' from InvMonthSum '
          +'  where convert(varchAr(4),InvMonth)= convert(varchAr,convert(integer,'''+MpsYear+''')-1)'
          +' and exists(select * from #TmPmpsR where  InvMonthSum.ItemCode=#TmPmpsR.ItemCode) '
          +' group by ItemCode ,InvMonth '
//----计算每月发生数量#rfsslR             sum(   ) as InvBillQty
         +' select t1.ItemCode,t1.InvBilldate, t1.InvBillqty  into #rfsslR'
         +' from '
         +' (select InvBillMonth,ItemCode,InvBilldate,InvBillqty from #TmpInvInBillR '
         +' union all'
         +' select InvBillMonth,ItemCode,InvBilldate,InvBillqty  from #TmpInvInBillTR '
         +' union all '
         +' select InvBillMonth,ItemCode,InvBilldate,-(InvBillqty) from #TmpInvOutBillR'
         +' union all'
         +' select InvBillMonth,ItemCode,InvBilldate,-(InvBillqty) from #TmpInvOutBillTR) t1';
     //    +' group by t1.ItemCode,t1.InvBilldate ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
    SqlText:=' Select ItemCode,datepArt(mm,InvBilldate) as Dates,Sum(InvBillQty) As MpsQty '
            +' From #rfsslR  '
            +' Group By ItemCode,datepArt(mm,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..12:UpdateField:='['+Inttostr(StrToInt(s))+'月]';
      end;
      SqlText:=' Update #TmpOblongdayMpsR '
              +' 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 #jcR';
    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..12:UpdateField:='['+Inttostr(i)+'月]';
        end;
          SqlText:=' Update #TmpOblongdayMpsR '
                  +' 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 #TmpOblongdayMpsR';
    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)*58+30)+'%!';
      i:=2;
      for i:=2 to 12 do
      begin
        Case I Of
          1..9:UpdateField:='['+Inttostr(i)+'月]';
          10..12:UpdateField:='['+Inttostr(i)+'月]';
        end;
        Case I Of
          1..9:UpdateField1:='['+Inttostr(i-1)+'月]';
          10..12:UpdateField1:='['+Inttostr(i-1)+'月]';
        end;
        SqlText:=' Update #TmpOblongdayMpsR '
                +' 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:=' Update #TmpOblongdayMpsR '
          +' Set  rpz=MonthTotalQty/'+inttostr(12)+' where falg<>6'  ;
  Executesql(AdoQry_Tmp,sqltext,1);}

  {SqlText:='Select ';
  For I:=1 To 12 Do
  begin
//    SqlText:=SqlText+' Sum(#TmpOblongdayMpsR.['+IntToStr(I)+'月]) As ['+IntToStr(I)+'月] ,'
      SqlText:=SqlText+' #TmpOblongdayMpsR.['+IntToStr(I)+'月] As ['+IntToStr(I)+'月] ,'  //  Sum(MonthTotalQty) As
  end;
  SqlText:=SqlText+' #TmpOblongdayMpsR.xsbz,#TmpOblongdayMpsR.ItemCode, #TmpOblongdayMpsR.MonthTotalQty ,rpz,falg,falg1,Item.ItemName,Uom.UomName, '
          +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag  '
          +' into #tmpdayMpsR '
          +' From #TmpOblongdayMpsR '
          +' left join Item on  #TmpOblongdayMpsR.ItemCode=Item.ItemCode '
          +' left join Uom on Item.UomCode=Uom.UomCode '
//          +' Group By #TmpOblongdayMpsR.ItemCode,Item.ItemName,Uom.UomName,#TmpOblongdayMpsR.MpsMonth,Item.ItemCode+'''+' '+'''+Item.ItemName '
          +' Order By #TmpOblongdayMpsR.ItemCode ,#TmpOblongdayMpsR.falg ';       //  ,#TmpOblongdayMpsR.ItemCode

    Executesql(AdoQry_Tmp,SqlText,1);

  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''主生产计划'','
        +'''汇总'','
        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 1)';
  Executesql(AdoQry_Tmp,SqlText,1);
  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''生产入库'','
        +'''汇总'','
        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 2)';
  Executesql(AdoQry_Tmp,SqlText,1);
  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''其他入库'','
        +'''汇总'','
        +''' 汇总'','
        +''''+inttostr(0)+''','
        +' 3)';
  Executesql(AdoQry_Tmp,SqlText,1);

  SqlText:=' Insert #tmpdayMpsR '
        +' (xsbz,ItemFlag,ItemCode,falg1,falg) '
        +' Values('
        +'''销售出库'','
        +'''汇总'','