/// <summary> /// 新聞公告 聲明 /// </summary> /// <param name="qry"></param> /// <param name="langCode"></param> /// <param name="model"></param> private void SearchAnnouncement(string qry, string langCode, ref List <SearchListDataModel> model) { using (var db = new TCGDB(_connectionString)) { var source = db.NEWS .AsEnumerable() .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && (s.N_TITLE == qry || s.N_DESC.RemoveHtmlAllTags() == qry || s.N_TITLE.Contains(qry) || s.N_DESC.RemoveHtmlAllTags().Contains(qry))) .Where(x => x.STATUS == "Y") .ToList(); foreach (var news in source) { SearchListDataModel temp = new SearchListDataModel(); temp.Title = news.N_TITLE; temp.Content = news.N_DESC.RemoveHtmlAllTags(); temp.UpDateTime = (DateTime)news.UPD_DT; temp.LinkAddr = string.Format("/News/AnnouncementContent?ID={0}&typeID={1}", news.ID, news.CATE_ID); temp.BD_DTString = (DateTime)news.BD_DT; temp.Sort = (news.SORT) ?? 0; model.Add(temp); } } }
/// <summary> /// 加入台灣民政府 /// </summary> /// <param name="qry"></param> /// <param name="langCode"></param> /// <param name="model"></param> private void SearchJoinUs(string qry, string langCode, ref List <SearchListDataModel> model) { using (var db = new TCGDB(_connectionString)) { Dictionary <int, List <int> > cateGroup = new Dictionary <int, List <int> >(); cateGroup.Add(0, new List <int>() { 1, 4, 5 }); cateGroup.Add(1, new List <int>() { 2, 6, 7 }); var source = db.JOINUS .AsEnumerable() .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && (s.C_TITLE == qry || s.C_DESC.RemoveHtmlAllTags() == qry || s.C_TITLE.Contains(qry) || s.C_DESC.RemoveHtmlAllTags().Contains(qry))) .Where(x => x.STATUS == "Y") .ToList(); foreach (var join in source) { var cate = cateGroup.Where(s => s.Value.Contains((int)join.CATE_ID)).ToDictionary(d => d.Key, d => d.Value.ToList()); if (cate.Count == 0) { throw new Exception("無法取得對應的JOINUS分類,請聯絡系統管理員"); } SearchListDataModel temp = new SearchListDataModel(); temp.Title = join.C_TITLE; temp.Content = join.C_DESC.RemoveHtmlAllTags(); temp.UpDateTime = (DateTime)join.UPD_DT; temp.BD_DTString = (DateTime)join.BD_DT; temp.Sort = (join.SORT) ?? 0; switch (cate.Keys.First()) { case 0: temp.LinkAddr = string.Format("/JoinUs/Apply"); break; case 1: temp.LinkAddr = string.Format("/JoinUs/Consult?id={0}", join.ID); break; default: break; } model.Add(temp); } } }
/// <summary> /// 取一個州別的直播列表 /// </summary> /// <param name="ID"></param> /// <returns></returns> public string GetStateVideo(int statesID) { string url = string.Empty; using (var db = new TCGDB(_connectionString)) { url = db.STATES_VIDEO .Where(s => s.STATUS == "Y" && s.CATE_ID == statesID) .Select(s => s.C_URL) .FirstOrDefault(); } return(url); }
/// <summary> /// 文章代表圖 /// </summary> /// <param name="id"></param> /// <returns></returns> private string GetMainImg(int id) { string imgStr = string.Empty; using (var db = new TCGDB(_connectionString)) { imgStr = db.IMG .Where(s => s.STATUS == "Y" && s.IMG_KIND == "Activity" && s.IMG_NO == (id.ToString())) .Select(s => s.IMG_FILE) .FirstOrDefault(); } return(imgStr); }
/// <summary> /// 列表 /// </summary> /// <param name="filter"></param> /// <returns></returns> public EventStatesResult GetList(int statesTypeID, EventStatesListFilter filter, int?costomPageSize = null, string isIndex = null) { EventStatesResult result = new EventStatesResult(); List <EventStatesData> data = new List <EventStatesData>(); using (var db = new TCGDB(_connectionString)) { try { var source = db.STATES .AsEnumerable() .Where(s => (string.IsNullOrEmpty(filter.LangCode) ? true : s.LANG_ID == filter.LangCode) && s.STATUS == "Y" && s.CATE_ID == statesTypeID && (string.IsNullOrEmpty(isIndex) ? true : s.IS_INDEX == isIndex)) .OrderByDescending(o => o.SORT) //排序大到小、發布日期新到舊、建檔日期新到舊 .ThenByDescending(s => s.C_DATE) .ThenByDescending(d => d.BD_DT) .ToList(); foreach (var item in source) { EventStatesData temp = new EventStatesData() { ID = item.ID, Title = item.C_TITLE, Img = GetMainImg(item.ID), PagingList = GetPagingListByID(item.ID), PublishDateString = item.C_DATE.Value.ToString("yyyy-MM-dd"), Sort = item.SORT.Value, BD_DTString = item.BD_DT.Value.ToString("yyyy-MM-dd"), }; temp.Remark = GetFirstPagingRemark(temp.PagingList); data.Add(temp); } result.StatesTypeID = statesTypeID; result.Url = GetStateVideo(statesTypeID); result.Data = data; result = this.ListPagination(ref result, filter.CurrentPage, costomPageSize ?? Convert.ToInt32(PublicMethodRepository.GetConfigAppSetting("DefaultPageSize"))); } catch (Exception ex) { throw ex; } } return(result); }
/// <summary> /// 取分類 根據ID /// </summary> /// <param name="id"></param> /// <param name="langCode"></param> /// <returns></returns> public Dictionary <int, string> GetNewsCateByID(int id, string langCode) { Dictionary <int, string> source = new Dictionary <int, string>(); using (var db = new TCGDB(_connectionString)) { source = GetNewsCate(langCode) .Where(s => s.Key == id) .ToDictionary(d => d.Key, d => d.Value); if (source.Count == 0) { throw new Exception("無法取得新聞分類"); } } return(source); }
/// <summary> /// 取得分頁的網址列表 /// </summary> /// <param name="id"></param> /// <returns></returns> private List <string> GetPagingUrlListByID(int id) { List <string> data = new List <string>(); using (var db = new TCGDB(_connectionString)) { data = db.URL .Where(s => s.STATUS == "Y" && s.URL_KIND == "Activity_Detail" && s.URL_NO.Contains(id.ToString())) .OrderByDescending(s => s.SORT) .Select(s => s.C_URL) .ToList(); } return(data); }
/// <summary> /// 全德各州所有分類 /// </summary> /// <param name="langCode"></param> /// <returns></returns> public Dictionary <int, string> GetEducationCate(string langCode) { Dictionary <int, string> cate = new Dictionary <int, string>(); using (var db = new TCGDB(_connectionString)) { cate = db.EDU_CATE .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && s.STATUS == "Y") .OrderByDescending(x => x.SORT) .ThenByDescending(y => y.BD_DT) .ToDictionary(d => d.ID, d => d.CATE_NAME); //if (cate.Count == 0) // throw new Exception("無法取得教育分類"); } return(cate); }
/// <summary> /// 取分類 /// </summary> /// <param name="langCode"></param> /// <returns></returns> public Dictionary <int, string> GetNewsCate(string langCode) { Dictionary <int, string> cate = new Dictionary <int, string>(); using (var db = new TCGDB(_connectionString)) { cate = db.NEWS_CATE .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && s.STATUS == "Y") .ToDictionary(d => d.ID, d => d.CATE_NAME); if (cate.Count == 0) { throw new Exception("無法取得新聞分類"); } } return(cate); }
/// <summary> /// 列表 /// </summary> /// <param name="filter"></param> /// <returns></returns> public AnnouncementLatestResult GetList(AnnouncementLatestFilter filter, int?costomPageSize = null, string isIndex = null) { AnnouncementLatestResult result = new AnnouncementLatestResult(); List <AnnouncementLatestData> data = new List <AnnouncementLatestData>(); using (var db = new TCGDB(_connectionString)) { try { var source = db.NEWS .AsEnumerable() .Where(s => (string.IsNullOrEmpty(filter.LangCode) ? true : s.LANG_ID == filter.LangCode) && s.STATUS == "Y" && (filter.TypeID == null ? true : s.CATE_ID == (int)filter.TypeID) && (string.IsNullOrEmpty(isIndex) ? true : s.IS_INDEX == isIndex)) .OrderByDescending(o => o.SORT) //排序大到小、發布日期新到舊、建檔日期新到舊 .ThenByDescending(s => s.N_DATE) .ThenByDescending(d => d.BD_DT) .ToList(); foreach (var item in source) { AnnouncementLatestData temp = new AnnouncementLatestData() { ID = item.ID, Title = item.N_TITLE, Img = GetMainImg(item.ID), CateIDInfo = GetNewsCateByID((int)item.CATE_ID, filter.LangCode), PublishDateString = item.N_DATE.Value.ToString("yyyy-MM-dd"), Content = item.N_DESC.RemoveHtmlAllTags(), BD_DTString = item.BD_DT.Value.ToString("yyyy-MM-dd"), Sort = item.SORT.Value, }; data.Add(temp); } result.Data = data; result = this.ListPagination(ref result, filter.CurrentPage, costomPageSize ?? Convert.ToInt32(PublicMethodRepository.GetConfigAppSetting("DefaultPageSize"))); } catch (Exception ex) { throw ex; } } return(result); }
/// <summary> /// 全德各州所有分類 /// </summary> /// <param name="langCode"></param> /// <returns></returns> public Dictionary <int, string> GetStatesCate(string langCode) { Dictionary <int, string> cate = new Dictionary <int, string>(); using (var db = new TCGDB(_connectionString)) { cate = db.STATES_CATE .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && s.STATUS == "Y") .OrderByDescending(o => o.SORT) //排序大到小、建檔日期新到舊 .ThenByDescending(d => d.BD_DT) .ToDictionary(d => d.ID, d => d.CATE_NAME); //if (cate.Count == 0) // throw new Exception("無法取得洲別分類"); } return(cate); }
/// <summary> /// 取得分頁的圖片列表 /// </summary> /// <param name="id"></param> /// <returns></returns> private List <PagingImageInfo> GetPaginImgsListByID(int id) { List <PagingImageInfo> data = new List <PagingImageInfo>(); using (var db = new TCGDB(_connectionString)) { data = db.IMG .Where(s => s.STATUS == "Y" && s.IMG_STY == "B" && s.IMG_KIND == "Focus_Detail" && s.IMG_NO == id.ToString()) .OrderByDescending(s => s.SORT) .Select(s => new PagingImageInfo() { ImgFileName = s.IMG_FILE, ImgDescription = s.IMG_DESC }) .ToList(); } return(data); }
/// <summary> /// 教育專欄 /// </summary> /// <param name="qry"></param> /// <param name="langCode"></param> /// <param name="model"></param> private void SearchEducation(string qry, string langCode, ref List <SearchListDataModel> model) { using (var db = new TCGDB(_connectionString)) { var query = db.EDU .Join(db.EDU_DETAIL, m => m.ID.ToString(), d => d.CATE_ID, (main, details) => new { Main = main, Details = details }) .AsEnumerable() .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.Main.LANG_ID == langCode) && (s.Main.STATUS == "Y" && s.Main.C_TITLE == (qry) || s.Main.C_DESC.RemoveHtmlAllTags() == qry || s.Main.C_TITLE.Contains(qry) || s.Main.C_DESC.RemoveHtmlAllTags().Contains(qry) || s.Details.C_TITLE == (qry) || s.Details.C_DESC.RemoveHtmlAllTags() == qry || s.Details.C_TITLE.Contains(qry) || s.Details.C_DESC.RemoveHtmlAllTags().Contains(qry))) .Where(w => w.Details.STATUS == "Y") .Where(x => x.Main.STATUS == "Y") .OrderByDescending(d => d.Details.UPD_DT) .GroupBy(g => g.Main.C_TITLE) .ToList() .Select(o => new SearchListDataModel() { ID = o.First().Details.ID, Title = o.First().Main.C_TITLE, Type = o.First().Main.ID, Content = o.First().Details.C_DESC.RemoveHtmlAllTags(), UpDateTime = (DateTime)o.First().Details.UPD_DT, LinkAddr = string.Format("/AboutUs/EducationContent?eduTypeID={0}&ID={1}&pagingID={2}", o.First().Main.CATE_ID, o.First().Details.CATE_ID, o.First().Details.ID), BD_DTString = (DateTime)o.First().Details.BD_DT, Sort = o.First().Main.SORT ?? 0, }) .ToList(); if (query.Count > 0) { model.AddRange(query); } } }
/// <summary> /// 列表 /// </summary> /// <param name="filter"></param> /// <returns></returns> public EducationResult GetList(int eduTypeID, EducationListFilter filter, int?coustomPageSize = null, string isIndex = null) { EducationResult result = new EducationResult(); List <EducationData> data = new List <EducationData>(); using (var db = new TCGDB(_connectionString)) { try { var source = db.EDU .AsEnumerable() .Where(s => (string.IsNullOrEmpty(filter.LangCode) ? true : s.LANG_ID == filter.LangCode) && s.STATUS == "Y" && s.CATE_ID == eduTypeID) .OrderByDescending(o => o.SORT) //排序大到小、發布日期新到舊、資料建檔日期新到舊 .ThenByDescending(s => s.C_DATE) .ThenByDescending(d => d.BD_DT) .ToList(); foreach (var item in source) { EducationData temp = new EducationData() { ID = item.ID, Title = item.C_TITLE, PagingList = GetPagingListByID(item.ID), PublishDateString = item.C_DATE.Value.ToString("yyyy-MM-dd"), }; temp.Remark = GetFirstPagingRemark(temp.PagingList); data.Add(temp); } result.Data = data; result = this.ListPagination(ref result, filter.CurrentPage, coustomPageSize ?? Convert.ToInt32(PublicMethodRepository.GetConfigAppSetting("DefaultPageSize"))); } catch (Exception ex) { throw ex; } } result.EduTypeInfo = GetEduCateByID(eduTypeID, filter.LangCode); return(result); }
/// <summary> /// 取得分頁 /// </summary> /// <param name="id"></param> /// <returns></returns> private List <EvnentPaging> GetPagingListByID(int id) { List <EvnentPaging> data = new List <EvnentPaging>(); using (var db = new TCGDB(_connectionString)) { data = db.EDU_DETAIL .AsEnumerable() .Where(o => o.CATE_ID == id.ToString() && o.STATUS == "Y") .OrderByDescending(o => o.SORT) .Select(s => new EvnentPaging() { ID = s.ID, Title = s.C_TITLE, Description = s.C_DESC, //ImagesList = GetPaginImgsListByID(s.ID), //InternetSiteList = GetPagingUrlListByID(s.ID) }) .ToList(); //語系join // data = db.ACTIVITY_DETAIL // .Join(db.LANG, // t1 => t1.LANG_ID, // t2 => t2.LANG_ID, //(details, lang) => new { Details = details, Lang = lang }) //.AsEnumerable() //.Where(o => o.Details.CATE_ID == id.ToString()) //.OrderBy(o => o.Details.SORT) //.Select(s => new EnentPaging() //{ // Title = s.Details.C_TITLE, // Description = s.Details.C_DESC, // ImagesList = GetPagingImgOrUrlListByID(s.Details.ID, DetailsContentKind.img), // InternetSiteList = GetPagingImgOrUrlListByID(s.Details.ID, DetailsContentKind.url) //}) //.ToList(); } return(data); }
/// <summary> /// 內容 /// </summary> /// <param name="id"></param> /// <param name="lagCode"></param> /// <returns></returns> public EventStatesContent GetContentByID(int statesTypeID, int id, string lagCode) { EventStatesContent result = new EventStatesContent(); using (var db = new TCGDB(_connectionString)) { var sourceList = db.STATES .AsEnumerable() .Where(s => (string.IsNullOrEmpty(lagCode) ? true : s.LANG_ID == lagCode) && s.STATUS == "Y" && s.CATE_ID == statesTypeID) .OrderByDescending(o => o.SORT) //排序大到小、發布日期新到舊、建檔日期新到舊 .ThenByDescending(s => s.C_DATE) .ThenByDescending(x => x.BD_DT) .ToList(); var source = sourceList.Where(s => s.ID == id).FirstOrDefault(); if (source == null) { throw new Exception("無法取得活動內容,是否已被移除."); } result.Data = new EventStatesData() { ID = source.ID, Title = source.C_TITLE, Img = GetMainImg(source.ID), PagingList = GetPagingListByID(source.ID), PublishDateString = source.C_DATE.Value.ToString("yyyy-MM-dd"), }; result.Data.Remark = GetFirstPagingRemark(result.Data.PagingList); int dataIndex = sourceList.IndexOf(source); int lastDataIndex = sourceList.Count - 1; result.PreviousIDStr = dataIndex == 0 ? "" : sourceList[(dataIndex - 1)].ID.ToString(); result.NextIDStr = dataIndex == lastDataIndex ? "" : sourceList[(dataIndex + 1)].ID.ToString(); } result.StatesCateInfo = GetStatesCateByID(statesTypeID, lagCode); return(result); }
/// <summary> /// 內容 /// </summary> /// <param name="id"></param> /// <param name="lagCode"></param> /// <returns></returns> public EventContent GetContentByID(int id, string lagCode) { EventContent result = new EventContent(); using (var db = new TCGDB(_connectionString)) { var sourceList = db.ACTIVITY .AsEnumerable() .Where(s => (string.IsNullOrEmpty(lagCode) ? true : s.LANG_ID == lagCode) && s.STATUS != "D") .OrderByDescending(o => o.SORT) .OrderByDescending(s => s.C_DATE) .ToList(); var source = sourceList.Where(s => s.ID == id).FirstOrDefault(); if (source == null) { throw new Exception("無法取得活動內容,是否已被移除."); } result.Data = new EventLatestData() { ID = source.ID, Title = source.C_TITLE, Img = GetMainImg(source.ID), PagingList = GetPagingListByID(source.ID), PublishDateString = source.C_DATE.Value.ToString("yyyy-MM-dd"), Remark = source.C_DESC }; //result.Data.Remark = GetFirstPagingRemark(result.Data.PagingList); int dataIndex = sourceList.IndexOf(source); int lastDataIndex = sourceList.Count - 1; result.PreviousIDStr = dataIndex == 0 ? "" : sourceList[(dataIndex - 1)].ID.ToString(); result.NextIDStr = dataIndex == lastDataIndex ? "" : sourceList[(dataIndex + 1)].ID.ToString(); } return(result); }
/// <summary> /// 內容 /// </summary> /// <param name="id"></param> /// <param name="langCode"></param> /// <returns></returns> public AnnouncementLatestContent GetContentByID(int id, int typeID, string langCode) { AnnouncementLatestContent result = new AnnouncementLatestContent(); using (var db = new TCGDB(_connectionString)) { var sourceList = db.NEWS .AsEnumerable() .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && s.STATUS == "Y" && s.CATE_ID == typeID) .OrderByDescending(o => o.SORT) .OrderByDescending(s => s.N_DATE) .ToList(); var source = sourceList.Where(s => s.ID == id).FirstOrDefault(); if (source == null) { throw new Exception("無法取得新聞內容,是否已被移除."); } result.Data = new AnnouncementLatestData() { ID = source.ID, Title = source.N_TITLE, Img = GetMainImg(source.ID), PublishDateString = source.N_DATE.Value.ToString("yyyy-MM-dd"), CateIDInfo = GetNewsCateByID((int)source.CATE_ID, langCode), Content = source.N_DESC }; int dataIndex = sourceList.IndexOf(source); int lastDataIndex = sourceList.Count - 1; result.PreviousIDStr = dataIndex == 0 ? "" : sourceList[(dataIndex - 1)].ID.ToString(); result.NextIDStr = dataIndex == lastDataIndex ? "" : sourceList[(dataIndex + 1)].ID.ToString(); } return(result); }
/// <summary> /// 關於我們 /// </summary> /// <param name="qry"></param> /// <param name="langCode"></param> /// <param name="model"></param> private void SearchAbout(string qry, string langCode, ref List <SearchListDataModel> model) { using (var db = new TCGDB(_connectionString)) { Dictionary <int, List <int> > cateGroup = new Dictionary <int, List <int> >(); cateGroup.Add(0, new List <int>() { 1, 10, 11 }); cateGroup.Add(1, new List <int>() { 2, 12, 13 }); cateGroup.Add(2, new List <int>() { 3, 14, 15 }); cateGroup.Add(3, new List <int>() { 4, 16, 17 }); cateGroup.Add(4, new List <int>() { 5, 18, 19 }); cateGroup.Add(5, new List <int>() { 6, 20, 21 }); cateGroup.Add(6, new List <int>() { 7, 22, 23 }); cateGroup.Add(7, new List <int>() { 8, 24, 25 }); cateGroup.Add(8, new List <int>() { 9, 26, 27 }); var source = db.ABOUTUS .AsEnumerable() .Where(s => (string.IsNullOrEmpty(langCode) ? true : s.LANG_ID == langCode) && (s.C_TITLE == qry || s.C_DESC.RemoveHtmlAllTags() == qry || s.C_TITLE.Contains(qry) || s.C_DESC.RemoveHtmlAllTags().Contains(qry))) .Where(x => x.STATUS == "Y") .ToList(); foreach (var about in source) { var cate = cateGroup.Where(s => s.Value.Contains((int)about.CATE_ID)).ToDictionary(d => d.Key, d => d.Value.ToList()); if (cate.Count == 0) { throw new Exception("無法取得對應ABOUTAS的分類,請聯絡系統管理員"); } SearchListDataModel temp = new SearchListDataModel(); temp.Title = about.C_TITLE; temp.Content = about.C_DESC.RemoveHtmlAllTags(); temp.UpDateTime = (DateTime)about.UPD_DT; temp.BD_DTString = (DateTime)about.BD_DT; temp.Sort = (about.SORT) ?? 0; switch (cate.Keys.First()) { case 0: temp.LinkAddr = string.Format("/AboutUs/TCG?id={0}", about.ID); break; case 1: temp.LinkAddr = string.Format("/AboutUs/Position?id={0}", about.ID); break; case 2: temp.LinkAddr = string.Format("/AboutUs/Statement?id={0}", about.ID); break; case 3: temp.LinkAddr = string.Format("/AboutUs/Law?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; case 4: temp.LinkAddr = string.Format("/AboutUs/Law?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; case 5: temp.LinkAddr = string.Format("/AboutUs/Law?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; case 6: temp.LinkAddr = string.Format("/AboutUs/Organization?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; case 7: temp.LinkAddr = string.Format("/AboutUs/Organization?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; case 8: temp.LinkAddr = string.Format("/AboutUs/Organization?cate_id={0}&id={1}", about.CATE_ID, about.ID); break; default: break; } model.Add(temp); } } }