<?php


session_start();
if($_SESSION['dbName']==""){
    header("location:../index.php");
}

include("config.inc.php");
include("function.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

$querylog="SELECT FI_SellingPriceAdjust as loginaccess, username as UserName from ".$_SESSION['dbLabel'].".ListUserDir where Usercode='".$_SESSION['useraccount']."' 	and Upper(concat(CompanyName,'_',Diryear)) = Upper('".$_SESSION['dbName']."')	";
	$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
	while($rows=mysql_fetch_array($resultlog)){
		extract($rows);
	}
	
if ($loginaccess=='' or $loginaccess==0)
	header("location:menu.php");

//Read From Setup Table							
$querysetup= " select  base1,base2,DecimalBase  from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}


$group_code 		= $_POST['group_code'];
$subgroup_code 		= $_POST['subgroup_code'];
$category_code 		= $_POST['category_code'];
$sellingprice_nbr 	= $_POST['sellingprice_nbr'];
$def_per 			= $_POST['def_per'];
$itemloaded 		= $_POST['itemloaded'];
$items2		 		= $_POST['items2'];
$loadedfromexcel 	= $_POST['loadedfromexcel'];
$Item_code 			= $_POST['Item_code'];
$adjustDays 		= $_POST['adjustDays'];
$mode				= $_GET['mode'];

$errorExists = 0;
$PriceArray = array('','I','II','III','IV','V');

 
if (($itemloaded == 1 || $loadedfromexcel == 1) && $mode == 'changePrice'){
	//  var_dump($_POST);echo"<BR>";
	$items = json_decode($items2,true);
	$ref = get_nextsequence('sellingPrice_Reference','',$connection);	
	// echo "<br>50-".
	$queryh = "insert into sellingprice_adjust (sellingprice_ref, group_code, subgroup_code, category_code, creationDate, user_code, sellingprice_nbr, default_per, default_val) values 
				(".$ref.", ".GetSQLValueString($group_code,'text').", ".GetSQLValueString($subgroup_code,'text').", ".GetSQLValueString($category_code,'text').", now(), '".$_SESSION['useraccount']."',
					".$sellingprice_nbr.", ".GetSQLValueString($def_per,'double').",  ".GetSQLValueString($def_val,'double')."  )";
	$result = mysql_query($queryh,$connection);
	$message = mysql_error();
	
	if($message == ''){
		$sellingprice_id = mysql_insert_id();
	} else $errorExists = 1;  
	if($errorExists == 0 && $sellingprice_id != ''){
		// echo "<br>62-".
		$queryload = "select Item_Code, items.currency_code, Item_SellingPrice$sellingprice_nbr
			from items
			left join grouping on grouping.group_code = items.group_code
			left join subgroup on subgroup.subgroup_code = items.subgroup_code
			left join category on category.category_code = items.category_code
			where ifnull(sale_group,0) = 1 and if('$group_code'='',1=1, items.group_code = '$group_code')
			and if('$subgroup_code'='',1=1, items.subgroup_code = '$subgroup_code') and if('$category_code'='',1=1, items.category_code = '$category_code')
			and if('$Item_code'='',1=1, items.item_code = '$Item_code')";
		if($existsinpurchases1 == 1){ 
			$queryload .= " and (";
			$queryload .= " items.item_code in (select distinct item_code from purchasesdt where Purchase_Date >= DATE_ADD(now(),INTERVAL - $adjustDays day))";
			$queryload .= " Or ";
			$queryload .= " items.item_code in (select distinct item_code from adjustmentdt where adjustment_Date >= DATE_ADD(now(),INTERVAL - $adjustDays day))";
			$queryload .= ")";
		}
			$queryload .= "order by item_code"; 
			 	  
		$i=0;
		$resload = mysql_query($queryload,$connection);
		while($rowload = mysql_fetch_array($resload)){	
			//echo"<BR>".$i;
			// echo "<pre>";
			// var_dump($rowload);
			// echo "</pre>";
		if(($loadedfromexcel == 1 && in_array($rowload[0],array_keys($items))) || $loadedfromexcel == 0){
				$defper = 'def_per'.$i;
				$defval = 'def_val'.$i;
				
				$newprice = 0;
				// echo"<br> $defper : ".$$defper;
				// echo"<br> rowload[2] : ".$rowload[2];
				// echo"<br> $defval : ".$$defval;
				if($rowload[2] >= 0 && ($$defper != '' || $$defval != '')){
					$nsp = "newsellingPrice".$i;
					// if ($$defper != '') 
						// $newprice = round($rowload[2]+($$defper*$rowload[2]/100),$DecimalBase);
					// else if ($$defval != '') $newprice = round($rowload[2] + $$defval,$DecimalBase);
					$newprice = $$nsp;
					$newpriceb1 = ConvertCurrency($rowload[1], $base1, date('Y-m-d'), $newprice, $connection);
					$newpriceb2 = ConvertCurrency($rowload[1], $base2, date('Y-m-d'), $newprice, $connection);
					
					if ($$defper == '') $$defper = 0;
					if ($$defval == '') $$defval = 0;
					
					 // echo"<br>90-".
					$queryd = "insert into sellingprice_adjustdt (sellingprice_id, item_code, currency_code, sellingpricedt_per, sellingpricedt_val, sellingpriceBefore,
									sellingprice".$sellingprice_nbr.", sellingprice".$sellingprice_nbr."b1, sellingprice".$sellingprice_nbr."b2) 
								values ( $sellingprice_id, '".$rowload[0]."', '".$rowload[1]."',".$$defper.", ".$$defval.", ".$rowload[2].",$newprice, $newpriceb1, $newpriceb2 )";
					$resultd = mysql_query($queryd,$connection);
					$message = mysql_error();	
					if ($message == ''){
						 // echo"<br>96-".
						$queryup = "update items set Item_SellingPrice".$sellingprice_nbr." = $newprice, 
												Item_SellingPrice".$sellingprice_nbr."Base1 = $newpriceb1, 
												Item_SellingPrice".$sellingprice_nbr."Base2 = $newpriceb2
									where item_code = '".$rowload[0]."' ";
						$resultup = mysql_query($queryup,$connection);
						 // echo"<br>97-".
						$queryd2 = "insert into sellingpricehistory ( username, item_code, fieldname, value_before, value_after, datemodified, currency_code, groupid) 
									select '".$UserName."', item_code, '".$PriceArray[$sellingprice_nbr]."', '$rowload[2]',  '$newprice', now(), items.currency_code, Increment
									from items 
									left join grouping on grouping.group_code = items.group_code
									where item_code = '".$rowload[0]."' ";
						$resultd2 = mysql_query($queryd2,$connection);			
					} else break;
				}
				$i++;
			}
		}			
	}
	$sellingprice_nbr = '';
	$loadedfromexcel  = 0;
	$items 			  = '';
	$message = "Selling Price Updated Successfully! ";
  // exit;
}

