Create a new version of paste:

<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of searchAuctionProduct1
 *
 * @author Kiril
 */
require_once 'auctionProduct.php';
require_once 'sellingTypeFilter.php';
require_once 'auctionStatusFilter.php';
require_once 'auctionFilter.php';
require_once 'categoryFilter.php';

class SearchAuctionProduct {
 

	/**
	 * Load auctions with there master fields(excluded products), matching params criteria
   *
	 * @param SallingTypeFilter[] $pSallingTypes
	 * @param AuctionFilter[] $pAuctions
   * @param AuctionStatusFilter[] $pAuctionStatuses
   * @param CategoryFilter[] $pCategories
	 * @throws AuctionProduct[]
	 */
	public static function search($isClientSearch=true, $pParameters=null, &$pSallingTypes=Array(), &$pAuctions=Array(), &$pAuctionStatuses=Array(),
          &$pCategories=Array(),&$pTotalResults=0,&$pFeatureValues=Array(),&$pTotalAuctions=0)
	{
    global $sqlAuctionProducts;
		global $sqlAuctions;
		global $sqlStatuses;
    global $sqlCategory;

		// intialize result
    $auctionProducts = Array();

    // clear out parameters;
    $pSallingTypes = Array();
		$pAuctions = Array();
		$pAuctionStatuses = Array();
    $pCategories =Array();

		try
    {
    	
    	if (!$isClientSearch) {
    		$officeId = Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getId():null;
                echo $officeId;

    		if(Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getOfficeGlobalId() : null ==2){
    			$companyId=Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getCompanyId():null;
    			if($companyId){
    				$officeId="";
	    			database_querySelect("SELECT id FROM office WHERE company_id =".$companyId, $offices);	    			
	    			foreach($offices as $o){
	    				$officeId.= $o['id'].',';	    				
	    			}
	    			$officeId = substr($officeId, 0, strlen($officeId)-1);
    			}	
    		}else{
    			$officeId = Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getId():null;
    		}
    		
    		database_querySelect("SELECT person_id FROM employee WHERE regional_office_id IN (".$officeId.")", $resss);
    		//echo "SELECT person_id FROM employee WHERE regional_office_id IN (".$officeId.")";
    		foreach ($resss as $rss) {
    			$str[] = $rss['person_id'];
    		}
    		
    		$str = implode(',', $str);
    		$str = ' AND a.createdby IN ('.$str.')';
    	}
    	
    	
			$where = "";
			$having = "";

			// auction id or name
      $where_auction = "";
			if (($pParameters->getAuctionId() > 0) || ($pParameters->getAuctionName() != "")){
				if ($pParameters->getAuctionId() > 0){
					//$where_auction .= " AND (a.id = " . $pParameters->getAuctionId() . ")";
					$where_auction .= " AND (a.id IN (" . $pParameters->getAuctionId() . "))";
				}
				else if ($pParameters->getAuctionName() != ""){
					$where_auction .= " AND (a.name LIKE '%" . $pParameters->getAuctionName() . "%')";
				}
			}
			
      // distinct id auction
      $where_distinct_id_auction = "";
			if ($pParameters->getDistinctIdAuction() > 0){
					//$where_auction .= " AND (a.id = " . $pParameters->getAuctionId() . ")";
					$where_distinct_id_auction .= " AND (a.id NOT IN (" . $pParameters->getDistinctIdAuction() . "))";
			}

      // specific filters only for client
      if ($isClientSearch){	      
        $where_client .= " AND (a.status_id NOT IN (1,5,6,7))";	              
      }

      // auction  status
      $where_auction_status = "";
			if ($pParameters->getAuctionStatus() != ""){
				$where_auction_status .= " AND (a.status_id IN (" . $pParameters->getAuctionStatus() . "))";
			}
			
      // auction  sellingtype
      $where_auction_sellingtype = "";
			if ($pParameters->getSellingTypeId() != ""){
				$where_auction_sellingtype .= " AND (a.sellingtype_id IN (" . $pParameters->getSellingTypeId() . "))";
			}

      // start date
      $where_start_date = "";
			if ($pParameters->getStartDateFrom() != null){
				//$where_start_date .= " AND (a.starttime >= '" . date("Y-m-d h:M:s", $pParameters->getStartDateFrom()) . "')";date_format(date_create(
				$where_start_date .= " AND (a.starttime >= '" . date(date_format(date_create($pParameters->getStartDateFrom()),"Y-m-d H:i:s")) . "')";
			}
			if ($pParameters->getStartDateTo() > 0){
				//$where_start_date .= " AND (a.starttime <= '" . date("Y-m-d h:M:s", $pParameters->getStartDateFrom()) . "')";
				$where_start_date .= " AND (a.starttime <= '" . date(date_format(date_create($pParameters->getStartDateTo()),"Y-m-d H:i:s")) . "')";
			}

      // end date
      $where_end_date = "";
			if ($pParameters->getEndDateFrom() != null){
				$where_end_date .= " AND (a.endtime >= '" . date(date_format(date_create($pParameters->getEndDateFrom()),"Y-m-d H:i:s")) . "')";
				//$where_end_date .= " AND (a.endtime >= '" . date("Y-m-d h:M:s", $pParameters->getEndDateFrom()) . "')";
			}
			if ($pParameters->getEndDateTo() > 0){
				$where_end_date .= " AND (a.endtime <= '" . date(date_format(date_create($pParameters->getEndDateTo()),"Y-m-d H:i:s")) . "')";
				//$where_end_date .= " AND (a.endtime <= '" . date("Y-m-d h:M:s", $pParameters->getEndDateTo()) . "')";
			}


      // Product Id or Name
      $where_product = "";
			if (($pParameters->getProductId() > 0) || ($pParameters->getProductName() != "")){
				if ($pParameters->getProductId() > 0){
					$where_product .= " AND (ap.id IN (". $pParameters->getProductId() . "))";
				}
				else if ($pParameters->getProductName() != ""){
					$where_product .= " AND (p.name LIKE '%" . $pParameters->getProductName() . "%')";
				}
			}
			
          // Product display mode and sold products
      $where_not_sold = "";
			if (($pParameters->getProductsDisplayMode()==1 && !$isClientSearch)){
					$where_not_sold .= " AND (ap.paid<>2) ";
			}

			//bidder
      $where_bidder = "";
			if ($pParameters->getBidderId() > 0){
				$where_bidder .= " AND (apb.bidder_id = ". $pParameters->getBidderId() . ")";
			}
			else if ($pParameters->getBidderName() != ""){
				$where_bidder .= " AND ((blgl.name  LIKE '%" . $pParameters->getBidderName() . "%') OR
                         (CONCAT(bnat.firstname, ' ', bnat.lastname)  LIKE '%" . $pParameters->getBidderName() . "%'))";
			}
			//final document 
			
	 $where_auction_document="";
	 		if($pParameters->getAuctionDocumentId()!=""){
	 			
	 			$doc_ids_temp=explode(",",$pParameters->getAuctionDocumentId());
	 			$doc_id1=$doc_ids_temp[1];
	 			$doc_id2=$doc_ids_temp[0];
	 			
	 			$where_auction_document.= " AND ( abd.auction_document_id= ".$doc_id1. " || abd.auction_document_id=".$doc_id2." )";
	 			
	 		}	
	 		if ($pParameters->getValidToFrom() != null){
				$where_auction_document .= " AND (abd.valid_to >= '" . date(date_format(date_create($pParameters->getValidToFrom()),"Y-m-d")) . "')";
			}
			if ($pParameters->getValidToTo() > 0){
				$where_auction_document .= " AND (abd.valid_to <= '" . date(date_format(date_create($pParameters->getValidToTo()),"Y-m-d")) . "')";
			}
			

      //debtor

      $where_debtor = "";
      /*
			if ($pParameters->getDebtorId() > 0){
				$where_debtor .= " AND (r.debtor_id = ". $pParameters->getDebtorId() . ")";
			}
			else if ($pParameters->getDebtorName() != ""){
				$where_debtor .= " AND ((dlgl.name  LIKE '%" . $pParameters->getDebtorId() . "%') OR
                         (CONCAT(dnat.firstname, ' ', dnat.lastname)  LIKE '%" . $pParameters->getDebtorName() . "%'))";
			}
		*/
      // Category Id or Name
      $where_category = "";
			if (($pParameters->getCategoryId() > 0) || ($pParameters->getCategoryName() != "")){
				if ($pParameters->getCategoryId() > 0){
					$where_category .= " AND (c.id IN (". $pParameters->getCategoryId() . "))";
				}
				else if ($pParameters->getCategoryName() != ""){
					$where_category .= " AND (c.name LIKE '%" . $pParameters->getCategoryName() . "%')";
				}
			}

      // location
      $where_location = "";
			if (($pParameters->getLatitude() > 0) && ($pParameters->getLongitude() > 0)){
					$where_location .= " (distance < 20) ";
			}
			
      // feature values
      $where_feature_values= "";
			if ($pParameters->getFeatureValues() != 0 && $pParameters->getFeatureValues()!=""){
					$where_feature_values.= " AND pfv.feature_value_id IN (".$pParameters->getFeatureValues().") ";
			}
			
			$having.=($where_location != ""?$where_location:"");
			
      // office filter
      $where_office="";
      if(!$isClientSearch && Employee::getCurrent()->getRoleId()!=1){
        //$where_office.=" AND regional_office_id=".Employee::getCurrent()->getOffice()->getId()." ";
        
      	$where_office.=" AND regional_office_id IN (".$officeId.") ";
        
      }
			
      $common_sql =
        " FROM auction a
        ".(($_SERVER['PHP_SELF']=='/admin/auctions.php')?" LEFT ":"")." JOIN auction_product ap ON a.id = ap.auction_id
          LEFT JOIN product p ON ap.product_id = p.id
          LEFT JOIN category c ON p.category_id = c.id
          LEFT JOIN product_location pl ON pl.product_id=p.id
          LEFT JOIN product_feature_value pfv ON pfv.product_id=p.id " .
          (($where_debtor != "") ? "
          LEFT JOIN ruling r ON a.ruling_id = r.id
          LEFT JOIN debtor d ON r.debtor_id = d.id
          LEFT JOIN legal dlgl ON d.person_id=dlgl.person_id
          LEFT JOIN `natural` dnat ON d.person_id=dnat.person_id " : "") .
          (($where_auction_document !="") ? "
          LEFT JOIN auction_bidder_document abd ON ap.id=abd.auction_product_id
          " : "").
          (($where_bidder != "") ? "
          LEFT JOIN auction_product_bidder apb ON ap.id = apb.auction_product_id
          LEFT JOIN bidder b ON apb.bidder_id = b.id
          LEFT JOIN legal blgl ON b.person_id=blgl.person_id
          LEFT JOIN `natural` bnat ON b.person_id=bnat.person_id " : "") .
          (!$isClientSearch && Employee::getCurrent()->getRoleId()!=1?
          "LEFT JOIN person pe ON pe.id=".(Employee::getCurrent()->getCurrentModule()->getUrl()=="/admin/auctions.php"?"a.createdby":"ap.created_by")."
          LEFT JOIN employee e ON e.person_id=pe.id":"");
                      
      $all_where = $where_client . $where_auction . $where_category . $where_auction_status .
                  $where_product .$where_not_sold. $where_start_date . $where_end_date . $where_distinct_id_auction .
                  $where_debtor . $where_auction_document .$where_bidder .$where_auction_sellingtype. $where_feature_values. $where_office;
                      
      // get auction products
      $sqlAuctionProducts =
        " SELECT SQL_CALC_FOUND_ROWS a.id as auction_id, ap.id as auction_product_id,
            ap.status_id, ap.startprice, ap.estimateprice, ap.endtime, ap.starttime,
            ".($isClientSearch?"ap.currentprice":"ap.currentprice").", ap.minbidincrement, ap.created_by, ap.modified_by,
            p.id, p.name, p.description, p.imageurl, pl.location " .
            ($where_location != ""?", ( 6371 *
              acos( cos( radians(".$pParameters->getLatitude().") ) *
              cos( radians( cast(SUBSTRING_INDEX(location, ',', 1) as decimal(10,6)) ) ) *
              cos( radians( cast(SUBSTRING_INDEX(location, ',', -1) as decimal(10,6)) ) -
              radians(".$pParameters->getLongitude().") ) +
              sin( radians(".$pParameters->getLatitude().") ) *
              sin( radians( cast(SUBSTRING_INDEX(location, ',', 1) as decimal(10,6)) ) ) ) ) AS distance " : "") .
      $common_sql . "
          WHERE (1 = 1) " . $all_where . "
          GROUP BY ap.id " .
          (($having != "") ? " HAVING " . $having : "");
      $OrderBy = $pParameters->resolveOrderBy($isClientSearch);
			//print_r($OrderBy);
      	$sqlAuctionProducts .= " ORDER BY ";
      		if ($_SERVER['PHP_SELF']=='/admin/auctions_validation.php') {
      			$sqlAuctionProducts .= " `a`.`status_id` DESC, "; 
      		}
      
			//$sqlAuctionProducts .= (($OrderBy) ? $OrderBy : " ap.id DESC ");
			$sqlAuctionProducts .= (($OrderBy) ? $OrderBy : " a.starttime DESC ");
			
    	$sqlAuctionProducts .= " LIMIT " . intval($pParameters->getOffset()); //. ", " . $pParameters->getResultsPerPage();
     //print $sqlAuctionProducts . "<br />";
     //exit;
      database_querySelect_Exception($sqlAuctionProducts, $rows);
    	
    	//get total number of results
    	 $pTotalResults=database_queryResult("SELECT FOUND_ROWS()", $totalResults);
    	
			foreach ($rows as $row)
      {
				if($row["auction_product_id"])
        {
          $bids_sql=
            " SELECT  b.id, b.auction_product_id, b.bidder_id, b.amount, b.date, p.email,
                CASE  WHEN l.id IS NOT NULL THEN l.name
                      WHEN n.id IS NOT NULL THEN CONCAT(n.firstname, ' ', n.lastname)
                      ELSE ''
                END AS name
              FROM bid b
              JOIN bidder bi ON b.bidder_id=bi.id
              JOIN person p on bi.person_id=p.id
              LEFT JOIN `natural` n on n.person_id=p.id
              LEFT JOIN legal l on l.person_id=p.id
              WHERE b.auction_product_id=".$row["auction_product_id"]."
              GROUP BY b.id
              ORDER BY b.date DESC";
        
          // print $sql . "<br />";
          //echo $bids_sql;
          database_querySelect_Exception($bids_sql, $rowsBids);
          //print_r($rowBids);

          $auctionProducts[$row["auction_product_id"]] = new AuctionProduct(
            $row["auction_product_id"], $row["id"], $row["auction_id"], $row["name"], $row["description"], $row["imageurl"],
            Array(), Array(), Array(),  $row["squarem"], $row["startprice"], $row["estimateprice"], $row["currentprice"],
            $row["minbidincrement"], $row["status_id"], $rowsBids, $row['location'], $row['created_by'], $row['modified_by'], 0, $row['starttime'], $row['endtime']);
        }
      }


      // get auction selling types
      $sqlSellingTypes =
        " SELECT st.id, st.name, COUNT(DISTINCT ap.id) as `count` " .
          $common_sql . "
          LEFT JOIN sellingtype st ON a.sellingtype_id = st.id
          WHERE (1 = 1) " . str_replace($where_auction_sellingtype, "", $all_where) . "
          GROUP BY st.id, st.name ";
      //print $sqlSellingTypes . "<br />";
      database_querySelect_Exception($sqlSellingTypes, $rows);
      foreach ($rows as $row){
				$pSallingTypes[$row["id"]] = new SellingTypeFilter($row["id"], $row["name"], "", $row["count"]);
			}


      // get auction statuses
      $sqlAuctionStatuses =
        " SELECT st.id, st.name, st.description, COUNT(DISTINCT ap.id) as `count` " .
          $common_sql . "
          LEFT JOIN auctionstatus st ON a.status_id = st.id
          WHERE (1 = 1) " . str_replace($where_auction_status, "", $all_where) . "
          GROUP BY st.id";
      //print $sqlSellingTypes . "<br />";
      database_querySelect_Exception($sqlAuctionStatuses, $rows);
      foreach ($rows as $row){
				$pAuctionStatuses[$row["id"]] = new AuctionStatusFilter($row["id"], $row["name"],
                $row["description"], $row["count"]);
			}


      // get auctions
//      $AuctionOrderBy="a.sellingtype_id";
		$AuctionOrderBy="a.starttime DESC";
      if(!$isClientSearch)
      {
	      $AuctionOrderBy = $pParameters->resolveOrderBy();
	      if($AuctionOrderBy=="")
	      {
	       //$AuctionOrderBy="a.sellingtype_id";
	       $AuctionOrderBy="a.starttime DESC";
	      }
      }
     
      $sqlAuctions =
        " SELECT SQL_CALC_FOUND_ROWS a.id, a.name, a.description, a.starttime, a.endtime, COUNT(DISTINCT ap.id) as `count`,a.sellingtype_id,a.status_id,a.createdby,a.modifiedby " .
          $common_sql . "
          WHERE (1 = 1) ";
          
		if($isClientSearch)
		{
			$sqlAuctions.=str_replace($where_auction, "", $all_where) . " ";
		}
		else 
		{
			$sqlAuctions.=$all_where." ";
		}
          $sqlAuctions.= " GROUP BY a.id ORDER BY ".$AuctionOrderBy;
          if(!$isClientSearch){
            $sqlAuctions .= " LIMIT " . intval($pParameters->getOffset()); //. ", " . $pParameters->getResultsPerPage();
          }
       //print $sqlAuctions . "<br />";
       database_querySelect_Exception($sqlAuctions, $rows);
      
        //get total number of results
        $pTotalAuctions=database_queryResult("SELECT FOUND_ROWS()", $pTotalAuctions);
        foreach ($rows as $row){
          $tempAuction = new AuctionFilter(
            $row["id"], $row["name"], $row["description"], $row["starttime"], $row["endtime"], $row["count"],
            $row['sellingtype_id']); //, $row['status_id']
			$tempAuction->setStatusId($row['status_id']);
          $tempAuction->setCreatedBy($row['createdby']);
          $tempAuction->setModifiedBy($row['modifiedby']);
          $pAuctions[$row["id"]]=$tempAuction;
        }
	//	print_r($pAuctions);

        // get categories
        $sqlCategory =
          " SELECT c.id, c.name, c.description, c.imageurl, COUNT(DISTINCT ap.id) as `count` " .
            $common_sql . "
            WHERE (1 = 1) " . str_replace($where_category, "", $all_where) . "
            GROUP BY c.id ORDER BY c.name";
        //print $sqlCategory . "<br />";
        database_querySelect_Exception($sqlCategory, $rows);
        foreach ($rows as $row){
          $pCategories[$row["id"]] = new CategoryFilter($row["id"], 0, $row["name"], $row["description"],
          $row["imageurl"], 0, Array(), Array(), $row["count"]);
        }
			
        // get City feature values
        $sqlFeatureValues =
          " SELECT pfv.feature_value_id as id, count(distinct ap.id) as `count`" .
            $common_sql . "
            LEFT JOIN feature_value fv ON fv.id = pfv.feature_value_id
            WHERE (1 = 1) " . str_replace($where_feature_values, "", $all_where) . "
            GROUP BY pfv.feature_value_id
            ORDER BY fv.string_value";
        //print $sqlFeatureValues."<br />";
        database_querySelect_Exception($sqlFeatureValues, $rows);
        //print_r($rows);
        foreach ($rows as $row){
          if($row['id']=="")
					continue;
          $featureValueFilter[$row["id"]]=new FeatureValueFilter($row['id'],$row['count']);
          //$featureValues[$row["id"]]=$featureValue->getByValueId($row["id"]);
        }
        $pFeatureValues=$featureValueFilter;
        //print_r($featureValueFilter);


        // get filter Statuses:
        /*$sqlStatuses = "SELECT k.status_id AS id, s.name, COUNT(*) AS total FROM ";
        $sqlStatuses .= "(SELECT o.id, o.status_id FROM sc_order o ";
        $sqlStatuses .= "LEFT JOIN sc_order_product op ON o.id = op.order_id ";
        $sqlStatuses .= "WHERE (1 = 1) ";
        $sqlStatuses .= $where;
        $sqlStatuses .= " GROUP BY o.id, o.status_id ) AS k";
        $sqlStatuses .= " LEFT JOIN sc_order_status s ON k.status_id = s.id ";
        $sqlStatuses .= " GROUP BY k.status_id ";
        database_querySelect_Exception($sqlStatuses, $pFilterStatuses);*/
      }
      catch (Exception $e) {
        throw new Exception("SearchAuctionProduct->search: " . $e->getMessage(), $e->getCode());
      }
      return $auctionProducts;
    }
  }
?>