bustrademonsumunit.~pas

公交行业的管理系统

unit BusTradeMonSumUnit;

interface

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

type
  TBusTradeMonSumFrm = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    ListView1: TListView;
    TabSheet2: TTabSheet;
    ListView2: TListView;
    ADOQuery1: TADOQuery;
    ADOQuery3: TADOQuery;
    Panel1: TPanel;
    Panel2: TPanel;
    Label4: TLabel;
    Label5: TLabel;
    ComboBox1: TComboBox;
    Panel5: TPanel;
    Label6: TLabel;
    Panel3: TPanel;
    Label2: TLabel;
    ComboBox3: TComboBox;
    Panel6: TPanel;
    Label8: TLabel;
    Panel4: TPanel;
    Label9: TLabel;
    ComboBox5: TComboBox;
    Panel8: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    Panel7: TPanel;
    Label10: TLabel;
    Label7: TLabel;
    ComboBox2: TComboBox;
    Edit1: TEdit;
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure ComboBox5Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    procedure WriteSpecialIndex(IndexName1:string;IndexName2:string);
    procedure initialcolumn(List:TListView);
  public
    { Public declarations }
  end;

var
  BusTradeMonSumFrm: TBusTradeMonSumFrm;

implementation

uses PrintProc, BusTradeDataModuleUnit, PrintInfoUnit;

{$R *.dfm}

procedure TBusTradeMonSumFrm.BitBtn1Click(Sender: TObject);
var sqlstr:string ;
         j:integer;
      iRow:integer;
iExistFlag:integer;
begin
        if(combobox1.text='') and (combobox3.text='')and (combobox5.text='')then
        begin
                showmessage('请选择汇总的基期和报告期！');
                BitBtn2.Enabled:=false;
                exit;
        end;

        if(combobox1.text='')then
        begin
                showmessage('请选择年份！');
                exit;
        end;

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

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

        if(length(combobox1.Text)<>4)then
        begin
                showmessage('对不起，您所输入的年份不正确！');
                exit;
        end  else
        begin
                try
                        strtoint(combobox1.text);
                except
                        showmessage('对不起，您所输入的年份不正确！');
                        exit;
                end;
        end;

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

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

        if ADOQuery1.RecordCount<=0 then
        begin
                showmessage('不存在'+combobox3.text+'月到'+combobox5.text+'月之间的数据！请重新选择！');
                BitBtn2.Enabled:=false;
                exit;
        end
        else
        begin
                iExistFlag:=0;
                ADOQuery1.First;
                for  iRow:= 0 to ADOQuery1.RecordCount-1 do
                begin
                        if ADOQuery1.FieldByName(combobox2.text).AsString<>'' then
                        begin
                                iExistFlag:=1;
                                break;
                        end;
                        ADOQuery1.Next;
                end;

                if iExistFlag=0 then
                begin
                        ShowMessage('对不起！不存在'+combobox3.Text+'月与'+combobox5.text+'月的数据！');
                        BitBtn2.enabled:=false;
                        exit;
                end;

        end;

        //----------判断最大月份是否有数据--------------------------------------

        sqlstr:='select 指标代码,'+combobox2.text+' from 公交行业统计月报_z where 统计年份='
                +combobox1.Text+' and 统计月份='
                +combobox5.Text+' order by 指标代码';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;

        //---------------若存在,向Listview中前25项指标填写数据
        if(ADOQuery1.RecordCount<=0)then
        begin
                showmessage('对不起，不存在'+combobox5.Text+'月数据，无法汇总！');
                BitBtn2.Enabled:=false;
                exit;
        end  else
        begin
                BitBtn2.Enabled:=true;
                ADOQuery1.First;
                for j:=0 to 24 do
                begin
                        if(j>=0)and (j<=19)then
                        begin
                                if(ADOQuery1.fieldbyname(combobox2.text).asstring='')then
                                begin
                                        ListView1.Items[j].SubItems[2]:='0';
                                end else
                                        ListView1.Items[j].SubItems[2]:=Converter(ADOQuery1.fieldbyname(combobox2.text).asstring);
                        end;

                        if(j>=20)and (j<=24)then
                        begin
                                if(ADOQuery1.FieldByName(combobox2.text).AsString='')then
                                begin
                                        ListView1.Items[j-20].SubItems[6]:='0';
                                end else
                                        ListView1.Items[j-20].SubItems[6]:=Converter(ADOQuery1.fieldbyname(combobox2.text).asstring);
                        end;
                        ADOQuery1.Next;

                end;
        end;

        //填写后面的数据
        sqlstr:='SELECT 指标代码, sum('+combobox2.text+') as 期间汇总  from 公交行业统计月报_z '
                        +' where 城市代码='+''''+'022'+''''+' and 统计年份='+Combobox1.text
                        +' and 统计月份 between '+Combobox3.Text +' and '+Combobox5.Text +' GROUP BY 指标代码';

        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;
        
        //对原来ListView中的数据进行刷新
        BitBtn2.enabled:=true;

        for j:=0 to ADOQuery1.RecordCount-1 do
        begin
                if (ADOQuery1.eof) then exit;

                   if (j>=25) and (j<=39) then
                begin
                        if(ADOQuery1.fieldbyname('期间汇总').Asstring='')then
                        begin
                                ListView1.Items[j-20].SubItems[6]:='0';
                        end else
                                ListView1.Items[j-20].SubItems[6]:=Converter(ADOQuery1.fieldbyname('期间汇总').Asstring);
                end;

                if (j>=40) and (j<=60) then
                begin
                        if(ADOQuery1.fieldbyname('期间汇总').Asstring='')then
                        begin
                                ListView2.Items[j-40].SubItems[2]:='0';
                        end else
                                ListView2.Items[j-40].SubItems[2]:=Converter(ADOQuery1.fieldbyname('期间汇总').Asstring);
                end;

                if (j>=61) and (j<=80) then
                begin
                        if(ADOQuery1.fieldbyname('期间汇总').Asstring='')then
                        begin
                                ListView2.Items[j-61].SubItems[6]:='0';
                        end else
                                ListView2.Items[j-61].SubItems[6]:=Converter(ADOQuery1.fieldbyname('期间汇总').Asstring);
                end;

                ADOQuery1.Next;
