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;
}