How can I get the proper result for TotalQt?
$query = $this->ItemLedgers
->find()
->where([
'ItemLedgers.processed_on <=' => date("Y-m-d",strtotime($to_date))
]);
$totalInCase = $query->newExpr()
->addCase(
$query->newExpr()->add(['in_out' => 'In']),
$query->newExpr()->add(['quantity']),
'integer'
);
$totalOutCase = $query->newExpr()
->addCase(
$query->newExpr()->add(['in_out' => 'Out']),
$query->newExpr()->add(['quantity']),
'integer'
);
$query
->select([
'total_in' => $query->func()->sum($totalInCase),
'total_out' => $query->func()->sum($totalOutCase),
'id',
'item_id',
'TotalQt' => $query->func()->sum($totalInCase) - $query->func()->sum($totalOutCase),
])
->contain(['Items' => function ($q) use($where) {
return $q
->where($where)
->contain(['Units']);
}])
->where(['company_id' => $st_company_id])
->group('item_id')
->autoFields(true)
->where($where);