bustradeyearrepunit.pas

公交行业的管理系统

        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        if ADOQuery1.RecordCount=0 then
        begin
                BitBtn2.Enabled:=false;
                ShowMessage('不存在' + iYear + '年的行业年报生成数据，请先生成！');
                ClearData(ListView1,23,0,1,2);
                ClearData(ListView1,23,0,1,6);
                ClearData(ListView2,21,0,1,2);
                ClearData(ListView2,19,0,1,6);
                exit;
        end
        else
        begin
                ADOQuery1.First;
                for  iRecNum:=0 to ADOQuery1.RecordCount-1 do
                begin
                        if ADOQuery1.FieldByName('实际').AsString<>'' then
                        begin
                                iExistFlag:=1;
                                break;
                        end;
                        ADOQuery1.Next;
                end;
                if iExistFlag=0 then
                begin
                        BitBtn2.Enabled:=false;
                        ShowMessage('不存在' + iYear + '年的行业年报数据，请先生成！');
                        ClearData(ListView1,23,0,1,2);
                        ClearData(ListView1,23,0,1,6);
                        ClearData(ListView2,21,0,1,2);
                        ClearData(ListView2,19,0,1,6);
                        exit;
                end;
        end;

        iExistFlag:=0;
        ADOQuery1.First;
        for iRecNum := 0 to 22 do
        begin
                ListView1.Items[iRecNum].SubItems[2]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                ADOQuery1.Next;
        end;

        for iRecNum := 0 to 22 do
        begin
                ListView1.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                ADOQuery1.Next;
        end;

        for iRecNum := 0 to 20 do
        begin
                ListView2.Items[iRecNum].SubItems[2]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                ADOQuery1.Next;
        end;

        for iRecNum := 0 to 18 do
        begin
                ListView2.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                ADOQuery1.Next;
        end;

        //清空之前的内容
        Memo1.Lines.Delete(1);

        //填加新内容
        sqlString:='select 备注 from 公交行业统计年报表 where 统计年份=' + iYear
                  +' and 城市代码=' + iCityCode + ' and 生成方式=' + '''' + '生成' + '''';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        if ADOQuery1.RecordCount<>0 then
        begin
                Memo1.Lines.Append(ADOQuery1.FieldbyName('备注').AsString);
        end;

        BitBtn2.Enabled:=true;

end;

procedure TBusTradeYearRepFrm.FormCreate(Sender: TObject);
begin
        iExistFlag:=0;
        iUpdateFlag:=0;
end;

procedure TBusTradeYearRepFrm.BitBtn1Click(Sender: TObject);
var
        iMonth:String;
        iYear:String;
        sqlString:String;
        iRecNum:Integer;
        iRow:Integer;
        iCityCode:String;
        iDivdStr:String;
        sMemoInfo:String;
        iTotalValue:single;
        iTotalLength:single;
        iHTableName:String;
