protected function getCategories() { $sql = ' SELECT C.idcategory AS id, CT.name AS label FROM category C INNER JOIN viewcategory CV ON CV.categoryid = idcategory LEFT JOIN categorytranslation CT ON C.idcategory = CT.categoryid AND CT.languageid = :languageid WHERE CV.viewid = :viewid AND C.enable = 1 GROUP BY C.idcategory ORDER BY C.distinction ASC '; $stmt = Db::getInstance()->prepare($sql); $stmt->BindParam('languageid', Helper::getLanguageId()); $stmt->BindParam('viewid', Helper::getViewId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('catid' => $rs['id'], 'catname' => $rs['label']); } return $Data; }
public function getCategories($params) { $sql = 'SELECT PC.categoryid AS id, CT.name AS caption, CT.seo FROM productcategory PC LEFT JOIN product P ON PC.productid = P.idproduct LEFT JOIN categorytranslation CT ON PC.categoryid = CT.categoryid AND CT.languageid = :languageid LEFT JOIN viewcategory VC ON PC.categoryid = VC.categoryid LEFT JOIN productstatuses PS ON PC.productid = PS.productid WHERE VC.viewid = :viewid AND PS.productstatusid = :statusid AND P.enable = 1 GROUP BY PC.categoryid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->bindValue('statusid', $params['statusId']); $stmt->execute(); $Data = array(); $Data[] = array('id' => 0, 'caption' => _('TXT_ALL')); while ($rs = $stmt->fetch()) { $Data[] = array('id' => $rs['id'], 'caption' => $rs['caption'], 'link' => $this->registry->router->generate('frontend.categorylist', true, array('param' => $rs['seo']))); } return $Data; }
public function getBoxesAll($subpage, $pageschemeid) { $Data = array(); $sql = ' SELECT LB.idlayoutbox AS id, LB.name, LB.controller, LBT.title FROM layoutbox LB LEFT JOIN layoutboxtranslation LBT ON LBT.layoutboxid = LB.idlayoutbox WHERE LBT.languageid = :languageid AND LB.pageschemeid = :pageschemeid ORDER BY LB.name ASC '; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('pageschemeid', $pageschemeid); $stmt->execute(); while ($rs = $stmt->fetch()) { if ($subpage != '' && $this->checkBoxForSubpage($rs['controller'], $subpage) == 1) { $Data[] = array('id' => $rs['id'], 'name' => $rs['name'] . ' - ' . $rs['title']); } } return $Data; }
public function getProducerAll() { $sql = 'SELECT P.idproducer AS id, PT.name, PT.seo, P.photoid, COUNT(PROD.idproduct) AS totalproducts FROM producer P LEFT JOIN producertranslation PT ON PT.producerid = P.idproducer AND PT.languageid = :language LEFT JOIN producerview PV ON P.idproducer = PV.producerid LEFT JOIN product PROD ON PROD.producerid = P.idproducer AND PROD.enable = 1 WHERE PV.viewid = :viewid GROUP BY P.idproducer HAVING totalproducts > 0 ORDER BY PT.name ASC'; $Data = array(); $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('language', Helper::getLanguageId()); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $Data[] = array('id' => $rs['id'], 'name' => $rs['name'], 'seo' => $rs['seo'], 'link' => $this->registry->router->generate('frontend.producerlist', true, array('param' => $rs['seo'])), 'active' => $this->getParam() == $rs['seo'] ? true : false, 'photo' => App::getModel('categorylist')->getImagePath($rs['photoid'])); } return $Data; }
public function getProductReviews($productid) { $sql = "SELECT\n\t\t\t\t\treview,\n\t\t\t\t\tnick,\n\t\t\t\t\tadddate,\n\t\t\t\t\tidproductreview\n\t\t\t\tFROM productreview\n\t\t\t\tWHERE productid = :productid AND viewid = :viewid AND enable = 1\n\t\t\t\tGROUP BY idproductreview\n\t\t\t\tORDER BY adddate ASC\n\t\t"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('productid', $productid); $stmt->bindValue('viewid', Helper::getViewId()); $Data = array(); try { $stmt->execute(); while ($rs = $stmt->fetch()) { $sql2 = "SELECT \n\t\t\t\t\t\t\tPR.rangetypeid,\n\t\t\t\t\t\t\tPR.value,\n\t\t\t\t\t\t\tRTT.name\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tproductrange PR\n\t\t\t\t\t\t\tLEFT JOIN rangetypetranslation RTT ON RTT.rangetypeid = PR.rangetypeid AND RTT.languageid = :languageid\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tPR.productreviewid = :reviewid"; $stmt2 = Db::getInstance()->prepare($sql2); $stmt2->bindValue('reviewid', $rs['idproductreview']); $stmt2->bindValue('languageid', Helper::getLanguageId()); $rangesRes = $stmt2->execute(); $ranges = array(); while ($rangesRes = $stmt2->fetch()) { $ranges[] = array('rangetypeid' => $rangesRes['rangetypeid'], 'value' => $rangesRes['value'], 'name' => $rangesRes['name']); } $Data[] = array('nick' => $rs['nick'], 'review' => $rs['review'], 'adddate' => $rs['adddate'], 'ranges' => $ranges); } } catch (Exception $e) { throw new FrontendException('Error while doing sql query- getProductReviews (productreview)'); } return $Data; }
public function getControllerSeoAll() { $sql = 'SELECT name as translation FROM controllerseo WHERE languageid = :languageid'; $Data = array(); $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $Data[] = array('translation' => $rs['translation']); } return $Data; }
public function getMainCategories() { $sql = "SELECT \n\t\t\t\t\tC.idcategory, \n\t\t\t\t\tCT.name,\n\t\t\t\t\tCT.seo,\n\t\t\t\t\tC.photoid,\n\t\t\t\t\tCT.shortdescription,\n\t\t\t\t\tCT.description,\n \t\t\t\tCOUNT(PC.productid) AS totalproducts,\n \t\t\t\tMIN(P.sellprice) AS minsellprice\n\t\t\t\tFROM category C\n\t\t\t\tLEFT JOIN viewcategory VC ON VC.categoryid = C.idcategory\n\t\t\t\tLEFT JOIN categorypath CP ON CP.ancestorcategoryid = C.idcategory\n\t\t\t\tLEFT JOIN productcategory PC ON CP.categoryid = PC.categoryid\n\t\t\t\tLEFT JOIN product P ON PC.productid = P.idproduct\n\t\t\t\tLEFT JOIN categorytranslation CT ON CT.categoryid = idcategory AND CT.languageid = :languageid\n\t\t\t\tWHERE C.categoryid IS NULL AND VC.viewid=:viewid AND C.enable = 1\n\t\t\t\tGROUP BY C.idcategory"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('name' => $rs['name'], 'idcategory' => $rs['idcategory'], 'qry' => $rs['totalproducts'], 'seo' => $rs['seo'], 'minsellprice' => $this->registry->core->processPrice($rs['minsellprice']), 'shortdescription' => $rs['shortdescription'], 'description' => $rs['description'], 'photo' => $this->getImagePath($rs['photoid'])); } return $Data; }
public function getOrderList() { $sql = "SELECT O.idorder as id, O.price, O.adddate as date, O.globalprice, O.dispatchmethodprice,\n\t\t\t\t\t\tO.dispatchmethodname, O.paymentmethodname,\n\t\t\t\t\t\tOST.name as orderstatusname,\n\t\t\t\t\t\tOCDelivery.firstname, OCDelivery.surname, OCDelivery.street, OCDelivery.streetno,\n\t\t\t\t\t\tOCDelivery.companyname, OCDelivery.NIP, OCDelivery.placeno, OCDelivery.postcode,\n\t\t\t\t\t\tOCDelivery.place,\n\t\t\t\t\t\tOCDelivery.phone,\n\t\t\t\t\t\tOCDelivery.phone2,\n\t\t\t\t\t\tOCDelivery.email\n\t\t\t\t\tFROM `order` O\n\t\t\t\t\tLEFT JOIN orderstatus OS ON OS.idorderstatus=O.orderstatusid\n \t\t\tLEFT JOIN orderstatustranslation OST ON OST.orderstatusid = OS.idorderstatus\n \t\t\tAND OST.languageid= :languageid\n\t\t\t\t\tLEFT JOIN orderclientdeliverydata OCDelivery ON OCDelivery.orderid= O.idorder"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $rs = $stmt->fetch(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('id' => $rs['id'], 'price' => $rs['price'], 'orderdate' => $rs['date'], 'globalprice' => $rs['globalprice'], 'dispatchmethodprice' => $rs['dispatchmethodprice'], 'dispatchmethodname' => $rs['dispatchmethodname'], 'paymentmethodname' => $rs['paymentmethodname'], 'orderstatusname' => $rs['orderstatusname'], 'firstname' => $rs['firstname'], 'surname' => $rs['surname'], 'street' => $rs['street'], 'streetno' => $rs['streetno'], 'companyname' => $rs['companyname'], 'NIP' => $rs['nip'], 'placeno' => $rs['placeno'], 'postcode' => $rs['postcode'], 'place' => $rs['place'], 'phone' => $rs['phone'], 'phone2' => $rs['phone2'], 'email' => $rs['email']); } return $Data; }
public function getNews() { $sql = "SELECT \n\t\t\t\tCONCAT(:url,:seo,'/',N.idnews,'/',NT.seo) as loc,\n\t\t\t\tDATE_FORMAT(N.adddate,'%Y-%m-%d') as lastmod\n\t\t\t\tFROM news N\n\t\t\t\tLEFT JOIN newstranslation NT ON N.idnews = NT.newsid AND NT.languageid = :languageid\n\t\t\t\tWHERE N.publish = 1\n\t\t\t\tGROUP BY N.idnews"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('url', URL); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('seo', Seo::getSeo('news')); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('loc' => $rs['loc'], 'lastmod' => $rs['lastmod']); } return $Data; }
public function getProductListIntegration() { $sql = "SELECT\n\t\t\t\t\tPC.categoryid AS id,\n\t\t\t\t\tP.idproduct,\n\t\t\t\t\tPT.name,\n\t\t\t\t\tP.sellprice,\n\t\t\t\t\tPT.shortdescription,\n\t\t\t\t\tPhoto.photoid,\n\t\t\t\t\tPT.seo,\n\t\t\t\t\t(SELECT\n\t\t\t\t\t GROUP_CONCAT(SUBSTRING(CT.name, 1) ORDER BY C.order DESC SEPARATOR ' / ')\n\t\t\t\t\tFROM categorytranslation CT\n\t\t\t\t\tLEFT JOIN categorypath C ON C.ancestorcategoryid = CT.categoryid\n\t\t\t\t\tWHERE C.categoryid = PC.categoryid AND CT.languageid = :languageid) AS webkupiec,\n\t\t\t\t\tPRT.name AS producername,\n\t\t\t\t\tP.weight\n\t\t\t\tFROM product P\n\t\t\t\tLEFT JOIN producttranslation PT ON PT.productid = P.idproduct AND PT.languageid=:languageid\n\t\t\t\tLEFT JOIN productcategory PC ON PC.productid = P.idproduct\n\t\t\t\tLEFT JOIN categorypath CP ON CP.ancestorcategoryid = PC.categoryid\n\t\t\t\tLEFT JOIN categorytranslation CT ON CP.ancestorcategoryid = CT.categoryid AND CT.languageid = :languageid\n\t\t\t\tLEFT JOIN producertranslation PRT ON PRT.producerid = P.producerid AND PRT.languageid = :languageid\n\t\t\t\tLEFT JOIN productphoto Photo ON Photo.productid = P.idproduct AND Photo.mainphoto = 1\n\t\t\t\tWHERE P.enable = 1\n\t GROUP BY P.idproduct"; $stmt = Db::getInstance()->prepare($sql); $stmt->BindParam('languageid', Helper::getLanguageId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('categoryid' => $rs['id'], 'seo' => $rs['seo'], 'categoryname' => $rs['webkupiec'], 'producername' => $rs['producername'], 'productid' => $rs['idproduct'], 'name' => $rs['name'], 'shortdescription' => $rs['shortdescription'], 'sellprice' => $rs['sellprice'], 'photoid' => $rs['photoid'], 'idproduct' => $rs['idproduct']); } foreach ($Data as $key => $Product) { $Image = App::getModel('gallery')->getOrginalImageById($Product['photoid']); $Data[$key]['photo'] = App::getModel('gallery')->getImagePath($Image, App::getURLAdress()); } return $Data; }
public function getProductListIntegration() { $sql = "SELECT\n\t\t\t\t\tPC.categoryid AS id,\n\t\t\t\t\tP.idproduct,\n\t\t\t\t\tPT.name,\n\t\t\t\t\tP.sellprice,\n\t\t\t\t\tPT.shortdescription,\n\t\t\t\t\tPhoto.photoid,\n\t\t\t\t\tPT.seo,\n\t\t\t\t\tCT.name AS categoryname\n\t\t\t\tFROM product P\n\t\t\t\tLEFT JOIN producttranslation PT ON PT.productid = P.idproduct AND PT.languageid=:languageid\n\t\t\t\tLEFT JOIN productcategory PC ON PC.productid = P.idproduct\n\t\t\t\tLEFT JOIN categorytranslation CT ON PC.categoryid = CT.categoryid AND CT.languageid = :languageid\n\t\t\t\tLEFT JOIN productphoto Photo ON Photo.productid = P.idproduct AND Photo.mainphoto = 1\n\t\t\t\tWHERE P.enable = 1\n\t GROUP BY P.idproduct"; $stmt = Db::getInstance()->prepare($sql); $stmt->BindParam('languageid', Helper::getLanguageId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('categoryid' => $rs['id'], 'seo' => $rs['seo'], 'categoryname' => $rs['categoryname'], 'productid' => $rs['idproduct'], 'name' => $rs['name'], 'shortdescription' => $rs['shortdescription'], 'sellprice' => $rs['sellprice'], 'photoid' => $rs['photoid'], 'idproduct' => $rs['idproduct']); } foreach ($Data as $key => $Product) { $Image = App::getModel('gallery')->getOrginalImageById($Product['photoid']); $Data[$key]['photo'] = App::getModel('gallery')->getImagePath($Image, App::getURLAdress()); } return $Data; }
public function getNewsById($id) { $sql = "SELECT \n\t\t\t\t\tN.idnews, \n\t\t\t\t\tNT.topic, \n\t\t\t\t\tNT.summary,\n\t\t\t\t\tNT.content,\n\t\t\t\t\tNT.seo,\n\t\t\t\t\tNT.keyword_title,\n\t\t\t\t\tNT.keyword,\n\t\t\t\t\tNT.keyword_description,\n\t\t\t\t\tN.adddate,\n\t\t\t\t\tN.featured\n\t\t\t\tFROM news N\n\t\t\t\tLEFT JOIN newsview NV ON NV.newsid = idnews\n\t\t\t\tLEFT JOIN newstranslation NT ON N.idnews = NT.newsid AND NT.languageid = :languageid\n\t\t\t\tWHERE\n\t\t\t\t\tidnews=:id\n\t\t\t\tAND\n\t\t\t\t\tpublish = 1\n\t\t\t\tAND\n\t\t\t\t\tIF((N.startdate IS NULL OR N.startdate = '0000-00-00 00:00:00' OR N.startdate <= CURDATE()) AND (N.enddate IS NULL OR N.enddate = '0000-00-00 00:00:00' OR N.enddate >= CURDATE()), 1, 0)\n\t\t\t\t\t\n\t\t\t\tAND\n\t\t\t\t\tNV.viewid = :viewid\n\t\t\t\tORDER BY N.`adddate` desc"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $id); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->execute(); $rs = $stmt->fetch(); $Data = array(); if ($rs) { $Data = array('featured' => $rs['featured'], 'topic' => $rs['topic'], 'adddate' => $rs['adddate'], 'summary' => $rs['summary'], 'content' => $rs['content'], 'seo' => $rs['seo'], 'link' => $this->generateUrl($rs['idnews'], $rs['seo']), 'keyword_title' => $rs['keyword_title'] == NULL || $rs['keyword_title'] == '' ? $rs['topic'] : $rs['keyword_title'], 'keyword' => $rs['keyword'], 'keyword_description' => $rs['keyword_description'], 'mainphoto' => $this->getPhotosByNewsId($id), 'otherphoto' => $this->getOtherPhotosByNewsId($id)); return $Data; } else { App::redirectUrl($this->registry->router->generate('frontend.news', true)); } }
public function getProductListIntegration() { $sql = "SELECT\n\t\t\t\t\tP.idproduct,\n\t\t\t\t\tPT.name,\n\t\t\t\t\t(P.sellprice * (1 + (V.value / 100)) * CR.exchangerate) AS sellprice,\n\t\t\t\t\tIF(P.promotion = 1 AND IF(P.promotionstart IS NOT NULL, P.promotionstart <= CURDATE(), 1) AND IF(P.promotionend IS NOT NULL, P.promotionend >= CURDATE(), 1), P.discountprice * (1 + (V.value / 100)) * CR.exchangerate, NULL) AS discountprice,\n\t\t\t\t\tPT.shortdescription,\n\t\t\t\t\tPhoto.photoid,\n\t\t\t\t\tPRT.name AS producername,\n\t\t\t\t\tPT.seo,\n\t\t\t\t\t(SELECT\n\t\t\t\t\t GROUP_CONCAT(SUBSTRING(CT.name, 1) ORDER BY C.order DESC SEPARATOR ' / ')\n\t\t\t\t\tFROM categorytranslation CT\n\t\t\t\t\tLEFT JOIN categorypath C ON C.ancestorcategoryid = CT.categoryid\n\t\t\t\t\tWHERE C.categoryid = PC.categoryid AND CT.languageid = :languageid) AS kreocen\n\t\t\t\tFROM product P\n\t\t\t\tLEFT JOIN producttranslation PT ON PT.productid = P.idproduct AND PT.languageid=:languageid\n\t\t\t\tLEFT JOIN productcategory PC ON PC.productid = P.idproduct\n\t\t\t\tLEFT JOIN categorypath CP ON CP.ancestorcategoryid = PC.categoryid\n\t\t\t\tLEFT JOIN categorytranslation CT ON CP.ancestorcategoryid = CT.categoryid AND CT.languageid = :languageid\n\t\t\t\tLEFT JOIN productphoto Photo ON Photo.productid = P.idproduct AND Photo.mainphoto=1\n\t\t\t\tLEFT JOIN producertranslation PRT ON PRT.producerid = P.producerid AND PRT.languageid = :languageid\n\t\t\t\tLEFT JOIN vat V ON P.vatid= V.idvat\n\t\t\t\tLEFT JOIN currencyrates CR ON CR.currencyfrom = P.sellcurrencyid AND CR.currencyto = :currencyto\n\t\t\t\tWHERE P.enable = 1\n\t GROUP BY P.idproduct"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindParam('languageid', Helper::getLanguageId()); $stmt->bindParam('currencyto', Session::getActiveCurrencyId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('productid' => $rs['idproduct'], 'seo' => $rs['seo'], 'name' => $rs['name'], 'shortdescription' => $rs['shortdescription'], 'sellprice' => number_format(!is_null($rs['discountprice']) ? $rs['discountprice'] : $rs['sellprice'], 2), 'photoid' => $rs['photoid'], 'idproduct' => $rs['idproduct'], 'producername' => $rs['producername'], 'kreocen' => $rs['kreocen']); } foreach ($Data as $key => $Product) { $Image = App::getModel('gallery')->getOrginalImageById($Product['photoid']); $Data[$key]['photo'] = App::getModel('gallery')->getImagePath($Image, App::getURLAdress()); } return $Data; }
public function getContactList() { $sql = "SELECT\n\t\t\t\t\tC.idcontact,\n\t\t\t\t\tCT.name, \n\t\t\t\t\tCT.email, \n\t\t\t\t\tCT.phone, \n\t\t\t\t\tCT.fax, \n\t\t\t\t\tCT.street, \n\t\t\t\t\tCT.streetno, \n\t\t\t\t\tCT.placeno, \n\t\t\t\t\tCT.placename, \n\t\t\t\t\tCT.postcode,\n\t\t\t\t\tCT.businesshours\n\t\t\t\tFROM contact C\n\t\t\t\tLEFT JOIN contacttranslation CT ON CT.contactid = C.idcontact AND CT.languageid = :languageid\n\t\t\t\tLEFT JOIN contactview CV ON CV.contactid = C.idcontact\n\t\t\t\tWHERE C.publish = 1 AND CV.viewid = :viewid\n\t\t\t\tORDER BY C.idcontact"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->bindValue('languageid', Helper::getLanguageId()); $Data = array(); try { $stmt->execute(); while ($rs = $stmt->fetch()) { $Data[$rs['idcontact']] = array('idcontact' => $rs['idcontact'], 'name' => $rs['name'], 'phone' => $rs['phone'], 'fax' => $rs['fax'], 'email' => $rs['email'], 'street' => $rs['street'], 'streetno' => $rs['streetno'], 'placeno' => $rs['placeno'], 'placename' => $rs['placename'], 'postcode' => $rs['postcode'], 'businesshours' => $rs['businesshours']); } } catch (Exception $e) { throw new FrontendException($fe->getMessage('ERR_QUERY_WISHLIST')); } return $Data; }
public function getProducerBySeo($seo) { $sql = "SELECT\n\t\t\t\t\tP.photoid,\n\t\t\t\t\tPT.producerid,\n\t\t\t\t\tPT.name,\n\t\t\t\t\tPT.seo,\n\t\t\t\t\tPT.description,\n\t\t\t\t\tPT.keyword_title,\n\t\t\t\t\tPT.keyword,\n\t\t\t\t\tPT.keyword_description\n\t\t\t\tFROM producertranslation PT\n\t\t\t\tLEFT JOIN producer P ON P.idproducer = PT.producerid\n\t\t\t\tWHERE PT.seo =:seo AND PT.languageid = :languageid"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('seo', $seo); try { $stmt->execute(); $rs = $stmt->fetch(); } catch (Exception $e) { throw new FrontendException($e->getMessage()); } $Data = array(); if ($rs) { $Data = array('id' => $rs['producerid'], 'name' => $rs['name'], 'description' => $rs['description'], 'seo' => $rs['seo'], 'photo' => $this->getImagePath($rs['photoid']), 'keyword_title' => $rs['keyword_title'] == NULL || $rs['keyword_title'] == '' ? $rs['name'] : $rs['keyword_title'], 'keyword' => $rs['keyword'], 'keyword_description' => $rs['keyword_description']); } return $Data; }
public static function loadTranslations() { if ((self::$translations = App::getRegistry()->cache->load('translations')) === FALSE) { $sql = 'SELECT T.name, TD.translation FROM translation T LEFT JOIN translationdata TD ON T.idtranslation = TD.translationid WHERE TD.languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { self::$translations[$rs['name']] = htmlspecialchars($rs['translation'], ENT_QUOTES); } App::getRegistry()->cache->save('translations', self::$translations); } }
public static function load() { if ((self::$seocontrollers = App::getRegistry()->cache->load('seocontrollers')) === FALSE) { $sql = 'SELECT C.name as name, IF(CS.name IS NOT NULL, CS.name, C.name) as alias FROM controller C LEFT JOIN controllerseo CS ON CS.controllerid = C.idcontroller WHERE CS.languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { self::$seocontrollers[$rs['alias']] = $rs['name']; } App::getRegistry()->cache->save('seocontrollers', self::$seocontrollers); } }
public function getCategoryById($id) { $sql = "SELECT\n\t\t\t\t\tCT.categoryid,\n\t\t\t\t\tCT.name,\n\t\t\t\t\tCT.seo,\n\t\t\t\t\tCT.shortdescription,\n\t\t\t\t\tCT.description,\n\t\t\t\t\tC.photoid,\n\t\t\t\t\tC.categoryid AS parent\n\t\t\t\tFROM categorytranslation CT\n\t\t\t\tLEFT JOIN category C ON CT.categoryid = C.idcategory\n\t\t\t\tLEFT JOIN viewcategory VC ON CT.categoryid = VC.categoryid \n\t\t\t\tWHERE C.idcategory =:id AND CT.languageid = :languageid AND VC.viewid = :viewid AND C.enable = 1"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->bindValue('id', $id); try { $stmt->execute(); $rs = $stmt->fetch(); } catch (Exception $e) { throw new FrontendException($e->getMessage()); } $Data = array(); if ($rs) { $Data = array('id' => $rs['categoryid'], 'name' => $rs['name'], 'seo' => $rs['seo'], 'shortdescription' => $rs['shortdescription'], 'description' => $rs['description'], 'photo' => $this->getImagePath($rs['photoid'])); } return $Data; }
public function clientsGroupsChart($request) { $Data = array(); $sql = 'SELECT count(CD.clientid) as clients,CGT.name as groupsname FROM clientdata CD LEFT JOIN clientgrouptranslation CGT ON CD.clientgroupid = CGT.clientgroupid AND CGT.languageid = :languageid GROUP BY CGT.clientgroupid;'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $Data['values'][] = array("value" => $rs['clients'], "label" => $rs['groupsname']); } $Data['colours'] = array('#d01f3c', '#356aa0', '#C79810'); $Data['animate'][] = array('type' => 'fade', 'type' => 'bounce', 'distance' => 4); $Data['oChartData']['bg_colour'] = "#ffffff"; $Data['oChartData']['elements'][] = array('type' => 'pie', 'tip' => '#label#<br>#val# (#percent#)', 'colours' => $Data['colours'], 'gradient-fill' => true, 'alpha' => 0.6, 'border' => 2, 'animate' => false, 'start-angle' => 65, 'values' => $Data['values']); $Data['oChartData']['title'] = array('text' => ''); return $Data; }
public function doAJAXUpdateTranslation($id, $translation) { Db::getInstance()->beginTransaction(); $sql = 'DELETE FROM translationdata WHERE translationid = :translationid AND languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('translationid', $id); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $sql = 'INSERT INTO translationdata SET translation=:translation, translationid = :translationid, languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('translationid', $id); $stmt->bindValue('translation', $translation); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); Db::getInstance()->commit(); $this->flushCacheTranslations(); return true; }
public function __construct($registry) { parent::__construct($registry); $this->queryColumns = array(); $this->queryFrom = ''; $this->queryGroupBy = ''; $this->queryOrderBy = ''; $this->queryHaving = ''; $this->queryLimit = 100; $this->queryOffset = 0; $this->pagination = 100; $this->currentPage = 0; $this->sqlParams = array(); $this->encryptionKey = Session::getActiveEncryptionKeyValue(); $this->languageId = Helper::getLanguageId(); $this->viewId = !is_null(Helper::getViewId()) ? Helper::getViewId() : 0; $this->queryAdditionalWhere = ''; $this->DataSet = array(); $this->cacheEnabled = array('enabled' => false, 'lifetime' => 3600, 'cacheid' => null); $this->layerData = $this->registry->loader->getCurrentLayer(); }
public function getMetadataForPage() { $controller = $this->registry->router->getCurrentController(); $Data = array(); $sql = "SELECT\n\t\t\t\t\tVT.keyword_title,\n\t\t\t\t\tC.description,\n\t\t\t\t\tVT.keyword,\n\t\t\t\t\tVT.keyword_description,\n\t\t\t\t\tVT.additionalmeta\n\t\t\t\tFROM controller C\n\t\t\t\tLEFT JOIN viewtranslation VT ON VT.viewid = :viewid AND VT.languageid = :languageid\n\t\t\t\tWHERE C.name = :controller AND C.mode = 0"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('controller', $controller); $stmt->bindValue('viewid', Helper::getViewId()); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $rs = $stmt->fetch(); if ($rs) { if ($rs['keyword_title'] == NULL || $rs['keyword_title'] == '') { $keyword_title = $controller == 'mainside' ? Session::getActiveShopName() : _($rs['description']); } else { $keyword_title = $rs['keyword_title']; } $title = $controller == 'mainside' ? $keyword_title : _($rs['description']); $Data = array('keyword_title' => $title, 'keyword' => $rs['keyword'], 'keyword_description' => $rs['keyword_description'], 'additionalmeta' => $rs['additionalmeta']); } return $Data; }
public function addEmptyDeliverer($request) { $sql = 'SELECT delivererid FROM deliverertranslation WHERE name = :name AND languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('name', $request['name']); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $rs = $stmt->fetch(); if ($rs) { $id = $rs['delivererid']; } else { $id = $this->addDeliverer(array()); $sql = 'INSERT INTO deliverertranslation (delivererid, name, languageid) VALUES (:delivererid, :name, :languageid)'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('delivererid', $id); $stmt->bindValue('name', $request['name']); $stmt->bindValue('languageid', Helper::getLanguageId()); try { $stmt->execute(); } catch (Exception $e) { throw new CoreException(_('ERR_DELIVERER_TRANSLATION_EDIT'), 10, $e->getMessage()); } } return array('id' => $id, 'options' => $this->getDelivererAsExchangeOptions()); }
public function staticcontent($id) { $sql = "SELECT\n\t\t\t\tCONCAT(:seo,'/',CC.idcontentcategory) AS link,\n\t\t\t\tCCT.name AS title\n\t\t\t\tFROM contentcategory CC\n\t\t\t\tLEFT JOIN contentcategorytranslation CCT ON CCT.contentcategoryid = CC.idcontentcategory AND CCT.languageid = :languageid\n\t\t\t\tWHERE CC.idcontentcategory = :id OR CC.idcontentcategory = (SELECT contentcategoryid FROM contentcategory WHERE idcontentcategory = :id)"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('seo', Seo::getSeo('staticcontent')); $stmt->bindValue('id', $id); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $Data[] = array('link' => $rs['link'], 'title' => $rs['title']); } return $Data; }
public function getSimilarProductView($id) { $sql = "SELECT \n\t\t\t\t\tPT.name AS relatedproduct\n\t\t\t\tFROM similarproduct CS\n\t\t\t\tLEFT JOIN product P ON P.idproduct= CS.relatedproductid\n\t\t\t\tLEFT JOIN producttranslation PT ON P.idproduct = PT.productid AND PT.languageid = :languageid\n\t\t\t\tWHERE CS.productid=:id"; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $id); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $Data[] = array('relatedproduct' => $rs['relatedproduct']); } return $Data; }
public function search($phrase) { $phrase = strtolower($phrase); $sql = ' SELECT O.idorder, O.adddate, AES_DECRYPT(OC.surname,:encryptionkey) AS surname, AES_DECRYPT(OC.firstname,:encryptionkey) AS firstname, AES_DECRYPT(OC.email,:encryptionkey) AS email FROM `order` O LEFT JOIN orderclientdata OC ON OC.orderid=O.idorder WHERE O.idorder = :id OR CONVERT(LOWER(AES_DECRYPT(OC.surname,:encryptionkey)) USING utf8) LIKE :phrase OR CONVERT(LOWER(AES_DECRYPT(OC.firstname,:encryptionkey)) USING utf8) LIKE :phrase OR CONVERT(LOWER(AES_DECRYPT(OC.email,:encryptionkey)) USING utf8) LIKE :phrase ORDER BY O.adddate DESC LIMIT 10 '; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $phrase); $stmt->bindValue('phrase', '%' . $phrase . '%'); $stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue()); $stmt->execute(); $Data = array(); while ($rs = $stmt->fetch()) { $url = App::getURLAdressWithAdminPane() . 'order/edit/' . $rs['idorder']; $str = '#' . $rs['idorder'] . ': ' . $rs['firstname'] . ' ' . $rs['surname'] . ' (' . $rs['email'] . ') z dnia ' . $rs['adddate']; $str = $this->highlight($phrase, $str); $str = '<li><a href="' . $url . '">' . $str . '</a></li>'; $Data['orders'][] = $str; } $sql = ' SELECT OC.clientid, AES_DECRYPT(OC.surname,:encryptionkey) AS surname, AES_DECRYPT(OC.firstname,:encryptionkey) AS firstname, AES_DECRYPT(OC.email,:encryptionkey) AS email FROM clientdata OC WHERE CONVERT(LOWER(AES_DECRYPT(OC.surname,:encryptionkey)) USING utf8) LIKE :phrase OR CONVERT(LOWER(AES_DECRYPT(OC.firstname,:encryptionkey)) USING utf8) LIKE :phrase OR CONVERT(LOWER(AES_DECRYPT(OC.email,:encryptionkey)) USING utf8) LIKE :phrase LIMIT 10 '; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $phrase); $stmt->bindValue('phrase', '%' . $phrase . '%'); $stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue()); $stmt->execute(); while ($rs = $stmt->fetch()) { $url = App::getURLAdressWithAdminPane() . 'client/edit/' . $rs['clientid']; $str = $rs['firstname'] . ' ' . $rs['surname'] . ' (' . $rs['email'] . ')'; $str = $this->highlight($phrase, $str); $str = '<li><a href="' . $url . '">' . $str . '</a></li>'; $Data['clients'][] = $str; } $sql = ' SELECT PT.productid, PT.name, P.ean, P.delivelercode FROM product P LEFT JOIN producttranslation PT ON PT.productid = P.idproduct AND PT.languageid = :languageid WHERE PT.name LIKE :phrase OR P.ean LIKE :phrase OR P.delivelercode LIKE :phrase LIMIT 20 '; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $phrase); $stmt->bindValue('phrase', '%' . $phrase . '%'); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $url = App::getURLAdressWithAdminPane() . 'product/edit/' . $rs['productid']; $str = $rs['name']; if ($rs['ean'] != '') { $str .= ', EAN: ' . $rs['ean']; } $str = $this->highlight($phrase, $str); $str = '<li><a href="' . $url . '">' . $str . '</a></li>'; $Data['products'][] = $str; } return $Data; }
public function getOrderStatusByEmailAndId($email, $id) { $sql = 'SELECT OST.name as orderstatusname, O.idorder FROM `order` O LEFT JOIN orderstatus OS ON OS.idorderstatus = O.orderstatusid LEFT JOIN orderstatustranslation OST ON OST.orderstatusid = OS.idorderstatus AND OST.languageid = :languageid LEFT JOIN orderclientdata OCD ON OCD.orderid = O.idorder WHERE AES_DECRYPT(OCD.email, :encryptionKey) = :email AND O.idorder = :id'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('email', $email); $stmt->bindValue('id', $id); $stmt->bindValue('encryptionKey', Session::getActiveEncryptionKeyValue()); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $rs = $stmt->fetch(); if ($rs) { return $rs['orderstatusname']; } return NULL; }
public function addEmptyProducer($request) { $sql = 'SELECT producerid FROM producertranslation WHERE name = :name AND languageid = :languageid'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('name', $request['name']); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); $rs = $stmt->fetch(); if ($rs) { $id = $rs['producerid']; } else { $id = $this->addProducer(array()); $seo = App::getModel('seo')->doAJAXCreateSeo($request); $sql = 'INSERT INTO producertranslation SET producerid = :producerid, name = :name, seo = :seo, languageid = :languageid '; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('producerid', $id); $stmt->bindValue('name', $request['name']); $stmt->bindValue('seo', $seo['seo']); $stmt->bindValue('languageid', Helper::getLanguageId()); try { $stmt->execute(); } catch (Exception $e) { throw new CoreException(_('ERR_PRODUCER_TRANSLATION_EDIT'), 15, $e->getMessage()); } $this->addView(Helper::getViewIdsDefault(), $id); } return array('id' => $id, 'options' => $this->getProducerAsExchangeOptions()); }
public function doAJAXUpdateProduct($request) { $id = $request['id']; $oRow = $request['product']; $product = $this->getProductView($id, false); $vatValue = $product['vatvalue']; $sql = 'UPDATE product SET enable = :enable, producerid = IF(:producer != \'\', (SELECT producerid FROM producertranslation WHERE name = :producer AND languageid = :languageid), NULL), ean = :ean, stock = :stock, sellprice = (:price / (1 + (:vat / 100))) , hierarchy = :hierarchy WHERE idproduct = :id'; $stmt = Db::getInstance()->prepare($sql); $stmt->bindValue('id', $id); $stmt->bindValue('ean', $oRow['ean']); $stmt->bindValue('stock', abs($oRow['stock'])); $stmt->bindValue('price', abs($oRow['sellprice_gross'])); $stmt->bindValue('hierarchy', abs($oRow['hierarchy'])); $stmt->bindValue('producer', $oRow['producer']); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->bindValue('enable', abs($oRow['enable'])); $stmt->bindValue('vat', $vatValue); $stmt->execute(); $this->syncStock(); return array('attr' => 1); }
public function getLayoutBoxParams() { $LayoutBoxParams = array(); $query = ' SELECT LB.idlayoutbox AS id, LB.controller AS controller, IF(LBT.title IS NOT NULL,LBT.title, LB.controller) AS heading FROM layoutbox LB LEFT JOIN layoutboxtranslation LBT ON LBT.layoutboxid = LB.idlayoutbox AND LBT.languageid = :languageid GROUP BY LB.idlayoutbox '; $stmt = Db::getInstance()->prepare($query); $stmt->bindValue('languageid', Helper::getLanguageId()); $stmt->execute(); while ($rs = $stmt->fetch()) { $boxId = $rs['id']; $queryJS = ' SELECT JS.variable AS variable, JS.value AS value FROM layoutboxjsvalue AS JS WHERE JS.layoutboxid = :layoutboxid '; $stmtJS = Db::getInstance()->prepare($queryJS); $stmtJS->bindValue('layoutboxid', $boxId); $stmtJS->execute(); $jsVariables = array(); while ($rsJS = $stmtJS->fetch()) { $jsVariables[$rsJS['variable']] = $rsJS['value']; } $queryCSS = ' SELECT CS.variable AS variable, CS.value AS value FROM layoutboxcontentspecificvalue AS CS WHERE CS.layoutboxid = :layoutboxid AND ((CS.languageid = :languageid) OR (CS.languageid IS NULL)) '; $stmtCSS = Db::getInstance()->prepare($queryCSS); $stmtCSS->bindValue('layoutboxid', $boxId); $stmtCSS->bindValue('languageid', Helper::getLanguageId()); $stmtCSS->execute(); $boxAttributes = array(); while ($rsCSS = $stmtCSS->fetch()) { if (empty($boxAttributes[$rsCSS['variable']])) { $boxAttributes[$rsCSS['variable']] = $rsCSS['value']; } elseif ($rsCSS['viewid'] == $viewId) { $boxAttributes[$rsCSS['variable']] = $rsCSS['value']; } } $LayoutBoxParams[$boxId] = array('controller' => $rs['controller'], 'heading' => $rs['heading'], 'js' => $jsVariables, 'css' => $boxAttributes); } return $LayoutBoxParams; }