public function unpaidPayeePayments()
{
$query = PayeePayment::unpaid();
$query->with(["payee", "client"]);
$query->join(User::table() . " as user", 'user.code', '=', PayeePayment::table() . '.payee_code');
$filters = Request::get('_filter');
if (count($filters)) {
foreach ($filters as $key => $filter) {
list($field, $value) = explode(':', $filter);
if (strpos($filter, 'search') !== false) {
$query->where(function ($query) use($value) {
$query->orWhere("user.name", "like", '%' . $value . '%');
});
} else {
$this->attachWhere($query, $value, $field);
}
}
}
$this->attachSort(new PayeePayment(), $query);
$count = $this->getQueryCount($query);
$offset = $this->attachOffset($query);
$limit = $this->attachLimit($query);
$items = $query->get([PayeePayment::table() . '.*']);
return Response::json(array('model' => "PayeePayment", 'items' => $items->toApiArray(), 'offset' => $offset, 'limit' => $limit, 'count' => $count), 200, [], JSON_NUMERIC_CHECK);
}
public static function apiQuery()
{
$query = self::query();
if (Auth::user()->isPublisherAdmin() || Auth::user()->isPublisher()) {
$query->where(PayeePayment::table() . '.company_id', '=', Auth::user()["company_id"]);
}
if (Auth::user()->isPayee()) {
$query->where(PayeePayment::table() . ".payee_code", '=', Auth::user()["code"]);
}
return $query;
}
public function savePayeePayments($rpf)
{
// $paymentSums = RoyaltyPayment::sumAmountReceivedGroupedByPayeeCode($rpf->id)->get();
// foreach ($paymentSums as $payeePaymentSum) {
// $payeePayment = PayeePayment::create(["amount" => $payeePaymentSum->amount,
// "status" => "unpaid",
// "payee_code" => $payeePaymentSum->payee_code,
// "company_id" => $rpf->company_id,
// "client_code" => $payeePaymentSum->client_code,
// "year" => $rpf->year,
// "quarter" => $rpf->quarter,
// "month" => $rpf->month,
// "half_year" => $rpf->half_year]);
// RoyaltyPayment::unattachedPaymentsForFileAndPayee($rpf->id, $payeePaymentSum->payee_code)
// ->update(["payee_payment_id" => $payeePayment->id]);
// }
$year = empty($rpf->year) ? 'NULL' : $rpf->year;
$quarter = empty($rpf->quarter) ? 'NULL' : $rpf->quarter;
$month = empty($rpf->month) ? 'NULL' : $rpf->month;
$halfYear = empty($rpf->half_year) ? 'NULL' : $rpf->half_year;
DB::connection()->getpdo()->exec('INSERT INTO ' . PayeePayment::table() . '(`amount`, `status`, `payee_code`, `company_id`, `client_code`, `year`, `quarter`, `month`, `half_year`)
SELECT sum(`amount_received`) AS amount, \'unpaid\', `payee_code`, ' . $rpf->company_id . ', `client_code`, ' . $year . ', ' . $quarter . ', ' . $month . ', ' . $halfYear . '
FROM ' . RoyaltyPayment::table() . '
WHERE `royalty_payment_file_id` = ' . $rpf->id . ' AND `payee_payment_id` IS NULL
GROUP BY `payee_code` ORDER BY `payee_code`');
DB::connection()->getpdo()->exec('UPDATE ' . RoyaltyPayment::table() . ' as rp ' . 'LEFT JOIN ' . PayeePayment::table() . ' as pp ' . 'ON rp.payee_code = pp.payee_code ' . 'SET rp.payee_payment_id = pp.id ' . 'WHERE rp.royalty_payment_file_id = ' . $rpf->id);
}
public static function sumAmountReceivedUnpaidPerPayee($payeeCode)
{
$unattachedAmount = self::unattachedPaymentsPerPayee($payeeCode)->select(DB::raw('sum(amount_received) AS amount'))->first();
$attachedUnpaid = self::attachedPaymentsPerPayee($payeeCode)->join(PayeePayment::table() . " as pp", 'pp.id', '=', 'payee_payment_id')->where('pp.status', '=', 'unpaid')->select(DB::raw('sum(amount_received) AS amount'))->first();
return ($unattachedAmount != null ? $unattachedAmount->amount : 0) + ($attachedUnpaid != null ? $attachedUnpaid->amount : 0);
}
