public List <EntranceTableNew> QueryNewAndroidSession(EntranceTableData data) { string command = ""; #region # sql command # command = " SELECT " + Environment.NewLine + data.Top + Environment.NewLine + " cache.UserSessionID, " + Environment.NewLine + " cache.ServiceID, " + Environment.NewLine + " cache.Pxid, " + Environment.NewLine + " cache.IPAddress, " + Environment.NewLine + " cache.ServiceName, " + Environment.NewLine + " cache.ServiceCountry, " + Environment.NewLine + " cache.CountryName, " + Environment.NewLine + " cache.CountryCode, " + Environment.NewLine + " cache.MobileOperatorID, " + Environment.NewLine + " cache.MobileOperatorName, " + Environment.NewLine + " cache.IdentificationSessionGuid, " + Environment.NewLine + " cache.Msisdn, " + Environment.NewLine + " cache.PaymentRequestID, " + Environment.NewLine + " pr.PaymentRequestStatusID, " + Environment.NewLine + " cache.PaymentID, " + Environment.NewLine + " cache.PaymentCreated, " + Environment.NewLine + " p.PaymentStatusID, " + Environment.NewLine + " cache.PaymentContentAccessPolicyID, " + Environment.NewLine + " cache.TransactionID, " + Environment.NewLine + " cache.TransactionCreated, " + Environment.NewLine + " cache.IsSubseguent, " + Environment.NewLine + " cache.SessionCreated " + Environment.NewLine + " FROM MobilePaywall.core.OLCache AS cache " + Environment.NewLine + " LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON cache.PaymentRequestID=pr.PaymentRequestID " + Environment.NewLine + " LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON cache.PaymentID=p.PaymentID " + Environment.NewLine + " LEFT OUTER JOIN MobilePaywall.core.AndroidClientSessionOLCacheMap AS map ON map.OLCacheID=cache.OLCacheID " + Environment.NewLine + " WHERE map.AndroidClientSessionID=" + data.AndroidClientSession.Value + Environment.NewLine + " ORDER BY cache.OLCacheID DESC"; #endregion this._lastCommand = command; List <EntranceTableNew> result = new List <EntranceTableNew>(); this._database.SetTimeout(0); DataTable table = this._database.Load(command); if (table == null) { return(result); } foreach (DataRow row in table.Rows) { EntranceTableNew tableRow = new EntranceTableNew(row); result.Add(tableRow); } return(result); }
public List <EntranceTableNew> QueryNew(EntranceTableData data) { string command = ""; #region # sql command # #region # SPECIAL CASES # // referer if needed string _REFERER_JOIN = string.IsNullOrEmpty(data.ReferrerContains) ? "" : " LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON us.UserSessionID=cache.UserSessionID "; string _REFERER_WHERE = string.IsNullOrEmpty(data.ReferrerContains) ? "" : " AND ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) "; string _TIME = " AND cache.SessionCreated >= '" + data.From + "' AND cache.SessionCreated <= '" + data.To + "' " + Environment.NewLine; if (data.UserSessionReference.Equals("false")) { _TIME = " AND cache.Created >= '" + data.From + "' AND cache.Created <= '" + data.To + "' "; if (data.TransactionUseUserSessionDate) { _TIME += " AND cache.SessionCreated >= '" + data.TransactionUserSessionDateFrom + "' AND cache.SessionCreated <= '" + data.TransactionUserSessionDateTo + "' "; } } string _OLCACHE_SEQUENTAL = (string.IsNullOrEmpty(data.OLCacheID) ? "" : " AND cache.OLCacheID<" + data.OLCacheID); #endregion command = " SELECT " + Environment.NewLine + data.Top + Environment.NewLine + " cache.OLCacheID, " + Environment.NewLine + " cache.UserSessionID, " + Environment.NewLine + " cache.ServiceID, " + Environment.NewLine + " cache.Pxid, " + Environment.NewLine + " cache.IPAddress, " + Environment.NewLine + " cache.ServiceName, " + Environment.NewLine + " cache.ServiceCountry, " + Environment.NewLine + " cache.CountryName, " + Environment.NewLine + " cache.CountryCode, " + Environment.NewLine + " cache.MobileOperatorID, " + Environment.NewLine + " cache.MobileOperatorName, " + Environment.NewLine + " cache.IdentificationSessionGuid, " + Environment.NewLine + " cache.Msisdn, " + Environment.NewLine + " cache.PaymentRequestID, " + Environment.NewLine + " pr.PaymentRequestStatusID, " + Environment.NewLine + " cache.PaymentID, " + Environment.NewLine + " cache.PaymentCreated, " + Environment.NewLine + " p.PaymentStatusID, " + Environment.NewLine + " cache.PaymentContentAccessPolicyID, " + Environment.NewLine + " cache.TransactionID, " + Environment.NewLine + " cache.TransactionCreated, " + Environment.NewLine + " cache.IsSubseguent, " + Environment.NewLine + " cache.SessionCreated " + Environment.NewLine + " FROM MobilePaywall.core.OLCache AS cache " + Environment.NewLine + " LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON cache.PaymentRequestID=pr.PaymentRequestID " + Environment.NewLine + " LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON cache.PaymentID=p.PaymentID " + Environment.NewLine + _REFERER_JOIN + Environment.NewLine + " WHERE " + Environment.NewLine + " ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("cache.ServiceCountry = '{0}' ", "cache.ServiceCountry != '{0}' ", data.Countries) + " )" + Environment.NewLine + " AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("cache.ServiceName LIKE '%{0}%'", "cache.ServiceName NOT LIKE '%{0}%'", data.Services) + " ) " + Environment.NewLine + " AND ( '" + data.MobileOperator + "' = -1 OR " + DataObjectBase.PrepareData("cache.MobileOperatorID", data.MobileOperator) + " )" + Environment.NewLine + " AND ( '" + (int)data.PaymentRequestSearchType + "' = -1 OR ( '" + (int)data.PaymentRequestSearchType + "' = 0 AND cache.PaymentRequestID IS NULL ) OR ( '" + (int)data.PaymentRequestSearchType + "' = 1 AND cache.PaymentRequestID IS NOT NULL ) )" + Environment.NewLine + " AND ( '" + (int)data.PaymentSearchType + "' = -1 OR ( '" + (int)data.PaymentSearchType + "' = 0 AND cache.PaymentID IS NULL ) OR ( '" + (int)data.PaymentSearchType + "' = 1 AND cache.PaymentID IS NOT NULL ) )" + Environment.NewLine + " AND ( '" + (int)data.TransactionSearchType + "' = -1 OR ( '" + (int)data.TransactionSearchType + "' = 0 AND cache.TransactionID IS NULL ) OR ( '" + (int)data.TransactionSearchType + "' = 1 AND cache.TransactionID IS NOT NULL ) ) " + Environment.NewLine //+ " AND ( '" + data.UseOLReference + "' = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) " + Environment.NewLine + " AND ( '" + data.Msisdn + "' = '' " + DataObjectBase.PrepareList("cache.Msisdn='{0}'", data.Msisdns) + " ) " + Environment.NewLine + " AND ( '" + data.Pxid + "' = '' " + DataObjectBase.PrepareList("cache.Pxid LIKE '{0}'", data.Pxids) + " ) " + Environment.NewLine + " AND ( '" + data.IP + "' = '' " + DataObjectBase.PrepareList("cache.IPAddress='{0}'", data.IPS) + " ) " + Environment.NewLine //+ " AND ( '" + data.ReferrerContains + "'='' " + DataObjectBase.PrepareList("us.Referrer LIKE '%{0}%'", data.ReferrerContainsList) + " ) " + Environment.NewLine //+ " AND ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) " + Environment.NewLine + " AND ( '" + data.PaymentStatus + "' = -1 OR ( p.PaymentStatusID = '" + data.PaymentStatus + "' ) ) " + Environment.NewLine + " AND " + (data.UserSessionReference.Equals("true") ? " cache.IsSubseguent=0 " : " cache.IsSubseguent=1 ") + Environment.NewLine + _REFERER_WHERE + Environment.NewLine + _TIME + Environment.NewLine + _OLCACHE_SEQUENTAL + Environment.NewLine + " ORDER BY cache.OLCacheID DESC"; #endregion this._lastCommand = command; List <EntranceTableNew> result = new List <EntranceTableNew>(); this._database.SetTimeout(0); DataTable table = this._database.Load(command); if (table == null) { return(result); } foreach (DataRow row in table.Rows) { EntranceTableNew tableRow = new EntranceTableNew(row); result.Add(tableRow); } return(result); }