ERPNext Currency Format

Currency Format in Standard Reply/Print Format:

Grand Total : {{ frappe.db.get_value("Currency", currency, "symbol") }} {{frappe.format_value(grand_total, {"fieldtype":"Currency"}) }}

Currency Format in Javascript:

format_currency(99,currency="INR")

Extend erpnext public js funtion

Extend erpnext public js funtion

Templating in ERPNext:

1) Add your js file in your app`s public and write your code, for over write any function of

2) other app`s public js.

3) add you file path in your app build.json

4) bench build

$.extend(erpnext.utils, {

render_address_and_contact: function(frm) {

// render address

$(frm.fields_dict['address_html'].wrapper)

.html(frappe.render_template("address_list",

cur_frm.doc.__onload))

.find(".btn-address").on("click", function() {

frappe.new_doc("Address");

});

// render contact

if(frm.fields_dict['contact_html']) {

$(frm.fields_dict['contact_html'].wrapper)

.html(frappe.render_template("contact_list",

cur_frm.doc.__onload))

.find(".btn-contact").on("click", function() {

frappe.new_doc("Contact");

}

);

console.log("my file trigger")

}

}

})

Result:
Use this code to change rendering of Address and Contact:

Install wkhtmltopdf (with patched qt) in Ubuntu 14.04 LTS

wget http://download.gna.org/wkhtmltopdf/0.12/0.12.2.1/wkhtmltox-0.12.2.1_linux-trusty-amd64.deb
sudo apt-get update
sudo apt-get install wkhtmltox
sudo apt-get -f install
sudo dpkg -i wkhtmltox-0.12.2.1_linux-trusty-amd64.deb
wkhtmltopdf -V
rm wkhtmltox-0.12.2.1_linux-trusty-amd64.deb

ERPNext Change Field Text Color and Background Color

Hello,

Today I received one email about how to change Text Color in ERP.
Frappe Framework uses jQuery, so if you know jQuery, you can add your custom css using jQuery selectors.
I am giving solution, it will helpful to Highlight fields based on condition. (e.g. Amount should be red is amount is greater than equal to 10)

In below image I have field "Amount"




Code to select Amount field in jquery

$('input[data-fieldname="amount"]')


Code to get value in amount field

$('input[data-fieldname="amount"]').val()


Code to add Text Color Red

$('input[data-fieldname="amount"]').css("color","red")


Code to add css to change background color

$('input[data-fieldname="amount"]').css("background-color","#FFE4C4")


Note: You can write above jquery code in onload function of Frappe Custom Script to execute your code before loading of Form.
Syntax:
frappe.ui.form.on("Doctype Name", "onload", function(frm,cdt,cdn) {
//your code
});


You can email me if you need any help. My email id is kolate.sambhaji@gmail.com

Thanks,
Sambhaji Kolate

ERPNext Change Field Text Color and Background Color

Hello,

Today I received one email about how to change Text Color in ERP.
Frappe Framework uses jQuery, so if you know jQuery, you can add your custom css using jQuery selectors.
I am giving solution, it will helpful to Highlight fields based on condition. (e.g. Amount should be red is amount is greater than equal to 10)

In below image I have field "Amount"




Code to select Amount field in jquery

$('input[data-fieldname="amount"]')


Code to get value in amount field

$('input[data-fieldname="amount"]').val()


Code to add Text Color Red

$('input[data-fieldname="amount"]').css("color","red")


Code to add css to change background color

$('input[data-fieldname="amount"]').css("background-color","#FFE4C4")


Note: You can write above jquery code in onload function of Frappe Custom Script to execute your code before loading of Form.
Syntax:
frappe.ui.form.on("Doctype Name", "onload", function(frm,cdt,cdn) {
//your code
});


You can email me if you need any help. My email id is kolate.sambhaji@gmail.com

Thanks,
Sambhaji Kolate

ERPNext: Color Cells in ERPNext Query Report

We can add color based on condition.

Here we have made cells in row green if Rental Payment greater than 100 else red.

add following code to query_report_name.js file

frappe.query_reports["Payments Received"] = {

"filters": [

{

"fieldname":"customer",

"label": __("Customer"),

"fieldtype": "Link",

"options": "Customer",

"width": "80"

}

],

"formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {

        value = default_formatter(row, cell, value, columnDef, dataContext);

   if (columnDef.id != "Customer" && columnDef.id != "Payment Date" && dataContext["Rental Payment"] < 100) {

            value = "<span style='color:red!important;font-weight:bold'>" + value + "</span>";

   }

   if (columnDef.id != "Customer" && columnDef.id != "Payment Date" && dataContext["Rental Payment"] > 100) {

            value = "<span style='color:green!important;font-weight:bold'>" + value + "</span>";

   }

   return value;

}

}




