/**
* Save the referral to the database.
*
* @param ReferralInterface $referral
*
* @return ReferralInterface
*/
public function save(ReferralInterface $referral)
{
$result = $this->_query->run('
INSERT INTO
refer_a_friend_referral
(
reward_config_id,
status,
referrer_id,
referred_email,
referred_name,
created_at,
created_by,
updated_at,
updated_by
)
VALUES
(
:rewardConfigID?i,
:status?s,
:referrerID?i,
:referredEmail?s,
:referredName?s,
:createdAt?d,
:createdBy?in,
:createdAt?d,
:createdBy?in
)
', ['rewardConfigID' => $referral->getRewardConfig()->getID(), 'status' => $referral->getStatus(), 'referrerID' => $referral->getReferrer()->id, 'referredEmail' => $referral->getReferredEmail(), 'referredName' => $referral->getReferredName(), 'createdAt' => $referral->getCreatedAt() ?: new \DateTime(), 'createdBy' => $this->_currentUser->id]);
$referral->setID($result->id());
return $referral;
}
protected function _setSubscribed($subscriber, $subscribed)
{
if (!$subscriber instanceof Subscriber) {
$email = $subscriber;
$subscriber = new Subscriber();
$subscriber->email = $email;
$subscriber->subscribed = (bool) $subscribed;
$subscriber->authorship->create(null, $this->_currentUser->id);
}
$subscriber->authorship->update(null, $this->_currentUser->id);
$result = $this->_query->run('
REPLACE INTO
email_subscription
SET
email = :email?s,
subscribed = :subscribed?b,
created_at = :createdAt?d,
created_by = :createdBy?in,
updated_at = :updatedAt?d,
updated_by = :updatedBy?in
', ['createdAt' => $subscriber->authorship->createdAt(), 'createdBy' => $subscriber->authorship->createdBy(), 'updatedAt' => $subscriber->authorship->updatedAt(), 'updatedBy' => $subscriber->authorship->updatedBy(), 'email' => $subscriber->email, 'subscribed' => $subscriber->subscribed]);
if ($this->_query instanceof DB\Transaction) {
return $subscriber;
}
return $this->_loader->getByEmail($subscriber->email);
}
/**
* Saves a payment-gateway mapping
*
* @param Payment $payment The payment
* @param GatewayInterface $gateway The gateway
*/
public function save(Payment $payment, GatewayInterface $gateway)
{
if (!$payment->id) {
throw new \LogicException("Payment ID must be set before saving payment gateway record.");
}
$this->_query->run("REPLACE INTO \n\t\t\t\t`payment_gateway`\n\t\t\tVALUES\n\t\t\t\t(?i, ?s)\n\t\t", [$payment->id, $gateway->getName()]);
}
public function getAll($includeDeleted = null)
{
if (null !== $includeDeleted) {
$this->includeDeleted($includeDeleted);
}
$result = $this->_query->run("\n\t\t\tSELECT DISTINCT\n\t\t\t\tcategory\n\t\t\tFROM\n\t\t\t\tproduct\n\t\t\tWHERE\n\t\t\t\tcategory IS NOT NULL\n\t\t\tAND\n\t\t\t\tcategory != ''\n\t\t\t" . (!$this->_includeDeleted ? " AND deleted_at IS NULL " : "") . "\n\t\t");
return $result->flatten();
}
protected function _loadProductNames()
{
$result = $this->_query->run("\n\t\t\tSELECT\n\t\t\t\tproduct_id,\n\t\t\t\tname\n\t\t\tFROM\n\t\t\t\tproduct\n\t\t\tWHERE\n\t\t\t\tdeleted_at IS NULL\n\t\t\tORDER BY\n\t\t\t\tname ASC\n\t\t");
$this->_productList = [];
foreach ($result as $product) {
$this->_productList[$product->product_id] = $product->name;
}
}
protected function _getProducts()
{
$result = $this->_query->run("\n\t\t\tSELECT\n\t\t\t\tproduct_id,\n\t\t\t\tname\n\t\t\tFROM\n\t\t\t\tproduct\n\t\t\tWHERE\n\t\t\t\tdeleted_at IS NULL\n\t\t\tORDER BY\n\t\t\t\tname ASC\n\t\t");
$products = [];
foreach ($result as $row) {
$products[$row->product_id] = $row->name;
}
return $products;
}
/**
* Save bundle to the database
*
* @param Bundle $bundle
*
* @return Bundle Return Bundle with ID and authorship details updated.
*/
public function save(Bundle $bundle)
{
if (!$bundle->getAuthorship()->createdAt()) {
$bundle->getAuthorship()->create(new DateTimeImmutable(), $this->_user->id);
}
$result = $this->_query->run("\n\t\t\tINSERT INTO\n\t\t\t\tdiscount_bundle\n\t\t\t\t(\n\t\t\t\t\t`name`,\n\t\t\t\t\tallow_codes,\n\t\t\t\t\tstart,\n\t\t\t\t\t`end`,\n\t\t\t\t\tcreated_at,\n\t\t\t\t\tcreated_by\n\t\t\t\t)\n\t\t\tVALUES\n\t\t\t\t(\n\t\t\t\t\t:name?s,\n\t\t\t\t\t:allowsCodes?b,\n\t\t\t\t\t:start?dn,\n\t\t\t\t\t:end?dn,\n\t\t\t\t\t:createdAt?d,\n\t\t\t\t\t:createdBy?i\n\t\t\t\t)\n\t\t", ['name' => $bundle->getName(), 'allowsCodes' => $bundle->allowsCodes(), 'start' => $bundle->getStart(), 'end' => $bundle->getEnd(), 'createdAt' => new \DateTime(), 'createdBy' => $this->_user->id]);
$bundle->setID($result->id());
$this->_bundleProductCreate->save($bundle);
$this->_bundlePriceCreate->save($bundle);
$this->_bundleImageCreate->save($bundle);
return $bundle;
}
public function _load($emails, $alwaysReturnArray = false)
{
if (!is_array($emails)) {
$emails = array($emails);
}
if (!$emails) {
return $alwaysReturnArray ? array() : false;
}
$result = $this->_query->run('
SELECT
email_subscription.*,
forename,
surname
FROM
email_subscription
LEFT JOIN
user USING (email)
WHERE
email IN (?sj)
', array($emails));
$return = array();
$resultData = $result->transpose('email');
foreach ($emails as $email) {
$subscriber = new Subscriber();
if (array_key_exists($email, $resultData)) {
$row = $resultData[$email];
$subscriber->email = $row->email;
$subscriber->forename = $row->forename;
$subscriber->surname = $row->surname;
$subscriber->subscribed = (bool) $row->subscribed;
if ($row->created_at) {
$subscriber->authorship->create(new DateTimeImmutable(date('c', $row->created_at)), $row->created_by);
}
if ($row->updated_at) {
$subscriber->authorship->update(new DateTimeImmutable(date('c', $row->updated_at)), $row->updated_by);
}
} else {
$subscriber->email = $email;
$subscriber->subscribed = false;
}
$return[$email] = $subscriber;
}
return $alwaysReturnArray || count($return) > 1 ? $return : reset($return);
}
public function _loadRefunds($return)
{
$refundIDs = $this->_query->run("\n\t\t\tSELECT\n\t\t\t\trefund_id\n\t\t\tFROM\n\t\t\t\treturn_refund\n\t\t\tWHERE\n\t\t\t\treturn_id = :returnID?i\n\t\t", ['returnID' => $return->id]);
$refundIDs = $refundIDs->flatten('refund_id');
$refunds = $this->_refundLoader->getByID($refundIDs) ?: [];
if (!is_array($refunds)) {
$refunds = [$refunds];
}
return $refunds;
}
/**
* Save product data assigned to a bundle to the database
*
* @param Bundle $bundle The bundle the product rows are assigned to
* @param bool $delete If set to true, existing product rows for the bundle will be deleted. True by default.
*/
public function save(Bundle $bundle, $delete = true)
{
if ($delete) {
$this->_query->run("\n\t\t\t\tDELETE FROM\n\t\t\t\t\tdiscount_bundle_product_row\n\t\t\t\tWHERE\n\t\t\t\t\tbundle_id = :bundleID?i\n\t\t\t", ['bundleID' => $bundle->getID()]);
$this->_query->run("\n\t\t\t\tDELETE FROM\n\t\t\t\t\tdiscount_bundle_product_option\n\t\t\t\tWHERE\n\t\t\t\t\tbundle_id = :bundleID?i\n\t\t\t", ['bundleID' => $bundle->getID()]);
}
foreach ($bundle->getProductRows() as $row) {
$result = $this->_query->run("\n\t\t\t\tINSERT INTO\n\t\t\t\t\tdiscount_bundle_product_row\n\t\t\t\t\t(\n\t\t\t\t\t\tbundle_id,\n\t\t\t\t\t\tproduct_id,\n\t\t\t\t\t\tquantity\n\t\t\t\t\t)\n\t\t\t\tVALUES\n\t\t\t\t\t(\n\t\t\t\t\t\t:bundleID?i,\n\t\t\t\t\t\t:productID?i,\n\t\t\t\t\t\t:quantity?i\n\t\t\t\t\t)\n\t\t\t", ['bundleID' => $bundle->getID(), 'productID' => $row->getProductID(), 'quantity' => $row->getQuantity()]);
$statements = [];
if (count($row->getOptions()) > 0) {
foreach ($row->getOptions() as $name => $value) {
$statement = "\n\t\t\t\t\t(\n\t\t\t\t\t\t:rowID?i,\n\t\t\t\t\t\t:bundleID?i,\n\t\t\t\t\t\t:name?s,\n\t\t\t\t\t\t:value?s\n\t\t\t\t\t)";
$params = ['rowID' => $result->id(), 'bundleID' => $bundle->getID(), 'name' => $name, 'value' => $value];
$statements[] = $this->_queryParser->parse($statement, $params);
}
$statements = implode(',' . PHP_EOL, $statements);
$this->_query->run("\n\t\t\t\t\tINSERT INTO\n\t\t\t\t\t\tdiscount_bundle_product_option\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\tproduct_row_id,\n\t\t\t\t\t\t\tbundle_id,\n\t\t\t\t\t\t\toption_name,\n\t\t\t\t\t\t\toption_value\n\t\t\t\t\t\t)\n\t\t\t\t\tVALUES\n\t\t\t\t" . $statements);
}
}
}
/**
* This loads only the information we need, and assigns it to a Barcode object. As it stands the system does not
* load the products efficiently enough and will break if the load is too high, so this binds to a lightweight
* Barcode object to mitigate this problem, see https://github.com/messagedigital/cog-mothership-commerce/issues/297
*
* @param $unitIDs array Not currently used but will be useful when dealing with individual units
*
* @return \Message\Cog\DB\Result
*/
protected function _getBarcodes($unitIDs = [])
{
$barcodes = $this->_query->run("\n\t\t\tSELECT DISTINCT\n\t\t\t\tu.unit_id AS unitID,\n\t\t\t\tp.brand,\n\t\t\t\tp.name,\n\t\t\t\tu.barcode,\n\t\t\t\tIFNULL(up.price, pp.price) AS price,\n\t\t\t\tIFNULL(up.currency_id, pp.currency_id) AS currency,\n\t\t\t\tGROUP_CONCAT(DISTINCT o.option_value SEPARATOR ', ') AS text\n\t\t\tFROM\n\t\t\t\tproduct_unit AS u\n\t\t\tLEFT JOIN\n\t\t\t\tproduct AS p\n\t\t\tUSING\n\t\t\t\t(product_id)\n\t\t\tLEFT JOIN\n\t\t\t\tproduct_unit_option AS o\n\t\t\tUSING\n\t\t\t\t(unit_id)\n\t\t\tLEFT JOIN\n\t\t\t\tproduct_unit_price AS up\n\t\t\tON\n\t\t\t\t(u.unit_id = up.unit_id AND up.type = :retail?s AND up.currency_id = :currencyID?s)\n\t\t\tLEFT JOIN\n\t\t\t\tproduct_info AS pi\n\t\t\tUSING\n\t\t\t\t(product_id)\n\t\t\tLEFT JOIN\n\t\t\t\tproduct_price AS pp\n\t\t\tON\n\t\t\t\t(u.product_id = pp.product_id AND pp.type = :retail?s AND pp.currency_id = :currencyID?s)\n\t\t\tWHERE\n\t\t\t\tbarcode IS NOT NULL\n\t\t\tAND\n\t\t\t\tbarcode != ''\n\t\t\t" . ($unitIDs ? "AND u.unit_id IN (:unitIDs?ij)" : "") . "\n\t\t\tGROUP BY\n\t\t\t\tu.unit_id\n\t\t\tORDER BY\n\t\t\t\tp.category ASC,\n\t\t\t\tCOALESCE(\n\t\t\t\t\tCONCAT(pi.sort_name, p.name),\n\t\t\t\t\tp.name\n\t\t\t\t)\n\t\t", ['retail' => 'retail', 'currencyID' => 'GBP', 'unitIDs' => $unitIDs])->bindTo('\\Message\\Mothership\\Commerce\\Product\\Barcode\\Barcode');
foreach ($barcodes as $barcode) {
$code = $barcode->getBarcode();
$barcode->text = trim($barcode->text, ' ,');
$barcode->file = $this->_getBarcodeImage($code);
$barcode->url = $barcode->file->getPublicUrl();
}
return $barcodes;
}
public function install()
{
$this->_query->run('
CREATE TABLE IF NOT EXISTS
migration (
migration_id INT (11) AUTO_INCREMENT,
adapter VARCHAR (255),
path TEXT,
batch INT (11),
run_at INT (11),
PRIMARY KEY (migration_id)
)
');
}
protected function _validateEmail(Discount $discount)
{
if (!empty($discount->emails)) {
if ($this->_order->userEmail || $this->_order->user) {
$email = $this->_order->userEmail ?: $this->_order->user->email;
if (!$email) {
throw new OrderValidityException(self::INVALID_EMAIL);
}
$result = $this->_query->run("\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tused_at\n\t\t\t\t\tFROM\n\t\t\t\t\t\tdiscount_email\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tdiscount_id = :id?i\n\t\t\t\t\tAND\n\t\t\t\t\t\tLOWER(email) = :email?s\n\t\t\t\t", ['id' => $discount->id, 'email' => strtolower($email)])->flatten();
if (empty($result)) {
throw new OrderValidityException(self::INVALID_EMAIL);
}
$usedAt = array_shift($result);
// if ($usedAt) {
// throw new OrderValidityException(self::ALREADY_USED);
// }
}
}
}
protected function _loadProduct($productIDs, $limit = null)
{
if (!is_array($productIDs)) {
$productIDs = (array) $productIDs;
}
if (0 === $this->_entityLoaders->count()) {
throw new \LogicException('Cannot load products when entity loaders are not set.');
}
$cachedProducts = [];
// Load products from cache if limit is not set. If limit is set this complicates the following query so
// this step is skipped.
if (null === $limit) {
foreach ($productIDs as $key => $id) {
if ($this->_productCache->exists($id)) {
$cachedProduct = $this->_productCache->get($id);
// Only load deleted products from the cache if `_includeDeleted` is set to true
if ($this->_includeDeleted || !$cachedProduct->authorship->isDeleted()) {
$cachedProducts[] = $cachedProduct;
unset($productIDs[$key]);
}
}
}
} else {
$this->_checkLimit($limit);
}
// If there are no uncached products remaining, return the cached product/s
if (count($productIDs) <= 0) {
return $this->_returnArray ? $cachedProducts : array_shift($cachedProducts);
}
$result = $this->_query->run('SELECT
product.product_id AS id,
product.product_id AS catalogueID,
product.created_at AS createdAt,
product.created_by AS createdBy,
product.updated_at AS updatedAt,
product.updated_by AS updatedBy,
product.deleted_at AS deletedAt,
product.deleted_by AS deletedBy,
product.brand AS brand,
product.type AS type,
product.name AS name,
product.category AS category,
product.tax_strategy AS taxStrategy,
product.tax_rate AS taxRate,
product.supplier_ref AS supplierRef,
product.weight_grams AS weight,
product_info.display_name AS displayName,
product_info.sort_name AS sortName,
product_info.description AS description,
product_info.short_description AS shortDescription,
product_info.notes AS notes,
product_export.export_description AS exportDescription,
product_export.export_value AS exportValue,
product_export.export_manufacture_country_id AS exportManufactureCountryID,
product_export.export_code AS exportCode
FROM
product
LEFT JOIN
product_info ON (product.product_id = product_info.product_id)
LEFT JOIN
product_export ON (product.product_id = product_export.product_id)
WHERE
product.product_id IN (?ij)
' . (!$this->_includeDeleted ? 'AND product.deleted_at IS NULL' : '') . '
' . ($limit ? 'LIMIT 0, ' . (int) $limit : '') . '
', array((array) $productIDs));
$tags = $this->_query->run('SELECT
product_tag.product_id AS id,
product_tag.name AS name
FROM
product_tag
WHERE
product_tag.product_id IN (?ij)
', array((array) $productIDs));
$products = $result->bindTo('Message\\Mothership\\Commerce\\Product\\ProductProxy', [$this->_locale, $this->_priceTypes, $this->_defaultCurrency, $this->_entityLoaders, clone $this->_taxStrategy]);
foreach ($result as $key => $data) {
// needs to set the product type if inclusive to get the base tax
if ($products[$key]->getTaxStrategy()->getName() === 'inclusive') {
$products[$key]->getTaxStrategy()->setProductType($data->type);
}
$data->taxRate = (double) $data->taxRate;
$data->exportValue = (double) $data->exportValue;
$products[$key]->authorship->create(new DateTimeImmutable(date('c', $data->createdAt)), $data->createdBy);
if ($data->updatedAt) {
$products[$key]->authorship->update(new DateTimeImmutable(date('c', $data->updatedAt)), $data->updatedBy);
}
if ($data->deletedAt) {
$products[$key]->authorship->delete(new DateTimeImmutable(date('c', $data->deletedAt)), $data->deletedBy);
}
foreach ($tags as $k => $tag) {
if ($tag->id == $data->id) {
$products[$key]->tags[$k] = $tag->name;
}
}
$this->_loadType($products[$key], $data->type);
// Add product to cache
$this->_productCache->add($products[$key]);
}
// Merge cached products and main products array
$products = array_merge($products, $cachedProducts);
return count($products) == 1 && !$this->_returnArray ? array_shift($products) : $products;
}
/**
* Save a barcode the database without generating a new one
*
* @param Unit $unit
*/
public function save(Unit $unit)
{
$this->_validateBarcode($unit->barcode);
$this->_query->run("\n\t\t\tUPDATE\n\t\t\t\tproduct_unit\n\t\t\tSET\n\t\t\t\tbarcode = :barcode?s\n\t\t\tWHERE\n\t\t\t\tunit_id = :id?i\n\t\t", ['barcode' => $unit->barcode, 'id' => $unit->id]);
}
/**
* Mark a bundle as not deleted
*
* @param Bundle $bundle
*/
public function restore(Bundle $bundle)
{
$this->_query->run("\n\t\t\tUPDATE\n\t\t\t\tdiscount_bundle\n\t\t\tSET\n\t\t\t\tdeleted_at = NULL,\n\t\t\t\tdeleted_by = NULL\n\t\t\tWHERE\n\t\t\t\tbundle_id = :id?i\n\t\t", ['id' => $bundle->getID()]);
}
/**
* Save the Config details to the database. Does not save entities.
*
* @param Config $config
*
* @return Config
*/
public function save(Config $config)
{
$result = $this->_query->run("\n\t\t\tINSERT INTO\n\t\t\t\trefer_a_friend_reward_config\n\t\t\t\t(\n\t\t\t\t\t`name`,\n\t\t\t\t\t`type`,\n\t\t\t\t\tmessage,\n\t\t\t\t\tcreated_at,\n\t\t\t\t\tcreated_by\n\t\t\t\t)\n\t\t\tVALUES\n\t\t\t\t(\n\t\t\t\t\t:name?s,\n\t\t\t\t\t:type?s,\n\t\t\t\t\t:message?s,\n\t\t\t\t\t:createdAt?d,\n\t\t\t\t\t:createdBy?i\n\t\t\t\t)\n\t\t\t;\n\t\t", ['name' => $config->getName(), 'type' => $config->getType()->getName(), 'message' => $config->getMessage(), 'createdAt' => new \DateTime(), 'createdBy' => $this->_currentUser->id]);
$config->setID($result->id());
return $config;
}
