<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
//use Illuminate\Http\Request;
use App\User;
use Session;

class Invoice extends Model
{
  
    function unique_code($limit)
        {
          return substr(base_convert(sha1(uniqid(mt_rand())), 16, 36), 0, $limit);
        }
    
    public function getqtnlast_id(){
       
        $results = app('db')->select("SELECT `po_id` FROM `mes_qto` ORDER BY `po_id` DESC LIMIT 1");
        return $results[0]->po_id;

    }

    public function sales_ref_list(){
       
        $results = app('db')->select("SELECT * FROM `mes_user` where `mes_user`.`user_departmentid` ='Marketing'");

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

    }
    
    public function get_sin_item_model($itemid){
        $results = app('db')->select("SELECT * FROM `mes_item` LEFT JOIN `item` ON `item`.`itm_id` = `mes_item`.`item_name` LEFT JOIN `mes_category` ON `mes_category`.`cat_id` = `mes_item`.`item_cat` where `mes_item`.`item_id` = '$itemid'");
         return $results;
    }

    public function get_sales_depo($saleid){

        $results = app('db')->select("SELECT `user_depoid` FROM `mes_user` where `mes_user`.`user_id` ='$saleid'");

        $data = $results[0]->user_depoid;
            return $data;
    }

    public function get_depo_item_stock($depo,$item,$qty){

                $results = app('db')->select("SELECT `dpstock_qty` FROM `mes_depot_stock` where `dpstock_dpoid` ='$depo' and `dpstock_itemid` ='$item'");

                 if ($results)
                    {
                        // it return number of rows in the table.
                        $row = count($results);
                        if($row>0){
                           $resultitem = $results[0]->dpstock_qty; 
                           if($resultitem >= $qty){ $data = "Available";}else{$data = "No Stock";}  
                        }else{
                            $data = "No Stock";
                        }
                    }else{
                        $data = "No Stock";
                    }

               return $data;      

                                  
    }


    public function get_main_item_stock($depo,$item,$qty){

                $results = app('db')->select("SELECT SUM(`avl_qty`) as tot FROM `batch` WHERE `product`='$item' and `avl_qty`!=0");
                $row = count($results);
                
                if($row>0){
                           $resultitem = $results[0]->tot; 
                           if($resultitem >= $qty){ $data = "Available";}else{$data = "No Stock";}  
                        }else{
                            $data = "No Stock";
                        }
                
                /* if ($results)
                    {
                        // it return number of rows in the table.
                        $row = count($results);
                        if($row>0){
                           $resultitem = $results[0]->tot; 
                           if($resultitem >= $qty){ $data = "Available";}else{$data = "No Stockk";}  
                        }else{
                            $data = "No Stock";
                        }
                    }else{
                        $data = "No Stock";
                    } */

               return $data;      

                                  
    }

    public function chk_itm_price($item,$item_price,$u_id){

        $resultitem = app('db')->select("SELECT * FROM `batch` where `batch_id` = '$item'");

                
                $resultgen = app('db')->select("SELECT * FROM `general`");

            $max_price = ($resultitem[0]->selling_price / 100 )*(100 - $resultgen[0]->price_pc); 
            $max_price2 = $max_price /2;
                
            if($item_price > $max_price){
                $detail = "Approved";
                $updt = app('db')->update("UPDATE `temp_qto_items` SET `po_itm_unitprice`='$item_price' WHERE `po_user` = '$u_id' and `po_itm_batch`=$item");
            }else if(($max_price2 < $item_price) && ($item_price < $max_price)){
                $detail = "Need Approval";
                $updt = app('db')->update("UPDATE `temp_qto_items` SET `po_itm_unitprice`='$item_price',`po_aprvl`=1 WHERE `po_user` = '$u_id' and `po_itm_batch`=$item");
            }else{
                $detail = "Not Approved";
            }
            
            if($item_price<1){
                $detail = "Not Approved";
            }
            return $detail;
    }


