jilly

SELECT x.order_name1,x.order_name2,x.tin1 as tin1,x.tin2 as tin2,x.tin3 as tin3,x.partner_name,x.last_name,x.first_name,x.middle_name,x.last_name2,x.first_name2,x.middle_name2,x.business_entity,x.partner_id, CONCAT(x.home,' ',x.street,' ',x.brgy) as address1, CONCAT(' ',x.province) as address2,x.postdate, 
				sum(x.nv) as nv, sum(x.zr) as zr, sum(x.srv) as srv, sum(x.cg) as cg, sum(x.ocg) as ocg, sum(x.vat) as vat, x.partner_id, x.branch_id,
				CONCAT(partner_branches.home,partner_branches.street,barangays.provDesc) as partner_branch_address1,
				CONCAT(cities.provDesc,provinces.provDesc) as partner_branch_address2
		from (
					SELECT p.partner_branch_id as branch_id, part.id as partner_id, part.business_entity as business_entity, part.tin as tin1,
							   part.tin2 as tin2, part.tin3 as tin3, part.name as partner_name, pc.last_name, pc.first_name, pc.middle_name,
								 pc2.last_name as last_name2, pc2.first_name as first_name2, pc2.middle_name as middle_name2, part.home as home,
								 part.street as street,
								(select provDesc from cities where cities.citymunCode = part.city GROUP BY cities.citymunCode) as city,
								b.provDesc as brgy,
								(select provDesc from provinces where provinces.provCode = part.province GROUP BY provinces.provCode) as province,
								sum(pp.vat) as vat,
								p.post_date as postdate, 
								DATE_FORMAT(p.post_date, '%M') as pmonth,
							  MAX(IF(pp.purchase_type = 'ocg', pp.expense, NULL)) as ocg,
								MAX(IF(pp.purchase_type = 'zr', pp.expense, NULL)) as zr,
							  MAX(IF(pp.purchase_type = 'srv', pp.expense, NULL)) as srv,
							  MAX(IF(pp.purchase_type = 'cg', pp.expense, NULL)) as cg,
							  MAX(IF(pp.purchase_type = 'nv', pp.expense, NULL)) as nv,
								(CASE
											WHEN part.business_entity = 'Individual' THEN pc2.last_name
											WHEN part.business_entity = 'Sole Proprietorship' 
											THEN
													IF(pc.last_name IS NOT NULL,pc.last_name, NULL)
											ELSE
												NULL
									END) as order_name1,
								(
									CASE
											WHEN part.business_entity = 'Sole Proprietorship'
											THEN
													IF(pc.last_name IS NULL,part.name,NULL)
											WHEN part.business_entity = 'Partnership' OR part.business_entity = 'Corporation'
											THEN part.name
											ELSE
													NULL
									END
								)as order_name2
							FROM postings p
							LEFT JOIN posting_purchases AS pp ON p.id = pp.posting_id
							INNER JOIN partners as part on p.partner_id = part.id
							LEFT JOIN barangays as b on part.barangay = b.id
							LEFT JOIN partner_contacts as pc on part.sole_id = pc.partner_id
							LEFT JOIN partner_contacts as pc2 on part.id = pc2.partner_id
							where pp.expense > 0 and YEAR(p.post_date) = '2018' and MONTH(p.post_date) = '1' and p.doc_type in ('RR','CV','PCF','CM','DM','JV') and p.status = 'Posted' and pp.purchase_type in ('nv','zr','srv','cg','ocg')
							GROUP BY p.id,part.id) as 
							x 
							LEFT OUTER JOIN partner_branches on x.branch_id = partner_branches.id
							LEFT JOIN barangays on partner_branches.barangay = barangays.id
							LEFT JOIN cities on partner_branches.city = cities.citymunCode
							LEFT JOIN provinces on partner_branches.province = provinces.provCode
							GROUP BY x.partner_id,x.pmonth, x.branch_id
							ORDER BY x.order_name2, x.order_name1