SELECT
	subdistrict.subdistrict_name as kecamatan_name,
	villages.villages_name as kelurahan_name,
	mk.name as anggota_name,
	area.area_name as lahan_name,
    tree.id AS tree_id,
    tree.tree_number,
    villages.id,
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
FROM tree
    LEFT JOIN area ON area.id = tree.area_id
    LEFT JOIN species ON species.id = tree.species_id
    left join subdistrict on subdistrict.id = area.subdistrict_id
    left join villages on villages.id = area.villages_id
    left join member_koperasi mk on mk.id = area.member_id
WHERE
    tree.is_valid = 1
    AND tree.deleted = 0
    AND tree.verification_status = 1
    AND tree.species_id = :species_id
    AND tree.diameter BETWEEN :diameter_min AND :diameter_max
    and subdistrict.id = 3169
    and villages.id = 46880
group by
	villages.villages_name
order by
	volume desc;

select
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from tree
	left join area on area.id = tree.area_id
	left join member_koperasi mk on mk.id = area.member_id
where
	tree.is_valid = 1
	and tree.deleted = 0
	and tree.verification_status = 1
	and tree.species_id = 4
    AND tree.diameter BETWEEN 20 AND 100
	and mk.name = 'SLAMET,SP';

select *
from tree
	left join area on area.id = tree.area_id
where area.subdistrict_id = 93714;


-- lalalala dipsi
SELECT
    tree.id AS tree_id,
	subdistrict.subdistrict_name as kecamatan_name,
	villages.villages_name as kelurahan_name,
	mk.name as anggota_name,
	area.area_name as lahan_name,
    tree.tree_number,
    tree.diameter,
    tree.keliling,
    tree.`length` as panjang,
    3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6 volume
FROM tree
    LEFT JOIN area ON area.id = tree.area_id
    LEFT JOIN species ON species.id = tree.species_id
    left join subdistrict on subdistrict.id = area.subdistrict_id
    left join villages on villages.id = area.villages_id
    left join member_koperasi mk on mk.id = area.member_id
WHERE
    tree.is_valid = 1
    AND tree.deleted = 0
    AND tree.verification_status = 1
    AND tree.species_id = 4
    AND tree.diameter BETWEEN 20 AND 1000
    and subdistrict.id = 3172
    and villages.id = 46903
order by
	volume desc;


-- INSIDENTALalalala, pencarian ranking terlebih dahulu, pada saat memilih satu kecamatan
-- otomatis mencari kelurahan terbesar dan lahan terbesar

-- cari ranking kecamatan
select
	sd.id as kecamatan_id,
	sd.subdistrict_name as kecamatan,
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from
	tree
	left join area on area.id = tree.area_id
	left join subdistrict sd on sd.id = area.subdistrict_id
where
	tree.deleted = 0
	and tree.is_valid = 1
	and tree.verification_status = 1
	and tree.koperasi_id = :koperasi_id
group by sd.id
order by volume desc;

-- cari ranking kelurahan, berdasarkan kecamatan tertentu
select
	v.id as kelurahan_id,
	v.villages_name as kelurahan,
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from
	tree
	left join area on area.id = tree.area_id
	left join subdistrict sd on sd.id = area.subdistrict_id
	left join villages v on v.id = area.villages_id
where
	tree.deleted = 0
	and tree.is_valid = 1
	and tree.verification_status = 1
	and sd.id = :kecamatan_id
group by v.id
order by volume desc;

-- cari ranking anggota, berdasarkan kecamatan dan kelurahan tertentu
select
	mk.name as anggota_name,
	area.id as lahan_id,
	area.area_name as nama_lahan,
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from
	tree
	left join area on area.id = tree.area_id
	left join subdistrict sd on sd.id = area.subdistrict_id
	left join villages v on v.id = area.villages_id
	left join member_koperasi mk on mk.id = area.member_id
where
	tree.deleted = 0
	and tree.is_valid = 1
	and tree.verification_status = 1
	and sd.id = :kecamatan_id
	and v.id = :kelurahan_id
	and tree.diameter >= 20
group by mk.id
order by volume desc;

-- cari ranking lahan, berdasarkan kecamatan dan kelurahan tertentu
select
	mk.name as anggota_name,
	area.id as lahan_id,
	area.area_name as nama_lahan,
    sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from
	tree
	left join area on area.id = tree.area_id
	left join subdistrict sd on sd.id = area.subdistrict_id
	left join villages v on v.id = area.villages_id
	left join member_koperasi mk on mk.id = area.member_id
where
	tree.deleted = 0
	and tree.is_valid = 1
	and tree.verification_status = 1
	and sd.id = :kecamatan_id
	and v.id = :kelurahan_id
group by area.id
order by volume desc;


-- Update tree_booking_line firstbuy uhuy
select *
from price_farmer pf
	left join sortimen on round(tree.diameter) >= sortimen.diameter_min and round(tree.diameter) <= sortimen.diameter_max
where pf.koperasi_id = 1
	and pf.species_id = 4
 	and tree.id in (
		2854, 156451, 8456, 9624, 10319, 14330, 14416, 14539, 15463, 15805, 15956, 16974, 17354, 25089, 19303, 20321, 20669, 45005, 45085, 21718, 22192, 27125, 27693, 27747, 29652, 30060, 32053, 35395, 40236, 42410, 42534, 44401, 44787, 44911, 46760, 50069, 68689, 73835, 78388, 84939, 87326, 87405, 87417, 100065, 100247, 100348, 104689, 109031, 109317, 132494, 132581, 152480, 152504, 152540, 152564, 152586, 152588, 152737, 164375, 202882, 203130, 203168, 202556, 203800, 204235, 204313, 204331, 204336, 205457, 205231, 205344, 206238, 206248, 206307, 206744, 212801, 212810, 221845, 222295, 225654, 225677, 225678, 226402, 226570, 226953, 282640
) group by sortimen.code;

