sfc_mooutqry.pas

一个MRPII系统源代码版本

unit Sfc_MoOutQry;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, Mask, DBCtrls;

Type
  TFrm_Sfc_MoOutQry = Class(TFrm_Base_Qry)
    Label1: TLabel;
    AdoQry_MainMoNo: TStringField;
    AdoQry_MainMoLineNo: TIntegerField;
    AdoQry_MainItemCodeName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainMOCtrlQty: TFloatField;
    AdoQry_MainOutQty: TFloatField;
    AdoQry_MainQtyDiff: TFloatField;
    AdoQry_MainQtyDiffPercent: TStringField;
    AdoQry_MainAmountDiff: TFloatField;
    Label2: TLabel;
    Lbl_ItemCodeName: TLabel;
    DBEdt_Item: TDBEdit;
    Label3: TLabel;
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure AdoQry_MainAfterScroll(DataSet: TDataSet);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    FStArtMoNo,FendMoNo,FDeptCode :String;
    FLineStatus :string;
    { Private declarations }
  public
    stArtno,endno:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Sfc_MoOutQry: TFrm_Sfc_MoOutQry;

implementation
Uses Sfc_MoOutQry_C;
{$R *.DFM}

{ TFrm_Sfc_MoOutQry }

procedure TFrm_Sfc_MoOutQry.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
   Application.CreateForm(TFrm_Sfc_MoOutQry_C,Frm_Sfc_MoOutQry_C);
   Act_Filter.Execute;
end;

procedure TFrm_Sfc_MoOutQry.Act_FilterExecute(Sender: TObject);
begin
//  inherited;
  Frm_Sfc_MoOutQry_C.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sfc_MoOutQry_C.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sfc_MoOutQry_C.SetDBConnect(DBConnect);
  if Frm_Sfc_MoOutQry_C.Showmodal=mrOk then
  begin
    Lbl_Condition.Caption:=Frm_Sfc_MoOutQry_C.ConditionHint;
    FStArtMoNo:=Frm_Sfc_MoOutQry_C.FStArtMoNo;
    FendMoNo:=Frm_Sfc_MoOutQry_C.FendMoNo;
    FDeptCode:=Frm_Sfc_MoOutQry_C.FDeptCode;
    FLineStatus:=Frm_Sfc_MoOutQry_C.FLineStatus;
    SelectFromSql:=' select MI.MoNo,Mi.MoLineNo, '+
          ' MI.ItemCode+'' ''+Item.ItemName As ItemCodeName, '+
          ' Uom.UomName,MI.MOCtrlQty,S.OutQty, '+
          ' Round(S.OutQty-MI.MOCtrlQty,2) QtyDiff,  '+
          ' case   '+
          '   when MI.MOCtrlQty<>0 then convert(varchAr(40),(S.OutQty-MI.MoCtrlQty)/MI.MoCtrlQty*100) '+
          '    else ''--'''+
          ' end QtyDiffPercent, '+  //差异率
          ' case '+
          '   when S.OutQty<>0 then Round((S.OutQty-MI.MoCtrlQty)*(S.OutAmount/S.OutQty),2) '+
          '    else 0 '+
          ' end AmountDiff  '+   //差异额
          ' from MNItemList MI  '+
          ' Left Join '+
          ' (select BL.MONo,BL.MoLineNo,BL.ItemCode,sum(InvBillQty) OutQty, '+
          ' Sum(InvBillNoTaxAmount) OutAmount '+
          'from InvOutBillLine BL,InvOutBill B,MO,MoLine '+
          'where mo.MONo between '''+FStArtMoNo+''' and  '''+FendMoNo+''' and '+
          '  b.DeptCode like '+quotedstr(FDeptCode+'%')+' and  '+
          '  MoLine.MoNO=MO.MoNo and '+
          FLineStatus+' and '+
          '   BL.MONo=MoLine.MoNo and '+
          '   BL.MoLineNo=MoLine.MoLineNo and '+
          '   B.InvBillWHChck=1  and '+ //仓库核定过的
             //从领料单据中获取
          '  B.InvBillId=BL.InvBillId '+
       ' group by BL.MoNo,Bl.MoLineNo,BL.ItemCode) S '+  //汇总出累计领用数量和金额
       ' On (S.MoNo=Mi.MoNo) and (S.MoLineNo=Mi.MoLineNo) and '+
       ' (S.ItemCode=Mi.ItemCode) '+
       ' Join Item on Item.ItemCode=Mi.ItemCode  '+
       ' Join Uom on Uom.UomCode=Item.UomCode ,mo,MoLine';
    if endno='' then
    condition:=' MI.MoNo='+format('''%s''',[stArtno])
    else
    Condition:=' MI.MoNo>='+format('''%s''',[stArtno])+
               ' and MI.MoNo<='+format('''%s''',[endno]);
    if stArtno='' then condition:='';
    if condition<>'' then condition:=condition+' and ';
    condition:=condition+
               FLineStatus+' and '+
               ' mo.mono=MoLine.mono and '+
               ' mo.mono=MI.mono and '+
               ' MoLine.MoLineno=MI.MoLineno and '+
               ' (mo.DeptCode='+quotedstr(FDeptCode)+
               ' or mo.DeptCode='+quotedstr(copy(FDeptCode,1,2))+')';
    OrderByFields:='MONO,MoLineNO';
    Lbl_Condition.Caption:=Frm_Sfc_MoOutQry_C.ConditionHint;
    GetData;
  end;

end;

procedure TFrm_Sfc_MoOutQry.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Sfc_MoOutQry_C.Release;
end;

procedure TFrm_Sfc_MoOutQry.AdoQry_MainAfterScroll(DataSet: TDataSet);
begin
  inherited;
  With AdoQry_Tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('Select MoLine.ItemCode+'' ''+Item.ItemName as ItemCodeName '+
            '  From MoLine,Item '+
            'Where MoLine.ItemCode=Item.ItemCode '+
            '  and MoLine.MoNo='''+AdoQry_Main.fieldbyname('MoNo').AsString+''''+
            '  and MoLine.MoLineNo='+AdoQry_Main.fieldbyname('MoLineNo').AsString);
    Open;
    Lbl_ItemCodeName.Caption:=fieldbyname('ItemCodeName').AsString;
  end;
end;

procedure TFrm_Sfc_MoOutQry.Act_PreviewExecute(Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

procedure TFrm_Sfc_MoOutQry.Act_PrintExecute(Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

procedure TFrm_Sfc_MoOutQry.Act_ExcelExecute(Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

end.