SHARE
TWEET

Untitled

a guest Oct 17th, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. /*
  3.  * To change this template, choose Tools | Templates
  4.  * and open the template in the editor.
  5.  */
  6.  
  7. /**
  8.  * Description of searchAuctionProduct1
  9.  *
  10.  * @author Kiril
  11.  */
  12. require_once 'auctionProduct.php';
  13. require_once 'sellingTypeFilter.php';
  14. require_once 'auctionStatusFilter.php';
  15. require_once 'auctionFilter.php';
  16. require_once 'categoryFilter.php';
  17.  
  18. class SearchAuctionProduct {
  19.  
  20.  
  21.     /**
  22.      * Load auctions with there master fields(excluded products), matching params criteria
  23.    *
  24.      * @param SallingTypeFilter[] $pSallingTypes
  25.      * @param AuctionFilter[] $pAuctions
  26.    * @param AuctionStatusFilter[] $pAuctionStatuses
  27.    * @param CategoryFilter[] $pCategories
  28.      * @throws AuctionProduct[]
  29.      */
  30.     public static function search($isClientSearch=true, $pParameters=null, &$pSallingTypes=Array(), &$pAuctions=Array(), &$pAuctionStatuses=Array(),
  31.           &$pCategories=Array(),&$pTotalResults=0,&$pFeatureValues=Array(),&$pTotalAuctions=0)
  32.     {
  33.     global $sqlAuctionProducts;
  34.         global $sqlAuctions;
  35.         global $sqlStatuses;
  36.     global $sqlCategory;
  37.  
  38.         // intialize result
  39.     $auctionProducts = Array();
  40.  
  41.     // clear out parameters;
  42.     $pSallingTypes = Array();
  43.         $pAuctions = Array();
  44.         $pAuctionStatuses = Array();
  45.     $pCategories =Array();
  46.  
  47.         try
  48.     {
  49.        
  50.         if (!$isClientSearch) {
  51.             $officeId = Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getId():null;
  52.                 echo $officeId;
  53.  
  54.             if(Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getOfficeGlobalId() : null ==2){
  55.                 $companyId=Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getCompanyId():null;
  56.                 if($companyId){
  57.                     $officeId="";
  58.                     database_querySelect("SELECT id FROM office WHERE company_id =".$companyId, $offices);                 
  59.                     foreach($offices as $o){
  60.                         $officeId.= $o['id'].',';                      
  61.                     }
  62.                     $officeId = substr($officeId, 0, strlen($officeId)-1);
  63.                 }  
  64.             }else{
  65.                 $officeId = Employee::getCurrent()->getOffice() ? getCurrent()->getOffice()->getId():null;
  66.             }
  67.            
  68.             database_querySelect("SELECT person_id FROM employee WHERE regional_office_id IN (".$officeId.")", $resss);
  69.             //echo "SELECT person_id FROM employee WHERE regional_office_id IN (".$officeId.")";
  70.             foreach ($resss as $rss) {
  71.                 $str[] = $rss['person_id'];
  72.             }
  73.            
  74.             $str = implode(',', $str);
  75.             $str = ' AND a.createdby IN ('.$str.')';
  76.         }
  77.        
  78.        
  79.        
  80.             $where = "";
  81.             $having = "";
  82.  
  83.             // auction id or name
  84.       $where_auction = "";
  85.             if (($pParameters->getAuctionId() > 0) || ($pParameters->getAuctionName() != "")){
  86.                 if ($pParameters->getAuctionId() > 0){
  87.                     //$where_auction .= " AND (a.id = " . $pParameters->getAuctionId() . ")";
  88.                     $where_auction .= " AND (a.id IN (" . $pParameters->getAuctionId() . "))";
  89.                 }
  90.                 else if ($pParameters->getAuctionName() != ""){
  91.                     $where_auction .= " AND (a.name LIKE '%" . $pParameters->getAuctionName() . "%')";
  92.                 }
  93.             }
  94.            
  95.       // distinct id auction
  96.       $where_distinct_id_auction = "";
  97.             if ($pParameters->getDistinctIdAuction() > 0){
  98.                     //$where_auction .= " AND (a.id = " . $pParameters->getAuctionId() . ")";
  99.                     $where_distinct_id_auction .= " AND (a.id NOT IN (" . $pParameters->getDistinctIdAuction() . "))";
  100.             }
  101.  
  102.       // specific filters only for client
  103.       if ($isClientSearch){      
  104.         $where_client .= " AND (a.status_id NOT IN (1,5,6,7))";              
  105.       }
  106.  
  107.       // auction  status
  108.       $where_auction_status = "";
  109.             if ($pParameters->getAuctionStatus() != ""){
  110.                 $where_auction_status .= " AND (a.status_id IN (" . $pParameters->getAuctionStatus() . "))";
  111.             }
  112.            
  113.       // auction  sellingtype
  114.       $where_auction_sellingtype = "";
  115.             if ($pParameters->getSellingTypeId() != ""){
  116.                 $where_auction_sellingtype .= " AND (a.sellingtype_id IN (" . $pParameters->getSellingTypeId() . "))";
  117.             }
  118.  
  119.       // start date
  120.       $where_start_date = "";
  121.             if ($pParameters->getStartDateFrom() != null){
  122.                 //$where_start_date .= " AND (a.starttime >= '" . date("Y-m-d h:M:s", $pParameters->getStartDateFrom()) . "')";date_format(date_create(
  123.                 $where_start_date .= " AND (a.starttime >= '" . date(date_format(date_create($pParameters->getStartDateFrom()),"Y-m-d H:i:s")) . "')";
  124.             }
  125.             if ($pParameters->getStartDateTo() > 0){
  126.                 //$where_start_date .= " AND (a.starttime <= '" . date("Y-m-d h:M:s", $pParameters->getStartDateFrom()) . "')";
  127.                 $where_start_date .= " AND (a.starttime <= '" . date(date_format(date_create($pParameters->getStartDateTo()),"Y-m-d H:i:s")) . "')";
  128.             }
  129.  
  130.       // end date
  131.       $where_end_date = "";
  132.             if ($pParameters->getEndDateFrom() != null){
  133.                 $where_end_date .= " AND (a.endtime >= '" . date(date_format(date_create($pParameters->getEndDateFrom()),"Y-m-d H:i:s")) . "')";
  134.                 //$where_end_date .= " AND (a.endtime >= '" . date("Y-m-d h:M:s", $pParameters->getEndDateFrom()) . "')";
  135.             }
  136.             if ($pParameters->getEndDateTo() > 0){
  137.                 $where_end_date .= " AND (a.endtime <= '" . date(date_format(date_create($pParameters->getEndDateTo()),"Y-m-d H:i:s")) . "')";
  138.                 //$where_end_date .= " AND (a.endtime <= '" . date("Y-m-d h:M:s", $pParameters->getEndDateTo()) . "')";
  139.             }
  140.  
  141.  
  142.       // Product Id or Name
  143.       $where_product = "";
  144.             if (($pParameters->getProductId() > 0) || ($pParameters->getProductName() != "")){
  145.                 if ($pParameters->getProductId() > 0){
  146.                     $where_product .= " AND (ap.id IN (". $pParameters->getProductId() . "))";
  147.                 }
  148.                 else if ($pParameters->getProductName() != ""){
  149.                     $where_product .= " AND (p.name LIKE '%" . $pParameters->getProductName() . "%')";
  150.                 }
  151.             }
  152.            
  153.           // Product display mode and sold products
  154.       $where_not_sold = "";
  155.             if (($pParameters->getProductsDisplayMode()==1 && !$isClientSearch)){
  156.                     $where_not_sold .= " AND (ap.paid<>2) ";
  157.             }
  158.  
  159.             //bidder
  160.       $where_bidder = "";
  161.             if ($pParameters->getBidderId() > 0){
  162.                 $where_bidder .= " AND (apb.bidder_id = ". $pParameters->getBidderId() . ")";
  163.             }
  164.             else if ($pParameters->getBidderName() != ""){
  165.                 $where_bidder .= " AND ((blgl.name  LIKE '%" . $pParameters->getBidderName() . "%') OR
  166.                          (CONCAT(bnat.firstname, ' ', bnat.lastname)  LIKE '%" . $pParameters->getBidderName() . "%'))";
  167.             }
  168.             //final document
  169.            
  170.      $where_auction_document="";
  171.             if($pParameters->getAuctionDocumentId()!=""){
  172.                
  173.                 $doc_ids_temp=explode(",",$pParameters->getAuctionDocumentId());
  174.                 $doc_id1=$doc_ids_temp[1];
  175.                 $doc_id2=$doc_ids_temp[0];
  176.                
  177.                 $where_auction_document.= " AND ( abd.auction_document_id= ".$doc_id1. " || abd.auction_document_id=".$doc_id2." )";
  178.                
  179.             }  
  180.             if ($pParameters->getValidToFrom() != null){
  181.                 $where_auction_document .= " AND (abd.valid_to >= '" . date(date_format(date_create($pParameters->getValidToFrom()),"Y-m-d")) . "')";
  182.             }
  183.             if ($pParameters->getValidToTo() > 0){
  184.                 $where_auction_document .= " AND (abd.valid_to <= '" . date(date_format(date_create($pParameters->getValidToTo()),"Y-m-d")) . "')";
  185.             }
  186.            
  187.  
  188.       //debtor
  189.  
  190.       $where_debtor = "";
  191.       /*
  192.             if ($pParameters->getDebtorId() > 0){
  193.                 $where_debtor .= " AND (r.debtor_id = ". $pParameters->getDebtorId() . ")";
  194.             }
  195.             else if ($pParameters->getDebtorName() != ""){
  196.                 $where_debtor .= " AND ((dlgl.name  LIKE '%" . $pParameters->getDebtorId() . "%') OR
  197.                          (CONCAT(dnat.firstname, ' ', dnat.lastname)  LIKE '%" . $pParameters->getDebtorName() . "%'))";
  198.             }
  199.         */
  200.       // Category Id or Name
  201.       $where_category = "";
  202.             if (($pParameters->getCategoryId() > 0) || ($pParameters->getCategoryName() != "")){
  203.                 if ($pParameters->getCategoryId() > 0){
  204.                     $where_category .= " AND (c.id IN (". $pParameters->getCategoryId() . "))";
  205.                 }
  206.                 else if ($pParameters->getCategoryName() != ""){
  207.                     $where_category .= " AND (c.name LIKE '%" . $pParameters->getCategoryName() . "%')";
  208.                 }
  209.             }
  210.  
  211.       // location
  212.       $where_location = "";
  213.             if (($pParameters->getLatitude() > 0) && ($pParameters->getLongitude() > 0)){
  214.                     $where_location .= " (distance < 20) ";
  215.             }
  216.            
  217.       // feature values
  218.       $where_feature_values= "";
  219.             if ($pParameters->getFeatureValues() != 0 && $pParameters->getFeatureValues()!=""){
  220.                     $where_feature_values.= " AND pfv.feature_value_id IN (".$pParameters->getFeatureValues().") ";
  221.             }
  222.            
  223.             $having.=($where_location != ""?$where_location:"");
  224.            
  225.       // office filter
  226.       $where_office="";
  227.       if(!$isClientSearch && Employee::getCurrent()->getRoleId()!=1){
  228.         //$where_office.=" AND regional_office_id=".Employee::getCurrent()->getOffice()->getId()." ";
  229.        
  230.         $where_office.=" AND regional_office_id IN (".$officeId.") ";
  231.        
  232.       }
  233.            
  234.       $common_sql =
  235.         " FROM auction a
  236.         ".(($_SERVER['PHP_SELF']=='/admin/auctions.php')?" LEFT ":"")." JOIN auction_product ap ON a.id = ap.auction_id
  237.           LEFT JOIN product p ON ap.product_id = p.id
  238.           LEFT JOIN category c ON p.category_id = c.id
  239.           LEFT JOIN product_location pl ON pl.product_id=p.id
  240.           LEFT JOIN product_feature_value pfv ON pfv.product_id=p.id " .
  241.           (($where_debtor != "") ? "
  242.           LEFT JOIN ruling r ON a.ruling_id = r.id
  243.           LEFT JOIN debtor d ON r.debtor_id = d.id
  244.           LEFT JOIN legal dlgl ON d.person_id=dlgl.person_id
  245.           LEFT JOIN `natural` dnat ON d.person_id=dnat.person_id " : "") .
  246.           (($where_auction_document !="") ? "
  247.           LEFT JOIN auction_bidder_document abd ON ap.id=abd.auction_product_id
  248.           " : "").
  249.           (($where_bidder != "") ? "
  250.           LEFT JOIN auction_product_bidder apb ON ap.id = apb.auction_product_id
  251.           LEFT JOIN bidder b ON apb.bidder_id = b.id
  252.           LEFT JOIN legal blgl ON b.person_id=blgl.person_id
  253.           LEFT JOIN `natural` bnat ON b.person_id=bnat.person_id " : "") .
  254.           (!$isClientSearch && Employee::getCurrent()->getRoleId()!=1?
  255.           "LEFT JOIN person pe ON pe.id=".(Employee::getCurrent()->getCurrentModule()->getUrl()=="/admin/auctions.php"?"a.createdby":"ap.created_by")."
  256.           LEFT JOIN employee e ON e.person_id=pe.id":"");
  257.                      
  258.       $all_where = $where_client . $where_auction . $where_category . $where_auction_status .
  259.                   $where_product .$where_not_sold. $where_start_date . $where_end_date . $where_distinct_id_auction .
  260.                   $where_debtor . $where_auction_document .$where_bidder .$where_auction_sellingtype. $where_feature_values. $where_office;
  261.                      
  262.       // get auction products
  263.       $sqlAuctionProducts =
  264.         " SELECT SQL_CALC_FOUND_ROWS a.id as auction_id, ap.id as auction_product_id,
  265.             ap.status_id, ap.startprice, ap.estimateprice, ap.endtime, ap.starttime,
  266.             ".($isClientSearch?"ap.currentprice":"ap.currentprice").", ap.minbidincrement, ap.created_by, ap.modified_by,
  267.             p.id, p.name, p.description, p.imageurl, pl.location " .
  268.             ($where_location != ""?", ( 6371 *
  269.               acos( cos( radians(".$pParameters->getLatitude().") ) *
  270.               cos( radians( cast(SUBSTRING_INDEX(location, ',', 1) as decimal(10,6)) ) ) *
  271.               cos( radians( cast(SUBSTRING_INDEX(location, ',', -1) as decimal(10,6)) ) -
  272.               radians(".$pParameters->getLongitude().") ) +
  273.               sin( radians(".$pParameters->getLatitude().") ) *
  274.               sin( radians( cast(SUBSTRING_INDEX(location, ',', 1) as decimal(10,6)) ) ) ) ) AS distance " : "") .
  275.       $common_sql . "
  276.           WHERE (1 = 1) " . $all_where . "
  277.           GROUP BY ap.id " .
  278.           (($having != "") ? " HAVING " . $having : "");
  279.       $OrderBy = $pParameters->resolveOrderBy($isClientSearch);
  280.             //print_r($OrderBy);
  281.         $sqlAuctionProducts .= " ORDER BY ";
  282.             if ($_SERVER['PHP_SELF']=='/admin/auctions_validation.php') {
  283.                 $sqlAuctionProducts .= " `a`.`status_id` DESC, ";
  284.             }
  285.      
  286.             //$sqlAuctionProducts .= (($OrderBy) ? $OrderBy : " ap.id DESC ");
  287.             $sqlAuctionProducts .= (($OrderBy) ? $OrderBy : " a.starttime DESC ");
  288.            
  289.         $sqlAuctionProducts .= " LIMIT " . intval($pParameters->getOffset()); //. ", " . $pParameters->getResultsPerPage();
  290.      //print $sqlAuctionProducts . "<br />";
  291.      //exit;
  292.       database_querySelect_Exception($sqlAuctionProducts, $rows);
  293.        
  294.         //get total number of results
  295.          $pTotalResults=database_queryResult("SELECT FOUND_ROWS()", $totalResults);
  296.        
  297.             foreach ($rows as $row)
  298.       {
  299.                 if($row["auction_product_id"])
  300.         {
  301.           $bids_sql=
  302.             " SELECT  b.id, b.auction_product_id, b.bidder_id, b.amount, b.date, p.email,
  303.                 CASE  WHEN l.id IS NOT NULL THEN l.name
  304.                       WHEN n.id IS NOT NULL THEN CONCAT(n.firstname, ' ', n.lastname)
  305.                       ELSE ''
  306.                 END AS name
  307.               FROM bid b
  308.               JOIN bidder bi ON b.bidder_id=bi.id
  309.               JOIN person p on bi.person_id=p.id
  310.               LEFT JOIN `natural` n on n.person_id=p.id
  311.               LEFT JOIN legal l on l.person_id=p.id
  312.               WHERE b.auction_product_id=".$row["auction_product_id"]."
  313.               GROUP BY b.id
  314.               ORDER BY b.date DESC";
  315.        
  316.           // print $sql . "<br />";
  317.           //echo $bids_sql;
  318.           database_querySelect_Exception($bids_sql, $rowsBids);
  319.           //print_r($rowBids);
  320.  
  321.           $auctionProducts[$row["auction_product_id"]] = new AuctionProduct(
  322.             $row["auction_product_id"], $row["id"], $row["auction_id"], $row["name"], $row["description"], $row["imageurl"],
  323.             Array(), Array(), Array(),  $row["squarem"], $row["startprice"], $row["estimateprice"], $row["currentprice"],
  324.             $row["minbidincrement"], $row["status_id"], $rowsBids, $row['location'], $row['created_by'], $row['modified_by'], 0, $row['starttime'], $row['endtime']);
  325.         }
  326.       }
  327.  
  328.  
  329.       // get auction selling types
  330.       $sqlSellingTypes =
  331.         " SELECT st.id, st.name, COUNT(DISTINCT ap.id) as `count` " .
  332.           $common_sql . "
  333.           LEFT JOIN sellingtype st ON a.sellingtype_id = st.id
  334.           WHERE (1 = 1) " . str_replace($where_auction_sellingtype, "", $all_where) . "
  335.           GROUP BY st.id, st.name ";
  336.       //print $sqlSellingTypes . "<br />";
  337.       database_querySelect_Exception($sqlSellingTypes, $rows);
  338.       foreach ($rows as $row){
  339.                 $pSallingTypes[$row["id"]] = new SellingTypeFilter($row["id"], $row["name"], "", $row["count"]);
  340.             }
  341.  
  342.  
  343.       // get auction statuses
  344.       $sqlAuctionStatuses =
  345.         " SELECT st.id, st.name, st.description, COUNT(DISTINCT ap.id) as `count` " .
  346.           $common_sql . "
  347.           LEFT JOIN auctionstatus st ON a.status_id = st.id
  348.           WHERE (1 = 1) " . str_replace($where_auction_status, "", $all_where) . "
  349.           GROUP BY st.id";
  350.       //print $sqlSellingTypes . "<br />";
  351.       database_querySelect_Exception($sqlAuctionStatuses, $rows);
  352.       foreach ($rows as $row){
  353.                 $pAuctionStatuses[$row["id"]] = new AuctionStatusFilter($row["id"], $row["name"],
  354.                 $row["description"], $row["count"]);
  355.             }
  356.  
  357.  
  358.  
  359.  
  360.       // get auctions
  361. //      $AuctionOrderBy="a.sellingtype_id";
  362.         $AuctionOrderBy="a.starttime DESC";
  363.       if(!$isClientSearch)
  364.       {
  365.           $AuctionOrderBy = $pParameters->resolveOrderBy();
  366.           if($AuctionOrderBy=="")
  367.           {
  368.            //$AuctionOrderBy="a.sellingtype_id";
  369.            $AuctionOrderBy="a.starttime DESC";
  370.           }
  371.       }
  372.      
  373.       $sqlAuctions =
  374.         " 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 " .
  375.           $common_sql . "
  376.           WHERE (1 = 1) ";
  377.          
  378.         if($isClientSearch)
  379.         {
  380.             $sqlAuctions.=str_replace($where_auction, "", $all_where) . " ";
  381.         }
  382.         else
  383.         {
  384.             $sqlAuctions.=$all_where." ";
  385.         }
  386.           $sqlAuctions.= " GROUP BY a.id ORDER BY ".$AuctionOrderBy;
  387.           if(!$isClientSearch){
  388.             $sqlAuctions .= " LIMIT " . intval($pParameters->getOffset()); //. ", " . $pParameters->getResultsPerPage();
  389.           }
  390.        //print $sqlAuctions . "<br />";
  391.        database_querySelect_Exception($sqlAuctions, $rows);
  392.      
  393.         //get total number of results
  394.         $pTotalAuctions=database_queryResult("SELECT FOUND_ROWS()", $pTotalAuctions);
  395.         foreach ($rows as $row){
  396.           $tempAuction = new AuctionFilter(
  397.             $row["id"], $row["name"], $row["description"], $row["starttime"], $row["endtime"], $row["count"],
  398.             $row['sellingtype_id']); //, $row['status_id']
  399.             $tempAuction->setStatusId($row['status_id']);
  400.           $tempAuction->setCreatedBy($row['createdby']);
  401.           $tempAuction->setModifiedBy($row['modifiedby']);
  402.           $pAuctions[$row["id"]]=$tempAuction;
  403.         }
  404.     //  print_r($pAuctions);
  405.  
  406.         // get categories
  407.         $sqlCategory =
  408.           " SELECT c.id, c.name, c.description, c.imageurl, COUNT(DISTINCT ap.id) as `count` " .
  409.             $common_sql . "
  410.             WHERE (1 = 1) " . str_replace($where_category, "", $all_where) . "
  411.             GROUP BY c.id ORDER BY c.name";
  412.         //print $sqlCategory . "<br />";
  413.         database_querySelect_Exception($sqlCategory, $rows);
  414.         foreach ($rows as $row){
  415.           $pCategories[$row["id"]] = new CategoryFilter($row["id"], 0, $row["name"], $row["description"],
  416.           $row["imageurl"], 0, Array(), Array(), $row["count"]);
  417.         }
  418.            
  419.         // get City feature values
  420.         $sqlFeatureValues =
  421.           " SELECT pfv.feature_value_id as id, count(distinct ap.id) as `count`" .
  422.             $common_sql . "
  423.             LEFT JOIN feature_value fv ON fv.id = pfv.feature_value_id
  424.             WHERE (1 = 1) " . str_replace($where_feature_values, "", $all_where) . "
  425.             GROUP BY pfv.feature_value_id
  426.             ORDER BY fv.string_value";
  427.         //print $sqlFeatureValues."<br />";
  428.         database_querySelect_Exception($sqlFeatureValues, $rows);
  429.         //print_r($rows);
  430.         foreach ($rows as $row){
  431.           if($row['id']=="")
  432.                     continue;
  433.           $featureValueFilter[$row["id"]]=new FeatureValueFilter($row['id'],$row['count']);
  434.           //$featureValues[$row["id"]]=$featureValue->getByValueId($row["id"]);
  435.         }
  436.         $pFeatureValues=$featureValueFilter;
  437.         //print_r($featureValueFilter);
  438.  
  439.  
  440.         // get filter Statuses:
  441.         /*$sqlStatuses = "SELECT k.status_id AS id, s.name, COUNT(*) AS total FROM ";
  442.         $sqlStatuses .= "(SELECT o.id, o.status_id FROM sc_order o ";
  443.         $sqlStatuses .= "LEFT JOIN sc_order_product op ON o.id = op.order_id ";
  444.         $sqlStatuses .= "WHERE (1 = 1) ";
  445.         $sqlStatuses .= $where;
  446.         $sqlStatuses .= " GROUP BY o.id, o.status_id ) AS k";
  447.         $sqlStatuses .= " LEFT JOIN sc_order_status s ON k.status_id = s.id ";
  448.         $sqlStatuses .= " GROUP BY k.status_id ";
  449.         database_querySelect_Exception($sqlStatuses, $pFilterStatuses);*/
  450.       }
  451.       catch (Exception $e) {
  452.         throw new Exception("SearchAuctionProduct->search: " . $e->getMessage(), $e->getCode());
  453.       }
  454.       return $auctionProducts;
  455.     }
  456.   }
  457. ?>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top