📄 bustradeyearrepunit.~pas
字号:
unit BusTradeYearRepUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComCtrls, StdCtrls, Buttons, ExtCtrls, ADODB, DB;
type
TBusTradeYearRepFrm = class(TForm)
Panel1: TPanel;
Panel7: TPanel;
Panel2: TPanel;
Panel8: TPanel;
Label6: TLabel;
Panel3: TPanel;
Label2: TLabel;
Label4: TLabel;
ComboBox1: TComboBox;
Panel4: TPanel;
BitBtn1: TBitBtn;
BitBtn3: TBitBtn;
BitBtn4: TBitBtn;
BitBtn2: TBitBtn;
ADOQuery1: TADOQuery;
ADOQuery3: TADOQuery;
ADOCommand1: TADOCommand;
Panel5: TPanel;
PageControl1: TPageControl;
TabSheet1: TTabSheet;
ListView1: TListView;
TabSheet2: TTabSheet;
ListView2: TListView;
Memo1: TMemo;
Label1: TLabel;
Label3: TLabel;
Label5: TLabel;
procedure BitBtn3Click(Sender: TObject);
procedure BitBtn4Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure BitBtn1Click(Sender: TObject);
procedure BitBtn2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
iExistFlag:Integer;
iUpdateFlag:Integer;
iUpdateFlagH:Integer;
procedure SpecYearData;
procedure WriteMonData();
procedure UpdateMonData();
end;
var
BusTradeYearRepFrm: TBusTradeYearRepFrm;
implementation
uses BusTradeDataModuleUnit, PrintInfoUnit, PrintProc;
{$R *.dfm}
procedure TBusTradeYearRepFrm.SpecYearData;
var
iRow:Integer;
iCol:Integer;
sqlString:String;
iYear:String;
iValue:String;
iCode:String;
iCode1:String;
begin
iYear:=ComboBox1.Text;
{写横表}
sqlString:='insert into 公交行业统计年报表 (城市代码,统计年份,生成方式,备注) values('
+'''' + '022' + '''' + ',' + iYear + ',' + '''' + '生成' + ''''
+','+ '''' + Memo1.Lines.Strings[0]+'''' + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
sqlString:='select * from 公交行业统计年报表 where 城市代码='+'''' + '022' + ''''
+ ' and 生成方式=' + '''' + '生成' + '''' + 'and 统计年份=' + iYear;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(sqlString);
ADOQuery1.Open;
ADOQuery1.Edit;
ADOQuery1.FieldByName('备注').Value:=Memo1.Lines.Strings[1];
{更新写纵表}
for iRow := 0 to 22 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 22 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 指标代码= ' + 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 18 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
ADOQuery1.Post;
end;
procedure TBusTradeYearRepFrm.UpdateMonData();
var
iRow:Integer;
iCol:Integer;
sqlString:String;
iYear:String;
iValue:String;
iCode:String;
iCode1:String;
begin
iYear:=ComboBox1.Text;
{更新横表}
{sqlString:='insert into 公交行业统计月报表 (城市代码,统计年份,统计月份,生成方式) values('
+'''' + '022' + '''' + ',' + iYear + ',' + iMonth + ',' + '''' + '生成' + '''' + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
}
sqlString:='select * from 公交行业统计年报表 where 城市代码='+'''' + '022' + ''''
+ ' and 生成方式=' + '''' + '生成' + '''' + 'and 统计年份=' + iYear;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(sqlString);
ADOQuery1.Open;
ADOQuery1.Edit;
ADOQuery1.FieldByName('备注').Value:=Memo1.Lines.Strings[1];
{更新写纵表}
for iRow := 0 to 22 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 22 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 指标代码= ' + 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 18 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 指标代码= ' + iCode;
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
ADOQuery1.Post;
end;
procedure TBusTradeYearRepFrm.WriteMonData();
var
iRow:Integer;
iCol:Integer;
sqlString:String;
iYear:String;
iValue:String;
iCode:String;
iCode1:String;
begin
iYear:=ComboBox1.Text;
{写横表}
sqlString:='insert into 公交行业统计年报表 (城市代码,统计年份,生成方式,备注) values('
+'''' + '022' + '''' + ',' + iYear + ',' + '''' + '生成' + ''''
+','+ '''' + Memo1.Lines.Strings[0]+'''' + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
sqlString:='select * from 公交行业统计年报表 where 城市代码='+'''' + '022' + ''''
+ ' and 生成方式=' + '''' + '生成' + '''' + 'and 统计年份=' + iYear;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(sqlString);
ADOQuery1.Open;
ADOQuery1.Edit;
{写纵表}
for iRow := 0 to 22 do
begin
iValue:=ListView1.Items[iRow].SubItems[2];
iCode:='''' + ListView1.Items[iRow].SubItems[0] + '''';
iCode1:=ListView1.Items[iRow].SubItems[0];
ADOQuery1.FieldByName(iCode1).Value:=iValue;
sqlString:= 'insert into 公交行业统计年报_z (城市代码,统计年份,指标代码,全年实际) '
+ ' values('+ '''' + '022' + '''' + ',' + iYear + ','
+ iCode + ',' + iValue + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 22 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 + ','
+ 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 + ','
+ iCode + ',' + iValue + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
for iRow:=0 to 18 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 + ','
+ iCode + ',' + iValue + ')';
ADOCommand1.CommandText:=sqlString;
ADOCommand1.Execute;
end;
ADOQuery1.Post;
end;
procedure TBusTradeYearRepFrm.BitBtn3Click(Sender: TObject);
begin
BusTradeYearRepFrm.Close;
end;
procedure TBusTradeYearRepFrm.BitBtn4Click(Sender: TObject);
var
iYear:String;
sqlString:String;
iRecNum:Integer;
iCityCode:String;
begin
iExistFlag:=0;
iYear:=ComboBox1.Text;
if (iYear<'1990') or (iYear >'2030') then
begin
ShowMessage('请输入在时间范围1990-2030内的合法年份!');
exit;
end;
iCityCode:='''' + '022' + '''';
//查询判断该年该月数据是否已经存在
sqlString:='select 指标代码,全年实际 as 实际 from 公交行业统计年报_z where '
+' 城市代码=' + iCityCode + ' and 统计年份='
+ iYear + ' order by 指标代码';
ADOQuery1.Close;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -