unit Query;

interface

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

type
  TQueryForm = class(TForm)
    Panel1: TPanel;
    Label20: TLabel;
    ComboBox1: TComboBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    //BitBtn3: TBitBtn;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Panel3: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    ListView1: TListView;
    TabSheet2: TTabSheet;
    Panel4: TPanel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    ListView2: TListView;
    TabSheet3: TTabSheet;
    Panel5: TPanel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    Label19: TLabel;
    ListView3: TListView;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    BitBtn3: TBitBtn;
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    tit1 : array [0..19] of string;
    tit2 : array [0..20] of string;
    tit3 : array [0..10] of string;
    colcount : integer;
    
    procedure InitListViews();
    procedure SumAll();
    procedure DealSumColumn();                 //處理合并列

    procedure FillinRowOne();
    procedure AnnexOne(stat:integer; stp:integer; num:integer ; aim :integer);    //把listview num中的從stat to stp add to aim
    function GetIndex(str: string; num : integer) :integer; //在第n個Listview中找caption is str 的index
  end;                                                      //return -1 when failed

var
  QueryForm: TQueryForm;

implementation

uses  BusTradeDataModuleUnit, PrintInfoUnit, PrintProc;

{$R *.dfm}
procedure TQueryForm.DealSumColumn();
var
        sqlstr1,xname: string;
        xindex : integer;
begin
        sqlstr1:= 'select count(單位代碼) as amot,[企業(單位)登記注冊類型] as type from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 城市代碼='+''''+'022'+''''+' group by [企業(單位)登記注冊類型]';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ( not AdoQuery1.eof) do
        begin
                xname := AdoQuery1.fieldbyname('type').asstring;
                xindex:= GetIndex(xname,1);
                listview1.items.item[xindex].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
                AdoQuery1.Next;
        end;

        sqlstr1:= 'select count(單位代碼) as amot,單位坐落區域 as type from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 城市代碼='+''''+'022'+''''+' group by 單位坐落區域';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ( not AdoQuery1.eof) do
        begin
                xname := AdoQuery1.fieldbyname('type').asstring;
                xindex:= GetIndex(xname,1);
                if (xindex<>-1) then
                        listview1.items.item[xindex].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring
                else
                begin
                        xindex:= GetIndex(xname,2);
                        listview2.items.item[xindex].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring
                end;
                AdoQuery1.Next;
        end;

        sqlstr1:= 'select count(單位代碼) as amot,營業狀況 as type from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 城市代碼='+''''+'022'+''''+' group by 營業狀況';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ( not AdoQuery1.eof) do
        begin
                xname := AdoQuery1.fieldbyname('type').asstring;
                xindex:= GetIndex(xname,2);
                listview2.items.item[xindex].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
                AdoQuery1.Next;
        end;

        sqlstr1:= 'select count(單位代碼) as amot,企業規模 as type from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 城市代碼='+''''+'022'+''''+' group by 企業規模';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ( not AdoQuery1.eof) do
        begin
                xname := AdoQuery1.fieldbyname('type').asstring;
                xindex:= GetIndex(xname,3);
                listview3.items.item[xindex].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
                AdoQuery1.Next;
        end;

        //----------------- 企業注冊資本金的合計列 ----------------------//
        listview3.items.item[7].subitems[1]:= listview3.items.item[7].subitems[2];
        listview3.items.item[8].subitems[1]:= listview3.items.item[8].subitems[3];
        listview3.items.item[9].subitems[1]:= listview3.items.item[9].subitems[4];
        listview3.items.item[10].subitems[1]:= listview3.items.item[10].subitems[5];
end;


procedure TQueryForm.FillinRowOne();
begin
        listview1.Items.Item[0].SubItems[1] := listview1.Items.Item[1].SubItems[1];
        listview1.Items.Item[0].SubItems[2] := listview1.Items.Item[1].SubItems[2];
        listview1.Items.Item[0].SubItems[3] := listview1.Items.Item[1].SubItems[3];
        listview1.Items.Item[0].SubItems[4] := listview1.Items.Item[1].SubItems[4];
        listview1.Items.Item[0].SubItems[5] := listview1.Items.Item[1].SubItems[5];
