bustradecountmonunit.~pas

公交行业的管理系统

unit BusTradeCountMonUnit;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, Grids, DBGrids, Db, ADODB, Buttons, ComCtrls;

type
  TBusTradeCountMonFm = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    ADOCommand1: TADOCommand;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    ListView1: TListView;
    TabSheet2: TTabSheet;
    ListView2: TListView;
    Panel4: TPanel;
    Label4: TLabel;
    Label5: TLabel;
    ComboBox1: TComboBox;
    Panel5: TPanel;
    Label2: TLabel;
    ComboBox2: TComboBox;
    Label1: TLabel;
    ADOQuery1: TADOQuery;
    ADOQuery3: TADOQuery;
    Panel6: TPanel;
    BitBtn4: TBitBtn;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    Memo1: TMemo;
    Label3: TLabel;
    Label6: TLabel;
    //procedure Button1Click(Sender: TObject);
    //procedure Button2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    iUpdateFlag:Integer;
    iExistFlag:Integer;
    procedure WriteMonData();
    procedure UpdateMonData();
end;

var
  BusTradeCountMonFm: TBusTradeCountMonFm;

implementation

uses BusTradeDataModuleUnit, PrintProc, PrintInfoUnit;

{$R *.DFM}
procedure TBusTradeCountMonFm.UpdateMonData();
var
        iRow:Integer;
        iCol:Integer;
        sqlString:String;
        iYear:String;
        iMonth:String;
        iValue:String;
        iCode:String;
        iCode1:String;
