Friday, October 14, 2016

How to run R script under SQL Server 2016

SQL Server 2016 has one new feature: R services. This is a good news for data scientist. You can choose install SQL Server 2016 express version with advanced service, or you can also choose to upgrade your current SQL Server older express version to 2016 express to use the new feature. Note after you finished your installation, you need to manually install the feature using SQL Server 2016 installation program.

After you finished the installation, you need to lauch SQL Server Management Service 2016, and run the following script:
Exec sp_configure  'external scripts enabled', 1  
Reconfigure  with  override  
After running this script, you need to restart your SQL Server Database instance, and run the following script:
Exec sp_configure  'external scripts enabled'  
After running the above script, it should get the following result:
 
After finish above configuration, you need to manually restart SQL Server LauchPad service:
To restart the LauchPad service, please go to Windows Administrative Tools -> Services and find "SQL Server LauchPad (Your DB Name)"


Now the last step to test if you can run R script:
exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go  
Expected Results:

hello 1

If experience the following error, you need to restart the LauchPad service.

Msg 39011, Level 16, State 1, Line 1
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.


Reference:
https://msdn.microsoft.com/en-us/library/mt696069.aspx
http://dba.stackexchange.com/questions/120205/msg-39011-sql-server-was-unable-to-communicate-with-the-launchpad-service

Wednesday, August 31, 2016

How to install window service


A window service need to be installed at first. Under Windows 10, click on window button, type dev inside the search input, and choose Developer Command Prompt for VS2012, which is the service installer named installutil.exe.

Note, when you applying installutil to install window service, you need to run it with Administrator privilege.

Then go to the directory of compiled service, usually it's inside directory of bin/debug, and type
installutil yourservice.exe.

Your service will be installed successfully.

Reference:
http://www.c-sharpcorner.com/uploadfile/naresh.avari/develop-and-install-a-windows-service-in-c-sharp/

Tuesday, August 9, 2016

Integrated R.Net within your ASP.Net MVC applicaiton

R is a programming language used for statistical computing. For business intelligence, R is very useful for prediction marketing. To better integrating R into Microsoft .Net system, there is one tool named R.Net. You can use NuGet to install R.Net to your .net project. The latest version of R.Net is 1.6.5. You can use the following command to install the latest R.Net:
Install-Package R.NET.Community
Note R.Net didn't set the environment variable correctly. So the first issue you suffered for R.Net would be package installation.
REngine r = REngine.GetInstance();
r.Evaluate("library(RODBC)");
An unhandled exception of type 'RDotNet.EvaluationException' occurred in RDotNet.dll
Additional information: Error: package or namespace load failed for 'RODBC'
This is happened because R.net can't find r.dll as the Path doesn't included. To set the correct Path under windows 10, click on windows button, choose Settings, click on System, choose About from the left menu, scroll down to the bottom, click on System Info, choose Advanced System Setting from the left menu, choose the Tab of Advanced, click on Environment Variables button, then click on Edit button, click on New button, add following path:
C:\\Program Files\\R\\R-3.3.1\\bin\\i386
After this, restart windows 10, you should resolve this issue. Note other resources prefer to add more paths such as library, it's wrong!!! It will affect some packages installation.


Reference:
https://www.nuget.org/packages/R.NET.Community/
http://jmp75.github.io/rdotnet/ts_asp_dot_net/




Friday, June 24, 2016

How to align an image center inside div


HTML Blocks are elements established as a block-level element, which are created by using the <div> element.

The most common way to center blocks with CSS is to set both the left and right margins to auto. Here is the CSS:

<style>
  div.center {
    margin: auto;
  }
</style>
The most common way to center blocks with CSS is to set both the left and right margins to auto. Here is the CSS:

Reference:
https://www.w3.org/Style/Examples/007/center.en.html

Thursday, June 23, 2016

How to vertical align an image inside an anchor

To vertical align an image inside an anchor, there are 3 necessary parts inside the CSS:

1. Your anchor should have same  height and inline-height.
2. Your image should have vertical-align: middle;
3. Your image should have display: inline-block;

<style>
    .thumbnail {
        width: 150px;
        height: 150px;
        text-align: center;
        line-height: 150px;
    }
    .thumbnail img {
        margin: auto;
        vertical-align: middle; 
    }  
</style>
Reference:
http://stackoverflow.com/questions/20700475/vertical-align-image-inside-an-anchor-with-css
http://plnkr.co/edit/b5jEtK5EWVglrhEkj14e?p=preview

Thursday, June 16, 2016

jsPDF AutoTable Plugin examples

Bundle.cfg
bundles.Add(new ScriptBundle("~/bundles/jsPDF").Include(
    "~/Scripts/plugins/jsPDF/jspdf.js",
    "~/Scripts/plugins/jsPDF/jspdf.plugin.text-align.js",
    "~/Scripts/plugins/jsPDF/jspdf.plugin.autotable.js"));
jspdf.plugin.text-align.js
(function (api, $) {
    'use strict';
    api.writeText = function (x, y, text, options) {
        options = options || {};
        var defaults = {
            align: 'left',
            width: this.internal.pageSize.width
        }

        var settings = $.extend({}, defaults, options);

        // Get current font size
        var fontSize = this.internal.getFontSize();

        // Get the actual text's width
        /* You multiply the unit width of your string by your font size and divide
         * by the internal scale factor. The division is necessary
         * for the case where you use units other than 'pt' in the constructor
         * of jsPDF.
         */
        var txtWidth = this.getStringUnitWidth(text) * fontSize / this.internal.scaleFactor;

        if (settings.align === 'center')
            x += (settings.width - txtWidth) / 2;
        else if (settings.align === 'right')
            x += (settings.width - txtWidth);

        //default is 'left' alignment
        this.text(text, x, y);
    }
})(jsPDF.API, jQuery);
jsPDF example
/*
 |--------------------------------------------------------------------------
 | This file contains examples of how to use this plugin
 |--------------------------------------------------------------------------
 |
 | To see what the pdfs generated by these examples looks like you can open
 | ´examples.html´ or go to http://simonbengtsson.github.io/jsPDF-AutoTable.
 |
 | To make it possible to view each example in examples.html some extra code
 | are added to the examples below. For example they return their jspdf
 | doc instance and gets generated data from the library faker.js. However you
 | can of course use this plugin how you wish and the simplest first example
 | below would look like this without any extras:
 |
 | var columns = ["ID", "Name", "Age", "City"];
 |
 | var data = [
 |     [1, "Jonatan", 25, "Gothenburg"],
 |     [2, "Simon", 23, "Gothenburg"],
 |     [3, "Hanna", 21, "Stockholm"]
 | ];
 |
 | var doc = new jsPDF('p', 'pt');
 | doc.autoTable(columns, data);
 | doc.save("table.pdf");
 |
 */

var examples = {};

// Default - shows what a default table looks like
examples.auto = function () {
    var doc = new jsPDF('p', 'pt');
    doc.autoTable(getColumns(), getData());
    return doc;
};

// Minimal - shows how compact tables can be drawn
examples.minimal = function () {
    var doc = new jsPDF('p', 'pt');
    doc.autoTable(getColumns(), getData(), {
        tableWidth: 'wrap',
        styles: {cellPadding: 2},
        headerStyles: {rowHeight: 15, fontSize: 8},
        bodyStyles: {rowHeight: 12, fontSize: 8, valign: 'middle'}
    });
    return doc;
};

// Long data - shows how the overflow features looks and can be used
examples.long = function () {
    var doc = new jsPDF('l', 'pt');
    var columnsLong = getColumns().concat([
        {title: shuffleSentence(), dataKey: "text"},
        {title: "Text with a\nlinebreak", dataKey: "text2"}
    ]);

    doc.text("Overflow 'ellipsize' (default)", 10, 40);
    doc.autoTable(columnsLong, getData(), {
        startY: 55,
        margin: {horizontal: 10},
        columnStyles: {text: {columnWidth: 250}}
    });

    doc.text("Overflow 'hidden'", 10, doc.autoTableEndPosY() + 30);
    doc.autoTable(columnsLong, getData(), {
        startY: doc.autoTableEndPosY() + 45,
        margin: {horizontal: 10},
        styles: {overflow: 'hidden'},
        columnStyles: {email: {columnWidth: 160}}
    });

    doc.text("Overflow 'linebreak'", 10, doc.autoTableEndPosY() + 30);
    doc.autoTable(columnsLong, getData(3), {
        startY: doc.autoTableEndPosY() + 45,
        margin: {horizontal: 10},
        styles: {overflow: 'linebreak'},
        bodyStyles: {valign: 'top'},
        columnStyles: {email: {columnWidth: 'wrap'}},
    });

    return doc;
};

