unit ryxxtjfx;       //用Chartfx控件  人員分析

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB, TeeProcs,
  TeEngine, Chart, Series, DbChart, ComCtrls, OleCtrls, Chartfx3;

type
  TTypeOfTimeSm = record // 數值數據的分段數據結構
    min:integer;  //一段的最小數值
    max:integer;  //一段的最大數值
    sm:string;    //關于本數值段的說明
  end;
  TTypeOfTitle =record     //存儲字段名和字段顯示名稱的數據結構
    zd:string;      //字段名
    xs:string;      //顯示名稱
  end;
  TfrmRyxxtjfx = class(TForm)
    GroupBox3: TGroupBox;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    cmbFlxm1: TComboBox;
    CmbFlxm2: TComboBox;
    Button1: TButton;
    Button2: TButton;
    ADODataSet1: TADODataSet;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet4: TTabSheet;
    DBGrid1: TDBGrid;
    ChartfxPie: TChartfx;
    ChartfxBar: TChartfx;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
    FTitleArray :array of TTypeOfTitle ;//分類統計時的字段名稱和顯示的數組
    //下面分別定義年齡、參加工作年限、進入本單位年限的分段數組
    FNlGroup,FCjgzGroup,FJrdwGroup:array of TTypeOfTimeSm ;
    //根據年齡字段的分組獲得進行sql查詢的字符串
    function getZdSqlStr(zdm:string;zdsz:array of TTypeOfTimeSm):string;
    procedure SynSeries;//根據查詢統計結果同步圖表的顯示數據
  public
    { Public declarations }
  end;

var
  frmRyxxtjfx: TfrmRyxxtjfx;

implementation

uses DM;

{$R *.dfm}

procedure TfrmRyxxtjfx.FormCreate(Sender: TObject);
var
  strSQL :string;
  i:integer;
begin
  //以下設置年齡的分段數組
  setLength(FNlGroup,6);
  FNlGroup[0].min := 0;
  FNlGroup[0].max := 20;
  FNlGroup[0].sm := '小于20歲';
  FNlGroup[1].min := 20;
  FNlGroup[1].max := 30;
  FNlGroup[1].sm := '20～30歲';
  FNlGroup[2].min := 30;
  FNlGroup[2].max := 40;
  FNlGroup[2].sm := '30～40歲';
  FNlGroup[3].min := 40;
  FNlGroup[3].max := 50;
  FNlGroup[3].sm := '40～50歲';
  FNlGroup[4].min := 50;
  FNlGroup[4].max := 60;
  FNlGroup[4].sm := '50～60歲';
  FNlGroup[5].min := 60;
  FNlGroup[5].max := MaxInt;
  FNlGroup[5].sm := '大于60歲';

  //以下設置進入單位時間的分段數組
  setLength(FJrdwGroup,9);
  FJrdwGroup[0].min := 0;
  FJrdwGroup[0].max := 1;
  FJrdwGroup[0].sm := '少于1年';
  FJrdwGroup[1].min := 1;
  FJrdwGroup[1].max := 2;
  FJrdwGroup[1].sm := '1～2年';
  FJrdwGroup[2].min := 2;
  FJrdwGroup[2].max := 3;
  FJrdwGroup[2].sm := '2～3年';
  FJrdwGroup[3].min := 3;
  FJrdwGroup[3].max := 5;
  FJrdwGroup[3].sm := '3～5年';
  FJrdwGroup[4].min := 5;
  FJrdwGroup[4].max := 10;
  FJrdwGroup[4].sm := '5～10年';
  FJrdwGroup[5].min := 10;
  FJrdwGroup[5].max := 20;
  FJrdwGroup[5].sm := '10～20年';
  FJrdwGroup[6].min := 20;
  FJrdwGroup[6].max := 30;
  FJrdwGroup[6].sm := '20～30年';
  FJrdwGroup[7].min := 30;
  FJrdwGroup[7].max := 40;
  FJrdwGroup[7].sm := '30～40年';
  FJrdwGroup[8].min := 40;
  FJrdwGroup[8].max := MaxInt;
  FJrdwGroup[8].sm := '多于40年';

  //以下設置參加工作年限分組
  FCjgzGroup := FJrdwGroup ;

  ADOQuery1.SQL.Clear ;
  strSQL := 'drop table TempJbzl';
  ADOQuery1.SQL.Add(strSQL); //首先刪除數據庫中的臨時表
  try
    ADOQuery1.ExecSQL ;
  except
  end;

  //設置查詢時的標題和對應的查詢字段
  SetLength(FTitleArray ,9);
  FTitleArray[0].zd := 'xbdm_mc';
  FTitleArray[0].xs := '性別';
  FTitleArray[1].zd := 'ageGroup';
  FTitleArray[1].xs := '年齡';
  FTitleArray[2].zd := 'workGroup';
  FTitleArray[2].xs := '工作年限';
  FTitleArray[3].zd := 'EnterGroup';
  FTitleArray[3].xs := '進入本單位年限';
  FTitleArray[4].zd := 'bmdm_mc';
  FTitleArray[4].xs := '部門';
  FTitleArray[5].zd := 'gwdm_mc';
  FTitleArray[5].xs := '崗位';
  FTitleArray[6].zd := 'hyzkdm_mc';
  FTitleArray[6].xs := '婚姻狀況';
  FTitleArray[7].zd := 'jszcdm_mc';
  FTitleArray[7].xs := '技術職稱';
  FTitleArray[8].zd := 'xldm_mc';
  FTitleArray[8].xs := '學歷';

  //下面設置分類統計項目的下拉選擇框
  for i := 0 to high(FTitleArray) do
  begin
    cmbFlxm1.Items.Add(FTitleArray[i].xs);
    cmbFlxm2.Items.Add(FTitleArray[i].xs);
  end;
  cmbFlxm2.Items.Add('無項目');
  cmbFlxm1.ItemIndex := 0;
  CmbFlxm2.ItemIndex := high(FTitleArray) + 1;

  //下面按照分類項目生成人員資料的臨時表
  strSQL := '';
  strSQL := 'select xbdm_mc ,' + getZdSqlStr('age',FNlGroup)
      + ' as ageGroup,' + getZdSqlStr('WorkTime',FCjgzGroup)
      + ' as workGroup,'+ getZdSqlStr('InYear',FJrdwGroup)
      + ' as EnterGroup,BMDM_MC,GWDM_MC,HYZKDM_MC,JSZCDM_MC,XLDM_MC '
      + ' into TempJbzl from JbzlCx ' ;
  ADOQuery1.SQL.Clear ;
  ADOQuery1.SQL.Add(strSQL);
  try
    ADOQuery1.ExecSQL ;
  except
    ShowMessage('查詢數據庫時發生錯誤！');
  end;
  Button1Click(self);
