<?php

namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

class Orders extends Model
{

    public $table      = 'uc_orders';
    public $timestamps = false;

    /*
     * Date: 10-01-2019
     * Author: Imran Khan
     * Parameter: $request
     * Description:  this function will return the order list
     */
    public static function GetOrderList($request = "",$page="")
    {
        $orders = DB::table('uc_orders as o');
        if (!empty($page)) {
        $orders = $orders->selectraw('o.order_id, o.uid, o.billing_first_name, o.billing_last_name, o.order_total, o.order_status, o.created, os.title, u.name, u.email');
        }
        else {
        $orders = $orders->selectraw('count(o.order_id) total_order, sum(o.order_total) total_revenue,"'.$request->order_status.'" as ostatus');
        }
        $orders = $orders->join('users as u', 'o.uid', '=', 'u.id')
            ->leftJoin('uc_order_statuses as os', 'o.order_status', '=', 'os.order_status_id');

        if (!empty($request)) {
            if (isset($request->order_id)) {
                $orders = $orders->where('o.order_id', '=', $request->order_id);
            }
            if (isset($request->order_status)) {
                $orders = $orders->where('o.order_status', '=', $request->order_status);
            }
            if (isset($request->customer_email)) {
                $orders = $orders->where('u.email', '=', $request->customer_email);
            }
            if (isset($request->source)) {
                $orders = $orders->where('o.source', '=', $request->source);
            }
            if (isset($request->from_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) >= TO_DAYS("' . $request->from_date . '")');
            }
            if (isset($request->to_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) <= TO_DAYS("' . $request->to_date . '")');
            }
        }

        $orders = $orders->orderBy('o.order_id', 'desc');				if ($page == 'all') {							$orders = $orders->get();			        } else {
			if (!empty($page)) {
				$orders = $orders->paginate(20);
			}
			else
			{
				$orders = $orders->first();
			}		}

        return $orders;
    }

    /*
     * Date: 11-01-2019
     * Author: Imran Khan
     * Parameter: $request
     * Description:  this function will return the order status list
     */

    public static function GetOrderStatus()
    {

        $ord_status = DB::table('uc_order_statuses')->orderBy('order_status_id', 'asc')->get();
        return $ord_status;
    }

    /*
     * Date: 11-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order detail
     */

    public static function GetOrder($order_id)
    {

        $order = DB::table('uc_orders as o')
            ->select('o.*', 'c.*')
            ->Join('users as u', 'u.id', '=', 'o.uid')
            ->leftJoin('country as c', 'c.iso', '=', 'u.country')
            ->where('order_id', '=', $order_id)->first();
        return $order;
    }
    /*
     * Date: 11-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order payment detail
     */

    public static function GetOrderPaymentReceipts($order_id)
    {

        $pament = DB::table('uc_payment_receipts')->where('order_id', '=', $order_id)->get();
        return $pament;
    }
    /*
     * Date: 14-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order products list
     */
    public static function GetOrderProducts($order_id)
    {
        $products = DB::table('uc_order_products')->where('order_id', '=', $order_id)->get();
        return $products;
    }
    /*
     * Date: 14-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order comments
     */
    public static function GetOrderComments($order_id)
    {
        $products = DB::table('uc_order_comments')->where('order_id', '=', $order_id)->get();
        return $products;
    }
    /*
     * Date: 14-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order comments
     */
    public static function GetOrderAdminComments($order_id)
    {
        $products = DB::table('uc_order_admin_comments')->where('order_id', '=', $order_id)->get();
        return $products;
    }

    /*
     * Date: 14-01-2019
     * Author: Imran Khan
     * Parameter: $data
     * Description:  this function will add the Order comments
     */
    public static function AddOrderComments($data)
    {
        DB::table('uc_order_comments')->insert($data);
        return true;
    }

    /*
     * Date: 19-01-2019
     * Author: Arshad
     * Parameter: $data
     * Description:  this function will add the Order
     */
    public static function CreateOrder($data)
    {

        $order_id = DB::table('uc_orders')->insertGetId($data);
        return $order_id;

    }

