db.php — Daily Prayer Time 2020.05.04

daily-prayer-time-for-mosques / Models / db.php

daily-prayer-time-for-mosques / Models Last commit date

Processors 6 years ago AssetsLoader.php 6 years ago DPTAjaxHandler.php 7 years ago DSTemplateLoader.php 7 years ago DailyShortCode.php 6 years ago DigitalScreen.php 6 years ago HijriDate.php 7 years ago Init.php 6 years ago MonthlyShortCode.php 7 years ago MonthlyTimeTable.php 6 years ago UpdateStyles.php 7 years ago Validator.php 7 years ago db.php 6 years ago dptWidget.php 7 years ago

db.php

276 lines

1	<?php
2
3	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
4
5	class DatabaseConnection
6	{
7	/** @var string */
8	private $dbTable = "";
9
10	/** @var string */
11	private $tableName = "";
12
13	/** @var array */
14	private $transients = array('prayerTimeForToday', 'fajrJamahForTomorrow', 'jamahChanges', 'fajrForTomorrow');
15
16	public function __construct()
17	{
18	global $wpdb;
19
20	$this->tableName = $wpdb->prefix . "timetable";
21	$this->dbTable = "`".DB_NAME ."`.`" .$this->tableName."`";
22	$this->createTableIfNotExist();
23	}
24
25	/**
26	* @return array
27	*/
28	public function getPrayerTimeForToday()
29	{
30	global $wpdb;
31
32	$today = user_current_time( 'Y-m-d' );
33	$sql = "SELECT * FROM $this->dbTable WHERE d_date = '$today' LIMIT 1";
34	$result = $wpdb->get_row($sql, ARRAY_A);
35	$result['jamah_changes'] = $this->getJamahChanges();
36
37	return $result;
38	}
39
40	public function getIqamahTimeForToday()
41	{
42	error_log('iqamah time for today');
43	$result = $this->getPrayerTimeForToday();
44	return array(
45	'fajr' => $result['fajr_jamah'],
46	'sunrise' => $result['sunrise'],
47	'zuhr' => $result['zuhr_jamah'],
48	'asr' => $result['asr_jamah'],
49	'maghrib' => $result['maghrib_jamah'],
50	'isha' => $result['isha_jamah']
51	);
52	}
53
54
55
56	/**
57	* @return array
58	*/
59	public function getFajrJamahForTomorrow()
60	{
61	if (false === ( $result = $this->getTransient( 'fajrForTomorrow' )) ) {
62	error_log('fajr for tomorrow');
63	global $wpdb;
64
65	$sql = "SELECT fajr_jamah FROM $this->dbTable WHERE d_date = CURDATE() + INTERVAL 1 DAY;";
66	$row = $wpdb->get_row($sql, ARRAY_A);
67	$result = $row['fajr_jamah'];
68
69	set_transient('fajrForTomorrow', $result,DAY_IN_SECONDS );
70	}
71
72	return $result;
73	}
74
75	/**
76	* @param int $min
77	* @return array
78	*/
79	public function getJamahChanges($min=null)
80	{
81	error_log('jamah chnages');
82	$xmin = get_option( 'jamah_changes' );
83	$xmin = empty($min) ? $xmin : $min;
84
85	if ( $xmin < 1) {
86	return;
87	}
88
89	global $wpdb;
90
91	$sql = "SELECT
92	abs(TIME_TO_SEC(TIMEDIFF(today.fajr_jamah, tomorrow.fajr_jamah)) / 60) as fajr_jamah,
93	abs(TIME_TO_SEC(TIMEDIFF(today.zuhr_jamah, tomorrow.zuhr_jamah)) /60) as zuhr_jamah,
94	abs(TIME_TO_SEC(TIMEDIFF(today.asr_jamah, tomorrow.asr_jamah)) /60) as asr_jamah,
95	abs(TIME_TO_SEC(TIMEDIFF(today.maghrib_jamah, tomorrow.maghrib_jamah)) /60) as maghrib_jamah,
96	abs(TIME_TO_SEC(TIMEDIFF(today.isha_jamah, tomorrow.isha_jamah)) /60) as isha_jamah
97	FROM $this->dbTable today
98	INNER JOIN $this->dbTable tomorrow
99	ON today.d_date = tomorrow.d_date + INTERVAL 1 DAY
100	WHERE today.d_date = CURDATE() + INTERVAL 1 DAY;";
101
102	$result = $wpdb->get_row($sql, ARRAY_A);
103
104	if ( empty($result) ) {
105	return null;
106	}
107
108	// get jamah name that has changes more than x min
109	$jamahNamesArray = array();
110	foreach($result as $key=>$time) {
111	if ((int)$time >= (int)$xmin) {
112	$diff = (int)$time - (int)$xmin;
113	$jamahNamesArray[$key] = $diff;
114	}
115	}
116
117	$jamahNamesString = implode(",", array_keys($jamahNamesArray));
118
119	if (empty($jamahNamesString)) {
120	return null;
121	}
122
123	$sql = "SELECT " . $jamahNamesString . "
124	FROM $this->dbTable
125	WHERE d_date = CURDATE() + INTERVAL 1 DAY;";
126
127	$result = $wpdb->get_row($sql, ARRAY_A);
128
129	return $result;
130	}
131
132	/**
133	* @param int $monthNumber
134	* @return array
135	*/
136	public function getPrayerTimeForMonth($monthNumber)
137	{
138	global $wpdb;
139
140	$sql = "SELECT * FROM $this->dbTable WHERE month(d_date) = $monthNumber AND YEAR(d_date) = YEAR(CURDATE()) ORDER BY d_date ASC";
141	$result = $wpdb->get_results($sql, ARRAY_A);
142
143	return $result;
144	}
145
146	/**
147	* @return array
148	*/
149	public function getPrayerTimeForRamadan()
150	{
151	global $wpdb;
152
153	$sql = "SELECT * FROM $this->dbTable WHERE is_ramadan = 1 AND YEAR(d_date) = YEAR(CURDATE()) ORDER BY d_date ASC";
154	$result = $wpdb->get_results($sql, ARRAY_A);
155
156	return $result;
157	}
158
159	/**
160	* @param array $row
161	* @return int\|bool
162	*/
163	public function insertRow($row)
164	{
165	$this->deleteTransients();
166
167	global $wpdb;
168
169	$createIfNotUpdate = "INSERT INTO " .$this->dbTable. " VALUES (";
170
171	foreach ($row as $key => $value) {
172	$createIfNotUpdate .= "'" .$value. "',";
173	}
174	$createIfNotUpdate = rtrim($createIfNotUpdate, ', ');
175
176	$createIfNotUpdate .= " ) ON DUPLICATE KEY UPDATE ";
177	foreach ($row as $key => $value) {
178	$createIfNotUpdate .= $key. "='" .$value. "',";
179	}
180	$createIfNotUpdate = rtrim($createIfNotUpdate, ', ');
181	$createIfNotUpdate .= ';';
182
183	return $wpdb->query($createIfNotUpdate);
184	}
185
186	private function createTableIfNotExist()
187	{
188	global $wpdb;
189
190	$charset_collate = $wpdb->get_charset_collate();
191
192	$sql = "CREATE TABLE " . $this->dbTable. "(
193	d_date date NOT NULL,
194	fajr_begins time DEFAULT '00:00',
195	fajr_jamah time DEFAULT '00:00',
196	sunrise time DEFAULT '00:00',
197	zuhr_begins time DEFAULT '00:00',
198	zuhr_jamah time DEFAULT '00:00',
199	asr_mithl_1 time DEFAULT '00:00',
200	asr_mithl_2 time DEFAULT '00:00',
201	asr_jamah time DEFAULT '00:00',
202	maghrib_begins time DEFAULT '00:00',
203	maghrib_jamah time DEFAULT '00:00',
204	isha_begins time DEFAULT '00:00',
205	isha_jamah time DEFAULT '00:00',
206	is_ramadan SMALLINT DEFAULT NULL,
207	hijri_date VARCHAR(256) DEFAULT NULL,
208	PRIMARY KEY (d_date)
209	) $charset_collate;";
210
211	$wpdb->get_var("SHOW TABLES LIKE '". $this->tableName . "'");
212	if($wpdb->num_rows != 1) {
213	dbDelta( $sql );
214	}
215	}
216
217	public function updateRow($monthData)
218	{
219	$this->deleteTransients();
220
221	global $wpdb;
222
223	foreach ($monthData as $day) {
224	$wpdb->update(
225	$this->tableName,
226	array(
227	'fajr_jamah' => $day['fajr_jamah'],
228	'zuhr_jamah' => $day['zuhr_jamah'],
229	'asr_jamah' => $day['asr_jamah'],
230	'maghrib_jamah' => $day['maghrib_jamah'],
231	'isha_jamah' => $day['isha_jamah']
232	),
233	array('d_date' => $day['d_date'])
234	);
235	}
236	}
237
238	public function getRows()
239	{
240	error_log('get rows');
241
242	global $wpdb;
243	$sql = "SELECT * FROM " . $this->dbTable;
244	$result = $wpdb->get_results($sql, ARRAY_A);
245	return $result;
246	}
247
248	private function getTransient($transientName)
249	{
250	if (date_i18n( 'g:ia' ) === '12:00am') {
251	delete_transient($transientName);
252	}
253
254	return get_transient($transientName);
255	}
256
257	private function deleteTransients()
258	{
259	foreach( $this->transients as $transientName ) {
260	delete_transient($transientName);
261	}
262	}
263	}
264
265
266	function user_current_time($format="")
267	{
268	$format = $format ? $format : 'mysql';
269	$result = current_time($format);
270	if (empty($result)) {
271	$result = date( $format, time() + ( get_option( 'gmt_offset' ) * 60 ) );
272	}
273
274	return $result;
275	}
276