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
date_default_timezone_set("America/Mexico_City");

require_once __DIR__ . "/dbconn.php";

/**
 * Clase POS que extiende dbconn, con soporte para inyección de PDO.
 * - Si se pasa un PDO en el constructor, se usa ese (ideal para compartir transacciones).
 * - Si no se pasa, crea su propia conexión con initDBO() (comportamiento anterior).
 */
class pos extends dbconn {

/**
     * Constructor con inyección opcional de PDO.
     *
     * @param PDO|null $pdo Instancia de PDO existente (opcional).
     */
    public function __construct(?PDO $pdo = null)
    {
        if ($pdo instanceof PDO) {
            // Usar la conexión inyectada
            $this->dblocal = $pdo;
        } else {
            // Comportamiento original: crear su propia conexión
            $this->initDBO();
        }
    }

/**
     * Setter opcional si prefieres inyectar el PDO después de construir el objeto.
     *
     * @param PDO $pdo
     * @return void
     */
    public function setPdo(PDO $pdo): void
    {
        $this->dblocal = $pdo;
    }
	
	/*****************************************************************
		start cfdi error
	*******************************************************************/
	
	public function saveErrorCFDI(string $sale_id, int $code, string $message){
		$db = $this->dblocal;

// Trunca el mensaje si excede VARCHAR(100)
		if (mb_strlen($message, 'UTF-8') > 100) {
			$message = mb_substr($message, 0, 100, 'UTF-8');
		}

try {
			$stmt = $db->prepare("
				INSERT INTO error_cfdi (sale_id, code, message, created_at)
				VALUES (:sale_id, :code, :message, :created_at)
			");
			$stmt->bindValue(':sale_id', $sale_id, PDO::PARAM_STR);
			$stmt->bindValue(':code', $code, PDO::PARAM_INT);
			$stmt->bindValue(':message', $message, PDO::PARAM_STR);
			$stmt->bindValue(':created_at', date('Y-m-d H:i:s'), PDO::PARAM_STR);

$stmt->execute();

$stat[0] = true;
			$stat[1] = [
				'id'        => $db->lastInsertId(),
				'sale_id'   => $sale_id,
				'code'      => $code,
				'message'   => $message,
				'created_at'=> date('Y-m-d H:i:s')
			];
			return $stat;
		} catch (PDOException $ex) {
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			return $stat;
		}
	}
	
	public function createSalidaQRAndPDF(string $sale_id, array $opts = []){
    $db = $this->dblocal;

// -------- Opciones / rutas --------
    $baseDir = realpath(__DIR__ . '/..');
    $dirPDF  = $baseDir . '/files/salidas';
    $dirQR   = $baseDir . '/files/qr';
    @mkdir($dirPDF, 0775, true);
    @mkdir($dirQR, 0775, true);
	
    // URL base fija a tu dominio (ajustada a tu comentario)
    $baseUrl = 'https://sistemasyusa.icu/sae';

// Lee logo desde settings si existe
    $logoRow = $this->getByPreffix('report_image');
    $logoVal = $logoRow[1]['val'] ?? ''; // ej. 'assets/logo.png'
    $logoPath = '';
    if ($logoVal) {
        $tmp = $baseDir . '/' . ltrim($logoVal, '/');
        if (is_file($tmp)) $logoPath = $tmp;
    }
    // Secreto HMAC (defínelo en tu tabla de settings con preffix "qr_secret")
    $secretRow = $this->getByPreffix('qr_secret');
    $secret = $secretRow[1]['val'] ?? 'Hues198020#';

// ¿Guardar PNG del QR además de incrustarlo en el PDF?
    $savePngQR = (bool)($opts['save_qr_png'] ?? true);

try {
        // -------- 1) Encabezado de venta --------
        $stmt = $db->prepare("SELECT sale_id, sale_date, client_id, total, paid, note FROM t_sale WHERE sale_id = :sid LIMIT 1");
        $stmt->execute([':sid'=>$sale_id]);
        $sale = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$sale) {
            $stat[0] = false; $stat[1] = "Venta no encontrada";
            return $stat;
        }

// -------- 2) Detalle (reusa tu método actual) --------
        $opsAll = $this->getOperations($sale_id);
        if (!($opsAll[0] ?? false)) {
            $stat[0] = false; $stat[1] = "No se pudieron obtener los detalles de la venta";
            return $stat;
        }
        $ops = $opsAll[1] ?? [];
        if (!$ops) {
            $stat[0] = false; $stat[1] = "La venta no tiene detalles";
            return $stat;
        }

// -------- 3) Token + URL para QR --------
        $total = (string)($sale['total'] ?? '0');
        $payload = $sale_id . '|' . $total;
        $token = hash_hmac('sha256', $payload, $secret);

$qrUrl = rtrim($baseUrl, '/') . '/almacen/entrada.php?sale_id=' . urlencode($sale_id) . '&t=' . $token;

// -------- 4) (Opcional) PNG del QR --------
        $qrPngPath = null;
        if ($savePngQR) {
            require_once $baseDir . "/libs/phpqrcode/qrlib.php";
            $qrPngPath = $dirQR . "/salida_" . preg_replace('/[^A-Za-z0-9_\-]/','_', $sale_id) . ".png";
            QRcode::png($qrUrl, $qrPngPath, QR_ECLEVEL_M, 5, 2);
        }

// -------- 5) PDF con TCPDF --------
        require_once $baseDir . "/libs/tcpdf/tcpdf.php";

// Configura TCPDF
        $pdf = new TCPDF('P', 'mm', 'LETTER', true, 'UTF-8', false);
        $pdf->SetCreator('Sistemas Yusa SAE');
        $pdf->SetAuthor('Sistemas Yusa');
        $pdf->SetTitle('Salida de mercancía ' . $sale_id);
        $pdf->SetSubject('Salida de mercancía');
        $pdf->SetKeywords('salida, mercancia, qr, sae');

// Header y footer
        if ($logoPath) {
            $pdf->SetHeaderData('', 0, '', ''); // usamos header custom con HTML debajo
        } else {
            $pdf->SetHeaderData('', 0, 'Salida de mercancía', "Venta: {$sale_id}");
        }
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

// Márgenes y fuente
        $pdf->SetMargins(12, 12, 12);
        $pdf->SetAutoPageBreak(TRUE, 14);
        $pdf->SetFont('dejavusans', '', 10);

$pdf->AddPage();

// Header bonito con logo (si existe)
        $htmlHeader = '<table width="100%" cellspacing="0" cellpadding="4">
            <tr>';
        if ($logoPath) {
            $htmlHeader .= '<td width="20%"><img src="'.htmlspecialchars($logoPath).'" height="45"></td>';
            $htmlHeader .= '<td width="60%" align="center" style="font-size:18px; font-weight:bold;">Salida de mercancía</td>';
            $htmlHeader .= '<td width="20%" align="right"></td>';
        } else {
            $htmlHeader .= '<td width="100%" align="center" style="font-size:18px; font-weight:bold;">Salida de mercancía</td>';
        }
        $htmlHeader .= '</tr></table>';
        $pdf->writeHTML($htmlHeader, true, false, true, false, '');

// Datos de la venta
        $fecha = htmlspecialchars((string)$sale['sale_date']);
        $cli   = htmlspecialchars((string)$sale['client_id']);
        $nota  = (string)$sale['note'];
        $htmlInfo = '
        <table width="100%" cellspacing="0" cellpadding="3" style="font-size:11px;">
            <tr>
                <td width="50%"><b>Venta:</b> '.htmlspecialchars($sale_id).'</td>
                <td width="50%" align="right"><b>Fecha:</b> '.$fecha.'</td>
            </tr>
            <tr>
                <td width="50%"><b>Cliente ID:</b> '.$cli.'</td>
                <td width="50%" align="right"><b>Total:</b> $'.number_format((float)$total,2).'</td>
            </tr>';
        if (strlen(trim($nota))>0) {
            $htmlInfo .= '<tr><td colspan="2"><b>Nota:</b> '.htmlspecialchars($nota).'</td></tr>';
        }
        $htmlInfo .= '</table><br>';
        $pdf->writeHTML($htmlInfo, true, false, true, false, '');

// Tabla de detalle (HTML)
        $htmlTable = '
        <style>
            .t { border-collapse: collapse; }
            .t th { background-color: #efefef; border: 0.4pt solid #999; font-weight: bold; }
            .t td { border: 0.4pt solid #999; }
        </style>
        <table class="t" width="100%" cellspacing="0" cellpadding="4">
            <tr>
                <th width="15%">SKU</th>
                <th width="45%">Descripción</th>
                <th width="10%">U.M.</th>
                <th width="10%" align="right">Cant.</th>
                <th width="10%" align="right">P.Unit</th>
                <th width="10%" align="right">Importe</th>
            </tr>';
        foreach ($ops as $op) {
            $sku   = htmlspecialchars((string)($op['barcode'] ?? ''));
            $desc  = htmlspecialchars((string)($op['item_name'] ?? ''));
            $um    = htmlspecialchars((string)($op['unit'] ?? ''));
            $qty   = (float)($op['qty'] ?? 0);
            $punit = (float)($op['price'] ?? 0);
            $imp   = $punit * $qty;

$htmlTable .= '
            <tr>
                <td>'.$sku.'</td>
                <td>'.$desc.'</td>
                <td align="center">'.$um.'</td>
                <td align="right">'.number_format($qty,2).'</td>
                <td align="right">$'.number_format($punit,2).'</td>
                <td align="right">$'.number_format($imp,2).'</td>
            </tr>';
        }
        $htmlTable .= '</table><br>';
        $pdf->writeHTML($htmlTable, true, false, true, false, '');

// Sección QR + texto
        $pdf->SetFont('dejavusans','B',12);
        $pdf->Cell(0, 6, 'Escanea para registrar entrada en almacén del cliente', 0, 1, 'L');
        $pdf->Ln(1);

// Posiciones
        $qrX = $pdf->GetX();   // x actual
        $qrY = $pdf->GetY();   // y actual
        $qrW = 40;             // ancho del QR (mm)

// TCPDF trae QR integrado (vectorial), lo ponemos a la izquierda
        $style = [
            'border' => 0,
            'padding' => 1,
            'fgcolor' => [0,0,0],
            'bgcolor' => false,
            'module_width' => 1,  // ancho módulo
            'module_height'=> 1,  // alto módulo
        ];
        $pdf->write2DBarcode($qrUrl, 'QRCODE,M', $qrX, $qrY, $qrW, $qrW, $style, 'N');

// Texto a la derecha del QR
        $pdf->SetXY($qrX + $qrW + 5, $qrY);
        $pdf->SetFont('dejavusans','',9);
        $pdf->MultiCell(0, 5, "URL: {$qrUrl}\nToken: ".substr($token,0,20)."…", 0, 'L');

// Footer
        $pdf->Ln(8);
        $pdf->SetFont('dejavusans','',8);
        $pdf->Cell(0, 5, 'Documento generado por appSAE', 0, 1, 'C');

// Guarda PDF
        $pdfPath = $dirPDF . "/salida_" . preg_replace('/[^A-Za-z0-9_\-]/','_', $sale_id) . ".pdf";
        $pdf->Output($pdfPath, 'F');

$stat[0] = true;
        $stat[1] = [
            'pdf'   => $pdfPath,
            'qr'    => $qrPngPath, // puede ser null si savePngQR=false
            'url'   => $qrUrl,
            'token' => $token
        ];
        return $stat;

} catch (PDOException $ex) {
        $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat;
    } catch (Throwable $ex) {
        $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat;
    }
}

//GENERA CODIGO QR Y PDF 
		public function createSalidaQRAndPDF_1(string $sale_id){
			$db = $this->dblocal;

// Rutas de salida (ajústalas si usas otras)
			$baseDirPDF = __DIR__ . "/../files/salidas";
			$baseDirQR  = __DIR__ . "/../files/qr";
			@mkdir($baseDirPDF, 0775, true);
			@mkdir($baseDirQR, 0775, true);

// URL base fija a tu dominio (HTTPS recomendado)
			$baseUrl = 'https://sistemasyusa.icu/sae';

// Secreto para HMAC (idealmente lee de settings)
			$secretRow = $this->getByPreffix('qr_secret');
			$secret = $secretRow[1]['val'] ?? 'Hues198020#';

try {
				// 1) Encabezado t_sale
				$stmt = $db->prepare("SELECT sale_id, sale_date, client_id, total, paid, note FROM t_sale WHERE sale_id = :sid LIMIT 1");
				$stmt->execute([':sid'=>$sale_id]);
				$sale = $stmt->fetch(PDO::FETCH_ASSOC);
				if (!$sale) {
					$stat[0] = false; $stat[1] = "Venta no encontrada";
					return $stat;
				}

// 2) Detalle (reusa tu método)
				$opsAll = $this->getOperations($sale_id);
				if (!($opsAll[0] ?? false)) {
					$stat[0] = false; $stat[1] = "No se pudieron obtener los detalles de la venta";
					return $stat;
				}
				$ops = $opsAll[1] ?? [];
				if (!$ops) {
					$stat[0] = false; $stat[1] = "La venta no tiene detalles";
					return $stat;
				}

// 3) Token HMAC (sale_id|total)
				$total = (string)($sale['total'] ?? '0');
				$payload = $sale_id . '|' . $total;
				$token = hash_hmac('sha256', $payload, $secret);

// 4) URL absoluta para el QR
				$qrUrl = rtrim($baseUrl, '/') . '/almacen/entrada.php?sale_id=' . urlencode($sale_id) . '&t=' . $token;

// 5) QR (PNG)
				require_once __DIR__ . "/../libs/phpqrcode/qrlib.php";
				$qrPngPath = $baseDirQR . "/salida_" . preg_replace('/[^A-Za-z0-9_\-]/','_', $sale_id) . ".png";
				QRcode::png($qrUrl, $qrPngPath, QR_ECLEVEL_M, 5, 2);

// 6) PDF (FPDF)
				require_once __DIR__ . "/../libs/fpdf/fpdf.php";
				$pdfPath = $baseDirPDF . "/salida_" . preg_replace('/[^A-Za-z0-9_\-]/','_', $sale_id) . ".pdf";

$pdf = new FPDF('P','mm','Letter');
				$pdf->AddPage();
				$pdf->SetFont('Arial','B',16);
				$pdf->Cell(0,8, utf8_decode('Salida de mercancía'), 0, 1, 'C');

$pdf->SetFont('Arial','',11);
				$pdf->Ln(2);
				$pdf->Cell(100,6, 'Venta: ' . $sale_id, 0, 0, 'L');
				$pdf->Cell(0,6, 'Fecha: ' . ($sale['sale_date'] ?? ''), 0, 1, 'R');
				$pdf->Cell(100,6, 'Cliente ID: ' . ($sale['client_id'] ?? ''), 0, 0, 'L');
				$pdf->Cell(0,6, 'Total: $' . number_format((float)$total, 2), 0, 1, 'R');
				if (!empty($sale['note'])) {
					$pdf->MultiCell(0,6, utf8_decode('Nota: ' . $sale['note']));
				}

$pdf->Ln(3);
				$pdf->SetFont('Arial','B',10);
				$pdf->Cell(20,7,'SKU',1,0,'C');
				$pdf->Cell(70,7,utf8_decode('Descripción'),1,0,'C');
				$pdf->Cell(20,7,'U.M.',1,0,'C');
				$pdf->Cell(20,7,'Cant.',1,0,'C');
				$pdf->Cell(30,7,'P. Unit.',1,0,'C');
				$pdf->Cell(30,7,'Importe',1,1,'C');

$pdf->SetFont('Arial','',10);
				foreach ($ops as $op) {
					$sku   = (string)($op['barcode'] ?? '');
					$desc  = (string)($op['item_name'] ?? '');
					$um    = (string)($op['unit'] ?? '');
					$qty   = (float)($op['qty'] ?? 0);
					$punit = (float)($op['price'] ?? 0);
					$imp   = $punit * $qty;

$pdf->Cell(20,7,utf8_decode(substr($sku,0,12)),1,0,'C');
					$pdf->Cell(70,7,utf8_decode(mb_strimwidth($desc,0,42,'…','UTF-8')),1,0,'L');
					$pdf->Cell(20,7,utf8_decode(substr($um,0,6)),1,0,'C');
					$pdf->Cell(20,7,number_format($qty,2),1,0,'R');
					$pdf->Cell(30,7,'$'.number_format($punit,2),1,0,'R');
					$pdf->Cell(30,7,'$'.number_format($imp,2),1,1,'R');
				}

$pdf->Ln(4);
				$pdf->SetFont('Arial','B',12);
				$pdf->Cell(0,7,'Escanea para registrar entrada en almacen del cliente',0,1,'L');

$y = $pdf->GetY();
				$pdf->Image($qrPngPath, 10, $y, 40, 40);
				$pdf->SetXY(55, $y);
				$pdf->SetFont('Arial','',9);
				$pdf->MultiCell(0,5, utf8_decode("URL: {$qrUrl}"), 0, 'L');

$pdf->Output('F', $pdfPath);

$stat[0] = true;
				$stat[1] = [
					'pdf'   => $pdfPath,
					'qr'    => $qrPngPath,
					'url'   => $qrUrl,
					'token' => $token
				];
				return $stat;

} catch (PDOException $ex) {
				$stat[0] = false; $stat[1] = $ex->getMessage(); return $stat;
			} catch (Throwable $ex) {
				$stat[0] = false; $stat[1] = $ex->getMessage(); return $stat;
			}
		}

/*****************************************************************
		start box
	**************************************************************&*****/
	//select box
public function getBox_open(){
	$db = $this->dblocal;
	//SELECT * FROM `box opening` WHERE DATE(`created_at`) <= CURDATE() OR TIME(`created_at`) <= "23:59:59";
	 try
	  {
	   $stmt = $db->prepare("SELECT *,cash as fondo FROM `box opening` WHERE DATE(`created_at`) = CURDATE() and TIME(`created_at`) <= '23:59:59' and box_id is null");
	   $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;
	 }
	}
	
//guardar box 
//INSERT INTO `box opening` (`id`, `stock_id`, `cash`, `user_id`, `status`, `created_at`) VALUES ('1', '1', '250', '9', '1', '2023-05-29 00:14:33.000000');
 public function new_openingBox($stock,$cash,$user_id,$status,$created_at)
	{
		$db = $this->dblocal;
		try
		{
			$kind=2;
			$stmt = $db->prepare("INSERT INTO `box opening`(`stock_id`, `cash`, `user_id`, `status`, `created_at`) values (:stock_id, :cash, :user_id, :status, :created_at)");
			$stmt->bindParam("stock_id",$stock);
			$stmt->bindParam("cash",$cash);
			$stmt->bindParam("user_id",$user_id);
			$stmt->bindParam("status",$status);
			$stmt->bindParam("created_at", $created_at);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "Success save box";
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			return $stat;
		}
	}
	//entradas y salidas de dinero
	//INSERT INTO `saving` (`id`, `concept`, `amount`, `date_at`, `kind`, `box_id`, `created_at`) 
		//VALUES ('1', 'PAGO CFE', NULL, '540', NULL, '1', NULL, '2023-05-29 07:30:15.000000');
	 public function new_InOut($concept,$amount,$kind,$user_id,$stock_id,$created_at)
	{
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("INSERT INTO `saving`(`concept`, `amount`, `kind`,`user_id`, `stock_id`, `created_at`) values (:concept, :amount, :kind,:user_id, :stock_id, :created_at)");
			$stmt->bindParam("concept",$concept);
			$stmt->bindParam("amount",$amount);
			$stmt->bindParam("kind",$kind);
			$stmt->bindParam("user_id",$user_id);
			$stmt->bindParam("stock_id",$stock_id);
			$stmt->bindParam("created_at", $created_at);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "Success save mov In/Out";
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			return $stat;
		}
	}

//id maximo de entradas salidas de dinero
 public function folio_inout(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT MAX(id) AS id from `saving` ");
     $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;
   }
 }	
 
