File: /var/www/ilya/data/www/povoleni.com/modules/stats/index.inc
<?
/*
Среднее, минимальное и максимальное время от подписки до получения решения, по месяцам
SELECT DATE_FORMAT(`updated`,'%Y-%m') AS `period`, AVG(DATEDIFF(`updated`, `added`)) AS `days` FROM `entries` WHERE `status`=3 AND DATEDIFF(`updated`, `added`)>3 GROUP BY DATE_FORMAT(`updated`,'%Y-%m')
SELECT DATE_FORMAT(`updated`,'%Y-%m') AS `period`, MIN(DATEDIFF(`updated`, `added`)) AS `days` FROM `entries` WHERE `status`=3 AND DATEDIFF(`updated`, `added`)>3 GROUP BY DATE_FORMAT(`updated`,'%Y-%m')
SELECT DATE_FORMAT(`updated`,'%Y-%m') AS `period`, MAX(DATEDIFF(`updated`, `added`)) AS `days` FROM `entries` WHERE `status`=3 AND DATEDIFF(`updated`, `added`)>3 GROUP BY DATE_FORMAT(`updated`,'%Y-%m')
------------------------------------------------------
Кол-во добавленных записей по месяцам
SELECT DATE_FORMAT(`added`,'%Y-%m') AS `period`, COUNT(`id`) FROM `entries` GROUP BY DATE_FORMAT(`added`,'%Y-%m')
------------------------------------------------------
Просто тесты для просмотра записей
SELECT *, DATEDIFF(`updated`, `added`) AS `days` FROM `entries` WHERE DATE_FORMAT(`updated`,'%Y-%m')='2012-05' and status=3
*/
$temp_html = "";
function check_email($email){
$re = '/^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/i';
return preg_match($re, $email);
}
function get_verify($length = 10)
{
// start with a blank password
$password = "";
// define possible characters - any character in this string can be
// picked for use in the password, so if you want to put vowels back in
// or add special characters such as exclamation marks, this is where
// you should do it
$possible = "2346789bcdfghjkmnpqrtvwxyzBCDFGHJKLMNPQRTVWXYZ";
// we refer to the length of $possible a few times, so let's grab it now
$maxlength = strlen($possible);
// check for length overflow and truncate if necessary
if ($length > $maxlength) {
$length = $maxlength;
}
// set up a counter for how many characters are in the password so far
$i = 0;
// add random characters to $password until $length is reached
while ($i < $length) {
// pick a random character from the possible ones
$char = substr($possible, mt_rand(0, $maxlength-1), 1);
// have we already used this character in $password?
if (!strstr($password, $char)) {
// no, so it's OK to add it onto the end of whatever we've already got...
$password .= $char;
// ... and increase the counter by one
$i++;
}
}
// done!
$result = query("SELECT * FROM `entries` WHERE `verify`='".$password."';");
if(mysql_num_rows($result)>0)
return get_verify();
else
return $password;
}
function get_mail($data) {
global $global_vars;
$mail = array();
$mail['subject'] = $global_vars['translation'][$global_vars['language']]['index_email_subject']." ".get_application_number($data);
$mail['subject'] = '=?UTF-8?B?'.base64_encode($mail['subject']).'?=';
$mail['text'] = $global_vars['translation'][$global_vars['language']]['index_email_text_1']." ".get_application_number($data)." ".$global_vars['translation'][$global_vars['language']]['index_email_text_2']." ".$data['email'].". ".$global_vars['translation'][$global_vars['language']]['index_email_text_3']." www.povoleni.ru/verify/?code=".$data['verify']."\n\n".$global_vars['translation'][$global_vars['language']]['index_email_text_4'];
$mail['headers'] = "From: ".$global_vars['translation'][$global_vars['language']]['site_name']." <no-reply@povoleni.ru>\r\nReply-To: info@povoleni.ru\r\nMIME-Version: 1.0\r\nContent-type: text/plain; charset=UTF-8\r\n";
return $mail;
}
if(isset($_POST['code'])) {
$year = $_POST['year'];
$type = $_POST['type'];
$code = $_POST['code'];
$email = $_POST['email'];
$error = "";
if(!check_email($email))
$error = $index_error_email;
if(!preg_match("/^[0-9-]+$/", $_POST['code']))
$error = $index_error_code;
if($error=='') {
$result = query("SELECT * FROM `entries` WHERE `email`='".$email."' AND `code`='".$code."' AND `type`='".$type."' AND `year`='".$year."' AND `status`<3;");
if(mysql_num_rows($result)>0)
$error = $index_error_duplicate;
}
if($error=='') {
$data = array();
$data['email'] = $_POST['email'];
$data['code'] = $_POST['code'];
$data['type'] = $_POST['type'];
$data['year'] = $_POST['year'];
$data['verify'] = get_verify();
$mail = get_mail($data);
$mailer = new PHPMailer(); // create a new object
$mailer->CharSet = "UTF-8";
$mailer->IsSMTP(); // enable SMTP
$mailer->SMTPDebug = false; // debugging: 1 = errors and messages, 2 = messages only
$mailer->SMTPAuth = true; // authentication enabled
$mailer->SMTPSecure = 'tls'; // secure transfer enabled REQUIRED for Gmail
$mailer->Host = "smtp.gmail.com";
$mailer->Port = 587; // or 587
$mailer->IsHTML(false);
$mailer->Username = SITE_EMAIL_USERNAME;
$mailer->Password = SITE_EMAIL_PASSWORD;
$mailer->SetFrom(SITE_EMAIL,$global_vars['translation'][$global_vars['language']]['site_name']);
$mailer->AddReplyTo(SITE_EMAIL_REPLYTO,$global_vars['translation'][$global_vars['language']]['site_name']);
$mailer->Subject = $mail['subject'];
$mailer->Body = $mail['text'];
$mailer->AddAddress($data['email']);
if($mailer->Send())
if(query("INSERT INTO `entries` (`id`,`email`,`code`,`type`,`year`,`status`,`verify`,`added`,`updated`,`language`) VALUES ( NULL , '".$data['email']."', '".$data['code']."', '".$data['type']."', '".$data['year']."', '1', '".$data['verify']."', NOW(), '', '".$language."');")) {
$msg = $index_msg_ok_1." ".$_POST['email'].$index_msg_ok_2;
$email = "";
$code = "";
$type = "";
$year = "";
}
else
$error = $index_error_db;
else
$error = $index_error_email_server;
}
}
else {
$year = date("Y");
$type = "DP";
}
$types = array("DP","DV","PP","TP","ZM");
$skin_html_type = "";
foreach($types as $t) {
if($t==$type)
$skin_html_type .= "<option selected>".$t."</option>\r";
else
$skin_html_type .= "<option>".$t."</option>\r";
}
$years = array(date("Y"),date("Y")-1,date("Y")-2);
$skin_html_year = "";
foreach($years as $y) {
if($y==$year)
$skin_html_year .= "<option selected>".$y."</option>\r";
else
$skin_html_year .= "<option>".$y."</option>\r";
}
if(isset($msg) && ($msg!=''))
$temp_html .= '<div class="alert alert-success">'.$msg.'</div>';
if(isset($error) && ($error!=''))
$temp_html .= '<div class="alert alert-error">'.$error.'</div>';
if(!isset($email))
$email = "";
if(!isset($code))
$code = "";
$temp_html .= preg_replace("/{%(\w+)%}/ee", "$\\1",_html("index.htm",0));
_html_design($temp_html);
?>