<?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;
use DateTime;
use Mail;
use DatePeriod;
use DateInterval;
use Exception;
use Carbon\Carbon;
use Illuminate\Support\Facades\Log;

class CronController extends Controller
{
    public function due_payments_sms() {
        $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', 7)
            ->first();
    
        date_default_timezone_set('Asia/Dhaka');
        $today = date('Y-m-d');
    
        // optimized join query with indexes
        $due_sms = DB::table('invoice')
            ->join('client', 'invoice.client_id', '=', 'client.client_id')
            ->select('invoice.client_id', 'client.client_title', 'client.client_phone1')
            ->where('invoice.status', 1)
            ->where('client.client_phone1', '!=', '')
            ->where('client.client_phone1', 'like', '880%')
            ->where('client.last_bill_summary_sms_date', 'not like', "$today%")
            ->groupBy('invoice.client_id', 'client.client_title', 'client.client_phone1')
            ->get();
    
        $i = 0;
        if ($due_sms->count()) {
            foreach ($due_sms as $row) {
                $messageTemplate = str_replace(
                    ['[[client_title]]', '[[client_id]]'],
                    [$row->client_title, $row->client_id],
                    $sms->message
                );
    
                $encoded_message = urlencode($messageTemplate);
                $api = str_replace(
                    ['[[sms]]', '[[gsm]]'],
                    [$encoded_message, $row->client_phone1],
                    $sms->link
                );
    
                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $api);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                $message = curl_exec($ch);
                curl_close($ch);
    
                if (strpos($message, "200") !== false) {
                    DB::table('client')
                        ->where('client_id', $row->client_id)
                        ->update(['last_bill_summary_sms_date' => date('Y-m-d H:i:s')]);
    
                    echo $row->client_id . " - SMS Send Done.<br>";
                    $i++;
                }
            }
        }
    
        echo "Total -" . $i . " SMS Send";
    
        DB::table('cron_settings')->where('cron_id', 13)
            ->update(['run_time' => date('Y-m-d H:i:s')]);
    
