Original Source
https://pdocrud.com/demo/pages/trigger-other-operation
https://pdocrud.com/demo/pages/raw-data-operations
https://pdocrud.com/demo/pages/export-print-heading
https://pdocrud.com/demo/pages/dynamic-invoice
Trigger Other Operation
Many times, you want to perform insert/update/delete operation in other table than main table also. For example, after entering employee leave date in "leaves" table, you might want to update total leaves taken by that employee in employee table also. One way is to do this is using the callback function, but you have to write quite a lines of code there. Now same thing can be achieved using the setTriggerOperation() function. You can specify tablename, column data, where data, type of operation and event to perform this operation.
$pdocrud = new PDOCrud();
$pdocrud->setTriggerOperation("student", array("total_attendance" => array("type" => "fixed", "val" => 10)),
array(), "insert", "after_insert");
// $pdocrud->setTriggerOperation("student", array("total_attendance" => array("type" => "last_insert_id")),
// array(), "insert", "after_insert");
// $pdocrud->setTriggerOperation("student", array("total_attendance" => array("type" => "array_data", "val" => "student_id")),
// array(), "insert", "before_insert");
// $pdocrud->setTriggerOperation("student", array("total_attendance" => array("type" => "fixed", "val" => 12)),
// array("student_id"=> array("type" => "array_data", "val" => "student_id")), "update", "after_update");
echo $pdocrud->dbTable("attendance")->render();
Raw Data Operation
PDOCrud uses PDOModel for database related operations. It supports all pdomodel functions. You can refer pdomodel documentation here.
$pdocrud = new PDOCrud();
//insert operation
$insertData = array("customer_name" => "bob", "email" => "builder@gmail.com", "postal_code" =>99423);
$pdocrud->getPDOModelObj()->insert("customertable", $insertData);
//update operation
//Example 2
$updateData = array("first_name"=>"Jon", "last_name"=>"snow", "email"=>"builder@gmail.com");
$pdomodel = $pdocrud->getPDOModelObj();
$pdomodel->where("id", 1);
$pdomodel->update("feedback", $updateData);
//Example 3
//select operation
$data = $pdocrud->getPDOModelObj()->select("orders");
print_r($data);
Set Column Heading for export/print
You can change the column heading for excel,csv,pdf export and print using this function.
$pdocrud = new PDOCrud();
$pdocrud->exportColHeading("customerName", "Client Name");
echo $pdocrud->dbTable("orders")->render();
Dynamic Invoices
Now, pdocrud support invoice generation directly from database. A new invoice layout is added to support the generation of invoice easily. Currently it is in beta release. We will release full featured invoice generation in next version. Please check the comments of the code to understand how it works.
PDF generation helps you automate various tasks including automatic calculations of various totals, generation of pdf in various invoice format. You will need xInvoice - Generate beautifully designed invoices dynamically in order to generate invoice pdf. You need to put xinvoice script in the library folder.
Database used for this demo contains 5 tables. You can design the database as you want.
Client table - Contains the information about client you are sending invoice
Company table - Contains the information about your company
Product table - Contains the information about the products
Order table - It contains the main details about the order. We will save main details like order number, date, total amount etc in this table
Order item table - It contains the information about items of order, it will be joined to main order table using order_id primary of main order table.
$pdocrud = new PDOCrud();
// set header fields of invoice. These fields basically will be shown before the
// items table. Rest fields will be shown after the items table
$headerFields = array("order_number", "order_date","company_id","client_id", "order_due_date");
$pdocrud->setInvoiceDetails($headerFields);
// This will add the print invoice pdf button. Please note that you need to
// buy xinvoice script to use this operation.
$text = '';
$pdocrud->enqueueBtnActions("printpdf", "javascript:;", "printpdf", $text);
// This is the sql that will be used to create the invoice pdf. You can learn
// more about the how to create this sql here
// http://xinvoice.biz/demo/pages/database-invoice-creation
$sql = "SELECT ot.order_number as invoice__no, ot.order_date as invoice__date, ot.order_due_date as invoice__due_date, ot.shipping as total__shipping, ot.tax as total__tax,ot.discount as total__discount, ot.order_total as total_grandtotal, ot.payment_method as payment__method1, co.company_name as from__name,co.address1 as from__address1, co.address2 as from_address2, co.city as from__city, co.country as from__country, co.state as from__state,co.email as from__email, cl.client_name as to__name, cl.address1 as to__address1, cl.address2 as to__address2, cl.city as to__city, cl.state as to__state, cl.country as to_country, cl.email as to__email, oi.item_name as item__name, oi.item_desc as item__desc, oi.item_qty as item__qty, oi.item_qty as item__qty, oi.item_rate as item__rate, oi.item_total as item__total FROM x_ordertable as ot INNER JOIN x_order_items as oi on ot.order_id = oi.order_id INNER JOIN x_company as co on ot.company_id = co.company_id INNER JOIN x_clients as cl on ot.client_id = cl.client_id where ot.order_id = {order_id}";
//set invoice complete data
$pdocrud->invoicePrint($sql);
// set javascript based calculations to dynamically calculation fields totals
$pdocrud->setJsActions("order_total", "{order_sub_total} + {tax} + {shipping} - {discount}","change");
// this is also dynamic calculation, You need to set the column
$pdocrud->setLeftJoinJsActions("col5", "col3 * col4","change", array("col3"=>"input","col4" => "input"));
// below functions are not required for invoice generation, you know these functions
// already. Since we are using company table and client table, we can easily manage
// this using our related table function.
$pdocrud->relatedData('company_id','x_company','company_id',array("company_name","address1", "city" ,"state", "country"));
$pdocrud->relatedData('client_id','x_clients','client_id',array("client_name","address1", "city" ,"state", "country"));
// Order table and order items table needs to be joined.
$pdocrud->joinTable("x_order_items", "x_order_items.order_id = x_ordertable.order_id", "LEFT JOIN");
// lets convert the item table to dropdown and get the data from products table.
$pdocrud->fieldTypes("item_name", "select");
$pdocrud->fieldDataBinding("item_name", "x_products", "product_id", array("product_name"), "db", "-", array(), array("product_name"));
echo $pdocrud->dbTable("x_ordertable")->render();
Date range wise report
You can directly add month wise, day wise, year wise (calendar as well day duration wise) report buttons to generate the table data based on the date range. Please note it work for the CRUD table not for SQL as sql statement can be of anytype.
$pdocrud = new PDOCrud();
/**
* Add date range report buttons (eg daily ,monthly ,yearly report button)
* @param string $text Name/Text of the button
* @param string $type Type of the report to be generated.
* return object Object of class
*/
$pdocrud->addDateRangeReport("This Year", "calendar_year", "order_date");
$pdocrud->addDateRangeReport("This Month", "calendar_month", "order_date");
$pdocrud->addDateRangeReport("Last 365 days", "year", "order_date");
$pdocrud->addDateRangeReport("Last 30 days", "month", "order_date");
$pdocrud->addDateRangeReport("1 Day", "Last 1 day", "order_date");
$pdocrud->addDateRangeReport("Today", "today", "order_date");
echo $pdocrud->dbTable("orders")->render();
Set Search Col Data Type
You can set search col data type to date/datetime/time etc to make search more friendly. Default type is text. For example When you set search column data type to date-range, it will show date range options.
$pdocrud = new PDOCrud();
//set the search column data type
$pdocrud->setSearchColumnDataType("order_date", "date-range");// other options are time-range, datetime-range
echo $pdocrud->dbTable("orders")->render();
SQL Operation
Render sql helps to display the data in grid/table format. You can write sql select statement to display data in the grid format. Please note that since sql statement can be of any type so the default grid function will not work with this sql render. From version 2.4, the pagination, records per page and display of total records is also removed. A better option to use the sql render with the jquery datatable plugin. You can see example here
$pdocrud = new PDOCrud();
$pdocrud->setQuery("select * from orders");
echo $pdocrud->render("SQL");
Subselect SQL
You can use the subselect query to generate columns dynamically. You can get data from other tables using the query. It accepts two parameters, first one is column name/alias and second is query.
$pdocrud = new PDOCrud();
$pdocrud->crudTableCol(array("first_name","last_name"));
/**
* Allows you to add the dynamic column based on sub query
* @param string $columnName Alias column name to be used for the query
* @param string $query Sub Query to be used
* return object Object of class
*/
$pdocrud->subQueryColumn("order_id", "select sum(id) from orders where customer_name = {user_name}");
echo $pdocrud->dbTable("users")->render()
