{*******************************************************}
{                                                       }
{                 通信貼票收入統計分析                  }
{                                                       }
{            中軟金馬公司版權所有。2002.12前            }
{                                                       }
{            編制：中軟金馬郵資票品項目開發組           }
{                                                       }
{                                                       }
{*******************************************************}
(*
省級綜合查詢

*)
unit sz_txtpsrtjfx;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  TeEngine, Series, ExtCtrls, TeeProcs, Chart, RXCtrls, StdCtrls, Spin,
  Buttons, CheckComboBox, Db, DBTables, Grids, DBGrids, ComboBoxDB, Clipbrd;

type
  TFrmz_txtpsrtjfx = class(TForm)
    Panel1: TPanel;
    XttxTitle: TRxLabel;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    GroupBox3: TGroupBox;
    RB_2D: TRadioButton;
    RB_3D: TRadioButton;
    BBt_Send: TBitBtn;
    BBt_Quit: TBitBtn;
    GroupBox4: TGroupBox;
    RB_Zhu: TRadioButton;
    RB_Bing: TRadioButton;
    Label4: TLabel;
    RB_Zhe: TRadioButton;
    Label1: TLabel;
    Label5: TLabel;
    ND_Start: TSpinEdit;
    ND_End: TSpinEdit;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    YF_Start: TSpinEdit;
    YF_End: TSpinEdit;
    Check_TQ: TCheckBox;
    CB_DW: TCheckComboBox;
    RB_Biao: TRadioButton;
    ScrollBox1: TScrollBox;
    DBGrid1: TDBGrid;
    Panel_Tl: TPanel;
    Panel3: TPanel;
    Chart1: TChart;
    DataSource1: TDataSource;
    Query1: TQuery;
    Label11: TLabel;
    CB_PPLB: TComboBox;
    CB_DWTQ: TComboBoxDB;
    Label10: TLabel;
    procedure BBt_SendClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Check_TQClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    procedure DoOnGetMarkText(Sender: TChartSeries; ValueIndex: Longint; var MarkText: string);
  public
    { Public declarations }
  end;

var
  Frmz_txtpsrtjfx: TFrmz_txtpsrtjfx;

implementation

uses
  pub,sz_main, DataList;

const
  Spacing = 2;

{$R *.DFM}

procedure TFrmz_txtpsrtjfx.BBt_SendClick(Sender: TObject);
  function GetCondDW: string;
  var
    i: integer;
  begin
    Result := '';
//2002-03-08 by jillshao
{    if Check_TQ.Checked then
    begin
      if CB_DWTQ.ItemIndex > 0 then
        Result := Format(' and (A.DWDM=''%s'')', [CB_DWTQ.Values[CB_DWTQ.ItemIndex]]);
    end
    else
    begin
      for i := 0 to CB_DW.Items.Count - 1 do
        if CB_DW.Checked[i] then
         //第0個表示全部，所以不用處理這個條件
          if i > 0 then
            if Result = '' then
              Result := Result + Format(' and (A.DWDM=''%s''', [VarAsType(PItemData(CB_DW.Items.Objects[i])^.Value, varString)])
            else
              Result := Result + Format(' or A.DWDM=''%s''', [VarAsType(PItemData(CB_DW.Items.Objects[i])^.Value, varString)])
          else
            exit;}
    for i := 0 to CB_DW.Items.Count - 1 do
      if CB_DW.Checked[i] then
        if Result = '' then
          Result := Result + Format(' and (A.DWDM=''%s''', [VarAsType(PItemData(CB_DW.Items.Objects[i])^.Value, varString)])
        else
          Result := Result + Format(' or A.DWDM=''%s''', [VarAsType(PItemData(CB_DW.Items.Objects[i])^.Value, varString)]);

    if Result <> '' then
      Result := Result + ')';
  end;
  function GetFmtMon(AMon: string): string;
  begin
    if Length(AMon) = 1 then
      Result := '0' + AMon
    else
      Result := AMon;
  end;
const
  QuerySQL = 'select %s,sum(A.SQTPSRHZ)/10000 本期貼票收入 from TYS_TPSRYKCDZB A%s where 1=1';
var
  FChartSeries: TChartSeries;
  CondDW: string;
  Cap: string;
  i, X: integer;
  FieldStart, FieldEnd: integer;
begin
  //建立統計圖類
  FChartSeries := nil;
  if RB_Zhu.Checked then
  begin
    FChartSeries := TBarSeries.Create(Self);
    with TBarSeries(FChartSeries) do
    begin
      BarWidthPercent := 100;
    end;
  end
  else if RB_Bing.Checked then
    FChartSeries := TPieSeries.Create(Self)
  else if RB_Zhe.Checked then
    FChartSeries := TLineSeries.Create(Self)
  else if RB_Biao.Checked then
  begin
    Chart1.Visible := false;
    DBGrid1.Visible := true;
  end;
  Panel_Tl.Visible := RB_Zhu.Checked;
  if FChartSeries <> nil then
  begin
    Chart1.Visible := true;
    DBGrid1.Visible := false;
    Chart1.SeriesList.Clear;
    if not RB_Bing.Checked then
      FChartSeries.OnGetMarkText := DoOnGetMarkText;
    Chart1.AxisVisible := true;
    Chart1.Title.Text.Clear;
    Chart1.AllowZoom := true;
    Chart1.AllowPanning := pmBoth;
    Chart1.View3DOptions.Orthogonal := true;
    Chart1.UndoZoom;
    if RB_2D.Checked then
      Chart1.View3D := false
    else
      Chart1.View3D := true;
    FChartSeries.ParentChart := Chart1;
    FChartSeries.Marks.Font.Name := '宋體';
    FChartSeries.Marks.Font.Size := 9;
  end;

  Query1.Close;
  Query1.SQL.Clear;
  if Check_TQ.Checked then
  begin
    if YF_Start.Value > 0 then
      Query1.SQL.Add(Format(QuerySQL, ['A.ND||''.''||A.YF 日期', ',TGS_GXDWSJB B']))
    else
      Query1.SQL.Add(Format(QuerySQL, ['A.ND 日期', ',TGS_GXDWSJB B']));
  end
  else
  begin
    Query1.SQL.Add(Format(QuerySQL, ['B.DWMC 單位名稱', ',TGS_GXDWSJB B']));
  end;
  Query1.SQL.Add('and A.DWDM=B.DWDM(+)');
  CondDW := GetCondDW;
  if CondDW <> '' then
    Query1.SQL.Add(CondDW);
