<?php
/*--------------------------------------------------------------*/
/* Function for Ledger
/*--------------------------------------------------------------*/

//Profit and loss function



function get_ledgervaltest($id,$key,$type,$stdate='',$endate='',$brid=''){
    global $con;
    $ser='';    
    if($brid==''){
    $ibrid='';//$_SESSION['abrid'];    
    }else{
    $ibrid=$brid;    
    }
    if($ibrid=='A' || $ibrid==''){$ser='';}else{$ser="AND brid='$ibrid' ";}
        
    if($key=='D' && $type=='N'){
    $sql="SELECT COALESCE (SELECT SUM(debit) FROM tbl_ledgerdebit  WHERE id='$id' ".$ser." GROUP BY id),0) AS rdata";    
    }elseif($key=='D' && $type=='O'){
    $sql="SELECT COALESCE ((SELECT SUM(debit) FROM tbl_ledgerdebit  WHERE id='$id' AND apdate <'$stdate' ".$ser." GROUP BY id),0) AS rdata";
    }elseif($key=='D' && $type=='D'){
    $sql="SELECT COALESCE ((SELECT SUM(debit) FROM tbl_ledgerdebit  WHERE id='$id' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser." GROUP BY id),0) AS rdata";
    }elseif($key=='C' && $type=='N'){
    $sql="SELECT COALESCE ((SELECT SUM(credit) FROM tbl_ledgercredit  WHERE id='$id' ".$ser." GROUP BY id),0) AS rdata";    
    }elseif($key=='C' && $type=='O'){
    $sql="SELECT COALESCE ((SELECT SUM(credit) FROM tbl_ledgercredit  WHERE id='$id' AND apdate < '$stdate' ".$ser." GROUP BY id),0) AS rdata";
    }elseif($key=='C' && $type=='D'){
    $sql="SELECT COALESCE ((SELECT SUM(credit) FROM tbl_ledgercredit  WHERE id='$id' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser." GROUP BY id),0) AS rdata";   
}

    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return getfloatval($row['rdata']);    
}


function get_goupvaltest($id,$key,$type,$gtype,$stdate='',$endate='',$brid=''){
    global $con;
    $ser='';
    $sear='';    
    if($brid==''){
    $ibrid='';//$_SESSION['abrid'];    
    }else{
    $ibrid=$brid;    
    }
    if($ibrid=='A' || $ibrid==''){$sear='';}else{$sear="AND brid='$ibrid' ";}
        
    if($gtype=='G'){
    $ser= "grid='".$id."' $sear";    
    }elseif($gtype=='S'){
    $ser= "sgrid='".$id."' $sear";
    }elseif($gtype=='C'){
    $ser= "clsid='".$id."' $sear";    
    }    
    if($key=='D' && $type=='N'){
    $sql="SELECT COALESCE ((SELECT SUM(debit) FROM tbl_ledgerdebit  WHERE ".$ser."),0) AS rdata";    
    }elseif($key=='D' && $type=='O'){
    $sql="SELECT COALESCE ((SELECT SUM(debit) FROM tbl_ledgerdebit  WHERE ".$ser." AND apdate < '$stdate'),0) AS rdata";
    }elseif($key=='D' && $type=='D'){
    $sql="SELECT COALESCE ((SELECT SUM(debit)  FROM tbl_ledgerdebit  WHERE ".$ser." AND apdate BETWEEN '$stdate' AND '$endate'),0) AS rdata"; 
    }elseif($key=='C' && $type=='N'){
    $sql="SELECT COALESCE ((SELECT SUM(credit)  FROM tbl_ledgercredit WHERE ".$ser."),0) AS rdata";    
    }elseif($key=='C' && $type=='O'){
    $sql="SELECT COALESCE ((SELECT SUM(credit) FROM tbl_ledgercredit WHERE ".$ser." AND apdate < '$stdate'),0) AS rdata";
    }elseif($key=='C' && $type=='D'){
    $sql="SELECT COALESCE ((SELECT SUM(credit)  FROM tbl_ledgercredit  WHERE ".$ser." AND apdate BETWEEN '$stdate' AND '$endate'),0) AS rdata";   
    }
        
    $result=mysqli_query($con,$sql) or die(mysqli_error($con));    
    $row=mysqli_fetch_array($result);
    return getfloatval($row['rdata']);    
}
   







//$key D=debit, C=credit, $type O=Opening,D=Duration,N=Total
function get_ledgerval($id,$key,$type,$stdate='',$endate='',$brid=''){
global $con;
$ser='';    
if($brid==''){
$ibrid='';//$_SESSION['abrid'];    
}else{
$ibrid=$brid;    
}
if($ibrid=='A' || $ibrid==''){$ser='';}else{$ser="AND brid='$ibrid' ";}
    
if($key=='D' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' ".$ser."GROUP BY dty,did),0) AS rdata";    
}elseif($key=='D' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' AND apdate <'$stdate' ".$ser."GROUP BY dty,did),0) AS rdata";
}elseif($key=='D' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser."GROUP BY dty,did),0) AS rdata";
}elseif($key=='C' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' ".$ser."GROUP BY cty,cid),0) AS rdata";    
}elseif($key=='C' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' AND apdate < '$stdate' ".$ser."GROUP BY cty,cid),0) AS rdata";
}elseif($key=='C' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser."GROUP BY cty,cid),0) AS rdata";   
}
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return getfloatval($row['rdata']);    
}

//$key D=debit, C=credit, $type O=Opening,D=Duration,N=Total,$gtype  G=group, S=subgroup, C=Class
function get_goupval($id,$key,$type,$gtype,$stdate='',$endate='',$brid=''){
global $con;
$ser='';
$sear='';    
if($brid==''){
$ibrid='';//$_SESSION['abrid'];    
}else{
$ibrid=$brid;    
}
if($ibrid=='A' || $ibrid==''){$sear='';}else{$sear="AND brid='$ibrid' ";}
    
if($gtype=='G'){
$ser= "tbl_ledger.grid='".$id."' $sear";    
}elseif($gtype=='S'){
$ser= "tbl_ledger.sgrid='".$id."' $sear";
}elseif($gtype=='C'){
$ser= "tbl_ledger.clsid='".$id."' $sear";    
}    
if($key=='D' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser."),0) AS rdata";    
}elseif($key=='D' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser." AND apdate < '$stdate'),0) AS rdata";
}elseif($key=='D' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser." AND apdate BETWEEN '$stdate' AND '$endate'),0) AS rdata"; 
}elseif($key=='C' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser."),0) AS rdata";    
}elseif($key=='C' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser." AND apdate < '$stdate'),0) AS rdata";
}elseif($key=='C' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger LEFT JOIN tbl_ledger ON tbl_ledger.id=(CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci) WHERE ".$ser." AND apdate BETWEEN '$stdate' AND '$endate'),0) AS rdata";   
}
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return getfloatval($row['rdata']);    
}

function get_ledgerofval($id,$key,$type,$invno,$stdate='',$endate='',$brid=''){
global $con;
$ser='';    
if($brid==''){
$ibrid='';//$_SESSION['abrid'];    
}else{
$ibrid=$brid;    
}
if($ibrid=='A' || $ibrid==''){$ser='';}else{$ser="AND brid='$ibrid' ";}
    
if($key=='D' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' ".$ser."GROUP BY dty,did),0) AS rdata";    
}elseif($key=='D' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' AND apdate < '$stdate' ".$ser."GROUP BY dty,did),0) AS rdata";
}elseif($key=='D' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(dty,did) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser."GROUP BY dty,did),0) AS rdata";
}elseif($key=='C' && $type=='N'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' ".$ser."GROUP BY cty,cid),0) AS rdata";    
}elseif($key=='C' && $type=='O'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' AND apdate < '$stdate' ".$ser."GROUP BY cty,cid),0) AS rdata";
}elseif($key=='C' && $type=='D'){
$sql="SELECT COALESCE ((SELECT SUM(amo) FROM tbl_traledger WHERE (CONVERT(CONCAT(cty,cid) USING utf8) COLLATE utf8_general_ci)='$id' AND invno!='$invno' AND apdate BETWEEN '$stdate' AND '$endate' ".$ser."GROUP BY cty,cid),0) AS rdata";   
}
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return getfloatval($row['rdata']);    
}

/*--------------------------------------------------------------*/
/* Function for get product cost And price
/*--------------------------------------------------------------*/

