Appointments is a class declared in my Sinatra app, its used to query my db using Sequel based upon the parameters that are given. I want to be able to have either 0 or 6 parameters and dynamically make the query reflect that. The second file is the route where the class method is called, I format the hash before hand so I can take the key and v…

app.rb

get '/tt_pt_api/api/v1/appointments' do

	params_hash = {
		"appointment.ticket_id"				=> params[:ticket_id], 
		"u_user.user_id"					=> params[:user_id], 
		"account.serial_num"				=> params[:serial_num], 
		"appointment.appointment_type_id" 	=> params[:appointment_type_id]
	}

	data = Appointments.appointments_with_params(params[:start_date], params[:end_date], params_hash)

	{appointments: data}.to_json
end

appointments.rb

class Appointments
	def self.appointments_with_params(start_date, end_date, params_hash)

		select_query = "SELECT 
				appointment_id,
				appointment.appointment_type_id,
				appointment_type.appointment_desc as appointment_type,
				appointment.appointmentstatus_id as appointment_status_id,	
				appointmentstatus.a_description as appointment_status,
				start,
				end,
				notes,
				created_user_id,
				c_user.username as created_username,
				concat(c_user.firstname,' ',c_user.lastname) as created_user,
				updated_user_id,
				u_user.username as updated_username,
				concat(u_user.firstname,' ',u_user.lastname) as updated_user,
				appointment.resource_id,
				resource.name as resource_name,
				patient_id as account_id,
				account.first as account_first,
				account.last as account_last,
				account.officename,
				account.serial_num,
				account.phone1 as office_phone,
				account.phone1ext as office_ext,
				appointment.created,
				appointment.last_updated,
				appointment.status_last_updated,
				appointment.provider_id,
				concat(p_user.firstname,' ',p_user.lastname) as provider_fullname,
				appointment.ticket_id
			FROM appointment
			LEFT JOIN account on account.account_id = appointment.patient_id
			LEFT JOIN appointment_type on appointment_type.appointment_type_id = appointment.appointment_type_id
			LEFT JOIN appointmentstatus on appointmentstatus.appointmentstatus_id = appointment.appointmentstatus_id
			LEFT JOIN user as c_user on c_user.user_id = appointment.created_user_id
			LEFT JOIN user as u_user on u_user.user_id = appointment.updated_user_id
			LEFT JOIN user as p_user on p_user.user_id = appointment.provider_id
			LEFT JOIN resource on resource.resource_id = appointment.resource_id"

		
		#add params to array and then separate them by " AND " to string them together for query

		start_d = start_date ? " DATE(appointment.start) >= DATE('#{start_date}')" : nil
		end_d = end_date ? " DATE(appointment.start) <= DATE('#{end_date}')" : nil

		if params_hash.values.any?
			params_array = params_hash.map{|k,v| "#{k} = '#{v}'" if v}
		else 
			params_array = []
		end

		params_array << start_d
		params_array << end_d

		puts params_array

		full_query = select_query

		unless params_array.compact.empty?
			where_query = " WHERE " + params_array.compact.join(" AND ")
			full_query = select_query + where_query
		end

		#build and run the query
		data = DB[full_query].all.map{ |row| 
			row.keys.each{|key|
				row[key] = row[key].to_utf8 if row[key].is_a?(String)
			}
			row
		}
		data
	end
end