<?php

require_once "conexion.php";

class ModeloVentas
{

	/*=============================================
	MOSTRAR VENTAS
	=============================================*/

	static public function mdlMostrarVentas($tabla, $item, $valor)
	{

		if ($item != null) {

			$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla WHERE $item = :$item ORDER BY id ASC");

			$stmt->bindParam(":" . $item, $valor, PDO::PARAM_STR);

			$stmt->execute();

			return $stmt->fetch();
		} else {

			$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla ORDER BY id ASC");

			$stmt->execute();

			return $stmt->fetchAll();
		}


		$stmt = null;
	}


	/*=============================================
	REGISTRO DE VENTA
	=============================================*/

	static public function mdlIngresarVenta($tabla, $datos)
	{

		$stmt = Conexion::conectar()->prepare("INSERT INTO $tabla(codigo, id_cliente, id_vendedor, productos, impuesto, neto, total, metodo_pago, total_pagado, total_pendiente_pago, descuento, observacion) VALUES (:codigo, :id_cliente, :id_vendedor, :productos, :impuesto, :neto, :total, :metodo_pago, :total_pagado, :total_pendiente_pago, :descuento, :observacion)");

		$stmt->bindParam(":codigo", $datos["codigo"], PDO::PARAM_INT);
		$stmt->bindParam(":id_cliente", $datos["id_cliente"], PDO::PARAM_INT);
		$stmt->bindParam(":id_vendedor", $datos["id_vendedor"], PDO::PARAM_INT);
		$stmt->bindParam(":productos", $datos["productos"], PDO::PARAM_STR);
		$stmt->bindParam(":impuesto", $datos["impuesto"], PDO::PARAM_STR);
		$stmt->bindParam(":neto", $datos["neto"], PDO::PARAM_STR);
		$stmt->bindParam(":total", $datos["total"], PDO::PARAM_STR);
		$stmt->bindParam(":metodo_pago", $datos["metodo_pago"], PDO::PARAM_STR);
		$stmt->bindParam(":total_pagado", $datos["total_pagado"], PDO::PARAM_STR);
		$stmt->bindParam(":total_pendiente_pago", $datos["total_pendiente_pago"], PDO::PARAM_STR);
		$stmt->bindParam(":descuento", $datos["descuento"], PDO::PARAM_STR);
		$stmt->bindParam(":observacion", $datos["observacion"], PDO::PARAM_STR);

		if ($stmt->execute()) {

			return "ok";
		} else {

			return "error";
		}


		$stmt = null;
	}

	/*=============================================
	EDITAR VENTA
	=============================================*/

	static public function mdlEditarVenta($tabla, $datos)
	{

		$stmt = Conexion::conectar()->prepare("UPDATE $tabla SET  id_cliente = :id_cliente, id_vendedor = :id_vendedor, productos = :productos, impuesto = :impuesto, neto = :neto, total= :total, total_pagado = :total_pagado, total_pendiente_pago = :total_pendiente_pago, descuento = :descuento, metodo_pago = :metodo_pago, observacion = :observacion WHERE codigo = :codigo");

		$stmt->bindParam(":codigo", $datos["codigo"], PDO::PARAM_INT);
		$stmt->bindParam(":id_cliente", $datos["id_cliente"], PDO::PARAM_INT);
		$stmt->bindParam(":id_vendedor", $datos["id_vendedor"], PDO::PARAM_INT);
		$stmt->bindParam(":productos", $datos["productos"], PDO::PARAM_STR);
		$stmt->bindParam(":impuesto", $datos["impuesto"], PDO::PARAM_STR);
		$stmt->bindParam(":neto", $datos["neto"], PDO::PARAM_STR);
		$stmt->bindParam(":total", $datos["total"], PDO::PARAM_STR);
		$stmt->bindParam(":metodo_pago", $datos["metodo_pago"], PDO::PARAM_STR);
		$stmt->bindParam(":total_pagado", $datos["total_pagado"], PDO::PARAM_STR);
		$stmt->bindParam(":total_pendiente_pago", $datos["total_pendiente_pago"], PDO::PARAM_STR);
		$stmt->bindParam(":descuento", $datos["descuento"], PDO::PARAM_STR);
		$stmt->bindParam(":observacion", $datos["observacion"], PDO::PARAM_STR);

		if ($stmt->execute()) {

			return "ok";
		} else {

			return "error";
		}


		$stmt = null;
	}

	/*=============================================
	ELIMINAR VENTA
	=============================================*/

	static public function mdlEliminarVenta($tabla, $datos)
	{

		$stmt = Conexion::conectar()->prepare("DELETE FROM $tabla WHERE id = :id");

		$stmt->bindParam(":id", $datos, PDO::PARAM_INT);

		if ($stmt->execute()) {

			return "ok";
		} else {

			return "error";
		}



		$stmt = null;
	}



	/*=============================================
	RANGO FECHAS LADO SERVIDOR
	=============================================*/

	static public function mdlRangoFechasVentasServer($tabla, $tabla2, $tabla3, $item, $valor, $between1, $between2)
	{

		if ($item != null) {

			$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla WHERE $item = :$item");

			$stmt->bindParam(":" . $item, $valor, PDO::PARAM_STR);

			$stmt->execute();

			return $stmt->fetch();
		} else {


			##Leer los valores
			$draw = $_POST["draw"];
			$row = $_POST["start"];
			$rowsPorPagina = $_POST["length"];
			$columnIndex = $_POST["order"][0]['column'];
			$columnName = $_POST["columns"][$columnIndex]['data'];
			$columnOrder = $_POST["order"][0]['dir'];
			$busqueda = $_POST["search"]["value"];



			$busquedaArray = array();

			$busquedaConsulta = "";
			if ($busqueda != "") {
				$busquedaConsulta = "AND (
						$tabla.id LIKE :id OR
						$tabla.codigo LIKE :codigo OR
						$tabla3.nombre LIKE :nombre OR 
						$tabla2.nombre LIKE :nombre OR 
						$tabla.metodo_pago LIKE :metodo_pago OR
						$tabla.total_pagado  LIKE :total_pagado  OR
						$tabla.total_pendiente_pago LIKE :total_pendiente_pago OR
						$tabla.descuento LIKE :descuento OR
						$tabla.observacion LIKE :observacion OR
						$tabla.fecha LIKE :fecha)";







				$busquedaArray = array(
					'id' => "%$busqueda%",
					'codigo' => "%$busqueda%",
					'nombre' => "%$busqueda%",
					'metodo_pago' => "%$busqueda%",
					'total_pagado' => "%$busqueda%",
					'total_pendiente_pago' => "%$busqueda%",
					'descuento' => "%$busqueda%",
					'observacion' => "%$busqueda%",
					'fecha' => "%$busqueda%"
				);
			}
			if ($_SESSION['perfil'] == "Administrador") {
				

				if ($between1 == null) {

					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];

					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT  COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta);
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];


					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta . " ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");


				} elseif ($between1 == $between2) {


					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];

					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT  COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta . "AND $tabla.fecha like '%$between2%'");
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];

					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta . "AND $tabla.fecha like '%$between2%' ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");


				} elseif ($between1 !== null && $between2 !== null) {

					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];



					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT  COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta . "AND $tabla.fecha  BETWEEN '$between1' AND '$between2'");
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];


					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta . "AND $tabla.fecha  BETWEEN '$between1' AND '$between2' ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");
					}
			
				} else if ($_SESSION['perfil'] == "Vendedor") {


				if ($between1 == null) {

					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];

					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE $tabla.id_vendedor = $valor  " . $busquedaConsulta);
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];

					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE $tabla.id_vendedor = $valor " . $busquedaConsulta . " ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");


				} elseif ($between1 == $between2) {

					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];

					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE $tabla.id_vendedor = $valor " . $busquedaConsulta. "AND $tabla.fecha like '%$between2%'");
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];

					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE $tabla.id_vendedor = $valor " . $busquedaConsulta . "AND $tabla.fecha like '%$between2%' ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");
				
			
			
				} elseif ($between1 !== null && $between2 !== null) {


					#Total de registros
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente");
					$stmt->execute();
					$registros = $stmt->fetch();
					$totalRegistros = $registros["cuentaAll"];

					#Total de registros filtrados
					$stmt = Conexion::conectar()->prepare("SELECT COUNT(*) AS cuentaAll FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE 1 " . $busquedaConsulta);
					$stmt->execute($busquedaArray);
					$registros = $stmt->fetch();
					$totalRegistrosConFiltro = $registros["cuentaAll"];

					#Traer Todos Los Registros
					$stmt = Conexion::conectar()->prepare("SELECT $tabla.*, $tabla2.nombre as usuario,
					$tabla3.nombre as cliente FROM $tabla
					INNER JOIN $tabla2 ON $tabla2.id = $tabla.id_vendedor
					INNER JOIN $tabla3 ON $tabla3.id = $tabla.id_cliente
					WHERE $tabla.id_vendedor = $valor " . $busquedaConsulta . "AND $tabla.fecha  BETWEEN '$between1' AND '$between2' ORDER BY " . $columnName . " " . $columnOrder . " LIMIT :limit,:offset");
				}
			}


			foreach ($busquedaArray as $key => $item1) {
				$stmt->bindParam(':' . $key, $item1, PDO::PARAM_STR);
			}
			$stmt->bindParam(':limit', $row, PDO::PARAM_INT);
			$stmt->bindParam(':offset', $rowsPorPagina, PDO::PARAM_INT);
			//$stmt -> bindParam(":".$codigo, $valor, PDO::PARAM_STR);
			$stmt->execute();
			$registrosTotales = $stmt->fetchAll();

			$data = array();


			foreach ($registrosTotales as $rowsRegistros) {
				$data[] = array(

					"id" => $rowsRegistros["id"],
					"codigo" => $rowsRegistros["codigo"],
					"id_cliente" => $rowsRegistros["cliente"],
					"total_pendiente_pago" => $rowsRegistros["total_pendiente_pago"],
					"total" => $rowsRegistros["total"],
					"fecha" => $rowsRegistros["fecha"],
					"descuento" => $rowsRegistros["descuento"],
					"total_pagado" => $rowsRegistros["total_pagado"],
					"id_vendedor" => $rowsRegistros["usuario"]




				);
			}

			##retorno de informacion

			$response = array(
				"draw" => intval($draw),
				"iTotalRecords" => $totalRegistros,
				"iTotalDisplayRecords" => $totalRegistrosConFiltro,
				"data" => $data
			);

			return $response;
		}
	}


	/*=============================================
	RANGO FECHAS
	=============================================*/

	static public function mdlRangoFechasVentas($tabla, $fechaInicial, $fechaFinal)
	{

		if ($fechaInicial == null) {

			$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla ORDER BY id ASC");

			$stmt->execute();

			return $stmt->fetchAll();
		} else if ($fechaInicial == $fechaFinal) {

			$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla WHERE fecha like '%$fechaFinal%'");

			$stmt->bindParam(":fecha", $fechaFinal, PDO::PARAM_STR);

			$stmt->execute();

			return $stmt->fetchAll();
		} else {

			$fechaActual = new DateTime();
			$fechaActual->add(new DateInterval("P1D"));
			$fechaActualMasUno = $fechaActual->format("d-m-Y");

			$fechaFinal2 = new DateTime($fechaFinal);
			$fechaFinal2->add(new DateInterval("P1D"));
			$fechaFinalMasUno = $fechaFinal2->format("d-m-Y");

			if ($fechaFinalMasUno == $fechaActualMasUno) {

				$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla WHERE fecha BETWEEN '$fechaInicial' AND '$fechaFinalMasUno'");
			} else {


				$stmt = Conexion::conectar()->prepare("SELECT * FROM $tabla WHERE fecha BETWEEN '$fechaInicial' AND '$fechaFinal'");
			}

			$stmt->execute();

			return $stmt->fetchAll();
		}
	}

	/*=============================================
	SUMAR EL TOTAL DE VENTAS
	=============================================*/

	static public function mdlSumaTotalVentas($tabla)
	{

		$stmt = Conexion::conectar()->prepare("SELECT SUM(total) as total FROM $tabla");

		$stmt->execute();

		return $stmt->fetch();



		$stmt = null;
	}
}
