<?php
require_once('config.php');
require_once('f_error_log.php');

function send_email($site_cd, $eml_log_from, $eml_log_to_email, $title, $body, $audit_user, $doc_no, $module, $action, $trigger_from, $parm1, $parm2, $parm3){
	global $conn;
	
	$now = date("Ymdhisa");
	
	$from = "";
	$to = "";
	$cc = "";	
	
	//PR Approved, WO Completion, 
	$event = $action;
	
	//The mail had been successfully sent.
	//The mail had been successfully sent to the queue.
	$email_status_msg = 'The mail had been successfully sent to the queue.';
	
	//P - Pending, S - Sent, E - Error
	$email_status = 'P';
	
	//Core, Web Work, Mobile
	$trigger_from = 'Mobile';
	
	$insert_email = true;
	
	//Get From Email by Empl ID
	$sql_get_emp_email = "Select emp_det_email_id
			From 	emp_mst (NOLOCK), emp_det (NOLOCK)
			Where 	emp_mst.site_cd ='" . $site_cd . "'
			AND		emp_mst.site_cd = emp_det.site_cd
			AND		emp_mst.rowid = emp_det.mst_rowid
			AND		emp_mst_empl_id = '".$eml_log_from."'";
			
	$stmt_get_emp_email = sqlsrv_query($conn, $sql_get_emp_email);

	if ($stmt_get_emp_email === false)
	{
		$error_message = "Error selecting table (emp_mst, emp_det) - ".$now;
		if (($errors = sqlsrv_errors()) != null)
		{
			foreach ($errors as $error)
			{
				//file_put_contents($file_name, $now . "\r\n" . $sql_wo_detail . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				insert_error_log($site_cd, $error['message'], $audit_user, $module, $action);
			}
		}
		
	}

	do
	{
		while ($row = sqlsrv_fetch_array($stmt_get_emp_email, SQLSRV_FETCH_ASSOC))
		{

			$from = $row['emp_det_email_id'];
		}
	}
	while (sqlsrv_next_result($stmt_get_emp_email));
	sqlsrv_free_stmt($stmt_get_emp_email);
	
	//Get To Email by Empl ID
	$placeholders = implode(', ', array_fill(0, count($eml_log_to_email), '?'));
	
	$sql_get_emp_to_email = "Select distinct emp_det_email_id
			From 	emp_mst (NOLOCK), emp_det (NOLOCK)
			Where 	emp_mst.site_cd ='" . $site_cd . "'
			AND		emp_mst.site_cd = emp_det.site_cd
			AND		emp_mst.rowid = emp_det.mst_rowid
			AND		emp_mst_empl_id IN ($placeholders)";
			
			
	$stmt_get_emp_to_email = sqlsrv_query($conn, $sql_get_emp_to_email, $eml_log_to_email);

	if ($stmt_get_emp_to_email === false)
	{
		$error_message = "Error selecting table (emp_mst, emp_det) - ".$now;
		if (($errors = sqlsrv_errors()) != null)
		{
			foreach ($errors as $error)
			{
				//file_put_contents($file_name, $now . "\r\n" . $sql_wo_detail . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				insert_error_log($site_cd, $error['message'], $audit_user, $module, $action);
			}
		}
		
	}

	do
	{
		while ($row = sqlsrv_fetch_array($stmt_get_emp_to_email, SQLSRV_FETCH_ASSOC))
		{
			$to = $to.$row['emp_det_email_id'].";";
		}
	}
	while (sqlsrv_next_result($stmt_get_emp_to_email));
	sqlsrv_free_stmt($stmt_get_emp_to_email);
	
	
	$subject = $title;
	$content = $body;
	
	//For Customization of Subject, Contenct, To, CC and etc
	if($module == "Purchase Request"){
		If ($action == "PR Approved"){
			
		}elseif($action == "PR Approval Next"){
			
		}elseif($action == "PR Rejected"){
			
		}elseif($action == "PR Disapproved"){
			
		}
		
	}elseif($module == "Work Order"){
		If ($action == "WO Completion"){
			
			$sql_wo_detail = "Select		wko_mst_wo_no,
								wko_mst_assetno,
								ast_mst_asset_shortdesc,
								wko_mst_work_area,
								wko_mst_asset_location,
								wko_mst_asset_level,
								wko_mst_descs,
								wko_mst_org_date,
								wko_mst_due_date,
								requested_by = (Select	emp_mst_name 
												From 	emp_mst (NOLOCK)
												Where 	emp_mst.site_cd = wko_mst.site_cd 
												and 	emp_mst_empl_id = wko_mst_originator),
								completed_by = (Select TOP 1 emp_mst_name 
												From emp_mst (NOLOCK), 
													 wko_sts (NOLOCK),
													 wrk_sts (NOLOCK)
												Where emp_mst.site_cd = wko_sts.site_cd 
												and emp_mst.emp_mst_empl_id = wko_sts_originator 
												and wko_sts_wo_no = wko_mst_wo_no
												and	wko_sts.site_cd = wko_mst.site_cd
												and wko_sts.site_cd = wrk_sts.site_cd 
												and wrk_sts_status = wko_sts_status
												and wrk_sts_typ_cd = 'COMPLETE'
												Order by wko_sts.rowid desc),
								wko_mst_originator,
								wko_mst_phone
					From		wko_mst (NOLOCK)
					LEFT JOIN	ast_mst (NOLOCK) ON wko_mst.site_cd = ast_mst.site_cd And wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
					Where		wko_mst.site_cd = ?
					And			wko_mst_wo_no = ?";
					
			$params_wo_detail = array($site_cd,	$doc_no);

			$stmt_wo_detail = sqlsrv_query($conn, $sql_wo_detail, $params_wo_detail);

			if ($stmt_wo_detail === false)
			{
				$error_message = "Error selecting table (wko_mst, ast_mst) - ".$now;
				if (($errors = sqlsrv_errors()) != null)
				{
					foreach ($errors as $error)
					{
						//file_put_contents($file_name, $now . "\r\n" . $sql_wo_detail . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
						insert_error_log($site_cd, $error['message'], $audit_user, $module, $action);
					}
				}
			}
			
			if (sqlsrv_has_rows($stmt_wo_detail)) {
				do
				{
					while ($row = sqlsrv_fetch_array($stmt_wo_detail, SQLSRV_FETCH_ASSOC))
					{

						$wko_mst_wo_no			 	= $row['wko_mst_wo_no'];
						$wko_mst_assetno 			= $row['wko_mst_assetno'];
						$ast_mst_asset_shortdesc 	= $row['ast_mst_asset_shortdesc'];
						$wko_mst_work_area 			= $row['wko_mst_work_area'];
						$wko_mst_asset_location 	= $row['wko_mst_asset_location'];
						$wko_mst_asset_level 		= $row['wko_mst_asset_level'];
						$wko_mst_descs 				= $row['wko_mst_descs'];
						$wko_mst_org_date 			= $row['wko_mst_org_date'];
						$wko_mst_due_date 			= $row['wko_mst_due_date'];
						$requested_by 				= $row['requested_by'];
						$completed_by 				= $row['completed_by'];
						$wko_mst_originator 		= $row['wko_mst_originator'];
						$wko_mst_phone 				= $row['wko_mst_phone'];

					}
				}
				while (sqlsrv_next_result($stmt_wo_detail));
				
				
				$subject = "The work order  ( ".$wko_mst_wo_no." ) is Completed.";
				$content = "Asset No: ".$wko_mst_assetno." / ".$ast_mst_asset_shortdesc." 
				\r\nWork Area: ".$wko_mst_work_area."
				\r\nLocation: ".$wko_mst_asset_location."
				\r\nLevel: ".$wko_mst_asset_level."
				\r\n\r\nRequested By: ".$requested_by."
				\r\nWork Order No: ".$wko_mst_wo_no." / ".$wko_mst_descs."
				\r\nWO Date: ".$wko_mst_org_date."
				\r\nWO Due Date: ".$wko_mst_due_date."
				\r\n\r\nCompleted By: ".$completed_by."
				\r\nRequested By: ".$requested_by."
				\r\nContact No: ".$wko_mst_phone."";
			
			}else{
				$subject = $title;
				$content = $body;
			}
		}elseif($action == "WO Assignment"){
			
		}
	}
	elseif($module == "Work Request"){
		If ($action == "New WR"){
			
		}elseif($action == "WR Approved"){
			
		}elseif($action == "WR Disapproved"){
			
		}
	}

	
	$sql_insert_eml_log = "	INSERT Into eml_log (	site_cd,			eml_log_from,			eml_log_to_email,		eml_log_cc_email,		eml_log_subject,		eml_log_content,			
												eml_log_date, 			eml_log_event,			eml_log_msg,			eml_log_status,			audit_user,				audit_date,				
												column1)					

							 VALUES 
											(	?,						?,						?,						?,						?,						?,						
												getdate(),				?,						?,						?,						?,						getdate(),				
												?)" ;

	$params_insert_eml_log = array(				$site_cd,				$from,					$to,					$cc,					$subject,				$content,
																		$event,					$email_status_msg,		$email_status,			$audit_user,	
												$trigger_from);
									
									
	//echo 	$params_insert_eml_log;							
	$stmt_insert_eml_log = sqlsrv_query( $conn, $sql_insert_eml_log, $params_insert_eml_log);	
	
	if( !$stmt_insert_eml_log ) {
		$error_message = "Error (INSERT Table eml_log)";
		if (($errors = sqlsrv_errors()) != null){
			foreach ($errors as $error)	{
				//echo $error['message'];
				//file_put_contents($file_name, $now . "\r\n" . $sql_wo_detail . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				insert_error_log($site_cd, $error['message'], $audit_user, $module, $action);
			}
		}
	
		$insert_email = false;
		
	}
	sqlsrv_free_stmt( $stmt_insert_eml_log);
	
	
	//Execute Send Email Stored Procedure
	$sql_exe_sp = "EXEC sp_send_email";
	
	$stmt_exe_sp = sqlsrv_query( $conn, $sql_exe_sp);
	
	if( !$stmt_exe_sp ) {
		$error_message = "Error (Execure sp_send_email)";
		if (($errors = sqlsrv_errors()) != null){
			foreach ($errors as $error)	{
				//echo $error['message'];
				//file_put_contents($file_name, $now . "\r\n" . $sql_wo_detail . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				//insert_error_log($site_cd, $error['message'], $audit_user, $module, $action);
			}
		}
	}
		
	return $insert_email;
}


?>