Matías López: 2019

lunes, 15 de abril de 2019

Pivot y Unpivot en MSSQL

Armando Query's con Pivot y Unpivot en MSSQL (SQLServer2008 R2)

Creamos una Base de Datos, con un esquema simple de una tabla:

USE [Nombre_Base_Datos];
GO

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO

Luego insertamos algunos datos:

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

Ahora probamos Pivot (varias SubQuery's con Union) en una Query:

-- And a simple union pivot for each column...
SELECT 'Orders N° 1' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp1
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp1)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 2' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp2
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp2)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 3' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp3
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp3)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 4' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp4
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp4)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 5' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp5
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp5)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable;
GO

Finalmente probamos Unpivot en una Query:

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

lunes, 8 de abril de 2019

Canvas y SVG

Arriba: Ejemplo con Canvas.

Abajo: Ejemplo con SVG.

Fuente para leer conceptos:
Canvas | W3Schools
SVG | W3Schools

Bien., Armemos una HTML WebPage de Ta Te Ti con Canvas y otro con SVG. Ahí va el código:

<!DOCTYPE html>
<html>
	<head>
		<title>Tic Tac Toe</title>
	</head>
	<body>
		<h1>Tic Tac Toe</h1>
		<h2>Made with HTML5!</h2>
		<hr />
		<br />
		<h3>It's a Tic Tac Toe drawing made with Canvas and Filled with JavaScript.</h3>
		<br />
		<table>

		<!-- La parte de Canvas... -->
		<tbody>
			<tr>
				<td>
					<canvas id="id11" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id12" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id13" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
			<tr>
				<td>
					<canvas id="id21" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id22" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id23" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
			<tr>
				<td>
					<canvas id="id31" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id32" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id33" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
		</tbody>
		</table>
		<br />
		<hr />
		<br />
		<h3>It's a Tic Tac Toe drawing made with SVG.</h3>
		<br />
		<table>

		<!-- La parte de SVG... -->
		<tbody>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
		</tbody>
		</table>
		
		<script>

			// JavaScript para llenar los espacios Canvas...
			function setCircle(c) {
			  var ctx = c.getContext("2d");
			  ctx.beginPath();
			  ctx.arc(50,50,48,0,2*Math.PI);
			  ctx.stroke();
			}

			function setCross(c) {
			  var ctx = c.getContext("2d");
			  ctx.moveTo(0,0);
			  ctx.lineTo(100,100);
			  ctx.stroke();
			  
			  var ctx2 = c.getContext("2d");
			  ctx2.moveTo(0,100);
			  ctx2.lineTo(100,0);
			  ctx2.stroke();
			}

			var c = document.getElementById("id11");
			setCross(c);

			c = document.getElementById("id12");
			setCircle(c);

			c = document.getElementById("id13");
			setCross(c);

			c = document.getElementById("id21");
			setCircle(c);

			c = document.getElementById("id22");
			setCross(c);

			c = document.getElementById("id23");
			setCircle(c);

			c = document.getElementById("id31");
			setCross(c);

			c = document.getElementById("id32");
			setCircle(c);

			c = document.getElementById("id33");
			setCross(c);
		</script>
	</body>
</html>

Hice algunas anotaciones en el código...
Espero que les sea de utilidad!

jueves, 14 de marzo de 2019

Usando ASP.NET MVC 2 con VS2008

Creando una Aplicación Web ASP.NET MVC 2 con VS2008
Usando .NET Framework 3.5

Al crear el proyecto, vemos un entorno similar a la siguiente imagen:

Configurando el DBConnectionString desde el Web.Config

Con el ConnectionString configurado (por defecto debería apuntar al App_Data del proyecto con el esquema de tablas del modelo, guardado como .mdf; o a la base que desee, teniendo en cuenta el modelo DB necesario), debería estar andando.
NOTA: si genera un error de navegación, apuntar a la URL local raíz con el puerto generado por el Debug.
NOTA 2: Al implementar la Solución, tengan en cuenta de subir el App_Data si usan ese DataSource, o referenciar desde el ConnectionString.

