PHP изчертаване на графика/и ...

[etsp]

В БД има следните таблици :



status в зависимост от стойността му при 0 – доставена, 1- потвърдена, 2 – завършена и отменена



Как мога примерно ако използвам колоните status и created_on ( или по някакъв друг начин) да се изчертае графика примерно с x и y координати.
До тук е направено следното:
- Показват се най-новите доставки/поръчки
- Показват се всички записи направени между дата1 и дата2
- Показват се поръчките по статус ( избор между – доставена, потвърдена, завършена и отменена
Как може да се обобщи тази информация чрез графика/и


Използвам следните сторед процедури за частта с датите и статуса:

CREATE PROCEDURE orders_get_orders_between_dates(
IN inStartDate DATETIME, IN inEndDate DATETIME)
BEGIN
SELECT o.order_id, o.total_amount, o.created_on,
o.shipped_on, o.status, c.name
FROM orders o
INNER JOIN customer c
ON o.customer_id = c.customer_id
WHERE o.created_on >= inStartDate AND o.created_on <= inEndDate
ORDER BY o.created_on DESC;
END$$

CREATE PROCEDURE orders_get_orders_by_status(IN inStatus INT)
BEGIN
SELECT o.order_id, o.total_amount, o.created_on,
o.shipped_on, o.status, c.name
FROM orders o
INNER JOIN customer c
ON o.customer_id = c.customer_id
WHERE o.status = inStatus
ORDER BY o.created_on DESC;
END$$

Това е кодът:

<?php
class Orders
{
public static $mOrderStatusOptions = array ('placed', // 0
'verified', // 1
'completed', // 2
'canceled'); // 3
// Get the most recent $how_many orders

public static function GetMostRecentOrders($how_many)
{
// Build the SQL query
$sql = 'CALL orders_get_most_recent_ordershow_many)';
// Build the parameters array
$params = array (':how_many' => $how_many);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}

// Get orders between two dates
public static function GetOrdersBetweenDates($startDate, $endDate)
{
// Build the SQL query
$sql = 'CALL orders_get_orders_between_datesstart_date, :end_date)';
// Build the parameters array
$params = array (':start_date' => $startDate, ':end_date' => $endDate);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
// Gets orders by status
public static function GetOrdersByStatus($status)
{
// Build the SQL query
$sql = 'CALL orders_get_orders_by_statusstatus)';
// Build the parameters array
$params = array (':status' => $status);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
}
?>


Визуалната част:

{load_presentation_object filename="admin_orders" assign="obj"}
{if $obj->mErrorMessage}<p class="error">{$obj->mErrorMessage}</p>{/if}
<form method="get" action="{$obj->mLinkToAdmin}">
<input name="Page" type="hidden" value="Orders" />
<p>
<font class="bold-text">Show the most recent</font>
<input name="recordCount" type="text" value="{$obj->mRecordCount}" />
<font class="bold-text">orders</font>
<input type="submit" name="submitMostRecent" value="Go!" />
</p>
<p>
<font class="bold-text">Show all records created between</font>
<input name="startDate" type="text" value="{$obj->mStartDate}" />
<font class="bold-text">and</font>
<input name="endDate" type="text" value="{$obj->mEndDate}" />
<input type="submit" name="submitBetweenDates" value="Go!" />
</p>
<p>
<font class="bold-text">Show orders by status</font>
{html_options name="status" options=$obj->mOrderStatusOptions
selected=$obj->mSelectedStatus}
<input type="submit" name="submitOrdersByStatus" value="Go!" />
</p>
</form>
{if $obj->mOrders}
<table class="tss-table">
<tr>
<th>Order ID</th>
<th>Date Created</th>
<th>Date Shipped</th>
<th>Status</th>
<th>Customer</th>
<th> </th>
</tr>
{section name=i loop=$obj->mOrders}
{assign var=status value=$obj->mOrders.status}
<tr>
<td>{$obj->mOrders.order_id}</td>
<td>{$obj->mOrders.created_on|date_format:"%Y-%m-%d %T"}</td>
<td>{$obj->mOrders.shipped_on|date_format:"%Y-%m-%d %T"}</td>
<td>{$obj->mOrderStatusOptions[$status]}</td>
<td>{$obj->mOrders.customer_name}</td>
<td align="right">
<a href="{$obj->mOrders.link_to_order_details_admin}">View Details</a>
</td>
</tr>
{/section}
</table>
{/if}



<?php
class AdminOrders
{
// Public variables available in smarty template
public $mOrders;
public $mStartDate;
public $mEndDate;
public $mRecordCount = 20;
public $mOrderStatusOptions;
public $mSelectedStatus = 0;
public $mErrorMessage = '';
public $mLinkToAdmin;
// Class constructor
public function __construct()
{
/* Save the link to the current page in the link_to_orders_admin
session variable; it will be used to create the
"back to admin orders ..." link in admin order details pages */
$_SESSION['link_to_orders_admin'] =
Link::Build(str_replace(VIRTUAL_LOCATION, '', getenv('REQUEST_URI')));
$this->mLinkToAdmin = Link::ToAdmin();
$this->mOrderStatusOptions = Orders::$mOrderStatusOptions;
}
public function init()
{
// If the "Show the most recent x orders" filter is in action ...
if (isset ($_GET['submitMostRecent']))
{
// If the record count value is not a valid integer, display error
if ((string)(int)$_GET['recordCount'] == (string)$_GET['recordCount'])
{
$this->mRecordCount = (int)$_GET['recordCount'];
$this->mOrders = Orders::GetMostRecentOrders($this->mRecordCount);
}
else
$this->mErrorMessage = $_GET['recordCount'] . ' is not a number.';
}
/* If the "Show all records created between date_1 and date_2"
filter is in action ... */
if (isset ($_GET['submitBetweenDates']))
{
$this->mStartDate = $_GET['startDate'];
$this->mEndDate = $_GET['endDate'];
// Check if the start date is in accepted format
if (($this->mStartDate == '') ||
($timestamp = strtotime($this->mStartDate)) == -1)
$this->mErrorMessage = 'The start date is invalid. ';
else
// Transform date to YYYY/MM/DD HH:MM:SS format
$this->mStartDate =
strftime('%Y/%m/%d %H:%M:%S', strtotime($this->mStartDate));
// Check if the end date is in accepted format
if (($this->mEndDate == '') ||
($timestamp = strtotime($this->mEndDate)) == -1)
$this->mErrorMessage .= 'The end date is invalid.';
else
// Transform date to YYYY/MM/DD HH:MM:SS format
$this->mEndDate =
strftime('%Y/%m/%d %H:%M:%S', strtotime($this->mEndDate));
// Check if start date is more recent than the end date
if ((empty ($this->mErrorMessage)) &&
(strtotime($this->mStartDate) > strtotime($this->mEndDate)))
$this->mErrorMessage .=
'The start date should be more recent than the end date.';
// If there are no errors, get the orders between the two dates
if (empty($this->mErrorMessage))
$this->mOrders = Orders::GetOrdersBetweenDates(
$this->mStartDate, $this->mEndDate);
}
// If "Show orders by status" filter is in action ...
if (isset ($_GET['submitOrdersByStatus']))
{
$this->mSelectedStatus = $_GET['status'];
$this->mOrders = Orders::GetOrdersByStatus($this->mSelectedStatus);
}
if (is_array($this->mOrders) && count($this->mOrders) == 0)
$this->mErrorMessage =
'No orders found matching your searching criteria!';
// Build View Details link
for ($i = 0; $i < count($this->mOrders); $i++)
{
$this->mOrders[$i]['link_to_order_details_admin'] =
Link::ToOrderDetailsAdmin($this->mOrders[$i]['order_id']);
}
}
}
?>

 

[proba]

http://www.jscharts.com/examples

https://google-developers.appspot.com/chart/interactive/docs/gallery/linechart

http://teethgrinder.co.uk/open-flash-chart/

Ето я и темата: http://web-tourist.net/forum/viewtopic.php?t=105190