/// <summary>
/// 用於返回事物所用到的sql語句
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string Save(UserQuery model)
{
model.Replace4MySQL();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" insert into user_history (user_id,user_name,file_name,content,creat_time,ip ) ");
sb.AppendFormat(" values({0},'{1}','{2}','{3}',{4},'{5}');", model.kuser_id, model.kuser_name, model.file_name, model.content, CommonFunction.GetPHPTime(model.created.ToString()), model.ip);
return sb.ToString();
}
public string Save(UserQuery uquery)
{
uquery.Replace4MySQL();
StringBuilder sql = new StringBuilder();
sql.Append("insert into users (user_id,user_email,user_new_email,user_password,user_newpasswd,user_name,user_mobile,user_zip,user_address,user_type, user_birthday_year,user_birthday_month,user_birthday_day,send_sms_ad,adm_note,user_status,user_source,user_login_attempts,user_actkey,user_reg_date,user_updatedate,user_mobile_bak)");
sql.AppendFormat(" values({0},'{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}',{9},{10},{11},", uquery.user_id, uquery.user_email, uquery.user_new_email, uquery.user_password, uquery.user_newpasswd, uquery.user_name, uquery.user_mobile, uquery.user_zip, uquery.user_address, uquery.user_type,
uquery.user_birthday_year, uquery.user_birthday_month);
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');", uquery.user_birthday_day, uquery.send_sms_ad == false ? 0 : 1, uquery.adm_note, uquery.user_status, uquery.user_source, uquery.user_login_attempts, uquery.user_actkey, uquery.user_reg_date, uquery.user_updatedate,uquery.user_mobile);
return sql.ToString();
}
public List<UserQuery> GetBonusList(UserQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
query.Replace4MySQL();
try
{
sql.Append("select su.s_id, u.user_id,u.user_email,u.user_name,u.user_mobile,u.user_status");
sql.Append(" ,u.user_reg_date,u.user_gender,user_company_id");
sqlCondi.Append(" from users u");
sqlCondi.Append(" left join sales_user su on u.user_id=su.user_id where 1=1");
if (!string.IsNullOrEmpty(query.user_email))
{
sqlCondi.AppendFormat(" and u.user_email like '%{0}%'", query.user_email);
}
if (!string.IsNullOrEmpty(query.user_name))
{
sqlCondi.AppendFormat(" and u.user_name like '%{0}%'", query.user_name);
}
if (!string.IsNullOrEmpty(query.user_mobile))
{
sqlCondi.AppendFormat(" and u.user_mobile like '%{0}%'", query.user_mobile);
}
if (query.is_select_status == 1)
{
sqlCondi.AppendFormat(" and u.user_status={0}", query.user_status);
}
if (query.date_start != 0)
{
sqlCondi.AppendFormat(" and u.user_reg_date>={0}", query.date_start);
}
if (query.date_end != 0)
{
sqlCondi.AppendFormat(" and u.user_reg_date<={0}", query.date_end);
}
sqlCondi.Append(" order by u.user_id desc");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _accessMySql.getDataTable("select count(u.user_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _accessMySql.getDataTableForObj<UserQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("UsersDao.GetBonusList-->" + ex.Message + sql.ToString(), ex);
}
}