Thanks to Jitendra for providing images and working code example.

How to add spaces in HTML or Jinjha?

Indent paragraphs with CSS is good way to add spaces in html. CSS paddings or margins give direct display instructions to the browser, so the result is more consistent.

You can easily add spaces even if you don't know css.

Sample code:

<style>p.indent{ padding-left: 1.8em }</style>
<p class="indent"> Sambhaji Kolate </p>

Get currency exchange rate in python

Using below code, you can get currency exchange rate.

>>> import requests

>>> response = requests.get("http://api.fixer.io/latest", params={

...     "base": "USD",

...     "symbols": "INR"

... })

>>> value = response.json()

>>> print value

{u'date': u'2016-06-22', u'base': u'USD', u'rates': {u'INR': 67.496}}

>>> 

Connecting to mysql using python

Connecting to mysql using python

In [1]:  import MySQLdb


#MySQLdb.connect("localhost","username","password","dbname")
In [2]: db = MySQLdb.connect("localhost","root","hPMkXT1vU7rh","erpdatabase")

In [3]:  cursor = db.cursor()

In [4]: cursor.execute("SELECT VERSION()")
Out[4]: 1L

In [5]: data = cursor.fetchone()

In [6]:

In [6]: print data
('10.0.25-MariaDB-1~trusty',)

In [7]: cursor.execute("SELECT name from tabItem")
Out[7]: 8L

In [8]: data = cursor.fetchall()

In [9]: print data
(('chemical',), ('Furan',), ('i',), ('T Meter',), ('Water Content Test IS 1001',), ('Water Content Test IS 9001',), ('Water Content Test IS 91',), ('Water Content Test IS91',))

In [10]: print list(data)
[('chemical',), ('Furan',), ('i',), ('T Meter',), ('Water Content Test IS 1001',), ('Water Content Test IS 9001',), ('Water Content Test IS 91',), ('Water Content Test IS91',)]


//print in list
In [14]: a=data

In [15]: print a
(('chemical',), ('Furan',), ('i',), ('T Meter',), ('Water Content Test IS 1001',), ('Water Content Test IS 9001',), ('Water Content Test IS 91',), ('Water Content Test IS91',))

In [16]:  [element for tupl in a for element in tupl]
Out[16]:
['chemical',
 'Furan',
 'i',
 'T Meter',
 'Water Content Test IS 1001',
 'Water Content Test IS 9001',
 'Water Content Test IS 91',
 'Water Content Test IS91']

 #print in list

In [23]: abc = [e[0] for e in a]

In [24]: abc
Out[24]:
['chemical',
 'Furan',
 'i',
 'T Meter',
 'Water Content Test IS 1001',
 'Water Content Test IS 9001',
 'Water Content Test IS 91',
 'Water Content Test IS91']

[ERPNext] How to set property of field in child table?

How to set property of field in child table?

Ans: We can set property of doc fields using following syntax.

cur_frm.set_df_property(FIELDNAME, Property, Value);

e.g.

cur_frm.set_df_property("cheque_no", "reqd", doc.voucher_type=="Bank Entry");

We can also set property of fields in child table, which is the main reason for this post.

Syntax:

var df = frappe.meta.get_docfield("TABLE NAME","FIELDNAME", cur_frm.doc.name);
df.read_only = 1;


e.g.
var df = frappe.meta.get_docfield("Employer Project Details","company_name", cur_frm.doc.name);
df.options = ["Tech M", "Wipro", "TCS"];

Using above code, we have generated dynamic dropdown in child table field of Select type.

  


Please comment if you like this post or need any help.

Thanks,
Sambhaji Kolate

[ERPNext] get value from another doc in javascript

Here is one example to fetch customer in Delivery Note using frappe.client.get_value method.
 Using this you can fetch values from another doc.


frappe.call({
    'method': 'frappe.client.get_value',
    'args': {
        'doctype': 'Delivery Note',
        'fieldname': 'customer',
          'filters': {
            'name': 'DN-00003'
          }
        }
        ,
       callback: function(r){
                              msgprint(r.message.customer);


       }
   
});


