PDA

View Full Version : MySQL grid in a Portal?



Street14
8 Sep 2010, 12:53 AM
Hello im very new too EXT JS
So bare in mind when you read my posts that i do not know much detail.

I have been reading posts and playing with the examples for over a week now.
I have had moderate success in understanding the basics.

One thing im thinking about is using a portal with a grid generated from mysql as one of the panels.
I do not have much problem creating a basic grid from mysql. The examples and tutorials where fairly good.

Though i see that if i had static grid content included in the .js file incorporating the grid into the portal would be no different then the example for the portal.

But as with my intentions how would you call the .js file or the html file to be shown in the portal as a panel?
Ive googled and read many number of posts and searched the forum. I have yet come across either a working example or a detailed explanation on how to do this.

Can anyone give me a link to a example with code supplied or provide me with such.
As a example there are two mysql grid tutorials in the tutorial section.
Each seems to generate the grid a bit differently one via a namespace and the other via a onReady call.
To say the least im lost.

Condor
8 Sep 2010, 2:54 AM
Are you talking about a JSR-168/268 portal or the portal example from the Ext SDK?

Street14
8 Sep 2010, 9:04 AM
The downloaded EXT JS framework example i guess its also called a JDK.

Street14
8 Sep 2010, 10:04 AM
Ok i have managed to use the autoLoad for a web page like either a html page or a php page.
I can get that to load in the portal just fine using the autoLoad.

Though that certainly is nice to know. It still leaves me short in actually loading a extjs grid via the js file.
So i guess it all boils down to how to use something like the autoLoad to render the js file.
autoLoad:{url: 'some.js.file.js'} just loads the actual js file code. Hmmm where to go from here. Still lost.

Condor
8 Sep 2010, 10:43 AM
What does somefile.js contain? If it contains a component definition (either config object or result of an executed anonymous function) then you could use the LiteRemoteComponent plugin (see User extensions section).

Street14
8 Sep 2010, 11:58 AM
Ok ill try to wrap the code of the php db file and then the js file.
I have stripped the js file down to what i think is the minimal code needed to render it to the html file loading the script.
The db file contains a test scenario with alot of sql calculations.
These files work just fine when i use the html file to call the script the grid shows up just fine to let you know.
Ok now for the code.



<?php

////////////////////////////////////////////////////////
// DATABASE.PHP - TUTORIAL PART 2
////////////////////////////////////////////////////////

// This will connect us to our database...
mysql_connect("localhost", "user", "password") or
die("Could not connect: " . mysql_error());
mysql_select_db("test");

// The ext grid script will send a task field which will specify what it wants to do
$task = '';
if ( isset($_POST['task'])){
$task = $_POST['task'];
}
switch($task){
case "LISTING":
getList();
break;
default:
echo "{failure:true}";
break;
}

