public SCAPI.ModelObject CreateEntity(EntityT entity, string db)
{
SCAPI.ModelObject ret = null;
if (string.IsNullOrWhiteSpace(db))
{
Logger.PrintLC("There was no DB associated to " + entity.TableName, 3);
return(ret);
}
if (!((entity.FlagBFD == "S") || (entity.FlagBFD == "N")))
{
Logger.PrintLC("Property FlagBFD of " + entity.TableName + " is not valid. Table will be skipped.", 3);
return(ret);
}
if (erRootObjCol != null)
{
OpenTransaction();
scItem = erRootObjCol.Add("Entity");
VBCon con = new VBCon();
//Controlli proprietà essenziali
//Nome tabella
if (!string.IsNullOrWhiteSpace(entity.TableName))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Nome Tabella"], entity.TableName))
{
Logger.PrintLC("Added Table Physical Name (" + entity.TableName + ") to " + scItem.ObjectId, 3);
}
else
{
Logger.PrintLC("Error adding Table Physical Name (" + entity.TableName + ") to " + scItem.ObjectId, 3);
CommitAndSave(trID);
return(scItem);
}
if (con.AssignToObjModel(ref scItem, "Name", entity.TableName))
{
Logger.PrintLC("Added Table Name to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Table Name to " + scItem.Name, 3);
CommitAndSave(trID);
return(scItem);
}
}
//SSA
if (!string.IsNullOrWhiteSpace(entity.SSA))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["SSA"], entity.SSA))
{
Logger.PrintLC("Added SSA to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding SSA to " + scItem.Name, 3);
}
}
//Table Description
if (!string.IsNullOrWhiteSpace(entity.TableDescr))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Descrizione Tabella"], entity.TableDescr))
{
Logger.PrintLC("Added Table Description to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Table Description to " + scItem.Name, 3);
}
}
if (!string.IsNullOrWhiteSpace(entity.TableDescr))
{
if (con.AssignToObjModel(ref scItem, "Definition", entity.TableDescr))
{
Logger.PrintLC("Added Table Definition to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Table Definition to " + scItem.Name, 3);
}
}
//Info Type
if (!string.IsNullOrWhiteSpace(entity.InfoType))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Tipologia Informazione"], entity.InfoType))
{
Logger.PrintLC("Added Information Type to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Information Type to " + scItem.Name, 3);
}
}
//Table Limit
if (!string.IsNullOrWhiteSpace(entity.TableLimit))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Perimetro Tabella"], entity.TableLimit))
{
Logger.PrintLC("Added Table Limit to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Table Limit to " + scItem.Name, 3);
}
}
//Table Granularity
if (!string.IsNullOrWhiteSpace(entity.TableGranularity))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Granularità Tabella"], entity.TableGranularity))
{
Logger.PrintLC("Added Table Granularity to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Table Granularity to " + scItem.Name, 3);
}
}
//Flag BFD
if (!string.IsNullOrWhiteSpace(entity.FlagBFD))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Flag BFD"], entity.FlagBFD))
{
Logger.PrintLC("Added Flag BFD to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Flag BFD to " + scItem.Name, 3);
}
}
//##################################################
//## Controllo esistenza DB ed eventuale aggiunta ##
if (!string.IsNullOrWhiteSpace(entity.DatabaseName))
{
if (!DatabaseN.Contains(entity.DatabaseName))
{
scDB = erRootObjCol.Add("DB2_Database");
if (con.AssignToObjModel(ref scDB, ConfigFile._TAB_NAME["Nome Database"], entity.DatabaseName))
{
Logger.PrintLC("Added Database Name to " + scDB.Name, 3);
}
else
{
Logger.PrintLC("Error adding Database Name to " + scDB.Name, 3);
}
if (!string.IsNullOrWhiteSpace(entity.HostName))
{
if (con.AssignToObjModel(ref scDB, ConfigFile._TAB_NAME["Nome host"], entity.HostName))
{
Logger.PrintLC("Added Host Name to " + scDB.Name, 3);
}
else
{
Logger.PrintLC("Error adding Host Name to " + scDB.Name, 3);
}
}
DatabaseN.Add(entity.DatabaseName);
}
}
//##################################################
//##################################################
//## Controllo esistenza SCHEMA ed eventuale aggiunta ##
if (!string.IsNullOrWhiteSpace(entity.Schema))
{
if (!SchemaN.Contains(entity.Schema))
{
scSchema = erRootObjCol.Add("Schema");
if (con.AssignToObjModel(ref scSchema, "Name", entity.Schema))
{
Logger.PrintLC("Created Schema Name to " + scSchema.Name, 3);
}
else
{
Logger.PrintLC("Error creating Schema Name to " + scSchema.Name, 3);
}
SchemaN.Add(entity.Schema);
}
//Schema
if (!string.IsNullOrWhiteSpace(entity.Schema))
{
if (con.AssignToObjModel(ref scItem, ConfigFile._TAB_NAME["Schema"], entity.Schema))
{
Logger.PrintLC("Added Schema to " + scItem.Name, 3);
}
else
{
Logger.PrintLC("Error adding Schema to " + scItem.Name, 3);
}
}
}
//##################################################
CommitAndSave(trID);
}
return(scItem);
}
/// <summary>
/// Reads and processes Table data from excel's 'TABELLE' sheet
/// </summary>
/// <param name="fileDaAprire"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static List <EntityT> ReadXFileEntity(FileInfo fileDaAprire, string db, string sheet = ConfigFile.TABELLE)
{
string file = fileDaAprire.FullName;
List <EntityT> listaFile = new List <EntityT>();
if (!File.Exists(file))
{
Logger.PrintLC("Reading Tables. File " + fileDaAprire.Name + " doesn't exist.", 3);
return(listaFile = null);
}
FileOps.RemoveAttributes(file);
if (fileDaAprire.Extension == ".xls")
{
if (!ConvertXLStoXLSX(file))
{
return(listaFile = null);
}
file = Path.ChangeExtension(file, ".xlsx");
fileDaAprire = new FileInfo(file);
}
ExcelPackage p = null;
ExcelWorkbook WB = null;
ExcelWorksheets ws = null;
try
{
p = new ExcelPackage(fileDaAprire);
WB = p.Workbook;
ws = WB.Worksheets; //.Add(wsName + wsNumber.ToString());
}
catch (Exception exp)
{
Logger.PrintLC("Reading Tables. Could not open file " + fileDaAprire.Name + "in location " + fileDaAprire.DirectoryName, 3);
return(listaFile = null);
}
bool FilesEnd = false;
int EmptyRow = 0;
int columns = 0;
foreach (var worksheet in ws)
{
if (worksheet.Name == sheet)
{
FilesEnd = false;
for (int RowPos = ConfigFile.HEADER_RIGA + 1;
FilesEnd != true;
RowPos++)
{
bool incorrect = false;
string nome = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Tabella"]].Text;
string flag = worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text;
if (string.IsNullOrWhiteSpace(nome))
{
incorrect = true;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = "";
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0));
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "KO";
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = "Valore di NOME TABELLA mancante.";
}
if (!(string.Equals(flag, "S", StringComparison.OrdinalIgnoreCase) || string.Equals(flag, "N", StringComparison.OrdinalIgnoreCase)))
{
incorrect = true;
string error = worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + 2].Text;
if (!string.IsNullOrWhiteSpace(error))
{
error = error + " ";
}
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0));
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "KO";
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET2].Value = error + "Valore di FLAG BFD non conforme.";
}
if (incorrect == false)
{
EmptyRow = 0;
EntityT ValRiga = new EntityT(row: RowPos, db: db, tName: nome);
ValRiga.TableName = nome;
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["SSA"]].Text))
{
ValRiga.SSA = worksheet.Cells[RowPos, ConfigFile._TABELLE["SSA"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome host"]].Text))
{
ValRiga.HostName = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome host"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Database"]].Text))
{
ValRiga.DatabaseName = worksheet.Cells[RowPos, ConfigFile._TABELLE["Nome Database"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Schema"]].Text))
{
ValRiga.Schema = worksheet.Cells[RowPos, ConfigFile._TABELLE["Schema"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Descrizione Tabella"]].Text))
{
ValRiga.TableDescr = worksheet.Cells[RowPos, ConfigFile._TABELLE["Descrizione Tabella"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Tipologia Informazione"]].Text))
{
ValRiga.InfoType = worksheet.Cells[RowPos, ConfigFile._TABELLE["Tipologia Informazione"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Perimetro Tabella"]].Text))
{
ValRiga.TableLimit = worksheet.Cells[RowPos, ConfigFile._TABELLE["Perimetro Tabella"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Granularità Tabella"]].Text))
{
ValRiga.TableGranularity = worksheet.Cells[RowPos, ConfigFile._TABELLE["Granularità Tabella"]].Text;
}
if (!string.IsNullOrWhiteSpace(worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text))
{
ValRiga.FlagBFD = worksheet.Cells[RowPos, ConfigFile._TABELLE["Flag BFD"]].Text;
}
listaFile.Add(ValRiga);
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 255, 0));
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Style.Font.Bold = true;
worksheet.Cells[RowPos, ConfigFile.HEADER_COLONNA_MAX_TABELLE + ConfigFile.TABELLE_EXCEL_COL_OFFSET1].Value = "OK";
}
else
{
EmptyRow += 1;
if (EmptyRow >= 10)
{
FilesEnd = true;
}
}
//******************************************
// Verifica lo stato delle successive 10 righe per determinare la fine della tabella.
int prossime = 0;
for (int i = 1; i < 11; i++)
{
if (string.IsNullOrWhiteSpace(worksheet.Cells[RowPos + i, ConfigFile._TABELLE["Nome Tabella"]].Text))
{
prossime++;
}
}
if (prossime == 10)
{
FilesEnd = true;
}
//******************************************
}
p.SaveAs(new FileInfo(Path.Combine(ConfigFile.FOLDERDESTINATION, fileDaAprire.Name)));
return(listaFile);
}
}
return(listaFile = null);
}