<?php
$strArray = array();
$key='b4a40212385dd8cdf64e737a2840532e9440b052eec2f4c9';
$url = 'https://smataco.my.id/api_digicon/';
$row_per_page=50;
$maxDistance=5;	// max distance 5 Km
$maxDistanceNearByInMarker=20;	// max distance 10 Km
$regionLatitude="-6.175403";
$regionLongitude="106.824584";

function encrypt_decrypt($action, $string) {
	$output = false;
	$encrypt_method = "AES-256-CBC";
	$secret_key = 'This is my secret key';
	$secret_iv = 'This is my secret iv';

	$key = hash('sha256', $secret_key);
			
	$iv = substr(hash('sha256', $secret_iv), 0, 16);

	if( $action == 'encrypt' ) {
		$output = openssl_encrypt($string, $encrypt_method, $key, 0, $iv);
		$output = base64_encode($output);
	} else if( $action == 'decrypt' ){
			$output = openssl_decrypt(base64_decode($string), $encrypt_method, $key, 0, $iv);
	}
	return $output;
	}
	
function GetFieldNameFromTableUserByUid($cn,$field,$uid) {
	$sqlF="SELECT ".$field." FROM dg_user WHERE user_email='".$uid."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function GetFieldNameFromTableByCode($cn,$table,$field,$code) {
	$sqlF="SELECT ".$field." FROM ".$table." WHERE code='".$code."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function GetFielNameFromTableByCode($cn,$table,$code) {
	$sqlF="SELECT name FROM ".$table." WHERE code='".$code."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
		$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function GetDataFromTableByCode($cn,$table,$code) {
	$sqlF="SELECT code,name FROM ".$table." WHERE id>0 ";
	if($code) {
		$sqlF=$sqlF." AND code='".$code."' ";
	} else {
		$sqlF=$sqlF." AND active='YY' ";
	}
	$sqlF=$sqlF." ORDER BY code";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_array($queryF)) {
		$strArray[] = array (
			'key' => trim($rsF[0]),
			'value' => trim($rsF[1])
		);
	}
	return json_encode($strArray);
}

function GetDataFromTableAssetTypeByAssetCategory($cn,$assetcode) {
	$sqlF="SELECT code,name FROM asset_type_m WHERE ac_code='".$assetcode."' AND active='YY' ";
	$sqlF=$sqlF." ORDER BY code";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_array($queryF)) {
		$strArray[] = array (
			'key' => trim($rsF[0]),
			'value' => trim($rsF[1])
		);
	}
	return json_encode($strArray);
}

function GetRefIdByImageName ($cn,$image) {
	$sqlF="SELECT ref_id FROM contribution_image WHERE image='".$image."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function CheckClientAndKey($cn,$client,$key) {
	$sqlF="SELECT * FROM dg_corporate WHERE dg_code='".$client."' AND dg_token='".$key."' AND dg_token_expired>='".date('Y-m-d')."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='NOTGRANTED';
	while($rsF=mysqli_fetch_array($queryF)) {
		$resultF='GRANTED';
	}
	return $resultF;
}

function CheckClientAndKeyTest($cn,$client,$key) {
	$sqlF="SELECT * FROM dg_corporate WHERE dg_code='".$client."' AND dg_token='".$key."' AND dg_token_expired>='".date('Y-m-d')."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$strArray = array (
		'code' => 1000,
		'description' => 'ClientId dan Token salah om ozan'
	);
	while($rsF=mysqli_fetch_array($queryF)) {
		$strArray = array (
		'code' => 200,
		'description' => 'OK'
		);
	}
	
	return json_encode($strArray);
}


function ContributionByRefId($cn,$clientid,$key,$refId,$user) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		$sqlF="SELECT contact_name,contact_type,contact_phone,asset_category,asset_type,province,city,kecamatan,kelurahan,address,cluster_apart_name,property_document,square_land,property_price,status_data,property_floor,square_building,apart_unit_floor,class_code,indicate_fee,condition_code,building_price_per_meter,price_building,allotment_code,fieldclass_code,streetclass_code,occupancy_code,traffic_code,flood_code,discount,price_after_disc,price_total_building,price_land_per_meter,price_unit_per_meter,latitude,longitude,ref_id,posted,discount,price_after_disc,price_total_building,price_land_per_meter,price_unit_per_meter,analyze_status,user_fullname FROM contribution a,dg_user b WHERE a.createdby=b.user_id AND deleted='NN' AND ref_id='".$refId."' LIMIT 1";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlI="SELECT image FROM contribution_image WHERE ref_id='".$refId."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$image='no-image-found.jpg';
			while($rsI=mysqli_fetch_array($queryI)) {
			$image=trim($rsI[0]);
			}
			
			$sqlBookmark="SELECT * FROM contribution_bookmark WHERE ref_id='".$refId."' AND user_email='".$user."' LIMIT 1";
			$queryBookmark=mysqli_query($cn,$sqlBookmark);
			$bookmark='notbookmark';
			while($rsBookmark=mysqli_fetch_array($queryBookmark)) {
			$bookmark='bookmark';
			}
			
				$contact_type=GetFieldNameFromTableByCode($cn,'contact_type_m','name',trim($rsF['contact_type']));
				$asset_category=GetFieldNameFromTableByCode($cn,'asset_category_m','name',trim($rsF['asset_category']));
				$asset_type=GetFieldNameFromTableByCode($cn,'asset_type_m','name',trim($rsF['asset_type']));
				$property_document=GetFieldNameFromTableByCode($cn,'document_m','name',trim($rsF['property_document']));
				$status_data=GetFieldNameFromTableByCode($cn,'status_data_m','name',trim($rsF['status_data']));
				$class_code=GetFieldNameFromTableByCode($cn,'class_building_m','name',trim($rsF['class_code']));
				$condition_code=GetFieldNameFromTableByCode($cn,'condition_building_m','name',trim($rsF['condition_code']));
				$allotment_code=GetFieldNameFromTableByCode($cn,'allotment_m','name',trim($rsF['allotment_code']));
				$fieldclass_code=GetFieldNameFromTableByCode($cn,'condition_field_m','name',trim($rsF['fieldclass_code']));
				$streetclass_code=GetFieldNameFromTableByCode($cn,'class_road_m','name',trim($rsF['streetclass_code']));
				$occupancy_code=GetFieldNameFromTableByCode($cn,'occupancy_building_m','name',trim($rsF['occupancy_code']));
				$traffic_code=GetFieldNameFromTableByCode($cn,'traffic_volume_m','name',trim($rsF['traffic_code']));
				$flood_code=GetFieldNameFromTableByCode($cn,'possible_flooding_m','name',trim($rsF['flood_code']));
				
				$strArray[]=array (
				'ref_id' => trim($rsF['ref_id']),
				'contact_name' => trim($rsF['contact_name']),
				'contact_type' => $contact_type,
				'contact_type_code' => trim($rsF['contact_type']),
				'contact_phone' => trim($rsF['contact_phone']),
				'asset_category' => $asset_category,
				'asset_category_code' => trim($rsF['asset_category']),
				'asset_type' => $asset_type,
				'asset_type_code' => trim($rsF['asset_type']),
				'province' => trim($rsF['province']),
				'city' => trim($rsF['city']),
				'kecamatan' => trim($rsF['kecamatan']),
				'kelurahan' => trim($rsF['kelurahan']),
				'address' => trim($rsF['address']),
				'cluster_apart_name' => trim($rsF['cluster_apart_name']),
				'property_document' => $property_document,
				'property_document_code' => trim($rsF['property_document']),
				'square_land' => trim($rsF['square_land']),
				'property_price' => trim($rsF['property_price']),
				'status_data' => $status_data,
				'status_data_code' => trim($rsF['status_data']),
				'property_floor' => trim($rsF['property_floor']),
				'square_building' => trim($rsF['square_building']),
				'apart_unit_floor' => trim($rsF['apart_unit_floor']),
				'class_code' => $class_code,
				'class_code_code' => trim($rsF['class_code']),
				'indicate_fee' => trim($rsF['indicate_fee']),
				'condition_code' => $condition_code,
				'condition_code_code' => trim($rsF['condition_code']),
				'building_price_per_meter' => trim($rsF['building_price_per_meter']),
				'price_building' => trim($rsF['price_building']),
				'allotment_code' => $allotment_code,
				'allotment_code_code' => trim($rsF['allotment_code']),
				'fieldclass_code' => $fieldclass_code,
				'fieldclass_code_code' => trim($rsF['fieldclass_code']),
				'streetclass_code' => $streetclass_code,
				'streetclass_code_code' => trim($rsF['streetclass_code']),
				'occupancy_code' => $occupancy_code,
				'occupancy_code_code' => trim($rsF['occupancy_code']),
				'traffic_code' => $traffic_code,
				'traffic_code_code' => trim($rsF['traffic_code']),
				'flood_code' => $flood_code,
				'flood_code_code' => trim($rsF['flood_code']),
				'discount' => trim($rsF['discount']),
				'price_after_disc' => trim($rsF['price_after_disc']),
				'price_total_building' => trim($rsF['price_total_building']),
				'price_land_per_meter' => trim($rsF['price_land_per_meter']),
				'price_unit_per_meter' => trim($rsF['price_unit_per_meter']),
				'analyze' => $rsF['analyze_status'],
				'latitude' => trim($rsF['latitude']),
				'longitude' => trim($rsF['longitude']),
				'posted' => date('d-m-Y',strtotime($rsF['posted'])),
				'username' => $rsF['user_fullname'],
				'bookmark' => $bookmark,
				'image' => $image				
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}


