mrp_qry_informaluprightmrp_d.pas

一个MRPII系统源代码版本

       edit12.Text:=fieldbyname('Vendorceo').asstring;
       edit8.Text:=fieldbyname('VendorremArk').asstring;
       showmodal;
     end;
    finally
    Frm_Mrp_Qry_InformalUpRightMrp_D_vd.Free;
     end;
  end;

end;

function  TFrm_Mrp_Qry_InformalUpRightMrp_D.existsMpsandmo(ItemCode:string;requiredate:string):integer;
var  bool1:boolean;
     bool2:boolean;
begin
 Result:=100;
  bool1:=ifexistsiteItemCode(ItemCode,requiredate,inttostr(getlt(ItemCode,2)));
  bool2:=existsMps(ItemCode,requiredate,inttostr(getlt(ItemCode,2)));
  if bool1 and bool2 then
   Result:=3
  else if bool1 then
  Result:=1
  else if bool2 then
  Result:=2;
if Result=100 then
try
Executesql(AdoQry_tmp,'drop table #tmpopenBom,#tmpopenBomResult,#tmpopenBomResult1,#tmpuPMrpResult,#tmpuPMrpResult1',1);
except
end;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D.ifexistsiteItemCode(ItemCode:string;date,Qclt:string):boolean;{ Private declarations }
var AdoQry:TAdoQuery;tMpsqltext:string;
begin
Result:=False;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.EnableBCD:=False;
  AdoQry.Connection:=dbconnect;
  try
   try Executesql(AdoQry_tmp,'drop table #tmpopenBomResult,#tmpuPMrpResult',1);
   except
   end;
 tMpsqltext:=' select top 0 InformalMrpResult.*,Bom.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
               +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
               +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt, '
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult '
               +'   from InformalMrpResult               '
               +' join Item on InformalMrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'
               +' join Bom on InformalMrpResult.ItemCode=Bom.ite_ItemCode';
 Executesql(AdoQry_tmp,tMpsqltext,1);
 Bomopen(dbconnect,ItemCode);
 //tMpsqltext:='select Bom.ite_ItemCode as ItemCode,Item.Preparelt from Bom,Item where Bom.ite_ItemCode=Item.ItemCode and Bom.ItemCode='+quotedstr(ItemCode);
 tMpsqltext:='select distinct Item.ItemCode,Item.PmCode,Item.Preparelt,Item.purchldtime,Bomqty=sum(Bomqty),BomScrAprate=sum(BomScrAprate) '
             +' into #tmpopenBomResult  '
             +'  From #TmpOpenBom,Item       '
             +' where #tmpopenBom.ItemCode=Item.ItemCode '
             +'   and exists(select * from InformalMrpResult where #tmpopenBom.ItemCode=InformalMrpResult.ItemCode )  '
             +' group by Item.ItemCode,Item.PmCode,Item.Preparelt,Item.purchldtime';
 Executesql(AdoQry_tmp,tMpsqltext,1);
 Executesql(AdoQry_tmp,'select * from #tmpopenBomResult',0);
 with AdoQry_tmp do
   begin
    First;
     while not eof do
       begin
         if tmplowPmCode=0 then
         tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct InformalMrpResult.*,#tmpopenBomResult.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from InformalMrpResult               '
                    +' join Item on InformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on InformalMrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where (ordinal=2 ) '
                    +'  and Orderqty>0    '
                    +'and InformalMrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and InformalMrpResult.releasedate=case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,date,-(strtoint(Qclt)+fieldbyname('Preparelt').asinteger+getlt(fieldbyname('ItemCode').asstring,3))))
                    +'                                 else  '+quotedstr(slCalendar(dbconnect,date,-(strtoint(Qclt)+fieldbyname('purchldtime').asinteger)))
                    +'                            end   '
         else
            tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct InformalMrpResult.*,#tmpopenBomResult.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from InformalMrpResult               '
                    +' join Item on InformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on InformalMrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where (ordinal=2 ) '
                    +' and Orderqty>0 '
                    +'and InformalMrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and InformalMrpResult.releasedate=case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,date,-(fieldbyname('Preparelt').asinteger+getlt(fieldbyname('ItemCode').asstring,3))))
                    +'                                 else  '+quotedstr(slCalendar(dbconnect,date,-(fieldbyname('purchldtime').asinteger)))
                    +'                            end   ';
         Executesql(AdoQry,tMpsqltext,1);
         next;
       end;
   end;
   Executesql(AdoQry,'select *  from #tmpuPMrpResult ',0);
   if AdoQry.RecordCount>0 then
     Result:=True
      else
     Result:=False;
  finally
    AdoQry.Free;
  end;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D.getlt(ItemCode:string;ltType:integer):integer;
