query.pas

公交行业的管理系统

                listview3.items.item[9].subitems[4] := AdoQuery1.fieldbyname('capi').asstring;
                listview3.items.item[6].subitems[4] := 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[10].subitems[5] := AdoQuery1.fieldbyname('capi').asstring;
                listview3.items.item[6].subitems[5] := AdoQuery1.fieldbyname('capi').asstring;
                AdoQuery1.Next;
        end;



{        //****************************deal with MNS49---MNS52***************************
        //sqlstr1 := 'select MNS03,MNS04,MNS05,MNS06 from 公交行业名录统计年报表 where 统计年份='+ComboBox1.Text+' and 记录类型='+''''+'企业注册资本金'+'''';
        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 单位代码 in (select 企业代码 from 主要业务活动表 where 公共汽车、电车='+''''+'1'+''''+')';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        listview3.Items.Item[7].SubItems[1] := AdoQuery1.fieldbyname('capi').AsString;
        listview3.Items.Item[7].SubItems[2] := AdoQuery1.fieldbyname('capi').AsString;
        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 单位代码 in (select 企业代码 from 主要业务活动表 where 地铁='+''''+'1'+''''+')';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        listview3.Items.Item[8].SubItems[1] := AdoQuery1.fieldbyname('capi').AsString;
        listview3.Items.Item[8].SubItems[3] := AdoQuery1.fieldbyname('capi').AsString;
        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 单位代码 in (select 企业代码 from 主要业务活动表 where 轻轨='+''''+'1'+''''+')';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        listview3.Items.Item[8].SubItems[1] := AdoQuery1.fieldbyname('capi').AsString;
        listview3.Items.Item[8].SubItems[4] := AdoQuery1.fieldbyname('capi').AsString;
        sqlstr1:= 'select sum(企业注册资本金) as capi from 公用事业单位基本情况表 where 单位代码 in (select 企业代码 from 主要业务活动表 where 轮渡='+''''+'1'+''''+')';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        listview3.Items.Item[10].SubItems[1] := AdoQuery1.fieldbyname('capi').AsString;
        listview3.Items.Item[10].SubItems[5] := AdoQuery1.fieldbyname('capi').AsString;
        listview3.Items.Item[6].SubItems[2] := listview3.Items.Item[7].SubItems[2];
        listview3.Items.Item[6].SubItems[3] := listview3.Items.Item[8].SubItems[3];
        listview3.Items.Item[6].SubItems[4] := listview3.Items.Item[9].SubItems[4];
        listview3.Items.Item[6].SubItems[5] := listview3.Items.Item[10].SubItems[5];
        if (listview3.Items.Item[6].SubItems[2]='') then listview3.Items.Item[6].SubItems[2]:='0';
        if (listview3.Items.Item[6].SubItems[3]='') then listview3.Items.Item[6].SubItems[3]:='0';
        if (listview3.Items.Item[6].SubItems[4]='') then listview3.Items.Item[6].SubItems[4]:='0';
        if (listview3.Items.Item[6].SubItems[5]='') then listview3.Items.Item[6].SubItems[5]:='0';
        listview3.Items.Item[6].SubItems[1] := IntToStr(StrToInt(listview3.Items.Item[6].SubItems[2])+StrToInt(listview3.Items.Item[6].SubItems[3])+StrToInt(listview3.Items.Item[6].SubItems[4])+StrToInt(listview3.Items.Item[6].SubItems[5]));
