public bool DeleteTSRFromTeam(string TSR_ID, string USER_ID)
{
try
{
//sql 1
string updateUsersCstmSql = "update USERS_CSTM set GROUP_ID_C = NULL where ID_C = @ID";
DynamicParameters paramsUpdateUserCstmSql = new DynamicParameters();
paramsUpdateUserCstmSql.Add("@ID", new Guid(TSR_ID));
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(updateUsersCstmSql, paramsUpdateUserCstmSql);
//sql 2
string updateUsersSql = "update USERS set DATE_MODIFIED = @DATE_MODIFIED, MODIFIED_USER_ID = @MODIFIED_USER_ID where ID = @ID";
DynamicParameters paramUpdateUsersSql = new DynamicParameters();
paramUpdateUsersSql.Add("@ID", new Guid(TSR_ID));
paramUpdateUsersSql.Add("@MODIFIED_USER_ID", new Guid(USER_ID));
paramUpdateUsersSql.Add("@DATE_MODIFIED", DateTime.Now);
sqlForms[1] = new SqlForm(updateUsersSql, paramUpdateUsersSql);
DapperORM.ExecuteTransaction(sqlForms);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(false);
}
}
public bool DeleteTSRFromTeam(string tsrId, string userId)
{
try
{
string sql1 = "update USERS_CSTM set GROUP_ID_C = NULL where ID_C = @ID";
DynamicParameters param1 = new DynamicParameters();
param1.Add("@ID", new Guid(tsrId));
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(sql1, param1);
string sql2 = "update USERS set DATE_MODIFIED = @DATE_MODIFIED, MODIFIED_USER_ID = @MODIFIED_USER_ID where ID = @ID";
DynamicParameters param2 = new DynamicParameters();
param2.Add("@ID", new Guid(tsrId));
param2.Add("@MODIFIED_USER_ID", new Guid(userId));
param2.Add("@DATE_MODIFIED", DateTime.Now);
sqlForms[1] = new SqlForm(sql2, param2);
DapperORM.ExecuteTransaction(sqlForms);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(false);
}
}
// async
public bool UpdateTitle(CreateTitleDataDto createTitleDataDto, string userID)
{
try
{
Console.WriteLine(createTitleDataDto.ToString());
//WHERE condition;
string titleSql = "UPDATE TITLE" +
" SET NAME = @name, NAME_USERTITLE = @nameUserTitle, DESCRIPTION = @description, MODIFIED_USER_ID = @modifiedUserId" +
" WHERE ID = @id;";
DynamicParameters paramTitle = new DynamicParameters();
paramTitle.Add("@id", createTitleDataDto.ID);
paramTitle.Add("@name", createTitleDataDto.NAME);
paramTitle.Add("@nameUserTitle", createTitleDataDto.NAME_USERTITLE);
paramTitle.Add("@description", createTitleDataDto.DESCRIPTION);
paramTitle.Add("@modifiedUserId", new Guid(userID));
//update 2
//String titleCSTMSql = @"
//INSERT INTO TITLE_CSTM(ID_C,TYPE_ID_C)
//VALUES(@ID_C, @TYPE_ID_C)";
string titleCSTMSql = "UPDATE TITLE_CSTM" +
" SET TYPE_ID_C = @typeIdC" +
" WHERE ID_C = @idC;";
DynamicParameters paramTitleCSTM = new DynamicParameters();
paramTitleCSTM.Add("@idC", createTitleDataDto.ID);
paramTitleCSTM.Add("@typeIdC", createTitleDataDto.TYPE_ID_C);
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(titleSql, paramTitle);
sqlForms[1] = new SqlForm(titleCSTMSql, paramTitleCSTM);
DapperORM.ExecuteTransaction(sqlForms);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(false);
}
}
// async
public bool CreateTitle(CreateTitleDataDto createTitleDataDto, string newId, string userID)
{
try
{
String titleSql = @"
INSERT INTO TITLE(ID,NAME,NAME_USERTITLE,DESCRIPTION,CREATED_BY,MODIFIED_USER_ID)
VALUES(@ID, @NAME, @NAME_USERTITLE, @DESCRIPTION, @CREATED_BY, @MODIFIED_USER_ID)";
DynamicParameters paramTitle = new DynamicParameters();
paramTitle.Add("@ID", new Guid(newId));
paramTitle.Add("@NAME", createTitleDataDto.NAME);
paramTitle.Add("@NAME_USERTITLE", createTitleDataDto.NAME_USERTITLE);
paramTitle.Add("@DESCRIPTION", createTitleDataDto.DESCRIPTION);
paramTitle.Add("@CREATED_BY", new Guid(userID));
paramTitle.Add("@MODIFIED_USER_ID", new Guid(userID));
String titleCSTMSql = @"
INSERT INTO TITLE_CSTM(ID_C,TYPE_ID_C)
VALUES(@ID_C, @TYPE_ID_C)";
DynamicParameters paramTitleCSTM = new DynamicParameters();
paramTitleCSTM.Add("@ID_C", new Guid(newId));
paramTitleCSTM.Add("@TYPE_ID_C", createTitleDataDto.TYPE_ID_C);
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(titleSql, paramTitle);
sqlForms[1] = new SqlForm(titleCSTMSql, paramTitleCSTM);
DapperORM.ExecuteTransaction(sqlForms);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(false);
}
}
public Guid UpdateUser(UpdateUserDto updateUserDto, string id, string modifiedBy)
{
try
{
// Check user name Exist
bool checkUserName = CheckUserExist("USER_NAME", updateUserDto.USER_NAME, id);
// 1: username exist, pick other name
if (checkUserName)
{
throw new Exception("USERNAME exist");
}
// Update
List <User> users = new List <User>();
List <Users_cstm> user_Cstms = new List <Users_cstm>();
String userSql = "UPDATE USERS " +
"SET PHONE_MOBILE = @PHONE_MOBILE ," +
" EMAIL1 = @EMAIL1, " +
" IS_ADMIN = @IS_ADMIN," +
" STATUS = @STATUS, " +
" LAST_NAME = @LAST_NAME, " +
" FIRST_NAME = @FIRST_NAME," +
" DATE_MODIFIED = @DATE_MODIFIED, " +
" MODIFIED_USER_ID = @MODIFIEDBY" +
" WHERE ID = @ID";
String userCstmSql = "UPDATE USERS_CSTM " +
"SET HR_ID_C = @HR_ID_C ," +
" USER_TYPE_C = @USER_TYPE_C, " +
" USER_TITLE_ID_C = @USER_TITLE_ID_C," +
" ACCOUNT_NUMBER_C = @ACCOUNT_NUMBER_C, " +
" REGION_ID_C = @REGION_ID_C, " +
" ROLE_ID_C = @ROLE_ID_C," +
" HO_TEN_C = @HO_TEN_C," +
" AGENT_ID_C = @AGENT_ID_C" +
" WHERE ID_C = @ID_C";
DynamicParameters paramCstm = new DynamicParameters();
paramCstm.Add("@ID_C", id);
paramCstm.Add("@HR_ID_C", updateUserDto.HR_ID_C);
paramCstm.Add("@USER_TYPE_C", updateUserDto.USER_TYPE_C);
paramCstm.Add("@USER_TITLE_ID_C", updateUserDto.USER_TITLE_ID_C);
paramCstm.Add("@ACCOUNT_NUMBER_C", updateUserDto.ACCOUNT_NUMBER_C);
paramCstm.Add("@REGION_ID_C", updateUserDto.REGION_ID_C);
paramCstm.Add("@ROLE_ID_C", updateUserDto.ROLE_ID_C);
paramCstm.Add("@HO_TEN_C", updateUserDto.FIRST_NAME);
paramCstm.Add("@AGENT_ID_C", updateUserDto.AGENT_ID_C);
// update user
DynamicParameters param = new DynamicParameters();
param.Add("@ID", id);
param.Add("@PHONE_MOBILE", updateUserDto.PHONE_MOBILE);
param.Add("@EMAIL1", updateUserDto.EMAIL1);
param.Add("@IS_ADMIN", updateUserDto.IS_ADMIN);
param.Add("@DATE_MODIFIED", DateTime.Now);
param.Add("@STATUS", updateUserDto.STATUS);
param.Add("@LAST_NAME", updateUserDto.LAST_NAME);
param.Add("@FIRST_NAME", updateUserDto.FIRST_NAME);
param.Add("@MODIFIEDBY", modifiedBy);
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(userSql, param);
sqlForms[1] = new SqlForm(userCstmSql, paramCstm);
// DapperORM.Execute(userSql, param);
//DapperORM.Execute(userCstmSql, paramCstm);
DapperORM.ExecuteTransaction(sqlForms);
return(new Guid(id));
}
catch (Exception e)
{
Console.WriteLine(e);
return(new Guid("00000000-0000-0000-0000-000000000000"));
}
}
//public async Task<Guid> CreateUserAsync(UpdateUserDto updateUserDto)
//{
// Guid id = Guid.NewGuid();
// User user = GenUser(updateUserDto);
// Users_cstm user_cstm = GenUser_cstm(updateUserDto);
// try
// {
// user_cstm.ID_C = id;
// List<User> users = new List<User>();
// users.Add(user);
// user.DATE_ENTERED = DateTime.Now;
// user.DATE_MODIFIED = DateTime.Now;
// List<Users_cstm> user_Cstms = new List<Users_cstm>();
// user_Cstms.Add(user_cstm);
// String userSql = @"
// INSERT INTO USERS(ID,USER_NAME, EMAIL1, PHONE_MOBILE, IS_ADMIN,
// STATUS, LAST_NAME, FIRST_NAME)
// VALUES(@ID, @USER_NAME, @EMAIL1, @PHONE_MOBILE, @IS_ADMIN, @STATUS, @LAST_NAME,
// @FIRST_NAME)";
// DynamicParameters param = new DynamicParameters();
// param.Add("@ID", id);
// param.Add("@USER_NAME", user.USER_NAME);
// param.Add("@PHONE_MOBILE", user.PHONE_MOBILE);
// param.Add("@EMAIL1", user.EMAIL1);
// param.Add("@IS_ADMIN", user.IS_ADMIN);
// param.Add("@STATUS", user.STATUS);
// param.Add("@LAST_NAME", user.LAST_NAME);
// param.Add("@FIRST_NAME", user.FIRST_NAME);
// // INSERT CSTM
// String userCstmSql = @"
// INSERT INTO USERS_CSTM(ID_C,HR_ID_C, USER_TYPE_C, USER_TITLE_ID_C, ACCOUNT_NUMBER_C,
// REGION_ID_C, ROLE_ID_C, HO_TEN_C)
// VALUES(@ID_C, @HR_ID_C, @USER_TYPE_C, @USER_TITLE_ID_C, @ACCOUNT_NUMBER_C,@REGION_ID_C, @ROLE_ID_C, @HO_TEN_C
// )";
// DynamicParameters paramCstm = new DynamicParameters();
// paramCstm.Add("@ID_C", id);
// paramCstm.Add("@HR_ID_C", user_cstm.HR_ID_C);
// paramCstm.Add("@USER_TYPE_C", user_cstm.USER_TYPE_C);
// paramCstm.Add("@USER_TITLE_ID_C", user_cstm.USER_TITLE_ID_C);
// paramCstm.Add("@ACCOUNT_NUMBER_C", user_cstm.ACCOUNT_NUMBER_C);
// paramCstm.Add("@REGION_ID_C", user_cstm.REGION_ID_C);
// paramCstm.Add("@ROLE_ID_C", user_cstm.ROLE_ID_C);
// paramCstm.Add("@HO_TEN_C", user_cstm.HO_TEN_C);
// SqlForm[] sqlForms = new SqlForm[2];
// sqlForms[0] = new SqlForm(userSql, param);
// sqlForms[1] = new SqlForm(userCstmSql, paramCstm);
// //DapperORM.ExecuteTransaction(sqlForms);
// await WithConnection(async conn =>
// {
// await conn.ExecuteAsync(userSql,
// new {
// ID = id,
// USER_NAME = user.USER_NAME,
// PHONE_MOBILE = user.PHONE_MOBILE,
// EMAIL1 = user.EMAIL1,
// IS_ADMIN = user.IS_ADMIN,
// STATUS = user.EMPLOYEE_STATUS,
// LAST_NAME = user.LAST_NAME,
// FIRST_NAME = user.FIRST_NAME
// });
// await conn.ExecuteAsync(userCstmSql,
// new { ID_C = id, HR_ID_C = user_cstm.HR_ID_C, USER_TYPE_C = user_cstm.USER_TYPE_C, USER_TITLE_ID_C = user_cstm.USER_TITLE_ID_C,
// ACCOUNT_NUMBER_C = user_cstm.ACCOUNT_NUMBER_C,
// REGION_ID_C = user_cstm.REGION_ID_C,
// ROLE_ID_C = user_cstm.ROLE_ID_C,
// HO_TEN_C = user_cstm.HO_TEN_C
// });
// });
// return id;
// }
// catch (Exception e)
// {
// throw e;
// }
//}
public Guid CreateUser(UpdateUserDto updateUserDto, string createdBy)
{
Guid id = Guid.NewGuid();
User user = GenUser(updateUserDto);
Users_cstm user_cstm = GenUser_cstm(updateUserDto);
try
{
user_cstm.ID_C = id;
bool checkUserName = CheckUserExist("USER_NAME", user.USER_NAME, null);
// 1: username exist, pick other name
if (checkUserName)
{
throw new Exception("USERNAME_EXIST");
}
List <User> users = new List <User>();
users.Add(user);
user.DATE_ENTERED = DateTime.Now;
user.DATE_MODIFIED = DateTime.Now;
List <Users_cstm> user_Cstms = new List <Users_cstm>();
user_Cstms.Add(user_cstm);
String userSql = @"
INSERT INTO USERS(ID,USER_NAME, EMAIL1, PHONE_MOBILE, IS_ADMIN,
STATUS, LAST_NAME, FIRST_NAME, CREATED_BY)
VALUES(@ID, @USER_NAME, @EMAIL1, @PHONE_MOBILE, @IS_ADMIN, @STATUS, @LAST_NAME,
@FIRST_NAME, @CREATED_BY)";
DynamicParameters param = new DynamicParameters();
param.Add("@ID", id);
param.Add("@USER_NAME", user.USER_NAME);
param.Add("@PHONE_MOBILE", user.PHONE_MOBILE);
param.Add("@EMAIL1", user.EMAIL1);
param.Add("@IS_ADMIN", user.IS_ADMIN);
param.Add("@STATUS", user.STATUS);
param.Add("@LAST_NAME", user.LAST_NAME);
param.Add("@FIRST_NAME", user.FIRST_NAME);
param.Add("@CREATED_BY", createdBy);
// INSERT CSTM
String userCstmSql = @"
INSERT INTO USERS_CSTM(ID_C,HR_ID_C, USER_TYPE_C, USER_TITLE_ID_C, ACCOUNT_NUMBER_C,
REGION_ID_C, ROLE_ID_C, HO_TEN_C, AGENT_ID_C)
VALUES(@ID_C, @HR_ID_C, @USER_TYPE_C, @USER_TITLE_ID_C, @ACCOUNT_NUMBER_C,@REGION_ID_C, @ROLE_ID_C, @HO_TEN_C, @AGENT_ID_C
)";
DynamicParameters paramCstm = new DynamicParameters();
paramCstm.Add("@ID_C", id);
paramCstm.Add("@HR_ID_C", user_cstm.HR_ID_C);
paramCstm.Add("@USER_TYPE_C", user_cstm.USER_TYPE_C);
paramCstm.Add("@USER_TITLE_ID_C", user_cstm.USER_TITLE_ID_C);
paramCstm.Add("@ACCOUNT_NUMBER_C", user_cstm.ACCOUNT_NUMBER_C);
paramCstm.Add("@REGION_ID_C", user_cstm.REGION_ID_C);
paramCstm.Add("@ROLE_ID_C", user_cstm.ROLE_ID_C);
paramCstm.Add("@HO_TEN_C", user_cstm.HO_TEN_C);
paramCstm.Add("@AGENT_ID_C", user_cstm.AGENT_ID_C);
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(userSql, param);
sqlForms[1] = new SqlForm(userCstmSql, paramCstm);
DapperORM.ExecuteTransaction(sqlForms);
return(id);
}
catch (Exception e)
{
return(new Guid("00000000-0000-0000-0000-000000000000"));
}
}
public DisplayMessage(SqlForm form)
{
this.form = form;
}
//chưa haofn thành
public ResponseForm <string> Update(UpdateBracnhsDto branchs, string USER_ID)
{
if (!string.IsNullOrWhiteSpace(branchs.NAME))
{
try
{
if (string.IsNullOrEmpty(branchs.ID))
{
DynamicParameters param = new DynamicParameters();
Guid id = Guid.NewGuid();
string query_update_branchs = "insert into BRANCHS(ID,NAME , SHORT_NAME, RANK,REGION_ID, PROVINE_NAME, BANK_STRUCT,DATE_MODIFIED,MODIFIED_USER_ID,CREATED_BY,DATE_ENTERED) values (@ID, @NAME , @SHORT_NAME, @RANK,@REGION_ID, @PROVINE_NAME, @BANK_STRUCT,@DATE_MODIFIED,@MODIFIED_USER_ID,@CREATED_BY,@DATE_ENTERED)";
param.Add("@ID", id);
param.Add("@NAME", branchs.NAME);
param.Add("@SHORT_NAME", branchs.SHORT_NAME);
param.Add("@RANK", branchs.RANK);
param.Add("@REGION_ID", new Guid(branchs.REGION_ID));
param.Add("@PROVINE_NAME", branchs.PROVINE_NAME);
param.Add("@BANK_STRUCT", branchs.BANK_STRUCT);
param.Add("@DATE_MODIFIED", DateTime.Now);
param.Add("@MODIFIED_USER_ID", USER_ID);
param.Add("@CREATED_BY", USER_ID);
param.Add("@DATE_ENTERED", DateTime.Now);
string query_update_cstm = "insert into BRANCHS_CSTM (ID_C, REGION_GROUP_C, LOCAL_GROUP_C) values(@ID_C,@REGION_GROUP_C,@LOCAL_GROUP_C)";
DynamicParameters param_cstm = new DynamicParameters();
param_cstm.Add("@ID_C", id);
param_cstm.Add("@REGION_GROUP_C", new Guid(branchs.REGION_GROUP_C));
param_cstm.Add("@LOCAL_GROUP_C", new Guid(branchs.LOCAL_GROUP_C));
SqlForm[] sqlForms = new SqlForm[2];
sqlForms[0] = new SqlForm(query_update_branchs, param);
sqlForms[1] = new SqlForm(query_update_cstm, param_cstm);
DapperORM.ExecuteTransaction(sqlForms);
return(new ResponseForm <string>(id.ToString(), "ok", 200));
}
else
{
DynamicParameters param = new DynamicParameters();
string query_update_branchs = "Update dbo.BRANCHS set DATE_MODIFIED = @DATE_MODIFIED,MODIFIED_USER_ID = @MODIFIED_USER_ID, NAME = @NAME, SHORT_NAME = @SHORT_NAME, RANK = @RANK, REGION_ID = @REGION_ID, PROVINE_NAME = @PROVINE_NAME, BANK_STRUCT = @BANK_STRUCT where ID = @id ";
param.Add("@id", new Guid(branchs.ID));
param.Add("@NAME", branchs.NAME);
param.Add("@SHORT_NAME", branchs.SHORT_NAME);
param.Add("@RANK", branchs.RANK);
param.Add("@REGION_ID", new Guid(branchs.REGION_ID));
param.Add("@PROVINE_NAME", branchs.PROVINE_NAME);
param.Add("@BANK_STRUCT", branchs.BANK_STRUCT);
param.Add("@DATE_MODIFIED", DateTime.Now);
param.Add("@MODIFIED_USER_ID", USER_ID);
DapperORM.ExcecuteNonReturn(query_update_branchs, param);
string query_update_cstm = "Update BRANCHS_CSTM set REGION_GROUP_C = @REGION_GROUP_C , LOCAL_GROUP_C = @local_group_c where ID_C = @id";
DynamicParameters param_cstm = new DynamicParameters();
param_cstm.Add("@id", new Guid(branchs.ID));
param_cstm.Add("@REGION_GROUP_C", new Guid(branchs.REGION_GROUP_C));
param_cstm.Add("@local_group_c", new Guid(branchs.LOCAL_GROUP_C));
DapperORM.ExcecuteNonReturn(query_update_cstm, param_cstm);
return(new ResponseForm <string>(branchs.ID.ToString(), "ok", 200));
}
}
catch (Exception e)
{
return(new ResponseForm <string>("0", e.Message, 600));
}
}
else
{
return(new ResponseForm <string>("0", "NAME is not null", 400));
}
}
public QueryHandler(SqlForm form)
{
_messenger = new DisplayMessage(form);
subQueryHandler = new SubQueryHandler();
}
