/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_amount_log model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "user_amount_log(");
strSql.Append("user_id,user_name,type,order_no,trade_no,payment_id,value,remark,status,add_time,complete_time)");
strSql.Append(" values (");
strSql.Append("@user_id,@user_name,@type,@order_no,@trade_no,@payment_id,@value,@remark,@status,@add_time,@complete_time)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@user_id", SqlDbType.Int,4),
new SqlParameter("@user_name", SqlDbType.NVarChar,100),
new SqlParameter("@type", SqlDbType.NVarChar,50),
new SqlParameter("@order_no", SqlDbType.NVarChar,100),
new SqlParameter("@trade_no", SqlDbType.NVarChar,100),
new SqlParameter("@payment_id", SqlDbType.Int,4),
new SqlParameter("@value", SqlDbType.Decimal,5),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@status", SqlDbType.TinyInt,1),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@complete_time", SqlDbType.DateTime),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.type;
parameters[3].Value = model.order_no;
parameters[4].Value = model.trade_no;
parameters[5].Value = model.payment_id;
parameters[6].Value = model.value;
parameters[7].Value = model.remark;
parameters[8].Value = model.status;
parameters[9].Value = model.add_time;
parameters[10].Value = model.complete_time;
parameters[11].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
if (model.status > 0)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update " + databaseprefix + "users set amount=amount+" + model.value);
strSql2.Append(" where id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters2[0].Value = model.user_id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[11].Value;
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.manager_role model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "manager_role(");
strSql.Append("role_name,role_type,is_sys)");
strSql.Append(" values (");
strSql.Append("@role_name,@role_type,@is_sys)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@role_name", SqlDbType.NVarChar,100),
new SqlParameter("@role_type", SqlDbType.TinyInt,1),
new SqlParameter("@is_sys", SqlDbType.TinyInt,1),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.role_name;
parameters[1].Value = model.role_type;
parameters[2].Value = model.is_sys;
parameters[3].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
StringBuilder strSql2;
if (model.manager_role_values != null)
{
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "manager_role_value(");
strSql2.Append("role_id,nav_name,action_type)");
strSql2.Append(" values (");
strSql2.Append("@role_id,@nav_name,@action_type)");
SqlParameter[] parameters2 = {
new SqlParameter("@role_id", SqlDbType.Int,4),
new SqlParameter("@nav_name", SqlDbType.NVarChar,100),
new SqlParameter("@action_type", SqlDbType.NVarChar,50)};
parameters2[0].Direction = ParameterDirection.InputOutput;
parameters2[1].Value = modelt.nav_name;
parameters2[2].Value = modelt.action_type;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
}
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[3].Value;
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_point_log model, bool is_upgrade)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "user_point_log(");
strSql.Append("user_id,user_name,value,remark,add_time)");
strSql.Append(" values (");
strSql.Append("@user_id,@user_name,@value,@remark,@add_time)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@user_id", SqlDbType.Int,4),
new SqlParameter("@user_name", SqlDbType.NVarChar,100),
new SqlParameter("@value", SqlDbType.Int,4),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.value;
parameters[3].Value = model.remark;
parameters[4].Value = model.add_time;
parameters[5].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update " + databaseprefix + "users set point=point+" + model.value);
if (model.value > 0 && is_upgrade)
{
strSql2.Append(",exp=exp+" + model.value);
}
strSql2.Append(" where id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters2[0].Value = model.user_id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[5].Value;
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
//删除商品的属性值
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from wx_shop_productAttr_value ");
strSql1.Append(" where productId=@productId ");
SqlParameter[] parameters1 = {
new SqlParameter("@productId", SqlDbType.Int,4)};
parameters1[0].Value = id;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1);
sqllist.Add(cmd);
//删除商品配件
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from wx_shop_sku ");
strSql2.Append(" where productId=@productId ");
SqlParameter[] parameters2 = {
new SqlParameter("@productId", SqlDbType.Int,4)};
parameters2[0].Value = id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//删除图片相册
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("delete from wx_shop_albums ");
strSql3.Append(" where productId=@productId ");
SqlParameter[] parameters3 = {
new SqlParameter("@productId", SqlDbType.Int,4)};
parameters3[0].Value = id;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
////删除会员组价格
//StringBuilder strSql4 = new StringBuilder();
//strSql4.Append("delete from " + databaseprefix + "user_group_price ");
//strSql4.Append(" where article_id=@article_id ");
//SqlParameter[] parameters4 = {
// new SqlParameter("@article_id", SqlDbType.Int,4)};
//parameters4[0].Value = id;
//cmd = new CommandInfo(strSql4.ToString(), parameters4);
//sqllist.Add(cmd);
//删除评论
StringBuilder strSql5 = new StringBuilder();
strSql5.Append("delete from wx_shop_comment ");
strSql5.Append(" where productId=@productId ");
SqlParameter[] parameters5 = {
new SqlParameter("@productId", SqlDbType.Int,4)};
parameters5[0].Value = id;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
//删除购物车
StringBuilder strSql6 = new StringBuilder();
strSql6.Append("delete from wx_shop_cart ");
strSql6.Append(" where productId=@productId ");
SqlParameter[] parameters6= {
new SqlParameter("@productId", SqlDbType.Int,4)};
parameters6[0].Value = id;
cmd = new CommandInfo(strSql6.ToString(), parameters6);
sqllist.Add(cmd);
//删除主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from wx_shop_product ");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = id;
cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.wx_shop_product model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into wx_shop_product(");
strSql.Append("wid,categoryId,brandId,sku,productName,shortDesc,unit,weight,description,seo_title,seo_keywords,seo_description,focusImgUrl,thumbnailsUrll,recommended,latest,hotsale,specialOffer,costPrice,marketPrice,salePrice,upselling,stock,addDate,vistiCounts,sort_id,productionDate,ExpiryEndDate,updateDate,catalogId)");
strSql.Append(" values (");
strSql.Append("@wid,@categoryId,@brandId,@sku,@productName,@shortDesc,@unit,@weight,@description,@seo_title,@seo_keywords,@seo_description,@focusImgUrl,@thumbnailsUrll,@recommended,@latest,@hotsale,@specialOffer,@costPrice,@marketPrice,@salePrice,@upselling,@stock,@addDate,@vistiCounts,@sort_id,@productionDate,@ExpiryEndDate,@updateDate,@catalogId)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@wid", SqlDbType.Int,4),
new SqlParameter("@categoryId", SqlDbType.Int,4),
new SqlParameter("@brandId", SqlDbType.Int,4),
new SqlParameter("@sku", SqlDbType.VarChar,20),
new SqlParameter("@productName", SqlDbType.VarChar,500),
new SqlParameter("@shortDesc", SqlDbType.VarChar,500),
new SqlParameter("@unit", SqlDbType.VarChar,10),
new SqlParameter("@weight", SqlDbType.Float,8),
new SqlParameter("@description", SqlDbType.VarChar,2000),
new SqlParameter("@seo_title", SqlDbType.VarChar,200),
new SqlParameter("@seo_keywords", SqlDbType.VarChar,300),
new SqlParameter("@seo_description", SqlDbType.VarChar,500),
new SqlParameter("@focusImgUrl", SqlDbType.VarChar,800),
new SqlParameter("@thumbnailsUrll", SqlDbType.VarChar,800),
new SqlParameter("@recommended", SqlDbType.Bit,1),
new SqlParameter("@latest", SqlDbType.Bit,1),
new SqlParameter("@hotsale", SqlDbType.Bit,1),
new SqlParameter("@specialOffer", SqlDbType.Bit,1),
new SqlParameter("@costPrice", SqlDbType.Float,8),
new SqlParameter("@marketPrice", SqlDbType.Float,8),
new SqlParameter("@salePrice", SqlDbType.Float,8),
new SqlParameter("@upselling", SqlDbType.Bit,1),
new SqlParameter("@stock", SqlDbType.Int,4),
new SqlParameter("@addDate", SqlDbType.DateTime),
new SqlParameter("@vistiCounts", SqlDbType.Int,4),
new SqlParameter("@sort_id", SqlDbType.Int,4),
new SqlParameter("@productionDate", SqlDbType.DateTime),
new SqlParameter("@ExpiryEndDate", SqlDbType.DateTime),
new SqlParameter("@updateDate", SqlDbType.DateTime),
new SqlParameter("@catalogId", SqlDbType.Int,4),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.wid;
parameters[1].Value = model.categoryId;
parameters[2].Value = model.brandId;
parameters[3].Value = model.sku;
parameters[4].Value = model.productName;
parameters[5].Value = model.shortDesc;
parameters[6].Value = model.unit;
parameters[7].Value = model.weight;
parameters[8].Value = model.description;
parameters[9].Value = model.seo_title;
parameters[10].Value = model.seo_keywords;
parameters[11].Value = model.seo_description;
parameters[12].Value = model.focusImgUrl;
parameters[13].Value = model.thumbnailsUrll;
parameters[14].Value = model.recommended;
parameters[15].Value = model.latest;
parameters[16].Value = model.hotsale;
parameters[17].Value = model.specialOffer;
parameters[18].Value = model.costPrice;
parameters[19].Value = model.marketPrice;
parameters[20].Value = model.salePrice;
parameters[21].Value = model.upselling;
parameters[22].Value = model.stock;
parameters[23].Value = model.addDate;
parameters[24].Value = model.vistiCounts;
parameters[25].Value = model.sort_id;
parameters[26].Value = model.productionDate;
parameters[27].Value = model.ExpiryEndDate;
parameters[28].Value = model.updateDate;
parameters[29].Value = model.catalogId;
parameters[30].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
#region //图片相册
if (model.albums != null)
{
StringBuilder strSql2;
foreach (Model.wx_shop_albums modelt in model.albums)
{
strSql2 = new StringBuilder();
strSql2.Append("insert into wx_shop_albums(");
strSql2.Append("productId,thumb_path,original_path,remark)");
strSql2.Append(" values (");
strSql2.Append("@productId,@thumb_path,@original_path,@remark)");
SqlParameter[] parameters2 = {
new SqlParameter("@productId", SqlDbType.Int,4),
new SqlParameter("@thumb_path", SqlDbType.NVarChar,255),
new SqlParameter("@original_path", SqlDbType.NVarChar,255),
new SqlParameter("@remark", SqlDbType.NVarChar,500)};
parameters2[0].Direction = ParameterDirection.InputOutput;
parameters2[1].Value = modelt.thumb_path;
parameters2[2].Value = modelt.original_path;
parameters2[3].Value = modelt.remark;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
}
#endregion
#region //商品属性
if (model.attrs != null)
{
StringBuilder strSql3;
foreach (Model.wx_shop_productAttr_value modelt in model.attrs)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into wx_shop_productAttr_value(");
strSql3.Append("productId,attributeId,paValue)");
strSql3.Append(" values (");
strSql3.Append("@productId,@attributeId,@paValue)");
SqlParameter[] parameters3= {
new SqlParameter("@productId", SqlDbType.Int,4),
new SqlParameter("@attributeId", SqlDbType.Int,4),
new SqlParameter("@paValue", SqlDbType.NVarChar,300)};
parameters3[0].Direction = ParameterDirection.InputOutput;
parameters3[1].Value = modelt.attributeId;
parameters3[2].Value = modelt.paValue;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
}
}
#endregion
#region //商品配件sku|规格
if (model.skulist != null)
{
StringBuilder strSql4;
foreach (Model.wx_shop_sku modelt in model.skulist)
{
strSql4 = new StringBuilder();
strSql4.Append("insert into wx_shop_sku(");
strSql4.Append("productId,attributeId,attributeValue,price)");
strSql4.Append(" values (");
strSql4.Append("@productId,@attributeId,@attributeValue,@price)");
SqlParameter[] parameters4 = {
new SqlParameter("@productId", SqlDbType.Int,4),
new SqlParameter("@attributeId", SqlDbType.Int,4),
new SqlParameter("@attributeValue", SqlDbType.NVarChar,300),
new SqlParameter("@price", SqlDbType.Float,10),
};
parameters4[0].Direction = ParameterDirection.InputOutput;
parameters4[1].Value = modelt.attributeId;
parameters4[2].Value = modelt.attributeValue;
parameters4[3].Value = modelt.price;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
}
}
#endregion
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[30].Value;
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
//获取用户旧数据
Model.users model = GetModel(id);
if (model == null)
{
return false;
}
List<CommandInfo> sqllist = new List<CommandInfo>();
//删除积分记录
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "user_point_log ");
strSql1.Append(" where user_id=@id");
SqlParameter[] parameters1 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters1[0].Value = id;
CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1);
sqllist.Add(cmd);
//删除金额记录
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "user_amount_log ");
strSql2.Append(" where user_id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters2[0].Value = id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//删除短消息
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("delete from " + databaseprefix + "user_message ");
strSql3.Append(" where post_user_name=@post_user_name or accept_user_name=@accept_user_name");
SqlParameter[] parameters3 = {
new SqlParameter("@post_user_name", SqlDbType.NVarChar,100),
new SqlParameter("@accept_user_name", SqlDbType.NVarChar,100)};
parameters3[0].Value = model.user_name;
parameters3[1].Value = model.user_name;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
//删除申请码
StringBuilder strSql4 = new StringBuilder();
strSql4.Append("delete from " + databaseprefix + "user_code ");
strSql4.Append(" where user_id=@id");
SqlParameter[] parameters4 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters4[0].Value = id;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
//删除登录日志
StringBuilder strSql5 = new StringBuilder();
strSql5.Append("delete from " + databaseprefix + "user_login_log ");
strSql5.Append(" where user_id=@id");
SqlParameter[] parameters5 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters5[0].Value = id;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
//删除用户记录
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "users ");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = id;
cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 删除一条数据，及子表所有相关数据
/// </summary>
public bool Delete(int id)
{
List<CommandInfo> sqllist = new List<CommandInfo>();
//删除管理角色权限
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "manager_role_value ");
strSql.Append(" where role_id=@role_id");
SqlParameter[] parameters = {
new SqlParameter("@role_id", SqlDbType.Int,4)};
parameters[0].Value = id;
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//删除管理角色
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "manager_role ");
strSql2.Append(" where id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters2[0].Value = id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.user_amount_log model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "user_amount_log set ");
strSql.Append("user_id=@user_id,");
strSql.Append("user_name=@user_name,");
strSql.Append("type=@type,");
strSql.Append("order_no=@order_no,");
strSql.Append("trade_no=@trade_no,");
strSql.Append("payment_id=@payment_id,");
strSql.Append("value=@value,");
strSql.Append("remark=@remark,");
strSql.Append("status=@status,");
strSql.Append("add_time=@add_time,");
strSql.Append("complete_time=@complete_time");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@user_id", SqlDbType.Int,4),
new SqlParameter("@user_name", SqlDbType.NVarChar,100),
new SqlParameter("@type", SqlDbType.NVarChar,50),
new SqlParameter("@order_no", SqlDbType.NVarChar,100),
new SqlParameter("@trade_no", SqlDbType.NVarChar,100),
new SqlParameter("@payment_id", SqlDbType.Int,4),
new SqlParameter("@value", SqlDbType.Decimal,5),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@status", SqlDbType.TinyInt,1),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@complete_time", SqlDbType.DateTime),
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.type;
parameters[3].Value = model.order_no;
parameters[4].Value = model.trade_no;
parameters[5].Value = model.payment_id;
parameters[6].Value = model.value;
parameters[7].Value = model.remark;
parameters[8].Value = model.status;
parameters[9].Value = model.add_time;
parameters[10].Value = model.complete_time;
parameters[11].Value = model.id;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
if (model.status > 0)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update " + databaseprefix + "users set amount=amount+" + model.value);
strSql2.Append(" where id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters2[0].Value = model.user_id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
//取得相册MODEL
List<Model.article_albums> albumsList = new DAL.article_albums(databaseprefix).GetList(id);
//取得附件MODEL
List<Model.article_attach> attachList = new DAL.article_attach(databaseprefix).GetList(id);
//删除扩展字段表
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "article_attribute_value ");
strSql1.Append(" where article_id=@article_id ");
SqlParameter[] parameters1 = {
new SqlParameter("@article_id", SqlDbType.Int,4)};
parameters1[0].Value = id;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1);
sqllist.Add(cmd);
//删除图片相册
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "article_albums ");
strSql2.Append(" where article_id=@article_id ");
SqlParameter[] parameters2 = {
new SqlParameter("@article_id", SqlDbType.Int,4)};
parameters2[0].Value = id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//删除附件
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("delete from " + databaseprefix + "article_attach ");
strSql3.Append(" where article_id=@article_id ");
SqlParameter[] parameters3 = {
new SqlParameter("@article_id", SqlDbType.Int,4)};
parameters3[0].Value = id;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
//删除会员组价格
StringBuilder strSql4 = new StringBuilder();
strSql4.Append("delete from " + databaseprefix + "user_group_price ");
strSql4.Append(" where article_id=@article_id ");
SqlParameter[] parameters4 = {
new SqlParameter("@article_id", SqlDbType.Int,4)};
parameters4[0].Value = id;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
//删除评论
StringBuilder strSql5 = new StringBuilder();
strSql5.Append("delete from " + databaseprefix + "article_comment ");
strSql5.Append(" where article_id=@article_id ");
SqlParameter[] parameters5 = {
new SqlParameter("@article_id", SqlDbType.Int,4)};
parameters5[0].Value = id;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
//删除主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article ");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = id;
cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
new DAL.article_albums(databaseprefix).DeleteFile(albumsList); //删除图片
new DAL.article_attach(databaseprefix).DeleteFile(attachList); //删除附件
return true;
}
else
{
return false;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "article(");
strSql.Append("channel_id,category_id,call_index,title,link_url,img_url,seo_title,seo_keywords,seo_description,zhaiyao,content,sort_id,click,status,groupids_view,vote_id,is_top,is_red,is_hot,is_slide,is_sys,is_msg,user_name,add_time,update_time,wid)");
strSql.Append(" values (");
strSql.Append("@channel_id,@category_id,@call_index,@title,@link_url,@img_url,@seo_title,@seo_keywords,@seo_description,@zhaiyao,@content,@sort_id,@click,@status,@groupids_view,@vote_id,@is_top,@is_red,@is_hot,@is_slide,@is_sys,@is_msg,@user_name,@add_time,@update_time,@wid)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@channel_id", SqlDbType.Int,4),
new SqlParameter("@category_id", SqlDbType.Int,4),
new SqlParameter("@call_index", SqlDbType.NVarChar,50),
new SqlParameter("@title", SqlDbType.NVarChar,100),
new SqlParameter("@link_url", SqlDbType.NVarChar,255),
new SqlParameter("@img_url", SqlDbType.NVarChar,255),
new SqlParameter("@seo_title", SqlDbType.NVarChar,255),
new SqlParameter("@seo_keywords", SqlDbType.NVarChar,255),
new SqlParameter("@seo_description", SqlDbType.NVarChar,255),
new SqlParameter("@zhaiyao", SqlDbType.NVarChar,255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@sort_id", SqlDbType.Int,4),
new SqlParameter("@click", SqlDbType.Int,4),
new SqlParameter("@status", SqlDbType.TinyInt,1),
new SqlParameter("@groupids_view", SqlDbType.NVarChar,255),
new SqlParameter("@vote_id", SqlDbType.Int,4),
new SqlParameter("@is_top", SqlDbType.TinyInt,1),
new SqlParameter("@is_red", SqlDbType.TinyInt,1),
new SqlParameter("@is_hot", SqlDbType.TinyInt,1),
new SqlParameter("@is_slide", SqlDbType.TinyInt,1),
new SqlParameter("@is_sys", SqlDbType.TinyInt,1),
new SqlParameter("@is_msg", SqlDbType.TinyInt,1),
new SqlParameter("@user_name", SqlDbType.NVarChar,100),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@update_time", SqlDbType.DateTime),
new SqlParameter("@wid", SqlDbType.Int,4),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.category_id;
parameters[2].Value = model.call_index;
parameters[3].Value = model.title;
parameters[4].Value = model.link_url;
parameters[5].Value = model.img_url;
parameters[6].Value = model.seo_title;
parameters[7].Value = model.seo_keywords;
parameters[8].Value = model.seo_description;
parameters[9].Value = model.zhaiyao;
parameters[10].Value = model.content;
parameters[11].Value = model.sort_id;
parameters[12].Value = model.click;
parameters[13].Value = model.status;
parameters[14].Value = model.groupids_view;
parameters[15].Value = model.vote_id;
parameters[16].Value = model.is_top;
parameters[17].Value = model.is_red;
parameters[18].Value = model.is_hot;
parameters[19].Value = model.is_slide;
parameters[20].Value = model.is_sys;
parameters[21].Value = model.is_msg;
parameters[22].Value = model.user_name;
parameters[23].Value = model.add_time;
parameters[24].Value = model.update_time;
parameters[25].Value = model.wid;
parameters[26].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//添加扩展字段
StringBuilder strSql2 = new StringBuilder();
StringBuilder strFieldName = new StringBuilder(); //字段列表
StringBuilder strFieldVar = new StringBuilder(); //字段声明
SqlParameter[] parameters2 = new SqlParameter[model.fields.Count + 1];
int i = 1;
strFieldName.Append("article_id");
strFieldVar.Append("@article_id");
parameters2[0] = new SqlParameter("@article_id", SqlDbType.Int, 4);
parameters2[0].Direction = ParameterDirection.InputOutput;
foreach (KeyValuePair<string, string> kvp in model.fields)
{
strFieldName.Append("," + kvp.Key);
strFieldVar.Append(",@" + kvp.Key);
if (kvp.Value.Length <= 4000)
{
parameters2[i] = new SqlParameter("@" + kvp.Key, SqlDbType.NVarChar, kvp.Value.Length);
}
else
{
parameters2[i] = new SqlParameter("@" + kvp.Key, SqlDbType.NText);
}
parameters2[i].Value = kvp.Value;
i++;
}
strSql2.Append("insert into " + databaseprefix + "article_attribute_value(");
strSql2.Append(strFieldName.ToString() + ")");
strSql2.Append(" values (");
strSql2.Append(strFieldVar.ToString() + ")");
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//图片相册
if (model.albums != null)
{
StringBuilder strSql3;
foreach (Model.article_albums modelt in model.albums)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "article_albums(");
strSql3.Append("article_id,thumb_path,original_path,remark)");
strSql3.Append(" values (");
strSql3.Append("@article_id,@thumb_path,@original_path,@remark)");
SqlParameter[] parameters3 = {
new SqlParameter("@article_id", SqlDbType.Int,4),
new SqlParameter("@thumb_path", SqlDbType.NVarChar,255),
new SqlParameter("@original_path", SqlDbType.NVarChar,255),
new SqlParameter("@remark", SqlDbType.NVarChar,500)};
parameters3[0].Direction = ParameterDirection.InputOutput;
parameters3[1].Value = modelt.thumb_path;
parameters3[2].Value = modelt.original_path;
parameters3[3].Value = modelt.remark;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
}
}
//文章附件
if (model.attach != null)
{
StringBuilder strSql4;
foreach (Model.article_attach modelt in model.attach)
{
strSql4 = new StringBuilder();
strSql4.Append("insert into " + databaseprefix + "article_attach(");
strSql4.Append("article_id,file_name,file_path,file_size,file_ext,down_num,point)");
strSql4.Append(" values (");
strSql4.Append("@article_id,@file_name,@file_path,@file_size,@file_ext,@down_num,@point)");
SqlParameter[] parameters4 = {
new SqlParameter("@article_id", SqlDbType.Int,4),
new SqlParameter("@file_name", SqlDbType.NVarChar,100),
new SqlParameter("@file_path", SqlDbType.NVarChar,255),
new SqlParameter("@file_size", SqlDbType.Int,4),
new SqlParameter("@file_ext", SqlDbType.NVarChar,20),
new SqlParameter("@down_num", SqlDbType.Int,4),
new SqlParameter("@point", SqlDbType.Int,4)};
parameters4[0].Direction = ParameterDirection.InputOutput;
parameters4[1].Value = modelt.file_name;
parameters4[2].Value = modelt.file_path;
parameters4[3].Value = modelt.file_size;
parameters4[4].Value = modelt.file_ext;
parameters4[5].Value = modelt.down_num;
parameters4[6].Value = modelt.point;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
}
}
//用户组价格
if (model.group_price != null)
{
StringBuilder strSql5;
foreach (Model.user_group_price models in model.group_price)
{
strSql5 = new StringBuilder();
strSql5.Append("insert into " + databaseprefix + "user_group_price(");
strSql5.Append("article_id,group_id,price)");
strSql5.Append(" values (");
strSql5.Append("@article_id,@group_id,@price)");
SqlParameter[] parameters5 = {
new SqlParameter("@article_id", SqlDbType.Int,4),
new SqlParameter("@group_id", SqlDbType.Int,4),
new SqlParameter("@price", SqlDbType.Decimal,5)};
parameters5[0].Direction = ParameterDirection.InputOutput;
parameters5[1].Value = models.group_id;
parameters5[2].Value = models.price;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
}
}
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[26].Value;
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
//删除会员组价格
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "user_group_price ");
strSql1.Append(" where group_id=@group_id ");
SqlParameter[] parameters1 = {
new SqlParameter("@group_id", SqlDbType.Int,4)};
parameters1[0].Value = id;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1);
sqllist.Add(cmd);
//删除主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "user_groups ");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = id;
cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 增加一条数据,及其子表数据
/// </summary>
public int Add(Model.orders model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "orders(");
strSql.Append("order_no,trade_no,user_id,user_name,payment_id,payment_fee,payment_status,payment_time,express_id,express_no,express_fee,express_status,express_time,accept_name,post_code,telphone,mobile,area,address,message,remark,payable_amount,real_amount,order_amount,point,status,add_time,confirm_time,complete_time,wid,openid,modelName,modelCode,modelActionName,modelActionId,orderSubject,city,district,notify_id,pay_info,isSubscribe,fahuoCode,fahuoMsg)");
strSql.Append(" values (");
strSql.Append("@order_no,@trade_no,@user_id,@user_name,@payment_id,@payment_fee,@payment_status,@payment_time,@express_id,@express_no,@express_fee,@express_status,@express_time,@accept_name,@post_code,@telphone,@mobile,@area,@address,@message,@remark,@payable_amount,@real_amount,@order_amount,@point,@status,@add_time,@confirm_time,@complete_time,@wid,@openid,@modelName,@modelCode,@modelActionName,@modelActionId,@orderSubject,@city,@district,@notify_id,@pay_info,@isSubscribe,@fahuoCode,@fahuoMsg)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@order_no", SqlDbType.NVarChar,100),
new SqlParameter("@trade_no", SqlDbType.NVarChar,100),
new SqlParameter("@user_id", SqlDbType.Int,4),
new SqlParameter("@user_name", SqlDbType.NVarChar,100),
new SqlParameter("@payment_id", SqlDbType.Int,4),
new SqlParameter("@payment_fee", SqlDbType.Decimal,5),
new SqlParameter("@payment_status", SqlDbType.TinyInt,1),
new SqlParameter("@payment_time", SqlDbType.DateTime),
new SqlParameter("@express_id", SqlDbType.Int,4),
new SqlParameter("@express_no", SqlDbType.NVarChar,100),
new SqlParameter("@express_fee", SqlDbType.Decimal,5),
new SqlParameter("@express_status", SqlDbType.TinyInt,1),
new SqlParameter("@express_time", SqlDbType.DateTime),
new SqlParameter("@accept_name", SqlDbType.NVarChar,50),
new SqlParameter("@post_code", SqlDbType.NVarChar,20),
new SqlParameter("@telphone", SqlDbType.NVarChar,30),
new SqlParameter("@mobile", SqlDbType.NVarChar,20),
new SqlParameter("@area", SqlDbType.NVarChar,100),
new SqlParameter("@address", SqlDbType.NVarChar,500),
new SqlParameter("@message", SqlDbType.NVarChar,500),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@payable_amount", SqlDbType.Decimal,5),
new SqlParameter("@real_amount", SqlDbType.Decimal,5),
new SqlParameter("@order_amount", SqlDbType.Decimal,5),
new SqlParameter("@point", SqlDbType.Int,4),
new SqlParameter("@status", SqlDbType.TinyInt,1),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@confirm_time", SqlDbType.DateTime),
new SqlParameter("@complete_time", SqlDbType.DateTime),
new SqlParameter("@wid", SqlDbType.Int,4),
new SqlParameter("@openid", SqlDbType.VarChar,100),
new SqlParameter("@modelName", SqlDbType.VarChar,300),
new SqlParameter("@modelCode", SqlDbType.VarChar,50),
new SqlParameter("@modelActionName", SqlDbType.VarChar,500),
new SqlParameter("@modelActionId", SqlDbType.Int,4),
new SqlParameter("@orderSubject", SqlDbType.VarChar,500),
new SqlParameter("@city", SqlDbType.VarChar,100),
new SqlParameter("@district", SqlDbType.VarChar,100),
new SqlParameter("@notify_id", SqlDbType.VarChar,128),
new SqlParameter("@pay_info", SqlDbType.VarChar,200),
new SqlParameter("@isSubscribe", SqlDbType.Bit,1),
new SqlParameter("@fahuoCode", SqlDbType.VarChar,50),
new SqlParameter("@fahuoMsg", SqlDbType.VarChar,500),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.order_no;
parameters[1].Value = model.trade_no;
parameters[2].Value = model.user_id;
parameters[3].Value = model.user_name;
parameters[4].Value = model.payment_id;
parameters[5].Value = model.payment_fee;
parameters[6].Value = model.payment_status;
parameters[7].Value = model.payment_time;
parameters[8].Value = model.express_id;
parameters[9].Value = model.express_no;
parameters[10].Value = model.express_fee;
parameters[11].Value = model.express_status;
parameters[12].Value = model.express_time;
parameters[13].Value = model.accept_name;
parameters[14].Value = model.post_code;
parameters[15].Value = model.telphone;
parameters[16].Value = model.mobile;
parameters[17].Value = model.area;
parameters[18].Value = model.address;
parameters[19].Value = model.message;
parameters[20].Value = model.remark;
parameters[21].Value = model.payable_amount;
parameters[22].Value = model.real_amount;
parameters[23].Value = model.order_amount;
parameters[24].Value = model.point;
parameters[25].Value = model.status;
parameters[26].Value = model.add_time;
parameters[27].Value = model.confirm_time;
parameters[28].Value = model.complete_time;
parameters[29].Value = model.wid;
parameters[30].Value = model.openid;
parameters[31].Value = model.modelName;
parameters[32].Value = model.modelCode;
parameters[33].Value = model.modelActionName;
parameters[34].Value = model.modelActionId;
parameters[35].Value = model.orderSubject;
parameters[36].Value = model.city;
parameters[37].Value = model.district;
parameters[38].Value = model.notify_id;
parameters[39].Value = model.pay_info;
parameters[40].Value = model.isSubscribe;
parameters[41].Value = model.fahuoCode;
parameters[42].Value = model.fahuoMsg;
parameters[43].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//订单商品列表
if (model.order_goods != null)
{
StringBuilder strSql2;
foreach (Model.order_goods models in model.order_goods)
{
strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "order_goods(");
strSql2.Append("order_id,goods_id,goods_title,goods_price,real_price,quantity,point)");
strSql2.Append(" values (");
strSql2.Append("@order_id,@goods_id,@goods_title,@goods_price,@real_price,@quantity,@point)");
SqlParameter[] parameters2 = {
new SqlParameter("@order_id", SqlDbType.Int,4),
new SqlParameter("@goods_id", SqlDbType.Int,4),
new SqlParameter("@goods_title", SqlDbType.NVarChar,100),
new SqlParameter("@goods_price", SqlDbType.Decimal,5),
new SqlParameter("@real_price", SqlDbType.Decimal,5),
new SqlParameter("@quantity", SqlDbType.Int,4),
new SqlParameter("@point", SqlDbType.Int,4)};
parameters2[0].Direction = ParameterDirection.InputOutput;
parameters2[1].Value = models.goods_id;
parameters2[2].Value = models.goods_title;
parameters2[3].Value = models.goods_price;
parameters2[4].Value = models.real_price;
parameters2[5].Value = models.quantity;
parameters2[6].Value = models.point;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
}
}
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[43].Value;
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
List<CommandInfo> sqllist = new List<CommandInfo>();
//分店
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from wx_ucard_store_fendian ");
strSql2.Append(" where sId=@sId ");
SqlParameter[] parameters2 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters2[0].Value = id;
CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//删除等级
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("delete from wx_ucard_udegree ");
strSql3.Append(" where sid=@sid ");
SqlParameter[] parameters3 = {
new SqlParameter("@sid", SqlDbType.Int,4)};
parameters3[0].Value = id;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
//卡面设置
StringBuilder strSql4 = new StringBuilder();
strSql4.Append("delete from wx_ucard_cardinfo ");
strSql4.Append(" where sId=@sId ");
SqlParameter[] parameters4 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters4[0].Value = id;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
//通知
StringBuilder strSql5 = new StringBuilder();
strSql5.Append("delete from wx_ucard_notice ");
strSql5.Append(" where sId=@sId ");
SqlParameter[] parameters5 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters5[0].Value = id;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
//特权
StringBuilder strSql6 = new StringBuilder();
strSql6.Append("delete from wx_ucard_privileges ");
strSql6.Append(" where sId=@sId ");
SqlParameter[] parameters6 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters6[0].Value = id;
cmd = new CommandInfo(strSql6.ToString(), parameters6);
sqllist.Add(cmd);
//优惠券
StringBuilder strSql7 = new StringBuilder();
strSql7.Append("delete from wx_ucard_ticket ");
strSql7.Append(" where sId=@sId ");
SqlParameter[] parameters7 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters7[0].Value = id;
cmd = new CommandInfo(strSql7.ToString(), parameters7);
sqllist.Add(cmd);
//礼品券
StringBuilder strSql8 = new StringBuilder();
strSql8.Append("delete from wx_ucard_gift ");
strSql8.Append(" where sId=@sId ");
SqlParameter[] parameters8 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters8[0].Value = id;
cmd = new CommandInfo(strSql8.ToString(), parameters8);
sqllist.Add(cmd);
//会员
StringBuilder strSql9 = new StringBuilder();
strSql9.Append("delete from wx_ucard_users ");
strSql9.Append(" where sId=@sId ");
SqlParameter[] parameters9 = {
new SqlParameter("@sId", SqlDbType.Int,4)};
parameters9[0].Value = id;
cmd = new CommandInfo(strSql9.ToString(), parameters9);
sqllist.Add(cmd);
////会员领卡信息
//StringBuilder strSql10 = new StringBuilder();
//strSql10.Append("delete from wx_ucard_usercard ");
//strSql10.Append(" where sid=@sid ");
//SqlParameter[] parameters10 = {
// new SqlParameter("@sId", SqlDbType.Int,4)};
//parameters10[0].Value = id;
//cmd = new CommandInfo(strSql10.ToString(), parameters10);
//sqllist.Add(cmd);
//主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from wx_ucard_store ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = id;
cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_attribute_field model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "article_attribute_field(");
strSql.Append("name,title,control_type,data_type,data_length,data_place,item_option,default_value,is_required,is_password,is_html,editor_type,valid_tip_msg,valid_error_msg,valid_pattern,sort_id,is_sys)");
strSql.Append(" values (");
strSql.Append("@name,@title,@control_type,@data_type,@data_length,@data_place,@item_option,@default_value,@is_required,@is_password,@is_html,@editor_type,@valid_tip_msg,@valid_error_msg,@valid_pattern,@sort_id,@is_sys)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@name", SqlDbType.NVarChar,100),
new SqlParameter("@title", SqlDbType.NVarChar,100),
new SqlParameter("@control_type", SqlDbType.NVarChar,50),
new SqlParameter("@data_type", SqlDbType.NVarChar,50),
new SqlParameter("@data_length", SqlDbType.Int,4),
new SqlParameter("@data_place", SqlDbType.TinyInt,1),
new SqlParameter("@item_option", SqlDbType.NText),
new SqlParameter("@default_value", SqlDbType.NText),
new SqlParameter("@is_required", SqlDbType.TinyInt,1),
new SqlParameter("@is_password", SqlDbType.TinyInt,1),
new SqlParameter("@is_html", SqlDbType.TinyInt,1),
new SqlParameter("@editor_type", SqlDbType.TinyInt,1),
new SqlParameter("@valid_tip_msg", SqlDbType.NVarChar,255),
new SqlParameter("@valid_error_msg", SqlDbType.NVarChar,255),
new SqlParameter("@valid_pattern", SqlDbType.NVarChar,500),
new SqlParameter("@sort_id", SqlDbType.Int,4),
new SqlParameter("@is_sys", SqlDbType.TinyInt,1),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.name;
parameters[1].Value = model.title;
parameters[2].Value = model.control_type;
parameters[3].Value = model.data_type;
parameters[4].Value = model.data_length;
parameters[5].Value = model.data_place;
parameters[6].Value = model.item_option;
parameters[7].Value = model.default_value;
parameters[8].Value = model.is_required;
parameters[9].Value = model.is_password;
parameters[10].Value = model.is_html;
parameters[11].Value = model.editor_type;
parameters[12].Value = model.valid_tip_msg;
parameters[13].Value = model.valid_error_msg;
parameters[14].Value = model.valid_pattern;
parameters[15].Value = model.sort_id;
parameters[16].Value = model.is_sys;
parameters[17].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//增加扩展字段表中一列
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("alter table " + databaseprefix + "article_attribute_value add " + model.name + " " + model.data_type);
SqlParameter[] parameters2 = { };
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[17].Value;
}
/// <summary>
/// 新增积分记录，比如签到
/// </summary>
/// <param name="model"></param>
/// <param name="isEW">额外的奖励,1为连续6天签到</param>
/// <returns></returns>
public int AddJiFen(Model.wx_ucard_users_consumeinfo model, int isEW)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into wx_ucard_users_consumeinfo(");
strSql.Append("sId,uid,moduleType,moduleTypeId,moduleActionName,moduleActionId,cScoreType,score,cMoneyType,consumeMoney,cContent,remark,addTime,sort_id)");
strSql.Append(" values (");
strSql.Append("@sId,@uid,@moduleType,@moduleTypeId,@moduleActionName,@moduleActionId,@cScoreType,@score,@cMoneyType,@consumeMoney,@cContent,@remark,@addTime,@sort_id)");
strSql.Append(";set @ReturnValue= @@IDENTITY ");
SqlParameter[] parameters = {
new SqlParameter("@sId", SqlDbType.Int,4),
new SqlParameter("@uid", SqlDbType.Int,4),
new SqlParameter("@moduleType", SqlDbType.VarChar,80),
new SqlParameter("@moduleTypeId", SqlDbType.Int,4),
new SqlParameter("@moduleActionName", SqlDbType.VarChar,200),
new SqlParameter("@moduleActionId", SqlDbType.Int,4),
new SqlParameter("@cScoreType", SqlDbType.Int,4),
new SqlParameter("@score", SqlDbType.Int,4),
new SqlParameter("@cMoneyType", SqlDbType.Int,4),
new SqlParameter("@consumeMoney", SqlDbType.Float,8),
new SqlParameter("@cContent", SqlDbType.VarChar,800),
new SqlParameter("@remark", SqlDbType.VarChar,300),
new SqlParameter("@addTime", SqlDbType.DateTime),
new SqlParameter("@sort_id", SqlDbType.Int,4),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.sId;
parameters[1].Value = model.uid;
parameters[2].Value = model.moduleType;
parameters[3].Value = 1;
if (isEW == 1)
{
parameters[4].Value = "连续6天签到奖励";
}
else
{
parameters[4].Value = model.moduleActionName;
}
parameters[5].Value = 0;
parameters[6].Value = 1;
parameters[7].Value = model.score;
parameters[8].Value = model.cMoneyType;
parameters[9].Value = model.consumeMoney;
parameters[10].Value = "";
parameters[11].Value = "";
parameters[12].Value = DateTime.Now;
parameters[13].Value = 0;
parameters[14].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update wx_ucard_users set qdScore=qdScore+@score,ttScore=ttScore+@score where id=@id");
SqlParameter[] parameters2 = {
new SqlParameter("@score", SqlDbType.Int,4),
new SqlParameter("@id", SqlDbType.Int,4),
};
parameters2[0].Value = model.score;
parameters2[1].Value = model.uid;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[14].Value;
}
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="model"></param>
/// <param name="isXiaoFei">是否为消费积分</param>
/// <returns></returns>
public int Add(WechatBuilder.Model.wx_ucard_users_consumeinfo model,bool isXiaoFei)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into wx_ucard_users_consumeinfo(");
strSql.Append("sId,uid,moduleType,moduleTypeId,moduleActionName,moduleActionId,cScoreType,score,cMoneyType,consumeMoney,cContent,remark,addTime,sort_id,sn,operName,pwd)");
strSql.Append(" values (");
strSql.Append("@sId,@uid,@moduleType,@moduleTypeId,@moduleActionName,@moduleActionId,@cScoreType,@score,@cMoneyType,@consumeMoney,@cContent,@remark,@addTime,@sort_id,@sn,@operName,@pwd)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@sId", SqlDbType.Int,4),
new SqlParameter("@uid", SqlDbType.Int,4),
new SqlParameter("@moduleType", SqlDbType.VarChar,80),
new SqlParameter("@moduleTypeId", SqlDbType.Int,4),
new SqlParameter("@moduleActionName", SqlDbType.VarChar,200),
new SqlParameter("@moduleActionId", SqlDbType.Int,4),
new SqlParameter("@cScoreType", SqlDbType.Int,4),
new SqlParameter("@score", SqlDbType.Int,4),
new SqlParameter("@cMoneyType", SqlDbType.Int,4),
new SqlParameter("@consumeMoney", SqlDbType.Float,8),
new SqlParameter("@cContent", SqlDbType.VarChar,800),
new SqlParameter("@remark", SqlDbType.VarChar,300),
new SqlParameter("@addTime", SqlDbType.DateTime),
new SqlParameter("@sort_id", SqlDbType.Int,4),
new SqlParameter("@sn", SqlDbType.VarChar,50),
new SqlParameter("@operName", SqlDbType.VarChar,50),
new SqlParameter("@pwd", SqlDbType.VarChar,50),
new SqlParameter("@ReturnValue",SqlDbType.Int)};
parameters[0].Value = model.sId;
parameters[1].Value = model.uid;
parameters[2].Value = model.moduleType;
parameters[3].Value = model.moduleTypeId;
parameters[4].Value = model.moduleActionName;
parameters[5].Value = model.moduleActionId;
parameters[6].Value = model.cScoreType;
parameters[7].Value = model.score;
parameters[8].Value = model.cMoneyType;
parameters[9].Value = model.consumeMoney;
parameters[10].Value = model.cContent;
parameters[11].Value = model.remark;
parameters[12].Value = model.addTime;
parameters[13].Value = model.sort_id;
parameters[14].Value = model.sn;
parameters[15].Value = model.operName;
parameters[16].Value = model.pwd;
parameters[17].Direction = ParameterDirection.Output;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
StringBuilder strSql2 = new StringBuilder();
string xfStr = "";
if (isXiaoFei)
{
xfStr = ",consumeScore=consumeScore+" + (model.score == null ? 0 : model.score.Value);
}
strSql2.Append("update wx_ucard_users set ttScore=ttScore+@newscore , consumeMoney=consumeMoney+@newMoney" + xfStr + " where id=@uid");
SqlParameter[] parameters2 = {
new SqlParameter("@newscore", SqlDbType.Int,4),
new SqlParameter("@newMoney", SqlDbType.Decimal,4),
new SqlParameter("@uid", SqlDbType.Int,4)};
parameters2[0].Value = MyCommFun.Obj2Int(model.score);
parameters2[1].Value = MyCommFun.Obj2Decimal(model.consumeMoney, 0);
parameters2[2].Value = model.uid;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[17].Value;
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.manager_role model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "manager_role set ");
strSql.Append("role_name=@role_name,");
strSql.Append("role_type=@role_type,");
strSql.Append("is_sys=@is_sys");
strSql.Append(" where id=@id");
SqlParameter[] parameters = {
new SqlParameter("@role_name", SqlDbType.NVarChar,100),
new SqlParameter("@role_type", SqlDbType.TinyInt,1),
new SqlParameter("@is_sys", SqlDbType.TinyInt,1),
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = model.role_name;
parameters[1].Value = model.role_type;
parameters[2].Value = model.is_sys;
parameters[3].Value = model.id;
List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//先删除该角色所有权限
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "manager_role_value where role_id=@role_id ");
SqlParameter[] parameters2 = {
new SqlParameter("@role_id", SqlDbType.Int,4)};
parameters2[0].Value = model.id;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
//添加权限
if (model.manager_role_values != null)
{
StringBuilder strSql3;
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "manager_role_value(");
strSql3.Append("role_id,nav_name,action_type)");
strSql3.Append(" values (");
strSql3.Append("@role_id,@nav_name,@action_type)");
SqlParameter[] parameters3 = {
new SqlParameter("@role_id", SqlDbType.Int,4),
new SqlParameter("@nav_name", SqlDbType.NVarChar,100),
new SqlParameter("@action_type", SqlDbType.NVarChar,50)};
parameters3[0].Value = model.id;
parameters3[1].Value = modelt.nav_name;
parameters3[2].Value = modelt.action_type;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
}
}
int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
