steveosoule
4/14/2015 - 9:13 PM

Miva - Google Shopping Feed - v2

Miva - Google Shopping Feed - v2

<mvt:comment>
	<!--
		Product Data Feed: Google Shopping
		File Output: /feeds/google-shopping.txt
		date; curl -o NUL --location --max-redirs 1000 --data "continue=redirect" http://www.example.com/feed-google-shopping.html; date;
	-->
</mvt:comment>

<mvt:if expr="ISNULL g.continue">
	<!DOCTYPE html>
	<html>
		<head>
			<meta charset="utf-8">
			<meta http-equiv="X-UA-Compatible" content="IE=edge">
			<title>Generating Google Shopping Feed | The Paper Store</title>
			<meta name="viewport" content="width=device-width, initial-scale=1">

			<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.1/css/bootstrap.min.css">
		</head>
		<body>
			<div class="jumbotron">
				<div class="container">
					<h1>Google Shopping <small>Feed Generator</small></h1>
				</div>
			</div>
			<div class="container">
				<div class="row">
					<div class="col-xs-12">
						<iframe src="/feed-google-shopping.html?continue=refresh" style="width:100%; height:150px; border:0;">Loading...</iframe>
					</div>
				</div>
			</div>
		</body>
	</html>
	<mvt:exit/>
</mvt:if>

<mvt:comment><!-- Helper Variables --></mvt:comment>
	<mvt:assign name="g.tab" value="asciichar(9)"/>
	<mvt:assign name="g.newline" value="asciichar(10)"/>
	<mvt:assign name="g.return" value="asciichar(13)"/>
	<mvt:assign name="g.pipe" value="asciichar(124)"/>
	<mvt:assign name="g.comma" value="asciichar(44)"/>
	<mvt:assign name="g.quote" value="asciichar(34)"/>
	<mvt:assign name="g.period" value="asciichar(46)"/>
	<mvt:assign name="g.delimiter" value="asciichar(9)"/>
	<mvt:item name="ry_toolbelt" param="datetime_format|g.expiration_date|s.dyn_time_t + (86400 * 29)|'yyyy-mm-dd'" />

<mvt:comment><!-- Data feed file variables --></mvt:comment>
	<mvt:assign name="g.temp_file_folder" value="'/feeds/'"/>
	<mvt:if expr="ISNULL g.temp_file_name">
		<mvt:assign name="g.temp_file_name" value="'google-shopping.temp'"/>
	</mvt:if>
	<mvt:assign name="g.temp_file_path" value="g.temp_file_folder $ g.temp_file_name"/>
	<mvt:assign name="g.final_file_path" value="glosub( g.temp_file_path, '.temp', '.txt' )"/>
	<mvt:assign name="g.header_row" value="
		'link'									$ g.delimiter $
		'title'									$ g.delimiter $
		'description'							$ g.delimiter $
		'availability'							$ g.delimiter $
		'gender'								$ g.delimiter $
		'age_group'								$ g.delimiter $
		'gtin'									$ g.delimiter $
		'image_link'							$ g.delimiter $
		'price'									$ g.delimiter $
		'id'									$ g.delimiter $
		'expiration_date'						$ g.delimiter $
		'brand'									$ g.delimiter $
		'mpn'									$ g.delimiter $
		'condition'								$ g.delimiter $
		'product_type'							$ g.delimiter $
		'google_product_category'				$ g.delimiter $
		'weight'								$ g.delimiter $
		'color'									$ g.delimiter $
		'size'									$ g.delimiter $
		'custom_label_0'						$ g.delimiter $
		'custom_label_1'						$ g.delimiter $
		'custom_label_2'						$
		g.newline
	"/>

<mvt:comment><!-- Delete File if it exists, create new file with header row --></mvt:comment>
	<mvt:if expr="ISNULL g.Per_Page">
		<mvt:assign name="g.Per_Page" value="100" />
	</mvt:if>
	<mvt:if expr="ISNULL g.AllOffset">
		<mvt:assign name="g.AllOffset" value="0" />
	</mvt:if>
	<mvt:if expr="g.AllOffset LT g.Per_Page AND g.output NE 'debug'">
		<mvt:assign name="g.file_exists" value="sexists(g.temp_file_path)"/>
		<mvt:if expr="g.file_exists EQ 1">
			<mvt:assign name="g.file_deleted" value="sdelete(g.temp_file_path)"/>
			<mvt:if expr="g.file_deleted EQ 1">
				<mvt:assign name="g.file_created" value="file_create(g.temp_file_path,'script', g.header_row)"/>
			</mvt:if>
		<mvt:else>
			<mvt:assign name="g.file_created" value="file_create(g.temp_file_path,'script', g.header_row)"/>
		</mvt:if>
	</mvt:if>

