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