<?php
require_once ('config.php');
require_once ('f_insert_email_log.php');
require_once ('send_notification_v1.php');
require_once ('f_insert_notification_log.php');
require_once ('find_online_tablet.php');
require_once ('f_logging.php');

$error_message;
$valid = true;
$insert_email = false;
$insert_ntf = false;
$now = date("Ymdhisa");
$file_name = __DIR__ . "\mobile_log\_update_action_workorder.log";

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}


$json = file_get_contents('php://input');
$result = json_decode($json);
	
$site_cd = $_GET['site_cd'];
$sync_step = $_GET['sync_step'];
$sync_time = $_GET['sync_time'];
$sync_status = $_GET['sync_status'];
$sync_url = $_GET['sync_url'];
$LOGINID = $_GET['LOGINID'];
$dvc_id = $_GET['dvc_id'];

f_logging($json,"off_update_action_workorder",$sync_url,$LOGINID,$sync_step,$sync_time,$site_cd);

foreach ($result->Header as $Header)
{
	
	$EmpID = $Header->EmpID;
	$ROW_ID = $Header->RowID;

	$wko_mst_status = $Header->wko_mst_status;
	$wko_mst_status_after = $Header->wko_mst_status_after;

	$wko_det_cause_code = $Header->wko_det_cause_code;
	$wko_det_act_code = $Header->wko_det_act_code;
	$wko_det_work_class = $Header->wko_det_work_class;
	$wko_det_corr_action = $Header->wko_det_corr_action;
	$wko_det_note1 = $Header->wko_det_note1;
	$wko_mst_wo_no= $Header->wko_mst_wo_no;
	$Is_checked = $Header->Is_checked;

	$Assestno = $Header->Assestno;
	$Assest_description = $Header->Assest_description;
	$Work_Area= $Header->Work_Area;
	$Assest_location = $Header->Assest_location;
	$Assest_level = $Header->Assest_level;
	$Originationdate= $Header->Originationdate;
	$Duedate = $Header->Duedate;
	$Work_Description = $Header->Work_Description;
	$Requested_by= $Header->Requested_by;
	$Contact_no = $Header->Contact_no;

	$current_date= $Header->current_date;
	
	
	$sql = "select 	wrk_sts_typ_cd,wrk_sts_status,wrk_sts_desc,wko_mst_org_date 
		from 	wko_mst(NOLOCK) 
		INNER 
		JOIN	wrk_sts (NOLOCK)
		ON		wko_mst.site_cd = wrk_sts.site_cd
		AND		wko_mst.wko_mst_status = wrk_sts.wrk_sts_status		
		where  wko_mst.site_cd = '".$site_cd."' 
		and wko_mst_wo_no = '".$wko_mst_wo_no."' ";			
	
	$stmt = sqlsrv_query( $conn, $sql);			
	if( !$stmt ) {
		$error_message = "Error select table (wrk_sts)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}
	do {
		while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
					
			$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];						
			$wrk_sts_status = $row['wrk_sts_status'];						
			$wrk_sts_desc 	= $row['wrk_sts_desc'];						
			$wko_mst_org_date = $row['wko_mst_org_date'];						
			 
		}
	} while ( sqlsrv_next_result($stmt));
	sqlsrv_free_stmt( $stmt);	
	
	
