Admin - Pacht

Tabelle

/* ab zweiter Parzelle */
INSERT IGNORE INTO jos_gardeners_accountings 
(type_id,f2,f1,members_id,parcel_id,value,year,description)
SELECT 1, p.size AS f2, p2m.percent/100 AS f1, p2m.members_id, p2m.parcel_id,  
ceil(p2m.percent/100*
ceil((p.size*r.price/2)*100))/100 AS value, 
2017 ,''
FROM jos_gardeners_parcel_to_member p2m
LEFT JOIN jos_gardeners_parcels p ON p2m.parcel_id=p.id 
LEFT JOIN jos_gardeners_rent r ON r.gyear=2017 
WHERE p2m.active=1 AND p2m.nbr>1
ON DUPLICATE KEY UPDATE info='xa';

/* erste Parzelle und size >=1 */
INSERT IGNORE INTO jos_gardeners_accountings 
(type_id,f2,f1,members_id,parcel_id,value,year,description)
SELECT 1, p.size AS f2, p2m.percent/100 AS f1, p2m.members_id, p2m.parcel_id,  
ceil(p2m.percent/100*ceil((r.price+(p.size-1)*r.price/2)*100))/100 AS value, 
2017 ,''
FROM jos_gardeners_parcel_to_member p2m
LEFT JOIN jos_gardeners_parcels p ON p2m.parcel_id=p.id 
LEFT JOIN jos_gardeners_rent r ON r.gyear=2017 
WHERE p2m.active=1 AND p2m.nbr=1 AND p.size>=1
ON DUPLICATE KEY UPDATE info='xb';

/* erste Parzelle size <1 */
INSERT IGNORE INTO jos_gardeners_accountings 
(type_id,f2,f1,members_id,parcel_id,value,year,description)
SELECT 1, p.size AS f2, p2m.percent/100 AS f1, p2m.members_id, p2m.parcel_id,  
ceil(p2m.percent/100*ceil(p.size*r.price*100))/100 AS value, 
2017 ,''
FROM jos_gardeners_parcel_to_member p2m
LEFT JOIN jos_gardeners_parcels p ON p2m.parcel_id=p.id 
LEFT JOIN jos_gardeners_rent r ON r.gyear=2017 
WHERE p2m.active=1 AND p2m.nbr=1 AND p.size<1
ON DUPLICATE KEY UPDATE info='xc';