public object getDisburseNameCodeList()
{
try
{
using (var connection = this.GetConnection())
{
var parameter = new OracleDynamicParameters();
parameter.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
var result = SqlMapper.Query <CustomDropDownModel>(connection, mainDbUser.DbUser + "SP_GetDisburseAccountDDL", param: parameter, commandType: CommandType.StoredProcedure);
connection.Close();
return(result);
}
}
catch (Exception ex)
{
throw;
}
}
public dynamic executeHMIS(RqGrafana rq)
{
List <HMISModel> result = new List <HMISModel>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.ds_hmis";
result = SqlMapper.Query <HMISModel>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <HMISModel>();
conn.Close();
}
return(result);
}
public async Task <CategoryViewModel> AddCategoryAsync(Category category)
{
try
{
var param = new OracleDynamicParameters();
param.Add(name: "CAT_CURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
param.Add(name: "CAT_NAME", category.Name, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input);
var query = PRODUCT_STORED_PACKAGE + ".SP_PRODUCT_ADDCATEGORY";
var conn = GetOpenConnection();
var result = (await SqlMapper.QueryAsync <CategoryViewModel>(conn, query, param: param, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <TM_RUTA_EMPRESA> Update(TM_RUTA_EMPRESA input)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("num_idrutaxemp_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDRUTAXEMP);
dyParam.Add("num_idruta_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDRUTA);
dyParam.Add("dte_fecinivig_", OracleDbType.Varchar2, ParameterDirection.Input, input.DTE_FECINIVIG.ToBDSirosDate());
dyParam.Add("dte_fecvenvig_", OracleDbType.Varchar2, ParameterDirection.Input, input.DTE_FECVENVIG.ToBDSirosDate());
dyParam.Add("str_numdocauto_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_NUMDOCAUTO);
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_RUTAXEMP.SP_ActualizarRutaxEmp");
var result = await connection.QueryFirstOrDefaultAsync <TM_RUTA_EMPRESA>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result);
}
}
public List <RegistrationReport> GetRegistrationReports(string regStatus, string fromDate, string toDate, string basedOn, string options, string accCategory, string accCategorySub)
{
using (var connection = this.GetConnection())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("REGSTATUS", OracleDbType.Varchar2, ParameterDirection.Input, regStatus);
dyParam.Add("FROMDATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(fromDate));
dyParam.Add("TODATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(toDate));
dyParam.Add("BASEDON", OracleDbType.Varchar2, ParameterDirection.Input, basedOn);
dyParam.Add("OPTIONS", OracleDbType.Varchar2, ParameterDirection.Input, options);
dyParam.Add("ACCCATEGORY", OracleDbType.Varchar2, ParameterDirection.Input, accCategory);
dyParam.Add("ACCCATEGORYSUB", OracleDbType.Varchar2, ParameterDirection.Input, accCategorySub == "All"?null: accCategorySub);
dyParam.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
List <RegistrationReport> result = SqlMapper.Query <RegistrationReport>(connection, dbUser + "RPT_REGISTRATIONDETAILS", param: dyParam, commandType: CommandType.StoredProcedure).ToList();
this.CloseConnection(connection);
return(result);
}
}
protected Guid SaveAndReturnPK(string sql, string pkName, OracleDynamicParameters oracleParameters, IDbConnection connection)
{
try
{
connection.Execute(sql, param: oracleParameters, commandType: CommandType.StoredProcedure);
var returnValueByteArray = oracleParameters.Get <byte[]>(pkName);
var primaryKeyguidId = GuidConvert.FromRaw(returnValueByteArray);
return(primaryKeyguidId);
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex)
{
s_log.ErrorException("SaveAndReturnPK Fails (Oracle Exception)", ex, "BaseStore");
throw new DBOperationException(ex.Message, ex.Number, GlobalOptions.DBExceptionScenarios.OracleExceptionOccured, ex);
}
catch (Exception ex)
{
s_log.ErrorException("SaveAndReturnPK Fails", ex, "BaseStore");
throw new DBOperationException(ex.Message, GlobalOptions.DBExceptionScenarios.ExceptionOccured, ex);
}
}
/*
* get chi tiet so luong thuc tan
*/
public dynamic execureDetailDataReal(BscRequest bscRequest)
{
List <DetailDataReal> result = new List <DetailDataReal>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "kiemsoat.bc_dashboard.detail_sl_thuctang";
result = SqlMapper.Query <DetailDataReal>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <DetailDataReal>();
//insertDetailDataReal(result);
}
return(result);
}
public object GetDistCodeByPmhone(string pmphhone)
{
try
{
using (var connection = this.GetConnection())
{
var parameter = new OracleDynamicParameters();
parameter.Add("PARENT_MPHONE", OracleDbType.Varchar2, ParameterDirection.Input, pmphhone);
parameter.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
var result = SqlMapper.Query <string>(connection, dbUser + "SP_GET_DIST_CODE_BY_PMPHONE", param: parameter, commandType: CommandType.StoredProcedure).FirstOrDefault();
this.CloseConnection(connection);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public bool GuardarResultadoPalabraClaveCantidad(PalabraClaveCantidadBE obj, int idresultado, int idusuario, OracleConnection db)
{
bool seGuardo = false;
try
{
string sp = $"{Package.Filtro}USP_INS_GUARDAR_PALABRA_CANT";
var p = new OracleDynamicParameters();
p.Add("PI_PALABRA_CANTIDAD", obj.PALABRA_CANTIDAD);
p.Add("PI_ID_RESULTADO", idresultado);
p.Add("PI_ID_USUARIO", idusuario);
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);
}
/// <summary>
/// delete employee
/// </summary>
/// <param name="employee">Employee instance</param>
/// <param name="handler">Handler id</param>
/// <returns>the task</returns>
public async Task <Employee> DeteteEmployee(Employee employee, int handler)
{
try
{
var query = QueryResource.Employee_DeleteById;
var dyParam = new OracleDynamicParameters();
dyParam.Add("p_id_nhanvien", OracleDbType.Int64, ParameterDirection.Input, employee.Id_NhanVien);
dyParam.Add("p_id_nv_capnhat", OracleDbType.Int64, ParameterDirection.Input, handler);
dyParam.Add("rsout", OracleDbType.RefCursor, ParameterDirection.Output);
return(await this.DbConnection.QueryFirstOrDefaultAsync <Employee>(
query,
param : dyParam,
commandType : CommandType.StoredProcedure));
}
catch (Exception ex)
{
throw ex;
}
}
public ResponseMessage Set_LOAN_WO(AST_LOAN_WO_STATUS_TEMP loan_wo, string pvc_appuser)
{
var responseMessage = new ResponseMessage();
var dyParam = new OracleDynamicParameters();
dyParam.Add("pvc_area_code", loan_wo.AREA_CODE, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_branch_code", loan_wo.BRANCH_CODE, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_osamount", loan_wo.OS_AMOUNT, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_woamount", loan_wo.WO_AMOUNT, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_wodate", loan_wo.WO_DATE.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture), OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_seg_id", loan_wo.SEG_ID, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_loan_number", loan_wo.LOAN_AC_NUMBER, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_product_code", loan_wo.PRODUCT_CODE, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_appuser", pvc_appuser, OracleMappingType.Varchar2, ParameterDirection.Input, 50);
dyParam.Add("pvc_status", 0, OracleMappingType.Varchar2, ParameterDirection.Output, 5);
dyParam.Add("pvc_statusmsg", 0, OracleMappingType.Varchar2, ParameterDirection.Output, 255);
var res = responseMessage.QueryExecute(Connection, "pkg_asset_manager.dpd_set_loan_wo", dyParam);
return(res);
}
public MonedaBE GetMonedaPorId(MonedaBE entidad)
{
try
{
using (IDbConnection db = new OracleConnection(CadenaConexion))
{
string sp = sPackage + "USP_GET_MONEDA";
var p = new OracleDynamicParameters();
p.Add("pID_MONEDA", entidad.ID_MONEDA);
p.Add("pRefcursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
entidad = db.Query <MonedaBE>(sp, p, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
catch (Exception ex)
{
Log.Error(ex);
}
return(entidad);
}
/// <summary>
/// Логирование sql запросов с параметрами
/// </summary>
/// <param name="stm">Строка запроса</param>
/// <param name="odp">Параметры</param>
protected void LogSqlWithParams(string stm, OracleDynamicParameters odp = null)
{
Regex regex = new Regex(" {0,}\r{0,1}\n{0,1} {2,}");
StringBuilder sb = new StringBuilder();
sb.AppendLine(regex.Replace(stm, " "));
if (odp != null)
{
// т.к. odp.GetParameter NullPointerException приходится использовать рефлексию
Dictionary <string, OracleDynamicParameters.OracleParameterInfo> parameters =
(Dictionary <string, OracleDynamicParameters.OracleParameterInfo>)odp.GetType()
.GetProperty("Parameters", BindingFlags.NonPublic | BindingFlags.Instance)
.GetValue(odp);
foreach (KeyValuePair <string, OracleDynamicParameters.OracleParameterInfo> param in parameters)
{
sb.AppendLine($"{param.Key}: {param.Value.Value}; ");
}
}
logger.Trace(sb.ToString());
}
public List <NescoRpt> NescoDSSReport(string fromDate, string toDate)
{
try
{
using (var connection = this.GetConnection())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("FROMDATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(fromDate));
dyParam.Add("TODATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(toDate));
dyParam.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
List <NescoRpt> result = SqlMapper.Query <NescoRpt>(connection, dbUser + "RPT_NESCO_DSS", param: dyParam, commandType: CommandType.StoredProcedure).ToList();
this.CloseConnection(connection);
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
}
public object GetDistributorListForDDL()
{
try
{
using (var connection = this.GetConnection())
{
var parameter = new OracleDynamicParameters();
parameter.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
var result = SqlMapper.Query <CustomDropDownModel>(connection, dbUser + "SP_GetDistributorListForDDL", param: parameter, commandType: CommandType.StoredProcedure);
this.CloseConnection(connection);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable> QueryDllAsyncUpdate()
{
var dyParam = new OracleDynamicParameters();
dyParam.Add(DynamicColumnsParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)ID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_PRORDUCT_ID, OracleDbType.Int64, ParameterDirection.Input, (object)ProductID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_PRORDUCT_DETAILS_ID, OracleDbType.Int64, ParameterDirection.Input, (object)ProductDetailID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_COLUMN_TYPE, OracleDbType.Int64, ParameterDirection.Input, (object)ColumnType ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_ST_LOB, OracleDbType.Int64, ParameterDirection.Input, (object)LineOfBuisness ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_ST_SUB_LOB, OracleDbType.Int64, ParameterDirection.Input, (object)SubLineOfBuisness ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_ST_CAT_ID, OracleDbType.Int64, ParameterDirection.Input, (object)CategoryID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_EXECLUDE_COL_TYPE, OracleDbType.Int64, ParameterDirection.Input, (object)ExecludedColumn ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_EXECLUDE_UW_DOC_ID, OracleDbType.Int64, ParameterDirection.Input, (object)UnderWritingDocID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_PARENT_ID, OracleDbType.Int64, ParameterDirection.Input, (object)ParentID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_UW_RISK_ID, OracleDbType.Int64, ParameterDirection.Input, (object)UnderWritingRiskID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_LANG_ID, OracleDbType.Decimal, ParameterDirection.Input, (object)LangID ?? DBNull.Value);
dyParam.Add(DynamicColumnsParams.PARAMETER_REF_SELECT, OracleDbType.RefCursor, ParameterDirection.Output);
return(await QueryExecuter.ExecuteQueryAsync <DynamicDdl>(DynamicColumnSpName.SP_LOAD_COLUMNS_UPDATE, dyParam));
}
public List <ApplicationUserReport> GetApplicationUserReports(string branchCode, string userName, string name, string mobileNo, string fromDate, string toDate, string roleId)
{
using (var connection = this.GetConnection())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("V_BRANCHCODE", OracleDbType.Varchar2, ParameterDirection.Input, branchCode == "" ? null : branchCode);
dyParam.Add("FROMDATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(fromDate));
dyParam.Add("TODATE", OracleDbType.Date, ParameterDirection.Input, Convert.ToDateTime(toDate));
dyParam.Add("V_USERNAME", OracleDbType.Varchar2, ParameterDirection.Input, userName == ""?null:userName);
dyParam.Add("V_NAME", OracleDbType.Varchar2, ParameterDirection.Input, name == ""?null:name);
dyParam.Add("V_MOBILENO", OracleDbType.Varchar2, ParameterDirection.Input, mobileNo == ""?null:mobileNo);
dyParam.Add("V_ROLEID", OracleDbType.Varchar2, ParameterDirection.Input, roleId == ""?null: roleId);
dyParam.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
List <ApplicationUserReport> result = SqlMapper.Query <ApplicationUserReport>(connection, dbUser + "RPT_APP_USER", param: dyParam, commandType: CommandType.StoredProcedure).ToList();
this.CloseConnection(connection);
return(result);
}
}
/// <summary>
/// Получение типа контракта
/// </summary>
/// <param name="soid">ИД заказа</param>
/// <param name="strCustomerId">ИД заказчика</param>
/// <returns>Тип контракта</returns>
public TContractType DecodeContractType(string soid, string strCustomerId)
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string flag;
string str = "select internal_customer_flag " +
"from customer_catalog " +
"where customer_descr_id = :p_customer_descr_id";
odp.Add("p_customer_descr_id", Convert.ToInt32(strCustomerId));
using (OracleConnection connection = GetConnection())
{
LogSqlWithParams(str, odp);
flag = connection.ExecuteScalar <string>(str, odp);
}
if (flag[0] == 'Y' || soid.Substring(0, 2) == "OMK")
{
return(TContractType.coInternal);
}
return(TContractType.coContract);
}
public async Task <TM_CONTRATOES> Update(TM_CONTRATOES input)
{
using (var connection = _connectionFactory.GetConnectionSIROS())
{
var dyParam = new OracleDynamicParameters();
dyParam.Add("num_idestservicioxent_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDESTSERVICIOXENT);
dyParam.Add("str_numcontrato_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_NUMCONTRATO);
dyParam.Add("dte_feccontrato_", OracleDbType.Varchar2, ParameterDirection.Input, input.DTE_FECCONTRATO.ToBDSirosDate());
dyParam.Add("dte_fecinivig_", OracleDbType.Varchar2, ParameterDirection.Input, input.DTE_FECINIVIG.ToBDSirosDate());
dyParam.Add("dte_fecvenvig_", OracleDbType.Varchar2, ParameterDirection.Input, input.DTE_FECVENVIG.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_CONTRATOES.SP_ActualizarContratoES");
var result = await connection.QueryFirstOrDefaultAsync <TM_CONTRATOES>(query, param : dyParam, commandType : CommandType.StoredProcedure);
return(result);
}
}
public bool Read(int id)
{
try
{
var p = new OracleDynamicParameters();
p.Add("Id", id);
p.Add("c1", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var result = Db.QuerySingle <dynamic>(Procs.Servicio_Asociado_Por_Id, param: p, commandType: CommandType.StoredProcedure);
Contrato_Api contrato = new Contrato_Api();
contrato.Read((int)result.CONTRATOID);
Servicio_Api servicio = new Servicio_Api();
servicio.Read((int)result.SERVICIOID);
Id = (int)result.ID;
Contrato = new Contrato()
{
Id = contrato.Id,
Curso = contrato.Curso,
Nombre = contrato.Nombre,
Descripcion = contrato.Descripcion,
Fecha_Viaje = contrato.Fecha_Viaje,
Valor = contrato.Valor
};
Servicio = new Servicio()
{
Id = servicio.Id,
Nombre = servicio.Nombre,
Descripcion = servicio.Descripcion,
Valor = servicio.Valor
};
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
public bool Read(int id)
{
try
{
var p = new OracleDynamicParameters();
p.Add("Id", id);
p.Add("c1", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var result = Db.QuerySingle <dynamic>(Procs.Pago_Actividad_Por_Id, param: p, commandType: CommandType.StoredProcedure);
Pago_Api pago = new Pago_Api();
pago.Read((int)result.PAGOID);
Actividad_Asociada_Api actividad_asociada = new Actividad_Asociada_Api();
actividad_asociada.Read((int)result.ACTIVIDAD_ASIGNADAID);
Id = (int)result.ID;
Pago = new Pago()
{
Id = pago.Id,
Alumno = pago.Alumno,
Valor_Pago = pago.Valor_Pago,
Total_Cuenta = pago.Total_Cuenta,
Fecha_Pago = pago.Fecha_Pago
};
Actividad_Asignada = new Actividad_Asociada()
{
Id = actividad_asociada.Id,
Actividad = actividad_asociada.Actividad,
Curso = actividad_asociada.Curso,
Total_Recaudado = actividad_asociada.Total_Recaudado,
Prorrateo = actividad_asociada.Prorrateo
};
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
}
// con esta funcion registras 10 estudiantes al azar
public async Task <bool> AddEstudianteAutomatic()
{
object result = null;
try
{
var dyParam = new OracleDynamicParameters();
using (IDbConnection con = GetConnection())
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (con.State == ConnectionState.Open)
{
var _estudiantes = GeneradorEstudiante();
foreach (Estudiante nuevoInsert in _estudiantes)
{
dyParam.Add("ci", OracleDbType.Varchar2, ParameterDirection.Input,
nuevoInsert.CI);
dyParam.Add("nombres", OracleDbType.Varchar2, ParameterDirection.Input,
nuevoInsert.NOMBRES);
dyParam.Add("apellidos", OracleDbType.Varchar2, ParameterDirection.Input,
nuevoInsert.APELLIDOS);
dyParam.Add("fechaNacimiento", OracleDbType.Date, ParameterDirection.Input,
nuevoInsert.FECHA_NACIMIENTO);
result = await AddEstudianteAsync(nuevoInsert);
dyParam = new OracleDynamicParameters();
}
}
}
}
catch (Exception err)
{
throw new Exception(err.Message);
}
return(true);
}
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 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 List <MedidaMitigacionBE> ListarMedidaMitigacion(MedidaMitigacionBE entidad)
{
List <MedidaMitigacionBE> Lista = null;
try
{
using (IDbConnection db = new OracleConnection(CadenaConexion))
{
string sp = sPackage + "USP_SEL_LISTA_MEDIDAMITIGACION";
var p = new OracleDynamicParameters();
p.Add("pRefcursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
Lista = db.Query <MedidaMitigacionBE>(sp, p, commandType: CommandType.StoredProcedure).ToList();
foreach (var item in Lista)
{
if (item.ID_NAMA == 1)
{
item.COLOR_NAMA = "amarilla";
}
else if (item.ID_NAMA == 2)
{
item.COLOR_NAMA = "roja";
}
else if (item.ID_NAMA == 3)
{
item.COLOR_NAMA = "verde";
}
else if (item.ID_NAMA == 4)
{
item.COLOR_NAMA = "azul";
}
}
}
}
catch (Exception ex)
{
Log.Error(ex);
}
return(Lista);
}
public IEnumerable <KljucneDjelatnosti> GetKljucneDjelatnosti(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 <KljucneDjelatnosti> sqlGenerator = new SqlGenerator <KljucneDjelatnosti>();
try
{
// Create repository instance
KljucneDjelatnostiRepository repository = new KljucneDjelatnostiRepository(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("p_grp", "djel", OracleDbType.Varchar2, ParameterDirection.Input, 32000);
param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output);
//Call repository method
var items = repository.OracleFunctionCursor("hko.pck_obuhvat.kljdjel", param);
return(items);
}
catch (Exception ex)
{
throw;
}
}
public dynamic getAllCenter(UnitRequest rq)
{
List <Unit> ttvt = new List <Unit>();
List <Unit> ttkd = new List <Unit>();
List <Unit> execute = new List <Unit>();
List <Unit> result = new List <Unit>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.getTTVT";
execute = SqlMapper.Query <Unit>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <Unit>();
}
foreach (var e in execute)
{
if (e.donvi_id > 40 && e.donvi_id < 62)
{
ttvt.Add(e);
}
else if (e.donvi_id < 41 || e.donvi_id > 61)
{
ttkd.Add(e);
}
}
if (rq.target.ToLower() == "ttvt")
{
result = ttvt;
}
else if (rq.target.ToLower() == "ttkd")
{
result = ttkd;
}
return(result);
}
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(CustomerType customerType)
{
string SPName = "";
string message = "";
ComplateOperation <int> complate = new ComplateOperation <int>();
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
if (customerType.ID.HasValue)
{
oracleParams.Add(CustomerTypeSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)customerType.ID ?? DBNull.Value);
SPName = CustomerTypesSpName.SP_UPADTE_CUSTOMER_TYPE;
message = "Updated Successfully";
}
else
{
oracleParams.Add(CustomerTypeSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = CustomerTypesSpName.SP_INSERT_CUSTOMER_TYPE;
message = "Inserted Successfully";
}
oracleParams.Add(CustomerTypeSpParams.PARAMETER_FIN_CST_ID, OracleDbType.Int64, ParameterDirection.Input, (object)customerType.CustomerID ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_LOC_CUST_TYPE, OracleDbType.Int64, ParameterDirection.Input, (object)customerType.LocCustomerType ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_CREATED_BY, OracleDbType.Varchar2, ParameterDirection.Input, (object)customerType.CreatedBy ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_CREATION_DATE, OracleDbType.Date, ParameterDirection.Input, (object)customerType.CreationDate ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_MODIFIED_BY, OracleDbType.Varchar2, ParameterDirection.Input, (object)customerType.ModifiedBy ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_MODIFICATION_DATE, OracleDbType.Date, ParameterDirection.Input, (object)customerType.ModificationDate ?? DBNull.Value);
oracleParams.Add(CustomerTypeSpParams.PARAMETER_FIN_GL_ID, OracleDbType.Int64, ParameterDirection.Input, (object)customerType.FinGlID ?? DBNull.Value);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
return(complate);
}
public async static Task <IDTO> AddUpdateMode(Country country)
{
string SPName = "";
string message = "";
OracleDynamicParameters oracleParams = new OracleDynamicParameters();
ComplateOperation <int> complate = new ComplateOperation <int>();
if (country.ID.HasValue)
{
oracleParams.Add(CountrySpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)country.ID ?? DBNull.Value);
SPName = CountrySPName.SP_UPADTE_COUNTRY;
message = "Updated Successfully";
}
else
{
oracleParams.Add(CountrySpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output);
SPName = CountrySPName.SP_INSER_COUNTRY;
message = "Inserted Successfully";
}
oracleParams.Add(CountrySpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.Name ?? DBNull.Value, 500);
oracleParams.Add(CountrySpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.Name2 ?? DBNull.Value, 500);
oracleParams.Add(CountrySpParams.PARAMETER_COUNTRY_NATIONALITY, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.Nationality ?? DBNull.Value, 100);
oracleParams.Add(CountrySpParams.PARAMETER_COUNTRY_CURRENCY_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.CurrencyCode ?? DBNull.Value, 30);
oracleParams.Add(CountrySpParams.PARAMETER_REFERNCE_NO, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.ReferenceNo ?? DBNull.Value, 500);
oracleParams.Add(CountrySpParams.PARAMETER_LOC_STATUS, OracleDbType.Int64, ParameterDirection.Input, (object)country.Status ?? DBNull.Value);
oracleParams.Add(CountrySpParams.PARAMETER_STATUS_DATE, OracleDbType.Date, ParameterDirection.Input, (object)country.StatusDate ?? DBNull.Value);
oracleParams.Add(CountrySpParams.PARAMETER_PHONE_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.PhoneCode ?? DBNull.Value, 50);
oracleParams.Add(CountrySpParams.PARAMETER_COUNTRY_FLAG, OracleDbType.Varchar2, ParameterDirection.Input, (object)country.Flag ?? DBNull.Value, 500);
if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1)
{
complate.message = message;
}
else
{
complate.message = "Operation Failed";
}
return(complate);
}
