<?php

namespace App\Http\Controllers;

use App\Invoice;
use App\QuotationProduct;
use App\Product;
use App\ProductSerialNo;
use App\Quotation;
use App\CustomProduct;
use App\ExportedJson;
use App\InvoiceProduct;
use DB;
use Illuminate\Http\Request;
use File;
use Illuminate\Support\Facades\Log;
use Exception;


class InvoiceController extends Controller
{
    function __construct()
    {
         $this->middleware('permission:invoice-list|invoice-create|invoice-edit|invoice-delete', ['only' => ['index','store']]);
         $this->middleware('permission:invoice-create', ['only' => ['create','store']]);
         $this->middleware('permission:invoice-edit', ['only' => ['edit','update']]);
         $this->middleware('permission:invoice-delete', ['only' => ['destroy']]);
    }
    public function index(Request $request)
    {
      
         $start = isset($request)?($request->start_date
        ? date('Y-m-d',strtotime($request->start_date)):now()->startOfMonth()):now()->startOfMonth();

    $end =  isset($request)?($request->end_date
        ? date('Y-m-d',strtotime($request->end_date)):now()->endOfMonth()):now()->endOfMonth();

        if(($request->user()->can('curd-all-invoice'))){
            $invoice=DB::table('companies')
				->select('companies.*','invoices.*','exported_jsons.path','users.name as created_by')
                ->join('invoices','invoices.company_id','=','companies.id')
				->leftjoin('exported_jsons','exported_jsons.id','=','invoices.json_id')
				->join('users', 'invoices.generated_by', '=', 'users.id')

                ->where("invoices.deleted_at",null)
                ->whereBetween('invoices.created_at', [$start, $end])
                ->orderBy('invoices.created_at', 'desc')
                ->get(); 

        }else{
            $invoice=DB::table('companies')
				->select('companies.*','invoices.*','exported_jsons.path')
                ->join('invoices','invoices.company_id','=','companies.id')
				->leftjoin('exported_jsons','exported_jsons.id','=','invoices.json_id')
                ->where("invoices.deleted_at",null)
                ->where("invoices.generated_by",'=',$request->user()->id)
                ->whereBetween('invoices.created_at', [$start, $end])
                ->orderBy('invoices.created_at', 'desc')
                       //->take(500)
                       ->get(); 
        }
        
		
      return view('invoices.index',compact('invoice','start',
        'end'));
    }

    public function shippingshow(Request $request)
    {
        $invoice_id=$request->invoice_id;

        $invoice=DB::table('invoices')
                ->where("invoices.id",$invoice_id)
                ->first();

        $pi_info=DB::table('p_i_s')
                ->where('pi_no',$invoice->pi_no)
                ->first();

        /*$products=DB::table('quotations')
                ->select('quotations.*','quotation_products.*','custom_products.name')
                ->join('quotation_products','quotation_products.quotation_no','=','quotations.quotation_no')
                ->rightjoin('custom_products','custom_products.id','=','quotation_products.item_id')
                ->where('quotations.id','=',$pi_info->quotation_id)
                ->get();*/
		$products = Invoice::where('id','=',$invoice_id)
                ->first()->getProducts()->get();
        $company=DB::table('companies')
                ->where("id",$invoice->company_id)
                ->first();

        return response()->json(['invoice'=>$invoice,'company'=>$company,'pi'=>$pi_info,'products'=>$products]);
    }

    public function shippingadd(Request $request)
    {
        $invoice_id=$request->invoice_id;
        $shipping_details=DB::table('invoices')->where('id',$invoice_id)
        ->update(
            [
                'courier_name'=>  $request->invoice_trackcourier,
                'tracking_id'=>  $request->invoice_tracktracking,
                'shipping_date'=>  $request->invoice_trackdate,
            ]
            );
        return response()->json($shipping_details);
    }

