/// <summary>
/// Update UserGroup data to Database
/// </summary>
/// <param name="updateDto">
/// object that contains data to update
/// </param>
/// <returns>
/// 0: insert successful
/// others: Sql Exception
/// </returns>
public int UpdateData(IDto updateDto)
{
SYS_UserGroupsDao userGroupDao = new SYS_UserGroupsDao();
SYS_UserGroupsDto dto = (SYS_UserGroupsDto)updateDto;
return(userGroupDao.UpdateData(dto));
}
public int SelectUsersByGroup(IDto selectDto, out DataTable dtUsers)
{
SYS_GroupsAssignDao groupAssignDao = new SYS_GroupsAssignDao();
SYS_UserGroupsDto userGroupsDto = (SYS_UserGroupsDto)selectDto;
return(groupAssignDao.SelectDataByGroup(userGroupsDto, out dtUsers));
}
/// <summary>
/// Insert Permission for UserGroup
/// </summary>
/// <param name="insertDto">
/// object that contains data to insert
/// </param>
/// <returns>
/// 0: insert successful
/// others: Sql Exception
/// </returns>
public int InsertPermisionData(IDto insertDto)
{
SYS_UserGroupsDao userGroupDao = new SYS_UserGroupsDao();
SYS_UserGroupsDto dto = (SYS_UserGroupsDto)insertDto;
return(userGroupDao.InsertPermissionData(dto));
}
public object SelectWarehousesByGroup(IDto selectDto)
{
#region Format outputed Datatable
DataTable dtResult = new DataTable();
dtResult.Columns.Add("Code");
dtResult.Columns.Add("Name");
dtResult.Columns.Add("All", typeof(bool));
#endregion
//Get all data from sy_function table (FunctionID, FunctionName)
DataTable dtWHs = new DataTable();
CommonBl commonBl = new CommonBl();
int returnCode = commonBl.SelectWarehouses(out dtWHs);
//Select warehouse assign data
DataTable dtWHsAssign = new DataTable();
MS_WarehouseAssignDao warehouseAssignDao = new MS_WarehouseAssignDao();
SYS_UserGroupsDto dto = (SYS_UserGroupsDto)selectDto;
warehouseAssignDao.SelectDataByGroupCode(dto.Code, out dtWHsAssign);
////Select function mapping data
//DataTable dtWHsMapping = new DataTable();
//MS_WarehouseMappingDao whMappingDao = new MS_WarehouseMappingDao();
//whMappingDao.SelectMappingInfo(CommonData.StringEmpty, CommonData.StringEmpty, out dtWHsMapping);
////Select permission assign data
//DataTable dtPermissionsAssign = new DataTable();
//SYS_PermissionAssignDao permissionsAssignDao = new SYS_PermissionAssignDao();
//SYS_UserGroupsDto dto = (SYS_UserGroupsDto)selectDto;
//permissionsAssignDao.SelectDataByGroupCode(dto.Code, out dtPermissionsAssign);
//Select function mapping data
//DataTable dtFunctionsMapping = new DataTable();
//SYS_FunctionMappingDao functionMappingDao = new SYS_FunctionMappingDao();
//functionMappingDao.SelectMappingInfo(CommonData.StringEmpty, CommonData.StringEmpty, out dtFunctionsMapping);
foreach (DataRow whRow in dtWHs.Rows)
{
DataRow dr = dtResult.NewRow();
dr["Code"] = whRow["Code"];
dr["Name"] = whRow["Name"];
dr["All"] = false;
//Check assign warehouse on group
DataRow[] whAssignRows = dtWHsAssign.Select("WarehouseCode = '" + whRow["Code"].ParseStrQuery() + "'");
if (whAssignRows.Length > 0)
{
dr["All"] = true;
}
dtResult.Rows.Add(dr);
}
return(dtResult);
}
/// <summary>
/// Search Data in UserGroup table
/// </summary>
/// <param name="iDto">
/// Dto of UserGroup form
/// </param>
/// <param name="dtResult">
/// Out a Datatable that contains search result(all columns) in UserGroup table
/// </param>
/// <returns>
/// 0: Update successful
/// 1: Access denied, login to database fail(invalid username, invalid password)
/// 2: Invalid host, cannot find server(host) that set in app config file
/// 3: Invalid database, cannot find database that set in DbConfig file
/// 4: Lost connection, cannot connect to database because lost connection
/// 5: Duplicate key: insert Primary Key or Unique Key that already exist in database
/// 6: Forgeign key not exist: insert a foreign key that not exist in primary key
/// 7: Foreign Key Violation: Foreign Key Violation (delete primary key that is foreign key in other table)
/// 8: Data not found: Delete or Update data that not exist in database
/// 9: Exception occured: other exception
/// </returns>
public int SearchData(IDto searchDto, out DataTable dtResult)
{
int returnCode = CommonData.DbReturnCode.Succeed;
SYS_UserGroupsDao mSUG00Dao = new SYS_UserGroupsDao();
SYS_UserGroupsDto dto = (SYS_UserGroupsDto)searchDto;
//Select group
returnCode = mSUG00Dao.SelectData(dto, out dtResult);
//Select user
DataTable dtDetail = new DataTable();
dtDetail.Columns.Add(CommonKey.UserCode, typeof(string));
dtDetail.Columns.Add(CommonKey.DisplayName, typeof(string));
dtDetail.Columns.Add(CommonKey.Status, typeof(string));
dtDetail.Columns.Add(CommonKey.GroupCode, typeof(string));
DataTable dtDetailTerm = new DataTable();
foreach (DataRow row in dtResult.Rows)
{
SYS_UserGroupsDto groupDto = new SYS_UserGroupsDto()
{
Code = CommonMethod.ParseString(row[CommonKey.Code]),
};
returnCode = this.SelectUsersByGroup(groupDto, out dtDetailTerm);
if (returnCode != 0)
{
return(returnCode);
}
foreach (DataRow detailRow in dtDetailTerm.Rows)
{
dtDetail.Rows.Add(detailRow[CommonKey.UserCode]
, detailRow[CommonKey.DisplayName]
, detailRow[CommonKey.Status]
, detailRow[CommonKey.GroupCode]
);
}
}
#region Create relationship
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dtResult);
dataSet.Tables.Add(dtDetail);
dataSet.Relations.Add(" ", dataSet.Tables[0].Columns[CommonKey.Code],
dataSet.Tables[1].Columns[CommonKey.GroupCode]);
#endregion
return(returnCode);
}
/// <summary>
/// Update UserGroup
/// </summary>
/// <param name="updateDto">
/// Dto that contains all UserGroup data
/// </param>
/// <returns>
/// 0: insert successful
/// others: Sql Exception
/// </returns>
public int UpdateData(SYS_UserGroupsDto updateDto)
{
int returnCode = CommonData.DbReturnCode.Succeed;
try
{
using (BaseDao context = new BaseDao())
{
ms_usergroups userGroup = context.ms_usergroups.FirstOrDefault(ig => ig.ID.Equals(updateDto.Id));
if (userGroup != null)
{
userGroup.Name1 = updateDto.Name1;
userGroup.Name2 = updateDto.Name2;
userGroup.Name3 = updateDto.Name3;
userGroup.Description = updateDto.Description;
//userGroup.SystemData = CommonData.Status.Disable;
//Create Update information
this.CreateUpdateHistory(userGroup, CommonData.FunctionGr.SY_UserGroups);
//Save to database
//returnCode = context.Saves();
returnCode = context.Saves(CommonData.FunctionGr.SY_UserGroups, CommonData.Action.Update, new { userGroup.Code, userGroup.Name1, userGroup.Name2, userGroup.Name3, userGroup.Description }, typeof(ms_usergroups).Name);
}
else
{
returnCode = CommonData.DbReturnCode.DataNotFound;
}
}
}
catch (Exception ex)
{
returnCode = this.ProcessDbException(ex);
}
return(returnCode);
}
/// <summary>
/// Insert new UserGroup
/// </summary>
/// <param name="insertDto">
/// Dto that contains all UserGroup data
/// </param>
/// <returns>
/// 0: insert successful
/// others: Sql Exception
/// </returns>
public int InsertData(SYS_UserGroupsDto insertDto)
{
int returnCode = CommonData.DbReturnCode.Succeed;
try
{
using (BaseDao context = new BaseDao())
{
ms_usergroups userGroup = new ms_usergroups();
userGroup.Code = CommonMethod.ParseString(insertDto.Code);
userGroup.Name1 = CommonMethod.ParseString(insertDto.Name1);
userGroup.Name2 = CommonMethod.ParseString(insertDto.Name2);
userGroup.Name3 = CommonMethod.ParseString(insertDto.Name3);
userGroup.Description = insertDto.Description;
userGroup.SystemData = CommonData.Status.Disable;
//Create insert information
this.CreateInsertHistory(userGroup, CommonData.FunctionGr.SY_UserGroups);
//Add itemGroup
context.ms_usergroups.AddObject(userGroup);
//Save to database
//returnCode = context.Saves();
returnCode = context.Saves(CommonData.FunctionGr.SY_UserGroups, CommonData.Action.Insert,
new { userGroup.Code, userGroup.Name1, userGroup.Name2, userGroup.Name3, userGroup.Description, userGroup.SystemData },
typeof(ms_usergroups).Name);
}
}
catch (Exception ex)
{
returnCode = this.ProcessDbException(ex);
}
return(returnCode);
}
public object SelectFunctionsByGroup(IDto selectDto)
{
#region Format outputed Datatable
DataTable dtResult = new DataTable();
dtResult.Columns.Add("FunctionID");
dtResult.Columns.Add("FunctionName");
dtResult.Columns.Add("AllMapping", typeof(string));
dtResult.Columns.Add("AllID", typeof(string));
dtResult.Columns.Add("AllName", typeof(string));
dtResult.Columns.Add("All", typeof(bool));
dtResult.Columns.Add("ViewMapping", typeof(string));
dtResult.Columns.Add("ViewID", typeof(string));
dtResult.Columns.Add("ViewName", typeof(string));
dtResult.Columns.Add("View", typeof(bool));
dtResult.Columns.Add("AddMapping", typeof(string));
dtResult.Columns.Add("AddID", typeof(string));
dtResult.Columns.Add("AddName", typeof(string));
dtResult.Columns.Add("Add", typeof(bool));
dtResult.Columns.Add("EditMapping", typeof(string));
dtResult.Columns.Add("EditID", typeof(string));
dtResult.Columns.Add("EditName", typeof(string));
dtResult.Columns.Add("Edit", typeof(bool));
dtResult.Columns.Add("DeleteMapping", typeof(string));
dtResult.Columns.Add("DeleteID", typeof(string));
dtResult.Columns.Add("DeleteName", typeof(string));
dtResult.Columns.Add("Delete", typeof(bool));
dtResult.Columns.Add("PrintMapping", typeof(string));
dtResult.Columns.Add("PrintID", typeof(string));
dtResult.Columns.Add("PrintName", typeof(string));
dtResult.Columns.Add("Print", typeof(bool));
dtResult.Columns.Add("ImportMapping", typeof(string));
dtResult.Columns.Add("ImportID", typeof(string));
dtResult.Columns.Add("ImportName", typeof(string));
dtResult.Columns.Add("Import", typeof(bool));
dtResult.Columns.Add("ExportMapping", typeof(string));
dtResult.Columns.Add("ExportID", typeof(string));
dtResult.Columns.Add("ExportName", typeof(string));
dtResult.Columns.Add("Export", typeof(bool));
dtResult.Columns.Add("Other", typeof(bool));
dtResult.Columns.Add("OtherData", typeof(DataTable));
#endregion
//Get all data from sy_function table (FunctionID, FunctionName)
DataTable dtFunctions = new DataTable();
CommonBl commonBl = new CommonBl();
commonBl.SelectFunctions(out dtFunctions);
//GetHashCode data from ms_permissionsAssign table (GroupCode, MappingID)
//whewe MSUG00UserGroupsDto.Code(selected GroupCode) = ms_permissionsAssign.GroupCode
//Select permission assign data
DataTable dtPermissionsAssign = new DataTable();
SYS_PermissionAssignDao permissionsAssignDao = new SYS_PermissionAssignDao();
SYS_UserGroupsDto dto = (SYS_UserGroupsDto)selectDto;
permissionsAssignDao.SelectDataByGroupCode(dto.Code, out dtPermissionsAssign);
//Select function mapping data
DataTable dtFunctionsMapping = new DataTable();
SYS_FunctionMappingDao functionMappingDao = new SYS_FunctionMappingDao();
functionMappingDao.SelectMappingInfo(CommonData.StringEmpty, CommonData.StringEmpty, out dtFunctionsMapping);
foreach (DataRow FunctionRow in dtFunctions.Rows)
{
DataTable otherData = new DataTable();
otherData.Columns.Add("FunctionID");
otherData.Columns.Add("MappingID");
otherData.Columns.Add("OperID");
otherData.Columns.Add("OperName");
otherData.Columns.Add("IsPermission", typeof(bool));
DataRow dr = dtResult.NewRow();
dr["FunctionID"] = FunctionRow["FunctionID"];
dr["FunctionName"] = FunctionRow["Name"];
dr["All"] = false;
dr["View"] = false;
dr["Add"] = false;
dr["Edit"] = false;
dr["Delete"] = false;
dr["Print"] = false;
dr["Import"] = false;
dr["Export"] = false;
dr["Other"] = false;
DataRow[] FunctionsMappingRows = dtFunctionsMapping.Select("FunctionID =" + "'" + FunctionRow["FunctionID"].ParseStrQuery() + "'");
bool checkOtherColumn = false;
foreach (DataRow FunctionsMappingRow in FunctionsMappingRows)
{
switch (FunctionsMappingRow["OperCode"].ToString())
{
case CommonData.OperId.View:
dr["View"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["ViewMapping"] = FunctionsMappingRow["ID"];
dr["ViewID"] = FunctionsMappingRow["OperCode"];
dr["ViewName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Add:
dr["Add"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["AddMapping"] = FunctionsMappingRow["ID"];
dr["AddID"] = FunctionsMappingRow["OperCode"];
dr["AddName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Edit:
dr["Edit"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["EditMapping"] = FunctionsMappingRow["ID"];
dr["EditID"] = FunctionsMappingRow["OperCode"];
dr["EditName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Delete:
dr["Delete"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["DeleteMapping"] = FunctionsMappingRow["ID"];
dr["DeleteID"] = FunctionsMappingRow["OperCode"];
dr["DeleteName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Import:
dr["Import"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["ImportMapping"] = FunctionsMappingRow["ID"];
dr["ImportID"] = FunctionsMappingRow["OperCode"];
dr["ImportName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Export:
dr["Export"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["ExportMapping"] = FunctionsMappingRow["ID"];
dr["ExportID"] = FunctionsMappingRow["OperCode"];
dr["ExportName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.Print:
dr["Print"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["PrintMapping"] = FunctionsMappingRow["ID"];
dr["PrintID"] = FunctionsMappingRow["OperCode"];
dr["PrintName"] = FunctionsMappingRow["OperName"];
break;
case CommonData.OperId.All:
dr["All"] = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
dr["AllMapping"] = FunctionsMappingRow["ID"];
dr["AllID"] = FunctionsMappingRow["OperCode"];
dr["AllName"] = FunctionsMappingRow["OperName"];
break;
default:
DataRow othRow = otherData.NewRow();
othRow["FunctionID"] = FunctionsMappingRow["FunctionId"];
othRow["MappingID"] = FunctionsMappingRow["ID"];
othRow["OperID"] = FunctionsMappingRow["OperCode"];
othRow["OperName"] = FunctionsMappingRow["OperName"];
bool OthPermission = false;
OthPermission = IsPermission(FunctionRow["FunctionID"].ToString(), FunctionsMappingRow["OperCode"].ToString(), dtPermissionsAssign);
othRow["IsPermission"] = OthPermission;
if (OthPermission == true)
{
checkOtherColumn = true;
}
otherData.Rows.Add(othRow);
break;
}
}
dr["OtherData"] = otherData;
dr["Other"] = checkOtherColumn;
dtResult.Rows.Add(dr);
}
return(dtResult);
}
/// <summary>
/// Search Data in ms_UserGroup table
/// </summary>
/// <param name="selectDto">
/// Dto that contains conditions: Code, Name(VN), Name(EN), Name(JP), Type
/// </param>
/// <param name="dtResult">
/// Out a Datatable that contains search result(all columns) in ms_UserGroup table
/// </param>
/// <returns>
/// 0: Search successful
/// others: Sql Exception
/// </returns>
public int SelectData(SYS_UserGroupsDto selectDto, out DataTable dtResult)
{
int returnCode = CommonData.DbReturnCode.Succeed;
dtResult = new DataTable();
using (BaseDao context = new BaseDao())
{
try
{
IQueryable <ms_usergroups> lstGroup = context.ms_usergroups.AsQueryable();
#region Search Condition
//Filter by Code
if (!CommonMethod.IsNullOrEmpty(selectDto.Code))
{
lstGroup = lstGroup.Where(ig => ig.Code.Contains(selectDto.Code));
}
//Filter by Name(VN)
if (!CommonMethod.IsNullOrEmpty(selectDto.Name1))
{
lstGroup = lstGroup.Where(ig => ig.Name1.Contains(selectDto.Name1));
}
//Filter by Name(EN)
if (!CommonMethod.IsNullOrEmpty(selectDto.Name2))
{
lstGroup = lstGroup.Where(ig => ig.Name2.Contains(selectDto.Name2));
}
//Filter by Name(JP)
if (!CommonMethod.IsNullOrEmpty(selectDto.Name3))
{
lstGroup = lstGroup.Where(ig => ig.Name3.Contains(selectDto.Name3));
}
#endregion Search Condition
#region Search data
IEnumerable <SYS_UserGroupsDto> lstResult = lstGroup.Select(ig => new SYS_UserGroupsDto
{
Id = ig.ID,
Code = ig.Code,
Name1 = ig.Name1,
Name2 = ig.Name2,
Name3 = ig.Name3,
Description = ig.Description,
});
dtResult = base.ToDataTable(lstResult);
#endregion Search data
}
catch (Exception ex)
{
returnCode = this.ProcessDbException(ex);
}
}
return(returnCode);
}
public int InsertPermissionData(SYS_UserGroupsDto permissionDto)
{
int returnCode = CommonData.DbReturnCode.Succeed;
using (BaseDao context = new BaseDao())
{
try
{
context.BeginTransaction();
ms_usergroups userGroup = context.ms_usergroups.FirstOrDefault(ig => ig.Code.Equals(permissionDto.GroupCode));
if (userGroup != null)
{
#region Assign function
#region Remove old permission
//Remove function assign
IQueryable <ms_permissionsassign> lstFunctionPermission = context.ms_permissionsassign.Where(p => p.GroupCode.Equals(userGroup.Code));
foreach (ms_permissionsassign permission in lstFunctionPermission)
{
//Delete from database
context.ms_permissionsassign.DeleteObject(permission);
}
#endregion Remove old permission
#region Insert new permission
//insert function assign
foreach (SYS_PermissionAssignDto mappingDto in permissionDto.Mappings)
{
ms_permissionsassign permissionsAssign = new ms_permissionsassign();
permissionsAssign.GroupCode = userGroup.Code;
permissionsAssign.MappingID = mappingDto.MappingId;
permissionsAssign.SystemData = CommonData.Status.Disable;
//Create insert infomation
this.CreateInsertHistory(permissionsAssign, CommonData.FunctionGr.SY_UserGroups);
//Add to database
context.ms_permissionsassign.AddObject(permissionsAssign);
}
#endregion Insert new permission
#endregion Assign function
#region Assign warehouse
#region Remove old warehouse
//Remove warehouse assign
IQueryable <ms_warehouseassign> lstWHPermission = context.ms_warehouseassign.Where(p => p.GroupCode.Equals(userGroup.Code));
foreach (ms_warehouseassign whAssign in lstWHPermission)
{
//Delete from database
context.ms_warehouseassign.DeleteObject(whAssign);
}
#endregion Remove old warehouse
#region Insert new warehouse
//insert warehouse assign
foreach (MS_WarehouseAssignDto mappingDto in permissionDto.WHMappings)
{
if (!CommonMethod.IsNullOrEmpty(mappingDto.WarehouseCode))
{
ms_warehouseassign whAssign = new ms_warehouseassign();
whAssign.GroupCode = userGroup.Code;
whAssign.WarehouseCode = mappingDto.WarehouseCode;
//whAssign.OperID = mappingDto.OperID;
//whAssign.SystemData = CommonData.Status.Disable;
//Create insert infomation
this.CreateInsertHistory(whAssign, CommonData.FunctionGr.SY_UserGroups);
//Add to database
context.ms_warehouseassign.AddObject(whAssign);
}
}
#endregion Insert new warehouse
#endregion Assign warehouse
returnCode = context.Saves();
if (returnCode == 0)
{
returnCode = context.Commit();
}
else
{
context.Rollback();
}
}
else
{
context.Rollback();
returnCode = CommonData.DbReturnCode.DataNotFound;
}
}
catch (Exception ex)
{
context.Rollback();
returnCode = this.ProcessDbException(ex);
}
}
return(returnCode);
}
