public IList <ExpensesMPA> FindByExpenseMPAID(IList <long> expensesMPAIdList) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.AppendLine("Select distinct doc.DocumentNo, doc.Subject, doc.DocumentDate, mpaDoc.MPADocumentID , w.WorkflowID "); sqlBuilder.AppendLine(" from Document AS doc "); sqlBuilder.AppendLine(" LEFT JOIN MPADocument AS mpaDoc "); sqlBuilder.AppendLine(" ON doc.DocumentID = mpaDoc.DocumentID "); sqlBuilder.AppendLine(" LEFT JOIN MPAItem AS mpaItem "); sqlBuilder.AppendLine(" ON mpaItem.MPADocumentID = mpaDoc.MPADocumentID "); sqlBuilder.AppendLine(" LEFT JOIN Workflow w ON w.DocumentID = doc.DocumentID "); sqlBuilder.AppendLine(" WHERE mpaItem.MPADocumentID IN (:expensesMPAIdList) "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); query.SetParameterList("expensesMPAIdList", expensesMPAIdList); QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder(); queryParameterBuilder.FillParameters(query); query.AddScalar("DocumentNo", NHibernateUtil.String); query.AddScalar("Subject", NHibernateUtil.String); query.AddScalar("DocumentDate", NHibernateUtil.Date); query.AddScalar("MPADocumentID", NHibernateUtil.Int64); query.AddScalar("WorkflowID", NHibernateUtil.Int64); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpensesMPA))).List <ExpensesMPA>()); }
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 IList <DbPaymentMethod> FindPaymentMethodNotAdd(IList <string> paymentMethodIDList) { StringBuilder sqlBuilder = new StringBuilder(); ISQLQuery query = null; sqlBuilder.Append("SELECT PaymentMethodID , PaymentMethodCode ,Active "); sqlBuilder.Append("FROM DbPaymentMethod "); if (paymentMethodIDList.Count == 0) { query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); } else { sqlBuilder.Append("WHERE PaymentMethodID NOT IN (:PaymentMethodIdList) "); query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); query.SetParameterList("PaymentMethodIdList", paymentMethodIDList); } query.AddScalar("Active", NHibernateUtil.Boolean); query.AddScalar("PaymentMethodID", NHibernateUtil.Int64); query.AddScalar("PaymentMethodCode", NHibernateUtil.String); IList <DbPaymentMethod> dbPyamentMethodList = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPaymentMethod))).List <DbPaymentMethod>(); return(dbPyamentMethodList); }
///// <summary> ///// Удалить удаленные сообщения. ///// </summary> ///// <param name="channel">* - все.</param> //public virtual void DeleteDeletedMessages(string channel) //{ // var msgLinks = new List<int>(); // using ( IDataQuery dataQuery = OpenQuery() ) // { // var query = QueryMessages(dataQuery) // .Where(msg => msg.Status.Value == MessageStatus.DELETED); // if ( String.IsNullOrEmpty(channel) ) // query.Where(msg => msg.Channel == null || msg.Channel == ""); // else if ( channel != "*" ) // query.Where(msg => msg.Channel == channel); // msgLinks = query.List().Select(msg => msg.LINK).ToList(); // } // if ( msgLinks.Count > 0 ) // { // msgLinks.Sort(); // DeleteMessages(msgLinks); // } //} ///// <summary> ///// Удалить устаревшие сообщения. ///// System.InvalidOperationException: Ошибка удаления устаревших сообщений. ---> System.Exception: Cannot supply null value to operator LessThanOrEqual ///// </summary> ///// <param name="channel">* - все.</param> ///// <param name="expiredDate"></param> ///// <param name="statuses"></param> //public virtual void DeleteExpiredMessages(string channel, DateTime expiredDate, List<string> statuses) //{ // var msgLinks = new List<int>(); // using ( IDataQuery dataQuery = OpenQuery() ) // { // var query = QueryMessages(dataQuery) // .Where(msg => (msg.TTL == null && msg.Date <= expiredDate) || (msg.TTL <= DateTime.Now)) // .AndRestrictionOn(msg => msg.Status.Value).IsIn(statuses); // if ( String.IsNullOrEmpty(channel) ) // query.Where(msg => msg.Channel == null || msg.Channel == ""); // else if ( channel != "*" ) // query.Where(msg => msg.Channel == channel); // msgLinks = query.List().Select(msg => msg.LINK).ToList(); // } // if ( msgLinks.Count > 0 ) // { // msgLinks.Sort(); // DeleteMessages(msgLinks); // } //} /// <summary> /// /// </summary> /// <param name="msgLinks"></param> public virtual void DeleteMessages(IEnumerable <int> msgLinks) { #region Validate parameters if (msgLinks == null) { throw new ArgumentNullException("msgLinks"); } #endregion int count = msgLinks.Count(); if (count > 0) { int skip = 0; while (skip < count) { List <int> links = msgLinks.Skip(skip).Take(1000).ToList(); skip += links.Count; using (UnitOfWork work = BeginWork()) { ISQLQuery query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_PROPERTIES)); query.SetParameterList("messages", links); query.ExecuteUpdate(); query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_CONTENTS)); query.SetParameterList("messages", links); query.ExecuteUpdate(); //query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_POSTS)); //query.SetParameterList("messages", links); //query.ExecuteUpdate(); query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE LINK IN (:messages)", Database.Tables.MESSAGES)); query.SetParameterList("messages", links); query.ExecuteUpdate(); work.End(); } } } }
public IList <TranslatedListItem> GetServiceTeamListItemByUserID(long userID, IList <short> userRole) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.AppendLine(" SELECT dst.serviceteamid AS strID,"); sqlBuilder.AppendLine(" '[ ' + dst.serviceteamcode + ' ] ' + dst.description AS strSymbol "); sqlBuilder.AppendLine(" FROM DbServiceTeam dst "); if (userID != 0) { sqlBuilder.AppendLine(" INNER JOIN SuRoleService srs "); sqlBuilder.AppendLine(" ON dst.ServiceTeamID = srs.ServiceTeamID "); sqlBuilder.AppendLine(" INNER JOIN SuUserRole sur "); sqlBuilder.AppendLine(" ON sur.RoleID = srs.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 dst.Active = 1"); if (userID != 0) { sqlBuilder.AppendLine(" AND su.UserID = :UserID "); sqlBuilder.AppendLine(" AND sr.RoleID in (:UserRoleList) "); sqlBuilder.AppendLine(" GROUP BY dst.ServiceTeamID,dst.ServiceTeamCode,dst.Description "); } //sqlBuilder.AppendLine(" ORDER BY strID"); sqlBuilder.AppendLine(" ORDER BY strSymbol"); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); if (userID != 0) { query.SetInt64("UserID", userID); query.SetParameterList("UserRoleList", userRole); } query.AddScalar("strID", NHibernateUtil.String); query.AddScalar("strSymbol", NHibernateUtil.String); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>()); }
public IList <Advance> FindRemittanceAdvanceAndItemsByAdvanceIDs(List <long> advanceIdList) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT fs.RemittanceID as RemittanceID , fs.AdvanceID as AdvanceID , r.TotalAmount as RemittedAmountTHB "); sql.Append("FROM FnRemittanceAdvance as fs "); sql.Append("INNER JOIN FnRemittance as r ON fs.RemittanceID = r.RemittanceID "); sql.Append("WHERE fs.AdvanceID in (:advanceList) "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString()); query.SetParameterList("advanceList", advanceIdList); query.AddScalar("RemittanceID", NHibernateUtil.Int64); query.AddScalar("AdvanceID", NHibernateUtil.Int64); query.AddScalar("RemittedAmountTHB", NHibernateUtil.Double); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(Advance))).List <Advance>()); }
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 <RemittanceValueObj> FindRemittanceItemByRemittanceIds(IList <long> remittanceIdList, short languageId) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT w.WorkflowID, r.RemittanceID as RemittanceID , d.Documentno as RemittanceNo , sl.StatusDesc as PaymentType , c.Symbol as Currency, "); sql.Append("ri.ForeignCurrencyAdvanced AS ForeignCurrencyAdvanced,ri.ExchangeRate AS ExchangeRate, "); sql.Append("ri.ForeignCurrencyRemitted AS ForeignCurrencyRemitted, ri.AmountTHB AS RemittanceAmountTHB, "); sql.Append("ri.MainCurrencyAmount AS RemittanceAmountMainCurrency ");//r.TotalAmount AS RemittanceAmountTHB "); sql.Append("FROM FnRemittance AS r "); sql.Append("INNER JOIN Document d ON d.DocumentID = r.DocumentID "); sql.Append("INNER JOIN Workflow w ON w.DocumentID = d.DocumentID "); sql.Append("INNER JOIN WorkflowState wState ON wState.workflowstateid = w.currentState "); sql.Append("INNER JOIN FnRemittanceItem ri ON ri.RemittanceID = r.RemittanceID "); sql.Append("INNER JOIN DbStatus s ON s.Status = ri.PaymentType AND S.GroupStatus = 'PaymentTypeFRN' "); sql.Append("INNER JOIN DbStatusLang sl ON sl.StatusID = s.StatusID AND sl.LanguageID = "); sql.Append(languageId.ToString()); sql.Append(" INNER JOIN DbCurrency AS c ON c.CurrencyID = ri.CurrencyID "); sql.Append("WHERE r.RemittanceID in(:remittanceIdList) and wState.Name = 'Complete' "); sql.Append("ORDER BY RemittanceNo"); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString()); query.SetParameterList("remittanceIdList", remittanceIdList); query.AddScalar("WorkflowID", NHibernateUtil.Int64); query.AddScalar("RemittanceID", NHibernateUtil.Int64); query.AddScalar("RemittanceNo", NHibernateUtil.String); query.AddScalar("PaymentType", NHibernateUtil.String); query.AddScalar("Currency", NHibernateUtil.String); query.AddScalar("ForeignCurrencyAdvanced", NHibernateUtil.Double); query.AddScalar("ExchangeRate", NHibernateUtil.Double); query.AddScalar("ForeignCurrencyRemitted", NHibernateUtil.Double); query.AddScalar("RemittanceAmountTHB", NHibernateUtil.Double); query.AddScalar("RemittanceAmountMainCurrency", NHibernateUtil.Double); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(RemittanceValueObj))).List <RemittanceValueObj>()); }
public List <PlayerItem> SearchForItems(ItemSearchRequest query) { Logger.Debug($"Searching for items with query {query}"); var queryFragments = new List <string>(); var queryParams = new Dictionary <string, object>(); if (!string.IsNullOrEmpty(query.Wildcard)) { queryFragments.Add("(PI.namelowercase LIKE :name OR PI.searchabletext LIKE :wildcard OR R.text LIKE :wildcard)"); queryParams.Add("name", $"%{query.Wildcard.Replace(' ', '%').ToLower()}%"); queryParams.Add("wildcard", $"%{query.Wildcard.ToLower()}%"); } if (query.RecipeItemsOnly) { queryFragments.Add("PI.BaseRecord IN (SELECT baserecord FROM RecipeItem_V2)"); } // Filter by mod/hc queryFragments.Add("(LOWER(PI.Mod) = LOWER( :mod ) OR PI.Mod IS NULL)"); queryParams.Add("mod", query.Mod); queryFragments.Add(query.IsHardcore ? "PI.IsHardcore" : "NOT PI.IsHardcore"); if (!string.IsNullOrEmpty(query.Rarity)) { queryFragments.Add("PI.Rarity = :rarity"); queryParams.Add("rarity", query.Rarity); } if (query.PrefixRarity > 0) { queryFragments.Add("PI.PrefixRarity >= :prefixRarity"); queryParams.Add("prefixRarity", query.PrefixRarity); } if (query.SocketedOnly) { queryFragments.Add("PI.MateriaRecord is not null and PI.MateriaRecord != ''"); } // Not 100% correct, we may have the same baserecord but different prefix/suffix, gets use pretty close though.. if (query.DuplicatesOnly) { queryFragments.Add(@"PI.BaseRecord IN (SELECT BaseRecord FROM ( select baserecord || prefixrecord || suffixrecord as Records, count(*) as N, BaseRecord from PlayerItem group by Records HAVING N > 1 order by N desc ))"); } // Add the MINIMUM level requirement (if any) if (query.MinimumLevel > 0) { queryFragments.Add("PI.LevelRequirement >= :minlevel"); queryParams.Add("minlevel", query.MinimumLevel); } // Add the MAXIMUM level requirement (if any) if (query.MaximumLevel < 120 && query.MaximumLevel > 0) { queryFragments.Add("PI.LevelRequirement <= :maxlevel"); queryParams.Add("maxlevel", query.MaximumLevel); } // Show only items from the past 12 hours if (query.RecentOnly) { queryFragments.Add("created_at > :filter_recentOnly"); queryParams.Add("filter_recentOnly", DateTime.UtcNow.AddHours(-12).ToTimestamp()); } // Only items which grants new skills if (query.WithGrantSkillsOnly) { // TODO: Are there any prefixes or suffixes which grants skills? queryFragments.Add($"PI.baserecord IN (SELECT PlayerItemRecord from ({ItemSkillDaoImpl.ListItemsQuery}) y)"); } if (query.WithSummonerSkillOnly) { queryFragments.Add(@"PI.baserecord IN (SELECT p.baserecord as PlayerItemRecord from itemskill_v2 s, itemskill_mapping map, DatabaseItem_v2 db, playeritem p, DatabaseItemStat_v2 stat where s.id_skill = map.id_skill and map.id_databaseitem = db.id_databaseitem and db.baserecord = p.baserecord and stat.id_databaseitem = s.id_databaseitem and stat.stat = 'spawnObjects')"); } var sql = new List <string> { $@"select PI.name as Name, PI.StackCount, PI.rarity as Rarity, PI.levelrequirement as LevelRequirement, PI.baserecord as BaseRecord, PI.prefixrecord as PrefixRecord, PI.suffixrecord as SuffixRecord, PI.ModifierRecord as ModifierRecord, PI.MateriaRecord as MateriaRecord, PI.{PlayerItemTable.PrefixRarity} as PrefixRarity, PI.{PlayerItemTable.AzureUuid} as AzureUuid, PI.{PlayerItemTable.CloudId} as CloudId, PI.{PlayerItemTable.IsCloudSynchronized} as IsCloudSynchronizedValue, PI.{PlayerItemTable.Id} as Id, PI.{PlayerItemTable.Mod} as Mod, CAST({PlayerItemTable.IsHardcore} as bit) as IsHardcore, coalesce((SELECT group_concat(Record, '|') FROM PlayerItemRecord pir WHERE pir.PlayerItemId = PI.Id AND NOT Record IN (PI.BaseRecord, PI.SuffixRecord, PI.MateriaRecord, PI.PrefixRecord)), '') AS PetRecord, R.text AS ReplicaInfo, PI.{PlayerItemTable.Seed} as Seed FROM PlayerItem PI LEFT OUTER JOIN ReplicaItem R ON PI.ID = R.playeritemid WHERE " + string.Join(" AND ", queryFragments) }; var subQuery = CreateDatabaseStatQueryParams(query); if (subQuery != null) { foreach (var sub in subQuery.SQL) { sql.Add($" AND PI.Id IN ({sub})"); } } // Can be several slots for stuff like "2 Handed" if (query.Slot?.Length > 0) { var subQuerySql = $@" SELECT Playeritemid FROM PlayerItemRecord WHERE record IN ( select baserecord from databaseitem_V2 db where db.baserecord in ( select baserecord from {PlayerItemTable.Table} union select prefixrecord from {PlayerItemTable.Table} union select suffixrecord from {PlayerItemTable.Table} union select materiarecord from {PlayerItemTable.Table} ) AND exists ( select id_databaseitem from databaseitemstat_v2 dbs WHERE stat = 'Class' AND TextValue in ( :class ) AND db.id_databaseitem = dbs.id_databaseitem ) ) "; sql.Add($" AND PI.Id IN ({subQuerySql})"); // ItemRelic = Components, we don't want to find every item that has a component, only those that are one. if (query.Slot.Length == 1 && query.Slot[0] == "ItemRelic") { sql.Add($" AND PI.{PlayerItemTable.Materia} = ''"); } } using (var session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { ISQLQuery q = session.CreateSQLQuery(string.Join(" ", sql)); foreach (var key in queryParams.Keys) { q.SetParameter(key, queryParams[key]); Logger.Debug($"{key}: " + queryParams[key]); } if (subQuery != null) { foreach (var key in subQuery.Parameters.Keys) { var parameterList = subQuery.Parameters[key]; q.SetParameterList(key, parameterList); Logger.Debug($"{key}: " + string.Join(",", subQuery.Parameters[key])); } } if (query.Slot?.Length > 0) { q.SetParameterList("class", query.Slot); } Logger.Debug(q.QueryString); //q.SetResultTransformer(new AliasToBeanResultTransformer(typeof(PlayerItem))); /* * List<PlayerItem> items = new List<PlayerItem>(); * foreach (var item in q.List()) { * items.Add(ToPlayerItem(item)); * }*/ var items = q.List <object>().Select(ToPlayerItem).ToList(); Logger.Debug($"Search returned {items.Count} items"); return(items); } } }