    public function store(Request $request)
    {
		
         $invoice= new Invoice([

            'invoice_no'=>$request->invoice_no,
            'pi_no'=>$request->pi_no,
            'quotation_no'=>$request->quotation_no,
            'company_id'=>$request->company_id,
            'courier_name'=>"null",
            'tracking_id'=>"null",
            'shipping_date'=>NULL,
			'invoice_type' => $request->invoice_type,
            'generated_by'=>$request->generated_byinvoice,
            'note'=>$request->note,
			'sales_person' => $request->sales_person,

         ]);
         $isInvoiceSaved = $invoice->save();
		 if($isInvoiceSaved){
		     
			 Log::info($request->invoice_no."Invoice created");
			 $quotationData = DB::table('quotations')->where('quotation_no',$request->quotation_no)->first();
			 $pidata = DB::table('p_i_s')->where('pi_no',$request->pi_no)->first();
			 $affected = DB::table('invoices')
				  ->where('invoice_no', $request->invoice_no)
				  ->update(['note' => $quotationData->note,
				  'bank_id'=>$pidata->bank_id ,
				  'po_number' => $pidata->po_number,
				  'paymnet_term'=>$pidata->paymnet_term,
				  'billing_id'=>$quotationData->billing_id,
				  'shipping_id'=>$quotationData->shipping_id]);
			
			$productData = DB::table('quotation_products')->where('quotation_no',$request->quotation_no)->get();			
				
			for($i =0; $i < count($productData); $i++){
                $answers[] = [    
                    'invoice_no' =>$request->invoice_no,
                    'item_id' => $productData[$i]->item_id,
                    'HSN'=>$productData[$i]->HSN,
                    'unit_cost' => $productData[$i]->unit_cost,
                    'quantity' => $productData[$i]->quantity,
                    'c_s_gst' => $productData[$i]->c_s_gst,
                    'i_gst' => $productData[$i]->i_gst,
                ];
        }
        
        $data=DB::table('invoice_products')->insert($answers);
			 // $this->generateSingleInvoiceJson($request->invoice_no);
			 if($data){
				 Log::info($request->invoice_no."Invoice item created");
			 }else{
				 Log::error($request->invoice_no."Invoice item not created");
			 }

		 }
         return response()->json($invoice);
    }

    public function show(Invoice $invoice,$id)
    {
        
        $invoice=Invoice::find($id);

        $pi_info=DB::table('p_i_s')
                ->where('pi_no',$invoice->pi_no)
                ->first();

        $bank_detail=DB::table('bank_details')
                ->where('id',$pi_info->bank_id)
                ->first();

        $quotation=DB::table('quotations')
                ->where('quotation_no',$invoice->quotation_no)
                ->first();

        $company_details=DB::table('companies')
                ->where('id',$invoice->company_id)
                ->first();
              
        $products_details=DB::table('quotations')
                ->select('quotations.*','quotation_products.*','custom_products.name')
                ->join('quotation_products','quotation_products.quotation_no','=','quotations.quotation_no')
                ->rightjoin('custom_products','custom_products.id','=','quotation_products.item_id')
                ->where('quotations.id','=',$pi_info->quotation_id)
                ->get();

        $billing_details=DB::table('company_addresses')
                ->leftjoin('cities','cities.id','=','company_addresses.city')
                ->leftjoin('states','states.state_id','=','company_addresses.state')
                ->where('address_id','=',$quotation->billing_id)->first();

        $shipping_details=DB::table('company_addresses')
                ->leftjoin('cities','cities.id','=','company_addresses.city')
                ->leftjoin('states','states.state_id','=','company_addresses.state')
                ->where('address_id','=',$pi_info->pi_shippingid)->first();
		$data = $invoice;
        return view('invoices.show',compact('data','pi_info','quotation','company_details','products_details','billing_details','shipping_details','bank_detail'));
    }

    public function destroy(Invoice $invoice,Request $request)
    {
        $id=$request->gettingid;

            $invoice=Invoice::find($id);
            
            if(!is_null($invoice))
            {
                
                $invoice->delete();
				Log::info($invoice->invoice_no."deleted");
            }
            
        return response()->json($invoice);
    }

    public function trash(Request $request)
    {
        $invoice=Invoice::onlyTrashed()->get();
        return response()->json($invoice);
                
    }

    public function destroyperm(Request $request)
    {
        $id=$request->gettingid;

        $invoice=Invoice::withTrashed()->find($id);
        
        if(!is_null($invoice))
        {
			$invoiceProducts = InvoiceProduct :: where('invoice_no',$invoice->invoice_no)->get();
				foreach($invoiceProducts as $products){
					$products->delete();
				}
			$jsons = ExportedJson :: where('id',$invoice->json_id)->get();
			{
				foreach($jsons as $json){
					$file_path = $json->path;
					if(File::exists($file_path)) File::delete($file_path);
					$json->delete();
				}
			}			
            $invoice->forceDelete();
		Log::info($invoice->invoice_no."deleted permenent");

        }
        
        return response()->json($invoice); 
    }

    public function restore(Request $request)
    {
        $id=$request->gettingid;

        $invoice=Invoice::withTrashed()->find($id);
        
        if(!is_null($invoice))
        {
            $invoice->restore();
		Log::info($invoice->invoice_no." restored");

        }
        
        return response()->json($invoice); 
    }

    public function ExportFromTTB(Request $request)
	{
		return view('invoices.exportttb');
	}
	
