unit ucgjhd;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, FR_DSet, FR_DBSet, FR_Class, RzButton, ADODB,
  FR_Desgn, FR_Cross, ComCtrls, RzDTP, ExtCtrls, GridsEh, DBGridEh, Grids,
  Excel2000, OleServer, ToolWin,
  ComObj,inifiles, RzTabs, ExcelXP;

type
  Tfrmcgjhd = class(TForm)
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    Panel1: TPanel;
    Panel2: TPanel;
    RzBitBtn1: TRzBitBtn;
    RzBitBtn2: TRzBitBtn;
    RzBitBtn3: TRzBitBtn;
    RzBitBtn4: TRzBitBtn;
    RzBitBtn5: TRzBitBtn;
    dtp1: TRzDateTimePicker;
    dtp2: TRzDateTimePicker;
    Label1: TLabel;
    combobox1: TComboBox;
    ADOQuery2: TADOQuery;
    frReport1: TfrReport;
    frDBDataSet1: TfrDBDataSet;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    Label2: TLabel;
    ADOQuery3: TADOQuery;
    ADOQuery4: TADOQuery;
    Panel3: TPanel;
    ProgressBar1: TProgressBar;
    DBGridEh1: TDBGridEh;
    procedure FormCreate(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure csh();
    procedure RzBitBtn3Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure RzBitBtn5Click(Sender: TObject);
    procedure RzBitBtn1Click(Sender: TObject);
    procedure dtp1Exit(Sender: TObject);
    procedure dtp2Exit(Sender: TObject);
    procedure RzBitBtn2Click(Sender: TObject);
    procedure RzBitBtn4Click(Sender: TObject);
    procedure DBGridEh1TitleClick(Column: TColumnEh);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmcgjhd: Tfrmcgjhd;

implementation
uses uconst,ufunction,udm1,ucx,unit3;
{$R *.dfm}
procedure tfrmcgjhd.csh();
var
sql:string;
begin
sql:='select distinct hthm from ddk where ldrq>=:rq1 and ldrq<=:rq2 order by hthm asc';
strsql:='select * from view_wlxq where 貨期>=:rq1 and 貨期<=:rq2 order by 工程單號 asc';
if adoquery1.Active then adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add(strsql);
adoquery2.SQL.Add(sql);
adoquery1.Parameters.ParamByName('rq1').Value:=dtp1.date;
adoquery1.Parameters.ParamByName('rq2').Value:=dtp2.date;
adoquery2.Parameters.ParamByName('rq1').Value:=dtp1.date;
adoquery2.Parameters.ParamByName('rq2').Value:=dtp2.date;
adoquery1.Open;
adoquery2.Open;
if not (adoquery2.Recordset.EOF and adoquery2.Recordset.BOF) then begin
      adoquery2.First;
      combobox1.Items.Clear;
      while not adoquery2.Eof do begin
            combobox1.Items.Add(adoquery2.fieldbyname('hthm').AsString);
            adoquery2.Next;
      end;
end;
adoquery2.Close;
end;
procedure Tfrmcgjhd.FormCreate(Sender: TObject);
begin
adoquery1.Connection:=dm1.ADOConnection1;
adoquery2.Connection:=dm1.ADOConnection1;
dtp1.date:=date;
dtp2.date:=date+30;
csh;
end;

procedure Tfrmcgjhd.ComboBox1Change(Sender: TObject);
begin
strsql:='select * from view_wlxq where 合同號碼='+''''+trim(combobox1.Text)+''''+
' order by 物料名稱 asc';
adoexect(adoquery1,strsql);
end;

procedure Tfrmcgjhd.RzBitBtn3Click(Sender: TObject);
begin
form3.frDBDataSet1.dataset:=adoquery1;
form3.frReport1.DoublePass := True;
form3.frReport1.Clear;
form3.frReport1.LoadFromFile(strpath+'/report/cgjh.frf');
frVariables['username']:=username;
form3.frReport1.Preview := Form3.frPreview1;
if form3.frReport1.PrepareReport then
  begin
    form3.frReport1.ShowPreparedReport;
  end;
form3.ShowModal;
end;

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

procedure Tfrmcgjhd.RzBitBtn5Click(Sender: TObject);
begin
close;
end;

procedure Tfrmcgjhd.RzBitBtn1Click(Sender: TObject);
begin
frmcx:=tfrmcx.Create(self);
frmcx.adocx:=adoquery1;
frmcx.ShowModal;
adoquery1:=frmcx.adocx;
ADOquery1.Requery();
end;

procedure Tfrmcgjhd.dtp1Exit(Sender: TObject);
begin
csh;
end;

procedure Tfrmcgjhd.dtp2Exit(Sender: TObject);
begin
csh;
end;

procedure Tfrmcgjhd.RzBitBtn2Click(Sender: TObject);
begin
form3.frReport1.DoublePass := True;
form3.frDBDataSet1.dataset:=adoquery1;
form3.frReport1.Clear;
form3.frReport1.LoadFromFile(strpath+'/report/cgjh.frf');
frVariables['username']:=username;
form3.frReport1.DesignReport;
end;

procedure Tfrmcgjhd.RzBitBtn4Click(Sender: TObject);
var
v,sheet,xl:Variant;
i,j,k,n:integer;
xqll:longint;
nowdept:TADOQuery;
tsql,consql: string;
begin
    if adoquery1.Recordset.EOF and adoquery1.Recordset.BOF then
       begin
       showmessage('當前沒有任何記錄可以導出！');
       exit;
       end else begin
       adoquery1.First;
       end;
    panel3.Visible:=true;
    frmcgjhd.Cursor:=crhourglass;
    nowdept:=TadoQuery.Create(nil) ;
    nowdept.Connection:=dm1.ADOConnection1;
    nowdept.Close;
try
   begin
    v:= CreateOleObject('Excel.Application');
    v.Workbooks.Add;//新建EXCEL文件
    v.Workbooks[1].WorkSheets[1].Name:='采購明細表';
    v.worksheets[1].Range['B1:o1'].Font.Name := '宋體';
    v.worksheets[1].Range['B1:o1'].Font.Size := 12;
    v.worksheets[1].Rows[1].Font.FontStyle := '加粗';
    v.WorkSheets[1].Cells[1, 2].Value := '采購明細表';
    v.worksheets[1].Rows[1].RowHeight := 20;
    v.worksheets[1].Rows[2].RowHeight := 30;
    v.worksheets[1].Rows[3].RowHeight := 15;
    v.worksheets[1].Rows[4].RowHeight := 15;
    v.worksheets[1].Columns[1].ColumnWidth := 2;
    v.worksheets[1].Columns[2].ColumnWidth := 20;
    v.worksheets[1].Rows[1].VerticalAlignment:= $FFFFEFF4;       //標題
    v.worksheets[1].Rows[1].horizontalAlignment:= $FFFFEFF4;
        Sheet:= v.Workbooks[1].WorkSheets[1];
        v.worksheets[1].range['B1:O1'].Merge(True);
        v.worksheets[1].range['B1:O1'].FONT.SIZE:='24';
        Sheet.Cells[2,2] :='物   料';
        Sheet.Cells[3,2] :='款號\數量';
        v.worksheets[1].range['B3:B4'].Merge(xl);
     strsql:='select cpdmk.cpdm,dds from ddk,cpdmk where cpdmk.cpdm=ddk.cpdm and hthm='+''''+trim(combobox1.Text)+
      ''''+' order by cpdmk.chang,cpdmk.cpdm asc';
     adoexect(adoquery2,strsql);
     k:=5;
     while not adoquery2.Eof do
         begin
         sheet.cells[k,2].value:= trim(adoquery2.FieldByName('cpdm').Value)+'-'+inttostr(adoquery2.FieldByName('dds').Value);
         adoquery2.Next;
         k:=k+1;
         ProgressBar1.Position:=TRUNC((k-5)/adoquery2.RecordCount*20);
         end;
         sheet.cells[k+2,2].value:='合計';
     strsql:='select 物料名稱,大類,顏色,色號,供應商,助記碼,物料代碼 from view_wlxq_3'+
     '  where 合同號碼='+''''+trim(combobox1.Text)+ ''''+' group by 大類,物料名稱,供應商,助記碼'+
     ',顏色,色號,物料代碼 order by 大類,供應商,物料名稱,助記碼 asc';
     adoexect(adoquery2,strsql);
     adoquery2.First;
     k:=3;
     i:=3;
     while not adoquery2.Eof do
         begin
         sheet.cells[2,k].value:=trim(adoquery2.fieldbyname('物料名稱').AsString);
         sheet.cells[3,k].value:=trim(adoquery2.fieldbyname('顏色').AsString);
         sheet.cells[4,k].value:=trim(adoquery2.fieldbyname('色號').AsString);
         strsql:='select cpdmk.cpdm from cpdmk,ddk where cpdmk.cpdm=ddk.cpdm and hthm='+''''+trim(combobox1.Text)+''''+
         ' order by chang,cpdmk.cpdm asc';
         j:=5; //用來記錄行坐標
             //用來激烈眼列坐標
         adoexect(adoquery3,strsql);
         adoquery3.First;
         xql:=0;
         while not adoquery3.Eof do
               begin
               strsql:='select 用量 from view_wlxq_3 where 物料代碼='+''''+trim(adoquery2.fieldbyname('物料代碼').AsString)+''''+
               ' and 產品代碼='+''''+trim(adoquery3.fieldbyname('cpdm').AsString)+'''';
               adoexect(adoquery4,strsql);
               if not(adoquery4.Recordset.BOF and adoquery4.Recordset.EOF) then
                  begin
                  sheet.cells[j,i].value:=trim(adoquery4.fieldbyname('用量').AsString);
                  xql:=xql+adoquery4.fieldbyname('用量').Value;
                  end;
               j:=j+1;
               adoquery3.Next;
               end;
         sheet.cells[j+2,i].value:=inttostr(xql);
         ProgressBar1.Position:=20+TRUNC((adoquery2.RecNo)/adoquery2.RecordCount*80);
         adoquery2.Next;
         i:=i+1;
         k:=k+1;
         end;
     adoquery2.Close;
     adoquery3.Close;
     adoquery4.Close;
     v.worksheets[1].Range[ 'B1:IV20' ].Borders[1].Weight := 2;
     v.worksheets[1].Range[ 'B1:IV20' ].Borders[2].Weight := 2;
     v.worksheets[1].Range[ 'B1:IV20' ].Borders[3].Weight := 2;
     v.worksheets[1].Range[ 'B1:IV20' ].Borders[4].Weight := 2;
     v.worksheets[1].Range['B1:IV20'].Font.Name := '宋體';
    v.worksheets[1].Range['B1:IV20'].Font.Size := 12;
    v.worksheets[1].Range['B1:IV20'].RowHeight:= 20;
    v.worksheets[1].Range['B1:IV20'].Font.FontStyle := '加粗';
    v.worksheets[1].Range['B1:IV20'].VerticalAlignment:= $FFFFEFF4;
    v.worksheets[1].Range['B1:IV20'].horizontalAlignment:= $FFFFEFF4;
    panel3.Visible:=false;
    frmcgjhd.Cursor:=crdefault;
     v.Visible := true;
    end;
  except
    SHOWMESSAGE('剛才產生的Excel未保存，可能沒裝Excel');
    v.DisplayAlerts := false;//是否提示存盤
    v.Quit;//如果出錯則退出
    exit;
 end;
end;

procedure Tfrmcgjhd.DBGridEh1TitleClick(Column: TColumnEh);
begin
Compositor(adoquery1,Column);
end;

end.