<?php
date_default_timezone_set('Asia/Dhaka');


function find_a_field($conn,$tablef,$fieldf,$conditionf)

	{

	 $sqlfn="select $fieldf from $tablef where $conditionf limit 1";

	$res=@mysqli_query($conn,$sqlfn);

	$count=@mysqli_num_rows($res);

	if($count>0)

	{

	$data=@mysqli_fetch_row($res);

	return $data[0];

	}

	else

	return NULL;

	}




function foreign_relation($conn,$tablef,$idf,$showf,$valuef,$conditionf=''){

if($conditionf=='')

$sqlfr="select $idf,$showf from $tablef";

else

$sqlfr="select $idf,$showf from $tablef where $conditionf";

echo $sqlfr;

$res=mysqli_query($conn,$sqlfr);

while($data=mysqli_fetch_row($res))

{

if($valuef==$data[0])

echo '<option value="'.$data[0].'" selected>'.$data[1].'</option>';

else

echo '<option value="'.$data[0].'">'.$data[1].'</option>';

}

}



function find_all_field_sql($conn,$sql)

	{

	$res=@mysqli_query($conn,$sql);

	$count=@mysqli_num_rows($res);

	

	if($count>0)

		{

		$data=@mysqli_fetch_object($res);

		return $data;

		}

	else

		return NULL;

	}
	
	
	
	
function auto_complete_from_db($conn,$table,$show,$id,$con,$text_field_id)
{
if($con!='') $condition = " where ".$con;
$query="Select ".$id.", ".$show." from ".$table.$condition;

$led=mysqli_query($conn,$query);
	if(mysqli_num_rows($led) > 0)
	{
		$ledger = '[';
		while($ledg = mysqli_fetch_row($led)){
		  $ledger .= '{ value: "'.$ledg[0].'", label: "'.$ledg[1].'" },';
		}
		$ledger = substr($ledger, 0, -1);
		$ledger .= ']';
	}
	else
	{
		$ledger = '[{ value: "empty", label: "" }]';
	}

echo '<script type="text/javascript">
var data = '.$ledger.';
$(function() {
    $("#'.$text_field_id.'").autocomplete({
		source:data, 
		matchContains: true,
		minChars: 0,
		minLength:0,scroll: true,
		scrollHeight: 300,
	}).bind("focus", function(){ $(this).autocomplete("search"); });
  });
</script>';
}





function auto_complete_from_db_sql($conn,$query,$text_field_id)
{

$led=mysqli_query($conn,$query);
	if(mysqli_num_rows($led) > 0)
	{
		$ledger = '[';
		while($ledg = mysqli_fetch_row($led)){
		  $ledger .= '{ value: "'.$ledg[0].'", label: "'.$ledg[1].'" },';
		}
		$ledger = substr($ledger, 0, -1);
		$ledger .= ']';
	}
	else
	{
		$ledger = '[{ value: "empty", label: "" }]';
	}

echo '<script type="text/javascript">
$(document).ready(function(){
    var data = '.$ledger.';
    $("#'.$text_field_id.'").autocomplete({
		source:data, 
		matchContains: true,
		minChars: 0,
		minLength:0,scroll: true,
		scrollHeight: 300,
	}).bind("focus", function(){ $(this).autocomplete("search"); });
  });
</script>';
}



	
	
	
function do_calander($field,$minDate='',$maxDate='')

{

if($minDate!='')

$add .= 'minDate: '.$minDate.', ';

if($maxDate!='')

$add .= 'maxDate: '.$maxDate.', ';

echo '<script type="text/javascript">

$(document).ready(function(){

$(function() {

$("'.$field.'").datepicker({

changeMonth: true,

changeYear: true,

'.$add.'

dateFormat: "yy-mm-dd"

});

});

});</script>';

}


  function selected_two($selected_two_data) {
        echo '<script language="javascript">
          $("'.$selected_two_data.'").select2({
              placeholder: "Select",
              allowClear: true
          });</script>';
    }



function create_combobox($field_id)
{

echo '<script>
  $( function() {
    $.widget( "custom.combobox", {
      _create: function() {
        this.wrapper = $( "<span>" )
          .addClass( "form-controls" )
          .insertAfter( this.element );
 
        this.element.hide();
        this._createAutocomplete();
        this._createShowAllButton();
      },
 
      _createAutocomplete: function() {
        var selected = this.element.children( ":selected" ),
          value = selected.val() ? selected.text() : "";
 
        this.input = $( "<input>" )
          .appendTo( this.wrapper )
          .val( value )
          .attr( "title", "" )
          .addClass( "custom-combobox-input ui-widget ui-widget-content ui-state-default ui-corner-left" )
          .autocomplete({
            delay: 0,
            minLength: 0,
            source: $.proxy( this, "_source" )
          })
          .tooltip({
            classes: {
              "ui-tooltip": "ui-state-highlight"
            }
          });
 
        this._on( this.input, {
          autocompleteselect: function( event, ui ) {
            ui.item.option.selected = true;
            this._trigger( "select", event, {
              item: ui.item.option
            });
          },
 
          autocompletechange: "_removeIfInvalid"
        });
      },
 
      _createShowAllButton: function() {
        var input = this.input,
          wasOpen = false;
 
        $( "<a>" )
          .attr( "tabIndex", -1 )
          .attr( "title", "Show All Items" )
          .tooltip()
          .appendTo( this.wrapper )
          .button({
            icons: {
              primary: "ui-icon-triangle-1-s"
            },
            text: false
          })
          .removeClass( "ui-corner-all" )
          .addClass( "custom-combobox-toggle ui-corner-right" )
          .on( "mousedown", function() {
            wasOpen = input.autocomplete( "widget" ).is( ":visible" );
          })
          .on( "click", function() {
            input.trigger( "focus" );
 
            // Close if already visible
            if ( wasOpen ) {
              return;
            }
 
            // Pass empty string as value to search for, displaying all results
            input.autocomplete( "search", "" );
          });
      },
 
      _source: function( request, response ) {
        var matcher = new RegExp( $.ui.autocomplete.escapeRegex(request.term), "i" );
        response( this.element.children( "option" ).map(function() {
          var text = $( this ).text();
          if ( this.value && ( !request.term || matcher.test(text) ) )
            return {
              label: text,
              value: text,
              option: this
            };
        }) );
      },
 
      _removeIfInvalid: function( event, ui ) {
 
        // Selected an item, nothing to do
        if ( ui.item ) {
          return;
        }
 
        // Search for a match (case-insensitive)
        var value = this.input.val(),
          valueLowerCase = value.toLowerCase(),
          valid = false;
        this.element.children( "option" ).each(function() {
          if ( $( this ).text().toLowerCase() === valueLowerCase ) {
            this.selected = valid = true;
            return false;
          }
        });
 
        // Found a match, nothing to do
        if ( valid ) {
          return;
        }
 
        // Remove invalid value
        this.input
          .val( "" )
          .attr( "title", value + " didnt match any item" )
          .tooltip( "open" );
        this.element.val( "" );
        this._delay(function() {
          this.input.tooltip( "close" ).attr( "title", "" );
        }, 2500 );
        this.input.autocomplete( "instance" ).term = "";
      },
 
      _destroy: function() {
        this.wrapper.remove();
        this.element.show();
      }
    });
 
    $( "#'.$field_id.'" ).combobox();
    $( "#toggle" ).on( "click", function() {
      $( "#'.$field_id.'" ).toggle();
    });
  } );
  </script>';
}




function add_to_sec_journal($conn, $jv_no,  $jv_date,  $ledger_id, $ref_no,  $narration,  $dr_amt,  $cr_amt,  $tr_from,  $tr_no,  $relevant_ledger,  $cc_code,  $tr_id,  $group_for,  $status,  $entry_by,  $entry_at) 

