0) $this_page = $startfrom; else $this_page = floor( $startfrom / $onpage ) + 1; # ulozeni previous if ($this_page > 1) $output['previous'] = $this_page - 1; # ulozeni next if ($total_pages > $this_page) $output['next'] = $this_page + 1; # ulozeni aktivni stranky $output['active_page'] = $this_page; # ulozeni first if ($this_page != 1) $output['first'] = 1; # ulozeni last if ($total_pages != $this_page) $output['last'] = $total_pages; # ulozeni total pages a total items $output['total_pages'] = $total_pages; $output['total_items'] = $total; $output['onpage'] = $onpage; $output['startfrom'] = $startfrom; # ulozeni pole se strankamy # do budoucna predelat (pridat ... moznost) for ($i = 1; $i <= $total_pages; $i++) $output['pages'][] = $i; if ($total_pages > 1) return ($output); } ?> Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot stavu na CZ $statusy = array ('ok' => 'aktivní', 'disabled' => 'pozastaven', 'deleted' => 'smazán'); $typy = array ('regular' => 'Venkovní VIP parkování', 'exclusive' => 'Garážové VIP parkování All Inclusive', 'cheap' => 'Venkovní parkování'); if (is_array ($data)) foreach ($data as $key=>$value) { # prepis statusu na CS verzi $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; # prepis typu na CS verzi $data[$key]['type_cs'] = $typy[$value['type']] ? $typy[$value['type']] : $data[$key]['type']; $this->cache[$value['id']] = $data[$key]; if ($value['promo']) $data[$key]['promos'] = preg_split( "/( +|( *, *))/", $value['promo'] ); } return ($data); } # ................................................................... # get + all pricelist_period function getComplete ($where = null, $order = null, $limit = null, $limit_from = null) { $data = $this->get ($where, $order, $limit, $limit_from); # iterate and get ids if (is_array ($data)) { foreach ($data as $key=>$radka) $ids[$key] = $radka['id']; if (is_array ($ids)) { # periods $temp = $this->DB->getAllRows( $this->DB->query ('SELECT id, pricelist_id, UNIX_TIMESTAMP(validfrom) AS validfrom, UNIX_TIMESTAMP(validtill) AS validtill FROM pricelist_period WHERE pricelist_id IN ("'.implode ('","', $ids).'")')); if (is_array ($temp)) foreach ($temp as $value) $save[$value['pricelist_id']]['periods'][$value['id']] = $value; } # add to main data in 'periods' array if (is_array ($save)) foreach ($data as $key=>$value) $data[$key] = array_merge ($data[$key], $save[$value['id']]); } return ($data); } # ................................................................... function countPrice ($departure, $arrival = null, $pricelist_id = 19, $type='regular') { $type_convertor = array ('regular'=>'outside_', 'exclusive'=>'inside_', 'cheap'=>'cheap_'); # kontrola a defaulty if (!$departure) return (null); if (!$arrival) $arrival = date ('j. n. Y H:i'); if (!in_array ($type, array ('regular', 'exclusive', 'cheap'))) $type = 'regular'; # pocetdni $days = countdays (parsedate ($departure), parsedate ($arrival))+1; # nacteni ceniku $pricelist = $this->getId (intval ($pricelist_id), false); if (!is_array ($pricelist)) return (false); # vlastni spocitani /* $days_maxprice = $days>31 ? 31 : $days; for ($i=1; $i<=$days_maxprice; $i++) { $price += $pricelist[$type_convertor[$type].$i]; } $price += $pricelist[$type_convertor[$type].$days_maxprice] * ($days-$days_maxprice); */ # pro pripad, ze nemam cenu u venkovniho, pouziji venkovni VIP for ($i=1; $i <= 32; $i++) $pricelist[$type_convertor['cheap'].$i] = $pricelist[$type_convertor['cheap'].$i]>0 ? $pricelist[$type_convertor['cheap'].$i] : $pricelist[$type_convertor['regular'].$i]; if ($days<32) $price = $pricelist[$type_convertor[$type].$days]; else $price = $pricelist[$type_convertor[$type].'31']+($pricelist[$type_convertor[$type].'32']* ($days-31)); return ($price); } # ................................................................... function getPrice ($departure=null, $arrival = null, $type='cheap', $promo=null, $pricelist=null) { if (!$departure) $departure = date ('j. n. Y H:i', time ()+86400); if (!$arrival) $arrival = date ('j. n. Y H:i'); $data['departure_ts'] = parsedate ($departure); $data['arrival_ts'] = parsedate ($arrival); $data['departure'] = date ('j. n. Y H:i', $data['departure_ts']); $data['arrival'] = date ('j. n. Y H:i', $data['arrival_ts']); $data['type'] = 'cheap'; $data['type_2'] = 'cheap'; //$data['type'] = $type=='inside' ? 'inside' : 'outside'; //$data['type_2'] = $type=='inside' ? 'exclusive' : 'regular'; # pocetdni $data['days'] = countdays ($data['departure_ts']-1801, $data['arrival_ts'])+1; if ($promo) $pricelist = $this->checkPromo($promo); if ($pricelist) { $data['promo'] = $promo; $data['pricelist_id'] = $pricelist; } $data['price'] = $this->countPrice ($data['departure_ts']-1801, $data['arrival_ts'], ($pricelist ? $pricelist : 19), $data['type_2']); if ($pricelist) $data['prevprice'] = $this->countPrice ($data['departure_ts']-1801, $data['arrival_ts'], 19, $data['type_2']); //$data['remaining'] = $this->getRemaining ($data['departure_ts']-1801, $data['arrival_ts'], 19, $data['type_2']); return ($data); } # ................................................................... function getPrices ($departure=null, $arrival = null, $promo=null) { if (!$departure) $departure = date ('j. n. Y H:i', time ()+86400); if (!$arrival) $arrival = date ('j. n. Y H:i'); $data['departure_ts'] = parsedate ($departure); $data['arrival_ts'] = parsedate ($arrival); $data['departure'] = date ('j. n. Y H:i', $data['departure_ts']); $data['arrival'] = date ('j. n. Y H:i', $data['arrival_ts']); $data['type'] = 'cheap'; $data['type_2'] = 'cheap'; //$data['type'] = $type=='inside' ? 'inside' : 'outside'; //$data['type_2'] = $type=='inside' ? 'exclusive' : 'regular'; # pocetdni $data['days'] = countdays ($data['departure_ts']-1801, $data['arrival_ts'])+1; if ($promo) $pricelist = $this->checkPromo($promo); if ($pricelist) { $data['promo'] = $promo; $data['pricelist_id'] = $pricelist; } $pricelists = $this->findIds ('p.status="ok"'); foreach ($pricelists as $id) { $data['price'][$id] = $this->countPrice ($data['departure_ts']-1801, $data['arrival_ts'], $id, $data['type_2']); $data['priceperday'][$id] = round ($data['price'][$id]/$data['days']); $data['pricelist'][$id] = $this->getId ($id, false); } return ($data); } # ................................................................... function getRemaining ($departure=null, $arrival = null, $type='outside', $promo=null) { # priprava dat if (!$departure) $departure = date ('j. n. Y H:i', time ()+86400); if (!$arrival) $arrival = date ('j. n. Y H:i'); $departure_ts = parsedate ($departure); $arrival_ts = parsedate ($arrival); $days = countdays ($departure_ts-1801, $arrival_ts)+1; $capacity = array ('outside'=>380, 'garage'=>70); if ($promo) $pricelist = $this->checkPromo($promo); if (!$pricelist) $pricelist=19; $list = $this->getId ($pricelist, false); if ($list['capacity_outside']) $capacity['outside'] = $list['capacity_outside']; if ($list['capacity_garage']) $capacity['garage'] = $list['capacity_garage']; switch ($type) { case 'cheap': $type = '="cheap"'; $max = $capacity['outside']; if ($list['capacity_outside'] && $pricelist!=19) $type.=' AND r.pricelist_id="'.$pricelist.'"'; break; case 'inside': case 'outside': default : $type = '<>"cheap"'; $max = $capacity['garage']; if ($list['capacity_garage'] && $pricelist!=19) $type.=' AND r.pricelist_id="'.$pricelist.'"'; } # 4 dny a mene parking + 5% kapacita if ($days<5) $max = round ($max*1.05); //exit ('SELECT IF(IFNULL(MIN(res.remaining),'.$max.')>0,IFNULL(MIN(res.remaining),'.$max.'),0) as capacity FROM (SELECT a.date, '.$max.'-COUNT(*) AS remaining FROM reservation r LEFT JOIN (select FROM_UNIXTIME(ROUND("'.$arrival_ts.'"/3600)*3600) + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) HOUR as date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c) a ON a.date BETWEEN r.arrival AND r.departure WHERE a.date BETWEEN FROM_UNIXTIME("'.$arrival_ts.'") AND FROM_UNIXTIME("'.$departure_ts.'") AND r.status IN ("new","pending","verified","paid") and r.type'.$type.' GROUP BY YEAR(a.date), MONTH(a.date), DAY(a.date), HOUR(a.date)) res;'); # query return ($this->DB->getResult ($this->DB->query ('SELECT IF(IFNULL(MIN(res.remaining),'.$max.')>0,IFNULL(MIN(res.remaining),'.$max.'),0) as capacity FROM (SELECT a.date, '.$max.'-COUNT(*) AS remaining FROM reservation r LEFT JOIN (select FROM_UNIXTIME(ROUND("'.$arrival_ts.'"/3600)*3600) + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) HOUR as date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c) a ON a.date BETWEEN r.arrival AND r.departure WHERE a.date BETWEEN FROM_UNIXTIME("'.$arrival_ts.'") AND FROM_UNIXTIME("'.$departure_ts.'") AND r.status IN ("new","pending","verified","paid") and r.type'.$type.' GROUP BY YEAR(a.date), MONTH(a.date), DAY(a.date), HOUR(a.date)) res;'))); } # ................................................................... function checkPromo ($promo=null) { //return ($this->findId ('p.status="ok" AND p.promo IS NOT NULL AND p.promo="' . mysql_real_escape_string ($promo) . '"')); return ($this->findId ('p.status="ok" AND p.promo IS NOT NULL AND LOWER(p.promo) REGEXP "(^|[, ]+)' . mysql_real_escape_string (trim ($promo)) . '($|[, ]+)"')); } # ................................................................... function getPriceListDouble ($pricelistId = 19) { $pl = $this->getId (intval ($pricelistId), false); for ($i=1; $i <= 16; $i++) { $pricesdouble[$i]['inside']['first'] = array ('day'=>$i,'price'=>$pl['inside_'.$i]); $pricesdouble[$i]['inside']['second'] = array ('day'=>$i+16,'price'=>$pl['inside_'.($i+16)]); $pricesdouble[$i]['outside']['first'] = array ('day'=>$i,'price'=>$pl['outside_'.$i]); $pricesdouble[$i]['outside']['second'] = array ('day'=>$i+16,'price'=>$pl['outside_'.($i+16)]); $pricesdouble[$i]['cheap']['first'] = array ('day'=>$i,'price'=>($pl['cheap_'.$i] ? $pl['cheap_'.$i] : $pl['outside_'.$i])); $pricesdouble[$i]['cheap']['second'] = array ('day'=>$i+16,'price'=>($pl['cheap_'.($i+16)] ? $pl['cheap_'.($i+16)] : $pl['outside_'.($i+16)])); } return ($pricesdouble); } # ................................................................... function getPriceList($pricelistId = 19) { $pl = $this->getId (intval ($pricelistId), false); for ($i=1; $i <= 32; $i++) { $prices[$i]['inside'] = $pl['inside_'.$i]; $prices[$i]['outside'] = $pl['outside_'.$i]; $prices[$i]['cheap'] = $pl['cheap_'.$i] ? $pl['cheap_'.$i] : $pl['outside_'.$i]; } return ($prices); } # ................................................................... function set ($set, $ids = null) { if (!is_array ($set)) return (false); # presunu do pomocne promene a vycistim $periods = $set['periods']; unset ($set['periods']); # ulozeni beznych dat if (@count ($set) >= 1) $temp = parent::set ($set, $ids); $ids = $temp ? $temp : $ids; # _______________________________________________________________ # Periody - pokud jsem nastavil, musi ukladat do M:N tabulky if (is_array ($periods) && is_numeric ($ids)) { unset ($sql); foreach ($periods as $key=>$value) { if ($key=='new') { $sql[] = '(' . (int) $ids . ', '.$value['validfrom'].', '.$value['validtill'].')'; continue; } $periods_ids[] = $key; $sql_update[(int) $key] = 'validfrom='.$value['validfrom'].', validtill='.$value['validtill']; } # promazu pripadne prebyvajici $this->DB->query ('DELETE FROM pricelist_period WHERE pricelist_id = "' . (int) $ids . '" AND id NOT IN ("' . implode ('", "', $periods_ids) . '");', get_class ($this) . ' -> set (DELETE old periods)'); # ulozim si existujici if (is_array ($sql)) $this->DB->query ('INSERT INTO pricelist_period (pricelist_id, validfrom, validtill) VALUES ' . implode (', ', $sql) . ';', get_class ($this) . ' -> set (INSERT new period)'); # update uz existujich (zmena ceny) if (is_array ($sql_update)) foreach ($sql_update as $key=>$value) $this->DB->query ('UPDATE pricelist_period SET '.$value.' WHERE id="'.$key.'";', get_class ($this) . ' -> set (UPDATE periods)'); } return ($ids); } } ?>0,IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 30 MINUTE))*150,0),0)'; //var $sqlbase_price_penalty_2 = 'IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)'; var $sqlbase = 'SELECT SQL_CALC_FOUND_ROWS r.id, r.status, UNIX_TIMESTAMP(r.created) AS created, r.user_id, UNIX_TIMESTAMP(r.arrival) AS arrival, UNIX_TIMESTAMP(r.departure) AS departure, TIMESTAMPDIFF(DAY,r.arrival,DATE_SUB(r.departure, INTERVAL 1801 SECOND))+1 AS days, r.type, r.promo, r.price, r.drivername, TRIM(r.driveremail) AS driveremail, r.driverphone, r.gatevoucherid, r.note, r.pricelist_id, u.name AS partner_name, p.title AS pricelist_title, r.car, p.promo AS pricelist_promo, LEFT(MD5(CONCAT("2fhdnPDBAteL8lRF", r.id)),6) AS hash, r.gopay_paymentsessionid, r.gopay_state, r.invoice_company, r.invoice_street, r.invoice_city, r.invoice_psc, r.invoice_ic, r.invoice_dic, r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0) AS price_total, SUM(re.price) AS price_extraservice, r.sms_sent, r.person, r.babycarseat, MAX(IF(re.extraservice_id=2,1,0)) AS valetparking, r.gopay_paid, r.payment, (r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0))-(r.gopay_paid+r.bitpay_paid+r.invoice_paid+IFNULL(pay.amount,0)) AS price_topay, GROUP_CONCAT(re.extraservice_id) AS extraservices, r.googlecalendar_arrival, r.googlecalendar_departure, UNIX_TIMESTAMP(r.arrival_real) AS arrival_real, UNIX_TIMESTAMP(r.departure_real) AS departure_real, r.alteruser_id, IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0) AS price_penalty, r.flightnumber, r.babycarseat_1, r.babycarseat_2, r.babycarseat_3, r.called, r.carkey, r.needadmin, r.bitpay_id, r.bitpay_state, r.bitpay_paid, r.ibod_ean, r.reslock, r.club_user_id, cr.points_parking, cr.points_extras, FLOOR(r.price/10) AS points_parking_future, FLOOR(IFNULL(SUM(re.price),0)/10) AS points_extras_future, IF(r.pricelist_id=43 AND cu.id<>10615,0,FLOOR((IFNULL(SUM(re.price),0)+r.price+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0))/10)-IFNULL(cp.points_parking+cp.points_extras,0)) AS points_future, SUM(cp.points_parking+cp.points_extras) AS points_received, r.ibod_done, cu.level AS club_user_level, IF(cu.password="" OR cu.password IS NULL,0,1) AS club_user_active, LEFT(MD5(cu.id),5) AS club_admin_security, cr.points_parking+cr.points_extras AS points_used, r.spz, r.lift_car, IF(r.payment="cash",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0)+IFNULL(pay.amount_cash,0)+IFNULL(pay.amount_auto_cash,0) AS paid_cash, IF(r.payment="card",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0)+IFNULL(pay.amount_card,0)+IFNULL(pay.amount_auto_card,0) AS paid_card, r.invoice_paid, r.price_original, r.provision, r.language, r.review_overall, r.review_navigation, r.review_stuff, r.review_transfer, r.review_cleaning, r.review_valet, r.review_source, r.review_recommend, r.review_improve, UNIX_TIMESTAMP(r.review_created) AS review_created, IF(lastchange > (now() - interval 31 minute), 1, 0) as recentupdate, review_show, r.iata, r.source, review_replay, review_replayname, rec.receive AS receive_id, UNIX_TIMESTAMP(rec.receive_date) AS receive_data, rec.company, gzt.id AS greenzone_id, gzt.cardnumber AS greenzone_cardnumber, UNIX_TIMESTAMP(gzt.entrytime) AS greenzone_entrytime, gzt.pincode AS greenzone_pincode, gzt.barcode AS greenzone_barcode, r.drop_car, IF(rec.company="premium",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0),0) AS receive_premium_total, IF(rec.company="premium",IF(r.payment="cash",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0),0) AS receive_premium_cash, IF(rec.company="premium",IF(r.payment="card",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0),0) AS receive_premium_card, IF(rec.company="premium",r.gopay_paid,0) AS receive_premium_gopay, IF(rec.company="premium",r.invoice_paid,0) AS receive_premium_invoice, IF(rec.company<>"premium" OR rec.company IS NULL,r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0),0) AS receive_tripassistant_total, IF(rec.company<>"premium" OR rec.company IS NULL,IF(r.payment="cash",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0),0) AS receive_tripassistant_cash, IF(rec.company<>"premium" OR rec.company IS NULL,IF(r.payment="card",r.price+IFNULL(SUM(re.price),0)+IF(IF(r.status NOT IN ("noshow","canceled","deleted"),TIMESTAMPDIFF(MINUTE,r.departure,IF(r.departure_real IS NOT NULL,r.departure_real,NOW())),0)>90,IF(r.status NOT IN ("noshow","canceled","deleted"),(TIMESTAMPDIFF(DAY,r.departure,DATE_SUB(IF(r.departure_real IS NOT NULL,r.departure_real,NOW()), INTERVAL 90 MINUTE))+1)*150,0),0)-(r.gopay_paid+r.bitpay_paid+r.invoice_paid),0),0) AS receive_tripassistant_card, IF(rec.company<>"premium" OR rec.company IS NULL,r.gopay_paid,0) AS receive_tripassistant_gopay, IF(rec.company<>"premium" OR rec.company IS NULL,r.invoice_paid,0) AS receive_tripassistant_invoice, r.vinciparkingcard, r.parking, r.parking_location, pay.amount, pay.payment_count, r.review_infinity, ec.number AS europortcard_number, pay.last_paid, UNIX_TIMESTAMP(r.transfer_e2t) AS transfer_e2t, transfer_e2t_user_id, u2.name AS transfer_e2t_user_name, UNIX_TIMESTAMP(r.transfer_t2e) AS transfer_t2e, transfer_t2e_user_id, u3.name AS transfer_t2e_user_name FROM reservation r LEFT JOIN user u ON r.user_id=u.id LEFT JOIN pricelist p ON r.pricelist_id=p.id LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id LEFT JOIN club_reservation cr ON r.id=cr.reservation_id LEFT JOIN club_point cp ON r.id=cp.reservation_id AND r.club_user_id=cp.club_user_id LEFT JOIN club_user cu ON r.club_user_id=cu.id LEFT JOIN receive rec ON r.id=rec.reservation_id LEFT JOIN greenzone_ticket gzt ON r.id=gzt.reservation_id AND gzt.valid=1 AND gzt.blocked=0 LEFT JOIN europortcard ec ON r.id=ec.reservation_id LEFT JOIN user u2 ON r.transfer_e2t_user_id=u.id LEFT JOIN user u3 ON r.transfer_t2e_user_id=u.id LEFT JOIN (SELECT reservation_id, IFNULL(SUM(amount),0) AS amount, COUNT(reservation_id) AS payment_count, IFNULL(SUM(IF(payment="cash",amount,0)),0) AS amount_cash, IFNULL(SUM(IF(payment="card",amount,0)),0) AS amount_card, IFNULL(SUM(IF(payment="gopay",amount,0)),0) AS amount_gopay, IFNULL(SUM(IF(payment="auto_cash",amount,0)),0) AS amount_auto_cash, IFNULL(SUM(IF(payment="auto_card",amount,0)),0) AS amount_auto_card, UNIX_TIMESTAMP(MAX(paid)) AS last_paid FROM payment WHERE status="ok" GROUP BY reservation_id) pay ON pay.reservation_id=r.id WHERE 1 GROUP BY r.id'; # zaklad SQL dotazu var $sqlupdate = 'UPDATE reservation r'; # zaklad SQL dotazu - UPDATE var $sqlinsert = 'INSERT INTO reservation'; # zaklad SQL dotazu - INSERT var $sqltable = 'r'; //var $sync = array ('order'); var $order = '-3'; var $limit = -1; var $fulltextcolumns = array ('r.id','r.drivername','r.driveremail','r.driverphone','r.note','r.promo', 'r.gatevoucherid', 'r.car', 'r.gopay_paymentsessionid', 'r.invoice_company', 'r.invoice_street', 'r.invoice_city', 'r.invoice_psc', 'r.invoice_ic', 'r.invoice_dic', 'p.title', 'r.flightnumber', 'r.bitpay_id', 'r.ibod_ean', 'r.spz','r.iata','r.source', 'gzt.pincode','gzt.cardnumber', 'ec.number'); var $kurzy; var $cache; var $googleCalURI = 'https://admin.lastminuteparking.cz/calimport.php'; var $googleCalClient; # ................................................................... # KONSTRUKTOR function Reservation (& $database) { return ($this->Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null, $nocalcrows = false) { $data = parent::get ($where, $order, $limit, $limit_from, $nocalcrows); # prepis hodnot stavu na CZ $statusy = array ('new' => 'Nová', 'pending' => 'Připravena', 'verified' => 'Aktivní (parkuje)', 'paid' => 'Předplaceno', 'noshow' => 'Propadlá', 'closed' => 'Uzavřena', 'canceled' => 'Zrušeno', 'deleted' => 'Smazáno'); $typy = array ('regular' => 'Garážové parkování', 'exclusive' => 'Garážové VIP parkování All Inclusive', 'cheap' => 'Venkovní parkování'); $typy_short = array ('regular' => 'Garáž', 'exclusive' => 'Garáž VIP', 'cheap' => 'Venku'); $payment = array ('cash' => 'Hotově', 'card' => 'Platební kartou', 'auto_cash' => 'Hotově v automatu', 'auto_card' => 'Platební kartou v automatu'); $source = array ('smileparking' => 'SmileParking.cz', 'dovolenaparking' => 'DovolenáParking.cz', 'valetparking' => 'ValetParking.cz'); $language_flag = array ('cs'=>'cz', 'en'=>'gb', 'ru'=>'ru', 'de'=>'de', 'ko'=>'ko'); /* dočtení extra services */ require_once (dirname(__FILE__)."/function.array_column.php"); $es = $this->getExtraServices (array_column ($data,'id')); /* require_once (dirname(__FILE__)."/class.ExtraService.php"); if (!is_object ($ExtraService)) $ExtraService = new ExtraService ($this->DB); $es = $ExtraService->get ('es.status<>"deleted"'); */ require_once (dirname(__FILE__)."/function.array_column.php"); $pay = $this->getPayments (array_column ($data,'id')); if (is_array ($data)) foreach ($data as $key=>$value) { # prepis statusu na CS verzi $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; # prepis typu na CS verzi $data[$key]['type_cs'] = $typy[$value['type']] ? $typy[$value['type']] : $data[$key]['type']; $data[$key]['type_short'] = $typy_short[$value['type']] ? $typy_short[$value['type']] : $data[$key]['type_cs']; $data[$key]['type_short'] = $typy_short[$value['type']] ? $typy_short[$value['type']] : $data[$key]['payment_cs'] = $payment[$value['payment']] ? $payments[$value['payment']] : $data[$key]['payment']; $data[$key]['source_cs'] = $source[$value['source']] ? $source[$value['source']] : $data[$key]['source']; $data[$key]['language_flag'] = $language_flag[$value['language']] ? $language_flag[$value['language']] : $data[$key]['language']; $data[$key]['drivername_short'] = preg_replace ('#([a-ž-]+\.? +)+(([a-ž])[a-ž-]+\.?)#iu', '$1$3.', $value['drivername']); if ($es[$value['id']]) $data[$key]['extraservice'] = $es[$value['id']]; /* if ($value['extraservices']) { unset ($es_out); unset ($es_ids); $es_ids = array_flip (explode (',',$value['extraservices'])); foreach ($es as $es_key=>$es_value) { if (isset ($es_ids[$es_value['id']])) $es_out[$es_value['id']] = $es_value; } $data[$key]['extraservice'] = $es_out; } */ if ($pay[$value['id']]) $data[$key]['payment'] = $pay[$value['id']]; $this->cache[$value['id']] = $data[$key]; } return ($data); } # ................................................................... function getExtraServices ($reservation_ids = null) { if (!$reservation_ids) return (false); if (!is_array ($reservation_ids)) $reservation_ids = array ($reservation_ids); /* dočtení extra services */ $this->DB->query ('SELECT re.reservation_id, es.id, es.title, es.status, re.price, es.title_en, es.title_ru, es.price AS price_regular, re.user_id, UNIX_TIMESTAMP(re.done) AS done, u.name AS user_name, es.title_de FROM extraservice es JOIN reservation_extraservice re ON es.id=re.extraservice_id LEFT JOIN user u ON u.id=re.user_id WHERE re.reservation_id IN ("'.implode ('","',$reservation_ids).'");', 'getExtraservices'); while ($radka = $this->DB->getRow ()) $data[$radka['reservation_id']][$radka['id']] = $radka; return ($data); } # ................................................................... function getPayments ($reservation_ids = null) { if (!$reservation_ids) return (false); if (!is_array ($reservation_ids)) $reservation_ids = array ($reservation_ids); $this->DB->query ('SELECT pay.reservation_id, pay.status, UNIX_TIMESTAMP(pay.paid) AS paid, pay.amount, pay.user_id, u.name AS user_name, pay.payment FROM payment pay LEFT JOIN user u ON pay.user_id=u.id WHERE pay.reservation_id IN ("'.implode ('","',$reservation_ids).'") ORDER BY paid;', 'getPayments'); while ($radka = $this->DB->getRow ()) $data[$radka['reservation_id']][] = $radka; return ($data); } # ................................................................... function getRegister ($from = null, $till = null) { $this->DB->query ('SELECT IFNULL(SUM(amount),0) AS amount, COUNT(reservation_id) AS payment_count, IFNULL(SUM(IF(payment="cash",amount,0)),0) AS amount_cash, IFNULL(SUM(IF(payment="card",amount,0)),0) AS amount_card, IFNULL(SUM(IF(payment="auto_cash",amount,0)),0) AS amount_auto_cash, IFNULL(SUM(IF(payment="auto_card",amount,0)),0) AS amount_auto_card FROM payment WHERE status="ok" AND payment<>"gopay" AND paid BETWEEN FROM_UNIXTIME("'.(int) $from.'") AND FROM_UNIXTIME("'.(int) $till.'")', 'getRegister'); $out = $this->DB->getRow (); $this->DB->query ('SELECT IFNULL(SUM(invoice_paid),0) AS fakturace, IFNULL(SUM(gopay_paid),0) AS gopay FROM reservation WHERE invoice_paid>0 AND gopay_paid>0 AND arrival_real BETWEEN FROM_UNIXTIME("'.(int) $from.'") AND FROM_UNIXTIME("'.(int) $till.'")', 'getRegister2'); $out2 = $this->DB->getRow (); $out['fakturace'] = $out2['fakturace']; $out['gopay'] = $out2['gopay']; $out['amount'] = $out['amount'] + $out['fakturace'] + $out['gopay']; return ($out); } # ................................................................... function getWithRepeatingClients ($where = null, $order = null, $limit = null, $limit_from = null, $nocalcrows = false) { //return ($this->get ($where, $order, $limit, $limit_from, $nocalcrows)); $sqlbase_backup = $this->sqlbase; $this->sqlbase = str_replace (array ('FROM reservation r ','WHERE 1 GROUP BY r.id'), array (', repcl.item_count AS repeatingclient FROM reservation r ', 'LEFT JOIN (SELECT "driveremail" AS item, driveremail AS matched, COUNT(driveremail) AS item_count FROM reservation WHERE driveremail<>"" AND status<>"deleted" GROUP BY driveremail HAVING item_count > 1 UNION SELECT "driverphone" AS item, driverphone AS matched, COUNT(driverphone) AS item_count FROM reservation WHERE driverphone<>"" AND status<>"deleted" GROUP BY driverphone HAVING item_count > 1) repcl ON IF(repcl.item="driveremail",r.driveremail,r.driverphone)=repcl.matched WHERE 1 GROUP BY r.id'), $this->sqlbase); $data = $this->get ($where, $order, $limit, $limit_from, $nocalcrows); $this->sqlbase = $sqlbase_backup; return ($data); } # ................................................................... function getInfoAboutWorkers ($where = null, $order = null, $limit = null, $limit_from = null, $nocalcrows = false) { //return ($this->get ($where, $order, $limit, $limit_from, $nocalcrows)); $sqlbase_backup = $this->sqlbase; $this->sqlbase = str_replace (array ('FROM reservation r ','LEFT JOIN club_user cu ON r.club_user_id=cu.id '), array (', GROUP_CONCAT(DISTINCT re.user_id SEPARATOR " ") AS extraservice_workers_group, GROUP_CONCAT(DISTINCT bu.user_id SEPARATOR " ") AS balance_workers_departure, GROUP_CONCAT(DISTINCT bu2.user_id SEPARATOR " ") AS balance_workers_arrival FROM reservation r ', 'LEFT JOIN club_user cu ON r.club_user_id=cu.id LEFT JOIN balance b ON IFNULL(r.departure_real,r.departure) BETWEEN b.started AND b.closed LEFT JOIN balance_user bu ON b.id=bu.balance_id LEFT JOIN balance b2 ON IFNULL(r.arrival_real,r.arrival) BETWEEN b2.started AND b2.closed LEFT JOIN balance_user bu2 ON b2.id=bu2.balance_id '), $this->sqlbase); $data = $this->get ($where, $order, $limit, $limit_from, $nocalcrows); $this->sqlbase = $sqlbase_backup; # get reservation ids foreach ($data as $key=>$value) { //$ids[$key]=$value['id']; unset ($balance_user_ids); $balance_user_ids = explode (' ', $value['balance_workers_departure']); if (is_array ($balance_user_ids)) { foreach ($balance_user_ids as $value_b) { $balance_users_sum[$value_b] = $value_b; $balance_users_departure[$key][] = $value_b; } } unset ($balance_user_ids); $balance_user_ids = explode (' ', $value['balance_workers_arrival']); if (is_array ($balance_user_ids)) { foreach ($balance_user_ids as $value_b) { $balance_users_sum[$value_b] = $value_b; $balance_users_arrival[$key][] = $value_b; } } } # get extra services /* $ids_reverse = array_flip ($ids); require_once (dirname(__FILE__).'/include/class.ReservationExtraservice.php'); if (!is_object ($ReservationExtraservice)) $ReservationExtraservice = new ReservationExtraservice ($this->DB); $extradata = $ReservationExtraservice->getNoCalRows (array ('re.reservation_id IN ('.implode (',', $ids).')'), null, -1); */ /* $extradata = $this->getExtraServices ($ids); if (is_array ($extradata)) { foreach ($extradata as $key=>$value) { $data[$key]['services'] = $value; } } */ # get balance (departure) users if (is_array ($balance_users_sum)) { require_once (dirname(__FILE__).'/class.User.php'); //require_once (dirname(__FILE__).'/include/function.getip.php'); if (!is_object ($User)) $User = new User ($this->DB); $balance_users_data = $User->getId ($balance_users_sum); foreach ($balance_users_departure as $data_id=>$value) { foreach ($value as $balance_user_id) { $data[$data_id]['shift_users_departure'][$balance_user_id] = $User->getId ($balance_user_id, false); } } foreach ($balance_users_arrival as $data_id=>$value) { foreach ($value as $balance_user_id) { $data[$data_id]['shift_users_arrival'][$balance_user_id] = $User->getId ($balance_user_id, false); } } } return ($data); } # ................................................................... function getDrivers ($reservation_ids = null) { if (!$reservation_ids) return (false); if (!is_array ($reservation_ids)) $reservation_ids = array ($reservation_ids); $this->DB->query ('SELECT r.id AS reservation_id, UNIX_TIMESTAMP(r.transfer_e2t) AS e2t_date, r.transfer_e2t_user_id AS e2t_id, u1.name AS e2t_name, UNIX_TIMESTAMP(r.transfer_t2e) AS t2e_date, r.transfer_t2e_user_id AS t2e_id, u2.name AS t2e_name FROM reservation r LEFT JOIN user u1 ON r.transfer_e2t_user_id=u1.id LEFT JOIN user u2 ON r.transfer_t2e_user_id=u2.id WHERE r.id IN ("'.implode ('","',$reservation_ids).'") ORDER BY r.id;', 'getDrivers'); while ($radka = $this->DB->getRow ()) { if ($radka['reservation_id']) $data[$radka['reservation_id']] = array ( 'e2t'=>array ( 'driver_id'=>$radka['e2t_id'], 'driver_name'=>$radka['e2t_name'], 'date'=>$radka['e2t_date'], ), 't2e'=>array ( 'driver_id'=>$radka['t2e_id'], 'driver_name'=>$radka['t2e_name'], 'date'=>$radka['t2e_date'], ) ); } return ($data); } # ................................................................... function getReservation ($reservation_id) { if (!$reservation_id) return (false); $data = $this->getId ($reservation_id,false); if (!is_array ($data)) return (false); # extraservices $es = $this->getExtraServices ($reservation_id); if ($es) $data['extraservice'] = $es[$reservation_id]; # payments $pay = $this->getPayments ($reservation_id); if ($pay) $data['payment'] = $pay[$reservation_id]; # drivers $drivers = $this->getDrivers ($reservation_id); if ($drivers) $data['drivers'] = $drivers[$reservation_id]; return ($data); } # ................................................................... function printReceive ($reservation_id, $user_id = 0) { $data = $this->getReservation ($reservation_id); # if no barcode but greenzone number, create it /* if ($data['greenzone_cardnumber'] && !$data['greenzone_barcode']) { require_once (dirname(__FILE__).'/class.GreenZone.php'); if (!is_object ($GreenZone)) $GreenZone = new GreenZone ($this->DB); $gz = $GreenZone->createCard ($data['id'], " ", " ", $data['greenzone_cardnumber']); $data['greenzone_barcode'] = $gz['barcode']; } */ $receive = $this->DB->getRow ($this->DB->query ('SELECT * FROM receive WHERE reservation_id='.intval ($reservation_id).';', 'printReceive -> Receive')); if ($receive) { $receive_id = ($data['pricelist_id']!=43 ? 'PI' : 'TA').$receive['receive']; $data['receive']['receive_date'] = $receive['receive_date']; } else { $receive_id = 1 + $this->DB->getResult ($this->DB->query ('SELECT receive FROM receive WHERE company="'.($data['pricelist_id']!=43 ? 'premium' : 'tripassistant').'" ORDER BY receive DESC LIMIT 1;', 'printReceive -> Receive last')); $data['receive']['receive_date'] = time (); $this->DB->query ('INSERT INTO receive (reservation_id, receive, company, user_id) VALUES ('.intval ($reservation_id).', '.$receive_id.',"'.($data['pricelist_id']!=43 ? 'premium' : 'tripassistant').'",'.(int) $user_id.');', 'printReceive -> Receive create'); $receive_id = ($data['pricelist_id']!=43 ? 'PI' : 'TA').$receive_id; } if ($data['pricelist_id']!=43) { $whattocount = ($data['invoice_paid'] && $data['price_topay']) ? 'price_topay' : 'price_total'; /* if ($data['invoice_paid'] && $data['price_topay']) { $dph['rates'][21]['dph'] = round (($data['price_topay']/100)*21,2); $dph['rates'][21]['base'] = $data['price_topay']-$dph['rates'][21]['dph']; } else { $dph['rates'][21]['dph'] = round (($data['price_total']/100)*21,2); $dph['rates'][21]['base'] = $data['price_total']-$dph['rates'][21]['dph']; } */ $dph['rates'][21]['base'] = round ($data[$whattocount]/1.21,2); $dph['rates'][21]['dph'] = $data[$whattocount]-$dph['rates'][21]['base']; $dph['dph']=(float) $dph['rates'][21]['dph']; $dph['base']=(float) $dph['rates'][21]['base']; $data['receive']['dph'] = $dph; } $data['receive']['receive_id']=$receive_id; //print_r ($data); return ($data); } # ................................................................... function getPartners ($where = null, $order = null, $limit = -1, $limit_from = null, $nocalcrows = true) { $where[] = 'r.user_id IS NOT NULL'; $this->get ($where, $order, $limit, $limit_from, $nocalcrows); # sub dotaz dle posledniho getu!!! $subsql = str_replace ('SQL_CALC_FOUND_ROWS', '', $this->cachesql); $subsql = preg_replace ('/LIMIT [0-9]+(, *[0-9]+)?/', '', $subsql); $subsql = str_replace (';', '', $subsql); $sql = 'SELECT partners.partner_name, COUNT(partners.id) AS id, SUM(partners.price) AS price, SUM(partners.price_total) AS price_total, SUM(partners.price_extraservice) AS price_extraservice, SUM(partners.gopay_paid) AS gopay_paid, SUM(IF(partners.gopay_paymentsessionid,1,0)) AS gopay_paymentsessionid, SUM(IF(partners.payment="cash",partners.price_total-(partners.gopay_paid+partners.bitpay_paid+partners.invoice_paid),0)) AS paid_cash, SUM(IF(partners.payment="card",partners.price_total-(partners.gopay_paid+partners.bitpay_paid+partners.invoice_paid),0)) AS paid_card, SUM(IF(partners.payment IS NOT NULL,partners.price_total-(partners.gopay_paid+partners.bitpay_paid+partners.invoice_paid),0)) AS paid, SUM(partners.price_penalty) AS price_penalty, SUM(partners.bitpay_paid) AS bitpay_paid, SUM(IF(partners.bitpay_id,1,0)) AS bitpay_id, AVG(partners.price_total) AS price_total_avg, AVG(days) AS days_avg, partners.user_id AS partner_id, SUM(IF(partners.payment IS NULL,partners.price_total-(partners.gopay_paid+partners.bitpay_paid+partners.invoice_paid),0)) AS paid_ck, SUM(partners.invoice_paid) AS invoice_paid, SUM(partners.provision) AS provision FROM ('.$subsql.') partners GROUP BY partners.user_id'; # ORDER BY - pridani trideni if ($order) foreach (explode (',', $order) as $partorder) { if (is_numeric ($partorder)) { if ($partorder < 0) $orders[] = (-1 * $partorder) . ' DESC'; else $orders[] = $partorder; } } if (is_array ($orders)) $sql .= ' ORDER BY ' . implode (', ', $orders); # SQL dotaz $this->DB->query ($sql, get_class ($this) . ' -> getPartners'); return ($this->DB->getAllRows ()); } # ................................................................... function getPromocodes ($where = null, $order = null, $limit = -1, $limit_from = null, $nocalcrows = true) { $where[] = 'r.promo<>""'; $this->get ($where, $order, $limit, $limit_from, $nocalcrows); # sub dotaz dle posledniho getu!!! $subsql = str_replace ('SQL_CALC_FOUND_ROWS', '', $this->cachesql); $subsql = preg_replace ('/LIMIT [0-9]+(, *[0-9]+)?/', '', $subsql); $subsql = str_replace (';', '', $subsql); $sql = 'SELECT subsql.promo, COUNT(subsql.id) AS id, SUM(subsql.price) AS price, SUM(subsql.price_total) AS price_total, SUM(subsql.price_extraservice) AS price_extraservice, SUM(subsql.gopay_paid) AS gopay_paid, SUM(IF(subsql.gopay_paymentsessionid,1,0)) AS gopay_paymentsessionid, SUM(IF(subsql.payment="cash",subsql.price_total-(subsql.gopay_paid+subsql.bitpay_paid+subsql.invoice_paid),0)) AS paid_cash, SUM(IF(subsql.payment="card",subsql.price_total-(subsql.gopay_paid+subsql.bitpay_paid+subsql.invoice_paid),0)) AS paid_card, SUM(IF(subsql.payment IS NOT NULL,subsql.price_total-(subsql.gopay_paid+subsql.bitpay_paid+subsql.invoice_paid),0)) AS paid, SUM(subsql.price_penalty) AS price_penalty, SUM(subsql.bitpay_paid) AS bitpay_paid, SUM(IF(subsql.bitpay_id,1,0)) AS bitpay_id, AVG(subsql.price_total) AS price_total_avg, AVG(days) AS days_avg, subsql.user_id AS partner_id, subsql.pricelist_id, GROUP_CONCAT(DISTINCT subsql.pricelist_title SEPARATOR ", ") AS pricelist_title, SUM(IF(subsql.payment IS NULL,subsql.price_total-(subsql.gopay_paid+subsql.bitpay_paid+subsql.invoice_paid),0)) AS paid_ck, SUM(subsql.invoice_paid) AS invoice_paid, SUM(subsql.provision) AS provision FROM ('.$subsql.') subsql GROUP BY subsql.promo'; # ORDER BY - pridani trideni if ($order) foreach (explode (',', $order) as $partorder) { if (is_numeric ($partorder)) { if ($partorder < 0) $orders[] = (-1 * $partorder) . ' DESC'; else $orders[] = $partorder; } } if (is_array ($orders)) $sql .= ' ORDER BY ' . implode (', ', $orders); # SQL dotaz $this->DB->query ($sql, get_class ($this) . ' -> getPromocodes'); return ($this->DB->getAllRows ()); } # ................................................................... # vrati definovane agregovane vysledky, tedy soucet, prumer, pocet atd vsech vysledku bez ohledu na limit # bere se podle posledniho getu!!! function getGroupTotal () { # sub dotaz dle posledniho getu!!! $subsql = str_replace ('SQL_CALC_FOUND_ROWS', '', $this->cachesql); $subsql = eregi_replace ('LIMIT [0-9]+(, *[0-9]+)?', '', $subsql); $subsql = str_replace (';', '', $subsql); $sql = 'SELECT COUNT(gt.id) AS id, SUM(gt.price) AS price, SUM(gt.price_total) AS price_total, SUM(gt.price_extraservice) AS price_extraservice, SUM(gt.gopay_paid) AS gopay_paid, SUM(IF(gt.gopay_paymentsessionid,1,0)) AS gopay_paymentsessionid, SUM(IF(gt.payment="cash",gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0)) AS paid_cash, SUM(IF(gt.payment="card",gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0)) AS paid_card, SUM(IF(gt.payment IS NOT NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0)) AS paid, SUM(gt.price_penalty) AS price_penalty, SUM(gt.bitpay_paid) AS bitpay_paid, SUM(IF(gt.bitpay_id,1,0)) AS bitpay_id, SUM(IF(gt.payment IS NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0)) AS paid_ck, SUM(gt.invoice_paid) AS invoice_paid, SUM(gt.provision) AS provision, COUNT(gt.review_overall) AS review_count, AVG(gt.review_overall) AS review_overall, AVG(NULLIF(gt.review_stuff,0)) AS review_stuff, AVG(NULLIF(gt.review_navigation,0)) AS review_navigation, AVG(NULLIF(gt.review_transfer,0)) AS review_transfer, AVG(NULLIF(gt.review_cleaning,0)) AS review_cleaning, AVG(NULLIF(gt.review_valet,0)) AS review_valet, AVG(gt.review_recommend) AS review_recommend, SUM(review_show) AS review_show, SUM(IF(gt.company="premium",IF(gt.payment IS NOT NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0),0)) AS company_premium, SUM(IF(gt.company="tripassistant",IF(gt.payment IS NOT NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid+gt.invoice_paid),0),0)) AS company_tripassistant, AVG(gt.days) AS days, SUM(gt.receive_premium_total) AS receive_premium_total, SUM(gt.receive_premium_cash) AS receive_premium_cash, SUM(gt.receive_premium_card) AS receive_premium_card, SUM(gt.receive_premium_gopay) AS receive_premium_gopay, SUM(gt.receive_premium_invoice) AS receive_premium_invoice, SUM(gt.receive_tripassistant_total) AS receive_tripassistant_total, SUM(gt.receive_tripassistant_cash) AS receive_tripassistant_cash, SUM(gt.receive_tripassistant_card) AS receive_tripassistant_card, SUM(gt.receive_tripassistant_gopay) AS receive_tripassistant_gopay, SUM(gt.receive_tripassistant_invoice) AS receive_tripassistant_invoice, SUM(price_topay) AS price_topay, AVG(NULLIF(gt.review_infinity,0)) AS review_infinity FROM ('.$subsql.') gt;'; # SQL dotaz $this->DB->query ($sql, get_class ($this) . ' -> getGroupTotal'); return ($this->DB->getRow ()); } # ................................................................... function createHash ($id = null) { if (!intval ($id)) { return (null); } return (substr (md5('2fhdnPDBAteL8lRF'.$id), 0, 6)); } # ................................................................... function getHash ($hash = null) { if (!$hash) { return (null); } return ($this->getReservation($this->findId ('LEFT(MD5(CONCAT("2fhdnPDBAteL8lRF", r.id)),6)="' . mysql_real_escape_string ($hash) . '"'))); } # ................................................................... function getCapacityDailyOutlook ($days=30) { # nactu obsazenost do ted $rightnow = $this->DB->getRow($this->DB->query('SELECT COUNT(*) AS pocet, SUM(IF(r.type="cheap",1,0)) AS venku, SUM(IF(r.type<>"cheap",1,0)) AS garaz FROM reservation r WHERE r.status="verified" AND r.parking_location="tuchomerice";', get_class ($this) . ' -> getCapacityDailyOutlook -> rightnow')); # nactu si prirustky $increments = $this->DB->getAllRows($this->DB->query('SELECT t.datum, SUM(t.pocet) AS pocet, SUM(t.prijezd) AS prijezd, SUM(t.odjezd) AS odjezd, SUM(t.prijezd_venku) AS prijezd_venku, SUM(t.odjezd_venku) AS odjezd_venku, SUM(t.prijezd_garaz) AS prijezd_garaz, SUM(t.odjezd_garaz) AS odjezd_garaz FROM (SELECT DATE_FORMAT(r.departure,"%Y-%m-%d") AS datum, COUNT(*) AS pocet, 0 AS prijezd, COUNT(*) AS odjezd, 0 AS prijezd_venku, SUM(IF(r.type="cheap",1,0)) AS odjezd_venku, 0 AS prijezd_garaz, SUM(IF(r.type<>"cheap",1,0)) AS odjezd_garaz FROM reservation r WHERE r.departure>=NOW() AND r.departure<=DATE_ADD(NOW(), INTERVAL '.(int) $days.' DAY) AND r.status IN ("verified","new","paid","pending") GROUP BY datum UNION ALL SELECT DATE_FORMAT(r.arrival,"%Y-%m-%d") AS datum, COUNT(*) AS pocet, COUNT(*) AS prijezd, 0 AS odjezd, SUM(IF(r.type="cheap",1,0)) AS prijezd_venku, 0 AS odjezd_venku, SUM(IF(r.type<>"cheap",1,0)) AS prijezd_garaz, 0 AS odjezd_garaz FROM reservation r WHERE r.arrival>=NOW() AND r.arrival<=DATE_ADD(NOW(), INTERVAL '.(int) $days.' DAY) AND r.status IN ("verified","new","paid","pending") GROUP BY datum) t GROUP BY t.datum ORDER BY t.datum;', get_class ($this) . ' -> getCapacityDailyOutlook -> outlook'), 'datum'); $capacity['europort'] = $this->DB->getResult ($this->DB->query ('SELECT COUNT(id) FROM reservation WHERE status IN ("new","pending","verified","paid") AND parking_location="airport";')); $capacity['venku'] = $rightnow['venku']; $capacity['garaz'] = $rightnow['garaz']; $capacity['total'] = $rightnow['pocet'] + $capacity['europort']; $capacity['europort_longterm'] = $this->DB->getResult ($this->DB->query ('SELECT COUNT(id) FROM reservation WHERE status IN ("pending","verified","paid") AND parking_location="longterm";')); unset ($output); $output[1] = array ( 'datum'=>date ('Y-m-d'), 'datum_ts'=>time (), 'prijezd_venku'=>0, 'prijezd_garaz'=>0, 'prijezd_total'=>0, 'odjezd_venku'=>0, 'odjezd_garaz'=>0, 'odjezd_total'=>0, 'pohyb'=>0, 'venku'=>$capacity['venku'], 'garaz'=>$capacity['garaz'], 'total'=>$capacity['total'], 'europort'=>$capacity['europort'], 'europort_longterm'=>$capacity['europort_longterm'] ); if (is_array ($increments)) { $start = time (); $end = strtotime ('+'.intval ($days).' days', $start); for ($i=$start; $i<=$end; $i=strtotime ('+1 day',$i)) { if ($increments[date ('Y-m-d',$i)]) { $row = $increments[date ('Y-m-d',$i)]; $capacity['venku'] += $row['prijezd_venku']-$row['odjezd_venku']; $capacity['garaz'] += $row['prijezd_garaz']-$row['odjezd_garaz']; $capacity['total'] += $row['prijezd']-$row['odjezd']; } else $row = null; $output[$i] = array ( 'datum'=>date ('Y-m-d',$i), 'datum_ts'=>$i, 'prijezd_venku'=>$row ? $row['prijezd_venku'] : 0, 'prijezd_garaz'=>$row ? $row['prijezd_garaz'] : 0, 'prijezd_total'=>$row ? $row['prijezd'] : 0, 'odjezd_venku'=>$row ? $row['odjezd_venku'] : 0, 'odjezd_garaz'=>$row ? $row['odjezd_garaz'] : 0, 'odjezd_total'=>$row ? $row['odjezd'] : 0, 'pohyb'=>$row ? ($row['prijezd']+$row['odjezd']) : 0, 'venku'=>$capacity['venku'], 'garaz'=>$capacity['garaz'], 'total'=>$capacity['total'], 'europort'=>$capacity['europort'] ); } # today actual without new arrivals/departures /* $output[$start]['total'] = $rightnow['pocet']; $output[$start]['venku'] = $rightnow['venku']; $output[$start]['garaz'] = $rightnow['garaz']; */ } return ($output); } # ................................................................... function getCapacityOutlook ($from=null, $till=null, $interval='d') { $from = (int) $from ? (int) $from : time (); $till = (int) $till ? (int) $till : time (); # nactu obsazenost do ted $rightnow = $this->DB->getRow($this->DB->query('SELECT COUNT(*) AS pocet, SUM(IF(r.type="cheap",1,0)) AS venku, SUM(IF(r.type<>"cheap",1,0)) AS garaz FROM reservation r WHERE r.status IN ("verified","closed") AND FROM_UNIXTIME("'.$from.'") BETWEEN IFNULL(r.arrival_real,r.arrival) AND IFNULL(r.departure_real,r.departure);', get_class ($this) . ' -> getCapacityOutlook -> start')); # nactu si prirustky $increments = $this->DB->getAllRows($this->DB->query('SELECT t.datum, SUM(t.pocet) AS pocet, SUM(t.prijezd) AS prijezd, SUM(t.odjezd) AS odjezd, SUM(t.prijezd_venku) AS prijezd_venku, SUM(t.odjezd_venku) AS odjezd_venku, SUM(t.prijezd_garaz) AS prijezd_garaz, SUM(t.odjezd_garaz) AS odjezd_garaz FROM (SELECT DATE_FORMAT(r.departure,"%Y-%m-%d") AS datum, COUNT(*) AS pocet, 0 AS prijezd, COUNT(*) AS odjezd, 0 AS prijezd_venku, SUM(IF(r.type="cheap",1,0)) AS odjezd_venku, 0 AS prijezd_garaz, SUM(IF(r.type<>"cheap",1,0)) AS odjezd_garaz FROM reservation r WHERE IFNULL(r.departure_real,r.departure) BETWEEN FROM_UNIXTIME("'.$from.'") AND FROM_UNIXTIME("'.$till.'") AND r.status IN ("verified","closed","new","paid","pending") GROUP BY datum UNION ALL SELECT DATE_FORMAT(r.arrival,"%Y-%m-%d") AS datum, COUNT(*) AS pocet, COUNT(*) AS prijezd, 0 AS odjezd, SUM(IF(r.type="cheap",1,0)) AS prijezd_venku, 0 AS odjezd_venku, SUM(IF(r.type<>"cheap",1,0)) AS prijezd_garaz, 0 AS odjezd_garaz FROM reservation r WHERE IFNULL(r.arrival_real,r.arrival) BETWEEN FROM_UNIXTIME("'.$from.'") AND FROM_UNIXTIME("'.$till.'") AND r.status IN ("verified","closed","new","paid","pending") GROUP BY datum) t GROUP BY t.datum ORDER BY t.datum;', get_class ($this) . ' -> getCapacityDailyOutlook -> outlook'), 'datum'); $capacity['venku'] = $rightnow['venku']; $capacity['garaz'] = $rightnow['garaz']; $capacity['total'] = $rightnow['pocet']; unset ($output); /*$output[1] = array ( 'datum'=>date ('Y-m-d',$from), 'datum_ts'=>$from, 'prijezd_venku'=>0, 'prijezd_garaz'=>0, 'prijezd_total'=>0, 'odjezd_venku'=>0, 'odjezd_garaz'=>0, 'odjezd_total'=>0, 'pohyb'=>0, 'venku'=>$capacity['venku'], 'garaz'=>$capacity['garaz'], 'total'=>$capacity['total'] );*/ if (is_array ($increments)) { for ($i=$from; $i<=$till; $i=strtotime ('+1 day',$i)) { if ($increments[date ('Y-m-d',$i)]) { $row = $increments[date ('Y-m-d',$i)]; $capacity['venku'] += $row['prijezd_venku']-$row['odjezd_venku']; $capacity['garaz'] += $row['prijezd_garaz']-$row['odjezd_garaz']; $capacity['total'] += $row['prijezd']-$row['odjezd']; } else $row = null; $output[$i] = array ( 'datum'=>date ('Y-m-d',$i), 'datum_ts'=>$i, 'prijezd_venku'=>$row ? $row['prijezd_venku'] : 0, 'prijezd_garaz'=>$row ? $row['prijezd_garaz'] : 0, 'prijezd_total'=>$row ? $row['prijezd'] : 0, 'odjezd_venku'=>$row ? $row['odjezd_venku'] : 0, 'odjezd_garaz'=>$row ? $row['odjezd_garaz'] : 0, 'odjezd_total'=>$row ? $row['odjezd'] : 0, 'pohyb'=>$row ? ($row['prijezd']+$row['odjezd']) : 0, 'venku'=>$capacity['venku'], 'garaz'=>$capacity['garaz'], 'total'=>$capacity['total'] ); } # today actual without new arrivals/departures /* $output[$from]['total'] = $rightnow['pocet']; $output[$from]['venku'] = $rightnow['venku']; $output[$from]['garaz'] = $rightnow['garaz']; */ } return ($output); } # ................................................................... function getReservationOutlook ($from=null, $till=null, $interval='d') { $from = (int) $from ? (int) $from : time (); $till = (int) $till ? (int) $till : time (); # nactu si prirustky $increments = $this->DB->getAllRows($this->DB->query(' SELECT DATE_FORMAT(r.created,"%Y-%m-%d") AS datum, COUNT(r.id) AS count_total, SUM(IF(r.source="smileparking" AND r.promo<>"dovolenaparking" AND r.pricelist_id<>43 AND (u.status<>"partner" OR u.status IS NULL),1,0)) AS count_smileparking, SUM(IF(r.pricelist_id=43 OR r.promo="dovolenaparking",1,0)) AS count_dovolenaparking, SUM(IF(r.source="valetparking",1,0)) AS count_valetparking, SUM(IF(u.status="partner",1,0)) AS count_partner FROM reservation r LEFT JOIN user u ON r.user_id=u.id WHERE r.created BETWEEN FROM_UNIXTIME("'.$from.'") AND FROM_UNIXTIME("'.$till.'") AND r.status IN ("verified","closed","new","paid","pending") GROUP BY datum ORDER BY datum;', get_class ($this) . ' -> getReservationOutlook'), 'datum'); if (is_array ($increments)) { for ($i=$from; $i<=$till; $i=strtotime ('+1 day',$i)) { if ($increments[date ('Y-m-d',$i)]) $output[$i] = $increments[date ('Y-m-d',$i)]; else $output[$i] = array ( 'count_total'=>0, 'count_smileparking'=>0, 'count_dovolenaparking'=>0, 'count_valetparking'=>0, ); $output[$i]['datum']=date ('Y-m-d',$i); $output[$i]['datum_ts']=$i; } } return ($output); } # ................................................................... function getArrivalsDepartures ($where=null, $from=null, $till=null, $grouper='day') { if (!$from || !$till || $from>=$till) return (null); # clean up of some filters unset ($where['re']); unset ($where['daterange']); $where[] = '1'; switch ($grouper) { case 'dayofweek': $grouper_meta['date'] = 'N'; $grouper_meta['groupby'] = 'DAYOFWEEK'; break; case 'hourofday': case 'hour': $grouper_meta['date'] = 'H'; $grouper_meta['groupby'] = 'HOUR'; break; case 'day': default: $grouper_meta['date'] = 'Ymd'; $grouper_meta['groupby'] = 'TO_DAYS'; } $result = $this->DB->query ('SELECT SUM(a.arrivals) AS arrivals, SUM(a.departures) AS departures, UNIX_TIMESTAMP(a.hitdate) AS hitdate, SUM(a.person_arrival) AS persons_arrival, SUM(a.person_departure) AS persons_departure FROM (SELECT COUNT(id) AS arrivals, 0 AS departures, IFNULL(arrival_real,arrival) AS hitdate, SUM(person) as person_arrival, 0 AS person_departure FROM reservation r WHERE IFNULL(arrival_real,arrival) BETWEEN FROM_UNIXTIME("' . $from . '") AND FROM_UNIXTIME("' . $till . '") AND '.implode (' AND ', $where).' GROUP BY '.$grouper_meta['groupby'].'(IFNULL(arrival_real,arrival)) UNION ALL SELECT 0 AS arrivals, COUNT(id) AS departures, IFNULL(departure_real,departure) AS hitdate, 0 AS person_arrival, SUM(person) as person_departure FROM reservation r WHERE IFNULL(departure_real,departure) BETWEEN FROM_UNIXTIME("' . $from . '") AND FROM_UNIXTIME("' . $till . '") AND '.implode (' AND ', $where).' GROUP BY '.$grouper_meta['groupby'].'(IFNULL(departure_real,departure))) a GROUP BY '.$grouper_meta['groupby'].'(hitdate) ORDER BY hitdate;', get_class ($this) . ' -> getArrivalsDepartures'); if ($grouper=='dayofweek') { $dayofweek = array (1=>'Pondělí','Úterý','Středa','Čtvrtek','Pátek','Sobota','Nedělě'); for ($i=1; $i <= 7; $i++) $output[$i] = array ('timestamp'=>$i, 'arrivals'=>0, 'departures'=>0, 'persons'=>0, 'title'=>$dayofweek[$i]); } else for ($i=$from; $i <= $till; $i=strtotime ('+1 '.($grouper=='hour' ? 'hour' : 'day'), $i)) $output[date ($grouper_meta['date'], $i)] = array ('timestamp'=>$i, 'arrivals'=>0, 'departures'=>0, 'persons'=>0); while ($row=$this->DB->getRow ($result)) { $output[date ($grouper_meta['date'], $row['hitdate'])]['arrivals'] = $row['arrivals']; $output[date ($grouper_meta['date'], $row['hitdate'])]['departures'] = $row['departures']; $output[date ($grouper_meta['date'], $row['hitdate'])]['timestamp'] = $row['hitdate']; $output[date ($grouper_meta['date'], $row['hitdate'])]['persons_arrival'] = $row['persons_arrival']; $output[date ($grouper_meta['date'], $row['hitdate'])]['persons_departure'] = $row['persons_departure']; if ($grouper=='dayofweek') $output[date ($grouper_meta['date'], $row['hitdate'])]['title'] = $dayofweek[date ($grouper_meta['date'], $row['hitdate'])]; } return ($output); } /* function getArrivalsDepartures ($from=null, $till=null) { if (!$from || !$till || $from<=$till) return (null); $result = $this->DB->query ('SELECT SUM(a.arrivals) AS arrivals, SUM(a.departures) AS departures, UNIX_TIMESTAMP(a.hitdate) AS hitdate FROM (SELECT COUNT(id) AS arrivals, 0 AS departures, arrival AS hitdate FROM reservation WHERE arrival BETWEEN FROM_UNIXTIME("'.$from.'") AND FROM_UNIXTIME("'.$till.'") GROUP BY TO_DAYS(arrival) UNION ALL SELECT 0 AS arrivals, COUNT(id) AS departures, departure AS hitdate FROM reservation WHERE departure BETWEEN FROM_UNIXTIME("'.$from.'") AND FROM_UNIXTIME("'.$till.'") GROUP BY TO_DAYS(departure)) a GROUP BY TO_DAYS(hitdate) ORDER BY hitdate;'); for ($i=$from; $i <= $till; strtotime ('+1 day', $i)) $output[date ('Ymd', $i)] = array ('timestamp'=>$i, 'arrivals'=>0, 'departures'=>0); while ($row=$this->DB->getRow ($result)) { $output[date ('Ymd', $row['hitdate'])]['arrivals'] = $row['arrivals']; $output[date ('Ymd', $row['hitdate'])]['departures'] = $row['departures']; } return ($output); } */ # ................................................................... /* function getStatsDayOfWeek ($where=null, $datetype='created') { # clean up of some filters //unset ($where['re']); # sub dotaz dle posledniho getu!!! $this->getNoCalcRows ($where, null, -1); $subsql = str_replace ('SQL_CALC_FOUND_ROWS', '', $this->cachesql); $subsql = eregi_replace ('LIMIT [0-9]+(, *[0-9]+)?', '', $subsql); $subsql = str_replace (';', '', $subsql); $sql = 'SELECT COUNT(gt.id) AS id, SUM(gt.price) AS price, SUM(gt.price_total) AS price_total, SUM(gt.price_extraservice) AS price_extraservice, SUM(gt.gopay_paid) AS gopay_paid, SUM(IF(gt.gopay_paymentsessionid,1,0)) AS gopay_paymentsessionid, SUM(IF(gt.payment="cash",gt.price_total-(gt.gopay_paid+gt.bitpay_paid),0)) AS paid_cash, SUM(IF(gt.payment="card",gt.price_total-(gt.gopay_paid+gt.bitpay_paid),0)) AS paid_card, SUM(IF(gt.payment IS NOT NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid),0)) AS paid, SUM(gt.price_penalty) AS price_penalty, SUM(gt.bitpay_paid) AS bitpay_paid, SUM(IF(gt.bitpay_id,1,0)) AS bitpay_id, SUM(IF(gt.payment IS NULL,gt.price_total-(gt.gopay_paid+gt.bitpay_paid),0)) AS paid_ck FROM ('.$subsql.') gt GROUP BY weekday(gt.'.$datetype.');'; # SQL dotaz return ($this->DB->getAllRows($this->DB->query ($sql, get_class ($this) . ' -> getStatsDayOfWeek'))); } */ # ................................................................... function getReservationJournal ($reservation_id=null) { //if (!$reservation_id) // return (false); # nactu obsazenost do ted return $this->DB->getAllRows($this->DB->query('SELECT rj.*, u.name AS user_name FROM reservation_journal rj JOIN user u ON rj.user_id=u.id '.(intval ($reservation_id) ? 'WHERE rj.reservation_id="'.intval ($reservation_id).'"' : '').' ORDER BY rj.changed DESC, rj.reservation_id;', get_class ($this) . ' -> getReservationJournal')); } # ................................................................... function getEuroport2Tuchomerice ($where=null, $order=-6, $limit=null, $limit_from=null) { return ($this->getNoCalcRows (array_merge ($where, array ('r.status IN ("new","pending","paid")', 'r.parking_location="airport"')), $order ? $order : -6, $limit, $limit_from)); } # ................................................................... function getTuchomerice2Europort ($where=null, $order=6, $limit=null, $limit_from=null) { return ($this->getNoCalcRows (array_merge ($where, array ('r.status IN ("verified","paid")', 'r.parking_location="tuchomerice"', 're.extraservice_id=2')), $order ? $order : 6, $limit, $limit_from)); } # ................................................................... function getTransferOverview ($where=null, $order=null, $limit=null, $limit_from=null) { $sql = 'SELECT SQL_CALC_FOUND_ROWS UNIX_TIMESTAMP(tran.transfer_date) AS transfer_date, u.name As driver_name, u.id AS driver_id, SUM(tran.trips_e2t) AS trips_e2t, SUM(tran.trips_t2e) AS trips_t2e, IF(SUM(tran.trips_e2t)>SUM(tran.trips_t2e),SUM(tran.trips_e2t),SUM(tran.trips_t2e)) AS roundtrips FROM (SELECT COUNT(r.id) AS trips_e2t, 0 AS trips_t2e, r.transfer_e2t AS transfer_date, r.transfer_e2t_user_id AS driver_id, "e2t" AS direction FROM reservation r WHERE transfer_e2t_user_id IS NOT NULL GROUP BY transfer_e2t_user_id, DATE(transfer_e2t) UNION ALL SELECT 0 AS trips_e2t, COUNT(r.id) AS trips_t2t, r.transfer_t2e AS transfer_date, r.transfer_t2e_user_id AS driver_id, "t2e" AS direction FROM reservation r WHERE transfer_t2e_user_id IS NOT NULL GROUP BY transfer_t2e_user_id, DATE(transfer_t2e)) tran JOIN user u ON u.id=tran.driver_id WHERE 1 GROUP BY DATE(tran.transfer_date), tran.driver_id'; return ($this->getCustom ($sql, $where, $order, $limit, $limit_from)); } # ................................................................... function getTransferOverviewDrivers ($data=null) { foreach ($data as $trip) { $output[$trip['driver_id']]['driver_name'] = $trip['driver_name']; $output[$trip['driver_id']]['roundtrips'] += $trip['roundtrips']; $output[$trip['driver_id']]['trips_e2t'] += $trip['trips_e2t']; $output[$trip['driver_id']]['trips_t2e'] += $trip['trips_t2e']; $output[$trip['driver_id']]['days']++; } foreach ($output as $driver_id=>$value) { $output[$driver_id]['roundtrips_avg'] = $value['roundtrips']/$value['days']; $output[$driver_id]['trips_e2t_avg'] = $value['trips_e2t']/$value['days']; $output[$driver_id]['trips_t2e_avg'] = $value['trips_t2e']/$value['days']; } return ($output); } # ................................................................... function setTransfer ($type='e2t', $reservation_id=null, $driver=null, $slot='', $when=null, $user_id) { $set['transfer_'.$type]='FROM_UNIXTIME("'.((int) $when ? (int) $when : time()).'")'; $set['transfer_'.$type.'_user_id']='"'.(int) $driver.'"'; $set['parking_location'] = $type=='e2t' ? '"tuchomerice"' : '"airport"'; $set['parking'] = '"'.mysql_real_escape_string ($slot).'"'; $set['vinciparkingcard'] = '""'; $set['status']='"verified"'; //print_r ($set);echo ('
'.$reservation_id); exit ('1'); return ($this->set ($set, (int) $reservation_id, $user_id)); } # ................................................................... function sendReviewRequest ($reservation_id=null, $data = null) { global $Smarty; static $mail; if (!$reservation_id) return (false); if (!$data) $data = $this->getId ($reservation_id, false); if ($data['review_overall']===null) { require_once (dirname(__FILE__).'/../lib/phpmailer/class.phpmailer.php'); if (!is_object ($mail)) $mail = new phpmailer(); $mail->CharSet = "UTF-8"; $mail->Encoding = "base64"; $mail->WordWrap = 75; $mail->SetLanguage ('cs', dirname(__FILE__).'/../lib/phpmailer/language/'); $mail->IsHTML(true); # email $Smarty->assign ('review_email_data', $data); $mail->Sender = 'info@smileparking.cz'; $mail->From = 'info@smileparking.cz'; $mail->FromName = 'SMILE PARKING'; $mail->Body = $Smarty->fetch($data['language'].'.email.reservation-review-email.html'); $mail->Subject = $Smarty->get_template_vars('EMAIL_SUBJECT'); $mail->AddAddress($data['driveremail'], $data['drivername']); //$mail->AddAddress('jan.mensik@gmail.com', $data['drivername']); $mail->send (); $mail->ClearAddresses (); $mail->ClearCCs (); $mail->ClearBCCs (); $mail->ClearAllRecipients (); $this->set (array ('review_sent'=>1),$reservation_id); } } # ................................................................... function set ($set, $ids = null, $user_id = null, $journal_note=null) { if (!is_array ($set)) return (false); # current data for journal if (is_array ($ids)) { foreach ($ids as $value) $reservations[$value]['old'] = $this->getReservation ($value); unset ($value); } elseif ($ids) $reservations[$ids]['old'] = $this->getReservation ($ids); # presunu extraservices $extraservice = $set['extraservice']; unset ($set['extraservice']); # GreenZone clear up if ($set['greenzone_id']) { $greenzone_id = $set['greenzone_id'] ? $set['greenzone_id'] : $reservation[$ids]['old']['greenzone_id']; unset ($set['greenzone_id']); } if ($user_id) $set['alteruser_id'] = $user_id; # ulozeni beznych dat if (@count ($set) >= 1) $next_id = parent::set ($set, $ids); if (!$ids) { $ids = $next_id; $new = true; } # _______________________________________________________________ # ExtraServices if (is_array ($extraservice) && is_numeric ($ids)) { # promazu existujici u objednavky $this->DB->query ('DELETE FROM reservation_extraservice WHERE reservation_id="'.(int) $ids.'";'); # ulozim si existujici if (count ($extraservice) > 0) { foreach ($extraservice as $key=>$value) { $es_more = is_array ($value) ? true : false; if ($es_more) $sql[] = '(' . (int) $ids . ', "' . (int) $key . '",' . $value['price'] . ', ' . $value['user_id'] . ', '.$value['done'].')'; else $sql[] = '(' . (int) $ids . ', "' . (int) $key . '",' . $value . ')'; } if ($es_more) $this->DB->query ('INSERT INTO reservation_extraservice (reservation_id, extraservice_id, price, user_id, done) VALUES ' . implode (', ', $sql) . ';'); else $this->DB->query ('INSERT INTO reservation_extraservice (reservation_id, extraservice_id, price) VALUES ' . implode (', ', $sql) . ';'); } } # _______________________________________________________________ # Google Calendar + JOURNAL $id_array = is_array ($ids) ? $ids : array ($ids); unset ($this->cache); foreach ($id_array as $res_id) { $res = $this->getReservation ($res_id); $reservations[$res_id]['new'] = $res; /* if ($res_id==2420) { print_r ($reservations[$res_id]['old']); echo ('
'); print_r ($reservations[$res_id]['new']); exit (); //continue; } */ # JOURNAL if ($reservations[$res_id]['old']) { $journal_grouped = null; $journal = null; foreach ($reservations[$res_id]['old'] as $key=>$value) { # tyto neloguji, nebo samostatne if (in_array ($key, array ('extraservice','extraservices','price_topay','type_cs','type_short','status_cs','alteruser_id','valetparking','price_extraservice','price_total','hash','pricelist_promo','pricelist_title','partner_name','days','recentupdate','club_admin_security'))) continue; # ostatni elseif ($reservations[$res_id]['old'][$key] != $reservations[$res_id]['new'][$key]) { $journal = array ( 'reservation_id'=>'"'.$res_id.'"', 'item'=>'"'.$key.'"', 'newvalue'=>'"'.mysql_real_escape_string ($reservations[$res_id]['new'][$key]).'"', 'oldvalue'=>'"'.mysql_real_escape_string ($reservations[$res_id]['old'][$key]).'"', 'user_id'=>($user_id ? '"'.$user_id.'"' : 'null'), 'external_id'=>'0', 'note'=>'"'.$journal_note.'"', 'specialvalue'=>'0' ); if ($key=='pricelist_id') { $journal['external_id'] = '"'.intval ($pricelist_id).'"'; } if ($key=='type' && $reservations[$res_id]['new']['price'] != $reservations[$res_id]['old']['price']) { $journal['specialvalue'] = $reservations[$res_id]['new']['price'] - $reservations[$res_id]['old']['price']; } $journal_grouped[] = implode (',',$journal); /*if ($key=='status' && $reservations[$res_id]['new']['status']=='closed') { $this->sendReviewRequest ($res_id, $res); }*/ } } # EXTRASERVICES # change / remove if (is_array ($reservations[$res_id]['old']['extraservice'])) { foreach ($reservations[$res_id]['old']['extraservice'] as $eskey=>$esvalue) { /*if ($res_id==2034) { print_r ($reservations[$res_id]['new']['extraservice']);echo ('
'); print_r ($reservations[$res_id]['old']['extraservice']);exit ('
'); }*/ # remove if (!$reservations[$res_id]['new']['extraservice'] || !$reservations[$res_id]['new']['extraservice'][$eskey]) { $journal_grouped[] = implode (',', array ( 'reservation_id'=>'"'.$res_id.'"', 'item'=>'"extraservice"', 'newvalue'=>'"'.$reservations[$res_id]['old']['extraservice'][$eskey]['title'].'"', 'oldvalue'=>'"removed"', 'user_id'=>($user_id ? '"'.$user_id.'"' : 'null'), 'external_id'=>'"'.$eskey.'"', 'note'=>'"'.$journal_note.'"', 'specialvalue'=>'"'.-1*$reservations[$res_id]['old']['extraservice'][$eskey]['price'].'"' )); } # change else { if ($reservations[$res_id]['new']['extraservice'][$eskey]['price'] != $reservations[$res_id]['old']['extraservice'][$eskey]['price']) $journal_grouped[] = implode (',', array ( 'reservation_id'=>'"'.$res_id.'"', 'item'=>'"extraservice.price"', 'newvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['price'].'"', 'oldvalue'=>'"'.$reservations[$res_id]['old']['extraservice'][$eskey]['price'].'"', 'user_id'=>($user_id ? '"'.$user_id.'"' : 'null'), 'external_id'=>'"'.$eskey.'"', 'note'=>'"'.$journal_note.'"', 'specialvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['price'].'"' )); if ($reservations[$res_id]['new']['extraservice'][$eskey]['done'] != $reservations[$res_id]['old']['extraservice'][$eskey]['done']) $journal_grouped[] = implode (',', array ( 'reservation_id'=>'"'.$res_id.'"', 'item'=>'"extraservice.done"', 'newvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['done'].'"', 'oldvalue'=>'"'.($reservations[$res_id]['new']['extraservice'][$eskey]['done'] ? 'added' : 'removed').'"', 'user_id'=>($user_id ? '"'.$user_id.'"' : 'null'), 'external_id'=>'"'.$eskey.'"', 'note'=>'"'.$journal_note.'"', 'specialvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['price'].'"' )); } } } # add if (is_array ($reservations[$res_id]['new']['extraservice'])) { foreach ($reservations[$res_id]['new']['extraservice'] as $eskey=>$esvalue) { if (!$reservations[$res_id]['old']['extraservice'][$eskey]) { $journal_grouped[] = implode (',', array ( 'reservation_id'=>'"'.$res_id.'"', 'item'=>'"extraservice"', 'newvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['title'].'"', 'oldvalue'=>'"added"', 'user_id'=>($user_id ? '"'.$user_id.'"' : 'null'), 'external_id'=>'"'.$eskey.'"', 'note'=>'"'.$journal_note.'"', 'specialvalue'=>'"'.$reservations[$res_id]['new']['extraservice'][$eskey]['price'].'"' )); } } } if ($journal_grouped) { $this->DB->query ('INSERT INTO reservation_journal (reservation_id,item,newvalue,oldvalue,user_id,external_id,note,specialvalue) VALUES (' . implode ('), (', $journal_grouped) . ');'); } } /* # smazu z kalendare if (($res['googlecalendar_arrival'] || $res['googlecalendar_departure']) && in_array ($res['status'], array ('canceled','deleted'))) { if ($res['googlecalendar_arrival']) $this->deleteGoogleCalendar ($res['googlecalendar_arrival']); if ($res['googlecalendar_departure']) $this->deleteGoogleCalendar ($res['googlecalendar_departure']); $this->DB->query ('UPDATE reservation SET googlecalendar_arrival="", googlecalendar_departure="" WHERE id="'.$res['id'].'";'); continue; } $extrasluzby = array (); if (is_array ($res['extraservice'])) foreach ($res['extraservice'] as $value) $extrasluzby[]=$value['title']; $summary = $res['id'].': '.$res['drivername'].($res['extraservice'][2] ? ' (Valet)' : ($res['extraservice'] ? ' (KLÍČE)' : '')).' | '.$res['status_cs']; $description = 'Telefon: '.$res['driverphone'] ."\nParkování: ".$res['type_short'] ."\nOsob: ".$res['person'] .($res['babycarseat'] ? ' + autosedačka '.$res['babycarseat'] : '') .($res['babycarseat_1'] ? ' + '.$res['babycarseat_1'].'* autosedačka 0-9 kg' : '') .($res['babycarseat_2'] ? ' + '.$res['babycarseat_2'].'* autosedačka 9-18 kg' : '') .($res['babycarseat_3'] ? ' + '.$res['babycarseat_3'].'* autosedačka 18-36 kg' : '') .($res['car'] ? "\nAuto: ".$res['car'] : '') .($res['spz'] ? "\nSPZ: ".$res['spz'] : '') .($res['note'] ? "\nPoznámka: ".$res['note'] : '') ."\n\n" .implode ("\n", $extrasluzby) ."\n\nCena: ".$res['price_total'].' Kč' .($res['gopay_paid'] ? "\nPředplaceno GoPay: ".$res['gopay_paid']." Kč\nZbývá uhradit: ".$res['price_topay'].' Kč' : '') .($res['bitpay_paid'] ? "\nPředplaceno Bitpay: ".$res['bitpay_paid']." Kč\nZbývá uhradit: ".$res['price_topay'].' Kč' : ''); # arrival $gl_arrival = $this->setGoogleCalendar ('P '.$summary, $description, $res['arrival'], 9, $res['googlecalendar_arrival']); # departure $gl_departure = $this->setGoogleCalendar ('O '.$summary, $description, $res['departure'], 11, $res['googlecalendar_departure']); if ($gl_arrival!=$res['googlecalendar_arrival'] || $gl_departure!=$res['googlecalendar_departure']) $this->DB->query ('UPDATE reservation SET googlecalendar_arrival="'.$gl_arrival.'", googlecalendar_departure="'.$gl_departure.'" WHERE id="'.$res['id'].'";'); */ # GreenZone Update if ($new) { require_once (dirname(__FILE__).'/class.GreenZone.php'); if (!is_object ($GreenZone)) $GreenZone = new GreenZone ($this->DB); $new_card=$GreenZone->createCard ($res['id'], $res['drivername'].'\n'.$res['typy_cs'], $res['spz']); unset ($this->cache[$res['id']]); } } # _______________________________________________________________ return ($ids); } # ................................................................... function setPayment ($amount = 0, $type='cash', $reservation_id = null, $user_id = 0) { if (!(int) $reservation_id) return (false); $this->DB->query ('INSERT INTO payment (reservation_id, paid, amount, payment, user_id) VALUES ("'.(int) $reservation_id.'",NOW(),"'.(int) $amount.'","'.$type.'","'.(int) $user_id.'");', 'setPayment'); return (true); } # ................................................................... function updatePayment ($type=null, $reservation_id = null, $paid = null) { if (!(int) $reservation_id || !$paid || !in_array ($type, array ('cash','card','auto_cash','auto_card'))) return (false); $this->DB->query ('UPDATE payment SET payment="'.mysql_real_escape_string ($type).'" WHERE reservation_id="'.(int) $reservation_id.'" AND paid=FROM_UNIXTIME("'.parsedate ($paid).'");', 'updatePayment'); //echo ('UPDATE payment SET payment="'.mysql_real_escape_string ($type).'" WHERE reservation_id="'.(int) $reservation_id.'" AND paid=FROM_UNIXTIME("'.parsedate ($paid).'");'); return (true); } # ................................................................... # ulozim do Google Calendare function setGoogleCalendar ($summary=null, $description=null, $date=null, $color=null, $id=null) { return (true); require_once (dirname(__FILE__).'/../lib/google-api-php-client/src/Google_Client.php'); require_once (dirname(__FILE__).'/../lib/google-api-php-client/src/contrib/Google_CalendarService.php'); if (!is_object ($this->googleCalClient)) $this->googleCalClient = new Google_Client(); $this->googleCalClient->setApplicationName("SmileParking.cz"); $this->googleCalClient->setClientId('335171415995-ukicgrurjtar3jr178io5n4ol111s596.apps.googleusercontent.com'); $this->googleCalClient->setClientSecret('cOzVUVHNBcGqNsuXooG_dNiM'); $this->googleCalClient->setRedirectUri($this->googleCalURI); $this->googleCalClient->setDeveloperKey('AIzaSyD3m2Xszz7LxpH11ZvMJ5rk8DNsqu127OA'); $this->googleCalClient->setAccessType('offline'); $cal = new Google_CalendarService($this->googleCalClient); $refreshToken = '1/8lhsWT18pPMkn83-UdD040-dEieVSg-VSa4NfKxzD9I'; if ($refreshToken) $this->googleCalClient->refreshToken($refreshToken); #uložení if ($this->googleCalClient->getAccessToken()) { $event = new Google_Event($id ? $cal->events->get('primary', $id) : null); if ($summary) $event->setSummary($summary); if ($description) $event->setDescription($description); if ($date) { $start = new Google_EventDateTime(); $start->setDateTime(date ('c', $date)); $event->setStart($start); $end = new Google_EventDateTime(); $end->setDateTime(date ('c', $date+1800)); $event->setEnd($end); } if ($color) $event->setColorId((int) $color); # ulozeni - insert nebo update $savedEvent = $id ? $cal->events->update('primary', $event->getId(), $event) : $cal->events->insert('primary', $event); return ($savedEvent['id']); } } # ................................................................... # smazu Google Calendare function deleteGoogleCalendar ($id) { return (true); require_once (dirname(__FILE__).'/../lib/google-api-php-client/src/Google_Client.php'); require_once (dirname(__FILE__).'/../lib/google-api-php-client/src/contrib/Google_CalendarService.php'); if (!is_object ($this->googleCalClient)) $this->googleCalClient = new Google_Client(); $this->googleCalClient->setApplicationName("SmileParking.cz"); $this->googleCalClient->setClientId('335171415995-ukicgrurjtar3jr178io5n4ol111s596.apps.googleusercontent.com'); $this->googleCalClient->setClientSecret('cOzVUVHNBcGqNsuXooG_dNiM'); $this->googleCalClient->setRedirectUri($this->googleCalURI); $this->googleCalClient->setDeveloperKey('AIzaSyD3m2Xszz7LxpH11ZvMJ5rk8DNsqu127OA'); $this->googleCalClient->setAccessType('offline'); $cal = new Google_CalendarService($this->googleCalClient); $refreshToken = '1/8lhsWT18pPMkn83-UdD040-dEieVSg-VSa4NfKxzD9I'; if ($refreshToken) $this->googleCalClient->refreshToken($refreshToken); #uložení if ($this->googleCalClient->getAccessToken()) { $cal->events->delete('primary', $id); return (true); } } } ?>Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot stavu na CZ $statusy = array ('ok' => 'aktivní', 'deleted' => 'smazán'); # ikonky $ikonky = array ( 1 => 'flaticon-clean myflaticon', 2 => 'icon-plane', 6 => 'flaticon-droplet1 myflaticon', 7 => 'flaticon-silhouette3 myflaticon', 3 => 'flaticon-fuel3 myflaticon' ); if (is_array ($data)) { # ziskam idcka foreach ($data as $key=>$value) $ids[] = $value['id']; # dotaz na proma $x = $this->DB->getAllRows ($this->DB->query ('SELECT * FROM extraservice_promo WHERE extraservice_id IN ("'.implode ('","', $ids).'")', 'ExtraService -> getPromos')); foreach ($x as $value) $promos[$value['extraservice_id']][$value['promo']] = $value['price']; unset ($x); foreach ($data as $key=>$value) { # prepis statusu na CS verzi $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; $data[$key]['icon'] = $ikonky[$value['id']] ? $ikonky[$value['id']] : null; if ($promos[$value['id']]) $data[$key]['promo'] = $promos[$value['id']]; $this->cache[$value['id']] = $data[$key]; } } return ($data); } # ................................................................... function getWithPromo ($where = null, $order = null, $limit = null, $limit_from = null, $promo=null) { $data = $this->get ($where, $order, $limit, $limit_from); if (is_array ($data)) foreach ($data as $key=>$value) if (is_array ($value['promo'])) foreach ($value['promo'] as $pr_title=>$pr_price) if (preg_match ('/(^|[, ]+)' . trim ($promo) . '($|[, ]+)/i', $pr_title)) { $data[$key]['price_regular'] = $data[$key]['price']; $data[$key]['price'] = $pr_price; } return ($data); } # ................................................................... function getPrice ($type="outside", $promo=null) { //$type=in_array ($type, array ('inside','exclusive')) ? 'exclusive' : 'regular'; switch ($type) { case 'inside': case 'exclusive': $type='exclusive'; break; case 'cheap': $type='cheap'; break; case 'outside': case 'regular': default : $type='regular'; } $data = $this->getWithPromo ('status="ok" AND public=1 AND canorder_'.$type, null, null, null, $promo); //$data = parent::get ('status="ok" AND canorder_'.$type.($promo ? ' AND (ep.promo IS NULL OR (LOWER(ep.promo) REGEXP "(^|[, ]+)' . mysql_real_escape_string (trim (strtolower ($promo))) . '($|[, ]+)"))' : ' AND ep.promo IS NULL')); if (is_array ($data)) foreach ($data as $key=>$value) { $output[$value['id']] = $value; if ($type=="exclusive" && $value['included_exclusive']) { $output[$value['id']]['price'] = 0; $output[$value['id']]['price_regular'] = $value['price']; } if ($type=="regular" && $value['included_regular']) { $output[$value['id']]['price'] = 0; $output[$value['id']]['price_regular'] = $value['price']; } } return ($output); } # ................................................................... function checkPromo ($promo=null) { //return ($this->findId ('p.status="ok" AND p.promo IS NOT NULL AND p.promo="' . mysql_real_escape_string ($promo) . '"')); return ($this->findId ('es.status="ok" AND ep.promo IS NOT NULL AND LOWER(ep.promo) REGEXP "(^|[, ]+)' . mysql_real_escape_string (trim ($promo)) . '($|[, ]+)"')); } # ................................................................... function set ($set, $ids = null) { if (!is_array ($set)) return (false); # presunu extraservices $promos = $set['promo']; unset ($set['promo']); # ulozeni beznych dat if (@count ($set) >= 1) $next_id = parent::set ($set, $ids); if (!$ids) $ids = $next_id; # _______________________________________________________________ # ExtraServices if (is_array ($promos) && is_numeric ($ids)) { # promazu existujici u objednavky $this->DB->query ('DELETE FROM extraservice_promo WHERE extraservice_id="'.(int) $ids.'";'); # ulozim si existujici if (count ($promos) > 0) { foreach ($promos as $key=>$value) $sql[] = '(' . (int) $ids . ', "' . $key . '",' . $value . ')'; $this->DB->query ('INSERT INTO extraservice_promo (extraservice_id, promo, price) VALUES ' . implode (', ', $sql) . ';'); } } # _______________________________________________________________ return ($ids); } } ?>Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot stavu na CZ $statusy = array ('ok' => 'aktivní', 'draft' => 'koncept', 'deleted' => 'smazán'); $web = array ('smile' => 'SmileParking.cz', 'dovolena' => 'DovolenáParking.cz', 'valet' => 'ValetParking.cz'); if (is_array ($data)) foreach ($data as $key=>$value) { # prepis statusu na CS verzi $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; $data[$key]['web_cs'] = $web[$value['web']] ? $web[$value['web']] : $data[$key]['web']; $data[$key]['seolink'] = substr (text2seolink ($value['title']),0,64); $data[$key]['seolink_en'] = substr (text2seolink ($value['title_en']),0,64); $data[$key]['seolink_de'] = substr (text2seolink ($value['title_de']),0,64); $data[$key]['seolink_ru'] = substr (text2seolink ($value['title_ru']),0,64); $this->cache[$value['id']] = $data[$key]; } return ($data); } } ?>Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot statusu na CZ # prevedi stringu na pole u pageschema $statusy = array ('ok' => 'člen', 'disabled' => 'zablokován', 'deleted' => 'smazán'); $level = array (0=>'silver', 6=>'gold', 12=>'platinum', 30=>'diamont'); $level_color = array ('silver'=>'silver', 'gold'=>'gold', 'platinum'=>'black'); $level_next = array ('silver'=>'Gold', 'gold'=>'Platinum', 'platinum'=>'Diamont'); if (is_array ($data)) foreach ($data as $key=>$value) { $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; $data[$key]['level_color'] = $level_color[$value['level']]; $data[$key]['level_next'] = $level_next[$value['level']]; $l_np = array_flip($level); $data[$key]['level_nextparking'] = $l_np[strtolower ($level_next[$value['level']])] - $value['reservation_valid_count']; /* foreach (array_keys ($level) as $l) { if ($l>$value['reservation_valid_count']) break; $data[$key]['level'] = $level[$l]; } */ $this->cache[$value['id']] = $value; } return ($data); } # ................................................................... # vrati definovane agregovane vysledky, tedy soucet, prumer, pocet atd vsech vysledku bez ohledu na limit # bere se podle posledniho getu!!! function getGroupTotal () { # sub dotaz dle posledniho getu!!! $subsql = str_replace ('SQL_CALC_FOUND_ROWS', '', $this->cachesql); $subsql = eregi_replace ('LIMIT [0-9]+(, *[0-9]+)?', '', $subsql); $subsql = str_replace (';', '', $subsql); $sql = 'SELECT COUNT(gt.id) AS id, SUM(gt.reservation_valid_count) AS reservation_valid_count, SUM(gt.points) AS points, SUM(gt.points_own) AS points_own, SUM(gt.points_parking) AS points_parking, SUM(gt.points_extras) AS points_extras, SUM(gt.spent_points_total) AS spent_points_total, SUM(gt.spent_points_parking) AS spent_points_parking, SUM(gt.spent_points_extras) AS spent_points_extras, SUM(gt.points_remaining) AS points_remaining, SUM(gt.remaining_points_parking) AS remaining_points_parking, SUM(gt.remaining_points_extras) AS remaining_points_extras, SUM(gt.points_aff) as points_aff FROM ('.$subsql.') gt;'; # SQL dotaz $this->DB->query ($sql, get_class ($this) . ' -> getGroupTotal'); return ($this->DB->getRow ()); } # ................................................................... function getAffPartners ($user_id=null) { if (!$user_id && $this->user['id']) $user_id = $this->user['id']; elseif (!$user_id) return (null); /* $this->DB->getAllRows($this->DB->query ('SELECT SQL_CALC_FOUND_ROWS cu.id, cu.email, cu.status, IFNULL(cp.points_parking,0) AS points_parking, IFNULL(cp.points_extras,0) AS points_extras, IFNULL(cp.own_points,0) AS points_own, IFNULL(cp.points,0)-IFNULL(SUM(cr.points_parking+cr.points_extras),0) AS points_remaining, cpr.drivername, cpr.driveremail, cpr.driverphone, cpr.car, cpr.invoice_company, cpr.invoice_street, cpr.invoice_city, cpr.invoice_psc, cpr.invoice_ic, cpr.invoice_dic, cur.reservation_valid_count, SUM(cr.points_parking) AS spent_points_parking, SUM(cr.points_parking+cr.points_extras) AS spent_points_total, SUM(cr.points_extras) AS spent_points_extras, UNIX_TIMESTAMP(cur.reservation_last_leave) AS reservation_last_leave, cu.prefered_id, IFNULL(cp.points_parking,0)-IFNULL(SUM(cr.points_parking),0) AS remaining_points_parking, IFNULL(cp.points_extras,0)-IFNULL(SUM(cr.points_extras),0) AS remaining_points_extras, cpr.name AS profile_name, cpr.id AS profile_id, UNIX_TIMESTAMP(cur.reservation_last_created) AS reservation_last_created, IFNULL(cp.aff_points,0) AS points_aff FROM club_user cu LEFT JOIN (SELECT cu2.id, cu2.email, cu2.status, COUNT(r.id) AS reservation_valid_count, MAX(r.departure) AS reservation_last_leave, MAX(r.created) AS reservation_last_created, FLOOR(r.price/10) AS points_parking_future, FLOOR(IFNULL(SUM(re.price),0)/10) AS points_extras_future, FLOOR((IFNULL(SUM(re.price),0)+r.price)/10) AS points_future FROM club_user cu2 JOIN reservation r ON r.club_user_id=cu2.id AND r.status NOT IN ("noshow","canceled","deleted") LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id LEFT JOIN club_point cp2 ON r.id=cp2.reservation_id WHERE cu2.parent_user_id="'.(int) $user_id.'" AND cp2.reservation_id IS NULL GROUP BY cu2.id) cur ON cur.id=cu.id LEFT JOIN club_point cu ON cu.id=cp.source_club_user_id WHERE cu.parent_user_id="'.(int) $user_id.'" GROUP BY cu.id'), get_class ($this) . ' -> getAffPartners'); */ return ($this->DB->getAllRows($this->DB->query ('SELECT SQL_CALC_FOUND_ROWS cu.id, cu.email, cu.status, IFNULL(SUM(cp.points_parking),0) AS points_parking, IFNULL(SUM(cp.points_extras),0) AS points_extras, IFNULL(SUM(cp.points_parking+cp.points_extras),0) AS points, UNIX_TIMESTAMP(cur.reservation_last_created) AS reservation_last_created, UNIX_TIMESTAMP(cur.reservation_last_leave) AS reservation_last_leave, cur.points_all, cur.points_parking_all, cur.points_extras_all, cur.reservation_valid_count, cur.points_all-IFNULL(SUM(cp.points_parking+cp.points_extras),0) AS points_future, cur.points_parking_all-IFNULL(SUM(cp.points_parking),0) AS points_parking_future, cur.points_extras_all-IFNULL(SUM(cp.points_extras),0) AS points_extras_future FROM club_user cu LEFT JOIN (SELECT cu2.id, cu2.email, cu2.status, COUNT(r.id) AS reservation_valid_count, MAX(r.departure) AS reservation_last_leave, MAX(r.created) AS reservation_last_created, FLOOR(r.price/10) AS points_parking_all, FLOOR(IFNULL(SUM(re.price),0)/10) AS points_extras_all, FLOOR((IFNULL(SUM(re.price),0)+r.price)/10) AS points_all FROM club_user cu2 JOIN reservation r ON r.club_user_id=cu2.id AND r.status NOT IN ("noshow","canceled","deleted") LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id WHERE cu2.parent_user_id="'.(int) $user_id.'" GROUP BY cu2.id) cur ON cur.id=cu.id LEFT JOIN club_point cp ON cu.id=cp.source_club_user_id WHERE cu.parent_user_id="'.(int) $user_id.'" GROUP BY cu.id', get_class ($this) . ' -> getAffPartners'))); } # ................................................................... /* function getProfiles ($user_id=null) { if (!$user_id && $this->user['id']) $user_id = $this->user['id']; elseif (!$user_id) return (null); $user = $this->getId ($user_id, false); require_once (dirname(__FILE__)."/class.Reservation.php"); if (!is_object ($Reservation)) $Reservation = new Reservation ($this->DB); $data = $Reservation->get ('r.club_user_id="'.$user_id.'"', 11, -1); if (!is_array ($data)) return (null); $profile_vars = array ('drivername','driveremail','driverphone','car','invoice_company', 'invoice_street', 'invoice_city', 'invoice_psc', 'invoice_ic', 'invoice_dic'); foreach ($data as $key=>$value) { foreach ($profile_vars as $profile_var) { $data_duplicates[$value['id']][$profile_var] = $value[$profile_var]; } } $data_unique = $this->remove_dup ($data_duplicates); # prefered if ($user['prefered_id']) { foreach ($profile_vars as $profile_var) $prefered[$profile_var] = $user[$profile_var]; foreach ($data_unique as $key=>$value) if ($prefered==$value) { $data_unique[$key]['prefered']=$user['prefered_id']; break; } } return ($data_unique); } */ function getProfiles ($user_id=null) { if (!$user_id && $this->user['id']) $user_id = $this->user['id']; elseif (!$user_id) return (null); require_once (dirname(__FILE__)."/class.ClubProfile.php"); if (!is_object ($ClubProfile)) $ClubProfile = new ClubProfile ($this->DB); return ($ClubProfile->get ('cu.id="'.$user_id.'"')); } # ................................................................... function onetimeLoadProfiles ($user_id=null) { if (!$user_id && $this->user['id']) $user_id = $this->user['id']; elseif (!$user_id) return (null); $user = $this->getId ($user_id, false); require_once (dirname(__FILE__)."/class.Reservation.php"); if (!is_object ($Reservation)) $Reservation = new Reservation ($this->DB); $data = $Reservation->get ('r.club_user_id="'.$user_id.'"', 11, -1); if (!is_array ($data)) return (null); $profile_vars = array ('drivername','driveremail','driverphone','car','invoice_company', 'invoice_street', 'invoice_city', 'invoice_psc', 'invoice_ic', 'invoice_dic','spz'); foreach ($data as $key=>$value) { foreach ($profile_vars as $profile_var) { $data_duplicates[$value['id']][$profile_var] = $value[$profile_var]; } } $data_unique = $this->remove_dup ($data_duplicates); # save to db if (is_array ($data_unique)) { require_once (dirname(__FILE__)."/class.ClubProfile.php"); if (!is_object ($ClubProfile)) $ClubProfile = new ClubProfile ($this->DB); foreach ($data_unique as $row) { unset ($set); $set['club_user_id'] = '"'.(int) $user_id.'"'; foreach ($row as $key=>$value) if ($value) $set[$key] = '"'.mysql_real_escape_string ($value).'"'; $ClubProfile->set($set); } } return ($data_unique); } function remove_dup($matriz) { $aux_ini=array(); $entrega=array(); foreach($matriz as $key=>$value) $aux_ini[$key]=serialize($value); $mat=array_unique($aux_ini); foreach($matriz as $key=>$value) //foreach($n=0;$nuser['email']; return (substr (md5('tajna konstanta 1654664687wer1'.$user_email),0,16)); } # ................................................................... function verify ($email = null, $password = null) { $user = $this->get (array ('cu.email = "' . mysql_real_escape_string ($email) . '"', 'cu.password = MD5("' . mysql_real_escape_string ($password) . '")', 'cu.status NOT IN("deleted","disabled")'), null, 1); if (is_array ($user)) $this->user = reset ($user); else return (null); # overeni, pak predelat if ($this->user['status'] != 'deleted') return ($this->user['id']); else { unset ($this->user); return (false); } } # ................................................................... function verifyPermanent ($hash) { $user = $this->get (array ('MD5(CONCAT(cu.id, cu.email)) = "' . mysql_real_escape_string ($hash) . '"', 'cu.status NOT IN("deleted","disabled")'), null, 1); if (is_array ($user)) $this->user = reset ($user); else return (null); # overeni, pak predelat if ($this->user['status'] != 'deleted') return ($this->user['id']); else { unset ($this->user); return (false); } } # ................................................................... function load ($user_id = null) { unset ($this->cache); $user = $this->get (array ('cu.id = "' . ($user_id ? $user_id : $this->user['id']) . '"'), null, 1); if (is_array ($user)) $this->user = reset ($user); else unset ($this->user); return ($this->user); } # ................................................................... # save to club_reservation points used for reservation function setReservation ($reservation_id, $used_parking=0, $used_extras=0, $user_id=null) { $user_id = (int) $user_id ? (int) $user_id : $this->user['id']; if (!(int) $user_id || !(int) $reservation_id) return (false); if ((int) $used_parking==0 && (int) $used_extras==0) $this->DB->query ('DELETE FROM club_reservation WHERE reservation_id="'.(int) $reservation_id.'";', get_class ($this) . ' -> setReservation'); else $this->DB->query ('INSERT INTO club_reservation (reservation_id,club_user_id,points_parking,points_extras) VALUES ("'.(int) $reservation_id.'","'.(int) $user_id.'","'.(int) $used_parking.'","'.(int) $used_extras.'") ON DUPLICATE KEY UPDATE reservation_id="'.(int) $reservation_id.'", club_user_id="'.(int) $user_id.'", points_parking="'.(int) $used_parking.'", points_extras="'.(int) $used_extras.'";', get_class ($this) . ' -> setReservation'); return (true); } # ................................................................... # save to club_point points used for reservation function addPointsLockedReservation ($reservation_ids=null, $own_parking_percentage=10, $own_extras_percentage=10, $aff_parking_percentage=10, $aff_extras_percentage=10) { if (!is_array ($reservation_ids) || (int) $reservation_ids<1) return (false); elseif (is_array ($reservation_ids)) $reservation_ids = implode ('","', $reservation_ids); else $reservation_ids = (int) $reservation_ids; //$this->DB->query ('INSERT INTO club_point (reservation_id,club_user_id,source_club_user_id,points_parking,points_extras) SELECT r.id AS reservation_id, cu.id AS club_user_id, null AS source_club_user_id, FLOOR(r.price*'.(int) $own_parking_percentage.'/100) AS points_price, FLOOR(SUM(IFNULL(re.price,0))*'.(int) $own_extras_percentage.'/100) AS points_extras FROM reservation r JOIN club_user cu ON r.club_user_id=cu.id LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id WHERE r.id IN ("'.$reservation_ids.'") GROUP BY r.id UNION ALL SELECT r.id AS reservation_id, cu2.id AS club_user_id, cu.id AS source_club_user_id, FLOOR(r.price*'.(int) $aff_parking_percentage.'/100) AS points_price, FLOOR(SUM(IFNULL(re.price,0))*'.(int) $aff_extras_percentage.'/100) AS points_extras FROM reservation r JOIN club_user cu ON r.club_user_id=cu.id JOIN club_user cu2 ON cu.parent_user_id=cu2.id LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id WHERE r.id IN ("'.$reservation_ids.'") AND r.status="closed" GROUP BY r.id;', get_class ($this) . ' -> addPointsLockedReservation'); $this->DB->query ('INSERT INTO club_point (reservation_id,club_user_id,source_club_user_id,points_parking,points_extras) SELECT r.id AS reservation_id, cu.id AS club_user_id, null AS source_club_user_id, FLOOR((r.price*'.(int) $own_parking_percentage.'+SUM(IFNULL(re.price,0))*'.(int) $own_extras_percentage.')/200) AS points_price, FLOOR((r.price*'.(int) $own_parking_percentage.'+SUM(IFNULL(re.price,0))*'.(int) $own_extras_percentage.')/200) AS points_extras FROM reservation r JOIN club_user cu ON r.club_user_id=cu.id LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id WHERE r.id IN ("'.$reservation_ids.'") AND r.status="closed" AND (r.pricelist_id<>43 OR cu.id=10615) GROUP BY r.id UNION ALL SELECT r.id AS reservation_id, cu2.id AS club_user_id, cu.id AS source_club_user_id, FLOOR((r.price*'.(int) $aff_parking_percentage.'+SUM(IFNULL(re.price,0))*'.(int) $aff_extras_percentage.')/200) AS points_price, FLOOR((r.price*'.(int) $aff_parking_percentage.'+SUM(IFNULL(re.price,0))*'.(int) $aff_extras_percentage.')/200) AS points_extras FROM reservation r JOIN club_user cu ON r.club_user_id=cu.id JOIN club_user cu2 ON cu.parent_user_id=cu2.id LEFT JOIN reservation_extraservice re ON re.reservation_id=r.id WHERE r.id IN ("'.$reservation_ids.'") AND r.status="closed" AND (r.pricelist_id<>43 OR cu.id=10615) GROUP BY r.id;', get_class ($this) . ' -> addPointsLockedReservation'); return (true); } # ................................................................... # save to club_point extra bonus points function addExtraPoints ($points_parking=0, $points_extras=0, $note="", $user_id=null) { if (!$user_id && $this->user['id']) $user_id = $this->user['id']; elseif (!$user_id) return (null); $this->DB->query ('INSERT INTO club_point (club_user_id,points_parking,points_extras,note) VALUES ("'.(int) $user_id.'","'.(int) $points_parking.'","'.(int) $points_extras.'","'.mysql_real_escape_string ($note).'");', get_class ($this) . ' -> addExtraPoints'); return (true); } # ................................................................... # save to club_point points used for reservation function prepareEmailNewPoints ($reservation_ids=null) { if (!is_array ($reservation_ids) || (int) $reservation_ids<1) return (false); elseif (is_array ($reservation_ids)) $reservation_ids = implode ('","', $reservation_ids); else $reservation_ids = (int) $reservation_ids; # load add points /*$points = $this->DB->getAllRows($this->DB->query ('SELECT cp.*, cu.email AS source_club_user_email FROM club_point cp LEFT JOIN club_user cu ON cp.source_club_user_id=cu.id WHERE cp.reservation_id IN ("'.$reservation_ids.'");', get_class ($this) . ' -> emailNewPoints'), 'id'); if (!is_array ($points)) return (false); # find club_users involved foreach ($points as $value) { $club_user_ids[$value['club_user_id']][] = $points[$value['id']]; } */ $points_result = $this->DB->query ('SELECT cp.*, cu.email AS source_club_user_email FROM club_point cp LEFT JOIN club_user cu ON cp.source_club_user_id=cu.id WHERE cp.reservation_id IN ("'.$reservation_ids.'");', get_class ($this) . ' -> emailNewPoints'); while ($row = $this->DB->getRow ()) { $club_user_ids[$row['club_user_id']][] = $row; } # get club_users $club_users = $this->get ('cu.id IN ("'.implode ('","',array_keys ($club_user_ids)).'")'); if (!is_array ($club_users)) return (false); # completing data for each email foreach ($club_users as $club_user) { foreach ($club_user_ids[$club_user['id']] as $value) { $output[] = array ('user'=>$club_user, 'points'=>$value); } } return ($output); } # ................................................................... function set ($set, $ids = null) { # vycistim cache unset ($this->cache); # presunu do pomocne promene a vycistim //$hotels = $set['hotels']; //unset ($set['hotels']); # ulozeni normalnich udaju $temp = parent::set ($set, $ids); $ids = $temp ? $temp : $ids; # _______________________________________________________________ # Hotel uzivatele - pokud jsem nastavil, musi ukladat do M:N tabulky /* if (is_array ($hotels) && is_numeric ($ids)) { unset ($sql); unset ($temp_ids); foreach ($hotels as $key=>$value) { $sql[] = '(' . (int) $ids . ', "' . (int) $key . '")'; $temp_ids[] = $key; } # promazu pripadne prebyvajici $this->DB->query ('DELETE FROM user2hotel WHERE user_id = ' . (int) $ids . ' AND hotel_id NOT IN ("' . implode ('", "', $temp_ids) . '");'); # ulozim si existujici $this->DB->query ('INSERT IGNORE INTO user2hotel (user_id, hotel_id) VALUES ' . implode (', ', $sql) . ';'); } */ $this->load (); return ($ids); } # ................................................................... function getUser ($what = null) { //if (count ($this->user) == 1) // return (null); if ($what) return ($this->user[$what]); else return ($this->user); } # ................................................................... function logout () { unset ($this->user); return (true); } } ?>Modul ($database)); } # ................................................................... /* function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot stavu na CZ $statusy = array ('ok' => 'aktivní', 'draft' => 'koncept', 'deleted' => 'smazán'); if (is_array ($data)) foreach ($data as $key=>$value) { # prepis statusu na CS verzi $data[$key]['status_cs'] = $statusy[$value['status']] ? $statusy[$value['status']] : $data[$key]['status']; $this->cache[$value['id']] = $data[$key]; } return ($data); } */ } ?>Modul ($database)); } # ................................................................... function get ($where = null, $order = null, $limit = null, $limit_from = null) { $data = parent::get ($where, $order, $limit, $limit_from); # prepis hodnot statusu na CZ # prevedi stringu na pole u pageschema $statusy = array ('new' => 'automatický', 'ok' => 'potvrzený', 'deleted' => 'smazán'); if (is_array ($data)) foreach ($data as $key=>$value) { if ($statusy[$value['status']]) $data[$key]['status_cs'] = $statusy[$value['status']]; else $data[$key]['status_cs'] = $data[$key]['status']; } return ($data); } # ................................................................... function trySetProfile ($data=null, $res_id=null) { if (!is_array ($data) || !$data['club_user_id']) return (null); if (!(int) $res_id) $res_id = rand (1, 999); foreach (array ('drivername','driveremail','driverphone','car', 'invoice_company', 'invoice_street', 'invoice_city', 'invoice_psc', 'invoice_ic', 'invoice_dic','club_user_id','spz') as $value) $set[$value]=$data[$value]; $set['name'] = '"Profil '.(int) $res_id.'"'; $this->set($set); return (true); } } ?>