/// <summary>
/// 根据指定条件查找职位分页集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="pager">分页对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的DataTable数据</returns>
public PageDataTable FindDataTable(PositionSearcher positionSearcher, Pager pager, ICTransaction tran)
{
PositionSearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
PageDataTable pDataTable = new PageDataTable();
DataSet resultSet = null;
StringBuilder query = new StringBuilder();
StringBuilder joinQuery = new StringBuilder();
StringBuilder conditionQuery = new StringBuilder();
StringBuilder sortQuery = new StringBuilder();
if (positionSearcher != null)
{
querySearcher = (PositionSearcher)positionSearcher.Clone();
querySearcher.TableName = "P";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
joinQuery.AppendLine(@"LEFT JOIN ");
joinQuery.AppendLine(@" `Company] C ON(C.CompanyId = P.CompanyId) ");
}
if (querySearcher.CurrDepartment != null)
{
querySearcher.CurrDepartment.TableName = "D";
joinQuery.AppendLine(@"LEFT JOIN ");
joinQuery.AppendLine(@" `Department] D ON(D.DepartmentId = P.DepartmentId) ");
}
}
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(@" P.`PositionId`");
query.AppendLine(@" ,P.`PositionCode`");
query.AppendLine(@" ,P.`CompanyId`");
query.AppendLine(@" ,P.`DepartmentId`");
query.AppendLine(@" ,P.`Name`");
query.AppendLine(@" ,P.`RVersion`");
query.AppendLine(@" ,P.`Status`");
query.AppendLine(@" ,P.`CreaterId`");
query.AppendLine(@" ,P.`CreateTime`");
query.AppendLine(@" ,P.`UpdatorId`");
query.AppendLine(@" ,P.`UpdateTime`");
query.AppendLine(@"FROM ");
query.AppendLine(@" `Position] P ");
query.AppendLine(joinQuery.ToString());
query.AppendLine(conditionQuery.ToString());
query.AppendLine(sortQuery.ToString());
if (pager != null && pager.PageSize != 0 && pager.StartRecord >= 0)
{
query.AppendLine(@" LIMIT " + pager.StartRecord.ToString() + "," + pager.PageSize + " ");
}
query.AppendLine(@"; ");
if (tran != null)
{
resultSet = MySqlHelper.ExecuteDataset((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
resultSet = MySqlHelper.ExecuteDataset(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
if (resultSet != null)
{
if (pager != null)
{
pDataTable.PageIndex = pager.CurrentPage;
}
pDataTable.TotalCount = this.Count(positionSearcher);
pDataTable.RecordList = resultSet.Tables[0];
}
return pDataTable;
}
/// <summary>
/// 根据指定条件删除职位
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="tran">中间事务对象</param>
public void Delete(PositionSearcher positionSearcher, ICTransaction tran)
{
int effectCount = 0;
PositionSearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"DELETE FROM ");
query.AppendLine(@" Position ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" PositionId IN (");
query.AppendLine(@" SELECT ");
query.AppendLine(@" * ");
query.AppendLine(@" FROM ");
query.AppendLine(@" (SELECT ");
query.AppendLine(@" P.PositionId ");
query.AppendLine(@" FROM ");
query.AppendLine(@" Position P ");
if (positionSearcher != null)
{
querySearcher = (PositionSearcher)positionSearcher.Clone();
querySearcher.TableName = "P";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" `Company] C ON(C.CompanyId = P.CompanyId) ");
}
if (querySearcher.CurrDepartment != null)
{
querySearcher.CurrDepartment.TableName = "D";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" `Department] D ON(D.DepartmentId = P.DepartmentId) ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
query.AppendLine(@") AS P1); ");
try
{
if (tran != null)
{
effectCount = MySqlHelper.ExecuteNonQuery((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
effectCount = MySqlHelper.ExecuteNonQuery(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
}
catch(SqlException sex)
{
if (sex.ErrorCode == (int)ResultCode.FKError)
{
throw new ResponseException((int)ResultCode.FKError, "DELETE Position");
}
}
}
/// <summary>
/// 根据指定条件删除公司
/// </summary>
/// <param name="companySearcher">公司查询对象</param>
/// <param name="tran">中间事务对象</param>
public void Delete(CompanySearcher companySearcher, ICTransaction tran)
{
int effectCount = 0;
CompanySearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
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)
{
effectCount = MySqlHelper.ExecuteNonQuery((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
effectCount = MySqlHelper.ExecuteNonQuery(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
}
catch(SqlException sex)
{
if (sex.ErrorCode == (int)ResultCode.FKError)
{
throw new ResponseException((int)ResultCode.FKError, "DELETE Company");
}
}
}
/// <summary>
/// 查询职位数量
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查询到的数量</returns>
public long Count(PositionSearcher positionSearcher, ICTransaction tran)
{
object count = 0;
long result = 0;
PositionSearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"SELECT ");
query.AppendLine(@" COUNT(P.PositionId) ");
query.AppendLine(@"FROM ");
query.AppendLine(@" Position P ");
if (positionSearcher != null)
{
querySearcher = (PositionSearcher)positionSearcher.Clone();
querySearcher.TableName = "P";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" `Company` C ON(C.CompanyId = P.CompanyId) ");
}
if (querySearcher.CurrDepartment != null)
{
querySearcher.CurrDepartment.TableName = "D";
query.AppendLine(@"LEFT JOIN ");
query.AppendLine(@" `Department` D ON(D.DepartmentId = P.DepartmentId) ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
if (tran != null)
{
count = MySqlHelper.ExecuteScalar((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
count = MySqlHelper.ExecuteScalar(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
return long.TryParse(count.ToString(), out result) ? result : 0;
}
/// <summary>
/// 查询公司数量
/// </summary>
/// <param name="companySearcher">公司查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查询到的数量</returns>
public long Count(CompanySearcher companySearcher, ICTransaction tran)
{
object count = 0;
long result = 0;
CompanySearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"SELECT ");
query.AppendLine(@" COUNT(C.CompanyId) ");
query.AppendLine(@"FROM ");
query.AppendLine(@" Company C ");
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);
}
if (tran != null)
{
count = MySqlHelper.ExecuteScalar((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
count = MySqlHelper.ExecuteScalar(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
return long.TryParse(count.ToString(), out result) ? result : 0;
}