querymail/querymail

Query Mail is a simple and powerful tools that generate fancy emails for reporting, statistics or monitoring your data.

, _(*1)

Query Mail

Description

Query Mail is a simple and powerful tool that generate fancy emails for reporting, statistics or monitoring your data., _(*2)

You can define your own SQL queries to generate KPI you need and then encapsulate them into a single email., _(*3)

An example of email with 2 KPIs and a sample layout is provided into the project, _(*4)

Query Mail is written in PHP based on a popular framework called FuelPHP, _(*5)

The project use a SQLite preconfigured database so there is minimal dependencies., _(*6)

Installation with docker

In order to run this project properly, the easiest way is to use the provided docker installation., _(*7)

Edit the docker/ssmtp.conf file and set your mail server credentials. First, you have to install dependencies with docker-compose run --rm php composer install --no-dev -o command. Then simply run docker-compose up -d at the root path of the project. This script will create a docker container for PHP and run a nginx webserver. Add the following line in your /etc/hosts file : 127.0.0.1 querymail, _(*8)

Then you can directly access Query Mail via http://querymail, _(*9)

Installation via composer

composer create-project querymail/querymail, _(*10)

Other installation

If you prefer you can setup your own server infrastructure. You just need to know that this code will work only for PHP5.4+, _(*11)

Scheduling

Query mail doesn't provide any scheduling feature. In order to call Query Mail webservice at specific interval, I recommend to use a simple linux crontab, _(*12)

Query mail will show for each of your email the URL you can call using Curl, _(*13)

Configuration

Databases

Configure the list of your databases into the file fuel/app/config/db.php. Add the following line of code for each database you need to add :, _(*14)

'myconnection' => array(
    'type' => 'pdo',
    'connection'  => array(
        'dsn'        => 'mysql:host=myhost;dbname=mydb',
        'username'   => 'user',
        'password'   => 'password',
    ),
    'identifier' => '`',
    'table_prefix' => '',
    'charset' => 'utf8',
    'enable_cache' => true,
    'profiling' => false,
),

Each connection must have its own alias and will automaticly appear in the web interface when creating a new KPI, _(*15)

Email template layout

A sample html layout is provided into the project. You can create your own layout and put them into fuel/app/views/emails/ Keep in mind that the template must be written with the old HTML4 style because of popular webmail restriction like Gmail, Yahoo, etc., _(*16)

Project

To organize your emails, you can setup your own project list. Projects list is saved into SQLite in the QMAIL_PROJECT table. As you will see, in the basic configuration, you have only one project called My Awesome Project, _(*17)

Email driver

If you use a specific driver to send email, you can configure it into the FuelPHP framework. You will find more information in the official docummentation, _(*18)