<?php 

/** class to access instacart integration methods */
class Instacart {

    //Dependancy injection(inject DB object to supply db resourec)
    private $obj;
    public function __construct(DB $obj){
        $this->obj = $obj;
    }

    public function getAllorders($where='',$userlocationWhere='',$start='',$pagesize='',$type='',$orderBY='') {
        

        $sql  = "select io.tracking_urls,io.tracking_no,io.category,io.sub_category,io.storeid,io.section,io.id,io.order_id,io.user_id,io.order_via,product_id,io.qty,io.order_date,io.order_status,
                 io.delivery_date,io.delivery_street_1,io.delivery_street_2,io.delivery_city,io.delivery_state,io.delivery_zipcode,io.country,io.customer_fname,io.customer_lname,io.delivery_estimates_from,io.delivery_estimates_to,
                 io.lead_time,io.tracking_urls, ";

        $sql .= "io.product_name,io.mrp_price,io.size,io.size_type,io.delivery_id,io.remarks,io.reference_code," ;
        $sql .= "io.loyalty_number,io.price_source,io.brand, io.fulfilled_item_type,io.store_location,io.partner_user_ID,io.discount,io.sell_price,io.loyalty_price,
                 io.estimated_retail_revenue,io.instacart_online_price,io.bottle_deposit,io.bag_tax,io.soda_tax,io.bottle_redemption_amt,io.sugar_tax_amt,
                 io.liqour_tax_amt,io.bottle_water_tax_amt,io.discounted_item_price ,io.GMV,io.bag_fees,io.instacart_online_revenue,io.tax_value, partner_order_id ,GMV,
                 "; 

        if($type=='TOTAL') {
                $sql .= "GROUP_CONCAT( DISTINCT io.storeid) all_storeid, SUM(io.tax_value) as tax_value , SUM(io.instacart_online_revenue) as total_amount, SUM(io.bag_fees) as bag_fees,SUM(io.bag_tax) bag_tax,SUM(io.GMV) as GMV,
                SUM(io.bottle_deposit) as bottle_deposit,SUM(io.bag_tax) as bag_tax,SUM(io.soda_tax) AS soda_tax,SUM(io.bottle_redemption_amt) as bottle_redemption_amt ,
                SUM(io.sugar_tax_amt) as sugar_tax_amt ,SUM(io.liqour_tax_amt) as liqour_tax_amt, SUM(io.bottle_water_tax_amt) as bottle_water_tax_amt,
                SUM(io.discounted_item_price) as discounted_item_price,";
        }

                
        $sql .= "io.ship_pincode,io.user_signup_zipcode,io.upc_plu,
                 io.store_location_zip_code,io.is_delivery,io.is_alcoholic,io.is_express_customer,io.is_pickup,io.picking_shopper_type,io.external_order_id
                from insta_order_itmes io where 1=1 ";

        if($where !='') {
            $sql .= $where ;
        }

       

        if($type=='TOTAL') {
            $sql .= "group by io.order_id ";
        }

        //$sql .= " order by io.order_id desc ";
        if($orderBY=='SUBCAT') {
            $sql .= " order by io.category,io.sub_category asc ";
        }else{
            $sql .= " order by io.order_date desc ";
        }
        

        if($pagesize !='') {
            $sql .= "limit $start,$pagesize";
        }
        
        $qyr = $this->obj->Query($sql);
        $result = $this->obj->fetchAllObject($qyr);
        //echo  $sql;die;
        return  $result;
                
    }

    //Method to get order stattsics for current day

    public function orderMetric($currentDate=false,$delivered=false,$cancelled=false,$shiped=false, $where=false) {
        $sql = "SELECT count(*) as total_item, SUM(GMV) AS total_amt ,SUM(tax_value) AS total_tax FROM insta_order_itmes io WHERE 1=1";
        if($currentDate) {
            $sql .= " AND order_date like '%$currentDate%' ";
        }
        
        if($delivered) {
            $sql .= " AND order_status='$delivered'";
        }
        if($cancelled) {
            $sql .= " AND order_status='$cancelled' AND order_status!='$delivered'";
        }
        if($shiped) {
            $sql .= " AND order_status='$shiped' AND order_status!='$delivered' AND order_status!='$cancelled'";
        }
                
            $sql .= " GROUP BY order_id";
       //echo $sql;
        $qyr= $this->obj->Query($sql);
        $totOrder = $this->obj->fetchAllObject($qyr);

        return $totOrder;
    }

//Method to get order stattsics for filters

    public function orderMetricOrders($order_status,$where=false) {
        $sql = "SELECT count(*) as allorder, SUM(GMV) AS total_amt ,SUM(tax_value*qty) AS total_tax FROM insta_order_itmes io WHERE 1=1 $where";
        
        if($order_status) {
            $sql .= " AND order_status='$order_status'";
        }
            
            $sql .= " GROUP BY order_id";
       //echo $sql;
        $qyr= $this->obj->Query($sql);
        $totOrder = $this->obj->fetchAllObject($qyr);

        return $totOrder;
    }

    //Method to get store list for instacart order
    public function getOrdersStore($all_store_id) {
        $all_store_id = "'" . str_replace(",", "','", $all_store_id) . "'";
        $sqlStore = "select storename from  insta_stores  where storeid IN ($all_store_id) ";
        $qyr= $this->obj->Query($sqlStore);
        $strArr = $this->obj->fetchAllObject($qyr);

        $strArr = array_unique($strArr);
        $storeList = implode(', ', array_column($strArr, 'storename')); 
        //$storeList = implode(',', $storeArray);
        return $storeList;
    }

    //Function for get Section/Delivery Partner
    public function sectionDeliveryPartner($all_store_id,$section,$ship_pincode) {
        $all_store_id = "'" . str_replace(",", "','", $all_store_id) . "'";
        if ($section == 1) {
           $sql_partner = "SELECT GROUP_CONCAT(DISTINCT  insta_store_zip.grocery_delivery_partner) grocery
                            FROM insta_store_zip WHERE insta_store_zip.zip = '" . $ship_pincode . "' AND 
                            insta_store_zip.storeid IN($all_store_id) group by insta_store_zip.zip";
            $partnerRslt = $this->obj->query($sql_partner);
            $grocery_partner =  $this->obj->fetchNextObject($partnerRslt);
            $section='grocery';
            return ucwords($section . " " . $grocery_partner->grocery) . "<br/>";

        }if ($section == 2) {
            $sql_partner = "SELECT GROUP_CONCAT(DISTINCT  insta_store_zip.grocery_delivery_partner) grocery
                             FROM insta_store_zip WHERE insta_store_zip.zip = '" . $ship_pincode . "' AND 
                             insta_store_zip.storeid IN($all_store_id) group by insta_store_zip.zip";
             $partnerRslt = $this->obj->query($sql_partner);
             $grocery_partner =  $this->obj->fetchNextObject($partnerRslt);
             $section='Meal Kit';
             return ucwords($section . " " . $grocery_partner->grocery) . "<br/>";
 
         }

        return true;
    }
}
?>