query.pas

公交行业的管理系统

                        iship:= StrToInt(listview2.Items.Item[itmp].SubItems[5]) + iship;
                end;
                listview2.Items.Item[aim].SubItems[2] := IntToStr(ibus);
                listview2.Items.Item[aim].SubItems[3] := IntToStr(irail);
                listview2.Items.Item[aim].SubItems[4] := IntToStr(ihigh);
                listview2.Items.Item[aim].SubItems[5] := IntToStr(iship);
            end;
         3 :
            begin
                for itmp := stat to  stp do
                begin
                        ibus := StrToInt(listview3.Items.Item[itmp].SubItems[2]) + ibus;
                        irail:= StrToInt(listview3.Items.Item[itmp].SubItems[3]) + irail;
                        ihigh:= StrToInt(listview3.Items.Item[itmp].SubItems[4]) + ihigh;
                        iship:= StrToInt(listview3.Items.Item[itmp].SubItems[5]) + iship;
                end;
                listview3.Items.Item[aim].SubItems[2] := IntToStr(ibus);
                listview3.Items.Item[aim].SubItems[3] := IntToStr(irail);
                listview3.Items.Item[aim].SubItems[4] := IntToStr(ihigh);
                listview3.Items.Item[aim].SubItems[5] := IntToStr(iship);
            end;
        end;


end;

function  TQueryForm.GetIndex(str : string; num : integer): integer;
var
        find : boolean;
        ind : integer;
begin
        find := false; ind := 0;
        case num of
          1 : while ((not find) and (ind < 20)) do
              begin
                if (str = tit1[ind])
                then
                   find := true
                else
                   ind := ind+1;
              end;
          2 : while ((not find) and (ind < 21)) do
              begin
                if (str = tit2[ind])
                then
                   find := true
                else
                   ind := ind+1;
              end;
          3 : while ((not find) and (ind < 11)) do
              begin
                if (str = tit3[ind])
                then
                   find := true
                else
                   ind := ind+1;
              end;
        end;
        if (find) then GetIndex := ind
        else    GetIndex := -1;
end;

procedure TQueryForm.InitListViews();
var
        rowindex : integer;
begin
        for rowindex := 0 to 19 do
        begin
                listview1.Items.Item[rowindex].SubItems[1] := '0';
                listview1.Items.Item[rowindex].SubItems[2] := '0';
                listview1.Items.Item[rowindex].SubItems[3] := '0';
                listview1.Items.Item[rowindex].SubItems[4] := '0';
                listview1.Items.Item[rowindex].SubItems[5] := '0';
        end;
        for rowindex := 0 to 20 do
        begin
                listview2.Items.Item[rowindex].SubItems[1] := '0';
                listview2.Items.Item[rowindex].SubItems[2] := '0';
                listview2.Items.Item[rowindex].SubItems[3] := '0';
                listview2.Items.Item[rowindex].SubItems[4] := '0';
                listview2.Items.Item[rowindex].SubItems[5] := '0';
        end;
        for rowindex := 0 to 10 do
        begin
                listview3.Items.Item[rowindex].SubItems[1] := '0';
                listview3.Items.Item[rowindex].SubItems[2] := '0';
                listview3.Items.Item[rowindex].SubItems[3] := '0';
                listview3.Items.Item[rowindex].SubItems[4] := '0';
                listview3.Items.Item[rowindex].SubItems[5] := '0';
        end;
end;

procedure TQueryForm.BitBtn2Click(Sender: TObject);
begin

        QueryForm.Close;
end;

procedure TQueryForm.BitBtn1Click(Sender: TObject);
var
        sqlstr1 : string;
        rowindex,i : integer;
        iname: string;
        ibus,irail,iship,ihigh,itmp :integer;
        colval : array [2..10] of string;      //各个列的合计值(个数)
        colcapi: array [2..10] of string;      //各个列的合计值(资本金)
