inv_monthreport_c.pas

一个MRPII系统源代码版本

    end
    else
      whCodecondition:=' and InvMonthSum.whCode='''+getCode(cmbbx_WhCode.text)+'''';
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,lm) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvblncAmount,0))'+
    '   from InvMonthSum,Item,#wtmp0 '+
    ' where InvMonthSum.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvMonth='''+stArtMonth+''''+whCodecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

    if cmbbx_WhCode.text='全部仓库' then
    begin
      whCodecondition:='';
    end
    else
      whCodecondition:=' and InvInBill.whCode='''+getCode(cmbbx_WhCode.text)+'''';

    //计算R1
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''r1''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,r1) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvInBill,InvInBillline,Item,#wtmp0 '+
    ' where InvInBillline.ItemCode=Item.ItemCode'+
    ' and InvInBill.InvBillid=InvInBillline.InvBillid'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvInBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

    //计算R2
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''r2''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,r2) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvInBill,InvInBillline,Item,#wtmp0 '+
    ' where InvInBillline.ItemCode=Item.ItemCode'+
    ' and InvInBill.InvBillid=InvInBillline.InvBillid'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvInBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

    //计算R3
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''r3''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,r3) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvInBill,InvInBillline,Item,#wtmp0 '+
    ' where InvInBillline.ItemCode=Item.ItemCode'+
    ' and InvInBill.InvBillid=InvInBillline.InvBillid'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvInBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;
    //计算R4
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''r4''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,r4) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvInBill,InvInBillline,Item,#wtmp0 '+
    ' where InvInBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvInBill.InvBillid=InvInBillline.InvBillid'+
    ' and InvInBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;
    //计算R5
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''r5''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,r5) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvInBill,InvInBillline,Item,#wtmp0 '+
    ' where InvInBillline.ItemCode=Item.ItemCode'+
    ' and InvInBill.InvBillid=InvInBillline.InvBillid'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvInBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

    //计算出仓
    if cmbbx_WhCode.text='全部仓库' then
    begin
      whCodecondition:='';
    end
    else
      whCodecondition:=' and InvOutBill.whCode='''+getCode(cmbbx_WhCode.text)+'''';

    //计算c1
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''c1''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,c1) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvOutBill,InvOutBillline,Item,#wtmp0 '+
    ' where InvOutBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvOutBill.InvBillid=InvOutBillline.InvBillid'+
    ' and InvOutBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

    //计算c2
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''c2''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,c2) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvOutBill,InvOutBillline,Item,#wtmp0 '+
    ' where InvOutBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvOutBill.InvBillid=InvOutBillline.InvBillid'+
    ' and InvOutBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;
    //计算c3
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''c3''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,c3) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvOutBill,InvOutBillline,Item,#wtmp0 '+
    ' where InvOutBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvOutBill.InvBillid=InvOutBillline.InvBillid'+
    ' and InvOutBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;
    //计算c4
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''c4''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,c4) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvOutBill,InvOutBillline,Item,#wtmp0 '+
    ' where InvOutBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvOutBill.InvBillid=InvOutBillline.InvBillid'+
    ' and InvOutBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;
    //计算c5
    Close;
    sql.clear;
    sql.Text:='select wherecondition from MaterialClasssum where fieldName=''c5''';
    open;
    wherecondition:=fieldbyname('wherecondition').asstring;
    if wherecondition<>'' then
       wherecondition:=' and '+wherecondition;
    Close;
    sql.clear;
    sql.Text:='insert #Materialsum(ClassCode,c5) '+
    '  select #wtmp0.pClassCode,sum(isnull(InvBillnotaxAmount,0))'+
    '   from InvOutBill,InvOutBillline,Item,#wtmp0 '+
    ' where InvOutBillline.ItemCode=Item.ItemCode'+
    ' and Item.ClassCode=#wtmp0.ClassCode '+
    ' and InvOutBill.InvBillid=InvOutBillline.InvBillid'+
    ' and InvOutBill.InvBillMonth='+quotedstr(medt_Date.text)+whCodecondition+wherecondition+
    ' group by #wtmp0.pClassCode ';
    ExecSQL;

   //汇总到#Materialsum1
    Close;
    sql.clear;
    sql.Text:='insert  #Materialsum1 select ClassCode,sum(lm),sum(r1) ,sum(r2),sum(r3),'+
              'sum(r4),sum(r5),0 ,'+
              'sum(c1),sum(c2),sum(c3),sum(c4),'+
              'sum(c5),0,0'+
              ' from #Materialsum group by ClassCode Order by ClassCode';
    ExecSQL;

    Close;
    sql.clear;
    sql.Text:='update #Materialsum1 set rj=r1+r2+r3+r4+r5,'+
              'cj=c1+c2+c3+c4+c5';
    ExecSQL;
    Close;
    sql.clear;
    sql.Text:='update #Materialsum1 set bl=lm+rj-(cj)';
    ExecSQL;

    {
    Close;
    sql.clear;
    sql.Text:='select * into aaaa from #Materialsum1 ';
    ExecSQL;
     }
  end;

  condition:='select m.ClassCode+'' ''+ItemClass.ClassName as ClassCode,'+
             ' lm,r1,r2,r3,r4,r5,rj,c1,c2,c3,c4,c5,cj,bl '+
             ' from #Materialsum1 m,ItemClass'+
             ' where m.ClassCode=ItemClass.ClassCode';
  ConditionHint:='月份：'+medt_Date.text+'/仓库：'+cmbbx_WhCode.Text+'/类别：'+ cmbbx_ItemClass.Text;
  modalResult:=mrok;




{

  with AdoQry_tmp do
  begin
    //按单据类型入库汇总
    Close;
    sql.clear;

    //不显示‘货位移动，待检入库’由于货位移动的IO=NULL，所以Stk模块只须把待检入库排除
    sql.Add('select BillTypeCode '+
            ' from BillType '+
            ' where io=0 '+
            '   and BillTypeCode<>''1102'' '+
            ' Order by BillTypeCode');
    open;
    First;

    if cmbbx_WhCode.text='全部仓库' then
    begin
      whCodecondition:='';
    end
    else
      whCodecondition:=' i.whCode='''+getCode(cmbbx_WhCode.text)+''' and ';

    while not eof do
    begin
      //产生临时表的表头入方字段