=========================================================================================
<< QMSControl/QMSTagManagement.cpp >>
void CQMSTagManagement::LoadListDataP()
{
if(m_csTagType.GetLength() ==0 || m_csLine.GetLength() == 0 || m_csMachine.GetLength() ==0
|| m_csClassify.GetLength() == 0)
{
AfxMessageBox("구분,라인,설비,분류 모두 선택하셔야합니다.");
return;
}
//정성훈: 비모니터링 인자조회일 경우 m_bMonitoringParam=true
//어디서 꼬였는지 모르지만 컬러 표시가 안됨.
//컬러가 m_CheckMonitoring에 따라 분기하므로 여기서 넣어주어 임시 처리함.
if(m_CheckMonitoring.GetCheck()==1)
m_bMonitoringParam=true;
else
m_bMonitoringParam=false;
CWaitCursor wait;
//m_arrTagName.RemoveAll();
m_ListTag.DeleteAllItems();
string strEquip=GetCurComboText(IDC_COMBO_MACHINE);
string strClass=GetCurComboText(IDC_COMBO_CLASSIFY);
m_ButtonDelete.EnableWindow(FALSE);
m_ButtonModify.EnableWindow(FALSE);
CString csLine;
// csLine = ConvertLine(m_csLine,false);
CString qry;
m_EditSearch.GetWindowText(m_csSearch);
m_csSearch.Trim();
#ifdef QMS_PROCEDURE
#ifndef _ORG_SOURCE_
CString strGrade="";
CComboBox * pCombo=(CComboBox *)GetDlgItem(IDC_COMBO_GRADE);
if(pCombo!=NULL)
{
int iSel=pCombo->GetCurSel();
if(iSel>0)
pCombo->GetLBText(iSel, strGrade);
}
qry.Format("{CALL dbo.PROC_SELECT_TAG_INFO ('%s' ,'%s', '%s','%s','%s','%s', '%s', '0')}",
m_csTagType,m_csLine, strGrade, m_csMachine, m_csClassify, m_csSearch,
/*정성훈: 컨트롤에서 집접 읽어옮*/
( m_CheckMonitoring.GetCheck() == 1 ? "NO" : "YES" )
);
#else
qry.Format("{CALL dbo.PROC_SELECT_TAG_INFO ('%s' ,'%s','%s','%s','%s', '%s')}",
m_csTagType,m_csLine, m_csMachine, m_csClassify, m_csSearch,
/*정성훈: 컨트롤에서 집접 읽어옮*/
( m_CheckMonitoring.GetCheck() == 1 ? "NO" : "YES" )
);
#endif
TStringRecBaseEx rs;
CQMSDBMgr dbmgr;
if(dbmgr.ExecuteSql((LPSTR)(LPCTSTR)qry, (DWORD)&rs, TRUE)!=0)
return;
#else
qry.Format("EXEC dbo.PROC_SELECT_TAG_INFO '%s' ,'%s','%s','%s','%s', '%s'",
m_csTagType,m_csLine, m_csMachine, m_csClassify, m_csSearch,
/*정성훈: 컨트롤에서 집접 읽어옮*/
( m_CheckMonitoring.GetCheck() == 1 ? "NO" : "YES" )
);
OLEDBConn conn;
HRESULT hr = conn.connect( GlobalSetting::GetDBDriver()
, GlobalSetting::GetDBServer()
, GlobalSetting::GetDBUser()
, GlobalSetting::GetDBPass() );
if( hr != S_OK )
MessageBox("DB Connect error",QMS_STR_TITLE, MB_ICONINFORMATION);
TStringRecBase rs;
if( !conn.executeQry(rs, qry) )
MessageBox("DB Query error",QMS_STR_TITLE, MB_ICONINFORMATION);
#endif
CString csCurName,csTempLine,csTempMachine,csTempClassify;
int n=0;
DBSTATUS dbStatus;
while( rs.MoveNext() == S_OK )
{
//m_ListTag.InsertItem(n, rs.GetString(1));
//정성훈 수정: 20120319
for(int i=0;i<=15;i++)
{
CString strText("");
if(!rs.GetStatus(i+1, &dbStatus) || dbStatus != DBSTATUS_S_ISNULL )
strText = rs.GetString(i+1);
else
strText = "";
if(i == 0)
m_ListTag.InsertItem(n, strText);
else
m_ListTag.SetItemText(n, i, strText);
}
n++;
}
rs.Close();
if(n ==0)
{
MessageBox("조회하신 데이타가 없습니다.","태그관리",MB_OK);
return;
}
}
BOOL CQMSTagManagement::OnInitDialog()
{
IMSBaseDialog::OnInitDialog();
CWaitCursor wait;
ImageBank::SetButtonImage(m_ButtonSearch,13);
m_ButtonSearch.SetWindowText (_T(" 조 회 "));
m_ButtonSearch.SizeToContent ();
m_ButtonSearch.Invalidate ();
ImageBank::SetButtonImage(m_ButtonReg,16);
m_ButtonReg.SetWindowText (_T(" 등 록 "));
m_ButtonReg.SizeToContent ();
m_ButtonReg.Invalidate ();
ImageBank::SetButtonImage(m_ButtonDelete,8);
m_ButtonDelete.SetWindowText (_T(" 삭 제 "));
m_ButtonDelete.SizeToContent ();
m_ButtonDelete.Invalidate ();
ImageBank::SetButtonImage(m_ButtonModify,17);
m_ButtonModify.SetWindowText (_T(" 수 정 "));
m_ButtonModify.SizeToContent ();
m_ButtonModify.Invalidate ();
// 품질인자의 태그공정모니터링에서의 순서 변경
m_btnTagOrder.SubclassDlgItem(IDC_BUTTON_QTAGORDER, this);
ImageBank::SetButtonImage(m_btnTagOrder,17);
m_btnTagOrder.SetWindowText (_T("품질인자순서"));
m_btnTagOrder.SizeToContent ();
m_btnTagOrder.Invalidate ();
// 공정인자의
m_btnTagProcOrder.SubclassDlgItem(IDC_BUTTON_PROCORDER, this);
ImageBank::SetButtonImage(m_btnTagProcOrder,17);
m_btnTagProcOrder.SetWindowText (_T("공정인자순서"));
m_btnTagProcOrder.SizeToContent ();
m_btnTagProcOrder.Invalidate ();
#ifndef _ORG_SOURCE_
m_btnTagProcOrder.ShowWindow(SW_HIDE);
#endif
m_ListTag.SetExtendedStyle( m_ListTag.GetExtendedStyle() | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES );
m_ListTag.InsertColumn(0, COLUMN_NAME_TAGTYPE, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(1, COLUMN_NAME_LINE, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(2, COLUMN_NAME_MACHINE, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(3, COLUMN_NAME_CLASSIFY, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(4, COLUMN_NAME_TAGID, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(5, COLUMN_NAME_DISP_NAME, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(6, COLUMN_NAME_DESC, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(7, COLUMN_NAME_QUALITY_PARAM, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(8, COLUMN_NAME_GRADE, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(9, COLUMN_NAME_TARGET, LVCFMT_CENTER, 10);
m_ListTag.InsertColumn(10, COLUMN_NAME_LCL, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(11, COLUMN_NAME_UCL, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(12, COLUMN_NAME_LSL, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(13, COLUMN_NAME_USL, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(14, COLUMN_NAME_USEABLE, LVCFMT_LEFT, 10);
m_ListTag.InsertColumn(15, COLUMN_NAME_REVISION, LVCFMT_LEFT, 10);
m_ComboTagType.SetCurSel(1);
m_ComboTagType.GetLBText( 1, m_csTagType);
m_ButtonModify.EnableWindow(FALSE);
m_ButtonDelete.EnableWindow(FALSE);
InitLineCombo();
InitMachineClassifyCombo("P");
ResizeControl();
// 비모니터링 인자 표시로 caption을 표시
m_CheckMonitoring.SetWindowText("비모니터링 인자 검색");
InitGrade();
// GetDlgItem(IDC_BUTTON_QTAGORDER)->ShowWindow(SW_HIDE);
return TRUE; // return TRUE unless you set the focus to a control
// 예외: OCX 속성 페이지는 FALSE를 반환해야 합니다.
}
=========================================================================================
<< MonView/MainFrm.cpp >>
int MainFrame::OnCreate(LPCREATESTRUCT lpCreateStruct)
{
if (CFrameWnd::OnCreate(lpCreateStruct) == -1)
return -1;
// SendMessage(WM_DESTROY);
CBCGPVisualManager::SetDefaultManager(RUNTIME_CLASS(CBCGPVisualManagerXP));
//////////////////////////////////////////////////////////////////////////
///현재 선택된 공장 기억
string strPath=GetModulePath();
strPath.append("IMSConfig.ini");
char chText[MAX_PATH];
memset(chText, 0x00, sizeof(chText));
::GetPrivateProfileString("SKI PROC", "PROC", NULL, chText, sizeof(chText), strPath.data());
string strProc=chText;
if(strProc.compare(QMS_STR_LIBS)==0)
m_qms = E_LIBS;
else if(strProc.compare(QMS_STR_FCCL)==0)
m_qms = E_FCCL;
else if(strProc.compare(QMS_STR_TAC)==0)
m_qms = E_TAC;
else
m_qms = E_LIBS;
//////////////////////////////////////////////////////////////////////////
// 공장번호를 대신 넣음
GlobalSetting::SetBlastNum( (int_t) m_qms );
#ifdef QMS_PROJECT
OraConn();
//#ifndef _DEBUG
if( !QMSLogin() )
{
return 0;
}
//#endif
if( !QMSFrameCreate() )
return -1;
//m_bLogin=TRUE;
QMSMenu();
#endif
// 메인 메뉴 생성
_mainMenu.LoadMenu( IDR_MENU_QMS );
// TODO: 권한에 따른 메뉴 정리
CString title;
title.Format("%s", "QMS");
this->SetWindowText(title);
CSplashWnd::EnableSplashScreen(TRUE);
CSplashWnd::ShowSplashScreen(this);
CSplashWnd::ChangeMessage("보고 유닛을 생성하고 있습니다...");
ModifyStyle(WS_CAPTION | FWS_ADDTOTITLE, 0);
//ModifyStyle(FWS_ADDTOTITLE, 0);
ModifyStyleEx(WS_EX_CLIENTEDGE, 0);
CBCGPVisualManager::SetDefaultManager (RUNTIME_CLASS (CBCGPMSMVisualManager));
CBCGPDockManager::SetDockMode(BCGP_DT_SMART);
//---------------------------------
// Set toolbar and menu image size:
//---------------------------------
CBCGPToolbarButton::m_bWrapText = FALSE;
CBCGPToolBar::SetMenuSizes (CSize (22, 22), CSize (16, 16));
CBCGPToolbarComboBoxButton::SetFlatMode();
CBCGPToolbarComboBoxButton::SetCenterVert();
if( !m_wndCaptionBar.Create(this, IDR_MAINFRAME) )
{
TRACE0("Failed to create captionbar\n");
return -1;
}
if (globalData.fontRegular.GetSafeHandle () != NULL)
{
LOGFONT lf;
ZeroMemory (&lf, sizeof (LOGFONT));
globalData.fontRegular.GetLogFont (&lf);
m_wndCaptionBar.SetCaptionFont (lf);
}
EnableDocking(CBRS_ALIGN_ANY);
CClientDC dc (this);
m_bIsHighColor = dc.GetDeviceCaps (BITSPIXEL) > 16;
UINT uiToolbarHotID = m_bIsHighColor ? IDB_HOTTOOLBAR : 0;
UINT uiToolbarColdID = m_bIsHighColor ? IDB_COLDTOOLBAR : 0;
UINT uiToolbarDisID = m_bIsHighColor ? IDB_DISABLEDTOOLBAR : 0;
UINT uiToolbarPresID = m_bIsHighColor ? IDB_PRESSEDTOOLBAR : 0;
UINT uiMenuID = m_bIsHighColor ? IDB_MENU_IMAGES : IDB_MENU_IMAGES_16;
CRect rectDummy(0, 24, 10, 64);
if( !m_wndToolBar.Create("MonToolBar", this, rectDummy, FALSE, 7777
, WS_CHILD | WS_VISIBLE | CBRS_TOP
, CBRS_BCGP_REGULAR_TABS, 0) )
{
TRACE0("Failed to create toolbar\n");
return -1; // fail to create
}
QMSToolbar();
DockControlBar(&m_wndToolBar);
m_wndToolBar.GetClientRect(&rectDummy);
#ifndef QMS_PROJECT
IMSMenuInit();
#endif
// 데이터 기본값 가져오기
CSplashWnd::ChangeMessage("기본 정보를 생성하고 있습니다...");
//// 필요없는 데이터들 제거합시다
RemoveTemporaryFile();
MonServerConn(TRUE);
InitDataPooler(strProc);
CSplashWnd::HideTimerOn(2000);
CSplashWnd::ChangeMessage("QMS를 시작합니다...");
OutputFrame::SetMainAppControl(&theApp, this);
// 본사에서 테스트하기 위함.
GlobalSetting::SetMonitorMode(true);
NetClientHandler::Disconnect();
#ifdef QMS_PROJECT
#ifdef _ORG_SOURCE_
QmsDataInitialize();
#endif
#endif
ShowWindow(SW_SHOW);
return 0;
}
=========================================================================================
<< FCCLControl/FCCLGridCtrl.h >>
#pragma once
#ifdef _QMS_VS2003_
#include "../BCGCBPro/BCGPGridCtrl.h"
#else
#include "../../ECMSDK/include/BCGCBPro/BCGPGridCtrl.h"
#endif
#define WM_GRID_LSELROW (WM_USER+7090)
#define WM_GRID_RSELROW (WM_USER+7091)
#define WM_GRID_LDBSELROW (WM_USER+7092)
// CFCCLGridCtrl
class AFX_EXT_CLASS CFCCLGridCtrl : public CBCGPGridCtrl
{
DECLARE_DYNAMIC(CFCCLGridCtrl)
bool _isSelectAllRow;
bool _useBCGSortFunc;
BOOL m_bUseAnotherContextMenu;
BOOL m_bShow;
int m_iCurRow;
int m_iCurCol;
BOOL _LButton;
CWnd* m_pParent;
public:
CFCCLGridCtrl();
/*생성자
bUseAnotherContextMenu = TRUE : 자기콘텍스트메뉴사용, FALSE : FCCLGridCtrl콘텍스트메뉴사용
*/
CFCCLGridCtrl(CWnd* pParent, BOOL bUseAnotherContextMenu = TRUE);
virtual ~CFCCLGridCtrl();
afx_msg void OnMenuExportToExcel();
afx_msg void OnContextMenu(CWnd* pWnd, CPoint point);
afx_msg int OnCreate(LPCREATESTRUCT lpCreateStruct);
afx_msg void OnLButtonDown(UINT nFlags, CPoint point);
afx_msg void OnRButtonDown(UINT nFlags, CPoint point);
afx_msg void OnLButtonDblClk(UINT nFlags, CPoint point);
void SetSelectAllRow(bool isSelectAllRow) { _isSelectAllRow = isSelectAllRow; }
void SelectAllRow(BOOL LButton, BOOL bDouble = FALSE);
int GetCurRow();
int GetCurCol();
BOOL Export2Excel(); ///엑셀파일로 내보내기
#ifdef _NEW_SOURCE_
BOOL OnMenuCopyToClipboard(); /// 그리드의 내용을 클립보드에 .csv 파일 형식으로 복사하는 함수
BOOL OnMenuPasteFromClipboard(); /// 클립보드의 내용을 그리드에 붙여넣은 후 저장소에 저장하는 함수
#endif
protected:
DECLARE_MESSAGE_MAP()
public:
static const UINT _EXCEL_EXPORT_ = WM_USER + 6790;
#ifdef _NEW_SOURCE_
static const UINT _COPY_TOCLIPBOARD_ = WM_USER + 6791;
static const UINT _PASTE_FROMCLIPBOARD_ = WM_USER + 6792;
#endif
};
<< FCCLControl/FCCLGridCtrl.cpp >>
#ifdef _NEW_SOURCE_
ON_COMMAND(_COPY_TOCLIPBOARD_, OnMenuCopyToClipboard)
ON_COMMAND(_PASTE_FROMCLIPBOARD_, OnMenuPasteFromClipboard)
#endif
void CFCCLGridCtrl::OnContextMenu(CWnd* pWnd, CPoint point)
{
if (m_pParent != NULL && m_bUseAnotherContextMenu)
{
CBCGPGridCtrl::OnContextMenu(pWnd, point);
return;
}
CMenu popMenu;
popMenu.CreatePopupMenu();
popMenu.AppendMenu(MF_STRING, _EXCEL_EXPORT_, FCCL_STR_EXCELEXPORT);
#ifdef _NEW_SOURCE_
popMenu.AppendMenu(MF_STRING, _COPY_TOCLIPBOARD_, "복사");
popMenu.AppendMenu(MF_STRING, _PASTE_FROMCLIPBOARD_, "붙여넣기");
#endif
popMenu.TrackPopupMenu(TPM_LEFTALIGN, point.x, point.y, this);
}
#ifdef _NEW_SOURCE_
BOOL CFCCLGridCtrl::OnMenuCopyToClipboard()
{
CWaitCursor wait;
return Copy(CBCGPGridCtrl::ExportTextFormat::Format_CSV);
}
BOOL CFCCLGridCtrl::OnMenuPasteFromClipboard()
{
CWaitCursor wait;
if( Paste() != TRUE )
return FALSE;
/////////////////////////////////////////////////////////////////
// 붙여넣은 데이터를 DB 에 저장하는 코드를 추가해야 함
// ...
/////////////////////////////////////////////////////////////////
}
#endif //_NEW_SOURCE_
=========================================================================================
<< ECMINER_IMS/PROC_SELECT_TAG_INFO 프로시져 >>
USE [ECMINER_IMS]
GO
/****** Object: StoredProcedure [dbo].[PROC_SELECT_TAG_INFO] Script Date: 04/25/2013 17:27:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PROC_SELECT_TAG_INFO]
@구분 VARCHAR(10), --콤보박스에 선택된 TEXT 그대로 입력, "수입" | "공정" | "품질"
@LINE VARCHAR(10), --콤보박스에 선택된 TEXT 그대로 입력, "01라인" ~~~
@GRADE VARCHAR(30), --콤보박스에 선택된 TEXT 그대로 입력, "전체" | "NONE" ~~~~
@설비 VARCHAR(64), --콤보박스에 선택된 TEXT 그대로 입력, "STRETCHER" ~~~
@분류 VARCHAR(64), --콤보박스에 선택된 TEXT 그대로 입력, "전체" | "FAN CURRENT" ~~~
@태그명 VARCHAR(128), --텍스트에 쓴 태그명 LIKE 검색
@사용여부 VARCHAR(16), --모니터링여부 YES, NO
@DEBUG VARCHAR(5) --
AS
BEGIN
SET NOCOUNT ON
DECLARE @V_SQL VARCHAR(MAX)=''
IF LEN(@태그명) > 0 -- 다른 조건 모두 무시하고 LIKE 검색 수행
BEGIN
SET @V_SQL='SELECT *
FROM (
SELECT CASE WHEN TAGTYPE=''I'' THEN ''수입''
WHEN TAGTYPE=''P'' THEN ''공정''
WHEN TAGTYPE=''Q'' THEN ''품질'' END AS [구분],
B.LINENAME AS [Line],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),''\'',3) AS [설비],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),''\'',4) AS [분류],
CASE WHEN B.TAGTYPE=''I'' THEN A.TAGNAME
WHEN B.TAGTYPE=''P'' THEN A.TAGNAME
WHEN TAGTYPE=''Q'' THEN B.TAGID END AS [TagID],
A.DisplayName as [Disp Name],
A.[desc] as [Desc],
dbo.FN_CONCATBYCOMMA(B.TAGNAME) as [연관 품질인자],
C.GRADE AS [Grade],
CONVERT(VARCHAR, C.Target) AS [Target],
CONVERT(VARCHAR, c.LCL) LCL,
CONVERT(VARCHAR, c.UCL) UCL,
CONVERT(VARCHAR, c.LSL) LSL,
CONVERT(VARCHAR, c.USL) USL,
b.USEABLE AS [Use]
, b.IDX
, C.REVISION REVISION
FROM TBE_COL_INFO A INNER JOIN TBQ_TAGFACETREE B ON A.TAGNAME = B.TAGNAME
inner join TBQ_CONTROLLINE C ON A.TAGNAME=C.TAGNAME
WHERE EXISTS (SELECT 1 FROM TBE_HLD_DTA_SET Z WHERE DATA_SET_CLSF_CD=''01'' AND A.tbl_id=Z.TBL_ID )
AND USEABLE='''+@사용여부+'''
) IT
WHERE 1=1
AND [TagID] LIKE ''%'+@태그명+'%''
AND [구분]='''+@구분+''' '
END
ELSE -- 태그명으로 조회하지 않을 때, 태그명 TEXTBOX가 빈 칸 일 때
BEGIN
PRINT 'TEAG'
SET @V_SQL='SELECT *
FROM (
SELECT CASE WHEN TAGTYPE=''I'' THEN ''수입''
WHEN TAGTYPE=''P'' THEN ''공정''
WHEN TAGTYPE=''Q'' THEN ''품질'' END AS [구분],
B.LINENAME AS [Line],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),''\'',3) AS [설비],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),''\'',4) AS [분류],
CASE WHEN B.TAGTYPE=''I'' THEN A.TAGNAME
WHEN B.TAGTYPE=''P'' THEN A.TAGNAME
WHEN TAGTYPE=''Q'' THEN B.TAGID END AS [TagID],
A.DisplayName as [Disp Name],
A.[desc] as [Desc],
dbo.FN_CONCATBYCOMMA(B.TAGNAME) as [연관 품질인자],
C.GRADE AS [Grade],
CONVERT(VARCHAR, C.Target) AS [Target],
CONVERT(VARCHAR, c.LCL) LCL,
CONVERT(VARCHAR, c.UCL) UCL,
CONVERT(VARCHAR, c.LSL) LSL,
CONVERT(VARCHAR, c.USL) USL,
b.USEABLE AS [Use]
, b.IDX
, C.REVISION REVISION
FROM TBE_COL_INFO A INNER JOIN TBQ_TAGFACETREE B ON A.TAGNAME = B.TAGNAME
inner join TBQ_CONTROLLINE C ON A.TAGNAME=C.TAGNAME
WHERE EXISTS (SELECT 1 FROM TBE_HLD_DTA_SET Z WHERE DATA_SET_CLSF_CD=''01'' AND A.tbl_id=Z.TBL_ID )
AND USEABLE='''+@사용여부+'''
UNION ALL
SELECT CASE WHEN TAGTYPE=''I'' THEN ''수입''
WHEN TAGTYPE=''P'' THEN ''공정''
WHEN TAGTYPE=''Q'' THEN ''품질'' END AS [구분],
B.LINENAME AS [Line],
NULL AS [설비],
NULL AS [분류],
B.TAGID AS [TagID],
B.TAGNAME as [Disp Name],
NULL as [Desc],
NULL as [연관 품질인자],
NULL AS [Grade],
NULL AS [Target],
NULL AS LCL,
NULL AS UCL,
NULL AS LSL,
NULL AS USL,
b.USEABLE AS [Use]
, b.IDX
, NULL REVISION
FROM TBQ_TAGFACETREE B
WHERE USEABLE='''+@사용여부+'''
) IT
WHERE 1=1
AND [구분]='''+@구분+'''
AND [LINE]='''+@LINE+'''
AND [설비]='''+@설비+''''
IF @분류<>'전체' AND @분류<>'' AND @분류 IS NOT NULL -- 분류가 전체일 때는 WHERE절에 분류에 대한 조건을 넣지 않는다.
BEGIN
SET @V_SQL=@V_SQL+' AND [분류]='''+@분류+''''
END
-- IF @GRADE <> '전체' OR @GRADE<>''
IF @GRADE != '' AND @GRADE IS NOT NULL
BEGIN
SET @V_SQL=@V_SQL+' AND Grade='''+@GRADE+''' '
END
END
IF @DEBUG = '0'
EXECUTE (@V_SQL)
ELSE
PRINT @V_SQL
END
<< ECMINER_IMS/PROC_SELECT_TAG_INFO_ORG 프로시져 >>
USE [ECMINER_IMS]
GO
/****** Object: StoredProcedure [dbo].[PROC_SELECT_TAG_INFO_ORG] Script Date: 04/25/2013 17:24:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PROC_SELECT_TAG_INFO_ORG]
@구분 VARCHAR(10), --콤보박스에 선택된 TEXT 그대로 입력, "수입" | "공정" | "품질"
@LINE VARCHAR(10), --콤보박스에 선택된 TEXT 그대로 입력, "01라인" ~~~
@설비 VARCHAR(64), --콤보박스에 선택된 TEXT 그대로 입력, "STRETCHER" ~~~
@분류 VARCHAR(64), --콤보박스에 선택된 TEXT 그대로 입력, "전체" | "FAN CURRENT" ~~~
@태그명 VARCHAR(128), --텍스트에 쓴 태그명 LIKE 검색
@사용여부 VARCHAR(16) --모니터링여부 YES, NO
AS
BEGIN
IF LEN(@태그명) > 0 -- 다른 조건 모두 무시하고 LIKE 검색 수행
BEGIN
SELECT *
FROM (
SELECT CASE WHEN TAGTYPE='I' THEN '수입'
WHEN TAGTYPE='P' THEN '공정'
WHEN TAGTYPE='Q' THEN '품질' END AS [구분],
B.LINENAME AS [Line],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',3) AS [설비],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',4) AS [분류],
CASE WHEN B.TAGTYPE='I' THEN A.TAGNAME
WHEN B.TAGTYPE='P' THEN A.TAGNAME
WHEN TAGTYPE='Q' THEN B.TAGID END AS [TagID],
A.DisplayName as [Disp Name],
A.[desc] as [Desc],
dbo.FN_CONCATBYCOMMA(B.TAGNAME) as [연관 품질인자],
C.GRADE AS [Grade],
CONVERT(VARCHAR, C.Target) AS [Target],
CONVERT(VARCHAR, c.LCL) LCL,
CONVERT(VARCHAR, c.UCL) UCL,
CONVERT(VARCHAR, c.LSL) LSL,
CONVERT(VARCHAR, c.USL) USL,
b.USEABLE AS [Use]
, b.IDX
FROM TBE_COL_INFO A INNER JOIN TBQ_TAGFACETREE B ON A.TAGNAME = B.TAGNAME
inner join TBQ_CONTROLLINE C ON A.TAGNAME=C.TAGNAME
WHERE EXISTS (SELECT 1 FROM TBE_HLD_DTA_SET Z WHERE DATA_SET_CLSF_CD='01' AND A.tbl_id=Z.TBL_ID )
AND USEABLE=@사용여부
) IT
WHERE 1=1
AND [TagID] LIKE '%'+@태그명+'%'
AND [구분]=@구분
END
ELSE -- 태그명으로 조회하지 않을 때, 태그명 TEXTBOX가 빈 칸 일 때
BEGIN
IF @분류 = '전체' -- 분류가 전체일 때는 WHERE절에 분류에 대한 조건을 넣지 않는다.
BEGIN
SELECT *
FROM (
SELECT CASE WHEN TAGTYPE='I' THEN '수입'
WHEN TAGTYPE='P' THEN '공정'
WHEN TAGTYPE='Q' THEN '품질' END AS [구분],
B.LINENAME AS [Line],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',3) AS [설비],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',4) AS [분류],
CASE WHEN B.TAGTYPE='I' THEN A.TAGNAME
WHEN B.TAGTYPE='P' THEN A.TAGNAME
WHEN TAGTYPE='Q' THEN B.TAGID END AS [TagID],
A.DisplayName as [Disp Name],
A.[desc] as [Desc],
dbo.FN_CONCATBYCOMMA(B.TAGNAME) as [연관 품질인자],
C.GRADE AS [Grade],
CONVERT(VARCHAR, C.Target) AS [Target],
CONVERT(VARCHAR, c.LCL) LCL,
CONVERT(VARCHAR, c.UCL) UCL,
CONVERT(VARCHAR, c.LSL) LSL,
CONVERT(VARCHAR, c.USL) USL,
b.USEABLE AS [Use]
, b.IDX
FROM TBE_COL_INFO A INNER JOIN TBQ_TAGFACETREE B ON A.TAGNAME = B.TAGNAME
inner join TBQ_CONTROLLINE C ON A.TAGNAME=C.TAGNAME
WHERE EXISTS (SELECT 1 FROM TBE_HLD_DTA_SET Z WHERE DATA_SET_CLSF_CD='01' AND A.tbl_id=Z.TBL_ID )
AND USEABLE=@사용여부
UNION ALL
SELECT CASE WHEN TAGTYPE='I' THEN '수입'
WHEN TAGTYPE='P' THEN '공정'
WHEN TAGTYPE='Q' THEN '품질' END AS [구분],
B.LINENAME AS [Line],
NULL AS [설비],
NULL AS [분류],
B.TAGID AS [TagID],
B.TAGNAME as [Disp Name],
NULL as [Desc],
NULL as [연관 품질인자],
NULL AS [Grade],
NULL AS [Target],
NULL AS LCL,
NULL AS UCL,
NULL AS LSL,
NULL AS USL,
b.USEABLE AS [Use]
, b.IDX
FROM TBQ_TAGFACETREE B
WHERE USEABLE=@사용여부
) IT
WHERE 1=1
AND [구분]=@구분
AND [LINE]=@LINE
AND [설비]=@설비
END
ELSE --분류가 전체가 아닐 경우
BEGIN
SELECT *
FROM (
SELECT CASE WHEN TAGTYPE='I' THEN '수입'
WHEN TAGTYPE='P' THEN '공정'
WHEN TAGTYPE='Q' THEN '품질' END AS [구분],
B.LINENAME AS [Line],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',3) AS [설비],
dbo.FUNC_SPLIT2(RTRIM(B.PATH),'\',4) AS [분류],
CASE WHEN B.TAGTYPE='I' THEN A.TAGNAME
WHEN B.TAGTYPE='P' THEN A.TAGNAME
WHEN TAGTYPE='Q' THEN B.TAGID END AS [TagID],
A.DisplayName as [Disp Name],
A.[desc] as [Desc],
dbo.FN_CONCATBYCOMMA(B.TAGNAME) as [연관 품질인자],
C.GRADE AS [Grade],
CONVERT(VARCHAR, C.Target) AS [Target],
CONVERT(VARCHAR, c.LCL) LCL,
CONVERT(VARCHAR, c.UCL) UCL,
CONVERT(VARCHAR, c.LSL) LSL,
CONVERT(VARCHAR, c.USL) USL,
b.USEABLE AS [Use]
, b.IDX
FROM TBE_COL_INFO A INNER JOIN TBQ_TAGFACETREE B ON A.TAGNAME = B.TAGNAME
inner join TBQ_CONTROLLINE C ON A.TAGNAME=C.TAGNAME
WHERE EXISTS (SELECT 1 FROM TBE_HLD_DTA_SET Z WHERE DATA_SET_CLSF_CD='01' AND A.tbl_id=Z.TBL_ID )
AND USEABLE=@사용여부
UNION ALL
SELECT CASE WHEN TAGTYPE='I' THEN '수입'
WHEN TAGTYPE='P' THEN '공정'
WHEN TAGTYPE='Q' THEN '품질' END AS [구분],
B.LINENAME AS [Line],
NULL AS [설비],
NULL AS [분류],
B.TAGID AS [TagID],
B.TAGNAME as [Disp Name],
NULL as [Desc],
NULL as [연관 품질인자],
NULL AS [Grade],
NULL AS [Target],
NULL AS LCL,
NULL AS UCL,
NULL AS LSL,
NULL AS USL,
b.USEABLE AS [Use]
, b.IDX
FROM TBQ_TAGFACETREE B
WHERE USEABLE=@사용여부
) IT
WHERE 1=1
AND [구분]=@구분
AND [LINE]=@LINE
AND [설비]=@설비
AND [분류]=@분류
ORDER BY [구분], [LINE], IDX ASC
END
END
END