<?php

session_start();

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

include("config.inc.php");
include("Functions_Purchase.php");
require 'PHPMailer-master/class.phpmailer.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");


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

$message='';
$mode = $_GET['mode'];
$txtAuctionId 	= $_POST['txtAuctionId']; 	if($txtAuctionId == '') $txtAuctionId = $_GET['txtAuctionId'];
$txtDate 		= $_POST['txtDate']; 		if($txtDate == '') 		$txtDate = $_GET['txtDate'];
$txtStartTime 	= $_POST['txtStartTime']; 	if($txtStartTime == '') $txtStartTime = $_GET['txtStartTime'];
$txtEndDate 	= $_POST['txtEndDate']; 	if($txtEndDate == '') 	$txtEndDate = $_GET['txtEndDate'];
$txtEndTime 	= $_POST['txtEndTime']; 	if($txtEndTime == '') 	$txtEndTime = $_GET['txtEndTime'];
$item_code 		= $_POST['item_code']; 		if($item_code == '') 	$item_code = $_GET['item_code'];
$txtQty 		= $_POST['txtQty']; 		if($txtQty == '') 		$txtQty = $_GET['txtQty'];
$auction_remark 	= $_POST['auction_remark']; 	if($auction_remark == '') 	$auction_remark = $_GET['auction_remark'];
$checked_auction = $_POST['checked_auction']; 	if($checked_auction == '') 	$checked_auction = $_GET['checked_auction'];

$auction_iddt = $_GET['auction_iddt'];
if($txtQty== '') $txtQty = 0;
if($checked_auction == 0) $checked_auction = 0;

$ndate = explode("/", $txtDate);
$txtDate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
if($txtStartTime != ''){
	
	$txtDate .=  " ".$txtStartTime.":00"; 
}
else $txtDate .=  " 00:00:00";

$ndate = explode("/", $txtEndDate);
$txtEndDate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
if($txtStartTime != '')
	$txtEndDate .=  " ".$txtEndTime.":00";
else $txtEndDate .=  " 00:00:00";

if($auction_remark == '' ) $auction_remark = "NULL";
else $auction_remark = "'".$auction_remark."'";

//Submit Record Mode
if ($mode == "insertitem" && $txtAuctionId =='' ){
	$ErrorExists == 0;
	fbegin();	
	$AuctionId = get_nextsequence('auction_id','',$connection);
	fcommit();
	
	fbegin();
	$query = " INSERT INTO temp_item_auction (auction_id, auction_startDate, auction_endDate, item_code, auction_qty, auction_remark )".
			 " VALUES ( ".$AuctionId.", '$txtDate', '$txtEndDate', '$item_code', $txtQty, $auction_remark)";
					 
	$result=mysql_query($query,$connection);
	if ($result){
		fcommit();
		$txtAuctionId = $AuctionId;
		$querysup = "select supplier_code from item_supplier where item_code='$item_code'; ";
		$ressup = mysql_query($querysup, $connection);
		while($rwsup = mysql_fetch_row($ressup)){
			fbegin();	
			$auctiondt_id = get_nextsequence('auctiondt_id','',$connection);
			fcommit();
			
			$querydt = " INSERT INTO temp_item_auctiondt (auctiondt_id, auction_id, supplier_code, auction_checked )".
						" VALUES ( $auctiondt_id, $AuctionId, '$rwsup[0]', 1);"; 
					 
			$result=mysql_query($querydt,$connection);
		}
	}else{
		$AuctionId = "";
		$message  = mysql_error(); 
		frollback();
	}
	
}
else if ($mode == "updateitem" && $txtAuctionId !='' ){
	$ErrorExists == 0;
	$queryold = "select item_code from temp_item_auction where auction_id = $txtAuctionId; ";	
	$resold = mysql_query($queryold,$connection);
	$rwold = mysql_fetch_row($resold);
	$item_codeold = $rwold[0];
	
	fbegin();
	$query = " update temp_item_auction set auction_startDate = '$txtDate', auction_endDate = '$txtEndDate', item_code = '$item_code', auction_qty =  $txtQty,".
			 " auction_remark = $auction_remark where auction_id = $txtAuctionId;";
		//echo$query;exit;	
	$result=mysql_query($query,$connection);
	if ($result){
		fcommit();
		if($item_codeold != $item_code){
			$querydel = "delete from temp_item_auctiondt where auction_id = $txtAuctionId;";
			$resdel=mysql_query($querydel,$connection);
			
			$querysup = "select supplier_code from item_supplier where item_code='$item_code'; ";
			$ressup = mysql_query($querysup, $connection);
			while($rwsup = mysql_fetch_row($ressup)){
				fbegin();	
				$auctiondt_id = get_nextsequence('auctiondt_id','',$connection);
				fcommit();
				
				$querydt = " INSERT INTO temp_item_auctiondt (auctiondt_id, auction_id, supplier_code, auction_checked )".
							" VALUES ( $auctiondt_id, $txtAuctionId, '$rwsup[0]', 0);"; 
						 
				$result=mysql_query($querydt,$connection);
			}
		}
	}else{
		$AuctionId = "";
		$message  = mysql_error(); 
		frollback();
	}
	
}

