Skip to content

Instantly share code, notes, and snippets.

@azhurb

azhurb/epg.patch

Created November 21, 2012 08:52
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save azhurb/4123877 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save azhurb/4123877 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
epg.patch
Index: D:/projects/stalker_portal/stalker_portal/server/lib/mysql.class.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/lib/mysql.class.php (revision 3255)
+++ D:/projects/stalker_portal/stalker_portal/server/lib/mysql.class.php (revision 3256)
@@ -445,8 +445,7 @@
}
public function insert($table, $keys){
-
-
+
$fields = array();
$values = array();
@@ -466,7 +465,44 @@
}
$value_str = implode(', ', $values);
-
+
+ $max_len = $this->get_max_allowed_packet();
+
+ if (mb_strlen($value_str, '8bit') + 1000 > $max_len){
+
+ $value_str = '';
+
+ $multiple_values_str = '';
+
+ $sql = 'INSERT INTO '.$table.' ('.implode(', ', $fields).') values ';
+
+ $total_result = true;
+
+ for ($i = 0; $i < count($values); $i++){
+
+ if (isset ($values[$i+1]) && mb_strlen($sql.$multiple_values_str.', '.$values[$i+1], '8bit') >= $max_len){
+
+ //echo $sql.$multiple_values_str."\n";
+
+ $result = $this->query($sql.$multiple_values_str);
+ $total_result = $total_result && $result;
+
+ $multiple_values_str = '';
+
+ }
+
+ if ($multiple_values_str != ''){
+ $multiple_values_str .= ', ';
+ }
+
+ $multiple_values_str .= $values[$i];
+ }
+
+ $this->reset_write();
+
+ return $total_result;
+ }
+
}else{
foreach ($keys as $field => $value){
@@ -475,15 +511,12 @@
}
$value_str = '('.implode(', ', $values).')';
-
- /*
- if (mb_strlen($value_str, '8bit') + 1000 > $this->get_max_allowed_packet()){
-
- }*/
}
$sql = 'INSERT INTO '.$table.' ('.implode(', ', $fields).') value '.$value_str;
-
+
+ //echo $sql;
+
$result = $this->query($sql);
$this->reset_write();
Index: D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php (revision 3255)
+++ D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php (revision 3256)
@@ -19,6 +19,8 @@
//private $correction_time = 0; // minutes
private $settings = array();
private $real_ids = array();
+ private $corrections = null;
+ private $channels_updated = array();
public function __construct(){
$this->db = Mysql::getInstance();
@@ -103,6 +105,10 @@
$insert_data = array();
$data_arr = array();
+ $start_time = microtime(1);
+
+ $total_need_to_delete = array();
+
foreach ($xml->programme as $programme){
$itv_id_arr = @$ids_arr[$setting['id_prefix'].strval($programme->attributes()->channel)];
@@ -128,10 +134,21 @@
$title = strval($programme->title);
foreach ($itv_id_arr as $itv_id){
+ $correction_time = $this->getCorrectionTimeByChannelId($itv_id);
+ $start_ts = $start + $correction_time * 60;
+ $need_to_delete = $this->getProgramIdsForClean($start_ts, $itv_id);
+ if ($need_to_delete){
+ $total_need_to_delete = array_merge($total_need_to_delete, $need_to_delete);
+ }
+ }
+
+ foreach ($itv_id_arr as $itv_id){
+
//$this->cleanEpgByDate($start_ts, $itv_id);
- $correction_time = (int) Mysql::getInstance()->from('itv')->where(array('id' => $itv_id))->get()->first('correct_time');
+ //$correction_time = (int) Mysql::getInstance()->from('itv')->where(array('id' => $itv_id))->get()->first('correct_time');
+ $correction_time = $this->getCorrectionTimeByChannelId($itv_id);
$start_ts = $start + $correction_time * 60;
$mysql_start = date("Y-m-d H:i:s", $start_ts);
@@ -149,22 +166,38 @@
$this->real_ids[$real_id] = true;
- $this->cleanEpgByDate($start_ts, $itv_id);
+ //$this->cleanEpgByDate($start_ts, $itv_id);
- $data_arr[$itv_id][] = array(
+ /*$data_arr[$itv_id][] = array(
'ch_id' => $itv_id,
'time' => $mysql_start,
'time_to' => $mysql_stop,
'duration' => $duration,
'real_id' => $real_id,
'name' => $title
+ );*/
+ $data_arr[] = array(
+ 'ch_id' => $itv_id,
+ 'time' => $mysql_start,
+ 'time_to' => $mysql_stop,
+ 'duration' => $duration,
+ 'real_id' => $real_id,
+ 'name' => $title
);
+ $this->channels_updated[$itv_id] = 1;
}
}
}
- $err = 0;
+ $xml = null;
+
+ if (!empty($total_need_to_delete)){
+ Mysql::getInstance()->query('delete from epg where id in ('.implode(', ', $total_need_to_delete).')');
+ Mysql::getInstance()->query('OPTIMIZE TABLE epg');
+ }
+
+ /*$err = 0;
$done = 0;
$xml_ids_done = '';
$xml_ids_err = '';
@@ -183,8 +216,10 @@
}
$total++;
- }
+ }*/
+ $result = $this->db->insert('epg', $data_arr);
+
$setting['etag'] = $etag;
$this->setSettings($setting);
@@ -192,13 +227,32 @@
$event->setUserListByMac('all');
$event->sendUpdateEpg();
- $str = sprintf(_("Updated %d channels from %d, %d errors"), $done, $total, $err)." \n";
- $str .= "<b>"._("Errors").": </b>\n".($err? $xml_ids_err : $err)."\n";
- $str .= "<b>"._("Successful").": </b>\n".$xml_ids_done."\n";
+ //$str = sprintf(_("Updated %d channels from %d, %d errors"), $done, $total, $err)." \n";
+ $str = "\n";
+ if (!$result){
+ $str .= "<b>"._("Errors").": </b> 1\n";
+ }
+ $str .= "<b>"._("Successful").": </b>".count($this->channels_updated)."\n";
+ $str .= "<b>"._("Queries").": </b>".Mysql::get_num_queries()."\n";
+ $str .= "<b>"._("Exec time").": </b>".round(microtime(1) - $start_time, 2).'s';
return $str;
}
+ private function getCorrectionTimeByChannelId($ch_id){
+
+ if ($this->corrections === null){
+ $this->corrections = array();
+ $channels = Mysql::getInstance()->from('itv')->get()->all();
+
+ foreach ($channels as $channel){
+ $this->corrections[$channel['id']] = $channel['correct_time'];
+ }
+ }
+
+ return empty($this->corrections[$ch_id]) ? 0 : (int) $this->corrections[$ch_id];
+ }
+
/**
* Return all EPG setting records.
*
@@ -263,7 +317,7 @@
* @param int $itv_id
* @return MysqlResult
*/
- private function cleanEpgByDate($date, $itv_id){
+ private function getProgramIdsForClean($date, $itv_id){
$real_from = date("Y-m-d H:i:s", $date);
@@ -284,13 +338,30 @@
if (!array_key_exists($date, $this->cleaned_epg[$itv_id])){
$this->cleaned_epg[$itv_id] = array($date => 1);
- $this->db->delete('epg',
+ $need_to_delete = Mysql::getInstance()
+ ->from('epg')
+ ->where(array(
+ 'ch_id' => $itv_id,
+ 'time>=' => $real_from,
+ 'time<' => $to
+ ))
+ ->get()
+ ->all('id');
+
+ return $need_to_delete;
+
+ /*$this->db->delete('epg',
array(
'ch_id' => $itv_id,
'time>=' => $real_from,
'time<' => $to
- ));
+ ));*/
+ /*if ($need_to_delete){
+ Mysql::getInstance()->query('delete from epg where id in ('.implode(', ', $need_to_delete).')');
+ }*/
}
+
+ return null;
}
/**
Index: D:/projects/stalker_portal/stalker_portal/server/administrator/events.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/administrator/events.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/administrator/events.php (revision 3263)
@@ -35,10 +35,15 @@
$event = new SysEvent();
if (@$_POST['user_list_type'] == 'to_all'){
- $event->setUserListByMac('all');
+
+ if ($_POST['event'] == 'send_msg'){
+ $event->setUserListByMac('all');
+ $user_list = Middleware::getOnlineUsersId();
+ }else{
+ $event->setUserListByMac('online');
+ $user_list = Middleware::getAllUsersId();
+ }
- $user_list = Middleware::getAllUsersId();
-
}elseif (@$_POST['user_list_type'] == 'to_single'){
$event->setUserListByMac(@$_POST['mac']);
Index: D:/projects/stalker_portal/stalker_portal/server/administrator/userlog.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/administrator/userlog.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/administrator/userlog.php (revision 3263)
@@ -405,7 +405,7 @@
echo "<center><table class='list' cellpadding='3' cellspacing='0' width='620'>\n";
echo "<tr>";
echo "<td class='list'><b>"._('Time')."</b></td>\n";
-echo "<td class='list'><b>"._('Action')."</b></td>\n";
+echo "<td class='list'><b>"._('Stb action')."</b></td>\n";
echo "<td class='list'><b>"._('Parameter')."</b></td>\n";
echo "</tr>\n";
while(@$rs->next()){
Index: D:/projects/stalker_portal/stalker_portal/server/tasks/cleanup_db.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/tasks/cleanup_db.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/tasks/cleanup_db.php (revision 3263)
@@ -6,4 +6,5 @@
Mysql::getInstance()->query('OPTIMIZE TABLE `user_log`');
Mysql::getInstance()->query('DELETE FROM `events` WHERE `eventtime`<now()');
+Mysql::getInstance()->query('OPTIMIZE TABLE `events`');
Index: D:/projects/stalker_portal/stalker_portal/server/lib/middleware.class.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/lib/middleware.class.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/lib/middleware.class.php (revision 3263)
@@ -19,6 +19,21 @@
//return $db->executeQuery('select * from users')->getValuesByName('id');
return $db->get('users')->all('id');
}
+
+ /**
+ * Return online boxes users id's
+ *
+ * @return array users id's
+ */
+ public static function getOnlineUsersId(){
+ return Mysql::getInstance()
+ ->from('users')
+ ->where(array(
+ 'UNIX_TIMESTAMP(keep_alive)>' => time() - Config::get('watchdog_timeout')*2
+ ))
+ ->get()
+ ->all('id');
+ }
/**
* Return users ids by mac
Index: D:/projects/stalker_portal/stalker_portal/server/lib/event.class.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/lib/event.class.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/lib/event.class.php (revision 3263)
@@ -36,6 +36,8 @@
if (is_string($list) || is_int($list)){
if ($list == 'all'){
$this->param['user_list'] = Middleware::getAllUsersId();
+ }else if ($list == 'online'){
+ $this->param['user_list'] = Middleware::getOnlineUsersId();
}else{
$this->param['user_list'] = array(Middleware::getUidByMac($list));
}
Index: D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php
===================================================================
--- D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php (revision 3262)
+++ D:/projects/stalker_portal/stalker_portal/server/lib/epg.class.php (revision 3263)
@@ -45,6 +45,10 @@
$result .= "\n";
}
+ $event = new SysEvent();
+ $event->setUserListByMac('online');
+ $event->sendUpdateEpg();
+
return $result;
}
@@ -223,10 +227,6 @@
$setting['etag'] = $etag;
$this->setSettings($setting);
- $event = new SysEvent();
- $event->setUserListByMac('all');
- $event->sendUpdateEpg();
-
//$str = sprintf(_("Updated %d channels from %d, %d errors"), $done, $total, $err)." \n";
$str = "\n";
if (!$result){
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment