Forum Replies Created

Viewing 15 posts - 1 through 15 (of 26 total)
  • Author
    Posts

  • SysProfile
    Participant

    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


    SysProfile
    Participant

    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

    SysProfile
    Participant

    I just need to change the labels


    SysProfile
    Participant

    @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?


    SysProfile
    Participant

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


    SysProfile
    Participant

    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

    in reply to: Grid Export to Excel Grid Export to Excel #84176

    SysProfile
    Participant

    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

    in reply to: Grid Export to Excel Grid Export to Excel #84169

    SysProfile
    Participant
    in reply to: Grid balance Grid balance #84105

    SysProfile
    Participant

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

    in reply to: Grid balance Grid balance #84066

    SysProfile
    Participant

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


    SysProfile
    Participant

    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
    in reply to: Grid balance Grid balance #84012

    SysProfile
    Participant

    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.


    SysProfile
    Participant

    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


    SysProfile
    Participant

    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’


    SysProfile
    Participant

    Apache
    SetEnv TZ America/Argentina/Buenos_Aires

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

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

Viewing 15 posts - 1 through 15 (of 26 total)