Tekst źródłowy strony Code: 12alarmy

'''12alarmy''' używa tabeli '''_db_alert''' do przygotowania maila przy wykorzystaniu bazy MySQL oraz basha w systemie Linux.


== struktura tabeli ==

<table border=1 cellpadding=2 cellspacing=0>
<tr>
<td bgcolor=silver class='medium'>Field</td><td bgcolor=silver class='medium'>Type</td><td bgcolor=silver class='medium'>Null</td><td bgcolor=silver class='medium'>Key</td><td bgcolor=silver class='medium'>Default</td><td bgcolor=silver class='medium'>Extra</td></tr>
<tr>
<td class='normal' valign='top'>guid</td>
<td class='normal' valign='top'>varchar(38)</td>
<td class='normal' valign='top'>NO</td>
<td class='normal' valign='top'>PRI</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>medium</td>
<td class='normal' valign='top'>int(11)</td>
<td class='normal' valign='top'>NO</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>0</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>cron</td>
<td class='normal' valign='top'>varchar(50)</td>
<td class='normal' valign='top'>NO</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>daily</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>sort</td>
<td class='normal' valign='top'>varchar(20)</td>
<td class='normal' valign='top'>NO</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>init_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>finish_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>mailto_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>mailcc_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>mailfrom_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>subject_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>header_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>body_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>footer_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>attachments_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>param1_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>param2_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>action_success_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>action_fail_sql</td>
<td class='normal' valign='top'>text</td>
<td class='normal' valign='top'>YES</td>
<td class='normal' valign='top'>&nbsp;</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
</table>

== Przykładowy kod przygotowujący (skrypt SH) ==

<pre>
#!/bin/sh

html_head='<HTML><HEAD> <META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=Windows-1250\"> </HEAD><BODY>'
html_foot='</BODY></HTML>'

mysql wegasa -vvv <<EOF
set names utf8;
delete from _db_alert where guid like 'PFRON';
insert into _db_alert values (
'PFRON',
0,'daily','1',
'',
'',
#to
'select "<xxx@yyy.zzz.pl>"',
#cc
'select "<aaa@bbb.ccc.pl>"',
#'',
#from
'select "Powiadomienia Motlawa <no-reply@yyy.zzz.pl>"',
'select "Log ulg PFRON"',
'select \'${html_head}<H3>Operacje PFRON z niezaznaczoną opcją "wydrukowana i wysłana"</H3>\'',
#body
'SELECT o.nrdokfin,up.kwota, up.ulga, o.datawystaw, o.dataplatnosci, K.nazwa \
 FROM ulga_pfron up LEFT JOIN kontrah K ON K.GUID = up.kontrGUID LEFT JOIN opng o ON o.GUID = up.operGUID  WHERE o.nrdokfin<>"" and up.PrintSend = 0 \
 ORDER BY datawystaw, dataplatnosci ',
#footer
'select \'<BR><BR><B><I>Wasz Automatyczny Serwis Powiadomień</B>${html_foot}</I>\'',
# attachment:
'',
# param1:
'',
'',
'',
''
);

EOF

</pre>



[[Kategoria: Programowanie]]