if($wrk_sts_typ_cd ==='OPEN' || ($wrk_sts_typ_cd === "COMPLETE" && $wrk_sts_status <> $wko_mst_status_after)){
	
	$sql = "UPDATE	wko_det 
			SET		wko_det_cause_code=?,
					wko_det_act_code=?,		
					wko_det_work_class=?,
					wko_det_corr_action =?,
					wko_det_note1 =?,
					wko_det.audit_user =?,						
					wko_det.audit_date=GETDATE(),
					wko_det.mbl_audit_user =?,						
					wko_det.mbl_audit_date=GETDATE()
			WHERE	wko_det.site_cd =?
			AND		wko_det.mst_RowID =?";

			$params_update_wko_det = array(
				trim($wko_det_cause_code),
				trim($wko_det_act_code),
				$wko_det_work_class,
				$wko_det_corr_action,
				$wko_det_note1,
				$LOGINID,
				$LOGINID,
				$site_cd,
				$ROW_ID
			);
	$stmt_wko_det = sqlsrv_query($conn, $sql, $params_update_wko_det);

	if ($stmt_wko_det === false)
	{
		if (($errors = sqlsrv_errors()) != null)
		{
			foreach ($errors as $error)
			{
				file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "json: " . $params_update_wko_det . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
			}
		}

		sqlsrv_rollback($conn);
		sqlsrv_close($conn);

		$error_message = "Error updating table (wko_det)";
		returnError($error_message);
	}
	sqlsrv_free_stmt($stmt_wko_det);

	if ($Is_checked == 'true')
	{
			
			
		if ($wko_mst_status != $wko_mst_status_after)
        {

            $sql = "UPDATE 	wko_mst  SET 
						wko_mst_status = ?, 
						audit_user = ?, 
						audit_date = GETDATE(),
						mbl_audit_user = ?, 
						mbl_audit_date = GETDATE()
						WHERE 	site_cd = ?
						AND 	wko_mst_wo_no = ?";

            $params_update_wko_mst = array(
                $wko_mst_status_after,
                $LOGINID,
                $LOGINID,
                $site_cd,
                $wko_mst_wo_no
            );
            $stmt_wko_mst = sqlsrv_query($conn, $sql, $params_update_wko_mst);

            if ($stmt_wko_mst === false)
            {
                if (($errors = sqlsrv_errors()) != null)
                {
                    foreach ($errors as $error)
                    {
                        file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
                    }
                }

                sqlsrv_rollback($conn);
                sqlsrv_close($conn);

                $error_message = "Error updating table (wko_mst) - ".$now;
                returnError($error_message);
            }

            sqlsrv_free_stmt($stmt_wko_mst);
			
			// Ensure $wko_mst_org_date is a DateTime object
			$start = ($wko_mst_org_date instanceof DateTime)     ? $wko_mst_org_date     : new DateTime($wko_mst_org_date);

			// Ensure $wko_det_cmpl_date is a DateTime object
			$end = ($wko_det_cmpl_date instanceof DateTime)     ? $wko_det_cmpl_date     : new DateTime($wko_det_cmpl_date);

			// Calculate difference in minutes
			$minutes = ($end->getTimestamp() - $start->getTimestamp()) / 60;

            $sql = "update wko_det 
								SET  
								wko_det_sc_date= ?,
								wko_det_cmpl_date= ?,
								wko_det_opn_cmp=?,
								mbl_audit_user = ?, 
								mbl_audit_date = GETDATE()								
								WHERE	wko_det.site_cd =?
								AND		wko_det.mst_RowID = ?";

            $params_update_wko_det = array(
                $current_date,
                $current_date,
				$minutes,
				$LOGINID,
                $site_cd,
                $ROW_ID
            );
            $stmt_wko_det1 = sqlsrv_query($conn, $sql, $params_update_wko_det);

            if ($stmt_wko_det1 === false)
            {
                if (($errors = sqlsrv_errors()) != null)
                {
                    foreach ($errors as $error)
                    {
                        file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
                    }
                }

                sqlsrv_rollback($conn);
                sqlsrv_close($conn);

                $error_message = "Error updating table (wko_det) - ".$now;
                returnError($error_message);
            }

            sqlsrv_free_stmt($stmt_wko_det1);

            $sql = "update wko_sts  
						SET wko_sts_end_date =GETDATE() , 
						wko_sts_duration =0 , 
						audit_user =?, 						
						audit_date =GETDATE(),
						mbl_audit_user =?, 						
						mbl_audit_date =GETDATE()						
						WHERE 
						wko_sts_end_date IS NULL 
						AND site_cd =? 
						AND wko_sts_wo_no =? 
						AND wko_sts_status =?";

            $params_update_wko_sts = array(
                $LOGINID,
                $LOGINID,
                $site_cd,
                $wko_mst_wo_no,
                $wko_mst_status
            );
            $stmt_wko_sts = sqlsrv_query($conn, $sql, $params_update_wko_sts);

            if ($stmt_wko_sts === false)
            {
                if (($errors = sqlsrv_errors()) != null)
                {
                    foreach ($errors as $error)
                    {
                        file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
                    }
                }

                sqlsrv_rollback($conn);
                sqlsrv_close($conn);

                $error_message = "Error updating table (wko_sts) - ".$now;
                returnError($error_message);
            }
            sqlsrv_free_stmt($stmt_wko_sts);

            $sql_insert_ast_mst = "INSERT wko_sts 
							(		site_cd ,					mst_RowID,						wko_sts_wo_no ,			wko_sts_status , 
									wko_sts_originator ,		wko_sts_start_date ,			wko_sts_end_date ,		wko_sts_duration , 
									audit_user , 				audit_date,						mbl_audit_user,			mbl_audit_date ) 
					VALUES	(		? ,							? ,								? ,						?,			
									? ,							getdate(),						NULL ,						NULL ,				 
									? ,							getdate(),						? ,							getdate())";

            $params_update_wko_sts = array(
                $site_cd,
                $ROW_ID,
                $wko_mst_wo_no,
                $wko_mst_status_after,
                $LOGINID,
                $LOGINID,
                $LOGINID
            );
            $stmt_wko_sts1 = sqlsrv_query($conn, $sql_insert_ast_mst, $params_update_wko_sts);

            if ($stmt_wko_sts1 === false)
            {
                if (($errors = sqlsrv_errors()) != null)
                {
                    foreach ($errors as $error)
                    {
                        file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_mst . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
                    }
                }

                sqlsrv_rollback($conn);
                sqlsrv_close($conn);

                $error_message = "Error inserting table (wko_sts) - ".$now;
                returnError($error_message);
            }

            sqlsrv_free_stmt($stmt_wko_sts1);

            $sql_email = " Select emp_mst_empl_id, emp_det_email_id 
							from emp_mst (NOLOCK), emp_det (NOLOCK)
							Where emp_mst.site_cd = emp_det.site_cd
							And emp_mst.RowID = emp_det.mst_RowID
							And emp_mst.emp_mst_empl_id = '" . $EmpID . "'";

            $stmt_email = sqlsrv_query($conn, $sql_email);

            if ($stmt_email === false)
            {
                if (($errors = sqlsrv_errors()) != null)
                {
                    foreach ($errors as $error)
                    {
                        file_put_contents($file_name, $now . "\r\n" . $sql_email . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
                    }
                }

                sqlsrv_rollback($conn);
                sqlsrv_close($conn);

                $error_message = "Error selecting table (emp_mst,emp_det) - ".$now;
                returnError($error_message);
            }

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

                    $emp_mst_empl_id = $row['emp_mst_empl_id'];
                    $emp_det_email_id = $row['emp_det_email_id'];

                }
            }
            while (sqlsrv_next_result($stmt_email));

            if (!Empty($emp_det_email_id))
            {

                //Insert Email
                $title = "Work Order Completed.";
                $body = "The work order  ( ".$wko_mst_wo_no." ) is Completed.";
                $module = "Work Order";
                $message_completed = "Asset No: " . $Assestno . " / " . $Assest_description . "\r\n" .
				 "Work Area: " . $Work_Area . "\r\n" .
				 "Location: " . $Assest_location . "\r\n" .
				 "Level: " . $Assest_level . "\r\n\r\n" .
				 "Work Order No: " . $wko_mst_wo_no . " / " . $Work_Description . "\r\n" .
				 "WO Date: " . $Originationdate . "\r\n" .
				 "WO Due Date: " . $Duedate . "\r\n\r\n" .
				 "Completed By: " . $EmpID . "\r\n" .
				 "Requested By: " . $Requested_by . "\r\n" .
				 "Contact No: " . $Contact_no;

                $insert_email = insert_email_log($site_cd, $EmpID, $emp_det_email_id, $title, $body, $message_completed, $LOGINID);

            }

            //Insert notification_log for Work Order Completed
            $title = "Work Order Completed";
            $body = "The work order  ('" . $wko_mst_wo_no . "') is Completed.";
            $module = "Work Order Completed";
            $message_completed = "Asset No: " . $Assestno . " / " . $Assest_description . "\r\n" .
			 "Work Area: " . $Work_Area . "\r\n" .
			 "Location: " . $Assest_location . "\r\n" .
			 "Level: " . $Assest_level . "\r\n\r\n" .
			 "Work Order No: " . $wko_mst_wo_no . " / " . $Work_Description . "\r\n" .
			 "WO Date: " . $Originationdate . "\r\n" .
			 "WO Due Date: " . $Duedate . "\r\n\r\n" .
			 "Completed By: " . $EmpID . "\r\n" .
			 "Requested By: " . $Requested_by . "\r\n" .
			 "Contact No: " . $Contact_no;

            $insert_ntf1 = insert_notification_log($site_cd, $EmpID, $Requested_by, $wko_mst_wo_no, $title, $body, $message_completed, $module, $dvc_id, $LOGINID);

            foreach ($insert_ntf1 as $x => $val)
            {

                $row = $x = $val["row"];
                $insert_ntf = $x = $val["insert_ntf"];

                //echo '<pre>'; print_r($row); echo '</pre>';
                //echo '<pre>'; print_r($insert_ntf); echo '</pre>';
                

                
            }

            //Get New Value Assignto Online Token
            $tkn_token_1 = get_online($Requested_by, $site_cd);

            //echo '<pre>'; print_r($tkn_token_1); echo '</pre>';
            //Notify token user
            if (sizeof($tkn_token_1) > 0)
            {

                foreach ($tkn_token_1 as $x => $val)
                {

                    $tokenlist = $x = $val["token"];
                    $RowID = $x = $val["RowID"];
                    $tkn_site = $x = $val["tkn_site"];

                    //echo '<pre>'; print_r($tkn_site); echo '</pre>';
                    //echo '<pre>'; print_r($RowID); echo '</pre>';
                    push_notification_android($tokenlist, 'The work order (' . $wko_mst_wo_no . ') is Completed', "Work Order Completed (" . $site_cd . ")", $site_cd, $tokenlist, $RowID, $row, $tkn_site, $LOGINID);

                }

            }

        }

	}
	
	if ($stmt_wko_det)
	{

		if ($Is_checked == 'true')
		{

			if ($wko_mst_status != $wko_mst_status_after)
			{

				if ($stmt_wko_mst && $stmt_wko_det1 && $stmt_wko_sts && $stmt_wko_sts1)
				{

					sqlsrv_commit($conn);
					sqlsrv_close($conn);
					returnData();
				}
				else
				{
					sqlsrv_rollback($conn);
					$error_message = "Transaction rolled back check Box true .<br />";
					returnError($error_message);
				}

			}

		}
		else
		{
			sqlsrv_commit($conn);
			sqlsrv_close($conn);
			returnData();
		}
		}
		else
		{
			sqlsrv_rollback($conn);
			$error_message = "Transaction rolled back check Box false.<br />";
			returnError($error_message);
		}
}else{
	returnData();
}
	
	
	

	
	
}





function returnData()
{
    $returnData = array(
        'status' => 'SUCCESS',
        'message' => 'Work order is completed successfully'
    );

    echo json_encode($returnData);
}

function returnError($error_message)
{

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

?>
