public function generateLink($email)
{
$sql = 'SELECT SHA1(AES_ENCRYPT(SHA1(:email), :encryptionkey)) AS link';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue());
$stmt->bindValue('email', $email);
$stmt->execute();
$rs = $stmt->fetch();
return $rs['link'];
}
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 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 static function getEncryptionKey()
{
return Session::getActiveEncryptionKeyValue();
}
protected function addOrder($Data)
{
Db::getInstance()->beginTransaction();
$email = $Data['email'];
$password = Core::passwordGenerate();
$hash = new \PasswordHash\PasswordHash();
$sql = 'SELECT idclient FROM client WHERE login = :login';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('login', $hash->HashLogin($email));
$stmt->execute();
$rs = $stmt->fetch();
if ($rs) {
// Update
} else {
$sql = 'INSERT INTO client (login, password, disable, viewid)
VALUES (:login, :password, :disable, :viewid)';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('login', $hash->HashLogin($email));
$stmt->bindValue('password', $hash->HashPassword($password));
$stmt->bindValue('disable', isset($Data['disable']) ? $Data['disable'] : 0);
$stmt->bindValue('viewid', Helper::getViewId());
try {
$stmt->execute();
} catch (Exception $e) {
throw new FrontendException($e->getMessage());
}
$idClient = Db::getInstance()->lastInsertId();
$sql = 'INSERT INTO clientdata SET
firstname = AES_ENCRYPT(:firstname, :encryptionKey),
surname = AES_ENCRYPT(:surname, :encryptionKey),
email = AES_ENCRYPT(:email, :encryptionKey),
phone = AES_ENCRYPT(:phone, :encryptionKey),
phone2 = AES_ENCRYPT(:phone2, :encryptionKey),
description = AES_ENCRYPT(:description, :encryptionKey),
clientgroupid = 10,
clientid = :clientid
';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('clientid', $idClient);
$stmt->bindValue('firstname', $Data['firstname']);
$stmt->bindValue('surname', $Data['surname']);
$stmt->bindValue('email', $Data['email']);
$stmt->bindValue('phone', $Data['phone']);
$stmt->bindValue('phone2', !empty($Data['phone2']) ? $Data['phone2'] : '');
$stmt->bindValue('description', !empty($Data['description']) ? $Data['description'] : '');
$stmt->bindValue('encryptionKey', Session::getActiveEncryptionKeyValue());
try {
$stmt->execute();
} catch (Exception $e) {
throw new FrontendException($e->getMessage());
}
$sql = 'INSERT INTO clientaddress SET
clientid = :clientid,
main = :main,
firstname = AES_ENCRYPT(:firstname, :encryptionKey),
surname = AES_ENCRYPT(:surname, :encryptionKey),
companyname = AES_ENCRYPT(:companyname, :encryptionKey),
street = AES_ENCRYPT(:street, :encryptionKey),
streetno = AES_ENCRYPT(:streetno, :encryptionKey),
placeno = AES_ENCRYPT(:placeno, :encryptionKey),
postcode = AES_ENCRYPT(:postcode, :encryptionKey),
nip = AES_ENCRYPT(:nip, :encryptionKey),
placename = AES_ENCRYPT(:placename, :encryptionKey),
countryid = :countryid
ON DUPLICATE KEY UPDATE
firstname = AES_ENCRYPT(:firstname, :encryptionKey),
surname = AES_ENCRYPT(:surname, :encryptionKey),
companyname = AES_ENCRYPT(:companyname, :encryptionKey),
street = AES_ENCRYPT(:street, :encryptionKey),
streetno = AES_ENCRYPT(:streetno, :encryptionKey),
placeno = AES_ENCRYPT(:placeno, :encryptionKey),
postcode = AES_ENCRYPT(:postcode, :encryptionKey),
nip = AES_ENCRYPT(:nip, :encryptionKey),
placename = AES_ENCRYPT(:placename, :encryptionKey),
countryid = :countryid';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('encryptionKey', Session::getActiveEncryptionKeyValue());
$stmt->bindValue('clientid', $idClient);
$stmt->bindValue('main', 1);
$stmt->bindValue('firstname', $Data['firstname']);
$stmt->bindValue('surname', $Data['surname']);
$stmt->bindValue('companyname', $Data['companyname']);
$stmt->bindValue('street', $Data['street']);
$stmt->bindValue('streetno', $Data['streetno']);
$stmt->bindValue('postcode', $Data['postcode']);
$stmt->bindValue('placeno', $Data['placeno']);
$stmt->bindValue('nip', $Data['nip']);
$stmt->bindValue('placename', $Data['placename']);
$stmt->bindValue('countryid', $this->getCountryByName($Data['country']));
try {
$stmt->execute();
} catch (Exception $e) {
throw new FrontendException($e->getMessage());
}
$sql = 'INSERT INTO clientaddress SET
clientid = :clientid,
main = :main,
firstname = AES_ENCRYPT(:firstname, :encryptionKey),
surname = AES_ENCRYPT(:surname, :encryptionKey),
companyname = AES_ENCRYPT(:companyname, :encryptionKey),
street = AES_ENCRYPT(:street, :encryptionKey),
streetno = AES_ENCRYPT(:streetno, :encryptionKey),
placeno = AES_ENCRYPT(:placeno, :encryptionKey),
postcode = AES_ENCRYPT(:postcode, :encryptionKey),
nip = AES_ENCRYPT(:nip, :encryptionKey),
placename = AES_ENCRYPT(:placename, :encryptionKey),
countryid = :countryid
ON DUPLICATE KEY UPDATE
firstname = AES_ENCRYPT(:firstname, :encryptionKey),
surname = AES_ENCRYPT(:surname, :encryptionKey),
companyname = AES_ENCRYPT(:companyname, :encryptionKey),
street = AES_ENCRYPT(:street, :encryptionKey),
streetno = AES_ENCRYPT(:streetno, :encryptionKey),
placeno = AES_ENCRYPT(:placeno, :encryptionKey),
postcode = AES_ENCRYPT(:postcode, :encryptionKey),
nip = AES_ENCRYPT(:nip, :encryptionKey),
placename = AES_ENCRYPT(:placename, :encryptionKey),
countryid = :countryid';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('encryptionKey', Session::getActiveEncryptionKeyValue());
$stmt->bindValue('clientid', $idClient);
$stmt->bindValue('main', 0);
$stmt->bindValue('firstname', $Data['firstname']);
$stmt->bindValue('surname', $Data['surname']);
$stmt->bindValue('companyname', $Data['companyname']);
$stmt->bindValue('street', !empty($Data['street2']) ? $Data['street2'] : $Data['street']);
$stmt->bindValue('streetno', !empty($Data['streetno2']) ? $Data['streetno2'] : $Data['streetno']);
$stmt->bindValue('postcode', !empty($Data['postcode2']) ? $Data['postcode2'] : $Data['postcode']);
$stmt->bindValue('placeno', !empty($Data['placeno2']) ? $Data['placeno2'] : $Data['placeno']);
$stmt->bindValue('nip', $Data['nip']);
$stmt->bindValue('placename', !empty($Data['placename2']) ? $Data['placename2'] : $Data['placename']);
$stmt->bindValue('countryid', $this->getCountryByName(!empty($Data['country2']) ? $Data['country2'] : $Data['country']));
try {
$stmt->execute();
} catch (Exception $e) {
throw new FrontendException($e->getMessage());
}
}
Db::getInstance()->commit();
}
public function clientGroupClients($id)
{
$sql = 'SELECT
AES_DECRYPT(firstname, :encryptionkey) AS firstname,
AES_DECRYPT(surname, :encryptionkey) AS surname,
idclientgroup as id
FROM clientdata
LEFT JOIN clientgroup ON idclientgroup = clientgroupid
WHERE clientgroupid= :id';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('id', $id);
$stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue());
$stmt->execute();
return $stmt->fetchAll();
}
public function getClientAddress($id, $main)
{
$Data = array('idclientaddress' => '', 'firstname' => '', 'surname' => '', 'companyname' => '', 'nip' => '', 'street' => '', 'streetno' => '', 'placeno' => '', 'placename' => '', 'postcode' => '', 'countryid' => '');
$sql = "SELECT \t \n\t\t\t\t\tidclientaddress,\n\t\t\t\t\tAES_DECRYPT(firstname, :encryptionkey) AS firstname,\n\t\t\t\t\tAES_DECRYPT(surname, :encryptionkey) AS surname,\n\t\t\t\t\tAES_DECRYPT(companyname, :encryptionkey) AS companyname,\n\t\t\t\t\tAES_DECRYPT(nip, :encryptionkey) AS nip,\n\t\t\t\t\tAES_DECRYPT(street, :encryptionkey) AS street,\n\t\t\t\t\tAES_DECRYPT(streetno, :encryptionkey) AS streetno,\n\t\t\t\t\tAES_DECRYPT(postcode, :encryptionkey) AS postcode,\n\t\t\t\t\tAES_DECRYPT(placename, :encryptionkey) AS placename,\n\t\t\t\t\tAES_DECRYPT(placeno, :encryptionkey) AS placeno,\n\t\t\t\t\tcountryid\n\t\t\t\tFROM clientaddress\n\t\t\t\tWHERE clientid=:clientid AND main = :main";
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('clientid', $id);
$stmt->bindValue('main', $main);
$stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue());
$stmt->execute();
$rs = $stmt->fetch();
try {
if ($rs) {
$Data = array('idclientaddress' => $rs['idclientaddress'], 'firstname' => $rs['firstname'], 'surname' => $rs['surname'], 'companyname' => $rs['companyname'], 'nip' => $rs['nip'], 'street' => $rs['street'], 'streetno' => $rs['streetno'], 'placeno' => $rs['placeno'], 'placename' => $rs['placename'], 'postcode' => $rs['postcode'], 'countryid' => $rs['countryid']);
}
} catch (Exception $e) {
throw new FrontendException(_('ERR_CLIENT_NO_EXIST'));
}
return $Data;
}
public function getOrderDeliveryData($idorder)
{
$sql = "SELECT\n\t\t\t\t\tAES_DECRYPT(firstname, :encryptionKey) AS firstname,\n\t\t\t\t\tAES_DECRYPT(surname, :encryptionKey) AS surname,\n\t\t\t\t\tAES_DECRYPT(street, :encryptionKey) AS street,\n\t\t\t\t\tAES_DECRYPT(streetno, :encryptionKey) AS streetno,\n\t\t\t\t\tAES_DECRYPT(placeno, :encryptionKey) AS placeno,\n\t\t\t\t\tAES_DECRYPT(postcode, :encryptionKey) AS postcode,\n\t\t\t\t\tAES_DECRYPT(place, :encryptionKey) AS place,\n \t\t\tO.dispatchmethodname\n \t\t\t\tFROM orderclientdeliverydata ODC\n\t\t\t\tLEFT JOIN `order`O ON ODC.orderid = O.idorder\n\t\t\t\tWHERE ODC.orderid = :idorder";
$Data = array();
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('idorder', $idorder);
$stmt->bindValue('encryptionKey', Session::getActiveEncryptionKeyValue());
$stmt->execute();
$rs = $stmt->fetch();
if ($rs) {
$Data = array('firstname' => $rs['firstname'], 'surname' => $rs['surname'], 'street' => $rs['street'], 'streetno' => $rs['streetno'], 'placeno' => $rs['placeno'], 'postcode' => $rs['postcode'], 'place' => $rs['place'], 'placename' => $rs['place'], 'dispatchmethodname' => $rs['dispatchmethodname']);
}
return $Data;
}
public function saveClientData()
{
if (Session::getActiveClientid() == 0) {
return false;
}
$sql = 'SELECT
AES_DECRYPT(email, :encryptionkey) AS email,
AES_DECRYPT(firstname, :encryptionkey) AS firstname,
AES_DECRYPT(surname, :encryptionkey) AS surname,
clientgroupid
FROM clientdata
LEFT JOIN client C ON C.idclient= :clientid
WHERE clientid= :clientid AND C.viewid= :viewid';
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('clientid', Session::getActiveClientid());
$stmt->bindValue('viewid', Helper::getViewId());
$stmt->bindValue('encryptionkey', Session::getActiveEncryptionKeyValue());
$stmt->execute();
$rs = $stmt->fetch();
if ($rs) {
Session::setActiveClientFirstname($rs['firstname']);
Session::setActiveClientSurname($rs['surname']);
Session::setActiveClientEmail($rs['email']);
Session::setActiveClientGroupid($rs['clientgroupid']);
}
return true;
}
