/**
* @param Lang $lang
* @return array|\Propel\Runtime\ActiveQuery\ModelCriteria
*
* The tax engine of Thelia is in PHP, so we can't compute orders for each customers
* directly in SQL, we need two SQL queries, and some computing to get the last order amount and total amount.
*/
public function buildDataSet(Lang $lang)
{
$locale = $lang->getLocale();
/**
* This first query get each customer info and addresses.
*/
$newsletterJoin = new Join(CustomerTableMap::EMAIL, NewsletterTableMap::EMAIL, Criteria::LEFT_JOIN);
$query = CustomerQuery::create()->useCustomerTitleQuery("customer_title_")->useCustomerTitleI18nQuery("customer_title_i18n_")->addAsColumn("title_TITLE", "customer_title_i18n_.SHORT")->endUse()->endUse()->useAddressQuery()->useCountryQuery()->useCountryI18nQuery()->addAsColumn("address_COUNTRY", CountryI18nTableMap::TITLE)->endUse()->endUse()->useCustomerTitleQuery("address_title")->useCustomerTitleI18nQuery("address_title_i18n")->addAsColumn("address_TITLE", "address_title_i18n.SHORT")->endUse()->endUse()->addAsColumn("address_LABEL", AddressTableMap::LABEL)->addAsColumn("address_FIRST_NAME", AddressTableMap::FIRSTNAME)->addAsColumn("address_LAST_NAME", AddressTableMap::LASTNAME)->addAsColumn("address_COMPANY", AddressTableMap::COMPANY)->addAsColumn("address_ADDRESS1", AddressTableMap::ADDRESS1)->addAsColumn("address_ADDRESS2", AddressTableMap::ADDRESS2)->addAsColumn("address_ADDRESS3", AddressTableMap::ADDRESS3)->addAsColumn("address_ZIPCODE", AddressTableMap::ZIPCODE)->addAsColumn("address_CITY", AddressTableMap::CITY)->addAsColumn("address_PHONE", AddressTableMap::PHONE)->addAsColumn("address_CELLPHONE", AddressTableMap::CELLPHONE)->addAsColumn("address_IS_DEFAULT", AddressTableMap::IS_DEFAULT)->endUse()->addJoinObject($newsletterJoin)->addAsColumn("newsletter_IS_REGISTRED", "IF(NOT ISNULL(" . NewsletterTableMap::EMAIL . "),1,0)")->select([CustomerTableMap::ID, CustomerTableMap::REF, CustomerTableMap::LASTNAME, CustomerTableMap::FIRSTNAME, CustomerTableMap::EMAIL, CustomerTableMap::DISCOUNT, CustomerTableMap::CREATED_AT, "title_TITLE", "address_TITLE", "address_LABEL", "address_COMPANY", "address_FIRST_NAME", "address_LAST_NAME", "address_ADDRESS1", "address_ADDRESS2", "address_ADDRESS3", "address_ZIPCODE", "address_CITY", "address_COUNTRY", "address_PHONE", "address_CELLPHONE", "address_IS_DEFAULT", "newsletter_IS_REGISTRED"])->orderById();
I18n::addI18nCondition($query, CountryI18nTableMap::TABLE_NAME, CountryTableMap::ID, CountryI18nTableMap::ID, CountryI18nTableMap::LOCALE, $locale);
I18n::addI18nCondition($query, CustomerTitleI18nTableMap::TABLE_NAME, "`customer_title_`.ID", "`customer_title_i18n_`.ID", "`customer_title_i18n_`.LOCALE", $locale);
I18n::addI18nCondition($query, CustomerTitleI18nTableMap::TABLE_NAME, "`address_title`.ID", "`address_title_i18n`.ID", "`address_title_i18n`.LOCALE", $locale);
/** @var CustomerQuery $query */
$results = $query->find()->toArray();
/**
* Then get the orders
*/
$orders = OrderQuery::create()->useCustomerQuery()->orderById()->endUse()->find();
/**
* And add them info the array
*/
$orders->rewind();
$arrayLength = count($results);
$previousCustomerId = null;
for ($i = 0; $i < $arrayLength; ++$i) {
$currentCustomer =& $results[$i];
$currentCustomerId = $currentCustomer[CustomerTableMap::ID];
unset($currentCustomer[CustomerTableMap::ID]);
if ($currentCustomerId === $previousCustomerId) {
$currentCustomer["title_TITLE"] = "";
$currentCustomer[CustomerTableMap::LASTNAME] = "";
$currentCustomer[CustomerTableMap::FIRSTNAME] = "";
$currentCustomer[CustomerTableMap::EMAIL] = "";
$currentCustomer["address_COMPANY"] = "";
$currentCustomer["newsletter_IS_REGISTRED"] = "";
$currentCustomer[CustomerTableMap::CREATED_AT] = "";
$currentCustomer[CustomerTableMap::DISCOUNT] = "";
$currentCustomer += ["order_TOTAL" => "", "last_order_AMOUNT" => "", "last_order_DATE" => ""];
} else {
/**
* Reformat created_at date
*/
$date = $currentCustomer[CustomerTableMap::CREATED_AT];
$dateTime = new \DateTime($date);
$currentCustomer[CustomerTableMap::CREATED_AT] = $dateTime->format($lang->getDatetimeFormat());
/**
* Then compute everything about the orders
*/
$total = 0;
$lastOrderAmount = 0;
$lastOrderDate = null;
$lastOrder = null;
$lastOrderCurrencyCode = null;
$lastOrderId = 0;
$defaultCurrency = Currency::getDefaultCurrency();
$defaultCurrencyCode = $defaultCurrency->getCode();
if (empty($defaultCurrencyCode)) {
$defaultCurrencyCode = $defaultCurrency->getCode();
}
$formattedDate = null;
/** @var \Thelia\Model\Order $currentOrder */
while (false !== ($currentOrder = $orders->current())) {
if ($currentCustomerId != $currentOrder->getCustomerId()) {
break;
}
$amount = $currentOrder->getTotalAmount($tax);
if (0 < ($rate = $currentOrder->getCurrencyRate())) {
$amount = round($amount / $rate, 2);
}
$total += $amount;
/** @var \DateTime $date */
$date = $currentOrder->getCreatedAt();
if (null === $lastOrderDate || $date >= $lastOrderDate && $lastOrderId < $currentOrder->getId()) {
$lastOrder = $currentOrder;
$lastOrderDate = $date;
$lastOrderId = $currentOrder->getId();
}
$orders->next();
}
if ($lastOrderDate !== null) {
$formattedDate = $lastOrderDate->format($lang->getDatetimeFormat());
$orderCurrency = $lastOrder->getCurrency();
$lastOrderCurrencyCode = $orderCurrency->getCode();
if (empty($lastOrderCurrencyCode)) {
$lastOrderCurrencyCode = $orderCurrency->getCode();
}
$lastOrderAmount = $lastOrder->getTotalAmount($tax_);
}
$currentCustomer += ["order_TOTAL" => $total . " " . $defaultCurrencyCode, "last_order_AMOUNT" => $lastOrderAmount === 0 ? "" : $lastOrderAmount . " " . $lastOrderCurrencyCode, "last_order_DATE" => $formattedDate];
}
$previousCustomerId = $currentCustomerId;
}
return $results;
}
