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

*)
unit sz_jysrtjfx;

interface

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

type
  TFrmz_jysrtjfx = class(TForm)
    Panel1: TPanel;
    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;
    Panel4: TPanel;
    Chart1: TChart;
    DataSource1: TDataSource;
    Query1: TQuery;
    RG_Tjx: TRadioGroup;
    Panel2: TPanel;
    Panel5: TPanel;
    Label10: TLabel;
    RxLabel_Title: TRxLabel;
    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_jysrtjfx: TFrmz_jysrtjfx;

implementation

uses pub,sz_main, DataList;

{$R *.DFM}

type
  TFieldData = record
    Title: string;
    Color: TColor;
  end;

const
  FieldNum = 4;
  Spacing = 2;
  FieldInfo: array[0..FieldNum - 1] of TFieldData =
    ((Title: '集郵票收入'; Color: clRed),
    (Title: '郵品收入'; Color: clBlue),
    (Title: '其它收入'; Color: clGreen),
    (Title: '毛利'; Color: clYellow)
    );

procedure TFrmz_jysrtjfx.BBt_SendClick(Sender: TObject);
  function GetCondDW: string;
  var
    i: integer;
  begin
    Result := '';
//2002-03-08 by jillshao
{    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.JYPSR)/10000 集郵票收入,sum(A.YPSR)/10000 郵品收入,sum(A.QTSR)/10000 其它收入,sum(A.ML)/10000 毛利 from TYS_JYYWSRTJB 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 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
      FieldStart := RG_Tjx.ItemIndex + 1;
      FieldEnd := FieldStart;
      FChartSeries.ColorEachPoint := true;
    end
    else
    begin
      FieldStart := 1;
      FieldEnd := FieldNum;
    end;
    X := 0;
    while not Query1.Eof do
    begin
      for i := FieldStart to FieldEnd 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, FieldInfo[i - 1].Color);
        X := X + 1;
      end;
      //以柱狀形勢統計時，在各單位或年度之間留空列
      if RB_Zhu.Checked then
        X := X + Spacing;
      Query1.Next;
    end;
  end;
end;

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

procedure TFrmz_jysrtjfx.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, '匯總');
  end;}
end;

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

procedure TFrmz_jysrtjfx.Check_TQClick(Sender: TObject);
begin
//  YF_End.Enabled := Check_TQ.Checked;
end;

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

end.