//2002-03-06時間段條件sql
{  if ND_Start.Value > 0 then
    Query1.SQL.Add(Format('and to_number(A.ND)>=%d', [ND_Start.Value]));
  if ND_End.Value > 0 then
    Query1.SQL.Add(Format('and to_number(A.ND)<=%d', [ND_End.Value]));
  if YF_Start.Value > 0 then
  begin
    Query1.SQL.Add(Format('and to_number(A.YF)>=%d', [YF_Start.Value]));
    if not Check_TQ.Checked and (YF_End.Value > 0) then
      Query1.SQL.Add(Format('and to_number(A.YF)<=%d', [YF_End.Value]));
  end; }
  Query1.SQL.Add(Format(' and to_number(ND||YF) between %s and %s', [ND_Start.Text + GetFmtMon(YF_Start.Text), ND_End.Text + GetFmtMon(YF_End.Text)]) + ' ');
  if CB_PPLB.ItemIndex > 0 then
    Query1.SQL.Add(Format('and A.PPLBMC=''%s''', [CB_PPLB.Items[CB_PPLB.ItemIndex]]));
  if Check_TQ.Checked then
  begin
    if YF_Start.Value > 0 then
      Query1.SQL.Add('group by A.ND,A.YF')
    else
      Query1.SQL.Add('group by A.ND');
  end
  else
  begin
    Query1.SQL.Add('group by B.DWMC');
  end;
  Query1.Open;

  //畫統計圖
  if FChartSeries <> nil then
  begin
    if RB_Zhe.Checked and (Query1.RecordCount < 2) then
    begin
      MessageBox(Handle, '現在的設置不適合做折線統計圖，請用其它方式進行統計。', '統計分析', MB_ICONWARNING);
      exit;
    end;
    if RB_Bing.Checked or RB_Zhe.Checked then
    begin
      FChartSeries.ColorEachPoint := true;
    end
    else
    begin
    end;
    X := 0;
    while not Query1.Eof do
    begin
      for i := 1 to 1 do
      begin
        {if i > 1 then Cap := ''
        else } Cap := Query1.Fields[0].AsString;
        if RB_Bing.Checked or RB_Zhe.Checked then
          FChartSeries.AddXY(X, Query1.Fields[i].AsFloat, Cap)
        else
          FChartSeries.AddXY(X, Query1.Fields[i].AsFloat, Cap, clBlue);
        X := X + 1;
      end;
      //以柱狀形勢統計時，在各單位或年度之間留空列
      if RB_Zhu.Checked then
        X := X + Spacing;
      Query1.Next;
    end;
  end;
end;

procedure TFrmz_txtpsrtjfx.DoOnGetMarkText(Sender: TChartSeries;
  ValueIndex: Integer; var MarkText: string);
begin
  MarkText := FormatFloat('#,##0.###', Sender.YValue[ValueIndex]);
end;

procedure TFrmz_txtpsrtjfx.FormCreate(Sender: TObject);
begin
  GetAllColWidth(Self);
  ND_Start.Value := VG_Year;
  ND_End.Value := VG_Year;
  YF_Start.Value := 1;
  YF_End.Value := VG_Month;
  InitDataList(CB_DW.Items, Frmz_main.DB_GZ.DatabaseName,
    'select DWMC,DWDM from TGS_GXDWSJB where ZJXJ=''1'' order by DWDM');
//2002-03-08 by jillshao
{  if CB_DW.Items.Count > 0 then
  begin
    CB_DW.Items.Insert(0, '匯總');
    CB_DW.ItemIndex := 0;
  end;
  if CB_DWTQ.Open then
  begin
    CB_DWTQ.Items.Insert(0, '匯總');
    CB_DWTQ.ItemIndex := 0;
  end;}
  with CB_PPLB.Items do
  begin
    Add('匯總');
    Add('通信票');
    Add('國際回信券');
  end;
  CB_PPLB.ItemIndex := 0;
end;

procedure TFrmz_txtpsrtjfx.FormDestroy(Sender: TObject);
begin
  ClearDataList(CB_DW.Items);
end;

procedure TFrmz_txtpsrtjfx.Check_TQClick(Sender: TObject);
begin
{  YF_End.Enabled := Check_TQ.Checked;
  CB_DW.Visible := not Check_TQ.Checked;
  CB_DWTQ.Visible := Check_TQ.Checked;}
end;

procedure TFrmz_txtpsrtjfx.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SaveAllColWidth(Self);
end;

end.