public IList <SuRTENodeSearchResult> GetRTEContent(short languageId, string nodetype, short nodeId)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT n.NodeId,n.NodeHeaderId,n.NodeOrderNo,c.languageId,n.NodeType,c.Header,c.Content,n.ImagePath");
strQuery.AppendLine(" From SuRTENode n LEFT OUTER JOIN SuRTEContent c on c.nodeid = n.nodeid ");
strQuery.AppendLine(" WHERE c.LanguageID = :LanguageID and n.nodetype = :NodeType ");
strQuery.AppendLine(" AND n.Active = 'TRUE' and n.NodeId = :NodeId");
strQuery.AppendLine(" ORDER BY n.NodeHeaderId,n.NodeOrderNo");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageId);
query.SetString("NodeType", nodetype);
query.SetInt16("NodeId", nodeId);
query.AddScalar("NodeId", NHibernateUtil.Int16);
query.AddScalar("NodeHeaderId", NHibernateUtil.Int16);
query.AddScalar("NodeOrderNo", NHibernateUtil.Int16);
query.AddScalar("languageId", NHibernateUtil.Int16);
query.AddScalar("NodeType", NHibernateUtil.String);
query.AddScalar("Header", NHibernateUtil.String);
query.AddScalar("Content", NHibernateUtil.String);
query.AddScalar("ImagePath", NHibernateUtil.String);
IList <SuRTENodeSearchResult> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRTENodeSearchResult))).List <SuRTENodeSearchResult>();
return(list);
}
public IList <SuProgramSearchResult> FindSuProgramByLanguageId(short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT p.ProgramID as ProgramID, p.ProgramCode as ProgramCode, p.ProgramPath as ProgramPath ");
strQuery.AppendLine(" , p.Comment as Comment, p.Active as Active ");
strQuery.AppendLine(" , pl.ProgramsName as ProgramName, l.LanguageID as LanguageId ");
strQuery.AppendLine(" FROM SuProgram p ");
strQuery.AppendLine(" INNER JOIN SuProgramLang pl ");
strQuery.AppendLine(" ON pl.ProgramID = p.ProgramID");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON l.LanguageID = pl.languageID and l.LanguageID = :LanguageID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID);
query.AddScalar("ProgramID", NHibernateUtil.Int16);
query.AddScalar("ProgramCode", NHibernateUtil.String);
query.AddScalar("ProgramPath", NHibernateUtil.String);
query.AddScalar("ProgramName", NHibernateUtil.String);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuProgramSearchResult)));
return(query.List <SuProgramSearchResult>());
}
public IList <WorkFlowStateEventWithLang> FindByWorkFlowStateEventByStateID(int workFlowStateID, short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT wse.WorkFlowStateEventID as EventID , wse.Name , wsel.DisplayName , wse.UserControlPath ");
strQuery.AppendLine(" FROM WorkFlowState ws ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent wse ");
strQuery.AppendLine(" ON ws.WorkFlowStateID = wse.WorkFlowStateID ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEventLang wsel ");
strQuery.AppendLine(" ON wse.WorkFlowStateEventID = wsel.WorkFlowStateEventID AND wsel.LanguageID = :LanguageID ");
strQuery.AppendLine(" WHERE ws.WorkFlowStateID = :WorkFlowStateID");
strQuery.AppendLine(" AND ws.Active = '1'");
strQuery.AppendLine(" AND wse.Active = '1'");
strQuery.AppendLine(" ORDER BY ws.Name ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID);
query.SetInt32("WorkFlowStateID", workFlowStateID);
query.AddScalar("EventID", NHibernateUtil.Int64);
query.AddScalar("Name", NHibernateUtil.String);
query.AddScalar("DisplayName", NHibernateUtil.String);
query.AddScalar("UserControlPath", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEventWithLang)));
return(query.List <WorkFlowStateEventWithLang>());
}
public List <UserMenu> FindAllMenu(long userID, short languageID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(
@"
SELECT M.MenuID,ISNULL(M.MenuMainID,M.MenuID)AS MenuMainID
,ML.MenuName
,P.ProgramPath
,M.MenuLevel
,M.MenuSeq
,ML.LanguageID as MenuLanguageID
,SUR.RoleID
FROM SuMenu as M
LEFT OUTER JOIN SuMenuLang as ML ON M.MenuID = ML.MenuID AND ML.LanguageID = :LanguageID
LEFT OUTER JOIN SuProgram as P ON M.ProgramID = P.ProgramID
LEFT OUTER JOIN SuProgramRole SPR ON P.ProgramID = SPR.ProgramID
LEFT OUTER JOIN SuUserRole SUR ON SPR.RoleID = SUR.RoleID AND SUR.UserID = :UserID
WHERE M.Active = 1 AND ML.Active=1 AND (m.programid is null OR sur.roleid is not null)
Order by M.MenuLevel ASC, M.MenuSeq ASC "
);
query.SetInt64("UserID", userID);
query.SetInt16("LanguageID", languageID);
query.AddScalar("MenuID", NHibernateUtil.Int16);
query.AddScalar("MenuMainID", NHibernateUtil.Int16);
query.AddScalar("MenuName", NHibernateUtil.String);
query.AddScalar("ProgramPath", NHibernateUtil.String);
query.AddScalar("MenuLevel", NHibernateUtil.Int16);
query.AddScalar("MenuSeq", NHibernateUtil.Int16);
query.AddScalar("MenuLanguageID", NHibernateUtil.Int16);
query.AddScalar("RoleID", NHibernateUtil.Int16);
IList <UserMenu> menuList = query.SetResultTransformer(Transformers.AliasToBean(typeof(UserMenu))).List <UserMenu>();
return(menuList as List <UserMenu>);
}
public ISQLQuery FindSuRoleLangSearchResult(SuRoleLangSearchResult roleLangSearchResult, short languageID, string roleName, long userID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT r.RoleID as RoleId, rl.RoleName as RoleName ");
strQuery.AppendLine(" , lang.LanguageID as LanguageId, lang.LanguageName as LanguageName ");
strQuery.AppendLine(" , r.Active as Active ");
strQuery.AppendLine(" FROM SuRole r ");
strQuery.AppendLine(" INNER JOIN SuRoleLang rl ");
strQuery.AppendLine(" ON rl.RoleID = r.RoleID AND rl.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ");
strQuery.AppendLine(" ON lang.LanguageID = :languageID ");
strQuery.AppendLine(" WHERE rl.RoleName LIKE :roleName ");
strQuery.AppendLine(" AND r.Active = 'true' ");
strQuery.AppendLine(" AND r.RoleID NOT IN ");
strQuery.AppendLine(" (SELECT ur.RoleID FROM SuUserRole ur WHERE ur.UserID = :userID) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.SetString("roleName", "%" + roleName + "%");
query.SetInt64("userID", userID);
query.AddScalar("RoleId", NHibernateUtil.Int16);
query.AddScalar("RoleName", NHibernateUtil.String);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("LanguageName", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRoleLangSearchResult)));
return query;
}
public InvoiceExchangeRate GetAdvanceExchangeRate(IList <long> advanceIDlist, short currencyID)
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.AppendLine(" SELECT SUM(ISNULL(Amount,0)) as TotalAmount, SUM(ISNULL(AmountTHB,0)) as TotalAmountTHB, SUM(ISNULL(MainCurrencyAmount,0)) as TotalAmountMainCurrency ");
queryBuilder.AppendLine(" FROM AvAdvanceItem ");
queryBuilder.AppendLine(" WHERE AdvanceID in ( :advanceIDList ) ");
queryBuilder.AppendLine(" AND CurrencyID = :currencyID ");
queryBuilder.AppendLine(" GROUP BY CurrencyID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(queryBuilder.ToString());
query.SetParameterList("advanceIDList", advanceIDlist);
query.SetInt16("currencyID", currencyID);
query.AddScalar("TotalAmount", NHibernateUtil.Double);
query.AddScalar("TotalAmountTHB", NHibernateUtil.Double);
query.AddScalar("TotalAmountMainCurrency", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(InvoiceExchangeRate)));
IList <InvoiceExchangeRate> list = query.List <InvoiceExchangeRate>();
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
public int CountBySuProgramRoleCriteria(SuProgramRole criteria, short languageId)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select count(*) as count");
sql.Append(" FROM SuProgramRole pr LEFT OUTER JOIN SuProgramLang pl ON pl.ProgramID = pr.ProgramID");
sql.Append(" where pl.Languageid = :LanguageId");
sql.Append(" and pr.RoleID = :RoleId");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetInt16("RoleId", criteria.Role.RoleID);
query.SetInt16("LanguageId", languageId);
query.AddScalar("count", NHibernateUtil.Int32);
return(Convert.ToInt32(query.UniqueResult()));
}
public string getParameterByGroupNo_SeqNo(string groupNo, string seqNo)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strGetParameterByGroupNo_SeqNo);
query.SetInt16("GroupNo", Utilities.ParseShort(groupNo));
query.SetInt16("SeqNo", Utilities.ParseShort(seqNo));
query.AddEntity(typeof(DTO.DbParameter));
IList <DbParameter> dbParameter = query.List <DbParameter>();
if (dbParameter.Count > 0)
{
return(dbParameter[0].ParameterValue);
}
else
{
return("");
}
}
public ISQLQuery FindSuRTENodeByLanguageId(short languageId, string sortExpression, bool isCount)
{
StringBuilder strQuery = new StringBuilder();
if (isCount)
{
strQuery.AppendLine(" SELECT Count(n.NodeId) as Count ");
strQuery.AppendLine(" From SuRTENode n ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ON ");
strQuery.AppendLine(" lang.LanguageID = :LanguageID ");
}
else
{
strQuery.AppendLine(" SELECT n.NodeId as Nodeid, n.NodeHeaderId as NodeHeaderid, n.NodeOrderNo as NodeOrderNo, ");
strQuery.AppendLine(" n.NodeType as NodeType, n.ImagePath as ImagePath, n.Comment as Comment, n.Active as Active");
strQuery.AppendLine(" From SuRTENode n ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ON ");
strQuery.AppendLine(" lang.LanguageID = :LanguageID ");
if (!string.IsNullOrEmpty(sortExpression))
{
strQuery.AppendLine(String.Format(" ORDER BY {0} ", sortExpression));
}
else
{
strQuery.AppendLine(" ORDER BY n.NodeId, n.NodeHeaderId, n.NodeOrderNo, n.NodeType, n.Comment, n.Active");
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageId);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("Nodeid", NHibernateUtil.Int16);
query.AddScalar("NodeHeaderid", NHibernateUtil.Int16);
query.AddScalar("NodeOrderNo", NHibernateUtil.Int16);
query.AddScalar("NodeType", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("ImagePath", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRTENode)));
}
return(query);
}
public IList <DbParameterGroup> GetParameterGroupByGroupNo(string groupNo)
{
string strSQL = " SELECT * FROM DbParameterGroup WHERE GroupNo = :GroupNo ";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strSQL);
query.SetInt16("GroupNo", Utilities.ParseShort(groupNo));
query.AddEntity(typeof(DTO.DbParameter));
IList <DbParameterGroup> dbParameterGroup = query.List <DbParameterGroup>();
return(dbParameterGroup);
}
public int FindCountSuProgramSearchResult(SuProgramSearchResult programSearchResult, short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT Count(*) as Count ");
strQuery.AppendLine(" FROM SuProgram p ");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON l.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN SuProgramLang pl ");
strQuery.AppendLine(" ON pl.ProgramID = p.ProgramID AND pl.LanguageID = l.LanguageID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.AddScalar("Count", NHibernateUtil.Int32);
return(Convert.ToInt32(query.UniqueResult()));
}
public IList <TranslatedListItem> GetPBLangByCriteria(short languageID, long UserID, IList <short> userRole)
{
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.AppendLine(" select pb.pbid as strID,pbl.description as strSymbol,pb.pbCode as strCode");
sqlbuilder.AppendLine(" from dbpb pb inner join dbpblang pbl");
sqlbuilder.AppendLine(" on pb.pbid = pbl.pbid");
sqlbuilder.AppendLine(" and pbl.languageID = :languageID ");
sqlbuilder.AppendLine(" and pbl.active = 1 ");
if (UserID != 0)
{
sqlbuilder.AppendLine(" inner join SuRolePB srp ");
sqlbuilder.AppendLine(" on pb.pbid = srp.pbid ");
sqlbuilder.AppendLine(" inner join SuUserRole sur ");
sqlbuilder.AppendLine(" on sur.RoleID = srp.RoleID ");
sqlbuilder.AppendLine(" inner join SuUser su ");
sqlbuilder.AppendLine(" on su.UserID = sur.UserID ");
sqlbuilder.AppendLine(" inner join SuRole sr ");
sqlbuilder.AppendLine(" on sr.RoleID = sur.RoleID ");
sqlbuilder.AppendLine(" where su.Userid = :UserID ");
sqlbuilder.AppendLine(" AND sr.RoleID in (:UserRoleList) ");
sqlbuilder.AppendLine(" Group By pb.pbid ,pbl.description,pb.pbCode ");
}
sqlbuilder.AppendLine(" ORDER BY strID");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlbuilder.ToString());
if (UserID != 0)
{
query.SetInt64("UserID", UserID);
query.SetParameterList("UserRoleList", userRole);
}
query.SetInt16("languageID", languageID);
query.AddScalar("strID", NHibernateUtil.String);
query.AddScalar("strSymbol", NHibernateUtil.String);
query.AddScalar("strCode", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>());
}
//public IList<SuOrganization_SuOrganizationLang> FindByLanguageId(int languageId)
//{
// StringBuilder strQuery = new StringBuilder();
// strQuery.AppendLine(" SELECT org.OrganizationID,orgLang.OrganizationName FROM SuOrganization org ");
// strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang ");
// strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID ");
// strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID ");
// strQuery.AppendLine(" ORDER BY orgLang.OrganizationName ");
// return GetCurrentSession().CreateSQLQuery(strQuery.ToString())
// .AddEntity(typeof(SuOrganization_SuOrganizationLang))
// .SetInt32("LanguageID", languageId)
// .List<SuOrganization_SuOrganizationLang>();
//}
#endregion
#region ISuOrganizationQuery Members
public IList <TranslatedListItem> GetTranslatedList(short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT org.OrganizationID as Id , orgLang.OrganizationName as Text FROM SuOrganization org ");
strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang ");
strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID ");
strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID ");
strQuery.AppendLine(" ORDER BY orgLang.OrganizationName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID == short.Parse("0")?short.Parse("1"):languageID);
query.AddScalar("Id", NHibernateUtil.Int16);
query.AddScalar("Text", NHibernateUtil.String);
IList <TranslatedListItem> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>();
return(list);
}
public IList <TranslatedListItem> GetTranslatedList(short languageId)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT ag.AnnouncementGroupID as Id , agl.AnnouncementGroupName as Text FROM SuAnnouncementGroup ag ");
strQuery.AppendLine(" INNER JOIN SuAnnouncementGroupLang agl ");
strQuery.AppendLine(" ON agl.AnnouncementGroupID = ag.AnnouncementGroupID ");
strQuery.AppendLine(" WHERE agl.LanguageID = :LanguageID ");
strQuery.AppendLine(" ORDER BY agl.AnnouncementGroupName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageId);
query.AddScalar("Id", NHibernateUtil.Int16);
query.AddScalar("Text", NHibernateUtil.String);
IList <TranslatedListItem> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>();
return(list);
}
public DocumentInitiator FindByInitiatorSequence(short Sequence)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT * FROM DocumentInitiator ");
sqlBuilder.Append(" WHERE Seq = :Sequence ");
parameterBuilder.AddParameterData("Sequence", typeof(short), Sequence);
// ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetInt16("Sequence", Sequence);
query.AddEntity(typeof(DocumentInitiator));
IList <DocumentInitiator> documentInitiator = query.List <DocumentInitiator>();
return(documentInitiator.Count > 0 ? documentInitiator[0] : null);
//throw new NotImplementedException();
}
public int FindCountSuRoleLangSearchResult(SuRoleLangSearchResult roleLangSearchResult, short languageID, string roleName, long userID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT Count(*) as Count ");
strQuery.AppendLine(" FROM SuRole r ");
strQuery.AppendLine(" INNER JOIN SuRoleLang rl ");
strQuery.AppendLine(" ON rl.RoleID = r.RoleID AND rl.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ");
strQuery.AppendLine(" ON lang.LanguageID = :languageID ");
strQuery.AppendLine(" WHERE rl.RoleName LIKE :roleName ");
strQuery.AppendLine(" AND r.Active = 'true' ");
strQuery.AppendLine(" AND r.RoleID NOT IN ");
strQuery.AppendLine(" (SELECT ur.RoleID FROM SuUserRole ur WHERE ur.UserID = :userID) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.SetString("roleName", "%" + roleName + "%");
query.SetInt64("userID", userID);
query.AddScalar("Count", NHibernateUtil.Int32);
return Convert.ToInt32(query.UniqueResult());
}
public ISQLQuery FindBySuProgramLangQuery(SuProgramLang programLang, short roleId, short languageId, bool isCount)
{
StringBuilder sql = new StringBuilder();
if (!isCount)
{
sql.Append(" select pl.ID as ProgramLangId, pl.ProgramId as ProgramId, pl.ProgramsName as ProgramName ,pl.Comment as Comment from SuProgramLang as pl");
}
else
{
sql.Append(" select count(*) as Count from SuProgramLang as pl");
}
sql.Append(" where pl.Languageid = :LanguageId");
sql.Append(" and pl.ProgramsName Like :ProgramsName");
sql.Append(" and pl.Programid not in");
sql.Append(" (select pr.Programid from SuProgramRole as pr where pr.RoleID = :RoleId)");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetInt16("RoleId", roleId)
.SetInt16("LanguageId", languageId)
.SetString("ProgramsName", "%" + programLang.ProgramsName + "%");
if (!isCount)
{
query.AddScalar("ProgramLangId", NHibernateUtil.Int64);
query.AddScalar("ProgramId", NHibernateUtil.Int16);
query.AddScalar("ProgramName", NHibernateUtil.String);
query.AddScalar("Comment", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ProgramLang)));
}
else
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
return(query);
}