db.php — Daily Prayer Time 2026.05.20

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

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

Hijri 3 years ago Processors 1 month ago QuranADay 2 months ago StartTime 1 year ago design 1 month ago AdminMenu.php 2 years ago AssetsLoader.php 1 year ago CustomPluginSettings.php 4 years ago DPTAjaxHandler.php 4 years ago DPTHelper.php 1 month ago DSTemplateLoader.php 2 years ago DailyShortCode.php 1 month ago DigitalScreen.php 1 month ago HijriDate.php 3 years ago Init.php 4 years ago MonthlyShortCode.php 2 years ago MonthlyTimeTable.php 3 years ago Shortcodes.php 2 months ago Translator.php 4 years ago UpdateStyles.php 1 month ago Validator.php 3 years ago db.php 2 months ago dptWidget.php 4 years ago

db.php

446 lines

1	<?php
2
3	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
4	require_once('HijriDate.php');
5	require_once('DPTHelper.php');
6	require_once('Processors/DebugProcessor.php');
7
8
9	class DatabaseConnection
10	{
11	/** @var string */
12	private $dbTable = "";
13
14	/** @var string */
15	private $tableName = "";
16
17	/** @var array */
18	private $transients = array('prayerTimeForToday', 'fajrJamahForTomorrow', 'jamahChanges', 'fajrForTomorrow');
19
20	/** @var HijriDate */
21	private $hijriDate;
22
23	/** @var DPTHelper */
24	private $dptHelper;
25
26	/** @var DebugProcessor */
27	private $logger;
28
29	public function __construct()
30	{
31	global $wpdb;
32
33	$this->tableName = $wpdb->prefix . "timetable";
34	$this->dbTable = "`" . DB_NAME . "`.`" . $this->tableName."`";
35	$this->hijriDate = new HijriDate();
36	$this->dptHelper = new DPTHelper();
37
38	$this->logger = new DPTDebugProcessor();
39	$this->createTableIfNotExist();
40	}
41
42	/**
43	* @param int $jamahChanges
44	* @return array
45	*/
46	public function getPrayerTimeForToday($jamahChanges=null)
47	{
48	global $wpdb;
49
50	$today = user_current_time( 'Y-m-d' );
51
52	$sql = "SELECT * FROM $this->dbTable WHERE d_date = '$today' LIMIT 1";
53	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
54	$result = $wpdb->get_row($sql, ARRAY_A);
55
56	if ( empty($result) ) {
57	$sql = "SELECT * FROM $this->dbTable WHERE month (d_date) = ". date('m') ." and day(d_date)=". date('d') ." LIMIT 1";
58	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
59
60	$result = $wpdb->get_row($sql, ARRAY_A);
61	}
62	$result['jamah_changes'] = $this->getJamahChanges($jamahChanges);
63
64	$date = strtotime($today);
65	$date = strtotime("+1 day", $date);
66	$tomorrow = date('Y-m-d', $date);
67
68	if ( !empty($tomorrowPrayerTimes = $this->getPrayerTimesForTomorrow($tomorrow)) ) {
69	$result['tomorrow'] = $tomorrowPrayerTimes;
70	}
71
72	$result = $this->dptHelper->updateZuhrWithJummahTimes($result);
73
74	return $result;
75	}
76
77	/**
78	* @param int $jamahChanges
79	* @return array
80	*/
81	public function getPrayerTimesForTomorrow($tomorrow)
82	{
83	global $wpdb;
84
85	$sql = "SELECT * FROM $this->dbTable WHERE d_date = '$tomorrow' LIMIT 1";
86	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
87
88	$result = $wpdb->get_row($sql, ARRAY_A);
89
90	if (empty($result)) {
91	$tomorrow = new DateTime('tomorrow');
92	$sql = "SELECT * FROM $this->dbTable " .
93	"WHERE month(d_date) = ". $tomorrow->format('m') . " AND day(d_date) = ". $tomorrow->format('d');
94	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
95
96	$result = $wpdb->get_row($sql, ARRAY_A);
97	}
98
99	return $result;
100	}
101
102	public function getIqamahTimeForToday()
103	{
104	$result = $this->getPrayerTimeForToday();
105	return array(
106	'fajr' => $result['fajr_jamah'],
107	'sunrise' => $result['sunrise'],
108	'zuhr' => $result['zuhr_jamah'],
109	'asr' => $result['asr_jamah'],
110	'maghrib' => $result['maghrib_jamah'],
111	'isha' => $result['isha_jamah']
112	);
113	}
114
115	/**
116	* @return array
117	*/
118	public function getFajrJamahForTomorrow()
119	{
120	global $wpdb;
121
122	$sql = "SELECT fajr_jamah FROM $this->dbTable WHERE d_date = CURDATE() + INTERVAL 1 DAY;";
123	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
124
125	$row = $wpdb->get_row($sql, ARRAY_A);
126	if (isset($row['fajr_jamah'])) {
127	$result = $row['fajr_jamah'];
128	}
129
130	if (empty($result)) {
131	$tomorrow = new DateTime('tomorrow');
132	$sql = "SELECT fajr_jamah FROM $this->dbTable " .
133	"WHERE month(d_date) = ". $tomorrow->format('m') . " AND day(d_date) = ". $tomorrow->format('d');
134	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
135
136	$row = $wpdb->get_row($sql, ARRAY_A);
137	$result = $row['fajr_jamah'];
138
139	}
140
141	return $result;
142	}
143
144	/**
145	* @param int $min
146	* @return array
147	*/
148	public function getJamahChanges($min=null)
149	{
150	$xmin = get_option( 'jamah_changes' );
151	$xmin = empty($min) ? $xmin : $min;
152
153	if ( $xmin < 1) {
154	return;
155	}
156
157	global $wpdb;
158
159	$baseSql = "SELECT
160	abs(TIME_TO_SEC(TIMEDIFF(today.fajr_jamah, tomorrow.fajr_jamah)) / 60) as fajr_jamah,
161	abs(TIME_TO_SEC(TIMEDIFF(today.zuhr_jamah, tomorrow.zuhr_jamah)) /60) as zuhr_jamah,
162	abs(TIME_TO_SEC(TIMEDIFF(today.asr_jamah, tomorrow.asr_jamah)) /60) as asr_jamah,
163	abs(TIME_TO_SEC(TIMEDIFF(today.maghrib_jamah, tomorrow.maghrib_jamah)) /60) as maghrib_jamah,
164	abs(TIME_TO_SEC(TIMEDIFF(today.isha_jamah, tomorrow.isha_jamah)) /60) as isha_jamah
165	FROM $this->dbTable today
166	INNER JOIN $this->dbTable tomorrow
167	ON today.d_date = tomorrow.d_date + INTERVAL 1 DAY";
168
169	$sql = $baseSql . " WHERE today.d_date = CURDATE() + INTERVAL 1 DAY;";
170	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
171
172
173	$result = $wpdb->get_row($sql, ARRAY_A);
174	if ( empty($result) ) {
175	$sql = $baseSql . "
176	WHERE today.d_date = (select d_date from $this->dbTable
177	WHERE month(d_date) = ". date('m') ." AND day(d_date) = ". date('d') .") + INTERVAL 1 DAY;";
178	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
179
180	$result = $wpdb->get_row($sql, ARRAY_A);
181	}
182
183	if (empty($result)) {
184	return null;
185	}
186
187	// get jamah name that has changes more than x min
188	$jamahNamesArray = array();
189
190	foreach($result as $key=>$time) {
191	if ((int)$time >= (int)$xmin) {
192	$diff = (int)$time - (int)$xmin;
193	$jamahNamesArray[$key] = $diff;
194	}
195	}
196
197	$jamahNamesString = implode(",", array_keys($jamahNamesArray));
198
199	if (empty($jamahNamesString)) {
200	return null;
201	}
202
203	$sql = "SELECT " . $jamahNamesString . "
204	FROM $this->dbTable
205	WHERE d_date = CURDATE() + INTERVAL 1 DAY;";
206	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
207
208	$result = $wpdb->get_row($sql, ARRAY_A);
209
210	if (empty($result)) {
211	$sql = "SELECT " . $jamahNamesString . "
212	FROM $this->dbTable
213	WHERE d_date = (select d_date from $this->dbTable
214	WHERE month(d_date) = ". date('m') ." AND day(d_date) = ". date('d') .") + INTERVAL 1 DAY;";
215	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
216
217	$result = $wpdb->get_row($sql, ARRAY_A);
218	}
219
220	return $result;
221	}
222
223	/**
224	* @param int $monthNumber
225	* @return array
226	*/
227	public function getPrayerTimeForMonth($monthNumber, $year)
228	{
229	global $wpdb;
230
231	$sql = "SELECT * FROM $this->dbTable WHERE month(d_date) = %d AND YEAR(d_date) = %d ORDER BY d_date ASC";
232	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
233
234	$prepared = $wpdb->prepare( $sql, array( (int)$monthNumber, (int)$year ) );
235	$result = $wpdb->get_results($prepared, ARRAY_A);
236
237	if ( empty($result) ) {
238	$sql = "SELECT * FROM $this->dbTable WHERE month(d_date) = %d and year(d_date) = (select max(year(d_date)) from ". $this->dbTable .") ORDER BY d_date ASC";
239	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
240
241	$prepared = $wpdb->prepare( $sql, array( (int)$monthNumber ) );
242	$result = $wpdb->get_results($prepared, ARRAY_A);
243	}
244
245	return $result;
246	}
247
248	/**
249	* @return array
250	*/
251	public function getPrayerTimeForRamadan()
252	{
253	global $wpdb;
254
255	$sql = "SELECT * FROM $this->dbTable WHERE is_ramadan = 1 AND YEAR(d_date) = YEAR(CURDATE()) ORDER BY d_date ASC";
256	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
257
258	$result = $wpdb->get_results($sql, ARRAY_A);
259
260	if ( empty($result) ){
261	$dates = $this->getRamadanDaysFromCalendar();
262	$this->updateRamadanDays($dates);
263	return $wpdb->get_results($sql, ARRAY_A);
264
265	}
266
267	return $result;
268	}
269
270	/**
271	* @param array $row
272	* @return array
273	*/
274	public function insertRow($row)
275	{
276	$this->deleteTransients();
277
278	global $wpdb;
279
280	$createIfNotUpdate = "INSERT INTO " .$this->dbTable. " VALUES (";
281
282	foreach ($row as $key => $value) {
283	$createIfNotUpdate .= "'" .$value. "',";
284	}
285	$createIfNotUpdate = rtrim($createIfNotUpdate, ', ');
286
287	$createIfNotUpdate .= " ) ON DUPLICATE KEY UPDATE ";
288	foreach ($row as $key => $value) {
289	$createIfNotUpdate .= $key. "='" .$value. "',";
290	}
291	$createIfNotUpdate = rtrim($createIfNotUpdate, ', ');
292	$createIfNotUpdate .= ';';
293
294	$result = $wpdb->query($createIfNotUpdate);
295
296	$date = $row['d_date'] ?? 'Unknown';
297
298	return [
299	'result' => $result,
300	'date' => $date
301	];
302	}
303
304	private function createTableIfNotExist()
305	{
306	global $wpdb;
307
308	$charset_collate = $wpdb->get_charset_collate();
309
310	$sql = "CREATE TABLE IF NOT EXISTS " . $this->dbTable. "(
311	d_date date NOT NULL,
312	fajr_begins time DEFAULT '00:00',
313	fajr_jamah time DEFAULT '00:00',
314	sunrise time DEFAULT '00:00',
315	zuhr_begins time DEFAULT '00:00',
316	zuhr_jamah time DEFAULT '00:00',
317	asr_mithl_1 time DEFAULT '00:00',
318	asr_mithl_2 time DEFAULT '00:00',
319	asr_jamah time DEFAULT '00:00',
320	maghrib_begins time DEFAULT '00:00',
321	maghrib_jamah time DEFAULT '00:00',
322	isha_begins time DEFAULT '00:00',
323	isha_jamah time DEFAULT '00:00',
324	is_ramadan SMALLINT DEFAULT NULL,
325	hijri_date VARCHAR(256) DEFAULT NULL,
326	PRIMARY KEY (d_date)
327	) $charset_collate;";
328
329	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
330
331	$wpdb->get_var("SHOW TABLES LIKE '". $this->tableName . "'");
332	if($wpdb->num_rows != 1) {
333	dbDelta( $sql );
334	}
335	}
336
337	public function quickUpdateRow($monthData)
338	{
339	$this->deleteTransients();
340
341	global $wpdb;
342
343	$asrStartField = get_option('asrSelect') == 'hanafi' ? 'asr_mithl_2' : 'asr_mithl_1';
344
345	foreach ($monthData as $day) {
346	$wpdb->update(
347	$this->tableName,
348	array(
349	'fajr_begins' => $day['fajr_begins'],
350	'fajr_jamah' => $day['fajr_jamah'],
351	'sunrise' => $day['sunrise'],
352	'zuhr_begins' => $day['zuhr_begins'],
353	'zuhr_jamah' => $day['zuhr_jamah'],
354	$asrStartField => $day['asr_begins'],
355	'asr_jamah' => $day['asr_jamah'],
356	'maghrib_begins' => $day['maghrib_begins'],
357	'maghrib_jamah' => $day['maghrib_jamah'],
358	'isha_begins' => $day['isha_begins'],
359	'isha_jamah' => $day['isha_jamah']
360	),
361	array('d_date' => $day['d_date'])
362	);
363	}
364	}
365
366	public function getYers()
367	{
368	global $wpdb;
369	$sql = "SELECT DISTINCT year(d_date) as year FROM ". $this->dbTable;
370	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
371
372	return $wpdb->get_results($sql, ARRAY_A);
373	}
374
375	public function getRows()
376	{
377	global $wpdb;
378	$sql = "SELECT * FROM ". $this->dbTable ." WHERE year(d_date) = (select max(year(d_date)) from ". $this->dbTable .")";
379	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
380
381	if (get_option('debugActivated')) {
382
383	}
384	return $wpdb->get_results($sql, ARRAY_A);
385	}
386
387	public function resetOtherYears()
388	{
389	global $wpdb;
390
391	$sql = "DELETE FROM " . $this->dbTable . 'WHERE YEAR(d_date) !=' . date('Y');
392	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
393
394	return $wpdb->query($sql);
395	}
396
397	private function deleteTransients()
398	{
399	foreach( $this->transients as $transientName ) {
400	delete_transient($transientName);
401	}
402	}
403
404	private function getRamadanDaysFromCalendar()
405	{
406	$data = [];
407	$year = (int)date('Y');
408	$daysOfMonths = [];
409	foreach(range(1, 12) as $month){
410	$daysOfMonths[] = (int)date('d', strtotime('Last day of ' . date('F', strtotime($year . '-' . $month . '-01')) . $year)) . PHP_EOL;
411	}
412	foreach($daysOfMonths as $monthId => $daysOfMonth) {
413	for($day = 1; $day <= $daysOfMonth; $day++) {
414	echo $monthId+1;
415	if ( strpos($this->hijriDate->getDate($day, $monthId+1, $year, true), 'Ramadan') ) {
416	$data [] = $year . '-' . trim($monthId+1) . '-' . trim($day);
417	}
418	}
419	}
420
421	return $data;
422	}
423
424	private function updateRamadanDays(array $dates)
425	{
426	global $wpdb;
427
428	$datesString = "'" . implode("','", $dates) . "'";
429	$sql = "UPDATE " . $this->dbTable . 'SET is_ramadan=1 WHERE d_date IN (' . $datesString . ')';
430	$this->logger->log(__FILE__ . ' - ' . __LINE__ . ': ' . $sql);
431
432	return $wpdb->query($sql);
433	}
434	}
435
436	function user_current_time($format="")
437	{
438	$format = $format ? $format : 'mysql';
439	$result = current_time($format);
440	if (empty($result)) {
441	$result = date( $format, time() + ( get_option( 'gmt_offset' ) * 60 ) );
442	}
443
444	return $result;
445	}
446