Una vez corriendo la Aplicación Web, podemos armar una Vista mas en la carpeta Views > Account del projecto. La podemos usar una vez que el Usuario ingrese al Sistema como Repositorio de Documentos. La llamamos ViewIntranet.aspx.

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
View Intranet
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> 

<h2>View Intranet</h2>
<span>  Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.<br /> Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old. Richard McClintock, a Latin professor at Hampden-Sydney College in Virginia, looked up one of the more obscure Latin words, consectetur, from a Lorem Ipsum passage, and going through the cites of the word in classical literature, discovered the undoubtable source. Lorem Ipsum comes from sections 1.10.32 and 1.10.33 of "de Finibus Bonorum et Malorum" (The Extremes of Good and Evil) by Cicero, written in 45 BC. This book is a treatise on the theory of ethics, very popular during the Renaissance. The first line of Lorem Ipsum, "Lorem ipsum dolor sit amet..", comes from a line in section 1.10.32.<br /> The standard chunk of Lorem Ipsum used since the 1500s is reproduced below for those interested. Sections 1.10.32 and 1.10.33 from "de Finibus Bonorum et Malorum" by Cicero are also reproduced in their exact original form, accompanied by English versions from the 1914 translation by H. Rackham.<br /> It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using 'Content here, content here', making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text, and a search for 'lorem ipsum' will uncover many web sites still in their infancy. Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like).<br /> There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text. All the Lorem Ipsum generators on the Internet tend to repeat predefined chunks as necessary, making this the first true generator on the Internet. It uses a dictionary of over 200 Latin words, combined with a handful of model sentence structures, to generate Lorem Ipsum which looks reasonable. The generated Lorem Ipsum is therefore always free from repetition, injected humour, or non-characteristic words etc.</span>
</asp:Content>

Una vez creado el archivo ViewIntranet.aspx, procederemos a modificar los siguientes archivos de esta manera, para requerir Autenticación si se quiere ver el Documento:

// Project MVCTest
// Controllers > AccountController.cs

// Add this User View Method...
[Authorize]
public ActionResult ViewIntranet()
{
return View();
}

// Project MVCTest.Tests
// Controllers > AccountControllerTest.cs

// Add this User View Method...
[TestMethod]
public void ViewIntranet_ReturnsView()
{
// Arrange
AccountController controller = GetAccountController();

// Act
ActionResult result = controller.ViewIntranet();

// Assert
Assert.IsInstanceOfType(result, typeof(ViewResult));
}

NOTA: se puede cambiar de manera directa el estilo del Sistema desde Site.Master (yo usé Bootstrap), y se puede agregar ViewIntranet.aspx al menú para que se vea intuitivo el acceso.

Espero que les sirva este ejemplo de Sistema de Documentación con Autenticación realizado en ASP.NET MVC 2 con VS2008.

miércoles, 2 de enero de 2019

Probando NodeJS

Probando NodeJS sobre editores Online.

Node.js® es un entorno de ejecución para JavaScript construido con el motor de JavaScript V8 de Chrome; según Google, Node.js es un entorno en tiempo de ejecución multiplataforma, de código abierto, para la capa del servidor basado en el lenguaje de programación ECMAScript, asíncrono, con I/O de datos en una arquitectura orientada a eventos y basado en el motor V8 de Google.

Documentación de W3Schools:

https://www.w3schools.com/nodejs/nodejs_intro.asp

Algunos editores NodeJS:
https://repl.it/repls/SpryChiefGenericsoftware
https://www.katacoda.com/courses/nodejs/playground

Empecemos con el código! Tengamos en cuenta que guardamos los archivos como <nombre_archivo>.js, y luego corremos la instrucción node <nombre_archivo>.js para que funcione correctamente, además de usar el puerto 8080 (lo puedes cambiar siempre y cuando uses correctamente el enlace con el puerto cambiado).

