<?php
require_once('includes/load.php');

/*--------------------------------------------------------------*/
/* Function for find all database table rows by table name
/*--------------------------------------------------------------*/
function find_all($table)
{
  global $db;
  if (tableExists($table)) {
    return find_by_sql("SELECT * FROM " . $db->escape($table) . " order by 1 desc");
  }
}
/*--------------------------------------------------------------*/
/* Function for Perform queries
/*--------------------------------------------------------------*/
function find_by_sql($sql)
{
  global $db;
  $result = $db->query($sql);
  $result_set = $db->while_loop($result);
  return $result_set;
}
/*--------------------------------------------------------------*/
/*  Function for Find data from table by id
/*--------------------------------------------------------------*/
function find_by_id($table, $id)
{
  global $db;
  $id = (int)$id;
  if (tableExists($table)) {
    $sql = $db->query("SELECT * FROM {$db->escape($table)} WHERE id='{$db->escape($id)}' LIMIT 1");
    if ($result = $db->fetch_assoc($sql))
      return $result;
    else
      return null;
  }
}
/*--------------------------------------------------------------*/
/* Function for Delete data from table by id
/*--------------------------------------------------------------*/
function delete_by_id($table, $id)
{
  global $db;
  if (tableExists($table)) {
    $sql = "DELETE FROM " . $db->escape($table);
    $sql .= " WHERE id=" . $db->escape($id);
    $sql .= " LIMIT 1";
    $db->query($sql);
    return ($db->affected_rows() === 1) ? true : false;
  }
}

function delete_by_field($table, $fieldName, $value)
{
  global $db;

  // Periksa apakah tabel ada
  if (tableExists($table)) {
    // Pastikan nama tabel dan kolom tidak di-escape
    $table = $db->escape($table);    // Escape nama tabel
    $fieldName = $db->escape($fieldName); // Escape nama kolom

    // Escape nilai (untuk mencegah SQL injection)
    $value = $db->escape($value);

    // Pastikan nilai yang akan digunakan dalam WHERE clause sesuai dengan tipe datanya
    $sql = "DELETE FROM `$table` WHERE `$fieldName` = '$value'";

    // Eksekusi query
    $db->query($sql);

    // Kembalikan true jika ada baris yang terpengaruh
    return ($db->affected_rows() !== 0);
  }

  // Jika tabel tidak ada, kembalikan false
  return false;
}
/*--------------------------------------------------------------*/
/* Function for Count id  By table name
/*--------------------------------------------------------------*/

function count_by_id($table)
{
  global $db;
  if (tableExists($table)) {
    $sql    = "SELECT COUNT(id) AS total FROM " . $db->escape($table);
    $result = $db->query($sql);
    return ($db->fetch_assoc($result));
  }
}
/*--------------------------------------------------------------*/
/* Determine if database table exists
/*--------------------------------------------------------------*/
function tableExists($table)
{
  global $db;
  $table_exit = $db->query('SHOW TABLES FROM ' . DB_NAME . ' LIKE "' . $db->escape($table) . '"');
  if ($table_exit) {
    if ($db->num_rows($table_exit) > 0)
      return true;
    else
      return false;
  }
}
/*--------------------------------------------------------------*/
/* Login with the data provided in $_POST,
 /* coming from the login form.
/*--------------------------------------------------------------*/
function authenticate($username = '', $password = '')
{
  global $db;
  $username = $db->escape($username);
  $password = $db->escape($password);
  $sql  = sprintf("SELECT id,username,password,user_level,status FROM users WHERE username ='%s' LIMIT 1", $username);
  $result = $db->query($sql);
  if ($db->num_rows($result)) {
    $user = $db->fetch_assoc($result);
    if (empty($user['status'])) {
      return false;
    }

    $password_request = sha1($password);
    if ($password_request === $user['password']) {
      return $user['id'];
    }
  }
  return false;
}
/*--------------------------------------------------------------*/
/* Login with the data provided in $_POST,
  /* coming from the login_v2.php form.
  /* If you used this method then remove authenticate function.
 /*--------------------------------------------------------------*/
function authenticate_v2($username = '', $password = '')
{
  global $db;
  $username = $db->escape($username);
  $password = $db->escape($password);
  $sql  = sprintf("SELECT id,username,password,user_level FROM users WHERE username ='%s' LIMIT 1", $username);
  $result = $db->query($sql);
  if ($db->num_rows($result)) {
    $user = $db->fetch_assoc($result);
    $password_request = sha1($password);
    if ($password_request === $user['password']) {
      return $user;
    }
  }
  return false;
}


