· 8 years ago · Mar 14, 2018, 04:46 PM
1<?php
2ini_set('error_reporting', E_ALL & ~E_NOTICE);
3class GetMail{
4 function __construct(){
5 /* -- CONFIG -- */
6 //endereco do servidor de banco de dados
7 $this->db_host = 'localhost';
8 //usuario do banco de dados
9 $this->db_user = 'root';
10 //senha do banco de dados
11 $this->db_password = '2323';
12 //nome do banco de dados
13 $this->database = 'Audit';
14
15 //senha do arquivo zip
16 $this->zip_password = '1';
17 //email do gmail
18 $this->username = '**********@gmail.com';
19 //senha do gmail
20 $this->password = '**********';
21 //titulo do e-mail que ira conter o link
22 $this->email_title = "Player Audit Account History Report";
23 /* -- FIM CONFIG -- */
24
25
26 //tabela que ira conter os ids dos emails
27 $this->email_id = 'email_id';
28
29 //tabela onde serao salvos os dados
30 $this->audit = "audit";
31
32
33 $this->hostname = '{imap.gmail.com:993/imap/ssl/novalidate-cert}INBOX';
34 $this->emails = null;
35 $this->inbox = null;
36 $this->conn = null;
37
38 }
39
40 //descompacta com a senha
41 function unzip($file_name){
42 $app7z=realpath(dirname(__FILE__)."/bin_e/7z");
43 $strCommandLine = $app7z." e ".$file_name." -aoa -p".$this->zip_password;
44 system($strCommandLine);
45 array_map('unlink', glob("*.zip"));
46 }
47
48
49 //conecta com o banco de dados
50 function connect(){
51 $retorno = 0;
52 $conn = mysql_connect($this->db_host, $this->db_user, $this->db_password, $this->database);
53
54 if ($conn->connect_error) {
55 echo "=====================================\n";
56 echo "Connection failed:\n" . $conn->connect_error."\n";
57 echo "Erro ao conectar com o banco de dados.\n";
58 }else{
59 $this->conn=$conn;
60 $retorno = 1;
61 }
62 return $retorno;
63 }
64
65 //checa banco de dados
66 function check_db(){
67 //verifica se ja foi cadastrado o e-mail
68 $conectado = $this->connect();
69 if($conectado == 1){
70 $total="";
71 $sql = "SHOW TABLES LIKE '".$this->email_id."'";
72 if($result = $this->conn->query($sql)){
73 if($result->num_rows != 1) {
74 $conectado = 0;
75 echo "=====================================\n";
76 echo "Tabela ".$this->email_id." nao encontrada\n";
77 }
78 }else{
79 var_dump($this->conn->error);
80 }
81
82
83 $sql = "SHOW TABLES LIKE '".$this->audit."'";
84 if($result = $this->conn->query($sql)){
85 if($result->num_rows != 1) {
86 $conectado = 0;
87 echo "=====================================\n";
88 echo "Tabela ".$this->audit." nao encontrada\n";
89 }
90 }else{
91 var_dump($this->conn->error);
92 }
93 $this->descconnect();
94 }else{
95 $conectado = 0;
96 }
97
98 if($conectado == 1){
99 if($this->get_inbox() == 0){
100 $conectado = 0;
101 echo ".\n=====================================\n";
102 echo "Erro ao conectar com sua conta do Gmail\n";
103 }
104
105 }
106
107
108 return $conectado;
109
110 }
111
112 //desconecta com o banco de dados
113 function descconnect(){
114 mysqli_close($this->conn);
115 }
116
117
118 //salva um email id
119 function insert_id($id){
120 $this->connect();
121 $sql = 'INSERT INTO email_id (message_id) VALUES (?)';
122 if($query = $this->conn->prepare($sql)){
123 $query->bind_param('s', $id);
124 $query->execute();
125 }else{
126 var_dump($this->conn->error);
127 }
128 $this->descconnect();
129 }
130
131 //verifica se ja foi cadastrado o e-mail
132 function check_id($id){
133 $this->connect();
134 $total="";
135 $sql = 'select count(*) as total from email_id where message_id = ?';
136 if($query = $this->conn->prepare($sql)){
137 $query->bind_param('s', $id);
138 $query->execute();
139 $query->bind_result($total);
140 $query->fetch();
141 }else{
142 var_dump($this->conn->error);
143 }
144 $this->descconnect();
145 return ($total>0) * 1;
146 }
147
148
149
150 //conecta na caixa de e-mail
151 function get_inbox(){
152 $retorno = 1;
153 $this->inbox = imap_open($this->hostname,$this->username,$this->password);
154 if(imap_last_error()){
155 $retorno = 0;
156 echo imap_last_error();
157 }
158 return $retorno;
159
160 }
161 //desloga da caixa de e-mail
162 function close(){
163 imap_close($this->inbox);
164 }
165
166
167 //obtem a lista dos e-mails das ultimas 72h
168 function get_emails(){
169 $this->emails=[];
170 for($i=0;$i<4;$i++){
171 //$date = date("j F Y");
172 $date=date('j F Y', strtotime('-'.$i.' days'));
173 $this->get_inbox();
174 $emails_aux = imap_search($this->inbox,"ON \"{$date}\"");
175 $this->emails = array_merge($this->emails, $emails_aux);
176 }
177 rsort($this->emails);
178 }
179
180
181 //le e-mail por e-mail procurando pelo titulo
182 //baixa e extrai o anexo
183 function email_loop(){
184 foreach($this->emails as $email_number) {
185 $overview = imap_fetch_overview($this->inbox,$email_number,0);
186 if($this->email_title == $overview[0]->subject){
187 $message = imap_fetchbody($this->inbox,$email_number,2);
188 if(!$this->check_id($overview[0]->message_id)){
189 $zip_url_array = explode("webservices",str_replace("\n","",str_replace("=","",$message)));
190 $zip_url = "http://reports.rationalwebservices".explode("zip",$zip_url_array[1])[0]."zip";
191 file_put_contents("Tmpfile.zip", fopen($zip_url, 'r'));
192 $this->unzip("Tmpfile.zip");
193 if($this->list_csv()){
194 $this->insert_id($overview[0]->message_id);
195 }
196 }
197 }
198 }
199 }
200
201
202 //busca os csvs da pasta e importa
203 function list_csv(){
204 $files = glob("*.csv");
205 $retorno = 0;
206 foreach($files as $file) {
207 if (($handle = fopen($file, "r")) !== FALSE) {
208 $n=0;
209 $titulo ="";
210 $sql ="";
211 while (($data = fgetcsv($handle, 4096, ",")) !== FALSE) {
212 if($n==0){
213 $titulo = explode("'",implode("\t", $data)."''''")[1];
214 }
215 if($n>2){
216 $d = DateTime::createFromFormat('Y/m/d h:i A', $data[0]);
217 $data[0]= $d->format('Y-m-d H:i');
218 $sql.=implode("','", $data);
219 }
220 $n++;
221 }
222 fclose($handle);
223 } else {
224 echo "Could not open file: " . $file;
225 }
226 $sql = "insert into audit values('".$titulo."','".$sql."');";
227 $this->connect();
228 if (!$this->conn->query($sql) === TRUE) {
229 echo "Error: " . $sql . "<br>" . $this->conn->error;
230 }else{
231 $retorno = 1;
232 }
233
234 }
235 array_map('unlink', glob("*.csv"));
236 return $retorno;
237 }
238}
239
240
241$getMail = new GetMail();
242if($getMail->check_db() == 1){
243 $getMail->get_inbox();
244 $getMail->get_emails();
245 $getMail->email_loop();
246 $getMail->close();
247}
248?>