begin
        //生成需要汇总数据的年份信息

        iYear:=ComboBox1.Text;
        if (iYear<'1990') or (iYear >'2030') then
        begin
                ShowMessage('请输入在时间范围1990-2030内的合法年份!');
                exit;
        end;

        iTotalValue:=0;
        iTotalLength:=0;
        iCityCode:='''' + '022' + '''';

        iHTableName:='公交行业统计年报表';

        iUpdateFlagH:=0;
        sqlString:='select * from ' + iHTableName + ' where 城市代码=' + iCityCode
                  +' and 统计年份=' + iYear + ' and 生成方式=' + '''' + '生成' + '''';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;
        if ADOQuery1.RecordCount<>0 then
        begin
                iUpdateFlagH:=1;
        end;


        //查询判断该年该月数据是否已经存在
        sqlString:='select 指标代码,全年实际 as 实际 from 公交行业统计年报_z where '
                  +' 城市代码=' + iCityCode + ' and 统计年份='
                   + iYear + ' order by 指标代码';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        //如果数据存在，直接显示；否则，生成数据并显示
        if ADOQuery1.RecordCount<>0 then
        begin
                ADOQuery1.First;
                for iRow:=0 to ADOQuery1.RecordCount do
                begin
                        if ADOQuery1.FieldByName('实际').Value<>0 then
                        begin
                                if(MessageDlg('数据库中已经存在' + iYear + '年的行业年报数据，是否覆盖？',mtConfirmation,[mbYes, mbNo],0) = mrNo)then
                                begin
                                        BitBtn4Click(Sender);
                                        exit;//不覆盖退出
                                end
                                else
                                begin
                                        iUpdateFlag:=1;//覆盖，置覆盖标志位
                                        break;
                                end;
                        end;
                        ADOQuery1.Next;
                end;
                iUpdateFlag:=1;
        end
        else
        begin
                iUpdateFlag:=0;
        end;
                //判断企业数据中是否存在当年当月的数据
        sqlString:='select a.指标代码 from 公交企业统计年报表_z a,公用事业单位基本情况表 b where '
                  +' b.统计年份=' + iYear + ' and b.城市代码=' + '''' + '022' + '''' + ' and a.企业代码=b.单位代码 and a.统计年份='
                  + iYear + ' order by a.指标代码';
        ADOQuery3.Close;
        ADOQuery3.SQL.Clear;
        ADOQuery3.SQL.Add(sqlString);
        ADOQuery3.Open;

        //如果不存在企业数据，录入；否则，汇总、显示
        if ADOQuery3.RecordCount=0 then
        begin
                ShowMessage('不存在' + iYear + '年企业年报数据,请先录入!');
                BitBtn2.Enabled:=false;
                exit;

        end
        else
        begin
        {生成数据}
                sqlString:='select a.指标代码,sum(全年实际) as 实际'
                          + ' from 公交企业统计年报表_z a,公用事业单位基本情况表 b where '
                          + ' b.城市代码=' + '''' + '022' + '''' + ' and b.统计年份=' + iYear + ' and a.企业代码=b.单位代码'
                          + ' and a.统计年份= ' + iYear + ' group by a.指标代码 order by a.指标代码';
                ADOQuery1.Close;
                ADOQuery1.SQL.Clear;
                ADOQuery1.SQL.Add(sqlString);
                ADOQuery1.Open;

                ADOQuery1.First;
                ADOQuery1.First;
                for iRecNum := 0 to 22 do
                begin
                        ListView1.Items[iRecNum].SubItems[2]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                        ADOQuery1.Next;
                end;

                for iRecNum := 0 to 22 do
                begin
                        ListView1.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                        ADOQuery1.Next;
                end;

                for iRecNum := 0 to 20 do
                begin
                        ListView2.Items[iRecNum].SubItems[2]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                        ADOQuery1.Next;
                end;

                for iRecNum := 0 to 18 do
                begin
                        ListView2.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                        ADOQuery1.Next;
                end;

                {特殊指标的处理}
                iDivdStr:=FloatToStr(StrToFloat(ListView1.Items[10].subItems[6]) + StrToFloat(ListView1.Items[17].subItems[6])
                                    +StrToFloat(ListView1.Items[21].subItems[6]));
                ListView1.Items[22].SubItems[6]:=SpecDataDiv(ListView1.Items[5].SubItems[6],iDivdStr,100);
                ListView2.Items[14].SubItems[2]:=SpecDataDiv(ListView2.Items[13].SubItems[2],ListView1.Items[4].SubItems[6],100);

                //------指标GN73-----
                SqlString:='SELECT a.企业代码, Max(a.全年实际) AS 成本, sum(b.本月实际) AS 里程, 成本*里程 AS 总额'
                          +' FROM 公交企业统计年报表_z AS a, 公交企业统计月报表_z AS b, 公用事业单位基本情况表 AS c'
                          +' WHERE c.统计年份=' + iYear + ' And a.统计年份=c.统计年份 And b.统计年份=c.统计年份'
                          +' And a.指标代码=' + '''' + 'GN73' + '''' + ' And b.指标代码=' + '''' + 'G54' + ''''
                          +' And a.企业代码=c.单位代码 And b.企业代码=c.单位代码'
                          +' GROUP BY a.企业代码';
                ADOQuery1.Close;
                ADOQuery1.SQL.Clear;
                ADOQuery1.SQL.Add(sqlString);
                ADOQuery1.Open;

                for iRow := 0 to ADOQuery1.RecordCount-1 do
                begin
                        iTotalValue:=iTotalValue+ADOQuery1.fieldbyName('总额').AsFloat;
                        ADOQuery1.Next;
                end;

                sqlString:='select sum(a.本月实际) as 里程总和'
                          +' from 公交企业统计月报表_z a,公用事业单位基本情况表 b'
                          +' where a.企业代码=b.单位代码'
                          +' and a.统计年份=' + iYear + ' and b.统计年份=' + iYear
                          +' and a.指标代码=' + '''' + 'G54' + ''''
                          +' group by a.指标代码';
                ADOQuery1.Close;
                ADOQuery1.SQL.Clear;
                ADOQuery1.SQL.Add(sqlString);
                ADOQuery1.Open;

                if ADOQuery1.RecordCount<>0 then
                begin
                        iTotalLength:=iTotalLength+ ADOQuery1.fieldByName('里程总和').AsFloat;
                end;

                ListView2.Items[5].SubItems[6]:=SpecDataDiv(FloatTostr(iTotalValue),FloatToStr(iTotalLength),1);
        end;

        Memo1.Lines.Delete(1);

        //------给出尚未录入数据的企业名单-----
        {sqlString:='select DISTINCT a.单位名称 as 名称 FROM 公用事业单位基本情况表 AS a, 公交企业统计年报表_z AS b'
                  +' WHERE a.统计年份=' + iYear + ' and b.统计年份=a.统计年份 and a.单位代码<>b.企业代码';//}
        sqlString:='select DISTINCT a.单位名称 as 名称 FROM 公用事业单位基本情况表 AS a '
                  +' WHERE a.统计年份=' + iYear + ' and a.单位代码 not in ('
                  +' select 企业代码 from 公交企业统计年报表_z where 统计年份=' + iYear + ')';
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        if ADOQuery1.RecordCount<>0 then
        begin
                for iRecNum := 0 to ADOQuery1.RecordCount-1 do
                begin
                        sMemoInfo:=sMemoInfo + ADOQuery1.fieldbyname('名称').AsString+';';
                        ADOQuery1.Next;
                end;
                Memo1.Lines.append(sMemoInfo);
        end;

        {写库}
        if (iUpdateFlag=1) and (iUpdateFlagH=1) then
        begin
                UpdateMonData;
        end
        else if (iUpdateFlag=0) and (iUpdateFlagH=0) then
        begin
                WriteMonData;
        end
        else
        begin
                SpecYearData;
        end;


        BitBtn2.Enabled:=true;
        ShowMessage(iYear+'年行业年报数据生成结束！');
end;

procedure TBusTradeYearRepFrm.BitBtn2Click(Sender: TObject);
var iRecNum,iCol,iRow:integer;
begin
        if (DeviceDetect=0) then
        begin
                exit;
        end;

        if(PrnInfoFrm.ShowModal<>mrOK)then
        begin
                exit;
        end;

        PrintInit(ExtractFilePath(Application.ExeName),'BusTradeYearStaTab');
        WriteCell(4,1,'汇总单位:  客管办');
        WriteCell(35,1,'汇总单位:  客管办');
        WriteCell(3,4,ComboBox1.Text + ' 年');
        WriteCell(34,4,ComboBox1.Text + ' 年');
        WriteCell(31,1,'统计负责人：' + PrnInfoFrm.StatMainPerson);
        WriteCell(31,4,'统计人员：' + PrnInfoFrm.StatPerson);
        WriteCell(31,8,DateToStr(PrnInfoFrm.StatDate));
        WriteCell(60,1,'统计负责人：' + PrnInfoFrm.StatMainPerson);
        WriteCell(60,4,'统计人员：' + PrnInfoFrm.StatPerson);
        WriteCell(60,8,DateToStr(PrnInfoFrm.StatDate));

        for iRecNum := 1 to 86 do
        begin
                if (iRecNum<=23)then
                begin
                        iRow := iRecNum+7;
                        iCol := 4;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum-1].SubItems[2]);
                end;
                if (iRecNum>=24) and (iRecNum<=46) then
                begin
                        iRow := iRecNum-16;
                        iCol:=8;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum-24].SubItems[6]);
                end;
                if (iRecNum>=47) and (iRecNum<=67) then
                begin
                        iRow := iRecNum-8;
                        iCol:=4;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-47].SubItems[2]);
                end;
                if (iRecNum>=68) and (iRecNum<=86) then
                begin
                        iRow := iRecNum-29;
                        iCol:=8;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-68].SubItems[6]);
                end;
        end;

        PrintExcelShow;
        PrintPreview;
        CloseActiveBook;

end;

end.