<?php 
/*
 * All Merchant Entity Logics like authentication,doLogin,Registeruser,forgot password are coming here.
*/
class Admin {

    public static function getValues() {
        return 1;
    }
    //function to get recent orders list list
    public static function  getrecentOrdersArray() {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'O.order_id,O.ordered_by ,O.store_id,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        //where condition
        $where                  =   " ";
        //search condition

        //group by
        $groupby                =   '';


        $limit                  =   "  0,5 ";
        //$limit                  =   '';

        $userDetails            = $dbh->getPagingData($fields,'orders AS O',$join,$where,$groupby,"DESC","O.order_date",$limit);

        return $userDetails;
    }
    public static function  getRecentMPSales() {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'O.order_id,O.ordered_by ,O.store_id,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        //where condition
        $where                  =   " WHERE done_through=2 and order_status>=7";
        //search condition

        //group by
        $groupby                =   '';


        $limit                  =   "  0,5 ";
        //$limit                  =   '';

        $saleDetails            = $dbh->getPagingData($fields,'orders AS O',$join,$where,$groupby,"DESC","O.order_date",$limit);
        foreach($saleDetails as $order) {
            $orderDetails                            =   new stdClass();
            $orderDetails->order_id                  =   Enums::ORDER_PREFIX.$order->order_id;
            $orderDetails->username                  =   $order->first_name." ".$order->last_name;
            $orderDetails->order_amount              =   $order->order_amount;
            $orderDetails->order_date                =   date("m/d/Y",strtotime($order->order_date));
            $recentSalesList[$loopCount]             =   $orderDetails;
            $loopCount++;
        }

        return $recentSalesList;
    }
    public static function  getRecentStores() {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'S.store_id,S.store_name,S.contact_email,DATE_FORMAT(S.created_date,"%Y-%m-%d") as created_date,S.merchant_id,M.first_name,M.last_name ';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN '.$dbh->tablePrefix.'merchant  AS M ON S.merchant_id=M.merchant_id';
        //where condition
        $where                  =   " WHERE S.status!=".Enums::INACTIVE_STORE_STATUS;
        //search condition

        //group by
        $groupby                =   '';


        $limit                  =   "  0,5 ";
        //$limit                  =   '';

        $storeDetailsArray            = $dbh->getPagingData($fields,'store AS S',$join,$where,$groupby,"DESC","S.created_date",$limit);

        foreach($storeDetailsArray as $store) {
            $storeDetails                            =   new stdClass();
            $storeDetails->store_name                =  $store->store_name;
            $storeDetails->merchant                  =   $store->first_name." ".$store->last_name;
            $storeDetails->contact_email              =   $store->contact_email;
            $storeDetails->created_date                =   date("m/d/Y",strtotime($store->created_date));
            $storeDetailsList[$loopCount]             =   $storeDetails;
            $loopCount++;
        }

        return $storeDetailsList;
    }
    //function to get sales count
    public static function  getFBSalesCount($startDate,$endDate) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'order_id';

        $where                  =   ' WHERE  	O.done_through=1 AND O.order_status>=7';
        if($startDate != '' || $endDate!='' ) {
            $where  .=  " AND DATE_FORMAT(order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(order_date,'%Y-%m-%d')< '".$endDate."'   " ;
        }

        $salesCount = $dbh->getPagingCount($fields,'orders AS O',$join,$where,$groupby,$orderType,$oderField);