{
	if($group_for>0) $group_for = $group_for; else $group_for = $_SESSION['user']['group'];
	if($entry_at=='') $entry_at = date('Y-m-d H:i:s');
	if($entry_by=='') $entry_by = $_SESSION['user']['id']; 
  $journal='INSERT INTO secondary_journal (
	jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no,  relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$ledger_id.'", "'.$ref_no.'", "'.$narration.'",  "'.$dr_amt.'",  "'.$cr_amt.'",  "'.$tr_from.'",  "'.$tr_no.'",  "'.$relevant_ledger.'",  "'.$cc_code.'",  "'.$tr_id.'",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'")';
	$query_journal=mysqli_query($conn,$journal);
}

function sec_journal_journal($conn,$sec_jv_no,$jv_no,$tr_froms)
{
 $sql = 'insert into journal select * from secondary_journal where jv_no = "'.$sec_jv_no.'" and tr_from = "'.$tr_froms.'"';
 $query = mysqli_query($conn,$sql);
}





function journal_item_control($conn, $ji_date,$item_id,$warehouse_id,$item_in,$item_ex,$item_price,$total_amt,$tr_from,$tr_no,$sr_no,$ref_no,$group_for,$data_table)
{


		 $entry_by = $_SESSION['user']['id']; 
		 $entry_at = $now=date('Y-m-d H:i:s');

  
   $sql='INSERT INTO journal_item 
  (ji_date, item_id, warehouse_id, item_in, item_ex, item_price, total_amt, tr_from, tr_no, sr_no, ref_no, group_for, data_table, entry_by, entry_at) 
  VALUES 
  ("'.$ji_date.'", "'.$item_id.'", "'.$warehouse_id.'", "'.$item_in.'", "'.$item_ex.'", "'.$item_price.'", "'.$total_amt.'", "'.$tr_from.'", "'.$tr_no.'", "'.$sr_no.'", "'.$ref_no.'", "'.$group_for.'", "'.$data_table.'", "'.$entry_by.'", "'.$entry_at.'")';
  mysqli_query($conn,$sql);
  
}






function auto_insert_invoice_secoundary_journal($conn,$jv_no){

$ms_sql="SELECT m.*, a.ledger_id  FROM journal_master m, accounts_reference a  WHERE m.ref_no=a.ref_no and m.jv_no='".$jv_no."'";
$ms_data = find_all_field_sql($conn,$ms_sql);

   $sql = 'select m.*, sum(s.total_amt) as invoice_amt, sum(s.discount_amt) as discount_amt, sum(s.amt_after_discount) as amt_after_discount
from journal_master m, sales_invoice s where m.jv_no=s.jv_no and s.jv_no ="'.$jv_no.'" group by s.jv_no';
	
	$query = mysqli_query($conn,$sql);
	$data=mysqli_fetch_object($query);

$status='MANUAL';
$entry_by =  $_SESSION['user']['id'];
$entry_at = $now=date('Y-m-d H:i:s');

$jv_no=$ms_data->jv_no;
$jv_date=$ms_data->jv_date;
$ledger_id=$ms_data->ledger_id;
$ref_no=$ms_data->ref_no;
$tr_from=$ms_data->tr_from;
$tr_no=0;
$cc_code=0;
$tr_id=0;
$group_for=$ms_data->group_for;
$view_jv_no=$ms_data->view_jv_no;
$cr_amt=0;
$relevant_ledger=0;


$config_sql="SELECT * FROM config_ledger  WHERE group_for='".$group_for."'";
$config_data = find_all_field_sql($conn,$config_sql);


 $cash_rec_sql="SELECT sum(a.order_no) as cash_rec_amt FROM journal_master m, sales_receive_data a 
 WHERE m.jv_no=a.jv_no and a.ledger_id>0 and m.ref_no in (200001,200004) and m.jv_no='".$jv_no."' group by a.jv_no";
$cash_rec_data = find_all_field_sql($conn,$cash_rec_sql);

$cash_rec_found=$cash_rec_data->cash_rec_amt;

$narration = 'Inv No# '.$view_jv_no;

$amt_receivable=($data->invoice_amt);

$discount_amt=($data->discount_amt);

$amt_after_discount=($data->invoice_amt-$data->discount_amt);


if($amt_after_discount>0){

if($ref_no==200001 || $ref_no==200004) {


  $sql_cash='select m.ledger_id as cash_ledger,  sum(s.receive_amt) as receive_amt, s.order_no 
from cash_bank_ledger m, sales_receive_data s
 where m.id=s.order_no and s.jv_no ="'.$jv_no.'" and m.ledger_id>0 group by s.id';
$query_cash = mysqli_query($conn,$sql_cash);

while($data_cash=mysqli_fetch_object($query_cash)) 
{


//echo $si++;

$cr_amt=0;
	$cash_journal='INSERT INTO secondary_journal (jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no, item_id, unit_price, total_unit, relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at, edit_at, edit_by, checked_at, checked_by)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$data_cash->cash_ledger.'", "'.$ref_no.'", "'.$narration.'", "'.$data_cash->receive_amt.'", "'.$cr_amt.'", "'.$tr_from.'",  "'.$tr_no.'", "0",  "0", "0", "'.$data_cash->cash_ledger.'",  "'.$cc_code.'",  "'.$data_cash->order_no.'",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'",  "0000-00-00", "0", "0000-00-00", "0")';

$query_cash_journal=mysqli_query($conn, $cash_journal);
}

}else{

$receivable_journal='INSERT INTO secondary_journal (jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no, item_id, unit_price, total_unit, relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at, edit_at, edit_by, checked_at, checked_by)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$ledger_id.'", "'.$ref_no.'", "'.$narration.'", "'.$amt_after_discount.'", "'.$cr_amt.'", "'.$tr_from.'",  "'.$tr_no.'", "0",  "0", "0", "0",  "'.$cc_code.'",  "0",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'",  "0000-00-00", "0", "0000-00-00", "0")';

$query_receivable_journal=mysqli_query($conn, $receivable_journal);

}


