[Print Format] inner table in table

you can make such format using below bootstrap code.
    <table class="table table-condensed table-hover table-bordered">
            <tr>
                <th colspan="3" class="text-center">col one</th>
                <th colspan="4" class="text-center">col 2</th>
                <th colspan="3" class="text-center">col 3</th>
            </tr>

            <tr>
                <td colspan="3" style="width: 33%;"></td>
                <td colspan="4" style="width: 34%;"></td>
                <td colspan="3" style="width: 33%;"></td>
            </tr>
            <tr>
                <td colspan="10" class="text-center"></td>
            </tr>
            <tr>
                <td style="width: 10%;">1</td>
                <td style="width: 10%;">2</td>
                <td style="width: 10%;">3</td>
                <td style="width: 10%;">4</td>
                <td style="width: 10%;">5</td>
                <td style="width: 10%;">6</td>
                <td style="width: 10%;">7</td>
                <td style="width: 10%;">8</td>
                <td style="width: 10%;">9</td>
                <td style="width: 10%;">0</td>
            </tr>
    </table>

[ERPNext] display value in html field

html field will be vaery useful if you want to show information to user.
This information is not stored into table, this is for only display purpose

Code:

frappe.ui.form.on("Student", "onload", function(frm,doctype,name) {

    $(cur_frm.fields_dict.my_html.wrapper).html("Student Info");
});

Here myhtml is html field added to Student doctype
Onload form, html field will be show "Student Info"

Find duplicate records in SQL

Here are some example to find duplicate records.
It will be very helpful if you want to add new unique validation, at that time you need to remove/rename duplicate records.


SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

select name, count(sample_id)
from `tabJob Card Creation`
GROUP BY sample_id
HAVING (count(sample_id)>1);

select name, count(customer_name)
from `tabCustomer`
GROUP BY customer_name
HAVING (count(customer_name)>1);



order by specific values in MYSQL

 We use order by to get sorted result in SQL.

But when we need specific order, then we can use this solution.


select name,job_card_status,
      CASE job_card_status
      WHEN 'Not Created' THEN 1
      WHEN 'Created' THEN 2
      WHEN 'Submitted' THEN 3
      ELSE 5
   END as id
   from `tabSample Entry Register` order by id

Sorted record based on two column:
Here id is ascending and name is in descending, so we will get latest Sample Entry with job not created status  

select name,job_card_status,
      CASE job_card_status
      WHEN 'Not Created' THEN 1
      WHEN 'Created' THEN 2
      WHEN 'Submitted' THEN 3
      ELSE 5
   END as id
   from `tabSample Entry Register` order by id, name desc



Slick Grid Link Formater

You can add URL links in slick grid report
Code:

example:
  {id: "sample_id", name: "Sample Id", field: "sampleid", minWidth:120,
   formatter: linkFormatter = function ( row, cell, value, columnDef, dataContext ) {
                            return '<a href="desk#Form/Sample Entry Register/' + dataContext['sampleid'] + '">' + value + '</a>';
    }
   }

full example:

columns.push(
                {id: "id", name: "Sr.No", field: "id", minWidth:5},
                {id: "sample_id", name: "Sample Id", field: "sampleid", minWidth:120,
                    formatter: linkFormatter = function ( row, cell, value, columnDef, dataContext ) {
                            return '<a href="desk#Form/Sample Entry Register/' + dataContext['sampleid'] + '">' + value + '</a>';
                        }},
                {id: "customer", name: "Customer", field: "customer",minWidth:200,
                      formatter: linkFormatter = function ( row, cell, value, columnDef, dataContext ) {
                                return '<a href="desk#Form/Customer/' + dataContext['customer'] + '">' + value + '</a>';
                            }},
                {id: "type", name: "Type", field: "type",minWidth:120},
                {id: "priority", name: "Priority", field: "priority",minWidth:120},
                {id: "standard", name: "Standard", field: "standard",minWidth:120}
                   );


Slick Grid filter with select row

without using filter, to select row


        var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
          selectedData.push(grid.getData()[value]);
        });



with filter code will be changed 


  var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
        selectedData.push(grid.getDataItem(value));
        });




Full Code:


frappe.provide('frappe.pages');
frappe.provide('frappe.views');
frappe.provide('sample_register');
frappe.require("assets/frappe/js/lib/slickgrid/slick.grid.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.grid.css");
frappe.require("assets/frappe/js/lib/slickgrid/slick.core.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.editors.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.formatters.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.checkboxselectcolumn.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.autotooltips.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellrangedecorator.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellrangeselector.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellexternalcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellselectionmodel.js");


frappe.pages['jobcard'].on_page_load = function(wrapper) {
    var page = frappe.ui.make_app_page({
        parent: wrapper,
        title: 'Job Card Creation',
        single_column: true
    });
    var options = {
        doctype: "Sample Entry Register",
        parent: page
    };
    $("<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:100%;height:500px;''></div>\
    </td>\
  </tr>\
</table>").appendTo($(wrapper).find('.layout-main-section'));
    setTimeout(function(){
        new new sample_register.JobCard(options, wrapper, page);   
    }, 1)
    frappe.breadcrumbs.add("Sample Register");

}

