//--------------------------------------------------------------------------- #include #include "ITSSkinF.h" #include "ITSUtilF.h" #include "ITSDbF.h" #include "AppGlobalF.h" #include "ITSLangTransF.h" #pragma hdrstop #include "VDSS020MF.h" //--------------------------------------------------------------------------- #pragma package(smart_init) #pragma link "cxButtons" #pragma link "cxClasses" #pragma link "cxContainer" #pragma link "cxControls" #pragma link "cxCustomData" #pragma link "cxData" #pragma link "cxDataStorage" #pragma link "cxDBData" #pragma link "cxEdit" #pragma link "cxFilter" #pragma link "cxGraphics" #pragma link "cxGrid" #pragma link "cxGridBandedTableView" #pragma link "cxGridCustomTableView" #pragma link "cxGridCustomView" #pragma link "cxGridDBBandedTableView" #pragma link "cxGridLevel" #pragma link "cxGridTableView" #pragma link "cxLabel" #pragma link "cxLookAndFeelPainters" #pragma link "cxLookAndFeels" #pragma link "cxPC" #pragma link "cxPCdxBarPopupMenu" #pragma link "cxSpinEdit" #pragma link "cxSplitter" #pragma link "cxStyles" #pragma link "cxTextEdit" #pragma link "dxSkinBlack" #pragma link "dxSkinBlue" #pragma link "dxSkinsCore" #pragma link "dxSkinscxPCPainter" #pragma link "cxCheckBox" #pragma resource "*.dfm" TVDSS020M *VDSS020M = NULL; //--------------------------------------------------------------------------- __fastcall TVDSS020M::TVDSS020M(TComponent* Owner) : TForm(Owner) { LangTrans->Translate(this, ITSDb_GetConnection()); ITSSkin_Load(this); LoadLocalSkin(); CMM_LoadForm(g_sFormsDir, this); FTitle = Caption;//"VDS-검지기 교통량 통계"; LoadConfig(); m_sLinkLevel = "1"; MyItsAtrdManager = new TItsAtrdManager(); MyItsAtrdManager->LoadFromDb(); MyItsAtrdManager->LoadAtrdLength(); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::LoadConfig() { m_sFooterRight = "";//GetConfigValue("APPLICATION", "REPORTRIGHT", ""); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::LoadLocalSkin() { /* * 스킨 변경 */ PgQuery->ParentColor = true; TsDay->ParentColor = true; TsMon->ParentColor = true; TsYear->ParentColor = true; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::FormCreate(TObject *Sender) { for (int ii = 0; ii < MAX_CHART; ii++) { m_pFrmChart[ii] = new TPlugInChart(this); switch(ii) { case 0: m_pFrmChart[ii]->Parent = PnlDayGraph; m_pFrmChart[ii]->SetChartInfo("", "시간", "교통량"); break; case 1: m_pFrmChart[ii]->Parent = PnlMonGraph; m_pFrmChart[ii]->SetChartInfo("", "일", "교통량"); break; case 2: m_pFrmChart[ii]->Parent = PnlYearGraph; m_pFrmChart[ii]->SetChartInfo("", "월", "교통량"); break; } m_pFrmChart[ii]->Show(); } PgList->HideTabs = true; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::FormShow(TObject *Sender) { PgList->ActivePageIndex = 0; PgQuery->ActivePageIndex= 0; //DMMain->InsertListHeaderMenuItem(CxDay, (TcxGridDBTableView*)TvDay); // form 초기화 FormInit(); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::FormInit() { DtDay->Date = IncHour(Now(), -24); DtMon->Date = StartOfTheMonth(IncHour(Now(), -24*30)); DtYear->Date = IncHour(Now(), -24); DtStTime->Time = StrToDateTime("00:00"); DtEdTime->Time = StrToDateTime("23:59"); ADOD->Connection = ITSDb_GetConnection(); ADOM->Connection = ITSDb_GetConnection(); ADOY->Connection = ITSDb_GetConnection(); TvDay->DataController->DataSource = DSD; TvMon->DataController->DataSource = DSM; TvYear->DataController->DataSource = DSY; //FillStatComboBox(CbStatCfg, "IST0810M"); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::CommClose() { try { CMM_SaveForm(g_sFormsDir, this); //DeleteStatComboBox(CbStatCfg); TvDay->BeginUpdate(); ADOD->Close(); TvDay->EndUpdate(); TvMon->BeginUpdate(); ADOM->Close(); TvMon->EndUpdate(); TvYear->BeginUpdate(); ADOY->Close(); TvYear->EndUpdate(); if (MyItsAtrdManager) { delete MyItsAtrdManager; MyItsAtrdManager = NULL; } } catch(...) { } } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TmrRefreshTimer(TObject *Sender) { TmrRefresh->Enabled = false; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::PgQueryChange(TObject *Sender) { //Tab Change PgList->ActivePageIndex = PgQuery->ActivePageIndex; switch(PgQuery->ActivePageIndex) { case 0: DtDay->SetFocus(); break; case 1: DtMon->SetFocus(); break; case 2: DtYear->SetFocus(); break; default: break; } } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::BtnGraphClick(TObject *Sender) { // 차트를 다른 창에서 보여줘야 할 경우 Frm1121100_1F.cpp 내 소스 참조 } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::BtnPrintClick(TObject *Sender) { TcxGrid *pGrid = NULL; TcxGridDBBandedTableView *pView = NULL; TLabel *LblQry= NULL; String sTitle; switch(PgQuery->ActivePageIndex) { case 0: pGrid = CxDay; pView = TvDay; LblQry = LblQryTm; break; case 1: pGrid = CxMon; pView = TvMon; LblQry = LblQryDay; break; case 2: pGrid = CxYear; pView = TvYear; LblQry = LblQryYear; break; default: return; } sTitle = LblRptTitle->Caption + "\r\n" + LblQry->Caption; if (pView->DataController->RecordCount == 0) { ShowMessage(FrmLang->lblPrintNoData->Caption);//"인쇄 할 데이터가 없습니다."); return; } //ComponentPrinterLink->OptionsView->Footers = true; //ComponentPrinterLink->OptionsView->GroupFooters = true; //ComponentPrinterLink->OptionsSize->AutoWidth = true; //CMM_PrintList(pGrid, sTitle, m_sFooterRight); pView->DataController->FocusedRecordIndex = 0; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::BtnExlSaveClick(TObject *Sender) { TcxGrid *pGrid = NULL; TcxGridDBBandedTableView *pView = NULL; TLabel *LblQry= NULL; String sTitle; switch(PgQuery->ActivePageIndex) { case 0: pGrid = CxDay; pView = TvDay; LblQry = LblQryTm; break; case 1: pGrid = CxMon; pView = TvMon; LblQry = LblQryDay; break; case 2: pGrid = CxYear; pView = TvYear; LblQry = LblQryYear; break; default: return; } sTitle = LblRptTitle->Caption + LblQry->Caption; if (pView->DataController->RecordCount == 0) { ShowMessage(FrmLang->lblSaveNoData->Caption);//"저장 할 데이터가 없습니다."); return; } CMM_ExportToExcelFile(sTitle, pGrid, pView, this); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::BtnSearchClick(TObject *Sender) { Application->ProcessMessages(); TSqlCursor sqlCrs((TControl*)BtnSearch); m_sLinkLevel = CbLinkLevel->Text; Application->ProcessMessages(); RefreshData(); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::RefreshData() { TcxGridDBBandedTableView *pView = NULL; TLabel *LblQry= NULL; TLabel *LblRecords= NULL; WORD nYear, nMonth, nDay, nHour, nMin, nSec, nMSec; int nDispCols; String sBandText = ""; String sMsgTitle, sMsgString; TDateTimePicker *DtQryTm; int nStYear, nEdYear; switch(PgQuery->ActivePageIndex) { case 0: pView = TvDay; DtQryTm = DtDay; LblQry = LblQryTm; LblRecords = LblRecordsTm; sBandText = "(" + DtDay->Date.FormatString("yyyy년 mm월 dd일") + ")"; nDispCols = 24; if (StartOfTheDay(Now()) <= DtDay->DateTime) { DecodeTime(Now(), nHour, nMin, nSec, nMSec); if (nMin > 5) nDispCols = nHour; else nDispCols = nHour-1; } break; case 1: pView = TvMon; DtQryTm = DtMon; LblQry = LblQryDay; LblRecords = LblRecordsDay; sBandText = "(" + DtMon->Date.FormatString("yyyy년 mm월") + ")"; nDispCols = ITSUtil_GetMonthLastDay(AnsiString(DtMon->Date.FormatString("yyyymm")).c_str()); if (StartOfTheDay(Now()) <= EndOfTheMonth(DtMon->DateTime)) { DecodeDate(Now(), nYear, nMonth, nDay); if (nDay < nDispCols) nDispCols = nDay-1; } { String sStDateTime = DtStTime->Time.FormatString("hhnn"); String sEdDateTime = DtEdTime->Time.FormatString("hhnn"); if (sStDateTime > sEdDateTime) { Application->MessageBox(FrmLang->lblQryDtErr->Caption.c_str(),//L"검색 시작시각이 검색 종료시각 보다 큽니다.", FTitle.c_str(), MB_OK|MB_ICONWARNING|MB_APPLMODAL); return; } } break; case 2: pView = TvYear; DtQryTm = DtYear; LblQry = LblQryYear; LblRecords = LblRecordsYear; sBandText = "(" + DtYear->Date.FormatString("yyyy년") + ")"; nDispCols = 12; if (Now().FormatString("yyyy") == DtYear->DateTime.FormatString("yyyy")) { DecodeDate(Now(), nYear, nMonth, nDay); nDispCols = nMonth; } break; default: return; } if (!pView) return; if (DtQryTm->DateTime.FormatString("yyyymmdd").ToInt() > Now().FormatString("yyyymmdd").ToInt()) { Application->MessageBox(FrmLang->lblCnfmStatTm->Caption.c_str(),//L"통계검색 시각 정보를 확인하세요.", FTitle.c_str(), MB_OK|MB_ICONWARNING|MB_APPLMODAL); MessageDlg(sMsgString.c_str(), mtInformation, TMsgDlgButtons() << mbOK, NULL); DtQryTm->SetFocus(); nDispCols = 0; /* 리턴하지 않는 경우 모든 컬럼을 숨긴다. */ return; } LblQry->Caption = sBandText; CMM_ShowListColumn(pView, nDispCols); TWaitCursor crs; try { pView->BeginUpdate(); switch(PgQuery->ActivePageIndex) { case 0: DispDayStatistics(); break; case 1: DispMonthStatistics(); break; default: return; } } __finally { pView->EndUpdate(); //pView->ApplyBestFit(NULL, false, false); LblRecords->Caption = FormatFloat("##,##0", pView->DataController->RecordCount) + FrmLang->lblEA->Caption;//" 건"; String sEnd = FrmLang->lblQrySel->Caption + " [" + LblRecords->Caption + "]";//"데이터 " + LblRecords->Caption + " 이 조회 되었습니다."; Application->MessageBox(sEnd.c_str(), FrmLang->lblQryEnd->Caption.c_str(),//L"데이터 조회 완료", MB_OK|MB_ICONINFORMATION|MB_APPLMODAL); } #if 1 switch(PgQuery->ActivePageIndex) { case 0: CMM_ExpandCollapseChk(TvDay, true); break; case 1: CMM_ExpandCollapseChk(TvMon, true); break; } #endif DrawChart(sBandText); #if 1 switch(PgQuery->ActivePageIndex) { case 0: CMM_ExpandCollapseChk(TvDay, false); break; case 1: CMM_ExpandCollapseChk(TvMon, false); break; } #endif #if 0 ShowMessage(String(TvDay->DataController->Groups->DataController->DataRowCount)); int nRows = TvDay->ViewData->RecordCount; for (int ii = 0; ii < nRows; ii++) { TcxCustomGridRow* ARow = TvDay->ViewData->Rows[ii]; #if 0 String sTitle1, sTitle2; if (TvDay->Columns[nAxisY1]->Visible && !VarIsNull(ARow->Values[nAxisY1])) { sTitle1 = ARow->Values[nAxisY1]; } if (TvDay->Columns[nAxisY2]->Visible && !VarIsNull(ARow->Values[nAxisY2])) { sTitle2 = ARow->Values[nAxisY2]; } if (sTitle1 == sTitle2) { //그룹핑했을 경우 그룹핑한 컬럼 제외 //continue; } #endif if (!ARow->IsData) { TcxDataSummaryItems *AItems; PVariant AValues; TcxGridGroupRow* AGroup = (TcxGridGroupRow*)TvDay->ViewData->Rows[ii]; AGroup->GetGroupSummaryInfo(AItems, AValues); int x = AItems->Count; int y = 0; for (int jj = 0; jj < x; jj++) { y = AValues[jj].VInteger; //Memo1->Lines->Add(String(jj) + ": " + String(y)); } } } TcxDataSummaryItem *pItem; TcxDataSummary *pSummary = TvDay->DataController->Summary; //ShowMessage(String(pSummary->DefaultGroupSummaryItems->Count)); for (int ii = 0; ii < pSummary->DefaultGroupSummaryItems->Count; ii++) { //pSummary->DefaultGroupSummaryItems->Items[ii]-> } #endif } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::DrawChart(String sTitle) { TcxGridDBBandedTableView *pView = NULL; String sAxisX, sAxisY; int nAxisY1, nAxisY2, nAxisY3, nStColIdx, nEdColIdx, nTotColIdx; nAxisY3 = 0; nEdColIdx = 0; nTotColIdx = -1; switch(PgQuery->ActivePageIndex) { case 0: pView = TvDay; nAxisY1 = vDayColumnNo->Index; nAxisY2 = vDayColumnLane->Index; nAxisY3 = Col00->Index; nEdColIdx = Col23->Index; nStColIdx = vDayColumnAvg->Index;//vDayColumn1->Index; break; case 1: pView = TvMon; nAxisY1 = vMonColumnNo->Index; nAxisY2 = vMonColumnLane->Index; nAxisY3 = TvMonColumn1->Index; nEdColIdx = vMonColumn31->Index; nStColIdx = vMonColumnAvg->Index;//vMonColumn1->Index; break; case 2: pView = TvYear; nAxisY1 = vYearColumnNo->Index; nAxisY2 = vYearColumnTot->Index; nAxisY3 = TvYearColumn1->Index; nEdColIdx = vYearColumn12->Index; nStColIdx = vYearColumnAvg->Index;//vYearColumn1->Index; break; default: return; } /* * Chart draw */ try { m_pFrmChart[PgQuery->ActivePageIndex]->Clear(); if (pView->DataController->RecordCount == 0) { //ShowMessage("표출 할 데이터가 없습니다."); return; } m_pFrmChart[PgQuery->ActivePageIndex]->BeginDraw("", "", ""); //m_pFrmChart[PgQuery->ActivePageIndex]->DrawChart4((TcxGridDBTableView *)pView, nAxisY1, nAxisY2, nStColIdx, nTotColIdx); m_pFrmChart[PgQuery->ActivePageIndex]->DrawChart6((TcxGridDBTableView *)pView, nAxisY1, nAxisY2, nAxisY3, nStColIdx, nEdColIdx, nTotColIdx); m_pFrmChart[PgQuery->ActivePageIndex]->EndDraw(); } catch(...) { } } //--------------------------------------------------------------------------- /* * 시간대별 일 통계 데이터 조회/출력 * arguments * * return * void */ void __fastcall TVDSS020M::DispDayStatistics() { String sQry; String sDD; String sFrom, sTo; String sTmp; int ii; sDD = DtDay->Date.FormatString("yyyyMMdd"); sFrom = sDD + "000000"; sTo = sDD + "235959"; sQry = "SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM, \r\n" " LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE, LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVL, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L00, NULL, 0, L00)) AS C00, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L01, NULL, 0, L01)) AS C01, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L02, NULL, 0, L02)) AS C02, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L03, NULL, 0, L03)) AS C03, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L04, NULL, 0, L04)) AS C04, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L05, NULL, 0, L05)) AS C05, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L06, NULL, 0, L06)) AS C06, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L07, NULL, 0, L07)) AS C07, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L08, NULL, 0, L08)) AS C08, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L09, NULL, 0, L09)) AS C09, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L10, NULL, 0, L10)) AS C10, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L11, NULL, 0, L11)) AS C11, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L12, NULL, 0, L12)) AS C12, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L13, NULL, 0, L13)) AS C13, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L14, NULL, 0, L14)) AS C14, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L15, NULL, 0, L15)) AS C15, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L16, NULL, 0, L16)) AS C16, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L17, NULL, 0, L17)) AS C17, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L18, NULL, 0, L18)) AS C18, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L19, NULL, 0, L19)) AS C19, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L20, NULL, 0, L20)) AS C20, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L21, NULL, 0, L21)) AS C21, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L22, NULL, 0, L22)) AS C22, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L23, NULL, 0, L23)) AS C23 \r\n" " FROM ( \r\n" " SELECT GROUPING(DTCT_NMBR) AS GROUPING_DTCT_NMBR, \r\n" " GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE, \r\n" " GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, \r\n" " CASE WHEN GROUPING(DTCT_NMBR ) = 1 THEN '검지기' ELSE DTCT_NMBR END AS GROUP_DTCT_NMBR, \r\n" " CASE WHEN GROUPING(ISTL_LANE) = 1 THEN '차로' ELSE ISTL_LANE END AS GROUP_ISTL_LANE, \r\n" " CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, \r\n" " DTCT_NMBR, ISTL_LANE, TRFTYPE, \r\n" " SUM(TFVL) TOTTFVL, \r\n" " ROUND(AVG(TFVL), 0) AVGTFVL, \r\n" " SUM(DECODE(STATHOUR, '00', TFVL)) AS L00, \r\n" " SUM(DECODE(STATHOUR, '01', TFVL)) AS L01, \r\n" " SUM(DECODE(STATHOUR, '02', TFVL)) AS L02, \r\n" " SUM(DECODE(STATHOUR, '03', TFVL)) AS L03, \r\n" " SUM(DECODE(STATHOUR, '04', TFVL)) AS L04, \r\n" " SUM(DECODE(STATHOUR, '05', TFVL)) AS L05, \r\n" " SUM(DECODE(STATHOUR, '06', TFVL)) AS L06, \r\n" " SUM(DECODE(STATHOUR, '07', TFVL)) AS L07, \r\n" " SUM(DECODE(STATHOUR, '08', TFVL)) AS L08, \r\n" " SUM(DECODE(STATHOUR, '09', TFVL)) AS L09, \r\n" " SUM(DECODE(STATHOUR, '10', TFVL)) AS L10, \r\n" " SUM(DECODE(STATHOUR, '11', TFVL)) AS L11, \r\n" " SUM(DECODE(STATHOUR, '12', TFVL)) AS L12, \r\n" " SUM(DECODE(STATHOUR, '13', TFVL)) AS L13, \r\n" " SUM(DECODE(STATHOUR, '14', TFVL)) AS L14, \r\n" " SUM(DECODE(STATHOUR, '15', TFVL)) AS L15, \r\n" " SUM(DECODE(STATHOUR, '16', TFVL)) AS L16, \r\n" " SUM(DECODE(STATHOUR, '17', TFVL)) AS L17, \r\n" " SUM(DECODE(STATHOUR, '18', TFVL)) AS L18, \r\n" " SUM(DECODE(STATHOUR, '19', TFVL)) AS L19, \r\n" " SUM(DECODE(STATHOUR, '20', TFVL)) AS L20, \r\n" " SUM(DECODE(STATHOUR, '21', TFVL)) AS L21, \r\n" " SUM(DECODE(STATHOUR, '22', TFVL)) AS L22, \r\n" " SUM(DECODE(STATHOUR, '23', TFVL)) AS L23 \r\n" " FROM ( \r\n" " SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS DTCT_NMBR, Y.VDS_DTCT_NM, \r\n" " TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, X.TRFTYPE, X.STATHOUR, X.TFVL \r\n" " FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_HH_STAT) */ \r\n" " 'DTCT' AS TRFTYPE, \r\n" " A.VDS_DTCT_NMBR AS DTCT_NMBR, SUBSTR(A.STAT_DT, 9, 2) AS STATHOUR, \r\n" " A.TFVL AS TFVL \r\n" " FROM TB_VDS_DTCT_HH_STAT A \r\n" " WHERE 1=1 \r\n" " AND A.STAT_DT BETWEEN :p01 AND :p02 \r\n" " ) X, \r\n" " TB_VDS_DTCT Y \r\n" " WHERE 1=1 \r\n" " AND Y.DEL_YN = 'N' \r\n" " AND Y.VDS_DTCT_NMBR = X.DTCT_NMBR \r\n" " ) \r\n" " GROUP BY ROLLUP (DTCT_NMBR, ISTL_LANE, TRFTYPE) \r\n" " HAVING GROUPING(TRFTYPE) = 0 \r\n" " ) LX, \r\n" " ( \r\n" " SELECT A.VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR AS DTCT_NMBR, \r\n" " B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_TYPE, \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CLSF_CD = 'DTT' \r\n" " AND CMMN_CD = B.DTCT_TYPE) AS DTCT_TYPE_NM \r\n" " FROM TB_VDS_CTLR A, \r\n" " TB_VDS_DTCT B \r\n" " WHERE 1=1 \r\n" " AND A.DEL_YN = 'N' \r\n" " AND B.DEL_YN = 'N' \r\n" " AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR \r\n" " ) LY \r\n" " WHERE LY.DTCT_NMBR = LX.DTCT_NMBR \r\n" //" WHERE LY.DTCT_NMBR = LX.DTCT_NMBR(+) \r\n" " ORDER BY LY.VDS_CTLR_NMBR, LY.DTCT_NMBR, LY.ISTL_LANE \r\n"; //" ORDER BY LY.VDS_CTLR_NMBR, LY.DTCT_TYPE_NM, LY.ISTL_LANE, LY.DTCT_NMBR \r\n"; try { ITSDb_SQLText(ADOD, sQry, false); ADOD->CursorType = ctOpenForwardOnly; ADOD->LockType = ltReadOnly; ITSDb_SQLBind(ADOD, "p01", sFrom); ITSDb_SQLBind(ADOD, "p02", sTo); ITSDb_SQLOpen(ADOD); ShowRecordCount(ADOD->RecordCount); } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, sQry); throw Exception(String(E.ClassName()) + E.Message); } catch(Exception &e) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(e.ClassName()), e.Message, sQry); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- /* * 일별 월 통계 데이터 조회/출력 * arguments * * return * void */ void __fastcall TVDSS020M::DispMonthStatistics() { String sQry = ""; String sMM; String sFrom, sTo; String sTmp; int ii; sMM = DtMon->Date.FormatString("yyyyMM"); sFrom = sMM + "01000000"; sTo = sMM + "31235959"; String sStTm = DtStTime->Time.FormatString("hhnn"); String sEdTm = DtEdTime->Time.FormatString("hhnn"); sQry = "SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM, \r\n" " LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE, LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVLX, \r\n" " DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+ \r\n" " NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+ \r\n" " NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+ \r\n" " NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+ \r\n" " NVL(L29,0)+NVL(L30,0)+NVL(L31,0), 0, 0, \r\n" " ROUND( \r\n" " (NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+ \r\n" " NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+ \r\n" " NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+ \r\n" " NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+ \r\n" " NVL(L29,0)+NVL(L30,0)+NVL(L31,0)) / \r\n" " (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+ \r\n" " DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+ \r\n" " DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+ \r\n" " DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)+ \r\n" " DECODE(L13,NULL,0,1)+DECODE(L14,NULL,0,1)+DECODE(L15,NULL,0,1)+ \r\n" " DECODE(L16,NULL,0,1)+DECODE(L17,NULL,0,1)+DECODE(L18,NULL,0,1)+ \r\n" " DECODE(L19,NULL,0,1)+DECODE(L20,NULL,0,1)+DECODE(L21,NULL,0,1)+ \r\n" " DECODE(L22,NULL,0,1)+DECODE(L23,NULL,0,1)+DECODE(L24,NULL,0,1)+ \r\n" " DECODE(L25,NULL,0,1)+DECODE(L26,NULL,0,1)+DECODE(L27,NULL,0,1)+ \r\n" " DECODE(L28,NULL,0,1)+DECODE(L29,NULL,0,1)+DECODE(L30,NULL,0,1)+ \r\n" " DECODE(L31,NULL,0,1)), 0)) AS AVGTFVL, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L01, NULL, 0, L01)) AS C01, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L02, NULL, 0, L02)) AS C02, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L03, NULL, 0, L03)) AS C03, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L04, NULL, 0, L04)) AS C04, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L05, NULL, 0, L05)) AS C05, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L06, NULL, 0, L06)) AS C06, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L07, NULL, 0, L07)) AS C07, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L08, NULL, 0, L08)) AS C08, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L09, NULL, 0, L09)) AS C09, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L10, NULL, 0, L10)) AS C10, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L11, NULL, 0, L11)) AS C11, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L12, NULL, 0, L12)) AS C12, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L13, NULL, 0, L13)) AS C13, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L14, NULL, 0, L14)) AS C14, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L15, NULL, 0, L15)) AS C15, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L16, NULL, 0, L16)) AS C16, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L17, NULL, 0, L17)) AS C17, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L18, NULL, 0, L18)) AS C18, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L19, NULL, 0, L19)) AS C19, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L20, NULL, 0, L20)) AS C20, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L21, NULL, 0, L21)) AS C21, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L22, NULL, 0, L22)) AS C22, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L23, NULL, 0, L23)) AS C23, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L24, NULL, 0, L24)) AS C24, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L25, NULL, 0, L25)) AS C25, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L26, NULL, 0, L26)) AS C26, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L27, NULL, 0, L27)) AS C27, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L28, NULL, 0, L28)) AS C28, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L29, NULL, 0, L29)) AS C29, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L30, NULL, 0, L30)) AS C30, \r\n" " DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L31, NULL, 0, L31)) AS C31 \r\n" " FROM ( \r\n" " SELECT GROUPING(DTCT_NMBR) AS GROUPING_DTCT_NMBR, \r\n" " GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE, \r\n" " GROUPING(TRFTYPE) AS GROUPING_TRFTYPE, \r\n" " CASE WHEN GROUPING(DTCT_NMBR ) = 1 THEN '검지기' ELSE DTCT_NMBR END AS GROUP_DTCT_NMBR, \r\n" " CASE WHEN GROUPING(ISTL_LANE) = 1 THEN '차로' ELSE ISTL_LANE END AS GROUP_ISTL_LANE, \r\n" " CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE, \r\n" " DTCT_NMBR, ISTL_LANE, TRFTYPE, \r\n" " SUM(TFVL) TOTTFVL, \r\n" " ROUND(AVG(TFVL), 0) AVGTFVL, \r\n" " SUM(DECODE(STATDAY, '01', TFVL)) AS L01, \r\n" " SUM(DECODE(STATDAY, '02', TFVL)) AS L02, \r\n" " SUM(DECODE(STATDAY, '03', TFVL)) AS L03, \r\n" " SUM(DECODE(STATDAY, '04', TFVL)) AS L04, \r\n" " SUM(DECODE(STATDAY, '05', TFVL)) AS L05, \r\n" " SUM(DECODE(STATDAY, '06', TFVL)) AS L06, \r\n" " SUM(DECODE(STATDAY, '07', TFVL)) AS L07, \r\n" " SUM(DECODE(STATDAY, '08', TFVL)) AS L08, \r\n" " SUM(DECODE(STATDAY, '09', TFVL)) AS L09, \r\n" " SUM(DECODE(STATDAY, '10', TFVL)) AS L10, \r\n" " SUM(DECODE(STATDAY, '11', TFVL)) AS L11, \r\n" " SUM(DECODE(STATDAY, '12', TFVL)) AS L12, \r\n" " SUM(DECODE(STATDAY, '13', TFVL)) AS L13, \r\n" " SUM(DECODE(STATDAY, '14', TFVL)) AS L14, \r\n" " SUM(DECODE(STATDAY, '15', TFVL)) AS L15, \r\n" " SUM(DECODE(STATDAY, '16', TFVL)) AS L16, \r\n" " SUM(DECODE(STATDAY, '17', TFVL)) AS L17, \r\n" " SUM(DECODE(STATDAY, '18', TFVL)) AS L18, \r\n" " SUM(DECODE(STATDAY, '19', TFVL)) AS L19, \r\n" " SUM(DECODE(STATDAY, '20', TFVL)) AS L20, \r\n" " SUM(DECODE(STATDAY, '21', TFVL)) AS L21, \r\n" " SUM(DECODE(STATDAY, '22', TFVL)) AS L22, \r\n" " SUM(DECODE(STATDAY, '23', TFVL)) AS L23, \r\n" " SUM(DECODE(STATDAY, '24', TFVL)) AS L24, \r\n" " SUM(DECODE(STATDAY, '25', TFVL)) AS L25, \r\n" " SUM(DECODE(STATDAY, '26', TFVL)) AS L26, \r\n" " SUM(DECODE(STATDAY, '27', TFVL)) AS L27, \r\n" " SUM(DECODE(STATDAY, '28', TFVL)) AS L28, \r\n" " SUM(DECODE(STATDAY, '29', TFVL)) AS L29, \r\n" " SUM(DECODE(STATDAY, '30', TFVL)) AS L30, \r\n" " SUM(DECODE(STATDAY, '31', TFVL)) AS L31 \r\n" " FROM ( \r\n" " SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS DTCT_NMBR, Y.VDS_DTCT_NM, \r\n" " TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, X.TRFTYPE, X.STATDAY, X.TFVL \r\n" " FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_HH_STAT) */ \r\n" " 'DTCT' AS TRFTYPE, \r\n" " A.VDS_DTCT_NMBR AS VDS_DTCT_NMBR, SUBSTR(A.STAT_DT, 7, 2) AS STATDAY, \r\n" " A.TFVL AS TFVL \r\n" " FROM TB_VDS_DTCT_HH_STAT A \r\n" " WHERE 1=1 \r\n" " AND A.STAT_DT BETWEEN :p01 AND :p02 \r\n" " AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN :p03 AND :p04 \r\n" " ) X, \r\n" " TB_VDS_DTCT Y \r\n" " WHERE 1=1 \r\n" " AND Y.DEL_YN = 'N' \r\n" " AND Y.VDS_DTCT_NMBR = X.VDS_DTCT_NMBR \r\n" " ) \r\n" " GROUP BY ROLLUP (DTCT_NMBR, ISTL_LANE, TRFTYPE) \r\n" " HAVING GROUPING(TRFTYPE) = 0 \r\n" " ) LX, \r\n" " ( \r\n" " SELECT A.VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR AS DTCT_NMBR, \r\n" " B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_TYPE, \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CLSF_CD = 'ACD' \r\n" " AND CMMN_CD = B.DTCT_TYPE) AS DTCT_TYPE_NM \r\n" " FROM TB_VDS_CTLR A, \r\n" " TB_VDS_DTCT B \r\n" " WHERE 1=1 \r\n" " AND A.DEL_YN = 'N' \r\n" " AND B.DEL_YN = 'N' \r\n" " AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR \r\n" " ) LY \r\n" " WHERE LY.DTCT_NMBR = LX.DTCT_NMBR \r\n" //" WHERE LY.DTCT_NMBR = LX.DTCT_NMBR(+) \r\n" " ORDER BY LY.VDS_CTLR_NMBR, LY.DTCT_NMBR, LY.ISTL_LANE \r\n"; //" ORDER BY LY.VDS_CTLR_NMBR, LY.DTCT_TYPE_NM, LY.ISTL_LANE, LY.DTCT_NMBR \r\n"; try { ITSDb_SQLText(ADOM, sQry, false); ADOM->CursorType = ctOpenForwardOnly; ADOM->LockType = ltReadOnly; ITSDb_SQLBind(ADOM, "p01", sFrom); ITSDb_SQLBind(ADOM, "p02", sTo); ITSDb_SQLBind(ADOM, "p03", sStTm); ITSDb_SQLBind(ADOM, "p04", sEdTm); ITSDb_SQLOpen(ADOM); ShowRecordCount(ADOM->RecordCount); } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, sQry); throw Exception(String(E.ClassName()) + E.Message); } catch(Exception &e) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(e.ClassName()), e.Message, sQry); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::ShowRecordCount(int nRecCnt) { // } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvListDataControllerSummaryFooterSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { CMM_SetSummaryItemKind(Arguments, OutArguments, 2, false, false); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvListDataControllerSummaryDefaultGroupSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { CMM_SetSummaryItemKind(Arguments, OutArguments, 1, false, false); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::BtnCloseClick(TObject *Sender) { Close(); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::vDayColumnCustomDrawCell(TcxCustomGridTableView *Sender, TcxCanvas *ACanvas, TcxGridTableDataCellViewInfo *AViewInfo, bool &ADone) { TColor tColor = ACanvas->Brush->Color; try { if (!VarIsNull(AViewInfo->Value)) { int sSpd = AViewInfo->Value; if (sSpd == NULL || sSpd == 0)// || sSpd == "-") { tColor = clFuchsia; } } else { tColor = clFuchsia; } ACanvas->Font->Color = clBlack; ACanvas->SetBrushColor(tColor); } catch(...) { } } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::FormClose(TObject *Sender, TCloseAction &Action) { CommClose(); VDSS020M = NULL; Action = caFree; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvDayTcxGridDBDataControllerTcxDataSummaryDefaultGroupSummaryItems0GetText(TcxDataSummaryItem *Sender, const Variant &AValue, bool AIsFooter, UnicodeString &AText) { AText = "소계"; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvDayTcxGridDBDataControllerTcxDataSummaryFooterSummaryItems0GetText(TcxDataSummaryItem *Sender, const Variant &AValue, bool AIsFooter, UnicodeString &AText) { AText = "합계"; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvDayDataControllerSummaryFooterSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { APP_SetSummaryItemKind(Arguments, OutArguments, 2, false, false); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvDayDataControllerSummaryDefaultGroupSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { APP_SetSummaryItemKind(Arguments, OutArguments, 1, false, false); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::ChkExpandClick(TObject *Sender) { CMM_ExpandCollapseChk(TvDay, ChkExpand->Checked); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::ChkExpand2Click(TObject *Sender) { CMM_ExpandCollapseChk(TvMon, ChkExpand2->Checked); } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvMonTcxGridDBDataControllerTcxDataSummaryFooterSummaryItems0GetText(TcxDataSummaryItem *Sender, const Variant &AValue, bool AIsFooter, UnicodeString &AText) { AText = "합계"; } //--------------------------------------------------------------------------- void __fastcall TVDSS020M::TvMonTcxGridDBDataControllerTcxDataSummaryDefaultGroupSummaryItems0GetText(TcxDataSummaryItem *Sender, const Variant &AValue, bool AIsFooter, UnicodeString &AText) { AText = "소계"; } //---------------------------------------------------------------------------