<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\Donations;
use DB;

class Donors extends Model
{
    //we can also protected table
    
    protected $primaryKey = 'id';
   // public $incrementing = true; //increments donor_id ?????

    public function donations(){
      return $this->hasMany(Donations::class,'donor_id');

    }
    
    //https://www.youtube.com/watch?v=ku3Lyy9wIm4
    //this may be ok for search field query
    public function scopeSearch($query, $first_name){
        return $query->where('first_name', 'like','%'.$first_name.'%')->orWhere('last_name','like','%' .$first_name. '%');
    }
    
    
    //add donations to Donor
    public function addDonation(Donations $donations){
      return $this->donations()->save($donations);  
    }

    protected function donorSearch($keyword)
    {
        $number="$keyword";

$regexp = "(0|.* )?".substr($number,0,3)."-?".substr($number,3,3)."-?".substr($number,6,4);

  $query = "(SELECT * FROM donors WHERE first_name LIKE '%" . 
            $keyword . "%' OR last_name LIKE '%" . $keyword ."%' OR city LIKE '%" . $keyword ."%' OR mobile_no REGEXP '$regexp' OR business_no REGEXP '$regexp')";
      
         $results = DB::select($query);
         return $results;
    }

    // protected function insertDonor($request)
    // {
    //      $id = DB::table('donors')->insertGetId(
    //         [
    //           'title' => $request->input('title'),
    //           'first_name' => $request->input('fname'),
    //           'last_name' => $request->input('lname'),
    //           'address' => $request->input('address'),
    //           'country' => $request->input('country'),
    //           'city' => $request->input('city'),
    //           'mobile_no' => $request->input('mobile_no')
    //         ]
    //     );
    //     return $id;
    //     //dd($id);
    // }

    protected function getDetails($id)
    {
        $results = $this->select('*')
                    ->join('donations','donations.donor_id','=','donors.id')
                    ->where('donations.receipt_no','=',$id)
                    ->get();
        return $results;
    }

    protected function getDonors()
    {
        $results = $this->select('*')
                    ->orderBy('id', 'desc')
                    ->get();
                    // dd($results);
        return $results;
    }

     protected function addDonations($id)
    {
        // dd($id);
         $results = $this->select(['donors.id','donors.title',
                   'donors.first_name','donors.last_name','donors.mobile_no' ,'donors.address','datacountries.country',
                                 'donors.city'])
                    ->join('datacountries','datacountries.id','=','donors.id')
                    ->where('donors.id','=',$id)
                    ->first();
        return $results;
       
    }

     protected function reciptDetails($id)
    {
        // dd($id);
         $results = $this->select(['donors.id','donors.title',
           'donors.first_name','donors.last_name','donors.address' ,'donors.city','donations.donation_date','donations.donation_amount'
           ,'donations.project','donations.allocation','donations.category','donations.receipt_no','donations.cheque_num','donations.bank','donations.currency', 'donations.rmd_status','donations.finance_status'])
                                 
                    ->join('donations','donations.donor_id','=','donors.id')
                    ->where('donations.id','=',$id)
                    ->first();
                    
        return $results;
    }


    protected function getDonorsFields()
    {
        // $query = "select country AS '0',count(*) as '1' from `donors` GROUP BY country";
        $query = " select  c.country AS '0',count(*) as '1' from `donors` d INNER JOIN datacountries c ON c.id = d.country  GROUP BY d.country";
        
        $results = DB::select($query);
        $result = array_map(function ($results) {
            return (array)$results;
        }, $results);
        return $result;
    }

    protected function getDonorReport()
    {
       $query = "SELECT id,donor_type,first_name,CONCAT(`first_name`,' ',`last_name`) AS name,`care_of`,`city`,`state`,`country`,`mobile_no`,`business_no`,`designation`,`address` FROM `donors` order by id desc";
        $results = DB::select($query);
       return $results;
    }

    protected function getDonationReport()
    {

       $query = "SELECT donations.id as donation_id,donors.id,donors.first_name,CONCAT(donors.first_name,' ',donors.last_name) AS name, donations.receipt_no, donations.donation_date, donations.donation_amount, donations.receipt_name, donations.project, donations.category, donations.allocation, donations.receipt_status, donations.rmd_status, donations.finance_status FROM `donations` INNER JOIN donors ON donations.donor_id = donors.id  order by donations.id desc";

            $results = DB::select($query);
       return $results;
     }

