bustradeyearsumunit.~pas

公交行业的管理系统

unit BusTradeYearSumUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, DB, ADODB, StdCtrls, Buttons, ExtCtrls;

type
  TBusTradeYearSumFrm = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    ListView1: TListView;
    TabSheet2: TTabSheet;
    ListView2: TListView;
    Panel1: TPanel;
    Panel3: TPanel;
    Label2: TLabel;
    ComboBox3: TComboBox;
    Panel6: TPanel;
    Label5: TLabel;
    Panel4: TPanel;
    Label3: TLabel;
    ComboBox2: TComboBox;
    Panel2: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    Panel7: TPanel;
    Label1: TLabel;
    ADOQuery1: TADOQuery;
    ADOQuery3: TADOQuery;
    Label4: TLabel;
    Panel8: TPanel;
    Label6: TLabel;
    Label8: TLabel;
    ComboBox1: TComboBox;
    Edit1: TEdit;
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    procedure FillTimeDataToArrary();
    procedure FillSumDataToArrary();
    procedure FillAverageData();
    procedure FillDataTolist();
    procedure ClearArrary();
    procedure initialcolumn(List:TListView);
    procedure WriteSpecialIndex();
    function  Converter(constr:string):String;
  public
    vals:array[1..86]of string;
  end;

var
  BusTradeYearSumFrm: TBusTradeYearSumFrm;

implementation

uses  PrintProc, BusTradeDataModuleUnit, PrintInfoUnit;

{$R *.dfm}

procedure TBusTradeYearSumFrm.BitBtn1Click(Sender: TObject);
var     sqlstr:string;
        j:integer;
        iRow:integer;
        iExistFlag:integer;
begin

        if(combobox3.text='')and(combobox2.text='') then
        begin
                showmessage('请选择汇总的基期和报告期！');
                exit;
        end;

        if(combobox3.text='')then
        begin
                showmessage('请选择汇总基期！');
                exit;
        end;

        if(combobox2.text='')then
        begin
                showmessage('请选择汇总报告期！');
                exit;
        end;

        if(length(combobox3.Text)<>4)or (length(combobox2.Text)<>4)then
        begin
                showmessage('对不起，您所输入的年份不正确！');
                BitBtn2.enabled:=false;
                exit;
        end else
        begin
                try
                        strtoint(combobox2.text);
                        strtoint(combobox3.Text);
                except
                        showmessage('对不起，您所输入的年份不正确！');
                        BitBtn2.enabled:=false;
                        exit;
                end;
        end;

        if(strtoint(combobox3.text)>strtoint(combobox2.text))then
        begin
                showmessage('对不起，您选择的年份有误！');
                BitBtn2.enabled:=false;
                exit;
        end;

        //开始判断数据库中是否需要的数据

        sqlstr:='select 指标代码,'+combobox1.text+' from 公交行业统计年报_z where 城市代码='+''''+'022'+''''
                +' and 统计年份>='+combobox3.text+' and 统计年份<='+combobox2.Text;
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;

        if ADOQuery1.RecordCount<=0 then
        begin
                showmessage(combobox3.text+'年到'+combobox2.text+'年之间没有数据！请重新选择！');
                BitBtn2.Enabled:=false;
                exit;
         end
         else
         begin
                iExistFlag:=0;
                ADOQuery1.First;
                for  iRow:= 0 to ADOQuery1.RecordCount-1 do
                begin
                        if ADOQuery1.FieldByName(combobox1.text).AsString<>'' then
                        begin
                                iExistFlag:=1;
                                break;
                        end;
                        ADOQuery1.Next;
                end;
                if iExistFlag=0 then
                begin
                        ShowMessage('对不起！数据库中没有'+combobox3.Text+'年与'+combobox2.text+'年的数据！');
                        BitBtn2.enabled:=false;
                        exit;
                end;
         end;
        //结束判断数据库中是否有需要的数据

        BitBtn2.Enabled:=true;

        FillTimeDataToArrary();
        FillSumDataToArrary();
        FillDataTolist();
        FillAverageData();
        WriteSpecialIndex();
        ClearArrary();

end;

procedure TBusTradeYearSumFrm.BitBtn2Click(Sender: TObject);
var iRecNum,iCol,iRow,i:integer;
begin
        if DeviceDetect=0 then
        begin
                exit;
        end;

        //AddPrn;
        if(PrnInfoFrm.ShowModal<>mrOK)then
        begin
                exit;
        end;


        PrintInit(ExtractFilePath(Application.ExeName),'BusTradeYearStaTab');
        for i:=0 to 1 do
        begin
                WriteCell(4+i*31,1,'汇总单位:  '+edit1.text);
                WriteCell(3+i*31,4,combobox3.text+' 年至 '+combobox2.text+' 年');
                WriteCell(31+i*29,1,'统计负责人:' + PrnInfoFrm.StatMainPerson);
                WriteCell(31+i*29,4, '统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(31+i*29,7,'填报日期:' + DateToStr(PrnInfoFrm.StatDate));

        end;
        for iRecNum := 1 to 86 do
        begin
                if (iRecNum<=23)then
                begin
                        iRow := iRecNum+7;
                        iCol := 4;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum-1].SubItems[2]);
                end;
                if (iRecNum>=24) and (iRecNum<=46) then
                begin
                        iRow := iRecNum-16;
                        iCol:=8;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum-24].SubItems[6]);
                end;
                if (iRecNum>=47) and (iRecNum<=67) then
                begin
                        iRow := iRecNum-8;
                        iCol:=4;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-47].SubItems[2]);
                end;
                if (iRecNum>=68) and (iRecNum<=86) then
                begin
                        iRow := iRecNum-29;
                        iCol:=8;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-68].SubItems[6]);
                end;
        end;

        PrintExcelShow;
        PrintPreview;
        CloseActiveBook;
end;

function TBusTradeYearSumFrm.Converter(constr:string):String;
var
        i:Integer;
        k:char;
        DesStr:String;
begin
        if(pos('.',constr)=0)then
        begin
                DesStr:=ConStr;
                Converter:=DesStr;
        end
        else
        begin
                i:=pos('.',constr);
                k:=constr[i+2];
                DesStr:=copy(constr,0,i+2);
                if(k >'i+2')then
                begin
                        Converter:=floattostr(strtofloat(DesStr)+0.01);
                end
                else
                begin
                        Converter:=DesStr;
                end;
        end;
end;

procedure TBusTradeYearSumFrm.initialcolumn(List:TListView);
var j:integer;
begin

   if(List=ListView1)then
   begin
        for j:=0 to 22 do
        begin
                if List.Items[j].SubItems.Count<7
                then begin
                        List.Items[j].SubItems.Add(' ');
                end else
                begin
                        List.Items[j].SubItems[2]:='';
                        List.Items[j].subItems[6]:='';
                end;
        end;
   end;

   if(List=ListView2)then
   begin
        for j:=0 to 20 do
        begin
                if List.Items[j].SubItems.Count<7 then
                begin
                        List.Items[j].SubItems.Add(' ');
                end else
                begin
                        List.Items[j].SubItems[2]:='';
                        List.Items[j].SubItems[6]:='';
                end;
        end;
   end;
   
end;

procedure TBusTradeYearSumFrm.BitBtn3Click(Sender: TObject);
begin
        initialcolumn(ListView1);
        initialcolumn(ListView2);
        BusTradeYearSumFrm.close;
end;

procedure TBusTradeYearSumFrm.FormCreate(Sender: TObject);
begin
        OpenExcelApp;
        PageControl1.ActivePage:=TabSheet2;
        PageControl1.ActivePage:=TabSheet1;
end;

procedure TBusTradeYearSumFrm.FormDestroy(Sender: TObject);
begin
        CloseExcelApp;
end;

procedure TBusTradeYearSumFrm.ComboBox3Change(Sender: TObject);
begin
               initialcolumn(ListView1);
               initialcolumn(ListView2);
end;

procedure TBusTradeYearSumFrm.ComboBox2Change(Sender: TObject);
begin
               initialcolumn(ListView1);
               initialcolumn(ListView2);
end;


procedure TBusTradeYearSumFrm.FillTimeDataToArrary();
var sqlstr:string;
j,index:integer;
begin
        sqlstr:='SELECT b.序号,a.指标代码, a.'+combobox1.Text+' as 时点值 FROM 公交行业统计年报_z as a ,'+
                ' 公交行业统计年报指标定义表 as b '+' where  a.城市代码='+''''+'022'+''''+
                ' and a.统计年份='+Combobox2.Text+' and a.指标代码=b.指标代码'+
                ' and b.生成类型='+''''+'时点'+''''+' order by a.指标代码';

        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;

        for j:=1 to ADOQuery1.RecordCount do
        begin
                index:=ADOQuery1.fieldbyname('序号').asinteger;
                vals[index]:=Converterpercent(ADOQuery1.fieldbyname('时点值').asstring);
                ADOQuery1.Next;
        end;
