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