gistfile1.txt

<?php
require_once dirname ( __FILE__ ) . '/../../configs/Constant.php';
require_once dirname ( __FILE__ ) . '/../../libs/common_function.php';
require_once dirname ( __FILE__ ) . '/../../bean/pagination.php';
require_once dirname ( __FILE__ ) . '/../../bean/descTransfer.php';
require_once dirname ( __FILE__ ) . '/../../bean/SmartyFactory.php';
require_once dirname ( __FILE__ ) . '/../../libs/ExcelClass/PHPExcel.php';

header ( "Content-Type:text/html;charset=utf-8" );
// const INVENTORY_ROOT = "http://query.app.inventory.ux168.cn:8080/inventory_app";
// const PRODUCTCENTER_ROOT = "http://query.app.productcenter.ux168.cn:8080/productcenter_app";
// http://m2.uxcell.com/photo_new/20111103/c/ux_a11110300ux0136_ux_c.jpg;
const IMAGES_ROOT = "http://m2.uxcell.com/photo_new";
const INVENTORY_ROOT = "http://172.16.10.43:8080/inventory_app";
const PRODUCTCENTER_ROOT = "http://172.16.10.43:8080/productcenter_app";
$smarty = SmartyFactory::getSmarty ();

$wsdl = INVENTORY_ROOT . "/SkuComponent?wsdl";
$inventorySkuComponent = new SoapClient ( $wsdl );
$wsdl = INVENTORY_ROOT . "/SkuSupplierCategoryComponent?wsdl";
$skuSupplierComponent = new SoapClient ( $wsdl );
$wsdl = PRODUCTCENTER_ROOT . "/TemplatesComponent?wsdls";
$templateComponent = new SoapClient ( $wsdl );
$wsdl = INVENTORY_ROOT . "/SkuAttributeComponent?wsdl";
$skuAttributeComponent = new SoapClient ( $wsdl );

ini_set ( 'memory_limit', '256M' );
$conn = getDBConnection ();