 //saldos de entradas 
 public function get_TranInDay($awal,$akhir)
{
 $db = $this->dblocal;
 try
 {
  $stmt = $db->prepare("SELECT SUM(`amount`) as suma,created_at,`kind`,`stock_id`,id FROM `saving` WHERE `kind` = 1 and date(created_at) = CURDATE() GROUP BY kind" );
  $stmt->bindParam("awal",$awal);
  $stmt->bindParam("akhir",$akhir);
  $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;
}
}

//saldos de entradas 
 public function get_TranOutDay($awal,$akhir)
{
 $db = $this->dblocal;
 try
 {
  $stmt = $db->prepare("SELECT SUM(`amount`) as suma,created_at,`kind`,`stock_id` FROM `saving` WHERE `kind` = 2 and (`created_at` BETWEEN :awal AND :akhir) GROUP BY kind" );
  $stmt->bindParam("awal",$awal);
  $stmt->bindParam("akhir",$akhir);
  $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;
}
}

//saldos de devoluciones 
 public function getTransDev()
{
 $db = $this->dblocal;
 try
 {
  $stmt = $db->prepare("SELECT *, SUM(`price`) AS suma FROM `spend` WHERE date(`created_at`) = CURDATE() GROUP BY `id_seller`;" );
  $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;
}
}

//SELECT *,abs(SUM(`val`)) AS suma FROM `payment` WHERE date(`created_at`) = CURDATE();
 public function getTransPay()
{
 $db = $this->dblocal;
 try
 {
  $stmt = $db->prepare("SELECT *,abs(SUM(`val`)) AS suma FROM `payment` WHERE date(`created_at`) = CURDATE();" );
  $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;
}
}
 
