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