// display remarks
$channels = array (
		"german" => "german",
		"japanese" => "japanese" 
);
$remarks = array ();
$realRemarks = array ();
foreach ( $channels as $key => $channel ) {
	$sql = "select distinct(remark) from temp.desc_transfer where channel = '{$channel}'  order by left(remark, 10) desc";
	$result = mysql_query ( $sql, $conn );
	while ( ! ! $row = mysql_fetch_assoc ( $result ) ) {
		$remark = trim ( $row ['remark'] );
		$sql = "select count(1) as num from temp.desc_transfer where remark = '$remark' and  channel='{$channel}'";
		$countRes = mysql_query ( $sql, $conn );
		$num = "";
		while ( ! ! $countRow = mysql_fetch_assoc ( $countRes ) ) {
			$num = $countRow ['num'];
		}
		$realRemarks [$key] [] = $remark;
		$remarks [$key] [] = $remark . " ($num)";
	}
}
// display field
$sql = "show columns from temp.desc_transfer";
$res = mysql_query ( $sql );
$dbField = array ();
while ( $content = mysql_fetch_assoc ( $res ) ) {
	$dbField [] = $content ['Field'];
}
$normalOrder = array (
		"id",
		"skuId",
		"channel",
		"status",
		"inventory cate", //
		"UPC",
		"title1",
		"enTitle",
		"description",
		"bulletPoint1",
		"bulletPoint2",
		"bulletPoint3",
		"bulletPoint4",
		"bulletPoint5",
		"searchTerms1",
		"searchTerms2",
		"searchTerms3",
		"searchTerms4",
		"searchTerms5",
		"Pack Size(L*W*H)",
		"Package Shipment Weight",
		"Product Net Weight",
		"Provisional Stock Level", // ?
		"picUrl c",
		"picUrl g",
		"picUrl g01",
		"picUrl g02",
		"picUrl g03",
		"createdBy",
		"createdOn",
		"modifiedBy",
		"modifiedOn",
		"remark" 
);
if (isset ( $_POST ) && ! empty ( $_POST )) {
	$fileDir = dirname ( __FILE__ ) . "/../../download_files/";
	cleanUpDirectory($fileDir);
	$jpRemarks = isset ( $_POST ['jpRemark'] ) ? $_POST ['jpRemark'] : '';
	$deRemarks = isset ( $_POST ['deRemark'] ) ? $_POST ['deRemark'] : '';
	if (count($jpRemarks) > 1 || count($deRemarks)){
		die	("too much!!");
	}
	$fields = isset ( $_POST ['field'] ) ? trim ( $_POST ['field'] ) : '';
	$fieldArr = explode ( '&', $fields );
	$queryField = array ();
	foreach ( $fieldArr as $field ) {
		$temArr = explode ( '=', $field );
		$realField = isset ( $temArr [1] ) ? $temArr [1] : '';
		if (strpos ( $realField, '+' ) !== false) {
			$realField = str_replace ( '+', ' ', $realField );
			$queryField [] = addslashes ( $realField );
		} else {
			$queryField [] = addslashes ( $realField );
		}
	}
	$queryRemark = array();
	if (! empty ( $jpRemarks )) {
		$queryRemark = $jpRemarks;
		$channel = 'japanese';
	}elseif (! empty ( $deRemarks )){
		$queryRemark = $deRemarks;
		$channel = 'german';
	}
		foreach ( $queryRemark as $remark ) {
			$remark = addslashes ( $remark );
			$totalQueryFiled = array_intersect ( $normalOrder, $queryField );
			$colunmnNameArray = $totalQueryFiled;
			$queryDbFiled = array_intersect ( $totalQueryFiled, $dbField );
			if (! empty ( $queryDbFiled )) {
				$fieldStr = implode ( ",", $queryDbFiled );
				$records = getDataFromDatabase ( $fieldStr, $channel, $remark, $conn );
				$skuIdArr = array ();
				foreach ( $records as $skuId => $content ) {
					$skuIdArr [] = $skuId;
					$records [$skuId] ['Pack Size(L*W*H)'] = getSizeBySku ( $skuId, $skuAttributeComponent );
					$records [$skuId] ['picUrl c'] = getURLBySkuIdAndPicType ( $skuId, 'c' );
					$records [$skuId] ['picUrl g'] = getURLBySkuIdAndPicType ( $skuId, 'g' );
					$records [$skuId] ['picUrl g01'] = getURLBySkuIdAndPicType ( $skuId, 'g01' );
					$records [$skuId] ['picUrl g02'] = getURLBySkuIdAndPicType ( $skuId, 'g02' );
					$records [$skuId] ['picUrl g03'] = getURLBySkuIdAndPicType ( $skuId, 'g03' );
				}
				$skuIdArrs = array_chunk ( $skuIdArr, 5 );
				foreach ( $skuIdArrs as $skuIds ) {
					$returnArr [] = getDataFromInventory ( $skuIds, $inventorySkuComponent, $skuSupplierComponent, $templateComponent );
				}
				foreach ( $returnArr as $key => $skuIdInfo ) {
					foreach ( $skuIdInfo as $sku => $infos ) {
						$records [$sku] = array_merge ( $records [$sku], $infos );
						unset($returnArr[$key]);
					}
				}
			}
			createXls ( $colunmnNameArray, $remark, $records );
		}
}
// get Pack Size(L*W*H)
function getSizeBySku($skuId, $skuAttributeComponent) {
	$skuIdListJsonEncode = json_encode ( array (
			$skuId 
	) );
	$attrNameListJsonEncode = json_encode ( array (
			"Pack Size(L*W*H)" 
	) );
	try {
		$callresponse = $skuAttributeComponent->getSkuAttributeBySkuIdAndAttributeName ( array (
				"skuIdListJsonEncode" => $skuIdListJsonEncode,
				"attributeNameListJsonEncode" => $attrNameListJsonEncode 
		) );
		$skuAttributeResponse = $callresponse->skuAttributeResponse;
		if ($skuAttributeResponse->responseMessage->messageType == "success" && $skuAttributeResponse->pagination->totalNumberOfCurrentPage > 0) {
			$skuAttributes = $skuAttributeResponse->skuAttributes;
			if ($skuAttributes->attributeName == "Pack Size(L*W*H)") {
				return $skuAttributes->attributeValue;
			}
		}
	} catch ( Exception $e ) {
		echo "getSkuBaseBySkuIdList exception: " . $e->getMessage () . "\r\n";
	}
}

// 根据skuId 和 picType 找出图片URL
function getURLBySkuIdAndPicType($skuId, $picType, $urlPath = "") {
	if ($urlPath == "")
		$urlPath = IMAGES_ROOT;
		// match year, month, day
	$matchCount = preg_match ( "/^[a-z]{1,2}[0-9]{6}/", $skuId, $matchArray );
	if ($matchCount == 0) {
		$urlPath .= '/other';
	} else {
		$matchStr = $matchArray [0];
		$urlPath .= '/20' . substr ( $matchStr, - 6 );
	}
	// match pic type
	if (in_array ( $picType, array (
			'm',
			'n',
			's' 
	) ) == true) {
		$urlPath .= '/' . $picType;
	} else if (substr ( $picType, 0, 1 ) == 'c') {
		$urlPath .= '/c';
	} else if (substr ( $picType, 0, 1 ) == 'g') {
		$urlPath .= '/g';
	} else {
		$urlPath .= '/other';
	}
	
	$urlPath .= '/ux_' . $skuId . '_ux_' . $picType . '.jpg';
	return $urlPath;
}

