ITS-OP/SRC/PLUGIN/04.STATISTICAL-ANALYSIS/IAN0050M/IAN00501F.cpp

//---------------------------------------------------------------------------

#include <vcl.h>
#include "ITSSkinF.h"
#include "ITSUtilF.h"
#include "ITSDbF.h"
#include "AppGlobalF.h"
#include "WindowMsgF.h"
#include "ITSLangTransF.h"
#pragma hdrstop

#include "IAN00501F.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "FRAME_ChartF"
#pragma link "cxButtons"
#pragma link "cxCalc"
#pragma link "cxCheckBox"
#pragma link "cxClasses"
#pragma link "cxContainer"
#pragma link "cxControls"
#pragma link "cxCustomData"
#pragma link "cxData"
#pragma link "cxDataStorage"
#pragma link "cxEdit"
#pragma link "cxFilter"
#pragma link "cxGraphics"
#pragma link "cxGrid"
#pragma link "cxGridBandedTableView"
#pragma link "cxGridCustomTableView"
#pragma link "cxGridCustomView"
#pragma link "cxGridLevel"
#pragma link "cxGridTableView"
#pragma link "cxLookAndFeelPainters"
#pragma link "cxLookAndFeels"
#pragma link "cxSplitter"
#pragma link "cxStyles"
#pragma link "cxTextEdit"
#pragma link "dxSkinBlack"
#pragma link "dxSkinBlue"
#pragma link "dxSkinsCore"
#pragma link "dxSkinscxPCPainter"
#pragma link "cxLabel"
#pragma resource "*.dfm"

TIAN00501 *IAN00501 = NULL;

//---------------------------------------------------------------------------
__fastcall TIAN00501::TIAN00501(TComponent* Owner, HWND hHandle,
                                String ALinkId, String ABaseMon, String ADiffMon, String AStTime, String AEdTime,
                                int ALinkLevel, int APatternType, String APattrernWeek, String AWeekName)
    : TForm(Owner)
{           
    LangTrans->Translate(this, ITSDb_GetConnection());
    ITSSkin_Load(this);
    CMM_LoadForm(g_sFormsDir, this);

    FParent      = hHandle;

    FLinkId      = ALinkId;
	FBaseMon     = ABaseMon,
    FDiffMon     = ADiffMon;
	FStTime      = AStTime,
    FEdTime      = AEdTime;
    FLinkLevel   = ALinkLevel;
    FPatternType = APatternType;
    FPatternWeek = APattrernWeek;
    FWeekName    = AWeekName;

    //Caption = "패턴데이터 분석";
}
//---------------------------------------------------------------------------
__fastcall TIAN00501::~TIAN00501(void)
{
}
//--------------------------------------------------------------------------

