bas_qry_multibomsviewmaster.pas

一个MRPII系统源代码版本

unit Bas_Qry_MultiBomSViewMaster;
//
Interface

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

Type
  TFrm_Bas_Qry_MultiBomSViewMaster = Class(TFrm_Base_Panel)
    DataSource_Main: TDataSource;
    Extedt_ItemCode: TExtEdit;
    Label1: TLabel;
    ExtPrintReport1: TExtPrintReport;
    ToolButton1: TToolButton;
    Lab_ItemName: TLabel;
    Label3: TLabel;
    AdoQry_tmp1: TAdoQuery;
    StringGrid1: TStringGrid;
    AdODataSet1: TAdODataSet;
    ToolButton2: TToolButton;
    ToolButton5: TToolButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure Act_ShowGridExecute(Sender: TObject);
    procedure StringGrid1DrawCell(Sender: TObject; ACol, Arow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure Extedt_ItemCodeExit(Sender: TObject);
    procedure Extedt_ItemCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    Old_ItemCode:string;
    insertend:boolean;  //数据是否填充完毕    
    procedure SetReport;
    procedure clearStringGrid;
    procedure StringGridToExcel(StringGrid:TStringGrid);
    procedure RunMultiBomSViewMaster(ItemCode:string);
  public
    { Public declarations }
    procedure ExpandBom(ItemCode:string);
    procedure Initform(AdoConnection:TAdoConnection);
  end;

var
  Frm_Bas_Qry_MultiBomSViewMaster: TFrm_Bas_Qry_MultiBomSViewMaster;

implementation
uses Bas_Qry_MultiBomSViewMasterDetail,Sys_Global;
{$R *.DFM}

{ TFrm_Bas_Qry_MultiBomSViewMaster }

procedure TFrm_Bas_Qry_MultiBomSViewMaster.Initform(
  AdoConnection: TAdoConnection);
begin
  AdODataSet1.Connection:=AdoConnection;
  AdoQry_Tmp.Connection:=AdoConnection;
  AdoQry_Tmp1.Connection:=AdoConnection;
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Bas_Qry_MultiBomSViewMaster:=nil;
end;

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

procedure TFrm_Bas_Qry_MultiBomSViewMaster.RunMultiBomSViewMaster(ItemCode:string);
var
  i,j,Tmp_MaxCol,Tmp_MaxRow:integer;
  HaveItemCode:boolean;
  S,TMpstr:String;
begin
  insertend:=False;  //数据是否填充完毕
  S:= 'set noCount on'+
      ' create table #table(ite_ItemCode varchAr(20),'+
//      '                    ite_ItemCodeNameP varchAr(80),'+
//      '                    ItemCodeName varchAr(80),'+
//      '                    UomCodeName varchAr(20),'+
//      '                    BomItemType integer,'+
//      '                    Bomqty decimal(20,8),'+
//      '                    BomScrAp_Percent decimal(20,8),'+
//      '                    BomStatus integer,'+
//      '                    PmCode varchAr(20),'+
//      '                    DeptCodeName varchAr(60),'+
      '                    ItemCode varchAr(20),'+
      '                    OrderCode varchAr(200))'+
      ' insert into #table'+
      ' exec newveiwMasterDetail'+QuoTedStr(ItemCode)+
      ' select   #table.* from #table'+
      ' join Item on #table.ite_ItemCode=Item.ItemCode'+
      ' and Item.ItemUsable=1'+
      ' drop table #table';

  AdoDataset1.commandtimeout:=0;
  AdODataSet1.Close;
  AdODataSet1.CommandText:=S;
  AdODataSet1.Open;


  i:=0;
  j:=0;
  Tmp_MaxCol:=0;
  HaveItemCode:=False;
  AdODataSet1.First;
  WHILE NOT AdODataSet1.EOF DO
  begin
    TMpstr:=','+AdODataSet1.fieldbyname('OrderCode').AsString+',';
    delete(TMpstr,1,1);
    if (Pos(UpperCase(ItemCode),UpperCase(TMpstr))<>0) then
    begin
      i:=i+1;
      StringGrid1.RowCount:=i+1;
      j:=1;
      HaveItemCode:=True;
      while HaveItemCode do
      begin
        StringGrid1.Cells[j,i]:=copy(TMpstr,1,Pos(',',TMpstr)-1);
        delete(TMpstr,1,Pos(',',TMpstr));
        if Pos(',',TMpstr)=0 then HaveItemCode:=False;
        StringGrid1.Cells[j,0]:='      第'+inttostr(j-1)+'层';
        StringGrid1.Cells[1,0]:=' 最终产品  第0层';
        j:=j+1;
      end;
      if j>Tmp_MaxCol then Tmp_MaxCol:=j;
      if i>Tmp_MaxRow then Tmp_MaxRow:=i;
    end;
    AdODataSet1.next;
  end;

  StringGrid1.colCount:=Tmp_MaxCol;
  StringGrid1.RowCount:=Tmp_MaxRow+1;
  Pnl_Hint.Refresh;
  if AdODataSet1.RecordCount>0 then
  begin
    StringGrid1.FixedRows:=1;
    StringGrid1.FixedCols:=1;
    StringGrid1.FixedColor:=clBtnFace;
  end;
  
  insertend:=True;  
end;


procedure TFrm_Bas_Qry_MultiBomSViewMaster.FormCreate(Sender: TObject);
var
 I:integer;
begin
  inherited;
  Old_ItemCode:='';
  StringGrid1.ColWidths[0]:=12;
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.Act_ShowGridExecute(
  Sender: TObject);
begin
  inherited;
  Frm_Bas_Qry_MultiBomSViewMasterDetail:=TFrm_Bas_Qry_MultiBomSViewMasterDetail.Create(self);
  Frm_Bas_Qry_MultiBomSViewMasterDetail.initform(AdODataSet1.Connection);
  Frm_Bas_Qry_MultiBomSViewMasterDetail.RunMuitiBomSViewMasterDetail(Extedt_ItemCode.text);
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.clearStringGrid;
var
  TmpColCount,TmpRowCount:integer;
  i,j:integer;
begin
  //清空STRINGGRID的内容
  TmpColCount:=StringGrid1.ColCount;
  TmpRowCount:=StringGrid1.RowCount;
  for i:=0 to TmpColCount do
    for j:=0 to TmpRowCount do
      StringGrid1.cells[i,j]:='';
  StringGrid1.ColCount:=0;
  StringGrid1.RowCount:=0;
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.StringGrid1DrawCell(
  Sender: TObject; ACol, Arow: Integer; Rect: TRect;
  State: TGridDrawState);
var
  I:integer;
begin
  inherited;
  if insertend=False then exit;

  //画背景及文字
  for i:=0 to round(StringGrid1.RowCount div 2)  do
  begin
    if (acol>0) and(Arow =2*i+1) then
    begin
//      StringGrid1.Canvas.Brush.Color := $f0f0f0;
      StringGrid1.Canvas.pen.Color :=StringGrid1.Font.Color ;
      StringGrid1.canvas.fillRect(rect);
      StringGrid1.Canvas.textout(rect.left+1,rect.Top ,StringGrid1.cells[acol,Arow]);
    end ;
  end;
  
  // 找到相同字串，画成红色
  if pos(Extedt_ItemCode.text,StringGrid1.Cells[ACol, Arow])<>0 then
  begin
    StringGrid1.Canvas.Font.Color := clred;
    StringGrid1.Canvas.Font.Style :=[fsBold];
//    StringGrid1.ColWidths[ACol]:=125;
  end;
  DrawText(StringGrid1.Canvas.Handle, PChAr(StringGrid1.Cells[ACol, Arow]), -1,
    Rect, DT_LEFT or DT_VCENTER);
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.SetReport;
begin

end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.Act_ExcelExecute(
  Sender: TObject);
begin
  StringGridToExcel(Stringgrid1);
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.StringGridToExcel(
  StringGrid: TStringGrid);
var
  Excel,Sheet:variant;
  KeyList:String;
  i,j,x,y,z:Integer;
begin
  Screen.Cursor:=CrAppStArt;
  try
    Excel:=UnAssigned;
    Excel:=CreateOleObject('Excel.Application');
    Excel.Visible:=False;
    Excel.WorkBooks.Add;
  except
    Excel:=UnAssigned;
    Screen.Cursor:=CrDefault;
    DispInfo('本机未安装EXCEL,本功能必须在安装有EXCEL的电脑上才能运行!',1);
    Abort;
  end;
  if varIsEmpty(Excel) then
  begin
    Screen.Cursor:=CrDefault;
    DispInfo('建立Excel对象不成功，请重试!',1);
    Excel:=UnAssigned;
    Abort;
  end;
  j:=0;
  for i:=0 to StringGrid1.ColCount-1 do
  begin
     j:=j+1;
  end; //取得显示列总列数
  i:=Round(j/2);
  Sheet:=Excel.WorkSheets[1];
  Sheet.Cells[1,i]:=Pnl_Title.Caption;
  Sheet.cells[2,1]:='日期:'+FormatDateTime('yyyy"年"mm"月"dd"日"',Now);
  Sheet.Cells[2,j-1]:='制表:'+UserCode;    //以上为表头输出
  J:=1;
  for I:=0 To StringGrid1.ColCount-1 Do
  begin
      Sheet.Cells[3,J]:=''''+StringGrid1.Cells[i+1,0];  //设置列标题
      j:=j+1;
  end;

  x:=0;
  repeat
    x:=x+1;
    y:=0;
    repeat
      y:=y+1;
      Sheet.Cells[y+3,x]:=''''+StringGrid1.Cells[x,y];
    until
      y=StringGrid1.RowCount;
  until
    x=StringGrid1.ColCount;

  Screen.Cursor:=CrDefault;
  Excel.Visible:=True;
  Excel:=UnAssigned;
end;


procedure TFrm_Bas_Qry_MultiBomSViewMaster.ExpandBom(ItemCode: string);
var
  S:string;
begin
  Extedt_ItemCode.Text:=ItemCode;
  Extedt_ItemCode.Color:=clBtnFace;
  Extedt_ItemCode.Enabled:=False;

  S:='select ItemName from Item where ItemCode='+quotedstr(ItemCode);
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(S);
  AdoQry_tmp.Open;
  Lab_ItemName.Caption:=AdoQry_tmp.fieldbyname('ItemName').asstring;
  RunMultiBomSViewMaster(ItemCode);
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.Extedt_ItemCodeExit(
  Sender: TObject);
var
  S:string;
begin
//  inherited;
  S:='select ItemCode from Item where ItemCode='+Quotedstr(Extedt_ItemCode.Text)+' and ItemUsable=1';
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(s);
  AdoQry_tmp.Open;
  if AdoQry_tmp.IsEmpty then
  begin
    Application.MessageBox('错误或无效的物料代码','信息提示',MB_OK+MB_IconInfoRMATION);
    Abort;
  end;
  Pnl_Hint.Caption:='';
  if ExtEdt_ItemCode.Text<>Old_ItemCode then
  begin
    clearStringGrid;
    Pnl_Hint.Caption:='正在展开,请稍候...';
    Pnl_Hint.Refresh;
    StringGrid1.Refresh;
    RunMultiBomSViewMaster(Extedt_ItemCode.text);
    Pnl_Hint.Caption:='物料'+Extedt_ItemCode.Text+'最终父项为: '+inttostr(StringGrid1.RowCount-1)+'条';
    Pnl_Hint.Refresh;
    Old_ItemCode:=ExtEdt_ItemCode.Text;
  end;
end;

procedure TFrm_Bas_Qry_MultiBomSViewMaster.Extedt_ItemCodeKeyDown(
  Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  inherited;
  If key=120 Then
  CommOnHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode','物料代码','Item','ItemUsable=1')
end;

end.