public static List <Supplier> getSupList(int id)
{
List <Supplier> lista = new List <Supplier>();
using (ViperContext ctx = new ViperContext())
{
try
{
//Validar si la base de datos existe
bool isDataBaseExist = Database.Exists(ctx.Database.Connection);
if (isDataBaseExist)
{
//Validar si la tabla utilizada existe
bool isTableExist = ctx.Database
.SqlQuery <int?>(@"
SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'Supplier'")
.SingleOrDefault() != null;
if (isTableExist)
{
lista = ctx.Suppliers.Where(x => x.Id == id).OrderBy(x => x.SupplierKey).ToList();
}
}
}
catch (Exception ex)
{
}
}
return(lista);
}
public static List <Site> getSites(int id_comp)
{
List <Site> Sites = new List <Site>();
//Utilizar el contexto para acceder a la base de datos
using (ViperContext ctx = new ViperContext())
{
try
{
//Validar si la base de datos existe
bool isDataBaseExist = Database.Exists(ctx.Database.Connection);
if (isDataBaseExist)
{
//Validar si la tabla utilizada existe
bool isTableExist = ctx.Database
.SqlQuery <int?>(@"
SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'Site'")
.SingleOrDefault() != null;
if (isTableExist)
{
Sites = ctx.Sites.Where(x => x.CompanyId == id_comp).OrderBy(x => x.CompanyName).ToList();
}
}
}
catch (Exception ex)
{
}
}
return(Sites);
}
public static DataTable getCustomer(string customerkey)
{
List <Customer> Customer = new List <Customer>();
DataTable dt = new DataTable();
//Utilizar el contexto para acceder a la base de datos
using (ViperContext ctx = new ViperContext())
{
try
{
//Validar si la base de datos existe
bool isDataBaseExist = Database.Exists(ctx.Database.Connection);
if (isDataBaseExist)
{
//Validar si la tabla utilizada existe
bool isTableExist = ctx.Database
.SqlQuery <int?>(@"
SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'Customer'")
.SingleOrDefault() != null;
if (isTableExist)
{
//Recuperar el menu de opciones
var result = (from sp in ctx.customers
where sp.CustomerKey == customerkey
select new
{
sp.CustomerKey,
sp.FullName,
sp.ContactName,
sp.PhoneNumber,
sp.CellphoneNumber,
sp.EmailAddress,
sp.Comment,
sp.DaysCredit
}).ToList();
//Crear las columnas del DataTable
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("CustomerKey", typeof(string)),
new DataColumn("FullName", typeof(string)),
new DataColumn("ContactName", typeof(string)),
new DataColumn("PhoneNumber", typeof(string)),
new DataColumn("CellphoneNumber", typeof(string)),
new DataColumn("EmailAddress", typeof(string)),
new DataColumn("Comment", typeof(string)),
new DataColumn("DaysCredit", typeof(string))
});
//Guardar los datos recuperados en una fila del DataTable
result.ToList().ForEach(x =>
{
//Crear una fila nueva
var row = dt.NewRow();
//Cargar los datos de la fila
row["CustomerKey"] = x.CustomerKey;
row["FullName"] = x.FullName;
row["ContactName"] = x.ContactName;
row["PhoneNumber"] = x.PhoneNumber;
row["CellphoneNumber"] = x.CellphoneNumber;
row["EmailAddress"] = x.EmailAddress;
row["Comment"] = x.Comment;
row["DaysCredit"] = x.DaysCredit;
//Añadir fila al DataTable
dt.Rows.Add(row);
});
}
}
}
catch (Exception ex)
{
}
}
return(dt);
}
public static string sp_insert_supplier(Supplier sup, Address a, AddressSAT ads)
{
string message = string.Empty;
bool isInserted = false;
//Utilizar el contexto para acceder a la base de datos
using (ViperContext ctx = new ViperContext())
{
using (var ctxTran = ctx.Database.BeginTransaction())
{
try
{
//Validar si la base de datos existe
bool isDataBaseExist = Database.Exists(ctx.Database.Connection);
if (isDataBaseExist)
{
//Validar si la tabla utilizada existe
bool isTableExist = ctx.Database
.SqlQuery <int?>(@"
SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'Supplier' OR table_name='Address' OR table_name='AddressSAT'")
.SingleOrDefault() != null;
if (isTableExist)
{
ctx.Addresses.Add(a);
isInserted = ctx.SaveChanges() > 0;
if (isInserted)
{
isInserted = false;
var addressID = ctx.Addresses.OrderByDescending(x => x.Id).FirstOrDefault().Id;
if (addressID > 0)
{
isInserted = false;
ctx.AddressesSAT.Add(ads);
isInserted = ctx.SaveChanges() > 0;
var addressSATID = ctx.AddressesSAT.OrderByDescending(x => x.Id).FirstOrDefault().Id;
if (addressSATID > 0)
{
if (isInserted)
{
isInserted = false;
sup.AddressSATId = addressSATID;
sup.AddressId = addressID;
ctx.Suppliers.Add(sup);
isInserted = ctx.SaveChanges() > 0;
var supId = ctx.Companies.OrderByDescending(x => x.Id).FirstOrDefault().Id;
if (supId > 0)
{
if (isInserted == true && string.IsNullOrEmpty(message))
{
ctxTran.Commit();
}
}
}
}
}
}
}
}
}
catch (DbEntityValidationException ex)
{
var errorMessages = ex.EntityValidationErrors
.SelectMany(x => x.ValidationErrors)
.Select(x => x.ErrorMessage);
var fullErrorMessage = string.Join("; ", errorMessages);
var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
message = exceptionMessage + "\n" + ex.EntityValidationErrors;
ctxTran.Rollback();
}
catch (DbUpdateConcurrencyException ex)
{
var entity = ex.Entries.Single().GetDatabaseValues();
if (entity == null)
{
message = "The entity being updated is already deleted by another user";
}
else
{
message = "The entity being updated has already been updated by another user";
}
ctxTran.Rollback();
}
catch (DbUpdateException ex)
{
var exception = HandleDbUpdateException(ex);
message = exception.Message;
ctxTran.Rollback();
}
}
}
return(message);
}
public static DataTable getProduct(string BarCode)
{
List <Product> Products = new List <Product>();
DataTable dt = new DataTable();
//Utilizar el contexto para acceder a la base de datos
using (ViperContext ctx = new ViperContext())
{
try
{
//Validar si la base de datos existe
bool isDataBaseExist = Database.Exists(ctx.Database.Connection);
if (isDataBaseExist)
{
//Validar si la tabla utilizada existe
bool isTableExist = ctx.Database
.SqlQuery <int?>(@"
SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'Product'")
.SingleOrDefault() != null;
if (isTableExist)
{
//Recuperar el menu de opciones
var result = (from pr in ctx.Products
where pr.BarCode == BarCode
select new
{
pr.BarCode,
pr.Description,
pr.Location,
pr.ActiveSubstance,
pr.QuantityPerUnit,
pr.StandardCost,
pr.ListPrice,
pr.IVA
}).ToList();
//Crear las columnas del DataTable
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("BarCode", typeof(string)),
new DataColumn("Description", typeof(string)),
new DataColumn("Location", typeof(string)),
new DataColumn("ActiveSubstance", typeof(string)),
new DataColumn("QuantityPerUnit", typeof(string)),
new DataColumn("StandardCost", typeof(string)),
new DataColumn("ListPrice", typeof(string)),
new DataColumn("IVA", typeof(string))
});
//Guardar los datos recuperados en una fila del DataTable
result.ToList().ForEach(x =>
{
//Crear una fila nueva
var row = dt.NewRow();
//Cargar los datos de la fila
row["BarCode"] = x.BarCode;
row["Description"] = x.Description;
row["Location"] = x.Location;
row["ActiveSubstance"] = x.ActiveSubstance;
row["QuantityPerUnit"] = x.QuantityPerUnit;
row["StandardCost"] = x.StandardCost;
row["ListPrice"] = x.ListPrice;
row["IVA"] = x.IVA;
//Añadir fila al DataTable
dt.Rows.Add(row);
});
}
}
}
catch (Exception ex)
{
}
}
return(dt);
}