query2.~pas

公交行业的管理系统

                listview3.Items.Item[rowindex].SubItems[4] := '0';
                listview3.Items.Item[rowindex].SubItems[5] := '0';
                for i := 6 to 13 do
                begin
                        listview3.Items.Item[rowindex].SubItems.Add('0');
                end;
        end;
end;


procedure TQueryForm2.FormShow(Sender: TObject);
var
        rowindex : integer;
        ss : string;
begin
        InitListViews();
        for rowindex := 0 to 19 do
        begin
                ss := listview1.Items.Item[rowindex].Caption;
                tit1[rowindex] := copy(ss,4,length(ss)-3);
        end;
        for rowindex := 0 to 18 do
        begin
                ss := listview2.Items.Item[rowindex].Caption;
                tit2[rowindex] := copy(ss,4,length(ss)-3);
        end;
        for rowindex := 0 to 4 do
        begin
                ss := listview3.Items.Item[rowindex].Caption;
                tit3[rowindex] := copy(ss,4,length(ss)-3);
        end;
end;


procedure TQueryForm2.BitBtn2Click(Sender: TObject);
begin
        QueryForm2.Close;
end;

procedure TQueryForm2.ComboBox1Change(Sender: TObject);
begin
        InitListViews();
end;

procedure TQueryForm2.BitBtn1Click(Sender: TObject);
var
        sqlstr1,strtmp,sqlstr2,ss : string;
        rowindex,colindex : integer;
        rowname,colname: string;
        ibus,irail,iship,ihigh,itmp,i :integer;
        val : single;
        ii : array [0..11] of string;
begin
        if (ComboBox1.Text='') or (length(ComboBox1.Text)<>4) or (ComboBox1.Text>'3000') or (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
                colname := AdoQuery1.fieldbyname('经济类型').AsString;
                colindex := GetColIndex(colname);
                // deal with MNS08 ------MNS27
                rowname := AdoQuery1.fieldbyname('坐落区域').AsString;
                rowindex := GetRowIndex(rowname,1);

                if (rowindex <> -1) then
                begin
                        listview1.Items.Item[rowindex].SubItems[colindex]:= IntToStr(StrToInt(listview1.Items.Item[rowindex].SubItems[colindex])+1);
               end
                else
                begin
                        rowindex := GetRowIndex(rowname,2);
                        listview2.Items.Item[rowindex].SubItems[colindex]:= IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[colindex])+1);
               end;


                // deal with MNS29------MNS33
                rowname := AdoQuery1.fieldbyname('营业状态').AsString;
                rowindex := GetRowIndex(rowname,2);
                listview2.Items.Item[rowindex].SubItems[colindex]:= IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[colindex])+1);
               // deal with MNS35------MNS39
                rowname := AdoQuery1.fieldbyname('企业规模').AsString;
                rowindex := GetRowIndex(rowname,2);
                listview2.Items.Item[rowindex].SubItems[colindex]:= IntToStr(StrToInt(listview2.Items.Item[rowindex].SubItems[colindex])+1);
                // deal with MNS03--06
                strtmp := AdoQuery1.fieldbyname('公共汽车').AsString;
                if (strtmp = '1') then listview1.Items.Item[2].SubItems[colindex]:= IntToStr(StrToInt(listview1.Items.Item[2].SubItems[colindex])+1);
                strtmp := AdoQuery1.fieldbyname('地铁').AsString;
                if (strtmp = '1') then listview1.Items.Item[3].SubItems[colindex]:= IntToStr(StrToInt(listview1.Items.Item[3].SubItems[colindex])+1);
                strtmp := AdoQuery1.fieldbyname('轻轨').AsString;
                if (strtmp = '1') then listview1.Items.Item[4].SubItems[colindex]:= IntToStr(StrToInt(listview1.Items.Item[4].SubItems[colindex])+1);
                strtmp := AdoQuery1.fieldbyname('轮渡').AsString;
                if (strtmp = '1') then listview1.Items.Item[5].SubItems[colindex]:= IntToStr(StrToInt(listview1.Items.Item[5].SubItems[colindex])+1);

                AdoQuery1.Next;
        end;

        sqlstr1 := 'select alist.[企业(单位)登记注册类型] as 经济类型,alist.企业注册资本金 as capi,';
        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
                colname := AdoQuery1.fieldbyname('经济类型').AsString;
                colindex := GetColIndex(colname);
                val := AdoQuery1.fieldbyname('capi').AsFloat;
                // deal with MNS41--44
                strtmp := AdoQuery1.fieldbyname('公共汽车').AsString;
                if (strtmp = '1') then listview3.Items.Item[1].SubItems[colindex]:= FloatToStr(StrToFloat(listview3.Items.Item[1].SubItems[colindex])+val);
                strtmp := AdoQuery1.fieldbyname('地铁').AsString;
                if (strtmp = '1') then listview3.Items.Item[2].SubItems[colindex]:= FloatToStr(StrToFloat(listview3.Items.Item[2].SubItems[colindex])+val);
                strtmp := AdoQuery1.fieldbyname('轻轨').AsString;
                if (strtmp = '1') then listview3.Items.Item[3].SubItems[colindex]:= FloatToStr(StrToFloat(listview3.Items.Item[3].SubItems[colindex])+val);
                strtmp := AdoQuery1.fieldbyname('轮渡').AsString;
                if (strtmp = '1') then listview3.Items.Item[4].SubItems[colindex]:= FloatToStr(StrToFloat(listview3.Items.Item[4].SubItems[colindex])+val);
                AdoQuery1.Next;
        end;

        GetSum();
        SumBlock(2,5,1,1);
        SumBlock(8,12,2,7);
        SumBlock(14,18,2,13);
        SumBlock(1,4,3,0);
        SumBlock(7,19,1,6);
        rowindex := 0;
        while (rowindex<=6) do
        begin
                for itmp := 1 to 13 do
                begin
                    listview1.Items.Item[6].SubItems[itmp]:= IntToStr(StrToInt(listview1.Items.Item[6].SubItems[itmp])+ StrToInt(listview2.Items.Item[rowindex].SubItems[itmp]));
                end;
              rowindex := rowindex+1;
        end;

        sqlstr2:='select count(企业代码) as num from 主要业务活动表 where 统计年份='+ComboBox1.Text;
        AdoQuery2.Close;
        AdoQuery2.SQL.Clear;
        AdoQuery2.SQL.Add(sqlstr2);
        AdoQuery2.Open;
        Listview1.Items.Item[1].SubItems[1]:= AdoQuery2.fieldbyname('num').AsString;
        ii[0]:= '国有企业';
        ii[1]:= '集体企业';
        ii[2]:= '股份合作企业';
        ii[3]:= '联营企业';
        ii[4]:= '有限责任公司';
        ii[5]:= '股份有限公司';
        ii[6]:= '私营企业';
        ii[7]:= '港澳台商投资企业';
        ii[8]:= '中外合资经营企业';
        ii[9]:= '中外合作经营企业';
        ii[10]:= '外商独资企业';
        ii[11]:= '外商投资股份有限公司';

        for itmp := 2 to 13 do
        begin
        //strtmp := colname[1];

        sqlstr2:='select  alist.企业代码 from 主要业务活动表 as alist, 公用事业单位基本情况表 as blist  where alist.统计年份='+ComboBox1.Text+ ' and alist.统计年份=blist.统计年份 and alist.企业代码=blist.单位代码 and blist.[企业(单位)登记注册类型]='+''''+ii[itmp-2]+'''';
        AdoQuery2.Close;
        AdoQuery2.SQL.Clear;
        AdoQuery2.SQL.Add(sqlstr2);
        AdoQuery2.Open;
        Listview1.Items.Item[1].SubItems[itmp]:= IntToStr(AdoQuery2.RecordCount);
        sqlstr2:='select sum(blist.企业注册资本金) as capi from 主要业务活动表 as alist, 公用事业单位基本情况表 as blist  where alist.统计年份='+ComboBox1.Text+ ' and alist.统计年份=blist.统计年份 and alist.企业代码=blist.单位代码 and blist.[企业(单位)登记注册类型]='+''''+ii[itmp-2]+'''';
        AdoQuery2.Close;
        AdoQuery2.SQL.Clear;
        AdoQuery2.SQL.Add(sqlstr2);
        AdoQuery2.Open;
        if (AdoQuery2.fieldbyname('capi').AsString<>'') then  Listview3.Items.Item[0].SubItems[itmp]:=  AdoQuery2.fieldbyname('capi').AsString;
        end;

        i := 1;
        while (i <=13) do
        begin
              listview1.Items.Item[0].SubItems[i]:= listview1.Items.Item[6].SubItems[i];
              i:=i+1;
        end;
        BitBtn1.Enabled := true;
        BitBtn2.Enabled := true;
        BitBtn3.Enabled := true;
