File: /var/www/ipsremont-demo/app/Services/Reconciliation/ReconciliationService.php
<?php
namespace App\Services\Reconciliation;
use App\Helpers\CurrencyHelper;
use App\Helpers\UserHelper;
use App\Models\Currency;
use App\Models\Document;
use App\Models\Repair\Repair;
use App\Models\Service\Service;
use App\Repository\Document\DocumentRepository;
use App\Services\ExcelService;
use NumberFormatter;
class ReconciliationService
{
public static function createReport(string $from, string $to, Service $service): string
{
$user = UserHelper::getUser();
$currency = $user->branches[0]->region->currency;
$startDate = date('Y-m-d', strtotime($from));
$endDate = date('Y-m-d', strtotime($to));
$doc = new ExcelService();
foreach ($doc->rows as $row) {
$doc->setColumnWidth($row, 12);
}
$rows = [];
/** @var Document[] $documents */
$documents = DocumentRepository::getDocumentsForReport($service->id, $startDate, $endDate)->get();
foreach ($documents as $document) {
$debit = 0;
$credit = 0;
// TODO Почему то не работает сравнение с константами пришлось сырые строки добавить
switch ($document->method) {
case Document::METHOD_SALES_RECEIPT:
case 'SalesReceipt':
$name = 'Расходная накладная';
$debit += $document->sum;
break;
case Document::METHOD_GET_IN_PAYMENT_ORDER:
case 'GetInPaymentOrder':
$name = 'Платежное поручение входящее';
$credit += $document->sum;
break;
case Document::METHOD_GET_OUT_PAYMENT_ORDER:
case 'GetOutPaymentOrder':
$name = 'Платежное поручение исходящее';
$debit += $document->sum;
break;
default:
$name = $document->method;
break;
}
$rows[] = [
'date' => date('d.m.Y', strtotime($document->date)),
'name' => $name . ' ' . $document->number,
'debit' => CurrencyHelper::getPriceConverted($debit, null, false),
'credit' => CurrencyHelper::getPriceConverted($credit, null, false),
];
}
/** @var Repair[] $repairs */
$repairs = Repair::query()->whereIn('status', ['completed', 'report'])->where('service_id', $service->id)->whereBetween('closed_at', [$startDate, $endDate])->get();
foreach ($repairs as $repair) {
$rows[] = [
'date' => $repair->getClosedDate(),
'name' => 'Наряд ' . $repair->getNumber(),
'debit' => 0,
'credit' => CurrencyHelper::getPriceConverted($repair->getCustomOrDefaultPrice(), null, false),
];
}
usort($rows, function ($a, $b) {
return $a['date'] <=> $b['date'];
});
$doc->setColumnWidth('A', 1);
$doc->setColumnWidth('I', 0.1);
$doc->setColumnWidth('H', 2);
$doc->setColumnWidth('L', 2);
$doc->setCellValue('B2', 'Акт сверки', 'Tahoma', 14, true, $doc->center, [2, 20], [], 'B2:O2');
$text = 'взаимных расчетов за период: ' . $from . '-' . $to . PHP_EOL . 'между __________________________' . PHP_EOL . 'и ' . $service->name;
$doc->setCellValue('B3', $text, 'Arial', 10, false, $doc->center, [3, 50], [], 'B3:O3');
$doc->setWrapText('B3');
$text = 'Мы, нижеподписавшиеся, ______________________________________________________________, с одной стороны, и ' . $service->name . ',' . '___________________, с другой стороны, составили настоящий акт сверки в том, что состояние взаимных расчетов по данным учета следующее:';
$doc->setCellValue('B5', $text, 'Arial', 10, false, '', [5, 50], [], 'B5:O5');
$doc->setWrapText('B5');
$doc->setCellValue('B7', 'По данным __________________________, ' . $currency->short_name, 'Arial', 10, false, '', [], [], 'B7:H7', true);
for ($i = 2; $i < 8; $i++) {
$doc->setCellValue($doc->rows[$i] . '7', '', '', '', '', '', [], [], '', true);
}
$doc->setCellValue('J7', 'По данным ' . $service->name . ', ' . $currency->short_name, 'Arial', 10, false, '', [], [], 'J7:O7', true);
for ($i = 10; $i < 15; $i++) {
$doc->setCellValue($doc->rows[$i] . '7', '', '', '', '', '', [], [], '', true);
}
$doc->setCellValue('B8', 'Дата', 'Arial', 9, true, $doc->center, [], [], '', true);
$doc->setCellValue('C8', 'Документ', 'Arial', 9, true, $doc->center, [], [], 'C8:D8', true);
$doc->setCellValue('D8', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('E8', 'Дебет', 'Arial', 9, true, $doc->center, [], [], 'E8:F8', true);
$doc->setCellValue('F8', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('G8', 'Кредит', 'Arial', 9, true, $doc->center, [], [], 'G8:H8', true);
$doc->setCellValue('H8', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('J8', 'Дата', 'Arial', 9, true, $doc->center, [], [], '', true);
$doc->setCellValue('K8', 'Документ', 'Arial', 9, true, $doc->center, [], [], 'K8:L8', true);
$doc->setCellValue('L8', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('M8', 'Дебет', 'Arial', 9, true, $doc->center, [], [], 'M8:N8', true);
$doc->setCellValue('N8', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('O8', 'Кредит', 'Arial', 9, true, $doc->center, [], [], '', true);
$balance = $service->getBalance();
$doc->setCellValue('B9', 'Сальдо начальное', 'Arial', 8, true, '', [], [], 'B9:D9', true);
$doc->setCellValue('D9', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('E9', $balance, '', '', '', '', [], [], 'E9:F9', true);
$doc->setCellValue('F9', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('G9', '', '', '', '', '', [], [], 'G9:H9', true);
$doc->setCellValue('H9', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('J9', 'Сальдо начальное', 'Arial', 8, true, '', [], [], 'J9:L9', true);
$doc->setCellValue('K9', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('L9', '', '', '', '', '', [], [], '', true);
$doc->setCellValue('M9', '', '', '', '', '', [], [], 'M9:N9', true);
$doc->setCellValue('O9', '', '', '', '', '', [], [], '', true);
$totalDebit = 0;
$totalCredit = 0;
// Строки
$i = 9;
foreach ($rows as $row) {
$totalDebit += $row['debit'];
$totalCredit += $row['credit'];
$i++;
$doc->setCellValue('B' . $i, $row['date'], '', '', '', '', [], [], '', true);
$doc->setCellValue('C' . $i, $row['name'], '', '', '', '', [], [], 'C' . $i . ':D' . $i, true);
$doc->setCellValue('D' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('E' . $i, empty($row['debit']) ? '' : $row['debit'], '', '', '', '', [], [], 'E' . $i . ':F' . $i, true);
$doc->setCellValue('F' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('G' . $i, empty($row['credit']) ? '' : $row['credit'], '', '', '', '', [], [], 'G' . $i . ':H' . $i, true);
$doc->setCellValue('H' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('J' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('K' . $i, '', '', '', '', '', [], [], 'K' . $i . ':L' . $i, true);
$doc->setCellValue('L' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('M' . $i, '', '', '', '', '', [], [], 'M' . $i . ':N' . $i, true);
$doc->setCellValue('N' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('O' . $i, '', '', '', '', '', [], [], '', true);
}
$finalBalance = $balance + $totalDebit - $totalCredit;
// Обороты за период
$i++;
$doc->setCellValue('B' . $i, 'Обороты за период', 'Arial', 8, true, '', [], [], 'B' . $i . ':D' . $i, true);
$doc->setCellValue('C' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('D' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('E' . $i, $totalDebit, 'Arial', 8, true, $doc->right, [], [], 'E' . $i . ':F' . $i, true);
$doc->setCellValue('F' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('G' . $i, $totalCredit, 'Arial', 8, false, $doc->right, [], [], 'G' . $i . ':H' . $i, true);
$doc->setCellValue('H' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('J' . $i, 'Обороты за период', 'Arial', 8, true, '', [], [], 'J' . $i . ':L' . $i, true);
$doc->setCellValue('K' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('L' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('M' . $i, '', '', '', '', '', [], [], 'M' . $i . ':N' . $i, true);
$doc->setCellValue('N' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('O' . $i, '', '', '', '', '', [], [], '', true);
// Сальдо конечное
$i++;
$doc->setCellValue('B' . $i, 'Сальдо конечное', 'Arial', 8, true, '', [], [], 'B' . $i . ':D' . $i, true);
$doc->setCellValue('C' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('D' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('E' . $i, '', '', '', '', '', [], [], 'E' . $i . ':F' . $i, true);
$doc->setCellValue('F' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('G' . $i, $finalBalance, 'Arial', 8, false, $doc->right, [], [], 'G' . $i . ':H' . $i, true);
$doc->setCellValue('H' . $i, '', '', '', '', '', [], [], '', true);
$doc->setColor('G' . $i);
$doc->setCellValue('J' . $i, 'Сальдо конечное', 'Arial', 8, true, '', [], [], 'J' . $i . ':L' . $i, true);
$doc->setCellValue('K' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('L' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('M' . $i, '', '', '', '', '', [], [], 'M' . $i . ':N' . $i, true);
$doc->setCellValue('N' . $i, '', '', '', '', '', [], [], '', true);
$doc->setCellValue('O' . $i, '', '', '', '', '', [], [], '', true);
$i += 2;
$doc->setCellValue('B' . $i, 'По данным __________________________, ' . $currency->short_name, 'Arial', 8, false);
$doc->setCellValue('J' . $i, 'По данным ' . $service->name . ', ' . $currency->short_name, 'Arial', 8, false);
$i++;
$text = 'на ' . date('d.m.Y') . ' задолженность ' . ($finalBalance < 0 ? '__________________________' : $service->name) . ' перед ' . ($finalBalance >= 0 ? '__________________________' : $service->name) . ' составляет ' . $finalBalance . ' ' . $currency->short_name . ' (' . self::str_price($finalBalance, $currency) . ')';
$doc->setCellValue('B' . $i, $text, 'Arial', 8, true, '', [$i, 50], [], 'B' . $i . ':G' . $i);
$doc->setWrapText('B' . $i);
$i += 2;
$doc->setCellValue('B' . $i, 'От __________________________', 'Arial', 8, false);
$doc->setCellValue('J' . $i, 'От ' . $service->name, 'Arial', 8, false);
$i += 2;
$doc->setCellValue('B' . $i, 'Генеральный директор', 'Arial', 8, false);
$doc->setCellValue('J' . $i, '', 'Arial', 8, false, '', [], [], 'J' . $i . ':K' . $i, 'bottom');
$doc->setCellValue('K' . $i, '', 'Arial', 8, false, '', [], [], '', 'bottom');
$i += 2;
$doc->setCellValue('B' . $i, '', 'Arial', 8, false, '', [], [], 'B' . $i . ':C' . $i, 'bottom');
$doc->setCellValue('C' . $i, '', 'Arial', 8, false, '', [], [], '', 'bottom');
$doc->setCellValue('D' . $i, '( )', 'Arial', 8, false);
$doc->setCellValue('J' . $i, '', 'Arial', 8, false, '', [], [], 'J' . $i . ':K' . $i, 'bottom');
$doc->setCellValue('K' . $i, '', 'Arial', 8, false, '', [], [], '', 'bottom');
$doc->setCellValue('L' . $i, '( )', 'Arial', 8, false);
$i += 2;
$doc->setCellValue('B' . $i, 'М.П.', 'Arial', 8, false);
$doc->setCellValue('J' . $i, 'М.П.', 'Arial', 8, false);
if (!is_dir(public_path('storage/reconciliation'))) {
mkdir(public_path('storage/reconciliation'));
}
$file_path = public_path('storage/reconciliation/reconciliation_act_' . $from . '_' . $to . '.xls');
$doc->generateDocument($file_path);
return $file_path;
}
/**
* @param $value
* @param Currency $currency
*
* @return string
*/
public static function str_price($value, $currency)
{
$value = explode('.', number_format($value, 2, '.', ''));
$f = new NumberFormatter('ru', NumberFormatter::SPELLOUT);
$str = $f->format($value[0]);
$str = mb_strtoupper(mb_substr($str, 0, 1)) . mb_substr($str, 1, mb_strlen($str));
$num = $value[0] % 100;
if ($num > 19) {
$num = $num % 10;
}
$names = [];
$name = 'рублей';
if ($currency->full_name) {
$names = explode(',', $currency->full_name);
} else {
$name = $currency->short_name;
}
$cents = 'копеек';
if (!empty($currency->subunit_full_name)) {
$cents = explode(',', $currency->subunit_full_name)[0];
}
switch ($num) {
case 1:
$rub = $names[0] ?? $name;
break;
case 2:
case 3:
case 4:
$rub = $names[1] ?? $name;
break;
default:
$rub = $names[2] ?? $name;
}
return $str . ' ' . $rub . ' ' . $value[1] . ' ' . $cents . '.';
}
}