Vamos a generar un texto plano desde la función createServer linealmente:

var http = require('http');



http.createServer(function (req, res) {

  res.writeHead(200, 'Test en Node.JS', {'Content-Type': 'text/html'});

  res.end('Hola Mundo! Test en Node.JS!');

}).listen(8080);

Ahora generemos código HTML (createServer lo usamos luego para generar el log en la consola):

var http = require('http');



var requestListener = function (req, res) {

  res.writeHead(200, 'WebPage de Test en Node.JS', {'Content-Type': 'text/html'});

  res.write('<h1>Hola Mundo!</h1>');

  res.write('<h2>WebPage de Test en Node.JS</h2>');

  res.write('<hr />');

  res.write('<p>Hecho con Node.JS.</p>');

  return res.end();

};



var server = http.createServer(requestListener);

server.listen(8080, function() { console.log('Listening on port 8080')});

También podemos usar API's externas. En este caso uso CORS Anywhere (la opción online), un Open API hecha por Rob Wu (https://cors-anywhere.herokuapp.com/), que elimina las políticas de CORS del navegador al hacer un call a un enlace externo (tener en cuenta que se define el encabezado en el cliente, si es que uso alguna política de Token).
El enlace que voy a usar, https://www.google.com/search?q=games, pero de la forma
https://cors-anywhere.herokuapp.com/<enlace_a_utilizar>

Veamos (en el código hay algunas anotaciones importantes):

var http = require('http');

var requestListener = function (req, res) {
res.writeHead(200, 'AngularJS Local Call Test', {'Content-Type': 'text/html'});
// **********

// Note 1: I didn't keep comma operator usage inside res.write(...)! Change this please!
// **********
res.write('<!DOCTYPE html>');

res.write('<html>');
res.write('<head>');
res.write('<title>AngularJS Local Call Test</title>');
res.write('<script src="angular.min.js"></script>');
res.write('<script>');

// **********
// Note 2: I used a local AngularJS v1.2.32! Save it to Server!

// **********
res.write('var app = angular.module('myApp', []);');
res.write('app.controller('myCtrl', function($scope, $http) {');
res.write('$http.get("AppData.html").then(function (response) {');
res.write('$scope.dataCall = response.data;');
res.write('})');
res.write('.catch(function(response) {');
res.write('$scope.dataCall = "Error!";');
res.write('});');
res.write('var sURL =  'https://cors-anywhere.herokuapp.com/https://www.google.com/search?q=games';');
res.write('var req = {');
res.write('method: 'GET',');
res.write('url: sURL,');
res.write('headers: {');
res.write(''Authorization': 'Negotiate', ');
res.write(''Access-Control-Allow-Headers': 'Content-Type,X-Requested-With,Authorization,Origin,Access-Control-Allow-Origin,Accept',');
res.write(''Access-Control-Allow-Origin': '*',');
res.write(''cache-control': 'no-cache',');
res.write(''X-PINGOTHER': 'pingpong'');
res.write('}');
res.write('};');
res.write('$http.get(sURL, req).then(function (response) {');
res.write('$scope.WebAPICall = response.data;');
res.write('})');
res.write('.catch(function(response) {');
res.write('$scope.WebAPICall = "Error!";');
res.write('});');
res.write('});');
res.write('</script>');
res.write('</head>');
res.write('<body>');
res.write('<h1>AngularJS Local Call Test</h1>');
res.write('<h2>Fill data from a string HTML file.</h2>');
res.write('<hr />');
res.write('<div ng-app="myApp" ng-controller="myCtrl">');
res.write('webData: {{ dataCall }}');
res.write('<br />');
res.write('<br />');
res.write('webapiData: {{ WebAPICall }}');
res.write('</div>');
res.write('</body>');
res.write('</html>');

return res.end();
};

var server = http.createServer(requestListener);
server.listen(8080, function() { console.log('Listening on port 8080')});

Espero que les sea de utilidad!