1: <?php
2: namespace Opencart\Admin\Model\Sale;
3: /**
4: * Class Subscription
5: *
6: * @package Opencart\Admin\Model\Sale
7: */
8: class Subscription extends \Opencart\System\Engine\Model {
9: /**
10: * Edit Subscription
11: *
12: * @param int $subscription_id
13: * @param array<string, mixed> $data
14: *
15: * @return void
16: */
17: public function editSubscription(int $subscription_id, array $data): void {
18: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `subscription_plan_id` = '" . (int)$data['subscription_plan_id'] . "', `customer_payment_id` = '" . (int)$data['customer_payment_id'] . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
19: }
20:
21: /**
22: * Edit Payment Method
23: *
24: * @param int $subscription_id
25: * @param int $customer_payment_id
26: *
27: * @return void
28: */
29: public function editPaymentMethod(int $subscription_id, int $customer_payment_id): void {
30: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `customer_payment_id` = '" . (int)$customer_payment_id . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
31: }
32:
33: /**
34: * Edit Subscription Plan
35: *
36: * @param int $subscription_id
37: * @param int $subscription_plan_id
38: *
39: * @return void
40: */
41: public function editSubscriptionPlan(int $subscription_id, int $subscription_plan_id): void {
42: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `subscription_plan_id` = '" . (int)$subscription_plan_id . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
43: }
44:
45: /**
46: * Edit Remaining
47: *
48: * @param int $subscription_id
49: * @param int $remaining
50: *
51: * @return void
52: */
53: public function editRemaining(int $subscription_id, int $remaining): void {
54: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `remaining` = '" . (int)$remaining . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
55: }
56:
57: /**
58: * Edit Trial Remaining
59: *
60: * @param int $subscription_id
61: * @param int $trial_remaining
62: *
63: * @return void
64: */
65: public function editTrialRemaining(int $subscription_id, int $trial_remaining): void {
66: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `trial_remaining` = '" . (int)$trial_remaining . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
67: }
68:
69: /**
70: * Edit Date Next
71: *
72: * @param int $subscription_id
73: * @param string $date_next
74: *
75: * @return void
76: */
77: public function editDateNext(int $subscription_id, string $date_next): void {
78: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `date_next` = '" . $this->db->escape($date_next) . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
79: }
80:
81: /**
82: * Delete Subscription By Customer Payment ID
83: *
84: * @param int $customer_payment_id
85: *
86: * @return void
87: */
88: public function deleteSubscriptionByCustomerPaymentId(int $customer_payment_id): void {
89: $this->db->query("DELETE FROM `" . DB_PREFIX . "subscription` WHERE `customer_payment_id` = '" . (int)$customer_payment_id . "'");
90: }
91:
92: /**
93: * Get Subscription
94: *
95: * @param int $subscription_id
96: *
97: * @return array<string, mixed>
98: */
99: public function getSubscription(int $subscription_id): array {
100: $subscription_data = [];
101:
102: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "subscription` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
103:
104: if ($query->num_rows) {
105: $subscription_data = $query->row;
106:
107: $subscription_data['option'] = ($query->row['option'] ? json_decode($query->row['option'], true) : '');
108: $subscription_data['payment_method'] = ($query->row['payment_method'] ? json_decode($query->row['payment_method'], true) : '');
109: $subscription_data['shipping_method'] = ($query->row['shipping_method'] ? json_decode($query->row['shipping_method'], true) : '');
110: }
111:
112: return $subscription_data;
113: }
114:
115: /**
116: * Get Subscription By Order Product ID
117: *
118: * @param int $order_id
119: * @param int $order_product_id
120: *
121: * @return array<string, mixed>
122: */
123: public function getSubscriptionByOrderProductId(int $order_id, int $order_product_id): array {
124: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "subscription` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
125:
126: return $query->row;
127: }
128:
129: /**
130: * Get Subscriptions
131: *
132: * @param array<string, mixed> $data
133: *
134: * @return array<int, array<string, mixed>>
135: */
136: public function getSubscriptions(array $data): array {
137: $sql = "SELECT `s`.`subscription_id`, `s`.*, CONCAT(`o`.`firstname`, ' ', `o`.`lastname`) AS customer, (SELECT `ss`.`name` FROM `" . DB_PREFIX . "subscription_status` `ss` WHERE `ss`.`subscription_status_id` = `s`.`subscription_status_id` AND `ss`.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS `subscription_status` FROM `" . DB_PREFIX . "subscription` `s` LEFT JOIN `" . DB_PREFIX . "order` `o` ON (`s`.`order_id` = `o`.`order_id`)";
138:
139: $implode = [];
140:
141: if (!empty($data['filter_subscription_id'])) {
142: $implode[] = "`s`.`subscription_id` = '" . (int)$data['filter_subscription_id'] . "'";
143: }
144:
145: if (!empty($data['filter_order_id'])) {
146: $implode[] = "`s`.`order_id` = '" . (int)$data['filter_order_id'] . "'";
147: }
148:
149: if (!empty($data['filter_order_product_id'])) {
150: $implode[] = "`s`.`order_product_id` = '" . (int)$data['filter_order_product_id'] . "'";
151: }
152:
153: if (!empty($data['filter_customer_payment_id'])) {
154: $implode[] = "`s`.`customer_payment_id` = " . (int)$data['filter_customer_payment_id'];
155: }
156:
157: if (!empty($data['filter_customer_id'])) {
158: $implode[] = "`s`.`customer_id` = " . (int)$data['filter_customer_id'];
159: }
160:
161: if (!empty($data['filter_customer'])) {
162: $implode[] = "LCASE(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_customer']) . '%') . "'";
163: }
164:
165: if (!empty($data['filter_date_next'])) {
166: $implode[] = "DATE(`s`.`date_next`) = DATE('" . $this->db->escape((string)$data['filter_date_next']) . "')";
167: }
168:
169: if (!empty($data['filter_subscription_status_id'])) {
170: $implode[] = "`s`.`subscription_status_id` = '" . (int)$data['filter_subscription_status_id'] . "'";
171: }
172:
173: if (!empty($data['filter_date_from'])) {
174: $implode[] = "DATE(`s`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
175: }
176:
177: if (!empty($data['filter_date_to'])) {
178: $implode[] = "DATE(`s`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
179: }
180:
181: if ($implode) {
182: $sql .= " WHERE " . implode(" AND ", $implode);
183: }
184:
185: $sort_data = [
186: 's.subscription_id',
187: 's.order_id',
188: 's.reference',
189: 'customer',
190: 's.subscription_status',
191: 's.date_added'
192: ];
193:
194: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
195: $sql .= " ORDER BY " . $data['sort'];
196: } else {
197: $sql .= " ORDER BY `s`.`subscription_id`";
198: }
199:
200: if (isset($data['order']) && ($data['order'] == 'DESC')) {
201: $sql .= " DESC";
202: } else {
203: $sql .= " ASC";
204: }
205:
206: if (isset($data['start']) || isset($data['limit'])) {
207: if ($data['start'] < 0) {
208: $data['start'] = 0;
209: }
210:
211: if ($data['limit'] < 1) {
212: $data['limit'] = 20;
213: }
214:
215: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
216: }
217:
218: $query = $this->db->query($sql);
219:
220: return $query->rows;
221: }
222:
223: /**
224: * Get Total Subscriptions
225: *
226: * @param array<string, mixed> $data
227: *
228: * @return int
229: */
230: public function getTotalSubscriptions(array $data = []): int {
231: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "subscription` `s` LEFT JOIN `" . DB_PREFIX . "order` `o` ON (`s`.`order_id` = `o`.`order_id`)";
232:
233: $implode = [];
234:
235: if (!empty($data['filter_subscription_id'])) {
236: $implode[] = "`s`.`subscription_id` = '" . (int)$data['filter_subscription_id'] . "'";
237: }
238:
239: if (!empty($data['filter_order_id'])) {
240: $implode[] = "`s`.`order_id` = '" . (int)$data['filter_order_id'] . "'";
241: }
242:
243: if (!empty($data['filter_customer_id'])) {
244: $implode[] = "`s`.`customer_id` = " . (int)$data['filter_customer_id'];
245: }
246:
247: if (!empty($data['filter_customer'])) {
248: $implode[] = "LCASE(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_customer']) . '%') . "'";
249: }
250:
251: if (!empty($data['filter_subscription_status_id'])) {
252: $implode[] = "`s`.`subscription_status_id` = '" . (int)$data['filter_subscription_status_id'] . "'";
253: }
254:
255: if (!empty($data['filter_date_from'])) {
256: $implode[] = "DATE(`s`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
257: }
258:
259: if (!empty($data['filter_date_to'])) {
260: $implode[] = "DATE(`s`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
261: }
262:
263: if ($implode) {
264: $sql .= " WHERE " . implode(" AND ", $implode);
265: }
266:
267: $query = $this->db->query($sql);
268:
269: return (int)$query->row['total'];
270: }
271:
272: /**
273: * Get Total Subscriptions By Store ID
274: *
275: * @param int $store_id
276: *
277: * @return int
278: */
279: public function getTotalSubscriptionsByStoreId(int $store_id): int {
280: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "subscription` WHERE `store_id` = '" . (int)$store_id . "'");
281:
282: return (int)$query->row['total'];
283: }
284:
285: /**
286: * Get Total Subscriptions By Subscription Status ID
287: *
288: * @param int $subscription_status_id
289: *
290: * @return int
291: */
292: public function getTotalSubscriptionsBySubscriptionStatusId(int $subscription_status_id): int {
293: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "subscription` WHERE `subscription_status_id` = '" . (int)$subscription_status_id . "'");
294:
295: return (int)$query->row['total'];
296: }
297:
298: /**
299: * Add History
300: *
301: * @param int $subscription_id
302: * @param int $subscription_status_id
303: * @param string $comment
304: * @param bool $notify
305: *
306: * @return void
307: */
308: public function addHistory(int $subscription_id, int $subscription_status_id, string $comment = '', bool $notify = false): void {
309: $this->db->query("INSERT INTO `" . DB_PREFIX . "subscription_history` SET `subscription_id` = '" . (int)$subscription_id . "', `subscription_status_id` = '" . (int)$subscription_status_id . "', `comment` = '" . $this->db->escape($comment) . "', `notify` = '" . (int)$notify . "', `date_added` = NOW()");
310:
311: $this->db->query("UPDATE `" . DB_PREFIX . "subscription` SET `subscription_status_id` = '" . (int)$subscription_status_id . "' WHERE `subscription_id` = '" . (int)$subscription_id . "'");
312: }
313:
314: /**
315: * Get Histories
316: *
317: * @param int $subscription_id
318: * @param int $start
319: * @param int $limit
320: *
321: * @return array<int, array<string, mixed>>
322: */
323: public function getHistories(int $subscription_id, int $start = 0, int $limit = 10): array {
324: if ($start < 0) {
325: $start = 0;
326: }
327:
328: if ($limit < 1) {
329: $limit = 10;
330: }
331:
332: $query = $this->db->query("SELECT `sh`.`date_added`, `ss`.`name` AS `status`, `sh`.`comment`, `sh`.`notify` FROM `" . DB_PREFIX . "subscription_history` `sh` LEFT JOIN `" . DB_PREFIX . "subscription_status` `ss` ON `sh`.`subscription_status_id` = `ss`.`subscription_status_id` WHERE `sh`.`subscription_id` = '" . (int)$subscription_id . "' AND `ss`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `sh`.`date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
333:
334: return $query->rows;
335: }
336:
337: /**
338: * Get Total Histories
339: *
340: * @param int $subscription_id
341: *
342: * @return int
343: */
344: public function getTotalHistories(int $subscription_id): int {
345: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "subscription_history` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
346:
347: return (int)$query->row['total'];
348: }
349:
350: /**
351: * Get Total Histories By Subscription Status ID
352: *
353: * @param int $subscription_status_id
354: *
355: * @return int
356: */
357: public function getTotalHistoriesBySubscriptionStatusId(int $subscription_status_id): int {
358: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "subscription_history` WHERE `subscription_status_id` = '" . (int)$subscription_status_id . "'");
359:
360: return (int)$query->row['total'];
361: }
362: }
363: