<?php

namespace App\Http\Controllers\api;

use App\Http\Controllers\Controller;
use App\Models\Project\Budget;
use App\Models\Admin\User;
use App\Models\UserDetail;
use App\Models\Project\Vendor;
use App\Models\Project\Member;
use App\Models\Project\Invoice_main;
use App\Models\Project\Expense_type;
use App\Models\Project\Expense_types_user;
use App\Models\Project\Expenses;
use App\Models\Project\Invoice_attachment;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;
use App\Traits\ApiLanguageTrait;

class ExpenseVendorsController extends Controller
{
	use ApiLanguageTrait;

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */


    public function expense_vendors(Request $request)
	{
		$id = $request->expense_type;
		$user_id = $request->user_id;
		$data = array();
		$data['total_amount'] = (string)DB::table('invoice_main')->where('user_id' , $user_id)->where('expense_type',$id)->where('status' , 1)->where(['is_reset' => 0, 'budget_id'=>$request->budget_id])->sum('net_total');
		
		$data['total_invoices'] = (string)DB::table('invoice_main')->where('user_id',$user_id)->where('expense_type',$id)->where('status' ,1)->where('invoice_type','!=',3)->where(['is_reset' => 0, 'budget_id'=>$request->budget_id])->count();
		$data['total_members'] = (string)DB::table('members')->where('user_id',$user_id)->where(['expense_type'=>$id])->count();

		$data['vendor_logo_url'] = "https://sarfapp.com/uploads/images/vendor/";
		
		$data['vendor_list'] =   DB::select(DB::raw("select b.vendor_id,a.id,a.uid,a.name,a.location,a.logo,a.insta_link,a.twitter_link,a.website,a.whatsapp,a.mobile,(SELECT COALESCE(count(id),0) FROM invoice_main WHERE vendor_id = a.uid AND expense_type = '$id' AND user_id = '$user_id' AND is_reset = 0 AND budget_id = '$request->budget_id') AS vendor_invoices,(SELECT COALESCE(SUM(net_total),0) FROM invoice_main WHERE vendor_id = a.uid AND expense_type = '$id' AND user_id = '$user_id' AND is_reset = 0 AND budget_id = '$request->budget_id') AS vendor_total,(SELECT expense_name FROM expense_types WHERE id = '$id') AS expense_type_name FROM vendor_detail a
        LEFT JOIN  members b ON b.vendor_id = a.uid			
        WHERE
        b.expense_type = '$id' AND b.user_id = '$user_id'"));
		
		$data['vendor_list_status'] = 1;
		if(empty($data['vendor_list'])){
			$data['vendor_list_status'] = 0;
			$data['msg_en'] = $this->__transEn('member_list_not_found');
			$data['msg_ar'] = $this->__transAr('member_list_not_found');
		}

		// get invoices
		$data['inv_list'] = Invoice_main::where('user_id', $user_id)->where('expense_type', $id)->where(['invoice_type'=>3, 'budget_id'=>$request->budget_id])->with('attachments')->orderBy('id', 'DESC')->get();
		$data['custom_inv_count'] = $data['inv_list']->count();
		$data['custom_inv_amount'] = $data['inv_list']->sum('net_total');
		$data['inv_status'] = 1;
		if(empty($data['inv_list'])){
			$data['inv_status'] = 0;
			$data['msg_en'] = $this->__transEn('not_found');
			$data['msg_ar'] = $this->__transAr('not_found');
		}
        return $data;  
	}

	public function expense_vendor_detail(Request $request)
	{
		$id = $request->vendor_id;
		$user_id = $request->user_id;
		$type_id = DB::table('members')->where('user_id', $user_id)->where(['vendor_id'=> $id])->first()->expense_type;
		$data = array();
		$data['vendor_logo_url'] = "https://sarfapp.com/uploads/images/vendor/";
		 $data['vendor_detail'] =   DB::select(DB::raw("select a.id,a.name,a.location,a.logo,a.	insta_link,a.twitter_link,a.website,a.whatsapp,a.mobile, a.location_lat, 
		 a.location_lng ,(SELECT COALESCE(count(id),0) FROM invoice_main WHERE vendor_id = '$id' AND user_id ='$user_id' AND budget_id = '$request->budget_id') AS vendor_invoices,
		 (SELECT COALESCE(SUM(net_total),0) FROM invoice_main WHERE vendor_id ='$id' AND user_id ='$user_id' AND budget_id = '$request->budget_id') AS vendor_total,
		 (SELECT expense_name FROM expense_types WHERE id = '$type_id') AS expense_type_name FROM vendor_detail a			
        WHERE
        a.uid = '$id' "));
		
		$data['vendor_detail_status'] = 1;
	if(empty($data['vendor_detail'])){
		 $data['vendor_detail_status'] = 0;
		$data['msg_en'] = $this->__transEn('member_data_not_found');
		$data['msg_ar'] = $this->__transAr('member_data_not_found');
	 }
	 
	 $data['vendor_invoices'] =   DB::select(DB::raw("select a.id,a.net_total,a.date,a.paid_status,(SELECT expense_name FROM expense_types WHERE id = a.expense_type) AS expense_name FROM invoice_main a
        WHERE a.vendor_id = '$id' AND a.user_id = '$user_id' AND a.budget_id = '$request->budget_id'"));
		
		$data['vendor_invoices_status'] = 1;
	if(empty($data['vendor_invoices'])){
		$data['vendor_invoices_status'] = 0;
		$data['msg_en'] = $this->__transEn('member_invoices_data_not_found');
		$data['msg_ar'] = $this->__transAr('member_invoices_data_not_found');
	 }
        return $data;  
	}
	
	public function invoice_detail(Request $request)
	{
		$id = $request->invoice_id;
		$data = array();
		// invoicer details
		$data['invoice_detail'] =   DB::select(DB::raw("select a.id,a.vendor_id,a.expense_type,a.date, a.payment_type, a.attachments, a.is_approve, (SELECT name FROM vendor_detail WHERE uid = a.vendor_id) AS vendor_name,(SELECT name FROM user_detail WHERE uid = a.user_id) AS user_name, (SELECT expense_name FROM expense_types WHERE id = a.expense_type) AS expense_name,a.total_amount,a.discount_invoice,a.tax,a.net_total,a.invoice_note FROM invoice_main a
        WHERE a.id = '$id'"));		
		$data['invoice_detail_status'] = 1;
		if(empty($data['invoice_detail'])){
		 	$data['invoice_detail_status'] = 0;
			$data['msg_en'] = $this->__transEn('invoices_data_not_found');
			$data['msg_ar'] = $this->__transAr('invoices_data_not_found');
		}

		// invoice items
	 	$data['invoice_items'] =   DB::select(DB::raw("select a.name,a.qty,a.unit,a.price,a.discount,a.amount FROM invoice_detail a
        WHERE
        a.invoice_main_id = '$id'"));		
		$data['invoice_items_status'] = 1;
		if(empty($data['invoice_items'])){
			$data['invoice_items_status'] = 0;
			$data['msg_en'] = $this->__transEn('invoices_items_data_not_found');
			$data['msg_ar'] = $this->__transAr('invoices_items_data_not_found');
		}

		// invoice attachments
		$data['invoice_attachments'] = Invoice_attachment::where('invoice_main_id', $id)->get();		
		$data['invoice_attachments_status'] = 1;
		if(empty($data['invoice_attachments'])){
			$data['invoice_attachments_status'] = 0;
			$data['msg_en'] = $this->__transEn('invoices_attachments_not_found');
			$data['msg_ar'] = $this->__transAr('invoices_attachments_not_found');
		}
		return $data;  
	}
	
	 public function vendor_warranty(Request $request)
	{
		$id = $request->vendor_id;
		$user_id = $request->user_id;
		$data = array();
		 $data['warranty_invoices'] =   DB::select(DB::raw("select a.id,a.net_total,a.date,(SELECT expense_name FROM expense_types WHERE id = a.expense_type) AS expense_name,b.warranty_date,b.warranty_note FROM invoice_main a,invoice_warranty b
        WHERE b.invoice_id = a.id AND 
        a.vendor_id = '$id' AND a.user_id = '$user_id' AND a.warranty = 1 "));
		
		$data['warranty_invoices_status'] = 1;
	if(empty($data['warranty_invoices'])){
		 $data['warranty_invoices_status'] = 0;
		$data['msg_en'] = $this->__transEn('member_warranty_data_not_found');
		$data['msg_ar'] = $this->__transAr('member_warranty_data_not_found');
	 }
        return $data;  
	}
	// user invoice setting
	 public function invoice_setting(Request $request)
	{
		$data = array();
		$id = $request->invoice_id;
		$user_id = $request->user_id;
		$action = $request->action;
		if ($action == "update"){
		$budget_type = 	DB::table('expense_types')->where('user_id', $user_id)->where('id', $request->expense_type)->first()->budget_id;
		$msg = DB::table('invoice_main')->where('id', $id)->where('user_id', $request->user_id)->update(array('expense_type' => $request->expense_type,'budget_id' =>$budget_type));	
		}else{
		$msg = DB::table('invoice_main')->where('id', $id)->where('user_id', $request->user_id)->update(array('expense_type' => 0));	
		}
		 if ($msg) {
		 $data['status'] = 1;
		$data['msg_en'] =  $this->__transEn('update_success');
		$data['msg_ar'] =  $this->__transAr('update_success');
	 }else{
		 $data['status'] = 0;
		 	if ($action == "update"){
		$data['msg_en'] = $this->__transEn('expense_type_already_updated');
		$data['msg_ar'] = $this->__transAr('expense_type_already_updated');
			}else{
			$data['msg_en'] = $this->__transEn('expense_type_already_removed');
			$data['msg_ar'] = $this->__transAr('expense_type_already_removed');	
			}
	 }
        return $data;  
	}
	
		// vendor  setting -- set member type to
	 public function vendor_setting(Request $request)
	{
		$data = array();
		$id = $request->vendor_id;
		$user_id = $request->user_id;
	
	
		$msg = DB::table('members')->where('vendor_id', $id)->where('user_id', $request->user_id)->update(array('expense_type' => $request->expense_type));	
		
		 if ($msg) {
		 $data['status'] = 1;
		$data['msg_en'] =  $this->__transEn('update_success');
		$data['msg_ar'] = 	$this->__transAr('update_success');
	 }else{
		 $data['status'] = 0;
		 $data['msg_en'] =  $this->__transEn('already_connected');
		$data['msg_ar'] = 	$this->__transAr('already_connected');
		
	 }
        return $data;  
	}


	public function due_list_users(Request $request)
	{
		$user_id = $request->user_id;
		$data = array();
	$data['due_amount'] = (string)DB::table('invoice_main')->where('vendor_id' , $user_id)->where('paid_status' , 0)->sum('net_total');
		
	
		$data['user_list'] =   DB::select(DB::raw("select user_id,SUM(net_total) as total_due,(select name from user_detail where uid = user_id) as user_name,
		(select mobile from users where id = user_id) as user_mobile,
		(select username from users where id = user_id) as username  FROM invoice_main 	
        WHERE
        paid_status = 0 AND vendor_id = '$user_id' GROUP BY user_id"));
		
		$data['user_list_status'] = 1;
		if(empty($data['user_list'])){
			$data['user_list_status'] = 0;
			$data['msg_en'] = $this->__transEn('not_found');
			$data['msg_ar'] = $this->__transAr('not_found');
		}
        return $data;  
	}

	public function due_list_vendors(Request $request)
	{
		$user_id = $request->user_id;
		$data = array();
	$data['due_amount'] = (string)DB::table('invoice_main')->where('user_id' , $user_id)->where('paid_status' , 0)->sum('net_total');
		
	
		$data['vendor_list'] =   DB::select(DB::raw("select vendor_id,SUM(net_total) as total_due,(select name from vendor_detail where uid = vendor_id) as user_name FROM invoice_main 	
        WHERE
        paid_status = 0 AND user_id = '$user_id' GROUP BY vendor_id"));
		
		$data['vendor_list_status'] = 1;
		if(empty($data['vendor_list'])){
			$data['vendor_list_status'] = 0;
			$data['msg_en'] = $this->__transEn('not_found');
			$data['msg_ar'] = $this->__transAr('not_found');
		}
        return $data;  
	}
}
