mrp_qry_newuprightmrp_d.pas

一个MRPII系统源代码版本

             +' 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 MrpResult.*,#tmpopenBomResult.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from MrpResult               '
                    +' join Item on MrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on MrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where (ordinal=2 ) '
                    +'  and Orderqty>0    '
                    +'and MrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and MrpResult.releasedate=case MrpResult.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 MrpResult.*,#tmpopenBomResult.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from MrpResult               '
                    +' join Item on MrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on MrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where (ordinal=2 ) '
                    +' and Orderqty>0 '
                    +'and MrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and MrpResult.releasedate=case MrpResult.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_NewUpRightMrp_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_NewUpRightMrp_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_NewUpRightMrp_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 MrpResult where #tmpopenBom.ItemCode=MrpResult.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:='(MrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' MrpResult.releaseDate= case MrpResult.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 MrpResult.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:='(MrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' MrpResult.releaseDate= case MrpResult.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 MrpResult.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 (MrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' MrpResult.releaseDate= case MrpResult.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 MrpResult.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 (MrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' MrpResult.releaseDate= case MrpResult.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 MrpResult.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 MrpResult.*,#tmpopenBomResult1.Bomqty,Mps.Mpsqty,  '
               +'Mps.MpsMonth,Mps.Mpsdate,Mps.Mpsstatus,Mps.MpsremArk,'
               +'  ScrAprate=convert(varchAr,#tmpopenBomResult1.BomScrAprate)+''%'','
               +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult1  '
               +'   from MrpResult,Item,Uom,Mps,#tmpopenBomResult1               '
               +' where MrpResult.ItemCode=Item.ItemCode  '
               +' and Item.UomCode*=Uom.UomCode'
               +' and MrpResult.ItemCode=Mps.ItemCode '
               +' and Mps.Mpsstatus=3  '
              // +' and  MrpResult.releasedate=Mps.Mpsdate '
               +' and MrpResult.ItemCode=#tmpopenBomResult1.ItemCode'
               +' and  (ordinal=5) and netqty>0'+' and ('+tmpcondition+')'
     +' Order by MrpResult.ItemCode,dueDate ';
     Executesql(AdoQry_tmp,tMpsqltext,1);
     Executesql(AdoQry_tmp,'select * from #tmpuPMrpResult1',0);
     if AdoQry_tmp.RecordCount>0 then
      Result:=True;
  end;
end;

function TFrm_Mrp_Qry_NewUpRightMrp_D.isAdjust(ItemCode: string): boolean;
var Qry:TAdoQuery;
begin
 Result:=False;
  Qry:=TAdoQuery.Create(self);
  Qry.Connection:=dbconnect;
   try
     Executesql(Qry,'select ItemCode from MrpResult where ordinal=4 and ItemCode='+quotedstr(ItemCode),0);
     if Qry.RecordCount>0 then
      Result:=True;
  finally
     Qry.Free;
  end;
end;

function TFrm_Mrp_Qry_NewUpRightMrp_D.getserverdate:string;
var Qry:TAdoQuery;
begin
  Result:='9999.12.30';
  Qry:=TAdoQuery.Create(self);
  Qry.Connection:=dbconnect;
  try
    Executesql(Qry,'select getdate() as serverdate',0);
    if Qry.RecordCount>0 then
    Result:=Qry.fieldbyname('serverdate').asstring;
  finally
    Qry.Free;
  end;
end;

end.