public static List <List <JsonItem> > ToJsonSerializable(List <PlayerHeldItem> items)
{
var jsonItems = items.Select(GetJsonItem).ToList();
var merged = ItemOperationsUtility.MergeStackSize(jsonItems);
foreach (var itemList in merged)
{
itemList.Sort();
}
return(merged);
}
public IList <BuddyItem> FindBy(ItemSearchRequest query)
{
List <string> queryFragments = new List <string>();
Dictionary <string, object> queryParams = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(query.Wildcard))
{
queryFragments.Add($"{BuddyItemsTable.NameLowercase} LIKE :name");
queryParams.Add("name", $"%{query.Wildcard.Replace(' ', '%').ToLowerInvariant()}%");
}
queryFragments.Add($"(LOWER({BuddyItemsTable.Mod}) = LOWER( :mod ) OR {BuddyItemsTable.Mod} IS NULL)");
queryParams.Add("mod", query.Mod);
if (!string.IsNullOrEmpty(query.Rarity))
{
queryFragments.Add($"{BuddyItemsTable.Rarity} = :rarity");
queryParams.Add("rarity", query.Rarity);
}
if (query.PrefixRarity > 1)
{
queryFragments.Add($"{BuddyItemsTable.PrefixRarity} >= :prefixRarity");
queryParams.Add("prefixRarity", query.PrefixRarity);
}
queryFragments.Add(query.IsHardcore ? "IsHardcore" : "NOT IsHardcore");
if (query.RecentOnly)
{
queryFragments.Add($"{BuddyItemsTable.CreatedAt} > :filter_recentOnly");
queryParams.Add("filter_recentOnly", DateTime.UtcNow.AddHours(-12).ToTimestamp());
}
// Add the MINIMUM level requirement (if any)
if (query.MinimumLevel > 0)
{
queryFragments.Add($"{BuddyItemsTable.LevelRequirement} >= :minlevel");
queryParams.Add("minlevel", query.MinimumLevel);
}
// Add the MAXIMUM level requirement (if any)
if (query.MaximumLevel < 120 && query.MaximumLevel > 0)
{
queryFragments.Add($"{BuddyItemsTable.LevelRequirement} <= :maxlevel");
queryParams.Add("maxlevel", query.MaximumLevel);
}
List <string> sql = new List <string>();
sql.Add($@"SELECT
{BuddyItemsTable.BaseRecord} as BaseRecord,
{BuddyItemsTable.PrefixRecord} as PrefixRecord,
{BuddyItemsTable.SuffixRecord} as SuffixRecord,
{BuddyItemsTable.ModifierRecord} as ModifierRecord,
{BuddyItemsTable.TransmuteRecord} as TransmuteRecord,
{BuddyItemsTable.MateriaRecord} as MateriaRecord,
{BuddyItemsTable.Rarity} as Rarity,
{BuddyItemsTable.PrefixRarity} as PrefixRarity,
{BuddyItemsTable.Name} as Name,
{BuddyItemsTable.LevelRequirement} as MinimumLevel,
{BuddyItemsTable.RemoteItemId} as RemoteItemId,
{BuddyItemsTable.StackCount} as Count,
{BuddyItemsTable.SubscriptionId} as BuddyId,
S.{BuddySubscriptionTable.Nickname} as Stash,
coalesce((SELECT group_concat(Record, '|') FROM {BuddyItemRecordTable.Table} pir WHERE pir.{BuddyItemRecordTable.Item} = PI.{BuddyItemsTable.RemoteItemId} AND NOT {BuddyItemRecordTable.Record} IN (PI.BaseRecord, PI.SuffixRecord, PI.MateriaRecord, PI.PrefixRecord)), '') AS PetRecord
FROM {BuddyItemsTable.Table} PI, {BuddySubscriptionTable.Table} S WHERE "
+ string.Join(" AND ", queryFragments)
+ $" AND {BuddyItemsTable.SubscriptionId} = {BuddySubscriptionTable.Id} "
);
var subquery = CreateDatabaseStatQueryParams(query);
if (subquery != null)
{
sql.Add($" AND PI.{BuddyItemsTable.RemoteItemId} IN (" + subquery.SQL + ")");
}
using (ISession session = SessionCreator.OpenSession()) {
using (session.BeginTransaction()) {
Logger.Debug(string.Join(" ", sql));
var q = session.CreateSQLQuery(string.Join(" ", sql));
q.AddScalar("BaseRecord", NHibernateUtil.String);
q.AddScalar("PrefixRecord", NHibernateUtil.String);
q.AddScalar("SuffixRecord", NHibernateUtil.String);
q.AddScalar("ModifierRecord", NHibernateUtil.String);
q.AddScalar("PrefixRarity", NHibernateUtil.Int64);
q.AddScalar("TransmuteRecord", NHibernateUtil.String);
q.AddScalar("MateriaRecord", NHibernateUtil.String);
q.AddScalar("Rarity", NHibernateUtil.String);
q.AddScalar("Name", NHibernateUtil.String);
q.AddScalar("MinimumLevel", NHibernateUtil.UInt32);
q.AddScalar("Id", NHibernateUtil.Int64);
q.AddScalar("Count", NHibernateUtil.UInt32);
q.AddScalar("Stash", NHibernateUtil.String);
q.AddScalar("BuddyId", NHibernateUtil.Int64);
q.AddScalar("RemoteItemId", NHibernateUtil.String);
q.AddScalar("PetRecord", NHibernateUtil.String);
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)
{
q.SetParameterList(key, subquery.Parameters[key]);
Logger.Debug($"{key}: " + string.Join(",", subquery.Parameters[key]));
}
}
Logger.Debug(q.QueryString);
q.SetResultTransformer(Transformers.AliasToBean <BuddyItem>());
var result = ItemOperationsUtility.MergeStackSize(q.List <BuddyItem>());
// stacksize is correct.. record is not
Logger.Debug($"Search returned {result.Count} items");
return(result);
}
}
}
/*
* private static PlayerItem ToPlayerItem(object o) {
* object[] arr = (object[]) o;
* int idx = 0;
* string name = arr[idx++] as string;
* long stackCount = (long) arr[idx++];
* string rarity = (string) arr[idx++];
* int levelrequirement = (int) (double) arr[idx++];
* string baserecord = (string) arr[idx++];
* string prefixrecord = (string) arr[idx++];
* string suffixrecord = (string) arr[idx++];
* string ModifierRecord = (string) arr[idx++];
* string MateriaRecord = (string) arr[idx++];
* int PrefixRarity = (int) arr[idx++];
* string AzureUuid = (string) arr[idx++];
* string CloudId = (string) arr[idx++];
* long? IsCloudSynchronized = (long?) arr[idx++];
* string PetRecord = (string) arr[idx++];
*
* return new PlayerItem {
* Name = name,
* StackCount = stackCount,
* Rarity = rarity,
* LevelRequirement = levelrequirement,
* BaseRecord = baserecord,
* PrefixRecord = prefixrecord,
* SuffixRecord = suffixrecord,
* ModifierRecord = ModifierRecord,
* MateriaRecord = MateriaRecord,
* PrefixRarity = PrefixRarity,
* AzureUuid = AzureUuid,
* CloudId = CloudId,
* IsCloudSynchronized = IsCloudSynchronized.HasValue && IsCloudSynchronized.Value == 1,
* PetRecord = PetRecord
* };
* }*/
public List <PlayerItem> SearchForItems(ItemSearchRequest query)
{
Logger.Debug($"Searching for items with query {query}");
List <string> queryFragments = new List <string>();
Dictionary <string, object> queryParams = new Dictionary <string, object>();
if (!String.IsNullOrEmpty(query.Wildcard))
{
queryFragments.Add("(PI.namelowercase LIKE :name OR searchabletext LIKE :wildcard)");
queryParams.Add("name", $"%{query.Wildcard.Replace(' ', '%').ToLower()}%");
queryParams.Add("wildcard", $"%{query.Wildcard.ToLower()}%");
}
// Filter by mod/hc
queryFragments.Add("(LOWER(PI.Mod) = LOWER( :mod ) OR PI.Mod IS NULL)");
queryParams.Add("mod", query.Mod);
if (query.IsHardcore)
{
queryFragments.Add("PI.IsHardcore");
}
else
{
queryFragments.Add("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 != ''");
}
// 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')");
}
List <string> sql = new List <string>();
sql.Add($@"select name as Name,
StackCount,
rarity as Rarity,
levelrequirement as LevelRequirement,
baserecord as BaseRecord,
prefixrecord as PrefixRecord,
suffixrecord as SuffixRecord,
ModifierRecord as ModifierRecord,
MateriaRecord as MateriaRecord,
{PlayerItemTable.PrefixRarity} as PrefixRarity,
{PlayerItemTable.AzureUuid} as AzureUuid,
{PlayerItemTable.CloudId} as CloudId,
{PlayerItemTable.IsCloudSynchronized} as IsCloudSynchronizedValue,
{PlayerItemTable.Id} as Id,
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
FROM PlayerItem PI WHERE " + string.Join(" AND ", queryFragments));
var subquery = CreateDatabaseStatQueryParams(query);
if (subquery != null)
{
sql.Add(" AND PI.Id IN (" + subquery.SQL + ")");
}
// Can be several slots for stuff like "2 Handed"
if (query.Slot?.Length > 0)
{
string 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 (ISession session = SessionCreator.OpenSession()) {
using (ITransaction transaction = session.BeginTransaction()) {
IQuery 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 = ItemOperationsUtility.MergeStackSize(q.List <PlayerItem>());
Logger.Debug($"Search returned {items.Count} items");
return(items);
}
}
}