function getList()
{
$taxRate = 8.00;
$subtractedholiday1 = 8.00;
$p1subtractedsickdays = 12.00;
$p2subtractedsickdays = 16.00;


$table = 'yearlysalary_copy';
$query = "SELECT * FROM $table";
$result = mysql_query($query);
$nbrows = mysql_num_rows($result);
if($nbrows>0){
while($rec = mysql_fetch_array($result)){
// render the right date format
$price = $rec['Part2 Year Jan 1 - Aug 31'];
$bill = $rec['hourlypayp1'];
$holidays = $rec['holiday'];
$sickdays = $rec['sickdays'];
$summerdays = $rec['summerdays'];
$nightp1rate = $rec['nightp1'];
$bill2 = $rec['hourlypayp2'];
$p2totalhours = $rec['Part1 Year Sept 1- Dec 31'];
$nightp2rate = $rec['nightp2'];


$rec['p1holidays'] = round(($holidays-$subtractedholiday1));
$rec['p1sickdays'] = round(($sickdays-$p1subtractedsickdays));
$rec['p1nightdays'] = round($price-($holidays-$subtractedholiday1)-($sickdays-$p1subtractedsickdays)-$summerdays);
$rec['hoursjantoaug'] = round(8,2);
$rec['p1nighthours'] = round(($price-($holidays-$subtractedholiday1)-($sickdays-$p1subtractedsickdays)-$summerdays)*$taxRate);
$rec['salarynightp1'] = round((($price-($holidays-$subtractedholiday1)-($sickdays-$p1subtractedsickdays)-$summerdays)*$taxRate)*$nightp1rate,2);
$rec['p2totalhours1'] = round($p2totalhours*($taxRate),2);
$rec['p2sickdays'] = round(($sickdays-$p2subtractedsickdays));
$rec['p2nighthours'] = round(($p2totalhours-($holidays-$subtractedholiday1)-($sickdays-$p2subtractedsickdays))*$taxRate);
$rec['salarynightp2'] = round((($p2totalhours-($holidays-$subtractedholiday1)-($sickdays-$p2subtractedsickdays))*$taxRate)*$nightp2rate,2);
$rec['tnightsal'] = round(((($p2totalhours-($holidays-$subtractedholiday1)-($sickdays-$p2subtractedsickdays))*$taxRate)*$nightp2rate)+((($price-($holidays-$subtractedholiday1)-($sickdays-$p1subtractedsickdays)-$summerdays)*$taxRate)*$nightp1rate),2);
$rec['totalsalary'] = round(($bill * $price * ($taxRate))+($bill2*$p2totalhours*($taxRate)),2);

$rec['tsalary'] = round(((((($p2totalhours -($holidays - $subtractedholiday1)-($sickdays - $p2subtractedsickdays))*$taxRate)*$nightp2rate)+((($price-($holidays-$subtractedholiday1)-($sickdays-$p1subtractedsickdays)-$summerdays)*$taxRate)*$nightp1rate))+(($bill * $price * ($taxRate))+($bill2*$p2totalhours*($taxRate)))),2);
// $rec['tax'] = round($price * ($taxRate),2);
// $rec['firstpartsalary'] = round($bill * $price * ($taxRate),2);

// $rec['tookoffice']=codeDate($rec['tookoffice']);
// $rec['leftoffice']=codeDate($rec['leftoffice']);
$arr[] = $rec;
}
$jsonresult = JEncode($arr);
echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
} else {
echo '({"total":"0", "results":""})';
}
}

// Encodes a SQL array into a JSON formated string
function JEncode($arr){
if (version_compare(PHP_VERSION,"5.2","<"))
{
require_once("./JSON.php"); //if php<5.2 need JSON class
$json = new Services_JSON();//instantiate new json object
$data=$json->encode($arr); //encode the data in json format
} else
{
$data = json_encode($arr); //encode the data in json format
}
return $data;
}

// Encodes a YYYY-MM-DD into a MM-DD-YYYY string
function codeDate ($date) {
$tab = explode ("-", $date);
$r = $tab[1]."/".$tab[2]."/".$tab[0];
return $r;
}

// Encodes a MM-DD-YYYY into a YYYY-MM-DD string
function decodeDate ($date) {
$tab = explode ("/", $date);
$n = count($tab);
if($n==3) {
$r = $tab[2]."-".$tab[0]."-".$tab[1];
} else {
$r = "";
}
return $r;
}

?>

Street14
8 Sep 2010, 11:59 AM
var PresidentsDataStore;
var PresidentsColumnModel;
var PresidentListingEditorGrid;
var PresidentListingWindow;

Ext.onReady(function(){

Ext.QuickTips.init();

PresidentsDataStore = new Ext.data.Store({
id: 'PresidentsDataStore',
proxy: new Ext.data.HttpProxy({
url: 'database.php',
method: 'POST'
}),
baseParams:{task: "LISTING"}, // this parameter is passed for any HTTP request
reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total',
id: 'id'
},[
{name: 'id', type: 'int', mapping: 'id'},
{name: 'age', type: 'int', mapping: 'age'},
{name: 'Raise Year', type: 'date',dateFormat:'Y'},
{name: 'Part2 Year Jan 1 - Aug 31', type: 'int'},
{name: 'p1holidays', type: 'int'},
{name: 'p1sickdays', type: 'int'},
{name: 'summerdays', type: 'int'},
{name: 'p1nightdays', type: 'int'},
{name: 'hoursjantoaug', type: 'int'},
{name: 'p1nighthours', type: 'int'},
{name: 'nightp1', type: 'float'},
{name: 'salarynightp1', type: 'float'},

{name: 'Part1 Year Sept 1- Dec 31', type: 'int'},
{name: 'p1holidays', type: 'int'},
{name: 'p2sickdays', type: 'int'},
{name: 'p2nighthours', type: 'int'},
{name: 'nightp2', type: 'float'},
{name: 'salarynightp2', type: 'float'},
{name: 'tnightsal', type: 'float'},
{name: 'totalsalary', type: 'float'},
{name: 'tsalary', type: 'float'}

]),
// sortInfo:{field: 'id', direction: "ASC"}
});

