<?php
include '../db/db.php';
session_start();
$log = htmlspecialchars(trim($_SESSION['user']), ENT_QUOTES, 'UTF-8');
$log_lang = htmlspecialchars(trim($_SESSION['lang']), ENT_QUOTES, 'UTF-8');

$menu = htmlspecialchars(trim($_SESSION['menu']), ENT_QUOTES, 'UTF-8');
$emp_id = htmlspecialchars(trim($_SESSION['emp_id']), ENT_QUOTES, 'UTF-8');

if (empty($log)) {
	$redirectUrl = "index.php";
	echo "<script type=\"text/javascript\">";
	echo "window.location.href = '$redirectUrl'";
	echo "</script>";

} else {

	$sqlu = "SELECT * FROM user_priv_q WHERE emp_id='$emp_id' AND user='$log' AND menu_id='$menu'";
	$resultu = mysqli_query($conn, $sqlu);
	$countu = mysqli_num_rows($resultu);

	if($countu == 1){
		$rowc = mysqli_fetch_assoc($resultu);
		$user_id = $rowc['user_id'];

		/****************************** Insert *************************************/
		date_default_timezone_set('Asia/Colombo');
		$reg_date = date("Y-m-d");
		
		if (array_key_exists('add_contractors', $_POST)) {
			$reg_year = htmlspecialchars(strip_tags(trim($_POST['reg_year'])), ENT_QUOTES, 'UTF-8');
			$name = htmlspecialchars(strip_tags(trim($_POST['name'])), ENT_QUOTES, 'UTF-8');
			$address = htmlspecialchars(strip_tags(trim($_POST['address'])), ENT_QUOTES, 'UTF-8');
			$business_reg = htmlspecialchars(strip_tags(trim($_POST['business_reg'])), ENT_QUOTES, 'UTF-8');
			$tel_home = htmlspecialchars(strip_tags(trim($_POST['tel_home'])), ENT_QUOTES, 'UTF-8');
			$tel_office = htmlspecialchars(strip_tags(trim($_POST['tel_office'])), ENT_QUOTES, 'UTF-8');
			$tel_mobile = htmlspecialchars(strip_tags(trim($_POST['tel_mobile'])), ENT_QUOTES, 'UTF-8');
			$fax = htmlspecialchars(strip_tags(trim($_POST['fax'])), ENT_QUOTES, 'UTF-8');
			$email = htmlspecialchars(strip_tags(trim($_POST['email'])), ENT_QUOTES, 'UTF-8');
			$SIDA_no = htmlspecialchars(strip_tags(trim($_POST['SIDA_no'])), ENT_QUOTES, 'UTF-8');
			$exp_date = htmlspecialchars(strip_tags(trim($_POST['exp_date'])), ENT_QUOTES, 'UTF-8');
			$bldg_grade = htmlspecialchars(strip_tags(trim($_POST['bldg_grade'])), ENT_QUOTES, 'UTF-8');
			$vat_reg = htmlspecialchars(strip_tags(trim($_POST['vat_reg'])), ENT_QUOTES, 'UTF-8');
			$VAT_no = htmlspecialchars(strip_tags(trim($_POST['VAT_no'])), ENT_QUOTES, 'UTF-8');

			if($log_lang == 1) {
				$sql_c = mysqli_query($conn, "SELECT SIDA_no FROM contractor_reg WHERE SIDA_no='$SIDA_no' AND del=0");
				if(mysqli_num_rows($sql_c) == 0) {
					// Password Hash
					$pwd = hash('sha256', $SIDA_no);
					
					$sql = "INSERT INTO contractor_reg SET name='$name', address='$address', business_reg='$business_reg', tel_home='$tel_home', tel_office='$tel_office', tel_mobile='$tel_mobile', fax='$fax', email='$email', SIDA_no='$SIDA_no', exp_date='$exp_date', bldg_grade='$bldg_grade', reg_date='$reg_date', reg_year='$reg_year', user='$user_id', vat_reg='$vat_reg', pwd='$pwd', VAT_no='$VAT_no'";

					if(mysqli_query($conn, $sql)) {
						echo json_encode(array("msgType" => 1, "msg" => "Successfully Added"));
					
					} else {
						echo json_encode(array("msgType" => 2, "msg" => "Sorry ! Could not be Save Data"));
					}
				} else {
					echo json_encode(array("msgType" => 3, "msg" => "Duplicate SIDA_no"));
				}
			}
		}

		/****************************** Remove *************************************/
		
		if (array_key_exists('remove_reg', $_POST)) {

			if($log_lang == 1) {
			
				$reg_id = htmlspecialchars(strip_tags(trim($_POST['reg_id'])), ENT_QUOTES, 'UTF-8');
				$sqld = mysqli_query($conn, "SELECT * FROM aworded_contractors WHERE supplier='$reg_id'");
				
				if(mysqli_num_rows($sqld) == 0) {
					$sql = "UPDATE contractor_reg SET del=1 WHERE id='$reg_id'";
					
					if(!empty($reg_id)) {
					
						if(mysqli_query($conn, $sql)) {
							echo json_encode(array("msgType" => 1, "msg" => "Successfully Removed"));
						
						} else {
							echo json_encode(array("msgType" => 2, "msg" => "Sorry ! not deleted"));
						}
					
					} else {
						echo json_encode(array("msgType" => 2, "msg" => "Sorry ! empty id"));
					}
				
				} else {
					echo json_encode(array("msgType" => 3, "msg" => "Data Already Exist"));
				}
			
			}

		}		
		
		/****************************** Load Form Data *************************************/
		
		if (array_key_exists('get_dataset', $_POST)) {

			$reg_id = htmlspecialchars(strip_tags(trim($_POST['reg_id'])), ENT_QUOTES, 'UTF-8');
			
			$data = array();
			$sql = mysqli_query($conn, "SELECT * FROM contractor_reg WHERE id=$reg_id AND del=0");

			while ($row = mysqli_fetch_assoc($sql)) {
				$data[] = $row;
			}
			echo json_encode($data);
		}
		
		/****************************** Update  *************************************/
		
		if (array_key_exists('update_reg', $_POST)) {
			$reg_id = htmlspecialchars(strip_tags(trim($_POST['reg_id'])), ENT_QUOTES, 'UTF-8');
			
			$reg_year = htmlspecialchars(strip_tags(trim($_POST['reg_year'])), ENT_QUOTES, 'UTF-8');
			$name = htmlspecialchars(strip_tags(trim($_POST['name'])), ENT_QUOTES, 'UTF-8');
			$address = htmlspecialchars(strip_tags(trim($_POST['address'])), ENT_QUOTES, 'UTF-8');
			$business_reg = htmlspecialchars(strip_tags(trim($_POST['business_reg'])), ENT_QUOTES, 'UTF-8');
			$tel_home = htmlspecialchars(strip_tags(trim($_POST['tel_home'])), ENT_QUOTES, 'UTF-8');
			$tel_office = htmlspecialchars(strip_tags(trim($_POST['tel_office'])), ENT_QUOTES, 'UTF-8');
			$tel_mobile = htmlspecialchars(strip_tags(trim($_POST['tel_mobile'])), ENT_QUOTES, 'UTF-8');
			$fax = htmlspecialchars(strip_tags(trim($_POST['fax'])), ENT_QUOTES, 'UTF-8');
			$email = htmlspecialchars(strip_tags(trim($_POST['email'])), ENT_QUOTES, 'UTF-8');
			$SIDA_no = htmlspecialchars(strip_tags(trim($_POST['SIDA_no'])), ENT_QUOTES, 'UTF-8');
			$exp_date = htmlspecialchars(strip_tags(trim($_POST['exp_date'])), ENT_QUOTES, 'UTF-8');
			$bldg_grade = htmlspecialchars(strip_tags(trim($_POST['bldg_grade'])), ENT_QUOTES, 'UTF-8');
			$vat_reg = htmlspecialchars(strip_tags(trim($_POST['vat_reg'])), ENT_QUOTES, 'UTF-8');
			$VAT_no = htmlspecialchars(strip_tags(trim($_POST['VAT_no'])), ENT_QUOTES, 'UTF-8');

			if($log_lang == 1) {
				$sql_c = mysqli_query($conn, "SELECT SIDA_no FROM contractor_reg WHERE SIDA_no='$SIDA_no' AND id!='$reg_id' AND del=0");
				if(mysqli_num_rows($sql_c) == 0) {
					$sql_up = mysqli_query($conn, "UPDATE contractor_reg SET reg_year='$reg_year', name='$name', address='$address', business_reg='$business_reg', tel_home='$tel_home', tel_office='$tel_office', tel_mobile='$tel_mobile', fax='$fax', email='$email', SIDA_no='$SIDA_no', exp_date='$exp_date', bldg_grade='$bldg_grade', user='$user_id', vat_reg='$vat_reg', VAT_no='$VAT_no' WHERE id='$reg_id'");

					if($sql_up) {
						echo json_encode(array("msgType" => 1, "msg" => "Successfully Added"));
					
					} else {
						echo json_encode(array("msgType" => 2, "msg" => "Sorry ! Could not be Save Data"));
					}
				} else {
					echo json_encode(array("msgType" => 3, "msg" => "Duplicate SIDA_no"));
				}
			}
		}		

		/********************************************************************************************************/
		
		if (array_key_exists('get_supplier', $_POST)) {			
			$pid = htmlspecialchars(strip_tags(trim($_POST['id'])), ENT_QUOTES, 'UTF-8');
			$sql = "SELECT
contractor_reg.name,
aworded_contractors.supplier,
aworded_contractors.pub_supplier,
aworded_contractors.contract_type
FROM
aworded_contractors
Inner Join contractor_reg ON aworded_contractors.supplier = contractor_reg.id
WHERE
aworded_contractors.project =  '$pid'  AND
contractor_reg.del =  '0'
";
		
			$result = mysqli_query($conn, $sql);
			echo'<option value="">Select Supplier...</option>';
			while ($row = mysqli_fetch_assoc($result)) {
				$data[] = $row;
			}
		
			if (!empty($data)) {
				foreach ($data AS $data) {
					if($data['contract_type'] == 'reg') {
						$con = '';
					} else {
						$con = ' - '.$data['pub_supplier'];
					}
					
					echo'<option value="' . $data['supplier'] . '">' . $data['name'].$con. '</option>';
				}
			} else {
				//echo'<option value="">No Data Found</option>';
			}
			//echo json_encode($data);
		}
		
		/*****************************************************************************************************/	
		
		if (array_key_exists('get_sup_details', $_POST)) {
			
			$pid = htmlspecialchars(strip_tags(trim($_POST['pid'])), ENT_QUOTES, 'UTF-8');
			$supplier = htmlspecialchars(strip_tags(trim($_POST['supplier'])), ENT_QUOTES, 'UTF-8');

			if(empty($pid)) {
				$data = array();
				
			} else {
				//$sql_c = mysqli_fetch_array(mysqli_query($conn, "SELECT c_amount, pro_sum, pub_supplier FROM aworded_contractors WHERE supplier='$supplier' AND project='$pid'"));
				
				$sql_sup = mysqli_query($conn, "SELECT vat_reg FROM contractor_reg WHERE id='$supplier'");
				$sql_con = mysqli_query($conn, "SELECT
aworded_contractors.c_amount,
aworded_contractors.pro_sum,
aworded_contractors.pub_supplier,
projects_reg.total_cost_esti AS allocation_tot
FROM
projects_reg
Inner Join aworded_contractors ON projects_reg.id = aworded_contractors.project
WHERE
aworded_contractors.supplier = '$supplier' AND
aworded_contractors.project = '$pid'");

				if(mysqli_num_rows($sql_sup) == 1 && mysqli_num_rows($sql_con) == 1) {
					$sql_s = mysqli_fetch_array($sql_sup);
					$vat_reg = $sql_s['vat_reg'];
					
					$sql_c = mysqli_fetch_array($sql_con);
					$c_amount = $sql_c['c_amount'];
					$pro_sum = $sql_c['pro_sum'];
					$pub_supp = $sql_c['pub_supplier'];
					$alloc_tot = $sql_c['allocation_tot'];
					$pro_val = $c_amount - $pro_sum;
					
					$sql_p = mysqli_query($conn, "SELECT payment_type FROM project_invoice WHERE supplier='$supplier' AND project='$pid' AND payment_type='Advance Payment' AND flag != 2");
					$count = mysqli_num_rows($sql_p);
					
					$sql_p1 = mysqli_query($conn, "SELECT payment_type FROM project_invoice WHERE supplier='$supplier' AND project='$pid' AND flag != 2");
					$countp = mysqli_num_rows($sql_p1);

					$sql_tot = mysqli_fetch_array(mysqli_query($conn, "SELECT SUM(tot_pay) AS tot_payment, SUM(less_reten_val) AS tot_reten, SUM(vat_val) AS tot_VAT FROM project_invoice WHERE supplier='$supplier' AND project='$pid' AND payment_type='Part Payment' AND flag != 2"));
					
					$tot_reten = $sql_tot['tot_reten'];
					if($tot_reten == NULL) {
						$tot_reten = 0;
					}
					
					$tot_payment = $sql_tot['tot_payment'] - $sql_tot['tot_VAT'];
					$prevGross_tot = $sql_tot['tot_payment'] - $sql_tot['tot_VAT'];
					
					$sql_gr = mysqli_fetch_array(mysqli_query($conn, "SELECT (tot_pay-vat_val) AS prevGross FROM project_invoice WHERE supplier='$supplier' AND project='$pid' AND payment_type='Part Payment' AND flag != 2 ORDER BY id DESC LIMIT 1"));
					$prevGross = $sql_gr['prevGross'];
					
					if($prevGross == NULL) {
						$prevGross = 0;
					}
					
					$data = array('vat_reg' => $vat_reg, 'c_amount' => $c_amount, 'pro_sum' => $pro_sum, 'pro_val' => $pro_val, 'tot_payment' => $tot_payment, 'tot_reten' => $tot_reten, 'pub_supp' => $pub_supp, 'allocation_tot' => $alloc_tot, 'count' => $count, 'countp' => $countp, 'prevGross' => $prevGross, 'prevGross_tot' => $prevGross_tot);
				
				} else {
					$data = array();
				}
			}
			
			echo json_encode($data);
		}
		
		/*****************************************************************************************************/			

	} else{
		$redirectUrl = "index.php";
		echo "<script type=\"text/javascript\">";
		echo "window.location.href = '$redirectUrl'";
		echo "</script>";	
	}
}
?>