sample_register.JobCard = Class.extend({
    init: function(opts, wrapper,page) {
        $.extend(this, opts);
        this.make_filters(wrapper);
        this.prepare_data();
            this.page.main.find(".page").css({"padding-top": "0px"});
    //this.page.add_menu_item(__("Create Job"), function() {this.create_job();    }, true);
    },
    make_fun: function(){
            this.page.set_title(__("Dashboard") + " - " + __("Job Card Creation"));

     },
    make: function(){
        this._super();
        this.make_fun();
    },
    make_filters: function(wrapper){
        var me = this;
        this.page = wrapper.page;
        this.page.set_primary_action(__("Create Job Card"),
            function() { me.refresh(); }, "icon-refresh")
        this.page.add_menu_item(__("Set Priority"), function() {me.set_priority_data();    }, true);
        this.page.add_menu_item(__("Set Standard"), function() {me.set_standards_data();    }, true);
        this.page.add_menu_item(__("Set Priority & Standard"), function() {me.set_sample_data();    }, true);
        this.page.add_menu_item(__("Refresh"), function() { location.reload(); }, true);


     create_job: function(){
        frappe.msgprint("Creating job in JobCard")
     },

    filters: [

        //{fieldtype:"Link", label: __("Sample Entry Register"),options:"Sample Entry Register"}
    ],

    setup_columns: function() {
        var std_columns = [];

    },
    check_formatter: function(row, cell, value, columnDef, dataContext) {
        return repl('<input type="checkbox" data-id="%(id)s" \
            class="plot-check" %(checked)s>', {
                "id": dataContext.id,
                "checked": dataContext.checked ? 'checked="checked"' : ""
            })
    },
    set_sample_data: function(){
        //sample data entry start
        var me = this;
        var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
        selectedData.push(grid.getDataItem(value));
        });
         var d = frappe.prompt([
        {label:__("Priority"), fieldtype:"Select",options: ["1-Emergency","2-Urgent", "3-Normal"],fieldname:"priority",'reqd': 1},
        {fieldtype: "Column Break"},
        {label:__("Standards"), fieldtype:"Link",options: "Standard",fieldname:"standards", 'reqd': 1},
        {fieldtype: "Section Break"},
        {'fieldname': 'test', 'fieldtype': 'HTML',options: "", 'label': 'test', 'reqd': 0},
        ],
        function(values){
            var c = d.get_values();
            var me = this;
             frappe.call({
                    method: "sample_register.sample_register.page.jobboard.jobboard.set_sample_data",
                     args: {
                         "priority": c.priority,
                         "standards": c.standards,
                         "selectedData":selectedData
                     },   
                    callback: function(r) {
                        location.reload();                }
                });

        },
        'Select Test',
        'Submit'
        );
        //sample data entry end
    },
    //set priority
    set_priority_data: function(){
        var me = this;
        var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
        selectedData.push(grid.getDataItem(value));
        });
        var d = frappe.prompt([
        {label:__("Priority"), fieldtype:"Select",options: ["1-Emergency","2-Urgent", "3-Normal"],fieldname:"priority",'reqd': 1},            ],
        function(values){
            var c = d.get_values();
            var me = this;
             frappe.call({
                    method: "sample_register.sample_register.page.jobboard.jobboard.set_priority_data",
                     args: {
                         "priority": c.priority,
                         "selectedData":selectedData
                     },   
                    callback: function(r) {
                        location.reload();                }
                });

        },
        'Select Test',
        'Submit'
        );
    },
    //set priority end

    //set standards
    set_standards_data: function(){
        var me = this;
        var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
          selectedData.push(grid.getData()[value]);
        });
        var d = frappe.prompt([
        {label:__("Standards"), fieldtype:"Link",options: "Standard",fieldname:"standards", 'reqd': 1},
            ],
        function(values){
            var c = d.get_values();
            var me = this;
             frappe.call({
                    method: "sample_register.sample_register.page.jobboard.jobboard.set_standards_data",
                     args: {
                         "standards": c.standards,
                         "selectedData":selectedData
                     },   
                    callback: function(r) {
                        location.reload();                }
                });

        },
        'Select Test',
        'Submit'
        );
    },
    //set standards end
    refresh: function(){
        var me = this;
        var selectedData = [],
        selectedIndexes;
        selectedIndexes = grid.getSelectedRows();
        jQuery.each(selectedIndexes, function (index, value) {
        selectedData.push(grid.getDataItem(value));
        });

         // frappe prompt box code
         var d = new frappe.prompt([
            {label:__("Test Group"), fieldtype:"Link",
                            options: "Test Group",
                            fieldname:"test_group"},
            {fieldtype: "Column Break"},
            {'fieldname': 'select_test', 'fieldtype': 'HTML',options: "Select Test Group<br>", 'label': 'Select Test', 'reqd': 0},
            {fieldtype: "Section Break"},
           // {'fieldname': 'comment', 'fieldtype': 'Text', 'label': 'Selected Test', 'reqd': 1},
            {'fieldname': 'test', 'fieldtype': 'HTML', 'label': 'test', 'reqd': 0},
            {fieldtype: "Section Break"},
          //  {'fieldtype': 'Button',    'label': __('Add')},
            ],
            function(values){
                var c = d.get_values();
                var me = this;
                var test_list = [];
                $(".frappe-control input:checkbox:checked").each ( function() {
                    test_list.push($(this).val());
                });

         frappe.call({
                method: "sample_register.sample_register.page.jobboard.jobboard.create_job_card_1",
                 args: {
                     "test_group": c.test_group,
                     "selectedData":selectedData,
                     "test_list_unicode":test_list
                 },   
                callback: function(r) {
                if (cur_frm) {
                            cur_frm.reload_doc();
                        }
                }
            });
    },
    'Select Test',
    'Submit'
    );
        d.get_input("test_group").on("change", function() {
        var test_group = d.get_value("test_group");
         frappe.call({
            method: "sample_register.sample_register.page.jobboard.jobboard.get_test_data",
            type: "GET",
            args: {
                "test_group": test_group
            },
            callback: function(r){
                if(r.message){
                    me.test_data = r.message;
                    $('.frappe-control input:checkbox').removeAttr('checked');

                    html=""
                    html += '<div class="testCont"  style="max-height: 200px;overflow: auto;overflow-x: hidden;min-height:150px">'
                    for (var i = 0; i<r.message.get_test_data.length; i=i+2) {
                        // html += "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[i][0]+"' value='"+r.message.get_test_data[i][0]+"'>"+r.message.get_test_data[i][0]+"<br>"
                        html += "<div class='row'>  <div class='col-sm-6'>"
                        html += "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[i][0]+"' value='"+r.message.get_test_data[i][0]+"'>"+r.message.get_test_data[i][0]+ "</div>"
                        html +=     "<div class='col-sm-6'>"
                        if(r.message.get_test_data[(i + 1)]){
                            j=i+1;
                            html +=     "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[j][0]+"' value='"+r.message.get_test_data[j][0]+"'>"+r.message.get_test_data[j][0]+ "</div> </div>"
                        }
                    }
                   html += '</div>'   
                      var wrapper = d.fields_dict.test.$wrapper;
                      wrapper.empty();
                    wrapper.html(html);

                 selected_sample_html="<p>Selected sample to perform Test: </p>"
                  for(r in selectedData){
                   selected_sample_html+="<p>"+selectedData[r]["sampleid"]+"</p>"
                }

                var wrapper_sample = d.fields_dict.select_test.$wrapper;
                wrapper_sample.html(selected_sample_html);
                }
            }
        });
        return false;
    });

    },

    prepare_data: function() {
        var me = this;
    //slick start
        function requiredFieldValidator(value) {
            if (value == null || value == undefined || !value.length) {
                return {valid: false, msg: "This is a required field"};
            } else {
                return {valid: true, msg: null};
            }
        }
        var columns = [];
          var options = {
            enableCellNavigation: true,
            enableColumnReorder: false,
            showHeaderRow: true,
            headerRowHeight: 30,
            explicitInitialization: true, //shoud be true
            multiColumnSort: true,
          };
          var columnFilters = {};

            function filter(item) {
            for (var columnId in columnFilters) {
              if (columnId !== undefined && columnFilters[columnId] !== "") {
                var c = grid.getColumns()[grid.getColumnIndex(columnId)];
                if (item[c.field] != columnFilters[columnId]) {
                  return false;
                }
              }
            }
            return true;
          }

        var grid;
          var data=[];
             frappe.call({
                method: "sample_register.sample_register.page.jobboard.jobboard.get_sample_data",
                type: "GET",
                args: {
                    args:{

                    }
                },
                callback: function(r){
                    if(r.message){
                        me.data = r.message;
                        me.make_grid(r.message,columns,options)
                        //me.waiting.toggle(false);

                    }
                }
            });
    },

    //function split to make new grid from frappe.call
    make_grid:function(data1,columns,options){

            $(function () {
            var data = [];

            for (var i = 0; i<data1.get_sample_data.length; i++) {
              data[i] = {
                  id: i,
                  checked:true,
                sampleid: data1.get_sample_data[i][1],
                customer: data1.get_sample_data[i][2],
                type: data1.get_sample_data[i][3],
                priority: data1.get_sample_data[i][4],
                standard: data1.get_sample_data[i][5],
                test_group: data1.get_sample_data[i][6]
              };
            }
            grid = new Slick.Grid("#myGrid", data, columns, options);
           
                var checkboxSelector = new Slick.CheckboxSelectColumn({
                  cssClass: "slick-cell-checkboxsel"
                    });
                columns.push(checkboxSelector.getColumnDefinition());
                  columns.push(
    {id: "sample_id", name: "Sample Id", field: "sampleid", minWidth:120},
    {id: "customer", name: "Customer", field: "customer",minWidth:200},
        {id: "id", name: "id", field: "id", minWidth:120},

    {id: "type", name: "Type", field: "type",minWidth:120},
    {id: "priority", name: "Priority", field: "priority",minWidth:120},
    {id: "standard", name: "Standard", field: "standard",minWidth:120}
                   );

  var columnFilters = {};

            dataView = new Slick.Data.DataView();

               grid = new Slick.Grid("#myGrid", dataView, columns, options);

         function filter(item) {
    for (var columnId in columnFilters) {
      if (columnId !== undefined && columnFilters[columnId] !== "") {
        var c = grid.getColumns()[grid.getColumnIndex(columnId)];
        if (item[c.field] != columnFilters[columnId]) {
          return false;
        }
      }
    }
    return true;
  }

    dataView.onRowCountChanged.subscribe(function (e, args) {
      grid.updateRowCount();
      grid.render();
    });
    dataView.onRowsChanged.subscribe(function (e, args) {
      grid.invalidateRows(args.rows);
      grid.render();
    });
    $(grid.getHeaderRow()).delegate(":input", "change keyup", function (e) {
      var columnId = $(this).data("columnId");
      if (columnId != null) {
        columnFilters[columnId] = $.trim($(this).val());
        dataView.refresh();
      }
    });
    grid.onHeaderRowCellRendered.subscribe(function(e, args) {
        $(args.node).empty();
        $("<input type='text'>")
           .data("columnId", args.column.id)
           .val(columnFilters[args.column.id])
           .appendTo(args.node);
    });

            grid.setSelectionModel(new Slick.RowSelectionModel({selectActiveRow: false}));
            grid.registerPlugin(checkboxSelector);
            grid.init();

            dataView.beginUpdate();

            dataView.setItems(data);

            dataView.setFilter(filter);

            dataView.endUpdate();

                           var columnpicker = new Slick.Controls.ColumnPicker(columns, grid, options);

          })


    },
});



