/// <summary>
/// Author: Charlene Remotigue
/// Date Created: 29/11/2011
/// (description) get manifest difference count
/// </summary>
/// <param name="ManifestType"></param>
/// <param name="CompareManifestType"></param>
/// <param name="BranchId"></param>
/// <param name="ManifesDate"></param>
/// <returns></returns>
public Int32 GetHotelManifestDiffCount(int ManifestType, int CompareManifestType, int BranchId, DateTime ManifesDate)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand dbCommand = null;
int MaximumRows = 0;
IDataReader dr = null;
try
{
dbCommand = db.GetStoredProcCommand("uspGetHotelManifestDifferenceCount");
db.AddInParameter(dbCommand, "@pManifestType", DbType.Int32, ManifestType);
db.AddInParameter(dbCommand, "@pCheckInDate", DbType.Date, ManifesDate);
db.AddInParameter(dbCommand, "@pBranchId", DbType.Int32, BranchId);
db.AddInParameter(dbCommand, "@pCompareManifestType", DbType.Int32, CompareManifestType);
dr = db.ExecuteReader(dbCommand);
if (dr.Read())
{
MaximumRows = Convert.ToInt32(dr["maximumRows"]);
}
return(MaximumRows);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dr != null)
{
dr.Dispose();
}
}
}
/// <summary>
/// Date Created: 28/11/2011
/// Created By: Josephine Gad
/// (description) Select manifest type
/// </summary>
/// <returns></returns>
public static DataTable GetManifestType()
{
DataTable dt = null;
DbCommand comm = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
comm = db.GetStoredProcCommand("uspGetManifestType");
dt = db.ExecuteDataSet(comm).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dt != null)
{
dt.Dispose();
}
if (comm != null)
{
comm.Dispose();
}
}
}
/// <summary>
/// Date Created: 28/11/2011
/// Created By: Josephine Gad
/// (description) Select Hotel Branch by User
/// </summary>
public static DataTable GetHotelBranchListByUser(string BranchName, string UserName, string UserRole, string sDate)
{
DataTable dt = null;
DbCommand comm = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
comm = db.GetStoredProcCommand("uspSelectHotelBranchListByUser");
db.AddInParameter(comm, "@pHotelName", DbType.String, BranchName);
db.AddInParameter(comm, "@pUserIDVarchar", DbType.String, UserName);
db.AddInParameter(comm, "@pUserRole", DbType.String, UserRole);
if (sDate != "")
{
db.AddInParameter(comm, "@pDate", DbType.DateTime, DateTime.Parse(sDate));
}
dt = db.ExecuteDataSet(comm).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dt != null)
{
dt.Dispose();
}
if (comm != null)
{
comm.Dispose();
}
}
}
public void ExecuteJob_NoOutputFormatSpecified_CommandExecutedWithoutWriter(string jobOutputFormat)
{
// setup
string connString = Guid.NewGuid().ToString();
ConnectionSetting connSetting = new ConnectionSetting(Guid.NewGuid().ToString(), connString);
ReportJob job = new ReportJob();
job.Command = Guid.NewGuid().ToString();
job.OutputFormat = jobOutputFormat;
IDbConnection dbConn = Substitute.For <IDbConnection>();
_dbConnectionFactory.CreateConnection(connString, true).Returns(dbConn);
IDbCommand cmd = Substitute.For <IDbCommand>();
dbConn.CreateCommand().Returns(cmd);
// execute
ReportJobResult result = _reportExecutor.ExecuteJob(connSetting, job);
// assert
cmd.Received(1).ExecuteNonQuery();
cmd.DidNotReceive().ExecuteReader();
Assert.AreEqual(0, result.RowCount);
Assert.GreaterOrEqual(result.ExecutionTime.TotalMilliseconds, 0);
}
public void ExecuteJob_OnExecute_CommandSettingsInitialisedCorrectly()
{
// setup
string connString = Guid.NewGuid().ToString();
ConnectionSetting connSetting = new ConnectionSetting(Guid.NewGuid().ToString(), connString);
ReportJob job = new ReportJob();
job.Command = Guid.NewGuid().ToString();
IDbConnection dbConn = Substitute.For <IDbConnection>();
_dbConnectionFactory.CreateConnection(connString, true).Returns(dbConn);
IDbCommand cmd = Substitute.For <IDbCommand>();
dbConn.CreateCommand().Returns(cmd);
// execute
_reportExecutor.ExecuteJob(connSetting, job);
// assert
dbConn.Received(1).CreateCommand();
cmd.Received(1).CommandTimeout = 0;
cmd.Received(1).CommandText = job.Command;
}
public void ExecuteJob_NoDbParameters_CommandParametersNotSet()
{
// setup
string connString = Guid.NewGuid().ToString();
ConnectionSetting connSetting = new ConnectionSetting(Guid.NewGuid().ToString(), connString);
ReportJob job = new ReportJob();
job.Command = Guid.NewGuid().ToString();
job.Parameters = Guid.NewGuid().ToString();
IDbConnection dbConn = Substitute.For <IDbConnection>();
_dbConnectionFactory.CreateConnection(connString, true).Returns(dbConn);
IDbCommand cmd = Substitute.For <IDbCommand>();
dbConn.CreateCommand().Returns(cmd);
_dbParameterUtility.ConvertXmlToDbParameters(job.Parameters).Returns(new SqlParameter[] { });
// execute
_reportExecutor.ExecuteJob(connSetting, job);
// assert
var temp = cmd.DidNotReceive().Parameters;
}
public void ExecuteJob_OneDbParameter_SingleCommandParameterSet()
{
// setup
string connString = Guid.NewGuid().ToString();
ConnectionSetting connSetting = new ConnectionSetting(Guid.NewGuid().ToString(), connString);
ReportJob job = new ReportJob();
job.Command = Guid.NewGuid().ToString();
job.Parameters = Guid.NewGuid().ToString();
IDbConnection dbConn = Substitute.For <IDbConnection>();
_dbConnectionFactory.CreateConnection(connString, true).Returns(dbConn);
IDbCommand cmd = Substitute.For <IDbCommand>();
dbConn.CreateCommand().Returns(cmd);
IDataParameterCollection parmColl = Substitute.For <IDataParameterCollection>();
cmd.Parameters.Returns(parmColl);
SqlParameter parm1 = new SqlParameter(Guid.NewGuid().ToString(), Guid.NewGuid().ToString());
_dbParameterUtility.ConvertXmlToDbParameters(job.Parameters).Returns(new SqlParameter[] { parm1 });
// execute
_reportExecutor.ExecuteJob(connSetting, job);
// assert
parmColl.Received(1).Add(parm1);
parmColl.Received(1).Add(Arg.Any <IDbDataParameter>());
}
/// <summary>
/// Date Created: 10/10/2011
/// Created By: Josephine Gad
/// (description) Get hotel booking by record locator or manual request ID, travel req ID, and SF ID
/// </summary>
public static DataTable GetSFHotelDetailsByID(string TravelReqID, string SfID, string sfStatus,
string recLoc, string manualReqID)
{
DataTable dt = null;
DbCommand command = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
command = db.GetStoredProcCommand("uspGetSFHotelInfoByID");
db.AddInParameter(command, "@pTravelReqIDInt", DbType.Int64, Int64.Parse(TravelReqID));
db.AddInParameter(command, "@pSeafarerIdInt", DbType.Int64, Int64.Parse(SfID));
db.AddInParameter(command, "@pSFStatus", DbType.String, sfStatus);
db.AddInParameter(command, "@pRecordLocator", DbType.String, recLoc);
db.AddInParameter(command, "@pRequestIDInt", DbType.Int64, Int64.Parse(manualReqID));
dt = db.ExecuteDataSet(command).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Date Created: 03/11/2011
/// Created By: Josephine Gad
/// (description) Get pending hotel bookings
/// </summary>
public static DataTable GetSFHotelTravelDetailsPending(string TravelReqID, string manualReqID)
{
DataTable dt = null;
DbCommand command = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
command = db.GetStoredProcCommand("uspGetSFHotelPendingByID");
db.AddInParameter(command, "@pTravelReqIDInt", DbType.Int64, Int64.Parse(TravelReqID));
db.AddInParameter(command, "@pRequestIDInt", DbType.Int64, Int64.Parse(manualReqID));
dt = db.ExecuteDataSet(command).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
public void ExecuteJob_OnSuccess_FinalisesJob()
{
// setup
ConnectionSetting connSetting = new ConnectionSetting("MyConn", "MyConnString");
ReportJob reportJob = new ReportJob();
reportJob.Id = new Random().Next(1, 100);
reportJob.CommandType = Guid.NewGuid().ToString();
reportJob.OutputFormat = Guid.NewGuid().ToString();
IReportExecutor reportExecutor = Substitute.For <IReportExecutor>();
_reportExecutorFactory.GetReportExecutor(reportJob.CommandType).Returns(reportExecutor);
// execute
_reportJobAgent.ExecuteJob(connSetting, reportJob);
// assert
_reportExecutorFactory.Received(1).GetReportExecutor(reportJob.CommandType);
_startJobCommand.Received(1).Execute(connSetting, reportJob.Id);
reportExecutor.Received(1).ExecuteJob(connSetting, reportJob);
_finaliseJobCommand.Received(1).Execute(connSetting, reportJob.Id);
_failJobCommand.Received(0).Execute(Arg.Any <ConnectionSetting>(), Arg.Any <int>(), Arg.Any <Exception>());
}
/// <summary>
/// Date Created: 21/07/2011
/// Created By: Josephine Gad
/// (description) Get seafarer port information per travel request ID/ request ID
/// --------------------------------------------------
/// Date Modified: 03/04/2012
/// Modified By: Josephine Gad
/// (description) Add parameter ViewInTR for the data in TblNoTravelRequest
/// --------------------------------------------------
/// Date Modified: 17/05/2012
/// Modified By: Josephine Gad
/// (description) Remove parameter ViewInTR
/// --------------------------------------------------
/// </summary>
public static DataTable GetSFPortTravelDetailsByTravelReqID(string TravelReqID, string ManualReqID, string SFStatus)
{
Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
DbCommand SFDbCommand = null;
DataTable InfoDataTable = null;
try
{
SFDbCommand = SFDatebase.GetStoredProcCommand("uspGetSFPortInfoByTravelRequestID");
SFDatebase.AddInParameter(SFDbCommand, "@pTravelReqIdInt", DbType.Int64, Int64.Parse(TravelReqID));
SFDatebase.AddInParameter(SFDbCommand, "@pRequestIDInt", DbType.Int64, Int64.Parse(ManualReqID));
SFDatebase.AddInParameter(SFDbCommand, "@pSFStatus", DbType.String, SFStatus);
//SFDatebase.AddInParameter(SFDbCommand, "@pViewInTR", DbType.Boolean, ViewInTR);
InfoDataTable = SFDatebase.ExecuteDataSet(SFDbCommand).Tables[0];
return(InfoDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (SFDbCommand != null)
{
SFDbCommand.Dispose();
}
if (InfoDataTable != null)
{
InfoDataTable.Dispose();
}
}
}
/// <summary>
/// Author: Josephine Monteza
/// Date Created: 30/Oct/2014
/// Description: Get overflow list count by month
/// ---------------------------------------------------------------
/// </summary>
/// <returns></returns>
public DataTable GetOverflowByMonthExport(string sUser)
{
DataTable dt = null;
DbCommand com = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
com = db.GetStoredProcCommand("uspGetHotelOverflowDashboardExport");
db.AddInParameter(com, "@pUser", DbType.String, sUser);
dt = db.ExecuteDataSet(com).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dt != null)
{
dt.Dispose();
}
if (com != null)
{
com.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Date Created: 14/09/2011
/// Created By: Josephine Gad
/// (description) Selecting list of air travel details by Air ID (nonSabre)
/// --------------------------------------------------------------------------
/// Date Modified: 28/11/2011
/// Modified By: Charlene Remotigue
/// (description) optimization (use datareader instead of datatable)
/// </summary>
/// <param name="AirIdBigint"></param>
/// <returns></returns>
public static IDataReader GetSFAirTravelDetailsOtherById(string AirIdBigint)
{
Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand SFDbCommand = null;
IDataReader SFDataReader = null;
try
{
SFDbCommand = SFDatebase.GetStoredProcCommand("uspGetSFAirTraveInfoOtherById");
SFDatebase.AddInParameter(SFDbCommand, "@TransAirIDBigInt", DbType.Int32, AirIdBigint);
SFDataReader = SFDatebase.ExecuteReader(SFDbCommand);
return(SFDataReader);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (SFDbCommand != null)
{
SFDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Created: 20/01/2012
/// Created By: Gelo Oquialda
/// (description) Load airport list by region
/// ---------------------------------------------
/// </summary>
//public static DataTable AirportLoad(string regionString, string userString)
//{
// Database SFDatebase= ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
// DbCommand SFDbCommand = null;
// IDataReader dataReader = null;
// DataTable HotelDataTable = null;
// try
// {
// SFDbCommand = SFDatebase.GetStoredProcCommand("uspSelectAirportByRegionID");
// SFDatebase.AddInParameter(SFDbCommand, "@pRegionIDInt", DbType.String, regionString);
// SFDatebase.AddInParameter(SFDbCommand, "@pUserIDVarchar", DbType.String, userString);
// dataReader = SFDatebase.ExecuteReader(SFDbCommand);
// HotelDataTable = new DataTable();
// HotelDataTable.Load(dataReader);
// return HotelDataTable;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (SFDbCommand != null)
// {
// SFDbCommand.Dispose();
// }
// if (HotelDataTable != null)
// {
// HotelDataTable.Dispose();
// }
// }
//}
/// <summary>
/// Date Created: 27/01/2012
/// Created By: Gelo Oquialda
/// (description) Loads airport code with airport name
/// </summary>
public static IDataReader GetAirportCodeName(int AirportID)
{
Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand SFDbCommand = null;
IDataReader dataReader = null;
try
{
SFDbCommand = SFDatebase.GetStoredProcCommand("uspGetAirportCodeName");
SFDatebase.AddInParameter(SFDbCommand, "@pAirportID", DbType.Int32, AirportID);
dataReader = SFDatebase.ExecuteReader(SFDbCommand);
return(dataReader);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (SFDbCommand != null)
{
SFDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Created: 14/09/2011
/// Created By: Josephine Gad
/// (description) Get airline list
/// </summary>
/// <returns></returns>
public static DataTable GetAirline()
{
Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand SFDbCommand = null;
DataTable SFDataTable = null;
try
{
SFDbCommand = SFDatebase.GetStoredProcCommand("uspSelectAirline");
SFDataTable = SFDatebase.ExecuteDataSet(SFDbCommand).Tables[0];
return(SFDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (SFDbCommand != null)
{
SFDbCommand.Dispose();
}
if (SFDataTable != null)
{
SFDataTable.Dispose();
}
}
}
/// <summary>
/// Author: Charlene Remotigue
/// Date Created: 29/11/2011
/// (description) get manifest difference
/// </summary>
/// <param name="ManifestType"></param>
/// <param name="CompareManifestType"></param>
/// <param name="BranchId"></param>
/// <param name="ManifesDate"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <returns></returns>
public DataTable GetHotelManifestDiff(int ManifestType, int CompareManifestType, int BranchId, DateTime ManifesDate, Int32 startRowIndex, Int32 maximumRows)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand dbCommand = null;
DataTable ManifestDataTable = null;
try
{
dbCommand = db.GetStoredProcCommand("uspGetHotelManifestDifference");
db.AddInParameter(dbCommand, "@pManifestType", DbType.Int32, ManifestType);
db.AddInParameter(dbCommand, "@pCheckInDate", DbType.Date, ManifesDate);
db.AddInParameter(dbCommand, "@pBranchId", DbType.Int32, BranchId);
db.AddInParameter(dbCommand, "@pCompareManifestType", DbType.Int32, CompareManifestType);
db.AddInParameter(dbCommand, "@pstartRowIndex", DbType.Int32, startRowIndex);
db.AddInParameter(dbCommand, "@pmaximumRows", DbType.Int32, maximumRows);
ManifestDataTable = db.ExecuteDataSet(dbCommand).Tables[0];
return(ManifestDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (ManifestDataTable != null)
{
ManifestDataTable.Dispose();
}
}
}
public int Execute(ConnectionSetting connection, int jobId)
{
using (IDbConnection dbConnection = _dbConnectionFactory.CreateConnection(connection.ConnectionString))
{
return(this.Execute(connection, jobId, dbConnection));
}
}
/// <summary>
/// Date Created: 18/08/2011
/// Created By: Josephine Gad
/// (description) Get user module menu
/// </summary>
public static DataTable GetMenu(string RoleKeyString, bool ViewInactiveBool)
{
DataTable RoleDataTable = null;
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleKeyString = (RoleKeyString == "0" ? "00000000-0000-0000-0000-000000000000" : RoleKeyString);
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspGetMenu");
RoleDatabase.AddInParameter(RoleDbCommand, "@pRoleIDUnique", DbType.String, RoleKeyString);
RoleDatabase.AddInParameter(RoleDbCommand, "@pViewInactive", DbType.Boolean, ViewInactiveBool);
RoleDataTable = RoleDatabase.ExecuteDataSet(RoleDbCommand).Tables[0];
return(RoleDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
if (RoleDataTable != null)
{
RoleDataTable.Dispose();
}
}
}
public int Execute(ConnectionSetting connection, int jobId, IDbConnection dbConnection)
{
const string query = "UPDATE ReportJobQueue SET ProcessStartDate = @ProcessStartDate, Status = @Status WHERE Id = @Id";
_concurrencyCoordinator.LockReportJob(connection.Name, jobId);
return(dbConnection.Execute(query, new { Id = jobId, ProcessStartDate = DateTime.UtcNow, Status = JobStatus.Processing }));
}
/// <summary>
/// Date Created: 23/08/2011
/// Created By: Josephine Gad
/// (description) Get user Regions
/// ------------------------------------
/// Date Created: 22/11/2011
/// Created By: Josephine Gad
/// (description) Add parameter Loginame
/// </summary>
//public static DataTable GetUserRegion(string UserString, string LoginName)
//{
// DataTable RoleDataTable = null;
// DbCommand RoleDbCommand = null;
// try
// {
// Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
// RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspGetUserMapRef");
// RoleDatabase.AddInParameter(RoleDbCommand, "@pUserIDVarchar", DbType.String, UserString);
// RoleDatabase.AddInParameter(RoleDbCommand, "@pLoginName", DbType.String, LoginName);
// RoleDataTable = RoleDatabase.ExecuteDataSet(RoleDbCommand).Tables[0];
// return RoleDataTable;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (RoleDbCommand != null)
// {
// RoleDbCommand.Dispose();
// }
// if (RoleDataTable != null)
// {
// RoleDataTable.Dispose();
// }
// }
//}
///// <summary>
///// Date Created: 24/08/2011
///// Created By: Josephine Gad
///// (description) Get user Regions not yet added in user
///// -------------------------------------------------------
///// Date Created: 22/11/2011
///// Created By: Josephine Gad
///// (description) Add parameter LoginName
///// </summary>
//public static DataTable GetUserRegionNotAdded(string UserString, string LoginName)
//{
// DataTable RoleDataTable = null;
// DbCommand RoleDbCommand = null;
// try
// {
// Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
// RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspGetUserMapRefNotAdded");
// RoleDatabase.AddInParameter(RoleDbCommand, "@pUserIDVarchar", DbType.String, UserString);
// RoleDatabase.AddInParameter(RoleDbCommand, "@pLoginName", DbType.String, LoginName);
// RoleDataTable = RoleDatabase.ExecuteDataSet(RoleDbCommand).Tables[0];
// return RoleDataTable;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (RoleDbCommand != null)
// {
// RoleDbCommand.Dispose();
// }
// if (RoleDataTable != null)
// {
// RoleDataTable.Dispose();
// }
// }
//}
/// <summary>
/// Date Created: 24/08/2011
/// Created By: Josephine Gad
/// (description) Add user region/mapReference
/// </summary>
public static Int32 AddUserMapRef(string UserIDString, string MapIDString, string CreatedByString)
{
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspAddUserMapRef");
RoleDatabase.AddInParameter(RoleDbCommand, "@pUserIDVarchar", DbType.String, UserIDString);
RoleDatabase.AddInParameter(RoleDbCommand, "@pMapIDInt", DbType.Int16, Int16.Parse(MapIDString));
RoleDatabase.AddInParameter(RoleDbCommand, "@pCreatedByVarchar", DbType.String, CreatedByString);
RoleDatabase.AddOutParameter(RoleDbCommand, "@pUserRegionID", DbType.Int32, 8);
RoleDatabase.ExecuteDataSet(RoleDbCommand);
Int32 UserRegionID = Convert.ToInt32(RoleDatabase.GetParameterValue(RoleDbCommand, "@pUserRegionID"));
return(UserRegionID);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Created: 24/08/2011
/// Created By: Josephine Gad
/// (description) Delete user region/mapReference
/// </summary>
public static void DeleteUserMapRef(string UserMapIDInt, string ModifiedByString)
{
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspDeleteUserMapRef");
//RoleDatabase.AddInParameter(RoleDbCommand, "@pUserIDVarchar", DbType.String, UserIDString);
//RoleDatabase.AddInParameter(RoleDbCommand, "@pMapIDInt", DbType.Int16, Int16.Parse(MapIDString));
RoleDatabase.AddInParameter(RoleDbCommand, "@pUserMapIDInt", DbType.Int16, Int16.Parse(UserMapIDInt));
RoleDatabase.AddInParameter(RoleDbCommand, "@pModifiedByVarchar", DbType.String, ModifiedByString);
RoleDatabase.ExecuteDataSet(RoleDbCommand);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Created: 23/08/2011
/// Created By: Josephine Gad
/// (description) Activate Menu
/// </summary>
public static void ActivateMenu(string MenuID, string ModifiedByString)
{
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspActivateMenu");
RoleDatabase.AddInParameter(RoleDbCommand, "@pMenuIDInt", DbType.Int16, Int16.Parse(MenuID));
RoleDatabase.AddInParameter(RoleDbCommand, "@pModifiedByVarchar", DbType.String, ModifiedByString);
RoleDatabase.ExecuteDataSet(RoleDbCommand);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Created: 18/08/2011
/// Created By: Josephine Gad
/// (description) Get user module rights
/// </summary>
public static DataTable GetUserModule(string RoleKeyString)
{
DataTable RoleDataTable = null;
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspGetModuleRights");
RoleDatabase.AddInParameter(RoleDbCommand, "@RoleIDUnique", DbType.String, RoleKeyString);
RoleDataTable = RoleDatabase.ExecuteDataSet(RoleDbCommand).Tables[0];
return(RoleDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
if (RoleDataTable != null)
{
RoleDataTable.Dispose();
}
}
}
/// <summary>
/// Date Created: 23/08/2011
/// Created By: Josephine Gad
/// (description) Get user module submenu
/// </summary>
public static DataTable GetSubMenuAll(string ParentIdInt)
{
DataTable RoleDataTable = null;
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspGetMenuSubAll");
RoleDatabase.AddInParameter(RoleDbCommand, "@pParentPageIDInt", DbType.Int16, Int16.Parse(ParentIdInt));
RoleDataTable = RoleDatabase.ExecuteDataSet(RoleDbCommand).Tables[0];
return(RoleDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
if (RoleDataTable != null)
{
RoleDataTable.Dispose();
}
}
}
/// <summary>
/// Date Created: 23/08/2011
/// Created By: Josephine Gad
/// (description) Delete Menu by Role ID and Page ID
/// </summary>
public static Int32 DeleteMenuByRoleId(string RoleKeyString, string PageIDString, string ModifiedBy)
{
DbCommand RoleDbCommand = null;
try
{
Database RoleDatabase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
RoleDbCommand = RoleDatabase.GetStoredProcCommand("uspDeleteMenuByRoleId");
RoleDatabase.AddInParameter(RoleDbCommand, "@pRoleIDUnique", DbType.String, RoleKeyString);
RoleDatabase.AddInParameter(RoleDbCommand, "@pPageIDInt", DbType.Int16, Int16.Parse(PageIDString));
RoleDatabase.AddInParameter(RoleDbCommand, "@pModifiedByVarchar", DbType.String, ModifiedBy);
RoleDatabase.AddOutParameter(RoleDbCommand, "@pMenuID", DbType.Int32, 8);
RoleDatabase.ExecuteDataSet(RoleDbCommand);
Int32 MenuID = Convert.ToInt32(RoleDatabase.GetParameterValue(RoleDbCommand, "@pMenuID"));
return(MenuID);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (RoleDbCommand != null)
{
RoleDbCommand.Dispose();
}
}
}
/// <summary>
/// Date Modified: 30/Oct/2013
/// Modified By: Josephine Gad
/// (description) Get Service Request List for Export Use
/// </summary>
public DataTable GetServiceRequestExport(string sUser)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase()
DbCommand dbComm = null;
DataSet ds = null;
DataTable dt = null;
try
{
dbComm = db.GetStoredProcCommand("uspGetServiceRequestExport");
db.AddInParameter(dbComm, "@pUserID", DbType.String, sUser);
ds = db.ExecuteDataSet(dbComm);
dt = ds.Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbComm != null)
{
dbComm.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Date Created: 16/08/2011
/// Created By: Josephine Gad
/// (description) Get Country List by user
/// <param name="UserID"></param>
/// <returns></returns>
public static DataTable CountryListByUser(string UserID)
{
Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand SFDbCommand = null;
DataTable CountryDataTable = null;
try
{
SFDbCommand = SFDatebase.GetStoredProcCommand("uspSelectCountryList");
SFDatebase.AddInParameter(SFDbCommand, "@pUserIDVarchar", DbType.String, UserID);
CountryDataTable = SFDatebase.ExecuteDataSet(SFDbCommand).Tables[0];
return(CountryDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (SFDbCommand != null)
{
SFDbCommand.Dispose();
}
if (CountryDataTable != null)
{
CountryDataTable.Dispose();
}
}
}
/// <summary>
/// Date Created: 17/11/2011
/// Created By: Gabriel Oquialda
/// (description) Get audit trail logs
/// </summary>
/// <param name="dFrom"></param>
/// <param name="dTo"></param>
/// <param name="StartRow"></param>
/// <param name="MaxRow"></param>
/// <returns></returns>
public static DataTable GetAuditTrail(DateTime DateFrom, DateTime DateTo, int StartRow, int MaxRow)
{
DataTable dt = null;
DbCommand command = null;
try
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
command = db.GetStoredProcCommand("uspSelectLogAuditTrail");
db.AddInParameter(command, "@pDateFrom", DbType.DateTime, DateFrom);
db.AddInParameter(command, "@pDateTo", DbType.DateTime, DateTo);
db.AddInParameter(command, "@pStartRow", DbType.Int16, StartRow);
db.AddInParameter(command, "@pMaxRow", DbType.Int64, MaxRow);
dt = db.ExecuteDataSet(command).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dt != null)
{
dt.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
}
/// <summary>
/// Date Created: 15/11/2011
/// Created By: Gabriel Oquialda
/// (description) Insert log audit trail
/// ----------------------------------------
/// Date Modified: 01/10/2012
/// Modified By: Josephine Gad
/// (description) Add trans.Dispose();
/// </summary>
public static void InsertLogAuditTrail(Int32 pID, string pSeqNo, String strLogDescription, String strFunction, String strPageName,
DateTime DateGMT, DateTime CreatedDate, String CreatedBy)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand dbCommand = null;
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction trans = connection.BeginTransaction();
try
{
string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString();
dbCommand = db.GetStoredProcCommand("uspInsertLogAuditTrail");
if (pID != 0)
{
db.AddInParameter(dbCommand, "@pIdBigint", DbType.Int32, pID);
}
if (pSeqNo.Trim() != "0" && pSeqNo != "")
{
db.AddInParameter(dbCommand, "@pSeqNoInt", DbType.Int32, Convert.ToInt32(pSeqNo));
}
db.AddInParameter(dbCommand, "@pLogDescriptionVarchar", DbType.String, strLogDescription);
db.AddInParameter(dbCommand, "@pFunctionVarchar", DbType.String, strFunction);
db.AddInParameter(dbCommand, "@pPageNameVarchar", DbType.String, strPageName);
db.AddInParameter(dbCommand, "@pTimeZoneVarchar", DbType.String, strTimeZone);
db.AddInParameter(dbCommand, "@pDateCreatedGMT", DbType.DateTime, DateGMT);
db.AddInParameter(dbCommand, "@pDateCreatedDate", DbType.DateTime, CreatedDate);
db.AddInParameter(dbCommand, "@pCreatedByVarchar", DbType.String, CreatedBy);
db.ExecuteNonQuery(dbCommand, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (dbCommand != null)
{
dbCommand.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
}
}
public static bool GenerateAll(string saveDictory, ConnectionSetting setting, IList selectedTables, string templatePath, string @namespace, out string errorMsg)
{
errorMsg = string.Empty;
string[] tables = new string[selectedTables.Count];
for (int i = 0; i < selectedTables.Count; i++)
{
tables[i] = selectedTables[i].ToString();
}
string guid = Guid.NewGuid().ToString("N");
int error = 0;
foreach (string name in tables)
{
GenerateParameter para = new GenerateParameter
{
TableName = name,
Setting = setting,
SavePath = saveDictory,
TemplatePath = templatePath,
Tables = tables
};
if (!Generate(para, guid, @namespace))
{
errorMsg += "[" + para.TableName + "]";
error++;
}
}
return(error == 0);
}
protected override void OnActivate()
{
try
{
using (ConnectionSetting form = new ConnectionSetting())
{
if (DialogResult.OK == form.ShowDialog())
{
}
}
}
catch (SystemException e)
{
MessageBox.Show(e.Message);
}
finally
{
Terminate();
}
}
private ConnectionSetting ParseLine(string line)
{
var setting = new ConnectionSetting();
setting.Index = -1;
setting.Name = String.Empty;
setting.ConnectionString = String.Empty;
setting.Valid = false;
try
{
var data = line.Split('~');
setting.Index = Convert.ToInt32(data[0].ToString());
setting.Name = data[1].ToString();
setting.ConnectionString = data[2].ToString();
setting.Valid = true;
}
catch
{
setting.Valid = false;
}
return setting;
}
/// <summary>
///
/// </summary>
/// <param name="connectionSetting">connection setting</param>
public SqlDataProvider(ConnectionSetting connectionSetting)
: base(connectionSetting)
{
}