        echo 'Cron Runned Successfully';
    }

	
	public function due_payments_email(){
	    
	    $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',7)
                ->first();
                                        
	    $due_email = DB::select("SELECT invoice.invoice_id,invoice.client_id,client.client_title,client.client_phone1,client.client_email1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.client_email1_status != '0' and client.last_bill_summary_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
	    //$due_email = DB::select("SELECT client_id,client_email1 FROM client WHERE client_id in (SELECT client_id FROM invoice_recurring WHERE status = 1) and client_email1 not like '880%' and last_bill_summary_date NOT LIKE '".date('Y-m-d')."%' ");
	    //$due_email = DB::select("SELECT client_id,client_email1 FROM client WHERE client_id in (4705,8238) and client_email1 not like '880%'; ");
        $i = 0;
        if($due_email){
	            foreach($due_email as $row){
	               //if($row->client_id == '4705'){
	                
	                $notification 	= DB::table('notification')->where('id',4)->first();
            
            $type = $notification->type;
	        $subject = $notification->subject;
	        //$subject = "সুরক্ষা মোডে সাময়িক সমস্যা – আপনার সহযোগিতার জন্য ধন্যবাদ";
	        $from_email = $notification->from_email;
	        $from_name = $notification->from_name;
	        
	        
	        $client_info = DB::table('client')->where('client_id',$row->client_id)->first();
    			
			$c_email = array();
			if($client_info->client_email1_status != 0){
			    $emails = [$client_info->client_email1];
			    $c_email = array($client_info->client_email1);
			}elseif($client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email2];
			    $c_email = array($client_info->client_email2);
			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email1,$client_info->client_email2];
			    $c_email = array($client_info->client_email1,$client_info->client_email2);
			}
			
			if(!empty($c_email)){
    		    //$data = array('invoice_id'=>$row->invoice_id,'client_id'=>$row->client_id);
    		    $data = array('client_id'=>$row->client_id);
                Mail::send('mail_template.due_reminder_new', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                //Mail::send('mail_template.employee_announcement_due', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                    //  echo $message;announcement_due
                    //  die();
                     $message->to($emails, $type)->subject($subject);
                     $message->from($from_email,$from_name);
                });
                
                $data_update = array(
    				'last_bill_summary_date' => date('Y-m-d H:i:s')
    			);
        		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
			}
			
			echo $row->client_id." - Email Send Done.<br>";
                        
                		$i++;
	        
	        
    				
    				
			
	            //}
			
			
			
    				
    				
	                
	            }
        }
        
        echo "Total -".$i." Email Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',12)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		
	    
	}
	
	public function due_payments_sms_bangla(){
	    
	    $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',27)
                ->first();
                                        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
        //$due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.client_id='7' GROUP by invoice.client_id");
        //$due_sms = DB::select("SELECT DISTINCT(client_phone1),client_title,client_id FROM client WHERE client_id in (SELECT client_id FROM invoice_recurring WHERE status = 1) and last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' ");
        
        
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
	                
	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
	                $encoded_message = urlencode($n2);
	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
	                //$api = str_replace('[[gsm]]',"01711043428",$m1);
	                
	                $ch = curl_init();
    				curl_setopt($ch, CURLOPT_URL, $api);
    				curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    				$message = curl_exec($ch);
    				curl_close($ch);
    				
    				//echo $message;
                    //if(strpos($message, "SMS SUBMITTED") !== false){
    				if(strpos($message, "200") !== false){
                        echo $row->client_id." - SMS Send Done.<br>";
                        $data_update = array(
            				'last_bill_summary_sms_date' => date('Y-m-d H:i:s')
            			);
                		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                		$i++;
            		
                    }
    				//exit();
    				//echo $row->client_id." - SMS Send Done.<br>";
    				
    				
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',13)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		
	    
	}
	
	public function due_payments_sms_prepaid(){
	    
	    $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',7)
                ->first();
                                        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.frequency='p1' and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
        
        // print_r($due_sms);
        // exit();
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
	                
	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
	                $encoded_message = urlencode($n2);
	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
	                
	                $ch = curl_init();
    				curl_setopt($ch, CURLOPT_URL, $api);
    				curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    				$message = curl_exec($ch);
    				curl_close($ch);
    				
    				echo $message;
    				//if(strpos($message, "SMS SUBMITTED") !== false){
    				if(strpos($message, "200") !== false){
        				echo $row->client_id." - SMS Send Done.<br>";
        				
        				$data_update = array(
                				'last_bill_summary_sms_date' => date('Y-m-d H:i:s')
                			);
                		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                		$i++;
    				}
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
	    
	}
	
	public function ping_monitor_ip($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-30 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $device_id = $id;
	    //$connection_list = DB::select("SELECT * FROM connection_list WHERE link_status=1 and device_id = $device_id ");
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id = $device_id) and status=1 and last_ping < '$cur_time' order by last_ping ASC limit 200");
	    
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = true;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
	    //$monitor_ip_list = DB::select("SELECT monitor_id,ip FROM connection_monitor_ip ");
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                //$connection_ip = DB::select("SELECT * FROM connection_ip where connection_id = $row->connection_id and type = 'ipv_four' ");
	                
	               // if($connection_ip){
	               //     foreach($connection_ip as $ips){
	                        
	                        $API->write('/ping',false);
                            $API->write('=address='.$row->ip.'',false); 
                            $API->write('=count=3',false);
                            $API->write('=interval=1');
                            $ARRAY4 = $API->read(false);
                            //echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            
                            $i++;
	                        
	               //     }
	               // }
	                
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',17)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	public function ping_monitor_ip_bandwidth($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-30 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $device_id = $id;
	    //$connection_list = DB::select("SELECT * FROM connection_list WHERE link_status=1 and device_id = $device_id ");
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id = $device_id) and status=1 and client_id='7955' order by last_ping ASC limit 200");
	    //and last_ping < '$cur_time' 
	   // print_r($connection_list);
	   // exit();
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = true;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
	    //$monitor_ip_list = DB::select("SELECT monitor_id,ip FROM connection_monitor_ip ");
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                //$connection_ip = DB::select("SELECT * FROM connection_ip where connection_id = $row->connection_id and type = 'ipv_four' ");
	                
	               // if($connection_ip){
	               //     foreach($connection_ip as $ips){
	                        
	                        
	                       // $API->write('/tool/bandwidth-test',false);
                        //     $API->write('=address=172.23.104.19',false);
                        //     $API->write('=duration=10',false);
                        //     $API->write('=protocal=udp',false);
                        //     $API->write('=direction=both');

	                        //$API->write('/tool bandwidth-test address=172.23.104.19 interval=1 duration=10', null, 'm');
                            // $API->write('=address='.$row->ip.'',false); 
                            // $API->write('=count=3',false);
                            // $API->write('=interval=1');
                            //$ARRAY4 = $API->read(false);
                            
                            $arrRXTX=$API->comm("/interface/monitor-traffic", array( ".proplist"=> "rx-bits-per-second,tx-bits-per-second", "?interface" => "ether2",));
                            print_r($arrRXTX);
	
                            //echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            exit();
                            
                            
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            
                            $i++;
	                        
	               //     }
	               // }
	                
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',17)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	public function ping_monitor_ip_down($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-5 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $device_id = $id;
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id = $device_id) and status=1 and ping=2 and last_ping < '$cur_time' order by last_ping limit 100");
	    
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = true;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                        
	                        $API->write('/ping',false);
                            $API->write('=address='.$row->ip.'',false); 
                            $API->write('=count=3',false);
                            $API->write('=interval=1');
                            $ARRAY4 = $API->read(false);
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            $i++;
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',18)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	
	public function ping_monitor_no_activity(){
	    
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE last_ping = '0000-00-00 00:00:00'");
	                        
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                        
	                        $this->ping_monitor_by_cid($row->connection_id);
                    
                            $i++;
	            }
        }
        echo "Total -".$i." Ping Done";
        
	}
	
	public function ping_monitor_status_off(){
	    
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE status !=2 and last_up + interval 7 day <= now() ORDER BY last_ping DESC ");
	    
	    
	    
	    $connection_list = $connection_list + DB::select("SELECT * FROM connection_monitor_ip WHERE status !=2 and connection_id not in (SELECT connection_id FROM connection_list WHERE link_status = 1)");
	    
	    $connection_list = $connection_list + DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id not in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id in (1,2)) and status!=2");
	                        
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                echo 'MID-'.$row->monitor_id.'<br>';
	                
	                        
	                        $data_update = array(
                        				'status' => 2,
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
	            }
        }
	}
	
	public function ping_monitor_by_cid($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-5 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $connection_info = DB::table('connection_list')->where('connection_id',$id)->first();
	    
	    $device_id = $connection_info->device_id;
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE status=1 and connection_id = $id");
	    
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = true;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                        
	                        $API->write('/ping',false);
                            $API->write('=address='.$row->ip.'',false); 
                            $API->write('=count=5',false);
                            $API->write('=interval=1');
                            
                            // /tool/ping address=10.155.126.252 count=5 interval=200ms
                            
                            ///$API->write('/ping'.$row->ip.' arp-ping=yes interface=Summit_Kasba_2358_Shared',false);
                            
                            // $API->write('/ip arp print',false);
                            // $API->write('=address='.$row->ip.'',false); 
                            // $API->write('=count=3',false);
                            // $API->write('=interval=1');
                            
                            $ARRAY4 = $API->read(false);
                            echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            $i++;
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
	}
	
	public function send_notify(Request $request)
	{
		$invoice_id = $request->invoice_id;
		$client_id = $request->client_id;
		$email_notification = $request->email_notification;
		$sms_notification = $request->sms_notification;
		if($email_notification == 1){
		    //sent email
		    $notification 	= DB::table('notification')->where('id',4)->first();
            
            $type = $notification->type;
	        $subject = $notification->subject;
	        $from_email = $notification->from_email;
	        $from_name = $notification->from_name;
			
			$client_info = DB::table('client')->where('client_id',$client_id)->first();
    			
			$c_email = array();
			if($client_info->client_email1_status != 0){
			    $emails = [$client_info->client_email1];
			    $c_email = array($client_info->client_email1);
			}elseif($client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email2];
			    $c_email = array($client_info->client_email2);
			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email1,$client_info->client_email2];
			    $c_email = array($client_info->client_email1,$client_info->client_email2);
			}
			
			if(!empty($c_email)){
    		    $data = array('invoice_id'=>$invoice_id,'client_id'=>$client_id);
                Mail::send('mail_template.due_reminder', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                    //  echo $message;
                    //  die();
                     $message->to($emails, $type)->subject($subject);
                     $message->from($from_email,$from_name);
                });
			}
            $request->session()->flash('status_notify','Email Send Successfully');
		}
	}
	
	public function bad_debt_sms() {
        $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', 7)
            ->first();
    
        $today = date('Y-m-d');
    
        $due_sms = DB::table('invoice')
            ->join('client', 'invoice.client_id', '=', 'client.client_id')
            ->select('invoice.client_id', 'client.client_title', 'client.client_phone1')
            ->whereIn('invoice.status', [2, 6])
            ->where('client.last_bill_summary_sms_date', 'NOT LIKE', "$today%")
            ->where('client.client_phone1', '!=', '')
            ->where('client.client_phone1', 'LIKE', '880%')
            ->groupBy('invoice.client_id', 'client.client_title', 'client.client_phone1')
            ->get();
    
        $i = 0;
    
        foreach ($due_sms as $row) {
            $n1 = str_replace('[[client_title]]', $row->client_title, $sms->message);
            $n2 = str_replace('[[client_id]]', $row->client_id, $n1);
            $encoded_message = urlencode($n2);
    
            $m1 = str_replace('[[sms]]', $encoded_message, $sms->link);
            $api = str_replace('[[gsm]]', $row->client_phone1, $m1);
    
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $api);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            $message = curl_exec($ch);
            curl_close($ch);
    
            echo $row->client_id . " - SMS Send Done.<br>";
    
            DB::table('client')
                ->where('client_id', $row->client_id)
                ->update(['last_bill_summary_sms_date' => date('Y-m-d H:i:s')]);
    
            $i++;
        }
    
        echo "Total -" . $i . " SMS Send";
    
        DB::table('cron_settings')
            ->where('cron_id', 6)
            ->update(['run_time' => date('Y-m-d H:i:s')]);
    }

	
	public function device_wise_disconnect($id = NULL){
        date_default_timezone_set('Asia/Dhaka');
        $to_date = date("Y-m-d");
        $device_id = (int) $id;
    
        // Normal connections
        $connection_result = DB::select("
            SELECT cl.connection_id, cl.limit_number, cl.limit_type, cl.client_id, i.invoice_created_date
            FROM connection_list AS cl
            INNER JOIN invoice AS i ON cl.client_id = i.client_id
            WHERE cl.link_status = 1
              AND cl.device_id = ?
              AND cl.last_cron_run != ?
              AND cl.auto_disconnect = 1
              AND i.status IN (1, 2, 6)
              AND DATE_ADD(i.invoice_created_date, INTERVAL cl.limit_number DAY) < ?
            GROUP BY cl.connection_id
        ", [$device_id, $to_date, $to_date]);
    
        // Prepaid connections
        $prepaid_connection_result = DB::select("
            SELECT cl.connection_id, cl.limit_number, cl.limit_type, cl.client_id, i.invoice_created_date
            FROM connection_list AS cl
            INNER JOIN invoice AS i ON cl.client_id = i.client_id
            WHERE cl.link_status = 1
              AND cl.device_id = ?
              AND cl.last_cron_run != ?
              AND cl.auto_disconnect = 1
              AND i.status IN (1, 2, 6)
              AND i.invoice_id IN (
                  SELECT inv.invoice_id
                  FROM invoice inv
                  INNER JOIN invoice_recurring ir ON inv.invoice_recurring_id = ir.invoice_id
                  WHERE ir.frequency = 'p1'
              )
            GROUP BY cl.connection_id
        ", [$device_id, $to_date]);
    
        // Device settings
        $device = DB::table('device_settings')->where('id', $device_id)->first();
        if (!$device) {
            echo "Device not found.";
            return;
        }
    
        $last_cron_run = ['last_cron_run' => date('Y-m-d')];
    
        include(app_path() . '/NatApi/routeros_api.class.php');
        $API = new \RouterosAPI();
        $API->debug = true;
    
        $i = 0;
    
        // Connect to MikroTik only once
        if ($API->connect($device->device_ip, $device->username, $device->password)) {
    
            // Process both normal + prepaid connections
            foreach (array_merge($connection_result, $prepaid_connection_result) as $value) {
    
                // Insert or update billing automation
                DB::table('billing_automation')->updateOrInsert(
                    ['connection_id' => $value->connection_id],
                    [
                        'admin_id' => 0,
                        'client_id' => $value->client_id,
                        'connection_id' => $value->connection_id,
                        'nat_add_time' => date("Y-m-d H:i:s"),
                        'note' => "Automation",
                        'status' => 1
                    ]
                );
    
                // Fetch IP addresses for the connection
                $ip_result = DB::select("
                    SELECT ip_address, type 
                    FROM connection_ip 
                    WHERE connection_id = ?
                ", [$value->connection_id]);
    
                // Update last_cron_run in connection_list
                DB::table('connection_list')
                    ->where('connection_id', $value->connection_id)
                    ->update($last_cron_run);
    
                // Add to MikroTik firewall
                foreach ($ip_result as $val) {
                    $API->comm("/ip/firewall/address-list/add", [
                        "list" => "payment_reminder",
                        "address" => $val->ip_address,
                        "comment" => "ILC-{$value->client_id}__CID-{$value->connection_id}"
                    ]);
    
                    if ($val->type == 'ipv_six') {
                        $API->comm("/ipv6/firewall/address-list/add", [
                            "list" => "payment_reminder",
                            "address" => $val->ip_address,
                            "comment" => "ILC-{$value->client_id}__CID-{$value->connection_id}"
                        ]);
                    }
                }
    
                echo "CID-{$value->connection_id} Disconnected.<br>";
                $i++;
            }
        } else {
            echo "Failed to connect to MikroTik device.";
        }
    
        //update cron log
        $cron_log = ['run_time' => date('Y-m-d H:i:s')];
        $cron_map = [
            1 => 9,
            2 => 10,
            3 => 11,
            5 => 19
        ];
    
        if (isset($cron_map[$id])) {
            $update = DB::table('cron_settings')->where('cron_id', $cron_map[$id])->update($cron_log);
            echo $update ? 'Cron Runned Successfully' : 'Nothing to Update';
        }
    
        echo "<br>Total Disconnections: $i";
    }

	
	public function device_wise_ppoe_setup($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
		$to_date = date("Y-m-d");
		$device_id = $id;
		
        
		$device = DB::table('device_settings')->where('id',$device_id)->first();
		
		
		
		include(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = true;
        $i=0; 		
		if($device){
    		
    			
    			if ($API->connect($device->device_ip, $device->username, $device->password)) {
                				        
                				        
                				        $name = "4705Zayed";
                				        $password = "4705Zayed";
                				        $service = "pppoe";
                				        $profile = "30 Mbps Zayed";
                				        $local_address = "172.21.60.1";
                				        
                				        $rate_limit="35M/32M";
                				        $dns_server = '1.1.1.1';
                				        $dns_server_2 = '8.8.8.8';
                				        
                				        $remote_address = "172.21.60.117";
                				        
                				        $id = "0";
                				        
                				        
                				        //$API->comm("/ppp/secret/add\n=name=".$name."\n=password=".$password."\n=service=".$service."\n=profile=".$profile."\n=local-address=".$local_address."\n=remote-address=".$remote_address."\n=comment=ILC-");
                				        //$API->comm("/ppp/secret/disable\n=numbers=".$name);remove
                				        //$API->comm("/ppp/secret/enable\n=numbers=".$name);
                				        
                				        // $API->write("/ppp/secret/print");
                            //             $ARRAYS = $API->read();
                                        
                            //             //print_r($ARRAYS);
                                        
                                        
                            //             foreach ($ARRAYS as $secret) {
                            //                 // if($secret['name'] ==  $name){
                            //                 //     $id = $secret['.id'];
                            //                 // }
                                            
                                            
                            //                 $ppoe_profile = DB::table('ppoe_profile')->where('profile',$secret['profile'])->first();
                                            
                            //                 echo $secret['name'];
                                            
                            //                 $router_ppoe_table = array(
                            //                     'name' 			=> $secret['name'],
                            //                     'password'          => $secret['password'],
                            //                     'profile_id'        => $ppoe_profile->id,
                            //                     'local_address'     => $secret['local-address'],
                            //                     'remote_address'  => $secret['remote-address'],
                            //                     'device_id' 				=> $device_id,
                            //                 );
                                            
                            //                 $ppoe_user_list = DB::table('router_ppoe_list')->where('name',$secret['name'])->first();
                            //                 if(empty($ppoe_user_list)){
                            //                     $insert_id = DB::table('router_ppoe_list')->insertGetId($router_ppoe_table);
                            //                 //echo $secret['name'];
                            //                 }
                                             
                                            
                            //                 //exit();
                            //             }
                                        
                            //             $API->comm("/ppp/secret/remove\n=numbers=". $id);
                            
                            
                            //$API->comm("/ppp/profile/add\n=name=".$profile."\n=rate-limit=".$rate_limit."\n=dns-server=".$dns_server.",".$dns_server_2."\n=only-one=yes"); 
                            
                             
                            //ppp profile set [find name="profile_name"] rate-limit="download/upload"  
                            
                            $API->write("/ppp/profile/print");
                                        $ARRAYS = $API->read();
                                        
                                        print_r($ARRAYS);
                                        exit();
                                        foreach ($ARRAYS as $secret) {
                                            if($secret['name'] ==  $profile){
                                                $id = $secret['.id'];
                                            }
                                        }
                            
                            //$API->comm("/ppp/profile/set\n=numbers=".$id."\n=rate-limit=".$rate_limit);
                            // $API->comm("/ppp/profile/remove\n=numbers=".$id);
                            //ppp profile remove [find name="profile_name"]
                            //ppp profile add name="5Mbps_2Mbps_Profile"  rate-limit="5M/2M"
                                        
                				        // ppp secret print detail brief
                				        echo "found";
                				        exit();
                				        
                				    }
		}
		
		
		$update = "";
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	public function recurring_invoice_create_cron_custom(){
	    $data['result'] = '';
	    return view('recurring_form',$data);
	}
	
	public function recurring_invoice_create_cron_custom_submit(Request $request){
	    $selectedDate = Carbon::parse($request->selected_date); // Example: 2025-08-01

        $currentMonth = $selectedDate->month;
        $currentYear = $selectedDate->year;
        
        // Total recurring count (active monthly recurring)
        $totalRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->count();
        
        // Bill created this month
        $billCreated = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->whereMonth('last_invoice_created_date', $currentMonth)
            ->whereYear('last_invoice_created_date', $currentYear)
            ->count();
        
        // Bill not created this month = total - created
        $billNotCreated = $totalRecurring - $billCreated;
        
        $data['result'] = [
            'total_recurring' => $totalRecurring,
            'bill_created' => $billCreated,
            'bill_not_created' => $billNotCreated,
        ];
        
	    $data['selected_date'] = $request->selected_date;
	    return view('recurring_form', $data);
	}
	
	public function recurring_invoice_create_cron_custom_process(Request $request){
	    
        date_default_timezone_set('Asia/Dhaka');

        // Selected date from user input or system (you can replace this with dynamic date)
        $selectedDate = Carbon::parse($request->selected_date); // Format: j/n/Y
        $selectedDay = date('j', strtotime(str_replace('/', '-', $selectedDate)));
        $selectedMonth = date('n', strtotime(str_replace('/', '-', $selectedDate)));
        $selectedYear = date('Y', strtotime(str_replace('/', '-', $selectedDate)));
        $totalDaysOfTheMonth = cal_days_in_month(CAL_GREGORIAN, $selectedMonth, $selectedYear);
        
        // Prepare dayArray
        $dayArray = [];
        $lastDateOfTheMonth = 0;
        if (intval($selectedDay) == intval($totalDaysOfTheMonth)) {
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for ($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++) {
                $dayArray[] = $i;
            }
        } else {
            $dayArray[] = $selectedDay;
        }
        
        // Fetch recurring invoices (monthly)
        $monthlyRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->where(function ($query) use ($selectedMonth, $selectedYear) {
                $query->where(function ($q) use ($selectedMonth, $selectedYear) {
                    $q->whereNotNull('last_invoice_id')
                      ->where(function ($q2) use ($selectedMonth, $selectedYear) {
                          $q2->whereMonth('last_invoice_created_date', '!=', $selectedMonth)
                             ->orWhereYear('last_invoice_created_date', '!=', $selectedYear);
                      });
                })
                ->orWhere(function ($q) {
                    $q->whereNull('last_invoice_id')
                      ->whereNull('last_invoice_created_date');
                });
            })
            ->get();
        
        $generatedCount = 0;
        $skippedCount = 0;
        
        if ($monthlyRecurring) {
            $i = 0;
            foreach ($monthlyRecurring as $invoice) {
                $shouldGenerate = false;
        
                if (!empty($invoice->starting_date)) {
                    $startingDateTimestamp = strtotime($invoice->starting_date);
                    $selectedDateTimestamp = strtotime(str_replace('/', '-', $selectedDate));
        
                    if ($startingDateTimestamp <= $selectedDateTimestamp) {
                        // Check if invoice count limit is reached
                        if ($invoice->number_of_invoice == 0 || $invoice->number_of_created_invoice < $invoice->number_of_invoice) {
                            $startingDay = date('j', strtotime($invoice->starting_date));
        
                            if (in_array($startingDay, $dayArray)) {
                                $shouldGenerate = true;
                            }
                        }
                    }
                }
        
                if ($shouldGenerate) {
                    $i++;
                    // Generate invoice
                    //$this->auto_create_invoice($invoice->invoice_id);
                    $generatedCount++;
        
                    echo $i." - Need Invoice Create: #{$invoice->invoice_id}<br/>";
                } else {
                    $skippedCount++;
                }
            }
        }
        
        // Summary Report
        echo "<hr/>";
        echo "<strong>Monthly Recurring Invoice Report for {$selectedDate}</strong><br/>";
        echo "Total Recurring Invoices: " . count($monthlyRecurring) . "<br/>";
        echo "Invoices Need Created: {$generatedCount}<br/>";
        //echo "Invoices Skipped: {$skippedCount}<br/>";

	    //$data['selected_date'] = $request->selected_date;
	    //return view('recurring_form', $data);
	}
	
	function recurring_invoice_create_cron_new(){
       
       date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        //$todayDate = date('j/n/Y');
        $todayDate = '01/08/2025';
        //echo $todayDate;die;
        // echo date('Y-m-d H:i:a');
        // exit();
        //$todayDay = date('j');
        $todayDay = 01;
        $todayMonth = date('n');
        //$todayYear = date('Y');
        $todayYear = '2025';
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        
        
        /*1 month*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m1')->where('status',1)->get();
        //$monthlyRecurring = DB::select("SELECT * FROM invoice_recurring WHERE status = 1 and frequency='m1' AND starting_date < '2024-12-02' and invoice_id not in (SELECT invoice_recurring_id FROM invoice WHERE invoice_created_date = '2024-12-01 00:00:00')");
		
        if($monthlyRecurring){
            $i = 0;
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                }else{
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if($startingDate_time <= $TodayDateString){
                        $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));

                        if(in_array($startingDay, $dayArray)){
                            
                            $current_month = date('m');
    						$current_year = date('Y');
    						$check_bill = DB::select("SELECT invoice_id,client_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
    						if($check_bill)
    						{
    						    //$i++;
    						    //echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' Created<br>';
    						    //$this->auto_create_invoice($invoice->invoice_id);
                                //$month1++;
    						}else{
    						   $i++;
    						   //$this->auto_create_invoice($invoice->invoice_id);
                               $month1++;
    						   echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' not created <br>';
    						}
                            
                        }
                    }
                }
            }
            /*else{
                if($lastDateOfTheMonth){
                    $this->invoice_recurring_model->auto_create_invoice($invoice->invoice_id);
                }
            }*/
        }
        }
        /*2 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m2')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 2 == 0) {
                                echo '2 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                                //$month2++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*3 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m3')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 3 == 0) {
                                echo '3 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                            	//$month3++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*6 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m6')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 6 == 0) {
                                echo '6 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                            	//$month6++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*1 year*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y1')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        echo '01 years - '.$invoice->invoice_id.'<br>';
                        //$this->auto_create_invoice($invoice->invoice_id);
                    	//$year1++;
                    }
                }
            }
        }
        }
        /*2 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y2')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 2 == 0) {
                            echo '02 years - '.$invoice->invoice_id.'<br>';
                            //$this->auto_create_invoice($invoice->invoice_id);
                            //$year2++;
                        }
                    }
                }
            }
        }
        }
        /*3 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y3')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 3 == 0) {
                            echo '03 years - '.$invoice->invoice_id.'<br>';
                            //$this->auto_create_invoice($invoice->invoice_id);
                            //$year3++;	
                        }
                    }
                }
            }
        }
        }
        
        echo 'cron runned successfully...';
//         $send_email = true;
// 		if($send_email) {
		    
// 		    $type = 'Cron Mail';
// 	        $subject = 'Recurring Billing Cron';
// 	        $from_email = 'info@infolinkbd.com';
			
// 			$emails = ['ne.zayed@gmail.com'];
			
// 		    $data = array('month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
// 		    echo 'Month- '.$month1;
// 		    echo '<br>Month2- '.$month2;
// 		    echo '<br>Month3- '.$month3;
// 		    echo '<br>Month6- '.$month6;
// 		    echo '<br>Year1- '.$year1;
// 		    echo '<br>Year2- '.$year2;
// 		    echo '<br>Year 3- '.$year3;
//             Mail::send('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
//                  $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject($subject);
         
//                  $message->from($from_email,'Infolink Limited');
//             });
			
// 		} 
        
//         $cron_log = array(
// 			'run_time' 	=> date('Y-m-d H:i:s')
// 		);
// 		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
// 		if($update){
// 		    echo 'Cron Runned Successfully';
// 		}else{
// 		    echo 'Nothing to Update';
// 		}
    }
	
	//new condition added for invoice recurring table last_invoice_id, last_invoice_created_date
    public function recurring_invoice_create_cron(){
        date_default_timezone_set('Asia/Dhaka');
    
        $todayDate   = date('j/n/Y');
        $todayDay    = (int) date('j');
        $todayMonth  = (int) date('n');
        $todayYear   = (int) date('Y');
        $totalDays   = cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
    
        // If it's last day of month, include up to 31
        $dayArray = ($todayDay === $totalDays) ? range($totalDays, 31) : [$todayDay];
    
        // First day of current month for date comparison
        $firstDayOfCurrentMonth = date('Y-m-01');
    
        $frequencies = [
            'm1' => function($months) { return true; },
            'm2' => function($months) { return $months % 2 === 0; },
            'm3' => function($months) { return $months % 3 === 0; },
            'm6' => function($months) { return $months % 6 === 0; },
            'y1' => function($years)  { return true; },
            'y2' => function($years)  { return $years % 2 === 0; },
            'y3' => function($years)  { return $years % 3 === 0; },
        ];
    
        $counts = array_fill_keys(array_keys($frequencies), 0);
    
        foreach ($frequencies as $freq => $conditionFn) {
            $recurrings = DB::table('invoice_recurring')
                ->where('frequency', $freq)
                ->where('status', 1)
                ->where(function ($query) use ($firstDayOfCurrentMonth) {
                    $query->where(function ($q) use ($firstDayOfCurrentMonth) {
                        // last invoice exists but before current month
                        $q->whereNotNull('last_invoice_id')
                          ->where('last_invoice_created_date', '<', $firstDayOfCurrentMonth);
                    })
                    ->orWhere(function ($q) {
                        // never billed before
                        $q->whereNull('last_invoice_id')
                          ->whereNull('last_invoice_created_date');
                    });
                })
                ->get();
    
            foreach ($recurrings as $invoice) {
                if (empty($invoice->starting_date)) {
                    continue;
                }
    
                if ($invoice->number_of_invoice != 0 &&
                    $invoice->number_of_invoice <= $invoice->number_of_created_invoice) {
                    continue;
                }
    
                $startTime = strtotime($invoice->starting_date);
                $todayTime = strtotime($todayDate);
    
                if ($startTime > $todayTime) {
                    continue;
                }
    
                $startDay = (int) date('j', $startTime);
                if (!in_array($startDay, $dayArray)) {
                    continue;
                }
    
                if (str_starts_with($freq, 'm')) {
                    $date1 = date_create(date('Y-m-01', $todayTime));
                    $date2 = date_create(date('Y-m-01', $startTime));
                    $monthsDiff = ((int) $date1->format('Y') - (int) $date2->format('Y')) * 12
                                + ((int) $date1->format('m') - (int) $date2->format('m'));
    
                    if (!$conditionFn($monthsDiff)) continue;
                } else {
                    $sameMonthDay = date('j/n', $startTime) == date('j/n', $todayTime);
                    if (!$sameMonthDay) continue;
    
                    $date1 = date_create(date('Y-01-01', $todayTime));
                    $date2 = date_create(date('Y-01-01', $startTime));
                    $yearsDiff = (int) $date1->format('Y') - (int) $date2->format('Y');
    
                    if (!$conditionFn($yearsDiff)) continue;
                }
    
                $this->auto_create_invoice($invoice->invoice_id);
                $counts[$freq]++;
                echo "$freq {$counts[$freq]}<br/>";
            }
        }
    
        // Send summary email
        Mail::send('mail_template.billing_cron_run_email', $counts, function($message) {
            $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')
                    ->bcc('ne.zayed@gmail.com')
                    ->subject('Recurring Billing Cron')
                    ->from('info@infolinkbd.com','Infolink Limited');
        });
    
        // Update cron log
        DB::table('cron_settings')->where('cron_id', 1)
            ->update(['run_time' => date('Y-m-d H:i:s')]);
    
        echo 'Cron Runned Successfully';
    }


    
    

    public function recurring_invoice_create_cron_niloy() {
        date_default_timezone_set('Asia/Dhaka');
        $today = new DateTime();
        $todayDate = $today->format('Y-m-d');
        $todayDay = (int) $today->format('j');
        $todayMonth = (int) $today->format('n');
        $todayYear = (int) $today->format('Y');
        $totalDaysOfMonth = cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
    
        $dayArray = ($todayDay === $totalDaysOfMonth)
            ? range($todayDay, 31)
            : [$todayDay];
    
        $counters = [
            'm1' => 0, 'm2' => 0, 'm3' => 0,
            'm6' => 0, 'y1' => 0, 'y2' => 0, 'y3' => 0
        ];
    
        $frequencies = [
            'm1' => 1,
            'm2' => 2,
            'm3' => 3,
            'm6' => 6,
            'y1' => 1,
            'y2' => 2,
            'y3' => 3
        ];
    
        foreach ($frequencies as $freq => $interval) {
            $invoices = DB::table('invoice_recurring')
                ->where('frequency', $freq)
                ->where('status', 1)
                ->where(function ($query) use ($todayMonth, $todayYear) {
                    $query->where(function ($q) use ($todayMonth, $todayYear) {
                        $q->whereNotNull('last_invoice_id')
                          ->where(function ($q2) use ($todayMonth, $todayYear) {
                              $q2->whereMonth('last_invoice_created_date', '!=', $todayMonth)
                                 ->orWhereYear('last_invoice_created_date', '!=', $todayYear);
                          });
                    })
                    ->orWhere(function ($q) {
                        $q->whereNull('last_invoice_id')
                          ->whereNull('last_invoice_created_date');
                    });
                })
                ->get();
    
            foreach ($invoices as $invoice) {
                if (empty($invoice->starting_date)) continue;
                if ($invoice->number_of_invoice != 0 && $invoice->number_of_created_invoice >= $invoice->number_of_invoice) continue;
    
                $startDate = new DateTime($invoice->starting_date);
                if ($startDate > $today) continue;
    
                $startDay = (int) $startDate->format('j');
                if (!in_array($startDay, $dayArray)) continue;
    
                $intervalType = substr($freq, 0, 1); // 'm' or 'y'
                $intervalValue = $interval;
    
                $monthsDiff = ((int) $today->format('Y') - (int) $startDate->format('Y')) * 12 + ((int) $today->format('n') - (int) $startDate->format('n'));
                $yearsDiff = (int) $today->format('Y') - (int) $startDate->format('Y');
    
                if (
                    ($intervalType === 'm' && $monthsDiff % $intervalValue === 0) ||
                    ($intervalType === 'y' && $yearsDiff % $intervalValue === 0 && $startDate->format('j/n') === $today->format('j/n'))
                ) {
                    try {
                        $this->auto_create_invoice($invoice->invoice_id);
                        $counters[$freq]++;
                        echo "$freq {$counters[$freq]}<br/>";
                    } catch (Exception $e) {
                        Log::error("Invoice generation failed for ID {$invoice->invoice_id}: " . $e->getMessage());
                    }
                }
            }
        }
    
        $this->sendCronReport($counters);
    
        $updated = DB::table('cron_settings')->where('cron_id', 1)->update([
            'run_time' => date('Y-m-d H:i:s')
        ]);
    
        echo $updated ? 'Cron Runned Successfully' : 'Nothing to Update';
    }
    
    private function sendCronReport(array $counters) {
        $subject = 'Recurring Billing Cron - ' . date('Y-m-d H:i:s');
        $from_email = 'info@infolinkbd.com';
        $to = ['shahrussell25@gmail.com', 'info@sakifahmed.me'];
        $bcc = ['ne.zayed@gmail.com'];
    
        Mail::send('mail_template.billing_cron_run_email', $counters, function ($message) use ($to, $bcc, $subject, $from_email) {
            $message->to($to, 'Cron Mail')
                    ->bcc($bcc)
                    ->subject($subject)
                    ->from($from_email, 'Infolink Limited');
        });
    
        foreach ($counters as $key => $count) {
            echo ucfirst($key) . ": $count<br/>";
        }
    }

    
    
    //this cron before 30-06-2025
    function recurring_invoice_create_cronOld(){
       
       date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y');
        // echo date('Y-m-d H:i:a');
        // exit();
        $todayDay = date('j');
        $todayMonth = date('n');
        $todayYear = date('Y');
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        //$totalDaysOfTheMonth =  date('t', mktime(0, 0, 0, $todayMonth, 1, $todayYear)); 
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        
        
        /*1 month*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m1')->where('status',1)->get();
        //$monthlyRecurring = DB::select("SELECT * FROM invoice_recurring WHERE status = 1 and frequency='m1' AND starting_date < '2024-12-02' and invoice_id not in (SELECT invoice_recurring_id FROM invoice WHERE invoice_created_date = '2024-12-01 00:00:00')");
		
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                }else{
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if($startingDate_time <= $TodayDateString){
                        $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));

                        if(in_array($startingDay, $dayArray)){
                            //echo $invoice->invoice_id.'<br>';
                                
                            $this->auto_create_invoice($invoice->invoice_id);
                            $month1++;
                        }
                    }
                }
            }
            /*else{
                if($lastDateOfTheMonth){
                    $this->invoice_recurring_model->auto_create_invoice($invoice->invoice_id);
                }
            }*/
        }
        }
        /*2 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m2')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 2 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                                $month2++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*3 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m3')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 3 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month3++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*6 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m6')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 6 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month6++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*1 year*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y1')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $this->auto_create_invoice($invoice->invoice_id);
                    	$year1++;
                    }
                }
            }
        }
        }
        /*2 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y2')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 2 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year2++;
                        }
                    }
                }
            }
        }
        }
        /*3 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y3')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 3 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year3++;	
                        }
                    }
                }
            }
        }
        }
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Month- '.$month1;
		    echo '<br>Month2- '.$month2;
		    echo '<br>Month3- '.$month3;
		    echo '<br>Month6- '.$month6;
		    echo '<br>Year1- '.$year1;
		    echo '<br>Year2- '.$year2;
		    echo '<br>Year 3- '.$year3;
            Mail::send('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject($subject);
         
                 //$message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            });
			
		} 
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
    }
    
    function recurring_prepaid_invoice_create_cron(){
       
       date_default_timezone_set('Asia/Dhaka');
        $prepaid1 = $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        
        $today = date('Y-m-d');
        //$prepaidRecurring = DB::table('invoice_recurring')->where('frequency','p1')->where('status',1)->where('starting_date','<=',$today)->get();
		$prepaidRecurring = DB::select("SELECT * FROM invoice_recurring WHERE frequency= 'p1' and status='1' and DATE_FORMAT(starting_date,'%Y-%m-%d') <= '".date('Y-m-d')."' and invoice_id not in (SELECT invoice_recurring_id FROM `invoice` WHERE status in ('1','0') and frequency='p1')");
		
        // print_r($prepaidRecurring);
        // echo count($prepaidRecurring);
        // exit();
        if($prepaidRecurring){
        foreach($prepaidRecurring as $invoice){
                            
            $starting_date = date('Y-m-d', strtotime($invoice->starting_date));
            $today = date('Y-m-d');
                        
            $last_bill_pay = DB::table('payment')->where('client_id',$invoice->client_id)->where('payment_date',$today)->get();
            $due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0'])->get();
            
            $last_invoice_create = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0','2','3'])->orderBy('invoice_created_date', 'desc')->first();
            
            //print_r($last_invoice_create);
            //echo $last_invoice_create->invoice_created_date;
            if($last_invoice_create){
                $last_invoice_date = date('Y-m-d', strtotime($last_invoice_create->invoice_created_date. ' + 30 days'));
            }else{
                $last_invoice_date = $today;
            }
            // echo $last_invoice_date;
            // exit();
            
            if(!$last_bill_pay->isEmpty()){
                echo "Not payment empty ";
                echo $invoice->client_id.'=============<br/>';
                //exit();
                                
            }else{
                
                // echo $invoice->client_id;
                //             exit();
                    if($last_invoice_date <= $today){
                                $last_payment_invoice = DB::table('payment')->where('client_id',$invoice->client_id)->orderBy('payment_date', 'desc')->get();
                                if($last_invoice_create){
                                    $last_payment_invoice_date = date('Y-m-d', strtotime($last_payment_invoice[0]->payment_date. ' + 30 days'));
                                }else{
                                    $last_payment_invoice_date = $today;
                                }
                                // echo $last_payment_invoice_date;
                                // exit();
                                if($last_payment_invoice_date <= $today){
                                    echo 'create invoice ';
                                    echo $invoice->client_id."<br>";
                                    echo $starting_date.' - '.$today.'=============<br/>';
                                    $created_id = $this->auto_create_invoice($invoice->invoice_id);
                                    echo $created_id.'---'.$invoice->invoice_id.'=============<br/>';
                                    $prepaid1++;
                                }
                    }else{
                        echo "Not payment empty ";
                    echo $invoice->client_id.'=============<br/>';
                        
                    }
            }
            
            
            
        }
        }
        
        echo $prepaid1;
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',16)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
        
        exit();
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Prepaid Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('Prepaid'=>$prepaid1,'month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Prepaid- '.$prepaid1;
            Mail::send('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            });
			
		} 
         
        
    }
    
    function recurring_prepaid_invoice_create_cron_old(){
       
       date_default_timezone_set('Asia/Dhaka');
        $prepaid1 = $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y');
        // echo date('Y-m-d H:i:a');
        // exit();
        $todayDay = date('j');
        $todayMonth = date('n');
        $todayYear = date('Y');
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        $today = date('Y-m-d');
        /*1 month*/
        $prepaidRecurring = DB::table('invoice_recurring')->where('frequency','p1')->where('status',1)->where('starting_date','<=',$today)->get();
        // print_r($prepaidRecurring);
        // echo count($prepaidRecurring);
        // exit();
        if($prepaidRecurring){
        foreach($prepaidRecurring as $invoice){
            
            //$due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->where('status',1)->get();
            $due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0'])->get();
            if(!$due_invoice_check->isEmpty()){
                //echo $invoice->client_id."<br>";
            }else{
                ////echo $invoice->client_id."not fount<br>";
                if(!empty($invoice->starting_date)){
                    if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                        
                    }else{
                        // $startingDate_time = date('U', strtotime($invoice->starting_date));
                        // $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                        // $TodayDateString = $myDateTime->format('U');
                        
                        $starting_date = date('Y-m-d', strtotime($invoice->starting_date));
                        $today = date('Y-m-d');
                        
                        $last_bill_pay = DB::table('payment')->where('client_id',$invoice->client_id)->where('payment_date',$today)->get();
                        
                        // print_r($last_bill_pay);
                        // exit();
                        
                        if($last_bill_pay->isEmpty()){
                            echo $invoice->client_id;
                            exit();
                            if ($starting_date <= $today) {
                                echo 'create invoice ';
                                echo $invoice->client_id."<br>";
                                echo $starting_date.' - '.$today.'=============<br/>';
                                $created_id = $this->auto_create_invoice($invoice->invoice_id);
                                echo $created_id.'---'.$invoice->invoice_id;
                            }else{
                                // echo 'date not match';
                                // echo $starting_date.' - '.$today.'=============<br/>';
                            }
                        }
                        
                        
                        // if($startingDate_time <= $TodayDateString){
                        //     $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        //     $startingDay = date('j', strtotime($invoice->starting_date));
    
                        //     if(in_array($startingDay, $dayArray)){
                        //         echo $invoice->client_id."<br>";
                        //         //$this->auto_create_invoice($invoice->invoice_id);
                        //         $month1++;
                        //     }
                        // }
                    }
                }
            }
            
            //echo $invoice->client_id.'<br>';
            
            
            
        }
        }
        
        
        
        exit();
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Prepaid Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('Prepaid'=>$prepaid1,'month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Prepaid- '.$prepaid1;
            Mail::send('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            });
			
		} 
         
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
// 		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
// 		if($update){.
// 		    echo 'Cron Runned Successfully';
// 		}else{
// 		    echo 'Nothing to Update';
// 		}
    }
    
    public function auto_create_invoice($recurring_id = NULL)
    {
        if(!$recurring_id){
            return;
        }
        
        $cur_time = date('Y-m-d');
        $invoice_created_date = date('Y-m-d 00:00:00');
        /*echo $recurring_id;
        echo $cur_time;die;*/
        //$data = DB::table('invoice_recurring')->where('invoice_id',$recurring_id)->first();
        $data = DB::select("SELECT * FROM invoice_recurring WHERE invoice_id = $recurring_id and invoice_id not in (SELECT invoice_recurring_id FROM invoice WHERE invoice_created_date ='$invoice_created_date' and invoice_recurring_id = $recurring_id)");
        
        if(!$data){
            return;
        }
        $data = $data[0];
        $invoice_table = array(
            'client_id' 			=> $data->client_id,
            'company_name'          => $data->company_name,
            'vat_reg_number'        => $data->vat_reg_number,
            'client_address_id'     => $data->client_address_id,
            'invoice_recurring_id'  => $recurring_id,
            'group_id' 				=> $data->group_id,
            'recurring_id'          => $recurring_id,
            'created_by' 			=> $data->admin_id,
            'invoice_created_date' 	=> date('Y-m-d'),
            'status' 				=> 1,
            'vat_status' 			=> $data->vat_status,
            'frequency' 			=> $data->frequency,
            'discount' 				=> $data->discount,
            'bill_period' 			=> $this->get_bill_period($data->bill_period),
            'note'                  => $data->note,
            'reference_by'	=> $data->reference_by,
            'referred_employee_id'	=> $data->referred_employee_id,
            'referred_employee_commission' => $data->referred_employee_commission
        );
        $insert_id = DB::table('invoice')->insertGetId($invoice_table);
        $i = 0;
        $new_number = 0;
        if($data->number_of_invoice > 0) {
            $number_of_invoice = $data->number_of_created_invoice;
            $new_number = intval($number_of_invoice) + 1;
            
            $data_update = array(
                            'number_of_created_invoice'=>$new_number
                            );
            DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
            
        }
        if($data->number_of_invoice != 0 && $data->number_of_invoice <= $new_number){
            $data_update = array(
                            'number_of_created_invoice'=> 0,
                            'status'=> 0
                            );
            DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
            
        }
        
        /***** for system log *****/
        $invoice_details_for_system_log = '';
        $recurring_items = DB::table('invoice_recurring_details')->where('invoice_id',$recurring_id)->get();
        
        /***** invoice details table******/
        foreach($recurring_items as $item) {
            $invoice_details_data = array(
                'invoice_id' => $insert_id,
                'income_category_id' => $item->income_category_id,
                'package_id' => $item->package_id,
                'title' => $item->title,
                'details' => $item->details,
                'vat' => $item->vat,
                'quantity' => $item->quantity,
                'price' => $item->price,
                'invoice_recurring_details_id' => $item->invoice_details_id,
				'invoice_type' => $item->invoice_type,
				'connectivity_type' => $item->connectivity_type,
				'download_speed' => $item->download_speed,
				'd_unit' => $item->d_unit,
				'upload_speed' => $item->upload_speed, 
				'u_unit' => $item->u_unit,
				'location' => $item->location
            );
            DB::table('invoice_details')->insert($invoice_details_data);

            $invoice_details_for_system_log .= 'Package Title: ' . $item->title . ', ';
            $invoice_details_for_system_log .= 'Details: ' . $item->details . ', ';
            $invoice_details_for_system_log .= 'Vat: ' . $item->vat . ', ';
            $invoice_details_for_system_log .= 'Quantity: ' . $item->quantity . ', ';
            $invoice_details_for_system_log .= 'Price: ' . $item->price . ', ';
            $i++;
        }
        
        if($data->auto_create_vat == 1){
            if($insert_id){
				$invoice_id = $insert_id;
				$vat_serial = array(
					'invoice_id' => $invoice_id,
					'admin_id' => 13
				);
				DB::table('invoice_vat_serial')->insert($vat_serial);
    		}
        }
        /*********** System Log ******************/
        $client_info = DB::table('client')->where('client_id',$data->client_id)->first();

        $system_log = array(
            'admin_id' 		=> $data->admin_id,
            'client_id' 	=> 0,
            'action'		=> 'Created',
            'description'	=>
                ',Invoice for - Client title:'.$client_info->client_title.
                ',Contact person: '.$client_info->contact_person.
                ',Invoice created date: '.date('Y-m-d').
                ',Invoice No: IL- '.$insert_id.
                ',Invoice status: '.$data->status.
                ',Discount:'.$data->discount.
                ',Bill Period:'.$invoice_table["bill_period"].
                ',Package details :'.$invoice_details_for_system_log,
            'ip_address'	=> $_SERVER['REMOTE_ADDR']
        );
        DB::table('system_log')->insert($system_log);
        /******* End  System Log *****************/
        //update invoice_id and date recurring table start
        $data_update = array(
                            'last_invoice_created_date'=> date('Y-m-d'),
                            'last_invoice_id' => $insert_id
                            );
        DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
        //update invoice_id and date recurring table start
        
        if($data->email_notification==1)
        {
            $invoice_id = $insert_id;
           
            $notification 	= DB::table('notification')->where('id',9)->first();
            
            $row = DB::table('invoice')
                ->join('client', 'invoice.client_id', '=', 'client.client_id')
                ->select('client.client_title','invoice.bill_period')
                ->where('invoice.invoice_id',$invoice_id)
                ->first();
           	
			$s = str_replace('[[client_title]]',$row->client_title,$notification->subject);
			
			$s1 = str_replace('[[bill_period]]',$row->bill_period,$s);
            
		    $type = $s1;
	        $subject = $s1;
	        $from_email = $notification->from_email;
			
			$emails = [$client_info->client_email1,$client_info->client_email2];
			
		    $data = array('invoice_id'=>$invoice_id);
		    
		    $c_email = array();
			if($client_info->client_email1_status != 0){
			    $emails = [$client_info->client_email1];
			    $c_email = array($client_info->client_email1);
			}elseif($client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email2];
			    $c_email = array($client_info->client_email2);
			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email1,$client_info->client_email2];
			    $c_email = array($client_info->client_email1,$client_info->client_email2);
			}
			
			if(!empty($c_email)){
                // Mail::send('mail_template.billing_cron_invoice_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                //      $message->to($emails, $type)->subject($subject);
                //      $message->from($from_email,'Infolink Limited');
                // });
			}
			
		
        }
        return $insert_id;
    }
    
    function get_bill_period($bperiod){
        $m1 = str_replace('[[current_month]]', date('F'), $bperiod);
        $m2 = str_replace('[[previous_month]]', date('F', strtotime(" -1 month ")), $m1);
        $m3 = str_replace('[[next_month]]', date('F', strtotime(" +1 month ")), $m2);
        $m4 = str_replace('[[current_year]]', date('Y'), $m3);
        $m5 = str_replace('[[previous_year]]', date('Y', strtotime("-1 year")), $m4);
        $m6 = str_replace('[[next_year]]', date('Y', strtotime("+1 year")), $m5);
        $m7 = str_replace('[[current_day]]', date('d'), $m6);
        $m8 = str_replace('[[next_day]]', date('d', strtotime("+1 day")), $m7);
        $m9 = str_replace('[[previous_day]]', date('d', strtotime("-1 day")), $m8);
        return $m9;
    }
    
    public function daily_report()
	{
		$today=date('dd/mm/yyyy');
		$invoice_sql = "SELECT inv.invoice_id,inv.created_by,inv.frequency,inv.invoice_created_date,inv.status,cl.client_title,b.branch_name,inv.bill_period,invdet.vat,invdet.quantity,invdet.price
                		FROM invoice inv
                		LEFT JOIN invoice_details invdet ON inv.invoice_id = invdet.invoice_id
                		LEFT JOIN client cl ON cl.client_id = inv.client_id
                		LEFT JOIN branch b ON b.branch_id = cl.branch_id
                		WHERE DATE_FORMAT(inv.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";

		$invoices = DB::select($invoice_sql);

		$payment_sql = "SELECT pay.id,pay.invoice_id,pay.accept_by,pay.payment_date,pay.payment_type,pay.reference_number,pay.amount,pay.admin_id,cl.client_title,b.branch_name,inv.status AS invoice_status,pay.status AS payment_status
                		FROM payment pay
                		LEFT JOIN invoice inv ON inv.invoice_id = pay.invoice_id
                		LEFT JOIN client cl ON cl.client_id = pay.client_id
                		LEFT JOIN branch b ON b.branch_id = cl.branch_id
                		WHERE DATE_FORMAT(pay.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";

		$payments = DB::select($payment_sql);
		
		$clients_sql = "SELECT cl.*,b.branch_name
		                FROM client as cl
		                LEFT JOIN branch b ON b.branch_id = cl.branch_id
		                WHERE DATE_FORMAT(cl.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$clients = DB::select($clients_sql);
		
		$connection_sql = "SELECT * 
		                    FROM connection_list 
		                    WHERE DATE_FORMAT(timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$connections = DB::select($connection_sql);
		
		$leads_sql = "SELECT * FROM leads WHERE DATE_FORMAT(created_at,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$leads = DB::select($leads_sql);
		
		$expense_sql = "SELECT * FROM expense WHERE DATE_FORMAT(clear_date,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$expenses = DB::select($expense_sql);
		
		//return view("mail_template.daily_report",compact('invoices','payments','clients','connections','leads'));
		
		//$emails = ['zayed@braintreebd.com', 'shahrussell25@gmail.com'];
		$data = array('invoices'=>$invoices,'payments'=>$payments,'clients'=>$clients,'connections'=>$connections,'leads'=>$leads,'expenses'=>$expenses);
        Mail::send('mail_template.daily_report', $data, function($message) {
         $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject('Regarding InfoLink Daily Billing Details '.date('Y-m-d'));
         $message->from('info@infolinkbd.com','Infolink Limited');
        });
        
		/*$data['run_time'] = date('Y-m-d H:i:s');
		DB::table('cron_settings')->where('cron_id',2)->update($cron_log);*/ 
		
		$cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		
		$update = DB::table('cron_settings')->where('cron_id',2)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
          
	}
	
	public function ssl_comm_admin_payment_check(){
	    
	    $link = "https://securepay.sslcommerz.com/validator/api/merchantTransIDvalidationAPI.php?tran_id=[[trn_id]]&store_id=infolinkbdlive&store_passwd=5EC4DDB3E972453074";
                
        date_default_timezone_set('Asia/Dhaka');
         
        $date_tele =  date('Y-m-d H:i:s');
        $date_tele =  date('Y-m-d H:i:s', strtotime('-10 minutes',strtotime($date_tele)));
        // echo $date_tele;
        // exit();
        
	    $due_list = DB::select("SELECT * FROM wallet_secure_payment WHERE payment_status = 1 and timestamp < '$date_tele' ORDER BY wallet_secure_payment.timestamp DESC ");
	    //echo count($due_list);die;
	    
        $i = 0;
        if($due_list){
	            foreach($due_list as $row){
	                
	                
	                
	                $api = str_replace('[[trn_id]]',"$row->tran_id",$link);
	                
	                $ch = curl_init();
    				curl_setopt($ch, CURLOPT_URL, $api);
    				curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    				$message = curl_exec($ch);
    				curl_close($ch);
    				
    				
    				
    				$data = json_decode($message, true);
    				$elements = $data['element'];
    				// echo $data['no_of_trans_found'];
    				// exit();
    				
    				$in_val = 0;
    				$valid_val = 0;
    				if($data['no_of_trans_found'] > 1){
    				    while($in_val<$data['no_of_trans_found']){
    				        $status = $elements[$in_val]['status'];
    				        if($status == 'VALID' || $status == 'VALIDATED')
    				        {
    				            $valid_val = $in_val;
    				        }
    				        $in_val++;
    				    }
    				}
    				
    				//echo $valid_val;
    				$status = $elements[$valid_val]['status'];
    				// echo '<br>'.$status;
    				// exit();
    				
        // 		    $status = $elements[0]['status'];
        				
    				if($data['no_of_trans_found'] == 0){
    				    $error = $elements[$valid_val]['status'];
    				}else{
        				
        				$error = $elements[$valid_val]['error'];
    				}
    				
    				
    				
    				if($status == 'VALID' || $status == 'VALIDATED')
    				{
    				    print_r($message);
    				    echo '<br><br><br>'.$status;
    				    
    				    //exit();
    				    
    				    $bank_tran_id = $elements[$valid_val]['bank_tran_id'];
                        $base_fair = $elements[$valid_val]['base_fair'];
                        $card_brand = $elements[$valid_val]['card_brand'];
                        $card_issuer = $elements[$valid_val]['card_issuer'];
                        $card_issuer_country = $elements[$valid_val]['card_issuer_country'];
                        $card_issuer_country_code = $elements[$valid_val]['card_issuer_country_code'];
                        $card_no = $elements[$valid_val]['card_no'] ;
                        $card_sub_brand = $elements[$valid_val]['card_sub_brand'];
                        $card_type = $elements[$valid_val]['card_type'];
                        $currency = $elements[$valid_val]['currency'] ;
                        $currency_amount = $elements[$valid_val]['currency_amount'];
                        $currency_rate = $elements[$valid_val]['currency_rate'];
                        $currency_type = $elements[$valid_val]['currency_type'];
                        $risk_level = $elements[$valid_val]['risk_level'];
                        $risk_title = $elements[$valid_val]['risk_title'];
                        $store_amount = $elements[$valid_val]['store_amount'];
                        //$updated_by = '3';
                        $response = $elements;
                        
                        $tran_id = $row->tran_id;
                        
                        $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,
                            //'updated_by'=>$updated_by,
                            'currency_type' =>$currency_type,
                            'currency_amount'=>$currency_amount,
                            'currency_rate'=>$currency_rate,
                            'base_fair'=>$base_fair,
                            'gateway_response' => $response,
                            'updated_by' => 3
                        );
                    $update = DB::table('wallet_secure_payment')->where('tran_id',$tran_id)->update($update_data);
                    if($update){
                        
                        
                        
            			if(empty($row->admin_id)){
            				
            			}else{
            			    $new_desc = "Cash Deposit By E-".$row->admin_id.". SSL-Comerce Transaction ID-".$tran_id;
                            $adjustment_data_tr = array(
                                'emp_id' => $row->admin_id,
                                'transfer_type' => '1',
                                'amount' => $row->amount,
                                'transfer_date' => date('Y-m-d'),
                                'description' => $new_desc,
                                'added_by' => $row->admin_id,
                                'added_date' => date('Y-m-d H:i:s'),
                            );
                            $insert_id = DB::table('employee_wallet')->insertGetId($adjustment_data_tr);
                            
                            $new_desc_tr = "Wallet Payment By E-".$row->admin_id.". SSL-Comerce Transaction ID-".$tran_id;
                            $adjustment_data = array(
                                'date' => date('Y-m-d'),
                                'description' => $new_desc_tr,
                                'adjustment_type' => '1',
                                'amount' => $row->amount,
                                'payment_type' => '2',
                                'account_type' => '0',
                                'account_id' => '14',
                                'admin_id' => $row->admin_id,
                                'status' => '2',
                            );
                            $insert_id = DB::table('account_adjustment')->insertGetId($adjustment_data);
                            
            			    
            			}
                        
                    }
                        
    				    exit();
    				}else{
    				    echo '<br><br><br>';
    				    print_r($message);
    				    
    				    echo '<br><br><br>'.$status;
    				    
    				    
    				    
    				    $data_update = array(
            				'payment_status' => '4',
            				'error' => $error,
            				'updated_by' => '3',
                            'gateway_response' => $elements,
            			);
                		DB::table('wallet_secure_payment')->where('tran_id',$row->tran_id)->update($data_update);
    				}
    				
    				
    				//old
    				//echo $message;
                    //if(strpos($message, "SMS SUBMITTED") !== false){
                    
                    
                    
                    
    				// if(strpos($message, "200") !== false){
        //                 echo $row->client_id." - SMS Send Done.<br>";
        //         		$i++;
            		
        //             }
    				//exit();
    				//echo $row->client_id." - SMS Send Done.<br>";
    				
    				
	                
	            }
        }
        
        
        
		
	    
	}
	
	public function payment_clear()
	{
		$today=date('dd/mm/yyyy');
		$payment_sql = "SELECT * FROM payment WHERE payment_type in (9) and account_id in (33) AND status = 0";

		$payments = DB::select($payment_sql);
		
		
		foreach($payments as $item) {
        
        $payment_id = $item->id;
	    
	    $payment_data = DB::table('payment')->where('id',$payment_id)->first();
	    
	    $invoice_id = $payment_data->invoice_id;
        
        $invoice_data = DB::table('invoice')->where('invoice_id',$invoice_id)->first();
        
        $item_list = DB::table('invoice_details')->where('invoice_id',$invoice_id)->get();
		
        $amount = 0;
		$vat = 0;
		$v = 0;
		foreach($item_list as $invoice_info)
		{
			$amount += (float)$invoice_info->price * (float)$invoice_info->quantity;
			$vat += ((float)$invoice_info->quantity * (float)$invoice_info->price * (float)$invoice_info->vat*.01);
			$v += (float)$invoice_info->vat;
		}
		$total_vat = $vat;
		$total_amount = $vat+$amount-$invoice_data->discount;
		
		$total_amount = number_format($total_amount,2);
		
        
        
        
        if($payment_data->status == $invoice_data->status){
            if($total_amount == $payment_data->amount){
                
                echo $payment_id.'-Clear<br>';
        
                $today_date = date('Y-m-d');
				$status_array = array(
					'status_clear_date'	=> $today_date,
                    'status'				 	 => '1',
                    'accept_by' 				 => '0',
                    'admin_id'   				 => '0',
				);
				DB::table('payment')->where('id',$payment_id)->update($status_array);
				
					$i_status = '3';
    		DB::table('invoice')->where('invoice_id',$invoice_id)->update(array('status'=>$i_status));
    		
            $check = DB::table('invoice_vat_serial')->where('invoice_id',$invoice_id)->first();
			if($check){
			}else{
				$client_id = $invoice_data->client_id;
			    $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' => $invoice_id,
        						'admin_id' => '122'
        					);
        				   DB::table('invoice_vat_serial')->insert($vat_serial);
			            }
			        }
			    }
			}
                
            }else{ echo $payment_id.'- NOT Clear (Amount)'.$total_amount.'=='.$payment_data->amount.'<br>'; }
            
            }else{ echo $payment_id.'- NOT Clear (Status)<br>'; }
        }
          
	}
	
	public function recurring_check_invoice_created(){
	    date_default_timezone_set('Asia/Dhaka');
        $start = new \DateTime('2025-08-01');
        $end = new \DateTime('2025-08-31');
        
        // Add one day to $end because DatePeriod excludes the end date
        $end->modify('+1 day');
        
        $interval = new \DateInterval('P1D');
        $period = new \DatePeriod($start, $interval, $end);
        
        $thisMonthDays = [];
        foreach ($period as $date) {
            $thisMonthDays[] = $date->format('d/m/Y');
        }
        $i = 0;
        foreach($thisMonthDays as $day){
            $todayDate = $day;
            $todayDay = date('j',strtotime($day));
            $todayMonth = date('n',strtotime($day));
            $todayYear = date('Y',strtotime($day));
            $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
            
            $dayArray = array();
            $lastDateOfTheMonth = 0;
            if(intval($todayDay) == intval($totalDaysOfTheMonth)){
                $lastDateOfTheMonth = 1;
                $highestDate = 31;
                for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                    $dayArray[] = $i;
                }
            }else{
                $dayArray[] = $todayDay;
            }
            
            $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->get();
            
            if($monthlyRecurring){
                foreach($monthlyRecurring as $invoice){
                    if(!empty($invoice->starting_date)){
                        if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                        }else{
                            $startingDate_time = date('U', strtotime($invoice->starting_date));
                            $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                            $TodayDateString = $myDateTime->format('U');
                            if($startingDate_time <= $TodayDateString){
                                $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                                $startingDay = date('j', strtotime($invoice->starting_date));
        
                                if(in_array($startingDay, $dayArray)){
                                    
                                    $current_month = date('m');
            						$current_year = date('Y');
            						$check_bill = DB::select("SELECT invoice_id,client_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
            						if($check_bill)
            						{
            						    //$i++;
            						    //echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' Created<br>';
                                        //$month1++;
            						}else{
            						   $i++;
            						   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                                        $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                                        
                                        $recurringUrl = 'https://admin.infolinkbd.com/recurring/details/' . base64_encode($invoice->invoice_id);
                                        $recurring_details = '<a href="' . $recurringUrl . '" target="_blank">RID-' . $invoice->invoice_id . '</a>';
                                        
                                        echo $i . ') ' . $client_details . ' = ' . $invoice->starting_date . ' = Frequency - ' . $invoice->frequency . ' - Recurring ID - ' . $recurring_details . ' = This month Invoice not created <br>';

            						}
                                }
                            }
                        }
                    }
                }
            }
        }
	}
	
	public function recurring_check_invoice_duplicate(){
	    date_default_timezone_set('Asia/Dhaka');

        $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->get();
        
        if($monthlyRecurring){
            $j = 0;
            foreach($monthlyRecurring as $invoice){
                $current_month = date('m');
				$current_year = date('Y');
				$check_bill = DB::select("SELECT invoice_id,client_id,status, reason_cancel, invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id' AND invoice_recurring_id = '$invoice->invoice_id'");
				if (count($check_bill) > 1) {
				    $j++;
				   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                    $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                    
                    echo $j . ') ' . $client_details . ' = Duplicate Invoice found RID- '. $invoice->invoice_id .'<br>';
                    echo '------------------------------<br/>';
                    foreach($check_bill as $key => $row){
                        if($row->status==1)
						{
							$status = "<span style='color:red;'>Due</span>";
						}elseif($row->status==3){
							$status = "<span style='color:green;'>Paid</span>";
						}elseif($row->status==2){
							$status = "<span style='color:blue;'>Partialy Paid</span>";
						}elseif($row->status==0){
							$status = "<span style='color:blue;'>Pending</span>";
						}elseif($row->status==4){
							$status = "Cancel";
						}elseif($row->status==5){
							$status = "Deleted";
						}elseif($row->status==6){
							$status = "Bad Debt";
						}else{
							$status = '';
						}
						$invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                        $invoice_details = '<a href="' . $invoiceUrl . '" target="_blank">' . 'IL-'.$row->invoice_id.' - '.$status .'</a> '.$row->reason_cancel;
    		            echo $invoice_details.'<br/>';
    		            if($key == 0){
    		                $main_invoice_id = $row->invoice_id;
    		            }
    		            if($key == 1){
    		                //echo '<b style="color:red">'.$row->invoice_id.' Mark for cancel </b>'.$main_invoice_id.'<br>';
    		              //  $update = DB::table('invoice')->where('invoice_id',$row->invoice_id)->update(array('status'=> 4,'reason_cancel' => "Cancelled for duplication. Duplicate invoice ID: IL-$main_invoice_id",'remarks'=>''));
    		              //  if($update){
    		              //      echo $row->invoice_id.' - Updated successfuly <br/>';
    		              //  }
    		                
    		            }
                    }
                    echo '--------------------------- <br/>';
				}
            }
        }
        
	}
	
	public function recurring_check_invoice_duplicate_other(){
	    date_default_timezone_set('Asia/Dhaka');

        $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->get();
        
        if($monthlyRecurring){
            $j = 0;
            foreach($monthlyRecurring as $invoice){
                $current_month = date('m');
				$current_year = date('Y');
				$check_bill = DB::select("SELECT invoice_id,client_id,status, reason_cancel,invoice_recurring_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
				if (count($check_bill) > 1) {
				    $j++;
				   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                    $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                    
                    echo $j . ') ' . $client_details . ' = Duplicate Invoice found RID- '. $invoice->invoice_id .'<br>';
                    echo '------------------------------<br/>';
                    foreach($check_bill as $key => $row){
                        if($row->status==1)
						{
							$status = "<span style='color:red;'>Due</span>";
						}elseif($row->status==3){
							$status = "<span style='color:green;'>Paid</span>";
						}elseif($row->status==2){
							$status = "<span style='color:blue;'>Partialy Paid</span>";
						}elseif($row->status==0){
							$status = "<span style='color:blue;'>Pending</span>";
						}elseif($row->status==4){
							$status = "Cancel";
						}elseif($row->status==5){
							$status = "Deleted";
						}elseif($row->status==6){
							$status = "Bad Debt";
						}else{
							$status = '';
						}
						$invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                        $invoice_details = '<a href="' . $invoiceUrl . '" target="_blank">' . 'IL-'.$row->invoice_id.' - '.$status .'</a> RID-'.$row->invoice_recurring_id;
    		            echo $invoice_details.'<br/>';
    		            
                    }
                    echo '--------------------------- <br/>';
				}
            }
        }
        
	}
	
	public function recurring_check_invoice_id(){
	    
        $currentMonth = date('m'); 
        $currentYear = date('Y'); 
        
        $monthlyRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->where(function ($query) use ($currentMonth, $currentYear) {
                $query->where(function ($q) use ($currentMonth, $currentYear) {
                    // Condition-1: last_invoice_id ache and date current month na
                    $q->whereNotNull('last_invoice_id')
                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                      });
                })
                ->orWhere(function ($q) {
                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                    $q->whereNull('last_invoice_id')
                      ->whereNull('last_invoice_created_date');
                });
            })
            ->get();

	    echo '<pre>';
	    print_r($monthlyRecurring);die;
	}
}
