query3unit.~pas

公交行业的管理系统

unit Query3Unit;

interface

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

type
  TQuery3Form = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    TabSheet4: TTabSheet;
    ListView3: TListView;
    ListView1: TListView;
    ListView4: TListView;
    ListView2: TListView;
    Panel1: TPanel;
    Label1: TLabel;
    BitBtn1: TBitBtn;
    ComboBox1: TComboBox;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    ADOQuery2DSDesigner: TWideStringField;
    ADOQuery2DSDesigner2: TSmallintField;
    ADOQuery2DSDesigner3: TWideStringField;
    ADOQuery2DSDesigner4: TWideStringField;
    ADOQuery2DSDesigner5: TWideStringField;
    ADOQuery2DSDesigner6: TWideStringField;
    ADOQuery3: TADOQuery;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
  private
    { Private declarations }
  public
    procedure AddPRO1(curPos:string;InputStr:string);
    procedure AddPRO2(curPos:string;InputStr:string);
    procedure AddService(curPos:string;Service:string;ServiceName:string);
    procedure AddMoney(curPos:string;Service:string;ServiceName:string;Money:single);
    procedure AddColSum;
    procedure AddRowSum(curListView:TListView;begincol,endcol,beginrow,endrow,desirow:integer);
    procedure InitChildItem(curListView:TListView);
    procedure ClearChildItem(curListView:TListView);
    procedure AddAllCount(UnitName:string);
    procedure AddSeviceCount(curPos:string);
    procedure AddSeviceSum(curPos:string;Money:single);
  end;

var
  Query3Form: TQuery3Form;

implementation

uses PrintInfoUnit, PrintProc, BusTradeDataModuleUnit, PrintProc2;

{$R *.dfm}

procedure TQuery3Form.InitChildItem(curListView:TListView);
var
i:integer;
j:integer;
begin
         for i:=0 to curListView.Items.Count-1 do
         begin
             for j:=2 to curListView.Columns.Count-1 do
             curListView.Items[i].SubItems.Add('0');
         end;
end;
procedure TQuery3Form.ClearChildItem(curListView:TListView);
var
i,j:integer;
begin
        for i:=0 to curListView.Items.Count-1 do
        begin
             for j:=2 to curListView.Columns.Count-1 do
             curListView.Items[i].SubItems[j-1]:='0';
        end;
end;
procedure TQuery3Form.FormCreate(Sender: TObject);
begin
        InitChildItem(ListView2);
        InitChildItem(ListView4);
        InitChildItem(ListView1);
        InitChildItem(ListView3);
end;

