public List<UserRecommendQuery> QueryAll(UserRecommendQuery store, out int totalCount) { try { return _UserRDao.QueryAll(store, out totalCount); } catch (Exception ex) { throw new Exception("UserRecommendMgr-->QueryAll-->" + ex.Message, ex); } }
public List<UserRecommendQuery> QueryAll(UserRecommendQuery query, out int totalCount) { totalCount = 0; StringBuilder str = new StringBuilder(); StringBuilder strall = new StringBuilder(); StringBuilder strcounts = new StringBuilder(); List<UserRecommendQuery> userRecommendQueryList = null; try { userRecommendQueryList = new List<UserRecommendQuery>(); strcounts.AppendFormat("select count(ur.id) as totalcounts from user_recommend ur "); strall.AppendFormat("SELECT ur.id,ur.user_id,ur.`name`,ur.recommend_user_id,u.user_name as usname,u1.user_name,ur.mail,ur.createtime,u1.user_email,u1.user_password,u1.user_gender,concat(u1.user_birthday_year,'/',u1.user_birthday_month,'/',u1.user_birthday_day) as birthday,u1.user_zip,u1.user_address,u1.user_mobile,u1.user_phone,u1.user_id as suser_id,CASE u1.user_type when '1' THEN '網路會員' else'電話會員' END as mytype,u1.user_gender,u1.user_gender,u1.adm_note,u1.send_sms_ad,u1.paper_invoice FROM user_recommend ur "); str.AppendFormat(" left join users u ON u.user_id = ur.recommend_user_id "); str.AppendFormat(" left join users u1 ON u1.user_id = ur.user_id "); str.AppendFormat(" where 1=1"); if (query.recommend_user_id != 0) str.AppendFormat(" and ur.user_id <> 0 "); if (query.con != "" && query.con != null) { switch (query.ddlstore) { case 1: str.AppendFormat(" and ur.mail like '%" + query.con + "%' "); break; case 2: str.AppendFormat(" and ur.`name` like '%" + query.con + "%' "); break; case 3: str.AppendFormat(" and u.user_name like '%" + query.con + "%' "); break; case 4: str.AppendFormat(" and u.user_email like '%" + query.con + "%' "); break; default: break; } } if (query.startdate != DateTime.Parse("2010/01/01")) str.AppendFormat(" and createtime >= '" + query.startdate.ToString("yyyy-MM-dd HH:mm:ss") + "' "); else str.AppendFormat(" and createtime >= '2010-01-01' "); if (query.enddate != DateTime.Parse("2010/01/01")) str.AppendFormat(" and createtime <= '" + query.enddate.ToString("yyyy-MM-dd HH:mm:ss") + "' "); else str.AppendFormat(" and createtime <= '" + DateTime.Now + "' "); totalCount = 0; if (query.IsPage) { System.Data.DataTable _dt = _access.getDataTable(strcounts.ToString() + str.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); } //order by ur.id DESC str.AppendFormat(" limit {0},{1} ", query.Start, query.Limit); } userRecommendQueryList = _access.getDataTableForObj<UserRecommendQuery>(strall.ToString() + str.ToString()); } catch (Exception ex) { throw new Exception("UserRecommendDao-->QueryAll-->" + ex.Message + "sql:" + strall.ToString() + str.ToString(), ex); } return userRecommendQueryList; }
public HttpResponseBase Recommendlist() { List<UserRecommendQuery> store = new List<UserRecommendQuery>(); string json = string.Empty; try { UserRecommendQuery query = new UserRecommendQuery(); 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.expired = Convert.ToInt32(Request.Params["ddlSel"]); } if (Request.Params["ddlSel"] != "") { query.ddlstore = Int32.Parse(Request.Params["ddlSel"]); } if (Request.Params["ddlCon"] != null && Request.Params["ddlCon"] != "") { query.con = Request.Params["ddlCon"]; } if (Request.Params["start_date"] != null && Request.Params["start_date"] != "") { query.startdate = Convert.ToDateTime(Request.Params["start_date"]); } if (Request.Params["end_date"] != null && Request.Params["end_date"] != "") { query.enddate = Convert.ToDateTime(Request.Params["end_date"]); } if (Request.Params["recommend"] != null && Request.Params["recommend"] != "") { query.recommend_user_id = Int32.Parse(Request.Params["recommend"]); } _userrecommendMgr = new UserRecommendMgr(mySqlConnectionString); int totalCount = 0; store = _userrecommendMgr.QueryAll(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 foreach (var item in store) { if (!string.IsNullOrEmpty(item.name)) { item.name = item.name.Substring(0, 1) + "**"; } if (!string.IsNullOrEmpty(item.usname)) { item.usname = item.usname.Substring(0, 1) + "**"; } if (!string.IsNullOrEmpty(item.user_name)) { item.user_name = item.user_name.Substring(0, 1) + "**"; } if (!string.IsNullOrEmpty(item.mail)) { item.mail = item.mail.Split('@')[0] + "@***"; } if (!string.IsNullOrEmpty(item.user_email)) { item.user_email = item.user_email.Split('@')[0] + "@***"; } //if (!string.IsNullOrEmpty(item.user_phone)) //{ // if (item.user_phone.ToString().Length > 3) // { // item.user_phone = item.user_phone.Substring(0, 3) + "***"; // } // else // { // item.user_phone = item.user_phone + "***"; // } //} if (!string.IsNullOrEmpty(item.user_mobile)) { if (item.user_mobile.ToString().Length == 48) { item.user_mobile = EncryptComputer.EncryptDecryptTextByApi(item.user_mobile, false); } if (item.user_mobile.ToString().Length > 3) { item.user_mobile = item.user_mobile.Substring(0, 3) + "***"; } else { item.user_mobile = item.user_mobile + "***"; } } if (item.user_address.ToString().Length > 3) { item.user_address = item.user_address.Substring(0, 3) + "***"; } else { item.user_address = item.user_address + "***"; } item.user_id = Convert.ToInt32(item.suser_id); item.Iuser_reg_date = CommonFunction.GetNetTime(item.user_reg_date); item.suser_reg_date = CommonFunction.GetNetTime(item.user_reg_date); // item.birthday = item.user_birthday_year.ToString() + "/" + item.user_birthday_month.ToString() + "/" + item.user_birthday_day.ToString(); } json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + ",'msg':'rec',}";//返回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; }