reference:
http://stackoverflow.com/questions/7944325/get-data-of-selected-rows-in-slickgrid



Change Slick Grid column width

           
You can use minwidth property to change column width
   var checkboxSelector = new Slick.CheckboxSelectColumn({
                  cssClass: "slick-cell-checkboxsel"
                    });
                columns.push(checkboxSelector.getColumnDefinition());
                  columns.push(
    {id: "sample_id", name: "Sample Id", field: "sampleid", minWidth:120},
    {id: "customer", name: "Customer", field: "customer",minWidth:200},
    {id: "type", name: "Type", field: "type",minWidth:120},
    {id: "priority", name: "Priority", field: "priority",minWidth:120},
    {id: "standard", name: "Standard", field: "standard",minWidth:120}
    // {id: "test_group", name: "Test Group", field: "test_group",minWidth:120}
                   );

 for more option see:  https://github.com/mleibman/SlickGrid/wiki/Column-Options

Bootstrap div vertical scrollbar and dynamic checkbox

1) Dynamicaly add CheckBox into bootstrap column with scrollbar

                    html=""
                    html += '<div class="testCont"  style="max-height: 200px;overflow: auto;overflow-x: hidden;min-height:150px">'
                    for (var i = 0; i<r.message.get_test_data.length; i=i+2) {
                        // html += "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[i][0]+"' value='"+r.message.get_test_data[i][0]+"'>"+r.message.get_test_data[i][0]+"<br>"
                        html += "<div class='row'>  <div class='col-sm-6'>"
                        html += "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[i][0]+"' value='"+r.message.get_test_data[i][0]+"'>"+r.message.get_test_data[i][0]+ "</div>"
                        html +=     "<div class='col-sm-6'>"
                        if(r.message.get_test_data[(i + 1)]){
                            j=i+1;
                            html +=     "<input type='checkbox' class='select' id='_select' name='"+r.message.get_test_data[j][0]+"' value='"+r.message.get_test_data[j][0]+"'>"+r.message.get_test_data[j][0]+ "</div> </div>"
                        }
                    }
