Skip to content

Instantly share code, notes, and snippets.

@TheLarkInn
Created January 21, 2014 14:04
Show Gist options
  • Save TheLarkInn/8540654 to your computer and use it in GitHub Desktop.
Save TheLarkInn/8540654 to your computer and use it in GitHub Desktop.
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…
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment