mrp_qry_informaluprightmrp_d_newparentmo.pas

一个MRPII系统源代码版本

               +' join Bom on #tInformalMrpResult.ItemCode=Bom.ite_ItemCode';
 Executesql(AdoQry_tmp,tMpsqltext,1);
 tMpsqltext:=' select ItemCode,Qclt from Item where ItemCode='+quotedstr(copy(strpath,1,pos(',',strpath)-1));
           // +'  and Bom.ItemCode='+quotedstr(copy(strpath,1,pos(',',strpath)-1));
 Executesql(AdoQry_tmp,tMpsqltext,0);
 with AdoQry_tmp do
        begin
        if tmplowPmCode1=0 then
         tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct #tInformalMrpResult.*,'
                    +floattostr(tmplowBomqty)+','
                    +' InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
                    +' noFinishqty=case ordinal when 2 then  Orderqty when 3 then null end,     '
                    +quotedstr(tmplowScrAp)+','
                   // +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
                    +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from #tInformalMrpResult               '
                    +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                   // +' join Bom on InformalMrpResult.ItemCode=Bom.ItemCode'
                    +' where '+iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ')
                    +' and #tInformalMrpResult.ItemCode= '+quotedstr(copy(strpath,1,pos(',',strpath)-1))
                    +' and #tInformalMrpResult.releasedate='+quotedstr(slCalendar(dbconnect,AdoQry_Main.fieldbyname('releasedate').asstring,fieldbyname('Qclt').asinteger+iifinteger(AdoQry_Main.fieldbyname('PmCode').asinteger=0,AdoQry_Main.fieldbyname('Preparelt').asinteger+getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,3),getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,4))))
         else
                  tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct #tInformalMrpResult.*,'
                    +floattostr(tmplowBomqty)+','
                    +' InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
                    +' noFinishqty=case ordinal when 2 then  Orderqty when 3 then null end,     '
                    +quotedstr(tmplowScrAp)+','
                    //+'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
                    +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from #tInformalMrpResult               '
                    +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                   // +' join Bom on InformalMrpResult.ItemCode=Bom.ItemCode'
                    +' where '+iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ')
                    +' and #tInformalMrpResult.ItemCode= '+quotedstr(copy(strpath,1,pos(',',strpath)-1))
                    +' and #tInformalMrpResult.releasedate='+quotedstr(slCalendar(dbconnect,AdoQry_Main.fieldbyname('releasedate').asstring,iifinteger(AdoQry_Main.fieldbyname('PmCode').asinteger=0,AdoQry_Main.fieldbyname('Preparelt').asinteger+getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,3),getlt(AdoQry_Main.fieldbyname('ItemCode').asstring,4))));
         Executesql(AdoQry,tMpsqltext,1);
   end;
   Executesql(AdoQry_Main,'select * from #tmpuPMrpResult where InformalMrpqty>0',0);
 finally
   AdoQry.free;
 end;
 //SelectFromSql:=' select distinct MrpResult.*,Bom.Bomqty,Mrpqty=case ordinal when 2 then Orderqty when 5 then netqty end,  '
 //              +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
 //              +'  Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,  '
 //              +'   Uom.UomName                           '
  //             +'   from MrpResult               '
  //             +' join Item on MrpResult.ItemCode=Item.ItemCode  '
  //             +' left outer join Uom on Item.UomCode=Uom.UomCode'
  //             +' join Bom on MrpResult.ItemCode=Bom.ItemCode';
 //Condition:='(ordinal=2 or ordinal=5) and Bom.ite_ItemCode='+quotedstr(AdoQry_Main.fieldbyname('ItemCode').asstring)+' and MrpResult.ItemCode='+quotedstr(copy(strpath,1,pos(',',strpath)-1));
