/// <summary> /// 新增sms表數據 /// </summary> /// <param name="query">新增信息</param> /// <returns></returns> public int InsertSms(SmsQuery query) { try { return _SmsDao.InsertSms(query); } catch (Exception ex) { throw new Exception("SmsMgr-->InsertSms-->" + ex.Message, ex); } }
public List<SmsQuery> GetSmsList(SmsQuery query, out int totalcount) { try { return _SmsDao.GetSmsList(query, out totalcount); } catch (Exception ex) { throw new Exception("SmsMgr-->GetSmsList-->" + ex.Message, ex); } }
/// <summary> /// 簡訊查詢列表 /// </summary> /// <param name="query"></param> /// <param name="totalcount"></param> /// <returns></returns> public List<SmsQuery> GetSmsList(SmsQuery query, out int totalcount) { query.Replace4MySQL(); StringBuilder sql = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); try {// FROM_UNIXTIME(UNIX_TIMESTAMP(modified)) as modified_time, FROM_UNIXTIME(UNIX_TIMESTAMP(estimated_send_time)) as estimated_time,created_time sql.Append(@" select id,order_id,mobile,subject,content,send,trust_send,FROM_UNIXTIME(UNIX_TIMESTAMP(created)) as created "); sql.Append(" ,FROM_UNIXTIME(UNIX_TIMESTAMP(estimated_send_time))as estimated_send_time,FROM_UNIXTIME(UNIX_TIMESTAMP(modified)) as modified"); sqlCondi.Append(" from sms "); sqlCondi.Append(" where 1=1 "); if (!string.IsNullOrEmpty(AddSqlQuery(query))) { sqlCondi.Append(AddSqlQuery(query)); } totalcount = 0; if (query.IsPage) { DataTable _dt = _access.getDataTable("select count(id) as totalCount " + sqlCondi.ToString()); if (_dt.Rows.Count > 0) { totalcount = int.Parse(_dt.Rows[0]["totalCount"].ToString()); } sqlCondi.Append(" order by id desc "); sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit); } sql.Append(sqlCondi.ToString()); return _access.getDataTableForObj<SmsQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("SmsDao.GetSmsList-->" + ex.Message + sql.ToString(), ex); } }
public HttpResponseBase updateSms() { string jsonStr = String.Empty; int result = 0; SmsQuery query = new SmsQuery(); try { if (!string.IsNullOrEmpty(Request.Params["id"])) { query.id = int.Parse(Request.Params["id"]); } if (!string.IsNullOrEmpty(Request.Params["content"])) { query.content = Request.Params["content"]; } if (Convert.ToBoolean(Request.Params["state1"]) == true) { query.send = 0;//1表示已經回覆 } else if (Convert.ToBoolean(Request.Params["state2"]) == true) { query.send = 3;//1表示已經回覆 } _ISmsMgr = new SmsMgr(mySqlConnectionString); result = _ISmsMgr.updateSms(query); if (result > 0) { jsonStr = "{success:true}"; } else { jsonStr = "{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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public HttpResponseBase SmsList() { string jsonStr = string.Empty; try { List<SmsQuery> SmsStore = new List<SmsQuery>(); SmsQuery query = new SmsQuery(); 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["datequery"])) { int type = int.Parse(Request.Params["datequery"]); if (type == 1) { query.send = 1; query.modified_time = DateTime.Now.ToString(); } else if (type == 0) { query.created_time = DateTime.Now.ToString(); } if (!string.IsNullOrEmpty(Request.Params["time_start"])) { query.StartTime = DateTime.Parse(Request.Params["time_start"]); } if (!string.IsNullOrEmpty(Request.Params["time_end"])) { query.EndTime = DateTime.Parse(Request.Params["time_end"]); } } if (!string.IsNullOrEmpty(Request.Params["searchcontent"])) { query.content = Request.Params["searchcontent"]; } if (!string.IsNullOrEmpty(Request.Params["send"])) { query.send = Convert.ToInt32(Request.Params["send"]); } if (!string.IsNullOrEmpty(Request.Params["trustsend"])) { int trustsend = 0; if (int.TryParse(Request.Params["trustsend"].ToString(), out trustsend)) { if (trustsend == 1) { query.trust_send = "1"; } else if (trustsend == 0) { query.trust_send = "0,2"; } } } if (!string.IsNullOrEmpty(Request.Params["relation_id"]))//待回覆 { query.id = Convert.ToInt32(Request.Params["relation_id"]); } int totalCount = 0; _ISmsMgr = new SmsMgr(mySqlConnectionString); SmsStore = _ISmsMgr.GetSmsList(query, out totalCount); foreach (var item in SmsStore) { if (Convert.ToBoolean(Request.Params["isSecret"])) { if (!string.IsNullOrEmpty(item.mobile)) { if (item.mobile.ToString().Length > 3) { item.mobile = item.mobile.Substring(0, 3) + "***"; } else { item.mobile = item.mobile.ToString() + "***"; } } } item.created_time = ""; if (item.created > DateTime.Parse("1970-01-01 08:00:00")) { item.created_time = item.created.ToString("yyyy-MM-dd HH:mm:ss"); } item.estimated_time = ""; if (item.estimated_send_time > DateTime.Parse("1970-01-01 08:00:00")) { item.estimated_time = item.estimated_send_time.ToString("yyyy-MM-dd HH:mm:ss"); } item.modified_time = ""; if (item.modified > DateTime.Parse("1970-01-01 08:00:00")) { item.modified_time = item.modified.ToString("yyyy-MM-dd HH:mm:ss"); } } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(SmsStore, 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); jsonStr = "{success:false,msg:0}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public HttpResponseBase ReplyQuestion() { string timestr = string.Empty; string path = Server.MapPath(xmlPath); SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path); SiteConfig Mail_From = _siteConfigMgr.GetConfigByName("Mail_From"); SiteConfig Mail_Host = _siteConfigMgr.GetConfigByName("Mail_Host"); SiteConfig Mail_Port = _siteConfigMgr.GetConfigByName("Mail_Port"); SiteConfig Mail_UserName = _siteConfigMgr.GetConfigByName("Mail_UserName"); SiteConfig Mail_UserPasswd = _siteConfigMgr.GetConfigByName("Mail_UserPasswd"); string EmailFrom = Mail_From.Value;//發件人郵箱 string SmtpHost = Mail_Host.Value;//smtp服务器 string SmtpPort = Mail_Port.Value;//smtp服务器端口 string EmailUserName = Mail_UserName.Value;//郵箱登陸名 string EmailPassWord = Mail_UserPasswd.Value;//郵箱登陸密碼 string json = string.Empty; _IOrderQuesMgr = new OrderQuestionMgr(mySqlConnectionString); _IOrderResponseMgr = new OrderResponseMgr(mySqlConnectionString); OrderQuestionQuery query = new OrderQuestionQuery(); OrderResponse ormodel = new OrderResponse(); SmsQuery smsquery = new SmsQuery(); //if (!string.IsNullOrEmpty(Request.Params["question_content"])) //{ // query.question_content = Request.Params["question_content"]; //} //string order_id = Request.Params["OrderId"].ToString(); //if (!string.IsNullOrEmpty(order_id)) //{ // query.order_id = Convert.ToUInt32(order_id); //} try { if (!string.IsNullOrEmpty(Request.Params["question_id"])) { query.question_id = Convert.ToUInt32(Request.Params["question_id"]); ormodel.question_id = query.question_id; } if (!string.IsNullOrEmpty(Request.Params["order_id"])) { smsquery.order_id = Convert.ToInt32(Request.Params["order_id"]); } //if (!string.IsNullOrEmpty(Request.Params["status"])) //{ // query.question_status = Convert.ToUInt32(Request.Params["status"]); //} if (!string.IsNullOrEmpty(Request.Params["response_content"])) { ormodel.response_content = Request.Params["response_content"].Replace("\\", "\\\\"); ; smsquery.content = ormodel.response_content.Replace("\\", "\\\\"); ; } if (!string.IsNullOrEmpty(Request.Params["question_createdate"])) { timestr = Request.Params["question_createdate"]; } if (!string.IsNullOrEmpty(Request.Params["question_username"])) { query.question_username = Request.Params["question_username"]; } if (!string.IsNullOrEmpty(Request.Params["this_question_email"])) { query.question_email = Request.Params["this_question_email"]; } if (!string.IsNullOrEmpty(Request.Params["question_phone"])) { smsquery.mobile = Request.Params["question_phone"].ToString(); } //回複方式 bool reply1 = false; bool reply2 = false; bool reply3 = false; if (!string.IsNullOrEmpty(Request.Params["reply_mail"])) { reply1 = Convert.ToBoolean(Request.Params["reply_mail"]); } if (!string.IsNullOrEmpty(Request.Params["reply_sms"])) { reply2 = Convert.ToBoolean(Request.Params["reply_sms"]); } if (!string.IsNullOrEmpty(Request.Params["reply_phone"])) { reply3 = Convert.ToBoolean(Request.Params["reply_phone"]); } //如果選擇結案就更新問題狀態 //更新order_question表狀態 query.question_status = 1; _IOrderQuesMgr.UpdateQuestionStatus(query); //獲取數據往回覆表添加數據 ormodel.user_id = uint.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString()); ormodel.response_ipfrom = CommonFunction.GetIP4Address(Request.UserHostAddress.ToString()); ; ormodel.response_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString())); //判斷選擇的回複方式 if (reply1 || !reply1 && !reply2 && !reply3) { ormodel.response_type = 1; _IOrderResponseMgr.insert(ormodel); FileStream fs = new FileStream(Server.MapPath("../ImportUserIOExcel/OrderQuestionResponse.html"), FileMode.OpenOrCreate, FileAccess.Read); StreamReader sr = new StreamReader(fs, Encoding.UTF8); string strTemp = sr.ReadToEnd(); sr.Close(); fs.Close(); strTemp = strTemp.Replace("{{$s_datetime$}}", timestr);//添加提問時間 strTemp = strTemp.Replace("{{$s_username$}}", query.question_username);//添加提問用戶名 strTemp = strTemp.Replace("{{$consultInfo$}}", query.question_content);//添加提問內容 strTemp = strTemp.Replace("{{$consultAnwser$}}", ormodel.response_content.Replace("\\\\", "\\")); sendmail(EmailFrom, FromName, query.question_email, query.question_username, EmailTile, strTemp, "", SmtpHost, Convert.ToInt32(SmtpPort), EmailUserName, EmailPassWord); } else if (reply2) { ormodel.response_type = 2; _IOrderResponseMgr.insert(ormodel); smsquery.type = 11;//10表示聯絡客服列表保存過去的 smsquery.send = 0; smsquery.trust_send = "9"; smsquery.created = DateTime.Now; smsquery.modified = DateTime.Now; _ISmsMgr = new SmsMgr(mySqlConnectionString); _ISmsMgr.InsertSms(smsquery); } else if (reply3) { ormodel.response_type = 3; _IOrderResponseMgr.insert(ormodel); } //if (!reply1 && !reply2 && !reply3) //{ // ormodel.response_type = 4; // _IOrderResponseMgr.insert(ormodel); //} json = "{success:true}"; } 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:0}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase update() { string path = Server.MapPath(xmlPath); SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path); SiteConfig Mail_From = _siteConfigMgr.GetConfigByName("Mail_From"); SiteConfig Mail_Host = _siteConfigMgr.GetConfigByName("Mail_Host"); SiteConfig Mail_Port = _siteConfigMgr.GetConfigByName("Mail_Port"); SiteConfig Mail_UserName = _siteConfigMgr.GetConfigByName("Mail_UserName"); SiteConfig Mail_UserPasswd = _siteConfigMgr.GetConfigByName("Mail_UserPasswd"); string EmailFrom = Mail_From.Value;//發件人郵箱 string SmtpHost = Mail_Host.Value;//smtp服务器 string SmtpPort = Mail_Port.Value;//smtp服务器端口 string EmailUserName = Mail_UserName.Value;//郵箱登陸名 string EmailPassWord = Mail_UserPasswd.Value;//郵箱登陸密碼 string jsonStr = String.Empty; int result = 0; ContactUsQuestion cusTion = new ContactUsQuestion(); ContactUsResponse cusponse = new ContactUsResponse(); SmsQuery smsquery = new SmsQuery(); try { _ctactMgr = new ContactUsQuestionMgr(mySqlConnectionString); _ctactrsponseMgr = new ContactUsResponseMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["question_id"])) { cusTion.question_id = Convert.ToUInt32(Request.Params["question_id"]); smsquery.serial_id = Convert.ToInt32(cusTion.question_id); } if (Convert.ToBoolean(Request.Params["state1"]) == true) { cusTion.question_status = 1;//1表示已經回覆 } else if (Convert.ToBoolean(Request.Params["state2"]) == true) { cusTion.question_status = 1;//1表示已經回覆 } else if (Convert.ToBoolean(Request.Params["state3"]) == true) { cusTion.question_status = 2;//2表示已經回覆 } string updatesql = _ctactMgr.UpdateSql(cusTion); string questiontime = string.Empty; if (!string.IsNullOrEmpty(Request.Params["question_createdate"])) { questiontime = Request.Params["question_createdate"];//提問問題時間 } if (!string.IsNullOrEmpty(Request.Params["question_phone"])) { smsquery.mobile = Request.Params["question_phone"];//提問者電話 } if (!string.IsNullOrEmpty(Request.Params["question_content"])) { cusTion.question_content = Request.Params["question_content"];//咨詢問題內容 } if (!string.IsNullOrEmpty(Request.Params["question_username"])) { cusTion.question_username = Request.Params["question_username"];//提問問題用戶名 } cusponse.response_id = Convert.ToUInt32(_ctactrsponseMgr.GetMaxResponseId()); if (!string.IsNullOrEmpty(Request.Params["question_id"])) { cusponse.question_id = Convert.ToUInt32(Request.Params["question_id"]);//咨詢問題id } cusponse.user_id = Convert.ToUInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id); if (!string.IsNullOrEmpty(Request.Params["content_reply"])) { cusponse.response_content = Request.Params["content_reply"].Replace("\\", "\\\\"); smsquery.content = Request.Params["content_reply"].ToString().Replace("\\", "\\\\");//保存sms表的數據 } bool reply1 = false; bool reply2 = false; bool reply3 = false; if (!string.IsNullOrEmpty(Request.Params["reply1"])) { reply1 = Convert.ToBoolean(Request.Params["reply1"]); } if (!string.IsNullOrEmpty(Request.Params["reply2"])) { reply2 = Convert.ToBoolean(Request.Params["reply2"]); } if (!string.IsNullOrEmpty(Request.Params["reply3"])) { reply3 = Convert.ToBoolean(Request.Params["reply3"]); } if (!string.IsNullOrEmpty(Request.Params["question_email"])) { cusTion.question_email = Request.Params["question_email"].ToString().Trim(); } cusponse.response_createdate = Convert.ToInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))); cusponse.response_ipfrom = CommonFunction.GetIP4Address(Request.UserHostAddress.ToString()); if (cusTion.question_status != 2) { //mh.SendToUser(Convert.ToInt32(cusponse.user_id),"郵箱標題","郵箱內容");//發送郵件 if (reply1 || !reply3 && !reply2 && !reply1) {//當email被勾選 cusponse.response_type = 1; _ctactrsponseMgr.Insert(updatesql, cusponse); FileStream fs = new FileStream(Server.MapPath("../ImportUserIOExcel/ContactUsQuestionResponse.html"), FileMode.OpenOrCreate, FileAccess.Read); StreamReader sr = new StreamReader(fs, Encoding.UTF8); string strTemp = sr.ReadToEnd(); sr.Close(); fs.Close(); strTemp = strTemp.Replace("{{$s_datetime$}}", questiontime); strTemp = strTemp.Replace("{{$s_username$}}", cusTion.question_username); strTemp = strTemp.Replace("{{$consultInfo$}}", cusTion.question_content); strTemp = strTemp.Replace("{{$consultAnwser$}}", cusponse.response_content.Replace("\\\\", "\\")); sendmail(EmailFrom, FromName, cusTion.question_email, cusTion.question_username, EmailTile, strTemp, "", SmtpHost, Convert.ToInt32(SmtpPort), EmailUserName, EmailPassWord); } else if (reply2) {//當簡訊被勾選 cusponse.response_id = Convert.ToUInt32(_ctactrsponseMgr.GetMaxResponseId()); smsquery.type = 10;//10表示聯絡客服列表保存過去的 smsquery.send = 0; smsquery.trust_send = "8"; smsquery.created = DateTime.Now; smsquery.modified = DateTime.Now; cusponse.response_type = 2; _ctactrsponseMgr.Insert(updatesql, cusponse); _ISmsMgr = new SmsMgr(mySqlConnectionString); _ISmsMgr.InsertSms(smsquery); } else if (reply3) {//當電話被勾選 cusponse.response_id = Convert.ToUInt32(_ctactrsponseMgr.GetMaxResponseId()); cusponse.response_type = 3; _ctactrsponseMgr.Insert(updatesql, cusponse); } //if (!reply3 && !reply2 && !reply1) //{ // cusponse.response_id = Convert.ToUInt32(_ctactrsponseMgr.GetMaxResponseId()); // cusponse.response_type = 4; // _ctactrsponseMgr.Insert(updatesql, cusponse); //} jsonStr = "{success:true}"; } else//等於2時,已處理,不操作其它 { cusponse.response_id = 4; result = _ctactrsponseMgr.Insert(updatesql, cusponse); if (result > 0) { jsonStr = "{success:true}"; } else { jsonStr = "{success:false}"; } DateTime today = DateTime.Now; DateTime a = DateTime.Now.AddDays(-5); } } 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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
/// <summary> /// 修改狀態 /// </summary> /// <param name="query"></param> /// <returns></returns> public int updateSms(SmsQuery query) { query.Replace4MySQL(); StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"update sms set content='{0}',send='{1}' where id='{2}' ", query.content, query.send, query.id); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("SmsDao.updateSms-->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 新增sms表數據 /// </summary> /// <param name="query">新增信息</param> /// <returns></returns> public int InsertSms(SmsQuery query) { query.Replace4MySQL(); StringBuilder sql = new StringBuilder(); try { sql.Append(@"INSERT INTO sms (type,serial_id,order_id,mobile,content,subject,estimated_send_time,send,trust_send,created,modified) "); sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}','{4}'", query.type, query.serial_id, query.order_id, query.mobile, query.content); sql.AppendFormat(@",'{0}','{1}','{2}','{3}'", "", CommonFunction.DateTimeToString(query.estimated_send_time), query.send, query.trust_send); sql.AppendFormat(@",'{0}','{1}')", CommonFunction.DateTimeToString(query.created), CommonFunction.DateTimeToString(query.modified)); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("SmsDao.InsertSms-->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 把要查詢的額外條件根據給擴展類的一些屬性的值來添加sql /// </summary> /// <param name="query"></param> /// <returns></returns> public string AddSqlQuery(SmsQuery query) { StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(query.created_time)) { if (query.StartTime > DateTime.MinValue) { sb.AppendFormat(" and created >='{0}' ", query.StartTime.ToString("yyyy-MM-dd 00:00:00")); } if (query.EndTime > DateTime.MinValue) { sb.AppendFormat(" and created <='{0}' ", query.EndTime.ToString("yyyy-MM-dd 23:59:59")); } } else if (!string.IsNullOrEmpty(query.modified_time)) { sb.AppendFormat(" and send='1' "); if (query.StartTime > DateTime.MinValue) { sb.AppendFormat(" and modified >='{0}' ", query.StartTime.ToString("yyyy-MM-dd 00:00:00")); } if (query.EndTime > DateTime.MinValue) { sb.AppendFormat(" and modified <='{0}' ", query.EndTime.ToString("yyyy-MM-dd 23:59:59")); } } if (!string.IsNullOrEmpty(query.content)) { Regex regex = new System.Text.RegularExpressions.Regex("^[0-9]*$"); if (regex.IsMatch(query.content)) { sb.AppendFormat(" and (id='{0}' or order_id='{0}' or mobile='{0}') ", query.content); } else { sb.AppendFormat(" and mobile='{0}' ", query.content); } } if (query.send != -1) { sb.AppendFormat(" and send='{0}'", query.send); } if (!string.IsNullOrEmpty(query.trust_send)) { sb.AppendFormat(" and trust_send in ({0})", query.trust_send); } if (query.id != 0) { sb.AppendFormat(" and id='{0}'", query.id); } return sb.ToString(); }