/// <summary>
/// 根据指定条件查找部门分页集合
/// </summary>
/// <param name="departmentSearcher">部门查询对象</param>
/// <param name="pager">分页对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的DataTable数据</returns>
public PageDataTable FindDataTable(DepartmentSearcher departmentSearcher, Pager pager, ICTransaction tran)
{
DepartmentSearcher querySearcher = null;
MssqlQueryParser queryParser = new MssqlQueryParser();
PageDataTable pDataTable = new PageDataTable();
DataSet resultSet = null;
StringBuilder query = new StringBuilder();
StringBuilder joinQuery = new StringBuilder();
StringBuilder conditionQuery = new StringBuilder();
StringBuilder sortQuery = new StringBuilder();
if (departmentSearcher != null)
{
querySearcher = (DepartmentSearcher)departmentSearcher.Clone();
querySearcher.TableName = "D";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
joinQuery.AppendLine(@"LEFT JOIN ");
joinQuery.AppendLine(@" [Company] C ON(C.CompanyId = D.CompanyId) ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
conditionQuery.AppendLine(@"WHERE ");
conditionQuery.AppendLine(@" " + queryParser.ConditionString);
}
if (!string.IsNullOrEmpty(queryParser.SortString))
{
sortQuery.AppendLine(@"ORDER BY ");
sortQuery.AppendLine(@" " + queryParser.SortString);
}
query.AppendLine(@"SELECT ");
query.AppendLine(@" D.[DepartmentId] ");
query.AppendLine(@" ,D.[DepartmentCode] ");
query.AppendLine(@" ,D.[CompanyId] ");
query.AppendLine(@" ,D.[Name] ");
query.AppendLine(@" ,D.[RVersion] ");
query.AppendLine(@" ,D.[Status] ");
query.AppendLine(@" ,D.[CreaterId] ");
query.AppendLine(@" ,D.[CreateTime] ");
query.AppendLine(@" ,D.[UpdatorId] ");
query.AppendLine(@" ,D.[UpdateTime] ");
query.AppendLine(@"FROM ");
query.AppendLine(@" [Department] D ");
query.AppendLine(joinQuery.ToString());
query.AppendLine(conditionQuery.ToString());
query.AppendLine(sortQuery.ToString());
query.AppendLine(@"; ");
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
if (pager != null)
{
resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), pager, "Department", queryParser.ParamCollection);
}
else
{
resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
}
else
{
if (pager != null)
{
resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), pager, "Department", queryParser.ParamCollection);
}
else
{
resultSet = MssqlHelper.ExecuteDataSet(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
}
if (resultSet != null)
{
if (pager != null)
{
pDataTable.PageIndex = pager.CurrentPage;
}
pDataTable.TotalCount = this.Count(departmentSearcher, tran);
pDataTable.RecordList = resultSet.Tables[0];
}
return pDataTable;
}
/// <summary>
/// 查询部门数量
/// </summary>
/// <param name="departmentSearcher">部门查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查询到的数量</returns>
public long Count(DepartmentSearcher departmentSearcher, ICTransaction tran)
{
object count = 0;
long result = 0;
DepartmentSearcher querySearcher = null;
MssqlQueryParser queryParser = new MssqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"SELECT ");
query.AppendLine(@" COUNT(D.DepartmentId) ");
query.AppendLine(@"FROM ");
query.AppendLine(@" Department D ");
if (departmentSearcher != null)
{
querySearcher = (DepartmentSearcher)departmentSearcher.Clone();
querySearcher.TableName = "D";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" [Company] C ON(C.CompanyId = D.CompanyId) ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
count = MssqlHelper.ExecuteScalar(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
else
{
count = MssqlHelper.ExecuteScalar(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
return long.TryParse(count.ToString(), out result) ? result : 0;
}
/// <summary>
/// 根据指定条件删除部门
/// </summary>
/// <param name="departmentSearcher">部门查询对象</param>
/// <param name="tran">中间事务对象</param>
public void Delete(DepartmentSearcher departmentSearcher, ICTransaction tran)
{
int effectCount = 0;
DepartmentSearcher querySearcher = null;
MssqlQueryParser queryParser = new MssqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"DELETE FROM ");
query.AppendLine(@" Department ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" DepartmentId IN (");
query.AppendLine(@" SELECT ");
query.AppendLine(@" D.DepartmentId ");
query.AppendLine(@" FROM ");
query.AppendLine(@" Department D ");
if (departmentSearcher != null)
{
querySearcher = (DepartmentSearcher)departmentSearcher.Clone();
querySearcher.TableName = "D";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" Company C ON(C.CompanyId = D.CompanyId) ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
query.AppendLine(@"); ");
try
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
}
catch(SqlException sex)
{
if (sex.ErrorCode == (int)ResultCode.FKError)
{
throw new ResponseException((int)ResultCode.FKError, "DELETE Department");
}
}
}
/// <summary>
/// 根据指定条件删除公司
/// </summary>
/// <param name="companySearcher">公司查询对象</param>
/// <param name="tran">中间事务对象</param>
public void Delete(CompanySearcher companySearcher, ICTransaction tran)
{
int effectCount = 0;
CompanySearcher querySearcher = null;
MssqlQueryParser queryParser = new MssqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"DELETE FROM ");
query.AppendLine(@" Company ");
if (companySearcher != null)
{
querySearcher = (CompanySearcher)companySearcher.Clone();
querySearcher.TableName = "C";
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
try
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), queryParser.ParamCollection);
}
}
catch(SqlException sex)
{
if (sex.ErrorCode == (int)ResultCode.FKError)
{
throw new ResponseException((int)ResultCode.FKError, "DELETE Company");
}
}
}
