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