<?php
namespace App\Http\Controllers\v2019;

use Illuminate\Http\Request;
use App\Http\Controllers\Auth\authentication;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\JsonResponse;
use App\Http\Controllers\Auth\User;
use App\Http\Controllers\Action\Common;
class awsQueriesController extends Controller
{ 
	

    public $appType = 'SM' ;
	public function __construct(Request $request) {
        if($request->has('debug') && $request->input('debug') == true) {
            $this->debug = true;			
        }
		$this->appType = 'SM';
		if($request->has('appType') && $request->input('appType') == true) {
            $this->appType = $request->input('appType');		
        } elseif($request->has('filters')){
			 $filter = $request->input('filters');
			 if(!empty($filter['app_type'])){
					$this->appType = 'CM';
			 }
		} elseif($request->has('app_type') && $request->input('app_type') == true) {
            $this->appType = $request->input('app_type');		
        }
		
	}
	
	public function PortfolioCustomReportCategory(Request $request){       
		$resourceType = "app";
		$user_id = 0;
		$result = array();	
		if($request->has('resourceType'))
			$resourceType = strtolower($request->input('resourceType'));
        if($request->has('memid'))
			$memid = strtolower($request->input('memid'));
        if($request->has('catID'))
			$catID = strtolower($request->input('catID'));
		
        $where='';
        if(empty($memid))
            return new JsonResponse(array("status"=>array("code" => 2100, "message" => "Memid blank.")));
        
         $query1 = DB::table('tbl_steelreport AS s1')
            ->select('c1.id as categoryTtitleID','c1.title AS Title')
            ->join('tbl_steelreports_category AS c1', 's1.steelreports_category_id', '=', 'c1.id')
            ->join('tbl_reg_logs AS r1', function ($join) {
                $join->on(DB::raw("FIND_IN_SET(r1.selected_products, c1.AccessibleWithPackage)"), DB::raw(''), DB::raw(''));
            })
            ->where('r1.memid', '=', $memid)
            ->whereIn('r1.f_product', ['MA', 'W', 'C'])
            ->whereIn('r1.status', ['Active', 'Expired'])
            ->where('r1.inv_no', '<>', '')
            ->whereIn('c1.isMonthlyOrCumulative', ['c', 'm'])            
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->where('c1.isMonthlyOrCumulative', '=', 'm')
                        ->whereRaw('r1.startdate <= s1.from_month_year')
                        ->whereRaw('r1.enddate >= s1.from_month_year');
                })->orWhere(function ($q) {
                    $q->where('c1.isMonthlyOrCumulative', '=', 'c')
                        ->whereRaw('r1.enddate >= s1.to_month_year')
                        ->whereRaw('r1.startdate <= s1.to_month_year');
                });
            });
            if(!empty($catID))
                 $query1 = $query1->where('c1.commodityID', '=', $catID);

        $query2 = DB::table('tbl_steelreport AS s2')
             ->select('c2.id as categoryTtitleID','c2.title AS Title')
            ->join('tbl_steelreports_category AS c2', 's2.steelreports_category_id', '=', 'c2.id')
            ->join('tbl_reg_logs AS r2', 'r2.selected_products', '=', 'c2.id')
            ->where('r2.memid', '=', $memid)
            ->where('r2.f_product', '=', 'CD')
            ->where('r2.cd_inv_no', '<>', '')
            ->whereIn('r2.status', ['Active', 'Expired'])
            ->whereIn('c2.isMonthlyOrCumulative', ['c', 'm'])
            
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->where('c2.isMonthlyOrCumulative', '=', 'm')
                        ->whereRaw('r2.startdate <= s2.from_month_year')
                        ->whereRaw('r2.enddate >= s2.from_month_year');
                })->orWhere(function ($q) {
                    $q->where('c2.isMonthlyOrCumulative', '=', 'c')
                        ->whereRaw('r2.enddate >= s2.to_month_year')
                        ->whereRaw('r2.startdate <= s2.to_month_year');
                });
            });
         if(!empty($catID))
                $query2 = $query2->where('c2.commodityID', '=', $catID);
    
       
        $categoryTitles = $query1->union($query2)->get();
        
       
        $code = 1001;
        $message = "Success";
        return new JsonResponse(array("status"=>array("code" => $code, "message" => $message), "data"=> $categoryTitles));		
	}
	
	public function PortfolioCustomReportList(Request $request){
		$resourceType = "app";
		$user_id = 0;
		$result = array();	
		if($request->has('resourceType'))
			$resourceType = strtolower($request->input('resourceType'));
        if($request->has('memid'))
			$memid = strtolower($request->input('memid'));
        if($request->has('catID'))
			$catID = strtolower($request->input('catID'));
		
        $where='';
        if(empty($memid))
            return new JsonResponse(array("status"=>array("code" => 2100, "message" => "Memid blank.")));
        $query1 = DB::table('tbl_steelreport AS s1')
            ->select('c1.id AS categoryTtitleID',
                DB::raw(" CASE
                        WHEN c1.isMonthlyOrCumulative = 'c' THEN CONCAT(s1.category, ' (', DATE_FORMAT(s1.from_month_year, '%b-%Y'), ' - ', DATE_FORMAT(s1.to_month_year, '%b-%Y'), ')')
                        ELSE CONCAT(s1.category, ' (', DATE_FORMAT(s1.from_month_year, '%b-%Y'), ')')
                    END AS category"),
                DB::raw("FROM_UNIXTIME(s1.date,'%d-%b-%Y') AS date"),
                's1.file AS file','s1.prefix_to_filepath')
            ->selectRaw("CASE 
                WHEN c1.isMonthlyOrCumulative = 'c' THEN 'cumulative' 
                WHEN c1.isMonthlyOrCumulative = 'm' THEN 'monthly' 
                ELSE '' 
                END AS data_type")
            ->join('tbl_steelreports_category AS c1', 's1.steelreports_category_id', '=', 'c1.id')
            ->join('tbl_reg_logs AS r1', function ($join) {
                $join->on(DB::raw("FIND_IN_SET(r1.selected_products, c1.AccessibleWithPackage)"), DB::raw(''), DB::raw(''));
            })
            ->where('r1.memid', '=', $memid)
            ->whereIn('r1.f_product', ['MA', 'W', 'C'])
            ->whereIn('r1.status', ['Active', 'Expired'])
            ->where('r1.inv_no', '<>', '')
            ->whereIn('c1.isMonthlyOrCumulative', ['c', 'm'])            
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->where('c1.isMonthlyOrCumulative', '=', 'm')
                        ->whereRaw('r1.startdate <= s1.from_month_year')
                        ->whereRaw('r1.enddate >= s1.from_month_year');
                })->orWhere(function ($q) {
                    $q->where('c1.isMonthlyOrCumulative', '=', 'c')
                        ->whereRaw('r1.enddate >= s1.to_month_year')
                        ->whereRaw('r1.startdate <= s1.to_month_year');
                });
            });
            if(!empty($catID))
                 $query1 = $query1->where('c1.commodityID', '=', $catID);

        $query2 = DB::table('tbl_steelreport AS s2')
            ->select('c2.id AS categoryTtitleID',
                DB::raw("
                    CASE
                        WHEN c2.isMonthlyOrCumulative = 'c' THEN CONCAT(s2.category, ' (', DATE_FORMAT(s2.from_month_year, '%b-%Y'), ' - ', DATE_FORMAT(s2.to_month_year, '%b-%Y'), ')')
                        ELSE CONCAT(s2.category, ' (', DATE_FORMAT(s2.from_month_year, '%b-%Y'), ')')
                    END AS category
                "),
                DB::raw("FROM_UNIXTIME(s2.date,'%d-%b-%Y') AS date"),
                's2.file AS file','s2.prefix_to_filepath')
            ->selectRaw("CASE 
                WHEN c2.isMonthlyOrCumulative = 'c' THEN 'cumulative' 
                WHEN c2.isMonthlyOrCumulative = 'm' THEN 'monthly' 
                ELSE '' 
                END AS data_type")
            ->join('tbl_steelreports_category AS c2', 's2.steelreports_category_id', '=', 'c2.id')
            ->join('tbl_reg_logs AS r2', 'r2.selected_products', '=', 'c2.id')
            ->where('r2.memid', '=', $memid)
            ->where('r2.f_product', '=', 'CD')
            ->where('r2.cd_inv_no', '<>', '')
            ->whereIn('r2.status', ['Active', 'Expired'])
            ->whereIn('c2.isMonthlyOrCumulative', ['c', 'm'])
            
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->where('c2.isMonthlyOrCumulative', '=', 'm')
                        ->whereRaw('r2.startdate <= s2.from_month_year')
                        ->whereRaw('r2.enddate >= s2.from_month_year');
                })->orWhere(function ($q) {
                    $q->where('c2.isMonthlyOrCumulative', '=', 'c')
                        ->whereRaw('r2.enddate >= s2.to_month_year')
                        ->whereRaw('r2.startdate <= s2.to_month_year');
                });
            });
         if(!empty($catID))
                $query2 = $query2->where('c2.commodityID', '=', $catID);
        $data = $query1->union($query2)->get();
       
        $code = 1001;
        $message = "Success";
        return new JsonResponse(array("status"=>array("code" => $code, "message" => $message), "data"=> $data));			
	}
}
