<?php
ini_set('display_errors', 1);


// create function that gets the main ledgerhead of the group. ok?
function getMainLedgerhead($conn,$specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $parent_ledgerhead)
{
    try {
        // checking if parent_ledgerhead provided is amongst ledgerhead_basics
        $res = $conn->prepare("SELECT  ledgerhead_name FROM  act_ledgerheads_basic WHERE  ledgerhead_name = ? ");
        $res->execute(array($parent_ledgerhead));
        $result = $res->fetchAll();
        if (count($result) == 1) {
            // found in ledgerhead_basics
            return $parent_ledgerhead;
        } else {
            //not found in basics
            // so now proceeding to check if parent_ledgerhead == one of the existing group names, if yes then return the main_ledgerhead of the found row in group_info table
            $sql = $specific_db_conn->prepare("SELECT main_ledgerhead FROM  act_group_info  where workspace_id = ? and company_id =? and branch_id =? and book_id =? and group_name = ?");
            $sql->execute(array($workspace_id, $company_id, $branch_id, $book_id, $parent_ledgerhead));
            $result_ledgerhead = $sql->fetchAll();

            if (count($result_ledgerhead) == 1) {
                return $result_ledgerhead[0]['main_ledgerhead'];
            } else {
                errorreturn("No matching group name found. Kindly enter a valid group name in parent ledgerhead.");
            }
        }
    } catch (Exception $e) {
        echo "Error : " . $e->getMessage();
    }
}

