public static function getCountryDefault($countryID) { if (!$countryID) { return false; } $sql = "SELECT *\n FROM `taxes`\n WHERE `country_id` = ?s\n AND `state_code` IS NULL"; $db = self::$_msql = SafeMySQL::getInstance(); $result = $db->getRow($sql, $countryID); if ($result) { $tax = new Tax(); $tax->fillFromArray($result); return $tax; } return false; }
public function __construct() { if (!self::$db) { self::$db = SafeMySQL::getInstance(); } parent::__construct(); }
public function beforeSave() { // make sure there isn't already a record with the same country and state code $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT tax_id\n FROM `taxes`\n WHERE `country_id` = ?s"; if ($this->state_code) { $sql .= " and state_code = ?s"; } else { $sql .= " and state_code is NULL"; } if ($this->tax_id) { $sql .= " and tax_id = ?i"; if ($this->state_code) { $result = $db->getOne($sql, $this->country_id, $this->state_code, $this->tax_id); } else { $result = $db->getOne($sql, $this->country_id, $this->tax_id); } } else { if ($this->state_code) { $result = $db->getOne($sql, $this->country_id, $this->state_code); } else { $result = $db->getOne($sql, $this->country_id); } } if ($result) { return false; } else { return TRUE; } }
public static function firePixelPb($model) { $db = SafeMySQL::getInstance(); if (!$model instanceof Order) { $model = Order::model()->findByPk($model); } if (!$model->order_id) { return false; } // NOTE: temp. If a payment bank in array - we don't need to fire pixel if ($model->payment && is_object($model->payment) && $model->payment->num2 && in_array($model->payment->num2, self::$_bannedBins) && in_array($model->campaign_id, self::$_bannedCampaigns)) { return false; } $fire = false; if ($model->aff_id) { $sql = 'SELECT * FROM `pixel_rates` WHERE `campaign_id` = ?i AND `method_id` = ?i AND `aff_id` = ?i'; $result = $db->query($sql, $model->campaign_id, $model->payment->method_id, $model->aff_id); $count_rows = $result->num_rows; } else { $count_rows = 0; } if (!$model->aff_id || !$count_rows) { $sql = 'SELECT * FROM `pixel_rates` WHERE `campaign_id` = ?i AND `method_id` = ?i AND `aff_id` IS NULL'; $result = $db->query($sql, $model->campaign_id, $model->payment->method_id); if (!$result->num_rows) { $fire = true; } else { $fire_result = $db->fetch($result); if ($fire_result['rate']) { $fire = true; } if ($model->aff_id) { $r = $fire_result['rate'] == 0 ? 0 : 1; $sql = 'INSERT IGNORE INTO `pixel_rates` (`campaign_id`, `method_id`, `aff_id`, `rate`, `total_fired`, `total_orders`) SELECT `campaign_id`, `method_id`, ?i, `rate`, ' . $r . ', 1 FROM `pixel_rates` WHERE `campaign_id` = ?i AND `method_id` = ?i AND `aff_id` IS NULL'; $db->query($sql, $model->aff_id, $model->campaign_id, $model->payment->method_id); } } } else { $fire_result = $db->fetch($result); if ($fire_result['rate'] != 0 && ($fire_result['total_orders'] == 0 || 100 * $fire_result['total_fired'] / ($fire_result['total_orders'] + 1) < $fire_result['rate'])) { $sql = 'UPDATE `pixel_rates` SET `total_fired` = `total_fired` + 1, `total_orders` = `total_orders` + 1 WHERE `campaign_id` = ?i AND `method_id` = ?s AND `aff_id` = ?i'; $db->query($sql, $model->campaign_id, $model->payment->method_id, $model->aff_id); $fire = true; } else { $sql = 'UPDATE `pixel_rates` SET `total_orders` = `total_orders` + 1 WHERE `campaign_id` = ?i AND `method_id` = ?s AND `aff_id` = ?i'; $db->query($sql, $model->campaign_id, $model->payment->method_id, $model->aff_id); } } if ($fire) { $sql = 'UPDATE `orders` SET `pixel_fired` = 1 WHERE `order_id` = ?i'; $db->query($sql, $model->order_id); exec('/usr/bin/php-cli /home/pinnacle/public_html/lj3/nws/pixels_pb.php ' . $model->order_id . ' > /dev/null 2>&1 &'); } }
public static function getAttachedByCampaign($campaign_id, $auto = true) { $db = SafeMySQL::getInstance(); $sql = "SELECT ca.`campattach_id`,\n\t\t\t\t\t\tca.`campaign_id`,\n ca.`campaign_attach_id`\n\t\t FROM `campaigns_attach` as ca\n WHERE ca.`campaign_id` = ?i"; if ($auto) { $sql .= " and ca.`auto_attach` = 1"; } $sql .= " ORDER BY ca.`campattach_id` ASC"; return $db->getAll($sql, $campaign_id); }
public function __construct() { if (!self::$_msql) { self::$_msql = SafeMySQL::getInstance(); } $this->setIsNewRecord(TRUE); $this->relations = $this->relations(); $this->modelName = strtolower(get_class($this)); $this->init(); }
function __construct($params) { self::$_msql = SafeMySQL::getInstance(); $modelName = get_class($this); foreach ($modelName::$vars as $k => $v) { $this->{$k} = isset($params[$k]) ? $params[$k] : $v; if ($v === FALSE && $this->{$k} === FALSE) { $this->setErrors('Undefined params ' . $k); } } }
private static function fillFromDatabase() { $cacheID = "all_currencies"; self::$_allCurrencies = AF::cache()->get($cacheID); if (!self::$_allCurrencies) { $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT * FROM `currency`"; self::$_allCurrencies = $db->getInd('currency_id', $sql); AF::cache()->set($cacheID, self::$_allCurrencies); } }
public function getResultsByCustomerId($id, $cc = true) { if (!$id) { return array(); } $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT\n\t\t\tp.`payment_id`,\n\t\t\tp.`customer_id`,\n\t\t\tp.`method_id`,\n\t\t\tp.`num1`,\n\t\t\tp.`num2`,\n\t\t\tp.`num3`,\n\t\t\tp.`num4`,\n\t\t\tp.`txt1`,\n\t\t\tp.`txt2`,\n\t\t\tp.`txt3`,\n\t\t\tp.`txt4`"; $sql .= $cc ? " , p.`note1`" : ''; $sql .= "FROM `payments` as p\n\t\t\tWHERE p.`customer_id`=?i"; $sqlParse = $db->parse($sql, $id); return $this->getSqlParse($sqlParse); }
public static function getDoubleShippingSKUArray($campaignArray, $campaignID) { self::$DoubleShippingSKUArray = array(); if (!$campaignArray) { return array(); } $db = SafeMySQL::getInstance(); $sql = "SELECT cp.`campaign_id`\n FROM `products` as p\n JOIN `campaigns_products` as cp ON p.`product_id` = cp.`product_id`\n WHERE cp.`campaign_id` IN (?a)\n AND p.`product_shipping_sku` != ''"; $result = $db->getAll($sql, $campaignArray); $resultArray = array(); foreach ($result as $item) { $resultArray[] = $item['campaign_id']; } self::$DoubleShippingSKUArray = in_array($campaignID, $resultArray) ? $resultArray : array(); }
public function getResultsById($id, $ps = null) { if (!$id) { return array(); } $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT\n\t\t\t\tpm.`profile_id`,\n\t\t\t\tpm.`method_id`,\n\t\t\t\tm.`method_name`,\n\t\t\t\tm.`method_ref`,\n\t\t\t\tpm.`load_balance`,\n\t\t\t\tg.`system_code`\n FROM `profiles_methods` as pm\n\t\t\t JOIN `methods` as m on m.`method_id` = pm.`method_id`\n\t\t\t JOIN `profiles_gateways` as pg on pg.`profile_id` = pm.`profile_id` and pg.`method_id` = pm.`method_id`\n\t\t\t JOIN `gateways` as g on g.`gateway_id` = pg.`gateway_id`\n WHERE pm.`profile_id` = ?i"; if ($ps && strlen($ps)) { $sql .= " AND g.`system_code` = ?s"; $sqlParse = $db->parse($sql, $id, $ps); } else { $sqlParse = $db->parse($sql, $id); } return $this->getSqlParse($sqlParse); }
function privilegeAction() { $model = new User(); $model->allFIelds = true; $id = AF::get($this->params, 'id', 0); if (!$id) { throw new AFHttpException(0, 'no_id'); } if (!$model->setByID($id)) { throw new AFHttpException(0, 'incorrect_id'); } $access = new Access(); $access->fillFromUser($model); $userAccess = $access->getUserUpdateAccess(); ksort($userAccess); if (isset($_POST['ajax'])) { $newAcces = AF::get($_POST, 'array'); if ($newAcces) { $access->setUserAccess($newAcces); // hack to get the uesrs_access table to update instead of insert $msql = SafeMySQL::getInstance(); $sql = "SELECT * FROM ?n WHERE user_id = ?i"; $result = $msql->getRow($sql, $access->tableName(), $access->user_id); if (!empty($result)) { $access->setIsNewRecord(0); } if ($access->save()) { $model->user_id_updated = $this->user->user_id; $model->updated = 'NOW():sql'; $model->IsNewRecord = false; $model->save(); Message::echoJsonSuccess(__('user_access_updated')); } else { Message::echoJsonError(__('user_access_not_updated')); } } else { Message::echoJsonError(__('user_access_not_updated')); } } Assets::js('jquery.form'); $this->addToPageTitle('User privilege'); $this->render('privilege', array('userAccess' => $userAccess, 'model' => $model)); }
public static function updateSites($campaignID) { $modelCampaign = Campaign::model()->with('domain')->cache()->findByPk($campaignID); if (!$modelCampaign) { return false; } $db = SafeMySQL::getInstance(); $sql = "SELECT c.`url`,\n c.`campaign_id`,\n c.`country_id`,\n c.`currency_id`\n FROM `campaigns` as c\n WHERE c.`domain_id` = ?i"; $campaigns = $db->getAll($sql, $modelCampaign->domain_id); $rArray = array(); $cIDs = array(); foreach ($campaigns as $campaign) { $cIDs[] = $campaign['campaign_id']; $rArray[$campaign['campaign_id']] = array('main' => array('path' => $campaign['url'], 'campaign_id' => $campaign['campaign_id'], 'country_id' => $campaign['country_id'], 'currency_id' => $campaign['currency_id']), 'products' => array(), 'custom_fields' => array()); $sql = "SELECT `name`, `value`\n FROM `site_variables`\n WHERE (`domain_id`=?i AND`campaign_id`=?i)\n OR (`domain_id`=?i AND `campaign_id` IS NULL)\n OR (`domain_id` IS NULL AND `campaign_id` IS NULL)"; $customFields = $db->getAll($sql, $modelCampaign->domain_id, $campaign['campaign_id'], $modelCampaign->domain_id); if ($customFields) { foreach ($customFields as $field) { $rArray[$campaign['campaign_id']]['custom_fields'][$field['name']] = $field['value']; } } } $sql = "SELECT p.`product_id`,\n p.`prodcat_id`,\n p.`product_name`,\n p.`product_price`,\n p.`product_weight`,\n p.`product_next_id`,\n p.`subscription_days`,\n cp.`campaign_id`\n FROM `products` as p\n JOIN `campaigns_products` as cp USING (`product_id`)\n WHERE cp.`campaign_id` IN (?a)\n AND cp.`enabled` = 1\n AND cp.`upsell_id` IS NULL"; $products = $db->getAll($sql, $cIDs); if ($products) { foreach ($products as $product) { $campaignID = $product['campaign_id']; unset($product['campaign_id']); array_push($rArray[$campaignID]['products'], $product); } } $siteApi = new SiteApi(); if (!$siteApi->createFile(json_encode($rArray))) { //save an error to the log file //.. return false; } $siteApi->domain = $modelCampaign->domain->url; $siteApi->update(); }
echo long2ip(2130706433); die; //time $mtime = explode(" ", microtime()); $Smtime = $mtime[1] + $mtime[0]; $start_memory_usage = memory_get_usage(); //timeend--- /** * User: Anton Antonov * Date: 6/6/14 * Time: 7:47 AM */ @(include_once 'settings/autoload.php'); $productArray['subscription_days'] = 2; $db = SafeMySQL::getInstance(); $result = array(); $result['status'] = 'upsell'; //$result['discount_next'] = 0; //$result['created'] = date("Y-m-d H:i:s"); $result['amount_product'] = 11.2; $result['amount_shipping'] = 0; $result['amount_refunded'] = 0; $result['payment_total'] = 0; //$productArray['product_price']; $result['pixel_fired'] = 0; //$result['product_id'] = 111; $result['shipping_id'] = 111; //$result['rma_code'] = 'NULL:sql'; $result['recurring'] = $productArray['subscription_days'] ? 0 : 'NULL:sql'; $result['billing_cycle'] = 0;
public static function removeAllByCM($campaignID, $methodID) { $msql = SafeMySQL::getInstance(); $sql = "DELETE FROM `pixel_rates`\n WHERE `campaign_id` = ?i\n AND `method_id`= ?i"; $msql->query($sql, $campaignID, $methodID); }
public static function getAttemptNumberByOrderID($orderID) { $msql = SafeMySQL::getInstance(); $sql = "SELECT `attempt_number` FROM `attempts` WHERE `order_id`=?i ORDER BY `attempt_number` DESC LIMIT 1"; $result = $msql->getRow($sql, $orderID); return isset($result['attempt_number']) && $result['attempt_number'] ? $result['attempt_number'] + 1 : 1; }
public static function getPixelratesAffids($campaignID, $methodID) { $msql = SafeMySQL::getInstance(); $sql = "SELECT a.`aff_id`, a.`aff_name`\n FROM `affiliates` as a\n LEFT JOIN `pixel_rates` as pr ON a.`aff_id`=pr.`aff_id` AND pr.`method_id`=?i AND pr.`campaign_id`=?i\n WHERE pr.`pixel_rate_id` IS NULL"; return $msql->getAll($sql, (int) $methodID, (int) $campaignID); }
/** * @param $gatewayID * @param $methodID * @param $amount * @return bool */ public static function add($gatewayID, $methodID, $amount) { $gatewayID = (int) $gatewayID; $methodID = (int) $methodID; $amount = number_format($amount, 2); if (!$gatewayID || !$methodID) { return false; } $db = SafeMySQL::getInstance(); $sql = "UPDATE `gateway_limits`\n SET `orders_count` = `orders_count`+1, `amount_count` = `amount_count` + ?s\n WHERE `method_id` = ?i\n AND `gateway_id` = ?i"; $db->query($sql, $amount, $methodID, $gatewayID); return true; }
public static function setNewRecurringDateByDay($ordersArray, $day, $dateMin = null, $dateMax = null) { $msql = SafeMySQL::getInstance(); if (!$dateMin) { $dateMin = '2000-01-01'; } if (!$dateMax) { $dateMax = '2100-01-01'; } foreach ($ordersArray as $orderID) { $sql = "UPDATE `orders_products`\n SET `recurring_next`= if(DATE_ADD(`recurring_next`, INTERVAL ?i DAY)>?s,?s, if(DATE_ADD(`recurring_next`, INTERVAL ?i DAY)<?s, ?s, DATE_ADD(`recurring_next`, INTERVAL ?i DAY)))\n WHERE `order_id` = ?i\n AND `recurring_next` IS NOT NULL"; $msql->query($sql, $day, $dateMax, $dateMax, $day, $dateMin, $dateMin, $day, $orderID); } }
/** * @return array of addresses to check for duplicates and reuse the address_id. */ public function getExistingAddresses($customer_id) { $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT *\n FROM `addresses`\n WHERE `customer_id` = ?i"; return $db->getAll($sql, $customer_id); }
public function __construct($modelName) { $this->modelName = $modelName; self::$_msql = SafeMySQL::getInstance(); }
public static function searchByEmail($email) { $msql = SafeMySQL::getInstance(); $searchText = "%{$email}%"; $sql = "SELECT `customer_id`, `email`\n FROM `customers`\n WHERE `email` LIKE ?s\n LIMIT 10"; return $msql->getAll($sql, $searchText); }
<?php include_once '../settings/autoload.php'; $msql = SafeMySQL::getInstance(); $sql = "DELETE FROM jobs\n WHERE job_status = 'done'"; $msql->query($sql); $sql = "UPDATE `jobs` SET `job_status` = 'new'\n WHERE job_status = 'processing'\n AND (updated + INTERVAL 1 DAY) < NOW()"; $msql->query($sql);
public static function updateRecurringNext($orderProductID) { $msql = SafeMySQL::getInstance(); $sql = "UPDATE `orders_products`\n JOIN `products` USING(`product_id`)\n SET `orders_products`.`recurring_next` = DATE_ADD(NOW(), INTERVAL `products`.`subscription_days` DAY)\n WHERE `orders_products`.`order_product_id`=?i"; $msql->query($sql, $orderProductID); }
public static function rebillNow($orderID, $productID) { //@o1 - order_id //@op1 - order_product_id //@o2 - new order_id $msql = SafeMySQL::getInstance(); $sql = "INSERT INTO `orders` (`status`, `flags`, `created`, `parent_id`, `customer_id`, `campaign_id`, `payment_id`, `gateway_id`, `shipping_id`, `amount_product`, `amount_shipping`, `aff_id`, `click_id`, `ip`, `address_id`, `billing_address_id`, `billing_cycle`)\n SELECT 'new' AS `status`, '' AS `flags`, NOW() AS `created`, ?i AS `parent_id`, `o`.`customer_id`, `o`.`campaign_id`, `o`.`payment_id`, IF(`op`.`next_gateway_id` IS NOT NULL, `op`.`next_gateway_id`, `o`.`gateway_id`) AS `gateway_id`, `s`.`shipping_id` AS `shipping_id`, ROUND(`p2`.`product_price` * (100 - IF(`op`.`discount_next` IS NULL, 0,`op`.`discount_next`)) / 100, 2) AS `amount_product`, IF(`p2`.`shippable` = 1, `s`.`amount_subscription`, 0) AS `amount_shipping`, `o`.`aff_id`, `o`.`click_id`, `o`.`ip`, `o`.`address_id`, `o`.`billing_address_id`, `o`.`billing_cycle`+1 AS `billing_cycle`\n FROM `orders` AS `o`\n JOIN `orders_products` AS `op`\n JOIN `products` AS `p` USING (`product_id`)\n JOIN `products` AS `p2` ON `p`.`product_next_id` = `p2`.`product_id`\n JOIN `shipping` AS `s` ON `op`.`shipping_id` = `s`.`shipping_id`\n WHERE `o`.`order_id` = ?i AND `op`.`order_product_id` = ?i"; $msql->query($sql, (int) $orderID, (int) $orderID, (int) $productID); $newOrderID = $msql->insertId(); $sql = 'INSERT IGNORE INTO `orders_recurring` (`order_id`, `next_order_id`) VALUES (?i, ?i)'; $msql->query($sql, (int) $orderID, (int) $newOrderID); $sql = 'INSERT IGNORE INTO `order_logs` (`order_id`, `user_id`, `action`, `create`, `notes`) VALUES (?i, 0, 26, NOW(), ?i)'; $msql->query($sql, (int) $orderID, (int) $newOrderID); $sql = "INSERT INTO `orders_products` (`order_id`, `product_id`, `flags`, `discount_next`, `shipping_id`)\n SELECT ?i AS `order_id`, `p`.`product_next_id` AS `product_id`, IF(FIND_IN_SET('stop_next_recurring', `op`.`flags`) = 0 AND `p2`.`product_next_id` IS NOT NULL, IF(FIND_IN_SET('keep_discount', `op`.`flags`) > 0, 'recurring,keep_discount', 'recurring'), '') AS `flags`, IF(FIND_IN_SET('stop_next_recurring', `op`.`flags`) = 0 AND `p2`.`product_next_id` IS NOT NULL AND FIND_IN_SET('keep_discount', `op`.`flags`) > 0, `op`.`discount_next`, NULL) AS `discount_next`, `op`.`shipping_id`\n FROM `orders_products` AS `op`\n JOIN `products` AS `p` USING (`product_id`)\n JOIN `products` AS `p2` ON `p`.`product_next_id` = `p2`.`product_id`\n WHERE `op`.`order_product_id` = ?i"; $msql->query($sql, (int) $newOrderID, (int) $productID); $sql = "UPDATE `orders_products` AS `op`\n SET `flags` = `flags` & ~pow(2, FIND_IN_SET('recurring', `flags`) - 1)\n WHERE `op`.`order_product_id` = ?i"; $msql->query($sql, (int) $productID); return $newOrderID; }
public static function isAccess($ip) { $cacheID = 'ip_' . md5($ip); $result = AF::cache()->get($cacheID); if (!$result) { $ipArray = explode('.', $ip); if (count($ipArray) != 4) { return false; } $msql = SafeMySQL::getInstance(); $sql = "SELECT count(`id`) as `count`\n FROM `ip_access`\n WHERE (`p1`=?i OR `p1` IS NULL)\n AND (`p2`=?i OR `p2` IS NULL)\n AND (`p3`=?i OR `p3` IS NULL)\n AND (`p4`=?i OR `p4` IS NULL)"; $result = $msql->getRow($sql, $ipArray[0], $ipArray[1], $ipArray[2], $ipArray[3]); AF::cache()->set($cacheID, $result, self::CACHE_USER_IP); $cacheArrayID = 'array_ip'; $arrayIps = AF::cache()->get($cacheArrayID); if (!$arrayIps) { $arrayIps = array(); } // Clean old keys foreach ($arrayIps as $k => $v) { if ($v + self::CACHE_USER_IP < time()) { unset($arrayIps[$k]); } } if (!isset($arrayIps[$cacheID])) { $arrayIps[$cacheID] = time(); AF::cache()->delete($cacheArrayID); AF::cache()->set($cacheArrayID, $arrayIps); } } return isset($result['count']) && $result['count'] ? true : false; }
public static function chargeBack($customerID) { $db = SafeMySQL::getInstance(); $sql = "UPDATE `orders` as o\n JOIN `orders_products` as op USING(`order_id`)\n SET\n o.`flags`=o.`flags`&~pow(2,FIND_IN_SET('retry', o.`flags`)-1),\n op.`flags`=op.`flags`&~pow(2,FIND_IN_SET('recurring', op.`flags`)-1)\n WHERE o.`customer_id`=?i"; $db->query($sql, (int) $customerID); }
public function getAddressById($address_id) { $db = self::$_msql = SafeMySQL::getInstance(); $sql = "SELECT a.*, c.country_name, s.state_name\n FROM `addresses` a\n\t\t\t\tJOIN `countries` c USING(country_id)\n\t\t\t\tJOIN `states` s ON s.state_code = a.state_id\n WHERE `address_id` = ?i"; return $db->getRow($sql, $address_id); }
private static function parserCsvFiles($downloadFilePath) { $header = NULL; $data = array(); if (($handle = fopen($downloadFilePath, 'r')) !== FALSE) { while (($row = fgetcsv($handle, 1000, ',')) !== FALSE) { if (!$header) { $header = $row; } else { $data[] = array_combine($header, $row); } } fclose($handle); } $db = SafeMySQL::getInstance(); echo '<pre>'; print_r($db->getConn()); echo '</pre>'; foreach ($data as $orderArray) { if (isset($orderArray['Tracking Number']) && isset($orderArray['Order Id'])) { /* * Old query $sql = "UPDATE `orders` SET `tracking_number`=?s, `status`='shipped' WHERE (`order_id`=?i OR `ship_with`=?i) AND `status` = 'sent'"; */ $sql = "UPDATE `packages_orders` AS `po`\n JOIN `packages` AS `p` USING (`package_id`)\n LEFT JOIN `orders` AS `o` ON `o`.`order_id` = `po`.`order_id` AND `o`.`status` IN ('ok', 'sent')\n SET `p`.`tracking_number` = ?s, `o`.`status` = 'shipped'\n WHERE `package_id` =\n (SELECT `package_id` FROM `packages_orders` AS `p1` WHERE `p1`.`order_id`=?i LIMIT 1)"; $orderID = (int) $orderArray['Order Id']; echo $sql . '<hr>'; $db->query($sql, $orderArray['Tracking Number'], $orderID); if ($db->affectedRows() > 0) { Event::setEvents($orderID, 9); OrderLog::createLog(0, $orderID, 20); } } } }
function newretentionAction() { $clearArray = array('campaign_id', 'detail_dates', 'detail_affiliates', 'detail_sid', 'simple', 'currency_id', 'country_id'); $this->filter($clearArray); $filterFields = $this->params; //AFActiveDataProvider::clearDateArray($this->params, array('r_dates')); if (!isset($filterFields['circle']) || isset($filterFields['circle']) && !$filterFields['circle']) { $filterFields['circle'] = 0; } if ($clearArray) { foreach ($clearArray as $value) { if (isset($filterFields[$value])) { $filterFields[$value] = explode(',', $filterFields[$value]); } } } // build filter select datasources $currencies = Currency::model()->cache()->findAllInArray(); $countries = Country::model()->cache()->findAllInArray(); $campaigns = isset($filterFields['campaign_id']) ? $filterFields['campaign_id'] : null; $country_id = isset($filterFields['country_id']) ? $filterFields['country_id'] : null; $currency_id = isset($filterFields['currency_id']) ? $filterFields['currency_id'] : null; $groupDate = isset($filterFields['detail_dates']) ? false : true; $groupAfid = isset($filterFields['detail_affiliates']) ? false : true; $groupSid = isset($filterFields['detail_sid']) ? false : true; $msql = SafeMySQL::getInstance(); $sql = 'SELECT campaign_id, campaign_name FROM `campaigns` ORDER BY `campaign_id`'; $campaignsFilterTemp = $msql->getAll($sql); $campaignsFilter = array(); foreach ($campaignsFilterTemp as $v) { $campaignsFilter[$v['campaign_id']] = $v; } unset($campaignsFilterTemp); $sql = 'SELECT aff_id, aff_name FROM `affiliates` ORDER BY `aff_id`'; $affidsTemp = $msql->getAll($sql); $affids = array(); foreach ($affidsTemp as $v) { $affids[$v['aff_id']] = $v; } unset($affidsTemp); $where = ''; if ($campaigns) { $where .= $msql->parse(" AND `campaign_id` IN (?a)", $campaigns); } if ($currency_id) { $where .= $msql->parse(" AND `campaign_id` IN ( select campaign_id from campaigns where currency_id in (?a))", $currency_id); } if ($country_id) { $where .= $msql->parse(" AND `campaign_id` IN ( select campaign_id from campaigns where country_id in (?a))", $country_id); } if (!isset($filterFields['r_dates'])) { $filterFields['r_dates'] = date('d.m.Y-d.m.Y'); } $dates = explode('-', $filterFields['r_dates']); if (isset($dates[0]) && isset($dates[1])) { $dateStartT = explode('.', $dates[0]); $dateStart = array_reverse($dateStartT); $dateStart = implode('-', $dateStart); $dateFinishT = explode('.', $dates[1]); $dateFinish = array_reverse($dateFinishT); $dateFinish = implode('-', $dateFinish); $where .= $msql->parse(" AND DATE(`date`) BETWEEN ?s AND ?s", $dateStart, $dateFinish); unset($dateStartT, $dateFinishT); } if (!isset($filterFields['report_date'])) { $filterFields['report_date'] = date('d.m.Y'); } $reportDateArray = explode('.', $filterFields['report_date']); if (isset($reportDateArray[0]) && isset($reportDateArray[1]) && isset($reportDateArray[2])) { $reportDate = array_reverse($reportDateArray); $reportDate = implode('-', $reportDate); $where .= $msql->parse("AND `id_date` <= ?s", $reportDate); unset($reportDate, $reportDateArray); } $where .= $msql->parse("AND CAST(SUBSTR(`col_name`,2,1) AS UNSIGNED) <= ?i", (int) $filterFields['circle']); $sql = "\n SELECT " . ($groupDate ? "'ALL' AS " : '') . "`date`, `campaign_id`, " . ($groupAfid ? "'ALL' AS " : '') . "`aff_id`, " . ($groupSid ? "'ALL' AS " : '') . "`sid`, `col_name`, SUM(`data`) AS `data`, `currency_id`\n FROM (\n SELECT `date`, `campaign_id`, `aff_id`, `sid`, `col_name`, `data`, `currency_id`\n FROM (\n SELECT `retention_history`.*, `campaigns`.`currency_id`\n FROM `retention_history`\n LEFT JOIN `campaigns` USING(`campaign_id`)\n WHERE 1 " . $where . "\n ORDER BY `id_date` DESC\n ) `t`\n GROUP BY `date`, `campaign_id`, `aff_id`, `col_name`\n ) `t2`\n GROUP BY " . ($groupDate ? '' : "`date`, ") . "`campaign_id`, " . ($groupAfid ? '' : "`aff_id`, ") . "`col_name`\n "; $result = $msql->getAll($sql); $report = array(); $reportCur = array(); $countR = 0; foreach ($result as $row) { if (isset($row['col_name'])) { $colNameArr = explode('_', $row['col_name']); if (isset($colNameArr[0])) { $countCycles = (int) substr($colNameArr[0], 1, 1); if ($countCycles > $countR) { $countR = $countCycles; } } } if (!isset($report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']]['c' . ($row['col_name'][1] + 1) . '_gross'])) { if (!isset($report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']])) { $report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] = array(); } $report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] += self::cycle_array($row['col_name'][1]); } $report[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']][$row['col_name']] = $row['data']; if (!isset($reportCur[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']])) { $reportCur[$row['date'] . '_' . $row['campaign_id'] . '_' . $row['aff_id']] = $row['currency_id']; } } $countR++; unset($result); foreach ($report as $row => $data) { for ($i = 0; isset($data['c' . ($i + 1) . '_gross']); $i++) { $report[$row]['c' . $i . '_gross'] = (int) $report[$row]['c' . $i . '_gross']; $report[$row]['c' . $i . '_declined'] = (int) $report[$row]['c' . $i . '_declined']; $report[$row]['c' . $i . '_void'] = (int) $report[$row]['c' . $i . '_void']; $report[$row]['c' . $i . '_pending'] = (int) $report[$row]['c' . $i . '_pending']; $report[$row]['c' . $i . '_rma'] = (int) $report[$row]['c' . $i . '_rma']; $report[$row]['c' . $i . '_partial_refund'] = (int) $report[$row]['c' . $i . '_partial_refund']; $report[$row]['c' . $i . '_chargeback'] = (int) $report[$row]['c' . $i . '_chargeback']; if ($i) { $report[$row]['c' . $i . '_discounts'] = (int) $report[$row]['c' . $i . '_discounts']; } $report[$row]['c' . $i . '_decline_rate'] = $report[$row]['c' . $i . '_gross'] ? round($report[$row]['c' . $i . '_declined'] / $report[$row]['c' . $i . '_gross'], 4) : NULL; $report[$row]['c' . $i . '_paid'] = $report[$row]['c' . $i . '_gross'] - $report[$row]['c' . $i . '_declined']; $report[$row]['c' . $i . '_void_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_void'] / $report[$row]['c' . $i . '_paid'], 4) : NULL; $report[$row]['c' . $i . '_net_approved'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_void']; $report[$row]['c' . $i . '_approval_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_net_approved'] / $report[$row]['c' . $i . '_paid'], 4) : NULL; $report[$row]['c' . $i . '_cancelled'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_pending'] - $report[$row]['c' . ($i + 1) . '_gross']; $report[$row]['c' . $i . '_cancel_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_cancelled'] / $report[$row]['c' . $i . '_paid'], 4) : NULL; $report[$row]['c' . $i . '_subscriptions_approved'] = $report[$row]['c' . $i . '_paid'] - $report[$row]['c' . $i . '_cancelled']; $report[$row]['c' . $i . '_rma_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_rma'] / $report[$row]['c' . $i . '_paid'], 4) : NULL; // $report[$row]['c'.$i.'_pending_rebill'] = $report[$row]['c'.$i.'_pending'] - $report[$row]['c'.$i.'_rma']; $report[$row]['c' . $i . '_chargeback_rate'] = $report[$row]['c' . $i . '_paid'] ? round($report[$row]['c' . $i . '_chargeback'] / $report[$row]['c' . $i . '_paid'], 4) : NULL; if ($i) { $report[$row]['c' . $i . '_retention_count_rate'] = $report[$row]['c' . ($i - 1) . '_paid'] ? round($report[$row]['c' . $i . '_paid'] / $report[$row]['c' . ($i - 1) . '_paid'], 4) : NULL; $report[$row]['c' . $i . '_retention_rate'] = $report[$row]['c' . $i . '_potential_revenue'] ? round($report[$row]['c' . $i . '_net_revenue'] / $report[$row]['c' . $i . '_potential_revenue'], 4) : NULL; } } if (isset($report[$row]['c' . $countR . '_gross']) && !$report[$row]['c' . $countR . '_gross']) { unset($report[$row]['c' . $countR . '_gross']); } } $extraFields = array(array('gross', 'declined', 'decline_rate', 'paid', 'void', 'void_rate', 'net_approved', 'approval_rate', 'cancelled', 'cancel_rate', 'subscriptions_approved', 'pending', 'rma', 'rma_rate', 'partial_refund', 'refund_amount', 'chargeback', 'chargeback_rate', 'chargeback_loss', 'net_revenue'), array('gross2', 'declined', 'decline_rate', 'paid', 'void', 'void_rate', 'net_approved', 'approval_rate', 'cancelled', 'cancel_rate', 'subscriptions_approved', 'pending', 'rma', 'rma_rate', 'discounts', 'discount_amount', 'partial_refund', 'refund_amount', 'chargeback', 'chargeback_rate', 'chargeback_loss', 'retention_count_rate', 'net_revenue', 'potential_revenue', 'retention_rate')); $simpleReportFields = array('gross', 'gross2', 'decline_rate', 'cancel_rate', 'chargeback_rate', 'pending', 'net_revenue', 'potential_revenue', 'retention_rate'); // CSV builder if (isset($this->params['download_csv']) && $this->params['download_csv']) { self::retentionCsvBuilder($report, $extraFields, $campaignsFilter, $affids, $countR, $simpleReportFields, $filterFields); die; } $tempArray = explode('-', $filterFields['r_dates']); $filterFields['r_dates_post'] = $filterFields['r_dates']; $filterFields['r_dates'] = HelperFormatted::date($tempArray[0], HelperFormatted::TIME_FORMAT_DATE) . '-' . HelperFormatted::date($tempArray[1], HelperFormatted::TIME_FORMAT_DATE); $filterFields['report_date'] = HelperFormatted::date($filterFields['report_date'], HelperFormatted::TIME_FORMAT_DATE); // set ajax table if (AF::isAjaxRequestModels()) { $this->view->includeFile('_newretantion_table', array('application', 'views', 'reports'), array('access' => $this->access, 'controller' => $this->controller, 'report' => $report, 'extraFields' => $extraFields, 'countR' => $countR, 'campaignsFilter' => $campaignsFilter, 'filterFields' => $filterFields, 'affids' => $affids, 'simpleReportFields' => $simpleReportFields, 'simple' => isset($this->params['simple']) ? (int) $this->params['simple'] : 0, 'currencies' => $currencies, 'countries' => $countries, 'reportCur' => $reportCur)); die; } Assets::css('jquery-ui'); Assets::js('//code.jquery.com/ui/1.10.3/jquery-ui.js'); Assets::js('dateRange/jquery.daterange'); Assets::js('jquery.form'); // for ajax submission used to build csv Assets::js('ajax_table'); Assets::js('af_input_field'); $this->render('newretention', array('report' => $report, 'extraFields' => $extraFields, 'countR' => $countR, 'campaignsFilter' => $campaignsFilter, 'filterFields' => $filterFields, 'affids' => $affids, 'simpleReportFields' => $simpleReportFields, 'simple' => isset($this->params['simple']) ? (int) $this->params['simple'] : 0, 'currencies' => $currencies, 'countries' => $countries, 'reportCur' => $reportCur)); }