<?php

namespace App;

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

class OptionList extends Model
{
    protected function getProjectList()
    {
        $projectList = DB::table('project_list')
                            ->select('pro_id as id', 'pro_name as name','active')
                            ->get();
        return $projectList;
    }
    
    protected function insertProject($project_name)
    {
        DB::table('project_list')->insert(
            [
              'pro_name' => $project_name,
              'active' => 1
            ]
        );
    }
    
    protected function updateProject($project_name,$project_id)
    {
        DB::table('project_list')
            ->where('pro_id', $project_id)
            ->update(['pro_name' => $project_name]);
    }
    
    protected function changeStatus($id,$status,$table)
    {
        if($table =='project_list'){
            $table_id='pro_id';
        }elseif($table =='category_list'){
            $table_id='cat_id';
        }elseif($table =='allocation_list'){
            $table_id='allo_id';
        }elseif($table =='users'){
            $table_id='id';
        }
        DB::table($table)
            ->where($table_id, $id)
            ->update(['active' => $status]);
        
    }
    

    protected function getCategoryList()
    {
        $categoryList = DB::table('category_list')
                            ->select('cat_id as id', 'cat_name as name','active')
                            ->where('active', 1)
                            ->get();
                            // dd($categoryList);
        return $categoryList;
    }

    
    protected function updateCategory($name,$id)
    {
        DB::table('category_list')
            ->where('cat_id', $id)
            ->update(['cat_name' => $name]);
    }
    
    protected function insertCategory($name)
    {
        DB::table('category_list')->insert(
            [
              'cat_name' => $name,
              'active' => 1
            ]
        );
    }
    
    protected function getAllocationList()
    {
        $allocationList = DB::table('allocation_list')
                            ->select('allo_id as id', 'allo_name as name','active')
                            ->get();
        return $allocationList;
    }
    
    protected function insertAllocation($name)
    {
        DB::table('allocation_list')->insert(
            [
              'allo_name' => $name,
              'active' => 1
            ]
        );
    }
    
    protected function updateAllocation($name,$id)
    {
        DB::table('allocation_list')
            ->where('allo_id', $id)
            ->update(['allo_name' => $name]);
    }

    protected function getDynamicReport($request)
    {
        $cols='';
        $columns = $request->donor;

       foreach($columns as $column)
        {
          $cols .= $column.',';
        }
         $all_cols = rtrim($cols,',');

       $query = "SELECT ".$all_cols." FROM `donations` INNER JOIN donors ON donors.id=donations.donor_id LEFT JOIN allocation_list ON allocation_list.allo_id = donations.allocation LEFT JOIN category_list  ON category_list.cat_id=donations.category LEFT JOIN project_list ON project_list.pro_id = donations.project LEFT JOIN datacountries ON datacountries.id = donors.country LEFT JOIN datastates ON datastates.id = donors.state where 1";

        if($request->start_date != "" && $request->end_date != "")

         $query =  $query." AND STR_TO_DATE(`donations`.`donation_date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('".$request->start_date."', '%m/%d/%Y') AND STR_TO_DATE('".$request->end_date."', '%m/%d/%Y')";

        if($request->city != "" )
                $query = $query." AND `donors`.`city` = '".$request->city."'";

        if($request->category != "" )
                $query = $query." AND `donations`.`category` = '".$request->category."'";

        if($request->project != "" )
                $query = $query." AND `donations`.`project` = '".$request->project."'";   

        if($request->allocation != "" )
                $query = $query." AND `donations`.`allocation` = '".$request->allocation."'";

        if($request->condition == 0){
         if($request->amount != "" )
            $query = $query." AND `donations`.`donation_amount` <= ".$request->amount."";
      }else{        
        if($request->amount != "" )
                $query = $query." AND `donations`.`donation_amount` >= ".$request->amount.""; 
         }       
        if($request->careof != "" )
                $query = $query." AND `donors`.`care_of` = '".$request->careof."'";
                $query = $query." order by donations.id desc";
                 // print_r($query);
                 // exit();                               
     $results = DB::select($query);
       return $results;
     }    


      protected function getExcelDynamicReport($request)
    {

        $val=array();
        foreach($request as $key => $product)
           {
             $start_date = $product['start_date'];
             $val = $product['donor'];
           }
           $all_cols = implode(',', $val);
           

       $query = "SELECT ".$all_cols." FROM `donations` INNER JOIN donors ON donors.id=donations.donor_id LEFT JOIN allocation_list ON allocation_list.allo_id = donations.allocation LEFT JOIN category_list  ON category_list.cat_id=donations.category LEFT JOIN project_list ON project_list.pro_id = donations.project LEFT JOIN datacountries ON datacountries.id = donors.country LEFT JOIN datastates ON datastates.id = donors.state where 1";

        if($product['start_date'] != "" && $product['end_date'] != "" )

         $query =  $query." AND STR_TO_DATE(`donations`.`donation_date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('".$product['start_date']."', '%m/%d/%Y') AND STR_TO_DATE('".$product['end_date']."', '%m/%d/%Y')";

        if($product['city'] != "" )
                $query = $query." AND `donors`.`city` = '".$product['city']."'";

        if($product['category'] != "" )
                $query = $query." AND `donations`.`category` = '".$product['category']."'";

        if($product['project'] != "" )
                $query = $query." AND `donations`.`project` = '".$product['project']."'";   

        if($product['allocation'] != "" )
              $query = $query." AND `donations`.`allocation` = '".$product['allocation']."'"; 

       if($product['condition'] == 0) {

         if($product['amount'] != "" )
         $query = $query." AND `donations`.`donation_amount` <= ".$product['amount'].""; 

       }else{

        if($product['amount']  != "" )
                $query = $query." AND `donations`.`donation_amount` >= ".$product['amount'].""; 
        }

        if($product['careof'] != "" )
                $query = $query." AND `donors`.`care_of` = '".$product['careof']."'";
                $query = $query." order by donations.id desc";

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