· 5 years ago · Jun 03, 2020, 10:26 AM
1<cfcomponent displayname="AlsoImport" extends="ImportTransform">
2
3 <cfimport taglib="../../../../tools/ecommerce/" prefix="EC">
4
5 <cfscript>
6 This.DEBUG_MODE = False;
7 This.FILE_FORMAT = "DOS";
8 This.BATCH = 5000;
9
10 This.LANGUAGE = 'NL';
11 This.lOtherLanguages = '';
12 This.lAllLanguages = '';
13
14 This.SUPPLIER = "ALSO";
15 This.TAXCODE = Request.context.ec.qShop.shop_taxcode;
16 This.CURRENCY = Request.context.ec.qShop.shop_currency;
17 This.sDelimiter = ";";
18 This.oWriter = 0;
19 This.oXLink = 0;
20 This.nObjectsPerFileLimit = 20000;
21 This.sProductTable = "tmp_also_products";
22 This.sStockTable = "tmp_also_stock";
23
24 This.sProductsRichTable = "tmp_also_products_";
25 This.sProductResourceTable = "tmp_also_products_resource_";
26 This.sProductDescriptionTable = "tmp_also_products_description_";
27 This.sProductsTargetFile = "product-{lng}.txt";
28 This.sResourcesTargetFile = "resources-{lng}.txt";
29 This.sDescriptionsTargetFile = "text-{lng}.txt";
30
31 This.aDummyEnTranslations = StructNew();
32 This.aDummyEnTranslations['EN'] = ArrayNew(1);
33 This.aDummyEnTranslations['EN'] = ["no information", "no informations", "A translation is not available", "no translation available", "no text available"];
34 </cfscript>
35
36 <cffunction name="transformProduct" returnType="void" output="False">
37 <cfset sProductImportType = This.getVariable("product_import_type") />
38 <cfset This.LANGUAGE = (This.getVariable("default_language") NEQ '' ? This.getVariable("default_language") : 'NL' )/>
39 <cfset This.lOtherLanguages = This.getVariable("other_languages") />
40 <cfset This.lAllLanguages = ListAppend(This.LANGUAGE, This.lOtherLanguages, ',') />
41
42 <cfif sProductImportType EQ "plain">
43 <cfset createProductTable()>
44 <cfset importProducts()>
45 <cfelseif sProductImportType EQ "rich">
46 <cfset This.oXLink = Request.createCFC("sys.db.XLink")>
47 <cfset This.sXlinkSource = This.oConnect.qConnect.connect_client>
48 <cfset This.qXscope = This.oXLink.getXscope(This.oConnect.qConnect.connect_root_id)>
49 <cfset This.sXlinkTarget = This.qXscope.xscope_uuid>
50
51 <cfset createProductTableRich()>
52 <cfset createProductResourceTables()>
53 <cfset createProductDescriptionTables()>
54
55 <cfset importProductsRich()>
56 </cfif>
57 </cffunction>
58
59 <cffunction name="transformStock" returnType="void" output="False">
60 <cfset createStockTable()>
61 <cfset importStock()>
62 </cffunction>
63
64
65 <cffunction name="createProductTable" returnType="void" output="False">
66 <cfset var lsCSVFile = This.oConnect.sTargetDir & This.oConnect.getFiles().connectfile_targetfile>
67
68 <cfquery datasource="#Request.rc.connectdsn#">
69 DROP TABLE IF EXISTS #This.sProductTable#
70 </cfquery>
71
72 <cfquery datasource="#Request.rc.connectdsn#">
73 CREATE TABLE IF NOT EXISTS #This.sProductTable# (
74 `ProductID` VARCHAR(30) NOT NULL,
75 `ManufacturerPartNumber` VARCHAR(255) NOT NULL,
76 `ManufacturerName` VARCHAR(255) NOT NULL,
77 `EuropeanArticleNumber` VARCHAR(255) NOT NULL,
78 `Description` VARCHAR(255) NOT NULL,
79 `AvailableQuantity` VARCHAR(10) NOT NULL,
80 `NetPrice` VARCHAR(10) NOT NULL,
81 `NetRetailPrice` VARCHAR(10) NOT NULL,
82 `CategoryText1` VARCHAR(50) NOT NULL,
83 `CategoryText2` VARCHAR(50) NOT NULL,
84 `CategoryText3` VARCHAR(50) NOT NULL,
85 `EndOfLife` VARCHAR(30) NOT NULL,
86 `GrossMass` VARCHAR(10) NOT NULL,
87 `CopyrightFee` VARCHAR(10) NOT NULL,
88 `ShortDescription` VARCHAR(255) NOT NULL,
89 `VatRate` VARCHAR(10) NOT NULL,
90 `CategoryID` VARCHAR(10) NOT NULL,
91 PRIMARY KEY (`ProductID`)
92 )
93 </cfquery>
94
95 <cfquery datasource="#Request.rc.connectdsn#">
96 LOAD DATA LOCAL INFILE '#Replace(Replace(lsCSVFile, "/", "\", "ALL"), "\", "\\", "ALL")#'
97 IGNORE INTO TABLE `#This.sProductTable#`
98 FIELDS TERMINATED BY ';' ENCLOSED BY '"'
99 LINES TERMINATED BY 0x0A
100 IGNORE 1 LINES
101 </cfquery>
102 </cffunction>
103
104 <cffunction name="createStockTable" returnType="void" output="False">
105 <cfset var lsCSVFile = This.oConnect.sTargetDir & This.oConnect.getFiles().connectfile_targetfile>
106
107 <cfquery datasource="#Request.rc.connectdsn#">
108 DROP TABLE IF EXISTS #This.sStockTable#
109 </cfquery>
110
111 <cfquery datasource="#Request.rc.connectdsn#">
112 CREATE TABLE IF NOT EXISTS #This.sStockTable# (
113 `ProductID` VARCHAR(30) NOT NULL,
114 `AvailableQuantity` VARCHAR(10) NOT NULL,
115 `AvailableNextDate` VARCHAR(10) NOT NULL,
116 `AvailableNextQuantity` VARCHAR(10) NOT NULL,
117 `AvailabilityDate` VARCHAR(10) NOT NULL,
118 `AvailabilityTime` VARCHAR(10) NOT NULL,
119 PRIMARY KEY (`ProductID`)
120 )
121 </cfquery>
122
123 <cfquery datasource="#Request.rc.connectdsn#">
124 LOAD DATA LOCAL INFILE '#Replace(Replace(lsCSVFile, "/", "\", "ALL"), "\", "\\", "ALL")#'
125 IGNORE INTO TABLE `#This.sStockTable#`
126 FIELDS TERMINATED BY '\t'
127 LINES TERMINATED BY 0x0A
128 IGNORE 1 LINES
129 </cfquery>
130 </cffunction>
131
132 <cffunction name="importProducts" returnType="void" output="False">
133
134 <cfquery name="lqProduct" datasource="#Request.rc.connectdsn#">
135 SELECT * FROM #This.sProductTable#
136 ORDER BY CategoryID ASC
137 </cfquery>
138
139 <cfset This.oWriter = Request.createCFC("sys.io.XmlWriter")>
140 <cfset This.lsFilename = This.oConnect.getFilename()>
141
142 <cfset This.oWriter.openFile(This.lsFilename)>
143 <cfset This.oWriter.header(client: This.oConnect.qConnect.connect_client)>
144 <cfset This.oWriter.open('children')>
145
146 <cfset lsLev1 = "">
147 <cfset lsLev2 = "">
148 <cfset lsLev3 = "">
149 <cfloop query="lqProduct">
150
151 <cfset lsNewLev1 = Mid(lqProduct.CategoryID, 1, 3)>
152 <cfset lsNewLev2 = Mid(lqProduct.CategoryID, 1, 6)>
153 <cfset lsNewLev3 = lqProduct.CategoryID>
154
155 <cfif lsLev1 NEQ "" AND lsLev1 NEQ lsNewLev1>
156 <cfset This.oWriter.close('children')>
157 <cfset This.oWriter.close('folder')>
158 </cfif>
159 <cfif lsLev2 NEQ "" AND lsLev2 NEQ lsNewLev2>
160 <cfset This.oWriter.close('children')>
161 <cfset This.oWriter.close('folder')>
162 </cfif>
163 <cfif lsLev3 NEQ "" AND lsLev3 NEQ lsNewLev3>
164 <cfset This.oWriter.close('children')>
165 <cfset This.oWriter.close('folder')>
166 </cfif>
167
168 <cfif lsLev1 NEQ lsNewLev1>
169 <cfset This.oWriter.open('folder', 'id="#lsNewLev1#" language="#This.LANGUAGE#"')>
170 <cfset This.oWriter.string('externalid', '#lsNewLev1#')>
171 <cfset This.oWriter.cdata('name', lqProduct.CategoryText1)>
172
173 <cfset This.oWriter.open('children')>
174 <cfset lsLev1 = lsNewLev1>
175 </cfif>
176
177 <cfif lsLev2 NEQ lsNewLev2>
178 <cfset This.oWriter.open('folder', 'id="#lsNewLev2#" language="#This.LANGUAGE#"')>
179 <cfset This.oWriter.string('externalid', '#lsNewLev2#')>
180 <cfset This.oWriter.cdata('name', lqProduct.CategoryText2)>
181
182 <cfset This.oWriter.open('children')>
183 <cfset lsLev2 = lsNewLev2>
184 </cfif>
185
186 <cfif lsLev3 NEQ lsNewLev3>
187 <cfset This.oWriter.open('folder', 'id="#lsNewLev3#" language="#This.LANGUAGE#"')>
188 <cfset This.oWriter.string('externalid', '#lsNewLev3#')>
189 <cfset This.oWriter.cdata('name', lqProduct.CategoryText3)>
190
191 <cfset This.oWriter.open('children')>
192 <cfset lsLev3 = lsNewLev3>
193 </cfif>
194
195 <cfset This.oWriter.open('product', 'id="#Trim(Request.XmlFormat2(lqProduct.ProductID))#" language="#This.LANGUAGE#"')>
196 <cfset This.oWriter.string('externalid', Trim(Request.XmlFormat2(lqProduct.ProductID)))>
197 <cfset This.oWriter.cdata('name', Trim(lqProduct.ShortDescription))>
198 <!---<cfset This.oWriter.cdata('description', Trim(lqProduct.Description))>--->
199 <cfset This.oWriter.string('code', Trim(lqProduct.ManufacturerPartNumber))>
200 <cfset This.oWriter.string('supplier', This.SUPPLIER)>
201 <cfset This.oWriter.string('suppliercode', Trim(Request.XmlFormat2(lqProduct.ProductID)))>
202 <cfset This.oWriter.string('taxcode', getTaxCode(lqProduct.VatRate))>
203 <cfset This.oWriter.string('currency', This.CURRENCY)>
204 <cfset This.oWriter.string('status', 'A')>
205 <cfset This.oWriter.string('costprice', lqProduct.NetPrice)>
206 <cfset This.oWriter.string('suggestedprice', lqProduct.NetRetailPrice)>
207 <cfset This.oWriter.string('manufacturer', Trim(lqProduct.ManufacturerName))>
208 <cfset This.oWriter.string('oemcode', Trim(lqProduct.ManufacturerPartNumber))>
209 <cfset This.oWriter.string('eancode', Trim(lqProduct.EuropeanArticleNumber))>
210 <cfset This.oWriter.close('product')>
211 </cfloop>
212
213 <cfset This.oWriter.close('children')>
214 <cfset This.oWriter.close('folder')>
215 <cfset This.oWriter.close('children')>
216 <cfset This.oWriter.close('folder')>
217 <cfset This.oWriter.close('children')>
218 <cfset This.oWriter.close('folder')>
219
220 <cfset This.oWriter.close('children')>
221 <cfset This.oWriter.close('rightclick')>
222 <cfset This.oWriter.closeFile()>
223 </cffunction>
224
225 <cffunction name="importStock" returnType="void" output="False">
226
227 <cfquery name="lqStock" datasource="#Request.rc.connectdsn#">
228 SELECT stock.AvailableQuantity, stock.ProductID, xlink_class_id
229 FROM #This.sStockTable# stock, #Request.rc.dsn#.xlink
230 WHERE
231 stock.ProductID = xlink_id
232 AND xlink_source = '#This.oConnect.qConnect.connect_client#'
233 AND xlink_language = '#This.LANGUAGE#'
234 AND xlink_class = 'product'
235 </cfquery>
236
237 <cfloop query="lqStock">
238 <cfset oStock = Request.createCFC("mod.ec.Stock").init(lqStock.xlink_class_id, This.SUPPLIER, lqStock.ProductID)>
239 <cfset oStock.setAbsoluteQuantity(quantity: lqStock.AvailableQuantity)>
240 </cfloop>
241
242 </cffunction>
243
244 <cffunction name="createProductTableRich" returnType="void" output="False">
245 <cfloop list="#This.lAllLanguages#" index="sLang">
246 <cfset var lsCSVFile = This.oConnect.sTargetDir & replace(This.sProductsTargetFile, "{lng}", LCase(sLang))>
247
248 <cfquery datasource="#Request.rc.connectdsn#">
249 DROP TABLE IF EXISTS #This.sProductsRichTable##LCase(sLang)#
250 </cfquery>
251
252 <cfquery datasource="#Request.rc.connectdsn#">
253 CREATE TABLE IF NOT EXISTS #This.sProductsRichTable##LCase(sLang)# (
254 `ProductID` VARCHAR(30) NOT NULL,
255 `CategoryText1` VARCHAR(50) NOT NULL,
256 `CategoryText2` VARCHAR(50) NOT NULL,
257 `CategoryText3` VARCHAR(50) NOT NULL,
258 `Description` VARCHAR(255) NOT NULL,
259 `MarketingTextID` VARCHAR(30) NULL,
260 `DatasheetUrl` VARCHAR(255) NULL,
261 `StandardImgage` VARCHAR(255) NULL,
262 PRIMARY KEY (`ProductID`)
263 )
264 </cfquery>
265
266 <cfif FileExists(lsCSVFile)>
267 <cfquery datasource="#Request.rc.connectdsn#">
268 LOAD DATA LOCAL INFILE '#Replace(Replace(lsCSVFile, "/", "\", "ALL"), "\", "\\", "ALL")#'
269 IGNORE INTO TABLE `#This.sProductsRichTable##LCase(sLang)#`
270 FIELDS TERMINATED BY '\t'
271 LINES TERMINATED BY 0x0A
272 IGNORE 1 LINES
273 </cfquery>
274 </cfif>
275
276 <cfquery datasource="#Request.rc.connectdsn#">
277 ALTER TABLE #This.sProductsRichTable##LCase(sLang)#
278 ADD INDEX `ProductID`(`ProductID`)
279 </cfquery>
280 </cfloop>
281 </cffunction>
282
283 <cffunction name="createProductResourceTables" returnType="void" output="False">
284 <cfloop list="#This.lAllLanguages#" index="sLang">
285 <cfset var lsCSVFile = This.oConnect.sTargetDir & replace(This.sResourcesTargetFile, "{lng}", LCase(sLang))>
286
287 <cfquery datasource="#Request.rc.connectdsn#">
288 DROP TABLE IF EXISTS #This.sProductResourceTable##LCase(sLang)#
289 </cfquery>
290
291 <cfquery datasource="#Request.rc.connectdsn#">
292 CREATE TABLE IF NOT EXISTS #This.sProductResourceTable##LCase(sLang)# (
293 `ProductID` VARCHAR(30) NOT NULL,
294 `ResourceID` VARCHAR(30) NOT NULL,
295 `Description` VARCHAR(255) NOT NULL,
296 `URI` VARCHAR(255) NOT NULL,
297 `MimeType` VARCHAR(30) NOT NULL,
298 `ImageID` VARCHAR(30) NOT NULL,
299 `Width` VARCHAR(50) NOT NULL,
300 `Height` VARCHAR(50) NOT NULL,
301 PRIMARY KEY (`ResourceID`)
302 )
303 </cfquery>
304
305 <cfif FileExists(lsCSVFile)>
306 <cfquery datasource="#Request.rc.connectdsn#">
307 LOAD DATA LOCAL INFILE '#Replace(Replace(lsCSVFile, "/", "\", "ALL"), "\", "\\", "ALL")#'
308 IGNORE INTO TABLE `#This.sProductResourceTable##LCase(sLang)#`
309 FIELDS TERMINATED BY '\t'
310 LINES TERMINATED BY 0x0A
311 IGNORE 1 LINES
312 </cfquery>
313 </cfif>
314
315 <cfquery datasource="#Request.rc.connectdsn#">
316 ALTER TABLE #This.sProductResourceTable##LCase(sLang)#
317 ADD INDEX `ResourceID`(`ResourceID`),
318 ADD INDEX `ProductID`(`ProductID`),
319 ADD INDEX `ImageID`(`ImageID`)
320 </cfquery>
321 </cfloop>
322 </cffunction>
323
324 <cffunction name="createProductDescriptionTables" returnType="void" output="False">
325 <cfloop list="#This.lAllLanguages#" index="sLang">
326 <cfset var lsCSVFile = This.oConnect.sTargetDir & replace(This.sDescriptionsTargetFile, "{lng}", LCase(sLang))>
327
328 <cfquery datasource="#Request.rc.connectdsn#">
329 DROP TABLE IF EXISTS #This.sProductDescriptionTable##LCase(sLang)#
330 </cfquery>
331
332 <cfquery datasource="#Request.rc.connectdsn#">
333 CREATE TABLE IF NOT EXISTS #This.sProductDescriptionTable##LCase(sLang)# (
334 `ID` VARCHAR(30) NOT NULL,
335 `Text` TEXT NOT NULL,
336 PRIMARY KEY (`ID`)
337 )
338 </cfquery>
339
340 <cfif FileExists(lsCSVFile)>
341 <cfquery datasource="#Request.rc.connectdsn#">
342 LOAD DATA LOCAL INFILE '#Replace(Replace(lsCSVFile, "/", "\", "ALL"), "\", "\\", "ALL")#'
343 IGNORE INTO TABLE `#This.sProductDescriptionTable##LCase(sLang)#`
344 FIELDS TERMINATED BY '\t'
345 LINES TERMINATED BY 0x0A
346 IGNORE 1 LINES
347 </cfquery>
348 </cfif>
349
350 <cfquery datasource="#Request.rc.connectdsn#">
351 ALTER TABLE #This.sProductDescriptionTable##LCase(sLang)#
352 ADD INDEX `ID`(`ID`)
353 </cfquery>
354 </cfloop>
355 </cffunction>
356
357 <cffunction name="buildSelectProductsQuery" returnType="string" output="False">
358 <!--- define query templates for selecting products and descriptions --->
359 <cfset sDefaultProdictIdQry = " `#This.sProductsRichTable##LCase(This.LANGUAGE)#`.`ProductID` " />
360 <cfset sDefaultFromProductQry = " `#This.sProductsRichTable##LCase(This.LANGUAGE)#` " />
361 <cfset sSelectProdDescQry = "`#This.sProductsRichTable#{lng}`.`Description` AS `ShortDescription_{lng}`,
362 `#This.sProductsRichTable#{lng}`.`MarketingTextID` AS `MarketingTextID_{lng}`,
363 `#This.sProductsRichTable#{lng}`.`StandardImgage` AS `StandardImgage_{lng}`,
364 `#This.sProductDescriptionTable#{lng}`.`ID` AS `DescriptionID_{lng}`,
365 `#This.sProductDescriptionTable#{lng}`.`Text` AS `Description_{lng}`, " />
366 <cfset sJoinDescrQry = " LEFT JOIN `#This.sProductDescriptionTable#{lng}` ON `#This.sProductsRichTable#{lng}`.`MarketingTextID` = `#This.sProductDescriptionTable#{lng}`.`ID` " />
367 <cfset sJoinProductQry = " LEFT JOIN `#This.sProductsRichTable#{lng}` ON `#This.sProductsRichTable##LCase(This.LANGUAGE)#`.`ProductID` = `#This.sProductsRichTable#{lng}`.`ProductID` " />
368
369 <!--- set default languages queries --->
370 <cfset sSelectProdDescQryDefaultLang = replace(sSelectProdDescQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
371 <cfset sJoinDescQryDefaultLang = replace(sJoinDescrQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
372
373 <cfset aSelectProdDescQrys = ArrayNew(1) />
374 <cfset aJoinProductQrys = ArrayNew(1) />
375 <cfset aJoinDescQrys = ArrayNew(1) />
376
377 <!--- append default language queries to corresponding arrays --->
378 <cfset ArrayAppend(aSelectProdDescQrys, sSelectProdDescQryDefaultLang, true) />
379 <cfset ArrayAppend(aJoinDescQrys, sJoinDescQryDefaultLang, true) />
380
381 <!--- append other languages queries to corresponding arrays --->
382 <cfloop list="#This.lOtherLanguages#" index="sLang">
383 <cfset ArrayAppend(aSelectProdDescQrys, replace(sSelectProdDescQry, "{lng}", LCase(sLang), "ALL"), true) />
384 <cfset ArrayAppend(aJoinProductQrys, replace(sJoinProductQry, "{lng}", LCase(sLang), "ALL"), true) />
385 <cfset ArrayAppend(aJoinDescQrys, replace(sJoinDescrQry, "{lng}", LCase(sLang), "ALL"), true) />
386 </cfloop>
387
388 <!--- Build main query --->
389 <cfset sMainQry = "SELECT " />
390
391 <cfloop array="#aSelectProdDescQrys#" item="sel">
392 <cfset sMainQry &= sel />
393 </cfloop>
394
395 <cfset sMainQry &= sDefaultProdictIdQry />
396 <cfset sMainQry &= "FROM " & sDefaultFromProductQry />
397
398 <cfloop array="#aJoinProductQrys#" item="sel">
399 <cfset sMainQry &= sel />
400 </cfloop>
401 <cfloop array="#aJoinDescQrys#" item="sel">
402 <cfset sMainQry &= sel />
403 </cfloop>
404
405 <cfreturn sMainQry />
406 </cffunction>
407
408 <cffunction name="importProductsRich" returnType="void" output="False">
409 <cfset var lsFilename = This.oConnect.getFilename()>
410 <cfset var sqSelectProductsRich = buildSelectProductsQuery() />
411
412 <cfquery name="lqProduct" datasource="#Request.rc.connectdsn#">
413 #sqSelectProductsRich#
414 </cfquery>
415
416 <cfset This.oWriter = Request.createCFC("sys.io.XmlWriter")>
417
418 <cfset lnCount = ceiling(lqProduct.recordcount / This.BATCH)>
419
420 <cfloop from="1" to="#lnCount#" index="lnFileIndex">
421 <cftry>
422 <cfset lnStart = (lnFileIndex * This.BATCH) - This.BATCH + 1>
423 <cfset lnEnd = (lnFileIndex * This.BATCH)>
424
425 <cfset This.oWriter.openFile(lsFilename & lnFileIndex)>
426
427 <!--- write default RightClick header --->
428 <cfset This.oWriter.header(client: This.oConnect.qConnect.connect_client)>
429 <cfset This.oWriter.open('children')>
430
431 <cfset importProductsRichBatch(lqProduct , lnStart, lnEnd)>
432
433 <!--- write closing tags --->
434 <cfset This.oWriter.close('children')>
435 <cfset This.oWriter.close('rightclick')>
436 <cfset This.oWriter.closeFile()>
437 <cfcatch>
438 <!--- finalize --->
439 <cfset This.oWriter.closeFile()>
440 <cfrethrow>
441 </cfcatch>
442 </cftry>
443 </cfloop>
444
445 <cfset WriteIndexFile(fileName: lsFilename, count: lnCount)>
446 </cffunction>
447
448 <cffunction name="WriteIndexFile" returnType="void" output="false">
449 <cfargument name="filename" type="string" required="true">
450 <cfargument name="count" type="numeric" required="true">
451
452 <cfset var lsFilename = "">
453 <cfset var lnIndex = 0>
454
455 <cftry>
456 <cfset This.oWriter = Request.createCFC("sys.io.XmlWriter")>
457 <cfset This.oWriter.init(filename: Arguments.filename)>
458 <cfset This.oWriter.open('rightclick')>
459
460 <cfloop from="1" to="#Arguments.count#" index="lnIndex">
461 <cfset lsFilename = Arguments.filename & lnIndex>
462 <cfset This.oWriter.string('filelist', lsFilename)>
463 </cfloop>
464
465 <cfset This.oWriter.close('rightclick')>
466 <cfset This.oWriter.closeFile()>
467
468 <cfcatch>
469 <cfset This.oWriter.closeFile()>
470 <cfrethrow>
471 </cfcatch>
472
473 </cftry>
474 </cffunction>
475
476 <cffunction name="buildResourcesQuery" returnType="string" output="False">
477 <cfargument name="product_id" type="numeric" required="true">
478
479 <cfset sSelectResQry = "`#This.sProductResourceTable#{lng}`.`ProductID` AS `ProductID_{lng}`,
480 `#This.sProductResourceTable#{lng}`.`ResourceID` AS `ResourceID_{lng}`,
481 `#This.sProductResourceTable#{lng}`.`ImageID` AS `ImageID_{lng}`,
482 `#This.sProductResourceTable#{lng}`.`Description` AS `ResourceDescription_{lng}`,
483 `#This.sProductResourceTable#{lng}`.`URI` AS `ResourceURI_{lng}`,
484 `#This.sProductResourceTable#{lng}`.`MimeType` AS `MimeType_{lng}`, " />
485 <cfset sDefaultFromResQry = " `#This.sProductResourceTable##LCase(This.LANGUAGE)#` " />
486 <cfset sJoinResQry = " LEFT JOIN `#This.sProductResourceTable#{lng}`
487 ON `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ResourceID` = `#This.sProductResourceTable#{lng}`.`ResourceID`
488 AND `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ProductID` = `#This.sProductResourceTable#{lng}`.`ProductID`
489 AND `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ImageID` = `#This.sProductResourceTable#{lng}`.`ImageID` " />
490 <cfset sWhereQry = " `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ProductID` = #Arguments.product_id#" />
491 <cfset sAndWhereImgSizeQry = " AND (`tmp_also_products_resource_{lng}`.`Width` = (
492 SELECT MAX(CAST(`r`.`Width` AS INT))
493 FROM `tmp_also_products_resource_{lng}` `r`
494 WHERE `r`.`ImageID` = `tmp_also_products_resource_{lng}`.`ImageID`
495 AND `r`.`ProductID` = `tmp_also_products_resource_{lng}`.`ProductID`
496 ) OR `tmp_also_products_resource_{lng}`.`Width` = '') " />
497 <cfset sWhereIsImage = " AND `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`MimeType` = 'image/jpeg' " />
498 <cfset sWhereIsNotImage = " AND `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`MimeType` <> 'image/jpeg' " />
499 <cfset sGroupByQry = " `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`ImageID` " />
500
501 <cfset aSelectResQrys = ArrayNew(1) />
502 <cfset aJoinResQrys = ArrayNew(1) />
503 <cfset aImgSizeQrys = ArrayNew(1) />
504
505 <cfset sSelectResDefaultLang = replace(sSelectResQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
506 <cfset ArrayAppend(aSelectResQrys, sSelectResDefaultLang, true) />
507
508 <cfset sAndWhereImgSizeQryDefaultLang = replace(sAndWhereImgSizeQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
509 <cfset ArrayAppend(aImgSizeQrys, sAndWhereImgSizeQryDefaultLang, true) />
510
511 <cfloop list="#This.lOtherLanguages#" index="sLang">
512 <cfset ArrayAppend(aSelectResQrys, replace(sSelectResQry, "{lng}", LCase(sLang), "ALL"), true) />
513 <cfset ArrayAppend(aJoinResQrys, replace(sJoinResQry, "{lng}", LCase(sLang), "ALL"), true) />
514 </cfloop>
515
516 <cfset sMainQry = "SELECT " />
517
518 <cfloop array="#aSelectResQrys#" item="sel">
519 <cfset sMainQry &= sel />
520 </cfloop>
521
522 <cfset sMainQry &= " 1 AS `Dummy` " />
523 <cfset sMainQry &= " FROM " & sDefaultFromResQry />
524
525 <cfloop array="#aJoinResQrys#" item="sel">
526 <cfset sMainQry &= sel />
527 </cfloop>
528
529 <cfset sMainQry &= " WHERE " & sWhereQry />
530 <cfset sMainQry &= sWhereIsImage />
531 <cfloop array="#aImgSizeQrys#" item="siz">
532 <cfset sMainQry &= siz />
533 </cfloop>
534
535 <cfset sMainQry &= " GROUP BY " & sGroupByQry />
536
537 <cfset sMainQry &= " UNION " />
538
539 <cfset sMainQry &= "SELECT " />
540
541 <cfloop array="#aSelectResQrys#" item="sel">
542 <cfset sMainQry &= sel />
543 </cfloop>
544
545 <cfset sMainQry &= " 1 AS `Dummy` " />
546 <cfset sMainQry &= " FROM " & sDefaultFromResQry />
547
548 <cfloop array="#aJoinResQrys#" item="sel">
549 <cfset sMainQry &= sel />
550 </cfloop>
551
552 <cfset sMainQry &= " WHERE " & sWhereQry />
553 <cfset sMainQry &= sWhereIsNotImage />
554
555 <cfreturn sMainQry />
556 </cffunction>
557
558 <cffunction name="importProductsRichBatch">
559 <cfargument name="query" type="query" required="true">
560 <cfargument name="startrow" type="numeric">
561 <cfargument name="endrow">
562
563 <cfset var lqProduct = arguments.query>
564
565 <cfloop query="lqProduct" startrow="#arguments.startrow#" endrow="#arguments.endrow#">
566 <cfquery name="lqProductResources" datasource="#Request.rc.connectdsn#">
567 #buildResourcesQuery(lqProduct.ProductID)#
568 </cfquery>
569
570 <cflog text="#lqProductResources.toString()#" file="lqProductResources.txt" />
571
572 <cfset This.oWriter.open('product', 'id="#Trim(Request.XmlFormat2(lqProduct.ProductID))#"')>
573 <cfset This.oWriter.string('externalid', Trim(Request.XmlFormat2(lqProduct.ProductID)))>
574
575 <cfif len(lqProduct["ShortDescription_#LCase(This.LANGUAGE)#"])>
576 <cfset This.oWriter.cdata('shortdescription', Trim(lqProduct["ShortDescription_#LCase(This.LANGUAGE)#"]))>
577 <cfset This.oWriter.cdata('shortdescription-plain', ReReplaceNoCase(#Trim(Evaluate("lqProduct['ShortDescription_#LCase(This.LANGUAGE)#']"))#, "<[^>]*>", "", "ALL"))>
578 </cfif>
579
580 <cfif len(lqProduct["Description_#LCase(This.LANGUAGE)#"])>
581 <cfset This.oWriter.cdata('description', Trim(lqProduct["Description_#LCase(This.LANGUAGE)#"]))>
582 <cfset This.oWriter.cdata('description-plain', ReReplaceNoCase(#Trim(Evaluate("lqProduct['Description_#LCase(This.LANGUAGE)#']"))#, "<[^>]*>", "", "ALL"))>
583 </cfif>
584
585 <cfset This.oWriter.open('relations')>
586 <cfif len(lqProduct.StandardImgage_nl)>
587 <cfset This.oWriter.open('relation', 'type="image/normal"')>
588 <cfset This.oWriter.open('image', 'language="#This.LANGUAGE#" id="main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#" default="Y" download="false"')>
589 <cfset This.oWriter.cdata('externalid', "main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#")>
590 <cfset This.oWriter.cdata('name', "Standard Image")>
591 <cfset This.oWriter.cdata('url', Trim(lqProduct["StandardImgage_#LCase(This.LANGUAGE)#"]))>
592
593 <cfif ListLen(This.lOtherLanguages) GT 0>
594 <cfset This.oWriter.open('relations')>
595 <cfloop list="#This.lOtherLanguages#" index="sLang">
596 <cfif len(lqProduct["StandardImgage_#LCase(sLang)#"])>
597 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
598 <cfset This.oWriter.open('image', 'language="#sLang#" id="main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#" download="false"')>
599 <cfset This.oWriter.cdata('externalid', "main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#")>
600 <cfset This.oWriter.cdata('name', "Standard Image")>
601 <cfset This.oWriter.cdata('url', Trim(lqProduct["StandardImgage_#LCase(sLang)#"]))>
602 <cfset This.oWriter.close('image')>
603 <cfset This.oWriter.close('relation')>
604 </cfif>
605 </cfloop>
606 <cfset This.oWriter.close('relations')>
607 </cfif>
608 <cfset This.oWriter.close('image')>
609 <cfset This.oWriter.close('relation')>
610 </cfif>
611
612 <cfif lqProductResources.RecordCount GT 0>
613 <cfloop query="lqProductResources">
614 <cfif lqProductResources["MimeType_#LCase(This.LANGUAGE)#"] EQ "image/jpeg" OR lqProductResources["MimeType_#LCase(This.LANGUAGE)#"] EQ "image/png">
615 <cfif len(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"])>
616 <cfset This.oWriter.open('relation', 'type="image/normal"')>
617 <cfset nDefaultLangImgImageID = lqProductResources["ImageID_#LCase(This.LANGUAGE)#"] />
618
619 <cfset This.oWriter.open('image', 'language="#This.LANGUAGE#" id="img-#Trim(nDefaultLangImgImageID)#" download="false"')>
620 <cfset This.oWriter.cdata('externalid', "img-#Trim(nDefaultLangImgImageID)#")>
621 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"]))>
622 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"]))>
623 <cfif ListLen(This.lOtherLanguages) GT 0>
624 <cfset This.oWriter.open('relations')>
625 <cfloop list="#This.lOtherLanguages#" index="sLang">
626 <cfif len(lqProductResources["ResourceURI_#LCase(sLang)#"])>
627 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
628 <cfset nLangImgImageID = lqProductResources["ImageID_#LCase(sLang)#"] />
629
630 <cfset This.oWriter.open('image', 'language="#sLang#" id="img-#Trim(nLangImgImageID)#-#sLang#" download="false"')>
631 <cfset This.oWriter.cdata('externalid', "img-#Trim(nLangImgImageID)#-#sLang#")>
632 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(sLang)#"]))>
633 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(sLang)#"]))>
634 <cfset This.oWriter.close('image')>
635 <cfset This.oWriter.close('relation')>
636 </cfif>
637 </cfloop>
638 <cfset This.oWriter.close('relations')>
639 </cfif>
640 <cfset This.oWriter.close('image')>
641 <cfset This.oWriter.close('relation')>
642 </cfif>
643 <cfelseif lqProductResources["MimeType_#LCase(This.LANGUAGE)#"] EQ "application/pdf">
644 <cfset sDocumentType = ''>
645
646 <cfset sResDesc = lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"]>
647 <cfswitch expression="#sResDesc#">
648 <cfcase value="Data Sheet">
649 <cfset sDocumentType = 'document/normal'>
650 </cfcase>
651 <cfcase value="User Manual">
652 <cfset sDocumentType = 'document/manual'>
653 </cfcase>
654 <cfcase value="Quick Start Guide">
655 <cfset sDocumentType = 'document/normal'>
656 </cfcase>
657 </cfswitch>
658
659 <cflog text="#lqProductResources.toString()#" file="lqProductResources1.txt" />
660
661 <cfset This.oWriter.open('relation', 'type="#sDocumentType#"')>
662 <cfset nDefaultLangDocResourceID = lqProductResources["ResourceID_#LCase(This.LANGUAGE)#"] />
663
664 <cfset This.oWriter.open('document', 'language="#This.LANGUAGE#" subclass="pdf" id="doc-#Trim(nDefaultLangDocResourceID)#"')>
665 <cfset This.oWriter.cdata('externalid', "doc-#Trim(nDefaultLangDocResourceID)#")>
666 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"]))>
667 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"]))>
668
669 <cfif ListLen(This.lOtherLanguages) GT 0>
670 <cfset This.oWriter.open('relations')>
671 <cfloop list="#This.lOtherLanguages#" index="sLang">
672 <cfif len(lqProductResources["ResourceURI_#LCase(sLang)#"])>
673 <cfset nLangDocResourceID = lqProductResources["ResourceID_#LCase(sLang)#"] />
674
675 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
676 <cfset This.oWriter.open('document', 'language="#sLang#" subclass="pdf" id="doc-#Trim(nLangDocResourceID)#-#sLang#"')>
677 <cfset This.oWriter.cdata('externalid', "doc-#Trim(nLangDocResourceID)#-#sLang#")>
678 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(sLang)#"]))>
679 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(sLang)#"]))>
680 <cfset This.oWriter.close('document')>
681 <cfset This.oWriter.close('relation')>
682 </cfif>
683 </cfloop>
684 <cfset This.oWriter.close('relations')>
685 </cfif>
686
687 <cfset This.oWriter.close('document')>
688 <cfset This.oWriter.close('relation')>
689 </cfif>
690 </cfloop>
691 </cfif>
692
693 <cfloop list="#This.lOtherLanguages#" index="sLang">
694 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
695 <cfset This.oWriter.open('product', 'id="#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#" language="#sLang#"')>
696 <cfset This.oWriter.string('externalid', "#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#")>
697
698 <cfif NOT isSimpleValue(This.oXLink)>
699 <cfset lqProductXlink = This.oXLink.lookup(lqProduct.ProductID, This.sXlinkSource, This.sXlinkTarget, This.Language, 'product')>
700 <cfif isDefined("lqProductXlink") AND lqProductXlink.RecordCount>
701 <cfset This.oWriter.cdata('name', Request.oFactory.get(lqProductXlink.xlink_directory_id).qget('name'))>
702 </cfif>
703 </cfif>
704
705 <cfif len(lqProduct["ShortDescription_#LCase(sLang)#"])>
706 <cfset This.oWriter.cdata('shortdescription', Trim(lqProduct["ShortDescription_#LCase(sLang)#"]))>
707 <cfset This.oWriter.cdata('shortdescription-plain', ReReplaceNoCase(#Trim(lqProduct["ShortDescription_#LCase(sLang)#"])#, "<[^>]*>", "", "ALL"))>
708 </cfif>
709
710 <cfif len(lqProduct["Description_#LCase(sLang)#"]) AND NOT ArrayContainsNoCase(This.aDummyEnTranslations[sLang], lqProduct["Description_#LCase(sLang)#"])>
711 <cfset This.oWriter.cdata('description', Trim(lqProduct["Description_#LCase(sLang)#"]))>
712 <cfset This.oWriter.cdata('description-plain', ReReplaceNoCase(#Trim(lqProduct["Description_#LCase(sLang)#"])#, "<[^>]*>", "", "ALL"))>
713 </cfif>
714 <cfset This.oWriter.close('product')>
715 <cfset This.oWriter.close('relation')>
716 </cfloop>
717 <cfset This.oWriter.close('relations')>
718 <cfset This.oWriter.close('product')>
719 </cfloop>
720 </cffunction>
721
722 <cffunction name="getTaxCode" returnType="string" output="False">
723 <cfargument name="lsTaxValue" type="string" required="true">
724 <cfset lsTaxCode = "N">
725
726 <cfswitch expression="#Trim(lsTaxValue)#">
727 <cfcase value="21">
728 <cfset lsTaxCode = "H">
729 </cfcase>
730 <cfcase value="9">
731 <cfset lsTaxCode = "L">
732 </cfcase>
733 <cfcase value="6">
734 <cfset lsTaxCode = "L">
735 </cfcase>
736 <cfcase value="0">
737 <cfset lsTaxCode = "N">
738 </cfcase>
739 <cfdefaultcase>
740 <cfset lsTaxCode = "N">
741 </cfdefaultcase>
742 </cfswitch>
743
744 <cfreturn lsTaxCode>
745 </cffunction>
746</cfcomponent>