namebooksalequeryunit.pas

公交行业的管理系统

end;

function  TNameBookSaleQuery.FindRow(List:TListView;curField:string):integer;
var i:integer;
tempstr:string;
begin
        for i:=0 to List.items.Count-1 do
         begin
          tempstr:=copy(List.Items[i].Caption,4,length(List.Items[i].Caption)-1);
          if(tempstr=curField)then
           begin
            FindRow:=i;
            exit;
           end;
         end;
         FindRow:=-1;
end;

procedure TNameBookSaleQuery.FillDataToTable2(fieldstr:string;Indexnum:integer);
var sqlstr,Indexstr:string;
i,j:integer;
begin
         sqlstr:='select 单位坐落区域 as 坐落区域 ,count(单位坐落区域) as 合计 from 公用事业单位基本情况表 '
                +' where 城市代码='+''''+'022'+''''+' and 统计年份='+combobox1.Text
                +' and 营业状况='+''''+fieldstr+''''+' group by [单位坐落区域]';

        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.add(sqlstr);
        ADOQuery1.Open;
        ADOQuery1.First;
        for i:=0 to ADOQuery1.recordcount-1 do
         begin
          Indexstr:=ADOQuery1.fieldbyname('坐落区域').asstring;
          j:=FindRow(ListView2,Indexstr);
          if (j>0) then
           begin
            ListView2.Items[j].SubItems[Indexnum]:=ADOQuery1.fieldbyname('合计').asstring;
           end;
          ADOQuery1.next;
         end;

end;

procedure TNameBookSaleQuery.FillDataToTable3(fieldstr:string;Indexnum:integer);
var sqlstr,Indexstr:string;
i,j:integer;
begin
        sqlstr:='select 企业规模 ,count(企业规模) as 合计 from 公用事业单位基本情况表 '
                +' where 城市代码='+''''+'022'+''''+' and 统计年份='+combobox1.Text
                +' and 营业状况='+''''+fieldstr+''''+' group by 企业规模';

        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.add(sqlstr);
        ADOQuery1.Open;
        if(ADOQuery1.recordcount>=0)then
          begin
                ADOQuery1.First;
                        for i:=0 to ADOQuery1.recordcount-1 do
                        begin
                          Indexstr:=ADOQuery1.fieldbyname('企业规模').asstring;
                          j:=FindRow(ListView3,Indexstr);
                        if j>0 then
                        begin
                          ListView3.Items[j].SubItems[Indexnum]:=ADOQuery1.fieldbyname('合计').asstring;
                        end;
                        ADOQuery1.next;
                        end;
        end;
end;

procedure TNameBookSaleQuery.FillDataToTable4(curName:string;curQuery:string;Colnum:integer);
var sqlstr:string;
Recnum:integer;
begin
        sqlstr:='select a.营业状况 ,b.'+curName+' from 公用事业单位基本情况表 as a,主要业务活动表 as b'
                +' where a.城市代码='+''''+'022'+''''+' and a.单位代码=b.企业代码'
                +' and a.统计年份='+combobox1.text+' and b.统计年份='+combobox1.text
                +' and a.营业状况='+''''+curQuery+''''+' and b.'+curName+'='+''''+'1'+'''';

        ADOQuery2.close;
        ADOQuery2.SQL.Clear;
        ADOQuery2.SQL.add(sqlstr);
        ADOQuery2.open;
        Recnum:=ADOQuery2.recordcount;
        if(curName='公共汽车、电车')then
         ListView1.items[2].subitems[Colnum]:=inttostr(Recnum);
        if(curName='地铁')then
         ListView1.items[3].subitems[Colnum]:=inttostr(Recnum);
        if(curName='轻轨')then
         ListView1.items[4].subitems[Colnum]:=inttostr(Recnum);
        if(curName='轮渡')then
         ListView1.items[5].subitems[Colnum]:=inttostr(Recnum);
end;

procedure TNameBookSaleQuery.FillSumDataToTable();
var i,j:integer;
begin
        //添加业务活动合计值
        for i:=2 to 6 do
        begin
           for j:=2 to 5 do
             begin
               ListView1.items[1].subitems[i]:=floattostr(strtofloat(ListView1.items[1].subitems[i])+strtofloat(ListView1.items[j].subitems[i]));
             end;
        end;
        //添加经济类型合计值
        for i:=2 to 6 do
        begin
           for j:=7 to 18 do
             begin
               ListView1.items[6].subitems[i]:=floattostr(strtofloat(ListView1.items[6].subitems[i])+strtofloat(ListView1.items[j].subitems[i]));
             end;
        end;

       //添加座落区域合计值
        for i:=2 to 6 do
          begin
             for j:=1 to 20 do
                begin
                  ListView2.items[0].subitems[i]:=floattostr(strtofloat(ListView2.items[0].subitems[i])+strtofloat(ListView2.items[j].subitems[i]));
                end;
         end;

       //添加企业规模合计值
       for i:=2 to 6 do
          begin
             for j:=1 to 5 do
                begin
                  ListView3.items[0].subitems[i]:=floattostr(strtofloat(ListView3.items[0].subitems[i])+strtofloat(ListView3.items[j].subitems[i]));
                end;
         end;

       //添加企业注册资本金合计值
       for i:=2 to 6 do
          begin
             for j:=7 to 10 do
                begin
                  ListView3.items[6].subitems[i]:=floattostr(strtofloat(ListView3.items[6].subitems[i])+strtofloat(ListView3.items[j].subitems[i]));
                end;
         end;


       for i:=2 to 6 do
         begin
           ListView1.Items[1].SubItems[i]:=ListView1.Items[6].subitems[i];
         end;
