bas_qry_multibom1.pas

一个MRPII系统源代码版本

//王小鹏///普源用
unit Bas_Qry_MultiBom1;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Panel, ActnList, Db, AdODB, ExtCtrls, ComCtrls, ToolWin, StdCtrls,
  Grids, DBGrids, Extdbgrid, DBGridEh, ExtPrintReport,pr_PrintReportType,
  jpeg;

Type
  TFrm_Bas_Qry_MultiBom1 = Class(TFrm_Base_Panel)
    AdoQry_Main: TAdoQuery;
    DataSource1: TDataSource;
    ToolButton1: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    Label1: TLabel;
    Label3: TLabel;
    Lbl_ItemName: TLabel;
    Edt_ItemCode: TEdit;
    DBGridEh1: TDBGridEh;
    ExtPrintReport1: TExtPrintReport;
    TlBtn_Preview: TToolButton;
    ToolButton6: TToolButton;
    TlBtn_Print: TToolButton;
    Action4: TAction;
    Action5: TAction;
    ToolButton2: TToolButton;
    Tlbtn_Hint: TToolButton;
    TlBtn_Excel: TToolButton;
    ToolButton7: TToolButton;
    procedure FormCreate(Sender: TObject);
    procedure Edt_ItemCodeExit(Sender: TObject);
    procedure TlBtn_PreviewClick(Sender: TObject);
    procedure TlBtn_PrintClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure GetTransValue(FieldName,OldValue:String;var NewValue:String) ;
    procedure Action4Execute(Sender: TObject);
    procedure Act_HintExecute(Sender: TObject);
    procedure TlBtn_ExcelClick(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure AdoQry_MainAfterOpen(DataSet: TDataSet);
    procedure FormDestroy(Sender: TObject);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    { Private declarations }
    OldItemCode:string;                                        //保存当前输入物料编码;
    procedure SetPnl_Head(ItemCode:String);                    //显示输入物料其它属性 ;
    Function ItemCodeUsable(Code:String):Boolean;              //判断物料是否存在.可用；
    Function PickItemCode(InitCode:String):string;             // 得到正确的物料编码 ;
    procedure SetReport;
    function SlaveCodeUsable(R_ItemCode:String):Boolean;
    function PickSlaveCode(InitCode:String):string;
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;    
  public
    { Public declarations }
    procedure Initform(AdoConnection:TAdoConnection);
  end;

var
  Frm_Bas_Qry_MultiBom1: TFrm_Bas_Qry_MultiBom1;
  Temp_DBGridEh:TDBGridEh;

implementation

uses Sys_Global, Bas_Qry_MultiBom;

{$R *.DFM}

procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);
var
  i:integer;
begin
  NewValue:=OldValue;
  if Temp_DBGridEh<>nil then
    for i:=0 to Temp_DBGridEh.Columns.Count-1 do
      if(Temp_DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        NewValue:=Temp_DBGridEh.Columns[i].PickList.Strings[Temp_DBGridEh.Columns[i].
          KeyList.IndexOf(OldValue)];
        break;
      end;
end;

procedure TFrm_Bas_Qry_MultiBom1.Initform(AdoConnection: TAdoConnection);
begin
  AdoQry_Main.Connection:=AdoConnection;
  AdoQry_Tmp.Connection:=AdoConnection;
end;

function TFrm_Bas_Qry_MultiBom1.ItemCodeUsable(Code: String): Boolean;
begin
  with AdoQry_Tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select ItemName '+
                    ' From Item '+
                    ' Where ItemCode= '+''''+Code+''''+
                    ' and  ItemUsable=1 ' );
    Prepared;
    open;
    Result:=(AdoQry_Tmp.recordCount>0);
  end;
end;

procedure TFrm_Bas_Qry_MultiBom1.SetPnl_Head(ItemCode: String);
begin
  with AdoQry_Tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select Item.ItemName ,'+
                    ' Uom.UomName,'+
                    ' Item.ItemShortName,'+
                    ' ItemType '+
                    ' from Item left join Uom '+
                    ' on Item.UomCode=Uom.UomCode '+
                    ' where  ItemCode= '+''''+Trim(ItemCode)+'''');
    Prepared;
    open;
    Lbl_ItemName.Caption:=fieldbyname('ItemName').AsString;
  end;
end;
procedure TFrm_Bas_Qry_MultiBom1.FormCreate(Sender: TObject);
begin
  inherited;
  OldItemCode:='';
  TlBtn_Excel.Enabled :=False;
end;

function TFrm_Bas_Qry_MultiBom1.PickItemCode(InitCode: String): string;
begin
  Result:=GetCodeHint(AdoQry_Tmp,
                      'ItemName','物料描述',
                      'ItemCode','物料代码',
                      'Item','InitCode','ItemUsable=1');
end;

