· 10 years ago · Aug 08, 2016, 07:48 AM
1<?php
2
3namespace Terra\OpencartBundle\Controller;
4
5use PDO;
6use Symfony\Bundle\FrameworkBundle\Controller\Controller;
7use Symfony\Component\HttpFoundation\Request;
8use Symfony\Component\HttpFoundation\Session\Session;
9use Symfony\Component\HttpFoundation\Session\Storage\PhpBridgeSessionStorage;
10use Terra\OpencartBundle\Entity\OcCategory;
11
12class ProductsController extends Controller
13{
14 private $pdo,$session;
15
16 public function __construct()
17 {
18 $this->session = new Session(new PhpBridgeSessionStorage());
19 }
20
21 private function init_db(){
22 $host = $this->getParameter('database_host');
23 $dbname = $this->getParameter('database_name');
24 $user = $this->getParameter('database_user');
25 $password = $this->getParameter('database_password');
26 $dsn = "mysql:host=$host;dbname=$dbname;charset=UTF8";
27 $opt = array(
28 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
29 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
30 );
31 $this->pdo = new PDO($dsn, $user, $password, $opt);
32 }
33 public function listAction(Request $request,OcCategory $category){
34
35 $this->init_db();
36 $this->session = new Session();
37 $this->checkReset($request);
38 $initAttributes = null;
39 $initBrands = null;
40 $initSku = null;
41 $sessionAttributes = $this->getAttributes($request,'filters');
42 $sessionBrands = $this->getAttributes($request,'brands');
43 $sessionSku = $this->getAttributes($request,'sku');
44 if(!empty($sessionAttributes)){
45 $initAttributes = $this->initTemporaryAttributes($sessionAttributes);
46 }
47 if(!empty($sessionBrands)){
48 $initBrands = $this->initTemporaryManufacturers($sessionBrands);
49 }
50 if(!empty($sessionBrands)){
51 $initSku = $this->initTemporarySku($sessionSku);
52 }
53
54 $categoryId = $category->getCategoryId();
55 $this->initTemporaryCategories($categoryId);
56
57 $per_page=10;
58 $page = (int)$request->get('page');
59 if (!empty($page) && $page > 0) {
60 $selectStart=$page-1;
61 }else{
62 $selectStart = 0;
63 $page = 1;
64 }
65 $start=abs(($selectStart)*$per_page);
66 $currentLocale = $request->getLocale();
67 $language_id=$this->getLanguageId($currentLocale);
68 $categoryDescription = $this->getDoctrine()
69 ->getManager()->getRepository('TerraOpencartBundle:OcCategoryDescription')
70 ->findOneBy(array('categoryId' => $category->getCategoryId(), 'languageId' => $language_id));
71 $categoryDescription->setDescription(html_entity_decode($categoryDescription->getDescription()));
72 $attributes = $this->getProductsAttributes($language_id,$sessionAttributes);
73
74 $sql = "
75 CREATE TEMPORARY TABLE temp1 (
76 SELECT DISTINCT
77 oc_product_to_category.product_id AS product_id
78 FROM oc_temp_categories
79 LEFT JOIN oc_product_to_category ON oc_product_to_category.category_id=oc_temp_categories.category_id
80 LEFT JOIN oc_product ON oc_product.product_id=oc_product_to_category.product_id
81 LEFT JOIN oc_product_attribute ON oc_product_attribute.product_id=oc_product_to_category.product_id
82 LEFT JOIN oc_stock_status ON oc_stock_status.stock_status_id=oc_product.stock_status_id
83 ";
84 if($initAttributes == true){
85 $sql .= "
86 INNER JOIN oc_temp_product_attributes ON oc_temp_product_attributes.attribute_id=oc_product_attribute.attribute_id
87 ";
88 }
89 if($initBrands == true){
90 $sql .= "
91 INNER JOIN oc_temp_product_manufacturers ON oc_temp_product_manufacturers.manufacturer_id=oc_product.manufacturer_id
92 ";
93 }
94 $sql .= "
95 LIMIT $start,$per_page
96 );
97 ";
98 $this->pdo->query($sql);
99 $findCount = $this->pdo->query("
100 SELECT COUNT(*) AS total_products FROM `temp1`
101 ")->fetchAll();
102
103 $countProducts = reset($findCount);
104 $num_pages=ceil($countProducts['total_products']/$per_page);
105
106 $manufacturers = $this->getProductsBrands($language_id, $sessionBrands);
107 $products = $this->getProducts($language_id);
108 if(!empty($products)){
109 return $this->render('TerraOpencartBundle:Products:list.html.twig', array(
110 'products' => $products,
111 'pages' => $num_pages,
112 'category' => $category,
113 'categoryDescription' => $categoryDescription,
114 'page' => $page,
115 'attributes' => $attributes,
116 'categoryId' => $categoryId,
117 'manufacturers' => $manufacturers,
118 'sessionAttributes' => $sessionAttributes,
119 'sessionBrands' => $sessionBrands,
120 ));
121 }else{
122 return $this->render('TerraOpencartBundle:Products:404.html.twig', array(
123
124 ));
125 }
126 }
127
128 public function showAction($id, Request $request){
129 $localeProvider = $this->get('terra.opencart.locale_provider');
130 $locale = $localeProvider->getLocale($request->getLocale());
131 $language_id=$locale->language_id;
132 $em = $this->getDoctrine()->getManager();
133 $productId = (int) $id;
134 $sql = "
135 SELECT `oc_product`.*,`oc_product_description`.* FROM `oc_product`
136 INNER JOIN oc_product_description
137 ON oc_product_description.product_id=oc_product.product_id
138 WHERE oc_product.product_id=$productId
139 AND oc_product_description.language_id=$language_id
140
141 ";
142 $productSearch = $em->getConnection()->executeQuery($sql)->fetchAll();
143 if(!empty($productSearch)){
144 $product = (object)reset($productSearch);
145 $product->description = html_entity_decode($product->description);
146 return $this->render('TerraOpencartBundle:Products:show.html.twig', array(
147 'product' => $product
148 ));
149 }else{
150 exit;
151 }
152 }
153
154 private function subCategories($parentId)
155 {
156 $categories = $this->pdo->query("SELECT * FROM `oc_category`")->fetchAll();
157 foreach ($categories as $category){
158 $cats_ID[$category['category_id']][] = $category;
159 $cats[$category['parent_id']][$category['category_id']] = $category;
160 }
161
162 function build_tree($cats,$parent_id,$only_parent = false){
163
164 if(is_array($cats) and isset($cats[$parent_id])){
165 $IN = "";
166 if(!$only_parent){
167 foreach($cats[$parent_id] as $cat){
168 $IN .= $cat['category_id'].',';
169 $IN .= build_tree($cats,$cat['category_id']);
170 }
171 }elseif($only_parent){
172 $cat = $cats[$parent_id][$only_parent];
173 $IN .= $cat['category_id'].',';
174 $IN .= build_tree($cats,$cat['category_id']);
175 }
176 }
177 else return null;
178 return $IN;
179 }
180 return build_tree($cats,$parentId);
181 }
182
183 public function initTemporaryCategories($categoryId){
184 $query = "
185 CREATE TEMPORARY TABLE IF NOT EXISTS oc_temp_categories(
186 category_id INT
187 );
188 ";
189 $sql = "
190 INSERT INTO oc_temp_categories(category_id) VALUES
191 ";
192 $this->pdo->query($query);
193 $categoriesString = substr($this->subCategories($categoryId), 0, -1);
194 if(!empty($categoriesString) ){
195 if(iconv_strlen($categoriesString) > 2){
196 $categories = explode(',',$categoriesString);
197 $values = '';
198 foreach ($categories as $category) {
199 $values .= "($category),";
200 }
201 $values = "($categoryId),".substr($values, 0, -1);
202 }elseif(iconv_strlen($categoriesString) == 2){
203 $values = "($categoryId), ($categoriesString)";
204 }
205 }else{
206 $values = "($categoryId)";
207 }
208 $sql .= " $values";
209 try{
210 $this->pdo->query($sql);
211 return true;
212 }catch (\Exception $e){
213 return false;
214 }
215 }
216
217 private function getLanguageId($currentLocale)
218 {
219 $localeProvider = $this->get('terra.opencart.locale_provider');
220 $locale = $localeProvider->getLocale($currentLocale);
221 return $locale->language_id;
222 }
223
224 private function getProductsAttributes($language,$sessionAttributes){
225 $sqlAttributes = "
226 SELECT
227 oc_product_to_category.product_id,
228 oc_product_attribute.text AS attribute_value,
229 oc_attribute_description.name AS attribute_name,
230 oc_attribute_description.attribute_id AS attribute_id
231 FROM oc_temp_categories
232 LEFT JOIN oc_product_to_category ON oc_product_to_category.category_id=oc_temp_categories.category_id
233 LEFT JOIN oc_product_attribute ON oc_product_attribute.product_id=oc_product_to_category.product_id
234 AND oc_product_attribute.language_id=$language
235 LEFT JOIN oc_attribute_description ON oc_attribute_description.attribute_id=oc_product_attribute.attribute_id
236 AND oc_attribute_description.language_id=$language
237 ";
238 $attributes = $this->pdo->query($sqlAttributes)->fetchAll();
239 $attrList = array();
240 if(!empty($attributes)){
241 foreach ($attributes as $attribute){
242 $attribute = (object)$attribute;
243 $attrName = $attribute->attribute_name;
244 $attrId =$attribute->attribute_id;
245 $attrValue = $attribute->attribute_value;
246
247 $checked = array_filter($sessionAttributes, function($key) use ($attrValue){
248 return $key == $attrValue;
249 },ARRAY_FILTER_USE_KEY);
250 if(!empty($attrName) && !empty($attrValue) && !empty($attrId)){
251 $attrList[$attrName][$attrValue] = array(
252 'value' => $attrValue,
253 'id' => $attrId
254 );
255 if(count($checked) > 0){
256 $attrList[$attrName][$attrValue]['checked'] = 'checked';
257 }else{
258 $attrList[$attrName][$attrValue]['checked'] = '';
259 }
260 }
261 }
262
263 }
264 return $attrList;
265 }
266
267 private function getProductsBrands($language,$sessionBrands){
268 $sqlBrands = "
269 SELECT
270 oc_product_to_category.product_id,
271 oc_product.manufacturer_id AS manufacturer_id,
272 oc_product.manufacturer_id AS manufacturer_id,
273 oc_manufacturer.name AS manufacturer_name
274 FROM oc_temp_categories
275 LEFT JOIN oc_product_to_category ON oc_product_to_category.category_id=oc_temp_categories.category_id
276 LEFT JOIN oc_product ON oc_product.product_id=oc_product_to_category.product_id
277 LEFT JOIN oc_manufacturer ON oc_manufacturer.manufacturer_id=oc_product.manufacturer_id
278 ";
279 $brands = $this->pdo->query($sqlBrands)->fetchAll();
280 $brandsList = array();
281 if(!empty($brands)){
282 foreach ($brands as $brand){
283 $attribute = (object)$brand;
284 $attrName = $attribute->manufacturer_name;
285 $attrId =$attribute->manufacturer_id;
286 $attrValue = $attribute->manufacturer_name;
287
288 $checked = array_filter($sessionBrands, function($key) use ($attrValue){
289 return $key == $attrValue;
290 },ARRAY_FILTER_USE_KEY);
291 if(!empty($attrName) && !empty($attrValue) && !empty($attrId)){
292 $brandsList[$attrName] = array(
293 'value' => $attrValue,
294 'id' => $attrId
295 );
296 if(count($checked) > 0){
297 $brandsList[$attrName]['checked'] = 'checked';
298 }else{
299 $brandsList[$attrName]['checked'] = '';
300 }
301 }
302 }
303
304 }
305 return $brandsList;
306 }
307
308 private function checkReset(Request $request)
309 {
310 if($request->getMethod() == 'POST'){
311 $reset = $request->get('reset');
312 if(!empty($reset)){
313 $this->session->set('filters', array());
314 $this->session->set('brands', array());
315 }
316 }
317 }
318
319 private function getAttributes(Request $request, $type){
320 $filters = $this->session->get($type);
321 $attributesRequest = $request->get($type);
322 if($request->getMethod() == 'POST'){
323 if(empty($attributesRequest)){
324 return array();
325 }else{
326 $this->session->set($type, $attributesRequest);
327 return $attributesRequest;
328 }
329 }
330 if(empty($filters)){
331 return array();
332 }else{
333 $this->session->set($type, $filters);
334 return $filters;
335 }
336 }
337
338 private function initTemporaryAttributes($sessionAttributes)
339 {
340 $attributes = array();
341 foreach ($sessionAttributes as $key=>$value){
342 $attributes[$value] = $value;
343 }
344 $sql = "
345 CREATE TEMPORARY TABLE IF NOT EXISTS oc_temp_product_attributes(
346 attribute_id INT NOT NULL
347 )Engine=MEMORY;
348 ";
349 $values = "";
350 foreach ($attributes as $key => $value){
351 $values .= "($value),";
352 }
353 $values = substr($values, 0 , -1);
354 $sql .= "
355 INSERT INTO oc_temp_product_attributes(attribute_id) VALUES $values ;
356 ";
357 try{
358 $this->pdo->query($sql);
359 return true;
360 }catch(\Exception $e){
361 return $e->getMessage();
362 }
363 }
364
365 private function initTemporaryManufacturers($sessionAttributes)
366 {
367 $attributes = array();
368 foreach ($sessionAttributes as $key=>$value){
369 $attributes[$value] = $value;
370 }
371
372 $sql = "
373 CREATE TEMPORARY TABLE IF NOT EXISTS oc_temp_product_manufacturers(
374 manufacturer_id INT NOT NULL
375 )Engine=MEMORY;
376 ";
377 $values = "";
378 foreach ($attributes as $key => $value){
379 $values .= "($value),";
380 }
381
382 $values = substr($values, 0 , -1);
383 $sql .= "
384 INSERT INTO oc_temp_product_manufacturers(manufacturer_id) VALUES $values ;
385 ";
386 try{
387 $this->pdo->query($sql);
388 return true;
389 }catch(\Exception $e){
390 return $e->getMessage();
391 }
392 }
393
394 private function initTemporarySku($sessionSku)
395 {
396
397 }
398
399 private function getProducts($language_id)
400 {
401 $query = "
402 SELECT
403 oc_product.product_id AS product_id,
404 oc_product.model AS model,
405 oc_product.sku AS sku,
406 oc_product.upc AS upc,
407 oc_product.ean AS ean,
408 oc_product.jan AS jan,
409 oc_product.isbn AS isbn,
410 oc_product.mpn AS mpn,
411 oc_product.quantity AS quantity,
412 oc_product.price AS price,
413 oc_product.weight AS weight,
414 oc_product.length AS product_length,
415 oc_product.width AS width,
416 oc_product.image AS image,
417 oc_product_description.name AS product_name,
418 oc_product_description.description AS description,
419 oc_product_description.tag AS tag,
420 oc_product_description.meta_title AS meta_title,
421 -- oc_product_description.meta_h1 AS meta_h1,
422 oc_product_description.meta_description AS meta_description,
423 oc_product_description.meta_keyword AS meta_keyword,
424 oc_stock_status.name AS stock_status,
425 oc_manufacturer.manufacturer_id AS manufacturer_id,
426 oc_manufacturer.name AS manufacturer,
427 oc_attribute_description.name AS attribute_name,
428 oc_product_attribute.text AS attribute_value,
429 oc_product_attribute.attribute_id AS attribute_id,
430 oc_attribute_description.attribute_id AS attribute_id
431 FROM temp1
432 LEFT JOIN oc_product ON oc_product.product_id=temp1.product_id
433 LEFT JOIN `oc_product_description`
434 ON `oc_product_description`.`product_id`=`oc_product`.`product_id`
435 AND `oc_product_description`.`language_id` = $language_id
436 LEFT JOIN `oc_manufacturer` ON `oc_manufacturer`.`manufacturer_id`=`oc_product`.`manufacturer_id`
437 LEFT JOIN `oc_stock_status` ON `oc_stock_status`.`stock_status_id`=`oc_product`.`stock_status_id`
438 LEFT JOIN `oc_product_attribute` ON `oc_product_attribute`.`product_id`=`oc_product`.`product_id`
439 AND `oc_product_attribute`.`language_id`=$language_id
440 LEFT JOIN `oc_attribute_description`
441 ON `oc_attribute_description`.`attribute_id`=`oc_product_attribute`.`attribute_id`
442 AND `oc_attribute_description`.`language_id`=$language_id
443 ORDER BY `oc_product_description`.`name` ASC
444 ";
445 $products = array();
446 $list = $this->pdo->query($query)->fetchAll();
447 foreach ($list as $item){
448 $product = (object)$item;
449 if($product->product_id == null) {continue;}
450 $description = html_entity_decode($product->description);
451 $meta_description = html_entity_decode($product->meta_description);
452 $product->meta_description = $meta_description;
453 $product->description = $description;
454 $products[$product->product_id] = $product;
455 }
456
457 return $products;
458 }
459
460
461}