--8. Buatlah Stored Procedure bernama “spCariPesananSendJek”
--Setiap pengemudi yang statusnya Tersedia dan punya saldo cukup dapat mencari pesanan Send Jek yang menunggu untuk dilayani.
create procedure spCariPesananSendjek (@saldo int, @status nvarchar(20))
as
begin
	if(@saldo >= 50000) and @status = 'Tersedia'
	begin
		select * from sendjek where idstatus = '001'
	end
	else
	begin
		print 'Saldo anda tidak mencukupi atau Status anda Tidak Tersedia'
	end
end

--28. Buatlah Stored Procedure bernama “spLihatDaftarPengemudi”
--Administrator bisa melihat daftar pengemudi Ohjek di kotanya sendiri diurutkan berdasarkan jumlah order yang dilayani terbanyak atau ratarata tertinggi nilai Rating yang diperoleh.
create procedure spLihatDaftarPengemudi
as
begin
	select id_kantor from administrator a inner join driver d on a.id_kantor = d.id_kantor
	order by
	((select Count(ID_Sendjek) from SendJek where ID_Driver = d.ID_Driver) + (select Count(ID_Ridejek) from RideJek where ID_Driver = d.ID_Driver) + (select Count(ID_Foodjek) from FoodJek where ID_Driver = d.ID_Driver)) desc
	, 	(((select Sum(Rating) from SendJek where ID_Driver = d.ID_Driver) + (select Sum(Rating) from RideJek where ID_Driver = d.ID_Driver) + (select Sum(Rating) from FoodJek where ID_Driver = d.ID_Driver))
	/ ((select Count(ID_Sendjek) from SendJek where ID_Driver = d.ID_Driver) + (select Count(ID_Ridejek) from RideJek where ID_Driver = d.ID_Driver) + (select Count(ID_Foodjek) from FoodJek where ID_Driver = d.ID_Driveer))) desc
end