IST0080MF.cpp 51 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131
  1. //---------------------------------------------------------------------------
  2. #include <vcl.h>
  3. #include "ITSSkinF.h"
  4. #include "ITSUtilF.h"
  5. #include "ITSDbF.h"
  6. #include "AppGlobalF.h"
  7. #include "ITSLangTransF.h"
  8. #pragma hdrstop
  9. #include "IST0080MF.h"
  10. //---------------------------------------------------------------------------
  11. #pragma package(smart_init)
  12. #pragma link "cxButtons"
  13. #pragma link "cxClasses"
  14. #pragma link "cxContainer"
  15. #pragma link "cxControls"
  16. #pragma link "cxCustomData"
  17. #pragma link "cxData"
  18. #pragma link "cxDataStorage"
  19. #pragma link "cxDBData"
  20. #pragma link "cxEdit"
  21. #pragma link "cxFilter"
  22. #pragma link "cxGraphics"
  23. #pragma link "cxGrid"
  24. #pragma link "cxGridBandedTableView"
  25. #pragma link "cxGridCustomTableView"
  26. #pragma link "cxGridCustomView"
  27. #pragma link "cxGridDBBandedTableView"
  28. #pragma link "cxGridLevel"
  29. #pragma link "cxGridTableView"
  30. #pragma link "cxLabel"
  31. #pragma link "cxLookAndFeelPainters"
  32. #pragma link "cxLookAndFeels"
  33. #pragma link "cxPC"
  34. #pragma link "cxPCdxBarPopupMenu"
  35. #pragma link "cxSpinEdit"
  36. #pragma link "cxSplitter"
  37. #pragma link "cxStyles"
  38. #pragma link "cxTextEdit"
  39. #pragma link "dxSkinBlack"
  40. #pragma link "dxSkinBlue"
  41. #pragma link "dxSkinsCore"
  42. #pragma link "dxSkinscxPCPainter"
  43. #pragma resource "*.dfm"
  44. TIST0080M *IST0080M = NULL;
  45. //---------------------------------------------------------------------------
  46. __fastcall TIST0080M::TIST0080M(TComponent* Owner)
  47. : TForm(Owner)
  48. {
  49. LangTrans->Translate(this, ITSDb_GetConnection());
  50. ITSSkin_Load(this);
  51. LoadLocalSkin();
  52. CMM_LoadForm(g_sFormsDir, this);
  53. FTitle = Caption;//"간선도로 통계";
  54. LoadConfig();
  55. m_sLinkLevel = "1";
  56. MyItsAtrdManager = new TItsAtrdManager();
  57. MyItsAtrdManager->LoadFromDb();
  58. MyItsAtrdManager->LoadAtrdLength();
  59. }
  60. //---------------------------------------------------------------------------
  61. /*
  62. * 환경설정정보 로딩
  63. *
  64. * arguments
  65. *
  66. * return
  67. * void
  68. */
  69. void __fastcall TIST0080M::LoadConfig()
  70. {
  71. m_sFooterRight = "";//GetConfigValue("APPLICATION", "REPORTRIGHT", "");
  72. }
  73. //---------------------------------------------------------------------------
  74. /*
  75. * 공통으로 처리되지 않는 스킨을 변경한다.
  76. * arguments
  77. *
  78. * return
  79. * void
  80. */
  81. void __fastcall TIST0080M::LoadLocalSkin()
  82. {
  83. /*
  84. * 스킨 변경
  85. */
  86. PgQuery->ParentColor = true;
  87. TsDay->ParentColor = true;
  88. TsMon->ParentColor = true;
  89. TsYear->ParentColor = true;
  90. }
  91. //---------------------------------------------------------------------------
  92. /*
  93. * form이 메모리에서 생성될때 호출되는 생성자 이벤트 핸들러
  94. * arguments
  95. * Sender : event handler 객체
  96. * return
  97. * void
  98. */
  99. void __fastcall TIST0080M::FormCreate(TObject *Sender)
  100. {
  101. for (int ii = 0; ii < MAX_CHART; ii++)
  102. {
  103. m_pFrmChart[ii] = new TPlugInChart(this);
  104. switch(ii)
  105. {
  106. case 0:
  107. m_pFrmChart[ii]->Parent = PnlDayGraph;
  108. m_pFrmChart[ii]->SetChartInfo("", lblText1->Caption, lblText4->Caption);//"시간", "평균속도");
  109. break;
  110. case 1:
  111. m_pFrmChart[ii]->Parent = PnlMonGraph;
  112. m_pFrmChart[ii]->SetChartInfo("", lblText2->Caption, lblText4->Caption);//"일", "평균속도");
  113. break;
  114. case 2:
  115. m_pFrmChart[ii]->Parent = PnlYearGraph;
  116. m_pFrmChart[ii]->SetChartInfo("", lblText3->Caption, lblText4->Caption);//"월", "평균속도");
  117. break;
  118. }
  119. m_pFrmChart[ii]->Show();
  120. }
  121. PgList->HideTabs = true;
  122. }
  123. //---------------------------------------------------------------------------
  124. /*
  125. * form을 보여줄때 호출되는 event 메서드이다.
  126. * arguments
  127. * Sender : event handler 객체
  128. * return
  129. * void
  130. */
  131. void __fastcall TIST0080M::FormShow(TObject *Sender)
  132. {
  133. PgList->ActivePageIndex = 0;
  134. PgQuery->ActivePageIndex= 0;
  135. //DMMain->InsertListHeaderMenuItem(CxDay, (TcxGridDBTableView*)TvDay);
  136. // form 초기화
  137. FormInit();
  138. }
  139. //---------------------------------------------------------------------------
  140. /*
  141. * form 초기화
  142. * arguments
  143. *
  144. * return
  145. * void
  146. */
  147. void __fastcall TIST0080M::FormInit()
  148. {
  149. DtDay->Date = IncHour(Now(), -24);
  150. DtMon->Date = StartOfTheMonth(IncHour(Now(), -24*30));
  151. DtYear->Date = IncHour(Now(), -24);
  152. DtStTime->Time = StrToDateTime("00:00");
  153. DtEdTime->Time = StrToDateTime("23:59");
  154. ADOD->Connection = ITSDb_GetConnection();
  155. ADOM->Connection = ITSDb_GetConnection();
  156. ADOY->Connection = ITSDb_GetConnection();
  157. TvDay->DataController->DataSource = DSD;
  158. TvMon->DataController->DataSource = DSM;
  159. TvYear->DataController->DataSource = DSY;
  160. //FillStatComboBox(CbStatCfg, "IST0810M");
  161. }
  162. //---------------------------------------------------------------------------
  163. /*
  164. * 닫기버튼 이나 x버튼 클릭시 공통으로 처리하는 로직이들어간다..
  165. * Form과 DataModule class를 delete시킨다.
  166. * arguments
  167. *
  168. * return
  169. * void
  170. */
  171. void __fastcall TIST0080M::CommClose()
  172. {
  173. try
  174. {
  175. CMM_SaveForm(g_sFormsDir, this);
  176. //DeleteStatComboBox(CbStatCfg);
  177. TvDay->BeginUpdate();
  178. ADOD->Close();
  179. TvDay->EndUpdate();
  180. TvMon->BeginUpdate();
  181. ADOM->Close();
  182. TvMon->EndUpdate();
  183. TvYear->BeginUpdate();
  184. ADOY->Close();
  185. TvYear->EndUpdate();
  186. if (MyItsAtrdManager)
  187. {
  188. delete MyItsAtrdManager;
  189. MyItsAtrdManager = NULL;
  190. }
  191. }
  192. catch(...)
  193. {
  194. }
  195. }
  196. //---------------------------------------------------------------------------
  197. /*
  198. * 화면이 로딩된 후 리스트 목록을 조회하도록 한다.
  199. * arguments
  200. *
  201. * return
  202. * void
  203. */
  204. void __fastcall TIST0080M::TmrRefreshTimer(TObject *Sender)
  205. {
  206. TmrRefresh->Enabled = false;
  207. }
  208. //---------------------------------------------------------------------------
  209. /*
  210. * 통계구분 탭 변경시 발생하는 이벤트 핸들러
  211. * arguments
  212. *
  213. * return
  214. * void
  215. */
  216. void __fastcall TIST0080M::PgQueryChange(TObject *Sender)
  217. {
  218. //Tab Change
  219. PgList->ActivePageIndex = PgQuery->ActivePageIndex;
  220. switch(PgQuery->ActivePageIndex)
  221. {
  222. case 0: DtDay->SetFocus(); break;
  223. case 1: DtMon->SetFocus(); break;
  224. case 2: DtYear->SetFocus(); break;
  225. default: break;
  226. }
  227. }
  228. //---------------------------------------------------------------------------
  229. /*
  230. * Graph Button Click Event Handler
  231. * arguments
  232. * Sender : event handler 객체
  233. * return
  234. * void
  235. */
  236. void __fastcall TIST0080M::BtnGraphClick(TObject *Sender)
  237. {
  238. // 차트를 다른 창에서 보여줘야 할 경우 Frm1121100_1F.cpp 내 소스 참조
  239. }
  240. //---------------------------------------------------------------------------
  241. /*
  242. * Print Button Click Event Handler
  243. * arguments
  244. * Sender : event handler 객체
  245. * return
  246. * void
  247. */
  248. void __fastcall TIST0080M::BtnPrintClick(TObject *Sender)
  249. {
  250. TcxGrid *pGrid = NULL;
  251. TcxGridDBBandedTableView *pView = NULL;
  252. TLabel *LblQry= NULL;
  253. String sTitle;
  254. switch(PgQuery->ActivePageIndex)
  255. {
  256. case 0: pGrid = CxDay; pView = TvDay;
  257. LblQry = LblQryTm;
  258. break;
  259. case 1: pGrid = CxMon; pView = TvMon;
  260. LblQry = LblQryDay;
  261. break;
  262. case 2: pGrid = CxYear; pView = TvYear;
  263. LblQry = LblQryYear;
  264. break;
  265. default: return;
  266. }
  267. sTitle = LblRptTitle->Caption + "\r\n" + LblQry->Caption;
  268. if (pView->DataController->RecordCount == 0)
  269. {
  270. ShowMessage(FrmLang->lblPrintNoData->Caption);//"인쇄 할 데이터가 없습니다.");
  271. return;
  272. }
  273. //ComponentPrinterLink->OptionsView->Footers = true;
  274. //ComponentPrinterLink->OptionsView->GroupFooters = true;
  275. //ComponentPrinterLink->OptionsSize->AutoWidth = true;
  276. //CMM_PrintList(pGrid, sTitle, m_sFooterRight);
  277. pView->DataController->FocusedRecordIndex = 0;
  278. }
  279. //---------------------------------------------------------------------------
  280. /*
  281. * Excel Button Click Event Handler
  282. * arguments
  283. * Sender : event handler 객체
  284. * return
  285. * void
  286. */
  287. void __fastcall TIST0080M::BtnExlSaveClick(TObject *Sender)
  288. {
  289. TcxGrid *pGrid = NULL;
  290. TcxGridDBBandedTableView *pView = NULL;
  291. TLabel *LblQry= NULL;
  292. String sTitle;
  293. switch(PgQuery->ActivePageIndex)
  294. {
  295. case 0: pGrid = CxDay; pView = TvDay;
  296. LblQry = LblQryTm;
  297. break;
  298. case 1: pGrid = CxMon; pView = TvMon;
  299. LblQry = LblQryDay;
  300. break;
  301. case 2: pGrid = CxYear; pView = TvYear;
  302. LblQry = LblQryYear;
  303. break;
  304. default: return;
  305. }
  306. sTitle = LblRptTitle->Caption + LblQry->Caption;
  307. if (pView->DataController->RecordCount == 0)
  308. {
  309. ShowMessage(FrmLang->lblSaveNoData->Caption);//"저장 할 데이터가 없습니다.");
  310. return;
  311. }
  312. CMM_ExportToExcelFile(sTitle, pGrid, pView, this);
  313. }
  314. //---------------------------------------------------------------------------
  315. /*
  316. * Refresh Button Click Event Handler
  317. * arguments
  318. * Sender : event handler 객체
  319. * return
  320. * void
  321. */
  322. void __fastcall TIST0080M::BtnSearchClick(TObject *Sender)
  323. {
  324. Application->ProcessMessages();
  325. TSqlCursor sqlCrs((TControl*)BtnSearch);
  326. m_sLinkLevel = CbLinkLevel->Text;
  327. Application->ProcessMessages();
  328. RefreshData();
  329. }
  330. //---------------------------------------------------------------------------
  331. /*
  332. * Refresh Data Event Function
  333. * arguments
  334. *
  335. * return
  336. * void
  337. */
  338. void __fastcall TIST0080M::RefreshData()
  339. {
  340. TcxGridDBBandedTableView *pView = NULL;
  341. TLabel *LblQry= NULL;
  342. TLabel *LblRecords= NULL;
  343. WORD nYear, nMonth, nDay, nHour, nMin, nSec, nMSec;
  344. int nDispCols;
  345. String sBandText = "";
  346. String sMsgTitle, sMsgString;
  347. TDateTimePicker *DtQryTm;
  348. int nStYear, nEdYear;
  349. switch(PgQuery->ActivePageIndex)
  350. {
  351. case 0:
  352. pView = TvDay;
  353. DtQryTm = DtDay;
  354. LblQry = LblQryTm;
  355. LblRecords = LblRecordsTm;
  356. sBandText = "(" + DtDay->Date.FormatString("yyyy년 mm월 dd일") + ")";
  357. nDispCols = 24;
  358. if (StartOfTheDay(Now()) <= DtDay->DateTime)
  359. {
  360. DecodeTime(Now(), nHour, nMin, nSec, nMSec);
  361. if (nMin > 5) nDispCols = nHour;
  362. else nDispCols = nHour-1;
  363. }
  364. break;
  365. case 1:
  366. pView = TvMon;
  367. DtQryTm = DtMon;
  368. LblQry = LblQryDay;
  369. LblRecords = LblRecordsDay;
  370. sBandText = "(" + DtMon->Date.FormatString("yyyy년 mm월") + ")";
  371. nDispCols = ITSUtil_GetMonthLastDay(AnsiString(DtMon->Date.FormatString("yyyymm")).c_str());
  372. if (StartOfTheDay(Now()) <= EndOfTheMonth(DtMon->DateTime))
  373. {
  374. DecodeDate(Now(), nYear, nMonth, nDay);
  375. if (nDay < nDispCols) nDispCols = nDay-1;
  376. }
  377. {
  378. String sStDateTime = DtStTime->Time.FormatString("hhnn");
  379. String sEdDateTime = DtEdTime->Time.FormatString("hhnn");
  380. if (sStDateTime > sEdDateTime)
  381. {
  382. Application->MessageBox(FrmLang->lblQryDtErr->Caption.c_str(),//L"검색 시작시각이 검색 종료시각 보다 큽니다.",
  383. FTitle.c_str(), MB_OK|MB_ICONWARNING|MB_APPLMODAL);
  384. return;
  385. }
  386. }
  387. break;
  388. case 2:
  389. pView = TvYear;
  390. DtQryTm = DtYear;
  391. LblQry = LblQryYear;
  392. LblRecords = LblRecordsYear;
  393. sBandText = "(" + DtYear->Date.FormatString("yyyy년") + ")";
  394. nDispCols = 12;
  395. if (Now().FormatString("yyyy") == DtYear->DateTime.FormatString("yyyy"))
  396. {
  397. DecodeDate(Now(), nYear, nMonth, nDay);
  398. nDispCols = nMonth;
  399. }
  400. break;
  401. default: return;
  402. }
  403. if (!pView) return;
  404. if (DtQryTm->DateTime.FormatString("yyyymmdd").ToInt() > Now().FormatString("yyyymmdd").ToInt())
  405. {
  406. Application->MessageBox(FrmLang->lblCnfmStatTm->Caption.c_str(),//L"통계검색 시각 정보를 확인하세요.",
  407. FTitle.c_str(), MB_OK|MB_ICONWARNING|MB_APPLMODAL);
  408. MessageDlg(sMsgString.c_str(), mtInformation, TMsgDlgButtons() << mbOK, NULL);
  409. DtQryTm->SetFocus();
  410. nDispCols = 0; /* 리턴하지 않는 경우 모든 컬럼을 숨긴다. */
  411. return;
  412. }
  413. LblQry->Caption = sBandText;
  414. CMM_ShowListColumn(pView, nDispCols);
  415. TWaitCursor crs;
  416. try
  417. {
  418. pView->BeginUpdate();
  419. switch(PgQuery->ActivePageIndex)
  420. {
  421. case 0: DispDayStatistics(); break;
  422. case 1: DispMonthStatistics(); break;
  423. case 2: DispYearStatistics(); break;
  424. default: return;
  425. }
  426. }
  427. __finally
  428. {
  429. pView->EndUpdate();
  430. //pView->ApplyBestFit(NULL, false, false);
  431. LblRecords->Caption = FormatFloat("##,##0", pView->DataController->RecordCount) + FrmLang->lblEA->Caption;//" 건";
  432. String sEnd = FrmLang->lblQrySel->Caption + " [" + LblRecords->Caption + "]";//"데이터 " + LblRecords->Caption + " 이 조회 되었습니다.";
  433. Application->MessageBox(sEnd.c_str(),
  434. FrmLang->lblQryEnd->Caption.c_str(),//L"데이터 조회 완료",
  435. MB_OK|MB_ICONINFORMATION|MB_APPLMODAL);
  436. }
  437. DrawChart(sBandText);
  438. }
  439. //---------------------------------------------------------------------------
  440. /*
  441. * 조회한 데이터를 기준으로 차트를 그린다.
  442. * arguments
  443. *
  444. * return
  445. * void
  446. */
  447. void __fastcall TIST0080M::DrawChart(String sTitle)
  448. {
  449. TcxGridDBBandedTableView *pView = NULL;
  450. String sAxisX, sAxisY;
  451. int nAxisY1, nAxisY2, nAxisY3, nStColIdx, nEdColIdx, nTotColIdx;
  452. nAxisY3 = 0;
  453. nEdColIdx = 0;
  454. nTotColIdx = -1;
  455. switch(PgQuery->ActivePageIndex)
  456. {
  457. case 0: pView = TvDay;
  458. nAxisY1 = vDayColumnNo->Index;
  459. nAxisY2 = vDayColumnTot->Index;
  460. nAxisY3 = TvDayColumn1->Index;
  461. nEdColIdx = cxGridDBBandedColumn54->Index;
  462. nStColIdx = vDayColumnAvg->Index;//vDayColumn1->Index;
  463. break;
  464. case 1: pView = TvMon;
  465. nAxisY1 = vMonColumnNo->Index;
  466. nAxisY2 = vMonColumnTot->Index;
  467. nAxisY3 = TvMonColumn1->Index;
  468. nEdColIdx = vMonColumn31->Index;
  469. nStColIdx = vMonColumnAvg->Index;//vMonColumn1->Index;
  470. break;
  471. case 2: pView = TvYear;
  472. nAxisY1 = vYearColumnNo->Index;
  473. nAxisY2 = vYearColumnTot->Index;
  474. nAxisY3 = TvYearColumn1->Index;
  475. nEdColIdx = vYearColumn12->Index;
  476. nStColIdx = vYearColumnAvg->Index;//vYearColumn1->Index;
  477. break;
  478. default: return;
  479. }
  480. /*
  481. * Chart draw
  482. */
  483. try
  484. {
  485. m_pFrmChart[PgQuery->ActivePageIndex]->Clear();
  486. if (pView->DataController->RecordCount == 0)
  487. {
  488. //ShowMessage("표출 할 데이터가 없습니다.");
  489. return;
  490. }
  491. m_pFrmChart[PgQuery->ActivePageIndex]->BeginDraw("", "", "");
  492. //m_pFrmChart[PgQuery->ActivePageIndex]->DrawChart4((TcxGridDBTableView *)pView, nAxisY1, nAxisY2, nStColIdx, nTotColIdx);
  493. m_pFrmChart[PgQuery->ActivePageIndex]->DrawChart5((TcxGridDBTableView *)pView, nAxisY1, nAxisY2, nAxisY3, nStColIdx, nEdColIdx, nTotColIdx);
  494. m_pFrmChart[PgQuery->ActivePageIndex]->EndDraw();
  495. }
  496. catch(...)
  497. {
  498. }
  499. }
  500. //---------------------------------------------------------------------------
  501. /*
  502. * 시간대별 일 통계 데이터 조회/출력
  503. * arguments
  504. *
  505. * return
  506. * void
  507. */
  508. void __fastcall TIST0080M::DispDayStatistics()
  509. {
  510. String sQry;
  511. String sDD;
  512. String sFrom, sTo;
  513. String sTmp;
  514. int ii;
  515. sDD = DtDay->Date.FormatString("yyyyMMdd");
  516. sFrom = sDD + "000000";
  517. sTo = sDD + "235959";
  518. sQry =
  519. "SELECT LY.ARTERYID, LY.ARTERYNAME, LY.DIRECTION, LY.DIRECTIONNAME, LY.ARTERYLENGHT, LX.TRFTYPE, \r\n"
  520. " DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD, \r\n"
  521. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T00, 0, '-', ROUND(L00*3.6/T00, 0))) AS C00, \r\n"
  522. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01, \r\n"
  523. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02, \r\n"
  524. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03, \r\n"
  525. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04, \r\n"
  526. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05, \r\n"
  527. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06, \r\n"
  528. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07, \r\n"
  529. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08, \r\n"
  530. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09, \r\n"
  531. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10, \r\n"
  532. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11, \r\n"
  533. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12, \r\n"
  534. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13, \r\n"
  535. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14, \r\n"
  536. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15, \r\n"
  537. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16, \r\n"
  538. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17, \r\n"
  539. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18, \r\n"
  540. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19, \r\n"
  541. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20, \r\n"
  542. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21, \r\n"
  543. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22, \r\n"
  544. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23 \r\n"
  545. " FROM ( \r\n"
  546. "SELECT GROUPING(ARTERYID) AS GROUPING_ARTERYID, \r\n"
  547. " GROUPING(DIRECTION) AS GROUPING_DIRECTION, \r\n"
  548. " GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, \r\n"
  549. " CASE WHEN GROUPING(ARTERYID ) = 1 THEN '도로' ELSE ARTERYID END AS GROUP_ARTERYID, \r\n"
  550. " CASE WHEN GROUPING(DIRECTION) = 1 THEN '방향' ELSE DIRECTION END AS GROUP_DIRECTION, \r\n"
  551. " CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, \r\n"
  552. " ARTERYID, DIRECTION, TRFTYPE, \r\n"
  553. " SUM(DECODE(TRVTM, NULL, 0, LINKLEN)) TLL, \r\n"
  554. " SUM(DECODE(TRVTM, NULL, 0, TRVTM )) TTM, \r\n"
  555. " SUM(DECODE(STATHOUR, '00', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T00, \r\n"
  556. " SUM(DECODE(STATHOUR, '00', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L00, \r\n"
  557. " SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T01, \r\n"
  558. " SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01, \r\n"
  559. " SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T02, \r\n"
  560. " SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02, \r\n"
  561. " SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T03, \r\n"
  562. " SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03, \r\n"
  563. " SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T04, \r\n"
  564. " SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04, \r\n"
  565. " SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T05, \r\n"
  566. " SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05, \r\n"
  567. " SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T06, \r\n"
  568. " SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06, \r\n"
  569. " SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T07, \r\n"
  570. " SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07, \r\n"
  571. " SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T08, \r\n"
  572. " SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08, \r\n"
  573. " SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T09, \r\n"
  574. " SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09, \r\n"
  575. " SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T10, \r\n"
  576. " SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10, \r\n"
  577. " SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T11, \r\n"
  578. " SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11, \r\n"
  579. " SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T12, \r\n"
  580. " SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12, \r\n"
  581. " SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T13, \r\n"
  582. " SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13, \r\n"
  583. " SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T14, \r\n"
  584. " SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14, \r\n"
  585. " SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T15, \r\n"
  586. " SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15, \r\n"
  587. " SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T16, \r\n"
  588. " SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16, \r\n"
  589. " SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T17, \r\n"
  590. " SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17, \r\n"
  591. " SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T18, \r\n"
  592. " SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18, \r\n"
  593. " SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T19, \r\n"
  594. " SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19, \r\n"
  595. " SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T20, \r\n"
  596. " SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20, \r\n"
  597. " SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T21, \r\n"
  598. " SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21, \r\n"
  599. " SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T22, \r\n"
  600. " SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22, \r\n"
  601. " SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T23, \r\n"
  602. " SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23 \r\n"
  603. " FROM ( \r\n"
  604. "SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE, \r\n"
  605. " Y.LINKID, Y.LINKLEN, X.STATHOUR, X.SPD, X.TRVTM \r\n"
  606. " FROM (SELECT /*+ INDEX(A IX_ROAD_HH_STAT_PK) */ \r\n"
  607. " 'FSN' AS TRFTYPE, \r\n"
  608. " B.ATRD_ID AS ARTERYID, A.ROAD_ID AS LINKID, C.SECT_LNGT AS LINKLEN, \r\n"
  609. " SUBSTR(A.STAT_DT, 9, 2) AS STATHOUR, A.SPED AS SPD, A.TRVL_HH AS TRVTM \r\n"
  610. " FROM TB_ROAD_HH_STAT A, TB_ATRD_ROAD_RLTN B, TB_ROAD C \r\n"
  611. " WHERE 1=1 \r\n"
  612. " AND A.STAT_DT BETWEEN :p01 AND :p02 \r\n"
  613. " AND A.ROAD_ID = B.ROAD_ID \r\n"
  614. " AND B.ROAD_ID = C.ROAD_ID \r\n"
  615. " ) X, \r\n"
  616. " VW_ATRD_ROAD_RLTN Y \r\n"
  617. " WHERE Y.LINKID = X.LINKID \r\n"
  618. ") \r\n"
  619. " GROUP BY ROLLUP (ARTERYID, DIRECTION, TRFTYPE) \r\n"
  620. " HAVING GROUPING(TRFTYPE) = 0) LX, \r\n"
  621. " VW_ATRD LY \r\n"
  622. " WHERE LY.ARTERYID = LX.ARTERYID(+) \r\n"
  623. " AND LY.DIRECTION = LX.DIRECTION(+) \r\n"
  624. " ORDER BY LY.ARTERYNAME, LY.DIRECTION, LX.TRFTYPE \r\n";
  625. try
  626. {
  627. ITSDb_SQLText(ADOD, sQry);
  628. ITSDb_SQLBind(ADOD, "p01", sFrom);
  629. ITSDb_SQLBind(ADOD, "p02", sTo);
  630. ITSDb_SQLOpen(ADOD);
  631. ShowRecordCount(ADOD->RecordCount);
  632. }
  633. catch(EDatabaseError &E)
  634. {
  635. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  636. DBERRORMSG(Caption, String(E.ClassName()), E.Message, sQry);
  637. throw Exception(String(E.ClassName()) + E.Message);
  638. }
  639. catch(Exception &exception)
  640. {
  641. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  642. DBERRORMSG(Caption, String(exception.ClassName()), exception.Message, sQry);
  643. throw Exception(String(exception.ClassName()) + exception.Message);
  644. }
  645. }
  646. //---------------------------------------------------------------------------
  647. /*
  648. * 일별 월 통계 데이터 조회/출력
  649. * arguments
  650. *
  651. * return
  652. * void
  653. */
  654. void __fastcall TIST0080M::DispMonthStatistics()
  655. {
  656. String sQry = "";
  657. String sMM;
  658. String sFrom, sTo;
  659. String sTmp;
  660. int ii;
  661. sMM = DtMon->Date.FormatString("yyyyMM");
  662. sFrom = sMM + "01000000";
  663. sTo = sMM + "31235959";
  664. String sStTm = DtStTime->Time.FormatString("hhnn");
  665. String sEdTm = DtEdTime->Time.FormatString("hhnn");
  666. sQry =
  667. "SELECT LY.ARTERYID, LY.ARTERYNAME, LY.DIRECTION, LY.DIRECTIONNAME, LY.ARTERYLENGHT, LX.TRFTYPE, \r\n"
  668. " DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD, \r\n"
  669. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01, \r\n"
  670. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02, \r\n"
  671. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03, \r\n"
  672. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04, \r\n"
  673. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05, \r\n"
  674. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06, \r\n"
  675. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07, \r\n"
  676. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08, \r\n"
  677. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09, \r\n"
  678. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10, \r\n"
  679. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11, \r\n"
  680. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12, \r\n"
  681. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13, \r\n"
  682. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14, \r\n"
  683. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15, \r\n"
  684. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16, \r\n"
  685. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17, \r\n"
  686. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18, \r\n"
  687. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19, \r\n"
  688. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20, \r\n"
  689. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21, \r\n"
  690. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22, \r\n"
  691. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23, \r\n"
  692. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T24, 0, '-', ROUND(L24*3.6/T24, 0))) AS C24, \r\n"
  693. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T25, 0, '-', ROUND(L25*3.6/T25, 0))) AS C25, \r\n"
  694. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T26, 0, '-', ROUND(L26*3.6/T26, 0))) AS C26, \r\n"
  695. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T27, 0, '-', ROUND(L27*3.6/T27, 0))) AS C27, \r\n"
  696. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T28, 0, '-', ROUND(L28*3.6/T28, 0))) AS C28, \r\n"
  697. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T29, 0, '-', ROUND(L29*3.6/T29, 0))) AS C29, \r\n"
  698. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T30, 0, '-', ROUND(L30*3.6/T30, 0))) AS C30, \r\n"
  699. " DECODE(LX.ARTERYID, NULL, '-', DECODE(T31, 0, '-', ROUND(L31*3.6/T31, 0))) AS C31 \r\n"
  700. " FROM ( \r\n"
  701. "SELECT GROUPING(ARTERYID) AS GROUPING_ARTERYID, \r\n"
  702. " GROUPING(DIRECTION) AS GROUPING_DIRECTION, \r\n"
  703. " GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, \r\n"
  704. " CASE WHEN GROUPING(ARTERYID ) = 1 THEN '도로' ELSE ARTERYID END AS GROUP_ARTERYID, \r\n"
  705. " CASE WHEN GROUPING(DIRECTION) = 1 THEN '방향' ELSE DIRECTION END AS GROUP_DIRECTION, \r\n"
  706. " CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, \r\n"
  707. " ARTERYID, DIRECTION, TRFTYPE, \r\n"
  708. " SUM(DECODE(TRVTM, NULL, 0, LINKLEN)) TLL, \r\n"
  709. " SUM(DECODE(TRVTM, NULL, 0, TRVTM )) TTM, \r\n"
  710. " SUM(DECODE(STATDAY, '01', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T01, \r\n"
  711. " SUM(DECODE(STATDAY, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01, \r\n"
  712. " SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T02, \r\n"
  713. " SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02, \r\n"
  714. " SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T03, \r\n"
  715. " SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03, \r\n"
  716. " SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T04, \r\n"
  717. " SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04, \r\n"
  718. " SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T05, \r\n"
  719. " SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05, \r\n"
  720. " SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T06, \r\n"
  721. " SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06, \r\n"
  722. " SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T07, \r\n"
  723. " SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07, \r\n"
  724. " SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T08, \r\n"
  725. " SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08, \r\n"
  726. " SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T09, \r\n"
  727. " SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09, \r\n"
  728. " SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T10, \r\n"
  729. " SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10, \r\n"
  730. " SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T11, \r\n"
  731. " SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11, \r\n"
  732. " SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T12, \r\n"
  733. " SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12, \r\n"
  734. " SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T13, \r\n"
  735. " SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13, \r\n"
  736. " SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T14, \r\n"
  737. " SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14, \r\n"
  738. " SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T15, \r\n"
  739. " SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15, \r\n"
  740. " SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T16, \r\n"
  741. " SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16, \r\n"
  742. " SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T17, \r\n"
  743. " SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17, \r\n"
  744. " SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T18, \r\n"
  745. " SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18, \r\n"
  746. " SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T19, \r\n"
  747. " SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19, \r\n"
  748. " SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T20, \r\n"
  749. " SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20, \r\n"
  750. " SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T21, \r\n"
  751. " SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21, \r\n"
  752. " SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T22, \r\n"
  753. " SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22, \r\n"
  754. " SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T23, \r\n"
  755. " SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23, \r\n"
  756. " SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T24, \r\n"
  757. " SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L24, \r\n"
  758. " SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T25, \r\n"
  759. " SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L25, \r\n"
  760. " SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T26, \r\n"
  761. " SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L26, \r\n"
  762. " SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T27, \r\n"
  763. " SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L27, \r\n"
  764. " SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T28, \r\n"
  765. " SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L28, \r\n"
  766. " SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T29, \r\n"
  767. " SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L29, \r\n"
  768. " SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T30, \r\n"
  769. " SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L30, \r\n"
  770. " SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T31, \r\n"
  771. " SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L31 \r\n"
  772. " FROM ( \r\n"
  773. "SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE, \r\n"
  774. " Y.LINKID, Y.LINKLEN, X.STATDAY, X.SPD, X.TRVTM \r\n"
  775. " FROM (SELECT /*+ INDEX(A IX_ROAD_15M_STAT_PK) */ \r\n"
  776. " 'FSN' AS TRFTYPE, \r\n"
  777. " B.ATRD_ID AS ARTERYID, A.ROAD_ID AS LINKID, C.SECT_LNGT AS LINKLEN, \r\n"
  778. " SUBSTR(A.STAT_DT, 7, 2) AS STATDAY, A.SPED AS SPD, A.TRVL_HH AS TRVTM \r\n"
  779. " FROM TB_ROAD_15M_STAT A, TB_ATRD_ROAD_RLTN B, TB_ROAD C \r\n"
  780. " WHERE 1=1 \r\n"
  781. " AND A.STAT_DT BETWEEN :p01 AND :p02 \r\n"
  782. " AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN :p03 AND :p04 \r\n"
  783. " AND A.ROAD_ID = B.ROAD_ID \r\n"
  784. " AND B.ROAD_ID = C.ROAD_ID \r\n"
  785. " ) X, \r\n"
  786. " VW_ATRD_ROAD_RLTN Y \r\n"
  787. " WHERE Y.LINKID = X.LINKID \r\n"
  788. ") \r\n"
  789. " GROUP BY ROLLUP (ARTERYID, DIRECTION, TRFTYPE) \r\n"
  790. " HAVING GROUPING(TRFTYPE) = 0) LX, \r\n"
  791. " VW_ATRD LY \r\n"
  792. " WHERE LY.ARTERYID = LX.ARTERYID(+) \r\n"
  793. " AND LY.DIRECTION = LX.DIRECTION(+) \r\n"
  794. " ORDER BY LY.ARTERYNAME, LY.DIRECTION, LX.TRFTYPE \r\n";
  795. try
  796. {
  797. ITSDb_SQLText(ADOM, sQry);
  798. ITSDb_SQLBind(ADOM, "p01", sFrom);
  799. ITSDb_SQLBind(ADOM, "p02", sTo);
  800. ITSDb_SQLBind(ADOM, "p03", sStTm);
  801. ITSDb_SQLBind(ADOM, "p04", sEdTm);
  802. ITSDb_SQLOpen(ADOM);
  803. ShowRecordCount(ADOM->RecordCount);
  804. }
  805. catch(EDatabaseError &E)
  806. {
  807. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  808. DBERRORMSG(Caption, String(E.ClassName()), E.Message, sQry);
  809. throw Exception(String(E.ClassName()) + E.Message);
  810. }
  811. catch (Exception &e)
  812. {
  813. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  814. DBERRORMSG(Caption, String(e.ClassName()), e.Message, sQry);
  815. throw Exception(String(e.ClassName()) + e.Message);
  816. }
  817. }
  818. //---------------------------------------------------------------------------
  819. /*
  820. * 월별 년 통계 데이터 조회/출력
  821. * arguments
  822. *
  823. * return
  824. * void
  825. */
  826. void __fastcall TIST0080M::DispYearStatistics()
  827. {
  828. #if 0
  829. String sQry = "";
  830. String sYYYY;
  831. String sTmp;
  832. int ii;
  833. sYYYY = DtYear->Date.FormatString("yyyy");
  834. String sQry1 = "";
  835. sQry1 = "SELECT LY.ARTERYID, LY.ARTERYNAME, LY.DIRECTION, LY.DIRECTIONNAME, LY.ARTERYLENGTH, LX.TRFTYPE, \r\n"
  836. " DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD, \r\n";
  837. int nLoops = 13;
  838. for (ii = 1; ii < nLoops; ii++)
  839. {
  840. sTmp = "";
  841. sTmp.printf(L" DECODE(LX.ARTERYID, NULL, '-', DECODE(T%02d, 0, '-', ROUND(L%02d*3.6/T%02d, 0))) AS C%02d ", ii, ii, ii, ii);
  842. if (ii == nLoops-1) sTmp += " \r\n";
  843. else sTmp += ",\r\n";
  844. sQry1 += sTmp;
  845. }
  846. String sQry2 = "";
  847. sQry2 = " FROM (SELECT GROUPING(ARTERYID) AS GROUPING_ARTERYID, GROUPING(DIRECTION) AS GROUPING_DIRECTION, \r\n"
  848. " GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, \r\n"
  849. " CASE WHEN GROUPING(ARTERYID ) = 1 THEN '도로' ELSE ARTERYID END AS GROUP_ARTERYID, \r\n"
  850. " CASE WHEN GROUPING(DIRECTION) = 1 THEN '방향' ELSE DIRECTION END AS GROUP_DIRECTION, \r\n"
  851. " CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, \r\n"
  852. " ARTERYID, DIRECTION, TRFTYPE, \r\n"
  853. " SUM(DECODE(TRVTM, NULL, 0, LINKLEN)) TLL, \r\n"
  854. " SUM(DECODE(TRVTM, NULL, 0, TRVTM )) TTM, \r\n";
  855. for (ii = 0; ii < nLoops; ii++)
  856. {
  857. sTmp = "";
  858. sTmp.printf(L" SUM(DECODE(STATMON, '%02d', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T%02d, \r\n"
  859. " SUM(DECODE(STATMON, '%02d', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L%02d", ii, ii, ii, ii);
  860. if (ii == nLoops-1) sTmp += " \r\n";
  861. else sTmp += ",\r\n";
  862. sQry2 += sTmp;
  863. }
  864. String sQry3 = 0;
  865. sQry3 = " FROM ( \r\n";
  866. int nBinds = 0;
  867. int nColCnt = CbStatCfg->Items->Count;
  868. for (ii = 0; ii < nColCnt; ii++)
  869. {
  870. TObjStat *pObj = (TObjStat *)GetStatObject(CbStatCfg, ii);
  871. if (NULL == pObj) continue;
  872. sTmp = "";
  873. sTmp.printf(L" SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE, \r\n"
  874. " Y.LINKID, Y.LINKLEN, X.STATMON, X.SPD, X.TRVTM \r\n"
  875. " FROM (SELECT /*+ INDEX(A %s) */ \r\n"
  876. " '%s' AS TRFTYPE, \r\n"
  877. " A.LINKID, A.STATMON, A.AVGSPD AS SPD, A.AVGTRVTM AS TRVTM \r\n"
  878. " FROM %s A \r\n"
  879. " WHERE A.STATYEAR = :f%d) X, \r\n"
  880. " (SELECT A.ARTERYID, B.ARTERYNAME, A.DIRECTION, B.ARTERYTYPE, \r\n"
  881. " DECODE(A.DIRECTION, '0', B.UPLENGTH, B.DNLENGTH) AS ARTERYLEN, \r\n"
  882. " C.LENGTH AS LINKLEN, C.LINKID, A.LINKSEQ \r\n"
  883. " FROM TB_MP_ARTERY_LINK A, TB_MS_ARTERY B, TB_MS_LINK C \r\n"
  884. " WHERE A.LINKLEVEL = :l%d \r\n"
  885. " AND A.ARTERYID = B.ARTERYID \r\n"
  886. " AND B.IMPYN = 'Y' \r\n"
  887. " AND A.LINKID = C.LINKID) Y \r\n"
  888. " WHERE Y.LINKID = X.LINKID \r\n",
  889. pObj->m_sIdx[enStatMon].c_str(),
  890. pObj->m_sStatDesc.c_str(),
  891. pObj->m_sTbl[enStatMon].c_str(),
  892. nBinds,
  893. nBinds);
  894. sQry3 += sTmp;
  895. if (ii != (nColCnt-1))
  896. {
  897. TObjStat *pObj = (TObjStat *)GetStatObject(CbStatCfg, ii+1);
  898. if (NULL != pObj)
  899. {
  900. sQry3 += " UNION ALL \r\n";
  901. }
  902. }
  903. nBinds++;
  904. }
  905. String sQry4 = "";
  906. sQry4 = " ) \r\n"
  907. " GROUP BY ROLLUP (ARTERYID, DIRECTION, TRFTYPE) \r\n"
  908. " HAVING GROUPING(TRFTYPE) = 0) LX, \r\n"
  909. " (SELECT ARTERYID, DIRECTION, DIRECTIONNAME, \r\n"
  910. " ARTERYNAME, ARTERYLENGTH, ARTERYTYPE \r\n"
  911. " FROM VW_MS_ARTERY ) LY \r\n"
  912. " WHERE LY.ARTERYID = LX.ARTERYID(+) \r\n"
  913. " AND LY.DIRECTION = LX.DIRECTION(+) \r\n"
  914. " ORDER BY LY.ARTERYNAME, LY.DIRECTION, LX.TRFTYPE \r\n";
  915. sQry = sQry1 + sQry2 + sQry3 + sQry4;
  916. try
  917. {
  918. ITSDb_SQLText(ADOY, sQry);
  919. for (ii = 0; ii < nBinds; ii++)
  920. {
  921. sTmp = "";
  922. sTmp.printf(L"f%d", ii);
  923. ITSDb_SQLBind(ADOY, sTmp, sYYYY);
  924. sTmp = "";
  925. sTmp.printf(L"l%d", ii);
  926. ITSDb_SQLBind(ADOY, sTmp, m_sLinkLevel);
  927. }
  928. ITSDb_SQLOpen(ADOY);
  929. ShowRecordCount(ADOY->RecordCount);
  930. }
  931. catch(EDatabaseError &E)
  932. {
  933. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  934. DBERRORMSG(Caption, String(E.ClassName()), E.Message, sQry);
  935. throw Exception(String(E.ClassName()) + E.Message);
  936. }
  937. catch (Exception &e)
  938. {
  939. ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2);
  940. DBERRORMSG(Caption, String(e.ClassName()), e.Message, sQry);
  941. throw Exception(String(e.ClassName()) + e.Message);
  942. }
  943. #endif
  944. }
  945. //---------------------------------------------------------------------------
  946. /*
  947. * 조회결과 데이터 건수 표출
  948. * arguments
  949. *
  950. * return
  951. * void
  952. */
  953. void __fastcall TIST0080M::ShowRecordCount(int nRecCnt)
  954. {
  955. //
  956. }
  957. //---------------------------------------------------------------------------
  958. /*
  959. * Summary Footer style set
  960. * arguments
  961. *
  962. * return
  963. * void
  964. */
  965. void __fastcall TIST0080M::TvListDataControllerSummaryFooterSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments,
  966. TcxSummaryEventOutArguments &OutArguments)
  967. {
  968. CMM_SetSummaryItemKind(Arguments, OutArguments, 2, false, false);
  969. }
  970. //---------------------------------------------------------------------------
  971. /*
  972. * Summary Footer style set
  973. * arguments
  974. *
  975. * return
  976. * void
  977. */
  978. void __fastcall TIST0080M::TvListDataControllerSummaryDefaultGroupSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments,
  979. TcxSummaryEventOutArguments &OutArguments)
  980. {
  981. CMM_SetSummaryItemKind(Arguments, OutArguments, 1, false, false);
  982. }
  983. //---------------------------------------------------------------------------
  984. /*
  985. * Close 버튼 클릭 이벤트 핸들러
  986. * arguments
  987. * Sender : event handler 객체
  988. * return
  989. * void
  990. */
  991. void __fastcall TIST0080M::BtnCloseClick(TObject *Sender)
  992. {
  993. Close();
  994. }
  995. //---------------------------------------------------------------------------
  996. void __fastcall TIST0080M::vDayColumnCustomDrawCell(TcxCustomGridTableView *Sender, TcxCanvas *ACanvas, TcxGridTableDataCellViewInfo *AViewInfo,
  997. bool &ADone)
  998. {
  999. TColor tColor = ACanvas->Brush->Color;
  1000. try
  1001. {
  1002. if (!VarIsNull(AViewInfo->Value))
  1003. {
  1004. String sSpd = AViewInfo->Value;
  1005. if (sSpd == NULL || sSpd.IsEmpty() || sSpd == "0" || sSpd == "-")
  1006. {
  1007. tColor = clFuchsia;
  1008. }
  1009. }
  1010. else
  1011. {
  1012. tColor = clFuchsia;
  1013. }
  1014. ACanvas->Font->Color = clBlack;
  1015. ACanvas->SetBrushColor(tColor);
  1016. }
  1017. catch(...)
  1018. {
  1019. }
  1020. }
  1021. //---------------------------------------------------------------------------
  1022. void __fastcall TIST0080M::FormClose(TObject *Sender, TCloseAction &Action)
  1023. {
  1024. CommClose();
  1025. IST0080M = NULL;
  1026. Action = caFree;
  1027. }
  1028. //---------------------------------------------------------------------------