require(RODBC)

richness <- sqlQuery(sws, paste("SELECT bin, COUNT(species) as richness FROM vegetation_soilbins",
	"WHERE species <> 'unvegetated' GROUP BY bin"))
unvegetated <- sqlQuery(sws, paste("SELECT bin, SUM(cover) as unvegetated FROM vegetation_soilbins",
	"WHERE species = 'unvegetated' GROUP BY bin"))
total.cover <- sqlQuery(sws, paste("SELECT bin, SUM(cover) as total_cover FROM vegetation_soilbins",
	"WHERE species <> 'unvegetated' GROUP BY bin"))
forb <- sqlQuery(sws, paste(
	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as forb ",
	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
	"ON vegetation_soilbins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'forb'",
	"GROUP BY bin"))
graminoid <- sqlQuery(sws, paste(
	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as graminoid ",
	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
	"ON vegetation_soilbins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'graminoid'",
	"GROUP BY bin"))
shrub <- sqlQuery(sws, paste(
	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as shrub ",
	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
	"ON vegetation_soilbins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'shrub'",
	"GROUP BY bin"))
Nfixer <- sqlQuery(sws, paste(
	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Nfixer ",
	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
	"ON vegetation_soilbins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'Nfixer'",
	"GROUP BY bin"))

vs.sum <- merge(richness, unvegetated, all = T)
vs.sum <- merge(vs.sum, total.cover, all = T)
vs.sum <- merge(vs.sum, forb, all = T)
vs.sum <- merge(vs.sum, graminoid, all = T)
vs.sum <- merge(vs.sum, shrub, all = T)
vs.sum <- merge(vs.sum, Nfixer, all = T)
vs.sum[(is.na(vs.sum) == T)] <- 0

richness <- sqlQuery(sws, paste("SELECT resin, COUNT(species) as richness FROM vegetation_resins",
	"WHERE species <> 'unvegetated' GROUP BY resin"))
unvegetated <- sqlQuery(sws, paste("SELECT resin, SUM(cover) as unvegetated FROM vegetation_resins",
	"WHERE species = 'unvegetated' GROUP BY resin"))
total.cover <- sqlQuery(sws, paste("SELECT resin, SUM(cover) as total_cover FROM vegetation_resins",
	"WHERE species <> 'unvegetated' GROUP BY resin"))
forb <- sqlQuery(sws, paste(
	"SELECT vegetation_resins.resin, SUM(vegetation_resins.cover) as forb ",
	"FROM vegetation_resins LEFT JOIN plant_traits ",
	"ON vegetation_resins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'forb'",
	"GROUP BY resin"))
graminoid <- sqlQuery(sws, paste(
	"SELECT vegetation_resins.resin, SUM(vegetation_resins.cover) as graminoid ",
	"FROM vegetation_resins LEFT JOIN plant_traits ",
	"ON vegetation_resins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'graminoid'",
	"GROUP BY resin"))
shrub <- sqlQuery(sws, paste(
	"SELECT vegetation_resins.resin, SUM(vegetation_resins.cover) as shrub ",
	"FROM vegetation_resins LEFT JOIN plant_traits ",
	"ON vegetation_resins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'shrub'",
	"GROUP BY resin"))
Nfixer <- sqlQuery(sws, paste(
	"SELECT vegetation_resins.resin, SUM(vegetation_resins.cover) as Nfixer ",
	"FROM vegetation_resins LEFT JOIN plant_traits ",
	"ON vegetation_resins.species = plant_traits.species",
	"WHERE plant_traits.functional = 'Nfixer'",
	"GROUP BY resin"))

vr.sum <- merge(richness, unvegetated, all = T)
vr.sum <- merge(vr.sum, total.cover, all = T)
vr.sum <- merge(vr.sum, forb, all = T)
vr.sum <- merge(vr.sum, graminoid, all = T)
vr.sum <- merge(vr.sum, shrub, all = T)
vr.sum <- merge(vr.sum, Nfixer, all = T)
vr.sum[(is.na(vr.sum) == T)] <- 0

rm(richness, unvegetated, total.cover, forb, graminoid, shrub, Nfixer)


#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Asteraceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Asteraceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Asteraceae'",
#	"GROUP BY bin"))
#
#Boraginaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Boraginaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Boraginaceae'",
#	"GROUP BY bin"))
#
#Brassicaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Brassicaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Brassicaceae'",
#	"GROUP BY bin"))
#
#Campanulaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Campanulaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Campanulaceae'",
#	"GROUP BY bin"))
#
#Caprifoleaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Caprifoleaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Caprifoleaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#
#Apiaceae <- sqlQuery(sws, paste(
#	"SELECT vegetation_soilbins.bin, SUM(vegetation_soilbins.cover) as Apiaceae ",
#	"FROM vegetation_soilbins LEFT JOIN plant_traits ",
#	"ON vegetation_soilbins.species = plant_traits.species",
#	"WHERE plant_traits.family = 'Apiaceae'",
#	"GROUP BY bin"))
#