end;
        WriteSpecialIndex('G65','G66');
        WriteSpecialIndex('G68','G69');
        WriteSpecialIndex('G72','G73');
        WriteSpecialIndex('G54','G77');
        WriteSpecialIndex('G75','G76');

end;
procedure TBusTradeMonSumFrm.WriteSpecialIndex(IndexName1:string;IndexName2:string);
var IndexValue11,IndexValue21:single;
IndexValue31:single;
i:integer;
begin
        ADOQuery1.locate('指标代码',IndexName1,[]);
        IndexValue11:=ADOQuery1.fieldbyname('期间汇总').asfloat;

        ADOQuery1.locate('指标代码',IndexName2,[]);
        IndexValue21:=ADOQuery1.fieldbyname('期间汇总').asfloat;

        if (IndexValue11=0)or(IndexValue21=0)then
        begin
                ListView2.Items[1].SubItems[6]:='0';
                ListView2.Items[4].SubItems[6]:='0';
                ListView2.Items[7].SubItems[6]:='0';
                ListView2.Items[10].SubItems[6]:='0';
                ListView2.Items[14].SubItems[6]:='0';
        end else
        begin
                IndexValue31:=(IndexValue11/IndexValue21)*100;
                if((IndexName1='G65') and (IndexName2='G66'))then
                begin
                        IndexValue31:=IndexValue31*100;
                        ListView2.Items[1].SubItems[6]:=Converter(floattostr(IndexValue31));
                end;
                if((IndexName1='G68') and (IndexName2='G69'))then
                begin
                        ListView2.Items[4].SubItems[6]:=Converter(floattostr(IndexValue31));
                end;
                if((IndexName1='G72') and (IndexName2='G73'))then
                begin
                        ListView2.Items[7].SubItems[6]:=Converter(floattostr(IndexValue31));
                end;
                if((IndexName1='G54') and (IndexName2='G77'))then
                begin
                        ListView2.Items[14].SubItems[6]:=Converter(floattostr(IndexValue31/100));
                end;
                if((IndexName1='G75') and (IndexName2='G76'))then
                begin
                        ListView2.Items[10].SubItems[6]:=Converter(floattostr(IndexValue31));
                end;
        end;
end;

procedure TBusTradeMonSumFrm.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),'BusTradeMonStaTab');
        for i:=0 to 1 do
        begin
                WriteCell(4+i*30,1,'汇总单位:  '+edit1.Text);
                WriteCell(3+i*30,3,combobox1.text+'年'+combobox3.text+' 月至 '+combobox5.text+' 月');
                WriteCell(28+i*31,1,'统计负责人:' + PrnInfoFrm.StatMainPerson);
                WriteCell(28+i*31,4, PrnInfoFrm.StatPerson);
                WriteCell(28+i*31,6,'填报日期:' + DateToStr(PrnInfoFrm.StatDate));
        end;

        for iRecNum := 1 to 80 do
        begin
           if (iRecNum<=20)then
           begin
                iRow := iRecNum+7;
                iCol := 4;
                WriteCell(iRow,iCol,ListView1.Items[iRecNum-1].SubItems[2]);
           end;
           if (iRecNum>=21) and (iRecNum<=40) then
           begin
                iRow := iRecNum-13;
                iCol:=8;
                WriteCell(iRow,iCol,ListView1.Items[iRecNum-21].SubItems[6]);
           end;
           if (iRecNum>=41) and (iRecNum<=61) then
           begin
                iRow := iRecNum-3;
                iCol:=4;
                WriteCell(iRow,iCol,ListView2.Items[iRecNum-41].SubItems[2]);
           end;
           if (iRecNum>=62) and (iRecNum<=80) then
           begin
                iRow := iRecNum-24;
                iCol:=8;
                WriteCell(iRow,iCol,ListView2.Items[iRecNum-62].SubItems[6]);
           end;
        end;
        PrintExcelShow;
        PrintPreview;
        CloseActiveBook;

end;

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

   if(List=ListView1)then
   begin
     for j:=0 to 19 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 TBusTradeMonSumFrm.BitBtn3Click(Sender: TObject);
begin
        BusTradeMonSumFrm.Close;
end;

procedure TBusTradeMonSumFrm.FormCreate(Sender: TObject);
begin
        OpenExcelApp;
end;

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

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

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

procedure TBusTradeMonSumFrm.ComboBox5Change(Sender: TObject);
begin
        initialcolumn(ListView1);
        initialcolumn(ListView2);
end;

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

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

end;

end.