public async Task <TM_OPERADOR_EMPRESA> Update(TM_OPERADOR_EMPRESA input)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("num_idnominaxemp_ ", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDNOMINAXEMP);
dyParam.Add("num_idtipooper_", OracleDbType.Varchar2, ParameterDirection.Input, input.NUM_IDTIPOOPER);
dyParam.Add("dte_fecnacimiento_", OracleDbType.Int32, ParameterDirection.Input, input.DTE_FECNACIMIENTO.ToBDSirosDate());
dyParam.Add("str_usuact_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_USUACT);
dyParam.Add("num_idsesion_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSESION);
dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output);
var query = _connectionFactory.GetQueryForSIROS("PKG_EMPRESA.SP_ActualizarOperadorEmp");
var result = await connection.QueryFirstOrDefaultAsync <TM_OPERADOR_EMPRESA>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result);
}
}
public async Task <List <TM_SUCURSAL_ES> > GetAllByFilter(int cantidadXPagina, int pagina, string filter)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var splFilter = filter.Split('@');
var dyParam = new OracleDynamicParameters();
dyParam.Add("num_filtrotipo_", OracleDbType.Varchar2, ParameterDirection.Input, splFilter[0]);
dyParam.Add("str_filtrovalor_", OracleDbType.Varchar2, ParameterDirection.Input, splFilter[1].ToUpper());
dyParam.Add("num_identidadusuario_", OracleDbType.Int32, ParameterDirection.Input, splFilter[2]);
dyParam.Add("num_pagina_", OracleDbType.Varchar2, ParameterDirection.Input, pagina);
dyParam.Add("num_registros_", OracleDbType.Varchar2, ParameterDirection.Input, cantidadXPagina);
dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output);
var query = _connectionFactory.GetQueryForSIROS("PKG_SUCURSAL.SP_GetListaSucursal");
var result = await connection.QueryAsync <TM_SUCURSAL_ES>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result.AsList());
}
}
public async Task <ADMIN.TM_SESION> RegistrarSesion(ADMIN.TM_SESION oItem)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("str_usuario_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_USUARIO);
dyParam.Add("str_userso_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_USERSO);
dyParam.Add("str_numeroip_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_NUMEROIP);
dyParam.Add("str_flagcr_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_FLAGCR);
dyParam.Add("num_idsesionsso_", OracleDbType.Int32, ParameterDirection.Input, oItem.NUM_IDSESIONSSO);
dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output);
var query = _connectionFactory.GetQueryForSIROS("PKG_ADMIN.SP_RegistrarSesion");
var result = await connection.QueryFirstOrDefaultAsync <ADMIN.TM_SESION>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result);
}
}
public async Task <TM_VEHICULO_RUTA_EMPRESA> Update(TM_VEHICULO_RUTA_EMPRESA input)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("num_vehxemp_ ", OracleDbType.Int32, ParameterDirection.Input, input.NUM_VEHXEMP);
dyParam.Add("num_idsucursalxes_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSUCURSALXES);
dyParam.Add("num_idcombustible_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDCOMBUSTIBLE);
dyParam.Add("str_usuact_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_USUACT);
dyParam.Add("num_idsesion_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSESION);
dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output);
var query = _connectionFactory.GetQueryForSIROS("PKG_VEHXEMP.SP_ActualizarVehxEmp");
var result = await connection.QueryFirstOrDefaultAsync <TM_VEHICULO_RUTA_EMPRESA>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public object GetEmployeeList()
{
object result = null;
try
{
var parameters = new OracleDynamicParameters();
parameters.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
var connection = GetConnection();
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
if (connection.State == ConnectionState.Open)
{
result = SqlMapper.Query(connection, "USP_GETEMPLOYEES", parameters, null, true, null, CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// Use this method to return single recordset.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="spname"></param>
/// <param name="parameters"></param>
/// <param name="timeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public IEnumerable <dynamic> QueryDynamic <T>(string spname, object parameters = null,
int timeout = 30, CommandType?commandType = null)
{
try
{
OracleDynamicParameters dynParams = new OracleDynamicParameters();
dynParams.Add("p_recordset", OracleDbType.RefCursor, ParameterDirection.Output);
//dynParams.Add(":id", OracleDbType.Int32, ParameterDirection.Input, value);
using (var connection = new OracleConnection(BaseUtility.ConnectionString))
{
connection.Open();
return(connection.Query <dynamic>(spname, param: dynParams,
commandTimeout: timeout, commandType: commandType));
}
}
catch (Exception ex)
{
new Emailer().Publish(new CustomException {
ErrorMessage = ex.Message, MethodName = new StackTrace().GetFrame(1).GetMethod().Name, StackTrace = ex.StackTrace, Source = ex.Source
});
throw;
}
}
public List <AgentInformation> GetAgentInfo(string fromDate, string toDate, string options, string accCategory, string accCategorySub)
{
using (var connection = this.GetConnection())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("FROMDATE", OracleDbType.Varchar2, ParameterDirection.Input, fromDate);
dyParam.Add("TODATE", OracleDbType.Varchar2, ParameterDirection.Input, toDate);
dyParam.Add("OPTIONS", OracleDbType.Varchar2, ParameterDirection.Input, options);
dyParam.Add("ACCCATEGORY", OracleDbType.Varchar2, ParameterDirection.Input, accCategory);
dyParam.Add("V_ACC_SUB_CAT", OracleDbType.Varchar2, ParameterDirection.Input, accCategorySub == "All" ? null : accCategorySub);
dyParam.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
List <AgentInformation> result = SqlMapper.Query <AgentInformation>(connection, dbUser + "RPT_AGENT_INFORMATION", param: dyParam, commandType: CommandType.StoredProcedure).ToList();
this.CloseConnection(connection);
this.CloseConnection(connection);
return(result);
}
}
public void ProcedureInsertIntoUserTable(UserTable user)
{
first_name = user.FIRST_NAME;
last_name = user.LAST_NAME;
user_name = user.USER_NAME;
email = user.EMAIL;
password = user.PASSWORD;
user_type = user.USER_TYPE;
var paramIn = new OracleDynamicParameters();
paramIn.Add("vpFirstName", dbType: OracleDbType.Varchar2, value: first_name, direction: ParameterDirection.Input);
paramIn.Add("vpLastName", dbType: OracleDbType.Varchar2, value: last_name, direction: ParameterDirection.Input);
paramIn.Add("vpUserName", dbType: OracleDbType.Varchar2, value: user_name, direction: ParameterDirection.Input);
paramIn.Add("vpEmail", dbType: OracleDbType.Varchar2, value: email, direction: ParameterDirection.Input);
paramIn.Add("vpPassword", dbType: OracleDbType.Varchar2, value: password, direction: ParameterDirection.Input);
paramIn.Add("npUserType", dbType: OracleDbType.Int32, value: user_type, direction: ParameterDirection.Input);
conn.Query <UserTable>("INSITE_DEMO.INSERT_ANALITYCS_DATA.INSERT_INTO_USER_TABLE", paramIn, commandType: CommandType.StoredProcedure);
}
public bool Create()
{
try
{
var p = new OracleDynamicParameters();
p.Add("Rut", this.Rut);
p.Add("DigitoV", this.DigitoV);
p.Add("Nombre", this.Nombre);
p.Add("APaterno", this.APaterno);
p.Add("AMaterno", this.AMaterno);
p.Add("Apoderado_Id", this.Apoderado.Id);
p.Add("Curso", this.Curso.Id);
Db.Execute(Procs.Alumno_Crear, p, commandType: CommandType.StoredProcedure);
logger.Info("Alumno N°{0} creado correctamente", Rut);
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
internal static OracleDynamicParameters GetSelectRuleSetsOnQueryParams(Guid?ruleSetGuid, long?departmetnID, string searchTerm, bool?getActive, bool getHospitalLevel, long hospitalId)
{
int?getActiveValue = null;
if (getActive != null)
{
getActiveValue = ((bool)getActive ? 1 : 0);
}
int getHospitalLevelValue = (getHospitalLevel ? 1 : 0);
OracleDynamicParameters dynamicParameters = new OracleDynamicParameters();
dynamicParameters.Add("p_departmentId", departmetnID, OracleMappingType.Long, ParameterDirection.Input);
dynamicParameters.Add("p_hospitalID", hospitalId, OracleMappingType.Long, ParameterDirection.Input);
dynamicParameters.Add("p_rulesetGUID ", GuidConvert.ToRaw(ruleSetGuid), OracleMappingType.Raw, ParameterDirection.Input);
dynamicParameters.Add("p_rulesetName", searchTerm, OracleMappingType.Varchar2, ParameterDirection.Input, 0, true, 9, 0, String.Empty, DataRowVersion.Current);
dynamicParameters.Add("p_isActive", getActiveValue, OracleMappingType.Int16, ParameterDirection.Input);
dynamicParameters.Add("p_hospitalOnly", getHospitalLevelValue, OracleMappingType.Int16, ParameterDirection.Input);
dynamicParameters.Add("l_ruleSet ", null, OracleMappingType.RefCursor, ParameterDirection.ReturnValue);
return(dynamicParameters);
}
public IEnumerable <ObuhvatSektora> GetObuhvatSektora(string sifraSektora, int mjesec, int godina, string sifraPodsektora)
{
if (sifraPodsektora == "-1")
{
sifraPodsektora = null;
}
if (sifraSektora == "-1")
{
sifraSektora = null;
}
ISqlGenerator <ObuhvatSektora> sqlGenerator = new SqlGenerator <ObuhvatSektora>();
try
{
// Create repository instance
ObuhvatSektoraRepository repository = new ObuhvatSektoraRepository(SqlCon, sqlGenerator);
OracleDynamicParameters param = new OracleDynamicParameters();
param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_zup", null, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_grp", "zup", OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output);
//Call repository method
var items = repository.OracleFunctionCursor("hko.pck_obuhvat.sektor", param);
return(items);
}
catch (Exception ex)
{
throw;
}
}
public async static Task <IDTO> AddUpdateMode(Bank bank)
{
string SPName = "";
string message = "";
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
ComplateOperation <int> complate = new ComplateOperation <int>();
if (bank.ID.HasValue)
{
oracleParams.Add(BankSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)bank.ID ?? DBNull.Value);
SPName = BankSPName.SP_UPADTE_BANCK;
message = "Updated Successfully";
}
else
{
oracleParams.Add(BankSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = BankSPName.SP_INSERT_BANCK;
message = "Inserted Successfully";
}
oracleParams.Add(BankSpParams.PARAMETER_CURRENCY_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.CurrencyCode ?? DBNull.Value, 100);
oracleParams.Add(BankSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Name ?? DBNull.Value, 500);
oracleParams.Add(BankSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Name2 ?? DBNull.Value, 500);
oracleParams.Add(BankSpParams.PARAMETER_PHONE_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.PhoneCode ?? DBNull.Value, 50);
oracleParams.Add(BankSpParams.PARAMETER_PHONE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Phone ?? DBNull.Value, 50);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
return(complate);
}
public async static Task <IDTO> AddUpdateMode(Report report)
{
string SPName = "";
string message = "";
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
ComplateOperation <int> complate = new ComplateOperation <int>();
if (report.ID.HasValue)
{
oracleParams.Add(ReportSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)report.ID ?? DBNull.Value);
SPName = ReportSPName.SP_UPADTE_Report;
message = "Updated Successfully";
}
else
{
oracleParams.Add(ReportSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = ReportSPName.SP_INSERT_Report;
message = "Inserted Successfully";
}
oracleParams.Add(ReportSpParams.PARAMETER_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Code ?? DBNull.Value, 30);
oracleParams.Add(ReportSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Name ?? DBNull.Value, 500);
oracleParams.Add(ReportSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Name2 ?? DBNull.Value, 500);
oracleParams.Add(ReportSpParams.PARAMETER_ORDER_BY, OracleDbType.Int64, ParameterDirection.Input, (object)report.Order ?? DBNull.Value);
oracleParams.Add(ReportSpParams.PARAMETER_GROUPID, OracleDbType.Int64, ParameterDirection.Input, (object)report.ReportGroupID ?? DBNull.Value);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
return(complate);
}
public IEnumerable <ZaposlenostUdjelDTO> GetNezaposlenostUdjeliRodovi(string sifraSektora, int mjesec, int godina, string sifraPodsektora, string zupanijaID)
{
if (sifraPodsektora == "-1")
{
sifraPodsektora = null;
}
if (sifraSektora == "-1")
{
sifraSektora = null;
}
if (zupanijaID == "-1")
{
zupanijaID = null;
}
ISqlGenerator <ZaposlenostUdjel> sqlGenerator = new SqlGenerator <ZaposlenostUdjel>();
try
{
// Create repository instance
ZaposlenostUdjelRepository repository = new ZaposlenostUdjelRepository(SqlCon, sqlGenerator);
OracleDynamicParameters param = new OracleDynamicParameters();
param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_zup", zupanijaID, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output);
//Call repository method
var items = repository.OracleFunctionCursor("hko.pck_sektori.nezaposleni_udjel", param);
var response = ZaposelnostUdjelHandler.MapNezaposlenostUdjelToDto(items);
return(response);
}
catch (Exception ex)
{
throw;
}
}
public ZaposlenostStopa GetZaposlenostStopa(string sifraSektora, int mjesec, int godina, string sifraPodsektora, string zupanijaID)
{
if (sifraPodsektora == "-1")
{
sifraPodsektora = null;
}
if (sifraSektora == "-1")
{
sifraSektora = null;
}
if (zupanijaID == "-1")
{
zupanijaID = null;
}
ISqlGenerator <ZaposlenostStopa> sqlGenerator = new SqlGenerator <ZaposlenostStopa>();
try
{
// Create repository instance
ZaposlenostStopaRepository repository = new ZaposlenostStopaRepository(SqlCon, sqlGenerator);
OracleDynamicParameters param = new OracleDynamicParameters();
param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input);
param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("p_zup", zupanijaID, OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output);
//Call repository method
var items = repository.OracleFunctionCursor("hko.pck_sektori.nezaposleni_stopa", param).First();
//ZaposlenostStopa response = ZaposlenostStopa.MapZaposlenostBrojToDto(items);
return(items);
}
catch (Exception ex)
{
throw;
}
}
public async static Task <IDTO> AddUpdateMode(Department department)
{
string SPName = "";
string message = "";
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
ComplateOperation <int> complate = new ComplateOperation <int>();
if (department.ID.HasValue)
{
oracleParams.Add(DepartmentSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ID ?? DBNull.Value);
SPName = DepartmentSPName.SP_UPADTE_COMPANY_DEPARTMENT;
message = "Updated Successfully";
}
else
{
oracleParams.Add(DepartmentSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = DepartmentSPName.SP_INSERT_COMPANY_DEPARTMENT;
message = "Inserted Successfully";
}
oracleParams.Add(DepartmentSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Name ?? DBNull.Value, 500);
oracleParams.Add(DepartmentSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Name2 ?? DBNull.Value, 500);
oracleParams.Add(DepartmentSpParams.PARAMETER_ADDRESS, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Address ?? DBNull.Value, 30);
oracleParams.Add(DepartmentSpParams.PARAMETER_EMAIL, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Email ?? DBNull.Value, 30);
oracleParams.Add(DepartmentSpParams.PARAMETER_COMPANY_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.CompanyID ?? DBNull.Value);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
return(complate);
}
public async static Task <IDTO> AddUsersToGroup(UserGroup userGroup)
{
string SPName = "";
string message = "";
ComplateOperation <int> complate = new ComplateOperation <int>();
for (int i = 0; i < userGroup.UserIDs.Length; i++)
{
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
if (userGroup.ID.HasValue)
{
oracleParams.Add(UserGroupSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.ID ?? DBNull.Value);
SPName = UserGroupSpName.SP_UPADTE_USER_GROUP;
message = "Updated Successfully";
}
else
{
oracleParams.Add(UserGroupSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = UserGroupSpName.SP_INSER_USER_GROUP;
message = "Inserted Successfully";
}
oracleParams.Add(UserGroupSpParams.PARAMETER_USER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.UserIDs[i] ?? DBNull.Value);
oracleParams.Add(UserGroupSpParams.PARAMETER_USERNAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)userGroup.UserNames[i] ?? DBNull.Value, 30);
oracleParams.Add(UserGroupSpParams.PARAMETER_LOCK_USER_CAT, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.UserRelationID ?? DBNull.Value);
oracleParams.Add(UserGroupSpParams.PARAMETER_REF_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.RefrenceID ?? DBNull.Value);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
}
return(complate);
}
public async Task <ManagerViewModel> UpdateManagerAsync(int id, Manager manager)
{
try
{
var param = new OracleDynamicParameters();
param.Add(name: "MNG_ID", value: id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input);
param.Add(name: "MNG_ROLE_ID", value: manager.Role_Id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input);
param.Add(name: "MNG_FIRSTNAME", value: manager.FirstName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "MNG_LASTNAME", value: manager.LastName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "MNG_EMAIL", value: manager.Email, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "MNG_PASSWORD_HASH", value: manager.Password_Hash, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "MNG_CURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
var query = "ESHOP_MANAGER_API.SP_MANAGER_UPDATEMANAGER";
var conn = GetOpenConnection();
var result = (await SqlMapper.QueryAsync <ManagerViewModel>(conn, query, param: param, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public string ExecuteAgentReplace(string newMobileNo, string exCluster, string newCluster, AgentPhoneCode item)
{
try
{
using (var connection = this.GetConnection())
{
var parameter = new OracleDynamicParameters();
parameter.Add("V_NEW_DISTRIBUTOR_NO", OracleDbType.Varchar2, ParameterDirection.Input, newMobileNo);
parameter.Add("V_EX_CLUSTER", OracleDbType.Varchar2, ParameterDirection.Input, exCluster);
parameter.Add("V_NEW_CLUSTER", OracleDbType.Varchar2, ParameterDirection.Input, newCluster);
parameter.Add("V_AGENT_PHONE", OracleDbType.Varchar2, ParameterDirection.Input, item.AgentPhone);
parameter.Add("MSGID", OracleDbType.Varchar2, ParameterDirection.Input, "999999999");
parameter.Add("V_FLAG", OracleDbType.Double, ParameterDirection.Output);
parameter.Add("OUTMSG", OracleDbType.Varchar2, ParameterDirection.Output, null, 32767);
SqlMapper.Query(connection, dbUser + "SP_Execute_Agent_Replace", param: parameter, commandType: CommandType.StoredProcedure);
this.CloseConnection(connection);
string flag = parameter.oracleParameters[5].Value != null ? parameter.oracleParameters[5].Value.ToString() : null;
string successOrErrorMsg = null;
if (flag == "0")
{
successOrErrorMsg = parameter.oracleParameters[6].Value != null ? parameter.oracleParameters[6].Value.ToString() : null;
}
else
{
successOrErrorMsg = flag;
}
return(successOrErrorMsg);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool Update()
{
try
{
var p = new OracleDynamicParameters();
p.Add("Id", this.Id);
p.Add("Curso", this.Curso.Id);
p.Add("Nombre", this.Nombre);
p.Add("Descripcion", this.Descripcion);
p.Add("Fecha_Viaje", this.Fecha_Viaje);
p.Add("Valor", this.Valor);
Db.Execute(Procs.Contrato_Actualizar, p, commandType: CommandType.StoredProcedure);
logger.Info("Contrato N°{0} actualizado correctamente", Id);
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
public bool Update()
{
try
{
var p = new OracleDynamicParameters();
p.Add("Id", this.Id);
p.Add("ContratoID", this.Contrato.Id);
p.Add("Tipo_Seguro", this.Tipo_Seguro.Id);
p.Add("Valor", this.Valor);
p.Add("SeguroID", this.Seguro);
p.Add("Total_Dias", this.Total_Dias);
Db.Execute(Procs.Seguro_Asociado_Actualizar, p, commandType: CommandType.StoredProcedure);
logger.Info("Seguro asociado N°{0} actualizado correctamente", Id);
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
public async Task <IList <IDepositModel> > GetSomeDeposits(string branch, string openDate, string codeSubject, string fileName, string currency, string accounttype)
{
IList <DepositModel> deposit = null;
try
{
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add("p_branch", OracleDbType.Varchar2, ParameterDirection.Input, "H");
parameters.Add("p_opendate", OracleDbType.Varchar2, ParameterDirection.Input, openDate);
parameters.Add("p_codesubject", OracleDbType.Varchar2, ParameterDirection.Input, "H");
parameters.Add("p_currency", OracleDbType.Varchar2, ParameterDirection.Input, "H");
parameters.Add("p_deposittype", OracleDbType.Varchar2, ParameterDirection.Input, "H");
parameters.Add("RC1", OracleDbType.RefCursor, ParameterDirection.Output);
deposit = await _dataAccess.LoadData <DepositModel, dynamic>("RPT_SEL_DEPOSITSV2_SP", parameters, "OracleConnectionString");
}
catch (Exception e)
{
Utils.NLogMessage(this.GetType(), $" {e.Message} - {e.InnerException}", Utils.NLogType.Error);
}
return(deposit.ToList <IDepositModel>());
}
public async Task <OrderResponse> SaveOrder(Order order)
{
using (var connection = this._connectionFactory.GetConnection)
{
OrderResponse response = new OrderResponse();
if (order.IsDirty)
{
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add("BuyerId", order.BuyerId, dbType: OracleDbType.Int64, direction: ParameterDirection.Input);
parameters.Add("OrderStatusCode", order.OrderStatusCode, dbType: OracleDbType.Int16, direction: ParameterDirection.Input);
parameters.Add("ShippingAddress", order.ShippingAddress, dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
parameters.Add("OrderId", order.OrderId, dbType: OracleDbType.Int64, direction: ParameterDirection.InputOutput);
parameters.Add("EffectiveDate", order.EffectiveDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input);
parameters.Add("ExpiryDate", order.ExpiryDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input);
await connection.QueryAsync <string>(CommonConstants.SaveOrder, param : parameters, commandType : CommandType.StoredProcedure);
response.OrderId = Convert.ToInt32(parameters.Get("OrderId"));
response.Message = CommonConstants.SuccessMessage;
}
else
{
response.OrderId = order.OrderId;
response.Message = CommonConstants.SuccessMessage;
}
order.ItemsList.ForEach(e =>
{
e.OrderId = order.OrderId;
e.StatusCode = order.OrderStatusCode;
e.EffectiveDate = order.EffectiveDate;
});
SaveItems(order.ItemsList.Where(item => item.IsDirty).ToList(), connection);
return(response);
}
}
public async static Task <IDTO> AddUpdateMode(GroupRelation groupRelation)
{
string SPName = "";
string message = "";
ComplateOperation <int> complate = new ComplateOperation <int>();
for (int i = 0; i < groupRelation.RefrenceIDs.Length; i++)
{
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
if (groupRelation.ID.HasValue)
{
oracleParams.Add(GroupRelationSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.ID ?? DBNull.Value);
SPName = GroupRelationSpName.SP_UPADTE_GROUP_RELATION;
message = "Updated Successfully";
}
else
{
oracleParams.Add(GroupRelationSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = GroupRelationSpName.SP_INSERT_GROUP_RELATION;
message = "Inserted Successfully";
}
oracleParams.Add(GroupRelationSpParams.PARAMETER_ST_GRP_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.GroupID ?? DBNull.Value);
oracleParams.Add(GroupRelationSpParams.PARAMETER_GRPNAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)groupRelation.GroupName ?? DBNull.Value, 1000);
oracleParams.Add(GroupRelationSpParams.PARAMETER_LOCK_GRP_CAT, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.LockUpGroupCat ?? DBNull.Value);
oracleParams.Add(GroupRelationSpParams.PARAMETER_REF_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.RefrenceIDs[i] ?? DBNull.Value);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
}
return(complate);
}
public List <PalabraClaveCantidadBE> BuscarPalabrasClavesCantidad(string busqueda, int registros, int pagina, string columna, string orden, OracleConnection db)
{
List <PalabraClaveCantidadBE> lista = new List <PalabraClaveCantidadBE>();
try
{
string sp = $"{Package.Mantenimiento}USP_SEL_LISTA_BUSQ_PALABRA_C";
var p = new OracleDynamicParameters();
p.Add("PI_BUSCAR", busqueda);
p.Add("PI_REGISTROS", registros);
p.Add("PI_PAGINA", pagina);
p.Add("PI_COLUMNA", columna);
p.Add("PI_ORDEN", orden);
p.Add("PO_REF", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
lista = db.Query <PalabraClaveCantidadBE>(sp, p, commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
Log.Error(ex);
}
return(lista);
}
public async Task <int> UpdateCompanyDepartment([FromBody] Department department)
{
var dyParam = new OracleDynamicParameters();
dyParam.Add(Params.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ID ?? DBNull.Value);
dyParam.Add(Params.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.NAME ?? DBNull.Value, 500);
dyParam.Add(Params.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.NAME2 ?? DBNull.Value, 500);
dyParam.Add(Params.PARAMETER_ADDRESS, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.ADDRESS ?? DBNull.Value, 30);
dyParam.Add(Params.PARAMETER_EMAIL, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.EMAIL ?? DBNull.Value, 30);
dyParam.Add(Params.PARAMETER_COMPANY_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ST_COM_ID ?? DBNull.Value, 30);
int result = await _UpdateRepository.UpdateObjectAsync(SPName.SP_UPADTE_COMPANY_DEPARTMENT, dyParam);
if (result == -1)
{
return(Convert.ToInt32(HttpStatusCode.OK));
}
else
{
return(Convert.ToInt32(HttpStatusCode.NotModified));
}
}
public bool Create()
{
try
{
var p = new OracleDynamicParameters();
p.Add("c1", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
p.Add("Curso", this.Curso.Id);
p.Add("Nombre", this.Nombre);
p.Add("Descripcion", this.Descripcion);
p.Add("Fecha_Viaje", this.Fecha_Viaje);
p.Add("Total", this.Valor);
var retorno = Db.QuerySingle <Contrato_Api>(Procs.Contrato_Crear, param: p, commandType: CommandType.StoredProcedure);
this.Id = retorno.Id;
logger.Info("Contrato N°{0} creado correctamente", Id);
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
public bool GuardarDatosArchivo(ExcelBE obj, OracleConnection db)
{
bool seGuardo = false;
try
{
string sp = $"{Package.Excel}USP_PRC_GUARDAR_EXCEL";
var p = new OracleDynamicParameters();
//p.Add("PI_ID_EXCEL", entidad.ID_EXCEL);
p.Add("PI_NOMBRE", obj.NOMBRE);
p.Add("PI_ANIO", obj.ANIO);
p.Add("PI_MES", obj.MES);
p.Add("PI_ID_TIPO_EXCEL", obj.ID_TIPO_EXCEL);
p.Add("PI_UPD_USUARIO", obj.UPD_USUARIO);
p.Add("PO_ROWAFFECTED", dbType: OracleDbType.Int32, direction: ParameterDirection.Output);
db.Execute(sp, p, commandType: CommandType.StoredProcedure);
int filasAfectadas = (int)p.Get <dynamic>("PO_ROWAFFECTED").Value;
seGuardo = filasAfectadas > 0;
}
catch (Exception ex) { Log.Error(ex); }
return(seGuardo);
}
public List <ExcelBE> ListarExcels(int tipoexcel, int registros, int pagina, string columna, string orden, OracleConnection db)
{
List <ExcelBE> lista = new List <ExcelBE>();
try
{
string sp = $"{Package.Excel}USP_SEL_LISTA_EXCEL";
var p = new OracleDynamicParameters();
p.Add("PI_TIPO_EXCEL", tipoexcel);
p.Add("PI_REGISTROS", registros);
p.Add("PI_PAGINA", pagina);
p.Add("PI_COLUMNA", columna);
p.Add("PI_ORDEN", orden);
p.Add("PO_REF", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
lista = db.Query <ExcelBE>(sp, p, commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
Log.Error(ex);
}
return(lista);
}
public async Task <CustomerViewModel> UpdateCustomerAsync(int id, Customer customer)
{
try
{
var param = new OracleDynamicParameters();
param.Add(name: "CTM_ID", value: id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input);
param.Add(name: "CTM_FIRSTNAME", value: customer.FirstName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "CTM_LASTNAME", value: customer.LastName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "CTM_EMAIL", value: customer.Email, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "CTM_PASSWORD_HASH", value: customer.Password_Hash, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
param.Add(name: "CTM_CURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
var query = CUSTOMER_STORED_PACKAGE + ".SP_CUSTOMER_UPDATECUSTOMER";
var conn = GetOpenConnection();
var result = (await SqlMapper.QueryAsync <CustomerViewModel>(conn, query, param: param, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