    public function gt_free_item($item,$qty){
        $resultitem = app('db')->select("SELECT * FROM `mes_item` LEFT JOIN `item` ON `item`.`itm_id` = `mes_item`.`item_name` LEFT JOIN `mes_category` ON `mes_category`.`cat_id` = `mes_item`.`item_cat` where `mes_item`.`item_id` = '$item'");

    if($resultitem[0]->item_freeitm!=0){
        $count = $resultitem[0]->item_freqty / $resultitem[0]->item_freeitm ;
    }else{
        $count = 0 ;
    }
        
        if($count!=0){$cal_free = $qty/$count;}else{$cal_free = 0;}
        
        $resultgen = app('db')->select("SELECT * FROM `general`");
        $max_free = $cal_free * $resultgen[0]->free_max_qty;

        return $max_free; 
    }
    
    public function cal_stock($depo,$type,$inv_id,$item_id,$qty,$add,$user){
      //  $headers = $request->header('Authorization');
       // $dt = new User;
       // $user_data = $dt->getBearerToken($headers);
       // $u_name = $user_data[0]->user_name;
        $u_name = $user;
        $date = date('Y-m-d'); 
        
            if($depo==0){
                $newstk = 0;
                $gt_stock = app('db')->select("SELECT * FROM `mes_stock` where `stc_itemid` = '$item_id'");
                 $stqty = $gt_stock[0]->stc_qty;
                        $acqty = $gt_stock[0]->stc_actqty;

                        if($add=="plus"){
                                $newstk = $stqty + $qty;
                                $newact = $acqty + $qty;
                            }else{
                                $newstk = $stqty - $qty;
                                $newact = $acqty - $qty;
                            }

                    $add_invntry = app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`,`invt_stock`, `cal`, `invt_proc`,`invt_cuser`)
                    VALUES ('$type', $inv_id, $item_id,$qty,$stqty,'$add','1','$u_name') ");  
                    
                   $updt = app('db')->update("UPDATE `mes_stock` SET `stc_actqty`='$newstk',`stc_qty`='$newstk' WHERE `stc_itemid` = '$item_id'");      
            }else{
                $newstk = 0;
                $gt_stock = app('db')->select("SELECT * FROM `mes_depot_stock` where `dpstock_itemid` = '$item_id' and `dpstock_dpoid` = '$depo'");
                $stqty = $gt_stock[0]->dpstock_qty;

                if($add=="plus"){
                                $newstk = $stqty + $qty;
                            }else{
                                $newstk = $stqty - $qty;
                            }

                $add_invntry = app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `invt_stock`,`cal`, `invt_proc`,`invt_cuser`) 
                VALUES ('$type', $inv_id, $item_id,$qty,$stqty,'$add','1','$u_name')"); 

                 $updt = app('db')->update("UPDATE `mes_depot_stock` SET `dpstock_qty`='$newstk' WHERE `dpstock_itemid` = '$item_id' and `dpstock_dpoid` = '$depo'");
            }
    } 
    
    public function cal_stock_no_inventry($depo,$type,$inv_id,$item_id,$qty,$add,$user){
            if($depo==0){
                $gt_stock = app('db')->select("SELECT * FROM `mes_stock` where `stc_itemid` = '$item_id'");
                 $stqty = $gt_stock[0]->stc_qty;
                        $acqty = $gt_stock[0]->stc_actqty;

                        if($add=="plus"){
                                $newstk = $stqty + $qty;
                                $newact = $acqty + $qty;
                            }else{
                                $newstk = $stqty - $qty;
                                $newact = $acqty - $qty;
                            }

                   // $add_invntry = app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `cal`, `invt_proc`) VALUES ('$type', $inv_id, $item_id,$qty,'$add','1') ");  
                    
                   $updt = app('db')->update("UPDATE `mes_stock` SET `stc_actqty`='$newstk',`stc_qty`='$newstk' WHERE `stc_itemid` = '$item_id'");      
            }else{
                $gt_stock = app('db')->select("SELECT * FROM `mes_depot_stock` where `dpstock_itemid` = '$item_id' and `dpstock_dpoid` = '$depo'");
                $stqty = $gt_stock[0]->dpstock_qty;

                if($add=="plus"){
                                $newstk = $stqty + $qty;
                            }else{
                                $newstk = $stqty - $qty;
                            }

                //$add_invntry = app('db')->insert("INSERT INTO `mes_inventry` (`invt_type`, `invt_type_id`, `invt_itemid`, `invt_qty`, `cal`, `invt_proc`) VALUES ('$type', $inv_id, $item_id,$qty,'$add','1') "); 

                 $updt = app('db')->update("UPDATE `mes_depot_stock` SET `dpstock_qty`='$newstk',`stc_qty`='$newstk' WHERE `dpstock_itemid` = '$item_id' and `dpstock_dpoid` = '$depo'");
            }
    }
    
    public function outstand($cust){
        $sdata = app('db')->select("SELECT SUM(`po_final_tot`) as tot,SUM(`return_amnt`) as ret FROM `mes_qto` where `po_sup_id` = '$cust' AND `po_payment`=0");
        $out = $sdata[0]->tot - $sdata[0]->ret;          
            return $out;
         
    }
    
    public function cal_batch($depo,$type,$inv_id,$item_id,$qty,$add,$batch,$usr){
            if($depo==0){
                $gt_stock = app('db')->select("SELECT * FROM `batch` where `batch_id` = '$batch'");

                        if($add=="plus"){
                                $newstk = $gt_stock[0]->avl_qty + $qty;
                            }else{
                                $newstk = $gt_stock[0]->avl_qty - $qty;
                            }
                
                    $batch_name = $gt_stock[0]->batch;
                    $avl_qty = $gt_stock[0]->avl_qty;
                    $today = date('Y-m-d');

                 $add_invntry = app('db')->insert("INSERT INTO `batch_log` (`batch_id`, `batch`, `type`, `doc_id`, `cal`, `qty`, `old_qty`, `new_qty`, `depo`, `date`, `user`) VALUES
                 ('$batch', '$batch_name','$type','$inv_id','$add',$qty,$avl_qty,$newstk ,'0','$today','$usr') ");  
                    
                   $updt = app('db')->update("UPDATE `batch` SET `avl_qty`='$newstk' WHERE `batch_id` = '$batch'");
                   
                   $update_stock = $this->cal_stock($depo,$type,$inv_id,$item_id,$qty,$add,$usr); 
            }else{
                $today = date('Y-m-d');
                $gt_stock = app('db')->select("SELECT * FROM `batch_depo` where `batch_id` = '$batch' and `depo`='$depo'");
                
                $newavlstk = 0;
                        if($add=="plus"){
                                $newavlstk = $gt_stock[0]->avl_qty + $qty;
                                $newtotstk = $gt_stock[0]->tot_qty + $qty;
                            }else{
                                $newavlstk = $gt_stock[0]->avl_qty - $qty;
                                $newtotstk = $gt_stock[0]->tot_qty - $qty;
                            }
                            $batch_name = $gt_stock[0]->batch;
                            $avl_aty = $gt_stock[0]->avl_qty;
                            $tot_qty= $gt_stock[0]->tot_qty;
                            $item = $gt_stock[0]->product;

                 $add_invntry = app('db')->insert("INSERT INTO `batch_log` (`batch_id`, `batch`, `type`, `doc_id`, `cal`, `qty`, `old_qty`, `new_qty`, `depo`, `date`, `user`) VALUES
                 ('$batch', '$batch_name','$type','$inv_id','$add',$qty,$avl_aty,$newavlstk ,'$depo','$today','$usr') ");  
                    
                   $updt = app('db')->update("UPDATE `batch_depo` SET `avl_qty`='$newavlstk',`tot_qty`='$tot_qty' WHERE `batch_id` = '$batch' and `depo`='$depo'"); 
                   $update_stock = $this->cal_stock($depo,$type,$inv_id,$item_id,$qty,$add,$usr);
                  // $updt = app('db')->update("UPDATE `mes_depot_stock` SET `dpstock_qty`='$newavlstk' WHERE `dpstock_itemid` = '$item' and `dpstock_dpoid`='$depo'"); 
            }
         return "done";
    }

}