end;

procedure TQueryForm.SumAll();
var
        rowindex : integer;
begin
        for rowindex := 0 to 19 do
        begin
                listview1.Items.Item[rowindex].SubItems[1] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[2])+StrToInt(listview1.Items.Item[rowindex].SubItems[3])+StrToInt(listview1.Items.Item[rowindex].SubItems[4])+StrToInt(listview1.Items.Item[rowindex].SubItems[5]));
        end;
        for rowindex := 0 to 20 do
        begin
                listview2.Items.Item[rowindex].SubItems[1] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[2])+StrToInt(listview2.Items.Item[rowindex].SubItems[3])+StrToInt(listview2.Items.Item[rowindex].SubItems[4])+StrToInt(listview2.Items.Item[rowindex].SubItems[5]));
        end;
        for rowindex := 0 to 5 do
        begin
                listview3.Items.Item[rowindex].SubItems[1] := IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[2])+StrToInt(listview3.Items.Item[rowindex].SubItems[3])+StrToInt(listview3.Items.Item[rowindex].SubItems[4])+StrToInt(listview3.Items.Item[rowindex].SubItems[5]));
        end;
        for rowindex := 6 to 10 do
        begin
                if (listview3.Items.Item[rowindex].SubItems[2]='') then listview3.Items.Item[rowindex].SubItems[2]:='0';
                if (listview3.Items.Item[rowindex].SubItems[3]='') then listview3.Items.Item[rowindex].SubItems[3]:='0';
                if (listview3.Items.Item[rowindex].SubItems[4]='') then listview3.Items.Item[rowindex].SubItems[4]:='0';
                if (listview3.Items.Item[rowindex].SubItems[5]='') then listview3.Items.Item[rowindex].SubItems[5]:='0';
                listview3.Items.Item[rowindex].SubItems[1] := FloatToStr(StrToFloat(listview3.Items.Item[rowindex].SubItems[2])+StrToFloat(listview3.Items.Item[rowindex].SubItems[3])+StrToFloat(listview3.Items.Item[rowindex].SubItems[4])+StrToFloat(listview3.Items.Item[rowindex].SubItems[5]));
        end;
end;

procedure TQueryForm.AnnexOne(stat:integer; stp:integer; num:integer ; aim :integer);
var
        ibus,irail,ihigh,iship,itmp : integer;
begin
        ibus := 0; irail := 0; ihigh := 0; iship := 0;
        case num of
         1 :
            begin
                for itmp := stat to  stp do
                begin
                        ibus := StrToInt(listview1.Items.Item[itmp].SubItems[2]) + ibus;
                        irail:= StrToInt(listview1.Items.Item[itmp].SubItems[3]) + irail;
                        ihigh:= StrToInt(listview1.Items.Item[itmp].SubItems[4]) + ihigh;
                        iship:= StrToInt(listview1.Items.Item[itmp].SubItems[5]) + iship;
                end;
                listview1.Items.Item[aim].SubItems[2] := IntToStr(ibus);
                listview1.Items.Item[aim].SubItems[3] := IntToStr(irail);
                listview1.Items.Item[aim].SubItems[4] := IntToStr(ihigh);
                listview1.Items.Item[aim].SubItems[5] := IntToStr(iship);
            end;
         2 :
            begin
                for itmp := stat to  stp do
                begin
                        ibus := StrToInt(listview2.Items.Item[itmp].SubItems[2]) + ibus;
                        irail:= StrToInt(listview2.Items.Item[itmp].SubItems[3]) + irail;
                        ihigh:= StrToInt(listview2.Items.Item[itmp].SubItems[4]) + ihigh;