inv_monthreport_c.pas

一个MRPII系统源代码版本

unit Inv_MonthReport_C;
//程序员

{ 最终显示的SQL语句如下:
set noCount on
create table #tmp1(ItemCode varchAr(16),R0101 float null,R0102 float null,R0103 float null,R0104 float null,R0105 float null,R0199 float null,C0202 float null, remant float null,qichujiechun float null)
create table #tmp3(ItemCode varchAr(16),R0101 float null,R0102 float null,R0103 float null,R0104 float null,R0105 float null,R0199 float null,C0202 float null, qichu float null)

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.01.01'   and i.InvBilldate<'2001.02.18'   and i.BillTypeCode='0101'   and i.InvBillwhchck<>0    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.01.01'   and i.InvBilldate<'2001.02.18'   and i.BillTypeCode='0102'   and i.InvBillwhchck<>0    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.01.01'   and i.InvBilldate<'2001.02.18'   and i.BillTypeCode='0103'   and i.InvBillwhchck<>0 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.01.01'   and i.InvBilldate<'2001.02.18'   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.01.01'   and i.InvBilldate<'2001.02.18'   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.01.01'   and i.InvBilldate<'2001.02.18'   and i.BillTypeCode='0199'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.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.02.18'   and o.InvBilldate<'2001.06.18'   and o.BillTypeCode='0202'   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.01'   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(C0202,0)) as C0202,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-C0202  from #tmp4

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.02.18' and '2001.06.18'   and i.BillTypeCode='0101'   and i.InvBillwhchck<>0    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.02.18' and '2001.06.18'   and i.BillTypeCode='0102'   and i.InvBillwhchck<>0    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.02.18' and '2001.06.18'   and i.BillTypeCode='0103'   and i.InvBillwhchck<>0    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.02.18' and '2001.06.18'   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.02.18' and '2001.06.18'   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.02.18' and '2001.06.18'   and i.BillTypeCode='0199'   and i.InvBillwhchck<>0    and i.InvBillid=il.InvBillid  group by il.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.02.18' and '2001.06.18'   and o.BillTypeCode='0202'   and o.InvBillwhchck<>0    and o.InvBillid=ol.InvBillid  group by ol.ItemCode
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(C0202,0)) as C0202,sum(isnull(qichujiechun,0)) as qichujiechun  into #tmp2  from #tmp1   group by ItemCode
select #tmp2.ItemCode,  #tmp2.ItemCode+' '+ItemName as ItemCodeName,  UomName,R0101,R0102,R0103,R0104,R0105,R0199,C0202,(qichujiechun+R0101+R0102+R0103+R0104+R0105+R0199-C0202) as remant ,R0101+R0102+R0103+R0104+R0105+R0199 as insum ,C0202 as outsum ,qichujiechun 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_MonthReport_C = Class(TFrm_Base_Condition)
    cmbbx_WhCode: TComboBox;
    medt_Date: TMaskEdit;
    Label1: TLabel;
    Label3: TLabel;
    cmbbx_ItemClass: TComboBox;
    Label2: TLabel;
    AdoQry_tmp1: TAdoQuery;
    AdoQry_tmp2: TAdoQuery;
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure cmbbx_WhCodeExit(Sender: TObject);
    procedure MonthCheck(Sender: TObject);
    procedure cmbbx_ItemClassExit(Sender: TObject);
  private
    tmp_userCode,tmp_Moduleid:string;
    procedure initcmbbx_ItemClass;
    { Private declarations }
  public
    procedure InitForm(UserCode:String;moduleid:string;LoginDate:string);
    procedure SetDBConnect(AdOConnection:TAdOConnection); Override;
    { Public declarations }
  end;

var
  Frm_Inv_MonthReport_C: TFrm_Inv_MonthReport_C;

implementation

uses Sys_Global,Inv_Global;
{$R *.DFM}

procedure TFrm_Inv_MonthReport_C.initcmbbx_ItemClass;
var
  n,i:integer;     //物料类别有多少级
  ns0,ns1:string;
