Skip to content

Instantly share code, notes, and snippets.

Created Sep 18, 2014
Embed
What would you like to do?
<cfcomponent>
<cffunction name="getExpense" output="false" access="remote" returntype="query">
<cfset var expenses= "">
<cfset var result= "">
<cfquery name="expenses" datasource="moRentals">
select *
FROM Dashboard, Drivers, Machine
</cfquery>
<cfquery dbtype="query" name="results">
select ID, hireDate, machineID, driverid, distanceMoved, description, expense, revenue, expenseReason, subTotal, driver_id, fname + ' ' + lname AS fullname, picture, machine_ID, machineNumber, mileage
FROM expenses
WHERE driverid = driver_id
AND machineID = machine_ID
ORDER BY hireDate
</cfquery>
<cfreturn results>
</cffunction>
<cffunction name="drivers" output="false" access="remote" returntype="query">
<cfset var qdrivers = "" />
<cfquery datasource="moRentals" name="qdrivers">
SELECT driver_ID, fname + ' ' + lname AS fullname, age, birthday, phone, startdate, picture, salary, residence, permitStart, permitEnd, driverDetails
FROM Drivers
</cfquery>
<cfreturn qdrivers />
</cffunction>
<cffunction name="machine" output="no" access="remote" returntype="query">
<cfset var qMachine = "" />
<cfquery datasource="moRentals" name="qMachine">
SELECT *
FROM Machine
</cfquery>
<cfreturn qMachine />
</cffunction>
<!--- Function to add New Entry --->
<cffunction name="addEntry" access="remote">
<cfargument name="hireDate" type="string" required="true"/>
<cfargument name="machineID" type="string" required="true"/>
<cfargument name="driverid" type="string" required="true"/>
<cfargument name="distanceMoved" type="string" required="true"/>
<cfargument name="revenue" type="string" required="true"/>
<cfargument name="expense" type="string" required="true"/>
<cfargument name="expenseReason" type="string" required="true"/>
<cfargument name="subTotal" type="string" required="true"/>
<cfargument name="description" type="string" required="true"/>
<cfargument name="machine_ID" type="numeric" required="true"/>
<cfargument name="updatedMileage" type="string" required="true"/>
<cftransaction>
<cfquery name="qexpense" datasource="moRentals">
insert into Dashboard(hireDate, machineID, driverid, distanceMoved, revenue, expense, expenseReason, subTotal, description)
values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.hireDate#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.machineID#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.driverid#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.distanceMoved#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.revenue#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.expense#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.expenseReason#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.subTotal#"/>,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#arguments.description#">
)
</cfquery>
<cfquery datasource="moRentals">
update Machine
set mileage = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.updatedMileage#"/>
where machine_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.machine_ID#" />
</cfquery>
</cftransaction>
</cffunction>
<!--- Function to Update an Entry --->
<cffunction name="updateEntry" access="remote" returntype="query">
<cfargument name="hireDate" type="date" required="yes"/>
<cfargument name="machineID" type="numeric" required="yes"/>
<cfargument name="driverid" type="numeric" required="yes"/>
<cfargument name="distanceMoved" type="string" required="yes"/>
<cfargument name="revenue" type="string" required="true"/>
<cfargument name="expense" type="string" required="true"/>
<cfargument name="expenseReason" type="string" required="true"/>
<cfargument name="subTotal" type="string" required="true"/>
<!--- <cfargument name="description" type="string"/>--->
<cfargument name="ID" type="numeric" required="true">
<cfquery datasource="moRentals">
update Dashboard
set hireDate = <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.hireDate#"/>,
machineID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.machineID#"/>,
driverid = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.driverid#"/>,
distanceMoved = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.distanceMoved#"/>,
revenue = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.revenue#"/>,
expense = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.expense#"/>,
expenseReason = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.expenseReason#"/>,
subTotal = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.subTotal#"/>
where ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.ID#" />
</cfquery>
<cfreturn getExpense()/>
</cffunction>
</cfcomponent>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment