public int Insert(int publishmentSystemID, StoreCategoryInfo categoryInfo)
{
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
var parentCategoryInfo = GetCategoryInfo(categoryInfo.ParentID);
InsertWithTrans(publishmentSystemID, parentCategoryInfo, categoryInfo, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
return(categoryInfo.ID);
}
public int Insert(StoreCategoryInfo storeCategoryInfo)
{
var storeCategoryId = 0;
IDataParameter[] parms = null;
var sqlInsert = BaiRongDataProvider.TableStructureDao.GetInsertSqlString(storeCategoryInfo.ToNameValueCollection(), ConnectionString, TableName, out parms);
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
storeCategoryId = ExecuteNonQueryAndReturnId(trans, sqlInsert, parms);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
return(storeCategoryId);
}
private void InsertWithTrans(int publishmentSystemID, StoreCategoryInfo parentInfo, StoreCategoryInfo categoryInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
categoryInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.ID;
categoryInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(publishmentSystemID, categoryInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
categoryInfo.Taxis = maxTaxis + 1;
}
else
{
categoryInfo.ParentsPath = "0";
categoryInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath(publishmentSystemID, "0");
categoryInfo.Taxis = maxTaxis + 1;
}
var SQL_INSERT = "INSERT INTO wx_StoreCategory (PublishmentSystemID, CategoryName, ParentID, ParentsPath, ParentsCount, ChildCount, IsLastNode, Taxis) VALUES (@PublishmentSystemID, @CategoryName, @ParentID, @ParentsPath, @ParentsCount, @ChildCount, @IsLastNode, @Taxis)";
var insertParms = new IDataParameter[]
{
GetParameter(PARM_PUBLISHIMENTSYSTEMID, EDataType.Integer, categoryInfo.PublishmentSystemID),
GetParameter(PARM_NAME, EDataType.NVarChar, 255, categoryInfo.CategoryName),
GetParameter(PARM_PARENT_ID, EDataType.Integer, categoryInfo.ParentID),
GetParameter(PARM_PARENTS_PATH, EDataType.NVarChar, 255, categoryInfo.ParentsPath),
GetParameter(PARM_PARENTS_COUNT, EDataType.Integer, categoryInfo.ParentsCount),
GetParameter(PARM_CHILDREN_COUNT, EDataType.Integer, 0),
GetParameter(PARM_IS_LAST_NODE, EDataType.VarChar, 18, true.ToString()),
GetParameter(PARM_TAXIS, EDataType.Integer, categoryInfo.Taxis),
};
string sqlString = $"UPDATE wx_StoreCategory SET Taxis = Taxis + 1 WHERE (Taxis >= {categoryInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
ExecuteNonQuery(trans, SQL_INSERT, insertParms);
categoryInfo.ID = BaiRongDataProvider.DatabaseDao.GetSequence(trans, "wx_StoreCategory");
if (!string.IsNullOrEmpty(categoryInfo.ParentsPath))
{
sqlString = string.Concat("UPDATE wx_StoreCategory SET ChildCount = ChildCount + 1 WHERE ID in (", categoryInfo.ParentsPath, ")");
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE wx_StoreCategory SET IsLastNode = 'False' WHERE ParentID = {categoryInfo.ParentID}";
ExecuteNonQuery(trans, sqlString);
sqlString =
$"UPDATE wx_StoreCategory SET IsLastNode = 'True' WHERE (ID IN (SELECT TOP 1 ID FROM wx_StoreCategory WHERE ParentID = {categoryInfo.ParentID} ORDER BY Taxis DESC))";
ExecuteNonQuery(trans, sqlString);
}
public int Insert(StoreCategoryInfo storeCategoryInfo)
{
var storeCategoryID = 0;
IDataParameter[] parms = null;
var SQL_INSERT = BaiRongDataProvider.TableStructureDao.GetInsertSqlString(storeCategoryInfo.ToNameValueCollection(), ConnectionString, TABLE_NAME, out parms);
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
ExecuteNonQuery(trans, SQL_INSERT, parms);
storeCategoryID = BaiRongDataProvider.DatabaseDao.GetSequence(trans, TABLE_NAME);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
return(storeCategoryID);
}
public static CategoryTreeItem CreateInstance(StoreCategoryInfo categoryInfo)
{
var item = new CategoryTreeItem();
item.categoryInfo = categoryInfo;
return(item);
}
private void InsertWithTrans(int publishmentSystemId, StoreCategoryInfo parentInfo, StoreCategoryInfo categoryInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
categoryInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id;
categoryInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(publishmentSystemId, categoryInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
categoryInfo.Taxis = maxTaxis + 1;
}
else
{
categoryInfo.ParentsPath = "0";
categoryInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath(publishmentSystemId, "0");
categoryInfo.Taxis = maxTaxis + 1;
}
var sqlInsert = "INSERT INTO wx_StoreCategory (PublishmentSystemID, CategoryName, ParentID, ParentsPath, ParentsCount, ChildCount, IsLastNode, Taxis) VALUES (@PublishmentSystemID, @CategoryName, @ParentID, @ParentsPath, @ParentsCount, @ChildCount, @IsLastNode, @Taxis)";
var insertParms = new IDataParameter[]
{
GetParameter(ParmPublishimentsystemid, EDataType.Integer, categoryInfo.PublishmentSystemId),
GetParameter(ParmName, EDataType.NVarChar, 255, categoryInfo.CategoryName),
GetParameter(ParmParentId, EDataType.Integer, categoryInfo.ParentId),
GetParameter(ParmParentsPath, EDataType.NVarChar, 255, categoryInfo.ParentsPath),
GetParameter(ParmParentsCount, EDataType.Integer, categoryInfo.ParentsCount),
GetParameter(ParmChildrenCount, EDataType.Integer, 0),
GetParameter(ParmIsLastNode, EDataType.VarChar, 18, true.ToString()),
GetParameter(ParmTaxis, EDataType.Integer, categoryInfo.Taxis),
};
string sqlString = $"UPDATE wx_StoreCategory SET Taxis = Taxis + 1 WHERE (Taxis >= {categoryInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
categoryInfo.Id = ExecuteNonQueryAndReturnId(trans, sqlInsert, insertParms);
if (!string.IsNullOrEmpty(categoryInfo.ParentsPath))
{
sqlString = string.Concat("UPDATE wx_StoreCategory SET ChildCount = ChildCount + 1 WHERE ID in (", categoryInfo.ParentsPath, ")");
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE wx_StoreCategory SET IsLastNode = 'False' WHERE ParentID = {categoryInfo.ParentId}";
ExecuteNonQuery(trans, sqlString);
sqlString =
$"UPDATE wx_StoreCategory SET IsLastNode = 'True' WHERE (ID IN (SELECT TOP 1 ID FROM wx_StoreCategory WHERE ParentID = {categoryInfo.ParentId} ORDER BY Taxis DESC))";
ExecuteNonQuery(trans, sqlString);
}
public void Update(int publishmentSystemID, StoreCategoryInfo categoryInfo)
{
var updateParms = new IDataParameter[]
{
GetParameter(PARM_NAME, EDataType.NVarChar, 255, categoryInfo.CategoryName),
GetParameter(PARM_PARENTS_PATH, EDataType.NVarChar, 255, categoryInfo.ParentsPath),
GetParameter(PARM_PARENTS_COUNT, EDataType.Integer, categoryInfo.ParentsCount),
GetParameter(PARM_CHILDREN_COUNT, EDataType.Integer, categoryInfo.ChildCount),
GetParameter(PARM_IS_LAST_NODE, EDataType.VarChar, 18, categoryInfo.IsLastNode.ToString()),
GetParameter(PARM_ID, EDataType.Integer, categoryInfo.ID)
};
ExecuteNonQuery(SQL_UPDATE, updateParms);
}
public void Update(int publishmentSystemId, StoreCategoryInfo categoryInfo)
{
var updateParms = new IDataParameter[]
{
GetParameter(ParmName, EDataType.NVarChar, 255, categoryInfo.CategoryName),
GetParameter(ParmParentsPath, EDataType.NVarChar, 255, categoryInfo.ParentsPath),
GetParameter(ParmParentsCount, EDataType.Integer, categoryInfo.ParentsCount),
GetParameter(ParmChildrenCount, EDataType.Integer, categoryInfo.ChildCount),
GetParameter(ParmIsLastNode, EDataType.VarChar, 18, categoryInfo.IsLastNode.ToString()),
GetParameter(ParmId, EDataType.Integer, categoryInfo.Id)
};
ExecuteNonQuery(SqlUpdate, updateParms);
}
public StoreCategoryInfo GetStoreCategoryInfoByParentId(int publishmentSystemId, int parentId)
{
StoreCategoryInfo storeCategoryInfo = null;
string sqlWhere = $"WHERE PublishmentSystemID = {publishmentSystemId} AND ParentID = {parentId}";
var sqlSelect = BaiRongDataProvider.TableStructureDao.GetSelectSqlString(ConnectionString, TableName, 0, SqlUtils.Asterisk, sqlWhere, null);
using (var rdr = ExecuteReader(sqlSelect))
{
if (rdr.Read())
{
storeCategoryInfo = new StoreCategoryInfo(rdr);
}
rdr.Close();
}
return(storeCategoryInfo);
}
public StoreCategoryInfo GetStoreCategoryInfoByParentID(int publishmentSystemID, int parentID)
{
StoreCategoryInfo storeCategoryInfo = null;
string SQL_WHERE = $"WHERE PublishmentSystemID = {publishmentSystemID} AND ParentID = {parentID}";
var SQL_SELECT = BaiRongDataProvider.TableStructureDao.GetSelectSqlString(ConnectionString, TABLE_NAME, 0, SqlUtils.Asterisk, SQL_WHERE, null);
using (var rdr = ExecuteReader(SQL_SELECT))
{
if (rdr.Read())
{
storeCategoryInfo = new StoreCategoryInfo(rdr);
}
rdr.Close();
}
return(storeCategoryInfo);
}
public StoreCategoryInfo GetCategoryInfo(int categoryID)
{
StoreCategoryInfo categoryInfo = null;
var parms = new IDataParameter[]
{
GetParameter(PARM_ID, EDataType.Integer, categoryID)
};
using (var rdr = ExecuteReader(SQL_SELECT, parms))
{
if (rdr.Read())
{
categoryInfo = new StoreCategoryInfo(rdr);
}
rdr.Close();
}
return(categoryInfo);
}
public StoreCategoryInfo GetCategoryInfo(int categoryId)
{
StoreCategoryInfo categoryInfo = null;
var parms = new IDataParameter[]
{
GetParameter(ParmId, EDataType.Integer, categoryId)
};
using (var rdr = ExecuteReader(SqlSelect, parms))
{
if (rdr.Read())
{
categoryInfo = new StoreCategoryInfo(rdr);
}
rdr.Close();
}
return(categoryInfo);
}
public List <StoreCategoryInfo> GetStoreCategoryInfoList(int publishmentSystemID)
{
var list = new List <StoreCategoryInfo>();
var builder = new StringBuilder(
$"WHERE {StoreCategoryAttribute.PublishmentSystemID} = {publishmentSystemID}");
var SQL_SELECT = BaiRongDataProvider.TableStructureDao.GetSelectSqlString(ConnectionString, TABLE_NAME, 0, SqlUtils.Asterisk, builder.ToString(), "ORDER BY Taxis");
using (var rdr = ExecuteReader(SQL_SELECT))
{
while (rdr.Read())
{
var categoryInfo = new StoreCategoryInfo(rdr);
list.Add(categoryInfo);
}
rdr.Close();
}
return(list);
}
public List <StoreCategoryInfo> GetAllStoreCategoryInfoList(int publishmentSystemId)
{
var list = new List <StoreCategoryInfo>();
var builder = new StringBuilder(
$"WHERE {StoreCategoryAttribute.PublishmentSystemId} = {publishmentSystemId}");
var sqlSelect = BaiRongDataProvider.TableStructureDao.GetSelectSqlString(ConnectionString, TableName, 0, SqlUtils.Asterisk, builder.ToString(), "ORDER BY ID");
using (var rdr = ExecuteReader(sqlSelect))
{
while (rdr.Read())
{
var storeCategoryInfo = new StoreCategoryInfo(rdr);
list.Add(storeCategoryInfo);
}
rdr.Close();
}
return(list);
}
private ArrayList GetCategoryInfoArrayList(int publishmentSystemID)
{
var arraylist = new ArrayList();
var parms = new IDataParameter[]
{
GetParameter(PARM_PUBLISHIMENTSYSTEMID, EDataType.Integer, publishmentSystemID)
};
using (var rdr = ExecuteReader(SQL_SELECT_ALL, parms))
{
while (rdr.Read())
{
var categoryInfo = new StoreCategoryInfo(rdr);
arraylist.Add(categoryInfo);
}
rdr.Close();
}
return(arraylist);
}
private ArrayList GetCategoryInfoArrayList(int publishmentSystemId)
{
var arraylist = new ArrayList();
var parms = new IDataParameter[]
{
GetParameter(ParmPublishimentsystemid, EDataType.Integer, publishmentSystemId)
};
using (var rdr = ExecuteReader(SqlSelectAll, parms))
{
while (rdr.Read())
{
var categoryInfo = new StoreCategoryInfo(rdr);
arraylist.Add(categoryInfo);
}
rdr.Close();
}
return(arraylist);
}
public static string GetCategoryRowHtml(int publishmentSystemId, StoreCategoryInfo categoryInfo, ECategoryLoadingType loadingType, NameValueCollection additional)
{
var treeItem = CategoryTreeItem.CreateInstance(categoryInfo);
var title = treeItem.GetItemHtml(loadingType, additional, false);
var rowHtml = string.Empty;
if (loadingType == ECategoryLoadingType.Category)
{
var editUrl = string.Empty;
var upLink = string.Empty;
var downLink = string.Empty;
var checkBoxHtml = string.Empty;
var urlEdit = ModalStoreCategoryAdd.GetOpenWindowStringToEdit(publishmentSystemId, categoryInfo.Id);
editUrl = $@"<a href=""javascript:;"" onclick=""{urlEdit}"">编辑</a>";
var categoryUrl = GetRedirectUrl(publishmentSystemId, categoryInfo.Id);
string urlUp = $"{categoryUrl}&Subtract=True";
upLink = $@"<a href=""{urlUp}""><img src=""../Pic/icon/up.gif"" border=""0"" alt=""上升"" /></a>";
string urlDown = $"{categoryUrl}&Add=True";
downLink = $@"<a href=""{urlDown}""><img src=""../Pic/icon/down.gif"" border=""0"" alt=""下降"" /></a>";
checkBoxHtml = $"<input type='checkbox' name='CategoryIDCollection' value='{categoryInfo.Id}' />";
rowHtml = $@"
<tr treeItemLevel=""{categoryInfo.ParentsCount + 1}"">
<td>{title}</td>
<td class=""center"">{upLink}</td>
<td class=""center"">{downLink}</td>
<td class=""center"">{editUrl}</td>
<td class=""center"">{checkBoxHtml}</td>
</tr>
";
}
return(rowHtml);
}
public override void Submit_OnClick(object sender, EventArgs e)
{
var isChanged = false;
try
{
if (categoryID == 0)
{
var categoryInfo = new StoreCategoryInfo();
categoryInfo.PublishmentSystemID = PublishmentSystemID;
categoryInfo.CategoryName = CategoryName.Text;
categoryInfo.ParentID = TranslateUtils.ToInt(ParentID.SelectedValue);
DataProviderWX.StoreCategoryDAO.Insert(PublishmentSystemID, categoryInfo);
}
else
{
var categoryInfo = DataProviderWX.StoreCategoryDAO.GetCategoryInfo(categoryID);
categoryInfo.CategoryName = CategoryName.Text;
categoryInfo.ParentID = TranslateUtils.ToInt(ParentID.SelectedValue);
DataProviderWX.StoreCategoryDAO.Update(PublishmentSystemID, categoryInfo);;
}
LogUtils.AddLog(BaiRongDataProvider.AdministratorDao.UserName, "维护门店属性信息");
SuccessMessage("类别设置成功！");
isChanged = true;
}
catch (Exception ex)
{
FailMessage(ex, "类别设置失败！");
}
if (isChanged)
{
JsUtils.OpenWindow.CloseModalPage(Page);
}
}
public override void Submit_OnClick(object sender, EventArgs e)
{
var isChanged = false;
try
{
if (_categoryId == 0)
{
var categoryInfo = new StoreCategoryInfo();
categoryInfo.PublishmentSystemId = PublishmentSystemId;
categoryInfo.CategoryName = CategoryName.Text;
categoryInfo.ParentId = TranslateUtils.ToInt(ParentId.SelectedValue);
DataProviderWx.StoreCategoryDao.Insert(PublishmentSystemId, categoryInfo);
}
else
{
var categoryInfo = DataProviderWx.StoreCategoryDao.GetCategoryInfo(_categoryId);
categoryInfo.CategoryName = CategoryName.Text;
categoryInfo.ParentId = TranslateUtils.ToInt(ParentId.SelectedValue);
DataProviderWx.StoreCategoryDao.Update(PublishmentSystemId, categoryInfo);;
}
LogUtils.AddAdminLog(Body.AdministratorName, "维护门店属性信息");
SuccessMessage("类别设置成功！");
isChanged = true;
}
catch (Exception ex)
{
FailMessage(ex, "类别设置失败！");
}
if (isChanged)
{
PageUtils.CloseModalPage(Page);
}
}
