/// <summary>
/// Processes the alarms for the unit specified
/// </summary>
/// <param name="alarmsToInsert">Alarms to insert in ALARMS table</param>
/// <param name="alarmsToDelete">Alarms to move from the ALARMS table to ALARMS_HIS table</param>
/// <param name="uniid">ID of the unit</param>
/// <returns>true if succesful, false if some error (no operation is done if false is returned)</returns>
public bool ProcessAlarms(ArrayList alarmsToInsert, ArrayList alarmsToDelete, int uniid, DateTime date)
{
// CFE --- Hago una llamada a generica para verificar el select correcto.
//Hashtable tableSchema = CFETest_GetAlarmsFromUnit( uniid );
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = null;
try
{
con = d.GetNewConnection();
con.Open();
InsertAlarms(alarmsToInsert, uniid, con, date);
DeleteAlarms(alarmsToDelete, uniid, con, date);
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
try { con.Close(); con.Dispose(); }
catch (Exception) { }
}
}
public int GetIPFromUnit(int idUnit, ref string szIP)
{
int nRes = -1;
szIP = "";
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = d.GetNewConnection();
con.Open();
string ssql = "SELECT UNI_IP FROM UNITS "
+ "WHERE UNI_ID = @UNITS.UNI_ID@";
DataTable dtUnit = d.FillDataTable(ssql, "UNITS", idUnit);
foreach (DataRow dr in dtUnit.Rows)
{
szIP = Convert.ToString(dr["UNI_IP"]);
nRes++;
}
con.Close();
return(nRes);
}
public void UpdateStatus(int id, int status)
{
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = d.GetNewConnection();
con.Open();
string ssql = "UPDATE UNITS SET UNI_DSTA_ID = @UNITS.UNI_DSTA_ID@ "
+ "WHERE UNI_ID = @UNITS.UNI_ID@";
d.ExecuteNonQuery(ssql, con, status, id);
con.Close();
}
/// <summary>
/// Get active alarms from Unit Id
/// </summary>
/// <param name="iUnitId">Unit Id</param>
/// <returns>Hashtable with alarmas</returns>
public bool GetAlarmsFromUnit(int iUnitId, out Hashtable htAlarms, out DateTime dtMaxDate)
{
bool bRet = true;
htAlarms = null;
dtMaxDate = DateTime.MinValue;
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = null;
OracleDataReader dr = null;
try
{
htAlarms = new Hashtable();
con = d.GetNewConnection();
con.Open();
OracleCommand cmd = new OracleCommand("select ALA_ID,ALA_DALA_ID,TO_CHAR(ALA_INIDATE,'hh24missddmmyy') ALA_INIDATE from ALARMS where ALA_UNI_ID = " +
iUnitId + " order by ALA_DALA_ID ASC");
cmd.Connection = (OracleConnection)con;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string sALA_ID = dr["ALA_ID"].ToString();
string sALA_DALA_ID = dr["ALA_DALA_ID"].ToString();
DateTime dtALA_INIDATE = OPS.Comm.Dtx.StringToDtx(dr["ALA_INIDATE"].ToString());
htAlarms.Add(sALA_ID, sALA_DALA_ID);
if (dtALA_INIDATE > dtMaxDate)
{
dtMaxDate = dtALA_INIDATE;
}
}
}
catch (Exception)
{
bRet = false;
}
finally
{
try
{
con.Close();
con.Dispose();
dr.Close();
dr.Dispose();
}
catch (Exception)
{
}
}
return(bRet);
}
/// <summary>
/// Saves subviews' permissions for a role (ROL_PERMISSIONS table)
/// </summary>
/// <param name="dt">DataTable containint the users to save</param>
public void SaveViewsAssignedToRoles(DataTable dt)
{
Database d = DatabaseFactory.GetDatabase();
IDbDataAdapter da = d.GetDataAdapter();
IDbCommand cmd = d.PrepareCommand("UPDATE ROL_PERMISSIONS SET RPER_INSALLOWED = @ROL_PERMISSIONS.RPER_INSALLOWED@, RPER_UPDALLOWED = @ROL_PERMISSIONS.RPER_UPDALLOWED@, RPER_DELALLOWED= @ROL_PERMISSIONS.RPER_DELALLOWED@, RPER_EXEALLOWED=@ROL_PERMISSIONS.RPER_EXEALLOWED@ WHERE RPER_ROL_ID = @ROL_PERMISSIONS.RPER_ROL_ID@ AND RPER_VELE_VIE_ID = @ROL_PERMISSIONS.RPER_VELE_VIE_ID@ AND RPER_VELE_ELEMENTNUMBER = @ROL_PERMISSIONS.RPER_VELE_ELEMENTNUMBER@", false);
IDbConnection con = d.GetNewConnection();
con.Open();
cmd.Connection = con;
da.UpdateCommand = cmd;
d.UpdateDataSet(da, dt);
con.Close();
dt.AcceptChanges();
}
public int InsertAuthTrans(string strTransId, DateTime dtDate, string sCCNumber, string sCCName, DateTime dtExprtnDate,
string strCCCodServ, string strCCDiscData, int nQuantity, int nUniId, string strOperInfo, out int nTransId)
{
Database d = DatabaseFactory.GetDatabase();
IDbTransaction tran = null;
IDbConnection con = null;
int res = -1;
nTransId = -1;
try
{
con = d.GetNewConnection();
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
res = InsertAuthTrans(tran, strTransId, dtDate, sCCNumber, sCCName, dtExprtnDate,
strCCCodServ, strCCDiscData, nQuantity, nUniId, strOperInfo, out nTransId);
if (res == 1)
{
tran.Commit();
}
else
{
tran.Rollback();
}
}
catch
{
tran.Rollback();
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (con != null)
{
con.Close();
con.Dispose();
}
}
return(res);
}
public long GetMinutesFromLastUpdateUnit(int idUnit)
{
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = d.GetNewConnection();
con.Open();
string ssql = "SELECT TRUNC(NVL(SYSDATE - UNI_DATE, -1)*24*60) FROM UNITS "
+ "WHERE UNI_ID = @UNITS.UNI_ID@";
long nRes = (long)d.ExecuteScalar(ssql, idUnit);
con.Close();
return(nRes);
}
public int UpdateBeforeCommitTrans(int nTransId, int nOpeId)
{
Database d = DatabaseFactory.GetDatabase();
IDbTransaction tran = null;
IDbConnection con = null;
int res = -1;
nTransId = -1;
try
{
con = d.GetNewConnection();
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
res = UpdateBeforeCommitTrans(tran, nTransId, nOpeId);
if (res == 1)
{
tran.Commit();
}
else
{
tran.Rollback();
}
}
catch
{
tran.Rollback();
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (con != null)
{
con.Close();
con.Dispose();
}
}
return(res);
}
/// <summary>
/// Updates the USR_PERMISSIONS table
/// </summary>
/// <param name="dt">DataTable to udpate</param>
public void SaveViewsAssignedToUsers(DataTable dt)
{
Database d = DatabaseFactory.GetDatabase();
IDbDataAdapter da = d.GetDataAdapter();
da.UpdateCommand = d.PrepareCommand("UPDATE USR_PERMISSIONS SET UPER_INSALLOWED = @USR_PERMISSIONS.UPER_INSALLOWED@, UPER_UPDALLOWED = @USR_PERMISSIONS.UPER_UPDALLOWED@, UPER_DELALLOWED= @USR_PERMISSIONS.UPER_DELALLOWED@, UPER_EXEALLOWED=@USR_PERMISSIONS.UPER_EXEALLOWED@ WHERE UPER_USR_ID = @USR_PERMISSIONS.UPER_USR_ID@ AND UPER_VELE_VIE_ID = @USR_PERMISSIONS.UPER_VELE_VIE_ID@ AND UPER_VELE_ELEMENTNUMBER = @USR_PERMISSIONS.UPER_VELE_ELEMENTNUMBER@", false);
da.InsertCommand = d.PrepareCommand("INSERT INTO USR_PERMISSIONS (UPER_USR_ID, UPER_VELE_VIE_ID, UPER_VELE_ELEMENTNUMBER, UPER_INSALLOWED,UPER_UPDALLOWED, UPER_DELALLOWED, UPER_EXEALLOWED) VALUES (@USR_PERMISSIONS.UPER_USR_ID@, @USR_PERMISSIONS.UPER_VELE_VIE_ID@, @USR_PERMISSIONS.UPER_VELE_ELEMENTNUMBER@, @USR_PERMISSIONS.UPER_INSALLOWED@,@USR_PERMISSIONS.UPER_UPDALLOWED@, @USR_PERMISSIONS.UPER_DELALLOWED@, @USR_PERMISSIONS.UPER_EXEALLOWED@)", false);
da.DeleteCommand = d.PrepareCommand("DELETE FROM USR_PERMISSIONS WHERE UPER_USR_ID = @USR_PERMISSIONS.UPER_USR_ID@ AND UPER_VELE_VIE_ID = @USR_PERMISSIONS.UPER_VELE_VIE_ID@ AND UPER_VELE_ELEMENTNUMBER = @USR_PERMISSIONS.UPER_VELE_ELEMENTNUMBER@", false);
IDbConnection con = d.GetNewConnection();
con.Open();
da.UpdateCommand.Connection = con;
da.InsertCommand.Connection = con;
da.DeleteCommand.Connection = con;
d.UpdateDataSet(da, dt);
con.Close();
dt.AcceptChanges();
}
public override void SaveData(DataTable dt)
{
// Loads XML Schema from database
LoadSchema();
Database d = DatabaseFactory.GetDatabase();
IDbConnection con = d.GetNewConnection();
con.Open();
IDbDataAdapter da = d.GetDataAdapter();
da.InsertCommand = d.PrepareCommand(CreateInsertCommand(), false);
da.UpdateCommand = d.PrepareCommand(CreateUpdateCommand(), false);
da.DeleteCommand = d.PrepareCommand(CreateDeleteCommand(), false);
da.InsertCommand.Connection = con;
da.UpdateCommand.Connection = con;
da.DeleteCommand.Connection = con;
d.UpdateDataSet(da, dt);
dt.AcceptChanges();
con.Close();
}
// Updates the to Unit IP
// TABLE UNITS
// FIELD UNI_IP
public int UpdateIP(int id, string szIPD)
{
Database d = null;
ILogger localLogger = null;
IDbTransaction tran = null;
IDbConnection con = null;
int res = -1;
try
{
//UPDATE UNITS SET UNI_IP ='192.168.1.100' WHERE UNI_ID = 10001
d = DatabaseFactory.GetDatabase();
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: Getting Connection...", LoggerSeverities.Debug);
}
con = d.GetNewConnection();
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: Opening...", LoggerSeverities.Debug);
}
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
string ssql = "UPDATE UNITS SET UNI_IP = @UNITS.UNI_IP@, UNI_DATE = @UNITS.UNI_DATE@ "
+ "WHERE UNI_ID = @UNITS.UNI_ID@";
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: Executing...", LoggerSeverities.Debug);
}
AppSettingsReader appSettings = new System.Configuration.AppSettingsReader();
double nDifHour = 0;
try
{
nDifHour = (double)appSettings.GetValue("HOUR_DIFFERENCE", typeof(double));
}
catch
{
nDifHour = 0;
}
res = d.ExecuteNonQuery(ssql, con, tran, szIPD, DateTime.Now.AddHours(nDifHour).ToString(), id);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
catch (Exception e)
{
// OOOOppps.... some error... do a rollback
tran.Rollback();
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpUnitsDB]: Closing...", LoggerSeverities.Debug);
}
con.Close();
}
catch (Exception) { }
//m.ReleaseMutex();
}
return(res);
}
/// <summary>
/// Insert a Command in the DataBase
/// </summary>
/// <param name="idCmdDef"></param>
/// <param name="idUnit"></param>
/// <returns></returns>
public int InsertCommand(int idCmdDef, int idUnit, int idCC, string szIP, string szCommand)
{
Database d = null;
ILogger localLogger = null;
IDbTransaction tran = null;
IDbConnection con = null;
int res = -1;
try
{
d = DatabaseFactory.GetDatabase();
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Getting Connection...", LoggerSeverities.Debug);
}
con = d.GetNewConnection();
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: IOpening...", LoggerSeverities.Debug);
}
con.Open();
int cmdID = Convert.ToInt32(d.ExecuteScalar("SELECT NVL(MAX(CMD_ID),0)+1 FROM COMMANDS", new object[] {}));
tran = con.BeginTransaction(IsolationLevel.Serializable);
string ssql = "INSERT INTO COMMANDS(CMD_ID,CMD_UNI_ID,CMD_PARAM,CMD_IP,CMD_STATUS,CMD_RESULT,CMD_DATE_INSERT,CMD_COMMAND_ID) VALUES ";
ssql = ssql + "(@COMMANDS.CMD_ID@,@COMMANDS.CMD_UNI_ID@,@COMMANDS.CMD_PARAM@,@COMMANDS.CMD_IP@,1,'INSERTED',SYSDATE,@COMMANDS.CMD_COMMAND_ID@)";
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Executing...", LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran, cmdID, idUnit, szCommand, szIP, idCmdDef);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
catch (Exception e)
{
// OOOOppps.... some error... do a rollback
tran.Rollback();
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Closing...", LoggerSeverities.Debug);
}
con.Close();
}
catch (Exception)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: finally", LoggerSeverities.Debug);
}
}
}
return(res);
}
/// <summary>
/// Get the Status from table Commands
/// </summary>
/// <param name="idUnit"></param>
/// <returns></returns>
public int GetStatusCmdFromUnit(int idUnit, int idCommandDef, ref string szResult, ref int nStatus, ref DateTime dtTime, ref int nCmdID)
{
int nRes = -1;
IDbConnection con = null;
ILogger localLogger = null;
try
{
localLogger = DatabaseFactory.Logger;
szResult = "";
nStatus = -1;
dtTime = DateTime.MinValue;
// Getting Databasse, open connection
Database d = DatabaseFactory.GetDatabase();
con = d.GetNewConnection();
con.Open();
string ssql = "SELECT CMD_RESULT,CMD_STATUS,CMD_DATE_RESULT,CMD_ID FROM COMMANDS "
+ "WHERE CMD_ID = "
+ "(SELECT NVL(MAX(CMD_ID),0) FROM COMMANDS WHERE CMD_COMMAND_ID = @COMMANDS.CMD_COMMAND_ID@)"
+ "AND CMD_UNI_ID = @COMMANDS.CMD_UNI_ID@";
DataTable dtUnit = d.FillDataTable(ssql, "COMMANDS", idCommandDef, idUnit);
foreach (DataRow dr in dtUnit.Rows)
{
if (!(dr["CMD_RESULT"] == DBNull.Value))
{
szResult = Convert.ToString(dr["CMD_RESULT"]);
}
if (!(dr["CMD_STATUS"] == DBNull.Value))
{
nStatus = Convert.ToInt32(dr["CMD_STATUS"]);
}
if (!(dr["CMD_DATE_RESULT"] == DBNull.Value))
{
dtTime = Convert.ToDateTime(dr["CMD_DATE_RESULT"]);
}
if (!(dr["CMD_ID"] == DBNull.Value))
{
nCmdID = Convert.ToInt32(dr["CMD_ID"]);
}
nRes++;
}
con.Close();
}
catch (Exception e)
{
// Some error...
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Closing...", LoggerSeverities.Debug);
}
con.Close();
throw e; // Propagate the error back!
}
finally
{
// Close connection...
try
{
con.Close();
}
catch (Exception)
{
}
}
return(nRes);
}
public int InsertCoupon(uint uiId, string strCode, int iNumberOfMinutesGiven, int iNumberOfCentsGiven, uint uiIdBookofCoupons)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbConnection con = null;
int res = -1;
try
{
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Opening...", LoggerSeverities.Debug);
}
string ssql = "";
ssql = "INSERT INTO MONEYOFF_COUPON (COUP_ID, COUP_CODE, COUP_BCOUP_ID,COUP_DISCOUNT_TIME,COUP_DISCOUNT_MONEY) " +
"VALUES (@MONEYOFF_COUPON.COUP_ID@,@MONEYOFF_COUPON.COUP_CODE@,@MONEYOFF_COUPON.COUP_BCOUP_ID@, @MONEYOFF_COUPON.COUP_DISCOUNT_TIME@, @MONEYOFF_COUPON.COUP_DISCOUNT_MONEY@ )";
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpMoneyOffCouponsDB ]" + ssql, LoggerSeverities.Debug);
}
con.Open();
res = d.ExecuteNonQuery(ssql, con, uiId, strCode, uiIdBookofCoupons,
(iNumberOfMinutesGiven == -1 ? DBNull.Value : (object)iNumberOfMinutesGiven),
(iNumberOfCentsGiven == -1 ? DBNull.Value : (object)iNumberOfCentsGiven));
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Coupon inserted OK", LoggerSeverities.Debug);
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Error inserting coupon" + e.Message, LoggerSeverities.Debug);
}
throw e; // Propagate the error back!
}
finally
{
con.Close();
}
return(res);
}
public int UpdateError(int nTransId, int iErrorCode, string strErrorMsg, int iRetries)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbTransaction tran = null; // Transacition
IDbConnection con = null; // Connection
int res = -1;
try
{
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Opening...", LoggerSeverities.Debug);
}
// Open connection
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
if (tran == null)
{
return(-3);
}
int iNumRegs = Convert.ToInt32(d.ExecuteScalar(string.Format("select count(*) from CREDIT_CARDS_TRANSACTIONS where CCT_ID={0}", nTransId), con, tran));
if (iNumRegs == 1)
{
AppSettingsReader appSettings = new System.Configuration.AppSettingsReader();
double nDifHour = 0;
try
{
nDifHour = (double)appSettings.GetValue("HOUR_DIFFERENCE", typeof(double));
}
catch
{
nDifHour = 0;
}
string ssql = string.Format("update CREDIT_CARDS_TRANSACTIONS set " +
((iErrorCode == -1) ? "" : "CCT_ERRORCODE=" + iErrorCode.ToString() + ",") +
((strErrorMsg == "") ? "" : "CCT_ERRORMSG='" + strErrorMsg + "',") +
"CCT_CURRRETRIES={0}," +
"CCT_STATE_DATE=to_date('{1}','hh24missddmmyy') " +
"where CCT_ID={2}",
iRetries, OPS.Comm.Dtx.DtxToString(DateTime.Now.AddHours(nDifHour)), nTransId);
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]" + ssql, LoggerSeverities.Debug);
localLogger.AddLog("[CmpCreditCardsTransactionsDB ] TransId: " + nTransId.ToString(), LoggerSeverities.Debug);
}
//DateTime dtNow = new DateTime();
res = d.ExecuteNonQuery(ssql, con, tran);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardDB ]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardDB ]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
else
{
res = 0;
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
if (tran != null)
{
tran.Rollback();
}
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Closing...", LoggerSeverities.Debug);
}
if (con != null)
{
con.Close();
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
}
}
return(res);
}
// public override void GetForeignData(DataSet ds, string sTable)
// {
// DataTable dtCmpUnitsDB = new CmpUnitsDB().GetData();
// DataTable dtCmpCodesDB = new CmpCodesDB().GetYesNoData();
//
// ds.Tables.Add (dtCmpUnitsDB);
// ds.Tables.Add (dtCmpCodesDB);
//
// DataTable parent = ds.Tables[sTable];
// ds.Relations.Add ((dtCmpUnitsDB.PrimaryKey)[0],parent.Columns["MUNI_UNI_ID"]);
//
// }
/// <summary>
/// Inserts a new register in the UNITS_MEASURES table
/// </summary>
/// <param name="dfinid">FIN_DFIN_ID value. Cannot be NULL</param>
public void InsertMeasures(int uniid, DateTime date,
float ms1, float ms2, float ms3, float ms4, float ms5)
{
IDbTransaction tran = null;
IDbConnection con = null;
int res = 0;
ILogger logger = null;
try
{
logger = DatabaseFactory.Logger;
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB:InsertMeasures ", LoggerSeverities.Debug);
}
/*
* if (ExistsFine(dfinid, number, vehicleid, model, manufacturer, colour, grpid, strid, strnumber,
* date, comments, usrid, uniid, dpayid))
* return;
*/
Database d = DatabaseFactory.GetDatabase();
con = d.GetNewConnection();
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB::InsertMeasures]: IOpening...", LoggerSeverities.Debug);
}
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB:Deleting previous ", LoggerSeverities.Debug);
}
// Borro la anterior si existe
d.ExecuteNonQuery("DELETE FROM UNITS_MEASURES WHERE MUNI_UNI_ID = " + uniid.ToString());
// Obtengo el nuevo índice de inserción
int id = Convert.ToInt32(d.ExecuteScalar("SELECT NVL(MAX(MUNI_ID),0) FROM UNITS_MEASURES", new object[] {}));
id++;
// Sentencia de inserción
string ssql = "INSERT INTO UNITS_MEASURES (MUNI_ID,MUNI_DATE,MUNI_UNI_ID,MUNI_VALUE1,"
+ "MUNI_VALUE2,MUNI_VALUE3,MUNI_VALUE4,MUNI_VALUE5,MUNI_VALUE6,MUNI_VALUE7,"
+ "MUNI_VALUE8,MUNI_VALUE9,MUNI_VALUE10) VALUES ("
+ "@UNITS_MEASURES.MUNI_ID@,@UNITS_MEASURES.MUNI_DATE@,@UNITS_MEASURES.MUNI_UNI_ID@,@UNITS_MEASURES.MUNI_VALUE1@,"
+ "@UNITS_MEASURES.MUNI_VALUE2@,@UNITS_MEASURES.MUNI_VALUE3@,@UNITS_MEASURES.MUNI_VALUE4@,@UNITS_MEASURES.MUNI_VALUE5@,@UNITS_MEASURES.MUNI_VALUE6@,"
+ "@UNITS_MEASURES.MUNI_VALUE7@,@UNITS_MEASURES.MUNI_VALUE8@,@UNITS_MEASURES.MUNI_VALUE9@,@UNITS_MEASURES.MUNI_VALUE10@)";
if (logger != null)
{
logger.AddLog(ssql, LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran,
//int res = d.ExecuteNonQuery(ssql,
id, date, (uniid == -1 ? DBNull.Value : (object)uniid),
(object)ms1,
(object)ms2,
(object)ms3,
(object)ms4,
(object)ms5,
DBNull.Value,
DBNull.Value,
DBNull.Value,
DBNull.Value,
DBNull.Value);
if (res == 1)
{
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB::InsertMeasures]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB::InsertMeasures]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
catch (Exception e)
{
// OOOOppps.... some error... do a rollback
tran.Rollback();
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (logger != null)
{
logger.AddLog("[CmpUnitsMeasuresDB]: Closing...", LoggerSeverities.Debug);
}
con.Close();
}
catch (Exception) { }
//m.ReleaseMutex();
}
//return res;
}
/// <summary>
///
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public int Insert(int iUniID, DateTime dtDate, int iValue, string sFineNumber, long lFineDef)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbTransaction tran = null; // Transacition
IDbConnection con = null; // Connection
int res = -1;
try
{
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: Opening...", LoggerSeverities.Debug);
}
// Open connection
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
if (tran == null)
{
return(-3);
}
int iNumRegs = Convert.ToInt32(d.ExecuteScalar(string.Format("select count(*) from BILLREADER_REFUNDSFINE where RBILLF_UNI_ID={0} and RBILLF_DATE=to_date('{1}','hh24missddmmyy')", iUniID, OPS.Comm.Dtx.DtxToString(dtDate)), con, tran));
if (iNumRegs == 0)
{
string ssql = " INSERT INTO BILLREADER_REFUNDSFINE (RBILLF_UNI_ID, RBILLF_DATE, RBILLF_VALUE, RBILLF_FIN_ID, RBILLF_DFIN_ID) " +
" VALUES (@BILLREADER_REFUNDSFINE.RBILLF_UNI_ID@,@BILLREADER_REFUNDSFINE.RBILLF_DATE@,@BILLREADER_REFUNDSFINE.RBILLF_VALUE@,@BILLREADER_REFUNDSFINE.RBILLF_FIN_ID@,@BILLREADER_REFUNDSFINE.RBILLF_DFIN_ID@) ";
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]" + ssql, LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran, iUniID, dtDate, iValue,
(sFineNumber == null ? DBNull.Value : (object)Convert.ToInt64(sFineNumber)),
(lFineDef == -1 ? DBNull.Value : (object)lFineDef));
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
else
{
res = 1;
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
if (tran != null)
{
tran.Rollback();
}
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: Closing...", LoggerSeverities.Debug);
}
if (con != null)
{
con.Close();
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpBillReaderRefundsFineDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
}
}
return(res);
}
public int DeleteCoupon(uint uiId)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbConnection con = null;
int res = -1;
try
{
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Opening...", LoggerSeverities.Debug);
}
string ssql = "";
ssql = "DELETE MONEYOFF_COUPON WHERE MONEYOFF_COUPON.COUP_ID = @MONEYOFF_COUPON.COUP_ID@";
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpMoneyOffCouponsDB ]" + ssql, LoggerSeverities.Debug);
}
con.Open();
res = d.ExecuteNonQuery(ssql, con, uiId);
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Coupon deleted OK", LoggerSeverities.Debug);
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpMoneyOffCouponsDB ]: Error deleting coupon" + e.Message, LoggerSeverities.Debug);
}
}
finally
{
con.Close();
}
return(res);
}
public int UpdateStatus(int idCmd, int nStatus)
{
Database d = null;
ILogger localLogger = null;
IDbTransaction tran = null;
IDbConnection con = null;
int res = -1;
try
{
//UPDATE UNITS SET UNI_IP ='192.168.1.100' WHERE UNI_ID = 10001
d = DatabaseFactory.GetDatabase();
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: Getting Connection...", LoggerSeverities.Debug);
}
con = d.GetNewConnection();
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: Opening...", LoggerSeverities.Debug);
}
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
/*
* //
* szSQL.Format( _T("UPDATE COMMANDS SET CMD_STATUS = %d ,CMD_RESULT='%s',CMD_DATE_RESULT=TO_DATE('%s','DD/MM/YY HH24:MI:SS') ")
* _T(" WHERE CMD_ID=%d"),C_FINALIZADOOK, pCommandDB->m_szResult,szDateWork,pCommandDB->m_nCommandID );
*
*/
string ssql = "UPDATE COMMANDS SET CMD_STATUS = @COMMANDS.CMD_STATUS@ "
+ " WHERE CMD_ID = @COMMANDS.CMD_ID@";
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: Executing...", LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran, nStatus, idCmd);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
catch (Exception e)
{
// OOOOppps.... some error... do a rollback
tran.Rollback();
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpCommandsDB]: Closing...", LoggerSeverities.Debug);
}
con.Close();
}
catch (Exception) { }
//m.ReleaseMutex();
}
return(res);
}
/// <summary>
///
/// </summary>
/// <param name="strUnitId">Unit identificator (must exists in groups table)</param>
/// <param name="dtDateInsert">Date when the statistic was generates (not date when the statistics
/// was sent</param>
/// <param name="strMessage">Message to which the statistic is referring to</param>
/// <param name="lValues">Values for the integer statistics (must be only 5)</param>
/// <param name="fValues">Values for the real statistics (must be only 2)</param>
/// <returns></returns>
public int Insert(int iUnitId, DateTime dtDateInsert, string strMessage, long[] lValues,
float[] fValues)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbTransaction tran = null; // Transacition
IDbConnection con = null; // Connection
int res = -1;
try
{
// Check the parameters: TODO: Check if the date is valid
if (strMessage.Length == 0)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Some input parameter is not valid", LoggerSeverities.Debug);
}
return(-1);
}
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Opening...", LoggerSeverities.Debug);
}
// Open connection
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
if (tran == null)
{
return(-3);
}
long lMax = Convert.ToInt64(d.ExecuteScalar("SELECT NVL(MAX(PMS_ID),0) FROM PDM_MESSAGES_STAT"));
string ssql = "INSERT INTO PDM_MESSAGES_STAT (PMS_ID, PMS_DATE, PMS_UNIT, PMS_MESSAGE, " +
"PMS_IVALUE1,PMS_IVALUE2, PMS_IVALUE3, PMS_IVALUE4, PMS_IVALUE5, PMS_RVALUE1, " +
"PMS_RVALUE2) VALUES (@PDM_MESSAGES_STAT.PMS_DATE@,@PDM_MESSAGES_STAT.PMS_UNIT@, " +
"@PDM_MESSAGES_STAT.PMS_MESSAGE@,@PDM_MESSAGES_STAT.PMS_IVALUE1@, " +
"@PDM_MESSAGES_STAT.PMS_IVALUE2@ ,@PDM_MESSAGES_STAT.PMS_IVALUE3@, " +
"@PDM_MESSAGES_STAT.PMS_IVALUE4@ ,@PDM_MESSAGES_STAT.PMS_IVALUE5@, " +
"@PDM_MESSAGES_STAT.PMS_RVALUE1@ ,@PDM_MESSAGES_STAT.PMS_RVALUE2@) ";
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpPDMMessagesStatDB]" + ssql, LoggerSeverities.Debug);
localLogger.AddLog("[CmpPDMMessagesStatDB] Unit ID " + iUnitId.ToString(), LoggerSeverities.Debug);
localLogger.AddLog("[CmpPDMMessagesStatDB] Message " + strMessage, LoggerSeverities.Debug);
localLogger.AddLog("[CmpPDMMessagesStatDB] Date " + dtDateInsert.ToString("F"), LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran, lMax, dtDateInsert, iUnitId, strMessage,
lValues[0], lValues[1], lValues[2], lValues[3], lValues[4],
fValues[0], fValues[1]);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: RollBack" + e.Message, LoggerSeverities.Debug);
}
if (tran != null)
{
tran.Rollback();
}
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: Closing...", LoggerSeverities.Debug);
}
if (con != null)
{
con.Close();
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpPDMMessagesStatDB]: RollBack" + e.Message, LoggerSeverities.Debug);
}
}
}
return(res);
}
//static System.Threading.Mutex m = new System.Threading.Mutex();
#region Specific Methods of CmpOperationsDB
/// <summary>
/// Inserts a new register in the OPERATIONS table.
/// Returns 0 = 0K, -1 = ERR, 1 = OPERACION YA EXISTENTE
/// </summary>
/// <param name="dopeid">OPE_DOPE_ID value. Cannot be NULL</param>
/// <param name="opeopeid">OPE_OPE_ID value. -1 for NULL</param>
/// <param name="artid">OPE_ART_ID value. -1 for NULL</param>
/// <param name="grpid">OPE_GRP_ID value. Cannot be NULL</param>
/// <param name="uniid">OPE_UNI_ID value. Cannot be NULL</param>
/// <param name="dpayid">OPE_DPAY_ID value. -1 for NULL</param>
/// <param name="mov">OPE_MOVDATE value. DateTime.MinValue for NULL</param>
/// <param name="ini">OPE_INIDATE value. DateTime.MinValue for NULL</param>
/// <param name="end">OPE_ENDDATE value. DateTime.MinValue for NULL</param>
/// <param name="duration">OPE_DURATION value. -1 for NULL</param>
/// <param name="quantity">OPE_VALUE value. -1 for NULL</param>
/// <param name="vehicleid">OPE_VEHICLEID value. May be NULL</param>
///
public int InsertOperation(int dopeid, int opeopeid, int artid, int grpid, int uniid, int dpayid, DateTime mov,
DateTime ini, DateTime end, int duration, double quantity, string vehicleid, int dartid, int mobileUserId,
int postpay, double dChipCardCredit, ulong ulChipCardId, double lFineNumber, long lFineType, int iRealDuration,
int quantityReturned, int iOpOnLine, int iTicketNumber, ref int nNewOperationsDB, out IDbTransaction tran)
{
tran = null;
int nRdo = 0;
if (ExistsOperation(dopeid, opeopeid, artid, grpid, uniid, dpayid, mov,
ini, end, duration, quantity, vehicleid))
{
return(1);
}
Database d = DatabaseFactory.GetDatabase();
//TimeSpan duration = end-ini;
tran = null;
IDbConnection con = null;
//m.WaitOne();
// Only one thread per time here....
try
{
con = d.GetNewConnection();
con.Open();
// Put the two operations inside a TRANSACTION because we want a block (updates to the
// table cannot be allowed when we are reading the PK and inserting the data).
tran = con.BeginTransaction(IsolationLevel.Serializable);
nNewOperationsDB = Convert.ToInt32(d.ExecuteScalar("select SEQ_OPERATIONS.NEXTVAL FROM DUAL", con, tran));
string ssql = "INSERT INTO OPERATIONS "
+ "(OPE_ID, OPE_DOPE_ID, OPE_OPE_ID, OPE_ART_ID, "
+ "OPE_GRP_ID, OPE_UNI_ID, OPE_DPAY_ID, OPE_MOVDATE, "
+ "OPE_INIDATE, OPE_ENDDATE, OPE_DURATION, OPE_VALUE, "
+ "OPE_VEHICLEID,OPE_DART_ID, OPE_MOBI_USER_ID, OPE_POST_PAY, "
+ "OPE_CHIPCARD_ID, OPE_CHIPCARD_CREDIT, OPE_FIN_ID, OPE_FIN_DFIN_ID, "
+ "OPE_REALDURATION, OPE_VALUE_IN_RETURN, OPE_OP_ONLINE, OPE_TICKETNUM) VALUES "
+ "(@OPERATIONS.OPE_ID@, @OPERATIONS.OPE_DOPE_ID@, @OPERATIONS.OPE_OPE_ID@, @OPERATIONS.OPE_ART_ID@, "
+ "@OPERATIONS.OPE_GRP_ID@, @OPERATIONS.OPE_UNI_ID@, @OPERATIONS.OPE_DPAY_ID@, @OPERATIONS.OPE_MOVDATE@, "
+ "@OPERATIONS.OPE_INIDATE@, @OPERATIONS.OPE_ENDDATE@, @OPERATIONS.OPE_DURATION@, @OPERATIONS.OPE_VALUE@, "
+ "@OPERATIONS.OPE_VEHICLEID@,@OPERATIONS.OPE_DART_ID@,@OPERATIONS.OPE_MOBI_USER_ID@,@OPERATIONS.OPE_POST_PAY@, "
+ "@OPERATIONS.OPE_CHIPCARD_ID@,@OPERATIONS.OPE_CHIPCARD_CREDIT@,@OPERATIONS.OPE_FIN_ID@,@OPERATIONS.OPE_FIN_DFIN_ID@, "
+ "@OPERATIONS.OPE_REALDURATION@,@OPERATIONS.OPE_VALUE_IN_RETURN@,@OPERATIONS.OPE_OP_ONLINE@,@OPERATIONS.OPE_TICKETNUM@)";
d.ExecuteNonQuery(ssql, con, tran,
nNewOperationsDB,
dopeid,
(opeopeid == -1 ? DBNull.Value : (object)opeopeid),
(artid == -1 ? DBNull.Value : (object)artid),
grpid,
uniid,
(dpayid == -1 ? DBNull.Value : (object)dpayid),
(mov == DateTime.MinValue ? DBNull.Value : (object)mov),
(ini == DateTime.MinValue ? DBNull.Value : (object)ini),
(end == DateTime.MinValue ? DBNull.Value : (object)end),
(duration == -1 ? DBNull.Value : (object)duration),
(quantity == -1 ? DBNull.Value : (object)quantity),
(vehicleid == null ? DBNull.Value : (object)vehicleid),
(dartid == -1 ? DBNull.Value : (object)dartid),
(mobileUserId == -1 ? DBNull.Value : (object)mobileUserId),
(postpay == -1 ? DBNull.Value : (object)postpay),
(ulChipCardId == 0 ? DBNull.Value : (object)ulChipCardId),
(dChipCardCredit == -1 ? DBNull.Value : (object)dChipCardCredit),
(lFineNumber == -1 ? DBNull.Value : (object)lFineNumber),
(lFineType == -1 ? DBNull.Value : (object)lFineType),
(iRealDuration == -1 ? DBNull.Value : (object)iRealDuration),
(quantityReturned == -1 ? DBNull.Value : (object)quantityReturned),
(iOpOnLine == -1 ? DBNull.Value : (object)iOpOnLine),
(iTicketNumber == -1 ? DBNull.Value : (object)iTicketNumber));
nRdo = 0;
}
catch (Exception e)
{
nRdo = -1;
// OOOOppps.... some error... do a rollback
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
//m.ReleaseMutex();
}
return(nRdo);
}
/// <summary>
///
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public int Insert(int iUserID, int iUniID, DateTime dtDate)
{
Database d = null; // Database
ILogger localLogger = null; // Logger to trace
IDbTransaction tran = null; // Transacition
IDbConnection con = null; // Connection
int res = -1;
try
{
// Getting Database
d = DatabaseFactory.GetDatabase();
if (d == null)
{
return(-1);
}
// Getting Logger
localLogger = DatabaseFactory.Logger;
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: Getting Connection...", LoggerSeverities.Debug);
}
// Getting connection
con = d.GetNewConnection();
if (con == null)
{
return(-2);
}
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: Opening...", LoggerSeverities.Debug);
}
// Open connection
con.Open();
tran = con.BeginTransaction(IsolationLevel.Serializable);
if (tran == null)
{
return(-3);
}
int iNumRegs = Convert.ToInt32(d.ExecuteScalar(string.Format("select count(*) from clock_in where ci_usr_id={0} and ci_date=to_date('{1}','hh24missddmmyy')", iUserID, OPS.Comm.Dtx.DtxToString(dtDate)), con, tran));
if (iNumRegs == 0)
{
string ssql = " INSERT INTO CLOCK_IN (CI_USR_ID, CI_UNI_ID, CI_DATE) " +
" VALUES (@CLOCK_IN.CI_USR_ID@,@CLOCK_IN.CI_UNI_ID@,@CLOCK_IN.CI_DATE@) ";
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: Executing...", LoggerSeverities.Debug);
localLogger.AddLog("[CmpClockInDB ]" + ssql, LoggerSeverities.Debug);
}
res = d.ExecuteNonQuery(ssql, con, tran, iUserID, iUniID, dtDate);
if (res == 1)
{
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: Commit", LoggerSeverities.Debug);
}
tran.Commit();
}
else
{
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: RollBack", LoggerSeverities.Debug);
}
tran.Rollback();
}
}
else
{
res = 1;
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
if (tran != null)
{
tran.Rollback();
}
throw e; // Propagate the error back!
}
finally
{
// Close connection... and release the mutex!
try
{
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: Closing...", LoggerSeverities.Debug);
}
if (con != null)
{
con.Close();
}
}
catch (Exception e)
{
res = -1;
if (localLogger != null)
{
localLogger.AddLog("[CmpClockInDB ]: RollBack" + e.Message, LoggerSeverities.Debug);
}
}
}
return(res);
}