<?

$pre_yr=$yr-1;

$thirty1=30;
$sixty1=60;
$ninty1=90;

$cl_stk_amt=0;
$prev_stk_amt=0;


$month_bal_tbl="monthwise_bal_".$brcode.$user;

mysqli_query($db,"drop table $month_bal_tbl");

mysqli_query($db,"CREATE TABLE $month_bal_tbl (month int ,op double(10,2) default 0 ,cl double(10,2) default 0, brcode varchar(50) )");

mysqli_query($db,"delete from $month_bal_tbl");




for($month=0; $month<13; $month++)
   {
       $totx=0;
	if($month<10)
		$month= sprintf("%02s", $month);

 	$dfrom=$yr."-".$month."-01"; 
       $timestamp    = strtotime($dfrom);
       $dto  = date('Y-m-t', $timestamp);


       if($month=='00')
        {
                   $dfrom=$pre_yr.'-12-31';
                   $dto  =$dfrom;
        }

    


	$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
	$brrow=mysqli_fetch_array($brres);



	$ker=mysqli_query($db,"select brstate from branch where brcode='$brcode'");
	$kar=mysqli_fetch_array($ker);

 	 $tbl_pur="purchasedetbr".$brcode.$user;

	mysqli_query($db,"drop table $tbl_pur");

	mysqli_query($db,"CREATE TABLE $tbl_pur select * from lubricant_purmast where prdate<='$dto'  ");
	 $myl=("CREATE TABLE $tbl_pur select * from lubricant_purmast where prdate<='$dto'  ");



	$tbl="stockagingbr".$brcode.$user;
	mysqli_query($db,"drop table $tbl");

	mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op double(10,2)default 0,purch double(10,2)default 0,serv double(10,2)default 0,sale double(10,2)default 0,transin double(10,2)default 0,transout double(10,2)default 0,sal_ret double(10,2)default 0,pur_ret double(10,2)default 0,war_sal double(10,2)default 0,war_sal_ret double(10,2)default 0, adj_in double(10,2)default 0,adj_out double(10,2)default 0,opin double(10,2)default 0,opout double(10,2)default 0,brcode varchar(20),grp varchar(100),state varchar(50),dto date,closing double(10,2),type varchar(100))");
	mysqli_query($db,"delete from $tbl");


	//opening new
	$op=mysqli_query($db,"select brcode from branch where brcode='$brcode'") or die("ERR:".mysqli_error($db));

	while($op1=mysqli_fetch_array($op))
	{
		mysqli_query($db,"insert into $tbl (partcode,partname,op,brcode,type) select itemcode as partcode,itemname as partname,sum(qty)op,brcode,'opening' from spareopstock where (brcode = '$op1[brcode]') group by  itemcode");

		mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select pcode,pname,sum(qty)opin,brcode,'purchase' from lubricant_purmast where (brcode = '$op1[brcode]') and prdate<'$dfrom'   group by  pcode");

		mysqli_query($db,"insert into $tbl (partcode,partname,opin,opout,brcode,type) select partcode,partname,sum(qty)opin,sum(outqty)opout,brcode,'adjstock' from adjstock where (brcode = '$op1[brcode]') and entrydate<'$dfrom' group by  partcode");

//Transactions



		mysqli_query($db,"insert into $tbl (partcode,partname,opin,brcode,type) select pcode,pname,sum(qty)purch,brcode,'purchase' from lubricant_purmast where (brcode = '$op1[brcode]') and prdate>='$dfrom' and prdate<='$dto'   group by  pcode");



		mysqli_query($db,"insert into $tbl (partcode,partname,sale,brcode,type) select partcode,partname,sum(qty)purch,brcode,'sale' from jcardparts where (brcode = '$op1[brcode]') and edate>='$dfrom' and edate<='$dto' group by  partcode");




		mysqli_query($db,"insert into $tbl (partcode,partname,adj_in,adj_out,brcode,type) select partcode,partname,sum(qty)adj_in,sum(outqty)adj_out,brcode,'adjstock' from adjstock where (brcode = '$op1[brcode]') and entrydate>='$dfrom' and entrydate<='$dto' group by  partcode");


	}



	mysqli_query($db,"update $tbl set dto='$dto',grp=(select partmaster.group1 from partmaster where partmaster.partcode = $tbl.partcode)");

	$trantbl=mysqli_query($db,"select partcode,partname,sum(op),sum(purch),sum(sale),sum(transin),sum(transout),sum(sal_ret),sum(pur_ret),sum(war_sal),sum(war_sal_ret),sum(adj_in),sum(adj_out),sum(opin),sum(opout),sum(serv) from $tbl group by partcode") or die("ERR:".mysqli_error($db));
	while($tran=mysqli_fetch_array($trantbl))
	{
		$mop = $tran[2]+$tran[13]-$tran[14];
		$mcl = $mop+$tran[3]-$tran[4]+$tran[5]-$tran[6]+$tran[7]-$tran[8]-$tran[9]+$tran[10]+$tran[11]-$tran[12]-$tran[15];
		mysqli_query($db,"update $tbl set closing='$mcl'where partcode = $tran[0])");

	}


	
	$ker=mysqli_query($db,"select brstate from branch where brcode='$brcode'");
	$kar=mysqli_fetch_array($ker);
	$tbl="stockagingbr".$brcode.$user;
	$tbl2="stockrepvalbr".$brcode.$user;
	$tbl_pur="purchasedetbr".$brcode.$user;
	$r=mysqli_query($db,"select dto from $tbl");

	$rw=mysqli_fetch_array($r);

 	$dtt=date("Y-m-d");
	$dtt1=date("Y-m-d");



	$next_year = mktime(0,0,0,date("m"),date("d"),date("Y")+1);


	mysqli_query($db,"CREATE TABLE $tbl2 (partcode varchar(200),partname varchar(200),entrydate date,supqty double(10,2),price double(10,2))");

	mysqli_query($db,"delete from $tbl2");
	$re=mysqli_query($db,"select partcode,partname,sum(op),sum(purch),sum(sale),sum(transin),sum(transout),sum(sal_ret),sum(pur_ret),sum(war_sal),sum(war_sal_ret),sum(adj_in),sum(adj_out),sum(opin),sum(opout),dto,sum(serv) from $tbl group by partcode") or die("ERR:".mysqli_error($db));

	while($row=mysqli_fetch_array($re))
	{
		$t='stk';
		$mop = $row[2]+$row[13]-$row[14];
		$no=$mop+$row[3]-$row[4]+$row[5]-$row[6]+$row[7]-$row[8]-$row[9]+$row[10]+$row[11]-$row[12]-$row[16];
	 	$sql="select prdate,qty,tot from $tbl_pur where pcode='$row[partcode]' and brcode='$brcode' and prdate<='$row[dto]' order by prdate desc";
		$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
		if(mysqli_num_rows($chk)<=0)
		{
			$sql="select prdate,qty,tot from $tbl_pur where pcode='$row[partcode]'  and prdate<='$row[dto]' order by prdate desc";
			$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
				$t='stk';
		}
		if ($no>0)
		{
			while($row1=mysqli_fetch_array($chk))
			{
				$pcode=$row[partcode];
				$pname=htmlspecialchars(addslashes($row[partname]));
				$dt=$row1[prdate];
				$pr=$row1[2]/$row1[1];
				$qt=$row1[1];
			
				if($no>0)
				{ 
					if($qt>$no)
					{
						$qt = $no;
					}
					 $sq="insert into $tbl2 values('$pcode','$pname','$dt', '$qt','$pr')";
					mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
				}
				else
				{	
					break;
				}
				$no-=$qt;
			}
			if($no>0)
			{ 
			
	
				$sql="select effectfrom,ndpcost from spareprmast where partcode='$row[partcode]' and brcode='$brcode' order by effectfrom desc limit 1";
	
	
				$pcode=$row[partcode];
				$pname=htmlspecialchars(addslashes($row[partname]));
				$mpart=mysqli_query($db,$sql);
				$row1=mysqli_fetch_array($mpart);
				$pr=$row1[1];
				$sq="insert into $tbl2 values('$pcode','$pname','0000-00-00', '$no','$pr')";
				mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
			}
		}
	}
	$next_year = mktime(0,0,0,date("m"),date("d"),date("Y")+1);
	$tbl_sal="saledet".$brcode.$user;

	$re2=mysqli_query($db,"select *,sum(price*supqty)/sum(supqty),sum(supqty) from $tbl2 group by partcode");

	while($row=mysqli_fetch_array($re2))
	{

		$x=$row[5]*$row[6];
		
		$tq+=$row[6];
		$totx+=$x;

	}


       mysqli_query($db,"insert into $month_bal_tbl values('$month','$prev_stk_amt','$totx','$brcode')"); 

       $prev_stk_amt=$totx;

  }//close for loop


?>

