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