if($cash_rec_found==0){

$cash_in_hand='1100100001';

$cash_rec_journal='INSERT INTO secondary_journal (jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no, item_id, unit_price, total_unit, relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at, edit_at, edit_by, checked_at, checked_by)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$cash_in_hand.'", "'.$ref_no.'", "'.$narration.'", "'.$amt_after_discount.'", "'.$cr_amt.'", "'.$tr_from.'",  "'.$tr_no.'", "0",  "0", "0", "'.$cash_in_hand.'",  "'.$cc_code.'",  "0",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'",  "0000-00-00", "0", "0000-00-00", "0")';

$query_receivable_journal=mysqli_query($conn, $cash_rec_journal);

}


}


if($discount_amt>0){
$journal='INSERT INTO secondary_journal (jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no, item_id, unit_price, total_unit, relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at, edit_at, edit_by, checked_at, checked_by)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$config_data->discount_ledger.'", "'.$ref_no.'", "'.$narration.'",  "'.$discount_amt.'",  "'.$cr_amt.'",  "'.$tr_from.'",  "'.$tr_no.'", "0", "0", "0",  "'.$relevant_ledger.'",  "'.$cc_code.'",  "'.$tr_id.'",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'",  "0000-00-00", "0", "0000-00-00", "0")';
$query_journal=mysqli_query($conn,$journal);

}



$sql_inv='select s.id, s.item_id, s.unit_price, sum(s.total_unit) as inv_qty, sum(s.total_amt) as inv_amt, i.income_ledger, c.item_category
from journal_master m, sales_invoice s, item_info i, item_category c
 where m.jv_no=s.jv_no and s.item_id=i.item_id and c.id=i.item_category and s.jv_no ="'.$jv_no.'" and s.total_amt>0 group by i.income_ledger';
$query_inv = mysqli_query($conn,$sql_inv);

