inv_mnoutsumqry_c.pas

一个MRPII系统源代码版本

unit Inv_MnOutSumQry_C;
//程序员：
Interface

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

Type
  TFrm_Inv_MnOutSumQry_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 medt_Date_EndExit(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_MnOutSumQry_C: TFrm_Inv_MnOutSumQry_C;

implementation

uses Sys_Global,Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_MnOutSumQry_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;
    cmbbx_WhCode.Itemindex:=0;
 // end;
 // else
//  begin
//    cmbbx_WhCode.clear;
//    InitUsableWHCmBx(AdoQry_tmp,tmp_UserCode,cmbbx_WhCode,False);
//  end;
end;

procedure TFrm_Inv_MnOutSumQry_C.InitForm(UserCode:String;moduleid:string;LoginDate:string);
begin
  medt_Date_begin.Text:=FormatDateTime('yyyy.mm.dd',IncMonth(Now,-1));
  medt_Date_End.Text:=FormatDateTime('yyyy.mm.dd',Now);
  tmp_Moduleid:=moduleid;
  tmp_userCode:=userCode;
end;

procedure TFrm_Inv_MnOutSumQry_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 il.ItemCode,'+
                   'sum(isnull(il.InvBillqty,0)) as InvBillqty,'+
                   'sum(isnull(il.InvBillnotaxAmount,0)) as InvBillnotaxAmount'+
            ' from InvOutBill i,InvOutBillline il ';

     sqltext2:=' and i.InvBillwhchck<>0'+
               ' and i.BillTypeCode=''0201'''+
               ' and i.InvBillid=il.InvBillid'+
            ' group by 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
  else
  begin
    ConditionHint:=ConditionHint+'  部门：全部部门 '+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_MnOutSumQry_C.FormActivate(Sender: TObject);
begin
  inherited;
  initwhCode(tmp_userCode,tmp_Moduleid);
end;

procedure TFrm_Inv_MnOutSumQry_C.medt_Date_EndExit(Sender: TObject);
begin
  inherited;
  DateCheck(Sender);
  if Param1='Amount' then
  begin
    InvDataChangeCheck(AdoQry_Tmp,Copy(TEdit(Sender).Text,1,7));
  end;
end;

end.