[SysProfileParticipant]

Thanks Christopher.

The example is excellent. Now I have a little extra difficulty. All report columns are dynamically created by a query that builds the entire worksheet.

My English is not very good, it will be much better to see code

  var menuID = "<?php echo $menuID; ?>";
  var objColumns = null;
  $.ajax({
    url: "sys_Fill_GridColumns.php",
    type: "POST",
    async: false,
    cache: false,
    data: "menuID=" + menuID,
    success: function (result) {
      objColumns = jQuery.parseJSON(result); // Here create the variable that contains the data of the columns.
    }
  });

      //
      var aryDataFields = new Array();
      for (var i = 0; i < objColumns.length; i++) {
        aryDataFields.push({
          "name": objColumns[i].cmn_menu_webcfg_dfName, // The field name that I use in the query MySQL
          "type": objColumns[i].cmn_menu_webcfg_fields_dfType, // Could be: string, date, number, float, int or bool
          "format": objColumns[i].cmn_menu_webcfg_fields_dfInFormat // This is a character string, especially for the date format, default MySQL returns 'yyyy-mm-dd'
        });
      }

      var aryColumns = new Array();
      for (var i = 0; i < objColumns.length; i++) {
        aryColumns.push({
          "datafield": objColumns[i].cmn_menu_webcfg_dfName, // The field name that I use in the query MySQL, the same as the previous case
          "columntype": objColumns[i].cmn_menu_webcfg_dfType, // 'textbox','dropdownlist','numberinput','checkbox' or 'datetimeinput'
          "text": objColumns[i].cmn_menu_webcfg_fields_dfText, // It contains the title text of column
          "filterable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfFiltrable), // true or false
          "sortable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfSorteable), // true or false
          "editable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfEditable), // true or false
          "groupable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfGroupable), // true or false
          "menu": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfMenu), // true or false
          "exportable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfExportable), // true or false
          "cellsAlign": objColumns[i].cmn_menu_webcfg_fields_dfLCR, // cell align, 'left', 'center' or 'right'
          "align": objColumns[i].cmn_menu_webcfg_fields_dfColLCR, // column align, 'left', 'center' or 'right'
          "width": objColumns[i].cmn_menu_webcfg_fields_dfWidth, // column width in pixels
          "cellsFormat": objColumns[i].cmn_menu_webcfg_fields_dfOutFormat, // This is a character string, especially for the date format, in my case  'dd-mm-yyyy' format
          "hidden": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfHidden) // true or false
        });
      }

some more code to complete and understand how I do my jqxGrid

      var source = {
        localData: result,
        dataType: "json",
        dataFields: aryDataFields
      };
      var dataAdapter = new $.jqx.dataAdapter(source, {
        loadComplete: function () {
          $("#showLoader").jqxLoader("close");
        }
      });
      $("#datacontainer").jqxGrid({
        autoheight: false,
        enablebrowserselection: true,
        showgroupsheader: false,
        groupable: true,
        sortable: true,
        editable: true,
        filterable: true,
        columnsResize: true,
        autoRowHeight: false,
        columnsReorder: true,
        columnsheight: 40,
        rowsheight: 20,
        autoShowLoadElement: false,
        filterMode: "excel",
        theme: "custom",
        localization: getLocalization(),
        source: dataAdapter,
        columns: aryColumns
      });

As you can see, your example is perfect if I have a static code, I do not know if in a column, no checkbox, the property “filteritems” will be applied correctly. I do not know what happens if “filteritems: null” in the other columns, stop working.

Thanks for your time

[SysProfileParticipant]

I just come back to this post, thanks for your response @hristo. In the example I not find a Boolean column. It’s very simple what I try to do, I need to change the label shown in the filter of a column. Not if you can make an example https://jsfiddle.net/

• This reply was modified 6 years, 7 months ago by  SysProfile. Reason: Tag an user response

[SysProfileParticipant]

I just need to change the labels

[SysProfileParticipant]

@hristo Thanks for your answer, I have seen the example of “Filter”, the example does not have what I need. I need to change True for “Verdadero” and False for “Falso”. How I could easily change these labels?

[SysProfileParticipant]

Please, I need help from someone. The component works, is a matter of appearance it is in another language

[SysProfileParticipant]

Sorry but I think you has misunderstood my question. To write the questions I am using Google Translate probably that’s the reason.

