db.php — Daily Prayer Time 2020.07.03

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

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

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

db.php

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