if($mode == 'importExcel'){
	$ErrorExists = 0;
	$loadedfromexcel = 0;
	if (isset($_FILES['image']) && $_FILES['image']['size'] > 0) { 

		$image_name = addslashes($_FILES['image']['name']);
		$image_size = getimagesize($_FILES['image']['tmp_name']);
		$image_type =$_FILES['image']['type'];
		
		if( $image_type != 'application/vnd.ms-excel'){
			$message = "File is not an Excel File";
			$ErrorExists = 1;
		}
		else 
			if ($_FILES['image']['size'] > 204800) {
				$message = "File is too large";
				$ErrorExists = 1;
			}
			else {
				$image = addslashes(file_get_contents($_FILES['image']['tmp_name']));
			}
	}
	
	if($ErrorExists == 0){
		
		
		$foldername='uploads';
		if (!file_exists($foldername)) {
			mkdir("./uploads", 0700);
		}

		$fileName = $_FILES["image"]["name"]; 
		$fileTmpLoc = $_FILES["image"]["tmp_name"];
		// Path and file name
		$pathAndName = "uploads/".$fileName;
		$pathAndName1 = "uploads/".$fileName;
		// Run the move_uploaded_file() function here
		$moveResult = move_uploaded_file($fileTmpLoc, $pathAndName);
		
		require_once('Spreadsheet/Excel/Reader.php');
		$excel = new Spreadsheet_Excel_Reader();
		$excel->read($pathAndName1); 
		$x=2;
		$arrayemp = array();
		
		$items = array();
		while($x<=$excel->sheets[0]['numRows']) { ///rowwwww
				
			$txtItemCode = $excel->sheets[0]['cells'][$x][1];
			$itemdesc = $excel->sheets[0]['cells'][$x][2];
			$newprice = $excel->sheets[0]['cells'][$x][3];
			
			
			$query = "select 1 from items where item_code ='$txtItemCode'";
			$result = mysql_query($query,$connection);
			$numrow = mysql_num_rows($result);
			
			if($numrow == 0){
				$message = "Item $txtItemCode does not exist!";
				$loadedfromexcel = 0;
				$itemloaded = 0;
				break;
			} else {
				$items[$txtItemCode]['newprice'] = $newprice;
			}
			
			$x++;
			
			
		} 
	}
	if($message == '' ) $loadedfromexcel = 1;
	$items = json_encode($items);
	unlink($pathAndName1); 
}

print <<<HERE
	<form name='sellingprice' method='POST' action='Form_SellingPrice.php'>
		<input type='hidden' name='message' id='message' value='$message'>
		<input type='hidden' name='items' id='items' value='$items'>
		<input type='hidden' name='sellingprice_nbr' id='sellingprice_nbr' value='$sellingprice_nbr'>
		<input type='hidden' name='loadedfromexcel' id='loadedfromexcel' value='$loadedfromexcel'>
		<input type='hidden' name='itemloaded' id='itemloaded' value='$itemloaded'>
	</form>
	<script>
		document.sellingprice.submit();
	</script>

HERE;


function ConvertCurrency($FromCurr, $ToCurr, $FromDate, $Amount, $connection)
{
	$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."', 0, 0, '', '', ifNull($Amount,0)),0) From Dual ";
	$result = mysql_query($query,$connection);
	$row = mysql_fetch_row($result);
	 Return	$row[0];

}
?>