// }
        {//****************************deal with others**********************************
        AnnexOne(2,13,1,1);       //****************************deal with row = MNS02
        AnnexOne(16,20,2,15);       //****************************deal with row = MNS36
        AnnexOne(1,5,3,0);       //****************************deal with row = MNS42

        //***********************deal with MNS15
        ibus := 0 ; irail := 0; ihigh := 0; iship := 0;
        for itmp := 15 to 19 do
        begin
            ibus := ibus + StrToInt(listview1.Items.Item[itmp].SubItems[2]);
            irail:= irail + StrToInt(listview1.Items.Item[itmp].SubItems[3]);
            ihigh:= ihigh + StrToInt(listview1.Items.Item[itmp].SubItems[4]);
            iship := iship + StrToInt(listview1.Items.Item[itmp].SubItems[5]);
        end;
        for itmp := 0 to 14 do
        begin
            ibus := ibus + StrToInt(listview2.Items.Item[itmp].SubItems[2]);
            irail:= irail + StrToInt(listview2.Items.Item[itmp].SubItems[3]);
            ihigh:= ihigh + StrToInt(listview2.Items.Item[itmp].SubItems[4]);
            iship := iship + StrToInt(listview2.Items.Item[itmp].SubItems[5]);
        end;
        listview1.Items.Item[14].SubItems[2] := IntToStr(ibus);
        listview1.Items.Item[14].SubItems[3] := IntToStr(irail);
        listview1.Items.Item[14].SubItems[4] := IntToStr(ihigh);
        listview1.Items.Item[14].SubItems[5] := IntToStr(iship);//}

/////////////////////////////////////////////////////////////////////////////////////////////////

        //***********处理各段的合计行 不包括注册资本金段**************//
        for i := 2 to colcount do    //colcount
        begin
                colval[i]:='0';
        end;
        for rowindex := 1 to 5 do           //以企业规模为例，得到值付给其他行
        begin
                for i := 2 to colcount do
                        colval[i]:=IntToStr(StrToInt(listview3.Items.Item[rowindex].SubItems[i])+StrToInt(colval[i]));
        end;

        for i := 2 to colcount do
        begin
                listview1.items.item[0].subitems[i]:= colval[i];
                listview1.items.item[1].subitems[i]:= colval[i];
                listview1.items.item[14].subitems[i]:= colval[i];
                listview2.items.item[15].subitems[i]:= colval[i];
                listview3.items.item[0].subitems[i]:= colval[i];
        end;

        //******************************处理合计列 不包括合计行的*****************************//
        DealSumColumn();

        //-------------------- 处理注册资本金段 和 合计行的合计列项   -----//
        sqlstr1:= 'select sum(企业注册资本金) as capi,count(单位代码) as amot from 公用事业单位基本情况表 where 统计年份='+ComboBox1.Text+' and 城市代码='+''''+'022'+'''';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr1);
        AdoQuery1.Open;
        listview3.items.item[6].subitems[1]:= AdoQuery1.fieldbyname('capi').asstring;
        listview1.items.item[0].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
        listview1.items.item[1].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
        listview1.items.item[14].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
        listview2.items.item[15].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;
        listview3.items.item[0].subitems[1]:= AdoQuery1.fieldbyname('amot').asstring;

        //FillinRowOne();
        for rowindex := 6 to 10 do //listview2
        begin
                for i := 1 to 5 do
                        listview3.items.item[rowindex].subitems[i]:=ConverterPercent(listview3.items.item[rowindex].subitems[i]);
        end;


        BitBtn1.Enabled := true;
        BitBtn2.Enabled := true;
        BitBtn3.Enabled := true;

end;

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

procedure TQueryForm.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 20 do
        begin
                ss := listview2.Items.Item[rowindex].Caption;
                tit2[rowindex] := copy(ss,4,length(ss)-3);
        end;
        for rowindex := 0 to 10 do
        begin
                ss := listview3.Items.Item[rowindex].Caption;
                tit3[rowindex] := copy(ss,4,length(ss)-3);
        end;
end;

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

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

                for iRow:=0 to 19 do
                        for iCol:=1 to 5 do
                                WriteCell(iRow+5,iCol+2,ListView1.Items[iRow].SubItems[iCol]);
                for iRow:=0 to 20 do
                        for iCol:=1 to 5 do
                                WriteCell(iRow+30,iCol+2,ListView2.Items[iRow].SubItems[iCol]);
                for iRow:=0 to 10 do
                        for iCol:=1 to 5 do
                                WriteCell(iRow+57,iCol+2,ListView3.Items[iRow].SubItems[iCol]);

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

                WriteCell(2,4,ComboBox1.text+'年');
                WriteCell(27,4,ComboBox1.text+'年');
                WriteCell(54,4,ComboBox1.text+'年');

                PrintExcelShow;
                PrintPreview;
                CloseActiveBook;                
        end;
end;

procedure TQueryForm.FormCreate(Sender: TObject);
begin
        colcount := 5;
end;

end.