In the example you tell me, I can not find the solution to my problem. I will attach a print screen to this message

https://drive.google.com/file/d/0BwBRLEj_yWFKaWNoTUJNOURmVEE/view?usp=sharing <- Google Drive shared image

[SysProfileParticipant]

Problem solved!, it is not a problem jqxdata.export.js. php.ini configuration has a maximum in MB the following items:

post_max_size
upload_max_filesize

By default these values are set to 2MB, for my needs I changed to 40MB

[SysProfileParticipant]

https://drive.google.com/file/d/0BwBRLEj_yWFKQ1hHNkhfLWpRdHM/view?usp=sharing link of image (google drive)

[SysProfileParticipant]

@taskmate, I saw that code but does not respect the format of the cell, I have thousands and decimal separator.

[SysProfileParticipant]

I develop a function in javascript, the problem is that when update the value in the cell, the format is “destroyed” completely

[SysProfileParticipant]

I do not speak English, I’ll show you the code with some comments

First I leave the structure of the data to be consulted in MySQL

This table shows the text shown in my development. Ustede should change “_esAR” for which matches your language
CREATE TABLEcmn_idiomas` (
_id smallint(5) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘ID’,
_esAR varchar(2048) NOT NULL DEFAULT ” COMMENT ‘Español (Argentina)’,
PRIMARY KEY (_id),
UNIQUE KEY _esAR (_esAR(255)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Languages’;`
Example

_id	_esAR
0
1	Es necesario ingresar usuario y contraseña para poder acceder al sistema
2	Interlog - InterSys
3	Operativo
4	El usuario o la contraseña no son correctos

This table is the configuration of the fields to be displayed
CREATE TABLEcmn_menu_webcfg_fields` (
_dfName varchar(60) NOT NULL,
_dfText smallint(5) unsigned NOT NULL COMMENT ‘Description (FOREIGN KEY – cmn_idiomas)’,
_dfType enum(‘string’,’date’,’number’,’float’,’int’,’bool’) DEFAULT ‘string’ COMMENT ‘Field type’,
_dfWidth smallint(4) unsigned DEFAULT NULL COMMENT ‘Width of column in pixels’,
_dfColLCR enum(‘left’,’center’,’right’) DEFAULT ‘left’ COMMENT ‘Text justification column (left, center o right)’,
_dfLCR enum(‘left’,’center’,’right’) DEFAULT ‘left’ COMMENT ‘Text justification cell (left, center o right)’,
_dfInFormat varchar(255) NOT NULL DEFAULT ” COMMENT ‘Input format (optional, useful format for dates. For example, “yyyy-MM-dd” from MySQL)’,
_dfOutFormat varchar(255) NOT NULL DEFAULT ” COMMENT ‘Output format (optional, useful if you need a different format to the American)’,
PRIMARY KEY (_dfName),
KEY _dfText (_dfText) USING BTREE,
KEY _dfName (_dfName) USING BTREE,
CONSTRAINT cmn_menu_webcfg_fields_dfText FOREIGN KEY (_dfText) REFERENCES cmn_idiomas (_id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;`
Example

_dfName			_dfText	_dfType	_dfWidth	_dfColLCR	_dfLCR	_dfInFormat	_dfOutFormat
cmn_legajos_creacion	352	date	90		left		right	yyyy-MM-dd	dd/MM/yyyy
cmn_lst_clientes_desc	7	string	220		left		left		
opr_docrec_fac_monto	192	number	100		left		right			d2

This table in the database has the configuration report related to specific functions of the column and behavior
CREATE TABLEcmn_menu_webcfg` (
_id varchar(30) NOT NULL COMMENT ‘Report ID’,
_dfName varchar(60) NOT NULL DEFAULT ” COMMENT ‘Field name (FOREIGN KEY – cmn_menu_webcfg_fields)’,
_dfFiltrable enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Filtrable? (true/false)’,
_dfSorteable enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Sorteable? (true/false)’,
_dfMenu enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Context menú ? (true/false)’,
_dfExportable enum(‘true’,’false’) DEFAULT ‘true’ COMMENT ‘Exportable ? (true/false)’,
_dfTextOpt varchar(255) DEFAULT NULL COMMENT ‘Only works if _dfTexto is = 0’,
_dfOrder smallint(3) unsigned NOT NULL COMMENT ‘Column order’,
PRIMARY KEY (_id,_dfName),
KEY _dfName (_dfName),
KEY _dfOrder (_dfOrder) USING BTREE,
CONSTRAINT cmn_menu_webcfg_dfName FOREIGN KEY (_dfName) REFERENCES cmn_menu_webcfg_fields (_dfName) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Report configuration’;`
Example

