masterbrow.pas

pasa人力资源考勤管理系统

      adodirect.close;
      adoprovname.close;
      adosexname.close;
      adoyears.close;
      adoage.close;
      adoleavereason.close;
      adodirect.sql.text:='';
    Case TreeView1.Selected.Level of
     0: {部门} begin
                adodirect.SQL.Text :='select count(a.cardno) as num,b.profkind from per24010 a ,per01010 b where a.prof=b.profname and a.leave=0 and b.profkind '+
                                     'like ''直接''+''%'' group by b.profkind '+
                                     'union '+
                                     'select count(a.cardno) as num,b.profkind from per24010 a ,per01010 b where a.prof=b.profname and a.leave=0 and b.profkind '+
                                     'like ''间接''+''%'' and substring(dept,1,charindex(''.'',dept,1)-1)='''+treeview1.selected.text+''' group by b.profkind ';
               end;
     1: {组别} begin
                adodirect.SQL.Text :='select count(a.cardno) as num,b.profkind from per24010 a ,per01010 b where a.prof=b.profname and a.leave=0 and b.profkind '+
                                     'like ''直接''+''%'' group by b.profkind '+
                                     'union '+
                                     'select count(a.cardno) as num,b.profkind from per24010 a ,per01010 b where a.prof=b.profname and a.leave=0 and b.profkind '+
                                     'like ''间接''+''%'' and dept='''+treeview1.selected.text+''' group by b.profkind ';
              end;
    end;
    if (TreeView1.Selected.Level <>2) or (VarMyString = 'Enterprise') then
    begin
     adodirect.Open;
     Notebook1.PageIndex :=0;
    end else
    begin
       Notebook1.PageIndex :=1;
       adodataset1.Open;
       adodataset2.open;
       adodataset3.open;
       adodataset4.open;
       adodataset1.Filtered:=false;
       adodataset1.Filter:='name='''+TreeView1.Selected.Text+'''';
       adodataset1.Filtered:=true;
    end;
    {性别分布图}
      if VarMyString = 'Enterprise' then
         adosexname.SQL.Text:='select sexname,count(workno) from per24010 where leave=0 group by sexname' else
      Case TreeView1.Selected.Level of
       0:{部门} begin
                 adosexname.SQL.Text:='select sexname,count(workno) from per24010 where substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 group by sexname';
                end;
       1:{组别} begin
                 adosexname.SQL.Text:='select sexname,count(workno) from per24010 where dept='''+treeview1.selected.text+''' and leave=0 group by sexname';
                end;
      end;
      adosexname.Open;
      if VarMyString = 'Enterprise' then
         adoprovname.SQL.Text:='select substring(provname,1,2),count(workno) from per24010 where leave=0 group by substring(provname,1,2)' else
      Case TreeView1.Selected.Level of
       0:{部门} begin
                 adoprovname.SQL.Text:='select substring(provname,1,2),count(workno) from per24010 where substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 group by substring(provname,1,2)';
                end;
       1:{组别} begin
                 adoprovname.SQL.Text:='select substring(provname,1,2),count(workno)  from per24010 where dept='''+treeview1.selected.text+''' group by substring(provname,1,2)';
                end;
      end;
      adoprovname.Open;
    {资历比例}
   if (VarMyString = 'Enterprise') or (TreeView1.Selected.Level<2) then
   begin
     adoyears.Close;
    if VarMyString = 'Enterprise' then
    begin
       adoyears.SQL.Text:='select ''半年以下'' as name1,count(workno) as num1 from per24010 where years<0.06 and years>0 and leave=0 '+
                          'union '+
                          'select ''半年至一年'' as name1,count(workno) from per24010 where years>=0.06 and years<=1 and leave=0 '+
                          'union '+
                          'select ''一年以上至二年'' as name1,count(workno) from per24010 where years>1 and years<=2 and leave=0 '+
                          ' union '+
                          'select ''二年以上至五年'' as name1,count(workno) from per24010 where years>2 and years<=5 and leave=0 '+
                          ' union '+
                          'select ''五年以上到十年'' as name1,count(workno) from per24010 where years>6.0 and years<=10 and leave=0 '+
                          'union '+
                          'select ''十年以上'' as name1,count(workno) from per24010 where years>10 and leave=0';
    end else
    Case TreeView1.Selected.Level of
     0:{部门} begin
               adoyears.SQL.Text:='select ''半年以下'' as name1,count(workno) as num1 from per24010 where years<0.06 and years>0 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''半年至一年'' as name1,count(workno) from per24010 where years>=0.06 and years<=1 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''一年以上至二年'' as name1,count(workno) from per24010 where years>1 and years<=2 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                  ' union '+
                                  'select ''二年以上至五年'' as name1,count(workno) from per24010 where years>2 and years<=5 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                  ' union '+
                                  'select ''五年以上到十年'' as name1,count(workno) from per24010 where years>6.0 and years<=10 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''十年以上'' as name1,count(workno) from per24010 where years>10 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 ';
              end;
     1:{组别} begin
               adoyears.SQL.Text:='select ''半年以下'' as name1,count(workno) as num1 from per24010 where years<0.06 and years>0 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''半年至一年'' as name1,count(workno) from per24010 where years>=0.06 and years<=1 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''一年以上至二年'' as name1,count(workno) from per24010 where years>1 and years<=2 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                  ' union '+
                                  'select ''二年以上至五年'' as name1,count(workno) from per24010 where years>2 and years<=5 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                  ' union '+
                                  'select ''五年以上到十年'' as name1,count(workno) from per24010 where years>6.0 and years<=10 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                  'union '+
                                  'select ''十年以上'' as name1,count(workno) from per24010 where years>10 and dept='''+treeview1.selected.text+''' and leave=0 ';
              end;
    end;
 end;
 adoyears.Open;
    {年龄比例图}
   if (VarMyString = 'Enterprise') or (TreeView1.Selected.Level<2) then
   begin
    adoage.Close;
    if VarMyString = 'Enterprise' then
    begin
       adoage.SQL.Text:='select ''20岁以下'' as name1,count(*) as num1  from per24010 where age<20 and leave=0 '+
                        'union '+
                        'select ''20-25岁'' as name1,count(*)  from per24010 where age>20 and age<=25 and leave=0 '+
                        'union '+
                        'select ''26-30岁'' as name1,count(*) from per24010 where age>=26 and age<=30 and leave=0 '+
                        'union '+
                        'select ''30-40岁'' as name1,count(*) from per24010 where age>30 and age<=40 and leave=0 '+
                        'union '+
                        'select ''40岁以上'' as name1,count(*) from per24010 where age>40 and leave=0 ';
    end else
    Case TreeView1.Selected.Level of
     0:{部门} begin
                adoage.SQL.Text:='select ''20岁以下'' as name1,count(*) as num1  from per24010 where age<20 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''20-25岁'' as name1,count(*)  from per24010 where age>20 and age<=25 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''26-30岁'' as name1,count(*) from per24010 where age>=26 and age<=30 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''30-40岁'' as name1,count(*) from per24010 where age>30 and age<=40 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''40岁以上'' as name1,count(*) from per24010 where age>40 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 ';
              end;
     1:{组别} begin
                adoage.SQL.Text:='select ''20岁以下'' as name1,count(*) as num1  from per24010 where age<20 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''20-25岁'' as name1,count(*)  from per24010 where age>20 and age<=25 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''26-30岁'' as name1,count(*) from per24010 where age>=26 and age<=30 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''30-40岁'' as name1,count(*) from per24010 where age>30 and age<=40 and dept='''+treeview1.selected.text+''' and leave=0 '+
                                 'union '+
                                 'select ''40岁以上'' as name1,count(*) from per24010 where age>40 and dept='''+treeview1.selected.text+''' and leave=0 ';
              end;
    end;
    adoage.Open;
    //
      qry:=tadoquery.create(self);
      qry.connection:=DataMod.Database1;
   if (VarMyString = 'Enterprise') or (TreeView1.Selected.Level<2) then
   begin
    {月薪}
    if VarMyString = 'Enterprise' then
    begin
       qry.Close;
       qry.SQL.Text :='select avg(basepay) as avgbasepay from per24010 where dayormon=0 and leave=0 ';
       qry.Open;
       Label8.Caption :='平均:'+FloatToStrF(qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
       Qry.Close;
       Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon=0 and leave=0 ';
       qry.Open;
       Label12.Caption :='最高:'+FloatToStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
       Qry.Close;
       Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon=0 and leave=0 ';
       Qry.Open;
       Label22.Caption :='最低:'+FloatToStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
    end else
    Case TreeView1.Selected.Level of
     0:{部门} begin
                Qry.Close;
                Qry.SQL.Text :='SELECT Avg(basepay) AS Avgbasepay FROM per24010 WHERE dayormon=0 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label8.Caption :='平均:'+FloattostrF(Qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon =0  and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label12.Caption :='最高:'+FloattoStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon = 0 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label22.Caption :='最低:'+FloattoStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
              end;
     1:{组别} begin
                Qry.Close;
                Qry.SQL.Text :='SELECT Avg(basepay) AS Avgbasepay FROM per24010 WHERE dayormon=0 and dept='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label8.Caption :='平均:'+FloattostrF(Qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon =0  and dept='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label12.Caption :='最高:'+FloattoStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon = 0 and dept='''+treeview1.selected.text+''' and leave=0 ';
                Qry.Open;
                Label22.Caption :='最低:'+FloattoStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
              end;
    end;

    {日薪}
   if VarMyString = 'Enterprise' then
    begin
       qry.Close;
       qry.SQL.Text :='select avg(basepay) as avgbasepay from per24010 where dayormon=1 and leave=0';
       qry.Open;
       Label26.Caption :='平均:'+FloatToStrF(qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
       Qry.Close;
       Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon=1  and leave=0';
       qry.Open;
       Label27.Caption :='最高:'+FloatToStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
       Qry.Close;
       Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon=1 and leave=0 ';
       Qry.Open;
       Label33.Caption :='最低:'+FloatToStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
    end else
    Case TreeView1.Selected.Level of
     0:{部门} begin
                Qry.Close;
                Qry.SQL.Text :='SELECT Avg(basepay) AS Avgbasepay FROM per24010 WHERE dayormon=1 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label26.Caption :='平均:'+FloattostrF(Qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon =1  and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label27.Caption :='最高:'+FloattoStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon =1 and substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label33.Caption :='最低:'+FloattoStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
              end;
     1:{组别} begin
                Qry.Close;
                Qry.SQL.Text :='SELECT Avg(basepay) AS Avgbasepay FROM per24010 WHERE dayormon=1 and dept='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label26.Caption :='平均:'+FloattostrF(Qry.FieldbyName('Avgbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT MAX(basepay) AS Maxbasepay FROM per24010 WHERE dayormon =1  and dept='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label27.Caption :='最高:'+FloattoStrF(Qry.FieldbyName('Maxbasepay').AsFloat,ffNumber,6,2);
                Qry.Close;
                Qry.SQL.Text :='SELECT Min(basepay) AS Minbasepay FROM per24010 WHERE dayormon = 1 and dept='''+treeview1.selected.text+''' and leave=0';
                Qry.Open;
                Label33.Caption :='最低:'+FloattoStrF(Qry.FieldbyName('Minbasepay').AsFloat,ffNumber,6,2);
              end;
    end;
   end;
   {离职原因分析}
  if (VarMyString = 'Enterprise') or (TreeView1.Selected.Level<2) then
     adoleavereason.Close;
       if VarMyString = 'Enterprise' then
          begin
            adoleavereason.SQL.Text:='select reason,count(workno) as num1 from per16010 group by reason order by num1'
          end else
        Case TreeView1.Selected.Level of
         0:{部门} begin
                     adoleavereason.sql.Text :='select reason,count(workno) as num1 from per16010 where substring(dept,1,case when charindex(''.'',dept,1)>1 then charindex(''.'',dept,1)-1 else len(dept) end)='''+treeview1.selected.text+''' group by reason order by num1'
                  end;
         1:{组别} begin
                    adoleavereason.SQL.Text:='select reason,count(workno) as num1 from per16010 where dept='''+treeview1.selected.text+''' group by reason order by num1'
                  end;
        end;
        adoleavereason.Open;
  end;
end;
end;

procedure TFormmasterbrow.BitBtn1Click(Sender: TObject);
begin
  VarMyString := 'Enterprise';
  CaclChart;
  VarMyString := '';
end;

procedure TFormmasterbrow.TreeView1DblClick(Sender: TObject);
begin
  CaclChart;
end;

procedure TFormmasterbrow.FormDestroy(Sender: TObject);
begin
  adoquery1.Close;
  adodataset1.Close;
  adodataset2.close;
  adodataset3.close;
  adodataset4.Close;
  adodirect.Close;
  adoprovname.Close;
  adosexname.Close;
  adoyears.Close;
  adoage.close;
  adoleavereason.Close;
end;

end.