invisio v2.0

<?php

function connectDB($secure)
{
	if ($secure == "30!dkxpGq1%21l") {

// $dbhost = "localhost";
		// $dbuser = "admin_w2";
		// $dbpass = "tcn0tvtK";
		// $dbname = "admin_w2";

$dbhost = "localhost";
		$dbuser = "admin_hrtw";
		$dbpass = "yoqLNeOK";
		$dbname = "admin_hrtw";

$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
		mysqli_set_charset($connection, "utf8");

if (!$connection) {
			die("Connection failed: " . mysqli_connect_error());
		} else {
			return $connection;
		}
	} else {
		return false;
	}
}
function stringInsert($str, $insertstr, $pos)
{
	$str = substr($str, 0, $pos) . $insertstr . substr($str, $pos);
	return $str;
}
function deleteTemp($line_id)
{
	$connection = connectDB("30!dkxpGq1%21l");

$sql_delete = "DELETE FROM tbl_line_temp WHERE line_id = '$line_id';";
	$delete = mysqli_query($connection, $sql_delete);
	return $delete;
}
function deleteRegister($line_id, $worker_id)
{
	$connection = connectDB("30!dkxpGq1%21l");

$sql_delete = "DELETE FROM tbl_line_register WHERE line_id = '$line_id' AND worker_id = '$worker_id';";
	$delete = mysqli_query($connection, $sql_delete);
	return $delete;
}

function getFormatFlexMessage($listFlexButton)
{
	$datas = array(
		"type" => "flex",
		"altText" => "กล่องข้อความ",
		"contents" => array(
			"type" => "bubble",
			"body" => array(
				"type" => "box",
				"layout" => "vertical",
				"alignItems"  => "center",
				"contents" => $listFlexButton,
			)
		)
	);
	return $datas;
}

function sentMessage($encodeJson, $datas)
{
	$datasReturn = [];
	$curl = curl_init();
	curl_setopt_array($curl, array(
		CURLOPT_URL => $datas['url'],
		CURLOPT_RETURNTRANSFER => true,
		CURLOPT_ENCODING => "",
		CURLOPT_MAXREDIRS => 10,
		CURLOPT_TIMEOUT => 30,
		CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		CURLOPT_CUSTOMREQUEST => "POST",
		CURLOPT_POSTFIELDS => $encodeJson,
		CURLOPT_HTTPHEADER => array(
			"authorization: Bearer " . $datas['token'],
			"cache-control: no-cache",
			"content-type: application/json; charset=UTF-8",
		),
	));

$response = curl_exec($curl);
	$err = curl_error($curl);

curl_close($curl);

if ($err) {
		$datasReturn['result'] = 'E';
		$datasReturn['message'] = $err;
		$datasReturn['text'] = $encodeJson;
	} else {
		if ($response == "{}") {
			$datasReturn['result'] = 'S';
			$datasReturn['message'] = 'Success';
			$datasReturn['text'] = $encodeJson;
		} else {
			$datasReturn['result'] = 'E';
			$datasReturn['message'] = $response;
			$datasReturn['text'] = $encodeJson;
		}
	}

return $datasReturn;
}

function sentMessageImage($encodeJson, $datas)
{
	$datasReturn = [];
	$curl = curl_init();
	curl_setopt_array($curl, array(
		CURLOPT_URL => $datas['url'],
		CURLOPT_RETURNTRANSFER => true,
		CURLOPT_ENCODING => "",
		CURLOPT_MAXREDIRS => 10,
		CURLOPT_TIMEOUT => 30,
		CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
		CURLOPT_CUSTOMREQUEST => "POST",
		CURLOPT_POSTFIELDS => $encodeJson,
		CURLOPT_HTTPHEADER => array(
			"authorization: Bearer " . $datas['token'],
			"cache-control: no-cache",
			"content-type: application/json; charset=UTF-8",
		),
	));

$response = curl_exec($curl);
	$err = curl_error($curl);

curl_close($curl);

if ($err) {
		$datasReturn['result'] = 'E';
		$datasReturn['message'] = $err;
	} else {
		if ($response == "{}") {
			$datasReturn['result'] = 'S';
			$datasReturn['message'] = 'Success';
		} else {
			$datasReturn['result'] = 'E';
			$datasReturn['message'] = $response;
		}
	}

return $datasReturn;
}