/*--------------------------------------------------------------*/
/* Find current log in user by session id
  /*--------------------------------------------------------------*/
function current_user()
{
  static $current_user;
  global $db;
  if (!$current_user) {
    if (isset($_SESSION['user_id'])):
      $user_id = intval($_SESSION['user_id']);
      $current_user = find_by_id('users', $user_id);
    endif;
  }
  return $current_user;
}
/*--------------------------------------------------------------*/
/* Find all user by
  /* Joining users table and user gropus table
  /*--------------------------------------------------------------*/
function find_all_user()
{
  global $db;
  $results = array();
  $sql = "SELECT u.id,u.name,u.username,u.user_level,u.status,u.last_login,";
  $sql .= "g.group_name ";
  $sql .= "FROM users u ";
  $sql .= "LEFT JOIN user_groups g ";
  $sql .= "ON g.group_level=u.user_level ORDER BY u.name ASC";
  $result = find_by_sql($sql);
  return $result;
}
/*--------------------------------------------------------------*/
/* Function to update the last log in of a user
  /*--------------------------------------------------------------*/

function updateLastLogIn($user_id)
{
  global $db;

  // Set timezone to Asia/Jakarta
  date_default_timezone_set('Asia/Jakarta');

  $date = make_date();
  $sql = "UPDATE users SET last_login='{$date}' WHERE id ='{$user_id}' LIMIT 1";
  $result = $db->query($sql);
  return ($result && $db->affected_rows() === 1 ? true : false);
}


/*--------------------------------------------------------------*/
/* Find all Group name
  /*--------------------------------------------------------------*/
function find_by_groupName($val)
{
  global $db;
  $sql = "SELECT group_name FROM user_groups WHERE group_name = '{$db->escape($val)}' LIMIT 1 ";
  $result = $db->query($sql);
  return ($db->num_rows($result) === 0 ? true : false);
}
/*--------------------------------------------------------------*/
/* Find group level
  /*--------------------------------------------------------------*/
function find_by_groupLevel($level)
{
  global $db;
  $sql = "SELECT group_level FROM user_groups WHERE group_level = '{$db->escape($level)}' LIMIT 1 ";
  $result = $db->query($sql);
  return ($db->num_rows($result) === 0 ? true : false);
}
/*--------------------------------------------------------------*/
/* Function for cheaking which user level has access to page
  /*--------------------------------------------------------------*/
function page_require_level($require_level)
{
  global $session;
  $current_user = current_user();
  $login_level = find_by_groupLevel($current_user['user_level']);
  //if user not login
  if (!$session->isUserLoggedIn(true)):
    $session->msg('d', 'Please login...');
    redirect(
      'index.php',
      false
    );
  //if Group status Deactive
  elseif (is_array($login_level) && $login_level['group_status'] === '0'):
    $session->msg('d', 'This level user has been band!');
    redirect('home.php', false);
  //cheackin log in User level and Require level is Less than or equal to
  elseif ($current_user['user_level'] <= (int)$require_level):
    return true;
  else:
    $session->msg("d", "Sorry! you dont have permission to view the page.");
    redirect('home.php', false);
  endif;
}
/*--------------------------------------------------------------*/
/* Function for Finding all product name
   /* JOIN with categorie  and media database table
   /*--------------------------------------------------------------*/
function join_product_table($per_page, $page)
{
  global $db;
  $start = ($page - 1) * $per_page;
  $sql  = "SELECT p.id, p.part_number, p.name, p.quantity, p.uom, p.buy_price, p.loc_id, p.new_code, p.media_id, p.date, c.name AS categorie, m.file_name AS image ";
  $sql .= "FROM products p ";
  $sql .= "LEFT JOIN categories c ON c.id = p.categorie_id ";
  $sql .= "LEFT JOIN media m ON m.id = p.media_id ";
  $sql .= "ORDER BY p.id ASC ";
  $sql .= "LIMIT {$start}, {$per_page}";
  return find_by_sql($sql);
}




/*--------------------------------------------------------------*/
/* Function for Finding all product name
  /* Request coming from ajax.php for auto suggest
  /*--------------------------------------------------------------*/

function find_product_by_title($product_name)
{
  global $db;
  $p_name = remove_junk($db->escape($product_name));
  $sql = "SELECT name FROM products WHERE name like '%$p_name%' LIMIT 5";
  $result = find_by_sql($sql);
  return $result;
}

