Coloring a row depending on value trirand jqgrid

Question

I need to color the background of a row depending on the value stored in a database. I'm using TriRand jqgrid. I need to get the value from POLineStatus and if it equals 2 I need to color the background red. I setup a class in the css file that will get the proper background-color property. Unfortunately I cannot figure out how to get it to do this on a per column basis.

$mycellattr =  <<<CELLATTR
function (rowid, value, rawObject, colModel, arraydata){ 
    if(value=="Rejected"){
    return {class:'colorClass'};
   }
}
CELLATTR;


$grid->setGridEvent('rowattr', $mycellattr);

gridViewSubGrid.php -------- Full code:

<?php
 if(!isset($_SESSION)) {
     session_start();
}

 $user = $_SESSION['loggedInUser'];

require_once "model.php"; 

require_once "jq-configSub.php";
$conn = sqlsrv_connect($serverName1, $connectionInfo1);

// include the jqGrid Class
require_once "php/jqGrid.php";

// include the SQLSRV driver class
require_once "php/jqGridSqlsrv.php";

// include the autocomplete class 
require_once "php/jqAutocomplete.php"; 

// include the datepicker class 
require_once "php/jqCalendar.php"; 

if(isset ($_REQUEST["CustPOID"])){
    $rowid = jqGridUtils::Strip($_REQUEST["CustPOID"]);
    }
else{
    $rowid = "";
}

$grid = new jqGridRender($conn);

$grid->SelectCommand = "SELECT [CustPOLineID]
      ,[Qty]
      ,[Price]
      ,[POLineComment]
      ,[Cost]
      ,[Origin]
      ,[POLineLoadDate]
      ,[ApptNum]
      ,[CreateDate]
      ,[VendRefNum]
      ,[HapcoPONum]
      ,[PriceAlt]
      ,[VendRefNumAlt]
      ,[MiscCharge]
      ,[PHDSCharge]
      ,[VBRCharge]
      ,[CostAlt]
      ,[VendAlt]
      ,[LineGM]
      ,[ModifyDate]
      ,[ModifiedBy]
      ,[CreatedBy]
      ,[VendPurchPONum]
      ,[CustPOID]
      ,cpl.VendId
      ,vn.VendDesc
      ,cpl.ProdID
      ,pd.ProdDesc
      ,cpl.POLineStatusID
      ,posl.POLineStatusDesc
      ,cpl.ChargeTypeID
      ,ct.ChargeTypeDesc
      ,cpl.AlertReasonID
      ,ar.AlertReasonCode
  FROM [HapcoSales].[dbo].[CustPOLine] as cpl 

                                        LEFT OUTER JOIN Vendor as vn
                                            on cpl.VendId  = vn.VendId
                                        LEFT OUTER JOIN Product as pd
                                            on cpl.ProdID  = pd.ProdID
                                        LEFT OUTER JOIN POLineStatus as posl
                                            on cpl.POLineStatusID  = posl.POLineStatusID
                                        LEFT OUTER JOIN ChargeType as ct
                                            on cpl.ChargeTypeID  = ct.ChargeTypeID
                                        LEFT OUTER JOIN AlertReason as ar
                                            on cpl.AlertReasonID  = ar.AlertReasonID
 WHERE CustPOID= ?";
// set the ouput format to json 
$grid->dataType = 'json'; 
$grid->table ="CustPOLine"; 
//Set primary key
$grid->setPrimaryKeyId("CustPOLineID"); 
$grid->setDbDate('Y-m-d'); 
$grid->setDbTime('Y-m-d H:i:s'); 
$grid->setUserDate('m/d/Y');
$grid->setUserTime('m/d/Y');
$grid->datearray = array('POLineLoadDate');




$Model = array(   
array("name"=>"CustPOLineID","editable"=>false,"hidden"=>true), 
array("name"=>"Qty", "label"=>"Qty:", "width"=>"50","editoptions"=>array("tabindex"=>1)),
array("name"=>"ProdDesc", "label"=>"Item:","width"=>"350","editoptions"=>array("tabindex"=>2)),
array("name"=>"ProdID","label"=>"Product ID:","hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("readonly"=>"readonly")),
array("name"=>"Price", "label"=>"Price:","width"=>"75","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>3)),
array("name"=>"Cost", "label"=>"Cost:","width"=>"75","formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>4)),
array("name"=>"VendDesc", "label"=>"Vendor:","width"=>"300","editoptions"=>array("tabindex"=>5)),
array("name"=>"VendId", "label"=>"Vendor ID:","hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("readonly"=>"readonly")),
array("name"=>"MiscCharge","formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>6)),
array("name"=>"PHDSCharge","formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>7)),
array("name"=>"VBRCharge","formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>8)),
array("name"=>"POLineLoadDate","label"=>"Load Date:","width"=>"125","editable"=>true,"editoptions"=>array("tabindex"=>9)),
array("name"=>"POLineComment","search"=>false,"width"=>"300","formatter"=>"textarea","label"=>"Comments","edittype"=>"textarea","editoptions"=>array("tabindex"=>10)),
array("name"=>"PriceAlt","label"=>"Alt Price:", "formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>11)),
array("name"=>"CostAlt", "label"=>"Alt Cost:","formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$"),"editoptions"=>array("tabindex"=>12)),
array("name"=>"VendAlt", "label"=>"Alt Vendor:","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>13)),
array("name"=>"ChargeTypeID", "label"=>"Charge Type","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>14)),
array("name"=>"ModifyDate", "label"=>"Modified:","formoptions"=>array("rowpos"=>2,"colpos"=>4),"editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("readonly"=>"readonly")),
array("name"=>"ModifiedBy", "label"=>"Modified By:","formoptions"=>array("rowpos"=>3,"colpos"=>4),"editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("readonly"=>"readonly")),
array("name"=>"CreatedBy","label"=>"Created By:","formoptions"=>array("rowpos"=>4,"colpos"=>4),"editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("readonly"=>"readonly")),
array("name"=>"HapcoPONum","formoptions"=>array("rowpos"=>2,"colpos"=>3),"editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>21)),
array("name"=>"Origin","formoptions"=>array("rowpos"=>3,"colpos"=>3),"editoptions"=>array("tabindex"=>22)),
array("name"=>"VendRefNumAlt","formoptions"=>array("rowpos"=>4,"colpos"=>3),"editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>23)),
array("name"=>"ApptNum","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>15)),
array("name"=>"VendRefNum","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true) ,"editoptions"=>array("tabindex"=>16)),
array("name"=>"VendPurchPONum","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>17)),
array("name"=>"AlertReasonCode","editoptions"=>array("tabindex"=>18)),
array("name"=>"AlertReasonID","editable"=>true,"hidden"=>true, "editrules"=>array("edithidden"=>true),"editoptions"=>array("tabindex"=>19)),
array("name"=>"POLineStatusDesc"),
array("name"=>"POLineStatusID"),
array("name"=>"LineGM", "label"=>"Gross Margin:", "editable"=>true,"editoptions"=>array("readonly"=>"readonly"),"formatter"=>"currency","formatter"=>"currency","formatoptions"=>array("decimalPlaces"=>2,"thousandsSeparator"=>",","prefix"=>"$")),
array("name"=>"CustPOID","hidden"=>true)
                    );

$grid->setColModel($Model, array(&$rowid));

$grid->setUrl('gridViewSubGrid.php');

$grid->setGridOptions(array(
    "hoverrows"=>true,
    "resizable"=>true,
    "scroll"=>1,//unlimited scroll
    "rowNum"=>100,//100 entries on load
    "caption"=>"Detailed view of each sale.",
    "rowList"=>array(10,20,30),
    "sortname"=>"CustPOLineID",//sort by
    "sortorder"=>"desc",
    "autowidth"=>true,//determine the users screen resolution, generate grid off of that
    "height"=>"175",
    "footerrow"=>true,
    "userDataOnFooter"=>true

    ));
$grid->setDatePicker("POLineLoadDate",null,true,false);
$grid->setColProperty("POLineLoadDate",array( 
    "formatter"=>"date", 
    "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"m/d/Y") 
    ) 
); 

$grid->setColProperty("CustPOID", array("hidden"=>true));
$grid->setColProperty("CustPOLineID", array("hidden"=>true));


$grid->setAutocomplete("VendDesc","#VendId","SELECT DISTINCT VendDesc, VendDesc as VendDesc2, VendId FROM Vendor WHERE VendDesc LIKE ? ORDER BY VendDesc",null,true,false);

$grid->setAutocomplete("ProdDesc","#ProdID","SELECT ProdDesc, ProdDesc AS ProdDesc2, ProdID FROM Product WHERE ProdDesc LIKE ? ORDER BY ProdDesc",null,true,false);

$grid->setSelect("POLineStatusID", "SELECT DISTINCT POLineStatusID, POLineStatusDesc, POLineStatusID AS POLineStatusDesc FROM POLineStatus ORDER BY 1",false,true,true,array(""=>"All"));


$grid->setSelect("ChargeTypeID", "SELECT DISTINCT ChargeTypeID, ChargeTypeDesc, ChargeTypeID AS ChargeTypeDesc FROM ChargeType ORDER BY 1",false,true,true,array(""=>"All"));
//We select the key:value for the select drop downs
$grid->setSelect("AlertReasonID", "SELECT DISTINCT AlertReasonID,AlertReasonDesc, AlertReasonID AS AlertReasonDesc FROM AlertReason ORDER BY 1",false,true,true,array(""=>"All"));


// Enable toolbar searching 
$grid->navigator = true; 
$grid->setNavOptions('navigator', array("excel"=>true,"add"=>true,"edit"=>true,"del"=>true,"view"=>true));
$grid->setNavOptions('edit', array("closeAfterEdit"=>true,"height"=>"auto","dataheight"=>"auto", "width"=>"auto")); 
$grid->setNavOptions('add', array("height"=>"auto","dataheight"=>"auto","width"=>"auto")); 



// disable the CRUD buttons when we initialy load the grid
$initgrid = <<<INIT
jQuery("#add_detail").addClass("ui-state-disabled");
jQuery("#edit_detail").addClass("ui-state-disabled");
jQuery("#del_detail").addClass("ui-state-disabled");
INIT;

// on beforeshow form when add we get the customer id and set it for posting
$beforeshow = <<<BEFORE
function(formid)
{
var srow = jQuery("#grid").jqGrid('getGridParam','selrow');
if(srow) {
    var gridrow = jQuery("#grid").jqGrid('getRowData',srow);
    $("#CustPOID",formid).val(gridrow.CustPOID);
}
}
BEFORE;
//This function brings up the edit dialog on a double click

$dbClk = <<<ONDBLCLK
function dbClk(rowid, selected)
{
jQuery(this).jqGrid('editGridRow', rowid, {"height" : "100%","width" : "100%", "dataheight" : "auto"});
}
ONDBLCLK;

$mycellattr =  <<<CELLATTR
            function (e, rd) {
                if (rd.POLineStatusID === "2") { // test the value of POLineStatus column
                    return {"class": "colorClass"};
                }

            }
CELLATTR;


$grid->setGridEvent('rowattr', $mycellattr);
//$grid->setColProperty('POLineStatusID', array("cellattr"=>"js:".$mycellattr));

//$grid->customFunc = "gmCalc";

$grid->setGridEvent('ondblClickRow', $dbClk);
$grid->setNavEvent('add', 'beforeShowForm', $beforeshow); 

$cid = jqGridUtils::GetParam('CustPOLineID'); //we get the CustPOLineID from the SQL query

//$grid->setAfterCrudAction('add', "UPDATE CustPOLine SET LineGM = 3240 WHERE CustPOLineID=?",array($cid));
$dateNow=dateTimeNow(); //goes out to model.php and calls the dateTimeNow function - and returns current value
$grid->setAfterCrudAction('edit', "UPDATE CustPOLine SET ModifiedBy = '".$user."' WHERE CustPOLineID=?",array($cid));
$grid->setAfterCrudAction('edit', "UPDATE CustPOLine SET ModifyDate = '".$dateNow."' WHERE CustPOLineID=?",array($cid));

$summaryrows = array("LineGM"=>array("LineGM"=>"SUM"));


$grid->renderGrid("#detail","#pgdetail", true, $summaryrows, array(&$rowid), true,true);
$conn = null;


function gmCalc($gdata, $conn)
{
// gdata->rows contain the grid data rows
   foreach($gdata->rows as $row ) 
   {
     //$total = ((($Price * $Qty)-($Cost * $Qty))-(($MiscCharge+$PHDSCharge+$VBRCharge)*$Qty));
     $row->LineGM = ((($row->Price*$row->Qty)-($row->Cost*$row->Qty))-(($row->MiscCharge+$row->PHDSCharge+$row->VBRCharge)*$row->Qty));
   }
   // return the modified
   return $gdata;
}

?>

------index.php

<?php 
    require_once ('model.php');
    loginCheck();
    require_once ('header.php'); 


?>

    <div id="main-container">
        <div id="mainContainer" class="wrapper clearfix">
            <div>
                <?php include "gridView.php";?>
            </div>  
                <div>
                <?php include "gridViewSubGrid.php"; ?>
                </div>


                  <h2>Gross Margin:</h2>
                  <div>From: <input type="text" id="from" >
                         To: <input type="text" id="to" onchange="setFrom()"></div>
                    <div id="myDiv"></div>
                  </div>
                  <div id="griddef"></div> 
        </div> <!-- #main -->
    </div> <br><!-- #main-container -->
<?php require_once ('footer.php'); ?>

-------Olegs fix:

$mycellattr =  <<<CELLATTR
function (rd,cur,id){ 
    if(rd.POLineStatusID=="2"){
    return {"class":"colorClass"};
   }
}
CELLATTR;


$grid->setGridEvent('rowattr', $mycellattr);

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

I'm not a PHP developer, but what is definitively wrong in your code is the parameters of rowattr. It has tree parameters rd, cur and id. One needs only the first parameter in the most cases. Look in the answer for a code example.

Alternatively you can use jqGridRowAttr jQuery event. See the answer for code examples. In your case it will be

$("#list").bind("jqGridRowAttr", function (e, rd) {
    if (rd.POLineStatus === "Rejected") { // test the value of POLineStatus column
        return {"class": "colorClass"};
    }
});

It's important to use {"class": "colorClass"} instead of {class: "colorClass"} to be compatible with old versions of Internet Explorer. It's important to set the bind before the grid will be created.

The demo demonstrates the usage of "jqGridRowAttr".

edited May 23 '17 at 11:59

Community

1
1

answered Mar 18 '14 at 16:12

Oleg

220,925
34
403
798

Oleg, thank you for taking the time to respond. My only issue is, I don't setup my grid the way you do. It's some proprietary way TriRand setup it seems. For instance, I'll show you my code. I edited my original post and put it up top again. – jon.r Mar 18 '14 at 17:34
@Waragi: You are welcome! Your problem was that you used `if(value=="Rejected")` where `value` is the second parameter of `rowattr`. It was wrong. I wrote you about two ways to fix the problem: one with fixing of the code of `rowattr` and another with usage of `$("#detail").bind("jqGridRowAttr", ...);`. For the second way you need understand how to mix JavaScript code with PHP code (see [the documentation](http://www.trirand.net/documentation/php/_2v90ju98u.htm)). You last code is better but still wrong. You use `function (e, rd)` instead of `function (rd)` in `$mycellattr`. – Oleg Mar 18 '14 at 18:29
@Waragi: The definition of CSS rule for the class `colorClass` could be another point of error. If you will not have correct results after fixing of `$mycellattr` you should verify (and post) the CSS rule. – Oleg Mar 18 '14 at 18:34
1

You are a saint, it's been over five hours - it's working! You're right, I need to understand how to mix PHP and javascript better. Thank you again – jon.r Mar 18 '14 at 19:01

Coloring a row depending on value trirand jqgrid

1 Answers1