 //registro por id
 //getInOutId
 
 public function getInOutId($id){
    $db = $this->dblocal;
    try
    {
		 $stmt = $db->prepare("select * from saving  where 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;
	  }
 }
		
	/******************************************************************************
    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 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;
	 }
}

//automplete person provider
public function autoCompleteProv($term)
{
	  $trm = "%".$term."%";
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("SELECT a.* FROM person a WHERE  name like :term and kind = 2 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 * from stock  where 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 getAllItem($category)
    {
      $db = $this->dblocal;
      try
      {
       $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM product t, 
        (SELECT @rownum := 0) r WHERE category_id = :category ORDER BY id ASC");
	   $stmt->bindParam("category",$category);	
       $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;
     }
   }
	
	//REPORTE DE inventario  con urutan
   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 item_name 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;
     }
   }
   
   //REPORTE DE inventario  con id
   public function getListItemID()
    {
      $db = $this->dblocal;
      try
      {
       $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS id,t.* FROM m_item t, 
        (SELECT @rownum := 0) r ORDER BY item_name 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;
     }
   }
   
   
   //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;
     }
   }
	/*
	SELECT a.* ,c.`username`
      FROM t_sale a 
      INNER JOIN m_user c ON a.`id_user` = c.`id_user` where a.sale_id = :id
	*/
   public function getItem($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.*, c.is_service , c.product_children, c.qy from m_item a 
	 JOIN product c ON a.`barcode` = c.`barcode` WHERE a.`id_item` = :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;
   }
 }
 
