1: | <?php
|
2: | namespace Opencart\Admin\Model\Sale;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Subscription extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
13: | |
14: | |
15: | |
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: | |
23: | |
24: | |
25: | |
26: | |
27: | |
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: | |
35: | |
36: | |
37: | |
38: | |
39: | |
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: | |
47: | |
48: | |
49: | |
50: | |
51: | |
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: | |
59: | |
60: | |
61: | |
62: | |
63: | |
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: | |
71: | |
72: | |
73: | |
74: | |
75: | |
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: | |
83: | |
84: | |
85: | |
86: | |
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: | |
94: | |
95: | |
96: | |
97: | |
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: | |
117: | |
118: | |
119: | |
120: | |
121: | |
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: | |
131: | |
132: | |
133: | |
134: | |
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: | |
225: | |
226: | |
227: | |
228: | |
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: | |
274: | |
275: | |
276: | |
277: | |
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: | |
287: | |
288: | |
289: | |
290: | |
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: | |
300: | |
301: | |
302: | |
303: | |
304: | |
305: | |
306: | |
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: | |
316: | |
317: | |
318: | |
319: | |
320: | |
321: | |
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: | |
339: | |
340: | |
341: | |
342: | |
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: | |
352: | |
353: | |
354: | |
355: | |
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: | |