ILIAS  Release_3_10_x_branch Revision 61812
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilPaymentBookings.php
Go to the documentation of this file.
1 <?php
2 /*
3  +-----------------------------------------------------------------------------+
4  | ILIAS open source |
5  +-----------------------------------------------------------------------------+
6  | Copyright (c) 1998-2001 ILIAS open source, University of Cologne |
7  | |
8  | This program is free software; you can redistribute it and/or |
9  | modify it under the terms of the GNU General Public License |
10  | as published by the Free Software Foundation; either version 2 |
11  | of the License, or (at your option) any later version. |
12  | |
13  | This program is distributed in the hope that it will be useful, |
14  | but WITHOUT ANY WARRANTY; without even the implied warranty of |
15  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16  | GNU General Public License for more details. |
17  | |
18  | You should have received a copy of the GNU General Public License |
19  | along with this program; if not, write to the Free Software |
20  | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
21  +-----------------------------------------------------------------------------+
22 */
31 include_once './payment/classes/class.ilPaymentVendors.php';
32 include_once './payment/classes/class.ilPaymentTrustees.php';
33 
34 
35 class ilPaymentBookings
36 {
37  /*
38  * id of vendor, admin or trustee
39  */
40  var $user_id = null;
41  var $db = null;
42 
43  var $bookings = array();
44 
45  var $booking_id = null;
46  var $payed = null;
47  var $access = null;
48  var $admin_view = false;
49 
50  /*
51  * admin_view = true reads all statistic data (only_used in administration)
52  */
53  function ilPaymentBookings($a_user_id = '',$a_admin_view = false)
54  {
55  global $ilDB;
56 
57  $this->admin_view = $a_admin_view;
58  $this->user_id = $a_user_id;
59  $this->db =& $ilDB;
60 
61  if($a_user_id)
62  {
63  $this->__read();
64  }
65  }
66 
67  // SET GET
68  function setBookingId($a_booking_id)
69  {
70  return $this->booking_id = $a_booking_id;
71  }
72  function getBookingId()
73  {
74  return $this->booking_id;
75  }
76  function setTransaction($a_transaction)
77  {
78  $this->transaction = $a_transaction;
79  }
80  function getTransaction()
81  {
82  return $this->transaction;
83  }
84  function setPobjectId($a_pobject_id)
85  {
86  $this->pobject_id = $a_pobject_id;
87  }
88  function getPobjectId()
89  {
90  return $this->pobject_id;
91  }
92  function setCustomerId($a_customer_id)
93  {
94  $this->customer_id = $a_customer_id;
95  }
96  function getCustomerId()
97  {
98  return $this->customer_id;
99  }
100  function setVendorId($a_vendor_id)
101  {
102  $this->vendor_id = $a_vendor_id;
103  }
104  function getVendorId()
105  {
106  return $this->vendor_id;
107  }
108  function setPayMethod($a_pay_method)
109  {
110  $this->pay_method = $a_pay_method;
111  }
112  function getPayMethod()
113  {
114  return $this->pay_method;
115  }
116  function setOrderDate($a_order_date)
117  {
118  $this->order_date = $a_order_date;
119  }
120  function getOrderDate()
121  {
122  return $this->order_date;
123  }
124  function setDuration($a_duration)
125  {
126  $this->duration = $a_duration;
127  }
128  function getDuration()
129  {
130  return $this->duration;
131  }
132  function setPrice($a_price)
133  {
134  $this->price = $a_price;
135  }
136  function getPrice()
137  {
138  return $this->price;
139  }
140  function setDiscount($a_discount)
141  {
142  $this->discount = $a_discount;
143  }
144  function getDiscount()
145  {
146  return $this->discount;
147  }
148  function setPayed($a_payed)
149  {
150  $this->payed = $a_payed;
151  }
152  function getPayedStatus()
153  {
154  return $this->payed;
155  }
156  function setAccess($a_access)
157  {
158  $this->access = $a_access;
159  }
160  function getAccessStatus()
161  {
162  return $this->access;
163  }
164  function setVoucher($a_voucher)
165  {
166  $this->voucher = $a_voucher;
167  }
168  function getVoucher()
169  {
170  return $this->voucher;
171  }
172  function setTransactionExtern($a_transaction_extern)
173  {
174  $this->transaction_extern = $a_transaction_extern;
175  }
176  function getTransactionExtern()
177  {
178  return $this->transaction_extern;
179  }
180 
181  function add()
182  {
183  $query = sprintf("INSERT INTO payment_statistic VALUES('',".
184  "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
185  $this->getTransaction(),
186  $this->getPobjectId(),
187  $this->getCustomerId(),
188  $this->getVendorId(),
189  $this->getPayMethod(),
190  $this->getOrderDate(),
191  $this->getDuration(),
192  $this->getPrice(),
193  $this->getDiscount(),
194  $this->getPayedStatus(),
195  $this->getAccessStatus(),
196  $this->getVoucher(),
197  $this->getTransactionExtern());
198 
199  $this->db->query($query);
200 
201  return $this->db->getLastInsertId();
202  }
203 
204  function update()
205  {
206  if($this->getBookingId())
207  {
208  $query = "UPDATE payment_statistic ".
209  "SET payed = '".(int) $this->getPayedStatus()."', ".
210  "access = '".(int) $this->getAccessStatus()."' ".
211  "WHERE booking_id = '".$this->getBookingId()."'";
212  $this->db->query($query);
213 
214  return true;
215  }
216  return false;
217  }
218 
219  function delete()
220  {
221  if($this->getBookingId())
222  {
223  $query = "DELETE FROM payment_statistic ".
224  "WHERE booking_id = '".$this->getBookingId()."'";
225 
226  $this->db->query($query);
227 
228  return true;
229  }
230  return false;
231  }
232 
233  function getBookingsOfCustomer($a_usr_id)
234  {
235  $query = 'SELECT * FROM payment_statistic as ps, payment_objects as po '.
236  "WHERE ps.pobject_id = po.pobject_id ".
237  "AND customer_id = '".$a_usr_id."' ".
238  "ORDER BY order_date DESC";
239 
240  $res = $this->db->query($query);
241  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
242  {
243  $booking[$row->booking_id]['booking_id'] = $row->booking_id;
244  $booking[$row->booking_id]['transaction'] = $row->transaction;
245  $booking[$row->booking_id]['pobject_id'] = $row->pobject_id;
246  $booking[$row->booking_id]['customer_id'] = $row->customer_id;
247  $booking[$row->booking_id]['order_date'] = $row->order_date;
248  $booking[$row->booking_id]['duration'] = $row->duration;
249  $booking[$row->booking_id]['price'] = $row->price;
250  $booking[$row->booking_id]['discount'] = $row->discount;
251  $booking[$row->booking_id]['payed'] = $row->payed;
252  $booking[$row->booking_id]['access'] = $row->access;
253  $booking[$row->booking_id]['ref_id'] = $row->ref_id;
254  $booking[$row->booking_id]['status'] = $row->status;
255  $booking[$row->booking_id]['pay_method'] = $row->pay_method;
256  $booking[$row->booking_id]['vendor_id'] = $row->vendor_id;
257  $booking[$row->booking_id]['b_vendor_id'] = $row->b_vendor_id;
258  $booking[$row->booking_id]['b_pay_method'] = $row->b_pay_method;
259  $booking[$row->booking_id]['voucher'] = $row->voucher;
260  $booking[$row->booking_id]['transaction_extern'] = $row->transaction_extern;
261  }
262  return $booking ? $booking : array();
263  }
264 
265  function getBookings()
266  {
267  return $this->bookings ? $this->bookings : array();
268  }
269 
270  function getBooking($a_booking_id)
271  {
272  $query = 'SELECT * FROM payment_statistic as ps, payment_objects as po '.
273  "WHERE ps.pobject_id = po.pobject_id ".
274  "AND booking_id = '".$a_booking_id."'";
275 
276  $res = $this->db->query($query);
277  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
278  {
279  $booking['booking_id'] = $row->booking_id;
280  $booking['transaction'] = $row->transaction;
281  $booking['pobject_id'] = $row->pobject_id;
282  $booking['customer_id'] = $row->customer_id;
283  $booking['order_date'] = $row->order_date;
284  $booking['duration'] = $row->duration;
285  $booking['price'] = $row->price;
286  $booking['discount'] = $row->discount;
287  $booking['payed'] = $row->payed;
288  $booking['access'] = $row->access;
289  $booking['ref_id'] = $row->ref_id;
290  $booking['status'] = $row->status;
291  $booking['pay_method'] = $row->pay_method;
292  $booking['vendor_id'] = $row->vendor_id;
293  $booking['b_vendor_id'] = $row->b_vendor_id;
294  $booking['b_pay_method'] = $row->b_pay_method;
295  $booking['voucher'] = $row->voucher;
296  $booking['transaction_extern'] = $row->transaction_extern;
297  }
298  return $booking ? $booking : array();
299  }
300 
301 
302  // STATIC
303  function _getCountBookingsByVendor($a_vendor_id)
304  {
305  global $ilDB;
306 
307  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
308  "WHERE b_vendor_id = '".$a_vendor_id."'";
309 
310  $res = $ilDB->query($query);
311  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
312  {
313  return $row->bid;
314  }
315  return 0;
316  }
317 
318  function _getCountBookingsByCustomer($a_vendor_id)
319  {
320  global $ilDB;
321 
322  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
323  "WHERE customer_id = '".$a_vendor_id."'";
324 
325  $res = $ilDB->query($query);
326  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
327  {
328  return $row->bid;
329  }
330  return 0;
331  }
332  function _getCountBookingsByObject($a_pobject_id)
333  {
334  global $ilDB;
335 
336  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
337  "WHERE pobject_id = '".$a_pobject_id."'";
338 
339  $res = $ilDB->query($query);
340  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
341  {
342  return $row->bid;
343  }
344  return 0;
345  }
346 
347  function _hasAccess($a_pobject_id,$a_user_id = 0)
348  {
349  global $ilDB,$ilias;
350 
351  $usr_id = $a_user_id ? $a_user_id : $ilias->account->getId();
352 
353  $query = "SELECT * FROM payment_statistic ".
354  "WHERE pobject_id = '".$a_pobject_id."' ".
355  "AND customer_id = '".$usr_id."' ".
356  "AND payed = '1' ".
357  "AND access = '1'";
358 
359  $res = $ilDB->query($query);
360  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
361  {
362  $orderDateYear = date("Y", $row->order_date);
363  $orderDateMonth = date("m", $row->order_date);
364  $orderDateDay = date("d", $row->order_date);
365  $orderDateHour = date("H", $row->order_date);
366  $orderDateMinute = date("i", $row->order_date);
367  $orderDateSecond = date("s", $row->order_date);
368  if (($orderDateMonth + $row->duration) > 12)
369  {
370  $years = floor(($orderDateMonth + $row->duration) / 12);
371  $months = ($orderDateMonth + $row->duration) - (12 * $years);
372  $orderDateYear += $years;
373  $orderDateMonth = $months;
374  }
375  else
376  {
377  $orderDateMonth += $row->duration;
378  }
379  $startDate = date("Y-m-d H:i:s", $row->order_date);
380  $endDate = date("Y-m-d H:i:s", mktime($orderDateHour, $orderDateMinute, $orderDateSecond, $orderDateMonth, $orderDateDay, $orderDateYear));
381  if (date("Y-m-d H:i:s") >= $startDate &&
382  date("Y-m-d H:i:s") <= $endDate)
383  {
384  return true;
385  }
386  }
387  return false;
388  }
389 
390  function _getActivation($a_pobject_id,$a_user_id = 0)
391  {
392  global $ilDB,$ilias;
393 
394  $usr_id = $a_user_id ? $a_user_id : $ilias->account->getId();
395 
396  $query = "SELECT * FROM payment_statistic ".
397  "WHERE pobject_id = '".$a_pobject_id."' ".
398  "AND customer_id = '".$usr_id."' ".
399  "AND payed = '1' ".
400  "AND access = '1'";
401 
402  $res = $ilDB->query($query);
403  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
404  {
405  $orderDateYear = date("Y", $row->order_date);
406  $orderDateMonth = date("m", $row->order_date);
407  $orderDateDay = date("d", $row->order_date);
408  $orderDateHour = date("H", $row->order_date);
409  $orderDateMinute = date("i", $row->order_date);
410  $orderDateSecond = date("s", $row->order_date);
411  if (($orderDateMonth + $row->duration) > 12)
412  {
413  $years = floor(($orderDateMonth + $row->duration) / 12);
414  $months = ($orderDateMonth + $row->duration) - (12 * $years);
415  $orderDateYear += $years;
416  $orderDateMonth = $months;
417  }
418  else
419  {
420  $orderDateMonth += $row->duration;
421  }
422  $startDate = date("Y-m-d H:i:s", $row->order_date);
423  $endDate = date("Y-m-d H:i:s", mktime($orderDateHour, $orderDateMinute, $orderDateSecond, $orderDateMonth, $orderDateDay, $orderDateYear));
424  if (date("Y-m-d H:i:s") >= $startDate &&
425  date("Y-m-d H:i:s") <= $endDate)
426  {
427  $activation = array(
428  "activation_start" => $row->order_date,
429  "activation_end" => mktime($orderDateHour, $orderDateMinute, $orderDateSecond, $orderDateMonth, $orderDateDay, $orderDateYear)
430  );
431  return $activation;
432  }
433  }
434  return false;
435  }
436 
437  function _getCountBookingsByPayMethod($a_pm)
438  {
439  switch($a_pm)
440  {
441  case 'pm_bill':
442  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
443  "WHERE pay_method = '1'";
444 
445  $res = $ilDB->query($query);
446  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
447  {
448  return $row->bid;
449  }
450  return 0;
451 
452  case 'pm_bmf':
453  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
454  "WHERE pay_method = '2'";
455 
456  $res = $ilDB->query($query);
457  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
458  {
459  return $row->bid;
460  }
461  return 0;
462 
463  case 'pm_paypal':
464  $query = "SELECT COUNT(booking_id) AS bid FROM payment_statistic ".
465  "WHERE pay_method = '3'";
466 
467  $res = $ilDB->query($query);
468  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
469  {
470  return $row->bid;
471  }
472  return 0;
473 
474  default:
475  return 0;
476  }
477  }
478 
479 
480  // PRIVATE
481  function __read()
482  {
483  $query = 'SELECT * FROM payment_statistic as ps, payment_objects as po';
484  if ($_SESSION["pay_statistics"]["customer"] or $_SESSION['pay_statistics']['vendor'])
485  {
486  $query .= ', usr_data as ud';
487  }
488  $query .= " WHERE ps.pobject_id = po.pobject_id ";
489 
490  if ($_SESSION["pay_statistics"]["transaction_value"] != "")
491  {
492  if ($_SESSION["pay_statistics"]["transaction_type"] == 0)
493  {
494  $query .= "AND transaction_extern LIKE '" . $_SESSION["pay_statistics"]["transaction_value"] . "%' ";
495  }
496  else if ($_SESSION["pay_statistics"]["transaction_type"] == 1)
497  {
498  $query .= "AND transaction_extern LIKE '%" . $_SESSION["pay_statistics"]["transaction_value"] . "' ";
499  }
500  }
501  if ($_SESSION["pay_statistics"]["customer"] != "")
502  {
503  $query .= "AND ud.login LIKE '%" . $_SESSION["pay_statistics"]["customer"] . "%' " .
504  "AND ud.usr_id = ps.customer_id ";
505  }
506  if ($_SESSION["pay_statistics"]["from"]["day"] != "" &&
507  $_SESSION["pay_statistics"]["from"]["month"] != "" &&
508  $_SESSION["pay_statistics"]["from"]["year"] != "")
509  {
510  $from = mktime(0, 0, 0, $_SESSION["pay_statistics"]["from"]["month"],
511  $_SESSION["pay_statistics"]["from"]["day"], $_SESSION["pay_statistics"]["from"]["year"]);
512  $query .= "AND order_date >= '" . $from . "' ";
513  }
514  if ($_SESSION["pay_statistics"]["til"]["day"] != "" &&
515  $_SESSION["pay_statistics"]["til"]["month"] != "" &&
516  $_SESSION["pay_statistics"]["til"]["year"] != "")
517  {
518  $til = mktime(23, 59, 59, $_SESSION["pay_statistics"]["til"]["month"],
519  $_SESSION["pay_statistics"]["til"]["day"], $_SESSION["pay_statistics"]["til"]["year"]);
520  $query .= "AND order_date <= '" . $til . "' ";
521  }
522  if ($_SESSION["pay_statistics"]["payed"] == "0" ||
523  $_SESSION["pay_statistics"]["payed"] == "1")
524  {
525  $query .= "AND payed = '" . $_SESSION["pay_statistics"]["payed"] . "' ";
526  }
527  if ($_SESSION["pay_statistics"]["access"] == "0" ||
528  $_SESSION["pay_statistics"]["access"] == "1")
529  {
530  $query .= "AND access = '" . $_SESSION["pay_statistics"]["access"] . "' ";
531  }
532  if ($_SESSION["pay_statistics"]["pay_method"] == "1" ||
533  $_SESSION["pay_statistics"]["pay_method"] == "2" ||
534  $_SESSION["pay_statistics"]["pay_method"] == "3")
535  {
536  $query .= "AND b_pay_method = '" . $_SESSION["pay_statistics"]["pay_method"] . "' ";
537  }
538 
539  if(!$this->admin_view)
540  {
541  $vendors = $this->__getVendorIds();
542  if (is_array($vendors) &&
543  count($vendors) > 0)
544  {
545  $in = 'ps.b_vendor_id IN (';
546  $in .= implode(',',$vendors);
547  $in .= ')';
548 
549  $query .= "AND ".$in." ";
550  }
551  }
552  else
553  {
554  if($_SESSION['pay_statistics']['vendor'])
555  {
556  $query .= "AND ud.login LIKE '%" . $_SESSION["pay_statistics"]["vendor"] . "%' " .
557  "AND ud.usr_id = ps.b_vendor_id ";
558  }
559  }
560  $query .= "ORDER BY order_date DESC";
561 
562  $res = $this->db->query($query);
563  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
564  {
565  $this->bookings[$row->booking_id]['booking_id'] = $row->booking_id;
566  $this->bookings[$row->booking_id]['transaction'] = $row->transaction;
567  $this->bookings[$row->booking_id]['pobject_id'] = $row->pobject_id;
568  $this->bookings[$row->booking_id]['customer_id'] = $row->customer_id;
569  $this->bookings[$row->booking_id]['order_date'] = $row->order_date;
570  $this->bookings[$row->booking_id]['duration'] = $row->duration;
571  $this->bookings[$row->booking_id]['price'] = $row->price;
572  $this->bookings[$row->booking_id]['discount'] = $row->discount;
573  $this->bookings[$row->booking_id]['payed'] = $row->payed;
574  $this->bookings[$row->booking_id]['access'] = $row->access;
575  $this->bookings[$row->booking_id]['ref_id'] = $row->ref_id;
576  $this->bookings[$row->booking_id]['status'] = $row->status;
577  $this->bookings[$row->booking_id]['pay_method'] = $row->pay_method;
578  $this->bookings[$row->booking_id]['vendor_id'] = $row->vendor_id;
579  $this->bookings[$row->booking_id]['b_vendor_id'] = $row->b_vendor_id;
580  $this->bookings[$row->booking_id]['b_pay_method'] = $row->b_pay_method;
581  $this->bookings[$row->booking_id]['voucher'] = $row->voucher;
582  $this->bookings[$row->booking_id]['transaction_extern'] = $row->transaction_extern;
583  }
584 
585  }
586 
587  function __getVendorIds()
588  {
589  if(ilPaymentVendors::_isVendor($this->user_id))
590  {
591  $vendors[] = $this->user_id;
592  }
593  if($vend = ilPaymentTrustees::_getVendorsForObjects($this->user_id))
594  {
595  foreach($vend as $v)
596  {
597  if(ilPaymentTrustees::_hasStatisticPermissionByVendor($this->user_id,$v))
598  {
599  $vendors[] = $v;
600  #vendors = array_merge($vendors,$v);
601  }
602  }
603  }
604  return $vendors ? $vendors : array();
605  }
606 
607 
608 }
609 ?>