API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 17th, 2019
150
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 19.15 KB | None | 0 0
raw download clone embed print report
  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. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!