$dayTH = ['อาทิตย์', 'จันทร์', 'อังคาร', 'พุธ', 'พฤหัสบดี', 'ศุกร์', 'เสาร์'];
$monthTH = [null, 'มกราคม', 'กุมภาพันธ์', 'มีนาคม', 'เมษายน', 'พฤษภาคม', 'มิถุนายน', 'กรกฎาคม', 'สิงหาคม', 'กันยายน', 'ตุลาคม', 'พฤศจิกายน', 'ธันวาคม'];
$monthTH_brev = [null, 'ม.ค.', 'ก.พ.', 'มี.ค.', 'เม.ย.', 'พ.ค.', 'มิ.ย.', 'ก.ค.', 'ส.ค.', 'ก.ย.', 'ต.ค.', 'พ.ย.', 'ธ.ค.'];
function thai_date_short($time)
{   // 19  ธ.ค. 2556a
	global $dayTH, $monthTH_brev;
	$thai_date_return = date("j", $time);
	$thai_date_return .= " " . $monthTH_brev[date("n", $time)];
	$thai_date_return .= " " . (date("y", $time) + 43);
	return $thai_date_return;
}
function thai_no_date_fullmonth($time)
{   // ธันวาคม 2556
	global $dayTH, $monthTH;
	$thai_date_return = $monthTH[date("n", $time)];
	$thai_date_return .= " " . (date("Y", $time) + 543);
	return $thai_date_return;
}
function thai_no_date_shortmonth($time)
{   // ธันวาคม 2556
	global $dayTH, $monthTH_brev;
	$thai_date_return = $monthTH_brev[date("n", $time)];
	$thai_date_return .= " " . (date("Y", $time) + 543);
	return $thai_date_return;
}
function thai_date_shortmonth($time)
{   // ธันวาคม 2556
	global $dayTH, $monthTH_brev;
	$thai_date_return = date("j", $time);
	$thai_date_return .= " " . $monthTH_brev[date("n", $time)];
	$thai_date_return .= " " . (date("Y", $time) + 543);
	return $thai_date_return;
}
function thai_date_full($time)
{   // 19  ธ.ค. 2556a
	global $dayTH, $monthTH;
	$thai_date_return = date("j", $time);
	$thai_date_return .= " " . $monthTH[date("n", $time)];
	$thai_date_return .= " " . (date("Y", $time) + 543);
	return $thai_date_return;
}

function clean($string)
{
	return preg_replace('/[^\p{L}\p{M}\p{Z}\p{N}\p{P}]/u', ' ', $string);
}