function ContributionByStatus($cn,$clientid,$key,$status) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		if($status==0) {
			$status='WW';
		} else if($status==1) {
			$status='OK';
		} else if($status==2) {
			$status='RR';
		}
		$sqlF="SELECT contact_name,contact_type,contact_phone,asset_category,asset_type,province,city,kecamatan,kelurahan,address,cluster_apart_name,property_document,square_land,property_price,status_data,property_floor,square_building,apart_unit_floor,class_code,indicate_fee,condition_code,building_price_per_meter,price_building,allotment_code,fieldclass_code,streetclass_code,occupancy_code,traffic_code,flood_code,discount,price_after_disc,price_total_building,price_land_per_meter,price_unit_per_meter,latitude,longitude,ref_id,analyze_status,createdby,created,corporate_code FROM contribution WHERE analyze_status='".$status."' AND deleted='NN' AND corporate_code='".$clientid."' ORDER BY id DESC";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlI="SELECT image FROM contribution_image WHERE ref_id='".$rsF['ref_id']."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$image='no-image-found.jpg';
			while($rsI=mysqli_fetch_array($queryI)) {
			$image=trim($rsI[0]);
			}
			$sqlI="SELECT dg_corporate_name FROM dg_corporate WHERE dg_code='".$clientid."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$corporate='';
			while($rsI=mysqli_fetch_array($queryI)) {
			$corporate=trim($rsI[0]);
			}
			
				$contact_type=GetFieldNameFromTableByCode($cn,'contact_type_m','name',trim($rsF['contact_type']));
				$asset_category=GetFieldNameFromTableByCode($cn,'asset_category_m','name',trim($rsF['asset_category']));
				$asset_type=GetFieldNameFromTableByCode($cn,'asset_type_m','name',trim($rsF['asset_type']));
				$property_document=GetFieldNameFromTableByCode($cn,'document_m','name',trim($rsF['property_document']));
				$status_data=GetFieldNameFromTableByCode($cn,'status_data_m','name',trim($rsF['status_data']));
				$class_code=GetFieldNameFromTableByCode($cn,'class_building_m','name',trim($rsF['class_code']));
				$condition_code=GetFieldNameFromTableByCode($cn,'condition_building_m','name',trim($rsF['condition_code']));
				$allotment_code=GetFieldNameFromTableByCode($cn,'allotment_m','name',trim($rsF['allotment_code']));
				$fieldclass_code=GetFieldNameFromTableByCode($cn,'condition_field_m','name',trim($rsF['fieldclass_code']));
				$streetclass_code=GetFieldNameFromTableByCode($cn,'class_road_m','name',trim($rsF['streetclass_code']));
				$occupancy_code=GetFieldNameFromTableByCode($cn,'occupancy_building_m','name',trim($rsF['occupancy_code']));
				$traffic_code=GetFieldNameFromTableByCode($cn,'traffic_volume_m','name',trim($rsF['traffic_code']));
				$flood_code=GetFieldNameFromTableByCode($cn,'possible_flooding_m','name',trim($rsF['flood_code']));
				
				$strArray[]=array (
				'contact_name' => trim($rsF['contact_name']),
				'contact_type' => $contact_type,
				'contact_phone' => trim($rsF['contact_phone']),
				'asset_category' => $asset_category,
				'asset_type' => $asset_type,
				'province' => trim($rsF['province']),
				'city' => trim($rsF['city']),
				'kecamatan' => trim($rsF['kecamatan']),
				'kelurahan' => trim($rsF['kelurahan']),
				'address' => trim($rsF['address']),
				'cluster_apart_name' => trim($rsF['cluster_apart_name']),
				'property_document' => $property_document,
				'square_land' => trim($rsF['square_land']),
				'property_price' => trim($rsF['property_price']),
				'property_price_format' => number_format(trim($rsF['property_price'])),
				'status_data' => $status_data,
				'property_floor' => trim($rsF['property_floor']),
				'square_building' => trim($rsF['square_building']),
				'apart_unit_floor' => trim($rsF['apart_unit_floor']),
				'class_code' => $class_code,
				'indicate_fee' => trim($rsF['indicate_fee']),
				'condition_code' => $condition_code,
				'building_price_per_meter' => trim($rsF['building_price_per_meter']),
				'price_building' => trim($rsF['price_building']),
				'allotment_code' => $allotment_code,
				'fieldclass_code' => $fieldclass_code,
				'streetclass_code' => $streetclass_code,
				'occupancy_code' => $occupancy_code,
				'traffic_code' => $traffic_code,
				'flood_code' => $flood_code,
				'discount' => trim($rsF['discount']),
				'price_after_disc' => trim($rsF['price_after_disc']),
				'price_total_building' => trim($rsF['price_total_building']),
				'price_land_per_meter' => trim($rsF['price_land_per_meter']),
				'price_unit_per_meter' => trim($rsF['price_unit_per_meter']),
				'latitude' => trim($rsF['latitude']),
				'longitude' => trim($rsF['longitude']),
				'analyze_status' => trim($rsF['analyze_status']),
				'ref_id' => trim($rsF['ref_id']),
				'created_by' => trim($rsF['createdby']),
				'created' => date('d-m-Y  H:i',strtotime($rsF['created'])),
				'corporate_code' => trim($rsF['corporate_code']),
				'asset_category_30' => substr($asset_category,0,30),
				'province_30' => ucwords(strtolower(substr(trim($rsF['province']),0,30))),
				'kelurahan_30' => ucwords(strtolower(substr(trim($rsF['kelurahan']),0,30))),
				'corporate_name' => substr($corporate,0,30),
				'image' => $image.'?t='.rand(1,10000000000)				
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}

function ContributionByStatusSearchingInMarker($cn,$clientid,$key,$status,$assetcategory,$province,$kelurahan,$price1,$price2,$rownumber) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		if($status==0) {
			$status='WW';
		} else if($status==1) {
			$status='OK';
		} else if($status==2) {
			$status='RR';
		}
		$sqlF="SELECT contact_name,contact_type,contact_phone,asset_category,asset_type,province,city,kecamatan,kelurahan,address,cluster_apart_name,property_document,square_land,property_price,status_data,property_floor,square_building,apart_unit_floor,class_code,indicate_fee,condition_code,building_price_per_meter,price_building,allotment_code,fieldclass_code,streetclass_code,occupancy_code,traffic_code,flood_code,discount,price_after_disc,price_total_building,price_land_per_meter,price_unit_per_meter,latitude,longitude,ref_id,analyze_status,createdby,created,corporate_code FROM contribution WHERE analyze_status='".$status."' AND deleted='NN' AND corporate_code='".$clientid."' ";
		
		$byQuery=0;
		if($assetcategory) {
			$sqlF=$sqlF." AND asset_category='".$assetcategory."' ";
			$byQuery=1;
		}
		if($province) {
			$sqlF=$sqlF." AND province='".$province."' ";
			$byQuery=1;
		}
		if($kelurahan) {
			$sqlF=$sqlF." AND kelurahan='".$kelurahan."' ";
			$byQuery=1;
		}
		if($price1>0) {
			$sqlF=$sqlF." AND (property_price between ".$price1." AND ".$price2.") ";
			$byQuery=1;
		}
		if($rownumber=='') {
		$rownumber=200;
		}
		$byQuery=1;
		
		if($byQuery==0) {
		    $sqlF=$sqlF." ORDER BY RAND() DESC LIMIT 200 ";
		} else {
		     $sqlF=$sqlF." ORDER BY RAND() DESC LIMIT ".$rownumber;
		}
		//exit($sqlF);
		$totaldata=0;
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlI="SELECT image FROM contribution_image WHERE ref_id='".$rsF['ref_id']."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$image='no-image-found.jpg';
			while($rsI=mysqli_fetch_array($queryI)) {
			$image=trim($rsI[0]);
			}
			
			$sqlI="SELECT dg_corporate_name FROM dg_corporate WHERE dg_code='".$clientid."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$corporate='';
			while($rsI=mysqli_fetch_array($queryI)) {
			$corporate=trim($rsI[0]);
			}
				$contact_type=GetFieldNameFromTableByCode($cn,'contact_type_m','name',trim($rsF['contact_type']));
				$asset_category=GetFieldNameFromTableByCode($cn,'asset_category_m','name',trim($rsF['asset_category']));
				$asset_type=GetFieldNameFromTableByCode($cn,'asset_type_m','name',trim($rsF['asset_type']));
				$property_document=GetFieldNameFromTableByCode($cn,'document_m','name',trim($rsF['property_document']));
				$status_data=GetFieldNameFromTableByCode($cn,'status_data_m','name',trim($rsF['status_data']));
				$class_code=GetFieldNameFromTableByCode($cn,'class_building_m','name',trim($rsF['class_code']));
				$condition_code=GetFieldNameFromTableByCode($cn,'condition_building_m','name',trim($rsF['condition_code']));
				$allotment_code=GetFieldNameFromTableByCode($cn,'allotment_m','name',trim($rsF['allotment_code']));
				$fieldclass_code=GetFieldNameFromTableByCode($cn,'condition_field_m','name',trim($rsF['fieldclass_code']));
				$streetclass_code=GetFieldNameFromTableByCode($cn,'class_road_m','name',trim($rsF['streetclass_code']));
				$occupancy_code=GetFieldNameFromTableByCode($cn,'occupancy_building_m','name',trim($rsF['occupancy_code']));
				$traffic_code=GetFieldNameFromTableByCode($cn,'traffic_volume_m','name',trim($rsF['traffic_code']));
				$flood_code=GetFieldNameFromTableByCode($cn,'possible_flooding_m','name',trim($rsF['flood_code']));
				
				$strArray[]=array (
				'contact_name' => trim($rsF['contact_name']),
				'contact_type' => $contact_type,
				'contact_phone' => trim($rsF['contact_phone']),
				'asset_category' => $asset_category,
				'asset_type' => $asset_type,
				'province' => trim($rsF['province']),
				'city' => trim($rsF['city']),
				'kecamatan' => trim($rsF['kecamatan']),
				'kelurahan' => trim($rsF['kelurahan']),
				'address' => trim($rsF['address']),
				'cluster_apart_name' => trim($rsF['cluster_apart_name']),
				'property_document' => $property_document,
				'square_land' => trim($rsF['square_land']),
				'property_price' => trim($rsF['property_price']),
				'property_price_format' => number_format(trim($rsF['property_price'])),
				'status_data' => $status_data,
				'property_floor' => trim($rsF['property_floor']),
				'square_building' => trim($rsF['square_building']),
				'apart_unit_floor' => trim($rsF['apart_unit_floor']),
				'class_code' => $class_code,
				'indicate_fee' => trim($rsF['indicate_fee']),
				'condition_code' => $condition_code,
				'building_price_per_meter' => trim($rsF['building_price_per_meter']),
				'price_building' => trim($rsF['price_building']),
				'allotment_code' => $allotment_code,
				'fieldclass_code' => $fieldclass_code,
				'streetclass_code' => $streetclass_code,
				'occupancy_code' => $occupancy_code,
				'traffic_code' => $traffic_code,
				'flood_code' => $flood_code,
				'discount' => trim($rsF['discount']),
				'price_after_disc' => trim($rsF['price_after_disc']),
				'price_total_building' => trim($rsF['price_total_building']),
				'price_land_per_meter' => trim($rsF['price_land_per_meter']),
				'price_unit_per_meter' => trim($rsF['price_unit_per_meter']),
				'latitude' => trim($rsF['latitude']),
				'longitude' => trim($rsF['longitude']),
				'analyze_status' => trim($rsF['analyze_status']),
				'ref_id' => trim($rsF['ref_id']),
				'created_by' => trim($rsF['createdby']),
				'created' => date('d-m-Y  H:i',strtotime($rsF['created'])),
				'corporate_code' => trim($rsF['corporate_code']),
				'asset_category_30' => substr($asset_category,0,30),
				'province_30' => ucwords(strtolower(substr(trim($rsF['province']),0,30))),
				'kelurahan_30' => ucwords(strtolower(substr(trim($rsF['kelurahan']),0,30))),
				'corporate_name' => substr($corporate,0,30),
				'image' => $image.'?t='.rand(1,10000000000)				
			);
		}
		
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}