	public function getExportDatafromDate(Request $request)
	{
	    $startDate = date('Y/m/d',strtotime($request->selectedStart));
	    $endDate = date('Y/m/d',strtotime($request->selectedEnd));

        // Test database connection
		try {
			DB::connection('mysql2')->getPdo();
		} catch (\Exception $e) {
			die("Could not connect to the database.  Please check your configuration. error:" . $e );
		}

			$exportData = DB::connection('mysql2')
			->select('select f.* ,g.value as hsn FROM(
			SELECT d.*,e.name AS "Product_name" , e.sku ,e.base_price as "product_price" , e.qty as quantity,e.base_row_total as "product_total" ,e.tax_amount as "product_tax" ,e.base_price_incl_tax as "price_inc_tax" ,e.product_id FROM(
			SELECT c.increment_id as "Invoice_No",c.created_at as "invoice_date",c.entity_id as invoice_id,b.* FROM (
			SELECT z.entity_id AS order_inc_id,z.increment_id as "order_id",concat(z.customer_firstname," " ,z.customer_lastname) as Name,z.customer_email as email , z.subtotal,z.tax_amount as "total_tax" ,z.customer_taxvat as "gstin" ,z.grand_total as Total,z.shipping_amount as "Shipping_charge_without_tax", z.shipping_incl_tax as "Shipping_charge",concat(z.street  , z.postcode )as "billing_address", z.region as "Billing_State",concat(a.street ,  a.postcode )as "shipping_address",a.region as "shipping_state" ,a.company FROM (
			SELECT x.* , y.region,y.postcode,y.city,y.street FROM (
			SELECT subtotal,tax_amount ,grand_total,billing_address_id,shipping_address_id,shipping_amount , increment_id,customer_firstname,customer_lastname,customer_email , shipping_incl_tax,customer_taxvat,entity_id FROM `mg_sales_flat_order` ORDER BY `mg_sales_flat_order`.`entity_id` DESC ) AS x 
			LEFT JOIN mg_sales_flat_order_address as y ON x.billing_address_id=y.entity_id) as z 
			LEFT JOIN mg_sales_flat_order_address as a  ON z.shipping_address_id= a.entity_id) as b JOIN 
			`mg_sales_flat_invoice` as c ON c.order_id = b.order_inc_id  where c.created_at >= "'.$startDate.'" AND  c.created_at < "'.$endDate.'" ) AS d
			LEFT JOIN mg_sales_flat_invoice_item as e ON d.invoice_id = e.parent_id) as f 
			JOIN mg_catalog_product_entity_varchar as g ON g.entity_id = f.product_id WHERE g.attribute_id =162');
		// 	print_r(json_encode($exportData,JSON_FORCE_OBJECT));
			$jsondata ;
			foreach($exportData as $data){
		// 	   // print_r(((array)$data)['Invoice_No']);
				$jsondata[]= (((array)$data));
		// 	    print_r($jsondata);
		// 	    exit;
			}
		// 	print_r(json_encode($jsondata,JSON_FORCE_OBJECT));
		// //   print_r(json_encode(($exportData[0], JSON_FORCE_OBJECT));
			// print_r("hhhhhhh");
			// print_r($jsondata);
		// 	exit;
			
	return response()->json(['data'=>$jsondata[0]]);
	}
	
	public function getExportDatafromDateCPQ(Request $request)
	{
		if($request->has('selectedStart')){
			
			$startDate = date('Y/m/d',strtotime($request->selectedStart));
			$endDate = date('Y/m/d',strtotime($request->selectedEnd));

			
			$invoiceData= Invoice ::whereBetween('created_at',[$startDate,$endDate])->get() ;
			$this->calculateJson($invoiceData);
		}	
			
		
	}

	/*----------------updated in v2.0.3--------------- */
	public function generateSingleInvoiceJson(Request $request){
		
		$id = $request->id;
		$invoiceData= Invoice ::where('id',$id)->get() ;
		return $this->calculateJson($invoiceData);
	}
	
	public function calculateJson($invoice){
	    ini_set('serialize_precision', '-1');
		$invoiceData = $invoice;
		$allData =  array();
		
			foreach($invoiceData as $key2 =>$invoice )
			{
				
				$company = $invoice->getCompany();
				
				$billingAddress = $invoice->getInvoiceBillAddress($invoice->billing_id);
				$shippingAddress = $invoice->getInvoiceShipAddress($invoice->shipping_id);
				$productData = $invoice->getProducts()->get();
				$invoiceNo = $invoice->invoice_no;
				$newJson = new Invoice();
				$newJson->Version = "1.1";
				$newJson->TranDtls = ['TaxSch' =>'GST' ,'SupTyp' =>$invoice->invoice_type ,'RegRev'=>'N','IgstOnIntra'=>'N'];
				$newJson->DocDtls = ['Typ'=>"INV" ,'NO'=> $invoice->invoice_no,'Dt' => date_format($invoice->created_at,"d/m/Y")];
				$newJson->SellerDtls =['Gstin'=>'24AALFT2125L1Z3' ,'LglNm' => 'Tanna Techbiz LLP','Addr1' => '"Vraj Bhoomi", 22, New Jagnath Plot'
										,'Loc'=>'Rajkot','Pin'=>360001,'Stcd'=>'24'];
				$billtoPin = $billingAddress->pincode;
				$shiptoPin = $shippingAddress->pincode;
				$billingStd = $billingAddress->getstates()->state_description;
				$shippingStd = $shippingAddress->getstates()->state_description;
				$shippingGST = $company->gstin;
				if($invoice->invoice_type != 'B2B'){
					$billtoPin = null;
					$shiptoPin = 999999;
					$billingStd = "96" ;
					$shippingStd = "96";
					$shippingGST = "URP";
				}
				
				$newJson->BuyerDtls =['Gstin'=>$company->gstin ,'LglNm' => $company->name,'Pos' => $billingAddress->getstates()->state_description,'Addr1' => $billingAddress->address_line1
										,'Loc'=>$billingAddress->getCity()->name,'Pin'=>$billtoPin,'Stcd'=>$billingStd];
				//$newJson->DispDtls = [];
				$newJson->ShipDtls = ['LglNm' => $company->name,'Addr1' => $shippingAddress->address_line1
										,'Loc'=>$shippingAddress->getCity()->name,'Pin'=>$shiptoPin,'Stcd'=>$shippingStd];
				
				$allItemTotal =0;
				$allItemTotalWithTax =0;
				$allItemCgst = 0;
				$allItemSgst = 0;
				$allItemIgst = 0;
				$allItemgst  = 0;
				if(sizeof($productData)){
					
					$item  = array();
					foreach($productData as $key => $product){
					$totalAmount = $this->getTotalAmount($product->quantity,$product->unit_cost);
					if($product->c_s_gst!== "null" )
					{
						$gstType = 'c_s_gst' ;
					}else{
						$gstType = 'i_gst';
					}
					if($gstType == 'i_gst'){
						$Igst = explode('%',$product->i_gst)[0];
						
						$c_s_gst = 0;
						$gst =  $Igst;
						$currGst = $this->getGstAmount($Igst ,  $totalAmount,$gstType);
						$allItemIgst = $allItemIgst + $currGst;
						$allItemgst = $allItemgst + $currGst ;

					}else{
						$Igst =0;
						$c_s_gst =((explode('%',$product->c_s_gst,2))[0])*2;
						$gst =  $c_s_gst;
						$allItemCgst = $allItemCgst + $this->getGstAmount($c_s_gst ,  $totalAmount,$gstType);
						$allItemSgst = $allItemSgst + $this->getGstAmount($c_s_gst ,  $totalAmount,$gstType);
						$currGst = ($this->getGstAmount($c_s_gst ,  $totalAmount,$gstType)*2);
						$allItemgst  = $allItemgst + $currGst;
					}
					
					$totalAmountWithTax =$currGst + $totalAmount;
					$isServ = 'N';
				    $HSN = array(996812 , 9967);
				    if(in_array($product->HSN ,$HSN)){
				        $isServ = 'Y';
				    }
					$item[$key] = ['SlNo'=>strval($key+1), 'IsServc'=>$isServ, 'PrdDesc'=>($product->getProductData())[0]->name
										, 'HsnCd'=>$product->HSN, 'Qty'=>$product->quantity, 'FreeQty'=>0, 'Unit'=>"PCS"
										, 'UnitPrice'=>$product->unit_cost 
										, 'TotAmt'=> $totalAmount
										, 'Discount'=>0, 'PreTaxVal'=>0
										, 'AssAmt'=>($product->quantity*$product->unit_cost) , 'GstRt'=>(int)$gst
										, 'IgstAmt'=>$this->getGstAmount($Igst , $totalAmount,$gstType)
										, 'CgstAmt'=>$this->getGstAmount($c_s_gst ,  $totalAmount,$gstType)
										, 'SgstAmt'=>$this->getGstAmount($c_s_gst ,  $totalAmount ,$gstType)
										, 'CesRt'=>0, 'CesAmt'=>0, 'CesNonAdvlAmt'=>0
										, 'StateCesRt'=>0, 'StateCesAmt'=>0, 'StateCesNonAdvlAmt'=>0
										, 'OthChrg'=>0, 'TotItemVal'=>($this->roundOff($totalAmountWithTax))['total']];
					
					$allItemTotal = $allItemTotal + $totalAmount;
					}
					$allItemTotalWithTax = $allItemTotal + $allItemgst;
				}
				$round = $this->roundOff($allItemTotalWithTax);
				$newJson->ItemList = $item;
				$newJson->ValDtls = ['AssVal'=>$allItemTotal ,'CgstVal'=>$allItemCgst
									,'SgstVal'=>$allItemSgst
				                    ,'IgstVal'=>$allItemIgst
									,'CesVal'=>0 ,'StCesVal'=>0 ,'Discount'=>0,'OthChrg'=>0,'RndOffAmt'=>$round['roundedValue']
									,'TotInvVal'=>$round['total']];
				$allData[$key2] = $newJson;
			}
			
			$fileName = $invoiceNo. '_datafile.json';
			/*$fileStorePath ='public/uploads/json/'.$fileName;
	  
			$filesaved = File::put($fileStorePath, json_encode($allData));
			*/
			//response()->download($fileStorePath);
//if($filesaved){
				$saveExported = DB::table('exported_jsons')
					   ->insertGetId([    
							'filename'=>$fileName,
							'path'=>"",
							'type'=>'cpq',
							'is_archived'=>'0',
						]);
			//}
			
			//update invoice table with jsonid............
			$affected = DB::table('invoices')
              ->where('invoice_no', $invoiceNo)
              ->update(['json_id' => $saveExported]);
			 
			 return response(
				json_encode($allData, JSON_PRETTY_PRINT),
				200,
				['Content-Type' => 'application/json']
			);
			  return response()->json($allData);
	}
	
	/*------------end update 2.0.1----------------*/
	public function getExportedData(Request $request){
		$getExported = ExportedJson :: where('is_archived','0')
						->where('type','cpq')->get(['id','filename','path'])->toArray();
						return response()->json($getExported);
					
	}
	public function roundOff($total){
		$finalvalue=0;
		$originalValue = $total;
		$roundedValue = round($originalValue);

		$newValue =round((($originalValue) - ($roundedValue)) * 100) / 100;


		  if ($newValue < 0) {
			  $finalvalue=($originalValue)+(-(($newValue)));
			  $newValue=-$newValue;
		  }
		  else if ($newValue > 0) {
			$finalvalue=($originalValue)-((($newValue)));
			$newValue=-$newValue;
		  }
		  else
		  {
			$finalvalue=($originalValue)+($newValue);
		  }
		  $round = [];
		  $round['total'] = round($finalvalue);
		  $round['roundedValue'] = ($newValue);
		  
		  return $round;
	}
	
	public function getTotalAmount($unit_price,$quantity){
		return round($unit_price*$quantity,2);
	}
	
	public function getTotalAmountWithGST($unit_price,$quantity){
		return round($unit_price*$quantity,2);
	}
	
	public function getGstAmount($rate , $unit_price ,$gstType){
		
		if($gstType == 'i_gst')
			return ($unit_price*$rate)/100;
		else 
			return (($unit_price*$rate)/100)/2;
	}

    public function getpi(Request $request)
    {
            $result=[];
            $usedQuotation=[];
            $search = $request['searchTerm'];
            $invoice_pi=DB::table('invoices')->get('pi_no');
            if($invoice_pi){
                foreach($invoice_pi as $data){
			        $usedQuotation[] =  $data->pi_no;
                }
		    $datas = DB::table('p_i_s')
                    ->whereNotIn('p_i_s.pi_no',$usedQuotation)
                    ->where('p_i_s.pi_no','like','%'.$search.'%')
                    ->get();
         
                if(count($datas)){
            foreach($datas as $data){
    			$result[] = array("id" => $data->id,
    				"text" => $data->pi_no);
    		}
         }                            
       }
		return response()
            ->json($result);
    }