2)  Some Property of bootstrap scrollbar
    To show scrollbar use overflow: true;
    this will show both horizontal and vertical scrollbar
    <div class="testDiv"  style="max-height: 200px;overflow: auto;">
    </div>
  
   To show only vertical scrollbar use overflow-x: hidden
    <div class="testDiv"  style="max-height: 200px;overflow: auto;overflow-x: hidden;min-height:150px">
    </div>

display check-box dynamicaly

python code
@frappe.whitelist()
def get_test_data(test_group):
    return {
    "get_test_data": frappe.db.sql("""select name from `tabTest Name` where test_group='%s' order by name"""%(test_group),debug=1, as_list=1)
    }  

JavaScript Cod:
we can use $each or for loop to print check box dynamically


frappe.call({
            method: "sample_register.sample_register.page.jobboard.jobboard.get_test_data",
            type: "GET",
            args: {
                "test_group": "Test Group 2"
            },
            callback: function(r){
                if(r.message){
                    me.test_data = r.message;

                    //$each to apend data, here test is html field
                $.each(r.message, function(idx, val){
                     $("<input type='checkbox' name='"+val[0]+"' value='Bike'>"+val[0]+"<br>").appendTo(d.fields_dict.test.wrapper);


                });
                //for loop to apend data
                    for (var i = 0; i<r.message.get_test_data.length; i++) {
                        msgprint(r.message.get_test_data[i][0]);
                        $("<input type='checkbox' name='"+r.message.get_test_data[i][0]+"' value='Bike'>"+r.message.get_test_data[i][0]+"<br>").appendTo(d.fields_dict.test.wrapper);

                }
            }
        }); 

 manual way
 $("<div style='min-height: 200px'><p>Select Test which you want to perform</p>\
        <input type='checkbox' name='vehicle' value='Bike'>Test One<br>\
        <input type='checkbox' name='car' value='Bike'>Test Two<br>\
        <input type='checkbox' name='bike' value='Car'>Test Three<br>\
         ").appendTo(d.fields_dict.test.wrapper);

frappe page to make query report with selectable row

Create report page with
frappe.provide('frappe.pages');
frappe.provide('frappe.views');
frappe.provide('sample_register');
frappe.require("assets/frappe/js/lib/slickgrid/slick.grid.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.grid.css");
frappe.require("assets/frappe/js/lib/slickgrid/slick.core.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.editors.js");
frappe.require("assets/frappe/js/lib/slickgrid/slick.formatters.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.checkboxselectcolumn.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.autotooltips.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellrangedecorator.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellrangeselector.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellexternalcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellselectionmodel.js");

var cur_page = null;
frappe.pages['jobboard'].on_page_load = function(wrapper) {
    var page = frappe.ui.make_app_page({
        parent: wrapper,
        title: 'Job Card Creation',
        single_column: true
    });
    var options = {
        doctype: "Sample Entry Register",
        parent: page
    };
    $("<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:600px;height:500px;''></div>\
    </td>\
  </tr>\
</table>").appendTo($(wrapper).find('.layout-main-section'));
    setTimeout(function(){
        new new sample_register.JobCard(options, wrapper, page);   
    }, 1)
    frappe.breadcrumbs.add("Sample Register");

}

sample_register.JobCard = Class.extend({
    init: function(opts, wrapper,page) {
        $.extend(this, opts);
        this.make_filters(wrapper);
        this.prepare_data();
            this.page.main.find(".page").css({"padding-top": "0px"});
    //this.page.add_menu_item(__("Create Job"), function() {this.create_job();    }, true);
    },
    make_fun: function(){
            this.page.set_title(__("Dashboard") + " - " + __("Job Card Creation"));

     },
    make: function(){
        this._super();
        this.make_fun();
    },
    make_filters: function(wrapper){
        var me = this;
        this.page = wrapper.page;

        this.page.set_primary_action(__("Refresh"),
            function() { me.refresh(); }, "icon-refresh")

        this.department = this.page.add_field({fieldtype:"Link", label:"Sample Entry Register",
            fieldname:"sample_entry_register", options:"Sample Entry Register"});
    },
    create_job: function(){
        frappe.msgprint("Creating job in JobCard")
     },

    filters: [

        //{fieldtype:"Link", label: __("Sample Entry Register"),options:"Sample Entry Register"}
    ],

    setup_columns: function() {
        var std_columns = [
  //  {id: "check", name: "Check", field: "_check", width: 30, formatter: this.check_formatter},
   // {id: "sample_id", name: "Sample Id", field: "sampleid"},
    //{id: "customer", name: "Customer", field: "customer"},
    //{id: "type", name: "Type", field: "type"},
    //{id: "priority", name: "Priority", field: "priority"},
    //{id: "standard", name: "Standard", field: "standard"},
    //{id: "test_group", name: "Test Group", field: "test_group"}

        ];
        //this.make_date_range_columns();
        //this.columns = std_columns;
    },
    check_formatter: function(row, cell, value, columnDef, dataContext) {
        return repl('<input type="checkbox" data-id="%(id)s" \
            class="plot-check" %(checked)s>', {
                "id": dataContext.id,
                "checked": dataContext.checked ? 'checked="checked"' : ""
            })
    },
    refresh: function(){
        //this.check_mandatory_fields()
        var me = this;
        //this.waiting.toggle(true);
        msgprint("refresh clicked");
        msgprint(this.page.fields_dict.sample_entry_register.get_parsed_value());
        //msgprint(grid);
        //test selection
//t3 start
            var selectedData = [],
                selectedIndexes;

            selectedIndexes = grid.getSelectedRows();
            jQuery.each(selectedIndexes, function (index, value) {
              selectedData.push(grid.getData()[value]);
            });
            msgprint(selectedData);  //selected data contains row data of currently selected checkbox
//t3 end
            var rows = grid.getData();
            //msgprint(rows[0]["sampleid"]);
           // msgprint(rows[1]["sampleid"]);
            msgprint(rows[2]["sampleid"]);

        for (r in rows) {
            var row = rows[r]
            for (i = 1; i < 4; ++i) {
                msgprint(rows[r][i]);
            }
        }
           
        //test selection

    },

    prepare_data: function() {
        // add Opening, Closing, Totals rows
        // if filtered by account and / or voucher
        var me = this;
    //slick start
            function requiredFieldValidator(value) {
                if (value == null || value == undefined || !value.length) {
                    return {valid: false, msg: "This is a required field"};
                } else {
                    return {valid: true, msg: null};
                }
            }
    var sam="sam";
    var columns = [
    //{id: "check", name: "Check", field: "check", width: 30, formatter: this.check_formatter},
    //{id: "sample_id", name: "Sample Id", field: "sampleid"},
    //{id: "customer", name: "Customer", field: "customer"},
    //{id: "type", name: "Type", field: "type"},
    //{id: "priority", name: "Priority", field: "priority"},
    //{id: "standard", name: "Standard", field: "standard"},
    //{id: "test_group", name: "Test Group", field: "test_group"}
  ];
  var options = {
    enableCellNavigation: true,
    enableColumnReorder: false
  };

        var grid;
          var data=[];
         frappe.call({
            method: "sample_register.sample_register.page.dashboard.dashboard.get_sample_data",
            type: "GET",
            args: {
                args:{

                }
            },
            callback: function(r){
                if(r.message){
                    me.data = r.message;
                    me.make_grid(r.message,columns,options)
                    //me.waiting.toggle(false);

                }
            }
        });

 //this.wrapper.find('[type="checkbox"]').attr(data-id, '3');
//$(".plot-check").hide()
  //slick end

   

        //this.data = [total_tickets, days_to_close, hours_to_close, hours_to_respond];
    },
    //function split to make new grid from frappe.call
    make_grid:function(data1,columns,options){
            $(function () {
            var data = [];

            for (var i = 0; i<data1.get_sample_data.length; i++) {
              data[i] = {
                  checked:true,
                sampleid: data1.get_sample_data[i][1],
                customer: data1.get_sample_data[i][2],
                type: data1.get_sample_data[i][3],
                priority: 1,
                standard: "1",
                test_group: 1
              };
            }
            grid = new Slick.Grid("#myGrid", data, columns, options);
           
                var checkboxSelector = new Slick.CheckboxSelectColumn({
                  cssClass: "slick-cell-checkboxsel"
                    });
                columns.push(checkboxSelector.getColumnDefinition());
                  columns.push(
    {id: "sample_id", name: "Sample Id", field: "sampleid"},
    {id: "customer", name: "Customer", field: "customer"},
    {id: "type", name: "Type", field: "type"},
    {id: "priority", name: "Priority", field: "priority"},
    {id: "standard", name: "Standard", field: "standard"},
    {id: "test_group", name: "Test Group", field: "test_group"}
                   );

            grid = new Slick.Grid("#myGrid", data, columns, options);   
            grid.setSelectionModel(new Slick.RowSelectionModel({selectActiveRow: false}));
            grid.registerPlugin(checkboxSelector);

            var columnpicker = new Slick.Controls.ColumnPicker(columns, grid, options);


          })


    },
    //new grid end frappe.call
});


working with frappe page

creating own report and add filter access fields on page

sample code

js code
frappe.provide('frappe.pages');
frappe.provide('frappe.views');
frappe.provide('sample_register');

var cur_page = null;
frappe.pages['jobcard'].on_page_load = function(wrapper) {
    var page = frappe.ui.make_app_page({
        parent: wrapper,
        title: 'Job Card Creation',
        single_column: true
    });
    var options = {
        doctype: "Sample",
        parent: page
    };
    $("<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:600px;height:500px;''></div>\
    </td>\
  </tr>\
</table>").appendTo($(wrapper).find('.layout-main-section'));
    setTimeout(function(){
        new new sample_register.JobCard(options, wrapper, page);   
    }, 1)
    frappe.breadcrumbs.add("Sample Register");

}

sample_register.JobCard = Class.extend({
    init: function(opts, wrapper,page) {
        $.extend(this, opts);
        this.make_filters(wrapper);
        this.prepare_data();
            this.page.main.find(".page").css({"padding-top": "0px"});
    //this.page.add_menu_item(__("Create Job"), function() {this.create_job();    }, true);
    },
    make_fun: function(){
            this.page.set_title(__("Dashboard") + " - " + __("Job Card Creation"));

     },
    make: function(){
        this._super();
        this.make_fun();
    },
    make_filters: function(wrapper){
        var me = this;
        this.page = wrapper.page;

        this.page.set_primary_action(__("Refresh"),
            function() { me.refresh(); }, "icon-refresh")

        this.department = this.page.add_field({fieldtype:"Link", label:"Sample Entry Register",
            fieldname:"sample_entry_register", options:"Sample"});
    },
    create_job: function(){
        frappe.msgprint("Creating job in JobCard")
     },

    filters: [

        //{fieldtype:"Link", label: __("Sample"),options:"Sample Entry Register"}
    ],

    setup_columns: function() {
        var std_columns = [
    {id: "check", name: "Check", field: "_check", width: 30, formatter: this.check_formatter},
    {id: "sample_id", name: "Sample Id", field: "sampleid"},
    {id: "customer", name: "Customer", field: "customer"},
    {id: "type", name: "Type", field: "type"},
    {id: "priority", name: "Priority", field: "priority"},
    {id: "standard", name: "Standard", field: "standard"},
    {id: "test_group", name: "Test Group", field: "test_group"}

        ];
        //this.make_date_range_columns();
        //this.columns = std_columns;
    },
    check_formatter: function(row, cell, value, columnDef, dataContext) {
        return repl('<input type="checkbox" data-id="%(id)s" \
            class="plot-check" %(checked)s>', {
                "id": dataContext.id,
                "checked": dataContext.checked ? 'checked="checked"' : ""
            })
    },
    refresh: function(){
        //this.check_mandatory_fields()
        var me = this;
        //this.waiting.toggle(true);
        msgprint("refresh clicked");
        msgprint(this.page.fields_dict.sample_entry_register.get_parsed_value())

    },

    prepare_data: function() {
        // add Opening, Closing, Totals rows
        // if filtered by account and / or voucher
        var me = this;
    //slick start
            function requiredFieldValidator(value) {
                if (value == null || value == undefined || !value.length) {
                    return {valid: false, msg: "This is a required field"};
                } else {
                    return {valid: true, msg: null};
                }
            }
    var grid;
    var sam="sam";
    var columns = [
    {id: "check", name: "Check", field: "check", width: 30, formatter: this.check_formatter},
    {id: "sample_id", name: "Sample Id", field: "sampleid"},
    {id: "customer", name: "Customer", field: "customer"},
    {id: "type", name: "Type", field: "type"},
    {id: "priority", name: "Priority", field: "priority"},
    {id: "standard", name: "Standard", field: "standard"},
    {id: "test_group", name: "Test Group", field: "test_group"}
  ];
  var options = {
    enableCellNavigation: true,
    enableColumnReorder: false
  };



          var data=[];
         frappe.call({
            method: "appname.module.page.dashboard.dashboard.get_sample_data",
            type: "GET",
            args: {
                args:{

                }
            },
            callback: function(r){
                if(r.message){
                    me.data = r.message;
                    //me.waiting.toggle(false);
                      $(function () {
                    var data = [];

                    for (var i = 0; i<r.message.get_sample_data.length; i++) {
                      data[i] = {
                          checked:false,
                        sampleid: r.message.get_sample_data[i][1],
                        customer: r.message.get_sample_data[i][2],
                        type: r.message.get_sample_data[i][3],
                        priority: 1,
                        standard: "1",
                        test_group: 1
                      };
                    }
                    grid = new Slick.Grid("#myGrid", data, columns, options);


                  })
                }
            }
        });

 //this.wrapper.find('[type="checkbox"]').attr(data-id, '3');
//$(".plot-check").hide()
  //slick end

   

        //this.data = [total_tickets, days_to_close, hours_to_close, hours_to_respond];
    },



});

python code to fetch data:
@frappe.whitelist()
def get_sample_data():
    return {
    "get_sample_data": frappe.db.sql("""select false, name, customer, type from `tabSample`  order by name""", as_list=1)
    }

Solution similar to Select Serial Number in Delivery Note

Solution similar to Select Serial Number in Delivery Note:

1) create New Doctype
2) add custom button on New Doctype Table
3) write following script
4) it will filter test records for selcted group and you can add this test record into [New Doctype Table].[Field Name] (like serial number)


