/**
* Esegue recupero dati delle fatture
*
* @param date $last_imp_date ultima data di riferimento dll'ultima esportazione
* @param int $max_records numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$invoicesTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/invoices');
$ordersTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/orders');
$ordersTableMage = Intelivemetrics_Unityreports_Model_Utils::getTableName('sales_flat_order');
$now = date('Y-m-d H:i:s');
try {
$collection = Mage::getModel('sales/order_invoice')->getCollection()->addAttributeToSelect('*');
$collection->getSelect()->joinLeft(array('orders' => $ordersTableMage), "orders.entity_id=main_table.order_id", array('o_increment_id' => 'increment_id'))->where("main_table.increment_id NOT IN (SELECT increment_id FROM {$invoicesTable} WHERE synced=1 OR sents>={$this->getMaxSents()} OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')<60)")->where("orders.increment_id IN (SELECT increment_id FROM {$ordersTable} WHERE synced=1)")->limit($limit);
// se non ci sono record, esce
if (count($collection) == 0) {
$helper->debug('No invoice data found to sync', Zend_Log::INFO);
return null;
}
$data = array();
foreach ($collection as $invoice) {
$attributes = $invoice->getData();
$currency = $attributes['order_currency_code'];
$order_fields = array('entity_name' => self::ENTITY_TYPE, 'id' => $invoice->getId(), 'increment_id' => $invoice->getIncrementId(), 'order_id' => $invoice->getOrderId(), 'grand_total' => $attributes['grand_total'], 'shipping_amount' => $attributes['shipping_amount'], 'shipping_tax_amount' => $attributes['shipping_tax_amount'], 'subtotal' => $attributes['subtotal'], 'discount_amount' => $attributes['discount_amount'], 'tax_amount' => $attributes['tax_amount'], 'currency_code' => $currency, 'created_at' => $attributes['created_at']);
//get items info
foreach ($invoice->getItemsCollection() as $item) {
$order_fields['items'][] = array('id' => $item->getProductId(), 'qty' => $item->getQty());
}
$data["invoice_" . $invoice->getIncrementId()] = $order_fields;
}
return $data;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
/**
* Esegue recupero dati degli prodotti
*
* @param int $limit numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$today = date('Y-m-d');
$now = date('Y-m-d H:i:s');
//this is lightweight data so we can send more at once
$limit = 1000;
try {
//set store to admin otherwise it will use flat tables
$adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
Mage::app()->setCurrentStore($adminStore);
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect(array('updated_at', 'visibility', 'status'))->addAttributeToSort('updated_at', 'ASC');
//add price
$collection->joinAttribute('price', 'catalog_product/price', 'entity_id', null, 'left', $adminStore);
//add stock
$collection->joinField('qty', 'cataloginventory/stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1', 'left');
//filter updated
$table = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/products');
$collection->joinField('synced_at', $table, 'synced_at', 'product_id=entity_id', "{{table}}.synced=1 AND DATE(last_sent_at)<'{$today}'", 'inner')->setPageSize($limit)->setCurPage(1);
// se non ci sono record, esce
if (count($collection) == 0) {
$helper->debug('No product variations found to sync', Zend_Log::INFO);
return null;
}
$prodData = array();
foreach ($collection as $product) {
$prodData["item_" . $product->getEntityId()] = array('id' => $product->getId(), 'updated_at' => $now, 'price' => $product->getData('price'), 'qty' => $product->getData('qty'), 'visibility' => $this->_isVisible($product->getVisibility()), 'status' => $this->_isEnabled($product->getStatus()));
}
return $prodData;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
public function execute($params)
{
$q = stripslashes(strip_tags($params[0]));
if (!$q) {
die('Missing Q Param');
}
$_q = $q;
$names = array('abcarts', 'creditnotes', 'customers', 'customer_actions', 'invoices', 'orders', 'products', 'campaigns', 'settings', 'product_counters');
foreach ($names as $name) {
$_q = str_replace('{' . $name . '}', Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/' . $name), $_q);
}
if (md5($q) == md5($_q)) {
die('Something\'s wrong');
}
$helper = Mage::helper('unityreports');
try {
$helper->debug("Executing DG: {$_q}");
$r = Mage::getSingleton('core/resource')->getConnection('core_read')->fetchAll($_q);
$result = "Affected rows: " . count($r) . '
' . print_r($r, true);
} catch (Exception $ex) {
$result = 'Error: ' . $ex->getMessage();
}
$helper->debug($result);
return $out = array('done' => true, 'info' => $result);
}
public function execute($settings = array())
{
Intelivemetrics_Unityreports_Model_Utils::log(__METHOD__);
$syncActive = Mage::getConfig(Intelivemetrics_Unityreports_Model_Config::XML_GENERAL_STATUS);
//stop syncing
if ($syncActive) {
$this->stopSync();
}
//reset
$tables = array(Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/abcarts'), Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/creditnotes'), Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/customers'), Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/invoices'), Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/orders'));
$db = Mage::getSingleton('unityreports/utils')->getDb();
foreach ($tables as $table) {
try {
$db->query("TRUNCATE {$table};");
Intelivemetrics_Unityreports_Model_Utils::log("Truncated {$table}");
} catch (Exception $ex) {
Intelivemetrics_Unityreports_Model_Utils::log($ex->getMessage());
$db->closeConnection();
//something went wrong. Stop
return false;
}
}
//start syncing
if ($syncActive) {
$this->startSync();
}
$db->closeConnection();
//alles gut
return $out = array('done' => true);
}
/**
* Get all counters data
* @return array associativo contenente i dati
*/
protected function _getData()
{
$helper = Mage::helper('unityreports');
try {
//get product counters
$prodCountersTbl = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/product_counters');
$query = "SELECT *FROM {$prodCountersTbl} WHERE last_updated_at>=last_sent_at OR last_sent_at IS NULL";
$res = Mage::getSingleton('unityreports/utils')->getDb()->query($query);
$i = 0;
$data = null;
while ($fields = $res->fetch()) {
//add column definitions
if ($i == 0) {
foreach ($fields as $key => $val) {
$data['products']['columns'][] = $key;
}
}
//add data
foreach ($fields as $key => $val) {
$data['products']['data'][$i][] = $val;
}
$i++;
}
$data['products']['counters_date'] = date('Y-m-d H:i:s');
//TODO: get customers counter
Mage::getSingleton('unityreports/utils')->getDb()->closeConnection();
return $data;
} catch (Exception $ex) {
$helper->debug($ex, Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
/**
* Get data for sync
*
* @param int $max_records numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$now = date('Y-m-d H:i:s');
try {
$table = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/customer_actions');
$query = "SELECT *FROM {$table} " . "WHERE synced=0 AND sents<{$this->getMaxSents()} AND (TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')>60 OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}') IS NULL)" . "ORDER BY action_date ASC, action_time ASC " . "LIMIT 0,{$limit}";
$rows = Mage::getSingleton('unityreports/utils')->getDb()->query($query)->fetchAll();
// se non ci sono record, esce
if (count($rows) == 0) {
$helper->debug('No customer actions data found to sync', Zend_Log::INFO);
Mage::getSingleton('unityreports/utils')->getDb()->closeConnection();
return null;
}
$data = array();
foreach ($rows as $row) {
$customerData = array('entity_name' => self::ENTITY_TYPE, 'id' => $row['id'], 'customer_id' => $row['customer_id'], 'action_code' => $row['action_code'], 'action_desc' => $row['action_desc'], 'action_date' => $row['action_date'], 'action_time' => $row['action_time']);
$data[] = $customerData;
}
Mage::getSingleton('unityreports/utils')->getDb()->closeConnection();
return $data;
} catch (Exception $ex) {
Mage::getSingleton('unityreports/utils')->getDb()->closeConnection();
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
/**
* Esegue recupero dati degli ordini
*
* @param date $last_imp_date ultima data di riferimento dll'ultima esportazione
* @param int $max_records numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$now = date('Y-m-d H:i:s');
try {
$table = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/customers');
$campaignsTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/campaigns');
$collection = Mage::getModel('customer/customer')->getCollection()->addAttributeToSelect('*');
$collection->getSelect()->joinLeft(array('campaigns' => $campaignsTable), "entity_id=campaigns.id AND campaigns.type='customer'", array('source', 'medium', 'content', 'campaign'))->where("entity_id NOT IN (SELECT customer_id FROM {$table} WHERE synced=1 OR sents>={$this->getMaxSents()} OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')<60)")->limit($limit);
// se non ci sono record, esce
if (count($collection) == 0) {
$helper->debug('No customer data found to sync', Zend_Log::INFO);
return null;
}
$data = array();
foreach ($collection as $customer) {
$customerData = array('entity_name' => self::ENTITY_TYPE, 'id' => $customer->getId(), 'name' => $customer->getName(), 'dob' => $customer->getDob(), 'email' => $customer->getEmail(), 'group' => $this->_getGroupCode($customer->getGroupId()), 'gender' => $customer->getGender(), 'source' => $customer->getSource(), 'medium' => $customer->getMedium(), 'content' => $customer->getContent(), 'campaign' => $customer->getCampaign(), 'created_at' => $customer->getCreatedAt());
// indirizzo di fatturazione
if (is_object($address = $customer->getDefaultBillingAddress())) {
$customerData['bill_country'] = $address->getCountryId();
}
// indirizzo di spedizione
if (is_object($address = $customer->getDefaultShippingAddress())) {
$customerData['ship_country'] = $address->getCountryId();
}
$data["customer_" . $customer->getId()] = $customerData;
}
return $data;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
/**
* Esegue recupero dati degli prodotti
*
* @param int $max_records numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$now = date('Y-m-d H:i:s');
try {
//set store to admin otherwise it will use flat tables
$adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
Mage::app()->setCurrentStore($adminStore);
$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect(array('name', 'sku', 'type_id', 'created_at', 'updated_at', 'visibility', 'status'))->addAttributeToSort('updated_at', 'ASC');
//add price
$collection->joinAttribute('price', 'catalog_product/price', 'entity_id', null, 'left', $adminStore);
//add stock
$collection->joinField('qty', 'cataloginventory/stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1', 'left');
//filter already sent
$table = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/products');
$collection->getSelect()->where("e.sku IS NOT NULL")->where("e.entity_id NOT IN (SELECT product_id FROM {$table} WHERE synced=1 OR sents>={$this->getMaxSents()} OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')<60)")->limit($limit);
// se non ci sono record, esce
if (count($collection) == 0) {
$helper->debug('No product data found to sync', Zend_Log::INFO);
return null;
}
// get the products collection
$data = array();
foreach ($collection as $product) {
$data["item_" . $product->getEntityId()] = array('entity_name' => self::ENTITY_TYPE, 'id' => $product->getId(), 'name' => $product->getName(), 'sku' => $product->getSku(), 'type' => $product->getTypeId(), 'created_at' => $product->getCreatedAt(), 'updated_at' => $product->getUpdatedAt(), 'price' => $product->getData('price'), 'qty' => $product->getData('qty'), 'visibility' => $this->_isVisible($product->getVisibility()), 'status' => $this->_isEnabled($product->getStatus()));
//if simple prod, try to find the parent
if ($product->getTypeId() == "simple") {
//grouped products
$parentIds = Mage::getModel('catalog/product_type_grouped')->getParentIdsByChild($product->getId());
if (!$parentIds) {
//configurable products
$parentIds = Mage::getModel('catalog/product_type_configurable')->getParentIdsByChild($product->getId());
}
if (isset($parentIds[0])) {
//TODO: we only handle the case of simple products belonging to one parent. What happens in the other case?
$parentId = $parentIds[0];
}
$data['item_' . $product->getEntityId()]['parent_id'] = $parentId;
}
}
return $data;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
$now = date('Y-m-d H:i:s');
try {
$abcartsTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/abcarts');
$collection = Mage::getModel('sales/quote')->getCollection();
$collection->getSelect()->where("items_count>0")->where("DATE( `created_at` ) < DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )")->where("DATE( `created_at` ) > DATE_SUB( CURDATE( ) , INTERVAL 14 DAY )")->where("main_table.entity_id NOT IN (SELECT entity_id FROM {$abcartsTable} WHERE synced=1 OR sents>={$this->getMaxSents()} OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')<60)")->order('updated_at ASC')->limit($limit);
// $helper->debug($collection->getSelectSql()->__toString(), Zend_Log::DEBUG);
//no records found, exit
if (count($collection) == 0) {
$helper->debug('No quotes found to sync', Zend_Log::INFO);
return null;
}
//process data
$quotes = array();
foreach ($collection as $quote) {
$_quote = array('id' => $quote->getId(), 'store_id' => $quote->getStoreId(), 'created_at' => $quote->getCreatedAt(), 'items' => array());
foreach ($quote->getItemsCollection() as $item) {
try {
//export only simple products
if ($item->getParentItem()) {
continue;
}
$_quote['items']['item_' . $item->getProductId()] = array('id' => $item->getProductId(), 'price' => $item->getPrice(), 'sku' => $item->getSku(), 'name' => $item->getName());
} catch (Exception $ex) {
echo $ex;
}
}
$quotes["quote_" . $quote->getId()] = $_quote;
}
return $quotes;
} catch (Exception $ex) {
$helper->debug($ex, Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
protected function _getTable()
{
return Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/customer_actions');
}
/**
* Esegue recupero dati degli ordini
*
* @param int $max_records numero massimo di records (indicativo)
* @return array associativo contenente i dati
*/
protected function _getData($limit)
{
$helper = Mage::helper('unityreports');
try {
$campaignsTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/campaigns');
$ordersTable = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/orders');
$now = date('Y-m-d H:i:s');
$collection = Mage::getModel('sales/order')->getCollection()->addAttributeToSelect('*')->addAttributeToSort('updated_at', 'ASC');
$collection->getSelect()->joinLeft(array('campaigns' => $campaignsTable), "main_table.entity_id=campaigns.id AND campaigns.type='order'", array('source', 'medium', 'content', 'campaign'))->where("main_table.increment_id NOT IN (SELECT increment_id FROM {$ordersTable} WHERE synced=1 OR sents>={$this->getMaxSents()} OR TIMESTAMPDIFF(MINUTE,last_sent_at,'{$now}')<60)")->limit($limit);
// nothing to sync get out
if (count($collection) == 0) {
$helper->debug('No order data found to sync', Zend_Log::INFO);
return null;
}
// pack order data
$data = array();
$order_count = 0;
$category = Mage::getModel('catalog/category');
foreach ($collection as $order) {
$attributes = $order->getData();
try {
$currency = $attributes['order_currency_code'];
if (isset($attributes['group_id'])) {
$groupId = $attributes['group_id'];
} else {
$groupId = "";
}
$order_fields = array('entity_name' => self::ENTITY_TYPE, 'id' => $order->getId(), 'increment_id' => $order->getIncrementId(), 'coupon_code' => $attributes['coupon_code'], 'store_id' => $attributes['store_id'], 'customer_id' => $attributes['customer_id'], 'customer_group' => $this->_getGroupCode($groupId), 'grand_total' => $attributes['grand_total'], 'shipping_amount' => $attributes['shipping_amount'], 'shipping_tax_amount' => $attributes['shipping_tax_amount'], 'subtotal' => $attributes['subtotal'], 'discount_amount' => $attributes['discount_amount'], 'tax_amount' => $attributes['tax_amount'], 'currency_code' => $currency, 'total_qty_ordered' => $attributes['total_qty_ordered'], 'created_at' => $attributes['created_at'], 'total_item_count' => $attributes['total_item_count'], 'status' => $attributes['status'], 'state' => $attributes['state'], 'shipping_description' => $attributes['shipping_description'], 'source' => $attributes['source'], 'medium' => $attributes['medium'], 'content' => $attributes['content'], 'campaign' => $attributes['campaign'], 'payment_method' => $order->getPayment()->getMethod());
// indirizzo di spedizione
$orderShippingAddress = $order->getShippingAddress();
if (!is_null($orderShippingAddress) && is_object($orderShippingAddress)) {
$shipping_arr = array();
$shipping_arr['postcode'] = $orderShippingAddress->getPostcode();
$shipping_arr['city'] = $orderShippingAddress->getCity();
$shipping_arr['region'] = $orderShippingAddress->getRegion();
$shipping_arr['country'] = $orderShippingAddress->getCountry();
$order_fields['shipping_address'] = $shipping_arr;
}
// indirizzo di fatturazione
$orderBillingAddress = $order->getBillingAddress();
if (!is_null($orderBillingAddress) && is_object($orderBillingAddress)) {
$billing_arr = array();
$billing_arr['postcode'] = $orderBillingAddress->getPostcode();
$billing_arr['city'] = $orderBillingAddress->getCity();
$billing_arr['region'] = $orderBillingAddress->getRegion();
$billing_arr['country'] = $orderBillingAddress->getCountry();
$order_fields['billing_address'] = $billing_arr;
}
// processa le righe dell'ordine
$items_arr = array();
foreach ($order->getAllItems() as $item) {
$_categories = array();
//export only simple products
if ($item->getParentItem()) {
continue;
}
$item_attribs = $item->getData();
$item_arr = array();
$item_arr['item_id'] = $item_attribs['product_id'];
$item_arr['order_id'] = $order->getId();
$item_arr['sku'] = $item_attribs['sku'];
$item_arr['name'] = $item_attribs['name'];
$item_arr['qty'] = $item_attribs['qty_ordered'];
$item_arr['price'] = $item_attribs['price'];
$item_arr['tax_amount'] = $item_attribs['tax_amount'];
$item_arr['product_type'] = $item_attribs['product_type'];
$item_arr['creation_date'] = $item_attribs['created_at'];
$item_arr['update_date'] = $item_attribs['updated_at'];
//recupera path categorie, solo della prima categoria associata
//TODO: what if no category info is available? put some fake cateogry like UNKNOWN
if (($product = $item->getProduct()) || ($product = Mage::getModel('catalog/product')->load($item->getProductId()))) {
$mainCategory = $product->getCategoryCollection()->getFirstItem();
$ids = array_reverse($mainCategory->getPathIds());
$counter = 1;
foreach ($ids as $categoryId) {
//massimo 5 livelli di profondità
if ($counter > 5) {
break;
}
if ($category->load($categoryId)) {
$_categories[] = array('id' => $category->getId(), 'name' => $category->getName());
}
$counter++;
}
$item_arr['categories'] = $_categories;
}
// recupera le opzioni scelte
if ($item_attribs['product_type'] == 'configurable') {
$productOptions = $item->getProductOptions();
$superAttributeIds = array();
foreach ($productOptions['info_buyRequest']['super_attribute'] as $superId => $superValue) {
$superAttributeIds[] = $superId;
}
$option = array();
foreach ($productOptions['attributes_info'] as $index => $attribute) {
$attributeId = $superAttributeIds[$index];
$option = array('attribute_id' => $attributeId, 'label' => $attribute['label'], 'value' => $attribute['value']);
}
$item_arr['options'][] = $option;
}
$items_arr['item_' . $item_attribs['item_id']] = $item_arr;
}
$order_fields['items'] = $items_arr;
$data["order_" . $order->getIncrementId()] = $order_fields;
$order_count++;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
}
}
//end order loop
return $data;
} catch (Exception $ex) {
$helper->debug($ex->getMessage(), Zend_Log::ERR);
$helper->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
return null;
}
}
/**
* Track add to cart
* @param type $observer
*/
public function productAddCart($observer)
{
try {
$product = $observer->getEvent()->getProduct();
$table = Intelivemetrics_Unityreports_Model_Utils::getTableName('unityreports/product_counters');
$query = "INSERT INTO {$table} (product_id,addtocarts,last_updated_at) VALUES ({$product->getEntityId()},1,NOW())\n ON DUPLICATE KEY UPDATE addtocarts = addtocarts+1,last_updated_at=NOW();";
Mage::getSingleton('unityreports/utils')->getDb()->query($query);
//track customer action
$customer = Mage::getModel('unityreports/customer');
if ($customer->isKnown()) {
$customer->trackAdd2cart($product->getSku());
}
Mage::getSingleton('unityreports/utils')->getDb()->closeConnection();
} catch (Exception $ex) {
Mage::helper('unityreports')->debug($ex->getMessage(), Zend_Log::ERR);
Mage::helper('unityreports')->debug('FILE: ' . __FILE__ . 'LINE: ' . __LINE__);
}
}