/// <summary>
/// GetMenuTitle
/// : 해당페이지의 메뉴명을 리턴한다. (타이틀 표시시 사용)
/// </summary>
/// <param name="asPageUrl"></param>
/// <returns></returns>
public string GetMenuTitle(string asPageUrl)
{
string s_query = @"
SELECT
ISNULL(MAX(MENU_NAME), ' ')
FROM
(SELECT MENU_NAME FROM COM_MENU_INFO WHERE UPPER(MENU_FULL_PATH) = UPPER(@PAGEURL) AND MENU_TYPE = 'S') A
";
string o_query = @"
SELECT
NVL(MAX(MENU_NAME), ' ')
FROM
(SELECT MENU_NAME FROM COM_MENU_INFO WHERE UPPER(MENU_FULL_PATH) = UPPER(@PAGEURL) AND MENU_TYPE = 'S') A
";
StringBuilder sbQuery = new StringBuilder();
IDbDataParameter[] paramArray = null;
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@PAGEURL", SqlDbType.VarChar);
paramArray[0].Value = asPageUrl;
return(Convert.ToString(DbAgentObj.ExecuteScalar(GetQueryStringByDb(s_query, o_query), paramArray)));
}
public int Count(int emp_ref_id
, string pos_dut_id
, DateTime start_date)
{
string query = @"SELECT COUNT(*) FROM EST_POS_DUT_EMP
WHERE (EMP_REF_ID = @EMP_REF_ID OR @EMP_REF_ID = 0)
AND (POS_DUT_ID = @POS_DUT_ID OR @POS_DUT_ID ='' )
AND START_DATE = @START_DATE";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[0].Value = emp_ref_id;
paramArray[1] = CreateDataParameter("@POS_DUT_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = pos_dut_id;
paramArray[2] = CreateDataParameter("@START_DATE", SqlDbType.DateTime);
paramArray[2].Value = start_date;
try
{
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString()));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// ConfirmRemainStep
/// : 현재 처리하려는 문서 이후로 몇건의 결재문서가 남아 있는가?
/// </summary>
/// <param name="asAppRefID"></param>
/// <param name="asTermRefID"></param>
/// <returns></returns>
public int ConfirmRemainStep(string asAppRefID, string asTermRefID)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT (C.MAX_APP_STEP - B.APP_STEP) V_REMAIN_STEP \n");
sbQuery.Append(" FROM COM_APPROVAL_INFO A, \n");
sbQuery.Append(" COM_APPROVAL_PRC B, \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(" ) C \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.APP_CODE = C.BIZ_TYPE_CODE \n");
sbQuery.Append(" AND A.APP_REF_ID = @APP_REF_ID \n");
sbQuery.Append(" AND A.APPTERM_REF_ID = CONVERT(INT, @TERM_REF_ID) \n");
paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@TERM_REF_ID", SqlDbType.VarChar);
paramArray[0].Value = asAppRefID;
paramArray[1].Value = asTermRefID;
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(sbQuery.ToString(), paramArray)));
}
public int Count(int comp_id, string est_id)
{
string query = @"SELECT COUNT(*) FROM EST_INFO
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_id;
try
{
object result = DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text);
if (string.IsNullOrEmpty(result.ToString()))
{
return(0);
}
else
{
return(Convert.ToInt32(result.ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
}
public int Count(int comp_id, string est_id, string col_key)
{
string query = @"SELECT COUNT(*) FROM EST_COLUMN_INFO
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND COL_KEY = @COL_KEY " ;
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@COL_KEY", SqlDbType.NVarChar, 100);
paramArray[2].Value = col_key;
try
{
int affectedRow = Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString());
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int Count(int comp_id
, string est_id
, string grade_id
, string scale_id)
{
string query = @"SELECT COUNT(*) FROM EST_SCOPE
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND GRADE_ID = @GRADE_ID
AND SCALE_ID = @SCALE_ID";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@GRADE_ID", SqlDbType.NVarChar, 6);
paramArray[2].Value = grade_id;
paramArray[3] = CreateDataParameter("@SCALE_ID", SqlDbType.NVarChar, 12);
paramArray[3].Value = scale_id;
try
{
int affectedRow = Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text));
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
// [20070917]juny177추가 : 평가부서 맵트리에서 부서 선택시 선택된 부서의 레벨을 리턴한다.
public string GetDeptpathSelectLevel(int est_det_ref_id, int est_term_ref_id)
{
string s_query = @"
SELECT
ISNULL(DEPT_LEVEL,0) AS LEVEL
FROM
EST_DEPT_INFO
WHERE
EST_DEPT_REF_ID = @EST_DEPT_REF_ID AND ESTTERM_REF_ID = @EST_TERM_REF_ID
";
string o_query = @"
SELECT
NVL(DEPT_LEVEL,0)
FROM
EST_DEPT_INFO
WHERE
EST_DEPT_REF_ID = @EST_DEPT_REF_ID AND ESTTERM_REF_ID = @EST_TERM_REF_ID
AND ROWNUM = 1
";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[0].Value = est_det_ref_id;
paramArray[1] = CreateDataParameter("@EST_TERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = est_term_ref_id;
return(DbAgentObj.ExecuteScalar(GetQueryStringByDb(s_query, o_query), paramArray, CommandType.Text).ToString());
}
public int Count(int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, string est_job_id)
{
string query = @"SELECT COUNT(*)
FROM EST_JOB_DETAIL
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID = 0)
AND (ESTTERM_STEP_ID = @ESTTERM_STEP_ID )
AND (EST_JOB_ID = @EST_JOB_ID OR @EST_JOB_ID ='' )" ;
IDbDataParameter[] paramArray = CreateDataParameters(6);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_JOB_ID", SqlDbType.NVarChar, 20);
paramArray[5].Value = est_job_id;
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString()));
}
public int Count(int comp_id
, int estterm_ref_id
, int estterm_sub_id
, string est_sche_id)
{
string query = @"SELECT COUNT(*) FROM EST_SCHE_DETAIL
WHERE COMP_ID = @COMP_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND EST_SCHE_ID = @EST_SCHE_ID" ;
IDbDataParameter[] paramArray = CreateDataParameters(4);
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("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[2].Value = estterm_sub_id;
paramArray[3] = CreateDataParameter("@EST_SCHE_ID", SqlDbType.NVarChar, 20);
paramArray[3].Value = est_sche_id;
try
{
int affectedRow = Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString());
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int NewIdx(int comp_id, int estterm_ref_id, int dept_ref_id, string est_id)
{
string query = @"SELECT MAX(SEQ) FROM EST_DEPT_POS_SCALE
WHERE COMP_ID = @COMP_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND DEPT_REF_ID = @DEPT_REF_ID
AND EST_ID = @EST_ID";
IDbDataParameter[] paramArray = CreateDataParameters(4);
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("@DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = dept_ref_id;
paramArray[3] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[3].Value = est_id;
try
{
object max = DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text);
if (max == DBNull.Value)
{
return(1);
}
return((int)max + 1);
}
catch (Exception ex)
{
throw ex;
}
}
public int Count(int comp_id
, string est_id
, string est_job_id
, string col_key)
{
string query = @"SELECT COUNT(*)
FROM EST_JOB_COLUMN_MAP
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (EST_JOB_ID = @EST_JOB_ID OR @EST_JOB_ID ='' )
AND (COL_KEY = @COL_KEY OR @COL_KEY ='' )" ;
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@EST_JOB_ID", SqlDbType.NVarChar, 20);
paramArray[2].Value = est_job_id;
paramArray[3] = CreateDataParameter("@COL_KEY", SqlDbType.NVarChar);
paramArray[3].Value = col_key;
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString()));
}
public bool InsertIssueGroupMap(IDbConnection conn, IDbTransaction trx, object estterm_ref_id, object group_code, DataTable dtInsert, object reg_user)
{
this.Transaction_Message = "";
string strQuery = @"
IF NOT EXISTS (SELECT TOP 1 * FROM BSC_KPI_GROUP_MAP WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID AND GROUP_CODE = @GROUP_CODE AND KPI_REF_ID IN ({0}))
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 2
END
";
string strExists = string.Empty;
foreach (DataRow dr in dtInsert.Rows)
{
strExists += dr["KPI_REF_ID"].ToString() + ", ";
}
strExists = strExists.Substring(0, strExists.Length - 2);
strQuery = string.Format(strQuery, strExists);
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@GROUP_CODE", SqlDbType.Int);
paramArray[1].Value = group_code;
if (DbAgentObj.ExecuteScalar(conn, trx, strQuery, paramArray, CommandType.Text).ToString() == "2")
{
return(false);
}
strQuery = @"
INSERT INTO BSC_KPI_GROUP_MAP (ESTTERM_REF_ID, GROUP_CODE, KPI_REF_ID, CREATE_USER, CREATE_DATE)
VALUES (@ESTTERM_REF_ID, @GROUP_CODE, @KPI_REF_ID, @CREATE_USER, GETDATE())
";
foreach (DataRow dr in dtInsert.Rows)
{
paramArray = null;
paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@GROUP_CODE", SqlDbType.Int);
paramArray[1].Value = group_code;
paramArray[2] = CreateDataParameter("@KPI_REF_ID", SqlDbType.Int);
paramArray[2].Value = dr["KPI_REF_ID"];
paramArray[3] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[3].Value = reg_user;
if (DbAgentObj.ExecuteNonQuery(conn, trx, strQuery, paramArray, CommandType.Text) == 0)
{
return(false);
}
}
return(true);
}
public int Count(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int tgt_dept_id
, int tgt_emp_id
, int seq)
{
string query = @"SELECT COUNT(*) FROM EST_QUESTION_COMMENT
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID = 0)
AND (ESTTERM_STEP_ID = @ESTTERM_STEP_ID OR @ESTTERM_STEP_ID = 0)
AND (EST_DEPT_ID = @EST_DEPT_ID OR @EST_DEPT_ID = 0)
AND (EST_EMP_ID = @EST_EMP_ID OR @EST_EMP_ID = 0)
AND (TGT_DEPT_ID = @TGT_DEPT_ID OR @TGT_DEPT_ID = 0)
AND (TGT_EMP_ID = @TGT_EMP_ID OR @TGT_EMP_ID = 0)
AND (SEQ = @SEQ OR @SEQ = 0)" ;
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[7].Value = tgt_dept_id;
paramArray[8] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[8].Value = tgt_emp_id;
paramArray[9] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[9].Value = seq;
try
{
int affectedRow = Convert.ToInt32(DbAgentObj.ExecuteScalar(conn, trx, query, paramArray, CommandType.Text));
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int SaveEmployeeDeptDetail(int aiEMP_REF_ID
, int aiPrevDeptID
, int aiDEPT_REF_ID
, int aiCREATE_EMP_ID)
{
object retValue;
string queryString;
IDbDataParameter[] paramArray;
queryString = @"SELECT EMP_REF_ID, DEPT_REF_ID
FROM BSC_EMP_COM_DEPT_DETAIL
WHERE EMP_REF_ID = @EMP_REF_ID AND DEPT_REF_ID = @PREV_DEPT_REF_ID";
paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[1] = CreateDataParameter("@PREV_DEPT_REF_ID", SqlDbType.Int);
paramArray[0].Value = aiEMP_REF_ID;
paramArray[1].Value = aiPrevDeptID;
//retValue = DbAgentObj.ExecuteNonQuery(queryString, paramArray);
retValue = DbAgentObj.ExecuteScalar(queryString, paramArray);
if (retValue == null)
{
queryString = @"INSERT INTO BSC_EMP_COM_DEPT_DETAIL
(EMP_REF_ID, DEPT_REF_ID, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE)
VALUES
(@EMP_REF_ID, @DEPT_REF_ID, @CREATE_EMP_ID, GETDATE(), @CREATE_EMP_ID, GETDATE())";
paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@CREATE_EMP_ID", SqlDbType.VarChar);
paramArray[0].Value = aiEMP_REF_ID;
paramArray[1].Value = aiDEPT_REF_ID;
paramArray[2].Value = aiCREATE_EMP_ID;
return(DbAgentObj.ExecuteNonQuery(queryString, paramArray));
}
else
{
queryString = @"UPDATE BSC_EMP_COM_DEPT_DETAIL
SET DEPT_REF_ID = @DEPT_REF_ID
WHERE EMP_REF_ID = @EMP_REF_ID AND DEPT_REF_ID = @PREV_DEPT_REF_ID";
paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@PREV_DEPT_REF_ID", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.VarChar);
paramArray[0].Value = aiEMP_REF_ID;
paramArray[1].Value = aiPrevDeptID;
paramArray[2].Value = aiDEPT_REF_ID;
return(DbAgentObj.ExecuteNonQuery(queryString, paramArray));
}
}
public int Count(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int tgt_emp_id
, int prj_ref_id
, string q_sbj_id)
{
string query = @"SELECT COUNT(*) FROM PRJ_QUESTION_DATA
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID = 0)
AND (ESTTERM_STEP_ID = @ESTTERM_STEP_ID OR @ESTTERM_STEP_ID = 0)
AND (EST_DEPT_ID = @EST_DEPT_ID OR @EST_DEPT_ID = 0)
AND (EST_EMP_ID = @EST_EMP_ID OR @EST_EMP_ID = 0)
AND (PRJ_REF_ID = @PRJ_REF_ID OR @PRJ_REF_ID = 0)
AND (Q_SBJ_ID = @Q_SBJ_ID OR @Q_SBJ_ID ='' )
AND (UPDATE_USER = @TGT_EMP_ID OR @TGT_EMP_ID = 0)";
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@PRJ_REF_ID", SqlDbType.Int);
paramArray[7].Value = prj_ref_id;
paramArray[8] = CreateDataParameter("@Q_SBJ_ID", SqlDbType.NVarChar, 20);
paramArray[8].Value = q_sbj_id;
paramArray[9] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[9].Value = tgt_emp_id;
try
{
return(DataTypeUtility.GetToInt32(DbAgentObj.ExecuteScalar(conn, trx, query, paramArray, CommandType.Text)));
}
catch (Exception ex)
{
throw ex;
}
}
private object NewCode(IDbConnection conn
, IDbTransaction trx
, string table_name
, string column_name)
{
string query = string.Format(@"SELECT MAX({0}) FROM {1}", column_name, table_name);
return(DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text));
}
public string SelectNextDeptID(IDbConnection conn, IDbTransaction trx)
{
object Result;
string query = @"
SELECT MAX(DEPT_REF_ID)+1
FROM COM_DEPT_INFO";
Result = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetString(Result));
}
public int Select_Max_Msg_Key(IDbConnection conn, IDbTransaction trx)
{
string query = @"
SELECT MAX(MSG_KEY)+1
FROM nhitpms.NHIS_SD
";
object Result = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetToInt32(Result));
}
public object GetBudGetSum(int iprj_ref_id)
{
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "SU";
paramArray[1] = CreateDataParameter("@iPRJ_REF_ID", SqlDbType.Int);
paramArray[1].Value = iprj_ref_id;
return(DbAgentObj.ExecuteScalar(GetQueryStringByDb("usp_PRJ_BUDGET", "PKG_PRJ_BUDGET.PROC_BUDGET_SUM"), paramArray, CommandType.StoredProcedure));
}
public int SelectMax_DB(IDbConnection conn
, IDbTransaction trx)
{
string query = @"
SELECT ISNULL(MAX(BIAS_GRP_ID), 0) AS BIAS_GRP_ID
FROM EST_BIAS_GROUP
";
object objMax = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetToInt32(objMax) + 1);
}
/// <summary>
/// GetAddEmpRefID
/// : 추가될 Emp_Ref_ID를 추출한다.
/// </summary>
/// <returns></returns>
public int GetAddEmpRefID()
{
string s_query = @"
SELECT ISNULL(MAX(EMP_REF_ID),0)+1 FROM COM_EMP_INFO
";
string o_query = @"
SELECT NVL(MAX(EMP_REF_ID),0)+1 FROM COM_EMP_INFO
";
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(GetQueryStringByDb(s_query, o_query))));
}
public int Select_Max_Kpi_Ref_Id(IDbConnection conn, IDbTransaction trx)
{
object Result;
string query = @"
SELECT ISNULL(MAX(KPI_REF_ID),1000)+1
FROM BSC_KPI_INFO
";
Result = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetToInt32(Result));
}
public int Count(string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int tgt_dept_id
, int tgt_emp_id
, int seq)
{
string query = @"SELECT COUNT(*) FROM EST_GRADE_CTRL_DETAIL
WHERE EST_ID = @EST_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND ESTTERM_STEP_ID = @ESTTERM_STEP_ID
AND EST_DEPT_ID = @EST_DEPT_ID
AND EST_EMP_ID = @EST_EMP_ID
AND TGT_DEPT_ID = @TGT_DEPT_ID
AND TGT_EMP_ID = @TGT_EMP_ID
AND SEQ = @SEQ";
IDbDataParameter[] paramArray = CreateDataParameters(9);
paramArray[0] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[0].Value = est_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[2].Value = estterm_sub_id;
paramArray[3] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[3].Value = estterm_step_id;
paramArray[4] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[4].Value = est_dept_id;
paramArray[5] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[5].Value = est_emp_id;
paramArray[6] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[6].Value = tgt_dept_id;
paramArray[7] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[7].Value = tgt_emp_id;
paramArray[8] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[8].Value = seq;
try
{
int affectedRow = Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString());
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 문서 및 각 테이블의 주요키 리턴
/// </summary>
/// <param name="psPre"></param>
/// <returns></returns>
public string GetAttachNo(string psPrefix)
{
IDbDataParameter[] paramArray = null;
string strSQL = "SELECT dbo.fn_GetAttachNo(@sPREFIX, GETDATE())";
string strORA = "SELECT fn_GetAttachNo(@sPREFIX) FROM DUAL";
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@sPREFIX", SqlDbType.VarChar);
paramArray[0].Value = psPrefix;
return((string)DbAgentObj.ExecuteScalar(GetQueryStringByDb(strSQL, strORA), paramArray, CommandType.Text));
}
public int SelectMax_DB(IDbConnection conn
, IDbTransaction trx)
{
string query = @"
SELECT ISNULL(MAX(KPI_POOL_VER_ID),0) FROM BSC_KPI_POOL_VER
";
object objMax = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetToInt32(objMax) + 1);
}
public int Count(int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int ctrl_emp_id
, int tgt_dept_id
, int tgt_emp_id
, int ctrl_seq)
{
string query = @"SELECT COUNT(*) FROM EST_CTRL_GRADE_DATA
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID = 0)
AND (ESTTERM_STEP_ID= @ESTTERM_STEP_ID OR @ESTTERM_STEP_ID = 0)
AND (CTRL_EMP_ID = @CTRL_EMP_ID OR @CTRL_EMP_ID = 0)
AND (TGT_DEPT_ID = @TGT_DEPT_ID OR @TGT_DEPT_ID = 0)
AND (TGT_EMP_ID = @TGT_EMP_ID OR @TGT_EMP_ID = 0)
AND (CTRL_SEQ = @CTRL_SEQ OR @CTRL_SEQ = 0)" ;
IDbDataParameter[] paramArray = CreateDataParameters(9);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@CTRL_EMP_ID", SqlDbType.Int);
paramArray[5].Value = ctrl_emp_id;
paramArray[6] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[6].Value = tgt_dept_id;
paramArray[7] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[7].Value = tgt_emp_id;
paramArray[8] = CreateDataParameter("@CTRL_SEQ", SqlDbType.Int);
paramArray[8].Value = ctrl_seq;
try
{
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text).ToString()));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// GetDocNo
/// : 문서번호 리턴
/// </summary>
/// <param name="asPrefix"></param>
/// <returns></returns>
public string GetDocNo(string asPrefix)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT DBO.FN_GETDOCNO(@PREFIX) \n");
paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@PREFIX", SqlDbType.VarChar);
paramArray[0].Value = asPrefix;
return(Convert.ToString(DbAgentObj.ExecuteScalar(sbQuery.ToString(), paramArray)));
}
public int Count(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string rel_grp_pos_data_id
, string rel_grp_id
, string rel_grp_pos_id
, string est_id
, int estterm_ref_id
, string pos_id
, string pos_value)
{
string query = @"SELECT COUNT(*)
FROM EST_REL_GROUP_POS_DATA
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (REL_GRP_POS_DATA_ID = @REL_GRP_POS_DATA_ID OR @REL_GRP_POS_DATA_ID ='' )
AND (REL_GRP_ID = @REL_GRP_ID OR @REL_GRP_ID ='' )
AND (REL_GRP_POS_ID = @REL_GRP_POS_ID OR @REL_GRP_POS_ID ='' )
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (POS_ID = @POS_ID OR @POS_ID ='' )
AND (POS_VALUE = @POS_VALUE OR @POS_VALUE ='' )" ;
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@REL_GRP_POS_DATA_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = rel_grp_pos_data_id;
paramArray[2] = CreateDataParameter("@REL_GRP_ID", SqlDbType.NVarChar, 20);
paramArray[2].Value = rel_grp_id;
paramArray[3] = CreateDataParameter("@REL_GRP_POS_ID", SqlDbType.NVarChar, 20);
paramArray[3].Value = rel_grp_pos_id;
paramArray[4] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[4].Value = est_id;
paramArray[5] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[5].Value = estterm_ref_id;
paramArray[6] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar, 20);
paramArray[6].Value = pos_id;
paramArray[7] = CreateDataParameter("@POS_VALUE", SqlDbType.NVarChar, 20);
paramArray[7].Value = pos_value;
try
{
return(Convert.ToInt32(DbAgentObj.ExecuteScalar(conn, trx, query, paramArray, CommandType.Text).ToString()));
}
catch (Exception ex)
{
throw ex;
}
}
public int Select_NewIdx_Pms_Com_Info(IDbConnection conn
, IDbTransaction trx)
{
string query = @"
SELECT
CASE WHEN MAX(IDX) IS NULL THEN 1
ELSE MAX(IDX)+1 END AS NEW_IDX
FROM PMS_COM_INFO
";
object Result = DbAgentObj.ExecuteScalar(conn, trx, query, null, CommandType.Text);
return(DataTypeUtility.GetToInt32(Result));
}
protected internal float GetAHPEstDeptStgHorizonPoint_Dac(int ver_id
, int estterm_ref_id
, int est_dept_ref_id
, int stg_ref_id
, int target_stg_ref_id)
{
string query = @"SELECT L_R_POINT
FROM (SELECT VER_ID
, ESTTERM_REF_ID
, EST_DEPT_REF_ID
, L_STG_REF_ID
, R_STG_REF_ID
, L_POINT
, S_POINT
, R_POINT
, CREATE_USER
, CREATE_DATE
, UPDATE_USER
, UPDATE_DATE
, CASE WHEN L_STG_REF_ID = @STG_REF_ID THEN L_POINT ELSE R_POINT END AS L_R_POINT
FROM BSC_AHP_EST_DEPT_STG_DATA
WHERE (VER_ID = @VER_ID OR @VER_ID = 0)
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (EST_DEPT_REF_ID = @EST_DEPT_REF_ID OR @EST_DEPT_REF_ID = 0)
AND (L_STG_REF_ID = @STG_REF_ID OR R_STG_REF_ID = @STG_REF_ID)) T
WHERE (L_STG_REF_ID = @T_STG_REF_ID OR R_STG_REF_ID = @T_STG_REF_ID)" ;
IDbDataParameter[] paramArray = CreateDataParameters(5);
paramArray[0] = CreateDataParameter("@VER_ID", SqlDbType.Int);
paramArray[0].Value = ver_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = est_dept_ref_id;
paramArray[3] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[3].Value = stg_ref_id;
paramArray[4] = CreateDataParameter("@T_STG_REF_ID", SqlDbType.Int);
paramArray[4].Value = target_stg_ref_id;
try
{
float affectedRow = Convert.ToSingle(DbAgentObj.ExecuteScalar(query, paramArray, CommandType.Text));
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
