public static function delete_role($id) { $query = "\n SELECT\n r.id AS role_id,\n COUNT(u.id) AS user_count\n FROM roles AS r\n LEFT JOIN users AS u ON (u.role_id = r.id)\n WHERE (r.id = {$id})\n GROUP BY r.id"; $roles = new Roles(); $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); $uc = $res->toArray(); if (!$uc) { return -1; } if ($uc[0]['user_count'] > 0) { return $uc[0]['user_count']; } $query = "\n DELETE FROM role_right AS rl_rt\n WHERE rl_rt.role_id IN (\n SELECT rls.id\n FROM roles AS rls\n WHERE (rls.id = " . $uc[0]['role_id'] . ")\n ); "; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); $query = "DELETE FROM roles WHERE (id = " . $uc[0]['role_id'] . ");"; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); return $uc[0]['user_count']; }
public function sendAction() { $today_timestamp = strtotime('today'); $yesterday_timestamp = strtotime("yesterday"); $yesterday = date('d.m.Y', $yesterday_timestamp); $messages = []; $roles = $this->roles; // Specifics for each role foreach ($roles as $role) { switch ($role) { case 'seller': $status = 3; $role_title = "Менеджер размещений"; $role_lang = false; $role_line = "Всего выполнено за день размещений:"; $role_subject = "Отчет менеджера размещений за {$yesterday}"; break; case 'translator': $status = 3; $role_title = "Переводчик"; $role_lang = true; $role_line = "Всего выполнено за день новых товаров:"; $role_subject = "Отчет переводчика за {$yesterday}"; break; case 'moderatorTranslating': $status = 5; $role_title = "Модератор перевода"; $role_lang = true; $role_line = "Всего отмодерировано за день товаров:"; $role_subject = "Отчет модератора перевода за {$yesterday}"; break; } // Find all users' emails for a role in mail list $table = new PMailList(); $sql = "SELECT DISTINCT(ac.email) FROM p_mail_list AS ml\n LEFT JOIN p_roles AS r\n ON ml.role_id = r.id\n JOIN p_acc_roles AS accr\n LEFT JOIN p_accounts AS ac\n ON accr.user_id = ac.id\n WHERE r.alias = '{$role}' AND accr.user_id = ml.user_id;"; $emails_mail_list = new Resultset(null, $table, $table->getReadConnection()->query($sql)); // Find all users with a specific role $table = new Roles(); $sql = "SELECT ac.id, ac.name, ac.email, ac.prefs FROM p_roles AS r\n LEFT JOIN p_acc_roles AS accr\n ON r.id = accr.role_id\n LEFT JOIN p_accounts AS ac\n ON accr.user_id = ac.id\n WHERE r.alias = '{$role}' AND ac.name IS NOT NULL\n ORDER BY ac.name;"; $all_users = new Resultset(null, $table, $table->getReadConnection()->query($sql)); // List of emails for mail list $emails_batch = []; foreach ($emails_mail_list as $email) { if ($email->email != null) { $emails_batch[] = $email->email; } } $emails_batch = join(', ', $emails_batch); // Find users stats if ($role == 'seller') { $users = MPlacement::count(["created BETWEEN {$yesterday_timestamp} AND {$today_timestamp}", 'group' => 'user_id'])->toArray(); } else { $users = PTasks::count(["tstart BETWEEN {$yesterday_timestamp} AND {$today_timestamp}\n AND tstatus = {$status}\n AND trole = '{$role}'", 'group' => 'tassignee'])->toArray(); } // Total count for a day $total_count = 0; foreach ($users as $user) { $total_count += $user['rowcount']; } $htmlbody_batch = []; $api_stats = []; foreach ($all_users as $one_user) { if (!empty($one_user->name) && !empty($one_user->email)) { foreach ($users as $user) { // The number of user's contributions if (isset($user['user_id'])) { // It is a seller $count[$user['user_id']] = in_array($one_user->id, $user) ? $user['rowcount'] : 0; } else { $count[$user['tassignee']] = in_array($one_user->id, $user) ? $user['rowcount'] : 0; } } $user_count = isset($count[$one_user->id]) ? (int) $count[$one_user->id] : 0; // Check if it is an API $is_api = preg_match('/^API/', $one_user->name); $htmlbody_one = null; $htmlbody_one_table = null; // Check if a user has a language assigned $lang = $role_lang ? " ({$one_user->prefs})" : ""; if (!$is_api) { $htmlbody_one = "{$role_title} {$one_user->name}{$lang}. {$role_line} <strong>{$user_count}</strong><br>"; $htmlbody_one_table = "<tr><td>{$one_user->name}{$lang}</td><td width='50px' align='right'>{$user_count}</td></tr>"; } elseif ($is_api && $user_count > 0) { $api_stats[] = "<tr><td>{$one_user->name}</td><td width='50px' align='right'>{$user_count}</td></tr>"; } $textbody_one = strip_tags($htmlbody_one); // Send an email to a user only if he/she made a contribution the previous day if ($user_count > 0 && !$is_api) { // Compose an email for every user $msg_one = ['To' => $one_user->email, 'From' => POSTMARK_FROM, 'Subject' => "{$role_subject}", 'TextBody' => $textbody_one, 'HtmlBody' => "<html><body>{$htmlbody_one}</body></html>"]; $messages[] = $msg_one; } $htmlbody_batch[] = $htmlbody_one_table; } } $api_stats = join('', $api_stats); $htmlbody_batch = join('', $htmlbody_batch); if ($role == 'seller') { $htmlbody_batch .= $api_stats; } $htmlbody_batch .= "<tr><td> </td></tr><tr><td><strong>Всего за день:</strong></td><td width='50px' align='right'><strong>{$total_count}</strong></td></tr>"; $htmlbody_batch = "<table width='330px'>{$htmlbody_batch}</table>"; $textbody_batch = strip_tags($htmlbody_batch, '<br>'); $textbody_batch = str_replace("<br>", "\n", $textbody_batch); // $this->view->zzz = "<table width='400px'>$htmlbody_batch</table>"; // Compose an email for mail list if (!empty($emails_batch)) { $msg_batch = ['To' => $emails_batch, 'From' => POSTMARK_FROM, 'Subject' => "{$role_subject}", 'TextBody' => $textbody_batch, 'HtmlBody' => "<html><body>{$htmlbody_batch}</body></html>"]; $messages[] = $msg_batch; } } $client = new PostmarkClient(POSTMARK_API); // var_dump($messages); print_r($messages); // $send = $client->sendEmailBatch($messages); }