public async Task UpdateAsync(CaseProceedingModel caseProceedingModel, int currUser)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_case_proceedings.p_update_case_proceeding",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_case_proceeding_id", caseProceedingModel.CaseProceedingId, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_proceeding_date", caseProceedingModel.ProceedingDate, dbType: OracleMappingType.Date, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_proceeding_decision", caseProceedingModel.ProceedingDecision, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_next_hearing_on", caseProceedingModel.NextHearingOn, dbType: OracleMappingType.Date, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_judgement_file", caseProceedingModel.JudgementFile, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_update_by", currUser, dbType: OracleMappingType.Int32, ParameterDirection.Input);
await _dataAccess.ExecuteAsync(sqlModel);
}
public async Task <int> CreateAsync(NewCaseTypeModel caseTypeModel)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_case_types.p_create_new_case_type",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_case_type_name", caseTypeModel.CaseTypeName, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_case_type_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int case_typeId = (int)sqlModel.Parameters.Get <decimal>("po_case_type_id");
return(case_typeId);
}
public async Task <int> CreateAsync(NewLocationModel locationModel)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_locations.p_create_new_location",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_location_name", locationModel.LocationName, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_location_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int locationId = (int)sqlModel.Parameters.Get <decimal>("po_location_id");
return(locationId);
}
public async Task <int> CreateAsync(NewAttachmentModel attachmentModel, byte[] attachmentFile, int currUser)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_attachments.p_create_new_attachment",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_filename", attachmentModel.Filename, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_content_type", attachmentModel.ContentType, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_attachment_file", attachmentFile, dbType: OracleMappingType.Blob, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_create_by", currUser, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("po_attachment_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
return(sqlModel.Parameters.Get <int>("po_attachment_id"));
}
public async Task <(int caseId, DateTime?deleted)> ExistsCaseNumberAsync(string caseNumber, int appealNumber)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_court_cases.p_exists_case_number",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_case_number", caseNumber, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_appeal_number", appealNumber, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("po_case_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
sqlModel.Parameters.Add("po_deleted", dbType: OracleMappingType.Date, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int caseId = sqlModel.Parameters.Get <int>("po_case_id");
DateTime?deleted = sqlModel.Parameters.Get <DateTime?>("po_deleted");
return(caseId, deleted);
}
public async Task <int> CreateAsync(NewLawyerModel lawyerModel)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_lawyers.p_create_new_lawyer",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_lawyer_email", lawyerModel.LawyerEmail, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_lawyer_fullname", lawyerModel.LawyerFullname, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_lawyer_phone", lawyerModel.LawyerPhone, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_lawyer_address", lawyerModel.LawyerAddress, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_lawyer_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int lawyerId = (int)sqlModel.Parameters.Get <decimal>("po_lawyer_id");
return(lawyerId);
}
public async Task <(int, string, string)> FetchUserDetailsAsync(string userEmail)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_auth.p_get_auth_details",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_user_email", userEmail, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_user_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
sqlModel.Parameters.Add("po_password", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Output, size: 4000);
sqlModel.Parameters.Add("po_salt", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Output, size: 4000);
await _dataAccess.ExecuteAsync(sqlModel);
int userId = (int)sqlModel.Parameters.Get <decimal>("po_user_id");
string password = sqlModel.Parameters.Get <string>("po_password");
string salt = sqlModel.Parameters.Get <string>("po_salt");
return(userId, password, salt);
}
public async Task <int> CreateAsync(NewUserModel userModel, string passwordSalt, string hashedPassword)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_app_users.p_create_new_user",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_user_email", userModel.UserEmail, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_user_fullname", userModel.UserFullname, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_user_password", hashedPassword, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_password_salt", passwordSalt, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_user_roles", userModel.UserRoles, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("po_user_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int userId = (int)sqlModel.Parameters.Get <decimal>("po_user_id");
return(userId);
}
public async Task UpdateAsync(CaseDatesModel caseDatesModel, int currUser)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_case_dates.p_update_case_dates",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_case_id", caseDatesModel.CaseId, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_case_filed_on", caseDatesModel.CaseFiledOn, dbType: OracleMappingType.Date, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_notice_received_on", caseDatesModel.NoticeReceivedOn, dbType: OracleMappingType.Date, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_first_hearing_on", caseDatesModel.FirstHearingOn, dbType: OracleMappingType.Date, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_update_by", currUser, dbType: OracleMappingType.Int32, ParameterDirection.Input);
await _dataAccess.ExecuteAsync(sqlModel);
}
public async Task <int> GetRoleIdAsync(string rolesCsv)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_roles.p_get_role_id",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_roles_csv", rolesCsv, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_role_id", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
await _dataAccess.ExecuteAsync(sqlModel);
int roleId = (int)sqlModel.Parameters.Get <decimal>("po_role_id");
return(roleId);
}
public async Task UpdateAsync(IEnumerable <CaseActorModel> caseActorModels, int currUser)
{
foreach (var model in caseActorModels)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_case_actors.p_update_case_actors",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_case_id", model.CaseId, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_actor_type_id", model.ActorTypeId, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_actor_name", model.ActorName, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_actor_address", model.ActorAddress, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_actor_email", model.ActorEmail, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_actor_phone", model.ActorPhone, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_detail_file", model.DetailFile, dbType: OracleMappingType.Int32, ParameterDirection.Input);
sqlModel.Parameters.Add("pi_update_by", currUser, dbType: OracleMappingType.Int32, ParameterDirection.Input);
await _dataAccess.ExecuteAsync(sqlModel);
}
}
