<?php

namespace App\Http\Controllers;

use DB;
// use App\Models\Nagad;
use App\Models\Orders;
use App\Models\Invoice;
use App\Models\InvoiceDetails;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use App\Library\SslCommerz\SslCommerzNotification;
use Closure;
use Illuminate\Support\Facades\URL;

class ApiController extends Controller
{
    public function get_connection_details(Request $request)
    {   
        $id = $request->CID;
        $AID = $request->AID;
        $APass = md5($request->APass);
        $a_user =DB::select('SELECT user FROM api_users WHERE user = "'.$AID.'" and password = "'.$APass.'" and status = "1" ');
        
        if(empty($a_user)){
            $data = DB::select('SELECT 401 as Code,"API User Not Found" as MSG ');
        }else{
            $ilc="'ILC-'";
            $il="'IL-'";
            
            if($AID == "test" && $id !='4705'){
                $data = DB::select('SELECT 404 as Code,"Client Not Access By This API User" as MSG ');
                echo json_encode($data);
                exit();
            }
            
            $client_id =DB::select('SELECT CONCAT('.$ilc.',client_id) as client_id,client_title FROM client WHERE client_id = '.$id.' ');
            
            if (empty($client_id)){
                $data = DB::select('SELECT 404 as Code,"Client Not Found" as MSG ');
            }
            else{
                
                $invoice_list =DB::select('SELECT GROUP_CONCAT(CONCAT('.$il.',invoice_details.invoice_id)) as invoice_list FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$id.' and invoice.status in (1,2,6) limit 1');
                
                $due = DB::select('SELECT sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))');
                $clientDD = $client_id[0]->client_id;
                $client_name = $client_id[0]->client_title;
                
                if($due[0]->due == null ){
                    $data = DB::select('SELECT 200 as Code,'.$id.' as ClientID,"'.$clientDD.'" as ClientNo,"'.$client_name.'" as ClientName, 0.0 as DueAmount, "'.$invoice_list[0]->invoice_list.'" as InvoiceList ');
                }else{
                    $data = DB::select('SELECT 200 as Code,'.$id.' as ClientID,"'.$clientDD.'" as ClientNo,"'.$client_name.'" as ClientName,CEILING(CAST(sum((price+ROUND((price*vat/100),2))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6)))-(SELECT COALESCE(sum(discount),0) FROM invoice WHERE client_id = '.$id.' and status in (1,2,6)) AS DECIMAL(36,2))*1.00) as DueAmount, "'.$invoice_list[0]->invoice_list.'" as InvoiceList FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))');
                }
            }
             
        }
        echo json_encode($data);
     }
     
    public function get_connection_details_v2(Request $request)
    {   
        $id = $request->CID;
        $AID = $request->AID;
        $APass = md5($request->APass);
        $a_user =DB::select('SELECT user FROM api_users WHERE user = "'.$AID.'" and password = "'.$APass.'" and status = "1" ');
        
        if(empty($a_user)){
            $data = DB::select('SELECT 401 as Code,"API User Not Found" as MSG ');
        }else{
            $ilc="'ILC-'";
            $il="'IL-'";
            
            if($AID == "test" && $id !='4705'){
                $data = DB::select('SELECT 404 as Code,"Client Not Access By This API User" as MSG ');
                echo json_encode($data);
                exit();
            }
            
            $client_id =DB::select('SELECT CONCAT('.$ilc.',client_id) as client_id,client_title FROM client WHERE client_id = '.$id.' ');
            
            if (empty($client_id)){
                $data = DB::select('SELECT 404 as Code,"Client Not Found" as MSG ');
            }
            else{
                
                $invoice_list =DB::select('SELECT GROUP_CONCAT(CONCAT('.$il.',invoice_details.invoice_id)) as invoice_list FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$id.' and invoice.status in (1,2,6) limit 1');
                $due = DB::select('SELECT sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))');
                $clientDD = $client_id[0]->client_id;
                $client_name = $client_id[0]->client_title;
                
                if($due[0]->due == null ){
                    $data = DB::select('SELECT 436 as Code,"Already Paid" as MSG,'.$id.' as ClientID,"'.$clientDD.'" as ClientNo,"'.$client_name.'" as ClientName, 0.0 as DueAmount ');
                }else{
                    $data = DB::select('SELECT 200 as Code,"Success" as MSG,'.$id.' as ClientID,"'.$clientDD.'" as ClientNo,"'.$client_name.'" as ClientName,CEILING(CAST(sum((price+ROUND((price*vat/100),2))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6)))-(SELECT COALESCE(sum(discount),0) FROM invoice WHERE client_id = '.$id.' and status in (1,2,6)) AS DECIMAL(36,2))*1.00) as DueAmount FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$id.' and status in (1,2,6))');
                }
            }
             
        }
        echo json_encode($data);
     }
     
    public function get_connection_details_amount(Request $request)
    {
        // $guest_id = $this->input->post("guest_id");
        // $this->request->CandidateFullName;
        $guest_id = $request->guest_id;
        // if($data = $this->Dashboard_model->specific_connection_guest($guest_id)) {
        //       echo json_encode($data);
        //  }
        $data['due'] = DB::select('SELECT sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$guest_id.' and status in (1,2,6))) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$guest_id.' and status in (1,2,6))');
        //print_r($due);
        $data['client_id'] =DB::select('SELECT * FROM client WHERE client_id = '.$guest_id.' ');
        $data['client_address'] =DB::select('SELECT * FROM client_address WHERE client_id = '.$guest_id.' ');
        $data['invoice_list'] =DB::select('SELECT invoice_details.invoice_id,sum((invoice_details.price+(invoice_details.price*invoice_details.vat/100))*invoice_details.quantity) as bill,(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id=invoice_details.invoice_id) as paid FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$guest_id.' and invoice.status in (1,2,6) group by invoice.invoice_id');
        //echo "Due: ".number_format($due[0]->due);
        echo json_encode($data);
     }

    public function exampleEasyCheckout()
    {
        return view('ssl/exampleEasycheckout');
    }

    public function exampleHostedCheckout()
    {
        return view('ssl/exampleHosted');
    }
    
    public function easycheckout()
    {
        return view('ssl/checkout_easy');
    }
    
    public function hostedcheckout()
    {
        return view('ssl/checkout_hosted');
    }

    public function index(Request $request)
    {
        # Here you have to receive all the order data to initate the payment.
        # Let's say, your oder transaction informations are saving in a table called "orders"
        # In "orders" table, order unique identity is "transaction_id". "status" field contain status of the transaction, "amount" is the order amount to be paid and "currency" is for storing Site Currency which will be checked with paid currency.
        
        $AID = $request->AID;
        $APass = md5($request->APass);
        $a_user =DB::select('SELECT user,account_id,payment_type FROM api_users WHERE user = "'.$AID.'" and password = "'.$APass.'" and status = "1" ');
        
        if(empty($a_user)){
            $data = DB::select('SELECT 401 as Code,"API User Not Found" as MSG ');
        }else{
            if($AID == "test" && $request->CID !='4705'){
                $data = DB::select('SELECT 404 as Code,"Client Not Access By This API User" as MSG ');
                echo json_encode($data);
                exit();
            }
            
            // echo $a_user[0]->account_id;
            // exit();
        $post_data = array();
        $post_data['total_amount'] = trim($request->Amount); # You cant not pay less than 10
        
        
        //$total_due =DB::select('SELECT ROUND(sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)))-(SELECT COALESCE(sum(discount),0) FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)),2) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6))');
        $total_due =DB::select('SELECT CEIL((sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)))-(SELECT COALESCE(sum(discount),0) FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)) )*1.00) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6))');
        $post_data['currency'] = "BDT";
        if($request->transaction_id){
            $post_data['tran_id'] = trim($request->transaction_id);
        }else{
            $post_data['tran_id'] = "API-".$AID."-ILC-".$request->CID."-".uniqid(); // tran_id must be unique
        }
        
        
        $client_id =DB::select('SELECT * FROM client WHERE client_id = '.$request->CID.' ');
        $client_address =DB::select('SELECT * FROM client_address WHERE client_id = '.$request->CID.' ');
        
        
        if(empty($client_id)){
            $data = DB::select('SELECT 404 as Code,"Client Not Found" as MSG ');
        }elseif($post_data['total_amount']<10){
            $data = DB::select('SELECT 405 as Code,"Amount is less than BDT 10" as MSG ');
        }elseif($post_data['total_amount'] != $total_due[0]->due){
            $data = DB::select('SELECT 406 as Code,"Amount Not Match With Due." as MSG ');
        }else{
        
        # CUSTOMER INFORMATION
        $post_data['cus_name'] = trim($client_id[0]->client_title);
        $post_data['cus_email'] = trim($client_id[0]->client_email1);
        $post_data['cus_add1'] = trim($client_address[0]->house);
        $post_data['cus_add2'] = trim($client_address[0]->post_office);;
        $post_data['cus_city'] = trim($client_address[0]->police_station);
        $post_data['cus_state'] = trim($client_address[0]->district);
        $post_data['cus_postcode'] = trim($client_address[0]->post_code);
        $post_data['cus_country'] = "Bangladesh";
        $post_data['cus_phone'] = trim($client_id[0]->client_phone1);
        $post_data['cus_fax'] = "";
        
        if($request->reason){
            $reason = trim($request->reason);
        }else{
            $reason = '';
        }
        if($request->CID){
            $CID = trim($request->CID);
        }else{
            $CID = 0;
        }
        if($request->invoice_id){
            $invoice_id = trim($request->invoice_id);
        }else{
            $invoice_id = 0;
        }
        if($request->PaymentStatus){
            $payment_status= trim($request->PaymentStatus);
        }else{
            $payment_status = 1;
        }
        
        # SHIPMENT INFORMATION
        $post_data['ship_name'] = trim($client_id[0]->client_title);
        $post_data['ship_add1'] = trim($client_address[0]->house);
        $post_data['ship_add2'] = "";
        $post_data['ship_city'] = trim($client_address[0]->police_station);
        $post_data['ship_state'] = trim($client_address[0]->district);
        $post_data['ship_postcode'] = trim($client_address[0]->post_code);
        $post_data['ship_phone'] = trim($client_id[0]->client_phone1);
        $post_data['ship_country'] = "Bangladesh";

        $post_data['shipping_method'] = "YES";
        $post_data['product_name'] = "internet";
        $post_data['product_category'] = "ISP";
        $post_data['product_profile'] = "internet-service";

        # OPTIONAL PARAMETERS
        $post_data['value_a'] = $reason;
		$post_data['value_b'] = $CID;
		$post_data['value_c'] = $invoice_id;
		$post_data['value_d'] = "ref004";
        
        
        /*if($this->input->post('reason')){
                $reason = trim($this->input->post('reason'));
            }else{
                $reason = '';
            }*/
        
        //payment_status 
        //1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
        /*$insert_sql = $this->db->query("INSERT INTO secure_payment (tran_id, transaction_date, name, email, phone, address,client_id,invoice_id,reason,type,payment_status,amount,currency,ip_address)
                                            VALUES ('$transaction_id','$trascation_date','$name', '$email','$phone','$address','$client_id','$invoice_id','$reason','1','1','$transaction_amount','$currency','$ip_address')");
        
        $data = array();
        $data['tran_id'] = $post_data['tran_id']; 
        $data['transaction_date'] = date('Y-m-d'); 
        $data['name'] = $post_data['cus_name']; 
        $data['email'] = $post_data['cus_email']; 
        $data['phone'] = $post_data['cus_phone']; 
        $data['address'] = $post_data['ship_add1'];
        $data['client_id'] = $client_id;
        $data['invoice_id'] = $invoice_id;
        $data['reason'] = $reason;
        $data['type'] = 1;
        $data['payment_status'] = 1;
        $data['amount'] = $transaction_amount;
        $data['currency'] = $currency;
        $data['ip_address'] = $ip_address;
        
        DB::table('orders')->insert($data);*/
        
        #Before  going to initiate the payment order status need to insert or update as Pending.
        $update_product = DB::table('orders')
            ->where('transaction_id', $post_data['tran_id'])
            ->updateOrInsert([
                'name' => $post_data['cus_name'],
                'email' => $post_data['cus_email'],
                'phone' => $post_data['cus_phone'],
                'amount' => $post_data['total_amount'],
                'status' => 'Pending',
                'address' => $post_data['cus_add1'],
                'transaction_id' => $post_data['tran_id'],
                'currency' => $post_data['currency']
            ]);
            
                    $ip_address = $_SERVER['REMOTE_ADDR'];
                    $trascation_date = date('Y-m-d');
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
                    
                    $secure_array = array(
                                'tran_id' => $post_data['tran_id'], 
                                'transaction_date' => $trascation_date, 
                                'name' => $post_data['cus_name'], 
                                'email' => $post_data['cus_email'], 
                                'phone' => $post_data['cus_phone'], 
                                'address' => $post_data['cus_add1'], 
                                'client_id' => $CID, 
                                'invoice_id' => $invoice_id, 
                                'bill_period' => $reason, 
                                'reason' => $reason, 
                                'type' => 2,
                                'payment_status' => 1,
                                'amount' => $post_data['total_amount'],
                                'currency' => $post_data['currency'],
                                'ip_address' => $ip_address,
                                'api_user' => $AID
                        );
                    $insert_sql = DB::table('secure_payment')->insert($secure_array);

        // $sslc = new SslCommerzNotification();
        // # initiate(Transaction Data , false: Redirect to SSLCOMMERZ gateway/ true: Show all the Payement gateway here )
        // $payment_options = $sslc->makePayment($post_data, 'hosted');

        // if (!is_array($payment_options)) {
        //     print_r($payment_options);
        //     $payment_options = array();
        // }
        
        //echo json_encode($post_data);
        if($payment_status == 2){
                
                $bank_tran_id = $request->bank_tran_id;
                $base_fair = trim($request->Amount);
                $card_brand = $request->card_brand;
                $card_issuer = $request->card_issuer;
                $card_issuer_country = $request->card_issuer_country;
                $card_issuer_country_code = $request->card_issuer_country_code;
                $card_no = $request->card_no;
                $card_sub_brand = $request->card_sub_brand;
                $card_type = $request->card_type;
                $currency = "BDT";
                $currency_amount = trim($request->Amount);
                $currency_rate = "1";
                $currency_type = "BDT";
                $store_amount = $request->TransferAmount;
                
                 $update_data = array(
                        'payment_status' => 2,
                        'bank_tran_id'=>$bank_tran_id,
                        'card_brand'=>$card_brand,
                        'card_issuer'=>$card_issuer,
                        'card_issuer_country'=>$card_issuer_country,
                        'card_issuer_country_code'=>$card_issuer_country_code,
                        'card_no'=>$card_no,
                        'card_sub_brand'=>$card_sub_brand,
                        'card_type'=>$card_type,
                        'store_amount'=>$store_amount,
                        'currency'=>$currency,
                        'currency_type' =>$currency_type,
                        'currency_amount'=>$currency_amount,
                        'currency_rate'=>$currency_rate,
                        'base_fair'=>$base_fair,
                        'updated_by' => 1
                    );
                $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
                if($update){
                    //Payment table update process
                    
                    $row = DB::table('secure_payment')->where('tran_id', $post_data['tran_id'])->first();
                    
                    $invoice_list =DB::select('SELECT invoice_details.invoice_id as inv_id,sum((invoice_details.price+(invoice_details.price*invoice_details.vat/100))*invoice_details.quantity) as bill,(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id=invoice_details.invoice_id) as paid, COALESCE(sum(discount),0) as discount FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$row->client_id.' and invoice.status in (1,2,6) group by invoice.invoice_id');
        
                    $check_payment = DB::table('payment')->where("invoice_id",$row->invoice_id)->first();
        			if(empty($row->client_id)){
        				//echo '<b style="color:red;">Invoice Number [IL-'.$row->invoice_id.'] is already paid.</b><br/>';
        			}else{
        			    
        			    //for($i = 0; $i < $invoice_list.length; $i++){
        			    foreach($invoice_list as $inv_list){
            				$array = array(
            					'invoice_id'				 => $inv_list->inv_id,
            					'payment_date'				 => date("Y-m-d"),
            					'payment_type'				 => $a_user[0]->payment_type,
            					'amount'				 	 => 0.0+$inv_list->bill-$inv_list->paid-$inv_list->discount,
            					'client_id'				 	 => $row->client_id,
            					'reference_number'			 => $row->reason,
            					'status'				 	 => 1,
            					'status_clear_date'			 => date('Y-m-d'),
            					'accept_by' 				 => 0,
            					'admin_id'   				 => 0,
            					'name'				 	     => $row->name,
            					'email'				 	     => $row->email,
            					'phone'				 	     => $row->phone,
            					'remarks' 				     => 'Online Payment - '.$AID,
            					'transaction_id' 		     => $row->tran_id,
            					'account_id' 		     	 => $a_user[0]->account_id,
            					'link'           		     => 'Email',
            					'payment_type_title'         => $row->card_brand,
            					'card_title'                 => $row->card_type,
            					'card_first_six_digit'       => $row->card_no,
            					'card_last_four_digit'       => '',
            					'card_holder_name'           => '',
            					'charge'                     => ''
            				);
            				
            				$bill_amount = 0.0+$inv_list->bill-$inv_list->paid-$inv_list->discount;
            				$payment_id = DB::table('payment')->insertGetId($array);
            				
            				$invoice_status = array('status'=>3);
            				
            				DB::table('invoice')->where('invoice_id',$inv_list->inv_id)->update($invoice_status);
            				
            				//prepaid update 
                            $invoice_frequency = DB::table('invoice')->where('invoice_id',$inv_list->inv_id)->first();
                    		if($invoice_frequency->frequency == 'p1'){
                    		    //echo "yes";
                    		    $invoice_Recurring = DB::table('invoice_recurring')->where('invoice_id',$invoice_frequency->invoice_recurring_id)->first();
                    		    if($invoice_Recurring != NULL){
                    		        $upcomming_date = date('Y-m-d', strtotime("+30 days"));
                                    $update_date = array(
                                            'starting_date' =>$upcomming_date
                                        );
                                    $updated = DB::table('invoice_recurring')->where('invoice_id',$invoice_frequency->invoice_recurring_id)->update($update_date);
                    		    }
                    		    
                    		}
                    		
                    		
                    		// sms send
                    		
                    		$sms = DB::table('sms_template')
                            ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                            ->select('sms_settings.link','sms_template.*')
                            ->where('sms_template.id',6)
                            ->first();
            		
            		        $gsm = $row->phone;
            		        
            		        $p_type = 'Online Payment';
            		        
            		        $n1 = str_replace('[[client_title]]',$row->name,$sms->message);
            		
                    		$n2 = str_replace('[[client_id]]',$row->client_id,$n1);
                    		
                    		$n3 = str_replace('[[payment_amount]]',$bill_amount,$n2);
                    		
                    		$n4 = str_replace('[[payment_type]]',$p_type,$n3);
                    		
                    		$encoded_message = urlencode($n4);
                    		
                    		$m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
            
            				$api = str_replace('[[gsm]]',"$gsm",$m1);
                    		
                    		// create a new cURL resource
                    		$ch = curl_init();
                    		curl_setopt($ch, CURLOPT_URL, $api);
                    		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                    		curl_exec($ch);
                    		curl_close($ch);
                    		//SMS END 
            		
            		
                    		
                    		/** Auto reconnect functionality start here */
                $client_id = $request->CID;
                if($client_id){
        	        $total_due_invoice = 0;
        	       // $bill_summery = $this->list_due_payment($client_id);
        	       // if(!empty($bill_summery)){
                // 		foreach($bill_summery as $value){
                // 			if($value["record_type"]=="Invoice"){
                // 				$total_due_invoice +=$value["record_bill"];
                // 			}else{
                // 				$total_due_invoice = 0.00;
                // 			}
                // 		}
        	       // }
        	        
        	        //Check connection exits in billing automation list
        	        $automation_data = DB::select("SELECT b.automation_id, b.admin_id, b.client_id,b.connection_id,b.nat_add_time,b.status,c.connection_id, c.auto_disconnect,c.device_id, c.consider_amount,c.limit_type,c.limit_number 
                                    	        FROM billing_automation as b INNER JOIN connection_list as c ON b.connection_id = c.connection_id 
                                    	        WHERE b.status = 1 AND b.client_id = $client_id AND c.auto_disconnect = 1");
                                    	        
        	        if(!empty($automation_data)){
        	            
        	            include(app_path() . '/NatApi/routeros_api.class.php');
        				    
                		$API = new \RouterosAPI();
                
                		$API->debug = true;
        	            
        	            foreach($automation_data as $lists){
                					
        					if($lists->limit_type == 1){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number days"));
        					}elseif($lists->limit_type == 2){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number months"));
        					}elseif($lists->limit_type == 3){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number years"));
        					}
        					
        					
        					$consider_date_check = DB::select("SELECT invoice_id,invoice_created_date,status FROM invoice WHERE DATE(invoice_created_date) < '".$newdate."' AND status ='1' AND client_id='".$lists->client_id."'");
        				
        					$date_count = count($consider_date_check);
        			
        					if(!empty($total_due_invoice)){	
        						if($total_due_invoice > $lists->consider_amount AND $date_count > 0){
        							//return true();
        						}else{
        						    $connection_id = $lists->connection_id;
        			
        							//Remove NAT
        							$ip_result = DB::table('connection_ip')->where("connection_id",$connection_id)->get();
        							if(!empty($ip_result)){
        								foreach($ip_result as $value){
        									$device = DB::table('device_settings')->where('id',$lists->device_id)->first();
                            				if($device != NULL){
                            				    if ($API->connect($device->device_ip, $device->username, $device->password)) {
                            				        $API->write('/ip/firewall/address-list/print', false);
                            						$API->write('?address='.$value->ip_address, false);
                            						$API->write('=.proplist=.id');
                            						$ARRAYS = $API->read();
                            						if($ARRAYS){
                            						   DB::table('billing_automation')->where('connection_id',$connection_id)->delete();
                            						}
                            						$API->write('/ip/firewall/address-list/remove', false);
                            						$API->write('=.id=' . $ARRAYS[0]['.id']);
                            						$READ = $API->read();
                            				    }
                            				}
        								}
        								
                                       
        							}
        						}
        				    }else{
        				        $connection_id = $lists->connection_id;
        			
        							//Remove NAT
        							$ip_result = DB::table('connection_ip')->where("connection_id",$connection_id)->get();
        							if(!empty($ip_result)){
        								foreach($ip_result as $value){
        									$device = DB::table('device_settings')->where('id',$lists->device_id)->first();
                            				if($device != NULL){
                            				    if ($API->connect($device->device_ip, $device->username, $device->password)) {
                            				        $API->write('/ip/firewall/address-list/print', false);
                            						$API->write('?address='.$value->ip_address, false);
                            						$API->write('=.proplist=.id');
                            						$ARRAYS = $API->read();
                            						if($ARRAYS){
                            						   DB::table('billing_automation')->where('connection_id',$connection_id)->delete();
                            						}
                            						$API->write('/ip/firewall/address-list/remove', false);
                            						$API->write('=.id=' . $ARRAYS[0]['.id']);
                            						$READ = $API->read();
                            				    }
                            				}
        								}
        								
        								
                                       
        							}
        				    }
        			    }
        	        }
        	    }
        	    
        	    /** Auto reconnect functionality start here */
        	    
        		
            				$check = DB::table('invoice_vat_serial')->where('invoice_id',$inv_list->inv_id)->first();
            				if($check){
            				    
            				}else{
            				    
            				    $branch_query = DB::table('client')->select('branch_id')->where('client_id', $client_id)->first();
    				    if($branch_query){
    				        $branch_id = $branch_query->branch_id;
    				        $branch_vat_check = DB::table('branch')->select('vat_print')->where('branch_id', $branch_id)->first();
    				        if($branch_vat_check){
    				            $vat_status = $branch_vat_check->vat_print;
    				            if($vat_status == 1){
            				    
        						$vat_serial = array(
        							'invoice_id' => $inv_list->inv_id,
        							'admin_id' => 13
        						);
        					   DB::table('invoice_vat_serial')->insert($vat_serial);
    				            }
    				        }
    				    }
            				}
                        }
        			}
                    
                }
                $data = DB::select('SELECT 200 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully." as MSG ');
        }elseif($payment_status == 3){
            $update_data = array(
                    'payment_status' => 3,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
            
            $data = DB::select('SELECT 201 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but failed." as MSG ');
        }elseif($payment_status == 4){
            $update_data = array(
                    'payment_status' => 4,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
            $data = DB::select('SELECT 202 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but canceled." as MSG ');
        }elseif($payment_status == 1){
            
            $data = DB::select('SELECT 203 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully with pending." as MSG ');
        }else{
            $data = DB::select('SELECT 204 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but payment status not found." as MSG ');
        }
            
        
        }
        }
        echo json_encode($data);

    }
    
    public function index_v2(Request $request)
    {
        
        $AID = $request->AID;
        $APass = md5($request->APass);
        $a_user =DB::select('SELECT user,account_id,payment_type FROM api_users WHERE user = "'.$AID.'" and password = "'.$APass.'" and status = "1" ');
        
        if(empty($a_user)){
            $data = DB::select('SELECT 401 as Code,"API User Not Found" as MSG ');
        }else{
            if($AID == "test" && $request->CID !='4705'){
                $data = DB::select('SELECT 404 as Code,"Client Not Access By This API User" as MSG ');
                echo json_encode($data);
                exit();
            }
        $post_data = array();
        $post_data['total_amount'] = trim($request->Amount); # You cant not pay less than 10
        
        
        $total_due =DB::select('SELECT CEIL((sum((price+(price*vat/100))*quantity)-(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id in (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)))-(SELECT COALESCE(sum(discount),0) FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6)) )*1.00) as due FROM invoice_details WHERE invoice_id IN (SELECT invoice_id FROM invoice WHERE client_id = '.$request->CID.' and status in (1,2,6))');
        $post_data['currency'] = "BDT";
        if($request->transaction_id){
            $post_data['tran_id'] = trim($request->transaction_id);
        }else{
            $post_data['tran_id'] = "API-".$AID."-ILC-".$request->CID."-".uniqid(); // tran_id must be unique
        }
        
        
        $client_id =DB::select('SELECT * FROM client WHERE client_id = '.$request->CID.' ');
        $client_address =DB::select('SELECT * FROM client_address WHERE client_id = '.$request->CID.' ');
        
        
        if(empty($client_id)){
            $data = DB::select('SELECT 404 as Code,"Client Not Found" as MSG ');
        }elseif($post_data['total_amount']<10){
            $data = DB::select('SELECT 405 as Code,"Amount is less than BDT 10" as MSG ');
        }elseif($total_due[0]->due == 0){
            $data = DB::select('SELECT 436 as Code,"Already Paid." as MSG ');
        }elseif($post_data['total_amount'] != $total_due[0]->due){
            $data = DB::select('SELECT 406 as Code,"Amount Not Match With Due." as MSG ');
        }else{
        
        # CUSTOMER INFORMATION
        $post_data['cus_name'] = trim($client_id[0]->client_title);
        $post_data['cus_email'] = trim($client_id[0]->client_email1);
        $post_data['cus_add1'] = trim($client_address[0]->house);
        $post_data['cus_add2'] = trim($client_address[0]->post_office);;
        $post_data['cus_city'] = trim($client_address[0]->police_station);
        $post_data['cus_state'] = trim($client_address[0]->district);
        $post_data['cus_postcode'] = trim($client_address[0]->post_code);
        $post_data['cus_country'] = "Bangladesh";
        $post_data['cus_phone'] = trim($client_id[0]->client_phone1);
        $post_data['cus_fax'] = "";
        
        if($request->reason){
            $reason = trim($request->reason);
        }else{
            $reason = '';
        }
        if($request->CID){
            $CID = trim($request->CID);
        }else{
            $CID = 0;
        }
        if($request->invoice_id){
            $invoice_id = trim($request->invoice_id);
        }else{
            $invoice_id = 0;
        }
        if($request->PaymentStatus){
            $payment_status= trim($request->PaymentStatus);
        }else{
            $payment_status = 1;
        }
        
        # SHIPMENT INFORMATION
        $post_data['ship_name'] = trim($client_id[0]->client_title);
        $post_data['ship_add1'] = trim($client_address[0]->house);
        $post_data['ship_add2'] = "";
        $post_data['ship_city'] = trim($client_address[0]->police_station);
        $post_data['ship_state'] = trim($client_address[0]->district);
        $post_data['ship_postcode'] = trim($client_address[0]->post_code);
        $post_data['ship_phone'] = trim($client_id[0]->client_phone1);
        $post_data['ship_country'] = "Bangladesh";

        $post_data['shipping_method'] = "YES";
        $post_data['product_name'] = "internet";
        $post_data['product_category'] = "ISP";
        $post_data['product_profile'] = "internet-service";

        # OPTIONAL PARAMETERS
        $post_data['value_a'] = $reason;
		$post_data['value_b'] = $CID;
		$post_data['value_c'] = $invoice_id;
		$post_data['value_d'] = "ref004";
        
        
        
        $update_product = DB::table('orders')
            ->where('transaction_id', $post_data['tran_id'])
            ->updateOrInsert([
                'name' => $post_data['cus_name'],
                'email' => $post_data['cus_email'],
                'phone' => $post_data['cus_phone'],
                'amount' => $post_data['total_amount'],
                'status' => 'Pending',
                'address' => $post_data['cus_add1'],
                'transaction_id' => $post_data['tran_id'],
                'currency' => $post_data['currency']
            ]);
            
                    $ip_address = $_SERVER['REMOTE_ADDR'];
                    $trascation_date = date('Y-m-d');
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
                    
                    $secure_array = array(
                                'tran_id' => $post_data['tran_id'], 
                                'transaction_date' => $trascation_date, 
                                'name' => $post_data['cus_name'], 
                                'email' => $post_data['cus_email'], 
                                'phone' => $post_data['cus_phone'], 
                                'address' => $post_data['cus_add1'], 
                                'client_id' => $CID, 
                                'invoice_id' => $invoice_id, 
                                'bill_period' => $reason, 
                                'reason' => $reason, 
                                'type' => 2,
                                'payment_status' => 1,
                                'amount' => $post_data['total_amount'],
                                'currency' => $post_data['currency'],
                                'ip_address' => $ip_address,
                                'api_user' => $AID
                        );
                    $insert_sql = DB::table('secure_payment')->insert($secure_array);

      
        
        //echo json_encode($post_data);
        if($payment_status == 2){
                
                $bank_tran_id = $request->bank_tran_id;
                $base_fair = trim($request->Amount);
                $card_brand = $request->card_brand;
                $card_issuer = $request->card_issuer;
                $card_issuer_country = $request->card_issuer_country;
                $card_issuer_country_code = $request->card_issuer_country_code;
                $card_no = $request->card_no;
                $card_sub_brand = $request->card_sub_brand;
                $card_type = $request->card_type;
                $currency = "BDT";
                $currency_amount = trim($request->Amount);
                $currency_rate = "1";
                $currency_type = "BDT";
                $store_amount = $request->TransferAmount;
                
                 $update_data = array(
                        'payment_status' => 2,
                        'bank_tran_id'=>$bank_tran_id,
                        'card_brand'=>$card_brand,
                        'card_issuer'=>$card_issuer,
                        'card_issuer_country'=>$card_issuer_country,
                        'card_issuer_country_code'=>$card_issuer_country_code,
                        'card_no'=>$card_no,
                        'card_sub_brand'=>$card_sub_brand,
                        'card_type'=>$card_type,
                        'store_amount'=>$store_amount,
                        'currency'=>$currency,
                        'currency_type' =>$currency_type,
                        'currency_amount'=>$currency_amount,
                        'currency_rate'=>$currency_rate,
                        'base_fair'=>$base_fair,
                        'updated_by' => 1
                    );
                $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
                if($update){
                    //Payment table update process
                    
                    $row = DB::table('secure_payment')->where('tran_id', $post_data['tran_id'])->first();
                    
                    $invoice_list =DB::select('SELECT invoice_details.invoice_id as inv_id,sum((invoice_details.price+(invoice_details.price*invoice_details.vat/100))*invoice_details.quantity) as bill,(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id=invoice_details.invoice_id) as paid, COALESCE(sum(discount),0) as discount FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$row->client_id.' and invoice.status in (1,2,6) group by invoice.invoice_id');
        
                    $check_payment = DB::table('payment')->where("invoice_id",$row->invoice_id)->first();
        			if(empty($row->client_id)){
        				//echo '<b style="color:red;">Invoice Number [IL-'.$row->invoice_id.'] is already paid.</b><br/>';
        			}else{
        			    
        			    //for($i = 0; $i < $invoice_list.length; $i++){
        			    foreach($invoice_list as $inv_list){
            				$array = array(
            					'invoice_id'				 => $inv_list->inv_id,
            					'payment_date'				 => date("Y-m-d"),
            					'payment_type'				 => $a_user[0]->payment_type,
            					'amount'				 	 => 0.0+$inv_list->bill-$inv_list->paid-$inv_list->discount,
            					'client_id'				 	 => $row->client_id,
            					'reference_number'			 => $row->reason,
            					'status'				 	 => 1,
            					'status_clear_date'			 => date('Y-m-d'),
            					'accept_by' 				 => 0,
            					'admin_id'   				 => 0,
            					'name'				 	     => $row->name,
            					'email'				 	     => $row->email,
            					'phone'				 	     => $row->phone,
            					'remarks' 				     => 'Online Payment - '.$AID,
            					'transaction_id' 		     => $row->tran_id,
            					'account_id' 		     	 => $a_user[0]->account_id,
            					'link'           		     => 'Email',
            					'payment_type_title'         => $row->card_brand,
            					'card_title'                 => $row->card_type,
            					'card_first_six_digit'       => $row->card_no,
            					'card_last_four_digit'       => '',
            					'card_holder_name'           => '',
            					'charge'                     => ''
            				);
            				$bill_amount = 0.0+$inv_list->bill-$inv_list->paid-$inv_list->discount;
            				$payment_id = DB::table('payment')->insertGetId($array);
            				
            				$invoice_status = array('status'=>3);
            				
            				DB::table('invoice')->where('invoice_id',$inv_list->inv_id)->update($invoice_status);
            				
            				//prepaid update 
                            $invoice_frequency = DB::table('invoice')->where('invoice_id',$inv_list->inv_id)->first();
                    		if($invoice_frequency->frequency == 'p1'){
                    		    //echo "yes";
                    		    $invoice_Recurring = DB::table('invoice_recurring')->where('invoice_id',$invoice_frequency->invoice_recurring_id)->first();
                    		    if($invoice_Recurring != NULL){
                    		        $upcomming_date = date('Y-m-d', strtotime("+30 days"));
                                    $update_date = array(
                                            'starting_date' =>$upcomming_date
                                        );
                                    $updated = DB::table('invoice_recurring')->where('invoice_id',$invoice_frequency->invoice_recurring_id)->update($update_date);
                    		    }
                    		    
                    		}
                    		
                    		// sms send
                    		
                    		$sms = DB::table('sms_template')
                            ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                            ->select('sms_settings.link','sms_template.*')
                            ->where('sms_template.id',6)
                            ->first();
            		
            		        $gsm = $row->phone;
            		        
            		        $p_type = 'Online Payment';
            		        
            		        $n1 = str_replace('[[client_title]]',$row->name,$sms->message);
            		
                    		$n2 = str_replace('[[client_id]]',$row->client_id,$n1);
                    		
                    		$n3 = str_replace('[[payment_amount]]',$bill_amount,$n2);
                    		
                    		$n4 = str_replace('[[payment_type]]',$p_type,$n3);
                    		
                    		$encoded_message = urlencode($n4);
                    		
                    		$m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
            
            				$api = str_replace('[[gsm]]',"$gsm",$m1);
                    		
                    		// create a new cURL resource
                    		$ch = curl_init();
                    		curl_setopt($ch, CURLOPT_URL, $api);
                    		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                    		curl_exec($ch);
                    		curl_close($ch);
                    		//SMS END 
                    		
                    		/** Auto reconnect functionality start here */
                $client_id = $request->CID;
                if($client_id){
        	        $total_due_invoice = 0;
        	       // $bill_summery = $this->list_due_payment($client_id);
        	       // if(!empty($bill_summery)){
                // 		foreach($bill_summery as $value){
                // 			if($value["record_type"]=="Invoice"){
                // 				$total_due_invoice +=$value["record_bill"];
                // 			}else{
                // 				$total_due_invoice = 0.00;
                // 			}
                // 		}
        	       // }
        	        
        	        //Check connection exits in billing automation list
        	        $automation_data = DB::select("SELECT b.automation_id, b.admin_id, b.client_id,b.connection_id,b.nat_add_time,b.status,c.connection_id, c.auto_disconnect,c.device_id, c.consider_amount,c.limit_type,c.limit_number 
                                    	        FROM billing_automation as b INNER JOIN connection_list as c ON b.connection_id = c.connection_id 
                                    	        WHERE b.status = 1 AND b.client_id = $client_id AND c.auto_disconnect = 1");
                                    	        
        	        if(!empty($automation_data)){
        	            
        	            include(app_path() . '/NatApi/routeros_api.class.php');
        				    
                		$API = new \RouterosAPI();
                
                		$API->debug = true;
        	            
        	            foreach($automation_data as $lists){
                					
        					if($lists->limit_type == 1){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number days"));
        					}elseif($lists->limit_type == 2){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number months"));
        					}elseif($lists->limit_type == 3){
        						$newdate = date("Y-m-d", strtotime("-$lists->limit_number years"));
        					}
        					
        					
        					$consider_date_check = DB::select("SELECT invoice_id,invoice_created_date,status FROM invoice WHERE DATE(invoice_created_date) < '".$newdate."' AND status ='1' AND client_id='".$lists->client_id."'");
        				
        					$date_count = count($consider_date_check);
        			
        					if(!empty($total_due_invoice)){	
        						if($total_due_invoice > $lists->consider_amount AND $date_count > 0){
        							//return true();
        						}else{
        						    $connection_id = $lists->connection_id;
        			
        							//Remove NAT
        							$ip_result = DB::table('connection_ip')->where("connection_id",$connection_id)->get();
        							if(!empty($ip_result)){
        								foreach($ip_result as $value){
        									$device = DB::table('device_settings')->where('id',$lists->device_id)->first();
                            				if($device != NULL){
                            				    if ($API->connect($device->device_ip, $device->username, $device->password)) {
                            				        $API->write('/ip/firewall/address-list/print', false);
                            						$API->write('?address='.$value->ip_address, false);
                            						$API->write('=.proplist=.id');
                            						$ARRAYS = $API->read();
                            						if($ARRAYS){
                            						   DB::table('billing_automation')->where('connection_id',$connection_id)->delete();
                            						}
                            						$API->write('/ip/firewall/address-list/remove', false);
                            						$API->write('=.id=' . $ARRAYS[0]['.id']);
                            						$READ = $API->read();
                            				    }
                            				}
        								}
        								
                                       
        							}
        						}
        				    }else{
        				        $connection_id = $lists->connection_id;
        			
        							//Remove NAT
        							$ip_result = DB::table('connection_ip')->where("connection_id",$connection_id)->get();
        							if(!empty($ip_result)){
        								foreach($ip_result as $value){
        									$device = DB::table('device_settings')->where('id',$lists->device_id)->first();
                            				if($device != NULL){
                            				    if ($API->connect($device->device_ip, $device->username, $device->password)) {
                            				        $API->write('/ip/firewall/address-list/print', false);
                            						$API->write('?address='.$value->ip_address, false);
                            						$API->write('=.proplist=.id');
                            						$ARRAYS = $API->read();
                            						if($ARRAYS){
                            						   DB::table('billing_automation')->where('connection_id',$connection_id)->delete();
                            						}
                            						$API->write('/ip/firewall/address-list/remove', false);
                            						$API->write('=.id=' . $ARRAYS[0]['.id']);
                            						$READ = $API->read();
                            				    }
                            				}
        								}
        								
        								
                                       
        							}
        				    }
        			    }
        	        }
        	    }
        	    
        	    /** Auto reconnect functionality start here */
                    		
            				$check = DB::table('invoice_vat_serial')->where('invoice_id',$inv_list->inv_id)->first();
            				if($check){
            				    
            				}else{
            				    $branch_query = DB::table('client')->select('branch_id')->where('client_id', $client_id)->first();
    				    if($branch_query){
    				        $branch_id = $branch_query->branch_id;
    				        $branch_vat_check = DB::table('branch')->select('vat_print')->where('branch_id', $branch_id)->first();
    				        if($branch_vat_check){
    				            $vat_status = $branch_vat_check->vat_print;
    				            if($vat_status == 1){
    				                
        						$vat_serial = array(
        							'invoice_id' => $inv_list->inv_id,
        							'admin_id' => 13
        						);
        					   DB::table('invoice_vat_serial')->insert($vat_serial);
    				            }
    				        }
    				    }
            				}
                        }
        			}
                    
                }
                $data = DB::select('SELECT 200 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully." as MSG ');
        }elseif($payment_status == 3){
            $update_data = array(
                    'payment_status' => 3,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
            
            $data = DB::select('SELECT 201 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but failed." as MSG ');
        }elseif($payment_status == 4){
            $update_data = array(
                    'payment_status' => 4,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$post_data['tran_id'])->update($update_data);
            $data = DB::select('SELECT 202 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but canceled." as MSG ');
        }elseif($payment_status == 1){
            
            $data = DB::select('SELECT 203 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully with pending." as MSG ');
        }else{
            $data = DB::select('SELECT 204 as Code,"'.$post_data['tran_id'].'" as TransactionID, "Transaction Posted Successfully but payment status not found." as MSG ');
        }
            
        
        }
        }
        echo json_encode($data);

    }
    
    // Why? - Sakif Ahmed
    // for test api reset? - zayed
    public function reset()
    {
        $update_data = array(
                        'status' => 1,
                    );
        $update = DB::table('invoice')->where('client_id', 4705)->where('status',3)->update($update_data);
        $update = DB::table('invoice')->where('client_id', 5778)->where('status',3)->update($update_data);
        $update = DB::table('invoice')->where('client_id', 4894)->where('status',3)->update($update_data);
        //$update = DB::table('invoice')->where('client_id', 4384)->where('status',0)->update($update_data);
        
        $delate = DB::table('payment')->where('client_id', 4705)->delete();
        $delate = DB::table('payment')->where('client_id', 5778)->delete();
        $delate = DB::table('payment')->where('client_id', 4894)->delete();
        //$delate = DB::table('payment')->where('client_id', 4384)->delete();
     }
    
    public function transaction_check(Request $request)
    {
        
        $id = $request->CID;
        $AID = $request->AID;
        $APass = md5($request->APass);
        $TransactionID = $request->TransactionID;
        $a_user =DB::select('SELECT user FROM api_users WHERE user = "'.$AID.'" and password = "'.$APass.'" and status = "1" ');
        
        if(empty($a_user)){
            $data = DB::select('SELECT 401 as Code,"API User Not Found" as MSG ');
        }else{
            if($AID == "test" && $request->CID !='4705'){
                $data = DB::select('SELECT 404 as Code,"Client Not Access By This API User" as MSG ');
                echo json_encode($data);
                exit();
            }
            $ilc="'ILC-'";
            $il="'IL-'";
            $client_id =DB::select('SELECT CONCAT('.$ilc.',client_id) as client_id,client_title FROM client WHERE client_id = '.$id.' ');
            
            if (empty($client_id)){
                $data = DB::select('SELECT 404 as Code,"Client Not Found" as MSG ');
            }
            else{
                
                $row =DB::select('SELECT * FROM secure_payment WHERE client_id = '.$id.' and tran_id = "'.$TransactionID.'" ');
                // echo $row;
                // exit();
                
                if(empty($row)){
                    $data = DB::select('SELECT 403 as Code,"Not Found" as Status,"Transaction Not Found." as MSG ');
                }
                elseif($row[0]->payment_status == 2 ){
                    $data = DB::select('SELECT 200 as Code,"SUCCESS" as Status,"Transaction Found Successfully." as MSG ');
                }elseif($row[0]->payment_status == 3 ){
                    $data = DB::select('SELECT 201 as Code,"FAILED" as Status,"Transaction Found Successfully but failed." as MSG ');
                }elseif($row[0]->payment_status == 4 ){
                    $data = DB::select('SELECT 202 as Code,"CANCELLED" as Status,"Transaction Found Successfully but canceled." as MSG ');
                }else{
                    $data = DB::select('SELECT 203 as Code,"PENDING" as Status,"Transaction Found Successfully but pending." as MSG ');
                }
                
                //$data['client_address'] =DB::select('SELECT * FROM client_address WHERE client_id = '.$id.' ');
            }
            
             
        }
        echo json_encode($data);
     }
    
    public function paynow($i_id = NULL)
    {
        $invoice_id = base64_decode($i_id);
        //echo $invoice_id;die;
		if($invoice_id)
		{
		    $invoice_information = DB::table('invoice')
                            ->join('client', 'invoice.client_id', '=', 'client.client_id')
                            ->select('client.client_id','client.client_title', 'client.client_phone1','client.client_email1','invoice.bill_period','invoice.discount','invoice.status','invoice.vat_status','invoice.client_address_id')
                            ->where('invoice.invoice_id',$invoice_id)
                            ->first();
                            
		    if($invoice_information){
		        $client_id = $invoice_information->client_id;
		        $cus_name = $invoice_information->client_title; 
		        $cus_email = $invoice_information->client_email1;
		        $cus_phone = $invoice_information->client_phone1;
		        
		        $address_query = DB::table('client_address')->where('client_address_id',$invoice_information->client_address_id)->first();
		        if($address_query){
		            $address = $address_query->house.','.$address_query->road.','.$address_query->district;
		            $district = $address_query->district;
		            $post_code = $address_query->post_code;
		        }else{
		            $address = 'Dhaka';
		            $district = 'Dhaka';
		            $post_code = '1212';
		        }
		        $bill_period = $invoice_information->bill_period;
		        
		        $invoice_details = DB::table('invoice_details')->where('invoice_id',$invoice_id)->get();
				$amount = 0;
				$vat = 0;
				foreach($invoice_details as $invoice_info)
				{
					$amount += $invoice_info->price*$invoice_info->quantity;
					$vat += ($invoice_info->quantity*$invoice_info->price*$invoice_info->vat*.01);
				}
				$total_vat = $vat;
				$total_amount = $amount - $invoice_information->discount;
				
				if($invoice_information->vat_status==1)
				{
					$pay_amount = ($total_vat+$total_amount);
				}else{
					$pay_amount = $total_amount;
				}
				
				if($pay_amount > 10){
				    
				    $tran_id = "SSLC".uniqid();
				    
				    $post_data = array();
                    $post_data['total_amount'] = $pay_amount; # You cant not pay less than 10
                    $post_data['currency'] = "BDT";
                    $post_data['tran_id'] = $tran_id; // tran_id must be unique
            
                    # CUSTOMER INFORMATION
                    $post_data['cus_name'] = $cus_name;
                    $post_data['cus_email'] = $cus_email;
                    $post_data['cus_add1'] = $address;
                    $post_data['cus_add2'] = "";
                    $post_data['cus_city'] = $district;
                    $post_data['cus_state'] = $district;
                    $post_data['cus_postcode'] = $post_code;
                    $post_data['cus_country'] = "Bangladesh";
                    $post_data['cus_phone'] = $cus_phone;
                    $post_data['cus_fax'] = "";
                    
                    # SHIPMENT INFORMATION
                    $post_data['ship_name'] = $cus_name;
                    $post_data['ship_add1'] = $address;
                    $post_data['ship_add2'] = "";
                    $post_data['ship_city'] = $district;
                    $post_data['ship_state'] = $district;
                    $post_data['ship_postcode'] = $post_code;
                    $post_data['ship_phone'] = $cus_phone;
                    $post_data['ship_country'] = "Bangladesh";
            
                    $post_data['shipping_method'] = "YES";
                    $post_data['product_name'] = "internet";
                    $post_data['product_category'] = "ISP";
                    $post_data['product_profile'] = "internet-service";
            
                    # OPTIONAL PARAMETERS
                    $post_data['value_a'] = $client_id;
            		$post_data['value_b'] = $invoice_id;
            		$post_data['value_c'] = 'Selfcare(iPayment),'.$bill_period;
            		$post_data['value_d'] = "ref004";
            
                    #Before  going to initiate the payment order status need to insert or update as Pending.
                    $update_product = DB::table('orders')
                        ->where('transaction_id', $post_data['tran_id'])
                        ->updateOrInsert([
                            'name' => $post_data['cus_name'],
                            'email' => $post_data['cus_email'],
                            'phone' => $post_data['cus_phone'],
                            'amount' => $post_data['total_amount'],
                            'status' => 'Pending',
                            'address' => $post_data['cus_add1'],
                            'transaction_id' => $post_data['tran_id'],
                            'currency' => $post_data['currency']
                        ]);
            
                    $sslc = new SslCommerzNotification();
                    # initiate(Transaction Data , false: Redirect to SSLCOMMERZ gateway/ true: Show all the Payement gateway here )
                    $payment_options = $sslc->makePayment($post_data, 'hosted');
            
                    if (!is_array($payment_options)) {
                        print_r($payment_options);
                        $payment_options = array();
                    }
				}else{
				    echo '<h4 style="color:red">Payment Amount Must be Greater than 10</h4>';
				}
		    }
		}
        
    }

    public function payViaAjax(Request $request)
    {

        # Here you have to receive all the order data to initate the payment.
        # Lets your oder trnsaction informations are saving in a table called "orders"
        # In orders table order uniq identity is "transaction_id","status" field contain status of the transaction, "amount" is the order amount to be paid and "currency" is for storing Site Currency which will be checked with paid currency.

        $post_data = array();
        $post_data['total_amount'] = '10'; # You cant not pay less than 10
        $post_data['currency'] = "BDT";
        $post_data['tran_id'] = "SSLC".uniqid(); // tran_id must be unique

        # CUSTOMER INFORMATION
        $post_data['cus_name'] = 'Yeasin Arafat';
        $post_data['cus_email'] = 'yeasinarafata.cse@mail.com';
        $post_data['cus_add1'] = 'Amuliya';
        $post_data['cus_add2'] = "";
        $post_data['cus_city'] = "";
        $post_data['cus_state'] = "";
        $post_data['cus_postcode'] = "";
        $post_data['cus_country'] = "Bangladesh";
        $post_data['cus_phone'] = '8801679067474';
        $post_data['cus_fax'] = "";

        # SHIPMENT INFORMATION
        $post_data['ship_name'] = "Store Test";
        $post_data['ship_add1'] = "Dhaka";
        $post_data['ship_add2'] = "Dhaka";
        $post_data['ship_city'] = "Dhaka";
        $post_data['ship_state'] = "Dhaka";
        $post_data['ship_postcode'] = "1000";
        $post_data['ship_phone'] = "";
        $post_data['ship_country'] = "Bangladesh";

        $post_data['shipping_method'] = "NO";
        $post_data['product_name'] = "Computer";
        $post_data['product_category'] = "Goods";
        $post_data['product_profile'] = "physical-goods";

        # OPTIONAL PARAMETERS
        $post_data['value_a'] = "ref001";
        $post_data['value_b'] = "ref002";
        $post_data['value_c'] = "ref003";
        $post_data['value_d'] = "ref004";
        
        #Before  going to initiate the payment order status need to update as Pending.
        $update_product = DB::table('orders')
            ->where('transaction_id', $post_data['tran_id'])
            ->updateOrInsert([
                'name' => $post_data['cus_name'],
                'email' => $post_data['cus_email'],
                'phone' => $post_data['cus_phone'],
                'amount' => $post_data['total_amount'],
                'status' => 'Pending',
                'address' => $post_data['cus_add1'],
                'transaction_id' => $post_data['tran_id'],
                'currency' => $post_data['currency']
            ]);

        $sslc = new SslCommerzNotification();
        # initiate(Transaction Data , false: Redirect to SSLCOMMERZ gateway/ true: Show all the Payement gateway here )
        $payment_options = $sslc->makePayment($post_data, 'checkout', 'json');

        if (!is_array($payment_options)) {
            print_r($payment_options);
            $payment_options = array();
        }

    }

    // public function success(Request $request)
    // {
    //     echo "Transaction is Successful";

    //     $tran_id = $request->input('tran_id');
    //     $amount = $request->input('amount');
    //     $currency = $request->input('currency');

    //     $sslc = new SslCommerzNotification();

    //     #Check order status in order tabel against the transaction id or order id.
    //     $order_detials = DB::table('orders')
    //         ->where('transaction_id', $tran_id)
    //         ->select('transaction_id', 'status', 'currency', 'amount')->first();

    //     if ($order_detials->status == 'Pending') {
    //         $validation = $sslc->orderValidate($tran_id, $amount, $currency, $request->all());

    //         if ($validation == TRUE) {
    //             /*
    //             That means IPN did not work or IPN URL was not set in your merchant panel. Here you need to update order status
    //             in order table as Processing or Complete.
    //             Here you can also sent sms or email for successfull transaction to customer
    //             */
    //             $update_product = DB::table('orders')
    //                 ->where('transaction_id', $tran_id)
    //                 ->update(['status' => 'Processing']);

    //             echo "<br >Transaction is successfully Completed";
    //         } else {
    //             /*
    //             That means IPN did not work or IPN URL was not set in your merchant panel and Transation validation failed.
    //             Here you need to update order status as Failed in order table.
    //             */
    //             $update_product = DB::table('orders')
    //                 ->where('transaction_id', $tran_id)
    //                 ->update(['status' => 'Failed']);
    //             echo "validation Fail";
    //         }
    //     } else if ($order_detials->status == 'Processing' || $order_detials->status == 'Complete') {
    //         /*
    //          That means through IPN Order status already updated. Now you can just show the customer that transaction is completed. No need to udate database.
    //          */
    //         echo "Transaction is successfully Completed";
    //     } else {
    //         #That means something wrong happened. You can redirect customer to your product page.
    //         echo "Invalid Transaction";
    //     }


    // }
    
    public function success(Request $request)
    {
        $tran_id = $request->input('tran_id');
        $amount = $request->input('amount');
        $currency = $request->input('currency');

        $sslc = new SslCommerzNotification();

        #Check order status in order tabel against the transaction id or order id.
        $order_detials = DB::table('secure_payment')
            ->where('tran_id', $tran_id)
            ->first();
        
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
        
        if ($order_detials->payment_status == 1) {
            $validation = $sslc->orderValidate($tran_id, $amount, $currency, $request->all());

            if ($validation == TRUE) {
                /*
                That means IPN did not work or IPN URL was not set in your merchant panel. Here you need to update order status
                in order table as Processing or Complete.
                Here you can also sent sms or email for successfull transaction to customer
                */
                /*$update_product = DB::table('orders')
                    ->where('transaction_id', $tran_id)
                    ->update(['status' => 'Processing']);*/
                    
                $bank_tran_id = $request->input('bank_tran_id');
                $base_fair = $request->input('base_fair');
                $card_brand = $request->input('card_brand');
                $card_issuer = $request->input('card_issuer');
                $card_issuer_country = $request->input('card_issuer_country');
                $card_issuer_country_code = $request->input('card_issuer_country_code');
                $card_no = $request->input('card_no');
                $card_sub_brand = $request->input('card_sub_brand');
                $card_type = $request->input('card_type');
                $currency = $request->input('currency');
                $currency_amount = $request->input('currency_amount');
                $currency_rate = $request->input('currency_rate');
                $currency_type = $request->input('currency_type');
                $error = $request->input('error'); 
                $risk_level = $request->input('risk_level');
                $risk_title = $request->input('risk_title');
                $status = $request->input('status');
                $store_amount = $request->input('store_amount');
                $store_id = $request->input('store_id');
                $response = json_encode($request->all());
                
                 $update_data = array(
                        'payment_status' => 2,
                        'status'=>$status,
                        'error'=>$error,
                        'bank_tran_id'=>$bank_tran_id,
                        'card_brand'=>$card_brand,
                        'card_issuer'=>$card_issuer,
                        'card_issuer_country'=>$card_issuer_country,
                        'card_issuer_country_code'=>$card_issuer_country_code,
                        'card_no'=>$card_no,
                        'card_sub_brand'=>$card_sub_brand,
                        'card_type'=>$card_type,
                        'risk_level'=>$risk_level,
                        'risk_title'=>$risk_title,
                        'store_amount'=>$store_amount,
                        'currency'=>$currency,
                        'store_id'=>$store_id,
                        'currency_type' =>$currency_type,
                        'currency_amount'=>$currency_amount,
                        'currency_rate'=>$currency_rate,
                        'base_fair'=>$base_fair,
                        'gateway_response' => $response,
                        'updated_by' => 1
                    );
                $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
                if($update){
                    //Payment table update process
                    
                    $row = DB::table('secure_payment')->where('tran_id', $tran_id)->first();
                    
                    $invoice_list =DB::select('SELECT invoice_details.invoice_id as inv_id,sum((invoice_details.price+(invoice_details.price*invoice_details.vat/100))*invoice_details.quantity) as bill,(SELECT COALESCE(sum(amount),0) FROM payment WHERE invoice_id=invoice_details.invoice_id) as paid FROM invoice_details,invoice WHERE invoice_details.invoice_id=invoice.invoice_id and invoice.client_id ='.$row->client_id.' and invoice.status in (1,2,6) group by invoice.invoice_id');
        
                    $check_payment = DB::table('payment')->where("invoice_id",$row->invoice_id)->first();
        			if(empty($row->client_id)){
        				//echo '<b style="color:red;">Invoice Number [IL-'.$row->invoice_id.'] is already paid.</b><br/>';
        			}else{
        			    
        			    //for($i = 0; $i < $invoice_list.length; $i++){
        			    foreach($invoice_list as $inv_list){
            				$array = array(
            					'invoice_id'				 => $inv_list->inv_id,
            					'payment_date'				 => date("Y-m-d"),
            					'payment_type'				 => 3,
            					'amount'				 	 => 0.0+$inv_list->bill-$inv_list->paid,
            					'client_id'				 	 => $row->client_id,
            					'reference_number'			 => $row->reason,
            					'status'				 	 => 1,
            					'status_clear_date'			 => date('Y-m-d'),
            					'accept_by' 				 => 0,
            					'admin_id'   				 => 0,
            					'name'				 	     => $row->name,
            					'email'				 	     => $row->email,
            					'phone'				 	     => $row->phone,
            					'remarks' 				     => 'Online Payment',
            					'transaction_id' 		     => $row->tran_id,
            					'account_id' 		     	 => 14,
            					'link'           		     => 'Email',
            					'payment_type_title'         => $row->card_brand,
            					'card_title'                 => $row->card_type,
            					'card_first_six_digit'       => $row->card_no,
            					'card_last_four_digit'       => '',
            					'card_holder_name'           => '',
            					'charge'                     => '',
            					'store_id'                   => $row->store_id
            				);
            				$payment_id = DB::table('payment')->insertGetId($array);
            				
            				$invoice_status = array('status'=>3);
            				
            				DB::table('invoice')->where('invoice_id',$inv_list->inv_id)->update($invoice_status);
            				
            				$check = DB::table('invoice_vat_serial')->where('invoice_id',$inv_list->inv_id)->first();
            				if($check){
            				    
            				}else{
        						$branch_query = DB::table('client')->select('branch_id')->where('client_id', $row->client_id)->first();
    				    if($branch_query){
    				        $branch_id = $branch_query->branch_id;
    				        $branch_vat_check = DB::table('branch')->select('vat_print')->where('branch_id', $branch_id)->first();
    				        if($branch_vat_check){
    				            $vat_status = $branch_vat_check->vat_print;
    				            if($vat_status == 1){
    				                
    				                $vat_serial = array(
        							'invoice_id' => $inv_list->inv_id,
        							'admin_id' => 13
        						);
        					   DB::table('invoice_vat_serial')->insert($vat_serial);
    				            }
    				        }
    				    }
            				}
                        }
        			}
                    
                }

                $msg = "Transaction is successfully Completed";
            } else {
                /*
                That means IPN did not work or IPN URL was not set in your merchant panel and Transation validation failed.
                Here you need to update order status as Failed in order table.
                */
                /*$update_product = DB::table('orders')
                    ->where('transaction_id', $tran_id)
                    ->update(['status' => 'Failed']);*/
                    
                $status = $request->input('status');
                $error = $request->input('error');
                $bank_tran_id = $request->input('bank_tran_id'); 
                $currency = $request->input('currency');
                $store_id = $request->input('store_id');
                $currency_type = $request->input('currency_type');
                $currency_amount = $request->input('currency_amount');
                $currency_rate = $request->input('currency_rate');
                $base_fair = $request->input('base_fair');
                
                $response = json_encode($request->all());
                
                $update_data = array(
                        'payment_status' => 3,
                        'status'=>$status,
                        'error'=>$error,
                        'bank_tran_id'=>$bank_tran_id,
                        'currency'=>$currency,
                        'store_id'=>$store_id,
                        'currency_type' =>$currency_type,
                        'currency_amount'=>$currency_amount,
                        'currency_rate'=>$currency_rate,
                        'base_fair'=>$base_fair,
                        'gateway_response' => $response,
                        'updated_by' => 1
                    );
                $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
                    
                $msg = "validation Fail";
            }
        } else if ($order_detials->payment_status == 2) {
            /*
             That means through IPN Order status already updated. Now you can just show the customer that transaction is completed. No need to udate database.
             */
            $msg = "Transaction is successfully Completed";
        } else {
            #That means something wrong happened. You can redirect customer to your product page.
            $msg = "Invalid Transaction";
        }

        $title = "Success";
        
        //return view('website.ssl.ssl_success',compact('title','msg'));
        echo $msg;
    }

    // public function fail(Request $request)
    // {
    //     $tran_id = $request->input('tran_id');

    //     $order_detials = DB::table('orders')
    //         ->where('transaction_id', $tran_id)
    //         ->select('transaction_id', 'status', 'currency', 'amount')->first();

    //     if ($order_detials->status == 'Pending') {
    //         $update_product = DB::table('orders')
    //             ->where('transaction_id', $tran_id)
    //             ->update(['status' => 'Failed']);
    //         echo "Transaction is Falied";
    //     } else if ($order_detials->status == 'Processing' || $order_detials->status == 'Complete') {
    //         echo "Transaction is already Successful";
    //     } else {
    //         echo "Transaction is Invalid";
    //     }

    // }
    
    public function fail(Request $request)
    {
        $tran_id = $request->input('tran_id');

        $order_detials = DB::table('secure_payment')
            ->where('tran_id', $tran_id)
            ->first();
        
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
        
        if ($order_detials->payment_status == 1) {
            
            $status = $request->input('status');
            $error = $request->input('error');
            $bank_tran_id = $request->input('bank_tran_id'); 
            $currency = $request->input('currency');
            $store_id = $request->input('store_id');
            $currency_type = $request->input('currency_type');
            $currency_amount = $request->input('currency_amount');
            $currency_rate = $request->input('currency_rate');
            $base_fair = $request->input('base_fair');
            
            $response = json_encode($request->all());
            
            $update_data = array(
                    'payment_status' => 3,
                    'status'=>$status,
                    'error'=>$error,
                    'bank_tran_id'=>$bank_tran_id,
                    'currency'=>$currency,
                    'store_id'=>$store_id,
                    'currency_type' =>$currency_type,
                    'currency_amount'=>$currency_amount,
                    'currency_rate'=>$currency_rate,
                    'base_fair'=>$base_fair,
                    'gateway_response' => $response,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
            
            $msg = "Transaction is Falied";
        } else if ($order_detials->status == 2) {
            $msg = "Transaction is already Successful";
        } else {
            $msg = "Transaction is Invalid";
        }
        
        $title = "Failed";
        
        //return view('website.ssl.ssl_fail',compact('title','msg'));
        echo $msg;
        
    }
    

    // public function cancel(Request $request)
    // {
    //     $tran_id = $request->input('tran_id');

    //     $order_detials = DB::table('orders')
    //         ->where('transaction_id', $tran_id)
    //         ->select('transaction_id', 'status', 'currency', 'amount')->first();

    //     if ($order_detials->status == 'Pending') {
    //         $update_product = DB::table('orders')
    //             ->where('transaction_id', $tran_id)
    //             ->update(['status' => 'Canceled']);
    //         echo "Transaction is Cancel";
    //     } else if ($order_detials->status == 'Processing' || $order_detials->status == 'Complete') {
    //         echo "Transaction is already Successful";
    //     } else {
    //         echo "Transaction is Invalid";
    //     }


    // }
    
    public function cancel(Request $request)
    {
        //$request->session()->regenerate();
        //print_r($request->session()->all());die;
        //print_r($request->session()->get('client_id'));die;
        $tran_id = $request->input('tran_id');

        $order_detials = DB::table('secure_payment')
            ->where('tran_id', $tran_id)
            ->first();
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
        if ($order_detials->payment_status == 1) {
            
            /*$update_product = DB::table('orders')
                ->where('transaction_id', $tran_id)
                ->update(['status' => 'Canceled']);*/
                
            $status = $request->input('status');
            $error = $request->input('error');
            $bank_tran_id = $request->input('bank_tran_id'); 
            $currency = $request->input('currency');
            $store_id = $request->input('store_id');
            $currency_type = $request->input('currency_type');
            $currency_amount = $request->input('currency_amount');
            $currency_rate = $request->input('currency_rate');
            $base_fair = $request->input('base_fair');
            
            $response = json_encode($request->all());
            
            $update_data = array(
                    'payment_status' => 4,
                    'status'=>$status,
                    'error'=>$error,
                    'bank_tran_id'=>$bank_tran_id,
                    'currency'=>$currency,
                    'store_id'=>$store_id,
                    'currency_type' =>$currency_type,
                    'currency_amount'=>$currency_amount,
                    'currency_rate'=>$currency_rate,
                    'base_fair'=>$base_fair,
                    'gateway_response' => $response,
                    'updated_by' => 1
                );
            $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
            
            $msg = "Transaction is Cancel";
            
        } else if ($order_detials->payment_status == 2) {
            
            $msg = "Transaction is already Successful";
            
        } else {
            $msg = "Transaction is Invalid";
        }
        
        $title = "Cancel";
        
        //return view('ssl.checkout_hosted',compact('title','msg'));

        echo $msg;
    }

    // public function ipn(Request $request)
    // {
    //     #Received all the payement information from the gateway
    //     if ($request->input('tran_id')) #Check transation id is posted or not.
    //     {

    //         $tran_id = $request->input('tran_id');

    //         #Check order status in order tabel against the transaction id or order id.
    //         $order_details = DB::table('orders')
    //             ->where('transaction_id', $tran_id)
    //             ->select('transaction_id', 'status', 'currency', 'amount')->first();

    //         if ($order_details->status == 'Pending') {
    //             $sslc = new SslCommerzNotification();
    //             $validation = $sslc->orderValidate($tran_id, $order_details->amount, $order_details->currency, $request->all());
    //             if ($validation == TRUE) {
    //                 /*
    //                 That means IPN worked. Here you need to update order status
    //                 in order table as Processing or Complete.
    //                 Here you can also sent sms or email for successful transaction to customer
    //                 */
    //                 $update_product = DB::table('orders')
    //                     ->where('transaction_id', $tran_id)
    //                     ->update(['status' => 'Processing']);

    //                 echo "Transaction is successfully Completed";
    //             } else {
    //                 /*
    //                 That means IPN worked, but Transation validation failed.
    //                 Here you need to update order status as Failed in order table.
    //                 */
    //                 $update_product = DB::table('orders')
    //                     ->where('transaction_id', $tran_id)
    //                     ->update(['status' => 'Failed']);

    //                 echo "validation Fail";
    //             }

    //         } else if ($order_details->status == 'Processing' || $order_details->status == 'Complete') {

    //             #That means Order status already updated. No need to udate database.

    //             echo "Transaction is already successfully Completed";
    //         } else {
    //             #That means something wrong happened. You can redirect customer to your product page.

    //             echo "Invalid Transaction";
    //         }
    //     } else {
    //         echo "Invalid Data";
    //     }
    // }
    
    public function ipn(Request $request)
    {
        // payment_satus : 1 = PENDING, 2 = SUCCESS, 3 = FAILED, 4 = CANCELLED
        
        #Received all the payement information from the gateway
        if ($request->input('tran_id')) #Check transation id is posted or not.
        {

            $tran_id = $request->input('tran_id');

            #Check order status in order tabel against the transaction id or order id.
            $order_details = DB::table('secure_payment')
                ->where('tran_id', $tran_id)
                ->first();

            if ($order_details->payment_status == 1) {
                $sslc = new SslCommerzNotification();
                $validation = $sslc->orderValidate($tran_id, $order_details->amount, $order_details->currency, $request->all());
                if ($validation == TRUE) {
                    /*
                    That means IPN worked. Here you need to update order status
                    in order table as Processing or Complete.
                    Here you can also sent sms or email for successful transaction to customer
                    */
                    /*$update_product = DB::table('orders')
                        ->where('transaction_id', $tran_id)
                        ->update(['status' => 'Processing']);*/
                    
                    $bank_tran_id = $request->input('bank_tran_id');
                    $base_fair = $request->input('base_fair');
                    $card_brand = $request->input('card_brand');
                    $card_issuer = $request->input('card_issuer');
                    $card_issuer_country = $request->input('card_issuer_country');
                    $card_issuer_country_code = $request->input('card_issuer_country_code');
                    $card_no = $request->input('card_no');
                    $card_sub_brand = $request->input('card_sub_brand');
                    $card_type = $request->input('card_type');
                    $currency = $request->input('currency');
                    $currency_amount = $request->input('currency_amount');
                    $currency_rate = $request->input('currency_rate');
                    $currency_type = $request->input('currency_type');
                    $error = $request->input('error'); 
                    $risk_level = $request->input('risk_level');
                    $risk_title = $request->input('risk_title');
                    $status = $request->input('status');
                    $store_amount = $request->input('store_amount');
                    $store_id = $request->input('store_id');
                    $response = json_encode($request->all());
                    
                     $update_data = array(
                            'payment_status' => 2,
                            'status'=>$status,
                            'error'=>$error,
                            'bank_tran_id'=>$bank_tran_id,
                            'card_brand'=>$card_brand,
                            'card_issuer'=>$card_issuer,
                            'card_issuer_country'=>$card_issuer_country,
                            'card_issuer_country_code'=>$card_issuer_country_code,
                            'card_no'=>$card_no,
                            'card_sub_brand'=>$card_sub_brand,
                            'card_type'=>$card_type,
                            'risk_level'=>$risk_level,
                            'risk_title'=>$risk_title,
                            'store_amount'=>$store_amount,
                            'currency'=>$currency,
                            'store_id'=>$store_id,
                            'currency_type' =>$currency_type,
                            'currency_amount'=>$currency_amount,
                            'currency_rate'=>$currency_rate,
                            'base_fair'=>$base_fair,
                            'gateway_response' => $response,
                            'updated_by' => 2
                        );
                    $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
                    if($update){
                        //payment process
                        
                        $row = DB::table('secure_payment')->where('tran_id', $tran_id)->first();
                    
                        $check_payment = DB::table('payment')->where("invoice_id",$row->invoice_id)->first();
            			if($check_payment){
            				//echo '<b style="color:red;">Invoice Number [IL-'.$row->invoice_id.'] is already paid.</b><br/>';
            			}else{
            				$array = array(
            					'invoice_id'				 => $row->invoice_id,
            					'payment_date'				 => date("Y-m-d"),
            					'payment_type'				 => 3,
            					'amount'				 	 => $row->amount,
            					'client_id'				 	 => $row->client_id,
            					'reference_number'			 => $row->reason,
            					'status'				 	 => 1,
            					'status_clear_date'			 => date('Y-m-d'),
            					'accept_by' 				 => 0,
            					'admin_id'   				 => 0,
            					'name'				 	     => $row->name,
            					'email'				 	     => $row->email,
            					'phone'				 	     => $row->phone,
            					'remarks' 				     => 'Online Payment',
            					'transaction_id' 		     => $row->tran_id,
            					'account_id' 		     	 => 14,
            					'link'           		     => 'Email',
            					'payment_type_title'         => $row->card_brand,
            					'card_title'                 => $row->card_type,
            					'card_first_six_digit'       => $row->card_no,
            					'card_last_four_digit'       => '',
            					'card_holder_name'           => '',
            					'charge'                     => '',
            					'store_id'                   => $row->store_id
            				);
            				$payment_id = DB::table('payment')->insertGetId($array);
            				
            				$invoice_status = array('status'=>3);
            				
            				DB::table('invoice')->where('invoice_id',$row->invoice_id)->update($invoice_status);
            				
            				$check = DB::table('invoice_vat_serial')->where('invoice_id',$row->invoice_id)->first();
            				if($check){
            				    
            				}else{
        						$branch_query = DB::table('client')->select('branch_id')->where('client_id', $row->client_id)->first();
    				    if($branch_query){
    				        $branch_id = $branch_query->branch_id;
    				        $branch_vat_check = DB::table('branch')->select('vat_print')->where('branch_id', $branch_id)->first();
    				        if($branch_vat_check){
    				            $vat_status = $branch_vat_check->vat_print;
    				            if($vat_status == 1){
    				                
    				                $vat_serial = array(
        							'invoice_id' => $row->invoice_id,
        							'admin_id' => 13
        						);
        					   DB::table('invoice_vat_serial')->insert($vat_serial);
    				            }
    				        }
    				    }
            				}
                        }
                    }
                    
                    echo "Transaction is successfully Completed";
                } else {
                    /*
                    That means IPN worked, but Transation validation failed.
                    Here you need to update order status as Failed in order table.
                    */
                    /*$update_product = DB::table('orders')
                        ->where('transaction_id', $tran_id)
                        ->update(['status' => 'Failed']);*/
                    
                    $status = $request->input('status');
                    $error = $request->input('error');
                    $bank_tran_id = $request->input('bank_tran_id'); 
                    $currency = $request->input('currency');
                    $store_id = $request->input('store_id');
                    $currency_type = $request->input('currency_type');
                    $currency_amount = $request->input('currency_amount');
                    $currency_rate = $request->input('currency_rate');
                    $base_fair = $request->input('base_fair');
                    
                    $response = json_encode($request->all());
                    
                    $update_data = array(
                            'payment_status' => 3,
                            'status'=>$status,
                            'error'=>$error,
                            'bank_tran_id'=>$bank_tran_id,
                            'currency'=>$currency,
                            'store_id'=>$store_id,
                            'currency_type' =>$currency_type,
                            'currency_amount'=>$currency_amount,
                            'currency_rate'=>$currency_rate,
                            'base_fair'=>$base_fair,
                            'gateway_response' => $response,
                            'updated_by' => 2
                        );
                    $update = DB::table('secure_payment')->where('tran_id',$tran_id)->update($update_data);
                    
                    echo "validation Fail";
                }

            } else if ($order_details->payment_status == 2) {

                #That means Order status already updated. No need to udate database.

                echo "Transaction is already successfully Completed";
            } else {
                #That means something wrong happened. You can redirect customer to your product page.

                echo "Invalid Transaction";
            }
        } else {
            echo "Invalid Data";
        }
    }
    
    public function payNagad_($i_id = NULL,Request $request)
    {
        
        $invoice_id = base64_decode($i_id);
        
		if($invoice_id)
		{
		    $invoice_information = DB::table('invoice')
                            ->join('client', 'invoice.client_id', '=', 'client.client_id')
                            ->select('client.client_id','client.client_title', 'client.client_phone1','client.client_email1','invoice.bill_period','invoice.discount','invoice.status','invoice.vat_status','invoice.client_address_id')
                            ->where('invoice.invoice_id',$invoice_id)
                            ->first();
                            
		    if($invoice_information){
		        $client_id = $invoice_information->client_id;
		        $cus_name = $invoice_information->client_title; 
		        $cus_email = $invoice_information->client_email1;
		        $cus_phone = $invoice_information->client_phone1;
		        
		        $address_query = DB::table('client_address')->where('client_address_id',$invoice_information->client_address_id)->first();
		        if($address_query){
		            $address = $address_query->house.','.$address_query->road.','.$address_query->district;
		            $district = $address_query->district;
		            $post_code = $address_query->post_code;
		        }else{
		            $address = 'Dhaka';
		            $district = 'Dhaka';
		            $post_code = '1212';
		        }
		        $bill_period = $invoice_information->bill_period;
		        
		        $invoice_details = DB::table('invoice_details')->where('invoice_id',$invoice_id)->get();
				$amount = 0;
				$vat = 0;
				foreach($invoice_details as $invoice_info)
				{
					$amount += $invoice_info->price*$invoice_info->quantity;
					$vat += ($invoice_info->quantity*$invoice_info->price*$invoice_info->vat*.01);
				}
				$total_vat = $vat;
				$total_amount = $amount - $invoice_information->discount;
				
				if($invoice_information->vat_status==1)
				{
					$pay_amount = ($total_vat+$total_amount);
				}else{
					$pay_amount = $total_amount;
				}
				
				if($pay_amount > 10){
				    
				    $callBackUrl = URL::to('/pay-nagad/callback');
				   //print_r($callBackUrl);
				    $nagadObj = new Nagad();
				    $response = $nagadObj->urlGenerateForOrder($pay_amount, $callBackUrl);
                    
                    $result = json_decode($response);
                    print_r($response);
                    
                    die;
                    if($result->status == 1){
                        #Before  going to initiate the payment order status need to insert or update as Pending.
                        $update_product = DB::table('orders')
                            ->where('transaction_id', $post_data['tran_id'])
                            ->updateOrInsert([
                                'name' => $post_data['cus_name'],
                                'email' => $post_data['cus_email'],
                                'phone' => $post_data['cus_phone'],
                                'amount' => $post_data['total_amount'],
                                'status' => 'Pending',
                                'address' => $post_data['cus_add1'],
                                'transaction_id' => $post_data['tran_id'],
                                'currency' => $post_data['currency']
                            ]);
                
                        $url = $result->url;
                        echo "<script>window.open($url, '_self')</script>";
                    }else{
                        echo '<h4 style="color:red">Something Wrong. Please try again.</h4>';
                    }
				}else{
				    echo '<h4 style="color:red">Payment Amount Must be Greater than 10</h4>';
				}
		    }
		}
        
    }
    public function payNagadCallback(Request $request){
        $merchantID = $request->merchant;
        $order_id = $request->order_id;
        $payment_ref_id = $request->payment_ref_id;
        $status = $request->status;
        
        $nagadObj = new Nagad();
        $returnJson = $nagadObj->getResponseData($payment_ref_id);
        
        $jsonToArr = json_decode($returnJson);
        if($jsonToArr->merchantId == $merchantID && $jsonToArr->orderId == $order_id){
            if($jsonToArr->status == 'Success'){
                $order_detials = Orders::where('transaction_id', $payment_ref_id)->select('transaction_id', 'status', 'currency', 'amount')->first();
    
                if ($order_detials->status == 'Pending') {
                    $update_product = Orders::where('transaction_id', $payment_ref_id)->update(['status' => 'Processing']);
    
                    echo "<br >Transaction is successfully Completed";
                    
                }
            }else{
                $update_product = Orders::where('transaction_id', $payment_ref_id)->update(['status' => $jsonToArr->status]);
                echo "validation ".$jsonToArr->status;
            }
        }
    }
}
