private function _getDailyPriceAverage($tableName, $merchantJoin, $report_where, $i, $where, $productUPC, $lookup_store_id) { $averageQuery = "SELECT d.*, p.title, p.id\n\t\t\tFROM {$tableName} d\n\t\t\tLEFT JOIN products p ON d.upc = p.upc_code\n\t\t\t{$merchantJoin}\n\t\t\tWHERE d.date BETWEEN '" . date("Y-m-d", $report_where['date_from']) . "' AND '" . date("Y-m-d", $report_where['date_to']) . "'\n\t\t\tAND p.id=" . $report_where['product_ids'][$i] . $where; $average = $this->db->query($averageQuery)->result(); $range['start'] = $report_where['date_from']; $range['end'] = $report_where['date_to']; if (count($average) > 0) { foreach ($average as $row) { $wholeSale = getPricePoint($productUPC, $lookup_store_id, 'wholesale_price', '', $range); $retail = getPricePoint($productUPC, $lookup_store_id, 'retail_price', '', $range); $map = getPricePoint($productUPC, $lookup_store_id, 'price_floor', '', $range); $idxMarketPlace = strtolower($row->marketplace); $this->finalData[$report_where['product_ids'][$i]][$idxMarketPlace][] = array('marketplace' => $row->marketplace, 'upc' => $row->upc, 'price' => floatval($row->price_total / $row->seller_total), 'wholesale' => $wholeSale, 'retail' => $retail, 'title' => $row->title, 'map' => $map, 'dt' => strtotime($row->date), 'timestamp' => $row->date, 'date' => (string) $row->date, 'prod_id' => $report_where['product_ids'][$i]); } } }
function searchProductPricingViolations($request_info = array(), $skip_product_id = false) { //create range key from cron log $from = $request_info['date_from']; $to = $request_info['date_to']; $crons = getLast24HoursCronIds($from, $to); $last24HoursCronsData = getLast24HoursCronLog($crons); foreach ($last24HoursCronsData as $l24hCD) { if (strtotime($l24hCD->start_datetime) < $from) { $from = strtotime($l24hCD->start_datetime); } if ($l24hCD->end_datetime != '0000-00-00 00:00:00' && strtotime($l24hCD->end_datetime) > $to) { $to = strtotime($l24hCD->end_datetime); } } // TODO: competitor_map if (!empty($request_info['competitor_map'])) { // get the intersection of the two sets of merchants $sql = "SELECT cmn.id\n\t\t\t\tFROM " . $this->_table_products . " p\n\t\t\t\tLEFT JOIN " . $this->_table_crowl_product_list . " cpl ON cpl.upc=p.upc_code\n\t\t\t\tLEFT JOIN " . $this->_table_crowl_merchant_name . " cmn ON cmn.id=cpl.merchant_name_id\n\t\t\t\tWHERE p.id = ?"; $merchant_query = $sql . " AND cmn.id IN ({$sql})"; $merchants_intersection = array(); $lookup_merchants = array(); foreach ($request_info['competitor_map'] as $prodId => $owner_product) { $result = $this->db->query($merchant_query, array($prodId, $owner_product['id']))->result_array(); for ($i = 0, $n = count($result); $i < $n; $i++) { $merchant_id = $result[$i]['id']; $merchants_intersection[$merchant_id] = true; } } // get the intersection of merchants filter and the set we just made if (isset($request_info['merchants'][0]) and $request_info['merchants'][0] !== 'all') { foreach ($request_info['merchants'] as $merchant_id) { if (isset($merchants_intersection[$merchant_id])) { $lookup_merchants[$merchant_id] = true; } } } else { $lookup_merchants = $merchants_intersection; } $request_info['merchants'] = array_keys($lookup_merchants); } $where = '1=1'; $MarketFilter = array(); $orderBy = ''; $orderByTmp = 'products.id'; $whereVals = requestInfoWhereValues($request_info, $MarketFilter, $orderByTmp); if (!$skip_product_id and isset($whereVals['products'])) { $request_info['product_ids'] = array_filter($request_info['product_ids']); $where .= ' AND products.id ' . $whereVals['products']; $orderBy = " ORDER BY {$orderByTmp} "; } if (isset($whereVals['marketplaces'])) { $where .= ' AND crowl_merchant_name_new.marketplace ' . $whereVals['marketplaces']; $MarketFilter = $whereVals['marketplaces']; } if (isset($whereVals['merchants'])) { $where .= ' AND crowl_merchant_name_new.id ' . $whereVals['merchants']; } if (isset($whereVals['store'])) { $where .= ' AND products.store_id ' . $whereVals['store']; } //TODO: change hashkey to sellerId instead of merchant_name $productQuery = "SELECT\n\t\t\t\tconcat(crowl_merchant_name_new.seller_id,'#',crowl_product_list_new.upc) as hashKey,\n\t\t\t\tcrowl_merchant_name_new.seller_id,\n\t\t\t\tproducts.upc_code,\n\t\t\t\tproducts.id,\n\t\t\t\tproducts.store_id,\n\t\t\t\tcrowl_merchant_name_new.merchant_name,\n\t\t\t\tcrowl_merchant_name_new.original_name\n\t\t\tFROM crowl_product_list_new\n\t\t\tINNER JOIN crowl_merchant_name_new ON crowl_merchant_name_new.id = crowl_product_list_new.merchant_name_id\n\t\t\tLEFT JOIN products ON products.upc_code = crowl_product_list_new.upc\n\t\t\tWHERE {$where}\n\t\t\tGROUP BY hashKey\n\t\t\t{$orderBy}"; //products.title, //products.price_floor, //products.retail_price, //products.wholesale_price, //echo "<pre>$productQuery<br>\n";exit; $products = $this->db->query($productQuery)->result(); $finalProductsArray = array(); foreach ($products as $product) { //TODO: store these two data inside the product_trends table instead $retailPricePoint = getPricePoint($product->upc_code, $product->store_id, 'retail_price'); $wholesalePricePoint = getPricePoint($product->upc_code, $product->store_id, 'wholesale_price'); // get the "nosql" data $priceTrends = $this->ProductsTrends->get_by_hashkey_and_date_range_and_marketplace($product->hashKey, $from, $to, $MarketFilter); foreach ($priceTrends->result_object() as $priceTrend) { //safety hack to not show incorrect violations if ((double) $priceTrend->mpo >= (double) $priceTrend->ap) { continue; } $violationTrendArray = array('productId' => (int) $product->id, 'upc_code' => (string) $product->upc_code, 'retail' => (double) $retailPricePoint, 'wholesale' => (double) $wholesalePricePoint, 'price' => (double) $priceTrend->mpo, 'map' => (double) $priceTrend->ap, 'title' => (string) $priceTrend->t, 'marketplace' => (string) $priceTrend->ar, 'url' => (string) $priceTrend->l, 'timestamp' => (int) $priceTrend->dt, 'hash_key' => (string) $product->hashKey, 'merchant_id' => (string) $product->seller_id, 'date' => (string) date('m/d/Y G:i:s', (int) $priceTrend->dt), 'shot' => (string) $priceTrend->ss); $finalProductsArray[$product->id][] = $violationTrendArray; } } return $finalProductsArray; }
private function _retailer_lookup($api, $search_url, $upcs, $floor_price, $cron_log_id, $store_id) { //$search_url = "http://www.amazon.com"; //$api = 'amazon'; //echo "\n-------------------------------------------------------------------------------------\n"; //echo "_retailer_lookup(".print_r($upcs,true);//.", //floor_price: ".print_r($floor_price,true).", //cron_log_id: $cron_log_id, //store_id: $store_id)"; //exit; foreach ($upcs as $key => $upc) { /*only one index, always*/ } if (empty($upc)) { log_message('error', "Missing UPC, {$api} " . print_r($upcs, true)); return; } $retailer_data = $this->_spider_search($upc, $api, $search_url); //echo "retailer_data: \n"; //print_r($retailer_data); //exit; if (empty($retailer_data)) { $this->updateUPCFlag($upc, $api, '0'); log_message('info', "upc {$upc} not found in {$api}"); return; } $price_floor = getPricePoint($upc, $store_id, 'price_floor', time()); // MAP $this->myDB = new mydb(); // only one screen_shot of each product page $uniquArr = array(); foreach ($retailer_data as $item) { log_message('info', 'item: ' . print_r($item, true)); if (empty($item)) { log_message('info', "Missing result, {$api} " . print_r($upcs, true)); continue; } $this->stats[$api]['data_found']++; $crawled_product = array(); $sku = $item['product.sku']; if (empty($sku)) { $sku = "No product SKU found."; } $crawled_product['ap'] = $price_floor; $crawled_product['t'] = $item['product.name']; $crawled_product['l'] = $item['product.url']; $crawled_product['ar'] = $api; $crawled_product['il'] = !empty($item['product.image_url']) ? $item['product.image_url'] : ''; // price offered $crawled_product['mpo'] = $this->parseDollarAmount($item['product.price_listed']); if (empty($crawled_product['mpo'])) { log_message('error', 'Failed parse ' . print_r($item, true)); continue; } if (!empty($item['product.shipping'])) { $crawled_product['msp'] = $this->parseDollarAmount($item['product.shipping']); } if (!empty($item['seller.logo'])) { $crawled_product['mil'] = $item['seller.logo']; } // TODO: make the seller_id or merchant_id into mandatory data. Merchant name changes all the time. // TODO: de-duplicate merchant table $seller_name = !empty($item['seller.name']) ? trim($item['seller.name']) : "No name found."; $sellerUrl = trim($item['seller.aboutus']); $seller_id = !empty($item['seller.seller_id']) ? $item['seller.seller_id'] : ''; $real_name = $seller_name; $merchant = $this->getMerchantNameForKey($seller_name, $upc, $api, $real_name, $seller_id, $sellerUrl); $merchant_name = $merchant['merchant_name']; if (empty($merchant_name) or strlen($merchant_name) < 2) { log_message('error', "Missing merchant_name " . print_r($item, true)); continue; } $crowlMerchantNameID = $merchant['id']; // TODO: analyze this. what's it used for? why is it updated for every item? $stQuery = "UPDATE cron_log set google_count=(google_count+1), last_UPC='{$upc}' WHERE id= '{$cron_log_id}' "; $this->myDB->simpleQuery($stQuery); // TODO: change the hashkey to use merchant_id#upc#storeId $hashKey = $merchant_name . '#' . $upc; $crawled_product['um'] = $hashKey; $crawled_product['dt'] = time(); $crawled_product['pid'] = $this->Products->get_product_id_from_upc($upc, $store_id); $crawled_product['upc'] = $upc; $crawled_product['mid'] = $crowlMerchantNameID; $crawled_product['mu'] = $sellerUrl; $crawled_product['rp'] = getPricePoint($upc, $store_id, 'retail_price'); $crawled_product['wp'] = getPricePoint($upc, $store_id, 'wholesale_price'); $this->stats[$api]['price_found']++; $this->insertUPCMerchant($merchant_name, $upc, $api, $crawled_product['mpo']); // Should we ignore violations that are less than a dollar wrong? $dataVio = $this->Products->get_products_by_floor($upc, $crawled_product['mpo'], $store_id); //echo "dataVio: Products->get_products_by_floor(upc_code: $upc, violatedPrice: {$crawled_product['mpo']}, store_id:$store_id)\n"; //print_r($dataVio); //exit; // Screen Shots $ss = ''; $llr = $this->ProductsTrends->get_latest_by_hashkey($hashKey)->result_object(); $lastCrawlPrice = (double) 0; if (!empty($llr->um)) { $lastCrawlPrice = isset($llr->mpo) ? (double) $llr->mpo : (double) 0; // Merchant Price Offered $ss = $llr->ss; } $violationFlag = false; if ($dataVio) { $violationFlag = true; $this->updateViolationSummary($dataVio); // take screenshot only if unique today, price has changed, and product is in violation $new_ss = date('Ymd', $crawled_product['dt']) . '/' . md5($crawled_product['l']) . '.png'; if ($ss != $new_ss && $lastCrawlPrice != $crawled_product['mpo']) { $ss = $new_ss; if (!isset($uniquArr[$ss])) { $uniquArr[$ss] = true; $this->addScreenShot($crawled_product['l'], $ss, false, $crawled_product['mpo']); } } } $crawled_product['ss'] = $ss; // store the nosql data $this->ProductsTrends->insertData($crawled_product, $api); // update price violators for all products, if MPO data exists $this->Violator->updatePriceViolator($crowlMerchantNameID, $upc, $violationFlag, $crawled_product['dt']); //echo "updatePriceViolator\(crowlMerchantNameID:$crowlMerchantNameID, upc_code:$upc, ".(int)$violationFlag.", dt:{$crawled_product['dt']} \)\n"; //exit; // do not set active=1 if existing row has active=0, the customer does not want to crawl them $where = "crowl_merchant_name_id = {$crowlMerchantNameID} and store_id = {$store_id}"; $rrr = $this->myDB->getByTableName($this->_table_violator_notifications, $where); if (empty($rrr[0]) or $rrr[0]['active'] == 1) { // assure violation notification record exists and is active $arrViolation = array('store_id' => $store_id, 'crowl_merchant_name_id' => $crowlMerchantNameID, 'active' => 1); $this->myDB->replace($this->_table_violator_notifications, $arrViolation, $where); } //echo "\ncrawled_products: \n"; //print_r($crawled_product); //exit; } //end iterator on returned retailer_data $this->updateUPCFlag($upc, $api, '1'); $this->myDB->close(); }
/** * Retrieve products by upc from products table where floor price is greater than specified value * * @param String $upc * @param float $min_floor * * @return array */ public function get_products_by_floor($upc, $min_floor, $store_id) { $ret = false; $map = getPricePoint($upc, $store_id, 'price_floor', time()); echo "MAP: {$map}\n"; if ($map > $min_floor) { $result = $this->db->select('p.id, p.store_id')->where('p.upc_code', $upc)->where_in('p.store_id', getStoreIdList($store_id))->get($this->_table_products . ' p'); //echo "MAP result: "; //print_r($result->result_array()); if ($result->num_rows() == 1) { $ret = $result->result_array(); } } return $ret; }
function loadPricePoints(array &$products, $ts) { if (!empty($products)) { for ($i = 0, $n = count($products); $i < $n; $i++) { $map = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'price_floor', $ts); $retail = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'retail_price', $ts); $wholesale = getPricePoint($products[$i]['upc_code'], $products[$i]['store_id'], 'wholesale_price', $ts); $product[$i]['price_floor'] = $map; $product[$i]['retail_price'] = $retail; $product[$i]['wholesale_price'] = $wholesale; } } }
/** * This handles the export functionality just for a store's product catalog. * * @author unknown * @param string $format */ public function export_catalog($format) { $catalog_columns = $this->Store->get_columns_by_store($this->store_id); $column_order = $column_names = array(); for ($i = 0, $n = count($catalog_columns); $i < $n; $i++) { $column_order[] = $catalog_columns[$i]->db_name; $column_names[] = $catalog_columns[$i]->display_name; } $csv_data = array($column_names); // get all non-archived products $products = $this->Product->get_products_for_store($this->store_id); //$products = $this->Product->getByStore($this->store_id); for ($i = 0, $n = count($products); $i < $n; $i++) { $row = array(); foreach ($column_order as $column) { switch ($column) { case 'wholesale_price': $price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column); $price = empty($price) ? '0.00' : number_format($price, 2, '.', ''); $row[] = $price; break; case 'retail_price': $price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column); $price = empty($price) ? '0.00' : number_format($price, 2, '.', ''); $row[] = $price; break; case 'price_floor': $price = getPricePoint($products[$i]['upc_code'], $this->store_id, $column); $price = empty($price) ? '0.00' : number_format($price, 2, '.', ''); $row[] = $price; break; default: $row[] = isset($products[$i][$column]) ? $products[$i][$column] : ''; } } $csv_data[] = $row; } $name = 'catalog'; /* if ($this->store_id !== 'all') $name = str_replace(' ', '_', $this->store_data['store_name']); */ // get current store being viewed $store = $this->Store->get_store_by_id_array($this->store_id); $name = str_replace(' ', '_', $store['store_name']); $name .= '_' . date('Y-m-d'); if ($format == 'excel') { createCSV($csv_data, $name); } else { createPDF($csv_data, $name); } exit; }