/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM(");
strSql.Append("select S.*,P.img_url,P.title as ptitle,P.remark,P.type,P.poundage_type,P.poundage_amount,P.redirect_url,P.return_url,P.notify_url,P.is_lock");
strSql.Append(" from " + databaseprefix + "payment as P INNER JOIN " + databaseprefix + "site_payment as S ON P.id=S.payment_id");
strSql.Append(") as temp_payment");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_attribute_field model = GetModel(id);//取得扩展字段实体
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//删除所关联的频道数据表相关列
DataTable dt = new DAL.site_channel(databaseprefix).GetFieldList(conn, trans, id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + model.name + "'");
if (rowsCount > 0)
{
//删除频道数据表一列
WriteDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " drop column " + model.name);
}
}
}
//删除频道关联字段表
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "site_channel_field");
strSql1.Append(" where field_id=@0");
WriteDataBase.Execute(conn, trans, strSql1.ToString(), id);
//删除扩展字段主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id=@0");
WriteDataBase.Execute(conn, trans, strSql.ToString(), id);
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 根据频道名称获取总记录数
/// </summary>
public int ArticleCount(string channel_name, int category_id, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (category_id > 0)
{
strSql.Append(" and category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
return(ReadDataBase.ExecuteScalar <int>(strSql.ToString()));
}
/// <summary>
/// 类别统计数量
/// </summary>
/// <param name="channel_id">频道ID</param>
/// <param name="category_id">类别ID</param>
/// <returns></returns>
public int GetCount(string channel_name, int category_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " s join " + databaseprefix + "article_category a on a.id =s.category_id where a.class_list like '%," + category_id + ",%' and s.status=0");
object obj = ReadDataBase.ExecuteScalar <object>(strSql.ToString());
if (null != obj)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public string GetToken(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 token from " + databaseprefix + "weixin_account");
strSql.Append(" where id=@0");
object obj = ReadDataBase.ExecuteScalar <object>(strSql.ToString(), id);
if (obj != null)
{
return(obj.ToString());
}
else
{
return(string.Empty);
}
}
/// <summary>
/// 删除一条数据，及子表所有相关数据
/// </summary>
public bool Delete(int id)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "order_goods ");
strSql2.Append(" where order_id=@0 ");
ReadDataBase.ExecuteScalar <int>(strSql2.ToString(), id);
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "orders ");
strSql.Append(" where id=@0");
int rowsAffected = WriteDataBase.Execute(strSql.ToString(), id);
return(rowsAffected > 0);
}
/// <summary>
/// 快捷添加系统默认导航，带事务
/// </summary>
public int Add(IDbConnection conn, IDbTransaction trans, string parent_name, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type, int is_lock)
{
//先根据名称查询该父ID
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from " + databaseprefix + "navigation");
strSql.Append(" where name=@0");
object obj = ReadDataBase.ExecuteScalar <object>(strSql.ToString(), parent_name);
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, is_lock));
}
/// <summary>
/// 根据频道名称获得查询分页数据
/// </summary>
public DataSet ArticleList(string channel_name, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (category_id > 0)
{
strSql.Append(" and category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 返回商品库存数量
/// </summary>
public int GetStockQuantity(string channel_name, int channel_id, int article_id, int goods_id)
{
if (goods_id > 0)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 stock_quantity ");
strSql.Append(" from " + databaseprefix + "article_goods");
strSql.Append(" where channel_id=" + channel_id + " and article_id=" + article_id + " and id=" + goods_id);
return(ReadDataBase.ExecuteScalar <int>(strSql.ToString()));
}
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 stock_quantity ");
strSql.Append(" from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where id=" + article_id);
return(ReadDataBase.ExecuteScalar <int>(strSql.ToString()));
}
}
/// <summary>
/// 检查更新Tags标签及关系，带事务
/// </summary>
public void Update(IDbConnection conn, IDbTransaction trans, string tags_title, int channel_id, int article_id)
{
int tagsId = 0;
//检查该Tags标签是否已存在
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from " + databaseprefix + "article_tags");
strSql.Append(" where title=@0");
object obj1 = ReadDataBase.ExecuteScalar <object>(conn, trans, strSql.ToString(), tags_title);
if (obj1 != null)
{
//存在则将ID赋值
tagsId = Convert.ToInt32(obj1);
}
//如果尚未创建该Tags标签则创建
if (tagsId == 0)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "article_tags(");
strSql2.Append("title,is_red,sort_id,add_time)");
strSql2.Append(" values (");
strSql2.Append("@0,@1,@2,@3)");
strSql2.Append(";SELECT @@@IDENTITY;");
object obj2 = WriteDataBase.ExecuteScalar <object>(conn, trans, strSql2.ToString(), tags_title, 0, 99, DateTime.Now);
if (obj2 != null)
{
//插入成功后返回ID
tagsId = Convert.ToInt32(obj2);
}
}
//匹配Tags标签与文章之间的关系
if (tagsId > 0)
{
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "article_tags_relation(");
strSql3.Append("channel_id,article_id,tag_id)");
strSql3.Append(" values (");
strSql3.Append("@0,@1,@2)");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), channel_id, article_id, tagsId);
}
}
/// <summary>
/// 根据频道名称及规格查询分页数据
/// </summary>
public DataSet ArticleList(string channel_name, int category_id, Dictionary <string, string> dicSpecIds, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
string specWhere = string.Empty;
foreach (KeyValuePair <string, string> kv in dicSpecIds)
{
if (Utils.StrToInt(kv.Value, 0) > 0)
{
if (!string.IsNullOrEmpty(specWhere))
{
specWhere += "and ";
}
specWhere += "B.spec_ids like '%," + kv.Value + ",%'";
}
}
if (!string.IsNullOrEmpty(specWhere))
{
specWhere = " and (" + specWhere + ")";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (category_id > 0)
{
strSql.Append(" and category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
if (!string.IsNullOrEmpty(specWhere))
{
strSql.Append(" and id in(");
strSql.Append("select A.id from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " as A," + databaseprefix + "article_goods as B");
strSql.Append(" where A.channel_id=B.channel_id and A.id=B.article_id " + specWhere);
strSql.Append(" group by A.id)");
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 查询是否存在列
/// </summary>
public bool Exists(string column_name)
{
//检查是否与文章字段相同
Model.article artModel = new Model.article();
//利用反射获得属性的所有公共属性
Type modelType = artModel.GetType();
PropertyInfo[] proInfo = modelType.GetProperties();
foreach (PropertyInfo pi in proInfo)
{
if (pi.Name.ToLower() == column_name.ToLower())
{
return(true);
}
}
//检查是否与扩展字段表列相同
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from " + databaseprefix + "article_attribute_field");
strSql.Append(" where name=@0 ");
return(ReadDataBase.ExecuteScalar <int>(strSql.ToString(), column_name) > 0);
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public List <Model.article> GetListPage(string channel_name, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
if (category_id > 0)
{
strSql.Append(" where category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
if (category_id > 0)
{
strSql.Append(" and " + strWhere);
}
else
{
strSql.Append(" where " + strWhere);
}
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(GetModelList(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_category model, int role_id)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
#region 添加主表数据====================
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder(); //数据字段
StringBuilder str2 = new StringBuilder(); //数据参数
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("insert into " + databaseprefix + "article_category(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
str1.Append(pi.Name + ","); //拼接字段
str2.Append("@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(") values (");
strSql.Append(str2.ToString().Trim(','));
strSql.Append(") ");
strSql.Append(";SELECT @@@IDENTITY;");
object obj = WriteDataBase.ExecuteScalar <object>(conn, trans, strSql.ToString(), paras.ToArray());
model.id = Convert.ToInt32(obj);
if (model.parent_id > 0)
{
Model.article_category model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
//添加权限菜单
object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务
if (null != name)
{
//自动分级
string parent_name = "channel_" + name.ToString() + "_category";
if (model.parent_id > 0)
{
parent_name += "_" + model.parent_id;
}
new DAL.navigation(databaseprefix).Add(conn, trans, parent_name, "channel_" + name.ToString() + "_category_" + model.id, model.title, "", model.sort_id, model.channel_id, "Show", 1);
if (role_id > 0)
{
Model.manager_role_value valModel = new Model.manager_role_value();
valModel.role_id = role_id;
valModel.nav_name = "channel_" + name.ToString() + "_category_" + model.id;
valModel.action_type = "Show";
new DAL.manager_role_value(databaseprefix).Add(valModel);
}
}
//修改节点列表和深度
WriteDataBase.Execute(conn, trans, "update " + databaseprefix + "article_category set class_list='" + model.class_list + "', class_layer=" + model.class_layer + " where id=" + model.id); //带事务
#endregion
#region 栏目规格===========================
if (model.category_specs != null)
{
StringBuilder strSql3;
foreach (Model.article_category_spec modelt in model.category_specs)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "article_category_spec(");
strSql3.Append("category_id,spec_id)");
strSql3.Append(" values (");
strSql3.Append("@0,@1)");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), model.id, modelt.spec_id);
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.article_attribute_field model)
{
int i = 0;
Model.article_attribute_field oldModel = GetModel(model.id);//取到旧的数据
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//修改主表信息
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "article_attribute_field set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
str1.Append(pi.Name + "=@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(" where id=@" + i + " ");
paras.Add(model.id);
WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray());
//检查字段名和类型有无变化
if (oldModel.name.ToLower() != model.name.ToLower() || oldModel.data_type.ToLower() != model.data_type.ToLower())
{
DataTable dt = new DAL.site_channel(databaseprefix).GetFieldList(conn, trans, model.id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + oldModel.name + "'");
if (rowsCount > 0)
{
//修改列数据类型
if (oldModel.data_type.ToLower() != model.data_type.ToLower())
{
WriteDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " alter column " + oldModel.name + " " + model.data_type);
}
//修改列名
if (oldModel.name.ToLower() != model.name.ToLower())
{
WriteDataBase.Execute(conn, trans, "exec sp_rename '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "." + oldModel.name + "','" + model.name + "','column'");
}
}
}
}
}
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.site_channel model)
{
int i = 0;
Model.site_channel oldModel = GetModel(model.id); //旧的数据
//取得站点对应的导航ID
int parent_id = new DAL.sites(databaseprefix).GetSiteNavId(model.site_id);
if (parent_id == 0)
{
return(false);
}
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
#region 修改频道表======================
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "site_channel set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
//!pi.Name.Equals("channel_fields")
if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null)
{
str1.Append(pi.Name + "=@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(" where id=@" + i + " ");
paras.Add(model.id);
WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray());
#endregion
//删除已移除扩展字段及频道数据表列
FieldDelete(conn, trans, model, oldModel);
//编辑扩展字段及频道数据表
FieldUpdate(conn, trans, model, oldModel);
#region 缩略图尺寸
//删除已移除的尺寸
ThumDelete(conn, trans, model.channel_thums, model.id);
//添加扩展字段
if (model.channel_thums != null)
{
StringBuilder strSql2; //SQL字符串
StringBuilder str21; //数据库字段
StringBuilder str22; //声明参数
foreach (Model.site_channel_thum modelt in model.channel_thums)
{
if (modelt.id > 0)
{
i = 0;
//更新
strSql2 = new StringBuilder();
str21 = new StringBuilder();
PropertyInfo[] pros2 = modelt.GetType().GetProperties();
List <object> paras2 = new List <object>();
strSql2.Append("update " + databaseprefix + "site_channel_thum set ");
foreach (PropertyInfo pi in pros2)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals(""))
{
str21.Append(pi.Name + "=@" + i + ","); //声明参数
i++;
paras2.Add(pi.GetValue(modelt, null)); //对参数赋值
}
}
}
strSql2.Append(str21.ToString().Trim(','));
strSql2.Append(" where id=@" + i + " ");
paras2.Add(modelt.id);
WriteDataBase.Execute(conn, trans, strSql2.ToString(), paras2.ToArray());
}
else
{
i = 0;
//新增
strSql2 = new StringBuilder();
str21 = new StringBuilder();
str22 = new StringBuilder();
PropertyInfo[] pros2 = modelt.GetType().GetProperties();
List <object> paras2 = new List <object>();
strSql2.Append("insert into " + databaseprefix + "site_channel_thum(");
foreach (PropertyInfo pi in pros2)
{
if (!pi.Name.Equals("id"))
{
if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals(""))
{
str21.Append(pi.Name + ",");
str22.Append("@" + i + ",");
i++;
if (pi.Name.Equals("channel_id"))
{
paras2.Add(model.id); //将规则ID赋值
}
else
{
paras2.Add(pi.GetValue(modelt, null));
}
}
}
}
strSql2.Append(str21.ToString().Trim(','));
strSql2.Append(") values (");
strSql2.Append(str22.ToString().Trim(','));
strSql2.Append(") ");
WriteDataBase.Execute(conn, trans, strSql2.ToString(), paras2.ToArray());
}
}
}
#endregion
#region
int newNavId = 0;
DAL.navigation dal = new DAL.navigation(databaseprefix);
if (!dal.Exists("channel_" + model.name))
{
//添加导航菜单
newNavId = dal.Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show");
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "//admin/article/article_list", 99, model.id, "Show,View,Add,Edit,Delete,Audit");
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "//admin/article/category_list", 100, model.id, "Show,View,Add,Edit,Delete");
//是否开启规格
if (model.is_spec > 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "//admin/article/spec_list", 101, model.id, "Show,View,Add,Edit,Delete");
}
//是否开启评论
if (model.is_comment > 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "//admin/article/comment_list", 101, model.id, "Show,View,Delete,Audit,Reply");
}
//是否开启回收站
if (model.is_recycle > 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "//admin/article/recycle_list", 102, model.id, "Show,View,Edit,Delete,Audit");
}
}
else
{
//修改菜单
newNavId = new DAL.navigation(databaseprefix).GetId(conn, trans, "channel_" + oldModel.name);
dal.Update(conn, trans, "channel_" + oldModel.name, parent_id, "channel_" + model.name, model.title, model.sort_id);
dal.Update(conn, trans, "channel_" + oldModel.name + "_list", "channel_" + model.name + "_list"); //内容管理
dal.Update(conn, trans, "channel_" + oldModel.name + "_category", "channel_" + model.name + "_category"); //栏目类别
//是否开启规格
if (model.is_spec > 0)
{
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_spec'") == 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "//admin/article/spec_list", 101, model.id, "Show,View,Add,Edit,Delete");
}
else
{
dal.Update(conn, trans, "channel_" + oldModel.name + "_spec", "channel_" + model.name + "_spec"); //评论管理
}
}
else
{
dal.Delete(conn, trans, "channel_" + model.name + "_spec");
}
//是否开启评论
if (model.is_comment > 0)
{
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_comment'") == 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "//admin/article/comment_list", 101, model.id, "Show,View,Delete,Audit,Reply");
}
else
{
dal.Update(conn, trans, "channel_" + oldModel.name + "_comment", "channel_" + model.name + "_comment"); //评论管理
}
}
else
{
dal.Delete(conn, trans, "channel_" + model.name + "_comment");
}
//是否开启回收站
if (model.is_recycle > 0)
{
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_recycle'") == 0)
{
dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "article/recycle_list.aspx", 102, model.id, "Show,View,Edit,Delete,Audit");
}
else
{
dal.Update(conn, trans, "channel_" + oldModel.name + "_recycle", "channel_" + model.name + "_recycle"); //回收站
}
}
else
{
dal.Delete(conn, trans, "channel_" + model.name + "_recycle");
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.article_category model)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//先判断选中的父节点是否被包含
if (IsContainNode(model.id, model.parent_id))
{
//查找旧数据
Model.article_category oldModel = GetModel(model.id);
//查找旧父节点数据
string class_list = "," + model.parent_id + ",";
int class_layer = 1;
if (oldModel.parent_id > 0)
{
Model.article_category oldParentModel = GetModel(conn, trans, oldModel.parent_id); //带事务
class_list = oldParentModel.class_list + model.parent_id + ",";
class_layer = oldParentModel.class_layer + 1;
}
//先提升选中的父节点
WriteDataBase.Execute(conn, trans, "update " + databaseprefix + "article_category set parent_id=" + oldModel.parent_id + ",class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + model.parent_id); //带事务
UpdateChilds(conn, trans, model.parent_id); //带事务
}
//更新子节点
if (model.parent_id > 0)
{
Model.article_category model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
#region 修改主表数据==========================
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "article_category set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
str1.Append(pi.Name + "=@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(" where id=@" + i + " ");
paras.Add(model.id);
WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray());
//首先判断菜单是否存在
object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务
if (null != name)
{
//自动分级
string parent_name = "channel_" + name.ToString() + "_category";
if (model.parent_id > 0)
{
parent_name += "_" + model.parent_id;
}
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='" + "channel_" + name.ToString() + "_category_" + model.id + "'") == 0)
{
new DAL.navigation(databaseprefix).Add(conn, trans, parent_name, "channel_" + name.ToString() + "_category_" + model.id, model.title, "", model.sort_id, model.channel_id, "Show", 1);
}
else
{
int parent_id = ReadDataBase.ExecuteScalar <int>(conn, trans, "select top 1 id from [" + databaseprefix + "navigation] where name='" + parent_name + "'");
if (parent_id > 0)
{
new DAL.navigation(databaseprefix).Update(conn, trans, parent_name = "channel_" + name.ToString() + "_category_" + model.id, parent_id, parent_name, model.title, model.sort_id);
}
}
}
//更新子节点
UpdateChilds(conn, trans, model.id);
#endregion
#region 栏目规格===========================
//删除已删除的栏目规格
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "article_category_spec where category_id=" + model.id);
//添加/修改栏目规格
if (model.category_specs != null)
{
StringBuilder strSql3;
foreach (Model.article_category_spec modelt in model.category_specs)
{
strSql3 = new StringBuilder();
if (modelt.id > 0)
{
strSql3.Append("update " + databaseprefix + "article_category_spec ");
strSql3.Append("category_id=@0,");
strSql3.Append("spec_id=@1");
strSql3.Append(" where id=@2");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), modelt.category_id, modelt.spec_id, modelt.id);
}
else
{
strSql3.Append("insert into " + databaseprefix + "article_category_spec(");
strSql3.Append("category_id,spec_id)");
strSql3.Append(" values (");
strSql3.Append("@0,@1)");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), model.id, modelt.spec_id);
}
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_category model = GetModel(id);
if (null != model)
{
bool resault = true;
//修改以事件删除数据时，同时删时删除权限
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//频道名称
object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务
if (null != name)
{
//先判断选中是否存在子节点
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from [" + databaseprefix + "article_category] where parent_id=" + model.id) > 0)
{
WriteDataBase.Execute(conn, trans, "update [" + databaseprefix + "article_category] set parent_id=" + model.parent_id + " where parent_id=" + model.id); //带事务
UpdateChilds(conn, trans, model.parent_id); //带事务
//修改权限菜单
Model.navigation modelt = new DAL.navigation(databaseprefix).GetModel(conn, trans, "channel_" + name.ToString() + "_category_" + model.id);
if (null != modelt)
{
WriteDataBase.Execute(conn, trans, "update [" + databaseprefix + "navigation] set parent_id=" + modelt.parent_id + " where parent_id=" + modelt.id); //带事务
}
}
//删除权限菜单
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "navigation where channel_id=" + model.channel_id + " and name='" + "channel_" + name.ToString() + "_category_" + model.id + "'");//删除角色权限
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "manager_role_value where nav_name='" + "channel_" + name.ToString() + "_category_" + model.id + "'");
}
//删除栏目规格
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "article_category_spec");
strSql2.Append(" where category_id=@0");
WriteDataBase.Execute(conn, trans, strSql2.ToString(), id);
//删除类别
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_category where id=@0");
WriteDataBase.Execute(conn, trans, strSql.ToString(), id);
trans.Commit();
}
catch (Exception ex)
{
resault = false;
trans.Rollback();
}
}
}
if (resault)
{
//删除图片
Utils.DeleteFile(model.img_url);
}
return(resault);
}
return(false);
}
