/** * Sweep user day table * * @return void **/ public function userDaySweeper() { $begin = microtime(true); $sql = "drop table if exists %USER_DAY_TMP%;\r\ncreate table %USER_DAY_TMP%\r\n(\r\n user_id int,\r\n dt date,\r\n result_id int,\r\n PRIMARY KEY (user_id, dt)\r\n);\r\n\r\ninsert into %USER_DAY_TMP% (user_id, dt, result_id) select user_id, date_entered, count(*) from %USER_PURCHASE% where status_id = 3 and is_deleted = 0 and date_entered >= '%DAY%' group by user_id, date_entered;\r\n\r\nupdate %USER_DAY% p left JOIN %USER_DAY_TMP% d on p.user_id = d.user_id and p.event_date = d.dt set p.result_id = ifnull(d.result_id, 0) where p.result_id > -1 and d.user_id is not null;\r\n\r\nupdate %USER_DAY% p, %USER_DAY_TMP% d set p.result_id = d.result_id where p.result_id < 0 and p.user_id = d.user_id and p.event_date = d.dt and d.result_id > 0;\r\n\r\ndrop table if exists %USER_DAY_TMP%;\r\n\r\ndelete from %USER_DAY% where event_date < '2006-04-01';"; $sql = str_replace('%USER_DAY%', $this->_mapper->table('day'), $sql); $sql = str_replace('%USER_DAY_TMP%', $this->_mapper->table('day') . '_fix_tmp', $sql); $sql = str_replace('%USER_PURCHASE%', M('Purchase')->table('purchase'), $sql); $day = M('Date')->dbDate(M('Date')->add(M('Date')->dbDate(), '-' . C('diary.min-date-shift'))); $sql = str_replace('%DAY%', $day, $sql); M('Db')->execBatch($sql); $end = microtime(true); M('Trace')->trace('user_day_sweeper', "User day sweeper execution time: " . round($end - $begin, 2) . " sec"); // так как sweeper запускается ночью, то отправим емейл с уведомлением (в логах трудно отследить, запускался sweeper ночью или нет). M("MailSend")->sendAdminMail(array("subject" => "User day sweeper", "body" => 'table: ' . $this->_mapper->table('day') . "\n" . 'execution time: ' . round($end - $begin, 2) . ' sec')) or die("Cannot send mail!"); }