    public static function AddProducts($data)
    {

        DB::table('uc_order_products')->insert($data);
        return true;

    }

    /*
     * Date: 14-01-2019
     * Author: Imran Khan
     * Parameter: $id,$data
     * Description:  this function will Update the Order comments
     */
    public static function UpdateOrder($id, $data)
    {
        DB::table('uc_orders')
            ->where('order_id', $id)
            ->update($data);
        return true;
    }

    /*
     * Date: 16-01-2019
     * Author: Imran Khan
     * Parameter: $request
     * Description:  this function will add the Order Admin comments
     */
    public static function AddOrderAdminComments($data)
    {
        DB::table('uc_order_admin_comments')->insert($data);
        return true;
    }

    /*
     * Date: 16-01-2019
     * Author: Imran Khan
     * Parameter: $request
     * Description:  this function will add the Order Admin comments
     */
    public static function AddOrderLog($data)
    {
        DB::table('uc_order_log')->insert($data);
        return true;
    }

    /*
     * Date: 16-01-2019
     * Author: Imran Khan
     * Parameter: $order_id
     * Description:  this function will return the order logs
     */
    public static function GetOrderLogs($order_id)
    {
        $products = DB::table('uc_order_log')->where('order_id', '=', $order_id)->get();
        return $products;
    }

    /*
     * Date: 19-01-2019
     * Author: Arshad
     * Parameter: $order_id
     * Description:  this function will return the order Detail
     */
    public static function GetOrderDetail($order_id)
    {
        //$products=DB::table('uc_order_products')->where('order_id','=',$order_id)->get();
        $sql = "select c.*,o.*,a.title item_title,v.volume_name volume,j.title journal_ebook_title,i.title issue,c.cost item_cost,c.price item_price,'article' as item_type,'N' as discount from uc_order_products c
              inner join uc_orders o on o.order_id=c.order_id
              inner join article a on a.nid=c.nid
              inner join volume v on v.volume_id=a.volume_id
              inner join journal j on j.journal_id=a.journal_id
              left join issue i on i.issue_id=a.issue_id
              WHERE c.order_id ='$order_id'

              union

              select c.*,o.*,ch.title item_title,v.volume_name volume,e.title journal_ebook_title,'' as issue,c.cost item_cost,c.price item_price,'chapter' as item_type,'N' as discount from uc_order_products c
              inner join uc_orders o on o.order_id=c.order_id
              inner join chapter ch on ch.nid=c.nid
              inner join ebook_volume v on v.ebook_volume_id=ch.ebook_volume_id
              inner join ebook e on e.ebook_id=v.ebook_id
              WHERE c.order_id ='$order_id'

              union

              select c.*,o.*,'' as item_title,v.volume_name volume,e.title journal_ebook_title,'' as issue,c.cost item_cost,c.price item_price,'ebook_volume' as item_type,'N' as discount from uc_order_products c
              inner join uc_orders o on o.order_id=c.order_id
              inner join ebook_volume v on v.nid=c.nid
              inner join ebook e on e.ebook_id=v.ebook_id
              WHERE c.order_id ='$order_id'

              ";

        $products = DB::select($sql);

        return $products;
    }

    /**
     * Date: 19-01-2019
     * Author: Arshad
     * Parameter: $order_id
     * Description:  Save an order to database
     */
    public static function uc_order_save($order)
    {   
        if (is_null($order->order_id) || intval($order->order_id) == 0) {
            return false;
        }
        $source     = "BSP";
        $source_val = 1;
        if (session()->has('trendmd_shared')) {
            if (session()->get('trendmd_shared') == 3) {
                $source = "jw";
            } elseif (session()->get('trendmd_shared') == 4) {
                $source = "disease";
            } else {
                $source = "trendmd";
            }
            $source_val = session()->get('trendmd_shared');
        }
        $data['uid']            = $order->uid;
        $data['order_status']   = $order->order_status;
        $data['order_total']    = $order->order_total;
        $data['product_count']  = $order->product_count;
        $data['primary_email']  = $order->primary_email;
        $data['payment_method'] = $order->payment_method;
        $data['transaction_id'] = $order->transactionNo;
        $data['data']           = serialize($order->data);
        $data['host']           = $order->host;
        $data['ip_address']     = $order->ip_address;
        $data['source']         = $source;
        $data['source_val']     = $source_val;
        $data['modified']       = time();

        Orders::UpdateOrder($order->order_id, $data);

    }

    /**
     * Date: 19-01-2019
     * Author: Arshad
     * Parameter: $order_id
     * Description:  Enter a payment for an order
    */
   
    public static function uc_payment_enter($order_id, $method, $amount, $uid, $data, $comment) {
      
      if (is_array($data)) {
        $data = serialize($data);
      }
      $mdata['order_id']=$order_id;
      $mdata['method']=$method;
      $mdata['amount']=$amount;
      $mdata['uid']=$uid;
      $mdata['data']=$data;
      $mdata['comment']=$comment;
      $mdata['received']=time();

      DB::table('uc_payment_receipts')->insert($mdata);
      return true;     
    }


    /**
     * Insert a comment, $type being either 'order' or 'admin'
     */
    public static function uc_order_comment_save($order_id, $uid, $message, $type = 'admin', $status = 'pending', $notify = FALSE) {
        
        $data['order_id'] = $order_id;
        $data['uid'] = $uid;
        $data['message'] = $message;
        $data['created'] = time();

          if ($type == 'admin') {
            DB::table('uc_order_admin_comments')->insert($data);            
          }
          elseif ($type == 'order') {
            
            $data['order_status'] = $status;
            $data['notified'] = $notify ? 1 : 0;            
            DB::table('uc_order_comments')->insert($data);
            
          }
    }
    /*
     * Date: 23-01-2019
     * Author: Imran Khan
     * Parameter: $request
     * Description:  this function will return the sale summary order reports
     */
    public static function GetSaleSummary($start_date="", $end_date="", $graph="")
    {
        $orders = DB::table('uc_orders as o');
        $orders = $orders->selectraw('FROM_UNIXTIME(o.created,"%e") day, FROM_UNIXTIME(o.created,"%Y") as year , FROM_UNIXTIME(o.created,"%b") as month, count(o.order_id) total_order, sum(o.order_total) total_revenue')
        ->where('o.order_status', '=', 'completed');

        if(!empty($graph) && $graph=="month")  
        {
            if (isset($start_date)) {
                $orders = $orders->whereRaw('Month(FROM_UNIXTIME(o.created)) = Month("'.$start_date.'")');
            }
            if (isset($end_date)) {
                $orders = $orders->whereRaw('Year(FROM_UNIXTIME(o.created)) = Year("'.$end_date.'")');
            }
        $orders = $orders->groupBy(DB::raw('FROM_UNIXTIME(o.created,"%d")'))->get();

        }
        else if(!empty($graph) && $graph=="year")  
        {
            if (isset($start_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) >= TO_DAYS("'.$start_date.'")');
            }
            if (isset($end_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) <= TO_DAYS("'.$end_date.'")');
            }
        $orders = $orders->orderByRaw('(FROM_UNIXTIME(o.created,"%Y") - FROM_UNIXTIME(o.created,"%m")) asc');
        $orders = $orders->groupBy(DB::raw('FROM_UNIXTIME(o.created,"%Y")'));
        $orders = $orders->groupBy(DB::raw('FROM_UNIXTIME(o.created,"%m")'));
        $orders = $orders->get();


        }
        else
        {
            if (isset($start_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) >= TO_DAYS("'.$start_date.'")');
            }
            if (isset($end_date)) {
                $orders = $orders->whereRaw('TO_DAYS(FROM_UNIXTIME(o.created,"%Y-%m-%d")) <= TO_DAYS("'.$end_date.'")');
            }
            $orders = $orders->first();
        }
        return $orders;
    } 

  
}