// Content - shows how tables can be integrated with any other pdf content
examples.content = function () {
    var doc = new jsPDF('p', 'pt');

    doc.setFontSize(18);
    doc.text('A story about Miusov', 40, 60);
    doc.setFontSize(11);
    doc.setTextColor(100);
    var text = doc.splitTextToSize(shuffleSentence(faker.lorem.words(55)) + '.', doc.internal.pageSize.width - 80, {});
    doc.text(text, 40, 80);

    var cols = getColumns();
    cols.splice(0, 2);
    doc.autoTable(cols, getData(40), {startY: 150});

    doc.text(text, 40, doc.autoTableEndPosY() + 30);

    return doc;
};

// Multiple - shows how multiple tables can be drawn both horizontally and vertically
examples.multiple = function () {
    var doc = new jsPDF('p', 'pt');
    doc.setFontSize(22);
    doc.text("Multiple tables", 40, 60);
    doc.setFontSize(12);

    doc.autoTable(getColumns().slice(0, 3), getData(), {
        startY: 90,
        pageBreak: 'avoid',
        margin: {right: 305}
    });

    doc.autoTable(getColumns().slice(0, 3), getData(), {
        startY: 90,
        pageBreak: 'avoid',
        margin: {left: 305}
    });

    for (var j = 0; j < 6; j++) {
        doc.autoTable(getColumns(), getData(9), {
            startY: doc.autoTableEndPosY() + 30,
            pageBreak: 'avoid',
        });
    }

    return doc;
};

// From html - shows how pdf tables can be be drawn from html tables
examples.html = function () {
    var doc = new jsPDF('p', 'pt');
    doc.text("From HTML", 40, 50);
    var res = doc.autoTableHtmlToJson(document.getElementById("basic-table"));
    doc.autoTable(res.columns, res.data, {startY: 60});
    return doc;
};

// Header and footers - shows how header and footers can be drawn
examples['header-footer'] = function () {
    var doc = new jsPDF('p', 'pt');

    var header = function (data) {
        doc.setFontSize(20);
        doc.setTextColor(40);
        doc.setFontStyle('normal');
        doc.addImage(headerImgData, 'JPEG', data.settings.margin.left, 40, 25, 25);
        doc.text("Report", data.settings.margin.left + 35, 60);
    };

    var totalPagesExp = "{total_pages_count_string}";
    var footer = function (data) {
        var str = "Page " + data.pageCount;
        // Total page number plugin only available in jspdf v1.0+
        if (typeof doc.putTotalPages === 'function') {
            str = str + " of " + totalPagesExp;
        }
        doc.text(str, data.settings.margin.left, doc.internal.pageSize.height - 30);
    };

    var options = {
        beforePageContent: header,
        afterPageContent: footer,
        margin: {top: 80}
    };
    doc.autoTable(getColumns(), getData(40), options);

    // Total page number plugin only available in jspdf v1.0+
    if (typeof doc.putTotalPages === 'function') {
        doc.putTotalPages(totalPagesExp);
    }

    return doc;
};

// Themes - shows how the different themes looks
examples.themes = function () {
    var doc = new jsPDF('p', 'pt');
    doc.setFontSize(12);
    doc.setFontStyle('bold');

    doc.text('Theme "striped"', 40, 50);
    doc.autoTable(getColumns(), getData(), {startY: 60});

    doc.text('Theme "grid"', 40, doc.autoTableEndPosY() + 30);
    doc.autoTable(getColumns(), getData(), {startY: doc.autoTableEndPosY() + 40, theme: 'grid'});

    doc.text('Theme "plain"', 40, doc.autoTableEndPosY() + 30);
    doc.autoTable(getColumns(), getData(), {startY: doc.autoTableEndPosY() + 40, theme: 'plain'});

    return doc;
};

// Horizontal - shows how tables can be drawn with horizontal headers
examples.horizontal = function () {
    var doc = new jsPDF('p', 'pt');
    doc.autoTable(getColumns().splice(1,4), getData(), {
        drawHeaderRow: function() {
            // Don't draw header row
            return false;
        },
        columnStyles: {
            first_name: {fillColor: [41, 128, 185], textColor: 255, fontStyle: 'bold'}
        }
    });
    return doc;
};


