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);
        }
Ejemplo n.º 3
0
        public string LoadTransactions(EntranceTableData data)
        {
            if (data.Validation())
            {
                return(string.Format("- validation error -"));
            }

            return(JsonObjectHelper.Json(new StatisticManager().LoadTransactions(data)));
        }
Ejemplo n.º 4
0
        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)));
        }
Ejemplo n.º 5
0
        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));
        }
Ejemplo n.º 6
0
        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));
        }
Ejemplo n.º 7
0
        // 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"));
        }
Ejemplo n.º 8
0
        // 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)));
        }
Ejemplo n.º 9
0
        // 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);
        }
Ejemplo n.º 10
0
        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));
        }
Ejemplo n.º 11
0
        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));
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
        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));
        }
Ejemplo n.º 15
0
        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);
        }
Ejemplo n.º 16
0
        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);
        }
Ejemplo n.º 17
0
        // 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);
        }
Ejemplo n.º 18
0
        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);
        }
Ejemplo n.º 19
0
        // 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);
        }