Fecha Inicial : 11/04/2018
Fecha Final :  11/04/2019
Hora Inicio: 10:05AM
Hora Final:  3:00 PM
Horas invertidas(Minutos): 245
Colaboradores: Wilson Lopez 
Actividad:
Corregir los errores en la entrada de las notas calculadas para cada estudiante , calcular las notas de a partir de la sumatoria de cada evaluación que se realizo a lo largo del 
semestre 

 Se agrego el sp calculo de promedio y studentEvaluationData

Código:
CREATE PROCEDURE VaciarTablas
AS
BEGIN
    EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?';
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
    EXEC sp_MSForEachTable 'DELETE FROM ?';
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
    EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?';
    EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 0)'
END
GO
------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_studentGroupStateData]  
AS
BEGIN
DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentGroupStateData.xml', SINGLE_BLOB) as studentGroupStateData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_studentGroupStateData FROM OPENXML(@hDoc, 'XML/studentGroupStateData/studentGroupState')
                WITH(
ID [int],
                    name [nvarchar](50)
                )
                begin
                    INSERT INTO EstadoEstudiante(ID,Nombre) SELECT ID,name FROM #tmp_studentGroupStateData
                    --EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
                end
                DROP TABLE #tmp_studentGroupStateData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE readXML_studentData
    
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentData.xml', SINGLE_BLOB) as studentData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_studentData FROM OPENXML(@hDoc, 'XML/studentData/student')
                WITH(

ID[int],
                    name [nvarchar](50),
                    lastName[nvarchar](50),
                    email [nvarchar](50),
                    carnet [nvarchar](50),
                    phone[int]
                )
                begin
                    INSERT INTO Estudiante(ID,Nombre,Apellido,Email,Carne,Telefono)SELECT ID,name,lastName,email,carnet,phone FROM #tmp_studentData
                    --EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
                end
                DROP TABLE #tmp_studentData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_teacherData]

AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    DECLARE @pName nvarchar
    DECLARE @pEmail nvarchar
    DECLARE @pPassword nvarchar
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\teacherData.xml', SINGLE_BLOB) as teacherData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_teacherData FROM OPENXML(@hDoc, 'XML/teacherData/teacher')
                WITH(
ID[int],
                    name [nvarchar](50),
                    email [nvarchar](50),
                    password [nvarchar](50)
                )
                begin
                    
                    INSERT INTO Profesor (ID,Nombre,Email,contrasenna) SELECT ID,name,email,password FROM #tmp_teacherData
                    --EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
                end
                DROP TABLE #tmp_teacherData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0 --failed
    END CATCH
    END
    GO
------------------------------------------------
CREATE PROCEDURE readXML_itemData
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\itemData.xml', SINGLE_BLOB) as itemData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_itemData FROM OPENXML(@hDoc, 'XML/itemData/item')
                WITH(
                    ID [int],
                    name [nvarchar](50)
                    
                )
                begin
                    INSERT INTO Rubros(ID,Nombre)SELECT ID,name FROM #tmp_itemData
                    --EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
                end
                DROP TABLE #tmp_itemData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE readXML_groupStateData
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupStateData.xml', SINGLE_BLOB) as groupStateData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_groupStateData FROM OPENXML(@hDoc, 'XML/groupStateData/groupState')
                WITH(
                    ID [int],
                    name [nvarchar](50)
                    
                )
                begin
                    INSERT INTO EstadoGrupo(ID,Nombre)SELECT ID,name FROM #tmp_groupStateData
                    --EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
                end
                DROP TABLE #tmp_groupStateData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_groupData] @Id_Periodo int
