protected function initDatagrid($datagrid)
{
$datagrid->setTableData('order', array('idorder' => array('source' => 'O.idorder'), 'client' => array('source' => 'CONCAT(\'<strong>\',CONVERT(LOWER(AES_DECRYPT(OC.surname, :encryptionkey)) USING utf8),\' \',CONVERT(LOWER(AES_DECRYPT(OC.firstname, :encryptionkey)) USING utf8),\'</strong><br />\',CONVERT(LOWER(AES_DECRYPT(OC.email, :encryptionkey)) USING utf8))', 'prepareForAutosuggest' => true), 'delivery' => array('source' => 'CONCAT(
CONVERT(LOWER(AES_DECRYPT(OCD.surname, :encryptionkey)) USING utf8),
\' \',
CONVERT(LOWER(AES_DECRYPT(OCD.firstname, :encryptionkey)) USING utf8),
\'<br />\',
CONVERT(LOWER(AES_DECRYPT(OCD.street, :encryptionkey)) USING utf8),
\' \',
CONVERT(LOWER(AES_DECRYPT(OCD.streetno, :encryptionkey)) USING utf8),
\' \',
CONVERT(LOWER(AES_DECRYPT(OCD.placeno, :encryptionkey)) USING utf8),
\'<br />\',
CONVERT(LOWER(AES_DECRYPT(OCD.postcode, :encryptionkey)) USING utf8),
\' \',
CONVERT(LOWER(AES_DECRYPT(OCD.place, :encryptionkey)) USING utf8)
)'), 'price' => array('source' => 'O.price'), 'currencysymbol' => array('source' => 'O.currencysymbol'), 'globalprice' => array('source' => 'O.globalprice'), 'dispatchmethodprice' => array('source' => 'O.dispatchmethodprice'), 'orderstatusname' => array('source' => 'OST.name'), 'products' => array('source' => 'GROUP_CONCAT(SUBSTRING(CONCAT(\' \', ROUND(OP.qty,2),\' x \', OP.name), 1) SEPARATOR \'<br/>\')', 'filter' => 'having'), 'orderstatusid' => array('source' => 'O.orderstatusid', 'prepareForTree' => true, 'first_level' => $this->getStatuses()), 'dispatchmethodname' => array('source' => 'O.dispatchmethodname', 'prepareForSelect' => true), 'paymentmethodname' => array('source' => 'O.paymentmethodname', 'prepareForSelect' => true), 'adddate' => array('source' => 'O.adddate'), 'clientid' => array('source' => 'O.clientid'), 'colour' => array('source' => 'OSG.colour'), 'view' => array('source' => 'V.name', 'prepareForSelect' => true), 'comments' => array('source' => 'O.customeropinion', 'processFunction' => array($this, 'parseComments'))));
$datagrid->setFrom('
`order` O
LEFT JOIN orderstatus OS ON OS.idorderstatus=O.orderstatusid
LEFT JOIN orderstatusorderstatusgroups OSOSG ON O.orderstatusid = OSOSG.orderstatusid
LEFT JOIN orderstatusgroups OSG ON OSG.idorderstatusgroups = OSOSG.orderstatusgroupsid
LEFT JOIN orderstatustranslation OST ON OS.idorderstatus = OST.orderstatusid AND OST.languageid = :languageid
LEFT JOIN orderclientdata OC ON OC.orderid=O.idorder
LEFT JOIN orderclientdeliverydata OCD ON OCD.orderid = O.idorder
LEFT JOIN orderproduct OP ON OP.orderid = O.idorder
LEFT JOIN view V ON V.idview = O.viewid
');
$datagrid->setGroupBy('
O.idorder
');
$datagrid->setAdditionalWhere('
O.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('contact', array('idcontact' => array('source' => 'C.idcontact'), 'name' => array('source' => 'CT.name', 'prepareForAutosuggest' => true), 'email' => array('source' => 'CT.email'), 'phone' => array('source' => 'CT.phone'), 'fax' => array('source' => 'CT.fax'), 'address' => array('source' => 'CONCAT(CT.street, \' \', CT.streetno, \' \', CT.placeno, \', \', CT.postcode, \' \', CT.placename)'), 'street' => array('source' => 'CT.street', 'prepareForAutosuggest' => true), 'streetno' => array('source' => 'CT.streetno'), 'placeno' => array('source' => 'CT.placeno'), 'postcode' => array('source' => 'CT.postcode'), 'placename' => array('source' => 'CT.placename', 'prepareForAutosuggest' => true)));
$datagrid->setFrom('
contact C
LEFT JOIN contacttranslation CT ON CT.contactid = C.idcontact AND CT.languageid = :languageid
LEFT JOIN contactview CV ON CV.contactid = C.idcontact
');
$datagrid->setAdditionalWhere('
CV.viewid IN (' . Helper::getViewIdsAsString() . ')
');
$datagrid->setGroupBy('C.idcontact');
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('sessionhandler', array('id' => array('source' => 'SH.idsessionhandler'), 'sessionid' => array('source' => 'SH.sessionid'), 'client' => array('source' => 'CONCAT(AES_DECRYPT(CD.firstname,:encryptionkey),\' \',AES_DECRYPT(CD.surname,:encryptionkey))'), 'client_session' => array('source' => 'SH.sessionid'), 'ipaddress' => array('source' => 'SH.ipaddress'), 'lastaddress' => array('source' => 'SH.url'), 'client_status' => array('source' => 'IF( SH.expiredate >= NOW(), \'TXT_ACTIVE\', \'TXT_INACTIVE\')', 'prepareForSelect' => true, 'processLanguage' => true), 'cart' => array('source' => 'CONCAT(SH.globalprice,\' \',SH.cartcurrency)'), 'browser' => array('source' => 'SH.browser', 'prepareForSelect' => true), 'platform' => array('source' => 'SH.platform', 'prepareForSelect' => true), 'isbot' => array('source' => 'IF( SH.isbot = 1, \'TXT_YES\', \'TXT_NO\')', 'prepareForSelect' => true, 'processLanguage' => true), 'ismobile' => array('source' => 'IF( SH.ismobile = 1, \'TXT_YES\', \'TXT_NO\')', 'prepareForSelect' => true, 'processLanguage' => true)));
$datagrid->setFrom('
sessionhandler SH
LEFT JOIN clientdata CD ON CD.clientid = SH.clientid
');
$datagrid->setGroupBy('
SH.sessionid
');
$datagrid->setAdditionalWhere('
SH.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('orderproduct', array('productid' => array('source' => 'OP.productid'), 'name' => array('source' => 'OP.name', 'prepareForAutosuggest' => true)));
$datagrid->setFrom('
orderproduct OP
LEFT JOIN `order` O ON OP.orderid = O.idorder
LEFT JOIN product P ON OP.productid = P.idproduct
');
$datagrid->setGroupBy('
OP.productid
');
$datagrid->setAdditionalWhere('
P.sellprice IS NOT NULL AND O.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
public function initDatagrid($datagrid)
{
$datagrid->setTableData('clientdata', array('idclient' => array('source' => 'C.idclient'), 'disable' => array('source' => 'disable'), 'clientorder' => array('source' => 'IF(SUM(O.globalprice) IS NULL, 0, SUM(O.globalprice))', 'filter' => 'having'), 'firstname' => array('source' => 'CONVERT(LOWER(AES_DECRYPT(CD.firstname, :encryptionkey)) USING utf8)', 'prepareForAutosuggest' => true), 'surname' => array('source' => 'CONVERT(LOWER(AES_DECRYPT(CD.surname, :encryptionkey)) USING utf8)', 'prepareForAutosuggest' => true), 'email' => array('source' => 'CD.email', 'encrypted' => true), 'groupname' => array('source' => 'CGT.name', 'prepareForSelect' => true), 'phone' => array('source' => 'CD.phone', 'encrypted' => true), 'phone2' => array('source' => 'CD.phone2', 'encrypted' => true), 'adddate' => array('source' => 'CD.adddate'), 'view' => array('source' => 'V.name', 'prepareForSelect' => true)));
$datagrid->setFrom('
client C
LEFT JOIN clientdata CD ON CD.clientid = C.idclient
LEFT JOIN clientgrouptranslation CGT ON CGT.clientgroupid = CD.clientgroupid AND CGT.languageid=:languageid
LEFT JOIN orderclientdata OCD ON OCD.clientid = CD.clientid
LEFT JOIN `order` O ON O.idorder = OCD.orderid
LEFT JOIN view V ON C.viewid = V.idview
');
$datagrid->setGroupBy('C.idclient');
$datagrid->setAdditionalWhere('
C.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('news', array('idnews' => array('source' => 'N.idnews'), 'topic' => array('source' => 'NT.topic'), 'summary' => array('source' => 'NT.summary', 'processFunction' => array($this, 'processNewsSummary')), 'publish' => array('source' => 'N.publish'), 'adddate' => array('source' => 'N.adddate'), 'startdate' => array('source' => 'N.startdate'), 'enddate' => array('source' => 'N.enddate')));
$datagrid->setFrom('
news N
LEFT JOIN newsview NV ON NV.newsid = N.idnews
LEFT JOIN newstranslation NT ON N.idnews = NT.newsid AND NT.languageid = :languageid
LEFT JOIN language L ON L.idlanguage=NT.languageid
');
$datagrid->setGroupBy('
NT.newsid
');
$datagrid->setAdditionalWhere('
NV.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('dispatchmethod', array('iddispatchmethod' => array('source' => 'D.iddispatchmethod'), 'name' => array('source' => 'DMT.name', 'prepareForAutosuggest' => true, 'processLanguage' => true), 'countries' => array('source' => 'D.countryids', 'processFunction' => array($this, 'getCountriesForDispatchmethod')), 'hierarchy' => array('source' => 'D.hierarchy')));
$datagrid->setFrom('
dispatchmethod D
LEFT JOIN dispatchmethodview DV ON DV.dispatchmethodid = D.iddispatchmethod
LEFT JOIN dispatchmethodtranslation DMT ON DMT.dispatchmethodid = D.iddispatchmethod AND DMT.languageid = :languageid
');
$datagrid->setGroupBy('
D.iddispatchmethod
');
if (Helper::getViewId() > 0) {
$datagrid->setAdditionalWhere('
DV.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
}
protected function initDatagrid($datagrid)
{
$datagrid->setTableData('product', array('id' => array('source' => 'P.idproduct'), 'name' => array('source' => 'PT.name'), 'qty' => array('source' => 'P.viewed'), 'categoryname' => array('source' => 'CT.name'), 'categoryid' => array('source' => 'PC.categoryid', 'prepareForTree' => true, 'first_level' => App::getModel('product')->getCategories()), 'ancestorcategoryid' => array('source' => 'CP.ancestorcategoryid'), 'categoriesname' => array('source' => 'GROUP_CONCAT(DISTINCT SUBSTRING(CONCAT(\' \', CT.name), 1))', 'filter' => 'having')));
$datagrid->setFrom('
product P
LEFT JOIN producttranslation PT ON P.idproduct = PT.productid
LEFT JOIN productcategory PC ON PC.productid = PT.productid
LEFT JOIN viewcategory VC ON PC.categoryid = VC.categoryid
LEFT JOIN category C ON C.idcategory = PC.categoryid
LEFT JOIN categorypath CP ON C.idcategory = CP.categoryid
LEFT JOIN categorytranslation CT ON C.idcategory = CT.categoryid AND CT.languageid = :languageid
');
$datagrid->setGroupBy('
P.idproduct
');
$datagrid->setAdditionalWhere('
VC.viewid IN (' . Helper::getViewIdsAsString() . ')
');
}
public function getSummaryStats()
{
$Data = array();
$period = date("Ym");
$sql = 'SELECT ROUND(SUM(globalprice * CR.exchangerate),2) as total, COUNT(idorder) as orders
FROM `order` O
LEFT JOIN currencyrates CR ON CR.currencyfrom = O.currencyid AND CR.currencyto = :currencyto
WHERE viewid IN (' . Helper::getViewIdsAsString() . ') AND DATE_FORMAT(adddate,\'%Y%m\') = :period';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('period', $period);
$stmt->bindValue('currencyto', Session::getActiveCurrencyId());
$stmt->execute();
while ($rs = $stmt->fetch()) {
$Data['summarysales'] = array('total' => sprintf('%.2f', $rs['total']), 'orders' => $rs['orders']);
}
// Daily sales
$sql = 'SELECT ROUND(SUM(globalprice * CR.exchangerate),2) as total, COUNT(idorder) as orders
FROM `order` O
LEFT JOIN currencyrates CR ON CR.currencyfrom = O.currencyid AND CR.currencyto = :currencyto
WHERE viewid IN (' . Helper::getViewIdsAsString() . ') AND DATE_FORMAT(adddate,\'%Y-%m-%d\') = CURDATE()';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('currencyto', Session::getActiveCurrencyId());
$stmt->execute();
while ($rs = $stmt->fetch()) {
$Data['todaysales'] = array('total' => sprintf('%.2f', $rs['total']), 'orders' => $rs['orders']);
}
// Total clients
$sql = 'SELECT COUNT(idclient) as totalclients
FROM `client`
WHERE viewid IN (' . Helper::getViewIdsAsString() . ') AND DATE_FORMAT(adddate,\'%Y%m\') = :period';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('period', $period);
$stmt->execute();
while ($rs = $stmt->fetch()) {
$Data['summaryclients'] = array('totalclients' => (int) $rs['totalclients']);
}
// Daily clients
$sql = 'SELECT COUNT(idclient) as clients
FROM `client`
WHERE viewid IN (' . Helper::getViewIdsAsString() . ') AND DATE_FORMAT(adddate,\'%Y-%m-%d\') = CURDATE()';
$stmt = Db::getInstance()->prepare($sql);
$stmt->execute();
while ($rs = $stmt->fetch()) {
$Data['todayclients'] = array('totalclients' => (int) $rs['clients']);
}
return $Data;
}
public function getProducerAll()
{
$sql = 'SELECT P.idproducer AS id,PT.name
FROM producer P
LEFT JOIN producertranslation PT ON PT.producerid = P.idproducer AND PT.languageid = :language
LEFT JOIN producerview PV ON PV.producerid = P.idproducer
WHERE PV.viewid IN (' . Helper::getViewIdsAsString() . ')';
$Data = array();
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('language', Helper::getLanguageId());
$stmt->execute();
while ($rs = $stmt->fetch()) {
$Data[] = array('id' => $rs['id'], 'name' => $rs['name']);
}
return $Data;
}
public function initDatagrid($datagrid)
{
$datagrid->setTableData('product', array('idproduct' => array('source' => 'P.idproduct'), 'name' => array('source' => 'PT.name', 'prepareForAutosuggest' => true), 'seo' => array('source' => 'PT.seo', 'processFunction' => array($this, 'getProductSeo')), 'delivelercode' => array('source' => 'P.delivelercode'), 'hierarchy' => array('source' => 'P.hierarchy'), 'ean' => array('source' => 'P.ean'), 'categoryname' => array('source' => 'CT.name'), 'categoryid' => array('source' => 'PC.categoryid', 'prepareForTree' => true, 'first_level' => $this->getCategories()), 'ancestorcategoryid' => array('source' => 'CP.ancestorcategoryid'), 'categoriesname' => array('source' => 'GROUP_CONCAT(DISTINCT SUBSTRING(CONCAT(\' \', CT.name), 1))', 'filter' => 'having'), 'sellprice' => array('source' => 'P.sellprice'), 'sellprice_gross' => array('source' => 'ROUND(P.sellprice * (1 + V.value / 100), 2)'), 'barcode' => array('source' => 'P.barcode', 'prepareForAutosuggest' => true), 'buyprice' => array('source' => 'P.buyprice'), 'buyprice_gross' => array('source' => 'ROUND(P.buyprice * (1 + V.value / 100), 2)'), 'producer' => array('source' => 'PRT.name', 'prepareForSelect' => true), 'deliverer' => array('source' => 'DT.name', 'prepareForSelect' => true), 'status' => array('source' => 'GROUP_CONCAT(DISTINCT SUBSTRING(CONCAT(\' \', PS.name), 1) SEPARATOR \'<br />\')', 'filter' => 'having'), 'vat' => array('source' => 'CONCAT(V.value, \'%\')', 'prepareForSelect' => true), 'stock' => array('source' => 'P.stock'), 'enable' => array('source' => 'P.enable'), 'weight' => array('source' => 'P.weight'), 'adddate' => array('source' => 'P.adddate'), 'thumb' => array('source' => 'PP.photoid', 'processFunction' => array($this, 'getThumbPathForId')), 'attributes' => array('source' => 'PAS.idproductattributeset'), 'trackstock' => array('source' => 'P.trackstock'), 'disableatstockenabled' => array('source' => 'P.disableatstockenabled')));
$datagrid->setFrom('
product P
LEFT JOIN producttranslation PT ON P.idproduct = PT.productid AND PT.languageid = :languageid
LEFT JOIN productcategory PC ON PC.productid = P.idproduct
LEFT JOIN productphoto PP ON PP.productid = P.idproduct AND PP.mainphoto = 1
LEFT JOIN productstatuses PSS ON PSS.productid = P.idproduct
LEFT JOIN productstatus PS ON PS.idproductstatus = PSS.productstatusid
LEFT JOIN productattributeset PAS ON PAS.productid = P.idproduct
LEFT JOIN viewcategory VC ON PC.categoryid = VC.categoryid
LEFT JOIN category C ON C.idcategory = PC.categoryid
LEFT JOIN categorytranslation CT ON C.idcategory = CT.categoryid AND CT.languageid = :languageid
LEFT JOIN categorypath CP ON C.idcategory = CP.categoryid
LEFT JOIN producertranslation PRT ON P.producerid = PRT.producerid AND PRT.languageid = :languageid
LEFT JOIN productdeliverer PD ON PD.productid = P.idproduct
LEFT JOIN deliverertranslation DT ON PD.delivererid = DT.delivererid AND DT.languageid = :languageid
LEFT JOIN vat V ON P.vatid = V.idvat
');
$datagrid->setGroupBy('
P.idproduct
');
if (Helper::getViewId() > 0) {
$datagrid->setAdditionalWhere('
IF(PC.categoryid IS NOT NULL, VC.viewid IN (' . Helper::getViewIdsAsString() . '), 1)
');
}
}
