· 5 years ago · Jun 03, 2020, 09:24 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 WHERE `#This.sProductsRichTable##LCase(This.LANGUAGE)#`.`ProductID` >= 1007881
415 LIMIT 10
416 </cfquery>
417
418 <cfset This.oWriter = Request.createCFC("sys.io.XmlWriter")>
419
420 <cfset lnCount = ceiling(lqProduct.recordcount / This.BATCH)>
421
422 <cfloop from="1" to="#lnCount#" index="lnFileIndex">
423 <cftry>
424 <cfset lnStart = (lnFileIndex * This.BATCH) - This.BATCH + 1>
425 <cfset lnEnd = (lnFileIndex * This.BATCH)>
426
427 <cfset This.oWriter.openFile(lsFilename & lnFileIndex)>
428
429 <!--- write default RightClick header --->
430 <cfset This.oWriter.header(client: This.oConnect.qConnect.connect_client)>
431 <cfset This.oWriter.open('children')>
432
433 <cfset importProductsRichBatch(lqProduct , lnStart, lnEnd)>
434
435 <!--- write closing tags --->
436 <cfset This.oWriter.close('children')>
437 <cfset This.oWriter.close('rightclick')>
438 <cfset This.oWriter.closeFile()>
439 <cfcatch>
440 <!--- finalize --->
441 <cfset This.oWriter.closeFile()>
442 <cfrethrow>
443 </cfcatch>
444 </cftry>
445 </cfloop>
446
447 <cfset WriteIndexFile(fileName: lsFilename, count: lnCount)>
448 </cffunction>
449
450 <cffunction name="WriteIndexFile" returnType="void" output="false">
451 <cfargument name="filename" type="string" required="true">
452 <cfargument name="count" type="numeric" required="true">
453
454 <cfset var lsFilename = "">
455 <cfset var lnIndex = 0>
456
457 <cftry>
458 <cfset This.oWriter = Request.createCFC("sys.io.XmlWriter")>
459 <cfset This.oWriter.init(filename: Arguments.filename)>
460 <cfset This.oWriter.open('rightclick')>
461
462 <cfloop from="1" to="#Arguments.count#" index="lnIndex">
463 <cfset lsFilename = Arguments.filename & lnIndex>
464 <cfset This.oWriter.string('filelist', lsFilename)>
465 </cfloop>
466
467 <cfset This.oWriter.close('rightclick')>
468 <cfset This.oWriter.closeFile()>
469
470 <cfcatch>
471 <cfset This.oWriter.closeFile()>
472 <cfrethrow>
473 </cfcatch>
474
475 </cftry>
476 </cffunction>
477
478 <cffunction name="buildResourcesQuery" returnType="string" output="False">
479 <cfargument name="product_id" type="numeric" required="true">
480
481 <cfset sSelectResQry = "`#This.sProductResourceTable#{lng}`.`ProductID` AS `ProductID_{lng}`,
482 `#This.sProductResourceTable#{lng}`.`ResourceID` AS `ResourceID_{lng}`,
483 `#This.sProductResourceTable#{lng}`.`ImageID` AS `ImageID_{lng}`,
484 `#This.sProductResourceTable#{lng}`.`Description` AS `ResourceDescription_{lng}`,
485 `#This.sProductResourceTable#{lng}`.`URI` AS `ResourceURI_{lng}`,
486 `#This.sProductResourceTable#{lng}`.`MimeType` AS `MimeType_{lng}`, " />
487 <cfset sDefaultFromResQry = " `#This.sProductResourceTable##LCase(This.LANGUAGE)#` " />
488 <cfset sJoinResQry = " LEFT JOIN `#This.sProductResourceTable#{lng}`
489 ON `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ResourceID` = `#This.sProductResourceTable#{lng}`.`ResourceID`
490 AND `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ProductID` = `#This.sProductResourceTable#{lng}`.`ProductID`
491 AND `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ImageID` = `#This.sProductResourceTable#{lng}`.`ImageID` " />
492 <cfset sWhereQry = " `#This.sProductResourceTable##LCase(This.LANGUAGE)#`.`ProductID` = #Arguments.product_id#" />
493 <cfset sAndWhereImgSizeQry = " AND (`tmp_also_products_resource_{lng}`.`Width` = (
494 SELECT MAX(CAST(`r`.`Width` AS INT))
495 FROM `tmp_also_products_resource_{lng}` `r`
496 WHERE `r`.`ImageID` = `tmp_also_products_resource_{lng}`.`ImageID`
497 AND `r`.`ProductID` = `tmp_also_products_resource_{lng}`.`ProductID`
498 ) OR `tmp_also_products_resource_{lng}`.`Width` = '') " />
499 <cfset sWhereIsImage = " AND `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`MimeType` = 'image/jpeg' " />
500 <cfset sWhereIsNotImage = " AND `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`MimeType` <> 'image/jpeg' " />
501 <cfset sGroupByQry = " `tmp_also_products_resource_#LCase(This.LANGUAGE)#`.`ImageID` " />
502
503 <cfset aSelectResQrys = ArrayNew(1) />
504 <cfset aJoinResQrys = ArrayNew(1) />
505 <cfset aImgSizeQrys = ArrayNew(1) />
506
507 <cfset sSelectResDefaultLang = replace(sSelectResQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
508 <cfset ArrayAppend(aSelectResQrys, sSelectResDefaultLang, true) />
509
510 <cfset sAndWhereImgSizeQryDefaultLang = replace(sAndWhereImgSizeQry, "{lng}", LCase(This.LANGUAGE), "ALL")>
511 <cfset ArrayAppend(aImgSizeQrys, sAndWhereImgSizeQryDefaultLang, true) />
512
513 <cfloop list="#This.lOtherLanguages#" index="sLang">
514 <cfset ArrayAppend(aSelectResQrys, replace(sSelectResQry, "{lng}", LCase(sLang), "ALL"), true) />
515 <cfset ArrayAppend(aJoinResQrys, replace(sJoinResQry, "{lng}", LCase(sLang), "ALL"), true) />
516 <!--- <cfset ArrayAppend(aImgSizeQrys, replace(sAndWhereImgSizeQry, "{lng}", LCase(sLang), "ALL"), true) /> --->
517 </cfloop>
518
519 <cfset sMainQry = "SELECT " />
520
521 <cfloop array="#aSelectResQrys#" item="sel">
522 <cfset sMainQry &= sel />
523 </cfloop>
524
525 <cfset sMainQry &= " 1 AS `Dummy` " />
526 <cfset sMainQry &= " FROM " & sDefaultFromResQry />
527
528 <cfloop array="#aJoinResQrys#" item="sel">
529 <cfset sMainQry &= sel />
530 </cfloop>
531
532 <cfset sMainQry &= " WHERE " & sWhereQry />
533 <cfset sMainQry &= sWhereIsImage />
534 <cfloop array="#aImgSizeQrys#" item="siz">
535 <cfset sMainQry &= siz />
536 </cfloop>
537
538 <cfset sMainQry &= " GROUP BY " & sGroupByQry />
539
540 <cfset sMainQry &= " UNION " />
541
542 <cfset sMainQry &= "SELECT " />
543
544 <cfloop array="#aSelectResQrys#" item="sel">
545 <cfset sMainQry &= sel />
546 </cfloop>
547
548 <cfset sMainQry &= " 1 AS `Dummy` " />
549 <cfset sMainQry &= " FROM " & sDefaultFromResQry />
550
551 <cfloop array="#aJoinResQrys#" item="sel">
552 <cfset sMainQry &= sel />
553 </cfloop>
554
555 <cfset sMainQry &= " WHERE " & sWhereQry />
556 <cfset sMainQry &= sWhereIsNotImage />
557
558 <cfreturn sMainQry />
559 </cffunction>
560
561 <cffunction name="importProductsRichBatch">
562 <cfargument name="query" type="query" required="true">
563 <cfargument name="startrow" type="numeric">
564 <cfargument name="endrow">
565
566 <cfset var lqProduct = arguments.query>
567
568 <cfloop query="lqProduct" startrow="#arguments.startrow#" endrow="#arguments.endrow#">
569 <cfquery name="lqProductResources" datasource="#Request.rc.connectdsn#">
570 #buildResourcesQuery(lqProduct.ProductID)#
571 </cfquery>
572
573 <cflog text="#lqProductResources.toString()#" file="lqProductResources.txt" />
574
575 <cfset This.oWriter.open('product', 'id="#Trim(Request.XmlFormat2(lqProduct.ProductID))#"')>
576 <cfset This.oWriter.string('externalid', Trim(Request.XmlFormat2(lqProduct.ProductID)))>
577
578 <cfif len(lqProduct["ShortDescription_#LCase(This.LANGUAGE)#"][lqProduct.currentRow])>
579 <cfset This.oWriter.cdata('shortdescription', Trim(lqProduct["ShortDescription_#LCase(This.LANGUAGE)#"][lqProduct.currentRow]))>
580 <cfset This.oWriter.cdata('shortdescription-plain', ReReplaceNoCase(#Trim(Evaluate("lqProduct['ShortDescription_#LCase(This.LANGUAGE)#'][lqProduct.currentRow]"))#, "<[^>]*>", "", "ALL"))>
581 </cfif>
582
583 <cfif len(lqProduct["Description_#LCase(This.LANGUAGE)#"][lqProduct.currentRow])>
584 <cfset This.oWriter.cdata('description', Trim(lqProduct["Description_#LCase(This.LANGUAGE)#"][lqProduct.currentRow]))>
585 <cfset This.oWriter.cdata('description-plain', ReReplaceNoCase(#Trim(Evaluate("lqProduct['Description_#LCase(This.LANGUAGE)#'][lqProduct.currentRow]"))#, "<[^>]*>", "", "ALL"))>
586 </cfif>
587
588 <cfset This.oWriter.open('relations')>
589 <cfif len(lqProduct.StandardImgage_nl)>
590 <cfset This.oWriter.open('relation', 'type="image/normal"')>
591 <cfset This.oWriter.open('image', 'language="#This.LANGUAGE#" id="main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#" default="Y" download="false"')>
592 <cfset This.oWriter.cdata('externalid', "main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#")>
593 <cfset This.oWriter.cdata('name', "Standard Image")>
594 <cfset This.oWriter.cdata('url', Trim(lqProduct["StandardImgage_#LCase(This.LANGUAGE)#"][lqProduct.currentRow]))>
595
596 <cfif ListLen(This.lOtherLanguages) GT 0>
597 <cfset This.oWriter.open('relations')>
598 <cfloop list="#This.lOtherLanguages#" index="sLang">
599 <cfif len(lqProduct["StandardImgage_#LCase(sLang)#"][lqProduct.currentRow])>
600 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
601 <cfset This.oWriter.open('image', 'language="#sLang#" id="main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#" download="false"')>
602 <cfset This.oWriter.cdata('externalid', "main-image-#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#")>
603 <cfset This.oWriter.cdata('name', "Standard Image")>
604 <cfset This.oWriter.cdata('url', Trim(lqProduct["StandardImgage_#LCase(sLang)#"][lqProduct.currentRow]))>
605 <cfset This.oWriter.close('image')>
606 <cfset This.oWriter.close('relation')>
607 </cfif>
608 </cfloop>
609 <cfset This.oWriter.close('relations')>
610 </cfif>
611 <cfset This.oWriter.close('image')>
612 <cfset This.oWriter.close('relation')>
613 </cfif>
614
615 <cfif lqProductResources.RecordCount GT 0>
616 <cfloop query="lqProductResources">
617 <cfif lqProductResources["MimeType_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow] EQ "image/jpeg" OR lqProductResources["MimeType_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow] EQ "image/png">
618 <cfif len(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow])>
619 <cfset This.oWriter.open('relation', 'type="image/normal"')>
620 <cfset nDefaultLangImgImageID = lqProductResources["ImageID_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow] />
621
622 <cfset This.oWriter.open('image', 'language="#This.LANGUAGE#" id="img-#Trim(nDefaultLangImgImageID)#" download="false"')>
623 <cfset This.oWriter.cdata('externalid', "img-#Trim(nDefaultLangImgImageID)#")>
624 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow]))>
625 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow]))>
626 <cfif ListLen(This.lOtherLanguages) GT 0>
627 <cfset This.oWriter.open('relations')>
628 <cfloop list="#This.lOtherLanguages#" index="sLang">
629 <cfif len(lqProductResources["ResourceURI_#LCase(sLang)#"][lqProductResources.currentRow])>
630 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
631 <cfset nLangImgImageID = lqProductResources["ImageID_#LCase(sLang)#"][lqProductResources.currentRow] />
632
633 <cfset This.oWriter.open('image', 'language="#sLang#" id="img-#Trim(nLangImgImageID)#-#sLang#" download="false"')>
634 <cfset This.oWriter.cdata('externalid', "img-#Trim(nLangImgImageID)#-#sLang#")>
635 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(sLang)#"][lqProductResources.currentRow]))>
636 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(sLang)#"][lqProductResources.currentRow]))>
637 <cfset This.oWriter.close('image')>
638 <cfset This.oWriter.close('relation')>
639 </cfif>
640 </cfloop>
641 <cfset This.oWriter.close('relations')>
642 </cfif>
643 <cfset This.oWriter.close('image')>
644 <cfset This.oWriter.close('relation')>
645 </cfif>
646 <cfelseif lqProductResources["MimeType_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow] EQ "application/pdf">
647 <cfset sDocumentType = ''>
648
649 <cfset sResDesc = lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow]>
650 <cfswitch expression="#sResDesc#">
651 <cfcase value="Data Sheet">
652 <cfset sDocumentType = 'document/normal'>
653 </cfcase>
654 <cfcase value="User Manual">
655 <cfset sDocumentType = 'document/manual'>
656 </cfcase>
657 <cfcase value="Quick Start Guide">
658 <cfset sDocumentType = 'document/normal'>
659 </cfcase>
660 </cfswitch>
661
662 <cflog text="#lqProductResources.toString()#" file="lqProductResources1.txt" />
663
664 <cfset This.oWriter.open('relation', 'type="#sDocumentType#"')>
665 <cfset nDefaultLangDocResourceID = lqProductResources["ResourceID_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow] />
666
667 <cfset This.oWriter.open('document', 'language="#This.LANGUAGE#" subclass="pdf" id="doc-#Trim(nDefaultLangDocResourceID)#"')>
668 <cfset This.oWriter.cdata('externalid', "doc-#Trim(nDefaultLangDocResourceID)#")>
669 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow]))>
670 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(This.LANGUAGE)#"][lqProductResources.currentRow]))>
671
672 <cfif ListLen(This.lOtherLanguages) GT 0>
673 <cfset This.oWriter.open('relations')>
674 <cfloop list="#This.lOtherLanguages#" index="sLang">
675 <cfif len(lqProductResources["ResourceURI_#LCase(sLang)#"][lqProductResources.currentRow])>
676 <cfset nLangDocResourceID = lqProductResources["ResourceID_#LCase(sLang)#"][lqProductResources.currentRow] />
677
678 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
679 <cfset This.oWriter.open('document', 'language="#sLang#" subclass="pdf" id="doc-#Trim(nLangDocResourceID)#-#sLang#"')>
680 <cfset This.oWriter.cdata('externalid', "doc-#Trim(nLangDocResourceID)#-#sLang#")>
681 <cfset This.oWriter.cdata('name', Trim(lqProductResources["ResourceDescription_#LCase(sLang)#"][lqProductResources.currentRow]))>
682 <cfset This.oWriter.cdata('url', Trim(lqProductResources["ResourceURI_#LCase(sLang)#"][lqProductResources.currentRow]))>
683 <cfset This.oWriter.close('document')>
684 <cfset This.oWriter.close('relation')>
685 </cfif>
686 </cfloop>
687 <cfset This.oWriter.close('relations')>
688 </cfif>
689
690 <cfset This.oWriter.close('document')>
691 <cfset This.oWriter.close('relation')>
692 </cfif>
693 </cfloop>
694 </cfif>
695
696 <cfloop list="#This.lOtherLanguages#" index="sLang">
697 <cfset This.oWriter.open('relation', 'type="translation/#sLang#"')>
698 <cfset This.oWriter.open('product', 'id="#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#" language="#sLang#"')>
699 <cfset This.oWriter.string('externalid', "#Trim(Request.XmlFormat2(lqProduct.ProductID))#-#sLang#")>
700
701 <cfif NOT isSimpleValue(This.oXLink)>
702 <cfset lqProductXlink = This.oXLink.lookup(lqProduct.ProductID, This.sXlinkSource, This.sXlinkTarget, This.Language, 'product')>
703 <cfif isDefined("lqProductXlink") AND lqProductXlink.RecordCount>
704 <cfset This.oWriter.cdata('name', Request.oFactory.get(lqProductXlink.xlink_directory_id).qget('name'))>
705 </cfif>
706 </cfif>
707
708 <cfif len(lqProduct["ShortDescription_#LCase(sLang)#"])>
709 <cfset This.oWriter.cdata('shortdescription', Trim(lqProduct["ShortDescription_#LCase(sLang)#"]))>
710 <cfset This.oWriter.cdata('shortdescription-plain', ReReplaceNoCase(#Trim(lqProduct["ShortDescription_#LCase(sLang)#"])#, "<[^>]*>", "", "ALL"))>
711 </cfif>
712
713 <cfif len(lqProduct["Description_#LCase(sLang)#"]) AND NOT ArrayContainsNoCase(This.aDummyEnTranslations[sLang], lqProduct["Description_#LCase(sLang)#"])>
714 <cfset This.oWriter.cdata('description', Trim(lqProduct["Description_#LCase(sLang)#"]))>
715 <cfset This.oWriter.cdata('description-plain', ReReplaceNoCase(#Trim(lqProduct["Description_#LCase(sLang)#"])#, "<[^>]*>", "", "ALL"))>
716 </cfif>
717 <cfset This.oWriter.close('product')>
718 <cfset This.oWriter.close('relation')>
719 </cfloop>
720 <cfset This.oWriter.close('relations')>
721 <cfset This.oWriter.close('product')>
722 </cfloop>
723 </cffunction>
724
725 <cffunction name="getTaxCode" returnType="string" output="False">
726 <cfargument name="lsTaxValue" type="string" required="true">
727 <cfset lsTaxCode = "N">
728
729 <cfswitch expression="#Trim(lsTaxValue)#">
730 <cfcase value="21">
731 <cfset lsTaxCode = "H">
732 </cfcase>
733 <cfcase value="9">
734 <cfset lsTaxCode = "L">
735 </cfcase>
736 <cfcase value="6">
737 <cfset lsTaxCode = "L">
738 </cfcase>
739 <cfcase value="0">
740 <cfset lsTaxCode = "N">
741 </cfcase>
742 <cfdefaultcase>
743 <cfset lsTaxCode = "N">
744 </cfdefaultcase>
745 </cfswitch>
746
747 <cfreturn lsTaxCode>
748 </cffunction>
749</cfcomponent>