mysql.js

 function sendCotizacion(db, userName, productsObjArray, totalsObj, clientData, res, req) {
    const pool = new sql.connect(config, err => {
        console.log("err: " + err)
    });
    const transaction = new sql.Transaction(pool);
 
    transaction.begin(err => {
        if (err) {
            console.log("err" + err);
        }
        var rolledBack = false;
        transaction.on('rollback', function(aborted) {
            rolledBack = true;
        })
        //const request = new sql.Request(transaction)
 
    var numRegObj = { numreg : "", ActConsecutivo: "" };
    var exchangeRateAndDateTime;
    var parametersObject = {};
    var returnMessage;
    var returnNumReg;
    var returnActCon;
 
    return sqlcode.NumRegUnico(numRegObj, transaction)
    .catch( function (err) {
        misc.timestamp("Caught rejection: " + err)
        throw "throwing copper";
    })        
    .then(function() { //begin then1
        returnNumReg = numRegObj['numreg'];
        misc.timestamp(userName + " sqlcode.GetExchangeRateDateAndTimeFromMSSQL")
        return sqlcode.GetExchangeRateDateAndTimeFromMSSQL(transaction)
        .catch( function (err) {
            misc.timestamp("Caught rejection: " + err)
            throw "throwing copper";
        })                    
        .then(function(recordsets) {
        console.log("recordsets2: " + JSON.stringify(recordsets[0]) );
        exchangeRateAndDateTime = recordsets[0][0];
    })
    .catch( function (err) {
        misc.timestamp("Caught rejection: " + err)
        throw "throwing copper";
    })            
    .then(function(recordsets) { //begin then2
        console.log("Using Serie: " + req.session.userjson.serie);
        return sqlcode.ActConsecutivo(req.session.userjson.serie, "Y", transaction)
        .catch(function(err) {
            misc.timestamp("Caught rejection123123: " + err)
        })
        .then(function(recordsets) { //then3
            console.log("recordsets: " + recordsets[0][0]['NUMERO_1'] );
            numRegObj.ActConsecutivo = recordsets[0][0]['NUMERO_1'];
            returnActCon = numRegObj['ActConsecutivo'];
        })
        .catch( function (err) {
            misc.timestamp("Caught rejection1: " + err)
            throw "throwing copper";
        })
        .then(function(recordsets) { // end then3, begin then4
            parametersObject = {
                numreg:  numRegObj['numreg'],
                codigoEmpresa: "03",  /*   HARD CODED    */
                codigoSucursal: req.session.userjson.codsuc,
                serie: req.session.userjson.serie,
                ActConsecutivo: pad(numRegObj['ActConsecutivo'], 7), // Num DOC padded to 7 zeres
                codigoCliente: clientData.clientid, // from sqlite3 now
                nombreCliente: clientData.clientname,
                direccionCliente: clientData.clientaddress,
                ciudadCliente: clientData.clientcity,
                plazoCliente: clientData.clientplazo ? clientData.clientplazo : 0,  // "Clientes de credito tienen plazo NULO"
                limiteCliente: clientData.clientcreditlimit,
                observaciones: 'COTIZADOR WEB Mark IV',
                tipoDocumento: '25',
                fecha: "CONVERT(DATETIME, '" + exchangeRateAndDateTime['Fecha'] + " 00:00:00', 102)",
                hora: "CONVERT(DATETIME, '1899-12-30 " + exchangeRateAndDateTime['Hora'] + "', 102)",
                tipoCambio: exchangeRateAndDateTime['TipoDeCambioDolar'],
                codigoVendedor: req.session.userjson.codigovendedor, // changed 20170630 for SAV-8
                items: productsObjArray,
                totals: totalsObj,
            };
            console.log("Parameters Object: " + JSON.stringify(parametersObject));
        // BEGIN MSSQL CALLS
        })
        .catch( function (err) {
            misc.timestamp("Caught rejection2: " + err)
            throw "throwing copper";
        })
        .then(function() {
            return sqlcode.CotizacionTarjDescDoc(parametersObject, transaction)
        })
        .catch( function (err) {
            misc.timestamp("Caught rejection3: " + err)
            throw "throwing copper";
        })                
        .then(function() { //begin then5
            console.log("post CTDD");
            return sqlcode.CotizacionVenta(parametersObject, transaction)
        })
        .catch( function (err) {
            misc.timestamp("Caught rejection4: " + err)
 
            throw "throwing copper";
        })
        .then(function() { // end then5, begin then6
            console.log("Post CV.");
            const CTKobj = {
                numreg: numRegObj['numreg'],
                // codigoSucursal: hardcoded_CODSUC,
                codigoSucursal: req.session.userjson.codsuc,
                unidades: 1,
                items: parametersObject.items
            };
            return sqlcode.CotizacionTarjetaKardex_BULK(CTKobj, transaction)
        })
        // .catch( function (err) {
        //     misc.timestamp("Caught rejection5a: " + err)
        //     throw "throwing copper";
        // })
        // .then(function() { // end then6, begin then7
        //     console.log("CTK Bulk Done");
        //     misc.timestamp(userName + " Clearing Shoppinglist")
        //     return sqlcode.ClearShoppingListFromSqlite(db, userName)
        // })
        // .catch( function (err) {
        //     misc.timestamp("Caught rejection6: " + err)
        //     throw "throwing copper";
        // })            
        // .then(function() { // end then7, begin then8
        //     misc.timestamp(userName + " Clearing Client: " + req.session.userjson.cid + " / " + req.session.userjson.cname);
        //     return sqlcode.ClearCurrentClientFromSqlite(db, userName);
        // })
        .catch( function (err) {
            misc.timestamp("Caught rejection7: " + err)
            returnMessage = "ACHTUNG KAPUT";
            console.log("rolledback: " + rolledBack);
            if (!rolledBack) {
                console.log("Rolling back");
                transaction.rollback(function(err) {
                    if (err) {
                        console.log("rollback error: " + err)
                    }
                    else {
                        console.log("rollback success");
                    }
                })
            }
        })            
        .then(function() { // end then8, begin then9
            // this stuff never happens if there's a rejection
            misc.timestamp(userName + " Done saving cotizacion data " + returnMessage);
 
            /* THIS IS WHERE AN E-MAIL SHOULD BE SENT TO THE USER WITH THE COTIZACION DETAILS */
 
            // if everything is OK
            if (req.session.userjson) {
                console.log("req.session.userjson defined");
            }
            if (req.session.userjson && ( returnMessage != "ACHTUNG KAPUT" )   )    {
                console.log("balls to the walls")
                // req.session.userjson.cid = undefined;
                // req.session.userjson.cname = undefined;
                returnMessage = "ALLES GUT";
 
                // without this, nothing really happens!
                transaction.commit(function(err) {
                    if (err) { console.log("commit error: " + err)}
                })                                                
            }
            res.send(
            {
                message : returnMessage,
                numreg : returnNumReg,
                act: returnActCon
            });
        })
     
    }) // end then4
    // }) // end then2
}) // end then1
 
}) // transaction begin
 
} // end function

