crud.cfc

<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>