public static List<Publicacion> GetAllActiveByParameters(String description, List<Rubro> lstRubros)
{
//Obtengo la publicacion que se encuentren activas y cumplan ciertos criterios (busqueda exacta)
var param = new List<SPParameter>
{
new SPParameter("Fecha_hoy", Configuration.ConfigurationVariables.FechaSistema),
new SPParameter("Descripcion", description)
};
var sp = new StoreProcedure(DataBaseConst.Publicacion.SPGetAllActiveByParameters, param);
var publications = sp.ExecuteReader<Publicacion>();
if (publications == null)
return null;
var result = new List<Publicacion>();
foreach (var pub in publications)
{
pub.GetObjectsById();
if (pub.Rubros.Count == lstRubros.Count && pub.Rubros.Select(i => i.ID).Intersect(lstRubros.Select(j => j.ID)).Count() == pub.Rubros.Count)
result.Add(pub);
}
return result;
}
/// <summary>
/// Metodo que permite cargar los StoreProcedures en el arbol.
/// </summary>
public void LoadTreeView()
{
if (_StoreProcedures == null)
LoadFromDataBase();
ProgressBar.Visible = true;
ProgressBar.Maximum = _StoreProcedures.Count + 1;
ProgressBar.Value = 0;
TreeViewHelper.AddElementEvent += new AddElementHandler(TreeViewHelper_AddElementEvent);
lblTreeViewSelected.Text = "Generando arbol..";
if (_StoreProcedures == null)
{
this.tvwChilds.Nodes.Clear();
TreeViewHelper.AddElementEvent -= new AddElementHandler(TreeViewHelper_AddElementEvent);
return;
}
if (_StoreProcedures.Count == 0)
{
this.tvwChilds.Nodes.Clear();
TreeViewHelper.AddElementEvent -= new AddElementHandler(TreeViewHelper_AddElementEvent);
return;
}
TreeViewHelper.LoadTreeView(this.tvwChilds, _StoreProcedures);
_SelectedStoreProcedure = _StoreProcedures[0];
_SelectedStoreProcedureName = _StoreProcedures[0].Name;
lblTreeViewSelected.Text = String.Empty;
ProgressBar.Visible = false;
ProgressBar.Value = 0;
}
public static List<HistoryOferta> GetAllOfertasByUsuario(Usuario user)
{
var param = new List<SPParameter> { new SPParameter("idUsuario", user.ID),
new SPParameter("Fecha_hoy", ConfigurationVariables.FechaSistema)};
var sp = new StoreProcedure(DataBaseConst.Oferta.SPGetHistoryOfertasByUsuario, param);
return sp.ExecuteReader<HistoryOferta>();
}
public static List<Funcionalidad> GetAll()
{
//Obtengo todas las funcionalidades almacenadas en la base de datos
var sp = new StoreProcedure(DataBaseConst.Funcionalidad.SPGetAllFuncionalidad);
//Retorno una lista de Funcionalidad a partir de un ExecuteReader
return sp.ExecuteReader<Funcionalidad>();
}
public static object ObtenerTodas()
{
//Obtengo todas las funcionalidades almacenadas en la base de datos
var sp = new StoreProcedure(DBQueries.Funcionalidad.SPGetFuncionalidades);
//Retorno una lista de Funcionalidad a partir de un ExecuteReader
return sp.ExecuteReader<Funcionalidad>();
}
public static List<Rol> GetByUser(Usuario user)
{
//Obtengo la lista de roles asignadas a determinado usuario
var param = new List<SPParameter> { new SPParameter("ID_User", user.ID) };
var sp = new StoreProcedure(DataBaseConst.Rol.SPGetAllRolByUser, param);
return sp.ExecuteReader<Rol>();
}
public static int GetUltimoNumeroFactura(SqlTransaction transaction)
{
var sp = new StoreProcedure(DataBaseConst.Factura.SPGetUltimoNumeroFactura);
var numeroFactura = Convert.ToInt32(sp.ExecuteScalar(transaction));
return numeroFactura;
}
public static List<Funcionalidad> ObtenerPorRol(Rol rol)
{
//Obtengo todas las funcionalidades asociadas a determinado rol
var param = new List<SPParameter> { new SPParameter("ID_Rol", rol.ID) };
var sp = new StoreProcedure(DBQueries.Funcionalidad.SPGetFuncionalidadesPorRol, param);
//Retorno una lista de Funcionalidad a partir de un ExecuteReader
return sp.ExecuteReader<Funcionalidad>();
}
public static List<Servicio> ObtenerServiciosDeRuta(int origen,int destino,string matricula)
{
var param = new List<SPParameter> {
new SPParameter("ID_Ciudad_Origen", origen),
new SPParameter("ID_Ciudad_Destino", destino),
new SPParameter("Matricula", matricula) };
var sp = new StoreProcedure(DBQueries.Servicio.SPObtenerServiciosDeRuta,param);
return sp.ExecuteReader<Servicio>();
}
public static List<Funcionalidad> GetByRole(Rol role, SqlTransaction transaction)
{
//Obtengo todas las funcionalidades asociadas a determinado rol (transaccionado)
var param = new List<SPParameter> { new SPParameter("ID_Rol", role.ID) };
var sp = new StoreProcedure(DataBaseConst.Funcionalidad.SPGetAllFuncionalidadByRol, param, transaction);
//Retorno una lista de Funcionalidad a partir de un ExecuteReader transaccionado
return sp.ExecuteReaderTransactioned<Funcionalidad>(transaction);
}
public static List<Empresa> GetAllBusinessByParametersLike(EmpresaFilters filters)
{
var param = new List<SPParameter> { new SPParameter("Razon_Social", filters.RazonSocial ?? (object)DBNull.Value),
new SPParameter("Cuit", filters.Cuit ?? (object)DBNull.Value),
new SPParameter("Email", filters.Email ?? (object)DBNull.Value)};
var sp = new StoreProcedure(DataBaseConst.Empresa.SPGetAllBusinessByParametersLike, param);
return sp.ExecuteReader<Empresa>();
}
public static void InhabilitarUser(Usuario user)
{
var param = new List<SPParameter>
{
new SPParameter("ID_User", user.ID)
};
var sp = new StoreProcedure(DBQueries.Usuario.SPInhabilitarUsuario, param);
sp.ExecuteNonQuery(null);
}
public static void LimpiarIntentos(Usuario user)
{
var param = new List<SPParameter>
{
new SPParameter("ID_User", user.ID)
};
var sp = new StoreProcedure(DBQueries.Usuario.SPLimpiarIntentos, param);
sp.ExecuteNonQuery(null);
}
public static void CambiarContrasena(Usuario user, string password)
{
var param = new List<SPParameter>
{
new SPParameter("ID_User", user.ID),
new SPParameter("Password", password)
};
var sp = new StoreProcedure(DBQueries.Usuario.SPActualizarContrasena, param);
sp.ExecuteNonQuery(null);
}
public static void ChangePassword(Usuario user, string password)
{
var param = new List<SPParameter>
{
new SPParameter("ID_User", user.ID),
new SPParameter("Password", password)
};
var sp = new StoreProcedure(DataBaseConst.Usuario.SPUpdatePassword, param);
sp.ExecuteNonQuery(null);
}
public static int ObtenerButacasDisponibles(int p)
{
var param = new List<SPParameter>
{
new SPParameter("ID_Viaje",p)
};
var sp = new StoreProcedure(DBQueries.Viaje.SPObtenerButacasDisponibles, param);
return (int)sp.ExecuteScalar(null);
}
public static void ActualizarFechaLlegada(int idViaje, DateTime fechaLlegada)
{
var param = new List<SPParameter>
{
new SPParameter("ID", idViaje),
new SPParameter("FechaLlegada", fechaLlegada),
};
var sp = new StoreProcedure(DBQueries.Viaje.SPActualizarFechaLlegada, param);
sp.ExecuteNonQuery(null);
}
public static int DarDeBajaPorVidaUtil(Aeronave aeronave)
{
var param = new List<SPParameter>
{
new SPParameter("ID_Aeronave", aeronave.ID),
};
var sp = new StoreProcedure(DBQueries.Aeronave.SPDarDeBajaPorVidaUtil, param);
return sp.ExecuteNonQuery(null);
}
public static Oferta GetLastOfertaByPublication(int idPublicacion, SqlTransaction transaction)
{
var param = new List<SPParameter> { new SPParameter("idPublicacion", idPublicacion)};
var sp = new StoreProcedure(DataBaseConst.Oferta.SPGetLastOfertaByPublication, param, transaction);
var offers = (transaction == null) ? sp.ExecuteReader<Oferta>() : sp.ExecuteReaderTransactioned<Oferta>(transaction);
if (offers == null || offers.Count == 0)
return null;
return offers[0];
}
public static int InsertarCiudad(Ciudad ciudadNueva)
{
var param = new List<SPParameter>
{
new SPParameter("Nombre_Ciudad", ciudadNueva.Nombre),
};
var sp= new StoreProcedure(DBQueries.Ciudad.SPInsertarCiudad, param);
return sp.ExecuteNonQuery(null);
}
public static Empresa GetByBusinessName(string businessName, SqlTransaction transaction)
{
var param = new List<SPParameter> { new SPParameter("Razon_Social", businessName) };
var sp = new StoreProcedure(DataBaseConst.Empresa.SPGetCompanyByBusinessName, param, transaction);
var users = sp.ExecuteReaderTransactioned<Empresa>(transaction);
if (users == null || users.Count == 0)
return null;
return users[0];
}
public static Empresa GetByCUIT(string cuit)
{
var param = new List<SPParameter> { new SPParameter("CUIT", cuit) };
var sp = new StoreProcedure(DataBaseConst.Empresa.SPGetCompanyByCUIT, param);
var users = sp.ExecuteReader<Empresa>();
if (users == null || users.Count == 0)
return null;
return users[0];
}
public static TipoButaca ObtenerTipoPorDescripcion(string tipo, SqlTransaction transaction)
{
var param = new List<SPParameter> { new SPParameter("Tipo", tipo)};
var sp = new StoreProcedure(DBQueries.TipoButaca.SPGetTipoPorDescripcion, param, transaction);
var tipos = sp.ExecuteReaderTransactioned<TipoButaca>(transaction);
if (tipos == null || tipos.Count == 0)
return null;
return tipos[0];
}
public static Butaca ObtenerIdTipoPorDescripcion(string descripcion, SqlTransaction transaction)
{
var param = new List<SPParameter> { new SPParameter("Descripcion", descripcion) };
var sp = new StoreProcedure(DBQueries.Butaca.SPGetIdTipoPorDescripcion, param, transaction);
var butacas = sp.ExecuteReaderTransactioned<Butaca>(transaction);
if (butacas == null || butacas.Count == 0)
return null;
return butacas[0];
}
public static List<Rubro> GetByPublicationId(int publicationID)
{
var param = new List<SPParameter> { new SPParameter("ID_Publicacion", publicationID) };
var sp = new StoreProcedure(DataBaseConst.Rubro.SPGetAllRubroByPublicationId, param);
var rubros = sp.ExecuteReader<Rubro>();
if (rubros == null || rubros.Count == 0)
return null;
return rubros;
}
public static void ActualizarPorContrasena(Usuario user)
{
var param = new List<SPParameter>
{
new SPParameter("ID_User", user.ID),
new SPParameter("Cant_Intentos", user.CantIntentos),
new SPParameter("Habilitado", user.Habilitado)
};
var sp = new StoreProcedure(DBQueries.Usuario.SPActualizarUsuarioPorContraIncorrecta, param);
sp.ExecuteNonQuery(null);
}
public static List<Cliente> GetAllClientsByParametersLike(ClienteFilters filters)
{
var param = new List<SPParameter> { new SPParameter("Nombre", filters.Nombre ?? (object)DBNull.Value),
new SPParameter("Apellido", filters.Apellido ?? (object)DBNull.Value),
new SPParameter("Tipo_Documento", filters.TipoDocumento ?? (object)DBNull.Value),
new SPParameter("Nro_Documento", filters.NroDocumento ?? (object)DBNull.Value),
new SPParameter("Email", filters.Email ?? (object)DBNull.Value)};
var sp = new StoreProcedure(DataBaseConst.Cliente.SPGetAllClientsByParametersLike, param);
return sp.ExecuteReader<Cliente>();
}
public static FormaPago GetById(int idPaymentMethod, SqlTransaction transaction)
{
var param = new List<SPParameter> { new SPParameter("ID_Forma_Pago", idPaymentMethod) };
var sp = new StoreProcedure(DataBaseConst.FormaPago.SPGetAllFormaPagoById, param, transaction);
var statusPublication = sp.ExecuteReaderTransactioned<FormaPago>(transaction);
if (statusPublication == null || statusPublication.Count == 0)
return null;
return statusPublication[0];
}
public static Cliente GetByDocument(int tipoDocumento, int nroDocumento)
{
var param = new List<SPParameter> { new SPParameter("Tipo_documento", tipoDocumento), new SPParameter("Nro_documento", nroDocumento) };
var sp = new StoreProcedure(DataBaseConst.Cliente.SPGetClientByDocument, param);
var users = sp.ExecuteReader<Cliente>();
if (users == null || users.Count == 0)
return null;
return users[0];
}
public static List<HistoryReputacion> getAllCalifiedToMeByParametersLike(Usuario user, HistoryReputacionFilters filters)
{
var param = new List<SPParameter> { new SPParameter("idUsuario", user.ID),
new SPParameter("Codigo_Calificacion", filters.Codigo ?? (object)DBNull.Value),
new SPParameter("Descripcion", filters.Descripcion ?? (object)DBNull.Value),
new SPParameter("Cantidad_Estrellas", filters.Cantidad ?? (object)DBNull.Value),
new SPParameter("Nombre", filters.Nombre ?? (object)DBNull.Value)
};
var sp = new StoreProcedure(DataBaseConst.Calificacion.SPGetHistoryCalificacionesRecibidasByParametersLike, param);
return sp.ExecuteReader<HistoryReputacion>();
}
public decimal SaleAmount(int id, int type)
{
decimal amount = 0;
SqlParameter[] param =
{
new SqlParameter("@Id", id),
new SqlParameter("@type", type),
};
StoreProcedure sp = new StoreProcedure("P_getSalesAmount", param);
var data = _databaseInstance.GetTables <AmountModel>(sp);
if (data != null && data.ModelList != null)
{
var item = data.ModelList.FirstOrDefault();
if (item != null)
{
amount = item.amount;
}
}
return(amount);
}
/// <summary>
/// Carga todos los store procedures de una base de datos .-
/// </summary>
/// <param name="pLoadParameters">Indica si se cargan los parametros y text del Store Procedure .-</param>
/// <returns></returns>
internal StoreProcedures LoadStoreProcedures(bool pLoadParameters)
{
StoreProcedure wStoreProcedure = null;
StoreProcedures wStoreProcedures = new StoreProcedures();
DataSet wDtsStores = null;
try
{
wDtsStores = GetStoredProcedureFromDatabase();
foreach (DataRow wDtr in wDtsStores.Tables[0].Rows)
{
wStoreProcedure = new StoreProcedure();
wStoreProcedure.Name = wDtr["Name"].ToString();
wStoreProcedure.Schema = wDtr["Schema"].ToString();
if (pLoadParameters == true)
{
FillParametesAndText(wStoreProcedure);
}
wStoreProcedures.Add(wStoreProcedure);
}
wStoreProcedures.IsLoaded = true;
wDtsStores.Dispose();
wDtsStores = null;
OnAddElementEvent();
return(wStoreProcedures);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Metodo que permite cargar los StoreProcedures en el arbol.
/// </summary>
public void LoadTreeView()
{
if (_StoreProcedures == null)
{
LoadFromDataBase();
}
ProgressBar.Visible = true;
ProgressBar.Maximum = _StoreProcedures.Count + 1;
ProgressBar.Value = 0;
TreeViewHelper.AddElementEvent += new AddElementHandler(TreeViewHelper_AddElementEvent);
lblTreeViewSelected.Text = "Generando arbol..";
if (_StoreProcedures == null)
{
this.tvwChilds.Nodes.Clear();
TreeViewHelper.AddElementEvent -= new AddElementHandler(TreeViewHelper_AddElementEvent);
return;
}
if (_StoreProcedures.Count == 0)
{
this.tvwChilds.Nodes.Clear();
TreeViewHelper.AddElementEvent -= new AddElementHandler(TreeViewHelper_AddElementEvent);
return;
}
TreeViewHelper.LoadTreeView(this.tvwChilds, _StoreProcedures);
_SelectedStoreProcedure = _StoreProcedures[0];
_SelectedStoreProcedureName = _StoreProcedures[0].Name;
lblTreeViewSelected.Text = String.Empty;
ProgressBar.Visible = false;
ProgressBar.Value = 0;
}
/// <summary>
/// Get Username from database
/// </summary>
/// <param name="userID">Username is ignore case</param>
/// <returns></returns>
public tb_User GetUserInfo(string userID)
{
return(StoreProcedure.sp_Common_Get_User(AppEnvironment.Database, userID));
}
public void DeleteCostType(string CostTypeCode)
{
StoreProcedure.sp_ACS110_DeleteCostType(AppEnvironment.Database, CostTypeCode);
}
public DataTable GetJournalEntryData(int year, int period)
{
return(StoreProcedure.sp_ACS330_Get_JournalEntryData(AppEnvironment.Database, year, period));
}
public bool CheckUserPassword(string userID, string password)
{
return(StoreProcedure.sp_Common_CheckUserPassword(AppEnvironment.Database, userID, password));
}
public DataTable RetrieveData(int year)
{
return(StoreProcedure.sp_ACS340_RetrieveData(AppEnvironment.Database, year));
}
public void DeleteMappingData(string AccCode, string CostType)
{
StoreProcedure.sp_ACS120_DeleteMappingData(AppEnvironment.Database, AccCode, CostType);
}
public Response GetClientValitated(string email, string password)
{
try
{
StoreProcedure storeProcedure = new StoreProcedure("weco.CLIENTE_GetClientValitated");
storeProcedure.AddParameter("@CLIE_CORREO_ELECTRONICO_VC", email);
storeProcedure.AddParameter("@CLIE_CLAVE_VC", password);
DataTable dataTable = storeProcedure.ReturnData(_connection, _timeOut);
Logger.Debug("StoreProcedure: {0} DataTable: {1}", SerializeJson.ToObject(storeProcedure), SerializeJson.ToObject(dataTable));
if (storeProcedure.Error.Length <= 0)
{
if (dataTable.Rows.Count > 0)
{
if (dataTable.Rows[0]["RESULTADO"].ToString().Equals("00"))
{
Core.Entity.Client result = new Core.Entity.Client
{
NameComplete = dataTable.Rows[0]["CLIE_NOMBRE_COMPLETO_VC"].ToString(),
Email = dataTable.Rows[0]["CLIE_CORREO_ELECTRONICO_VC"].ToString(),
Password = dataTable.Rows[0]["CLIE_CLAVE_VC"].ToString(),
Role = Convert.ToInt32(dataTable.Rows[0]["CLIE_ROL_IN"]) == 0 ? Role.Admin : Role.User,
IsVerified = Convert.ToBoolean(dataTable.Rows[0]["CLIE_ESTADO_VERIFICACION_BT"]),
CompleteRegister = Convert.ToBoolean(dataTable.Rows[0]["CLIE_REGISTRO_COMPLETO_BT"]),
CurrentModule = Convert.ToInt32(dataTable.Rows[0]["MODULO_ACTUAL"])
};
if (BC.Verify(password, result.Password))
{
return(Response.Success(result));
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("NotLogin"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "NotLogin"));
}
}
else
{
if (dataTable.Rows[0]["RESULTADO"].ToString().Equals("02"))
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("AccountNotValidated"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "AccountNotValidated"));
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("NotLogin"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "NotLogin"));
}
}
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("NotLogin"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "NotLogin"));
}
}
else
{
Logger.Error("Message: {0}; StoreProcedure.Error: {1}", Response.CommentMenssage("Sql"), storeProcedure.Error);
return(Response.Error(storeProcedure.Error, "Sql"));
}
}
catch (Exception ex)
{
Logger.Error("Message: {0} Exception: {1}", ex.Message, SerializeJson.ToObject(ex));
return(Response.Error(ex, "Error"));
}
}
public string CheckUseCostType(string CostTypeCode)
{
return(StoreProcedure.sp_ACS110_CheckUseCostType(AppEnvironment.Database, CostTypeCode));
}
public DataTable RetrievePrdData(string srchAccCode, string srchAccName)
{
return(StoreProcedure.sp_ACS120_SearchAccount(AppEnvironment.Database, srchAccCode, srchAccName));
}
public Response GetWinners()
{
try
{
StoreProcedure storeProcedure = new StoreProcedure("weco.CLIENTE_GetWinnerbyModule");
DataTable dataTable = storeProcedure.ReturnData(_connection, _timeOut);
Logger.Debug("StoreProcedure: {0} DataTable: {1}", SerializeJson.ToObject(storeProcedure), SerializeJson.ToObject(dataTable));
if (storeProcedure.Error.Length <= 0)
{
if (dataTable.Rows.Count > 0)
{
if (dataTable.Rows[0]["RESULTADO"].ToString().Equals("00"))
{
WinnersdResponse result = new WinnersdResponse();
result.Winners.AddRange(from DataRow dataRow in dataTable.Rows
let winner = new WinnersdResponse.Winner()
{
Position = Convert.ToInt32(dataRow["POSITION"]),
Email = dataRow["CLIE_CORREO_ELECTRONICO_VC"].ToString(),
Ci = dataRow["CLIE_CI_VC"].ToString(),
NameComplete = dataRow["CLIE_NOMBRE_COMPLETO_VC"].ToString(),
Gender = dataRow["CLIE_GENERO_VC"].ToString(),
Birthdate = Convert.ToDateTime(dataRow["CLIE_FECHA_NACIMIENTO_DT"]),
Age = Convert.ToInt32(dataRow["CLIE_EDAD_IN"]),
Department = dataRow["CLIE_DEPARTAMENTO_VC"].ToString(),
City = dataRow["CLIE_CIUDAD_VC"].ToString(),
Address = dataRow["CLIE_DIRECCION_VC"].ToString(),
CellPhone = dataRow["CLIE_NUMERO_CELULAR_VC"].ToString(),
Phone = Convert.ToString(dataRow["CLIE_NUMERO_FIJO_VC"]),
EducationLevel = dataRow["CLIE_NIVEL_EDUCACION_VC"].ToString(),
Disability = Convert.ToBoolean(dataRow["CLIE_DISCAPACIDAD_BT"]),
TypeDisability = Convert.ToString(dataRow["CLIE_TIPO_DISCAPACIDAD_VC"]),
ReferenceName = Convert.ToString(dataRow["CLIE_NOMBRE_REFERENCIA_VC"]),
ReferenceCellphone = Convert.ToString(dataRow["CLIE_CELULAR_REFERENCIA_VC"]),
Role = Convert.ToInt32(dataTable.Rows[0]["CLIE_ROL_IN"]) == 1 ? "User" : "Admin",
CompleteRegister = Convert.ToBoolean(dataRow["CLIE_REGISTRO_COMPLETO_BT"]),
CurrentModule = Convert.ToInt32(dataRow["MODULO_ACTUAL"]),
NumberModuleFinished = Convert.ToInt32(dataRow["NUMERO_MODULOS_TERMINADOS"]),
ModulesFinishComplete = dataRow["MODULOS_TERMINADOS"].ToString()
}
select winner);
List <WinnersdResponse.Winner.FinishedModule> addfinishedModules = new List <WinnersdResponse.Winner.FinishedModule>();
foreach (var winner in result.Winners)
{
if (string.IsNullOrEmpty(winner.ModulesFinishComplete))
{
break;
}
foreach (string module in winner.ModulesFinishComplete.Split("@"))
{
var finishModule = module.Split(":");
winner.FinishedModules.Add(new WinnersdResponse.Winner.FinishedModule
{
ModuleNumber = Convert.ToInt32(finishModule[0]),
Coupon = finishModule[1].ToString()
});
}
}
return(Response.Success(result));
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("ErrorGetWinners"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "ErrorGetWinners"));
}
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("NotLogin"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "NotLogin"));
}
}
else
{
Logger.Error("Message: {0}; StoreProcedure.Error: {1}", Response.CommentMenssage("Sql"), storeProcedure.Error);
return(Response.Error(storeProcedure.Error, "Sql"));
}
}
catch (Exception ex)
{
Logger.Error("Message: {0} Exception: {1}", ex.Message, SerializeJson.ToObject(ex));
return(Response.Error(ex, "Error"));
}
}
public DataTable RetrieveMapData(string mapAccCode)
{
return(StoreProcedure.sp_ACS120_SearchMappingData(AppEnvironment.Database, mapAccCode));
}
private void 一般用法介绍()
{
string connectionName = "default";
IsolationLevel isolation = IsolationLevel.ReadCommitted;
string name = "command1";
string sql = "select * from table1 where .....";
object args = new { a = 1, b = 2 };
// 基本数据库操作
using (DbContext db = DbContext.Create(connectionName)) {
db.BeginTransaction(isolation); // 开启事务，并允许设置事务隔离级别（可选）
db.XmlCommand.Create(name, args).ExecuteNonQuery();
db.CPQuery.Create(sql, args).ExecuteNonQuery();
db.StoreProcedure.Create(name, args).ExecuteNonQuery();
db.Commit();
}
// 封装 DbContext ，允许跨方法调用
using (ConnectionScope scope = ConnectionScope.Create(connectionName)) {
scope.BeginTransaction(isolation); // 开启事务，并允许设置事务隔离级别（可选）
// 下面三个调用，调用了三个静态工厂方法，
// 因此可以放在不同的方法中，不必传递 scope 变量
XmlCommand.Create(name, args).ExecuteNonQuery();
CPQuery.Create(sql, args).ExecuteNonQuery();
StoreProcedure.Create(name, args).ExecuteNonQuery();
scope.Commit();
}
// DbContext/ConnectionScope 其它创建方法
DbContext c1 = DbContext.Create("connectionString", "providerName");
ConnectionScope c2 = ConnectionScope.Create("connectionString", "providerName");
// 甚至可以由显式转换来实现：
DbContext c3 = (DbContext)"connectionString";
// 嵌套使用（一段代码访问不同数据源）
using (ConnectionScope scope = ConnectionScope.Create("connectionName_1")) {
XmlCommand.Create(name, args).ExecuteNonQuery();
using (DbContext db = DbContext.Create("connectionName_2")) {
db.XmlCommand.Create(name, args).ExecuteNonQuery();
}
}
/*
* Execute 包含的操作
*
* abstract class BaseCommand {
* ExecuteNonQuery()
* ExecuteScalar<T>()
* ToScalarList<T>()
* ToSingle<T>()
* ToList<T>()
* ToDataTable()
* ToDataSet()
*
* ExecuteNonQueryAsync()
* ExecuteScalarAsync<T>()
* ToScalarListAsync<T>()
* ToSingleAsync<T>()
* ToListAsync<T>()
* ToDataTableAsync()
* ToDataSetAsync()
* }
*/
/*
* // 从DataTable中加载数据
* class TableExtensions{
* ToList<T>(DataTable);
* ToSingle<T>(DataRow);
* }
*/
// 嵌套使用，允许：XmlCommnad 包含 CPQuery， CPQuery 包含 CPQuery
using (DbContext db = DbContext.Create(connectionName)) {
CPQuery subQuery1 = db.CPQuery.Create(name, args);
CPQuery subQuery2 = db.CPQuery.Create(name, args);
db.XmlCommand.Create(
"select * from t1 where [email protected] and {filter1} and {filter2}",
new {
id = 2,
filter1 = subQuery1,
filter2 = subQuery2
}
).ExecuteNonQuery();
db.XmlCommand.Create(
"select * from t1 where [email protected] and {filter1} and {filter2}",
new {
id = 2,
filter1 = CPQuery.Create(name, args),
filter2 = CPQuery.Create(name, args)
}
).ExecuteNonQuery();
}
}
public DataTable GetAllMessage()
{
DataTable dtbMsg = StoreProcedure.GetMessage(AppEnvironment.Database, null);
return(dtbMsg);
}
public void UpdateCostType(string CostTypeCode, string CostDesc, string CostGroup, int ProcessID, bool Status, string byUser)
{
StoreProcedure.sp_ACS110_UpdateCostType(AppEnvironment.Database, CostTypeCode, CostDesc, CostGroup, ProcessID, Status, byUser);
}
public DataTable GetPeriodCombobox()
{
return(StoreProcedure.sp_GetPeriodCombo(AppEnvironment.Database));
}
public DataTable RetrieveData(string searchCostType, string searchDesc, string searchCostGrp)
{
return(StoreProcedure.sp_ACS110_SearchCostType(AppEnvironment.Database, searchCostType, searchDesc, searchCostGrp));
}
public DataTable GetConfig()
{
return(StoreProcedure.sp_Common_Get_Config(AppEnvironment.Database));
}
public DataTable GetItemGroupCombobox()
{
return(StoreProcedure.sp_GetItemGroupCombo(AppEnvironment.Database, null));
}
public DataTable GetInventoryRevaluationData(int year, int period)
{
return(StoreProcedure.sp_ACS330_Get_InventoryRevaluationData(AppEnvironment.Database, year, period));
}
public ACS320_Process ReCalculateData(int year, int period, decimal pActMOH, decimal pActCapaUsed, decimal pSoldLiter, decimal pEndingLiter, decimal pSoldLiterOEM, decimal pEndingLiterOEM, string byUser)
{
ACS320_Process result = StoreProcedure.sp_ACS320_Process_ReCal(AppEnvironment.Database, year, period, pActMOH, pActCapaUsed, pSoldLiter, pEndingLiter, pSoldLiterOEM, pEndingLiterOEM, byUser);
return(result);
}
public void UpdateMappingData(string AccCode, string CostType, decimal Percent, string byUser)
{
StoreProcedure.sp_ACS120_UpdateMappingData(AppEnvironment.Database, AccCode, CostType, Percent, byUser);
}
public Response GetInformationClient(string email)
{
try
{
StoreProcedure storeProcedure = new StoreProcedure("weco.CLIENTE_GetInformationClient");
storeProcedure.AddParameter("@CLIE_CORREO_ELECTRONICO_VC", email);
DataTable dataTable = storeProcedure.ReturnData(_connection, _timeOut);
Logger.Debug("StoreProcedure: {0} DataTable: {1}", SerializeJson.ToObject(storeProcedure), SerializeJson.ToObject(dataTable));
if (storeProcedure.Error.Length <= 0)
{
if (dataTable.Rows.Count > 0)
{
if (dataTable.Rows[0]["RESULTADO"].ToString().Equals("00"))
{
List <MyInformationResponse.FinishedModule> addfinishedModules = new List <MyInformationResponse.FinishedModule>();
if (!string.IsNullOrEmpty(dataTable.Rows[0]["MODULOS_TERMINADOS"].ToString()))
{
addfinishedModules.AddRange(from string item in dataTable.Rows[0]["MODULOS_TERMINADOS"].ToString().Split("@")
let moduleFinish = new MyInformationResponse.FinishedModule()
{
ModuleNumber = Convert.ToInt32(item.Substring(0, item.IndexOf(":"))),
Coupon = item.Substring(item.IndexOf(":") + 1)
}
select moduleFinish);
}
MyInformationResponse result = new MyInformationResponse
{
Email = dataTable.Rows[0]["CLIE_CORREO_ELECTRONICO_VC"].ToString(),
Ci = dataTable.Rows[0]["CLIE_CI_VC"].ToString(),
NameComplete = dataTable.Rows[0]["CLIE_NOMBRE_COMPLETO_VC"].ToString(),
Gender = dataTable.Rows[0]["CLIE_GENERO_VC"].ToString(),
Birthdate = Convert.ToDateTime(dataTable.Rows[0]["CLIE_FECHA_NACIMIENTO_DT"]),
Age = Convert.ToInt32(dataTable.Rows[0]["CLIE_EDAD_IN"]),
Department = dataTable.Rows[0]["CLIE_DEPARTAMENTO_VC"].ToString(),
City = dataTable.Rows[0]["CLIE_CIUDAD_VC"].ToString(),
Address = dataTable.Rows[0]["CLIE_DIRECCION_VC"].ToString(),
CellPhone = dataTable.Rows[0]["CLIE_NUMERO_CELULAR_VC"].ToString(),
Phone = dataTable.Rows[0]["CLIE_NUMERO_FIJO_VC"].ToString(),
EducationLevel = dataTable.Rows[0]["CLIE_NIVEL_EDUCACION_VC"].ToString(),
Disability = Convert.ToBoolean(dataTable.Rows[0]["CLIE_DISCAPACIDAD_BT"]),
TypeDisability = Convert.ToString(dataTable.Rows[0]["CLIE_TIPO_DISCAPACIDAD_VC"]),
ReferenceName = dataTable.Rows[0]["CLIE_NOMBRE_REFERENCIA_VC"].ToString(),
ReferenceCellphone = dataTable.Rows[0]["CLIE_CELULAR_REFERENCIA_VC"].ToString(),
Role = Convert.ToInt32(dataTable.Rows[0]["CLIE_ROL_IN"]) == 1 ? "User" : "Admin",
CompleteRegister = Convert.ToBoolean(dataTable.Rows[0]["CLIE_REGISTRO_COMPLETO_BT"]),
CurrentModule = Convert.ToInt32(dataTable.Rows[0]["MODULO_ACTUAL"]),
finishedModules = addfinishedModules
};
return(Response.Success(result));
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("ParticipantDoesNotExist"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "ParticipantDoesNotExist"));
}
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("NotLogin"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "NotLogin"));
}
}
else
{
Logger.Error("Message: {0}; StoreProcedure.Error: {1}", Response.CommentMenssage("Sql"), storeProcedure.Error);
return(Response.Error(storeProcedure.Error, "Sql"));
}
}
catch (Exception ex)
{
Logger.Error("Message: {0} Exception: {1}", ex.Message, SerializeJson.ToObject(ex));
return(Response.Error(ex, "Error"));
}
}
public tb_Transfer GetTransferData(int year, int period)
{
return(StoreProcedure.sp_ACS320_Get_TransferData(AppEnvironment.Database, year, period));
}
static void Main(string[] args)
{
LogDetail logDetail = GetLogDetail("Starting application");
Logger.WriteDiagnosticLog(logDetail);
var perfTracker = new PerformanceTracker("FullLogging.Console_Execution", string.Empty, logDetail.UserName, logDetail.Location, logDetail.Product, logDetail.Layer);
//try
//{
// var forcedException = new Exception("Something bad has happened!");
// forcedException.Data.Add("input param", "nothing to see here");
// throw forcedException;
//}
//catch (Exception ex)
//{
// logDetail = GetLogDetail(string.Empty, ex);
// Logger.WriteErrorLog(logDetail);
//}
var fullLoggingConnection = ConfigurationManager.ConnectionStrings["FullLoggingConnection"].ToString();
using (var db = new SqlConnection(fullLoggingConnection))
{
db.Open();
try
{
//RAW ADO.NET
//var adoCommand = new SqlCommand("uspCreateCustomer", db);
//adoCommand.CommandType = System.Data.CommandType.StoredProcedure;
//adoCommand.Parameters.Add(new SqlParameter("@Name", "asdadsadasdadsadadasdsad"));
//adoCommand.Parameters.Add(new SqlParameter("@TotalPurchases", 12000));
//adoCommand.Parameters.Add(new SqlParameter("@TotalReturns", 100.50M));
//adoCommand.ExecuteNonQuery();
var customADO = new StoreProcedure(db, "uspCreateCustomer");
customADO.AddParameter("@Name", "stadasdasudahdusahdua");
customADO.AddParameter("@TotalPurchases", 12000);
customADO.AddParameter("@TotalReturns", 100.50M);
customADO.ExecuteNonQuery();
}
catch (Exception ex)
{
var exceptionLogDetail = GetLogDetail(string.Empty, ex);
Logger.WriteErrorLog(exceptionLogDetail);
}
}
logDetail = GetLogDetail("used full logging console.");
Logger.WriteUsageLog(logDetail);
logDetail = GetLogDetail("Stopping app");
Logger.WriteDiagnosticLog(logDetail);
perfTracker.Stop();
}
public bool CheckRepeatProcess(int year, int period)
{
string strRet = StoreProcedure.sp_ACS320_Check_RepeatProcess(AppEnvironment.Database, year, period);
return(Util.ConvertObjectToBoolean(strRet));
}
public Response InsertClientModuleAnswers(string email, int numberModule, string userCreation)
{
try
{
StoreProcedure storeProcedure = new StoreProcedure("weco.CLIENTE_MODULO_InsertClientModuleAnswers");
storeProcedure.AddParameter("@CLIE_CORREO_ELECTRONICO_VC", email);
storeProcedure.AddParameter("@MODU_NUMERO_MODULO_IN", numberModule);
storeProcedure.AddParameter("@CLIE_USUARIO_CREACION_VC", userCreation);
DataTable dataTable = storeProcedure.ReturnData(_connection, _timeOut);
Logger.Debug("StoreProcedure: {0} DataTable: {1}", SerializeJson.ToObject(storeProcedure), SerializeJson.ToObject(dataTable));
if (storeProcedure.Error.Length <= 0)
{
if (dataTable.Rows.Count > 0)
{
if (!dataTable.Rows[0]["RESULTADO"].Equals("03"))
{
if (!dataTable.Rows[0]["RESULTADO"].Equals("02"))
{
if (!dataTable.Rows[0]["RESULTADO"].Equals("01"))
{
return(Response.Success(new Core.Entity.Coupon
{
CouponRegistred = dataTable.Rows[0]["CLMO_CUPON_VC"].ToString(),
CouponNumber = dataTable.Rows[0]["MODU_NUMERO_MODULO_IN"].ToString()
}));
}
else
{
Logger.Error("Message: {0}; DataTable: {1}", "", SerializeJson.ToObject(dataTable));
return(Response.Error("CouponAlreadyRegistered"));
}
}
else
{
Logger.Error("Message: {0}; DataTable: {1}", "", SerializeJson.ToObject(dataTable));
return(Response.Error("ClientFinallyModule"));
}
}
else
{
Logger.Error("Message: {0}; DataTable: {1}", "", SerializeJson.ToObject(dataTable));
return(Response.Error("ModuleNotRegistred"));
}
}
else
{
Logger.Error("Message: {0}; dataTable: {1}", Response.CommentMenssage("ModuleNotRegistred"), SerializeJson.ToObject(dataTable));
return(Response.Error(null, "ModuleNotRegistred"));
}
}
else
{
Logger.Error("Message: {0}; StoreProcedure.Error: {1}", Response.CommentMenssage("Sql"), storeProcedure.Error);
return(Response.Error(storeProcedure.Error, "Sql"));
}
}
catch (Exception ex)
{
Logger.Error("Message: {0} Exception: {1}", ex.Message, SerializeJson.ToObject(ex));
return(Response.Error(ex, "Error"));
}
}
public DataTable GetCostTypeCombo(string CostType)
{
return(StoreProcedure.sp_GetCostTypeCombo(AppEnvironment.Database, CostType));
}