var AdoQry:TAdoQuery;
begin
  Result:=0;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.EnableBCD:=False;
  try
    with AdoQry do
      begin
        Close;
        Connection:=dbconnect;
        sql.clear;
        sql.Add('select Preparelt,Qclt,runlt,purchldtime from Item where ItemCode='
                +quotedstr(ItemCode));
      open;
      case  ltType of
       1:  Result:=fieldbyname('Preparelt').asinteger;
       2:  Result:=fieldbyname('Qclt').asinteger;
       3:  Result:=fieldbyname('runlt').asinteger;
       4:  Result:=fieldbyname('purchldtime').asinteger;
      end;
      end;
  finally
    AdoQry.Free;
  end;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D.existsfArItemCode(ItemCode:string):boolean;
var AdoQry:TAdoQuery;
begin
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.EnableBCD:=False;
  try
    with AdoQry do
      begin
        Close;
        Connection:=dbconnect;
        sql.clear;
        sql.Add('select ite_ItemCode from Bom where ItemCode='
                +quotedstr(ItemCode));
      open;
      if recordCount=0 then
        Result:=False
      else Result:=True;
      end;
  finally
    AdoQry.Free;
  end;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D.existsMps(ItemCode:string;date,Qclt:string):boolean;
var tMpsqltext,tmpcondition:string;
    i:integer;
    lowItemPmCode:integer;
begin
  Result:=False;
  lowItemPmCode:=ItemPmCode;
  try Executesql(AdoQry_tmp,'drop table #tmpopenBomResult1,#tmpuPMrpResult1',1);
  except
  end;
    tMpsqltext:='select distinct Item.ItemCode,Item.Preparelt,Item.runlt,Item.PmCode,Item.purchldtime,Bomqty=sum(Bomqty),BomScrAprate=sum(BomScrAprate) '
             +' into #tmpopenBomResult1  '
             +'  From #TmpOpenBom,Item       '
             +'  where #tmpopenBom.ItemCode=Item.ItemCode  '
             +'    and exists(select * from InformalMrpResult where #tmpopenBom.ItemCode=InformalMrpResult.ItemCode)  '
             +' group by Item.ItemCode,Item.Preparelt,Item.runlt,Item.purchldtime,Item.PmCode';
   Executesql(AdoQry_tmp,tMpsqltext,1);
   Executesql(AdoQry_tmp,'select * from #tmpopenBomResult1',0);

     with AdoQry_tmp do
    begin
      if eof then
        exit;
      i:=0;
      While NOT Eof Do
         begin
           If I=0 Then
               begin
                if lowItemPmCode=0 then
                Tmpcondition:='(InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
                else
                 Tmpcondition:='(InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(dbconnect,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(dbconnect,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
               end
            Else
               begin
                 if lowItemPmCode=0 then
                 Tmpcondition:=Tmpcondition+' Or (InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
                 else
                 Tmpcondition:=Tmpcondition+' Or (InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(dbconnect,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(dbconnect,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(dbconnect,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
               end;
      I:=I+1;
      AdoQry_Tmp.Next;
      end;
    tMpsqltext:=' select distinct InformalMrpResult.*,#tmpopenBomResult1.Bomqty,Mps.Mpsqty,  '
               +'Mps.MpsMonth,Mps.Mpsdate,Mps.Mpsstatus,Mps.MpsremArk,'
               +'  ScrAprate=convert(varchAr,#tmpopenBomResult1.BomScrAprate)+''%'','
               +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult1  '
               +'   from InformalMrpResult,Item,Uom,Mps,#tmpopenBomResult1               '
               +' where InformalMrpResult.ItemCode=Item.ItemCode  '
               +' and Item.UomCode*=Uom.UomCode'
               +' and InformalMrpResult.ItemCode=Mps.ItemCode '
               +' and Mps.Mpsstatus=3  '
              // +' and  InformalMrpResult.releasedate=Mps.Mpsdate '
               +' and InformalMrpResult.ItemCode=#tmpopenBomResult1.ItemCode'
               +' and  (ordinal=5) and netqty>0'+' and ('+tmpcondition+')'
   +' Order by InformalMrpResult.ItemCode,dueDate ';
   Executesql(AdoQry_tmp,tMpsqltext,1);
   Executesql(AdoQry_tmp,'select * from #tmpuPMrpResult1',0);
   if AdoQry_tmp.RecordCount>0 then
    Result:=True;

end;
end;

end.