ILIAS  release_4-3 Revision
 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 
403  include_once('Services/Calendar/classes/class.ilDateTime.php');
404 
405  $tmp_ts = new ilDateTime($this->getAccessStartdate(),IL_CAL_DATETIME);
406  $start_date = $tmp_ts->getUnixTime();
407  $duration = $this->getDuration();
408 
409  $startDateYear = date("Y", $start_date);
410  $startDateMonth = date("m", $start_date);
411  $startDateDay = date("d", $start_date);
412  $startDateHour = date("H", $start_date);
413  $startDateMinute = date("i",$start_date);
414  $startDateSecond = date("s", $start_date);
415 
416  $access_enddate = date("Y-m-d H:i:s", mktime($startDateHour, $startDateMinute, $startDateSecond,
417  $startDateMonth + $duration, $startDateDay, $startDateYear));
418 
419  $this->setAccessEnddate($access_enddate);
420  }
421 
422  public function add()
423  {
424  include_once './Services/Payment/classes/class.ilPaymentPrices.php';
425  switch($this->getPriceType())
426  {
427  case ilPaymentPrices::TYPE_DURATION_DATE:
428  // do nothing. access_startdate, access_enddate is already set
429  break;
430  case ilPaymentPrices::TYPE_DURATION_MONTH:
431  case ilPaymentPrices::TYPE_UNLIMITED_DURATION:
432  if($this->getAccessExtension() == 1)
433  {
434  $this->__checkExtensionDependencies();
435  }
436  else
437  {
438  $this->setAccessStartdate(date('Y-m-d H:i:s', $this->getOrderDate()));
439  if($this->getDuration() > 0)
440  {
441  $this->__calculateAccessEnddate();
442  }
443  }
444  break;
445  default:
446  return false; // price_type_not_set!!!
447  break;
448  }
449 
450  $next_id = $this->db->nextId('payment_statistic');
451  if(ilPayMethods::_EnabledSaveUserAddress((int)$this->getPayMethod()) == 1)
452  {
453  $statement = $this->db->insert('payment_statistic',
454  array(
455  'booking_id' => array('integer', $next_id),
456  'transaction' => array('text', $this->getTransaction()),
457  'pobject_id' => array('integer', $this->getPobjectId()),
458  'customer_id' => array('integer', $this->getCustomerId()),
459  'b_vendor_id' => array('integer', $this->getVendorId()),
460  'b_pay_method' => array('integer', $this->getPayMethod()),
461  'order_date' => array('integer', $this->getOrderDate()),
462  'duration' => array('text', $this->getDuration()),
463  'price' => array('float', $this->getPrice()),
464  'discount' => array('float', $this->getDiscount()),
465 
466  'payed' => array('integer', $this->getPayedStatus()),
467  'access_granted'=> array('integer', $this->getAccessStatus()),
468  'voucher' => array('text', $this->getVoucher()),
469  'transaction_extern'=> array('text',$this->getTransactionExtern()),
470  'street' => array('text', $this->getStreet()),
471  'po_box' => array('text', $this->getPoBox()),
472  'zipcode' => array('text', $this->getZipcode()),
473  'city' => array('text', $this->getCity()),
474  'country' => array('text', $this->getCountry()),
475  'vat_rate' => array('float', $this->getVatRate()),
476 
477  'vat_unit' => array('float', $this->getVatUnit()),
478  'object_title' => array('text', $this->getObjectTitle()),
479  'email_extern' => array('text', $this->getEmailExtern()),
480  'name_extern' => array('text', $this->getNameExtern()),
481  'currency_unit' => array('text', $this->getCurrencyUnit()),
482  'access_startdate'=> array('timestamp', $this->getAccessStartdate()),
483  'access_enddate'=> array('timestamp', $this->getAccessEnddate())
484  ));
485  }
486  else
487  {
488  $statement = $this->db->insert('payment_statistic',
489  array(
490  'booking_id' => array('integer', $next_id),
491  'transaction' => array('text', $this->getTransaction()),
492  'pobject_id' => array('integer', $this->getPobjectId()),
493  'customer_id' => array('integer', $this->getCustomerId()),
494  'b_vendor_id' => array('integer', $this->getVendorId()),
495  'b_pay_method' => array('integer', $this->getPayMethod()),
496  'order_date' => array('integer', $this->getOrderDate()),
497  'duration' => array('text', $this->getDuration()),
498  'price' => array('float', $this->getPrice()),
499  'discount' => array('float', $this->getDiscount()),
500 
501  'payed' => array('integer', $this->getPayedStatus()),
502  'access_granted'=> array('integer', $this->getAccessStatus()),
503  'voucher' => array('text', $this->getVoucher()),
504  'transaction_extern'=> array('text',$this->getTransactionExtern()),
505 # 'street' => array('text', $this->getStreet()),
506 # 'po_box' => array('text', $this->getPoBox()),
507 # 'zipcode' => array('text', $this->getZipcode()),
508 # 'city' => array('text', $this->getCity()),
509 # 'country' => array('text', $this->getCountry()),
510  'vat_rate' => array('float', $this->getVatRate()),
511 
512  'vat_unit' => array('float', $this->getVatUnit()),
513  'object_title' => array('text', $this->getObjectTitle()),
514  'email_extern' => array('text', $this->getEmailExtern()),
515  'name_extern' => array('text', $this->getNameExtern()),
516  'currency_unit' => array('text', $this->getCurrencyUnit()),
517  'access_startdate'=> array('timestamp', $this->getAccessStartdate()),
518  'access_enddate'=> array('timestamp', $this->getAccessEnddate())
519  ));
520  }
521 
522  return $next_id;
523  }
524 
525  public function update()
526  {
527  if($this->getBookingId())
528  {
529  $statement = $this->db->manipulateF('
530  UPDATE payment_statistic
531  SET payed = %s,
532  access_granted = %s
533  WHERE booking_id = %s',
534  array('integer', 'integer', 'integer'),
535  array((int) $this->getPayedStatus(), (int) $this->getAccessStatus(), $this->getBookingId()));
536 
537  return true;
538  }
539  return false;
540  }
541 
542  public function delete()
543  {
544  if($this->getBookingId())
545  {
546  $statement = $this->db->manipulateF('
547  DELETE FROM payment_statistic WHERE booking_id = %s',
548  array('integer'),
549  array((int)$this->getBookingId())
550  );
551 
552  return true;
553  }
554  return false;
555  }
556 
557  public static function getBookingsOfCustomer($a_usr_id)
558  {
559  global $ilDB;
560 
561  if(ANONYMOUS_USER_ID == $a_usr_id)
562  return array();
563 
564  $booking = array();
565  $res = $ilDB->queryf('
566  SELECT * from payment_statistic ps, payment_objects po
567  WHERE ps.pobject_id = po.pobject_id
568  AND customer_id = %s
569  ORDER BY order_date DESC',
570  array('integer'),
571  array($a_usr_id)
572  );
573 
574  while($row = $ilDB->fetchAssoc($res))
575  {
576  $booking[$row['booking_id']] = $row;
577  }
578 
579  return $booking;
580  }
581 
582  public function getBookings()
583  {
584  return $this->bookings ? $this->bookings : array();
585  }
586 
587  public function getBooking($a_booking_id)
588  {
589  $booking = array();
590  $res = $this->db->queryf('
591  SELECT * FROM payment_statistic ps, payment_objects po
592  WHERE ps.pobject_id = po.pobject_id
593  AND booking_id = %s',
594  array('integer'),
595  array($a_booking_id));
596 
597  while($row = $this->db->fetchObject($res))
598  {
599  $booking = $row;
600  }
601  return $booking ? $booking : array();
602  }
603 
604  // STATIC
605  public static function _getCountBookingsByVendor($a_vendor_id)
606  {
607  global $ilDB;
608 
609  $res = $ilDB->queryf(
610  'SELECT COUNT(booking_id) bid FROM payment_statistic
611  WHERE b_vendor_id = %s',
612  array('integer'),
613  array($a_vendor_id));
614 
615  while($row = $ilDB->fetchAssoc($res))
616  {
617  return $row['bid'];
618  }
619  return 0;
620  }
621 
622  public static function _getCountBookingsByCustomer($a_vendor_id)
623  {
624  global $ilDB;
625 
626  if(ANONYMOUS_USER_ID == $a_vendor_id)
627  return 0;
628 
629  $res = $ilDB->queryf('
630  SELECT COUNT(booking_id) bid FROM payment_statistic
631  WHERE customer_id = %s',
632  array('integer'),
633  array($a_vendor_id));
634 
635  while($row = $ilDB->fetchObject($res))
636  {
637  return $row->bid;
638  }
639  return 0;
640  }
641 
642  public static function _getCountBookingsByObject($a_pobject_id)
643  {
644  global $ilDB;
645 
646  $res = $ilDB->queryf('
647  SELECT COUNT(booking_id) bid FROM payment_statistic
648  WHERE pobject_id = %s',
649  array('integer'),
650  array($a_pobject_id));
651 
652  while($row = $ilDB->fetchObject($res))
653  {
654  return $row->bid;
655  }
656  return 0;
657  }
658 
659  public static function _hasAccess($a_pobject_id, $a_user_id = 0, $a_transaction = 0)
660  {
661  global $ilDB, $ilUser;
662 
663  if(ANONYMOUS_USER_ID == $ilUser->getId() && !$a_transaction)
664  {
665  return false;
666  }
667  else
668  if($a_transaction)
669  {
670  $res = $ilDB->queryf('
671  SELECT * FROM payment_statistic
672  WHERE pobject_id = %s
673  AND transaction = %s
674  AND payed = %s
675  AND access_granted = %s',
676  array('integer','text', 'integer', 'integer'),
677  array($a_pobject_id, $a_transaction, '1', '1'));
678  }
679  else
680  {
681  $usr_id = $a_user_id ? $a_user_id : $ilUser->getId();
682 
683  $res = $ilDB->queryf('
684  SELECT order_date, duration, access_startdate, access_enddate
685  FROM payment_statistic
686  WHERE pobject_id = %s
687  AND customer_id = %s
688  AND payed = %s
689  AND access_granted = %s',
690  array('integer', 'integer', 'integer', 'integer'),
691  array($a_pobject_id, $usr_id, '1', '1'));
692  }
693 
694  while($row = $ilDB->fetchAssoc($res))
695  {
696  if($row['duration'] == 0 && $row['access_enddate'] == NULL)
697  {
698  return true;
699  }
700  else
701  if($row['access_startdate'] == NULL)
702  {
703  if(time() >= $row['order_date']
704  && date("Y-m-d H:i:s") <= $row['access_enddate'])
705  {
706  return true;
707  }
708  }
709  else
710  if (date("Y-m-d H:i:s") >= $row['access_startdate']
711  && date("Y-m-d H:i:s") <= $row['access_enddate'])
712  {
713  return true;
714  }
715  }
716  return false;
717  }
718 
719  // PRIVATE
720  public function __read()
721  {
722  global $ilUser;
723 
724  if(ANONYMOUS_USER_ID == $ilUser->getId())
725  return false;
726 
727  $data = array();
728  $data_types = array();
729 
730  $query = 'SELECT * FROM payment_statistic ps '
731  . 'INNER JOIN payment_objects po ON po.pobject_id = ps.pobject_id ';
732  if($_SESSION['pay_statistics']['customer'])
733  {
734  $query .= 'LEFT JOIN usr_data ud ON ud.usr_id = ps.customer_id ';
735  }
736  if($_SESSION['pay_statistics']['vendor'] && $this->admin_view)
737  {
738  $query .= 'LEFT JOIN usr_data udv ON udv.usr_id = ps.b_vendor_id ';
739  }
740  $query .= 'WHERE 1 = 1 ';
741  if ($_SESSION['pay_statistics']['transaction_value'] != '')
742  {
743  if ($_SESSION['pay_statistics']['transaction_type'] == 0)
744  {
745  $query .= "AND transaction_extern LIKE %s ";
746  $data_types[] = 'text';
747  $data[] = $_SESSION['pay_statistics']['transaction_value'].'%';
748  }
749  else if ($_SESSION['pay_statistics']['transaction_type'] == 1)
750  {
751  $query .= "AND transaction_extern LIKE %s ";
752  $data_types[] = 'text';
753  $data[] = '%'.$_SESSION['pay_statistics']['transaction_value'];
754  }
755  }
756  if ($_SESSION['pay_statistics']['customer'] != '')
757  {
758  $query .= "AND ud.login LIKE %s ";
759  $data_types[] = 'text';
760  $data[] = '%'.$_SESSION['pay_statistics']['customer'].'%';
761  }
762 
763  if ($_SESSION['pay_statistics']['from']['date']['d'] != '' &&
764  $_SESSION['pay_statistics']['from']['date']['m'] != '' &&
765  $_SESSION['pay_statistics']['from']['date']['y'] != '')
766  {
767 
768  $from = mktime(0, 0, 0, $_SESSION['pay_statistics']['from']['date']['m'],
769  $_SESSION['pay_statistics']['from']['date']['d'],
770  $_SESSION['pay_statistics']['from']['date']['y']);
771  $query .= 'AND order_date >= %s ';
772  $data_types[] = 'integer';
773  $data[] = $from;
774  }
775  if ($_SESSION['pay_statistics']['til']['date']['d'] != '' &&
776  $_SESSION['pay_statistics']['til']['date']['m'] != '' &&
777  $_SESSION['pay_statistics']['til']['date']['y'] != '')
778  {
779  $til = mktime(23, 59, 59, $_SESSION['pay_statistics']['til']['date']['m'],
780  $_SESSION['pay_statistics']['til']['date']['d'],
781  $_SESSION['pay_statistics']['til']['date']['y']);
782  $query .= 'AND order_date <= %s ';
783  $data_types[] = 'integer';
784  $data[] = $til;
785  }
786  if ($_SESSION['pay_statistics']['payed'] == '0' ||
787  $_SESSION['pay_statistics']['payed'] == '1')
788  {
789  $query .= 'AND payed = %s ';
790  $data_types[] = 'integer';
791  $data[] = $_SESSION['pay_statistics']['payed'];
792  }
793  if ($_SESSION['pay_statistics']['access'] == '0' ||
794  $_SESSION['pay_statistics']['access'] == '1')
795  {
796  $query .= 'AND access_granted = %s ';
797  $data_types[] = 'integer';
798  $data[] = $_SESSION['pay_statistics']['access'];
799  }
800  if ($_SESSION['pay_statistics']['pay_method'] == '1' ||
801  $_SESSION['pay_statistics']['pay_method'] == '2' ||
802  $_SESSION['pay_statistics']['pay_method'] == '3')
803  {
804  $query .= 'AND b_pay_method = %s ';
805  $data_types[] = 'integer';
806  $data[] = $_SESSION['pay_statistics']['pay_method'];
807  }
808 
809  if(!$this->admin_view)
810  {
811  $vendors = $this->__getVendorIds();
812 
813  if (is_array($vendors) &&
814  count($vendors) > 1)
815  {
816  $query .= ' AND '.$this->db->in('ps.b_vendor_id', $vendors, false, 'integer').' ';
817  }
818  else if(is_array($vendors) && count($vendors) == 1)
819  {
820  $query .= 'AND ps.b_vendor_id = %s ';
821  $data[] = $vendors['0'];
822  $data_types[] = 'integer';
823  }
824  if((int)$_SESSION['pay_statistics']['filter_title_id'])
825  {
826  $query .= "AND po.ref_id = ".(int)$_SESSION['pay_statistics']['filter_title_id']." ";
827  }
828  }
829  else
830  {
831  if($_SESSION['pay_statistics']['vendor'])
832  {
833  $query .= 'AND udv.login LIKE %s ';
834 
835  $data[] = '%'.$_SESSION['pay_statistics']['vendor'].'%';
836  $data_types[] = 'text';
837  }
838 
839  if((int)$_SESSION['pay_statistics']['adm_filter_title_id'])
840  {
841  $query .= "AND po.ref_id = ".(int)$_SESSION['pay_statistics']['adm_filter_title_id']." ";
842  }
843  }
844  $query .= 'ORDER BY order_date DESC';
845 
846  $cnt_data = count($data);
847  $cnt_data_types = count($data_types);
848 
849  if($cnt_data == 0 || $cnt_data_types == 0)
850  {
851  $res = $this->db->query($query);
852  }
853  else
854  {
855  $res= $this->db->queryf($query, $data_types, $data);
856  }
857 
858  while($row = $this->db->fetchAssoc($res))
859  {
860  $this->bookings[$row['booking_id']] = $row;
861  }
862  return true;
863  }
864 
865  public function __getVendorIds()
866  {
867  if(ilPaymentVendors::_isVendor($this->user_id))
868  {
869  $vendors[] = $this->user_id;
870  }
871 
872  $vend = ilPaymentTrustees::_getVendorsForStatisticsByTrusteeId($this->user_id) ;
873 
874  if(isset ($vend))
875  {
876  foreach($vend as $v)
877  {
878  if(ilPaymentTrustees::_hasStatisticPermissionByVendor($this->user_id,$v))
879  {
880  $vendors[] = $v;
881  }
882  }
883  }
884  return $vendors ? $vendors : array();
885  }
886 
887 
888  public static function __readBillByTransaction($a_user_id, $a_transaction_nr)
889  {
890  global $ilDB;
891 
892  $query = 'SELECT * FROM payment_statistic as ps, payment_objects as po
893  WHERE ps.pobject_id = po.pobject_id
894  AND customer_id = %s
895  AND transaction = %s';
896 
897  $res = $ilDB->queryF($query, array('integer','text'), array($a_user_id, $a_transaction_nr));
898  while($row = $ilDB->fetchAssoc($res))
899  {
900  $bookings[] = $row;
901  }
902 
903  return $bookings;
904  }
905 
906  public function getDistinctTransactions($a_user_id)
907  {
908  global $ilDB;
909 
910  $query = 'SELECT * FROM payment_statistic
911  WHERE customer_id = %s
912  GROUP BY transaction
913  ORDER BY order_date DESC';
914 
915  $res = $ilDB->queryF($query, array('integer'), array($a_user_id));
916  while($row = $ilDB->fetchAssoc($res))
917  {
918  $booking[$row['booking_id']] = $row;
919  }
920  return $booking ? $booking : array();
921  }
922 
923  public function getBookingsByPaymethod($pay_method)
924  {
925  global $ilDB;
926 
927  $res = $ilDB->queryF('
928  SELECT * FROM payment_statistic WHERE b_pay_method = %s', array('integer'), array($pay_method));
929 
930  while($row = $ilDB->fetchAssoc($res))
931  {
932  $booking[] = $row;
933  }
934  return $booking ? $booking : array();
935  }
936 
937  public function deleteAddressesByPaymethod($pay_method)
938  {
939  global $ilDB;
940 
941  $ilDB->manipulateF('
942  UPDATE payment_statistic
943  SET street = null,
944  po_box = null,
945  city = null,
946  zipcode = null,
947  country = null
948  WHERE b_pay_method = %s',
949  array('integer'),
950  array($pay_method));
951  }
952 
953  public static function _readBookingByTransaction($a_transaction)
954  {
955  global $ilDB;
956 
957  $trans_exp = explode('_', $a_transaction);
958  $user_id = $trans_exp[1];
959 
960  $res = $ilDB->queryF('SELECT * FROM payment_statistic
961  WHERE transaction = %s
962  AND payed = %s
963  AND access_granted = %s
964  AND customer_id = %s',
965  array('text', 'integer','integer','integer'),
966  array($a_transaction, 1,1, (int)$user_id));
967  if($row = $ilDB->numRows($res))
968  {
969  return true;
970  }
971  return false;
972  }
973 
974  public function getUniqueTitles()
975  {
976  $query = 'SELECT DISTINCT(po.ref_id) ref_id FROM payment_statistic ps, payment_objects po';
977  $query .= " WHERE ps.pobject_id = po.pobject_id ";
978 
979  if(!$this->admin_view)
980  {
981  $vendors = $this->__getVendorIds();
982 
983  if (is_array($vendors) && count($vendors) > 1)
984  {
985  foreach($vendors as $vendor)
986  {
987  $arr_data[] = '%s';
988  $data[] = $vendor;
989  $data_types[] = 'integer';
990  }
991  $str_data = implode(',',$arr_data);
992 
993  $query .= 'AND ps.b_vendor_id IN ('.$str_data.') ';
994 
995  }
996  else if(is_array($vendors) && count($vendors) == 1)
997  {
998  $query .= 'AND ps.b_vendor_id = %s ';
999  $data[] = $vendors['0'];
1000  $data_types[] = 'integer';
1001  }
1002  }
1003  $query .= "ORDER BY order_date DESC";
1004 
1005  if(!$data_types && !$data)
1006  {
1007  $res = $this->db->query($query);
1008  }
1009  else $res = $this->db->queryf($query, $data_types, $data);
1010 
1011  $rows = array();
1012  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
1013  {
1014  $rows[] = $row->ref_id;
1015  }
1016  return is_array($rows) ? $rows : array();
1017  }
1018 
1019  public static function _hasAccesstoExtensionPrice($a_user_id, $a_pobject_id)
1020  {
1021  global $ilDB;
1022 
1023  $res = $ilDB->queryF('SELECT * FROM payment_statistic
1024  WHERE customer_id = %s AND pobject_id = %s
1025  AND duration > %s',
1026  array('integer','integer', 'integer'),
1027  array($a_user_id, $a_pobject_id, 0));
1028 
1029  if($row = $ilDB->numRows($res))
1030  {
1031  return true;
1032  }
1033  return false;
1034  }
1035  public static function _lookupOrder($a_pobject_id)
1036  {
1037  global $ilUser, $ilDB;
1038 
1039  $booking = array();
1040  $res = $ilDB->queryF('
1041  SELECT order_date, duration
1042  FROM payment_statistic
1043  WHERE pobject_id = %s
1044  AND customer_id = %s
1045  ORDER BY order_date DESC',
1046  array('integer', 'integer'),
1047  array($a_pobject_id, $ilUser->getId()));
1048 
1049  while($row = $ilDB->fetchAssoc($res))
1050  {
1051  $booking = $row;
1052  break;
1053  }
1054 
1055  return $booking;
1056  }
1057 }
1058 ?>