    public function pidetails(Request $request)
    {
        $pi_id=$request->pi_id;
            
        $data=DB::table('p_i_s')
                ->where("id","=",$pi_id)
                ->first();
        
        $bank_detail=DB::table('bank_details')
        ->where('id',$data->bank_id)
        ->first();
         
        $quotation=DB::table('quotations')
                ->where('id','=',$data->quotation_id)
                 ->first();

        $company=DB::table('companies')
                ->where('id','=',$quotation->company_id)
                ->first();
    
        $products=DB::table('quotations')
                    ->select('quotations.*','quotation_products.*','custom_products.name')
                    ->join('quotation_products','quotation_products.quotation_no','=','quotations.quotation_no')
                    ->rightjoin('custom_products','custom_products.id','=','quotation_products.item_id')
                    ->where('quotations.id','=',$data->quotation_id)
                    ->get();
 
        $billing_details=DB::table('company_addresses')
                ->leftjoin('cities','cities.id','=','company_addresses.city')
                ->leftjoin('states','states.state_id','=','company_addresses.state')
                ->where('address_id','=',$quotation->billing_id)->first();

        $shipping_details=DB::table('company_addresses')
                        ->leftjoin('cities','cities.id','=','company_addresses.city')
                        ->leftjoin('states','states.state_id','=','company_addresses.state')
                        ->where('address_id','=',$data->pi_shippingid)->first();
            
        return response()->json(['data'=>$data,'quotation'=>$quotation,'company'=>$company,'product'=>$products,'billing'=>$billing_details,'shipping'=>$shipping_details,'bank'=>$bank_detail]);        
    }

    public function getinvoicedetail(Request $request)
    {
        $invoice_id=$request->invoice_id;
            
        $invoice=Invoice::find($invoice_id);
		$company=DB::table('companies')
                ->where('id',$invoice->company_id)
                ->first();
				
		$billingId = $invoice->billing_id;
		$shippingId = $invoice->shipping_id;
		$bankId =$invoice->bank_id;
		$pi_info = [];
		$quotation = [];
		$pdata;
		$data = [];
		$isSerialNo =0;


		if($invoice->pi_no){
        $pi_info=DB::table('p_i_s')
                ->where('pi_no',$invoice->pi_no)
                ->first();
	

        $quotation=DB::table('quotations')
                ->where('quotation_no',$invoice->quotation_no)
                ->first();
        

        $products=DB::table('quotations')
                ->select('quotations.*','quotation_products.*','custom_products.name')
                ->join('quotation_products','quotation_products.quotation_no','=','quotations.quotation_no')
                ->rightjoin('custom_products','custom_products.id','=','quotation_products.item_id')
                ->where('quotations.id','=',$pi_info->quotation_id)
                ->get();
				
		
		$productData=Invoice::where('id','=',$request->invoice_id)
                ->first('quotation_no')->getQuotation()->first()->getProducts()->get('item_id')->toArray();
				foreach($productData as $key => $val){
					$pdata[$key] = $val['item_id'];
				}
				$invNo = Invoice::where('id',$request->invoice_id)->first('invoice_no');	
				$data =DB::select('select t1.serial_no,custom_products.id, custom_products.name from(select product_serial_nos.serial_no,product_serial_nos.invoice_no , product_serial_nos.product_id from product_serial_nos where invoice_no = "'.$invNo->invoice_no.'") as t1 RIGHT JOIN custom_products on custom_products.id = t1.product_id where custom_products.id IN('.implode(",",$pdata).')');
				foreach($data as $key =>$val){
				   if($val->serial_no)
						$isSerialNo++;
					
				}
					
			$billingId = $quotation->billing_id;
			$shippingId = $pi_info->pi_shippingid;
			$bankId = $pi_info->bank_id;
		}else{
			$products=DB::table('invoices')
					->select('invoices.*','invoice_products.*','custom_products.name')
					->join('invoice_products','invoice_products.invoice_no','=','invoices.invoice_no')
					->rightjoin('custom_products','custom_products.id','=','invoice_products.item_id')
					->where('invoices.id','=',$invoice_id)
					->distinct()->get();
					
			
			$productData=Invoice::where('id','=',$request->invoice_id)->first()
					->getProducts()->get('item_id')->toArray();
					foreach($productData as $key => $val){
						$pdata[$key] = $val['item_id'];
					}
					$invNo = Invoice::where('id',$request->invoice_id)->first('invoice_no');	
					$data =DB::select('select t1.serial_no,custom_products.id, custom_products.name from(select product_serial_nos.serial_no,product_serial_nos.invoice_no , product_serial_nos.product_id from product_serial_nos where invoice_no = "'.$invNo->invoice_no.'") as t1 RIGHT JOIN custom_products on custom_products.id = t1.product_id where custom_products.id IN('.implode(",",$pdata).')');
					$isSerialNo =0;
					foreach($data as $key =>$val){
					   if($val->serial_no)
							$isSerialNo++;
						
					}
					
			$pi_info['po_number'] = $invoice->po_number;
			$pi_info['paymnet_term'] = $invoice->paymnet_term;
		}
        $billing_details=DB::table('company_addresses')
                ->leftjoin('cities','cities.id','=','company_addresses.city')
                ->leftjoin('states','states.state_id','=','company_addresses.state')
                ->where('address_id','=',$billingId)->first();

        $shipping_details=DB::table('company_addresses')
                ->leftjoin('cities','cities.id','=','company_addresses.city')
                ->leftjoin('states','states.state_id','=','company_addresses.state')
                ->where('address_id','=',$shippingId)->first();

		$bank_detail=DB::table('bank_details')->where('id',$bankId)
                        ->first();
			
        return response()->json(['invoice'=>$invoice,'pi_info'=>$pi_info,'quotation'=>$quotation,'company'=>$company,'product'=>$products,'billing'=>$billing_details,'shipping'=>$shipping_details,'data' =>$invoice,'serialNo'=>$data,'bank'=>$bank_detail,'isSerialNo' =>$isSerialNo]);
    }

