/// <summary>
/// 查找指定条件的职位集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回职位实体对象集合</returns>
public IList<Position> FindList(PositionSearcher positionSearcher, ICTransaction tran)
{
PageList<Position> pageList = this.FindList(positionSearcher, null, tran);
return pageList == null ? null : pageList.RecordList;
}
/// <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)
{
PageDataTable pDataTable = null;
PageList<Position> positionList = this.FindList(positionSearcher, pager, tran);
if (positionList != null)
{
pDataTable.PageIndex = positionList.PageIndex;
pDataTable.TotalCount = positionList.TotalCount;
pDataTable.RecordList = new DataTable("Position");
pDataTable.RecordList.Columns.AddRange(new DataColumn[] {
new DataColumn("PositionCode", typeof(String)),
new DataColumn("PositionId", typeof(String)),
new DataColumn("CompanyId", typeof(String)),
new DataColumn("DepartmentId", typeof(String)),
new DataColumn("CreaterId", typeof(String)),
new DataColumn("CreateTime", typeof(DateTime)),
new DataColumn("Name", typeof(String)),
new DataColumn("RVersion", typeof(Int32)),
new DataColumn("Status", typeof(Int32)),
new DataColumn("UpdateTime", typeof(DateTime)),
new DataColumn("UpdatorId", typeof(String))
});
foreach (Position position in positionList.RecordList)
{
pDataTable.RecordList.Rows.Add(
position.PositionCode,
position.PositionId,
position.CurrCompany.CompanyId,
position.CurrDepartment.DepartmentId,
position.CreaterId,
position.CreateTime,
position.Name,
position.RVersion,
position.Status,
position.UpdateTime,
position.UpdatorId
);
}
}
return pDataTable;
}
/// <summary>
/// 查找指定条件的职位集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <returns>返回职位实体对象集合</returns>
public IList<Position> FindList(PositionSearcher positionSearcher)
{
PageList<Position> pageList = this.FindList(positionSearcher, null, null);
return pageList == null ? null : pageList.RecordList;
}
/// <summary>
/// 根据指定条件查找职位集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回一个DataTable对象</returns>
public DataTable FindDataTable(PositionSearcher positionSearcher, ICTransaction tran)
{
PageDataTable pageDataTable = this.FindDataTable(positionSearcher, null, tran);
return pageDataTable == null ? null : pageDataTable.RecordList;
}
/// <summary>
/// 根据指定条件查找职位分页集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="pager">分页对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的DataTable数据</returns>
public PageDataTable FindDataTable(PositionSearcher positionSearcher, Pager pager)
{
return this.FindDataTable(positionSearcher, pager, null);
}
/// <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="positionSearcher">职位查询对象</param>
/// <returns>返回一个DataTable对象</returns>
public DataTable FindDataTable(PositionSearcher positionSearcher)
{
PageDataTable pageDataTable = this.FindDataTable(positionSearcher, null, null);
return pageDataTable == null ? null : pageDataTable.RecordList;
}
/// <summary>
/// 查询职位数量
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <returns>返回查询到的数量</returns>
public long Count(PositionSearcher positionSearcher)
{
return this.Count(positionSearcher, null);
}
/// <summary>
/// 根据指定条件删除职位
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
public void Delete(PositionSearcher positionSearcher)
{
this.Delete(positionSearcher, null);
}
/// <summary>
/// 查找指定条件的职位分页集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="pager">分页对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的对象集合</returns>
public PageList<Position> FindList(PositionSearcher positionSearcher, Pager pager, ICTransaction tran)
{
PageList<Position> resultList = new PageList<Position>();
PageDataTable pageDataTable = this.FindDataTable(positionSearcher, pager, tran);
Position ele = null;
if (pageDataTable != null)
{
resultList = new PageList<Position>();
resultList.PageIndex = pageDataTable.PageIndex;
resultList.TotalCount = pageDataTable.TotalCount;
if (pageDataTable.RecordList != null && pageDataTable.RecordList.Rows.Count > 0)
{
foreach (DataRow aRow in pageDataTable.RecordList.Rows)
{
ele = new Position();
if (!(aRow["PositionId"] is DBNull))
{
ele.PositionId = aRow["PositionId"].ToString();
}
if (!(aRow["CompanyId"] is DBNull))
{
ele.CompanyId = aRow["CompanyId"].ToString();
}
if (!(aRow["DepartmentId"] is DBNull))
{
ele.DepartmentId = aRow["DepartmentId"].ToString();
}
if (!(aRow["PositionCode"] is DBNull))
{
ele.PositionCode = aRow["PositionCode"].ToString();
}
if (!(aRow["CreaterId"] is DBNull))
{
ele.CreaterId = aRow["CreaterId"].ToString();
}
if (!(aRow["CreateTime"] is DBNull))
{
ele.CreateTime = Convert.ToDateTime(aRow["CreateTime"]);
}
if (!(aRow["Name"] is DBNull))
{
ele.Name = aRow["Name"].ToString();
}
if (!(aRow["RVersion"] is DBNull))
{
ele.RVersion = Convert.ToInt32(aRow["RVersion"]);
}
if (!(aRow["Status"] is DBNull))
{
ele.Status = Convert.ToInt32(aRow["Status"]);
}
if (!(aRow["UpdateTime"] is DBNull))
{
ele.UpdateTime = Convert.ToDateTime(aRow["UpdateTime"]);
}
if (!(aRow["UpdatorId"] is DBNull))
{
ele.UpdatorId = aRow["UpdatorId"].ToString();
}
resultList.RecordList.Add(ele);
}
}
}
return resultList;
}
/// <summary>
/// 查找指定ID的职位
/// </summary>
/// <param name="positionId">职位ID</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回职位实体对象</returns>
public Position FindSingle(string positionId, ICTransaction tran)
{
PositionSearcher positionSearcher = new PositionSearcher();
positionSearcher.PositionId.Equal(positionId);
IList<Position> positionList = this.FindList(positionSearcher);
return (positionList == null || positionList.Count == 0) ? null : positionList[0];
}
/// <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="positionId">职位ID</param>
/// <param name="tran">中间事务对象</param>
public void Delete(string positionId, ICTransaction tran)
{
PositionSearcher querySearcher = new PositionSearcher();
querySearcher.PositionId.Equal(positionId);
this.Delete(querySearcher, tran);
}
/// <summary>
/// 查找指定条件的职位分页集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="pager">分页对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的对象集合</returns>
public PageList<Position> FindList(PositionSearcher positionSearcher, Pager pager)
{
return this.FindList(positionSearcher, pager, null);
}
/// <summary>
/// 根据指定条件删除职位
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="tran">中间事务对象</param>
public void Delete(PositionSearcher positionSearcher, ICTransaction tran)
{
PositionSearcher querySearcher = null;
HibernateTransaction hTran = null;
ISession session = null;
HQLQueryParser queryParser = new HQLQueryParser();
StringBuilder query = new StringBuilder();
query.Append("DELETE FROM ");
query.Append(" Position ");
query.Append("WHERE ");
query.Append(" PositionId IN (");
query.Append(" SELECT ");
query.Append(" P.PositionId ");
query.Append(" FROM ");
query.Append(" Position P ");
if (positionSearcher != null)
{
querySearcher = (PositionSearcher)positionSearcher.Clone();
querySearcher.TableName = "P";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.Append("LEFT JOIN ");
query.Append(" P.CurrCompany C ");
}
if (querySearcher.CurrDepartment != null)
{
querySearcher.CurrDepartment.TableName = "D";
query.Append("LEFT JOIN ");
query.Append(" P.CurrDepartment D ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.Append("WHERE ");
query.Append(" " + queryParser.ConditionString);
}
query.Append(")");
if (tran != null)
{
hTran = (HibernateTransaction)tran;
session = hTran.CurrentSession;
}
else
{
session = this.CurrentHibernateConfig.GetCurrentSession();
}
int effectCount = HibernateHelper.DeleteObjectByHQL(session, query.ToString(), queryParser.ParamCollection);
}
/// <summary>
/// 查找指定条件的职位分页集合
/// </summary>
/// <param name="positionSearcher">职位查询对象</param>
/// <param name="pager">分页对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查找到的分页集合，包括按条件可查询到的所有记录数和当前分页的对象集合</returns>
public PageList<Position> FindList(PositionSearcher positionSearcher, Pager pager, ICTransaction tran)
{
PositionSearcher querySearcher = null;
HibernateTransaction hTran = null;
ISession session = null;
PageList<Position> pList = new PageList<Position>();
HQLQueryParser queryParser = new HQLQueryParser();
StringBuilder query = new StringBuilder();
query.Append("SELECT ");
query.Append(" P ");
query.Append("FROM ");
query.Append(" Position P ");
if (positionSearcher != null)
{
querySearcher = (PositionSearcher)positionSearcher.Clone();
querySearcher.TableName = "P";
if (querySearcher.CurrCompany != null)
{
querySearcher.CurrCompany.TableName = "C";
query.Append("LEFT JOIN ");
query.Append(" P.CurrCompany C ");
}
if (querySearcher.CurrDepartment != null)
{
querySearcher.CurrDepartment.TableName = "D";
query.Append("LEFT JOIN ");
query.Append(" P.CurrDepartment D ");
}
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.Append("WHERE ");
query.Append(" " + queryParser.ConditionString);
}
if (!string.IsNullOrEmpty(queryParser.SortString))
{
query.Append("ORDER BY ");
query.Append(" " + queryParser.SortString);
}
if (tran != null)
{
hTran = (HibernateTransaction)tran;
session = hTran.CurrentSession;
pList.TotalCount = this.Count(querySearcher, tran);
}
else
{
session = this.CurrentHibernateConfig.GetCurrentSession();
pList.TotalCount = this.Count(querySearcher);
}
if (pager != null)
{
pList.RecordList = HibernateHelper.FindObjectListByHQL<Position>(session, query.ToString(), queryParser.ParamCollection, pager);
}
else
{
pList.RecordList = HibernateHelper.FindObjectListByHQL<Position>(session, query.ToString(), queryParser.ParamCollection);
}
return pList;
}
/// <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;
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 (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());
query.AppendLine(@"; ");
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
if (pager != null)
{
resultSet = MssqlHelper.ExecuteDataSet(dbTran, CommandType.Text, query.ToString(), pager, "Position", 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, "Position", 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(positionSearcher);
pDataTable.RecordList = resultSet.Tables[0];
}
return pDataTable;
}