Location: PHPKode > scripts > Simple Accounting System > SimpleAccounts/addAccount.php
<?php
session_start();
include_once "member.php";
include_once "lib.dbconnect.php";
include_once "lib.actualGroupHeadConfig.php";

//mysql_select_db($_COOKIE['username']);

$modAccountname = "";
$modActualGroupHead = "";
$modOtherdetails = "";
$modOpeningBalance = "";
$modOpeningBalanceType = "";
$modClosingBalance = "";
$modClosingBalanceType = "";
$message = "";
$whereCondition = "";
$disableActualGroupHead = false;
$displayOpeningBalance = true;
$displayClosingBalance = true;


if ( isset($_GET['operation']) && ($_GET['id'] != '') ) 
{
	if ($_GET['operation'] == "modify") { 
		editAccounts($_GET['id']); 
	} else if ($_GET['operation'] == "delete") {
		deleteAccount($_GET['id']);
	}	 		
} else if (isset($_POST['modifysaveid']) &&  ($_POST['modifysaveid'] != '')){
	modifySaveAccounts();
}  else if (isset($_POST['searchby']) && ($_POST['searchby'] != "")){
	searchAccounts();	
}  else if ($_POST && (!isset($_GET['operation'])) ) {
		addAccounts();			 
}

function searchAccounts()
{
	global $message,$whereCondition;
	global $modAccountname,$modActualGroupHead,$modOtherdetails;	
	switch ($_POST['searchby'])
	{
		case "accountname" :
			if ($_POST['accountname'] != "") {
				$whereCondition = " where acc_name like '%".trim(strtoupper($_POST['accountname']))."%'";	
				$modAccountname = trim(strtoupper($_POST['accountname']));
			} else {
				$message = "Account Name cannot be empty";
				return false;
			}
		break;
		case "actualgrouphead" :
			if ($_POST['actualgrouphead'] != "") {
				$whereCondition = " where act_group_head='".trim(strtoupper($_POST['actualgrouphead']))."'";
				$modActualGroupHead = trim($_POST['actualgrouphead']);			
			} else {
				$message = "Group Head cannot be empty";
			}
		break;
		case "otherdetails" :				
			$whereCondition = " where other_details like '%".trim($_POST['otherdetails'])."%'";
			$modOtherdetails= trim($_POST['otherdetails']);			
		break;
	}	
}

function deleteAccount($id) {
	global $message;		
	$query = "select debit, credit from ".$_COOKIE['username']."_daybook where (credit=(select acc_name from ".$_COOKIE['username']."_account_name where refid=".$id.") or debit=(select acc_name from ".$_COOKIE['username']."_account_name where refid=".$id."))";
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0) {
		$message = "Transactions made with this account, So Account Name Cannot be deleted";
		return false;
	}
	
	if (mysql_query ("LOCK TABLES ".$_COOKIE['username']."_account_name WRITE")) {
		$query = "DELETE FROM ".$_COOKIE['username']."_account_name WHERE refid=".$id;
		if (mysql_query($query))
		{ 
			$message = "Deleted the record";
		} else { 
			$message = "Record not deleted"; 
		}  
		mysql_query ("UNLOCK TABLES");
	}
}

function modifySaveAccounts() {	
	global $message;
	if ($_POST['accountname'] == "") { $message = "Account Name cannot be empty"; return false;}
	if (checkAccountAlreadyExists($_POST['modifysaveid'])){
		$result = updateDbAccountById ($_POST['modifysaveid']);
		if ($result){			
			//echo "Account Name : ".retrieveAccountNameById($_POST['modifysaveid']);
			$debitUpdateQuery = "update ".$_COOKIE['username']."_daybook set credit='".trim(strtoupper($_POST['accountname']))."' where credit='".retrieveAccountNameById($_POST['modifysaveid'])."'"; 
			$creditUpdateQuery = "update ".$_COOKIE['username']."_daybook set debit='".trim(strtoupper($_POST['accountname']))."' where debit='".retrieveAccountNameById($_POST['modifysaveid'])."'"; 
			$updateCreditResultSet = mysql_query($debitUpdateQuery);
			$updateDebitResultSet = mysql_query($creditUpdateQuery);
			if ($updateDebitResultSet && $updateCreditResultSet){
				$message =   "Account Name &quot;".$_POST['accountname']."&quot; been modified";
				return true;
			}
		} else {
			$message = "Failed to modify data";
		}
	} 
}

