API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 25th, 2015
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 9.95 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2.  
  3. //comprova que s'executi desde una associacio
  4. //$idAsso = getIdAssociacioByApiKey($apiKey);
  5.  
  6. if (isset($idBotiga)) {
  7.     $idBotiga = $idBotiga;
  8. } else {
  9.     $idBotiga = "";
  10. }
  11. $idBotiga = escapa($idBotiga);
  12.  
  13. if (isset($idAssociacio)) {
  14.     $idAssociacio = $idAssociacio;
  15. } else {
  16.     $idAssociacio = "";
  17. }
  18. $idAssociacio = escapa($idAssociacio);
  19.  
  20. if (($idBotiga == '') && ($idAssociacio == '')) {
  21.     $arr["cfg"]["error"] = $i18n->_('Falta especificar un valor en la variable') . ' \'idBotiga\' o \'idAssociacio\'';
  22.     $arr["cfg"]["errorNum"] = $numApi . '001';
  23.     if (modeDevelopment())
  24.         $arr["cfg"]["debugTrace"] = "" . __FILE__ . ":" . __LINE__ . "";
  25.     exit(printData($arr));
  26. }
  27. if (isset($cercador)) {
  28.     $cercador = $cercador;
  29. } else {
  30.     $cercador = "";
  31. }
  32. if (isset($excel)) {
  33.     $excel = $excel;
  34. } else {
  35.     $excel = "";
  36. }
  37. if (isset($idFiltre)) {
  38.     $idFiltre = $idFiltre;
  39. } else {
  40.     $idFiltre = "";
  41. }
  42. if ($idFiltre == "") {
  43.     $arr["cfg"]["error"] = $i18n->_('Falta especificar un valor en la variable') . ' \'idFiltre\'';
  44.     $arr["cfg"]["errorNum"] = $numApi . '002';
  45.     if (modeDevelopment())
  46.         $arr["cfg"]["debugTrace"] = "" . __FILE__ . ":" . __LINE__ . "";
  47.     exit(printData($arr));
  48. }
  49. $idFiltre = escapa($idFiltre);
  50.  
  51. if ($idAssociacio != '') {
  52.     $sqlAux = " AND idAssociacio = $idAssociacio ";
  53.     $sqlAux2 = " cb.idAssociacio =  '$idAssociacio' ";
  54. }
  55. if ($idBotiga != "") {
  56.     $sqlAux = " AND idBotiga = $idBotiga";
  57.     $sqlAux2 = " cb.idBotiga =  '$idBotiga'";
  58. }
  59.  
  60.  
  61. $query = " SELECT * FROM filtres_com WHERE idFiltre = $idFiltre $sqlAux";
  62.  
  63. $result = $connexio_mysqli->query($query);
  64. if ($result->num_rows == 0) {
  65.     $arr["cfg"]["error"] = $i18n->_("No s'ha trobat el filtre");
  66.     $arr["cfg"]["errorNum"] = $numApi . '002';
  67.     if (modeDevelopment())
  68.         $arr["cfg"]["debugTrace"] = "" . __FILE__ . ":" . __LINE__ . "";
  69.     exit(printData($arr));
  70. }
  71. $filtre = $result->fetch_array();
  72. if ($debug)
  73.     printr2($filtre);
  74.  
  75.  
  76. $sqlBot = '';
  77. if (($idBotiga != '') && ($idBotiga != 0)) {
  78.     $sqlBot = " WHERE cb.idBotiga = '$idBotiga'  ";
  79. }
  80.  
  81. if (($idAssociacio != '') && ($idAssociacio != 0)) {
  82.     $sqlBot = " WHERE c.idAssociacio = '$idAssociacio'  ";
  83. }
  84.  
  85. if ($sqlBot != '') {
  86.     $sqlAux1 = ' AND ';
  87. } else {
  88.     $sqlAux1 = '';
  89. }
  90. if ($filtre['nom'] != '') {
  91.     $sqlAux1 .= " CONCAT( c.nom,  ' ', c.cognom1,  ' ', c.cognom2 ) LIKE '%$filtre[nom]%' AND \n";
  92. }
  93.  
  94. if ($filtre['sexe'] != '') {
  95.     $sqlAux1 .= " c.sexe = '$filtre[sexe]' AND \n";
  96. }
  97.  
  98. if ($filtre['diaAniversari'] != '') {
  99.     $sqlAux1 .= " c.data_naixament LIKE '%-$filtre[diaAniversari]' AND \n";
  100. }
  101.  
  102. if ($filtre['mesAniversari'] != '') {
  103.     $sqlAux1 .= " c.data_naixament LIKE '%-$filtre[mesAniversari]-%' AND \n";
  104. }
  105.  
  106. if (($filtre['edatMin'] != 0) && ($filtre['edatMin'] != '')) {
  107.     //buscar any actual i restali la edat minima aixi
  108.     $dataMin = (date('Y') - $filtre['edatMin']) . '-' . date('m-d');
  109.     $sqlAux1 .= " c.data_naixament <= '$dataMin' AND \n";
  110. }
  111.  
  112. if (($filtre['edatMax'] != 0) && ($filtre['edatMax'] != '') && ($filtre['edatMax'] != 120)) {
  113.     //buscar any actual i restali la edat minima aixi
  114.     $dataMin = (date('Y') - $filtre['edatMax']) . '-' . date('m-d');
  115.     $sqlAux1 .= " c.data_naixament >= '$dataMin' AND \n";
  116. }
  117.  
  118. if ($filtre['registratAbans'] != '') {
  119.     $sqlAux1 .= " c.tempsEstampat <= '$filtre[registratAbans]' AND \n";
  120. }
  121.  
  122. if ($filtre['registratDespres'] != '') {
  123.     $sqlAux1 .= " c.tempsEstampat >= '$filtre[registratDespres]' AND \n";
  124. }
  125.  
  126. if ($filtre['cp'] != '') {
  127.     $sqlAux1 .= " c.cp = '$filtre[cp]' AND \n";
  128. }
  129.  
  130. if ($filtre['correu'] != '') {
  131.     if ($filtre['correu'] == 0) {
  132.         //no tenen correu
  133.         $sqlAux1 .= " c.email = '' AND \n";
  134.     } else {
  135.         //tenen correu
  136.         $sqlAux1 .= " c.email != '' AND \n";
  137.     }
  138. }
  139. $join = '';
  140. if (($filtre['operacio'] != '') || ($filtre['hanComprat'] != '') || ($filtre['noHanComprat'] != '')) {
  141.     if (($filtre['hanComprat'] != '') || ($filtre['operacio'] != '')) {
  142.         $join = "INNER JOIN ultima_operacio uo ON uo.idClient = c.idClient ";
  143.     }
  144.     if ($filtre['noHanComprat'] != '') {
  145.         if ($idAssociacio != '') {
  146.             $join = " LEFT JOIN (SELECT idClient, idBotiga, idAssociacio";
  147.             if($filtre['operacio'] != ''){
  148.                 $join .= ", operacio ";
  149.             }
  150.              $join .= " , MAX(data) as data FROM ultima_operacio WHERE idAssociacio = '$idAssociacio' ";
  151.              
  152.              if($filtre['operacio'] != ''){
  153.                  $join .= " AND operacio = '$filtre[operacio]' ";
  154.              }
  155.              
  156.              $join .=" GROUP BY idClient, idAssociacio";
  157.              if($filtre['operacio'] != ''){
  158.                  $join .= ",operacio";
  159.              }
  160.              $join .=") as uo ON uo.idClient = c.idClient";
  161.              
  162.              
  163.              
  164.         } else {
  165.  
  166.             $join = "INNER JOIN ultima_operacio uo ON uo.idClient = c.idClient ";
  167.         }
  168.     }
  169. }
  170. $sqlAux2 = '';
  171. if ($filtre['operacio'] != '') {
  172.     $sqlAux2 .= " uo.operacio = '$filtre[operacio]' AND \n";
  173. }
  174. if ($filtre['hanComprat'] != '') {
  175.     $sqlAux2 .= " uo.data >= '$filtre[hanComprat]' AND \n";
  176. }
  177.  
  178. if ($filtre['noHanComprat'] != '') {
  179.     $sqlAux2 .= "(uo.data <= '$filtre[noHanComprat]' OR uo.data is null ) AND \n";
  180. }
  181.  
  182.  
  183.  
  184. if ($idBotiga != '') {
  185.     $campCom = 'idBotiga';
  186. }
  187. if ($idAssociacio != '') {
  188.     $campCom = 'idAssociacio';
  189. }
  190. $join3= '';
  191. if ($filtre['comunicacio'] != '') {
  192.         $join3 = " LEFT JOIN (SELECT * FROM baixes_com WHERE $campCom = ".$$campCom.") as bc ON bc.idClient = c.idClient ";
  193.     if ($filtre['comunicacio'] == 1) {
  194.        
  195.        
  196.         //volen rebre comunicacio
  197.         $sqlAux2 .= " bc.idBaixa IS NULL AND \n";
  198.     } else {
  199.         //no volen rebre comunicacio
  200.         $sqlAux2 .= " bc.idBaixa IS NOT NULL AND \n";
  201.     }
  202. }
  203.  
  204. if(($filtre['sector'] != '')&&($filtre['sector'] != 0)){
  205.     $sqlAux2 .= " cbs.sector = '$filtre[sector]' AND \n";
  206. }
  207.  
  208.  
  209.  
  210.  
  211. if ($sqlAux2 != '') {
  212.     $sqlAux2 = substr($sqlAux2, 0, -5);
  213. }
  214. if ($sqlAux != '') {
  215.     $sqlAux1 = substr($sqlAux1, 0, -5);
  216. }
  217.  
  218. if (isset($start)) {
  219.     $start = $start;
  220. } else {
  221.     $start = 0;
  222. }
  223.  
  224. if (isset($pagina)) {
  225.     $sqlPag = "LIMIT $start , 25";
  226. } else {
  227.     $sqlPag = '';
  228. }
  229.  
  230.  
  231. $and = '';
  232. if ((($sqlAux1 != '') && ($sqlAux2 != '')) || (($sqlBot != '') && ($sqlAux2 != ''))) {
  233.     $and = ' AND ';
  234. }
  235. $sqlCercador = '';
  236. if ($cercador != '') {
  237.     if (($sqlBot != '') && ($sqlAux1 != '') && ($sqlAux2 != '')) {
  238.         if (($sqlBot != '') && ($sqlAux1 == '') && ($sqlAux2 == '')) {
  239.             $sqlCercador .= ' AND ';
  240.         }
  241.     } else {
  242.         $sqlCercador .= ' AND ';
  243.     }
  244.     $sqlCercador .= "  (
  245.                        CONCAT( c.nom,  ' ', c.cognom1,  ' ', c.cognom2 ) LIKE '%$cercador%'
  246.                        OR t.tokenCurt  LIKE '%$cercador%'
  247.                        OR c.dni        LIKE '%$cercador%'
  248.                        OR c.tel_fix    LIKE '%$cercador%'
  249.                        OR c.email      LIKE '%$cercador%'  
  250.                        OR g.name       LIKE '%$cercador%'    
  251.                    ) ";
  252. }
  253.  
  254. $sqlAux3 = '';
  255. if (($idBotiga != 0) && ($idBotiga != '')) {
  256.     $sqlAux3 = "  LEFT JOIN clients_botigues cb
  257.        ON cb.idClient = c.idClient";
  258. }
  259.  
  260. $join2 = '';
  261. if(($filtre['sector'] != '')&&($filtre['sector'] != '0') &&($idAssociacio != '')){
  262.     $join2 = " LEFT JOIN (SELECT cb.idClient, b.sector FROM clients_botigues cb LEFT JOIN botigues b ON b.idBotiga = cb.idBotiga WHERE cb.idAssociacio = '$idAssociacio' AND b.sector = '$filtre[sector]') as cbs ON cbs.idClient = c.idClient   ";
  263. }
  264.  
  265.  
  266.  
  267.  
  268.  
  269.  
  270. //
  271. //$sqlAux4 = '';
  272. //if(($idAssociacio != '') &&($idAssociacio != 0)){
  273. //    $sqlAux4 = " c.idAssociacio = $idAssociacio  "
  274. //}
  275. //consulta cache
  276. $cache = Cache::get($test . date('Y_m_d_h') . "_filtre_$idFiltre");
  277.  
  278. if ($debug)
  279.     var_dump($cache);
  280.  
  281. if (!$cache) {
  282.  
  283.     $query = "SELECT
  284.            c.idClient
  285.        FROM clients c
  286.        LEFT JOIN geo_name g
  287.        ON g.geonameid = c.poblacio
  288.        $sqlAux3
  289.        LEFT JOIN targetes t ON t.idTargeta = c.idTargeta
  290.        $join3
  291.        $join
  292.        $join2
  293.        $sqlBot
  294.        $sqlAux1
  295.        $and
  296.        $sqlAux2
  297.        GROUP BY c.idClient";
  298.       if ($debug)
  299.         printr2($query);
  300.  
  301.    
  302.     $ress = $connexio_mysqli->query($query);
  303.     $cache = $ress->num_rows;
  304.     Cache::put($test . date('Y_m_d') . "_filtre_$idFiltre", $cache);
  305.  
  306. }
  307. $total = $cache;
  308.  
  309. if ($debug)
  310.     printr2("TOTAL:: $total");
  311.  
  312.  
  313. $query = "SELECT
  314.            c.idClient,
  315.            CONCAT( c.nom,  ' ', c.cognom1,  ' ', c.cognom2 ) AS nomClient,
  316.            c.email,
  317.            c.tel_fix,
  318.            g.name AS poblacio,
  319.            c.tempsEstampat,
  320.            c.usuari,
  321.            t.tokenCurt,
  322.            c.tempsEstampat
  323.        FROM clients c
  324.        LEFT JOIN geo_name g
  325.        ON g.geonameid = c.poblacio
  326.        $sqlAux3
  327.        LEFT JOIN targetes t ON t.idTargeta = c.idTargeta
  328.        $join3
  329.        $join
  330.        $join2
  331.        $sqlBot
  332.        $sqlAux1
  333.        $and
  334.        $sqlAux2
  335.        $sqlCercador    
  336.        GROUP BY c.idClient
  337.        
  338.        $sqlPag
  339.  
  340.        ";
  341. if ($debug)
  342.     printr2($query);
  343.  
  344. $result = $connexio_mysqli->query($query);
  345.  
  346.  
  347. if ($result->num_rows == 0) {
  348.     $arr["cfg"]["error"] = $i18n->_("No hi ha clients que passin el filtre");
  349.     $arr["cfg"]["errorNum"] = $numApi . '002';
  350.     if (modeDevelopment())
  351.         $arr["cfg"]["debugTrace"] = "" . __FILE__ . ":" . __LINE__ . "";
  352.     exit(printData($arr));
  353. }
  354.  
  355. while ($row = $result->fetch_array()) {
  356.     $clients[$row['idClient']] = $row;
  357. }
  358.  
  359.  
  360. $arr['cfg']['data']['total'] = $total;
  361. $arr['cfg']['data']['mails'] = $clients;
  362. $arr['cfg']['getClientsFromFiltre'] = true;
  363. $arr["cfg"]["error"] = "false";
  364. if (modeDevelopment())
  365.     $arr["cfg"]["debugTrace"] = "" . __FILE__ . ":" . __LINE__ . "";
  366. exit(printData($arr));
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!