<?php
//f_get_update_budget('UZMA', '20200907', 'CC01', 'STORE', 'CHARGE', 1000, $conn);

function f_clear_shortage($as_site, $as_res, $as_stock, $al_rowid, $as_loc, $ad_qty, $conn){
	
	if ($as_res == 0 || $al_rowid == 0 || $ad_qty == 0) {
        return;
    }
	
	$sql = "SELECT COUNT(*) AS li_count
            FROM itm_rsv (NOLOCK)
            WHERE itm_rsv_short_qty > 0 
                AND itm_rsv.site_cd = ?
                AND itm_rsv.itm_rsv_stock_no = ?
                AND itm_rsv.itm_rsv_itm_loc = ?";

    $stmt = sqlsrv_query($conn, $sql, array($as_site, $as_stock, $as_loc));

    if (!$stmt) {
		sqlsrv_rollback($conn);
		sqlsrv_close($conn);
        $error_message = "Error selecting table (itm_rsv)";
        // Log the error or handle it as appropriate
        die(print_r(sqlsrv_errors(), true));
    }

    do {
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $li_count = $row['li_count'];
        }
    } while (sqlsrv_next_result($stmt));
	
	$ldc_p_rcv_qty = $ad_qty;
	
	if ($li_count > 0){
		$sql = "SELECT 	itm_rsv.site_cd,
						itm_rsv.itm_rsv_stock_no,
						itm_rsv.itm_rsv_itm_loc, 
						itm_rsv.itm_rsv_wo_no,
						itm_rsv.itm_rsv_assetno,
						itm_rsv.itm_rsv_mtr_no,
						itm_rsv.itm_rsv_mtr_lineno,
						itm_rsv.itm_rsv_rsv_date,
						itm_rsv.itm_rsv_due_date,
						itm_rsv.itm_rsv_rsv_qty,
						itm_rsv.itm_rsv_short_qty,
						itm_rsv.RowID
						FROM 	itm_rsv (NOLOCK)
			WHERE		itm_rsv_short_qty > 0 
			AND			itm_rsv.site_cd = '".$as_site."'
			AND			itm_rsv.itm_rsv_stock_no = '".$as_stock."'
			AND			itm_rsv.itm_rsv_itm_loc = '".$as_loc."'";

		$stmt = sqlsrv_query( $conn, $sql);				
						
		if( !$stmt ) {
			sqlsrv_rollback($conn);
			sqlsrv_close($conn);
			 $error_message = "Error select table (itm_rsv)";
			 $valid = false;
			 die( print_r( sqlsrv_errors(), true));
		}

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				$site_cd 			= $row['site_cd'];
				$itm_rsv_stock_no 	= $row['itm_rsv_stock_no'];
				$itm_rsv_itm_loc 	= $row['itm_rsv_itm_loc'];
				$itm_rsv_wo_no 		= $row['itm_rsv_wo_no'];
				$itm_rsv_assetno 	= $row['itm_rsv_assetno'];
				$itm_rsv_mtr_no 	= $row['itm_rsv_mtr_no'];
				$itm_rsv_mtr_lineno = $row['itm_rsv_mtr_lineno'];
				$itm_rsv_rsv_date	= $row['itm_rsv_rsv_date'];
				$itm_rsv_due_date	= $row['itm_rsv_due_date'];
				$itm_rsv_rsv_qty	= $row['itm_rsv_rsv_qty'];
				$ldc_sht_qty		= $row['itm_rsv_short_qty'];
				$ll_rsv_rowid		= $row['RowID'];
			 }
		} while ( sqlsrv_next_result($stmt) );	
		
		$ldc_balance_qty = $ldc_sht_qty;
		
		if($ldc_p_rcv_qty == $ldc_balance_qty){
			//Update itm_rsv
			$sql_update_itm_rsv = "UPDATE itm_rsv
                                SET itm_rsv_rsv_qty = itm_rsv_rsv_qty + ?,
                                    itm_rsv_short_qty = itm_rsv_short_qty - ?
                                WHERE RowID = ?";

			$stmt_update_itm_rsv = sqlsrv_query($conn, $sql_update_itm_rsv, array($ldc_p_rcv_qty, $ldc_p_rcv_qty, $ll_rsv_rowid));

			if (!$stmt_update_itm_rsv) {
				sqlsrv_rollback($conn);
				sqlsrv_close($conn);
				$error_message = "Error updating table (itm_rsv)";
				// Log the error or handle it as appropriate
				die(print_r(sqlsrv_errors(), true));
			}

			sqlsrv_free_stmt($stmt_update_itm_rsv);
			
			//Update itm_det
			$sql_update_itm_det = "UPDATE 	itm_det
									SET 		itm_det_ttl_hard_resrv = itm_det_ttl_hard_resrv + ".$ldc_p_rcv_qty.",
												itm_det_ttl_short = itm_det_ttl_short - ".$ldc_p_rcv_qty."
									WHERE	( itm_det.mst_RowID = ".$al_RowID.") ";

			$stmt_update_itm_det = sqlsrv_query( $conn, $sql_update_itm_det);				
							
			if( !$stmt_update_itm_det ) {
				sqlsrv_rollback($conn);
				sqlsrv_close($conn);
				 $error_message = "Error update table (itm_det)";
				 //returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}
			
			sqlsrv_free_stmt($stmt_update_itm_det);
			
			//Update itm_loc
			$sql_update_itm_loc = "UPDATE 	itm_loc
									SET 		itm_loc_hard_resrv = COALESCE(itm_loc_hard_resrv, 0) + ".$ldc_p_rcv_qty.",
												itm_loc_short_qty = COALESCE(itm_loc_short_qty, 0) - ".$ldc_p_rcv_qty."
									WHERE	( itm_loc.mst_RowID = ".$al_RowID." ) 
									AND		( itm_loc_stk_loc = ".$as_loc." )";

			$stmt_update_itm_loc = sqlsrv_query( $conn, $sql_update_itm_loc);				
							
			if( !$stmt_update_itm_loc ) {
				sqlsrv_rollback($conn);
				sqlsrv_close($conn);

				 $error_message = "Error update table (itm_loc)";
				 //returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}
			
			sqlsrv_free_stmt($stmt_update_itm_loc);
			
		}else{
			if($ldc_p_rcv_qty > $ldc_balance_qty){
				
				$ldc_p_rcv_qty = $ldc_p_rcv_qty - $ldc_balance_qty;
				
				//Update itm_rsv
				$sql_update_itm_rsv = "UPDATE itm_rsv
									SET itm_rsv_rsv_qty = itm_rsv_rsv_qty + ?,
										itm_rsv_short_qty = itm_rsv_short_qty - ?
									WHERE RowID = ?";

				$stmt_update_itm_rsv = sqlsrv_query($conn, $sql_update_itm_rsv, array($ldc_balance_qty, $ldc_balance_qty, $ll_rsv_rowid));

				if (!$stmt_update_itm_rsv) {
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);

					$error_message = "Error updating table (itm_rsv)";
					// Log the error or handle it as appropriate
					die(print_r(sqlsrv_errors(), true));
				}

				sqlsrv_free_stmt($stmt_update_itm_rsv);
				
				//Update itm_det
				$sql_update_itm_det = "UPDATE 	itm_det
										SET 		itm_det_ttl_hard_resrv = itm_det_ttl_hard_resrv + ".$ldc_balance_qty.",
													itm_det_ttl_short = itm_det_ttl_short - ".$ldc_balance_qty."
										WHERE	( itm_det.mst_RowID = ".$al_RowID.") ";

				$stmt_update_itm_det = sqlsrv_query( $conn, $sql_update_itm_det);				
								
				if( !$stmt_update_itm_det ) {
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);
					 $error_message = "Error update table (itm_det)";
					 //returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
				}
				
				sqlsrv_free_stmt($stmt_update_itm_det);
				
				//Update itm_loc
				$sql_update_itm_loc = "UPDATE 	itm_loc
										SET 		itm_loc_hard_resrv = COALESCE(itm_loc_hard_resrv, 0) + ".$ldc_balance_qty.",
													itm_loc_short_qty = COALESCE(itm_loc_short_qty, 0) - ".$ldc_balance_qty."
										WHERE	( itm_loc.mst_RowID = ".$al_RowID." ) 
										AND		( itm_loc_stk_loc = ".$as_loc." )";

				$stmt_update_itm_loc = sqlsrv_query( $conn, $sql_update_itm_loc);				
								
				if( !$stmt_update_itm_loc ) {
					 $error_message = "Error update table (itm_loc)";
					 //returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
				}
				
				sqlsrv_free_stmt($stmt_update_itm_loc);
				
			}
		}
	}
}



?>