55 lines
4.7 KiB
PHP
55 lines
4.7 KiB
PHP
<?php
|
|
namespace App\Modules\Inventory;
|
|
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Illuminate\Support\Facades\Crypt;
|
|
|
|
/** Physical registration-period quantities, projected from the canonical book only. */
|
|
final class StockPeriodReport
|
|
{
|
|
public function __construct(private ConnectionInterface $db){}
|
|
public function read(array $binding,string $fingerprint,array $filters,?string $token):array
|
|
{
|
|
return $this->db->transaction(function()use($binding,$fingerprint,$filters,$token){
|
|
abort_unless($this->db->transactionLevel()===1,503);
|
|
$this->db->statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY');
|
|
$this->db->statement("SET LOCAL statement_timeout='3s'");
|
|
$reader=new \Tempo\Inventory\InventoryReader($this->db,$binding['schema'],$binding['source_tenant_id'],$binding['warehouse_ids']);
|
|
try{$revision=$reader->revision();}catch(\UnexpectedValueException){abort(503,'Fonte de stock incompatível.');}
|
|
$observed=time();
|
|
if($token!==null){
|
|
try{$cut=json_decode(Crypt::decryptString($token),true,32,JSON_THROW_ON_ERROR);}catch(\Throwable){abort(409,'Referência de relatório inválida. Actualize a consulta.');}
|
|
abort_unless(($cut['fingerprint']??null)===$fingerprint&&($cut['revision']??null)===$revision
|
|
&&is_int($cut['observed']??null)&&$cut['observed']<=time()&&$cut['observed']>=time()-600,409,'A fonte ou o contexto mudou. Actualize o relatório.');
|
|
$observed=$cut['observed'];
|
|
}
|
|
$from=$filters['from'].' 00:00:00';
|
|
$until=(new \DateTimeImmutable($filters['to'],new \DateTimeZone('UTC')))->modify('+1 day')->format('Y-m-d').' 00:00:00';
|
|
$q=$this->db->table('public.stock_movements AS m')
|
|
->join('public.stock_items AS i',fn($j)=>$j->on('i.id','=','m.stock_item_id')->on('i.tenant_id','=','m.tenant_id'))
|
|
->join('public.warehouses AS w',fn($j)=>$j->on('w.id','=','m.warehouse_id')->on('w.tenant_id','=','m.tenant_id'))
|
|
->where('m.tenant_id',$binding['source_tenant_id'])->whereIn('m.warehouse_id',$binding['warehouse_ids'])->where('m.moved_at','<',$until);
|
|
if($filters['warehouse_id']!==null)$q->where('m.warehouse_id',$filters['warehouse_id']);
|
|
if($filters['search']!==''){
|
|
$literal='%'.str_replace(['\\','%','_'],['\\\\','\\%','\\_'],$filters['search']).'%';
|
|
$q->where(fn($s)=>$s->where('i.code','ilike',$literal)->orWhere('i.name','ilike',$literal));
|
|
}
|
|
$q->select(['m.stock_item_id','m.warehouse_id','i.code','i.name','i.unit','w.code AS warehouse_code','w.name AS warehouse_name'])
|
|
->selectRaw('SUM(CASE WHEN m.moved_at < ? THEN m.quantity_milli::numeric ELSE 0 END)::text AS opening_milli',[$from])
|
|
->selectRaw('SUM(CASE WHEN m.moved_at >= ? AND m.quantity_milli > 0 THEN m.quantity_milli::numeric ELSE 0 END)::text AS incoming_milli',[$from])
|
|
->selectRaw('SUM(CASE WHEN m.moved_at >= ? AND m.quantity_milli < 0 THEN -m.quantity_milli::numeric ELSE 0 END)::text AS outgoing_milli',[$from])
|
|
->selectRaw('SUM(m.quantity_milli)::text AS closing_milli')
|
|
->selectRaw('COUNT(*) FILTER (WHERE m.moved_at >= ?)::text AS period_movements',[$from])
|
|
->groupBy('m.stock_item_id','m.warehouse_id','i.code','i.name','i.unit','w.code','w.name')
|
|
->orderBy('m.stock_item_id')->orderBy('m.warehouse_id');
|
|
$total=(int)$this->db->query()->fromSub((clone $q)->reorder(),'stock_period_pairs')->count();
|
|
$rows=$q->limit($filters['per_page'])->offset(($filters['page']-1)*$filters['per_page'])->get()->map(fn($r)=>(array)$r)->all();
|
|
return ['status'=>'available','synthetic_only'=>true,'report'=>'physical_period','period'=>['from'=>$filters['from'],'to'=>$filters['to'],'timezone'=>'UTC','basis'=>'registration_time'],
|
|
'rows'=>$rows,'pagination'=>['total'=>$total,'page'=>$filters['page'],'per_page'=>$filters['per_page'],'last_page'=>max(1,(int)ceil($total/$filters['per_page']))],
|
|
'observed_at'=>gmdate('c',$observed),'snapshot'=>Crypt::encryptString(json_encode(['fingerprint'=>$fingerprint,'revision'=>$revision,'observed'=>$observed],JSON_THROW_ON_ERROR)),
|
|
'ledger_revision'=>$revision,'capabilities'=>['write'=>false,'valuation'=>false,'reserved'=>false,'blocked'=>false],
|
|
'notice'=>'Quantidades por data de registo UTC; designações actuais. Não há total somado entre unidades diferentes. Sem valorização, reservas ou disponibilidade. Pares sem história até ao fecho não são saldos zero.'];
|
|
});
|
|
}
|
|
}
|