public ActionResult Index(EntranceTableData data)
{
MobilePaywall.Direct.ServerDirect db = new ServerDirect();
DataTable table = db.Load(@"SELECT DISTINCT
SJ.Name AS JobName,
SJ.description AS JobDescription,
SJH.run_date AS LastRunDate,
CASE SJH.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS LastRunStatus
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJH.job_id = SJ.job_id and SJH.run_date = (SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
AND SJ.name LIKE 'MobilePaywall%'
ORDER BY SJH.run_date desc");
if (table == null || table.Rows.Count == 0)
{
return(this.Content("Load error"));
}
List <DatabaseReportActivityEntry> dataEntries = new List <DatabaseReportActivityEntry>();
foreach (DataRow row in table.Rows)
{
dataEntries.Add(new DatabaseReportActivityEntry(row));
}
return(View("~/Views/Statistic/DatabaseReport.cshtml", new DatabaseReportModel(dataEntries)));
}
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 string LoadTransactions(EntranceTableData data)
{
if (data.Validation())
{
return(string.Format("- validation error -"));
}
return(JsonObjectHelper.Json(new StatisticManager().LoadTransactions(data)));
}
public ActionResult QuickReport(EntranceTableData data)
{
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
//StatisticManager manager = new StatisticManager();
//QuickReportModel model = new QuickReportModel(manager.LoadQuickReports(data));
return(View("~/Views/Statistic/QuickReport.cshtml", new QuickReportModel(null)));
}
public ActionResult Cashflow(EntranceTableData data)
{
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
CashflowLogManager manager = new CashflowLogManager();
CashflowStatisticModel model = new CashflowStatisticModel(manager.Query(data, true));
return(View("~/Views/Statistic/CashflowReport.cshtml", model));
}
public ActionResult LoadNewSubsequental(EntranceTableData data)
{
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
EntranceTableManager entranceTableManager = new EntranceTableManager();
DataTableModel model = new DataTableModel(entranceTableManager);
return(View("~/Views/Database/_DataNewSequential.cshtml", model));
}
// SUMMARY: Get .csv from for specific entrance table data
public FileContentResult Csv(EntranceTableData data)
{
if (data.Validation())
{
return(null);
}
EntranceTableManager entranceTableManager = new EntranceTableManager();
entranceTableManager.Query(data);
return(File(new System.Text.UTF8Encoding().GetBytes(entranceTableManager.ConvertToCvs()), "text/csv", "database.csv"));
}
// SUMMARY: Load entrance table data
public ActionResult Load(EntranceTableData data)
{
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
EntranceTableManager entranceTableManager = new EntranceTableManager();
entranceTableManager.Query(data);
return(View("~/Views/Database/_Data.cshtml", new DataTableModel(entranceTableManager)));
}
// SUMMARY: Sql for getting all transactions for all paywall services
public List <CashflowBehaviorModelTable> Query(EntranceTableData data, bool newTransactions = true)
{
string command = string.Empty;
#region # sql command #
char transactionModulator = newTransactions ? '=' : '>';
command = " ( " +
" SELECT bm.Name, COUNT(*) FROM Cashflow.core.[Transaction] AS t " +
" LEFT OUTER JOIN Cashflow.core.TransactionGroup AS tg ON t.TransactionGroupID=tg.TransactionGroupID " +
" LEFT OUTER JOIN Cashflow.core.Subscription AS s ON s.TransactionGroupID=tg.TransactionGroupID " +
" LEFT OUTER JOIN Cashflow.core.SubscriptionRequest AS sr ON sr.SubscriptionRequestID=s.SubscriptionRequestID " +
" LEFT OUTER JOIN Cashflow.core.BehaviorModel AS bm ON bm.BehaviorModelID=sr.BehaviorModelID " +
" WHERE bm.ProductID=8 AND " +
" t.Created >= '" + data.From + "' AND t.Created <= '" + data.To + "' AND t.TransactionStatusID=4 AND " +
" ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("bm.Name LIKE '%{0}%'", data.Services) + " ) AND " +
" ( SELECT COUNT(*) FROM Cashflow.core.[Transaction] WHERE TransactionGroupID=tg.TransactionGroupID) " + transactionModulator + " 1 " +
" GROUP BY bm.Name " +
" ) " +
" UNION ALL " +
" ( " +
" SELECT bm.Name, COUNT(*) FROM Cashflow.core.[Transaction] AS t " +
" LEFT OUTER JOIN Cashflow.core.TransactionGroup AS tg ON t.TransactionGroupID=tg.TransactionGroupID " +
" LEFT OUTER JOIN Cashflow.core.Purchase AS s ON s.TransactionGroupID=tg.TransactionGroupID " +
" LEFT OUTER JOIN Cashflow.core.PurchaseRequest AS sr ON sr.PurchaseRequestID=s.PurchaseRequestID " +
" LEFT OUTER JOIN Cashflow.core.BehaviorModel AS bm ON bm.BehaviorModelID=sr.BehaviorModelID " +
" WHERE bm.ProductID=8 AND " +
" t.Created >= '" + data.From + "' AND t.Created <= '" + data.To + "' AND t.TransactionStatusID=4 AND " +
" ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("bm.Name LIKE '%{0}%'", data.Services) + " ) AND " +
" ( SELECT COUNT(*) FROM Cashflow.core.[Transaction] WHERE TransactionGroupID=tg.TransactionGroupID) " + transactionModulator + " 1 " +
" GROUP BY bm.Name " +
" ) " +
" ORDER BY Name; ";
#endregion
CashflowDirect database = CashflowDirect.Instance;
DataTable table = database.Load(command);
if (table == null)
{
return(new List <CashflowBehaviorModelTable>());
}
List <CashflowBehaviorModelTable> result = new List <CashflowBehaviorModelTable>();
foreach (DataRow row in table.Rows)
{
result.Add(new CashflowBehaviorModelTable(row));
}
return(result);
}
public ActionResult Timeline(EntranceTableData data)
{
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
StatisticManager manager = new StatisticManager();
TimelineModel model = new TimelineModel();
model.Data = manager.LoadTimeline(data);
return(View("~/Views/Statistic/Timeline.cshtml", model));
}
public ActionResult GetData()
{
AndroidInputModel inputModel = new AndroidInputModel(this.Request);
EntranceTableData inputData = inputModel.ToEntranceTableData();
if (inputData.Validation())
{
return(this.Json(new { status = false }, JsonRequestBehavior.AllowGet));
}
EntranceTableManager entranceTableManager = new EntranceTableManager();
List <EntranceTableAndroid> result = entranceTableManager.QueryNewAndroid(inputData);
return(this.Json(result, JsonRequestBehavior.AllowGet));
}
public List <JsonObjectHelper> LoadTransactions(EntranceTableData data)
{
string command = string.Empty;
#region # sql command #
command = " ( " +
" SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID " +
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID " +
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" t.Created >= '" + data.From + "' AND t.Created <= '" + data.To + "' AND " +
//" (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID AND TransactionStatusID=5) = 1 " +
" p.Created > '" + data.From + "' " +
" ) UNION ALL ( " +
" SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID " +
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID " +
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" t.Created >= '" + data.From + "' AND t.Created <= '" + data.To + "' AND " +
//" (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID AND TransactionStatusID=5) > 1 " +
" p.Created < '" + data.From + "' " +
")";
#endregion
DataTable table = this.Load(command);
List <JsonObjectHelper> result = new List <JsonObjectHelper>();
result.Add(new JsonObjectHelper("transactions", table.Rows[0][0].ToString(), "tran"));
result.Add(new JsonObjectHelper("subsequents", table.Rows[1][0].ToString(), "sub"));
return(result);
}
public List <JsonObjectHelper> LoadPayments(EntranceTableData data)
{
string command = string.Empty;
#region # sql command #
command = " SELECT p.PaymentStatusID, COUNT(*) FROM MobilePaywall.core.Payment AS p " +
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID " +
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" us.Created >= '" + data.From + "' AND us.Created <= '" + data.To + "' AND us.CustomerID IS NOT NULL " +
" GROUP BY p.PaymentStatusID ";
#endregion
DataTable dataTable = this.Load(command);
if (dataTable == null)
{
return(new List <JsonObjectHelper>());
}
List <JsonObjectHelper> result = new List <JsonObjectHelper>();
result.Add(new JsonObjectHelper("Initialized", 0, "1")); // index 0, row 1
result.Add(new JsonObjectHelper("Pending", 0, "2")); // index 1, row 2
result.Add(new JsonObjectHelper("Successful", 0, "3")); // index 2, row 3
result.Add(new JsonObjectHelper("Failed", 0, "4")); // index 3, row 4
result.Add(new JsonObjectHelper("Cancelled", 0, "5")); // index 4, row 5
foreach (DataRow row in dataTable.Rows)
{
foreach (JsonObjectHelper joh in result)
{
if (joh.Reference.Equals(row[0].ToString()))
{
joh.Value = row[1].ToString();
}
}
}
return(result);
}
public ActionResult LoadNew(EntranceTableData data)
{
if (data.UseSequentialSearch)
{
return(this.LoadNewSubsequental(data));
}
if (data.Validation())
{
return(Content(string.Format("<b class=\"message_error\">Internal error. '{0}'</b>", data.ErrorMessage)));
}
EntranceTableManager entranceTableManager = new EntranceTableManager();
DataTableModel model = new DataTableModel(entranceTableManager,
(!data.AndroidClientSession.HasValue ?
entranceTableManager.QueryNew(data) :
entranceTableManager.QueryNewAndroidSession(data)));
return(View(string.Format("~/Views/Database/{0}.cshtml", data.ReturnView), model));
}
public List <JsonObjectHelper> LoadUserSessions(EntranceTableData data)
{
string command = string.Empty;
#region # command #
command = "( SELECT COUNT(*) FROM MobilePaywall.core.UserSession AS us " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" us.Created >= '" + data.From + "' AND us.Created <= '" + data.To + "' ) " +
" UNION ALL " +
"( SELECT COUNT(*) FROM MobilePaywall.core.UserSession AS us " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" us.Created >= '" + data.From + "' AND us.Created <= '" + data.To + "' AND us.CustomerID IS NOT NULL ) ";
#endregion
DataTable table = this.Load(command);
if (data == null)
{
return(new List <JsonObjectHelper>());
}
List <JsonObjectHelper> result = new List <JsonObjectHelper>();
result.Add(new JsonObjectHelper("sessions", table.Rows[0][0].ToString()));
result.Add(new JsonObjectHelper("identified", table.Rows[1][0].ToString()));
return(result);
}
public List <EntranceTable> Query(EntranceTableData data)
{
string command = "";
#region # sql command #
#region # set Date reference for loading. Load by user session date or transaction date (with specific user session date ) #
string timeLoad = string.Empty;
if (data.UserSessionReference.Equals("false"))
{
timeLoad = " AND t.Created > '"+ data.From + "' AND t.Created < '" + data.To + "' AND p.Created <= DATEADD(day, -1, CONVERT(DATE, '" + data.From + "') ) ";
if (data.TransactionUseUserSessionDate)
{
timeLoad += string.Format(" AND us.Created >= '{0}' AND us.Created <= '{1}' ", data.TransactionUserSessionDateFrom, data.TransactionUserSessionDateTo);
}
}
else
{
timeLoad = ((int)data.TransactionSearchType == 1 ? "AND t.Created > '" + data.From + "' AND t.Created < '" + data.To + "'" : "") + " AND us.Created > '" + data.From + "' AND us.Created < '" + data.To + "' ";
}
#endregion
command = " SELECT " + Environment.NewLine
+ data.Top + Environment.NewLine
+ " us.UserSessionID, " + Environment.NewLine
+ " country.TwoLetterIsoCode, " + Environment.NewLine
+ " country.GlobalName, " + Environment.NewLine
+ " us.UserSessionGuid AS 'SGID'," + Environment.NewLine
+ " us.Created AS 'SCreated'," + Environment.NewLine
+ " us.EntranceUrl AS 'EntranceUrl', " + Environment.NewLine
+ " service.Name AS 'ServiceName'," + Environment.NewLine
+ " mo.Name AS 'Operator'," + Environment.NewLine
+ " ls.IdentificationSessionGuid, " + Environment.NewLine
+ " us.IPAddress AS 'IP'," + Environment.NewLine
+ " c.Msisdn," + Environment.NewLine
+ " pr.PaymentRequestID, " + Environment.NewLine
+ " prStatus.Name, " + Environment.NewLine
+ " pr.ExternalPaymentRequestGuid," + Environment.NewLine
+ " pr.PaymentRedirectUrl," + Environment.NewLine
+ " p.ExternalPaymentGuid," + Environment.NewLine
+ " paymentStatus.Name AS 'PStatus'," + Environment.NewLine
+ " p.Created AS 'PCreated'," + Environment.NewLine
+ " pcapm.PaymentContentAccessPolicyMapID, " + Environment.NewLine
+ " t.TransactionID AS 'TID'," + Environment.NewLine
+ " t.Created AS 'TCreated'" + Environment.NewLine
+ " FROM MobilePaywall.core.UserSession AS us " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.Country AS country ON us.CountryID = country.CountryID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.Service AS service ON us.ServiceID = service.ServiceID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.Customer AS c ON us.CustomerID=c.CustomerID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.MobileOperator AS mo ON c.MobileOperatorID=mo.MobileOperatorID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.LookupSession AS ls ON us.UserSessionID=ls.UserSessionID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.LookupSessionResult AS lsr ON ls.LookupSessionID=lsr.LookupSessionID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON us.UserSessionID=pr.UserSessionID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.PaymentRequestStatus AS prStatus ON pr.PaymentRequestStatusID=prStatus.PaymentRequestStatusID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON p.PaymentRequestID=pr.PaymentRequestID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.PaymentStatus AS paymentStatus ON p.PaymentStatusID = paymentStatus.PaymentStatusID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.PaymentContentAccessPolicyMap AS pcapm ON pcapm.PaymentID=p.PaymentID " + Environment.NewLine
+ " LEFT OUTER JOIN MobilePaywall.core.[Transaction] AS t ON pcapm.TransactionID=t.TransactionID " + Environment.NewLine
+ " WHERE " + Environment.NewLine
+ " ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("service.Description LIKE UPPER('{0}') + '%' ", "service.Description NOT LIKE UPPER('{0}') + '%' ", data.Countries) + " )" + Environment.NewLine
+ " AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("service.Name LIKE '%{0}%'", "service.Name NOT LIKE '%{0}%'", data.Services) + " ) " + Environment.NewLine
+ " AND ( '" + data.MobileOperator + "' = -1 OR " + DataObjectBase.PrepareData("mo.MobileOperatorID", data.MobileOperator) + " )" + Environment.NewLine
+ " AND ( '" + (int)data.PaymentRequestSearchType + "' = -1 OR ( '" + (int)data.PaymentRequestSearchType + "' = 0 AND pr.PaymentRequestID IS NULL ) OR ( '" + (int)data.PaymentRequestSearchType + "' = 1 AND pr.PaymentRequestID IS NOT NULL ) )" + Environment.NewLine
+ " AND ( '" + (int)data.PaymentSearchType + "' = -1 OR ( '" + (int)data.PaymentSearchType + "' = 0 AND p.PaymentID IS NULL ) OR ( '" + (int)data.PaymentSearchType + "' = 1 AND p.PaymentID IS NOT NULL ) )" + Environment.NewLine
+ " AND ( '" + (int)data.TransactionSearchType + "' = -1 OR ( '" + (int)data.TransactionSearchType + "' = 0 AND t.TransactionID IS NULL ) OR ( '" + (int)data.TransactionSearchType + "' = 1 AND t.TransactionID IS NOT NULL ) ) " + Environment.NewLine
+ " AND ( '" + data.UseOLReference + "' = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) " + Environment.NewLine
+ " AND ( '" + data.Msisdn + "' = '' " + DataObjectBase.PrepareList("c.Msisdn='{0}'", data.Msisdns) + " " + DataObjectBase.PrepareList("c.EncryptedMsisdn = '{0}'", data.Msisdns) + " ) " + Environment.NewLine
+ " AND ( '" + data.Pxid + "' = '' " + DataObjectBase.PrepareList("us.EntranceUrl LIKE '%pxid={0}%'", data.Pxids) + " ) " + Environment.NewLine
+ " AND ( '" + data.IP + "' = '' " + DataObjectBase.PrepareList("us.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
+ timeLoad + Environment.NewLine
+ " ORDER BY us.UserSessionID DESC";
this._lastCommand = command;
#endregion
this._result = new List <EntranceTable>();
this._database.SetTimeout(0);
DataTable table = this._database.Load(command);
if (table == null)
{
return(this._result);
}
;
foreach (DataRow row in table.Rows)
{
EntranceTable tableRow = new EntranceTable(row);
this._result.Add(tableRow);
}
return(this._result);
}
// SUMMARY: only this is in use! TIMELINE
public TimelineTable LoadTimeline(EntranceTableData data)
{
string command = "";
#region # prepare reference days #
DateTime startingDate = DateTime.Parse(data.From),
endingDate = DateTime.Parse(data.To),
referenceStartDate = DateTime.Now,
referenceEndDate = DateTime.Now;
switch (data.StatisticGroupBy)
{
case "HOUR":
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
startingDate = startingDate.AddDays(1);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
break;
case "WEEK":
startingDate = startingDate.AddDays(1 - (int)startingDate.DayOfWeek);
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
startingDate = startingDate.AddDays(7);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0);
data.StatisticGroupBy = "DAY";
break;
case "MONTH":
referenceStartDate = new DateTime(startingDate.Year, startingDate.Month, 1, 0, 0, 0);
startingDate = startingDate.AddMonths(1);
referenceEndDate = new DateTime(startingDate.Year, startingDate.Month, 1, 0, 0, 0);
data.StatisticGroupBy = "DAY";
break;
case "YEAR":
referenceStartDate = new DateTime(startingDate.Year, 1, 1, 0, 0, 0);
startingDate = startingDate.AddYears(1);
referenceEndDate = new DateTime(startingDate.Year, 1, 1, 0, 0, 0);
data.StatisticGroupBy = "MONTH";
break;
}
#endregion
string startDate = data.ConvertDate(referenceStartDate),
endDate = data.ConvertDate(referenceEndDate);
#region # sql command #
command = " SELECT us._time, us.num AS 'us_num', pr.num AS 'pr_num', t.num AS 't_num', st.num AS 'st_num' " +
" FROM " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", us.Created) AS _time FROM MobilePaywall.core.UserSession AS us, " +
//" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
//" WHERE us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' " +
" ( SELECT UserSessionGuid FROM MobilePaywall.core.UserSession AS us WHERE us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' GROUP BY us.UserSessionGuid ) AS us1, " +
" ( SELECT ServiceID, Name, Description FROM MobilePaywall.core.Service AS s ) AS s " +
" WHERE us1.UserSessionGuid=us.UserSessionGuid AND us.ServiceID=s.ServiceID AND us.Created >= '" + startDate + "' AND us.Created <= '" + endDate + "' " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", us.Created) " +
" ) AS us " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", pr.Created) AS _time FROM MobilePaywall.core.PaymentRequest AS pr " +
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE pr.Created >= '"+ startDate + "' AND pr.Created <= '" + endDate + "' " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", pr.Created) " +
" ) AS pr ON us._time=pr._time " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", t.Created) AS _time FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID "+
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID "+
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE t.Created >= '"+ startDate + "' AND t.Created <= '" + endDate + "' AND t.TransactionStatusID=5 " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" AND (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID)=1 "+
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", t.Created) "+
" ) AS t ON t._time=us._time " +
" LEFT OUTER JOIN " +
" ( " +
" SELECT COUNT(*) AS num, DATEPART("+ data.StatisticGroupBy + ", t.Created) AS _time FROM MobilePaywall.core.[Transaction] AS t " +
" LEFT OUTER JOIN MobilePaywall.core.Payment AS p ON t.PaymentID=p.PaymentID "+
" LEFT OUTER JOIN MobilePaywall.core.PaymentRequest AS pr ON p.PaymentRequestID=pr.PaymentRequestID "+
" LEFT OUTER JOIN MobilePaywall.core.UserSession AS us ON pr.UserSessionID=us.UserSessionID "+
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID "+
" WHERE t.Created >= '"+ startDate + "' AND t.Created <= '" + endDate + "' AND t.TransactionStatusID=5 " +
" AND ( '" + data.Service + "' = '' " + DataObjectBase.PrepareList("s.Name LIKE '%{0}%'", data.Services) + " ) " +
" AND ( '" + data.MobileOperator + "' = -1 OR us.MobileOperatorID = '" + data.MobileOperator + "' ) " +
" AND ( '" + data.Country + "' = '' " + DataObjectBase.PrepareList("s.Description LIKE UPPER('{0}') + '%' ", data.Countries) + " ) " +
" AND (SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] WHERE PaymentID=p.PaymentID)>1 "+
" GROUP BY DATEPART("+ data.StatisticGroupBy + ", t.Created) "+
" ) AS st ON st._time=us._time " +
" ORDER BY us._time ASC ";
#endregion
TimelineTable returnTable = new TimelineTable();
returnTable.Title = string.Format("Group by {0} in time of {1} - {2}", data.StatisticGroupBy, referenceStartDate.ToString(), referenceEndDate.ToString());
DataTable table = this.Load(command);
if (table == null)
{
return(returnTable);
}
foreach (DataRow row in table.Rows)
{
returnTable.Time.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Time].ToString()) ? "0" : row[(int)TimelineTable.Columns.Time].ToString());
returnTable.UserSession.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.UserSession].ToString()) ? "0" : row[(int)TimelineTable.Columns.UserSession].ToString());
returnTable.Identification.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Identification].ToString()) ? "0" : row[(int)TimelineTable.Columns.Identification].ToString());
returnTable.Transaction.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Transaction].ToString()) ? "0" : row[(int)TimelineTable.Columns.Transaction].ToString());
returnTable.Subsequent.Add(string.IsNullOrEmpty(row[(int)TimelineTable.Columns.Subsequent].ToString()) ? "0" : row[(int)TimelineTable.Columns.Subsequent].ToString());
}
return(returnTable);
}
public List <EntranceTableAndroid> QueryNewAndroid(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() + " ) ";
#endregion
command = " SELECT " + Environment.NewLine
+ data.Top + Environment.NewLine
+ " cache.UserSessionID, " + Environment.NewLine
+ " cache.ServiceName, " + Environment.NewLine
+ " cache.CountryCode, " + Environment.NewLine
+ " cache.MobileOperatorName, " + Environment.NewLine
+ " cache.IdentificationSessionGuid, " + Environment.NewLine
+ " cache.PaymentRequestID, " + Environment.NewLine
+ " p.PaymentStatusID, " + Environment.NewLine
+ " cache.TransactionID, " + 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.TransactionUseUserSessionDate ? " cache.IsSubseguent=1 " : " cache.IsSubseguent=0 ") + Environment.NewLine
+ _REFERER_WHERE + Environment.NewLine
+ " AND cache.SessionCreated >= '" + data.From + "' AND cache.SessionCreated <= '" + data.To + "' " + Environment.NewLine
+ " ORDER BY cache.OLCacheID DESC";
#endregion
this._lastCommand = command;
List <EntranceTableAndroid> result = new List <EntranceTableAndroid>();
this._database.SetTimeout(0);
DataTable table = this._database.Load(command);
if (table == null)
{
return(result);
}
foreach (DataRow row in table.Rows)
{
EntranceTableAndroid tableRow = new EntranceTableAndroid(row);
result.Add(tableRow);
}
return(result);
}
// SUMMARY: QuickReport all in one load
public List <DataRow> LoadQuickReports(EntranceTableData data)
{
string command = "";
#region # sql command #
command = " " +
" DECLARE @result TABLE " +
" ( " +
" ID INT, " +
" UserSession INT, " +
" Identification INT, " +
" PaymentReqeustALL INT, " +
" PaymentRequestCOMPLETE INT, " +
" PaymentRequestEXISTS INT, " +
" PaymentRequestFAILED INT, " +
" PaymentALL INT, " +
" PaymentSUCCESS INT, " +
" PaymentFAILED INT, " +
" PaymentPENDING INT, " +
" PaymentCANCEL INT, " +
" TransactionNEW INT, " +
" TransactionSUBSEQUENTS INT " +
" ) " +
" INSERT INTO @result (ID) VALUES (1); " +
" " +
// " -- Getting user sessions " +
" DECLARE @us TABLE ( UserSessionID INT, CustomerID INT ); " +
" INSERT INTO @us " +
" SELECT UserSessionID, CustomerID FROM MobilePaywall.core.UserSession AS us " +
" LEFT OUTER JOIN MobilePaywall.core.Service AS s ON us.ServiceID=s.ServiceID " +
" WHERE " +
" ( '" + data.Country + "' = '' OR s.Description LIKE UPPER('" + data.Country + "') + '%' ) AND " +
" ( '" + data.Service + "' = '' OR s.Name LIKE '%' + LOWER('" + data.Service + "') + '%' ) AND " +
" ( " + data.MobileOperator + " = -1 OR us.MobileOperatorID = " + data.MobileOperator + " ) AND " +
" ( " + data.UseOLReference + " = 0 OR us.Referrer LIKE '" + data.OLRefferer + "' + '%' ) AND " +
" ( '" + data.ReferrerContains + "'='' " + data.PrepareReffererParts() + " ) AND " +
" us.Created >= '" + data.From + "' AND us.Created <= '" + data.To + "'; " +
" UPDATE @result SET UserSession = ( SELECT COUNT(*) FROM @us ) " +
" UPDATE @result SET Identification = ( SELECT COUNT(*) FROM @us WHERE CustomerID IS NOT NULL); " +
" " +
//" --Getting Payment reqeusts " +
" DECLARE @pr AS TABLE ( PaymentRequestID INT, StatusID INT ) " +
" INSERT INTO @pr " +
" SELECT pr.PaymentRequestID, pr.PaymentRequestStatusID FROM MobilePaywall.core.PaymentRequest AS pr, @us AS us " +
" WHERE pr.UserSessionID=us.UserSessionID " +
" " +
" UPDATE @result SET PaymentReqeustALL = (SELECT COUNT(*) FROM @pr ) WHERE ID=1 " +
" UPDATE @result SET PaymentRequestCOMPLETE = (SELECT COUNT(*) FROM @pr WHERE StatusID=3 ) WHERE ID=1 " +
" UPDATE @result SET PaymentRequestEXISTS = (SELECT COUNT(*) FROM @pr WHERE StatusID=5 ) WHERE ID=1 " +
" UPDATE @result SET PaymentRequestFAILED = (SELECT COUNT(*) FROM @pr WHERE StatusID=4 ) WHERE ID=1 " +
" " +
//" -- Getting payments " +
" DECLARE @p AS Table ( PaymentID INT, StatusID INT ); " +
" INSERT INTO @p " +
" SELECT PaymentID, PaymentStatusID FROM MobilePaywall.core.Payment AS p, @pr AS pr " +
" WHERE p.PaymentRequestID=pr.PaymentRequestID; " +
" " +
" UPDATE @result SET PaymentALL = (SELECT COUNT(*) FROM @p ) WHERE ID=1 " +
" UPDATE @result SET PaymentSUCCESS = (SELECT COUNT(*) FROM @p WHERE StatusID=3 ) WHERE ID=1 " +
" UPDATE @result SET PaymentFAILED = (SELECT COUNT(*) FROM @p WHERE StatusID=4) WHERE ID=1 " +
" UPDATE @result SET PaymentCANCEL = (SELECT COUNT(*) FROM @pr WHERE StatusID=5 ) WHERE ID=1 " +
" UPDATE @result SET PaymentPENDING = (SELECT COUNT(*) FROM @p WHERE StatusID=2 ) WHERE ID=1 " +
" " +
//" -- Getting Transactions " +
" UPDATE @result SET TransactionNEW = ( " +
" SELECT COUNT(*) FROM MobilePaywall.core.[Transaction] AS t, @p AS p " +
" WHERE t.PaymentID=p.PaymentID " +
" ); " +
" " +
" SELECT * FROM @result; ";
#endregion
DataTable table = this.Load(command);
List <DataRow> rows = new List <DataRow>();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
return(rows);
}
