/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select a.*,
b.StoreName,
CASE
c.Nickname
WHEN NULL THEN
c.TrueName ELSE c.Nickname
END AS CustomerName,
CASE
d.TrueName
WHEN NULL THEN
d.Nickname ELSE d.TrueName
END AS BusinessName FROM t_service_evaluation a
LEFT JOIN t_store b ON a.StoreId = b.ID
LEFT JOIN t_customer c ON a.CustomerId = c.ID
LEFT JOIN t_business d ON a.BusinessId = d.ID " );
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT
a.*, b.StoreName,
CASE c.TrueName
WHEN NULL THEN
c.Nickname
ELSE
c.TrueName
END AS BusinessName,
CASE d.TrueName
WHEN NULL THEN
d.Nickname
ELSE
d.TrueName
END AS AgentName
FROM
t_withdraw a
LEFT JOIN t_store b ON a.StoreId = b.ID
LEFT JOIN t_business c ON a.OpenId = c.OpenId
LEFT JOIN t_agent d ON a.OpenId = d.OpenId ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 根据用户名返回上一次登录记录
/// </summary>
public Model.manager_log GetModel(string user_name, int top_num, string action_type)
{
int rows = GetCount("user_name='" + user_name + "'");
if (top_num == 1)
{
rows = 2;
}
if (rows > 1)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id from (select id from " + databaseprefix + "manager_log");
strSql.Append(" where user_name=@user_name and action_type=@action_type order by id desc limit " + top_num + ") as T ");
strSql.Append(" order by id asc");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 100)
};
parameters[0].Value = user_name;
parameters[1].Value = action_type;
object obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
if (obj != null)
{
return(GetModel(Convert.ToInt32(obj)));
}
}
return(null);
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select a.*,
b.ID AS BusinessId,
CASE
b.TrueName
WHEN NULL THEN
b.Nickname ELSE b.TrueName
END AS BusinessName,
c.AgentId,
CASE
d.TrueName
WHEN NULL THEN
d.Nickname ELSE d.TrueName
END AS AgentName,
e.DistributionName FROM " + @"t_store a
LEFT JOIN t_business b ON a.ApplyOpenId = b.OpenId
LEFT JOIN t_agent_store c ON a.ID = c.StoreId
LEFT JOIN t_agent d ON c.AgentId = d.ID
left join t_reward_distribution e on a.PlanId = e.ID " );
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 返回数据数
/// </summary>
public int GetCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) as H from " + databaseprefix + "manager_log ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Convert.ToInt32(DbHelperMySql.GetSingle(strSql.ToString())));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "manager_log");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.*,IFNULL( b.StoreNum, 0 ) AS StoreNum FROM " + @"t_agent a
LEFT JOIN(SELECT COUNT(* ) AS StoreNum, AgentId FROM t_agent_store GROUP BY AgentId) b ON a.ID = b.AgentId ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 得到最大ID
/// </summary>
private int GetMaxId(MySqlConnection conn, MySqlTransaction trans)
{
string strSql = "select id from " + databaseprefix + "manager_log order by id desc limit 1";
object obj = DbHelperMySql.GetSingle(conn, trans, strSql);
if (obj == null)
{
return(0);
}
else
{
return(int.Parse(obj.ToString()));
}
}
/// <summary>
/// 返回角色名称
/// </summary>
public string GetTitle(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select role_name from " + databaseprefix + "manager_role");
strSql.Append(" where id=" + id);
strSql.Append(" limit 1");
string title = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(title))
{
return("");
}
return(title);
}
/// <summary>
/// 根据导航的名称查询其ID
/// </summary>
public int GetNavId(string nav_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id from " + databaseprefix + "navigation");
strSql.Append(" where name=@nav_name limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@nav_name", MySqlDbType.VarChar, 50)
};
parameters[0].Value = nav_name;
string str = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString(), parameters));
return(Utils.StrToInt(str, 0));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select b.*, c.StoreName, c.StoreImgUrl, CASE a.RoleId
WHEN 0 THEN '超级管理员'
WHEN 1 THEN '管理员'
WHEN 2 THEN '门店经理'
WHEN 3 THEN '服务员'END AS RoleId,a.State, a.ID r_ID FROM "
+ " t_store_business a LEFT JOIN t_business b ON a.BusinessId = b.ID LEFT JOIN t_store c ON a.StoreId = c.ID ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获取父类下的所有子类ID(含自己本身)
/// </summary>
public string GetIds(string nav_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id from " + databaseprefix + "navigation");
strSql.Append(" where name=@nav_name limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@nav_name", MySqlDbType.VarChar, 50)
};
parameters[0].Value = nav_name;
object obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
if (obj != null)
{
return(GetIds(Convert.ToInt32(obj)));
}
return(string.Empty);
}
/// <summary>
/// 根据用户名取得Salt
/// </summary>
public string GetSalt(string user_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select salt from " + databaseprefix + "manager");
strSql.Append(" where user_name=@user_name");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100)
};
parameters[0].Value = user_name;
string salt = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString(), parameters));
if (string.IsNullOrEmpty(salt))
{
return("");
}
return(salt);
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM t_checkcode ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
object obj = DbHelperMySql.GetSingle(strSql.ToString());
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 快捷添加系统默认导航，带事务
/// </summary>
public int Add(MySqlConnection conn, MySqlTransaction trans, string parent_name, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type)
{
//先根据名称查询该父ID
StringBuilder strSql = new StringBuilder();
strSql.Append("select id from " + databaseprefix + "navigation");
strSql.Append(" where name=@parent_name limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_name", MySqlDbType.VarChar, 50)
};
parameters[0].Value = parent_name;
object obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
int parent_id = Convert.ToInt32(obj);
return(Add(conn, trans, parent_id, nav_name, title, link_url, sort_id, channel_id, action_type));
}
/// <summary>
/// 根据用户名密码返回一个实体
/// </summary>
public Model.manager GetModel(string user_name, string password)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id from " + databaseprefix + "manager");
strSql.Append(" where user_name=@user_name and password=@password and is_lock=0");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@password", MySqlDbType.VarChar, 100)
};
parameters[0].Value = user_name;
parameters[1].Value = password;
object obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
if (obj != null)
{
return(GetModel(Convert.ToInt32(obj)));
}
return(null);
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT
a.*,
CASE
b.Nickname
WHEN NULL THEN
b.TrueName ELSE b.Nickname
END AS CustomerName,
CASE
c.TrueName
WHEN NULL THEN
c.Nickname ELSE c.TrueName
END AS BusinessName,
d.StoreName,
CASE
f.TrueName
WHEN NULL THEN
f.Nickname ELSE f.TrueName
END AS AgentName
FROM
t_reward a
LEFT JOIN t_customer b ON a.CustomerId = b.ID
LEFT JOIN t_business c ON a.BusinessId = c.ID
LEFT JOIN t_store d ON a.StoreId = d.ID
LEFT JOIN t_agent_store e ON d.ID = e.StoreId
LEFT JOIN t_agent f ON e.AgentId = f.ID " );
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 快捷添加系统默认导航
/// </summary>
public int Add(string parent_name, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type)
{
//先根据名称查询该父ID
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("select id from " + databaseprefix + "navigation");
strSql1.Append(" where name=@parent_name limit 1");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@parent_name", MySqlDbType.VarChar, 50)
};
parameters1[0].Value = parent_name;
object obj1 = DbHelperMySql.GetSingle(strSql1.ToString(), parameters1);
if (obj1 == null)
{
return(0);
}
int parent_id = Convert.ToInt32(obj1);
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "navigation(");
strSql.Append("parent_id,channel_id,nav_type,name,title,link_url,sort_id,action_type,is_lock,is_sys)");
strSql.Append(" values (");
strSql.Append("@parent_id,@channel_id,@nav_type,@name,@title,@link_url,@sort_id,@action_type,@is_lock,@is_sys)");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_id", MySqlDbType.Int32, 4),
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@nav_type", MySqlDbType.VarChar, 50),
new MySqlParameter("@name", MySqlDbType.VarChar, 50),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@link_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 500),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4)
};
parameters[0].Value = parent_id;
parameters[1].Value = channel_id;
parameters[2].Value = DTEnums.NavigationEnum.System.ToString();
parameters[3].Value = nav_name;
parameters[4].Value = title;
parameters[5].Value = link_url;
parameters[6].Value = sort_id;
parameters[7].Value = action_type;
parameters[8].Value = 0;
parameters[9].Value = 1;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
