/// <summary>
/// 删除
/// </summary>
/// <param name="strWhere"></param >
/// <param name="DFTable"></param>
public bool Delete(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("delete from {0}", TableName);
strSql.Append(GetWhere(strWhere));
return(DBHelperMySql.ExcuteNonQuery(strSql.ToString()) > 0);
}
/// <summary>
/// 更新方法模版
/// </summary>
/// <param name="setValue">更新字段和值</param>
/// <param name="strWhere"></param>
/// <param name="arrayList"></param>
public bool Update(string setValue, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("update {0} set {1}", TableName, setValue);
strSql.Append(GetWhere(strWhere));
return(DBHelperMySql.ExcuteNonQuery(strSql.ToString()) > 0);
}
/// <summary>
/// 查询经营类目详细信息列表
/// </summary>
/// <returns></returns>
public IList <CategoryDetail> QueryDetails()
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.categoryName AS parentName FROM categorys t1 ");
builder.Append("LEFT JOIN categorys t2 ON t1.parentId = t2.cid; ");
var dataSet = DBHelperMySql.Query(builder.ToString());
return(dataSet.ToList <CategoryDetail>());
}
/// <summary>
/// 查询分页总数
/// </summary>
/// <param name="type"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="where"></param>
/// <returns></returns>
public int SelectLimitCount(int type, string beginTime, string endTime, string where)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT COUNT(t1.payId) FROM pays t1 ");
builder.AppendFormat($"WHERE {where}");
var result = DBHelperMySql.GetSingle(builder.ToString());
return(Convert.ToInt32(result));
}
public int Count()
{
StringBuilder strSql = new StringBuilder();
MySqlParameter[] parameters;
strSql.AppendFormat("select count(*) from {0}", TableName);
object result = DBHelperMySql.GetSingle(strSql.ToString());
return(Convert.ToInt32(result));
}
/// <summary>
/// 查询钱包信息
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public Wallet SelectById(int uid)
{
var dataSet = DBHelperMySql.Query("SELECT * FROM wallets WHERE userId = " + uid);
var list = dataSet.ToList <Wallet>();
if (list == null || list.Count == 0)
{
return(null);
}
return(list.First());
}
/// <summary>
/// 查询分页总数
/// </summary>
/// <param name="state"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="where"></param>
/// <returns></returns>
public int SelectLimitCount(int state, string beginTime, string endTime, string where)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT COUNT(t1.pid) FROM products t1 ");
builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid ");
builder.AppendFormat($"WHERE {where}");
var result = DBHelperMySql.GetSingle(builder.ToString());
return(Convert.ToInt32(result));
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="R"></typeparam>
/// <returns></returns>
public IList <R> Query <R>(QueryParam model, string tableName)
{
MySqlParameter[] parameters =
{
new MySqlParameter("_fields", model.Fields),
new MySqlParameter("_tableName", tableName),
new MySqlParameter("_where", model.StrWhere),
new MySqlParameter("_orderby", model.OrderBy),
};
return(DBHelperMySql.RunProceReturnDataSet("Query", parameters)
.Tables[0].ToList <R>());
}
/// <summary>
/// 查询店铺商品信息
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public IList <ShopProductDetail> GetShopProducts(int uid)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t3.cid, t3.categoryName AS parentName, t2.* FROM products t1 ");
builder.Append("LEFT JOIN categorys t2 ON t1.categoryId = t2.cid ");
builder.Append("LEFT JOIN categorys t3 ON t2.parentId = t3.cid ");
builder.Append($"WHERE t1.sid = {uid} AND t1.status = 1 ");
var dataSet = DBHelperMySql.Query(builder.ToString());
return(dataSet.ToList <ShopProductDetail>());
}
/// <summary>
/// 插入返回主键
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="DFTable"></param>
public bool InsertReturn <T>(T model, ref long pk)
{
StringBuilder strSql = new StringBuilder();
MySqlParameter[] parameters;
strSql.AppendFormat("insert into {0}", TableName);
strSql.Append(GetInsertFields());
strSql.Append(" values ");
strSql.Append(GetInsertValues <T>(model, out parameters));
int result = DBHelperMySql.ExcuteInsertReturnId(strSql.ToString(), out pk, parameters);
return(result > 0);
}
/// <summary>
/// 查询分页
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="state"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="where"></param>
/// <returns></returns>
public IList <Accounts> SelectLimit(int page, string limit, int state, string beginTime, string endTime, string where)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.* FROM accounts t1 ");
builder.AppendFormat($"WHERE {where} GROUP BY t1.accountsId ORDER BY t1.addTime DESC LIMIT {page},{limit}");
var dataSet = DBHelperMySql.Query(builder.ToString());
if (dataSet == null || dataSet.Tables.Count == 0)
{
return(null);
}
return(dataSet.Tables[0].ToList <Accounts>());
}
/// <summary>
/// 查询分页
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="type"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="where"></param>
/// <returns></returns>
public IList <PaysDetail> SelectLimit(int page, string limit, int tradeType, int type, string beginTime, string endTime, string where)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.username,t3.* FROM pays t1 LEFT JOIN users t2 ON t1.fromUid = t2.uid ");
builder.Append("LEFT JOIN pays t3 ON t1.toUid = t2.uid ");
builder.AppendFormat($"WHERE {where} GROUP BY t1.payId ORDER BY t1.addTime DESC LIMIT {page},{limit}");
var dataSet = DBHelperMySql.Query(builder.ToString());
if (dataSet == null || dataSet.Tables.Count == 0)
{
return(null);
}
return(dataSet.Tables[0].ToList <PaysDetail>());
}
/// <summary>
/// 查询经营类目详细信息
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public CategoryDetail QueryDetail(int value)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.categoryName AS parentName FROM categorys t1 ");
builder.Append($"LEFT JOIN categorys t2 ON t1.parentId = t2.cid WHERE t1.cid = {value} ");
var dataSet = DBHelperMySql.Query(builder.ToString());
var list = dataSet.ToList <CategoryDetail>();
if (list == null || list.Count == 0)
{
return(null);
}
return(list.First());
}
/// <summary>
/// 查询商品详情 韦德 2018年9月25日15:39:40
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public ProductDetail QueryDetail(int value)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.roleId,t2.level,t3.* FROM products t1 ");
builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid ");
builder.Append("LEFT JOIN permissions t3 ON t2.roleId = t3.permissionId ");
builder.Append($"WHERE pid = {value}; ");
var dateSet = DBHelperMySql.Query(builder.ToString());
if (dateSet == null || dateSet.Tables.Count <= 0)
{
return(null);
}
return(dateSet.Tables[0].ToList <ProductDetail>().First());
}
/// <summary>
/// 查询用户财务详情
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public UserFinanceDetail SelectFinanceDetail(int uid)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.* FROM users t1 ");
builder.Append("LEFT JOIN wallets t2 ON t1.uid = t2.userId ");
builder.Append("WHERE uid = " + uid);
var dataSet = DBHelperMySql.Query(builder.ToString());
var userFinanceDetails = dataSet.ToList <UserFinanceDetail>();
if (userFinanceDetails == null || userFinanceDetails.Count == 0)
{
return(null);
}
return(userFinanceDetails.First());
}
/// <summary>
/// 查询多个用户的财务详情
/// </summary>
/// <param name="uids"></param>
/// <returns></returns>
public IList <UserFinanceDetail> SelectFinanceDetails(int[] uids)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*,t2.* FROM users t1 ");
builder.Append("LEFT JOIN wallets t2 ON t1.uid = t2.userId ");
builder.Append($"WHERE uid IN ({uids.AsJoin()}) ");
var dataSet = DBHelperMySql.Query(builder.ToString());
var userFinanceDetails = dataSet.ToList <UserFinanceDetail>();
if (userFinanceDetails == null || userFinanceDetails.Count == 0)
{
return(null);
}
return(userFinanceDetails);
}
/// <summary>
/// 查询分页
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="state"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="where"></param>
/// <returns></returns>
public List <ProductDetail> SelectLimit(int page, string limit, int state, string beginTime, string endTime, string where)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT t1.*, t2.*, t4.*, (SELECT permissionId FROM permissions WHERE permissionId IN(t3.permissionList)), (SELECT roleName FROM permissions WHERE permissionId IN(t3.permissionList)) FROM products t1 ");
builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid ");
builder.Append("LEFT JOIN permission_relations t3 ON t1.sid = t3.uid ");
builder.Append("LEFT JOIN categorys t4 ON t1.categoryId = t4.cid ");
builder.AppendFormat($"WHERE {where} GROUP BY t1.pid ORDER BY t1.addTime DESC LIMIT {page},{limit}");
var dataSet = DBHelperMySql.Query(builder.ToString());
if (dataSet == null || dataSet.Tables.Count == 0)
{
return(null);
}
return(dataSet.Tables[0].ToList <ProductDetail>());
}
/// <summary>
/// 查询收入支出金额
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public IDictionary <string, string> SelectAmount(int uid)
{
StringBuilder builder = new StringBuilder();
builder.Append($"SELECT (SELECT SUM(amount) FROM pays WHERE fromUid != {uid}) AS incomeAmount, ");
builder.AppendFormat($"(SELECT SUM(amount) FROM pays WHERE fromUid = {uid}) AS expendAmount FROM pays LIMIT 1");
var dataSet = DBHelperMySql.Query(builder.ToString());
if (dataSet == null || dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
{
return(null);
}
var convertDictionary = dataSet.Tables[0].Rows.Cast <DataRow>().ToDictionary(x => x[0].ToString(), x => x[1].ToString());
IDictionary <string, string> dictionary = new Dictionary <string, string>();
dictionary.Add("incomeAmount", Convert.ToString(convertDictionary.First().Key));
dictionary.Add("expendAmount", Convert.ToString(convertDictionary.First().Value));
return(dictionary);
}
/// <summary>
/// 根据条件查询分页订单列表--条件不含子表信息--只查询一张主表
/// </summary>
/// <param name="condition">查询条件</param>
/// <returns>返回需要的订单列表(不含订单子表信息)</returns>
public virtual PageModel <R> PageQuery <R>(PageParam model) where R : class, new()
{
StringBuilder strSql = new StringBuilder();
MySqlParameter[] parameters = new MySqlParameter[7];
parameters[0] = new MySqlParameter("_fields", model.Fields);
parameters[1] = new MySqlParameter("_tableName", model.TableName ?? TableName);
parameters[2] = new MySqlParameter("_where", model.StrWhere);
parameters[3] = new MySqlParameter("_orderby", model.OrderBy);
parameters[4] = new MySqlParameter("_pageindex", model.PageIndex);
parameters[5] = new MySqlParameter("_pagesize", model.PageSize);
parameters[6] = new MySqlParameter("_totalcount", MySqlDbType.Int32);
parameters[6].Direction = ParameterDirection.Output;
DataSet ds = DBHelperMySql.RunProceReturnDataSet("PageQuery", parameters);
PageModel <R> respQuery = new PageModel <R>()
{
List = ds.Tables[0].ToList <R>(),
TotalCount = int.Parse(parameters[6].Value.ToString()),
PageIndex = model.PageIndex,
PageSize = model.PageSize
};
return(respQuery);
}
/// <summary>
/// IDictionary
/// </summary>
/// <param name="DFTable">sql 哈希集合</param>
public void CommitTransaction(IDictionary <string, DbParameter[]> DFTable, IsolationLevel isolationLevel)
{
DBHelperMySql.ExecuteSqlTran(DFTable, isolationLevel);
}
/// <summary>
/// Hashtable提交事务
/// </summary>
/// <param name="DFTable">sql 哈希集合</param>
public void CommitTransaction(Hashtable DFTable)
{
DBHelperMySql.ExecuteSqlTran(DFTable);
}
/// <summary>
/// 查询返回List
/// </summary>
/// <typeparam name="R">占位符</typeparam>
/// <param name="strSql">Sql</param>
/// <param name="param">参数</param>
/// <returns></returns>
public IList <R> Query <R>(string strSql)
{
return(DBHelperMySql.Query(strSql).Tables[0].ToList <R>());
}
/// <summary>
/// 查询返回List
/// </summary>
/// <typeparam name="R">占位符</typeparam>
/// <param name="strSql">Sql</param>
/// <param name="param">参数</param>
/// <returns></returns>
protected IList <R> Query <R>(string strSql, MySqlParameter[] param)
{
return(DBHelperMySql.Query(strSql, param).Tables[0].ToList <R>());
}
/// <summary>
/// 根据uid查询权限列表 韦德 2018年9月16日14:35:31
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public IList <Permission> SelectList(int uid)
{
string sql = $"SELECT * FROM permissions WHERE permissionId IN( SELECT permissionList FROM `permission_relations` WHERE uid = {uid} )";
return(DBHelperMySql.Query(sql).Tables[0].ToList <Permission>());
}
/// <summary>
/// Hashtable提交事务--支持乐观锁
/// </summary>
/// <param name="DFTable"></param>
/// <returns>返回false代表存在Sql受影响行数为0，更新失败！事务回滚</returns>
public bool CommitTransactionLock(Hashtable DFTable)
{
return(DBHelperMySql.ExecuteSqlTranLock(DFTable));
}
/// <summary>
/// IDictionary
/// </summary>
/// <param name="DFTable">sql 哈希集合</param>
public void CommitTransaction(IDictionary <string, DbParameter[]> DFTable)
{
DBHelperMySql.ExecuteSqlTran(DFTable);
}
/// <summary>
/// IDictionary
/// </summary>
/// <param name="DFTable">sql 哈希集合</param>
public bool CommitTransactionLock(IDictionary <StringBuilder, DbParameter[]> DFTable)
{
return(DBHelperMySql.ExecuteSqlTranLock(DFTable));
}
