<?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));
   }
}
/*--------------------------------------------------------------*/
/* Function for find all database table rows by table name
/*--------------------------------------------------------------*/
function find_all_unique($table,$columnname) {
  global $db;
  if(tableExists($table))
  {
    return find_by_sql("SELECT DISTINCT $columnname FROM ".$db->escape($table));
  }
}

/*--------------------------------------------------------------*/
/* Function for find all database table rows by table name
/*--------------------------------------------------------------*/
function find_all_uniqueall($table,$columnname) {
  global $db;
  if(tableExists($table))
  {
    return find_by_sql("SELECT DISTINCT $columnname, PlanName FROM ".$db->escape($table));
  }
}
/*--------------------------------------------------------------*/
/* 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 Find data from table by Custom id
/*--------------------------------------------------------------*/
function find_by_id_custom($table,$id,$column)
{
  global $db;
  $id = (int)$id;
  
    if(tableExists($table)){
          $sql = $db->query("SELECT * FROM {$db->escape($table)} WHERE {$column}='{$db->escape($id)}' LIMIT 1");
          if($result = $db->fetch_assoc($sql))
            return $result;
          else
            return null;
     }
}
/*--------------------------------------------------------------*/
/*  Function for Find data from table by dynamic id
/*--------------------------------------------------------------*/
function find_by_id_new($table,$id,$Idcolumn)
{
  global $db;
  $id = (int)$id;
    if(tableExists($table)){
          $sql = $db->query("SELECT * FROM {$db->escape($table)} WHERE {$db->escape($Idcolumn)}={$db->escape($id)} LIMIT 1");
          if($result = $db->fetch_assoc($sql))
            return $result;
          else
            return null;
     }
}

/*--------------------------------------------------------------*/
/*  Function for Find data from table by dynamic id
/*--------------------------------------------------------------*/
function find_by_column($table,$value,$column)
{
  global $db;
  $value = (int)$value;
    if(tableExists($table)){
          $sql = $db->query("SELECT * FROM {$db->escape($table)} WHERE {$db->escape($column)}={$db->escape($value)}");
          if($result = $db->fetch_assoc($sql))
            return $result;
          else
            return null;
     }
}

/*--------------------------------------------------------------*/
/*  Function for Find data from table by dynamic string value
/*--------------------------------------------------------------*/
function find_by_columnnew($table,$value,$column)
{
  global $db;
  //$value = (int)$value;
    if(tableExists($table)){
          $sql = $db->query("SELECT * FROM {$db->escape($table)} WHERE {$db->escape($column)}='{$db->escape($value)}'");
          if($result = $db->fetch_assoc($sql))
            return $result;
          else
            return null;
     }
}

/*--------------------------------------------------------------*/
/*  Function for Find data from table by dynamic string value
/*--------------------------------------------------------------*/
function find_by_columnnall($table,$value,$column)
{
  global $db;
  //$value = (int)$value;
  $sql = "SELECT * FROM {$db->escape($table)} WHERE {$db->escape($column)}='{$db->escape($value)}'";
  if(tableExists($table)) {
            return find_by_sql($sql);
   } else {
            return null;
   }
     
}

/*--------------------------------------------------------------*/
/*  Function for Find data from table by multiple fields - by Vishnu
/*--------------------------------------------------------------*/
function find_by_fields($table, $fields)
{
  global $db;
  $conditions = array();
  foreach ($fields as $column => $value) {
    $conditions[] = "{$db->escape($column)} = '{$db->escape($value)}'";
  }
  $where_clause = implode(' AND ', $conditions);

  $sql = "SELECT * FROM {$db->escape($table)} WHERE {$where_clause}";

  if (tableExists($table)) {
    return find_by_sql($sql);
  } else {
    return null;
  }
}