function editAccounts($id)
{
	global $xml_file_name, $fileNameWithPath;
	global $modAccountname,$modActualGroupHead,$modOtherdetails,$modOpeningBalance, $modOpeningBalanceType, $modClosingBalance, $modClosingBalanceType;
	global $disableActualGroupHead,$displayOpeningBalance;
	global $listOfActualGroupHead;
	
	$query = "select debit, credit from ".$_COOKIE['username']."_daybook where (credit=(select acc_name from ".$_COOKIE['username']."_account_name where refid=".$id.") or debit=(select acc_name from ".$_COOKIE['username']."_account_name where refid=".$id."))";
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0) {
		$disableActualGroupHead = true;
	}
	$result = retrieveAccountById($id);
	if (mysql_num_rows($result) > 0) {
		while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
			$modAccountname = trim(strtoupper($row[0]));
			$modActualGroupHead = trim(strtoupper($row[1]));			
			$modOtherdetails= trim($row[2]);
			$modOpeningBalance = trim($row[3]);			
			$modOpeningBalanceType = trim($row[4]);		
			$modClosingBalance = trim($row[5]);			
			$modClosingBalanceType = trim($row[6]);		
			
			if ($listOfActualGroupHead[$modActualGroupHead][0] != "bs") {
				$displayOpeningBalance = false;
			}
		}
	}	
}

// Inserts Account Name, Account Head, Group Head and Other Details into Database.
function insertDbAccount(){
	global $message;
	global $listOfActualGroupHead;	
	$act_group_head = $_POST['actualgrouphead'];
	
	$query = "insert into ".$_COOKIE['username']."_account_name(acc_name, act_group_head, other_details, acc_head, group_head";
	if ($_POST['openingbalance'] != "") {
		$query .= ",opening_balance,opening_balance_type";
	}
	if ($_POST['actualgrouphead'] == "STOCK" && $_POST['closingbalance'] != "") {
		$query .= ",closing_balance,closing_balance_type";
	}
	$query .= ") ";
	$query .= "values ('".trim(strtoupper($_POST['accountname']))."','";
	$query .= trim(strtoupper($_POST['actualgrouphead']))."','";
	$query .= trim(strtoupper($_POST['otherdetails']))."','";
	$query .= $listOfActualGroupHead[$act_group_head][0]."','";
	$query .= $listOfActualGroupHead[$act_group_head][1]."";
	if ($_POST['openingbalance'] != "") {
		$query .= "',".trim($_POST['openingbalance']).",'";
		$query .= trim($_POST['openingbalancetype']);
	}
	if ($_POST['actualgrouphead'] == "STOCK" && $_POST['closingbalance'] != "") {
		$query .= "',".trim($_POST['closingbalance']).",'";
		$query .= trim($_POST['closingbalancetype']);
	}
	$query .= "')";
	
	if (mysql_query($query)){
		$message = "Account Name &quot;".$_POST['accountname']."&quot; Successfully Created";
		return true;
	} else {
		$message =  "Failed to Create Account Name &quot;".$_POST['accountname']."&quot;";
		return false;
	} 
}

function checkAccountAlreadyExists($id = "") {	
	global $message;
	$query = "select acc_name from ".$_COOKIE['username']."_account_name where acc_name = '".trim(strtoupper($_POST['accountname']))."'";
	if ($id != "") { $query .= " and refid <> ".$id; } 
	$result = mysql_query($query);
	if (mysql_num_rows($result) > 0){
		$message = "Account Name already Exists";
		return false;			
	} else {
		return true;
	}
}

