|
|
I have done this, and it works great.
First off, I had to create a table for logging jobs, so I made a table called jobs.
There are a lot more tables linked to it for adding customers, sites and engineers.
CREATE TABLE IF NOT EXISTS `job` (
`Order_Date` datetime DEFAULT NULL,
`AttendBy` date DEFAULT NULL,
`End_Date` date DEFAULT NULL,
`File_No` varchar(50) DEFAULT NULL,
`Job_No` int(50) NOT NULL AUTO_INCREMENT,
`CustomerId` int(255) DEFAULT NULL,
`Order_Time` varchar(50) DEFAULT NULL,
`Contract` longtext,
`Order_Site_Address` varchar(200) DEFAULT NULL,
`Job_Description` longtext,
`VAT` decimal(7,2) DEFAULT NULL,
`Invoice_Printed` tinyint(1) DEFAULT NULL,
`Invoice_Printing_Date` date DEFAULT NULL,
`Invoice_Tax_Date` date DEFAULT NULL,
`Payment_Received` tinyint(1) DEFAULT NULL,
`Job_Finished` tinyint(1) DEFAULT NULL,
`CustomerRef` varchar(50) DEFAULT NULL,
`Customer_Name` varchar(50) DEFAULT NULL,
`Payment_Due_date` date DEFAULT NULL,
`Payment_Date` date DEFAULT NULL,
`Note` longtext,
`Date_Added` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`EngineerId` varchar(50) NOT NULL,
`PDAAllocation` varchar(100) DEFAULT NULL,
`LocationId` varchar(255) DEFAULT NULL,
`JobTypeId` varchar(50) NOT NULL,
`Job_Sheet` varchar(50) NOT NULL,
`Post_Code` varchar(200) DEFAULT NULL,
`Line1` varchar(200) DEFAULT NULL,
`DoorCode` varchar(200) DEFAULT NULL,
`GateCode` varchar(200) DEFAULT NULL,
`AlarmCode` varchar(200) DEFAULT NULL,
`ExtraCodes` varchar(200) DEFAULT NULL,
`Exported` tinyint(1) DEFAULT NULL,
`Logged_By` varchar(200) DEFAULT NULL,
`PhoneNumber` varchar(20) DEFAULT NULL,
`CRB_No` varchar(50) DEFAULT NULL,
`CustomerEmail` varchar(200) DEFAULT NULL,
`Engineer_Note` longtext,
`ResponseCatagories` tinyint(1) DEFAULT NULL,
`CallerName` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Job_No`),
UNIQUE KEY `File_No` (`File_No`),
KEY `Order_Site_Address` (`Order_Site_Address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1028081 ;
I then made another two tables,
Materials
CREATE TABLE IF NOT EXISTS `material` (
`Title` varchar(500) NOT NULL,
`Job_No` varchar(50) NOT NULL,
`Quantity` double(7,2) DEFAULT NULL,
`Unit_Price` double(7,2) DEFAULT NULL,
`Additional_Cost` double(7,2) DEFAULT NULL,
`Description` varchar(500) DEFAULT NULL,
`Date` datetime DEFAULT NULL,
`upload` longtext,
`File Name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`Title`,`Job_No`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
And Labour
CREATE TABLE IF NOT EXISTS `labour` (
`Job_No` varchar(50) NOT NULL,
`Engineer_Name` varchar(50) NOT NULL,
`Paying_Rate` double(7,2) DEFAULT NULL,
`EngineerId` int(10) DEFAULT NULL,
`Working_Hrs` double(7,2) DEFAULT NULL,
`Sheet_Returned` tinyint(1) DEFAULT NULL,
`Invoice` double(7,2) DEFAULT NULL,
`Send_To_Invoice` tinyint(2) DEFAULT NULL,
`PDAAllocation` varchar(100) DEFAULT NULL,
PRIMARY KEY (`Job_No`,`Engineer_Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Now, A caller rings in, and the job gets logged into our system, it then gets exported as an XML file and after 5 mins, it will popup on the engineers PDA.
The engineer will complete the job and get the customer to sign on the PDA screen, this then gets sent back to our server where a VB script runs and imports the data back into our database.
The imported data can be viewed as a child table to the job table, where the amount of hours spent on the job can be added inline to the labour table, and any materials can also be added to the materials table.
Now, in PHPRunner, we make a Custom view of the job table and call it Invoice.
We go into the SQL editor and change the SQL to read this.
SELECT
IFNULL((SELECT sum(Invoice) FROM labour WHERE Job_No = job.Job_No),0)AS Labsubtotal,
IFNULL((SELECT ROUND(sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)),2)FROM material WHERE Job_No = job.Job_No),0)AS Matsubtotal,
(SELECT Labsubtotal+Matsubtotal)AS Subtotal,
(SELECT Subtotal*VAT/100)AS VATAmount,
(SELECT Subtotal+VATAmount)AS Total,
IFNULL((select sum(Working_Hrs) from labour where Job_No = job.Job_No),0) as JSH,
job.Invoice_Tax_Date,
job.Job_No,
job.Customer_Name,
job.Order_Site_Address,
job.Contract,
job.CustomerRef,
job.Order_Date,
job.File_No,
job.VAT,
customer.Line1,
customer.Line2,
customer.Line3,
customer.Line4,
customer.Line5,
customer.Line6,
sites.SLine1,
sites.SLine2,
sites.SLine3,
sites.SLine4,
sites.SLine5
FROM job
INNER JOIN customer ON job.Customer_Name = customer.Customer_Name
INNER JOIN sites ON job.LocationId = sites.LocationId
This now displays everything apart from the materials, so in the visual editor for the view page of the invoice, we place this code.
global $conn;
$sql = "select Title from material where job_no='".postvalue("editid1")."'";
$rs = db_query($sql,$conn);
$data = db_fetch_array($rs);
if(!$data)
return;
echo "<table>";
while(1)
{
echo "<tr>";
foreach($data as $v)
{
echo "<td>";
echo htmlspecialchars($v);
echo "</td>";
}
echo "</tr>";
if(!($data = db_fetch_array($rs)))
break;
}
echo "</table>";
This will now list all the materials used on that job.
We build the project and go into the templates and edit the invoice_view.html. I use frontpage and drag the fields around until I get it to look like this, and print it out on A4 or email the page to the client.
|