public async Task <Transformer> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, TransformerForeign trForeign, EntityWriteOption opt)
{
// check if entity already exists
Transformer existingTr = await _context.Transformers.SingleOrDefaultAsync(tr => tr.WebUatId == trForeign.WebUatId);
// check if we should not modify existing entities
if (opt == EntityWriteOption.DontReplace && existingTr != null)
{
return(existingTr);
}
// check if substation type is valid
string ssTypeSubstation = "SubStation";
string ssTypeGenStation = "Generating Station";
if (!(trForeign.StationType == ssTypeSubstation || trForeign.StationType == ssTypeGenStation))
{
_log.LogCritical($"substation type is not {ssTypeSubstation} or {ssTypeGenStation} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
MajorSubstation hvSubstation = null;
int hvSubstationWebUatId = -1;
if (trForeign.StationType == ssTypeSubstation)
{
// The transformer is in Substation
hvSubstationWebUatId = trForeign.HVStationWebUatId;
hvSubstation = await _context.MajorSubstations.SingleOrDefaultAsync(hvss => hvss.WebUatId == hvSubstationWebUatId);
if (hvSubstation == null)
{
_log.LogCritical($"Unable to find MajorSubstation with webUatId {hvSubstationWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
}
GeneratingStation hvGenStation = null;
int hvGenstationWebUatId = -1;
if (trForeign.StationType == ssTypeGenStation)
{
// The transformer is in GeneratingStation
hvGenstationWebUatId = trForeign.HVStationWebUatId;
hvGenStation = await _context.GeneratingStations.SingleOrDefaultAsync(hvgt => hvgt.WebUatId == hvGenstationWebUatId);
if (hvGenStation == null)
{
_log.LogCritical($"Unable to find GeneratingStation with webUatId {hvGenstationWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
}
// find the HV Voltage of the Transformer via the Voltage WebUatId
int hvVoltWebUatId = trForeign.HighVoltLevelWebUatId;
VoltLevel hvVolt = await _context.VoltLevels.SingleOrDefaultAsync(vl => vl.WebUatId == hvVoltWebUatId);
// if voltage level doesnot exist, skip the import. Ideally, there should not be such case
if (hvVolt == null)
{
_log.LogCritical($"Unable to find HV VoltLevel with webUatId {hvVoltWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
// find the LV Voltage of the Transformer via the Voltage WebUatId
int lvVoltWebUatId = trForeign.LowVoltLevelWebUatId;
VoltLevel lvVolt = await _context.VoltLevels.SingleOrDefaultAsync(vl => vl.WebUatId == lvVoltWebUatId);
// if voltage level doesnot exist, skip the import. Ideally, there should not be such case
if (lvVolt == null)
{
_log.LogCritical($"Unable to find LV VoltLevel with webUatId {lvVoltWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
// uncomment this after vendor complies for non null LV voltage types
// return null;
}
// find the State of the substation via the State WebUatId
int stateWebUatId = trForeign.StateWebUatId;
State state = await _context.States.SingleOrDefaultAsync(s => s.WebUatId == stateWebUatId);
// if state doesnot exist, skip the import. Ideally, there should not be such case
if (state == null)
{
_log.LogCritical($"Unable to find State with webUatId {stateWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
// find the TransformerType of the Transformer via the TransformerTypeWebUatId
int trTypeWebUatId = trForeign.TransTypeWebUatId;
TransformerType trType = await _context.TransformerTypes.SingleOrDefaultAsync(trt => trt.WebUatId == trTypeWebUatId);
// if TransformerType doesnot exist, skip the import. Ideally, there should not be such case
if (trType == null)
{
_log.LogCritical($"Unable to find TransformerType with webUatId {trTypeWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}");
return(null);
}
// check if we have to replace the entity completely
if (opt == EntityWriteOption.Replace && existingTr != null)
{
_context.Transformers.Remove(existingTr);
}
// if entity is not present, then insert or check if we have to replace the entity completely
if (existingTr == null || (opt == EntityWriteOption.Replace && existingTr != null))
{
Transformer newTr = new Transformer();
newTr.Name = trForeign.Name;
newTr.StationType = trForeign.StationType;
newTr.HighVoltLevelId = hvVolt.VoltLevelId;
if (lvVolt != null)
{
newTr.LowVoltLevelId = lvVolt.VoltLevelId;
}
newTr.TransformerNumber = trForeign.TransformerNumber;
newTr.TransformerTypeId = trType.TransformerTypeId;
newTr.StateId = state.StateId;
newTr.MVACapacity = trForeign.MVACapacity;
newTr.CodDate = trForeign.CodDate;
newTr.CommDate = trForeign.CommDate;
newTr.DecommDate = trForeign.DecommDate;
newTr.WebUatId = trForeign.WebUatId;
if (trForeign.StationType == ssTypeSubstation)
{
newTr.HvSubstationId = hvSubstation.MajorSubstationId;
}
else if (trForeign.StationType == ssTypeGenStation)
{
newTr.HvGeneratingStationId = hvGenStation.GeneratingStationId;
}
_context.Transformers.Add(newTr);
await _context.SaveChangesAsync();
return(newTr);
}
// check if we have to modify the entity
if (opt == EntityWriteOption.Modify && existingTr != null)
{
existingTr.Name = trForeign.Name;
existingTr.StationType = trForeign.StationType;
existingTr.HighVoltLevelId = hvVolt.VoltLevelId;
if (lvVolt != null)
{
existingTr.LowVoltLevelId = lvVolt.VoltLevelId;
}
existingTr.TransformerNumber = trForeign.TransformerNumber;
existingTr.TransformerTypeId = trType.TransformerTypeId;
existingTr.StateId = state.StateId;
existingTr.MVACapacity = trForeign.MVACapacity;
existingTr.CodDate = trForeign.CodDate;
existingTr.CommDate = trForeign.CommDate;
existingTr.DecommDate = trForeign.DecommDate;
if (trForeign.StationType == ssTypeSubstation)
{
existingTr.HvSubstationId = hvSubstation.MajorSubstationId;
}
else if (trForeign.StationType == ssTypeGenStation)
{
existingTr.HvGeneratingStationId = hvGenStation.GeneratingStationId;
}
await _context.SaveChangesAsync();
return(existingTr);
}
return(null);
}
public List <TransformerForeign> ExtractTransformersForeign(string oracleConnString)
{
using (OracleConnection con = new OracleConnection(oracleConnString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = @"select ID, TRANSFORMER_NAME, HV_STATION_ID, CIRCUIT_NUMBER, TYPE_GT_ICT, MVA_CAPACITY,
DATETIME_OF_COMMISSIONING, DATETIME_OF_DECOMMISSIONING, DATETIME_OF_COD, LOCATION_ID,
STATIONTYPE, HV_VOLTAGE_LEVEL, LV_VOLTAGE_LEVEL from TRANSFORMER where :id=1 AND TRANSFORMER_NAME IS NOT NULL and STATIONTYPE IS NOT NULL
and HV_STATION_ID IS NOT NULL and CIRCUIT_NUMBER IS NOT NULL and TYPE_GT_ICT IS NOT NULL and LOCATION_ID IS NOT NULL AND HV_VOLTAGE_LEVEL IS NOT NULL";
// Assign id parameter
OracleParameter id = new OracleParameter("id", 1);
cmd.Parameters.Add(id);
//Execute the command and use DataReader to display the data
OracleDataReader reader = cmd.ExecuteReader();
List <TransformerForeign> transformersForeign = new List <TransformerForeign>();
while (reader.Read())
{
TransformerForeign transForeign = new TransformerForeign();
transForeign.WebUatId = reader.GetInt32(0);
transForeign.Name = reader.GetString(1);
transForeign.HVStationWebUatId = reader.GetInt32(2);
transForeign.TransformerNumber = reader.GetInt32(3);
transForeign.TransTypeWebUatId = reader.GetInt32(4);
transForeign.MVACapacity = reader.GetDecimal(5);
transForeign.CommDate = reader.GetDateTime(6);
transForeign.DecommDate = reader.GetDateTime(7);
transForeign.CodDate = reader.GetDateTime(8);
transForeign.StateWebUatId = reader.GetInt32(9);
transForeign.StationType = reader.GetString(10);
transForeign.HighVoltLevelWebUatId = reader.GetInt32(11);
// checking for null values due to vendor non compliance
if (!reader.IsDBNull(12))
{
transForeign.LowVoltLevelWebUatId = reader.GetInt32(12);
}
else
{
transForeign.LowVoltLevelWebUatId = -1;
}
transformersForeign.Add(transForeign);
}
reader.Dispose();
return(transformersForeign);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
}
