/// <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="tran">中间事务对象</param> /// <returns>返回查询到的数量</returns> public long Count(PositionSearcher positionSearcher, ICTransaction tran) { object count = 0; long result = 0; PositionSearcher querySearcher = null; MssqlQueryParser queryParser = new MssqlQueryParser(); 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) { 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="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="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); }