unit fmreports;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls, DB, Grids, DBGrids, ADODB,
  OleServer, Excel2000;

type
  TFormReports = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    GroupBox1: TGroupBox;
    DBGrid1: TDBGrid;
    GroupBox2: TGroupBox;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    CheckBox4: TCheckBox;
    CheckBox5: TCheckBox;
    CheckBox6: TCheckBox;
    Button1: TButton;
    Button2: TButton;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    Button4: TButton;
    procedure Button2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    procedure MakeRep1;
    procedure MakeRep2;
    procedure MakeRep3;
    procedure MakeRep4;

  public
    function get_sql: string;
  end;

var
  FormReports: TFormReports;

implementation
uses selectcompany, datamodule, main; //   ,mobile_form
{$R *.dfm}

procedure TFormReports.Button2Click(Sender: TObject);
begin
  close;
end;

procedure TFormReports.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TFormReports.Button1Click(Sender: TObject);
begin
  MainForm.OpenGroupView.Execute;
end;

procedure TFormReports.Button3Click(Sender: TObject);
var select_list: string;
begin
  if RadioButton1.Checked then begin
    //select_list:='SELECT phonenum,shortnum,[user],gender,cID,address,tel,sGloabal,sZone,s136,sPeople FROM centre where 0=0'+get_sql;
    MakeRep2;
  end;
  if RadioButton2.Checked then begin
    //select_list := 'SELECT phonenum,[user],service,shortnum, b1,b2, b3, bOther FROM centre where 0=0' + get_sql;
    MakeRep3;
  end;
  if RadioButton3.Checked then begin
    MakeRep4;
  end;
end;

function TFormReports.get_sql: string;
var select_mobile1, select_mobile2, select_mobile3, select_mobile4: string;
begin
  if CheckBox1.Checked then select_mobile1 := ' and groupname=' + '''' + trim(ComboBox1.text) + '''';
  if CheckBox2.Checked then begin
    select_mobile2 := ' and DT between ' + '#' + DateToStr(DateTimePicker1.DateTime) + '#';
    select_mobile3 := ' and ' + '#' + DateToStr(DateTimePicker2.DateTime) + '#';
  end;
  if CheckBox3.Checked then select_mobile4 := 'and saler=' + '''' + trim(ComboBox2.text) + '''';
  result := select_mobile1 + select_mobile2 + select_mobile3 + select_mobile4;
end;

procedure TFormReports.FormCreate(Sender: TObject);
begin
  ComboBox1.Items.text := trim(DM.getrsstring('select distinct * from qyAllGroupName'));
  ComboBox2.Items.text := trim(DM.getrsstring('select distinct Saler from centre'));
end;
procedure TFormReports.MakeRep1;
var select_list: string;
  i: integer;
begin
  select_list := 'SELECT 0 AS 序號, phonenum AS 手機號碼, shortnum AS 短號, [user] AS 姓名, ';
  select_list := select_list + ' iif(gender,''男'',''女'') AS 性別, ''身份證'' AS 證件類型, cID AS 證件號碼, address AS 聯系地址, tel AS 聯系電話, ';
  select_list := select_list + ' sGloabal as 全球通,sZone as 動感,s136 as 神州,sPeople as 大眾 FROM centre where 0=0';
  select_list := select_list + get_sql;
  if get_sql = '' then
    showmessage('缺少必要條件!')
  else begin
    ADOQuery1.close;
    ADOQuery1.SQL.text := select_list;
    ADOQuery1.Open;
    for i := 0 to DBGrid1.Columns.Count - 1 do
      DBGrid1.Columns.Items[i].Width := 100;
  end;

end;
procedure TFormReports.MakeRep2;
var select_list,r: string;
  i: integer;
  xlApp: TExcelApplication;
  xlWb: TExcelWorkbook;
  xlSht: TExcelWorksheet;