function imagettftextright($image, $size, $x, $y, $color, $fontfile, $text)
{
	// Get height of single line
	$rect = imagettfbbox($size, 0, $fontfile, "Tq");
	$minY = min(array($rect[1], $rect[3], $rect[5], $rect[7]));
	$maxY = max(array($rect[1], $rect[3], $rect[5], $rect[7]));
	$h1 = $maxY - $minY;

// Get height of two lines
	$rect = imagettfbbox($size, 0, $fontfile, "Tq\nTq");
	$minY = min(array($rect[1], $rect[3], $rect[5], $rect[7]));
	$maxY = max(array($rect[1], $rect[3], $rect[5], $rect[7]));
	$h2 = $maxY - $minY;

// amount of padding that should be between each line
	$vpadding = $h2 - $h1 - $h1;

// calculate the dimensions of the text itself
	$frect = imagettfbbox($size, 0, $fontfile, $text);
	$minX = min(array($frect[0], $frect[2], $frect[4], $frect[6]));
	$maxX = max(array($frect[0], $frect[2], $frect[4], $frect[6]));
	$text_width = $maxX - $minX;

$text = explode("\n", $text);
	foreach ($text as $txt) {
		$rect = imagettfbbox($size, 0, $fontfile, $txt);
		$minX = min(array($rect[0], $rect[2], $rect[4], $rect[6]));
		$maxX = max(array($rect[0], $rect[2], $rect[4], $rect[6]));
		$minY = min(array($rect[1], $rect[3], $rect[5], $rect[7]));
		$maxY = max(array($rect[1], $rect[3], $rect[5], $rect[7]));

$width = $maxX - $minX;
		$height = $maxY - $minY;

$_x = ($x + $text_width) - $width;

imagettftext($image, $size, 0, $_x, $y, $color, $fontfile, $txt);
		$y += ($height + $vpadding);
	}

return $rect;
}
function Wallet($worker_id){
	$connection = connectDB("30!dkxpGq1%21l");

$total_wage = 0;
	$final = 0;
	$check_date = date("Y-m-d");
	//เช็คว่าจ่ายเป็นรายเดือนหรือรายวัน
	$sql_check_pay = "SELECT position,pay_roll_status,site_id FROM tbl_worker WHERE worker_id = '$worker_id' ";
	$rs_check_pay = mysqli_query($connection, $sql_check_pay) or die($connection->error);
	$row_check_pay = mysqli_fetch_array($rs_check_pay);
	$site_id = $row_check_pay['site_id'];
	if ($row_check_pay['position'] != 1) {
		if ($row_check_pay['pay_roll_status'] == 2) {
			$period_type = '2';
		} else {
			$period_type = '3';
		}
	} else if ($row_check_pay['position'] = 1) {
		$period_type = '1';
	}
	// หางวดใกล้เคียง 
	$sql_find_period = "SELECT *
							FROM tbl_site_period
							WHERE (start_date <= '$check_date')
							AND site_id = '{$row_check_pay['site_id']}'
							AND period_type = '$period_type'  ORDER BY start_date DESC LIMIT  1 ;";
	// echo $sql_find_period;
	$rs_find_period = mysqli_query($connection, $sql_find_period) or die($connection->error);
	$row_find_period = mysqli_fetch_array($rs_find_period);
	$start_date = $row_find_period['start_date'];
	if ($row_find_period['start_date'] != "") {
		//เงินเพิ่มี่มีการตีย์
		$sql_extra = "SELECT SUM(extra_cost) as total_extra
					  FROM tbl_extra_money 
					  WHERE extra_date BETWEEN '$start_date' AND '$check_date'
					  AND worker_id = '$worker_id' 
					  AND cancel_status = 0";
		$rs_extra = mysqli_query($connection, $sql_extra) or die($connection->error);
		$row_extra = mysqli_fetch_array($rs_extra);
		////////////////////////////////////////////////////////////////
		// เงินเพิ่มจากการตั้งค่า
		$sql_personal = "SELECT SUM(extra_cost) as personal_extra FROM tbl_personal_extra WHERE worker_id = '$worker_id' ;";
		$rs_personal = mysqli_query($connection, $sql_personal) or die($connection->error);
		$row_personal = mysqli_fetch_array($rs_personal);
		// หาจำนวนวันทำงาน
		$sql_find_check_month = "SELECT COUNT(worker_id) as work_day
								 FROM tbl_working_check
								 WHERE working_date BETWEEN '$start_date'AND '$check_date' 
								 AND worker_id = '$worker_id'
								 AND site_id = '$site_id'
								 AND approve_status = '1';";
		$rs_find_check_month = mysqli_query($connection, $sql_find_check_month) or die($connection->error);
		$row_find_check_month = mysqli_fetch_array($rs_find_check_month);
		if ($row_find_period['period_type'] == 2) {
			$personal_extra = ($row_personal['personal_extra'] / 30) * $row_find_check_month['work_day'];
		} else {
			$personal_extra = ($row_personal['personal_extra'] / 15) * $row_find_check_month['work_day'];
		}
		$extra_money = $row_extra['total_extra'] + $personal_extra;
		$fine_cost = 0;
		$sql_fine = "SELECT IFNULL(SUM(fine_cost),0) as total_fine
					 FROM tbl_fine_cost
					 WHERE fine_date BETWEEN '$start_date' AND '$check_date'
					 AND worker_id = '$worker_id'
					 AND cancel_status = 0";

$rs_fine = mysqli_query($connection, $sql_fine) or die($connection->error);
		// echo $sql_fine;
		$row_fine = mysqli_fetch_array($rs_fine);
		$fine_cost = $row_fine['total_fine'];
		if ($row_check_pay['position'] != "2") {
			//หายอดรวมค่าแรงหลังจากวันจ่ายเงินเดือนล่าสุดที่ได้รับการอนุมัติแล้ว
			$sql_total_wage = "SELECT SUM(wage_rate) as total_wage 
						   FROM tbl_working_check 
						   WHERE working_date BETWEEN '$start_date' 
						   AND '$check_date' AND worker_id = '$worker_id'
						   AND site_id = '$site_id'
						   AND approve_status = '1'";
			$rs_total_wage = mysqli_query($connection, $sql_total_wage) or die($connection->error);
			$row_total_wage = mysqli_fetch_array($rs_total_wage);
			// echo $sql_total_wage;
			$total_wage = $row_total_wage['total_wage'];
			// echo $total_wage;
			// 90% ของค่าแรง
		} else {
			// echo "aaa";
			$sql_total_wage_month = "SELECT wage as total_wage
						   FROM tbl_worker
						   WHERE worker_id = '$worker_id';";
			$rs_total_wage_month = mysqli_query($connection, $sql_total_wage_month) or die($connection->error);
			$row_total_wage_month = mysqli_fetch_array($rs_total_wage_month);
			// หา OT
			$sql_ot = "SELECT SUM(otx_1) as otx_1,
						  SUM(otx_15) as otx_15,
						  SUM(otx_week) as otx_week,
						  SUM(otx_cu) as otx_cu,
						  SUM(otx_3) as otx_3
				   FROM tbl_working_check 
				   WHERE working_date BETWEEN '$start_date' AND '$check_date'
				   AND worker_id = '$worker_id'
				   AND site_id = '$site_id'
				   AND approve_status = '1';";
			$rs_ot = mysqli_query($connection, $sql_ot) or die($connection->error);
			$row_ot = mysqli_fetch_array($rs_ot);

$wage_per_day = $row_total_wage_month['total_wage'] / 30;
			$wage_per_h = $wage_per_day / 8;
			$otx_1 = $wage_per_h * $row_ot['otx_1'];
			$otx_15 = ($wage_per_h * $row_ot['otx_15']) * 1.5;
			$otx_week = ($wage_per_h * $row_ot['otx_week']) * 2;
			$otx_cu = ($wage_per_h * $row_ot['otx_cu']) * 2;
			$otx_3 = ($wage_per_h * $row_ot['otx_3']) * 3;
			$temp_ot = ($otx_1 + $otx_15 + $otx_week + $otx_cu + $otx_3);
			$temp_wage = $wage_per_day * $row_find_check_month['work_day'];
			$total_wage = $temp_wage + $temp_ot;
		}
		// สูตรเก่า
		$withdraw_before_cut = $total_wage - 500;
		// echo $withdraw_before_cut;
		//หาเงินผ่อน และ เงินกู้ของผู้เบิก
		$sql_find_installment = "SELECT * 
								 FROM tbl_installment_head 
								 WHERE worker_id = '$worker_id' AND cancel_status IS NULL";
		$rs_find_installment = mysqli_query($connection, $sql_find_installment) or die($connection->error);
		// echo $sql_f
		$cut_installment = 0;
		$loan = 0;
		$withdraw_early = 0;
		while ($row_find_installment = mysqli_fetch_array($rs_find_installment)) {
			$installment_id = $row_find_installment['installment_id'];
			if ($installment_id != "") {
				$sql_cal_insta = "SELECT b.installment_paid FROM tbl_installment_head a 
				LEFT JOIN tbl_installment_detail b ON a.installment_id = b.installment_id 
				WHERE a.installment_id = '$installment_id'
				AND a.worker_id = '$worker_id' 
				AND b.pay_status = '0'
				AND b.cancel_status = '0' 
				ORDER BY b.list_order ASC LIMIT 1";
				$rs_cal_insta = $connection->query($sql_cal_insta) or die($connection->error);
				$row_cal_insta = $rs_cal_insta->fetch_assoc();
				$cal_installment = $row_cal_insta['installment_paid'];
				$cut_installment += $cal_installment;
				// echo $cut_installment;
			}
		}

//เงินกู้
		$sql_lone = "SELECT COUNT(*) AS lone FROM tbl_loan_head a 
				LEFT JOIN tbl_loan_detail b ON a.loan_id = b.loan_id 
				WHERE a.worker_id = '$worker_id' AND b.pay_status = '0'";
		$rs_lone = $connection->query($sql_lone) or die($connection->error);
		$row_lone = $rs_lone->fetch_assoc();

if ($row_lone['lone'] >= "1") {
			$sql_cal_lone = "SELECT * FROM tbl_loan_head a 
								 LEFT JOIN tbl_loan_detail b ON a.loan_id = b.loan_id 
								 WHERE a.worker_id = '$worker_id' 
								 AND b.pay_status = '0' 
								 ORDER BY b.list_order ASC LIMIT 1";
			$rs_cal_lone = $connection->query($sql_cal_lone) or die($connection->error);
			$row_cal_lone = $rs_cal_lone->fetch_assoc();

$loan = $row_cal_lone['paid'];
		}
		////เอามาหักกับเงินกู้และเงินผ่อน
		$withdraw_after_cut = ($withdraw_before_cut - $cut_installment - $loan - $fine_cost) + $extra_money;
		///หาเงินเบิกก่อนหน้า
		$sql_withdraw_ealry = "SELECT SUM(deposit_amount + (deposit_amount * interest_advance / 100)) as withdraw_total 
							   FROM tbl_advance_deposit 
							   WHERE create_date BETWEEN '$start_date 00:00:01' AND '$check_date 23:59:59'
							   AND worker_id = '$worker_id' 
							   AND approve_result = 1
							   AND cancel_date IS NULL;";

//echo $sql_withdraw_ealry;
		$rs_withdraw_early = mysqli_query($connection, $sql_withdraw_ealry) or die($connection->error);
		$row_withdraw_early = mysqli_fetch_array($rs_withdraw_early);
		// echo $sql_withdraw_ealry;
		$withdraw_early = $row_withdraw_early['withdraw_total'];
		///รวม(หัก 1000 ค่า PPE)
		$final = ($withdraw_after_cut - $withdraw_early);
	} else {
		$final = 0;
	}
	return $final;
}