Example #1
0
        /// <summary>
        /// 可變更的承辦人清單
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public List <TbSchoolData> qryAdminChangeDataByList(TbSchoolData condition)  //只會有USER_ID
        {
            string sql;

            sql = this.getSelectSql("TbSchoolDataSqlProvider", "selectTableAdmin");

            List <TbSchoolData> userList = new List <TbSchoolData>();

            var resultList = QueryTableListBySql(sql, new { USER_ID = condition.USER_ID });

            foreach (var row in resultList)
            {
                TbSchoolData model = new TbSchoolData();
                model.USER_ID      = row.USER_ID; //給admin_change.cshtml使用
                model.ACCOUNT      = row.ACCOUNT;
                model.ACCOUNT_NAME = row.ACCOUNT_NAME;
                model.TITLE        = row.TITLE;
                //model.STATUS = row.STATUS;
                model.EMAIL = row.EMAIL;
                userList.Add(model);
            }

            CommonDataDao commonDataDao = new CommonDataDao();

            commonDataDao.dbConn = this.dbConn;
            IEnumerable <TbSchoolData> model_ie = userList.Select(
                row =>
            {
                string[] title = row.TITLE.Split(',');
                foreach (var item in title)
                {
                    //複選職稱的處理
                    row.TITLE_CHT += commonDataDao.qryCommonName(item, "TITLE") + ",";
                }

                row.TITLE_CHT = row.TITLE_CHT.Substring(0, row.TITLE_CHT.Length - 1);       //去除末位的逗號

                return(row);
            }
                );

            return(model_ie.ToList());
        }
        /// <summary>
        /// 回傳筆數給Grid用
        /// </summary>
        /// <returns></returns>
        public int qryUserDataForGridCount(TbUserData condition = null)
        {
            CommonDataDao dao          = new CommonDataDao();
            string        conditionStr = "where [LOGIN_TYPE] not in (1, 4)";

            if (!string.IsNullOrEmpty(condition.USER_ID))
            {
                conditionStr += " and USER_ID like @USER_ID ";
            }
            if (condition.COUNTY_ID.ToString() != "0")
            {
                conditionStr += " and COUNTY_ID = @COUNTY_ID ";
            }
            if (condition.SCHOOL_SYSTEM_SNO.ToString() != "0")
            {
                conditionStr += " and SCHOOL_SYSTEM_SNO = @SCHOOL_SYSTEM_SNO ";
            }
            if (!string.IsNullOrEmpty(condition.SCHOOL))
            {
                conditionStr += " and SCHOOL like @SCHOOL ";
            }
            if (condition.SCHOOL_TYPE_ARR != null && condition.SCHOOL_TYPE_ARR.Length > 0)
            {
                string schoolTypes = "";
                int    i           = 0;
                foreach (int val in condition.SCHOOL_TYPE_ARR)
                {
                    schoolTypes += "'" + val + "'";
                    if (i < condition.SCHOOL_TYPE_ARR.Length - 1)
                    {
                        schoolTypes += ",";
                    }
                    i++;
                }
                conditionStr += " and SCHOOL_TYPE in (" + schoolTypes + ") ";
            }
            string sql   = dao.getCountSql("USER_ID", "VW_LOGIN_INFO", conditionStr);
            int    count = 0;
            IEnumerable <dynamic> result;

            if (string.IsNullOrEmpty(conditionStr))
            {
                result = dbConn.Query(sql);
            }
            else
            {
                result = dbConn.Query(sql, new
                {
                    USER_ID           = "%" + condition.USER_ID + "%",
                    COUNTY_ID         = condition.COUNTY_ID,
                    SCHOOL_SYSTEM_SNO = condition.SCHOOL_SYSTEM_SNO,
                    SCHOOL            = "%" + condition.SCHOOL + "%"
                });
            }

            foreach (var item in result)
            {
                count = item.COUNT;
            }

            return(count);
        }