I have this function on my controller to print a monthly report. The report displays the balance from months before and display transaction inputs and outputs units from stocks in certain month.
I've tried query transactions in this month, but I can't insert balance's query from months before into one table.
public function p_report(Request $request) {
$report = DB::table('unit as u')
->select('u.id','name_u',
'price',
DB::raw('sum(qty_in) vol_in'),
DB::raw('sum(price*qty_in) qty_ins'),
DB::raw('sum(qty_out) vol_out'),
DB::raw('sum(price*qty_out) qty_outs'),
DB::raw('sum(qty_in)-sum(qty_out) vol_end'),
DB::raw('sum(price*qty_in)-sum(price*qty_out)qty_end')
)
->leftJoin('transaction as t', 't.unit_id', '=', 'u.id')
->where('status',1)
->where('type_unit', '=' , $request->type_unit)
->whereMonth('date_transaction','=', $request->month)
->whereYear('date_transaction', '=' , $request->year)
->groupBy(['u.id','name_u','price'])
->orderBy('name_u')
->get();
foreach ($report as $reports) {
$balance = DB::table('unit as u')
->select(
DB::raw('sum(price*qty_in)-sum(price*qty_out) qty_balance')
)
->leftJoin('transaction as t', 't.unit_id', '=', 'u.id')
->where('u.id', '=' , $reports->id)
->whereMonth('date_transaction','<', $request->month)
->whereYear('date_transaction', '=' , $request->year)
->get();
$reports->qty_balance = $balance;
}
return view ('report/p_report', compact('report','balance','reports'));
}
Can I insert a query into another query to be single table? I have tried, but the result like this, I want qty_balance display a value not inside Collection like this:
Collection {#247 ▼
#items: array:3 [▼
0 => {#245 ▼
+"id": 3
+"name_unit": "Folio"
+"price": 40000
+"vol_in": "81"
+"qty_ins": "3240000"
+"vol_out": "16"
+"qty_outs": "640000"
+"vol_end": "65"
+"qty_end": "2600000"
+"qty_balance": Collection {#262 ▶}
}
1 => {#246 ▼
+"id": 5
+"name_unit": "Pulpen"
+"price": 50000
+"vol_in": "100"
+"qty_ins": "5000000"
+"vol_out": "23"
+"qty_outs": "1150000"
+"vol_end": "77"
+"qty_end": "3850000"
+"qty_balance": Collection {#267 ▶}
}
]
}
