Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Gmail API Email Processing with MySQL connection pooling (hackathon)
var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
var _ = require('underscore');
var Promise = require('promise/setimmediate');
var SCOPES = [
'https://www.googleapis.com/auth/gmail.compose',
'https://www.googleapis.com/auth/gmail.readonly',
'https://www.googleapis.com/auth/gmail.modify'
];
var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
process.env.USERPROFILE) + '/.credentials/';
var TOKEN_PATH = TOKEN_DIR + '...';
// Load client secrets from a local file.
fs.readFile('client_secret.json', function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
// Authorize a client with the loaded credentials, then call the
// Gmail API.
authorize(JSON.parse(content), listMail);
});
//mysql cnxn
var mysql = require('mysql');
var pool = mysql.createPool({
connectionLimit : 10,
host : 'localhost',
user : '...',
password : '....',
database : '.....'
});
/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
*
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
function authorize(credentials, callback) {
var clientSecret = credentials.installed.client_secret;
var clientId = credentials.installed.client_id;
var redirectUrl = credentials.installed.redirect_uris[0];
var auth = new googleAuth();
var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, function(err, token) {
if (err) {
getNewToken(oauth2Client, callback);
} else {
oauth2Client.credentials = JSON.parse(token);
callback(oauth2Client);
}
});
}
/**
* Get and store new token after prompting for user authorization, and then
* execute the given callback with the authorized OAuth2 client.
*
* @param {google.auth.OAuth2} oauth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback to call with the authorized
* client.
*/
function getNewToken(oauth2Client, callback) {
var authUrl = oauth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES
});
console.log('Authorize this app by visiting this url: ', authUrl);
var rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
rl.question('Enter the code from that page here: ', function(code) {
rl.close();
oauth2Client.getToken(code, function(err, token) {
if (err) {
console.log('Error while trying to retrieve access token', err);
return;
}
oauth2Client.credentials = token;
storeToken(token);
callback(oauth2Client);
});
});
}
/**
* Store token to disk be used in later program executions.
*
* @param {Object} token The token to store to disk.
*/
function storeToken(token) {
try {
fs.mkdirSync(TOKEN_DIR);
} catch (err) {
if (err.code != 'EEXIST') {
throw err;
}
}
fs.writeFile(TOKEN_PATH, JSON.stringify(token));
console.log('Token stored to ' + TOKEN_PATH);
}
/**
* Lists the labels in the user's account.
*
* @param {google.auth.OAuth2} auth An authorized OAuth2 client.
*/
function listLabels(auth) {
var gmail = google.gmail('v1');
gmail.users.labels.list({
auth: auth,
userId: 'me',
}, function(err, response) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
var labels = response.labels;
if (labels.length == 0) {
console.log('No labels found.');
} else {
console.log('Labels:');
for (var i = 0; i < labels.length; i++) {
var label = labels[i];
console.log('- %s', label.name);
}
}
});
}
function listMail(auth) {
var gmail = google.gmail('v1');
gmail.users.messages.list({
auth: auth,
userId: 'me',
}, function(err, response) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
// console.log(JSON.stringify(response, null, 2));
_(response.messages).each(function(message) {
getMessage(auth, message.id);
});
setTimeout(function() { listMail(auth); }, 20000);
});
}
function getMessage(auth, id) {
var gmail = google.gmail('v1');
gmail.users.messages.get({
auth: auth,
userId: 'me',
id: id
}, function(err, message) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
processMessage(auth, message);
});
}
function processMessage(auth, message) {
// console.log(JSON.stringify(message, null, 2));
//do we have a message we care about?
var toHeader = getHeader(message, 'To');
if (toHeader && toHeader.value.indexOf('+') > -1) {
var userId = toHeader.value.split('+')[1].split('@')[0];
console.log('userId', userId);
var plainPart = _(message.payload.parts).find(function(part) {
return part.mimeType == 'text/plain';
});
if (plainPart && plainPart.body.data) {
var body = new Buffer(plainPart.body.data, 'base64').toString('ascii');
var applicationLink = getLinkFromEmailBody(body)[0];
var applicationPin = getPinFromEmailBody(body)[1];
console.log('link from email: ' + applicationLink, 'pin', applicationPin);
getUsersInfo(userId).then(function(response) {
console.log('email: ', response);
updateUserDetail(userId, applicationPin, applicationLink).then(function(res) {
sendMemberMail(auth, response.first_name, response.email, applicationLink).then(function(res) {
console.log('sent!', res);
deleteMessage(auth, message.id);
});
});
});
}
}
}
function sendMemberMail(auth, firstName, email, link) {
var promise = new Promise(function (resolve, reject) {
var gmail = google.gmail('v1');
email_lines = [];
email_lines.push("From: \"...\" <...@gmail.com>");
email_lines.push("To: " + email);
email_lines.push('Content-type: text/html;charset=iso-8859-1');
email_lines.push('MIME-Version: 1.0');
email_lines.push("Subject: Your Application");
email_lines.push("Message-ID: <1234@local.machine.example>");
email_lines.push("Date: Fri, 21 Nov 1997 09:55:06 -0600");
email_lines.push("Hello " + firstName + ",<br/><br/>");
email_lines.push("Your application is ready to be finalized. Please visit your Dashboard at https://localhost/member/dashboard to complete the application process.<br/><br/>");
email_lines.push("PLEASE NOTE: YOUR APPLICATION IS INCOMPLETE UNTIL YOU COMPLETE THIS LAST STEP.<br/><br/>");
email_lines.push("Sincerely,<br/><br/>");
email_lines.push("...");
var emailMsg = email_lines.join("\r\n").trim();
console.log('Sent email to', emailMsg);
var base64EncodedEmail = new Buffer(emailMsg).toString('base64').replace(/\//g,'_').replace(/\+/g,'-');
try {
gmail.users.messages.send({
auth: auth,
userId: "me",
resource: {
raw: base64EncodedEmail
}
}, function(err, response) {
if (err) {
console.log('The API returned an error: ' + err);
reject(err);
return;
}
resolve(response);
});
} catch (e) {
//The API returned an error: Error: Insufficient Permission
console.log('error!', e);
reject(err);
}
});
return promise;
}
function deleteMessage(auth, messageId) {
console.log('delete');
var gmail = google.gmail('v1');
try {
gmail.users.messages.trash({
auth: auth,
userId: "me",
id: messageId
}, function(err, response) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
console.log('email deleted', response);
});
} catch (e) {
//The API returned an error: Error: Insufficient Permission
console.log('error!', e);
}
}
function getHeader(message, desiredHeader) {
return _(message.payload.headers).find(function(header) {
return header.name === desiredHeader;
});
}
var urlRegex = new RegExp('(http|ftp|https)://[a-z0-9\-_]+(\.[a-z0-9\-_]+)+([a-z0-9\-\.,@\?^=%&;:/~\+#]*[a-z0-9\-@\?^=%&;/~\+#])?', 'i');
var pinRegex = new RegExp(/(?:PIN: )(\d{5,})/);
function getLinkFromEmailBody(body) {
return urlRegex.exec(body);
}
function getPinFromEmailBody(body) {
return pinRegex.exec(body);
}
function getUsersInfo(userId) {
var promise = new Promise(function (resolve, reject) {
pool.getConnection(function(err, connection) {
// Use the connection
connection.query( 'SELECT u.username as email, p.first_name FROM user u, person p where u.id = ' + userId + ' and u.id = p.user_id', function(err, rows) {
connection.release();
if (err || !rows) {
console.log('rejected');
reject(err, rows);
}
console.log('resolve', rows[0]);
resolve(rows[0]);
});
});
});
return promise;
}
function updateUserDetail(userId, pin, url) {
var promise = new Promise(function (resolve, reject) {
pool.getConnection(function(err, connection) {
// Use the connection
connection.query( 'update person set pending_application_url = \'' + url + '\', pending_application_pin = \'' + pin + '\' where user_id = ' + userId, function(err, rows) {
connection.release();
if (err || !rows) {
console.log('not added', err);
reject(err, rows);
}
console.log('added', rows);
resolve(rows);
});
});
});
return promise;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment