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); }