sfc_mnoutqry.pas

一个MRPII系统源代码版本

unit Sfc_MnOutQry;

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, jpeg;

Type
  TFrm_Sfc_MnOutQry = Class(TFrm_Base_Qry)
    Label3: TLabel;
    Lbl_ItemFlag: TLabel;
    AdoQry_MainIte_ItemCode: TStringField;
    AdoQry_MainIte_ItemName: TStringField;
    AdoQry_MainMoNo: TStringField;
    AdoQry_MainMoLineNo: TIntegerField;
    AdoQry_MainMoQty: TFloatField;
    AdoQry_MainMoNoFinishQty: TFloatField;
    AdoQry_MainDeptCode: TStringField;
    AdoQry_MainDeptName: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainInvBillQty: TFloatField;
    AdoQry_MainMoCtrlQty: TFloatField;
    AdoQry_MainDiffQty: TFloatField;
    AdoQry_MainDiffPer: TStringField;
    AdoQry_MainIte_UomName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainMoLineDate: TDateField;
    procedure FormDestroy(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
  private
    FStArtDate,FendDate,FDeptCode,FStArtMoNo,
    FendMoNo,FLineStatus,FStArtItemCode,FendItemCode:String;
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;
  Type TAdoQueryExpress=Class(TAdoQuery)
  Public
    Property CommandTimeOut;
  end;
var
  Frm_Sfc_MnOutQry: TFrm_Sfc_MnOutQry;

implementation
Uses Sfc_MnOutQry_C, Sys_Global;
{$R *.DFM}

{ TFrm_Sfc_MoOutQry }

procedure TFrm_Sfc_MnOutQry.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  TAdoQueryExpress(AdoQry_Main).CommandTimeOut:=0;
  TAdoQueryExpress(AdoQry_Tmp).CommandTimeOut:=0;
  Application.CreateForm(TFrm_Sfc_MnOutQry_C,Frm_Sfc_MnOutQry_C);
  Frm_Sfc_MnOutQry_C.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
  Frm_Sfc_MnOutQry_C.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  Frm_Sfc_MnOutQry_C.SetDBConnect(DBConnect);
  if Frm_Sfc_MnOutQry_C.Showmodal=mrOk then
  begin
    CreatePanel(1,DbGrideh);
    Lbl_Condition.Caption:=Frm_Sfc_MnOutQry_C.ConditionHint;
    FStArtDate:=Frm_Sfc_MnOutQry_C.FStArtDate;
    FendDate:=Frm_Sfc_MnOutQry_C.FendDate;
    FDeptCode:=Frm_Sfc_MnOutQry_C.FDeptCode;
    FStArtMoNo:=Frm_Sfc_MnOutQry_C.FStArtMoNo;
    FendMoNo:=Frm_Sfc_MnOutQry_C.FendMoNo;
    FstArtItemCode:=Frm_Sfc_MnOutQry_C.FStArtItemCode;
    FendItemCode:=Frm_Sfc_MnOutQry_C.FendItemCode;
    FLineStatus:=Frm_Sfc_MnOutQry_C.FLineStatus;
    If Trim(FDeptCode)<>'' Then
      SelectFromSQL:=' Select MoLine.ItemCode As Ite_ItemCode,Item.ItemName As Ite_ItemName,MoLine.MoLineDate,'
                    +' Uom.UomName As Ite_UomName, MoLine.MoNo,MoLine.MoLineNo,MoLine.MoQty, '
                    +' MoLine.MoNoFinishQty,MnItemList.DeptCode,Dept.DeptName, '
                    +' MnItemList.ItemCode,I1.ItemName,U1.UomName, '
                    +' IsNull(Sum(MnItemList.MoRealQty),0) As InvBillQty, ' //实际领料
                    +' IsNull(Sum(MnItemList.moCtrlQty),0) As MoCtrlQty, '//限额领料
                    +' (IsNull(Sum(MnItemList.moCtrlQty),0)-IsNull(Sum(MnItemList.MoRealQty),0)) As DiffQty, ' //差异量
                    +' Case When IsNull(Sum(MnItemList.MoCtrlQty),0)=0 Then ''--'' Else '
                    +' Convert(varchAr,Convert(Decimal(18,2),(IsNull(Sum(MnItemList.moCtrlQty),0)-IsNull(Sum(MnItemList.MoRealQty),0))/'
                    +' IsNull(Sum(MnItemList.MoCtrlQty),0)*100))+''%'' end As  DiffPer'
                    +' Into #Tmpbbb '
                    +' From Mo '
                    +' Join MoLine On Mo.MoNo=MoLine.MoNo '
                    +' Left Join MnItemList On MnItemList.MoNo=MoLine.MoNo And MnItemList.MoLineNo=MoLine.MoLineNO '
                    +' Left Join Item On Item.ItemCode=MoLine.ItemCode '
                    +' Left Join Item I1 On I1.ItemCode=MnItemList.ItemCode '
                    +' Left Join Dept On MnItemList.DeptCode=Dept.DeptCode '
                    +' Left Join Uom On Item.UomCode=Uom.UomCode '
                    +' Left Join Uom U1 On I1.UomCode=U1.UomCode '
                    +' Where MnItemList.DeptCode='''+FDeptCode+''''
                    +' and MoLine.MoLineDate>='''+FStArtDate+''''
                    +' and MoLine.MoLineDate<='''+FendDate+''''
                    +' and MO.MoNo>='''+FStArtMoNo+''' And Mo.MoNo<='''+FendMoNo+''''
                    +' and MoLine.ItemCode>='''+FStArtItemCode+''' And MoLine.ItemCode<='''+FendItemCode+''''
                    +' and MoLine.MoLineStatus='''+FLineStatus+''''
                    +' Group by MoLine.ItemCode ,MnItemList.DeptCode,MnItemList.ItemCode, '
                    +' Item.ItemName,I1.ItemName,Dept.DeptName,MoLine.MoNo, '
                    +' MoLine.MoLineNo,MoLine.MoQty,MoLine.MoNoFinishQty,Uom.UomName,U1.UomName,MoLine.MoLineDate '
    Else
      SelectFromSQL:=' Select MoLine.ItemCode As Ite_ItemCode,Item.ItemName As Ite_ItemName,MoLine.MoLineDate,'
                    +' Uom.UomName As Ite_UomName, MoLine.MoNo,MoLine.MoLineNo,MoLine.MoQty, '
                    +' MoLine.MoNoFinishQty,MnItemList.DeptCode,Dept.DeptName, '
                    +' MnItemList.ItemCode,I1.ItemName,U1.UomName, '
                    +' IsNull(Sum(MnItemList.MoRealQty),0) As InvBillQty, ' //实际领料
                    +' IsNull(Sum(MnItemList.moCtrlQty),0) As MoCtrlQty, '//限额领料
                    +' (IsNull(Sum(MnItemList.moCtrlQty),0)-IsNull(Sum(MnItemList.MoRealQty),0)) As DiffQty, ' //差异量
                    +' Case When IsNull(Sum(MnItemList.MoCtrlQty),0)=0 Then ''--'' Else '
                    +' Convert(varchAr,Convert(Decimal(18,2),(IsNull(Sum(MnItemList.moCtrlQty),0)-IsNull(Sum(MnItemList.MoRealQty),0))/'
                    +' IsNull(Sum(MnItemList.MoCtrlQty),0)*100))+''%'' end As  DiffPer'
                    +' Into #Tmpbbb '
                    +' From Mo '
                    +' Join MoLine On Mo.MoNo=MoLine.MoNo '
                    +' Left Join MnItemList On MnItemList.MoNo=MoLine.MoNo And MnItemList.MoLineNo=MoLine.MoLineNO '
                    +' Left Join Item On Item.ItemCode=MoLine.ItemCode '
                    +' Left Join Item I1 On I1.ItemCode=MnItemList.ItemCode '
                    +' Left Join Dept On MnItemList.DeptCode=Dept.DeptCode '
                    +' Left Join Uom On Item.UomCode=Uom.UomCode '
                    +' Left Join Uom U1 On I1.UomCode=U1.UomCode '
                    +' Where MoLine.MoLineDate>='''+FStArtDate+''''
                    +' and MoLine.MoLineDate<='''+FendDate+''''
                    +' and MO.MoNo>='''+FStArtMoNo+''' And Mo.MoNo<='''+FendMoNo+''''
                    +' and MoLine.ItemCode>='''+FStArtItemCode+''' And MoLine.ItemCode<='''+FendItemCode+''''
                    +' and MoLine.MoLineStatus='''+FLineStatus+''''
                    +' Group by MoLine.ItemCode ,MnItemList.DeptCode,MnItemList.ItemCode, '
                    +' Item.ItemName,I1.ItemName,Dept.DeptName,MoLine.MoNo, '
                    +' MoLine.MoLineNo,MoLine.MoQty,MoLine.MoNoFinishQty,Uom.UomName,U1.UomName,MoLine.MoLineDate ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SelectFromSQL;
    AdoQry_Tmp.ExecSQL;
    SelectFromSQL:='Select * From #Tmpbbb ';
    OrderByFields:=' DeptCode,MoNo,MoLineNo';
    GetData;
    if ShowAnimate<>nil then ShowAnimate.Free;
    if ShowPanel<>nil then ShowPanel.Free;
  end;
end;

procedure TFrm_Sfc_MnOutQry.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Sfc_MnOutQry_C.Release;
  Frm_Sfc_MnOutQry:=Nil;
end;

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

end;

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

end;

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

end;

procedure TFrm_Sfc_MnOutQry.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=' Drop Table #Tmpbbb';
  Try
    AdoQry_Tmp.ExecSQL;
  Except
  end;
end;

procedure TFrm_Sfc_MnOutQry.DataSourceDataChange(Sender: TObject;
  Field: TField);
begin
  inherited;
  Lbl_ItemFlag.Caption:=Trim(AdoQry_Main.fieldbyname('ItemCode').AsString)+' '
                       +Trim(AdoQry_Main.fieldbyname('ItemName').AsString);
end;

end.