62 lines
2.0 KiB
PHP
62 lines
2.0 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace KupShop\AdminBundle\Query;
|
|
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
|
|
class StockIn
|
|
{
|
|
public static function getAveragePriceSubQuery(?\DateTime $date = null): QueryBuilder
|
|
{
|
|
if ($dateFromLastClosure = static::getDateFromLastClosure($date)) {
|
|
$dateFrom = $dateFromLastClosure->format('Y-m-d H:i:s');
|
|
} else {
|
|
$dateFrom = (new \DateTime())->format('Y-01-01 00:00:00');
|
|
}
|
|
|
|
if (!$date) {
|
|
$dateTo = (new \DateTime())->format('Y-12-31 23:59:59');
|
|
} else {
|
|
$dateTo = $date->format('Y-m-d 23:59:59');
|
|
}
|
|
|
|
$priceField = 'sii.price';
|
|
if (findModule(\Modules::STOCK_IN, \Modules::SUB_WEIGHTED_PURCHASE_PRICE)) {
|
|
$priceField = '(sii.price+COALESCE(sii.additional_costs, 0))';
|
|
}
|
|
|
|
return sqlQueryBuilder()->select("sii.id_product, sii.id_variation,
|
|
SUM(sii.quantity*{$priceField})/SUM(sii.quantity) average_price")
|
|
->from('stock_in_items', 'sii')
|
|
->join('sii', 'stock_in', 'si', 'si.id=sii.id_stock_in')
|
|
->where(Operator::between('COALESCE(si.date_stock_in, si.date_issued)', new \Range($dateFrom, $dateTo)))
|
|
->andWhere('si.id_index NOT IN ("future")')
|
|
->andWhere('sii.quantity > 0')
|
|
->groupBy('sii.id_product, sii.id_variation');
|
|
}
|
|
|
|
public static function getDateFromLastClosure(?\DateTime $date): ?\DateTime
|
|
{
|
|
$date = $date ?: new \DateTime();
|
|
|
|
$closureDate = sqlQueryBuilder()
|
|
->select('date_issued')
|
|
->from('stock_in')
|
|
->andWhere(Operator::equals(['id_index' => 'closure']))
|
|
->andWhere('date_created < :date')
|
|
->orderBy('id', 'DESC')
|
|
->setMaxResults(1)
|
|
->setParameter('date', $date->format('Y-m-d H:i:s'))
|
|
->execute()->fetchOne();
|
|
|
|
if (!$closureDate) {
|
|
return null;
|
|
}
|
|
|
|
return new \DateTime($closureDate);
|
|
}
|
|
}
|