public int SelectByArticleName(VoteArticleQuery m) { try { return _votearticleDao.SelectByArticleName(m); } catch (Exception ex) { throw new Exception("VoteArticleMgr-->SelectByArticleName-->" + ex.Message, ex); } }
public int SelMaxSort(VoteArticleQuery query) { try { return _votearticleDao.SelMaxSort(query); } catch (Exception ex) { throw new Exception("VoteArticleMgr-->SelMaxSort-->" + ex.Message, ex); } }
public List<VoteArticleQuery> GetAll(VoteArticleQuery query, out int totalCount) { try { return _votearticleDao.GetAll(query,out totalCount); } catch (Exception ex) { throw new Exception("VoteArticleMgr-->GetAll-->" + ex.Message, ex); } }
public int UpdateStatus(VoteArticleQuery m) { try { return _votearticleDao.UpdateStatus(m); } catch (Exception ex) { throw new Exception("VoteArticleMgr-->UpdateStatus-->" + ex.Message, ex); } }
public List<VoteArticleQuery> GetAll(VoteArticleQuery query, out int totalCount) { StringBuilder str = new StringBuilder(); StringBuilder strall = new StringBuilder(); StringBuilder strcounts = new StringBuilder(); try { strcounts.AppendFormat("select count(va.article_id) as totalcounts FROM vote_article va "); strall.AppendFormat("SELECT article_id,va.event_id,va.product_id,va.prod_link,p.product_name,va.user_id,m.user_name as name,article_content,va.vote_count,va.article_sort,va.article_start_time,va.article_end_time,va.article_show_start_time,va.article_show_end_time ");// strall.AppendFormat(",(select count(vd.article_id)from vote_detail vd where va.article_id=vd.article_id and vd.vote_status=1) as reception_count,article_status "); strall.AppendFormat(",article_title,article_banner,va.create_user,va.create_time,va.update_time,va.update_user,ve.event_name FROM vote_article va "); str.AppendFormat(" LEFT JOIN vote_event ve ON va.event_id=ve.event_id "); str.AppendFormat(" LEFT JOIN product p ON va.product_id=p.product_id "); str.AppendFormat(" LEFT JOIN users m ON va.user_id=m.user_id "); str.AppendFormat(" where 1=1 "); totalCount = 0; if (query.article_id > 0) { str.AppendFormat(" and va.article_id='{0}' ", query.article_id); } if (query.event_id>0)//活動編號 { str.AppendFormat(" and va.event_id='{0}' ",query.event_id); } if (!string .IsNullOrEmpty(query.article_title))//文章標題 { str.AppendFormat(" and va.article_title like N'%{0}%' ", query.article_title); } if (query.date != 0) { if (!string.IsNullOrEmpty(query.time_start)) { if (!string.IsNullOrEmpty(query.time_end)) { switch (query.date) { case 1: str.AppendFormat(" and va.create_time between'{0}' and '{1}' ", query.time_start, query.time_end); break; case 2: str.AppendFormat(" and va.article_start_time between'{0}' and '{1}' ", query.time_start, query.time_end); break; case 3: str.AppendFormat(" and va.article_end_time between'{0}' and '{1}' ", query.time_start, query.time_end); break; case 4: str.AppendFormat(" and va.article_show_start_time between'{0}' and '{1}' ", query.time_start, query.time_end); break; case 5: str.AppendFormat(" and va.article_show_end_time between'{0}' and '{1}' ", query.time_start, query.time_end); break; } } } } totalCount = 0; if (query.IsPage) { strcounts.Append(str.ToString()); System.Data.DataTable _dt = _access.getDataTable(strcounts.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); } str.AppendFormat("order by va.article_sort DESC limit {0},{1} ", query.Start, query.Limit); } else { str.Append(" order by va.article_sort DESC "); } strall.Append(str.ToString()); return _access.getDataTableForObj<VoteArticleQuery>(strall.ToString() ); } catch (Exception ex) { throw new Exception(" VoteArticleDao-->GetAll-->" + ex.Message + "sql:" + strall.ToString() + strcounts.ToString(), ex); } }
public int SelMaxSort(VoteArticleQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("select max(article_sort) as maxsort from vote_article where event_id='{0}';", query.event_id); DataTable _dt = _access.getDataTable(sql.ToString()); if (_dt.Rows[0]["maxsort"].ToString()!="") { return Convert.ToInt32(_dt.Rows[0]["maxsort"]) + 1; } else { return 1; } } catch (Exception ex) { throw new Exception(" VoteArticleDao-->SelMaxSort-->" + ex.Message + sql.ToString(), ex); } }
public int SelectByArticleName(VoteArticleQuery m) { StringBuilder str = new StringBuilder(); try { str.AppendFormat("SELECT article_id,article_title FROM vote_article where article_title ='{0}' and article_id<>'{1}'",m.article_title,m.article_id); return _access.getDataTable(str.ToString()).Rows.Count; } catch (Exception ex ) { throw new Exception(" VoteArticleDao-->SelectByArticleName-->" + ex.Message + "sql:" + str.ToString(), ex); } }
public int UpdateStatus(VoteArticleQuery m) {//變更狀態 StringBuilder str = new StringBuilder(); try { m.Replace4MySQL(); if (m.article_id > 0) { str.Append("set sql_safe_updates=0;"); str.AppendFormat("Update vote_article SET article_status='{0}',update_time='{1}',update_user='******' where article_id='{3}';", m.article_status, CommonFunction.DateTimeToString(m.update_time), m.update_user, m.article_id); str.Append("set sql_safe_updates=1;"); //添加修改message表所有狀態 return _access.execCommand(str.ToString()); } else { return 0; } } catch (Exception ex) { throw new Exception(" VoteArticleDao-->UpdateStatus-->" + ex.Message + "sql:" + str.ToString(), ex); } }
public int Update(VoteArticleQuery m) { StringBuilder str = new StringBuilder(); m.Replace4MySQL(); try { str.AppendFormat("update vote_article SET user_id='{0}',article_content='{1}',article_title='{2}',product_id='{3}',prod_link='{4}' ", m.user_id, m.article_content, m.article_title, m.product_id,m.prod_link); str.AppendFormat(",article_banner='{0}',update_time='{1}',update_user='******'", m.article_banner, CommonFunction.DateTimeToString(m.update_time), m.update_user); str.AppendFormat(",event_id='{0}',vote_count='{1}',article_sort='{2}', article_start_time='{3}', article_end_time='{4}',article_show_start_time='{5}',article_show_end_time='{6}' WHERE article_id='{7}';", m.event_id, m.vote_count, m.article_sort, CommonFunction.DateTimeToString(m.article_start_time), CommonFunction.DateTimeToString(m.article_end_time), CommonFunction.DateTimeToString(m.article_show_start_time), CommonFunction.DateTimeToString(m.article_show_end_time), m.article_id); return _access.execCommand(str.ToString()); } catch (Exception ex) { throw new Exception(" VoteArticleDao-->Update-->" + ex.Message + "sql:" + str.ToString(), ex); } }
public int Save(VoteArticleQuery m) { StringBuilder str = new StringBuilder(); m.Replace4MySQL(); try { m.Replace4MySQL(); str.AppendFormat("insert into vote_article (event_id,user_id,article_content,article_status,article_title,article_banner,create_user,create_time,update_time,update_user,product_id,vote_count,article_sort,prod_link,article_start_time,article_end_time,article_show_start_time,article_show_end_time) Value('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')", m.event_id, m.user_id, m.article_content, m.article_status, m.article_title, m.article_banner, m.create_user, CommonFunction.DateTimeToString(m.create_time), CommonFunction.DateTimeToString(m.update_time), m.update_user, m.product_id, m.vote_count, m.article_sort, m.prod_link, CommonFunction.DateTimeToString(m.article_start_time),CommonFunction.DateTimeToString(m.article_end_time), CommonFunction.DateTimeToString(m.article_show_start_time),CommonFunction.DateTimeToString(m.article_show_end_time)); return _access.execCommand(str.ToString()); } catch (Exception ex) { throw new Exception(" VoteArticleDao-->Save-->" + ex.Message + "sql:"+ str.ToString(), ex); } }
public HttpResponseBase GetVoteArticleList() { List<VoteArticleQuery> store = new List<VoteArticleQuery>(); string json = string.Empty; int totalCount = 0; try { _muMgr = new ManageUserMgr(mySqlConnectionString); ManageUserQuery muq = new ManageUserQuery(); muq.IsPage = false; List<ManageUserQuery> mustore = _muMgr.GetNameMail(muq, out totalCount); VoteArticleQuery query = new VoteArticleQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Params["limit"])) { query.Limit = Convert.ToInt32(Request.Params["limit"]); } if (!string.IsNullOrEmpty(Request.Params["ddlSel"])) { query.event_id = Convert.ToInt32(Request.Params["ddlSel"]); } if (!string.IsNullOrEmpty(Request.Params["selcontent"])) { query.article_title = Request.Params["selcontent"]; } if (!string.IsNullOrEmpty(Request.Params["date"])) { query.date = Convert.ToInt32(Request.Params["date"]); } if (!string.IsNullOrEmpty(Request.Params["time_start"])) { query.time_start = Convert.ToDateTime(Request.Params["time_start"]).ToString("yyyy-MM-dd HH:mm:ss"); } if (!string.IsNullOrEmpty(Request.Params["time_end"])) { query.time_end = Convert.ToDateTime(Request.Params["time_end"]).ToString("yyyy-MM-dd HH:mm:ss"); } _votearticle = new VoteArticleMgr(mySqlConnectionString); store = _votearticle.GetAll(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; foreach (var item in store) { item.kendo_editor = Server.HtmlDecode(Server.HtmlDecode(item.article_content)); if (item.article_banner != "") { item.article_banner = imgServerPath + PaperPath + item.article_banner; } int indexc = mustore.FindIndex((ManageUserQuery e) => e.user_id == uint.Parse(item.create_user.ToString())); if (indexc != -1) { item.creat_name = mustore[indexc].user_name; } else { item.creat_name = string.Empty; } int indexu = mustore.FindIndex((ManageUserQuery e) => e.user_id == uint.Parse(item.update_user.ToString())); if (indexu != -1) { item.upd_name = mustore[indexu].user_name; } else { item.upd_name = string.Empty; } } //listUser是准备转换的对象 json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據 } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase SelMaxSort() { string json = string.Empty; VoteArticleQuery query = new VoteArticleQuery(); try { if (!string.IsNullOrEmpty(Request.Params["event_id"])) { query.event_id = Convert.ToInt32(Request.Params["event_id"]); } _votearticle = new VoteArticleMgr(mySqlConnectionString); int sort=_votearticle.SelMaxSort(query); json = "{success:'true',sort:'" + sort + "'}"; } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:'false',sort:'0'}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase SelectByArticleName() { string json = ""; try { VoteArticleQuery query = new VoteArticleQuery(); if (!string.IsNullOrEmpty(Request.Params["id"])) { query.article_id = Convert.ToInt32(Request.Params["id"]); } if (!string.IsNullOrEmpty(Request.Params["article_title"])) { query.article_title = Request.Params["article_title"]; } query.article_title = query.article_title.Trim(); _votearticle = new VoteArticleMgr(mySqlConnectionString); if (_votearticle.SelectByArticleName(query) <= 0) { json = "{success:true,msg:'0'}";//返回json數據 } else { json = "{success:true,msg:'1'}";//返回json數據 } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase UpdateStatsVoteArticle() { List<VoteArticleQuery> store = new List<VoteArticleQuery>(); string json = string.Empty; try { _votearticle = new VoteArticleMgr(mySqlConnectionString); VoteArticleQuery query = new VoteArticleQuery(); if (!string.IsNullOrEmpty(Request.Params["id"])) { query.article_id = Convert.ToInt32(Request.Params["id"]); } if (!string.IsNullOrEmpty(Request.Params["status"])) { query.article_status = Convert.ToInt32(Request.Params["status"]); } query.update_user = (Session["caller"] as Caller).user_id; query.update_time = DateTime.Now; if (_votearticle.UpdateStatus(query) > 0) { json = "{success:true}"; } else { json = "{success:false}"; } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase SaveVoteArticle() { string json = string.Empty; #region 圖片用 string path = Server.MapPath(xmlPath); SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path); SiteConfig extention_config = _siteConfigMgr.GetConfigByName("PIC_Extention_Format"); SiteConfig minValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_Min_Element"); SiteConfig maxValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MaxValue"); SiteConfig admin_userName = _siteConfigMgr.GetConfigByName("ADMIN_USERNAME"); SiteConfig admin_passwd = _siteConfigMgr.GetConfigByName("ADMIN_PASSWD"); //擴展名、最小值、最大值 string extention = extention_config.Value == "" ? extention_config.DefaultValue : extention_config.Value; string minValue = minValue_config.Value == "" ? minValue_config.DefaultValue : minValue_config.Value; string maxValue = maxValue_config.Value == "" ? maxValue_config.DefaultValue : maxValue_config.Value; string localBannerPath = imgLocalPath + PaperPath;//圖片存儲地址 FileManagement fileLoad = new FileManagement(); #endregion try { _votearticle = new VoteArticleMgr(mySqlConnectionString); List<VoteArticleQuery> store = new List<VoteArticleQuery>(); VoteArticleQuery query = new VoteArticleQuery(); if (!string.IsNullOrEmpty(Request.Params["id"])) {//如果是編輯獲取該id數據 int totalCount = 0; query.IsPage = false; query.article_id = int.Parse(Request.Params["id"]); store = _votearticle.GetAll(query, out totalCount); } //product_id if (!string.IsNullOrEmpty(Request.Params["product_id"])) { query.product_id = Convert.ToUInt32(Request.Params["product_id"]); } if (!string.IsNullOrEmpty(Request.Params["event_id"])) { query.event_id = Convert.ToInt32(Request.Params["event_id"]); } if (!string.IsNullOrEmpty(Request.Params["article_title"])) { query.article_title = Request.Params["article_title"]; } if (!string.IsNullOrEmpty(Request.Params["user_id"])) { query.user_id = int.Parse(Request.Params["user_id"]); } if (!string.IsNullOrEmpty(Request.Params["article_content"])) { query.article_content = Request.Params["article_content"]; } if (!string.IsNullOrEmpty(Request.Params["article_sort"])) { query.article_sort =Convert.ToInt32(Request.Params["article_sort"]); } if (!string.IsNullOrEmpty(Request.Params["prod_link"])) { query.prod_link = Request.Params["prod_link"]; } if (!string.IsNullOrEmpty(Request.Params["vote_count"])) { query.vote_count = int.Parse(Request.Params["vote_count"]); } if (!string.IsNullOrEmpty(Request.Params["article_start_time"])) { query.article_start_time =Convert.ToDateTime(Request.Params["article_start_time"]); } if (!string.IsNullOrEmpty(Request.Params["article_end_time"])) { query.article_end_time = Convert.ToDateTime(Request.Params["article_end_time"]); } if (!string.IsNullOrEmpty(Request.Params["article_show_start_time"])) { query.article_show_start_time = Convert.ToDateTime(Request.Params["article_show_start_time"]); } if (!string.IsNullOrEmpty(Request.Params["article_show_end_time"])) { query.article_show_end_time = Convert.ToDateTime(Request.Params["article_show_end_time"]); } #region 上傳圖片 string oldImg = string.Empty; foreach (var item in store) { oldImg = item.article_banner; } if (!string.IsNullOrEmpty(Request.Params["id"]) && Request.Params["article_banner"] == oldImg) { query.article_banner = oldImg; } else { string ServerPath = string.Empty; try { ServerPath = Server.MapPath(imgLocalServerPath + PaperPath); if (Request.Files["article_banner"] != null && Request.Files["article_banner"].ContentLength > 0) { HttpPostedFileBase file = Request.Files["article_banner"]; string fileName = string.Empty;//當前文件名 string fileExtention = string.Empty;//當前文件的擴展名 //獲取圖片名稱 fileName = Path.GetFileName(file.FileName); //獲得後綴名 fileExtention = Path.GetExtension(file.FileName); //獲得不帶後綴名的文件名 fileName = Path.GetFileNameWithoutExtension(file.FileName); string NewFileName = string.Empty; BLL.gigade.Common.HashEncrypt hash = new BLL.gigade.Common.HashEncrypt(); NewFileName = hash.Md5Encrypt(fileName, "32"); //判斷目錄是否存在,不存在則創建 FTP f_cf = new FTP(); f_cf.MakeMultiDirectory(localBannerPath.Substring(0, localBannerPath.Length - PaperPath.Length + 1), PaperPath.Substring(1, PaperPath.Length - 2).Split('/'), ftpuser, ftppwd); fileName = NewFileName + fileExtention; NewFileName = localBannerPath + NewFileName + fileExtention;//絕對路徑 string ErrorMsg = string.Empty; bool result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd); if (result)//上傳成功 { query.article_banner = fileName; //上傳新圖片成功后,再刪除舊的圖片 CommonFunction.DeletePicFile(ServerPath + oldImg);//刪除本地圖片 FTP ftp = new FTP(localBannerPath, ftpuser, ftppwd); List<string> tem = ftp.GetFileList(); if (tem.Contains(oldImg)) { FTP ftps = new FTP(localBannerPath + oldImg, ftpuser, ftppwd); ftps.DeleteFile(localBannerPath + oldImg);//刪除ftp:71.159上的舊圖片 } } } else { //上傳之前刪除已有的圖片 CommonFunction.DeletePicFile(ServerPath + oldImg);//刪除本地圖片 FTP ftp = new FTP(localBannerPath, ftpuser, ftppwd); List<string> tem = ftp.GetFileList(); if (tem.Contains(oldImg)) { FTP ftps = new FTP(localBannerPath + oldImg, ftpuser, ftppwd); ftps.DeleteFile(localBannerPath + oldImg);//刪除ftp:71.159上的舊圖片 } query.article_banner = ""; } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,msg:'圖片上傳失敗!'}"; } } #endregion query.article_status = 0;//默認啟用 query.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; query.update_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; query.create_time = DateTime.Now; query.update_time = DateTime.Now; if (query.article_id > 0) {//編輯 if (_votearticle.Update(query) > 0) { json = "{success:true}"; } else { json = "{success:false,msg:'修改失敗!'}"; } } else {//新增 if (_votearticle.Save(query) > 0) { json = "{success:true}"; } else { json = "{success:false,msg:'新增失敗!'}"; } } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }