?? 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.
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -