public DocumentSeries GetDocumentSeriesSingle(int series)
{
DocumentSeries documentSeries = null;
StringBuilder oSQL = new StringBuilder();
oSQL.Append("SELECT series, seriesName ");
oSQL.Append(string.Format("FROM NNM1 where series = '{0}'", series));
DbCommand sqlCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString());
using (this.reader = this.dataBase.ExecuteReader(sqlCommand))
{
while (this.reader.Read())
{
documentSeries = new DocumentSeries()
{
series = int.Parse(this.reader.GetValue(0).ToString()),
seriesName = this.reader.GetValue(1).ToString()
};
}
}
return(documentSeries);
}
private ArchiveInfo GetSelectedArchiveInfo(DocumentSeries docSeries)
{
if (docSeries != null)
{
return(DocumentSeriesFacade.GetArchiveInfo(docSeries));
}
return(null);
}
public ICollection <ResolutionKindDocumentSeries> GetResolutionAndSeries()
{
ResolutionKind resolutionKind = null;
DocumentSeries documentSeries = null;
return(NHibernateSession.QueryOver <ResolutionKindDocumentSeries>()
.JoinAlias(x => x.DocumentSeries, () => documentSeries)
.JoinAlias(x => x.ResolutionKind, () => resolutionKind)
.List <ResolutionKindDocumentSeries>());
}
protected override IQueryOver <ResolutionKind, ResolutionKind> DecorateCriteria(IQueryOver <ResolutionKind, ResolutionKind> queryOver)
{
ResolutionKindDocumentSeries resolutionKindDocumentSeries = null;
DocumentSeries documentSeries = null;
return(queryOver
.Left.JoinAlias(x => x.ResolutionKindDocumentSeries, () => resolutionKindDocumentSeries)
.Left.JoinAlias(() => resolutionKindDocumentSeries.DocumentSeries, () => documentSeries)
.Where(x => x.IsActive == 1));
}
public ICollection <ResolutionKindDocumentSeries> GetByIdResolutionKind(Guid idResolutionKind)
{
ResolutionKind resolutionKind = null;
DocumentSeries documentSeries = null;
return(NHibernateSession.QueryOver <ResolutionKindDocumentSeries>()
.JoinAlias(x => x.DocumentSeries, () => documentSeries)
.JoinAlias(x => x.ResolutionKind, () => resolutionKind)
.Where(() => resolutionKind.Id == idResolutionKind)
.List <ResolutionKindDocumentSeries>());
}
public ResolutionKindDocumentSeries GetByResolutionAndSeries(Guid idResolutionKind, int idSeries)
{
ResolutionKind resolutionKind = null;
DocumentSeries documentSeries = null;
return(NHibernateSession.QueryOver <ResolutionKindDocumentSeries>()
.JoinAlias(x => x.DocumentSeries, () => documentSeries)
.JoinAlias(x => x.ResolutionKind, () => resolutionKind)
.Where(() => resolutionKind.Id == idResolutionKind)
.And(() => documentSeries.Id == idSeries)
.SingleOrDefault <ResolutionKindDocumentSeries>());
}
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);
}