staticsfrm.~pas

这个是个简单的关于出票申请的处理

unit StaticsFrm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ExtCtrls, StdCtrls, ExcelXP, OleServer, ComObj,
  TeeProcs, TeEngine, Chart, Series, systemPH;

type
  TReportType = (rtNone, rtApply, rtLQTicket, rtStoreTicket);
  TFormStatics = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    tvStatics: TTreeView;
    pnlTitle: TPanel;
    Panel3: TPanel;
    Label2: TLabel;
    Panel4: TPanel;
    Label1: TLabel;
    dtpStartDate: TDateTimePicker;
    Label3: TLabel;
    dtpEndDate: TDateTimePicker;
    btnStatics: TButton;
    Panel5: TPanel;
    procedure FormCreate(Sender: TObject);
    procedure btnStaticsClick(Sender: TObject);
    procedure tvStaticsMouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
  private
    { Private declarations }
    FCurReportType: TReportType;
    function  GetAgentIdList(AStartDate, AEndDate: TDate): TStringList;
    function  GetApplyRecordCount(AStartDate, AEndDate: TDate): Integer;
    function  GetStoreTicketRecordCount(AStartDate, AEndDate: TDate): Integer;
    procedure ExportLQTicketReportToExcel(AStartDay, AEndDay: TDate);
    procedure ExportApplyTicketReportToExcel(AStartDay, AEndDay: TDate);
    procedure ExportStoreTicketReportToExcel(AStartDay, AendDay: TDate);
    procedure WMSETCURSTATICS(var message: TMessage); message WM_STATICS_MSG;
    procedure SetCurStatics(Node: TTreeNode);
  public
    { Public declarations }
  end;

var
  FormStatics: TFormStatics;

implementation
uses
  SystemDM, CommonFuncLib;
{$R *.dfm}
function TFormStatics.GetAgentIdList(AStartDate, AEndDate: TDate): TStringList;
var
  sSqlstr: string;
  nAgentId: Integer;
begin
  Result := TStringList.Create;
  sSqlstr := 'SELECT DISTINCT AGENTID FROM TICKET_NUMBER_HISTORY WHERE ' +
              'OPERATEDATE >=' + #39 + DateToStr(AStartDate) + #39 +
              ' AND OPERATEDATE <=' + #39 + DateToStr(AEndDate) + #39;
  if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlstr) then
  with DMSystem.Qry_Temp do
  try
    First;
    while not Eof do
    begin
      nAgentId := FieldByName('AGENTID').AsInteger;
      if nAgentId <> COMPANYAGENTID then
        Result.Add(IntToStr(nAgentId));
      Next;
      Application.ProcessMessages;
    end;
  finally
  end;
end;

procedure TFormStatics.FormCreate(Sender: TObject);
begin
  FCurReportType := rtNone;
  dtpStartDate.DateTime := Now;
  dtpEndDate.DateTime := Now;
end;

procedure TFormStatics.btnStaticsClick(Sender: TObject);
begin
  if not DMSystem.ConnMain.Connected then
  begin
    SendMessage(Application.MainForm.Handle, DM_CONNECTDB, 0, 0);
    Exit;
  end;

  if dtpStartDate.Date > dtpEndDate.Date then
  begin
    ShowMessage('开始时间不能大于结束时间');
    Exit;
  end;
  case FCurReportType of
    rtApply: ExportApplyTicketReportToExcel(dtpStartDate.Date, dtpEndDate.Date);
    rtLQTicket: ExportLQTicketReportToExcel(dtpStartDate.Date, dtpEndDate.Date);
    rtStoreTicket: ExportStoreTicketReportToExcel(dtpStartDate.Date, dtpEndDate.Date);
  end;
end;

function TFormStatics.GetApplyRecordCount(AStartDate,
  AEndDate: TDate): Integer;
var
  sSqlStr: string;
begin
  Result := 0;
  sSqlStr := 'SELECT COUNT(AGENTID) AS APPLYRECORDCOUNT FROM TICKET_APPLY WHERE' +
             ' APPLYDATE >=' + #39 + DateToStr(AStartDate) + #39 + ' AND ' +
             'APPLYDATE <=' + #39 + DateToStr(AEndDate) + #39;
  if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
    Result := DMSystem.Qry_Temp.FieldByName('APPLYRECORDCOUNT').AsInteger;
