/** * 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__); } }