function ContributionForDistance($cn) {
	$sqlF="SELECT ref_id,latitude,longitude,name,asset_type,property_price,streetclass_code FROM contribution a,asset_category_m b WHERE a.asset_category=b.code AND deleted='NN' AND analyze_status='OK' ORDER BY RAND() ";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
		$queryImage=mysqli_query($cn,$sqlImage);
		$image='no-image-found.png';
		while($rsImage=mysqli_fetch_array($queryImage)) {
		$image=trim($rsImage[0]);
		}
		
			$sqlAt="SELECT name FROM asset_type_m WHERE code='".$rsF['asset_type']."' LIMIT 1";
			$queryAt=mysqli_query($cn,$sqlAt);
			$asset_type='';
			while($rsAt=mysqli_fetch_array($queryAt)) {
			$asset_type=trim($rsAt[0]);
			}
			
		$strArray[] = array (
			'ref_id' => trim($rsF['ref_id']),
			'latitude' => trim($rsF['latitude']),
			'longitude' => trim($rsF['longitude']),
			'asset_category' => trim($rsF['name']),
			'asset_type' => $asset_type,
			'street' => trim($rsF['streetclass_code']),
			'price' => trim($rsF['property_price']),
			'image' => $image
		);
	}
		return json_encode($strArray);
}

function ContributionForKelurahan($cn,$kelurahan) {
	$sqlF="SELECT ref_id,latitude,province,kelurahan,longitude,name,asset_type,property_price,streetclass_code,corporate_code FROM contribution a,asset_category_m b WHERE a.asset_category=b.code AND deleted='NN' AND analyze_status='OK' AND kelurahan='".strtoupper($kelurahan)."' ORDER BY RAND() ";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
		$queryImage=mysqli_query($cn,$sqlImage);
		$image='no-image-found.png';
		while($rsImage=mysqli_fetch_array($queryImage)) {
		$image=trim($rsImage[0]);
		}
		
			$sqlCorporate="SELECT * FROM dg_corporate WHERE dg_code='".$rsF['corporate_code']."' LIMIT 1";
			$queryCorporate=mysqli_query($cn,$sqlCorporate);
			$corporateCode='';
			$corporateName='';
			$corporateLogo='';
			while($rsCorporate=mysqli_fetch_assoc($queryCorporate)) {
				$corporateCode=trim($rsCorporate['dg_code']);
				$corporateName=trim($rsCorporate['dg_corporate_name']);
				$corporateLogo=trim($rsCorporate['dg_logo']);
			}
			
			$sqlAt="SELECT name FROM asset_type_m WHERE code='".$rsF['asset_type']."' LIMIT 1";
			$queryAt=mysqli_query($cn,$sqlAt);
			$asset_type='';
			while($rsAt=mysqli_fetch_array($queryAt)) {
			$asset_type=trim($rsAt[0]);
			}
			
		$strArray[] = array (
			'ref_id' => trim($rsF['ref_id']),
			'latitude' => trim($rsF['latitude']),
			'longitude' => trim($rsF['longitude']),
			'asset_category' => trim($rsF['name']),
			'corporate_code' => $corporateCode,
			'corporate_name' => $corporateName,
			'corporate_logo' => $corporateLogo,
			'asset_type' => $asset_type,
			'street' => trim($rsF['streetclass_code']),
			'price' => trim($rsF['property_price']),
			'province' => trim($rsF['province']),
			'kelurahan' => trim($rsF['kelurahan']),
			'image' => $image
		);
	}
		return json_encode($strArray);
}

function ContributionForDistanceAll($cn,$currClientId) {
	$sqlF="SELECT ref_id,latitude,longitude,name,asset_type,property_price,streetclass_code,corporate_code,dg_logo,dg_corporate_name FROM contribution a,asset_category_m b,dg_corporate c WHERE a.asset_category=b.code AND a.corporate_code=c.dg_code AND deleted='NN' AND analyze_status='OK' ORDER BY RAND() ";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
		$queryImage=mysqli_query($cn,$sqlImage);
		$image='no-image-found.png';
		while($rsImage=mysqli_fetch_array($queryImage)) {
		$image=trim($rsImage[0]);
		}
		
			$sqlAt="SELECT name FROM asset_type_m WHERE code='".$rsF['asset_type']."' LIMIT 1";
			$queryAt=mysqli_query($cn,$sqlAt);
			$asset_type='';
			while($rsAt=mysqli_fetch_array($queryAt)) {
			$asset_type=trim($rsAt[0]);
			}
			
		$strArray[] = array (
			'ref_id' => trim($rsF['ref_id']),
			'latitude' => trim($rsF['latitude']),
			'longitude' => trim($rsF['longitude']),
			'asset_category' => trim($rsF['name']),
			'asset_type' => $asset_type,
			'street' => trim($rsF['streetclass_code']),
			'price' => trim($rsF['property_price']),
			'corporate_code' => $rsF['corporate_code'],
			'corporate_name' => $rsF['dg_corporate_name'],
			'corporate_logo' => $rsF['dg_logo'],
			'image' => $image
		);
	}
		return json_encode($strArray);
}

function getDistanceBetweenPoints($lat1, $lon1, $lat2, $lon2) {
    $theta = $lon1 - $lon2;
    $miles = (sin(deg2rad($lat1)) * sin(deg2rad($lat2))) + (cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)));
    $miles = acos($miles);
    $miles = rad2deg($miles);
    $miles = $miles * 60 * 1.1515;
    //$feet  = $miles * 5280;
    //$yards = $feet / 3;
    $kilometers = $miles * 1.609344;
    //$meters = $kilometers * 1000;
    //return compact('miles','feet','yards','kilometers','meters'); 
	return $kilometers; 
}

function Contribution($cn,$clientid,$key,$row_per_page,$page) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		//$currpage = $page;
		//$page = ($page - 1) * $row_per_page;
		
		$sqlF="SELECT count(*) FROM contribution WHERE analyze_status='OK' AND deleted='NN'";	
		$totalrow=0;
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_array($queryF)) {
		$totalrow=$rsF[0];
		}
		$totalpage = ceil($totalrow/$row_per_page);
		
		//$sqlF="SELECT ref_id,posted,b.name as category,kelurahan,property_price,latitude,longitude FROM contribution a,asset_category_m b WHERE a.asset_category=b.code AND //a.id>0 AND deleted='NN' AND analyze_status='OK' ORDER BY a.id DESC LIMIT ".$row_per_page." OFFSET ".$page;	
		$sqlF="SELECT ref_id,posted,b.name as category,kelurahan,property_price,latitude,longitude  FROM contribution a,asset_category_m b WHERE a.asset_category=b.code AND a.id>0 AND deleted='NN' AND analyze_status='OK' ORDER BY a.id DESC LIMIT 1,".($page*$row_per_page);
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlI="SELECT image FROM contribution_image WHERE ref_id='".$rsF['ref_id']."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$image='no-image-found.png';
			while($rsI=mysqli_fetch_array($queryI)) {
			$image=trim($rsI[0]);
			}
				$strArray[]=array (
				'page' => $page,
				'total_page' => $totalpage,
				'current_data_number' => ($page*$row_per_page),
				'total_data' => $totalrow,
				'ref_id' => trim($rsF['ref_id']),
				'latitude' => trim($rsF['latitude']), 
				'longitude' => trim($rsF['longitude']), 
				'posted' => date('d-m-Y',strtotime($rsF['posted'])),
				'asset_category' => trim($rsF['category']),
				'kelurahan' => trim($rsF['kelurahan']),
				'price' => trim($rsF['property_price']),
				'image' => $image				
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}

function CountContributionAll($cn,$clientid,$key,$analyze) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		if($analyze=='') {
			$analyze='WW';
		}
		$sqlF="SELECT COUNT(*) as total FROM contribution WHERE analyze_status='OK' AND corporate_code='".$clientid."'";	
		$queryF=mysqli_query($cn,$sqlF);
		$countF=0;
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$countF=$rsF['total'];
			$strArray=array (
				'result' => $countF
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}

function ContributionAll($cn,$clientid,$key,$analyze) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		if($analyze=='') {
			$analyze='WW';
		}
		$sqlF="SELECT user_fullname,created,ref_id,posted,province,square_land,square_building,contact_name,contact_phone,b.name as category,c.name as cattype,kelurahan,property_price,analyze_status FROM contribution a,asset_category_m b,asset_type_m c,dg_user d WHERE a.asset_category=b.code AND a.asset_type=c.code AND a.createdby=d.user_email AND deleted='NN' AND a.id>0 AND analyze_status='".$analyze."' ORDER BY a.ref_id DESC";	
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlI="SELECT image FROM contribution_image WHERE ref_id='".$rsF['ref_id']."' LIMIT 1";
			$queryI=mysqli_query($cn,$sqlI);
			$image='no-image-found.jpg';
			while($rsI=mysqli_fetch_array($queryI)) {
			$image=trim($rsI[0]);
			}
				$strArray[]=array (
				'ref_id' => trim($rsF['ref_id']),
				'posted' => date('d-m-Y',strtotime($rsF['posted'])),
				'contact_name' => trim($rsF['contact_name']),
				'analyze' => $rsF['analyze_status'],
				'contact_phone' => trim($rsF['contact_phone']),
				'asset_category' => trim($rsF['category']),
				'asset_type' => trim($rsF['cattype']),
				'province' => trim($rsF['province']),
				'kelurahan' => trim($rsF['kelurahan']),
				'square_land' => trim($rsF['square_land']),
				'square_building' => trim($rsF['square_building']),
				'price' => trim($rsF['property_price']),
				'username' => $rsF['user_fullname'],
				'created' => date('d-m-Y H:i',strtotime($rsF['created'])),
				'image' => $image				
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
	return json_encode($strArray);
}


function Login($cn,$userid,$pin) {
	$sqlF="SELECT * FROM dg_user WHERE user_email='".$userid."' AND user_pin='".encrypt_decrypt('encrypt',$pin)."' AND user_active='YY' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	if(mysqli_num_rows($queryF)==1) {
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlC="SELECT * FROM dg_corporate WHERE dg_code='".trim($rsF['user_corporate'])."' AND dg_token_expired>='".date('Y-m-d')."'LIMIT 1";
			$queryC=mysqli_query($cn,$sqlC);
			if(mysqli_num_rows($queryC)==1) {
				while($rsC=mysqli_fetch_array($queryC)) {
					$strArray[]=array (
					'result' => 'FOUND',
					'key'=> trim($rsC['dg_token']),
					'user_email' => $userid,
					'user_fullname' => trim($rsF['user_fullname']),
					'user_pin' => $pin,
					'corporate_code' => trim($rsF['user_corporate']),
					'web_key' => encrypt_decrypt('encrypt',trim($rsF['user_corporate'])).'|'.encrypt_decrypt('encrypt',trim($rsC['dg_token']))
					);
				}
			} else {
				$strArray[]=array (
				'result' => 'FOUND',
				'key'=> 'expired'
				);
			}
		}
	} else {
		$strArray[]=array (
			'result' => 'NOTFOUND'
		);
	}	
	return json_encode($strArray);
}

function ImageContributionByRefId($cn,$ref) {
	$sqlF="SELECT * FROM contribution_image WHERE ref_id='".$ref."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[] = array (
		'image'=> $rsF['image']
		);
	}
		return json_encode($strArray);
}

function ImagesContributionByRefId($cn,$ref,$url_api) {
	$sqlF="SELECT * FROM contribution_image WHERE ref_id='".$ref."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[] = array (
		 'image' => $url_api.'assets/images/'. $rsF['image']
		);
	}
		return json_encode($strArray);
}

function SaveFcm($cn,$userid,$name,$fcm) {
	$sqlF="SELECT * FROM dg_user_fcm WHERE user_email='".$userid."' AND user_fcm='".$fcm."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	if(mysqli_num_rows($queryF)==0) {
		mysqli_query($cn,"INSERT INTO dg_user_fcm SET 
		user_fullname='".mysqli_real_escape_string($cn,$name)."',
		user_email='".$userid."',
		fcm_create='".date('Y-m-d')."',
		user_fcm='".$fcm."'");
		
		$strArray['data']['code']='200';
		$strArray['data']['status']='FCM Saved Successfully';
	} else {
		$strArray['data']['code']='405';
		$strArray['data']['status']='FCM Not Save';
	}	
		mysqli_query($cn,"UPDATE dg_user_fcm SET user_fullname='".mysqli_real_escape_string($cn,$name)."' WHERE user_email='".$userid."'");
	
	return json_encode($strArray);
}

function Province($cn) {
	$sqlF="SELECT DISTINCT province FROM province_kelurahan ORDER BY province";
	$queryF=mysqli_query($cn,$sqlF);
	if (mysqli_num_rows($queryF) > 0) {
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$strArray['result']['success'] = true;
			$strArray['result']['code'] = 200;
			$strArray['result']['description'] = 'Data Fetched Successfully';
			$strArray['data']['value'][] = [
				'province' => trim($rsF['province'])
			];
		}
	} else {
			$strArray['result']['success'] = false;
			$strArray['result']['code'] = 400;
			$strArray['result']['description'] = 'Data Not Found';
	}
		return json_encode($strArray);
}

function Combo_Province($cn) {
	$sqlF="SELECT DISTINCT province FROM province_kelurahan ORDER BY province";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[] = array(
			'key' => trim($rsF['province']),
			'value' => trim($rsF['province'])
		);
	}
	return json_encode($strArray);
}

function City($cn,$province) {
	if($province) {
		$sqlF="SELECT DISTINCT city FROM province_kelurahan WHERE province='".$province."' ORDER BY city";
		$queryF=mysqli_query($cn,$sqlF);
		if (mysqli_num_rows($queryF) > 0) {
			while($rsF=mysqli_fetch_assoc($queryF)) {
				$strArray['result']['success'] = true;
				$strArray['result']['code'] = 200;
				$strArray['result']['description'] = 'Data Fetched Successfully';
				$strArray['data']['province'] = $province;
				$strArray['data']['value'][] = [
					'city' => trim($rsF['city'])
				];
			}
		} else {
				$strArray['result']['success'] = false;
				$strArray['result']['code'] = 400;
				$strArray['result']['description'] = 'Data Not Found';
		}
	} else {
		$strArray['result']['success'] = false;
		$strArray['result']['code'] = 405;
		$strArray['result']['description'] = 'Missing Parameter';
	}
		return json_encode($strArray);
}

function Combo_City($cn,$province) {
		
	if($province) {
		$sqlF="SELECT DISTINCT city FROM province_kelurahan WHERE province='".$province."' ORDER BY city";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$strArray[] = array(
				'key' => trim($rsF['city']),
				'value' => trim($rsF['city'])
			);
		}
	}
	return json_encode($strArray);
}

function Kecamatan($cn,$data) {
	if($data['province'] && $data['city']) {
		$sqlF="SELECT DISTINCT kecamatan FROM province_kelurahan WHERE province='".$data['province']."' AND city='".$data['city']."' ORDER BY kecamatan";
		$queryF=mysqli_query($cn,$sqlF);
		if (mysqli_num_rows($queryF) > 0) {
			while($rsF=mysqli_fetch_assoc($queryF)) {
				$strArray['result']['success'] = true;
				$strArray['result']['code'] = 200;
				$strArray['result']['description'] = 'Data Fetched Successfully';
				$strArray['data']['province'] = $data['province'];
				$strArray['data']['city'] = $data['city'];
				$strArray['data']['value'][] = [
					'kecamatan' => trim($rsF['kecamatan'])
				];
			}
		} else {
				$strArray['result']['success'] = false;
				$strArray['result']['code'] = 400;
				$strArray['result']['description'] = 'Data Not Found';
		}
	} else {
		$strArray['result']['success'] = false;
		$strArray['result']['code'] = 405;
		$strArray['result']['description'] = 'Missing Parameter';
	}
		return json_encode($strArray);
}

function Combo_Kecamatan($cn,$data) {
	if($data['province'] && $data['city']) {
		$sqlF="SELECT DISTINCT kecamatan FROM province_kelurahan WHERE province='".$data['province']."' AND city='".$data['city']."' ORDER BY kecamatan";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$strArray[] = array(
				'key' => trim($rsF['kecamatan']),
				'value' => trim($rsF['kecamatan'])
			);
		}
	}
		return json_encode($strArray);
}

function Kelurahan($cn,$data) {
	if($data['province'] && $data['city'] && $data['kecamatan']) {
		$sqlF="SELECT DISTINCT kelurahan FROM province_kelurahan WHERE province='".$data['province']."' AND city='".$data['city']."' AND kecamatan='".$data['kecamatan']."'  ORDER BY kelurahan";
		$queryF=mysqli_query($cn,$sqlF);
		if (mysqli_num_rows($queryF) > 0) {
			while($rsF=mysqli_fetch_assoc($queryF)) {
				$strArray['result']['success'] = true;
				$strArray['result']['code'] = 200;
				$strArray['result']['description'] = 'Data Fetched Successfully';
				$strArray['data']['province'] = $data['province'];
				$strArray['data']['city'] = $data['city'];
				$strArray['data']['kecamatan'] = $data['kecamatan'];
				$strArray['data']['value'][] = [
					'kelurahan' => trim($rsF['kelurahan'])
				];
			}
		} else {
				$strArray['result']['success'] = false;
				$strArray['result']['code'] = 400;
				$strArray['result']['description'] = 'Data Not Found';
		}
	} else {
		$strArray['result']['success'] = false;
		$strArray['result']['code'] = 405;
		$strArray['result']['description'] = 'Missing Parameter';
	}
		return json_encode($strArray);
}

