private int InsertSurfaceCardHeader(SqlConnection sqlConn, SqlTransaction sqlTrans, int dynoCardId, DynoCard dynoCard)
{
string cmdText = string.Empty;
SqlCommand sqlCmd = null;
// Insert surface card header for the dyno card
cmdText = "INSERT INTO ACTIVE.CARD_HEADER " +
"(DC_ID, " +
"CH_CARD_TYPE, " +
"CH_EPOC_DATE, " +
"CH_NUMBER_OF_POINTS, " +
"CH_UPDATE_DATE, " +
"CH_UPDATE_BY, " +
"CH_STROKE_LENGTH, " +
"CH_STROKE_PERIOD) " +
"OUTPUT INSERTED.CH_ID " +
"VALUES" +
"(@DcId, " +
"'S', " +
"@EpocDate, " +
"@NumPoints, " +
"GETUTCDATE(), " +
"'SYSTEM', " +
"@ScaledMaxLoad, " +
"@ScaledMinLoad)";
sqlCmd = new SqlCommand(cmdText, sqlConn, sqlTrans);
sqlCmd.Parameters.AddWithValue("@DcId", dynoCardId);
sqlCmd.Parameters.AddWithValue("@EpocDate", TimeHelper.ConvertToEpoch(dynoCard.surfaceCard.Timestamp));
sqlCmd.Parameters.AddWithValue("@NumPoints", dynoCard.surfaceCard.NumPoints);
sqlCmd.Parameters.AddWithValue("@ScaledMaxLoad", dynoCard.surfaceCard.ScaledMaxLoad);
sqlCmd.Parameters.AddWithValue("@ScaledMinLoad", dynoCard.surfaceCard.ScaledMinLoad);
return((int)sqlCmd.ExecuteScalar());
}
private int InsertPumpCardHeader(SqlConnection sqlConn, SqlTransaction sqlTrans, int dynoCardId, DynoCard dynoCard)
{
try
{
SqlCommand sqlCmd = null;
string cmdText = string.Empty;
// Insert surface card header for the dyno card
cmdText = "INSERT INTO ACTIVE.CARD_HEADER " +
"(DC_ID, " +
"CH_CARD_TYPE, " +
"CH_EPOC_DATE, " +
"CH_NUMBER_OF_POINTS, " +
"CH_UPDATE_DATE, " +
"CH_UPDATE_BY, " +
"CH_GROSS_STROKE, " +
"CH_NET_STROKE, " +
"CH_PUMP_FILLAGE, " +
"CH_FLUID_LOAD) " +
"OUTPUT INSERTED.CH_ID " +
"VALUES" +
"(@DcId, " +
"'P', " +
"@EpocDate, " +
"@NumPoints, " +
"GETUTCDATE(), " +
"'SYSTEM', " +
"@GrossStroke, " +
"@NetStroke, " +
"@PumpFillage, " +
"@FluidLoad)";
sqlCmd = new SqlCommand(cmdText, sqlConn, sqlTrans);
sqlCmd.Parameters.AddWithValue("@DcId", dynoCardId);
sqlCmd.Parameters.AddWithValue("@EpocDate", TimeHelper.ConvertToEpoch(dynoCard.pumpCard.Timestamp));
sqlCmd.Parameters.AddWithValue("@NumPoints", dynoCard.pumpCard.NumPoints);
sqlCmd.Parameters.AddWithValue("@GrossStroke", dynoCard.pumpCard.GrossStroke);
sqlCmd.Parameters.AddWithValue("@NetStroke", dynoCard.pumpCard.NetStroke);
sqlCmd.Parameters.AddWithValue("@PumpFillage", dynoCard.pumpCard.PumpFillage);
sqlCmd.Parameters.AddWithValue("@FluidLoad", dynoCard.pumpCard.FluidLoad);
return((int)sqlCmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
}
private int InsertEvent(SqlConnection sqlConn, SqlTransaction sqlTrans, DynoCardAnomalyEvent dcae)
{
string cmdText = string.Empty;
SqlCommand sqlCmd = null;
cmdText = "DECLARE @EventId AS INT " +
"SELECT @EventId = EV_ID FROM ACTIVE.EVENT WITH(UPDLOCK, HOLDLOCK) WHERE EV_ANOMALY_ID = @AnomalyId; " +
"IF ISNULL(@EventId, 0) = 0 " +
" BEGIN " +
"INSERT INTO ACTIVE.EVENT( " +
"PU_ID, " +
"EV_EPOC_DATE, " +
"EV_ANOMALY_ID, " +
"EV_UPDATE_DATE, " +
"EV_UPDATE_BY) " +
"OUTPUT INSERTED.EV_ID " +
"VALUES( " +
"@PumpId, " +
"@EpochDate, " +
"@AnomalyId, " +
"GETUTCDATE(), " +
"'SYSTEM') " +
" END " +
"ELSE " +
" BEGIN " +
" SELECT @EventId " +
" END";
sqlCmd = new SqlCommand(cmdText, sqlConn, sqlTrans);
sqlCmd.Parameters.AddWithValue("@PumpId", dcae.PumpId);
sqlCmd.Parameters.AddWithValue("@EpochDate", TimeHelper.ConvertToEpoch(dcae.Timestamp));
sqlCmd.Parameters.AddWithValue("@AnomalyId", dcae.AnomalyId.ToString());
var eventId = sqlCmd.ExecuteScalar();
return((int)eventId);
}