end;

procedure TQueryForm2.FormCreate(Sender: TObject);
begin
        colname[2]:= '国有企业';
        colname[3]:= '集体企业';
        colname[4]:= '股份合作企业';
        colname[5]:= '联营企业';
        colname[6]:= '有限责任公司';
        colname[7]:= '股份有限公司';
        colname[8]:= '私营企业';
        colname[9]:= '港澳台商投资企业';
        colname[10]:= '中外合资经营企业';
        colname[11]:= '中外合作经营企业';
        colname[12]:= '外商独资企业';
        colname[13]:= '外商投资股份有限公司';
end;

procedure TQueryForm2.BitBtn3Click(Sender: TObject);
var
        iRow:Integer;
        iCol:Integer;
begin
        if PrnInfoFrm.ShowModal=mrOK then
        begin

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

                for iRow:=0 to 19 do
                        for iCol:=1 to 13 do
                                WriteCell(iRow+5,iCol+2,ListView1.Items[iRow].SubItems[iCol]);
                for iRow:=0 to 18 do
                        for iCol:=1 to 13 do
                                WriteCell(iRow+31,iCol+2,ListView2.Items[iRow].SubItems[iCol]);
                for iRow:=0 to 4 do
                        for iCol:=1 to 13 do
                                WriteCell(iRow+57,iCol+2,ListView3.Items[iRow].SubItems[iCol]);

                WriteCell(25,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(25,6,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(25,13,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));
                WriteCell(50,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(50,6,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(50,13,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));
                WriteCell(78,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(78,6,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(78,13,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

                WriteCell(2,7,ComboBox1.text+'年');
                WriteCell(28,7,ComboBox1.text+'年');
                WriteCell(54,7,ComboBox1.text+'年');

                PrintExcelShow;
                PrintPreview;
                CloseActiveBook;                
        end;
end;

end.