function updateDbAccountById($id)
{
	global $listOfActualGroupHead;
	$query = "update ".$_COOKIE['username']."_account_name set acc_name='".addslashes(trim(strtoupper($_POST['accountname'])))."', ";
	if ($_POST['actualgrouphead'] != "") { 
		$act_group_head = $_POST['actualgrouphead'];		
		$query .= " act_group_head='".trim(strtoupper($_POST['actualgrouphead']))."', ";
		$query .= " acc_head='".$listOfActualGroupHead[$act_group_head][0]."', ";
		$query .= " group_head='".$listOfActualGroupHead[$act_group_head][1]."', ";			
	}
	
	if ($_POST['openingbalance'] != "" && isset($_POST['openingbalancetype'])) {
		$query .= " opening_balance=".$_POST['openingbalance'].",";
		$query .= " opening_balance_type='".$_POST['openingbalancetype']."',";
	}
	
	if (($_POST['closingbalance'] != "") && ($_POST['actualgrouphead'] == "STOCK")) {
		$query .= " closing_balance=".$_POST['closingbalance'].",";
		$query .= " closing_balance_type='debit',";
	}
	$query .= "other_details='".addslashes(trim($_POST['otherdetails']))."' where refid=".$id;
	//echo $query;
	return mysql_query($query);
}

function retrieveAccountById($id) {
	$query = "select acc_name, act_group_head, other_details,opening_balance,opening_balance_type,closing_balance,closing_balance_type from ".$_COOKIE['username']."_account_name where refid=".$id;
	return mysql_query($query);	
}

function retrieveAccountNameById($id) {
	$query = "select acc_name from ".$_COOKIE['username']."_account_name where refid=".$id;
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
		return trim(strtoupper($row[0]));			
	}	
}

function retrieveAllAccounts() {
	global $whereCondition; // will be set search function.	
	global $message; 
	// how many rows to show per page
	$rowsPerPage = 10;
	// by default we show first page
	$pageNum = 1;
	// if $_GET['page'] defined, use it as page number
	if(isset($_GET['page']) && $_GET['page'] != "")
	{
		$pageNum = $_GET['page'];
	}
	// counting the offset
	$offset = ($pageNum - 1) * $rowsPerPage;	

	$query = "select refid,acc_name, act_group_head, opening_balance,opening_balance_type, closing_balance,other_details from ".$_COOKIE['username']."_account_name";
	if ($whereCondition != "") { // will be set by search function 
		$query .= $whereCondition;
	}
	$query .= " LIMIT ".$offset.",".$rowsPerPage;

	//echo $query;
	$result = mysql_query($query);	
	$rows = mysql_num_rows($result);
	if ($rows > 0) {
		while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
			echo "<tr>";
			echo "<td>".stripslashes($row[1])."</td>";
			echo "<td>".stripslashes($row[2])."</td>";
			echo "<td>".stripslashes($row[3])."</td>";
			echo "<td>".stripslashes($row[4])."</td>";
			echo "<td>".stripslashes($row[5])."</td>";
			echo "<td>".stripslashes($row[6])."</td>";
			echo "<td><a href=\"addAccount.php?operation=modify&id=".$row[0]."&page=".$_GET['page']."\">Modify</a></td>";
			echo "<td><a href=\"addAccount.php?operation=delete&id=".$row[0]."&page=".$_GET['page']."\">Delete</a></td>";			
			echo "</tr>";
		}
	} else {
		$message = "No Records Found";
	}
	// how many rows we have in database
	$query   = "SELECT COUNT(refid) AS numrows FROM ".$_COOKIE['username']."_account_name";
	if ($whereCondition != ""){
		$query .= $whereCondition;
	}	
	$result  = mysql_query($query) or die('Error, query failed');
	$row     = mysql_fetch_array($result, MYSQL_ASSOC);
	$numrows = $row['numrows'];
	// how many pages we have when using paging?
	$maxPage = ceil($numrows/$rowsPerPage);
	// print the link to access each page
	$self = $_SERVER['PHP_SELF'];
	$nav  = '';
	for ($page = 1; $page <= $maxPage; $page++) {
		if ($page == $pageNum){
			$nav .= " $page "; // no need to create a link to current page
		} else {
			$nav .= " <a href=\"$self?page=$page\">$page</a> ";
		}
	}
	echo $nav;
}