// if AdoQry_Main.fieldbyname('duedate').asstring<>'' then
 //condition:=condition+' and MrpResult.releasedate='+quotedstr(AdoQry_Main.fieldbyname('duedate').asstring);
 strpath:=copy(strpath,pos(',',strpath)+1,length(strpath)-pos(',',strpath));
 //OrderByFields:='ItemCode,dueDate ';
 //GetData;}
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.Action2Execute(Sender: TObject);
var tMpstr:string;
begin
  inherited;
  tMpstr:=strpath;
 if not existsfArItemCode(AdoQry_Main.fieldbyname('ItemCode').asstring) then
     begin
     DispInfo(AdoQry_Main.fieldbyname('ItemCode').asstring+'无父项物料！',3);
     exit;
      end;
 strpath:=AdoQry_Main.fieldbyname('ItemCode').asstring+','+strpath;
 tmplowPmCode1:=AdoQry_Main.fieldbyname('PmCode').asinteger;
 if not ifexistsiteItemCode(AdoQry_Main.fieldbyname('ItemCode').asstring,AdoQry_Main.fieldbyname('releasedate').asstring,inttostr(AdoQry_Main.fieldbyname('Qclt').asinteger)) then
    begin
     DispInfo(AdoQry_Main.fieldbyname('ItemCode').asstring+'无父项物料需求！',3);
     strpath:=tMpstr;
     exit;
    end;
 tmplowPmCode1:=AdoQry_Main.fieldbyname('PmCode').asinteger;
 tmplowBomqty:=AdoQry_Main.fieldbyname('Bomqty').asfloat;
 tmplowScrAp:=AdoQry_Main.fieldbyname('ScrAprate').asstring;
 Executesql(AdoQry_Main,'select * from #tmpuPMrpResult where InformalMrpqty>0',0);
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.FormCreate(Sender: TObject);
begin
  inherited;
//tlbtn_Sum.Action:=action1;
//tlbtn_look.Action:=action2;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.ifexistsiteItemCode(ItemCode,
  date,Qclt: string): boolean;
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 #tmpopenBom,#tmpopenBomResult,#tmpuPMrpResult',1);
    except

    end;
 tMpsqltext:=' select top 0 #tInformalMrpResult.*,Bom.Bomqty,InformalMrpqty=case ordinal when 2 then Orderqty when 3 then onhand end,  '
               +'  noFinishqty=convert(float,0),   '
               +'  ScrAprate=convert(varchAr,Bom.BomScrAp_Percent)+''%'','
               +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'  Item.Qclt, '
               +'  Item.Preparelt,'
               +'   Uom.UomName                           '
               +'  into #tmpuPMrpResult '
               +'   from #tInformalMrpResult               '
               +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
               +' left outer join Uom on Item.UomCode=Uom.UomCode'
               +' join Bom on #tInformalMrpResult.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.Preparelt,Item.PmCode,Item.Preparelt+Item.runlt as purchldtime,Bomqty=sum(Bomqty),BomScrAprate=sum(BomScrAprate) '
             +' into #tmpopenBomResult  '
             +'  From #TmpOpenBom,Item      '
             +'  where #tmpopenBom.ItemCode=Item.ItemCode '
             +' group by Item.ItemCode,Item.Preparelt,Item.PmCode,Item.runlt,Item.Qclt';
 Executesql(AdoQry_tmp,tMpsqltext,1);
 Executesql(AdoQry_tmp,'select * from #tmpopenBomResult',0);
 with AdoQry_tmp do
   begin
    First;
     while not eof do
       begin
         if tmplowPmCode1=0 then
         tMpsqltext:='insert into #tmpuPMrpResult'
                    +' select distinct #tInformalMrpResult.*,#tmpopenBomResult.Bomqty,InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
                    +' noFinishqty=case ordinal when 2 then  Orderqty when 3 then null end,     '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from #tInformalMrpResult               '
                    +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on #tInformalMrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where '+iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ')
                    +'  and #tInformalMrpResult.canpegging=1    '
                    +' and #tInformalMrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and #tInformalMrpResult.releasedate=case #tInformalMrpResult.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 #tInformalMrpResult.*,#tmpopenBomResult.Bomqty,InformalMrpqty=case ordinal when 2 then realOrderqty when 3 then onhand end,  '
                    +' noFinishqty=case ordinal when 2 then  Orderqty when 3 then null end,     '
                    +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
                    +'  Itemflag=#tInformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
                    +'  Item.Qclt,'
                    +'  Item.Preparelt,'
                    +'   Uom.UomName                           '
                    +'   from #tInformalMrpResult               '
                    +' join Item on #tInformalMrpResult.ItemCode=Item.ItemCode  '
                    +' left outer join Uom on Item.UomCode=Uom.UomCode'
                    +' join #tmpopenBomResult on #tInformalMrpResult.ItemCode=#tmpopenBomResult.ItemCode'
                    +' where '+iifstring(peggingType=1,' ordinal=3 ',' (ordinal=2 or ordinal=3) ')
                    +' and #tInformalMrpResult.canpegging=1  '
                    +' and #tInformalMrpResult.ItemCode= '+quotedstr(fieldbyname('ItemCode').asstring)
                    +' and #tInformalMrpResult.releasedate=case #tInformalMrpResult.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   ';
         Executesql(AdoQry,tMpsqltext,1);
         next;
       end;
   end;
   Executesql(AdoQry,'select * from #tmpuPMrpResult where InformalMrpqty>0',0);
   if AdoQry.RecordCount>0 then
     begin
     Result:=True
     end
     else Result:=False;
  finally
    AdoQry.Free;
  end;
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
try
Executesql(AdoQry_tmp,'drop table #tmpopenBom,#tInformaLMrpResult,#tmpopenBomResult,#tmpuPMrpResult',1);
except
end;
end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.existsfArItemCode(
  ItemCode: string): boolean;
