public object SyncUser(dynamic Data)
{
DateTime TranDate = DateTime.Now;
DBHelper dbHelpRE = new DBHelper(ICON.Configuration.Database.REM_ConnectionString, null);
AP_Interface_Log Log = ICON.Interface.Transaction.CreateAPLog("User", "SyncUser", null, TranBy);
int ResponseCode = 500;
object ResponseData = null;
string ErrrorMessage = string.Empty;
try
{
string sql = string.Empty;
sql = @"
SELECT
RoleID,
RoleName,
Description
FROM
Sys_Admin_Roles";
DataTable DT_Role = dbHelpRE.ExecuteDataTable(sql);
sql = @"
SELECT
DepartmentID,
DepartmentName,
Description,
isDelete
FROM Sys_Master_Department;";
DataTable DT_Departments = dbHelpRE.ExecuteDataTable(sql);
List <HR_Master_Department> ListDepartments = new List <HR_Master_Department>();
List <HR_Master_Position> ListPositions = new List <HR_Master_Position>();
DBHelper dbHelpAP = new DBHelper(ICON.Configuration.Database.AP_ConnectionString, null);
System.Data.IDbTransaction APTran = dbHelpAP.BeginTransaction();
try
{
foreach (DataRow DR in DT_Departments.Rows)
{
sql = $"SELECT * FROM HR_Master_Department WHERE Code = '{DR["DepartmentID"].ToString()}'";
DataTable DT_APDepartments = dbHelpAP.ExecuteDataTable(sql, APTran);
HR_Master_Department Department = new HR_Master_Department();
if (DT_APDepartments.Rows.Count > 0)
{
Department.EditId = false;
Department.Code = DR["DepartmentID"].ToString();
Department.Name = DR["DepartmentName"].ToString();
Department.ModifyById = 0;
Department.ModifyBy = TranBy;
Department.ModifyDate = TranDate;
Department.ExecCommand.Update(dbHelpAP, APTran);
}
else
{
Department.Code = DR["DepartmentID"].ToString();
Department.Name = DR["DepartmentName"].ToString();
Department.CreateById = 0;
Department.CreateBy = TranBy;
Department.CreateDate = TranDate;
Department.ModifyById = 0;
Department.ModifyBy = TranBy;
Department.ModifyDate = TranDate;
Department.ExecCommand.Insert(dbHelpAP, APTran);
}
ListDepartments.Add(Department);
}
foreach (DataRow DR in DT_Role.Rows)
{
sql = $"SELECT * FROM HR_Master_Position WHERE Code = '{DR["RoleID"].ToString()}'";
DataTable DT_Position = dbHelpAP.ExecuteDataTable(sql, APTran);
HR_Master_Position Position = new HR_Master_Position();
if (DT_Position.Rows.Count > 0)
{
Position.ExecCommand.Load(DT_Position.Rows[0]);
Position.Name = Position.Name = DR["RoleName"].ToString();
Position.ModifyById = 0;
Position.ModifyBy = TranBy;
Position.ModifyDate = TranDate;
Position.ExecCommand.Update(dbHelpAP, APTran);
}
else
{
Position.Code = DR["RoleID"].ToString();
Position.Name = DR["RoleName"].ToString();
Position.CreateById = 0;
Position.CreateBy = TranBy;
Position.CreateDate = TranDate;
Position.ModifyById = 0;
Position.ModifyBy = TranBy;
Position.ModifyDate = TranDate;
Position.ExecCommand.Insert(dbHelpAP, APTran);
}
ListPositions.Add(Position);
}
sql = @"
SELECT
US.Username,
US.Password,
US.FirstName,
US.LastName,
US.Email,
UP.BirthDate,
UP.Mobile,
ISNULL(US.IsDelete, 0) IsDelete,
R.RoleId,
ISNULL(US.DepartmentID,'') DepartmentID
FROM Sys_Admin_Users US
LEFT JOIN Sys_Admin_UserProfile UP ON US.UserId = UP.UserId
LEFT JOIN Sys_Admin_UsersInBU IBU ON US.UserId = IBU.UserId
AND isDefault = 1
LEFT JOIN Sys_Admin_BU bu ON BU.BUId = IBU.BUId
LEFT JOIN Sys_Admin_Roles R ON R.RoleId = BU.RoleId
WHERE
1 = 1 ";
DataTable DT_Users = dbHelpRE.ExecuteDataTable(sql);
foreach (DataRow DR in DT_Users.Rows)
{
sql = $"SELECT * FROM HR_Master_User WHERE Username = '******' ";
DataTable DT_APUser = dbHelpAP.ExecuteDataTable(sql, APTran);
if (DT_APUser.Rows.Count > 0)
{
HR_Master_User User = new HR_Master_User();
User.ExecCommand.Load(DT_APUser.Rows[0]);
User.EditId = false;
User.Username = DR["Username"].ToString();
User.Password = DR["Password"].ToString();
User.FirstName = DR["FirstName"] != null && DR["FirstName"].ToString() != "" ? DR["FirstName"].ToString() : null;
User.LastName = DR["LastName"] != null && DR["LastName"].ToString() != "" ? DR["LastName"].ToString() : null;
User.Email = DR["Email"] != null && DR["Email"].ToString() != "" ? DR["Email"].ToString() : null;
HR_Master_Position Position = ListPositions.Find(p => p.Code == DR["RoleId"].ToString());
if (Position != null)
{
User.PositionId = ListPositions.Find(p => p.Code == DR["RoleId"].ToString()).Id;
}
else
{
User.PositionId = null;
}
HR_Master_Department Department = ListDepartments.Find(p => p.Code == DR["DepartmentID"].ToString());
if (Department != null)
{
User.DepartmentId = ListDepartments.Find(p => p.Code == DR["DepartmentID"].ToString()).Id;
}
else
{
User.DepartmentId = null;
}
if (DR["BirthDate"] != null && DR["BirthDate"].ToString() != "")
{
User.BirthDate = Convert.ToDateTime(DR["BirthDate"]);
}
else
{
User.BirthDate = null;
}
User.Telephone = DR["Mobile"] != null && DR["Mobile"].ToString() != "" ? DR["Mobile"].ToString() : null;
User.Status = !Convert.ToBoolean(DR["IsDelete"]) ? "Active" : "InActive";
User.ModifyById = 0;
User.ModifyBy = TranBy;
User.ModifyDate = TranDate;
User.ExecCommand.Update(dbHelpAP, APTran);
}
else
{
HR_Master_User User = new HR_Master_User();
User.Username = DR["Username"].ToString();
User.Password = DR["Password"].ToString();
User.FirstName = DR["FirstName"] != null && DR["FirstName"].ToString() != "" ? DR["FirstName"].ToString() : null;
User.LastName = DR["LastName"] != null && DR["LastName"].ToString() != "" ? DR["LastName"].ToString() : null;
User.Email = DR["Email"] != null && DR["Email"].ToString() != "" ? DR["Email"].ToString() : null;
HR_Master_Position Position = ListPositions.Find(p => p.Code == DR["RoleId"].ToString());
if (Position != null)
{
User.PositionId = ListPositions.Find(p => p.Code == DR["RoleId"].ToString()).Id;
}
else
{
User.PositionId = null;
}
HR_Master_Department Department = ListDepartments.Find(p => p.Code == DR["DepartmentID"].ToString());
if (Department != null)
{
User.DepartmentId = ListDepartments.Find(p => p.Code == DR["DepartmentID"].ToString()).Id;
}
else
{
User.DepartmentId = null;
}
if (DR["BirthDate"] != null && DR["BirthDate"].ToString() != "")
{
User.BirthDate = Convert.ToDateTime(DR["BirthDate"]);
}
else
{
User.BirthDate = null;
}
User.Telephone = DR["Mobile"] != null && DR["Mobile"].ToString() != "" ? DR["Mobile"].ToString() : null;
User.Status = !Convert.ToBoolean(DR["IsDelete"]) ? "Active" : "InActive";
User.CreateById = 0;
User.CreateBy = TranBy;
User.CreateDate = TranDate;
User.ModifyById = 0;
User.ModifyBy = TranBy;
User.ModifyDate = TranDate;
User.ExecCommand.Insert(dbHelpAP, APTran);
}
}
dbHelpAP.CommitTransaction(APTran);
}
catch (Exception ex)
{
dbHelpAP.RollbackTransaction(APTran);
throw ex;
}
ResponseCode = 200;
ResponseData = new { status = true, message = "success" };
}
catch (Exception ex)
{
ErrrorMessage = ex.Message;
ResponseData = new { status = false, message = ex.Message };
}
finally
{
ICON.Interface.Transaction.UpdateAPLog(Log.TranID, Newtonsoft.Json.JsonConvert.SerializeObject(ResponseData), ResponseCode, ErrrorMessage);
}
return(ResponseData);
}
public object SyncProjects(dynamic Data)
{
DateTime TranDate = DateTime.Now;
DBHelper dbHelpRE = new DBHelper(ICON.Configuration.Database.REM_ConnectionString, null);
AP_Interface_Log Log = ICON.Interface.Transaction.CreateAPLog("REM", "SyncProjects", null, TranBy);
int ResponseCode = 500;
object ResponseData = null;
string ErrrorMessage = string.Empty;
try
{
string sql = string.Empty;
sql = @"
SELECT ProjectID,
ProjectName,
ISNULL(IsDelete,0) IsDelete
FROM Sys_Master_Projects";
DataTable DT_REMProjects = dbHelpRE.ExecuteDataTable(sql);
DBHelper dbHelpAP = new DBHelper(ICON.Configuration.Database.AP_ConnectionString, null);
System.Data.IDbTransaction APTran = dbHelpAP.BeginTransaction();
try
{
foreach (DataRow DR in DT_REMProjects.Rows)
{
sql = $"SELECT * FROM REM_Master_Project WHERE Code = '{DR["ProjectID"].ToString()}'";
DataTable DT_APProject = dbHelpAP.ExecuteDataTable(sql, APTran);
REM_Master_Project AP_Project = new REM_Master_Project();
if (DT_APProject.Rows.Count > 0)
{
AP_Project.ExecCommand.Load(DT_APProject.Rows[0]);
AP_Project.EditId = false;
AP_Project.Code = DR["ProjectID"].ToString();
AP_Project.Name = DR["ProjectName"].ToString();
AP_Project.Status = !Convert.ToBoolean(DR["IsDelete"]) ? "Active" : "InActive";
AP_Project.ModifyById = 0;
AP_Project.ModifyBy = TranBy;
AP_Project.ModifyDate = TranDate;
AP_Project.ExecCommand.Update(dbHelpAP, APTran);
}
else
{
AP_Project.Code = DR["ProjectID"].ToString();
AP_Project.Name = DR["ProjectName"].ToString();
AP_Project.Status = !Convert.ToBoolean(DR["IsDelete"]) ? "Active" : "InActive";
AP_Project.CreateById = 0;
AP_Project.CreateBy = TranBy;
AP_Project.CreateDate = TranDate;
AP_Project.ModifyById = 0;
AP_Project.ModifyBy = TranBy;
AP_Project.ModifyDate = TranDate;
AP_Project.ExecCommand.Insert(dbHelpAP, APTran);
}
}
dbHelpAP.CommitTransaction(APTran);
}
catch (Exception ex)
{
dbHelpAP.RollbackTransaction(APTran);
throw ex;
}
ResponseCode = 200;
ResponseData = new { status = true, message = "success" };
}
catch (Exception ex)
{
ErrrorMessage = ex.Message;
ResponseData = new { status = false, message = ex.Message };
}
finally
{
ICON.Interface.Transaction.UpdateAPLog(Log.TranID, Newtonsoft.Json.JsonConvert.SerializeObject(ResponseData), ResponseCode, ErrrorMessage);
}
return(ResponseData);
}