while($data_inv=mysqli_fetch_object($query_inv))
{

$dr_amt=0;
//$narration_item ='Sales Of '.$data_inv->item_category;

 $item_journal='INSERT INTO secondary_journal (jv_no,  jv_date,  ledger_id, ref_no, narration,  dr_amt,  cr_amt,  tr_from,  tr_no, item_id, unit_price, total_unit, relevant_ledger,  cc_code,  tr_id,  group_for,  status,  entry_by,  entry_at, edit_at, edit_by, checked_at, checked_by)
	VALUES ("'.$jv_no.'",  "'.$jv_date.'",  "'.$data_inv->income_ledger.'", "'.$ref_no.'", "'.$narration.'", "'.$dr_amt.'", "'.$data_inv->inv_amt.'", "'.$tr_from.'",  "'.$tr_no.'", "'.$data_inv->item_id.'",  "'.$data_inv->unit_price.'", "'.$data_inv->inv_qty.'", "0",  "'.$cc_code.'",  "'.$data_inv->id.'",  "'.$group_for.'",  "'.$status.'",  "'.$entry_by.'",  "'.$entry_at.'",  "0000-00-00", "0", "0000-00-00", "0")';
$query_item_journal=mysqli_query($conn, $item_journal);

}





$jv_approval = find_a_field($conn,'journal_type','jv_approval','journal_type="'.$tr_from.'"');

if($jv_approval=="Yes"){

sec_journal_journal($conn,$jv_no,$jv_no,$tr_from);

$time_now = date('Y-m-d H:i:s');

$up_sj='update secondary_journal set status="CHECKED", checked_at="'.$time_now.'", checked_by="'.$_SESSION['user']['id'].'" 
where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);

$up_jv='update journal set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jv);

$up_jm='update journal_master set status="CHECKED", approve_by="'.$_SESSION['user']['id'].'", approve_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

}elseif($jv_approval=="No") {
$up_jm='update journal_master set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

$up_sj='update secondary_journal set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);
}




}






