<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='index.php'; </script>";
 return;
}
include("includes/top.php");
include("opendb.php");








include("test.php");
$tblmis="misbr".$brcode.$user;
mysqli_query($db,"drop table $tblmis");
//print $tblmis;
 mysqli_query($db,"CREATE TABLE $tblmis(brcode varchar(20),partic varchar(100),jc date,cs date,pur date,ch date,grn date,cr date,cp date,br date,bp date, wty date,coup date,wtyp date,coupp date,jv date)");
//$sql1=mysqli_query($db,"select distinct GRNNo,InvoiceNo,GRNDate from eicherpurchase where ac_postdt IS NULL and brcode='$brcode' order by GRNNo")or die('Led Post Err3'.mysqli_error($db));
//$dt_tm=date("Y-m-d");
mysqli_query($db,"insert into $tblmis (brcode,jc,partic) select brcode,max(date),'1' from jobcard group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,cs,partic) select brcode,max(invdate),'1' from spareinvoice group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,grn,partic) select brcode,max(grndate) ,'1' from transferin group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,ch,partic) select brcode,max(invoicedate) ,'1' from transferout group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,pur,partic) select brcode,max(entrydate) ,'1' from sparepurchase group by brcode ");
/*
mysqli_query($db,"insert into $tblmis (brcode,cr) select brcode,max(date) from cshrec group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,cp) select brcode,max(date) from cshpay group by brcode");
mysqli_query($db,"insert into $tblmis (brcode,br) select brcode,max(date) from bnkrec group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,bp) select brcode,max(date) from bnkpay group by brcode");
*/
mysqli_query($db,"insert into $tblmis (brcode,cr,partic) select brcode,max(date),'1' from journal2 where partic='CASH IN HAND' and j_type='CR' group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,cp,partic) select brcode,max(date) ,'1' from journal2 where partic='CASH IN HAND' and j_type='CP' group by brcode");
mysqli_query($db,"insert into $tblmis (brcode,br,partic) select brcode,max(date),partic from journal2 where  j_type='BR' group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,bp,partic) select brcode,max(date),partic from journal2 where  j_type='BP' group by brcode");

mysqli_query($db,"insert into $tblmis (brcode,wty,partic) select brcode,max(invdate),'1' from warspareinvoice group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,coup,partic) select brcode,max(invdate) ,'1' from couponinvoice group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,wtyp,partic) select brcode,max(invdate),'1' from warspareinvoicepass where  invtype!='ZFRE' group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,coupp,partic) select brcode,max(invdate) ,'1' from warspareinvoicepass where invtype='ZFRE' group by brcode ");
mysqli_query($db,"insert into $tblmis (brcode,jv,partic) select brcode,max(date),'1' from journal2 where  j_type='JV' group by brcode");

?>

<table border=1>
<th>Branch<th>Jobcard<th>Counter Sale<th>Purchase<th>Trans IN<th>Trans OUT <th>Cash Rec<th>Cash Pay<th>Bank Rec<th>Bank Pay<th>Warr item<th>Warr Pass<th>coupon item<th>Coupon Pass<th>Journal</th>
<?
function getdays($date5)
{
$dt2=$date5;
$sq="select to_days(curdate())-to_days('$dt2')";
$dtre=mysqli_query($db,$sq)or die(mysqli_error($db));
$dtrow=mysqli_fetch_row($dtre);
return $dtrow[0];

}

function getbrname1($code)
{
 $rew=mysqli_query($db,"select brname from branch where brcode='$code'");
 $rerow=mysqli_fetch_row($rew);
  return $rerow[0];
}
$trantbl=mysqli_query($db,"SELECT brcode,max(jc) jcd,max(cs) csd,max(pur) purd,max(ch) chd,max(grn) grnd,max(cr) crd,max(cp) cpd,max(br) brd,max(bp) bpd,max(wty) wtyd,max(coup) coupd ,max(wtyp) wtypd,max(coupp) couppd,max(jv) jvd FROM $tblmis group by brcode order by brcode");

while($tran=mysqli_fetch_array($trantbl))
{
    $brna=getbrname1($tran[brcode]);
 print"<tr><td >$brna</td>";


//print $dtc;
//<td>".datetohtml($tran[csd])."</td><td>".datetohtml($tran[purd])."</td><td>".datetohtml($tran[grnd])."</td><td>".datetohtml($tran[chd])."</td><td>".datetohtml($tran[crd])."</td><td>".datetohtml($tran[cpd])."</td><td>".datetohtml($tran[brd])."</td><td>".datetohtml($tran[bpd])."</td><td>".datetohtml($tran[wtyd])."</td><td>".datetohtml($tran[coupd])."</td></tr>";
$dtc=getdays($tran[jcd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[jcd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[jcd])."</td>";
}

$dtc=getdays($tran[csd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[csdd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[csd])."</td>";
}
$dtc=getdays($tran[purd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[purd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[purd])."</td>";
}
$dtc=getdays($tran[grnd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[grnd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[grnd])."</td>";
}
$dtc=getdays($tran[chd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[chd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[chd])."</td>";
}
$dtc=getdays($tran[crd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[crd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[crd])."</td>";
}
$dtc=getdays($tran[cpd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[cpd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[cpd])."</td>";
}

$dtc=getdays($tran[brd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[brd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[brd])."</td>";
}
$dtc=getdays($tran[bpd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[bpd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[bpd])."</td>";
}


$dtc=getdays($tran[wtyd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[wtyd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[wtyd])."</td>";
}

$dtc=getdays($tran[wtypd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[wtypd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[wtypd])."</td>";
}

$dtc=getdays($tran[coupd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[coupd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[coupd])."</td>";
}


$dtc=getdays($tran[couppd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[couppd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[couppd])."</td>";
}
$dtc=getdays($tran[jvd]);
if($dtc>10)
{
 print"<td bgcolor =red>".datetohtml($tran[jvd])."</td>";
 }
else
{
    print"<td >".datetohtml($tran[jvd])."</td>";
}

print"</tr>";

//print"<tr><td bgcolor =red>$tran[brcode]</td><td>".datetohtml($tran[jcd])."</td><td>".datetohtml($tran[csd])."</td><td>".datetohtml($tran[purd])."</td><td>".datetohtml($tran[grnd])."</td><td>".datetohtml($tran[chd])."</td><td>".datetohtml($tran[crd])."</td><td>".datetohtml($tran[cpd])."</td><td>".datetohtml($tran[brd])."</td><td>".datetohtml($tran[bpd])."</td><td>".datetohtml($tran[wtyd])."</td><td>".datetohtml($tran[coupd])."</td></tr>";

//print"<tr><td>$tran[brcode]</td><td>".datetohtml($tran[jcd])."</td><td>$tran[csd]</td><td>$tran[purd]</td><td>$tran[grnd]</td><td>$tran[chd]</td><td>$tran[crd]</td><td>$tran[cpd]</td><td>$tran[brd]</td><td>$tran[bpd]</td><td>$tran[wtyd]</td><td>$tran[coupd]</td></tr>";
}

?>
</table>