function fetchLedgerheadGroups($conn,$specific_db_conn,  $workspace_id, $company_id, $branch_id,$book_id,$search_string,$option_type,$limit,$offset,$exemption_group)
{
    
    $sql_info = "SELECT  count(*) as total_rows FROM  act_group_info  where workspace_id  =? and company_id =? and branch_id =? and book_id =? and group_name LIKE ?";
    $array_params = [$workspace_id, $company_id, $branch_id,$book_id,'%'.$search_string.'%'];
    $stmt_info = $specific_db_conn->prepare($sql_info);
    $stmt_info->execute($array_params);
    $result_total = $stmt_info->fetchAll();
    $totalrows = $result_total[0]['total_rows'];
    
    if ($option_type == "all") {
        $extra_query='';
        if($exemption_group !=null)
        {
            $extra_query=" and id != ".$exemption_group;
        }
         $sql_count = "select count(*) as total from (SELECT null as id, ledgerhead_name as group_name, null as main_ledgerhead from act_ledgerheads_basic
                UNION
                SELECT id, group_name, main_ledgerhead from act_group_info  WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? ".$extra_query.") as I
                where group_name like ? order by group_name";
            
       $stmt_count = $specific_db_conn->prepare($sql_count);
       $stmt_count->execute(array($workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%'));
       $data = [];
       $row_count = $stmt_count->fetchAll();
       $total_rows = $row_count[0]['total'];

        // if limit == all, then dont use offset and limit in sql string
        if($limit=='all')
        {
           
         //   $sql = "SELECT act.* FROM act_group_info AS act JOIN act_ledgerheads_basic AS basic on act.group_name = basic.ledgerhead_name WHERE act.workspace_id  =? and act.company_id =? and act.branch_id =? and act.book_id =? and    act.group_name LIKE ?";
        $sql = "select id, group_name, main_ledgerhead from (SELECT null as id, ledgerhead_name as group_name, null as main_ledgerhead from act_ledgerheads_basic
                UNION
                SELECT id, group_name, main_ledgerhead from act_group_info  WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? ".$extra_query.") as I
               where group_name like ? order by group_name";
        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array($workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%'));
        $data = [];
        $row = $stmt->fetchAll();
        
        }
        else {
            
            $sql = "select id, group_name, main_ledgerhead from (SELECT null as id, ledgerhead_name as group_name, null as main_ledgerhead from act_ledgerheads_basic
                UNION
                SELECT id, group_name, main_ledgerhead from act_group_info  WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =?".$extra_query.") as I
                where group_name like ? order by group_name LIMIT " . $offset . " ," . $limit . " ";
        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array($workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%'));
        $data = [];
       $row = $stmt->fetchAll();
         
        }
        

       foreach($row as $item){
            $data[] = array('group_id'=>$item['id'],'group_name'=>$item['group_name'],'parent_ledgerhead'=>$item['main_ledgerhead']);
        }
       
       if(count($row) < 50){
            return array('total_rows' => $total_rows, 'group_info' => $data, 'maxreach' => 1);
        } else {
            return array('total_rows' => $total_rows, 'group_info' => $data, 'maxreach' => 0);
        }
        //getting leadgerhead_name for act_ledgerheads_basic  
    } 
    elseif ($option_type == "ledgerheadname") {
        // if limit == all, then dont use offset and limit in sql string

        $sql = "SELECT ledgerhead_name FROM act_ledgerheads_basic WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and ledgerhead_name LIKE ?  LIMIT " . $offset . " ," . $limit . " ";
        $stmt = $conn->prepare($sql);
        $stmt->execute(array( $workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%'));
        $row = $stmt->fetchAll();
        if(count($row) > 0){
       foreach($row as $item){
            $data[] = array('group_id'=>$item['id'],'group_name'=>$item['group_name']);
        }
        }else{
            errorreturn("Group Info not exists.");
        }
        
       if(count($row) < 50){
            return array('total_rows' => $totalrows, 'group_info' => $data, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'group_info' => $data, 'maxreach' => 0);
        }
    } elseif ($option_type == "groupname") {
        // if limit == all, then dont use offset and limit in sql string
        if($limit == 'all')
        {
             $sql = "SELECT count(*) as total FROM act_group_info WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and (group_name LIKE ? or main_ledgerhead LIKE ?)";
        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array( $workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%','%' . $search_string . '%'));
        $data = array();
        $row_count = $stmt->fetchAll();
         $totalrows = $row_count[0]['total'];
            
            $sql = "SELECT * FROM act_group_info WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and (group_name LIKE ? or main_ledgerhead LIKE ?)";
        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array( $workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%','%' . $search_string . '%'));
        $data = array();
        $row = $stmt->fetchAll();
            
        }else{
        $sql = "SELECT * FROM act_group_info WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and (group_name LIKE ? or main_ledgerhead LIKE ?)  LIMIT " . $offset . " ," . $limit . " ";
        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array( $workspace_id, $company_id, $branch_id,$book_id,'%' . $search_string . '%','%' . $search_string . '%'));
        $data = array();
        $row = $stmt->fetchAll();
        
        }
       
          foreach($row as $item){
           $data[] = array('group_id'=>$item['id'],'group_name'=>$item['group_name'],'parent_ledgerhead'=>$item['main_ledgerhead']);

        }
       
        if(count($row) < 50){
            return array('total_rows' => $totalrows, 'group_info' => $data, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'group_info' => $data, 'maxreach' => 0);
        }
       
    }
    
 
}

// function to get the id, parent_ledgerhead, main_ledgerhead of a passed group / ledgerheadbasic
function getSpecificGroup($specific_db_conn,$workspace_id, $company_id,$branch_id,$book_id,$group_id) {
    // reeturns all data from the specific row of the profile
    $sql = $specific_db_conn->prepare("SELECT * FROM   act_group_info  where id = ? and workspace_id =? and company_id = ? and branch_id =? and book_id = ?");
    $sql->execute(array($group_id,$workspace_id, $company_id,$branch_id,$book_id));
    $rows = $sql->fetchAll();

    if (count($rows) == 1) {
        return array('group_id' => $rows[0]['id'],'group_name' => $rows[0]['group_name'],'parent_ledgerhead' => $rows[0]['main_ledgerhead'],
        'parent_lvl1' => $rows[0]['parent_lvl1'],'parent_lvl2' => $rows[0]['parent_lvl2'],'parent_lvl2' => $rows[0]['parent_lvl2'],'parent_lvl3' => $rows[0]['parent_lvl3'],
        'parent_lvl4' => $rows[0]['parent_lvl4'],'parent_lvl5' => $rows[0]['parent_lvl5']);
      
    }else{
          errorreturn("Enter Invalid Group ID");
    }

}
