/**
* Import and Export data from/to csv
* @author Reto Kohli <*****@*****.**> (parts)
*/
function _import()
{
global $_ARRAYLANG, $objDatabase;
self::$pageTitle = $_ARRAYLANG['TXT_SHOP_IMPORT_TITLE'];
self::$objTemplate->loadTemplateFile('module_shop_import.html');
self::$objTemplate->setGlobalVariable(array('TXT_SHOP_IMPORT_CATEGORIES_TIPS' => contrexx_raw2xhtml($_ARRAYLANG['TXT_SHOP_IMPORT_CATEGORIES_TIPS']), 'TXT_SHOP_IMPORT_CHOOSE_TEMPLATE_TIPS' => contrexx_raw2xhtml($_ARRAYLANG['TXT_SHOP_IMPORT_CHOOSE_TEMPLATE_TIPS'])));
$objCSVimport = new CsvImport();
// Delete template
if (isset($_REQUEST['deleteImg'])) {
$query = "\n DELETE FROM " . DBPREFIX . "module_shop" . MODULE_INDEX . "_importimg\n WHERE img_id=" . $_REQUEST['img'];
if ($objDatabase->Execute($query)) {
\Message::ok($_ARRAYLANG['TXT_SHOP_IMPORT_SUCCESSFULLY_DELETED']);
} else {
\Message::error($_ARRAYLANG['TXT_SHOP_IMPORT_ERROR_DELETE']);
}
}
// Save template
if (isset($_REQUEST['SaveImg'])) {
$query = "\n INSERT INTO " . DBPREFIX . "module_shop" . MODULE_INDEX . "_importimg (\n img_name, img_cats, img_fields_file, img_fields_db\n ) VALUES (\n '" . $_REQUEST['ImgName'] . "',\n '" . $_REQUEST['category'] . "',\n '" . $_REQUEST['pairs_left_keys'] . "',\n '" . $_REQUEST['pairs_right_keys'] . "'\n )";
if ($objDatabase->Execute($query)) {
\Message::ok($_ARRAYLANG['TXT_SHOP_IMPORT_SUCCESSFULLY_SAVED']);
} else {
\Message::error($_ARRAYLANG['TXT_SHOP_IMPORT_ERROR_SAVE']);
}
}
$objCSVimport->initTemplateArray();
$fileExists = false;
$fileName = isset($_POST['csvFile']) ? contrexx_input2raw($_POST['csvFile']) : '';
$uploaderId = isset($_POST['importCsvUploaderId']) ? contrexx_input2raw($_POST['importCsvUploaderId']) : '';
if (!empty($fileName) && !empty($uploaderId)) {
$objSession = \cmsSession::getInstance();
$tmpFile = $objSession->getTempPath() . '/' . $uploaderId . '/' . $fileName;
$fileExists = \Cx\Lib\FileSystem\FileSystem::exists($tmpFile);
}
// Import Categories
// This is not subject to change, so it's hardcoded
if (isset($_REQUEST['ImportCategories']) && $fileExists) {
// delete existing categories on request only!
// mind that this necessarily also clears all products and
// their associated attributes!
if (!empty($_POST['clearCategories'])) {
Products::deleteByShopCategory(0, false, true);
ShopCategories::deleteAll();
// NOTE: Removing Attributes is now disabled. Optionally enable this.
// Attributes::deleteAll();
}
$objCsv = new CsvBv($tmpFile);
$importedLines = 0;
$arrCategoryLevel = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
$line = $objCsv->NextLine();
while ($line) {
$level = 0;
foreach ($line as $catName) {
++$level;
if (!empty($catName)) {
$parentCatId = $objCSVimport->getCategoryId($catName, $arrCategoryLevel[$level - 1]);
$arrCategoryLevel[$level] = $parentCatId;
}
}
++$importedLines;
$line = $objCsv->NextLine();
}
\Message::ok($_ARRAYLANG['TXT_SHOP_IMPORT_SUCCESSFULLY_IMPORTED_CATEGORIES'] . ': ' . $importedLines);
}
// Import
if (isset($_REQUEST['importFileProducts']) && $fileExists) {
if (isset($_POST['clearProducts']) && $_POST['clearProducts']) {
Products::deleteByShopCategory(0, false, true);
// The categories need not be removed, but it is done by design!
ShopCategories::deleteAll();
// NOTE: Removing Attributes is now disabled. Optionally enable this.
// Attributes::deleteAll();
}
$arrFileContent = $objCSVimport->GetFileContent($tmpFile);
$query = '
SELECT img_id, img_name, img_cats, img_fields_file, img_fields_db
FROM ' . DBPREFIX . 'module_shop' . MODULE_INDEX . '_importimg
WHERE img_id=' . $_REQUEST['ImportImage'];
$objResult = $objDatabase->Execute($query);
$arrCategoryName = preg_split('/;/', $objResult->fields['img_cats'], null, PREG_SPLIT_NO_EMPTY);
$arrFirstLine = $arrFileContent[0];
$arrCategoryColumnIndex = array();
for ($x = 0; $x < count($arrCategoryName); ++$x) {
foreach ($arrFirstLine as $index => $strColumnName) {
if ($strColumnName == $arrCategoryName[$x]) {
$arrCategoryColumnIndex[] = $index;
}
}
}
$arrTemplateFieldName = preg_split('/;/', $objResult->fields['img_fields_file'], null, PREG_SPLIT_NO_EMPTY);
$arrDatabaseFieldIndex = array();
for ($x = 0; $x < count($arrTemplateFieldName); ++$x) {
foreach ($arrFirstLine as $index => $strColumnName) {
if ($strColumnName == $arrTemplateFieldName[$x]) {
$arrDatabaseFieldIndex[] = $index;
}
}
}
$arrProductFieldName = preg_split('/;/', $objResult->fields['img_fields_db'], null, PREG_SPLIT_NO_EMPTY);
$arrProductDatabaseFieldName = array();
for ($x = 0; $x < count($arrProductFieldName); ++$x) {
$dbname = $objCSVimport->DBfieldsName($arrProductFieldName[$x]);
$arrProductDatabaseFieldName[$dbname] = (isset($arrProductDatabaseFieldName[$dbname]) ? $arrProductDatabaseFieldName[$dbname] . ';' : '') . $x;
}
$importedLines = 0;
$errorLines = 0;
// Array of IDs of newly inserted records
$arrId = array();
for ($x = 1; $x < count($arrFileContent); ++$x) {
$category_id = false;
for ($cat = 0; $cat < count($arrCategoryColumnIndex); ++$cat) {
$catName = $arrFileContent[$x][$arrCategoryColumnIndex[$cat]];
if (empty($catName) && !empty($category_id)) {
break;
}
if (empty($catName)) {
$category_id = $objCSVimport->GetFirstCat();
} else {
$category_id = $objCSVimport->getCategoryId($catName, $category_id);
}
}
if ($category_id == 0) {
$category_id = $objCSVimport->GetFirstCat();
}
$objProduct = new Product('', $category_id, '', Distribution::TYPE_DELIVERY, 0, 1, 0, 0, 0);
foreach ($arrProductDatabaseFieldName as $index => $strFieldIndex) {
$value = '';
if (strpos($strFieldIndex, ';')) {
$prod2line = explode(';', $strFieldIndex);
for ($z = 0; $z < count($prod2line); ++$z) {
$value .= $arrFileContent[$x][$arrDatabaseFieldIndex[$prod2line[$z]]] . '<br />';
}
} else {
$value = $arrFileContent[$x][$arrDatabaseFieldIndex[$strFieldIndex]];
}
$objProduct->{$index}($value);
}
if ($objProduct->store()) {
$arrId[] = $objProduct->id();
++$importedLines;
} else {
++$errorLines;
}
}
// Fix picture field and create thumbnails
Products::makeThumbnailsById($arrId);
if ($importedLines) {
\Message::ok($_ARRAYLANG['TXT_SHOP_IMPORT_SUCCESSFULLY_IMPORTED_PRODUCTS'] . ': ' . $importedLines);
}
if ($errorLines) {
\Message::error($_ARRAYLANG['TXT_SHOP_IMPORT_NOT_SUCCESSFULLY_IMPORTED_PRODUCTS'] . ': ' . $errorLines);
}
}
// end import
$jsnofiles = '';
$fileFields = $dblist = null;
$arrTemplateArray = $objCSVimport->getTemplateArray();
if (isset($_REQUEST['mode']) && $_REQUEST['mode'] != 'ImportImg') {
if (count($arrTemplateArray) == 0) {
self::$objTemplate->hideBlock('import_products');
self::$objTemplate->touchBlock('import_products_no_template');
} else {
$imageChoice = $objCSVimport->GetImageChoice();
self::$objTemplate->setVariable(array('IMAGE_CHOICE' => $imageChoice));
}
} else {
if (!isset($_REQUEST['SelectFields'])) {
$jsnofiles = "selectTab('import1');";
} else {
if (isset($_POST['mode']) && $_POST['csvFile'] == '') {
$jsnofiles = "selectTab('import4');";
} else {
$jsnofiles = "selectTab('import2');";
if ($fileExists) {
$fileFields = '
<select name="FileFields" id="file_field" style="width: 200px;" size="10">
' . $objCSVimport->getFilefieldMenuOptions($tmpFile) . '
</select>' . "\n";
}
$dblist = '
<select name="DbFields" id="given_field" style="width: 200px;" size="10">
' . $objCSVimport->getAvailableNamesMenuOptions() . '
</select>' . "\n";
}
}
}
$jsSelectLayer = 'selectTab("import1");';
if (isset($_REQUEST['mode']) && $_REQUEST['mode'] == 'ImportImg') {
$jsSelectLayer = 'selectTab("import2");';
}
$arrTemplateArray = $objCSVimport->getTemplateArray();
if ($arrTemplateArray) {
$arrName = $objCSVimport->getNameArray();
self::$objTemplate->setVariable('SHOP_IMPORT_TEMPLATE_MENU', \Html::getSelect('ImportImage', $arrName));
} else {
self::$objTemplate->touchBlock('import_products_no_template');
}
for ($x = 0; $x < count($arrTemplateArray); ++$x) {
self::$objTemplate->setVariable(array('IMG_NAME' => $arrTemplateArray[$x]['name'], 'IMG_ID' => $arrTemplateArray[$x]['id'], 'CLASS_NAME' => 'row' . ($x % 2 + 1), 'SHOP_CMS_OFFSET' => \Cx\Core\Core\Controller\Cx::instanciate()->getWebsiteOffsetPath()));
self::$objTemplate->parse('imgRow');
}
//initialize the uploader
$uploader = new \Cx\Core_Modules\Uploader\Model\Entity\Uploader();
//create an uploader
$uploader->setCallback('importUploaderCallback');
$uploader->setOptions(array('id' => 'importCsvUploader', 'allowed-extensions' => array('csv', 'xls'), 'data-upload-limit' => 1, 'style' => 'display:none'));
self::$objTemplate->setVariable(array('SELECT_LAYER_ONLOAD' => $jsSelectLayer, 'NO_FILES' => isset($jsnofiles) ? $jsnofiles : '', 'FILE_FIELDS_LIST' => isset($fileFields) ? $fileFields : '', 'DB_FIELDS_LIST' => isset($dblist) ? $dblist : '', 'SHOP_IMPORT_CSV_UPLOADER_CODE' => $uploader->getXHtml(), 'SHOP_IMPORT_CSV_UPLOADER_ID' => $uploader->getId()));
// TODO: !!! CSV EXPORT IS OBSOLETE AND DYSFUNCT !!!
/*
// Export groups -- hardcoded
$content_location = '';
if (isset($_REQUEST['group'])) {
$query = $fieldNames = $content_location = '';
$arrPictures = null;
switch ($_REQUEST['group']) {
// products - plain fields:
case 'tproduct':
$content_location = "ProdukteTabelle.csv";
$fieldNames = array(
'id', 'product_id', 'picture', 'title', 'catid', 'distribution',
'normalprice', 'resellerprice', 'short', 'long',
'stock', 'stock_visible', 'discountprice', 'discount_active',
'active', 'b2b', 'b2c', 'date_start', 'date_end',
'manufacturer', 'manufacturer_url', 'external_link',
'ord', 'vat_id', 'weight',
'flags', 'group_id', 'article_id', 'keywords', );
$query = "
SELECT id, product_id, picture, title, catid, distribution,
normalprice, resellerprice, short, long,
stock, stock_visible, discountprice, discount_active,
active, b2b, b2c, date_start, date_end,
manufacturer, manufacturer_url, external_link,
sort_order, vat_id, weight,
flags, group_id, article_id, keywords
FROM ".DBPREFIX."module_shop_products
ORDER BY id ASC";
break;
// products - custom:
case 'rproduct':
$content_location = "ProdukteRelationen.csv";
$fieldNames = array(
'id', 'product_id', 'picture', 'title',
'catid', 'category', 'parentcategory', 'distribution',
'normalprice', 'resellerprice', 'discountprice', 'discount_active',
'short', 'long',
'stock', 'stock_visible',
'active', 'b2b', 'b2c',
'date_start', 'date_end',
'manufacturer_name', 'manufacturer_website',
'manufacturer_url', 'external_link',
'ord',
'vat_percent', 'weight',
'discount_group', 'article_group', 'keywords', );
// c1.catid *MUST NOT* be NULL
// c2.catid *MAY* be NULL (if c1.catid is root)
// vat_id *MAY* be NULL
$query = "
SELECT p.id, p.product_id, p.picture, p.title,
p.catid, c1.catname as category, c2.catname as parentcategory, p.distribution,
p.normalprice, p.resellerprice, p.discountprice, p.discount_active,
p.short, p.long, p.stock, p.stock_visible,
p.active, p.b2b, p.b2c, p.date_start, p.date_end,
m.name as manufacturer_name,
m.url as manufacturer_website,
p.manufacturer_url, p.external_link,
p.ord,
v.percent as vat_percent, p.weight,
d.name AS discount_group,
a.name AS article_group,
p.keywords
FROM ".DBPREFIX."module_shop_products p
INNER JOIN ".DBPREFIX."module_shop_categories c1 ON p.catid=c1.catid
LEFT JOIN ".DBPREFIX."module_shop_categories c2 ON c1.parentid=c2.catid
LEFT JOIN ".DBPREFIX."module_shop_vat v ON vat_id=v.id
LEFT JOIN ".DBPREFIX."module_shop_manufacturer as m ON m.id = p.manufacturer
LEFT JOIN ".DBPREFIX."module_shop_discountgroup_count_name as d ON d.id = p.group_id
LEFT JOIN ".DBPREFIX."module_shop_article_group as a ON a.id = p.article_id
ORDER BY catid ASC, product_id ASC";
break;
// customer - plain fields:
// TODO: Use Customer class!
case 'tcustomer':
$content_location = "KundenTabelle.csv";
$fieldNames = array(
'customerid', 'username', 'password', 'prefix', 'company', 'firstname', 'lastname',
'address', 'city', 'zip', 'country_id', 'phone', 'fax', 'email',
'ccnumber', 'ccdate', 'ccname', 'cvc_code', 'company_note',
'is_reseller', 'register_date', 'customer_status', 'group_id', );
$query = "
SELECT customerid, username, password, prefix, company, firstname, lastname,
address, city, zip, country_id, phone, fax, email,
ccnumber, ccdate, ccname, cvc_code, company_note,
is_reseller, register_date, customer_status,
group_id
FROM ".DBPREFIX."module_shop_customers
ORDER BY lastname ASC, firstname ASC";
break;
// customer - custom:
// TODO: Use Customer class!
case 'rcustomer':
$content_location = "KundenRelationen.csv";
$fieldNames = array(
'customerid', 'username', 'firstname', 'lastname', 'prefix', 'company',
'address', 'zip', 'city', 'countries_name',
'phone', 'fax', 'email', 'is_reseller', 'register_date', 'group_name', );
$query = "
SELECT c.customerid, c.username, c.firstname, c.lastname, c.prefix, c.company,
c.address, c.zip, c.city, n.countries_name,
c.phone, c.fax, c.email, c.is_reseller, c.register_date,
d.name AS group_name
FROM ".DBPREFIX."module_shop_customers c
INNER JOIN ".DBPREFIX."module_shop_countries n ON c.country_id=n.countries_id
LEFT JOIN ".DBPREFIX."module_shop_customer_group d ON c.group_id=d.id
ORDER BY c.lastname ASC, c.firstname ASC";
break;
// orders - plain fields:
case 'torder':
$content_location = "BestellungenTabelle.csv";
$fieldNames = array(
'id', 'customer_id', 'currency_id', 'order_sum', 'sum',
'date_time', 'status', 'ship_prefix', 'ship_company', 'ship_firstname', 'ship_lastname',
'ship_address', 'ship_city', 'ship_zip', 'ship_country_id', 'ship_phone',
'vat_amount', 'currency_ship_price', 'shipment_id', 'payment_id', 'currency_payment_price',
'ip', 'host', 'lang_id', 'browser', 'note',
'last_modified', 'modified_by');
$query = "
SELECT id, customer_id, currency_id, order_sum, sum,
date_time, status, ship_prefix, ship_company, ship_firstname, ship_lastname,
ship_address, ship_city, ship_zip, ship_country_id, ship_phone,
vat_amount, currency_ship_price, shipment_id, payment_id, currency_payment_price,
ip, host, lang_id, browser, note,
last_modified, modified_by
FROM ".DBPREFIX."module_shop".MODULE_INDEX."_orders
ORDER BY id ASC";
break;
// orders - custom:
case 'rorder':
// TODO: Use Customer class!
$content_location = "BestellungenRelationen.csv";
$fieldNames = array(
'id', 'order_sum', 'vat_amount', 'currency_ship_price', 'currency_payment_price',
'sum', 'date_time', 'status', 'ship_prefix', 'ship_company',
'ship_firstname', 'ship_lastname', 'ship_address', 'ship_city', 'ship_zip',
'ship_phone', 'note',
'customer_id', 'username', 'firstname', 'lastname', 'prefix', 'company',
'address', 'zip', 'city', 'countries_name',
'phone', 'fax', 'email', 'is_reseller', 'register_date',
'currency_code', 'shipper_name', 'payment_name',
'account_number', 'bank_name', 'bank_code');
$query = "
SELECT o.id, o.order_sum, o.vat_amount, o.currency_ship_price, o.currency_payment_price,
o.sum, o.date_time, o.status, o.ship_prefix, o.ship_company,
o.ship_firstname, o.ship_lastname, o.ship_address, o.ship_city, o.ship_zip,
o.ship_phone, o.note,
o.customer_id,
c.username, c.firstname, c.lastname, c.prefix, c.company,
c.address, c.zip, c.city, n.countries_name,
c.phone, c.fax, c.email, c.is_reseller, c.register_date,
u.code AS currency_code, s.name AS shipper_name, p.name AS payment_name,
l.holder, l.bank, l.blz
FROM ".DBPREFIX."module_shop_orders o
INNER JOIN ".DBPREFIX."module_shop_customers c ON o.customer_id=c.customerid
INNER JOIN ".DBPREFIX."module_shop_countries n ON c.country_id=n.countries_id
INNER JOIN ".DBPREFIX."module_shop_currencies u ON o.currency_id=u.id
LEFT JOIN ".DBPREFIX."module_shop_shipper s ON o.shipment_id=s.id
LEFT JOIN ".DBPREFIX."module_shop_payment p ON o.payment_id=p.id
LEFT JOIN ".DBPREFIX."module_shop_lsv l ON o.id=l.order_id
ORDER BY o.id ASC";
break;
} // switch
if ($query && $objResult = $objDatabase->Execute($query)) {
// field names
$fileContent = '"'.join('";"', $fieldNames)."\"\n";
while (!$objResult->EOF) {
$arrRow = $objResult->FetchRow();
$arrReplaced = array();
// Decode the pictures
foreach ($arrRow as $index => $field) {
if ($index == 'picture') {
$arrPictures = Products::get_image_array_from_base64($field);
$field =
'http://'.
$_SERVER['HTTP_HOST'].'/'.
ASCMS_SHOP_IMAGES_WEB_PATH.'/'.
$arrPictures[1]['img'];
}
$arrReplaced[] = str_replace('"', '""', $field);
}
$fileContent .= '"'.join('";"', $arrReplaced)."\"\n";
}
// Test the output for UTF8!
if (strtoupper(CONTREXX_CHARSET) == 'UTF-8') {
$fileContent = utf8_decode($fileContent);
}
// TODO: Add success message?
// set content to filename and -type for download
header("Content-Disposition: inline; filename=$content_location");
header("Content-Type: text/comma-separated-values");
echo($fileContent);
exit();
}
\Message::error($_ARRAYLANG['TXT_SHOP_EXPORT_ERROR']);
} else {
// can't submit without a group selection
} // if/else group
// end export
// make sure that language entries exist for all of
// TXT_SHOP_EXPORT_GROUP_*, TXT_SHOP_EXPORT_GROUP_*_TIP !!
$arrGroups = array('tproduct', 'rproduct', 'tcustomer', 'rcustomer', 'torder', 'rorder');
$tipText = '';
for ($i = 0; $i < count($arrGroups); ++$i) {
self::$objTemplate->setCurrentBlock('groupRow');
self::$objTemplate->setVariable(array(
'SHOP_EXPORT_GROUP' => $_ARRAYLANG['TXT_SHOP_EXPORT_GROUP_'.strtoupper($arrGroups[$i])],
'SHOP_EXPORT_GROUP_CODE' => $arrGroups[$i],
'SHOP_EXPORT_INDEX' => $i,
'CLASS_NAME' => 'row'.($i % 2 + 1),
));
self::$objTemplate->parse('groupRow');
$tipText .= 'Text['.$i.']=["","'.$_ARRAYLANG['TXT_SHOP_EXPORT_GROUP_'.strtoupper($arrGroups[$i]).'_TIP'].'"];';
}
*/
}
function GetFileContent()
{
$csv_source = new CsvBv($_FILES['importfile']['tmp_name'], CsvImport::$separator, CsvImport::$delimiter, CsvImport::$escapor);
$csv_source->SkipEmptyRows(true);
$csv_source->TrimFields(true);
$FileContent = $csv_source->csv2Array();
return $FileContent;
}
/**
* Get the file content from the csv file
*
* @param string $file file name with path
*
* @return array
*/
function GetFileContent($file)
{
$csv_source = new CsvBv($file, CsvImport::$separator, CsvImport::$delimiter, CsvImport::$escapor);
$csv_source->SkipEmptyRows(true);
$csv_source->TrimFields(true);
$FileContent = $csv_source->csv2Array();
return $FileContent;
}