public int Insert(AreaInfo areaInfo)
{
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
var parentAreaInfo = GetAreaInfo(areaInfo.ParentId);
InsertWithTrans(parentAreaInfo, areaInfo, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
AreaManager.ClearCache();
return(areaInfo.Id);
}
private void SetTaxisSubtract(int areaId, string parentsPath, int subtractNum)
{
var path = PageUtils.FilterSql(parentsPath);
string sqlString =
$"UPDATE siteserver_Area SET Taxis = Taxis - {subtractNum} WHERE Id = {areaId} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
AreaManager.ClearCache();
}
private void UpdateSubtractChildrenCount(string parentsPath, int subtractNum)
{
if (!string.IsNullOrEmpty(parentsPath))
{
var sqlString = string.Concat("UPDATE siteserver_Area SET ChildrenCount = ChildrenCount - ", subtractNum, " WHERE Id IN (", PageUtils.FilterSql(parentsPath), ")");
ExecuteNonQuery(sqlString);
AreaManager.ClearCache();
}
}
private void SetTaxisAdd(int areaId, string parentsPath, int addNum)
{
var path = PageUtils.FilterSql(parentsPath);
string sqlString =
$"UPDATE bairong_Area SET Taxis = Taxis + {addNum} WHERE AreaID = {areaId} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
AreaManager.ClearCache();
}
public void UpdateCountOfAdmin()
{
var areaIdList = AreaManager.GetAreaIdList();
foreach (var areaId in areaIdList)
{
var count = DataProvider.AdministratorDao.GetCountByAreaId(areaId);
string sqlString = $"UPDATE {TableName} SET CountOfAdmin = {count} WHERE Id = {areaId}";
ExecuteNonQuery(sqlString);
}
AreaManager.ClearCache();
}
public void UpdateCountOfAdmin()
{
var areaIdList = AreaManager.GetAreaIdList();
foreach (var areaId in areaIdList)
{
string sqlString =
$"UPDATE bairong_Area SET CountOfAdmin = (SELECT COUNT(*) AS CountOfAdmin FROM bairong_Administrator WHERE AreaID = {areaId}) WHERE AreaID = {areaId}";
ExecuteNonQuery(sqlString);
}
AreaManager.ClearCache();
}
public void Delete(int areaId)
{
var areaInfo = GetAreaInfo(areaId);
if (areaInfo != null)
{
var areaIdList = new List <int>();
if (areaInfo.ChildrenCount > 0)
{
areaIdList = GetIdListForDescendant(areaId);
}
areaIdList.Add(areaId);
string sqlString =
$"DELETE FROM siteserver_Area WHERE Id IN ({TranslateUtils.ToSqlInStringWithoutQuote(areaIdList)})";
int deletedNum;
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
deletedNum = ExecuteNonQuery(trans, sqlString);
if (deletedNum > 0)
{
string sqlStringTaxis =
$"UPDATE siteserver_Area SET Taxis = Taxis - {deletedNum} WHERE (Taxis > {areaInfo.Taxis})";
ExecuteNonQuery(trans, sqlStringTaxis);
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
UpdateIsLastNode(areaInfo.ParentId);
UpdateSubtractChildrenCount(areaInfo.ParentsPath, deletedNum);
}
AreaManager.ClearCache();
}
public void Update(AreaInfo areaInfo)
{
IDataParameter[] updateParms =
{
GetParameter(ParmName, DataType.VarChar, 255, areaInfo.AreaName),
GetParameter(ParmParentsPath, DataType.VarChar, 255, areaInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, areaInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, areaInfo.ChildrenCount),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, areaInfo.IsLastNode.ToString()),
GetParameter(ParmCountOfAdmin, DataType.Integer, areaInfo.CountOfAdmin),
GetParameter(ParmId, DataType.Integer, areaInfo.Id)
};
ExecuteNonQuery(SqlUpdate, updateParms);
AreaManager.ClearCache();
}
private void InsertWithTrans(AreaInfo parentInfo, AreaInfo areaInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
areaInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id;
areaInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(areaInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
areaInfo.Taxis = maxTaxis + 1;
}
else
{
areaInfo.ParentsPath = "0";
areaInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath("0");
areaInfo.Taxis = maxTaxis + 1;
}
var sqlInsert = "INSERT INTO siteserver_Area (AreaName, ParentID, ParentsPath, ParentsCount, ChildrenCount, IsLastNode, Taxis, CountOfAdmin) VALUES (@AreaName, @ParentID, @ParentsPath, @ParentsCount, @ChildrenCount, @IsLastNode, @Taxis, @CountOfAdmin)";
IDataParameter[] insertParms =
{
GetParameter(ParmName, DataType.VarChar, 255, areaInfo.AreaName),
GetParameter(ParmParentId, DataType.Integer, areaInfo.ParentId),
GetParameter(ParmParentsPath, DataType.VarChar, 255, areaInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, areaInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, 0),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, true.ToString()),
GetParameter(ParmTaxis, DataType.Integer, areaInfo.Taxis),
GetParameter(ParmCountOfAdmin, DataType.Integer, areaInfo.CountOfAdmin)
};
string sqlString = $"UPDATE siteserver_Area SET {SqlUtils.ToPlusSqlString("Taxis")} WHERE (Taxis >= {areaInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
areaInfo.Id = ExecuteNonQueryAndReturnId(TableName, nameof(AreaInfo.Id), trans, sqlInsert, insertParms);
if (!string.IsNullOrEmpty(areaInfo.ParentsPath) && areaInfo.ParentsPath != "0")
{
sqlString = $"UPDATE siteserver_Area SET {SqlUtils.ToPlusSqlString("ChildrenCount")} WHERE Id IN ({PageUtils.FilterSql(areaInfo.ParentsPath)})";
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE siteserver_Area SET IsLastNode = '{false}' WHERE ParentID = {areaInfo.ParentId}";
ExecuteNonQuery(trans, sqlString);
//sqlString =
// $"UPDATE siteserver_Area SET IsLastNode = 'True' WHERE (Id IN (SELECT TOP 1 Id FROM siteserver_Area WHERE ParentID = {areaInfo.ParentId} ORDER BY Taxis DESC))";
sqlString =
$"UPDATE siteserver_Area SET IsLastNode = '{true}' WHERE Id IN ({SqlUtils.ToInTopSqlString(TableName, "Id", $"WHERE ParentID = {areaInfo.ParentId}", "ORDER BY Taxis DESC", 1)})";
ExecuteNonQuery(trans, sqlString);
AreaManager.ClearCache();
}
