<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of production
 *
 * @author User
 */
class production
{

    protected $info = array();
    private $db;
    private $year_id;
    private $year_id_actual;

    public function __construct()
    {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
    }

    public function setData($data)
    {
        $this->info = $data;
        return $this;
    }

    public function add_production_template()
    {
        $this->db->query('START TRANSACTION');
        $this->info['out_product_ids'] = empty($this->info['out_product_ids']) ? '' : $this->info['out_product_ids'];
        $this->info['in_product_ids'] = empty($this->info['in_product_ids']) ? '' : $this->info['in_product_ids'];
        $data_main = [
            &$this->info['template_name'],
            &$this->info['Currency_ID'],
            &$this->info['out_product_ids'],
            &$this->info['in_product_ids'],
            &$this->info['is_line_required'],
            &$this->info['production_emp_type_ids'],
            &$this->info['production_category_id'], $this->info['template_type']
        ];
        $this->db->prepare_Stmt('insert into tbl_production_template (`template_name`,`Currency_ID`,
            product_ids,produced_product_ids,is_line_required,production_emp_type_ids,category_id,template_type)
            values(?,?,?,?,?,?,?,?)', $data_main);
        $template_id = $this->db->id();
        $i = 'A';
        foreach ($this->info['items'] as $item) {
            $formula = $item['value_type'] == 2 ? $item['item_qty'] : '';
            $qty = $item['value_type'] == 1 ? $item['item_qty'] : 0;
            $item['optional'] = !empty($item['optional']) ? $item['optional'] : 0;
            $item['group'] = !empty($item['group']) ? $item['group'] : '';
            $default_quantity = Product::getConvertedQuantity($item['item_uom'], $qty, $item['item_id']);
            $temp_identifier = !empty($item['identifier']) ? '' : ('trustoPT' . $i++);
            $data_items = [
                &$template_id, &$item['item_id'], &$item['item_uom'],
                &$qty, &$default_quantity, &$item['operation'], &$item['item_depot'],
                &$item['identifier'], $temp_identifier, $formula, $item['value_type'], $item['optional'], $item['group']
            ];
            $this->db->prepare_Stmt('insert into tbl_production_template_details (`template_id`,`Product_ID`,
                `UOM_ID`,`current_quantity`,`default_quantity`,`operation`,`Depot_ID`,identifier,temp_identifier,
                formula,value_type,optional,group_name)
                values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_items);
        }
        if (!empty($this->info['expense_arr'])) {
            foreach ($this->info['expense_arr'] as $ex) {
                $data_expenses = [
                    &$template_id, &$ex['account_id'], &$ex['expense_cost'],
                    &$ex['is_total']
                ];
                $this->db->prepare_Stmt('insert into tbl_production_template_expenses (`template_id`,
                `Account_ID`, `cost`, `is_total`)
                values(?,?,?,?)', $data_expenses);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_production_template_id($id, $with_optional)
    {
        //        echo ($with_optional ? 1 : 0).'==';die();
        $data = array(
            'main' => array(), 'details' => array(), 'expense' => array(),
            'variables' => array(), 'id_val' => array(), 'id_content' => array()
        );
        $query_template = $this->db->prepare_Stmt('select t.*,c.*,
            ifnull(cat.category_name,"") as category_name from tbl_production_template t 
            join tbl_common_currency c using(Currency_ID)
            left join tbl_production_category cat on cat.category_id = t.category_id
            where t.template_id = ?', [&$id]);
        $data['main'] = $this->db->assoc($query_template);
        $data['products'] = $data['production_emp_type'] = $data['produced_products'] = array();
        if (!empty($data['main']['product_ids'])) {
            $query = $this->db->query('select concat_ws(" - ",Product_Name,Product_Code) as text,Product_ID as id 
                from tbl_stock_products where Product_ID in (' . $data['main']['product_ids'] . ')');
            while ($row = $this->db->assoc($query)) {
                $data['products'][] = $row;
            }
        }
        if (!empty($data['main']['produced_product_ids'])) {
            $query = $this->db->query('select concat_ws(" - ",Product_Name,Product_Code) as text,Product_ID as id 
                from tbl_stock_products where Product_ID in (' . $data['main']['produced_product_ids'] . ')');
            while ($row = $this->db->assoc($query)) {
                $data['produced_products'][] = $row;
            }
        }
        if (!empty($data['main']['production_emp_type_ids'])) {
            $query = $this->db->query('select type_name  as text,type_id as id 
                from tbl_production_emp_types where type_id in (' . $data['main']['production_emp_type_ids'] . ')');
            while ($row = $this->db->assoc($query)) {
                $data['production_emp_type'][] = $row;
            }
        }
        $data['main']['groups'] = array();
        $query_detail = $this->db->prepare_Stmt('select d.*,p.is_lot,p.is_serial,u.UOM_Name,p.Product_Name,p.Status_ID,w.Depot_Name,p.is_expiry,p.is_service
            from tbl_production_template_details d
            join tbl_stock_products p on p.Product_ID = d.Product_ID 
            join tbl_stock_uom u on u.UOM_ID = d.UOM_ID 
            join tbl_stock_depot_warehouse w on w.Depot_ID = d.Depot_ID
            where d.template_id = ?', [&$id]);
        while ($row_detail = $this->db->assoc($query_detail)) {
            $cost = CommonBusiness::viewCurrentProductCost($row_detail['Product_ID']);
            $row_detail['current_cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['main']['Currency_ID'], $cost);
            $row_detail['new_identifier'] = !empty($row_detail['identifier']) ? $row_detail['identifier'] : $row_detail['temp_identifier'];
            if ($row_detail['group_name'] != '' && !in_array($row_detail['group_name'], $data['main']['groups']))
                $data['main']['groups'][] = $row_detail['group_name'];
            if ($row_detail['optional'] == 0 || $with_optional == 1)
                $data['details'][] = $row_detail;
            if (!empty($row_detail['formula'])) {
                $matches = array();
                preg_match_all('/[a-zA-Z]+/', $row_detail['formula'], $matches);
                foreach ($matches[0] as $match) {
                    $data['variables'][$match][] = $row_detail['new_identifier'];
                    $data['id_content'][$row_detail['new_identifier']][] = $match;
                }
            }
            $data['id_val'][$row_detail['new_identifier']] = 0;
        }

        $query_expense = $this->db->prepare_Stmt('select e.*,a.Account_Name from tbl_production_template_expenses e
            join tbl_chart_of_accounts a  using(Account_ID) where e.template_id = ?', [&$id]);
        while ($row_exp = $this->db->assoc($query_expense)) {
            $data['expense'][] = $row_exp;
        }
        return $data;
    }

    public function update_production_template()
    {
        $this->db->error('START TRANSACTION');
        $this->info['out_product_ids'] = empty($this->info['out_product_ids']) ? '' : $this->info['out_product_ids'];
        $this->info['in_product_ids'] = empty($this->info['in_product_ids']) ? '' : $this->info['in_product_ids'];
        $data_main = [
            &$this->info['template_name'],
            &$this->info['Currency_ID'],
            &$this->info['out_product_ids'],
            &$this->info['in_product_ids'],
            &$this->info['is_line_required'],
            &$this->info['production_emp_type_ids'],
            &$this->info['production_category_id'],
            &$this->info['template_id']
        ];
        $this->db->prepare_Stmt('update tbl_production_template set
            `template_name` = ?,`Currency_ID` = ?,product_ids=?,produced_product_ids=?,is_line_required = ?,
            production_emp_type_ids = ?,category_id=? where template_id = ?', $data_main);
        $this->db->prepare_Stmt('delete from tbl_production_template_details where template_id = ?', [&$this->info['template_id']]);
        $this->db->prepare_Stmt('delete from tbl_production_template_expenses where template_id = ?', [&$this->info['template_id']]);
        $i = 'A';
        foreach ($this->info['items'] as $item) {
            $formula = $item['value_type'] == 2 ? $item['item_qty'] : '';
            $qty = $item['value_type'] == 1 ? $item['item_qty'] : 0;
            $item['optional'] = !empty($item['optional']) ? $item['optional'] : 0;
            $item['group'] = !empty($item['group']) ? $item['group'] : '';
            $temp_identifier = !empty($item['identifier']) ? '' : ('trustoPT' . $i++);
            $default_quantity = Product::getConvertedQuantity($item['item_uom'], $qty, $item['item_id']);
            $data_items = [
                &$this->info['template_id'], &$item['item_id'], &$item['item_uom'],
                &$item['item_qty'], &$default_quantity, &$item['operation'], &$item['item_depot'],
                &$item['identifier'], $temp_identifier, $formula, $item['value_type'], $item['optional'], $item['group']
            ];
            $this->db->prepare_Stmt('insert into tbl_production_template_details (`template_id`,`Product_ID`,
                `UOM_ID`,`current_quantity`,`default_quantity`,`operation`,`Depot_ID`,identifier,temp_identifier,
                formula,value_type,optional,group_name)
                values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_items);
        }
        if (!empty($this->info['expense_arr'])) {
            foreach ($this->info['expense_arr'] as $ex) {
                $data_expenses = [
                    &$this->info['template_id'], &$ex['account_id'], &$ex['expense_cost'],
                    &$ex['is_total']
                ];
                $this->db->prepare_Stmt('insert into tbl_production_template_expenses (`template_id`,
                `Account_ID`, `cost`, `is_total`)
                values(?,?,?,?)', $data_expenses);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function templateSearch($term = null, $page, $limit)
    {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where template_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT template_id as id, template_name as text 
            FROM tbl_production_template' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function searchProductionEmpType($term = null, $page, $limit)
    {
        $data = array();
        $cond = '';
        if (!empty($this->info['template_id'])) {
            $query = $this->db->query('select production_emp_type_ids 
                from tbl_production_template where template_id ="' . $this->info['template_id'] . '"');
            $row = $this->db->assoc($query)['production_emp_type_ids'];
            $cond = !empty($row) ? ' and type_id in (' . $row . ')' : ' and 1=2 ';
        } else {
            $cond = ' 1=2';
        }
        $data['is_more'] = false;
        $data['data'] = array();
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and type_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT type_id as id, type_name as text 
            FROM tbl_production_emp_types where 1=1 ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function add_production_process()
    {
        $this->db->query('START TRANSACTION');
        $barcode = date("ymd");
        $query_barcode = $this->db->query('select  barcode
        from tbl_production_process where barcode = "' . $barcode . '"');
        $num = $this->db->rowsno($query_barcode);
        if ($num == null)
            $num = 0;
        $num += 1;
        $order_nb = str_pad("$num", 3, "0", STR_PAD_LEFT);
        $full_barcode = $barcode . '' . $order_nb;
        $barcode_arr = [
            $full_barcode
        ];
        $this->db->prepare_Stmt('insert into tbl_production_barcode (`barcode`) values(?)', $barcode_arr);
        $cost_date = date("Y-m-d H:i:s");
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $main = [
            &$this->info['template_id'],
            &$this->info['date'],
            &$cost_date,
            &$this->info['Currency_ID'],
            &$rate,
            &$this->info['total_cost'],
            &$this->info['process_note'],
            &$this->info['process_qty'],
            &$this->info['line_id'],
            &$this->year_id,
            &$this->info['states_name'],
            &smc_session()['user_logged_id'],
            $barcode,
            $order_nb,
            $full_barcode,
            &$this->info['production_color']
        ];
        $this->db->prepare_Stmt('insert into tbl_production_process (`template_id`, `date`, `date_time`, `Currency_ID`, `current_rate`,
            `total_cost`, `process_note`, `process_quantity`,line_id, `current_year_id`,
            `states_name`,`Logged_ID`,`barcode`,`order_nb`,`barcode_nb`,`production_color_id`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $main);

        $process_id = $this->db->id();
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array();
        foreach ($this->info['items'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $item['item_lot'] = !empty($item['item_lot']) ? $item['item_lot'] : '';
            $item['item_serial'] = !empty($item['item_serial']) ? $item['item_serial'] : '';
            $item['is_service'] = !empty($item['is_service']) ? $item['is_service'] : 0;
            if (empty($depot_arr[$item['item_depot']]))
                $depot_arr[$item['item_depot']] = $item['item_depot'];
            $default_quantity = Product::getConvertedQuantity($item['item_uom'], $item['item_qty'], $item['item_id']);
            $data_items = [
                &$process_id, &$item['item_id'], &$item['item_uom'], &$item['initial_qty'],
                &$item['item_qty'], &$default_quantity, &$item['operation'], &$item['item_cost'],
                &$item['item_depot'], &$expiry_date, &$item['item_lot'], &$item['item_serial'],
                &$item['formula'], &$item['identifier']
            ];
            //            echo"<pre>";print_r($data_items);
            $this->db->prepare_Stmt('insert into tbl_production_process_details (`process_id`, `Product_ID`,
                `UOM_ID`,initial_current_quantity, `current_quantity`, `default_quantity`, `operation`, `cost`, 
                `Depot_ID`,expiry_date,lot_nb,serial_nb,formula,identifier)
                values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_items);
            $costdata = [
                "Product_ID" => $item['item_id'],
                "Quantity" => $default_quantity,
                "Unit_Cost" => $item['item_cost'],
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $cost_date,
                "current_rate" => $rate,
                "depot_id" => $item['item_depot'],
                "trans_date" => $this->info['date'],
                "current_quantity" => $item['item_qty'],
                "uom_id" => $item['item_uom'],
                "is_actual" => 0,
                "lot_nb" => $item['item_lot'],
                "serial_nb" => $item['item_serial'],
                "expiry_date" => $expiry_date,
                "main_id" => $process_id,
                "is_service" => $item['is_service']
            ];
            if ($this->info['states_name'] == 'Finished') {

                CommonBusiness::updateLinkQuantities($costdata, ($item['operation'] == '+' ? 'add_produced_items' : 'add_raw_items'), $item['operation']);
                if ($item['operation'] == '-') {
                    if (!empty($expiry_date) && !empty(smc_session()['default_setting']['activate_expiry_qty_exist'])) {
                        $expiry_valid_arr['expiry'][] = $expiry_date;
                        if (!isset($expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date]))
                            $expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date] = 0;
                        $expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date] += $default_quantity;
                    }
                    if (!empty($item['item_serial']) && !empty(smc_session()['default_setting']['activate_serial_qty_exist'])) {
                        $serial_valid_arr['serial'][] = $item['item_serial'];
                        if (!isset($serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']]))
                            $serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']] = 0;
                        $serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']] += $default_quantity;
                    }
                    if (!empty($item['item_lot']) && !empty(smc_session()['default_setting']['activate_lot_qty_exist'])) {
                        $lot_valid_arr['lot'][] = $item['item_lot'];
                        if (!isset($lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']]))
                            $lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']] = 0;
                        $lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']] += $default_quantity;
                    }
                    if (!empty($default_quantity) && empty(smc_session()['default_setting']['negative_sales'])) {
                        if (!isset($qty_valid_arr[$item['item_id']][$item['item_depot']]))
                            $qty_valid_arr[$item['item_id']][$item['item_depot']] = 0;
                        $qty_valid_arr[$item['item_id']][$item['item_depot']] += $default_quantity;
                    }
                }
            }
        }

        if ($this->info['states_name'] == 'Finished'  && smc_session()['default_setting']['enable_project_production_process'] == 1) {
            $branch_id =   empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id'];
            $second_rate =   CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
            $jv = JV::returnJV(0);
            $ref = 'produ.' . $process_id;
            $logged_id = smc_session()['user_logged_id'];
            $date_time =  date('Y-m-d H:i:s');
            $Transaction_Date =  date('Y-m-d');

            $period_query = $this->db->query('  select *  from tbl_project_period where period_id = "' . $this->info['period_id'] . '"');
            $period_row = $this->db->assoc($period_query);
            $flag = 0;
            $activity_accounts = array();
            $activity_query = $this->db->query(' select * from tbl_activity_tree where activity_id =  ' .  $this->info['activity_id'] . ' ');
            while ($row = $this->db->assoc($activity_query)) {
                $activity_accounts[$row['activity_id']] = $row['account_id'];
            }
            $supp_acc_id = Supplier::get_all_aupp_accs($this->info['supplier_id'])['exp'];


            $dat_2 = [
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $supp_acc_id,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $this->info['total_cost'],
                    "Transaction_Date" => $Transaction_Date,
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "date_created" => $date_time,
                    "date_time" => $date_time,
                    "Transaction_Description" => ' production process ',
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $process_id,
                    "main_type" => 'pex',
                    "branch_id" => $branch_id,
                    "main_nb" => $process_id,
                    "job_id" => null,
                    "project_id" => $period_row['project_id'],
                ];

            JV::addTransaction($dat_2, $flag);
            $dat_1 = [
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $activity_accounts[$this->info['activity_id']],
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $this->info['total_cost'],
                    "Transaction_Date" => $Transaction_Date,
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "date_created" => $date_time,
                    "date_time" => $date_time,
                    "Transaction_Description" => ' production process '.$this->info['process_note'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $process_id,
                    "main_type" => 'pex',
                    "branch_id" => $branch_id,
                    "main_nb" => $process_id,
                    "job_id" => null,
                    "project_id" => $period_row['project_id'],
                    "activity_id" => $this->info['activity_id'],
                ];
            JV::addTransaction($dat_1, $flag);


            // if paid

            $dat_2 = [
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $supp_acc_id,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $this->info['paid_amount'],
                    "Transaction_Date" => $Transaction_Date,
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "date_created" => $date_time,
                    "date_time" => $date_time,
                    "Transaction_Description" => 'production process ',
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $process_id,
                    "main_type" => 'pex',
                    "branch_id" => $branch_id,
                    "main_nb" => $process_id,
                    "job_id" => null,
                    "project_id" => $period_row['project_id'],
                ];
            JV::addTransaction($dat_2, $flag);
            $dat_2 = [
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $this->info['payment_acc_id'],
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $this->info['paid_amount'],
                    "Transaction_Date" => $Transaction_Date,
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "date_created" => $date_time,
                    "date_time" => $date_time,
                    "Transaction_Description" => 'production process ',
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $process_id,
                    "main_type" => 'pex',
                    "branch_id" => $branch_id,
                    "main_nb" => $process_id,
                    "job_id" => null,
                    "project_id" => $period_row['project_id'],
                ];
            JV::addTransaction($dat_2, $flag);
        }
        if (!empty($expiry_valid_arr)) {
            $expiry_valid = CommonBusiness::validate_expiry_qty_exist($expiry_valid_arr, $depot_arr);
            if (!empty($expiry_valid)) {
                $this->db->query('ROLLBACK');
                return ['expiry_error', $expiry_valid];
            }
        }
        if (!empty($lot_valid_arr)) {
            $lot_valid = CommonBusiness::validate_lot_qty_exist($lot_valid_arr, $depot_arr);
            if (!empty($lot_valid)) {
                $this->db->query('ROLLBACK');
                return ['lot_error', $lot_valid];
            }
        }
        if (!empty($serial_valid_arr)) {
            $serial_valid = CommonBusiness::validate_serial_qty_exist($serial_valid_arr, $depot_arr);
            if (!empty($serial_valid)) {
                $this->db->query('ROLLBACK');
                return ['serial_error', $serial_valid];
            }
        }
        if (!empty($qty_valid_arr)) {
            $qty_valid = CommonBusiness::validate_product_qty_exist($qty_valid_arr, $depot_arr);
            if (!empty($qty_valid)) {
                $this->db->query('ROLLBACK');
                return ['qty_error', $qty_valid];
            }
        }
        //   $error = $this->db->error();
        //                    echo"<pre>";
        //        die(print_r($error));
        if (!empty($this->info['expense_arr'])) {
            foreach ($this->info['expense_arr'] as $ex) {
                $data_expenses = [
                    &$process_id, &$ex['account_id'], &$ex['expense_cost'], &$ex['total_cost'],
                    &$ex['is_total']
                ];
                $this->db->prepare_Stmt('insert into tbl_production_process_expenses (`process_id`, `Account_ID`,
                `cost`, `total_cost`, `is_total`)
                values(?,?,?,?,?)', $data_expenses);
            }
        }
        if (!empty($this->info['production_employee_type'])) {
            foreach ($this->info['production_employee_type'] as $row) {
                $data_row = [&$process_id, &$row['type_id'], &$row['employee_id'], &$row['cost']];
                $this->db->prepare_Stmt('insert into tbl_production_process_employee_type (`process_id`, `type_id`,
                `employee_id`,cost)
                values(?,?,?,?)', $data_row);
            }
        }

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_production_process_id($id)
    {
        $data = array(
            "main" => array(), "details" => array(), "plus" => array(),
            "minus" => array(), "expense" => array(), "employee_type" => array(),
            "variables" => array(), "id_val" => array(), "id_content" => array()
        );
        $query_template = $this->db->query('select co.*,p.*,c.*,t.template_name,t.production_emp_type_ids,line.line_name 
        from tbl_production_process p 
            join tbl_production_template t using(template_id)
            join tbl_common_currency c on c.Currency_ID = p.Currency_ID
            left join tbl_line line using(line_id)
            LEFT JOIN tbl_production_color co
            ON co.id = p.production_color_id
            where p.process_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_template)) {
            $data['main'] = $row;
        }
        //        
        $query_detail = $this->db->prepare_Stmt('select d.*,u.UOM_Name,p.Product_Name,w.Depot_Name,p.is_expiry,
            p.is_lot,p.is_serial
            from tbl_production_process_details d
            join tbl_stock_products p on p.Product_ID = d.Product_ID 
            join tbl_stock_uom u on u.UOM_ID = d.UOM_ID 
            join tbl_stock_depot_warehouse w on w.Depot_ID = d.Depot_ID
            where d.process_id = ?', [&$id]);
        while ($row_detail = $this->db->assoc($query_detail)) {
            $cost = CommonBusiness::viewCurrentProductCost($row_detail['Product_ID']);
            $row_detail['current_cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['main']['Currency_ID'], $cost);
            $type = $row_detail['operation'] == '+' ? 'plus' : 'minus';
            $data[$type][$row_detail['Product_ID']]['Product_ID'] = $row_detail['Product_ID'];
            $data[$type][$row_detail['Product_ID']]['Product_Name'] = $row_detail['Product_Name'];
            $data[$type][$row_detail['Product_ID']]['UOM_Name'] = $row_detail['UOM_Name'];
            $data[$type][$row_detail['Product_ID']]['initial_current_quantity'] = $row_detail['initial_current_quantity'];
            $data[$type][$row_detail['Product_ID']]['is_expiry'] = $row_detail['is_expiry'];
            $data[$type][$row_detail['Product_ID']]['is_serial'] = $row_detail['is_serial'];
            $data[$type][$row_detail['Product_ID']]['is_lot'] = $row_detail['is_lot'];
            $data[$type][$row_detail['Product_ID']]['details'][] = $row_detail;
            $row_detail['new_identifier'] = $row_detail['identifier'];

            $data['details'][] = $row_detail;
            if (!empty($row_detail['formula'])) {
                $matches = array();
                preg_match_all('/[a-zA-Z]+/', $row_detail['formula'], $matches);
                foreach ($matches[0] as $match) {
                    $data['variables'][$match][] = $row_detail['new_identifier'];
                    $data['id_content'][$row_detail['new_identifier']][] = $match;
                }
                $data['id_val'][$row_detail['new_identifier']] = 0;
            }
        }

        $query_expense = $this->db->prepare_Stmt('select e.*,a.Account_Name from tbl_production_process_expenses e
            join tbl_chart_of_accounts a  using(Account_ID) where e.process_id = ?', [&$id]);
        while ($row_exp = $this->db->assoc($query_expense)) {
            $data['expense'][] = $row_exp;
        }
        if (!empty($data['main']['production_emp_type_ids'])) {
            $production_emp_type_query = $this->db->query('select current_type.type_id,current_type.type_name
            from tbl_production_emp_types current_type 
            left join tbl_production_process_employee_type prod_emp_type 
            on current_type.type_id = prod_emp_type.type_id and prod_emp_type.process_id = "' . $id . '"
            where current_type.type_id in (' . $data['main']['production_emp_type_ids'] . ')');
            while ($row = $this->db->assoc($production_emp_type_query)) {
                $data['employee_type'][$row['type_id']] = ['type_id' => $row['type_id'], 'type_name' => $row['type_name'], 'employees' => array()];
            }
        }
        $query_emp_type = $this->db->prepare_Stmt('select employee_type.*,
        concat_ws(" ",Employee_FirstName,Employee_LastName) as employee_name
        from  tbl_production_process_employee_type employee_type
        inner join tbl_hrm_employee employee on employee.Employee_ID = employee_type.employee_id
        where employee_type.process_id = ? ', [&$id]);
        while ($row_emp_type = $this->db->assoc($query_emp_type)) {
            //            $data['employee_type'][$row_emp_type['type_id']]['type_id'] = $row_emp_type['type_id'];
            //            $data['employee_type'][$row_emp_type['type_id']]['type_name'] = $row_emp_type['type_name'];
            $data['employee_type'][$row_emp_type['type_id']]['employees'][] = $row_emp_type;
        }
        $data['employee_type'] = array_values($data['employee_type']);
        return $data;
    }

    public function update_production_process()
    {
        $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d h:i:s a");
        $cost_date = date("Y-m-d H:i:s");
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $process_id = $this->info['process_id'];

        $main = [
            &$this->info['template_id'],
            &$this->info['date'],
            &$this->info['Currency_ID'],
            &$rate,
            &$this->info['total_cost'],
            &$this->info['process_note'],
            &$this->info['process_qty'],
            &$this->info['line_id'],
            &$this->info['states_name'],
            &$this->info['production_color'],
            &$process_id

        ];
        $this->db->prepare_Stmt('update tbl_production_process set  `template_id` = ?,`date` = ?,
            `Currency_ID` = ?, `current_rate` = ?,`total_cost` = ?, `process_note` = ?,
            `process_quantity` = ?,line_id = ?,states_name= ?,production_color_id=? where `process_id` = ?', $main);
        $query_det = $this->db->prepare_Stmt('select d.*,p.Currency_ID,p.date_time,p.date,p.current_rate,pr.is_service
            from tbl_production_process_details d
            join tbl_production_process p using(process_id)
            join tbl_stock_products pr on pr.Product_ID = d.Product_ID
                                        where process_id = ?', [&$process_id]);


        /*while ($row_det = $this->db->assoc($query_det)) {
            $old_expiry_date = !empty($row_det['expiry_date']) ? $row_det['expiry_date'] : null;
            $costdata = [
                "Product_ID" => $row_det['Product_ID'],
                "Quantity" => $row_det['default_quantity'],
                "Unit_Cost" => $row_det['cost'],
                "Currency_ID" => $row_det['Currency_ID'],
                "date_time" => $row_det['date_time'],
                "current_rate" => $row_det['current_rate'],
                "depot_id" => $row_det['Depot_ID'],
                "trans_date" => $row_det['date'],
                "current_quantity" => $row_det['current_quantity'],
                "uom_id" => $row_det['UOM_ID'],
                "is_actual" => 0,
                'lot_nb' => $row_det['lot_nb'],
                'serial_nb' => $row_det['serial_nb'],
                "expiry_date" => $old_expiry_date,
                "main_id" => $process_id,
                "is_service" => $row_det['is_service']
            ];
            CommonBusiness::updateLinkQuantities($costdata, ($row_det['operation'] == '+' ? 'edit_produced_items_old' : 'edit_raw_items_old'), ($row_det['operation'] == '+' ? '-' : '+'));
        }*/


        $this->db->prepare_Stmt('delete from tbl_production_process_details where process_id = ?', [&$process_id]);
        CommonBusiness::update_trans_record_current('prd', $process_id);
        CommonBusiness::update_trans_record_current('raw', $process_id);
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array();
        foreach ($this->info['items'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $item['item_lot'] = !empty($item['item_lot']) ? $item['item_lot'] : '';
            $item['item_serial'] = !empty($item['item_serial']) ? $item['item_serial'] : '';
            $item['is_service'] = !empty($item['is_service']) ? $item['is_service'] : 0;
            if (empty($depot_arr[$item['item_depot']]))
                $depot_arr[$item['item_depot']] = $item['item_depot'];
            $default_quantity = Product::getConvertedQuantity($item['item_uom'], $item['item_qty'], $item['item_id']);
            $data_items = [
                &$process_id, &$item['item_id'], &$item['item_uom'], &$item['initial_qty'],
                &$item['item_qty'], &$default_quantity, &$item['operation'], &$item['item_cost'],
                &$item['item_depot'], &$expiry_date, &$item['item_lot'], &$item['item_serial'],
                &$item['formula'], &$item['identifier']
            ];
            //            echo"<pre>";print_r($data_items);
            $this->db->prepare_Stmt('insert into tbl_production_process_details (`process_id`, `Product_ID`,
                    `UOM_ID`,initial_current_quantity, `current_quantity`, `default_quantity`, `operation`, `cost`, 
                    `Depot_ID`,expiry_date,lot_nb,serial_nb,formula,identifier)
                    values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_items);
            $costdata = [
                "Product_ID" => $item['item_id'],
                "Quantity" => $default_quantity,
                "Unit_Cost" => $item['item_cost'],
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $cost_date,
                "current_rate" => $rate,
                "depot_id" => $item['item_depot'],
                "trans_date" => $this->info['date'],
                "current_quantity" => $item['item_qty'],
                "uom_id" => $item['item_uom'],
                "is_actual" => 0,
                "lot_nb" => $item['item_lot'],
                "serial_nb" => $item['item_serial'],
                "expiry_date" => $expiry_date,
                "main_id" => $process_id,
                "is_service" => $item['is_service']
            ];
            if ($this->info['states_name'] == 'Finished') {
                CommonBusiness::updateLinkQuantities($costdata, ($item['operation'] == '+' ? 'add_produced_items' : 'add_raw_items'), $item['operation']);
                if ($item['operation'] == '-') {
                    if (!empty($expiry_date) && !empty(smc_session()['default_setting']['activate_expiry_qty_exist'])) {
                        $expiry_valid_arr['expiry'][] = $expiry_date;
                        if (!isset($expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date]))
                            $expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date] = 0;
                        $expiry_valid_arr['prod'][$item['item_id']][$item['item_depot']][$expiry_date] += $default_quantity;
                    }
                    if (!empty($item['item_serial']) && !empty(smc_session()['default_setting']['activate_serial_qty_exist'])) {
                        $serial_valid_arr['serial'][] = $item['item_serial'];
                        if (!isset($serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']]))
                            $serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']] = 0;
                        $serial_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_serial']] += $default_quantity;
                    }
                    if (!empty($item['item_lot']) && !empty(smc_session()['default_setting']['activate_lot_qty_exist'])) {
                        $lot_valid_arr['lot'][] = $item['item_lot'];
                        if (!isset($lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']]))
                            $lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']] = 0;
                        $lot_valid_arr['prod'][$item['item_id']][$item['item_depot']][$item['item_lot']] += $default_quantity;
                    }
                    if (!empty($default_quantity) && empty(smc_session()['default_setting']['negative_sales'])) {
                        if (!isset($qty_valid_arr[$item['item_id']][$item['item_depot']]))
                            $qty_valid_arr[$item['item_id']][$item['item_depot']] = 0;
                        $qty_valid_arr[$item['item_id']][$item['item_depot']] += $default_quantity;
                    }
                }
            }
        }





        if ($this->info['states_name'] == 'Finished' && smc_session()['default_setting']['enable_project_production_process'] == 1) {
            $branch_id =   empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id'];
            $second_rate =   CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
            $jv = JV::returnJV(0);
            $ref = 'produ.' . $process_id;
            $logged_id = smc_session()['user_logged_id'];
            $date_time =  date('Y-m-d H:i:s');
            $Transaction_Date =  date('Y-m-d');

            $period_query = $this->db->query('
            select * 
            from tbl_project_period where period_id = "' . $this->info['period_id'] . '"');
            $period_row = $this->db->assoc($period_query);
            $flag = 0;
            $activity_accounts = array();
            $activity_query = $this->db->query('
            select * 
            from tbl_activity_tree where activity_id =  ' .  $this->info['activity_id'] . ' ');
            while ($row = $this->db->assoc($activity_query)) {
                $activity_accounts[$row['activity_id']] = $row['account_id'];
            }
            $supp_acc_id = Supplier::get_all_aupp_accs($this->info['supplier_id'])['exp'];


            $dat_2 = [
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $supp_acc_id,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['total_cost'],
                "Transaction_Date" => $Transaction_Date,
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => ' production process ',
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $process_id,
                "main_type" => 'pex',
                "branch_id" => $branch_id,
                "main_nb" => $process_id,
                "job_id" => null,
                "project_id" => $period_row['project_id'],
            ];

            JV::addTransaction($dat_2, $flag);
            $dat_1 = [
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $activity_accounts[$this->info['activity_id']],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['total_cost'],
                "Transaction_Date" => $Transaction_Date,
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => ' production process '.$this->info['process_note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $process_id,
                "main_type" => 'pex',
                "branch_id" => $branch_id,
                "main_nb" => $process_id,
                "job_id" => null,
                "project_id" => $period_row['project_id'],
                "activity_id" => $this->info['activity_id'],
            ];
            JV::addTransaction($dat_1, $flag);


            // if paid

            $dat_2 = [
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $supp_acc_id,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['paid_amount'],
                "Transaction_Date" => $Transaction_Date,
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => 'production process ',
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $process_id,
                "main_type" => 'pex',
                "branch_id" => $branch_id,
                "main_nb" => $process_id,
                "job_id" => null,
                "project_id" => $period_row['project_id'],
            ];
            JV::addTransaction($dat_2, $flag);
            $dat_2 = [
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $this->info['payment_acc_id'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['paid_amount'],
                "Transaction_Date" => $Transaction_Date,
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => 'production process ',
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $process_id,
                "main_type" => 'pex',
                "branch_id" => $branch_id,
                "main_nb" => $process_id,
                "job_id" => null,
                "project_id" => $period_row['project_id'],
            ];
            JV::addTransaction($dat_2, $flag);
        }   
        if (!empty($expiry_valid_arr)) {
            $expiry_valid = CommonBusiness::validate_expiry_qty_exist($expiry_valid_arr, $depot_arr);
            if (!empty($expiry_valid)) {
                $this->db->query('ROLLBACK');
                return ['expiry_error', $expiry_valid];
            }
        }
        if (!empty($lot_valid_arr)) {
            $lot_valid = CommonBusiness::validate_lot_qty_exist($lot_valid_arr, $depot_arr);
            if (!empty($lot_valid)) {
                $this->db->query('ROLLBACK');
                return ['lot_error', $lot_valid];
            }
        }
        if (!empty($serial_valid_arr)) {
            $serial_valid = CommonBusiness::validate_serial_qty_exist($serial_valid_arr, $depot_arr);
            if (!empty($serial_valid)) {
                $this->db->query('ROLLBACK');
                return ['serial_error', $serial_valid];
            }
        }
        if (!empty($qty_valid_arr)) {
            $qty_valid = CommonBusiness::validate_product_qty_exist($qty_valid_arr, $depot_arr);
            if (!empty($qty_valid)) {
                $this->db->query('ROLLBACK');
                return ['qty_error', $qty_valid];
            }
        }

        $this->db->prepare_Stmt('delete from tbl_production_process_expenses where process_id = ?', [&$process_id]);
        $this->db->prepare_Stmt('delete from tbl_production_process_employee_type where process_id = ?', [&$process_id]);
        if (!empty($this->info['expense_arr'])) {
            foreach ($this->info['expense_arr'] as $ex) {
                $data_expenses = [
                    &$process_id, &$ex['account_id'], &$ex['expense_cost'], &$ex['total_cost'],
                    &$ex['is_total']
                ];
                $this->db->prepare_Stmt('insert into tbl_production_process_expenses (`process_id`, `Account_ID`,
                `cost`, `total_cost`, `is_total`)
                values(?,?,?,?,?)', $data_expenses);
            }
        }
        if (!empty($this->info['production_employee_type'])) {
            foreach ($this->info['production_employee_type'] as $row) {
                $data_row = [&$process_id, &$row['type_id'], &$row['employee_id'], &$row['cost']];
                $this->db->prepare_Stmt('insert into tbl_production_process_employee_type (`process_id`, `type_id`,
                `employee_id`,cost)
                values(?,?,?,?)', $data_row);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteProductionProcess()
    {
        $this->db->query('START TRANSACTION');
        $query_det = $this->db->prepare_Stmt('select d.*,p.Currency_ID,p.date_time,p.date,p.current_rate,pr.is_service
            from tbl_production_process_details d
            join tbl_production_process p using(process_id)
            join tbl_stock_products pr on pr.Product_ID = d.Product_ID
                                        where process_id = ?', [&$this->info['process_id']]);

        while ($row_det = $this->db->assoc($query_det)) {
            $old_expiry_date = !empty($row_det['expiry_date']) ? $row_det['expiry_date'] : null;
            $costdata = [
                "Product_ID" => $row_det['Product_ID'],
                "Quantity" => $row_det['default_quantity'],
                "Unit_Cost" => $row_det['cost'],
                "Currency_ID" => $row_det['Currency_ID'],
                "date_time" => $row_det['date_time'],
                "current_rate" => $row_det['current_rate'],
                "depot_id" => $row_det['Depot_ID'],
                "trans_date" => $row_det['date'],
                "current_quantity" => $row_det['current_quantity'],
                "uom_id" => $row_det['UOM_ID'],
                "is_actual" => 0,
                'lot_nb' => $row_det['lot_nb'],
                'serial_nb' => $row_det['serial_nb'],
                "expiry_date" => $old_expiry_date,
                "main_id" => $this->info['process_id'],
                "is_service" => $row_det['is_service']
            ];
            CommonBusiness::updateLinkQuantities($costdata, ($row_det['operation'] == '+' ? 'delete_produced_items' : 'delete_raw_items'), ($row_det['operation'] == '+' ? '-' : '+'));
        }
        CommonBusiness::update_trans_record_current('prd', $this->info['process_id']);
        CommonBusiness::update_trans_record_current('raw', $this->info['process_id']);
        $this->db->prepare_Stmt('delete from tbl_production_process where process_id = ?', [&$this->info['process_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_production_templates($cat_id = null)
    {
        $data = array();
        $cond = '';
        if (!is_null($cat_id))
            $cond .= ' where category_id = "' . $cat_id . '" ';
        $query = $this->db->query('select * from tbl_production_template ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function get_production_template_details($id)
    {
        $data = array(
            'products_in' => array(), 'products_out' => array(), 'products' => array(),
            'produced_products' => array(), 'variables' => array(), 'id_val' => array(), 'id_content' => array(),
            'groups' => array()
        );
        $query = $this->db->query('select * from tbl_production_template where template_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);

        //
        $query = $this->db->query('select d.*,p.Product_Code,p.Product_Name,u.UOM_Name,p.is_service,
            p.default_view_uom_id,d.Depot_ID
            from tbl_production_template_details d 
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            join tbl_stock_uom u on u.UOM_ID = d.UOM_ID
            where template_id = "' . $id . '"');
        while ($row = $this->db->assoc($query)) {
            if (!empty($row['group_name']) && !in_array($row['group_name'], $data['groups']))
                $data['groups'][] = $row['group_name'];
            $row['new_identifier'] = !empty($row['identifier']) ? $row['identifier'] : $row['temp_identifier'];
            if ($row['operation'] == '+') {
                if ($row['optional'] == 0)
                    $data['products_out'][] = $row;
                else if (empty($row['group_name'])) {
                    $data['produced_products'][] = $row;
                }
            } else {
                if ($row['optional'] == 0)
                    $data['products_in'][] = $row;
                else if (empty($row['group_name'])) {
                    $data['products'][] = $row;
                }
            }
            if (!empty($row['formula'])) {
                $matches = array();
                preg_match_all('/[a-zA-Z]+/', $row['formula'], $matches);
                foreach ($matches[0] as $match) {
                    $data['variables'][$match][] = $row['new_identifier'];
                    $data['id_content'][$row['new_identifier']][] = $match;
                }
                $data['id_val'][$row['new_identifier']] = 0;
            }
        }
        //
        if (!empty($data['main']['product_ids'])) {
            $query = $this->db->query('select p.Product_Name,p.Product_Code,p.Product_ID,
                p.default_view_uom_id,u.UOM_Name,p.is_service,"" as formula,"" as identifier
                from tbl_stock_products p 
                join tbl_stock_uom u
                on u.UOM_ID = p.default_view_uom_id
                where p.Status_ID = 1 and Product_ID in (' . $data['main']['product_ids'] . ')');
            while ($row = $this->db->assoc($query)) {
                $data['products'][] = $row;
            }
        }
        //
        if (!empty($data['main']['produced_product_ids'])) {
            $query = $this->db->query('select p.Product_Name,p.Product_Code,p.Product_ID,
                p.default_view_uom_id,u.UOM_Name,p.is_service
                from tbl_stock_products p 
                join tbl_stock_uom u
                on u.UOM_ID = p.default_view_uom_id
                where p.Status_ID = 1 and Product_ID in (' . $data['main']['produced_product_ids'] . ')');
            while ($row = $this->db->assoc($query)) {
                $data['produced_products'][] = $row;
            }
        }
        return $data;
    }

    public function submitProductionEmployeeTypes()
    {
        $type_id = $this->info['type_id'];
        if ($type_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_production_emp_types
                                     (type_name)
                                     VALUES
                                    (?)
                                         ', [&$this->info['type_name']]);
        } else {
            $this->db->prepare_Stmt('UPDATE tbl_production_emp_types
                                                 SET
                                                        type_name = ?
                                                 WHERE 
                                                        type_id =?

                                         ', [&$this->info['type_name'], &$type_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteProductionEmpType()
    {
        $this->db->prepare_Stmt('Delete from tbl_production_emp_types WHERE type_id =? ', [&$this->info['type_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function get_production_categories()
    {
        $data = array();
        $query = $this->db->query('select category_id as id,category_name as name,0 as is_line_required,
            1 as is_cat
           from tbl_production_category');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        //
        $query = $this->db->query('select template_id as id,template_name as name,is_line_required,
            0 as is_cat
           from tbl_production_template where category_id = 0');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function opt_temp_prod_search($term, $page, $limit, $is_needed, $template_id)
    {
        $data = array();
        $cond = '';
        if (!empty($template_id)) {
            $cond .= ' and d.template_id = "' . $template_id . '" ';
        }
        if (isset($is_needed)) {
            if ($is_needed == 1)
                $cond .= ' and operation = "-" ';
            else
                $cond .= ' and operation = "+" ';
        }
        $data['is_more'] = false;
        $data['data'] = array();
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (p.Product_Name LIKE ? or p.Product_Code like ?) ';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT p.Product_ID as id,p.Product_Code,p.Product_Name,
            concat_ws(" - ",p.Product_Code,p.Product_Name) as text,d.formula,d.identifier ,p.is_service,
            u.UOM_Name,d.current_quantity,d.UOM_ID,t.Currency_ID,d.Depot_ID,dp.Depot_Name
            FROM `tbl_production_template_details` d 
            join tbl_production_template t on t.template_id = d.template_id
            join tbl_stock_products p on p.Product_ID = d.`Product_ID` 
            join tbl_stock_uom u on u.UOM_ID = d.`UOM_ID`
            join tbl_stock_depot_warehouse dp on dp.Depot_ID = d.`Depot_ID`
            where optional=1 ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $cost = CommonBusiness::viewCurrentProductCost($row['id']);
            $row['current_cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $row['Currency_ID'], $cost);
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function delete_production_template($temp_id)
    {
        $query = $this->db->query('select * from tbl_production_process 
            where template_id = "' . $temp_id . '" limit 1');
        $count = $this->db->rowsno($query);
        if ($count > 0) {
            $this->db->set_error('unable_delete_production_template');
            return [CommonLanguage::getMessage('unable_delete_production_template')];
        }
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_production_template_expenses where template_id = ?', [$temp_id]);
        $this->db->prepare_Stmt('delete from tbl_production_template_details where template_id = ?', [$temp_id]);
        $this->db->prepare_Stmt('delete from tbl_production_template where template_id = ?', [$temp_id]);
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function get_group_temp_products()
    {
        $data = array('products' => array());
        $query = $this->db->query('select d.*,p.Product_Code,p.Product_Name,u.UOM_Name,p.is_service,
            p.default_view_uom_id,d.Depot_ID
            from tbl_production_template_details d 
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            join tbl_stock_uom u on u.UOM_ID = d.UOM_ID
            where template_id = "' . $this->info['id'] . '" 
            and d.group_name="' . $this->info['group'] . '" and operation="' . $this->info['operation'] . '" 
            and optional=1');
        while ($row = $this->db->assoc($query)) {
            $row['new_identifier'] = !empty($row['identifier']) ? $row['identifier'] : $row['temp_identifier'];
            $data['products'][] = $row;
        }
        return $data;
    }

}
