ryxxtjfx.pas

酒店资源管理的软件

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.