function get_proval($pid,$key){
global $con;    
$sval=0;
if($key=='C'){
$sql="SELECT COALESCE ((SELECT cost FROM tbl_traproduct WHERE pid='$pid' AND mods='PU' ORDER BY pid DESC LIMIT 1),0) AS cost";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
if(floatval($row['cost'])>0){
$sval=$row['cost'];    
}else{
$sql="SELECT COALESCE ((SELECT cost FROM tbl_item WHERE id='$pid' LIMIT 1),0) AS cost";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
$sval=$row['cost'];    
}    
}elseif($key=='P'){
$sql="SELECT COALESCE ((SELECT price FROM tbl_traproduct WHERE pid='$pid' AND mods='PU' ORDER BY pid DESC LIMIT 1),0) AS price"; $result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
if(floatval($row['price'])>0){
$sval=$row['price'];    
}else{
$sql="SELECT COALESCE ((SELECT price FROM tbl_item WHERE id='$pid' LIMIT 1),0) AS price";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
$sval=$row['price'];    
}
}elseif($key=='W'){
$sql="SELECT COALESCE ((SELECT wday FROM tbl_traproduct WHERE pid='$pid' AND mods='PU' ORDER BY pid DESC LIMIT 1),0) AS wday";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
if(floatval($row['wday'])>0){
$sval=$row['wday'];    
}else{
$sql="SELECT COALESCE ((SELECT wday FROM tbl_item WHERE id='$pid' LIMIT 1),0) AS wday";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
$sval=$row['wday'];    
}    
}
return getfloatval($sval);    
}

function get_prostinfo($unqid,$key){
global $con;
$sql="SELECT ".$key." AS rdata FROM tbl_stock WHERE unqid='$unqid' LIMIT 1";
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}

/*--------------------------------------------------------------*/
/* Function for get product avarage cost And price
/*--------------------------------------------------------------*/

function get_proavgval($pid,$key){
global $con;
if($key=='C'){    
$sql="SELECT ROUND(SUM(tbl_traproduct.p_qty*tbl_stock.cost)/SUM(tbl_traproduct.p_qty),2) AS rdata FROM tbl_traproduct LEFT JOIN tbl_stock ON tbl_stock.unqid=tbl_traproduct.unqid WHERE tbl_traproduct.pid='$pid' AND tbl_traproduct.mods='PU'";
}else{
$sql="SELECT IFNULL(ROUND((IFNULL(SUM(subtot),0)/IFNULL(SUM(s_qty),0)),2),0) AS rdata FROM tbl_traproduct WHERE tbl_traproduct.pid='$pid' AND tbl_traproduct.mods='SE'";    
}
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}


/*--------------------------------------------------------------*/
/* Function for inventory report
/*--------------------------------------------------------------*/

//PU=Purchase Quantity,PC=Purchase Received,PR=Purchase Return,ER=External Received,ES=External Send,O=Opning, D=duration, H=Hole, C=Current Date

function get_pdinfo($pid,$mods,$key,$brid='',$fadte='',$tdate=''){
global $con;
$sear='';
if($mods=='O'){
$sear=" AND apdate < '$fadte'";    
}elseif($mods=='C'){
$sear=" AND apdate <= '$fadte'";    
}elseif($mods=='D'){    
$sear=" AND apdate BETWEEN '$fadte' AND '$tdate'";    
}else{
$sear='';    
}

if($brid!=''){
if($sear!=''){
$sear.= " AND brid='$brid'";    
}else{
$sear= " AND brid='$brid'";    
}        
}    
    
if($key=='PC'){    
$sql="SELECT COALESCE((SELECT SUM(p_in) FROM tbl_traproduct WHERE mods IN ('PU','RC') AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='PU'){
$sql="SELECT COALESCE((SELECT SUM(p_qty) FROM tbl_traproduct WHERE mods='PU' AND pid='$pid'$sear),0) AS rdata";    
}elseif($key=='PR'){    
$sql="SELECT COALESCE((SELECT SUM(p_out) FROM tbl_traproduct WHERE mods='PR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='ER'){
$sql="SELECT COALESCE((SELECT SUM(p_in) FROM tbl_traproduct WHERE mods='TR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='ES'){
$sql="SELECT COALESCE((SELECT SUM(p_out) FROM tbl_traproduct WHERE mods='TR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='SO'){
$sql="SELECT COALESCE((SELECT SUM(s_qty) FROM tbl_traproduct WHERE mods='SE' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='DL'){
$sql="SELECT COALESCE((SELECT SUM(p_out) FROM tbl_traproduct WHERE mods IN ('SE','DL') AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='SR'){
$sql="SELECT COALESCE((SELECT SUM(p_in) FROM tbl_traproduct WHERE mods='SR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='WO'){
$sql="SELECT COALESCE((SELECT SUM(p_out) FROM tbl_traproduct WHERE mods='WR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='WI'){
$sql="SELECT COALESCE((SELECT SUM(p_in) FROM tbl_traproduct WHERE mods='WR' AND pid='$pid'$sear),0) AS rdata";
}elseif($key=='DS'){
$sql="SELECT COALESCE((SELECT SUM(p_out) FROM tbl_traproduct WHERE mods='DS' AND pid='$pid'$sear),0) AS rdata";    
}
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}