end;


procedure TBusTradeYearSumFrm.FillSumDataToArrary();
var sqlstr:string;
j,index:integer;
begin
        sqlstr:='SELECT b.序号,a.指标代码, sum(a.'+combobox1.text+') AS 期间汇总 FROM 公交行业统计年报_z as a ,'+
                ' 公交行业统计年报指标定义表 as b '+' where  a.城市代码='+''''+'022'+''''+
                ' and a.统计年份 between '+Combobox3.Text +' and '+Combobox2.Text+' and a.指标代码=b.指标代码'+
                ' and b.生成类型='+''''+'总和'+''''+' GROUP BY a.指标代码, b.序号';

        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;

        for j:=1 to ADOQuery1.RecordCount do
        begin
                index:=ADOQuery1.fieldbyname('序号').asinteger;
                vals[index]:=converterpercent(ADOQuery1.fieldbyname('期间汇总').asstring);
                ADOQuery1.Next;
        end;
end;

procedure TBusTradeYearSumFrm.FillAverageData();
var YearNum:integer;
Num:single;
begin
        YearNum:=strtoint(Combobox2.text)-strtoint(Combobox3.text)+1;
        Num:=strtofloat(ListView2.items[5].subitems[6])/YearNum;
        ListView2.items[5].subitems[6]:=Converter(floattostr(Num));
end;

procedure TBusTradeYearSumFrm.ClearArrary();
var i:integer;
begin
        for i:=1 to 86 do
        begin
                vals[i]:='0';
        end;
end;

procedure TBusTradeYearSumFrm.FillDataTolist();
var i:integer;
begin
        for i:=1 to 86 do
        begin
                if(i>=1)and (i<=23)then
                begin
                        if(vals[i]='')then
                                ListView1.Items[i-1].SubItems[2]:='0'
                        else
                                ListView1.Items[i-1].subitems[2]:=vals[i];
                end;

                if(i>=24)and (i<=46)then
                begin
                        if(vals[i]='')then
                                ListView1.Items[i-24].subitems[6]:='0'
                        else
                                ListView1.Items[i-24].subitems[6]:=vals[i];
                end;

                if(i>=47)and (i<=67)then
                begin
                        if(vals[i]='')then
                                ListView2.Items[i-47].subitems[2]:='0'
                        else
                                ListView2.Items[i-47].subitems[2]:=vals[i];
                end;

                if(i>=68)and (i<=86)then
                begin
                        if(vals[i]='')then
                                ListView2.Items[i-68].SubItems[6]:='0'
                        else
                                ListView2.Items[i-68].SubItems[6]:=vals[i];
                end;
        end;
end;

procedure TBusTradeYearSumFrm.WriteSpecialIndex();
var  IndexValue1,IndexValue2,IndexValue:single;
     Num:single;
begin
        IndexValue1:=strtofloat(ListView1.Items[5].SubItems[6]);
        IndexValue2:=strtofloat(ListView1.Items[10].SubItems[6])+strtofloat(ListView1.Items[17].SubItems[6]);
        IndexValue:=IndexValue2+strtofloat(ListView1.Items[21].SubItems[6]);
        if (IndexValue1=0)or (IndexValue=0)then
        begin
                ListView1.Items[22].SubItems[6]:='0';
        end else
        begin
                Num:=IndexValue1/IndexValue*100;
                ListView1.Items[22].SubItems[6]:=ConverterPercent(floattostr(Num));
        end;

        IndexValue1:=strtofloat(ListView2.Items[13].SubItems[2]);
        IndexValue2:=strtofloat(ListView1.Items[4].SubItems[6]);
        if(IndexValue1=0)or (IndexValue2=0)then
        begin
                ListView2.Items[14].SubItems[2]:='0';
        end else
        begin
                Num:=(IndexValue1/IndexValue2)*100;
                ListView2.Items[14].SubItems[2]:=ConverterPercent(floattostr(Num));
        end;
end;

procedure TBusTradeYearSumFrm.ComboBox1Change(Sender: TObject);
begin
               initialcolumn(ListView1);
               initialcolumn(ListView2);
end;

procedure TBusTradeYearSumFrm.FormShow(Sender: TObject);
begin
        BitBtn2.Enabled:=false;
        PageControl1.ActivePage:=TabSheet1;
        PageControl1.ActivePage:=TabSheet2;
end;

end.