/// <summary>
/// Saaif: 20131003
/// </summary>
/// <param name="transactionList"></param>
/// <param name="dbName"></param>
/// <returns></returns>
internal bool ExecuteSqlTransaction(List <SQLTransaction> transactionList, string dbName)
{
bool result = true;
try
{
String strConnectionString = DBConstants.GetConnectionString(dbName);
{
using (SqlConnection con = new SqlConnection(strConnectionString))
{
con.Open();
using (SqlTransaction transaction = con.BeginTransaction())
{
try
{
int dbResult = -1;
foreach (SQLTransaction sqlTransaction in transactionList)
{
dbResult = -1;
dbResult = SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlTransaction.SqlQuery);
if (dbResult >= 0)
{
continue;
}
else
{
transaction.Rollback();
result = false;
break;
}
}
if (dbResult > 0 && result)
{
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
}
con.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// Gets the seat details.
/// </summary>
/// <param name="objGetContactDetails">The obj get contact details.</param>
/// <returns></returns>
public string GetSeatDetails(BusinessEntities.ContactDetails objGetContactDetails)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
sqlParam = new SqlParameter[1];
string sname = string.Empty;
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objGetContactDetails.EMPId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objGetContactDetails.EMPId;
}
objDataReader = objDA.ExecuteReaderSP(SPNames.Employee_GetSeatDetails, sqlParam);
while (objDataReader.Read())
{
sname = objDataReader[DbTableColumn.Seat_SeatName].ToString();
}
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, GETSEATDETAILS, EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
if (objDataReader != null)
{
objDataReader.Close();
}
objDA.CloseConncetion();
}
// Return the Collection
return(sname);
}
/// <summary>
/// gets the Employee details as per the ID.
/// </summary>
/// <param name="EmpID"></param>
/// <returns></returns>
public BusinessEntities.SeatAllocation GetEmployeeDetailsByID(BusinessEntities.SeatAllocation EmpID)
{
DataAccessClass objDASeatAllocation = new DataAccessClass();
try
{
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.EmployeeID, DbType.Int32);
sqlParam[0].Value = EmpID.EmployeeID;
//--get result
DataSet dsSeatDescription = objDASeatAllocation.GetDataSet(SPNames.SeatAllocation_GetEmployeeDetailsByID, sqlParam);
//--Create entities and add to list
BusinessEntities.SeatAllocation objBESeatDetail = new BusinessEntities.SeatAllocation();
foreach (DataRow dr in dsSeatDescription.Tables[0].Rows)
{
if (dr[DbTableColumn.Seat_EmployeeCode].ToString() != string.Empty)
{
objBESeatDetail.EmployeeCode = dr[DbTableColumn.Seat_EmployeeCode].ToString();
}
if (dr[DbTableColumn.Seat_EmployeeCode].ToString() != string.Empty)
{
objBESeatDetail.EmployeeCode = dr[DbTableColumn.Seat_EmployeeCode].ToString();
}
if (dr[DbTableColumn.Seat_EmployeeName].ToString() != string.Empty)
{
objBESeatDetail.EmployeeName = dr[DbTableColumn.Seat_EmployeeName].ToString();
}
if (dr[DbTableColumn.Seat_EMPEmailID].ToString() != string.Empty)
{
objBESeatDetail.EmployeeEmailID = dr[DbTableColumn.Seat_EMPEmailID].ToString();
}
}
return(objBESeatDetail);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetEmployeeDetailsByID", EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
finally
{
objDASeatAllocation.CloseConncetion();
}
}
private void OnAction_SelectTop100Rows(object sender, EventArgs e)
{
ObjectInfo objInfo = GetObjectInfoForWordAtCursor();
if ((objInfo == null) || (!DBConstants.DoesObjectTypeHoldsData(objInfo.ObjectTypeAbb)))
{
MessageBox.Show("Object is not a table or view!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var script = $"SELECT TOP 100 * FROM {objInfo.FullNameQuoted}";
ExecScript(script, ScriptRunType.Execute, 0, false, false);
}
/// <summary>
/// Checks the CR reference no.
/// </summary>
/// <param name="objCRDetails">The obj CR details.</param>
/// <returns></returns>
public bool checkCRReferenceNo(BusinessEntities.Contract objCRDetails)
{
bool result = false;
DataAccessClass ProjectDetails = new DataAccessClass();
try
{
ProjectDetails.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[4];
sqlParam[0] = new SqlParameter(SPParameter.CRReferenceNo, DbType.String);
sqlParam[0].Value = objCRDetails.CRReferenceNo;
sqlParam[1] = new SqlParameter(SPParameter.ContractId, DbType.Int32);
sqlParam[1].Value = objCRDetails.ContractID;
sqlParam[2] = new SqlParameter(SPParameter.ProjectCode, DbType.String);
sqlParam[2].Value = objCRDetails.CRProjectCode;
sqlParam[3] = new SqlParameter(SPParameter.COUNT, DbType.Int32);
sqlParam[3].Direction = ParameterDirection.Output;
//gets the all employee details related to project .
ProjectDetails.ExecuteNonQuerySP(SPNames.Contracts_CheckCRReferenceNo, sqlParam);
int count = Convert.ToInt32(sqlParam[3].Value);
if (count > 0)
{
result = true;
}
else
{
result = false;
}
return(result);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, Contracts, "checkCRReferenceNo", EventIDConstants.RAVE_HR_CONTRACT_PRESENTATION_LAYER);
}
finally
{
ProjectDetails.CloseConncetion();
}
}
private void ModifySelectedObjectsEx()
{
if (grd.SelectedRows.Count == 0)
{
return;
}
int objId = -1;
string objType = String.Empty;
string objName = String.Empty;
IList<frmScriptEditor> editors = new List<frmScriptEditor>();
foreach (DataGridViewRow row in grd.SelectedRows)
{
DataGridViewCell cellObjid = row.Cells[0];
DataGridViewCell cellName = row.Cells[2];
DataGridViewCell cellType = row.Cells[3];
if (cellName.ValueType != typeof(string) || cellName.Value == null)
{
continue;
}
if (cellType.ValueType != typeof(string) || cellType.Value == null)
{
continue;
}
if (cellObjid.ValueType != typeof(int) || cellObjid.Value == null)
{
continue;
}
objId = (int)cellObjid.Value;
objType = (string)cellType.Value;
objName = (string)cellName.Value;
if (DBConstants.DoesObjectTypeHasScript(objType))
{
int type = DBConstants.GetDBObjectType(objType);
string script = ScriptingHelper.GetAlterScript(_connParams.ConnectionString, _connParams.Database, objId, type);
frmScriptEditor editor = ScriptEditorFactory.Create(objName, script, objId, type, _connParams, _connParams.Database);
editors.Add(editor);
}
}
foreach (frmScriptEditor editor in editors)
{
ScriptEditorFactory.ShowScriptEditor(editor);
}
}
/// <summary>
/// get the branches of a section.
/// </summary>
/// <param name="branchID"></param>
/// <returns></returns>
public RaveHRCollection GetSectionByBranch(int branchID)
{
BusinessEntities.SeatAllocation objSeatAllocation = null;
SqlDataReader objReader = null;;
DataAccessClass objDASeatAllocation = new DataAccessClass();
try
{
raveHRCollection = new RaveHRCollection();
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.BranchID, DbType.Int32);
sqlParam[0].Value = branchID;
objReader = objDASeatAllocation.ExecuteReaderSP(SPNames.SeatAllocation_GetSection, sqlParam);
while (objReader.Read())
{
objSeatAllocation = new BusinessEntities.SeatAllocation();
objSeatAllocation.SectionID = int.Parse(objReader[DbTableColumn.Seat_SectionID].ToString());
objSeatAllocation.SectionName = objReader[DbTableColumn.Seat_SectionName].ToString();
raveHRCollection.Add(objSeatAllocation);
}
return(raveHRCollection);
}
//catches RaveHRException exception
catch (RaveHRException ex)
{
throw ex;
}
//catches genral exception
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetSectionByBranch", EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
//close datareader and connection
finally
{
//checks if datareader is null
if (!objReader.IsClosed)
{
//close datareader
objReader.Close();
}
//close connection
objDASeatAllocation.CloseConncetion();
}
}
/// <summary>
/// Saaif:20130910. Executes a Transact-SQL statement against the connection and returns the number of rows affected.
/// </summary>
/// <param name="sqlQuery"></param>
/// <param name="dbName"></param>
/// <returns>The number of rows affected.</returns>
internal object ExecuteNonQuery(string sqlQuery, string dbName)
{
object objValue;
try
{
objValue = SqlHelper.ExecuteNonQuery(DBConstants.GetConnectionString(dbName), CommandType.Text, sqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(objValue);
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public DataTable GetDataTableBySp(string spName, string dbName)
{
DataTable dtResults = new DataTable();
try
{
dtResults = SqlHelper.ExecuteDataTable(DBConstants.GetConnectionString(dbName), CommandType.StoredProcedure, spName);
}
catch (Exception ex)
{
throw ex;
}
return(dtResults);
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public object GetScalarValueBySp(string spName, string dbName)
{
object objValue;
try
{
objValue = SqlHelper.ExecuteScalar(DBConstants.GetConnectionString(dbName), CommandType.StoredProcedure, spName);
}
catch (Exception ex)
{
throw ex;
}
return(objValue);
}
/// <summary>
///
/// </summary>
/// <param name="strQuery"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public object GetScalarValueByQuery(string strQuery, string dbName)
{
object objValue;
try
{
objValue = SqlHelper.ExecuteScalar(DBConstants.GetConnectionString(dbName), CommandType.Text, strQuery);
}
catch (Exception ex)
{
throw ex;
}
return(objValue);
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="sparams"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public DataTable GetDataTableBySpWithParams(string spName, object[] sparams, string dbName)
{
DataTable dtResults = new DataTable();
try
{
dtResults = SqlHelper.ExecuteDataTable(DBConstants.GetConnectionString(dbName), spName, sparams);
}
catch (Exception ex)
{
throw ex;
}
return(dtResults);
}
/// <summary>
///
/// </summary>
/// <param name="strQuery"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public DataTable GetDataTableByQuery(string strQuery, string dbName)
{
DataTable dtResults = new DataTable();
try
{
dtResults = SqlHelper.ExecuteDataTable(DBConstants.GetConnectionString(dbName), CommandType.Text, strQuery);
}
catch (Exception ex)
{
throw ex;
}
return(dtResults);
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="sparams"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public object GetScalarValueBySp(string spName, object[] sparams, string dbName)
{
object objValue;
try
{
objValue = SqlHelper.ExecuteScalar(DBConstants.GetConnectionString(dbName), spName, sparams);
}
catch (Exception ex)
{
throw ex;
}
return(objValue);
}
private void RenderContextMenu( )
{
mnuItemOpen.Visible = false;
mnuItemModify.Visible = false;
if (grd.SelectedRows.Count == 0)
{
return;
}
int objId = -1;
string objType = String.Empty;
string objName = String.Empty;
foreach (DataGridViewRow row in grd.SelectedRows)
{
DataGridViewCell cellName = row.Cells[0];
DataGridViewCell cellType = row.Cells[1];
DataGridViewCell cellObjid = row.Cells[2];
if (cellName.ValueType != typeof(string) || cellName.Value == null)
{
continue;
}
if (cellType.ValueType != typeof(string) || cellType.Value == null)
{
continue;
}
if (cellObjid.ValueType != typeof(int) || cellObjid.Value == null)
{
continue;
}
objId = (int)cellObjid.Value;
objType = (string)cellType.Value;
objName = (string)cellName.Value;
if (!mnuItemOpen.Visible && DBConstants.DoesObjectTypeHoldsData(objType))
{
mnuItemOpen.Visible = true;
}
if (!mnuItemModify.Visible && DBConstants.DoesObjectTypeHasScript(objType))
{
mnuItemModify.Visible = true;
}
}
}
/// <summary>
/// This method will fetch records from data base and return to business layer
/// </summary>
/// <param name=></param>
/// <returns></returns>
public List <BusinessEntities.ContractProject> GetProjectdetails()
{
List <BusinessEntities.ContractProject> objListOfProjects = null;
DataAccessClass objProjectDetails = new DataAccessClass();
try
{
objProjectDetails.OpenConnection(DBConstants.GetDBConnectionString());
//gets the all project details.
DataSet dsProjectdetails = objProjectDetails.GetDataSet(SPNames.Contract_SearchProjectDetails);
//Create entities and add to list
BusinessEntities.ContractProject objProjects = null;
objListOfProjects = new List <BusinessEntities.ContractProject>();
foreach (DataRow dr in dsProjectdetails.Tables[0].Rows)
{
objProjects = new BusinessEntities.ContractProject();
objProjects.ProjectID = Convert.ToInt32(dr[DbTableColumn.Con_ProjectID]);
objProjects.ProjectCode = dr[DbTableColumn.Con_ProjectCode].ToString();
objProjects.DocumentName = dr[DbTableColumn.Con_DocumentName].ToString();
objProjects.ContractCode = dr[DbTableColumn.Con_ContractCode].ToString();
objProjects.ProjectName = dr[DbTableColumn.Con_ConProjectName].ToString();
objProjects.ContractType = dr[DbTableColumn.Con_ContractType].ToString();
//--add to list
objListOfProjects.Add(objProjects);
}
return(objListOfProjects);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetProjectdetails", EventIDConstants.RAVE_HR_CONTRACT_DATA_ACCESS_LAYER);
}
finally
{
objProjectDetails.CloseConncetion();
}
}
/// <summary>
/// Deletes the contact details.
/// </summary>
/// <param name="objDeleteContactDetails">The obj delete contact details.</param>
public void DeleteContactDetails(BusinessEntities.ContactDetails objDeleteContactDetails)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
sqlParam = new SqlParameter[2];
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.EmpContactId, SqlDbType.Int);
if (objDeleteContactDetails.EmployeeContactId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objDeleteContactDetails.EmployeeContactId;
}
sqlParam[1] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objDeleteContactDetails.EMPId == 0)
{
sqlParam[1].Value = DBNull.Value;
}
else
{
sqlParam[1].Value = objDeleteContactDetails.EMPId;
}
//Execute SP along with proper parameters
objDA.ExecuteNonQuerySP(SPNames.Employee_DeleteContact, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, DELETECONTACTDETAILS, EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}
/// <summary>
/// Get_s the client abbrivation.
/// </summary>
/// <param name="MasterId">The master id.</param>
/// <returns></returns>
public string Get_ClientAbbrivation(int MasterId)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
SqlParameter[] sqlParam = new SqlParameter[1];
string sname = string.Empty;
SqlDataReader objDataReader;
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.MasterId, SqlDbType.Int);
if (MasterId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = MasterId;
}
objDataReader = objDA.ExecuteReaderSP(SPNames.Contracts_GetClientAbbrivation, sqlParam);
while (objDataReader.Read())
{
sname = objDataReader[DbTableColumn.Con_Details].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
objDA.CloseConncetion();
}
}
return(sname);
}
/// <summary>
/// Gets the logged in user emailid
/// </summary>
/// <returns>string</returns>
public string GetWindowsUsernameAsPerNorthgate(string windowsUsername, out string domainName)
{
string username = "";
string domName = "";
SqlDataReader objReader;
DataAccessClass objDA;
try
{
objDA = new DataAccessClass();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[3];
sqlParam[0] = new SqlParameter("@Username", SqlDbType.VarChar, 100);
if (windowsUsername == "" || windowsUsername == null)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = windowsUsername;
}
sqlParam[1] = new SqlParameter("@OutUsername", SqlDbType.VarChar, 100);
sqlParam[1].Value = "";
sqlParam[1].Direction = ParameterDirection.Output;
sqlParam[2] = new SqlParameter("@domainName", SqlDbType.VarChar, 100);
sqlParam[2].Value = "";
sqlParam[2].Direction = ParameterDirection.Output;
objReader = objDA.ExecuteReaderSP(SPNames.Master_GetNorthgateUsername, sqlParam);
username = sqlParam[1].Value.ToString();
domName = sqlParam[2].Value.ToString();
domainName = domName;
return(username);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
new RaveHRException(ex.Message, ex, Sources.CommonLayer, "RaveHRAuthorizationManager", "GetWindowsUsernameAsPerNorthgate", EventIDConstants.AUTHORIZATION_MANAGER_ERROR);
}
domainName = domName;
return(username);
}
/// <summary>
/// gets the email id for a employee.
/// </summary>
/// <param name="empId"></param>
/// <returns></returns>
public string GetEmailID(string empName)
{
string[] empNameArray = empName.Split(' ');
string firstName = empNameArray[0];
string lastName = empNameArray[empNameArray.Length - 1];
SqlConnection objConnection = null;
SqlCommand objCommand = null;
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objCommand = new SqlCommand(SPNames.USP_EMPLOYEE_GETEMAILID, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue(SPParameter.FirstName, firstName);
objCommand.Parameters.AddWithValue(SPParameter.LastName, lastName);
objConnection.Open();
string emailID = string.Empty;
objDataReader = objCommand.ExecuteReader();
while (objDataReader.Read())
{
emailID = objDataReader[DbTableColumn.EmailId].ToString();
}
return(emailID);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, Contracts, GETEMAILID, EventIDConstants.RAVE_HR_CONTRACT_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
/// <summary>
/// Checks the name of project.
/// </summary>
/// <param name="projectName"></param>
/// <returns></returns>
public bool checkProjectCode(string projectCode)
{
bool result = false;
DataAccessClass ProjectDetails = new DataAccessClass();
try
{
objContractProject = new BusinessEntities.ContractProject();
ProjectDetails.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[2];
sqlParam[0] = new SqlParameter(SPParameter.ProjectCode, DbType.String);
sqlParam[0].Value = projectCode;
sqlParam[1] = new SqlParameter(SPParameter.COUNT, DbType.Int32);
sqlParam[1].Direction = ParameterDirection.Output;
//gets the all employee details related to project .
ProjectDetails.ExecuteNonQuerySP(SPNames.Contract_CheckProjectCode, sqlParam);
int count = Convert.ToInt32(sqlParam[1].Value);
if (count > 0)
{
result = true;
}
else
{
result = false;
}
return(result);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "checkProjectName", EventIDConstants.RAVE_HR_CONTRACT_DATA_ACCESS_LAYER);
}
finally
{
ProjectDetails.CloseConncetion();
}
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="sparams"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public object GetScalarValueBySp(string spName, object[] sparams, string dbName)
{
object objValue;
try
{
objValue = SqlHelper.ExecuteScalar(DBConstants.GetConnectionString(dbName), spName, sparams);
}
catch (Exception ex)
{
System.ArgumentException argEx = new System.ArgumentException(spName, ex);
throw argEx;
}
return(objValue);
}
public int AddExistingQuestionInPaper(int assessmentPaperId, int questionId)
{
int saveStatus = 0;
SqlConnection objConnection = null;
SqlCommand objCommand = null;
string ConnStr = string.Empty;
try
{
ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
Master master = new Master();
objCommand = new SqlCommand(SPNames.AddExistingQuestionInPaper, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParam = new SqlParameter[9];
sqlParam[0] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentPaperId, assessmentPaperId);
sqlParam[1] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentQuestionId, questionId);
sqlParam[2] = objCommand.Parameters.AddWithValue(SPParameter.IsActive, true);
sqlParam[3] = objCommand.Parameters.AddWithValue(SPParameter.CreatedById, master.GetEmployeeIDByEmailID());
sqlParam[4] = objCommand.Parameters.AddWithValue(SPParameter.CreatedByDate, DateTime.Now);
sqlParam[5] = objCommand.Parameters.AddWithValue(SPParameter.LastModifiedById, master.GetEmployeeIDByEmailID());
sqlParam[6] = objCommand.Parameters.AddWithValue(SPParameter.LastModifiedDate, DateTime.Now);
sqlParam[7] = objCommand.Parameters.AddWithValue(SPParameter.Status, 0);
sqlParam[7].Direction = ParameterDirection.Output;
sqlParam[8] = objCommand.Parameters.AddWithValue(SPParameter.IsNewQuestion, 0);
objCommand.ExecuteNonQuery();
saveStatus = Convert.ToInt32(sqlParam[7].Value);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, "AddExistingQuestionInPaper", "AddExistingQuestionInPaper", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
return(saveStatus);
}
/// <summary>
///
/// </summary>
/// <param name="Source"></param>
/// <param name="Destination"></param>
/// <returns></returns>
public bool ShiftLocation(BusinessEntities.SeatAllocation Source, BusinessEntities.SeatAllocation Destination)
{
DataAccessClass objDASeatAllocation = new DataAccessClass();
try
{
bool result = false;
//Opens the connection.
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[2];
sqlParam[0] = new SqlParameter(SPParameter.SourceLocation, DbType.Int32);
sqlParam[0].Value = Source.SeatID;
sqlParam[1] = new SqlParameter(SPParameter.Destination, DbType.Int32);
sqlParam[1].Value = Destination.SeatID;
//sqlParam[2] = new SqlParameter(SPParameter.EmployeeID, DbType.Int32);
//sqlParam[2].Value = Destination.EmployeeID;
//update changes in the database.
int shift = objDASeatAllocation.ExecuteNonQuerySP(SPNames.SeatAllocation_ShiftLocation, sqlParam);
if (shift != 0)
{
result = true;
}
else
{
result = false;
}
return(result);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, SHIFTLOCATION, EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
finally
{
objDASeatAllocation.CloseConncetion();
}
}
/// <summary>
/// Deletes the professional details.
/// </summary>
/// <param name="objDeleteProfessionalDetails">The obj delete professional details.</param>
public void DeleteProfessionalDetails(BusinessEntities.ProfessionalDetails objDeleteProfessionalDetails)
{
objDA = new DataAccessClass();
sqlParam = new SqlParameter[2];
try
{
objDA.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.ProfessionalId, SqlDbType.Int);
if (objDeleteProfessionalDetails.ProfessionalId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objDeleteProfessionalDetails.ProfessionalId;
}
sqlParam[1] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objDeleteProfessionalDetails.EMPId == 0)
{
sqlParam[1].Value = DBNull.Value;
}
else
{
sqlParam[1].Value = objDeleteProfessionalDetails.EMPId;
}
objDA.ExecuteNonQuerySP(SPNames.Employee_DeleteProfessionalDetails, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, "DeleteProfessionalDetails", EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}
public static SelectList FillMasterKSSTypeList(string CategoryName, int UserEmpId)
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.TNI_USP_TNI_GetDepartmentWiseKSSType, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("@Category", CategoryName);
objCommand.Parameters.AddWithValue("@UserEmpId", UserEmpId);
objReader = objCommand.ExecuteReader();
SelectListItem selListItem;
List <SelectListItem> newList = new List <SelectListItem>();
newList.Add(new SelectListItem {
Selected = true, Text = "Select", Value = ""
});
while (objReader.Read())
{
selListItem = new SelectListItem()
{
Value = objReader[0].ToString().Trim(), Text = objReader[1].ToString().Trim()
};
newList.Add(selListItem);
}
return(new SelectList(newList, "Value", "Text"));
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsCommonRepository, "FillMasterKSSTypeList", RMS.Common.Constants.EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
//Siddhesh Arekar Issue ID : 55884 Closure Type
/// <summary>
/// Get Master Type Details
/// </summary>
/// <param name="category"></param>
/// <returns>string</returns>
public KeyValue <string> GetMasterTypeDetails(int categoryId, string key)
{
//Declare DataAccess Class Object
objDA = new DataAccessClass();
SqlParameter[] sqlParam = new SqlParameter[1];
try
{
BusinessEntities.RaveHRCollection raveHRCollection = new BusinessEntities.RaveHRCollection();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.Category, SqlDbType.Int);
sqlParam[0].Value = categoryId;
objReader = objDA.ExecuteReaderSP(SPNames.Master_GetMasterData, sqlParam);
KeyValue <string> keyValue = new KeyValue <string>();
while (objReader.Read())
{
if (objReader.GetValue(1).ToString() != "Abort")
{
if (Convert.ToString(objReader.GetValue(1)) == key)
{
keyValue.KeyName = objReader.GetValue(0).ToString();
keyValue.Val = objReader.GetValue(1).ToString();
break;
}
}
}
return(keyValue);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
}
objDA.CloseConncetion();
}
}
public int SaveAssessmentPaper(AssessmentPaperModel assessmentPaperModel)
{
int saveStatus = 0;
int assessmentPaperId = 0;
SqlConnection objConnection = null;
SqlCommand objCommand = null;
string ConnStr = string.Empty;
try
{
ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
Master master = new Master();
objCommand = new SqlCommand(SPNames.AssessmentPaperSave, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParam = new SqlParameter[7];
sqlParam[0] = objCommand.Parameters.AddWithValue(SPParameter.CourseId, assessmentPaperModel.CourseId);
sqlParam[1] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentDate, assessmentPaperModel.AssessmentDate);
sqlParam[2] = objCommand.Parameters.AddWithValue(SPParameter.TimeDuration, assessmentPaperModel.TimeDuration);
sqlParam[3] = objCommand.Parameters.AddWithValue(SPParameter.CreatedById, master.GetEmployeeIDByEmailID());
sqlParam[4] = objCommand.Parameters.AddWithValue(SPParameter.IsActive, assessmentPaperModel.IsActive);
sqlParam[5] = objCommand.Parameters.AddWithValue(SPParameter.Status, 0);
sqlParam[6] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentPaperId, 0);
sqlParam[5].Direction = ParameterDirection.Output;
sqlParam[6].Direction = ParameterDirection.Output;
objCommand.ExecuteNonQuery();
saveStatus = Convert.ToInt32(sqlParam[5].Value);
assessmentPaperId = Convert.ToInt32(sqlParam[6].Value);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, "SaveAssessmentPaper", "SaveAssessmentPaper", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
return(assessmentPaperId);
}
public static List <SelectListItem> GetMasterCategoryList(string CategoryName, bool defaultValue, string[] selItems)
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.TNI_GetMasterSP, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("@Category", CategoryName);
objReader = objCommand.ExecuteReader();
SelectListItem selListItem;
List <SelectListItem> newList = new List <SelectListItem>();
if (defaultValue)
{
newList.Add(new SelectListItem {
Selected = true, Text = "Select", Value = "0"
});
}
while (objReader.Read())
{
selListItem = new SelectListItem()
{
Value = objReader[0].ToString().Trim(), Text = objReader[1].ToString().Trim(), Selected = selItems.Contains(Convert.ToString(objReader[0]).Trim()) ? true : false
};
newList.Add(selListItem);
}
return(newList);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsCommonRepository, "GetMasterCategoryList", RMS.Common.Constants.EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
public string AccessForTrainingModule(int UserEmpId)
{
SqlDataReader objReader;
DataAccessClass objDA;
try
{
objDA = new DataAccessClass();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[3];
sqlParam[0] = new SqlParameter(SPParameter.UserEmpID, SqlDbType.VarChar, 100);
if (String.IsNullOrWhiteSpace(Convert.ToString(UserEmpId)))
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = Convert.ToString(UserEmpId);
}
sqlParam[1] = new SqlParameter(SPParameter.RoleId, SqlDbType.VarChar, 10);
sqlParam[1].Value = "";
sqlParam[1].Direction = ParameterDirection.Output;
sqlParam[2] = new SqlParameter(SPParameter.RoleName, SqlDbType.VarChar, 50);
sqlParam[2].Value = "";
sqlParam[2].Direction = ParameterDirection.Output;
//objCommand.ExecuteNonQuery();
objReader = objDA.ExecuteReaderSP(SPNames.TNI_AccessRightForTrainingModule, sqlParam);
return(Convert.ToString(sqlParam[2].Value));
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
new RaveHRException(ex.Message, ex, Sources.CommonLayer, "AccessForTrainingModule", "AccessForTrainingModule", EventIDConstants.AUTHORIZATION_MANAGER_ERROR);
}
return(string.Empty);
}