/*--------------------------------------------------------------*/
/* Function for Finding all product info by product title
  /* Request coming from ajax.php
  /*--------------------------------------------------------------*/
function find_all_product_info_by_title($title)
{
  global $db;
  $sql  = "SELECT * FROM products ";
  $sql .= " WHERE name ='{$title}'";
  $sql .= " LIMIT 1";
  return find_by_sql($sql);
}

/*--------------------------------------------------------------*/
/* Function for Update product quantity
  /*--------------------------------------------------------------*/
function update_product_qty($qty, $p_id, $is_incoming = false)
{
  global $db;

  // Escape input to prevent SQL injection
  $qty = (int) $qty;
  $id = (int) $p_id;

  // Get the current stock of the product
  $current_stock = get_product_stock($id);

  if ($is_incoming) {
    // If it's an incoming goods operation, add the quantity to stock
    $new_stock = $current_stock + $qty;
  } else {
    // For other scenarios (e.g., sales), subtract the quantity from stock
    $new_stock = $current_stock - $qty;
  }

  // Update the product stock in the database
  $sql = "UPDATE products SET quantity='{$new_stock}' WHERE id='{$id}'";
  $result = $db->query($sql);

  return ($result && $db->affected_rows() === 1);
}
/*--------------------------------------------------------------*/
/* Function for Display Recent product Added
  /*--------------------------------------------------------------*/
function find_recent_product_added($limit)
{
  global $db;
  $sql   = " SELECT p.id,p.name,p.sale_price,p.media_id,c.name AS categorie,";
  $sql  .= "m.file_name AS image FROM products p";
  $sql  .= " LEFT JOIN categories c ON c.id = p.categorie_id";
  $sql  .= " LEFT JOIN media m ON m.id = p.media_id";
  $sql  .= " ORDER BY p.id DESC LIMIT " . $db->escape((int)$limit);
  return find_by_sql($sql);
}
/*--------------------------------------------------------------*/
/* Function for Find Highest saleing Product
 /*--------------------------------------------------------------*/
function find_higest_saleing_product($limit)
{
  global $db;
  $sql  = "SELECT p.name, COUNT(s.product_id) AS totalSold, SUM(s.qty) AS totalQty";
  $sql .= " FROM sales s";
  $sql .= " LEFT JOIN products p ON p.id = s.product_id ";
  $sql .= " GROUP BY s.product_id";
  $sql .= " ORDER BY SUM(s.qty) DESC LIMIT " . $db->escape((int)$limit);
  return $db->query($sql);
}
/*--------------------------------------------------------------*/
/* Function for find all sales
 /*--------------------------------------------------------------*/
function find_all_sale()
{
  global $db;
  $sql  = "SELECT s.id,s.qty,s.price,s.date,p.name";
  $sql .= " FROM sales s";
  $sql .= " LEFT JOIN products p ON s.product_id = p.id";
  $sql .= " ORDER BY s.date DESC";
  return find_by_sql($sql);
}
/*--------------------------------------------------------------*/
/* Function for Display Recent sale
 /*--------------------------------------------------------------*/
