<?php
require_once('config.php');
require_once('utility_image.php');
require_once('f_resize_imagepath.php');

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

$error_message;
$valid = true;
$counter = 0;


$json_return = array();
$json_img_link = array();

$json = file_get_contents('php://input');
$data = json_decode($json, true);

$site_cd 			= $data['site_cd'];
$mst_RowID 			= $data['mst_RowID'];
$wko_isp_asset_no 	= $data['wko_isp_asset_no'];
$dvc_id				= $data['dvc_id'];
$Folder 			= $data['folder'];
$LOGINID 			= $data['LOGINID'];
$MobileURL 			= $data['url'];
$wko_isp_job_cd 	= $data['wko_isp_job_cd'];

$uploaddir = "C:\\wamp64\\www\\".$Folder."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";

if (!is_dir($uploaddir)) {
	mkdir($uploaddir, 0777, true);
	mkdir($uploaddir."\\thumbnail\\", 0777, true);
}

$MobileURLFullSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/";
$MobileURLTnSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/thumbnail/";

$exportdir = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";
$exportdirtn = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\thumbnail\\";

$sql = "SELECT RowID, attachment, file_name  from wko_isp (NOLOCK) 
		where 	site_cd ='".$site_cd."' 
		And 	mst_RowID =".$mst_RowID."
		And 	DataLength(attachment) > 0 
		And		DataLength(attachment) is not null
		And 	mst_RowID is not null
		And 	file_name not like '%xlsx%' 
		And 	file_name not like '%xls%'
		AND 	wko_isp_asset_no = '".$wko_isp_asset_no."'
		AND 	wko_isp_job_cd = '".$wko_isp_job_cd."'";

$stmt = sqlsrv_query($conn, $sql);

if (!$stmt) {
	 
	 $error_messages = [];
	 if (($errors = sqlsrv_errors()) != null) {
        foreach ($errors as $error) {
            // Collect all error messages
            $error_messages[] = $error['message'];
        }
     }
     sqlsrv_rollback($conn);
     sqlsrv_close($conn);
     returnError("Error insert table (wko_isp)"  .implode("; ",$error_messages));
	 
}

//$json_img_link = array();

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		 
		$Photo = $row['attachment'];
		$fileName = $row['file_name'];
		$rowid = $row['RowID'];
		
		
		// Select file type
		$extension = strtolower(pathinfo($fileName,PATHINFO_EXTENSION));
		// Valid file extensions
		$videoFormats = array("3gp", "avi", "mov", "mp4", "mpeg", "m4v", "mkv", "flv", "webm", "wmv");
		
		$imageFormats   = array("jpg", "jpeg", "png", "gif", "bmp", "webp", "tiff", "tif", "ico", "heic", "heif", "svg", "psd", "raw", "arw", "cr2", "nef", "orf", "rw2","cr2", "cr3", "crw", "nef", "nrw", "arw", "sr2", "srf", "rw2", "orf", "raf", "dng", "erf", "k25", "kdc", "mef", "mrw", "pef", "x3f", "iiq","avs", "dds", "djvu", "dcx", "dpx", "exr", "fits", "flif", "hrz", "icb", "j2c", "j2k", "jp2", "jpc", "jpf", "jpm", "jps", "jpt", "miff", "mng", "mtv", "ora", "otb", "palm", "pam", "pbm", "pcd", "pcds", "pct", "pcx", "pgm", "pgx", "pix", "pnm", "ppm", "ptif", "ras", "sgi", "sun", "tga", "vda", "viff", "wbmp", "xbm", "xcf", "xpm", "yuv","jfif");
		
		$documentFormats = array("pdf","doc", "docx", "epub", "odt", "txt", "rtf", "html", "csv", "xlsx", "ppt", "pptx", "ps", "txt", "json", "xml", "yaml", "odf", "xps");
		
		if (in_array($extension, $imageFormats)) {
			
			$file_name = generateUniqueName($fileName,$rowid);
				
			$exportFullPathFileName = $exportdir.$file_name;
			$exportFullPathTNFileName = $exportdirtn.$file_name;
				 
			$publicFullPathFileName = $MobileURLFullSize.$file_name;		  
			$publicFullPathTNFileName = $MobileURLTnSize.$file_name;
			
			if (!file_exists($exportFullPathFileName)) {
				// If the full-size file does not exist, write the file
				writeFile($exportFullPathFileName, $Photo);
			}

			if (!file_exists($exportFullPathTNFileName)) {
				// If the thumbnail does not exist, resize and write the thumbnail
				resizeImageImagick($Photo, $exportFullPathTNFileName);
			} 
			
			
			$sql="	UPDATE 	wko_isp 
					SET 
							file_link ='".$publicFullPathFileName."',
							file_name ='".$file_name."'
					WHERE 	RowID = '".$rowid."' ;";	
					
							
			$Update_wko_isp_stmt = sqlsrv_query( $conn, $sql);

			if( !$Update_wko_isp_stmt ) {
				
				 $error_messages = [];
				 if (($errors = sqlsrv_errors()) != null) {
					foreach ($errors as $error) {
						$error_messages[] = $error['message'];
					}
				 }
				 sqlsrv_rollback($conn);
				 sqlsrv_close($conn);
				 returnError("Error insert table (wko_isp)"  .implode("; ",$error_messages));
			}

			while ( sqlsrv_next_result($Update_wko_isp_stmt) );

			
		}
		   

    }
} while (sqlsrv_next_result($stmt));


$sql = "SELECT rowid,
			mst_rowid,
			site_cd,
			wko_isp_asset_no,
			wko_isp_job_cd,
			wko_isp_job_desc,
			wko_isp_sec_no,
			wko_isp_sec_desc,
			wko_isp_stp_no,
			wko_isp_stp_desc,
			wko_isp_stp_datatype,
			wko_isp_stp_rowid,
			wko_isp_varchar1,
			wko_isp_varchar2,
			wko_isp_varchar3,
			wko_isp_numeric1,
			wko_isp_numeric2,
			wko_isp_numeric3,
			wko_isp_datetime1,
			wko_isp_datetime2,
			wko_isp_datetime3,
			wko_isp_checkbox1,
			wko_isp_checkbox2,
			wko_isp_checkbox3,
			wko_isp_dropdown1,
			wko_isp_dropdown2,
			wko_isp_dropdown3,		
			wko_isp_uom,
			wko_isp_min_thr,
			wko_isp_max_thr,
			wko_isp_ovr_thr,
			column1,
			file_name,
			file_link,
			audit_user,
			audit_date,
			mbl_audit_user,
			mbl_audit_date
			
			
			
			
			FROM wko_isp A (NOLOCK)
			WHERE site_cd = '".$site_cd."' 
			AND wko_isp_asset_no = '".$wko_isp_asset_no."'
			AND wko_isp_job_cd = '".$wko_isp_job_cd."'
			AND mst_rowid = '".$mst_RowID." ' order by wko_isp_job_cd, wko_isp_sec_no,wko_isp_stp_no" ;
			
$wko_isp_stmt = sqlsrv_query( $conn, $sql);

if( !$wko_isp_stmt ) {
     $error_messages = [];
	 if (($errors = sqlsrv_errors()) != null) {
        foreach ($errors as $error) {
            // Collect all error messages
            $error_messages[] = $error['message'];
        }
     }
     sqlsrv_rollback($conn);
     sqlsrv_close($conn);
     // Send JSON response with SQL error message
     returnError("Error insert table (wko_isp)"  .implode("; ",$error_messages));
}

$json = array();

