fmreports.pas

中国移动集群网销售资料录入管理系统。 包含集群网用户录入

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.