/// <summary>
/// 取得活動限制人員清單
/// </summary>
/// <param name="activity_id">活動代號</param>
/// <returns>取得活動限制人員清單</returns>
public List <VO.EmployeeVO> SelectByActivity_id(Guid activity_id)
{
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@activity_id", SqlDbType.UniqueIdentifier);
sqlParams[0].Value = activity_id;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT A.id as keyID,B.ID,B.WORK_ID,B.NATIVE_NAME,B.C_DEPT_NAME,B.C_DEPT_ABBR ");
sb.AppendLine("FROM ActivityGroupLimit A ");
sb.AppendLine("inner join V_ACSM_USER2 B on A.emp_id=B.ID ");
sb.AppendLine("WHERE A.activity_id = @activity_id ");
SqlConnection aconn = MyConn();
SqlDataReader MyDataReader = SqlHelper.ExecuteReader(aconn, CommandType.Text, sb.ToString(), sqlParams);
List <VO.EmployeeVO> myEmployeeVOList = new List <ACMS.VO.EmployeeVO>();
while (MyDataReader.Read())
{
VO.EmployeeVO myEmployeeVO = new ACMS.VO.EmployeeVO();
myEmployeeVO.keyID = (int)MyDataReader["keyID"];
myEmployeeVO.ID = (string)MyDataReader["ID"];
myEmployeeVO.WORK_ID = (string)MyDataReader["WORK_ID"];
myEmployeeVO.NATIVE_NAME = (string)MyDataReader["NATIVE_NAME"];
myEmployeeVO.C_DEPT_ABBR = (string)MyDataReader["C_DEPT_ABBR"];
myEmployeeVO.C_DEPT_NAME = (string)MyDataReader["C_DEPT_NAME"];
myEmployeeVOList.Add(myEmployeeVO);
}
MyDataReader.Close();
aconn.Close();
if (MyDataReader != null)
{
MyDataReader.Dispose();
}
if (aconn != null)
{
aconn.Dispose();
}
return(myEmployeeVOList);
}
/// <summary>
/// 取得活動限制人員清單
/// </summary>
/// <param name="activity_id">活動代號</param>
/// <returns>取得活動限制人員清單</returns>
public List<VO.EmployeeVO> SelectByActivity_id(Guid activity_id)
{
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@activity_id", SqlDbType.UniqueIdentifier);
sqlParams[0].Value = activity_id;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT A.id as keyID,B.ID,B.WORK_ID,B.NATIVE_NAME,B.C_DEPT_NAME,B.C_DEPT_ABBR ");
sb.AppendLine("FROM ActivityGroupLimit A ");
sb.AppendLine("inner join V_ACSM_USER2 B on A.emp_id=B.ID ");
sb.AppendLine("WHERE A.activity_id = @activity_id ");
SqlConnection aconn = MyConn();
SqlDataReader MyDataReader = SqlHelper.ExecuteReader(aconn, CommandType.Text, sb.ToString(), sqlParams);
List<VO.EmployeeVO> myEmployeeVOList = new List<ACMS.VO.EmployeeVO>();
while (MyDataReader.Read())
{
VO.EmployeeVO myEmployeeVO = new ACMS.VO.EmployeeVO();
myEmployeeVO.keyID = (int)MyDataReader["keyID"];
myEmployeeVO.ID = (string)MyDataReader["ID"];
myEmployeeVO.WORK_ID = (string)MyDataReader["WORK_ID"];
myEmployeeVO.NATIVE_NAME = (string)MyDataReader["NATIVE_NAME"];
myEmployeeVO.C_DEPT_ABBR = (string)MyDataReader["C_DEPT_ABBR"];
myEmployeeVO.C_DEPT_NAME = (string)MyDataReader["C_DEPT_NAME"];
myEmployeeVOList.Add(myEmployeeVO);
}
MyDataReader.Close();
aconn.Close();
if (MyDataReader != null) MyDataReader.Dispose();
if (aconn != null) aconn.Dispose();
return myEmployeeVOList;
}
// 7-2 角色人員管理 選取所有在職員工
/// <summary>
/// 取得員工資料
/// </summary>
/// <param name="DEPT_ID">部門名稱</param>
/// <param name="WORK_ID">工號</param>
/// <param name="NATIVE_NAME">中英文名字</param>
/// <param name="UnderDept">包含所屬單位</param>
/// <param name="COMPANY_CODE">公司別代號</param>
/// <returns>取得員工資料</returns>
public List<VO.EmployeeVO> GetEmployeeSelector(string DEPT_ID, string WORK_ID, string NATIVE_NAME,Boolean UnderDept,string COMPANY_CODE)
{
if (DEPT_ID == "請選擇")
{
DEPT_ID = "";
}
SqlParameter[] sqlParams = new SqlParameter[4];
sqlParams[0] = new SqlParameter("@DEPT_ID", SqlDbType.NVarChar, 36);
sqlParams[0].Value = DEPT_ID;
sqlParams[1] = new SqlParameter("@WORK_ID", SqlDbType.NVarChar, 36);
sqlParams[1].Value = WORK_ID;
sqlParams[2] = new SqlParameter("@NATIVE_NAME", SqlDbType.NVarChar, 200);
sqlParams[2].Value = NATIVE_NAME;
sqlParams[3] = new SqlParameter("@COMPANY_CODE", SqlDbType.NVarChar, 200);
sqlParams[3].Value = COMPANY_CODE;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT B.WINDOWS_ID, B.[ID],B.C_DEPT_NAME,B.[C_DEPT_ABBR],B.[WORK_ID],B.[NATIVE_NAME] ");
sb.AppendLine("FROM V_ACSM_USER2 B ");
sb.AppendLine("WHERE status<2 ");//在職員工
if(UnderDept)
{
sb.AppendLine("and ((B.DEPT_ID=@DEPT_ID or B.C_DEPT_NAME like '%'+@DEPT_ID+'%') or @DEPT_ID='') ");
}
else
{
sb.AppendLine("and ((B.DEPT_ID=@DEPT_ID or B.C_DEPT_NAME =@DEPT_ID )or @DEPT_ID='') ");
}
//sb.AppendLine("and (B.DEPT_ID=@DEPT_ID ) ");
sb.AppendLine(" and B.COMPANY_CODE = @COMPANY_CODE");
sb.AppendLine("and (B.WORK_ID like '%'+@WORK_ID+'%' or @WORK_ID='') ");
sb.AppendLine("and (B.ENGLISH_NAME like '%' +@NATIVE_NAME+ '%'or B.NATIVE_NAME like '%'+@NATIVE_NAME+'%' or @NATIVE_NAME='') ");
SqlConnection aconn=MyConn ();
SqlDataReader MyDataReader = SqlHelper.ExecuteReader(aconn, CommandType.Text, sb.ToString(), sqlParams);
List<VO.EmployeeVO> myEmployeeVOList = new List<ACMS.VO.EmployeeVO>();
while (MyDataReader.Read())
{
VO.EmployeeVO myEmployeeVO = new ACMS.VO.EmployeeVO();
myEmployeeVO.ID = (string)MyDataReader["WINDOWS_ID"] + "(" + (string)MyDataReader["NATIVE_NAME"] + ")";
myEmployeeVO.NATIVE_NAME = (string)MyDataReader["NATIVE_NAME"];
myEmployeeVO.WORK_ID = (string)MyDataReader["WORK_ID"];
myEmployeeVO.C_DEPT_NAME = (string)MyDataReader["C_DEPT_NAME"];
myEmployeeVO.C_DEPT_ABBR = (string)MyDataReader["C_DEPT_ABBR"];
myEmployeeVOList.Add(myEmployeeVO);
}
MyDataReader.Close();
aconn.Close();
if (MyDataReader != null) MyDataReader.Dispose();
if (aconn != null) aconn.Dispose();
return myEmployeeVOList;
}
//2-3個人報名-開啟代理報名選單 或 開啟選擇隊員-列出可加入此活動的隊員
/// <summary>
/// 個人報名-開啟代理報名選單 或 開啟選擇隊員-列出可加入此活動的隊員
/// </summary>
/// <param name="DEPT_ID">部門名稱</param>
/// <param name="WORK_ID">工號</param>
/// <param name="NATIVE_NAME">中英文姓名</param>
/// <param name="activity_id">活動代號</param>
/// <param name="activity_type">活動類別</param>
/// <param name="UnderDept">包含所屬單位</param>
/// <param name="Company_ID">公司別代號</param>
/// <returns>個人報名-開啟代理報名選單 或 開啟選擇隊員-列出可加入此活動的隊員</returns>
public List<VO.EmployeeVO> RegistableMember(string DEPT_ID, string WORK_ID, string NATIVE_NAME, string activity_id,string activity_type,Boolean UnderDept,string Company_ID )
{
if (string.IsNullOrEmpty(activity_id))
{
return null;
}
if (DEPT_ID == "請選擇")
{
DEPT_ID = "";
}
string tablename = (activity_type == "1" ? "ActivityRegist" : "ActivityTeamMember");
SqlParameter[] sqlParams = new SqlParameter[5];
sqlParams[0] = new SqlParameter("@DEPT_ID", SqlDbType.NVarChar, 36);
sqlParams[0].Value = DEPT_ID;
sqlParams[1] = new SqlParameter("@WORK_ID", SqlDbType.NVarChar, 36);
sqlParams[1].Value = WORK_ID;
sqlParams[2] = new SqlParameter("@NATIVE_NAME", SqlDbType.NVarChar, 200);
sqlParams[2].Value = NATIVE_NAME;
sqlParams[3] = new SqlParameter("@activity_id", SqlDbType.UniqueIdentifier);
sqlParams[3].Value = new Guid(activity_id);
sqlParams[4] = new SqlParameter("@Company_ID", SqlDbType.NVarChar,100);
sqlParams[4].Value = Company_ID;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT A.[ID],A.[C_DEPT_NAME],A.[C_DEPT_ABBR],A.[WORK_ID],A.[NATIVE_NAME],CASE WHEN B.emp_id is null THEN 'true' ELSE 'false' END as theEnable ");
sb.AppendLine("FROM V_ACSM_USER2 A ");
sb.AppendLine(string.Format("left join (SELECT emp_id FROM {0} WHERE activity_id=@activity_id) B on A.ID = B.emp_id ",tablename));//已報名過不可再選
sb.AppendLine("WHERE A.ID in ");
sb.AppendLine("( ");
sb.AppendLine("SELECT CASE WHEN AA.is_grouplimit='Y' THEN BB.emp_id ELSE A.ID END ");
sb.AppendLine("FROM Activity AA ");
sb.AppendLine("left join ( select distinct activity_id, emp_id from ActivityGroupLimit where activity_id =@activity_id union select distinct activity_id, B.id as emp_id from ActivityLimitCompany A inner join V_ACSM_USER2 B on A.COMPANY_CODE =B.COMPANY_CODE where A.activity_id=@activity_id) BB on AA.id=BB.activity_id ");//區分是否有族群限制
sb.AppendLine("WHERE AA.active='Y' ");
sb.AppendLine("and AA.id=@activity_id ");
sb.AppendLine(") ");
sb.AppendLine("and A.status <2 ");//不為離職或留職停薪
// sb.AppendLine("and (A.DEPT_ID=@DEPT_ID or @DEPT_ID='') ");
if (UnderDept)
{
sb.AppendLine("and (A.DEPT_ID=@DEPT_ID or A.C_DEPT_NAME like '%'+@DEPT_ID +'%' or @DEPT_ID='') ");
}
else
{
sb.AppendLine("and (A.DEPT_ID=@DEPT_ID or A.C_DEPT_NAME=@DEPT_ID or @DEPT_ID='') ");
}
sb.AppendLine("and (A.WORK_ID like '%'+@WORK_ID+'%' or @WORK_ID='') ");
sb.AppendLine("and (A.ENGLISH_NAME like '%'+@NATIVE_NAME+'%' or A.NATIVE_NAME like '%'+@NATIVE_NAME+'%' or @NATIVE_NAME='') ");
sb.AppendLine(" and A.COMPANY_CODE like '%'+@Company_ID+'%'");
sb.AppendLine(" and A.Status='1'");
SqlConnection aconn = MyConn();
SqlDataReader MyDataReader = SqlHelper.ExecuteReader(aconn, CommandType.Text, sb.ToString(), sqlParams);
List<VO.EmployeeVO> myEmployeeVOList = new List<ACMS.VO.EmployeeVO>();
while (MyDataReader.Read())
{
VO.EmployeeVO myEmployeeVO = new ACMS.VO.EmployeeVO();
myEmployeeVO.ID = (string)MyDataReader["ID"];
myEmployeeVO.NATIVE_NAME = (string)MyDataReader["NATIVE_NAME"];
myEmployeeVO.WORK_ID = (string)MyDataReader["WORK_ID"];
myEmployeeVO.C_DEPT_ABBR = (string)MyDataReader["C_DEPT_ABBR"];
myEmployeeVO.C_DEPT_NAME = (string)MyDataReader["C_DEPT_NAME"];
myEmployeeVO.keyValue = Convert.ToBoolean(MyDataReader["theEnable"]);
myEmployeeVOList.Add(myEmployeeVO);
}
MyDataReader.Close();
aconn.Close();
if (MyDataReader!=null ) MyDataReader.Dispose();
if (aconn != null) aconn.Dispose();
return myEmployeeVOList;
}
//6-1 新增修改活動 族群限定 選取人員的GridView資料來源
/// <summary>
/// 新增修改活動 族群限定 選取人員的GridView資料來源
/// </summary>
/// <param name="DEPT_ID">部門名稱</param>
/// <param name="JOB_GRADE_GROUP">級職</param>
/// <param name="WORK_ID">工號</param>
/// <param name="NATIVE_NAME">中英文名字</param>
/// <param name="SEX">性別</param>
/// <param name="BIRTHDAY_S">生日開始</param>
/// <param name="BIRTHDAY_E">生日結束</param>
/// <param name="EXPERIENCE_START_DATE">年資起算日</param>
/// <param name="C_NAME">公司名稱</param>
/// <param name="activity_id">活動代號</param>
/// <param name="UnderDept">包含所屬單位</param>
/// <param name="COMPANY_CODE">公司別代號</param>
/// <returns>新增修改活動 族群限定 選取人員的GridView資料來源</returns>
public List<VO.EmployeeVO> EmployeeSelector(string DEPT_ID, string JOB_GRADE_GROUP, string WORK_ID, string NATIVE_NAME, string SEX, string BIRTHDAY_S, string BIRTHDAY_E, string EXPERIENCE_START_DATE, string C_NAME, Guid activity_id, Boolean UnderDept,string COMPANY_CODE)
{
SqlParameter[] sqlParams = new SqlParameter[11];
if (DEPT_ID == "請選擇")
{
DEPT_ID = "";
}
sqlParams[0] = new SqlParameter("@DEPT_ID", SqlDbType.NVarChar, 36);
sqlParams[0].Value = DEPT_ID;
sqlParams[1] = new SqlParameter("@JOB_GRADE_GROUP", SqlDbType.Int);
if (JOB_GRADE_GROUP == "")
{
sqlParams[1].Value = 0;
}
else
{
sqlParams[1].Value = JOB_GRADE_GROUP;
}
sqlParams[2] = new SqlParameter("@WORK_ID", SqlDbType.NVarChar, 36);
sqlParams[2].Value = WORK_ID;
sqlParams[3] = new SqlParameter("@NATIVE_NAME", SqlDbType.NVarChar, 200);
sqlParams[3].Value = NATIVE_NAME;
sqlParams[4] = new SqlParameter("@SEX", SqlDbType.NVarChar, 2);
sqlParams[4].Value = SEX;
sqlParams[5] = new SqlParameter("@BIRTHDAY_S", SqlDbType.NVarChar, 50);
sqlParams[5].Value = BIRTHDAY_S;
sqlParams[6] = new SqlParameter("@BIRTHDAY_E", SqlDbType.NVarChar, 50);
sqlParams[6].Value = BIRTHDAY_E;
sqlParams[7] = new SqlParameter("@EXPERIENCE_START_DATE", SqlDbType.NVarChar, 50);
sqlParams[7].Value = EXPERIENCE_START_DATE;
sqlParams[8] = new SqlParameter("@C_NAME", SqlDbType.NVarChar, 120);
sqlParams[8].Value = C_NAME;
sqlParams[9] = new SqlParameter("@activity_id", SqlDbType.UniqueIdentifier);
sqlParams[9].Value = activity_id;
sqlParams[10] = new SqlParameter("@COMPANY_CODE", SqlDbType.NVarChar,200);
sqlParams[10].Value = COMPANY_CODE;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT CASE WHEN B.emp_id is null THEN 'false' ELSE 'true' END as YN, A.[ID],A.[NATIVE_NAME],A.[ENGLISH_NAME],A.[WORK_ID],A.[OFFICE_MAIL],A.[DEPT_ID],A.[C_DEPT_NAME],A.[C_DEPT_ABBR],A.[OFFICE_PHONE],A.[EXPERIENCE_START_DATE],A.[BIRTHDAY],A.[SEX],A.[JOB_CNAME],A.[STATUS],A.[WORK_END_DATE],A.[COMPANY_CODE],A.[C_NAME] ");
sb.AppendLine("FROM V_ACSM_USER2 A ");
sb.AppendLine("left join (SELECT * FROM ActivityGroupLimit WHERE activity_id=@activity_id) B on A.ID=B.emp_id ");
sb.AppendLine("WHERE A.STATUS<2 ");
//sb.AppendLine("and (A.DEPT_ID =@DEPT_ID or @DEPT_ID='') ");
if (UnderDept)
{
sb.AppendLine("and (A.C_DEPT_NAME like '%'+ @DEPT_ID+'%' or @DEPT_ID='' ) ");
}
else
{
sb.AppendLine("and (A.C_DEPT_NAME = @DEPT_ID or @DEPT_ID='' ) ");
}
sb.AppendLine(" and A.COMPANY_CODE=@COMPANY_CODE");
sb.AppendLine("and (A.JOB_GRADE_GROUP >= @JOB_GRADE_GROUP or @JOB_GRADE_GROUP=0) ");
sb.AppendLine("and (A.WORK_ID like '%'+@WORK_ID+'%' or @WORK_ID='') ");
sb.AppendLine("and (A.ENGLISH_NAME like '%'+@NATIVE_NAME+'%' or A.NATIVE_NAME like '%'+@NATIVE_NAME+'%' or @NATIVE_NAME='') ");
if (SEX == "0")
{
sb.AppendLine(" and (A.SEX= @SEX or A.SEX='F' or @SEX='')");
}
else
{
sb.AppendLine(" and (A.SEX= @SEX or A.SEX='M' or @SEX='')");
}
sb.AppendLine("and (A.BIRTHDAY >= @BIRTHDAY_S or @BIRTHDAY_S='') ");
sb.AppendLine("and (A.BIRTHDAY <= @BIRTHDAY_E or @BIRTHDAY_E='') ");
sb.AppendLine("and (A.EXPERIENCE_START_DATE >=@EXPERIENCE_START_DATE or @EXPERIENCE_START_DATE='') ");
sb.AppendLine("and (A.C_NAME = @C_NAME or @C_NAME='') ");
SqlConnection aconn = MyConn();
SqlDataReader MyDataReader = SqlHelper.ExecuteReader(aconn, CommandType.Text, sb.ToString(), sqlParams);
List<VO.EmployeeVO> myEmployeeVOList = new List<ACMS.VO.EmployeeVO>();
while (MyDataReader.Read())
{
VO.EmployeeVO myEmployeeVO = new ACMS.VO.EmployeeVO();
myEmployeeVO.keyValue = Convert.ToBoolean(MyDataReader["YN"]);
myEmployeeVO.ID = (string)MyDataReader["ID"];
myEmployeeVO.NATIVE_NAME = (string)MyDataReader["NATIVE_NAME"];
myEmployeeVO.ENGLISH_NAME = (string)MyDataReader["ENGLISH_NAME"];
myEmployeeVO.WORK_ID = (string)MyDataReader["WORK_ID"];
myEmployeeVO.OFFICE_MAIL = (string)MyDataReader["OFFICE_MAIL"];
myEmployeeVO.DEPT_ID = (string)MyDataReader["DEPT_ID"];
myEmployeeVO.C_DEPT_NAME = (string)MyDataReader["C_DEPT_NAME"];
myEmployeeVO.C_DEPT_ABBR = (string)MyDataReader["C_DEPT_ABBR"];
myEmployeeVO.OFFICE_PHONE = (string)MyDataReader["OFFICE_PHONE"];
myEmployeeVO.EXPERIENCE_START_DATE = (DateTime?)(MyDataReader["EXPERIENCE_START_DATE"] == DBNull.Value ? null : MyDataReader["EXPERIENCE_START_DATE"]);
myEmployeeVO.BIRTHDAY = (string)MyDataReader["BIRTHDAY"];
myEmployeeVO.SEX = (string)MyDataReader["SEX"];
myEmployeeVO.JOB_CNAME = (string)MyDataReader["JOB_CNAME"];
myEmployeeVO.STATUS = (string)MyDataReader["STATUS"];
myEmployeeVO.WORK_END_DATE = (DateTime?)(MyDataReader["WORK_END_DATE"] == DBNull.Value ? null : MyDataReader["WORK_END_DATE"]);
myEmployeeVO.COMPANY_CODE = (string)MyDataReader["COMPANY_CODE"];
myEmployeeVO.C_NAME = (string)MyDataReader["C_NAME"];
myEmployeeVOList.Add(myEmployeeVO);
}
MyDataReader.Close();
aconn.Close();
return myEmployeeVOList;
}
