inv_mnoutsumqry_c1.pas

一个MRPII系统源代码版本

unit Inv_MnOutSumQry_C1;
//程序员：
Interface

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

Type
  TFrm_Inv_MnOutSumQry_C1 = Class(TFrm_Base_Condition)
    cmbbx_WhCode: TComboBox;
    Extedt_Dept1: TExtEdit;
    medt_Date_begin: TMaskEdit;
    medt_Date_End: TMaskEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    lbl_Dept: TLabel;
    ExtEdt_ItemCode3: TLinkEdit;
    ExtEdt_ItemCode4: TLinkEdit;
    Label6: TLabel;
    Label7: TLabel;
    ExtEdt_ItemName3: TEdit;
    ExtEdt_ItemName4: TEdit;
    Extedt_Dept: TLinkEdit;
    Extedt_DeptName: TEdit;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    ExtEdt_ItemCode1: TLinkEdit;
    ExtEdt_ItemCode2: TLinkEdit;
    ExtEdt_ItemName1: TEdit;
    ExtEdt_ItemName2: TEdit;
    IsList: TCheckBox;
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure medt_Date_EndExit(Sender: TObject);
    procedure Edt_DeptCodeExit(Sender: TObject);
    procedure ExtEdt_ItemCode1Exit(Sender: TObject);
    procedure ExtEdt_ItemCode2Exit(Sender: TObject);
    procedure ExtEdt_ItemCode3Exit(Sender: TObject);
    procedure ExtEdt_ItemCode4Exit(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_C1: TFrm_Inv_MnOutSumQry_C1;

implementation

uses Sys_Global,Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_MnOutSumQry_C1.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_C1.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_C1.btn_okClick(Sender: TObject);
var
  SqlText,SqlText1,SqlText2,SqlText3,SqlText4,SqlText5:string;
begin
  inherited;
//这是一个嵌套查询,条件是组合产生，所以把语句分成三部份，是后结果在condition<>''时组合生成
//  Author:zhuzhongfu
//  Date:2001.12.22
  SqlText:='Select  TmpInvOutBill.Ite_ItemCode,TmpItem1.ItemName,TmpInvOutBill.Ite_ItemCode+'' ''+IsNull(TmpItem1.ItemName,'''') As Ite_ItemFlag, '+
                      'TmpInvOutBill.ItemCode,TmpItem2.ItemName,TmpInvOutBill.ItemCode+'' ''+TmpItem2.ItemName As ItemFlag, '+
                      'InvBillQty,Price=Case InvBillQty '+
                                      'When 0 then 0 '+
                                      'Else InvBillNotTaxAmount/InvBillQty*1.0 '+
                                  'end,InvBillNotTaxAmount,ItemCodeFlag '+
                  'From (Select Ite_ItemCode,ItemCode,Sum(IsNull(InvBillQty,0)) As InvBillQty, '+
                                                 'Sum(IsNull(InvBillNotTaxAmount,0)) As InvBillNotTaxAmount,ItemCodeFlag '+
                           'From (Select MoLine.ItemCode As Ite_ItemCode,InvOutBillLine.ItemCode, '+
                                            'Sum(IsNull(InvOutBillLine.InvBillQty,0)) As InvBillQty, '+
                                            'Sum(Isnull(InvOutBillLine.InvBillnotaxAmount,0)) As InvBillNotTaxAmount, '+
                                            'InvOutBillLine.ItemCode As ItemCodeFlag '+
                                        'From InvOutBill '+
                                             'Join InvOutBillLine On InvOutBill.InvBillId=InvOutBillLine.InvBillID '+
                                             'Join MoLine On MoLine.MONo+Convert(varchAr,MoLine.MoLineNo)= '+
                                                            'InvOutBill.MoNo+Convert(varchAr,InvOutBill.MoLineNo) ';


  SqlText1:=                            'Group By  MoLine.ItemCode,InvOutBillLine.ItemCode )TmpInvBill2 '+
                          'Group By Ite_ItemCode,ItemCode,ItemCodeFlag '+
                       'Union ';
  SqlText2:=           'Select InvOutBillLine.ItemCode+'' 合计'' As Ite_ItemCode,InvOutBillLine.ItemCode, '+
                                    'Sum(IsNull(InvOutBillLine.InvBillQty,0)) As InvBillQty, '+
                                    'Sum(Isnull(InvOutBillLine.InvBillnotaxAmount,0)) As InvBillNotTaxAmount, '+
                                    'InvOutBillLine.ItemCode+'' 合计'' As ItemCodeFlag '+
                               'From InvOutBill '+
                                    'Join InvOutBillLine On InvOutBill.InvBillId=InvOutBillLine.InvBillID ';

  SqlText3:=                  ' Group By  InvOutBillLine.ItemCode ) TmpInvOutBill '+
                   'Left Join Item TmpItem1 On TmpInvOutBill.Ite_ItemCode=TmpItem1.ItemCode '+
                   'Left Join Item TmpItem2 On TmpInvOutBill.ItemCode=TmpItem2.ItemCode '+
                'Order By ItemCodeFlag,Ite_ItemCode';

  SqlText4:='Select  TmpInvOutBill.Ite_ItemCode,TmpItem1.ItemName,TmpInvOutBill.Ite_ItemCode+'' ''+IsNull(TmpItem1.ItemName,'''') As Ite_ItemFlag, '+
                      'TmpInvOutBill.ItemCode,TmpItem2.ItemName,TmpInvOutBill.ItemCode+'' ''+TmpItem2.ItemName As ItemFlag, '+
                      'InvBillQty,Price=Case InvBillQty '+
                                      'When 0 then 0 '+
                                      'Else InvBillNotTaxAmount/InvBillQty*1.0 '+
                                  'end,InvBillNotTaxAmount,ItemCodeFlag '+
                  'From ( ';

  Condition:='Where ';
  ConditionHint:='';

  If Cmbbx_WhCode.text='全部仓库' Then
    begin
        Condition:=Condition+' InvOutBill.WHCode In (Select WHCode From Warehouse ) ';
        ConditionHint:='仓库：全部仓库';
    end
  Else
    begin
      Condition:=Condition+' InvOutBill.WHCode='''+GetCode(cmbbx_WhCode.text)+'''';
      ConditionHint:='仓库：'+cmbbx_WhCode.text;
    end;

  If Extedt_Dept.Text<>'' Then
    begin
      Condition:=condition+' and InvOutBill.DeptCode like '''+Extedt_Dept.text+'%''';
      ConditionHint:=ConditionHint+'  部门：'+Extedt_Dept.text+' '+lbl_Dept.Caption;
    end
  Else
    ConditionHint:=ConditionHint+'  部门：全部部门 '+lbl_Dept.Caption;

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

  Condition:=Condition+' And ISNull(InvOutBillLine.ItemCode,'''') Between '''+ExtEdt_ItemCode1.Text+''' And '''+ExtEdt_ItemCode2.Text+''' ';
  ConditionHint:=ConditionHint+' 子项代码从：'+ExtEdt_ItemCode1.Text+' 到 '+ExtEdt_ItemCode2.Text;
  Condition:=Condition+' And IsNull(Ite_ItemCode,'''') Between '''+ExtEdt_ItemCode3.Text+''' And '''+ExtEdt_ItemCode4.Text+''' ';
  ConditionHint:=ConditionHint+' 父项代码从：'+ExtEdt_ItemCode3.Text+' 到 '+ExtEdt_ItemCode4.Text;

  If Condition<>'' Then
     IF IsList.Checked=True Then
       Condition:=Sqltext4+SqlText2+Condition+SqlText3
     Else
       Condition:=Sqltext+Condition+Sqltext1+Sqltext2+Condition+SqlText3
  Else
    Abort;
  ModalResult:=Mrok;

{  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=1'+
               ' and i.BillTypeCode=''0201'')'+
               '  or (i.BillTypeCode=''0299'' and i.AmountAdjust=1))'+
               ' and i.WipWaster<>1 '+
               ' 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_C1.FormActivate(Sender: TObject);
begin
  inherited;
  initwhCode(tmp_userCode,tmp_Moduleid);
end;

procedure TFrm_Inv_MnOutSumQry_C1.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;

procedure TFrm_Inv_MnOutSumQry_C1.Edt_DeptCodeExit(Sender: TObject);
var
  SqlText:String;
begin
  If Trim(Extedt_Dept.Text)='' Then
    Abort;

  SqlText:=' Select * '
          +' From Dept '
          +' Where DeptCode='''+Trim(Extedt_Dept.Text)+'''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
  If AdoQry_Tmp.Eof Then
  begin
    DispInfo('领料部门代码错误，请重新输入!',1);
    TWinControl(Sender).SetFocus;
    Abort;
  end;
  Extedt_DeptName.Text:=AdoQry_Tmp.fieldbyname('DeptName').AsString;
end;

procedure TFrm_Inv_MnOutSumQry_C1.ExtEdt_ItemCode1Exit(Sender: TObject);
var
  SqlText:String;
begin
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  SqlText:=' Select Item.ItemName '
                +' From Item '
                +' Where ItemCode='+''''+Trim(ExtEdt_ItemCode1.Text)+''''
                +'  and ItemUsable=1';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
{  If (AdoQry_Tmp.Eof) And (ExtEdt_ItemCode1.Text<>'ZZZZZ')  Then
  begin
    DispInfo('输入了错误的物料代码，请重新输入!',3);
    TWincontrol(Sender).SetFocus;
    Abort;
  end;}
  ExtEdt_ItemName1.text:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
end;

procedure TFrm_Inv_MnOutSumQry_C1.ExtEdt_ItemCode2Exit(Sender: TObject);
var
  SqlText:String;
begin
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  SqlText:=' Select Item.ItemName '
                +' From Item '
                +' Where ItemCode='+''''+Trim(ExtEdt_ItemCode2.Text)+''''
                +'  and ItemUsable=1';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
{  If (AdoQry_Tmp.Eof) And (ExtEdt_ItemCode1.Text<>'ZZZZZ')  Then
  begin
    DispInfo('输入了错误的物料代码，请重新输入!',3);
    TWincontrol(Sender).SetFocus;
    Abort;
  end;}
  ExtEdt_ItemName2.text:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
end;

procedure TFrm_Inv_MnOutSumQry_C1.ExtEdt_ItemCode3Exit(Sender: TObject);
var
  SqlText:String;
begin
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  SqlText:=' Select Item.ItemName '
                +' From Item '
                +' Where ItemCode='+''''+Trim(ExtEdt_ItemCode3.Text)+''''
                +'  and ItemUsable=1';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
{  If (AdoQry_Tmp.Eof) And (ExtEdt_ItemCode1.Text<>'ZZZZZ')  Then
  begin
    DispInfo('输入了错误的物料代码，请重新输入!',3);
    TWincontrol(Sender).SetFocus;
    Abort;
  end;}
  ExtEdt_ItemName3.text:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
end;

procedure TFrm_Inv_MnOutSumQry_C1.ExtEdt_ItemCode4Exit(Sender: TObject);
var
  SqlText:String;
begin
  If ActiveControl.Name='btn_Cancel' Then
    Exit;
  SqlText:=' Select Item.ItemName '
                +' From Item '
                +' Where ItemCode='+''''+Trim(ExtEdt_ItemCode4.Text)+''''
                +'  and ItemUsable=1';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
{  If (AdoQry_Tmp.Eof) And (ExtEdt_ItemCode1.Text<>'ZZZZZ')  Then
  begin
    DispInfo('输入了错误的物料代码，请重新输入!',3);
    TWincontrol(Sender).SetFocus;
    Abort;
  end;}
  ExtEdt_ItemName4.text:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
end;


end.