inv_invcompare_c.pas

一个MRPII系统源代码版本

unit Inv_InvCompare_C;

Interface

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

Type
  TFrm_Inv_InvCompare_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    medt_Date_begin: TMaskEdit;
    medt_Date_End: TMaskEdit;
    Label2: TLabel;
    chbx_Sum: TCheckBox;
    cmbbx_Show: TComboBox;
    Label3: TLabel;
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(UserCode:String;moduleid:string;LoginDate:string);
    { Public declarations }
  end;

var
  Frm_Inv_InvCompare_C: TFrm_Inv_InvCompare_C;

implementation

uses Sys_Global;
{$R *.DFM}

{
程序员：
该程序最后生成的SQL语句
set noCount on
create table #tmp1(tdate datetime,
                   ItemCode varchAr(16),
                   ItemCode2 varchAr(8),
                   BillTypeCode varchAr(4),
                   oldBillqty decimal(20,8),
                   newBillqty decimal(20,8))
insert #tmp1(tdate,ItemCode,ItemCode2,BillTypeCode,oldBillqty)

select odate,ItemCode,ItemCode2,BillTypeCode,sum(isnull(Billqty,0))
from Involddata
group by odate,ItemCode,ItemCode2,BillTypeCode

insert #tmp1(tdate,BillTypeCode,ItemCode,ItemCode2,newBillqty)
select a.InvBilldate,a.BillTypeCode,a.ItemCode,i.ItemCode2,a.InvQty
from
(
select i.InvBilldate,i.BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty
  from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid
 where i.InvBilldate between '2001.07.01' and '2001.07.24'
   and i.BillTypeCode='0101'
group by i.InvBilldate,i.BillTypeCode,il.ItemCode
union
select i.InvBilldate,'0101' as BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty
  from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid
 where i.InvBilldate between '2001.07.01' and '2001.07.24'
   and i.BillTypeCode='0102'
group by i.InvBilldate,BillTypeCode,il.ItemCode
union
select i.InvBilldate,i.BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty
  from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid
 where i.InvBilldate between '2001.07.01' and '2001.07.24'
   and i.BillTypeCode='0103'
group by i.InvBilldate,i.BillTypeCode,il.ItemCode
union
select o.InvBilldate,o.BillTypeCode,ol.ItemCode,sum(isnull(ol.InvBillqty,0)) as InvQty
  from InvOutBill o join InvOutBillline ol on o.InvBillid=ol.InvBillid
 where o.InvBilldate between '2001.07.01' and '2001.07.24'
   and o.InvBillwhchck=1
   and o.BillTypeCode='0201'
group by o.InvBilldate,o.BillTypeCode,ol.ItemCode
union
select o.InvBilldate,o.BillTypeCode,ol.ItemCode,sum(isnull(ol.InvBillqty,0)) as InvQty
  from InvOutBill o join InvOutBillline ol on o.InvBillid=ol.InvBillid
 where o.InvBilldate between '2001.07.01' and '2001.07.24'
   and o.InvBillwhchck=1
   and o.BillTypeCode='0202'
group by o.InvBilldate,o.BillTypeCode,ol.ItemCode
) a
left join Item i on a.ItemCode=i.ItemCode

select b.tdate,b.BillTypeCode,b.BillTypeCode+' '+bt.BillTypeName,b.ItemCode,b.ItemCode+' '+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty
from
(
select tdate,BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty
from #tmp1
group by tdate,BillTypeCode,ItemCode,ItemCode2 ) b
left join BillType bt on b.BillTypeCode=bt.BillTypeCode
left join Item i on b.ItemCode=i.ItemCode
Order by b.tdate,b.ItemCode,b.ItemCode2,b.BillTypeCode
drop table #tmp1
}

procedure TFrm_Inv_InvCompare_C.InitForm(UserCode:String;moduleid:string;LoginDate:string);
begin
  //
end;

procedure TFrm_Inv_InvCompare_C.btn_okClick(Sender: TObject);
var
  showcolumns:string;
begin
  inherited;
  if medt_Date_End.Text<medt_Date_begin.text then
  begin
    DispInfo('起始日期不能大于终止日期！',1);
    abort;
  end;
  if chbx_Sum.checked=False then
  begin
     conditionHint:='日期从:'+ medt_Date_begin.text+' 到'+medt_Date_End.text;
     showcolumns:='True';
  end
  else
  begin
     conditionHint:='日期从:'+ medt_Date_begin.text+' 到'+medt_Date_End.text+' 汇总查询';
     showcolumns:='False';
  end;
  condition:=
    ' set noCount on '+
    ' create table #tmp1(tdate datetime, '+
                   'ItemCode varchAr(16) null,'+
                   'ItemCode2 varchAr(8) null,'+
                   'BillTypeCode varchAr(4),'+
                   'oldBillqty decimal(20,8),'+
                   'newBillqty decimal(20,8)) '+
    ' insert #tmp1(tdate,ItemCode,ItemCode2,BillTypeCode,oldBillqty) '+
    '   select odate,ItemCode,ItemCode2,BillTypeCode,sum(isnull(Billqty,0)) '+
    '     from Involddata '+
    '     where odate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
    '    group by odate,ItemCode,ItemCode2,BillTypeCode '+
    ' insert #tmp1(tdate,BillTypeCode,ItemCode,ItemCode2,newBillqty) '+
    '   select a.InvBilldate,a.BillTypeCode,a.ItemCode,i.ItemCode2,a.InvQty '+
    '     from '+
    '      ( '+
           ' select i.InvBilldate,i.BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty '+
           '    from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid '+
           '    where i.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
                ' and i.BillTypeCode=''0101'' '+
                ' group by i.InvBilldate,i.BillTypeCode,il.ItemCode '+
           ' union '+
           ' select i.InvBilldate,''0101'' as BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty '+
           '   from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid '+
           '   where i.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
               ' and i.BillTypeCode=''0102'' '+
               ' group by i.InvBilldate,BillTypeCode,il.ItemCode '+
           ' union '+
           ' select i.InvBilldate,i.BillTypeCode,il.ItemCode,sum(isnull(il.InvBillqty,0)) as InvQty '+
           '   from InvInBill i join InvInBillline il on i.InvBillid=il.InvBillid '+
           '   where i.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
               ' and i.BillTypeCode=''0103'' '+
               ' group by i.InvBilldate,i.BillTypeCode,il.ItemCode '+
           ' union '+
           ' select o.InvBilldate,o.BillTypeCode,ol.ItemCode,sum(isnull(ol.InvBillqty,0)) as InvQty '+
           '   from InvOutBill o join InvOutBillline ol on o.InvBillid=ol.InvBillid '+
           '   where o.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
               ' and o.BillTypeCode=''0201'' '+
               ' and o.InvBillwhchck=1 '+
               ' group by o.InvBilldate,o.BillTypeCode,ol.ItemCode '+
           ' union '+
           ' select o.InvBilldate,o.BillTypeCode,ol.ItemCode,sum(isnull(ol.InvBillqty,0)) as InvQty '+
           '   from InvOutBill o join InvOutBillline ol on o.InvBillid=ol.InvBillid '+
           '  where o.InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+''''+
              ' and o.BillTypeCode=''0202'' '+
              ' group by o.InvBilldate,o.BillTypeCode,ol.ItemCode '+
           ') a '+
       ' left join Item i on a.ItemCode=i.ItemCode ';
    if chbx_Sum.checked=False then
    begin
      if cmbbx_Show.Text='存在差异' then
        condition:=condition+
        ' select b.tdate,b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select tdate,BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by tdate,BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' where (b.newBillqty-b.oldBillqty)<>0 '+
        ' Order by b.tdate,b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 '
      else if  cmbbx_Show.Text='单方存在' then
        condition:=condition+
        ' select b.tdate,b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select tdate,BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by tdate,BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' where (b.ItemCode is null) or (b.ItemCode2 is null) or (b.ItemCode2='''') '+
        ' Order by b.tdate,b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 '
      else
        condition:=condition+
        ' select b.tdate,b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select tdate,BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by tdate,BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' Order by b.tdate,b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 ';
    end
    else
    begin
      if cmbbx_Show.Text='存在差异' then
        condition:=condition+
        ' select b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' where (b.newBillqty-b.oldBillqty)<>0 '+
        ' Order by b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 '
      else if cmbbx_Show.Text='单方存在' then
        condition:=condition+
        ' select b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' where (b.ItemCode is null) or (b.ItemCode2 is null) or (b.ItemCode2='''') '+
        ' Order by b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 '
      else
        condition:=condition+
        ' select b.BillTypeCode,b.BillTypeCode+'+''' '''+'+bt.BillTypeName as BillTypeName,b.ItemCode,b.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,b.ItemCode2,b.newBillqty,b.oldBillqty,(b.newBillqty-b.oldBillqty) as Compareqty '+
        '   from '+
              ' ( '+
                ' select BillTypeCode,ItemCode,ItemCode2,sum(isnull(oldBillqty,0)) as oldBillqty,sum(isnull(newBillqty,0)) as newBillqty '+
                ' from #tmp1 '+
                ' group by BillTypeCode,ItemCode,ItemCode2 '+
              ' ) b '+
        ' left join BillType bt on b.BillTypeCode=bt.BillTypeCode '+
        ' left join Item i on b.ItemCode=i.ItemCode '+
        ' Order by b.ItemCode,b.ItemCode2,b.BillTypeCode '+
        ' drop table #tmp1 ';
    end;
  FatherForm.SetFormParam(showcolumns,'','','','','');
  modalResult:=mrok;
end;

procedure TFrm_Inv_InvCompare_C.FormActivate(Sender: TObject);
begin
  inherited;
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select max(odate) as maxodate,min(odate) as minodate from Involddata ');
    open;
    medt_Date_begin.text:=fieldbyname('minodate').asstring;
    medt_Date_End.text:=fieldbyname('maxodate').asstring;
  end;
  cmbbx_Show.Itemindex:=0;
end;

end.