function auto_insert_iou_return_secoundary_journal($conn,$jv_no){

$ms_sql="SELECT m.* FROM journal_master m, accounts_reference a  WHERE m.ref_no=a.ref_no and m.jv_no='".$jv_no."'";
$ms_data = find_all_field_sql($conn,$ms_sql);

 $sql = 'select m.*, sum(s.dr_amt) as iou_return_amt
from journal_master m, journal_iou_cash_receive s where m.jv_no=s.jv_no and s.jv_no ="'.$jv_no.'" group by s.jv_no';
	
	$query = mysqli_query($conn,$sql);
	$data=mysqli_fetch_object($query);

$status='MANUAL';
$entry_by =  $_SESSION['user']['id'];
$entry_at = $now=date('Y-m-d H:i:s');

$jv_date=$ms_data->jv_date;

$jv_type=1;
$jv_special=1;
$jv_title=find_a_field($conn,'journal_type','jv_title','id="'.$jv_type.'"');

		$YR = date('Y',strtotime($jv_date));
 		$year = date('y',strtotime($jv_date));
  		$month = date('m',strtotime($jv_date));
 		$inv_cy_id = find_a_field($conn,'journal_master','max(jv_id)','year="'.$YR.'" and jv_type="'.$jv_type.'"')+1;
  		$cy_id = sprintf("%06d", $inv_cy_id);
   		$invoice_no=''.$year.''.$month.''.$jv_type.''.$cy_id;
		$view_invoice_no=$jv_title.''.$year.''.$month.''.$jv_type.''.$cy_id;
		
$rec_jv_no=$invoice_no;

$iou_jv_no=$ms_data->jv_no;

$iou_ledger_id=$ms_data->master_ledger_id;
$ref_no=$ms_data->ref_no;
$tr_from='Receipt';
$tr_no=$ms_data->jv_no;
$cc_code=0;
$group_for=$ms_data->group_for;
$iou_view_jv_no=$ms_data->view_jv_no;
$dr_amt=0;
$relevant_ledger=0;
$jv_special=1;
$special_note='IOU Settlement';
$remarks=$ms_data->remarks;


 $ins_sql = 'INSERT INTO  journal_master (jv_no, year, jv_id, view_jv_no, jv_type, jv_date, group_for, master_ledger_id, tr_from, remarks, status, entry_at, entry_by,  ref_no, jv_special, special_note, emp_code) VALUES

("'.$invoice_no.'", "'.$YR.'", "'.$cy_id.'", "'.$view_invoice_no.'", "'.$jv_type.'", "'.$jv_date.'", "'.$group_for.'", "'.$iou_ledger_id.'", "'.$tr_from.'", "'.$remarks.'", "'.$status.'", "'.$entry_at.'", "'.$entry_by.'",  "'.$ref_no.'", "'.$jv_special.'", "'.$special_note.'", "'.$ref_no.'")';
	
mysqli_query($conn,$ins_sql);


$narration = 'JV No# '.$iou_view_jv_no;

$iou_return_amt=$data->iou_return_amt;


$sql_inv='select s.* from journal_master m, journal_iou_cash_receive s where m.jv_no=s.jv_no and s.jv_no ="'.$jv_no.'" group by s.id';
$query_inv = mysqli_query($conn,$sql_inv);

while($data_inv=mysqli_fetch_object($query_inv))
{

add_to_sec_journal($conn, $invoice_no, $jv_date, $data_inv->ledger_id, $ref_no, $data_inv->narration, $data_inv->dr_amt, $cr_amt,  $tr_from, $tr_no, $iou_ledger_id,  $cc_code,  $data_inv->id, $group_for, $status, $entry_by, $entry_at);	

}

if($iou_return_amt>0){
add_to_sec_journal($conn, $invoice_no,  $jv_date,  $iou_ledger_id, $ref_no, $narration, $dr_amt, $iou_return_amt,  $tr_from,  $tr_no,  $relevant_ledger,  $cc_code,  $tr_id,  $group_for,  $status,  $entry_by,  $entry_at);
}



$up_iou='update journal_iou_cash_receive set status="CHECKED", tr_no="'.$invoice_no.'" where jv_no="'.$iou_jv_no.'"';
mysqli_query($conn,$up_iou);


$jv_approval = find_a_field($conn,'journal_type','jv_approval','journal_type="'.$tr_from.'"');

if($jv_approval=="Yes"){

sec_journal_journal($conn,$rec_jv_no,$rec_jv_no,$tr_from);

$time_now = date('Y-m-d H:i:s');

$up_sj='update secondary_journal set status="CHECKED", checked_at="'.$time_now.'", checked_by="'.$_SESSION['user']['id'].'" 
where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);

$up_jv='update journal set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jv);

$up_jm='update journal_master set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

$up_si='update sales_invoice set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_si);

}elseif($jv_approval=="No") {
$up_jm='update journal_master set status="UNCHECKED" where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

$up_sj='update secondary_journal set status="UNCHECKED" where jv_no="'.$rec_jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);
}

}