 //BUSCAMOS PRODUCTO EN OPCION MAS DE UNA PIEZA
   public function getItem3($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.*, c.is_service , c.product_children, c.qy,e.description from m_item a, product e 
	 JOIN product c ON a.`barcode` = c.`barcode` 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;
   }
 }
 
 //BUSCAMOS PRODUCTO EN OPCION MAS DE UNA PIEZA
   public function getItem_BAR($id_item){
    $db = $this->dblocal;
    try
    {
	//select a.*, c.is_service , c.product_children, c.qy,e.description from m_item a, product e 
	// JOIN product c ON a.`barcode` = c.`barcode` WHERE a.`barcode` = :id  OR and status is null
	//SELECT p.* , c.* FROM `product` p, m_item c WHERE (p.`barcode` = '112233' OR `description` = '112233') and status is null
     $stmt = $db->prepare("SELECT * FROM m_item  WHERE `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;
   }
 }
 
 //BUSCAMOS PRODUCTO EN OPCION MAS DE UNA PIEZA
   public function getItem_ALT($id_item){
    $db = $this->dblocal;
    try
    {
	//select a.*, c.is_service , c.product_children, c.qy,e.description from m_item a, product e 
	// JOIN product c ON a.`barcode` = c.`barcode` WHERE a.`barcode` = :id  OR and status is null
	//SELECT p.* , c.* FROM `product` p, m_item c WHERE (p.`barcode` = '112233' OR `description` = '112233') and status is null
     $stmt = $db->prepare("SELECT * FROM m_item  WHERE `alterno` = :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;
   }
 }
 
 //obtener registro de tabla m_item por codigo de barras
	public function getBarcode($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.*, c.is_service , c.product_children, c.qy from m_item a 
	 JOIN product c ON a.`barcode` = c.`barcode` 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 getIdProduct($barcode){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT id,name,barcode,image,product_children,price_in,qy 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;
   }
 }
 
 //ventas por producto
 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;
   }
 }
 
 //ventas por turno
 public function getVentasBox(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select sum(total) as ventas from sell where box_id is null");
     $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;
   }
 }
 
  //ventas por dealle
 public function getTotalSellId($sale_id){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT sum(`qty`*`price`) as totalSell FROM `t_sale_detail` WHERE `sale_id` = :sale_id");
     $stmt->bindParam("sale_id",$sale_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;
   }
 }
 
 
 //entradas por turno
 public function getEntradasBox(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT sum(`amount`) as entradas FROM `saving` WHERE `kind` = 1 and `box_id` is null");
     $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;
   }
 }
 
 //salidas por turno
 public function getSalidasBox(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT sum(`amount`) as salidas FROM `saving` WHERE `kind` = 2 and `box_id` is null");
     $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;
   }
 }
 
  //sleeccionar cliente por rfc
  public function getClientByRFC($client_rfc){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from customer a where a.rfc = :client_rfc ");
     $stmt->bindParam("client_rfc",$client_rfc);
     $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 getClient($id_cust){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from customer a where a.id_cust = :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;
   }
 }
 
 //guardar cliente
 public function new_customer($name,$country,$phone,$gender)
	{
		$db = $this->dblocal;
		try
		{
			$kind=2;
			$stmt = $db->prepare("insert into customer(name,country,phone,gender,kind) values (:name,:country,:phone,:gender,:kind)");
			$stmt->bindParam("name",$name);
			$stmt->bindParam("country",$country);
			$stmt->bindParam("phone",$phone);
			$stmt->bindParam("gender",$gender);
			$stmt->bindParam("kind", $kind);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = "Success save customer";
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			return $stat;
		}
	}
 
 //obtener el ultimo registro de compras
  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;
   }
 }
 
 
 //obtener el ultimo registro de compras
  public function getRes(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT c.`product_id`,c.`barcode`,p.`barcode`,p.item_name,p.stock,p.price FROM `operation2` c INNER JOIN m_item p on c.`barcode` = p.`barcode` WHERE c.`product_id` IS NULL GROUP BY p.barcode");
     $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 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 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,$item_name,$price,$unit,$stock,$tax,$note)
 {
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("UPDATE m_item 
    SET  item_name = UPPER(:item_name),
    unit= :unit,
    stock= :stock, 
	 tax= :tax,
    price= :price,
    note= :note 
    WHERE id_item= :iditem;");

$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->bindParam("tax",$tax);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Actualizado!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}
//noviembre/2021 ($barcode,$item_name,$unit,$stock,$price,$note ,$tax)
public function saveItem($barcode,$item_name,$unit,$stock,$price,$note ,$tax){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("call saveItem(:barcode,:item_name,:unit,:stock ,:price,:note,:tax)");
		$stmt->bindParam("barcode",$barcode);
	   $stmt->bindParam("item_name",$item_name);
	    $stmt->bindParam("price",$price);
	   $stmt->bindParam("unit",$unit);
	   $stmt->bindParam("stock",$stock);
	    $stmt->bindParam("note",$note);
	    $stmt->bindParam("tax",$tax);
	   $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;
	 }
}

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;
 }
}

