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

$error_message;
$valid = true;

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

$Folder		= $_REQUEST['Folder'];
$dvc_id		= $_REQUEST['dvc_id'];
$MobileURL	= $_REQUEST['MobileURL'];
$EmpID		= $_REQUEST['EmpID'];
$page 		= $_REQUEST['page'];
$pageSize 	= 10;
$type		= $_REQUEST['type'];
$LOGINID 	= $_REQUEST['LOGINID'];


$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\\";	


switch ($type) {
	
	case 'All':
	
	$sql = "SELECT
			itm_mst.site_cd,
			itm_mst.itm_mst_stockno,
			itm_mst.itm_mst_costcenter,
			itm_mst.itm_mst_account,
			itm_mst.itm_mst_mstr_locn,			
			itm_mst.itm_mst_desc,	
			itm_mst.itm_mst_issue_uom,			
			itm_mst.itm_mst_issue_price,
			itm_mst.itm_mst_ttl_oh,
			itm_mst_ext_desc = COALESCE(cast(itm_mst.itm_mst_ext_desc as varchar (255)),''),	
			itm_det.itm_det_part_deac_status,
			itm_det.itm_det_order_pt,		
			cur_mst.cur_mst_label,			
			itm_det_ttl_oh= COALESCE(itm_det_ttl_oh,0),
			
			category = CASE
			WHEN		((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt )
			AND         ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'L' ) THEN 'BELOW'

			WHEN		((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt )
			AND         ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'T' ) THEN 'BELOW'

			ELSE 'NORMAL' END,			
			
			itm_mst.RowID,
			attachment					= COALESCE(itm_ref.attachment,''),
			file_name					= COALESCE(itm_ref.file_name,''),
			itm_ref.RowID AS file_RowID				
			
			FROM		itm_mst (NOLOCK)

			INNER JOIN	itm_det (NOLOCK)            
			ON          itm_mst.site_cd = itm_det.site_cd
			AND         itm_mst.RowID = itm_det.mst_RowID

			INNER JOIN  itm_sts (NOLOCK)
			ON          itm_det.site_cd = itm_sts.site_cd 
			AND         itm_det.itm_det_part_deac_status = itm_sts.itm_sts_status

			LEFT OUTER 
			JOIN		itm_loc
			ON			itm_mst.site_cd = itm_loc.site_cd
			AND			itm_mst.RowID = itm_loc.mst_RowID
			AND			itm_mst.itm_mst_mstr_locn = itm_loc.itm_loc_stk_loc
			
			LEFT OUTER 
			JOIN		cur_mst
			ON			itm_mst.site_cd = cur_mst.site_cd
			and			cur_mst.cur_mst_base_cur =  1

			LEFT 
			OUTER 
			JOIN	( Select *, ROW_NUMBER() OVER (PARTITION BY mst_rowid ORDER BY itm_ref.type DESC) AS rn
						From itm_ref (NOLOCK)
						WHERE		itm_ref.type  IN ('P','A')
						And 		DataLength(itm_ref.attachment) > 0 
						And			DataLength(itm_ref.attachment) is not null
						and			itm_ref.file_name not like '%xlsx%' 
						and			itm_ref.file_name not like '%xls%'
					) itm_ref 
			ON itm_mst.site_cd = itm_ref.site_cd AND itm_mst.rowid = itm_ref.mst_rowid AND rn = 1

			WHERE		itm_mst.site_cd = '".$_REQUEST['site_cd']."'
			AND			itm_sts_typ_cd = '".$_REQUEST['status']."'
			

			ORDER BY	itm_mst_stockno OFFSET ".($page  -1)*$pageSize." ROWS FETCH NEXT ".$pageSize." ROWS ONLY ";
						
			$stmt = sqlsrv_query( $conn, $sql);

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

			$json = array();

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				
				$site_cd = $row['site_cd'];
				$RowID = $row['RowID'];
				$itm_mst_stockno = $row['itm_mst_stockno'];
				$itm_mst_costcenter = $row['itm_mst_costcenter'];
				$itm_mst_account = $row['itm_mst_account'];
				$itm_mst_mstr_locn = $row['itm_mst_mstr_locn'];
				$itm_mst_desc = $row['itm_mst_desc'];
				$itm_mst_issue_uom = $row['itm_mst_issue_uom'];
				$itm_mst_issue_price = $row['itm_mst_issue_price'];
				$itm_mst_ttl_oh = $row['itm_mst_ttl_oh'];
				$itm_mst_ext_desc = $row['itm_mst_ext_desc'];
				$itm_det_part_deac_status = $row['itm_det_part_deac_status'];
				$itm_det_order_pt = $row['itm_det_order_pt'];
				$itm_det_ttl_oh = $row['itm_det_ttl_oh'];
				$category = $row['category'];
				$cur_mst_label = $row['cur_mst_label'];
				
				$file_name = $row['file_name'];

				$attachment;
				$fileName;
				
				if(empty($file_name)){
					
					$attachment = Null;
					$fileName =Null;
					
				}else{
						  
					if (strpos($file_name, '.pdf') !== false) {
							
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, '.mp4') !== false) {
						
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, 'docx') == true){	
								
						$attachment = Null;
						$fileName =Null;
					
					}else{
									
						$Photo = $row['attachment'];
						$file_Name = $row['file_name'];
						$file_RowID = $row['file_RowID'];
						
						$file_Name = generateUniqueName($file_Name,$file_RowID);

						$exportFullPathFileName = $exportdir . $file_Name;
						$exportFullPathTNFileName = $exportdirtn . $file_Name;

						$publicFullPathFileName = $MobileURLFullSize . $file_Name;
						$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;
						
						if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
							resizeImageImagick($Photo, $exportFullPathTNFileName);
						} 

						$attachment = $publicFullPathTNFileName;
						$fileName =$file_Name;
						
					}
				}
				
				
				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['RowID'] = $RowID;
				$json_img_link['itm_mst_stockno'] = $itm_mst_stockno;
				$json_img_link['itm_mst_costcenter'] = $itm_mst_costcenter;
				$json_img_link['itm_mst_account'] = $itm_mst_account;
				$json_img_link['itm_mst_mstr_locn'] = $itm_mst_mstr_locn;
				$json_img_link['itm_mst_desc'] = $itm_mst_desc;
				$json_img_link['itm_mst_issue_uom'] = $itm_mst_issue_uom;
				$json_img_link['itm_mst_issue_price'] = $itm_mst_issue_price;
				$json_img_link['itm_mst_ttl_oh'] = $itm_mst_ttl_oh;
				$json_img_link['itm_mst_ext_desc'] = $itm_mst_ext_desc;
				$json_img_link['itm_det_part_deac_status'] = $itm_det_part_deac_status;
				$json_img_link['itm_det_order_pt'] = $itm_det_order_pt;
				$json_img_link['itm_det_ttl_oh'] = $itm_det_ttl_oh;
				$json_img_link['cur_mst_label'] = $cur_mst_label;
				$json_img_link['category'] = $category;
				$json_img_link['attachment'] = $attachment;
				$json_img_link['fileName'] = $fileName;
				
				//$json_img_link['attachment'] = 'https://evantage.ddns.net:80/React_mob_Dev/8.4/temp_POSTMAN/thumbnail/itm_ref_Ref_image_692639.jpeg ';
				 //$json_img_link['fileName'] = 'Test.png';
				array_push($json_return, $json_img_link);
								
			 }
		} while ( sqlsrv_next_result($stmt) );
	
	break;
	
	case 'Below':
	
	$sql = "SELECT
			itm_mst.site_cd,
			itm_mst.itm_mst_stockno,
			itm_mst.itm_mst_costcenter,
			itm_mst.itm_mst_account,
			itm_mst.itm_mst_mstr_locn,			
			itm_mst.itm_mst_desc,	
			itm_mst.itm_mst_issue_uom,			
			itm_mst.itm_mst_issue_price,
			itm_mst.itm_mst_ttl_oh,
			itm_mst_ext_desc = COALESCE(cast(itm_mst.itm_mst_ext_desc as varchar (255)),''),	
			itm_det.itm_det_part_deac_status,
			itm_det.itm_det_order_pt,		
			cur_mst.cur_mst_label,			
			itm_det_ttl_oh= COALESCE(itm_det_ttl_oh,0),
			
			category = CASE
			WHEN		((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt )
			AND         ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'L' ) THEN 'BELOW'

			WHEN		((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt )
			AND         ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'T' ) THEN 'BELOW'

			ELSE 'NORMAL' END,			
			
			itm_mst.RowID,
			attachment					= COALESCE(itm_ref.attachment,''),
			file_name					= COALESCE(itm_ref.file_name,''),
			itm_ref.RowID AS file_RowID				
			
			FROM		itm_mst (NOLOCK)

			INNER JOIN	itm_det (NOLOCK)            
			ON          itm_mst.site_cd = itm_det.site_cd
			AND         itm_mst.RowID = itm_det.mst_RowID

			INNER JOIN  itm_sts (NOLOCK)
			ON          itm_det.site_cd = itm_sts.site_cd 
			AND         itm_det.itm_det_part_deac_status = itm_sts.itm_sts_status

			LEFT OUTER 
			JOIN		itm_loc
			ON			itm_mst.site_cd = itm_loc.site_cd
			AND			itm_mst.RowID = itm_loc.mst_RowID
			AND			itm_mst.itm_mst_mstr_locn = itm_loc.itm_loc_stk_loc
			
			LEFT OUTER 
			JOIN		cur_mst
			ON			itm_mst.site_cd = cur_mst.site_cd
			and			cur_mst.cur_mst_base_cur =  1

			LEFT 
			OUTER 
			JOIN	( Select *, ROW_NUMBER() OVER (PARTITION BY mst_rowid ORDER BY itm_ref.type DESC) AS rn
						From itm_ref (NOLOCK)
						WHERE		itm_ref.type  IN ('P','A')
						And 		DataLength(itm_ref.attachment) > 0 
						And			DataLength(itm_ref.attachment) is not null
						and			itm_ref.file_name not like '%xlsx%' 
						and			itm_ref.file_name not like '%xls%'
					) itm_ref 
			ON itm_mst.site_cd = itm_ref.site_cd AND itm_mst.rowid = itm_ref.mst_rowid AND rn = 1

			WHERE	((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt)
							AND ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND	( itm_sts_typ_cd = '".$_REQUEST['status']."' )
							AND	( itm_mst.site_cd = '".$_REQUEST['site_cd']."' )
							AND (itm_mst_order_rule = 'L') or
					((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt)
							AND ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND	( itm_sts_typ_cd = '".$_REQUEST['status']."' )
							AND	( itm_mst.site_cd = '".$_REQUEST['site_cd']."' )
							AND (itm_mst_order_rule = 'T') 
							
							AND			itm_sts_typ_cd = '".$_REQUEST['status']."'

			ORDER BY	itm_mst_stockno OFFSET ".($page  -1)*$pageSize." ROWS FETCH NEXT ".$pageSize." ROWS ONLY ";
						
			$stmt = sqlsrv_query( $conn, $sql);

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

			$json = array();

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				
				$site_cd = $row['site_cd'];
				$RowID = $row['RowID'];
				$itm_mst_stockno = $row['itm_mst_stockno'];
				$itm_mst_costcenter = $row['itm_mst_costcenter'];
				$itm_mst_account = $row['itm_mst_account'];
				$itm_mst_mstr_locn = $row['itm_mst_mstr_locn'];
				$itm_mst_desc = $row['itm_mst_desc'];
				$itm_mst_issue_uom = $row['itm_mst_issue_uom'];
				$itm_mst_issue_price = $row['itm_mst_issue_price'];
				$itm_mst_ttl_oh = $row['itm_mst_ttl_oh'];
				$itm_mst_ext_desc = $row['itm_mst_ext_desc'];
				$itm_det_part_deac_status = $row['itm_det_part_deac_status'];
				$itm_det_order_pt = $row['itm_det_order_pt'];
				$itm_det_ttl_oh = $row['itm_det_ttl_oh'];
				$category = $row['category'];
				$cur_mst_label = $row['cur_mst_label'];
				
				
				$file_name = $row['file_name'];

				$attachment;
				$fileName;
				
				if(empty($file_name)){
					
					$attachment = Null;
					$fileName =Null;
					
				}else{
						  
					if (strpos($file_name, '.pdf') !== false) {
							
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, '.mp4') !== false) {
						
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, 'docx') == true){	
								
						$attachment = Null;
						$fileName =Null;
					
					}else{
									

									
						$Photo = $row['attachment'];
						$file_Name = $row['file_name'];
						
						$file_RowID = $row['file_RowID'];
						
						$file_Name = generateUniqueName($file_Name,$file_RowID);

						$exportFullPathFileName = $exportdir . $file_Name;
						$exportFullPathTNFileName = $exportdirtn . $file_Name;

						$publicFullPathFileName = $MobileURLFullSize . $file_Name;
						$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;
						
						if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
							resizeImageImagick($Photo, $exportFullPathTNFileName);
						} 

						$attachment = $publicFullPathTNFileName;
						$fileName =$file_Name;
						
					}
				}
				
				
				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['RowID'] = $RowID;
				$json_img_link['itm_mst_stockno'] = $itm_mst_stockno;
				$json_img_link['itm_mst_costcenter'] = $itm_mst_costcenter;
				$json_img_link['itm_mst_account'] = $itm_mst_account;
				$json_img_link['itm_mst_mstr_locn'] = $itm_mst_mstr_locn;
				$json_img_link['itm_mst_desc'] = $itm_mst_desc;
				$json_img_link['itm_mst_issue_uom'] = $itm_mst_issue_uom;
				$json_img_link['itm_mst_issue_price'] = $itm_mst_issue_price;
				$json_img_link['itm_mst_ttl_oh'] = $itm_mst_ttl_oh;
				$json_img_link['itm_mst_ext_desc'] = $itm_mst_ext_desc;
				$json_img_link['itm_det_part_deac_status'] = $itm_det_part_deac_status;
				$json_img_link['itm_det_order_pt'] = $itm_det_order_pt;
				$json_img_link['itm_det_ttl_oh'] = $itm_det_ttl_oh;
				$json_img_link['cur_mst_label'] = $cur_mst_label;
				$json_img_link['category'] = $category;
				$json_img_link['attachment'] = $attachment;
				$json_img_link['fileName'] = $fileName;
				
				array_push($json_return, $json_img_link);
								
			 }
		} while ( sqlsrv_next_result($stmt) );
	
	break;
	
	
	case 'Normal':
	
	
	$sql = "SELECT
			itm_mst.site_cd,
			itm_mst.itm_mst_stockno,
			itm_mst.itm_mst_costcenter,
			itm_mst.itm_mst_account,
			itm_mst.itm_mst_mstr_locn,			
			itm_mst.itm_mst_desc,	
			itm_mst.itm_mst_issue_uom,			
			itm_mst.itm_mst_issue_price,
			itm_mst.itm_mst_ttl_oh,
			itm_mst_ext_desc = COALESCE(cast(itm_mst.itm_mst_ext_desc as varchar (255)),''),	
			itm_det.itm_det_part_deac_status,
			itm_det.itm_det_order_pt,		
			cur_mst.cur_mst_label,			
			itm_det_ttl_oh= COALESCE(itm_det_ttl_oh,0),
			
			category = CASE
			WHEN		((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt )
			AND         ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'L' ) THEN 'BELOW'

			WHEN		((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt )
			AND         ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum )
			AND         ( itm_det_maximum  > 0 )
			AND         ( itm_det_order_pt  > 0 )
			AND         ( itm_mst_order_rule = 'T' ) THEN 'BELOW'

			ELSE 'NORMAL' END,			
			
			itm_mst.RowID,
			attachment					= COALESCE(itm_ref.attachment,''),
			file_name					= COALESCE(itm_ref.file_name,''),
			itm_ref.RowID AS file_RowID				
			
			FROM		itm_mst (NOLOCK)

			INNER JOIN	itm_det (NOLOCK)            
			ON          itm_mst.site_cd = itm_det.site_cd
			AND         itm_mst.RowID = itm_det.mst_RowID

			INNER JOIN  itm_sts (NOLOCK)
			ON          itm_det.site_cd = itm_sts.site_cd 
			AND         itm_det.itm_det_part_deac_status = itm_sts.itm_sts_status

			LEFT OUTER 
			JOIN		itm_loc
			ON			itm_mst.site_cd = itm_loc.site_cd
			AND			itm_mst.RowID = itm_loc.mst_RowID
			AND			itm_mst.itm_mst_mstr_locn = itm_loc.itm_loc_stk_loc
			
			LEFT OUTER 
			JOIN		cur_mst
			ON			itm_mst.site_cd = cur_mst.site_cd
			and			cur_mst.cur_mst_base_cur =  1

			LEFT 
			OUTER 
			JOIN	( Select *, ROW_NUMBER() OVER (PARTITION BY mst_rowid ORDER BY itm_ref.type DESC) AS rn
						From itm_ref (NOLOCK)
						WHERE		itm_ref.type  IN ('P','A')
						And 		DataLength(itm_ref.attachment) > 0 
						And			DataLength(itm_ref.attachment) is not null
						and			itm_ref.file_name not like '%xlsx%' 
						and			itm_ref.file_name not like '%xls%'
					) itm_ref 
			ON itm_mst.site_cd = itm_ref.site_cd AND itm_mst.rowid = itm_ref.mst_rowid AND rn = 1

			WHERE	NOT (
							((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt)
							AND ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND	( itm_sts_typ_cd = '".$_REQUEST['status']."' )
							AND	( itm_mst.site_cd = '".$_REQUEST['site_cd']."' )
							AND (itm_mst_order_rule = 'L')
						) AND NOT (
							((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt)
							AND ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND	( itm_sts_typ_cd = '".$_REQUEST['status']."' )
							AND	( itm_mst.site_cd = '".$_REQUEST['site_cd']."' )
							AND (itm_mst_order_rule = 'T')
						)
			AND			itm_sts_typ_cd = '".$_REQUEST['status']."'
			AND 		itm_mst.site_cd = '".$_REQUEST['site_cd']."'
			ORDER BY	itm_mst_stockno OFFSET ".($page  -1)*$pageSize." ROWS FETCH NEXT ".$pageSize." ROWS ONLY ";
						
			$stmt = sqlsrv_query( $conn, $sql);

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

			$json = array();

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				
				$site_cd = $row['site_cd'];
				$RowID = $row['RowID'];
				$itm_mst_stockno = $row['itm_mst_stockno'];
				$itm_mst_costcenter = $row['itm_mst_costcenter'];
				$itm_mst_account = $row['itm_mst_account'];
				$itm_mst_mstr_locn = $row['itm_mst_mstr_locn'];
				$itm_mst_desc = $row['itm_mst_desc'];
				$itm_mst_issue_uom = $row['itm_mst_issue_uom'];
				$itm_mst_issue_price = $row['itm_mst_issue_price'];
				$itm_mst_ttl_oh = $row['itm_mst_ttl_oh'];
				$itm_mst_ext_desc = $row['itm_mst_ext_desc'];
				$itm_det_part_deac_status = $row['itm_det_part_deac_status'];
				$itm_det_order_pt = $row['itm_det_order_pt'];
				$itm_det_ttl_oh = $row['itm_det_ttl_oh'];
				$category = $row['category'];
				$cur_mst_label = $row['cur_mst_label'];
				
				
				$file_name = $row['file_name'];

				$attachment;
				$fileName;
				
				if(empty($file_name)){
					
					$attachment = Null;
					$fileName =Null;
					
				}else{
						  
					if (strpos($file_name, '.pdf') !== false) {
							
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, '.mp4') !== false) {
						
						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, 'docx') == true){	
								
						$attachment = Null;
						$fileName =Null;
					
					}else{
									
									
						$Photo = $row['attachment'];
						$file_Name = $row['file_name'];
						
						$file_RowID = $row['file_RowID'];
						
						$file_Name = generateUniqueName($file_Name,$file_RowID);

						$exportFullPathFileName = $exportdir . $file_Name;
						$exportFullPathTNFileName = $exportdirtn . $file_Name;

						$publicFullPathFileName = $MobileURLFullSize . $file_Name;
						$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;
						
						if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
							resizeImageImagick($Photo, $exportFullPathTNFileName);
						} 

						$attachment = $publicFullPathTNFileName;
						$fileName =$file_Name;

						
						
					}
				}
				
				
				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['RowID'] = $RowID;
				$json_img_link['itm_mst_stockno'] = $itm_mst_stockno;
				$json_img_link['itm_mst_costcenter'] = $itm_mst_costcenter;
				$json_img_link['itm_mst_account'] = $itm_mst_account;
				$json_img_link['itm_mst_mstr_locn'] = $itm_mst_mstr_locn;
				$json_img_link['itm_mst_desc'] = $itm_mst_desc;
				$json_img_link['itm_mst_issue_uom'] = $itm_mst_issue_uom;
				$json_img_link['itm_mst_issue_price'] = $itm_mst_issue_price;
				$json_img_link['itm_mst_ttl_oh'] = $itm_mst_ttl_oh;
				$json_img_link['itm_mst_ext_desc'] = $itm_mst_ext_desc;
				$json_img_link['itm_det_part_deac_status'] = $itm_det_part_deac_status;
				$json_img_link['itm_det_order_pt'] = $itm_det_order_pt;
				$json_img_link['itm_det_ttl_oh'] = $itm_det_ttl_oh;
				$json_img_link['cur_mst_label'] = $cur_mst_label;
				$json_img_link['category'] = $category;
				$json_img_link['attachment'] = $attachment;
				$json_img_link['fileName'] = $fileName;
				array_push($json_return, $json_img_link);
								
			 }
		} while ( sqlsrv_next_result($stmt) );
	
	break;
}





