function get_products_names($queryString, $is_competition = FALSE)
{
$this->_response_type('json');
$this->data = array();
if (strlen($queryString) > 0) {
$store_id_list = getStoreIdList($this->store_id);
if ($is_competition) {
$this->db->join($this->_table_brand_product . ' bp', 'bp.product_id=p.id')->where_in('bp.store_id', $store_id_list);
} else {
$this->db->where_in('p.store_id', $store_id_list);
}
$result = $this->db->select('p.id, p.title')->like('p.title', $queryString)->where('p.is_tracked', 1)->group_by('p.upc_code')->limit(10)->get($this->_table_products . ' p');
$num_rows = $result->num_rows();
if ($num_rows > 0) {
$result = $result->result_array();
for ($i = 0; $i < $num_rows; $i++) {
$result[$i]['title'] = html_entity_decode($result[$i]['title']);
}
$this->data = $result;
}
}
}
function requestInfoWhereValues(array $request_info, &$marketFilter = NULL, &$order_by = NULL)
{
$ci =& get_instance();
$where = array();
//
// Product WHERE statement
//
if (!empty($request_info['product_ids'])) {
$request_info['product_ids'] = array_filter($request_info['product_ids']);
$strProduct = implode(',', $request_info['product_ids']);
$where['products'] = "IN (" . $ci->db->escape_str($strProduct) . ")";
// TODO: fix orderby
//$order_by = " ORDER BY FIELD(products.id, ".trim($strProduct, ',').")";
}
//
// Marketplaces WHERE statement
//
$marketFilter = array();
if (isset($request_info['api_type']) && is_array($request_info['api_type'])) {
foreach ($request_info['api_type'] as $val) {
if ($val !== 'all') {
$marketFilter[] = strtolower($val);
}
}
if (isset($request_info['api_type'][0]) && $request_info['api_type'][0] != 'all') {
$strMarkets = implode(",", array_map(array($ci->db, 'escape'), $marketFilter));
$where['marketplaces'] = "IN (" . $strMarkets . ")";
}
}
//
// Merchants WHERE statement
//
if (isset($request_info['merchants'][0]) && $request_info['merchants'][0] != 'all') {
$strMerchants = implode(",", array_map(array($ci->db, 'escape'), $request_info['merchants']));
$where['merchants'] = "IN (" . $strMerchants . ")";
}
//
// Store WHERE statement
//
if (!empty($request_info['competitor_store_id']) && $request_info['competitor_store_id'][0] !== false) {
$where['store'] = 'IN (' . getStoreIdList($request_info['competitor_store_id'], TRUE) . ')';
} elseif (!empty($request_info['store_id'])) {
$where['store'] = 'IN (' . getStoreIdList($request_info['store_id'], TRUE) . ')';
}
return $where;
}
public function isOwnerProductsPricing($id, $store_id)
{
$res = $this->db->select('count(*) as count')->join($this->_table_products . ' p', 'p.id=pp.product_id')->where('pp.pricing_id', (int) $id)->where_in('p.store_id', getStoreIdList($store_id))->get($this->_table_products_pricing . ' pp')->row();
return !empty($res->count);
}
function get_columns_by_store($msid)
{
$rowData = $this->db->select('c.id, c.display_name, c.db_name')->distinct()->join($this->_table_brand_columns . ' bc', 'bc.column_id=c.id', 'left')->where_in('bc.user_store_id', getStoreIdList($msid))->order_by('bc.sort', 'ASC')->get($this->_table_columns . ' c')->result();
return $rowData;
}
/**
*
* function getCountByMerchant
*
*
*/
function getCountByMerchant($store_id, $marketplace = null, $startTime = null, $endTime = null)
{
$this->db->select('COUNT(cpl.id) as total_products, mpn.id, mpn.merchant_name, mpn.marketplace, mpn.original_name')->join($this->_table_products . ' p', 'p.upc_code=cpl.upc', 'left')->join($this->_table_crowl_merchant_name . ' mpn', 'mpn.id=cpl.merchant_name_id', 'left')->where_in('p.store_id', getStoreIdList($store_id));
$where = array();
if ($marketplace) {
$where['mpn.marketplace'] = $marketplace;
}
$start = $startTime ? strtotime($startTime) : strtotime('-24 hours');
$end = $endTime ? strtotime($endTime) : time();
$where['cpl.last_date >='] = $start;
$where['cpl.last_date <='] = $end;
$result = $this->db->where($where)->group_by('mpn.merchant_name')->get($this->_table_crowl_product_list . ' cpl')->result_array();
return $result;
}
/**
* Retrieve a list of crowl_merchant_name_new ids with "violated" flag by store
*
* @param int $storeId
* @param String $dateStart { default : null }
* @param String $dateEnd { default : null }
* @param bool $violatedFlag { default : true }
* @return array
*/
protected function getPriceViolatorsByStoreId($storeId, $dateStart = null, $dateEnd = null, $violatedFlag = true)
{
$sql = "SELECT DISTINCT\n\t\t\t\t\tcp.merchant_name_id\n\t\t\t\tFROM\n\t\t\t\t\t{$this->_table_crowl_product_list} cp\n\t\t\t\tLEFT JOIN\n\t\t\t\t\t{$this->_table_products} p ON p.upc_code=cp.upc\n\t\t\t\tWHERE\n\t\t\t\t\tp.store_id IN (" . getStoreIdList($storeId, TRUE) . ")";
$variables = array();
if (isset($violatedFlag)) {
$sql .= " AND cp.violated=?";
$variables[] = $violatedFlag ? 1 : 0;
}
if (isset($dateStart)) {
$sign_and_date = $this->signAndDate($dateStart, 'Y-m-d H:i:s', '>=');
$sql .= ' AND cp.last_date' . $sign_and_date['sign'] . '?';
$variables[] = strtotime($sign_and_date['date']);
}
if (isset($dateEnd)) {
$sign_and_date = $this->signAndDate($dateEnd, 'Y-m-d H:i:s', '<=');
$sql .= ' AND cp.last_date' . $sign_and_date['sign'] . '?';
$variables[] = strtotime($sign_and_date['date']);
}
$result = $this->db->query($sql, $variables);
//echo $this->db->last_query();
$ret = array();
if ($result->num_rows()) {
foreach ($result->result() as $row) {
$ret[] = $row->merchant_name_id;
}
}
return $ret;
}
/**
*
* @author unknown
* @param string $marketplace
* @param int $merchantId
* @param int $reportId
*/
function report_merchant($marketplace, $merchantId, $reportId = false)
{
$this->data->retailer = $this->marketplace_m->is_retailer($marketplace);
$this->data->marketplace = $marketplace;
$merchant_details = $this->merchant_products_m->getMerchantDetailsById($merchantId);
$this->data->merchant = $this->data->original_name = trim($merchant_details['original_name']);
$this->data->show_notify_resource = $merchantId;
$this->data->report_where = array('report_type' => $this->data->report_type, 'is_retailer' => $this->data->retailer, 'report_function' => 'report_merchant', 'marketplace' => $marketplace, 'merchant_id' => $merchantId, 'time_frame' => '24');
if ($reportId) {
$this->data->report_id = base64_decode(urldecode($reportId));
}
$this->data->report_name = $this->data->retailer ? marketplace_display_name($marketplace) . ' Product Listing' : marketplace_display_name($marketplace) . ' Seller: ' . marketplace_display_name($this->data->merchant) . ' Product Listing';
$this->data->file_name = str_replace(' ', '_', $this->data->report_name);
//view data
$this->data->time_frame = '24';
$lastCrawl = $this->crawl_data_m->last_crawl($marketplace);
//var_dump($lastCrawl); exit();
$this->crawl_range = $this->crawl_data_m->last_crawl_range();
$this->data->crawl_range = $this->crawl_range;
if ($this->config->item('environment') == 'local') {
$crawl_range_from_int = strtotime('2015-08-01 00:00:00');
$crawl_range_to_int = strtotime('2015-08-02 00:00:00');
} else {
$crawl_range_from_int = strtotime($this->crawl_range['from']);
$crawl_range_to_int = strtotime($this->crawl_range['to']);
}
//TODO
//this should occur in the reports model...
//now setup the product data
$productQuery = "SELECT\n\t\t\t\tptn.um as hashKey,\n\t\t\t\tcmn.seller_id,\n\t\t\t\tp.upc_code,\n\t\t\t\tp.sku,\n\t\t\t\tp.title,\n\t\t\t\tp.retail_price,\n\t\t\t\tp.wholesale_price,\n\t\t\t\tcmn.merchant_name,\n\t\t\t\tcmn.original_name,\n\t\t\t\tptn.dt\n\t\t\tFROM\n\t\t\t\t{$this->_table_products_trends} ptn\n\t\t\tINNER JOIN {$this->_table_crowl_merchant_name} cmn ON cmn.id = ptn.mid\n\t\t\tLEFT JOIN {$this->_table_products} p ON p.id = ptn.pid\n\t\t\tWHERE p.store_id IN (" . getStoreIdList($this->store_id, TRUE) . ")\n\t\t\t\tAND ptn.ar = '{$marketplace}'\n\t\t\t\tAND cmn.id = {$merchantId}\n\t\t\t\tAND ptn.dt >= {$crawl_range_from_int}\n\t\t\t\tAND ptn.dt <= {$crawl_range_to_int}\n\t\t\tGROUP BY hashKey";
//echo "<PRE>";
//echo $productQuery;
//exit;
$products = $this->db->query($productQuery)->result();
$finalProductsArray = array();
$gData = array('violoation' => 0, 'non_violoation' => 0);
$hashes = array();
if (empty($products)) {
} else {
foreach ($products as $product) {
$hashes[] = $product->hashKey;
$products_assoc[$product->hashKey] = $product;
}
$priceTrends = $this->ProductsTrends->get_all_by_hashkeys_and_date_range($hashes, strtotime($lastCrawl->start_datetime), strtotime($lastCrawl->end_datetime));
foreach ($priceTrends->result_object() as $priceTrend) {
if ((double) $priceTrend->mpo >= (double) $priceTrend->ap) {
$gData['non_violoation']++;
} else {
$gData['violoation']++;
}
$violationArray = array('productId' => (int) $priceTrend->pid, 'upc_code' => (string) $priceTrend->upc, 'retail' => $priceTrend->rp ? (double) $priceTrend->rp : $products_assoc[$priceTrend->um]->retail_price, 'wholesale' => $priceTrend->wp ? (double) $priceTrend->wp : $products_assoc[$priceTrend->um]->wholesale_price, 'price' => (double) $priceTrend->mpo, 'map' => (double) $priceTrend->ap, 'title' => (string) $priceTrend->t, 'title2' => (string) $products_assoc[$priceTrend->um]->title, 'marketplace' => (string) $priceTrend->ar, 'url' => (string) $priceTrend->l, 'timestamp' => (int) $priceTrend->dt, 'hash_key' => (string) $priceTrend->um, 'hashKey' => (string) $priceTrend->um, 'merchant_id' => (string) $products_assoc[$priceTrend->um]->seller_id, 'original_name' => (string) $products_assoc[$priceTrend->um]->original_name, 'sku' => (string) $products_assoc[$priceTrend->um]->sku, 'date' => (string) date('m/d/Y G:i:s', (int) $priceTrend->dt), 'shot' => (string) $priceTrend->ss);
$finalProductsArray[$priceTrend->pid] = $violationArray;
}
}
$googleDataArray = array();
$googleDataArray[0] = array('State', 'Count');
$googleDataArray[] = array('Non Violation', $gData['non_violoation']);
$googleDataArray[] = array('Violation', $gData['violoation']);
$gData['googleData'] = $googleDataArray;
$gData['type'] = 'pie';
$this->data->Data = $finalProductsArray;
$this->data->gData = $gData;
// violator notification data
$this->data->merchant_id = $merchantId;
$this->data->show_notify_resource = $merchantId;
$this->data->violator_notification = $this->store_m->get_violator_notification_by_seller($merchantId, $this->store_id);
$this->data->smtp = $this->store_m->get_store_smtp_by_store($this->store_id);
$this->data->default_email_from = $this->Store->get_smtp_email($this->data->smtp);
//array_push($this->javascript_files, 'views/merchant_report.php');
}
function get_all_lookup_items()
{
$this->_response_type('json');
$request_url = $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
$parse_url_array = parse_url($request_url);
parse_str($parse_url_array['query']);
if (empty($marketplace_id)) {
$this->data = array('data' => array('title' => 'Select a retailer'));
return;
}
$kywd = null;
if (isset($keyword)) {
$kywd = $keyword;
$kywd = trim($kywd);
if (strlen($kywd) > 0) {
$kywd = str_replace(array('-', ' ', ',', '_'), ' ', $kywd);
}
}
$query = "SELECT * FROM {$this->_table_products} p \n";
if ($kywd) {
$query .= ", MATCH(title,sku,search) AGAINST('{$kywd}') as score\n";
}
$query .= "LEFT JOIN {$this->_table_products_lookup} pl ON p.id=pl.product_id AND pl.marketplace_id={$marketplace_id}\n";
$query .= "WHERE p.store_id IN (" . getStoreIdList($this->store_id, TRUE) . ") \n";
$query .= "AND p.deleted_at IS NULL \n";
if (empty($is_archived)) {
$query .= "AND is_archived = 0 \n";
}
if ($kywd) {
$query .= "AND MATCH(title,sku,search) AGAINST('{$kywd}')\n";
$query .= "ORDER BY score desc";
} else {
$query .= "ORDER BY title";
}
$result = $this->db->query($query)->result_array();
//echo $this->db->last_query(); exit;
$data = array();
for ($i = 0, $n = count($result); $i < $n; $i++) {
$row = array();
$row['check'] = 0;
$row['upc_code'] = $result[$i]['upc_code'];
$row['sku'] = $result[$i]['sku'];
$row['title'] = html_entity_decode($result[$i]['title']);
$row['is_archived'] = $result[$i]['is_archived'];
$row['id'] = $result[$i]['id'];
$row['url'] = !empty($result[$i]['url']) ? $result[$i]['url'] : '';
$data[$i] = $row;
}
$this->data = array('data' => $data);
}