//autocompletar por nombre o codigo
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 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;
 }
}

//autocompletar folio de venta  o trans
public function autoCompleteSell($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM sell a WHERE  ( id like :term or sale_id  like :term ) and is_draft = 0 order by id 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;
 }
}

//autocompletar por nombre o codigo CON EXITENCIAS
public function autoCompleteItemINV($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 and stock > 0 order by item_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;
 }
}
 /*******************************************************************************
 END OF TABEL M_Item
 *******************************************************************************/
 
 
 /******************************************************************************
    START TABEL Product actualizacion 20/04/2021
    *******************************************************************************/
	
/*
INSERT INTO `m_item` (`id_item`, `item_name`, `barcode`, `alterno`, `note`, `stock`, `location`, `stock2`,
 `stock3`, `stock4`, `price_in`, `price`, `tax`, `fixed_cost`, `type_tax`, `unit`, `status`, `type_prod`, 
 `created_at`) VALUES ('PB003', 'BOBINA 100 MTS UTP INTERIOR GRIS', 'C4567', 'C4567_1', 'NA', 
'100', 'M1', '0', '0', '0', '1.00', '0.00', '8', '30.00', 'tasa', 'PCS', NULL, NULL, CURRENT_TIMESTAMP);
*/

public function SaveItemOK($id_item,$item_name,$barcode,$note,$stock,$price,$tax,$unit){
	  $db = $this->dblocal;
	  try
	  {
	   $stmt = $db->prepare("INSERT INTO `m_item`(`id_item`, `item_name`, `barcode`, `note`, `stock`, `price`, `tax`, `unit`) VALUE 
	   ( :id_item,:item_name,:barcode,:note,:stock,:price,:tax,:unit) ");
	   $stmt->bindParam("id_item",$id_item);
	   $stmt->bindParam("item_name",$item_name);
	   $stmt->bindParam("barcode",$barcode);
	   $stmt->bindParam("note",$note);
	   $stmt->bindParam("stock",$stock);
	   $stmt->bindParam("price",$price);
	   $stmt->bindParam("tax",$tax);
	   $stmt->bindParam("unit",$unit);
	   $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;
	 }
}

//obtener el ultimo registro de compras
  public function getItemMax(){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("SELECT MAX(`id_item`) AS id FROM m_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 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
 *******************************************************************************/

/******************************************************************************
   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;
    }
  }
  
  //eliminamos sell temp  DELETE FROM `sell_M` WHERE `uniqid` = '64a5e1ffcc931'
   public function deleteSellTempByUniqid($uniqid)
   {
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("delete from temp_sell where uniqid = :id");
      $stmt->bindParam("id",$uniqid);
      $stmt->execute();
      $stat[0] = true;
      $stat[1] = "Success Delete!";
      return $stat;
    }
    catch(PDOException $ex)
    {
      $stat[0] = false;
      $stat[1] = $ex->getMessage();
      return $stat;
    }
  }
  
  //ELIMINAMOS registros al timbrar de la tabla 
 
 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;
    }
  }
  
  //agregar registros de tempSEll
  public function getListSellTemp($uniqid){
    $db = $this->dblocal;
    try
    {
      $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
        FROM temp_sell t, 
        (SELECT @rownum := 0) r where  t.uniqid= :uniqid  ORDER BY input_date DESC");
      $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 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 deleteSellTemp($uniqid,$id_item)
  {
   $db = $this->dblocal;
   try
   {
    $stmt = $db->prepare("delete from temp_sell where uniqid = :uniqid and id = :id_item ");
    $stmt->bindParam("uniqid",$uniqid);
    $stmt->bindParam("id_item",$id_item);
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Delete sell id!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

//eliminar registro de temp_sale
  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;
  }
}

//actualizar a ceros todo el inventario tabla m_item  valores de stok negativos
public function update_zeros()
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("UPDATE `m_item` SET `stock` = '0' WHERE `m_item`.`stock` < 0 ");
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Edit a zeros!";
    return $stat;
  }
  catch(PDOException $ex)
  {
    $stat[0] = false;
    $stat[1] = $ex->getMessage();
    return $stat;
  }
}

//actualizar a ceros todo el inventario tabla m_item valores de stok positivos 
public function update_zeros_pos()
{
  $db = $this->dblocal;
  try
  {
    $stmt = $db->prepare("UPDATE `m_item` SET `stock` = '0' WHERE `m_item`.`stock` > 0 ");
    $stmt->execute();
    $stat[0] = true;
    $stat[1] = "Success Edit a zeros!";
    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 ");