frappe.ui.form.on("DocType Item", "custom_button", function(frm, cdt, cdn) {
    var test_details = frappe.get_doc(cdt, cdn);
    var grid_row = cur_frm.open_grid_row();
    //if (job.item_code == "Artwork") {
    //  job.job_type="New";
    //  refresh_field("job_type", job.name, job.parentfield);
    //}
    var d = new frappe.ui.Dialog({
        title: __("Add Serial No"),
        fields: [
            {
                "fieldtype": "Link",
                "options": "Test Name",
                "label": __("Test Name"),
                "get_query": function () {
                    return {
                        filters: {
                            test_group:grid_row.doc.test_group
                        }
                    }
                }
            },
            {
                "fieldtype": "Button",
                "label": __("Add")
            }
        ]
    });

    d.get_input("add").on("click", function() {
        var test_name = d.get_value("test_name");
        if(test_name) {
            var val = (grid_row.doc.test || "").split("\n").concat([test_name]).join("\n");
            grid_row.fields_dict.test.set_model_value(val.trim());
        }
        d.hide();
        return false;
    });

    d.show();
//select test finish
})

check user role from python and client side script

To check user role from client side

@frappe.whitelist(allow_guest=True)
def validate_role(self, method):
        user="kolate.sambhaji@gmail.com"
        frappe.msgprint(_("In role validate function"))
        role_list = frappe.db.sql("""select name,owner,parent,idx,role from tabUserRole where parent=%s and role='Blogger'""",
                user)
        if len(role_list) > 0:
            frappe.msgprint("user is blogger")
        frappe.msgprint(role_list) #this will test user role blogger