        return $salesCount[0]->cnt;
    }
    //function to get sales count
    public static function  getMPSalesCount($startDate,$endDate) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'order_id';

        $where                  =   ' WHERE  	O.done_through=2 AND O.order_status>=7';
        if($startDate != '' || $endDate!='' ) {
            $where  .=  " AND DATE_FORMAT(order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(order_date,'%Y-%m-%d')< '".$endDate."'   " ;
        }

        $salesCount = $dbh->getPagingCount($fields,'orders AS O',$join,$where,$groupby,$orderType,$oderField);

        return $salesCount[0]->cnt;
    }
    //function to get sales count
    public static function  getMPRevenue($startDate,$endDate) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'sum(order_amount-discount_amount) as revenue';

        $where                  =   ' WHERE  	O.done_through=2 AND O.order_status>=7';
        if($startDate != '' || $endDate!='' ) {
            $where  .=  " AND DATE_FORMAT(order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(order_date,'%Y-%m-%d')< '".$endDate."'   " ;
        }

        $salesCount = $dbh->getPagingData($fields,'orders AS O',$join,$where,$groupby,$orderType,$oderField);

        return $salesCount[0]->revenue;
    }
    //function to get recent orders list list
    public static function  getMaxSoldProduct() {

        $dbh                    =   new db();


        $query= $dbh->execute("select count(product_id) as sum , product_id,done_through from (SELECT  I.product_id,O.order_id,O.done_through FROM `tbl_orders` AS O

                                left join tbl_ordered_items I ON O.order_id=I.order_id

                                 group by I.product_id ,O.done_through,O.order_id

                                ORDER BY `O`.`order_id` ASC ) as count group by product_id  order by sum desc limit 0,5
                ");
        $productDetails= $dbh->fetchAll($query);
        $loopCount=0;
        foreach($productDetails as $product) {
            $soldDetails                                =   new stdClass();
            $pName                                      =   Product::getProductName($product->product_id);
            $soldDetails->product_name                  =   substr($pName->product_name,0,30);
            if($product->sum)
                $soldDetails->sum                       =   $product->sum;
            else {
                $soldDetails->sum                       =   0;
            }

            $productCountArray                          =   Admin::getProductSoldCount($product->product_id,$storeId);
            foreach($productCountArray as $productCount) {
                if($productCount->done_through==1)
                    $soldDetails->storecount                    =   $productCount->sum;
                else if($productCount->done_through==2)
                    $soldDetails->mpcount                       =   $productCount->sum;
                else if($productCount->done_through==3)
                    $soldDetails->nwcount                       =   $productCount->sum;
            }

            if(!$soldDetails->storecount)
                $soldDetails->storecount                =   0;
            if(!$soldDetails->mpcount)
                $soldDetails->mpcount                   =   0;
            if(!$soldDetails->nwcount)
                $soldDetails->nwcount                   =   0;

            $productSoldDetails[$loopCount]                       =   $soldDetails;
            $loopCount++;

        }

        return $productSoldDetails;
    }
    //function to get recent orders list list
    public static function  getMPMaxSoldProduct() {

        $dbh                    =   new db();


        $query= $dbh->execute("select count(product_id) as sum , product_id from (SELECT  I.product_id,O.order_id FROM `tbl_orders` AS O

                                left join tbl_ordered_items I ON O.order_id=I.order_id

                                where O.done_through=2  group by I.product_id ,O.order_id

                                ORDER BY `O`.`order_id` ASC ) as count group by product_id  order by sum desc limit 0,5
                ");
        $productDetails= $dbh->fetchAll($query);
        $loopCount=0;
        foreach($productDetails as $product) {
            $soldDetails                                =   new stdClass();
            $pName                                      =   Product::getProductName($product->product_id);
            $soldDetails->product_name                  =   substr($pName->product_name,0,30);
            if($product->sum)
                $soldDetails->sum                       =   $product->sum;
            else {
                $soldDetails->sum                       =   0;
            }


            $productSoldDetails[$loopCount]                       =   $soldDetails;
            $loopCount++;

        }

        return $productSoldDetails;
    }
    //function to getsales count of prodcut in market place and store front
    public static function getProductSoldCount($productId = "") {
        $dbh                    =   new db();

        $query= $dbh->execute("select count(product_id) as sum , done_through from (SELECT  I.product_id,O.order_id,O.done_through FROM `tbl_orders` AS O

left join tbl_ordered_items I ON O.order_id=I.order_id

where  	 product_id=$productId group by O.done_through,O.order_id

ORDER BY `O`.`order_id` ASC ) as count group by done_through  order by sum desc limit 0,5
                ");
        $listProduct= $dbh->fetchAll($query);
        return $listProduct;

    }
    public static function getStoreSoldCount($storeId = "") {
        $dbh                    =   new db();

        $query= $dbh->execute("select count(product_id) as sum , done_through from (SELECT  I.product_id,O.order_id,O.done_through FROM `tbl_orders` AS O

left join tbl_ordered_items I ON O.order_id=I.order_id

where  	 store_id=$storeId group by O.done_through,O.order_id

ORDER BY `O`.`order_id` ASC ) as count group by done_through  order by sum desc limit 0,5
                ");
        $listProduct= $dbh->fetchAll($query);
        return $listProduct;

    }
    //function to get recent orders list list
    public static function  getMaxSoldStore() {

        $dbh                    =   new db();


        $query= $dbh->execute("select count(store_id) as sum , store_id,store_name,done_through,product_id from (SELECT  I.product_id,O.order_id,O.done_through,O.store_id,S.store_name FROM `tbl_orders` AS O

                              left join tbl_store as S on S.store_id=O.store_id  left join tbl_ordered_items I ON O.order_id=I.order_id

                                 group by I.product_id ,O.done_through,O.order_id

                                ORDER BY `O`.`order_id` ASC ) as count group by store_id  order by sum desc limit 0,5
                ");
        $storeDetailsArray= $dbh->fetchAll($query);

        $loopCount=0;
        foreach($storeDetailsArray as $store) {
            $storeDetails                            =   new stdClass();
            $storeDetails->store_name                =  $store->store_name;
            if($store->sum)
                $storeDetails->sum                       =   $store->sum;
            else {
                $storeDetails->sum                       =   0;
            }


            $productCountArray                          =   Admin::getStoreSoldCount($store->store_id);

            foreach($productCountArray as $productCount) {
                if($productCount->done_through==1)
                    $storeDetails->storecount                    =   $productCount->sum;
                else if($productCount->done_through==2)
                    $storeDetails->mpcount                       =   $productCount->sum;
                else if($productCount->done_through==3)
                    $storeDetails->nwcount                       =   $productCount->sum;
            }

            if(!$storeDetails->storecount)
                $storeDetails->storecount                =   0;
            if(!$storeDetails->mpcount)
                $storeDetails->mpcount                   =   0;
            if(!$storeDetails->nwcount)
                $storeDetails->nwcount                   =   0;
            $storeDetailsList[$loopCount]            =   $storeDetails;
            $loopCount++;

        }

        return $storeDetailsList;
    }

    public static function getStorePanelUrl($storeId) {




        $merchant =   Store::getMerchantFromStoreId($storeId);

        $merchantId = $merchant->merchant_id;
        if($storeId)
            return "<a href=\"".BASE_URL."admin/adminauthentication/$merchantId\" target=\"_blank\" >View Store</a>";



    }
    public static function whitelabeling($storeId) {




        $whitelabeling = Store::getStoreWhiteLabelFlag($storeId);

        if($storeId) {
            if($whitelabeling)
                return "<a href=\"".BASE_URL."admin/whitelabeling/$storeId\" id=\"whitelabel_".$storeId."_".$whitelabeling."\"  class=\"jqAddWhiteLabel\">Undo</a>";
            else
                return "<a href=\"".BASE_URL."admin/whitelabeling/$storeId\" id=\"whitelabel_".$storeId."_".$whitelabeling."\" class=\"jqAddWhiteLabel\">Whitelabel Store</a>";

        }

    }

    public static function getMerchantPanelUrl($merchantId) {

        $merchantDetails =   Merchant::getMerchantDetails($merchantId);


        $storeExist =   Store::getMerchantStoreId($merchantId);

        if($storeExist->store_id!="")
            return "<a href=\"".BASE_URL."admin/adminauthentication/$merchantId\"  target=\"_blank\">View Store</a>";



    }
    public static function setMerchantSessionFromAdmin($merchantId) {

        $merchantDetails =   Merchant::getMerchantDetails($merchantId);

        $session    =   new LibSession();

        $session->set("merchant_id", $merchantId);
        $session->set("admin_merchant_login", 1);
        $storeExist =   Store::getMerchantStoreId($merchantId);
        if($storeExist->store_id)
            $session->set("store_id", $storeExist->store_id);

        header('Location:'.BASE_URL."index/dashboard");
        exit;


    }
    public static function getOrderAmount($orderId='') {
        $dbh    =   new db();
        $fields  =   " order_amount, discount_amount";
        $where  =   " order_id='".$orderId."' ";
        $order = $dbh->selectResult("orders",$fields,$where);
        $totalAmount    =   $order[0]->order_amount-$order[0]->discount_amount;

        return $totalAmount;
    }
    public static function getReferenceId($orderId='') {
        $reference                  =   Enums::ORDER_PREFIX.$orderId;
        return $reference;
    }

    public static function getProductImage($productId='') {

        $dbh        = new Db();
        $table      = "products";
        $field      = "images,store_id";
        $where      = "product_id='".$productId."'";
        $listProduct   = $dbh->selectResult($table,$field,$where);

        $productImagesArray                                 =   json_decode($listProduct[0]->images);

        $defaultImageFileId                                 =    $productImagesArray->default;
        $productImagename                                   =   Product::getImageName($defaultImageFileId);

        //get store hash id
        $storehashId                                        =   Store::getStorehashId($listProduct[0]->store_id);

        return '<img src="'.BASE_URL.'project/files/stores/'.$storehashId.'/products/40_40/'.$productImagename.'"> ';
    }

    public static function changeMPProductStatus($values) {
        $id     =  $values['id'];
        $value  =  $values['value'];
        return     $url   = BASE_URL.'product/changeMPProductStatus?id='.$id.'&value='.$value;
    }
    public static function getOrderDetailLink($orderId) {

        return BASE_URL."cms?section=mporderdetails&order_id=$orderId";

    }public


































    static function getInvoiceDetailLink($invoiceId) {

        return BASE_URL."cms?section=invoicedetails&invoice_id=$invoiceId";

    }

    public static function getRecentMPSalesLink() {

        $url =  BASE_URL.'cms?section=marketplace_orders';
        return $url;


    }
    public static function getRecentStoresLink() {

        $url =  BASE_URL.'cms?section=store';
        return $url;

    }


    public static function changeUserStatus($values) {


        $id     =  $values['id'];
        $value  =  $values['value'];
        $userStatusUrl = BASE_URL.'store/changeuserstatus?id='.$id.'&value='.$value;
        return      $userStatusUrl;
    }
    public static function changeStoreStatus($values) {


        $id     =  $values['id'];
        $value  =  $values['value'];
        $storeStatusUrl = BASE_URL.'store/changestorestatus?id='.$id.'&value='.$value;
        return   $storeStatusUrl;
    }
    public static function changeMerchantStatus(
            $values) {


        $id     =  $values['id'];
        $value  =  $values['value'];
        $userStatusUrl = BASE_URL.'merchant/changemerchantstatus?id='.$id.'&value='.$value;
        return $userStatusUrl;
    }
    //function to get users list
    public static function  getFlaggedProductList( $oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$excludeUserStatus) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'F.product_id ,count(F.product_id) as count,P.product_name';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_products  AS P ON F.product_id=P.product_id';
        //where condition
        $where                  =   " ";
        //search condition
        if($searchColumn!="" ) {

            $where              .=   " AND  $searchColumn LIKE '%$searchText%'
                    ";
        }
        //group by

        $groupby                =   ' F.product_id';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';

        $flaggedproducts            = $dbh->getPagingData($fields,'flagged_products AS F',$join,$where,$groupby,$orderType,$oderField,$limit);

        return
                $flaggedproducts;
    }
    public static function  getFlaggedProductListCount(
            $oderField,$orderType,$searchColumn,$searchText) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' distinct(F.product_id) ';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_products  AS P ON F.product_id=P.product_id';
        //where condition
        $where                  =   " ";
        //search condition
        if($searchColumn!="" ) {

            $where              .=   " AND  $searchColumn LIKE '%$searchText%'
                    ";
        }
        //group by
        //
        $groupby                =   ' F.product_id';

        $flaggedproducts            = $dbh->getPagingCount($fields,'flagged_products AS F',$join,$where,$groupby,$orderType,$oderField,$limit);

        return $flaggedproducts[0
                ]->cnt;
    }
    //function to get users list
    public static function  getFlaggedProductDetails(
            $productId,$oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$excludeUserStatus) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'flagged_id,F.user_id, flagged_comment,flagged_date ,U.first_name ,U.last_name';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user  AS U ON F.user_id=U.user_id ';
        //where condition
        $where                  =   "  WHERE F.product_id=$productId";
        //search condition
        if($searchColumn!="" ) {

            $where              .=   " AND  $searchColumn LIKE '%$searchText%'
                    ";
        }
        //group by
        //
        //  $groupby                =   ' F.product_id';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';

        $flaggedproductsDetails            = $dbh->getPagingData($fields,'flagged_products AS F',$join,$where,$groupby,$orderType,$oderField,$limit);

        return
                $flaggedproductsDetails;
    }
    //function to get users list
    public static function  getFlaggedProductDetailsCount( $productId,$oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$excludeUserStatus) {


        $dbh                    =   new db();
        //selected fields
        $fields                 =   'flagged_id';
        //join with file table to get banner image
        $join                   =   '  ';
        //where condition
        $where                  =   "  WHERE F.product_id=$productId";
        //search condition
        if($searchColumn!="" ) {

            $where              .=   " AND  $searchColumn LIKE '%$searchText%'
                    ";
        }
        //group by
        //
        $groupby                =   ' F.product_id';



        $flaggedproducts            = $dbh->getPagingCount($fields,'flagged_products AS F',$join,$where,$groupby,$orderType,$oderField,$limit);

        return $flaggedproducts[0]->cnt;

    }
    // function to delete prodcuct option set
    public static function  deleteProduct(
            $productId) {

        $dbh        = new Db();
        $table      =   "products";
        $where      =   "product_id=$productId";
        $saveRes    = $dbh->deleteRecord($table,$where);
        return
                $saveRes;

    }
    // function to delete prodcuct option set
    public static function  deleteProductFlaggedComments(
            $productId) {

        $dbh        = new Db();
        $table      =   "flagged_products";
        $where      =   "product_id=$productId";
        $saveRes    = $dbh->deleteRecord($table,$where);
        return
                $saveRes;

    }
    // function to delete prodcuct option set
    public static function  deleteProductFlaggedComment(
            $flaggedId) {

        $dbh        = new Db();
        $table      =   "flagged_products";
        $where      =   "flagged_id=$flaggedId";
        $saveRes    = $dbh->deleteRecord($table,$where);
        return

                $saveRes;

    }

    //function to get users list
    public static function  getMessageList( $oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {


// if page is not set
        if($page=="")
            $page=1;
//finding start page
        $startPage=($page-1)*$perPageSize;
        $limit=" LIMIT $startPage,$perPageSize";
        $dbh                    =   new db();
        $query ="SELECT  m.message_id,m.sender_id as m_sender_id ,r.sender_id as r_sender_id,m.sender_type as m_sender_type,r.sender_type as r_sender_type,m.receiver_id as m_receiver_id,r.receiver_id as r_receiver_id,m.receiver_type as m_receiver_type,r.receiver_type as r_receiver_type,m.store_id,m.subject as m_subject,r.subject as r_subject,m.message as m_message,r.content as r_content,m.time as m_time,r.time as r_time,m.read_flag  FROM `tbl_messages` m
                                LEFT JOIN ( SELECT * FROM `tbl_message_replies` GROUP BY `message_id` ORDER BY `time` DESC  ) `r`
                                ON `r`.`message_id` = m.message_id
                                 ORDER BY m.time DESC $limit";
        $res = $dbh->execute($query);
        return $dbh->fetchAll($res);

    }
    public static function  getmessagelistcount( $oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {


        $dbh                    =   new db();
        $query ="SELECT  m.message_id,m.sender_id as m_sender_id ,r.sender_id as r_sender_id,m.sender_type as m_sender_type,r.sender_type as r_sender_type,m.receiver_id as m_receiver_id,r.receiver_id as r_receiver_id,m.receiver_type as m_receiver_type,r.receiver_type as r_receiver_type,m.store_id,m.subject as m_subject,r.subject as r_subject,m.message as m_message,r.content as r_content,m.time as m_time,r.time as r_time,m.read_flag  FROM `tbl_messages` m
                                LEFT JOIN ( SELECT * FROM `tbl_message_replies` GROUP BY `message_id` ORDER BY `time` DESC  ) `r`
                                ON `r`.`message_id` = m.message_id
                                 ORDER BY m.time DESC";
        $res = $dbh->execute($query);
        $row = $dbh->fetchAll($res);
        return count($row);



    }



    public static function getAdminUserName($adminId) {

        $dbh        =   new Db();
        $query ="SELECT  username FROM cms_users       where  id='".$adminId."'";
        $res = $dbh->execute($query);
        return $adminName =  $dbh->fetchOne($res);


    } // End Function
    public static function getAdminUserId($username) {

        $dbh        =   new Db();
        $query ="SELECT  id FROM cms_users       where  username='".$username."'";
        $res = $dbh->execute($query);
        return $adminId =  $dbh->fetchOne($res);


    } // End Function

    public static function sendMessage( $messageDetails) {

        $dbh    =   new Db();
        if(!empty($messageDetails)) {

            $messageId = $dbh->addFields('messages',$messageDetails);
            return $messageId;

        }

    }
    public static function sendReply( $messageDetails) {

        $dbh    =   new Db();
        if(!empty($messageDetails)) {

            $replyId = $dbh->addFields('message_replies',$messageDetails);
            return $replyId;

        }

    }

    public static function getMerchantList( $text) {
        $dbh                    =   new db();
        $query ="SELECT  merchant_id as id ,CONCAT(first_name,last_name)as label  FROM ".$dbh->tablePrefix."merchant  where first_name like '%$text%'  ";
        $res = $dbh->execute($query);
        return $dbh->fetchAll($res);


    }

    public static function getMerchantIdFromMessage($messageId) {
        $dbh    =   new db();
        $fields  =   " sender_id,sender_type,receiver_id,receiver_type ";
        $where  =   "  message_id='".$messageId."'";
        $content = $dbh->selectResult("messages",$fields,$where);
        if($content[0]->sender_type==2)
            return $content[0]->sender_id;
        if($content[0]->receiver_type==2)
            return $content[0]->receiver_id;

    }
    //sending mails after successful creation of store
    public static function  messageCreationMail($messageId,$receiverId) {
        $db = new Db();
        $session    =   new LibSession();
        $merchantId =   $session->get("merchant_id");
        $merchantDetails    =  Merchant::getMerchantDetails($receiverId);
        $storeDetails                       =   Store::getStoreDetails($storeId, $merchantId);
        $merchantEmail      =   $storeDetails->contact_email;
        $adminName                         =   $session->get("cms_username");
        $adminDetails=Admin::getAdminUserDetails($adminName);
        $adminEmail      =   $adminDetails->contact_email;


        $url = "<a href=\"".BASE_URL."cms?section=messages#$messageId\">here</a>";
        $replaceClassParameters = array("{here}" => $url,
                "merchantname"      => ucwords($merchantDetails->first_name.' '.$merchantDetails->last_name));

        Utils::sendMessageMail($merchantEmail, 'merchant_message_creation', $replaceClassParameters,$adminEmail,$merchantEmail);
    }
    //sending mails after successful creation of store
    public static function  messageReplyMail($messageId,$receiverId) {

        $db = new Db();
        $session                            =   new LibSession();
        $merchantId =   $session->get("merchant_id");
        $merchantDetails    =  Merchant::getMerchantDetails($receiverId);
        $storeDetails                       =   Store::getStoreDetails($storeId, $merchantId);
        $merchantEmail      =   $storeDetails->contact_email;
        $adminName                         =   $session->get("cms_username");
        $adminDetails=Admin::getAdminUserDetails($adminName);
        $adminEmail      =   $adminDetails->contact_email;
        $url                                =   "<a href=\"".BASE_URL."cms?section=message_reply&message_id=$messageId\">here</a>";
        $replaceClassParameters             =   array("{here}" => $url,
                "{merchantname}"      => ucwords($merchantDetails->first_name.' '.$merchantDetails->last_name));

        Utils::sendMessageMail($merchantEmail, 'merchant_message_reply', $replaceClassParameters,$adminEmail,$merchantEmail);
    }
    public static function getAdminUserDetails($username) {

        $dbh 	 = new Db();

        $res = $dbh->execute("SELECT id,type,role_id,email FROM cms_users WHERE username='".$dbh->escapeString($username)."' ");
        $user = $dbh->fetchRow($res);
        return $user;
    }
    public static function  getMessageListReplies($messageId) {


        $dbh                    =   new db();
        $query ="SELECT  r.sender_id as r_sender_id,r.sender_type as r_sender_type,r.receiver_id as r_receiver_id,r.receiver_type as r_receiver_type,r.subject as r_subject,r.content as r_content,r.time as r_time  FROM
                                `tbl_message_replies` as r
                                where r.message_id=$messageId
                                ORDER BY r.time DESC";
        $res = $dbh->execute($query);
        return $dbh->fetchAll($res);

    }
    public static function getMessageDetails($messageId) {
        $dbh    =   new db();
        $fields  =   " sender_id,sender_type,receiver_id,receiver_type,subject,message";
        $where  =   "  message_id='".$messageId."'";
        $content = $dbh->selectResult("messages",$fields,$where);
        return $content;

    }

    
       public static function deleteNetworker($networkerId) {
       $params['status'] = 4;
        $dbh    =   new Db();
        if(!empty($params)) {
            if($networkerId) {
                 $dbh->updateFields('user',$params," user_id=".$networkerId);
            }

             return array('status'=>'success');

        }

    }
    

    public static function getInvoiceReferenceId($invoiceId='') {
        $reference                  =   Enums::INVOICE_PREFIX.$invoiceId;
        return $reference;
    }
    public static function getInvoiceMerchant($invoiceId='') {
        $dbh            =   new db();
        $fields         =   "store_id";
        $where          =   " invoice_id='".$invoiceId."'";
        $storeDetails   = $dbh->selectRecord("invoices",$fields,$where);
        $storeId = $storeDetails->store_id;
        $fields         =   "merchant_id";
        $where          =   " store_id='".$storeId."'";
        $storeDetails   = $dbh->selectRecord("store",$fields,$where);
        $merchantId  = $storeDetails->merchant_id;

        $merchantDetails    =  Merchant::getMerchantDetails($merchantId);
        return $merchantDetails->first_name." ".$merchantDetails->last_name;
    }
    public static function getInvoicePaidDate($invoiceId='') {
        $dbh            =   new db();
        $fields         =   "paid_date";
        $where          =   " invoice_id='".$invoiceId."'";
        $payementDetails   = $dbh->selectRecord("invoices",$fields,$where);
        if(date("m/d/Y",strtotime($payementDetails->paid_date))=="01/01/1970")
            $invoiceDetails->paid_date = "-";
        else
            $invoiceDetails->paid_date                      =   date("m/d/Y",strtotime($payementDetails->paid_date));
        return $invoiceDetails->paid_date;
    }
    public static function getInvoiceNetworker($invoiceId='') {
        $dbh            =   new db();
        $fields         =   "store_id";
        $where          =   " invoice_id='".$invoiceId."'";
        $storeDetails   = $dbh->selectRecord("invoices",$fields,$where);
        $storeId = $storeDetails->store_id;
        $fields         =   "networker_id";
        $where          =   " shelf_id='".$storeId."'";
        $shelfDetails   = $dbh->selectRecord("networker_shelf",$fields,$where);
        $networkerId  = $shelfDetails->networker_id;
        $fields         =   "first_name,last_name";
        $where          =   " user_id='".$networkerId."'";
        $userDetails   = $dbh->selectRecord("user",$fields,$where);

        return $userDetails->first_name." ".$userDetails->last_name;
    }



    public static function getNWInvoiceDetailLink($invoiceId) {

        return BASE_URL."cms?section=nwinvoicedetails&invoice_id=$invoiceId";

    }
    //function to get recent orders list list
    public static function  getInvoiceArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'invoice_id,type ,store_name,I.store_id,amount,DATE_FORMAT(I.created_date,"%Y-%m-%d") as created_date,DATE_FORMAT(due_date,"%Y-%m-%d") as due_date, DATE_FORMAT(paid_date,"%Y-%m-%d") as paid_date,payment_status';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN   tbl_store  AS S ON I.store_id=S.store_id';
        $join                   .=   ' LEFT JOIN   tbl_merchant  AS M ON S.merchant_id=M.merchant_id';
        //where condition
        $where                  =   " WHERE  type=1";
        if($searchColumn!="" ) {
            if($searchColumn=="invoice_id") {
                $searchText = str_replace(Enums::INVOICE_PREFIX, "", $searchText);
                $where              .=   " AND  invoice_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="merchant_id")
                $where              .=   " AND ( M.first_name LIKE '%$searchText%'
                    OR M.last_name LIKE '%$searchText%'
                    OR CONCAT( M.first_name, ' ', M.last_name ) LIKE '%$searchText%' )";
            else if($searchColumn=="payment_status" && strtolower($searchText)=="paid")
                $where              .=   " AND  payment_status=1 ";
            else  if($searchColumn=="payment_status" && strtolower($searchText)!="paid")
                $where              .=   " AND  payment_status!=1 ";
            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(I.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="created_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }

        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "store_name";
        if($orderField=="merchant_id")
            $orderField = "M.first_name";
        $orderDetails            = $dbh->getPagingData($fields,'invoices AS I',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    //function to get recent orders list list
    public static function  getInvoiceCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'invoice_id';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN   tbl_store  AS S ON I.store_id=S.store_id';
        $join                   .=   ' LEFT JOIN   tbl_merchant  AS M ON S.merchant_id=M.merchant_id';
        //where condition
        $where                  =   " WHERE   type=1";
        if($searchColumn!="" ) {
            if($searchColumn=="invoice_id") {
                $searchText = str_replace(Enums::INVOICE_PREFIX, "", $searchText);
                $where              .=   " AND  invoice_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="merchant_id")
                $where              .=   " AND ( M.first_name LIKE '%$searchText%'
                    OR M.last_name LIKE '%$searchText%'
                    OR CONCAT( M.first_name, ' ', M.last_name ) LIKE '%$searchText%' )";
            else if($searchColumn=="payment_status" && strtolower($searchText)=="paid")
                $where              .=   " AND  payment_status=1 ";
            else  if($searchColumn=="payment_status" && strtolower($searchText)!="paid")
                $where              .=   " AND  payment_status!=1 ";
            else  if($startDate!="" && $endDate!="" ) {

                $where  .=   " AND  DATE_FORMAT(I.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%Y-%m-%d')<= '".$endDate."' ";
            }
            else if($searchColumn!="created_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }

        //group by
        $groupby                =   '';

        //perpagesize


        $invoiceCount            = $dbh->getPagingCount($fields,'invoices AS I',$join,$where,$groupby,"","",$limit);

        return $invoiceCount[0]->cnt;
    }
    public static function  getNetworkerInvoiceArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'invoice_id,type ,I.store_id,user_id,amount,DATE_FORMAT(I.created_date,"%Y-%m-%d") as created_date,DATE_FORMAT(due_date,"%Y-%m-%d") as due_date, DATE_FORMAT(paid_date,"%Y-%m-%d") as paid_date,payment_status';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN    tbl_networker_shelf   AS S ON I.store_id=S.shelf_id';
        $join                   .=   ' LEFT JOIN   tbl_user   AS M ON S.networker_id=M.user_id';
        //where condition
        $where                  =   " WHERE  type=2";
        if($searchColumn!="" ) {
            if($searchColumn=="invoice_id") {
                $searchText = str_replace(Enums::INVOICE_PREFIX, "", $searchText);
                $where              .=   " AND  invoice_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="merchant_id")
                $where              .=   " AND  M.first_name LIKE '%$searchText%' ";
            else if($searchColumn=="payment_status" && strtolower($searchText)=="paid")
                $where              .=   " AND  payment_status=1 ";
            else  if($searchColumn=="payment_status" && strtolower($searchText)!="paid")
                $where              .=   " AND  payment_status!=1 ";
            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(I.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="created_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }

        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "store_name";
        if($orderField=="merchant_id")
            $orderField = "M.first_name";
        $orderDetails            = $dbh->getPagingData($fields,'invoices AS I',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    //function to get recent orders list list
    public static function  getNetworkerInvoiceCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'invoice_id';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN   tbl_store  AS S ON I.store_id=S.store_id';
        $join                   .=   ' LEFT JOIN   tbl_merchant  AS M ON S.merchant_id=M.merchant_id';
        //where condition
        $where                  =   " WHERE   type=2";
        if($searchColumn!="" ) {
            if($searchColumn=="invoice_id") {
                $searchText = str_replace(Enums::INVOICE_PREFIX, "", $searchText);
                $where              .=   " AND  invoice_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="merchant_id")
                $where              .=   " AND  M.first_name LIKE '%$searchText%' ";
            else if($searchColumn=="payment_status" && strtolower($searchText)=="paid")
                $where              .=   " AND  payment_status=1 ";
            else  if($searchColumn=="payment_status" && strtolower($searchText)!="paid")
                $where              .=   " AND  payment_status!=1 ";
            else  if($startDate!="" && $endDate!="" ) {

                $where  .=   " AND  DATE_FORMAT(I.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%Y-%m-%d')<= '".$endDate."' ";
            }
            else if($searchColumn!="created_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }

        //group by
        $groupby                =   '';

        //perpagesize


        $invoiceCount            = $dbh->getPagingCount($fields,'invoices AS I',$join,$where,$groupby,"","",$limit);

        return $invoiceCount[0]->cnt;
    }
    public static function  getOrdersArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        //$where                  =   " WHERE  done_through=2";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";

        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getPagingData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }


    public static function  getMPOrdersArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=2";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND ( U.first_name LIKE '%$searchText%'
                    OR U.last_name LIKE '%$searchText%'
                    OR CONCAT( U.first_name, ' ', U.last_name ) LIKE '%$searchText%' )";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getPagingData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    //function to get recent orders list list
    public static function  getOrderCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'order_id';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=1";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize


        $orderCount            = $dbh->getPagingCount($fields,'orders AS O',$join,$where,$groupby,"","",$limit);

        return $orderCount[0]->cnt;
    }
    //function to get recent orders list list
    public static function  getMPOrderCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'order_id';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=2";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND ( U.first_name LIKE '%$searchText%'
                    OR U.last_name LIKE '%$searchText%'
                    OR CONCAT( U.first_name, ' ', U.last_name ) LIKE '%$searchText%' )";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize


        $orderCount            = $dbh->getPagingCount($fields,'orders AS O',$join,$where,$groupby,"","",$limit);

        return $orderCount[0]->cnt;
    }
    public static function  getNetworkerOrdersReportsArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=3";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize

        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getAllData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField);

        return $orderDetails;
    }
    public static function  getMPOrdersReportsArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=2";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }

        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize

        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getAllData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField);

        return $orderDetails;
    }
    public static function  getOrdersReportsArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=1";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }

        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize

        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getAllData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField);

        return $orderDetails;
    }
    public static function  getNetworkerOrdersArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   ' O.order_id,O.ordered_by ,O.store_id,store_name,U.first_name,U.last_name,O.order_status,DATE_FORMAT(O.order_date,"%Y-%m-%d") as order_date,O.order_amount as order_amount,done_through,O.discount_amount as discount_amount,(order_amount - discount_amount) as total';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=3";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="total")
            $orderField = "total";
        if($orderField=="ordered_by")
            $orderField = "U.first_name";
        $orderDetails            = $dbh->getPagingData($fields,'orders  AS O',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    //function to get recent orders list list
    public static function  getNetworkerOrderCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'order_id';
        //join with file table to get banner image
        $join                   =   ' LEFT JOIN  tbl_user AS U ON O.ordered_by=U.user_id';
        $join                   .=   ' LEFT JOIN   tbl_store   AS S ON S.store_id=O.store_id';
        //where condition
        $where                  =   " WHERE  done_through=3";
        if($searchColumn!="" ) {
            if($searchColumn=="order_id") {
                $searchText = str_replace(Enums::ORDER_PREFIX, "", $searchText);
                $where              .=   " AND  order_id LIKE '%$searchText%' ";
            }
            if($searchColumn=="store_id")
                $where              .=   " AND  store_name LIKE '%$searchText%' ";
            else if($searchColumn=="ordered_by")
                $where              .=   " AND  U.first_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(O.order_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(O.order_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="order_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        $where .=" AND O.order_status!=".Enums::DELETED_ORDER_STATUS." AND O.order_status!=".Enums::FAILED_ORDER_STATUS." ";
        //group by
        $groupby                =   '';

        //perpagesize


        $orderCount            = $dbh->getPagingCount($fields,'orders AS O',$join,$where,$groupby,"","",$limit);

        return $orderCount[0]->cnt;
    }

    public static function formUrl($request,$sectionConfig) {

        $url   = BASE_URL."cms?section=".$request['section'];
        if($request['page']!="")  $url    .=  "&page=".$request['page'];
        // if($request['orderField']!="")  $url    .=  "&orderField=".$request['orderField']."&orderType=".$request['orderType'];
        if($request['searchField']!="") $url    .=  "&searchField=".$request['searchField']."&searchText=".$request['searchText'];
        if($request['action']=="edit")  $url     .=  "&action=edit&".$sectionConfig->keyColumn."=".$request[$sectionConfig->keyColumn];
        if($request['action']=="add")  $url     .=  "&action=add";
        if($request['parent_section']!="") $url .=  "&parent_section=".$request['parent_section'];
        if($request['parent_section']!="") $url .=  "&parent_id=".$request['parent_id'];

        return $url;
    }
// function to display pagination
    public static function pagination($total, $perPage  =   5, $url  =   '',$page) {



        $adjacents          =   "2";
        $page               =   ($page == 0 ? 1 : $page);
        $start              =   ($page - 1) * $perPage;
        $prev               =   $page - 1;
        $next               =   $page + 1;
        $lastPage           =   ceil($total/$perPage);
        $lpm1               =   $lastPage - 1;
        $pagination         =   "";
        if($lastPage > 1) {
            $pagination     .=  "<ul class='pagination'>";
            if($page>1)
                $pagination .=  "<li><a href='{$url}page=$prev'>&laquo;</a></li>";
            if ($lastPage < 5 + ($adjacents * 2)) {
                for ($counter = 1;
                $counter <= $lastPage;
                $counter++) {
                    if ($counter == $page)
                        $pagination .= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination .= "<li><a href='{$url}page=$counter'>$counter</a></li>";
                }
            }
            elseif($lastPage > 5 + ($adjacents * 2)) {
                if($page < 1 + ($adjacents * 2)) {
                    for ($counter = 1;
                    $counter < 4 + ($adjacents * 2);
                    $counter++) {
                        if ($counter == $page)
                            $pagination .= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination .= "<li><a href='{$url}page=$counter'>$counter</a></li>";
                    }
                    $pagination .= "<li><a class='current'>..</a></li>";
                    $pagination .= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                    $pagination .= "<li><a href='{$url}page=$lastPage'>$lastPage</a></li>";
                }
                elseif($lastPage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
                    $pagination .= "<li><a href='{$url}page=1'>1</a></li>";
                    $pagination .= "<li><a href='{$url}page=2'>2</a></li>";
                    $pagination .= "<li><a href='#'>..</a></li>";
                    for ($counter = $page - $adjacents;
                    $counter <= $page + $adjacents;
                    $counter++) {
                        if ($counter == $page)
                            $pagination .= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination .= "<li><a href='{$url}page=$counter'>$counter</a></li>";
                    }
                    $pagination .= "<li><a class='current'>..</a></li>";
                    $pagination .= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                    $pagination .= "<li><a href='{$url}page=$lastPage'>$lastPage</a></li>";
                }
                else {
                    $pagination .= "<li><a href='{$url}page=1'>1</a></li>";
                    $pagination .= "<li><a href='{$url}page=2'>2</a></li>";
                    $pagination .= "<li><a href='#'>..</a></li>";
                    for ($counter = $lastPage - (2 + ($adjacents * 2));
                    $counter <= $lastPage;
                    $counter++) {
                        if ($counter == $page)
                            $pagination .= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination .= "<li><a href='{$url}page=$counter'>$counter</a></li>";
                    }
                }
            }
            if ($page < $counter - 1) {
                $pagination .= "<li><a href='{$url}page=$next'>&raquo;</a></li>";

            }

            $pagination .='<li class="is-padded">Page<input type="text" name="goto" class="input goto is-padded" value="'.$page.'"> of  '.$lastPage.'</li>';
            $pagination .= "</ul>\n";
        }
//echo $pagination;exit;

        return $pagination;


    }
    public static function changeNetworkerInvoicetatus($invoiceID,$invoiceDetails) {
        $dbh    =   new Db();
        if(!empty($invoiceDetails)) {
            if($invoiceID > 0) {
                $saveRes = $dbh->updateFields('invoices',$invoiceDetails," invoice_id=".$invoiceID);

                return $saveRes;
            }
        }

    }

    public static function formatAdminPanelDate($date,$time = false) {
        if($time)
            return  $date && $date!='0000-00-00 00:00:00'?date('m/d/Y H:i:s',strtotime($date)):'--';
        else
            return  $date && $date!='0000-00-00 00:00:00'?date('m/d/Y',strtotime($date)):'--';

    }
    public static function getShelfDetails($shelfId = "") {
        $dbh        = new Db();
        $table      = "networker_shelf";
        $field      = "*";
        $where      = "shelf_id='".$shelfId."'";
        $shelfDetails   = $dbh->selectRecord($table,$field,$where);
        return $shelfDetails;
    }

    public static function  getProductVisitList( $oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {


// if page is not set
        if($page=="")
            $page=1;
//finding start page
        $startPage=($page-1)*$perPageSize;
        $limit=" LIMIT $startPage,$perPageSize";
        $dbh                    =   new db();
        $query ="SELECT sum(visit_count_mp) as visit_count_mp,sum(visit_count_fb) as visit_count_fb,sum(visit_count_nw) as visit_count_nw,sum(visit_count_mp+visit_count_fb+visit_count_nw) as total,P.product_name,S.store_name,S.store_id,V.product_id FROM `tbl_products_visits_db`  AS V
                    LEFT JOIN tbl_products as P ON P.product_id=V.product_id
                    LEFT JOIN tbl_store as S ON P.store_id=S.store_id 
                              group by V.product_id order by total DESC  $limit";
        $res = $dbh->execute($query);
        return $dbh->fetchAll($res);

    }
    public static function getProductVisitCount() {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'distinct product_id';
        //join with file table to get banner image
        $join                   =   '  ';
        //where condition
        $where                  =   "  ";
        //search condition
        if($searchColumn!="" ) {

            $where              .=   " AND  $searchColumn LIKE '%$searchText%'
                    ";
        }
        //group by
        //
        $groupby                =   '';



        $visitCount            = $dbh->getPagingCount($fields,'products_visits_db AS V',$join,$where,$groupby,$orderType,$oderField,$limit);

        return $visitCount[0]->cnt;
    }
    public static function getProductImageThumb($productId='') {
        $dbh        = new Db();
        $table      = "products";
        $field      = "images,store_id";
        $where      = "product_id='".$productId."'";
        $listProduct   = $dbh->selectResult($table,$field,$where);

        $productImagesArray                                 =   json_decode($listProduct[0]->images);

        $defaultImageFileId                                 =    $productImagesArray->default;
        $productImagename                                   =   Product::getImageName($defaultImageFileId);
        //get store hash id
        $storehashId                                        =   Store::getStorehashId($listProduct[0]->store_id);

        return '<img src="'.BASE_URL.'project/files/stores/'.$storehashId.'/products/40_40/'.$productImagename.'"> ';
    }

    public static function  getTopsellingProducts($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";

        $query= $dbh->execute("select count(product_id) as sum , product_id,done_through,store_id from (SELECT  I.product_id,O.order_id,O.done_through,O.store_id FROM `tbl_orders` AS O

                                left join tbl_ordered_items I ON O.order_id=I.order_id

                                 group by I.product_id ,O.done_through,O.order_id

                                ORDER BY O.order_id ASC ) as count group by product_id  order by sum desc limit $limit
                ");
        $productDetails= $dbh->fetchAll($query);
        $loopCount=0;
        foreach($productDetails as $product) {
            if($product->product_id) {
                $soldDetails                                =   new stdClass();
                $pName                                      =   Product::getProductName($product->product_id);
                $soldDetails->product_name                  =   substr($pName->product_name,0,30);
                if($product->sum)
                    $soldDetails->sum                       =   $product->sum;
                else {
                    $soldDetails->sum                       =   0;
                }
                $soldDetails->product_id                    =   $product->product_id;
                $soldDetails->store_id                    =   $product->store_id;
                $productCountArray                          =   Admin::getProductSoldCount($product->product_id,$storeId);


                $soldDetails->storecount                    =   $productCountArray[0]->sum;
                $soldDetails->mpcount                       =   $productCountArray[1]->sum;
                $soldDetails->nwcount                       =   $productCountArray[2]->sum;
                if(!$soldDetails->storecount)
                    $soldDetails->storecount                =   0;
                if(!$soldDetails->mpcount)
                    $soldDetails->mpcount                   =   0;
                if(!$soldDetails->nwcount)
                    $soldDetails->nwcount                   =   0;

                $productSoldDetails[$loopCount]                       =   $soldDetails;
                $loopCount++;
            }
        }

        return $productSoldDetails;
    }
    public static function  getTopsellingProductCount() {

        $dbh                    =   new db();


        $query= $dbh->execute("select count(product_id) as sum , product_id,done_through from (SELECT  I.product_id,O.order_id,O.done_through FROM `tbl_orders` AS O

                                left join tbl_ordered_items I ON O.order_id=I.order_id

                                 group by I.product_id ,O.done_through,O.order_id

                                ORDER BY O.order_id ASC ) as count group by product_id  order by sum desc
                ");
        $productDetails= $dbh->fetchAll($query);
        $loopCount=0;


        return count($productDetails);
    }
    public static function  getTopStores($type,$orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        if($type=="sales" || $type=="") {
            $query= $dbh->execute("select count(order_id) as count,O.store_id,store_name,S.merchant_id,first_name,last_name,email,created_date,contact_email  FROM `tbl_orders` AS O
                left join tbl_store S on S.store_id=O.store_id
                  left join tbl_merchant M on S.merchant_id=M.merchant_id
                                where O.store_id!=0
                                group by store_id
                                 order by count desc limit $limit
                    ");
        }
        if($type=="visits" ) {
            $query= $dbh->execute("select count(visit_id) as count,item_id as store_id,S.merchant_id,store_name,first_name,last_name,email,created_date,contact_email  FROM `tbl_site_visits` AS V
                left join tbl_store S on S.store_id=V.item_id
                  left join tbl_merchant M on S.merchant_id=M.merchant_id
                                where done_through=3 OR  done_through=1
                                group by item_id
                                 order by count desc limit $limit
                    ");
        }
        $storeDetails= $dbh->fetchAll($query);



        return $storeDetails;
    }
    public static function  getTopStoresCount($type,$orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        if($searchColumn=="sales" || $searchColumn=="") {
            $query= $dbh->execute("select count(order_id) as count,O.store_id,store_name,S.merchant_id,first_name,last_name,email  FROM `tbl_orders` AS O
                left join tbl_store S on S.store_id=O.store_id
                  left join tbl_merchant M on S.merchant_id=M.merchant_id
                                 where O.store_id!=0
                                group by store_id
                                 order by count desc
                    ");
        }
        if($type=="visits" ) {
            $query= $dbh->execute("select count(visit_id) as count,item_id as store_id,S.merchant_id,store_name,first_name,last_name,email  FROM `tbl_site_visits` AS V
                left join tbl_store S on S.store_id=V.item_id
                  left join tbl_merchant M on S.merchant_id=M.merchant_id
                                where done_through=3 OR  done_through=1
                                group by item_id
                                 order by count desc 
                    ");
        }
        $networkerDetails= $dbh->fetchAll($query);



        return count($networkerDetails);
    }
    public static function  getTopNetworkers($type,$orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        if($type=="sales" || $type=="") {
            $query= $dbh->execute("select count(order_id) as count,benificary_id,networker_id,namespace,first_name,last_name,email  FROM `tbl_orders` AS O
                left join tbl_networker_shelf on benificary_id=shelf_id
                  left join tbl_user on networker_id=user_id
                                where O.done_through=3
                                group by benificary_id
                                 order by count desc limit $limit
                    ");
        }
        if($type=="visits" ) {
            $query= $dbh->execute("select count(visit_id) as count,item_id as benificary_id,networker_id,namespace,first_name,last_name,email  FROM `tbl_site_visits` AS V
                left join tbl_networker_shelf on item_id=shelf_id
                  left join tbl_user U on networker_id=U.user_id
                                where done_through=3
                                group by item_id
                                 order by count desc limit $limit
                    ");
        }
        $networkerDetails= $dbh->fetchAll($query);



        return $networkerDetails;
    }

    public static function  getNetworkersCount($type,$orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        if($searchColumn=="sales" || $searchColumn=="") {
            $query= $dbh->execute("select count(order_id) as count,benificary_id,networker_id,namespace,first_name,last_name  FROM `tbl_orders` AS O
                left join tbl_networker_shelf on benificary_id=shelf_id
                  left join tbl_user on networker_id=user_id
                                where O.done_through=3
                                group by benificary_id
                                 order by count desc
                    ");
        }
        if($type=="visits" ) {
            $query= $dbh->execute("select count(visit_id) as count,item_id as benificary_id,networker_id,namespace,first_name,last_name,email  FROM `tbl_site_visits` AS V
                left join tbl_networker_shelf on item_id=shelf_id
                  left join tbl_user U on networker_id=U.user_id
                                where done_through=3
                                group by item_id
                                 order by count desc 
                    ");
        }
        $networkerDetails= $dbh->fetchAll($query);



        return count($networkerDetails);
    }
    public static function plotSiteTrafficGraph($fromDate,$toDate) {
        $FC = new FusionCharts("MSLine","520","250");
        # Set Relative Path of swf file.
        $FC->setSwfPath(BASE_URL."project/lib/fusioncharts/FusionCharts/");
        #Store the chart attributes in a variable
        $strParam="YAxisMaxValue=10;YAxisMinValue=0;showValues=0;showNames=1;numdivlines=1;canvasBorderThickness=1;showLegend=1;formatNumber=0; formatNumberScale=0;";
        $timeFlag    =    Graph::getTimeflag($fromDate,$toDate);
        # Create combination chart object


        # Set chart attributes
        $FC->setChartParams($strParam);

// setting x-axis values
        Graph::setMSIndustry($FC,strtotime($fromDate),strtotime($toDate),$timeFlag);

        $FC->addDataset("Site Traffic","parentYAxis=P;color=#1E659E;");
        self::populateSiteTrafficGraph($FC,'', strtotime($fromDate),strtotime($toDate),$timeFlag);
        $FC->renderChart();


    }
    function getTimeflag($fromDate='',$toDate='') {
        $timeFlag=0;
        $loopCounter=0;
        $loopValue='';
        $timeDuration=strtotime($toDate)-strtotime($fromDate);

        $timeDuration=$timeDuration/(24*60*60);

        if($timeDuration<=7) {
            $loopCounter=$timeDuration;
            $loopValue='Day';
            $timeFlag=1;
        }
        elseif($timeDuration>7 && $timeDuration<=28) {
            $loopCounter=$timeDuration/7;
            $loopValue='Week';
            $timeFlag=2;
        }
        else if($timeDuration>28 && $timeDuration<=365) {
            $loopCounter=$timeDuration/30;
            $loopValue='Month';
            $timeFlag=3;
        }
        else {

            $loopValue='Year';
            $timeFlag=4;

        }
        return $timeFlag;
    }
    function populateSiteTrafficGraph($FC,$type,$fromDate='',$toDate='',$timeFlag='',$shelfId) {

        $startDate=$fromDate;
        $endDate=$fromDate;
        $flag=0;


        $counter=0;

        while($endDate<=$toDate) {
            if($timeFlag==1) {
                $startDate=$endDate;
                $endDate=$startDate+(24*60*60);
                $dateStr=date("M d, Y",$startDate);
                $counter++;
            }
            elseif($timeFlag==2) {
                $startDate=$endDate;
                $endDate=$startDate+(7*24*60*60);
                $dateStr=date("d M",$startDate)."-".date("d M",$endDate);
                $counter++;
            }
            elseif($timeFlag==3) {
                $startDate=$endDate;
                $endDate=mktime(0,0,0,date("m",$startDate)+1,1,date("Y",$startDate));//$startDate+(30*24*60*60);
                $dateStr=date("F Y",$startDate);
                $counter++;
            }
            else {
                $startDate=$endDate;
                $endDate=mktime(0,0,0,date("m",$startDate),date("d",$startDate),date("Y",$startDate)+1);//$startDate+(30*24*60*60);
                $dateStr=date("Y",$startDate);
                $counter++;

            }


            $val= Admin::getSiteTraffic(date("Y-m-d",$startDate),date("Y-m-d",$endDate));

            if($val=="NULL" || $val=="") {
                $val=0;
            }
//            /$val=number_format($val,0,'.',',');
            if($type=="sales") {
                $toolText="Sales On $dateStr";
            }
            else if($type=="Benchmark Sales") {
                $toolText="Twitter On $dateStr";
            }

            $FC->addChartData((int)$val,"toolText= $toolText");




        }



    }
    public static function  getSiteTraffic($startDate,$endDate) {

        $dbh                    =   new db();

        $where  =   "   where V.done_through=3  AND  DATE_FORMAT(visited_time,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(visited_time,'%Y-%m-%d')<= '".$endDate."' ";
        $query= $dbh->execute("select count(visit_id) as count  FROM `tbl_site_visits` AS V
                where DATE_FORMAT(visited_time,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(visited_time,'%Y-%m-%d')< '".$endDate."' 
                               
                               
                    ");


        $trafficDetails= $dbh->fetchAll($query);



        return $trafficDetails[0]->count;
    }
    public static function getProductPrice($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        return $productDetails->price;

    }
    public static function getProductDescription($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        return $productDetails->description;

    }
    public static function getProductStore($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        $store =  Store::getStoreDetailsFromStoreId("store_name",$productDetails->store_id);
        return $store->store_name;

    }
    public static function getProductDate($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        return date("m/d/Y",strtotime($productDetails->created_on));


    }
    public static function getProductMPindustry($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        $mpIndustryName = Admin::getProductMPindustryName($productDetails->mp_industry_id);
        return $mpIndustryName;

    }
    public static function getProductImages($mpid) {
        $productId =  Admin::getMPProductId($mpid);

        $productDetails                        =   Product::getProductDetails($productId);
        return  Admin::getProductImageThumb($productDetails->product_id);


    }

    //$productImages                                      =   Product::getProductImagesList($visit->product_id);


    public static function getMPProductId($mpid = "") {
        $dbh        = new Db();
        $table      = "product_mp_relation ";
        $field      = "product_id";
        $where      = "mp_id='".$mpid."'";
        $listProduct   = $dbh->selectRecord($table,$field,$where);
        return $listProduct->product_id;

    }
    

    public static function  getSiteTrackingDetails($startDate,$endDate,$orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {



        $dbh                    =   new db();
        //selected fields
        $fields                 =   '*';
        //join with file table to get banner image
        $join                   =   ' ';
        //where condition
        $where                  =   " where 1";
        //search condition
        if($searchColumn!="" ) {
            if($startDate!="" && $endDate!="") {

                $where .= " AND DATE_FORMAT(date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(date,'%Y-%m-%d')< '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }



        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';

        $siteTrackingDetails            = $dbh->getPagingData($fields,'tracker',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $siteTrackingDetails            ;
    }
    public static function  getSiteTrackingCount($startDate,$endDate,$oderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {



        $dbh                    =   new db();
        //selected fields
        $fields                 =   '*';
        //join with file table to get banner image
        $join                   =   ' ';
        //where condition
        $where                  =   " where 1 ";
        //search condition
        if($searchColumn!="" ) {
            if($startDate!="" && $endDate!="") {

                $where .= " AND DATE_FORMAT(date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(date,'%Y-%m-%d')< '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }



        $siteTrackingDetails            =$dbh->getPagingCount($fields,'tracker',$join,$where,$groupby,$orderType,$oderField);

        return $siteTrackingDetails[0]->cnt;
    }

    public static function getSiteTraficUser($userId,$done_through) {
        if($done_through ==4) {
            $merchantDetails    =  Merchant::getMerchantDetails($merchantId);
            return ucwords($merchantDetails->first_name.' '.$merchantDetails->last_name);
        }
        else {
            $userDetails = User::userUserDetails($userId);
            return ucwords($userDetails->first_name.' '.$userDetails->last_name);
        }

    }
    public static function getSiteTrackingAction($action) {
        if($action==1) {
            $actionText = "View Product";

        }

        if($action==3) {
            $actionText = "Add Comment";

        }
        if($action==4) {
            $actionText = "Post to wall";

        }
        if($action==5) {
            $actionText = "Facebook Send";

        }
        if($action==6) {
            $actionText = "Invite Friends";

        }
        if($action==7) {
            $actionText = "Twitter Share";

        }
        if($action==8) {
            $actionText = "Email Share";

        }
        if($action==9) {
            $actionText = "Pinit Share";

        }
        if($action==10) {
            $actionText = "Flag Product";

        }
        if($action==11) {
            $actionText = "Add To Cart";

        }
        if($action==12) {
            $actionText = "Remove From Cart";

        }
        if($action==13) {
            $actionText = "Update Cart";

        }
        if($action==14) {
            $actionText = "Store Creation";

        }
        if($action==15) {
            $actionText = "Add Product";

        }

        if($action==15) {
            $actionText = "Publish Store";

        }
        if($action==15) {
            $actionText = "Product Import";

        }
        if($action==15) {
            $actionText = "Store Update";

        }
        if($action==15) {
            $actionText = "FB Merchant Login Error";

        }
        return $actionText;
    }

    public static function saveTheme($themeId,$themeDetails) {
        $dbh    =   new Db();
        if(!empty($themeDetails)) {
            if($themeId > 0) {
                $saveRes = $dbh->updateFields('store_themes',$themeDetails," theme_id =".$themeId);
                return $themeId;
            }else {
                $themeId = $dbh->addFields('store_themes',$themeDetails);
                return $themeId;
            }
        }

    }
    //function to get store invitation list
    public static function  getThemeList($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'theme_id,theme_name,theme_folder_name,style,private_theme,added_stores,status,created_date,preview_image';
        //join with file table to get banner image
        $join                   =   '';
        //where condition
        $where                  =   "  ";
        //search condition
        if($searchColumn!="" ) {
            $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';

        $userDetails            = $dbh->getPagingData($fields,'store_themes AS T',$join,$where,$groupby,$orderType,$oderField,$limit);

        return $userDetails;
    }

    //function to get users count
    public static function  getThemeListCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'theme_id';
        //join with file table to get banner image
        $join                   =   '';
        //where condition
        $where                  =   "  ";
        //search condition
        if($searchColumn!="" ) {
            $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        //group by
        $groupby                =   '';



        $userCount = $dbh->getPagingCount($fields,'store_themes AS T',$join,$where,$groupby,$orderType,$oderField);

        return $userCount[0]->cnt;
    }


    public static function getThemeDetailsToEdit($theme_id) {
        $dbh    =   new db();
        $fields  =   " theme_id,theme_name,theme_folder_name,style,private_theme,added_stores,status,created_date";
        $where  =   "theme_id  ='".$theme_id."' ";
        $content = $dbh->selectRecord("store_themes",$fields,$where);
        return $content;
    }
    public static function getthemeAddedStores($storeIds) {

        $dbh    =   new db();

        $fields  =   " store_id,store_name";
        $where  =   "store_id in( ".$storeIds.") ";
        $stores = $dbh->selectResult("store",$fields,$where);
//        foreach($stores as $store){
//            $json = array();
//            $json['value'] = $store->store_id;
//            $json['name'] = $store->store_name;
//
//            $data[] = $json;
//        }
//
        $string = "";
        foreach($stores as $store) {
            $json = array();
            $string .= $store->store_id."::".$store->store_name."**";

        }
        $string = substr($string,0,-2);
        //echopre($store);exit;

        return $string;
    }

    public static function updateStoreTheme($storeId,$details) {
        $dbh    =   new Db();
        if(!empty($details)) {
            if($storeId > 0) {
                $saveRes = $dbh->updateFields('store',$details," store_id =".$storeId);
                return $storeId;
            }
        }

    }
    public static function  getFavoriteProductsArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'F.product_id ,F.user_id,F.created_date,S.store_name,P.product_name,U.email,U.first_name,U.last_name,U.fb_user_id';
        //join with file table to get banner image
        $join                   =   ' INNER JOIN tbl_user AS U on F.user_id=U.user_id';
        $join                   .=   ' INNER JOIN tbl_products AS P on F.product_id=P.product_id';
        $join                   .=   ' INNER JOIN   tbl_store   AS S ON S.store_id=P.store_id';
        //where condition
        $where                  =   " WHERE  1 ";
        if($searchColumn!="" ) {
            if($searchColumn=="store")
                $where              .=   " AND  S.store_name LIKE '%$searchText%' ";
            else if($searchColumn=="product_name")
                $where              .=   " AND  P.product_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(F.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(F.created_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
        }

        //group by
        $groupby                =   '';

        //perpagesize
        $startPage=($page-1)*$perPageSize;
        $limit                  =   "  $startPage,$perPageSize ";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="product_id")
            $orderField = "P.product_name";
        if($orderField=="user_id")
            $orderField = "F.user_id";
        $orderDetails            = $dbh->getPagingData($fields,'favorite_products  AS F',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    public static function  getFavoriteProductsReportArray($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {

        $dbh                    =   new db();
        //selected fields
        $fields                 =   'F.product_id ,F.user_id,F.created_date,S.store_name,P.product_name,U.email,U.first_name,U.last_name,U.fb_user_id';
        //join with file table to get banner image
        $join                   =   ' INNER JOIN tbl_user AS U on F.user_id=U.user_id';
        $join                   .=   ' INNER JOIN tbl_products AS P on F.product_id=P.product_id';
        $join                   .=   ' INNER JOIN   tbl_store   AS S ON S.store_id=P.store_id';
        //where condition
        $where                  =   " WHERE  1 ";
        if($searchColumn!="" ) {
            if($searchColumn=="store")
                $where              .=   " AND  S.store_name LIKE '%$searchText%' ";
            else if($searchColumn=="product_name")
                $where              .=   " AND  P.product_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(F.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(F.created_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
        }

        //group by
        $groupby                =   '';

        //perpagesize
        
        $limit                  =   "";
        //$limit                  =   '';
        if($orderField=="store_id")
            $orderField = "S.store_name";
        if($orderField=="product_id")
            $orderField = "P.product_name";
        if($orderField=="user_id")
            $orderField = "F.user_id";
        $orderDetails            = $dbh->getPagingData($fields,'favorite_products  AS F',$join,$where,$groupby,$orderType,$orderField,$limit);

        return $orderDetails;
    }
    //function to get recent orders list list
    public static function  getFavoriteProductsCount($orderField,$orderType,$searchColumn,$searchText,$page,$perPageSize,$startDate,$endDate) {
        $dbh                    =   new db();
        //selected fields
        $fields                 =   'favorite_id';

        //join with file table to get banner image
        $join                   =   ' INNER JOIN tbl_user AS U on F.user_id=U.user_id';
        $join                   =   ' INNER JOIN tbl_products AS P on F.product_id=P.product_id';
        $join                   .=   ' INNER JOIN   tbl_store   AS S ON S.store_id=P.store_id';
        //where condition
        $where                  =   " WHERE  1 ";
        if($searchColumn!="" ) {
            
            if($searchColumn=="store")
                $where              .=   " AND  S.store_name LIKE '%$searchText%' ";
            else if($searchColumn=="product_name")
                $where              .=   " AND  P.product_name LIKE '%$searchText%' ";

            else  if($startDate!="" && $endDate!="") {

                $where  .=   " AND  DATE_FORMAT(F.created_date,'%Y-%m-%d')>= '".$startDate."' AND DATE_FORMAT(F.created_date,'%Y-%m-%d')<= '".$endDate."' ";
                // $where  .=   " AND  DATE_FORMAT(I.created_date,'%m/%d/%Y')>= '".$startDate."' AND DATE_FORMAT(I.created_date,'%m/%d/%Y')<= '".$endDate."' ";
            }
            else if($searchColumn!="created_date")
                $where              .=   " AND  $searchColumn LIKE '%$searchText%' ";
        }
        //group by
        $groupby                =   '';

        //perpagesize


        $orderCount            = $dbh->getPagingCount($fields,'favorite_products  AS F',$join,$where,$groupby,"","",$limit);

        return $orderCount[0]->cnt;
    }


}


?>