end;

function TFormStatics.GetStoreTicketRecordCount(AStartDate,
  AEndDate: TDate): Integer;
var
  sSqlStr: string;
begin
  Result := 0;
  sSqlStr := 'SELECT COUNT(*) AS STORERECORDCOUNT FROM TICKET_NUMBER_HISTORY WHERE AGENTID =' + IntToStr(COMPANYAGENTID) +
             'AND OPERATEDATE >=' + #39 + DateToStr(AStartDate) + #39 + ' AND ' +
             'OPERATEDATE <=' + #39 + DateToStr(AEndDate) + #39;
  if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
    Result := DMSystem.Qry_Temp.FieldByName('STORERECORDCOUNT').AsInteger;

end;

procedure TFormStatics.tvStaticsMouseDown(Sender: TObject;
  Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
var
  Node: TTreeNode;
begin
  Node := tvStatics.GetNodeAt(X, Y);
  if Node = nil then Exit;
  SetCurStatics(Node);
end;

procedure TFormStatics.ExportApplyTicketReportToExcel(AStartDay,
  AEndDay: TDate);
var
  xlApp, xlBook, xlSheet, xlQuery: Variant;
  sSqlStr, sUserName, sDateSqlStr: String;
  i, iNextRow, AgentTotalCount ,AllAgentTotalCount, nRecordCount, nAgentId: Integer;
  fPercentage: Double;
begin
  nRecordCount := GetApplyRecordCount(AStartDay, AEndDay);
  if nRecordCount = 0 then
  begin
    ShowMessage('无记录，不做统计！');
    Exit;
  end;

  try
    sUserName := '';

    sDateSqlStr := ' APPLYDATE >=' + #39 + DateToStr(AStartDay) + #39 + ' AND ' +
                   'APPLYDATE <=' + #39 + DateToStr(AEndDay) + #39;
    AllAgentTotalCount := 0;
    AgentTotalCount := 0;

    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_START_UPDATE, nRecordCount + 8);
    Screen.Cursor := crHourGlass;
    try
      //建立OLE对象
      xlApp := CreateOleObject('Excel.Application');
      xlBook := xlApp.Workbooks.Add;
      xlSheet := xlBook.Worksheets['sheet1'];
      xlApp.Visible := false;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 1);

      //定义Excel格式
      xlSheet.Cells.Font.Name := '宋体';
      xlSheet.Cells.Font.Size := 12;
      xlSheet.Cells.VerticalAlignment := 2;
      //xlSheet.Cells.RowHeight := 17.25;
      xlSheet.Range['C:D'].HorizontalAlignment := xlCenter;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 2);


      //输出标题内容
      xlSheet.Range['A1'].Value := '票号申请统计表';
      xlSheet.Range['A1:G1'].HorizontalAlignment := 7;
      xlSheet.Range['1:1'].Font.Size := 13;
      xlSheet.Range['1:1'].Font.Bold := true;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 3);

      if AStartDay = AEndDay then
      begin
        xlSheet.Range['F2'].Value := '日期: ' + DatetoStr(AStartDay);
        xlSheet.Range['F2:G2'].HorizontalAlignment := 7;
      end
      else
      begin
        xlSheet.Range['E2'].Value := '日期:从' + DateToStr(AStartDay) + '到' + DateToStr(AEndDay);
        xlSheet.Range['E2:G2'].HorizontalAlignment := 7;
      end;
      xlSheet.Range['A3'].Value := '代理人姓名' ;
      xlSheet.Range['B3'].Value := '代理人帐号' ;
      xlSheet.Range['C3'].Value := '申请总票数' ;
      xlSheet.Range['D3'].Value := '申请日期' ;
      xlSheet.Range['E3'].Value := '识别码' ;
      xlSheet.Range['F3'].Value := '处理结果' ;
      xlSheet.Range['G3'].Value := '处理日期' ;
      iNextRow := 4;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 4);

      //查询结果, 导到EXCEL
      sSqlStr := 'SELECT AGENTID, TICKETCOUNT, APPLYDATE, IDENTIFYCODE, RESULTS, DOAPPLYDATE FROM TICKET_APPLY ' +
                 'WHERE' + sDateSqlStr;
      if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
      with DMSystem.Qry_Temp do
      try
        First;
        while not Eof do
        begin
          nAgentId := FieldByName('AGENTID').AsInteger;
          sSqlStr := 'SELECT USERNAME FROM SITE_USER WHERE ID = ' + IntToStr(nAgentId);
          if DMSystem.SQL_Querys(DMSystem.Qry_Info, sSqlStr) then
            sUserName := DMSystem.Qry_Info.FieldByName('USERNAME').AsString;

          xlSheet.Range[Format('A%d', [iNextRow])].Value := sUserName;
          xlSheet.Range[Format('B%d', [iNextRow])].Value := IntToStr(nAgentId);
          xlSheet.Range[Format('C%d', [iNextRow])].Value := FieldByName('TICKETCOUNT').AsString;
          xlSheet.Range[Format('D%d', [iNextRow])].Value := FieldByName('APPLYDATE').AsString;
          xlSheet.Range[Format('E%d', [iNextRow])].Value := FieldByName('IDENTIFYCODE').AsString;
          xlSheet.Range[Format('F%d', [iNextRow])].Value := FieldByName('RESULTS').AsString;
          xlSheet.Range[Format('G%d', [iNextRow])].Value := FieldByName('DOAPPLYDATE').AsString;

          Inc(iNextRow);
          SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 5 + I);
          Next;
          Application.ProcessMessages;
        end;
      finally
      end;

      sSqlStr := 'SELECT SUM(TICKETCOUNT) AS ALLTOTALCOUNT FROM TICKET_APPLY WHERE ' + sDateSqlStr;
      if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
       AllAgentTotalCount := DMSystem.Qry_Temp.FieldByName('ALLTOTALCOUNT').AsInteger;

      //合计
      xlSheet.Range[Format('C%d', [iNextRow])].Value := '合计:' + IntToStr(AllAgentTotalCount) + '(票)';
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 6 + nRecordCount);

      //设置边框
      xlSheet.Range[Format('A3:G%0:d', [iNextRow])].Borders.LineStyle := xlContinuous;
      xlSheet.Cells.EntireColumn.AutoFit;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 7 + nRecordCount);

      //设置页脚
      xlSheet.PageSetup.LeftFooter := '制表：' + DMSystem.WorkName;
      xlSheet.PageSetup.CenterFooter := '审核：';
      xlSheet.PageSetup.RightFooter := '第 &P 页，共 &N 页';
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 8 + nRecordCount);
    except
      if not VarIsNull(xlApp) then
      begin
        xlApp.Quit;
        xlApp.Disconnect;
        xlApp := Unassigned;
        xlApp := NULL;
      end;
      Exit;
    end;
  finally
    Screen.Cursor := crDefault;
    xlSheet := Unassigned;
    xlBook := Unassigned;
    if not VarIsNull(xlApp) then
    begin
      xlApp.Visible := true;
      xlApp := Unassigned;
    end;
    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_FINISHED_UPDATE, 0);
  end;
end;

procedure TFormStatics.ExportLQTicketReportToExcel(AStartDay,
  AEndDay: TDate);
var
  oAgentList: TStringList;
  xlApp, xlBook, xlSheet, xlQuery: Variant;
  sSqlStr, sUserName, sDateSqlStr: String;
  i, iNextRow, AgentTotalCount ,AllAgentTotalCount: Integer;
  fPercentage: Double;
begin
  try
    sUserName := '';
    oAgentList := GetAgentIdList(AStartDay, AEndDay);
    sDateSqlStr := ' AND OPERATEDATE >=' + #39 + DateToStr(AStartDay) + #39 + ' AND ' +
                   'OPERATEDATE <=' + #39 + DateToStr(AEndDay) + #39;
    AllAgentTotalCount := 0;
    AgentTotalCount := 0;