/** * Property tables export * * Exports category details data to either csv, xml or HTML format * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.4 */ 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'); $this->_domainname = CsviHelper::getDomainName(); $classname = 'CsviCom_Ezrealty_Config'; if (class_exists($classname)) { $config = new $classname(); } // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'id': case 'alias': case 'checked_out': case 'checked_out_time': case 'editor': case 'ordering': case 'published': $userfields[] = $db->qn('e.' . $field->field_name); break; case 'category': $userfields[] = $db->qn('c.name'); break; case 'country': $userfields[] = $db->qn('cn.name', 'country'); break; case 'state': $userfields[] = $db->qn('st.name', 'state'); break; case 'city': $userfields[] = $db->qn('loc.ezcity', 'city'); break; case 'fname': case 'file_title': case 'file_description': case 'file_ordering': case 'picture_url': case 'picture_url_thumb': $userfields[] = $db->qn('e.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($db->qn('#__ezrealty', 'e')); $query->leftJoin($db->qn('#__ezrealty_catg', 'c') . ' ON ' . $db->qn('e.cid') . ' = ' . $db->qn('c.id')); $query->leftJoin($db->qn('#__ezrealty_country', 'cn') . ' ON ' . $db->qn('e.cnid') . ' = ' . $db->qn('cn.id')); $query->leftJoin($db->qn('#__ezrealty_state', 'st') . ' ON ' . $db->qn('e.stid') . ' = ' . $db->qn('st.id')); $query->leftJoin($db->qn('#__ezrealty_locality', 'loc') . ' ON ' . $db->qn('e.locid') . ' = ' . $db->qn('loc.id')); $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state != '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = $db->qn('e.published') . ' = ' . $publish_state; } // Filter by transaction type $transaction_type = $template->get('transaction_type', 'property'); if ($transaction_type[0] != '') { $selectors[] = $db->qn('e.type') . ' IN (' . implode(',', $transaction_type) . ')'; } // Filter by property type $property_type = $template->get('property_type', 'property'); if ($property_type[0] != '') { $selectors[] = $db->qn('e.cid') . ' IN (' . implode(',', $property_type) . ')'; } // Filter by street $street = $template->get('street', 'property'); if ($street[0] != '') { $selectors[] = $db->qn('e.address2') . " IN ('" . implode("','", $street) . "')"; } // Filter by locality $locality = $template->get('locality', 'property'); if ($locality[0] != '') { $selectors[] = $db->qn('e.locality') . " IN ('" . implode("','", $locality) . "')"; } // Filter by states $state = $template->get('state', 'property'); if ($state[0] != '') { $selectors[] = $db->qn('e.state') . " IN ('" . implode("','", $state) . "')"; } // Filter by countries $country = $template->get('country', 'property'); if ($country[0] != '') { $selectors[] = $db->qn('e.country') . " IN ('" . implode("','", $country) . "')"; } // Filter by owner $owner = $template->get('owner', 'property'); if ($owner[0] != '') { $selectors[] = $db->qn('e.owner') . ' IN (' . implode(',', $owner) . ')'; } // 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('custom', 'category', 'country', 'state', 'city', 'fname', 'file_title', 'file_description', 'file_ordering', 'picture_url', 'picture_url_thumb'); // 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; $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 'aucdate': case 'availdate': case 'checked_out_time': case 'listdate': case 'ohdate': case 'ohdate2': $date = JFactory::getDate($record->{$fieldname}); $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $date->toUnix())); $record->output[$column_id] = $fieldvalue; break; case 'expdate': case 'lastupdate': $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $record->{$fieldname})); $record->output[$column_id] = $fieldvalue; break; case 'bond': case 'closeprice': case 'offpeak': case 'price': $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 ($template->get('add_currency_to_price', 'general')) { $fieldvalue = $config->get('er_currencycode') . ' ' . $fieldvalue; } $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; } $record->output[$column_id] = $fieldvalue; break; case 'custom_title': // Get the custom title $query = $db->getQuery(true); $query->select($db->qn('custom_title')); $query->from($db->qn('#__virtuemart_customs')); $query->where($db->qn('virtuemart_custom_id') . ' = ' . $db->q($record->vm_custom_id)); $db->setQuery($query); $title = $db->loadResult(); $fieldvalue = CsviHelper::replaceValue($field->replace, $title); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'category': $fieldvalue = $record->name; // 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 'fname': case 'file_title': case 'file_description': case 'file_ordering': $query = $db->getQuery(true); $query->select($db->qn('i.' . str_ireplace('file_', '', $fieldname))); $query->from($db->qn('#__ezrealty_images', 'i')); $query->leftJoin($db->qn('#__ezrealty', 'e') . ' ON ' . $db->qn('i.propid') . ' = ' . $db->qn('e.id')); $query->where($db->qn('e.id') . ' = ' . $db->q($record->id)); $query->order('i.ordering'); $db->setQuery($query); $titles = $db->loadColumn(); 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; } $record->output[$column_id] = $fieldvalue; break; case 'picture_url': case 'picture_url_thumb': $query = $db->getQuery(true); $query->select($db->qn('fname')); $query->from($db->qn('#__ezrealty_images')); $query->where($db->qn('propid') . ' = ' . $record->id); $query->order($db->qn('ordering')); $db->setQuery($query, 0, 1); $fieldvalue = $db->loadResult(); // Check if there is already a product full image if (strlen(trim($fieldvalue)) > 0) { if ($fieldname == 'picture_url') { $picture_url = $this->_domainname . '/components/com_ezrealty/ezrealty/' . $fieldvalue; } else { $picture_url = $this->_domainname . '/components/com_ezrealty/ezrealty/th/' . $fieldvalue; } } else { $picture_url = $field->default_value; } $picture_url = CsviHelper::replaceValue($field->replace, $picture_url); $record->output[$column_id] = $picture_url; 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(); } } }
/** * Customfields tables 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'); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'id': $userfields[] = $db->qn('c.id'); break; case 'custom_title': $userfields[] = $db->qn('c.vm_custom_id'); break; case 'display_type': $userfields[] = $db->qn('c.type_id'); $userfields[] = $db->qn('f.type', 'display_type'); break; case 'smart_search': case 'expanded': case 'scrollbar_after': $userfields[] = $db->qn('c.params'); break; case 'custom': break; default: $userfields[] = $db->qn($field->field_name); break; } } // Export SQL Query // Build the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from($db->qn("#__cf_customfields", "c")); $query->leftJoin($db->qn("#__cf_filtertypes", "f") . " ON " . $db->qn("c") . '.' . $db->qn("type_id") . ' = ' . $db->qn("f") . '.' . $db->qn("id")); // Ingore fields $ignore = array('custom', 'custom_title', 'display_type', 'smart_search', 'smart_search', 'expanded', 'scrollbar_after'); // 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; $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 'custom_title': // Get the custom title $query = $db->getQuery(true); $query->select($db->qn('custom_title')); $query->from($db->qn('#__virtuemart_customs')); $query->where($db->qn('virtuemart_custom_id') . ' = ' . $db->q($record->vm_custom_id)); $db->setQuery($query); $title = $db->loadResult(); $fieldvalue = CsviHelper::replaceValue($field->replace, $title); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'smart_search': case 'expanded': case 'scrollbar_after': $params = json_decode($record->params); $fieldvalue = $params->{$fieldname}; if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $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(); } } }
/** * Waiting list export * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.1 */ 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'); // 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_user_id': case 'virtuemart_product_id': case 'created_on': case 'modified_on': case 'locked_on': case 'created_by': case 'modified_by': case 'locked_by': $userfields[] = $db->quoteName('#__virtuemart_waitingusers') . '.' . $db->quoteName($field->field_name); break; // Man made fields, do not export them // Man made fields, do not export them case 'custom': case 'username': 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_waitingusers'); $query->leftJoin('#__virtuemart_products ON #__virtuemart_products.virtuemart_product_id = #__virtuemart_waitingusers.virtuemart_product_id'); $query->leftJoin('#__users ON #__users.id = #__virtuemart_waitingusers.virtuemart_user_id'); // Ignore fields $ignore = array('custom', 'username'); // 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(); $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 '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; 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(); } } }
/** * Shipping rate export * * Exports shipping rates 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'); // 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_shipmentmethod_id': case 'shipment_name': case 'shipment_desc': case 'custom': case 'slug': case 'shopper_group_name': $userfields[] = $db->quoteName('#__virtuemart_shipmentmethods') . '.' . $db->quoteName('virtuemart_shipmentmethod_id'); break; case 'shipment_logos': case 'countries': case 'zip_start': case 'zip_stop': case 'weight_start': case 'weight_stop': case 'weight_unit': case 'nbproducts_start': case 'nbproducts_stop': case 'orderamount_start': case 'orderamount_stop': case 'cost': case 'package_fee': case 'tax_id': case 'tax': case 'free_shipment': $userfields[] = $db->quoteName('#__virtuemart_shipmentmethods') . '.' . $db->quoteName('shipment_params'); 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_shipmentmethods'); $query->leftJoin('#__virtuemart_shipmentmethods_' . $template->get('language', 'general') . ' ON #__virtuemart_shipmentmethods_' . $template->get('language', 'general') . '.virtuemart_shipmentmethod_id = #__virtuemart_shipmentmethods.virtuemart_shipmentmethod_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_manufacturers.published = ' . $db->Quote($publish_state); } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Fields to ignore $ignore = array('shipment_name', 'shipment_desc', 'custom', 'slug', 'shipment_logos', 'countries', 'zip_start', 'zip_stop', 'weight_start', 'weight_stop', 'weight_unit', 'nbproducts_start', 'nbproducts_stop', 'orderamount_start', 'orderamount_stop', 'cost', 'package_fee', 'tax_id', 'free_shipment', 'tax', 'shopper_group_name'); // 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(); JRequest::setVar('logcount', $logcount); if ($logcount > 0) { $linenumber = 1; $shipment_params = array(); while ($record = $csvidb->getRow()) { $csvilog->setLinenumber($linenumber++); if ($template->get('export_file', 'general') == 'xml' || $template->get('export_file', 'general') == 'html') { $this->addExportContent($exportclass->NodeStart()); } // Check if the shipment params need to be converted if (isset($record->shipment_params)) { $ship_params = explode('|', $record->shipment_params); array_pop($ship_params); foreach ($ship_params as $param) { list($name, $value) = explode('=', $param); $shipment_params[$name] = $value; } } 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 'shipment_name': case 'shipment_desc': case 'slug': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_shipmentmethods_' . $template->get('language', 'general')); $query->where('virtuemart_shipmentmethod_id = ' . $record->virtuemart_shipmentmethod_id); $db->setQuery($query); $fieldvalue = $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 'shipment_logos': case 'countries': $fieldvalue = json_decode($shipment_params[$fieldname]); if (!empty($fieldvalue)) { $fieldvalue = implode(',', $fieldvalue); } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'zip_start': case 'zip_stop': case 'weight_start': case 'weight_stop': case 'weight_unit': case 'nbproducts_start': case 'nbproducts_stop': case 'orderamount_start': case 'orderamount_stop': case 'cost': case 'tax_id': case 'package_fee': case 'free_shipment': $fieldvalue = json_decode($shipment_params[$fieldname]); $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header, true); break; case 'shopper_group_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_shoppergroups g'); $query->leftJoin('#__virtuemart_shipmentmethod_shoppergroups s ON g.virtuemart_shoppergroup_id = s.virtuemart_shoppergroup_id'); $query->where('s.virtuemart_shipmentmethod_id = ' . $record->virtuemart_shipmentmethod_id); $db->setQuery($query); $fieldvalue = implode('|', $db->loadResultArray()); // 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 'tax': $fieldvalue = json_decode($shipment_params['tax_id']); switch ($fieldvalue) { case '-1': $fieldvalue = 'norule'; break; case '0': $fieldvalue = 'default'; break; default: $query = $db->getQuery(true); $query->select('calc_name'); $query->from('#__virtuemart_calcs'); $query->where($db->quoteName('virtuemart_calc_id') . ' = ' . $fieldvalue); $db->setQuery($query); $fieldvalue = $db->loadResult(); break; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $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(); // Empty the shipment params $shipment_params = null; } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }
/** * Product type names export * * Exports product type names 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'); $producttypeid = $template->get('producttypenames', 'producttypename', false); // Create ID list for searching if (!$producttypeid) { return false; } $typeids = array(); foreach ($producttypeid as $key => $value) { $typeids[] = $db->q('vm_product_type_' . $value); } $component_tables = implode(',', $typeids); // Build something fancy to only get the fieldnames the user wants $userfields = array(); $vmtables = array(); $vmids = array(); $ignore = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'product_sku': $userfields[] = '#__virtuemart_products.product_sku'; break; case 'product_id': $userfields[] = '#__virtuemart_products.virtuemart_product_id AS product_id'; break; case 'product_type_name': $userfields[] = '#__vm_product_type.product_type_name'; break; case 'product_type_id': $userfields[] = '#__vm_product_type.product_type_id'; break; // Man made fields, do not export them // Man made fields, do not export them case 'custom': $ignore[] = $field->field_name; break; default: // Check which product type table belongs to the field $table = null; $query = $db->getQuery(true); $query->select($db->qn('component_table'))->from($db->qn('#__csvi_available_fields'))->where($db->qn('csvi_name') . ' = ' . $db->q($field->field_name))->where($db->qn('component_table') . ' IN (' . $component_tables . ')'); $db->setQuery($query, 0, 1); $table = $db->loadResult(); if ($table) { $vmtables[$table][] = $field->field_name; $vmids[] = $table; } else { $ignore[] = $field->field_name; } break; } } // Check if we have any product type tables to export $vmids = array_unique($vmids); if (!empty($vmids)) { /** * Export SQL Query * Get all products - including items as well as products without a price */ $queries = array(); $filterid = ''; $userfields = array_unique($userfields); foreach ($vmids as $vmidkey => $vmid) { $q = "(SELECT " . implode(",\n", $userfields); foreach ($vmtables as $vmtableskey => $vmfields) { if ($vmid == $vmtableskey) { $filterid = str_replace('vm_product_type_', '', $vmid); foreach ($vmfields as $vmfieldkey => $vmfield) { $q .= ",\n" . $db->qn('#__' . $vmid) . '.' . $db->qn($vmfield) . ' AS ' . $db->qn($vmfield); } } else { foreach ($vmfields as $vmfieldkey => $vmfield) { $q .= ",\n '' AS " . $db->qn($vmfield); } } } $q .= ' FROM #__vm_product_type LEFT JOIN #__vm_product_product_type_xref ON #__vm_product_product_type_xref.product_type_id = #__vm_product_type.product_type_id LEFT JOIN #__virtuemart_products ON #__vm_product_product_type_xref.product_id = #__virtuemart_products.virtuemart_product_id '; // Add the product type X tables $q .= "\nLEFT JOIN #__" . $vmid . " ON #__" . $vmid . ".product_id = #__virtuemart_products.virtuemart_product_id " . "\n"; // Check if there are any selectors $selectors = array(); // Add product type ID checks if (is_int($filterid)) { $selectors[] = '#__vm_product_type.product_type_id = ' . $filterid; } // Filter by product type name if ($producttypeid) { $selectors[] = '#__vm_product_type.product_type_id IN (' . implode(',', $producttypeid) . ')'; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $q .= ' WHERE ' . implode(' AND ', $selectors) . "\n"; } // Special field treatment $special = array(); $special['product_sku'] = $db->qn('#__virtuemart_products') . '.' . $db->qn('product_sku'); $special['product_id'] = $db->qn('#__virtuemart_products') . '.' . $db->qn('virtuemart_product_id'); $special['product_type_name'] = $db->qn('#__vm_product_type') . '.' . $db->qn('product_type_name'); $special['product_type_id'] = $db->qn('#__vm_product_type') . '.' . $db->qn('product_type_id'); // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $ignore, $special); if (!empty($filter)) { $q .= " GROUP BY " . $filter; } } // Order by set field $orderby = $this->getFilterBy('sort', $ignore, $special); if (!empty($orderby)) { $q .= " ORDER BY " . $orderby; } $queries[] = $q . ')'; } // Create the full query $q = implode("\nUNION\n", $queries); // Add export limits $limits = $this->getExportLimit(); // Execute the query $csvidb->setQuery($q, $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) { if ($field->process) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { 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(); } } } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); $this->writeOutput(); $csvilog->AddStats('incorrect', $db->getErrorMsg()); } }
/** * Multiple prices export * * @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'); // 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 'product_sku': $userfields[] = $db->qn('#__virtuemart_product_prices.virtuemart_product_id'); break; case 'product_name': $userfields[] = $db->qn('#__virtuemart_products.virtuemart_product_id'); break; case 'product_currency': $userfields[] = $db->qn('#__virtuemart_currencies.currency_code_3'); break; case 'custom': 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($db->qn('#__virtuemart_product_prices')); $query->leftJoin($db->qn('#__virtuemart_products') . ' ON ' . $db->qn('#__virtuemart_product_prices.virtuemart_product_id') . ' = ' . $db->qn('#__virtuemart_products.virtuemart_product_id')); $query->leftJoin($db->qn('#__virtuemart_shoppergroups') . ' ON ' . $db->qn('#__virtuemart_product_prices.virtuemart_shoppergroup_id') . ' = ' . $db->qn('#__virtuemart_shoppergroups.virtuemart_shoppergroup_id')); $query->leftJoin($db->qn('#__virtuemart_currencies') . ' ON ' . $db->qn('#__virtuemart_product_prices.product_currency') . ' = ' . $db->qn('#__virtuemart_currencies.virtuemart_currency_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_products.published = ' . $publish_state; } // Shopper group selector $shopper_group = $template->get('shopper_groups', 'multipleprices', array()); if ($shopper_group && $shopper_group[0] != 'none') { $selectors[] = "#__virtuemart_shoppergroups.virtuemart_shoppergroup_id IN ('" . implode("','", $shopper_group) . "')"; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Fields to ignore $ignore = array('product_sku', 'product_name', 'shopper_group_name', 'product_currency'); // 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 'product_sku': $query = $db->getQuery(true); $query->select('product_sku'); $query->from('#__virtuemart_products'); $query->where('virtuemart_product_id = ' . $record->virtuemart_product_id); $db->setQuery($query); $fieldvalue = $db->loadResult(); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'product_name': $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; } $record->output[$column_id] = $fieldvalue; break; case 'product_price': $product_price = number_format($record->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; } $product_price = CsviHelper::replaceValue($field->replace, $product_price); $record->output[$column_id] = $product_price; break; case 'product_price_publish_up': case 'product_price_publish_down': $date = JFactory::getDate($record->{$fieldname}); $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $date->toUnix())); $record->output[$column_id] = $fieldvalue; break; case 'product_currency': $fieldvalue = $record->currency_code_3; // Perform the replacement rules $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'shopper_group_name': // Check if the shopper group name is empty if (empty($field->default_value) && empty($fieldvalue)) { $fieldvalue = '*'; } // 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; 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(); } } }
/** * User info export * * Exports user info 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'); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'virtuemart_user_id': case 'created_on': case 'modified_on': case 'locked_on': case 'created_by': case 'modified_by': case 'locked_by': case 'name': case 'agreed': $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName($field->field_name); break; case 'full_name': $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('first_name'); $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('middle_name'); $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('last_name'); break; case 'id': $userfields[] = $db->quoteName('#__users') . '.' . $db->quoteName('id'); break; case 'usergroup_name': $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('virtuemart_user_id'); break; case 'virtuemart_vendor_id': $userfields[] = $db->quoteName('#__virtuemart_vmusers') . '.' . $db->quoteName('virtuemart_vendor_id'); break; case 'state_2_code': case 'state_3_code': case 'state_name': $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('virtuemart_state_id'); break; case 'country_2_code': case 'country_3_code': case 'country_name': case 'virtuemart_country_id': $userfields[] = $db->quoteName('#__virtuemart_userinfos') . '.' . $db->quoteName('virtuemart_country_id'); break; case 'custom': 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_userinfos'); $query->leftJoin('#__virtuemart_vmusers ON #__virtuemart_vmusers.virtuemart_user_id = #__virtuemart_userinfos.virtuemart_user_id'); $query->leftJoin('#__virtuemart_vmuser_shoppergroups ON #__virtuemart_vmuser_shoppergroups.virtuemart_user_id = #__virtuemart_userinfos.virtuemart_user_id'); $query->leftJoin('#__virtuemart_vendors ON #__virtuemart_vendors.virtuemart_vendor_id = #__virtuemart_vmusers.virtuemart_vendor_id'); $query->leftJoin('#__virtuemart_shoppergroups ON #__virtuemart_shoppergroups.virtuemart_shoppergroup_id = #__virtuemart_vmuser_shoppergroups.virtuemart_shoppergroup_id'); $query->leftJoin('#__users ON #__users.id = #__virtuemart_userinfos.virtuemart_user_id'); // Check if there are any selectors $selectors = array(); // Filter by vendors $vendors = $template->get('vendors', 'userinfo', false); if ($vendors && $vendors[0] != 'none') { $selectors[] = '#__virtuemart_vmusers.virtuemart_vendor_id IN (\'' . implode("','", $vendors) . '\')'; } // Filter by permissions $permissions = $template->get('permissions', 'userinfo', false); if ($permissions && $permissions[0] != 'none') { $selectors[] = '#__virtuemart_vmusers.perms IN (\'' . implode("','", $permissions) . '\')'; } // Filter by address type $address = $template->get('userinfo_address', 'userinfo', false); if ($address) { $selectors[] = '#__virtuemart_userinfos.address_type = ' . $db->Quote(strtoupper($address)); } // Filter by user info modified date start $date = $template->get('userinfomdatestart', 'userinfo', false); if ($date) { $userinfomdate = JFactory::getDate($date); $selectors[] = '#__virtuemart_userinfos.modified_on >= ' . $db->Quote($userinfomdate->toMySQL()); } // Filter by user info date end $date = $template->get('userinfomdateend', 'userinfo', false); if ($date) { $userinfomdate = JFactory::getDate($date); $selectors[] = '#__virtuemart_userinfos.modified_on <= ' . $db->Quote($userinfomdate->toMySQL()); } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Array of fields not to handle $ignore = array('full_name', 'usergroup_name', 'state_2_code', 'state_3_code', 'state_name', 'country_2_code', 'country_3_code', 'country_name'); // 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', array('export' => $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 'created_on': case 'modified_on': case 'locked_on': case 'lastvisitdate': $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 'address_type': // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } if ($fieldvalue == 'BT') { $fieldvalue = JText::_('COM_CSVI_BILLING_ADDRESS'); } else { if ($fieldvalue == 'ST') { $fieldvalue = JText::_('COM_CSVI_SHIPPING_ADDRESS'); } } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'full_name': $fieldvalue = str_replace(' ', ' ', $record->first_name . ' ' . $record->middle_name . ' ' . $record->last_name); $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); break; case 'usergroup_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__usergroups'); $query->leftJoin('#__user_usergroup_map ON #__user_usergroup_map.group_id = #__usergroups.id'); $query->where($db->quoteName('user_id') . ' = ' . $record->virtuemart_user_id); $db->setQuery($query); $fieldvalue = $db->loadResult(); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'state_2_code': case 'state_3_code': case 'state_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_states'); $query->where('virtuemart_state_id = ' . $record->virtuemart_state_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'country_2_code': case 'country_3_code': case 'country_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_countries'); $query->where('virtuemart_country_id = ' . $record->virtuemart_country_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $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(); } } }
/** * 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(); } } }
/** * 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'); // 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 'notes': case 'params': case 'state': $userfields[] = $db->quoteName('#__akeebasubs_users') . '.' . $db->quoteName($field->field_name); break; case 'user_id': $userfields[] = $db->quoteName('#__akeebasubs_subscriptions') . '.' . $db->quoteName('user_id'); break; case 'custom': 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('#__akeebasubs_subscriptions'); $query->leftJoin('#__akeebasubs_users ON #__akeebasubs_users.user_id = #__akeebasubs_subscriptions.user_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[] = '#__akeebasubs_subscriptions.enabled = ' . $publish_state; } // Filter by order number start $ordernostart = $template->get('ordernostart', 'order', 0, 'int'); if ($ordernostart > 0) { $selectors[] = '#__akeebasubs_subscriptions.akeebasubs_subscription_id >= ' . $ordernostart; } // Filter by order number end $ordernoend = $template->get('ordernoend', 'order', 0, 'int'); if ($ordernoend > 0) { $selectors[] = '#__akeebasubs_subscriptions.akeebasubs_subscription_id <= ' . $ordernoend; } // Filter by list of order numbers $orderlist = $template->get('orderlist', 'order'); if ($orderlist) { $selectors[] = '#__akeebasubs_subscriptions.akeebasubs_subscription_id IN (' . $orderlist . ')'; } // Check for a pre-defined date $daterange = $template->get('orderdaterange', 'order', ''); if ($daterange != '') { $jdate = JFactory::getDate(); switch ($daterange) { case 'yesterday': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)'; break; case 'thisweek': // Get the current day of the week $dayofweek = $jdate->__get('dayofweek'); $offset = $dayofweek - 1; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) <= CURDATE()'; break; case 'lastweek': // Get the current day of the week $dayofweek = $jdate->__get('dayofweek'); $offset = $dayofweek + 6; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) <= DATE_SUB(CURDATE(), INTERVAL ' . $dayofweek . ' DAY)'; break; case 'thismonth': // Get the current day of the week $dayofmonth = $jdate->__get('day'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $dayofmonth . ' DAY)'; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) <= CURDATE()'; break; case 'lastmonth': // Get the current day of the week $dayofmonth = $jdate->__get('day'); $month = date('n'); $year = date('y'); if ($month > 1) { $month--; } else { $month = 12; $year--; } $daysinmonth = date('t', mktime(0, 0, 0, $month, 25, $year)); $offset = $daysinmonth + $dayofmonth - 1; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) <= DATE_SUB(CURDATE(), INTERVAL ' . $dayofmonth . ' DAY)'; break; case 'thisquarter': // Find out which quarter we are in $month = $jdate->__get('month'); $year = date('Y'); $quarter = ceil($month / 3); switch ($quarter) { case '1': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-01-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-04-01'); break; case '2': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-04-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-07-01'); break; case '3': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-07-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-10-01'); break; case '4': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-10-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year++ . '-01-01'); break; } break; case 'lastquarter': // Find out which quarter we are in $month = $jdate->__get('month'); $year = date('Y'); $quarter = ceil($month / 3); if ($quarter == 1) { $quarter = 4; $year--; } else { $quarter--; } switch ($quarter) { case '1': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-01-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-04-01'); break; case '2': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-04-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-07-01'); break; case '3': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-07-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-10-01'); break; case '4': $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-10-01'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year++ . '-01-01'); break; } break; case 'thisyear': $year = date('Y'); $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-01-01'); $year++; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-01-01'); break; case 'lastyear': $year = date('Y'); $year--; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) >= ' . $db->quote($year . '-01-01'); $year++; $selectors[] = 'DATE(#__akeebasubs_subscriptions.created_on) < ' . $db->quote($year . '-01-01'); break; } } else { // Filter by order date start $orderdatestart = $template->get('orderdatestart', 'order', false); if ($orderdatestart) { $orderdate = JFactory::getDate($orderdatestart); $selectors[] = $db->quoteName('#__akeebasubs_subscriptions') . '.' . $db->quoteName('created_on') . ' >= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order date end $orderdateend = $template->get('orderdateend', 'order', false); if ($orderdateend) { $orderdate = JFactory::getDate($orderdateend); $selectors[] = $db->quoteName('#__akeebasubs_subscriptions') . '.' . $db->quoteName('created_on') . ' <= ' . $db->Quote($orderdate->toMySQL()); } } // Filter by order status $orderstatus = $template->get('orderstatus', 'order', false); if ($orderstatus && $orderstatus[0] != '') { $selectors[] = '#__akeebasubs_subscriptions.state IN (\'' . implode("','", $orderstatus) . '\')'; } // Filter by payment method $orderpayment = $template->get('orderpayment', 'order', false); if ($orderpayment && $orderpayment[0] != '') { $selectors[] = '#__akeebasubs_subscriptions.processor IN (\'' . implode("','", $orderpayment) . '\')'; } // Filter by order price start $pricestart = $template->get('orderpricestart', 'order', false, 'float'); if ($pricestart) { $selectors[] = '#__akeebasubs_subscriptions.gross_amount >= ' . $pricestart; } // Filter by order price end $priceend = $template->get('orderpriceend', 'order', false, 'float'); if ($priceend) { $selectors[] = '#__akeebasubs_subscriptions.gross_amount <= ' . $priceend; } // Filter by order user id $orderuser = $template->get('orderuser', 'order', false); if ($orderuser && $orderuser[0] != '') { $selectors[] = '#__akeebasubs_subscriptions.user_id IN (\'' . implode("','", $orderuser) . '\')'; } // Filter by order product $orderproduct = $template->get('orderproduct', 'order', false); if ($orderproduct && $orderproduct[0] != '') { $selectors[] = '#__akeebasubs_subscriptions.akeebasubs_level_id IN (\'' . implode("','", $orderproduct) . '\')'; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby'); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort'); 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 'net_amount': case 'tax_amount': case 'gross_amount': case 'prediscount_amount': case 'discount_amount': case 'affiliate_comission': $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 'publish_up': case 'publish_down': case 'created_on': case 'first_contact': case 'second_contact': $date = JFactory::getDate($record->{$fieldname}); $fieldvalue = CsviHelper::replaceValue($field->replace, date($template->get('export_date_format', 'general'), $date->toUnix())); $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(); } } }
/** * User export * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.4 */ 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'); $sef = new CsviSef(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'fullname': $userfields[] = $db->qn('u.name', 'fullname'); break; case 'usergroup_name': $userfields[] = $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($db->qn("#__users", "u")); $selectors = array(); // Filter by published state $user_state = $template->get('user_state', 'user'); if ($user_state != '*') { $selectors[] = $db->qn('u.block') . ' = ' . $user_state; } // Filter by active state $user_active = $template->get('user_active', 'user'); if ($user_active == '0') { $selectors[] = $db->qn('u.activation') . ' = ' . $db->q(''); } elseif ($user_active == '1') { $selectors[] = $db->qn('u.activation') . ' = ' . $db->q('32'); } // Filter by user group $user_groups = $template->get('user_group', 'user'); if ($user_groups && $user_groups[0] != '*') { $query->join('LEFT', $db->qn('#__user_usergroup_map', 'map2') . ' ON ' . $db->qn('map2.user_id') . ' = ' . $db->qn('u.id')); if (isset($user_groups)) { $selectors[] = $db->qn('map2.group_id') . ' IN (' . implode(',', $user_groups) . ')'; } } // Filter on range $user_range = $template->get('user_range', 'user'); if ($user_range != '*') { jimport('joomla.utilities.date'); // Get UTC for now. $dNow = new JDate(); $dStart = clone $dNow; switch ($user_range) { case 'past_week': $dStart->modify('-7 day'); break; case 'past_1month': $dStart->modify('-1 month'); break; case 'past_3month': $dStart->modify('-3 month'); break; case 'past_6month': $dStart->modify('-6 month'); break; case 'post_year': case 'past_year': $dStart->modify('-1 year'); break; case 'today': // Ranges that need to align with local 'days' need special treatment. $app = JFactory::getApplication(); $offset = $app->getCfg('offset'); // Reset the start time to be the beginning of today, local time. $dStart = new JDate('now', $offset); $dStart->setTime(0, 0, 0); // Now change the timezone back to UTC. $tz = new DateTimeZone('GMT'); $dStart->setTimezone($tz); break; } if ($user_range == 'post_year') { $selectors[] = $db->qn('u.registerDate') . ' < ' . $db->q($dStart->format('Y-m-d H:i:s')); } else { $selectors[] = $db->qn('u.registerDate') . ' >= ' . $db->q($dStart->format('Y-m-d H:i:s')) . ' AND u.registerDate <=' . $db->q($dNow->format('Y-m-d H:i:s')); } } // 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('custom', 'fullname', 'usergroup_name'); // Check if we need to group the users 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; $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 'usergroup_name': $query = $db->getQuery(true); $query->select($db->qn('title')); $query->from($db->qn('#__usergroups')); $query->leftJoin($db->qn('#__user_usergroup_map') . ' ON ' . $db->qn('#__user_usergroup_map.group_id') . ' = ' . $db->qn('#__usergroups.id')); $query->where($db->qn('user_id') . ' = ' . $record->id); $db->setQuery($query); $groups = $db->loadColumn(); if (is_array($groups)) { $fieldvalue = implode('|', $groups); } else { $fieldvalue = ''; } 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(); } } }
/** * Order items export * * Exports order items 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'); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'created_on': case 'modified_on': case 'locked_on': case 'created_by': case 'modified_by': case 'locked_by': case 'virtuemart_order_id': case 'order_status': case 'virtuemart_vendor_id': $userfields[] = $db->quoteName('#__virtuemart_order_items') . '.' . $db->quoteName($field->field_name); break; case 'product_sku': $userfields[] = $db->quoteName('#__virtuemart_order_items') . '.' . $db->quoteName('order_item_sku') . ' AS product_sku'; break; case 'full_name': $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('first_name'); $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('middle_name'); $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('last_name'); break; default: $userfields[] = $db->quoteName($field->field_name); break; } } // Construct the query // Build the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__virtuemart_order_items'); $query->leftJoin('#__virtuemart_orders ON #__virtuemart_orders.virtuemart_order_id = #__virtuemart_order_items.virtuemart_order_id'); $query->leftJoin('#__virtuemart_order_userinfos AS user_info1 ON user_info1.virtuemart_order_id = #__virtuemart_order_items.virtuemart_order_id'); $query->leftJoin('#__virtuemart_orderstates ON #__virtuemart_orderstates.order_status_code = #__virtuemart_order_items.order_status'); // Check if there are any selectors $selectors = array(); // Filter by order number start $ordernostart = $template->get('orderitemnostart', 'orderitem', array(), 'int'); if ($ordernostart > 0) { $selectors[] = '#__virtuemart_order_items.virtuemart_order_id >= ' . $ordernostart; } // Filter by order number end $ordernoend = $template->get('orderitemnoend', 'orderitem', array(), 'int'); if ($ordernoend > 0) { $selectors[] = '#__virtuemart_order_items.virtuemart_order_id <= ' . $ordernoend; } // Filter by list of order numbers $orderlist = $template->get('orderitemlist', 'orderitem'); if ($orderlist) { $selectors[] = '#__virtuemart_order_items.virtuemart_order_id IN (' . $orderlist . ')'; } // Filter by order date start $orderdatestart = $template->get('orderitemdatestart', 'orderitem', false); if ($orderdatestart) { $orderdate = JFactory::getDate($orderdatestart); $selectors[] = '#__virtuemart_order_items.created_on >= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order date end $orderdateend = $template->get('orderitemdateend', 'orderitem', false); if ($orderdateend) { $orderdate = JFactory::getDate($orderdateend); $selectors[] = '#__virtuemart_order_items.created_on <= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order modified date start $ordermdatestart = $template->get('orderitemmdatestart', 'orderitem', false); if ($ordermdatestart) { $ordermdate = JFactory::getDate($ordermdatestart); $selectors[] = '#__virtuemart_order_items.modified_on >= ' . $db->Quote($ordermdate->toMySQL()); } // Filter by order modified date end $ordermdateend = $template->get('orderitemmdateend', 'orderitem', false); if ($ordermdateend) { $ordermdate = JFactory::getDate($ordermdateend); $selectors[] = '#__virtuemart_order_items.modified_on <= ' . $db->Quote($ordermdate->toMySQL()); } // Filter by order status $orderstatus = $template->get('orderitemstatus', 'orderitem', false); if ($orderstatus && $orderstatus[0] != '') { $selectors[] = '#__virtuemart_order_items.order_status IN (\'' . implode("','", $orderstatus) . '\')'; } // Filter by order price start $pricestart = $template->get('orderitempricestart', 'orderitem', false, 'float'); if ($pricestart) { $selectors[] = '#__virtuemart_orders.order_total >= ' . $pricestart; } // Filter by order price end $priceend = $template->get('orderitempriceend', 'orderitem', false, 'float'); if ($priceend) { $selectors[] = '#__virtuemart_orders.order_total <= ' . $priceend; } // Filter by order product $orderproduct = $template->get('orderitemproduct', 'orderitem', false); if ($orderproduct && $orderproduct[0] != '') { $selectors[] = '#__virtuemart_order_items.order_item_sku IN (\'' . implode("','", $orderproduct) . '\')'; } // Filter by order currency $ordercurrency = $template->get('orderitemcurrency', 'orderitem', false); if ($ordercurrency && $ordercurrency[0] != '') { $selectors[] = '#__virtuemart_orders.order_currency IN (\'' . implode("','", $ordercurrency) . '\')'; } // 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('full_name'); // 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 '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())); $record->output[$column_id] = $fieldvalue; break; case 'product_item_price': case 'product_final_price': case 'product_price': $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')); $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; } $record->output[$column_id] = $fieldvalue; break; case 'full_name': $fieldvalue = str_replace(' ', ' ', $record->first_name . ' ' . $record->middle_name . ' ' . $record->last_name); // 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; 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(); } } }
/** * Order export * * Exports order details * * @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'); $address = strtoupper($template->get('order_address', 'order', false)); if ($address == 'BTST') { $user_info_fields = CsviModelAvailablefields::DbFields('virtuemart_order_userinfos'); } else { $user_info_fields = array(); } // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'created_by': case 'created_on': case 'locked_by': case 'locked_on': case 'modified_by': case 'modified_on': case 'order_status': case 'virtuemart_user_id': case 'virtuemart_vendor_id': case 'virtuemart_order_id': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName($field->field_name); break; case 'email': $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName($field->field_name); break; case 'id': $userfields[] = $db->quoteName('#__users') . '.' . $db->quoteName($field->field_name); break; case 'payment_element': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('virtuemart_paymentmethod_id'); break; case 'shipment_element': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('virtuemart_shipmentmethod_id'); break; case 'state_2_code': case 'state_3_code': case 'state_name': $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('virtuemart_state_id'); break; case 'country_2_code': case 'country_3_code': case 'country_name': case 'virtuemart_country_id': $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('virtuemart_country_id'); break; case 'user_currency': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('user_currency_id'); break; case 'username': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('virtuemart_user_id'); break; case 'full_name': $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('first_name'); $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('middle_name'); $userfields[] = $db->quoteName('user_info1') . '.' . $db->quoteName('last_name'); break; case 'total_order_items': $userfields[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('virtuemart_order_id'); break; case 'product_price_total': $userfields[] = 'product_item_price*product_quantity AS product_price_total'; break; case 'discount_percentage': $userfields[] = '(order_discount/order_total)*100 AS discount_percentage'; break; case 'custom': // These are man made fields, do not try to get them from the database break; default: if ($address == 'BTST' && preg_match("/" . $field->field_name . "/i", join(",", array_keys($user_info_fields)))) { $userfields[] = 'COALESCE(user_info2.' . $field->field_name . ', user_info1.' . $field->field_name . ') AS ' . $field->field_name; } else { $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_orders'); $query->leftJoin('#__virtuemart_order_items ON #__virtuemart_orders.virtuemart_order_id = #__virtuemart_order_items.virtuemart_order_id'); $query->leftJoin('#__virtuemart_order_userinfos AS user_info1 ON #__virtuemart_orders.virtuemart_order_id = user_info1.virtuemart_order_id'); if ($address == 'BTST') { $query->leftJoin('#__virtuemart_order_userinfos AS user_info2 ON #__virtuemart_orders.virtuemart_order_id = user_info2.virtuemart_order_id AND user_info2.address_type = ' . $db->Quote('ST')); } $query->leftJoin('#__virtuemart_orderstates ON #__virtuemart_orders.order_status = #__virtuemart_orderstates.order_status_code'); $query->leftJoin('#__virtuemart_product_manufacturers ON #__virtuemart_order_items.virtuemart_product_id = #__virtuemart_product_manufacturers.virtuemart_product_id'); $query->leftJoin('#__virtuemart_manufacturers ON #__virtuemart_product_manufacturers.virtuemart_manufacturer_id = #__virtuemart_manufacturers.virtuemart_manufacturer_id'); $query->leftJoin('#__users ON #__users.id = user_info1.virtuemart_user_id'); $query->leftJoin('#__virtuemart_countries ON #__virtuemart_countries.virtuemart_country_id = user_info1.virtuemart_country_id'); // Check if there are any selectors $selectors = array(); // Filter by manufacturer $manufacturer = $template->get('ordermanufacturer', 'order', false); if ($manufacturer && $manufacturer[0] != 'none') { $selectors[] = '#__virtuemart_manufacturers.virtuemart_manufacturer_id IN (' . implode(',', $manufacturer) . ')'; } // Filter by order number start $ordernostart = $template->get('ordernostart', 'order', false, 'int'); if ($ordernostart > 0) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id >= ' . $ordernostart; } // Filter by order number end $ordernoend = $template->get('ordernoend', 'order', false, 'int'); if ($ordernoend > 0) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id <= ' . $ordernoend; } // Filter by list of order numbers $orderlist = $template->get('orderlist', 'order'); if ($orderlist) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id IN (' . $orderlist . ')'; } // Filter by order date start $orderdatestart = $template->get('orderdatestart', 'order', false); if ($orderdatestart) { $orderdate = JFactory::getDate($orderdatestart); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('created_on') . ' >= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order date end $orderdateend = $template->get('orderdateend', 'order', false); if ($orderdateend) { $orderdate = JFactory::getDate($orderdateend); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('created_on') . ' <= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order modified date start $ordermdatestart = $template->get('ordermdatestart', 'order', false); if ($ordermdatestart) { $ordermdate = JFactory::getDate($ordermdatestart); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('modified_on') . ' >= ' . $db->Quote($ordermdate->toMySQL()); } // Filter by order modified date end $ordermdateend = $template->get('ordermdateend', 'order', false); if ($ordermdateend) { $ordermdate = JFactory::getDate($ordermdateend); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('modified_on') . ' <= ' . $db->Quote($ordermdate->toMySQL()); } // Filter by order status $orderstatus = $template->get('orderstatus', 'order', false); if ($orderstatus && $orderstatus[0] != '') { $selectors[] = '#__virtuemart_orders.order_status IN (\'' . implode("','", $orderstatus) . '\')'; } // Filter by order price start $pricestart = $template->get('orderpricestart', 'order', false, 'float'); if ($pricestart) { $selectors[] = '#__virtuemart_orders.order_total >= ' . $pricestart; } // Filter by order price end $priceend = $template->get('orderpriceend', 'order', false, 'float'); if ($priceend) { $selectors[] = '#__virtuemart_orders.order_total <= ' . $priceend; } // Filter by order user id $orderuser = $template->get('orderuser', 'order', false); if ($orderuser && $orderuser[0] != '') { $selectors[] = '#__virtuemart_orders.virtuemart_user_id IN (\'' . implode("','", $orderuser) . '\')'; } // Filter by order product $orderproduct = $template->get('orderproduct', 'order', false); if ($orderproduct && $orderproduct[0] != '') { $selectors[] = '#__virtuemart_order_items.order_item_sku IN (\'' . implode("','", $orderproduct) . '\')'; } // Filter by address type if ($address) { switch (strtoupper($address)) { case 'BTST': $selectors[] = "user_info1.address_type = 'BT'"; break; default: $selectors[] = 'user_info1.address_type = ' . $db->Quote(strtoupper($address)); break; } } // Filter by order currency $ordercurrency = $template->get('ordercurrency', 'order', false); if ($ordercurrency && $ordercurrency[0] != '') { $selectors[] = '#__virtuemart_orders.order_currency IN (\'' . implode("','", $ordercurrency) . '\')'; } // Filter by payment method $orderpayment = $template->get('orderpayment', 'order', false); if ($orderpayment && $orderpayment[0] != '') { $selectors[] = '#__virtuemart_orders.virtuemart_paymentmethod_id IN (\'' . implode("','", $orderpayment) . '\')'; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Check if we need to group the orders together $groupby = $template->get('groupby', 'general', false, 'bool'); if ($groupby) { $filter = $this->getFilterBy('groupby', $address, $user_info_fields); if (!empty($filter)) { $query->group($filter); } } // Order by set field $orderby = $this->getFilterBy('sort', $address, $user_info_fields); 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) { if ($field->process) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { case 'payment_element': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_paymentmethods'); $query->where('virtuemart_paymentmethod_id = ' . $record->virtuemart_paymentmethod_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'shipment_element': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_shipmentmethods'); $query->where('virtuemart_shipmentmethod_id = ' . $record->virtuemart_shipmentmethod_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'state_2_code': case 'state_3_code': case 'state_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_states'); $query->where('virtuemart_state_id = ' . $record->virtuemart_state_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'country_2_code': case 'country_3_code': case 'country_name': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_countries'); $query->where('virtuemart_country_id = ' . $record->virtuemart_country_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'user_currency': $query = $db->getQuery(true); $query->select('currency_code_3'); $query->from('#__virtuemart_currencies'); $query->where('virtuemart_currency_id = ' . $record->user_currency_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'user_email': $fieldvalue = CsviHelper::replaceValue($field->replace, $record->email); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'user_id': $fieldvalue = CsviHelper::replaceValue($field->replace, $record->virtuemart_user_id); $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; 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 'address_type': // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } if ($fieldvalue == 'BT') { $fieldvalue = JText::_('COM_CSVI_BILLING_ADDRESS'); } else { if ($fieldvalue == 'ST') { $fieldvalue = JText::_('COM_CSVI_SHIPPING_ADDRESS'); } } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'full_name': $fieldvalue = str_replace(' ', ' ', $record->first_name . ' ' . $record->middle_name . ' ' . $record->last_name); // 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 'total_order_items': $query = $db->getQuery(true); $query->select('COUNT(virtuemart_order_id) AS totalitems'); $query->from('#__virtuemart_order_items'); $query->where('virtuemart_order_id = ' . $record->virtuemart_order_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); break; case 'custom': // Has no database value, get the default value only $fieldvalue = $field->default_value; $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'username': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__users'); $query->where('id = ' . $record->virtuemart_user_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); break; case 'order_tax': case 'order_total': case 'order_subtotal': case 'order_shipment': case 'order_shipment_tax': case 'order_payment': case 'order_payment_tax': case 'coupon_discount': case 'order_discount': case 'user_currency_rate': case 'product_price_total': case 'discount_percentage': $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')); $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); 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; } } } // Clean the totalitems JRequest::setVar('total_order_items', 0); 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(); } } }
/** * SnelStart export * * Exports order details * * @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'); $order_discount = 0; $coupon_code = ''; $coupon_discount = ''; $address = strtoupper($template->get('order_address', 'order', false)); $user_info_fields = CsviModelAvailablefields::DbFields('virtuemart_order_userinfos'); // Build something fancy to only get the fieldnames the user wants $userfields = array(); // Order ID is needed as controller $userfields[] = $db->qn('#__virtuemart_orders.virtuemart_order_id'); // Process the other export fields foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'created_by': case 'created_on': case 'locked_by': case 'locked_on': case 'modified_by': case 'modified_on': case 'order_status': case 'virtuemart_user_id': case 'virtuemart_vendor_id': case 'virtuemart_order_id': case 'virtuemart_paymentmethod_id': case 'virtuemart_shipmentmethod_id': $userfields[] = $db->qn('#__virtuemart_orders.' . $field->field_name); break; case 'email': case 'company': case 'title': case 'last_name': case 'first_name': case 'middle_name': case 'phone_1': case 'phone_2': case 'fax': case 'address_1': case 'address_2': case 'city': case 'zip': $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('BT') . ' THEN ' . $db->qn('user_info.' . $field->field_name) . ' ELSE NULL END) AS ' . $db->qn($field->field_name); break; case 'id': $userfields[] = $db->qn('#__users.' . $field->field_name); break; case 'payment_element': $userfields[] = $db->qn('#__virtuemart_orders.virtuemart_paymentmethod_id'); break; case 'shipment_element': $userfields[] = $db->qn('#__virtuemart_orders.virtuemart_shipmentmethod_id'); break; case 'state_2_code': case 'state_3_code': case 'state_name': $userfields[] = $db->qn('user_info.virtuemart_state_id', $field->field_name); break; case 'country_2_code': case 'country_3_code': case 'country_name': case 'virtuemart_country_id': $userfields[] = $db->qn('user_info.virtuemart_country_id', $field->field_name); break; case 'user_currency': $userfields[] = $db->qn('#__virtuemart_orders.user_currency_id'); break; case 'username': $userfields[] = $db->qn('#__virtuemart_orders.virtuemart_user_id'); break; case 'full_name': $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('BT') . ' THEN ' . $db->qn('user_info.first_name') . ' ELSE NULL END) AS ' . $db->qn('first_name'); $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('BT') . ' THEN ' . $db->qn('user_info.middle_name') . ' ELSE NULL END) AS ' . $db->qn('middle_name'); $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('BT') . ' THEN ' . $db->qn('user_info.last_name') . ' ELSE NULL END) AS ' . $db->qn('last_name'); break; case 'product_price_total': $userfields[] = 'product_item_price*product_quantity AS product_price_total'; break; case 'discount_percentage': $userfields[] = '(order_discount/order_total)*100 AS discount_percentage'; break; case 'product_subtotal_discount_percentage': $userfields[] = $db->qn('#__virtuemart_order_items.product_basePriceWithTax'); $userfields[] = $db->qn('#__virtuemart_order_items.product_final_price'); $userfields[] = $db->qn('#__virtuemart_order_items.product_subtotal_discount'); break; case 'shipping_company': case 'shipping_title': case 'shipping_last_name': case 'shipping_first_name': case 'shipping_middle_name': case 'shipping_phone_1': case 'shipping_phone_2': case 'shipping_fax': case 'shipping_address_1': case 'shipping_address_2': case 'shipping_city': case 'shipping_zip': case 'shipping_email': $name = str_ireplace('shipping_', '', $field->field_name); $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.' . $name) . ' ELSE NULL END) AS ' . $db->qn($field->field_name); break; case 'shipping_state_name': case 'shipping_state_2_code': case 'shipping_state_3_code': $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.virtuemart_state_id') . ' ELSE NULL END) AS ' . $db->qn($field->field_name); break; case 'shipping_country_name': case 'shipping_country_2_code': case 'shipping_country_3_code': $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.virtuemart_country_id') . ' ELSE NULL END) AS ' . $db->qn($field->field_name); break; case 'shipping_full_name': $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.first_name') . ' ELSE NULL END) AS ' . $db->qn('shipping_first_name'); $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.middle_name') . ' ELSE NULL END) AS ' . $db->qn('shipping_middle_name'); $userfields[] = 'MAX(CASE WHEN ' . $db->qn('user_info.address_type') . ' = ' . $db->q('ST') . ' THEN ' . $db->qn('user_info.last_name') . ' ELSE NULL END) AS ' . $db->qn('shipping_last_name'); break; case 'coupon_discount': // Coupon fields $userfields[] = $db->qn('#__virtuemart_orders.coupon_discount'); $userfields[] = $db->qn('#__virtuemart_orders.coupon_code'); break; case 'total_order_items': case 'custom': // These are man made fields, do not try to get them from the database 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('#__virtuemart_orders'); $query->leftJoin('#__virtuemart_order_items ON #__virtuemart_orders.virtuemart_order_id = #__virtuemart_order_items.virtuemart_order_id'); $query->leftJoin('#__virtuemart_order_userinfos AS user_info ON #__virtuemart_orders.virtuemart_order_id = user_info.virtuemart_order_id'); $query->leftJoin('#__virtuemart_orderstates ON #__virtuemart_orders.order_status = #__virtuemart_orderstates.order_status_code'); $query->leftJoin('#__virtuemart_product_manufacturers ON #__virtuemart_order_items.virtuemart_product_id = #__virtuemart_product_manufacturers.virtuemart_product_id'); $query->leftJoin('#__virtuemart_manufacturers ON #__virtuemart_product_manufacturers.virtuemart_manufacturer_id = #__virtuemart_manufacturers.virtuemart_manufacturer_id'); $query->leftJoin('#__users ON #__users.id = user_info.virtuemart_user_id'); $query->leftJoin('#__virtuemart_countries ON #__virtuemart_countries.virtuemart_country_id = user_info.virtuemart_country_id'); $query->leftJoin('#__virtuemart_invoices ON #__virtuemart_orders.virtuemart_order_id = #__virtuemart_invoices.virtuemart_order_id'); $query->leftJoin('#__virtuemart_paymentmethods_' . $template->get('language', 'general') . ' ON #__virtuemart_orders.virtuemart_paymentmethod_id = #__virtuemart_paymentmethods_' . $template->get('language', 'general') . '.virtuemart_paymentmethod_id'); $query->leftJoin('#__virtuemart_shipmentmethods_' . $template->get('language', 'general') . ' ON #__virtuemart_orders.virtuemart_shipmentmethod_id = #__virtuemart_shipmentmethods_' . $template->get('language', 'general') . '.virtuemart_shipmentmethod_id'); // Check if there are any selectors $selectors = array(); // Filter by manufacturer $manufacturer = $template->get('ordermanufacturer', 'order', false); if ($manufacturer && $manufacturer[0] != 'none') { $selectors[] = '#__virtuemart_manufacturers.virtuemart_manufacturer_id IN (' . implode(',', $manufacturer) . ')'; } // Filter by order number start $ordernostart = $template->get('ordernostart', 'order', 0, 'int'); if ($ordernostart > 0) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id >= ' . $ordernostart; } // Filter by order number end $ordernoend = $template->get('ordernoend', 'order', 0, 'int'); if ($ordernoend > 0) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id <= ' . $ordernoend; } // Filter by list of order numbers $orderlist = $template->get('orderlist', 'order'); if ($orderlist) { $selectors[] = '#__virtuemart_orders.virtuemart_order_id IN (' . $orderlist . ')'; } // Check for a pre-defined date $daterange = $template->get('orderdaterange', 'order', ''); if ($daterange != '') { $jdate = JFactory::getDate(); switch ($daterange) { case 'yesterday': $selectors[] = 'DATE(#__virtuemart_orders.created_on) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)'; break; case 'thisweek': // Get the current day of the week $dayofweek = $jdate->__get('dayofweek'); $offset = $dayofweek - 1; $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__virtuemart_orders.created_on) <= CURDATE()'; break; case 'lastweek': // Get the current day of the week $dayofweek = $jdate->__get('dayofweek'); $offset = $dayofweek + 6; $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__virtuemart_orders.created_on) <= DATE_SUB(CURDATE(), INTERVAL ' . $dayofweek . ' DAY)'; break; case 'thismonth': // Get the current day of the week $dayofmonth = $jdate->__get('day'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $dayofmonth . ' DAY)'; $selectors[] = 'DATE(#__virtuemart_orders.created_on) <= CURDATE()'; break; case 'lastmonth': // Get the current day of the week $dayofmonth = $jdate->__get('day'); $month = date('n'); $year = date('y'); if ($month > 1) { $month--; } else { $month = 12; $year--; } $daysinmonth = date('t', mktime(0, 0, 0, $month, 25, $year)); $offset = $daysinmonth + $dayofmonth - 1; $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= DATE_SUB(CURDATE(), INTERVAL ' . $offset . ' DAY)'; $selectors[] = 'DATE(#__virtuemart_orders.created_on) <= DATE_SUB(CURDATE(), INTERVAL ' . $dayofmonth . ' DAY)'; break; case 'thisquarter': // Find out which quarter we are in $month = $jdate->__get('month'); $year = date('Y'); $quarter = ceil($month / 3); switch ($quarter) { case '1': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-01-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-04-01'); break; case '2': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-04-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-07-01'); break; case '3': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-07-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-10-01'); break; case '4': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-10-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year++ . '-01-01'); break; } break; case 'lastquarter': // Find out which quarter we are in $month = $jdate->__get('month'); $year = date('Y'); $quarter = ceil($month / 3); if ($quarter == 1) { $quarter = 4; $year--; } else { $quarter--; } switch ($quarter) { case '1': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-01-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-04-01'); break; case '2': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-04-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-07-01'); break; case '3': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-07-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-10-01'); break; case '4': $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-10-01'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year++ . '-01-01'); break; } break; case 'thisyear': $year = date('Y'); $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-01-01'); $year++; $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-01-01'); break; case 'lastyear': $year = date('Y'); $year--; $selectors[] = 'DATE(#__virtuemart_orders.created_on) >= ' . $db->quote($year . '-01-01'); $year++; $selectors[] = 'DATE(#__virtuemart_orders.created_on) < ' . $db->quote($year . '-01-01'); break; } } else { // Filter by order date start $orderdatestart = $template->get('orderdatestart', 'order', false); if ($orderdatestart) { $orderdate = JFactory::getDate($orderdatestart); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('created_on') . ' >= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order date end $orderdateend = $template->get('orderdateend', 'order', false); if ($orderdateend) { $orderdate = JFactory::getDate($orderdateend); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('created_on') . ' <= ' . $db->Quote($orderdate->toMySQL()); } // Filter by order modified date start $ordermdatestart = $template->get('ordermdatestart', 'order', false); if ($ordermdatestart) { $ordermdate = JFactory::getDate($ordermdatestart); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('modified_on') . ' >= ' . $db->Quote($ordermdate->toMySQL()); } // Filter by order modified date end $ordermdateend = $template->get('ordermdateend', 'order', false); if ($ordermdateend) { $ordermdate = JFactory::getDate($ordermdateend); $selectors[] = $db->quoteName('#__virtuemart_orders') . '.' . $db->quoteName('modified_on') . ' <= ' . $db->Quote($ordermdate->toMySQL()); } } // Filter by order status $orderstatus = $template->get('orderstatus', 'order', false); if ($orderstatus && $orderstatus[0] != '') { $selectors[] = '#__virtuemart_orders.order_status IN (\'' . implode("','", $orderstatus) . '\')'; } // Filter by order price start $pricestart = $template->get('orderpricestart', 'order', false, 'float'); if ($pricestart) { $selectors[] = '#__virtuemart_orders.order_total >= ' . $pricestart; } // Filter by order price end $priceend = $template->get('orderpriceend', 'order', false, 'float'); if ($priceend) { $selectors[] = '#__virtuemart_orders.order_total <= ' . $priceend; } // Filter by order user id $orderuser = $template->get('orderuser', 'order', false); if ($orderuser && $orderuser[0] != '') { $selectors[] = '#__virtuemart_orders.virtuemart_user_id IN (\'' . implode("','", $orderuser) . '\')'; } // Filter by order product $orderproduct = $template->get('orderproduct', 'order', false); if ($orderproduct && $orderproduct[0] != '') { $selectors[] = '#__virtuemart_order_items.order_item_sku IN (\'' . implode("','", $orderproduct) . '\')'; } // Filter by address type if ($address) { switch (strtoupper($address)) { case 'BTST': $selectors[] = "user_info.address_type = 'BT'"; break; default: $selectors[] = 'user_info.address_type = ' . $db->q(strtoupper($address)); break; } } // Filter by order currency $ordercurrency = $template->get('ordercurrency', 'order', false); if ($ordercurrency && $ordercurrency[0] != '') { $selectors[] = '#__virtuemart_orders.order_currency IN (\'' . implode("','", $ordercurrency) . '\')'; } // Filter by payment method $orderpayment = $template->get('orderpayment', 'order', false); if ($orderpayment && $orderpayment[0] != '') { $selectors[] = '#__virtuemart_orders.virtuemart_paymentmethod_id IN (\'' . implode("','", $orderpayment) . '\')'; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Check if we need to group the orders together $query->group($db->qn('#__virtuemart_orders.virtuemart_order_id')); // Order by set field $orderby = $this->getFilterBy('sort', $address, $user_info_fields); 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; $orderid = null; while ($record = $csvidb->getRow()) { $csvilog->setLinenumber($linenumber++); // Add an order if (is_null($orderid) || $record->virtuemart_order_id != $orderid) { if (!is_null($orderid)) { // Output the contents $this->addExportContent($exportclass->NodeEnd()); $this->writeOutput(); } $orderid = $record->virtuemart_order_id; $this->addExportContent($exportclass->Order()); } // Add an orderline $this->addExportContent($exportclass->Orderline()); foreach ($export_fields as $column_id => $field) { if ($field->process) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { case 'payment_element': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_paymentmethods'); $query->where('virtuemart_paymentmethod_id = ' . $record->virtuemart_paymentmethod_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $record->output[$column_id] = $fieldvalue; break; case 'shipment_element': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__virtuemart_shipmentmethods'); $query->where('virtuemart_shipmentmethod_id = ' . $record->virtuemart_shipmentmethod_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $record->output[$column_id] = $fieldvalue; break; case 'state_2_code': case 'state_3_code': case 'state_name': case 'shipping_state_2_code': case 'shipping_state_3_code': case 'shipping_state_name': $query = $db->getQuery(true); $query->select(str_ireplace('shipping_', '', $fieldname)); $query->from('#__virtuemart_states'); $query->where('virtuemart_state_id = ' . $record->{$fieldname}); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $record->output[$column_id] = $fieldvalue; break; case 'country_2_code': case 'country_3_code': case 'country_name': case 'shipping_country_2_code': case 'shipping_country_3_code': case 'shipping_country_name': $query = $db->getQuery(true); $query->select(str_ireplace('shipping_', '', $fieldname)); $query->from('#__virtuemart_countries'); $query->where('virtuemart_country_id = ' . $record->{$fieldname}); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $record->output[$column_id] = $fieldvalue; break; case 'user_currency': $query = $db->getQuery(true); $query->select('currency_code_3'); $query->from('#__virtuemart_currencies'); $query->where('virtuemart_currency_id = ' . $record->user_currency_id); $db->setQuery($query); $fieldvalue = CsviHelper::replaceValue($field->replace, $db->loadResult()); $record->output[$column_id] = $fieldvalue; break; case 'user_email': $fieldvalue = CsviHelper::replaceValue($field->replace, $record->email); $record->output[$column_id] = $fieldvalue; break; case 'user_id': $fieldvalue = CsviHelper::replaceValue($field->replace, $record->virtuemart_user_id); $record->output[$column_id] = $fieldvalue; break; 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())); $record->output[$column_id] = $fieldvalue; break; case 'address_type': // Check if we have any content otherwise use the default value if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } if ($fieldvalue == 'BT') { $fieldvalue = JText::_('COM_CSVI_BILLING_ADDRESS'); } else { if ($fieldvalue == 'ST') { $fieldvalue = JText::_('COM_CSVI_SHIPPING_ADDRESS'); } } $record->output[$column_id] = $fieldvalue; break; case 'full_name': $fieldvalue = str_replace(' ', ' ', $record->first_name . ' ' . $record->middle_name . ' ' . $record->last_name); // 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 'shipping_full_name': $fieldvalue = str_replace(' ', ' ', $record->shipping_first_name . ' ' . $record->shipping_middle_name . ' ' . $record->shipping_last_name); if (empty($fieldvalue)) { $fieldvalue = str_replace(' ', ' ', $record->first_name . ' ' . $record->middle_name . ' ' . $record->last_name); } // 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 'total_order_items': $query = $db->getQuery(true); $query->select('COUNT(virtuemart_order_id) AS totalitems'); $query->from('#__virtuemart_order_items'); $query->where('virtuemart_order_id = ' . $record->virtuemart_order_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; case 'username': $query = $db->getQuery(true); $query->select($fieldname); $query->from('#__users'); $query->where('id = ' . $record->virtuemart_user_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 'order_tax': case 'order_total': case 'order_subtotal': case 'order_shipment': case 'order_shipment_tax': case 'order_payment': case 'order_payment_tax': case 'order_discount': case 'user_currency_rate': case 'product_price_total': case 'discount_percentage': $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')); $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; } $record->output[$column_id] = $fieldvalue; break; case 'product_subtotal_discount_percentage': if ($record->product_basePriceWithTax > 0) { $fieldvalue = number_format($record->product_subtotal_discount / $record->product_basePriceWithTax * 100 * -1, 3); } else { number_format($record->product_subtotal_discount / $record->product_final_price * 100 * -1, 3); } if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $record->output[$column_id] = $fieldvalue; break; case 'order_discountAmount': // Set some discounts $order_discount = number_format($fieldvalue * -1, 3); $record->output[$column_id] = null; break; case 'coupon_discount': // For coupon field usage if ($record->coupon_discount > 0) { $coupon_code = $record->coupon_code; $coupon_discount = number_format($record->coupon_discount * -1, 3); } $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')); $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; } $record->output[$column_id] = $fieldvalue; break; default: //if strpos($fieldname, 'shipping_') // 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); // Add the order discount row if (!empty($order_discount)) { $record = new stdClass(); foreach ($export_fields as $column_id => $field) { $value = null; switch ($field->field_name) { case 'order_item_sku': $value = 'Korting'; break; case 'order_item_name': $value = 'Korting'; break; case 'product_quantity': $value = '1'; break; case 'product_item_price': $value = $order_discount ? $order_discount : 0; $order_discount = 0; break; } $record->output[$column_id] = $value; } $this->addExportContent($exportclass->Orderline()); $this->addExportFields($record); } // Add the coupon discount if (!empty($coupon_code) && !empty($coupon_discount)) { $record = new stdClass(); foreach ($export_fields as $column_id => $field) { $value = null; switch ($field->field_name) { case 'order_item_sku': $value = $coupon_code; break; case 'order_item_name': $value = $coupon_code; break; case 'product_quantity': $value = '1'; break; case 'product_item_price': $value = $coupon_discount ? $coupon_discount : 0; break; } $record->output[$column_id] = $value; } $this->addExportContent($exportclass->Orderline()); $this->addExportFields($record); } // Clean the totalitems JRequest::setVar('total_order_items', 0); } // Close the XML structure $this->addExportContent($exportclass->NodeEnd()); $this->writeOutput(); } else { $this->addExportContent(JText::_('COM_CSVI_NO_DATA_FOUND')); // Output the contents $this->writeOutput(); } } }
/** * Content export * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.4 */ 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'); $sef = new CsviSef(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'category_path': $userfields[] = $db->qn('c.catid'); break; case 'article_url': $userfields[] = $db->qn('c.id'); $userfields[] = $db->qn('c.catid'); 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($db->qn("#__content", "c")); $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state != '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = $db->qn('c.published') . ' = ' . $publish_state; } // Filter by language $language = $template->get('content_language', 'general'); if ($language != '*') { $selectors[] = $db->qn('c.language') . ' = ' . $db->q($language); } // Filter by category $categories = $template->get('content_categories', 'content'); if ($categories && $categories[0] != '*') { $selectors[] = $db->qn('catid') . " IN ('" . implode("','", $categories) . "')"; } // 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('custom', 'category_path', 'article_url'); // 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; $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': $query->clear(); $query->select('path')->from('#__categories')->where('id = ' . $record->catid); $db->setQuery($query); $fieldvalue = $db->loadResult(); if (strlen(trim($fieldvalue)) == 0) { $fieldvalue = $field->default_value; } $fieldvalue = CsviHelper::replaceValue($field->replace, $fieldvalue); $record->output[$column_id] = $fieldvalue; break; case 'article_url': // Let's create a SEF URL $_SERVER['QUERY_STRING'] = 'option=com_content&view=article&id=' . $record->id . '&catid=' . $record->catid . '&Itemid=' . $template->get('j_itemid', 'product', 1, 'int'); $article_url = $sef->getSiteRoute('index.php?' . $_SERVER['QUERY_STRING']); // Check for https, replace with http. https has unnecessary overhead if (substr($article_url, 0, 5) == 'https') { $article_url = 'http' . substr($article_url, 5); } $article_url = CsviHelper::replaceValue($field->replace, $article_url); $record->output[$column_id] = $article_url; 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(); } } }
/** * 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(); } } }
/** * Product type parameters export * * Exports product type parameters 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'); $ignore = array(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'product_type_id': $userfields[] = '#__vm_product_type.product_type_id'; break; case 'custom': $ignore[] = $field->field_name; break; default: if ($field->process) { $userfields[] = $db->quoteName($field->field_name); } break; } } // Execute the query $userfields = array_unique($userfields); $query = $db->getQuery(true); $query->select(implode(",\n", $userfields)); $query->from('#__vm_product_type_parameter')->leftJoin('#__vm_product_type ON #__vm_product_type_parameter.product_type_id = #__vm_product_type.product_type_id'); // Check if we need to group the results 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(); $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()); } // Process all the export fields foreach ($export_fields as $column_id => $field) { if ($field->process) { $fieldname = $field->field_name; // Add the replacement if (isset($record->{$fieldname})) { $fieldvalue = CsviHelper::replaceValue($field->replace, $record->{$fieldname}); } else { $fieldvalue = ''; } switch ($fieldname) { 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(); } } }
/** * 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'); // 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 'user_id': case 'akeebasubs_affiliate_id': $userfields[] = $db->quoteName('#__akeebasubs_affiliates') . '.' . $db->quoteName($field->field_name); break; case 'money_owed': case 'money_paid': case 'total_commission': $userfields[] = $db->quoteName('#__akeebasubs_affiliates') . '.' . $db->quoteName('akeebasubs_affiliate_id'); break; case 'custom': 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('#__akeebasubs_affiliates'); $query->leftJoin('#__akeebasubs_affpayments ON #__akeebasubs_affpayments.akeebasubs_affiliate_id = #__akeebasubs_affiliates.akeebasubs_affiliate_id'); $query->leftJoin('#__users ON #__users.id = #__akeebasubs_affiliates.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[] = '#__akeebasubs_affiliates.enabled = ' . $publish_state; } // Check if we need to attach any selectors to the query if (count($selectors) > 0) { $query->where(implode("\n AND ", $selectors)); } // Ignore some custom fields $ignore = array('money_owed', 'money_paid', 'total_commission'); // 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 'money_owed': $query1 = $db->getQuery(true); $query1->select('akeebasubs_affiliate_id, SUM(affiliate_comission) AS money_owed'); $query1->from('#__akeebasubs_subscriptions'); $query1->where('state = ' . $db->Quote('C')); $query1->where('akeebasubs_affiliate_id = ' . $record->akeebasubs_affiliate_id); $query1->group('akeebasubs_affiliate_id'); $query2 = $db->getQuery(true); $query2->select('akeebasubs_affiliate_id, SUM(amount) AS money_paid'); $query2->from('#__akeebasubs_affpayments'); $query2->where('akeebasubs_affiliate_id = ' . $record->akeebasubs_affiliate_id); $query2->group('akeebasubs_affiliate_id'); $query = $db->getQuery(true); $query->select('money_owed-money_paid AS balance'); $query->from('#__akeebasubs_affiliates'); $query->leftJoin('(' . $query1 . ') AS o USING (' . $db->quoteName('akeebasubs_affiliate_id') . ')'); $query->leftJoin('(' . $query2 . ') AS p USING (' . $db->quoteName('akeebasubs_affiliate_id') . ')'); $query->where('akeebasubs_affiliate_id = ' . $record->akeebasubs_affiliate_id); $db->setQuery($query); $fieldvalue = $db->loadResult(); $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; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'money_paid': $query = $db->getQuery(true); $query->select('SUM(amount) AS money_paid'); $query->from('lwraz_akeebasubs_affpayments'); $query->where('akeebasubs_affiliate_id = ' . $record->akeebasubs_affiliate_id); $db->setQuery($query); $fieldvalue = $db->loadResult(); $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; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'total_commission': $query = $db->getQuery(true); $query->select('SUM(affiliate_comission) AS total_commision'); $query->from('#__akeebasubs_subscriptions'); $query->where('state = ' . $db->Quote('C')); $query->where('akeebasubs_affiliate_id = ' . $record->akeebasubs_affiliate_id); $query->group('akeebasubs_affiliate_id'); $db->setQuery($query); $fieldvalue = $db->loadResult(); $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; } $this->addExportField($field->combine, $fieldvalue, $fieldname, $field->column_header); break; case 'created_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; 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(); } } }
/** * Gift certificate 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'); // 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 'id': $userfields[] = $db->qn('gp') . '.' . $db->qn('id'); break; case 'product_id': case 'published': $userfields[] = $db->qn('gp') . '.' . $db->qn($field->field_name); break; case 'coupon_code': $userfields[] = $db->qn('gp') . '.' . $db->qn('coupon_template_id'); break; case 'profile_image': $userfields[] = $db->qn('gp') . '.' . $db->qn('profile_id'); break; case 'estore': $userfields[] = $db->qn('gp') . '.' . $db->qn('estore'); 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($db->qn('#__awocoupon_giftcert_product', 'gp')); $query->leftJoin($db->qn('#__awocoupon_giftcert_code', 'gc') . ' ON ' . $db->qn('gc.product_id') . '=' . $db->qn('gp.product_id')); $query->leftJoin($db->qn('#__virtuemart_products', 'p') . ' ON ' . $db->qn('p.virtuemart_product_id') . '=' . $db->qn('gp.product_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[] = $db->qn('gp.published') . ' = ' . $publish_state; } // Filter on product SKU $productskufilter = $template->get('product_sku', 'giftcertificate'); if ($productskufilter !== '') { $productskufilter .= ','; if (strpos($productskufilter, ',')) { $skus = explode(',', $productskufilter); $wildcard = ''; $normal = array(); foreach ($skus as $sku) { if (!empty($sku)) { if (strpos($sku, '%')) { $wildcard .= $db->qn('p.product_sku') . ' LIKE ' . $db->q($sku) . " OR "; } else { $normal[] = $db->q($sku); } } } if (substr($wildcard, -3) == 'OR ') { $wildcard = substr($wildcard, 0, -4); } if (!empty($wildcard) && !empty($normal)) { $selectors[] = '(' . $wildcard . ' OR ' . $db->qn('p.product_sku') . ' IN (' . implode(',', $normal) . '))'; } else { if (!empty($wildcard)) { $selectors[] = "(" . $wildcard . ")"; } else { if (!empty($normal)) { $selectors[] = '(' . $db->qn('p.product_sku') . ' IN (' . implode(',', $normal) . '))'; } } } } } // Filter on estore $estore = $template->get('estore', 'giftcertificate'); if ($estore !== '') { $selectors[] = $db->qn('gp.estore') . ' = ' . $db->q($estore); } // Filter on template $awotemplate = $template->get('template', 'giftcertificate'); if ($awotemplate !== '') { $selectors[] = $db->qn('gp.coupon_template_id') . ' = ' . $db->q($awotemplate); } // Filter on coupon value type $profile = $template->get('profile', 'giftcertificate'); if ($profile !== '') { $selectors[] = $db->qn('gp.profile_id') . ' = ' . $db->q($profile); } // 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('product_sku', 'coupon_code', 'profile_image'); // 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 'coupon_code': // Get all linked product SKUs $query = $db->getQuery(true)->select($db->qn('coupon_code'))->from($db->qn('#__awocoupon'))->where($db->qn('id') . ' = ' . $record->coupon_template_id); $db->setQuery($query); $code = $db->loadResult(); // Create the SKUs if (strlen(trim($code)) == 0) { $username = $field->default_value; } $code = CsviHelper::replaceValue($field->replace, $code); $record->output[$column_id] = $code; break; case 'profile_image': // Get all linked product SKUs $query = $db->getQuery(true)->select($db->qn('title'))->from($db->qn('#__awocoupon_profile'))->where($db->qn('id') . ' = ' . $record->profile_id); $db->setQuery($query); $code = $db->loadResult(); // Create the SKUs if (strlen(trim($code)) == 0) { $username = $field->default_value; } $code = CsviHelper::replaceValue($field->replace, $code); $record->output[$column_id] = $code; 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(); } } }
/** * 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(); } } }
/** * 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(); } } }
/** * 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'); // 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 'custom': 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('#__scrollerwithtabs_content'); // 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_vm.published = ' . $publish_state; } // 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(); // 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) { 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(); } } }
/** * Start the export * * @copyright * @author RolandD * @todo * @see * @access public * @param * @return void * @since 3.4 */ 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_K2(); // Build something fancy to only get the fieldnames the user wants $userfields = array(); foreach ($export_fields as $column_id => $field) { switch ($field->field_name) { case 'category_path': $userfields[] = $db->qn('i') . '.' . $db->qn('catid'); break; case 'image': $userfields[] = "CONCAT(MD5(CONCAT('Image', " . $db->qn('id') . ")), '.jpg') AS " . $db->qn('image'); 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($db->qn("#__k2_items", "i")); $selectors = array(); // Filter by published state $publish_state = $template->get('publish_state', 'general'); if ($publish_state != '' && ($publish_state == 1 || $publish_state == 0)) { $selectors[] = $db->qn('i.published') . ' = ' . $publish_state; } // Filter by language $language = $template->get('item_language', 'general'); if ($language != '*') { $selectors[] = $db->qn('i.language') . ' = ' . $db->q($language); } // Filter by category $categories = $template->get('item_categories', 'item'); if ($categories && $categories[0] != '') { $selectors[] = $db->qn('catid') . " IN ('" . implode("','", $categories) . "')"; } // 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('custom', 'category_path', 'image'); // 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; $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': $category_path = trim($helper->createCategoryPathById($record->catid)); 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 '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(); } } }