inv_mninsumqry_c.pas

一个MRPII系统源代码版本

unit Inv_MnInSumQry_C;

Interface

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

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

var
  Frm_Inv_MnInSumQry_C: TFrm_Inv_MnInSumQry_C;

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

procedure TFrm_Inv_MnInSumQry_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;}
  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;
  cmbbx_WhCode.Itemindex:=0;
end;

procedure TFrm_Inv_MnInSumQry_C.InitForm(UserCode:String;moduleid:string;LoginDate:string;tBillType:string);
begin
  medt_Date_begin.text:=datetostr(incMonth(strtodatetime(LoginDate),-1));
  medt_Date_End.text:=LoginDate;
  tmp_Moduleid:=moduleid;
  tmp_userCode:=userCode;
  tmp_BillType:=tBillType;
end;

procedure TFrm_Inv_MnInSumQry_C.btn_okClick(Sender: TObject);
var
  sqltext1,sqltext2:string;
begin
  inherited;
  //这是一个嵌套查询,条件是组合产生，所以把语句分成三部份，是后结果在condition<>''时组合生成
  sqltext1:=
    'select a.ItemCode,'+
       'a.ItemCode+'+''' '''+'+i.ItemName as ItemCodeName,'+
       'a.DeptCode+'+''' '''+'+d.DeptName as DeptName,'+
       'a.whCode+'+''' '''+'+w.whName as whName,'+
       'u.UomName,'+
       'a.InvBillqty,'+
       'Price='+
       'case a.InvBillqty '+
         'when 0 then 0 '+
         'else   a.InvBillnotaxAmount/a.InvBillqty'+
       ' end,'+
       'a.InvBillnotaxAmount'+
    ' from (select  i.whCode,'+
                   'i.DeptCode,'+
                   'il.ItemCode,'+
                   'sum(isnull(il.InvBillqty,0)) as InvBillqty,'+
                   'sum(isnull(il.InvBillnotaxAmount,0)) as InvBillnotaxAmount'+
            ' from InvInBill i,InvInBillline il ';
  if tmp_BillType='半成品' then
     sqltext2:=' and i.InvBillwhchck<>0'+
               ' and i.BillTypeCode in (''0104'')'+
               ' and i.InvBillid=il.InvBillid'+
            ' group by i.whCode,i.DeptCode,il.ItemCode ) a'+
    ' left join Item i  on a.ItemCode=i.ItemCode '+
    ' left join Dept d  on a.DeptCode=d.DeptCode '+
    ' left join Warehouse w on a.whCode=w.whCode '+
    ' left join Uom u on  i.UomCode=u.UomCode '
  else
     sqltext2:=' and i.InvBillwhchck<>0'+
               ' and i.BillTypeCode in (''0105'')'+
               ' and i.InvBillid=il.InvBillid'+
            ' group by i.whCode,i.DeptCode,il.ItemCode ) a'+
    ' left join Item i  on a.ItemCode=i.ItemCode '+
    ' left join Dept d  on a.DeptCode=d.DeptCode '+
    ' left join Warehouse w on a.whCode=w.whCode '+
    ' left join Uom u on  i.UomCode=u.UomCode ';
  condition:='';
  ConditionHint:='';

  if cmbbx_WhCode.text='全部仓库' then
  begin
  //  if uppercase(tmp_Moduleid)='Sfc' then
  //  begin
      condition:=' i.whCode in (select whCode from Warehouse ) ';
      ConditionHint:='仓库：全部仓库';
   // end
  //  else
  //  begin
  //    DispInfo('没有可用仓库！',1);
  //    abort;
   // end
  end
  else
  begin
    condition:=' i.whCode='''+getCode(cmbbx_WhCode.text)+'''';
    ConditionHint:='仓库：'+cmbbx_WhCode.text;
  end;

  if Extedt_Dept.text<>'' then
  begin
    condition:=condition+' and i.DeptCode like '''+Extedt_Dept.text+'%''';
    ConditionHint:=ConditionHint+'  部门：'+Extedt_Dept.text+' '+lbl_Dept.Caption;
  end;

  if medt_Date_begin.text>medt_Date_End.text then
  begin
    DispInfo('终止日期不能大于起始日期!',1);
    abort;
  end
  else
  begin
    condition:=condition+' and InvBilldate between '''+medt_Date_begin.text+''' and '''+medt_Date_End.text+'''';
    ConditionHint:=ConditionHint+' 日期从：'+medt_Date_begin.text+' 到 '+medt_Date_End.text;
  end;

  if condition<>'' then
    condition:=sqltext1+' where '+condition+sqltext2
  else
    abort;

  modalResult:=mrok;

end;

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

procedure TFrm_Inv_MnInSumQry_C.DateCheck(Sender: TObject);
begin
  inherited;
  if uppercase(tmp_Moduleid)='Stk' then
    InvDataChangeCheck(AdoQry_Tmp,copy(TEdit(Sender).Text,1,7));
end;

end.