unit NameBookScaleQueryUnit;

interface

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

type
  TNameBookScaleQuery = class(TForm)
    Panel1: TPanel;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    Panel2: TPanel;
    ListView1: TListView;
    ListView2: TListView;
    ListView3: TListView;
    ComboBox1: TComboBox;
    Label1: TLabel;
    BitBtn1: TBitBtn;
    ADOQuery1: TADOQuery;
    Label2: TLabel;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    procedure BitBtn1Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
  private
    { Private declarations }
  public
    function FindRow(LView:TListView;input:String;sPos:integer;ePos:integer):integer;
    function FindCol(input:String):integer;
    procedure StandardInput(LView:TListView;Spos:integer;Epos:integer;Sfield:string);
    procedure sumCa(Spos:integer;Epos:integer;LView:TListView);
    procedure sumCa2(Spos:integer;Epos:integer;LView:TListView);
    procedure moneySum(input:string;Row:integer);
    procedure sumXJ(Spos:integer;Epos:integer;LView:TListView);
    procedure sumXJ2(Spos:integer;Epos:integer;LView:TListView);
    procedure ZongSum(Col:integer;Input:string);
    { Public declarations }
  end;

var
  NameBookScaleQuery: TNameBookScaleQuery;

implementation

uses BusTradeDataModuleUnit, PrintProc, PrintInfoUnit, PrintProc2;

{$R *.dfm}

function TNameBookScaleQuery.FindRow(LView:TListView;input:String;sPos:integer;ePos:integer):integer;
var
        temp:string;
        k:integer;
begin
        for k:=sPos to ePos do
        begin
                temp:=copy(LView.Items[k].Caption,4,Length(LView.Items[k].Caption)-1);
                if temp=input then
                begin
                        FindRow:=k;
                        exit;
                end;
        end;
        FindRow:=-1;
end;

function TNameBookScaleQuery.FindCol(input:String):integer;
begin
        if input='特大型' then
        begin
                FindCol:=2;
                exit;
        end;
        if input='大型' then
        begin
                FindCol:=3;
                exit;
        end;
        if input='中型' then
        begin
                FindCol:=4;
                exit;
        end;
        if input='小型' then
        begin
                FindCol:=5;
                exit;
        end;
        if input='其他' then
        begin
                FindCol:=6;
                exit;
        end;
        FindCol:=-1;
end;

procedure TNameBookScaleQuery.StandardInput(LView:TListView;Spos:integer;Epos:integer;Sfield:string);
var
        i,j,iRow:integer;
        sString:string;
        scale: array [0..4] of string;
begin
        scale[0]:='特大型';
        scale[1]:='大型';
        scale[2]:='中型';
        scale[3]:='小型';
        scale[4]:='其他';
        j:=2;
        for i:=0 to 4 do
        begin
                sString:='select '+Sfield+' AS 字段類型, count('+Sfield+') AS 合計';
                sString:=sString+' FROM 公用事業單位基本情況表 WHERE 企業規模='+''''+scale[i]+'''';
                sString:=sString+' AND 統計年份='+ComboBox1.Text+' and 城市代碼='+''''+'022'+''''+' GROUP BY '+Sfield;

                ADOQuery1.Close;
                ADOQuery1.SQL.Clear;
                ADOQuery1.SQL.Add(sString);
                ADOQuery1.Open;
                ADOQuery1.First;

                while (not ADOQuery1.Eof) do
                begin
                        if FindRow(LView,ADOQuery1.fieldbyname('字段類型').AsString,Spos,Epos)>0 then
                                iRow:=FindRow(LView,ADOQuery1.fieldbyname('字段類型').AsString,Spos,Epos)
                        else begin
                                showmessage('名錄庫中有錯誤！請檢查名錄庫中的數據！');
                                exit;
                        end;
                        LView.Items[iRow].SubItems[j]:=ADOQuery1.fieldbyname('合計').AsString;
                        ADOQuery1.Next;
                end;
                j:=j+1;
        end;
end;

procedure TNameBookScaleQuery.sumCa(Spos:integer;Epos:integer;LView:TListView);
var
        i,j,temp:integer;
begin
        for j:=Spos to Epos do
        begin
                temp:=0;
                for i:=2 to 6 do
                        temp:=temp+StrToInt(LView.Items[j].SubItems[i]);
                LView.Items[j].SubItems[1]:=IntToStr(temp);
        end;
end;

procedure TNameBookScaleQuery.sumCa2(Spos:integer;Epos:integer;LView:TListView);
var
        i,j:integer;
        temp:single;
begin
        for j:=Spos to Epos do
        begin
                temp:=0;
                for i:=2 to 6 do
                        temp:=temp+StrToFloat(LView.Items[j].SubItems[i]);
                LView.Items[j].SubItems[1]:=FloatToStr(temp);
        end;
end;

procedure TNameBookScaleQuery.moneySum(input:string;Row:integer);
var
        i:integer;
        sString:String;
        scale: array [0..4] of string;
begin
        scale[0]:='特大型';
        scale[1]:='大型';
        scale[2]:='中型';
        scale[3]:='小型';
        scale[4]:='其他';

        for i:=0 to 4 do
        begin
                sString:='select b.'+input+' as 類型, sum(a.企業注冊資本金) AS 合計';
                sString:=sString+' FROM 公用事業單位基本情況表 AS a, 主要業務活動表 AS b';
                sString:=sString+' WHERE a.統計年份='+ComboBox1.Text+' and b.統計年份=a.統計年份';
                sString:=sString+' and b.'+input+'='+''''+'1'+''''+' and a.城市代碼='+''''+'022'+'''';
                sString:=sString+' and a.企業規模='+''''+scale[i]+''''+' and a.單位代碼=b.企業代碼 GROUP BY b.'+input;

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

                if (ADOQuery1.fieldbyname('合計').AsString<>'') then
                        ListView3.Items[Row].SubItems[i+2]:=ADOQuery1.fieldbyname('合計').AsString
        end;
end;

procedure TNameBookScaleQuery.sumXJ(Spos:integer;Epos:integer;LView:TListView);{計算按類型分的各個規模小記XJ}
var
        i,j,temp:integer;
begin
        for i:=2 to 6 do
        begin
                temp:=0;
                for j:=Spos to Epos do
                        temp:=temp+StrToInt(LView.Items[j].SubItems[i]);
                LView.Items[Spos-1].SubItems[i]:=IntToStr(temp);
        end;
end;