end;

procedure TNameBookSaleQuery.GetSum();
var rowindex,colindex : integer;
begin
        for rowindex := 1 to 18 do
        begin
                for colindex := 2 to 6 do
                begin
                     listview1.Items[rowindex].SubItems[1]:= floatToStr(StrTofloat(listview1.Items[rowindex].SubItems[1])+StrTofloat(listview1.Items[rowindex].SubItems[colindex]));
                end;
        end;
        for rowindex := 0 to 19 do
        begin
                for colindex := 2 to 6 do
                begin
                     listview2.Items[rowindex].SubItems[1]:= floatToStr(StrTofloat(listview2.Items[rowindex].SubItems[1])+StrTofloat(listview2.Items[rowindex].SubItems[colindex]));
                end;
        end;
        for rowindex := 0 to 10 do
        begin
                for colindex := 2 to 6 do
                begin
                     listview3.Items[rowindex].SubItems[1]:= FloatToStr(StrToFloat(listview3.Items[rowindex].SubItems[1])+StrToFloat(listview3.Items[rowindex].SubItems[colindex]));
                end;
        end;
end;

procedure TNameBookSaleQuery.FillDataToTable5(curName:string;queryname:string;Colnum:integer);
var Recnum,sqlstr:string;
begin
        sqlstr:='select sum(a.企业注册资本金) as 合计,b.'+curName
                +' from 公用事业单位基本情况表 as a,主要业务活动表 as b'
                +' where  a.城市代码='+''''+'022'+''''+' and a.单位代码=b.企业代码'
                +' and a.统计年份='+combobox1.text+' and b.统计年份='+combobox1.text+' and a.营业状况='
                +''''+queryname+''''+' and b.'+curName+'='+''''+'1'+''''+' group by b.'+curName;

        ADOQuery2.close;
        ADOQuery2.SQL.Clear;
        ADOQuery2.SQL.add(sqlstr);
        ADOQuery2.open;
        if(ADOQuery2.RecordCount<=0)then
        exit;

        Recnum:=ADOQuery2.fieldbyname('合计').asstring;
        if(curName='公共汽车、电车')then
         ListView3.items[7].subitems[Colnum]:=Recnum;
        if(curName='地铁')then
         ListView3.items[8].subitems[Colnum]:=Recnum;
        if(curName='轻轨')then
         ListView3.items[9].subitems[Colnum]:=Recnum;
        if(curName='轮渡')then
         ListView3.items[10].subitems[Colnum]:=Recnum;

end;

procedure TNameBookSaleQuery.FillOneRowData(curName:string;colnum:integer);
var sqlstr1:string;
begin
        sqlstr1:='select distinct 单位代码 from 公用事业单位基本情况表 where 城市代码='+''''
                +'022'+''''+' and 营业状况='+''''+curName+''''+' and 统计年份='+combobox1.Text;
        ADOQuery2.Close;
        ADOQuery2.SQL.Clear;
        ADOQuery2.SQL.Add(sqlstr1);
        ADOQuery2.Open;
        ListView1.Items[0].SubItems[colnum]:=inttostr(ADOQuery2.RecordCount);
end;
procedure TNameBookSaleQuery.BitBtn3Click(Sender: TObject);
var
        iRow:Integer;
        iCol:Integer;
begin
         if PrnInfoFrm.ShowModal=mrOK then
        begin

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

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

                WriteCell(24,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(24,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(24,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));
                WriteCell(51,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(51,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(51,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));
                WriteCell(76,1,'统计负责人：'+PrnInfoFrm.StatMainPerson);
                WriteCell(76,4,'统计人员：'+PrnInfoFrm.StatPerson);
                WriteCell(76,7,'填报日期：'+DateToStr(PrnInfoFrm.StatDate));

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

                PrintExcelShow;
                PrintPreview;
                CloseActiveBook;                
        end;
end;

procedure TNameBookSaleQuery.FillSumMoney(SaleState:string;ColNum:integer);
var sqlstr:string;
begin
        sqlstr:='select 统计年份,sum(企业注册资本金) as 合计 from 公用事业单位基本情况表 where 统计年份='+
                combobox1.text+' and 城市代码='+''''+'022'+''''+' and 营业状况='+''''+SaleState+''''
                +' group by 统计年份 ';
        ADOQuery3.close;
        ADOQuery3.sql.clear;
        ADOQuery3.sql.add(sqlstr);
        ADOQuery3.open;
        if(ADOQuery3.fieldbyname('合计').asstring='')then
                Listview3.items[6].subitems[ColNum]:='0'
        else
                Listview3.items[6].subitems[ColNum]:=ADOQuery3.fieldbyname('合计').asstring;
end;

procedure TNameBookSaleQuery.FillTotalMoney();
var sqlstr:string;
begin
        sqlstr:='select 统计年份,sum(企业注册资本金) as 合计 from 公用事业单位基本情况表 where 统计年份='+
                combobox1.text+' and 城市代码='+''''+'022'+''''
                +' group by 统计年份 ';
        ADOQuery3.close;
        ADOQuery3.sql.clear;
        ADOQuery3.sql.add(sqlstr);
        ADOQuery3.open;
        Listview3.items[6].subitems[1]:=ADOQuery3.fieldbyname('合计').asstring;

end;
end.