<?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 `stc_qty` FROM `mes_stock` where `stc_itemid` ='$item'");
                $row = count($results);
                 if ($results)
                    {
                        // it return number of rows in the table.
                        $row = count($results);
                        if($row>0){
                           $resultitem = $results[0]->stc_qty; 
                           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){

        $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'");

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

            $max_price = ($resultitem[0]->item_selling_price / 100 )*(100 - $resultgen[0]->price_pc); 
                
            if($item_price > $max_price){
                $detail = "approved";
            }else{
                $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,$from){
      //  $headers = $request->header('Authorization');
       // $dt = new User;
       // $user_data = $dt->getBearerToken($headers);
       // $u_name = $user_data[0]->user_name;
        $u_name = 'nance';
        $date = date('Y-m-d'); 
        
            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`,`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{
                $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,$from){
            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;
        
    }

}