How to make child table field read only?

Using below code, one can set child table field read only.


Syntax
 
    var df = frappe.meta.get_docfield("CHILD TABLE DOCTYPE","FIELD NAME", cur_frm.doc.name);
    df.read_only = 1;


example:
 Using this, we can set item_name field read-only. This will helpful if you want to make fields read-only based on some condition

  frappe.ui.form.on("Sales Order","onload", function(frm, cdt, cdn) {
    var df = frappe.meta.get_docfield("Sales Order Item","item_name", cur_frm.doc.name);
    df.read_only = 1;
});

How to setup naming series in ERPNext?

Hello All,
I am sharing some naming series option which will be helpful.

• You can now use custom fields in naming series.
• You can also use YY MM DD in naming series, where YY represent current Year, MM represent current Month and DD represent Today.
• You can also use fields from document in naming series like supplier code/customer code

Examples:
1) QTN-.YYYY.-.customer.-.####
Resulting in QTN-2016-SBK-0001 (Asuming SBK is selected customer in Quotation)
2) PO-.YY.MM.-.{vendor_id}.-.#####
Resulting in "PO-1603-WN-00001" (where "Vendor ID" is custom field created under Document: Supplier and fetched into Purchase Order.)
3) DE/./.YY./.MM./.##### will create a series like
           DE/16/03/0001 where 16 is the year, 03 is the month and 0001 is the series
   Autoname rules:

• The key is separated by '.'
• '####' represents a series. The string before this part becomes the prefix:
• ABC.#### creates a series ABC0001, ABC0002 etc
• 'MM' represents the current month
• 'YY' and 'YYYY' represent the current year
• Each Series Prefix on a new line.Allowed special characters are "/" and "-"Optionally, set the number of digits in the series using dot (.) followed by hashes (#). For example, ".####" means that the series will have four digits.
  Default is five digits.

Thanks,
Sambhaji Kolate
Email: kolate.sambhaji@gmail.com

editable report and get values python side using get_server_fields

This code will create editable report.
On save buttn click, this will call python side code using

get_server_fields('update_skill_mapping_details', args, '', frm.doc,'','',1, function(r){
    frm.refresh()
  })
here data and form will be passed to get_server_fields
var args={
  'doc': frm.doc,
  'data': data
}



full js code:

frappe.require("assets/erp_customization/js/slick/lib/firebugx.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangedecorator.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangeselector.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellselectionmodel.js");



frappe.require("assets/erp_customization/js/slick/slick.formatters.js");
frappe.require("assets/erp_customization/js/slick/slick.editors.js");
frappe.require("assets/erp_customization/js/slick/slick.grid.js");
frappe.require("assets/erp_customization/js/slick/slick.core.js");



frappe.require("assets/erp_customization/js/slick/slick.groupitemmetadataprovider.js");
frappe.require("assets/erp_customization/js/slick/slick.dataview.js");
frappe.require("assets/erp_customization/js/slick/controls/slick.pager.js");
frappe.require("assets/erp_customization/js/slick/controls/slick.columnpicker.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.cellcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellexternalcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");





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

    // $().appendTo($(wrapper).find('.layout-main-section'));

    $(cur_frm.fields_dict.mygrid.wrapper).append( "<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:100%;height:300px;''></div>\
    </td>\
  </tr>\
</table>" );

});

// frappe.require("assets/frappe/js/slickgrid.min.js");
var selected_grid_data;
var grid_data;
var selectedData;