This will print  -1 if user role Account Manager is not present
If user role Account Manager is present, then first msgprint will print value greater than one

To check user role from python
1) frappe.get_roles() will give you all roles of current  user

2) to check other user role
You can use following(python) code to check particular user roles.

@frappe.whitelist(allow_guest=True)
def validate_role(self, method):
        user="kolate.sambhaji@gmail.com"
        frappe.msgprint(_("In role validate function"))
        role_list = frappe.db.sql("""select name,owner,parent,idx,role from tabUserRole where parent=%s and role='Blogger'""",
                user)
        frappe.msgprint(role_list) #this will test user role blogger

Above script will check user role for 'kolate.sambhaji@gmail.com', you can pass any user id to check its roles.

display current logged in user name and data in ERPNext/Frappe Print Format

<!-- TO print current logged in user email id -->
<div class="row">
    <div class="col-xs-5 text-right"><big><b>Print By(email id)</b>  </big></div>
    <div class="col-xs-7 "><big>{{ frappe.user }} </big> </div>
</div> 

<!-- TO print current logged in user fist name -->
{% set u = frappe.get_doc("User", frappe.user) %} 
<div class="row">
    <div class="col-xs-5 text-right"><big><b>Print By(user name)</b>  </big></div>
    <div class="col-xs-7 "><big>{{ u.first_name }} </big> </div>