//Edit Detail Tables
else if (($mode == "editdt-ajax") && $txtAuctionId != "" && $auction_iddt != ""){
	
				
	fbegin();	
					
	$query = " update temp_item_auction set auction_startDate = '$txtDate', auction_endDate = '$txtEndDate', auction_qty =  $txtQty,".
			 " auction_remark = $auction_remark where auction_id = $txtAuctionId;";
					 						 
	//echo $query; exit;
	$result=mysql_query($query,$connection);
	if ($result){
		fcommit();
		$querydt = " update temp_item_auctiondt set auction_checked = '$checked_auction' where auctiondt_id = $auction_iddt;";
					 						 
		//echo $query; exit;
		$result=mysql_query($querydt,$connection);
			
	}else{
		$message  = mysql_error(); 
		frollback();
	}				

}

else if ($mode == 'deletedt' && $auction_iddt != ''){
	$querydelete = "delete from temp_item_auctiondt where auctiondt_id = $auction_iddt ";
	$result = mysql_query($querydelete, $connection);
	
} 
//Delete Record Mode From Main Tables
else if ($mode == "" && $mainmode == "delete" && $txtAuctionId != ''){

	$AllowDelete = 1;
	$message  = '';
	
	//If No Constraints Exists On Delete
	if ($AllowDelete == 1) {
	
	    $query = " DELETE FROM item_auctiondt ".
				 " WHERE auction_id = ".$txtAuctionId;
		if (!updateData($connection, $query, true)){
			$message  = mysql_error(); 
			$AllowDelete == 0;
		}
		
	}
	if ($AllowDelete == 1) {
		$query = " DELETE FROM item_auction ".
					 " WHERE auction_id = ".$txtAuctionId;
			if (!updateData($connection, $query, true))
				$message  = mysql_error();   
			else $message  = "Item Auction has been Deleted!";   
	}
}

else if($mode == "" && $mainmode == "deletepending" && $txtAuctionId != '' ){
	$querydel = "delete from temp_item_auctiondt where auction_id = $txtAuctionId ";
	$resdel = mysql_query($querydel,$connection);
	$querydel = "delete from temp_item_auction where auction_id = $txtAuctionId ";
	$resdel = mysql_query($querydel,$connection);
	
}
//Post Mode	
else if($mode == "post" && $txtAuctionId != ''){  

	
    $SaveAllow   = 1;
	$ErrorExists = 0;
	
	
	$sub_query = " Select Count(*) FROM temp_item_auction ".
				 " WHERE auction_id = ".$txtAuctionId;
	$result = selectData($connection, $sub_query);
	$auctiondts = $result[0];
	if($auctiondts == '') $auctiondts = 0;
	
	if ($auctiondts == 0) {
		$ErrorExists = 1;
		$message = "Save not allowed. Please add at least one Supplier.";
	}
	
	
	
		
	//Check User Access For Date Modification/ Lock Period
	$MainCompanyName=explode('_',$_SESSION['dbName']);

	$sub_query= " Select ifNull(AllowLockedPeriod,0) As AllowLockedPeriod, LockedPeriod As LockedPeriodDate, LockedPeriodDays As LockedPeriodDays ".
				" From ".$_SESSION['dbLabel'].".ListUserDir  ".
				" Where Usercode='".$_SESSION['useraccount']."' ".
				" And Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ".
				" And Upper(Diryear) = Upper('".$MainCompanyName[1]."') ";
				  
	$sub_result = mysql_query($sub_query,$connection) or die(mysql_error());
	$rowsperiod = mysql_num_rows($sub_result);

	while($sub_rows=mysql_fetch_array($sub_result)){
		extract($sub_rows);
	}	
	
	$ExceedLockPeriod = 0;
	if ($rowsperiod > 0 && $AllowLockedPeriod == 1) { 
		if (!(empty($LockedPeriodDate)) && $LockedPeriodDate != '' && insertMySQLDate($vPrdDate) < $LockedPeriodDate) $ExceedLockPeriod = 1;
		if ($LockedPeriodDays != '' && dateDiff(insertMySQLDate(displayToday()),insertMySQLDate($vPrdDate)) > $LockedPeriodDays) $ExceedLockPeriod = 1;
	}
	//echo $sub_query;exit;
	

	
	if ($SaveAllow == 1 && $ErrorExists == 0 && $ExceedLockPeriod == 0) {
	
			
	$query = " update temp_item_auction set auction_startDate = '$txtDate', auction_endDate = '$txtEndDate', auction_qty =  $txtQty,".
			 " auction_remark = $auction_remark where auction_id = $txtAuctionId;";
					 						 
	//echo $query; exit;
	$result=mysql_query($query,$connection);
		
		fbegin();
		$query = "SELECT CopyData_TempAuction( '".$txtAuctionId."') FROM Dual ";
		//echo $query;exit;
		$result = selectData($connection, $query);
		
		$result = $result[0];	
		if ($result){
			fcommit();
						
			$query   = " SELECT auction_reference FROM item_auction WHERE auction_id = $txtAuctionId ";
			$result  = selectData($connection, $query);
			$vRef = $result[0];
					
			if ($vRef == ''){
							
				$vProjCostCent = '';
							
				fbegin();
				$vRef = get_nextsequence('auction_reference',$vProjCostCent,$connection);	
				fcommit();
							
				$query = " UPDATE item_auction SET ".
						 " auction_reference = $vRef ".
						 " WHERE auction_id = $txtAuctionId ";//echo $query;
							
				if (!updateData($connection, $query, true)) 
					$message  = mysql_error();
						
			}
			$recipients = array();
			$query = " select Supplier_Email, concat(item_auction.item_code, ' ', item_description), auction_reference,date_format(item_auction.auction_startDate,'%d-%m-%Y %h:%i %p'),
						date_format(item_auction.auction_endDate,'%d-%m-%Y %h:%i %p') as auction_endDate
						from item_auctiondt
						inner join item_auction on item_auction.auction_id = item_auctiondt.auction_id
						left join suppliers on suppliers.ledger_number = item_auctiondt.supplier_code
						left join items on items.item_code = item_auction.item_code
						WHERE item_auction.auction_id = $txtAuctionId and ifnull(auction_checked,0) = 1";
			$res = mysql_query($query,$connection);
			$i=0;
			while($rw= mysql_fetch_row($res)){
				$Body = "<table><tr><td>Dear Vendor,</td></tr>
				<tr><td>We are pleased to inform you of our below requirement. As an approved vendor, we have invited you to participate in the negotiation program.</td></tr>
				<tr><td>Start Date: ".$rw[3]." </td></tr>
				<tr><td>End Date: ".$rw[4]." </td></tr>
				<tr><td>Item: ".$rw[1]." </td></tr>
				<tr><td> Kindly log into your vendor portal and follow the instructions to participate in the negotiation process.</td></tr>
				<tr><td>In case of any query, please feel free to reach any of the following persons below: </td></tr>
				<tr><td> </td></tr>
				<tr><td>  </td></tr>
				<tr><td>Ire      Procurement Analyst    08056491293   ire@sevenup.org </td></tr>
				<tr><td>Kennedy  National Sourcing Mgr  08056491223   nmpm@sevenup.org </td></tr>
				<tr><td>Tajudeen Software Engineer      08035979936   Support@compusoft-ng.com </td></tr>
				<tr><td>Radhe    Head of Supply Chain   08056491050   radhe@sevenup.org </td></tr>
				<tr><td>  </td></tr>
				<tr><td>  </td></tr>
				<tr><td>  </td></tr>
				<tr><td> Many thanks & regards, </td></tr>
				<tr><td> Seven-Up Bottling Co. PLC,</td></tr>
				<tr><td> N i g e r i a.  </td></tr>
				</table>";
			//$recipients[$i] = $rw[0];
			$emailusers = $rw[0];
			$Subject = "Seven Up Request # ".$rw[2]." for Quotation for Item: $rw[1]";
			
				//send email
				$query_mailsetup="select hostname,mailagent,port,SenderEmail,ReceiverEmail,password from configurationemail";
				$result_mailsetup = mysql_query($query_mailsetup, $connection);
				$row_setup=mysql_fetch_row($result_mailsetup); 
				$hostname=$row_setup[0];
				
				$sender=$row_setup[3];
				$pswd = $row_setup[5];
				$recipients="";
				
				$mail = new PHPMailer;
				$mail->IsSMTP();                                      // Set mailer to use SMTP
				$mail->Host = $hostname; //'smtp.gmail.com';  // Specify main and backup server
				$mail->SMTPAuth = true;   
				//$mail->SMTPSecure = 'tls';	// Enable SMTP authentication
				$mail->Username = $sender;                            // SMTP username
				$mail->Password = $pswd;                           // SMTP password
				//$mail->SMTPSecure = 'ssl';                            // Enable encryption, 'ssl' also accepted
				$mail->From = $sender;
				$mail->WordWrap = 50;                                 // Set word wrap to 50 characters
				$mail->Body    =  $Body;
				$mail->IsHTML(true);                                  // Set email format to HTML
				$mail->Subject = $Subject;
				//$mail->SMTPDebug = 1;
				$mail->Port = $row_setup[2]; 
				$mail->AddAddress($emailusers, ''); 	
				$recipients = $recipients." $emailusers";
				 if(!$mail->Send()) { 
					  $error = $mail->ErrorInfo;
						$query="insert into mailogs(ticket_date,errormsg,sender,recipient,mailbody) values (now(),'$error','$sender','$recipients','$Body')";
						$result = mysql_query($query, $connection); 
					} 	 		
			$i++;
			}  
			
			
			//if(!(empty($recipients)))
			//	emailsend($recipients, $Body, $Subject, $connection);

		} else{
			$message  = mysql_error(); 
			frollback();
		}	
	}// End if($SaveAllowed == 1)
	else {
		if ($ExceedLockPeriod == 1)
			$message = 'Save not allowed. Exceed Lock Period.';			
			
		frollback();
	}
	if ($SaveAllow == 1 && $ErrorExists == 0 )
		header ("Location: ./Form_ItemAuction.php?actionmode=".$mode."&mainaction=".$mainmode."&txtAuctionId=".$txtAuctionId."&PrPrdRef=".$vRef."&message=".$message);
	else
		header ("Location: ./Form_ItemAuction.php?mainaction=".$mainmode."&txtAuctionId=".$txtAuctionId."&message=".$message);
	exit;	
}