begin

  cmbbx_ItemClass.clear;
  n:=0;
  AdoQry_tmp.Close;
  AdoQry_tmp.sql.clear;
  AdoQry_tmp.sql.Add('select max(ClassgrAde) as ClassgrAde from ItemClass');
  AdoQry_tmp.open;
  n:=AdoQry_Tmp.fieldbyname('ClassgrAde').AsInteger;
  for i :=1  to n do
  begin
     cmbbx_ItemClass.Items.Add('第'+Trim(inttostr(i))+'级');
  end;
//  cmbbx_ItemClass.Items.Add('全部');
  cmbbx_ItemClass.Itemindex:=0;
end;

procedure TFrm_Inv_MonthReport_C.InitForm(UserCode:String;moduleid:string;LoginDate:string);
begin

  medt_Date.text:=LoginDate;
  tmp_Moduleid:=moduleid;
  tmp_userCode:=userCode;

end;

procedure TFrm_Inv_MonthReport_C.btn_okClick(Sender: TObject);
var
  ItemClass,stArtMonth,stArtMonth1,tableinfields,tableoutfields,sqlinsum,sqloutsum,sql_SumInvInBillqty,sql_SumInvOutBillqty,Qry_Sumfields_InvMonthSum,Qry_Sumfields,ClassCodelen:string;
  tmp_SqlBillTypeCode,sql_qichujiechun,sql_SumInvInBillqty_qichu,sql_SumInvOutBillqty_qichu,sql_tmptable,sql_Sum,sqltext,sqlinremant,
  sqloutremant,sqloutfields,sqlinfields,sql_Result,whCodecondition,ClassCode1,ClassCode2,tmpClassCode:string;
  jzMonth,cxMonth,wherecondition:string;
  ClassgrAde:integer;
begin
  inherited;
 //所有的语句组合结果请看最顶上的注解。
 // Frm_Inv_MonthReport_C.cursor:= crHourGlass;
  Qry_Sumfields_InvMonthSum:='InvBlncAmount';
  Qry_Sumfields:='InvBillnotaxAmount';
  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:='';
  ClassCodelen:='0';
  whCodecondition:='';