</div>

hiding fields based on some condition

To hide fields on form

frappe.ui.form.on("Quotation", "onload", function(frm) {
       cur_frm.set_df_property("shipping_rule", "hidden",true);
       msgprint("hi");
});


To hide fields in child table:

frappe.ui.form.on("Quotation", "onload", function(frm) {
       //cur_frm.set_df_property("shipping_rule", "hidden",true);
       cur_frm.fields_dict["items"].grid.set_column_disp('description', false)
       msgprint("hi");
});


TO held fileds in table, you can use eval property of field
eval:doc.item_code == "sample item"
eval:doc.item_code.substr(0,3)== "720"
 

Substring in javascript

Substring in Javascript:

The substr() method returns the characters in a string beginning at the specified location through the specified number of characters.

Syntax

str.substr(start[, length])

Frappe Example

frappe.ui.form.on("Quotation", "validate", function(frm) {
       var a = frm.doc.customer;
       a= a.substr(0,3);
        msgprint(a);
});

Above script returns first three letter  of customer name.


reference:
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/substr
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/slice

Comparing date in frappe or js

I want to check given date1 is Today or not
I used 

var date2 =get_today();
var result = (date1 == date2); 

It works successfully.


After that, I want to check, given date1 is Today or Yesterday or The day before yesterday. I used <= operator, but its not working properly.
After searching I come up with this solution, before comparing date we need to parse it.
Solution is:

