?? 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' + '''';
//查詢判斷該年該月數據是否已經存在
+' 城市代碼=' + 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.
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -