public bool ProcessFile(string fileName, ref int rowImported, ref int totalRow) { bool res = true; TaskInfo tInfo = TaskInfo.Load(fileName); TaskHeader task = FacadeFactory.Instance.TaskHeaderFacade.GetById(tInfo.taskId); DocSuiteWebAPI.DocumentSeriesItemDTO dto = Newtonsoft.Json.JsonConvert.DeserializeObject <DocSuiteWebAPI.DocumentSeriesItemDTO>(tInfo.taskCode); FileLogger.Info(this.LoggerName, String.Format("Elaborazione file '{0}'", fileName)); TaskDetail taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.Info, Title = String.Format("Elaborazione file '{0}'", fileName), TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); //informazioni sull'archivio DocumentSeries docSeries = FacadeFactory.Instance.DocumentSeriesFacade.GetById(dto.IdDocumentSeries.Value); IList <DocumentSeriesSubsection> docSeriesSubsections = FacadeFactory.Instance.DocumentSeriesSubsectionFacade.GetAll(); ArchiveInfo archInfo = GetSelectedArchiveInfo(docSeries); List <string> columns = new List <string>(_basicFields); columns.AddRange(archInfo.VisibleChainAttributes.Select(p => p.Name)); string excelVers = ""; if (Path.GetExtension(tInfo.documentFilename).ToLower() == ".xls") { excelVers = "Excel 8.0"; } if (Path.GetExtension(tInfo.documentFilename).ToLower() == ".xlsx") { excelVers = "Excel 12.0"; } rowImported = 0; List <List <string> > righeErrore = new List <List <string> >(); string connStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"{1};HDR=Yes;IMEX=1;\"", tInfo.documentFilename, excelVers); using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); string sheetName = conn.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString(); //Gestione del file excel IDbCommand command = conn.CreateCommand(); command.CommandText = string.Format("SELECT {0} FROM [{1}]", string.Format("[{0}]", string.Join("], [", columns)), sheetName); command.CommandType = CommandType.Text; using (IDataReader dr = command.ExecuteReader()) { if (!dr.Read()) { dr.Close(); taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.ErrorType, Title = "Foglio excel vuoto.", TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); throw new DocSuiteException("Foglio excel vuoto."); } //Verifica delle colonne DataTable dt = dr.GetSchemaTable(); if (dt.Select("ColumnName = 'Subject'").Length < 1) { taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.ErrorType, Title = "Colonna [Subject] mancante.", TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); throw new DocSuiteException("Colonna [Subject] mancante."); } foreach (ArchiveAttribute attribute in archInfo.VisibleChainAttributes) { int columnIndex = dt.Select(string.Format("ColumnName = '{0}'", attribute.Name)).Length; if (columnIndex < 1 & attribute.Required) { taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.ErrorType, Title = string.Format("Colonna obbligatoria [{0}] mancante.", attribute.Name), TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); throw new DocSuiteException(string.Format("Colonna obbligatoria [{0}] mancante.", attribute.Name)); } } int currentRow = 0; //import while (true) { string rowId = string.Empty; string message = string.Empty; string subSection = string.Empty; try { rowId = dr["Subject"].ToString(); if (tInfo.IsRowProcessed(rowId)) { message = string.Format("Riga [{0}]: saltata poiché è già stato processato un elemento con campo oggetto '{1}'", currentRow + 2, rowId); FileLogger.Info(LoggerName, message); taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.Info, Title = message, TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); //next if (!dr.Read()) { break; } continue; } // Catena Biblos per la gestione dei Metadati BiblosChainInfo chain = new BiblosChainInfo(); List <DocumentInfo> mainDocs = new List <DocumentInfo>(); if (dt.Select("ColumnName = 'DOC_Main'").Length == 1) { mainDocs = ParseDocumentString(dr["DOC_Main"].ToString()); chain.AddDocuments(mainDocs); } // Recupero i metadati da EXCEL e li salvo nella catena foreach (ArchiveAttribute attribute in archInfo.VisibleChainAttributes) { if (dt.Select(string.Format("ColumnName = '{0}'", attribute.Name)).Length < 1) { continue; } FileLogger.Debug(LoggerName, attribute.Name); chain.AddAttribute(attribute.Name, string.Format(attribute.Format, dr[attribute.Name])); } // DocumentSeriesItem da salvare DocumentSeriesItem item = new DocumentSeriesItem(); if (dt.Select("ColumnName = 'Subsection'").Length > 0) { subSection = dr["Subsection"].ToString(); if (!subSection.IsNullOrEmpty()) { DocumentSeriesSubsection subSectionToAdd = docSeriesSubsections.FirstOrDefault(x => x.Description == subSection); if (subSectionToAdd != null) { item.DocumentSeriesSubsection = subSectionToAdd; } else { message = string.Format("Sottosezione [{0}] della serie documentale [{1}] non caricata perchè non presente sul database", subSection, docSeries.Name); FileLogger.Info(LoggerName, message); } } } item.DocumentSeries = docSeries; item.Subject = dr["Subject"].ToString(); // Recupero e salvo i dati di classificazione Category selectedCategory = FacadeFactory.Instance.CategoryFacade.GetById(dto.Category.Id.Value); Category root = selectedCategory.Root; if (selectedCategory.Equals(root)) { item.Category = selectedCategory; } else { item.Category = root; item.SubCategory = selectedCategory; } // Recupero e salvo l'evenatuale data di pubblicazione if (dto.PublishingDate.HasValue) { item.PublishingDate = dto.PublishingDate.Value; } // Imposto la STATUS desiderato DocumentSeriesItemStatus status = (DocumentSeriesItemStatus)dto.Status; List <DocumentInfo> annexed = new List <DocumentInfo>(); if (dt.Select("ColumnName = 'DOC_Annexed'").Length == 1) { annexed.AddRange(ParseDocumentString(dr["DOC_Annexed"].ToString())); } List <DocumentInfo> unpublished = new List <DocumentInfo>(); if (dt.Select("ColumnName = 'DOC_UnpublishedAnnexed'").Length == 1) { unpublished.AddRange(ParseDocumentString(dr["DOC_UnpublishedAnnexed"].ToString())); } // Salvo l'Item in DB FacadeFactory.Instance.DocumentSeriesItemFacade.SaveDocumentSeriesItem(item, chain, annexed, unpublished, status, "Registrazione importata da documento EXCEL."); //A questo punto sono certo che la procedura ha salvato in BiblosDS i documenti, //li rimuovo dalla sorgente. RemoveProcessedFiles(mainDocs, annexed, unpublished); message = string.Format("Riga [{0}]: Ok", currentRow + 2); FileLogger.Info(LoggerName, message); taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.Info, Title = message, TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); tInfo.AddRowInfo(rowId, message, RowInfo.RowStatus.Processed); // Incremento il counter rowImported += 1; } catch (Exception ex) { int count = dr.FieldCount; //genero la lista da inserire nella lista di errori List <string> errorValues = new List <string>(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < count; i++) { errorValues.Add(dr.GetValue(i).ToString()); } righeErrore.Add(errorValues); FileLogger.Debug(LoggerName, "QRY su EXEL: " + command.CommandText); message = string.Format("Riga [{0}]: Errore - {1}", currentRow + 2, ex.Message); FileLogger.Error(LoggerName, message, ex); taskDetail = new TaskDetail() { DetailType = DetailTypeEnum.ErrorType, Title = message, ErrorDescription = ex.StackTrace, TaskHeader = task }; FacadeFactory.Instance.TaskDetailFacade.Save(ref taskDetail); tInfo.AddRowInfo(rowId, message, RowInfo.RowStatus.Error); res = false; } finally { currentRow += 1; } //next if (!dr.Read()) { break; } } totalRow = currentRow; } // chiudo la conn oledb conn.Close(); } return(res); }
/// <summary> /// Aggiorna gli attributi della serie documentale per bandi di gara e contratti /// </summary> /// <param name="item"></param> /// <param name="pub"></param> /// <param name="archiveInfo"></param> /// <param name="chain"></param> /// <returns></returns> public BiblosChainInfo UpdateAttributeBandiDiGara(DocumentSeriesItem item, pubblicazione pub, ArchiveInfo archiveInfo, BiblosChainInfo chain) { foreach (ArchiveAttribute attribute in archiveInfo.Attributes) { string valueString = string.Empty; DynamicAttribute attr; try { attr = EnumHelper.ParseDescriptionToEnum <DynamicAttribute>(attribute.Name); } catch (Exception) { continue; } switch (attr) { case DynamicAttribute.Aggiudicatario: valueString = this.GetAziendeAggiudicatarie(pub); break; case DynamicAttribute.Lotti: valueString = this.GetLotti(pub); break; case DynamicAttribute.Liquidato: valueString = this.GetImportoSommeLiquidate(pub).ToString(); break; case DynamicAttribute.DitteInvitate: valueString = this.GetAziendeInvitate(pub); break; case DynamicAttribute.DittePartecipanti: valueString = this.GetAziendePartecipanti(pub); break; case DynamicAttribute.ProceduraAggiudicazione: valueString = this.GetSceltaContraente(pub); break; case DynamicAttribute.ImportoComplessivo: valueString = this.GetImportoAggiudicazione(pub).ToString(); break; case DynamicAttribute.StrutturaProponente: valueString = this.GetStrutturaProponente(pub); break; } if (!string.IsNullOrEmpty(valueString)) { chain.AddAttribute(attribute.Name, valueString); continue; } DateTime valueDatetime = DateTime.MinValue; switch (EnumHelper.ParseDescriptionToEnum <DynamicAttribute>(attribute.Name)) { case DynamicAttribute.DurataAl: DateTime?endDate = this.GetDataFineLavori(pub); if ((endDate.HasValue)) { valueDatetime = endDate.Value; } break; case DynamicAttribute.DurataDal: DateTime?startDate = this.GetDataInizioLavori(pub); if ((startDate.HasValue)) { valueDatetime = startDate.Value; } break; } if (valueDatetime != DateTime.MinValue) { chain.AddAttribute(attribute.Name, valueDatetime.ToString()); } } return(chain); }