function auto_insert_bill_secoundary_journal($conn,$jv_no){

$ms_sql="SELECT * FROM journal_master  WHERE jv_no='".$jv_no."'";
$ms_data = find_all_field_sql($conn,$ms_sql);

  $sql = 'select m.*, sum(s.total_amt) as invoice_amt
from journal_master m, purchase_invoice s where m.jv_no=s.jv_no and s.jv_no ="'.$jv_no.'" group by s.jv_no';
	
	$query = mysqli_query($conn,$sql);
	$data=mysqli_fetch_object($query);

$status='MANUAL';
$entry_by =  $_SESSION['user']['id'];
$entry_at = $now=date('Y-m-d H:i:s');

$jv_no=$ms_data->jv_no;
$jv_date=$ms_data->jv_date;
$ledger_id=$ms_data->master_ledger_id;
$ref_no=$ms_data->ref_no;
$tr_from=$ms_data->tr_from;
$tr_no=0;
$cc_code=0;
$tr_id=0;
$group_for=$ms_data->group_for;
$view_jv_no=$ms_data->view_jv_no;
$dr_amt=0;
$relevant_ledger=0;
	
$narration = 'Bill No# '.$view_jv_no;


$data_ar1 = [
    'jv_no' => $jv_no,
    'jv_date' => $jv_date,
    'ledger_id' => $ledger_id,
    'ref_no' => $ref_no,
    'narration' => $narration,
    'dr_amt' => $dr_amt,
    'cr_amt' => $data->invoice_amt,
	'tr_from' => $tr_from,
	'tr_no' => $tr_no,
	'relevant_ledger' => $relevant_ledger,
	'cc_code' => $cc_code,
	'tr_id' => $tr_id,
	'group_for' => $group_for,
	'status' => $status,
	'entry_by' => $entry_by,
	'entry_at' => $entry_at
	
];

$columns = implode(", ", array_keys($data_ar1));
$values  = implode("', '", array_values($data_ar1));
	
if($data->invoice_amt>0){
  $sql = "INSERT INTO secondary_journal ($columns) VALUES ('$values')";
mysqli_query($conn,$sql);
}



$sql_inv='select s.id, s.warehouse_id, sum(s.total_amt) as inv_amt, s.unit_price, s.total_unit, i.item_id, i.item_name, i.item_type, i.inventory_ledger, c.item_category
from journal_master m, purchase_invoice s, item_info i, item_category c
 where m.jv_no=s.jv_no and s.item_id=i.item_id and c.id=i.item_category and s.jv_no ="'.$jv_no.'" and i.inventory_ledger>0
  group by s.id order by s.id';
$query_inv = mysqli_query($conn,$sql_inv);
while($data_inv=mysqli_fetch_object($query_inv))
{


$data_ar2 = [
    'jv_no' => $jv_no,
    'jv_date' => $jv_date,
    'ledger_id' => $data_inv->inventory_ledger,
    'ref_no' => $ref_no,
    'narration' => $data_inv->item_name,
    'dr_amt' => $data_inv->inv_amt,
    'cr_amt' => 0,
	'tr_from' => $tr_from,
	'tr_no' => $tr_no,
	'relevant_ledger' => $relevant_ledger,
	'cc_code' => $cc_code,
	'tr_id' =>  $data_inv->id,
	'group_for' => $group_for,
	'status' => $status,
	'entry_by' => $entry_by,
	'entry_at' => $entry_at,
	'item_id' => $data_inv->item_id,
	'unit_price' => $data_inv->unit_price,
	'total_unit' => $data_inv->total_unit
];

$columns2 = implode(", ", array_keys($data_ar2));
$values2  = implode("', '", array_values($data_ar2));
	

  $sql2 = "INSERT INTO secondary_journal ($columns2) VALUES ('$values2')";
   mysqli_query($conn,$sql2);



$data_table='purchase_invoice';
if($data_inv->item_type==1){
$data_ar3 = [
    'ji_date' => $jv_date,
    'item_id' => $data_inv->item_id,
    'warehouse_id' => $data_inv->warehouse_id,
    'item_in' => $data_inv->total_unit,
    'item_ex' => 0,
    'item_price' => $data_inv->unit_price,
    'total_amt' => $data_inv->inv_amt,
	'tr_from' => $tr_from,
	'tr_no' => $data_inv->id,
	'sr_no' => $jv_no,
	'ref_no' => $ref_no,
	'group_for' =>  $group_for,
	'data_table' => $data_table
];

$columns3 = implode(", ", array_keys($data_ar3));
$values3  = implode("', '", array_values($data_ar3));
	

  echo $sql3 = "INSERT INTO journal_item ($columns3) VALUES ('$values3')";
   mysqli_query($conn,$sql3);

}

}


//$sql_inv_non='select s.id, sum(s.total_amt) as inv_non_amt,  s.unit_price, s.total_unit, i.item_id, i.item_name, i.cost_ledger, c.item_category
//from journal_master m, purchase_invoice s, item_info i, item_category c
// where m.jv_no=s.jv_no and s.item_id=i.item_id and c.id=i.item_category and s.jv_no ="'.$jv_no.'" and i.inventory_ledger=0
//  group by s.id order by s.id';
//$query_inv_non = mysqli_query($conn,$sql_inv_non);
//while($data_inv_non=mysqli_fetch_object($query_inv_non))
//{
//
//$narration_item_non =''.$data_inv_non->item_name;
//
//add_to_sec_journal($conn, $jv_no, $jv_date, $data_inv_non->cost_ledger, $ref_no, $narration_item_non, $data_inv_non->inv_non_amt, $dr_amt, $tr_from, $tr_no, $relevant_ledger, $cc_code, $data_inv_non->id, $group_for, $status, $entry_by, $entry_at, $data_inv_non->item_id, $data_inv_non->unit_price, $data_inv_non->total_unit);	
//
//}


$jv_approval = find_a_field($conn,'journal_type','jv_approval','journal_type="'.$tr_from.'"');

if($jv_approval=="Yes"){

sec_journal_journal($conn,$jv_no,$jv_no,$tr_from);

$time_now = date('Y-m-d H:i:s');

$up_sj='update secondary_journal set status="CHECKED", checked_at="'.$time_now.'", checked_by="'.$_SESSION['user']['id'].'" 
where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);

$up_jv='update journal set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jv);

$up_jm='update journal_master set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

}elseif($jv_approval=="No") {
$up_jm='update journal_master set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

$up_sj='update secondary_journal set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);
}

}


