function buildProductUploadXML($id, $inc_image = true)
{
global $db, $currencies, $messageStack;
$result = $db->Execute("select * from " . TABLE_INVENTORY . " where id = " . $id);
if ($result->RecordCount() != 1) {
$messageStack->add(OSCOMMERCE_INVALID_SKU, 'error');
return false;
}
$this->sku = $result->fields['sku'];
if (OSCOMMERCE_USE_PRICE_SHEETS == '1') {
$sql = "select id, default_levels from " . TABLE_PRICE_SHEETS . " \r\n\t\twhere '" . date('Y-m-d', time()) . "' >= effective_date \r\n\t\tand sheet_name = '" . OSCOMMERCE_PRICE_SHEET . "' and inactive = '0'";
$default_levels = $db->Execute($sql);
if ($default_levels->RecordCount() == 0) {
$messageStack->add(OSCOMMERCE_ERROR_NO_PRICE_SHEET . OSCOMMERCE_PRICE_SHEET, 'error');
return false;
}
$sql = "select price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " \r\n\t\twhere inventory_id = " . $id . " and price_sheet_id = " . $default_levels->fields['id'];
$special_levels = $db->Execute($sql);
if ($special_levels->RecordCount() > 0) {
$price_levels = $special_levels->fields['price_levels'];
} else {
$price_levels = $default_levels->fields['default_levels'];
}
}
// prepare some information before assembling string
if ($inc_image && $result->fields['image_with_path']) {
// image file
$filename = DIR_FS_MY_FILES . $_SESSION['company'] . '/inventory/images/' . $result->fields['image_with_path'];
if (file_exists($filename)) {
if ($handle = fopen($filename, "rb")) {
$contents = fread($handle, filesize($filename));
fclose($handle);
// osCommerce only support one level, so we'll use the first path dir and filename only
$temp = explode('/', $result->fields['image_with_path']);
if (sizeof($temp) > 1) {
$image_path = $temp[0];
$image_filename = array_pop($temp);
} else {
$image_path = '';
$image_filename = $temp[0];
}
$image_data = base64_encode($contents);
}
} else {
unset($image_data);
}
}
// url encode all of the values to avoid upload bugs
foreach ($result->fields as $key => $value) {
$result->fields[$key] = urlencode($result->fields[$key]);
}
$this->strXML = '<?xml version="1.0" encoding="iso-8859-1" ?>' . chr(10);
$this->strXML .= '<AccessRequest>' . chr(10);
$this->strXML .= '<AccessUserID>' . OSCOMMERCE_USERNAME . '</AccessUserID>' . chr(10);
$this->strXML .= '<AccessPassword>' . OSCOMMERCE_PASSWORD . '</AccessPassword>' . chr(10);
$this->strXML .= '<AccessFunction>ProductUpload</AccessFunction>' . chr(10);
$this->strXML .= '<Version>1.00</Version>' . chr(10);
$this->strXML .= '<ProductUpload>' . chr(10);
$this->strXML .= '<RequestHeader>' . chr(10);
$this->strXML .= '<RequestAction>InsertUpdate</RequestAction>' . chr(10);
$this->strXML .= '<ReferenceName>Product Upload sku: ' . $result->fields['sku'] . '</ReferenceName>' . chr(10);
$this->strXML .= '<Originator>' . chr(10);
// $this->strXML .= '<StoreID>' . OSCOMMERCE_STORE_ID . '</StoreID>' . chr(10);
// $this->strXML .= '<SalesRepID>' . OSCOMMERCE_SALES_REP_ID . '</SalesRepID>' . chr(10);
$this->strXML .= '<Language>' . $_SESSION['language'] . '</Language>' . chr(10);
$this->strXML .= '</Originator>' . chr(10);
$this->strXML .= '</RequestHeader>' . chr(10);
$this->strXML .= '<ProductInformation>' . chr(10);
$this->strXML .= '<SKU>' . $result->fields['sku'] . '</SKU>' . chr(10);
// Specific to osCommerce
$this->strXML .= '<ProductVirtual>0</ProductVirtual>' . chr(10);
$this->strXML .= '<ProductStatus>' . ($result->fields['inactive'] ? '0' : '1') . '</ProductStatus>' . chr(10);
$this->strXML .= '<ProductFreeShipping>0</ProductFreeShipping>' . chr(10);
$this->strXML .= '<ProductHidePrice>0</ProductHidePrice>' . chr(10);
$this->strXML .= '<ProductCategory>' . $result->fields['category_id'] . '</ProductCategory>' . chr(10);
$this->strXML .= '<ProductSortOrder>' . $result->fields['id'] . '</ProductSortOrder>' . chr(10);
// End specific to osCommerce
// TBD need to map ProductType
$this->strXML .= '<ProductType>Product - General</ProductType>' . chr(10);
$this->strXML .= '<ProductName>' . $result->fields['description_short'] . '</ProductName>' . chr(10);
$this->strXML .= '<ProductModel>' . $result->fields['description_short'] . '</ProductModel>' . chr(10);
$this->strXML .= '<ProductDescription>' . $result->fields['description_sales'] . '</ProductDescription>' . chr(10);
$this->strXML .= '<ProductURL>' . $result->fields['spec_file'] . '</ProductURL>' . chr(10);
if (isset($image_data)) {
$this->strXML .= '<ProductImageDirectory>' . $image_path . '</ProductImageDirectory>' . chr(10);
$this->strXML .= '<ProductImageFileName>' . $image_filename . '</ProductImageFileName>' . chr(10);
$this->strXML .= '<ProductImageData>' . $image_data . '</ProductImageData>' . chr(10);
}
// TBD need mappping for TaxClassType
$this->strXML .= '<ProductTaxable>' . ($result->fields['item_taxable'] ? 'True' : 'False') . '</ProductTaxable>' . chr(10);
$this->strXML .= '<TaxClassType>' . OSCOMMERCE_PRODUCT_TAX_CLASS . '</TaxClassType>' . chr(10);
// Price Levels
$this->strXML .= '<ProductPrice>' . chr(10);
$this->strXML .= '<MSRPrice>' . $result->fields['full_price'] . '</MSRPrice>' . chr(10);
$this->strXML .= '<RetailPrice>' . $result->fields['full_price'] . '</RetailPrice>' . chr(10);
if (OSCOMMERCE_USE_PRICE_SHEETS) {
$this->strXML .= '<PriceDiscounts>' . chr(10);
$this->strXML .= '<PriceDiscountType>2</PriceDiscountType>' . chr(10);
// set to actual price type
$prices = inv_calculate_prices($result->fields['item_cost'], $result->fields['full_price'], $price_levels);
foreach ($prices as $level => $amount) {
$this->strXML .= '<PriceLevel>' . chr(10);
$this->strXML .= '<DiscountLevel>' . ($level + 1) . '</DiscountLevel>' . chr(10);
$this->strXML .= '<Quantity>' . $amount['qty'] . '</Quantity>' . chr(10);
$this->strXML .= '<Amount>' . $currencies->clean_value($amount['price']) . '</Amount>' . chr(10);
$this->strXML .= '</PriceLevel>' . chr(10);
}
$this->strXML .= '</PriceDiscounts>' . chr(10);
} else {
$this->strXML .= '<PriceDiscounts>' . chr(10);
$this->strXML .= '<PriceDiscountType>0</PriceDiscountType>' . chr(10);
// clear qty discount flag
$this->strXML .= '</PriceDiscounts>' . chr(10);
}
$this->strXML .= '</ProductPrice>' . chr(10);
$this->strXML .= '<ProductWeight>' . $result->fields['item_weight'] . '</ProductWeight>' . chr(10);
$this->strXML .= '<DateAdded>' . $result->fields['creation_date'] . '</DateAdded>' . chr(10);
$this->strXML .= '<DateUpdated>' . $result->fields['last_update'] . '</DateUpdated>' . chr(10);
// $this->strXML .= '<DateAvailable>' . $result->fields['creation_date'] . '</DateAvailable>' . chr(10);
$this->strXML .= '<StockLevel>' . $result->fields['quantity_on_hand'] . '</StockLevel>' . chr(10);
$this->strXML .= '<Manufacturer>' . $result->fields['manufacturer'] . '</Manufacturer>' . chr(10);
// Hook for including customiation of product attributes
if (file_exists(DIR_FS_MY_FILES . 'custom/oscommerce/extra_actions/extra_product_attrs.php')) {
include DIR_FS_MY_FILES . 'custom/oscommerce/extra_actions/extra_product_attrs.php';
}
// EOF _ Hook for customization
$this->strXML .= '</ProductInformation>' . chr(10);
$this->strXML .= '</ProductUpload>' . chr(10);
$this->strXML .= '</AccessRequest>';
return true;
}
function buildProductUploadXML($id, $inc_image = true)
{
global $db, $currencies, $messageStack;
$result = $db->Execute("select * from " . TABLE_INVENTORY . " where id = " . $id);
if ($result->RecordCount() != 1) {
$messageStack->add(ZENCART_INVALID_SKU, 'error');
return false;
}
$this->sku = $result->fields['sku'];
if (ZENCART_USE_PRICE_SHEETS == '1') {
$sql = "select id, default_levels from " . TABLE_PRICE_SHEETS . " \n\t\twhere '" . date('Y-m-d', time()) . "' >= effective_date \n\t\tand sheet_name = '" . ZENCART_PRICE_SHEET . "' and inactive = '0'";
$default_levels = $db->Execute($sql);
if ($default_levels->RecordCount() == 0) {
$messageStack->add(ZENCART_ERROR_NO_PRICE_SHEET . ZENCART_PRICE_SHEET, 'error');
return false;
}
$sql = "select price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " \n\t\twhere inventory_id = " . $id . " and price_sheet_id = " . $default_levels->fields['id'];
$special_levels = $db->Execute($sql);
if ($special_levels->RecordCount() > 0) {
$price_levels = $special_levels->fields['price_levels'];
} else {
$price_levels = $default_levels->fields['default_levels'];
}
}
// prepare some information before assembling string
if ($result->fields['image_with_path']) {
// image file
// Zencart only support one level, so we'll use the first path dir and filename only
$temp = explode('/', $result->fields['image_with_path']);
if (sizeof($temp) > 1) {
$image_path = $temp[0];
$image_filename = array_pop($temp);
} else {
$image_path = '';
$image_filename = $temp[0];
}
}
if ($inc_image && $result->fields['image_with_path']) {
// image file
$filename = DIR_FS_MY_FILES . $_SESSION['company'] . '/inventory/images/' . $result->fields['image_with_path'];
if (file_exists($filename)) {
if ($handle = fopen($filename, "rb")) {
$contents = fread($handle, filesize($filename));
fclose($handle);
$image_data = base64_encode($contents);
}
} else {
unset($image_data);
}
}
// url encode all of the values to avoid upload bugs
foreach ($result->fields as $key => $value) {
$result->fields[$key] = urlencode($result->fields[$key]);
}
$this->strXML = '<?xml version="1.0" encoding="UTF-8" ?>' . chr(10);
$this->strXML .= '<Request>' . chr(10);
$this->strXML .= xmlEntry('Version', '2.00');
$this->strXML .= xmlEntry('UserName', ZENCART_USERNAME);
$this->strXML .= xmlEntry('UserPassword', ZENCART_PASSWORD);
$this->strXML .= xmlEntry('Language', $_SESSION['language']);
$this->strXML .= xmlEntry('Operation', 'ProductUpload');
$this->strXML .= xmlEntry('Action', 'InsertUpdate');
$this->strXML .= xmlEntry('Reference', 'Product Upload SKU: ' . $result->fields['sku']);
$this->strXML .= '<Product>' . chr(10);
$this->strXML .= xmlEntry('SKU', $result->fields['sku']);
// Specific to Zencart
$this->strXML .= xmlEntry('ProductVirtual', '0');
$this->strXML .= xmlEntry('ProductStatus', $result->fields['inactive'] ? '0' : '1');
$this->strXML .= xmlEntry('ProductFreeShipping', '0');
$this->strXML .= xmlEntry('ProductHidePrice', '0');
$this->strXML .= xmlEntry('ProductCategory', $result->fields['category_id']);
$this->strXML .= xmlEntry('ProductSortOrder', $result->fields['id']);
// End specific to Zencart
// TBD need to map ProductType
$this->strXML .= xmlEntry('ProductType', 'Product - General');
$this->strXML .= xmlEntry('ProductName', $result->fields['description_short']);
$this->strXML .= xmlEntry('ProductDescription', $result->fields['description_sales']);
if (isset($result->fields['ProductURL'])) {
$this->strXML .= xmlEntry('ProductURL', $result->fields['ProductURL']);
}
if (isset($result->fields['ProductModel'])) {
$this->strXML .= xmlEntry('ProductModel', $result->fields['ProductModel']);
}
if (isset($image_data)) {
$this->strXML .= xmlEntry('ProductImageDirectory', $image_path);
$this->strXML .= xmlEntry('ProductImageFileName', $image_filename);
$this->strXML .= xmlEntry('ProductImageData', $image_data);
}
// TBD need mappping for TaxClassType
$this->strXML .= xmlEntry('ProductTaxable', $result->fields['item_taxable'] ? 'True' : 'False');
$this->strXML .= xmlEntry('TaxClassType', ZENCART_PRODUCT_TAX_CLASS);
// Price Levels
$this->strXML .= ' <ProductPrice>' . chr(10);
$this->strXML .= xmlEntry('MSRPrice', $result->fields['full_price']);
$this->strXML .= xmlEntry('RetailPrice', $result->fields['full_price']);
if (ZENCART_USE_PRICE_SHEETS) {
$this->strXML .= ' <PriceDiscounts>' . chr(10);
$this->strXML .= xmlEntry('PriceDiscountType', '2');
// set to actual price type
$prices = inv_calculate_prices($result->fields['item_cost'], $result->fields['full_price'], $price_levels);
foreach ($prices as $level => $amount) {
$this->strXML .= ' <PriceLevel>' . chr(10);
$this->strXML .= xmlEntry('DiscountLevel', $level + 1);
$this->strXML .= xmlEntry('Quantity', $amount['qty']);
$this->strXML .= xmlEntry('Amount', $currencies->clean_value($amount['price']));
$this->strXML .= ' </PriceLevel>' . chr(10);
}
$this->strXML .= ' </PriceDiscounts>' . chr(10);
} else {
$this->strXML .= ' <PriceDiscounts>' . chr(10);
$this->strXML .= xmlEntry('PriceDiscountType', '0');
// clear qty discount flag
$this->strXML .= ' </PriceDiscounts>' . chr(10);
}
$this->strXML .= ' </ProductPrice>' . chr(10);
$this->strXML .= xmlEntry('ProductWeight', $result->fields['item_weight']);
$this->strXML .= xmlEntry('DateAdded', $result->fields['creation_date']);
if ($result->fields['last_update'] < $result->fields['last_journal_date']) {
$this->strXML .= xmlEntry('DateUpdated', $result->fields['last_journal_date']);
} else {
$this->strXML .= xmlEntry('DateUpdated', $result->fields['last_update']);
}
$this->strXML .= xmlEntry('StockLevel', $result->fields['quantity_on_hand']);
$this->strXML .= xmlEntry('Manufacturer', $result->fields['manufacturer']);
// Hook for including customiation of product attributes
if (file_exists(DIR_FS_MODULES . 'zencart/custom/extra_product_attrs.php')) {
include DIR_FS_MODULES . 'zencart/custom/extra_product_attrs.php';
}
// EOF _ Hook for customization
$this->strXML .= '</Product>' . chr(10);
$this->strXML .= '</Request>' . chr(10);
return true;
}
if (is_object($price_sheets)) {
if ($price_sheets->RecordCount() > 0) {
$cnt = 0;
while (!$price_sheets->EOF) {
echo '<table class="ui-widget" style="border-collapse:collapse;width:300px">';
echo '<thead class="ui-widget-header">';
echo '<tr><th colspan="2">' . PRICE_SHEETS_LOG . $price_sheets->fields['sheet_name'] . '</th></tr>';
echo '<tr><th>' . TEXT_QUANTITY . '</th><th align="center">' . TEXT_PRICE . '</th></tr>';
echo ' </thead><tbody class="ui-widget-content">' . chr(10);
// remove the first and last element from the price level source array (not used and Level 1 price source)
$first_source_list = $price_mgr_sources;
array_shift($first_source_list);
array_pop($first_source_list);
// extract the pricing information
$levels = isset($special_prices[$price_sheets->fields['id']]) ? $special_prices[$price_sheets->fields['id']] : $price_sheets->fields['default_levels'];
$prices = inv_calculate_prices($inventory_details->fields['item_cost'], $inventory_details->fields['full_price'], $levels);
foreach ($prices as $price_level) {
echo '<tr onclick="setReturnPrice(\'' . $rowId . '\', \'price_' . $cnt . '\')">';
echo '<td style="cursor:pointer" align="center">' . $price_level['qty'] . '</td>';
echo '<td style="cursor:pointer" align="right">' . html_input_field('price_' . $cnt, $price_level['price'], 'style="cursor:pointer" readonly="readonly" size="10" style="text-align:right"') . '</td></tr>';
$cnt++;
}
$price_sheets->MoveNext();
echo '</tbody></table>';
}
echo html_hidden_field('num_prices', $cnt);
}
} else {
echo '<div align="center"><p>' . INV_NO_PRICE_SHEETS . '</p></div>';
}
// end if ($price_sheets->RecordCount() > 0)
function inv_calculate_sales_price($qty, $sku_id, $contact_id = '')
{
global $db, $currencies;
// get the inventory prices
$inventory = $db->Execute("select item_cost, full_price, price_sheet from " . TABLE_INVENTORY . " where id = '" . $sku_id . "'");
// determine what price to charge based on customer and sku information
if ($inventory->fields['price_sheet'] != '') {
// if inventory price sheet is defined, it has priority
$sheet_name = $inventory->fields['price_sheet'];
} else {
$customer = $db->Execute("select price_sheet from " . TABLE_CONTACTS . " where id = '" . $contact_id . "'");
if ($customer->fields['price_sheet'] != '') {
// no price sheet used for this customer, check for default
$sheet_name = $customer->fields['price_sheet'];
} else {
$customer = $db->Execute("select sheet_name from " . TABLE_PRICE_SHEETS . " where default_sheet = '1'");
$sheet_name = $customer->RecordCount() == 0 ? '' : $customer->fields['sheet_name'];
}
}
// determine the sku price ranges from the price sheet in effect
$sql = "select id, default_levels from " . TABLE_PRICE_SHEETS . " \r\n\t\twhere inactive = '0' and \r\n\t\t(expiration_date is null or expiration_date = '0000-00-00' or expiration_date >= '" . date('Y-m-d', time()) . "') \r\n\t\tand sheet_name = '" . $sheet_name . "'";
$price_sheets = $db->Execute($sql);
// retrieve special pricing for this inventory item
$sql = "select price_sheet_id, price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " \r\n\t\twhere price_sheet_id = '" . $price_sheets->fields['id'] . "' and inventory_id = " . $sku_id;
$result = $db->Execute($sql);
$special_prices = array();
while (!$result->EOF) {
$special_prices[$result->fields['price_sheet_id']] = $result->fields['price_levels'];
$result->MoveNext();
}
$levels = isset($special_prices[$price_sheets->fields['id']]) ? $special_prices[$price_sheets->fields['id']] : $price_sheets->fields['default_levels'];
$prices = inv_calculate_prices($inventory->fields['item_cost'], $inventory->fields['full_price'], $levels);
$price = '0.0';
foreach ($prices as $value) {
if ($qty >= $value['qty']) {
$price = $currencies->clean_value($value['price']);
}
}
return $price;
}
function dumpAmazon()
{
global $db, $messageStack;
$separator = "\t";
$file_name = "amazon_upload.txt";
// $separator = ",";
// $file_name = "amazon_upload.csv";
$result = $db->Execute("SELECT * FROM " . TABLE_INVENTORY . " WHERE amazon='1' AND inactive='0'");
$line = array();
// $output = "TemplateType=ConsumerElectronics,Version=1.01,This row for Amazon.com use only. Do not modify or delete.,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Rebates,,,,,,,,PC,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,PDA,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Misc Consumer Electronics";
$output = "TemplateType=ConsumerElectronics,Version=1.01,This row for Amazon.com use only. Do not modify or delete.";
$line[] = str_replace(",", $separator, $output);
// $output = "sku,standard-product-id,product-id-type,title,manufacturer,brand,mfr-part-number,merchant-catalog-number,bullet-point1,bullet-point2,bullet-point3,bullet-point4,bullet-point5,description,product_type,legal-disclaimer,prop-65,item-type,used-for1,used-for2,used-for3,used-for4,used-for5,other-item-attributes1,other-item-attributes2,other-item-attributes3,other-item-attributes4,other-item-attributes5,subject-content1,subject-content2,subject-content3,subject-content4,subject-content5,search-terms1,search-terms2,search-terms3,search-terms4,search-terms5,platinum-keywords1,platinum-keywords2,platinum-keywords3,platinum-keywords4,platinum-keywords5,main-image-url,other-image-url1,other-image-url2,other-image-url3,other-image-url4,other-image-url5,other-image-url6,other-image-url7,other-image-url8,item-weight-unit-of-measure,item-weight,item-length-unit-of-measure,item-length,item-height,item-width,package-weight-unit-of-measure,package-weight,package-length-unit-of-measure,package-length,package-height,package-width,product-tax-code,launch-date,map,msrp,item-price,sale-price,sale-from-date,sale-through-date,quantity,leadtime-to-ship,is-gift-message-available,is-giftwrap-available,update-delete,target-audience-keywords1,target-audience-keywords2,target-audience-keywords3,item-condition,PC_AdditionalDrives1,PC_AdditionalDrives2,PC_AdditionalDrives3,PC_AdditionalDrives4,PC_AdditionalDrives5,PC_AdditionalDrives6,PC_AdditionalDrives7,PC_AdditionalDrives8,PC_AdditionalDrives9,PC_AdditionalDrives10,PC_HardDriveSize1,PC_HardDriveSize2,PC_HardDriveSize3,PC_HardDriveSize4,PC_HardDriveSize5,PC_HardDriveSize6,PC_HardDriveSize7,PC_HardDriveSize8,PC_HardDriveSizeUnitOfMeasure,PC_HardDriveInterface1,PC_HardDriveInterface2,PC_HardDriveInterface3,PC_HardDriveInterface4,PC_SoftwareIncluded,PC_ComputerMemoryType1,PC_ComputerMemoryType2,PC_ComputerMemoryType3,PC_ComputerMemoryType4,PC_ComputerMemoryType5,PC_ComputerMemoryType6,PC_ComputerMemoryType7,PC_ComputerMemoryType8,PC_ComputerMemoryType9,PC_ComputerMemoryType10,PC_RAMSize,PC_RAMSizeUnitOfMeasure,PC_MemorySlotsAvailable,PC_ProcessorBrand,PC_ProcessorSpeed,PC_ProcessorSpeedUnitOfMeasure,PC_ProcessorType,PC_ProcessorCount,PC_ScreenResolution,PC_ScreenSize,PC_ScreenSizeUnitOfMeasure,PC_OperatingSystem1,PC_OperatingSystem2,PC_HardwarePlatform,PC_U-RackSize,PC_WirelessType1,PC_WirelessType2,PC_WirelessType3,PC_GraphicsDescription1,PC_GraphicsRAMSize1,PC_GraphicsCardInterface1,PC_GraphicsDescription2,PC_GraphicsRAMSize2,PC_GraphicsCardInterface2,PC_GraphicsRAMSizeUnitOfMeasure,PC_AdditionalFeatures,rebate-start-date1,rebate-end-date1,rebate-message1,rebate-name1,rebate-start-date2,rebate-end-date2,rebate-message2,rebate-name2,PDA_HardDriveSize,PDA_HardDriveSizeUnitOfMeasure,PDA_SoftwareIncluded,PDA_ComputerMemoryType1,PDA_ComputerMemoryType2,PDA_ComputerMemoryType3,PDA_ComputerMemoryType4,PDA_ComputerMemoryType5,PDA_ComputerMemoryType6,PDA_ComputerMemoryType7,PDA_ComputerMemoryType8,PDA_ComputerMemoryType9,PDA_ComputerMemoryType10,PDA_MemorySlotsAvailable,PDA_RAMSize,PDA_RAMSizeUnitOfMeasure,PDA_ProcessorSpeed,PDA_ProcessorSpeedUnitOfMeasure,PDA_ProcessorType,PDA_ROMSize,PDA_ROMSizeUnitOfMeasure,PDA_ScreenResolution,PDA_ColorScreen,PDA_ScreenSize,PDA_ScreenSizeUnitOfMeasure,PDA_OperatingSystem1,PDA_OperatingSystem2,PDA_OperatingSystem3,PDA_OperatingSystem4,PDA_OperatingSystem5,PDA_WirelessType1,PDA_WirelessType2,PDA_WirelessType3,PDA_AdditionalFeatures,PDA_PDABaseModel1,PDA_PDABaseModel2,PDA_PDABaseModel3,PDA_PDABaseModel4,PDA_PDABaseModel5,CE_Color,CE_SpeakerDiameter,CE_SpeakerDiameterUnitOfMeasure,CE_ColorMap,CE_Voltage,CE_Wattage,CE_PowerSource,CE_AdditionalFeatures,CE_VehicleSpeakerSize,CE_TelephoneType1,CE_TelephoneType2,CE_TelephoneType3,CE_TelephoneType4,CE_PDABaseModel1,CE_PDABaseModel2,CE_PDABaseModel3,CE_PDABaseModel4,CE_PDABaseModel5,CE_DigitalMediaFormat,CE_HomeAutomationCommunicationDevice,CE_DigitalAudioCapacity,CE_HolderCapacity,CE_MemorySlotsAvailable,CE_RAMSize,CE_RAMSizeUnitOfMeasure,CE_ScreenResolution,CE_ColorScreen,CE_ScreenSize,CE_ScreenSizeUnitOfMeasure,CE_WirelessType1,CE_WirelessType2,CE_WirelessType3,CE_HardDriveSize1,CE_HardDriveSize2,CE_HardDriveSize3,CE_HardDriveSize4,CE_HardDriveSize5,CE_HardDriveSize6,CE_HardDriveSize7,CE_HardDriveSize8,CE_HardDriveSizeUnitOfMeasure,CE_HardDriveInterface1,CE_HardDriveInterface2,CE_HardDriveInterface3,CE_HardDriveInterface4,CE_OperatingSystem1,CE_OperatingSystem2,CE_OperatingSystem3,CE_OperatingSystem4,CE_OperatingSystem5,CE_HardwarePlatform,CE_ComputerMemoryType1,CE_ComputerMemoryType2,CE_ComputerMemoryType3,CE_ComputerMemoryType4,CE_ComputerMemoryType5,CE_ComputerMemoryType6,CE_ComputerMemoryType7,CE_ComputerMemoryType8,CE_ComputerMemoryType9,CE_ComputerMemoryType10,CE_ItemPackageQuantity,id";
// $line[] = $output;
// $line[] = str_replace(",","\t",$output);
$first_run = true;
while (!$result->EOF) {
// fetch the price levels
if (ZENCART_USE_PRICE_SHEETS) {
$sql = "SELECT id, default_levels FROM " . TABLE_PRICE_SHEETS . "\n\t\t\t\t\tWHERE '" . date('Y-m-d', time()) . "'>=effective_date AND sheet_name = '" . MODULE_AMAZON_DEFAULT_PRICE_SHEET . "' AND inactive='0'";
$default_levels = $db->Execute($sql);
if ($default_levels->RecordCount() == 0) {
$messageStack->add('Couldn\'t find a default price level for price sheet: ' . MODULE_AMAZON_DEFAULT_PRICE_SHEET, 'error');
return false;
}
$sql = "SELECT price_levels FROM " . TABLE_INVENTORY_SPECIAL_PRICES . "\n\t\t\t\t\tWHERE inventory_id={$result->fields['id']} AND price_sheet_id={$default_levels->fields['id']}";
$special_levels = $db->Execute($sql);
if ($special_levels->RecordCount() > 0) {
$price_levels = $special_levels->fields['price_levels'];
} else {
$price_levels = $default_levels->fields['default_levels'];
}
}
//build the uplink array sequenced properly
$output = array();
$output['sku'] = $result->fields['sku'];
// test for duplicate upc's
if (!$result->fields['upc_code'] && !$result->fields['amazon_asin']) {
// find the dup
$messageStack->add('Missing UPC code and Amazon ASIN ID for SKU: ' . $result->fields['sku'], 'error');
}
$output['standard-product-id'] = $result->fields['amazon_asin'] ? $result->fields['amazon_asin'] : $result->fields['upc_code'];
$output['product-id-type'] = $result->fields['amazon_asin'] ? 'ASIN' : 'UPC';
$output['title'] = $result->fields['description_sales'];
$output['manufacturer'] = $result->fields['manufacturer'];
$output['brand'] = $result->fields['brand'];
$output['mfr-part-number'] = $result->fields['oem_sku'];
$output['merchant-catalog-number'] = $result->fields['merchant_catalog_number'];
$output['bullet-point1'] = $result->fields['bullet_point_1'];
$output['bullet-point2'] = $result->fields['bullet_point_2'];
$output['bullet-point3'] = $result->fields['bullet_point_3'];
$output['bullet-point4'] = $result->fields['bullet_point_4'];
$output['bullet-point5'] = $result->fields['bullet_point_5'];
$output['description'] = $result->fields['description'];
$output['product_type'] = 'ConsumerElectronics';
// $result->fields['product_type'];
$output['legal-disclaimer'] = $result->fields['legal_disclaimer'];
$output['prop-65'] = $result->fields['prop_65'] ? 'true' : 'false';
$output['item-type'] = $result->fields['item_type'];
// item_type_keyword ???
// $output['used-for1'] = ''; // $result->fields['used-for1'];
// $output['used-for2'] = ''; // $result->fields['used-for2'];
// $output['used-for3'] = ''; // $result->fields['used-for3'];
// $output['used-for4'] = ''; // $result->fields['used-for4'];
// $output['used-for5'] = ''; // $result->fields['used-for5'];
$output['other-item-attributes1'] = $result->fields['other_item_attributes_1'];
$output['other-item-attributes2'] = $result->fields['other_item_attributes_2'];
$output['other-item-attributes3'] = $result->fields['other_item_attributes_3'];
$output['other-item-attributes4'] = $result->fields['other_item_attributes_4'];
$output['other-item-attributes5'] = $result->fields['other_item_attributes_5'];
// $output['subject-content1'] = ''; // $result->fields['subject-content1'];
// $output['subject-content2'] = ''; // $result->fields['subject-content2'];
// $output['subject-content3'] = ''; // $result->fields['subject-content3'];
// $output['subject-content4'] = ''; // $result->fields['subject-content4'];
// $output['subject-content5'] = ''; // $result->fields['subject-content5'];
$output['search-terms1'] = $result->fields['search_terms_1'];
$output['search-terms2'] = $result->fields['search_terms_2'];
$output['search-terms3'] = $result->fields['search_terms_3'];
$output['search-terms4'] = $result->fields['search_terms_4'];
$output['search-terms5'] = $result->fields['search_terms_5'];
// $output['platinum-keywords1'] = $result->fields['platinum-keywords1'];
// $output['platinum-keywords2'] = $result->fields['platinum-keywords2'];
// $output['platinum-keywords3'] = $result->fields['platinum-keywords3'];
// $output['platinum-keywords4'] = $result->fields['platinum-keywords4'];
// $output['platinum-keywords5'] = $result->fields['platinum-keywords5'];
// Amazon only supports one level, so we'll use the first path dir and filename only
$imageType = substr($result->fields['image_with_path'], -3);
if (in_array($imageType, array('jpg', 'JPG', 'peg', 'PEG', 'gif', 'GIF'))) {
$output['main-image-url'] = PPS_FULL_URL . $result->fields['image_with_path'];
} else {
$output['main-image-url'] = '';
$messageStack->add_session('Image at path: ' . $result->fields['image_with_path'] . ' for sku ' . $output['sku'] . ' must be of type jpg or gif for amazon!', 'error');
}
// $output['other-image-url1'] = $result->fields['other-image-url1'];
// $output['other-image-url2'] = $result->fields['other-image-url2'];
// $output['other-image-url3'] = $result->fields['other-image-url3'];
// $output['other-image-url4'] = $result->fields['other-image-url4'];
// $output['other-image-url5'] = $result->fields['other-image-url5'];
// $output['other-image-url6'] = $result->fields['other-image-url6'];
// $output['other-image-url7'] = $result->fields['other-image-url7'];
// $output['other-image-url8'] = $result->fields['other-image-url8'];
$output['item-weight-unit-of-measure'] = 'LB';
// $result->fields['item_weight_unit_of_measure'];
$output['item-weight'] = $result->fields['item_weight'];
$output['item-length-unit-of-measure'] = 'IN';
// $result->fields['item_length_unit_of_measure'];
$output['item-length'] = $result->fields['dim_length'];
$output['item-height'] = $result->fields['dim_height'];
$output['item-width'] = $result->fields['dim_width'];
$output['package-weight-unit-of-measure'] = 'LB';
// $result->fields['package_weight_unit_of_measure'];
if ($result->fields['item_weight'] == 0) {
$messageStack->add_session('Item ' . $result->fields['sku'] . ' has no weight. Please edit the inventory record and add a weight.', 'error');
}
$output['package-weight'] = $result->fields['package_weight'] > 0 ? $result->fields['package_weight'] : ceil($result->fields['item_weight']);
$output['package-length-unit-of-measure'] = 'IN';
// $result->fields['package_length_unit_of_measure'];
$output['package-length'] = $result->fields['package_length'] > 0 ? $result->fields['package_length'] : ceil($result->fields['dim_length']);
$output['package-height'] = $result->fields['package_height'] > 0 ? $result->fields['package_height'] : ceil($result->fields['dim_height']);
$output['package-width'] = $result->fields['package_width'] > 0 ? $result->fields['package_width'] : ceil($result->fields['dim_width']);
// $output['product-tax-code'] = $result->fields['product_tax_code']; // *****************************
$output['launch-date'] = substr($result->fields['creation_date'], 0, 10);
// $output['release-date'] = $result->fields['creation_date'];
$output['msrp'] = $result->fields['full_price'];
$prices = inv_calculate_prices($result->fields['item_cost'], $result->fields['full_price'], $price_levels);
$output['currency'] = 'USD';
// if ($result->fields['map_price'] > 0) $output['map-price'] = $result->fields['map_price'];
$output['item-price'] = $prices[0]['price'];
// $output['sale-price'] = ''; // $result->fields['sale-price'];
// $output['sale-from-date'] = ''; // $result->fields['sale-from-date'];
// $output['sale-through-date']= ''; // $result->fields['sale-through-date'];
// for assemblies, we need to determine if we have enough product to assemble a unit and adjust qty accordingly
if ($result->fields['inventory_type'] == 'as') {
$sql = "SELECT a.qty, i.quantity_on_hand\n\t\t\t\t FROM " . TABLE_INVENTORY_ASSY_LIST . " a JOIN " . TABLE_INVENTORY . " i on a.sku = i.sku\n\t\t\t\t where a.ref_id = " . $result->fields['id'];
$bom_list = $db->Execute($sql);
$min_qty = 9999;
while (!$bom_list->EOF) {
$qty = $bom_list->fields['quantity_on_hand'] / $bom_list->fields['qty'];
$min_qty = min($min_qty, floor($qty));
$bom_list->MoveNext();
}
$result->fields['quantity_on_hand'] = $min_qty;
}
$available = $result->fields['quantity_on_hand'] - $result->fields['quantity_on_sales_order'] - $result->fields['quantity_on_allocation'];
$output['quantity'] = max(0, $available);
// no negative numbers
$output['leadtime-to-ship'] = $result->fields['leadtime_to_ship'];
// $output['is-discontinued-by-manufacturer'] = '';
// $output['update-delete'] = $result->fields['update_delete'];
$output['target-audience-keywords1'] = 'people';
$output['target-audience-keywords2'] = 'professional-audience';
// $output['target-audience-keywords3'] = '';
$output['item-condition'] = 'New';
// $result->fields['item_condition'];
// $output['condition_note'] = $result->fields['condition_note'];
/*
$output[] = ''; // $result->fields['rebate-start-date1'];
$output[] = ''; // $result->fields['rebate-end-date1'];
$output[] = ''; // $result->fields['rebate-message1'];
$output[] = ''; // $result->fields['rebate-name1'];
$output[] = ''; // $result->fields['rebate-start-date2'];
$output[] = ''; // $result->fields['rebate-end-date2'];
$output[] = ''; // $result->fields['rebate-message2'];
$output[] = ''; // $result->fields['rebate-name2'];
*/
$output['CE_Voltage'] = substr($result->fields['cat_attrib_01'], 0, strpos($result->fields['cat_attrib_01'], ' '));
/*
$output[] = ''; // $result->fields['CE_Color'];
$output[] = ''; // $result->fields['CE_ColorMap'];
$output[] = ''; // $result->fields['CE_Wattage'];
$output[] = ''; // $result->fields['CE_PowerSource'];
$output[] = ''; // $result->fields['CE_AdditionalFeatures'];
$output[] = ''; // $result->fields['CE_ItemPackageQuantity'];
*/
// implode it and download to user
if ($first_run) {
// save the keys for the first pass
$headings = array_keys($output);
$line[] = implode($separator, $headings);
$first_run = false;
}
// if it's comma separated delimit
if ($separator == ",") {
foreach ($output as $key => $value) {
if (strpos($value, ',') !== false) {
$output[$key] = '"' . $value . '"';
}
}
}
$line[] = implode($separator, $output);
$result->MoveNext();
}
// BOF - Special section for upload of free shipping override
if (false) {
$result = $db->Execute("SELECT sku, amazon_freeship FROM " . TABLE_INVENTORY . " WHERE amazon='1' AND inactive='0'");
$line = array();
$output = "TemplateType=Overrides,Version=1.01,This row for Amazon.com use only. Do not modify or delete.";
$line[] = str_replace(",", $separator, $output);
$first_run = true;
while (!$result->EOF) {
if ($result->fields['amazon_freeship']) {
$output = array();
$output['sku'] = $result->fields['sku'];
$output['Locale1'] = 'ContinentalUS';
$output['FulfillmentServiceLevel1'] = 'Standard';
$output['ShippingAmt1'] = '0.00';
$output['Type1'] = 'Exclusive';
// $output['DoNotShip1'] = ''; // possible values are true and false
// more fields allowed Locale2 through DoNotShip5
// There is no way to delete this directly on Amazon, send feed with all SKUs set to
// delete to remove the Free Shipping Override flag
$output['UpdateDelete'] = 'Update';
// implode it and download to user
if ($first_run) {
// save the keys for the first pass
$headings = array_keys($output);
$line[] = implode($separator, $headings);
$first_run = false;
}
// if it's comma separated delimit
if ($separator == ",") {
foreach ($output as $key => $value) {
if (strpos($value, ',') !== false) {
$output[$key] = '"' . $value . '"';
}
}
}
$line[] = implode($separator, $output);
}
$result->MoveNext();
}
}
// EOF - Special section for upload of free shipping overrride
$contents = implode("\n", $line);
$contents .= "\n";
if ($messageStack->size == 0) {
header('Content-type: text/plain');
header('Content-Length: ' . strlen($contents));
header('Content-Disposition: attachment; filename=' . $file_name);
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo $contents;
exit;
}
}
function calulate_special_fields($id)
{
global $db, $currencies;
$new_data = array();
// get the inventory prices
$inventory = $db->Execute("select item_cost, full_price, price_sheet from " . TABLE_INVENTORY . " where id = '{$id}'");
// determine what price sheet to use, priority: inventory, default
if ($inventory->fields['price_sheet'] != '') {
$sheet_name = $inventory->fields['price_sheet'];
} else {
$default_sheet = $db->Execute("select sheet_name from " . TABLE_PRICE_SHEETS . "\n\t\t\twhere type = 'c' and default_sheet = '1'");
$sheet_name = $default_sheet->RecordCount() == 0 ? '' : $default_sheet->fields['sheet_name'];
}
// determine the sku price ranges from the price sheet in effect
$levels = false;
if ($sheet_name != '') {
$sql = "select id, default_levels from " . TABLE_PRICE_SHEETS . "\n\t\t where inactive = '0' and type = 'c' and sheet_name = '{$sheet_name}' and \n\t\t (expiration_date is null or expiration_date = '0000-00-00' or expiration_date >= '" . date('Y-m-d') . "')";
$price_sheets = $db->Execute($sql);
// retrieve special pricing for this inventory item
$result = $db->Execute("select price_sheet_id, price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . "\n\t\t\twhere price_sheet_id = '{$price_sheets->fields['id']}' and inventory_id = " . $id);
$special_prices = array();
while (!$result->EOF) {
$special_prices[$result->fields['price_sheet_id']] = $result->fields['price_levels'];
$result->MoveNext();
}
$levels = isset($special_prices[$price_sheets->fields['id']]) ? $special_prices[$price_sheets->fields['id']] : $price_sheets->fields['default_levels'];
}
$new_data = array('price_level_1' => '', 'price_qty_1' => '', 'price_level_2' => '', 'price_qty_2' => '', 'price_level_3' => '', 'price_qty_3' => '', 'price_level_4' => '', 'price_qty_4' => '', 'price_level_5' => '', 'price_qty_5' => '');
if ($levels) {
$prices = inv_calculate_prices($inventory->fields['item_cost'], $inventory->fields['full_price'], $levels);
if (is_array($prices)) {
foreach ($prices as $key => $value) {
$new_data['price_level_' . ($key + 1)] = $currencies->clean_value($value['price']);
$new_data['price_qty_' . ($key + 1)] = $value['qty'];
}
}
}
return $new_data;
}
function inv_calculate_sales_price($qty, $sku_id, $contact_id = 0, $type = 'c')
{
global $db, $currencies;
$price_sheet = '';
$contact_tax = 1;
if ($contact_id) {
$contact = $db->Execute("select type, price_sheet, tax_id from " . TABLE_CONTACTS . " where id = '{$contact_id}'");
$type = $contact->fields['type'];
$price_sheet = $contact->fields['price_sheet'];
$contact_tax = $contact->fields['tax_id'];
}
// get the inventory prices
if ($type == 'v') {
if ($contact_id) {
$inventory = $db->Execute("select p.item_cost, a.full_price, a.price_sheet, p.price_sheet_v, a.item_taxable, p.purch_taxable from " . TABLE_INVENTORY . " a join " . TABLE_INVENTORY_PURCHASE . " p on a.sku = p.sku where a.id = '{$sku_id}' and p.vendor_id = '{$contact_id}'");
} else {
$inventory = $db->Execute("select MAX(p.item_cost) as item_cost, a.full_price, a.price_sheet, p.price_sheet_v, a.item_taxable, p.purch_taxable from " . TABLE_INVENTORY . " a join " . TABLE_INVENTORY_PURCHASE . " p on a.sku = p.sku where a.id = '{$sku_id}'");
}
$inv_price_sheet = $inventory->fields['price_sheet_v'];
} else {
$inventory = $db->Execute("select MAX(p.item_cost) as item_cost, a.full_price, a.price_sheet, p.price_sheet_v, a.item_taxable, p.purch_taxable from " . TABLE_INVENTORY . " a join " . TABLE_INVENTORY_PURCHASE . " p on a.sku = p.sku where a.id = '{$sku_id}'");
$inv_price_sheet = $inventory->fields['price_sheet'];
}
// set the default tax rates
$purch_tax = $contact_tax == 0 && $type == 'v' ? 0 : $inventory->fields['purch_taxable'];
$sales_tax = $contact_tax == 0 && $type == 'c' ? 0 : $inventory->fields['item_taxable'];
// determine what price sheet to use, priority: customer, inventory, default
if ($price_sheet != '') {
$sheet_name = $price_sheet;
} elseif ($inv_price_sheet != '') {
$sheet_name = $inv_price_sheet;
} else {
$default_sheet = $db->Execute("select sheet_name from " . TABLE_PRICE_SHEETS . " where type = '{$type}' and default_sheet = '1'");
$sheet_name = $default_sheet->RecordCount() == 0 ? '' : $default_sheet->fields['sheet_name'];
}
// determine the sku price ranges from the price sheet in effect
$price = $type == 'v' ? $inventory->fields['item_cost'] : $inventory->fields['full_price'];
if ($price == '' || $price == null) {
$price = 0.0;
}
if ($sheet_name != '') {
$sql = "select id, default_levels from " . TABLE_PRICE_SHEETS . "\n\t\t\t where inactive = '0' and type = '{$type}' and sheet_name = '{$sheet_name}' and\n\t\t\t (expiration_date is null or expiration_date = '0000-00-00' or expiration_date >= '" . date('Y-m-d') . "')";
$price_sheets = $db->Execute($sql);
// retrieve special pricing for this inventory item
$sql = "select price_sheet_id, price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " where price_sheet_id = '{$price_sheets->fields['id']}' and inventory_id = {$sku_id}";
$result = $db->Execute($sql);
$special_prices = array();
while (!$result->EOF) {
$special_prices[$result->fields['price_sheet_id']] = $result->fields['price_levels'];
$result->MoveNext();
}
$levels = isset($special_prices[$price_sheets->fields['id']]) ? $special_prices[$price_sheets->fields['id']] : $price_sheets->fields['default_levels'];
$prices = inv_calculate_prices($inventory->fields['item_cost'], $inventory->fields['full_price'], $levels);
if (is_array($prices)) {
foreach ($prices as $value) {
if ($qty >= $value['qty']) {
$price = $currencies->clean_value($value['price']);
}
}
}
}
return array('price' => $price, 'sales_tax' => $sales_tax, 'purch_tax' => $purch_tax);
}