// erpnext.selling.CustomQueryReport = erpnext.selling.QuotationController.extend({
frappe.ui.form.on("Skill Map", {
  render: function(frm){
        var me=this;
        msgprint(frm.doc.customer)
        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 columns = [
        {id: "sel", name: "#", field: "num", cssClass: "cell-selection", width: 40, resizable: false, selectable: false, focusable: false },
        {id: "title", name: "Title", field: "title", width: 120, cssClass: "cell-title", editor: Slick.Editors.Text, validator: requiredFieldValidator},
        {id: "duration", name: "Duration", field: "duration",editor: Slick.Editors.Text},
        {id: "%", name: "% Complete", field: "percentComplete",editor: Slick.Editors.Text,},
        {id: "start", name: "Start", field: "start", minWidth: 60, editor: Slick.Editors.Date},
        {id: "finish", name: "Finish", field: "finish", minWidth: 60, editor: Slick.Editors.Date},
        {id: "effort-driven", name: "Effort Driven", field: "effortDriven",editor: Slick.Editors.Text,}
      ];
      var columnFilters = {};
      var options = {
        showHeaderRow: true,
        headerRowHeight: 30,
        editable: true,
        enableAddRow: true,
        asyncEditorLoading: false,
        enableCellNavigation: true,
        enableColumnReorder: false,
        explicitInitialization: true,
        editable: true,
      };

        var data = [];
        for (var i = 0; i < 100; i++) {
          data[i] = {
            id: i,
            title: "Task " + i,
            duration: i,
            percentComplete: i,
            start: i,
            finish: i,
            effortDriven: i
          };
        }
          var columnFilters = {};
              dataView = new Slick.Data.DataView();
              //call to create grid report
            grid = new Slick.Grid("#myGrid", dataView, columns, options);

    //Start filter in slick grid
            function filter(item) {
            // Regex pattern to validate numbers
            var patRegex_no = /^[$]?[-+]?[0-9.,]*[$%]?$/; // a number negative/positive with decimals with/without $, %

            for (var columnId in columnFilters) {
                if (columnId !== undefined && columnFilters[columnId] !== "") {
                    var c = grid.getColumns()[grid.getColumnIndex(columnId)];
                    var filterVal = columnFilters[columnId].toString().toLowerCase();
                    var filterChar1 = filterVal.substring(0, 1); // grab the 1st Char of the filter field, so we could detect if it's a condition or not

                    if(item[c.field] == null)
                        return false;

                    // First let see if the user supplied a condition (<, <=, >, >=, !=, <>, =, ==)
                    // Substring on the 1st Char is enough to find out if it's a condition or not
                    // if a condition is supplied, we might have to transform the values (row values & filter value) before comparing
                    // for a String (we'll do a regular indexOf), for a number (parse to float then compare), for a date (create a Date Object then compare)
                    if( filterChar1 == '<' || filterChar1 == '>' || filterChar1 == '!' || filterChar1 == '=') {
                        // We found a Condition filter, find the white space index position of the condition substring (should be index 1 or 2)
                        var idxFilterSpace = filterVal.indexOf(" ");

                        if( idxFilterSpace > 0 ) {
                            // Split the condition & value of the full filter String
                            var condition = filterVal.substring(0, idxFilterSpace);
                            filterNoCondVal = columnFilters[columnId].substring(idxFilterSpace+1);

                            // Which type are the row values? We'll convert to proper format before applying the condition
                            // Then apply the condition comparison: String (we'll do a regular indexOf), number (parse to float then compare)
                            if( patRegex_no.test(item[c.field]) ) {                           
                                if( testCondition(condition, parseFloat(item[c.field]), parseFloat(filterNoCondVal)) == false )
                                    return false;
                            // whatever is remain will be tested as a regular String format   
                            }else {                           
                                if ( testCondition(condition, item[c.field].toString().toLowerCase(), filterNoCondVal.toString().toLowerCase()) == false )
                                    return false;
                            }
                        }
                    }else{
                        if (item[c.field].toString().toLowerCase().indexOf(columnFilters[columnId].toString().toLowerCase()) == -1)
                            return false;
                    }
                }
            }
            return true;
        }
    //end of filter
        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.init();
        dataView.beginUpdate();
        dataView.setItems(data);
        dataView.setFilter(filter);
        dataView.endUpdate();
        msgprint(grid.getDataLength())

        // me.grid_data = dataView.getItems()
        // msgprint(me.grid_data)
        var ids = [];
        for (var i=0; i<grid.getDataLength() ; i++) {
          // ids.push(grid.getDataItem(i).id);
          ids.push(grid.getDataItem(i));
        }
        me.selectedData = ids;

        grid.onAddNewRow.subscribe(function (e, args) {
          var item = args.item;
          item.id =1;
          grid.invalidateRow(data.length);
          dataView.addItem(item);
          grid.updateRowCount();
          grid.render();
        });
  }, //end of render

 
  save_record: function(frm) {
    var me=this;
    msgprint(me.selectedData.length)

    cur_frm.events.update_skill_mapping_details(cur_frm,me.selectedData)
    // msgprint(me.grid_data);

  },
  update_skill_mapping_details: function(frm, data) {
    me = this;
    // me.frm = frm
    // msgprint(data)
    msgprint("Testtttt: hi from update_skill_mapping_details")
    msgprint(data.length)

    // aa = cur_frm.add_child("skill_mapping_details");
    // aa.industry = "sam";
    // refresh_field("skill_mapping_details");

      // frappe.call({
      //     method: "erp_customization.erp_customization.doctype.skill_map.skill_map.update_skill_details",
      //      args: {
      //       "data": data
      //       // "standards": c.standards,
      //       // "selectedData":selectedData
      //      },
      //     callback: function(r) {
      //         // location.reload(); 
      //              }
      //   });

var args={
  'doc': frm.doc,
  'data': data
}
console.log(args)
  get_server_fields('update_skill_mapping_details', args, '', frm.doc,'','',1, function(r){
    frm.refresh()
  })
  // return frappe.call({
  //     method: "update_skill_mapping_details",
  //     doc: args,
  //     callback: function(r, rt){
  //       frm.refresh()
  //     }
  //   });
  },

})












Python:
# -*- coding: utf-8 -*-
# Copyright (c) 2015, SBK and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe.model.document import Document
from frappe.utils import flt, getdate, nowdate, now_datetime
from frappe import msgprint, _
from frappe.utils import flt, getdate, nowdate
from datetime import date
import json

class SkillMap(Document):
    def update_skill_mapping_details(self, args):
        for data in args.get('data'):
            frappe.errprint(data.get('id'))
        frappe.msgprint("pair coding with rohit");

@frappe.whitelist()
def update_skill_details(data):
    frappe.msgprint("hi from update_skill_details");
    frappe.msgprint(data)

Loop over data:

          for data in args.get('data'):
            frappe.errprint(data.get('id'))
        frappe.msgprint("Pair coding with Rohit, Thanks, Sambhaji");

editable report with top filter

frappe.require("assets/erp_customization/js/slick/lib/firebugx.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangedecorator.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangeselector.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellselectionmodel.js");



frappe.require("assets/erp_customization/js/slick/slick.formatters.js");
frappe.require("assets/erp_customization/js/slick/slick.editors.js");
frappe.require("assets/erp_customization/js/slick/slick.grid.js");
frappe.require("assets/erp_customization/js/slick/slick.core.js");



frappe.require("assets/erp_customization/js/slick/slick.groupitemmetadataprovider.js");
frappe.require("assets/erp_customization/js/slick/slick.dataview.js");
frappe.require("assets/erp_customization/js/slick/controls/slick.pager.js");
frappe.require("assets/erp_customization/js/slick/controls/slick.columnpicker.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.cellcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.cellexternalcopymanager.js");
frappe.require("assets/frappe/js/lib/slickgrid/plugins/slick.rowselectionmodel.js");





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

    // $().appendTo($(wrapper).find('.layout-main-section'));

    $(cur_frm.fields_dict.mygrid.wrapper).append( "<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:100%;height:300px;''></div>\
    </td>\
  </tr>\
</table>" );

});