procedure TQuery3Form.BitBtn1Click(Sender: TObject);
begin
        try
                if (StrToInt(ComboBox1.Text)<2000) or (StrToInt(ComboBox1.Text)>2050) then
                begin
                        MessageBox(self.Handle,pchar('年份输入界限不对！'),pchar('消息'),MB_OK);
                end;
        except
                 MessageBox(self.Handle,pchar('年份输入不对！'),pchar('消息'),MB_OK);
                 exit;
        end;

        
        ClearChildItem(ListView2);
        ClearChildItem(ListView4);
        ClearChildItem(ListView1);
        ClearChildItem(ListView3);
        ADOQuery2.SQL.Clear;
        ADOQuery2.SQL.Add('SELECT 单位代码, 统计年份, 营业状况, 企业规模, [企业(单位)登记注册类型], 单位坐落区域'+
                          ' FROM 公用事业单位基本情况表 where 城市代码='+''''+'022'+''''+' and  统计年份='+ComboBox1.Text);
        ADOQuery2.Open;
        ADOQuery2.First;
        while not ADOQuery2.Eof do
        begin
             AddAllCount(ADOQuery2.FieldByName('单位坐落区域').AsString);
             AddPRO1(ADOQuery2.FieldByName('单位坐落区域').AsString,
                      ADOQuery2.FieldByName('企业(单位)登记注册类型').AsString);
             AddPRO2(ADOQuery2.FieldByName('单位坐落区域').AsString,
                          ADOQuery2.FieldByName('营业状况').AsString);
             AddPRO2(ADOQuery2.FieldByName('单位坐落区域').AsString,
                         ADOQuery2.FieldByName('企业规模').AsString);
             ADOQuery2.Next;
        end;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add('SELECT b.企业代码, b.统计年份, 营业状况, 企业规模, [企业(单位)登记注册类型], 主要业务活动, 企业注册资本金, 单位坐落区域, b.[公共汽车、电车], b.地铁, b.轻轨, b.轮渡'+
                          ' FROM 公用事业单位基本情况表 AS a, 主要业务活动表 AS b'+
                          ' WHERE a.城市代码='+''''+'022'+''''+' and  a.统计年份 = b.统计年份  AND a.单位代码 = b.企业代码');
        ADOQuery1.Open;
        ADOQuery1.First;
        while not ADOQuery1.Eof do
        begin
              AddService(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         ADOQuery1.FieldByName('公共汽车、电车').AsString,'公共汽车、电车');
              AddService(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         ADOQuery1.FieldByName('地铁').AsString,'地铁');
              AddService(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         ADOQuery1.FieldByName('轻轨').AsString,'轻轨');
              AddService(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         ADOQuery1.FieldByName('轮渡').AsString,'轮渡');
              AddMoney(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         '公共汽车、电车',
                         ADOQuery1.FieldByName('公共汽车、电车').asstring,
                         ADOQuery1.FieldByName('企业注册资本金').AsInteger);
              AddMoney(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         '地铁',
                         ADOQuery1.FieldByName('地铁').asstring,
                         ADOQuery1.FieldByName('企业注册资本金').AsInteger);
              AddMoney(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         '轻轨',
                         ADOQuery1.FieldByName('轻轨').asstring,
                         ADOQuery1.FieldByName('企业注册资本金').AsInteger);
              AddMoney(ADOQuery1.FieldByName('单位坐落区域').AsString,
                         '轮渡',
                         ADOQuery1.FieldByName('轮渡').asstring,
                         ADOQuery1.FieldByName('企业注册资本金').AsInteger);
              ADOQuery1.Next;
        end;
        ADOQuery3.SQL.Clear;
        ADOQuery3.SQL.Add('SELECT 单位代码, 统计年份,企业注册资本金, 单位坐落区域'+
                          ' FROM 公用事业单位基本情况表 where 城市代码='+''''+'022'+''''+' and  统计年份='+ComboBox1.Text
                          +' and 单位代码 in (select 企业代码 from 主要业务活动表 where 统计年份='+ComboBox1.Text
                          +')');
        ADOQuery3.Open;
        ADOQuery3.First;
        while not  ADOQuery3.eof do
        begin
              AddSeviceCount(ADOQuery3.FieldByName('单位坐落区域').AsString);
              AddSeviceSum(ADOQuery3.FieldByName('单位坐落区域').AsString,
                           ADOQuery3.FieldByName('企业注册资本金').asfloat);
              ADOQuery3.next;
        end;
        AddColSum;
        AddRowSum(ListView2,1,11,7,18,6);
        AddRowSum(ListView4,1,10,7,18,6);
        AddRowSum(ListView3,1,11,1,5,0);
        AddRowSum(ListView3,1,11,7,11,6);
        AddRowSum(ListView1,1,10,1,5,0);
        AddRowSum(ListView1,1,10,7,11,6);
end;

procedure TQuery3Form.BitBtn2Click(Sender: TObject);
var
        iRow:Integer;
        iCol:Integer;
begin

        if PrnInfoFrm.ShowModal=mrOK then
        begin

                PrintInit(ExtractFilePath(application.exeName),'BusQuery3');

                for iRow:=5 to 23 do
                for iCol:=3 to 12 do
                WriteCell(iRow,iCol,ListView2.Items[iRow-5].SubItems[iCol-2]);
                for iRow:=29 to 47 do
                for iCol:=3 to 12 do
                WriteCell(iRow,iCol,ListView4.Items[iRow-29].SubItems[iCol-2]);
                for iRow:=54 to 70 do
                for iCol:=3 to 13 do
                WriteCell(iRow,iCol,ListView3.Items[iRow-54].SubItems[iCol-2]);
                for iRow:=78 to 94 do
                for iCol:=3 to 12 do
                WriteCell(iRow,iCol,ListView1.Items[iRow-78].SubItems[iCol-2]);

                WriteCell(24,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(24,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(24,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

                WriteCell(48,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(48,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(48,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

                WriteCell(71,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(71,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(71,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

                WriteCell(95,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(95,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(95,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

                WriteCell(2,7,ComboBox1.text+'年');
                WriteCell(26,7,ComboBox1.text+'年');
                WriteCell(51,7,ComboBox1.text+'年');
                WriteCell(75,7,ComboBox1.text+'年');

                PrintExcelShow;
                PrintPreview;
                CloseActiveBook;                
        end;
end;
procedure TQuery3Form.AddSeviceCount(curPos:string);
var
i:integer;
begin
        for i:=0 to ListView2.Columns.Count-1 do
        begin
              if(ListView2.Columns[i].Caption=curPos)then
              begin
                      ListView2.Items[1].SubItems[i-1]:=IntToStr(StrToInt(ListView2.Items[1].SubItems[i-1])+1);
                      exit;
              end;
        end;
        for i:=0 to ListView4.Columns.Count-1 do
        begin
              if(ListView4.Columns[i].Caption=curPos)then
              begin
                      ListView4.Items[1].SubItems[i-1]:=IntToStr(StrToInt(ListView4.Items[1].SubItems[i-1])+1);
                      exit;
              end;