function addAccounts()
{
	global 	$modAccountname, $modActualGroupHead, $modOtherdetails, $modOpeningBalance, $modOpeningBalanceType; 
	global $message;
	$modAccountname  = addslashes(trim(strtoupper($_POST['accountname'])));
	$modActualGroupHead  = addslashes($_POST['actualgrouphead']);	
	$modOtherdetails = addslashes(trim($_POST['otherdetails']));	
	$modOpeningBalance = addslashes(trim($_POST['modOpeningBalance']));
	$modOpeningBalanceType = addslashes(trim($_POST['modOpeningBalanceType']));
	$modOpeningBalance = addslashes(trim($_POST['modClosingBalance']));
	$modOpeningBalanceType = addslashes(trim($_POST['modClosingBalanceType']));	
	
	if ($_POST['accountname'] == "") {
		$message =  "Account Name cannot be empty";
		return false;
	} else if ($_POST['actualgrouphead'] == "") {
		$message =  "Group Head cannot be empty";
		return false;
	} 
	
	if ($_POST['actualgrouphead'] == "") {
	
	}
	
	if (checkAccountAlreadyExists()) {
		if (insertDbAccount()){
			$modAccountname = ""; $modActualGroupHead = ""; $modOtherdetails = ""; 
			$modOpeningBalance = ""; $modOpeningBalanceType = ""; $modClosingBalance = ""; $modClosingBalanceType = "";
		}
	}
	/*
	$account_already_exists = false;	
	global $xml_file_name, $fileNameWithPath;	
	
	if (file_exists($xml_file_name)) {
		$xml = simplexml_load_file($xml_file_name);
	}

	foreach ( $xml->xpath('//account') as $account ) {			
		$accountname = strtolower((string) $account->accountname); 
		$id = ((integer) $account->id);
		if ( trim($accountname) == trim(strtolower($_POST['accountname'])) )	{
			echo "Account Name already Exists<br />";
			$account_already_exists = true;
		}
	}
	
	if ($account_already_exists == false) 
	{
		//echo "Inside the False Condition"; 
		$writeContent = "<account> \n";
		$id = $id +1;
		$writeContent .= "<id>".$id."</id> \n";
		$writeContent .= "<accountname>".$_POST['accountname']."</accountname> \n";
		$writeContent .= "<accounthead>".$_POST['accounthead']."</accounthead> \n";
		$writeContent .= "<grouphead>".$_POST['grouphead']."</grouphead> \n";
		$writeContent .= "<firstname></firstname> \n";
		$writeContent .= "<lastname></lastname> \n";
		$writeContent .= "<address></address>\n";
		$writeContent .= "<phoneno></phoneno> \n";
		$writeContent .= "<mobileno></mobileno> \n";
		$writeContent .= "</account> \n";

		$fd = fopen ($fileNameWithPath, "r"); 
		while (!feof ($fd)) {
			$buffer = fgets($fd, 4096);
			$lines[] = $buffer;
		}
		fclose ($fd);

		$fd = fopen ($fileNameWithPath,"w");
		foreach ($lines as $value) {
			if (trim($value) == "</accounts>") {
				fwrite($fd,$writeContent);
			}
			$value = trim($value)."\n";
			fwrite($fd,$value); 
		}
		fclose ($fd);
	}
	*/
}
?>
<html>
<head>
<title>Manage Accounts</title>
<script>
// Hold Element for document.getElementById;
function he(id,typeofsearch){
	document.getElementById(id).value = typeofsearch;
}



