<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use App\Invoice;
use App\User;
use App\Product;
use App\Payment;

class ReturnController extends Controller
{

     public function __construct()
    {
       //$this->middleware('auth');
    }



   function get_prod_byref(Request $request){


       $id = $request->input('refid');

        $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $depo = $user_data[0]->user_depoid;

        $us_temp = app('db')->select("SELECT COUNT(`po_itm_id`) as tempcount FROM `temp_qto_items` WHERE `po_user`='$id'");
        $tempitem = $us_temp[0]->tempcount;


                $resultitem = app('db')->select("SELECT `mes_item`.`item_id` as dpstock_id,`mes_item`.`item_id` as dpstock_itemid,`mes_item`.*,`item`.*
                    FROM `mes_item` LEFT JOIN `item` ON `item`.`itm_id` = `mes_item`.`item_name` where `mes_item`.`item_status` =1");

                foreach ($resultitem as $row) {

                    //$detail = app('db')->select("SELECT `product`, (`buying_price`) as buying, (`selling_price`) as selling, (`retail_price`) as retail FROM `batch` WHERE `avl_qty` >0 AND `product` = '$row->dpstock_itemid'");
                    $detail = app('db')->select("SELECT `product`, (`buying_price`) as buying, (`selling_price`) as selling, (`retail_price`) as retail FROM `batch` WHERE `product` = '$row->dpstock_itemid'");

                      if(count($detail)>0){
                            if($detail[0]->selling<1){$ssel = "0.00";}else{ $ssel = $detail[0]->selling;}
                        if($detail[0]->retail<1){$rrtl = "0.00";}else{$rrtl = $detail[0]->retail;}
                         if($row->item_freeitm<1){$itmf = "0";}else{$itmf = $row->item_freeitm;}
                        if($row->item_freqty<1){$frq = "0";}else{$frq = $row->item_freqty;}

                        if($rrtl=="0.00"){
                            $detail_new = app('db')->select("SELECT `product`, `buying_price`, `selling_price`,`retail_price` FROM `batch` WHERE `product` = '$row->dpstock_itemid' ORDER BY `exp_date` DESC");
                            if(count($detail_new)>0){ $rrtl = $detail_new[0]->retail_price;}

                         }

                         if($ssel=="0.00"){
                            $detail_new = app('db')->select("SELECT `product`, `buying_price`, `selling_price`,`retail_price` FROM `batch` WHERE `product` = '$row->dpstock_itemid' ORDER BY `exp_date` DESC");
                            if(count($detail_new)>0){$ssel = $detail_new[0]->selling_price; }
                         }

                         $resultitemm[] = array('item_id'=>($row->dpstock_itemid),'dpstock_dpoid'=>("0"),'dpstock_qty'=>("0"),
                        'itm_name'=>($row->itm_name),'item_selling_price'=>($ssel),'item_retail_price'=>($rrtl),'item_freeitm'=>($itmf),'item_freqty'=>($frq),'temp'=>($tempitem));

                      }


                }





        $data = response()->json($resultitemm);
            return $data;

    }


   function ret_item(Request $request){
        $this->validate($request, [
            'cust_id' => 'required',
            'inv_id' => 'required',
            'batch_id' => 'required',
            'qty' => 'required',
            'free_qty' => 'required'

        ]);

        $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;
        $user_id = $user_data[0]->user_id;

        $cust_id = $request->input('cust_id');
        $batch_id = $request->input('batch_id');
        $qty = $request->input('qty');
        $free_qty = $request->input('free_qty');
        $inv = $request->input('inv_id');
        $date = date('Y-m-d');

        if($inv!=""){
            $add_invntry = app('db')->insert("INSERT INTO `temp_invreturn_item`(`user_id`, `cust_id`, `batch_id`, `qty`, `free_qty`, `inv_num`, `ret_value`,`date`)
        VALUES ('$user_id','$cust_id','$batch_id','$qty','$free_qty','$inv','1800','$date')");

      //  $insrtn_main = app('db')->insert("INSERT INTO `mes_spec` (`spec_code`, `spec_spec`, `spec_cuser`, `spec_euser`)
                           // VALUES ('$batch_id', '$qty','$inv','inretitem1') ");

        $data['result'] = "Added";
        $results = app('db')->select("SELECT `ret_id` FROM `temp_invreturn_item` where `cust_id` = '$cust_id' ORDER BY `ret_id` DESC LIMIT 1");
        $last = $results[0]->ret_id;
        }else{
            $data['result'] ="Incorrect Quantity";
            $last = "";
        }
        $temp_data = app('db')->select("SELECT `batch` FROM `batch` WHERE `batch_id`='$batch_id'");
        $batch_name = $temp_data[0]->batch;

        $data['data'] = array("ret_id"=>$last,"inv_num"=>$inv,"batch_id"=>$batch_name,"batch"=>$batch_id,"qty"=>$qty,"free_qty"=>$free_qty);


         $dataa = response()->json($data);
            return $dataa;

   }

   function all_ret_item(Request $request){
        $this->validate($request, [
            'cust_id' => 'required'

        ]);

        $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;
        $user_id = $user_data[0]->user_id;

        $cust_id = $request->input('cust_id');
        $temp_data = app('db')->select("SELECT `temp_invreturn_item`.*,`batch`.`batch`,`item`.`itm_name` as item FROM `temp_invreturn_item`
        JOIN `batch` ON `temp_invreturn_item`.`batch_id`=`batch`.`batch_id`
        JOIN `mes_item` ON `mes_item`.`item_id`=`batch`.`product` JOIN `item` ON `item`.`itm_id`=`mes_item`.`item_name`
        WHERE `cust_id`='772' AND `user_id`='9'");

        if(count($temp_data)>0){
            $data['data'] = $temp_data;
        }else{
            $data['data'] ="No Data";
        }



         $dataa = response()->json($data);
            return $dataa;

   }

   function new_return(Request $request){
          $this->validate($request, [
            'batch_id' => 'required',
            'values' => 'required',
            'inv_id' => 'required',
            'ret_amnt' => 'required',
            'ret_reason' => 'required',
            'free_return' => 'required'

        ]);

         if ($request->isJson()) {
                $data = "sdf";
            } else {
                $data = $request->all();
            }

        $dtt = $request->getContent();
        $dtt = json_decode($dtt);
       // print_r($dtt);

        //'free_return' => 'required',
        $str_arr = $request->input('batch_id');
        $str_arr1 = $request->input('values');
        $str_arr2 = $request->input('inv_id');
        $str_arr3 = $request->input('ret_amnt');
        $str_arr4 = $request->input('ret_reason');

        $id = $dtt->batch_id;
        $qt = $dtt->values;
        $inv_id = $dtt->inv_id;
        $ret_amnt = $dtt->ret_amnt;
        $ret_reason = $dtt->ret_reason;
        $free_return = $dtt->free_return;


     //   $free_return =  $dtt->batch_id;
        if($free_return!=""){
     //       $str_arr5 = $request->input('free_return');
           $frr = $free_return = $dtt->free_return; ;
      }else{
            $frr = 0;
        }


        $today = date('Y-m-d');
        $tot = $insert_id= $tott= $all_tott=$ret_amnt = 0;


       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;
        $user_id = $user_data[0]->user_id;

      if(count($qt)>0){



       $insrtn_main = app('db')->insert("INSERT INTO `inv_return_main` (`retm_invnum`, `retm_totqty`, `retm_amount`, `retm_reason`, `retm_depo`, `retm_cuser`, `retm_cdate`, `retm_stetus`)
                            VALUES ('0', '0','0','0','$u_depo','$user_id','$today','1') ");
                $rtn_main = app('db')->select("SELECT `retm_id` FROM `inv_return_main` WHERE `retm_cuser` ='$user_id' ORDER BY `retm_id` DESC LIMIT 1");
                $rtn_main_id = $rtn_main[0]->retm_id;



        for($i=0; $i<count($id); $i++){

           if($qt[$i]!=""){


               $pro_det = app('db')->select("SELECT `po_itm_unitprice`,`po_itm_item_id`,`po_itm_batch` FROM `qto_items` WHERE `po_itm_id` ='$id[$i]' AND `po_itm_po_id` ='$inv_id[$i]'");


                        $un_price = $pro_det[0]->po_itm_unitprice;
                            $tot_price = $un_price * $qt[$i];
                        $item_id = $pro_det[0]->po_itm_item_id;
                        $item_batch = $pro_det[0]->po_itm_batch;

                    $inv_det = app('db')->select("SELECT `po_discount` FROM `mes_qto` WHERE `po_id` ='$inv_id[$i]'");
                        if($inv_det[0]->po_discount>0){

                            $disval = ($tot_price/100)*$inv_det[0]->po_discount;
                            $ret_amntt= $tot_price-$disval;

                        } else{$ret_amntt = $tot_price;

                        }



                     $tott = $qt[$i] + $frr[$i];
                          $add_rtn = app('db')->insert("INSERT INTO `inv_return` (`ret_invnum`, `ret_totqty`, `ret_amount`, `ret_reason`, `ret_depo`, `ret_cuser`, `ret_cdate`,`ret_main_id`)
                            VALUES ('$inv_id[$i]', '$tott','$ret_amntt','$ret_reason[$i]','$u_depo','$user_id','$today','$rtn_main_id') ");

                            $det = app('db')->select("SELECT `ret_id` FROM `inv_return` WHERE `ret_cuser` ='$user_id' ORDER BY `ret_id` DESC LIMIT 1");
                            $insert_id = $det[0]->ret_id;

                    app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `cal`, `invt_cuser`, `invt_cdate`)
                    VALUES ('INVRTN','$insert_id','$item_id','$qt[$i]','plus','$user_id','$today') ");


                      app('db')->insert("INSERT INTO `return_items` (`ret_itm_item_id`,`ret_item_batch`, `depo_id`, `ret_note_id`, `ret_inv_id`, `ret_itm_qty`, `free_item`,
                     `ret_itm_unitprice`,`ret_aprvl`, `ret_user`)
                    VALUES ('$item_id','$item_batch', '$u_depo', '$insert_id', '$inv_id[$i]', '$qt[$i]', '$frr[$i]','$un_price','0', '$user_id') ");

                    $tot = $tot+$qt[$i];
                    $ret_amnt = $ret_amnt + $ret_amntt;

                                    $gtcustom = app('db')->select("SELECT `po_sup_id` FROM `mes_qto` WHERE `po_id` ='$inv_id[0]'");
                                    $cust_id = $gtcustom[0]->po_sup_id;

                                    $credit_cal = new Payment();
                                    $chk = $credit_cal->cal_credit_limit($cust_id);
                    $all_tott = $all_tott +$tott;

                }

        }

        $updt_rtn = app('db')->update("UPDATE `inv_return_main` SET `retm_totqty`='$all_tott',`retm_amount`='$ret_amnt' WHERE `retm_id`='$rtn_main_id'");

  $send = "New Invoice Return Created";



      }else{
          $send = "Cannot Save. Please Recheck";
      }

        $dataa = response()->json($send);
           return $dataa;


   }

   //credit note
   function sendCreditRequest(Request $request){
          $this->validate($request, [
            'batch_id' => 'required',
            'values' => 'required',
            'ret_amnt' => 'required',
            'ret_reason' => 'required',
            'free_return' => 'required',
            'cust_id' => 'required'

        ]);

         if ($request->isJson()) {
                $data = "sdf";
            } else {
                $data = $request->all();
            }

        $dtt = $request->getContent();
        $dtt = json_decode($dtt);
       // print_r($dtt);

        //'free_return' => 'required',
        //$selcust_id = $request->input('cust_id');

        $str_arr = $request->input('batch_id');
        $str_arr1 = $request->input('values');
        $str_arr2 = $request->input('inv_id');
        $str_arr3 = $request->input('ret_amnt');
        $str_arr4 = $request->input('ret_reason');

        $id = $dtt->batch_id;
        $qt = $dtt->values;
        $inv_id = $dtt->inv_id;
        $ret_amnt = $dtt->ret_amnt;
        $ret_reason = $dtt->ret_reason;
        $free_return = $dtt->free_return;
        $selcust_id = $dtt->cust_id;


     //   $free_return =  $dtt->batch_id;
        if($free_return!=""){
     //       $str_arr5 = $request->input('free_return');
           $frr = $free_return = $dtt->free_return; ;
      }else{
            $frr = 0;
        }


        $today = date('Y-m-d');
        $tot = $insert_id= $tott= $ret_amnt1 = 0;


       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;
        $user_id = $user_data[0]->user_id;

      if(count($qt)>0){



       $insrtn_main = app('db')->insert("INSERT INTO `inv_return_credit_main` (`retm_invnum`, `retm_totqty`, `retm_amount`, `retm_reason`,`cust_id`, `retm_depo`, `retm_cuser`, `retm_cdate`, `retm_stetus`)
                            VALUES ('0', '0','0','0','$selcust_id','$u_depo','$user_id','$today','1') ");
                $rtn_main = app('db')->select("SELECT `retm_id` FROM `inv_return_credit_main` WHERE `retm_cuser` ='$user_id' ORDER BY `retm_id` DESC LIMIT 1");
                $rtn_main_id = $rtn_main[0]->retm_id;



        for($i=0; $i<count($id); $i++){

           if($qt[$i]!=""){

                   $item_dt = new Invoice();
            $data = $item_dt->get_sin_item_model($id[$i]);

             $prc = app('db')->select("SELECT `retail_price`,`batch_id` FROM `batch` WHERE `product` = '$id[$i]'");

                         //$un_price = $prc[0]->retail_price;
                         $un_price = $ret_amnt[$i];
                         $prd_btch = $prc[0]->batch_id;
                            $tot_price = $un_price * $qt[$i];
                        $item_id = $id[$i];

                  $ret_amntt = $tot_price;


                     $tott = $qt[$i];

                            $insert_id = $rtn_main_id;

                    app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `cal`, `invt_cuser`, `invt_cdate`)
                    VALUES ('CREDIT','$insert_id','$item_id','$qt[$i]','plus','$user_id','$today') ");


                      app('db')->insert("INSERT INTO `credit_return_items` (`ret_itm_item_id`,`ret_item_batch`, `depo_id`, `ret_note_id`, `ret_inv_id`, `ret_itm_qty`, `free_item`,
                     `ret_itm_unitprice`,`ret_aprvl`, `ret_user`)
                    VALUES ('$item_id','$prd_btch', '$u_depo', '$insert_id', '0', '$qt[$i]', '$frr[$i]','$un_price','0', '$user_id') ");

                    $tot = $tot+$qt[$i];
                    $ret_amnt1 = $ret_amnt1 + $ret_amntt;


                                    $cust_id = $selcust_id;

                                    $credit_cal = new Payment();
                                    $chk = $credit_cal->cal_credit_limit($cust_id[0]);
                   // $tott =

                }

        }

        $updt_rtn = app('db')->update("UPDATE `inv_return_credit_main` SET `retm_totqty`='$tott',`retm_amount`='$ret_amnt1' WHERE `retm_id`='$rtn_main_id'");

  $send = "New Credit Note Created";



      }else{
          $send = "Cannot Save. Please Recheck";
      }

        $dataa = response()->json($send);
           return $dataa;


   }

   function new_return_test(Request $request){
          $this->validate($request, [
            'batch_id' => 'required',
            'values' => 'required',
            'inv_id' => 'required',
            'ret_amnt' => 'required',
            'ret_reason' => 'required',
            'free_return' => 'required'

        ]);

         if ($request->isJson()) {
                $data = "sdf";
            } else {
                $data = $request->all();
            }

        $dtt = $request->getContent();
        $dtt = json_decode($dtt);
       // print_r($dtt);

        //'free_return' => 'required',
        $str_arr = $request->input('batch_id');
        $str_arr1 = $request->input('values');
        $str_arr2 = $request->input('inv_id');
        $str_arr3 = $request->input('ret_amnt');
        $str_arr4 = $request->input('ret_reason');

        $id = $dtt->batch_id;
        $qt = $dtt->values;
        $inv_id = $dtt->inv_id;
        $ret_amnt = $dtt->ret_amnt;
        $ret_reason = $dtt->ret_reason;
        $free_return = $dtt->free_return;



     //   $free_return =  $dtt->batch_id;
        if($free_return!=""){
     //       $str_arr5 = $request->input('free_return');
           $frr = $free_return = $dtt->free_return; ;
      }else{
            $frr = 0;
        }


        $today = date('Y-m-d');
        $tot = $insert_id= $tott= $ret_amnt = 0;


       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;
        $user_id = $user_data[0]->user_id;

           $send = "Cannot SAve. Please Recheck";
        $dataa = response()->json($send);
           return $dataa;


   }

   function remove_ret_item(Request $request){
       $this->validate($request, [
            'ret_id' => 'required'

        ]);

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

        $updt = app('db')->delete("DELETE FROM `temp_invreturn_item` WHERE `ret_id` = '$ret_id'");
        $data = "done";
        $dataa = response()->json($data);
            return $dataa;

   }


   function get_invoice(Request $request){
       $this->validate($request, [
            'cust_id' => 'required'

        ]);
       $cust = $request->input('cust_id');
       // $inv = app('db')->select("SELECT * FROM `mes_qto` WHERE `po_status`=1 AND `po_sup_id`='$cust' AND `po_auth`='1'");

       $headers = $request->header('Authorization');
                        $dt = new User;
                        $user_data = $dt->getBearerToken($headers);
                        $user_name = $user_data[0]->user_name;
                        $user_id = $user_data[0]->user_id;

        $inv = app('db')->select("SELECT * FROM `mes_qto`
JOIN `inv_return` ON `inv_return`.`ret_invnum` = `mes_qto`.`po_id`
WHERE `po_status`=1 AND `po_sup_id`='$cust' AND `po_auth`='1'
AND `inv_return`.`ret_proceed`!=0 AND `mes_qto`.`po_ref_id`='$user_id' ");

        $invdt="No invoices";

        for($i=0; $i<count($inv); $i++){
            $qtoid = $inv[$i]->po_id;
           $chkk = app('db')->select("SELECT * FROM `inv_return` WHERE `ret_invnum`='$qtoid' AND `ret_stetus`=1 AND `ret_proceed`='0'");
           if(count($chkk)<1){
               $paym = app('db')->select("SELECT * FROM `mes_payment` WHERE `pay_stetus`=1 AND `pay_invid`='$qtoid' AND `pay_authrize`='0'");
                if(count($paym)<1){
                    $invdt[] = array($inv[$i]->po_code,$inv[$i]->po_id);
                }else{

                }

           }

        }

        if(count($inv)<1){
          $invdt="No invoices";
        }
        if(empty($invdt)){
          $invdt="No invoices";
        }

        $data['invoice'] = $invdt;

        $reason = app('db')->select("SELECT `ret_reason_id`,`ret_reason` FROM `mes_ret_reason` WHERE `ret_reason_status` =1");

        $data['ret_reason'] = $reason;

        $dataa = response()->json($data);
            return $dataa;
   }


   function prod_invoices(Request $request){
       $this->validate($request, [
            'cust_id' => 'required',
            'prod_id' => 'required'

        ]);

       $cust = $request->input('cust_id');
       $pro_id = $request->input('prod_id');

        $inv = app('db')->select("SELECT * FROM `mes_qto` WHERE `po_status`=1 AND `po_sup_id`='$cust' AND `po_auth`='1'");
        //$inv = app('db')->select("SELECT * FROM `mes_qto` WHERE `po_status`=1 AND `po_sup_id`='$cust'");

        $invdt =array();

        for($i=0; $i<count($inv); $i++){
            $qtoid = $inv[$i]->po_id;


            $poid = $inv[$i]->po_id;
                    $chk_btch = app('db')->select("SELECT * FROM `qto_items` WHERE `po_itm_po_id`='$poid' AND `po_itm_item_id`='$pro_id'");
                    if(count($chk_btch)>0){
                        //$invdt[] = array($inv[$i]->po_code,$inv[$i]->po_id,$chk_btch[0]->po_itm_qty);
                        $invdt[] = array('inv_code'=>$inv[$i]->po_code,'inv'=>$inv[$i]->po_id,'qty'=>$chk_btch[0]->po_itm_qty,'free'=>$chk_btch[0]->free_item);
                    }


         //  $chkk = app('db')->select("SELECT * FROM `inv_return` WHERE `ret_invnum`='$qtoid' AND `ret_stetus`=1 AND `ret_proceed`='0'");
          // if(count($chkk)<1){
          //     $paym = app('db')->select("SELECT * FROM `mes_payment` WHERE `pay_stetus`=1 AND `pay_invid`='$qtoid' AND `pay_authrize`='0'");
           //     if(count($paym)<1){
          //          $poid = $inv[$i]->po_id;
          //          $chk_btch = app('db')->select("SELECT * FROM `qto_items` WHERE `po_itm_po_id`='$poid' AND `po_itm_item_id`='$pro_id'");
          ////          if(count($chk_btch)>0){
           //             //$invdt[] = array($inv[$i]->po_code,$inv[$i]->po_id,$chk_btch[0]->po_itm_qty);
           //             $invdt[] = array('inv_code'=>$inv[$i]->po_code,'inv'=>$inv[$i]->po_id,'qty'=>$chk_btch[0]->po_itm_qty,'free'=>$chk_btch[0]->free_item);
        //            }

      //          }else{
      //
      //              }

      //     }

           $poid = $inv[$i]->po_id;


        }

        if(count($inv)<1){
          $invdt="No invoices";
        }
        if(empty($invdt)){
          $invdt="No invoices";
        }

        $data['invoice'] = $invdt;

        $reason = app('db')->select("SELECT `ret_reason_id`,`ret_reason` FROM `mes_ret_reason` WHERE `ret_reason_status` =1");

        $data['ret_reason'] = $reason;

        $dataa = response()->json($data);
            return $dataa;
   }

   function get_product(Request $request){
       $this->validate($request, [
            'cust_id' => 'required'

        ]);
       $cust = $request->input('cust_id');
        $inv = app('db')->select("SELECT `qto_items`.`po_itm_item_id` as item FROM `mes_qto` JOIN `qto_items` ON `mes_qto`.`po_id` = `qto_items`.`po_itm_po_id`
        WHERE `po_status`=1 AND `po_sup_id`='$cust' AND `po_auth`='1' GROUP BY `qto_items`.`po_itm_item_id`");


        for($i=0; $i<count($inv); $i++){
            $item_id = $inv[$i]->item;
           $item_dt = new Invoice();
            $data = $item_dt->get_sin_item_model($item_id);
            $invdt[] = array($inv[$i]->item,$data[0]->itm_name);
        }

        if(count($inv)<1){
          $invdt="No Products";
        }
        if(empty($invdt)){
          $invdt="No invoices";
        }

        $data['invoice'] = $invdt;

        $reason = app('db')->select("SELECT `ret_reason_id`,`ret_reason` FROM `mes_ret_reason` WHERE `ret_reason_status` =1");

        $data['ret_reason'] = $reason;

        $dataa = response()->json($data);
            return $dataa;
   }

   function get_batch(Request $request){
       $this->validate($request, [
            'cust_id' => 'required'

        ]);
       $cust = $request->input('cust_id');
       //$prod_id = $request->input('prod_id');

        $inv = app('db')->select("SELECT `batch`.`batch` as batch, `batch`.`batch_id` as batch_id,`item`.`itm_name` as item
        FROM `batch` JOIN `mes_item` ON `mes_item`.`item_id`=`batch`.`product` JOIN `item` ON `item`.`itm_id`=`mes_item`.`item_name`");

        for($i=0; $i<count($inv); $i++){
            $batch = $inv[$i]->batch;
            $batch_id = $inv[$i]->batch_id;
            $item = $inv[$i]->item;
            $invdt[] = array($batch_id,$batch,$item);
        }

        if(count($inv)<1){
          $invdt="No Products";
        }
        if(empty($invdt)){
          $invdt="No invoices";
        }

        $data['batch'] = $invdt;

        $dataa = response()->json($data);
            return $dataa;
   }

   function all_return(Request $request){

       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $user_id = $user_data[0]->user_id;


           $crd = app('db')->select("SELECT * FROM `inv_return_credit_main`  where `inv_return_credit_main`.`retm_cuser`='$user_id' ORDER BY `retm_id` DESC");
        for($i=0; $i<count($crd); $i++){
              $invid = $crd[$i]->retm_code.$crd[$i]->retm_id;
              $ret_main = $crd[$i]->retm_id;
              $ret_cust = $crd[$i]->cust_id;


            $qt = app('db')->select("SELECT * FROM `mes_customer` WHERE `cust_id` = '$ret_cust'");


            if($crd[$i]->retm_proceed<1){
                        $sts = "Authorized";
                    }else{

                        $sts = "Pending Authorize";
                    }
                    $sts = "Pending Authorize";

                   $link = "https://neha.webip.lk/pdf_invcrd/".$crd[$i]->retm_id;
            $invdt[] = array('return_id'=>$invid,'invoice'=>$ret_main,'Customer'=>$qt[0]->cust_company,
            'reason'=>'','retirn_qty'=>$crd[$i]->retm_totqty,'return_date'=>$crd[$i]->retm_cdate,$sts,'return_amnt'=>$crd[$i]->retm_amount,'link'=>$link,'auth'=>$sts);
        }

       $inv = app('db')->select("SELECT * FROM `inv_return_main`  where `inv_return_main`.`retm_cuser`='$user_id' ORDER BY `retm_id` DESC");

        for($i=0; $i<count($inv); $i++){
              $invid = $inv[$i]->retm_code.$inv[$i]->retm_id;
              $ret_main = $inv[$i]->retm_id;
            $rtn_note = app('db')->select("SELECT * FROM `inv_return`  where `ret_main_id`='$ret_main' ORDER BY `ret_id` DESC");
            $inv_num = $rtn_note[0]->ret_invnum;
            $qt = app('db')->select("SELECT * FROM `mes_qto` LEFT JOIN `mes_customer` ON `mes_customer`.`cust_id` = `mes_qto`.`po_sup_id` WHERE `po_id` = '$inv_num'");

            $inv_count = count($rtn_note);

            $rtn_auth = app('db')->select("SELECT * FROM `inv_return`  where `ret_main_id`='$ret_main' AND `ret_proceed`=0");


            if(count($rtn_auth)<1){
                        $sts = "Authorized";
                    }else{

                        $sts = "Pending Authorize";
                    }

                   $link = "https://neha.webip.lk/pdf_invrtn/".$inv[$i]->retm_id;
            $invdt[] = array('return_id'=>$invid,'invoice'=>$ret_main,'Customer'=>$qt[0]->cust_company,
            'reason'=>'','retirn_qty'=>$inv[$i]->retm_totqty,'return_date'=>$inv[$i]->retm_cdate,'auth'=>$sts,'return_amnt'=>$inv[$i]->retm_amount,'link'=>$link);
        }

       if(empty($invdt)){
           $invdt = array();
       }

        $dataa = response()->json($invdt);
            return $dataa;

   }

   function all_return_old(Request $request){

       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $user_id = $user_data[0]->user_id;

       $inv = app('db')->select("SELECT * FROM `inv_return` LEFT JOIN `mes_qto` ON `mes_qto`.`po_id` = `inv_return`.`ret_invnum`
       LEFT JOIN `mes_ret_reason` ON `mes_ret_reason`.`ret_reason_id` = `inv_return`.`ret_reason`  where `mes_qto`.`po_ref_id`='$user_id' ORDER BY `ret_id` DESC");

        for($i=0; $i<count($inv); $i++){
            $poid = $inv[$i]->po_id;

            $qt = app('db')->select("SELECT * FROM `mes_qto` LEFT JOIN `mes_customer` ON `mes_customer`.`cust_id` = `mes_qto`.`po_sup_id` WHERE `po_id` = '$poid'");
            $invid = $inv[$i]->ret_code.$inv[$i]->ret_id;

            if($inv[$i]->ret_proceed==1){
                        if($inv[$i]->ret_stetus==1){
                        $sts = "Authorized";
                        }else{
                        $sts = "Invoice Cancelled";}

                    }else{
                        if($inv[$i]->ret_stetus==1){
                        $sts = "Pending Authorize";
                        }else{
                        $sts = "Invoice Cancelled";}

                    }
                   $link = "https://newapp.nance.lk/pdf_invrtn/".$inv[$i]->ret_id;
            $invdt[] = array('return_id'=>$invid,'invoice_'=>$inv[$i]->ret_invnum,'Customer'=>$qt[0]->cust_company,
            'reason'=>$inv[$i]->ret_reason,'retirn_qty'=>$inv[$i]->ret_totqty,'returnQty'=>$inv[$i]->ret_cdate,$sts,'return_amnt'=>$inv[$i]->ret_amount,'link'=>$link);
        }

       if(empty($invdt)){
           $invdt = "No return Notes";
       }

        $dataa = response()->json($invdt);
            return $dataa;

   }

   function get_inv_product(Request $request){

       $this->validate($request, [
            'inv_id' => 'required',
            'prod_id' => 'required',

        ]);

       $inv_id = $request->input('inv_id');
       $prod_id = $request->input('prod_id');

       if($inv_id!="0"){

       $dataa = app('db')->select("SELECT * FROM `qto_items` WHERE `po_aprvl` ='0' AND `po_itm_po_id` = '$inv_id' AND `po_itm_item_id`=$prod_id ");
       $det= [];

       for($i=0; $i<count($dataa); $i++){
           $item_id = $dataa[$i]->po_itm_item_id;

            $item_dt = new Invoice();
            $data = $item_dt->get_sin_item_model($item_id);

            $rec = app('db')->select("SELECT sum(return_items.ret_itm_qty) as rec, sum(return_items.free_item) as free FROM `return_items`
            WHERE `ret_inv_id` = '$inv_id' AND `ret_aprvl` = 1 AND `ret_itm_item_id` = '$item_id'");


            if($rec[0]->rec){
                $rc=$rec[0]->rec; $rcfr=$rec[0]->free;

            }else{
                $rec = app('db')->select("SELECT sum(`mes_inventry`.`invt_qty`) as rec FROM `inv_return`
                        LEFT JOIN `mes_inventry` ON `mes_inventry`.`invt_type_id` = `inv_return`.`ret_id`
                WHERE `inv_return`.`ret_invnum` = '$inv_id' AND `mes_inventry`.`invt_type` = 'INVRTN' AND `mes_inventry`.`invt_itemid` = '$item_id' AND `invt_proc`=1");

                if($rec[0]->rec){$rc=$rec[0]->rec; $rcfr=0;}else{
                    $rc="0"; $rcfr="0";
                }
               $rcfr="0";

            }
                        $rem = $dataa[$i]->po_itm_qty - $rc;
                        $remfree = $dataa[$i]->free_item - $rcfr;


                if(count($dataa)>1){
                    $get_sum = app('db')->select("SELECT sum(po_itm_qty) as invoice_qty,sum(free_item) as free_qty FROM `qto_items` WHERE `po_aprvl` ='0' AND `po_itm_po_id` = '$inv_id' AND `po_itm_item_id`=$prod_id ");
                    $invoiced_qty = $get_sum[0]->invoice_qty; $free_item = $get_sum[0]->free_qty;
                }else{
                    $invoiced_qty = $dataa[$i]->po_itm_qty;  $free_item = $dataa[$i]->free_item;
                }

            $det[] = array('Invoice'=>$inv_id,'item'=>$dataa[$i]->po_itm_id ,
            'item_des'=>$data[0]->itm_name,'invoiced_price'=>$dataa[$i]->po_itm_unitprice,'invoiced_qty'=>$invoiced_qty,
            'already_returned_qty'=>$rc,'free_item'=>$free_item,'already returned free'=>$rcfr);


          /*  $det[] = array('item_id'=>$dataa[$i]->po_itm_item_id,'item'=>$data[0]->cat_shortcode."-".$data[0]->item_id,
            'item_des'=>$data[0]->itm_name,'invoiced_price'=>$dataa[$i]->po_itm_unitprice,'invoiced_qty'=>$dataa[$i]->po_itm_qty,
            'already_returned_qty'=>$rc); */
       }

       }else{

           $item_dt = new Invoice();
            $data = $item_dt->get_sin_item_model($prod_id);

             $qt = app('db')->select("SELECT `retail_price` FROM `batch` WHERE `product` = '$prod_id'");


            $det[] = array('Invoice'=>'','item'=>$prod_id ,
            'item_des'=>$data[0]->itm_name,'invoiced_price'=>$qt[0]->retail_price,'invoiced_qty'=>"50",
            'already_returned_qty'=>"0",'free_item'=>"0",'already returned free'=>"0");
       }

       $dataa = response()->json($det);
            return $dataa;
   }

    function save_return(Request $request){
        $this->validate($request, [
            'ids' => 'required',
            'values' => 'required',
            'inv_id' => 'required',
            'ret_amnt' => 'required',
            'ret_reason' => 'required',
            'free_return' => 'required'

        ]);

        //'free_return' => 'required',
        $str_arr = $request->input('ids');
        $str_arr1 = $request->input('values');

        $id = explode (",", $str_arr);
        $qt = explode (",", $str_arr1);
        $inv_id = $request->input('inv_id');
        $ret_amnt = $request->input('ret_amnt');
        $ret_reason = $request->input('ret_reason');
        $free_return = $request->input('free_return');
        if($free_return!=""){
            $frr = explode (",", $free_return);
        }else{
            $frr = 0;
        }

        $today = date('Y-m-d');
        $tot = 0;


       $headers = $request->header('Authorization');
        $dt = new User;
        $user_data = $dt->getBearerToken($headers);
        $u_name = $user_data[0]->user_name;
        $u_depo = $user_data[0]->user_depoid;

        $inv_det = app('db')->select("SELECT `po_discount` FROM `mes_qto` WHERE `po_id` ='$inv_id'");
        if($inv_det[0]->po_discount>0){
            $disval = ($ret_amnt/100)*$inv_det[0]->po_discount;
            $ret_amnt= $ret_amnt-$disval;}

        $add_invntry = app('db')->insert("INSERT INTO `inv_return` (`ret_invnum`, `ret_totqty`, `ret_amount`, `ret_reason`, `ret_depo`, `ret_cuser`, `ret_cdate`)
        VALUES ('$inv_id', '$tot','$ret_amnt','$ret_reason','$u_depo','$u_name','$today') ");

        $det = app('db')->select("SELECT `ret_id` FROM `inv_return` WHERE `ret_cuser` ='$u_name' ORDER BY `ret_id` DESC LIMIT 1");
        $insert_id = $det[0]->ret_id;

        for($i=0; $i<count($id); $i++){
                if($qt[$i]!=""){

                       // $un_pr = app('db')->select("SELECT `po_itm_unitprice` FROM `qto_items` WHERE `po_itm_po_id` ='$inv_id' and `po_itm_item_id` ='$qt[$i]'");
                       // $unit_price = $un_pr[0]->po_itm_unitprice;

                   // $data = app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `cal`, `invt_cuser`, `invt_cdate`)
                 //   VALUES ('INVRTN','$insert_id','$id[$i]','$qt[$i]','plus','$u_name','$today') ");

                        $pro_det = app('db')->select("SELECT `po_itm_unitprice` FROM `qto_items` WHERE `po_itm_item_id` ='$id[$i]' AND `po_itm_po_id` ='$inv_id'");
                        $un_price = $pro_det[0]->po_itm_unitprice;

                      $data = app('db')->insert("INSERT INTO `return_items` (`ret_itm_item_id`, `depo_id`, `ret_note_id`, `ret_inv_id`, `ret_itm_qty`, `free_item`,
                     `ret_itm_unitprice`,`ret_aprvl`, `ret_user`)
                    VALUES ('$id[$i]', '$u_depo', $insert_id, '$inv_id', '$qt[$i]', '$frr[$i]','$un_price','0', '$u_name') ");

                    $tot = $tot+$qt[$i];
                }
            }

            $updt = app('db')->update("UPDATE `inv_return` SET `ret_totqty`='$tot' WHERE `ret_id`='$insert_id'");

            if($updt){
                $send = "New Invoice Return Created";
            }else{
                $send = "Please Retry";
            }

        $gtcustom = app('db')->select("SELECT `po_sup_id` FROM `mes_qto` WHERE `po_id` ='$inv_id'");
        $cust_id = $det[0]->po_sup_id;

        $credit_cal = new Payment();
       $chk = $credit_cal->cal_credit_limit($cust_id);

        $dataa = response()->json($send);
            return $dataa;

    }

    function grn_auth(Request $request){
        $this->validate($request, [
            'ret_id' => 'required'

        ]);

        $id = $request->input('ret_id');
        $data = app('db')->select("SELECT * FROM `mes_inventry` WHERE `invt_type` ='INVRTN' and `invt_type_id` ='$id' and `invt_proc` ='0' ");
        $dep = app('db')->select("SELECT * FROM `inv_return` WHERE `ret_id` ='$id'");
        $dept = $dep[0]->ret_depo;
        for($i=0; $i<count($data); $i++){
                    $invv = $data[$i]->invt_id;
                 $updt = app('db')->update("UPDATE `mes_inventry` SET `invt_proc`='1' WHERE `invt_type`='INVRTN' and `invt_id`='$invv'");

                  $cal = new Invoice();
                 $cal_stock = $cal->cal_stock_no_inventry('$dept',"INVRTN",'$id','$data[$i]->invt_itemid','$data[$i]->invt_qty',"plus","0");
            }

         $updt = app('db')->update("UPDATE `inv_return` SET `ret_proceed`='1' WHERE `ret_id`='$id'");
         $inv = app('db')->select("SELECT * FROM `mes_qto` WHERE `po_id` ='$dept'");

         if($inv[0]->po_payment < 1){
                    $new_amnt = $inv[0]->return_amnt + $dep[0]->ret_amount;

                   $updt = app('db')->update("UPDATE `mes_qto` SET `return_amnt`='$new_amnt' WHERE `po_id`='$dept'");
                   $updt = app('db')->update("UPDATE `inv_return` SET `ret_to_inv`='1' WHERE `ret_id`='$id'");
                }
        $ret = "Return Data Authorized";

        $dataa = response()->json($ret);
            return $dataa;
    }

    function return_reason(Request $request){
        $reason = app('db')->select("SELECT `ret_reason_id`,`ret_reason` FROM `mes_ret_reason` WHERE `ret_reason_status` =1");

        $data['ret_reason'] = $reason;

            $dataa = response()->json($data);
            return $dataa;
    }

    function get_single_return(Request $request){

         $this->validate($request, [
            'retid' => 'required'
        ]);

       $id = $request->input('retid');

       $mainrtn = app('db')->select("SELECT * FROM `inv_return_main`
       LEFT JOIN `inv_return` ON `inv_return`.`ret_main_id` = `inv_return_main`.`retm_id`
       LEFT JOIN `mes_qto` ON `inv_return`.`ret_invnum` = `mes_qto`.`po_id`
       LEFT JOIN `mes_customer` ON `mes_customer`.`cust_id` = `mes_qto`.`po_sup_id`
       LEFT JOIN `mes_user` ON `mes_user`.`user_id` = `mes_customer`.`cust_salseperson` WHERE `retm_id` = '$id'");


       $rtn = app('db')->select("SELECT * FROM `inv_return`
       LEFT JOIN `mes_qto` ON `inv_return`.`ret_invnum` = `mes_qto`.`po_id`
       LEFT JOIN `mes_customer` ON `mes_customer`.`cust_id` = `mes_qto`.`po_sup_id`
       LEFT JOIN `mes_user` ON `mes_user`.`user_id` = `mes_customer`.`cust_salseperson`
       LEFT JOIN `mes_ret_reason` ON `inv_return`.`ret_reason` = `mes_ret_reason`.`ret_reason_id`
       WHERE `ret_main_id` = '$id'");

       for($i=0; $i<count($rtn); $i++){
           $rtn_code = $rtn[$i]->ret_id;

           $rtnitm = app('db')->select(" SELECT * FROM `return_items`
           LEFT JOIN `mes_item` ON `mes_item`.`item_id` = `return_items`.`ret_itm_item_id`
           LEFT JOIN `mes_category` ON `mes_category`.`cat_id` = `mes_item`.`item_cat`
           LEFT JOIN `mes_brand` ON `mes_brand`.`brand_id` = `mes_item`.`item_brand`
           LEFT JOIN `mes_model` ON `mes_model`.`model_id` = `mes_item`.`item_model`
           LEFT JOIN `mes_unit` ON `mes_unit`.`unit_id` = `mes_item`.`item_unit`
           LEFT JOIN `item` ON `mes_item`.`item_name` = `item`.`itm_id` WHERE `ret_note_id` = '$rtn_code'
          ");

          $rtninv = $rtnitm[0]->ret_inv_id;
          $rtntem = $rtnitm[0]->ret_itm_item_id;
          $uprice = app('db')->select("SELECT `po_itm_unitprice` FROM `qto_items` WHERE `po_itm_po_id` = '$rtninv' AND `po_itm_item_id` = '$rtntem'");


          $inv_num = $rtn[$i]->qto_code."".$rtn[$i]->po_id;

           $inv[] = array($inv_num,
                    $rtnitm[0]->itm_name,
                    $rtnitm[0]->unit_symble,
                    $rtnitm[0]->ret_itm_qty,
                    $rtnitm[0]->ret_itm_unitprice,
                    //$uprice[0]->po_itm_unitprice,
                    $rtnitm[0]->free_item,$rtnitm[0]->ret_inv_id);
       }


        if(count($mainrtn)<1){
            $data = response()->json("Incorrect ID");
        }else{
            $result[0] = $mainrtn;
            $result[1] = $inv;
            $data = response()->json($result);
        }


            return $data;
    }

}
