/// <summary>
/// 해당 세션ID 정보가 있는지 확인
/// </summary>
public int CheckConnectLog(string SESSION_ID, string LOGIN_ID)
{
DataSet DS = new DataSet();
string query = @"
SELECT COUNT(*)
FROM COM_CONNECT_LOG
WHERE SESSION_ID = @SESSION_ID
AND LOGIN_ID = @LOGIN_ID";
int cnt;
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@SESSION_ID", SqlDbType.NVarChar);
paramArray[0].Value = SESSION_ID;
paramArray[1] = CreateDataParameter("@LOGIN_ID", SqlDbType.NVarChar);
paramArray[1].Value = LOGIN_ID;
DS = DbAgentObj.Fill(query, paramArray);
cnt = Convert.ToInt32(DS.Tables[0].Rows[0][0].ToString());
return(cnt);
}
/// <summary>
/// GetKPIStatus
/// : KPI_REF_ID와 월정보를 가지고 KPI실적테이블에서 각 STATUS를 추출한다.
/// </summary>
/// <param name="asKpiRefID"></param>
/// <param name="asMonthInfo"></param>
/// <returns></returns>
public DataSet GetKPIStatus(string asKpiRefID, string asMonthInfo)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT ISNULL(CHECKSTATUS, 0) CHECKSTATUS, \n");
sbQuery.Append(" ISNULL(CONFIRMSTATUS, 0) CONFIRMSTATUS, \n");
sbQuery.Append(" V_CNT \n");
sbQuery.Append(" FROM ( \n");
sbQuery.Append(" SELECT MAX(CONVERT (INT, CHECKSTATUS)) CHECKSTATUS, \n");
sbQuery.Append(" MAX(CONVERT (INT, CONFIRMSTATUS)) CONFIRMSTATUS, \n");
sbQuery.Append(" COUNT(*) V_CNT \n");
sbQuery.Append(" FROM KPI_RESULT \n");
sbQuery.Append(" WHERE KPI_REF_ID= CONVERT(INT, @KPI_REF_ID) \n");
sbQuery.Append(" AND TMCODE = CONVERT(INT, @TMCODE) \n");
sbQuery.Append(" ) A \n");
paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@KPI_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@TMCODE", SqlDbType.VarChar);
paramArray[0].Value = asKpiRefID;
paramArray[1].Value = asMonthInfo;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
/// <summary>
/// GetSearchCode
/// : 평가등급 수정시 검색
/// </summary>
/// <param name="asCodeID"></param>
/// <returns></returns>
public DataSet GetSearchCode(string asCodeID)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT KPI_THRESHOLD_CODE_ID V_CODE_ID , \n");
sbQuery.Append(" KPI_THRESHOLD_TYPE V_TYPE , \n");
sbQuery.Append(" KPI_THRESHOLD_STEPNAME V_STEPNAME , \n");
sbQuery.Append(" BASE_MIN_VALUE V_MIN_VALUE , \n");
sbQuery.Append(" MARK_COLOR V_COLOR , \n");
sbQuery.Append(" MARK_IMAGE_PATH V_IMG_PATH , \n");
sbQuery.Append(" POINT V_POINT , \n");
sbQuery.Append(" SEQUENCE \n");
sbQuery.Append(" FROM COM_KPI_THRESHOLD_CODE \n");
sbQuery.Append(" WHERE 1=1 \n");
sbQuery.Append(" AND KPI_THRESHOLD_CODE_ID = CONVERT(int, @KPI_THRESHOLD_CODE_ID) \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" SEQUENCE \n");
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@KPI_THRESHOLD_CODE_ID", SqlDbType.VarChar);
paramArray[0].Value = asCodeID;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
public DataSet GetTopMenuInfo()
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT \n");
sbQuery.Append(" MENU_REF_ID , \n");
sbQuery.Append(" UP_MENU_ID , \n");
sbQuery.Append(" MENU_NAME , \n");
sbQuery.Append(" MENU_DIR , \n");
sbQuery.Append(" MENU_PAGE_NAME , \n");
sbQuery.Append(" MENU_PARAM , \n");
sbQuery.Append(" MENU_FULL_PATH , \n");
sbQuery.Append(" MENU_DESC , \n");
sbQuery.Append(" MENU_PRIORITY , \n");
sbQuery.Append(" MENU_TYPE , \n");
sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n");
sbQuery.Append(" MENU_PREV_ICON_PATH , \n");
sbQuery.Append(" MENU_CREATE_DATE \n");
sbQuery.Append(" FROM COM_MENU_INFO \n");
sbQuery.Append(" WHERE MENU_TYPE = 'T' \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" MENU_PRIORITY \n");
return(DbAgentObj.Fill(sbQuery.ToString()));
}
public DataSet GetMenuInfo(int aiUpRefID)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT \n");
sbQuery.Append(" MENU_REF_ID , \n");
sbQuery.Append(" UP_MENU_ID , \n");
sbQuery.Append(" MENU_NAME , \n");
sbQuery.Append(" MENU_DIR , \n");
sbQuery.Append(" MENU_PAGE_NAME , \n");
sbQuery.Append(" MENU_PARAM , \n");
sbQuery.Append(" MENU_FULL_PATH , \n");
sbQuery.Append(" MENU_DESC , \n");
sbQuery.Append(" MENU_PRIORITY , \n");
sbQuery.Append(" MENU_TYPE , \n");
sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n");
sbQuery.Append(" MENU_PREV_ICON_PATH , \n");
sbQuery.Append(" MENU_CREATE_DATE \n");
sbQuery.Append(" FROM COM_MENU_INFO \n");
sbQuery.Append(" WHERE 1=1 \n");
sbQuery.Append(" AND UP_MENU_ID = @UP_MENU_ID \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" MENU_PRIORITY \n");
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@UP_MENU_ID", SqlDbType.Int);
paramArray[0].Value = aiUpRefID;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
public DataTable SelectInPower(string cr_year)
{
string query = @"
SELECT CR_YEAR
,CR_MONTH
,CR_MONTH || '월' AS CR_MONTH_NAME
,TARGET_FULL_RATE
,RESULT_FULL_RATE
,RESULT_DONG_RATE
,RESULT_BDONG_RATE
,TARGET_FULL_RATE / 100 AS TARGET_FULL_RATE_001
,RESULT_FULL_RATE / 100 AS RESULT_FULL_RATE_001
,RESULT_DONG_RATE / 100 AS RESULT_DONG_RATE_001
,RESULT_BDONG_RATE / 100 AS RESULT_BDONG_RATE_001
,TARGET_FULL_RATE * 100 AS TARGET_FULL_RATE_100
,RESULT_FULL_RATE * 100 AS RESULT_FULL_RATE_100
,RESULT_DONG_RATE * 100 AS RESULT_DONG_RATE_100
,RESULT_BDONG_RATE * 100 AS RESULT_BDONG_RATE_100
FROM NHIT_DASHBOARD_EMP_MERGE
WHERE CR_YEAR = @CR_YEAR
ORDER BY CR_MONTH
";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar);
paramArray[0].Value = cr_year;
return(DbAgentObj.Fill(query, paramArray).Tables[0]);
}
public DataSet GetMenuInfo()
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT MENU_REF_ID , \n");
sbQuery.Append(" UP_MENU_ID , \n");
sbQuery.Append(" MENU_NAME , \n");
sbQuery.Append(" MENU_DIR , \n");
sbQuery.Append(" MENU_PAGE_NAME , \n");
sbQuery.Append(" MENU_PARAM , \n");
sbQuery.Append(" MENU_FULL_PATH , \n");
sbQuery.Append(" MENU_DESC , \n");
sbQuery.Append(" MENU_PRIORITY , \n");
sbQuery.Append(" MENU_TYPE , \n");
sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n");
sbQuery.Append(" MENU_PREV_ICON_PATH , \n");
sbQuery.Append(" MENU_CREATE_DATE , \n");
sbQuery.Append(" CASE MENU_TYPE WHEN 'T' THEN 1 \n");
sbQuery.Append(" WHEN 'M' THEN 2 \n");
sbQuery.Append(" WHEN 'S' THEN 3 \n");
sbQuery.Append(" ELSE 4 \n");
sbQuery.Append(" END V_ORDER \n");
sbQuery.Append(" FROM COM_MENU_INFO \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" V_ORDER , \n");
sbQuery.Append(" MENU_PRIORITY \n");
return(DbAgentObj.Fill(sbQuery.ToString()));
}
/// <summary>
/// 정보가 없으면 "" 리턴
/// </summary>
public string Select_EMP_REF_ID(string LOGIN_ID)
{
DataSet DS = new DataSet();
string result;
string query = @"
SELECT EMP_REF_ID
FROM COM_EMP_INFO
WHERE LOGINID = @LOGIN_ID
ORDER BY MODIFY_DATE DESC";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@LOGIN_ID", SqlDbType.NVarChar);
paramArray[0].Value = LOGIN_ID;
DS = DbAgentObj.Fill(query, paramArray);
if (DS.Tables[0].Rows.Count == 0)
{
result = "";
}
else
{
result = DS.Tables[0].Rows[0][0].ToString();
}
return(result);
}
public DataTable Select_DB(object dept_ref_id)
{
string query = @"
SELECT DEPT_REF_ID
, UP_DEPT_ID
, DEPT_LEVEL
, DEPT_NAME
, DEPT_CODE
, DEPT_FLAG
, DEPT_TYPE
, SORT_ORDER
, DEPT_DESC
, CREATE_USER
, UPDATE_USER
FROM COM_DEPT_INFO
WHERE ( DEPT_REF_ID = @DEPT_REF_ID OR @DEPT_REF_ID = 0 )
";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int);
paramArray[0].Value = dept_ref_id;
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
/// <summary>
/// GetDSCancelMailInfo
/// : 취소시에 메일링 처리할 내용을 리턴한다.
/// </summary>
/// <param name="asAppRefID"></param>
/// <param name="asAppStep"></param>
/// <returns></returns>
public DataSet GetDSCancelMailInfo(string asAppRefID, string asAppStep)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT D.KPI_CODE + ' (' + D.KPI_NAME + ')' V_KPIINFO, \n");
sbQuery.Append(" F.EMP_NAME V_APP_EMP_NAME, \n");
sbQuery.Append(" F.EMP_EMAIL V_APP_EMP_EMAIL, \n");
sbQuery.Append(" E.EMP_EMAIL V_REP_EMP_EMAIL \n");
sbQuery.Append(" FROM COM_APPROVAL_INFO A, \n");
sbQuery.Append(" COM_APPROVAL_PRC B, \n");
sbQuery.Append(" KPI_INFO D, \n");
sbQuery.Append(" COM_EMP_INFO E, \n");
sbQuery.Append(" COM_EMP_INFO F \n");
sbQuery.Append(" WHERE A.APP_REF_ID = B.APP_REF_ID \n");
sbQuery.Append(" AND B.APP_STEP = CONVERT(INT,@APP_STEP) \n");
sbQuery.Append(" AND D.KPI_REF_ID = A.EVENT_ID \n");
sbQuery.Append(" AND E.EMP_REF_ID = A.REP_EMP_ID \n");
sbQuery.Append(" AND F.EMP_REF_ID = B.APP_EMP_ID \n");
sbQuery.Append(" AND A.APP_REF_ID = @APP_REF_ID \n");
paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@APP_STEP", SqlDbType.VarChar);
paramArray[0].Value = asAppRefID;
paramArray[1].Value = asAppStep;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
/// <summary>
/// 최상위 객체의 정보를 가지고 온다. (UP_STG_ID 이 NULL 인 개체)
/// </summary>
/// <returns></returns>
public DataSet GetTopParents(int isContainType)
{
// -1->최상위부모가아님, 0->전체, 1->자식이존재함, 2->자식이존재하지않음
string query = @"
SELECT * FROM (
SELECT A.ESTTERM_REF_ID
, A.EST_DEPT_REF_ID
, A.STG_REF_ID
, NULL AS UP_STG_ID
, A.VIEW_REF_ID AS STG_MAP_TYPE
, B.VIEW_SEQ
, A.SORT_ORDER
, CASE WHEN
(SELECT COUNT(*)
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND UP_STG_REF_ID = A.STG_REF_ID) > 0
THEN
1
ELSE
2
END AS ISCONTAIN
FROM BSC_MAP_STG A
JOIN BSC_VIEW_INFO B ON A.VIEW_REF_ID = B.VIEW_REF_ID
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND A.MAP_VERSION_ID = @MAP_VERSION_ID
AND A.STG_REF_ID NOT IN (SELECT STG_REF_ID
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID)
) X
WHERE (X.ISCONTAIN = @ISCONTAIN OR @ISCONTAIN = 0)
ORDER BY X.VIEW_SEQ, X.SORT_ORDER";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@ISCONTAIN", SqlDbType.Int);
paramArray[2].Value = isContainType;
paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet ds = DbAgentObj.Fill(query, paramArray, CommandType.Text);
return(ds);
}
/// <summary>
/// GetDDLAppGubun
/// : 업무 구분 드롭다운리스트 추출
/// </summary>
/// <returns></returns>
public DataSet GetDDLAppGubun()
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT BIZ_TYPE_CODE V_CODE, \n");
sbQuery.Append(" BIZ_TYPE_NAME V_NAME \n");
sbQuery.Append(" FROM COM_BIZ_INFO \n");
sbQuery.Append(" ORDER BY BIZ_TYPE_CODE DESC \n");
return(DbAgentObj.Fill(sbQuery.ToString()));
}
/// <summary>
/// GetDDLEstTerm
/// : 평가 기간 드롭다운리스트 추출
/// </summary>
/// <returns></returns>
public DataSet GetDDLEstTerm()
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT ESTTERM_REF_ID V_CODE, \n");
sbQuery.Append(" ESTTERM_NAME V_NAME \n");
sbQuery.Append(" FROM EST_TERM_INFO \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" EST_STARTDATE DESC \n");
return(DbAgentObj.Fill(sbQuery.ToString()));
}
public DataTable Select_Com_Dept_Type()
{
DataSet DS = new DataSet();
string query = @"
SELECT TYPE_REF_ID
FROM COM_DEPT_TYPE_INFO";
DS = DbAgentObj.Fill(query);
return(DS.Tables[0]);
}
/// <summary>
/// GetAllType
/// : 측정단계 드롭다운리스트 추출
/// </summary>
/// <returns></returns>
public DataSet GetAllType()
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT DISTINCT \n");
sbQuery.Append(" CONVERT(VARCHAR,KPI_THRESHOLD_TYPE) + ' 단계' V_TYPE_NAME, \n");
sbQuery.Append(" KPI_THRESHOLD_TYPE V_TYPE \n");
sbQuery.Append(" FROM COM_KPI_THRESHOLD_CODE \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" KPI_THRESHOLD_TYPE \n");
return(DbAgentObj.Fill(sbQuery.ToString()));
}
public DataTable Select_Est_Position_Table(object pos_id
, object pos_table_name)
{
string query = string.Format(@"
SELECT POS_{0}_ID
, POS_{0}_NAME
FROM {1}
", pos_id, pos_table_name);
DataTable dt = DbAgentObj.Fill(query).Tables[0];
return(dt);
}
// 전사 매출/손익
public DataTable SelectJeonSa(object grp_one_code
, object grp_two_code
, object grp_three_code
, object cr_year
, object cr_month
, object tg_gubun)
{
string query = @"
SELECT GRP_ONE_CODE
,GRP_ONE_NAME
,GRP_TWO_CODE
,GRP_TWO_NAME
,GRP_THREE_CODE
,GRP_THREE_NAME
,CR_YEAR
,CR_MONTH
,CR_MONTH + '월' AS CR_MONTH_NAME
,TARGET_YEAR
,TARGET_TS
,RESULT_TS
,DAL_RATE
FROM NHIT_DASHBOARD_MAIN
WHERE GRP_ONE_CODE = @GRP_ONE_CODE
AND GRP_TWO_CODE = @GRP_TWO_CODE
AND GRP_THREE_CODE = @GRP_THREE_CODE
AND (CR_YEAR = @CR_YEAR )
AND (CR_MONTH = @CR_MONTH OR @CR_MONTH ='' )
AND TG_GUBUN = @TG_GUBUN
ORDER BY GRP_TWO_CODE, CR_MONTH
";
IDbDataParameter[] paramArray = CreateDataParameters(6);
paramArray[0] = CreateDataParameter("@GRP_ONE_CODE", SqlDbType.NVarChar);
paramArray[0].Value = grp_one_code;
paramArray[1] = CreateDataParameter("@GRP_TWO_CODE", SqlDbType.NVarChar);
paramArray[1].Value = grp_two_code;
paramArray[2] = CreateDataParameter("@GRP_THREE_CODE", SqlDbType.NVarChar);
paramArray[2].Value = grp_three_code;
paramArray[3] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar);
paramArray[3].Value = cr_year;
paramArray[4] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar);
paramArray[4].Value = cr_month;
paramArray[5] = CreateDataParameter("@TG_GUBUN", SqlDbType.NVarChar);
paramArray[5].Value = tg_gubun;
return(DbAgentObj.Fill(query, paramArray).Tables[0]);
}
public DataTable Select_DB(object comp_id
, object estterm_ref_id
, object est_id
, object seq
, object pos_id
, object pos_value
, object dept_type_ref_id)
{
string query = @"
SELECT COMP_ID
, ESTTERM_REF_ID
, EST_ID
, SEQ
, POS_ID
, POS_VALUE
, DEPT_TYPE_REF_ID
, WEIGHT
FROM EST_POS_WEIGHT
WHERE ( COMP_ID = @COMP_ID OR @COMP_ID = 0 )
AND ( ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0 )
AND ( EST_ID = @EST_ID OR @EST_ID ='' )
AND ( SEQ = @SEQ OR @SEQ = 0 )
AND ( POS_ID = @POS_ID OR @POS_ID ='' )
AND ( POS_VALUE = @POS_VALUE OR @POS_VALUE ='' )
AND ( DEPT_TYPE_REF_ID = @DEPT_TYPE_REF_ID OR @DEPT_TYPE_REF_ID = 0 )
";
IDbDataParameter[] paramArray = CreateDataParameters(7);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[2].Value = est_id;
paramArray[3] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[3].Value = seq;
paramArray[4] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar);
paramArray[4].Value = pos_id;
paramArray[5] = CreateDataParameter("@POS_VALUE", SqlDbType.NVarChar);
paramArray[5].Value = pos_value;
paramArray[6] = CreateDataParameter("@DEPT_TYPE_REF_ID", SqlDbType.Int);
paramArray[6].Value = dept_type_ref_id;
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
public DataTable SelectRoot_DB()
{
DataSet DS = new DataSet();
string query = @"
SELECT DEPT_REF_ID
FROM COM_DEPT_INFO
WHERE ISNULL(UP_DEPT_ID, 0) = 0
AND DEPT_FLAG = 1
";
DS = DbAgentObj.Fill(query, null);
return(DS.Tables[0]);
}
public DataTable Select_Est_Pos_Weight(object pos_id
, object pos_table_name
, object dept_type_ref_id_list
, object comp_id
, object estterm_ref_id
, object est_id)
{
string query = string.Format(@"
SELECT '{0}' AS POS_ID
, POS.POS_{0}_ID AS POS_VALUE
, POS.POS_{0}_NAME AS POS_VALUE_NAME
, PW.WEIGHT
, PW.SEQ
, PW.DEPT_TYPE_REF_ID
, DI.TYPE_NAME AS DEPT_TYPE_NAME
FROM {1} POS
LEFT OUTER JOIN EST_POS_WEIGHT PW
ON (
POS.POS_{0}_ID = PW.POS_VALUE
AND PW.POS_ID = POS_ID
AND COMP_ID = @COMP_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND PW.DEPT_TYPE_REF_ID IN ( {2} )
AND EST_ID = @EST_ID
)
LEFT OUTER JOIN COM_DEPT_TYPE_INFO DI
ON (
PW.DEPT_TYPE_REF_ID=DI.TYPE_REF_ID
)
ORDER BY PW.SEQ ASC
", pos_id, pos_table_name, dept_type_ref_id_list);
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[2].Value = est_id;
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
public DataTable SelectMonth_DB()
{
DataSet DS = new DataSet();
string query = @"
SELECT CR_MONTH
, CR_MONTH + '월' AS CR_MONTH_NAME
FROM NHIT_DASHBOARD_MAIN
GROUP BY CR_MONTH
ORDER BY CR_MONTH
";
DS = DbAgentObj.Fill(query, null);
return(DS.Tables[0]);
}
public DataTable Select_Emp_Info(object emp_ref_id)
{
string query = @"
SELECT EMP_REF_ID
, EMP_CODE
, LOGINID
, LOGINIP
, PASSWD
, EMP_NAME
, EMP_EMAIL
, POSITION_CLASS_CODE
, POSITION_GRP_CODE
, POSITION_RANK_CODE
, POSITION_DUTY_CODE
, POSITION_STAT_CODE
, POSITION_KIND_CODE
, DAILY_PHONE
, CELL_PHONE
, FAX_NUMBER
, JOB_STATUS
, ZIPCODE
, ADDR_1
, ADDR_2
, SIGN_PATH
, STAMP_PATH
, CREATE_TYPE
, CREATE_DATE
, CREATE_EMP_ID
, MODIFY_TYPE
, MODIFY_DATE
, MODIFY_EMP_ID
FROM COM_EMP_INFO
WHERE EMP_REF_ID = @EMP_REF_ID
";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.NVarChar);
paramArray[0].Value = emp_ref_id;
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
public DataSet GetRoleInfo(int aiEmpRefID)
{
string query = @"SELECT ROLE_REF_ID
, ROLE_NAME
FROM COM_ROLE_INFO
WHERE ROLE_REF_ID NOT IN (SELECT ROLE_REF_ID
FROM COM_EMP_ROLE_REL
WHERE EMP_REF_ID = @EMP_REF_ID)
ORDER BY SORT_ORDER";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[0].Value = aiEmpRefID;
return(DbAgentObj.Fill(query, paramArray));
}
/// <summary>
/// 부서와 상위부서
/// </summary>
public DataTable Select_Dept_UpDept()
{
string query = @"
SELECT DEPT.DEPT_REF_ID AS DEPT_ID
, DEPT.DEPT_NAME
, DEPT.UP_DEPT_ID
, UP_DEPT.DEPT_NAME AS UP_DEPT_NAME
FROM COM_DEPT_INFO DEPT
LEFT OUTER JOIN COM_DEPT_INFO UP_DEPT
ON DEPT.UP_DEPT_ID = UP_DEPT.DEPT_REF_ID
ORDER BY
DEPT.SORT_ORDER
";
DataTable dt = DbAgentObj.Fill(query).Tables[0];
return(dt);
}
public DataTable SelectYear_DB()
{
DataSet DS = new DataSet();
string query = @"
SELECT CR_YEAR
, CR_YEAR + '년' AS CR_YEAR_NAME
FROM NHIT_DASHBOARD_MAIN
GROUP BY CR_YEAR
ORDER BY CR_YEAR
";
DS = DbAgentObj.Fill(query, null);
return(DS.Tables[0]);
}
public DataTable SelectEmpProjectDetail(object cr_year
, object cr_month
, object project_name)
{
string query = @"
SELECT CR_YEAR
,CR_MONTH
,BONBU_NAME
,TEAM_NAME
,GRP_ONE_B_NAME
,GRP_ONE_C_NAME
,EMP_NAME
,RESULT_FULL
,RESULT_DONG
,RESULT_BDONG
FROM NHIT_DASHBOARD_EMP
WHERE CR_YEAR = @CR_YEAR
AND CR_MONTH = @CR_MONTH
AND PROJECT_NAME = @PROJECT_NAME
ORDER BY CR_YEAR
,CR_MONTH
,BONBU_NAME
,TEAM_NAME
,GRP_ONE_B_NAME
,GRP_ONE_C_NAME
,EMP_NAME
,RESULT_FULL
,RESULT_DONG
,RESULT_BDONG
";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar);
paramArray[0].Value = cr_year;
paramArray[1] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar);
paramArray[1].Value = cr_month;
paramArray[2] = CreateDataParameter("@PROJECT_NAME", SqlDbType.NVarChar);
paramArray[2].Value = project_name;
return(DbAgentObj.Fill(query, paramArray).Tables[0]);
}
public DataTable Select_DB(object pos_id
, object pos_name
, object pos_table_name
, object pos_type_name
, object use_yn
, object sort_order)
{
string query = @"
SELECT POS_ID
, POS_NAME
, POS_TABLE_NAME
, POS_TYPE_NAME
, USE_YN
, SORT_ORDER
FROM EST_POSITION_INFO
WHERE ( POS_ID = @POS_ID OR @POS_ID ='' )
AND ( POS_NAME = @POS_NAME OR @POS_NAME ='' )
AND ( POS_TABLE_NAME = @POS_TABLE_NAME OR @POS_TABLE_NAME ='' )
AND ( POS_TYPE_NAME = @POS_TYPE_NAME OR @POS_TYPE_NAME ='' )
AND ( USE_YN = @USE_YN OR @USE_YN ='' )
AND ( SORT_ORDER = @SORT_ORDER OR @SORT_ORDER = 0 )
";
IDbDataParameter[] paramArray = CreateDataParameters(6);
paramArray[0] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar);
paramArray[0].Value = pos_id;
paramArray[1] = CreateDataParameter("@POS_NAME", SqlDbType.NVarChar);
paramArray[1].Value = pos_name;
paramArray[2] = CreateDataParameter("@POS_TABLE_NAME", SqlDbType.NVarChar);
paramArray[2].Value = pos_table_name;
paramArray[3] = CreateDataParameter("@POS_TYPE_NAME", SqlDbType.NVarChar);
paramArray[3].Value = pos_type_name;
paramArray[4] = CreateDataParameter("@USE_YN", SqlDbType.NVarChar);
paramArray[4].Value = use_yn;
paramArray[5] = CreateDataParameter("@SORT_ORDER", SqlDbType.Int);
paramArray[5].Value = sort_order;
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
/// <summary>
/// GetSearchDetail
/// : 세부사항 추출쿼리 (app2000에서 사용)
/// </summary>
/// <param name="asAppRefID"></param>
/// <returns></returns>
public DataSet GetSearchDetail(string asAppRefID)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT A.APP_STEP V_APP_STEP , -- 순번 \n");
sbQuery.Append(" B.EMP_NAME V_EMP_NAME , -- 성명 \n");
sbQuery.Append(" B.POSITION_DUTY_NAME V_POSITION_DUTY_NAME, -- 직책 \n");
sbQuery.Append(" B.DEPT_NAME V_DEPT_NAME , -- 부서명 \n");
sbQuery.Append(" \n");
sbQuery.Append(" CASE A.APP_STATUS WHEN 'P' THEN '대기' \n");
sbQuery.Append(" WHEN 'E' THEN '승인' \n");
sbQuery.Append(" WHEN 'C' THEN '취소' \n");
sbQuery.Append(" END V_APP_STATUS , -- 결재상태 \n");
sbQuery.Append(" A.APP_STATUS V_APP_STATUS_CD , -- 결재상태 코드 \n");
sbQuery.Append(" A.APP_DATE V_APP_DATE , -- 결재일시 \n");
sbQuery.Append(" A.APP_REMARK V_APP_REMARK -- 취소사유 \n");
sbQuery.Append(" FROM COM_APPROVAL_PRC A, \n");
sbQuery.Append(" ( \n");
sbQuery.Append(" SELECT \n");
sbQuery.Append(" A.EMP_REF_ID, \n");
sbQuery.Append(" A.EMP_NAME, \n");
sbQuery.Append(" D.POSITION_DUTY_NAME, \n");
sbQuery.Append(" B.DEPT_NAME \n");
sbQuery.Append(" FROM COM_EMP_INFO A, -- 사용자 테이블 \n");
sbQuery.Append(" COM_DEPT_INFO B, -- 부서 테이블 \n");
sbQuery.Append(" REL_DEPT_EMP C, -- 사용자, 부서코드 연결 테이블 \n");
sbQuery.Append(" COM_POSITION_DUTY D -- 직책테이블 \n");
sbQuery.Append(" WHERE A.EMP_REF_ID = C.EMP_REF_ID \n");
sbQuery.Append(" AND C.DEPT_REF_ID= B.DEPT_REF_ID \n");
sbQuery.Append(" AND D.POSITION_DUTY_CODE = A.POSITION_DUTY_CODE \n");
sbQuery.Append(" ) B \n");
sbQuery.Append(" WHERE A.APP_EMP_ID = B.EMP_REF_ID \n");
sbQuery.Append(" AND A.APP_REF_ID = @APP_REF_ID \n");
sbQuery.Append(" ORDER BY \n");
sbQuery.Append(" APP_STEP \n");
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar);
paramArray[0].Value = asAppRefID;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
public DataTable SelectEmpProject(object cr_year
, object cr_month)
{
string query = @"
SELECT CR_YEAR
,CR_MONTH
,BONBU_NAME
,TEAM_NAME
,GRP_ONE_B_NAME
,GRP_ONE_C_NAME
,PROJECT_NAME
,COUNT(EMP_NAME) AS EMP_COUNT
FROM NHIT_DASHBOARD_EMP
WHERE CR_YEAR = @CR_YEAR
AND CR_MONTH = @CR_MONTH
GROUP BY CR_YEAR
,CR_MONTH
,BONBU_NAME
,TEAM_NAME
,GRP_ONE_B_NAME
,GRP_ONE_C_NAME
,PROJECT_NAME
ORDER BY CR_YEAR
,CR_MONTH
,BONBU_NAME
,TEAM_NAME
,GRP_ONE_B_NAME
,GRP_ONE_C_NAME
,PROJECT_NAME
";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar);
paramArray[0].Value = cr_year;
paramArray[1] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar);
paramArray[1].Value = cr_month;
return(DbAgentObj.Fill(query, paramArray).Tables[0]);
}
/// <summary>
/// GetKPIApprovalInfo
/// : KPI_REF_ID가 포함된 결재관련 정보 리턴
/// </summary>
/// <param name="asKpiRefID"></param>
/// <returns></returns>
public DataSet GetKPIApprovalInfo(string asKpiRefID, string asAppCode, string asMonthInfo)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT \n");
sbQuery.Append(" DBO.FN_GETAPPSTATUS(CONVERT(INT, @KPI_REF_ID), @APP_CODE, @MONTH_INFO) V_APP_STATUS -- 전체결재상태 \n");
sbQuery.Append(" ,(SELECT MAX(CHAMPION_EMP_ID) FROM KPI_INFO WHERE KPI_REF_ID = CONVERT(INT, @KPI_REF_ID)) V_CHAM_EMP_ID -- 챔피언 \n");
sbQuery.Append(" ,COUNT(A.APP_STATUS) V_CNT \n");
sbQuery.Append(" FROM COM_APPROVAL_INFO A, -- 결재정보 \n");
sbQuery.Append(" COM_APPROVAL_PRC B, -- 결재 HISTORY 정보 \n");
sbQuery.Append(" COM_EMP_INFO C, -- 상신자 정보 \n");
sbQuery.Append(" COM_EMP_INFO D, -- 결재자 정보 \n");
sbQuery.Append(" COM_BIZ_INFO E, -- 결재타입 정보 \n");
sbQuery.Append(" ( \n");
sbQuery.Append(" SELECT BIZ_TYPE_CODE, \n");
sbQuery.Append(" MAX(APP_STEP) MAX_APP_STEP \n");
sbQuery.Append(" FROM COM_APPROVAL_LINE \n");
sbQuery.Append(" GROUP BY \n");
sbQuery.Append(" BIZ_TYPE_CODE \n");
sbQuery.Append(" ) F -- 결재선 정보 \n");
sbQuery.Append(" WHERE 1=1 \n");
sbQuery.Append(" AND A.APP_REF_ID = B.APP_REF_ID \n");
sbQuery.Append(" AND A.CUR_APP_STEP = B.APP_STEP \n");
sbQuery.Append(" AND A.REP_EMP_ID = C.EMP_REF_ID \n");
sbQuery.Append(" AND B.APP_EMP_ID = D.EMP_REF_ID \n");
sbQuery.Append(" AND A.APP_CODE = E.BIZ_TYPE_CODE \n");
sbQuery.Append(" AND E.BIZ_TYPE_CODE = F.BIZ_TYPE_CODE \n");
sbQuery.Append(" AND A.EVENT_ID = CONVERT(INT, @KPI_REF_ID) \n");
sbQuery.Append(" AND A.APP_CODE = @APP_CODE \n");
paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@KPI_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@APP_CODE", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@MONTH_INFO", SqlDbType.VarChar);
paramArray[0].Value = asKpiRefID;
paramArray[1].Value = asAppCode;
paramArray[2].Value = asMonthInfo;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