</script>
<link rel="stylesheet" type="text/css" href="lib.global.css"/>
</head>
<style>
body {color:#2F2F2F}
.fieldsetform {border:1px solid #B4B4B4;margin:0 0 15px;padding:15px;}
.rowform {padding: 8px 0px;}
.rowform label {float:left;font-weight:bold;text-align:right;width:175px;padding: 0px 5px;}
</style>
<body>





<body>
<div id="maincontainer">

<?php 
$headerTitle = $companyname;
include_once "header.php" ?>

<div id="contentwrapper">
	<div id="contentcolumn">
		<div class="innertube">
			<?php echo $message."<br />"; ?>
			<?php 
			if ( isset($_GET['operation']) && ($_GET['operation'] == "modify")){
				$saveLabel = "Modify Accounts";
			} else {
				$saveLabel =  "Add Accounts";
			}
			?>
			<form method="post" action="addAccount.php" name="search">
			<input type="hidden" id="searchby" name="searchby"></input>			
			<fieldset class="fieldsetform">
			<legend><?php echo $saveLabel; ?></legend>			 
				<div class="rowform">
					<label>Account Name</label>
					<input type="text" maxlength="50" name="accountname" id="accountname" value="<?php echo $modAccountname; ?>" maxlength="50"></input>
					<a href="javascript:he('searchby','accountname');document.search.submit();" ><img src="search_16x16.gif" alt="Search by Account Name" title="Search by Account Name"></a>
				</div>
			
				<?php
				$listOfActualGroupHeadKeys = array_keys($listOfActualGroupHead);							
				echo "<script language=\"javascript\">";
				echo "actualGroupHeadArrayKeys = new Array();";
				echo "actualGroupHeadArray = new Array();";
				echo "actualGroupHeadTypeArray = new Array();";
				for ($i = 0 ; $i < count($listOfActualGroupHeadKeys); $i++) {			
					echo "	actualGroupHeadArrayKeys.push('".$listOfActualGroupHeadKeys[$i]."');";
					echo "  actualGroupHeadArray.push('".$listOfActualGroupHead[$listOfActualGroupHeadKeys[$i]][0]."');";
					echo "  actualGroupHeadTypeArray.push('".$listOfActualGroupHead[$listOfActualGroupHeadKeys[$i]][1]."');";
				}
				echo "</script>\n";							
				?>
				<script language="javascript">
				function displayOpeningBalance()
				{				
					var actualgrouphead = document.getElementById("actualgrouphead").value;					
					//alert (actualgrouphead);
					
					
					/* The opening balance under for this accounts 
					 under this actual group head can only be debited.  */
					if (actualgrouphead == "STOCK" || actualgrouphead == "CASH" || actualgrouphead == "INVESTMENTS" || actualgrouphead == "FIXED ASSETS" || actualgrouphead == "OTHER ASSETS")
					{
						document.getElementById("opbcredit").className="hideRow";
					} else {
						document.getElementById("opbcredit").className="";
					}
					
					/* The opening balance under for this accounts 
					 under this actual group head can only be credited.  */					 
					if (actualgrouphead == "CAPITAL" || actualgrouphead == "RESERVES" || actualgrouphead == "TERM LOAN"	|| actualgrouphead == "DEFERRED LIABILITY")
					{
						document.getElementById("opbdebit").className="hideRow";
					} else {
						document.getElementById("opbdebit").className="";
					}
					
					
					if (actualgrouphead != ""){
						for (var i =0; i < actualGroupHeadArrayKeys.length; i++){
							if (actualGroupHeadArrayKeys[i] == actualgrouphead) {
								if  (actualGroupHeadArray[i] == "bs"){
									//alert ('inside true condition');
									document.getElementById('openingbalancetr').className="rowForm";									
									//alert (actualGroupHeadTypeArray[i]);
									if (actualGroupHeadTypeArray[i] == "asset") {
									//document.getElementById('openingbalancetype')[0].SelectedValue="debit";
									} else {
									//document.getElementById('openingbalancetype')[1].SelectedValue="credit";
									}
									return true;
								} 
							}
						} 
						document.getElementById('openingbalancetr').className="hideRow";
						//return false;
					} 
					
					
				}
				
				function displayClosingBalance()
				{
					var actualgrouphead = document.getElementById("actualgrouphead").value;							
					if (actualgrouphead == "STOCK"){
						document.getElementById('closingbalancetr').className="rowform";
					} else {
						document.getElementById('closingbalancetr').className="hideRow";									
					}
				}
				
				</script>
				<style>
					.hideRow {display:none;}					
				</style>

	
				
				<?php if ($disableActualGroupHead == false) { ?>
				<div class="rowform">
					<label>Group Head</label>
					<select name="actualgrouphead" id="actualgrouphead" onselect="javascript:displayOpeningBalance();displayClosingBalance();" onchange="javascript:displayOpeningBalance();displayClosingBalance();">
					<option></option>
						<?php						
							for ($i = 0; $i < count($listOfActualGroupHeadKeys); $i++) 
							{
								echo "<option"; 
								if ($modActualGroupHead == $listOfActualGroupHeadKeys[$i])
								{
									echo " selected=\"selected\""; 
								} 
								echo " value=\"".$listOfActualGroupHeadKeys[$i]."\">".$listOfActualGroupHeadKeys[$i]."</option>";
							}
						?>
					</select>
					<a href="javascript:he('searchby','actualgrouphead');document.search.submit();" ><img src="search_16x16.gif" /></a>
				</div>				
				<?php } ?>
				
				
				<?php 				
				if ($displayOpeningBalance == true) {
				?>
				<div class="rowform" id="openingbalancetr" name="openingbalancetr">
					<label>Opening Balance</label>
					<select name="openingbalancetype" id="openingbalancetype">
							<option id="opbdebit" value="debit" <?php if ($modOpeningBalanceType == "debit"){ echo "selected=selected"; } ?>>debit</option>
							<option id="opbcredit" value="credit" <?php if ($modOpeningBalanceType == "credit"){ echo "selected=selected"; } ?>>credit</option>
					</select>						
					<input type="text"  maxlength="15" name="openingbalance" id="openingbalance" value="<?php echo $modOpeningBalance; ?>"></input>
				</div>
				<?php } ?>
				
				
				<div class="rowform" id="closingbalancetr" name="closingbalancetr">
					<label>Closing Balance</label>
					<input type="text"  maxlength="15" name="closingbalance" id="closingbalance" value="<?php echo $modClosingBalance; ?>"></input>
					<sup>Applies only to stock</sup>
				</div>
				
				<div class="rowform">
					<label>Other Details</label>
					<textarea name="otherdetails" id="otherdetails" maxlength="500"><?php echo $modOtherdetails; ?></textarea>
					<a href="javascript:he('searchby','otherdetails');document.search.submit();" ><img src="search_16x16.gif"></a>
				</div>
	
				<div class="rowform">
					<label>&nbsp;</label>
					<input type="submit" value="<?php echo $saveLabel; ?>"></input>
				</div>			
			</fieldset>

			<?php 
			if (isset($_GET['operation']) && ($_GET['operation'] == "modify"))
			{ 
				echo "<input type=\"hidden\" name=\"modifysaveid\" value=\"".$_GET['id']."\">"; 
			}
			?>
			</form>

			<div style="display:table">
				<table cellspacing="3px" border="1">
					<tr style="background-color:#cccccc">
					<th>Account Name</th>
					<th>Group Head</th>
					<th>Opening Balance</th>										
					<th>Opening Balance Type</th>										
					<th>Closing Balance</th>										
					<th>Other Details</th>										
					<th>Modify</th>
					<th>Delete</th>
					</tr>
					<?php						
						retrieveAllAccounts();
					?>
				</table>
			</div>
		</div>
	</div>
</div>
<div id="leftcolumn">
	<div class="innertube_high">
		<b><a href="addAccount.php">Manage Account Details</a></b>
	</div>
	<div class="innertube">
		<b><a href="index.php">Transactions and Statements</a></b>
	</div>
</div>
<?php 
		include_once "footer.php";		
?>
</div> <!-- End of main container -->
</body>
</html>
Return current item: Simple Accounting System