function NumRegUnico(obj, transaction) {    
    console.log("Entering NumRegUnico");
    return sql.connect(config)
        .then(function(recordsets) {
            var request = new sql.Request(transaction);
            request.output('NuevoNumReg', sql.char);
            return request.execute('dbo.uspNuevoRegistro').then(function(recordsets) {
                console.log("output param: " + request.parameters.NuevoNumReg.value); // new inc'd numreg
                obj.numreg = request.parameters.NuevoNumReg.value; 
                return obj;
            })
         });
}

 
// need this for mCodGuardar parameter ...
function ActConsecutivo(mCodDia, mTipDia, transaction) {
    console.log("Entering ActConsecutivo ...");
    // return sql.connect(config)
        .then(function(recordsets) {
            console.log('ugh111');     
            return new sql.Request(transaction)
            .input('mCodDia', sql.NVarChar(8), mCodDia)
            .input('mTipDia', sql.NVarChar(1), mTipDia)
            .execute('dbo.uspActConsecutivo');
    // });
}

function GetExchangeRateDateAndTimeFromMSSQL(transaction) {
    // return sql.connect(config)
    .then(function(recordsets) {
        return new sql.Request(transaction)
        .execute('dbo.uspGetExchangeRateDateAndTimeFromMSSQL'); // was CTDD before
    // });
}
 
 
// function to push data to Cotizacion Tarjeta Desc Doc using MSSQL module
// the single parameter should be a JSON with all the parameter names an parameters
// that will be pushed to MSSQL
// ugh need to leanr how to make JSONs so i can write/use this function
function CotizacionTarjDescDoc(CTDDjson, transaction) {
 
    // var date = new Date();
    // console.log("hello123");    
    // var formattedDate = moment(date).format('YYYY-MM-DD-HH:mm:ss');
    // console.log("Timestamp:" + formattedDate);
    misc.timestamp();
    console.log("Entering CotizacionTarjDescDoc");
    console.log("Our object: " + JSON.stringify(CTDDjson));
    // return sql.connect(config).then(function() {
        console.log("Our object, again: " + JSON.stringify(CTDDjson)  );
        console.log("Numero de Registro: " + CTDDjson.numreg);
        console.log("Tipo Documento: " + CTDDjson.tipoDocumento);
        request = new sql.Request(transaction)
        request.verbose = true;
       
        request.input('mNumero', sql.Int, CTDDjson.numreg)
        request.input('mCodEmp', sql.NVarChar(2), CTDDjson.codigoEmpresa)
        // 20170628 i want to use the same SUC and SERIE for all, but we will mee w Rosa Gomez tomorrow first
        // 20170822 this doesnt work because Facturacion was never truly centralized, see SAV-38
        request.input('mCodSuc', sql.NVarChar(3), CTDDjson.codigoSucursal) // // Hardcoded in calling function, approved by Rosa Gomez 20170629
        request.input('mDiario', sql.NVarChar(8), CTDDjson.serie) // Hardcoded in calling function, approved by Rosa Gomez 20170629
        request.input('mCodGuardar', sql.NVarChar(7), CTDDjson.ActConsecutivo) // ActConsecutivo
        request.input('mCodId', sql.NVarChar(7), CTDDjson.codigoCliente) // client ID
        request.input('mDescDoc', sql.NVarChar(100), CTDDjson.nombreCliente) // client name
        request.input('mTipDoc', sql.TinyInt,  CTDDjson.tipoDocumento) // 25 credit, 26 cash
        request.input('mFecha', sql.NVarChar(50), CTDDjson.fecha)
        request.input('mHora', sql.NVarChar(50), CTDDjson.hora)
        request.input('mTipoCamb', sql.Float, CTDDjson.tipoCambio)
        request.input('mCodigoVendedor', sql.NVarChar(5), CTDDjson.codigoVendedor) // codigo vendedor??? // should be cotizador web USERNAME
        // we also need to add a new parameter, mCodVendedor so we can store the vodigo vendedor in Cotitzacion Tarj Desc Doc
        request.execute('uspInsertCTDD');
        return request;
    // });
}
 
// Inserts to legacy SCM table for quotations, 'Cotizacion Ventas'
// The original "Cotizador Web" ignored many columns, but we've included them here
// Alimenta la tabla original SCM 'dbo.[Cotizacion Ventas]'
// el Cotizador Web ignoraba muchas columnas, pero las hemos listado todas aqui
function CotizacionVenta(CVjson, transaction) {
    console.log("Entering CotizacionVenta");
    console.log("Our object: " + JSON.stringify(CVjson));
    // return sql.connect(config).then(function() {
        request = new sql.Request(transaction)
            request.verbose = true;
            // same types as SCM DB table
            request.input('mNumero', sql.Int, CVjson.numreg);
            request.input('mPlazo', sql.SmallInt, CVjson.plazoCliente);
            request.input('mCodVend', sql.NVarChar(5), CVjson.codigoVendedor); // ActConsecutivo
            // NUM_ORD
            // MONEDA
            // TIP_DESC
            // TIP_EXO
            // COD_ZON
            request.input('mDireccion', sql.NVarChar(400), CVjson.direccionCliente);
            // TELEFONO
            // FAX
            request.input('mObservaciones', sql.NVarChar(400), CVjson.observaciones);
            // VAL_PORC_DESC
            // VAL_PORC_DESCS (En serio)
            // VAL_PORC_IGC
            // VAL_PORC_IEC
            // IR
            // VAL_IR
            // VALOR_BONIF
            // COSTO_BONIF
            request.input('mCosto', sql.Float, CVjson.totals.COSPROMCTOTAL); // SUM of all COSTO_PROMEDIO_NIO
            request.input('mDescuento', sql.Float, 0); // descuento on the whole cotizacion??? 20170718
            // DESCUENTOS
            request.input('mSubtotal', sql.Float,  CVjson.totals.SUBTOTALTOTAL); // sub of QTY*PRICE for entire cotizacion
            request.input('mIgv', sql.Float, CVjson.totals.IVATOTAL); // sum of IVA for entire cotizacion
            // IEC
            request.input('mValor', sql.Float, CVjson.totals.PRECIOCONIVATOTAL); // final total for the cot
            // tip_vent
            return request.execute('uspInsertCV')
            // .catch( function (err) {
            //     misc.timestamp("Caught rejection5: " + err)
            //     //throw "shitballs";
            //     // return Promise.reject();
            // })
            // .done (function() {
            //   return request;  
            // })
            // return request;
    // })
}
 
function CotizacionTarjetaKardex_BULK(CTKjson, transaction) {
    // return sql.connect(config).then(function() {
       
/*
        var table = new sql.Table('testing');
        table.columns.add('testing', sql.NChar(10), {nullable: true});
 
        for(var i = 0; i < 10; i++) {
        table.rows.add('hubert');    
        }
        */
       
        var table = new sql.Table('Cotizacion Tarjeta de Kardex');
        itemsObject = CTKjson.items; // itemsObject is really an array of objects
        console.log("itemsObject: " + itemsObject);
 
        table.columns.add('NUM_REG',            sql.Int,            {nullable: false});
        table.columns.add('COD_SUC',            sql.NVarChar(3),    {nullable: true});
        table.columns.add('NUM_LIN',            sql.Float,          {nullable: true});
        table.columns.add('TIPO_ITEM',          sql.NVarChar(1),    {nullable: true});
        table.columns.add('COD_PROD',           sql.NVarChar(25),   {nullable: true});
        table.columns.add('CANTIDAD',           sql.Float,          {nullable: true});
        table.columns.add('COSTO',              sql.Float,          {nullable: true});
        table.columns.add('Unidades',           sql.Float,          {nullable: true});
        table.columns.add('Cod_Prec',           sql.TinyInt,        {nullable: true});
        table.columns.add('VAL_ORIG',           sql.Float,          {nullable: true});
        table.columns.add('VALOR',              sql.Float,          {nullable: true});
        table.columns.add('VAL_PORC_DESC',      sql.Float,          {nullable: true});
        table.columns.add('VAL_NETO',           sql.Float,          {nullable: true});
        table.columns.add('BONIFICACION',       sql.NVarChar(1),    {nullable: true});
        table.columns.add('VAL_ORG_IGV',        sql.Float,          {nullable: true});
        table.columns.add('VAL_PORC_IGV',       sql.Float,          {nullable: true});
        table.columns.add('EXIST_ANT',          sql.Float,          {nullable: true});
        table.columns.add('COSTO_ANT',          sql.Float,          {nullable: true});
        table.columns.add('COSTO_D',            sql.Float,          {nullable: true});
        table.columns.add('TIPO',               sql.TinyInt,        {nullable: true});
        table.columns.add('COD_MEC',            sql.NVarChar(3),    {nullable: true});
        table.columns.add('CANTIDAD_SOLICITADA',sql.Float,          {nullable: true});
        table.columns.add('COSTO1',             sql.Float,          {nullable: true});
        table.columns.add('PRECIOPEDIDO',       sql.Float,          {nullable: true});
        table.columns.add('EXIST_SUCSOLICITANTE',sql.Float,         {nullable: true});
        table.columns.add('COD1',               sql.NVarChar(8),    {nullable: true});
        table.columns.add('NUM1',               sql.NVarChar(7),    {nullable: true});
        table.columns.add('promvent',           sql.Float,          {nullable: true});
        table.columns.add('BLOQUEO',            sql.TinyInt,        {nullable: true});
        table.columns.add('COD_SEG',            sql.Float,          {nullable: true});
        table.columns.add('DESC_MANUAL',        sql.NVarChar(150),  {nullable: true});
 
        // loop through each item in the cart and build a list of rows for mssql BULK insert
 
        for (var i = 0; i < itemsObject.length; i++) {
            var obj = itemsObject[i];
            table.rows.add(
                CTKjson.numreg,
                CTKjson.codigoSucursal,
                //obj.lineNumber,
                i,                      // LINE NUMBER
                'P',                    // TIPO_ITEM
                obj.ALT,                // COD_PROD
                -Math.abs(obj.QTY),             // CANTIDAD
                obj.COSPROMC,           // COSTO
                1,                      // Unidades -- always '1' in legacy
                // 20170427 EVERY PRODUCT CAN HAVE A DIFFERENT PRICE TYPE, THIS NEEDS TO BE RECORDED IN SQLITE CART CONTENTS
                //CTKjson.usuarioPrecio,    // Cod_Prec
                obj.PRICETYPE, // fixed 20170427 still need to verify in CTKK in SCM DB
                obj.PRECIOSISTEMA,      // VAL_ORIG -- aka PRECIO UNITARIO
                obj.PRECIOUSUARIO,      // VALOR
                obj.DESCUENTO,          // VAL_PORC_DESC -- 0 for now, we are not handling discounts
                obj.PRECIONETO,     // VAL_NETO
                '',                     // BONIFICACION -- always empty in legacy
                15,                     // VAL_ORG_IGV -- always 15 in legacy, hardcoded
                15,                     // VAL_PORC_IGV -- always 15 in legacy, hardcoded
                1,                      // EXIST_ANT -- always '1' in legacy
                0,                      // COSTO_ANT -- left null in legacy
                obj.PCOSD,          // COSTO_D
                0,                      // TIPO -- left null in legacy
                '',                     // COD_MEC -- always empty in legacy
                0,                     // CANTIDAD_SOLICITADA -- null in legacy
                0,                     // COSTO1 -- null in legacy
                0,                     // PRECIOPEDIDO -- null in legacy
                0,                     // EXIST_SUCSOLICITANTE -- null in legacy
                '',                       // COD1 -- empty in legacy
                '',                      // NUM1 -- empty in legacy
                0,                       // promvent -- null in legacy
                0,                       // BLOQUEO -- null in legacy
                0,                      // COD_SEG -- null in legacy
                'COTIZADOR WEB Mark III');         // DESC_MANUAL -- NULL in legacy
 
        }
        request = new sql.Request(transaction);
        request.verbose = true;
        request.bulk(table);
        return request;
    // });
}