sdsdsd

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,
				
							(select CONCAT(partner_branches.home,partner_branches.street,barangays.provDesc)
								from partner_branches 
								INNER JOIN barangays on partner_branches.barangay = barangays.id
								where partner_branches.id = x.branch_id
							) as partner_branch_address1,
							(
								select CONCAT(cities.provDesc,provinces.provDesc)
								from partner_branches
								INNER JOIN cities on partner_branches.city = cities.citymunCode
								INNER JOIN provinces on partner_branches.province = provinces.provCode
								where partner_branches.id = x.branch_id
							) 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 
							GROUP BY x.partner_id,x.pmonth, x.branch_id
							ORDER BY x.order_name2, x.order_name1