_id		_dfName		_dfFiltrable	_dfSorteable	_dfMenu	_dfExportable	_dfTextOpt	_dfOrder	
cmn_fup_impo	opr_lst_ftp_id	false		false		false	false		opr_lst_ftp_id	1
cmn_fup_impo	opr_carga_fc	true		true		true	true				2

The query to the database on PHP

<?php
session_start();

function ConnectDB() {
  $_SESSION["mysqlconn"] = mysqli_connect ($_SESSION["dbHost"], $_SESSION["dbUser"], $_SESSION["dbPass"], $_SESSION["dbName"], $_SESSION["dbPort"]);
  if ($_SESSION["mysqlconn"] != 0 && $_SESSION["mysqlconn"] != false) {
    return true;
  } else {
    return false;
  }
}

function Execute($sql) {
  ConnectDB();
  $resource = $_SESSION["mysqlconn"];
  return mysqli_query($resource, $sql);
}

$menuID = $_POST["menuID"];
$lang = $_SESSION["strMyLang"];
$rnlf = "\r\n";
$query = "SELECT".$rnlf;
$query .= "cmn_menu_webcfg._dfName AS cmn_menu_webcfg_dfName,".$rnlf;
$query .= "IF(cmn_menu_webcfg_fields._dfText > 0, IF(ISNULL(cmn_menu_webcfg._dfTextOpt), cmn_idiomas._$lang, CONCAT(cmn_idiomas._$lang, cmn_menu_webcfg._dfTextOpt)), cmn_menu_webcfg._dfTextOpt) AS cmn_menu_webcfg_dfText,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfType AS cmn_menu_webcfg_dfType,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfWidth AS cmn_menu_webcfg_dfWidth,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfColLCR AS cmn_menu_webcfg_dfColLCR,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfLCR AS cmn_menu_webcfg_dfLCR,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfInFormat AS cmn_menu_webcfg_dfInFormat,".$rnlf;
$query .= "cmn_menu_webcfg_fields._dfOutFormat AS cmn_menu_webcfg_dfOutFormat,".$rnlf;
$query .= "cmn_menu_webcfg._dfFiltrable AS cmn_menu_webcfg_dfFiltrable,".$rnlf;
$query .= "cmn_menu_webcfg._dfSorteable AS cmn_menu_webcfg_dfSorteable,".$rnlf;
$query .= "cmn_menu_webcfg._dfMenu AS cmn_menu_webcfg_dfMenu,".$rnlf;
$query .= "cmn_menu_webcfg._dfExportable AS cmn_menu_webcfg_dfExportable".$rnlf;
$query .= "FROM".$rnlf;
$query .= "cmn_menu_webcfg".$rnlf;
$query .= "INNER JOIN cmn_menu_webcfg_fields ON cmn_menu_webcfg._dfName = cmn_menu_webcfg_fields._dfName".$rnlf;
$query .= "  INNER JOIN cmn_idiomas ON cmn_menu_webcfg_fields._dfText = cmn_idiomas._id".$rnlf;
$query .= "WHERE".$rnlf;
$query .= "cmn_menu_webcfg._id = '$menuID'".$rnlf;
$query .= "ORDER BY".$rnlf;
$query .= "cmn_menu_webcfg._dfOrder ASC";
$sql = Execute($query);
$rows = array();
while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)) {
  $rows[] = $row;
}
$json = json_encode($rows);
echo $json;
?>

These variables are used to display icons below

/* GRID - Clip */
var imgClip = "<div style=\"margin-left: 5px; margin-top: 3px; height=16px; width=11px;\"><img src=\"images/attachs.png\" border=\"0\" /></div>";
/* GRID - PostIt */
var imgPostIt = "<div style=\"margin-left: 5px; margin-top: 3px; height=16px; width=16px;\"><img src=\"images/post_it.png\" border=\"0\" /></div>";

// Draw icon on column header
var renderer = function (columnfield) {
  if (columnfield == "opr_lst_ftp_id") {
    return imgClip;
  } else if (columnfield == "opr_obs_id") {
    return imgPostIt;
  }
}