function renderDate(value){return value ? value.dateFormat('Y') : '';};
function renderZero(val){if(val <= 0){return '<span style="color:green;">' + 0 + '</span>';}return val;};

PresidentsColumnModel = new Ext.grid.ColumnModel(
[
{align: 'right',header: "ID" ,dataIndex: 'id' ,width: 25},
{align: 'right',header: "Age" ,dataIndex: 'age' ,width: 30},
{align: 'right',header: "RYear" ,dataIndex: 'Raise Year' ,renderer: renderDate ,width: 50},
{align: 'right',header: "P1D" ,dataIndex: 'Part2 Year Jan 1 - Aug 31' ,width: 30},
{align: 'right',header: "P1H" ,dataIndex: 'p1holidays' ,width: 30},
{align: 'right',header: "P1S" ,dataIndex: 'p1sickdays' ,width: 30},
{align: 'right',header: "Sum" ,dataIndex: 'summerdays' ,width: 30},
{align: 'right',header: "TP1ND" ,dataIndex: 'p1nightdays' ,width: 50},
{align: 'right',header: "Hrs" ,dataIndex: 'hoursjantoaug' ,width: 30},
{align: 'right',header: "TP1NH" ,dataIndex: 'p1nighthours' ,width: 50},
{align: 'right',header: "$P1N" ,dataIndex: 'nightp1' ,renderer: Ext.util.Format.usMoney ,width: 40},
{align: 'right',header: "$P1NSal" ,dataIndex: 'salarynightp1' ,renderer: Ext.util.Format.usMoney ,width: 50},
{align: 'right',header: "P2D" ,dataIndex: 'Part1 Year Sept 1- Dec 31' ,width: 30},
{align: 'right',header: "P2H" ,dataIndex: 'p1holidays' ,width: 30},
{align: 'right',header: "P2Si" ,dataIndex: 'p2sickdays' ,width: 30},
{align: 'right',header: "TP2NH" ,dataIndex: 'p2nighthours' ,renderer: renderZero ,width: 50},
{align: 'right',header: "$P2N" ,dataIndex: 'nightp2' ,renderer: Ext.util.Format.usMoney ,width: 60},
{align: 'right',header: "$P2NSal" ,dataIndex: 'salarynightp2' ,renderer: Ext.util.Format.usMoney ,width: 70},
{align: 'right',header: "$NSal" ,dataIndex: 'tnightsal' ,renderer: Ext.util.Format.usMoney ,width: 70},
{align: 'right',header: "$Base" ,dataIndex: 'totalsalary' ,renderer: Ext.util.Format.usMoney ,width: 70},
{align: 'right',header: "$N+B$=$T",dataIndex: 'tsalary' ,renderer: Ext.util.Format.usMoney ,width: 70}
]
);

PresidentsColumnModel.defaultSortable= true;

PresidentListingEditorGrid = new Ext.grid.EditorGridPanel({
id: 'PresidentListingEditorGrid',
store: PresidentsDataStore,
cm: PresidentsColumnModel,
enableColLock:false,
clicksToEdit:1,
selModel: new Ext.grid.RowSelectionModel({singleSelect:false})
});

PresidentListingWindow = new Ext.Window({
id: 'PresidentListingWindow',
title: 'The Presidents of the USA',
closable:true,
width:950,
height:350,
plain:true,
layout: 'fit',
items: PresidentListingEditorGrid
});

PresidentsDataStore.load();
PresidentListingWindow.show();

});







<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Tutorial Part Three : Grid / PHP / SQL For Newbies</title>
<!--
<link rel="stylesheet" type="text/css" href="resources/css/ext-all.css"/>

<script type="text/javascript" src="adapter/ext/ext-base.js"></script>
-->
<script type="text/javascript" src="../../../../adapter/ext/ext-base.js"></script>

<script type="text/javascript" src="../../../../ext-all.js"></script>

<link rel="stylesheet" type="text/css" href="../../../../resources/css/ext-all.css" />


<!--
<script type="text/javascript" src="../../../..ext-all.js"></script>
-->
<script type="text/javascript" src="mainscript.js"></script>

</head>
<body>
</body>
</html>

Street14
8 Sep 2010, 12:00 PM
Ill take a look at at the literemotecomponent plugin. Hopefull i can understand it with out to much problem.

Street14
8 Sep 2010, 11:02 PM
LiteRemoteComponent plugin.
Spent a few hours looking and playing with this.
Either im missing something or this primarily loads data directly formatted from json generated file.
It will also work with already asp or php file or even a html file that have layouts already applied to them.
Though from what i have read it looks like it might work with ext grids from a json file.
I was hopeful to find a way for portability sake to incorporate other components directly into a portal.
As with grids from a db as in my example can be generated like this through a js file.
Seeing working with the js file seems to be a bit easier to navigate through. Im still hopeful to find a way to fire up the grid in a portal by just using the js file.

Condor
9 Sep 2010, 12:10 AM
I can't read PHP. Can you post what that PHP file actually returns (e.g. copy from the Firebug Net tab)?

Street14
9 Sep 2010, 8:02 AM
Sure what part of the net tab would you like to see.
Just a basic explanation of the php file first though.
It is nothing more then a MySQL query though. It query's the MySQL and lists the identifiers for each individual query.
Each individual query actually is one of the columns in the expected ext js grid.
At the bottom of the php file is the json encode parameters that is passed on to the json reader in the js file which then is formatted into the column model of the ext js grid.
Here is the HTML from the Net tab