function Combo_Kelurahan($cn,$data) {
	if($data['province'] && $data['city'] && $data['kecamatan']) {
		$sqlF="SELECT DISTINCT kelurahan FROM province_kelurahan WHERE province='".$data['province']."' AND city='".$data['city']."' AND kecamatan='".$data['kecamatan']."'  ORDER BY kelurahan";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$strArray[] = array(
				'key' => trim($rsF['kelurahan']),
				'value' => trim($rsF['kelurahan'])
			);
		}
	}
		return json_encode($strArray);
}

function Combo_KelurahanByProvince($cn,$province) {
	$sqlF="SELECT DISTINCT kelurahan FROM province_kelurahan WHERE province='".$province."' ORDER BY kelurahan";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[] = array(
		'key' => trim($rsF['kelurahan']),
		'value' => trim($rsF['kelurahan'])
		);
	}
	return json_encode($strArray);
}

function get_image_location($image = ''){
    $exif = exif_read_data($image, 0, true);
    if($exif && isset($exif['GPS'])){
        $GPSLatitudeRef = $exif['GPS']['GPSLatitudeRef'];
        $GPSLatitude    = $exif['GPS']['GPSLatitude'];
        $GPSLongitudeRef= $exif['GPS']['GPSLongitudeRef'];
        $GPSLongitude   = $exif['GPS']['GPSLongitude'];
        
        $lat_degrees = count($GPSLatitude) > 0 ? gps2Num($GPSLatitude[0]) : 0;
        $lat_minutes = count($GPSLatitude) > 1 ? gps2Num($GPSLatitude[1]) : 0;
        $lat_seconds = count($GPSLatitude) > 2 ? gps2Num($GPSLatitude[2]) : 0;
        
        $lon_degrees = count($GPSLongitude) > 0 ? gps2Num($GPSLongitude[0]) : 0;
        $lon_minutes = count($GPSLongitude) > 1 ? gps2Num($GPSLongitude[1]) : 0;
        $lon_seconds = count($GPSLongitude) > 2 ? gps2Num($GPSLongitude[2]) : 0;
        
        $lat_direction = ($GPSLatitudeRef == 'W' or $GPSLatitudeRef == 'S') ? -1 : 1;
        $lon_direction = ($GPSLongitudeRef == 'W' or $GPSLongitudeRef == 'S') ? -1 : 1;
        
        $latitude = $lat_direction * ($lat_degrees + ($lat_minutes / 60) + ($lat_seconds / (60*60)));
        $longitude = $lon_direction * ($lon_degrees + ($lon_minutes / 60) + ($lon_seconds / (60*60)));

        return array('latitude'=>$latitude, 'longitude'=>$longitude);
    }else{
        return false;
    }
}

function gps2Num($coordPart){
    $parts = explode('/', $coordPart);
    if(count($parts) <= 0)
    return 0;
    if(count($parts) == 1)
    return $parts[0];
    return floatval($parts[0]) / floatval($parts[1]);
}

function getaddressJson($lat,$lng,$key_maps) {
     $url = 'https://maps.googleapis.com/maps/api/geocode/json?latlng='.trim($lat).','.trim($lng).'&sensor=false&key='.$key_maps;
	 $json = @file_get_contents($url);
     $data=json_decode($json);
     $status = $data->status;
	 $address='';
     if($status=="OK") {
		 $empty=true;
		 foreach($data->results as $results) {
			if($results->geometry->location_type=='RANGE_INTERPOLATED') {
				$empty=false;
				$address=$results->formatted_address;
			} 
		 }
	 
		if($empty==true) {
			$strArray = array (
			'code' => 202,
			'description' => 'Address Found Not In Range Interpolated',
			'addresss' => $data->results[0]->formatted_address
			);
		} else {
			$strArray = array (
			'code' => 200,
			'description' => 'Address Found',
			'addresss' => $address
			);
		}
     } else {
		$strArray = array (
		'code' => 202,
		'description' => 'Address Not Found Or Token Maps is Invalid',
		'addresss' => Null
		);
	 }
	 return json_encode($strArray);
}

function getaddress($lat,$lng,$key_maps) {
     $url = 'https://maps.googleapis.com/maps/api/geocode/json?latlng='.trim($lat).','.trim($lng).'&sensor=false&key='.$key_maps;
	 $json = @file_get_contents($url);
     $data=json_decode($json);
     $status = $data->status;
	 $address='';
     if($status=="OK") {
		 $empty=true;
		 foreach($data->results as $results) {
			if($results->geometry->location_type=='RANGE_INTERPOLATED') {
				$empty=false;
				$address=$results->formatted_address;
			} 
		 }
	 
		if($empty==true) {
			$address=$data->results[0]->formatted_address;
		}
     }
	 return $address;
}

function compress($source, $destination, $quality) {

    $info = getimagesize($source);

    if ($info['mime'] == 'image/jpeg') 
        $image = imagecreatefromjpeg($source);

    elseif ($info['mime'] == 'image/gif') 
        $image = imagecreatefromgif($source);

    elseif ($info['mime'] == 'image/png') 
        $image = imagecreatefrompng($source);

    imagejpeg($image, $destination, $quality);

    return $destination;
}

function SaveToFile($cn,$data) {
	$latitudeArray=explode('.',$data['latitude']);
	$longitudeArray=explode('.',$data['longitude']);
	$latitude=$latitudeArray[0].'.'.substr($latitudeArray[1],0,7);
	$longitude=$longitudeArray[0].'.'.substr($longitudeArray[1],0,7);
	$sqlF="INSERT INTO dg_user_file SET 
	user_id='".$data['userid']."',
	created='".$data['created']."',
	expired='".$data['expired']."',
	latitude='".$latitude."',
	longitude='".$longitude."',
	address='".mysqli_real_escape_string($cn,$data['address'])."',
	file_code='".$data['code']."'";
	if(mysqli_query($cn,$sqlF)) {
		if($data['image1']!='') {
			mysqli_query($cn,"INSERT INTO dg_user_file_image SET 
			file_code='".$data['code']."',
			image='".$data['image1']."'");
		}
		if($data['image2']!='') {
			mysqli_query($cn,"INSERT INTO dg_user_file_image SET 
			file_code='".$data['code']."',
			image='".$data['image2']."'");
		}
		if($data['image3']!='') {
			mysqli_query($cn,"INSERT INTO dg_user_file_image SET 
			file_code='".$data['code']."',
			image='".$data['image3']."'");
		}
		if($data['image4']!='') {
			mysqli_query($cn,"INSERT INTO dg_user_file_image SET 
			file_code='".$data['code']."',
			image='".$data['image4']."'");
		}
		$strArray['result'][] = [
		'success' => true,
		'code' => 200,
		'expired' => date('d-m-Y H:i',strtotime($data['expired'])),
		'description' => 'Success'
		];
	} else {
		$strArray['result'][] = [
		'success' => false,
		'code' => 202,
		'description' => 'Error to Save'
		];
	}
	return json_encode($strArray);
}

function CheckRandomFileCode($cn,$user,$code) {
	$sqlF="SELECT * FROM dg_user_file WHERE user_id='".$user."' AND file_code='".$code."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF=0;
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$resultF=1;
	}
	return $resultF;
}

function validateLatitude($lat) {
    $lat_array = explode( '.' , $lat );
    if( sizeof($lat_array) !=2 ){
        return '_n_';
    }
    if ( ! ( is_numeric($lat_array[0]) && $lat_array[0]==round($lat_array[0], 0) && is_numeric($lat_array[1]) && $lat_array[1]==round($lat_array[1], 0)  ) ){
        return '_n_';
    }
    if( $lat >= -90 && $lat <= 90 ){
        return '_s_';
    }
    else {
        return '_n_';
    }
}

function validateLongitude($long) {
    $long_array = explode( '.' , $long );
    if( sizeof($long_array) !=2 ){
        return '_n_';
    }
    if (!( is_numeric($long_array[0]) && $long_array[0]==round($long_array[0], 0) && is_numeric($long_array[1]) && $long_array[1]==round($long_array[1], 0)  ) ){
        return '_n_';
    }
    if( $long >= -180 && $long <= 180 ){
        return '_s_';
    }
    else {
        return '_n_';
    }
}

function ApproveContribution($cn,$data) {
	$sqlF="UPDATE contribution SET 
	analyze_status='".$data['cbo_approval']."',
	analyze_note='".mysqli_real_escape_string($cn,$data['note_approval'])."',
	analyze_date='".date('Y-m-d H:i')."' WHERE ref_id='".$data['ref_id']."'";
	
	if(mysqli_query($cn,$sqlF)) {
		$strArray['result'][] = [
		'success' => true,
		'code' => 200,
		'description' => 'Success'
		];	
	} else {
		$strArray['result'][] = [
		'success' => false,
		'code' => 202,
		'description' => 'Error'
		];	
	}	
	return json_encode($strArray);
}

function ContributionSave($cn,$data) {
	$ref_id=CreateRefId($cn);
	$sqlF="INSERT INTO contribution SET 
	contact_name='".mysqli_real_escape_string($cn,$data['contact_name'])."',
	contact_type='".$data['contact_type']."',
	contact_phone='".$data['contact_phone']."',
	asset_category='".$data['asset_category']."',
	asset_type='".$data['asset_type']."',
	province='".$data['province']."',
	city='".$data['city']."',
	kecamatan='".$data['kecamatan']."',
	kelurahan='".$data['kelurahan']."',
	address='".mysqli_real_escape_string($cn,$data['address'])."',
	cluster_apart_name='".mysqli_real_escape_string($cn,$data['cluster_name'])."',
	property_document='".$data['certificate']."',
	square_land=".$data['square_land'].",
	property_price=".$data['price'].",
	status_data='".$data['status_data']."',
	property_floor=".$data['floor'].",
	square_building=".$data['square_building'].",
	apart_unit_floor=".$data['floor_to_unit'].",
	class_code='".$data['building_class']."',
	condition_code='".$data['building_condition']."',
	allotment_code='".$data['allotment']."',
	fieldclass_code='".$data['field_category']."',
	streetclass_code='".$data['class_street']."',
	occupancy_code='".$data['occupancy']."',
	traffic_code='".$data['traffic']."',
	flood_code='".$data['flood']."',
	discount=".$data['discount'].",
	price_after_disc=".$data['price_after_disc'].",
	price_total_building=".$data['price_total_building'].",
	price_land_per_meter=".$data['price_land_per_meter'].",
	price_unit_per_meter=0,
	latitude='".$data['latitude']."',
	longitude='".$data['longitude']."',
	createdby='".$data['userid']."',
	created='".date('Y-m-d H:i')."',
	corporate_code='".$data['corporate_code']."',
	indicate_fee=".$data['indicate_fee'].",
	building_price_per_meter=".$data['building_price_per_meter'].",
	price_building=".$data['price_building'].",
	ref_id='".$ref_id."'";
		
	//$myfile = fopen("aries_27072024.txt", "w") or die("Unable to open file!");
	//$txt = $sqlF;
	//fwrite($myfile, $txt);
	//fclose($myfile);
	//exit;
	
	if(mysqli_query($cn,$sqlF)) {		
		for($i=0;$i<$data['image_total'];$i++) {
			$image=$data['image_code'].'_'.($i+1).'.jpg';
			mysqli_query($cn,"INSERT INTO contribution_image SET 
			ref_id='".$ref_id."',
			image='".$image."'");
		}
		
		
		$imgWhatsappUrl = $data['url_image'].'assets/images/'.$data['image_code'].'_1.jpg';		
		$msgWhatsapp='Hai *'.$data['user_fullname'].'*,\nInformasi Data Digicon anda tanggal '.date('d-m-Y H:i').'\n\nNo. Ref ID :\n*'.$ref_id.'*\n\nKategori Asset :\n*'.$data['asset_category_name'].'*\n\nJenis Asset :\n*'.$data['asset_type_name'].'*\n\nHarga Penawaran :\n*Rp.'.number_format($data['price']).'*\n\nMenunggu persetujuan sebelum dipublikasikan';
		
		
		SendMessageWithFile($data['key_whatsapp'],$data['user_phone'],$msgWhatsapp,$imgWhatsappUrl);
		
		$strArray['result'][] = [
		'success' => true,
		'code' => 200,
		'description' => 'Success'
		];	
	} else {
		$strArray['result'][] = [
		'success' => false,
		'code' => 202,
		'description' => 'Error'
		];	
	}	
	return json_encode($strArray);
}

function ContributionFileSave($cn,$data,$filecode,$imagecode) {
	$ref_id=CreateRefId($cn);
	$latitude=0;
	$longitude=0;
	$sqlF="SELECT * FROM dg_user_file WHERE file_code='".$filecode."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$latitude=$rsF['latitude'];
		$longitude=$rsF['longitude'];
	}
	
	$sqlF="SELECT * FROM dg_user_file_image WHERE file_code='".$filecode."'";
	$queryF=mysqli_query($cn,$sqlF);
	$numberImage=0;
	$myImageCover='no-image-found.png';
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$numberImage++;
		if($numberImage==1) {
			$myImageCover=$rsF['image'];
		}
		mysqli_query($cn,"INSERT INTO contribution_image SET ref_id='".$ref_id."',image='".$rsF['image']."'");
	}
	
	$sqlF="INSERT INTO contribution SET 
	contact_name='".mysqli_real_escape_string($cn,$data['contact_name'])."',
	contact_type='".$data['contact_type']."',
	contact_phone='".$data['contact_phone']."',
	asset_category='".$data['asset_category']."',
	asset_type='".$data['asset_type']."',
	province='".$data['province']."',
	city='".$data['city']."',
	kecamatan='".$data['kecamatan']."',
	kelurahan='".$data['kelurahan']."',
	address='".mysqli_real_escape_string($cn,$data['address'])."',
	cluster_apart_name='".mysqli_real_escape_string($cn,$data['cluster_name'])."',
	property_document='".$data['certificate']."',
	square_land=".$data['square_land'].",
	property_price=".$data['price'].",
	status_data='".$data['status_data']."',
	property_floor=".$data['floor'].",
	square_building=".$data['square_building'].",
	apart_unit_floor=".$data['floor_to_unit'].",
	class_code='".$data['building_class']."',
	condition_code='".$data['building_condition']."',
	allotment_code='".$data['allotment']."',
	fieldclass_code='".$data['field_category']."',
	streetclass_code='".$data['class_street']."',
	occupancy_code='".$data['occupancy']."',
	traffic_code='".$data['traffic']."',
	flood_code='".$data['flood']."',
	discount=".$data['discount'].",
	price_after_disc=".$data['price_after_disc'].",
	price_total_building=".$data['price_total_building'].",
	price_land_per_meter=".$data['price_land_per_meter'].",
	price_unit_per_meter=0,
	latitude='".$latitude."',
	longitude='".$longitude."',
	createdby='".$data['createdby']."',
	created='".date('Y-m-d H:i')."',
	corporate_code='".$data['corporate_code']."',
	indicate_fee=".$data['indicate_fee'].",
	building_price_per_meter=".$data['building_price_per_meter'].",
	price_building=".$data['price_building'].",
	ref_id='".$ref_id."'";
	
	if(mysqli_query($cn,$sqlF)) {		
		
		// Remove in Temp File Table
		mysqli_query($cn,"DELETE FROM dg_user_file WHERE file_code='".$filecode."'");
		mysqli_query($cn,"DELETE FROM dg_user_file_image WHERE file_code='".$filecode."'");
		
		$imgWhatsappUrl = $data['url_image'].'assets/images/'.$myImageCover;		
		$msgWhatsapp='Hai *'.$data['user_fullname'].'*,\nInformasi Data Digicon anda tanggal '.date('d-m-Y H:i').'\n\nNo. Ref ID :\n*'.$ref_id.'*\n\nKategori Asset :\n*'.$data['asset_category_name'].'*\n\nJenis Asset :\n*'.$data['asset_type_name'].'*\n\nHarga Penawaran :\n*Rp.'.number_format($data['price']).'*\n\nMenunggu persetujuan sebelum dipublikasikan';
		SendMessageWithFile($data['key_whatsapp'],$data['user_phone'],$msgWhatsapp,$imgWhatsappUrl);
		
		
		$strArray = array(
		'success' => true,
		'code' => 200,
		'description' => 'Success'
		);	
	} else {
		$strArray = array(
		'success' => false,
		'code' => 202,
		'description' => 'Error'
		);	
	}	
	return json_encode($strArray);
}


function CreateRefId($cn) {
generate_refid:
	$refid = rand(1000000000,9999999999);
	$sqlF="SELECT * FROM contribution WHERE ref_id='".$refid."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_array($queryF)) {
		goto generate_refid;
	}
	return $refid;
}

function GetStrataCode($cn,$buildingclasscode) {
	$sqlF="SELECT strata_code FROM class_building_m WHERE code='".$buildingclasscode."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function IndikasiBiayaBangunan($cn,$stratacode,$province,$city) {
	$sqlF="SELECT ".$stratacode." FROM btb_m WHERE province='".$province."' AND city='".$city."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF=0;
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=$rsF[0];
	}
	return $resultF;
}

function StrataPercentFromBuildingCondition($cn,$code) {
	$sqlF="SELECT strata_percent FROM condition_building_m WHERE code='".$code."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF=0;
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=$rsF[0];
	}
	return $resultF;
}

function GetContributionForNotification($cn,$uid) {
	$sqlF="SELECT user_fullname FROM dg_user WHERE user_email='".$uid."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$fullnameF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$fullnameF=trim($rsF[0]);
	}
	$stsApprove=0;
	$sqlF="SELECT * FROM contribution WHERE send_notification IS NULL AND deleted='NN' AND analyze_status='RR' AND createdby='".$uid."'";
	$queryF=mysqli_query($cn,$sqlF);
	$stsReject=0;
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$stsReject=($stsReject*1)+1;
		mysqli_query($cn,"UPDATE contribution SET send_notification='".date('Y-m-d H:i')."' WHERE ref_id='".trim($rsF['ref_id'])."'");
	}
	
	$strArray[] = array (
	'approve' => $stsApprove,
	'reject' => $stsApprove,
	'fullname' => $fullnameF,
	'description' => 'Hi '.$fullnameF.', '.$stsReject.' dengan status REJECT'
	);	
	
	return json_encode($strArray);
	
}

function GetContributionForNotificationApprove($cn,$url,$uid) {
	$sqlF="SELECT user_fullname FROM dg_user WHERE user_email='".$uid."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$fullnameF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$fullnameF=trim($rsF[0]);
	}
	
	//For Approve Status
	$sqlF="SELECT * FROM contribution WHERE send_notification_approve IS NULL AND deleted='NN' AND analyze_status='OK' AND createdby='".$uid."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$stsApprove=0;
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' ORDER BY id LIMIT 1";
		$queryImage=mysqli_query($cn,$sqlImage);
		$img=$url.'assets/images/no-image-found.png';
		while($rsImage=mysqli_fetch_array($queryImage)) {
		$img=$url.'assets/images/'.$rsImage[0];
		}
		mysqli_query($cn,"UPDATE contribution SET send_notification_approve='".date('Y-m-d H:i')."' WHERE ref_id='".trim($rsF['ref_id'])."'");
		
		$strArray[] = array (
		'img' => $img,
		'ref_id' => trim($rsF['ref_id']),
		'status' => 'Disetujui'
		);	
	}	
	return json_encode($strArray);
}

	
function ContributionUpdate($cn,$client,$key,$data) {
	if(CheckClientAndKey($cn,$client,$key)=='GRANTED') {
		$sqlF="UPDATE contribution SET 
		contact_name='".mysqli_real_escape_string($cn,$data['contact_name'])."',
		contact_type='".$data['contact_type']."',
		contact_phone='".$data['contact_phone']."',
		asset_category='".$data['asset_category']."',
		asset_type='".$data['asset_type']."',
		province='".$data['province']."',
		city='".$data['city']."',
		kecamatan='".$data['kecamatan']."',
		kelurahan='".$data['kelurahan']."',
		address='".mysqli_real_escape_string($cn,$data['address'])."',
		cluster_apart_name='".mysqli_real_escape_string($cn,$data['cluster_apart_name'])."',
		property_document='".$data['property_document_code']."',
		status_data='".$data['status_data']."',
		class_code='".$data['class_code']."',
		condition_code='".$data['condition_code']."',
		allotment_code='".$data['allotment_code']."',
		fieldclass_code='".$data['fieldclass_code']."',
		streetclass_code='".$data['streetclass_code']."',
		occupancy_code='".$data['occupancy_code']."',
		traffic_code='".$data['traffic_code']."',
		flood_code='".$data['flood_code']."',
		property_floor=".$data['property_floor'].",
		apart_unit_floor=".$data['apart_unit_floor'].",
		square_land='".$data['square_land']."',
		square_building=".$data['square_building'].",
		property_price=".$data['property_price'].",
		indicate_fee=".$data['indicate_fee'].",
		building_price_per_meter=".$data['building_price_per_meter'].",
		price_building=".$data['price_building'].",";
		
		if($data['analyze']!='') {
			$sqlF=$sqlF."analyze_status='".$data['analyze']."',";
		}
		
		$sqlF=$sqlF."analyze_note='".$data['analyze_note']."',
		analyze_date='".date('Y-m-d H:i')."',
		discount='".$data['discount']."',
		price_after_disc=".$data['price_after_disc'].",
		price_total_building=".$data['price_total_building'].",
		price_land_per_meter=".$data['price_land_per_meter'].",
		price_unit_per_meter=".$data['price_unit_per_meter'].",
		latitude='".$data['latitude']."',
		longitude='".$data['longitude']."',
		posted='".date('Y-m-d H:i',strtotime($data['posted']))."',
		postedby='".$data['postedby']."',
		expired='".date('Y-m-d',strtotime($data['expired']))."' WHERE ref_id='".$data['refid']."'";
		
		
		if(mysqli_query($cn,$sqlF)) {
			$strArray = array (
				'code' => 200,
				'description' => 'Data saved successfully'
			);
		} else {
			$strArray = array (
				'code' => 202,
				'description' => 'An error occcured'
			);
		}
	
	} else {
			$strArray = array (
				'code' => 405,
				'description' => 'Client Id or Key is not allowed'
			);
	}
	
	return json_encode($strArray);
	
}

function SendMessage($key,$phone,$message) {
	$curl = curl_init();

	curl_setopt_array($curl, array(
	  CURLOPT_URL => 'http://116.203.92.59/api/async_send_message',
	  CURLOPT_RETURNTRANSFER => true,
	  CURLOPT_ENCODING => '',
	  CURLOPT_MAXREDIRS => 10,
	  CURLOPT_TIMEOUT => 0,
	  CURLOPT_FOLLOWLOCATION => true,
	  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
	  CURLOPT_CUSTOMREQUEST => 'POST',
	  CURLOPT_POSTFIELDS =>'{
	  "phone_no": "'.$phone.'",
	  "key": "'.$key.'",
	  "message": "'.$message.'"
	}',
	  CURLOPT_HTTPHEADER => array(
		'accept: */*',
		'Content-Type: application/json'
	  ),
	));

	$response = curl_exec($curl);
	curl_close($curl);
	return $response;
}

function SendMessageWithFile($key,$phone,$message,$pathfile) {
	$curl = curl_init();

	curl_setopt_array($curl, array(
	  CURLOPT_URL => 'http://116.203.92.59/api/async_send_image_url',
	  CURLOPT_RETURNTRANSFER => true,
	  CURLOPT_ENCODING => '',
	  CURLOPT_MAXREDIRS => 10,
	  CURLOPT_TIMEOUT => 0,
	  CURLOPT_FOLLOWLOCATION => true,
	  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
	  CURLOPT_CUSTOMREQUEST => 'POST',
	  CURLOPT_POSTFIELDS =>'{
	  "phone_no": "'.$phone.'",
	  "key": "'.$key.'",
	  "message": "'.$message.'",
	  "url" : "'.$pathfile.'"
	}',
	  CURLOPT_HTTPHEADER => array(
		'accept: */*',
		'Content-Type: application/json'
	  ),
	));

	$response = curl_exec($curl);
	curl_close($curl);
	return $response;
}

function GetDataDigiconFromSearchFilter($cn,$data) {
	if(CheckClientAndKey($cn,$data['client_id'],$data['key'])=='GRANTED') {
		$sqlF="SELECT * FROM contribution WHERE analyze_status='OK' AND deleted='NN' ";
		
		if($data['asset_category']!='' && $data['asset_category']!='null') {
			$sqlF=$sqlF." AND asset_category='".$data['asset_category']."' ";
		}
		if($data['asset_type']!='' && $data['asset_type']!='null') {
			$sqlF=$sqlF." AND asset_type='".$data['asset_type']."' ";
		}
		if($data['min_price']>0) {
			$sqlF=$sqlF." AND property_price>=".str_replace(',','',$data['min_price']);
		}
		if($data['max_price']>0) {
			$sqlF=$sqlF." AND property_price<=".str_replace(',','',$data['max_price']);
		}
		if($data['province']!='' && $data['province']!='null') {
			$sqlF=$sqlF." AND province='".$data['province']."' ";
		}
		if($data['kelurahan']!='' && $data['kelurahan']!='null') {
			$sqlF=$sqlF." AND kelurahan='".$data['kelurahan']."' ";
		}
		$sqlF=$sqlF." AND corporate_code='".$data['client_id']."' ORDER BY id";
		$queryF=mysqli_query($cn,$sqlF);
		$resultF=0;
		$rownumber=0;
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$rownumber++;
			if(substr($rsF['latitude'],0,1)=='-') {
				$resultF=1;
				$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
				$queryImage=mysqli_query($cn,$sqlImage);
				$image='no-image-found.png';
				while($rsImage=mysqli_fetch_array($queryImage)) {
				$image=trim($rsImage[0]);
				}
				
				$sqlAc="SELECT name FROM asset_category_m WHERE code='".$rsF['asset_category']."' LIMIT 1";
				$queryAc=mysqli_query($cn,$sqlAc);
				$asset_category='';
				while($rsAc=mysqli_fetch_array($queryAc)) {
				$asset_category=trim($rsAc[0]);
				}
				
				$sqlAt="SELECT name FROM asset_type_m WHERE code='".$rsF['asset_type']."' LIMIT 1";
				$queryAt=mysqli_query($cn,$sqlAt);
				$asset_type='';
				while($rsAt=mysqli_fetch_array($queryAt)) {
				$asset_type=trim($rsAt[0]);
				}
				
				//Marker
				if($rsF['streetclass_code']=='CR001') {
					$marker='marker_green.png';
				} else if($rsF['streetclass_code']=='CR002') {
					$marker='marker_yellow2.png';
				} else if($rsF['streetclass_code']=='CR003') {
					$marker='marker_cyan.png';
				} else if($rsF['streetclass_code']=='CR004') {
					$marker='marker_black.png';
				}
				
				$strArray[] = array (
					'ref_id' => $rsF['ref_id'],
					'latitude' => $rsF['latitude'],
					'longitude' => $rsF['longitude'],
					'asset_category' => $asset_category,
					'asset_type' => $asset_type,
					'street' => $rsF['streetclass_code'],
					'marker_image' => $marker,
					'price' =>  $rsF['property_price'],
					'image' => $image,
					'corporate_code' => $rsF['corporate_code'],
					'region_latitude' => $regionLatitude,
					'region_longitude' => $regionLongitude
				);
			}
		}
	}
	return json_encode($strArray);
}

function GetFileData ($cn,$uid) {
	$sqlF="SELECT * FROM dg_user_file WHERE user_id='".$uid."' ORDER BY id DESC";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlImage="SELECT image FROM dg_user_file_image WHERE file_code='".$rsF['file_code']."' LIMIT 1";
			$queryImage=mysqli_query($cn,$sqlImage);
			$image='no-image-found.png';
			while($rsImage=mysqli_fetch_array($queryImage)) {
				$image=$rsImage[0];
			}
			
			if(date('Y-m-d H:i',strtotime($rsF['expired'])) < date('Y-m-d H:i')) {
				$status='expired';
			} else {
				$status='';
			}
		$strArray[]=array (
			'code' => $rsF['file_code'],
			'created' => date('d-m-Y H:i',strtotime($rsF['created'])),
			'expired'=> date('d-m-Y H:i',strtotime($rsF['expired'])),
			'status' => $status,
			'address' => $rsF['address'],
			'latitude' => $rsF['latitude'],
			'longitude' => $rsF['longitude'],
			'image' => $image
		);
	}
		return json_encode($strArray);
}
	
function RemoveFileData($cn,$code) {
	$resultF='failed';
	$sqlF="SELECT image FROM dg_user_file_image WHERE file_code='".$code."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		unlink ('assets/temp_image/'.$rsF['image']);
	}
	
	mysqli_query($cn,"DELETE FROM dg_user_file_image WHERE file_code='".$code."'");
	if(mysqli_query($cn,"DELETE FROM dg_user_file WHERE file_code='".$code."'")) {
		$resultF='success';	
	}
	
	return $resultF;
}

function GetFileDataByCode($cn,$code) {
	$sqlF="SELECT * FROM dg_user_file WHERE file_code='".$code."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[]=$rsF;
	}
		return json_encode($strArray);
}	

function GetFileDataImageByCode($cn,$code) {
	$sqlF="SELECT image FROM dg_user_file_image WHERE file_code='".$code."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[]=$rsF;
	}
		return json_encode($strArray);
}

function CacheTables($cn,$tblName) {
	$sqlF="SELECT code,name FROM ".$tblName." ORDER BY id";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[]=$rsF;
	}
	return json_encode($strArray);
}

function Users($cn,$clientid) {
	$sqlF="SELECT * FROM dg_user WHERE user_corporate='".$clientid."' ORDER BY user_fullname";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$strArray[]=$rsF;
	}
	return json_encode($strArray);
}

function GetFieldDataFromTableByParams($cn,$field,$table,$params) {
	$sqlF="SELECT ".$field." FROM ".$table.$params." LIMIT 1";
	echo $sqlF;
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function GetFieldDataFromTableByParams_New($cn,$field,$table,$params) {
	$sqlF="SELECT ".$field." FROM ".$table." WHERE ".$params." LIMIT 1";
	echo $sqlF;
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function RemoveFileWorksheet($cn) {
	$twodays=date('Y-m-d H:i',strtotime('-2 day'));
	$sqlF="SELECT * FROM dg_user_file WHERE expired<='".$twodays."'";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlImage="SELECT image FROM dg_user_file_image WHERE file_code='".$rsF['file_code']."'";
		$queryImage=mysqli_query($cn,$sqlImage);
		while($rsImage=mysqli_fetch_array($queryImage)) {
			unlink ('../assets/temp_image/'.$rsImage[0]);
		}
		mysqli_query($cn,"DELETE FROM dg_user_file WHERE file_code='".$rsF['file_code']."'");
		mysqli_query($cn,"DELETE FROM dg_user_file_image WHERE file_code='".$rsF['file_code']."'");
	}
	return;
}

function DatabaseByUserAndStatus($cn,$clientid,$key,$user,$status) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		if($status==0) {
			$orderby='id ';
		} else {
			$orderby='posted DESC ';
		}	
		if($status==0) {
			$status='WW';
		} else if($status==1) {
			$status='OK';
		} else {
			$status='RR';
		}
		$sqlF="SELECT * FROM contribution WHERE createdby='".$user."' AND deleted='NN' AND analyze_status='".$status."' ORDER BY ".$orderby;
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$asset_category=GetFielNameFromTableByCode($cn,'asset_category_m',$rsF['asset_category']);
				$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
				$queryImage=mysqli_query($cn,$sqlImage);
				$image='no-image-found.png';
				while($rsImage=mysqli_fetch_array($queryImage)) {
				$image=trim($rsImage[0]);
				}
			$strArray[]=array (
			'result' => 'GRANTED',
			'refid' => $rsF['ref_id'],
			'posted' => date('d-m-Y H:i',strtotime($rsF['posted'])),
			'expired'=> date('d-m-Y',strtotime($rsF['expired'])),
			'created'=> date('d-m-Y H:i',strtotime($rsF['created'])),
			'assetcategory' => $asset_category,
			'province' => $rsF['province'],
			'kelurahan' => $rsF['kelurahan'],
			'address' => $rsF['address'],
			'image' => $image
			);
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');
	}
		return json_encode($strArray);
}

function RemoveDatabaseByRef($cn,$ref) {
	if(mysqli_query($cn,"UPDATE contribution SET deleted='YY' WHERE ref_id='".$ref."'")) {
		return 'success';
	} else {
		return 'failed';
	}
}

function DatabaseBookmark($cn,$user,$ref) {
	$sqlF="SELECT * FROM contribution_bookmark WHERE user_email='".$user."' AND ref_id='".$ref."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$existF=0;
	while($rsF=mysqli_fetch_array($queryF)) {
		$existF=1;
	}
	if($existF==0) {
		mysqli_query($cn,"INSERT INTO contribution_bookmark SET user_email='".$user."',ref_id='".$ref."'");
		$strArray=array ('result' => 'bookmark');
	} else {
		mysqli_query($cn,"DELETE FROM contribution_bookmark WHERE user_email='".$user."' AND ref_id='".$ref."'");
		$strArray=array ('result' => 'notbookmark');
	}
	return json_encode($strArray);
}

function Bookmark($cn,$clientid,$key,$user) {
	if(CheckClientAndKey($cn,$clientid,$key)=='GRANTED') {
		$sqlF="SELECT * FROM contribution_bookmark WHERE user_email='".$user."' ORDER BY id DESC";
		$queryF=mysqli_query($cn,$sqlF);
		while($rsF=mysqli_fetch_assoc($queryF)) {
			$sqlCon="SELECT * FROM contribution WHERE ref_id='".$rsF['ref_id']."' LIMIT 1";
			$queryCon=mysqli_query($cn,$sqlCon);
			while($rsCon=mysqli_fetch_assoc($queryCon)) {
				$sqlAsset="SELECT name FROM asset_category_m WHERE code='".$rsCon['asset_category']."' LIMIT 1";
				$queryAsset=mysqli_query($cn,$sqlAsset);
				$assetcategory='';
				while($rsAsset=mysqli_fetch_array($queryAsset)) {
				$assetcategory=$rsAsset['name'];
				}
				
				$sqlImage="SELECT image FROM contribution_image WHERE ref_id='".trim($rsF['ref_id'])."' LIMIT 1";
				$queryImage=mysqli_query($cn,$sqlImage);
				$image='no-image-found.png';
				while($rsImage=mysqli_fetch_array($queryImage)) {
				$image=trim($rsImage[0]);
				}
				
				$strArray[]=array(
					'ref_id' => $rsF['ref_id'],
					'assetcategory' => $assetcategory,
					'province' => $rsCon['province'],
					'kelurahan' => $rsCon['kelurahan'],
					'price' => $rsCon['property_price'],
					'image' => $image
				);
			}
		}
	} else {
		$strArray=array('result' => 'NOTGRANTED');		
	}
		return json_encode($strArray);
}

function getFieldContributionByRefId($cn,$field,$ref) {
	$sqlF="SELECT ".$field." FROM contribution WHERE ref_id='".$ref."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function GetFieldUserByEmail ($cn,$field,$email) {
	$sqlF="SELECT ".$field." FROM dg_user WHERE user_email='".$email."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	$resultF='';
	while($rsF=mysqli_fetch_array($queryF)) {
	$resultF=trim($rsF[0]);
	}
	return $resultF;
}

function ContributionImageCover($cn,$ref) {
	$sqlF="SELECT image FROM contribution_image	WHERE ref_id='".$ref."' LIMIT 1";
	$queryF=mysqli_Query($cn,$sqlF);
	$imageF='no-image-found';
	while($rsF=mysqli_fetch_array($queryF)) {
	$imageF=trim($rsF[0]);
	}
	return $imageF;
}

function UserProfile($cn,$email) {
	$sqlF="SELECT * FROM dg_user WHERE user_email='".$email."' LIMIT 1";
	$queryF=mysqli_query($cn,$sqlF);
	while($rsF=mysqli_fetch_assoc($queryF)) {
		$sqlC="SELECT dg_corporate_name,dg_token_expired FROM dg_corporate WHERE dg_code='".$rsF['user_corporate']."' LIMIT 1";
		$queryC=mysqli_query($cn,$sqlC);
		$corporateName='';
		$token='';
		while($rsC=mysqli_fetch_array($queryC)) {
			$corporateName=$rsC[0];
			$token=date('d-m-Y',strtotime($rsC[1]));
		}
		$strArray[]=array (
			'fullname' => trim($rsF['user_fullname']),
			'email' => trim($rsF['user_email']),
			'tag_id' => trim($rsF['user_tag_id']),
			'corporate' => $corporateName,
			'pin' => $rsF['user_pin'],
			'expired' => $token
		);
	}
	echo json_encode($strArray);
}

function ChangePassword($cn,$email,$newpin) {
	if(mysqli_query($cn,"UPDATE dg_user SET user_pin='".encrypt_decrypt('encrypt',$newpin)."' WHERE user_email='".$email."'")) {
		$strArray=array(
			'result' => 'success'
		);
	} else {
		$strArray=array(
			'result' => 'failed'
		);
	}
		return json_encode($strArray);
}

function GetTotalDataByAsetCategory($cn,$clientcode,$assetcategory) {
	$sqlF="SELECT COUNT(*) FROM contribution WHERE analyze_status='OK' AND deleted='NN' ";
	if($assetcategory=='AC001' || $assetcategory=='AC007') {
		$sqlF=$sqlF." AND asset_category='".$assetcategory."' ";
	} else {
		$sqlF=$sqlF." AND asset_category not in ('AC001','AC007') ";
	}
	if($clientcode!='DIG001') {
		$sqlF=$sqlF." AND corporate_code='".$clientcode."'";
	}
	$queryF=mysqli_query($cn,$sqlF);
	$totalF=0;
	while($rsF=mysqli_fetch_array($queryF)) {
	$totalF=$rsF[0];
	}
	$strArray = array (
		'total' => $totalF
	);
	return json_encode($strArray);
}
?>