frappe.ui.form.on("Skill Mapping", "render", function(frm,doctype,name) {

  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 columns = [
    {id: "sel", name: "#", field: "num", cssClass: "cell-selection", width: 40, resizable: false, selectable: false, focusable: false },
    {id: "title", name: "Title", field: "title", width: 120, cssClass: "cell-title", editor: Slick.Editors.Text, validator: requiredFieldValidator},
    {id: "duration", name: "Duration", field: "duration",editor: Slick.Editors.Text},
    {id: "%", name: "% Complete", field: "percentComplete",editor: Slick.Editors.Text,},
    {id: "start", name: "Start", field: "start", minWidth: 60, editor: Slick.Editors.Date},
    {id: "finish", name: "Finish", field: "finish", minWidth: 60, editor: Slick.Editors.Date},
    {id: "effort-driven", name: "Effort Driven", field: "effortDriven",editor: Slick.Editors.Text,}
  ];
  var columnFilters = {};
  var options = {
    showHeaderRow: true,
    headerRowHeight: 30,
    editable: true,
    enableAddRow: true,
    asyncEditorLoading: false,
    enableCellNavigation: true,
    enableColumnReorder: false,
    explicitInitialization: true,
    editable: true,
  };


  $(function () {
    var data = [];
    for (var i = 0; i < 100; i++) {
      data[i] = {
        id: i,
        title: "Task " + i,
        duration: "5 days",
        percentComplete: Math.round(Math.random() * 100),
        start: "01/01/2009",
        finish: "01/05/2009",
        effortDriven: (i % 5 == 0)
      };
    }
      var columnFilters = {};
          dataView = new Slick.Data.DataView();
          //call to create grid report
        grid = new Slick.Grid("#myGrid", dataView, columns, options);

//Start filter in slick grid
        function filter(item) {
        // Regex pattern to validate numbers
        var patRegex_no = /^[$]?[-+]?[0-9.,]*[$%]?$/; // a number negative/positive with decimals with/without $, %

        for (var columnId in columnFilters) {
            if (columnId !== undefined && columnFilters[columnId] !== "") {
                var c = grid.getColumns()[grid.getColumnIndex(columnId)];
                var filterVal = columnFilters[columnId].toString().toLowerCase();
                var filterChar1 = filterVal.substring(0, 1); // grab the 1st Char of the filter field, so we could detect if it's a condition or not

                if(item[c.field] == null)
                    return false;

                // First let see if the user supplied a condition (<, <=, >, >=, !=, <>, =, ==)
                // Substring on the 1st Char is enough to find out if it's a condition or not
                // if a condition is supplied, we might have to transform the values (row values & filter value) before comparing
                // for a String (we'll do a regular indexOf), for a number (parse to float then compare), for a date (create a Date Object then compare)
                if( filterChar1 == '<' || filterChar1 == '>' || filterChar1 == '!' || filterChar1 == '=') {
                    // We found a Condition filter, find the white space index position of the condition substring (should be index 1 or 2)
                    var idxFilterSpace = filterVal.indexOf(" ");

                    if( idxFilterSpace > 0 ) {
                        // Split the condition & value of the full filter String
                        var condition = filterVal.substring(0, idxFilterSpace);
                        filterNoCondVal = columnFilters[columnId].substring(idxFilterSpace+1);

                        // Which type are the row values? We'll convert to proper format before applying the condition
                        // Then apply the condition comparison: String (we'll do a regular indexOf), number (parse to float then compare)
                        if( patRegex_no.test(item[c.field]) ) {                            
                            if( testCondition(condition, parseFloat(item[c.field]), parseFloat(filterNoCondVal)) == false )
                                return false;
                        // whatever is remain will be tested as a regular String format    
                        }else {                            
                            if ( testCondition(condition, item[c.field].toString().toLowerCase(), filterNoCondVal.toString().toLowerCase()) == false )
                                return false;
                        }
                    }
                }else{
                    if (item[c.field].toString().toLowerCase().indexOf(columnFilters[columnId].toString().toLowerCase()) == -1)
                        return false;
                }
            }
        }
        return true;
    }
//end of filter
    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();

    // grid.onAddNewRow.subscribe(function (e, args) {
    //   var item = args.item;
    //   grid.invalidateRow(data.length);
    //   data.push(item);
    //   grid.updateRowCount();
    //   grid.render();
    // });


    grid.onAddNewRow.subscribe(function (e, args) {
      var item = args.item;
      item.id =1;  //hardcoded
      grid.invalidateRow(data.length);
      dataView.addItem(item);
      grid.updateRowCount();
      grid.render();
    });

  })
});


Slick Grid editable report in ERPNext

Frappe is very pourful framework to develop application.

Using slickgrid we can make editable reports.
Value edited in report will be stored into doctype/child table.
So, this will give excel type data entry user experience.
It will also useful to make interactive dashboard

Code to make editable report:

frappe.require("assets/erp_customization/js/slick/lib/firebugx.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangedecorator.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellrangeselector.js");
frappe.require("assets/erp_customization/js/slick/plugins/slick.cellselectionmodel.js");
frappe.require("assets/erp_customization/js/slick/slick.formatters.js");
frappe.require("assets/erp_customization/js/slick/slick.editors.js");
frappe.require("assets/erp_customization/js/slick/slick.grid.js");


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

        // $().appendTo($(wrapper).find('.layout-main-section'));

    $(cur_frm.fields_dict.mygrid.wrapper).append( "<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:100%;height:300px;''></div>\
    </td>\
  </tr>\
</table>" );

});