function find_recent_sale_added($limit)
{
  global $db;
  $sql  = "SELECT s.id,s.qty,s.price,s.date,p.name";
  $sql .= " FROM sales s";
  $sql .= " LEFT JOIN products p ON s.product_id = p.id";
  $sql .= " ORDER BY s.date DESC LIMIT " . $db->escape((int)$limit);
  return find_by_sql($sql);
}
/*--------------------------------------------------------------*/
/* Function for Generate sales report by two dates
/*--------------------------------------------------------------*/
function find_sale_by_dates($start_date, $end_date)
{
  global $db;
  $start_date  = date("Y-m-d", strtotime($start_date));
  $end_date    = date("Y-m-d", strtotime($end_date));
  $sql  = "SELECT s.date, p.name,p.sale_price,p.buy_price,";
  $sql .= "COUNT(s.product_id) AS total_records,";
  $sql .= "SUM(s.qty) AS total_sales,";
  $sql .= "SUM(p.sale_price * s.qty) AS total_saleing_price,";
  $sql .= "SUM(p.buy_price * s.qty) AS total_buying_price ";
  $sql .= "FROM sales s ";
  $sql .= "LEFT JOIN products p ON s.product_id = p.id";
  $sql .= " WHERE s.date BETWEEN '{$start_date}' AND '{$end_date}'";
  $sql .= " GROUP BY DATE(s.date),p.name";
  $sql .= " ORDER BY DATE(s.date) DESC";
  return $db->query($sql);
}
/*--------------------------------------------------------------*/
/* Function for Generate Daily sales report
/*--------------------------------------------------------------*/
function  dailySales($year, $month)
{
  global $db;
  $sql  = "SELECT s.qty,";
  $sql .= " DATE_FORMAT(s.date, '%Y-%m-%e') AS date,p.name,";
  $sql .= "SUM(p.sale_price * s.qty) AS total_saleing_price";
  $sql .= " FROM sales s";
  $sql .= " LEFT JOIN products p ON s.product_id = p.id";
  $sql .= " WHERE DATE_FORMAT(s.date, '%Y-%m' ) = '{$year}-{$month}'";
  $sql .= " GROUP BY DATE_FORMAT( s.date,  '%e' ),s.product_id";
  return find_by_sql($sql);
}
/*--------------------------------------------------------------*/
/* Function for Generate Monthly sales report
/*--------------------------------------------------------------*/
function  monthlySales($year)
{
  global $db;
  $sql  = "SELECT s.qty,";
  $sql .= " DATE_FORMAT(s.date, '%Y-%m-%e') AS date,p.name,";
  $sql .= "SUM(p.sale_price * s.qty) AS total_saleing_price";
  $sql .= " FROM sales s";
  $sql .= " LEFT JOIN products p ON s.product_id = p.id";
  $sql .= " WHERE DATE_FORMAT(s.date, '%Y' ) = '{$year}'";
  $sql .= " GROUP BY DATE_FORMAT( s.date,  '%c' ),s.product_id";
  $sql .= " ORDER BY date_format(s.date, '%c' ) ASC";
  return find_by_sql($sql);
}

function count_products()
{
  global $db;
  $query  = "SELECT COUNT(id) AS total FROM products";
  $result = $db->query($query);
  confirm($result);
  $row = $db->fetch_assoc($result);
  return $row['total'];
}

function confirm($result)
{
  global $db;
  if (!$result) {
    exit("Database query failed: " . $db->error);
  }
}

// File: includes/functions.php

function count_all_products()
{
  global $db;
  $sql = "SELECT COUNT(id) AS total FROM products"; // Ganti 'products' dengan nama tabel produk Anda
  $result = $db->query($sql);
  $row = $db->fetch_assoc($result);
  return isset($row['total']) ? (int)$row['total'] : 0;
}

function bulk_update_v2($table, $updates, $conditionField, $conditionValues)
{
  global $db;

  // Prepare the SET clause
  $setClauses = [];
  foreach ($updates as $field => $value) {
    $valueToSet = $value == "NULL" ? 'NULL' : "'{$value}'";
    $setClauses[] = "{$field} = {$valueToSet}";
  }
  $setClause = implode(", ", $setClauses);

  // Create a string of condition values for the WHERE clause
  $conditionValuesList = implode("','", array_map('addslashes', $conditionValues));

  // Prepare the SQL update statement
  $sql = "UPDATE {$table} SET {$setClause} WHERE {$conditionField} IN ('{$conditionValuesList}')";

  // Execute the query
  return $db->query($sql) === TRUE;
}

function update_by_field($table, $field, $value, $conditionField, $conditionValue)
{
  global $db;

  // Escape the inputs to prevent SQL injection
  // Menggunakan prepared statements lebih aman, tetapi untuk contoh ini kita akan tetap menggunakan escape
  $value = $value;
  $conditionValue = $conditionValue;

  // Siapkan nilai untuk query
  $valueToSet = $value == "NULL" ? 'NULL' : "'{$value}'";

  // Prepare the SQL update statement
  $sql = "UPDATE {$table} SET {$field} = {$valueToSet} WHERE {$conditionField} = '{$conditionValue}'";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}

