public int GetEstDeptRefIDByTop1(int estterm_ref_id, int emp_ref_id)
{
string query = @"
SELECT A.EST_DEPT_REF_ID
FROM EST_DEPT_INFO A
INNER JOIN BSC_EMP_COM_DEPT_DETAIL B
ON A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND A.DEPT_REF_ID = B.DEPT_REF_ID
AND B.EMP_REF_ID = @EMP_REF_ID
ORDER BY A.DEPT_TYPE";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[1].Value = emp_ref_id;
DataSet ds = DbAgentCache.Fill(query, paramArray);
//DataSet ds = DbAgent.Fill(query, paramArray);
if (ds.Tables[0].Rows.Count > 0)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["EST_DEPT_REF_ID"]));
}
return(0);
}
public int GetEstDeptRefIDByTop1(int estterm_ref_id)
{
string query = @"SELECT A.EST_DEPT_REF_ID
FROM (
SELECT
ED.EST_DEPT_REF_ID
,ED.UP_EST_DEPT_ID
,LEVEL as DEPT_LEVEL
FROM
EST_DEPT_INFO ED
WHERE
ESTTERM_REF_ID = @ESTTERM_REF_ID
AND TEMP_FLAG = 1
START WITH EST_DEPT_REF_ID = (SELECT EST_DEPT_REF_ID FROM EST_DEPT_INFO WHERE NVL(UP_EST_DEPT_ID,0) = 0 AND ROWNUM = 1)
CONNECT BY PRIOR EST_DEPT_REF_ID = UP_EST_DEPT_ID
)A
JOIN (SELECT DISTINCT EST_DEPT_REF_ID
FROM BSC_EST_DEPT_ORG_DETAIL
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID) B ON A.EST_DEPT_REF_ID = B.EST_DEPT_REF_ID
ORDER BY DEPT_LEVEL" ;
query = @"
SELECT DISTINCT EST_DEPT_REF_ID
FROM BSC_EST_DEPT_ORG_DETAIL
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
DataSet ds = DbAgentCache.Fill(query, paramArray);
//DataSet ds = DbAgent.Fill(query, paramArray);
if (ds.Tables[0].Rows.Count > 0)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["EST_DEPT_REF_ID"]));
}
return(0);
}
public int GetEstDeptRefIDByTopYN(int estterm_ref_id)
{
string query = @"SELECT DISTINCT EST_DEPT_REF_ID FROM BSC_EST_DEPT_ORG_DETAIL
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_TOP_YN_ORG = 'Y'" ;
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
DataSet ds = DbAgentCache.FillDataSet(query, "EST_ID", null, paramArray, CommandType.Text);
if (ds.Tables[0].Rows.Count > 0)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["EST_DEPT_REF_ID"]));
}
return(0);
}