({"total":"10","results":[{"0":"1","id":"1","1":"56","age":"56","2":"2010","Raise Year":"2010","3":"88","Part1 Year Sept 1- Dec 31":"88","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"19.54","hourlypayp1":"19.54","6":"20.13","hourlypayp2":"20.13","7":"0.79","nightp1":"0.79","8":"0.81","nightp2":"0.81","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":669.92,"p2totalhours1":704,"p2sickdays":8,"p2nighthours":600,"salarynightp2":486,"tnightsal":1155.92,"totalsalary":41214.88,"tsalary":42370.8},{"0":"2","id":"2","1":"57","age":"57","2":"2011","Raise Year":"2011","3":"88","Part1 Year Sept 1- Dec 31":"88","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"20.13","hourlypayp1":"20.13","6":"20.73","hourlypayp2":"20.73","7":"0.81","nightp1":"0.81","8":"0.83","nightp2":"0.83","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":686.88,"p2totalhours1":704,"p2sickdays":8,"p2nighthours":600,"salarynightp2":498,"tnightsal":1184.88,"totalsalary":42453.84,"tsalary":43638.72},{"0":"3","id":"3","1":"58","age":"58","2":"2012","Raise Year":"2012","3":"87","Part1 Year Sept 1- Dec 31":"87","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"20.73","hourlypayp1":"20.73","6":"21.04","hourlypayp2":"21.04","7":"0.83","nightp1":"0.83","8":"0.85","nightp2":"0.85","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":703.84,"p2totalhours1":696,"p2sickdays":8,"p2nighthours":592,"salarynightp2":503.2,"tnightsal":1207.04,"totalsalary":43334.16,"tsalary":44541.2},{"0":"4","id":"4","1":"59","age":"59","2":"2013","Raise Year":"2013","3":"86","Part1 Year Sept 1- Dec 31":"86","4":"175","Part2 Year Jan 1 - Aug 31":"175","5":"21.04","hourlypayp1":"21.04","6":"21.36","hourlypayp2":"21.36","7":"0.85","nightp1":"0.85","8":"0.87","nightp2":"0.87","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":108,"hoursjantoaug":8,"p1nighthours":864,"salarynightp1":734.4,"p2totalhours1":688,"p2sickdays":8,"p2nighthours":584,"salarynightp2":508.08,"tnightsal":1242.48,"totalsalary":44151.68,"tsalary":45394.16},{"0":"5","id":"5","1":"60","age":"60","2":"2014","Raise Year":"2014","3":"88","Part1 Year Sept 1- Dec 31":"88","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"21.36","hourlypayp1":"21.36","6":"21.68","hourlypayp2":"21.68","7":"0.87","nightp1":"0.87","8":"0.89","nightp2":"0.89","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":737.76,"p2totalhours1":704,"p2sickdays":8,"p2nighthours":600,"salarynightp2":534,"tnightsal":1271.76,"totalsalary":44824.96,"tsalary":46096.72},{"0":"6","id":"6","1":"61","age":"61","2":"2015","Raise Year":"2015","3":"88","Part1 Year Sept 1- Dec 31":"88","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"21.68","hourlypayp1":"21.68","6":"22.00","hourlypayp2":"22.00","7":"0.89","nightp1":"0.89","8":"0.91","nightp2":"0.91","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":754.72,"p2totalhours1":704,"p2sickdays":8,"p2nighthours":600,"salarynightp2":546,"tnightsal":1300.72,"totalsalary":45493.12,"tsalary":46793.84},{"0":"7","id":"7","1":"62","age":"62","2":"2016","Raise Year":"2016","3":"87","Part1 Year Sept 1- Dec 31":"87","4":"174","Part2 Year Jan 1 - Aug 31":"174","5":"22.00","hourlypayp1":"22.00","6":"22.33","hourlypayp2":"22.33","7":"0.91","nightp1":"0.91","8":"0.93","nightp2":"0.93","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":107,"hoursjantoaug":8,"p1nighthours":856,"salarynightp1":778.96,"p2totalhours1":696,"p2sickdays":8,"p2nighthours":592,"salarynightp2":550.56,"tnightsal":1329.52,"totalsalary":46165.68,"tsalary":47495.2},{"0":"8","id":"8","1":"63","age":"63","2":"2017","Raise Year":"2017","3":"86","Part1 Year Sept 1- Dec 31":"86","4":"174","Part2 Year Jan 1 - Aug 31":"174","5":"22.33","hourlypayp1":"22.33","6":"22.67","hourlypayp2":"22.67","7":"0.93","nightp1":"0.93","8":"0.95","nightp2":"0.95","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":107,"hoursjantoaug":8,"p1nighthours":856,"salarynightp1":796.08,"p2totalhours1":688,"p2sickdays":8,"p2nighthours":584,"salarynightp2":554.8,"tnightsal":1350.88,"totalsalary":46680.32,"tsalary":48031.2},{"0":"9","id":"9","1":"64","age":"64","2":"2018","Raise Year":"2018","3":"86","Part1 Year Sept 1- Dec 31":"86","4":"175","Part2 Year Jan 1 - Aug 31":"175","5":"22.67","hourlypayp1":"22.67","6":"23.01","hourlypayp2":"23.01","7":"0.95","nightp1":"0.95","8":"0.97","nightp2":"0.97","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"0","subtractholiday":"0","14":"0","subtractsickdays":"0","p1holidays":5,"p1sickdays":12,"p1nightdays":108,"hoursjantoaug":8,"p1nighthours":864,"salarynightp1":820.8,"p2totalhours1":688,"p2sickdays":8,"p2nighthours":584,"salarynightp2":566.48,"tnightsal":1387.28,"totalsalary":47568.88,"tsalary":48956.16},{"0":"10","id":"10","1":"65","age":"65","2":"2019","Raise Year":"2019","3":"0","Part1 Year Sept 1- Dec 31":"0","4":"173","Part2 Year Jan 1 - Aug 31":"173","5":"23.01","hourlypayp1":"23.01","6":"0.00","hourlypayp2":"0.00","7":"0.97","nightp1":"0.97","8":"0.00","nightp2":"0.00","9":"25","vacation":"25","10":"13","holiday":"13","11":"50","summerdays":"50","12":"24","sickdays":"24","13":"5","subtractholiday":"5","14":"8","subtractsickdays":"8","p1holidays":5,"p1sickdays":12,"p1nightdays":106,"hoursjantoaug":8,"p1nighthours":848,"salarynightp1":822.56,"p2totalhours1":0,"p2sickdays":8,"p2nighthours":-104,"salarynightp2":0,"tnightsal":822.56,"totalsalary":31845.84,"tsalary":32668.4}]})

Street14
9 Sep 2010, 8:04 AM
As you see it is nothing more then the data that would be passed on to the ext js script to be formatted and displayed in the ext js grid.