begin
        if (ComboBox1.Text='') then
        begin
                showmessage('请选择查询时间！');
                exit;
        end;
        if (length(ComboBox1.Text)<>4) or (StrToInt(ComboBox1.Text)>3000) or (StrToInt(ComboBox1.Text)<1900) then
        begin
                showmessage('对不起！您输入的查询时间非法！请重新选择！');
                exit;
        end;
        if (Messagebox(self.Handle,'生成年报时间较长，请等候！', '提示', mb_OKCancel ) = IDCancel)
        then    exit;

        BitBtn1.Enabled := false;
        BitBtn2.Enabled := false;

        sqlstr1 := 'select alist.单位坐落区域 as 坐落区域,alist.[企业(单位)登记注册类型] as 经济类型,alist.营业状况 as 营业状态,alist.企业规模,';
        sqlstr1 := sqlstr1+'  blist.公共汽车、电车 as 公共汽车, blist.地铁 as 地铁, blist.轻轨 as 轻轨, blist.轮渡 as 轮渡';
        sqlstr1 := sqlstr1 + '  from 公用事业单位基本情况表 as alist, 主要业务活动表 as blist where alist.统计年份='+ComboBox1.Text+' and blist.统计年份='+ComboBox1.Text+' and alist.单位代码=blist.企业代码';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;

        while (not AdoQuery1.Eof) do
        begin
                //************************take MNS03---MNS13**************************
                iname := AdoQuery1.fieldbyname('经济类型').AsString;
                ibus := AdoQuery1.fieldbyname('公共汽车').AsInteger;
                irail := AdoQuery1.fieldbyname('地铁').AsInteger;
                iship := AdoQuery1.fieldbyname('轮渡').AsInteger;
                ihigh := AdoQuery1.fieldbyname('轻轨').AsInteger;
                rowindex := GetIndex(iname,1);
                if (ibus > 0) then listview1.Items.Item[rowindex].SubItems[2] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[2])+1);
                if (irail >0 )then listview1.Items.Item[rowindex].SubItems[3] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[3])+1);
                if (ihigh >0) then listview1.Items.Item[rowindex].SubItems[4] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[4])+1);
                if (iship >0) then listview1.Items.Item[rowindex].SubItems[5] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[5])+1);
                //**************************deal with MNS16---MNS35********************
                iname := AdoQuery1.fieldbyname('坐落区域').AsString;
                rowindex := GetIndex(iname,1);
                if (rowindex <> -1) then
                begin
                        if (ibus > 0) then listview1.Items.Item[rowindex].SubItems[2] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[2])+1);
                        if (irail >0 )then listview1.Items.Item[rowindex].SubItems[3] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[3])+1);
                        if (ihigh >0) then listview1.Items.Item[rowindex].SubItems[4] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[4])+1);
                        if (iship >0) then listview1.Items.Item[rowindex].SubItems[5] := IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[5])+1);
                end
                else
                begin
                        rowindex := GetIndex(iname,2);
                        if (ibus > 0) then listview2.Items.Item[rowindex].SubItems[2] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[2])+1);
                        if (irail >0 )then listview2.Items.Item[rowindex].SubItems[3] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[3])+1);
                        if (ihigh >0) then listview2.Items.Item[rowindex].SubItems[4] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[4])+1);
                        if (iship >0) then listview2.Items.Item[rowindex].SubItems[5] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[5])+1);
                end;
                //****************************deal with MNS37---MNS41**************************
                iname := AdoQuery1.fieldbyname('营业状态').AsString;
                rowindex := GetIndex(iname,2);
                if (ibus > 0) then listview2.Items.Item[rowindex].SubItems[2] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[2])+1);
                if (irail >0 )then listview2.Items.Item[rowindex].SubItems[3] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[3])+1);
                if (ihigh >0) then listview2.Items.Item[rowindex].SubItems[4] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[4])+1);
                if (iship >0) then listview2.Items.Item[rowindex].SubItems[5] := IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[5])+1);
                //****************************deal with MNS42---MNS47***************************
                iname := AdoQuery1.fieldbyname('企业规模').AsString;
                rowindex := GetIndex(iname,3);
                if (ibus > 0) then listview3.Items.Item[rowindex].SubItems[2] := IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[2])+1);
                if (irail >0 )then listview3.Items.Item[rowindex].SubItems[3] := IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[3])+1);
                if (ihigh >0) then listview3.Items.Item[rowindex].SubItems[4] := IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[4])+1);
                if (iship >0) then listview3.Items.Item[rowindex].SubItems[5] := IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[5])+1);
                AdoQuery1.Next;
        end;

//****************************逐条在listview中处理企业注册资本金段的行***************************
        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 公共汽车、电车='+''''+'1'+''''+' and 统计年份='+ComboBox1.Text+' and 城市代码='+''''+'022'+'''';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ((not AdoQuery1.eof) and (AdoQuery1.fieldbyname('capi').asstring<>'')) do //最多一个区域一条记录，共20
        begin
                listview3.items.item[7].subitems[2] := AdoQuery1.fieldbyname('capi').asstring;
                listview3.items.item[6].subitems[2] := AdoQuery1.fieldbyname('capi').asstring;
                AdoQuery1.Next;
        end;

        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 地铁='+''''+'1'+''''+' and 统计年份='+ComboBox1.Text+' and 城市代码='+''''+'022'+'''';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ((not AdoQuery1.eof) and (AdoQuery1.fieldbyname('capi').asstring<>'')) do //最多一个区域一条记录，共20
        begin

                listview3.items.item[8].subitems[3] := AdoQuery1.fieldbyname('capi').asstring;
                listview3.items.item[6].subitems[3] := AdoQuery1.fieldbyname('capi').asstring;
                AdoQuery1.Next;
        end;

        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 轻轨='+''''+'1'+''''+' and 统计年份='+ComboBox1.Text+' and 城市代码='+''''+'022'+'''';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        while ((not AdoQuery1.eof) and (AdoQuery1.fieldbyname('capi').asstring<>'')) do //最多一个区域一条记录，共20
        begin