/** * Specify the options to load * * @copyright * @author RolandD * @todo Set to use chosen language * @see * @access protected * @param * @return array an array of options * @since 4.0 */ protected function getOptions() { $this->options = array(); if (class_exists('com_virtuemart')) { $conf = JFactory::getConfig(); $lang = strtolower(str_replace('-', '_', $conf->get('language'))); $helper = new Com_VirtueMart(); $this->options = $helper->getCategoryTree($lang); } return $this->options; }
/** * Specify the options to load * * @copyright * @author RolandD * @todo Set to use chosen language * @see * @access protected * @param * @return array an array of options * @since 4.0 */ protected function getOptions() { $this->options = array(); if (class_exists('com_virtuemart')) { $jinput = JFactory::getApplication()->input; $template = $jinput->get('template', null, null); $conf = JFactory::getConfig(); $lang = strtolower($template->get('language', 'general', str_replace('-', '_', $conf->get('language')))); $helper = new Com_VirtueMart(); $this->options = $helper->getCategoryTree($lang); } return $this->options; }
/** * Calculation rules export * * Exports calculation rules details data to either csv, xml or HTML format * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.0 */ public function getStart() { // Get some basic data $db = JFactory::getDbo(); $csvidb = new CsviDb(); $jinput = JFactory::getApplication()->input; $csvilog = $jinput->get('csvilog', null, null); $template = $jinput->get('template', null, null); $exportclass = $jinput->get('export.class', null, null); $export_fields = $jinput->get('export.fields', array(), 'array'); $helper = new Com_VirtueMart(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { if ($field->process) { switch ($field->field_name) { case 'virtuemart_calc_id': case 'created_by': case 'created_on': case 'locked_by': case 'locked_on': case 'modified_by': case 'modified_on': case 'ordering': case 'published': case 'shared': case 'virtuemart_vendor_id': $userfields[] = '#__virtuemart_calcs.' . $field->field_name; break; case 'category_path': case 'shopper_group_name': case 'country_name': case 'country_2_code': case 'country_3_code': case 'state_name': case 'state_2_code': case 'state_3_code': $userfields[] = '#__virtuemart_calcs.virtuemart_calc_id'; break; default: $userfields[] = $db->quoteName($field->field_name); break; } } } // Build the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__virtuemart_calcs'); $query->leftJoin('#__virtuemart_calc_categories ON #__virtuemart_calc_categories.virtuemart_calc_id = #__virtuemart_calcs.virtuemart_calc_id'); $query->leftJoin('#__virtuemart_calc_countries ON #__virtuemart_calc_countries.virtuemart_calc_id = #__virtuemart_calcs.virtuemart_calc_id'); $query->leftJoin('#__virtuemart_calc_shoppergroups ON #__virtuemart_calc_shoppergroups.virtuemart_calc_id = #__virtuemart_calcs.virtuemart_calc_id'); $query->leftJoin('#__virtuemart_calc_states ON #__virtuemart_calc_states.virtuemart_calc_id = #__virtuemart_calcs.virtuemart_calc_id'); $query->leftJoin('#__virtuemart_currencies ON #__virtuemart_currencies.virtuemart_currency_id = #__virtuemart_calcs.calc_currency'); // Check if there are any selectors $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state != '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = '#__virtuemart_calcs.published = ' . $publish_state; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Ignore fields $ignore = array('category_path', 'shopper_group_name', 'country_name', 'country_2_code', 'country_3_code', 'state_name', 'state_2_code', 'state_3_code'); // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $ignore); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort', $ignore); if (!empty($orderby)) { $query->order($orderby); } // Add a limit if user wants us to $limits = $this->getExportLimit(); // Execute the query $csvidb->setQuery($query, $limits['offset'], $limits['limit']); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // There are no records, write SQL query to log if (!is_null($csvidb->getErrorMsg())) { $this->addExportContent(JText::sprintf('COM_CSVI_ERROR_RETRIEVING_DATA', $csvidb->getErrorMsg())); $this->writeOutput(); $csvilog->AddStats('incorrect', $csvidb->getErrorMsg()); } else { $logcount = $csvidb->getNumRows(); $jinput->set('logcount', $logcount); if ($logcount > 0) { $linenumber = 1; while ($record = $csvidb->getRow()) { $csvilog->setLinenumber($linenumber++); if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeStart()); } foreach ($export_fields as $column_id => $field) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { case 'category_path': // Get all the category IDs $query = $db->getQuery(true); $query->select('virtuemart_category_id'); $query->from('#__virtuemart_calc_categories'); $query->where('virtuemart_calc_id = ' . $record->virtuemart_calc_id); $db->setQuery($query); $catids = $db->loadColumn(); if (!empty($catids)) { $categories = array(); foreach ($catids as $catid) { $categories[] = $helper->createCategoryPathById($catid); } $fieldvalue = implode('|', $categories); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'shopper_group_name': $query = $db->getQuery(true); $query->select('virtuemart_shoppergroup_id'); $query->from('#__virtuemart_calc_shoppergroups'); $query->where('virtuemart_calc_id = ' . $record->virtuemart_calc_id); $db->setQuery($query); $groupids = $db->loadResultArray(); if (!empty($groupids)) { $query = $db->getQuery(true); $query->select('shopper_group_name'); $query->from('#__virtuemart_shoppergroups'); $query->where('virtuemart_shoppergroup_id IN (' . implode(',', $groupids) . ')'); $db->setQuery($query); $names = $db->loadResultArray(); $fieldvalue = implode('|', $names); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'country_name': case 'country_2_code': case 'country_3_code': $query = $db->getQuery(true); $query->select('virtuemart_country_id'); $query->from('#__virtuemart_calc_countries'); $query->where('virtuemart_calc_id = ' . $record->virtuemart_calc_id); $db->setQuery($query); $groupids = $db->loadResultArray(); if (!empty($groupids)) { $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_countries'); $query->where('virtuemart_country_id IN (' . implode(',', $groupids) . ')'); $db->setQuery($query); $names = $db->loadResultArray(); $fieldvalue = implode('|', $names); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'state_name': case 'state_2_code': case 'state_3_code': $query = $db->getQuery(true); $query->select('virtuemart_state_id'); $query->from('#__virtuemart_calc_states'); $query->where('virtuemart_calc_id = ' . $record->virtuemart_calc_id); $db->setQuery($query); $groupids = $db->loadResultArray(); if (!empty($groupids)) { $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_states'); $query->where('virtuemart_state_id IN (' . implode(',', $groupids) . ')'); $db->setQuery($query); $names = $db->loadResultArray(); $fieldvalue = implode('|', $names); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; default: // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; } } if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeEnd()); } // Output the contents $this->writeOutput(); } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }
/** * Product export * * Exports product data to either csv, xml or HTML format * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.0 */ public function getStart() { // Get some basic data $jinput = JFactory::getApplication()->input; $db = JFactory::getDbo(); $csvidb = new CsviDb(); $jinput = JFactory::getApplication()->input; $csvilog = $jinput->get('csvilog', null, null); $template = $jinput->get('template', null, null); $exportclass = $jinput->get('export.class', null, null); $export_fields = $jinput->get('export.fields', array(), 'array'); $this->_domainname = CsviHelper::getDomainName(); $helper = new Com_VirtueMart(); $sef = new CsviSef(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { if ($field->process) { switch ($field->field_name) { case 'created_on': case 'modified_on': case 'locked_on': case 'created_by': case 'modified_by': case 'locked_by': case 'virtuemart_product_id': case 'virtuemart_vendor_id': case 'hits': case 'metaauthor': case 'metarobot': case 'published': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName($field->field_name); break; case 'category_id': case 'category_path': $userfields[] = $db->quoteName('#__virtuemart_product_categories') . '.' . $db->quoteName('virtuemart_category_id'); $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('virtuemart_product_id'); break; case 'product_name': case 'product_s_desc': case 'product_desc': case 'metadesc': case 'metakey': case 'slug': case 'customtitle': case 'custom_value': case 'custom_price': case 'custom_param': case 'custom_title': case 'file_url': case 'file_url_thumb': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('virtuemart_product_id'); break; case 'product_parent_sku': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_parent_id'); break; case 'related_products': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('virtuemart_product_id') . ' AS main_product_id'; break; case 'product_box': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_packaging'); break; case 'product_price': case 'price_with_tax': $userfields[] = $db->quoteName('#__virtuemart_product_prices') . '.' . $db->quoteName('product_price'); $userfields[] = $db->quoteName('#__virtuemart_currencies') . '.' . $db->quoteName('currency_code_3'); break; case 'product_url': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('virtuemart_product_id'); $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_url'); $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_parent_id'); break; case 'price_with_discount': $userfields[] = $db->quoteName('#__virtuemart_product_prices') . '.' . $db->quoteName('product_price'); $userfields[] = $db->quoteName('#__virtuemart_currencies') . '.' . $db->quoteName('currency_code_3'); //$userfields[] = $db->quoteName('#__virtuemart_calcs').'.'.$db->quoteName('calc_value'); //$userfields[] = $db->quoteName('#__virtuemart_calcs').'.'.$db->quoteName('calc_value_mathop'); //$userfields[] = $db->quoteName('#__virtuemart_calcs').'.'.$db->quoteName('calc_kind'); break; case 'product_currency': $userfields[] = $db->quoteName('#__virtuemart_currencies') . '.' . $db->quoteName('currency_code_3'); break; case 'custom_shipping': $userfields[] = $db->quoteName('#__virtuemart_product_prices') . '.' . $db->quoteName('product_price'); $userfields[] = '1 AS tax_rate'; break; case 'max_order_level': case 'min_order_level': $userfields[] = $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_params'); break; // Man made fields, do not export them // Man made fields, do not export them case 'custom': case 'picture_url': case 'basepricewithtax': case 'discountedpricewithouttax': case 'pricebeforetax': case 'salesprice': case 'taxamount': case 'discountamount': case 'pricewithouttax': case 'manufacturer_name': break; default: $userfields[] = $db->quoteName($field->field_name); break; } } } /** Export SQL Query * Get all products - including items * as well as products without a price */ $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__virtuemart_products'); $query->leftJoin('#__virtuemart_product_prices ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_product_prices.virtuemart_product_id'); $query->leftJoin('#__virtuemart_product_manufacturers ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_product_manufacturers.virtuemart_product_id'); $query->leftJoin('#__virtuemart_shoppergroups ON #__virtuemart_product_prices.virtuemart_shoppergroup_id = #__virtuemart_shoppergroups.virtuemart_shoppergroup_id'); $query->leftJoin('#__virtuemart_manufacturers ON #__virtuemart_product_manufacturers.virtuemart_manufacturer_id = #__virtuemart_manufacturers.virtuemart_manufacturer_id'); $query->leftJoin('#__virtuemart_product_categories ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_product_categories.virtuemart_product_id'); $query->leftJoin('#__virtuemart_categories ON #__virtuemart_product_categories.virtuemart_category_id = #__virtuemart_categories.virtuemart_category_id'); $query->leftJoin('#__virtuemart_currencies ON #__virtuemart_currencies.virtuemart_currency_id = #__virtuemart_product_prices.product_currency'); // Check if there are any selectors $selectors = array(); // Filter by product category /** * We are doing a selection on categories, need to redo the query to make sure child products get included * 1. Search all product ID's for that particular category * 2. Search for all child product ID's * 3. Load all products with these ids */ $productcategories = $template->get('product_categories', 'product', false); if ($productcategories && $productcategories[0] != '') { $product_ids = array(); // If selected get products of all subcategories as well if ($template->get('incl_subcategory', 'product', false)) { $q_subcat_ids = "SELECT category_child_id\r\n\t\t\t\t\t\t\t\t\tFROM #__virtuemart_category_categories\r\n\t\t\t\t\t\t\t\t\tWHERE category_parent_id IN ('" . implode("','", $productcategories) . "')"; $db->setQuery($q_subcat_ids); $subcat_ids = $db->loadResultArray(); $productcategories = array_merge($productcategories, $subcat_ids); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); } // Get only the parent products and products without children if ($template->get('parent_only', 'product', 0, 'bool')) { // Get all product IDs in the selected categories $q_product_ids = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\tLEFT JOIN #__virtuemart_product_categories x\r\n\t\t\t\t\t\t\tON p.virtuemart_product_id = x.virtuemart_product_id\r\n\t\t\t\t\t\t\tWHERE x.virtuemart_category_id IN ('" . implode("','", $productcategories) . "')\r\n\t\t\t\t\t\t\tAND p.product_parent_id = 0"; $db->setQuery($q_product_ids); $product_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); } else { if ($template->get('child_only', 'product', 0, 'bool')) { // Load all non child IDs $q_child = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\t\t\tLEFT JOIN #__virtuemart_product_categories x\r\n\t\t\t\t\t\t\t\t\tON p.virtuemart_product_id = x.virtuemart_product_id\r\n\t\t\t\t\t\t\t\t\tWHERE x.virtuemart_category_id IN ('" . implode("','", $productcategories) . "')"; $db->setQuery($q_child); $allproduct_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Get all child product IDs in the selected categories $q_child = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\tWHERE p.product_parent_id IN ('" . implode("','", $allproduct_ids) . "')"; $db->setQuery($q_child); $child_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Get all parent product IDs in the selected categories $q_child = "SELECT p.product_parent_id\r\n\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\tWHERE p.virtuemart_product_id IN ('" . implode("','", $child_ids) . "')"; $db->setQuery($q_child); $parent_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Combine all the IDs $product_ids = array_merge($child_ids, array_diff($allproduct_ids, $parent_ids)); } else { // Get all product IDs $q_product_ids = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\tLEFT JOIN #__virtuemart_product_categories x\r\n\t\t\t\t\t\t\tON p.virtuemart_product_id = x.virtuemart_product_id\r\n\t\t\t\t\t\t\tWHERE x.virtuemart_category_id IN ('" . implode("','", $productcategories) . "')"; $db->setQuery($q_product_ids); $product_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Get all child product IDs if ($product_ids) { $q_childproduct_ids = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\t\tWHERE p.product_parent_id IN ('" . implode("','", $product_ids) . "')"; $db->setQuery($q_childproduct_ids); $childproduct_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Now we have all the product IDs $product_ids = array_merge($product_ids, $childproduct_ids); } } } // Check if the user want child products if (!empty($product_ids)) { $selectors[] = '#__virtuemart_products.virtuemart_product_id IN (\'' . implode("','", $product_ids) . '\')'; } } else { // Filter by published category state $category_publish = $template->get('publish_state_categories', 'product'); // Filter on parent products and products without children if ($template->get('parent_only', 'product', 0, 'bool')) { $selectors[] = '#__virtuemart_products.product_parent_id = 0'; if (!empty($category_publish)) { $selectors[] = '#__virtuemart_categories.published = ' . $category_publish; } } else { if ($template->get('child_only', 'product', 0, 'bool')) { // Load all non child IDs $q_nonchild = 'SELECT #__virtuemart_products.virtuemart_product_id FROM #__virtuemart_products '; $state = $category_publish == '1' ? '0' : '1'; if (!empty($category_publish)) { $q_nonchild .= 'LEFT JOIN #__virtuemart_product_categories ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_product_categories.virtuemart_product_id LEFT JOIN #__virtuemart_categories ON #__virtuemart_product_categories.virtuemart_category_id = #__virtuemart_categories.virtuemart_category_id WHERE #__virtuemart_categories.published = ' . $state; } $db->setQuery($q_nonchild); $nonchild_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Get the child IDs from the filtered category if (!empty($category_publish)) { $q_nonchild = 'SELECT #__virtuemart_products.virtuemart_product_id FROM #__virtuemart_products '; $q_nonchild .= 'LEFT JOIN #__virtuemart_product_categories ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_product_categories.virtuemart_product_id LEFT JOIN #__virtuemart_categories ON #__virtuemart_product_categories.virtuemart_category_id = #__virtuemart_categories.virtuemart_category_id WHERE #__virtuemart_products.product_parent_id IN (\'' . implode("','", $nonchild_ids) . '\')'; $q_nonchild .= ' GROUP BY virtuemart_product_id'; $db->setQuery($q_nonchild); $child_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); if (is_array($child_ids)) { $nonchild_ids = array_merge($nonchild_ids, $child_ids); } } $selectors[] = '#__virtuemart_products.virtuemart_product_id NOT IN (\'' . implode("','", $nonchild_ids) . '\')'; } else { if (!empty($category_publish)) { // Get all product IDs $q_product_ids = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\t\tLEFT JOIN #__virtuemart_product_categories x\r\n\t\t\t\t\t\t\t\tON p.virtuemart_product_id = x.virtuemart_product_id\r\n\t\t\t\t\t\t\t\tLEFT JOIN #__virtuemart_categories c\r\n\t\t\t\t\t\t\t\tON x.virtuemart_category_id = c.virtuemart_category_id\r\n\t\t\t\t\t\t\t\tWHERE c.category_publish = " . $db->Quote($category_publish); $db->setQuery($q_product_ids); $product_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Get all child product IDs if ($product_ids) { $q_childproduct_ids = "SELECT p.virtuemart_product_id\r\n\t\t\t\t\t\t\t\t\tFROM #__virtuemart_products p\r\n\t\t\t\t\t\t\t\t\tWHERE p.product_parent_id IN ('" . implode("','", $product_ids) . "')"; $db->setQuery($q_childproduct_ids); $childproduct_ids = $db->loadResultArray(); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // Now we have all the product IDs $product_ids = array_merge($product_ids, $childproduct_ids); } // Check if the user want child products if (!empty($product_ids)) { $selectors[] = '#__virtuemart_products.virtuemart_product_id IN (\'' . implode("','", $product_ids) . '\')'; } } } } } // Filter on featured products $featured = $template->get('featured', 'product', ''); if ($featured) { $selectors[] = "#__virtuemart_products.product_special = 1"; } // Filter by published state $product_publish = $template->get('publish_state', 'general'); if ($product_publish !== '' && ($product_publish == 1 || $product_publish == 0)) { $selectors[] = '#__virtuemart_products.published = ' . $db->Quote($product_publish); } // Filter by product SKU $productskufilter = $template->get('productskufilter', 'product'); if ($productskufilter) { $productskufilter .= ','; if (strpos($productskufilter, ',')) { $skus = explode(',', $productskufilter); $wildcard = ''; $normal = array(); foreach ($skus as $sku) { if (!empty($sku)) { if (strpos($sku, '%')) { $wildcard .= "#__virtuemart_products.product_sku LIKE " . $db->Quote($sku) . " OR "; } else { $normal[] = $db->Quote($sku); } } } if (substr($wildcard, -3) == 'OR ') { $wildcard = substr($wildcard, 0, -4); } if (!empty($wildcard) && !empty($normal)) { $selectors[] = "(" . $wildcard . " OR #__virtuemart_products.product_sku IN (" . implode(',', $normal) . "))"; } else { if (!empty($wildcard)) { $selectors[] = "(" . $wildcard . ")"; } else { if (!empty($normal)) { $selectors[] = "(#__virtuemart_products.product_sku IN (" . implode(',', $normal) . "))"; } } } } } // Filter on price from $priceoperator = $template->get('priceoperator', 'product', 'gt'); $pricefrom = $template->get('pricefrom', 'product', 0, 'float'); $priceto = $template->get('priceto', 'product', 0, 'float'); if (!empty($pricefrom)) { switch ($priceoperator) { case 'gt': $selectors[] = "ROUND(#__virtuemart_product_prices.product_price, " . $template->get('export_price_format_decimal', 'general', 2, 'int') . ") > " . $pricefrom; break; case 'eq': $selectors[] = "ROUND(#__virtuemart_product_prices.product_price, " . $template->get('export_price_format_decimal', 'general', 2, 'int') . ") = " . $pricefrom; break; case 'lt': $selectors[] = "ROUND(#__virtuemart_product_prices.product_price, " . $template->get('export_price_format_decimal', 'general', 2, 'int') . ") < " . $pricefrom; break; case 'bt': $selectors[] = "ROUND(#__virtuemart_product_prices.product_price, " . $template->get('export_price_format_decimal', 'general', 2, 'int') . ") BETWEEN " . $pricefrom . " AND " . $priceto; break; } } // Filter by stocklevel start $stocklevelstart = $template->get('stocklevelstart', 'product', 0, 'int'); if ($stocklevelstart) { $selectors[] = '#__virtuemart_products.product_in_stock >= ' . $stocklevelstart; } // Filter by stocklevel end $stocklevelend = $template->get('stocklevelend', 'product', 0, 'int'); if ($stocklevelend) { $selectors[] = '#__virtuemart_products.product_in_stock <= ' . $stocklevelend; } // Filter by shopper group id $shopper_group = $template->get('shopper_groups', 'product', array()); if ($shopper_group && $shopper_group[0] != 'none') { $selectors[] = "#__virtuemart_product_prices.virtuemart_shoppergroup_id IN ('" . implode("','", $shopper_group) . "')"; } // Filter by manufacturer $manufacturer = $template->get('manufacturers', 'product', array()); if ($manufacturer && $manufacturer[0] != 'none') { $selectors[] = "#__virtuemart_manufacturers.virtuemart_manufacturer_id IN ('" . implode("','", $manufacturer) . "')"; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Ingore fields $ignore = array('metadesc', 'metakey', 'product_name', 'product_s_desc', 'product_desc', 'slug', 'customtitle', 'category_path', 'manufacturer_name', 'category_id', 'picture_url', 'product_box', 'product_parent_sku', 'related_products', 'custom_shipping', 'basepricewithtax', 'discountedpricewithouttax', 'pricebeforetax', 'salesprice', 'taxamount', 'discountamount', 'pricewithouttax', 'custom_title', 'custom_value', 'custom_price', 'custom_param', 'file_url', 'file_url_thumb', 'min_order_level', 'max_order_level'); // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $ignore); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort', $ignore); if (!empty($orderby)) { $query->order($orderby); } // Add export limits $limits = $this->getExportLimit(); // Execute the query $csvidb->setQuery($query, $limits['offset'], $limits['limit']); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // There are no records, write SQL query to log if (!is_null($csvidb->getErrorMsg())) { $this->addExportContent(JText::sprintf('COM_CSVI_ERROR_RETRIEVING_DATA', $csvidb->getErrorMsg())); $this->writeOutput(); $csvilog->AddStats('incorrect', $csvidb->getErrorMsg()); } else { $logcount = $csvidb->getNumRows(); JRequest::setVar('logcount', $logcount); if ($logcount > 0) { $linenumber = 1; while ($record = $csvidb->getRow()) { $csvilog->setLinenumber($linenumber++); // Start the XML/HTML output if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeStart()); } // Load JoomFish translation if needed $joomfish = array(); if ($template->get('use_joomfish', 'product', false)) { $jfdb = JFactory::getDBO(); // Get the product details $q = "SELECT value, reference_field\r\n\t\t\t\t\t\t\tFROM #__jf_content\r\n\t\t\t\t\t\t\tWHERE reference_table = 'vm_product'\r\n\t\t\t\t\t\t\tAND language_id = " . $template->get('joomfish_language', 'product') . "\r\n\t\t\t\t\t\t\tAND reference_id = " . $record->main_product_id; $jfdb->setQuery($q); $joomfish = $jfdb->loadObjectList('reference_field'); } // Reset the prices $this->_prices = array(); // Process all the export fields foreach ($export_fields as $column_id => $field) { if ($field->process) { $fieldname = $field->field_name; // Add the replacement & JoomFish if (isset($record->{$fieldname})) { // Get the JoomFish data if (array_key_exists($fieldname, $joomfish)) { $fieldvalue = $joomfish[$fieldname]->value; } else { $fieldvalue = $record->{$fieldname}; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); } else { $fieldvalue = ''; } switch ($fieldname) { case 'category_id': $category_path = trim($helper->createCategoryPath($record->virtuemart_product_id, true)); if (strlen(trim($category_path)) == 0) { $category_path = $field->default_value; } $category_path = CsviHelper::replaceValue($field->replace, $category_path); $this->addExportField($field->combine, $category_path, $fieldname, $field->column_header, true); break; case 'category_path': $category_path = trim($helper->createCategoryPath($record->virtuemart_product_id)); if (strlen(trim($category_path)) == 0) { $category_path = $field->default_value; } $category_path = CsviHelper::replaceValue($field->replace, $category_path); $this->addExportField($field->combine, $category_path, $fieldname, $field->column_header, true); break; case 'product_name': case 'product_s_desc': case 'product_desc': case 'metadesc': case 'metakey': case 'slug': case 'customtitle': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_products_' . $template->get('language', 'general')); $query->where('virtuemart_product_id = ' . $record->virtuemart_product_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'picture_url': $query = $db->getQuery(true); $query->select('file_url'); $query->from('#__virtuemart_medias'); $query->leftJoin('#__virtuemart_product_medias ON #__virtuemart_product_medias.virtuemart_media_id = #__virtuemart_medias.virtuemart_media_id'); $query->where('virtuemart_product_id = ' . $record->virtuemart_product_id); $query->order('#__virtuemart_product_medias.ordering'); $db->setQuery($query, 0, 1); $fieldvalue = $db->loadResult(); // Check if there is already a product full image if (strlen(trim($fieldvalue)) > 0) { $picture_url = $this->_domainname . '/' . $fieldvalue; } else { $picture_url = $field->default_value; } $picture_url = CsviHelper::replaceValue($field->replace, $picture_url); $this->addExportField($field->combine, $picture_url, $fieldname, $field->column_header); break; case 'product_parent_sku': $query = $db->getQuery(true); $query->select('product_sku'); $query->from('#__virtuemart_products'); $query->where('virtuemart_product_id = ' . $record->product_parent_id); $db->setQuery($query); $product_parent_sku = $db->loadResult(); $product_parent_sku = CsviHelper::replaceValue($field->replace, $product_parent_sku); $this->addExportField($field->combine, $product_parent_sku, $fieldname, $field->column_header); break; case 'related_products': // Get the custom ID $related_records = array(); $query = $db->getQuery(true); $query->select($db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_sku')); $query->from($db->quoteName('#__virtuemart_product_customfields')); $query->leftJoin($db->quoteName('#__virtuemart_customs') . ' ON ' . $db->quoteName('#__virtuemart_customs') . '.' . $db->quoteName('virtuemart_custom_id') . ' = ' . $db->quoteName('#__virtuemart_product_customfields') . '.' . $db->quoteName('virtuemart_custom_id')); $query->leftJoin($db->quoteName('#__virtuemart_products') . ' ON ' . $db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('virtuemart_product_id') . ' = ' . $db->quoteName('#__virtuemart_product_customfields') . '.' . $db->quoteName('custom_value')); $query->where($db->quoteName('#__virtuemart_customs') . '.' . $db->quoteName('field_type') . ' = ' . $db->quote('R')); $query->where($db->quoteName('#__virtuemart_product_customfields') . '.' . $db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($record->virtuemart_product_id)); $query->group($db->quoteName('#__virtuemart_products') . '.' . $db->quoteName('product_sku')); $db->setQuery($query); $related_records = $db->loadResultArray(); if (is_array($related_records)) { $related_products = implode('|', $related_records); } else { $related_products = ''; } if (strlen(trim($related_products)) == 0) { $related_products = $field->default_value; } $related_products = CsviHelper::replaceValue($field->replace, $related_products); $this->addExportField($field->combine, $related_products, $fieldname, $field->column_header); break; case 'product_available_date': case 'created_on': case 'modified_on': case 'locked_on': $date = JFactory::getDate($record->{$fieldname}); $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $date->toUnix())); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'product_box': $product_box = $record->product_packaging >> 16 & 0xffff; $product_box = CsviHelper::replaceValue($field->replace, $product_box); $this->addExportField($field->combine, $product_box, $fieldname, $field->column_header); break; case 'product_packaging': $product_packaging = $record->product_packaging & 0xffff; $product_packaging = CsviHelper::replaceValue($field->replace, $product_packaging); $this->addExportField($field->combine, $product_packaging, $fieldname, $field->column_header); break; case 'product_price': $product_price = $this->_convertPrice($record->product_price, $record->currency_code_3); $product_price = number_format($product_price, $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); if (strlen(trim($product_price)) == 0) { $product_price = $field->default_value; } if ($template->get('add_currency_to_price', 'general')) { if ($template->get('targetcurrency', 'product') != '') { $product_price = $template->get('targetcurrency', 'product') . ' ' . $product_price; } else { $product_price = $record->currency_code_3 . ' ' . $product_price; } } $product_price = CsviHelper::replaceValue($field->replace, $product_price); $this->addExportField($field->combine, $product_price, $fieldname, $field->column_header); break; case 'product_override_price': $product_price = number_format($record->product_override_price, $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); if (strlen(trim($product_price)) == 0) { $product_price = $field->default_value; } if ($template->get('add_currency_to_price', 'general')) { if ($template->get('targetcurrency', 'product') != '') { $product_price = $template->get('targetcurrency', 'product') . ' ' . $product_price; } else { $product_price = $record->currency_code_3 . ' ' . $product_price; } } $product_price = CsviHelper::replaceValue($field->replace, $product_price); $this->addExportField($field->combine, $product_price, $fieldname, $field->column_header); break; case 'product_url': // Check if there is already a product URL if (is_null($record->product_url) || strlen(trim($record->product_url)) == 0) { // Get the category id // Check to see if we have a child product $category_id = $helper->getCategoryId($record->virtuemart_product_id); if ($category_id > 0) { // Let's create a SEF URL $_SERVER['QUERY_STRING'] = 'option=com_virtuemart&Itemid=' . $template->get('vm_itemid', 'product', 1, 'int') . '&view=productdetails&virtuemart_product_id=' . $record->virtuemart_product_id . '&virtuemart_category_id=' . $category_id; $product_url = $sef->getSiteRoute('index.php?' . $_SERVER['QUERY_STRING']); } else { $product_url = ""; } } else { $product_url = $record->product_url; } // Add the suffix if (!empty($product_url)) { $product_url .= $template->get('producturl_suffix', 'product'); } // Check for https, replace with http. https has unnecessary overhead if (substr($product_url, 0, 5) == 'https') { $product_url = 'http' . substr($product_url, 5); } $product_url = CsviHelper::replaceValue($field->replace, $product_url); $this->addExportField($field->combine, $product_url, $fieldname, $field->column_header, true); break; case 'price_with_tax': $prices = $this->_getProductPrice($record->virtuemart_product_id); $price_with_tax = number_format($prices['salesPrice'], $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); // Check if we have any content otherwise use the default value if (strlen(trim($price_with_tax)) == 0) { $price_with_tax = $field->default_value; } if ($template->get('add_currency_to_price', 'general')) { $price_with_tax = $record->product_currency . ' ' . $price_with_tax; } $price_with_tax = CsviHelper::replaceValue($field->replace, $price_with_tax); $this->addExportField($field->combine, $price_with_tax, $fieldname, $field->column_header); break; case 'basepricewithtax': case 'discountedpricewithouttax': case 'pricebeforetax': case 'salesprice': case 'taxamount': case 'discountamount': case 'pricewithouttax': $prices = $this->_getProductPrice($record->virtuemart_product_id); if (isset($prices[$fieldname])) { $price = number_format($prices[$fieldname], $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); } // Check if we have any content otherwise use the default value if (strlen(trim($price)) == 0) { $price = $field->default_value; } // Check if the currency needs to be added if ($template->get('add_currency_to_price', 'general')) { $price = $record->product_currency . ' ' . $price; } // Perform the replacement rules $price = CsviHelper::replaceValue($field->replace, $price); // Export the data $this->addExportField($field->combine, $price, $fieldname, $field->column_header); break; case 'product_currency': $fieldvalue = $record->currency_code_3; // Check if we have any content otherwise use the default value if ($template->get('targetcurrency', 'product') != '') { $fieldvalue = $template->get('targetcurrency', 'product'); } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'custom_shipping': // Get the prices $prices = $this->_getProductPrice($record->virtuemart_product_id); // Check the shipping cost if (isset($prices['salesprice'])) { $price_with_tax = number_format($prices['salesprice'], $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); $result = $helper->shippingCost($price_with_tax); if ($result) { $fieldvalue = $result; } } // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'manufacturer_name': $query = $db->getQuery(true); $query->select('mf_name'); $query->from('#__virtuemart_manufacturers_' . $template->get('language', 'general')); $query->leftJoin('#__virtuemart_product_manufacturers ON #__virtuemart_product_manufacturers.virtuemart_manufacturer_id = #__virtuemart_manufacturers_' . $template->get('language', 'general') . '.virtuemart_manufacturer_id'); $query->where('virtuemart_product_id = ' . $record->virtuemart_product_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'custom_title': // Get the custom title $query = $db->getQuery(true); $query->select($db->quoteName('custom_title')); $query->from($db->quoteName('#__virtuemart_customs') . ' AS c'); $query->leftJoin($db->quoteName('#__virtuemart_product_customfields') . ' AS f ON c.virtuemart_custom_id = f.virtuemart_custom_id'); $query->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($record->virtuemart_product_id)); // Check if we need to filter $title_filter = array(); $title_filter = $template->get('custom_title', 'product', array(), 'array'); if (!empty($title_filter) && $title_filter[0] != '') { $query->where($db->quoteName('f') . '.' . $db->quoteName('virtuemart_custom_id') . ' IN (' . implode(',', $title_filter) . ')'); } $query->order($db->quoteName('f') . '.' . $db->quoteName('virtuemart_custom_id')); $db->setQuery($query); $titles = $db->loadResultArray(); if (is_array($titles)) { $fieldvalue = CsviHelper::replaceValue($field->replace, implode('|', $titles)); // Check if we have any content otherwise use the default value } else { $fieldvalue = ''; } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'custom_value': case 'custom_price': case 'custom_param': if (!isset($this->_customfields[$record->virtuemart_product_id][$fieldname])) { $query = $db->getQuery(true); $query->select($db->quoteName($fieldname)); $query->from($db->quoteName('#__virtuemart_product_customfields')); $query->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($record->virtuemart_product_id)); // Check if we need to filter $title_filter = array(); $title_filter = $template->get('custom_title', 'product', array()); if (!empty($title_filter) && $title_filter[0] != '') { $query->where($db->quoteName('virtuemart_custom_id') . ' IN (' . implode(',', $title_filter) . ')'); } $query->order($db->quoteName('virtuemart_custom_id')); $db->setQuery($query); $customfields = $db->loadObjectList(); $csvilog->addDebug('COM_CSVI_CUSTOM_FIELD_QUERY', true); if (!empty($customfields)) { $values = array(); foreach ($customfields as $customfield) { $values[] = $customfield->{$fieldname}; } $this->_customfields[$record->virtuemart_product_id][$fieldname] = $values; $fieldvalue = implode('|', $this->_customfields[$record->virtuemart_product_id][$fieldname]); } else { $fieldvalue = ''; } } else { $fieldvalue = implode('|', $this->_customfields[$record->virtuemart_product_id][$fieldname]); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'file_url': case 'file_url_thumb': $query = $db->getQuery(true); $query->select($db->quoteName($fieldname)); $query->from($db->quoteName('#__virtuemart_medias') . ' AS m'); $query->leftJoin($db->quoteName('#__virtuemart_product_medias') . ' AS p ON m.virtuemart_media_id = p.virtuemart_media_id'); $query->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($record->virtuemart_product_id)); $query->where($db->quoteName('file_type') . ' = ' . $db->quote('product')); $query->order('p.ordering'); $db->setQuery($query); $titles = $db->loadResultArray(); if (is_array($titles)) { $fieldvalue = CsviHelper::replaceValue($field->replace, implode('|', $titles)); // Check if we have any content otherwise use the default value } else { $fieldvalue = ''; } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'min_order_level': case 'max_order_level': if (strpos($record->product_params, '|')) { $params = explode('|', $record->product_params); foreach ($params as $param) { if ($param) { list($param_name, $param_value) = explode('=', $param); if ($param_name == $fieldname) { $fieldvalue = str_replace('"', '', $param_value); } } } } else { $fieldvalue = ''; } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; default: // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; } } } if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeEnd()); } // Output the contents $this->writeOutput(); } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }
/** * Category details export * * Exports category details data to either csv, xml or HTML format * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.0 */ public function getStart() { // Get some basic data $db = JFactory::getDbo(); $csvidb = new CsviDb(); $jinput = JFactory::getApplication()->input; $csvilog = $jinput->get('csvilog', null, null); $template = $jinput->get('template', null, null); $exportclass = $jinput->get('export.class', null, null); $export_fields = $jinput->get('export.fields', array(), 'array'); $helper = new Com_VirtueMart(); // Get all categories $query = $db->getQuery(true); $query->select('LOWER(l.category_name) AS category_name, category_child_id AS cid, category_parent_id AS pid'); $query->from('#__virtuemart_categories c'); $query->leftJoin('#__virtuemart_category_categories x ON x.category_child_id = c.virtuemart_category_id'); $query->leftJoin('#__virtuemart_categories_' . $template->get('language', 'general') . ' l ON l.virtuemart_category_id = c.virtuemart_category_id'); $db->setQuery($query); $cats = $db->loadObjectList(); // Check if there are any categories if (empty($cats)) { if (!is_null($csvidb->getErrorMsg())) { $this->addExportContent(JText::sprintf('COM_CSVI_ERROR_RETRIEVING_DATA', $csvidb->getErrorMsg())); $csvilog->AddStats('incorrect', $csvidb->getErrorMsg()); } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); } $this->writeOutput(); return false; } $categories = array(); // Group all categories together according to their level foreach ($cats as $key => $cat) { $categories[$cat->pid][$cat->cid] = $cat->category_name; } // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { if ($field->process) { switch ($field->field_name) { case 'virtuemart_category_id': case 'ordering': $userfields[] = '#__virtuemart_categories.' . $field->field_name; break; case 'file_url': case 'file_url_thumb': $userfields[] = '#__virtuemart_category_medias.virtuemart_media_id'; break; case 'category_name': case 'category_description': case 'metadesc': case 'metakey': case 'slug': case 'category_path': case 'category_path_trans': $userfields[] = $db->quoteName('#__virtuemart_categories') . '.' . $db->quoteName('virtuemart_category_id'); break; default: $userfields[] = $db->quoteName($field->field_name); break; } } } // Build the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__virtuemart_categories'); $query->leftJoin('#__virtuemart_categories_' . $template->get('language', 'general') . ' ON #__virtuemart_categories_' . $template->get('language', 'general') . '.virtuemart_category_id = #__virtuemart_categories.virtuemart_category_id'); $query->leftJoin('#__virtuemart_category_medias ON #__virtuemart_category_medias.virtuemart_category_id = #__virtuemart_categories.virtuemart_category_id'); // Check if there are any selectors $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state != '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = '#__virtuemart_categories.published = ' . $publish_state; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Ignore fields $ignore = array('category_path', 'file_url', 'file_url_thumb', 'category_path_trans'); // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $ignore); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort', $ignore); if (!empty($orderby)) { $query->order($orderby); } // Add a limit if user wants us to $limits = $this->getExportLimit(); // Execute the query $csvidb->setQuery($query, $limits['offset'], $limits['limit']); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // There are no records, write SQL query to log if (!is_null($csvidb->getErrorMsg())) { $this->addExportContent(JText::sprintf('COM_CSVI_ERROR_RETRIEVING_DATA', $csvidb->getErrorMsg())); $this->writeOutput(); $csvilog->AddStats('incorrect', $csvidb->getErrorMsg()); } else { $logcount = $csvidb->getNumRows(); $jinput->set('logcount', $logcount); if ($logcount > 0) { $linenumber = 1; while ($record = $csvidb->getRow()) { $csvilog->setLinenumber($linenumber++); if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeStart()); } foreach ($export_fields as $column_id => $field) { $fieldname = $field->field_name; $fieldreplace = $field->field_name . $field->column_header; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { case 'category_path': $fieldvalue = $helper->createCategoryPathById($record->virtuemart_category_id); $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; case 'category_path_trans': $fieldvalue = $helper->createCategoryPathById($record->virtuemart_category_id, 'target_language'); $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; case 'file_url': case 'file_url_thumb': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_medias'); $query->where('virtuemart_media_id = ' . $record->virtuemart_media_id); $db->setQuery($query); $fieldvalue = $db->loadResult(); $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; case 'category_name': case 'category_description': case 'metadesc': case 'metakey': case 'slug': case 'customtitle': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_categories_' . $template->get('language', 'general')); $query->where('virtuemart_category_id = ' . $record->virtuemart_category_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'custom': if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; default: // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; } } // Output the data $this->addExportFields($record); if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeEnd()); } // Output the contents $this->writeOutput(); } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }
/** * Load the category tree * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return string json encoded values * @since 4.0 */ public function loadCategoryTree() { $jinput = JFactory::getApplication()->input; $helper = new Com_VirtueMart(); $options = $helper->getCategoryTree($jinput->get('language')); echo json_encode($options); }
/** * Subscription export * * Exports subscription details data to either csv, xml or HTML format * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.0 */ public function getStart() { // Get some basic data $db = JFactory::getDbo(); $csvidb = new CsviDb(); $jinput = JFactory::getApplication()->input; $csvilog = $jinput->get('csvilog', null, null); $template = $jinput->get('template', null, null); $exportclass = $jinput->get('export.class', null, null); $export_fields = $jinput->get('export.fields', array(), 'array'); require_once JPATH_COMPONENT_ADMINISTRATOR . '/helpers/com_virtuemart.php'; $helper = new Com_VirtueMart(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { if ($field->process) { switch ($field->field_name) { case 'shoppergroup': case 'username': case 'manufacturer_name': case 'product_sku': case 'category_path': $userfields[] = $db->qn('#__awocoupon') . '.' . $db->qn('id'); break; case 'custom': break; default: $userfields[] = $db->qn($field->field_name); break; } } } // Build the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__awocoupon'); //$query->leftJoin('#__awocoupon_vm_category ON #__awocoupon_vm_category.coupon_id = #__awocoupon_vm.id'); //$query->leftJoin('#__users ON #__users.id = #__akeebasubs_subscriptions.user_id'); // Check if there are any selectors $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state !== '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = '#__awocoupon.published = ' . $publish_state; } // Filter on estore $estore = $template->get('estore', 'coupon'); if ($estore !== '') { $selectors[] = $db->qn('#__awocoupon.estore') . ' = ' . $db->q($estore); } // Filter on function type $function_type = $template->get('function_type', 'coupon'); if ($function_type !== '') { $selectors[] = '#__awocoupon.function_type = ' . $db->quote($function_type); } // Filter on function type 2 $function_type2 = $template->get('function_type2', 'coupon'); if ($function_type2 !== '') { $selectors[] = '#__awocoupon.function_type2 = ' . $db->quote($function_type2); } // Filter on coupon value type $coupon_value_type = $template->get('coupon_value_type', 'coupon'); if ($coupon_value_type !== '') { switch ($coupon_value_type) { case 'empty': $selectors[] = '#__awocoupon.coupon_value_type IS NULL'; break; default: $selectors[] = '#__awocoupon.coupon_value_type = ' . $db->quote($coupon_value_type); break; } } // Filter on discount type $discount_type = $template->get('discount_type', 'coupon'); if ($discount_type !== '') { switch ($discount_type) { case 'empty': $selectors[] = '#__awocoupon.discount_type IS NULL'; break; default: $selectors[] = '#__awocoupon.discount_type = ' . $db->quote($discount_type); break; } } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Any fields to ignore $ignore = array('category_path', 'product_sku', 'manufacturer_name', 'username', 'shoppergroup'); // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $ignore); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort', $ignore); if (!empty($orderby)) { $query->order($orderby); } // Add a limit if user wants us to $limits = $this->getExportLimit(); // Execute the query $csvidb->setQuery($query, $limits['offset'], $limits['limit']); $csvilog->addDebug(JText::_('COM_CSVI_EXPORT_QUERY'), true); // There are no records, write SQL query to log if (!is_null($csvidb->getErrorMsg())) { $this->addExportContent(JText::sprintf('COM_CSVI_ERROR_RETRIEVING_DATA', $csvidb->getErrorMsg())); $this->writeOutput(); $csvilog->AddStats('incorrect', $csvidb->getErrorMsg()); } else { $logcount = $csvidb->getNumRows(); $jinput->set('logcount', $logcount); if ($logcount > 0) { while ($record = $csvidb->getRow()) { if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeStart()); } foreach ($export_fields as $column_id => $field) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { case 'category_path': // Get all linked category IDs $query = $db->getQuery(true)->select($db->qn('asset_id'))->from($db->qn('#__awocoupon_asset1'))->where($db->qn('coupon_id') . ' = ' . $record->id)->where($db->qn('asset_type') . '=' . $db->q('category')); $db->setQuery($query); $catids = $db->loadColumn(); // Create the paths $category_path = trim($helper->createCategoryPathById($catids)); if (strlen(trim($category_path)) == 0) { $category_path = $field->default_value; } $category_path = CsviHelper::replaceValue($field->replace, $category_path); $record->output[$column_id] = $category_path; break; case 'product_sku': // Get all linked product SKUs $query = $db->getQuery(true)->select($db->qn('product_sku'))->from($db->qn('#__virtuemart_products', 'p'))->leftJoin($db->qn('#__awocoupon_asset1', 'a') . ' ON ' . $db->qn('p.virtuemart_product_id') . ' = ' . $db->qn('a.asset_id'))->where($db->qn('a.coupon_id') . ' = ' . $record->id)->where($db->qn('a.asset_type') . '=' . $db->q('product')); $db->setQuery($query); $skus = $db->loadColumn(); // Create the SKUs $product_sku = implode('|', $skus); if (strlen(trim($product_sku)) == 0) { $product_sku = $field->default_value; } $product_sku = CsviHelper::replaceValue($field->replace, $product_sku); $record->output[$column_id] = $product_sku; break; case 'manufacturer_name': $query = $db->getQuery(true)->select($db->qn('mf_name'))->from($db->qn('#__virtuemart_manufacturers_' . $template->get('language', 'general'), 'm'))->leftJoin($db->qn('#__virtuemart_product_manufacturers', 'pm') . ' ON ' . $db->qn('pm.virtuemart_manufacturer_id') . ' = ' . $db->qn('m.virtuemart_manufacturer_id'))->leftJoin($db->qn('#__awocoupon_asset1', 'a') . ' ON ' . $db->qn('a.asset_id') . ' = ' . $db->qn('m.virtuemart_manufacturer_id'))->where($db->qn('a.coupon_id') . ' = ' . $record->id)->where($db->qn('asset_type') . '=' . $db->q('manufacturer'))->group($db->qn('mf_name')); $db->setQuery($query); $manufacturers = $db->loadColumn(); // Create the manufacturer name $manufacturer_name = implode('|', $manufacturers); if (strlen(trim($manufacturer_name)) == 0) { $manufacturer_name = $field->default_value; } $manufacturer_name = CsviHelper::replaceValue($field->replace, $manufacturer_name); $record->output[$column_id] = $manufacturer_name; break; case 'username': // Get all linked product SKUs $query = $db->getQuery(true)->select($db->qn('u.username'))->from($db->qn('#__users', 'u'))->leftJoin($db->qn('#__awocoupon_user', 'au') . ' ON ' . $db->qn('au.user_id') . ' = ' . $db->qn('u.id'))->where('au.coupon_id = ' . $record->id); $db->setQuery($query); $ids = $db->loadColumn(); // Create the SKUs $username = implode('|', $ids); if (strlen(trim($username)) == 0) { $username = $field->default_value; } $username = CsviHelper::replaceValue($field->replace, $username); $record->output[$column_id] = $username; break; case 'shoppergroup': // Get all linked product SKUs $query = $db->getQuery(true)->select($db->qn('shopper_group_name'))->from($db->qn('#__virtuemart_shoppergroups', 's'))->leftJoin($db->qn('#__awocoupon_usergroup', 'u') . ' ON ' . $db->qn('u.shopper_group_id') . '=' . $db->qn('s.virtuemart_shoppergroup_id'))->where($db->qn('u.coupon_id') . ' = ' . $record->id); $db->setQuery($query); $ids = $db->loadColumn(); // Create the shopper groups $usergroup = implode('|', $ids); if (strlen(trim($usergroup)) == 0) { $usergroup = $field->default_value; } $usergroup = CsviHelper::replaceValue($field->replace, $usergroup); $record->output[$column_id] = $usergroup; break; case 'coupon_value': if (!empty($fieldvalue)) { $fieldvalue = number_format($fieldvalue, $template->get('export_price_format_decimal', 'general', 2, 'int'), $template->get('export_price_format_decsep', 'general'), $template->get('export_price_format_thousep', 'general')); } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'startdate': case 'expiration': if (!empty($record->{$fieldname})) { $date = JFactory::getDate($record->{$fieldname}); $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $date->toUnix())); } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'custom': if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; default: // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; } } // Output the data $this->addExportFields($record); if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeEnd()); } // Output the contents $this->writeOutput(); } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }