<?php

namespace App\Http\Controllers\administrator\Construction\Purchase;

use App\ConstructionModel\BOQDetailModel;
use App\ConstructionModel\BOQModel;
use App\ConstructionModel\ProductModel;
use App\ConstructionModel\PurchaseOrderDetailModel;
use App\ConstructionModel\PurchaseOrderModel;
use App\ConstuctionModel\SuppliersModel;
use App\ProjectModel;
use App\PurchaseOrderSettingModel;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Yajra\DataTables\DataTables;
use App\ConstructionModel\AjustPurchaseOrderDetailModel;
use App\ConstructionModel\AjustPurchaseOrderModel;
use App\ConstructionModel\BrandModel;
use App\ConstructionModel\TaskWorkingGroupModel;
use Gate;

class PurchaseOrderController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:Construction-CEO-Approval-Purchase-Order-List',['only'=>['ceo_approval_index']]);
        $this->middleware('permission:Construction-CEO-Approval-Ajustment-Purchase-List',['only'=>['ceo_approval_ajust_purchase_index']]);
        $this->middleware('permission:Construction-Financial-Approval-Purchase-Order-List',['only'=>['financial_approval_index']]);
        $this->middleware('permission:Construction-Financial-Approval-Ajustment-Purchase-List',['only'=>['financial_approval_ajust_purchase_index']]);
        $this->middleware('permission:Construction-Purchase-Order-List',['only'=>['index']]);
        $this->middleware('permission:Construction-Assign-Product-To-Warehouse-List',['only'=>['verified_product_list']]);
    }
    public function index(Request $request)
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->pluck("project_name","id")->prepend("Please Select","");
        $supplers = DB::table("nso007_construction_suppliers")->select("name",'id')->pluck("name","id")->prepend("Please Select","");
        if($request->ajax()){
            $request_name = $request->request_name;
            $supplier_id = $request->supplier_id;
            $stock_position = $request->stock_position;
            $project_id = $request->project_id;
            $data = PurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_construction_purchase.project_id")
                ->select("nso007_construction_purchase.*","nso007_project_stock.project_name")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->when(request('request_name'),function($query) use ($request_name){
                    $query->where("nso007_construction_purchase.request_by","LIKE","%".$request_name."%");
                })
                ->when(request('supplier_id'),function($query) use ($supplier_id){
                    $query->where("nso007_construction_purchase.supplier_id",$supplier_id);
                })
                ->when(request('stock_position'),function($query) use ($stock_position){
                    $query->where("stock_position","LIKE","%".$stock_position."%");
                })
                ->when(request('project_id'),function($query) use($project_id){
                    $query->where('nso007_construction_purchase.project_id',$project_id);
                })
                ->orderBy('nso007_construction_purchase.date','DESC')
                ->get();
                
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('phone_number',function($data)
                    {
                        return optional(SuppliersModel::find($data->supplier_id))->phone;
                    })
                    ->editColumn('amount',function($data){
                        return "$".number_format($data->total_amount,2);
                    })
                    ->editColumn('status',function($data){
                        if($data->ajust_status==1) return "<span style='color:rgb(184, 95, 11)'>Requesting Ajust P.O</span>";
                        else if($data->ajust_status==2) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(Financial Approve)</span>";
                        else if($data->ajust_status==3) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(CEO Approve)</span>";
                        else if($data->status==1) return "<span style='color:rgb(184, 95, 11)'>Requesting P.O</span>";
                        else if($data->status==0) return "<span style='color:rgb(42, 156, 8)'>Approved P.O</span>";
                        else if($data->status==2) return "<span style='color:rgb(185, 51, 17)'>".(!empty($data->reason_reject_financial)?("Financial Reason:".$data->reason_reject_financial.","):"").(!empty($data->reason_reject_ceo)?("CEO Reason:".$data->reason_reject_ceo):"")." (Rejected P.O)</span>";
                        else if($data->status==5) {
                            return "<span style='color:rgb(17, 118, 185)'>Order</span>";
                        }
                        else if($data->status==6) {
                            return "<span style='color:rgb(17, 118, 185)'>Verified</span>";
                        }
                        else {}
                    })
                    ->addColumn('action', function($data) {
                        $button ="";
                        if(Gate::check('Construction-Purchase-Order-Show'))
                        {
                            $button .= " <a title='Show Detail' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->id))."' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";
                        }
                        
                        if($data->status==1){
                            if(Gate::check('Construction-Purchase-Order-Edit'))
                            {
                                $button .= " <a title='Edit' href='".route('construction-make-purchase-order.edit',Crypt::encrypt($data->id))."' class='btn btn-sm btn-primary' style='margin-left:5px !important;'><i class='fa fa-edit' ></i></a> ";
                            }
                        }
                        if($data->status == 0) {
                            if(Gate::check('Construction-Purchase-Order-Make-Order'))
                            {
                                $button .= " <a title='Order Product' onclick = 'display_form_order(".$data->id.")' href='javascript:void(0)' class='btn btn-sm btn-primary' style='margin-left:5px !important;'><i class='fa fa-shopping-basket'></i></a> ";
                            }
                            
                        }
                        if($data->status==5&&$data->ajust_status!=1){
                            if(Gate::check('Construction-Purchase-Order-Verify-Product'))
                            {
                                $button .= " <a title='Verify Product' href='".route('construction-purchase.verify_product',Crypt::encrypt($data->id))."' class='btn btn-sm btn-success' style='margin-left:5px !important;'><i class='fa fa-check' ></i></a> ";
                            }
                        }
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
        }
        return view("administrator.construction.purchase.purchase-order.index",compact('projects','supplers'));
    }

    public function create(Request $request)
    {
        $boq = BOQModel::selectRaw('CONCAT(title," / ",nso007_sub_category_stock.sub_category_name," ( prepared by ",prepared_by,")") as boq_name,nso007_boq_construction.id')
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_boq_construction.project_id")
            ->join("nso007_sub_category_stock","nso007_sub_category_stock.id","nso007_boq_construction.sub_category_house_id")
            ->where('nso007_boq_construction.status', 0)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        $supplier = SuppliersModel::pluck('name', 'id');
        $zones = DB::table('nso007_zone_construction')->pluck("name","id")->prepend("Please Select","");
        $house_categories = DB::table('nso007_sub_category_stock')->pluck("sub_category_name","id")->prepend("Please Select","");

        $work_group_types = TaskWorkingGroupModel::pluck('name','id')->prepend("Please Select","");
        return view("administrator.construction.purchase.purchase-order.create", compact('boq','supplier','zones','house_categories','zones','house_categories','work_group_types'));
    }

    public function get_bog_title_work_group($boq_id)
    {
        $data = BOQDetailModel::where('boq_id',$boq_id)
        ->select("title")
        ->groupBy('title')
        ->get();
        return json_encode($data);
    }

    public function get_bog_detail(Request $request)
    {
        // dd("OK");
        $boq = BOQModel::find($request->boq);
        $projects = null;
        $boq_detail = null;
        if ($boq) {
            $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
                ->where('id', $boq->project_id)
                ->pluck("project_name", "id");
            $detail = BOQDetailModel::where('boq_id', $boq->id)
                ->where(function($query) use ($request){
                    if(!empty($request->task_group_categories))
                        $query->where("title",$request->task_group_categories);
                })
                ->select("*",DB::raw('SUM(qty_used_all_amount_of_house) as qty_product'))
                ->groupBy("product_id")
                ->get();

            $product_boq_purchased_qty = [];

            foreach ($detail as $key => $row) {
                $product = ProductModel::join("nso007_product_brand_construction", "nso007_product_brand_construction.id", 'nso007_construction_product.brand_id')
                    ->select("nso007_product_brand_construction.name as subcategory_name", 'nso007_construction_product.*')
                    ->where("nso007_construction_product.id", $row->product_id)
                    ->first();
                $unit = DB::table('nso007_unit_construction')->find($product->unit_id);
                $boq_detail[$key] = new \stdClass();
                $boq_detail[$key]->product_id = $product->id;
                $boq_detail[$key]->item_name = $product->name;
                $boq_detail[$key]->unit_name = $unit ? $unit->name : '';
                $boq_detail[$key]->qty_product = $row->qty_product;
                $boq_detail[$key]->retail = $product->current_price;
                $boq_detail[$key]->unit_id = $product->unit_id;

                $product_boq_purchased = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$boq->id)
                    ->where(function($query) use ($request){
                        if(!empty($request->task_group_categories))
                            $query->where("boq_detail_title",$request->task_group_categories);
                    })
                    ->where("product_id",$product->id)->select("total_qty_request")->first();

                $product_boq_purchased_qty[$key] = !empty($product_boq_purchased)?(!empty($product_boq_purchased->total_qty_request)?$product_boq_purchased->total_qty_request:0):0;
            }
        }

        return response()->json(['project' => $projects, 'boq_detail' => $boq_detail,'product_boq_purchased_qty'=>$product_boq_purchased_qty]);
    }
    public function check_product_qty_boq(Request $request)
    {
        $boq_product_qty    = 0;
        $boq_data           = BOQDetailModel::select(DB::Raw('SUM(qty_used_all_amount_of_house) As qty'),'specific')
            ->where([['boq_id',$request->boq_id],['product_id',$request->product_id],['title',$request->task_group_categories]])
            ->first();
        $boq_product_qty    = (!empty($boq_data)?$boq_data->qty:0);
        $request_amount     = $request->product_qty;
        $qty_product_buyed  = 0;

        $buyed_data = DB::table('nso007_construction_purchase')
                    ->join('nso007_construction_purchase_details',
                        'nso007_construction_purchase_details.purchase_id',
                        'nso007_construction_purchase.id'
                    )
                    ->select("qty")
                    ->where('boq_id',$request->boq_id)
                    ->where("task_group_work_title",$request->task_group_categories)
                    ->where('product_id',$request->product_id)
                    ->get();
        foreach($buyed_data as $ke=>$val)
        {
            $qty_product_buyed += $val->qty;
        }
        if(empty($boq_data)){
            return json_encode(['status'=>1]);//No data Purchase
        }else{
            if(!empty($boq_data->specific)){
                if($boq_product_qty < $request_amount + $qty_product_buyed){
                    return json_encode(['status'=>2,'balance'=>($boq_product_qty - $qty_product_buyed)]);//Qty of product is not enough
                }
            }
        }
        return json_encode(['status'=>0]);//correct request
    }
    public function check_product_qty_boq_verify_product(Request $request)
    {
        // dd($request->all());
        $boq_product_qty    = 0;
        $boq_data           = BOQDetailModel::select(DB::Raw('SUM(qty_used_all_amount_of_house) As qty'),'specific')->where([['boq_id',$request->boq_id],['product_id',$request->product_id],['title'=>$request->task_group_categories]])->first();

        $boq_product_qty    = (!empty($boq_data)?$boq_data->qty:0);
        $request_amount     = $request->product_qty;
        $qty_product_buyed  = 0;

        $buyed_data = DB::table('nso007_construction_purchase')
                    ->join('nso007_construction_purchase_details','nso007_construction_purchase_details.purchase_id','nso007_construction_purchase.id')
                    ->select("qty")
                    ->where('boq_id',$request->boq_id)
                    ->where('product_id',$request->product_id)
                    ->get();
        foreach($buyed_data as $ke=>$val)
        {
            $qty_product_buyed += $val->qty;
        }
        if(empty($boq_data)){
            return json_encode(['status'=>1]);//No data Purchase
        }else{
            if(!empty($boq_data->specific)){
                if($boq_product_qty < $request_amount + $qty_product_buyed){
                    return json_encode(['status'=>2,'balance'=>($boq_product_qty - $qty_product_buyed)]);//Qty of product is not enough
                }
            }
        }
        return json_encode(['status'=>0]);//correct request
    }
    public function store(Request $request)
    {
        $this->validate($request, [
            'project'       => 'required',
            'request_by'    => 'required',
            'stock_position'=> 'required',
            'supplier'      => 'required',
            'date'          => 'required',
            'type'          => 'required',
            'work_group_type_id'=>'required',

            'qty_per_house' => 'required',
            'retail'        => 'required',
            'checkbox'      => 'required',
            'qty_per_house.*' => 'required',
            'retail.*'      => 'required',
            'checkbox.*'    => 'required',
            'warehouse_id'  => 'required',
            'using_to'      => 'required',
        ]);
        if($request->purchase_type==0){
            $this->validate($request,[
                'boq'=>'required',
                // 'task_group_categories'=>'required'
            ]);
        }
        //throw ValidationException::withMessages(['field_name' => 'This value is incorrect']);


        $checkbox       = $request->checkbox;
        $qty_per_house  = $request->qty_per_house;
        $retail         = $request->retail;
        $unit_id        = $request->unit_id;
        $pur_location   = $request->pur_location;
        $type           = $request->type;
        $error          = [];

        /*if ($type == 'order') {
            foreach ($checkbox as $key => $row) {
                $amount         = $qty_per_house[$row] * $retail[$row];
                $cate_setting   = PurchaseOrderSettingModel::where('type', 'category')->pluck('category_id');
                $amount_setting = PurchaseOrderSettingModel::where('type', 'amount')->pluck('amount');
                $product        = ProductModel::find($row);

                foreach ($cate_setting as $ii => $cate) {
                    if ($cate == $product->category_id) {
                        $error[$product->name . 'cate'] = 'The ' . $product->name . ' can not Order without request !!';
                    }
                }
                foreach ($amount_setting as $ii => $amt) {
                    if ($amt < $amount) {
                        $error[$product->name . 'amount'] = 'The ' . $product->name . ' need to request because total amount is greater than $' . $amt;
                    }
                }
            }
        }*/
        if (count($error) > 0) {
            throw ValidationException::withMessages($error);
        }
        if($type == 'order'){
            $status = 5;
        }else{
            $status = 1;
        }
        $purchase = new PurchaseOrderModel();
        $purchase->user_id = Auth::user()->id;
        $purchase->boq_id = $request->boq;
        $purchase->project_id = $request->project;
        $purchase->request_by = $request->request_by;
        $purchase->stock_position = $request->stock_position;
        $purchase->phone_number = $request->phone_number;
        $purchase->supplier_id = $request->supplier;
        $purchase->status = 6;
        $purchase->date = date('Y-m-d', strtotime($request->date));
        $purchase->task_group_work_title = $request->task_group_categories;
        $purchase->purchase_type = $request->purchase_type;
        $purchase->work_group_type_id = $request->work_group_type_id;
        $purchase->warehouse_id = $request->warehouse_id;
        $purchase->using_to = $request->using_to;
        $purchase->save();
        $total_amount = 0;
        if($request->purchase_type==0){

            $boq_insert = BOQModel::find($request->boq);
            $boq_insert->status_editable = 1;
            $boq_insert->save();

        }

        $zone_id    = $request->zone_id;

        foreach ($checkbox as $key => $row) {
            $purchase_detail = new PurchaseOrderDetailModel();
            $purchase_detail->purchase_id = $purchase->id;
            $purchase_detail->product_id = $row;
            $purchase_detail->unit_id = $unit_id[$row];
            $purchase_detail->qty =$qty_per_house[$row];
            $purchase_detail->price = $retail[$row];
            $purchase_detail->purpose_and_location = $pur_location[$row];
            $purchase_detail->save();
            $total_amount+= ($qty_per_house[$row]*$retail[$row]);

            ProductModel::where("id",$row)->update(['current_price'=>$retail[$row]]);

            //Add Product To Stock
            $qty = !empty($qty_per_house[$row])?$qty_per_house[$row]:0;
            $check_warehouse = DB::table('nso007_construction_product_origin_stock')->where('warehouse_id',$request->warehouse_id)->first();
            PurchaseOrderDetailModel::where('id',$purchase_detail->id)->update(['date_add_to_stock'=>date('Y-m-d',strtotime($request->date)),'qty_added_to_stock'=>DB::raw('qty_added_to_stock + '.$qty)]);
            if(empty($check_warehouse)){
                $data = array(
                    'warehouse_id'=>$request->warehouse_id,
                    'product_id'=>$row,
                    'qty'=>$qty_per_house[$row],
                    'user_id'=>Auth::user()->id,
                    'zone_id'=>$zone_id
                );
                DB::table('nso007_construction_product_origin_stock')->insert($data);
            }else{
                $check_product = DB::table('nso007_construction_product_origin_stock')->where('product_id',$row)->where("zone_id",$zone_id)->where('warehouse_id',$request->warehouse_id)->first();

                if(empty($check_product)){
                    $data = array(
                        'warehouse_id'=>$request->warehouse_id,
                        'product_id'=>$row,
                        'qty'=>$qty_per_house[$row],
                        'user_id'=>Auth::user()->id,
                        'zone_id'=>$zone_id,
                        'using_to'=>$request->using_to
                    );
                    DB::table('nso007_construction_product_origin_stock')->insert($data);
                }else{
                    DB::table('nso007_construction_product_origin_stock')->where('product_id',$row)->where("zone_id",$zone_id)->where('warehouse_id',$request->warehouse_id)->where("using_to",$request->using_to)->increment('qty',$qty_per_house[$row]);
                }
            }
            //End
            
            if(!empty($request->boq)){
                $boq_request_po = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$request->boq)
                    ->where('product_id',$row)
                    ->where(function($query) use ($request){
                        if(!empty($request->task_group_categories))
                            $query->where('boq_detail_title',$request->task_group_categories);
                    })
                    ->first();
                if(empty($boq_request_po)){
                    DB::table("nso007_boq_request_po")->insert(['boq_id'=>$request->boq,'product_id'=>$row,'boq_detail_title'=>$request->task_group_categories,'total_qty_request'=>$qty_per_house[$row]]);
                }else{
                    DB::table("nso007_boq_request_po")->where('id',$boq_request_po->id)->update(['total_qty_request'=>DB::raw("total_qty_request+".$qty_per_house[$row])]);
                }
            }
            
        }
        $search_status = DB::table('nso007_construction_purchase_details')->where("purchase_id",$purchase->id)->get();
        $status_add = 0;
        foreach($search_status as $valu){
            if($valu->qty_added_to_stock<$valu->qty){
                $status_add = 0;
                break;
            }else{
                $status_add = 1;
            }
        }

        PurchaseOrderModel::where("id",$purchase->id)->update(['total_amount'=>$total_amount,'add_stock_status'=>$status_add,'status_editable'=>1,'zone_id'=>$zone_id]);
        \Globals::purchase_add_accounting($purchase->id);
        session()->put('success','Record was added successfully');
        \LogActivity::addToLog('Purchase order store successfully !');
        return response()->json(['route'=>route('construction-make-purchase-order.index'),'status'=>'success']);
    }

    /*public function add_product_stock_warehouse($data)
    {
        // dd($request->all());
        // $data_purchase = PurchaseOrderDetailModel::where('purchase_id',$request->purchase_id)->get();
        foreach($request->product_id as $k=>$val){
            $qty = !empty($request->qty_add_to_warehouse[$k])?$request->qty_add_to_warehouse[$k]:0;
            $check_warehouse = DB::table('nso007_construction_product_origin_stock')->where('warehouse_id',$request->warehouse_id)->first();
            PurchaseOrderDetailModel::where('id',$request->purchase_detail_id[$k])->update(['date_add_to_stock'=>date('Y-m-d',strtotime($request->date)),'qty_added_to_stock'=>DB::raw('qty_added_to_stock + '.$qty)]);
            if(empty($check_warehouse)){
                $data = array(
                    'warehouse_id'=>$request->warehouse_id,
                    'product_id'=>$val,
                    'qty'=>$request->qty_add_to_warehouse[$k],
                    'user_id'=>Auth::user()->id
                );
                DB::table('nso007_construction_product_origin_stock')->insert($data);
            }else{
                $check_product = DB::table('nso007_construction_product_origin_stock')->where('product_id',$val)->where('warehouse_id',$request->warehouse_id)->first();

                if(empty($check_product)){
                    $data = array(
                        'warehouse_id'=>$request->warehouse_id,
                        'product_id'=>$val,
                        'qty'=>$request->qty_add_to_warehouse[$k],
                        'user_id'=>Auth::user()->id,
                    );
                    DB::table('nso007_construction_product_origin_stock')->insert($data);
                }else{
                    DB::table('nso007_construction_product_origin_stock')->where('product_id',$val)->where('warehouse_id',$request->warehouse_id)->increment('qty',$request->qty_add_to_warehouse[$k]);
                }
            }
        }
        $search_status = DB::table('nso007_construction_purchase_details')->where("purchase_id",$request->purchase_id)->get();
        $status_add = 0;
        foreach($search_status as $valu){
            if($valu->qty_added_to_stock<$valu->qty){
                $status_add = 0;
                break;
            }else{
                $status_add = 1;
            }
        }
        PurchaseOrderModel::where('id',$request->purchase_id)->update(['add_stock_status'=>$status_add,'status_editable'=>1]);
        \LogActivity::addToLog('Product add to stock successfully !');
        return \back()->with('success','Add to warehouse successfully !');
    }*/

    public function show($id){
        $id = Crypt::decrypt($id);
        $purchase = PurchaseOrderModel::find($id);
        $detail = PurchaseOrderDetailModel::where('purchase_id',$id)->get();
        $supplier_phone = optional(DB::table('nso007_construction_suppliers')->where("id",$purchase->supplier_id)->first())->phone;
        return view("administrator.construction.purchase.purchase-order.show",compact('purchase','detail','supplier_phone'));
    }
    public function show_print(){
        $id = $_GET['purchase_id'];
        $purchase = PurchaseOrderModel::find($id);
        $detail = PurchaseOrderDetailModel::where('purchase_id',$id)->get();
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        $supplier_phone = optional(DB::table('nso007_construction_suppliers')->where("id",$purchase->supplier_id)->first())->phone;
        return view("administrator.construction.purchase.purchase-order.print_po",compact('purchase','detail','branch','supplier_phone'));
    }
    public function edit($id){
        $id = Crypt::decrypt($id);
        $purchase = PurchaseOrderModel::find($id);
        $boq = BOQModel::selectRaw('CONCAT(nso007_boq_construction.title," ( prepared by ",nso007_boq_construction.prepared_by," )") as boq_name,nso007_boq_construction.id')
            ->join('nso007_project_stock','nso007_project_stock.id','nso007_boq_construction.project_id')
            ->where('nso007_boq_construction.status', 0)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        
        $supplier = SuppliersModel::pluck('name', 'id');

        $projects=ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->pluck('project_name','id');

        $detail = PurchaseOrderDetailModel::join('nso007_construction_purchase',
                'nso007_construction_purchase.id',
                'nso007_construction_purchase_details.purchase_id'
            )
            ->join('nso007_construction_product','nso007_construction_product.id','nso007_construction_purchase_details.product_id')
            ->join('nso007_unit_construction','nso007_unit_construction.id','nso007_construction_product.unit_id')
            ->select('nso007_construction_product.name as product_name',
                'nso007_unit_construction.name as unit_name',
                'nso007_construction_purchase_details.*')
            ->where('purchase_id',$id)
            ->get();

        $product_boq_purchased_qty_balance = array();
        
        foreach($detail as $key=>$value)
        {
            $detail_pro = BOQDetailModel::where('boq_id', $purchase->boq_id)
                ->where("title",$purchase->task_group_work_title)
                ->select("*",DB::raw('SUM(qty_used_all_amount_of_house) as qty_product'))
                ->where("product_id",$value->product_id)
                ->first();

            $product_boq_purchased = DB::table("nso007_boq_request_po")->where("boq_id",$purchase->boq_id)->where("boq_detail_title",$purchase->task_group_work_title)->where("product_id",$value->product_id)->select("total_qty_request")->first();

            $product_boq_purchased_qty_balance[$key] = $detail_pro->qty_product - (!empty($product_boq_purchased)?(!empty($product_boq_purchased->total_qty_request)?$product_boq_purchased->total_qty_request:0):0);
            
        }

        $data_title = BOQDetailModel::where('boq_id',$purchase->boq_id)
            ->select("title")
            ->groupBy('title')
            ->pluck("title","title");

        return view("administrator.construction.purchase.purchase-order.edit", compact('boq','supplier','purchase','detail','projects','data_title','product_boq_purchased_qty_balance'));

    }
    public function update(Request $request, $id)
    {
        // dd("OK");
        $this->validate($request, [
            'project_id' => 'required',
            'request_by' => 'required',
            'stock_position' => 'required',
            'supplier_id' => 'required',
            'date' => 'required',

            'qty_per_house' => 'required',
            'retail' => 'required',
            'checkbox' => 'required',

            'qty_per_house.*' => 'required',
            'retail.*' => 'required',
            'checkbox.*' => 'required',
        ]);
        if($request->purchase_type==0){
            $this->validate($request,[
                'boq_id'=>'required',
                'task_group_categories'=>'required'
            ]);
        }
        //throw ValidationException::withMessages(['field_name' => 'This value is incorrect']);


        $checkbox = $request->checkbox;
        $qty_per_house = $request->qty_per_house;
        $retail = $request->retail;
        $unit_id = $request->unit_id;
        $pur_location = $request->pur_location;
        $type = $request->type;
        $error = [];
        // foreach ($checkbox as $key => $row) {
        //     $amount = $qty_per_house[$row] * $retail[$row];
        //     $cate_setting = PurchaseOrderSettingModel::where('type', 'category')->pluck('category_id');
        //     $amount_setting = PurchaseOrderSettingModel::where('type', 'amount')->pluck('amount');
        //     $product = ProductModel::find($row);
        //     foreach ($cate_setting as $ii => $cate) {
        //         if ($cate == $product->category_id) {
        //             $error[$product->name . 'cate'] = 'The ' . $product->name . ' can not Order without request !!';
        //         }
        //     }
        //     foreach ($amount_setting as $ii => $amt) {
        //         if ($amt < $amount) {
        //             $error[$product->name . 'amount'] = 'The ' . $product->name . ' need to request because total amount is greater than $' . $amt;
        //         }
        //     }

        // }
        // if (count($error) > 0) {
        //     throw ValidationException::withMessages($error);
        // }
        $purchase_old = PurchaseOrderModel::find($id);
        foreach(PurchaseOrderDetailModel::where('purchase_id',$id)->get() as $value){
            $boq_request_po_old = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$purchase_old->boq_id)
                    ->where('product_id',$value->product_id)
                    ->where('boq_detail_title',$purchase_old->task_group_work_title)
                    ->first();
            if(!empty($boq_request_po_old))
                DB::table("nso007_boq_request_po")->where('id',$boq_request_po_old->id)->update(['total_qty_request'=>DB::raw("total_qty_request - ".$value->qty)]);
        }
        
        
        $purchase = PurchaseOrderModel::find($id);
        $purchase->user_id = Auth::user()->id;
        $purchase->boq_id = $request->boq_id;
        $purchase->project_id = $request->project_id;
        $purchase->request_by = $request->request_by;
        $purchase->stock_position = $request->stock_position;
        $purchase->phone_number = $request->phone_number;
        $purchase->supplier_id = $request->supplier_id;
        $purchase->date = date('Y-m-d', strtotime($request->date));
        $purchase->task_group_work_title = $request->task_group_categories;
        $purchase->purchase_type = $request->purchase_type;
        $purchase->save();
        $total_amount = 0;
        PurchaseOrderDetailModel::where('purchase_id',$id)->delete();
        foreach ($checkbox as $key => $row) {
            $purchase_detail = new PurchaseOrderDetailModel();
            $purchase_detail->purchase_id = $id;
            $purchase_detail->product_id = $row;
            $purchase_detail->unit_id = $unit_id[$row];
            $purchase_detail->qty =$qty_per_house[$row];
            $purchase_detail->price = $retail[$row];
            $purchase_detail->purpose_and_location = $pur_location[$row];
            $purchase_detail->save();
            $total_amount += ($qty_per_house[$row]*$retail[$row]);

            if(!empty($request->boq_id)){
                $boq_request_po = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$request->boq_id)
                    ->where('product_id',$row)
                    ->where('boq_detail_title',$request->task_group_categories)
                    ->first();
                if(empty($boq_request_po)){
                    DB::table("nso007_boq_request_po")->insert(['boq_id'=>$request->boq_id,'product_id'=>$row,'boq_detail_title'=>$request->task_group_categories,'total_qty_request'=>$qty_per_house[$row]]);
                }else{
                    DB::table("nso007_boq_request_po")->where('id',$boq_request_po->id)->update(['total_qty_request'=>DB::raw("total_qty_request + ".$qty_per_house[$row])]);
                }
            }

        }

        

        PurchaseOrderModel::where("id",$id)->update(['total_amount'=>$total_amount]);
        session()->put('success','Record was updated successfully');
        \LogActivity::addToLog('Purchase order store successfully !');
        return redirect()->route('construction-make-purchase-order.index');
    }

    public function financial_approval_index(Request $request)
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->pluck("project_name","id")->prepend("Please Select","");
        $supplers = DB::table("nso007_construction_suppliers")->select("name",'id')->pluck("name","id")->prepend("Please Select","");
        if($request->ajax()){

            $request_name = $request->request_name;
            $supplier_id = $request->supplier_id;
            $stock_position = $request->stock_position;
            $project_id = $request->project_id;

            $data =PurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_construction_purchase.project_id")
                ->select("nso007_construction_purchase.*","nso007_project_stock.project_name")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->when(request('request_name'),function($query) use ($request_name){
                    $query->where("nso007_construction_purchase.request_by","LIKE","%".$request_name."%");
                })
                ->when(request('supplier_id'),function($query) use ($supplier_id){
                    $query->where("nso007_construction_purchase.supplier_id",$supplier_id);
                })
                ->when(request('stock_position'),function($query) use ($stock_position){
                    $query->where("stock_position","LIKE","%".$stock_position."%");
                })
                ->when(request('project_id'),function($query) use($project_id){
                    $query->where('nso007_construction_purchase.project_id',$project_id);
                })
                ->where('nso007_construction_purchase.status',"!=",2)
                ->orderBy('nso007_construction_purchase.date','DESC')
                ->get();
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('status',function($data){
                        if($data->status==1) return "<span style='color:rgb(184, 95, 11)'>Pendding</span>";
                        else if($data->status==0) return "<span style='color:rgb(42, 156, 8)'>Approved</span>";
                        else if($data->status==4) return "<span style='color:rgb(42, 156, 8)'>CEO Approved</span>";
                        else if($data->status == 3) return "<span style='color:rgb(42, 156, 8)'>Financial Approved</span>";
                        else if($data->status==5) {
                            return "<span style='color:rgb(17, 118, 185)'>Order</span>";
                        }
                        else if($data->status==6) return "<span style='color:rgb(17, 118, 185)'>Verified</span>";
                        else {

                        }
                    })
                    ->editColumn('amount',function($data)
                    {
                        return "$".number_format($data->total_amount,2);
                    })
                    ->editColumn("phone_number",function($data){
                        return optional(SuppliersModel::find($data->supplier_id))->phone;
                    })
                    ->addColumn('action', function($data) {
                        $button ="";
                        if(Gate::check('Construction-Financial-Approval-Purchase-Order-Show'))
                            $button .= " <a title='Show Detail' target = '_blank' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->id))."' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";
                            if($data->status==1||$data->status==4){
                                if(Gate::check('Construction-Financial-Approval-Purchase-Order-Appove'))
                                    $button .= " <a title='Approval' href='".route('construction-purchase.financial_approval_form',Crypt::encrypt($data->id))."' class='btn btn-sm btn-primary' style='margin-left:5px !important;'><i class='fa fa-check' ></i></a> ";
                                if(Gate::check('Construction-Financial-Approval-Purchase-Order-Reject'))
                                    $button .= " <a title='Reject' onclick='display_reject(".$data->id.")' href='javascript:void(0)' class='btn btn-sm btn-danger' style='margin-left:5px !important;'><i class='fa fa-times' ></i></a>";
                            }
    
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
            }
            return view("administrator.construction.financial-approval.index_purchase_order",compact('projects','supplers'));
    }
    public function financial_approval_form($id)
    {
        $id = Crypt::decrypt($id);
        $purchase = PurchaseOrderModel::find($id);
        $boq = BOQModel::selectRaw('CONCAT(title," ( prepared by ",prepared_by," )") as boq_name,nso007_boq_construction.id')
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_boq_construction.project_id")
            ->where('nso007_boq_construction.status', 0)
            ->where('nso007_boq_construction.id',$purchase->boq_id)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        
        $supplier = SuppliersModel::pluck('name', 'id');
        $projects=ProjectModel::where('id',$purchase->project_id)->pluck('project_name','id');
        $detail = PurchaseOrderDetailModel::join('nso007_construction_purchase','nso007_construction_purchase.id','nso007_construction_purchase_details.purchase_id')->join('nso007_construction_product','nso007_construction_product.id','nso007_construction_purchase_details.product_id')
        ->join('nso007_unit_construction','nso007_unit_construction.id','nso007_construction_product.unit_id')
        ->select('nso007_construction_product.name as product_name','nso007_unit_construction.name as unit_name','nso007_construction_purchase_details.*')
        ->where('purchase_id',$id)
        ->get();

        $product_boq_purchased_qty_balance = array();
        
        foreach($detail as $key=>$value)
        {
            $detail_pro = BOQDetailModel::where('boq_id', $purchase->boq_id)
                ->where("title",$purchase->task_group_work_title)
                ->select("*",DB::raw('SUM(qty_used_all_amount_of_house) as qty_product'))
                ->where("product_id",$value->product_id)
                ->first();

            $product_boq_purchased = DB::table("nso007_boq_request_po")->where("boq_id",$purchase->boq_id)->where("boq_detail_title",$purchase->task_group_work_title)->where("product_id",$value->product_id)->select("total_qty_request")->first();

            $product_boq_purchased_qty_balance[$key] = $detail_pro->qty_product - (!empty($product_boq_purchased)?(!empty($product_boq_purchased->total_qty_request)?$product_boq_purchased->total_qty_request:0):0);
            
        }
        return view("administrator.construction.financial-approval.form_approve_po",compact('boq','supplier','purchase','detail','projects','product_boq_purchased_qty_balance'));
    }

    public function financial_approval_store(Request $request,$id)
    {
        // dd("OK");
        $this->validate($request, [
            'project_id' => 'required',
            'request_by' => 'required',
            'stock_position' => 'required',
            'supplier_id' => 'required',
            'date' => 'required',

            'qty_per_house' => 'required',
            'retail' => 'required',
            'checkbox' => 'required',

            'qty_per_house.*' => 'required',
            'retail.*' => 'required',
            'checkbox.*' => 'required',
        ]);
        //throw ValidationException::withMessages(['field_name' => 'This value is incorrect']);
        if($request->purchase_type==0)
        {
            $this->validate($request,[
                'boq_id' => 'required'
            ]);
        }

        $checkbox = $request->checkbox;
        $qty_per_house = $request->qty_per_house;
        $retail = $request->retail;
        $unit_id = $request->unit_id;
        $pur_location = $request->pur_location;
        $type = $request->type;
        $error = [];

        $purchase_old = PurchaseOrderModel::find($id);
        foreach(PurchaseOrderDetailModel::where('purchase_id',$id)->get() as $value){
            $boq_request_po_old = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$purchase_old->boq_id)
                    ->where('product_id',$value->product_id)
                    ->where('boq_detail_title',$purchase_old->task_group_work_title)
                    ->first();
            if(!empty($boq_request_po_old))
                DB::table("nso007_boq_request_po")->where('id',$boq_request_po_old->id)->update(['total_qty_request'=>DB::raw("total_qty_request - ".$value->qty)]);
        }
        
        $purchase = PurchaseOrderModel::find($id);
        $purchase->financial_user_id = Auth::user()->id;
        if($request->purchase_type==0){
            $purchase->boq_id = $request->boq_id;
        }
        $purchase->project_id = $request->project_id;
        $purchase->request_by = $request->request_by;
        $purchase->stock_position = $request->stock_position;
        $purchase->phone_number = $request->phone_number;
        $purchase->supplier_id = $request->supplier_id;
        $purchase->date = date('Y-m-d', strtotime($request->date));
        if($purchase->status==1){
            $purchase->status = 3;
        }
        if($purchase->status==4){
            $purchase->status = 0;
        }
        
        $purchase->save();

        $total_amount = 0;

        PurchaseOrderDetailModel::where('purchase_id',$id)->delete();
        foreach ($checkbox as $key => $row) {
            $purchase_detail = new PurchaseOrderDetailModel();
            $purchase_detail->purchase_id = $id;
            $purchase_detail->product_id = $row;
            $purchase_detail->unit_id = $unit_id[$row];
            $purchase_detail->qty =$qty_per_house[$row];
            $purchase_detail->price = $retail[$row];
            $purchase_detail->purpose_and_location = $pur_location[$row];
            $purchase_detail->save();
            $total_amount += ($qty_per_house[$row]*$retail[$row]);
            
            if(!empty($request->boq_id)){
                $boq_request_po = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$request->boq_id)
                    ->where('product_id',$row)
                    ->where('boq_detail_title',$purchase->task_group_work_title)
                    ->first();
                if(empty($boq_request_po)){
                    DB::table("nso007_boq_request_po")->insert(['boq_id'=>$request->boq_id,'product_id'=>$row,'boq_detail_title'=>$purchase->task_group_work_title,'total_qty_request'=>$qty_per_house[$row]]);
                }else{
                    DB::table("nso007_boq_request_po")->where('id',$boq_request_po->id)->update(['total_qty_request'=>DB::raw("total_qty_request + ".$qty_per_house[$row])]);
                }
            }
        }

        if($total_amount>\Globals::condition_financial_approval()){
            PurchaseOrderModel::where("id",$id)->update(['status'=>3]);
        }
        else{
            PurchaseOrderModel::where("id",$id)->update(['status'=>0]);
        }

        PurchaseOrderModel::where("id",$id)->update(['total_amount'=>$total_amount]);

        session()->put('success','Record was approved successfully');
        \LogActivity::addToLog('Purchase order Approval successfully !');
        return redirect()->route('construction-purchase.financial_approval_index');
    }
    public function financial_reject_store()
    {
        $id                             = $_GET['purchase_id'];
        $des                            = $_GET['description'];
        $purchase                       = PurchaseOrderModel::find($id);
        $purchase->status               = 2;
        $purchase->financial_user_id    = Auth::user()->id;
        $purchase->reason_reject_financial = $des ? $des : null;
        $purchase->save();

        $this->decrease_qty_po_boq($id);

        session()->put('success','Record was reject successfully');
        \LogActivity::addToLog('Purchase order reject successfully !');
        return redirect()->route('construction-purchase.financial_approval_index');
    }
    public function ceo_approval_index(Request $request)
    {
        if($request->ajax()){
            $request_name = $request->request_name;
            $supplier_id = $request->supplier_id;
            $stock_position = $request->stock_position;
            $project_id = $request->project_id;
            $data =PurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_construction_purchase.project_id")
                ->select("nso007_construction_purchase.*","nso007_project_stock.project_name")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->when(request('request_name'),function($query) use ($request_name){
                    $query->where("nso007_construction_purchase.request_by","LIKE","%".$request_name."%");
                })
                ->when(request('supplier_id'),function($query) use ($supplier_id){
                    $query->where("nso007_construction_purchase.supplier_id",$supplier_id);
                })
                ->when(request('stock_position'),function($query) use ($stock_position){
                    $query->where("stock_position","LIKE","%".$stock_position."%");
                })
                ->when(request('project_id'),function($query) use($project_id){
                    $query->where('nso007_construction_purchase.project_id',$project_id);
                })
                ->where('nso007_construction_purchase.status',3)
                ->where('nso007_construction_purchase.total_amount','>',\Globals::condition_financial_approval())
                ->orderBy('nso007_construction_purchase.date','DESC')
                ->get();
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('phone_number',function($data){
                        return optional(SuppliersModel::find($data->supplier_id))->phone;
                    })
                    ->editColumn('amount',function($data){
                        return "$".number_format($data->total_amount,2);
                    })
                    ->editColumn('status',function($data){
                        if($data->status==1) return "<span style='color:rgb(184, 95, 11)'>Pendding</span>";
                        else if($data->status==0) return "<span style='color:rgb(42, 156, 8)'>Approved</span>";
                        else if($data->status==4) return "<span style='color:rgb(42, 156, 8)'>CEO Approved</span>";
                        else if($data->status == 3) return "<span style='color:rgb(42, 156, 8)'>Financial Approved</span>";
                        else if($data->status==5) {
                            return "<span style='color:rgb(17, 118, 185)'>Order</span>";
                        }
                        else if($data->status == 6) return "<span style='color:rgb(17, 118, 185)'>Verified</span>";
                        else {}
                    })
                    ->addColumn('action', function($data) {
                        $button ="";
                        if(Gate::check('Construction-CEO-Approval-Purchase-Order-Show'))
                            $button .= " <a title='Show Detail' target = '_blank' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->id))."' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";
                        if($data->status==1||$data->status==3){
                            if(Gate::check('Construction-CEO-Approval-Purchase-Order-Appove'))
                                $button .= " <a title='Approval' href='javascript:void(0)' onclick='display_form_approval(".$data->id.")' class='btn btn-sm btn-primary' style='margin-left:5px !important;'><i class='fa fa-check' ></i></a> ";
                            if(Gate::check('Construction-CEO-Approval-Purchase-Order-Reject'))
                                $button .= " <a title='Reject' onclick='display_reject(".$data->id.")' href='javascript:void(0)' class='btn btn-sm btn-danger' style='margin-left:5px !important;'><i class='fa fa-times' ></i></a>";
                        }
    
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
            }

            $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck("project_name","id")->prepend("Please Select","");
            $supplers = DB::table("nso007_construction_suppliers")->select("name",'id')->pluck("name","id")->prepend("Please Select","");
            
            return view("administrator.construction.ceo-approval.index_purchase_order",compact('projects','supplers'));
    }
    public function ceo_approval_store()
    {
        $id = $_GET['purchase_id'];
        $purchase = PurchaseOrderModel::find($id);
        $purchase->ceo_user_id = Auth::user()->id;
        if($purchase->status==1){
            $purchase->status = 4;
        }else if($purchase->status==3){
            $purchase->status = 0;
        }
        else{}
        $purchase->save();
        session()->put('success','Record was approval successfully');
        \LogActivity::addToLog('Purchase order approve successfully !');
        return redirect()->route('construction-purchase.ceo_approval_index');
    }
    public function ceo_reject_store()
    {
        $id                             = $_GET['purchase_id'];
        $des                            = $_GET['description'];
        $purchase                       = PurchaseOrderModel::find($id);
        $purchase->ceo_user_id          = Auth::user()->id;
        $purchase->status               = 2;
        $purchase->reason_reject_ceo    = $des ? $des : null;
        $purchase->save();

        $this->decrease_qty_po_boq($id);

        session()->put('success','Record was reject successfully');
        \LogActivity::addToLog('Purchase order reject successfully !');
        return redirect()->route('construction-purchase.ceo_approval_index');
    }
    public function verify_product($purchase_id)
    {
        $id = Crypt::decrypt($purchase_id);
        $purchase = PurchaseOrderModel::find($id);
        $boq = BOQModel::selectRaw('CONCAT(title," ( prepared by ",prepared_by," )") as boq_name,nso007_boq_construction.id')
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_boq_construction.project_id")
            ->where('nso007_boq_construction.status', 0)
            ->where('nso007_boq_construction.id',$purchase->boq_id)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        
        $supplier = SuppliersModel::pluck('name', 'id');
        $projects=ProjectModel::where('id',$purchase->project_id)->pluck('project_name','id');
        $detail = PurchaseOrderDetailModel::join('nso007_construction_purchase','nso007_construction_purchase.id','nso007_construction_purchase_details.purchase_id')->join('nso007_construction_product','nso007_construction_product.id','nso007_construction_purchase_details.product_id')
        ->join('nso007_unit_construction','nso007_unit_construction.id','nso007_construction_product.unit_id')
        ->select('nso007_construction_product.name as product_name','nso007_unit_construction.name as unit_name','nso007_construction_purchase_details.*')
        ->where('purchase_id',$id)
        ->get();
        
        return view("administrator.construction.purchase.purchase-order.form_verify_product", compact('boq','supplier','purchase','detail','projects'));
    }
    public function request_ajust_purchase(Request $request,$id)
    {
        $this->validate($request, [
            'project_id' => 'required',
            'request_by' => 'required',
            'stock_position' => 'required',
            'supplier_id' => 'required',
            'date' => 'required',

            'qty_per_house' => 'required',
            'retail' => 'required',
            'checkbox' => 'required',

            'qty_per_house.*' => 'required',
            'retail.*' => 'required',
            'checkbox.*' => 'required',
        ]);
        if($request->purchase_type==0){
            $this->validate($request,[
                'boq_id' => 'required'
            ]);
        }
        //throw ValidationException::withMessages(['field_name' => 'This value is incorrect']);
        

        $checkbox = $request->checkbox;
        $qty_per_house = $request->qty_per_house;
        $retail = $request->retail;
        $unit_id = $request->unit_id;
        $pur_location = $request->pur_location;
        $type = $request->type;
        $error = [];

        PurchaseOrderModel::where('id',$id)->update(['ajust_status'=>'1']);


        $purchase_old = PurchaseOrderModel::find($id);
        foreach(PurchaseOrderDetailModel::where('purchase_id',$id)->get() as $value){
            $boq_request_po_old = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$purchase_old->boq_id)
                    ->where('product_id',$value->product_id)
                    ->where('boq_detail_title',$purchase_old->task_group_work_title)
                    ->first();
            if(!empty($boq_request_po_old))
                DB::table("nso007_boq_request_po")->where('id',$boq_request_po_old->id)->update(['total_qty_request'=>DB::raw("total_qty_request - ".$value->qty)]);
        }
        
        $purchase = new AjustPurchaseOrderModel;
        $purchase->user_id = Auth::user()->id;
        $purchase->purchase_id = $id;
        if($request->purchase_type==0)
        {
            $purchase->boq_id = $request->boq_id;
        }
        $purchase->project_id = $request->project_id;
        $purchase->request_by = $request->request_by;
        $purchase->stock_position = $request->stock_position;
        $purchase->phone_number = $request->phone_number;
        $purchase->supplier_id = $request->supplier_id;
        $purchase->date = date('Y-m-d', strtotime($request->date));
        $purchase->status = 1;
        $purchase->save();
        foreach ($checkbox as $key => $row) {
            $purchase_detail = new AjustPurchaseOrderDetailModel();
            $purchase_detail->request_ajust_purchase_id = $purchase->id;
            $purchase_detail->product_id = $row;
            $purchase_detail->unit_id = $unit_id[$row];
            $purchase_detail->qty =$qty_per_house[$row];
            $purchase_detail->price = $retail[$row];
            $purchase_detail->purpose_and_location = $pur_location[$row];
            $purchase_detail->save();
            
            if(!empty($request->boq_id))
            {
                $boq_request_po = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$request->boq_id)
                    ->where('product_id',$row)
                    ->where('boq_detail_title',PurchaseOrderModel::find($id)->task_group_work_title)
                    ->first();
                if(empty($boq_request_po)){
                    DB::table("nso007_boq_request_po")->insert(['boq_id'=>$request->boq_id,'product_id'=>$row,'boq_detail_title'=>PurchaseOrderModel::find($id)->task_group_work_title,'total_qty_request'=>$qty_per_house[$row]]);
                }else{
                    DB::table("nso007_boq_request_po")->where('id',$boq_request_po->id)->update(['total_qty_request'=>DB::raw("total_qty_request + ".$qty_per_house[$row])]);
                }
            }
            
        }
        
        session()->put('success','Record was created successfully');
        \LogActivity::addToLog('request ajust P.O successfully !');
        
        return redirect()->route('construction-make-purchase-order.index');
    }
    public function financial_approval_ajust_purchase_index(Request $request)
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->pluck("project_name","id")->prepend("Please Select","");

        $supplers = DB::table("nso007_construction_suppliers")->select("name",'id')->pluck("name","id")->prepend("Please Select","");

        if($request->ajax()){
            $request_name = $request->request_name;
            $supplier_id = $request->supplier_id;
            $stock_position = $request->stock_position;
            $project_id = $request->project_id;
            $data =AjustPurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_request_ajust_purchase_construction.project_id")
                ->select("nso007_request_ajust_purchase_construction.*","nso007_project_stock.project_name")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->when(request('request_name'),function($query) use ($request_name){
                    $query->where("nso007_request_ajust_purchase_construction.request_by","LIKE","%".$request_name."%");
                })
                ->when(request('supplier_id'),function($query) use ($supplier_id){
                    $query->where("nso007_request_ajust_purchase_construction.supplier_id",$supplier_id);
                })
                ->when(request('stock_position'),function($query) use ($stock_position){
                    $query->where("stock_position","LIKE","%".$stock_position."%");
                })
                ->when(request('project_id'),function($query) use($project_id){
                    $query->where('nso007_request_ajust_purchase_construction.project_id',$project_id);
                })
                ->where('nso007_request_ajust_purchase_construction.status',"!=",2)
                ->orderBy('nso007_request_ajust_purchase_construction.date','DESC')
                ->get();
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('status',function($data){
                        if($data->status==1) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O</span>";
                        else if($data->status==3) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(Financial Approve)</span>";
                        else if($data->status==4) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(CEO Approve)</span>";
                        else if($data->status==0) return "<span style='color:rgb(184, 95, 11)'>Approved</span>";
                        else {}
                    })
                    ->addColumn('action', function($data) {
                        $button ="";
                        if(Gate::check('Construction-Financial-Approval-Ajustment-Purchase-Show'))
                            $button .= " <a title='Show Detail' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->purchase_id))."' target='_blank' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";

                        if($data->status!=0){
                            if(Gate::check('Construction-Financial-Approval-Ajustment-Purchase-Approve')||Gate::check('Construction-Financial-Approval-Ajustment-Purchase-Reject'))
                                $button .= " <a title='Approve Ajust P.O' href='".route('construction-purchase.financial_approval_ajust_purchase_form',Crypt::encrypt($data->id))."' class='btn btn-sm btn-primary' style='margin-left:10px;'><i class='fa fa-check-circle'></i></a> ";
                        
                        }
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
        }
        return view("administrator.construction.financial-approval.ajust_purchase_index",compact('projects','supplers'));
    }
    public function financial_approval_ajust_purchase_form($request_ajust_purchase_id)
    {
        $id = Crypt::decrypt($request_ajust_purchase_id);
        $request_ajust_purchase = AjustPurchaseOrderModel::where('id',$id)->first();
        $boq = BOQModel::selectRaw('CONCAT(building_type," (",prepared_by,")") as boq_name,nso007_boq_construction.id')
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_boq_construction.project_id")
            ->where('nso007_boq_construction.status', 0)
            ->where('nso007_boq_construction.id',$request_ajust_purchase->boq_id)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        
        $supplier = SuppliersModel::pluck('name', 'id');
        $projects=ProjectModel::where('id',$request_ajust_purchase->project_id)->pluck('project_name','id');
        $detail = AjustPurchaseOrderDetailModel::join('nso007_request_ajust_purchase_construction','nso007_request_ajust_purchase_construction.id','nso007_request_ajust_purchase_detail_construction.request_ajust_purchase_id')->join('nso007_construction_product','nso007_construction_product.id','nso007_request_ajust_purchase_detail_construction.product_id')
        ->join('nso007_unit_construction','nso007_unit_construction.id','nso007_construction_product.unit_id')
        ->select('nso007_construction_product.name as product_name','nso007_unit_construction.name as unit_name','nso007_request_ajust_purchase_detail_construction.*')
        ->where('request_ajust_purchase_id',$request_ajust_purchase->id)
        ->get();
        return view("administrator.construction.financial-approval.ajust_purchase_form", compact('boq','supplier','request_ajust_purchase','detail','projects'));
    }
    public function financial_approval_ajust_purchase_store(Request $request,$request_ajust_purchase_id)
    {
        $status = 99;
        $request_ajust_purchase = AjustPurchaseOrderModel::find($request_ajust_purchase_id);

        $request_ajust_purchase->financial_user_id = Auth::user()->id;
        if($request_ajust_purchase->status==1){
            $request_ajust_purchase->status = 3;
            $status = 3;
        }else if($request_ajust_purchase->status==4){
            $request_ajust_purchase->status = 0;
            $status = 0;
        }
        else{}

        $total_amount_check = 0;
        foreach(AjustPurchaseOrderDetailModel::where("request_ajust_purchase_id",$request_ajust_purchase_id)->get() as $k=>$v){
            $total_amount_check += $v->qty*$v->price;
        }
        if($total_amount_check>\Globals::condition_financial_approval()){

        }else{
            $request_ajust_purchase->status = 0;
            $status = 0;
        }

        $request_ajust_purchase->save();



        $purchase = PurchaseOrderModel::find($request_ajust_purchase->purchase_id);
        if($status==0){
            $purchase->ajust_status = 0;
            $status = 0;
        }
        else{
            if($purchase->ajust_status==1){
                $purchase->ajust_status = 2;
                $status = 3;
            }
            else if($purchase->ajust_status==3)
            {
                $purchase->ajust_status = 0;
                $status = 0;
            }
            else{}
        }
        
        $total_amount = 0;
        if($status==0)
        {
            foreach(AjustPurchaseOrderDetailModel::where("request_ajust_purchase_id",$request_ajust_purchase_id)->get() as $k=>$val){
                PurchaseOrderDetailModel::where('purchase_id',$request_ajust_purchase->purchase_id)->where("product_id",$val->product_id)->update(["qty"=>$val->qty,'price'=>$val->price]);
            }
            $total_amount += $val->qty*$val->price;
            $purchase->total_amount = $total_amount;
            $purchase->status = 6;
        }
        
        $purchase->save();

        if($purchase->purchase_type==0&&$status==0){
            $purchase_detail= PurchaseOrderDetailModel::where('purchase_id',$request_ajust_purchase->purchase_id)->get();
            $boq            = BOQModel::find($purchase->boq_id);
            foreach($purchase_detail as $key => $pur){
                BOQDetailModel::whereNull('specific')
                ->where([['product_id',$pur->product_id],['boq_id',$purchase->boq_id],['title',$purchase->task_group_work_title]])->update([
                    'qty_used_one_house'            => DB::raw("qty_used_one_house + ".($pur->qty/$boq->amount_of_house_used)),
                    'qty_used_all_amount_of_house'  => DB::raw("qty_used_all_amount_of_house + ".$pur->qty) 
                ]);
            }
        }

        \Globals::purchase_add_accounting($purchase->id);

        session()->put('success','successfully');
        \LogActivity::addToLog('Ajust P.O approve successfully !');
        return redirect()->route('construction-purchase.financial_approval_ajust_purchase_index');
    }
    public function ceo_approval_ajust_purchase_index(Request $request)
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->pluck("project_name","id")->prepend("Please Select","");
        $supplers = DB::table("nso007_construction_suppliers")->select("name",'id')->pluck("name","id")->prepend("Please Select","");

        if($request->ajax()){
            $request_name = $request->request_name;
            $supplier_id = $request->supplier_id;
            $stock_position = $request->stock_position;
            $project_id = $request->project_id;
            $data =AjustPurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_request_ajust_purchase_construction.project_id")
                ->select("nso007_request_ajust_purchase_construction.*","nso007_project_stock.project_name")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->when(request('request_name'),function($query) use ($request_name){
                    $query->where("nso007_request_ajust_purchase_construction.request_by","LIKE","%".$request_name."%");
                })
                ->when(request('supplier_id'),function($query) use ($supplier_id){
                    $query->where("nso007_request_ajust_purchase_construction.supplier_id",$supplier_id);
                })
                ->when(request('stock_position'),function($query) use ($stock_position){
                    $query->where("stock_position","LIKE","%".$stock_position."%");
                })
                ->when(request('project_id'),function($query) use($project_id){
                    $query->where('nso007_request_ajust_purchase_construction.project_id',$project_id);
                })
                ->where('nso007_request_ajust_purchase_construction.status',3)
                
                ->orderBy('nso007_request_ajust_purchase_construction.date','DESC')
                ->get();
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('status',function($data){
                        if($data->status==1) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O</span>";
                        else if($data->status==3) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(Financial Approve)</span>";
                        else if($data->status==4) return "<span style='color:rgb(184, 95, 11)'>Request Ajust P.O(CEO Approve)</span>";
                        else if($data->status==0) return "<span style='color:rgb(184, 95, 11)'>Approved</span>";
                        else {}
                    })
                    ->addColumn('action', function($data) {
                        $button ="";
                        if(Gate::check('Construction-CEO-Approval-Ajustment-Purchase-Show'))
                            $button .= " <a title='Show Detail' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->purchase_id))."' target='_blank' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";
                        if($data->status!=0){
                            if(Gate::check('Construction-CEO-Approval-Ajustment-Purchase-Approve')||Gate::check('Construction-CEO-Approval-Ajustment-Purchase-Reject'))
                                $button .= " <a title='Approve Ajust P.O' href='".route('construction-purchase.ceo_approval_ajust_purchase_form',Crypt::encrypt($data->id))."' class='btn btn-sm btn-primary' style='margin-left:10px;'><i class='fa fa-check-circle' aria-hidden='true'></i></a> ";
                        }
                        
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
        }
        return view("administrator.construction.ceo-approval.index_ajust_purchase",compact('projects','supplers'));
    }
    public function ceo_approval_ajust_purchase_form($request_ajust_purchase_id)
    {
        $id = Crypt::decrypt($request_ajust_purchase_id);
        $request_ajust_purchase = AjustPurchaseOrderModel::where('id',$id)->first();
        
        $boq = BOQModel::selectRaw('CONCAT(building_type," (",prepared_by,")") as boq_name,nso007_boq_construction.id')
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_boq_construction.project_id")
            ->where('nso007_boq_construction.status', 0)
            ->where('nso007_boq_construction.id',$request_ajust_purchase->boq_id)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('boq_name', 'id');
        
        $supplier = SuppliersModel::pluck('name', 'id');
        $projects=ProjectModel::where('id',$request_ajust_purchase->project_id)->pluck('project_name','id');
        $detail = AjustPurchaseOrderDetailModel::join('nso007_request_ajust_purchase_construction','nso007_request_ajust_purchase_construction.id','nso007_request_ajust_purchase_detail_construction.request_ajust_purchase_id')->join('nso007_construction_product','nso007_construction_product.id','nso007_request_ajust_purchase_detail_construction.product_id')
        ->join('nso007_unit_construction','nso007_unit_construction.id','nso007_construction_product.unit_id')
        ->select('nso007_construction_product.name as product_name','nso007_unit_construction.name as unit_name','nso007_request_ajust_purchase_detail_construction.*')
        ->where('request_ajust_purchase_id',$request_ajust_purchase->id)
        ->get();
        
        return view("administrator.construction.ceo-approval.ajust_purchase_form", compact('boq','supplier','request_ajust_purchase','detail','projects'));
    }
    public function ceo_approval_ajust_purchase_store(Request $request,$request_ajust_purchase_id)
    {
        $request_ajust_purchase = AjustPurchaseOrderModel::find($request_ajust_purchase_id);
        $request_ajust_purchase->ceo_user_id = Auth::user()->id;
        $status = 99;
        if($request_ajust_purchase->status==1){
            $request_ajust_purchase->status = 4;
        }else if($request_ajust_purchase->status==3){
            $request_ajust_purchase->status = 0;
        }
        else{}
        $request_ajust_purchase->save();
        $purchase = PurchaseOrderModel::find($request_ajust_purchase->purchase_id);
        if($purchase->ajust_status==1){
            $purchase->ajust_status = 3;
        }
        else if($purchase->ajust_status==2)
        {
            $purchase->ajust_status = 0;
            $total_amount = 0;

            foreach(AjustPurchaseOrderDetailModel::where("request_ajust_purchase_id",$request_ajust_purchase_id)->get() as $k=>$val){
                PurchaseOrderDetailModel::where('purchase_id',$request_ajust_purchase->purchase_id)->where("product_id",$val->product_id)->update(["qty"=>$val->qty,'price'=>$val->price]);
            }
            $total_amount += $val->qty*$val->price;
            $purchase->total_amount = $total_amount;

            if($purchase->purchase_type==0){
                $purchase_detail= PurchaseOrderDetailModel::where('purchase_id',$request_ajust_purchase->purchase_id)->get();
                $boq            = BOQModel::find($purchase->boq_id);
                foreach($purchase_detail as $key => $pur){
                    BOQDetailModel::whereNull('specific')
                    ->where([['product_id',$pur->product_id],['boq_id',$purchase->boq_id],['title',$purchase->task_group_work_title]])->update([
                        'qty_used_one_house'            => DB::raw("qty_used_one_house + ".($pur->qty/$boq->amount_of_house_used)),
                        'qty_used_all_amount_of_house'  => DB::raw("qty_used_all_amount_of_house + ".$pur->qty) 
                    ]);
                }
            }

            $purchase->status = 6;
        }
        else{}
        $purchase->save();

        \Globals::purchase_add_accounting($purchase->id);

        session()->put('success','successfully');
        \LogActivity::addToLog('Ajust P.O approve successfully !');
        return redirect()->route('construction-purchase.ceo_approval_ajust_purchase_index');
    }
    public function order_product()
    {
        $purchase_id    = $_GET['purchase_id'];
        PurchaseOrderModel::where('id',$purchase_id)->update(['status'=>5]);
        \LogActivity::addToLog('Product order successfully !');
        return \back()->with("success","successfully");
    }
    public function verify_order_product(){
        
        $purchase_id    = $_GET['purchase_id'];
        $purchase       = PurchaseOrderModel::find($purchase_id);
        $purchase_detail= PurchaseOrderDetailModel::where('purchase_id',$purchase_id)->get();
        if($purchase->purchase_type==0)
        {
            $boq            = BOQModel::find($purchase->boq_id);
            foreach($purchase_detail as $key => $pur){
                BOQDetailModel::whereNull('specific')
                ->where([['product_id',$pur->product_id],['boq_id',$purchase->boq_id],['title',$purchase->task_group_work_title]])->update([
                    'qty_used_one_house'            => DB::raw("qty_used_one_house + ".($pur->qty/$boq->amount_of_house_used)),
                    'qty_used_all_amount_of_house'  => DB::raw("qty_used_all_amount_of_house + ".$pur->qty) 
                ]);
            }
        }
        PurchaseOrderModel::where('id',$purchase_id)->update(['status'=>6]);

        \Globals::purchase_add_accounting($purchase_id);//Add to accounting
        
        \LogActivity::addToLog('product verified successfully !');
        return redirect()->route('construction-make-purchase-order.index')->with("success","successfully");
    }
    public function financial_reject_ajust_po()
    {
        $request_ajust_purchase_id = $_GET['request_ajust_purchase_id'];
        $reject_reason = $_GET['reject_reason'];
        $request_ajust_purchase = AjustPurchaseOrderModel::find($request_ajust_purchase_id);
        $request_ajust_purchase->status = 2;
        $request_ajust_purchase->financial_user_id = Auth::user()->id;
        $request_ajust_purchase->financial_reject_reason = $reject_reason;
        $request_ajust_purchase->save();

        $purchase = PurchaseOrderModel::find($request_ajust_purchase->purchase_id);
        $purchase->ajust_status = 4;
        $purchase->status = 5;
        $purchase->save();

        $this->decrease_qty_po_boq($request_ajust_purchase->purchase_id);

        \LogActivity::addToLog('financial reject ajust P.O successfully !');
        return redirect()->route('construction-purchase.financial_approval_ajust_purchase_index')->with("success","successfully");
    }
    public function ceo_reject_ajust_po()
    {
        $request_ajust_purchase_id = $_GET['request_ajust_purchase_id'];
        $reject_reason = $_GET['reject_reason'];
        $request_ajust_purchase = AjustPurchaseOrderModel::find($request_ajust_purchase_id);
        $request_ajust_purchase->status = 2;
        $request_ajust_purchase->ceo_user_id = Auth::user()->id;
        $request_ajust_purchase->ceo_reject_reason = $reject_reason;
        $request_ajust_purchase->save();

        $purchase = PurchaseOrderModel::find($request_ajust_purchase->purchase_id);
        $purchase->ajust_status = 4;
        $purchase->status = 5;
        $purchase->save();

        $this->decrease_qty_po_boq($request_ajust_purchase->purchase_id);

        session()->put('success','successfully');
        \LogActivity::addToLog('ceo reject ajust P.O successfully !');
        return redirect()->route('construction-purchase.ceo_approval_ajust_purchase_index');
    }
    public function verified_product_list(Request $request)
    {
        if($request->ajax()){
            $data =PurchaseOrderModel::join("nso007_project_stock","nso007_project_stock.id","nso007_construction_purchase.project_id")
                ->select("nso007_construction_purchase.*","nso007_project_stock.project_name")
                ->where('nso007_construction_purchase.status',6)
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->orderBy('nso007_construction_purchase.date','DESC')
                ->get();
                return  DataTables::of($data)
                    ->setRowId('id')
                    ->addIndexColumn()
                    ->editColumn('date',function($data){
                        return date('d-m-Y',strtotime($data->date));
                    })
                    ->editColumn('supplier_id',function($data){
                        return SuppliersModel::find($data->supplier_id)->name;
                    })
                    ->editColumn('status',function($data){
                        if($data->add_stock_status==1) return "Added To Stock";
                        else return "Verified";
                    })
                    ->addColumn('action', function($data) {
                        $button = "";
                        if(Gate::check('Construction-Assign-Product-To-Warehouse-Show'))
                            $button .= " <a title='Show Purchase Detail' href='".route('construction-make-purchase-order.show',Crypt::encrypt($data->id))."' class='btn btn-sm btn-info' ><i class='fa fa-eye'></i></a> ";
                            $button .= " <a title='Show adding to warehouse detail' style='margin-left:10px !important;' href='javascript:void(0)' class='btn btn-sm btn-primary' onclick='show_add_warhouse(".$data->id.")'><i class='fa fa-eye'></i></a> ";
                        if($data->add_stock_status==1){
                        }else{
                            if(Gate::check('Construction-Assign-Product-To-Warehouse-Add-Product'))
                                $button .= " <a title='Add Product To Stock' href='javascript:void(0)' onclick='display_form_add_product(".$data->id.")' class='btn btn-sm btn-success' style='margin-left:10px;'><i class='fa fa-institution'></i></a> ";
                        }
                        
                        return $button;
                    })
                    ->rawColumns(['status'=>'status','action'=>'action'])
                    ->make(true);
            }
            $warehouses = DB::table('nso007_warehouse_construction')
                            ->join('nso007_project_stock','nso007_project_stock.id','nso007_warehouse_construction.project_id')
                            ->select('nso007_warehouse_construction.*')
                            ->when(empty(\Branch::branch_key()), function ($query) {
                                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                            })
                            ->get();
            return view("administrator.construction.purchase.purchase-order.list_verified_product",compact('warehouses'));
    }
    public function add_product_stock_store(Request $request)
    {
        // dd($request->all());
        // $data_purchase = PurchaseOrderDetailModel::where('purchase_id',$request->purchase_id)->get();
        foreach($request->product_id as $k=>$val){
            $qty = !empty($request->qty_add_to_warehouse[$k])?$request->qty_add_to_warehouse[$k]:0;
            $check_warehouse = DB::table('nso007_construction_product_origin_stock')->where('warehouse_id',$request->warehouse_id)->first();
            PurchaseOrderDetailModel::where('id',$request->purchase_detail_id[$k])->update(['date_add_to_stock'=>date('Y-m-d',strtotime($request->date)),'qty_added_to_stock'=>DB::raw('qty_added_to_stock + '.$qty)]);
            if(empty($check_warehouse)){
                $data = array(
                    'warehouse_id'=>$request->warehouse_id,
                    'product_id'=>$val,
                    'qty'=>$request->qty_add_to_warehouse[$k],
                    'user_id'=>Auth::user()->id
                );
                DB::table('nso007_construction_product_origin_stock')->insert($data);
            }else{
                $check_product = DB::table('nso007_construction_product_origin_stock')->where('product_id',$val)->where('warehouse_id',$request->warehouse_id)->first();

                if(empty($check_product)){
                    $data = array(
                        'warehouse_id'=>$request->warehouse_id,
                        'product_id'=>$val,
                        'qty'=>$request->qty_add_to_warehouse[$k],
                        'user_id'=>Auth::user()->id,
                    );
                    DB::table('nso007_construction_product_origin_stock')->insert($data);
                }else{
                    DB::table('nso007_construction_product_origin_stock')->where('product_id',$val)->where('warehouse_id',$request->warehouse_id)->increment('qty',$request->qty_add_to_warehouse[$k]);
                }
            }
        }
        $search_status = DB::table('nso007_construction_purchase_details')->where("purchase_id",$request->purchase_id)->get();
        $status_add = 0;
        foreach($search_status as $valu){
            if($valu->qty_added_to_stock<$valu->qty){
                $status_add = 0;
                break;
            }else{
                $status_add = 1;
            }
        }
        PurchaseOrderModel::where('id',$request->purchase_id)->update(['add_stock_status'=>$status_add,'status_editable'=>1]);
        \LogActivity::addToLog('Product add to stock successfully !');
        return \back()->with('success','Add to warehouse successfully !');
    }
    public function get_product_purchase_no_boq($purchase_id)
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
        })
        ->get();
        $category_product_no_boq = DB::table("nso007_purchase_order_setting")
            ->join("nso007_product_categories_construction","nso007_product_categories_construction.id","nso007_purchase_order_setting.category_id")
            ->select("nso007_product_categories_construction.*")
            ->where("type","order_do_not_need_boq")
            ->get();
        $data = array("projects"=>$projects,'product'=>$category_product_no_boq);
        return json_encode($data);
    }
    public function get_product_by_category_to_purchase($category_id)
    {
        $products = DB::table("nso007_construction_product")
                ->join("nso007_unit_construction","nso007_unit_construction.id","nso007_construction_product.unit_id")
                ->select("nso007_construction_product.*","nso007_unit_construction.name as unit_name","nso007_unit_construction.id as unit_id")
                ->where("category_id",$category_id)
                ->get();
        $subcategories = BrandModel::join("nso007_purchase_order_setting_sub_category","nso007_purchase_order_setting_sub_category.sub_category_id","nso007_product_brand_construction.id")->where("nso007_purchase_order_setting_sub_category.category_id",$category_id)->get();
        $data = array(
            'products'=>$products,
            'subcategories'=>$subcategories
        );
        return json_encode($data);
    }
    public function get_product_by_subcategory_to_purchase($subcategory_id)
    {
        $data = DB::table("nso007_construction_product")
                ->join("nso007_unit_construction","nso007_unit_construction.id","nso007_construction_product.unit_id")
                ->select("nso007_construction_product.*","nso007_unit_construction.name as unit_name","nso007_unit_construction.id as unit_id")
                ->where("brand_id",$subcategory_id)
                ->get();
        return json_encode($data);
    }
    public function decrease_qty_po_boq($po_id){
        $id = $po_id;
        $purchase_old = PurchaseOrderModel::find($id);
        if(empty($purchase_old)) return ;
        foreach(PurchaseOrderDetailModel::where('purchase_id',$id)->get() as $value){
            $boq_request_po_old = DB::table("nso007_boq_request_po")
                    ->where("boq_id",$purchase_old->boq_id)
                    ->where('product_id',$value->product_id)
                    ->where('boq_detail_title',$purchase_old->task_group_work_title)
                    ->first();
            if(!empty($boq_request_po_old))
                DB::table("nso007_boq_request_po")->where('id',$boq_request_po_old->id)->update(['total_qty_request'=>DB::raw("total_qty_request - ".$value->qty)]);
        }
    }
    public function check_qty_limit(Request $request)
    {
        $project_id = $request->project_id;
        $product_id = $request->product_id;
        $condition = DB::table("nso007_purchase_order_setting_limit_product")
                     ->select("qty_limit","limit_by")
                     ->where("product_id",$product_id)
                     ->first();
        $respon = '';
        if(empty($condition)){
            $respon = "Unlimited";
        }
        else{
            
            if($condition->limit_by==1){
                $first_date = date("Y-m-d");
            }else if($condition->limit_by==2){
                $first_date = date('Y-m-d', strtotime('this week'));
            }else if($condition->limit_by==3){
                $first_date = date('Y-m-d', strtotime('first day of this month'));
            }else{

            }
            $qty = DB::table('nso007_construction_purchase_details')
                    ->join("nso007_construction_purchase","nso007_construction_purchase.id","nso007_construction_purchase_details.purchase_id")
                    ->select(DB::raw('SUM(qty) as sum_qty'))
                    ->where("nso007_construction_purchase_details.product_id",$product_id)
                    ->where("nso007_construction_purchase.project_id",$project_id)
                    ->whereBetween("date",[$first_date,date("Y-m-d")])
                    ->first();
            if(!empty($qty)) return json_encode($condition->qty_limit - $qty->sum_qty);
            else return json_encode($condition->qty_limit);
        }
        return json_encode($respon);
    }
    public function get_qty_add_to_warehouse($purchase_id){
        $detail = PurchaseOrderDetailModel::where('purchase_id',$purchase_id)->get();
        $tr = '';
        foreach($detail as $key=>$value){
            $tr .= ' <tr>
                        <td width="5%">
                            '.($key+1).'
                        </td>
                        <td width="30%">
                            '.(DB::table('nso007_construction_product')->where('id',$value->product_id)->first()->name).'
                            <input type="hidden" name = "purchase_detail_id[]" class="form-control" value="'.$value->id.'">
                            <input type="hidden" name = "product_id[]" class="form-control" value="'.$value->product_id.'">
                        </td>
                        <td width="15%">
                            '.(DB::table('nso007_unit_construction')->where("id",$value->unit_id)->first()->name).'
                        </td>
                        <td width="15%">
                            '.($value->qty - $value->qty_added_to_stock).'
                        </td>
                        <td width="15%">
                            <input type="text" name = "qty_add_to_warehouse[]" class="form-control number-only" required value="0">
                        </td>
                    </tr>';
        }
        
        return $tr;
    }

    public function get_qty_add_to_warehouse_show($purchase_id){
        $detail = PurchaseOrderDetailModel::where('purchase_id',$purchase_id)->get();
        $tr = '';
        foreach($detail as $key=>$value){
            $tr .= ' <tr>
                        <td width="5%">
                            '.($key+1).'
                        </td>
                        <td width="30%">
                            '.(DB::table('nso007_construction_product')->where('id',$value->product_id)->first()->name).'
                            <input type="hidden" name = "purchase_detail_id[]" class="form-control" value="'.$value->id.'">
                            <input type="hidden" name = "product_id[]" class="form-control" value="'.$value->product_id.'">
                        </td>
                        <td width="15%">
                            '.(DB::table('nso007_unit_construction')->where("id",$value->unit_id)->first()->name).'
                        </td>
                        <td width="15%">
                            '.($value->qty - $value->qty_added_to_stock).'
                        </td>
                        <td width="15%">
                           '.$value->qty_added_to_stock.'
                        </td>
                    </tr>';
        }
        
        return $tr;
    }

}
