<?php
function getSpecificStock($specific_db_conn, $stock_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_stockdetails  where id = ? and workspace_id =? and company_id = ? and branch_id = ? and book_id = ?");
    $sql->execute(array($stock_id, $workspace_id, $company_id, $branch_id, $book_id));
    $rows = $sql->fetchAll();

    if (count($rows) == 1) {

        $file = $rows[0]['filesattached'];
        if ($file != null) {
            $keyName = $file;
            $filesattached = displayFileFromS3($GLOBALS['s3_finware_bucket'], $GLOBALS['finware_bucketName'], $keyName);
        } else {
            $filesattached = null;
        }

        $sql_branch = $specific_db_conn->prepare("SELECT number_of_decimal_places FROM  branch_info  where   id=? and workspace_id =? and company_id = ? ");

        $sql_branch->execute(array($branch_id, $workspace_id, $company_id));
        $result_branch = $sql_branch->fetchAll();
        if (count($result_branch) == 1) {

            $number_of_decimal_places = $result_branch[0]['number_of_decimal_places'];
        } else {
            $number_of_decimal_places = 2;
        }
        $perprice = 0.00;
        if ($rows[0]['opening_balance'] != null) {

            $opening_balance = $rows[0]['opening_balance'];
            $opening_qty = $rows[0]['opening_qty'];
            if ($opening_qty != 0) {
                $perprice  = $opening_balance / $opening_qty;
                // $perprice = $per_price;
            }
        } else {

            $perprice = null;
        }

        $default_sp = $rows[0]['default_sp'];
        $opening_balance = $rows[0]['opening_balance'];

        // 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']);
            }
        }

        $region_id = $rows[0]['region_id'];


        $res = $specific_db_conn->prepare("SELECT name FROM inventory_region_details WHERE id = ? and workspace_id= ? and company_id = ? and branch_id= ? and book_id =?");
        $res->execute(array($region_id, $workspace_id, $company_id, $branch_id, $book_id));
        $result1 = $res->fetchAll();
        if (count($result1) == 1) {
            $region_name = $result1[0]['name'];


            return array(
                'stock_id' => $rows[0]['id'], 'stock_name' => $rows[0]['stock_name'], 'current_qty' => $rows[0]['current_qty'], 'hsn_code' => $rows[0]['hsn_code'], 'default_sp' => $default_sp,
                'unit_name' => $rows[0]['unit_name'], 'unit_id' => $rows[0]['unit_id'], 'default_tax_ledgerid' => $rows[0]['default_tax_ledgerid'], 'opening_qty' => $rows[0]['opening_qty'], 'opening_balance' => $opening_balance, 'per_price' => $perprice, 'parent_group' => $rows[0]['parent_group'], 'boolean_inventory' => $rows[0]['boolean_inventory'],
                'tax_applicable' => $rows[0]['tax_applicable'], 'filesattached' => $filesattached, 'region_id' => $region_id, 'region_name' => $region_name,
                'inter_tax_ledgerids' => $inter_tax_ledgerids, 'intra_tax_ledgerids' => $intra_tax_ledgerids, 'other_tax_ledgerids' => $other_tax_ledgerids, 'product_type' => $rows[0]['product_type']
            );
        }
    } else {
        errorreturn("Enter Invalid Stock ID");
    }
}

function GetBatchDetail($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $stock_id, $search_string, $type, $mindate, $maxdate, $limit, $offset)
{
    $sql = "Select count(*) as total_rows from inventory_batch_details where workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and date >= ? and date <= ? and (stock_barcode_uin like ? or remarks like ?)";
    $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate, '%' . $search_string . '%', '%' . $search_string . '%'];
    if ($type != "all") {
        $sql = $sql . " and type = ?";
        $array_params[] = $type;
    }
    $validate_batch = $specific_db_conn->prepare($sql);
    $validate_batch->execute($array_params);

    $result_total = $validate_batch->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    if ($limit == 'all') {

        if ($type != null) {

            if ($type == 'purchase' or $type == 'sales'  or $type == 'opening') {
                $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ? and type = ?  ORDER BY id ASC";
                $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate, $type];
            } elseif ($type == "all") {
                $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ?  ORDER BY id ASC";
                $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate];
            }
        } else {
            $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ? and (stock_barcode_uin LIKE ?   OR remarks LIKE ?)  ORDER BY id ASC ";
            $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate, '%' . $search_string . '%', '%' . $search_string . '%'];
        }
    } else {

        if ($type != null) {
            if ($type == 'purchase' or $type == 'sales'  or $type == 'opening') {
                $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ? and type = ?  ORDER BY id ASC  LIMIT " . $offset . " ," . $limit . "";
                $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate, $type];
            } elseif ($type == "all") {
                $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ?  ORDER BY id ASC  LIMIT " . $offset . " ," . $limit . "";
                $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate];
            }
        } else {

            $sql = "select *  FROM  inventory_batch_details WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and stock_id = ? and  date >= ? and date <= ? and (stock_barcode_uin LIKE ?   OR remarks LIKE ?)  ORDER BY id ASC  LIMIT " . $offset . " ," . $limit . "";
            $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $stock_id, $mindate, $maxdate, '%' . $search_string . '%', '%' . $search_string . '%'];
        }
    }

    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result = $stmt->fetchAll();
    $array_return = [];
    foreach ($result as $rows) {

        $array_return[] = array(
            "stock_id" => $rows['stock_id'], "stock_barcode_uin" => $rows['stock_barcode_uin'],  "type" => ucwords(strtolower($rows['type'])), "rate" => $rows['rate'], "qty" => $rows['qty'],
            "expiry" => $rows['expiry'], "mfg_date" => $rows['mfg_date'], "amount" => $rows['amount'], "remarks" => $rows['remarks']
        );
    }

    if (count($result) < 50) {
        return array('total_rows' => $totalrows, 'batch_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'batch_info' => $array_return, 'maxreach' => 0);
    }
}

function GetStockDetail($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $search_string, $additionaldata, $limit, $offset, $appended_region_bool, $book_opening_date, $book_closing_date)
{

    // Checking if any barcode matches with that of the search string 
    $sql_search_barcode = "SELECT stock_id, stock_barcode_uin FROM inventory_batch_details WHERE workspace_id = ? AND company_id = ? AND branch_id = ? AND book_id = ? AND stock_barcode_uin = ?";
    $search_barcode = $specific_db_conn->prepare($sql_search_barcode);
    $search_barcode->execute(array($workspace_id, $company_id, $branch_id, $book_id, $search_string));
    $result_barcode = $search_barcode->fetchAll();

    // Counting rows from inventory_stockdetails and related rows from inventory_batch_details
    $sql = "SELECT count(*) as total_rows
        FROM inventory_stockdetails
        WHERE workspace_id = ? 
        AND company_id = ? 
        AND branch_id = ? 
        AND book_id = ? 
        AND stock_name LIKE ?";
    $array_params = [$workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%'];

    $validate_ledger = $specific_db_conn->prepare($sql);
    $validate_ledger->execute($array_params);

    $result_total = $validate_ledger->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    // $sql = "Select * FROM  inventory_batch_details where workspace_id = ? and company_id =? and branch_id = ? and book_id=? and stock_barcode_uin = ? ";
    // $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $search_string];

    // $stmt = $specific_db_conn->prepare($sql);
    // $stmt->execute($array_params);
    // $result = $stmt->fetchAll();
    // if (count($result) >= 1) {
    //     $stock_barcode_uin = $result[0]['stock_barcode_uin'];
    // } else {

    //     $stock_barcode_uin =  null;
    // }


    $sql = "Select ";
    $array_counter = 0;
    foreach ($additionaldata as $column_name) {
        if ($array_counter == 0) {
            $sql = $sql . " " . $column_name;
        } else {
            $sql = $sql . ", " . $column_name;
        }
        $array_counter++;
    }
    // getting all the ids from search string
    $stock_ids_with_barcode = [];
    $stock_barcode_uin_array = [];
    foreach ($result_barcode as $temp_barcode) {
        $stock_ids_with_barcode[] = $temp_barcode['stock_id'];
        $stock_barcode_uin_array[] = $temp_barcode['stock_barcode_uin'];
    }
    $sql = $sql . " FROM  inventory_stockdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and (stock_name LIKE ?";
    if (count($stock_ids_with_barcode) > 0) {
        $sql .= "or id IN ('" . implode("','", $stock_ids_with_barcode) . "')";
    }
    $sql .= ") ";
    $array_params = [$workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%'];
    if ($limit != "all") {
        $sql = $sql . " LIMIT " . $offset . "," . $limit;
    }
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result = $stmt->fetchAll();


    $array_return = [];


    if (in_array('inter_tax_ledgerids', $additionaldata) or isset($additionaldata['inter_tax_ledgerids']) or in_array('other_tax_ledgerids', $additionaldata)) {
        // preparing query
        $sql_fetch_tax_details = "Select ledger_name, param_1 from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id =?";
        $fetch_tax_ledger_details = $specific_db_conn->prepare($sql_fetch_tax_details);
    }

    $tax_ledgerids_array = [];
    $tax_details_array = [];
    $items_array = array();
    $uniqueArrays = [];

    foreach ($result as $res) {
        $items_array[] = $res['id'];


        $fieldarray = [];
        // get the boolean inventory value here
        $boolean_inventory = $res['boolean_inventory'];
        foreach ($additionaldata as $column_name) {

            if (in_array($column_name, ['inter_tax_ledgerids', 'intra_tax_ledgerids', 'other_tax_ledgerids'])) {
                $temp_field__value = null;
                if (isset($res[$column_name]) && !empty($res[$column_name])) {
                foreach (explode(",", $res[$column_name]) as $tax_ledgerid_temp) {

                    if (in_array(str_replace("'", "", $tax_ledgerid_temp), $tax_ledgerids_array)) {
                        $temp_field__value[] = $tax_details_array[array_search(str_replace("'", "", $tax_ledgerid_temp), $tax_ledgerids_array) . ""];
                    } else {
                        $ledgerid = str_replace("'", "", $tax_ledgerid_temp);

                        // $sql_fetch_tax_details = "Select ledger_name, param_1 from act_ledgerdetails where workspace_id = ? and company_id =? and branch_id=? and book_id=? and id =?";
                        // $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, $ledgerid));
                        $result_tax_ledgerid = $fetch_tax_ledger_details->fetchAll();

                        if (count($result_tax_ledgerid) >= 1) {
                            //  print_r(count($result_tax_ledgerid));die();
                            $tax_ledgerids_array[] = $tax_ledgerid_temp;
                            $tax_details_array[] = array('id' => $ledgerid, 'name' => $result_tax_ledgerid[0]['ledger_name'], 'tax_percentage' => $result_tax_ledgerid[0]['param_1']);
                            $temp_field__value[] = $tax_details_array[count($tax_details_array) - 1];
                        } else {

                            //errorreturn("Error at establishing tax information for $tax_ledgerid_temp");
                        }
                    }
                }
                }
                $fieldarray[$column_name] = $temp_field__value;
            } else {
                // $fieldarray['stock_barcode_uin'] = $stock_barcode_uin;
                $fieldarray[$column_name] = $res[$column_name];

                if ($column_name == 'id') {
                    if (in_array($res['id'], $stock_ids_with_barcode)) {
                        $fieldarray['barcode_match'] = true;
                        $fieldarray['stock_barcode_uin'] = $stock_barcode_uin_array[array_search($res['id'], $stock_ids_with_barcode) . ""];
                    } else {
                        $fieldarray['barcode_match'] = false;
                    }
                }

                if ($column_name == 'region_id') {
                    if ($appended_region_bool == 1) {
                        // append to name if asked
                        $fieldarray['stock_name'] = $fieldarray['stock_name'];
                        $fieldarray['region_name'] = getRegionName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $res[$column_name]);
                    } else {
                        // send region_name seperately

                        $fieldarray['region_name'] = getRegionName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $res[$column_name]);
                    }
                }

                // if column name == 'current_qty'
                // // if inventory applicable , set field value as current_qty received
                // $fieldarray[$column_name] = $res[$column_name];
                // if not, set as null (NA)
                // $fieldarray[$column_name] = 'NA';

                // if column name == current balance }}
                // if invemntory applicable yes, then get specific stock summary fifi closing and set the value from there
                if ($column_name === 'current_qty') {
                    // Check if 'boolean_inventory' value is 0
                    if ($res['boolean_inventory'] == 0) {
                        $fieldarray['current_qty'] = 'NA';
                    } else {
                        // If 'boolean_inventory' is not 0 (considered true)
                        $fieldarray['current_qty'] = $res['current_qty'];
                    }
                }

                if ($column_name === 'current_balance') {
                    if ($res['boolean_inventory'] == 1) {
                        // Get 'closing_stock' from the specific stock summary
                        $fieldarray['current_balance'] = getAvgCpSummaryClosingStockSpecific($specific_db_conn, $book_opening_date, $book_closing_date, $workspace_id, $company_id, $branch_id, $book_id, [$res['id']])[0]['closing_stock'];
                    }
                }


                if ($column_name === 'filesattached') {

                    $fieldarray['filesattached'] = $res[$column_name];
                    if ($fieldarray['filesattached'] !=  null) {


                        $keyName = $fieldarray['filesattached'];
                        // $encodedKeyName = urlencode($keyName);
                        $presignedUrl = displayFileFromS3($GLOBALS['s3_finware_bucket'], $GLOBALS['finware_bucketName'], $keyName);

                        $fieldarray['filesattached'] = ($presignedUrl);
                    } else {
                        $fieldarray['filesattached'] =  null;
                    }
                }
            }
        }
        array_push($array_return, $fieldarray);
    }

    if (count($result) < 50) {

        return array('total_rows' => $totalrows, 'stock_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'stock_info' => $array_return, 'maxreach' => 0);
    }
}




function getStockRelativeDifference($newqty, $oldqty)
{


    return floatval($newqty - floatval($oldqty), 2, '.', '');
}

function deleteOnlyStockDetails($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $stock_id)
{
    $transaction_unique_ids = array();
    $res = $specific_db_conn->prepare("SELECT * FROM  inventory_stockdetails WHERE id = ? and workspace_id  =? and company_id=? and branch_id=? and book_id =?");
    $res->execute(array($stock_id, $workspace_id, $company_id, $branch_id, $book_id));
    $result1 = $res->fetchAll();

    $transaction_date = $result1[0]['date'];
    $existing_opening_qty = $result1[0]['opening_qty'];
    $existing_closing_balance = $result1[0]['closing_balance'];

    foreach ($result1 as $row) {
        $transaction_unique_ids[] = array('stock_id' => $row['id'], 'current_qty' => $row['current_qty'], 'opening_qty' => $row['opening_qty']);
    }

    $sql_update_stockdetails = "Update inventory_stockdetails SET current_qty =  current_qty + ? where id = ? and workspace_id = ? and company_id =? and branch_id =? and book_id =? ";
    $update_stockdetails = $specific_db_conn->prepare($sql_update_stockdetails);

    foreach ($transaction_unique_ids as $row1) {

        $stock_id = $row1['stock_id'];
        $current_qty = $row1['current_qty'];

        $opening_qty = $row1['opening_qty'];
        //  $diff = $balancetype == 'dr' ? -$amount : $amount;

        $diff = getStockRelativeDifference($opening_qty, $existing_opening_qty);
        $update_stockdetails->execute(array($current_qty, $stock_id, $workspace_id, $company_id, $branch_id, $book_id));
    }


    $sql = "DELETE FROM inventory_stockdetails WHERE id=? and workspace_id = ? and company_id =? and branch_id =? and book_id =? ";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($stock_id, $workspace_id, $company_id, $branch_id, $book_id));
}