AS
BEGIN
    DECLARE @grupcount int
    DECLARE @y int
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupData.xml', SINGLE_BLOB) as groupData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_groupData FROM OPENXML(@hDoc, 'XML/groupData/group')
                WITH(
                     ID [int],
                     groupStateID[int],
                     teacherID[int],
                     termID[int],
                     courseName[nvarchar](50),
                     code[nvarchar](50)
                    
                )
                begin
                /* Lee el tamaño de la tabla grupo , le suma 1 ya que los ID comienzan en 1 y no en 0
                    inserta los nuevos datos , y leo el nuevo tamaño de la tabla , ese sera el stop del while
                */
                     SELECT @y = COUNT(*) FROM Grupo
                     INSERT INTO Grupo(ID,FK_Estado,FK_Profesor,FK_Periodo,NombreCurso,CodigoGrupo)SELECT ID,groupStateID,teacherID,termID,courseName,code FROM #tmp_groupData WHERE termID = @Id_Periodo


                     DECLARE @PractitionerId int = 0
                     WHILE(1 = 1)
                     BEGIN
                     SELECT @PractitionerId = MIN(ID)
                     FROM dbo.Grupo WHERE ID > @PractitionerId and FK_Periodo = @Id_Periodo
                     IF @PractitionerId IS NULL BREAK
                     --SELECT @PractitionerId
                       EXEC readXML_groupItemData @Id_Grupo = @PractitionerId
                        EXEC readXML_studentGroupData @Id_Grupo = @PractitionerId
                    END
                end
                DROP TABLE #tmp_groupData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            Select ERROR_MESSAGE()
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_groupItemData] @Id_Grupo int
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupItemData.xml', SINGLE_BLOB) as groupItemData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_groupItemData FROM OPENXML(@hDoc, 'XML/groupItemData/groupItem')
                WITH(
                    ID[int],
                     itemID [int],
                     groupID [int],
                     percentage [nvarchar](50),
                     count [nvarchar](50),
                     constantCount [nvarchar](50)
                    
                )
                begin
                    --SELECT * FROM #tmp_groupItemData
                    
                    INSERT INTO GrupoxRubro(ID,FK_Grupo,FK_Rubro,Valor,EsFijo,Q) SELECT ID,groupID,itemID,percentage,constantCount,count FROM #tmp_groupItemData WHERE groupID = @Id_Grupo
                    
                end
                DROP TABLE #tmp_groupItemData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            SELECT ERROR_MESSAGE()
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE readXML_studentGroupData @Id_Grupo int
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentGroupData.xml', SINGLE_BLOB) as studentGroupData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_studentGroupData FROM OPENXML(@hDoc, 'XML/studentGroupData/studentGroup')
                WITH(
                    ID [int],
                    totalGrade [nvarchar](50),
                    groupID [int],
                    studentID [int],
                    studentGroupStateID [int]
                )
                begin
                    --SELECT * FROM #tmp_studentGroupData
                    
                    INSERT INTO GrupoxEstudiante(ID,FK_Estudiante,FK_Grupo,NotaAcumulada,FK_Estado) SELECT ID,studentID,groupID,0,2 FROM #tmp_studentGroupData WHERE groupID = @Id_Grupo
                    
                end
                DROP TABLE #tmp_studentGroupData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            SELECT ERROR_MESSAGE()
            return 0 --failed
    END CATCH
END
GO
------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_evaluationData] @Finicial date,@Ffinal date
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    DECLARE @tmp_Date date
    DECLARE @tmp_data int
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\evaluationData.xml', SINGLE_BLOB) as evaluationData(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_evaluationData FROM OPENXML(@hDoc,'XML/evaluationData/evaluation')
                WITH(
                    ID [int],   
                    groupItemID [int],
                    name [nvarchar](50),
                    date [datetime],
                    percentage [nvarchar](50),
                    description [nvarchar](50)
                )
                begin
                
                 SET @tmp_Date = @Finicial
                 WHILE(@tmp_Date<=@Ffinal)
                    BEGIN                       
                        INSERT INTO Evaluacion(ID,FK_Grupo_Rubro,Nombre,Fecha,ValorPorcentual,Descripcion) SELECT ID,groupItemID,name,date,percentage,description FROM #tmp_evaluationData WHERE (SELECT CONVERT(date, date)) = @tmp_Date;
                        SELECT @tmp_data = ID FROM Evaluacion WHERE (SELECT CONVERT(date, Fecha)) = @tmp_Date
                        --SI ESE DIA HUBIERON EVALUACIONES AGREGUELAS A EVALUACION X ESTUDIANTE
                        iF(@tmp_data IS NOT NULL)
                        BEGIN
                            EXEC readXML_studentEvaluationData @Fecha = @tmp_Date   
                        END 
                        SET @tmp_data = NULL                                    
                        SET @tmp_Date = (SELECT DATEADD(day, 1, @tmp_Date) AS DateAdd)
                    END
                end
                DROP TABLE #tmp_evaluationData
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            SELECT ERROR_MESSAGE()
            return 0 --failed
    END CATCH
