//--------------------------------------------------------------------------- #include #include "ITSSkinF.h" #include "ITSUtilF.h" #include "AppGlobalF.h" #include "ITSLangTransF.h" #include "ITS_OPLibF.h" #pragma hdrstop #include "IST0010MF.h" //--------------------------------------------------------------------------- #pragma package(smart_init) #pragma link "FRAME_ChartF" #pragma link "cxButtons" #pragma link "cxCalc" #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 "cxGridChartView" #pragma link "cxGridCustomTableView" #pragma link "cxGridCustomView" #pragma link "cxGridDBChartView" #pragma link "cxGridLevel" #pragma link "cxGridTableView" #pragma link "cxLabel" #pragma link "cxLookAndFeelPainters" #pragma link "cxLookAndFeels" #pragma link "cxPC" #pragma link "cxPCdxBarPopupMenu" #pragma link "cxSplitter" #pragma link "cxStyles" #pragma link "dxSkinBlack" #pragma link "dxSkinBlue" #pragma link "dxSkinsCore" #pragma link "dxSkinscxPCPainter" #pragma link "dxSkinMcSkin" #pragma resource "*.dfm" TIST0010M *IST0010M = NULL; //--------------------------------------------------------------------------- __fastcall TIST0010M::TIST0010M(TComponent* Owner) : TForm(Owner) { LangTrans->Translate(this, ITSDb_GetConnection()); ITSSkin_Load(this); CMM_LoadForm(g_sFormsDir, this); FTitle = Caption;//"인터넷접속 통계"; } //--------------------------------------------------------------------------- void __fastcall TIST0010M::FormCreate(TObject *Sender) { PgList->HideTabs = true; } //--------------------------------------------------------------------------- void __fastcall TIST0010M::FormShow(TObject *Sender) { PgList->ActivePageIndex = 0; PgQuery->ActivePageIndex= 0; FormInit(); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::FormInit() { DtDay->Date = IncHour(Now(), -24); DtMon->Date = StartOfTheMonth(IncHour(Now(), -24)); DtYear->Date = IncHour(Now(), -24); DtStYear->Date = IncHour(Now(), -24); DtEdYear->Date = IncHour(Now(), -24); ADOD->Connection = ITSDb_GetConnection(); ADOM->Connection = ITSDb_GetConnection(); ADOY->Connection = ITSDb_GetConnection(); ADOT->Connection = ITSDb_GetConnection(); FRAMEChart1->SetChartLegend(FrmLang->lblChartHour->Caption, FrmLang->lblChartCnt->Caption);//"시간대", "건수"); FRAMEChart1->ChartValueDisplay(FRAMEChart1->ChkDisplayVal->Checked); FRAMEChart1->TvChart->ToolBox->CustomizeButton = true; FRAMEChart1->TvChart->Legend->Position = cppDefault; FRAMEChart2->SetChartLegend(FrmLang->lblChartDay->Caption, FrmLang->lblChartCnt->Caption);//"일자", "건수"); FRAMEChart2->ChartValueDisplay(FRAMEChart2->ChkDisplayVal->Checked); FRAMEChart2->TvChart->ToolBox->CustomizeButton = true; FRAMEChart2->TvChart->Legend->Position = cppDefault; FRAMEChart3->SetChartLegend(FrmLang->lblChartMonth->Caption, FrmLang->lblChartCnt->Caption);//"월", "건수"); FRAMEChart3->ChartValueDisplay(FRAMEChart3->ChkDisplayVal->Checked); FRAMEChart3->TvChart->ToolBox->CustomizeButton = true; FRAMEChart3->TvChart->Legend->Position = cppDefault; FRAMEChart4->SetChartLegend(FrmLang->lblChartYear->Caption, FrmLang->lblChartCnt->Caption);//"년도", "건수"); FRAMEChart4->ChartValueDisplay(FRAMEChart4->ChkDisplayVal->Checked); FRAMEChart4->TvChart->ToolBox->CustomizeButton = true; FRAMEChart4->TvChart->Legend->Position = cppDefault; CMM_SetSummaryAlign((TcxGridTableView *)TvDay); CMM_SetSummaryAlign((TcxGridTableView *)TvMon); CMM_SetSummaryAlign((TcxGridTableView *)TvYear); CMM_SetSummaryAlign((TcxGridTableView *)TvTermYear); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::CommClose() { try { TvDay->BeginUpdate(); ADOD->Close(); TvDay->EndUpdate(); TvMon->BeginUpdate(); ADOM->Close(); TvMon->EndUpdate(); TvYear->BeginUpdate(); ADOY->Close(); TvYear->EndUpdate(); TvTermYear->BeginUpdate(); ADOT->Close(); TvTermYear->EndUpdate(); CMM_SaveForm(g_sFormsDir, this); } catch(...) { } } //--------------------------------------------------------------------------- void __fastcall TIST0010M::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; case 3: DtStYear->SetFocus(); break; default: break; } } //--------------------------------------------------------------------------- void __fastcall TIST0010M::BtnPrintClick(TObject *Sender) { #if 0 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; case 3: pGrid = CxTermYear; pView = TvTermYear; LblQry = LblQryTermYear; 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; PrintList(pGrid, sTitle, m_sFooterRight); pView->DataController->FocusedRecordIndex = 0; #endif } //--------------------------------------------------------------------------- void __fastcall TIST0010M::BtnExlSaveClick(TObject *Sender) { TcxGrid *pGrid = NULL; TcxGridBandedTableView *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; case 3: pGrid = CxTermYear; pView = TvTermYear; LblQry = LblQryTermYear; break; default: return; } sTitle = LblRptTitle->Caption + LblQry->Caption; if (pView->DataController->RecordCount == 0) { ShowMessage(FrmLang->lblSaveNoData->Caption);//"저장 할 데이터가 없습니다."); return; } CMM_ExportToExcelFile(sTitle, pGrid, (TcxGridTableView *)pView, this); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::BtnSearchClick(TObject *Sender) { Application->ProcessMessages(); TSqlCursor sqlCrs((TControl*)BtnSearch); Application->ProcessMessages(); RefreshData(); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::RefreshData() { TcxGridBandedTableView *pView = NULL; TLabel *LblQry= NULL; WORD nYear, nMonth, nDay, nHour, nMin, nSec, nMSec; int nDispCols; String sBandText = ""; TDateTimePicker *DtQryTm; int nStYear, nEdYear; switch(PgQuery->ActivePageIndex) { case 0: pView = (TcxGridBandedTableView *)TvDay; DtQryTm = DtDay; LblQry = LblQryTm; 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; // nDispCols = nHour+1; } break; case 1: pView = TvMon; DtQryTm = DtMon; sBandText = "(" + DtMon->Date.FormatString("yyyy년 mm월") + ")"; LblQry = LblQryDay; 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; } break; case 2: pView = TvYear; DtQryTm = DtYear; sBandText = "(" + DtYear->Date.FormatString("yyyy년") + ")"; LblQry = LblQryYear; nDispCols = 12; if (Now().FormatString("yyyy") == DtYear->DateTime.FormatString("yyyy")) { DecodeDate(Now(), nYear, nMonth, nDay); nDispCols = nMonth; } break; case 3: pView = TvTermYear; DtQryTm = DtStYear; nStYear = DtStYear->DateTime.FormatString("yyyy").ToInt(); nEdYear = DtEdYear->DateTime.FormatString("yyyy").ToInt(); if (nStYear > nEdYear) { MessageDlg(L"통계검색 시각 정보를 확인하세요...", mtInformation, TMsgDlgButtons() << mbOK, NULL); DtStYear->SetFocus(); nDispCols = 0; /* 리턴하지 않는 경우 모든 컬럼을 숨긴다. */ return; } if ((nEdYear - nStYear) > 23) { nEdYear = nStYear + 23; String sDateTime; sDateTime = String(nEdYear) + "-01-01 00:00:00"; DtEdYear->DateTime = StrToDateTime(sDateTime); } nDispCols = nEdYear - nStYear; nDispCols++; sBandText = "(" + DtStYear->Date.FormatString("yyyy년 ~ ") + DtEdYear->Date.FormatString("yyyy년") + ")"; LblQry = LblQryTermYear; #if 0 if (Now().FormatString("yyyy") == DtYear->DateTime.FormatString("yyyy")) { DecodeDate(Now(), nYear, nMonth, nDay); nDispCols = nMonth; } #endif break; default: return; } if (!pView) return; if (PgQuery->ActivePageIndex == 3) { } else { if (DtQryTm->DateTime.FormatString("yyyymmdd").ToInt() > Now().FormatString("yyyymmdd").ToInt()) { MessageDlg(L"통계검색 시각 정보를 확인하세요...", mtInformation, TMsgDlgButtons() << mbOK, NULL); DtQryTm->SetFocus(); nDispCols = 0; /* 리턴하지 않는 경우 모든 컬럼을 숨긴다. */ return; } } LblQry->Caption = sBandText; CMM_ShowListColumn((TcxGridDBBandedTableView *)pView, nDispCols); TWaitCursor crs; try { pView->BeginUpdate(); switch(PgQuery->ActivePageIndex) { case 0: SelDayStatistics(nDispCols); break; case 1: SelMonthStatistics(nDispCols); break; case 2: SelYearStatistics(nDispCols); break; case 3: SelTermYearStatistics(nDispCols); break; default: return; } } __finally { pView->EndUpdate(); //pView->ApplyBestFit(NULL, false, false); } DrawChart(sBandText); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::DrawChart(String sTitle) { #if 0 TcxGridDBBandedTableView *pView = NULL; String sAxisX, sAxisY; int nAxisY, nStColIdx, nTotColIdx; nTotColIdx = 1; switch(PgQuery->ActivePageIndex) { case 0: pView = TvDay; nAxisY = vDayColumnNm->Index; nStColIdx = vDayColumnTot->Index; break; case 1: pView = TvMon; nAxisY = vMonColumnNm->Index; nStColIdx = vMonColumnTot->Index; break; case 2: pView = TvYear; nAxisY = vYearColumnNm->Index; nStColIdx = vYearColumnTot->Index; break; case 3: pView = TvTermYear; nAxisY = cxGridDBBandedColumn2->Index; nStColIdx = cxGridDBBandedColumn3->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]->DrawChart1((TcxGridDBTableView *)pView, nAxisY, nStColIdx, nTotColIdx); m_pFrmChart[PgQuery->ActivePageIndex]->EndDraw(); } catch(...) { } #endif } //--------------------------------------------------------------------------- void __fastcall TIST0010M::SelDayStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOD; String sQry; String sParamDay; sParamDay = DtDay->Date.FormatString("yyyymmdd"); sQry = "SELECT DECODE(Y.PAGETP, 'W', '웹', 'S', '스마트폰', '') AS PAGETPNAME, \r\n" " Y.PAGEID, Y.PAGE_NAME, \r\n" " NVL(X.PAGEID,0) PAGEID, NVL(X.TOT,0) TOT, \r\n" " NVL(X.C00,0) C00, NVL(X.C01,0) C01, NVL(X.C02,0) C02, \r\n" " NVL(X.C03,0) C03, NVL(X.C04,0) C04, NVL(X.C05,0) C05, \r\n" " NVL(X.C06,0) C06, NVL(X.C07,0) C07, NVL(X.C08,0) C08, \r\n" " NVL(X.C09,0) C09, NVL(X.C10,0) C10, NVL(X.C11,0) C11, \r\n" " NVL(X.C12,0) C12, NVL(X.C13,0) C13, NVL(X.C14,0) C14, \r\n" " NVL(X.C15,0) C15, NVL(X.C16,0) C16, NVL(X.C17,0) C17, \r\n" " NVL(X.C18,0) C18, NVL(X.C19,0) C19, NVL(X.C20,0) C20, \r\n" " NVL(X.C21,0) C21, NVL(X.C22,0) C22, NVL(X.C23,0) C23 \r\n" " FROM (SELECT CASE WHEN GROUPING(A.PAGEID) = 1 THEN '0' \r\n" " ELSE A.PAGEID END AS PAGEID, \r\n" " SUM(A.CONNCNT) AS TOT, \r\n" " SUM(DECODE(A.STATHOUR, '00', A.CONNCNT, 0)) AS C00, \r\n" " SUM(DECODE(A.STATHOUR, '01', A.CONNCNT, 0)) AS C01, \r\n" " SUM(DECODE(A.STATHOUR, '02', A.CONNCNT, 0)) AS C02, \r\n" " SUM(DECODE(A.STATHOUR, '03', A.CONNCNT, 0)) AS C03, \r\n" " SUM(DECODE(A.STATHOUR, '04', A.CONNCNT, 0)) AS C04, \r\n" " SUM(DECODE(A.STATHOUR, '05', A.CONNCNT, 0)) AS C05, \r\n" " SUM(DECODE(A.STATHOUR, '06', A.CONNCNT, 0)) AS C06, \r\n" " SUM(DECODE(A.STATHOUR, '07', A.CONNCNT, 0)) AS C07, \r\n" " SUM(DECODE(A.STATHOUR, '08', A.CONNCNT, 0)) AS C08, \r\n" " SUM(DECODE(A.STATHOUR, '09', A.CONNCNT, 0)) AS C09, \r\n" " SUM(DECODE(A.STATHOUR, '10', A.CONNCNT, 0)) AS C10, \r\n" " SUM(DECODE(A.STATHOUR, '11', A.CONNCNT, 0)) AS C11, \r\n" " SUM(DECODE(A.STATHOUR, '12', A.CONNCNT, 0)) AS C12, \r\n" " SUM(DECODE(A.STATHOUR, '13', A.CONNCNT, 0)) AS C13, \r\n" " SUM(DECODE(A.STATHOUR, '14', A.CONNCNT, 0)) AS C14, \r\n" " SUM(DECODE(A.STATHOUR, '15', A.CONNCNT, 0)) AS C15, \r\n" " SUM(DECODE(A.STATHOUR, '16', A.CONNCNT, 0)) AS C16, \r\n" " SUM(DECODE(A.STATHOUR, '17', A.CONNCNT, 0)) AS C17, \r\n" " SUM(DECODE(A.STATHOUR, '18', A.CONNCNT, 0)) AS C18, \r\n" " SUM(DECODE(A.STATHOUR, '19', A.CONNCNT, 0)) AS C19, \r\n" " SUM(DECODE(A.STATHOUR, '20', A.CONNCNT, 0)) AS C20, \r\n" " SUM(DECODE(A.STATHOUR, '21', A.CONNCNT, 0)) AS C21, \r\n" " SUM(DECODE(A.STATHOUR, '22', A.CONNCNT, 0)) AS C22, \r\n" " SUM(DECODE(A.STATHOUR, '23', A.CONNCNT, 0)) AS C23 \r\n" " FROM TB_ST_HOUR_CONN_WEB A \r\n" " WHERE A.STATDATE = :p01 \r\n" " GROUP BY ROLLUP(A.PAGEID)) X, \r\n" " (SELECT PAGEID, PAGETP, PAGETP || ':' || PAGENAME AS PAGE_NAME \r\n" " FROM TB_MS_WEB_PAGE) Y \r\n" " WHERE Y.PAGEID = X.PAGEID(+) \r\n" " ORDER BY Y.PAGEID \r\n"; try { ITSDb_SQLText(pADO, sQry); ITSDb_SQLBind(pADO, "p01", sParamDay); ITSDb_SQLOpen(pADO); DspDayStatistics(ADispCols); } 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 TIST0010M::DspDayStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOD; TcxGridTableView *TvList = (TcxGridTableView*)TvDay; TcxDataController *pGDC = TvList->DataController; TcxGridChartView *TvChart = FRAMEChart1->TvChart; FRAMEChart1->FTitle = LblQryTm->Caption; try { TcxGridChartSeries *pChart = NULL; TvChart->ClearSeries(); FRAMEChart1->ChartValueDisplay(FRAMEChart1->ChkDisplayVal->Checked); CMM_ClearGridTableView(TvList); int nRow = 0; int nDataCnt = 0; try { TvList->BeginUpdate(); TvChart->BeginUpdate(); nDataCnt = pADO->RecordCount; TvChart->DataController->RecordCount = ADispCols+1; pGDC->RecordCount = nDataCnt; int ii; String sCol, sChart; TvChart->Categories->Values[0] = FrmLang->lblChartSoge->Caption;//"소계"; for (ii = 0; ii < ADispCols; ii++) { sChart.printf(L"%02d", ii); TvChart->Categories->Values[ii+1] = sChart; } for( ; !pADO->Eof; pADO->Next(), nRow++) { String sId = pADO->FieldByName("PAGEID")->AsString; String sNm = pADO->FieldByName("PAGE_NAME")->AsString; pGDC->Values[nRow][ColDayNo->Index] = sId; pGDC->Values[nRow][ColDayNm->Index] = sNm; pChart = TvChart->CreateSeries(); pChart->DataBinding->ValueType = "Integer"; pChart->DisplayText = sNm; pGDC->Values[nRow][ColDayTt->Index] = pADO->FieldByName("TOT")->AsInteger; pChart->Values[0] = pGDC->Values[nRow][ColDayTt->Index]; for (int ii = 0; ii < ADispCols; ii++) { sCol.printf(L"C%02d", ii); pGDC->Values[nRow][ColDay00->Index+ii] = pADO->FieldByName(sCol)->AsInteger; pChart->Values[ii+1] = pGDC->Values[nRow][ColDay00->Index+ii]; } } } __finally { if (pADO) { pADO->Close(); } TvChart->EndUpdate(); TvList->EndUpdate(); } } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, pADO->SQL->Text); 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, pADO->SQL->Text); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- /* * 일별 월 통계 데이터 조회/출력 * arguments * * return * void */ void __fastcall TIST0010M::SelMonthStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOM; String sQry; String sQryDt; sQryDt = DtMon->Date.FormatString("yyyymm"); sQry = "SELECT DECODE(Y.PAGETP, 'W', '웹', 'S', '스마트폰', '') AS PAGETPNAME, \r\n" " Y.PAGEID, Y.PAGE_NAME, \r\n" " NVL(X.PAGEID, 0) PAGE, NVL(X.TOT,0) TOT, \r\n" " NVL(X.C31,0) C31, NVL(X.C01,0) C01, NVL(X.C02,0) C02, \r\n" " NVL(X.C03,0) C03, NVL(X.C04,0) C04, NVL(X.C05,0) C05, \r\n" " NVL(X.C06,0) C06, NVL(X.C07,0) C07, NVL(X.C08,0) C08, \r\n" " NVL(X.C09,0) C09, NVL(X.C10,0) C10, NVL(X.C11,0) C11, \r\n" " NVL(X.C12,0) C12, NVL(X.C13,0) C13, NVL(X.C14,0) C14, \r\n" " NVL(X.C15,0) C15, NVL(X.C16,0) C16, NVL(X.C17,0) C17, \r\n" " NVL(X.C18,0) C18, NVL(X.C19,0) C19, NVL(X.C20,0) C20, \r\n" " NVL(X.C21,0) C21, NVL(X.C22,0) C22, NVL(X.C23,0) C23, \r\n" " NVL(X.C24,0) C24, NVL(X.C25,0) C25, NVL(X.C26,0) C26, \r\n" " NVL(X.C27,0) C27, NVL(X.C28,0) C28, NVL(X.C29,0) C29, \r\n" " NVL(X.C30,0) C30 \r\n" " FROM (SELECT CASE WHEN GROUPING(A.PAGEID) = 1 THEN '0' \r\n" " ELSE A.PAGEID END AS PAGEID, \r\n" " SUM(A.CONNCNT) AS TOT, \r\n" " SUM(DECODE(A.STATDAY, '01', A.CONNCNT, 0)) AS C01, \r\n" " SUM(DECODE(A.STATDAY, '02', A.CONNCNT, 0)) AS C02, \r\n" " SUM(DECODE(A.STATDAY, '03', A.CONNCNT, 0)) AS C03, \r\n" " SUM(DECODE(A.STATDAY, '04', A.CONNCNT, 0)) AS C04, \r\n" " SUM(DECODE(A.STATDAY, '05', A.CONNCNT, 0)) AS C05, \r\n" " SUM(DECODE(A.STATDAY, '06', A.CONNCNT, 0)) AS C06, \r\n" " SUM(DECODE(A.STATDAY, '07', A.CONNCNT, 0)) AS C07, \r\n" " SUM(DECODE(A.STATDAY, '08', A.CONNCNT, 0)) AS C08, \r\n" " SUM(DECODE(A.STATDAY, '09', A.CONNCNT, 0)) AS C09, \r\n" " SUM(DECODE(A.STATDAY, '10', A.CONNCNT, 0)) AS C10, \r\n" " SUM(DECODE(A.STATDAY, '11', A.CONNCNT, 0)) AS C11, \r\n" " SUM(DECODE(A.STATDAY, '12', A.CONNCNT, 0)) AS C12, \r\n" " SUM(DECODE(A.STATDAY, '13', A.CONNCNT, 0)) AS C13, \r\n" " SUM(DECODE(A.STATDAY, '14', A.CONNCNT, 0)) AS C14, \r\n" " SUM(DECODE(A.STATDAY, '15', A.CONNCNT, 0)) AS C15, \r\n" " SUM(DECODE(A.STATDAY, '16', A.CONNCNT, 0)) AS C16, \r\n" " SUM(DECODE(A.STATDAY, '17', A.CONNCNT, 0)) AS C17, \r\n" " SUM(DECODE(A.STATDAY, '18', A.CONNCNT, 0)) AS C18, \r\n" " SUM(DECODE(A.STATDAY, '19', A.CONNCNT, 0)) AS C19, \r\n" " SUM(DECODE(A.STATDAY, '20', A.CONNCNT, 0)) AS C20, \r\n" " SUM(DECODE(A.STATDAY, '21', A.CONNCNT, 0)) AS C21, \r\n" " SUM(DECODE(A.STATDAY, '22', A.CONNCNT, 0)) AS C22, \r\n" " SUM(DECODE(A.STATDAY, '23', A.CONNCNT, 0)) AS C23, \r\n" " SUM(DECODE(A.STATDAY, '24', A.CONNCNT, 0)) AS C24, \r\n" " SUM(DECODE(A.STATDAY, '25', A.CONNCNT, 0)) AS C25, \r\n" " SUM(DECODE(A.STATDAY, '26', A.CONNCNT, 0)) AS C26, \r\n" " SUM(DECODE(A.STATDAY, '27', A.CONNCNT, 0)) AS C27, \r\n" " SUM(DECODE(A.STATDAY, '28', A.CONNCNT, 0)) AS C28, \r\n" " SUM(DECODE(A.STATDAY, '29', A.CONNCNT, 0)) AS C29, \r\n" " SUM(DECODE(A.STATDAY, '30', A.CONNCNT, 0)) AS C30, \r\n" " SUM(DECODE(A.STATDAY, '31', A.CONNCNT, 0)) AS C31 \r\n" " FROM TB_ST_DAY_CONN_WEB A \r\n" " WHERE A.STATMONTH = :p01 \r\n" " GROUP BY ROLLUP(A.PAGEID)) X, \r\n" " (SELECT PAGEID, PAGETP, PAGETP || ':' || PAGENAME AS PAGE_NAME \r\n" " FROM TB_MS_WEB_PAGE) Y \r\n" " WHERE Y.PAGEID = X.PAGEID(+) \r\n" " ORDER BY Y.PAGEID \r\n"; try { ITSDb_SQLText(pADO, sQry); ITSDb_SQLBind(pADO, "p01", sQryDt); ITSDb_SQLOpen(pADO); DspMonthStatistics(ADispCols); } 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 TIST0010M::DspMonthStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOM; TcxGridTableView *TvList = (TcxGridTableView*)TvMon; TcxDataController *pGDC = TvList->DataController; TcxGridChartView *TvChart = FRAMEChart2->TvChart; FRAMEChart2->FTitle = LblQryDay->Caption; try { TcxGridChartSeries *pChart = NULL; TvChart->ClearSeries(); FRAMEChart2->ChartValueDisplay(FRAMEChart2->ChkDisplayVal->Checked); CMM_ClearGridTableView(TvList); int nRow = 0; int nDataCnt = 0; try { TvList->BeginUpdate(); TvChart->BeginUpdate(); nDataCnt = pADO->RecordCount; TvChart->DataController->RecordCount = ADispCols+1; pGDC->RecordCount = nDataCnt; int ii; String sCol, sChart; TvChart->Categories->Values[0] = FrmLang->lblChartSoge->Caption;//"소계"; for (ii = 0; ii < ADispCols; ii++) { sChart.printf(L"%02d", ii+1); TvChart->Categories->Values[ii+1] = sChart; } for( ; !pADO->Eof; pADO->Next(), nRow++) { String sId = pADO->FieldByName("PAGEID")->AsString; String sNm = pADO->FieldByName("PAGE_NAME")->AsString; pGDC->Values[nRow][ColMonNo->Index] = sId; pGDC->Values[nRow][ColMonNm->Index] = sNm; pChart = TvChart->CreateSeries(); pChart->DataBinding->ValueType = "Integer"; pChart->DisplayText = sNm; pGDC->Values[nRow][ColMonTt->Index] = pADO->FieldByName("TOT")->AsInteger; pChart->Values[0] = pGDC->Values[nRow][ColMonTt->Index]; for (int ii = 0; ii < ADispCols; ii++) { sCol.printf(L"C%02d", ii+1); pGDC->Values[nRow][ColMon01->Index+ii] = pADO->FieldByName(sCol)->AsInteger; pChart->Values[ii+1] = pGDC->Values[nRow][ColMon01->Index+ii]; } } } __finally { if (pADO) { pADO->Close(); } TvChart->EndUpdate(); TvList->EndUpdate(); } } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, pADO->SQL->Text); 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, pADO->SQL->Text); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- /* * 월별 년 통계 데이터 조회/출력 * arguments * * return * void */ void __fastcall TIST0010M::SelYearStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOY; String sQry; String sQryDt; sQryDt = DtYear->Date.FormatString("yyyy"); sQry = "SELECT DECODE(Y.PAGETP, 'W', '웹', 'S', '스마트폰', '') AS PAGETPNAME, \r\n" " Y.PAGEID, Y.PAGE_NAME, \r\n" " NVL(X.PAGEID, 0) PAGE, NVL(X.TOT,0) TOT, \r\n" " NVL(X.C12,0) C12, NVL(X.C01,0) C01, NVL(X.C02,0) C02, \r\n" " NVL(X.C03,0) C03, NVL(X.C04,0) C04, NVL(X.C05,0) C05, \r\n" " NVL(X.C06,0) C06, NVL(X.C07,0) C07, NVL(X.C08,0) C08, \r\n" " NVL(X.C09,0) C09, NVL(X.C10,0) C10, NVL(X.C11,0) C11 \r\n" " FROM (SELECT CASE WHEN GROUPING(A.PAGEID) = 1 THEN '0' \r\n" " ELSE A.PAGEID END AS PAGEID, \r\n" " SUM(A.CONNCNT) AS TOT, \r\n" " SUM(DECODE(A.STATMON, '01', A.CONNCNT, 0)) AS C01, \r\n" " SUM(DECODE(A.STATMON, '02', A.CONNCNT, 0)) AS C02, \r\n" " SUM(DECODE(A.STATMON, '03', A.CONNCNT, 0)) AS C03, \r\n" " SUM(DECODE(A.STATMON, '04', A.CONNCNT, 0)) AS C04, \r\n" " SUM(DECODE(A.STATMON, '05', A.CONNCNT, 0)) AS C05, \r\n" " SUM(DECODE(A.STATMON, '06', A.CONNCNT, 0)) AS C06, \r\n" " SUM(DECODE(A.STATMON, '07', A.CONNCNT, 0)) AS C07, \r\n" " SUM(DECODE(A.STATMON, '08', A.CONNCNT, 0)) AS C08, \r\n" " SUM(DECODE(A.STATMON, '09', A.CONNCNT, 0)) AS C09, \r\n" " SUM(DECODE(A.STATMON, '10', A.CONNCNT, 0)) AS C10, \r\n" " SUM(DECODE(A.STATMON, '11', A.CONNCNT, 0)) AS C11, \r\n" " SUM(DECODE(A.STATMON, '12', A.CONNCNT, 0)) AS C12 \r\n" " FROM TB_ST_MON_CONN_WEB A \r\n" " WHERE A.STATYEAR = :p01 \r\n" " GROUP BY ROLLUP(A.PAGEID)) X, \r\n" " (SELECT PAGEID, PAGETP, PAGETP || ':' || PAGENAME AS PAGE_NAME \r\n" " FROM TB_MS_WEB_PAGE) Y \r\n" " WHERE Y.PAGEID = X.PAGEID(+) \r\n" " ORDER BY Y.PAGEID \r\n"; try { ITSDb_SQLText(pADO, sQry); ITSDb_SQLBind(pADO, "p01", sQryDt); ITSDb_SQLOpen(pADO); DspYearStatistics(ADispCols); } 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 TIST0010M::DspYearStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOY; TcxGridTableView *TvList = (TcxGridTableView*)TvYear; TcxDataController *pGDC = TvList->DataController; TcxGridChartView *TvChart = FRAMEChart3->TvChart; FRAMEChart3->FTitle = LblQryYear->Caption; try { TcxGridChartSeries *pChart = NULL; TvChart->ClearSeries(); FRAMEChart3->ChartValueDisplay(FRAMEChart3->ChkDisplayVal->Checked); CMM_ClearGridTableView(TvList); int nRow = 0; int nDataCnt = 0; try { TvList->BeginUpdate(); TvChart->BeginUpdate(); nDataCnt = pADO->RecordCount; TvChart->DataController->RecordCount = ADispCols+1; pGDC->RecordCount = nDataCnt; int ii; String sCol, sChart; TvChart->Categories->Values[0] = FrmLang->lblChartSoge->Caption;//"소계"; for (ii = 0; ii < ADispCols; ii++) { sChart.printf(L"%02d", ii+1); TvChart->Categories->Values[ii+1] = sChart; } for( ; !pADO->Eof; pADO->Next(), nRow++) { String sId = pADO->FieldByName("PAGEID")->AsString; String sNm = pADO->FieldByName("PAGE_NAME")->AsString; pGDC->Values[nRow][ColYearNo->Index] = sId; pGDC->Values[nRow][ColYearNm->Index] = sNm; pChart = TvChart->CreateSeries(); pChart->DataBinding->ValueType = "Integer"; pChart->DisplayText = sNm; pGDC->Values[nRow][ColYearTt->Index] = pADO->FieldByName("TOT")->AsInteger; pChart->Values[0] = pGDC->Values[nRow][ColYearTt->Index]; for (int ii = 0; ii < ADispCols; ii++) { sCol.printf(L"C%02d", ii+1); pGDC->Values[nRow][ColYear01->Index+ii] = pADO->FieldByName(sCol)->AsInteger; pChart->Values[ii+1] = pGDC->Values[nRow][ColYear01->Index+ii]; } } } __finally { if (pADO) { pADO->Close(); } TvChart->EndUpdate(); TvList->EndUpdate(); } } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, pADO->SQL->Text); 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, pADO->SQL->Text); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- void __fastcall TIST0010M::SelTermYearStatistics(int ADispCols) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOT; String sQry; String sCap, sTemp, sTemp2; String sStYear, sEdYear; sTemp = ""; sTemp2= ""; int ii; int nStYear = DtStYear->DateTime.FormatString("yyyy").ToInt(); int nEdYear = DtEdYear->DateTime.FormatString("yyyy").ToInt(); int nYears = nEdYear - nStYear; nYears++; sStYear = DtStYear->Date.FormatString("yyyy"); sEdYear = DtEdYear->Date.FormatString("yyyy"); TcxGridBand *pBand; try { TvTermYear->BeginUpdate(); pBand = TvTermYear->Bands->Items[1]; pBand->Visible = false; int nCols = pBand->ColumnCount; try { for (ii = 0; ii < nCols; ii++) { pBand->Columns[ii]->Visible = false; } for (ii = 0; ii < nYears && ii < 24; ii++) { TcxGridBandedColumn *pCol = TvTermYear->Columns[ColTYear00->Index+ii]; sCap.printf(L"%04d", ii+nStYear); pCol->Caption = sCap; pCol->Width = 70; pCol->Visible = true; if (ii != 0) { sTemp += ", \r\n"; sTemp2 += ", \r\n"; } sCap.printf(L" NVL(X.C%02d,0) C%02d", ii, ii); sTemp += sCap; sCap.printf(L" SUM(DECODE(A.STATYEAR, '%04d', A.CONNCNT, 0)) AS C%02d", ii + nStYear, ii); sTemp2 += sCap; } sTemp += "\r\n"; sTemp2 += "\r\n"; } catch(...) { } } __finally { pBand->Width = nYears*70; pBand->Visible = true; TvTermYear->EndUpdate(); } sQry = "SELECT DECODE(Y.PAGETP, 'W', '웹', 'S', '스마트폰', '') AS PAGETPNAME, \r\n" " Y.PAGEID, Y.PAGE_NAME, \r\n" " NVL(X.PAGEID, 0) PAGE, NVL(X.TOT,0) TOT, \r\n" + sTemp + // " NVL(X.C00,0) C00, \r\n" // " NVL(X.C01,0) C01, \r\n" // " NVL(X.C02,0) C02 \r\n" " FROM (SELECT CASE WHEN GROUPING(A.PAGEID) = 1 THEN '0' \r\n" " ELSE A.PAGEID END AS PAGEID, \r\n" " SUM(A.CONNCNT) AS TOT, \r\n" + sTemp2 + // " SUM(DECODE(A.STATYEAR, '2010', A.CONNCNT, 0)) AS C00, \r\n" // " SUM(DECODE(A.STATYEAR, '2011', A.CONNCNT, 0)) AS C01, \r\n" // " SUM(DECODE(A.STATYEAR, '2012', A.CONNCNT, 0)) AS C02 \r\n" " FROM TB_ST_MON_CONN_WEB A \r\n" " WHERE A.STATYEAR BETWEEN :p01 AND :p02 \r\n" " GROUP BY ROLLUP(A.PAGEID)) X, \r\n" " (SELECT PAGEID, PAGETP, PAGETP || ':' || PAGENAME AS PAGE_NAME \r\n" " FROM TB_MS_WEB_PAGE) Y \r\n" " WHERE Y.PAGEID = X.PAGEID(+) \r\n" " ORDER BY Y.PAGEID \r\n"; try { ITSDb_SQLText(pADO, sQry); ITSDb_SQLBind(pADO, "p01", sStYear); ITSDb_SQLBind(pADO, "p02", sEdYear); ITSDb_SQLOpen(pADO); DspTermYearStatistics(ADispCols, nStYear); } 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 &exception) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(exception.ClassName()), exception.Message, sQry); throw Exception(String(exception.ClassName()) + exception.Message); } } //--------------------------------------------------------------------------- void __fastcall TIST0010M::DspTermYearStatistics(int ADispCols, int AStYear) { TSqlCursor sqlCrs; TADOQuery *pADO = ADOT; TcxGridTableView *TvList = (TcxGridTableView*)TvTermYear; TcxDataController *pGDC = TvList->DataController; TcxGridChartView *TvChart = FRAMEChart4->TvChart; FRAMEChart4->FTitle = LblQryTermYear->Caption; try { TcxGridChartSeries *pChart = NULL; TvChart->ClearSeries(); FRAMEChart4->ChartValueDisplay(FRAMEChart4->ChkDisplayVal->Checked); CMM_ClearGridTableView(TvList); int nRow = 0; int nDataCnt = 0; try { TvList->BeginUpdate(); TvChart->BeginUpdate(); nDataCnt = pADO->RecordCount; TvChart->DataController->RecordCount = ADispCols+1; pGDC->RecordCount = nDataCnt; int ii; String sCol, sChart; TvChart->Categories->Values[0] = FrmLang->lblChartSoge->Caption;//"소계"; for (ii = 0; ii < ADispCols; ii++) { sChart.printf(L"%02d", AStYear + ii); TvChart->Categories->Values[ii+1] = sChart; } for( ; !pADO->Eof; pADO->Next(), nRow++) { String sId = pADO->FieldByName("PAGEID")->AsString; String sNm = pADO->FieldByName("PAGE_NAME")->AsString; pGDC->Values[nRow][ColTYearNo->Index] = sId; pGDC->Values[nRow][ColTYearNm->Index] = sNm; pChart = TvChart->CreateSeries(); pChart->DataBinding->ValueType = "Integer"; pChart->DisplayText = sNm; pGDC->Values[nRow][ColTYearTt->Index] = pADO->FieldByName("TOT")->AsInteger; pChart->Values[0] = pGDC->Values[nRow][ColTYearTt->Index]; for (int ii = 0; ii < ADispCols; ii++) { sCol.printf(L"C%02d", ii); pGDC->Values[nRow][ColTYear00->Index+ii] = pADO->FieldByName(sCol)->AsInteger; pChart->Values[ii+1] = pGDC->Values[nRow][ColTYear00->Index+ii]; } } } __finally { if (pADO) { pADO->Close(); } TvChart->EndUpdate(); TvList->EndUpdate(); } } catch(EDatabaseError &E) { ::PostMessage(Application->MainForm->Handle, (UINT)(WM_USER+0xF4), (WPARAM)0xB2, (LPARAM)0xB2); DBERRORMSG(Caption, String(E.ClassName()), E.Message, pADO->SQL->Text); 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, pADO->SQL->Text); throw Exception(String(e.ClassName()) + e.Message); } } //--------------------------------------------------------------------------- void __fastcall TIST0010M::TvListDataControllerSummaryFooterSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { CMM_SetSummaryItemKind(Arguments, OutArguments, 2, false, false); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::TvListDataControllerSummaryDefaultGroupSummaryItemsSummary(TcxDataSummaryItems *ASender, TcxSummaryEventArguments &Arguments, TcxSummaryEventOutArguments &OutArguments) { CMM_SetSummaryItemKind(Arguments, OutArguments, 1, false, false); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::BtnCloseClick(TObject *Sender) { Close(); } //--------------------------------------------------------------------------- void __fastcall TIST0010M::FormClose(TObject *Sender, TCloseAction &Action) { CommClose(); IST0010M = NULL; Action = caFree; } //---------------------------------------------------------------------------