/// <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; }