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); }