procedure TFrm_Bas_Qry_MultiBom1.Edt_ItemCodeExit(Sender: TObject);
begin
  inherited;
  if Trim(OldItemCode)=Trim(Edt_ItemCode.text) then
    abort;
  if not SlaveCodeUsable(Trim(Edt_ItemCode.Text)) then
  begin
    DispInfo('请输入代码没找到！',1);
    Edt_ItemCode.SetFocus;
    Abort;
  end;
  AdoQry_Main.Close;
  Pnl_Hint.Caption:='';
  if ItemCodeUsable(Trim(Edt_ItemCode.text)) Then
  begin
    Pnl_Hint.Caption:='提示:Bom正在展开!';
    Pnl_Hint.refresh;
    SetPnl_Head(Trim(Edt_ItemCode.text));
    Lbl_ItemName.Refresh;
    try
      Screen.Cursor:=CrSQLWait;
      with AdoQry_Main do
      begin
        Close;
        sql.clear;
        sql.Add(' exec expandBom1 '''+Trim(Edt_ItemCode.text)+'''');
        Prepared;
        open;
      end;
      OldItemCode:=Trim(Edt_ItemCode.text);
      TlBtn_Print.enabled:=(AdoQry_Main.recordCount>0);
      TlBtn_Preview.enabled:=(AdoQry_Main.recordCount>0);
    finally
      Screen.Cursor:=crDefault ;
    end;
    Pnl_Hint.Caption:='提示:Bom展开完毕!';
    Pnl_Hint.refresh;
    TlBtn_Excel.Enabled :=(AdoQry_Main.RecordCount>0);
    if AdoQry_Main.active=False then AdoQry_Main.active:= True ;
      AdoQry_Main.First ;
    if AdoQry_Main.recordCount>0 then
      Pnl_Hint.Caption:='提示:共有:'+''+inttostr(AdoQry_Main.recordCount)+''+'条记录'
    else
      Pnl_Hint.Caption:='提示:此物料无子项!';
  end
  else
  begin
    Edt_ItemCode.text:=PickItemCode(Trim(Edt_ItemCode.text));
    Edt_ItemCode.SetFocus;
  end;
end;

procedure TFrm_Bas_Qry_MultiBom1.TlBtn_PreviewClick(Sender: TObject);
begin
  inherited;
  TlBtn_Preview.Enabled:=False;
end;

procedure TFrm_Bas_Qry_MultiBom1.TlBtn_PrintClick(Sender: TObject);
begin
  inherited;
  TlBtn_Print.Enabled:=False;
end;

procedure TFrm_Bas_Qry_MultiBom1.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action:=Cafree;
end;

procedure TFrm_Bas_Qry_MultiBom1.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  if (AdoQry_Main.active=False ) or ( (AdoQry_Main.active=True) and (AdoQry_Main.recordCount=0)) then
  begin
    DispInfo('没有数据可打印!',3);
    abort;
  end;
  SetReport;
  ExtPrintReport1.print(self);
end;

procedure TFrm_Bas_Qry_MultiBom1.Act_PreviewExecute(Sender: TObject);
begin
  inherited;
  if (AdoQry_Main.active=False ) or ( (AdoQry_Main.active=True) and (AdoQry_Main.recordCount=0)) then
  begin
    DispInfo('没有数据可打印预览!',3);
    abort;
  end;
  SetReport;
  ExtPrintReport1.preview;
end;

procedure TFrm_Bas_Qry_MultiBom1.GetTransValue(FieldName, OldValue: String;
  var NewValue: String);
var
    i:integer;
begin
  NewValue:=OldValue;
  if Temp_DBGridEh<>nil then
    for i:=0 to Temp_DBGridEh.Columns.Count-1 do
      if(Temp_DBGridEh.Columns[i].FieldName=FieldName)then
      begin
        NewValue:=Temp_DBGridEh.Columns[i].PickList.Strings[Temp_DBGridEh.Columns[i].
        KeyList.IndexOf(OldValue)];
        break;
      end;
end;

procedure TFrm_Bas_Qry_MultiBom1.SetReport;
var
  i,j:integer;
begin
  inherited;
  ExtPrintReport1.DataSet :=nil;
  ExtPrintReport1.Headers.clear;
  i:=0;
  with ExtPrintReport1 do
  begin
    for j:=0 to DBGridEH1.Columns.Count-1 do
      if DBGridEH1.Columns[j].Visible then
      begin
        Headers.Add;
        Headers.Items[i].Caption :=DBGridEH1.Columns[j].Title.Caption;
        Headers.Items[i].FieldName :=DBGridEH1.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH1.Columns[j].Width div (DBGridEH1.Columns[j].Font.Size-2);
        Headers.Items[i].Alignment :=DBGridEH1.Columns[j].Alignment;
//        if(Temp_DBGridEh.Columns[j].PickList.Count>0)and
//          (Temp_DBGridEh.Columns[j].KeyList.Count>0)then
//        begin
//          Headers.Items[i].style:=dstransform;
//          Headers.Items[i].TransformFunction:=GetTransValue;
//        end
//        else
//          SetColumnsStyle(i,DBGridEH.Columns[j].FieldName);
        inc(i);
      end;
    DataSet:=AdoQry_Main;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select SysParamValueC '+
                         'From SysParam '+
                         'where SysParamCode=''Name0''';//Name0是使用本系统的客户的名称
    AdoQry_Tmp.Open;
    ExtPrintReport1.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
     ExtPrintReport1.Title2:='多层Bom展开报表';
    {AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Add('Select ReportName1,ISOCode1 '+
                         'From ReportCtrl '+
                         'where SysMenuId='''+MenuId+'''');
    AdoQry_Tmp.Open;
    ExtPrintReport1.Title2:=AdoQry_Tmp.fieldbyname('ReportName1').AsString;}
    //ExtPrintReport1.SubTitle1:=AdoQry_Tmp.fieldbyname('ISOCode1').AsString;
    //ExtPrintReport1.Title2:=Pnl_Title.Caption;
    {//ExtPrintReport1.Subtitle2:=Lbl_ConditionTitle.Caption+Lbl_VendorCode.Caption+'
    //'+Lbl_VendorName1.Caption+Lbl_VendorName.Caption;
    //ExtPrintRepor1t.Subtitle3:=Lbl_include1.Caption+Lbl_include.Caption;}
    ExtPrintReport1.subTitle1:='物料代码:'+Edt_ItemCode.text+'                 '+
                              '物料名称:'+Lbl_ItemName.Caption+'                 ';
  end;
end;
function TFrm_Bas_Qry_MultiBom1.PickSlaveCode(InitCode: String): string;
begin
  Result:=GetCodeHint(
        AdoQry_Tmp,
        'ItemName','项目描述',
        'ItemCode','项目代码',
        'Item',InitCode,'ItemUsable=1');
end;

function TFrm_Bas_Qry_MultiBom1.SlaveCodeUsable(
  R_ItemCode: String): Boolean;
var
  T_Count:integer;
  T_Sql:string;
begin
   T_Sql:=
    'Select Count(*) as RecordCount '+
    'from Item '+
    'where ItemCode='''+R_ItemCode+''' '+
    '  and ItemUsable=1';
    with AdoQry_Tmp do
    begin
      Close;
      SQL.clear;
      SQL.Add(T_Sql);
      open;
      T_Count:=fieldbyname('RecordCount').AsInteger;
      Close;
    end;
    if (T_Count>0 ) then
      Result:=True
    else
      Result:=False;
end;

procedure TFrm_Bas_Qry_MultiBom1.Action4Execute(Sender: TObject);
begin
  inherited;
  if ActiveControl.Name='Edt_ItemCode' then
  begin
    Edt_ItemCode.Text:=PickSlaveCode(Edt_ItemCode.Text);
    Edt_ItemCode.SetFocus;
  end;

end;

procedure TFrm_Bas_Qry_MultiBom1.Act_HintExecute(Sender: TObject);
begin
  inherited;
  if ActiveControl.Name='Edt_ItemCode' then
  begin
    Edt_ItemCode.Text:=PickSlaveCode(Trim(Edt_ItemCode.Text));
    Edt_ItemCode.SetFocus;
  end;
end;

procedure TFrm_Bas_Qry_MultiBom1.SetColumnsStyle(ItemIndex: Integer;
  FieldName: String);
begin

end;

procedure TFrm_Bas_Qry_MultiBom1.TlBtn_ExcelClick(Sender: TObject);
begin
  inherited;
 DBGridEhToExcel(Dbgrideh1);
end;

procedure TFrm_Bas_Qry_MultiBom1.Act_ExcelExecute(Sender: TObject);
begin
  inherited;
  DBGridEhToExcel(Dbgrideh1);
end;

procedure TFrm_Bas_Qry_MultiBom1.AdoQry_MainAfterOpen(DataSet: TDataSet);
begin
  inherited;
  TFloatField(DataSet.fieldbyname('BomQty')).DisplayFormat:='0.#####';
  TFloatField(DataSet.fieldbyname('BomScrAp_Percent')).DisplayFormat:='0.#####';
  if AdoQry_Main.recordCount>0 then
    Pnl_Hint.Caption:='提示:共有:'+''+inttostr(AdoQry_Main.recordCount)+''+'条记录'
  else
    Pnl_Hint.Caption:='提示:此物料无子项!';

end;

procedure TFrm_Bas_Qry_MultiBom1.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Bas_Qry_MultiBom:=nil;
end;

procedure TFrm_Bas_Qry_MultiBom1.DBGridEh1GetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if (AdoQry_Main.fieldbyname ('ItemUsable').asstring='0') then
   AFont.Color:=clRed;

end;

end.