// Custom style - shows how custom styles can be applied to tables
examples.custom = function () {
    var doc = new jsPDF('p', 'pt');
    doc.autoTable(getColumns().slice(2, 6), getData(20), {
        styles: {
            font: 'courier',
            fillStyle: 'DF',
            lineColor: [44, 62, 80],
            lineWidth: 2
        },
        headerStyles: {
            fillColor: [44, 62, 80],
            fontSize: 15,
            rowHeight: 30
        },
        bodyStyles: {
            fillColor: [52, 73, 94],
            textColor: 240
        },
        alternateRowStyles: {
            fillColor: [74, 96, 117]
        },
        columnStyles: {
            email: {
                fontStyle: 'bold'
            }
        },
        createdCell: function (cell, data) {
            if (data.column.dataKey === 'expenses') {
                cell.styles.halign = 'right';
                if (cell.raw > 600) {
                    cell.styles.textColor = [255, 100, 100];
                    cell.styles.fontStyle = 'bolditalic';
                }
                cell.text = '$' + cell.text;
            } else if (data.column.dataKey === 'country') {
                cell.text = cell.raw.split(' ')[0];
            }
        }
    });
    return doc;
};

// Custom style - shows how custom styles can be applied to tables
examples.spans = function () {
    var doc = new jsPDF('p', 'pt');
    doc.setFontSize(12);
    doc.setTextColor(0);
    doc.setFontStyle('bold');
    doc.text('Col and row span', 40, 50);
    var data = getData(20);
    data.sort(function (a, b) {
        return parseFloat(b.expenses) - parseFloat(a.expenses);
    });
    doc.autoTable(getColumns(), data, {
        theme: 'grid',
        startY: 60,
        drawRow: function (row, data) {
            // Colspan
            doc.setFontStyle('bold');
            doc.setFontSize(10);
            if (row.index === 0) {
                doc.setTextColor(200, 0, 0);
                doc.rect(data.settings.margin.left, row.y, data.table.width, 20, 'S');
                doc.autoTableText("Priority Group", data.settings.margin.left + data.table.width / 2, row.y + row.height / 2, {
                    halign: 'center',
                    valign: 'middle'
                });
                data.cursor.y += 20;
            } else if (row.index === 5) {
                doc.rect(data.settings.margin.left, row.y, data.table.width, 20, 'S');
                doc.autoTableText("Other Groups", data.settings.margin.left + data.table.width / 2, row.y + row.height / 2, {
                    halign: 'center',
                    valign: 'middle'
                });
                data.cursor.y += 20;
            }
        },
        drawCell: function (cell, data) {
            // Rowspan
            if (data.column.dataKey === 'id') {
                if (data.row.index % 5 === 0) {
                    doc.rect(cell.x, cell.y, data.table.width, cell.height * 5, 'S');
                    doc.autoTableText(data.row.index / 5 + 1 + '', cell.x + cell.width / 2, cell.y + cell.height * 5 / 2, {
                        halign: 'center',
                        valign: 'middle'
                    });
                }
                return false;
            }
        }
    });
    return doc;
};

/*
 |--------------------------------------------------------------------------
 | Below is some helper functions for the examples
 |--------------------------------------------------------------------------
 */

// Returns a new array each time to avoid pointer issues
var getColumns = function () {
    return [
        {title: "ID", dataKey: "id"},
        {title: "Name", dataKey: "first_name"},
        {title: "Email", dataKey: "email"},
        {title: "City", dataKey: "city"},
        {title: "Country", dataKey: "country"},
        {title: "Expenses", dataKey: "expenses"}
    ];
};

// Uses the faker.js library to get random data.
function getData(rowCount) {
    rowCount = rowCount || 4;
    var sentence = faker.lorem.words(12);
    var data = [];
    for (var j = 1; j <= rowCount; j++) {
        data.push({
            id: j,
            first_name: faker.name.findName(),
            email: faker.internet.email(),
            country: faker.address.country(),
            city: faker.address.city(),
            expenses: faker.finance.amount(),
            text: shuffleSentence(sentence),
            text2: shuffleSentence(sentence)
        });
    }
    return data;
}

function shuffleSentence(words) {
    words = words || faker.lorem.words(8);
    var str = faker.helpers.shuffle(words).join(' ').trim();
    return str.charAt(0).toUpperCase() + str.slice(1);
}

// Use http://dopiaza.org/tools/datauri or similar service to convert an image into image data
var headerImgData = 'data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIAAD/4g';
References:
https://simonbengtsson.github.io/jsPDF-AutoTable/#custom
https://github.com/simonbengtsson/jsPDF-AutoTable/tree/master/examples