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