<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Action\Enquiry;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\Http\Controllers\Auth\authentication;
use App\Http\Controllers\Auth\User ;
use DB;

use App\Http\Controllers\Action\Tender;
use App\Http\Controllers\Action\Common;
use Illuminate\Http\Response;

class TendersController extends Controller
{
    private $temp ;
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    private $category_corelation = [
        "Billet" => "INGOT,BILLET,BLOOM,SLAB",
        "Sponge Iron" => "SPONGE_IRON,SPONGE_PELLET",
        "Finish Long" => "PIPES,RAIL,ROUND,STRUCTURE,WIRE,TMT",
        "Finish Flat" => "HR_PLATE, HRC, CRC,SHEET, PLATE" ,
        "MnOre" => "MANGANESE_ORE" ,
        "CrOre" => "CHROME_ORE,CHROME CONC" ,
        "SiMn" => "SILICO_MANGANESE" ,
        "FeMn" => "FERRO_MANGANESE" ,
        "FeCr" => "FERRO_CHROME" ,
        "FeSi" => "FERRO_SILICON" ,
    ];
    public function getalltenders(Request $request)
    {
        $GLOBALS['currDate'] = date("Y-m-d");
        $start = 0;
        $number_of_posts = 30 ;
        $filters='';
        $category = '';
        $arr=array();
        $_app_post='' ;
        $userAccess = 0;
        $user = null ;
		
		$appType='SM';
		if($request->has('appType') && strtoupper($request->input('appType')) == 'CM'){ 
			$appType=$request->input('appType');
		}
		
        if($request->has('token') || $request->has('memid')
             )
		{
            if($request->input('memid')==56350){
				$user_id =56350;
			}else if($request->has('memid')){
				$memid = $request->input('memid');
				$memid = _encrypt_decrypt($action='decrypt', $memid);
				$auth = new authentication();
				$user_id = $auth->authUserId($memid);//for web user
			}else{
				$auth = new authentication();
				$token = $request->input('token');
				$user_id = $auth->authToken($token);
				}
			
            if ($user_id) {
                $user = new User($user_id, $appType);

            } else{
			 return new JsonResponse(array(
                    "status#" => "403" ,
                    "message#" => "Token unauthenticated"
                )) ;
              //  abort(403, 'Unauthorized action. Token access denied');
			}
        }

        /**
         * To limit get post after that post number
         */
        if($request->has('postfrom'))
        {
            $_app_post = $request->input('postfrom') ;
        }

        /*
         * For start limit
         */
        if ($request->has('start'))
        {
            $start = $request->input('start') ;
        }

        /*
         * Number of posts wants to see
         */
        if ($request->has('number'))
        {
            $number_of_posts = $request->input('number') ;
        }

        /*
        * For getting filter type
        */
        if ($request->has('arr_filter'))
        {
            $arr = $request->input('arr_filter') ;

            if(!empty($arr['date']))
            {
                $pattern ='(\\d.*)';
                $matches = '';
                preg_match($pattern, $arr['date'], $matches);
                $arr['date'] = $matches[0] ;
            }
        }

        if ($request->has('filter'))
        {
            $filter = $request->input('filter') ;
        }
		
		
		
		/*portfolio filter*/
		if($request->has('portfolio'))
        {
            $common = new Common();
			$temp = $common->getUserPortfolio($user_id, 'tenders', $appType);		

			$arr['category_portfolio'] = $temp['category'];
			//$arr['category'] = $temp['category'];
			$arr['lead_type'] = str_replace(',',';',$temp['lead_type']); 			
			//$arr['country_name'] = $temp['country']; 
			if(empty($arr['category']) && empty($arr['lead_type'])){
				return new Response( json_decode(html_entity_decode(json_encode($posts) ) ));
			}
        }///*end portfolio filter*/
		
        $posts = array();
        /* get the posts */
        $currDate=$GLOBALS['currDate'];
        //$currDate='2014-01-01';
        $having='';
        $col=''; $val='';

        $tender = new Tender();

        /*
         * dd = due_date
         * od = opening_date
         */

        /**
         * For filters. If filter col:val
         * col = date
         * col = product/category
         * col = state_name
         * col = Country
         * col = search, then value has been search in all
         */
		 $additional_col=''; $catName = '';
        if(!empty($filter))
        {

            list($col, $val) = explode(':', $filter);

            $col = trim($col);
            $val = trim($val);
            if( !empty($val) && 'all' !=$val )
            {
                if($col=='date')
                {
                    $val = date('Y-m-d' , strtotime($val));
                    $having.="HAVING (( dd >= '$currDate' and dd <= '$val') OR (od >= '$currDate' and od <= '$val')) ";
                }
                elseif($col=='state_name' || $col=='Country')
                {
                    if($col=='country')
                        $tender_ids = $tender->getTenderByCountry($val);
                    else
                        $tender_ids = $tender->getTenderByState($val);

                    if($tender_ids)
                        $having.="HAVING id in ($tender_ids) ";
                    else
                        $having.="HAVING id in (0) ";
                }
                else
                {
                    if($col=='product' || $col=="category")
                    {
                        $val = explode('/', $val);
                        $str=''; 
                        if(count($val)>0)
                        {
                            foreach($val as $k=>$v)
                            {
                                if($k>0)
                                    $str.=" or ";

                                /* foreach( $this->category_corelation as $key => $value )
                                {
                                    $v = str_replace( $key , $value , $v ) ;
                                } */
                                if($col=="Product")
                                    $str.= " sub_category_all like '%".$v."%'";
                                else{
                                    $str.= " lower(categoryToMatch) like '%". strtolower($v) ."%'";
									$catName = $v;
								}	
                            }
                        }

                        $having.="HAVING (".$str.")";
                    }
                    elseif($col=='search')
                    {
                        $v = $val;
                        $v = $val;
						$additional_col = ", REPLACE(UPPER(concat(title, sub_category_all, category_all, company_name, buy_sell,offer_type, country_name, state_name, region, description, remarks, floor_price, qty, grade)), ' ', '') as s_res";
					
						$_whr_1 = "";
						$search_title_Arr = explode(" ", $val);
						if(count($search_title_Arr)>0){
							foreach($search_title_Arr as $key=> $_s_key){
								if(trim($_s_key)!='' ){
									if($_whr_1)
										$_whr_1.=' and ';
									$_s_key = str_replace(" ", "",$_s_key);
									$_whr_1.= ' s_res like UPPER("%'.$_s_key.'%") ';
								}
							}
						}
						$having.="HAVING (".$_whr_1.") ";
					   /* $having.="HAVING
						  (title like '%".$v."%' or
						  sub_category_all like '%".$v."%' or
						  category_all like '%".$v."%' or
						  sub_category_all like '%".$v."%' or
						  company_name like '%".$v."%' or
						  buy_sell like '%".$v."%' or
						  offer_type like '%".$v."%' or
						  state_name like '%".$v."%' or
						  country_name like '%".$v."%' or
						  live_expired like '%".$v."%' or
						  region like '%".$v."%' )
						"; */
                    }
                    else
                        $having.="HAVING ".$col." like '%".$val."%'";
                }
            }
        }
		

        /**
         * Below are for all filter
         * item = sub category
         * islive = live or not
         * lead_type = Buy/Sell/Services
         * offer_type = TENDER/AUCTION
         * category
         * floor_price
         * opening_date
         * closing_date
         * country_name
         */

        /**
         * For sub_category, not required by API
         */
        if((isset($arr['item']) && strtolower($arr['item'])!='') || $col=='item')
        {
            $item = '';
            if($col=='item')
                $item=$val;
            else
                $item=$arr['item'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $item = "'" . str_replace(";","','", $item)."'";
            $having.=" sub_category like '%".$item."%'";
        }

        /**
         *
         */
        if((isset($arr['archive']) && strtolower($arr['archive'])!='' && strtolower($arr['archive'])!='all') )
        {
            $live = '';
            if($col=='islive')
                $live=$val;
            else
                $live=$arr['archive'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";

            // $having.=" live_expired = '$live'";
            $live_in = "'" . str_replace(",","','", $live)."'";
            $having.=" live_expired in (".$live_in.")";
        }
        else
        {
            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";

            $having.=" live_expired = 'live'";
        }

        if((isset($arr['lead_type']) && strtolower($arr['lead_type'])!='' && strtolower($arr['lead_type'])!='all') )
        {
            $lead_type = '';
            if($col=='lead_type')
                $lead_type=$val;
            else
                $lead_type=$arr['lead_type'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            // $having.=" buy_sell = '$lead_type'";
			$lead_type = rtrim( $lead_type,','); 
			$lead_type = rtrim( $lead_type,';'); 
            $buy_sell_in = "'" . str_replace(";","','", $lead_type)."'";

            $having.=" buy_sell in (".$buy_sell_in.")";
        }

        if((isset($arr['company_name']) && strtolower($arr['company_name'])!='' && strtolower($arr['company_name'])!='all'))
        {
            $company_name = '' ;
            if($col=='company_name')
                $company_name=$val;
            else
                $company_name=$arr['company_name'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            //$having.=" buy_sell = '$lead_type'";

            $company_name_in = "'" . str_replace(";","','", $company_name)."'";

            $having.=" company_name in (".$company_name_in.")";
        }

        if(isset($arr['date']) && strtolower($arr['date'])!='' )
        {
            $_date = '';
            if($col=='date')
                $_date=$val;
            else
                $_date=$arr['date'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            //$having.=" buy_sell = '$lead_type'";
            $max_date='';
            if($max_date=='')
                $max_date =  date('Y-m-d' , strtotime($_date));
            elseif(strtotime($_date) > strtotime($max_date))
                $max_date =  date('Y-m-d' , strtotime($_date));

            $having.=" (( dd >= '$currDate' and dd <= '$max_date') OR (od >= '$currDate' and od <= '$max_date')) ";
        }

        if((isset($arr['offer_type']) && strtolower($arr['offer_type'])!='') )
        {
            $offer_type = '' ;
            if($col=='offer_type')
                $offer_type=$val;
            else
                $offer_type=$arr['offer_type'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $having.=" offer_type in (".$offer_type.")";
        }
		
        if(  (!empty($arr['category'])) && $arr['category'] !='all' )
        {
            $category = '' ;
            if($col=='category')
                $category=$val;
            else
                $category=$arr['category'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $category = str_replace(";",",", $category);
			 $category = rtrim( $category,','); 
            foreach( $this->category_corelation as $key => $value )
            {
                $category = str_replace( $key , $value , $category ) ;
            }
            // $having.=" sub_category = '$category'";
            $category = str_replace("_", " ",$category);
            //$sub_category_in = "'" . str_replace(",","','", $category)."'";
            $sub_category_like = "lower(sub_category_all) like '%" . str_replace(",","%' OR sub_category_all like '%", strtolower($category))."%'";
            //$sub_category_like = "lower(category_all) like '%" . str_replace(",","%' OR category_all like '%", strtolower($category))."%'";

            $having.=" ($sub_category_like) ";
        }
		
		
		///for coalmint
		if($request->has('appType') && strtoupper($request->input('appType')) == 'CM'){        
            $category=$arr['category_portfolio'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
           
			$category = rtrim( $category,','); 
            $category_like = "lower(category_all) like '%coal%'";

            $having.=" ($category_like) ";
        }
		
		 if(  (!empty($arr['category_portfolio'])) && $arr['category_portfolio'] !='all' )        {
            $category=$arr['category_portfolio'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
           
			$category = rtrim( $category,','); 
            $category_like = "lower(category_all) like '%" . str_replace(",","%' OR category_all like '%", strtolower($category))."%'";

            $having.=" ($category_like) ";
        }
		
        if((isset($arr['floor_price']) && strtolower($arr['floor_price'])!='' && strtolower($arr['floor_price'])!='all') )
        {
            $floor_price = '' ;
            if($col=='floor_price')
                $floor_price=$val;
            else
                $floor_price=$arr['floor_price'];

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $tender_cond = $arr['tender_cond'];
            $having.=" floor_price ".$tender_cond." '$floor_price'";
        }
        if(isset($arr['opening_date']) && strtolower($arr['opening_date'])!='' )
        {
            $opening_date=trim($arr['opening_date']);
            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $having.=" tt.opening_date like '$opening_date%'";
        }
        if(isset($arr['closing_date']) && strtolower($arr['closing_date'])!='' )
        {
            $closing_date=trim($arr['closing_date']);
            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";
            $having.=" tt.closing_date like '$closing_date%'";
        }

        $tender_ids = '' ;

        if((isset($arr['country_name']) && strtolower($arr['country_name'])!='') || (isset($arr['state_name']) && strtolower($arr['state_name'])!='') || (isset($arr['city_name']) && strtolower($arr['city_name'])!='') )
        {
            if(isset($arr['country_name']) && strtolower($arr['country_name'])!='')
            {
                $arr['country_name'] = "'" .str_replace(";","','", $arr['country_name']) . "'";
                // $arr['country_name'] =  "'" . str_replace(";","','", $arr['country_name'])."'" ;
                $tender_ids = $tender->qryToCommaSepString("select tender_id from  tbl_tenders_region where country_name in ( ".trim($arr['country_name'])." ) ");
            }

            if(isset($arr['state_name']) && strtolower($arr['state_name'])!='')
            {
                $arr['state_name'] =  "'". str_replace(";","','", $arr['state_name']) . "'";
                $tender_ids_1 = $tender->qryToCommaSepString("select tender_id from  tbl_tenders_region where state_name in ( '".trim($arr['country_name'])."' ) ");
                if($tender_ids)
                    if($tender_ids_1)
                        $tender_ids.=',';
                $tender_ids.=$tender_ids_1;
            }
            else
            {
                $tender_ids_2 = "" ;
                if(!empty($arr['city_name']))
                {
                    $arr['city_name'] = str_replace(";","','", $arr['city_name']);
                    $tender_ids_2 = $tender->qryToCommaSepString("select tender_id from  tbl_tenders_region where city_name in ( '".trim($arr['city_name'])."' ) ");
                }

                if(!empty( $tender_ids) )
                    if($tender_ids_2)
                        $tender_ids.=',';
                $tender_ids.=$tender_ids_2;
            }

            if($tender_ids)
            {
                if($having)
                    $having.=" and ";
                else
                    $having.=" HAVING ";

                $having.=" id in ($tender_ids) ";
            }
            else
            {
                if($having)
                    $having.=" and ";
                else
                    $having.=" HAVING ";
                $having.=" id in (0) ";
            }

            //echo $having;
        }

        if(!empty($_app_post))
        {
            list($col, $val_id) = explode(':', $_app_post);

            if($having)
                $having.=" and ";
            else
                $having.=" HAVING ";

            if($col=='new')///return ids > $val_id
                $having.=" tt.ID > '$val_id'";

            if($col=='edit')
                $having.=" tt.ID = '$val_id'";
        }
// OR (tt.opening_date <> '0000-00-00 00:00:00' AND tt.opening_date >= '$currDate')
        //echo $having;
		// hv replaced "replace(tt.category_all, '_', ' ') category_all," with category
		
		if( $catName == '' ){
			$colvalue = "category as category_all";	
		} else {
			$colvalue = "'$catName' as category_all";	
		}
		//category_all as categoryToMatch,
        $query = "select tt.ID, tt.f_date, tt.company_name,
                  tt.f_authorised ,
                  tt.qty,
                  tt.qty_unit,
                  tt.grade,
                  DATE_FORMAT(tt.opening_date,'%d %b %y') as opening_date,
                  tt.opening_date as od,
                  tt.closing_date,
                  DATE_FORMAT(tt.due_date,'%d %b %y') as due_date,
                  tt.due_date as dd,
                  tt.pdf_path,
                  replace(tt.sub_category, '_', ' ') sub_category,
                  replace(tt.sub_category_all, '_', ' ') sub_category_all,
                  $colvalue,
				  category as categoryToMatch,
                  tt.title,
                  tt.memid,
                  tt.description ,
                  tt.buy_sell,
                  tt.imgpath,
                  tt.offer_type,
                  ttr.state_name,
                  ttr.country_name,
                  CASE
                  WHEN tt.multi_loction = 1 THEN 'Multi Location'
                  WHEN ttr.country_name = 'INDIA' THEN concat(ttr.city_name,'/',ttr.state_name,'/',ttr.country_name)
                  ELSE concat(ttr.city_name, '/', ttr.country_name)
                  END as region,
                  CASE
                  WHEN (tt.due_date <> '0000-00-00 00:00:00' AND tt.due_date >= '$currDate') THEN 'Live'
                  ELSE 'Expired'
                  END as live_expired,
                  CASE 
					WHEN tt.corrigendum <> '' THEN tt.corrigendum
					ELSE ''
				  END as corrigendum,
                  CASE 
					WHEN tt.contact_person <> '' THEN tt.contact_person
					ELSE ''
				  END as contact_person,
                  CASE 
					WHEN tt.email <> '' THEN tt.email
					ELSE ''
				  END as email,
                  (select name from tbl_designation where id = tt.designation) 
				  as designation,
                  DATE_FORMAT(tt.publish_date,'%d %b %y') as publish_date
                  ,CASE
					WHEN tt.mobile<>'' then concat(tt.std_code, tt.mobile) 
					ELSE ''
				  END as phone_no
				  $additional_col 
                  from tbl_tenders as tt
                  left join
                  tbl_tenders_region as ttr

                  on tt.id = ttr.tender_id
				  where tt.verified = 1
                  group by tt.id
                  $having
                  order by tt.due_date asc
                  LIMIT $start,$number_of_posts
                  ";

        if($request->has('debug') && $request->input('debug') == true) {
            echo "<pre>", print_r($request->all()), "</pre>";        
            echo $query;    
			dd();
        }
	
        $result = $tender->query($query) ;

        $posts = $this->result_parser($result, $user) ;
		
			
        /* return the posts in the JSON format */
        if($request->has('memid'))
			return new Response( json_decode(html_entity_decode(json_encode($posts) ) ));
		else 
			return new Response( array ( "status#" => "1" , "data#" => json_decode(html_entity_decode(json_encode($posts) ) )) );

    }

    private function result_parser($result, $user )
    {
        $posts = array();
        foreach ($result as $row )
        {
            $tenderDateToCompare = $row['f_date'];
            $isAccessible = 0 ;
            if ($user != null )
                $isAccessible = $user->getTakenServicesByUser($item='tenders', $at_place='mobileapp', $tenderDateToCompare);

            // echo $isAccessible ;
            $corrigendum='';
            if($row['corrigendum']>0){
                $row['corrigendum']='Corrigendum: '.$row['corrigendum'];
            }else
                $row['corrigendum']='';

            $row['ID'] = strval($row['ID']);
            $row['data_target']='';
            $row['data_target_href']='javascript:void(0)';
            //if(!empty($user) || $row['ID']==6938){
            if(!empty($user) ){
                //if($isAccessible == -1 || $row['ID']==6938){//free trial or $sub_category is active
                if($isAccessible == -1 ){//free trial or $sub_category is active

                    if($row['pdf_path']){
                        //$row['pdf_path'] = str_replace(' ', '%20',$row['pdf_path'].'\',\'images/tenders\')');
                        $row['pdf_path'] = str_replace(' ', '%20',$row['pdf_path']);
                        $row['pdf_path'] = "https://www.steelmint.com/images/tenders/" . $row['pdf_path'];
                    }
                    // else
                    //    $row['pdf_path'] ="go('not found!')";
                    $row['imgpath']= env('TENDER_IMAGE_PREFIX').str_replace(' ', '%20',$row['imgpath']);
					$row['qty'] = number_format($row['qty']) . ' ' . $row['qty_unit'];
                }else{

                    // $row['pdf_path'] ="go('This File is available only for Paid members')";
                    $row['email'] = 'Upgrade' ;
                    $row['contact_person'] = 'Upgrade' ;
                    $row['designation'] = 'Upgrade' ;
                    $row['pdf_path'] = 'Upgrade' ;
                    $row['company_name'] = 'Upgrade';
                    $row['qty'] = 'Upgrade';                    
					$row['phone_no'] = 'Upgrade';
                    // $row['imgpath']= env('TENDER_IMAGE_PREFIX').str_replace(' ', '%20',$row['imgpath']);
                    $row['imgpath'] = "https://www.steelmint.com/images/tenders/tender-login.png" ;
                    //$row['data_target']='';
                    $row['data_target_href']='memberegi';
                }
            }else{
                // $row['pdf_path'] ="go('Please Login to download this')" ;
				$row['email'] = 'Login' ;
				$row['contact_person'] = 'Login' ;
				$row['designation'] = 'Login' ;
                $row['pdf_path'] = 'Login' ;
                $row['company_name'] = 'Login';
                $row['qty'] = 'Login';				
				$row['phone_no'] = 'Upgrade';
                // $row['imgpath']= env('TENDER_IMAGE_PREFIX').str_replace(' ', '%20',$row['imgpath']);
                $row['imgpath'] = "https://www.steelmint.com/images/tenders/tender-login.png" ;
                $row['data_target']='.bs-example-modal-sm';
            }
			if($row['designation'] == '' || $row['designation'] == 'null')
				$row['designation'] = '';
            $row['description'] = $row['company_name']." :".$row['description'] ;
            $row['state_name'] = ucwords(strtolower($row['state_name']));
            $row['country_name'] = ucwords(strtolower($row['country_name']));

            //$row['company_name'] = htmlspecialchars($str, ENT_QUOTES);
            // Remove html tag and truncate the content
            //$row['post_content'] = substr(strip_tags($row['post_content']), 0, 200) . '...';
            $row['od_display']='block';
            $row['green_text']='Due Date';
            if (empty($row['phone_no']))
                $row['phone_no']=null ;

            if(date('Y-m-d',strtotime($row['opening_date'])) < "2011-01-01"){
                $row['opening_date']='';
                $row['od_display']='none';
            }

            if(date('Y-m-d',strtotime($row['due_date'])) < "2011-01-01"){
                $row['day']=date('M',strtotime($row['opening_date']));
                $row['month_year']=date('d, Y',strtotime($row['opening_date']));

                $row['opening_date']='';
                $row['od_display']='none';
                $row['due_date']='';
                $row['green_text']='Opening Date';
            }else{
                $row['day']=date('M',strtotime($row['due_date']));
                $row['month_year']=date('d, Y',strtotime($row['due_date']));
            }

            if(empty($row['category_all']))
                $row['category_all'] = 'Others';

            $row['document_class']='';
            $img_1 = explode('.', basename($row['pdf_path']));
            $img_1_ext = strtolower($img_1[count($img_1) - 1]);
            if(strtolower($img_1_ext)=='doc' || strtolower($img_1_ext)=='docx'){
                $row['document_class']='fa-file-word-o';
            }
            elseif(strtolower($img_1_ext)=='gif' || strtolower($img_1_ext)=='docx' || strtolower($img_1_ext)=='png'){
                $row['document_class']='fa-file-photo-o';
            }
            elseif(strtolower($img_1_ext)=='pdf'){
                $row['document_class']='fa-file-pdf-o';
            }
            elseif(strtolower($img_1_ext)=='xls' || strtolower($img_1_ext)=='xlsx' || strtolower($img_1_ext)=='csv'){
                $row['document_class']='fa-file-excel-o';
            }
            elseif(strtolower($img_1_ext)=='zip' || strtolower($img_1_ext)=='rar'){
                $row['document_class']='fa-file-archive-o';
            }
            $posts[] = $row;
        }

        return $posts ;
    }
    /**
     * Get all tenders filters in form of array.
     */
    public function getfilters(Request $request)
    {
        /*
		$country_list = DB::connection('mysql')
            ->table('tbl_tenders_region')
            ->select("country_name as filter")
            ->whereRaw('country_name IS NOT NULL AND country_name <> \'\'')
            ->groupBy('country_name')
            ->orderByRaw('TRIM(country_name)')
            ->get();
		*/
		$currentDate = $GLOBALS['currDate'];
		$_cm_cond='';
		
		if($request->has('appType') && strtoupper($request->input('appType')) == 'CM'){
			$appType = $request->input('appType');
			$_cm_cond = " AND category_all like '%coal%'";
		}
		
		$country_list = DB::connection('mysql')
            ->table('tbl_tenders_region')
			->leftjoin('tbl_tenders', 'tbl_tenders.id', '=', 'tbl_tenders_region.tender_id')
            ->selectRaw("DISTINCT TRIM(country_name) as filter")
            ->whereRaw("country_name IS NOT NULL AND country_name <> '' AND tbl_tenders.id IS NOT NULL")
            ->whereRaw("tbl_tenders.verified = 1 AND  tbl_tenders.due_date <> '0000-00-00 00:00:00' AND tbl_tenders.due_date >= '$currentDate' $_cm_cond ")
            ->groupBy('country_name')
            ->orderByRaw('TRIM(country_name)')
            ->get();
		//echo '<pre>';
		//dd(DB::getQueryLog());
		//print_r($country_list);echo '</pre>';

        $res[] = array("filter" => "country_name" ,
            "sub_filter" => $country_list
        );

        //        echo $currentDate;

        $company_list = DB::connection('mysql')
            ->table('tbl_tenders')
            ->selectRaw('DISTINCT TRIM(company_name) as company_name')
            ->whereRaw(" company_name<>'' and verified = 1 AND due_date <> '0000-00-00 00:00:00' AND due_date >= '$currentDate' $_cm_cond")
	        ->orderByRaw('TRIM(company_name)')
            ->get();


        $list = array() ;
        foreach($company_list as $company )
            $list[]["filter"]= $company['company_name'] ;

        $res[] = array( "filter" => "company_name",
            "sub_filter" => $list
        ) ;

//        $company_list = @file_get_contents('https://www.steelmint.com/news/company_list.txt') ;
//        if ($company_list == FALSE )
//            $company_list = "JSPL,NMDC,SteelMint,MMTC,NINL,KIOCL,OMC";
//        if ($company_list)
//        {
//            $list = array() ;
//            $company_list = explode(',',$company_list) ;
//            foreach($company_list as $company )
//            {
//                $list[]["filter"]= $company ;
//            }
//            $res[] = array( "filter" => "company_name",
//                "sub_filter" => $list
//                ) ;
//        }
 //DB::enableQueryLog();  
		 /*$category_list = DB::connection('mysql')
            ->table('tbl_tenders')
            ->selectRaw('case category when "" then "Others" else category end as m_category, sub_category')
            ->whereRaw(" verified = 1 AND due_date <> '0000-00-00 00:00:00' AND due_date >= '$currentDate' and sub_category<>''")
	        ->groupBy('m_category')
	        ->groupBy('sub_category')
			->orderByRaw("case category
					when 'RAW MATERIAL' then 1
					when 'SEMI FINISHED' then 2
					when 'FINISH LONG' then 3
					when 'FINISH FLAT' then 4
					when 'FERRO ALLOY' then 5
					when 'Others' then 6
					else 99
				end")
            ->get();
		$list = array() ;
		$d_category='';
		$i=0;
		//dd(DB::getQueryLog());
        foreach($category_list as $_category ){
            if($d_category != $_category['m_category']){
				$d_category=$_category['m_category'];
				$i++;
				$list[$i-1]['filter']= $d_category;				
			}			
            $list[$i-1]['sub_filter'][]['filter'] = $_category['sub_category'] ;
		}
		
		$res[] = array( "filter" => "category",
            "sub_filter" => $list
        ) ; */
		if($request->has('appType')  && strtoupper($request->input('appType')) == 'CM'){
			$path = storage_path() . "/json/constants/v2_coalmint_action_tenders_filters.json";
        }else {
			$path = storage_path() . "/json/constants/v2_action_tenders_filters.json";
		}
		
        $category_list = json_decode(file_get_contents($path), true);

        $res[] = $category_list;

	
	 	/* echo '<pre>';
		//echo DB::last_query();
		print_r($category_list); 
		print_r($res); 
		echo '</pre>'; */ 
        /*  $res[] = array(
            "filter"=> "category",
            "sub_filter" => [
                [ "filter" => "RAW MATERIAL",
                    "sub_filter" =>
                        [
                            ["filter" => "IRON ORE"],
                            ["filter" => "PELLETS"],
                            ["filter" => "COAL"],
                            ["filter" => "SCRAP"]
                        ]
                ],
                [ "filter" => "SEMI FINISHED",
                    "sub_filter" =>
                        [
                            ["filter" => "BILLET"],
                            ["filter" => "SPONGE IRON"],
                            ["filter" => "PIG IRON"]
                        ]
                ],
                [ "filter" => "FINISHED STEEL",
                    "sub_filter" =>
                        [
                            ["filter" => "FINISH LONG"],
                            ["filter" => "FINISH FLAT"]
                        ]
                ],
                [ "filter" => "FERRO ALLOYS",
                    "sub_filter" =>
                        [
                            ["filter" => "MnOre"],
                            ["filter" => "CrOre"],
                            ["filter" => "SiMn"],
                            ["filter" => "FeMn"],
                            ["filter" => "FeCr"],
                            ["filter" => "FeSi"],
                        ]
                ]
            ]
        ); 
 */
        $res[] = array( "filter" => "lead_type" ,
            "sub_filter" => [ ["filter" => "buy"],
                [ "filter" => "sell" ],
                [ "filter" => "services" ] ]
        ) ;
        $res[] = array( "filter" => "archive" ,
            "sub_filter" => [
                [ "filter" => "expired" ] ]
        );

        $res[] = array(
            "filter" => "date",
            "sub_filter" =>
                [
                    ["filter" => "Expiring in 5 days"],
                    ["filter" => "Expiring in 10 days"],
                    ["filter" => "Expiring in 15 days"]
                ]

        );


        return new JsonResponse(array ( "status#" => "1" , "data#" => $res ) );
    }
   public function getcategory_new(Request $request)
    {
        /* $tender = new Tender();
        $result = $tender->getCategory();
        if (count($result) > 0)
        {
            $response = array() ;
            foreach ($result as $category)
            {
                if ( empty($category['category']) )
                    continue;
                $response[]['cat_name'] = $category['category'] ;
            }
            return new JsonResponse( array( "status#" => "1" , "data#" => $response ) ) ;
        }
        return new JsonResponse( array( "status#" => "0" , "message#" => "No category found in database" ) ) ; */
		/* $response = '[{"cat_name": "IRON ORE"},{"cat_name": "COAL"},{"cat_name": "SCRAP"},{"cat_name": "SHIP BREAKING"},{"cat_name": "SEMI FINISHED"},{"cat_name": "FINISH LONG"},{"cat_name": "FERRO ALLOY"},{"cat_name":"FINISH FLAT"}]'; */
		
		$response = array(
		   "123422",
		   "SddsING",
		   "SEMI FINISHED",
		   "FINISH LONG",
		   "FERRO ALLOY",
		   "FINISH FLAT"
		);

		$data = array();
		foreach($response as $arr) {
			$data[] = array(
				'cat_name'    =>    $arr
			);
		}

		//echo json_encode(array("data#" => $data));		
		
		
		return new JsonResponse( array( "status#" => "1" , "data#" => $data ) ) ;
    }
    public function getcategory(Request $request)
    {
        /*$tender = new Tender();
        $result = $tender->getCategory();
        if (count($result) > 0)
        {
            $response = array() ;
            foreach ($result as $category)
            {
                if ( empty($category['category']) )
                    continue;
                $response[]['cat_name'] = $category['category'] ;
            }
            return new JsonResponse( array( "status#" => "1" , "data#" => $response ) ) ;
        }
        return new JsonResponse( array( "status#" => "0" , "message#" => "No category found in database" ) ) ;*/

        $path = storage_path() . "/json/constants/v2_action_tenders_category.json";
        $filter = json_decode(file_get_contents($path), true);

        return new JsonResponse($filter);
    }

	public function getcategoryByID(Request $request , $tender_id )
    {
		$GLOBALS['currDate'] = date("Y-m-d");
		$user = null ;
        $tender = new Tender();
        $posts = array();
		
		$appType='SM';
		if($request->has('appType') && strtoupper($request->input('appType')) == 'CM'){ 
			$appType=$request->input('appType');
		}
		
		if($request->has('token') || $request->has('memid') )
		{
			if($request->has('memid')){
				$memid = $request->input('memid');
				$memid = _encrypt_decrypt($action='decrypt', $memid);
				$auth = new authentication();
				$user_id = $auth->authUserId($memid);//for web user
			}else{
				$auth = new authentication();
				$token = $request->input('token');
				$user_id = $auth->authToken($token);
			}
			if ($user_id) {
				$user = new User($user_id, $appType);

			} else{
			 return new JsonResponse(array(
					"status#" => "403" ,
					"message#" => "Token unauthenticated"
				)) ;
			  //  abort(403, 'Unauthorized action. Token access denied');
			}
		}
	
       $query = "
				SELECT tt.ID,
					   tt.f_date,
					   tt.company_name,
					   tt.f_authorised,
					   concat(tt.qty,' ', tt.qty_unit) as qty,
					   tt.grade,
					   DATE_FORMAT(tt.opening_date,'%d %b %y') AS opening_date,
					   tt.opening_date AS od,
					   tt.closing_date,
					   DATE_FORMAT(tt.due_date,'%d %b %y') AS due_date,
					   tt.due_date AS dd,
					   tt.pdf_path,
					   replace(tt.sub_category, '_', ' ') sub_category,
					   replace(tt.sub_category_all, '_', ' ') sub_category_all,
					   replace(tt.category_all, '_', ' ') category_all,
					   tt.title,
					   tt.description,
					   tt.buy_sell,
					   tt.imgpath,
					   tt.offer_type,
					   ttr.state_name,
					   ttr.country_name,
					   CASE
						   WHEN tt.multi_loction = 1 THEN 'Multi Location'
						   WHEN ttr.country_name = 'INDIA' THEN concat(ttr.city_name,'/',ttr.state_name,'/',ttr.country_name)
						   ELSE concat(ttr.city_name, '/', ttr.country_name)
					   END AS region,
					   CASE
						   WHEN (tt.due_date <> '0000-00-00 00:00:00'
								 AND tt.due_date >= '$currDate') THEN 'Live'
						   ELSE 'Expired'
					   END AS live_expired,
					   tt.corrigendum,
					   DATE_FORMAT(tt.publish_date,'%d %b %y') AS publish_date ,
					   concat(tt.std_code, tt.mobile) AS phone_no
				FROM tbl_tenders AS tt
				LEFT JOIN tbl_tenders_region AS ttr ON tt.id = ttr.tender_id
				WHERE tt.ID = $tender_id
	   ";			
       $result = $tender->query($query) ;

        $posts = $this->result_parser($result, $user);

        // return the posts in the JSON format
        return new Response( array ( "status#" => "1" , "data#" => json_decode(html_entity_decode(json_encode($posts) ) )) );	   
	}		
	
 /* 
	public function getcategoryByID(Request $request , $tender_id )
    {
        $GLOBALS['currDate'] = date("Y-m-d");
        $currDate = $GLOBALS['currDate'];
        $user = null ;

        if ($request->has('token')) {
            $auth = new authentication();
            $token = $request->input('token');
            $user_id = $auth->authToken($token);
            if ($user_id) {
                $user = new User($user_id);

            } else
                abort(403, 'Unauthorized action. Token access denied');

        }

        $tender = new Tender();

        $posts = array();

        //echo $having;
        $query = "select tt.ID, tt.f_date, tt.company_name,
                  tt.qty,
                  tt.grade,
                  DATE_FORMAT(tt.opening_date,'%d %b %y') as opening_date,
                  tt.opening_date as od,
                  tt.closing_date,
                  DATE_FORMAT(tt.due_date,'%d %b %y') as due_date,
                  tt.due_date as dd,
                  tt.pdf_path,
                  replace(tt.sub_category, '_', ' ') sub_category,
                  replace(tt.sub_category_all, '_', ' ') sub_category_all,
                  replace(tt.category_all, '_', ' ') category_all,
                  tt.title,
                  tt.buy_sell,
                  tt.imgpath,
                  tt.offer_type,
                  ttr.state_name,
                  ttr.country_name,

                  tt.corrigendum,
                  DATE_FORMAT(tt.publish_date,'%d %b %y') as publish_date
				  ,tt.contact_person,					des.name as designation,					concat(tt.std_code, tt.mobile) as phone_no
                  from tbl_tenders as tt
                  left join
                  tbl_tenders_region as ttr

                  on tt.id = ttr.tender_id				  left join 				tbl_designation as des				on tt.designation = des.id

                  WHERE tt.ID = $tender_id
                  group by tt.id

                  order by tt.id desc

                  ";

        $result = $tender->query($query) ;

        $posts = $this->result_parser($result, $user);

        // return the posts in the JSON format
        return new Response( array ( "status#" => "1" , "data#" => json_decode(html_entity_decode(json_encode($posts) ) )) );
    } */

//    public function

    public function tenderEnquiry(Request $request )
    {
        $user_id = null ;
        $user = null ;
		
		$appType='SM';
		if($request->has('appType') && strtoupper($request->input('appType')) == 'CM'){ 
			$appType=$request->input('appType');
		}
		
        if ($request->has('token')) {
            $auth = new authentication();
            $token = $request->input('token');
            $user_id = $auth->authToken($token);
            if ($user_id) {
                $user = new User($user_id);

            } else
                abort(403, 'Unauthorized action. Token access denied');
        }

        if(
            $request->has('tender_id') &&
            $request->has('name') &&
            $request->has('email') &&
            $request->has('mobile') &&
            $request->has('country_id') &&
            $request->has('comments') )
        {
            $result = DB::table('tbl_tenders')
                ->select('f_authorised' , 'title' , 'contact_person', 'email as seller_email' )
                ->where('id' , '=' , $request->input('tender_id'))
                ->first();

            if($result['f_authorised'] == 'Y' && !empty($user_id) )
                abort(404, "Please login to send enquiry") ;

            $this->temp = $request->input('country_id') ;

            $data['offer'] = $request->input('comments');
            $data['title'] = $result['title'] ;
            $data['fname'] = '' ;
            $data['name'] = $request->input('name') ;
            $data['company'] = '' ;
			
			$location_out_of_list = '';
			if( $request->has('location_out_of_list') )
				$location_out_of_list = $request->input('location_out_of_list') ;

            if($request->has('company'))
                $data['company'] = $request->input('company');

            $data['email'] = $request->input('email');
            $data['mobile'] = $request->input('mobile');
            $data['c_id_code'] = DB::table('calling_codes')
                ->select('prefix as c_id_code')
                ->where('comment' , '=' , function($query) {
                    $query->select('country')
                        ->from('tbl_country')
                        ->where('id' , '=' , $this->temp ) ;
                })
                ->first()['c_id_code'] ;
			
			$data['country_id'] = '';
            $data['country_id'] = $request->input('country_id') ;

			$data['state_id'] = '' ;
            if($request->has('state_id'))
                $data['state_id'] = $request->input('state_id') ;

            $data['city_id'] = '' ;
            if($request->has('city_id'))
                $data['city_id'] = $request->input('city_id') ;

            $data['lead_id'] = $request->input('tender_id') ;
            $data['lead_type'] = '' ;
            $data['datetime'] = $GLOBALS['currDateTime'] ;
            $data['status'] = "pending";
            $data['verified'] = "no";
            $data['free_paid']='';

			$ipAddress = $_SERVER['REMOTE_ADDR'];
			if (array_key_exists('HTTP_X_FORWARDED_FOR', $_SERVER)) {
				$ipAddress = array_pop(explode(',', $_SERVER['HTTP_X_FORWARDED_FOR']));
			}

			if(empty($user_id) || $user_id == null) $user_id = 0;

            $table = array(
                "title" => $data["title"] ,
                "offer" => $data["offer"] ,
                "fname" => $data["fname"] ,
                "name" => $data["name"] ,
                "company" => $data["company"] ,
                "email" => $data["email"] ,
                "mobile" => $data["mobile"] ,
                "country_id" => $data["country_id"] ,
                "state_id" => $data["state_id"] ,
                "city_id" => $data["city_id"] ,
                "website" => "" ,
                "datetime" => $data["datetime"] ,
                "status" => $data["status"] ,
                "verified" => $data["verified"] ,
                "lead_id" => $data["lead_id"] ,
                "lead_type" => $data["lead_type"] ,
                "free_paid" => $data["free_paid"] ,
                "lead_for" => "tender",
				"ip"=> $ipAddress,
				"memid"=>$user_id,
				"location_out_of_list"=>$location_out_of_list
            );



            $sel_eml=$result['seller_email'] ;
            $contact_person=$result['contact_person'] ;

            $enquiry_id = DB::table('submit_enquiry')
                ->insert($table);

            $this->tender_enquiry($data , $sel_eml , $contact_person , 59 ) ;

            return new JsonResponse( array( "status#" => "1" )  ) ;
        }
        else{
            abort(403, 'parameter missing.');
            return false ;
        }

        return new JsonResponse( array( "status#" => "1101" , "message#" => "comments required to bid" )  ) ;

    }

    private function tender_enquiry( $data , $email, $contact_person , $id , $_admin_email = 'info@steelmint.com' )
    {
        $to=$email . ",tenders@steelmint.com,alok@steelmint.com";
        $subject='';
        $message='';
        $rep='';

        $contact_person=$contact_person;
        $title=$data['title'];
        $offer=$data['offer'];

        $name=$data['name'];
        $company_name=$data['company'];
        $RMemail=$data['email'];
        $RMmobile=$data['mobile'];

        /* if( $id==1002){
            $company_name="xxxxxxxxxxxx";
            $RMemail="xxxxxxxxxxxx";;
            $RMmobile="xxxxxxxxxxxx";;
        } */

        $type = $data['lead_type'];
        $tbl = 'leads';

        /// mail to OWNER OF LEAD

        $res=$res = DB::table('tbl_email_template')
            ->where('id' , '=' , $id )
            ->first() ;

        $subject=urlencode($res['sub_txt']);

        $message=$res['body_txt'];
        $rep=$res['to_be_replace'];
        $exp=explode(',',$rep);
        for($i=0;$i<count($exp);$i++){
            $find_str='%recipient.'.$exp[$i].'%';
            $message=str_replace($find_str,$$exp[$i],$message);
        }
		$message=str_replace('tender_id',$data['lead_id'],$message);
        //require_once("/home/steelmin/public_html/mf.php");
        //sendSMMail( $message , $to, $to_name="", $subject , $attachment='' , $from_email = $_admin_email );
		file_get_contents("https://www.steelmint.com/registrationAJAX.php?app_sendFeedbackmail=Y&message_con=" . urlencode($message) . "&to=$to&sub=$subject");
//        $this->sentinal->mail_to_user($to.','.$_admin_email,$subject,$message);

        //MAIL TO USER
        $res = DB::table('tbl_email_template')
            ->where('id' , '=' , 60 )
            ->first() ;

        $subject=urlencode($res['sub_txt']);
        $message=$res['body_txt'];
        $rep=$res['to_be_replace'];
        //$exp=explode(',',$rep);

        ///*
		$res = DB::table('tbl_tenders')
			->leftJoin('tbl_designation' , 'tbl_designation.id' , '=' , 'tbl_tenders.designation')
            ->select('contact_person' , 'tbl_designation.name as designation' , 'email' , 'mobile')
            ->where('tbl_tenders.id' , '=' , $data['lead_id'])
            ->first();
		//*/

		/*
		echo $data['lead_id'];
		$res = DB::table('leads')
            ->select('contact_person' , 'designation' , 'email_id' , 'mobile')
            ->where('id' , '=' , $data['lead_id'])
            ->first();
		*/
        $contact_person = $res['contact_person'] ;
        $designation = $res['designation'] ;
        $email_id = $res['email'] ;
        $mobile = $res['mobile'] ;

        //contact_person,title,offer,name,company_name,RMemail,RMmobile
        $message=str_replace('%recipient.name%',$data['name'],$message);
        $message=str_replace('%recipient.title%',$data['title'],$message);
        $message=str_replace('%recipient.offer%',$data['offer'],$message);

        $message=str_replace('%recipient.contact_person%',$contact_person,$message);
        $message=str_replace('%recipient.designation%',$designation,$message);
        $message=str_replace('%recipient.RMemail%',$email_id,$message);
        $message=str_replace('%recipient.RMmobile%',$mobile,$message);
		$message=str_replace('tender_id',$data['lead_id'],$message);
//        $this->sentinal->mail_to_user($to=$data['email'],$subject,$message);

        //require_once("/home/steelmin/public_html/mf.php");
        //sendSMMail($message, $data['email'] , $to_name="", $subject );
		file_get_contents("https://www.steelmint.com/registrationAJAX.php?app_sendFeedbackmail=Y&message_con=" . urlencode($message) . "&to=" . $data['email']  . "&sub=$subject");
    }
}