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;
    iUpdateFlagH:Integer;
    procedure SpecMonData;
    procedure WriteMonData();
    procedure UpdateMonData();
end;

var
  BusTradeCountMonFm: TBusTradeCountMonFm;

implementation

uses BusTradeDataModuleUnit, PrintProc, PrintInfoUnit;

{$R *.DFM}
procedure TBusTradeCountMonFm.SpecMonData;
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;
        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:= '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:= '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:= 'update 公交行业统计月报_z set 本月实际=' + iValue
                          + ' where 城市代码=' + '''' + '022' + '''' + ' and 统计年份=' + iYear
                          + ' and 统计月份=' + iMonth + ' and 指标代码= ' + iCode;


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

        ADOQuery1.Post;

end;

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);