else if($mode == "" && $mainmode == "search" && $txtAuctionId != '' ){
	$AllowEdit = 1;
	$query = " insert into temp_item_auction (auction_id, auction_reference, auction_startDate, auction_endDate, item_code, auction_qty, auction_remark, timeExtended ) ".
			 " select auction_id, auction_reference, auction_startDate, auction_endDate, item_code, auction_qty, auction_remark, timeExtended  ".
			 " from item_auction where auction_id = $txtAuctionId ";
	
	if (!updateData($connection, $query, true)){
		  $message  = mysql_error(); 
		  $AllowEdit = 0;
	}
	if($AllowEdit == 1)  {
		$querydt = " insert into temp_item_auctiondt (auctiondt_id, auction_id, supplier_code, auction_checked)".
				   " select auctiondt_id, auction_id, supplier_code, auction_checked ".
				   " from item_auctiondt where auction_id = $txtAuctionId ";
		if (!updateData($connection, $querydt, true)){
		  $message  = mysql_error(); 
		  $AllowEdit = 0;
		}
	}
}

else if($mode == "cancelAuction" && $txtAuctionId != '' ){
	$errorExist = 0;
	$delete1 = "delete from temp_item_auctiondt where auction_id=$txtAuctionId;";
	$res1 = mysql_query($delete1,$connection);
	if(!$res1){
		$message = mysql_error();
		$errorExist = 1;
	}
	
	if($errorExist == 0){
		$delete = "delete from temp_item_auction where auction_id=$txtAuctionId;";
		$res = mysql_query($delete,$connection);
		$message = mysql_error();
	}
}
$message = ereg_replace(' ','%20',$message);


if ( ($mode == "insertitem" ) || ($mode == "updateitem" )){
	
	echo "<form name='auction' method='POST'>";
	echo "<input type='hidden' name='txtAuctionId' value='$txtAuctionId'>";
	echo "<input type='hidden' name='message' value='$message'>";
	echo "</form>";
	echo "<script>";
	echo " document.auction.action='Form_ItemAuction.php'; ";
	echo " document.auction.submit();";
	echo "</script>";
	exit;
}
	
else if ($mode == "" && $mainmode == "delete" )	
	header ("Location: ./Form_ItemAuctionSearch.php?message=".$message);
else if($mode == "" && $mainmode == "deletepending" && $txtAuctionId != '' )
	header ("Location: ./Form_AuctionItemPending.php");
else if($mode == "" && $mainmode == "search" && $txtAuctionId != '' ){
	if ($AllowEdit == 1)
	header ("Location: ./Form_ItemAuctionSearch.php?PMode=1&message=".$message);
	else header ("Location: ./Form_ItemAuctionSearch.php?PMode=0&message=".$message);
}
else if($mode == "cancelAuction") 
	header ("Location: ./Form_ItemAuction.php?message=".$message);

$return_arr = array();

$row_array['message'] = $message;
$row_array['transid'] = $txtAuctionId;
		
array_push($return_arr,$row_array);
echo json_encode($return_arr);

function emailsend($mail){
	if(!$mail->Send()) { 
	  $error = $mail->ErrorInfo;
		$query="insert into mailogs(ticket_date,errormsg,sender,recipient,mailbody) values (now(),'$error','$sender','$recipients','$Body')";
		$result = mysql_query($query, $connection); 
	} else {
		echo "ok";
	}
	
}

?>