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