Skip to content

Instantly share code, notes, and snippets.

@isidroamv

isidroamv/mysql.js

Created November 23, 2015 06:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save isidroamv/2b2de308e4eb3fe995e9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save isidroamv/2b2de308e4eb3fe995e9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. isidroamv created this gist Nov 23, 2015.
    18 changes: 18 additions & 0 deletions mysql.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    'use strict';
    var mysql = require('mysql');
    var config = require('../config');
    var pool = mysql.createPool(config.mysql);
    exports.exec = function(query, params, callback) {
    if (!query) {
    callback(true);
    }
    pool.getConnection(function(err, connection) {
    if(err) { console.log(err); callback(true); return; }
    var q = connection.query(query, params, function(err, results) {
    connection.release();
    if(err) { console.log(err); callback(true); return; }
    callback(false, results);
    });
    console.log("Query: ",q.sql);
    });
    };
    181 changes: 181 additions & 0 deletions user.controller.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,181 @@
    'use strict';

    var _ = require('lodash');
    var User = require('./user.model');
    var https = require('https');
    var mysql = require('../../components/mysql');
    var utility = require('../../components/utility');



    // Get list of Users
    exports.index = function(req, res) {

    // Define number to paginate
    var from = parseInt(req.query.from) || 0;
    var to = parseInt(req.query.to) || 1;

    // Create query and parameters
    var query = "SELECT * FROM users LIMIT ?";
    var pagination = [from,to];

    // Return user
    mysql.exec(query, [pagination], function (err, user) {
    if (err) { handleError(res, err, 500) };
    res.json(user);
    });
    };



    // Get one User
    exports.show = function (req, res) {

    // Define Query and parameters
    var query = "SELECT * FROM users WHERE id = ?";
    var id = req.params.id;

    // Return One User
    mysql.exec(query, [id], function (err, users) {
    if (err) { handleError(res, err, 500) };
    res.json(users);
    });
    }



    // Create a User
    exports.create = function (req, res) {
    var user = req.body;
    var now = utility.dateNow();

    // Validate input fields
    User.validate( user, 'post' , function (err) {
    if (err) { handleError(res,err,400); return; };

    // Define values to insert
    var values = {
    name: user.name,
    last_name: user.last_name,
    username: user.username,
    password: user.password,
    email: user.email,
    birthdate: user.birthdate,
    social_account_id: user.social_account_id,
    customers_id: user.customers_id,
    plan_id: user.plan_id,
    controllers_id: user.controllers_id,
    updated_at: now,
    created_at: now
    };

    // Define Query
    var query = "INSERT INTO users SET ? ";

    // Return One User
    mysql.exec(query, values, function (err, data) {
    if (err) { handleError(res, err, 500); return; };

    if (data.affectedRows < 1) {
    let error = { errors: [{
    title: 'Can not insert User'
    }]};
    handleError(res, error, 400 ); return;
    }

    res.json({
    id: data.insertId,
    username: values.username,
    password: values.password
    });
    });
    });
    }



    // Update a User
    exports.update = function (req, res) {
    var user_new = req.body;
    var now = utility.dateNow();
    var error = { errors: [] };

    // User Id comes from URL
    user_new.id = req.params.id;

    User.validate( user_new ,'put', function (err) {
    if (err) { handleError(res,err,400); return; };

    User.findById(user_new.id, function (err, user) {
    if (err) { handleError(res,err,400); return; };

    if (!user){
    err = {
    source: { parameter: 'id'},
    title: 'User no found'
    };
    error.errors.push(err);
    handleError(res,err,404); return;
    }

    // Define values to insert
    var values = {
    name: user_new.name || user.name,
    last_name: user_new.last_name || user.last_name,
    username: user_new.username || user.username,
    password: user_new.password || user.password,
    email: user_new.email || user.email,
    birthdate: user_new.birthdate || user.birthdate ,
    social_account_id: user_new.social_account_id || user.social_account_id,
    plan_id: user_new.plan_id || user.plan_id,
    controllers_id: user_new.controllers_id || user.controllers_id,
    updated_at: now,
    created_at: now
    }

    // Define Query
    var query = "UPDATE users SET ? WHERE id = ? ";

    // Return Query Status
    mysql.exec(query, [values, user.id], function (err, data) {
    if (err) { handleError(res, err, 500); };
    if (data.affectedRows < 1) {
    error.errors.push({ title: 'Can not insert Users' });
    handleError(res, error, 400 ); return;
    }
    res.json( {success: true });
    });
    });

    });
    }



    // Delete a User
    exports.delete = function (req, res) {
    var error = { errors: [] };

    // SQL Query
    var query = "DELETE FROM users WHERE id = ?";

    // Return One User
    mysql.exec(query, req.params.id , function (err, data) {
    if (err) { handleError(res, err, 500); return; };
    if (data.affectedRows < 1) {
    error.errors.push({ title: 'Can not delete User' });
    handleError(res, error, 400 ); return;
    }
    res.json( {success: true });
    });
    }



    // Reponse with error and code
    function handleError(res, err, code) {
    if (code===500) {
    err = "Internar Error";
    }
    res.status(code).send(err);
    }