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


function fetchInventoryGroups($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $search_string, $function_type, $limit, $offset, $exemption_group)
{

    $data = array();


//    if ($function_type == "all") {

        $extra_query = '';
        if ($exemption_group != null) {
            $extra_query = " and id != " . $exemption_group;
        }

        $sql_count = "select count(*) as total from (SELECT id, group_name from inventory_groups_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";
        //print_r($sql_count);die();
        $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') {
            $sql_info = "SELECT  count(*) as total_rows FROM  inventory_groups_info  where workspace_id  =? and company_id =? and branch_id =? and book_id =? and group_name LIKE ?" . $extra_query . "";
            $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'];


            $sql = "SELECT id,group_name FROM inventory_groups_info WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and group_name LIKE ? " . $extra_query . "";
            $stmt = $specific_db_conn->prepare($sql);
            $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%'));
            $row = $stmt->fetchAll();

            foreach ($row as $item) {
                $data[] = array('group_id' => $item['id'], 'group_name' => $item['group_name']);
            }

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


            $sql_info = "SELECT  count(*) as total_rows FROM  inventory_groups_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'];


            $sql = "SELECT * FROM inventory_groups_info WHERE workspace_id  =? and company_id =? and branch_id =? and book_id =? and group_name LIKE ? LIMIT " . $offset . " ," . $limit . "";
            $stmt = $specific_db_conn->prepare($sql);
            $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%'));
            $row = $stmt->fetchAll();

            foreach ($row as $item) {
                // preparing query
                $intra_tax_ledgerids = null;
                if ($item['intra_tax_ledgerids'] != null) {
                    $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id=? and book_id= ? and id IN (" . $item['intra_tax_ledgerids'] . ")";
                    $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
                    $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
                    $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
                    foreach ($result_tax_ledger_details as $temp) {
                        $intra_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
                    }
                }
        
                $inter_tax_ledgerids = null;
                if ($item['inter_tax_ledgerids'] != null) {
                    $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id IN (" . $item['inter_tax_ledgerids'] . ")";
                    $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
                    $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
                    $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
                    foreach ($result_tax_ledger_details as $temp) {
                        $inter_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
                    }
                }
        
                $other_tax_ledgerids = null;
                if ($item['other_tax_ledgerids'] != null) {
                    $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id IN (" . $item['other_tax_ledgerids'] . ")";
                    $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
                    $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
                    $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
                    foreach ($result_tax_ledger_details as $temp) {
                        $other_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
                    }
                }

                
                
                $data[] = array('group_id' => $item['id'], 'group_name' => $item['group_name'], 'inter_tax_ledgerids' => $inter_tax_ledgerids, 'intra_tax_ledgerids' => $intra_tax_ledgerids, 'other_tax_ledgerids' => $other_tax_ledgerids);
            }

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




function getSpecificGroups($specific_db_conn, $group_id, $workspace_id, $company_id, $branch_id, $book_id)
{

    // reeturns all data from the specific row of the profile
    $sql = $specific_db_conn->prepare("SELECT * FROM  inventory_groups_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) {
        // preparing query
        $intra_tax_ledgerids = null;
        if ($rows[0]['intra_tax_ledgerids'] != null) {
            $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id=? and book_id= ? and id IN (" . $rows[0]['intra_tax_ledgerids'] . ")";
            $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
            $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
            $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
            foreach ($result_tax_ledger_details as $temp) {
                $intra_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
            }
        }

        $inter_tax_ledgerids = null;
        if ($rows[0]['inter_tax_ledgerids'] != null) {
            $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id IN (" . $rows[0]['inter_tax_ledgerids'] . ")";
            $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
            $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
            $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
            foreach ($result_tax_ledger_details as $temp) {
                $inter_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
            }
        }

        $other_tax_ledgerids = null;
        if ($rows[0]['other_tax_ledgerids'] != null) {
            $sql_fetch_tax_details = "Select id, ledger_name from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id IN (" . $rows[0]['other_tax_ledgerids'] . ")";
            $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
            $fetch_tax_ledger_details->execute(array($workspace_id, $company_id, $branch_id, $book_id));
            $result_tax_ledger_details = $fetch_tax_ledger_details->fetchAll();
            foreach ($result_tax_ledger_details as $temp) {
                $other_tax_ledgerids[] = array('id' => $temp['id'], 'label' => $temp['ledger_name']);
            }
        }

        return array('group_id' => $rows[0]['id'], 'group_name' => $rows[0]['group_name'], 'parent_group' => $rows[0]['parent_lvl1'], 'inter_tax_ledgerids' => $inter_tax_ledgerids, 'intra_tax_ledgerids' => $intra_tax_ledgerids, 'other_tax_ledgerids' => $other_tax_ledgerids);
    } else {
        errorreturn("Enter Invalid group id.");
    }
}