function update_multiple_fields_condition_array($table, $updates, $conditionField, $conditionValues)
{
  global $db;

  // Pastikan $conditionValues adalah array
  if (!is_array($conditionValues) || empty($conditionValues)) {
    return false; // Jika bukan array atau kosong, hentikan eksekusi
  }


  $setClauses = [];
  foreach ($updates as $field => $value) {
    if ($value === null) {
      $setClauses[] = "{$field} = NULL";
    } else {
      $setClauses[] = "{$field} = '{$value}'";
    }
  }
  $setClause = implode(", ", $setClauses);
  $inClause = implode("', '", $conditionValues);

  // Siapkan query
  $sql = "UPDATE {$table} SET {$setClause} WHERE {$conditionField} IN ('{$inClause}')";

  // Eksekusi query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}
function update_multiple_fields($table, $updates, $conditionField, $conditionValue)
{
  global $db;

  // Siapkan bagian SET untuk query
  $setClauses = [];
  foreach ($updates as $field => $value) {
    if ($value === null) {
      $setClauses[] = "{$field} = NULL";
    } else {
      $setClauses[] = "{$field} = '{$value}'";
    }
  }
  $setClause = implode(", ", $setClauses);

  // Siapkan query
  $sql = "UPDATE {$table} SET {$setClause} WHERE {$conditionField} = '{$conditionValue}'";

  // Eksekusi query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}

function soft_delete($table, $id)
{
  global $db;

  // Get the current timestamp
  $timestamp = date('Y-m-d H:i:s');

  // Escape the inputs to prevent SQL injection
  $id = $id;

  // Prepare the SQL update statement
  $sql = "UPDATE {$table} SET deleted_at = '{$timestamp}' WHERE id = '{$id}'";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}

function soft_delete_by_field($table, $field, $value)
{
  global $db;

  // Get the current timestamp
  $timestamp = date('Y-m-d H:i:s');

  // Escape the inputs to prevent SQL injection
  $field = $field;
  $value = $value;

  // Prepare the SQL update statement
  $sql = "UPDATE {$table} SET deleted_at = '{$timestamp}' WHERE {$field} = '{$value}'";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}

function insert_order_history($order_sn, $old_zipcode, $new_zipcode, $old_sku, $new_sku)
{
  global $db; // Assuming $db is your database connection

  // Prepare the SQL insert statement
  $sql = "INSERT INTO orders_history (order_sn, old_zipcode, new_zipcode, old_sku, new_sku) 
            VALUES ('$order_sn', '$old_zipcode', '$new_zipcode', '$old_sku', '$new_sku')";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true; // Insertion successful
  } else {
    return false; // Insertion failed
  }
}

function bulk_update($table, $updateFields, $conditionField, $conditions)
{
  global $db;

  // Prepare the SQL update statement
  $updateStatements = [];
  foreach ($updateFields as $field => $value) {
    $valueToSet = ($value == "NULL") ? 'NULL' : "'{$db->escape($value)}'";
    $updateStatements[] = "{$field} = {$valueToSet}";
  }
  $updateQuery = implode(", ", $updateStatements);

  $conditionStatements = [];
  foreach ($conditions as $conditionValue) {
    $conditionStatements[] = "{$conditionField} = '{$db->escape($conditionValue)}'";
  }
  $conditionQuery = implode(" OR ", $conditionStatements);

  // Prepare the full SQL update query
  $sql = "UPDATE {$table} SET {$updateQuery} WHERE {$conditionQuery}";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}

function bulk_insert($table, $data)
{
  global $db;

  // Escape the inputs and prepare the SQL insert statement
  $columns = array_keys($data[0]); // Assuming all rows have the same columns
  $columnsList = implode(", ", $columns);

  $values = [];
  foreach ($data as $row) {
    $escapedValues = array_map(function ($value) use ($db) {
      return ($value === "NULL") ? 'NULL' : "'" . $value . "'";
    }, $row);

    $values[] = "(" . implode(", ", $escapedValues) . ")";
  }

  $valuesList = implode(", ", $values);

  // Prepare the SQL insert statement
  $sql = "INSERT INTO {$table} ({$columnsList}) VALUES {$valuesList}";

  // Execute the query
  if ($db->query($sql) === TRUE) {
    return true;
  } else {
    return false;
  }
}
function page_require_level_array($require_level)
{
  global $session;
  $current_user = current_user();
  $login_level = find_by_groupLevel($current_user['user_level']);

  // Jika pengguna belum login
  if (!$session->isUserLoggedIn(true)):
    $session->msg('d', 'Please login...');
    redirect('index.php', false);
  // Jika status grup pengguna adalah "Deactive"
  elseif (is_array($login_level) && $login_level['group_status'] === '0'):
    $session->msg('d', 'This level user has been banned!');
    redirect('home.php', false);
  // Memeriksa apakah level pengguna saat ini ada dalam daftar level yang dibutuhkan
  elseif (is_array($require_level) && in_array($current_user['user_level'], $require_level)):
    return true;
  // Jika level pengguna tidak sesuai dengan level yang dibutuhkan
  else:
    $session->msg("d", "Sorry! you don't have permission to view the page.");
    redirect('home.php', false);
  endif;
}
