<?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'];



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.itm_mst_partno,
			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
			
			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']."'
			

			ORDER BY	itm_mst_stockno ";
						
			$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_mst_partno = $row['itm_mst_partno'];
				$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'];
				
				$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_mst_partno'] = $itm_mst_partno;
				$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;
				
				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.itm_mst_partno,
			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
			
			
			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_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') 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_mst_order_rule = 'T') 

			ORDER BY	itm_mst_stockno ";
						
			$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_mst_partno = $row['itm_mst_partno'];
				$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'];
				
				$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_mst_partno'] = $itm_mst_partno;
				$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;
				
				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.itm_mst_partno,
			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
			
			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	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_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_mst_order_rule = 'T')
						)

			ORDER BY	itm_mst_stockno";
						
			$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_mst_partno = $row['itm_mst_partno'];
				$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'];
				
				$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_mst_partno'] = $itm_mst_partno;
				$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;
				
				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";
		
				

				$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 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();
}
	


?>