<?php

namespace App\Traits;
use DB;

trait ReceiptReportAble
{
    public static function cityReceiptData($from_date, $date_to, $province_id): array
    {
        return DB::select(
            'SELECT 
            city_fa,
            city_id,
            COUNT(id) AS tedade, 
            COUNT(CASE WHEN report_base = 0 THEN 1 END) AS police_rah,
            COUNT(CASE WHEN report_base = 1 THEN 1 END) AS gozaresh_gasht, 
            SUM(`sum`) AS kol_sabt_shode,
            SUM(CASE WHEN STATUS > 3 THEN `sum` END) AS vasel_shode,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_insurance_amount` END) AS vasel_shode_bimeh,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_daghi_amount` END) AS vasel_shode_daghi,
            SUM(CASE WHEN STATUS > 3 THEN `final_amount` END) AS vasel_shode_final

            FROM `accidents` 

            where created_at BETWEEN "'.$from_date.'" AND "'.$date_to.'" AND province_id ='.$province_id.'
            GROUP BY city_id
            '
        );

    }

    public static function provinceReceiptData($from_date, $date_to) : array {

        return DB::select(
            'SELECT 
            "کشوری" as province_fa,
            -1 as province_id,
            COUNT(id) AS tedade, 
            COUNT(CASE WHEN report_base = 0 THEN 1 END) AS police_rah,
            COUNT(CASE WHEN report_base = 1 THEN 1 END) AS gozaresh_gasht, 
            SUM(`sum`) AS kol_sabt_shode,
            SUM(CASE WHEN STATUS > 3 THEN `sum` END) AS vasel_shode,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_insurance_amount` END) AS vasel_shode_bimeh,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_daghi_amount` END) AS vasel_shode_daghi,
            SUM(CASE WHEN STATUS > 3 THEN `final_amount` END) AS vasel_shode_final

            FROM `accidents` 

            where created_at BETWEEN "'.$from_date.'" AND "'.$date_to.'"

            union

            SELECT 
            province_fa,
            province_id,
            COUNT(id) AS tedade, 
            COUNT(CASE WHEN report_base = 0 THEN 1 END) AS police_rah,
            COUNT(CASE WHEN report_base = 1 THEN 1 END) AS gozaresh_gasht, 
            SUM(`sum`) AS kol_sabt_shode,
            SUM(CASE WHEN STATUS > 3 THEN `sum` END) AS vasel_shode,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_insurance_amount` END) AS vasel_shode_bimeh,
            SUM(CASE WHEN STATUS > 3 THEN `deposit_daghi_amount` END) AS vasel_shode_daghi,
            SUM(CASE WHEN STATUS > 3 THEN `final_amount` END) AS vasel_shode_final

            FROM `accidents` 

            where created_at BETWEEN "'.$from_date.'" AND "'.$date_to.'"
            GROUP BY province_id
            '
        );

    }
}