select tbl.tree_booking_id, tree_id
from tree_booking_line tbl
	left join tree on tree.id = tbl.tree_id
	left join price_farmer pf on pf.koperasi_id = 1
		and pf.species_id = 4
		and pf.sortimen_id = (
			select sortimen.id
			from sortimen
				left join tree treeb on round(treeb.diameter) >= sortimen.diameter_min
					and round(treeb.diameter) <= sortimen.diameter_max
			where treeb.id = 2854
		)
where
	tbl.price_per_m3 is null;


select sortimen.id, sortimen.code
from sortimen
	left join tree on round(tree.diameter) >= sortimen.diameter_min and round(tree.diameter) <= sortimen.diameter_max
where tree.id in (
		2854, 156451, 8456, 9624, 10319, 14330, 14416, 14539, 15463, 15805, 15956, 16974, 17354, 25089, 19303, 20321, 20669, 45005, 45085, 21718, 22192, 27125, 27693, 27747, 29652, 30060, 32053, 35395, 40236, 42410, 42534, 44401, 44787, 44911, 46760, 50069, 68689, 73835, 78388, 84939, 87326, 87405, 87417, 100065, 100247, 100348, 104689, 109031, 109317, 132494, 132581, 152480, 152504, 152540, 152564, 152586, 152588, 152737, 164375, 202882, 203130, 203168, 202556, 203800, 204235, 204313, 204331, 204336, 205457, 205231, 205344, 206238, 206248, 206307, 206744, 212801, 212810, 221845, 222295, 225654, 225677, 225678, 226402, 226570, 226953, 282640
) group by sortimen.code;

select koperasi.koperasi_id as id
from `order`
	left join regency on regency.id = `order`.regency_id
	left join koperasi on koperasi.regency_id = regency.id
where `order`.id = 1
group by koperasi.koperasi_id;

select tree.area_id, count(tree.id) total_pohon
from tree
  LEFT JOIN area on area.id = tree.area_id
WHERE
  tree.is_valid = 1
  and tree.deleted = 0
  and tree.koperasi_id = 4
  and tree.verification_by is null
group by tree.area_id;

select username, user_access_id
from user_access
where username in ('verif1',
					'verif2',
					'verif3', 'verif4', 'verif5', 'verif6', 'verif7', 'verif8');



-- gross volume sampai tanggal tertentu
select
	sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) gross_volume
from tree
where
	tree.is_valid = 1
	and tree.deleted = 0
	and tree.verification_status = 1
	and tree.koperasi_id = 1
	and tree.species_id = 1
	and tree.created_on <= '2017-06-20 23:59:59';

-- gross total tree sampai tanggal tertentu
select
	count(1)
from tree
where
	tree.is_valid = 1
	and tree.deleted = 0
	and tree.verification_status = 1
	and tree.koperasi_id = 1
	and tree.species_id = 1
	and tree.created_on <= '2017-06-20 23:59:59';

select species_id, koperasi_id, created_on
from tree
	where species_id = 1 and koperasi_id = 1 and created_on is not null
order by created_on;

SELECT count(CREATED_ON) FROM tree WHERE created_on = 'NULL';

select count(1)
from tree
where diameter is not null;

update user_access_area
set created_on = now();

select
	sum(3.14 * (tree.diameter/200) * (tree.diameter/200) * (tree.`length`/100) * 0.6) volume
from tree
where
	tree.id in (
		select wp.tree_id
		from wood_product wp
		where
			wp.created_on >= '2017-06-10 23:59:59'
			and wp.created_on <= '2017-06-20 23:59:59'
	)
	and tree.koperasi_id = 1
	and tree.species_id = 1;

select tree.koperasi_id
from wood_product wp
	left join tree on tree.id = wp.tree_id
where wp.created_on <= '2017-06-20 23:59:59';

select distinct tree_id
from wood_product;

-- KOPERASI 2, data lama, sesuaikan dengan keadaan DB baru

-- Diameter ada, tapi verif status dan verif date kosong
update tree
set verification_status = 0
	, verification_date = now()
where
	koperasi_id=2
	and verification_status = 0
-- 	and verification_date = '2017-06-28 01:05:33'
	and diameter <> 0
	and is_valid=1
	and deleted=0;

-- Diameter gak ada, tapi verif status 0
update tree
set verification_by = null
where
	koperasi_id=2
	and verification_status = 0
	and is_valid=1
	and deleted=0;

select *
from tree
where
	koperasi_id=2
	and verification_status = 0
	and created_on = 0
	and uid = 0
	and is_valid=1
	and deleted=0;

select
	mk.name pemilik,
	area.area_name lahan,
	tree.koperasi_id,
	sum(case when verification_status = 0 then 1 end) unverified,
	sum(case when verification_status = 1 then 1 end) verified,
	count(1) total_pohon
from tree
	left join area on area.id = tree.area_id
	left join member_koperasi mk on mk.id = area.member_id
where
	area_id in (1325, 1444, 1031, 1032, 1033, 1034, 1036, 1037, 1038, 1039, 1040, 1042, 1043, 1044, 1048, 1049, 1050, 1051, 1053, 1062, 1063, 1067, 1074, 1088)
group by mk.name, area.area_name