do {
     while ($row = sqlsrv_fetch_array($wko_isp_stmt, SQLSRV_FETCH_ASSOC)) {
			$site_cd = $row['site_cd'];
			$rowid = $row['rowid'];
			
			$wko_isp_asset_no = $row['wko_isp_asset_no'];
			$wko_isp_job_cd = $row['wko_isp_job_cd'];
			$wko_isp_job_desc = $row['wko_isp_job_desc'];
			$wko_isp_sec_no = $row['wko_isp_sec_no'];
			$wko_isp_sec_desc = $row['wko_isp_sec_desc'];
			$wko_isp_stp_no = $row['wko_isp_stp_no'];
			$wko_isp_stp_desc = $row['wko_isp_stp_desc'];
			$wko_isp_stp_datatype = $row['wko_isp_stp_datatype'];
			$wko_isp_stp_rowid = $row['wko_isp_stp_rowid'];
			
			$wko_isp_varchar1 = $row['wko_isp_varchar1'];
			$wko_isp_varchar2 = $row['wko_isp_varchar2'];
			$wko_isp_varchar3 = $row['wko_isp_varchar3'];
			$wko_isp_numeric1 = $row['wko_isp_numeric1'];
			$wko_isp_numeric2 = $row['wko_isp_numeric2'];
			$wko_isp_numeric3 = $row['wko_isp_numeric3'];
			$wko_isp_datetime1 = $row['wko_isp_datetime1'];
			$wko_isp_datetime2 = $row['wko_isp_datetime2'];
			$wko_isp_datetime3 = $row['wko_isp_datetime3'];
			$wko_isp_checkbox1 = $row['wko_isp_checkbox1'];
			$wko_isp_checkbox2 = $row['wko_isp_checkbox2'];
			$wko_isp_checkbox3 = $row['wko_isp_checkbox3'];
			$wko_isp_dropdown1 = $row['wko_isp_dropdown1'];
			$wko_isp_dropdown2 = $row['wko_isp_dropdown2'];
			$wko_isp_dropdown3 = $row['wko_isp_dropdown3'];
			
			
			
			$wko_isp_uom = $row['wko_isp_uom'];
			$wko_isp_min_thr = $row['wko_isp_min_thr'];
			$wko_isp_max_thr = $row['wko_isp_max_thr'];
			$wko_isp_ovr_thr = $row['wko_isp_ovr_thr'];
			
			$column1 = $row['column1'];
			
			$file_name = $row['file_name'];
			$file_link = $row['file_link'];
			
			
			$audit_user = $row['audit_user'];
			$audit_date = $row['audit_date'];
			$mbl_audit_user = $row['mbl_audit_user'];
			$mbl_audit_date = $row['mbl_audit_date'];
			
			$sql = "SELECT stp_zom_data FROM stp_zom(NOLOCK) WHERE site_cd = '".$site_cd."' AND mst_rowid = '".$wko_isp_stp_rowid." ' order by stp_zom.column1, stp_zom.stp_zom_data asc";
			
			$stp_zom = sqlsrv_query( $conn, $sql);

			if( !$stp_zom ) {
				 $error_message = "Error selecting table (stp_zom List)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}

			$json_list = array();

			do {
				 while ($row = sqlsrv_fetch_array($stp_zom, SQLSRV_FETCH_ASSOC)) {
					//$json_list[] = $row;
					
					$stp_zom_data = $row['stp_zom_data'];
					$json_zoom['label'] = $stp_zom_data;
					$json_zoom['value'] = $stp_zom_data;
					array_push($json_list, $json_zoom);
				 }
			} while ( sqlsrv_next_result($stp_zom) );
			
			
			$sql = "SELECT Img = count(*) FROM wko_isp_ref (NOLOCK) 
			WHERE site_cd = '".$site_cd."' 
			AND mst_RowID = '".$rowid." '
			And DataLength(attachment) > 0 
			And	DataLength(attachment) is not null
			And mst_RowID is not null
			And COALESCE(column2,'') <> 'SIGN' 
			And COALESCE(column2,'') <> 'RESPONSE_SIGN' 
			And COALESCE(column2,'') <> 'Checklist'
			And file_name not like '%xlsx%' 
			And file_name not like '%xls%'";
			
			$stp_ref = sqlsrv_query( $conn, $sql);

			if( !$stp_ref ) {
				 $error_message = "Error selecting table (wko_isp_ref List)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}

			$Img_count;

			do {
				 while ($row = sqlsrv_fetch_array($stp_ref, SQLSRV_FETCH_ASSOC)) {
					//$json_list[] = $row;
					
					$Img_count = $row['Img'];
					
				 }
			} while ( sqlsrv_next_result($stp_ref) );
			
			
			
			$json_img_link['site_cd'] = $site_cd;
			$json_img_link['rowid'] = $rowid;
			$json_img_link['wko_isp_asset_no'] = $wko_isp_asset_no;
			$json_img_link['wko_isp_job_cd'] = $wko_isp_job_cd;
			$json_img_link['wko_isp_job_desc'] = $wko_isp_job_desc;
			$json_img_link['wko_isp_sec_no'] = $wko_isp_sec_no;
			$json_img_link['wko_isp_sec_desc'] = $wko_isp_sec_desc;
			$json_img_link['wko_isp_stp_no'] = $wko_isp_stp_no;
			$json_img_link['wko_isp_stp_desc'] = $wko_isp_stp_desc;
			$json_img_link['wko_isp_stp_datatype'] = $wko_isp_stp_datatype;
			$json_img_link['wko_isp_stp_rowid'] = $wko_isp_stp_rowid;
			
			$json_img_link['wko_isp_varchar1'] = $wko_isp_varchar1;
			$json_img_link['wko_isp_varchar2'] = $wko_isp_varchar2;
			$json_img_link['wko_isp_varchar3'] = $wko_isp_varchar3;
			$json_img_link['wko_isp_numeric1'] = $wko_isp_numeric1;
			$json_img_link['wko_isp_numeric2'] = $wko_isp_numeric2;
			$json_img_link['wko_isp_numeric3'] = $wko_isp_numeric3;
			$json_img_link['wko_isp_datetime1'] = $wko_isp_datetime1;
			$json_img_link['wko_isp_datetime2'] = $wko_isp_datetime2;
			$json_img_link['wko_isp_datetime3'] = $wko_isp_datetime3;
			$json_img_link['wko_isp_checkbox1'] = $wko_isp_checkbox1;
			$json_img_link['wko_isp_checkbox2'] = $wko_isp_checkbox2;
			$json_img_link['wko_isp_checkbox3'] = $wko_isp_checkbox3;
			$json_img_link['wko_isp_dropdown1'] = $wko_isp_dropdown1;
			$json_img_link['wko_isp_dropdown2'] = $wko_isp_dropdown2;
			$json_img_link['wko_isp_dropdown3'] = $wko_isp_dropdown3;
			
			$json_img_link['wko_isp_uom'] = $wko_isp_uom;
			$json_img_link['wko_isp_min_thr'] = $wko_isp_min_thr;
			$json_img_link['wko_isp_max_thr'] = $wko_isp_max_thr;
			$json_img_link['wko_isp_ovr_thr'] = $wko_isp_ovr_thr;
			$json_img_link['column1'] = $column1;
			$json_img_link['file_name'] = $file_name;
			$json_img_link['file_link'] = $file_link;
			$json_img_link['audit_user'] = $audit_user;
			$json_img_link['audit_date'] = $audit_date;
			$json_img_link['mbl_audit_user'] = $mbl_audit_user;
			$json_img_link['mbl_audit_date'] = $mbl_audit_date;
			$json_img_link['Zoom_list'] = $json_list;
			$json_img_link['Img_count'] = $Img_count;
			
			array_push($json_return, $json_img_link);
     }
} while ( sqlsrv_next_result($wko_isp_stmt) );

if ($valid) {
	sqlsrv_free_stmt( $wko_isp_stmt);
	sqlsrv_close( $conn);	
	
	returnData($json_return);	
}


function generateUniqueName($file_name,$image_count) {
	
    // Get current date in Ymd format (YearMonthDay);
	//$date = date("YmdHis");	
	
	//$random_id = rand(100000, 999999);
	
	// Concatenate date and image count with file extension
    $unique_name = "checklist_image_" . $image_count . ".jpeg"; 
    return $unique_name;
}



function returnData($json){
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Select Successfully',
	'data' => $json);
	
	echo json_encode($returnData);
}

function returnError($error_message){
	$json = array();
	
	$returnData = array(
	'status' => 'ERROR',
	'message' => $error_message,);
	
	echo json_encode($returnData);		
	exit();
}


?>