<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class shipmentmodel extends CI_Model {

    function fnValidateShipmentInfo($VarShipmentId='',$VarInvoiceNo='',$VarWebsiteId='',$Validate=1,$VarShipmentType='',$VarShipmentStatus='',$VarWayBillNo='',$VarDateUpdated='',$VarOrderDate='',$orderId='') {
        $this->db->select('id,websiteid,orderdate,orderno,waybillno,carrierid,shipmenttype,customername,emailid,mobileno,address,state,city,noofboxes,weight,unittype,shipmentstatus,emailnotificationalert,mobilenotificationalert,updatedby,status,datecreated,dateupdated,shipmentdate,orderdate,zipcode,trackingdate,trackingmsg,trackingid,trackingid,orderid,quantity');
        $this->db->from(ZM_PRODUCT_SHIPMENT);
        $ArrWhere = array('status'=>"1");
        if($VarShipmentId<>'') {
            $ArrWhere['id']		        = $VarShipmentId;
        }
        if($VarInvoiceNo<>'') {
            $ArrWhere['orderno']		= $VarInvoiceNo;
        }
        if($orderId<>'') {
            $ArrWhere['orderid']		= $orderId;
        }
        if($VarWebsiteId<>'') {
            $ArrWhere['websiteid']		= $VarWebsiteId;
        }
        if($VarShipmentType<>'') {
            $ArrWhere['shipmenttype']	= $VarShipmentType;
        }
        if($VarShipmentStatus<>'') {
            $ArrWhere['shipmentstatus']		= $VarShipmentStatus;
        }
        if($VarWayBillNo<>'') {
            $ArrWhere['waybillno']	        = $VarWayBillNo;
        }
        if($VarDateUpdated<>'') {
            $ArrWhere['date(dateupdated)']	    = $VarDateUpdated;
        }
        if($VarOrderDate<>'') {
            $ArrWhere['date(orderdate)']	    = $VarOrderDate;
        }
        $this->db->where($ArrWhere);
        $ObjUserInfo					= $this->db->get();

        if($Validate==1) {
            $VarNumRows					= $ObjUserInfo->num_rows();
            return $VarNumRows;
        } else {
            $ArrProfileInfo				= $ObjUserInfo->result_array();
            return $ArrProfileInfo;
        }
    }

    function fnSaveShipmentInfo($ArrShipmentInfo){
        $this->db->insert(ZM_PRODUCT_SHIPMENT,$ArrShipmentInfo);
        return $this->db->insert_id();
    }

    function fnSaveShipmentNotificationInfo($ArrShipmentNotificationInfo) {
        $this->db->insert(ZM_PRODUCT_SHIPMENT_NOTIFICATION,$ArrShipmentNotificationInfo);
        return $this->db->insert_id();
    }

    function fnSaveShipmentNotificationType($ArrShipmentNotificationInfo) {
        $this->db->insert(ZM_PRODUCT_SHIPMENT_NOTIFICATION_TYPE,$ArrShipmentNotificationInfo);
        return $this->db->insert_id();
    }

    function fnCountShipmentProductInfo($VarWayBillNo='',$VarOrderType='',$VarCourierName='',$VarCustomerName='',$VarShipmentStatus='',$VarMobile='',$VarEmailId='',$VarFromDate='',$VarToDate=''){
        $ArrWhere                   = array();
        if($VarMobile<>'') {
            $ArrWhere[]             = "mobileno like '%".$VarMobile."%'";
        }
        if($VarEmailId<>'') {
            $ArrWhere[]             = "emailid like '%".$VarEmailId."%'";
        }
        if($VarWayBillNo<>'') {
            $ArrWhere[]             = "waybillno like '%".$VarWayBillNo."%'";
        }
        if($VarOrderType<>'') {
            $ArrWhere[]             = "shipmenttype in(".$VarOrderType.")";
        }
        if($VarCourierName<>'') {
            $ArrWhere[]             = "carrierid in(".$VarCourierName.")";
        }
        if($VarCustomerName<>'') {
            $ArrWhere[]             = "customername like '%".$VarCustomerName."%'";
        }
        if($VarShipmentStatus<>'') {
            $ArrWhere[]             = "shipmentstatus in(".$VarShipmentStatus.")";
        }
        if($VarFromDate<>'' && $VarToDate<>'') {
            $ArrWhere[]             = "(trackingdate>='".date('Y-m-d',strtotime($VarFromDate))."' and trackingdate<='".date('Y-m-d',strtotime($VarToDate))."')";
        }
        $VarWhere                   = '';
        if(@$ArrWhere[0]<>'') {
            $VarWhere               = " and ".implode(" and ",$ArrWhere);
        }
        $VarSqlEmployeeList	        = "SELECT id  FROM  ".ZM_PRODUCT_SHIPMENT." WHERE status=1 ".$VarWhere;
        $VarNumRows					= $this->db->query($VarSqlEmployeeList)->num_rows();
        return $VarNumRows;
    }

    function fnListShipmentProductInfo($VarWayBillNo='',$VarOrderType='',$VarCourierName='',$VarCustomerName='',$VarShipmentStatus='',$VarMobile='',$VarEmailId='',$VarFromDate='',$VarToDate='',$VarLimit = 10, $offset = 0,$VarSortBy,$VarSortOrder){
        $VarSortOrder				= ($VarSortOrder=='desc')? 'desc' : 'asc';
        $VarSortCols				= array("1"=>'customername',"2"=>'dateupdated');
        $VarSortBy					= (in_array($VarSortBy,$VarSortCols)) ? $VarSortBy : 'dateupdated';
        $VarLimitInfo				= $VarLimit;
        if($offset>=1) {$VarLimitInfo	 = $offset.",".$VarLimit;}
        $ArrWhere                   = array();
        if($VarMobile<>'') {
            $ArrWhere[]             = "mobileno like '%".$VarMobile."%'";
        }
        if($VarEmailId<>'') {
            $ArrWhere[]             = "emailid like '%".$VarEmailId."%'";
        }
        if($VarWayBillNo<>'') {
            $ArrWhere[]             = "waybillno like '%".$VarWayBillNo."%'";
        }
        if($VarOrderType<>'') {
            $ArrWhere[]             = "shipmenttype in(".$VarOrderType.")";
        }
        if($VarCourierName<>'') {
            $ArrWhere[]             = "carrierid in(".$VarCourierName.")";
        }
        if($VarCustomerName<>'') {
            $ArrWhere[]             = "customername like '%".$VarCustomerName."%'";
        }
        if($VarShipmentStatus<>'') {
            $ArrWhere[]             = "shipmentstatus in(".$VarShipmentStatus.")";
        }
        if($VarFromDate<>'' && $VarToDate<>'') {
            $ArrWhere[]             = "(trackingdate>='".date('Y-m-d',strtotime($VarFromDate))."' and trackingdate<='".date('Y-m-d',strtotime($VarToDate))."')";
        }
        $VarWhere                   = '';
        if(@$ArrWhere[0]<>'') {
            $VarWhere               = " and ".implode(" and ",$ArrWhere);
        }
        $VarSqlShipmentList		    = "SELECT id,websiteid,orderdate,orderno,waybillno,carrierid,shipmenttype,customername,emailid,mobileno,state,city,shipmentstatus,status,datecreated,dateupdated,shipmentdate FROM ".ZM_PRODUCT_SHIPMENT." where status=1 ".$VarWhere." order by ".$VarSortBy." ".$VarSortOrder." limit ".$VarLimitInfo;
        $ObjResult					= $this->db->query($VarSqlShipmentList);
        return $ObjResult;
    }

    function fnUpdateShipmentInfo($ArrSaveShipmentDetails=array(),$VarTrackingId='') {
        $VarResult = $this->db->update(ZM_PRODUCT_SHIPMENT,$ArrSaveShipmentDetails,array('id'=>$VarTrackingId));
        return $VarResult;
    }

    function fnDelShipmentNotificationInfo($VarTrackingId='') {
        $VarDelQuery = "delete from ".ZM_PRODUCT_SHIPMENT_NOTIFICATION." where shipmentid=".$VarTrackingId;
        $this->db->query($VarDelQuery);
    }

    function fnDelShipmentNotificationType($VarTrackingId='') {
        $VarDelQuery = "delete from ".ZM_PRODUCT_SHIPMENT_NOTIFICATION_TYPE." where shipmentid=".$VarTrackingId;
        $this->db->query($VarDelQuery);
    }


    function fnGetShipmentNotificationInfo($VarShipmentId='',$VarContactType='') {
        $this->db->select('id,shipmentid,contactdetails,contacttype,updatedby,status,datecreated,dateupdated');
        $this->db->from(ZM_PRODUCT_SHIPMENT_NOTIFICATION);
        $ArrWhere = array('status'=>"1");
        if($VarShipmentId<>'') {
            $ArrWhere['shipmentid']		        = $VarShipmentId;
        }
        if($VarContactType<>'') {
            $ArrWhere['contacttype']		    = $VarContactType;
        }
        $this->db->where($ArrWhere);
        $ObjUserInfo					        = $this->db->get();
        $ArrProfileInfo				            = $ObjUserInfo->result_array();
        return $ArrProfileInfo;
    }

    function fnGetShipmentNotificationType($VarShipmentId='',$VarNotificationType='') {
        $this->db->select('id,shipmentid,notificationtype,contacttype,updatedby,status,datecreated,dateupdated');
        $this->db->from(ZM_PRODUCT_SHIPMENT_NOTIFICATION_TYPE);
        $ArrWhere = array('status'=>"1");
        if($VarShipmentId<>'') {
            $ArrWhere['shipmentid']		        = $VarShipmentId;
        }
        if($VarNotificationType<>'') {
            $ArrWhere['notificationtype']		= $VarNotificationType;
        }
        $this->db->where($ArrWhere);
        $ObjUserInfo					        = $this->db->get();
        $ArrProfileInfo				            = $ObjUserInfo->result_array();
        return $ArrProfileInfo;
    }

    function fnDelShipmentNotificationLog($VarShipmentId='') {
        $VarDelQuery = "delete from ".ZM_PRODUCT_SHIPMENT_NOTIFICATION_LOG." where shipmentid=".$VarShipmentId;
        $this->db->query($VarDelQuery);
    }

    function fnSaveShipmentNotificationLog($ArrShipmentNotificationLog) {
        $this->db->insert(ZM_PRODUCT_SHIPMENT_NOTIFICATION_LOG,$ArrShipmentNotificationLog);
        return $this->db->insert_id();
    }

    function fnGetPendingShipmentJobInfo($VarCarrierList='') {
        if($VarCarrierList<>'') {
            $VarSqlShipmentList		            = "SELECT id,websiteid,orderdate,orderno,waybillno,carrierid,shipmenttype,customername,emailid,mobileno,state,city,shipmentstatus,status,datecreated,dateupdated,shipmentdate FROM ".ZM_PRODUCT_SHIPMENT." where status=1 and carrierid in(".$VarCarrierList.") and shipmentstatus!=4";
            $ObjResult					        = $this->db->query($VarSqlShipmentList);
            return $ObjResult->result_array();
        }
    }

    function fnGetShipmentNotificationLog($VarShipmentId='',$VarShipmentStatus='',$VarStatus='') {
        $this->db->select('id,shipmentid,waybillno,logdate,description,logdetails,shipmentstatus,status,datecreated,dateupdated');
        $this->db->from(ZM_PRODUCT_SHIPMENT_NOTIFICATION_LOG);
        $ArrWhere = array('status'=>"1");
        if($VarShipmentId<>'') {
            $ArrWhere['shipmentid']		        = $VarShipmentId;
        }
        if($VarShipmentStatus<>'') {
            $ArrWhere['shipmentstatus']	        = $VarShipmentStatus;
        }
        if($VarStatus<>'') {
            $ArrWhere['status']		            = $VarStatus;
        }
        $this->db->where($ArrWhere);
        $this->db->order_by('logdate', 'DESC');
        $ArrShipmentLogInfo			            = $this->db->get()->result_array();
        return $ArrShipmentLogInfo;
    }

    function fnSaveShipmentOrderInfo($ArrShipmentOrderInfo) {
        $this->db->insert(ZM_PRODUCT_SHIPMENT_ORDER_INFO,$ArrShipmentOrderInfo);
        return $this->db->insert_id();
    }

    function fnGetShipmentOrderInfo($VarShipmentId='',$VarStatus='') {
        $this->db->select('id,shipmentid,reforderid,startserialno,endserialno,status,datecreated,dateupdated');
        $this->db->from(ZM_PRODUCT_SHIPMENT_ORDER_INFO);
        $ArrWhere = array('status'=>"1");
        if($VarShipmentId<>'') {
            $ArrWhere['shipmentid']		        = $VarShipmentId;
        }
        if($VarStatus<>'') {
            $ArrWhere['status']		            = $VarStatus;
        }
        $this->db->where($ArrWhere);
        $ArrShipmentOrderInfo			        = $this->db->get()->result_array();
        return $ArrShipmentOrderInfo;
    }

    function fnDelOrderShipmentDetails($orderId) {
        return $this->db->update(ZM_PRODUCT_SHIPMENT_ORDER_INFO,array('status'=>3,'dateupdated'=>date('Y-m-d H:i:s')),array('reforderid'=>$orderId));
    }

    function fnGetNotificationList($VarShipmentId='',$VarNotificationType='') {
        if($VarShipmentId>=1 && $VarNotificationType>=1) {
            $VarSqlShipmentNotificationList     = "SELECT n.contactdetails,n.contacttype FROM ".ZM_PRODUCT_SHIPMENT_NOTIFICATION_TYPE." as nt INNER JOIN ".ZM_PRODUCT_SHIPMENT_NOTIFICATION." as n ON n.shipmentid=nt.shipmentid where n.shipmentid=".$VarShipmentId." and nt.shipmentid=".$VarShipmentId." and nt.notificationtype=".$VarNotificationType." group by n.contactdetails,n.contacttype";
            $ObjResult					        = $this->db->query($VarSqlShipmentNotificationList);
            return $ObjResult->result_array();
        }
    }

    function fnGetDashboardShipmentStats($VarStartDate='',$VarEndDate='',$ArrShipmentStatus=array(),$VarCarrierId='') {
        if($VarStartDate<>'' && $VarEndDate<>'' && @$ArrShipmentStatus[0]>=1) {
            $VarWhere                           = "";
            if($VarCarrierId>=1) {
                $VarWhere                       = " and carrierid=".$VarCarrierId;
            }
            $VarSqlShipmentStats                = "select count(id) as cnt,shipmentstatus from ".ZM_PRODUCT_SHIPMENT." where shipmentstatus in(".implode(",",$ArrShipmentStatus).") and status=1 and (date(trackingdate)>='".$VarStartDate."' and date(trackingdate)<='".$VarEndDate."')".$VarWhere." group by shipmentstatus";
            $ObjResult					        = $this->db->query($VarSqlShipmentStats);
            return $ObjResult->result_array();
        }
    }

}