function auto_insert_salary_advance_secoundary_journal($conn,$jv_no){

$ms_sql="SELECT * FROM journal_master  WHERE jv_no='".$jv_no."'";
$ms_data = find_all_field_sql($conn,$ms_sql);

   $sql = 'select * from employee_advance where tr_no ="'.$jv_no.'" group by tr_no';
	
	$query = mysqli_query($conn,$sql);
	$data=mysqli_fetch_object($query);
	
$status='MANUAL';
$entry_by =  $_SESSION['user']['id'];
$entry_at = $now=date('Y-m-d H:i:s');
$group_for = $_SESSION['user']['group'];
$jv_no=$ms_data->jv_no;
$view_jv_no=$ms_data->view_jv_no;
$jv_date=$ms_data->jv_date;
$tr_from=$ms_data->tr_from;
		
$config_sql="SELECT * FROM config_ledger  WHERE group_for='".$group_for."'";
$config_data = find_all_field_sql($conn,$config_sql);


add_to_sec_journal($conn, $jv_no, $jv_date, $config_data->salary_advance, $data->emp_code, 'Advance Payment', $data->advance_amt, $cr_amt,  $tr_from, $jv_no,
 $ms_data->master_ledger_id, $cc_code, $data->id, $group_for, $status, $entry_by, $entry_at);
 
 add_to_sec_journal($conn, $jv_no, $jv_date, $ms_data->master_ledger_id, $data->emp_code, 'Advance Payment',  $dr_amt, $data->advance_amt,  $tr_from, $jv_no,
 $config_data->salary_advance, $cc_code, $data->id, $group_for, $status, $entry_by, $entry_at);


	
$jv_approval = find_a_field($conn,'journal_type','jv_approval','journal_type="'.$tr_from.'"');

if($jv_approval=="Yes"){

sec_journal_journal($conn,$jv_no,$jv_no,$tr_from);

$time_now = date('Y-m-d H:i:s');

$up_sj='update secondary_journal set status="CHECKED", checked_at="'.$time_now.'", checked_by="'.$_SESSION['user']['id'].'" 
where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);

$up_jv='update journal set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jv);

$up_jm='update journal_master set status="CHECKED", checked_by="'.$_SESSION['user']['id'].'", checked_at="'.$time_now.'"
 where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

}elseif($jv_approval=="No") {
$up_jm='update journal_master set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_jm);

$up_sj='update secondary_journal set status="UNCHECKED" where jv_no="'.$jv_no.'" and tr_from="'.$tr_from.'"';
mysqli_query($conn,$up_sj);
}


}






?>