/*--------------------------------------------------------------*/
/*  Function for Find data from table by max id
/*--------------------------------------------------------------*/
function find_by_id_max($table,$Idcolumn)
{
  global $db;
  //$id = (int)$id;
    if(tableExists($table)){
          $sql = $db->query("SELECT MAX($Idcolumn) as lastbarcode FROM {$db->escape($table)}");
          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 for Delete data from table by dynamic id
/*--------------------------------------------------------------*/
function delete_by_id_new($table,$id,$Idcolumn)
{
  global $db;
  if(tableExists($table))
   {
    $sql = "DELETE FROM ".$db->escape($table);
    $sql .= " WHERE $Idcolumn=". $db->escape($id);
    $sql .= " LIMIT 1";
    $db->query($sql);
    return ($db->affected_rows() === 1) ? true : false;
   }
}

/*--------------------------------------------------------------*/
/* Function for Delete data from table by dynamic id
/*--------------------------------------------------------------*/
function delete_all($table,$id,$Idcolumn)
{
  global $db;
  if(tableExists($table))
   {
    $sql = "DELETE FROM ".$db->escape($table);
    $sql .= " WHERE $Idcolumn=". $db->escape($id);
    //$sql .= " LIMIT 1";
    $db->query($sql);
    return ($db->affected_rows() >= 1) ? true : 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));
  }
}
/*--------------------------------------------------------------*/
/* Function for Count id  By table name
/*--------------------------------------------------------------*/

