<?php

class PlanificadorController extends \BaseController {

	public static $jqgrid_colmodel = [
		[
			'label' => 'Orden',
			'name' => 'ORDEN',
			'hidden' => true
		],
		[
			'label' => 'Id',
			'name' => 'ID',
			'hidden' => true
		],
		[
			'label' => 'Fecha',
			'name' => 'FECHA_F',
			'searchoptions' => [
				'sopt' => ['custom']
			]
		],
		[
			'label' => 'Nombre Producto',
			'name' => 'NOMBRE',
		],
		[
			'label' => 'Cantidad',
			'name' => 'CANTIDAD',
		],
		[
			'label' => 'Estado',
			'name' => 'ESTADO',
			'stype' => 'select',
			'searchoptions' => [
				'value' => ':;1:Pendiente'
			]
		],
		[
			'label'  => 'Reagendar',
			'name'   => 'texto',
		],
		[
			'label' => 'Estado1',
			'name' => 'ESTADO1',
			'hidden' => true
		],
	];

	public static $estados = [
		1 => 'Pendiente',
		2 => 'Fabricado',
		3 => 'Control de Calidad',
	];

	public function index(){
		$title = 'Planificador';
		Breadcrumb::append_crumb($title);
		Jqgrid::add_js_colmodel('planificador', self::$jqgrid_colmodel);
		return View::make('planificador.index', compact('title'));
	}

	public function datagrid() {
		Jqgrid::init('vPlanificacion', self::$jqgrid_colmodel, self::jqgrid_format(), self::jqgrid_filter_format());
		Jqgrid::get_query()->where('DIFERENCIA', '<', 8);
		return Jqgrid::datagrid();
	}

	/**
	 * Retorna funcion anonima que define los formatos para los valores de las distintas columnas de la grilla
	 * @return function
	 */
	private static function jqgrid_format() {
		return function ($column, $value) {
			$result = $value;
			if(in_array($column, ['ESTADO'])) {
				$result = self::$estados[$value];
			}
			if (in_array($column, ['texto'])) {
				$result = '<button type="button" id="REAGENDAR' . $value . '" name="REAGENDAR_' . $value . '" style="height: 22px" class="btn btn-primary" title="Reagendar formula"><i class="fa fa-calendar"></i></button>';
            }
			return $result;
		};
	}

	/**
	 * Retorna funcion anonima que define los formatos de búsqueda para los valores de las distintas columnas de la grilla
	 * @return function
	 */
	private static function jqgrid_filter_format() {
		return function ($field, $data) {
			$result = $data;
			if (in_array($field, ['FECHA_F'])) {
				$values = explode(' | ', $data);
				$v1 = date('d-m-Y', strtotime($values[0]));
				$v2 = date('d-m-Y', strtotime($values[1]));
				$aux = "FECHA >= '$v1 00:00:00' AND FECHA <= '$v2 23:59:59'";
				$result = $aux;
			}
			return $result;
		};
	}

	/**
	 * Muestra vista agregar planificacion
	 *
	 * @return Response
	 */
	public function vista_agregar() {
		$title = 'Planificador';
		Breadcrumb::append_crumb($title);
		return View::make('planificador.agregar', compact('title'));
	}


	/**
	 * Muestra Vista de editar fórmula
	 *
	 * @return \Illuminate\Http\Response
	 */
	public function editar_planificador($id) {
		$title = 'Editar';
		Breadcrumb::append_crumb('Reagendar planificación de fecha');
		Breadcrumb::append_crumb($title);

		$planificacion     = DB::table('vPlanificacion')->where('ID', '=', $id)->first();

		return View::make('planificador.fecha', compact('id', 'title', 'planificacion'));
	}