/*--------------------------------------------------------------*/
/* Function for item prfit and loss
/*--------------------------------------------------------------*/

function get_pidvalinv($invno,$pid,$key){
global $con;


$sql="SELECT COALESCE((SELECT SUM(".$key.") FROM (SELECT pid,colid,sizid,ROUND((tbl_traproduct.cost*tbl_traproduct.s_qty),2) AS cost,(tbl_traproduct.price*tbl_traproduct.s_qty) AS mrp,tbl_traproduct.subtot,(tbl_traproduct.disamo+((tbl_sales.disamo/tbl_sales.subtot)*tbl_traproduct.subtot)) AS fdisamo,ROUND(((totdis/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS disamo,ROUND(((vatamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS vatamo,ROUND(((aitamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS aitamo,ROUND(((otamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS otamo,ROUND(((freight/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS freight,ROUND(((less/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS less FROM tbl_traproduct LEFT JOIN tbl_item ON tbl_item.id=tbl_traproduct.pid LEFT JOIN tbl_sales ON tbl_sales.invno=tbl_traproduct.invno WHERE tbl_sales.invno='$invno') AS res WHERE pid='$pid' GROUP BY pid),0) AS rdata";    
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}

function get_pidvalinvre($invno,$pid,$key){
    global $con;
    
    
    $sql="SELECT COALESCE((SELECT SUM(".$key.") FROM (SELECT pid,colid,sizid,ROUND((tbl_traproduct.cost*tbl_traproduct.p_in),2) AS cost,(tbl_traproduct.price*tbl_traproduct.p_in) AS mrp,tbl_traproduct.subtot,(tbl_traproduct.disamo+((tbl_sales.disamo/tbl_sales.subtot)*tbl_traproduct.subtot)) AS fdisamo,ROUND(((totdis/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS disamo,ROUND(((vatamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS vatamo,ROUND(((aitamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS aitamo,ROUND(((otamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS otamo,ROUND(((freight/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS freight,ROUND(((less/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS less FROM tbl_traproduct LEFT JOIN tbl_item ON tbl_item.id=tbl_traproduct.pid LEFT JOIN tbl_sales ON tbl_sales.invno=tbl_traproduct.refinv WHERE tbl_sales.invno='$invno') AS res WHERE pid='$pid' GROUP BY pid),0) AS rdata";    
        
    $result=mysqli_query($con,$sql) or die(mysqli_error($con));    
    $row=mysqli_fetch_array($result);
    return $row['rdata'];    
    }

