Guest User

Untitled

a guest
Oct 17th, 2019
90
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