public static int Insert(DepartmentInfo dataInfo)
{
var sqlString = $@"INSERT INTO {TableName}
(
{nameof(DepartmentInfo.SiteId)},
{nameof(DepartmentInfo.DepartmentName)},
{nameof(DepartmentInfo.UserNames)},
{nameof(DepartmentInfo.Taxis)}
) VALUES (
@{nameof(DepartmentInfo.SiteId)},
@{nameof(DepartmentInfo.DepartmentName)},
@{nameof(DepartmentInfo.UserNames)},
@{nameof(DepartmentInfo.Taxis)}
)";
var parameters = new List <IDataParameter>
{
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.SiteId), dataInfo.SiteId),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.DepartmentName), dataInfo.DepartmentName),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.UserNames), dataInfo.UserNames),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Taxis), dataInfo.Taxis)
};
var departmentId = Context.DatabaseApi.ExecuteNonQueryAndReturnId(TableName, nameof(DepartmentInfo.Id),
Context.ConnectionString, sqlString, parameters.ToArray());
DepartmentManager.ClearCache(dataInfo.SiteId);
return(departmentId);
}
public int Insert(DepartmentInfo departmentInfo)
{
using (var conn = GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
var parentDepartmentInfo = GetDepartmentInfo(departmentInfo.ParentId);
InsertWithTrans(parentDepartmentInfo, departmentInfo, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
DepartmentManager.ClearCache();
return(departmentInfo.Id);
}
public int Insert(DepartmentInfo departmentInfo)
{
var departmentId = _repository.Insert(departmentInfo);
DepartmentManager.ClearCache(departmentInfo.SiteId);
return(departmentId);
}
public static void Delete(int siteId, int departmentId)
{
var sqlString =
$"DELETE FROM {TableName} WHERE {nameof(DepartmentInfo.Id)} = {departmentId}";
Context.DatabaseApi.ExecuteNonQuery(Context.ConnectionString, sqlString);
DepartmentManager.ClearCache(siteId);
}
private void SetTaxisSubtract(int id, string parentsPath, int subtractNum)
{
var path = PageUtils.FilterSql(parentsPath);
string sqlString =
$"UPDATE siteserver_Department SET Taxis = Taxis - {subtractNum} WHERE Id = {id} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
private void UpdateSubtractChildrenCount(string parentsPath, int subtractNum)
{
if (!string.IsNullOrEmpty(parentsPath))
{
var sqlString = string.Concat("UPDATE siteserver_Department SET ChildrenCount = ChildrenCount - ", subtractNum, " WHERE Id IN (", PageUtils.FilterSql(parentsPath), ")");
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
}
private void SetTaxisAdd(int departmentId, string parentsPath, int addNum)
{
var path = PageUtils.FilterSql(parentsPath);
string sqlString =
$"UPDATE bairong_Department SET Taxis = Taxis + {addNum} WHERE DepartmentID = {departmentId} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
private void SetTaxisAdd(int id, string parentsPath, int addNum)
{
var path = AttackUtils.FilterSql(parentsPath);
var sqlString =
$"UPDATE siteserver_Department SET Taxis = Taxis + {addNum} WHERE Id = {id} OR ParentsPath = '{path}' OR ParentsPath LIKE '{path},%'";
ExecuteNonQuery(sqlString);
DepartmentManager.ClearCache();
}
public void UpdateCountOfAdmin()
{
var idList = DepartmentManager.GetDepartmentIdList();
foreach (var departmentId in idList)
{
var count = DataProvider.AdministratorDao.GetCountByDepartmentId(departmentId);
string sqlString = $"UPDATE {TableName} SET CountOfAdmin = {count} WHERE Id = {departmentId}";
ExecuteNonQuery(sqlString);
}
DepartmentManager.ClearCache();
}
public void UpdateCountOfAdmin()
{
var departmentIdList = DepartmentManager.GetDepartmentIdList();
foreach (var departmentId in departmentIdList)
{
string sqlString =
$"UPDATE bairong_Department SET CountOfAdmin = (SELECT COUNT(*) AS CountOfAdmin FROM bairong_Administrator WHERE DepartmentID = {departmentId}) WHERE DepartmentID = {departmentId}";
ExecuteNonQuery(sqlString);
}
DepartmentManager.ClearCache();
}
public void Delete(int id)
{
var departmentInfo = GetDepartmentInfo(id);
if (departmentInfo != null)
{
var idList = new List <int>();
if (departmentInfo.ChildrenCount > 0)
{
idList = GetIdListForDescendant(id);
}
idList.Add(id);
string sqlString =
$"DELETE FROM siteserver_Department WHERE Id IN ({TranslateUtils.ToSqlInStringWithoutQuote(idList)})";
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_Department SET Taxis = Taxis - {deletedNum} WHERE (Taxis > {departmentInfo.Taxis})";
ExecuteNonQuery(trans, sqlStringTaxis);
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
UpdateIsLastNode(departmentInfo.ParentId);
UpdateSubtractChildrenCount(departmentInfo.ParentsPath, deletedNum);
}
DepartmentManager.ClearCache();
}
public void Update(DepartmentInfo departmentInfo)
{
var updateParms = new IDataParameter[]
{
GetParameter(ParmName, DataType.VarChar, 255, departmentInfo.DepartmentName),
GetParameter(ParmCode, DataType.VarChar, 50, departmentInfo.Code),
GetParameter(ParmParentsPath, DataType.VarChar, 255, departmentInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, departmentInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, departmentInfo.ChildrenCount),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, departmentInfo.IsLastNode.ToString()),
GetParameter(ParmSummary, DataType.VarChar, 255, departmentInfo.Summary),
GetParameter(ParmCountOfAdmin, DataType.Integer, departmentInfo.CountOfAdmin),
GetParameter(ParmId, DataType.Integer, departmentInfo.Id)
};
ExecuteNonQuery(SqlUpdate, updateParms);
DepartmentManager.ClearCache();
}
public static void Update(DepartmentInfo departmentInfo)
{
var sqlString = $@"UPDATE {TableName} SET
{nameof(DepartmentInfo.SiteId)} = @{nameof(DepartmentInfo.SiteId)},
{nameof(DepartmentInfo.DepartmentName)} = @{nameof(DepartmentInfo.DepartmentName)},
{nameof(DepartmentInfo.UserNames)} = @{nameof(DepartmentInfo.UserNames)},
{nameof(DepartmentInfo.Taxis)} = @{nameof(DepartmentInfo.Taxis)}
WHERE Id = @Id";
var parameters = new List <IDataParameter>
{
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.SiteId), departmentInfo.SiteId),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.DepartmentName), departmentInfo.DepartmentName),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.UserNames), departmentInfo.UserNames),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Taxis), departmentInfo.Taxis),
Context.DatabaseApi.GetParameter(nameof(DepartmentInfo.Id), departmentInfo.Id)
};
Context.DatabaseApi.ExecuteNonQuery(Context.ConnectionString, sqlString, parameters.ToArray());
DepartmentManager.ClearCache(departmentInfo.SiteId);
}
private void InsertWithTrans(DepartmentInfo parentInfo, DepartmentInfo departmentInfo, IDbTransaction trans)
{
if (parentInfo != null)
{
departmentInfo.ParentsPath = parentInfo.ParentsPath + "," + parentInfo.Id;
departmentInfo.ParentsCount = parentInfo.ParentsCount + 1;
var maxTaxis = GetMaxTaxisByParentPath(departmentInfo.ParentsPath);
if (maxTaxis == 0)
{
maxTaxis = parentInfo.Taxis;
}
departmentInfo.Taxis = maxTaxis + 1;
}
else
{
departmentInfo.ParentsPath = "0";
departmentInfo.ParentsCount = 0;
var maxTaxis = GetMaxTaxisByParentPath("0");
departmentInfo.Taxis = maxTaxis + 1;
}
var sqlInsert = "INSERT INTO siteserver_Department (DepartmentName, Code, ParentID, ParentsPath, ParentsCount, ChildrenCount, IsLastNode, Taxis, AddDate, Summary, CountOfAdmin) VALUES (@DepartmentName, @Code, @ParentID, @ParentsPath, @ParentsCount, @ChildrenCount, @IsLastNode, @Taxis, @AddDate, @Summary, @CountOfAdmin)";
var insertParms = new IDataParameter[]
{
GetParameter(ParmName, DataType.VarChar, 255, departmentInfo.DepartmentName),
GetParameter(ParmCode, DataType.VarChar, 50, departmentInfo.Code),
GetParameter(ParmParentId, DataType.Integer, departmentInfo.ParentId),
GetParameter(ParmParentsPath, DataType.VarChar, 255, departmentInfo.ParentsPath),
GetParameter(ParmParentsCount, DataType.Integer, departmentInfo.ParentsCount),
GetParameter(ParmChildrenCount, DataType.Integer, 0),
GetParameter(ParmIsLastNode, DataType.VarChar, 18, true.ToString()),
GetParameter(ParmTaxis, DataType.Integer, departmentInfo.Taxis),
GetParameter(ParmAddDate, DataType.DateTime, departmentInfo.AddDate),
GetParameter(ParmSummary, DataType.VarChar, 255, departmentInfo.Summary),
GetParameter(ParmCountOfAdmin, DataType.Integer, departmentInfo.CountOfAdmin)
};
string sqlString =
$"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("Taxis")} WHERE (Taxis >= {departmentInfo.Taxis})";
ExecuteNonQuery(trans, sqlString);
departmentInfo.Id = ExecuteNonQueryAndReturnId(TableName, nameof(DepartmentInfo.Id), trans, sqlInsert, insertParms);
if (!string.IsNullOrEmpty(departmentInfo.ParentsPath))
{
sqlString = $"UPDATE siteserver_Department SET {SqlUtils.ToPlusSqlString("ChildrenCount")} WHERE Id IN ({PageUtils.FilterSql(departmentInfo.ParentsPath)})";
ExecuteNonQuery(trans, sqlString);
}
sqlString = $"UPDATE siteserver_Department SET IsLastNode = '{false}' WHERE ParentID = {departmentInfo.ParentId}";
ExecuteNonQuery(trans, sqlString);
//sqlString =
// $"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN (SELECT TOP 1 Id FROM siteserver_Department WHERE ParentID = {departmentInfo.ParentId} ORDER BY Taxis DESC))";
sqlString =
$"UPDATE siteserver_Department SET IsLastNode = '{true}' WHERE (Id IN ({SqlUtils.ToInTopSqlString("siteserver_Department", "Id", $"WHERE ParentID = {departmentInfo.ParentId}", "ORDER BY Taxis DESC", 1)}))";
ExecuteNonQuery(trans, sqlString);
DepartmentManager.ClearCache();
}
public void Delete(int siteId, int departmentId)
{
_repository.Delete(departmentId);
DepartmentManager.ClearCache(siteId);
}
public void Update(DepartmentInfo departmentInfo)
{
_repository.Update(departmentInfo);
DepartmentManager.ClearCache(departmentInfo.SiteId);
}