function get_pdvaluinfo($pid,$mods,$key,$type,$brid='',$fadte='',$tdate=''){
global $con;
$sear='';
if($mods=='O'){
$sear=" tbl_traproduct.apdate < '$fadte'";    
}elseif($mods=='D'){
$sear=" tbl_traproduct.apdate BETWEEN '$fadte' AND '$tdate'";    
}else{
$sear='';    
}

if($brid!=''){
if($sear!=''){
$sear.= " AND tbl_traproduct.brid='$brid'";    
}else{
$sear= "tbl_traproduct.brid='$brid'";    
}        
}

if($type=='SE'){
if($sear!=''){
$isear="AND tbl_traproduct.mods='SE'";    
}else{
$isear="tbl_traproduct.mods='SE'";    
}        
$sql="SELECT COALESCE((SELECT SUM(".$key.") FROM (SELECT pid,colid,sizid,ROUND((tbl_traproduct.cost*tbl_traproduct.s_qty),2) AS cost,(tbl_traproduct.price*tbl_traproduct.s_qty) AS mrp,tbl_traproduct.disamo AS itmdis,tbl_traproduct.subtot,(tbl_traproduct.disamo+((tbl_sales.disamo/tbl_sales.subtot)*tbl_traproduct.subtot)) AS fdisamo,ROUND(((tbl_sales.disamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS disamo,ROUND(((vatamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS vatamo,ROUND(((aitamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS aitamo,ROUND(((otamo/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS otamo,ROUND(((freight/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS freight,ROUND(((less/tbl_sales.subtot)*tbl_traproduct.subtot),2) AS less FROM tbl_traproduct LEFT JOIN tbl_item ON tbl_item.id=tbl_traproduct.pid LEFT JOIN tbl_sales ON tbl_sales.invno=tbl_traproduct.invno WHERE $sear $isear) AS res WHERE pid='$pid' GROUP BY pid),0) AS rdata";    
}else{
if($sear!=''){
$isear="AND tbl_traproduct.mods='PU'";    
}else{
$isear="tbl_traproduct.mods='PU'";    
}    
$sql="SELECT COALESCE((SELECT SUM(".$key.") FROM (SELECT pid,colid,sizid,(tbl_traproduct.cost*tbl_traproduct.p_qty) AS mrp,tbl_traproduct.disamo AS itmdis,tbl_traproduct.subtot,ROUND(((totdis/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS disamo,ROUND(((vatamo/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS vatamo,ROUND(((tbl_purchase.taxamo/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS taxamo,ROUND(((tbl_purchase.disamo/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS otamo,ROUND(((spmoney/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS spmoney,ROUND(((freight/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS freight,ROUND(((less/tbl_purchase.subtot)*tbl_traproduct.subtot),2) AS less FROM tbl_traproduct LEFT JOIN tbl_item ON tbl_item.id=tbl_traproduct.pid LEFT JOIN tbl_purchase ON tbl_purchase.invno=tbl_traproduct.invno WHERE $sear $isear) AS res WHERE pid='$pid' GROUP BY pid),0) AS rdata";    
}
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}
/*--------------------------------------------------------------*/
/* Function for customer supplier balance
/*--------------------------------------------------------------*/
function get_clientbal($type,$id,$key){
global $con;
    
if($type=='SU'){
$sty=",'CLA'";
$wty="";    
}else{
$wty=",'CLA'";    
$sty="";    
}

$sql="SELECT COALESCE ((SELECT ".$key." FROM (SELECT SUM(IF(invty IN ('PAV','PRE','SEL','SER','PDR','BSC'".$sty."),amount,(debit+jdebit))) AS debit,SUM(IF(invty IN ('PUR','REV','SRE'".$wty."),amount,(credit+jcredit))) AS credit,(SUM(IF(invty IN ('PAV','PRE','SEL','SER','PDR','BSC'".$sty."),amount,debit))-SUM(IF(invty IN ('PUR','REV','SRE'".$wty."),amount,credit))) AS balance FROM (SELECT SUBSTRING(invno,1,3) AS invty,ROUND(ABS(IF(SUBSTRING(invno,1,3) IN ('REV','PAV'),SUM(debit-credit),SUM(IF(type NOT IN('BA','MO') AND seid NOT IN ('LE2'),debit-credit,0)))),2) AS amount,ROUND(SUM(IF(((type='LE' AND (CONVERT(opid USING utf8) COLLATE utf8_general_ci)='2') OR type IN ('BA','MO')),debit,0)),2) AS debit,ROUND(SUM(IF(SUBSTRING(invno,1,3)='JOU' AND debit>0,debit,0)),2) AS jdebit,ROUND(SUM(IF(((type='LE' AND (CONVERT(opid USING utf8) COLLATE utf8_general_ci)='2') OR type IN ('BA','MO')),credit,0)),2) AS credit,ROUND(SUM(IF(SUBSTRING(invno,1,3)='JOU' AND credit>0,credit,0)),2) AS jcredit FROM (SELECT id,(CASE WHEN dty='$type' THEN did WHEN cty='$type' THEN cid END) AS sid,(CASE WHEN dty='$type' THEN cty WHEN cty='$type' THEN dty END) AS type,(CASE WHEN dty!='$type' THEN did WHEN cty!='$type' THEN cid END)  AS opid,(CASE WHEN dty!='$type' THEN CONCAT(dty,did) WHEN cty!='$type' THEN CONCAT(cty,cid) END)  AS seid,IF(dty='$type',amo,0) AS debit,IF(cty='$type',amo,0) AS credit,invno FROM tbl_traledger WHERE (dty='$type' AND (CONVERT(did USING utf8) COLLATE utf8_general_ci)='$id') OR (cty='$type' AND (CONVERT(cid USING utf8) COLLATE utf8_general_ci)='$id')) AS curep GROUP BY SUBSTRING(invno,1,3)) AS res) AS sdata),0) AS rdata";    
    
$result=mysqli_query($con,$sql) or die(mysqli_error($con));    
$row=mysqli_fetch_array($result);
return $row['rdata'];    
}
?>