      protected function getDonationFilterReport($from,$to)
    {
         $start_date =$from;
         $end_date = $to;

      $filter = "STR_TO_DATE(`donations`.`donation_date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('".$start_date."', '%m/%d/%Y') AND STR_TO_DATE('".$end_date."', '%m/%d/%Y')";
      $filter = $filter."AND `donations`.`donation_date` != ''";
      // dd($end_date);
       $query = "SELECT donations.id as donation_id,donors.id,donors.first_name,CONCAT(donors.first_name,' ',donors.last_name) AS name, donations.receipt_no, donations.donation_date, donations.donation_amount, donations.receipt_name, donations.project, donations.category, donations.allocation, donations.receipt_status, donations.rmd_status, donations.finance_status FROM `donations` INNER JOIN donors ON donations.donor_id = donors.id where ".$filter." order by donations.id desc";

      
       // dd($query);
          $results = DB::select($query);
       return $results;
     }


protected function getReport()
    {

 $qry = donors::select(DB::raw('CONCAT(first_name," ",last_name) AS name'),'first_name','care_of','donor_type','address','mobile_no','city','datacountries.country','datastates.stateName','donors.id')
               ->Leftjoin('datacountries', 'donors.country', '=', 'datacountries.id')
               ->Leftjoin('datastates', 'donors.state', '=', 'datastates.id')
               ->get();

                // dd($qry);
            return $qry;
   }  

     protected function getExcelDonorData()
    {

       $query = "SELECT d.id as 'Donor ID',d.donor_type as 'DONOR TYPE', CONCAT_WS(' ',d.first_name,d.last_name) as 'FULL NAME', c.country as 'COUNTRY', s.stateName as 'STATE',d.city as 'CITY',d.care_of as 'CARE OF', d.designation as 'DESIGNATION',d.address as 'ADDRESS', d.address2 as 'OTHER ADDRESS', d.mobile_no as 'MOBILE NUMBER', d.business_no as 'ALTERNATE NUMBER', d.email as 'EMAIL',d.company as 'COMPANY', d.comment as 'COMMENT' FROM `donors` d LEFT JOIN datacountries c ON d.country=c.id LEFT JOIN datastates s ON d.state = s.id";

        $results = DB::select($query);
       return $results;
// dd($results);

    }


     protected function getExcelDonationData($request)
    {
        $start_date=$request->from_date;
        $end_date=$request->to_date;

        $query = "SELECT d.id as 'Donor ID',don.id as 'DONATION ID',d.donor_type as 'DONOR TYPE', CONCAT_WS(' ',d.first_name,d.last_name) as 'FULL NAME',d.city as 'CITY',d.care_of as 'CARE OF', d.address as 'ADDRESS', d.mobile_no as 'MOBILE NUMBER',don.receipt_no as 'RECEIPT NUMBER', CONCAT(don.currency, ' ' ,don.donation_amount) as 'AMOUNT', don.donation_date as 'DONATION DATE', don.tax as 'TAX',don.exchange as 'EXCHANGE RATE', don.actual_amt as 'RECEIVED AMOUNT', don.receipt_name as 'RECEIPT NAME', don.mode as 'MOOD OF PAYMENT', don.cheque_num as 'CHEQUE NUMBER' , don.bank as 'BANK',don.ins_date as 'INSTRUMENT DATE', al.allo_name as 'ALLOCATION',don.description as 'ALLOCATION DESCRIPTION', don.purpose as 'PURPOSE', c.cat_name as 'CATEGORY', pro.pro_name as 'PROJECT' FROM `donations` don INNER JOIN donors d ON d.id=don.donor_id LEFT JOIN allocation_list al ON al.allo_id = don.allocation LEFT JOIN category_list c ON c.cat_id=don.category LEFT JOIN project_list pro ON pro.pro_id = don.project where 1";

            if($start_date != "" && $end_date != "")
                
             $query =  $query." AND STR_TO_DATE(`don`.`donation_date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('".$start_date."', '%m/%d/%Y') AND STR_TO_DATE('".$end_date."', '%m/%d/%Y')";

            $query = $query." AND `don`.`donation_date` != '' order by don.id desc";

        $results = DB::select($query);
       return $results;
// dd($results);

    }   
    
}