//  jzMonth:=0;
//  cxMonth:=0;
  ClassgrAde:=1;
  if Trim(cmbbx_ItemClass.text)='' then
  begin
    DispInfo('物料类别不能为空！',1);
    exit;
  end;
  if Trim(cmbbx_ItemClass.text)='全部' then
  else
  begin
    ClassgrAde:=strtoint(copy(Trim(cmbbx_ItemClass.text),3,1));
  end;
  try
    AdoQry_tmp.Close;
    AdoQry_tmp.sql.clear;
    AdoQry_tmp.sql.Add('drop table #wtmp0,#wtmp1,#wtmp2,#wtmp3');
    AdoQry_tmp.ExecSQL;
  except

  end;
  with AdoQry_tmp do
  begin
  //从用户定义的级别中找出没有子项的类
  // #wtmp0记录了该级别下的所有子类
    Close;
    sql.clear;
    sql.Add(' create table #wtmp0(pClassCode varchAr(16),ClassCode varchAr(16))'+
            ' insert #wtmp0 select a.ClassCode,a.ClassCode from '+
            ' (select ClassCode from ItemClass where ClassgrAde='+inttostr(ClassgrAde)+') a '+
            ' where a.ClassCode not in (select pClassCode from ItemClass)');
    ExecSQL;
    //找出所有该级别的类
    Close;
    sql.clear;
    sql.Add('select ClassCode from ItemClass where ClassgrAde='+inttostr(ClassgrAde));
    open;
    First;
    while not eof do
    begin
      tmpClassCode:=fieldbyname('ClassCode').asstring;
      //逐个找子项
      AdoQry_tmp1.Close;
      AdoQry_tmp1.sql.clear;
      AdoQry_tmp1.sql.Add('select ClassCode into #wtmp1 from ItemClass where pClassCode='+quotedstr(tmpClassCode));
      AdoQry_tmp1.ExecSQL;
      AdoQry_tmp2.Close;
      AdoQry_tmp2.SQL.clear;
      AdoQry_tmp2.sql.Add('select * from #wtmp1');
      AdoQry_tmp2.open;
      while not AdoQry_tmp2.eof do
      begin
        AdoQry_tmp2.Close;
        AdoQry_tmp2.SQL.clear;
        AdoQry_tmp2.sql.Add('insert #wtmp0 select '+quotedstr(tmpClassCode)+',ClassCode from #wtmp1');
        AdoQry_tmp2.ExecSQL;
        AdoQry_tmp1.Close;
        AdoQry_tmp1.sql.clear;
        AdoQry_tmp1.sql.Add('select ClassCode into #wtmp2 from ItemClass'+
                            ' where pClassCode in (select ClassCode from #wtmp1)'+
                            ' delete from #wtmp1'+
                            ' insert #wtmp1 select * from #wtmp2'+
                            ' drop table #wtmp2' );
        AdoQry_tmp1.ExecSQL;
        AdoQry_tmp2.Close;
        AdoQry_tmp2.SQL.clear;
        AdoQry_tmp2.sql.Add('select * from #wtmp1');
        AdoQry_tmp2.open;
      end;
      AdoQry_tmp1.Close;
      AdoQry_tmp1.sql.clear;
      AdoQry_tmp1.sql.Add(' drop table #wtmp1 ' );
      AdoQry_tmp1.ExecSQL;
      next;
    end;

  end;


  //如果结帐月份大于等于查询月份，则期初月份为当前查询月份的前一个月
  //如果结帐月份小于查询月份，则期初月份为结帐月份
  //查询月份-结帐月份<=1
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add(' select left(convert(varchAr,Invstatus),7) as Invstatus '+
      '  from Invstatus '+
      '  where InvstatusName=''clsperiod'' ');
    open;
    jzMonth:=fieldbyname('Invstatus').asstring;
    if incMonth(strtodate(medt_Date.text+'.01'),-1)>strtodate(jzMonth+'.01') then
    begin
      showmessage(formatdatetime('yyyy.mm',incMonth(strtodate(medt_Date.text+'.01'),-1))+'尚未结帐，无法获取期初数据！');
      exit;
    end;
    stArtMonth:=FormatDateTime('yyyy.mm',incMonth(strtodate(medt_Date.text+'.01'),-1));
  end;

  with AdoQry_tmp do
  begin
    try
      Close;
      sql.clear;
      sql.Text:='drop table #Materialsum,#Materialsum1';
      ExecSQL;
    except
    end;

    //产生临时表
    Close;
    sql.clear;
    sql.Text:='create table #Materialsum(ClassCode varchAr(16),lm decimal(20,8) default 0,'+
              'r1 decimal(20,8) default 0,'+
              'r2 decimal(20,8) default 0,'+
              'r3 decimal(20,8) default 0,'+
              'r4 decimal(20,8) default 0,'+
              'r5 decimal(20,8) default 0,'+
              'rj decimal(20,8) default 0,'+
              'c1 decimal(20,8) default 0,'+
              'c2 decimal(20,8) default 0,'+
              'c3 decimal(20,8) default 0,'+
              'c4 decimal(20,8) default 0,'+
              'c5 decimal(20,8) default 0,'+
              'cj decimal(20,8) default 0,'+
              'bl decimal(20,8) default 0)';
    ExecSQL;
    Close;
    sql.clear;
    sql.Text:='create table #Materialsum1(ClassCode varchAr(16),lm decimal(20,8) default 0,'+
              'r1 decimal(20,8) default 0,'+
              'r2 decimal(20,8) default 0,'+
              'r3 decimal(20,8) default 0,'+
              'r4 decimal(20,8) default 0,'+
              'r5 decimal(20,8) default 0,'+
              'rj decimal(20,8) default 0,'+
              'c1 decimal(20,8) default 0,'+
              'c2 decimal(20,8) default 0,'+
              'c3 decimal(20,8) default 0,'+
              'c4 decimal(20,8) default 0,'+
              'c5 decimal(20,8) default 0,'+
              'cj decimal(20,8) default 0,'+
              'bl decimal(20,8) default 0)';
    ExecSQL;

    //计算期初数据LM
    if cmbbx_WhCode.text='全部仓库' then
    begin
      whCodecondition:='';