 <?php 
 function GetLederNameByAccountId($lid)
 {
  global $db;
  $GetGT=DB_query("select AccountsID , CompanyId , AccountName  from accountledger  where AccountsID='".$lid."' and CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
	$RowGT=DB_fetch_array($GetGT);
 	return(str_replace("'","\'",$RowGT['AccountName']));
 }
 function exciseTax($act)
 {
	global $db;
	$p=array();
	 
	$query=DB_query("select ledger  from excise_tax    where ledger='".$act."' ",$db);
	$n=DB_num_rows($query);
	return($n);
}  
	  
	  
	  
 function invoice_ExciseTotalByLedger($act,$branchid='')
 {
 global $db;
 if($branchid!=''){
   	$BranchID1=SeperateByCommes($branchid);
  	$GetGT_invoice=DB_query("select sum(excise_duty)   as total  from invoice where excise_legder='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchID1)",$db);
	}
 else
    	$GetGT_invoice=DB_query("select sum(excise_duty)   as total  from invoice where excise_legder='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
	 
	if($RowGT_invoice['total']>0)
 	return($RowGT_invoice['total']);
	else
	return(0);
}  


function invoice_edu_cess_TotalByLedger($act,$branchid='')
 {
 global $db;
  
  if($branchid!=''){
  	$BranchID1=SeperateByCommes($branchid);
 	$GetGT_invoice=DB_query("select sum(edu_cess)   as total  from invoice where edu_cess_ledger='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchID1)",$db);
	}
 else
     	$GetGT_invoice=DB_query("select sum(edu_cess)   as total  from invoice where edu_cess_ledger='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
	 
	if($RowGT_invoice['total']>0)
 	return($RowGT_invoice['total']);
	else
	return(0);
}  

function invoice_she_cess_TotalByLedger($act,$branchid='')
 {
 global $db;
   if($branchid!='')
   {
    $BranchID1=SeperateByCommes($branchid);		 
  	$GetGT_invoice=DB_query("select  sum(she_cess)   as total  from invoice where she_cess_ledger='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchID1)",$db);
	}
 else{
   	$GetGT_invoice=DB_query("select  sum(she_cess) as total  from invoice where she_cess_ledger='".$act."' and  CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
	}
	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
 	if($RowGT_invoice['total']>0)
 	return($RowGT_invoice['total']);
	else
	return(0);
}  
 	 
	 
function SalesTaxByLedger($act,$BranchID='')
 {
 
   global $db;
 if($BranchID!='')
 {
 	$BranchID1=SeperateByCommes($BranchID);
	//echo "select  sum(vat_cst*total_taxable *.01) as amt from invoice where vat_cst_ledger='".$act."' and CompanyId='".$_SESSION['ACGCompanyId']."' and   branch_name in($BranchID1)  ";
       $GetGT_invoice=DB_query("select  sum(vat_cst*total_taxable *.01) as amt from invoice where vat_cst_ledger='".$act."' and CompanyId='".$_SESSION['ACGCompanyId']."' and   branch_name in($BranchID1)  ",$db);

 }
 else{
     $GetGT_invoice=DB_query("select  sum(vat_cst*total_taxable *.01) as amt from invoice where vat_cst_ledger='".$act."' and CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
	 } 
	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
  	if($RowGT_invoice['amt']>0)
	{
 
  	$amt= $RowGT_invoice['amt'] ;
 	return($amt);
	}
	else
	return(0);
}  
	  
 function invoiceTotalByLedger($lid,$CBranch='')
 {    global $db;
  	 	$GetGT=DB_query("  select AccountsID , CompanyId , AccountName  from accountledger  where AccountsID='".$lid."' and CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
		$RowGT=DB_fetch_array($GetGT);
	
	
	if($CBranch!='')	
	{
 	
	$branch_name=SeperateByCommes($CBranch);
 
   $GetGT_invoice=DB_query("select sum(value_of_goods) as total  from invoice where (ledger_id='".str_replace("'","\'",$RowGT['AccountName'])."'or ledger_id='".$lid."')  and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in ($branch_name)",$db);

	}else{
 	   $GetGT_invoice=DB_query("select sum(value_of_goods) as total  from invoice where (ledger_id='".str_replace("'","\'",$RowGT['AccountName'])."'or ledger_id='".$lid."') and  CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
	   }
  	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
	if($RowGT_invoice['total']>0)
 	return($RowGT_invoice['total']);
	else
	return(0);
	
 }
 function invoiceTotalByClient($lid,$BranchID)
{
global $db;
if($BranchID!=''){
 $BranchID1=SeperateByCommes($BranchID);
 $GetGT_invoice=DB_query("select sum(grand_total) as total  from invoice A ,clientmaster B where B.ledger_id='".$lid."' and A.client_id=B.id  and A.CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchID1)",$db);
 }
 else
 $GetGT_invoice=DB_query("select sum(grand_total) as total  from invoice A ,clientmaster B where B.ledger_id='".$lid."' and A.client_id=B.id  and A.CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
 
	$RowGT_invoice=DB_fetch_array($GetGT_invoice);
 	if($RowGT_invoice['total']>0)
 	return($RowGT_invoice['total']);
	else
	return(0);
}
 
function CountJournelVoucher_Enter($ledgerid)
{ global $db;
$t=0;

 $Get=DB_query("select sum(debit) as GTDebit  , sum(credit) as GTCredit from journalvoucher where ledger_id='".$ledgerid."' and CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
  	 $Rowc=DB_fetch_array($Get) ; 
 		$arr[]= $Rowc['GTDebit'];
		$arr[]= $Rowc['GTCredit'];
 		 return($arr);
 }
 
 
 
 function SumJV_ByTrans($ledgerid,$CBranch='' )
{
global $db;
$t=0;
if($CBranch!='')
{
 $CBranch1=SeperateByCommes($CBranch);
  $Get=DB_query("select debit  as GTDebit, credit  as GTCredit ,branch from journalvoucher where ledger_id='".$ledgerid."'  and CompanyId='".$_SESSION['ACGCompanyId']."' and branch in($CBranch1)  ",$db);
}
else
{
  $Get=DB_query("select debit  as GTDebit, credit  as GTCredit,branch  from journalvoucher where ledger_id='".$ledgerid."'  and CompanyId='".$_SESSION['ACGCompanyId']."'  ",$db);
}  
	if(DB_num_rows($Get)>0)
	{
			while($Rowc=DB_fetch_array($Get))
			{ 	
				$arr[]= array($Rowc['GTDebit'],$Rowc['GTCredit']);
			}
			 return($arr);
	}		 
 }




function CountCashBankPayment($ledgerid,$CBranch)
{ global $db;
$t=0;
 $i=0;
 if($CBranch!='')
 {
$CBranch1=SeperateByCommes($CBranch);
$Get=DB_query("select (C2.debit) as GTDebit,(C2.credit) as GTCredit , C1.PreFix from cashbankvoucher_master C1 ,cashbankvoucher  C2 where C1.voucherMasterId=C2.TransId and  C2.ledger_id='".$ledgerid."'  and C1.CompanyId='".$_SESSION['ACGCompanyId']."' and BranchID in($CBranch1)",$db);
 }
 else{
  $Get=DB_query("select (C2.debit) as GTDebit  , (C2.credit) as GTCredit , C1.PreFix from cashbankvoucher_master C1 ,cashbankvoucher  C2 where C1.voucherMasterId=C2.TransId and  C2.ledger_id='".$ledgerid."'  and C1.CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
 } 
  	 	while($Rowc=DB_fetch_array($Get))
		{ 	
			$arr[]= array($Rowc['GTDebit'],$Rowc['GTCredit']);
		}
 		 return($arr);
 }
 


function CountCashBankPaymentByPrefix($CaseBankId,$BranchID)
{ global $db;
$t=0;
if($BranchID!=''){
 
	$BranchID1=SeperateByCommes($BranchID);
 
   $Get=DB_query("select C1.dr_cr,C1.PreFix,(C1.debit) as GTDebit  , (C1.credit) as GTCredit ,C1.CompanyId,C1.BranchID  from cashbankvoucher_master C1 ,cashbankvoucher  C2 where C1.voucherMasterId=C2.TransId and  C1.CaseBankId='".$CaseBankId."' and C1.CompanyId='".$_SESSION['ACGCompanyId']."' and C1.BranchID in($BranchID1) ",$db);
   }
   else
   $Get=DB_query("select C1.dr_cr,C1.PreFix,(C1.debit) as GTDebit  , (C1.credit) as GTCredit ,C1.CompanyId,C1.BranchID from cashbankvoucher_master C1 ,cashbankvoucher  C2 where C1.voucherMasterId=C2.TransId and  C1.CaseBankId='".$CaseBankId."' and C1.CompanyId='".$_SESSION['ACGCompanyId']."'  ",$db);
	if( DB_num_rows($Get)>0)
	{
		 while($Rowc=DB_fetch_array($Get))
			{ 	
				$arr[]= array($Rowc['dr_cr'],$Rowc['PreFix'],$Rowc['GTDebit'],$Rowc['GTCredit']);
			}
			
			 return($arr);
	}		 
 }


function GetClientLedgerID_ByCRM($lid,$CBranch='')
{
 
 global $db;
 
 $BranchId= SeperateByCommes($CBranch);
 $GetGT=DB_query("  select AccountsID , CompanyId , AccountName  from accountledger  where AccountsID='".$lid."'     ",$db);
$RowGT=DB_fetch_array($GetGT);
 
$SQL_led=DB_query("SELECT  * from clientmaster  A  Where    A.CompanyId='".$_SESSION['ACGCompanyId']."' and A.ledger_id='".$RowGT['AccountsID']."' order by A.c_name"  ,$db);
$clietids=array();
  while($rowled=DB_fetch_array($SQL_led))//$SQL_led
 {
   $clietids[]= $rowled['id'] ;
 
 }
	if(count($clietids)>0)
	{
		$cval=implode(",",$clietids);
		//echo "select sum(grand_total) as total  from invoice where client_id in($cval)  and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchId) ";
 		$GetGT_invoice=DB_query("select sum(grand_total) as total  from invoice where client_id in($cval)  and  CompanyId='".$_SESSION['ACGCompanyId']."' and branch_name in($BranchId) ",$db);
		$RowGT_invoice=DB_fetch_array($GetGT_invoice);
		return($RowGT_invoice['total']);
	}
 }
 
function GetClientId_ByCRM($lid)
{
 global $db;
$GetGT=DB_query("select AccountsID , CompanyId , AccountName  from accountledger  where AccountsID='".$lid."' and CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$RowGT=DB_fetch_array($GetGT);

$SQL_led=DB_query("SELECT    id  from clientmaster  A  Where    A.CompanyId='".$_SESSION['ACGCompanyId']."' and A.ledger_id='".$RowGT['AccountsID']."' order by A.c_name"  ,$db);

 $clietids=array();
 while($rowled=DB_fetch_array($SQL_led))
 {
   $clietids[]= $rowled['id'] ;
 }
 if(count($clietids)>0)
{
    $cval=implode(",",$clietids);
}
 return ($cval);
}

//****************************************** 

function invoice_salesorder()
{
global $db;
    $GetGT_invoice=DB_query("select crm_sales_order  from invoice   where CompanyId='".$_SESSION['ACGCompanyId']."' ",$db);
 	$salesorder=array();
  	while($row=DB_fetch_array($GetGT_invoice))
	{
	    $salesorder[]=$row['crm_sales_order'];
	}
	if(count($salesorder)>0)
     $cval=implode(",",$salesorder);
	 
 return ($cval);
}

 
function GetCashBank_amount($GetLedgerName,$BranchId='')
{
global $db;
 
if($BranchId!='')
{
  $branchid=SeperateByCommes($BranchId);
 
$q1=DB_query("select (A.client_id) from cashbankvoucher_master A,cashbankvoucher B   where  B.ledger_id='".$GetLedgerName."' and A.voucherMasterId=B.TransId and A.BranchID in($branchid)",$db); 
}
else{
$q1=DB_query("select (A.client_id) from cashbankvoucher_master A,cashbankvoucher B   where  B.ledger_id='".$GetLedgerName."'  and A.voucherMasterId=B.TransId",$db); 

}
while( $RVPrefix=DB_fetch_array($q1))
{
	 $ViewClients= $RVPrefix['client_id'];
	//echo "<br>*********<br>";
if($BranchId!='')
{
$branchid=SeperateByCommes($BranchId);
$GetRecord2 = "Select  sum(B.Amount) as amt from cashbankvoucher_master A,cashbankvoucher B where B.TransId=A.voucherMasterId and   A.client_id='".$ViewClients."'  and A.BranchID in($branchid) group by A.client_id  ";
}else{
 	  $GetRecord2 = "Select  sum(B.Amount) as amt from cashbankvoucher_master A,cashbankvoucher B where B.TransId=A.voucherMasterId and   A.client_id='".$ViewClients."' group by A.client_id  ";
}	  
	  $ResultData2 = DB_query($GetRecord2,$db);
	  $RVPrefix=DB_fetch_array($ResultData2);
	  return($RVPrefix['amt']);
		
 }

}

function AccountGroupMaster_Parent($mainid)
{
global $db;
static $rx=0;
 $q1=DB_query("select * from accountgroupmaster where GroupId='".$mainid."'   and CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
 while( $RVPrefix=DB_fetch_array($q1))
 {
 	if($RVPrefix['MainGroupId']==0)
	{
	    $rx=$RVPrefix['GroupId'];
 	   //break;
	}
	  AccountGroupMaster_Parent($RVPrefix['MainGroupId']);
  }
    return ($rx);
}


//*****************************************************
function TrialBalance_Amount($AID,$CBranch)
{
$GTDebit=array();
$GTCredit=array();
$AccountsID=explode(',',$AID);
$t=0;$t1=0;
	for($i=0;$i<count($AccountsID);$i++)
	{
  	 $GetLedgerName=GetLederNameByAccountId($AccountsID[$i]);					 
 							$dr1=array();$cr1=array();
							$JvEntry= SumJV_ByTrans($AccountsID[$i],$CBranch);
							for($kp=0;$kp<count($JvEntry);$kp++)
							  {
								$split=$JvEntry[$kp];
								 if($split[0]==0.00)
								 {
									$cr_jv=$split[1];
								 }
								 else{
									$dr_jv=$split[0];
								 }
							  }
						   $CashBankPayment= CountCashBankPayment($AccountsID[$i]);
						   for($kp=0;$kp<count($CashBankPayment);$kp++)
							  {
								$split=$CashBankPayment[$kp];
								 
								 if($split[0]==0.00)
								 {
									$cr=$split[1];
									 
								 }
								 else{
									$dr=$split[0];
								 
								 }
							  }
							  
							 $voucherdata= CountCashBankPaymentByPrefix($AccountsID[$i],$CBranch);
							  for($kp=0;$kp<count($voucherdata);$kp++)
							  {
								$split=$voucherdata[$kp];
								 if($split[0]=='Cr')
								 {
									$cr1[]=$split[3];
								 
								 }
								  if($split[0]=='Dr'){
								   $dr1[]=$split[2];
								 
								  }
							  }
							 
							$klp=exciseTax($GetLedgerName);	
							$Sales_TaxByLedger=SalesTaxByLedger($GetLedgerName,$CBranch);
						   if( $klp>0)					  
						   {
								$exciseTax=0;
								$exciseval=invoice_ExciseTotalByLedger($GetLedgerName,$CBranch);
								$eduval=invoice_edu_cess_TotalByLedger($GetLedgerName,$CBranch);
								$she_cessval=invoice_she_cess_TotalByLedger($GetLedgerName,$CBranch);
								$exciseTax=$exciseval+$eduval+$she_cessval ;
						   }
						  
						  //**************************************************************//
	 
						 $cashBankvoucher_amount=GetCashBank_amount($GetLedgerName,$CBranch);
						 if($cashBankvoucher_amount=='')
						 {
						 $cashBankvoucher_amount=0;
						 }
			 					   
			$GET_CRM_INVOICE= GetClientLedgerID_ByCRM($AccountsID[$i],$CBranch);
		  	$t= array_sum($dr1)+$dr+$dr_jv+invoiceTotalByClient($AccountsID[$i],$CBranch)+$GET_CRM_INVOICE;
 			 $GTDebit[]=$t;
				 
							  if( $klp>0){
	
							$t1= array_sum($cr1)+$cr_jv+$cr+invoiceTotalByLedger($AccountsID[$i],$CBranch)+$exciseTax+$cashBankvoucher_amount ;
							}else
							{
							  $t1=  array_sum($cr1)+$cr_jv+$cr+invoiceTotalByLedger($AccountsID[$i],$CBranch)+$cashBankvoucher_amount;
							}
							if($Sales_TaxByLedger!=0){
							$t1+=$Sales_TaxByLedger;
							}
 						 $GTCredit[]=$t1;
	}	
 	if(count($GTDebit)>0)
	 $p[]=array_sum($GTDebit);
	if(count($GTCredit)>0)
	 $p[]=array_sum($GTCredit);
	return($p) ;
}

//*****************************************************
function SeperateByCommes($str)
{
   
 $x=explode(",",$str);
  if(count($x)>1)
   {
	   $val="";
	   for($i=0;$i<count($x);$i++)
	   {
		  $val.="'".$x[$i]."',";
	   }
	   
	   $val1=substr($val,0,-1);
   }
   else{
    $val1="'".$str."'";
   }
  
   return($val1);
   
}
function Brought_OpeningBalance($underbal,$finyear,$branch_name)
{
global $db;
$count_ledger = "select * from accountledger where UnderGroup  =".$underbal." and CompanyId='".$_SESSION['ACGCompanyId']."' ";					 
 $rs_ledger	= DB_query($count_ledger,$db);
if(DB_num_rows($rs_ledger)>0)
{
 $t=array();
 $t1=array();
 while($RowOBLed=DB_fetch_array($rs_ledger))
 {
	$count_ledger =DB_query("select * from brought_opening_amount where ledger_id='".$RowOBLed['AccountsID']."' and   CompanyId='".$_SESSION['ACGCompanyId']."' and finyear='".$finyear."'  and BranchId='".$branch_name."'",$db);		
	$amountval=array();
	while($RowOB=DB_fetch_array($count_ledger))
	{
	  if($RowOB['amounttype']=='DR'){
	  $t[]=$RowOB['Amount'];
	  $amountval[0]=$RowOB['Amount'];
	  }
	  elseif($RowOB['amounttype']=='CR'){
	   $t1[]=$RowOB['Amount'];
	  $amountval[1]=$RowOB['Amount'];
	  }
	}  
  
 }
 $f=array();
    $CDR=array_sum($t);
   $CCR=array_sum($t1);
  if($CCR>$CDR){
    $f[]=$CCR-$CDR;  
	$f[]='CR';
  }
  else{
   $f[]=$CDR- $CCR; 
   $f[]='DR';
   }
   
   return($f); 
}   
  }

 ?>