public List<DataFilterValueEntity> GetDataFilterValuesByDataFilter(string dataFilter, string functionID)
{
// 1. Get available user data filter values from database.
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_VALUES_GET_BY_FUNCTION");
helper.AssignParameterValues(
command,
ExtendedMembership.ApplicationName,
AppContext.Current.UserName,
dataFilter,
functionID
);
helper.Fill(dt, command);
// 2. Fill available data filter values into collcection (List<DataFilterValues>)
List<DataFilterValueEntity> availableDataFilterValues = new List<DataFilterValueEntity>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
availableDataFilterValues.Add(
new DataFilterValueEntity(
functionID,
row["DATA_FILTER"].ToString(),
row["DATA_FILTER_VALUE"].ToString(),
row["DATA_FILTER_ID"].ToString(),
row["DATA_FILTER_VALUE_ID"].ToString()
));
}
}
return availableDataFilterValues;
}
public OfficeDetailEntity GetOfficeDetails(string OrganisationalUnitID)
{
OfficeDetailEntity rOfficeDetailEntity = new OfficeDetailEntity();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_OrganisationalUnitDetail_S");
helper.AssignParameterValues(command, OrganisationalUnitID);
Helper.Fill(dt, command);
if (dt.Rows.Count > 0)
{
rOfficeDetailEntity.OrganisationalUnitDetailsID = int.Parse(dt.Rows[0]["OrganisationalUnitDetailID"].ToString());
rOfficeDetailEntity.OrganisationalUnitID = dt.Rows[0]["OrganisationalUnitID"].ToString();
rOfficeDetailEntity.OrganisationalUnitName = dt.Rows[0]["OrganisationalUnitName"].ToString();
rOfficeDetailEntity.Address1 = dt.Rows[0]["Address1"].ToString();
rOfficeDetailEntity.Address2 = dt.Rows[0]["Address2"].ToString();
rOfficeDetailEntity.Address3 = dt.Rows[0]["Address3"].ToString();
rOfficeDetailEntity.Postcode = dt.Rows[0]["Postcode"].ToString();
rOfficeDetailEntity.Suburb = dt.Rows[0]["Suburb"].ToString();
rOfficeDetailEntity.State = dt.Rows[0]["State"].ToString();
rOfficeDetailEntity.CountryNameCode = dt.Rows[0]["CountryNameCode"].ToString();
rOfficeDetailEntity.PostalDeliveryNumber = dt.Rows[0]["PostalDeliveryNumber"].ToString();
rOfficeDetailEntity.StreetNumber1 = dt.Rows[0]["StreetNumber1"].ToString();
rOfficeDetailEntity.StreetNumber2 = dt.Rows[0]["StreetNumber2"].ToString();
rOfficeDetailEntity.StreetName = dt.Rows[0]["StreetName"].ToString();
rOfficeDetailEntity.LevelNumber = dt.Rows[0]["LevelNumber"].ToString();
rOfficeDetailEntity.UnitNumber = dt.Rows[0]["UnitNumber"].ToString();
rOfficeDetailEntity.PhoneNumber = dt.Rows[0]["PhoneNumber"].ToString();
rOfficeDetailEntity.FaxNumber = dt.Rows[0]["FaxNumber"].ToString();
rOfficeDetailEntity.EmailAddress = dt.Rows[0]["EmailAddress"].ToString();
rOfficeDetailEntity.PhoneNumberTollFree = dt.Rows[0]["PhoneNumberTollFree"].ToString();
}
return rOfficeDetailEntity;
}
public SessionData CreateOrRenewSession(string sessionId,string userName, string fullName, string ipAddress, string hostName)
{
int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout;
DbHelper dbHelper = new DbHelper();
DbCommand command = dbHelper.BuildDbCommand("P_IC_SESSIONS_CREATE_OR_RENEW");
dbHelper.AssignParameterValues(command, sessionId, userName, fullName, DateTime.Now,
DateTime.Now, ipAddress, hostName, timeoutMinutes, DateTime.Now);
dbHelper.ExecuteNonQuery(command);
TimeSpan refreshInterval = new TimeSpan(0, 0, (int)(SessionSettings.GetServiceSetting().RefreshInterval * 60));
TimeSpan timeoutInterval = new TimeSpan(0, 0, (SessionSettings.GetServiceSetting().SessionTimeout * 60));
return new SessionData { SessionID = sessionId, RefreshInterval = refreshInterval, SessionTimeoutInterval = timeoutInterval };
}
public SessionData CheckSessionID(string sessionID, string userName)
{
int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout;
DbHelper dbHelper = new DbHelper();
DbCommand comannd = dbHelper.BuildDbCommand("P_IC_SESSIONS_CHECK_USER_NAME");
dbHelper.AssignParameterValues(comannd, sessionID, userName, timeoutMinutes,DateTime.Now);
dbHelper.ExecuteNonQuery(comannd);
SessionData sessionData = new SessionData();
sessionData.IsKilled = (bool)dbHelper.GetParameterValue(comannd, "p_is_killed");
sessionData.IsSessionMatched = (bool)dbHelper.GetParameterValue(comannd, "p_user_session_matched");
sessionData.IsTimeoutOrInvalid = (bool)dbHelper.GetParameterValue(comannd, "p_is_timeout");
return sessionData;
}
public List<string> GetActionsForUser(string userName)
{
string AppName = NCS.IConnect.Security.BusinessActions.ApplicationName;
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_ACTIONS_GET_FOR_USER");
helper.AssignParameterValues(
command,
AppName,
userName
);
helper.Fill(dt, command);
var list = new List<string>();
foreach (DataRow row in dt.Rows)
{
list.Add(row["ACTION_CODE"].ToString());
}
return list;
}
public List<DelegationValueEntity> FindDelegationValueEntity(DelegationSearchCriteria objDelegationSearchCriteria)
{
int showActive = objDelegationSearchCriteria.IsShowActive ?1:0;
List<DelegationValueEntity> lstDelegationValueEntity = new List<DelegationValueEntity>();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_SEARCH");
helper.AssignParameterValues(
command,
objDelegationSearchCriteria.DelegationFunctionId,
objDelegationSearchCriteria.AdminNotes,
showActive
);
Helper.Fill(dt, command);
foreach (DataRow dr in dt.Rows)
{
DelegationValueEntity objDelegationValueEntity = new DelegationValueEntity();
objDelegationValueEntity.DelegationValueId = Convert.ToInt32(dr["DelegationValueID"].ToString());
objDelegationValueEntity.DelegationFunctionId = Convert.ToInt32(dr["DelegationFunctionID"].ToString());
objDelegationValueEntity.DelegationFunctionName = dr["DelegationName"].ToString();
objDelegationValueEntity.Branch = dr["Branch"].ToString();
objDelegationValueEntity.Unit = dr["Unit"].ToString();
objDelegationValueEntity.Subunit = dr["Subunit"].ToString();
objDelegationValueEntity.Grade = dr["Grade"].ToString();
objDelegationValueEntity.Act = dr["Act"].ToString();
objDelegationValueEntity.DelegationValue = dr["DelegationValue"].ToString();
objDelegationValueEntity.OperationalCondition = dr["OperationalConditions"].ToString();
objDelegationValueEntity.PolicyValue = dr["PolicyValue"].ToString();
objDelegationValueEntity.DelegationReference = dr["DelegationReference"].ToString();
objDelegationValueEntity.StartDate = Convert.ToDateTime(dr["EffectiveStartDate"].ToString());
objDelegationValueEntity.EndDate = Convert.ToDateTime(dr["EffectiveEndDate"].ToString());
lstDelegationValueEntity.Add(objDelegationValueEntity);
}
return lstDelegationValueEntity;
}
public List<ETRoleEntity> GetETRolesForUser(string UserName)
{
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_GISUserRole_S");
helper.AssignParameterValues(command, UserName);
Helper.Fill(dt, command);
List<ETRoleEntity> dtet = new List<ETRoleEntity>();
ETRoleEntity ET;
foreach (DataRow dr in dt.Rows)
{
ET = new ETRoleEntity();
ET.RoleName = dr["RoleName"].ToString();
ET.UserId = dr["UserName"].ToString();
dtet.Add(ET);
}
return dtet;
}
public List<DataFilterEntity> GetDataFiltersForUser(string userName, string roleName, bool ignoreSameFilterValue)
{
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_VALUES_GET_FOR_USER");
helper.AssignParameterValues(
command,
Membership.ApplicationName,
userName,
roleName
);
Helper.Fill(dt, command);
List<DataFilterEntity> dataFilterEntities = new List<DataFilterEntity>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
var filterID = row["DATA_FILTER_ID"];
var valueID = row["DATA_FILTER_VALUE_ID"];
if (!ignoreSameFilterValue
|| dataFilterEntities
.Where(d => d.DataFilterID.Equals(filterID.ToString())
&& d.DataFilterValueID.Equals(valueID.ToString())).Count() == 0)
{
dataFilterEntities.Add(
new DataFilterEntity(
row["USER_DATA_FILTER_VALUE_ID"].ToString(),
userName,
string.Empty,
row["ROLE_NAME"].ToString(),
row["DATA_FILTER_ID"].ToString(),
string.Empty,
row["DATA_FILTER_VALUE_ID"].ToString(),
string.Empty,
DataFilterRecordStatus.Original
)
);
}
}
}
return dataFilterEntities;
}
public DelegationValueEntity SearchDelegationValueByID(int DelegationValueID)
{
DelegationValueEntity objDelegationValueEntity = new DelegationValueEntity();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_S_ByDelegationValueID");
helper.AssignParameterValues(
command,
DelegationValueID
);
Helper.Fill(dt, command);
foreach (DataRow dr in dt.Rows)
{
objDelegationValueEntity.DelegationValueId = Convert.ToInt32(dr["DelegationValueID"].ToString());
objDelegationValueEntity.DelegationFunctionId = Convert.ToInt32(dr["DelegationFunctionID"].ToString());
objDelegationValueEntity.DelegationFunctionName = dr["DelegationName"].ToString();
objDelegationValueEntity.Branch = dr["Branch"].ToString();
objDelegationValueEntity.Unit = dr["Unit"].ToString();
objDelegationValueEntity.Subunit = dr["Subunit"].ToString();
objDelegationValueEntity.Grade = dr["Grade"].ToString();
objDelegationValueEntity.Act = dr["Act"].ToString();
objDelegationValueEntity.DelegationValue = dr["DelegationValue"].ToString();
objDelegationValueEntity.PolicyValue = dr["PolicyValue"].ToString();
objDelegationValueEntity.DelegationReference = dr["DelegationReference"].ToString();
objDelegationValueEntity.OperationalCondition = dr["OperationalConditions"].ToString();
objDelegationValueEntity.StartDate = Convert.ToDateTime(dr["EffectiveStartDate"].ToString());
objDelegationValueEntity.EndDate = Convert.ToDateTime(dr["EffectiveEndDate"].ToString());
objDelegationValueEntity.VersionNo = Convert.ToInt32(dr["VersionNo"].ToString());
}
return objDelegationValueEntity;
}
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationalUnitDetailByOrgID(string OrganisationalUnitID)
{
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetailByOrgID_S");
helper.AssignParameterValues(command, OrganisationalUnitID);
Helper.Fill(LookupOrganisationalUnitDataTable, command);
return LookupOrganisationalUnitDataTable;
}
private void SaveOrganisationLookupDetail(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail, int? AddressID)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = null;
if (dtOrgDetail.Rows.Count > 0)
command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_U");
else
command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_I");
helper.AssignParameterValues(command,
Membership.ApplicationName,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID,
AddressID,
dtLookupOrganisationalUnitDataTable[0].Phone,
dtLookupOrganisationalUnitDataTable[0].Fax,
dtLookupOrganisationalUnitDataTable[0].Mobile,
dtLookupOrganisationalUnitDataTable[0].TollFreePhone,
dtLookupOrganisationalUnitDataTable[0].Email,
1,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now);
Helper.ExecuteNonQuery(command);
}
public void SaveOrganisationLookup(string OrganisationalUnitID, string OrganisationName, int VersionNumber)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U");
helper.AssignParameterValues(command, Membership.ApplicationName, OrganisationalUnitID, OrganisationName, VersionNumber, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now );
Helper.ExecuteNonQuery(command);
}
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationLookupByOrgID(string OrganisationalUnitID)
{
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnitByOrgID_S");
helper.AssignParameterValues(command, OrganisationalUnitID);
Helper.Fill(LookupOrganisationalUnitDataTable, command);
#region Get organisation info and address details
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail = GetOrganisationalUnitDetailByOrgID(OrganisationalUnitID);
if (dtOrgDetail.Rows.Count > 0)
{
LookupOrganisationalUnitDataTable[0].UnitNumber = dtOrgDetail[0].UnitNumber;
LookupOrganisationalUnitDataTable[0].StreetNumber = dtOrgDetail[0].StreetNumber;
LookupOrganisationalUnitDataTable[0].StreetNumber = dtOrgDetail[0].StreetNumber;
LookupOrganisationalUnitDataTable[0].Street = dtOrgDetail[0].Street;
LookupOrganisationalUnitDataTable[0].Type = dtOrgDetail[0].Type;
LookupOrganisationalUnitDataTable[0].Suffix = dtOrgDetail[0].Suffix;
LookupOrganisationalUnitDataTable[0].Suburb = dtOrgDetail[0].Suburb;
LookupOrganisationalUnitDataTable[0].PostCode = dtOrgDetail[0].PostCode;
LookupOrganisationalUnitDataTable[0].State = dtOrgDetail[0].State;
LookupOrganisationalUnitDataTable[0].Mobile = dtOrgDetail[0].Mobile;
LookupOrganisationalUnitDataTable[0].Phone = dtOrgDetail[0].Phone;
LookupOrganisationalUnitDataTable[0].TollFreePhone = dtOrgDetail[0].TollFreePhone;
LookupOrganisationalUnitDataTable[0].Fax = dtOrgDetail[0].Fax;
LookupOrganisationalUnitDataTable[0].Email = dtOrgDetail[0].Email;
}
#endregion
return LookupOrganisationalUnitDataTable;
}
public List<DataFilterEntity> GetUserDataFilterValuesByUserNameAndRoleName(string userName, string roleName)
{
List<DataFilterEntity> dataFilterEntities = new List<DataFilterEntity>();
if (!String.IsNullOrEmpty(userName)) // username is not allowed setting "Null" or "Empty"
{
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_USERS_DATA_FILTER_VALUES_GET_BY_USER_NAME_AND_ROLE_NAME");
helper.AssignParameterValues(
command,
Membership.ApplicationName,
userName,
roleName
);
Helper.Fill(dt, command);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
dataFilterEntities.Add(
new DataFilterEntity(
row["USER_DATA_FILTER_VALUE_ID"].ToString(),
row["USER_NAME"].ToString(),
row["ROLE_ID"].ToString(),
row["ROLE_NAME"].ToString(),
row["DATA_FILTER_ID"].ToString(),
row["DATA_FILTER"].ToString(),
row["DATA_FILTER_VALUE_ID"].ToString(),
row["DATA_FILTER_VALUE"].ToString(),
OfficeRecordStatus.Original
)
);
}
}
}
return dataFilterEntities;
}
public string[] GetUsersWithFunctionAndDataFilter(string functionId, string dataFilterId,string dataFilterValueId)
{
// 1. Get available users from database.
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_VALUES_GET_USERS_BY_FUNCTION");
helper.AssignParameterValues(
command,
ExtendedMembership.ApplicationName,
functionId,
dataFilterId,
dataFilterValueId
);
helper.Fill(dt, command);
// 2. Fill available username values into collcection (List<string>)
List<string> userNames = new List<string>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
userNames.Add(row["USER_NAME"].ToString() );
}
}
return userNames.ToArray();
}
public void UpdateDelegationValue(DelegationValueEntity objDelegationValueEntity)
{
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_U");
helper.AssignParameterValues(
command,
objDelegationValueEntity.DelegationValueId,
objDelegationValueEntity.DelegationFunctionId,
objDelegationValueEntity.Branch,
objDelegationValueEntity.Unit,
objDelegationValueEntity.Subunit,
objDelegationValueEntity.Grade,
objDelegationValueEntity.Act,
objDelegationValueEntity.DelegationValue,
objDelegationValueEntity.PolicyValue,
objDelegationValueEntity.OperationalCondition,
objDelegationValueEntity.DelegationReference,
objDelegationValueEntity.StartDate,
objDelegationValueEntity.EndDate,
objDelegationValueEntity.VersionNo,
NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId,
AppContext.Current.UserName
);
Helper.ExecuteNonQuery(command);
}
public List<DelegationValueEntity> GetAllDelegationValueEntity()
{
List<DelegationValueEntity> lstDelegationValueEntity = new List<DelegationValueEntity>();
DataTable dtDelegationValueEntity = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_S");
helper.AssignParameterValues(command);
Helper.Fill(dtDelegationValueEntity, command);
foreach (DataRow dr in dtDelegationValueEntity.Rows)
{
DelegationValueEntity objDelegationValueEntity = new DelegationValueEntity();
objDelegationValueEntity.DelegationValueId = Convert.ToInt32(dr["DelegationValueID"].ToString());
objDelegationValueEntity.DelegationFunctionId = Convert.ToInt32(dr["DelegationFunctionID"].ToString());
objDelegationValueEntity.DelegationFunctionName = dr["DelegationName"].ToString();
objDelegationValueEntity.Branch = dr["Branch"].ToString();
objDelegationValueEntity.Unit = dr["Unit"].ToString();
objDelegationValueEntity.Subunit = dr["Subunit"].ToString();
objDelegationValueEntity.Grade = dr["Grade"].ToString();
objDelegationValueEntity.Act = dr["Act"].ToString();
objDelegationValueEntity.DelegationValue = dr["DelegationValue"].ToString();
objDelegationValueEntity.PolicyValue = dr["PolicyValue"].ToString();
objDelegationValueEntity.DelegationReference = dr["DelegationReference"].ToString();
objDelegationValueEntity.StartDate = Convert.ToDateTime(dr["EffectiveStartDate"].ToString());
objDelegationValueEntity.EndDate = Convert.ToDateTime(dr["EffectiveEndDate"].ToString());
lstDelegationValueEntity.Add(objDelegationValueEntity);
}
return lstDelegationValueEntity;
}
public List<DelegationFunctionEntity> GetAllDelegationFunctions()
{
List<DelegationFunctionEntity> lstDelegationFunctionEntity = new List<DelegationFunctionEntity>();
DataTable dtDelegationFunction = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationFunction_S");
helper.AssignParameterValues(command);
Helper.Fill(dtDelegationFunction, command);
foreach (DataRow dr in dtDelegationFunction.Rows)
{
lstDelegationFunctionEntity.Add(new DelegationFunctionEntity(Convert.ToInt32(dr["DelegationFunctionID"].ToString()), dr["Name"].ToString(), dr["Description"].ToString()));
}
return lstDelegationFunctionEntity;
}
public UserInfoEntity[] GetUserInRoleByRoleName(string roleName)
{
if (string.IsNullOrEmpty(roleName)) return new UserInfoEntity[] { };
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_USER_IN_ROLE_SEARCH");
helper.AssignParameterValues(
command,
roleName
);
Helper.Fill(dt, command);
Dictionary<string, UserInfoEntity> arrayList = new Dictionary<string, UserInfoEntity>();
UserInfoEntity userInfo ;
foreach (DataRow row in dt.Rows)
{
userInfo = new UserInfoEntity();
string userId = row["USER_ID"].ToString();
userInfo.UserName = row["USER_NAME"].ToString();
userInfo.FirstName = row["FIRST_NAME"].ToString();
userInfo.Initials = row["INITIALS"].GetType() == typeof(System.DBNull) ? String.Empty : row["INITIALS"].ToString();
userInfo.LastName = row["LAST_NAME"].ToString();
userInfo.Display = row["DISPLAY"].ToString();
userInfo.Alias = row["ALIAS"].GetType() == typeof(System.DBNull) ? String.Empty : row["ALIAS"].ToString();
userInfo.Gender = row["GENDER"].ToString();
userInfo.Title = row["TITLE"].ToString();
userInfo.DateOfBirth = row["DATE_OF_BIRTH"].GetType() == typeof(System.DBNull) ? MinMaxValues.MinDate : Convert.ToDateTime(row["DATE_OF_BIRTH"]);
userInfo.Email = row["EMAIL"].ToString();
userInfo.TelephoneNo = row["TELEPHONE_NO"].ToString();
userInfo.FaxNo = row["FAX_NO"].GetType() == typeof(System.DBNull) ? String.Empty : row["FAX_NO"].ToString();
userInfo.MobileNo = row["MOBILE_NO"].GetType() == typeof(System.DBNull) ? String.Empty : row["MOBILE_NO"].ToString();
userInfo.PageNo = row["PAGER_NO"].GetType() == typeof(System.DBNull) ? String.Empty : row["PAGER_NO"].ToString();
//UIE.Organisation = row["ORGANISATION"].ToString();
userInfo.Remarks = row["REMARKS"].GetType() == typeof(System.DBNull) ? String.Empty : row["REMARKS"].ToString();
userInfo.UserStatus = row["STATUS"].ToString();
userInfo.CreatedOn = (DateTime)row["CREATED_TIME"];
userInfo.Office = row["OFFICE"].GetType() == typeof(DBNull) ? String.Empty : row["OFFICE"].ToString();
arrayList.Add(userId, userInfo);
}
dt.Dispose();
return arrayList.Values.ToArray<UserInfoEntity>();
}
/// <summary>
/// Add selected data filter value
/// </summary>
/// <param name="dataFilterEntity">dataFilterEntity</param>
private void CreateDataFilterValue(DataFilterEntity dataFilterEntity)
{
if (CheckDataFilterEntity(dataFilterEntity))
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_USERS_DATA_FILTER_VALUES_INSERT");
helper.AssignParameterValues(
command,
Membership.ApplicationName,
dataFilterEntity.UserDataFilterValueID,
dataFilterEntity.UserName,
dataFilterEntity.RoleName,
dataFilterEntity.DataFilterID,
dataFilterEntity.DataFilterValueID,
DateTime.Now,
transactionid,
AppContext.Current.UserName
);
Helper.ExecuteNonQuery(command);
}
}
public List<string> GetFilterActionsByDependencyType(List<string> allUserActions, int dependencyType)
{
var dt = new AuthorizationDataSet.ActionDependenciesDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_ACTIONS_GET_DEPENDENCIES_BY_TYPE");
helper.AssignParameterValues(
command,
dependencyType //dependency actions
);
helper.Fill(dt, command);
List<string> availableDependencyUserActions = new List<string>();
if (dt.Rows.Count > 0)
{
var actions = from a in allUserActions
join b in dt on a equals b.ACTION_CODE
select a;
// foreach (DataRow row in dt.Rows)
foreach (string explicitAction in actions)
{
// FindDependencyActionInDepth(ref availableDependencyUserActions, allUserActions, row["ACTION_CODE"].ToString(), dt);
FindDependencyActionInDepth(ref availableDependencyUserActions, allUserActions, explicitAction, dt);
}
}
return availableDependencyUserActions.Distinct().ToList();
}
/// <summary>
/// Delete selected data filter value
/// </summary>
/// <param name="dataFilterEntity">dataFilterEntity</param>
private void DeleteDataFilterValue(DataFilterEntity dataFilterEntity)
{
if (CheckDataFilterEntity(dataFilterEntity))
{
//NOTE:No transaction id
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_USERS_DATA_FILTER_VALUES_DELETE");
helper.AssignParameterValues(
command,
Membership.ApplicationName,
dataFilterEntity.UserDataFilterValueID
);
Helper.ExecuteNonQuery(command);
}
}
public void SaveOrganisationLookup(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U");
helper.AssignParameterValues(command,
Membership.ApplicationName,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitName,
dtLookupOrganisationalUnitDataTable[0].VersionNo,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now);
Helper.ExecuteNonQuery(command);
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail = GetOrganisationalUnitDetailByOrgID(dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID);
int? addressId = SaveAddress(dtLookupOrganisationalUnitDataTable,dtOrgDetail);
SaveOrganisationLookupDetail(dtLookupOrganisationalUnitDataTable,dtOrgDetail,addressId);
}
/// <summary>
/// Gets the authorised data filters.
/// </summary>
/// <param name="roleName">The rolename.</param>
/// <returns>The authorised data filters.</returns>
private List<string> GetAuthorisedDataFilters(string roleName)
{
List<string> authorisedDataFilter = new List<string>();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_AUTHORISATION");
helper.AssignParameterValues(
command,
Membership.ApplicationName,
roleName
);
Helper.Fill(dt, command);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
authorisedDataFilter.Add(row["DATA_FILTER_ID"].ToString());
}
}
return authorisedDataFilter;
}
private int? SaveAddress(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail)
{
int? retaddressID = null;
int versionAddressId = 1;
var row = dtLookupOrganisationalUnitDataTable[0];
if (string.IsNullOrEmpty(row.State)
&& string.IsNullOrEmpty(row.UnitNumber)
&& string.IsNullOrEmpty(row.StreetNumber)
&& string.IsNullOrEmpty(row.Street)
&& string.IsNullOrEmpty(row.Type)
&& string.IsNullOrEmpty(row.Suffix)
&& string.IsNullOrEmpty(row.Suburb)
&& string.IsNullOrEmpty(row.PostCode))
{
return retaddressID;
}
//If need to add address, State cannot be empty
if (string.IsNullOrEmpty(row.State))
{
throw new ArgumentException("State is mandatory when to add address.");
}
string address1 = string.Concat(row.UnitNumber,
string.IsNullOrEmpty(row.UnitNumber) &&
string.IsNullOrEmpty(row.StreetNumber)
? string.Empty
: "/", row.StreetNumber, " ",
row.Street, " ",
row.Type).Trim();
if(string.IsNullOrEmpty(address1) == false)
{
if(address1.EndsWith("/"))
{
address1 = address1.Substring(0, address1.Length - 1);
}
if(address1.StartsWith("/"))
{
address1 = address1.Substring(1, address1.Length - 1);
}
}
if (dtOrgDetail.Rows.Count > 0)
{
if (string.IsNullOrEmpty(dtOrgDetail[0].AddressID))
{
retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId);
}
else
{
versionAddressId = GetAddressVersion(Convert.ToInt32(dtOrgDetail[0].AddressID));
retaddressID = Convert.ToInt32(dtOrgDetail[0].AddressID.ToString());
}
}
else
retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId);
if (retaddressID == null)
{
string transactionid =
NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_Address_I");
helper.AssignParameterValues(command,
null,
null,
string.IsNullOrEmpty(row.UnitNumber)?null:row.UnitNumber,
null,
null,
string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber,
null,
string.IsNullOrEmpty(row.Street) ? null : row.Street,
string.IsNullOrEmpty(row.Type) ? null : row.Type,
string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix,
null,
null,
string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb,
string.IsNullOrEmpty(row.State) ? null : row.State,
string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode,
string.IsNullOrEmpty(address1.Trim()) ? null : address1.Trim(),
null,
null,
null,
null,
null,
0,
null,
null,
null,
null,
1101,
null,
0,
versionAddressId,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now,
AppContext.Current.UserName.ToString(),
DateTime.Now,
null);
object objAddID = null;
Helper.ExecuteNonQuery(command);
objAddID = command.Parameters["@addressid"].Value;
return (int) objAddID;
}
else
{
string transactionid =
NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_Address_U");
helper.AssignParameterValues(command,
retaddressID,
null,
null,
string.IsNullOrEmpty(row.UnitNumber) ? null : row.UnitNumber,
null,
null,
string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber,
null,
string.IsNullOrEmpty(row.Street) ? null : row.Street,
string.IsNullOrEmpty(row.Type) ? null : row.Type,
string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix,
null,
null,
string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb,
string.IsNullOrEmpty(row.State) ? null : row.State,
string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode,
string.IsNullOrEmpty(address1) ? null : address1,
null,
null,
null,
null,
null,
0,
null,
null,
null,
null,
1101,
null,
0,
versionAddressId,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now,
AppContext.Current.UserName.ToString(),
DateTime.Now,
null);
Helper.ExecuteNonQuery(command);
}
return retaddressID;
}
public void DeleteUsersETRole(ETRoleEntity etRoleEntity)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_GISUserRole_D");
helper.AssignParameterValues(command, AppContext.Current.UserName.ToString(), Membership.ApplicationName, transactionid, etRoleEntity.UserId, etRoleEntity.RoleName);
Helper.ExecuteNonQuery(command);
}
public UserInfoEntity[] FindUsers(UserInfoSearchCriteria userInfoSearchCriteria)
{
if (userInfoSearchCriteria == null) return new UserInfoEntity[] { };
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_USER_INFO_SEARCH");
var appName = NCS.IConnect.Security.ExtendedMembership.ApplicationName;
helper.AssignParameterValues(
command,
SearchHelper.TranslateWildcard(userInfoSearchCriteria.UserName),
SearchHelper.TranslateWildcard(userInfoSearchCriteria.UserStatus),
userInfoSearchCriteria.CreatedFrom,
userInfoSearchCriteria.CreatedTo,
SearchHelper.TranslateWildcard(userInfoSearchCriteria.Display),
SearchHelper.TranslateWildcard(userInfoSearchCriteria.Email),
userInfoSearchCriteria.UserType,
SearchHelper.TranslateWildcard(userInfoSearchCriteria.Office),
appName
);
Helper.Fill(dt, command);
Dictionary<string, UserInfoEntity> arrayList = new Dictionary<string, UserInfoEntity>();
const string Yes = "Y";
DataRow[] matchedRecords = dt.Select(string.Format("IS_MASTER='{0}'", Yes));
//1. add the records that is the default office
foreach (DataRow row in matchedRecords)
{
string userId = row["USER_ID"].ToString();
//if (!(string.IsNullOrEmpty(userInfoSearchCriteria.Office)
// || SearchHelper.IsRegexMatch(row["DEFAULT_OFFICE"].ToString(), userInfoSearchCriteria.Office, @"\w|\W")))
//{
// //Not match office criteria
// continue;
//}
if (arrayList.ContainsKey(userId))
{
//Duplicated
continue;
}
arrayList.Add(userId, CreatEntity(row));
}
//2.Union the records that have no default office
matchedRecords = dt.Select(string.Format("IS_MASTER<>'{0}'",Yes));
foreach (DataRow row in matchedRecords)
{
string userId = row["USER_ID"].ToString();
//if (!(string.IsNullOrEmpty(userInfoSearchCriteria.Office)
// || SearchHelper.IsRegexMatch(row[""].ToString(), userInfoSearchCriteria.Office, @"\w|\W")))
//{
// //Not match office criteria
// continue;
//}
if (arrayList.ContainsKey(userId))
{
//Duplicated or has default office records.
continue;
}
UserInfoEntity entity = CreatEntity(row);
entity.Office = string.Empty;
arrayList.Add(userId, entity);
}
//Compine all offices for every user
foreach (KeyValuePair<string,UserInfoEntity> user in arrayList)
{
string userId = user.Key;
matchedRecords = dt.Select(string.Format("USER_ID='{0}'", userId));
StringBuilder allOffices = new StringBuilder();
foreach (var row in matchedRecords)
{
allOffices.Append(row["DEFAULT_OFFICE"].ToString());
allOffices.Append(",");
}
user.Value.AllOffices = allOffices.ToString().TrimEnd(',');
}
dt.Dispose();
return arrayList.Values.ToArray<UserInfoEntity>();
}
public FunctionAndDataFilterEntity[] GetActionCodeAndDFListByRoleName(string roleName)
{
ArrayList arrayList = new ArrayList();
if (!String.IsNullOrEmpty(roleName))
{
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_ACTION_CODE_DF_BY_ROLE_NAME");
helper.AssignParameterValues(
command,
roleName
);
Helper.Fill(dt, command);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
arrayList.Add(new FunctionAndDataFilterEntity(
row["ACTION_CODE"].ToString(),
row["DATA_FILTER"].ToString()
)
);
}
}
}
return (FunctionAndDataFilterEntity[])arrayList.ToArray(typeof(FunctionAndDataFilterEntity));
}
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationLookup()
{
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_S");
helper.AssignParameterValues(command);
Helper.Fill(LookupOrganisationalUnitDataTable, command);
return LookupOrganisationalUnitDataTable;
}
public OfficesHierarchyDataSet.LookupOrganisationalUnitHierarchyDataTable GetOrganisationalUnitHierarchyByParentID(int ParentId)
{
OfficesHierarchyDataSet.LookupOrganisationalUnitHierarchyDataTable LookupOrganisationalUnitHierarchyDataTable = new OfficesHierarchyDataSet.LookupOrganisationalUnitHierarchyDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnitHierarchy_S_ByParentID");
helper.AssignParameterValues(command, ParentId);
Helper.Fill(LookupOrganisationalUnitHierarchyDataTable, command);
return LookupOrganisationalUnitHierarchyDataTable;
}