begin
  select_list := 'SELECT phonenum as 手機號碼, [user] as 機主姓名, service as 所屬品牌, shortnum as 短號碼';
  select_list := select_list + ' , b1 as 全球通10元包月, b2 as 預付費5元包月, b3 as 保留原集群網優惠, bOther as 其他套餐, '''' as 機主簽名, '''' as 備注 FROM centre where 0=0';
  select_list := select_list + get_sql;
  if get_sql = '' then begin
    showmessage('缺少必要條件!');
    abort;
  end
  else begin
    ADOQuery1.close;
    ADOQuery1.SQL.text := select_list;
    ADOQuery1.Open;
    for i := 0 to DBGrid1.Columns.Count - 1 do
      DBGrid1.Columns.Items[i].Width := 100;
  end;
  if CheckBox4.Checked then begin
    with TSaveDialog.Create(Self) do begin
      DefaultExt := '.xls';
      FileName := '預付費登記表.xls';
      if Execute then begin
        //以下從vb中移植過來
        try

          xlApp := TExcelApplication.Create(Self);
          xlApp.Visible[0] := CheckBox5.Checked;
          xlWb := TExcelWorkbook.Create(Self);
          xlSht := TExcelWorksheet.Create(Self);
          xlApp.Connect;
        except
          showmessage('Excel 沒有安裝!');
          abort;
        end;
        xlApp.Workbooks.Add(EmptyParam, 0);
        xlWb.ConnectTo(xlApp.Workbooks[1]);
        xlSht.ConnectTo(xlWb.Worksheets[1] as _worksheet);
        xlSht.Range['A1', 'Z1'].Insert(xlDown);
        for i := ord('A') to ord('I') do begin
          xlSht.Range[Chr(i) + '1', Chr(i) + '2'].HorizontalAlignment := xlCenter;
          xlSht.Range[Chr(i) + '1', Chr(i) + '2'].VerticalAlignment := xlCenter;
          xlSht.Range[Chr(i) + '1', Chr(i) + '2'].MergeCells := True;
        end;
        xlSht.Range['J1', 'M1'].HorizontalAlignment := xlCenter;
        xlSht.Range['J1', 'M1'].VerticalAlignment := xlCenter;
        xlSht.Range['J1', 'M1'].MergeCells := True;

        xlSht.Range['J1', 'J1'].Value := '品牌屬性';
        xlSht.Range['N1', 'N2'].HorizontalAlignment := xlCenter;
        xlSht.Range['N1', 'N2'].VerticalAlignment := xlCenter;
        xlSht.Range['N1', 'N2'].MergeCells := True;
        xlSht.Range['N1', 'N1'].Value := '客戶簽' + #13#10 + '名確認';
        xlSht.Range['K2', 'N2'].HorizontalAlignment := xlCenter;
        xlSht.Range['K2', 'N2'].VerticalAlignment := xlCenter;
          xlSht.Range[ 'A1','Z1'].Insert( xlDown);
          xlSht.Range['A1','N1'].HorizontalAlignment := xlCenter ;
          xlSht.Range['A1','N1'].VerticalAlignment := xlCenter;
          xlSht.Range['A1','N1'].MergeCells := True;
          xlSht.Range['A1','A1'].Value := '預付費用戶信息登記表';
          for i := 5 to 8888 do begin
          if trim(xlSht.Range['A'+IntToStr(i), 'A'+IntToStr(i)].Text) = '' then break ;
            xlSht.range['A'+IntToStr(i), 'A'+IntToStr(i)].Value := i - 4
            end;
            r := IntToStr(i);
            xlSht.Range['B'+ r, 'N' + r].HorizontalAlignment := xlLeft;
            xlSht.Range['B'+ r , 'N' + r].VerticalAlignment := xlCenter;
            xlSht.Range['B'+ r , 'N' + r].MergeCells := True;
            xlSht.Range['B2','B2'].Value := '填表須知：' + #13#10
            +'1）此表為預付費用戶申請短號集群網業務完成用戶注冊工作，申請單位應確保機主簽名為本人簽署并提供機主身份證復印件。否則，由此而造成的機主投訴由申請單位負責協調處理。' + #13#10
            +'2）選擇“品牌屬性”時，請在對應項目下打勾（必選其一）';
            xlSht.Range['H'+IntToStr(i + 2),'H'+IntToStr(i + 2)].Value := '申請單位蓋章：';
            xlSht.Range['H'+IntToStr(i + 3),'H'+IntToStr(i + 3)].Value: = '經辦人：';
            xlSht.Range['B'+IntToStr(i + 3),'B'+IntToStr(i + 3)].Value := '客戶經理：';
            xlSht.Range['H'+IntToStr(i + 4),'H'+IntToStr(i + 4)].Value := '日期：年 月 日';
         {

          '2.Design form
            xlSht.Cells.Font.Name = "Arial"
            xlSht.Cells.Font.Size = 9
            xlSht.Cells.Columns.AutoFit
            xlSht.Cells.RowHeight = 18
            xlSht.Rows(r & ": " & r).RowHeight = 50
            xlSht.Range['A1'].Font.Size = 16
            xlSht.Range['A1'].Font.Bold = True
            xlSht.Rows['3: 4 '].Font.Bold = True
            xlSht.Rows['2: 2 '].RowHeight = 9
            'xlSht.Columns['A:A'].ColumnWidth = 3
            xlSht.Range['A3: N" & r).Borders.LineStyle = xlContinuous
            xlSht.Rows['2: 2 '].Insert Shift := xlDown
            xlSht.Cells(3, 2) = "集團名稱：" & GrpName
            xlSht.Range['b3'].Font.Bold = True
            xlSht.Range['b3'].Font.Size = 11
            xlSht.Range['b3'].RowHeight = 15
            xlSht.Rows['2: 2 '].RowHeight = 5





          '3.Set page for print
            xlSht.PageSetup.LeftFooter = ""
            xlSht.PageSetup.CenterHeader = ""
            xlSht.PageSetup.RightHeader = ""
            xlSht.PageSetup.LeftFooter = ""
            xlSht.PageSetup.CenterFooter = ""
            xlSht.PageSetup.RightFooter = ""
            xlSht.PageSetup.Orientation = xlLandscape



          'Update state
            CurrentDb.Execute "update centre set state = 2 where " & s


          xlWb.Save
            xlWb.close
            xlApp.Quit
            set xlWb = Nothing
            set xlApp = Nothing



          'Call procedur to made second spresheet
            DoEvents
            MakeRep2(fnam)

          MsgBox "用戶信息登記表表已經建立，文件是" & fnam, 64


          Exit Sub
            err:
          xlWb.close
            xlApp.Quit
            set xlWb = Nothing
            set xlApp = Nothing
            MsgBox err.Description, 16, "錯誤: " & err.Number  }
        xlSht.SaveAs(FileName);
        xlApp.Disconnect;
        xlWb.close;
        xlSht.Free;
        xlApp.Quit;
        xlWb.Free;
        xlApp.Free;
      end;
    end;
  end;
end;
procedure TFormReports.MakeRep3;
var select_list: string;
  i: integer;
begin
  select_list := 'SELECT phonenum AS 客戶手機號碼, ''√'' AS 申請業務, '''' AS 取消業務, [user] AS 客戶簽名確認 FROM centre where 0=0';
  select_list := select_list + get_sql;
  if get_sql = '' then
    showmessage('缺少必要條件!')
  else begin
    ADOQuery1.close;
    ADOQuery1.SQL.text := select_list;
    ADOQuery1.Open;
    for i := 0 to DBGrid1.Columns.Count - 1 do
      DBGrid1.Columns.Items[i].Width := 100;
  end;

end;
procedure TFormReports.MakeRep4;
var select_list: string;
  i: integer;
begin
  select_list := 'SELECT saler as  業務員, count(*) AS 業績 From centre WHERE DT between #' + DateToStr(DateTimePicker1.DateTime) + '# and #' + DateToStr(DateTimePicker2.DateTime) + '# GROUP BY SALER';
  ADOQuery1.close;
  ADOQuery1.SQL.text := select_list;
  ADOQuery1.Open;
  for i := 0 to DBGrid1.Columns.Count - 1 do
    DBGrid1.Columns.Items[i].Width := 100;

end;
end.