void __fastcall TIAN00501::FormInit()
{
    ADOQry->Connection = ITSDb_GetConnection();
    m_pGDC = TvList->DataController;

    TvList->OptionsView->NoDataToDisplayInfoText = FrmLang->lblNoInfo->Caption;//"<패턴데이터 분석 정보>";

    FRAMEChart1->FTitle = Caption;//"패턴데이터 분석";
    //FRAMEChart1->SetChartLegend("패턴시각", "속도(Km)");
    FRAMEChart1->SetChartLegend(lblText1->Caption, "");//"패턴시각", "");
    FRAMEChart1->ChartValueDisplay(FRAMEChart1->ChkDisplayVal->Checked);
    FRAMEChart1->TvChart->ToolBox->CustomizeButton = true;
    FRAMEChart1->TvChart->Legend->Position = cppDefault;

    LblSearch->Caption = FrmLang->lblQryCond->Caption + FLinkId + " " + FWeekName ;
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::FormShow(TObject *Sender)
{
    FormInit();
    Refresh();
    TmrShow->Enabled = true;
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::CommClose()
{
    CMM_SaveForm(g_sFormsDir, this);
    //IAN00501 = NULL;
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::TmrShowTimer(TObject *Sender)
{
    TmrShow->Enabled = false; 
    Application->ProcessMessages();
    SelHistory();
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::ChkExpandClick(TObject *Sender)
{
    CMM_ExpandCollapseChk(TvList, ChkExpand->Checked);
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::SelHistory()
{
    TSqlCursor sqlCrs;

    String sQry;
    TADOQuery *pADO = ADOQry;

    String sPatternTable;
	if (FLinkLevel == 0)
		sPatternTable = FPatternType == 0 ? "TB_LINK_5M_PTRN" : "TB_LINK_15M_PTRN";
	else
    if (FLinkLevel == 1)
		sPatternTable = FPatternType == 0 ? "TB_IFSC_5M_PTRN" : "TB_IFSC_15M_PTRN";
	else
		sPatternTable = FPatternType == 0 ? "TB_ROAD_5M_PTRN" : "TB_ROAD_15M_PTRN";



	if (FLinkLevel == 0)
    {
        sQry = "SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,                                 \r\n"
               "       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM,                                                             \r\n"
               "       MAX(DECODE(STAT_YM, :p01, TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, :p02, SPED,    0)) SPED_A,               \r\n"
               "       MAX(DECODE(STAT_YM, :p03, OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, :p04, TRVL_HH, 0)) TRVLHH_A,             \r\n"
               "       MAX(DECODE(STAT_YM, :p05, TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, :p06, SPED,    0)) SPED_B,               \r\n"
               "       MAX(DECODE(STAT_YM, :p07, OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, :p08, TRVL_HH, 0)) TRVLHH_B              \r\n"
               "  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,                                           \r\n"
               "               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH                                          \r\n"
               "          FROM (SELECT T2.LINK_ID, T2.F_NODE_ID, T2.T_NODE_ID, T2.LINK_LENG, T3.NODE_NAME||'-'||T4.NODE_NAME SECT_NAME,  \r\n"
               "                       T2.SECT_GRAD_CD                                                                                   \r\n"
               "                  FROM TB_LINK T2, TB_NODE T3, TB_NODE T4                                                                \r\n"
               "                 WHERE T2.LINK_ID   = :p09                                                                               \r\n"
               "                   AND T2.F_NODE_ID = T3.NODE_ID                                                                         \r\n"
               "                   AND T2.T_NODE_ID = T4.NODE_ID                                                                         \r\n"
               "               ) A,                                                                                                      \r\n"
               "               (SELECT STAT_YM, LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH                            \r\n"
               "                  FROM " + sPatternTable + "                                                                             \r\n"
               "                 WHERE LINK_ID = :p10                                                                                    \r\n"
               "                   AND STAT_YM IN(:p11, :p12)                                                                            \r\n"
               "                   AND STAT_HM BETWEEN :p13 AND :p14                                                                     \r\n"
               "                   AND DAY_TYPE_CD = :p15                                                                                \r\n"
               "               ) B                                                                                                       \r\n"
               "         WHERE A.LINK_ID = B.LINK_ID                                                                                     \r\n"
               "      )                                                                                                                  \r\n"
               " GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM                                                                                  \r\n"
               " ORDER BY STAT_HM                                                                                                        \r\n";
	}
    else if (FLinkLevel == 1)
    {
        sQry = "SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,                                 \r\n"
               "       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM,                                                             \r\n"
               "       MAX(DECODE(STAT_YM, :p01, TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, :p02, SPED,    0)) SPED_A,               \r\n"
               "       MAX(DECODE(STAT_YM, :p03, OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, :p04, TRVL_HH, 0)) TRVLHH_A,             \r\n"
               "       MAX(DECODE(STAT_YM, :p05, TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, :p06, SPED,    0)) SPED_B,               \r\n"
               "       MAX(DECODE(STAT_YM, :p07, OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, :p08, TRVL_HH, 0)) TRVLHH_B              \r\n"
               "  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,                                           \r\n"
               "               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH                                          \r\n"
               "          FROM (SELECT T2.IFSC_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD \r\n"
               "                  FROM TB_IFSC T2                                                                                        \r\n"
               "                 WHERE T2.IFSC_ID = :p09                                                                                 \r\n"
               "               ) A,                                                                                                      \r\n"
               "               (SELECT STAT_YM, IFSC_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH                    \r\n"
               "                  FROM " + sPatternTable + "                                                                             \r\n"
               "                 WHERE IFSC_ID = :p10                                                                                    \r\n"
               "                   AND STAT_YM IN(:p11, :p12)                                                                            \r\n"
               "                   AND STAT_HM BETWEEN :p13 AND :p14                                                                     \r\n"
               "                   AND DAY_TYPE_CD = :p15                                                                                \r\n"
               "               ) B                                                                                                       \r\n"
               "         WHERE A.LINK_ID = B.LINK_ID                                                                                     \r\n"
               "       )                                                                                                                 \r\n"
               " GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM                                                                                  \r\n"
               " ORDER BY STAT_HM                                                                                                        \r\n";
	}
    else if (FLinkLevel == 2)
    {
        sQry = "SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,                                 \r\n"
               "       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM,                                                             \r\n"
               "       MAX(DECODE(STAT_YM, :p01, TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, :p02, SPED,    0)) SPED_A,               \r\n"
               "       MAX(DECODE(STAT_YM, :p03, OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, :p04, TRVL_HH, 0)) TRVLHH_A,             \r\n"
               "       MAX(DECODE(STAT_YM, :p05, TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, :p06, SPED,    0)) SPED_B,               \r\n"
               "       MAX(DECODE(STAT_YM, :p07, OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, :p08, TRVL_HH, 0)) TRVLHH_B              \r\n"
               "  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,                                           \r\n"
               "               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH                                          \r\n"
               "          FROM (SELECT T2.ROAD_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD \r\n"
               "                  FROM TB_ROAD T2                                                                                        \r\n"
               "                 WHERE T2.ROAD_ID = :p09                                                                                 \r\n"
               "               ) A,                                                                                                      \r\n"
               "               (SELECT STAT_YM, ROAD_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH                    \r\n"
               "                  FROM " + sPatternTable + "                                                                             \r\n"
               "                 WHERE ROAD_ID = :p10                                                                                    \r\n"
               "                   AND STAT_YM IN(:p11, :p12)                                                                            \r\n"
               "                   AND STAT_HM BETWEEN :p13 AND :p14                                                                     \r\n"
               "                   AND DAY_TYPE_CD = :p15                                                                                \r\n"
               "               ) B                                                                                                       \r\n"
               "         WHERE A.LINK_ID = B.LINK_ID                                                                                     \r\n"
               "       )                                                                                                                 \r\n"
               " GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM                                                                                  \r\n"
               " ORDER BY STAT_HM                                                                                                        \r\n";
    }
    else return;

    try
    {
        ITSDb_SQLText(pADO, sQry);
        ITSDb_SQLBind(pADO, "p01", FBaseMon);
        ITSDb_SQLBind(pADO, "p02", FBaseMon);
        ITSDb_SQLBind(pADO, "p03", FBaseMon);
        ITSDb_SQLBind(pADO, "p04", FBaseMon);

        ITSDb_SQLBind(pADO, "p05", FDiffMon);
        ITSDb_SQLBind(pADO, "p06", FDiffMon);
        ITSDb_SQLBind(pADO, "p07", FDiffMon);
        ITSDb_SQLBind(pADO, "p08", FDiffMon);

        ITSDb_SQLBind(pADO, "p09", FLinkId);
        ITSDb_SQLBind(pADO, "p10", FLinkId);

        ITSDb_SQLBind(pADO, "p11", FBaseMon);
        ITSDb_SQLBind(pADO, "p12", FDiffMon);

        ITSDb_SQLBind(pADO, "p13", FStTime);
        ITSDb_SQLBind(pADO, "p14", FEdTime);

        ITSDb_SQLBind(pADO, "p15", FPatternWeek);

        ITSDb_SQLOpen(pADO);

        DspHistory();
    }
    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 TIAN00501::DspHistory()
{
    TADOQuery *pADO = ADOQry;
    TcxGridChartView *TvChart = FRAMEChart1->TvChart;

    try
    {
    	TcxGridChartSeries *pChart1 = NULL;
    	TcxGridChartSeries *pChart2 = NULL;
    	TcxGridChartSeries *pChart3 = NULL;
    	TcxGridChartSeries *pChart4 = NULL;
    	TcxGridChartSeries *pChart5 = NULL;
    	TcxGridChartSeries *pChart6 = 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 = nDataCnt;
            m_pGDC->RecordCount = nDataCnt;

			pChart1 = TvChart->CreateSeries();
			pChart1->DataBinding->ValueType = "Integer";
			pChart1->DisplayText = cxLabel1->Caption;//"기준월 속도(km/h)";

			pChart2 = TvChart->CreateSeries();
			pChart2->DataBinding->ValueType = "Integer";
			pChart2->DisplayText = cxLabel2->Caption;//"비교월 속도(km/h)";

			pChart3 = TvChart->CreateSeries();
			pChart3->DataBinding->ValueType = "Integer";
			pChart3->DisplayText = cxLabel3->Caption;//"차이 속도(km/h)";

			pChart4 = TvChart->CreateSeries();
			pChart4->DataBinding->ValueType = "Integer";
			pChart4->DisplayText = cxLabel4->Caption;//"기준월 통행시간(초)";

			pChart5 = TvChart->CreateSeries();
			pChart5->DataBinding->ValueType = "Integer";
			pChart5->DisplayText = cxLabel5->Caption;//"비교월 통행시간(초)";

			pChart6 = TvChart->CreateSeries();
			pChart6->DataBinding->ValueType = "Integer";
			pChart6->DisplayText = cxLabel6->Caption;//"차이 통행시간(초)";

            int spd1, spd2, spd3;
            int trv1, trv2, trv3;

            for( ; !pADO->Eof; pADO->Next(), nRow++)
            {
                m_pGDC->Values[nRow][Column01->Index] = pADO->FieldByName("LINK_ID")->AsString;
                m_pGDC->Values[nRow][Column02->Index] = pADO->FieldByName("LINK_NAME")->AsString;
                m_pGDC->Values[nRow][Column03->Index] = pADO->FieldByName("LINK_LENG")->AsString;
                m_pGDC->Values[nRow][Column04->Index] = FWeekName;
                m_pGDC->Values[nRow][Column05->Index] = pADO->FieldByName("STAT_HM")->AsString;

                spd1 = pADO->FieldByName("SPED_A")->AsInteger;
                trv1 = pADO->FieldByName("TRVLHH_A")->AsInteger;
                m_pGDC->Values[nRow][Column06->Index] = pADO->FieldByName("TFVL_A")->AsInteger;
                m_pGDC->Values[nRow][Column07->Index] = spd1;
                m_pGDC->Values[nRow][Column08->Index] = pADO->FieldByName("OCPYRATE_A")->AsInteger;
                m_pGDC->Values[nRow][Column09->Index] = trv1;

                spd2 = pADO->FieldByName("SPED_B")->AsInteger;
                trv2 = pADO->FieldByName("TRVLHH_B")->AsInteger;
                m_pGDC->Values[nRow][Column10->Index] = pADO->FieldByName("TFVL_B")->AsInteger;
                m_pGDC->Values[nRow][Column11->Index] = spd2;
                m_pGDC->Values[nRow][Column12->Index] = pADO->FieldByName("OCPYRATE_B")->AsInteger;
                m_pGDC->Values[nRow][Column13->Index] = trv2;

                m_pGDC->Values[nRow][Column14->Index] = spd1 - spd2;
                m_pGDC->Values[nRow][Column15->Index] = trv1 - trv2;

				pChart1->Values[nRow] = m_pGDC->Values[nRow][Column07->Index];
				pChart2->Values[nRow] = m_pGDC->Values[nRow][Column11->Index];
				pChart3->Values[nRow] = m_pGDC->Values[nRow][Column14->Index];

				pChart4->Values[nRow] = m_pGDC->Values[nRow][Column09->Index];
				pChart5->Values[nRow] = m_pGDC->Values[nRow][Column13->Index];
				pChart6->Values[nRow] = m_pGDC->Values[nRow][Column15->Index];

				TvChart->Categories->Values[nRow] = m_pGDC->Values[nRow][Column05->Index];
            }
        }
        __finally
        {
            if (pADO)
            {
                pADO->Close();
            }

    		TvChart->EndUpdate();
            TvList->EndUpdate();
            //CxList->SetFocus();
            LblRecords->Caption = FormatFloat("##,##0", m_pGDC->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);
        }
    }
    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 TIAN00501::OnMessage(TMessage &Msg)
{
    switch (Msg.Msg)
    {
    case WM_PARAM_DATABASE:
        if (WP_DB_SELECT_OK == Msg.WParam)
        {
            //ShowMessage("Select Ok");
        }
        break;
    }
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::BtnExlSaveClick(TObject *Sender)
{
    TcxGrid          *pGrid = CxList;
    TcxGridTableView *pView = TvList;
    String            sTitle= Caption;//"패턴데이터분석";

    CMM_ExportToExcelFile(sTitle, pGrid, pView, this);
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::FormClose(TObject *Sender, TCloseAction &Action)
{
    POST_MSG(FParent, WM_SUBFORM_CLOSE, 0, 0);
    CommClose();
    //IAN00501 = NULL;
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::FormDestroy(TObject *Sender)
{
    CommClose();
}
//---------------------------------------------------------------------------

void __fastcall TIAN00501::Column14CustomDrawCell(TcxCustomGridTableView *Sender,
          TcxCanvas *ACanvas, TcxGridTableDataCellViewInfo *AViewInfo,
          bool &ADone)
{
    TColor tColor = ACanvas->Brush->Color;
    TColor tFont  = clBlack;

    try
    {
        if (!VarIsNull(AViewInfo->Value))
        {
            int sVal = AViewInfo->Value;
            if (sVal > 2)
            {
                tColor = clRed;
            }
            else
            if (sVal < -2)
            {
                tColor = clFuchsia;
            }
            else
            {
                tColor = clLime;
            }
        }

        ACanvas->Font->Color = tFont;
        ACanvas->SetBrushColor(tColor);
    }
    catch(...)
    {
    }
}
//---------------------------------------------------------------------------