var a = Date.parse(dataContext.ETD);

var b =  Date.parse(get_today());

var d = (a - b)/(1000 * 3600 * 24);

var c = ((d==0)||(d==(-1))||(d==(-2))); //returns true if ETD is today, yesterday or the day before yesterday

 

Please write comment if you have any doubt or suggestion. 

Ability to Color cells based on cell value Reports

Here is my code,
You can put this code in js file of report.
Code:

"formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
                    value = default_formatter(row, cell, value, columnDef, dataContext);
            if (columnDef.id == "Debit") {
                                   if(dataContext.Debit>1){
                                    value = "<span style='color:blue;font-weight:bold'>" + value + "</span>";
                                    }
                        msgprint(dataContext.Debit)

            }


            if (dataContext.Debit) {
            }

            return value;
        }

fetch address using frappe.call

frappe.ui.form.on("Documents Required", "buyer", function(frm, cdt, cdn) {
    var d = frappe.get_doc(cdt, cdn);
    msgprint("hi from ui");
        if(d.buyer) {
        return frm.call({
            method: "erpnext.utilities.doctype.address.address.get_address_display",
            child: d,
            args: {
                "address_dict":  d.buyer
            },
                        callback: function(r) {
            if(r.message)
                                    msgprint("hi from callback"+r.message);
                                frappe.model.set_value(cdt, cdn, "buyer_address", r.message);
        }
        });
     }
})

Script to filter address in child table and document

# Script to filter records in child table

cur_frm.set_query("buyer", "documents_required", function(doc) {
            if(doc.customer) return {filters: { 'address_type': 'Buyer', customer: doc.customer } };
        });


#script to filter records in document

cur_frm.set_query('buyer', function (doc) {
    if(doc.customer){
        return {
            filters: {
                'address_type': 'Buyer',
                'customer' : doc.customer
            }
        }
    }
    else {
        return {
        filters: {
                'address_type': 'Empty',
            }
        }
    }
});

remove words from string in Jinjha

This post help you to remove selected word from string.
This is also useful to replace word in string.

I have used this feature to remove Currency and float precision in ERPNext print format
e.g 1) to remove USD and 'only' form string
{{ doc.total_amount_in_words | replace("USD", "") | replace("only.", "") }}

e.g. 2) to remove float precision
{{ doc.get_formatted("net_total_export", doc) | replace(".00", "") }}<br>

e.g. 3) apply multiple formats
{{ '{:20,.0f}'.format(55.258) }}

How to write Client Side and Server Side Script in Frappe

Lets see, we have one requirement.
In Purchase Invoice, We need to add Payment Due Date. Payment Due Date is equal to Posting Date + Credit Days.
Also one more requirement, Supplier Invoice No should be unique in all Purchase Invoice.
Steps to achieve this. 1)  Create custom field ‘Credit Days’ and ‘Due Date’ in Purchase Invoice.
To fetch value of Credit Days from supplier into purchase invoice, write this script in Purchase Invoice.

    cur_frm.add_fetch(supplier ,'credit_days','credit_days');

2) To calculate Due Date = Posting Date + Credit Days, write below script in Purchase Invoice.



    frappe.ui.form.on("Purchase Invoice ", " validate ", function(frm) {
        var nos = frm.doc.credit_days* 1;
        var ddate = frappe.datetime.add_days(frm.doc.posting_date,nos);
        cur_frm.set_value("due_date", ddate);
    });



Note:
i) doc.field_name will give value of current doc.
ii) cur_frm.set_value("field_name", value) used to set value.

3) To add validation, Supplier Invoice No should be unique in all Purchase Invoice.
(This will explain how to call server side method using hooks.)

In hooks.py write this code

    doc_events = {
        "Purchase Invoice": {
            "validate": "sf_custom_changes.sf_acc.purchase.validate_bill_no"
        }
    }

In purchase.py write this code

    def validate_bill_no(self, method):
            if self.bill_no:
                # validate bill no is unique
                bill_list = frappe.db.sql("""select name from `tabPurchase Invoice` where bill_no=%s and docstatus =1""",
                    self.bill_no)
                if len(bill_list) > 0:
                    items = [e[0] for e in bill_list if e[0]!=self.name]
                    frappe.throw(_("Supplier Invoice Number must be unique. Current Supplier Invoice Number already exists for {0}").format(comma_and(items)))
            if self.bill_date:
                    if getdate(self.bill_date) > getdate(self.posting_date):
                        frappe.throw(_("Supplier Invoice Date cannot be after Purchase Order Date"))


This will help you to write client and server side script. Please ask if you have any doubt in this example.