begin
        iYear:=ComboBox1.Text;
        iMonth:=ComboBox2.Text;

        {更新横表}
        {sqlString:='insert into 公交行业统计月报表 (城市代码,统计年份,统计月份,生成方式) values('
                  +'''' + '022' + '''' + ',' + iYear + ',' + iMonth + ',' + '''' + '生成' + '''' + ')';
        ADOCommand1.CommandText:=sqlString;
        ADOCommand1.Execute;
        }
        sqlString:='select * from  公交行业统计月报表 where 城市代码='+'''' + '022' + ''''
                  + ' and 生成方式=' + '''' + '生成' + '''' + 'and 统计年份=' + iYear + ' and 统计月份=' + iMonth;
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        ADOQuery1.Edit;

        ADOQuery1.FieldByName('备注').Value:=Memo1.Lines.Strings[1];

        {更新写纵表}
        for iRow := 0 to 19 do
        begin
                iValue:=ListView1.Items[iRow].SubItems[2];
                iCode:='''' + ListView1.Items[iRow].SubItems[0] + '''';
                iCode1:=ListView1.Items[iRow].SubItems[0];
                //ADOQuery1.FieldByName(iCode).AsInteger:=iValue;
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                sqlString:= 'update 公交行业统计月报_z set 本月实际=' + iValue
                          + ' where 城市代码=' + '''' + '022' + '''' + ' and 统计年份=' + iYear
                          + ' and 统计月份=' + iMonth + ' and 指标代码= ' + iCode;

                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow:=0 to 19 do
        begin
                iValue:=ListView1.Items[iRow].SubItems[6];
                iCode:='''' + ListView1.Items[iRow].SubItems[4] + '''';
                iCode1:=ListView1.Items[iRow].SubItems[4];
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                {sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';}

                sqlString:= 'update 公交行业统计月报_z set 本月实际=' + iValue
                          + ' where 城市代码=' + '''' + '022' + '''' + ' and 统计年份=' + iYear
                          + ' and 统计月份=' + iMonth + ' and 指标代码= ' + iCode;


                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow := 0 to 20 do
        begin
                iValue:=ListView2.Items[iRow].SubItems[2];
                iCode:='''' + ListView2.Items[iRow].SubItems[0] + '''';
                iCode1:=ListView2.Items[iRow].SubItems[0];
                //ADOQuery1.FieldByName(iCode).AsFloat:=iValue;
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                {sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';}
                sqlString:= 'update 公交行业统计月报_z set 本月实际=' + iValue
                          + ' where 城市代码=' + '''' + '022' + '''' + ' and 统计年份=' + iYear
                          + ' and 统计月份=' + iMonth + ' and 指标代码= ' + iCode;


                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow:=0 to 19 do
        begin
                iValue:=ListView2.Items[iRow].SubItems[6];
                iCode:='''' + ListView2.Items[iRow].SubItems[4] + '''';
                iCode1:=ListView2.Items[iRow].SubItems[4];
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                {sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';}

                sqlString:= 'update 公交行业统计月报_z set 本月实际=' + iValue
                          + ' where 城市代码=' + '''' + '022' + '''' + ' and 统计年份=' + iYear
                          + ' and 统计月份=' + iMonth + ' and 指标代码= ' + iCode;


                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;

        ADOQuery1.Post;

end;
procedure TBusTradeCountMonFm.WriteMonData();
var
        iRow:Integer;
        iCol:Integer;
        sqlString:String;
        iYear:String;
        iMonth:String;
        iValue:String;
        iCode:String;
        iCode1:String;
begin
        iYear:=ComboBox1.Text;
        iMonth:=ComboBox2.Text;

        {写横表}
        sqlString:='insert into 公交行业统计月报表 (城市代码,统计年份,统计月份,生成方式,备注) values('
                  +'''' + '022' + '''' + ',' + iYear + ',' + iMonth + ',' + '''' + '生成' + ''''
                  +',' + '''' + Memo1.Lines.Strings[1] + '''' +')';
        ADOCommand1.CommandText:=sqlString;
        ADOCommand1.Execute;

        sqlString:='select * from  公交行业统计月报表 where 城市代码='+'''' + '022' + ''''
                  + ' and 生成方式=' + '''' + '生成' + '''' + 'and 统计年份=' + iYear + ' and 统计月份=' + iMonth;
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add(sqlString);
        ADOQuery1.Open;

        ADOQuery1.Edit;

        {写纵表}
        for iRow := 0 to 19 do
        begin
                iValue:=ListView1.Items[iRow].SubItems[2];
                iCode:='''' + ListView1.Items[iRow].SubItems[0] + '''';
                iCode1:=ListView1.Items[iRow].SubItems[0];
                //ADOQuery1.FieldByName(iCode).AsInteger:=iValue;
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';

                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow:=0 to 19 do
        begin
                iValue:=ListView1.Items[iRow].SubItems[6];
                iCode:='''' + ListView1.Items[iRow].SubItems[4] + '''';
                iCode1:=ListView1.Items[iRow].SubItems[4];
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';

                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow := 0 to 20 do
        begin
                iValue:=ListView2.Items[iRow].SubItems[2];
                iCode:='''' + ListView2.Items[iRow].SubItems[0] + '''';
                iCode1:=ListView2.Items[iRow].SubItems[0];

                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';

                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;
        for iRow:=0 to 19 do
        begin
                iValue:=ListView2.Items[iRow].SubItems[6];
                iCode:='''' + ListView2.Items[iRow].SubItems[4] + '''';
                iCode1:=ListView2.Items[iRow].SubItems[4];
                {if (iRow >=0) and (iRow <=7) then
                begin
                        ADOQuery1.FieldByName(iCode).AsFloat:=iValue;
                end
                else if (iRow >=8) and (iRow<=9) then
                begin
                        ADOQuery1.FieldByName(iCode).AsInteger:=iValue;
                end
                else if (iRow=10) or (iRow=14) then
                begin
                        ADOQuery1.FieldByName(iCode).AsFloat:=iValue;
                end
                else if (iRow>=11) and (iRow <=13) then
                begin
                        ADOQuery1.FieldByName(iCode).AsInteger:=iValue;
                end
                else if (iRow>=15) and (iRow <=19) then
                begin
                        ADOQuery1.FieldByName(iCode).AsInteger:=iValue;
                end;
                }
                ADOQuery1.FieldByName(iCode1).Value:=iValue;

                sqlString:= 'insert into 公交行业统计月报_z (城市代码,统计年份,统计月份,指标代码,本月实际) '
                          + ' values('+ '''' + '022' + '''' + ',' + iYear + ',' + iMonth + ','
                          + iCode + ',' + iValue + ')';

                ADOCommand1.CommandText:=sqlString;
                ADOCommand1.Execute;
        end;

        ADOQuery1.Post;
        
        {for iCol=0 to  80 do
        begin
                if (iCol >=0) and (iCol<=19) then
                begin
                        ADOQuery1.FieldByName()
                end
                else if (iCol>=20) and (iCol <=39) then
                begin
                end
                else if (iCol>=40) and (iCol <=60) then
                begin
                end
                else if (iCol>=61) and (iCol <=80) then
                begin
                end;
        end;}

end;
{
procedure TBusTradeCountMonFm.Button1Click(Sender: TObject);
var
        iMonth:String;
        iYear:String;
        sqlString:String;
begin
        //生成需要汇总数据的年份和月份信息
        iMonth:=Edit2.Text;
        iYear:=Edit1.Text;

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

        //如果数据存在，直接显示；否则，生成数据并显示
        if ADOQuery1.RecordCount<>0 then
        begin
                ShowMessage('该年该月的行业数据已存在！');
                DBGrid1.DataSource:= DataSource1;
        end
        else
        begin
                //判断企业数据中是否存在当年当月的数据
                sqlString:='select 指标代码 from 公交企业统计月报表_z where 统计年份='
                        + iYear + ' and 统计月份=' + iMonth;
                ADOQuery3.Close;
                ADOQuery3.SQL.Clear;
                ADOQuery3.SQL.Add(sqlString);
                ADOQuery3.Open;

                //如果不存在企业数据，录入；否则，汇总、显示
                if ADOQuery3.RecordCount=0 then
                begin
                        ShowMessage('不存在本年本月的企业月报数据,请先录入!');
                        exit;
                end
                else
                begin
                        sqlString:='insert into 公交行业统计月报_z (统计年份,统计月份,指标代码,本年本月实际) '
                                + ' select ' + iYear + ',' + iMonth + ', 指标代码,sum(本月实际)'
                                + ' from 公交企业统计月报表_z where 统计年份= ' + iYear + ' and 统计月份= '
                                + iMonth + ' group by 指标代码';
                        ADOCommand1.CommandText:=sqlString;
                        ADOCommand1.Execute;

                        sqlString:= 'select 指标代码,本年本月实际 as 本月实际 from 公交行业统计月报_z where 统计年份='
                        + iYear + ' and 统计月份 = ' + iMonth;

                        ADOQuery1.Close;
                        ADOQuery1.SQL.Clear;
                        ADOQuery1.SQL.Add(sqlString);
                        ADOQuery1.Open;
                        DBGrid1.DataSource:= DataSource1;
                end;
        end;

end;
}
{procedure TBusTradeCountMonFm.Button2Click(Sender: TObject);
var
        iMonth:String;
        iYear:String;
        sqlString:String;

begin
        //生成需要汇总数据的年份和月份信息
        iMonth:=Edit2.Text;
        iYear:=Edit1.Text;

        //查询判断该年该月数据是否已经存在
        sqlString:='SELECT sum(a.本年本月实际) AS 累计, a.指标代码, max(b.本年本月实际) AS 本月实际 '
                +' FROM 公交行业统计月报_z AS a, 公交行业统计月报_z AS b'
                +' WHERE a.统计年份=' + iYear + ' and a.统计月份<= ' + iMonth
                +' and b.统计年份=' + iYear + ' and b.统计月份=' + iMonth
                +' and b.指标代码=a.指标代码 GROUP BY a.指标代码';

        ADOQuery4.Close;
        ADOQuery4.SQL.Clear;
        ADOQuery4.SQL.Add(sqlString);