<mvt:comment><!-- Loop over all of the products & write line to feed file --></mvt:comment>
	<mvt:foreach iterator="product" array="all_products:products">

		<mvt:comment><!-- Rename custom field & additional image variables to shorten them --></mvt:comment>
			<mvt:assign name="l.settings:product:images" value="l.settings:product:customfield_values:productimagecustomfields" />
			<mvt:assign name="l.settings:product:cf" value="l.settings:product:customfield_values:customfields" />
			<mvt:assign name="l.settings:product:customfield_values:productimagecustomfields" value="''" />
			<mvt:assign name="l.settings:product:customfield_values:customfields" value="''" />

		<mvt:if expr="l.settings:product:cf:google_exclude_product">
			<mvt:if expr="g.output EQ 'debug'">
				Skipped [google_exclude_product]: &mvt:product:code;<hr>
			</mvt:if>
			<mvt:foreachcontinue/>
		</mvt:if>

		<mvt:if expr="l.settings:product:cf:Show_Sell NE 'SELL'">
			<mvt:if expr="g.output EQ 'debug'">
				Skipped [Show/Sell]: &mvt:product:code;<hr>
			</mvt:if>
			<mvt:foreachcontinue/>
		</mvt:if>

		<mvt:comment><!-- Get category codes & names --></mvt:comment>
			<mvt:item name="ry_toolbelt" param="Product_Categories|l.all_settings:product:cf:incategory:count|l.all_settings:product:code" />

			<mvt:if expr="miva_array_elements(l.settings:product_categories) GT 0">
				<mvt:assign name="l.settings:product:cf:all_categories_excluded" value="1" />
			</mvt:if>

			<mvt:foreach iterator="category" array="product_categories">
				<mvt:item name="customfields" param="Read_Category_ID(l.settings:category:id, 'google_exclude_category', l.settings:category:cf:google_exclude_category)" />
				<mvt:item name="customfields" param="Read_Category_ID(l.settings:category:id, 'google_product_category', l.settings:category:cf:google_product_category)" />

				<mvt:if expr="l.settings:category:cf:google_exclude_category">
					<mvt:foreachcontinue/>
				</mvt:if>

				<mvt:assign name="l.settings:product:cf:all_categories_excluded" value="''" />

				<mvt:if expr="ISNULL l.settings:product:cf:product_type">
					<mvt:assign name="l.settings:product:cf:product_type" value="l.settings:category:name" />
				</mvt:if>

				<mvt:if expr="ISNULL l.settings:product:cf:google_product_category AND l.settings:category:cf:google_product_category">
					<mvt:assign name="l.settings:product:cf:google_product_category" value="l.settings:category:cf:google_product_category" />
				</mvt:if>

			</mvt:foreach>

			<mvt:if expr="l.settings:product:cf:all_categories_excluded">
				<mvt:if expr="g.output EQ 'debug'">
					Skipped [All Categories Excluded]: &mvt:product:code;<hr>
				</mvt:if>
				<mvt:foreachcontinue/>
			</mvt:if>

			<mvt:assign name="l.settings:product:cf:incategories" value="l.settings:product_categories" />

			<mvt:comment><!-- Stock Level --></mvt:comment>
				<mvt:if expr="l.settings:product:inv_active">
					<mvt:assign name="l.settings:product:cf:total_stock_level" value="l.settings:product:inv_available" />
				<mvt:else>
					<mvt:comment><!-- Lookup Variant Stock LEvels --></mvt:comment>
						<mvt:assign name="l.settings:query:query" value="'
							SELECT
								pvp.part_id AS \'id\',
								pv.product_id AS \'master_id\',

								GROUP_CONCAT(CASE WHEN a.code = \'color\' THEN o.code END) AS \'color_code\',
								GROUP_CONCAT(CASE WHEN a.code = \'size\' THEN o.code END) AS \'size_code\',

								GROUP_CONCAT(CASE WHEN a.code = \'color\' THEN o.prompt END) AS \'color_prompt\',
								GROUP_CONCAT(CASE WHEN a.code = \'size\' THEN o.prompt END) AS \'size_prompt\',

								p.code AS \'code\',
								p.name AS \'name\',
								p.price AS \'price\',
								p.cost AS \'cost\',
								i.image AS \'image\',

								ipc.inventory AS \'inventory\'
							FROM s01_ProductVariants pv
							LEFT JOIN s01_Attributes a
								ON pv.attr_id = a.id
							LEFT JOIN s01_Options o
								ON pv.option_id = o.id
							LEFT JOIN s01_ProductVariantParts pvp
								ON pv.variant_id = pvp.variant_id
							LEFT JOIN s01_Products p
								ON pvp.part_id = p.id
							LEFT JOIN s01_InventoryProductCounts ipc
								ON p.id = ipc.product_id
							LEFT JOIN s01_ProductImages pi
								ON p.id = pi.product_id AND pi.type_id = 1
							LEFT JOIN s01_Images i
								ON pi.image_id = i.id
							WHERE
								pv.product_id = ? AND ipc.inventory > 0
							GROUP BY pvp.part_id
						'" />
						<mvt:assign name="l.settings:query:bind_parameters[1]" value="l.settings:product:id" />
						<mvt:item name="sql" param="l.settings:query" />

						<mvt:assign name="l.settings:product:variant:query" value="l.settings:query" />

						<mvt:if expr="miva_array_elements( l.settings:product:variant:query:results ) EQ 0 ">
							<mvt:assign name="l.settings:product:cf:total_stock_level" value="0" />
						<mvt:else>
							<mvt:foreach iterator="variant" array="product:variant:query:results">
								<mvt:assign name="l.settings:product:cf:total_stock_level" value="l.settings:product:cf:total_stock_level + l.settings:variant:inventory" />

								<mvt:if expr="l.settings:variant:color_prompt AND miva_array_find( l.settings:variant:color_prompt, l.settings:product:cf:variant_colors, 1 ) EQ 0">
									<mvt:assign name="l.index" value="miva_array_insert( l.settings:product:cf:variant_colors, l.settings:variant:color_prompt, -1 )" />
								</mvt:if>

								<mvt:if expr="l.settings:variant:size_prompt AND miva_array_find( l.settings:variant:size_prompt, l.settings:product:cf:variant_sizes, 1 ) EQ 0">
									<mvt:assign name="l.index" value="miva_array_insert( l.settings:product:cf:variant_sizes, l.settings:variant:size_prompt, -1 )" />
								</mvt:if>

							</mvt:foreach>
						</mvt:if>
				</mvt:if>

			<mvt:if expr="NOT ( l.settings:product:cf:total_stock_level GE 5 )">
				<mvt:if expr="g.output EQ 'debug'">
					Skipped [Low/Out of Stock]: &mvt:product:code;<hr>
				</mvt:if>
				<mvt:foreachcontinue/>
			</mvt:if>

			<mvt:comment><!-- Determing Feed Column Content --></mvt:comment>

				<mvt:assign name="l.settings:product:feed:link" value="'http://' $ g.domain:name $ '/product/' $ l.settings:product:code $ '.html'" />
				<mvt:assign name="l.settings:product:feed:title" value="l.settings:product:cf:Brand $ ' ' $ l.settings:product:name" />
				<mvt:comment><!-- Clean Description --></mvt:comment>
					<mvt:item name="toolkit" param="nohtml|l.all_settings:product:feed:description|l.all_settings:product:descrip" />
					<mvt:assign name="l.settings:product:feed:description" value="glosub( l.settings:product:feed:description, g.newline, '' )" />
					<mvt:assign name="l.settings:product:feed:description" value="glosub( l.settings:product:feed:description, g.tab, '' )" />
					<mvt:assign name="l.settings:product:feed:description" value="glosub( l.settings:product:feed:description, g.return, '' )" />
					<mvt:assign name="l.settings:product:feed:description" value="trim( l.settings:product:feed:description )" />
				<mvt:assign name="l.settings:product:feed:availability" value="'in stock'" />
				<mvt:assign name="l.settings:product:feed:gender" value="l.settings:product:cf:Style" />
				<mvt:assign name="l.settings:product:feed:age_group" value="l.settings:product:cf:age_group" />
				<mvt:assign name="l.settings:product:feed:gtin" value="l.settings:product:cf:gtin" />
				<mvt:assign name="l.settings:product:feed:image_link" value="'http://' $ g.domain:name $ '/mm5/' $ l.settings:product:images:Default" />
				<mvt:assign name="l.settings:product:feed:price" value="l.settings:product:price" />
				<mvt:assign name="l.settings:product:feed:id" value="l.settings:product:code" />
				<mvt:assign name="l.settings:product:feed:expiration_date" value="g.expiration_date" />
				<mvt:assign name="l.settings:product:feed:Brand" value="l.settings:product:cf:Brand" />
				<mvt:assign name="l.settings:product:feed:mpn" value="l.settings:product:cf:Vendor_Style" />
				<mvt:assign name="l.settings:product:feed:condition" value="'new'" />
				<mvt:assign name="l.settings:product:feed:product_type" value="l.settings:product:cf:product_type" />
				<mvt:assign name="l.settings:product:feed:google_product_category" value="l.settings:product:cf:google_product_category" />
				<mvt:assign name="l.settings:product:feed:weight" value="l.settings:product:weight" />
				<mvt:if expr="l.settings:product:cf:variant_colors">
					<mvt:assign name="l.settings:product:feed:color" value="l.settings:product:cf:variant_colors" />
				<mvt:else>
					<mvt:assign name="l.settings:product:feed:color" value="l.settings:product:cf:Color_Pattern" />
				</mvt:if>
				<mvt:if expr="l.settings:product:cf:variant_sizes">
					<mvt:assign name="l.settings:product:feed:size" value="l.settings:product:cf:variant_sizes" />
				<mvt:else>
					<mvt:assign name="l.settings:product:feed:size" value="l.settings:product:cf:Size_Search" />
				</mvt:if>
				<mvt:assign name="l.settings:product:feed:custom_label_0" value="l.settings:product:cf:custom_label_0" />
				<mvt:assign name="l.settings:product:feed:custom_label_1" value="l.settings:product:cf:custom_label_1" />
				<mvt:assign name="l.settings:product:feed:custom_label_2" value="l.settings:product:cf:custom_label_2" />

			<mvt:comment><!-- Output File Row --></mvt:comment>
				<mvt:if expr="g.output EQ 'debug'">
					<mvt:comment><!-- Dump file row to screen --></mvt:comment>
						<mvt:eval expr="decodeattribute(glosub(miva_array_serialize(l.settings:product), ',', '<br>'))" />
						<hr>
				<mvt:else>
					<mvt:comment><!-- Write row to file --></mvt:comment>
						<mvt:assign name="g.file_row" value="
							l.settings:product:feed:link									$ g.delimiter $
							l.settings:product:feed:title									$ g.delimiter $
							l.settings:product:feed:description								$ g.delimiter $
							l.settings:product:feed:availability							$ g.delimiter $
							l.settings:product:feed:gender									$ g.delimiter $
							l.settings:product:feed:age_group								$ g.delimiter $
							l.settings:product:feed:gtin									$ g.delimiter $
							l.settings:product:feed:image_link								$ g.delimiter $
							l.settings:product:feed:price									$ g.delimiter $
							l.settings:product:feed:id										$ g.delimiter $
							l.settings:product:feed:expiration_date							$ g.delimiter $
							l.settings:product:feed:brand									$ g.delimiter $
							l.settings:product:feed:mpn										$ g.delimiter $
							l.settings:product:feed:condition								$ g.delimiter $
							l.settings:product:feed:product_type							$ g.delimiter $
							l.settings:product:feed:google_product_category					$ g.delimiter $
							l.settings:product:feed:weight									$ g.delimiter $
							l.settings:product:feed:color									$ g.delimiter $
							l.settings:product:feed:size									$ g.delimiter $
							l.settings:product:feed:custom_label_0							$ g.delimiter $
							l.settings:product:feed:custom_label_1							$ g.delimiter $
							l.settings:product:feed:custom_label_2							$
							g.newline
						"/>
						<mvt:assign name="g.write_line" value="file_append( g.temp_file_path, 'script', g.file_row )"/>
						<mvt:if expr="g.continue EQ 'exit'">
							Write Line: &mvt:global:write_line;<br>
						</mvt:if>
				</mvt:if>

	</mvt:foreach>

<mvt:comment><!-- Pagination/Response for google-merchant Crawler --></mvt:comment>
	<mvt:assign name="g.current_url" value="'http://' $ g.domain:name $ '/' $ l.settings:page:code $ '.html?continue=' $ g.continue $ '&output=' $ g.output $ '&AllOffset=' $ g.AllOffset $ '&Per_Page=' $ g.Per_Page $ '&Product_Code=' $ g.Product_Code" />
	<mvt:assign name="g.next_url" value="'http://' $ g.domain:name $ '/' $ l.settings:page:code $ '.html?continue=' $ g.continue $ '&output=' $ g.output $ '&AllOffset=' $ g.AllNextOffset $ '&Per_Page=' $ g.Per_Page $ '&Product_Code=' $ g.Product_Code" />
	<mvt:assign name="g.percent_complete" value="rnd(l.settings:all_products:page_links:current_page/l.settings:all_products:page_links:last_page, 2) * 100" />
	<mvt:if expr="g.continue CIN 'exit|debug'">
		<a href="&mvt:global:next_url;">&mvt:global:next_url;</a><br>
		<progress value="&mvt:all_products:page_links:current_page;" max="&mvt:all_products:page_links:last_page;" style="width:100%; height: 50px"></progress>
	</mvt:if>

	<mvt:if expr="l.settings:all_products:page_links:current_page EQ l.settings:all_products:page_links:last_page">
		<mvt:comment><!-- Complete --></mvt:comment>
		<mvt:assign name="g.file_exists" value="sexists(g.temp_file_path)"/>
		<mvt:if expr="g.file_exists EQ 1">
			<mvt:assign name="g.file_renamed" value="srename(g.temp_file_path, g.final_file_path)"/>
		</mvt:if>
		<mvt:if expr="g.continue EQ 'refresh'">
			<!DOCTYPE html>
			<html lang="en">
				<head>
					<meta charset="UTF-8">
					<title>Generating... (Page &mvt:all_products:page_links:current_page; of &mvt:all_products:page_links:last_page;)</title>
					<meta http-equiv="refresh" content="1;url=&mvte:global:next_url;">
					<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.1/css/bootstrap.min.css">
				</head>
				<body>
					<div class="progress">
						<div class="progress-bar progress-bar-striped active" role="progressbar" aria-valuenow="&mvt:all_products:page_links:current_page;" aria-valuemin="1" aria-valuemax="&mvt:all_products:page_links:last_page;" style="width: &mvte:global:percent_complete;%">
							<span class="sr-only">&mvte:global:percent_complete;% Complete</span>
						</div>
					</div>
					<div class="alert alert-success">Complete</div>
					<a href="&mvte:global:final_file_path;" class="btn btn-default" download>Download</a><a href="&mvte:global:final_file_path;" class="btn btn-primary">View File</a>
				</body>
			</html>
		</mvt:if>
	<mvt:else>
		<mvt:comment><!-- Continue|&mvt:global:AllNextOffset; --></mvt:comment>
		<mvt:if expr="g.continue EQ 'redirect'">
			<mvt:assign name="l.header" value="miva_output_header( 'Status', '301 Moved Permanently' )" />
			<mvt:assign name="l.header" value="miva_output_header( 'Location', g.next_url )" />
		<mvt:elseif expr="g.continue EQ 'refresh'">
			<!DOCTYPE html>
			<html lang="en">
				<head>
					<meta charset="UTF-8">
					<title>Generating... (Page &mvt:all_products:page_links:current_page; of &mvt:all_products:page_links:last_page;)</title>
					<meta http-equiv="refresh" content="1;url=&mvte:global:next_url;">
					<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.1/css/bootstrap.min.css">
				</head>
				<body>
					<div class="progress">
						<div class="progress-bar progress-bar-striped active" role="progressbar" aria-valuenow="&mvt:all_products:page_links:current_page;" aria-valuemin="1" aria-valuemax="&mvt:all_products:page_links:last_page;" style="width: &mvte:global:percent_complete;%">
							<div class="text-right">&mvte:global:percent_complete;% Complete&nbsp;</div>
						</div>
					</div>
					<div class="alert alert-info">Processing...</div>
					Page &mvt:all_products:page_links:current_page; of &mvt:all_products:page_links:last_page;
				</body>
			</html>
		</mvt:if>
	</mvt:if>