bustradecountmonunit.~pas

公交行业的管理系统

        ADOQuery4.Open;

        if ADOQuery4.RecordCount=0 then
        begin
                ShowMessage('尚无该年该月的行业月报数据，请先汇总生成该统计数据!');
                exit;
        end
        else
                DBGrid1.DataSource:=DataSource2;
        begin

        end;

end;
}
procedure TBusTradeCountMonFm.BitBtn1Click(Sender: TObject);
var
        iMonth:String;
        iYear:String;
        sqlString:String;
        iRecNum:Integer;
        iRow:Integer;
        iCityCode:String;
        sMemoInfo:String;
begin
        //生成需要汇总数据的年份和月份信息

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

        iCityCode:='''' + '022' + '''';
        //查询判断该年该月数据是否已经存在
        sqlString:='select 指标代码,本月实际 from 公交行业统计月报_z where '
                  +' 城市代码=' + iCityCode + ' and 统计年份='
                   + iYear + ' and 统计月份 = ' + iMonth + ' 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 + '年' + iMonth + '月的行业月报，是否覆盖？',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 + ' and a.统计月份=' + iMonth;
        ADOQuery3.Close;
        ADOQuery3.SQL.Clear;
        ADOQuery3.SQL.Add(sqlString);
        ADOQuery3.Open;

        //如果不存在企业数据，录入；否则，汇总、显示
        if ADOQuery3.RecordCount=0 then
        begin
                ShowMessage('不存在' + iYear + '年' + iMonth + '月的企业月报数据,请先录入!');
                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 + ' and a.统计月份= '
                          + iMonth + ' group by a.指标代码 order by a.指标代码';
                ADOQuery1.Close;
                ADOQuery1.SQL.Clear;
                ADOQuery1.SQL.Add(sqlString);
                ADOQuery1.Open;

                ADOQuery1.First;

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

                for iRecNum := 0 to 19 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 19 do
                begin
                        ListView2.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                        ADOQuery1.Next;
                end;

                {特殊指标的处理}
                ListView2.Items[1].SubItems[6]:=SpecDataDiv(ListView2.Items[2].SubItems[6],ListView2.Items[3].SubItems[6],10000);
                ListView2.Items[4].SubItems[6]:=SpecDataDiv(ListView2.Items[5].SubItems[6],ListView2.Items[6].SubItems[6],100);
                ListView2.Items[7].SubItems[6]:=SpecDataDiv(ListView2.Items[8].SubItems[6],ListView2.Items[9].SubItems[6],100);
                ListView2.Items[10].SubItems[6]:=SpecDataDiv(ListView2.Items[11].SubItems[6],ListView2.Items[12].SubItems[6],100);
                ListView2.Items[14].SubItems[6]:=SpecDataDiv(ListView2.Items[13].SubItems[2],ListView2.Items[13].SubItems[6],1);

        end;

        Memo1.Lines.Delete(1);
        //------给出尚未录入数据的企业名单-----
        {sqlString:='select DISTINCT a.单位名称 as 名称 FROM 公用事业单位基本情况表 AS a, 公交企业统计月报表_z AS b'
                  +' WHERE a.统计年份=' + iYear + ' and b.统计年份=a.统计年份 and b.统计月份=' + iMonth + ' and a.单位代码<>b.企业代码';//}
        sqlString:='select DISTINCT a.单位名称 as 名称 FROM 公用事业单位基本情况表 AS a '
                  +' WHERE a.统计年份=' + iYear + ' and a.单位代码 not in ('
                  +' select 企业代码 from 公交企业统计月报表_z where 统计年份=' + iYear + ' and 统计月份=' + iMonth + ')';
        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 then
        begin
                UpdateMonData;
        end
        else
        begin
                WriteMonData;
        end;


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

end;

procedure TBusTradeCountMonFm.BitBtn3Click(Sender: TObject);
begin
        ClearData(ListView1,20,0,1,2);
        ClearData(ListView1,20,0,1,6);
        ClearData(ListView2,21,0,1,2);
        ClearData(ListView2,20,0,1,6);
        Close;
end;

procedure TBusTradeCountMonFm.BitBtn4Click(Sender: TObject);
var
        iMonth:String;
        iYear:String;
        sqlString:String;
        iRecNum:Integer;
        iCityCode:String;
begin
        iMonth:=ComboBox2.Text;
        iYear:=ComboBox1.Text;
        if (iYear<'1990') or (iYear >'2030') then
        begin
                ShowMessage('请输入在时间范围1990-2030内的合法年份!');
                exit;
        end
        else if (iMonth='') then
        begin
                ShowMessage('请选择月份!');
                exit;
        end;

        iCityCode:='''' + '022' + '''';

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

        if ADOQuery1.RecordCount=0 then
        begin
                BitBtn2.Enabled:=false;
                ShowMessage('不存在' + iYear + '年' + iMonth + '月的行业数据，请先生成！');
                ClearData(ListView1,20,0,1,2);
                ClearData(ListView1,20,0,1,6);
                ClearData(ListView2,21,0,1,2);
                ClearData(ListView2,20,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 + '年' + iMonth + '月的行业数据，请先生成！');
                        exit;
                end;
        end;

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

        for iRecNum := 0 to 19 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 19 do
        begin
                ListView2.Items[iRecNum].SubItems[6]:=Converter(ADOQuery1.FieldByName('实际').AsString);
                ADOQuery1.Next;
        end;

        Memo1.Lines.Delete(1);
        //-------查询备注信息----
        sqlString:='select 备注 from 公交行业统计月报表 where 统计年份=' + iYear + ' and 统计月份=' + iMonth
                  +' 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 TBusTradeCountMonFm.BitBtn2Click(Sender: TObject);
var iRow,iCol,iRecNum:integer;
begin
        if (DeviceDetect=0) then
        begin
                exit;
        end;

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

        PrintInit(ExtractFilePath(Application.ExeName),'BusCorMonInputTab');
        iRow := 1 ;
        iRecNum := 1 ;

        iCol := 4;
        for iRecNum := 0 to 80 do
        begin
                if (iRecNum>=0) and (iRecNum<=19) then
                begin
                        iRow := iRecNum+8;
                        iCol := 4;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum].SubItems[2]);

                end
                else if (iRecNum >= 20) and (iRecNum <= 39) then
                begin
                        iCol:= 8;
                        iRow:= iRecNum - 20+8;
                        WriteCell(iRow,iCol,ListView1.Items[iRecNum-20].SubItems[6]);

                end;
                if (iRecNum >= 40) and ( iRecNum <= 60)then
                begin
                        iCol:= 4;
                        iRow:= iRecNum - 10 + 8;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-40].SubItems[2]);

                end;
                if (iRecNum >= 61) and (iRecNum <= 80)then
                begin
                        iCol:= 8;
                        iRow:= iRecNum - 31+ 8;
                        WriteCell(iRow,iCol,ListView2.Items[iRecNum-61].SubItems[6]);

                end;

                case iRecNum of
                  0:
                  begin
                        WriteCell(iRow-4,1,'填报单位：客管办');
                        WriteCell(iRow-5,3,ComboBox1.Text + ' 年 ' + ComboBox2.Text + ' 月');
                        WriteCell(iRow+20,1,'统计负责人:' + PrnInfoFrm.StatMainPerson);
                        WriteCell(iRow+20,4,PrnInfoFrm.StatPerson);
                        WriteCell(iRow+20,6,'填报日期:' + DateToStr(PrnInfoFrm.StatDate));
                  end
                ;
                  40:
                  begin
                        WriteCell(iRow-4,1,'填报单位：客管办');
                        WriteCell(iRow-5,3,ComboBox1.Text + ' 年 ' + ComboBox2.Text + ' 月');
                        WriteCell(iRow+21,1,'统计负责人:' + PrnInfoFrm.StatMainPerson);
                        WriteCell(iRow+21,4,PrnInfoFrm.StatPerson);
                        WriteCell(iRow+21,6,'填报日期:' + DateToStr(PrnInfoFrm.StatDate));
                 end
                ;
                else ;
                end;

        end;

        PrintExcelShow;
        PrintPreview;
        CloseActiveBook;

end;

procedure TBusTradeCountMonFm.FormCreate(Sender: TObject);
begin
        OpenExcelApp
end;

procedure TBusTradeCountMonFm.FormDestroy(Sender: TObject);
begin
        CloseExcelApp
end;

end.