B3RT1337 - ADVANCED WEB SHELL

Linux ubuntu 5.4.0-163-generic #180-Ubuntu SMP Tue Sep 5 13:21:23 UTC 2023 x86_64
Apache/2.4.41 (Ubuntu)
74.208.127.88

pos.php

<?php
class pos extends dbconn {
	public function __construct()
	{
		$this->initDBO();
	}

//todas las ventas	
public function getListCollection()
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("SELECT * FROM `t_sale` where id_client > 100 order by input_date asc");
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List OK";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
	}

//ventas por cobrador
public function getCollectorList($id_collector)
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("SELECT * FROM `t_sale` where id_collector = :id_collector order by input_date asc");
			 $stmt->bindParam("id_collector",$id_collector);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List OK";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
}

//ventas por cobrador
public function getPendingRouteByColector($id_collector)
	{
		$db = $this->dblocal;
		try
		{
      $currentDay=strtoupper(strftime("%A"));
      $dayNumber=strftime("%d");
			$stmt = $db->prepare("SELECT pr.`sale_id`,pr.`total`,pr.`pay_min`,pr.`balance`,pr.`payday`,
                            cus.name,cus.direccion,cus.contract
                            FROM pending_route pr
                            left JOIN customer cus ON cus.`contract`= pr.`contract`
                            where `collector_id` = :id_collector
                            AND (UPPER(`payday`)=:currentDay  AND `pay_period`='S')
                            OR (`payday`=:dayNumber AND `pay_period`='M')
                            order by `payday` asc");
			 $stmt->bindParam("id_collector",$id_collector);
			 $stmt->bindParam("currentDay",$currentDay);
			 $stmt->bindParam("dayNumber",$dayNumber);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List OK pending_route";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
}

public function getPays($id_cust){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT SUM(val) AS balance,created_at FROM payment WHERE person_id= :id_cust and payment_type_id = 2");
     $stmt->bindParam("id_cust",$id_cust);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

//fecha del ultimo pago por cliente
public function getPaysUltimo($id_cust){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT `id`,`person_id`,MAX(`created_at`),MAX(pay_date) FROM `payment` WHERE (`payment_type_id`=2) AND `person_id`= :id_cust ORDER BY `created_at` DESC ");
     $stmt->bindParam("id_cust",$id_cust);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 //fecha del ultimo pago por cliente
public function getPaysUltimo2($id_cust){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT `id`,`person_id`,`created_at`,  FROM `payment` WHERE (`payment_type_id`=1) AND `person_id`= :id_cust ");
     $stmt->bindParam("id_cust",$id_cust);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 //bucamos pago por numero de semana
public function getPaysWeek($semC,$semA,$id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT TIMESTAMPDIFF(week, :semC,:semA) AS sems FROM `payment` WHERE `person_id` = :id_item and `payment_type_id` = 2");
     $stmt->bindParam("semC",$semC);
	 $stmt->bindParam("semA",$semA);
	 $stmt->bindParam("id_item",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 } 
 
  //bucamos pago por numero de meses
public function getPaysMonth($semC,$semA,$id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT TIMESTAMPDIFF(month, :semC,:semA) AS month FROM `payment` WHERE `person_id` = :id_item and `payment_type_id` = 2");
     $stmt->bindParam("semC",$semC);
	 $stmt->bindParam("semA",$semA);
	 $stmt->bindParam("id_item",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 } 
	/******************************************************************************
    START TABEL person
    *******************************************************************************/
public function autoCompleteC($term)
{
	  $trm = "%".$term."%";
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("SELECT a.* FROM customer a WHERE  (name like :term or contract  like :term ) order by name asc");
	   $stmt->bindParam("term",$trm);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

public function autoCompleteP($term)
{
	  $trm = "%".$term."%";
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("SELECT a.* FROM supplier a WHERE  name like :term order by name asc");
	   $stmt->bindParam("term",$trm);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

/******************************************************************************
    START TABEL stock
    *******************************************************************************/
	
	public function getStockId($id){
    $db = $this->dblocal;
    try
    {
		 $stmt = $db->prepare("select a.* from stock a where a.id = :id  ");
		 $stmt->bindParam("id",$id);
		 $stmt->execute();
		 $stat[0] = true;
		 $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
		 return $stat;
	}
	catch(PDOException $ex)
	  {
		 $stat[0] = false;
		 $stat[1] = $ex->getMessage();
		 return $stat;
	  }
 }
  /******************************************************************************
    end TABEL stock
    *******************************************************************************/

/******************************************************************************
    START TABEL m_item
    *******************************************************************************/
	
  public function getTotalQTempSale(){
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("SELECT SUM(qty) AS total FROM purchase_order");
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = $stmt->fetchColumn(0);
    $stat[2] = $stmt->rowCount();
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}
	
	
	
    public function getListItem()
    {
      $db = $this->dblocal;
      try
      {
       $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM m_item t, 
        (SELECT @rownum := 0) r ORDER BY id_item ASC");
       $stmt->execute();
       $stat[0] = true;
       $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
       return $stat;
     }
     catch(PDOException $ex)
     {
       $stat[0] = false;
       $stat[1] = $ex->getMessage();
       return $stat;
     }
   }
   
   //lista de inventario
   public function list_inventary()
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("select * from m_item order by stock DESC");
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List inventary";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
	}
	
	//lista de ventas
	 public function list_credit()
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("select * from t_sale order by sale_id DESC");
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List sales credit";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
	}
	
   
   //inventario de tabla stock
   public function getAllstock()
    {
      $db = $this->dblocal;
      try
      {
       $stmt = $db->prepare("SELECT * FROM `stocks` ORDER BY id ASC");
       $stmt->execute();
       $stat[0] = true;
       $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
       return $stat;
     }
     catch(PDOException $ex)
     {
       $stat[0] = false;
       $stat[1] = $ex->getMessage();
       return $stat;
     }
   }

public function getItem($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from m_item a where a.id_item = :id and status is null");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
	public function getIdProduct($barcode){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT id,name,barcode,price_in FROM product WHERE barcode= :barcode");
     $stmt->bindParam("barcode",$barcode);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
	public function getItemCompras($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(q) as compras from operation2 where product_id = :id and operation_type_id = 1");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 	public function getItemCompras2($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(q) as Ocompras from operation2 where barcode = :barcode and operation_type_id = 1");
     $stmt->bindParam("barcode",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 	public function getItemMerma($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(q) as merma from operation2 where product_id = :id and operation_type_id = 2");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 	public function getItemVentas($barcode){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(qty) as ventas from t_sale_detail where barcode = :id and is_draft = 0");
     $stmt->bindParam("id",$barcode);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 public function getItemDev($id){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(q) as dev from operation where product_id = :id and operation_type_id = 5");
     $stmt->bindParam("id",$id);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
  public function getClientMostrador($item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from customer a where a.name = :name ");
     $stmt->bindParam("name",$item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 //buscar cliete por id
  public function getByIdClient($item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from customer a where a.id_cust = :id ");
     $stmt->bindParam("id",$item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 
 public function getMaxC(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT MAX(id_cust) AS id FROM customer");
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
  public function getRe(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT MAX(id) AS id FROM re");
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
    public function getItemPrice($id_item){
    $db = $this->dblocal;
    try
    {
		 $stmt = $db->prepare("select a.* from listprice a where a.barcode = :id ");
		 $stmt->bindParam("id",$id_item);
		 $stmt->execute();
		 $stat[0] = true;
		 $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
		 return $stat;
	}
	catch(PDOException $ex)
	  {
		 $stat[0] = false;
		 $stat[1] = $ex->getMessage();
		 return $stat;
	  }
 }
 
   public function getItem3($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from m_item a where a.barcode = :id and status is null");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
  public function getItem2($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("SELECT COUNT(*) as contador FROM m_item where stock < :id");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

public function updateItem($iditem,$barcode,$item_name,$price,$unit,$stock,$note)
 {
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("UPDATE m_item 
    SET  barcode = UPPER(:barcode),
	item_name = UPPER(:item_name),
    unit= :unit,
    stock= :stock, 
    price= :price,
    note= :note 
    WHERE id_item= :iditem;");
	$stmt->bindParam("barcode",$barcode);
   $stmt->bindParam("iditem",$iditem);
   $stmt->bindParam("item_name",$item_name);
   $stmt->bindParam("price",$price);
   $stmt->bindParam("note",$note);
   $stmt->bindParam("unit",$unit);
   $stmt->bindParam("stock",$stock);

$stmt->execute();
   $stat[0] = true;
   $stat[1] = "Actualizado!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function saveItem($barcode,$item_name,$price,$unit,$stock,$note){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("call saveItem(:barcode,:item_name,:unit,:stock,:price,:note)");
		$stmt->bindParam("barcode",$barcode);
	   $stmt->bindParam("item_name",$item_name);
	   $stmt->bindParam("price",$price);
	   $stmt->bindParam("note",$note);
	   $stmt->bindParam("unit",$unit);
	   $stmt->bindParam("stock",$stock);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

//guardar clientes

public function saveClient($nombre,$contrato,$conyugue,$direccion,$payday,$credito,$poblacion, $telefono){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("call saveClient(:nombre,:contrato,:conyugue,:direccion,:payday,:credito,:poblacion, :telefono)");
		$stmt->bindParam("nombre",$nombre);
	   $stmt->bindParam("contrato",$contrato);
	   $stmt->bindParam("conyugue",$conyugue);
	   $stmt->bindParam("conyugue",$conyugue);
	   $stmt->bindParam("direccion",$direccion);
	   $stmt->bindParam("payday",$payday);
	   $stmt->bindParam("credito",$credito);
	   $stmt->bindParam("poblacion",$poblacion);
	   $stmt->bindParam("telefono",$telefono);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

//saveAval name,relationship,colonia,direction,customer_id,country,phone
public function saveAval($name,$relationship,$colonia,$direction,$customer_id,$country,$phone){
	  $db = $this->dblocal;
	  try
	  {
		  //INSERT INTO `endorsement` (`id`, `name`, `relationship`, `colonia`, `direction`, `customer_id`, `country`, `phone`) VALUES ('3', 'alama apata', '1', 'allamedas', 'centro de lerdo 2', '1', 'torrreon', '32984384');
	   $stmt = $db->prepare("insert into endorsement(`name`, `relationship`, `colonia`, `direction`, `customer_id`, `country`, `phone`)
	   values (:name ,:relationship,:colonia,:direction,:customer_id,:country,:phone)");
		$stmt->bindParam("name",$name);
	   $stmt->bindParam("relationship",$relationship);
	    $stmt->bindParam("colonia",$colonia);
	   $stmt->bindParam("direction",$direction);
	   $stmt->bindParam("customer_id",$customer_id);
	   $stmt->bindParam("country",$country);
	    $stmt->bindParam("phone",$phone);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save endorsement!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
		 echo "exception";
		 die;
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

public function saveAdicionales($workplace,$laborOld,$salary,$payment_periodicity,$avales,$id_client){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("insert into job(workplace, labor_Old, salary, payment_periodicity,endorsements, id_client) values 
      (:workplace , :laborOld , :salary, :payment_periodicity, :avales, :id_client)");
		$stmt->bindParam("workplace",$workplace);
	   $stmt->bindParam("laborOld",$laborOld);
	   $stmt->bindParam("salary",$salary);
	   $stmt->bindParam("payment_periodicity",$payment_periodicity);
	   $stmt->bindParam("avales",$avales);
	   $stmt->bindParam("id_client",$id_client);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save ad!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

//dias de pago y demas valores
public function saveAdicionales2($id_client,$payday,$payment_period,$min_pay){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("insert into collection_data(id_client ,payday	, txtpayment_period, min_pay) values 
      ( :id_client,:payday , :payment_period , :min_pay)");
	   $stmt->bindParam("id_client",$id_client);
	   $stmt->bindParam("payday",$payday);
	   $stmt->bindParam("payment_period",$payment_period);
	   $stmt->bindParam("min_pay",$min_pay);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save ad2!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

//obtenemos el pago minimo 
public function getCollectionPay($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select * from collection_data where id_client = :id_item ");
     $stmt->bindParam("id_item",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

public function updateClient($id,$nombre,$contrato,$conyugue,$direccion,$payday,$credito,$poblacion, $telefono)
 {
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("UPDATE customer 
    SET  contract = UPPER(:contrato),
    name= :nombre,
    conyugue= :conyugue, 
    direccion= :direccion,
    payday_in= :payday,
	credito= :credito,
	poblacion= :poblacion, 
	phone= :telefono 
    WHERE id_cust= :id_cust;");

$stmt->bindParam("id_cust",$id);
   $stmt->bindParam("contrato",$contrato);
   $stmt->bindParam("conyugue",$conyugue);
   $stmt->bindParam("direccion",$direccion);
   $stmt->bindParam("payday",$payday);
   $stmt->bindParam("credito",$credit);
    $stmt->bindParam("poblacion",$poblacion);
	 $stmt->bindParam("telefono",$telefono);

$stmt->execute();
   $stat[0] = true;
   $stat[1] = "Actualizado!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

//buscar una categoria
 public function getCategoria($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from category a where a.id = :id ");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

//todas las categorias	
public function getListCategory()
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("SELECT * FROM `category` order by created_at asc");
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "List OK";
			$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			$stat[2] = [];
			return $stat;
		}
	}

//Guardar categoria
public function saveCategory($item_name, $created_at){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("insert into category ( name , created_at ) values ( :name , :created_at )");
	   $stmt->bindParam("name",$item_name);
	     $stmt->bindParam("created_at",$created_at);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}

//actualizacion categoria
public function editCategory($nameitem,$id)
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update category set name= :value where id= :id");
    $stmt->bindParam("value",$nameitem);
	 $stmt->bindParam("id",$id);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Edit!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

//eliminar categoria
public function delCategory($id)
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("delete from category where id = :id");
	 $stmt->bindParam("id",$id);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success delete!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

//buscar categoria
public function autoCompleteCat($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM category a WHERE  name like :term order by name asc limit 10");
   $stmt->bindParam("term",$trm);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function deleteItem($iditem)
{
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("delete from m_item where id_item = :id");
   $stmt->bindParam("id",$iditem);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success Delete!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function autoCompleteItem($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM m_item a WHERE  ( item_name like :term or barcode  like :term ) and status is NULL order by item_name");
   $stmt->bindParam("term",$trm);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}
//employee empleados
public function autoCompleteCollector($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM employee a WHERE name like :term order by name desc");
   $stmt->bindParam("term",$trm);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}
//cambiar ruta
public function changeEmployee($newCollectorId,$saleId){
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update `t_sale` set id_collector=:id where sale_id= :sale");
   $stmt->bindParam("id",$newCollectorId);
   $stmt->bindParam("sale",$saleId);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success save!";
   return $stat;
 }
 catch(PDOException $ex)
 {
  
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

//cambiar status deliver_sts
public function changeDeliverSts($newStsId,$saleId){
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update `t_sale` set deliver_sts=:id where sale_id= :sale");
   $stmt->bindParam("id",$newStsId);
   $stmt->bindParam("sale",$saleId);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success save!";
   return $stat;
 }
 catch(PDOException $ex)
 {
  
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

//buscar repartidores libres
public function getAviableEmployees($id){
	 $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select id_cust,name from employee  where id_cust != :id ");
     $stmt->bindParam("id",$id);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
}
//buscar empleado
public function getEmployee($id_cust){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select * from employee  where id_cust = :id_cust  and id_puesto>0");
     $stmt->bindParam("id_cust",$id_cust);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

//autocompletado  creditos
public function autoCompleteCredit($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM t_sale a WHERE  id_client like :term  and is_credit = 1 order by sale_date");
   $stmt->bindParam("term",$trm);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

/*******************************************************************************
 END OF TABEL M_Item
 *******************************************************************************/
 
 
 /******************************************************************************
    START TABEL Product actualizacion 20/04/2021
    *******************************************************************************/
 public function saveProduct($barcode,$item_name,$price,$unit,$stock,$note){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("call saveItem(:barcode,:item_name,:unit,:stock,:price,:note)");
		$stmt->bindParam("barcode",$barcode);
	   $stmt->bindParam("item_name",$item_name);
	   $stmt->bindParam("price",$price);
	   $stmt->bindParam("note",$note);
	   $stmt->bindParam("unit",$unit);
	   $stmt->bindParam("stock",$stock);
	   $stmt->execute();
	   $stat[0] = true;
	   $stat[1] = "Success save!";
	   $stat[2] =  $stmt->fetchColumn(0);
	   return $stat;
	 }
	 catch(PDOException $ex)
	 {
	   $stat[0] = false;
	   $stat[1] = $ex->getMessage();
	   return $stat;
	 }
}
 /*******************************************************************************
 END OF TABEL Product
 *******************************************************************************/
 
 
 
 
  /*******************************************************************************
 START OF TABEL promotion
 *******************************************************************************/
 public function getPromo($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select a.* from promotion a where a.product_id = :product_id ");
     $stmt->bindParam("product_id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

/******************************************************************************
  END OF TABEL promotion
 *******************************************************************************/

public function getCustomer($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select * from customer where contract = :contract ");
     $stmt->bindParam("contract",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }
 
 //buscar cliente por contrato rapido
public function getCustomerCobro($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt =$db->prepare("select name,direccion,come_in from customer where contract = :contract ");
     $stmt->bindParam("contract",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }

/******************************************************************************
   TABEL T_JUAL AND TEMP_JUAL
 *******************************************************************************/
   public function deleteTempSaleByUser($iduser)
   {
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("delete from temp_sale where id_user = :id");
      $stmt->bindParam("id",$iduser);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = "Success Delete!";
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
 
 public function resetTempSaleByUserSession($iduser,$uniqid)
  {
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("delete from temp_sale where id_user = :id and uniqid = :uniqid");
      $stmt->bindParam("id",$iduser);
      $stmt->bindParam("uniqid",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = "Success Delete!";
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
  
  public function resetTempSaleByUserSessionOC($iduser,$uniqid)
  {
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("delete from purchase_order where id_user = :id and uniqid != :uniqid");
      $stmt->bindParam("id",$iduser);
      $stmt->bindParam("uniqid",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = "Success Delete!";
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
  
  
  public function getListTempSale($cashier,$uniqid){
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
        FROM temp_sale t, 
        (SELECT @rownum := 0) r where t.id_user= :cashier and t.uniqid= :uniqid  ORDER BY input_date DESC");
      $stmt->bindParam("cashier",$cashier);
      $stmt->bindParam("uniqid",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
  
    public function getListTempSale2($cashier,$uniqid){
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
        FROM temp_exist t, 
        (SELECT @rownum := 0) r where t.id_user= :cashier and t.uniqid= :uniqid  ORDER BY input_date DESC");
      $stmt->bindParam("cashier",$cashier);
      $stmt->bindParam("uniqid",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
  
   public function getListTempSale4($cashier,$uniqid){
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
        FROM purchase_order t, 
        (SELECT @rownum := 0) r where t.id_user = :cashier and t.uniqid != :uniqid  ORDER BY input_date DESC");
      $stmt->bindParam("cashier",$cashier);
      $stmt->bindParam("uniqid",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }

public function deleteTempSaleProduct($cashier,$uniqid,$id_item)
  {
   $db = $this->dblocal;
   try
   {
    $stmt = $db->prepare("delete from temp_sale where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
    $stmt->bindParam("id",$cashier);
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Delete!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

public function deleteTempSaleProduct2($cashier,$uniqid,$id_item)
  {
   $db = $this->dblocal;
   try
   {
    $stmt = $db->prepare("delete from temp_exist where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
    $stmt->bindParam("id",$cashier);
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Delete!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

public function deleteTempSaleProduct4($cashier,$uniqid,$id_item)
  {
   $db = $this->dblocal;
   try
   {
    $stmt = $db->prepare("delete from purchase_order where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
    $stmt->bindParam("id",$cashier);
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Delete!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

public function updateTempSale($cashier,$uniqid,$id_item)
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update temp_sale set qty=qty+1 where uniqid= :uniqid and id_user = :cashier 
      and id_item = :id_item ");

$stmt->bindParam("cashier",$cashier);
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);

$stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Edit!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

public function updateTempSale4($cashier,$uniqid,$id_item)
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update purchase_order set qty=qty+1 where uniqid= :uniqid and id_user = :cashier 
      and id_item = :id_item ");

$stmt->bindParam("cashier",$cashier);
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);

public function updateTempSaleHargaSale($cashier,$uniqid,$id_item,$value)
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("update temp_sale set price = :value
      where uniqid= :uniqid and id_user = :cashier 
      and id_item = :id_item ");