public void Process()
    {
        // use the following method to update the status on the AsyncProcessorDetail
        // in the AsyncProcessManager
        AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing has started");

        try
        {
            int datasets   = 0;
            int errorCount = 0;
            int processed  = 0;
            int total      = 0;

            //PRIMER PASO
            //Nos aseguramos que el usuario que va a subir la información tiene una sesión abierta
            System.Web.Security.MembershipUser usr = System.Web.Security.Membership.GetUser();
            if (usr == null)
            {
                AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
            }

            //SEGUNDO PASO
            //Comprobamos que en los parámetros de importación están definidos el campo Fecha e Indicador así como
            //evaluar los atributos definidos para la importación
            int?colFecha = null;
            int?colValor = null;
            List <importColumns>   jsonDimensions = (List <importColumns>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <importColumns> >(ProcessorDetail.Info);
            List <DimensionPrompt> vDimensions    = new List <DimensionPrompt>();
            foreach (importColumns elem in jsonDimensions)
            {
                switch (elem.attrid)
                {
                case -3:
                    colValor = elem.column;
                    break;

                case -2:
                    colFecha = elem.column;
                    break;

                case -1:
                    break;

                default:
                    DimensionPrompt NuevaDimension = new DimensionPrompt();
                    NuevaDimension.tablecolumn = elem.column;
                    NuevaDimension.dimensionid = elem.attrid;
                    NuevaDimension.tablename   = elem.name;
                    NuevaDimension.importmode  = 0;
                    vDimensions.Add(NuevaDimension);
                    break;
                }
            }
            List <importAttributes> jsonAttributes = (List <importAttributes>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <importAttributes> >(ProcessorDetail.Atributtes);
            foreach (importAttributes elem in jsonAttributes)
            {
                DimensionPrompt NuevaDimension = new DimensionPrompt();
                NuevaDimension.dimensionid = elem.id;
                NuevaDimension.valor       = elem.name;
                NuevaDimension.importmode  = 1;
                vDimensions.Add(NuevaDimension);
            }

            //Nos aseguramos que existe el campo Fecha y el campo valor
            if ((!colValor.HasValue) || (!colFecha.HasValue))
            {
                AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
            }

            //TERCER PASO
            //Comprobamos que el fichero se subió correctamente y que por lo tanto existe en el disco
            //duro para leer
            string path = System.Web.Hosting.HostingEnvironment.MapPath("~/uploads/imports/" + usr.ProviderUserKey.ToString() + "/" + ProcessorDetail.FileName);
            if (!System.IO.File.Exists(path))
            {
                AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
            }

            //create object for CSVReader and pass the stream
            System.IO.FileStream   stream      = System.IO.File.Open(path, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            Excel.IExcelDataReader excelReader = null;

            if (System.IO.Path.GetExtension(path).ToLower() == ".xls")
            {
                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                excelReader = Excel.ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else if (System.IO.Path.GetExtension(path).ToLower() == ".xlsx")
            {
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            else
            {
                AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
            }

            //3. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            using (System.Data.DataSet dsResult = excelReader.AsDataSet())
            {
                System.Data.DataTable TablaDeDatos = new System.Data.DataTable();

                DateTime?dFechaActual = null;
                decimal? dValorActual = null;
                string   sSelectA, sSelectB, sComa, sNombre;
                foreach (System.Data.DataRow Fila in dsResult.Tables[ProcessorDetail.Hoja].Rows)
                {
                    //Nueva fila a procesar
                    total++;

                    //Inicializamos la búsqueda
                    sSelectA = string.Empty;
                    sSelectB = string.Empty;
                    sNombre  = string.Empty;
                    sComa    = string.Empty;

                    //Para cada dimension especificada buscamos aquellos datasets en las que coinciden todas sus dimensiones
                    using (Clases.cKPI_DATASETS objDataset = new Clases.cKPI_DATASETS())
                    {
                        foreach (DimensionPrompt DimensionActual in vDimensions)
                        {
                            if (DimensionActual.importmode == 0)
                            {
                                DimensionActual.valor = Fila[DimensionActual.tablecolumn].ToString().Replace("'", "");
                                sNombre = sNombre + sComa + DimensionActual.tablename + " = " + Fila[DimensionActual.tablecolumn].ToString().Replace("'", "");
                            }
                            else if (DimensionActual.importmode == 1)
                            {
                                sNombre = sNombre + sComa + DimensionActual.valor;
                            }
                            sSelectA = sSelectA + DimensionActual.DimensionSelect();
                            sSelectB = sSelectB + sComa + DimensionActual.dimensionid.ToString();
                            sComa    = ", ";
                        }

                        objDataset.indicatorid = ProcessorDetail.IndicatorId;
                        objDataset.userid      = Convert.ToInt32(usr.ProviderUserKey);
                        if (!objDataset.bExiste(sSelectA, sSelectB))
                        {
                            objDataset.indicatorid = ProcessorDetail.IndicatorId;
                            objDataset.userid      = Convert.ToInt32(usr.ProviderUserKey);
                            if (string.IsNullOrEmpty(sNombre))
                            {
                                objDataset.nombre = "undefined";
                            }
                            else
                            {
                                objDataset.nombre = sNombre;
                            }
                            objDataset.dimension = "L";
                            objDataset.importid  = ProcessorDetail.ID;
                            if (objDataset.bInsertar())
                            {
                                datasets++;
                                foreach (DimensionPrompt DimensionActual in vDimensions)
                                {
                                    using (Clases.cKPI_DIMENSION_VALUES objDimensionValues = new Clases.cKPI_DIMENSION_VALUES())
                                    {
                                        objDimensionValues.datasetid   = objDataset.datasetid;
                                        objDimensionValues.dimensionid = DimensionActual.dimensionid;
                                        objDimensionValues.codigo      = DimensionActual.valor;
                                        objDimensionValues.bInsertarFromImport();
                                    }
                                }
                            }
                        }
                        using (Clases.cKPI_DATASET_VALUES objValor = new Clases.cKPI_DATASET_VALUES())
                        {
                            objValor.indicatorid = ProcessorDetail.IndicatorId;
                            objValor.datasetid   = objDataset.datasetid.Value;
                            objValor.userid      = Convert.ToInt32(usr.ProviderUserKey);
                            objValor.importid    = ProcessorDetail.ID;
                            dFechaActual         = ConvertirStringToFecha(Fila[colFecha.Value].ToString());
                            dValorActual         = ConvertirStringToDecimal(Fila[colValor.Value].ToString());
                            if ((dFechaActual.HasValue) && (dValorActual.HasValue))
                            {
                                switch (objDataset.dimension)
                                {
                                case "D":
                                    objValor.fecha = dFechaActual;
                                    objValor.valor = dValorActual;
                                    if (objValor.bGuardarLibre(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "s":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    objValor.mes       = dFechaActual.Value.Month;
                                    if (dFechaActual.Value.Day < 8)
                                    {
                                        objValor.semana = 1;
                                    }
                                    else if (dFechaActual.Value.Day < 15)
                                    {
                                        objValor.semana = 2;
                                    }
                                    else if (dFechaActual.Value.Day < 22)
                                    {
                                        objValor.semana = 3;
                                    }
                                    else
                                    {
                                        objValor.semana = 4;
                                    }
                                    objValor.valor = dValorActual;
                                    if (objValor.bGuardarSemana(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "Q":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    objValor.mes       = dFechaActual.Value.Month;
                                    objValor.quincena  = dFechaActual.Value.Day < 16 ? 1 : 2;
                                    objValor.valor     = dValorActual;
                                    if (objValor.bGuardarQuincena(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "M":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    objValor.mes       = dFechaActual.Value.Month;
                                    objValor.valor     = dValorActual;
                                    if (objValor.bGuardarMes(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "T":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    if (dFechaActual.Value.Month < 4)
                                    {
                                        objValor.trimestre = 1;
                                    }
                                    else if (dFechaActual.Value.Month < 7)
                                    {
                                        objValor.trimestre = 2;
                                    }
                                    else if (dFechaActual.Value.Month < 10)
                                    {
                                        objValor.trimestre = 3;
                                    }
                                    else
                                    {
                                        objValor.trimestre = 4;
                                    }
                                    objValor.valor = dValorActual;
                                    if (objValor.bGuardarTrimestre(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "S":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    objValor.semestre  = dFechaActual.Value.Month < 7 ? 1 : 2;
                                    objValor.valor     = dValorActual;
                                    if (objValor.bGuardarSemestre(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "A":
                                    objValor.ejercicio = dFechaActual.Value.Year;
                                    objValor.valor     = dValorActual;
                                    if (objValor.bGuardarEjercicio(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;

                                case "L":
                                    objValor.fecha = dFechaActual;
                                    objValor.valor = dValorActual;
                                    if (objValor.bGuardarLibre(ProcessorDetail.Modo))
                                    {
                                        processed++;
                                    }
                                    else
                                    {
                                        errorCount++;
                                    }
                                    break;
                                }
                            }
                            else
                            {
                                errorCount++;
                                if (!dFechaActual.HasValue)
                                {
                                    InsertaDetalleError(total, Fila[colFecha.Value].ToString(), "El valor especificado no es un campo de FECHA válido");
                                }
                                if (!dValorActual.HasValue)
                                {
                                    InsertaDetalleError(total, Fila[colValor.Value].ToString(), "El valor especificado no es un campo de NÚMERO válido");
                                }
                            }
                        }
                    }
                    // in loops, call the UpdateStatus method to determine if you should update the counts
                    // on the current iteration. This will save processing time so your not updating the
                    // counts on every iteration
                    if (UpdateStatus(processed, total))
                    {
                        // use the following method to update the counts on the AsyncProcessorDetail
                        // in the AsyncProcessManager. This should be included inside any worker loops
                        // you may have or between code blocks to update the user
                        AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateCounts(processed, errorCount, 0, total);
                    }
                }
            }
            using (Clases.cKPI_IMPORTS objImport = new Clases.cKPI_IMPORTS())
            {
                objImport.importid       = ProcessorDetail.ID;
                objImport.num_datasets   = datasets;
                objImport.num_data_ok    = processed;
                objImport.num_data_error = errorCount;
                objImport.finalizado     = true;
                objImport.bModificar();
            }

            // check to see if the process has been cancelled by calling AsyncProcessManager.Continue
            // this method should be called within an UpdateStatus condition within any loops you have
            // and should be followed by a break;
            if (!AsyncProcessManager.Continue(ProcessorDetail.ID))
            {
                AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing cancelled");
            }

            AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing is complete");
        }
        catch (Exception ex)
        {
            string error = ex.Message + ex.StackTrace;
        }

        AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
    }
예제 #2
0
    public void Process()
    {
        // use the following method to update the status on the AsyncProcessorDetail
        // in the AsyncProcessManager
        AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing has started");

        try
        {
            int datasets   = 0;
            int errorCount = 0;
            int processed  = 0;
            int total      = 0;

            //PRIMER PASO
            //Nos aseguramos que el usuario que va a subir la información tiene una sesión abierta
            System.Web.Security.MembershipUser usr = System.Web.Security.Membership.GetUser();
            if (usr == null)
            {
                AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
            }

            //SEGUNDO PASO
            //Comprobamos que en los parámetros de importación están definidos el campo Fecha e Indicador así como
            //evaluar los atributos definidos para la importación
            int?    colValor = null;
            decimal?dValorActual = null;
            string  sSelectA, sSelectB, sComa, sNombre;
            List <importColumns>   jsonDimensions = (List <importColumns>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <importColumns> >(ProcessorDetail.Info);
            List <DimensionPrompt> vDimensions    = new List <DimensionPrompt>();
            foreach (importColumns elem in jsonDimensions)
            {
                switch (elem.attrid)
                {
                case -3:
                    colValor = elem.column;
                    break;

                default:
                    DimensionPrompt NuevaDimension = new DimensionPrompt();
                    NuevaDimension.tablecolumn = elem.column;
                    NuevaDimension.dimensionid = elem.attrid;
                    NuevaDimension.tablename   = elem.name;
                    vDimensions.Add(NuevaDimension);
                    break;
                }
            }

            //TERCER PASO
            //Recorremos todas las filas para importar
            Boolean       FilaNula                = false;
            List <object> jsonRows                = (List <object>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <object> >(ProcessorDetail.Filas);
            foreach (object row in jsonRows)
            {
                //Nueva fila a procesar
                total++;

                List <object> jsonRow = (List <object>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <object> >(row.ToString());
                //Nos aseguramos que la fila tiene todos los datos asignados no nulos
                FilaNula = false;
                for (int i = 0; i <= colValor; i++)
                {
                    if (jsonRow[i] == null)
                    {
                        FilaNula = true;
                    }
                }
                if (FilaNula)
                {
                    continue;
                }

                //Inicializamos la búsqueda
                sSelectA = " AND A.DIMENSION = '" + ProcessorDetail.Dimension + "'";
                sSelectB = string.Empty;
                sNombre  = string.Empty;
                sComa    = string.Empty;
                using (Clases.cKPI_DATASETS objDataset = new Clases.cKPI_DATASETS())
                {
                    foreach (DimensionPrompt DimensionActual in vDimensions)
                    {
                        DimensionActual.valor = jsonRow[DimensionActual.tablecolumn].ToString();
                        sSelectA = sSelectA + DimensionActual.DimensionSelect();
                        sNombre  = sNombre + sComa + DimensionActual.tablename + " = " + jsonRow[DimensionActual.tablecolumn].ToString();
                        sSelectB = sSelectB + sComa + DimensionActual.dimensionid.ToString();
                        sComa    = ", ";
                    }
                    objDataset.indicatorid = ProcessorDetail.IndicatorId;
                    objDataset.userid      = Convert.ToInt32(usr.ProviderUserKey);
                    if (!objDataset.bExiste(sSelectA, sSelectB))
                    {
                        datasets++;
                        objDataset.indicatorid = ProcessorDetail.IndicatorId;
                        objDataset.userid      = Convert.ToInt32(usr.ProviderUserKey);
                        objDataset.nombre      = sNombre;
                        objDataset.dimension   = ProcessorDetail.Dimension;
                        objDataset.importid    = ProcessorDetail.ID;
                        if (objDataset.bInsertar())
                        {
                            foreach (DimensionPrompt DimensionActual in vDimensions)
                            {
                                using (Clases.cKPI_DIMENSION_VALUES objDimensionValues = new Clases.cKPI_DIMENSION_VALUES())
                                {
                                    objDimensionValues.datasetid   = objDataset.datasetid;
                                    objDimensionValues.dimensionid = DimensionActual.dimensionid;
                                    objDimensionValues.codigo      = DimensionActual.valor;
                                    objDimensionValues.bInsertar();
                                }
                            }
                        }
                    }
                    using (Clases.cKPI_DATASET_VALUES objValor = new Clases.cKPI_DATASET_VALUES())
                    {
                        objValor.indicatorid = ProcessorDetail.IndicatorId;
                        objValor.datasetid   = objDataset.datasetid.Value;
                        objValor.userid      = Convert.ToInt32(usr.ProviderUserKey);
                        objValor.importid    = ProcessorDetail.ID;
                        dValorActual         = ConvertirStringToDecimal(jsonRow[colValor.Value].ToString());
                        if (dValorActual.HasValue)
                        {
                            switch (ProcessorDetail.Dimension)
                            {
                            //case "D":
                            //    objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                            //    objValor.mes = ConvertirStringToInteger(jsonRow[1].ToString());
                            //    objValor.dia = ConvertirStringToInteger(jsonRow[2].ToString());
                            //    objValor.valor = dValorActual;
                            //    if (objValor.bGuardarLibre(ProcessorDetail.Modo))
                            //        processed++;
                            //    else
                            //        errorCount++;
                            //    break;
                            case "s":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.mes       = ConvertirStringToInteger(jsonRow[1].ToString());
                                objValor.semana    = ConvertirStringToInteger(jsonRow[2].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarSemana(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "Q":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.mes       = ConvertirStringToInteger(jsonRow[1].ToString());
                                objValor.quincena  = ConvertirStringToInteger(jsonRow[2].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarQuincena(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "M":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.mes       = ConvertirStringToInteger(jsonRow[1].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarMes(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "T":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.trimestre = ConvertirStringToInteger(jsonRow[1].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarTrimestre(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "S":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.semestre  = ConvertirStringToInteger(jsonRow[1].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarSemestre(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "A":
                                objValor.ejercicio = ConvertirStringToInteger(jsonRow[0].ToString());
                                objValor.valor     = dValorActual;
                                if (objValor.bGuardarEjercicio(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;

                            case "L":
                                objValor.fecha = ConvertirStringToFecha(jsonRow[0].ToString());
                                objValor.valor = dValorActual;
                                if (objValor.bGuardarLibre(ProcessorDetail.Modo))
                                {
                                    processed++;
                                }
                                else
                                {
                                    errorCount++;
                                }
                                break;
                            }
                        }
                        else
                        {
                            errorCount++;
                            if (!dValorActual.HasValue)
                            {
                                InsertaDetalleError(total, jsonRow[colValor.Value].ToString(), "El valor especificado no es un campo de NÚMERO válido");
                            }
                        }
                    }
                }
                // in loops, call the UpdateStatus method to determine if you should update the counts
                // on the current iteration. This will save processing time so your not updating the
                // counts on every iteration
                if (UpdateStatus(processed, total))
                {
                    // use the following method to update the counts on the AsyncProcessorDetail
                    // in the AsyncProcessManager. This should be included inside any worker loops
                    // you may have or between code blocks to update the user
                    AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateCounts(processed, errorCount, 0, total);
                }
            }
            using (Clases.cKPI_IMPORTS objImport = new Clases.cKPI_IMPORTS())
            {
                objImport.importid       = ProcessorDetail.ID;
                objImport.num_datasets   = datasets;
                objImport.num_data_ok    = processed;
                objImport.num_data_error = errorCount;
                objImport.finalizado     = true;
                objImport.bModificar();
            }

            // check to see if the process has been cancelled by calling AsyncProcessManager.Continue
            // this method should be called within an UpdateStatus condition within any loops you have
            // and should be followed by a break;
            if (!AsyncProcessManager.Continue(ProcessorDetail.ID))
            {
                AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing cancelled");
            }

            AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing is complete");
        }
        catch (Exception ex)
        {
            string error = ex.Message + ex.StackTrace;
        }

        AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID);
    }