public string GetLastCustomerCode()
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_GetLastCustomerCode().FirstOrDefault().CustomerCode);
}
}
public IEnumerable <Sp_GetEmployeeToComboBox> SpGetEmployeeToComboBox()
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_GetEmployeeToComboBox().ToList());
}
}
public IEnumerable <Sp_GetCategories> GetCategories()
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_GetCategories().ToList());
}
}
public IEnumerable <KardexGridModel> GetAllKardexGridModel(string productCode, int bodegaID)
{
using (var db = new KoontabiDBEntities())
{
var result = from k in db.Kardexes
join pd in db.ProductDetails
on k.ProductDetailsID equals pd.ProductDetailsID
join p in db.Products
on pd.ProductID equals p.ProductID
join ivd in db.InvoiceDetails
on k.KardexID equals ivd.KardexID into LeftJoined
from ivd in LeftJoined.DefaultIfEmpty()
join iv in db.Invoices
on ivd.InvoiceID equals iv.InvoiceID into LeftJoined2
from iv in LeftJoined2.DefaultIfEmpty()
where p.ProductCode.Equals(productCode)
where pd.BodegaID == bodegaID
select new KardexGridModel()
{
RegisterDate = k.RegisterDate,
TypeOfMove = k.TypeOfMove,
ProductCode = p.ProductCode,
ProductName = p.ProductName,
KardexIn = (Int32)k.KardexIn,
KardexOut = (Int32)k.KardexOut,
KardexStock = (Int32)k.KardexStock
};
return(result.ToList <KardexGridModel>().OrderByDescending(x => x.RegisterDate));
}
}
public void Delete(int idEntity)
{
using (var db = new KoontabiDBEntities()) {
try
{
db.Database.BeginTransaction();
var currentObj = db.Categories.Find(idEntity);
if (currentObj != null)
{
db.Entry(currentObj).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
else
{
throw new Exception("No existe categoria");
}
db.Database.CurrentTransaction.Commit();
}
catch (Exception)
{
db.Database.CurrentTransaction.Rollback();
throw;
}
}
}
public IEnumerable <sp_getAllMunicipalitiesById> GetAllMunicipalitiesById(int id)
{
using (var db = new KoontabiDBEntities())
{
return(db.sp_getAllMunicipalitiesById(id).ToList());
}
}
public bool AddPaidAccountDetail(PaidAccountDetail _paidAccountDetail)
{
using (var db = new KoontabiDBEntities())
{
try
{
db.Database.BeginTransaction();
db.PaidAccountDetails.Add(_paidAccountDetail);
var result = db.PaidAccounts.Find(_paidAccountDetail.PaidAccountID);
result.AmountPending -= _paidAccountDetail.Payment;
if (result.AmountPending == 0)
{
result.State = db.SP_GET_STATUS(2).FirstOrDefault().STATUS;
result.DayDelay = 0;
}
db.SaveChanges();
db.Database.CurrentTransaction.Commit();
return(true);
}
catch (Exception)
{
db.Database.CurrentTransaction.Rollback();
throw;
}
}
}
public Customer Find(int idEntity)
{
using (var db = new KoontabiDBEntities())
{
return(db.Customers.Find(idEntity));
}
}
public List <sp_GetAllCustomerRadGridShowData> FillGridView()
{
using (var db = new KoontabiDBEntities())
{
return(db.sp_GetAllCustomerRadGridShowData().ToList());
}
}
public void UpdateDayDelayAccounts()
{
using (var db = new KoontabiDBEntities())
{
db.Database.ExecuteSqlCommand("Exec UpdateDayDelayInPaidAccounts");
}
}
public IEnumerable <V_DisplayAllSales> DisplayAllInvoiceByMonth(int initMonth, int year, int endMonth = 0)
{
using (var db = new KoontabiDBEntities())
{
if (endMonth != 0)
{
var result = from m in db.V_DisplayAllSales
where DbFunctions.TruncateTime(m.InvoiceDate).Value.Month >= initMonth &&
DbFunctions.TruncateTime(m.InvoiceDate).Value.Month <= endMonth &&
DbFunctions.TruncateTime(m.InvoiceDate).Value.Year == year
select m;
return(result.ToList());
}
else
{
var result = from m in db.V_DisplayAllSales
where DbFunctions.TruncateTime(m.InvoiceDate).Value.Month == initMonth &&
DbFunctions.TruncateTime(m.InvoiceDate).Value.Year == year
select m;
return(result.ToList());
}
}
}
public IEnumerable <sp_getAllProvinces> GetAllProvinces()
{
using (var db = new KoontabiDBEntities())
{
return(db.sp_getAllProvinces().ToList());
}
}
public Sp_GetProductByID SpGetProductByID(int productID)
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_GetProductByID(productID).FirstOrDefault());
}
}
public List <ProductToSearch> ProductToSearching()
{
try
{
using (var db = new KoontabiDBEntities())
{
var result = from p in db.Products
join pd in db.ProductDetails
on p.ProductID equals pd.ProductID
select new ProductToSearch()
{
ProductID = p.ProductID,
ProductDetailID = pd.ProductDetailsID,
ProductCode = p.ProductCode,
ProductName = p.ProductName,
UnitPrice1 = pd.UnitPrice1.HasValue? pd.UnitPrice1.Value : 0,
UnitPrice2 = pd.UnitPrice2.HasValue ? pd.UnitPrice2.Value : 0,
UnitPrice3 = pd.UnitPrice3.HasValue ? pd.UnitPrice2.Value : 0,
Stock = pd.Stock
};
return(result.ToList());
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
///if refNumberInvoice exit return false and other way return true
/// </summary>
/// <param name="refNumberInvoice"></param>
/// <returns>Bool</returns>
public bool SpValidateRefNumberInvoicePay(string refNumberInvoice)
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_ValidateRefNumberInvoicePay(refNumberInvoice).FirstOrDefault().Result.Value);
}
}
public Nullable <Int32> SpGetLastInvoiceNumber()
{
using (var db = new KoontabiDBEntities())
{
return(db.Sp_GetLastInvoiceNumber().FirstOrDefault().InvoiceNumber);
}
}
public decimal GetTaxValue(int taxID)
{
using (var db = new KoontabiDBEntities()) {
return((from taxvalue in db.Taxes
where taxvalue.TaxID == taxID
select taxvalue.TaxValue).SingleOrDefault());
}
}
public bool IsExitCustomerCode(string customerCode)
{
using (var db = new KoontabiDBEntities())
{
var result = db.sp_ExitCustomerCode(customerCode).FirstOrDefault();
return(result.Code == 1);
}
}
public bool isExitReferenceInvoice(string refenceInvoice)
{
using (var db = new KoontabiDBEntities())
{
var _referenceInvoice = db.SpValidateIfExitRefenceInvoice(refenceInvoice).FirstOrDefault();
return(_referenceInvoice != null? _referenceInvoice.ReferenceInvoice == 1:false);
}
}
public void Update(Customer entity)
{
using (var db = new KoontabiDBEntities())
{
db.Entry(entity).State = EntityState.Modified;
db.SaveChanges();
}
}
public IEnumerable <GetInvoiceByIDView> FindInvoiceToReport(Expression <Func <GetInvoiceByIDView, bool> > specification)
{
using (var db = new KoontabiDBEntities())
{
var result = db.GetInvoiceByIDViews.Where(specification).ToList();
return(result);
}
}
public List <Sp_GetCustomerByParam> GetCustomerByParam(string param)
{
using (var db = new KoontabiDBEntities())
{
var result = db.Sp_GetCustomerByParam(param).ToList();
return(result);
}
}
public IEnumerable <Sp_GetAllBodega> GetAllBodega()
{
using (var db = new KoontabiDBEntities())
{
db.Configuration.LazyLoadingEnabled = false;
return(db.Sp_GetAllBodega().ToList());
}
}
public IEnumerable <Unit> GetAll()
{
using (var db = new KoontabiDBEntities())
{
var result = db.Units.ToList();
return(result);
}
}
public List <PaidAccountDetail> ListPayAccountDetail(Expression <Func <PaidAccountDetail, bool> > conditions)
{
using (var db = new KoontabiDBEntities())
{
var result = db.PaidAccountDetails.Where(conditions).Select(x => x).ToList();
return(result);
}
}
public int?ValidateInvoiceOldByPay(int customerid, DateTime invoiceDateToValidate)
{
using (var db = new KoontabiDBEntities())
{
var result = db.Sp_ValidateInvoiceOldByPay(invoiceDateToValidate, customerid).FirstOrDefault();
return(result != null?result.InvoiceNumber:0);
}
}
public Invoice Add(Invoice entity)
{
using (var db = new KoontabiDBEntities())
{
try
{
db.Database.BeginTransaction();
db.Invoices.Add(entity);
if (entity.TypeOfPayID == 2)
{
decimal subtotal = 0;
decimal discount = 0;
decimal itbis = 0;
foreach (var item in entity.InvoiceDetails)
{
subtotal += item.Quantity * item.UnitPrice;
}
if (entity.DisCount.HasValue)
{
discount = ((decimal)entity.DisCount.Value * 100) * subtotal / 100;
}
if (entity.TaxID.HasValue)
{
Tax t = db.Taxes.Find(entity.TaxID.Value);
itbis = (t.TaxValue * subtotal) / 100;
}
subtotal -= discount;
subtotal += itbis;
PaidAccount p = new PaidAccount();
p.InvoiceID = entity.InvoiceID;
p.DateEmit = entity.InvoiceDate;
p.ExpirationDate = entity.ExpirationInvoiceDate;
p.Amount = subtotal;
p.AmountPending = subtotal;
p.DayDelay = Convert.ToInt32((entity.InvoiceDate - entity.ExpirationInvoiceDate).TotalDays);
p.State = db.SP_GET_STATUS(1).FirstOrDefault().STATUS;
db.PaidAccounts.Add(p);
}
db.SaveChanges();
db.Database.CurrentTransaction.Commit();
return(new Invoice()
{
InvoiceID = entity.InvoiceID
});
}
catch (Exception)
{
db.Database.CurrentTransaction.Rollback();
throw;
}
}
}
public Customer Add(Customer entity)
{
using (var db = new KoontabiDBEntities())
{
db.Customers.Add(entity);
db.SaveChanges();
return(entity);
}
}
public Product Add(Product entity)
{
using (var db = new KoontabiDBEntities())
{
db.Products.Add(entity);
db.SaveChanges();
return(entity);
}
}
public IEnumerable <Customer> GetAll()
{
using (var db = new KoontabiDBEntities())
{
var result = from c in db.Customers
select c;
return(result.ToList());
}
}
