inv_dailyreport_c.pas

一个MRPII系统源代码版本

unit Inv_DailyReport_C;
//程序员

{ 查询条件为O1仓库，日期为2001。06。01到达2001。06。27 最终显示的SQL语句如下:

set noCount on
 //创建临时表存放2001。06。01到达2001。06。27符合条件的记录
create table #tmp1(ItemCode varchAr(16),R0101 float null,R0102 float null,R0103 float null,R0104 float null,R0105 float null,R0199 float null,C0201 float null,C0202 float null,C0203 float null,C0204 float null,C0299 float null, remant float null,qichujiechun float null)
//创建临时表存放结帐月份到达2001。06。01符合条件的记录，用于计算期初数量
create table #tmp3(ItemCode varchAr(16),R0101 float null,R0102 float null,R0103 float null,R0104 float null,R0105 float null,R0199 float null,C0201 float null,C0202 float null,C0203 float null,C0204 float null,C0299 float null, qichu float null)
//产生期初数放到#TMP3中
insert #tmp3(ItemCode,R0101) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0101'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,R0102) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0102'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,R0103) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0103'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,R0104) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0104'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,R0105) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0105'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,R0199) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate>='2001.05.01'   and i.InvBilldate<'2001.06.01'   and i.BillTypeCode='0199'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp3(ItemCode,C0201)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate>='2001.05.01'   and o.InvBilldate<'2001.06.01'  and o.BillTypeCode='0201'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp3(ItemCode,C0202)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate>='2001.05.01'   and o.InvBilldate<'2001.06.01'  and o.BillTypeCode='0202'    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp3(ItemCode,C0203)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate>='2001.05.01'   and o.InvBilldate<'2001.06.01'  and o.BillTypeCode='0203'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp3(ItemCode,C0204)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate>='2001.05.01'   and o.InvBilldate<'2001.06.01'  and o.BillTypeCode='0204'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp3(ItemCode,C0299)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate>='2001.05.01'   and o.InvBilldate<'2001.06.01'  and o.BillTypeCode='0299'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode

insert #tmp3(ItemCode,qichu)   select ItemCode,InvBlncqty   from InvMonthSum  where InvMonth='2001.05'   and whCode='01' select ItemCode, sum(isnull(R0101,0)) as R0101, sum(isnull(R0102,0)) as R0102, sum(isnull(R0103,0)) as R0103, sum(isnull(R0104,0)) as R0104, sum(isnull(R0105,0)) as R0105, sum(isnull(R0199,0)) as R0199, sum(isnull(C0201,0)) as C0201, sum(isnull(C0202,0)) as C0202, sum(isnull(C0203,0)) as C0203, sum(isnull(C0204,0)) as C0204, sum(isnull(C0299,0)) as C0299,sum(isnull(qichu,0)) as qichu into #tmp4  from #tmp3   group by ItemCode

//产生期初结存数列数据
insert into #tmp1(ItemCode,qichujiechun)    select ItemCode,qichu+R0101+R0102+R0103+R0104+R0105+R0199-(C0201+C0202+C0203+C0204+C0299)  from #tmp4
//日期为2001。06。01到达2001。06。27 期间的数量
insert #tmp1(ItemCode,R0101) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0101'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,R0102) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0102'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,R0103) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0103'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,R0104) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0104'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,R0105) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0105'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,R0199) select  il.ItemCode,   sum(isnull(il.InvBillqty,0)) as InvInBillqty  from InvInBill i,InvInBillline il  where i.whCode='01'   and i.InvBilldate between '2001.06.01' and '2001.06.27'   and i.BillTypeCode='0199'   and i.InvBillid=il.InvBillid  group by il.ItemCode
insert #tmp1(ItemCode,C0201)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate between '2001.06.01' and '2001.06.27'  and o.BillTypeCode='0201'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp1(ItemCode,C0202)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate between '2001.06.01' and '2001.06.27'  and o.BillTypeCode='0202'    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp1(ItemCode,C0203)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate between '2001.06.01' and '2001.06.27'  and o.BillTypeCode='0203'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp1(ItemCode,C0204)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate between '2001.06.01' and '2001.06.27'  and o.BillTypeCode='0204'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
insert #tmp1(ItemCode,C0299)  select  ol.ItemCode, sum(isnull(ol.InvBillqty,0)) as InvOutBillqty  from InvOutBill o,InvOutBillline ol  where o.whCode='01'   and o.InvBilldate between '2001.06.01' and '2001.06.27'  and o.BillTypeCode='0299'    and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
//把所有数量汇总后放到#TMP2里得到最后计算结果
select ItemCode, sum(isnull(R0101,0)) as R0101, sum(isnull(R0102,0)) as R0102, sum(isnull(R0103,0)) as R0103, sum(isnull(R0104,0)) as R0104, sum(isnull(R0105,0)) as R0105, sum(isnull(R0199,0)) as R0199, sum(isnull(C0201,0)) as C0201, sum(isnull(C0202,0)) as C0202, sum(isnull(C0203,0)) as C0203, sum(isnull(C0204,0)) as C0204, sum(isnull(C0299,0)) as C0299,sum(isnull(qichujiechun,0)) as qichujiechun  into #tmp2  from #tmp1   group by ItemCode
//结果显示
select
 #tmp2.ItemCode,
 #tmp2.ItemCode+' '+ItemName as ItemCodeName,
 UomName,
R0101=case  when R0101=0 then null else R0101 end ,
R0102=case  when R0102=0 then null else R0102 end ,
R0103=case  when R0103=0 then null else R0103 end ,
R0104=case  when R0104=0 then null else R0104 end ,
R0105=case  when R0105=0 then null else R0105 end ,
R0199=case  when R0199=0 then null else R0199 end ,
C0201=case  when C0201=0 then null else C0201 end ,
C0202=case  when C0202=0 then null else C0202 end ,
C0203=case  when C0203=0 then null else C0203 end ,
C0204=case  when C0204=0 then null else C0204 end ,
C0299=case  when C0299=0 then null else C0299 end ,
remant=case when (qichujiechun+R0101+R0102+R0103+R0104+R0105+R0199-(C0201+C0202+C0203+C0204+C0299))=0 then null else (qichujiechun+R0101+R0102+R0103+R0104+R0105+R0199-(C0201+C0202+C0203+C0204+C0299)) end ,
insum=case when R0101+R0102+R0103+R0104+R0105+R0199=0 then null else R0101+R0102+R0103+R0104+R0105+R0199 end ,
outsum=case when C0201+C0202+C0203+C0204+C0299=0 then null else C0201+C0202+C0203+C0204+C0299 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
 }

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Condition, Db, AdODB, StdCtrls, Mask, ExtEdit;

