Domingo 21/06/2015 (Nuevos Filtros)

Tiempo invertido: 6 horas y 30 minutos, aproximadamente.

13:00-19:30

Para este nuevo proyecto se ve a necesitar crear nuevos filtros, con los cuales podremos acceder a los diferentes datos que son necesarios para poder realizar de una forma efectiva y exitosa las nuevas consultas.

Para poder realizar estos nuevos filtros es necesario crear varias funciones nuevas, las cuales seran las encargadas de realizar todas las acciones necesarias para poder tener el resultado que se requiere. 

Estas funciones son las encargadas de realizar las acciones para tener los datos como: los indices de productividad de un lote, o por un tipo de actividad especifica o por una tipo de solicitud.

 Acontinuacion se explicara brevemente como se implementaran las funciones en la base de datos, y asi obtener el resultado necesario.

Primero se debe crear un procedimiento en la cual se actualiza el indice de productividad, en el cual lo que se realiza es un Update del indice de productividad de un lote por ciclo, esto se hace de la siguiente manera: se suman los tres balances que se encuentran en el lote por ciclo que son los balances de suministros, maquinaria y servicios, luego esto se divide entre los metros cuadrados del lote para asi obtener el indice de productividad del ciclo y poder actualizarlo en la tabla.

CREATE PROCEDURE APSP_UpdateProductivityIndex
AS
BEGIN
    UPDATE AP_LotXCycle SET ProductivityIndex = (LC.SuppliesBalance + LC.ServicesBalance + LC.MachineryBalance) / L.SquareMeters
        FROM dbo.AP_LotXCycle LC
        inner join dbo.AP_Lot L ON L.ID = LC.FK_Lot
END

Tambien se debe crear una funcion por medio de la cual se obtenga los indices de productividad de los lotes en un cierto rango de fechas, para poder realizar esta funcion de deben tener varios datos como parametros que son los siguientes: el FK del lote, fecha inicio, fecha fin (Rango de fechas), la actividad y el tipo de solicitud.

Luego de obtener estos datos como parametros se declaran dos variables en la funcion, una de esta variables es una tabla donde se guardan los ID, la otra variables es el indice de productividad.

Luego de declarar las variables de la funcion se procede a verificar todos los datos para poder obtener el indice de productividad de un ciclo en un rango especifico, para poder lograr esto se debe hacer uso de la funcion de SQL ISNULL para poder ir comparando y verificando que los datos que nos dan por parametros son los correctos.

Luego de comprara todos los datos y veridficar que son correctos, en la variable indice de productividad se van a sumar los balances de los tres tipos de solicitudes que existen, luego de que se suman estos balances, el resultado se divide entre los metros cuadrados del lote para asi poder obtener el indice de productividad en el rango de fecha pedido.

Acontinuacion el codigo de dicha funcion:

CREATE FUNCTION APFN_ProductivityIndexReport(@FK_LotXCycle INT, @Start DATE, @End DATE, @ActivityType VARCHAR(50), @RequestType VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
    DECLARE @QueryResult TABLE(ID INT)
    DECLARE @ProductivityIndex FLOAT
    INSERT INTO @QueryResult(ID)
    SELECT R.ID FROM dbo.AP_Historical H
        inner join dbo.AP_Request R ON R.ID = H.FK_Request
        inner join dbo.AP_ActivityType AT ON AT.ID = R.FK_ActivityType
        inner join dbo.AP_RequestType RT ON RT.ID = R.FK_RequestType
        WHERE R.FK_LotXCycle = @FK_LotXCycle
            and AT.Name = ISNULL(@ActivityType, AT.Name)
            and RT.Name = ISNULL(@RequestType, RT.Name)
            and    (H.ActivityDate >= ISNULL(@Start, H.ActivityDate) and H.ActivityDate <= ISNULL(@End, H.ActivityDate))
    SELECT @ProductivityIndex = ISNULL(@ProductivityIndex, 0) + ISNULL(SUM(ISNULL(SerM.Amount, 0)), 0) FROM dbo.AP_ServiceMovement SerM
        inner join dbo.AP_ServiceRequest SerR ON SerR.ID = SerM.FK_ServiceRequest
        inner join @QueryResult R ON R.ID = SerR.ID
    SELECT @ProductivityIndex = ISNULL(@ProductivityIndex, 0) + ISNULL(SUM(ISNULL(MacM.Amount, 0)), 0) FROM dbo.AP_MachineryMovement MacM
        inner join dbo.AP_MachineryRequest MacR ON MacR.ID = MacM.FK_MachineryRequest
        inner join @QueryResult R ON R.ID = MacR.ID
    SELECT @ProductivityIndex = ISNULL(@ProductivityIndex, 0) + ISNULL(SUM(ISNULL(SupM.Amount, 0)), 0) FROM dbo.AP_SupplyMovement SupM
        inner join dbo.AP_SupplyRequest SupR ON SupR.ID = SupM.FK_SupplyRequest
        inner join @QueryResult R ON R.ID = SupR.ID

    SELECT @ProductivityIndex = @ProductivityIndex / L.SquareMeters FROM dbo.AP_Lot L
        inner join dbo.AP_LotXCycle LC ON LC.FK_Lot = L.ID
        WHERE LC.ID = @FK_LotXCycle           

    RETURN @ProductivityIndex
END

Luego de tener el indice de productividad de cada ciclo, se debe crear un nuevo procedimiento por el medio del cual se pueden observar los indices de productividad de los ciclos que se encuentran entre un cierto rango de tiempo.

Para crear este procedimiento se necesita: el FK de la propiedad, fecha inicio y fecha fin (rango de tiempo), la actividad, y tipo de solicitud.

Luego que se tiene todos estos datos, se seleccionan todos los lotes que se encuentran dentro del rango de fechas, y se muestran de forma descendente (Mayor a menor).

CREATE PROCEDURE APSP_ProductivityIndexes(@FK_Property INT, @Start DATE, @End DATE, @Activity VARCHAR(50), @RequestType VARCHAR(50))
AS
BEGIN
    BEGIN TRY
        SELECT L.ID, dbo.APFN_ProductivityIndexReport(LC.ID, @Start, @End, @Activity, @RequestType) AS ProductivityIndex FROM dbo.AP_Lot L
            inner join dbo.AP_LotXCycle LC ON LC.FK_Lot = L.ID
            WHERE L.FK_Property = @FK_Property
            ORDER BY ProductivityIndex DESC
    END TRY
    BEGIN CATCH
        RETURN @@ERROR * -1
    END CATCH
END

Estos procedimientos son los encargados de realizar las nuevas consultas, ya que por medio de ellos podremos filtrar y encontrar los datos que se requieren en los reportes que se deben realizar, y asi poder obtener los diferentes indices de productividad que tiene cada lote en un respectivo ciclo.