    public function detailsforlabel(Request $request)
    { 

            if($request->type=='excel')
            {
            $invoice_id=$request->id;
            $invoice_data=[];
            $product=[];
            $shipping_detailsdata=[];

            for($i=0;$i<count($request->id);$i++)
            {   
                    $invoice=DB::table('companies')
                        ->join('invoices','invoices.company_id','=','companies.id')
                        ->where("invoices.id",$invoice_id[$i])
                        ->first();
            
                    $pi=DB::table('p_i_s')
                        ->where("pi_no",$invoice->pi_no)
                        ->first();

                    /*$products=DB::table('quotations')
                        ->select('quotations.*','quotation_products.*','custom_products.name')
                        ->join('quotation_products','quotation_products.quotation_no','=','quotations.quotation_no')
                        ->rightjoin('custom_products','custom_products.id','=','quotation_products.item_id')
                        ->where('quotations.id','=',$pi->quotation_id)
                        ->get();*/
					$products=	Invoice::where('id','=',$invoice_id[$i])
                ->first()->getProducts()->get();

                    $shipping_details=DB::table('company_addresses')
                        ->leftjoin('cities','cities.id','=','company_addresses.city')
                        ->leftjoin('states','states.state_id','=','company_addresses.state')
                        ->where('address_id','=',$invoice->shipping_id)->first();

                    $invoice_data[]=[$invoice];
                    $product[]=[$products];
                    $shipping_detailsdata[]=[$shipping_details];

                    $enterlog=DB::table('excel_shipping_label_log')
                        ->insert([
                            'invoice_no'=>$invoice->invoice_no,
                            'created_by'=>$request->creater
                        ]);
					date_default_timezone_set('Asia/Kolkata');
					$date = date('d-m-y');
            }

            return response()->json(['invoice'=>$invoice_data,'products'=>$product,'ship'=>$shipping_detailsdata,'date'=>$date]);
        }
 
        if($request->type=='pdf')
            {
            $invoice_id=$request->id;
            $invoice_data=[];
            $shipping_detailsdata=[];

            for($i=0;$i<count($request->id);$i++)
            {
                    $invoice=DB::table('companies')
                        ->join('invoices','invoices.company_id','=','companies.id')
                        ->where("invoices.id",$invoice_id[$i])
                        ->first();
            
                    /*$pi=DB::table('p_i_s')
                    ->where("pi_no",$invoice->pi_no)
                    ->first();*/

                    $shipping_details=DB::table('company_addresses')
                    ->leftjoin('cities','cities.id','=','company_addresses.city')
                    ->leftjoin('states','states.state_id','=','company_addresses.state')
                    ->where('address_id','=',$invoice->shipping_id)->first();

                    $invoice_data[]=[$invoice];
                    $shipping_detailsdata[]=[$shipping_details];

            }

            return response()->json(['invoice'=>$invoice_data,'ship'=>$shipping_detailsdata]);
        }



    }
	
	public function getSerialNoData(Request $request){
		$pdata;
		if(Invoice::where('id','=',$request->invoice_id)->first('quotation_no')['quotation_no'] != 0){
		$productData=Invoice::where('id','=',$request->invoice_id)
                ->first('quotation_no')->getQuotation()->first()->getProducts()->get('item_id')->toArray();
		
				}
				else{
					$productData=Invoice::where('id','=',$request->invoice_id)
                ->first()->getProducts()->get('item_id')->toArray();
		
				}
			foreach($productData as $key => $val){
			$pdata[$key] = $val['item_id'];
		}
		$invNo = Invoice::where('id',$request->invoice_id)->first('invoice_no');	
		$data =DB::select('select t1.serial_no,custom_products.id, custom_products.name from(select product_serial_nos.serial_no,product_serial_nos.invoice_no , product_serial_nos.product_id from product_serial_nos where invoice_no = "'.$invNo->invoice_no.'") as t1 RIGHT JOIN custom_products on custom_products.id = t1.product_id where custom_products.id IN('.implode(",",$pdata).')');
		return response()->json(['products'=>$data,'invoice_no'=>$invNo->invoice_no]);

	}