	public function obtener_stocks() {
		$result = [];

		if(\Input::has('fields')) {
			$nreguists = [];
			$fields    = \Input::get('fields');
			$case      = "SUM(CASE ";
			foreach($fields as $key        => $value) {

				$nreguists_anterior         = [];
				$case_sub = "SUM(CASE ";
				foreach($fields as $key2   => $value2) {

					/* Lee formula */
					$nreguistFormula     = $value2['nreguist'];
    				$formula  = DB::table('vFormulas')
	    			->where('NREGUIST', '=', "$nreguistFormula")
		    		->select(DB::raw('DEN_TOTAL'))
					->first();
					$DEN_TOTAL  = $formula->DEN_TOTAL;

     				/* Calculos 1 */
	    			$factor_cantidad1           = $value['cantidad'] / $value['volumen_formula'];
		    		$factor_peso1               = round((($value['cantidad'] * 1000) * $DEN_TOTAL) / $value['peso_formula'], 0.3);

				    /* Calculos 2 */
					if($value2['nreguist'] == $value['nreguist']) break;
					$factor_cantidad2       = $value2['cantidad'] * 100 / $value2['volumen_formula'];
					$factor_peso2           = round((($value2['cantidad'] * 1000) * $DEN_TOTAL) / $value2['peso_formula'], 0.3);
					$case_sub              .= "WHEN D.NREGUIST = {$value2['nreguist']} THEN CASE WHEN UNIDMED='Kilos' THEN D.PESO * {$factor_peso2} ELSE D.VOLUMEN * {$factor_cantidad2} END";
					$nreguists_anterior[]   = $value2['nreguist'];
				}
				$case_sub .= "END)";
				if(empty($nreguists_anterior)) {
					$where_nreguist = "WHERE 1=1";
					$case_sub = '0';
				} else {
					$nreguists_anterior = implode(',', $nreguists_anterior);
					$where_nreguist = "WHERE [D].[NREGUIST] IN ($nreguists_anterior)";
				}
				$result[$value['nreguist']] = DB::select(DB::raw("SELECT D.NREGUIST_MP, D.NOMBRE,  
				CASE WHEN UNIDMED='Kilos' THEN ISNULL(tblStockRestante.STOCK_RESTANTE, ISNULL(STK.STOCK_DISPONIBLE, 0)) - (((D.PESO / 1000) * {$factor_peso1}) + ISNULL(XX.PESO, 0)) ELSE
				ISNULL(tblStockRestante.STOCK_RESTANTE, ISNULL(STK.STOCK_DISPONIBLE, 0)) - (((D.VOLUMEN / 1000) * {$factor_cantidad1}) + ISNULL(XX.VOLUMEN, 0)) END as diferencia
					FROM [vDetallesFormula] as [D] LEFT JOIN (
					SELECT ARTICULO, SUM(STK_DISPONIBLE) AS STOCK_DISPONIBLE FROM vStockProductos GROUP BY ARTICULO
					) STK ON [D].[NREGUIST_MP] = [STK].[ARTICULO]
					LEFT JOIN (
						SELECT D.NREGUIST_MP AS NREGUIST_MP, (SUM(ISNULL(STK.STOCK_DISPONIBLE, 0)) - $case_sub) AS STOCK_RESTANTE
						FROM [vDetallesFormula] as [D] LEFT JOIN (SELECT ARTICULO, SUM(STK_DISPONIBLE) AS STOCK_DISPONIBLE FROM vStockProductos GROUP BY ARTICULO) STK
						ON [D].[NREGUIST_MP] = [STK].[ARTICULO]
						$where_nreguist AND [D].[NREGUIST] IN ({$value['nreguist']})
						GROUP BY [D].[NREGUIST_MP], [D].[NOMBRE]
					) tblStockRestante ON D.NREGUIST_MP = tblStockRestante.NREGUIST_MP
					LEFT JOIN (Select vd.NREGUIST, NREGUIST_MP, SUM(PESO) PESO, SUM(VOLUMEN) VOLUMEN 
					   FROM dbo.vDetallesFormula vd Left Join dbo.vPlanificacion vp on (vd.NREGUIST=vp.NREGUIST)
                       WHERE VP.ESTADO=1 GROUP BY vd.NREGUIST, NREGUIST_MP
                    ) XX ON (XX.NREGUIST_MP=D.NREGUIST_MP  AND [XX].[NREGUIST] IN ({$value['nreguist']}))
					WHERE [D].[NREGUIST] IN ({$value['nreguist']})
					ORDER BY[D].[NOMBRE] ASC"));
				$case .= "WHEN D.NREGUIST = {$value['nreguist']} THEN CASE WHEN UNIDMED='Kilos' THEN D.PESO * {$factor_peso1} 
				ELSE D.VOLUMEN * {$factor_cantidad1} END ";
				$nreguists[] = $value['nreguist'];
			}
			$nreguists = implode(',', $nreguists);
			$case .= "END) / 1000";
		}
		$response['data'] = DB::select(DB::raw("SELECT D.NREGUIST_MP, D.NOMBRE, D.UNIDMED, $case AS STOCK_REQUERIDO, 
		CASE WHEN D.UNIDMED='Kilos' THEN ISNULL(STK.STOCK_DISPONIBLE, 0) - SUM(ISNULL(XX.PESO,0)) ELSE ISNULL(STK.STOCK_DISPONIBLE, 0) - SUM(ISNULL(XX.VOLUMEN,0)) END AS STOCK_DISPONIBLE, 
		CASE WHEN D.UNIDMED='Kilos' THEN (ISNULL(STK.STOCK_DISPONIBLE, 0) - ($case + SUM(ISNULL(XX.PESO, 0)))) ELSE (ISNULL(STK.STOCK_DISPONIBLE, 0) - ($case + SUM(ISNULL(XX.VOLUMEN, 0)))) END AS STOCK_RESTANTE
			FROM vDetallesFormula D LEFT JOIN (SELECT ARTICULO, SUM(STK_DISPONIBLE) AS STOCK_DISPONIBLE FROM vStockProductos GROUP BY ARTICULO) STK
			ON D.NREGUIST_MP = STK.ARTICULO
			LEFT JOIN (Select vd.NREGUIST, NREGUIST_MP, SUM(PESO/1000) PESO, SUM(VOLUMEN/1000) VOLUMEN FROM dbo.vDetallesFormula vd Left Join dbo.vPlanificacion vp on (vd.NREGUIST=vp.NREGUIST)
                       WHERE VP.ESTADO=1 GROUP BY vd.NREGUIST, NREGUIST_MP
			) XX ON (XX.NREGUIST_MP=STK.ARTICULO AND XX.NREGUIST IN ($nreguists))
			WHERE D.NREGUIST IN ($nreguists)
			GROUP BY D.NREGUIST_MP, D.NOMBRE, STK.STOCK_DISPONIBLE, D.UNIDMED;"));
		foreach($result as $index => $value) {
			$response['estados'][$index] = 'Disponible';
			foreach($value as $mp) {
				if($mp->diferencia < 0)
				{
					$response['estados'][$index] = 'No Disponible';
				} 
			}
		}

		$response['sql'] = "SELECT D.NREGUIST_MP, D.NOMBRE, D.UNIDMED, $case AS STOCK_REQUERIDO, 
		CASE WHEN D.UNIDMED='Kilos' THEN ISNULL(STK.STOCK_DISPONIBLE, 0) - SUM(ISNULL(XX.PESO,0)) ELSE ISNULL(STK.STOCK_DISPONIBLE, 0) - SUM(ISNULL(XX.VOLUMEN,0)) END AS STOCK_DISPONIBLE, 
		CASE WHEN D.UNIDMED='Kilos' THEN (ISNULL(STK.STOCK_DISPONIBLE, 0) - ($case + SUM(ISNULL(XX.PESO, 0)))) ELSE (ISNULL(STK.STOCK_DISPONIBLE, 0) - ($case + SUM(ISNULL(XX.VOLUMEN, 0)))) END AS STOCK_RESTANTE
			FROM vDetallesFormula D LEFT JOIN (SELECT ARTICULO, SUM(STK_DISPONIBLE) AS STOCK_DISPONIBLE FROM vStockProductos GROUP BY ARTICULO) STK
			ON D.NREGUIST_MP = STK.ARTICULO
			LEFT JOIN (Select vd.NREGUIST, NREGUIST_MP, SUM(PESO/1000) PESO, SUM(VOLUMEN/1000) VOLUMEN FROM dbo.vDetallesFormula vd Left Join dbo.vPlanificacion vp on (vd.NREGUIST=vp.NREGUIST)
                       WHERE VP.ESTADO=1 GROUP BY vd.NREGUIST, NREGUIST_MP
			) XX ON (XX.NREGUIST_MP=STK.ARTICULO AND XX.NREGUIST IN ($nreguists))
			WHERE D.NREGUIST IN ($nreguists)
			GROUP BY D.NREGUIST_MP, D.NOMBRE, STK.STOCK_DISPONIBLE, D.UNIDMED;";

		return \Response::json($response);
	}

	public function obtener($all = false, $order = 'DESC') {
		$mes = \Input::get('mes');
		$anio = \Input::get('anio');
		$mes = $mes?:date('m');
		$anio = $anio?:date('Y');
		$result = DB::table('vPlanificacion')->where('estado', 1);//Pendiente
		if(!$all) {
			$result = $result->whereRaw("MONTH(FECHA) = $mes")->whereRaw("YEAR(FECHA) = $anio");
		}
		$result = $result->orderBy('FECHA', $order)->get();
		return \Response::json($result);
	}

	public function agregar() {
		$campos = \Input::except('INDEX');

		$vdata = [
			'NREGUIST_PROD' => $campos['NREGUIST_PROD'],
			'CANTIDAD' => $campos['CANTIDAD'],
			'FECHA' => $campos['FECHA'],
			'ESTADO' => 1, //1 = PENDIENTE
		];

		$vrules = [
			'FECHA' => 'required',
		];

		$vmessages = [
			'FECHA.required' => 'Seleccione una fecha',
		];

		$validador = Validator::make($vdata, $vrules, $vmessages);

		if($validador->fails()) {
			$message = ['content' => 'Debe ingresar una fecha.', 'type' => 'error'];
			$response = ['status' => 409, 'message' => $message];
		} else {
			DB::table('PRODUCCION_PLANIFICACION')->insert($vdata);
			$message = ['content' => 'Producto ha sido agregado a planificación', 'type' => 'success'];
			$response = ['status' => 200, 'message' => $message];
		}

		return Response::json($response, $response['status']);
	}

	public function reagendar($id) {
		$vdata        = [
			'FECHA'         => Input::get('FECHA'),
		];

		$vrules       = [
			'FECHA'   => 'required',
		];

		$vmessages    = [
			'FECHA.required' => 'Seleccione una fecha',
		];

		$validador    = Validator::make($vdata, $vrules, $vmessages);
		$FECHA        = $vdata['FECHA'];

		if($validador->fails()) {
			$message  = ['content' => 'Debe ingresar una fecha.', 'type' => 'error'];
			$response = ['status' => 409, 'message' => $message];
		} else {			 
            // Actualiza Planificación
			DB::table('PRODUCCION_PLANIFICACION')
			->where('ID', $id)
			->update(['FECHA'=>$FECHA]);

			$message  = ['content' => $FECHA.' Producto ha sido agregado a planificación', 'type' => 'success'];
			$response = ['status' => 200, 'message' => $message];
		}

		return Response::json($response, $response['status']);
	}


	/**
	 * Elimina planificación de base de datos
	 * @return mixed
	 */
	public function eliminar() {
		$success = DB::table('PRODUCCION_PLANIFICACION')
			->where('ID', Input::get('id'))
			->delete();

		if($success) {
			$message = ['content' => 'La planificación fue eliminada.', 'type' => 'success'];
			$response = ['status' => 200, 'message' => $message];
		} else {
			$message = ['content' => 'La planificación no pudo ser eliminada.', 'type' => 'error'];
			$response = ['status' => 409, 'message' => $message];
		}

		return Response::json($response, $response['status']);
	}

	/**
	 * Obtiene listado de productos terminados
	 * @return mixed
	 */
	public function select_productos() {
		$query = Input::get('q', '');
		$productos = DB::table('vFormulas')
			->where('NOMBRE', 'LIKE', "%$query%")
			->select(DB::raw('NREGUIST as id, NOMBRE as text, PRECVTA as precio_venta, LITROS volumen, PESO_TOTAL peso'))
			->paginate(10);
		return $productos;
	}
}