// get data from inventory
function getDataFromInventory($skuIdArr, $inventorySkuComponent, $skuSupplierComponent, $templateComponent) {
	$returnArr = array ();
	$skuIdListJsonEncode = json_encode ( $skuIdArr );
	
	try {
		$callresponse = $inventorySkuComponent->getSkuBaseBySkuIdList ( array (
				"skuIdListJsonEncode" => $skuIdListJsonEncode 
		) );
		
		$skuBaseResponse = $callresponse->skuBaseResponse;
		if ($skuBaseResponse->responseMessage->messageType == "success" && $skuBaseResponse->pagination->totalNumberOfCurrentPage > 0) {
			$bases = $skuBaseResponse->skuBases;
			if (is_array ( $bases )) {
				foreach ( $bases as $base ) {
					$returnArr [$base->skuId] [] = array (
							"Provisional Stock Level" => $base->provisionalStockLevel,
							"Product Net Weight" => $base->netWeight,
							"Package Shipment Weight" => $base->packageWeight + $base->netWeight 
					);
				}
			} else {
				$returnArr [$bases->skuId] [] = array (
						"Provisional Stock Level" => $bases->provisionalStockLevel,
						"Product Net Weight" => $bases->netWeight,
						"Package Shipment Weight" => $bases->packageWeight + $bases->netWeight 
				);
			}
		}
	} catch ( Exception $e ) {
		echo "getSkuBaseBySkuIdList exception: " . $e->getMessage () . "\r\n";
	}
	try {
		$callresponse = $skuSupplierComponent->getSkuCategoryIdFullPathBySkuIdList ( array (
				"skuIdListJsonEncode" => $skuIdListJsonEncode 
		) );
		$skuSupplierCategoryFullPathResponse = $callresponse->skuSupplierCategoryFullPathResponse;
		if ($skuSupplierCategoryFullPathResponse->responseMessage->messageType == "success" && $skuSupplierCategoryFullPathResponse->pagination->totalNumberOfCurrentPage > 0) {
			$skuSupplierCategoryFullPaths = $skuSupplierCategoryFullPathResponse->skuSupplierCategoryFullPaths;
			if (is_array ( $skuSupplierCategoryFullPaths )) {
				foreach ( $skuSupplierCategoryFullPaths as $skuSupplierCategoryFullPath ) {
					$returnArr [$skuSupplierCategoryFullPath->skuId] [] = array (
							"inventory cate" => $skuSupplierCategoryFullPath->categoryFullPath 
					);
				}
			} else {
				$returnArr [$skuSupplierCategoryFullPaths->skuId] [] = array (
						"inventory cate" => $skuSupplierCategoryFullPaths->categoryFullPath 
				);
			}
		}
	} catch ( Exception $e ) {
		echo "getSkuCategoryIdFullPathBySkuIdList exception: " . $e->getMessage () . "\r\n";
	}
	try {
		$callresponse = $templateComponent->getTemplatesBySkuList ( array (
				"skuIdListJsonEncode" => $skuIdListJsonEncode 
		) );
		$templatesResponses = $callresponse->templatesResponse;
		if ($templatesResponses->responseMessage->messageType == "success" && $templatesResponses->pagination->totalNumberOfCurrentPage > 0) {
			$templates = $templatesResponses->templates;
			if (is_array ( $templates )) {
				foreach ( $templates as $template ) {
					if ($template->productIdType == "UPC") {
						$returnArr [$template->skuId] [] = array (
								"UPC" => $template->standardProductId 
						);
					} else {
						$returnArr [$template->skuId] [] = array (
								"UPC" => "" 
						);
					}
				}
			} else {
				if ($templates->productIdType == "UPC") {
					$returnArr [$templates->skuId] [] = array (
							"UPC" => $templates->standardProductId 
					);
				} else {
					$returnArr [$templates->skuId] [] = array (
							"UPC" => "" 
					);
				}
			}
		}
	} catch ( Exception $e ) {
		echo "" . $e->getMessage () . "\r\n";
	}
	foreach ( $returnArr as $skuId => $SubArray ) {
		$temArr [$skuId] = call_user_func_array ( "array_merge", $returnArr [$skuId] );
	}
	return $temArr;
}
// get data from database
function getDataFromDatabase($fieldStr, $channel, $remark, $conn) {
	$sql = "select count(*) as num from temp.desc_transfer where remark = '{$remark}' and channel= '{$channel}'";
	echo $sql;
	$res = mysql_query ( $sql, $conn );
	if (! $res) {
		return $returnMessage = mysql_error ( $conn );
		exit ();
	}
	$totalRecord = "";
	$countRow = "";
	while ( $countRow = mysql_fetch_assoc ( $res ) ) {
		$totalRecord = $countRow ['num'];
	}
	$records = array ();
	for($i = 0; $i < ceil ( $totalRecord / 10 ); $i ++) {
		$start = $i * 10;
		$offset = 10;
		$sql = "select $fieldStr from temp.desc_transfer where remark = '{$remark}' ";
		$sql .= "and channel = '{$channel}' limit $start, $offset";
		echo $sql . "\r\n";
		$res = mysql_query ( $sql, $conn );
		if (! mysql_num_rows ( $res )) {
			continue;
		}
		while ( $row = mysql_fetch_assoc ( $res ) ) {
			$fieldArr = explode ( ',', $fieldStr );
			$tempArr = array ();
			foreach ( $fieldArr as $field ) {
				$tempArr [$field] = stripHtml ( isset ( $row [$field] ) ? $row [$field] : '' );
			}
			$skuIdArr [] = $row ['skuId'];
			$records [$row ['skuId']] = $tempArr;
		}
	}
	return $records;
}
function stripHtml($str) {
	$str = htmlspecialchars_decode ( $str );
	$str = stripslashes ( $str );
	$str = strip_tags ( $str );
	return $str;
}