    public function setserialno(Request $request)
    {
        $invoice_no=$request->invoice;
        $generated_by=$request->generater;
        $product_id=$request->id;
        $serial_no=$request->data;

        for($i=0;$i<count($product_id);$i++)
        {
            $data=DB::table('product_serial_nos')
                   ->insert([    
                        'invoice_no'=>$invoice_no,
                        'product_id'=>$product_id[$i],
                        'serial_no'=>$serial_no[$i],
                        'generated_by'=>$generated_by,
                    ]);
        }
		
		if($data){
			$invoice=DB::table('invoices')
					->where('invoice_no','=',$request->invoice)
					->update(['isSerialNo'=>1]);
		}
        
        return response()->json($data);

    }
	
	 public function productstore(Request $request)
    {
		$isInvoiceTable = $invNo = Invoice::where('invoice_no',$request->invoice_no)->first('invoice_no');	
		if(isInvoiceTable){
       for($i =0; $i < count($request->quotation_itemid); $i++){
                $answers[] = [    
                    'invoice_no' =>$request->invoice_no,
                    'item_id' => $request->quotation_itemid[$i],
                    'HSN'=>$request->quotation_HSN[$i],
                    'unit_cost' => $request->quotation_unitcost[$i],
                    'quantity' => $request->quotation_quantity[$i],
                    'c_s_gst' => $request->quotation_csgst[$i],
                    'i_gst' => $request->quotation_igst[$i],
                ];
        }
        
        $data=DB::table('invoice_products')->insert($answers);
		$affected = DB::table('invoices')
              ->where('invoice_no', $request->invoice_no)
              ->update(['note' => $request->note,'bank_id'=>$request->bank_id ,
			  'po_number' => $request->invoice_without_pi_ponumber,
			  'paymnet_term'=>$request->invoice_without_pi_paymentterm]);
        }else{
			$data = NULL;
		}
		
		if($data){
			  //$this->generateSingleInvoiceJson($request->invoice_no);
		 }
        return response()->json($data);
    }
	
	public function storeInvoiceWithoutPi(Request $request)
    { 
        try {
        $invoices= new Invoice([  
            'invoice_no'=>$request->invoice_no,
			'pi_no'=>0,
			'quotation_no' => 0,
            'company_id'=>$request->companyid,
            'billing_id'=>$request->billing_id,
            'shipping_id'=>$request->shipping_id,
            'issue_date'=>$request->issuedate,
            'generated_by'=>$request->generated_byquotation,
			'courier_name'=>"null",
            'tracking_id'=>"null",
            'shipping_date'=>NULL,
			'invoice_type' => $request->invoice_type,
			'sales_person' => $request->sales_person,
        ]);
        $invoices->saveOrFail();
        
        return response()->json($invoices);
        } catch (Exception $e) {
        // Log the error and redirect back with message
        return redirect()->back()->withErrors(['error' => 'Unable to store data: ' . $e->getMessage()]);
    }
		
    }
	
	public function getBankDetails(){
		$bank_detail=DB::table('bank_details')
                        ->get();
			
			foreach($bank_detail as $data){
			$result[] = array("id" => $data->id,
				"text" => $data->bank_aliasname." - ".$data->bank_name." , ".$data->bank_branch." , ".$data->bank_ifsc." , ".$data->ac_no);
		}
		return response()->json($result);

	}
	
	public function getInvocieForSelect2(Request $request)
    {
            $result=[];
            $usedQuotation=[];
            $search = $request['searchTerm'];
            $invoices=DB::table('invoices')
					->where('invoice_no','like','%'.$search.'%')
					->get();
         
            if(count($invoices)){
            foreach($invoices as $data){
    			$result[] = array("id" => $data->id,
    				"text" => $data->invoice_no);
    		}
         }                            
       
		return response()
            ->json($result);
    }

	public function cancel(Request $request){
		$invoice_id=$request->id;
		$update = DB::table('invoices')->whereIn('id',$invoice_id)
        ->update(
            [
                'invoice_status'=> 'Canceled'
                
            ]
            );
				return response()
            ->json($update);
	}
	
	public function destroyunsaved(Request $request)
    {
            $id=$request->sessionforquotation;
            $invoices=Invoice::withTrashed()->find($id);        
                if(!is_null($invoices))
                {
                    $invoices->forceDelete();
                }
        
            return response()->json($invoices); 
    }
	
}