END
GO
----------------------------------------------------
CREATE PROCEDURE [dbo].[readXML_studentEvaluationData] @Fecha date
AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    DECLARE @tmp_Date date
    BEGIN TRY
        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentEvaluationData.xml', SINGLE_BLOB) as studentEvaluation(roow)
        if (@xml IS NOT NULL)
        begin
            begin tran
                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_studentEvaluation FROM OPENXML(@hDoc,'XML/studentEvaluationData/studentEvaluation')
                WITH(
                    ID [int],
                    studentGroupID [int],   
                    evaluationID [int],
                    grade[varchar](50)
                )
                begin
                    --SELECCIONE LOS IDS(@PractitionerId) DE LAS EVALUACINES DE UN DIA
                    --SELECT ID FROM Evaluacion WHERE (SELECT CONVERT(date, Fecha)) = @Fecha
                    DECLARE @PractitionerId int = 0
                     WHILE(1 = 1)
                     BEGIN
                     SELECT @PractitionerId = MIN(ID)
                     FROM dbo.Evaluacion WHERE ID > @PractitionerId and (SELECT CONVERT(date, Fecha)) = @Fecha
                     IF @PractitionerId IS NULL BREAK
                    
                     INSERT INTO EvaluacionxEstudiante(ID,FK_Grupo_Estudiante,FK_Evaluacion,Nota) SELECT ID,studentGroupID,evaluationID,grade FROM #tmp_studentEvaluation WHERE evaluationID = @PractitionerId
                     EXEC calculo_promedio @EvaluationID = @PractitionerId
                    
                    
                     END
                end
                DROP TABLE #tmp_studentEvaluation
        end
        commit tran
        return 1 --successful
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            SELECT ERROR_MESSAGE()
            return 0 --failed
    END CATCH
END
GO
----------------------------------------
CREATE PROCEDURE [dbo].[calculo_promedio] @EvaluationID int

AS
BEGIN
    DECLARE @tmp_porcetage float
    DECLARE @tmp_nota_evaluacion float
    DECLARE @tmp_nota_final float
    DECLARE @ID_GrupoxEstudiante int

        DECLARE @PractitionerId int = 0
        WHILE(1 = 1)
            BEGIN
                SELECT @PractitionerId = MIN(ID)
                FROM dbo.EvaluacionxEstudiante WHERE ID > @PractitionerId AND FK_Evaluacion = @EvaluationID
                IF @PractitionerId IS NULL BREAK
                --SELECT @PractitionerId
                SELECT @ID_GrupoxEstudiante = es.FK_Grupo_Estudiante FROM EvaluacionxEstudiante es WHERE es.ID = @PractitionerId
                SELECT @tmp_porcetage = e.ValorPorcentual FROM Evaluacion e WHERE e.ID = @EvaluationID
                SELECT @tmp_nota_evaluacion = es1.Nota FROM EvaluacionxEstudiante es1 WHERE es1.ID = @PractitionerId
                SELECT @tmp_nota_final = (@tmp_porcetage*@tmp_nota_evaluacion)/100


                --SELECT @ID_GrupoxEstudiante,@tmp_porcetage,@tmp_nota_evaluacion,@tmp_nota_final
                EXEC update_grupoxestudiante @ID = @ID_GrupoxEstudiante ,@Nota = @tmp_nota_final
            END

END

Comentarios

Publicar un comentario