SELECT
    `VehicleClaim`.`id` AS `Vehicle_claim_id`, `VehicleClaim`.`Vehicle_registration_number`,
    `Claim`.`id` AS `claim_id`, `Claim`.`created` AS `claim_created`, DATE_FORMAT(`Claim`.`created`, '%b') AS `claim_created_month`,
    `ClaimSubtype`.`name` AS `claim_subtype`,
    `Vendor`.`name` as `vendor_name`,

    IF(`VehicleClaim`.`id` IS NOT NULL,
        IF(`VehicleClaimUpdate`.`id` IS NOT NULL, `VehicleClaimUpdateStatus`.`status`, `ClaimStatus`.`status`),
        IF(`ClaimUpdate`.`id` IS NOT NULL, `ClaimUpdateStatus`.`status`, `ClaimStatus`.`status`)
    ) AS `status`,

    IF(`VehicleClaimUpdate`.`id` IS NOT NULL, `VehicleClaimUpdate`.`result`,
        IF(`ClaimUpdate`.`id` IS NOT NULL, `ClaimUpdate`.`result`, NULL)
    ) AS `result`,

    SUM(
        (IF((`VehicleClaimUpdate`.`conceded_labour_cost` is not null),
            `VehicleClaimUpdate`.`conceded_labour_cost`,
            IF((`ClaimUpdate`.`id` is not null),
                `ClaimUpdate`.`conceded_labour_cost`,
                IF((`Claim`.`repair_order_id` is not null),
                    IF((`RepairOrder`.`cost` is not null),
                        `RepairOrder`.`cost`,
                        SUM((`RepairOrder`.`removal_refit_hours` * `RepairOrderHourlyRate`.`rate`))
                    ),
                    IF((`VehicleClaim`.`id` is not null),SUM((`VehicleClaim`.`work_hours` * `VehicleClaimHourlyRate`.`rate`)),0)
                )
            )
        ))
        +
        (IF((`VehicleClaimUpdate`.`conceded_material_cost` is not null),
            `VehicleClaimUpdate`.`conceded_material_cost`,
            IF((`ClaimUpdate`.`id` is not null),
                `ClaimUpdate`.`conceded_material_cost`,
                IF((`ClaimMaterial`.`id` is not null),SUM((((`ClaimMaterial`.`unit_cost` * `ClaimMaterial`.`quantity`) * `ClaimMaterial`.`percentage_claimed`) / 100)),0)
            )
        ))
        +
        (IF(`VehicleClaimUpdate`.`conceded_freight_cost` IS NOT NULL,
            `VehicleClaimUpdate`.`conceded_freight_cost`,
            IF(`ClaimUpdate`.`id` IS NOT NULL,
                `ClaimUpdate`.`conceded_freight_cost`,
                IF(`ClaimFreight`.`id` IS NOT NULL, `ClaimFreight`.`cost`, 0)
            )
        ))
    ) AS `total_cost`,

    `User`.`id` AS `user_id`, `User`.`username`,
    `Client`.`id` AS `client_id`
FROM `Vehicle_claims` AS `VehicleClaim`
    RIGHT JOIN `claims`                     AS `Claim` ON (`Claim`.`id` = `VehicleClaim`.`claim_id`)
    LEFT JOIN `claim_materials`             AS `ClaimMaterial` ON (`ClaimMaterial`.`claim_id` = `Claim`.`id`)
    LEFT JOIN `claim_freight`               AS `ClaimFreight` ON (`ClaimFreight`.`claim_id` = `Claim`.`id`)
    LEFT JOIN `claim_subtypes`              AS `ClaimSubtype` ON (`ClaimSubtype`.`id` = `Claim`.`claim_subtype_id`)
    LEFT JOIN `repair_orders`               AS `RepairOrder` ON (`RepairOrder`.`id` = `Claim`.`repair_order_id`)
    LEFT JOIN `hourly_rates`                AS `RepairOrderHourlyRate` ON (`RepairOrderHourlyRate`.`id` = `RepairOrder`.`hourly_rate_id`)
    LEFT JOIN `hourly_rates`                AS `VehicleClaimHourlyRate` ON (`VehicleClaimHourlyRate`.`id` = `VehicleClaim`.`hourly_rate_id`)
    -- LEFT JOIN `view_final_material_cost` AS `FinalMaterialCost` ON (`FinalMaterialCost`.`claim_id` = `Claim`.`id` AND `FinalMaterialCost`.`Vehicle_claim_id` = `VehicleClaim`.`id`)
    -- LEFT JOIN `view_final_freight_cost`      AS `FinalFreightCost` ON (`FinalFreightCost`.`claim_id` = `Claim`.`id` AND `FinalFreightCost`.`Vehicle_claim_id` = `VehicleClaim`.`id`)
    -- LEFT JOIN `view_final_labour_cost`       AS `FinalLabourCost` ON (`FinalLabourCost`.`claim_id` = `Claim`.`id` AND `FinalLabourCost`.`Vehicle_claim_id` = `VehicleClaim`.`id`)
    LEFT JOIN `vendor_contacts`             AS `VendorContact` ON (`VendorContact`.`id` = `Claim`.`vendor_contact_id`)
    LEFT JOIN `vendors`                     AS `Vendor` ON (`Vendor`.`id` = `VendorContact`.`vendor_id`)
    LEFT JOIN `users`                       AS `User` ON (`User`.`id` = `Claim`.`user_id`)
    LEFT JOIN `clients`                     AS `Client` ON (`Client`.`id` = `Claim`.`client_id`)
    LEFT JOIN `claim_statuses`              AS `ClaimStatus` ON (`ClaimStatus`.`id` = `Claim`.`claim_status_id`)
    LEFT JOIN `claim_updates`               AS `ClaimUpdate` ON (`ClaimUpdate`.`id` = `Claim`.`claim_update_id`)
    left join `claim_statuses`              AS `ClaimUpdateStatus` on (`ClaimUpdateStatus`.`id` = `ClaimUpdate`.`claim_status_id`)
    left join `claim_statuses`              AS `VehicleClaimStatus` on (`ClaimStatus`.`id` = `VehicleClaim`.`claim_status_id`)
    LEFT JOIN `claim_updates`               AS `VehicleClaimUpdate` ON (`VehicleClaimUpdate`.`id` = `VehicleClaim`.`claim_update_id`)
    left join `claim_statuses`              AS `VehicleClaimUpdateStatus` on (`VehicleClaimUpdateStatus`.`id` = `VehicleClaimUpdate`.`claim_status_id`)
WHERE YEAR(`Claim`.`created`) = YEAR(CURDATE())
GROUP BY `VehicleClaim`.`id`