var AdoQry:TAdoQuery;
begin
  AdoQry:=TAdoQuery.Create(self);
  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_NewParentMo.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('Preparelt').asinteger+fieldbyname('runlt').asinteger;
      end;
      end;
  finally
    AdoQry.Free;
  end;
end;

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_NewParentMo.setItemInfo(ItemCode: string);
var AdoQry:TAdoQuery;
    sqltext:string;
begin
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.EnableBCD:=False;
  AdoQry.Connection:=dbconnect;
  sqltext:='select Item.*,Uom.UomName,Itemflag=ItemCode+'' ''+ItemName'+
               '   from Item,Uom '+
               ' where Item.UomCode*=Uom.UomCode '+
               '   and Item.ItemCode ='+quotedstr(ItemCode);
  try
    Executesql(AdoQry,sqltext,0);
    label3.Caption:=ItemCode;
    label4.Caption:=AdoQry.fieldbyname('ItemName').asstring;
    label6.Caption:=AdoQry.fieldbyname('UomName').asstring;
    case AdoQry.fieldbyname('ItemType').asinteger of
     0:  label8.Caption:='0 普通物料';
     1:  label8.Caption:='1 资源';
     2:  label8.Caption:='2 工具';
     3:  label8.Caption:='3 参考';
     4:  label8.Caption:='4 虚项';
     5:  label8.Caption:='5 劳务';
     6:  label8.Caption:='6 副产品';
     7:  label8.Caption:='7 成本';
    end;
    case AdoQry.fieldbyname('PmCode').asinteger of
     0:  label10.Caption:='0 制造';
     1:  label10.Caption:='1 采购';
     2:  label10.Caption:='2 委外';
     3:  label10.Caption:='3 既自制又委外';
    end;
//    label24.Caption:=floattostr(AdoQry.fieldbyname('mnldtime').asfloat);
//    label26.Caption:=floattostr(AdoQry.fieldbyname('purchldtime').asfloat);
    label28.Caption:=floattostr(AdoQry.fieldbyname('Preparelt').asfloat);
    label30.Caption:=floattostr(AdoQry.fieldbyname('runlt').asfloat);
    label32.Caption:=floattostr(AdoQry.fieldbyname('Qclt').asfloat);
    case AdoQry.fieldbyname('Batchstrat').asinteger of
     0:  label12.Caption:='0 直接批量法';
     1:  label12.Caption:='1 固定批量法';
    end;
    label14.Caption:=floattostr(AdoQry.fieldbyname('PmBatch').asfloat);
    label16.Caption:=floattostr(AdoQry.fieldbyname('maxqty').asfloat);
    label18.Caption:=floattostr(AdoQry.fieldbyname('minqty').asfloat);
  finally
    AdoQry.Free;
  end;
end;

end.