frappe.ui.form.on("KPI", "render", function(frm,doctype,name) {

  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 columns = [
    {id: "title", name: "Title", field: "title", width: 120, cssClass: "cell-title", editor: Slick.Editors.Text, validator: requiredFieldValidator},
    {id: "duration", name: "Duration", field: "duration",editor: Slick.Editors.Text},
    {id: "%", name: "% Complete", field: "percentComplete",editor: Slick.Editors.Text,},
    {id: "start", name: "Start", field: "start", minWidth: 60, editor: Slick.Editors.Date},
    {id: "finish", name: "Finish", field: "finish", minWidth: 60, editor: Slick.Editors.Date},
    {id: "effort-driven", name: "Effort Driven", field: "effortDriven",editor: Slick.Editors.Text,}
  ];
  var options = {
      editable: true,
    enableAddRow: true,
    asyncEditorLoading: false,
    enableCellNavigation: true,
    enableColumnReorder: false,
    editable: true,
  };

var query_report=cur_frm.fields_dict.mygrid.wrapper

  $(function () {
    var data = [];
    for (var i = 0; i < 100; i++) {
      data[i] = {
        title: "Task " + i,
        duration: "5 days",
        percentComplete: Math.round(Math.random() * 100),
        start: "01/01/2009",
        finish: "01/05/2009",
        effortDriven: (i % 5 == 0)
      };
    }
    grid = new Slick.Grid("#myGrid", data, columns, options);


     grid.setSelectionModel(new Slick.CellSelectionModel());
    grid.onAddNewRow.subscribe(function (e, args) {
      var item = args.item;
      grid.invalidateRow(data.length);
      data.push(item);
      grid.updateRowCount();
      grid.render();
    });

  })
});


slick grid report in erpnext form

You can create slick grid report in ERPNext form.

So, data entry and report will be in same page. This is also helpful for making dashboard.

Add following code into custom script:



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.ui.form.on("KPI", "onload", function(frm,doctype,name) {

        // $().appendTo($(wrapper).find('.layout-main-section'));

    $(cur_frm.fields_dict.mygrid.wrapper).append( "<table width='100%>\
  <tr>\
    <td valign='top' width='50%'>\
      <div id='myGrid' style='width:100%;height:300px;''></div>\
    </td>\
  </tr>\
</table>" );

});

frappe.ui.form.on("KPI", "render", function(frm,doctype,name) {

    // $(cur_frm.fields_dict.mygrid.wrapper).html("<div class='myGrid' style='width:600px;height:500px;></div>");
  var grid;
  var columns = [
    {id: "title", name: "Title", field: "title"},
    {id: "duration", name: "Duration", field: "duration"},
    {id: "%", name: "% Complete", field: "percentComplete"},
    {id: "start", name: "Start", field: "start"},
    {id: "finish", name: "Finish", field: "finish"},
    {id: "effort-driven", name: "Effort Driven", field: "effortDriven"}
  ];
  var options = {
    enableCellNavigation: true,
    enableColumnReorder: false,
    editable: true,
    enableAddRow: true,
  };
   // var query_report=document.getElementById('myGrid')
var query_report=cur_frm.fields_dict.mygrid.wrapper

  $(function () {
    var data = [];
    for (var i = 0; i < 500; i++) {
      data[i] = {
        title: "Task " + i,
        duration: "5 days",
        percentComplete: Math.round(Math.random() * 100),
        start: "01/01/2009",
        finish: "01/05/2009",
        effortDriven: (i % 5 == 0)
      };
    }
    grid = new Slick.Grid("#myGrid", data, columns, options);
  })
});

Add barcode

Add barcode to html field

frappe.ui.form.on("Coupon", "onload", function(frm, cdt, cdn) {
var qr="<img src='http://www.barcodes4.me/barcode/qr/myfilename.png?value="+frm.doc.name+"'>"
var bcode="<img src='http://www.barcodes4.me/barcode/c128b/"+frm.doc.name+".gif'>"

var html="<div class='row'><div class='col-md-6'>"+qr+ "</div><div class='col-md-6 text-right'>"+bcode+"</div></div>"
$(cur_frm.fields_dict.html_12.wrapper).html(html);
});

Add Barcode to readonly data field

frappe.ui.form.on("Coupon", "onload", function(frm, cdt, cdn) {
var qr="<img src='http://www.barcodes4.me/barcode/qr/myfilename.png?value="+frm.doc.name+"'>"
var bcode="<img src='http://www.barcodes4.me/barcode/c128b/"+frm.doc.name+".gif'>"

var html=" :<div class='row'><div class='col-md-6'>"+qr+ "</div><div class='col-md-6 text-right'>"+bcode+"</div></div>"

frm.doc.data_12=html;
});

[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"