function count_by_id_new($table,$column){
  global $db;
  if(tableExists($table))
  {
    $sql    = "SELECT COUNT($column) 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 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['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;
    $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($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 subcategorie
   /*--------------------------------------------------------------*/
  function join_category_table(){
     global $db;
     $sql  =" SELECT p.CategoryId,p.SubCategoryId,c.CategoryName,p.SubCategoryName,p.UOM";
    $sql  .=" FROM subcategorydetails p";
    $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
    $sql  .=" ORDER BY p.SubCategoryId ASC";
    return find_by_sql($sql);
	
  }
	
   /*--------------------------------------------------------------*/
   /* Function for Finding all product name
   /* JOIN with categorie  and media database table
   /*--------------------------------------------------------------*/
  function join_product_table(){
     global $db;
     $sql  =" SELECT distinct b.Barcode, p.ProductId,p.Itemcode,p.ItemName,p.CategoryId,p.SubCategoryId,c.CategoryName,m.SubCategoryName,
     IF(p.CategoryId!=6 ,SUM(IF(b.Quantity>0,b.Quantity,0)),IF(b.Quantity>0,b.Quantity,0)) as AvlQty,
     IF(p.CategoryId!=6,SUM(IF(b.Quantity>0,1,0)),IF(b.Quantity>0,1,0)) as barcodecount,b.UOM";
    $sql  .=" FROM productdetails p";
    $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
    $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = p.SubCategoryId";
    $sql  .=" LEFT JOIN productreportview prv ON prv.ProductId = p.ProductId";
    $sql  .=" LEFT JOIN stockdetails s ON (s.ProductId = p.ProductId and s.StockType='IN - First Entry')";
    $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
    $sql  .=" GROUP BY p.ProductId";
    $sql  .=" ORDER BY p.ItemName ASC";
    return find_by_sql($sql);

   }
   
/*--------------------------------------------------------------*/
   /* Function for Finding all product name
   /* JOIN with categorie  and media database table
   /*--------------------------------------------------------------*/
   function join_product_table_excel(){
    global $db;
    $sql  =" SELECT distinct p.ProductId,p.Itemcode,p.ItemName,p.CategoryId,p.SubCategoryId,c.CategoryName,m.SubCategoryName,
    IF(p.CategoryId=3,SUM(IF(b.Quantity>0,b.Quantity,0)),IF(b.Quantity>0,b.Quantity,0)) as AvlQty,
     IF(p.CategoryId=3,SUM(IF(b.Quantity>0,1,0)),IF(b.Quantity>0,1,0)) as barcodecount,b.UOM";
   $sql  .=" FROM productdetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = p.SubCategoryId";
   $sql  .=" LEFT JOIN productreportview prv ON prv.ProductId = p.ProductId";
   $sql  .=" LEFT JOIN stockdetails s ON (s.ProductId = p.ProductId and s.StockType='IN - First Entry')";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=" GROUP BY p.ProductId";
   $sql  .=" ORDER BY p.ItemName ASC";
   return $db->query($sql);

  }

      /*--------------------------------------------------------------*/
   /* Function for Finding specific product name
   /* JOIN with categorie  and media database table
   /*--------------------------------------------------------------*/
  function join_product_table_new($productid){
    global $db;
    $sql  =" SELECT p.ProductId,p.Itemcode,p.ItemName,p.CategoryId,p.SubCategoryId,c.CategoryName,m.SubCategoryName";
   $sql  .=" FROM productdetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = p.SubCategoryId";
   $sql  .=" Where p.ProductId=".$productid;
   return find_by_sql($sql);

  }

        /*--------------------------------------------------------------*/
   /* Function for Finding specific sub category uom list
   /* JOIN with subcategorie  and uom database table
   /*--------------------------------------------------------------*/
   function join_subcategory_table($subcatid){
    global $db;
    $sql  =" SELECT s.SubCategoryId,s.SubCategoryName,s.UOM,u.UomId,u.UomSubType";
   $sql  .=" FROM subcategorydetails s";
   $sql  .=" LEFT JOIN uomdetails u ON u.UOMType = s.UOM";
   $sql  .=" Where s.SubCategoryId=".$subcatid;
   return find_by_sql($sql);

  }

         /*--------------------------------------------------------------*/
   /* Function for Finding specific sub category uom list
   /* JOIN with subcategorie  and uom database table
   /*--------------------------------------------------------------*/
   function category_subcategory_table($catid){
    global $db;
    $sql  =" SELECT * FROM subcategorydetails";
   $sql  .=" Where CategoryId=".$catid;
   return find_by_sql($sql);

  }

   /*--------------------------------------------------------------*/
   /* Function for Finding all stock details
   /* JOIN with categorie,location,unit  and subcategory database table
   /*--------------------------------------------------------------*/
   function join_stock_table(){
    global $db;
   $sql   =" SELECT s.StockId,s.Barcode,s.ProductId,p.Itemcode,p.ItemName,s.CategoryId,s.SubCategoryId,c.CategoryName,m.SubCategoryName,s.StockType,s.Quantity,s.LocationId,l.LocationName,s.UnitId,u.UnitName,s.PlanNo,uom.UomId,uom.UomSubType,b.Fullbarcode,s.Created_at";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=" ORDER BY s.StockId ASC";
   return find_by_sql($sql);

  }

  function join_stock_table_count($whereSQL){
    global $db;
   $sql   =" SELECT COUNT(*) as rowNum";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=$whereSQL;
   $sql  .=" ORDER BY s.StockId ASC";
   return $db->query($sql);

  }

  function join_stock_table_pagination($whereSQL,$offset,$limit){
    global $db;
   $sql   =" SELECT s.StockId,s.Barcode,s.ProductId,p.Itemcode,p.ItemName,s.CategoryId,s.SubCategoryId,c.CategoryName,m.SubCategoryName,s.StockType,s.Quantity,s.LocationId,l.LocationName,s.UnitId,u.UnitName,s.PlanNo,uom.UomId,uom.UomSubType,b.Fullbarcode,s.Created_at";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=$whereSQL;
   $sql  .=" ORDER BY s.StockId ASC";
   $sql  .=" LIMIT $offset,$limit";
   return $db->query($sql);

  }

  function join_stock_table_default($limit){
    global $db;
   $sql   =" SELECT s.StockId,s.Barcode,s.ProductId,p.Itemcode,p.ItemName,s.CategoryId,s.SubCategoryId,c.CategoryName,m.SubCategoryName,s.StockType,s.Quantity,s.LocationId,l.LocationName,s.UnitId,u.UnitName,s.PlanNo,uom.UomId,uom.UomSubType,b.Fullbarcode,s.Created_at";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=" ORDER BY s.StockId ASC";
   $sql  .=" LIMIT $limit";
   return $db->query($sql);

  }


     /*--------------------------------------------------------------*/
   /* Function for Finding all stock details
   /* JOIN with categorie,location,unit  and subcategory database table
   /*--------------------------------------------------------------*/
   function join_stock_table_user($userid){
    global $db;
   $sql   =" SELECT s.StockId,s.Barcode,s.ProductId,p.Itemcode,p.ItemName,s.CategoryId,s.SubCategoryId,c.CategoryName,m.SubCategoryName,s.StockType,s.Quantity,s.LocationId,l.LocationName,s.UnitId,u.UnitName,s.PlanNo,uom.UomId,uom.UomSubType,b.Fullbarcode,s.Created_at";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   if($userid) {
    $sql  .=" WHERE s.id=".$userid;
   }
   $sql  .=" ORDER BY s.StockId ASC";
   return find_by_sql($sql);

  }

  /*--------------------------------------------------------------*/
/* Function for Generate sales report by two dates
/*--------------------------------------------------------------*/
function find_stock_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.StockId,s.Barcode,s.ProductId,p.Itemcode,p.ItemName,s.CategoryId,s.SubCategoryId,c.CategoryName,m.SubCategoryName,s.StockType,s.Quantity,
  s.LocationId,l.LocationName,s.UnitId,u.UnitName,s.PlanNo,uom.UomId,uom.UomSubType,b.Fullbarcode,s.Created_at,s.Updated_at,us.Name";
   $sql  .=" FROM stockdetails as s";
   $sql  .=" LEFT JOIN productdetails p ON p.ProductId= s.ProductId";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = s.CategoryId";
   $sql  .=" LEFT JOIN subcategorydetails m ON m.SubCategoryId = s.SubCategoryId";
   $sql  .=" LEFT JOIN locationdetails l ON l.LocationId = s.LocationId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = s.UnitId";
   $sql  .=" LEFT JOIN uomdetails uom ON uom.UomId = s.UomId";
   $sql  .=" LEFT JOIN barcodedetails b ON b.Barcode = s.Barcode";
   $sql  .=" LEFT JOIN users us ON us.id = s.id";
   $sql  .=" WHERE DATE(s.Created_at) BETWEEN ".$start_date." AND ".$end_date;
   $sql  .=" ORDER BY s.StockId ASC";
  return $db->query($sql);
}

  /*--------------------------------------------------------------*/
  
   /*-----------------------------------------New Features----------------------------------------------------*/
  
/* Function for Generate sales report by two dates & prodict
/*--------------------------------------------------------------*/
function find_stock_by_dates_product($start_date,$end_date,$product_name){
  global $db;
  $start_date  = "'".date("Y-m-d", strtotime($start_date))."'";
  $end_date    = "'".date("Y-m-d", strtotime($end_date))."'";
  $sql   =" SELECT * ";
   $sql  .=" FROM datewiseview as s";
   $sql  .=" WHERE DATE(s.StockDate) BETWEEN ".$start_date." AND ".$end_date;
   if($product_name!=0) {
    $sql  .=" AND s.ProductId=".$product_name;
    }
   $sql  .=" ORDER BY s.StockDate DESC";
  return $db->query($sql);
}

  /*--------------------------------------------------------------*/
/* Function for Generate sales report by two dates & prodict
/*--------------------------------------------------------------*/
function find_product_report($product_name){
  global $db;
  $sql   =" SELECT * ";
   $sql  .=" FROM productreportview as s";
   if($product_name!=0) {
    $sql  .=" WHERE s.ProductId=".$product_name;
    }
   $sql  .=" ORDER BY s.ItemName ASC";
  return $db->query($sql);

}


  /*--------------New Queries For MMVKC ------------------------------------8?

  /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_salesplan_table(){
    global $db;
    $sql  =" SELECT distinct p.Id, p.PlanId,m.PlanName,p.UnitId,u.UnitName,m.PlanNumber,p.CategoryId,c.CategoryName,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.CaseCount,p.PairCount,p.SAPCode";
   $sql  .=" FROM salesplandetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   //$sql  .=" GROUP BY p.PlanId";
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);
  }
  
    /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_dailyplan_table_full(){
    global $db;
    $sql  =" SELECT distinct p.Id,p.PlanDate,sp.Id as SalesPlanId, p.JobNo, m.PlanId,m.PlanName,m.PlanNumber,p.ArticleId,prv.ArticleName,s.ColorId,p.SAPCode,
    s.ColorName,b.SizeId,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve,p.Thirteen,p.Fourteen";
   $sql  .=" FROM salesplandetails sp";
   //$sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN dailyplandetails p ON sp.Id = p.SalesPlanId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = sp.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = sp.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = sp.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = sp.SizeId";
   //$sql  .=" GROUP BY p.PlanId";
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);
  }

    /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_dailyplan_table(){
    global $db;
    $sql  =" SELECT distinct p.Id,p.PlanDate,p.SalesPlanId, p.JobNo, p.PlanId,m.PlanName,m.PlanNumber,p.ArticleId,prv.ArticleName,s.ColorId,p.SAPCode,
    s.ColorName,b.SizeId,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve,p.Thirteen,p.Fourteen";
   $sql  .=" FROM dailyplandetails p";
   //$sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN salesplandetails sp ON sp.Id = p.SalesPlanId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   //$sql  .=" GROUP BY p.PlanId";
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);
  }

    /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_dailyplan_table_by_Id($id){
    global $db;
    $sql  =" SELECT distinct p.Id,p.PlanDate, p.JobNo, p.PlanId,p.SalesPlanId,m.PlanName,m.PlanNumber,p.ArticleId,prv.ArticleName,s.ColorId,p.SAPCode,
    s.ColorName,b.SizeId,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve,p.Thirteen,p.Fourteen";
   $sql  .=" FROM dailyplandetails p";
   //$sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN salesplandetails sp ON sp.id = p.SalesPlanId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where p.Id=".$id;
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);
  }

      /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_dailyplan_table_by_feilds($fields){
    global $db;
    $conditions = array();
    foreach ($fields as $column => $value) {
      if($column=='PlanDate' || $column== 'JobNo') {
        $condition1="p.{$db->escape($column)} = '{$db->escape($value)}'";
      } else {
        $condition1="p.{$db->escape($column)} = {$db->escape($value)}";
      }
      $conditions[] = $condition1;
    }
    $where_clause = implode(' AND ', $conditions);

    $sql  =" SELECT distinct p.Id,p.PlanDate, p.SalesPlanId, p.JobNo, p.PlanId,m.PlanName,m.PlanNumber,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve";
   $sql  .=" FROM dailyplandetails p";
   //$sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   //$sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where {$where_clause}";
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);
  }

   /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /*--------------------------------------------------------------*/
   function join_dailyplan_table_by_feilds_export($fields){
    global $db;
    $conditions = array();
    foreach ($fields as $column => $value) {
      if($column=='PlanDate' || $column== 'JobNo') {
        $condition1="p.{$db->escape($column)} = '{$db->escape($value)}'";
      } else {
        $condition1="p.{$db->escape($column)} = {$db->escape($value)}";
      }
      $conditions[] = $condition1;
    }
    $where_clause = implode(' AND ', $conditions);

    $sql  =" SELECT distinct p.PlanDate, p.JobNo, m.PlanName,prv.ArticleName,
    s.ColorName,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve";
   $sql  .=" FROM dailyplandetails p";
   //$sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   //$sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where {$where_clause}";
   $sql  .=" ORDER BY p.PlanId ASC";
   return $db->query($sql);
  }

  /*--------------------------------------------------------------*/
   /* Function restructure the articlestagedetails table
   /*--------------------------------------------------------------*/
   function articlestagedetails($sapcode,$stageorder=0){
    global $db;
    $where="";
    if($stageorder !=0) {
      $where ="where stageorder=".$stageorder;
    }
    
    $sql  ="select * from (select a.`Id`, `SAPCode`,s.StageId, `Clicking` as stageorder, 'Clicking' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Clicking'
    where Clicking >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `Folding` as stageorder, 'Folding' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Folding'
    where Folding > 0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `Cuttex` as stageorder, 'Cuttex' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Cuttex'
    where Cuttex >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `PrintingOUT` as stageorder, 'Printing' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='PrintingOUT'
    where PrintingOUT >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `PrintingIN` as stageorder, 'Printing' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='PrintingIN'
    where PrintingIN >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `StitchingOUT` as stageorder, 'Stitching' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='StitchingOUT'
    where StitchingOUT >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `StitchingIN` as stageorder, 'Moulding' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='StitchingIN'
    where StitchingIN >0 and SAPCode='".$sapcode."') as finaltable ".$where;
    //echo $sql;die();
   return find_by_sql($sql);
  }

  /*--------------------------------------------------------------*/
   /* Function restructure the articlestagedetails table
   /*--------------------------------------------------------------*/
   function findnextstage($sapcode,$planid,$jobno){
    global $db;
    
    $sql  ="Select min(f.StageId) as nextstage,s1.StageName from (select * from (select a.`Id`, `SAPCode`,s.StageId, `Clicking` as stageorder, 'Clicking' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Clicking'
    where Clicking >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `Folding` as stageorder, 'Folding' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Folding'
    where Folding > 0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `Cuttex` as stageorder, 'Cuttex' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='Cuttex'
    where Cuttex >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `PrintingOUT` as stageorder, 'PrintingOUT' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='PrintingOUT'
    where PrintingOUT >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `PrintingIN` as stageorder, 'PrintingIN' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='PrintingIN'
    where PrintingIN >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `StitchingOUT` as stageorder, 'StitchingOUT' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='StitchingOUT'
    where StitchingOUT >0 and SAPCode='".$sapcode."'
    union all
    select a.`Id`, `SAPCode`,s.StageId, `StitchingIN` as stageorder, 'StitchingIN' Stage
    from articlestagedetails a
    join stagedetails as s on s.StageName='StitchingIN'
    where StitchingIN >0 and SAPCode='".$sapcode."' ) as s) as f LEFT join stagedetails s1 on f.Stage=s1.StageName
    left join trackingdetails t on (t.SAPCode=f.SAPCode and t.StageId=s1.StageId and t.JobNo='".$jobno."')
    where t.JobNo IS NULL and f.StageId NOT IN (select StageId from trackingdetails where t.PlanId=".$planid." and t.JobNo='".$jobno."' and Status=1)
   group by t.PlanId,t.JobNo ";
   return find_by_sql($sql);
  }


   /*--------------------------------------------------------------*/
   /* Function for  finding status of jobs
   /*--------------------------------------------------------------*/
   function findstatusdetails($planid,$Jobno){
    global $db;
    $sql  =" Select * from trackingdetails";
   $sql  .=" Where PlanId=".$planid." and JobNo='".$Jobno."'";
      //echo $sql;die();
   return find_by_sql($sql);

  }

    /*--------------------------------------------------------------*/
   /* Function for  finding status of jobs
   /*--------------------------------------------------------------*/
   function checkstatusdetails($planid,$Jobno,$stage){
    global $db;
    $sql  =" Select * from trackingdetails";
    $sql  .=" Where PlanId=".$planid." and JobNo='".$Jobno."' and StageId=".$stage;
   //echo $sql;
   return $db->query($sql);

  }

   /*--------------------------------------------------------------*/
   /* Function for Finding all Sales Plans 
   /* JOIN with categorie  all releated database table
   /* with valid plan id
   /*--------------------------------------------------------------*/
   function join_salesplan_table_plan($planid){
    global $db;
    $sql  =" SELECT distinct p.Id, p.PlanId,m.PlanName,p.UnitId,u.UnitName,m.PlanNumber,p.CategoryId,c.CategoryName,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.CaseCount,p.PairCount,p.SAPCode";
   $sql  .=" FROM salesplandetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where p.PlanId=".$planid;
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);

  }

  function join_salesplan_table_plan_export($planid){
    global $db;
    $sql  =" SELECT distinct p.Id, p.PlanId,m.PlanName,p.UnitId,u.UnitName,m.PlanNumber,p.CategoryId,c.CategoryName,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.CaseCount,p.PairCount,p.SAPCode";
   $sql  .=" FROM salesplandetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where p.PlanId=".$planid;
   $sql  .=" ORDER BY p.PlanId ASC";
   return $db->query($sql);

  }

  function join_salesplan_table_plan_by_id($planid){
    global $db;
    $sql  =" SELECT distinct p.Id, p.PlanId,m.PlanName,p.UnitId,u.UnitName,m.PlanNumber,p.CategoryId,c.CategoryName,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.CaseCount,p.PairCount,p.SAPCode";
   $sql  .=" FROM salesplandetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where p.Id=".$planid;
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);

  }

  function join_salesplan_table_plan_by_planid($planid){
    global $db;
    $sql  =" SELECT distinct p.Id, p.PlanId,m.PlanName,p.UnitId,u.UnitName,m.PlanNumber,p.CategoryId,c.CategoryName,p.ArticleId,prv.ArticleName,s.ColorId,
    s.ColorName,b.SizeId,b.SizeAssortment,p.CaseCount,p.PairCount,p.SAPCode";
   $sql  .=" FROM salesplandetails p";
   $sql  .=" LEFT JOIN categorydetails c ON c.CategoryId = p.CategoryId";
   $sql  .=" LEFT JOIN unitdetails u ON u.UnitId = p.UnitId";
   $sql  .=" LEFT JOIN plandetails m ON m.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   $sql  .=" Where p.PlanId=".$planid;
   $sql  .=" ORDER BY p.PlanId ASC";
   return find_by_sql($sql);

  }

          /*--------------------------------------------------------------*/
   /* Function for  finding casecount from salesplan
   /* JOIN with subcategorie  and uom database table
   /*--------------------------------------------------------------*/
   function salesplandetailsforarticle($planid,$unitid,$article){
    global $db;
    $sql  =" Select * from salesplandetails";
   $sql  .=" Where PlanId=".$planid." and UnitId=".$unitid." and SAPCode ='".$article."' Limit 1";
   return find_by_sql($sql);

  }

            /*--------------------------------------------------------------*/
   /* Function for  finding all the completed items
   /* JOIN with stage and plandetails table
   /*--------------------------------------------------------------*/
   function trackiingdetailsfull(){
    global $db;
    $sql  =" SELECT distinct p.PlanDate, p.JobNo, m.PlanName,prv.ArticleName,u.StageName,t.SAPCode,t.TotalQty,t.Quantity,t.CompletionDate,t.Remarks,t.VendorUnit,t.ChallanNo,
    s.ColorName,b.SizeAssortment,p.InitialPlan,p.CurrentPlan,p.Four,p.Five,p.Six,p.Seven,p.Eight,p.Nine,p.Ten,p.Eleven,p.Twelve";
   $sql  .=" FROM trackingdetails t";
   $sql  .=" LEFT JOIN dailyplandetails p ON (t.PlanId = p.PlanId and t.JobNo=p.JobNo)";
   $sql  .=" LEFT JOIN stagedetails u ON u.StageId = t.StageId";
   $sql  .=" LEFT JOIN plandetails m ON t.PlanId = p.PlanId";
   $sql  .=" LEFT JOIN articledetails prv ON prv.ArticleId = p.ArticleId";
   $sql  .=" LEFT JOIN colordetails s ON s.ColorId = p.ColorId ";
   $sql  .=" LEFT JOIN sizedetails b ON b.SizeId = p.SizeId";
   //$sql  .=" Where {$where_clause}";
   $sql  .=" ORDER BY t.PlanId DESC";
   return $db->query($sql);
   return find_by_sql($sql);

  }


?>