$sql = "SELECT 
				COUNT(*) AS total_count,
				SUM(CASE
						WHEN ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt)
							AND ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							
							AND (itm_mst_order_rule = 'L') THEN 1
						WHEN ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt)
							AND ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND (itm_mst_order_rule = 'T') THEN 1
						ELSE 0
					END) AS below_count,
				SUM(CASE
						WHEN ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_order_pt)
							AND ((itm_loc_oh_qty - itm_loc_hard_resrv - itm_loc_short_qty + itm_loc_pr_due_in + itm_loc_due_in) < itm_loc_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND (itm_mst_order_rule = 'L') THEN 0
						WHEN ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_order_pt)
							AND ((itm_mst_ttl_oh - itm_det_ttl_hard_resrv - itm_det_ttl_short + itm_det_pr_due_in + itm_det_due_in) < itm_det_maximum)
							AND (itm_det_maximum > 0)
							AND (itm_det_order_pt > 0)
							AND (itm_mst_order_rule = 'T') THEN 0
						ELSE 1
					END) AS normal_count
			FROM itm_mst (NOLOCK)

			INNER JOIN	itm_det (NOLOCK)            
			ON          itm_mst.site_cd = itm_det.site_cd
			AND         itm_mst.RowID = itm_det.mst_RowID

			INNER JOIN  itm_sts (NOLOCK)
			ON          itm_det.site_cd = itm_sts.site_cd 
			AND         itm_det.itm_det_part_deac_status = itm_sts.itm_sts_status

			LEFT OUTER 
			JOIN		itm_loc
			ON			itm_mst.site_cd = itm_loc.site_cd
			AND			itm_mst.RowID = itm_loc.mst_RowID
			AND			itm_mst.itm_mst_mstr_locn = itm_loc.itm_loc_stk_loc
			
			LEFT OUTER 
			JOIN		cur_mst
			ON			itm_mst.site_cd = cur_mst.site_cd
			and			cur_mst.cur_mst_base_cur =  1
			
			WHERE		itm_mst.site_cd = '".$_REQUEST['site_cd']."'
			AND			itm_sts_typ_cd = '".$_REQUEST['status']."'";
		
				

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

				if( !$stmt ) {
					 $error_message = "Error selecting table (itm_mst) All Type";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
					 
				}
				do {
					 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {		
						$total_count = $row['total_count'];
						$below_count = $row['below_count'];
						$normal_count = $row['normal_count'];
					
					 }
				} while ( sqlsrv_next_result($stmt) );

if ($valid) {
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
	returnData($json_return,$total_count,$below_count,$normal_count);	
}


function generateUniqueName($file_name,$image_count) {

    $unique_name = "CMMS_IMAGE_" . $image_count . ".jpg"; 
    return $unique_name;
}

function returnData($json,$total_count,$below_count,$normal_count){
	$returnData = array(
	'status' => 'SUCCESS',	
	'total_count' => $total_count,	
	'below_count' => $below_count,	
	'normal_count' => $normal_count,	
	'data' => $json);
	
	echo json_encode($returnData);
}

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


?>