public override void update()
{
if (CswNbtImportDef.checkForDefinitionEntries(_CswNbtSchemaModTrnsctn, "CAF"))
{
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr(_CswNbtSchemaModTrnsctn, "CAF");
ImpMgr.CAFimportOrder("Equipment", "materials", "equipment_view", "legacyid", createLegacyId: true);
//Simple props
ImpMgr.importBinding("serialno", CswNbtObjClassEquipment.PropertyName.SerialNo, "");
ImpMgr.importBinding("barcodeid", CswNbtObjClassEquipment.PropertyName.EquipmentId, "");
ImpMgr.importBinding("expirationdate", CswNbtObjClassEquipment.PropertyName.ServiceEndsOn, "");
ImpMgr.importBinding("model", CswNbtObjClassEquipment.PropertyName.Model, "");
ImpMgr.importBinding("manufacturer", CswNbtObjClassEquipment.PropertyName.Manufacturer, "");
ImpMgr.importBinding("description", CswNbtObjClassEquipment.PropertyName.Description, "");
//Relationships and Locations
ImpMgr.importBinding("ownerid", CswNbtObjClassEquipment.PropertyName.User, CswEnumNbtSubFieldName.NodeID.ToString());
ImpMgr.importBinding("locationid", CswNbtObjClassEquipment.PropertyName.Location, CswEnumNbtSubFieldName.NodeID.ToString());
ImpMgr.importBinding("materialsubclassid", CswNbtObjClassEquipment.PropertyName.Type, CswEnumNbtSubFieldName.NodeID.ToString());
//LOB data
ImpMgr.importBinding("struct_pict", CswNbtObjClassEquipment.PropertyName.Picture, CswEnumNbtSubFieldName.Blob.ToString(), BlobTableName: "materials", LobDataPkColOverride: "materialid");
ImpMgr.finalize();
}
}
public override void update()
{
bool DefinitionExists = CswNbtImportDef.checkForDefinitionEntries( _CswNbtSchemaModTrnsctn, CswScheduleLogicNbtCAFImport.DefinitionName );
if( DefinitionExists )
{
// CAF bindings definitions for Supplies
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr( _CswNbtSchemaModTrnsctn, "CAF" );
ImpMgr.CAFimportOrder( "Supply", "packages", "supplies_view", "packageid" );
//Simple Props
ImpMgr.importBinding( "materialid", CswNbtPropertySetMaterial.PropertyName.LegacyMaterialId, "" );
ImpMgr.importBinding( "productdescription", "Description", "" );
ImpMgr.importBinding( "materialname", CswNbtPropertySetMaterial.PropertyName.TradeName, "" );
ImpMgr.importBinding( "productno", CswNbtPropertySetMaterial.PropertyName.PartNumber, "" );
ImpMgr.importBinding( "approved_trans", CswNbtPropertySetMaterial.PropertyName.ApprovedForReceiving, "" );
//Relationships
ImpMgr.importBinding( "vendorid", CswNbtPropertySetMaterial.PropertyName.Supplier, CswEnumNbtSubFieldName.NodeID.ToString() );
//LOBs
ImpMgr.importBinding( "struct_pict", CswNbtObjClassNonChemical.PropertyName.Picture, CswEnumNbtSubFieldName.Blob.ToString(), BlobTableName: "materials", LobDataPkColOverride: "materialid" );
ImpMgr.finalize();
}
} // update()
public override void update()
{
bool DefinitionExists = CswNbtImportDef.checkForDefinitionEntries( _CswNbtSchemaModTrnsctn, CswScheduleLogicNbtCAFImport.DefinitionName );
if( DefinitionExists )
{
// CAF bindings definitions for Biologicals
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr( _CswNbtSchemaModTrnsctn, "CAF" );
ImpMgr.CAFimportOrder( "Biological", "packages", "biologicals_view", "packageid" );
//Simple Props
ImpMgr.importBinding( "materialid", CswNbtPropertySetMaterial.PropertyName.LegacyMaterialId, "" );
ImpMgr.importBinding( "refno", CswNbtObjClassBiological.PropertyName.ReferenceNumber, "" );
ImpMgr.importBinding( "type", CswNbtObjClassBiological.PropertyName.Type, "" );
ImpMgr.importBinding( "species", CswNbtObjClassBiological.PropertyName.SpeciesOrigin, "" );
ImpMgr.importBinding( "biosafety", CswNbtObjClassBiological.PropertyName.BiosafetyLevel, "" );
ImpMgr.importBinding( "vectors", CswNbtObjClassBiological.PropertyName.Vectors, "" );
ImpMgr.importBinding( "storage_conditions", CswNbtObjClassBiological.PropertyName.StorageCondition, "" );
ImpMgr.importBinding( "materialname", "Biological Name", "" );
ImpMgr.importBinding( "productno", CswNbtPropertySetMaterial.PropertyName.PartNumber, "" );
ImpMgr.importBinding( "approved_trans", CswNbtPropertySetMaterial.PropertyName.ApprovedForReceiving, "" );
//Relationships
ImpMgr.importBinding( "vendorid", CswNbtPropertySetMaterial.PropertyName.Supplier, CswEnumNbtSubFieldName.NodeID.ToString() );
//LOBs
ImpMgr.importBinding( "struct_pict", CswNbtObjClassNonChemical.PropertyName.Picture, CswEnumNbtSubFieldName.Blob.ToString(), BlobTableName: "materials", LobDataPkColOverride: "materialid" );
ImpMgr.finalize();
}
} // update()
public override void update()
{
if( CswNbtImportDef.checkForDefinitionEntries( _CswNbtSchemaModTrnsctn, "CAF" ) )
{
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr( _CswNbtSchemaModTrnsctn, "CAF" );
ImpMgr.removeImportBinding( "CAF", "controlzonename", "Control Zone", "Name", "Text" );
ImpMgr.importBinding( "controlzonename", CswNbtObjClassControlZone.PropertyName.ControlZoneName, "", "CAF", "Control Zone" );
ImpMgr.finalize();
}
} // update()
public override void update()
{
if (CswNbtImportDef.checkForDefinitionEntries(_CswNbtSchemaModTrnsctn, "CAF"))
{
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr(_CswNbtSchemaModTrnsctn, "CAF");
ImpMgr.CAFimportOrder("Equipment Type", "materials_subclass", "equipment_type_view", "materialsubclassid");
//Simple props
ImpMgr.importBinding("subclassname", CswNbtObjClassEquipmentType.PropertyName.TypeName, "");
ImpMgr.finalize();
}
}
public override void update()
{
bool DefinitionExists = CswNbtImportDef.checkForDefinitionEntries( _CswNbtSchemaModTrnsctn, CswScheduleLogicNbtCAFImport.DefinitionName );
if( DefinitionExists )
{
// CAF bindings definitions for Biologicals
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr( _CswNbtSchemaModTrnsctn, "CAF" );
ImpMgr.CAFimportOrder( "Constituent", "materials", "constituents_view", "legacyid" );
//Simple Props
ImpMgr.importBinding( "name", CswNbtObjClassChemical.PropertyName.TradeName, "" );
ImpMgr.importBinding( "casno", CswNbtObjClassChemical.PropertyName.CasNo, "" );
ImpMgr.importBinding( "einecs", CswNbtObjClassChemical.PropertyName.EINECS, "" );
ImpMgr.finalize();
}
} // update()
public override void update()
{
bool DefinitionExists = CswNbtImportDef.checkForDefinitionEntries( _CswNbtSchemaModTrnsctn, CswScheduleLogicNbtCAFImport.DefinitionName );
if( DefinitionExists )
{
// CAF bindings definitions for Biologicals
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr( _CswNbtSchemaModTrnsctn, "CAF" );
ImpMgr.CAFimportOrder( "Material Component", "component_casnos", "materialcomps_view", "legacyid" );
//Simple Props
ImpMgr.importBinding( "quantity", CswNbtObjClassMaterialComponent.PropertyName.TargetPercentageValue, "" );
ImpMgr.importBinding( "quantity", CswNbtObjClassMaterialComponent.PropertyName.HighPercentageValue, "" );
//Relationships
ImpMgr.importBinding( "packageid", CswNbtObjClassMaterialComponent.PropertyName.Mixture, CswEnumNbtSubFieldName.NodeID.ToString() );
ImpMgr.importBinding( "constituentid", CswNbtObjClassMaterialComponent.PropertyName.Constituent, CswEnumNbtSubFieldName.NodeID.ToString() );
ImpMgr.finalize();
}
} // update()
/// <summary>
/// Stores data in temporary Oracle tables
/// </summary>
public static Int32 storeData(CswNbtResources CswNbtResources, string FileName, string FullFilePath, string ImportDefinitionName, bool Overwrite)
{
CswNbtSchemaModTrnsctn _CswNbtSchemaModTrnsctn = new CswNbtSchemaModTrnsctn(CswNbtResources);
//StringCollection ret = new StringCollection();
DataSet ExcelDataSet = ReadExcel(FullFilePath);
// Store the job reference in import_data_job
CswTableUpdate ImportDataJobUpdate = CswNbtResources.makeCswTableUpdate("Importer_DataJob_Insert", CswNbtImportTables.ImportDataJob.TableName);
DataTable ImportDataJobTable = ImportDataJobUpdate.getEmptyTable();
DataRow DataJobRow = ImportDataJobTable.NewRow();
DataJobRow[CswNbtImportTables.ImportDataJob.filename] = FileName;
DataJobRow[CswNbtImportTables.ImportDataJob.userid] = CswNbtResources.CurrentNbtUser.UserId.PrimaryKey;
DataJobRow[CswNbtImportTables.ImportDataJob.datestarted] = CswConvert.ToDbVal(DateTime.Now);
ImportDataJobTable.Rows.Add(DataJobRow);
Int32 JobId = CswConvert.ToInt32(DataJobRow[CswNbtImportTables.ImportDataJob.importdatajobid]);
ImportDataJobUpdate.update(ImportDataJobTable);
foreach (DataTable ExcelDataTable in ExcelDataSet.Tables)
{
string SheetName = ExcelDataTable.TableName;
CswNbtImportDef Definition = null;
//try
//{
Definition = new CswNbtImportDef(CswNbtResources, ImportDefinitionName, SheetName);
//}
//catch( Exception ex )
//{
// //OnMessage( "Sheet '" + SheetName + "' is invalid: " + ex.Message );
//}
// ignore bad sheetnames
if (null != Definition)
{
// Determine Oracle table name
Int32 i = 1;
string ImportDataTableName = CswNbtImportTables.ImportDataN.TableNamePrefix + i.ToString();
while (_CswNbtSchemaModTrnsctn.isTableDefinedInDataBase(ImportDataTableName))
{
i++;
ImportDataTableName = CswNbtImportTables.ImportDataN.TableNamePrefix + i.ToString();
}
// Generate an Oracle table for storing and manipulating data
_CswNbtSchemaModTrnsctn.addTable(ImportDataTableName, CswNbtImportTables.ImportDataN.PkColumnName);
_CswNbtSchemaModTrnsctn.addBooleanColumn(ImportDataTableName, CswNbtImportTables.ImportDataN.error, "", false);
_CswNbtSchemaModTrnsctn.addClobColumn(ImportDataTableName, CswNbtImportTables.ImportDataN.errorlog, "", false);
foreach (DataColumn ExcelColumn in ExcelDataTable.Columns)
{
_CswNbtSchemaModTrnsctn.addStringColumn(ImportDataTableName, CswNbtImportDefBinding.SafeColName(ExcelColumn.ColumnName), "", false, 4000);
}
foreach (CswNbtImportDefOrder Order in Definition.ImportOrder.Values)
{
_CswNbtSchemaModTrnsctn.addLongColumn(ImportDataTableName, Order.PkColName, "", false);
}
CswNbtResources.commitTransaction();
CswNbtResources.beginTransaction();
CswNbtResources.DataDictionary.refresh();
// Store the sheet reference in import_data_map
CswTableUpdate ImportDataMapUpdate = CswNbtResources.makeCswTableUpdate("Importer_DataMap_Insert", CswNbtImportTables.ImportDataMap.TableName);
DataTable ImportDataMapTable = ImportDataMapUpdate.getEmptyTable();
DataRow DataMapRow = ImportDataMapTable.NewRow();
DataMapRow[CswNbtImportTables.ImportDataMap.datatablename] = ImportDataTableName;
DataMapRow[CswNbtImportTables.ImportDataMap.importdefid] = Definition.ImportDefinitionId;
DataMapRow[CswNbtImportTables.ImportDataMap.importdatajobid] = JobId;
DataMapRow[CswNbtImportTables.ImportDataMap.overwrite] = CswConvert.ToDbVal(Overwrite);
DataMapRow[CswNbtImportTables.ImportDataMap.completed] = CswConvert.ToDbVal(false);
ImportDataMapTable.Rows.Add(DataMapRow);
ImportDataMapUpdate.update(ImportDataMapTable);
// Copy the Excel data into the Oracle table
CswTableUpdate ImportDataUpdate = CswNbtResources.makeCswTableUpdate("Importer_Update", ImportDataTableName);
DataTable ImportDataTable = ImportDataUpdate.getEmptyTable();
foreach (DataRow ExcelRow in ExcelDataTable.Rows)
{
bool hasData = false;
DataRow ImportRow = ImportDataTable.NewRow();
ImportRow[CswNbtImportTables.ImportDataN.error] = CswConvert.ToDbVal(false);
foreach (DataColumn ExcelColumn in ExcelDataTable.Columns)
{
if (ExcelRow[ExcelColumn] != DBNull.Value)
{
hasData = true;
}
ImportRow[CswNbtImportDefBinding.SafeColName(ExcelColumn.ColumnName)] = ExcelRow[ExcelColumn];
}
if (hasData == true)
{
ImportDataTable.Rows.Add(ImportRow);
}
}
ImportDataUpdate.update(ImportDataTable);
//OnMessage( "Sheet '" + SheetName + "' is stored in Table '" + ImportDataTableName + "'" );
} // if( null != Definition )
} // foreach( DataTable ExcelDataTable in ExcelDataSet.Tables )
CswNbtResources.commitTransaction();
CswNbtResources.beginTransaction();
//return ret;
return(JobId);
}