Type
  TFrm_Inv_DailyReport_C = Class(TFrm_Base_Condition)
    cmbbx_WhCode: TComboBox;
    medt_Date_begin: TMaskEdit;
    medt_Date_End: TMaskEdit;
    Label1: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    tmp_userCode,tmp_Moduleid:string;
    procedure initwhCode(tmp_userCode:string;tmp_Moduleid:string);
    { Private declarations }
  public
    procedure InitForm(UserCode:String;moduleid:string;LoginDate:string);
    { Public declarations }
  end;

var
  Frm_Inv_DailyReport_C: TFrm_Inv_DailyReport_C;

implementation
uses Sys_Global;
{$R *.DFM}

procedure TFrm_Inv_DailyReport_C.initwhCode(tmp_userCode:string;tmp_Moduleid:string);
begin
  //初始化仓库控件
  if uppercase(tmp_Moduleid)='Sfc' then
  begin
    cmbbx_WhCode.clear;
    cmbbx_WhCode.Items.Add('');
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('select whCode,whName from Warehouse ');
      open;
      First;
      while not eof do
      begin
        cmbbx_WhCode.Items.Add(fieldbyname('whCode').asstring+' '+fieldbyname('whName').asstring);
        next;
      end;
    end;
  end
  else
  begin
    cmbbx_WhCode.clear;
    InitUsableWHCmBx(AdoQry_tmp,tmp_UserCode,cmbbx_WhCode,False);
  end;
