<commit_before><?php

namespace App\Http\Controllers\Api\v1\Modules;

use App;
use App\Http\Controllers\Controller;
use App\Models\Einheiten;
use App\Models\Objekte;
use App\Models\RentDefinition;
use Carbon\Carbon;
use Exception;
use Illuminate\Http\Request;
use XLSXWriter;

class ReportController extends Controller
{
    /**
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function revenue(Request $request, Objekte $object)
    {
        $file = tempnam(sys_get_temp_dir(), 'revenue_report_');
        $start = Carbon::today()->firstOfYear();
        $end = $start->copy()->lastOfYear();

        if ($request->has('date')) {
            $start = Carbon::parse($request->input('date'));
            $end = Carbon::parse($request->input('date'));
        }
        if ($request->has('period')) {
            $period = $request->input('period');
            switch ($period) {
                case 'year':
                    $start->firstOfYear();
                    $end->lastOfYear();
                    break;
                case 'quarter':
                    $start->firstOfQuarter();
                    $end->lastOfQuarter();
                    break;
                case 'month':
                    $start->firstOfMonth();
                    $end->lastOfMonth();
                    break;
            }
        }

        $header = [
            'Apartment No.' => 'string',
            'Space' => '0.00',
            'Occupant Name' => 'string',
            'Contract Start' => 'date',
            'Contract End' => 'date',
            'Days Occupied' => 'integer',
            'Basic Rent' => 'euro',
            'Operating Costs' => 'euro',
            'Heating Costs' => 'euro',
            'Actual Payments' => 'euro'
        ];

        $sheet = 'Revenue';

        $writer = new XLSXWriter();

        $writer->writeSheetHeader($sheet, $header);

        $units = Einheiten::whereHas('haus.objekt', function ($query) use ($object) {
            $query->where('OBJEKT_ID', $object->OBJEKT_ID);
        })->with(['haus.objekt', 'mietvertraege.mieter'])->defaultOrder()->get();
        $rows = 1;

        foreach ($units as $unit) {
            $rentalContracts = $unit->mietvertraege()
                ->where(function ($query) use ($start, $end) {
                    $query->where(function ($query) use ($start, $end) {
                        $query->active('>=', $start)
                            ->active('<=', $end);
                    })->orWhere(function ($query) use ($start, $end) {
                        $query->whereHas('postings', function ($query) use ($start, $end) {
                            $query->where('KONTENRAHMEN_KONTO', 80001)
                                ->where('KOSTENTRAEGER_TYP', 'Mietvertrag')
                                ->whereDate('DATUM', '>=', $start)
                                ->whereDate('DATUM', '<=', $end);
                        });
                    });
                })->defaultOrder()->get();
            if ($rentalContracts->isEmpty()) {
                $writer->writeSheetRow($sheet, [$unit->EINHEIT_KURZNAME, $unit->EINHEIT_QM, '', '', '', 0, 0, 0, 0, 0]);
                $rows++;
            } else {
                foreach ($rentalContracts as $index => $rentalContract) {
                    $basicRent = RentDefinition::sumDefinitions($rentalContract->basicRentDefinitions($start, $end), $start, $end);
                    $basicRentDeduction = RentDefinition::sumDefinitions($rentalContract->basicRentDeductionDefinitions($start, $end), $start, $end);
                    $heatingCostAdvance = RentDefinition::sumDefinitions($rentalContract->heatingExpenseAdvanceDefinitions($start, $end), $start, $end);
                    $heatingCostSettlement = RentDefinition::sumDefinitions($rentalContract->heatingExpenseSettlementDefinitions($start, $end), $start, $end);
                    $operatingCostAdvance = RentDefinition::sumDefinitions($rentalContract->operatingCostAdvanceDefinitions($start, $end), $start, $end);
                    $operatingCostSettlement = RentDefinition::sumDefinitions($rentalContract->operatingCostSettlementDefinitions($start, $end), $start, $end);
                    $postings = $rentalContract->postings($start, $end)->where('KONTENRAHMEN_KONTO', 80001)->sum('BETRAG');
                    $occupantName = $rentalContract->mieter->implode('full_name', '; ');
                    $to = $rentalContract->MIETVERTRAG_BIS === '0000-00-00' ? '' : $rentalContract->MIETVERTRAG_BIS;
                    $row = ['', '', $occupantName, $rentalContract->MIETVERTRAG_VON, $to, $rentalContract->overlaps($start, $end), $basicRent + $basicRentDeduction, $operatingCostAdvance + $operatingCostSettlement, $heatingCostAdvance + $heatingCostSettlement, $postings];
                    if ($index === 0) {
                        $row[0] = $unit->EINHEIT_KURZNAME;
                        $row[1] = $unit->EINHEIT_QM;
                    }
                    $writer->writeSheetRow($sheet, $row);
                    $rows++;
                }
            }
        }
        $row = ['Total', "=SUM(B2:B$rows)", '', '', '', '', "=SUM(G2:G$rows)", "=SUM(H2:H$rows)", "=SUM(I2:I$rows)", "=SUM(J2:J$rows)"];
        $writer->writeSheetRow($sheet, $row);
        $writer->writeSheetRow($sheet, ['', '', '', '', '', '', '', '', '', '']);
        $writer->writeSheetRow($sheet, ['', '', 'Query Period:', $start->toDateString(), $end->toDateString(), '', '', '', '', '']);

        $writer->writeToFile($file);<commit_msg>change: split revenue report in advances and settlements<commit_after><?php

namespace App\Http\Controllers\Api\v1\Modules;

use App;
use App\Http\Controllers\Controller;
use App\Models\Einheiten;
use App\Models\Objekte;
use App\Models\RentDefinition;
use Carbon\Carbon;
use Exception;
use Illuminate\Http\Request;
use XLSXWriter;

class ReportController extends Controller
{
    /**
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function revenue(Request $request, Objekte $object)
    {
        $file = tempnam(sys_get_temp_dir(), 'revenue_report_');
        $start = Carbon::today()->firstOfYear();
        $end = $start->copy()->lastOfYear();

        if ($request->has('date')) {
            $start = Carbon::parse($request->input('date'));
            $end = Carbon::parse($request->input('date'));
        }
        if ($request->has('period')) {
            $period = $request->input('period');
            switch ($period) {
                case 'year':
                    $start->firstOfYear();
                    $end->lastOfYear();
                    break;
                case 'quarter':
                    $start->firstOfQuarter();
                    $end->lastOfQuarter();
                    break;
                case 'month':
                    $start->firstOfMonth();
                    $end->lastOfMonth();
                    break;
            }
        }

        $header = [
            'Apartment No.' => 'string',
            'Space' => '0.00',
            'Occupant Name' => 'string',
            'Contract Start' => 'date',
            'Contract End' => 'date',
            'Days Occupied' => 'integer',
            'Basic Rent' => 'euro',
            'Basic Rent Deduction' => 'euro',
            'Operating Cost Advances' => 'euro',
            'Operating Cost Settlements' => 'euro',
            'Heating Cost Advances' => 'euro',
            'Heating Cost Settlements' => 'euro',
            'Actual Payments' => 'euro'
        ];

        $sheet = 'Revenue';

        $writer = new XLSXWriter();

        $writer->writeSheetHeader($sheet, $header);

        $units = Einheiten::whereHas('haus.objekt', function ($query) use ($object) {
            $query->where('OBJEKT_ID', $object->OBJEKT_ID);
        })->with(['haus.objekt', 'mietvertraege.mieter'])->defaultOrder()->get();
        $rows = 1;

        foreach ($units as $unit) {
            $rentalContracts = $unit->mietvertraege()
                ->where(function ($query) use ($start, $end) {
                    $query->where(function ($query) use ($start, $end) {
                        $query->active('>=', $start)
                            ->active('<=', $end);
                    })->orWhere(function ($query) use ($start, $end) {
                        $query->whereHas('postings', function ($query) use ($start, $end) {
                            $query->where('KONTENRAHMEN_KONTO', 80001)
                                ->where('KOSTENTRAEGER_TYP', 'Mietvertrag')
                                ->whereDate('DATUM', '>=', $start)
                                ->whereDate('DATUM', '<=', $end);
                        });
                    });
                })->defaultOrder()->get();
            if ($rentalContracts->isEmpty()) {
                $writer->writeSheetRow($sheet, [$unit->EINHEIT_KURZNAME, $unit->EINHEIT_QM, '', '', '', 0, 0, 0, 0, 0, 0, 0, 0]);
                $rows++;
            } else {
                foreach ($rentalContracts as $index => $rentalContract) {
                    $basicRent = RentDefinition::sumDefinitions($rentalContract->basicRentDefinitions($start, $end), $start, $end);
                    $basicRentDeduction = RentDefinition::sumDefinitions($rentalContract->basicRentDeductionDefinitions($start, $end), $start, $end);
                    $heatingCostAdvance = RentDefinition::sumDefinitions($rentalContract->heatingExpenseAdvanceDefinitions($start, $end), $start, $end);
                    $heatingCostSettlement = -1 * RentDefinition::sumDefinitions($rentalContract->heatingExpenseSettlementDefinitions($start, $end), $start, $end);
                    $operatingCostAdvance = RentDefinition::sumDefinitions($rentalContract->operatingCostAdvanceDefinitions($start, $end), $start, $end);
                    $operatingCostSettlement = -1 * RentDefinition::sumDefinitions($rentalContract->operatingCostSettlementDefinitions($start, $end), $start, $end);
                    $postings = $rentalContract->postings($start, $end)->where('KONTENRAHMEN_KONTO', 80001)->sum('BETRAG');
                    $occupantName = $rentalContract->mieter->implode('full_name', '; ');
                    $to = $rentalContract->MIETVERTRAG_BIS === '0000-00-00' ? '' : $rentalContract->MIETVERTRAG_BIS;
                    $row = ['', '', $occupantName, $rentalContract->MIETVERTRAG_VON, $to, $rentalContract->overlaps($start, $end), $basicRent, $basicRentDeduction, $operatingCostAdvance, $operatingCostSettlement, $heatingCostAdvance, $heatingCostSettlement, $postings];
                    if ($index === 0) {
                        $row[0] = $unit->EINHEIT_KURZNAME;
                        $row[1] = $unit->EINHEIT_QM;
                    }
                    $writer->writeSheetRow($sheet, $row);
                    $rows++;
                }
            }
        }
        $row = ['Total', "=SUM(B2:B$rows)", '', '', '', '', "=SUM(G2:G$rows)", "=SUM(H2:H$rows)", "=SUM(I2:I$rows)", "=SUM(J2:J$rows)", "=SUM(K2:K$rows)", "=SUM(L2:L$rows)", "=SUM(M2:M$rows)"];
        $writer->writeSheetRow($sheet, $row);
        $writer->writeSheetRow($sheet, ['', '', '', '', '', '', '', '', '', '', '', '', '']);
        $writer->writeSheetRow($sheet, ['', '', 'Query Period:', $start->toDateString(), $end->toDateString(), '', '', '', '', '', '', '', '']);

        $writer->writeToFile($file);
{'fraction_non_alphanumeric': 0.17456858985532508, 'fraction_numerical': 0.006972285166463308, 'mean_word_length': 2.0567394778902504, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 154, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6740114', 'n_tokens_mistral': 3506, 'n_tokens_neox': 3269, 'n_words': 538}