inv_dailyreport_c.pas

一个MRPII系统源代码版本

            ' from BillType '+
            ' where io=0 '+
            '   and BillTypeCode<>''1102'' '+
            ' Order by BillTypeCode');
    open;
    First;
    while not eof do
    begin
      //产生临时表的表头入方字段
      tableinfields:=tableinfields+'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' float null,';
      if sqlinsum='' then
      begin
        sqlinsum:=' sum(isnull(R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqlinremant:='R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqlinfields:='R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
                     ' when R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
                     'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end '
      end
      else
      begin
         sqlinsum:=sqlinsum+','+' sum(isnull(R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
         sqlinremant:=sqlinremant+'+R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
         sqlinfields:=sqlinfields+',R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
                     ' when R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
                     'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
      end;


      //把入方的数据根据入方单据类型按物料汇总，插入到临时表
      if (fieldbyname('BillTypeCode').asstring='0104') or
         (fieldbyname('BillTypeCode').asstring='0105') or
         (fieldbyname('BillTypeCode').asstring='0103')
         then
         tmp_SqlBillTypeCode:='   and i.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''''+
                              '   and i.InvBillwhchck<>0 '
      else
         tmp_SqlBillTypeCode:='   and i.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+'''';

      sql_SumInvInBillqty:=sql_SumInvInBillqty+
        ' insert #tmp1(ItemCode,'+
                       'R'+fieldbyname('BillTypeCode').asstring+')'+
        ' select '+
           ' il.ItemCode,'+
        '   sum(isnull(il.'+Qry_Sumfields+',0)) as InvInBillqty '+
        ' from InvInBill i,InvInBillline il '+
        ' where i.whCode='''+getCode(cmbbx_WhCode.text)+''''+
        '   and i.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
        tmp_SqlBillTypeCode+
        '   and i.InvBillid=il.InvBillid '+
        ' group by il.ItemCode ';

      //结帐月份到查询起始日期的数量 入方
      sql_SumInvInBillqty_qichu:=sql_SumInvInBillqty_qichu+
        ' insert #tmp3(ItemCode,'+
                       'R'+fieldbyname('BillTypeCode').asstring+')'+
        ' select '+
           ' il.ItemCode,'+
        '   sum(isnull(il.'+Qry_Sumfields+',0)) as InvInBillqty '+
        ' from InvInBill i,InvInBillline il '+
        ' where i.whCode='''+getCode(cmbbx_WhCode.text)+''''+
        '   and i.InvBilldate>'''+stArtMonth1+''''+
        '   and i.InvBilldate<'''+medt_Date_begin.text+''''+

        tmp_SqlBillTypeCode+
        '   and i.InvBillid=il.InvBillid '+
        ' group by il.ItemCode ';
      next;
    end;

    //出库
    Close;
    sql.clear;
    sql.Add(' select BillTypeCode '+
            ' from BillType '+
            ' where io=1 '+
            '  and BillTypeCode not in (select BillTypeCode '+
                                        ' from BillType '+
                                        ' where BillTypeCode like ''1%'') '+
            'Order by BillTypeCode');
    open;
    First;

    while not eof do
    begin
      //产生出方的临时表字段
      tableoutfields:= tableoutfields+'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' float null,';
      if sqloutsum='' then
      begin
        sqloutsum:=' sum(isnull(C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqloutremant:='C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqloutfields:='C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
                      ' when C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
                      'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
      end
      else
      begin
        sqloutsum:=sqloutsum+','+' sum(isnull(C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqloutremant:=sqloutremant+'+C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
        sqloutfields:=sqloutfields+',C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
                ' when C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
                'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
      end;
      //按单据类型出库按物料汇总，插入到临时表中
      If (fieldbyname('BillTypeCode').asstring='0201') or
         (fieldbyname('BillTypeCode').asstring='0203') or
         (fieldbyname('BillTypeCode').asstring='0204') or
         (fieldbyname('BillTypeCode').asstring='0299') then
          tmp_SqlBillTypeCode:='  and o.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''' '+
                             '   and o.InvBillwhchck<>0 ';
      //else
      if (fieldbyname('BillTypeCode').asstring='0202') then
          tmp_SqlBillTypeCode:='  and o.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''' ';

      sql_SumInvOutBillqty:=sql_SumInvOutBillqty+
          ' insert #tmp1(ItemCode,'+
                      'C'+fieldbyname('BillTypeCode').asstring+')'+
          '  select '+
             ' ol.ItemCode,'+
             ' sum(isnull(ol.'+Qry_Sumfields+',0)) as InvOutBillqty '+
          ' from InvOutBill o,InvOutBillline ol '+
          ' where o.whCode='''+getCode(cmbbx_WhCode.text)+''''+
          '   and o.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
          tmp_SqlBillTypeCode+
          '   and o.InvBillid=ol.InvBillid '+
          ' group by ol.ItemCode ';

      //结帐月份到查询起始日期的数量  出方
      sql_SumInvOutBillqty_qichu:=sql_SumInvOutBillqty_qichu+
          ' insert #tmp3(ItemCode,'+
                      'C'+fieldbyname('BillTypeCode').asstring+')'+
          '  select '+
             ' ol.ItemCode,'+
             ' sum(isnull(ol.'+Qry_Sumfields+',0)) as InvOutBillqty '+
          ' from InvOutBill o,InvOutBillline ol '+
          ' where o.whCode='''+getCode(cmbbx_WhCode.text)+''''+
          '   and o.InvBilldate>'''+stArtMonth1+''''+
          '   and o.InvBilldate<'''+medt_Date_begin.text+''''+
          tmp_SqlBillTypeCode+
          '   and o.InvBillid=ol.InvBillid '+
          ' group by ol.ItemCode ';
      next;
    end;
  end;

 // 产生临时表的语句 ,一个用于存放出，入数量，一个用于计算期初数量的出入数量
  sql_tmptable:=
    ' set noCount on '+
    ' create table #tmp1(ItemCode varchAr(16),'+tableinfields+tableoutfields+' remant float null,qichujiechun float null)'+
    ' create table #tmp3(ItemCode varchAr(16),'+tableinfields+tableoutfields+' qichu float null) ';

  // 从InvMonthSum中取得当前结帐月份的结存数量增加到#TMP3，
  // 结帐月份到查询起始日期的数量 入  增加到#TMP3，
  // 结帐月份到查询起始日期的数量 出  增加到#TMP3，
  //期初结存数从#TMP3中计算出来增加到TMP2表的 QICHUJIECHUN 字段中
  // #tmp4为一个过渡的临时表
  sql_qichujiechun:=
    sql_SumInvInBillqty_qichu+
    sql_SumInvOutBillqty_qichu+
    ' insert #tmp3(ItemCode,qichu) '+
    '  select ItemCode,'+Qry_Sumfields_InvMonthSum+
    '   from InvMonthSum '+
    ' where InvMonth='''+copy(stArtMonth,1,7)+''''+
    '   and whCode='''+getCode(cmbbx_WhCode.text)+''''+
    ' select ItemCode,'+sqlinsum+','+sqloutsum+',sum(isnull(qichu,0)) as qichu'+
    ' into #tmp4  from #tmp3  '+
    ' group by ItemCode  '+
    ' insert into #tmp1(ItemCode,qichujiechun) '+
    '   select ItemCode,qichu+'+sqlinremant+'-('+sqloutremant+')'+
    '  from #tmp4 ';

 //按物料汇总临时表#tmp1数量
  sql_Sum:=
    ' select ItemCode,'+
             sqlinsum+','+
             sqloutsum+
             ',sum(isnull(qichujiechun,0)) as qichujiechun '+
    ' into #tmp2  from #tmp1  '+
    ' group by ItemCode  ';

  //显示结果
  sql_Result:=
    ' select #tmp2.ItemCode,'+
    '  #tmp2.ItemCode+'+''' '''+'+ItemName as ItemCodeName,'+
    '  UomName,'+
       sqlinfields+','+
       sqloutfields+
       ',remant=case when (qichujiechun+'+ sqlinremant+'-('+sqloutremant+'))=0 then null else (qichujiechun+'+ sqlinremant+'-('+sqloutremant+')) end '+
       ',insum=case when '+sqlinremant+'=0 then null else '+sqlinremant+' end '+
       ',outsum=case when '+sqloutremant+'=0 then null else '+sqloutremant+' end '+
       ',qichujiechun=case when qichujiechun=0 then null else qichujiechun end '+
    ' from #tmp2 '+
    ' left join Item on  #tmp2.ItemCode=Item.ItemCode '+
    ' left join Uom on Item.UomCode=Uom.UomCode '+
    ' drop table #tmp1 '+
    ' drop table #tmp2 '+
    ' drop table #tmp3 '+
    ' drop table #tmp4 ';


 //最后的SQL语句

  sqltext:=sql_tmptable+
           sql_qichujiechun+
           sql_SumInvInBillqty+
           sql_SumInvOutBillqty+
           sql_Sum+
           sql_Result;

  condition:=sqltext;

  modalResult:=mrok;

end;

procedure TFrm_Inv_DailyReport_C.FormActivate(Sender: TObject);
begin
  inherited;
  initwhCode(tmp_userCode,tmp_Moduleid);
end;

end.