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 function unpaidStatementsCount()
{
return PayeePayment::unpaidWherePayeeCode($this->code)->count();
}
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 function testEmptyLinesInFile()
{
$this->resetEvents();
$fileName = "with_empty_lines.csv";
$path = $this->copyFileToTemp($fileName);
$s = $this->service();
$companyId = rand(1000, 1000000);
$fileDetails = ["year" => 2015, "month" => 7];
$rpf = $s->process($path, $companyId, $fileDetails);
$this->assertTrue($rpf->status == RoyaltyPaymentFile::STATUS_PAYMENTS_PROCESSED);
$rpf = $s->processRoyaltyStreams($rpf);
$this->assertTrue($rpf->status == RoyaltyPaymentFile::STATUS_PROCESSED);
$payments = RoyaltyPayment::all();
$this->assertTrue(count($payments) == 4);
$this->assertTrue($payments[0]->payee_code == 1128);
$this->assertTrue($payments[1]->payee_code == 1111);
$this->assertTrue($payments[2]->payee_code == 1545);
$this->assertTrue($payments[3]->payee_code == 1332);
$clients = Client::all();
$this->assertTrue(count($clients) == 4);
$payeePayments = PayeePayment::all();
$this->assertTrue(count($payments) == 4);
$deals = Deal::all();
$this->assertTrue(count($deals) == 4);
$streams = RoyaltyStream::all();
$this->assertTrue(count($streams) == 4);
$this->assertTrue($streams[0]->song_number == 33959);
$this->assertTrue($streams[1]->song_number == 14402);
$this->assertTrue($streams[2]->song_number == 12043);
$this->assertTrue($streams[3]->song_number == 51311);
$streamFiles = RoyaltyStreamFile::all();
$this->assertTrue(count($streamFiles) == 4);
$this->resetEvents();
}
public function testMarkAsPaidWithMultipleAdvances2CompleteUnfixedAmount()
{
$this->resetEvents();
$user = User::create(self::userArray());
$advance1 = Advance::create(self::advanceArray());
$advance2 = Advance::create(self::advanceArray());
$advance3 = Advance::create(self::advanceArray());
$paymentDate = date("Y-m-d", strtotime("+ 5 days"));
$payeePayment = PayeePayment::create(self::payeePaymentArray());
$royaltyPayment = self::royaltyPaymentsArray($payeePayment);
$royaltyPayment["amount_received"] = 800;
RoyaltyPayment::create($royaltyPayment);
RoyaltyPayment::create($royaltyPayment);
RoyaltyPayment::create($royaltyPayment);
$payeePayment->markAsPaid($paymentDate);
$this->assertTrue(count(AdvancePayment::all()) == 5);
$this->assertTrue(count(AdvancePayment::where("advance_id", "=", $advance1->id)->get()) == 2);
$this->assertTrue(count(AdvancePayment::where("advance_id", "=", $advance2->id)->get()) == 2);
$this->assertTrue(count(AdvancePayment::where("advance_id", "=", $advance3->id)->get()) == 1);
$advanceFound1 = Advance::find($advance1->id);
$this->assertTrue($advanceFound1->status == "complete");
$advanceFound2 = Advance::find($advance2->id);
$this->assertTrue($advanceFound2->status == "complete");
$advanceFound3 = Advance::find($advance3->id);
$this->assertTrue($advanceFound3->status == "incomplete");
$this->assertTrue($advanceFound3->amountLeftToPay() == 600);
$this->assertTrue(AdvancePayment::find(1)->amount == 800);
$this->assertTrue(AdvancePayment::find(2)->amount == 200);
$this->assertTrue(AdvancePayment::find(3)->amount == 600);
$this->assertTrue(AdvancePayment::find(4)->amount == 400);
$this->assertTrue(AdvancePayment::find(5)->amount == 400);
$payeePayment1 = PayeePayment::find($payeePayment->id);
$this->assertTrue($payeePayment1->status == "paid");
$this->resetEvents();
}
public function createModels()
{
$models = [];
$models["c1"] = Company::create(["name" => "test 1"]);
$models["c2"] = Company::create(["name" => "test 2"]);
$models["a"] = User::create(["name" => "admin", "email" => "*****@*****.**", "company_id" => $models["c1"]->id, "type" => User::TYPE_ADMIN]);
$models["p1"] = User::create(["name" => "user1", "email" => "*****@*****.**", "company_id" => $models["c1"]->id, "type" => User::TYPE_PAYEE, "code" => "1111"]);
$models["p2"] = User::create(["name" => "user2", "email" => "*****@*****.**", "company_id" => $models["c1"]->id, "type" => User::TYPE_PAYEE, "code" => "2222"]);
$models["p3"] = User::create(["name" => "user3", "email" => "*****@*****.**", "company_id" => $models["c1"]->id, "type" => User::TYPE_PAYEE, "code" => "3333"]);
$models["p4"] = User::create(["name" => "user4", "email" => "*****@*****.**", "company_id" => $models["c2"]->id, "type" => User::TYPE_PAYEE, "code" => "4444"]);
$models["p5"] = User::create(["name" => "user5", "email" => "*****@*****.**", "company_id" => $models["c2"]->id, "type" => User::TYPE_PAYEE, "code" => "5555"]);
$models["p6"] = User::create(["name" => "user6", "email" => "*****@*****.**", "company_id" => $models["c2"]->id, "type" => User::TYPE_PAYEE, "code" => "6666"]);
$models["p7"] = User::create(["name" => "user7", "email" => "*****@*****.**", "company_id" => $models["c2"]->id, "type" => User::TYPE_PAYEE, "code" => "7777"]);
$models["pp1"] = PayeePayment::create(["amount" => 100, "status" => "unpaid", "payee_code" => $models["p1"]->code, "company_id" => $models["p1"]->company_id]);
$models["pp2"] = PayeePayment::create(["amount" => 100, "status" => "paid", "payee_code" => $models["p1"]->code, "company_id" => $models["p1"]->company_id]);
$models["pp3"] = PayeePayment::create(["amount" => 100, "status" => "unpaid", "payee_code" => $models["p1"]->code, "company_id" => $models["p1"]->company_id]);
$models["pp4"] = PayeePayment::create(["amount" => 100, "status" => "paid", "payee_code" => $models["p2"]->code, "company_id" => $models["p2"]->company_id]);
$models["pp5"] = PayeePayment::create(["amount" => 100, "status" => "paid", "payee_code" => $models["p2"]->code, "company_id" => $models["p2"]->company_id]);
$models["pp6"] = PayeePayment::create(["amount" => 100, "status" => "unpaid", "payee_code" => $models["p2"]->code, "company_id" => $models["p2"]->company_id]);
$models["pp7"] = PayeePayment::create(["amount" => 100, "status" => "unpaid", "payee_code" => $models["p2"]->code, "company_id" => $models["p2"]->company_id]);
return $models;
}
public static function apiPayeesCounts()
{
return ["new_payees" => PayeeCompany::payeesWithoutEmail()->count(), "all_payees" => PayeeCompany::payeesWithEmail()->count(), "unpaid_statements" => PayeePayment::unpaid()->count(), "paid_statements" => PayeePayment::paid()->count()];
}
public static function updateOrCreatePayeePayment($payeePayment)
{
$payeePayment = self::formatFromExcel($payeePayment);
if (isset($payeePayment['payee_payment.id']) && is_int(intval($payeePayment['payee_payment.id']))) {
$payment = PayeePayment::find($payeePayment['payee_payment.id']);
$payment->update($payeePayment);
} else {
$payeePayment['company_id'] = Auth::user()["company_id"];
$payment = PayeePayment::create($payeePayment);
}
if (isset($payeePayment['status']) && $payeePayment['status'] == 'paid') {
$payment->distributePayeePaymentToAdvancePayments();
}
}
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);
}
