/// <summary>
/// 根据楼栋ID获取楼栋信息
/// </summary>
/// <param name="buildingId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static DATBuilding GetBuildingByBuildingId(int buildingId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} BuildingId=:buildingId and CityID=:cityId and Valid=1";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATBuilding));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("buildingId", buildingId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
DATBuilding building = db.DB.GetCustomSQLQueryEntity <DATBuilding>(sql, parameters);
//.GetCustom<DATBuilding>(
// (Expression<Func<DATBuilding, bool>>)
// (tbl =>
// tbl.BuildingId == buildingId && tbl.CityID == cityId && tbl.Valid == 1
// ));
db.Close();
return(building);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据楼盘ID获取楼栋列表
/// </summary>
/// <param name="projectId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DATBuilding> GetBuildingByProjectId(int projectId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} ProjectId = :projectId and CityID = :cityId and Valid=1";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATBuilding));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("projectId", projectId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
IList <DATBuilding> buildingList = db.DB.GetCustomSQLQueryList <DATBuilding>(sql, parameters).ToList();
// GetListCustom<DATBuilding>(
//(Expression<Func<DATBuilding, bool>>)
//(tbl =>
// tbl.ProjectId == projectId && tbl.CityID == cityId&&tbl.Valid==1
//)).ToList<DATBuilding>();
db.Close();
return(buildingList);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取当前用户所拥有菜单类型的页面信息
/// </summary>
/// <param name="userName"></param>
/// <param name="companyId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <SYSMenu> GetSYSMenuPageByUserNameAndCompanyIdAndCityId(string userName, int companyId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = @"{0} TypeCode=:typdCode and Id in (
select MenuId from {1} with(nolock) where RoleId in (
select RoleId from {2} with(nolock) where FxtCompanyId=:companyId and CityId in (0,:cityId) and UserName=:userName
)
)";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_SYSMenu),
NHibernateUtility.TableName_SYSRoleMenu, NHibernateUtility.TableName_SYSRoleUser);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("typdCode", SYSCodeManager.MENUTYPECODE_1, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("userName", userName, NHibernateUtil.String));
IList <SYSMenu> list = db.DB.GetCustomSQLQueryList <SYSMenu>(sql, parameters);
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
//SYS_Role_Menu SYS_Role_User
}
/// <summary>
/// 根据状态ID和任务ID获取最新一条任务状态记录信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="allotId"></param>
/// <param name="stateCode"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static DatAllotSurvey GetDatAllotSurveyLastByAllotIdAndStateCode(int cityId, long allotId, int stateCode, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} AllotId=:allotId and CityId=:cityId and StateCode=:stateCode Order By Id desc";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DatAllotSurvey, keyword: "top 1"));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("allotId", allotId, NHibernateUtil.Int64));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("stateCode", stateCode, NHibernateUtil.Int32));
IList <DatAllotSurvey> list = db.DB.GetCustomSQLQueryList <DatAllotSurvey>(sql, parameters).ToList();
db.Close();
if (list == null || list.Count < 1)
{
return(null);
}
return(list[0]);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据状态ID和多个任务ID获取每个任务最新一条任务状态记录信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="allotIds"></param>
/// <param name="stateCode"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DatAllotSurvey> GetDatAllotSurveyLastByAllotIdAndStateCode(int cityId, long[] allotIds, int stateCode, DataBase _db = null)
{
if (allotIds == null || allotIds.Length < 1)
{
return(new List <DatAllotSurvey>());
}
DataBase db = new DataBase(_db);
try
{
string sql = string.Format("{0} AllotId in ({1}) and CityId=:cityId and StateCode=:stateCode and ID=(select max(ID) from {2} with(nolock) where allotId=_tb.allotId and statecode=_tb.statecode and cityId=_tb.cityId)",
NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DatAllotSurvey),
allotIds.ConvertToString(), NHibernateUtility.TableName_DatAllotSurvey);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("stateCode", stateCode, NHibernateUtil.Int32));
IList <DatAllotSurvey> list = db.DB.GetCustomSQLQueryList <DatAllotSurvey>(sql, parameters).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据状态ID和多个任务ID获取任务状态记录信息
/// </summary>
/// <param name="cityId">城市</param>
/// <param name="allotIds">逗号分隔的多个任务ID</param>
/// <param name="stateCode">状态</param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DatAllotSurvey> GetDatAllotSurveyByAllotIdsAndStateCode(int cityId, long[] allotIds, int stateCode, DataBase _db = null)
{
if (allotIds == null || allotIds.Length < 1)
{
return(new List <DatAllotSurvey>());
}
DataBase db = new DataBase(_db);
try
{
string sql = "{0} CityId=:cityId and StateCode=:stateCode and AllotId in ({1}) ";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DatAllotSurvey), allotIds.ConvertToString());
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("stateCode", stateCode, NHibernateUtil.Int32));
IList <DatAllotSurvey> list = db.DB.GetCustomSQLQueryList <DatAllotSurvey>(sql, parameters);
//IList<DatAllotSurvey> list = db.DB.CreateCriteria(typeof(DatAllotSurvey)).Add(
// Restrictions.And(
// Restrictions.Eq("CityId", cityId),
// Restrictions.Eq("StateCode", stateCode))
// ).Add(
// Restrictions.In("AllotId", allotIds)
// ).List<DatAllotSurvey>();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取用户所属的角色信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="userName"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <SYSRoleUser> GetSYSRoleUserByUserName(int cityId, int companyId, string userName, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} CityID in(0,:cityId) and FxtCompanyID in(0,:companyId) and UserName=:userName";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_SYSRoleUser));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("userName", userName, NHibernateUtil.String));
IList <SYSRoleUser> list = db.DB.GetCustomSQLQueryList <SYSRoleUser>(sql, parameters).ToList();
//.GetListCustom<SYSRoleUser>(
//(Expression<Func<SYSRoleUser, bool>>)(tbl => tbl.CityID == cityId && tbl.FxtCompanyID == companyId && tbl.UserName == userName)
//).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取机构下的用户列表对应的角色信息
/// </summary>
/// <param name="cityId">当前城市</param>
/// <param name="companyId">当前企业</param>
/// <param name="userNames">当前用户数组</param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <SYSRole> GetSYSRoleByCityIdAndUserNames(int cityId, int companyId, string[] userNames, DataBase _db = null)
{
if (userNames == null || userNames.Length < 1)
{
return(new List <SYSRole>());
}
DataBase db = new DataBase(_db);
try
{
StringBuilder userNameSb = new StringBuilder();
foreach (string str in userNames)
{
userNameSb.Append("'").Append(str).Append("',");
}
string sql = string.Format(" {0} Valid=1 and FxtCompanyId in (0,{1}) and CityId in (0,{2}) and Id in (select RoleId from {3} with(nolock) where FxtCompanyId={1} and CityID={2} and UserName in ({4}))",
NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_SYSRole),
companyId, cityId, NHibernateUtility.TableName_SYSRoleUser, userNameSb.ToString().TrimEnd(','));
IList <SYSRole> list = db.DB.GetCustomSQLQueryList <SYSRole>(sql, null);
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
public override Response Refresh(string objectType)
{
string keyFile = string.Format("{0}{1}.{2}.key",
_settings["AppDataPath"], _settings["ProjectName"], _settings["ApplicationName"]);
if (File.Exists(_settings["DBDictionaryPath"]))
{
_dbDictionary = NHibernateUtility.LoadDatabaseDictionary(_settings["DBDictionaryPath"], keyFile);
}
if (_dbDictionary == null || _dbDictionary.dataObjects == null)
{
Response response = new Response()
{
Level = StatusLevel.Error,
Messages = new Messages()
{
"Dictionary is empty."
},
};
return(response);
}
return(Generate(_settings["projectName"], _settings["applicationName"]));
}
/// <summary>
/// 根据多个机构类型code获取楼盘关联公司
/// </summary>
/// <param name="cityId"></param>
/// <param name="projectId"></param>
/// <param name="companyTypes"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <LNKPCompany> GetLNKPCompanyByCompanyTypes(int cityId, int projectId, int[] companyTypes, DataBase _db = null)
{
if (companyTypes == null || companyTypes.Length < 1)
{
return(new List <LNKPCompany>());
}
DataBase db = new DataBase(_db);
try
{
string sql = "{0} CityId=:cityId and ProjectId=:projectId and CompanyType in ({1}) ";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_LNKPCompany), companyTypes.ConvertToString());
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("projectId", projectId, NHibernateUtil.Int32));
IList <LNKPCompany> list = db.DB.GetCustomSQLQueryList <LNKPCompany>(sql, parameters);
//IList<LNKPCompany> list = db.DB.CreateCriteria(typeof(LNKPCompany)).Add(
// Restrictions.And(
// Restrictions.Eq("LNKPCompanyPX.ProjectId", projectId),
// Restrictions.Eq("LNKPCompanyPX.CityId", cityId))
// ).Add(Restrictions.In("LNKPCompanyPX.CompanyType", companyTypes)).List<LNKPCompany>();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据楼栋ID获取房号列表
/// </summary>
/// <param name="buildingId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DATHouse> GetHouseByBuildingId(int buildingId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} BuildingId=:buildingId and CityID =:cityId and Valid=1";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATHouse));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("buildingId", buildingId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
IList <DATHouse> houseList = db.DB.GetCustomSQLQueryList <DATHouse>(sql, parameters).ToList();
// .GetListCustom<DATHouse>(
//(Expression<Func<DATHouse, bool>>)
//(tbl =>
// tbl.BuildingId == buildingId && tbl.CityID == cityId&&tbl.FloorNo==floorNo&&tbl.Valid==1
//)).ToList<DATHouse>();
db.Close();
return(houseList);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据房号ID获取房号信息
/// </summary>
/// <param name="houseId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static DATHouse GetHouseByHouseId(int houseId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} HouseId=:houseId and CityID=:cityId and Valid=1";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATHouse));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("houseId", houseId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
DATHouse house = db.DB.GetCustomSQLQueryEntity <DATHouse>(sql, parameters);
//DATHouse house = db.DB.GetCustom<DATHouse>(
// (Expression<Func<DATHouse, bool>>)
// (tbl =>
// tbl.HouseId == houseId && tbl.CityID == cityId&&tbl.Valid==1
// ));
db.Close();
return(house);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
public static IList <DATHouse> GetHouseByHouseIds(int[] houseIds, int cityId, DataBase _db = null)
{
if (houseIds == null || houseIds.Length < 1)
{
return(new List <DATHouse>());
}
DataBase db = new DataBase(_db);
try
{
string sql = "{0} CityID=:cityId and HouseId in ({1}) ";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATHouse), houseIds.ConvertToString());
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
IList <DATHouse> list = db.DB.GetCustomSQLQueryList <DATHouse>(sql, parameters);
//IList<DATHouse> list = db.DB.CreateCriteria(typeof(DATHouse)).Add(
// Restrictions.And(
// Restrictions.Eq("CityID", cityId),
// Restrictions.In("HouseId", houseIds))
// ).List<DATHouse>();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
public static PriviDepartment GetDepartmentByDepartmentName(int cityId, int companyId, string departmentName, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} FK_CityId in (0,:cityId) and Fk_CompanyId in (0,:companyId) and DValid=1 and DepartmentName=:departmentName";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("departmentName", departmentName, NHibernateUtil.String));
PriviDepartment obj = db.DB.GetCustomSQLQueryEntity <PriviDepartment>(sql, parameters);
//db.DB.GetCustom<PriviDepartment>(
//(Expression<Func<PriviDepartment, bool>>)(tbl => (tbl.FK_CityId == 0 || tbl.FK_CityId == cityId) && (tbl.Fk_CompanyId == 0 || tbl.Fk_CompanyId == companyId) && tbl.DValid == 1 && tbl.DepartmentName == departmentName)
//);
db.Close();
return(obj);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取当前用户的小组信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="userName"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static PriviDepartmentUser GetDepartmentUserByUserName(int cityId, int companyId, string userName, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} FxtCompanyID=:companyId and CityId=:cityId and UserName=:userName and exists (select * from {1} as tb2 with(nolock) where tb2.DepartmentID=_tb.DepartmentID and DValid=1)";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartmentUser), NHibernateUtility.TableName_PriviDepartment);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("userName", userName, NHibernateUtil.String));
PriviDepartmentUser pdu = db.DB.GetCustomSQLQueryEntity <PriviDepartmentUser>(sql, parameters);
//PriviDepartmentUser pdu = db.DB.GetCustom<PriviDepartmentUser>(
// (Expression<Func<PriviDepartmentUser, bool>>)(tbl => tbl.CityID == cityId && tbl.FxtCompanyID == companyId && tbl.UserName == userName)
// );
db.Close();
return(pdu);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取小组列表
/// </summary>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="isGetCount"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <PriviDepartment> GetDepartmentByCompanyId(int cityId, int companyId, string keyword, int pageIndex, int pageSize, out int count, bool isGetCount = true, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
count = 0;
IList <PriviDepartment> list = new List <PriviDepartment>();
string sql = "{0} FK_CityId in (0,:cityId) and Fk_CompanyId in (0,:companyId) and DValid=1";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
if (!string.IsNullOrEmpty(keyword))
{
sql = sql + " and DepartmentName like :keyword";
parameters.Add(new NHParameter("keyword", "%" + keyword + "%", NHibernateUtil.String));
}
UtilityPager pageInfo = new UtilityPager(pageSize: pageSize, pageIndex: pageIndex, isGetCount: isGetCount);
list = db.DB.PagerList <PriviDepartment>(pageInfo, sql, parameters, " DepartmentId", "Desc");
count = pageInfo.Count;
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据状态ID和任务ID获取任务状态记录信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="allotId"></param>
/// <param name="stateCode"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DatAllotSurvey> GetDatAllotSurveyByAllotIdAndStateCode(int cityId, long allotId, int stateCode, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} AllotId=:allotId and CityId=:cityId and StateCode=:stateCode";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DatAllotSurvey));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("allotId", allotId, NHibernateUtil.Int64));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("stateCode", stateCode, NHibernateUtil.Int32));
IList <DatAllotSurvey> list = db.DB.GetCustomSQLQueryList <DatAllotSurvey>(sql, parameters).ToList();
//IList<DatAllotSurvey> list = db.DB.GetListCustom<DatAllotSurvey>
// ((Expression<Func<DatAllotSurvey, bool>>)
// (tbl => tbl.AllotId == allotId && tbl.CityId == cityId && tbl.StateCode == stateCode)).ToList<DatAllotSurvey>();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
protected void Application_Start()
{
NHibernateUtility utility = new NHibernateUtility();
utility.Initialize();
AreaRegistration.RegisterAllAreas();
RouteConfig.RegisterRoutes(RouteTable.Routes);
}
public void TestInit()
{
ServiceFactory.UserService = new UserService();
ServiceFactory.MessageService = new MessageService();
ServiceFactory.CommentService = new CommentService();
var utility = new NHibernateUtility();
utility.Initialize();
}
protected void Application_Start()
{
NHibernateUtility utility = new NHibernateUtility();
utility.Initialize();
AreaRegistration.RegisterAllAreas();
RouteConfig.RegisterRoutes(RouteTable.Routes);
}
public void TestInit()
{
ServiceFactory.UserService = new UserService();
ServiceFactory.MessageService = new MessageService();
ServiceFactory.CommentService = new CommentService();
var utility = new NHibernateUtility();
utility.Initialize();
}
public T Create<T>(IDataItem source)
{
if (source == null) return default(T);
if (typeof(T).IsAssignableFrom(source.GetType()))
return (T)source;
// need to unproxy otherwise Mapper won't recognize the type (nhibernate proxy class)
T result = Mapper.Map<T>(NHibernateUtility.Unproxy(Session, source));
foreach (var injection in _injections)
{
result.InjectFrom(injection, source);
}
return result;
}
/// <summary>
/// 获取楼盘照片
/// </summary>
/// <param name="projectId"></param>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <LNKPPhoto> GetLNKPPhotoByProjectId(int projectId, int cityId, int companyId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} Valid=1 and CityId={1} and ProjectId={2} and FxtCompanyId={3} ";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_LNKPPhoto), cityId, projectId, companyId);
IList <LNKPPhoto> list = db.DB.GetCustomSQLQueryList <LNKPPhoto>(sql, null).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取楼盘照片个数
/// </summary>
/// <param name="projectId"></param>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static int GetLNKPPhotoCountByProjectId(int projectId, int cityId, int companyId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
int count = 0;
try
{
string sql = "{0} Valid=1 and CityId={1} and ProjectId={2} and FxtCompanyId={3} ";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_COUNT(NHibernateUtility.TableName_LNKPPhoto), cityId, projectId, companyId);
object obj = db.DB.GetCustomSQLQueryUniqueResult <object>(sql, null);
count = Convert.ToInt32(obj);
}
catch (Exception ex)
{
log.Error("获取照片个数", ex);
}
db.Close();
return(count);
}
/// <summary>
/// 根据任务ID获取任务审核信息
/// </summary>
/// <param name="allotId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static DatCheck GetCheckByAllotId(long allotId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} allotId=:allotId order by Id desc";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DatCheck, keyword: " top 1 "));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("allotId", allotId, NHibernateUtil.Int64));
DatCheck obj = db.DB.GetCustomSQLQueryEntity <DatCheck>(sql, parameters);
db.Close();
return(obj);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据ID获取分组信息
/// </summary>
/// <param name="departmentId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static PriviDepartment GetDepartmentById(int departmentId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
//db.DB.SessionLock("PriviDepartment", LockMode.None);
string sql = string.Format("{0} DepartmentId={1}", NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment), departmentId);
PriviDepartment pd = db.DB.GetCustomSQLQueryEntity <PriviDepartment>(sql, null);
//.GetCustom<PriviDepartment>(
// (Expression<Func<PriviDepartment, bool>>)(tbl => tbl.DepartmentId == departmentId)
// );
db.Close();
return(pd);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据城市+企业ID+用户+页面url+指定的操作项,获取其具有的操作项
/// </summary>
/// <param name="cityId">城市</param>
/// <param name="companyId">企业ID</param>
/// <param name="userName">用户</param>
/// <param name="pageUrl">页面url</param>
/// <param name="functionCodes">指定的操作项codes</param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <SYSRoleMenuFunction> GetSYSRoleMenuFunctionByUserNameAndCompanyIdAndCityIdAndUrlAndFunctionCodes(int cityId, int companyId, string userName, string pageUrl, int[] functionCodes, DataBase _db = null)
{
if (string.IsNullOrEmpty(pageUrl) || string.IsNullOrEmpty(userName) || functionCodes == null || functionCodes.Length < 1)
{
return(new List <SYSRoleMenuFunction>());
}
DataBase db = new DataBase(_db);
try
{
string sql = new StringBuilder("{0} cityId in (:cityId,0) ")
.Append(" and fxtCompanyId in(:companyId,0) ")
.Append(" and FunctionCode in ({1}) ")
.Append(" and Valid=1 and RoleMenuId in ( ")
.Append(" select Id from {2} with(nolock) where ")
.Append(" cityId in (:cityId,0) and fxtCompanyId in (:companyId,0) ")
.Append(" and ")
.Append(" RoleId in ( ")
.Append(" select RoleId from {3} with(nolock) where userName=:userName and cityId in (:cityId,0) and fxtCompanyId in (:companyId,0) ")
.Append(" ) ")
.Append(" and menuId in (select Id from {4} with(nolock) where url=:pageUrl) ")
.Append(" )").ToString();
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_SYSRoleMenuFunction),
functionCodes.ConvertToString(), NHibernateUtility.TableName_SYSRoleMenu, NHibernateUtility.TableName_SYSRoleUser, NHibernateUtility.TableName_SYSMenu);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("userName", userName, NHibernateUtil.String));
parameters.Add(new NHParameter("pageUrl", pageUrl.ToLower(), NHibernateUtil.String));
IList <SYSRoleMenuFunction> list = db.DB.GetCustomSQLQueryList <SYSRoleMenuFunction>(sql, parameters);
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取当前用户属于的组信息
/// </summary>
/// <param name="cityId"></param>
/// <param name="companyId"></param>
/// <param name="userName"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static PriviDepartment GetDepartmentByUserName(int cityId, int companyId, string userName, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} Fk_CompanyId=:companyId and FK_CityId=:cityId and DValid=1 and exists (select * from {1} as tb2 with(nolock) where tb2.DepartmentID=_tb.DepartmentID and UserName=:userName)";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment), NHibernateUtility.TableName_PriviDepartmentUser);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("userName", userName, NHibernateUtil.String));
PriviDepartment obj = db.DB.GetCustomSQLQueryEntity <PriviDepartment>(sql, parameters);
db.Close();
return(obj);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据多个ID获取小组信息
/// </summary>
/// <param name="departmentIds"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <PriviDepartment> GetDepartmentByIds(int[] departmentIds, DataBase _db = null)
{
if (departmentIds == null || departmentIds.Length < 1)
{
return(new List <PriviDepartment>());
}
DataBase db = new DataBase(_db);
try
{
string sql = string.Format("{0} DepartmentId in ({1})", NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment), departmentIds.ConvertToString());
IList <PriviDepartment> list = db.DB.GetCustomSQLQueryList <PriviDepartment>(sql, null).ToList();// db.DB.GetListCustom<PriviDepartment>("DepartmentId", departmentIds).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据楼盘获取所有房号信息
/// </summary>
/// <param name="projectId"></param>
/// <param name="cityId"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <DATHouse> GetHouseByProjectId(int projectId, int cityId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = "{0} cityId=:cityId and buildingId in (select buildingId from {1} with(nolock) where projectid=:projectId and cityId=:cityId)";
sql = string.Format(sql, NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_DATHouse), NHibernateUtility.TableName_DATBuilding);
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("projectId", projectId, NHibernateUtil.Int32));
IList <DATHouse> list = db.DB.GetCustomSQLQueryList <DATHouse>(sql, parameters);
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 根据多个ID获取角色信息
/// </summary>
/// <param name="roleIds"></param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <SYSRole> GetSYSRoleByRoleIds(int[] roleIds, DataBase _db = null)
{
if (roleIds == null || roleIds.Length < 1)
{
return(new List <SYSRole>());
}
DataBase db = new DataBase(_db);
try
{
string sql = string.Format("{0} ID in ({1})", NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_SYSRole), roleIds.ConvertToString());
IList <SYSRole> list = db.DB.GetCustomSQLQueryList <SYSRole>(sql, null).ToList <SYSRole>();
// db.DB.GetListCustom<SYSRole>("ID", roleIds).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
/// <summary>
/// 获取当前机构的分组信息列表
/// </summary>
/// <param name="cityId">哪个城市</param>
/// <param name="companyId">哪个结构ID</param>
/// <param name="_db"></param>
/// <returns></returns>
public static IList <PriviDepartment> GetDepartmentByCompanyId(int cityId, int companyId, DataBase _db = null)
{
DataBase db = new DataBase(_db);
try
{
string sql = string.Format("{0} FK_CityId in (0,:cityId) and Fk_CompanyId in (0,:companyId) and DValid=1",
NHibernateUtility.GetMSSQL_SQL_NOLOCK(NHibernateUtility.TableName_PriviDepartment));
List <NHParameter> parameters = new List <NHParameter>();
parameters.Add(new NHParameter("cityId", cityId, NHibernateUtil.Int32));
parameters.Add(new NHParameter("companyId", companyId, NHibernateUtil.Int32));
IList <PriviDepartment> list = db.DB.GetCustomSQLQueryList <PriviDepartment>(sql, parameters).ToList <PriviDepartment>();
//IList<PriviDepartment> list = db.DB.GetListCustom<PriviDepartment>(
// (Expression<Func<PriviDepartment, bool>>)(tbl => (tbl.FK_CityId == 0 || tbl.FK_CityId == cityId) && (tbl.Fk_CompanyId == 0 || tbl.Fk_CompanyId == companyId) && tbl.DValid == 1)
// ).ToList();
db.Close();
return(list);
}
catch (Exception ex)
{
db.Close();
throw ex;
}
}
public void Init()
{
var utility = new NHibernateUtility();
utility.Initialize();
}