ILIAS  Release_5_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilPaymentBookings.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
11 include_once './Services/Payment/classes/class.ilPaymentVendors.php';
12 include_once './Services/Payment/classes/class.ilPaymentTrustees.php';
13 
14 class ilPaymentBookings
15 {
16  /*
17  * id of vendor, admin or trustee
18  */
19  public $user_id = null;
20  public $db = null;
21 
22  public $bookings = array();
23 
24  public $booking_id = null;
25  public $transaction = null;
26  public $pobject_id = null;
27  public $customer_id = null;
28  public $vendor_id = null;
29  public $pay_method = null;
30  public $order_date = null;
31  public $duration = 0;
32  public $unlimited_duration = 0;
33  public $price = 0;
34  public $discount = 0;
35  public $transaction_extern = null;
36  public $vat_unit = 0;
37  public $object_title = null;
38  public $payed = null;
39  public $access = null;
40  public $voucher = null;
41  public $street = null;
42  public $house_nr = null;
43  public $po_box = null;
44  public $zipcode = null;
45  public $city = null;
46  public $country = null;
47 
48  public $email_extern = null;
49  public $name_extern= null;
50  public $currency_unit = null;
51 
52  public $access_startdate = null;
53  public $access_enddate = null;
54  public $admin_view = false;
55  private $price_type = null;
56 
57  public $access_extension = false;
58 
59  /*
60  * admin_view = true reads all statistic data (only_used in administration)
61  */
62  public function __construct($a_user_id = '',$a_admin_view = false)
63  {
64  global $ilDB;
65 
66  $this->admin_view = $a_admin_view;
67  $this->user_id = $a_user_id;
68  $this->db = $ilDB;
69 
70  include_once './Services/Payment/classes/class.ilPaymentSettings.php';
71  $genSet = ilPaymentSettings::_getInstance();
72  $this->currency_unit = $genSet->get('currency_unit');
73 
74  if($a_user_id)
75  {
76  $this->__read();
77  }
78  }
79 
80  // SET GET
81  public function setBookingId($a_booking_id)
82  {
83  return $this->booking_id = $a_booking_id;
84  }
85  public function getBookingId()
86  {
87  return $this->booking_id;
88  }
89  public function setTransaction($a_transaction)
90  {
91  $this->transaction = $a_transaction;
92  }
93  public function getTransaction()
94  {
95  return $this->transaction;
96  }
97  public function setPobjectId($a_pobject_id)
98  {
99  $this->pobject_id = $a_pobject_id;
100  }
101  public function getPobjectId()
102  {
103  return $this->pobject_id;
104  }
105  public function setCustomerId($a_customer_id)
106  {
107  $this->customer_id = $a_customer_id;
108  }
109  public function getCustomerId()
110  {
111  return $this->customer_id;
112  }
113  public function setVendorId($a_vendor_id)
114  {
115  $this->vendor_id = $a_vendor_id;
116  }
117  public function getVendorId()
118  {
119  return $this->vendor_id;
120  }
121  public function setPayMethod($a_pay_method)
122  {
123  $this->pay_method = $a_pay_method;
124  }
125  public function getPayMethod()
126  {
127  return $this->pay_method;
128  }
129  public function setOrderDate($a_order_date)
130  {
131  $this->order_date = $a_order_date;
132  }
133  public function getOrderDate()
134  {
135  return $this->order_date;
136  }
137  public function setDuration($a_duration)
138  {
139  $this->duration = $a_duration;
140  }
141  public function getDuration()
142  {
143  return $this->duration;
144  }
145  public function setUnlimitedDuration($a_unlimited_duration)
146  {
147  if($a_unlimited_duration == '' || null) $a_unlimited_duration = 0;
148  $this->unlimited_duration = $a_unlimited_duration;
149  }
150 
151  public function getUnlimitedDuration()
152  {
153  return $this->unlimited_duration;
154  }
155 
156  public function setPriceType($a_price_type)
157  {
158  $this->price_type = $a_price_type;
159  }
160  public function getPriceType()
161  {
162  return $this->price_type;
163  }
164 
165  public function setPrice($a_price)
166  {
167  $this->price = $a_price;
168  }
169  public function getPrice()
170  {
171  return $this->price;
172  }
173  public function setDiscount($a_discount)
174  {
175  if($a_discount == null) $a_discount = 0;
176  $this->discount = $a_discount;
177  }
178  public function getDiscount()
179  {
180  if($this->discount == null) $this->discount = 0;
181  return $this->discount;
182  }
183  public function setPayed($a_payed)
184  {
185  $this->payed = $a_payed;
186  }
187  public function getPayedStatus()
188  {
189  return $this->payed;
190  }
191  public function setAccess($a_access)
192  {
193  $this->access = $a_access;
194  }
195  public function getAccessStatus()
196  {
197  return $this->access;
198  }
199  public function setVoucher($a_voucher)
200  {
201  $this->voucher = $a_voucher;
202  }
203  public function getVoucher()
204  {
205  return $this->voucher;
206  }
207  public function setTransactionExtern($a_transaction_extern)
208  {
209  $this->transaction_extern = $a_transaction_extern;
210  }
211  public function getTransactionExtern()
212  {
213  return $this->transaction_extern;
214  }
215 
216  public function getStreet()
217  {
218  return $this->street;
219  }
220  public function setStreet($a_street, $a_house_nr)
221  {
222  $street = $a_street.' '.$a_house_nr;
223  $this->street = $street;
224  }
225  public function getPoBox()
226  {
227  return $this->po_box;
228  }
229  public function setPoBox($a_po_box)
230  {
231  $this->po_box = $a_po_box;
232  }
233 
234  public function getZipcode()
235  {
236  return $this->zipcode;
237  }
238  public function setZipcode($a_zipcode)
239  {
240  $this->zipcode = $a_zipcode;
241  }
242  public function getCity()
243  {
244  return $this->city;
245  }
246  public function setCity($a_city)
247  {
248  $this->city = $a_city;
249  }
250 
251  public function getCountry()
252  {
253  return $this->country;
254  }
255  public function setCountry($a_country)
256  {
257  $this->country = $a_country;
258  }
259  public function setVatUnit($a_vat_unit)
260  {
261 
262  $this->vat_unit = $a_vat_unit;
263  }
264  public function getVatUnit()
265  {
266 
267  return $this->vat_unit;
268  }
269  public function setVatRate($a_vat_rate)
270  {
271  $this->vat_rate = $a_vat_rate;
272  }
273  public function getVatRate()
274  {
275  return $this->vat_rate;
276  }
277  public function setObjectTitle($a_object_title)
278  {
279  $this->object_title = $a_object_title;
280  }
281  function getObjectTitle()
282  {
283  return $this->object_title;
284  }
285 
286  public function setEmailExtern($a_email_extern)
287  {
288  $this->email_extern = $a_email_extern;
289  }
290  public function getEmailExtern()
291  {
292  return $this->email_extern;
293  }
294 
295  public function setNameExtern($a_name_extern)
296  {
297  $this->name_extern = $a_name_extern;
298  }
299  public function getNameExtern()
300  {
301  return $this->name_extern;
302  }
303  public function setCurrencyUnit($a_currency_unit)
304  {
305  $this->currency_unit = $a_currency_unit;
306  }
307  public function getCurrencyUnit()
308  {
309  return $this->currency_unit;
310  }
311 
312  public function setAccessStartdate($a_access_startdate)
313  {
314  $this->access_startdate = $a_access_startdate;
315  }
316  public function getAccessStartdate()
317  {
318  return $this->access_startdate;
319  }
320 
321  public function setAccessEnddate($a_access_enddate)
322  {
323  $this->access_enddate = $a_access_enddate;
324  }
325  public function getAccessEnddate()
326  {
327  return $this->access_enddate;
328  }
329 
330  public function setAccessExtension($a_access_extension)
331  {
332  $this->access_extension = $a_access_extension;
333  }
334  public function getAccessExtension()
335  {
336  return $this->access_extension;
337  }
338 
339  private function __checkExtensionDependencies()
340  {
341  global $ilDB;
342 
343  $ilDB->setLimit(1,0);
344 
345  // check unlimited_duration
346  $res_1 = $ilDB->queryF('SELECT * FROM payment_statistic
347  WHERE pobject_id = %s
348  AND customer_id = %s
349  AND duration = %s
350  AND access_enddate = %s',
351  array('integer','integer', 'integer', 'timestamp'),
352  array($this->getPobjectId(), $this->getCustomerId(), 0, NULL)
353  );
354 
355  $row_1 = $ilDB->fetchAssoc($res_1);
356  if(count($row_1) > 0 )
357  {
358  // error: user already has unlimited duration. not able to extend ...
359  return false;
360  }
361 
362  // get last access_enddate
363  $res_2 = $ilDB->queryF('SELECT * FROM payment_statistic
364  WHERE pobject_id = %s
365  AND customer_id = %s
366  ORDER BY access_enddate DESC',
367  array('integer','integer'),
368  array($this->getPobjectId(), $this->getCustomerId())
369  );
370 
371  $row_2 = $ilDB->fetchAssoc($res_2);
372  if(count($row_2) > 0)
373  {
374  //user has already bought this object
375  // use access_enddate as startdate
376  $old_enddate = $row_2['access_enddate']; # Y-m-d H:i:s
377  $current_date = date("Y-m-d H:i:s", $this->getOrderDate());
378 
379  if($old_enddate <= $current_date)
380  {
381  // Zugriff ist abgelaufen, nimm das aktuelle Datum als startdate
382  $this->setAccessStartdate($current_date);
383  }
384  else
385  {
386  // der Zugriff ist noch nicht abgelaufen, nimm enddate als startdate
387  $this->setAccessStartdate($old_enddate);
388  }
389 
390  if($this->getDuration() > 0)
391  {
392  $this->__calculateAccessEnddate();
393  }
394  }
395  return true;
396  }
397 /*
398  * calculate AccessEnddate for TYPE_DURATION_MONTH !!
399  */
400  private function __calculateAccessEnddate()
401  {
402  include_once('Services/Calendar/classes/class.ilDateTime.php');
403 
404  $tmp_ts = new ilDateTime($this->getAccessStartdate(),IL_CAL_DATETIME);
405  $start_date = $tmp_ts->getUnixTime();
406  $duration = $this->getDuration();
407 
408  $startDateYear = date("Y", $start_date);
409  $startDateMonth = date("m", $start_date);
410  $startDateDay = date("d", $start_date);
411  $startDateHour = date("H", $start_date);
412  $startDateMinute = date("i",$start_date);
413  $startDateSecond = date("s", $start_date);
414 
415  $access_enddate = date("Y-m-d H:i:s", mktime($startDateHour, $startDateMinute, $startDateSecond,
416  $startDateMonth + $duration, $startDateDay, $startDateYear));
417 
418  $this->setAccessEnddate($access_enddate);
419  }
420 
421  public function add()
422  {
423  include_once './Services/Payment/classes/class.ilPaymentPrices.php';
424  switch($this->getPriceType())
425  {
426  case ilPaymentPrices::TYPE_DURATION_DATE:
427  // do nothing. access_startdate, access_enddate is already set
428  break;
429  case ilPaymentPrices::TYPE_DURATION_MONTH:
430  case ilPaymentPrices::TYPE_UNLIMITED_DURATION:
431  if($this->getAccessExtension() == 1)
432  {
433  $this->__checkExtensionDependencies();
434  }
435  else
436  {
437  $this->setAccessStartdate(date('Y-m-d H:i:s', $this->getOrderDate()));
438  if($this->getDuration() > 0)
439  {
440  $this->__calculateAccessEnddate();
441  }
442  }
443  break;
444  default:
445  return false; // price_type_not_set!!!
446  break;
447  }
448 
449  $next_id = $this->db->nextId('payment_statistic');
450  if(ilPayMethods::_EnabledSaveUserAddress((int)$this->getPayMethod()) == 1)
451  {
452  $this->db->insert('payment_statistic',
453  array(
454  'booking_id' => array('integer', $next_id),
455  'transaction' => array('text', $this->getTransaction()),
456  'pobject_id' => array('integer', $this->getPobjectId()),
457  'customer_id' => array('integer', $this->getCustomerId()),
458  'b_vendor_id' => array('integer', $this->getVendorId()),
459  'b_pay_method' => array('integer', $this->getPayMethod()),
460  'order_date' => array('integer', $this->getOrderDate()),
461  'duration' => array('text', $this->getDuration()),
462  'price' => array('float', $this->getPrice()),
463  'discount' => array('float', $this->getDiscount()),
464 
465  'payed' => array('integer', $this->getPayedStatus()),
466  'access_granted'=> array('integer', $this->getAccessStatus()),
467  'voucher' => array('text', $this->getVoucher()),
468  'transaction_extern'=> array('text',$this->getTransactionExtern()),
469  'street' => array('text', $this->getStreet()),
470  'po_box' => array('text', $this->getPoBox()),
471  'zipcode' => array('text', $this->getZipcode()),
472  'city' => array('text', $this->getCity()),
473  'country' => array('text', $this->getCountry()),
474  'vat_rate' => array('float', $this->getVatRate()),
475 
476  'vat_unit' => array('float', $this->getVatUnit()),
477  'object_title' => array('text', $this->getObjectTitle()),
478  'email_extern' => array('text', $this->getEmailExtern()),
479  'name_extern' => array('text', $this->getNameExtern()),
480  'currency_unit' => array('text', $this->getCurrencyUnit()),
481  'access_startdate'=> array('timestamp', $this->getAccessStartdate()),
482  'access_enddate'=> array('timestamp', $this->getAccessEnddate())
483  ));
484  }
485  else
486  {
487  $this->db->insert('payment_statistic',
488  array(
489  'booking_id' => array('integer', $next_id),
490  'transaction' => array('text', $this->getTransaction()),
491  'pobject_id' => array('integer', $this->getPobjectId()),
492  'customer_id' => array('integer', $this->getCustomerId()),
493  'b_vendor_id' => array('integer', $this->getVendorId()),
494  'b_pay_method' => array('integer', $this->getPayMethod()),
495  'order_date' => array('integer', $this->getOrderDate()),
496  'duration' => array('text', $this->getDuration()),
497  'price' => array('float', $this->getPrice()),
498  'discount' => array('float', $this->getDiscount()),
499 
500  'payed' => array('integer', $this->getPayedStatus()),
501  'access_granted'=> array('integer', $this->getAccessStatus()),
502  'voucher' => array('text', $this->getVoucher()),
503  'transaction_extern'=> array('text',$this->getTransactionExtern()),
504 # 'street' => array('text', $this->getStreet()),
505 # 'po_box' => array('text', $this->getPoBox()),
506 # 'zipcode' => array('text', $this->getZipcode()),
507 # 'city' => array('text', $this->getCity()),
508 # 'country' => array('text', $this->getCountry()),
509  'vat_rate' => array('float', $this->getVatRate()),
510 
511  'vat_unit' => array('float', $this->getVatUnit()),
512  'object_title' => array('text', $this->getObjectTitle()),
513  'email_extern' => array('text', $this->getEmailExtern()),
514  'name_extern' => array('text', $this->getNameExtern()),
515  'currency_unit' => array('text', $this->getCurrencyUnit()),
516  'access_startdate'=> array('timestamp', $this->getAccessStartdate()),
517  'access_enddate'=> array('timestamp', $this->getAccessEnddate())
518  ));
519  }
520 
521  return $next_id;
522  }
523 
524  public function update()
525  {
526  if($this->getBookingId())
527  {
528  $this->db->manipulateF('
529  UPDATE payment_statistic
530  SET payed = %s,
531  access_granted = %s
532  WHERE booking_id = %s',
533  array('integer', 'integer', 'integer'),
534  array((int) $this->getPayedStatus(), (int) $this->getAccessStatus(), $this->getBookingId()));
535 
536  return true;
537  }
538  return false;
539  }
540 
541  public function delete()
542  {
543  if($this->getBookingId())
544  {
545  $this->db->manipulateF('
546  DELETE FROM payment_statistic WHERE booking_id = %s',
547  array('integer'),
548  array((int)$this->getBookingId())
549  );
550 
551  return true;
552  }
553  return false;
554  }
555 
556  public static function getBookingsOfCustomer($a_usr_id)
557  {
558  global $ilDB;
559 
560  if(ANONYMOUS_USER_ID == $a_usr_id)
561  return array();
562 
563  $booking = array();
564  $res = $ilDB->queryf('
565  SELECT * from payment_statistic ps, payment_objects po
566  WHERE ps.pobject_id = po.pobject_id
567  AND customer_id = %s
568  ORDER BY order_date DESC',
569  array('integer'),
570  array($a_usr_id)
571  );
572 
573  while($row = $ilDB->fetchAssoc($res))
574  {
575  $booking[$row['booking_id']] = $row;
576  }
577 
578  return $booking;
579  }
580 
581  public function getBookings()
582  {
583  return $this->bookings ? $this->bookings : array();
584  }
585 
586  public function getBooking($a_booking_id)
587  {
588  $booking = array();
589  $res = $this->db->queryf('
590  SELECT * FROM payment_statistic ps, payment_objects po
591  WHERE ps.pobject_id = po.pobject_id
592  AND booking_id = %s',
593  array('integer'),
594  array($a_booking_id));
595 
596  while($row = $this->db->fetchObject($res))
597  {
598  $booking = $row;
599  }
600  return $booking ? $booking : array();
601  }
602 
603  // STATIC
604  public static function _getCountBookingsByVendor($a_vendor_id)
605  {
606  global $ilDB;
607 
608  $res = $ilDB->queryf(
609  'SELECT COUNT(booking_id) bid FROM payment_statistic
610  WHERE b_vendor_id = %s',
611  array('integer'),
612  array($a_vendor_id));
613 
614  while($row = $ilDB->fetchAssoc($res))
615  {
616  return $row['bid'];
617  }
618  return 0;
619  }
620 
621  public static function _getCountBookingsByCustomer($a_vendor_id)
622  {
623  global $ilDB;
624 
625  if(ANONYMOUS_USER_ID == $a_vendor_id)
626  return 0;
627 
628  $res = $ilDB->queryf('
629  SELECT COUNT(booking_id) bid FROM payment_statistic
630  WHERE customer_id = %s',
631  array('integer'),
632  array($a_vendor_id));
633 
634  while($row = $ilDB->fetchObject($res))
635  {
636  return $row->bid;
637  }
638  return 0;
639  }
640 
641  public static function _getCountBookingsByObject($a_pobject_id)
642  {
643  global $ilDB;
644 
645  $res = $ilDB->queryf('
646  SELECT COUNT(booking_id) bid FROM payment_statistic
647  WHERE pobject_id = %s',
648  array('integer'),
649  array($a_pobject_id));
650 
651  while($row = $ilDB->fetchObject($res))
652  {
653  return $row->bid;
654  }
655  return 0;
656  }
657 
658  public static function _hasAccess($a_pobject_id, $a_user_id = 0, $a_transaction = 0)
659  {
660  global $ilDB, $ilUser;
661 
662  if(ANONYMOUS_USER_ID == $ilUser->getId() && !$a_transaction)
663  {
664  return false;
665  }
666  else
667  if($a_transaction)
668  {
669  $res = $ilDB->queryf('
670  SELECT * FROM payment_statistic
671  WHERE pobject_id = %s
672  AND transaction = %s
673  AND payed = %s
674  AND access_granted = %s',
675  array('integer','text', 'integer', 'integer'),
676  array($a_pobject_id, $a_transaction, '1', '1'));
677  }
678  else
679  {
680  $usr_id = $a_user_id ? $a_user_id : $ilUser->getId();
681 
682  $res = $ilDB->queryf('
683  SELECT order_date, duration, access_startdate, access_enddate
684  FROM payment_statistic
685  WHERE pobject_id = %s
686  AND customer_id = %s
687  AND payed = %s
688  AND access_granted = %s',
689  array('integer', 'integer', 'integer', 'integer'),
690  array($a_pobject_id, $usr_id, '1', '1'));
691  }
692 
693  while($row = $ilDB->fetchAssoc($res))
694  {
695  if($row['duration'] == 0 && $row['access_enddate'] == NULL)
696  {
697  return true;
698  }
699  else
700  if($row['access_startdate'] == NULL)
701  {
702  if(time() >= $row['order_date']
703  && date("Y-m-d H:i:s") <= $row['access_enddate'])
704  {
705  return true;
706  }
707  }
708  else
709  if (date("Y-m-d H:i:s") >= $row['access_startdate']
710  && date("Y-m-d H:i:s") <= $row['access_enddate'])
711  {
712  return true;
713  }
714  }
715  return false;
716  }
717 
718  // PRIVATE
719  public function __read()
720  {
721  global $ilUser;
722 
723  if(ANONYMOUS_USER_ID == $ilUser->getId())
724  return false;
725 
726  $data = array();
727  $data_types = array();
728 
729  $query = 'SELECT * FROM payment_statistic ps '
730  . 'INNER JOIN payment_objects po ON po.pobject_id = ps.pobject_id ';
731  if($_SESSION['pay_statistics']['customer'])
732  {
733  $query .= 'LEFT JOIN usr_data ud ON ud.usr_id = ps.customer_id ';
734  }
735  if($_SESSION['pay_statistics']['vendor'] && $this->admin_view)
736  {
737  $query .= 'LEFT JOIN usr_data udv ON udv.usr_id = ps.b_vendor_id ';
738  }
739  $query .= 'WHERE 1 = 1 ';
740  if ($_SESSION['pay_statistics']['transaction_value'] != '')
741  {
742  if ($_SESSION['pay_statistics']['transaction_type'] == 0)
743  {
744  $query .= "AND transaction_extern LIKE %s ";
745  $data_types[] = 'text';
746  $data[] = $_SESSION['pay_statistics']['transaction_value'].'%';
747  }
748  else if ($_SESSION['pay_statistics']['transaction_type'] == 1)
749  {
750  $query .= "AND transaction_extern LIKE %s ";
751  $data_types[] = 'text';
752  $data[] = '%'.$_SESSION['pay_statistics']['transaction_value'];
753  }
754  }
755  if ($_SESSION['pay_statistics']['customer'] != '')
756  {
757  $query .= "AND ud.login LIKE %s ";
758  $data_types[] = 'text';
759  $data[] = '%'.$_SESSION['pay_statistics']['customer'].'%';
760  }
761 
762  if ($_SESSION['pay_statistics']['from']['date']['d'] != '' &&
763  $_SESSION['pay_statistics']['from']['date']['m'] != '' &&
764  $_SESSION['pay_statistics']['from']['date']['y'] != '')
765  {
766 
767  $from = mktime(0, 0, 0, $_SESSION['pay_statistics']['from']['date']['m'],
768  $_SESSION['pay_statistics']['from']['date']['d'],
769  $_SESSION['pay_statistics']['from']['date']['y']);
770  $query .= 'AND order_date >= %s ';
771  $data_types[] = 'integer';
772  $data[] = $from;
773  }
774  if ($_SESSION['pay_statistics']['til']['date']['d'] != '' &&
775  $_SESSION['pay_statistics']['til']['date']['m'] != '' &&
776  $_SESSION['pay_statistics']['til']['date']['y'] != '')
777  {
778  $til = mktime(23, 59, 59, $_SESSION['pay_statistics']['til']['date']['m'],
779  $_SESSION['pay_statistics']['til']['date']['d'],
780  $_SESSION['pay_statistics']['til']['date']['y']);
781  $query .= 'AND order_date <= %s ';
782  $data_types[] = 'integer';
783  $data[] = $til;
784  }
785  if ($_SESSION['pay_statistics']['payed'] == '0' ||
786  $_SESSION['pay_statistics']['payed'] == '1')
787  {
788  $query .= 'AND payed = %s ';
789  $data_types[] = 'integer';
790  $data[] = $_SESSION['pay_statistics']['payed'];
791  }
792  if ($_SESSION['pay_statistics']['access'] == '0' ||
793  $_SESSION['pay_statistics']['access'] == '1')
794  {
795  $query .= 'AND access_granted = %s ';
796  $data_types[] = 'integer';
797  $data[] = $_SESSION['pay_statistics']['access'];
798  }
799  if ($_SESSION['pay_statistics']['pay_method'] == '1' ||
800  $_SESSION['pay_statistics']['pay_method'] == '2' ||
801  $_SESSION['pay_statistics']['pay_method'] == '3')
802  {
803  $query .= 'AND b_pay_method = %s ';
804  $data_types[] = 'integer';
805  $data[] = $_SESSION['pay_statistics']['pay_method'];
806  }
807 
808  if(!$this->admin_view)
809  {
810  $vendors = $this->__getVendorIds();
811 
812  if (is_array($vendors) &&
813  count($vendors) > 1)
814  {
815  $query .= ' AND '.$this->db->in('ps.b_vendor_id', $vendors, false, 'integer').' ';
816  }
817  else if(is_array($vendors) && count($vendors) == 1)
818  {
819  $query .= 'AND ps.b_vendor_id = %s ';
820  $data[] = $vendors['0'];
821  $data_types[] = 'integer';
822  }
823  if((int)$_SESSION['pay_statistics']['filter_title_id'])
824  {
825  $query .= "AND po.ref_id = ".(int)$_SESSION['pay_statistics']['filter_title_id']." ";
826  }
827  }
828  else
829  {
830  if($_SESSION['pay_statistics']['vendor'])
831  {
832  $query .= 'AND udv.login LIKE %s ';
833 
834  $data[] = '%'.$_SESSION['pay_statistics']['vendor'].'%';
835  $data_types[] = 'text';
836  }
837 
838  if((int)$_SESSION['pay_statistics']['adm_filter_title_id'])
839  {
840  $query .= "AND po.ref_id = ".(int)$_SESSION['pay_statistics']['adm_filter_title_id']." ";
841  }
842  }
843  $query .= 'ORDER BY order_date DESC';
844 
845  $cnt_data = count($data);
846  $cnt_data_types = count($data_types);
847 
848  if($cnt_data == 0 || $cnt_data_types == 0)
849  {
850  $res = $this->db->query($query);
851  }
852  else
853  {
854  $res= $this->db->queryf($query, $data_types, $data);
855  }
856 
857  while($row = $this->db->fetchAssoc($res))
858  {
859  $this->bookings[$row['booking_id']] = $row;
860  }
861  return true;
862  }
863 
864  public function __getVendorIds()
865  {
866  if(ilPaymentVendors::_isVendor($this->user_id))
867  {
868  $vendors[] = $this->user_id;
869  }
870 
871  $vend = ilPaymentTrustees::_getVendorsForStatisticsByTrusteeId($this->user_id) ;
872 
873  if(isset ($vend))
874  {
875  foreach($vend as $v)
876  {
877  if(ilPaymentTrustees::_hasStatisticPermissionByVendor($this->user_id,$v))
878  {
879  $vendors[] = $v;
880  }
881  }
882  }
883  return $vendors ? $vendors : array();
884  }
885 
886 
887  public static function __readBillByTransaction($a_user_id, $a_transaction_nr)
888  {
889  global $ilDB;
890 
891  $query = 'SELECT * FROM payment_statistic as ps, payment_objects as po
892  WHERE ps.pobject_id = po.pobject_id
893  AND customer_id = %s
894  AND transaction = %s';
895 
896  $res = $ilDB->queryF($query, array('integer','text'), array($a_user_id, $a_transaction_nr));
897  while($row = $ilDB->fetchAssoc($res))
898  {
899  $bookings[] = $row;
900  }
901 
902  return $bookings;
903  }
904 
905  public function getDistinctTransactions($a_user_id)
906  {
907  global $ilDB;
908 
909  $query = 'SELECT * FROM payment_statistic
910  WHERE customer_id = %s
911  GROUP BY transaction
912  ORDER BY order_date DESC';
913 
914  $res = $ilDB->queryF($query, array('integer'), array($a_user_id));
915  while($row = $ilDB->fetchAssoc($res))
916  {
917  $booking[$row['booking_id']] = $row;
918  }
919  return $booking ? $booking : array();
920  }
921 
922  public function getBookingsByPaymethod($pay_method)
923  {
924  global $ilDB;
925 
926  $res = $ilDB->queryF('
927  SELECT * FROM payment_statistic WHERE b_pay_method = %s', array('integer'), array($pay_method));
928 
929  while($row = $ilDB->fetchAssoc($res))
930  {
931  $booking[] = $row;
932  }
933  return $booking ? $booking : array();
934  }
935 
936  public function deleteAddressesByPaymethod($pay_method)
937  {
938  global $ilDB;
939 
940  $ilDB->manipulateF('
941  UPDATE payment_statistic
942  SET street = null,
943  po_box = null,
944  city = null,
945  zipcode = null,
946  country = null
947  WHERE b_pay_method = %s',
948  array('integer'),
949  array($pay_method));
950  }
951 
952  public static function _readBookingByTransaction($a_transaction)
953  {
954  global $ilDB;
955 
956  $trans_exp = explode('_', $a_transaction);
957  $user_id = $trans_exp[1];
958 
959  $res = $ilDB->queryF('SELECT * FROM payment_statistic
960  WHERE transaction = %s
961  AND payed = %s
962  AND access_granted = %s
963  AND customer_id = %s',
964  array('text', 'integer','integer','integer'),
965  array($a_transaction, 1,1, (int)$user_id));
966  if($row = $ilDB->numRows($res))
967  {
968  return true;
969  }
970  return false;
971  }
972 
973  public function getUniqueTitles()
974  {
975  $query = 'SELECT DISTINCT(po.ref_id) ref_id FROM payment_statistic ps, payment_objects po';
976  $query .= " WHERE ps.pobject_id = po.pobject_id ";
977 
978  if(!$this->admin_view)
979  {
980  $vendors = $this->__getVendorIds();
981 
982  if (is_array($vendors) && count($vendors) > 1)
983  {
984  foreach($vendors as $vendor)
985  {
986  $arr_data[] = '%s';
987  $data[] = $vendor;
988  $data_types[] = 'integer';
989  }
990  $str_data = implode(',',$arr_data);
991 
992  $query .= 'AND ps.b_vendor_id IN ('.$str_data.') ';
993 
994  }
995  else if(is_array($vendors) && count($vendors) == 1)
996  {
997  $query .= 'AND ps.b_vendor_id = %s ';
998  $data[] = $vendors['0'];
999  $data_types[] = 'integer';
1000  }
1001  }
1002  $query .= "ORDER BY order_date DESC";
1003 
1004  if(!$data_types && !$data)
1005  {
1006  $res = $this->db->query($query);
1007  }
1008  else $res = $this->db->queryf($query, $data_types, $data);
1009 
1010  $rows = array();
1011  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
1012  {
1013  $rows[] = $row->ref_id;
1014  }
1015  return is_array($rows) ? $rows : array();
1016  }
1017 
1018  public static function _hasAccesstoExtensionPrice($a_user_id, $a_pobject_id)
1019  {
1020  global $ilDB;
1021 
1022  $res = $ilDB->queryF('SELECT * FROM payment_statistic
1023  WHERE customer_id = %s AND pobject_id = %s
1024  AND duration > %s',
1025  array('integer','integer', 'integer'),
1026  array($a_user_id, $a_pobject_id, 0));
1027 
1028  if($row = $ilDB->numRows($res))
1029  {
1030  return true;
1031  }
1032  return false;
1033  }
1034  public static function _lookupOrder($a_pobject_id)
1035  {
1036  global $ilUser, $ilDB;
1037 
1038  $booking = array();
1039  $res = $ilDB->queryF('
1040  SELECT order_date, duration
1041  FROM payment_statistic
1042  WHERE pobject_id = %s
1043  AND customer_id = %s
1044  ORDER BY order_date DESC',
1045  array('integer', 'integer'),
1046  array($a_pobject_id, $ilUser->getId()));
1047 
1048  while($row = $ilDB->fetchAssoc($res))
1049  {
1050  $booking = $row;
1051  break;
1052  }
1053 
1054  return $booking;
1055  }
1056 }