// Draw an icon in each cell, if the conditions "if" apply 
var cellsrenderer = function (row, columnfield, value, defaulthtml, columnproperties) {
  if (columnfield == "opr_lst_ftp_id") {
    if (value != "&nbsp;") {
      return imgClip;
    }
  } else if (columnfield == "opr_obs_id") {
    if (value == "1") {
      return imgPostIt;
    } else {
      return "&nbsp;";
    }
  }
}

// Change the background color if the value of each record occurs in the following ranges
var cellclassname = function (row, columnfield, value, rowdata) {
  if (columnfield == "opr_embarque_eta_dias") {
    if (value < -5) {
      return 'red';
    } else if (value >= -5 && value <= 5) {
      return 'orange';
    } else if (value > 5) {
      return 'green';
    }
  }
}

// Here the result is shown in "result" of the query
var objColumns = jQuery.parseJSON(result);

var aryDataFields = new Array();
for (var i = 0; i < objColumns.length; i++) {
  aryDataFields.push({
    "name": objColumns[i].cmn_menu_webcfg_dfName,
    "type": objColumns[i].cmn_menu_webcfg_dfType,
    "format": objColumns[i].cmn_menu_webcfg_dfInFormat
  });
}

var aryColumns = new Array();
for (var i = 0; i < objColumns.length; i++) {
  aryColumns.push({
    "datafield": objColumns[i].cmn_menu_webcfg_dfName,
    "text": objColumns[i].cmn_menu_webcfg_dfText,
    "filterable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfFiltrable),
    "sortable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfSorteable),
    "menu": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfMenu),
    "exportable": $.parseJSON(objColumns[i].cmn_menu_webcfg_dfExportable),
    "cellsAlign": objColumns[i].cmn_menu_webcfg_dfLCR,
    "align": objColumns[i].cmn_menu_webcfg_dfColLCR,
    "width": objColumns[i].cmn_menu_webcfg_dfWidth,
    "cellsFormat": objColumns[i].cmn_menu_webcfg_dfOutFormat,
    "cellsrenderer": cellsrenderer,
    "cellclassname": cellclassname,
    "renderer": renderer
  });
}
var source = {
  localData: queryResult,
  dataType: "json",
  dataFields: aryDataFields
};
var dataAdapter = new $.jqx.dataAdapter(source, {
  loadComplete: function () {
    // Some own code (does not apply to the example)
  }
});
$("#datacontainer").jqxGrid({
  width: 800,
  height: 600,
  enablebrowserselection: true,
  showgroupsheader: false,
  groupable: true,
  sortable: true,
  filterable: true,
  columnsResize: true,
  autoRowHeight: false,
  columnsReorder: true,
  columnsheight: 40,
  rowsheight: 20,
  autoShowLoadElement: false,
  filterMode: "excel",
  theme: "custom",
  localization: getLocalization(),
  source: dataAdapter,
  columns: aryColumns
});

If you have any questions I try to answer with my limitations with the language

• This reply was modified 6 years, 9 months ago by  SysProfile. Reason: Add records examples

[SysProfileParticipant]

Thanks for your suggestion, that you propose would happen in “bindingcomplete” would be best to do it on “cellsrenderer” or “renderer” it is much difference between a performance and a method.

[SysProfileParticipant]

Dear Peter, I now see that you are given in the definition of the input fields and the format of the column. In the examples there that I did not see it. As to what he tells me to expose code in principle I want to apologize. I thought it only showed “a record of a table” will not happen again. Please, if possible readdress to update the examples on the web and include these properties. Now I see it correctly. Thanks for your support

Best Regards,
Sergio Haurat

[SysProfileParticipant]

taskmate, Thanks for your reply. If you run this code (http://jsfiddle.net/SysProfile/5YtCm/137/) you read the date correctly?. The first record, column No. 2 (cmn_legajos_creacion). Real date, ‘2016-03-22’ and when I run the code, jsFiddle show me ‘Mon Mar 21 2016’

[SysProfileParticipant]

Apache
SetEnv TZ America/Argentina/Buenos_Aires

PHP
date.timezone = “America/Argentina/Buenos_Aires”

MySQL
default-time-zone = “America/Argentina/Buenos_Aires”

[Author]

Posts