/**
 * create excel
 *
 * @param unknown $colunmnNameArray        	
 * @param
 *        	file name & sheet title $remark
 * @param unknown $content        	
 */
function createXls($colunmnNameArray, $remark, $records) {
	$remark = preg_replace ( '/[\s,\\,\:,\/,\*,\?,",<,>,\|,\-,\.,(,)]+/', '_', $remark );
	$xls = new PHPExcel ();
	$write = new PHPExcel_Writer_Excel5 ( $xls );
	// write into xls
	$xls->setActiveSheetIndex ( 0 );
	
	$colunmnArray = array ();
	$sheetObj = $xls->getActiveSheet ();
	$sheetObj->setTitle ( substr ( $remark, 0, 31 ) );
	foreach ( $colunmnNameArray as $key => $colunmnName ) {
		$key = PHPExcel_Cell::stringFromColumnIndex ( $key );
		if (! in_array ( $colunmnName, $colunmnArray )) {
			$colunmnArray [$key] = $colunmnName;
			$sheetObj->setCellValue ( $key . '1', $colunmnName );
		}
	}
	$i = 1; // row
	foreach ( $records as $skuId => $record ) {
		$i ++;
		foreach ( $colunmnArray as $titleKey => $colunmnName ) { // colunmn
			$value = $record [$colunmnName];
			$sheetObj->setCellValue ( $titleKey . $i, $value );
		}
	}
	$fileDir = dirname ( __FILE__ ) . "/../../download_files/";
	if (! is_dir ( $fileDir )) {
		mkdir ( $fileDir );
	}
	$excelName = $fileDir . $remark . "_transfer.xls";
	if (file_exists ( $excelName )) {
		unlink ( $excelName );
	}
	$write->save ( $excelName );
}
function cleanUpDirectory($path) {
	if (substr($path, -1, 1) != "/") {
		$path .= "/";
	}
	$normal_files = glob($path . "*");
	$hidden_files = glob($path . "\.?*");
	$all_files = array_merge($normal_files, $hidden_files);
	foreach ($all_files as $file) {
		if (preg_match("/(\.|\.\.)$/", $file)){
			continue;
		}
		if (is_file($file) === TRUE) {
			unlink($file);
		}
		elseif (is_dir($file) === TRUE) {
			cleanUpDirectory($file);
		}
	}
}


$germanRemark = array_combine ( $realRemarks ['german'], $remarks ['german'] );
$smarty->assign ( "german", $germanRemark );
$japanRemark = array_combine ( $realRemarks ['japanese'], $remarks ['japanese'] );
$smarty->assign ( "japan", $japanRemark );
$smarty->assign ( "fieldArr", $normalOrder );
$smarty->display ( "export_transfer_by_remark.tpl" );
?>