end;

procedure TFrm_Inv_DailyReport_C.InitForm(UserCode:String;moduleid:string;LoginDate:string);
begin
  medt_Date_begin.text:=LoginDate;
  medt_Date_End.text:=LoginDate;
  tmp_Moduleid:=moduleid;
  tmp_userCode:=userCode;
end;

procedure TFrm_Inv_DailyReport_C.btn_okClick(Sender: TObject);
var
  stArtMonth,stArtMonth1,tableinfields,tableoutfields,sqlinsum,sqloutsum,sql_SumInvInBillqty,sql_SumInvOutBillqty,Qry_Sumfields_InvMonthSum,Qry_Sumfields:string;
  tmp_SqlBillTypeCode,sql_qichujiechun,sql_SumInvInBillqty_qichu,sql_SumInvOutBillqty_qichu,sql_tmptable,sql_Sum,sqltext,sqlinremant,sqloutremant,sqloutfields,sqlinfields,sql_Result:string;
begin
  inherited;
  //Stk模块要计算金额 ，所有的语句组合结果请看最顶上的注解。
  if  tmp_Moduleid='Inv' then
  begin
     Qry_Sumfields_InvMonthSum:='InvBlncqty';
     Qry_Sumfields:='InvBillqty';
  end
  else
  begin
     Qry_Sumfields_InvMonthSum:='InvBlncAmount';
     Qry_Sumfields:='InvBillnotaxAmount';
  end;

  condition:='';
  ConditionHint:='';
  tableinfields:='';
  tableoutfields:='';
  sqlinsum:='';
  sqloutsum:='';
  sql_SumInvInBillqty:='';
  sql_SumInvOutBillqty:='';
  sql_tmptable:='';
  sql_Sum:='';
  sqltext:='';
  sqlinremant:='';
  sqloutremant:='';
  sqloutfields:='';
  sqlinfields:='';
  sql_SumInvInBillqty_qichu:='';
  sql_SumInvOutBillqty_qichu:='';
  sql_qichujiechun:='';
  tmp_SqlBillTypeCode:='';
  //控制日期
  if medt_Date_begin.text>medt_Date_End.text then
  begin
    DispInfo('终止日期不能大于起始日期!',1);
    abort;
  end;

  conditionHint:='仓库：'+cmbbx_WhCode.text+'  日期：'+medt_Date_begin.text+' 到：'+medt_Date_End.text;
  //如果结帐月份大于等于查询月份，则期初月份为当前查询月份的前一个月
  //如果结帐月份小于查询月份，则期初月份为结帐月份
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add(' select left(convert(varchAr,Invstatus),7) as Invstatus '+
      '  from Invstatus '+
      '  where InvstatusName=''clsperiod'' ');
    open;
    if (fieldbyname('Invstatus').asstring>=copy(medt_Date_begin.text,1,7)) then
    begin
      stArtMonth:=copy(datetostr((strtodate((copy(medt_Date_begin.text,1,7)+'.01'))-1)),1,7)+'.01';
      stArtMonth1:=datetostr(incMonth(strtodate(stArtMonth),1)-1);
    end
    else
    begin
       stArtMonth:=Trim(fieldbyname('Invstatus').asstring)+'.01';
       stArtMonth1:=datetostr(incMonth(strtodate(stArtMonth),1)-1);
    end;
  end;

  with AdoQry_tmp do
  begin
    //按单据类型入库汇总
    Close;
    sql.clear;
    //Inv模块中不显示‘货位移动，待检入库，委外加工材料费，委外加工差异’
    //Stk模块中不显示‘货位移动，待检入库’由于货位移动的IO=NULL，所以Stk模块只须把待检入库排除
    if uppercase(tmp_Moduleid)='Inv' then
      sql.Add('select BillTypeCode '+
            ' from BillType '+
            ' where io=0 '+
            '  and BillTypeCode not in (select BillTypeCode '+
                                        ' from BillType '+
                                        ' where BillTypeCode like ''1%'') '+
            ' Order by BillTypeCode')
    else
      sql.Add('select BillTypeCode '+