end;

//根據數值字段的分類規則，將字段的值劃分到某一個分類中的sql條件
function TfrmRyxxtjfx.getZdSqlStr(zdm:string;
    zdsz:array of TTypeOfTimeSm):string;
var
  i:integer;
begin
  result:= '';
  for i := low(zdsz) to high(zdsz) do
  begin
    result := result + 'iif((' + zdm + '>=' + inttoStr(zdsz[i].min) +
      ') and (' + zdm +'<' + IntToStr(zdsz[i].max) + '),''' +
      zdsz[i].sm + ''',';
  end;
  for i := low(zdsz) to high(zdsz) do
  begin
    result := result + ')';
  end;
  if result = '' then
  begin
    result := zdm;
  end;
end;


procedure TfrmRyxxtjfx.Button1Click(Sender: TObject);
var
  strSQL :string;
begin
  if cmbFlxm1.ItemIndex = CmbFlxm2.ItemIndex then
  begin
    ShowMessage('主分類統計項目與第二分類統計分類項目相同！');
    CmbFlxm2.SetFocus ;
    exit;
  end;

  //下面根據用戶選擇的分類統計項目進行
  if CmbFlxm2.ItemIndex = high(FTitleArray) + 1 then//第二分類項目為空
  begin
    strSQL := 'SELECT ' + FTitleArray[cmbFlxm1.itemIndex].zd +
              ',Count(xbdm_mc) as 人數' +
            ' FROM TempJbzl ' +
            ' GROUP BY ' + FTitleArray[cmbFlxm1.itemIndex].zd ;
  end
  else
  begin
    strSQL := ' TRANSFORM Count(xbdm_mc) ' +
            'SELECT '+ FTitleArray[cmbFlxm1.itemIndex].zd +
            ',Count(xbdm_mc) as 合計 FROM TempJbzl ' +
            ' GROUP BY ' + FTitleArray[cmbFlxm1.itemIndex].zd +
            ' PIVOT ' +  FTitleArray[cmbFlxm2.itemIndex].zd ;
  end;
  ADODataSet1.Active := False ;
  ADODataSet1.CommandText := strSQL ;
  ADODataSet1.Active := true;
  DBGrid1.Columns[0].Title.Caption := FTitleArray[cmbFlxm1.itemIndex].xs ;
  dbgrid1.Columns[0].Width := 100;
  SynSeries ;
end;

//同步圖表和查詢結果的數據
procedure TfrmRyxxtjfx.SynSeries;
var
  i,j :integer;
  max:double;
begin
  if ADODataSet1.IsEmpty then  //查詢結果為空
  begin
    exit;
  end;

  max := 0;
  if CmbFlxm2.ItemIndex = high(FTitleArray) + 1 then //第二分類項目為空
  begin
    ADODataSet1.First ;
    i:=0;
    ChartfxBar.OpenDataEx(COD_VALUES,1,ADODataSet1.RecordCount);
    ChartfxBar.ThisSerie:=0;
    ChartfxPie.OpenDataEx(COD_VALUES,1,ADODataSet1.RecordCount);
    ChartfxPie.ThisSerie:=0;
    while not ADODataSet1.Eof do   //循環所有查詢到的記錄
    begin
      if ADODataSet1.Fields[1].Value = null then  //字段值為空值
      begin
        ChartfxBar.Value[i]:=0 ;
        ChartfxPie.Value[i]:=0 ;
      end
      else  //字段值不為空值
      begin
        if ADODataSet1.Fields[1].Value >max then
        begin
          max := ADODataSet1.Fields[1].Value;
        end;
        ChartfxBar.Value[i]:=ADODataSet1.Fields[1].Value ;
        ChartfxPie.Value[i]:=ADODataSet1.Fields[1].Value ;
      end;//if ADODataSet1.Fields[1].Value =null
      ChartfxBar.Legend[i]:=ADODataSet1.Fields[0].Value ;
      ChartfxBar.Adm[CSA_MAX] := max ;
      ChartfxPie.Legend[i]:=ADODataSet1.Fields[0].Value ;
      inc(i);
      ADODataSet1.Next ;
    end;//while not ADODataSet1.Eof
    ChartfxBar.Title[CHART_LEFTTIT]:= '人數';
    ChartfxBar.Title[CHART_BOTTOMTIT]:= cmbFlxm1.Text;
    ChartfxPie.Title[CHART_LEFTTIT]:= '人數';
    ChartfxPie.Title[CHART_BOTTOMTIT]:= cmbFlxm1.Text;
    ChartfxBar.CloseData(COD_VALUES);
    ChartfxPie.CloseData(COD_VALUES);
  end
  else  //第二分類項目不為空
  begin
    ADODataSet1.First ;
    i:=0;
    ChartfxBar.OpenDataEx(COD_VALUES,
      ADODataSet1.FieldCount-2,ADODataSet1.RecordCount);
    ChartfxPie.OpenDataEx(COD_VALUES,1,ADODataSet1.RecordCount);
    while not ADODataSet1.Eof do   //循環所有查詢到的記錄
    begin
      //下面為ChartfxPie賦值
      ChartfxPie.ThisSerie:= 0;
      if ADODataSet1.Fields[1].Value = null then
      begin
        ChartfxPie.Value[i]:=0 ;
      end
      else
      begin
        ChartfxPie.Value[i] := ADODataSet1.Fields[1].Value ;
      end;
      ChartfxPie.Legend[i] := ADODataSet1.Fields[0].Name ;
      //下面為ChartfxBar賦值
      for j := 2 to ADODataSet1.FieldCount - 1 do
      begin
        ChartfxBar.ThisSerie:= j-2;
        if ADODataSet1.Fields[j].Value =null then
        begin
          ChartfxBar.Value[i]:=0 ;
        end
        else
        begin
          if ADODataSet1.Fields[j].Value >max then
          begin
            max := ADODataSet1.Fields[j].Value;
          end;
          ChartfxBar.Value[i]:=ADODataSet1.Fields[j].Value ;
        end;
      end;
      ChartfxBar.Legend[i]:=ADODataSet1.Fields[0].Name ;
      ChartfxBar.Adm[CSA_MAX] := max ;
      inc(i);
      ADODataSet1.Next ;
    end;//while not ADODataSet1.Eof
    for j := 2 to ADODataSet1.FieldCount - 1 do
    begin
      ChartfxBar.SerLeg[j-2] := ADODataSet1.Fields[j].DisplayName;
    end;
    ChartfxBar.Title[CHART_LEFTTIT]:= '人數';
    ChartfxBar.Title[CHART_BOTTOMTIT]:=cmbFlxm1.Text;
    ChartfxPie.Title[CHART_LEFTTIT]:= '人數';
    ChartfxPie.Title[CHART_BOTTOMTIT]:= cmbFlxm1.Text;
    ChartfxPie.CloseData(COD_VALUES);
    ChartfxBar.CloseData(COD_VALUES);
  end;// if CmbFlxm2.ItemIndex = high(FTitleArray) + 1
end;

procedure TfrmRyxxtjfx.FormClose(Sender: TObject;
  var Action: TCloseAction);
var
  strSQL: string;
begin
  ADOQuery1.SQL.Clear ;
  strSQL := 'drop table TempJbzl';
  ADOQuery1.SQL.Add(strSQL);//刪除查詢結果臨時表
  try
    ADOQuery1.ExecSQL ;
  except
  end;
  Action:= caFree; 
end;

procedure TfrmRyxxtjfx.Button2Click(Sender: TObject);
begin
  Close ;
end;

end.