public static List<DeliveryOrderHeaderViewModel> GetByFilter(string filterString)
{
List<DeliveryOrderHeaderViewModel> result = new List<DeliveryOrderHeaderViewModel>();
using (var db = new BuahSayurContext())
{
result = (from dOr in db.DeliveryOrders
join c in db.Customers
on dOr.Customer_Username equals c.Username
select new DeliveryOrderHeaderViewModel
{
Id = dOr.Id,
Customer_Username = c.Username,
Reference = dOr.Reference,
SellingDate = dOr.SellingDate,
//IsSent = dOr.IsSent,
IsReturned = dOr.IsReturned
}).Where(o => o.IsReturned == false && o.SellingDate < DateTime.Today).ToList();
//
result = result.Where(o => o.Reference.ToLower().Contains(filterString.ToLower()) || o.Customer_Username.ToLower().Contains(filterString.ToLower())).ToList();
}
return result;
}
public static SupplierViewModel GetByCode(string code)
{
SupplierViewModel result = new SupplierViewModel();
using (var db = new BuahSayurContext())
{
result = (from s in db.Suppliers
join c in db.Cities
on s.City_Code equals c.Code into clf
from c in clf.DefaultIfEmpty()
join p in db.Provinces
on c.Province_Code equals p.Code into plf
from p in plf.DefaultIfEmpty()
where s.Code == code
select new SupplierViewModel
{
Id = s.Id,
Code = s.Code,
Name = s.Name,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
Address = s.Address,
IsActivated = s.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static bool SaveShipment(ShipmentOrderViewModel models)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
ShipmentOrder shipmentorder = new ShipmentOrder
{
Id = 1,
DeliveryOrder_Id = models.DeliveryOrder_Id,
ShippingDate = models.ShippingDate,
Vehicle = models.Vehicle,
PersonInCharge = models.PersonInCharge,
Fee = models.Fee
};
db.ShipmentOrders.Add(shipmentorder);
db.SaveChanges();
}
}
catch (Exception ex)
{
result = false;
Message = ex.Message;
}
return(result);
}
public static List <SupplierViewModel> GetAll()
{
List <SupplierViewModel> result = new List <SupplierViewModel>();
using (var db = new BuahSayurContext())
{
result = (from s in db.Suppliers
join c in db.Cities
on s.City_Code equals c.Code
join p in db.Provinces
on c.Province_Code equals p.Code
select new SupplierViewModel
{
Id = s.Id,
Code = s.Code,
Name = s.Name,
Address = s.Address,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
IsActivated = c.IsActivated
}).ToList();
}
return(result);
}
public static List <CustomerViewModel> GetAll()
{
List <CustomerViewModel> result = new List <CustomerViewModel>();
using (var db = new BuahSayurContext())
{
result = (from cs in db.Customers
join ct in db.Cities
on cs.City_Code equals ct.Code
join pr in db.Provinces
on ct.Province_Code equals pr.Code
select new CustomerViewModel
{
Id = cs.Id,
Username = cs.Username,
FirstName = cs.FirstName,
LastName = cs.LastName,
Address = cs.Address,
PhoneNumber = cs.PhoneNumber,
Province_Code = pr.Code,
Province_Name = pr.Name,
City_Code = ct.Code,
City_Name = ct.Name,
IsActivated = ct.IsActivated
}).ToList();
}
return(result);
}
public static SupplierViewModel GetById(int id) // to find id for delete method and edit in SupplierController
{
SupplierViewModel result = new SupplierViewModel();
using (var db = new BuahSayurContext())
{
result = (from s in db.Suppliers
join c in db.Cities
on s.City_Code equals c.Code into clf
from c in clf.DefaultIfEmpty()
join p in db.Provinces
on c.Province_Code equals p.Code into plf
from p in plf.DefaultIfEmpty()
where s.Id == id
select new SupplierViewModel
{
Id = s.Id,
Code = s.Code,
Name = s.Name,
Address = s.Address,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
IsActivated = c.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static CustomerViewModel GetByUsername(string username)
{
CustomerViewModel result = new CustomerViewModel();
using (var db = new BuahSayurContext())
{
result = (from cs in db.Customers
join c in db.Cities
on cs.City_Code equals c.Code into clf
from c in clf.DefaultIfEmpty()
join p in db.Provinces
on c.Province_Code equals p.Code into plf
from p in plf.DefaultIfEmpty()
where cs.Username == username
select new CustomerViewModel
{
Id = cs.Id,
Username = cs.Username,
FirstName = cs.FirstName,
LastName = cs.LastName,
Address = cs.Address,
PhoneNumber = cs.PhoneNumber,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
IsActivated = cs.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static string GetNewReference()
{
string result = string.Empty;
try
{
using (var db = new BuahSayurContext())
{
var refTemplate = "PO-" + (DateTime.Now).ToString("yy") + string.Format("{0:00}", DateTime.Now.Month) + "-";
var lastRef = (from po in db.PurchasingOrders
where po.Reference.Contains(refTemplate)
select new { Id = po.Id, Reference = po.Reference }).OrderByDescending(o => o.Reference).FirstOrDefault();
int increment = 1;
if (lastRef != null)
{
string[] splitRef = lastRef.Reference.Split('-');
increment = int.Parse(splitRef[2]) + 1;
}
result = refTemplate + increment.ToString("D4");
}
}
catch (Exception ex)
{
Message = ex.Message;
//throw;
}
return(result);
}
public static List <CustomerViewModel> GetByFilter(string filterString)
{
List <CustomerViewModel> result = new List <CustomerViewModel>();
using (var db = new BuahSayurContext())
{
result = (from cs in db.Customers
join c in db.Cities
on cs.City_Code equals c.Code
join p in db.Provinces
on c.Province_Code equals p.Code
select new CustomerViewModel
{
Id = cs.Id,
Username = cs.Username,
FirstName = cs.FirstName,
LastName = cs.LastName,
Address = cs.Address,
PhoneNumber = cs.PhoneNumber,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
IsActivated = cs.IsActivated
}).ToList();
result = result.Where(o => o.Username.ToLower().Contains(filterString.ToLower()) || o.FullName.ToLower().Contains(filterString.ToLower()) || o.FullAddress.ToLower().Contains(filterString.ToLower())).ToList();
}
return(result);
}
public static CustomerViewModel GetById(int id)
{
CustomerViewModel result = new CustomerViewModel();
using (var db = new BuahSayurContext())
{
result = (from cs in db.Customers
join ct in db.Cities
on cs.City_Code equals ct.Code into ctlf
from ct in ctlf.DefaultIfEmpty()
join pr in db.Provinces
on ct.Province_Code equals pr.Code into prlf
from pr in prlf.DefaultIfEmpty()
where cs.Id == id
select new CustomerViewModel
{
Id = cs.Id,
Username = cs.Username,
FirstName = cs.FirstName,
LastName = cs.LastName,
Address = cs.Address,
PhoneNumber = cs.PhoneNumber,
Province_Code = pr.Code,
Province_Name = pr.Name,
City_Code = ct.Code,
City_Name = ct.Name,
IsActivated = ct.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static List <DeliveryOrderDetailViewModel> GetSellingDetailByReference(string reference)
{
List <DeliveryOrderDetailViewModel> result = new List <DeliveryOrderDetailViewModel>();
//string sRef = SellingDataAccess.SelectedSelling.Reference;
using (var db = new BuahSayurContext())
{
result = (from dod in db.DeliveryOrderDetails
join doh in db.DeliveryOrders
on dod.DeliveryOrder_Id equals doh.Id
join i in db.Items
on dod.Item_Code equals i.Code
where doh.Reference == reference
select new DeliveryOrderDetailViewModel
{
Id = dod.Id,
DeliveryOrder_Id = doh.Id,
Item_Code = dod.Item_Code,
Item_Name = i.Name,
Item_Weight = i.Weight,
Item_Size = i.Size,
Quantity = dod.Quantity,
Price = dod.Price,
Total = dod.Total
}).ToList();
}
return(result);
}
public static List <SupplierViewModel> GetByFilter(string filterString)
{
List <SupplierViewModel> result = new List <SupplierViewModel>();
using (var db = new BuahSayurContext())
{
result = (from s in db.Suppliers
join c in db.Cities
on s.City_Code equals c.Code
join p in db.Provinces
on c.Province_Code equals p.Code
select new SupplierViewModel
{
Id = s.Id,
Code = s.Code,
Name = s.Name,
Province_Code = p.Code,
Province_Name = p.Name,
City_Code = c.Code,
City_Name = c.Name,
Address = s.Address,
IsActivated = s.IsActivated
}).ToList();
result = result.Where(o => o.Code.ToLower().Contains(filterString.ToLower()) || o.Name.ToLower().Contains(filterString.ToLower()) || o.FullAddress.ToLower().Contains(filterString.ToLower())).ToList();
}
return(result);
}
public static bool Update(ItemViewModel model)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
if (model.Id == 0)
{
Item item = new Item
{
Code = model.Code,
Name = model.Name,
Price_Selling = model.Price_Selling,
Price_Purchasing = model.Price_Purchasing,
Category = model.Category,
Stock = model.Stock,
Weight = model.Weight,
Size = model.Size,
IsActivated = model.IsActivated,
Created = DateTime.Now,
CreatedBy = "Ilham",
};
db.Items.Add(item);
db.SaveChanges();
}
else
{
Item item = db.Items.Where(o => o.Id == model.Id).FirstOrDefault();
if (item != null)
{
item.Code = model.Code;
item.Name = model.Name;
item.Price_Selling = model.Price_Selling;
item.Price_Purchasing = model.Price_Purchasing;
item.Category = model.Category;
item.Stock = model.Stock;
item.Weight = model.Weight;
item.Size = model.Size;
item.IsActivated = model.IsActivated;
item.Modified = DateTime.Now;
item.ModifiedBy = "Ilham";
db.SaveChanges();
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static SellingResult Save(DeliveryOrderHeaderViewModel models)
{
SellingResult result = new SellingResult();
try
{
using (var db = new BuahSayurContext())
{
string newRef = GetNewReference();
result.Reference = newRef;
DeliveryOrder deliveryOrder = new DeliveryOrder
{
Id = 1,
Customer_Username = models.Customer_Username,
Reference = newRef,
SellingDate = models.SellingDate,
IsSent = false,
IsReturned = false
};
db.DeliveryOrders.Add(deliveryOrder);
foreach (var item in models.DeliveryDetails)
{
DeliveryOrderDetail deliveryDetail = new DeliveryOrderDetail
{
DeliveryOrder_Id = deliveryOrder.Id,
Item_Code = item.Item_Code,
Quantity = item.Quantity,
Price = item.Price,
Total = item.Quantity * item.Price
};
result.Total += (item.Quantity * item.Price);
db.DeliveryOrderDetails.Add(deliveryDetail);
// Update Stock
Item Stock = db.Items.Where(x => x.Code == item.Item_Code).FirstOrDefault();
if (Stock != null)
{
Stock.Stock = Stock.Stock - item.Quantity;
}
}
db.SaveChanges();
}
}
catch (Exception ex)
{
result.Success = false;
Message = ex.Message;
}
return result;
}
public static bool Update(CustomerViewModel model)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
if (model.Id == 0)
{
Customer customer = new Customer
{
Username = model.Username,
FirstName = model.FirstName,
LastName = model.LastName,
Address = model.Address,
PhoneNumber = model.PhoneNumber,
City_Code = model.City_Code,
IsActivated = model.IsActivated,
Created = DateTime.Now,
CreatedBy = "Hasan"
};
db.Customers.Add(customer);
db.SaveChanges();
}
else
{
Customer customer = db.Customers.Where(o => o.Id == model.Id).FirstOrDefault();
if (customer != null)
{
customer.Username = model.Username;
customer.FirstName = model.FirstName;
customer.LastName = model.LastName;
customer.Address = model.Address;
customer.PhoneNumber = model.PhoneNumber;
customer.City_Code = model.City_Code;
customer.IsActivated = model.IsActivated;
customer.Modified = DateTime.Now;
customer.ModifiedBy = "Hasan";
db.SaveChanges();
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static ReturnResult Save(ReturnOrderHeaderViewModel models)
{
ReturnResult result = new ReturnResult();
try
{
using (var db = new BuahSayurContext())
{
string newRef = GetNewReference();
result.Reference = newRef;
ReturnOrder returnOrder = new ReturnOrder
{
Id = 1,
DeliveryOrder_Id = models.DeliveryOrder_Id,
Reference = newRef,
ReturnDate = models.ReturnDate,
};
db.ReturnOrders.Add(returnOrder);
foreach (var item in models.ReturnOrderDetails)
{
ReturnOrderDetail returnOrderDetail = new ReturnOrderDetail
{
Return_Id = returnOrder.Id,
Item_Code = item.Item_Code,
ReturnAmount = item.ReturnAmount,
Replacement = item.Replacement,
Shortage = item.Shortage,
Status = item.Status
};
db.ReturnOrderDetails.Add(returnOrderDetail);
}
// set IsReturned to true
DeliveryOrder deliveryOrder = db.DeliveryOrders.Where(x => x.Id == models.DeliveryOrder_Id).FirstOrDefault();
deliveryOrder.IsReturned = true;
db.SaveChanges();
}
}
catch (Exception ex)
{
result.Success = false;
Message = ex.Message;
}
return(result);
}
public static PurchasingResult Save(PurchasingOrderHeaderViewModel models)
{
PurchasingResult result = new PurchasingResult();
try
{
using (var db = new BuahSayurContext())
{
string newRef = GetNewReference();
result.Reference = newRef;
PurchasingOrder purchasingOrder = new PurchasingOrder
{
Id = 1,
Supplier_Code = models.Supplier_Code,
Reference = newRef,
PurchasingDate = models.PurchasingDate
};
db.PurchasingOrders.Add(purchasingOrder);
foreach (var item in models.PurchasingDetails)
{
PurchasingOrderDetail purchasingDetail = new PurchasingOrderDetail
{
PurchasingOrder_Id = purchasingOrder.Id,
Item_Code = item.Item_Code,
Quantity = item.Quantity,
Price = item.Price,
Total = item.Quantity * item.Price
};
result.Total += (item.Quantity * item.Price);
db.PurchasingOrderDetails.Add(purchasingDetail);
// Update Stock
Item Stock = db.Items.Where(x => x.Code == item.Item_Code).FirstOrDefault();
if (Stock != null)
{
Stock.Stock = Stock.Stock + item.Quantity;
}
}
db.SaveChanges();
}
}
catch (Exception ex)
{
result.Success = false;
Message = ex.Message;
}
return(result);
}
public static bool Update(CityViewModel model)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
if (model.Id == 0)
{
City city = new City
{
Code = model.Code,
Province_Code = model.Province_Code,
Name = model.Name,
PostalCode = model.PostalCode,
IsActivated = model.IsActivated,
Created = DateTime.Now,
CreatedBy = "Hasan"
};
db.Cities.Add(city);
db.SaveChanges();
}
else
{
City city = db.Cities.Where(o => o.Id == model.Id).FirstOrDefault();
if (city != null)
{
city.Code = model.Code;
city.Province_Code = model.Province_Code;
city.Name = model.Name;
city.PostalCode = model.PostalCode;
city.IsActivated = model.IsActivated;
city.Modified = DateTime.Now;
city.ModifiedBy = "Hasan";
db.SaveChanges();
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static bool Update(SupplierViewModel model)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
if (model.Id == 0)
{
Supplier supplier = new Supplier
{
Code = model.Code,
Name = model.Name,
Address = model.Address,
City_Code = model.City_Code,
IsActivated = model.IsActivated,
Created = DateTime.Now,
CreatedBy = "Ilham"
};
db.Suppliers.Add(supplier);
db.SaveChanges();
}
else
{
Supplier supplier = db.Suppliers.Where(o => o.Id == model.Id).FirstOrDefault();
if (supplier != null)
{
supplier.Code = model.Code;
supplier.Name = model.Name;
supplier.Address = model.Address;
supplier.City_Code = model.City_Code;
supplier.IsActivated = model.IsActivated;
supplier.Modified = DateTime.Now;
supplier.ModifiedBy = "Ilham";
db.SaveChanges();
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static List <ProvinceViewModel> GetAll()
{
List <ProvinceViewModel> result = new List <ProvinceViewModel>();
using (var db = new BuahSayurContext())
{
result = (from p in db.Provinces
select new ProvinceViewModel
{
Id = p.Id,
Code = p.Code,
Name = p.Name,
IsActivated = p.IsActivated
}).ToList();
}
return(result);
}
public static ProvinceViewModel GetById(int id)
{
ProvinceViewModel result = new ProvinceViewModel();
using (var db = new BuahSayurContext())
{
result = (from p in db.Provinces
where p.Id == id
select new ProvinceViewModel
{
Id = p.Id,
Code = p.Code,
Name = p.Name,
IsActivated = p.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static bool Update(ProvinceViewModel model)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
if (model.Id == 0)
{
Province province = new Province
{
Code = model.Code,
Name = model.Name,
IsActivated = model.IsActivated,
Created = DateTime.Now,
CreatedBy = "Hasan",
};
db.Provinces.Add(province);
db.SaveChanges();
}
else
{
Province province = db.Provinces.Where(o => o.Id == model.Id).FirstOrDefault();
if (province != null)
{
province.Code = model.Code;
province.Name = model.Name;
province.IsActivated = model.IsActivated;
province.Modified = DateTime.Now;
province.ModifiedBy = "Hasan";
db.SaveChanges();
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static List <CityViewModel> GetByProvince(string provinceCode)
{
List <CityViewModel> result = new List <CityViewModel>();
using (var db = new BuahSayurContext())
{
result = (from ct in db.Cities
join p in db.Provinces
on ct.Province_Code equals p.Code
where ct.Province_Code == provinceCode
select new CityViewModel
{
Code = ct.Code,
Province_Code = ct.Province_Code,
Name = ct.Name,
PostalCode = ct.PostalCode,
IsActivated = ct.IsActivated
}).ToList();
}
return(result);
}
public static List <DeliveryOrderHeaderViewModel> GetByFilter(string filterString)
{
List <DeliveryOrderHeaderViewModel> result = new List <DeliveryOrderHeaderViewModel>();
using (var db = new BuahSayurContext())
{
result = (from doh in db.DeliveryOrders
join c in db.Customers
on doh.Customer_Username equals c.Username
select new DeliveryOrderHeaderViewModel
{
Id = doh.Id,
Customer_Username = c.Username,
Reference = doh.Reference,
SellingDate = doh.SellingDate
}).ToList();
result = result.Where(o => o.Reference.ToLower().Contains(filterString.ToLower()) || o.Customer_Username.ToLower().Contains(filterString.ToLower())).ToList();
}
return(result);
}
public static bool Delete(int id)
{
bool result = true;
try
{
using (var db = new BuahSayurContext())
{
Province province = db.Provinces.Where(o => o.Id == id).FirstOrDefault();
if (province != null)
{
db.Provinces.Remove(province);
db.SaveChanges();
}
}
}
catch (Exception ex)
{
Message = ex.Message;
result = false;
}
return(result);
}
public static CityViewModel GetById(int id)
{
CityViewModel result = new CityViewModel();
using (var db = new BuahSayurContext())
{
result = (from ct in db.Cities
join p in db.Provinces
on ct.Province_Code equals p.Code
where ct.Id == id
select new CityViewModel
{
Id = ct.Id,
Code = ct.Code,
Province_Code = ct.Province_Code,
ProvinceName = p.Name,
Name = ct.Name,
PostalCode = ct.PostalCode,
IsActivated = ct.IsActivated
}).FirstOrDefault();
}
return(result);
}
public static List <ItemViewModel> GetByExceptionMax(List <string> ExceptionListMax)
{
List <ItemViewModel> result = new List <ItemViewModel>();
using (var db = new BuahSayurContext())
{
result = (from it in db.Items
select new ItemViewModel
{
Id = it.Id,
Code = it.Code,
Name = it.Name,
Price_Purchasing = it.Price_Purchasing,
Category = it.Category,
Stock = it.Stock,
Weight = it.Weight,
Size = it.Size,
IsActivated = it.IsActivated
}).Where(o => !ExceptionListMax.Contains(o.Code) && o.Stock < 20).ToList();
}
return(result);
}
public static DeliveryOrderHeaderViewModel GetByReference(string reference)
{
DeliveryOrderHeaderViewModel result = new DeliveryOrderHeaderViewModel();
using (var db = new BuahSayurContext())
{
result = (from dOr in db.DeliveryOrders
join c in db.Customers
on dOr.Customer_Username equals c.Username into clf
from c in clf.DefaultIfEmpty()
where dOr.Reference == reference
select new DeliveryOrderHeaderViewModel
{
Id = dOr.Id,
Customer_Username = c.Username,
Reference = dOr.Reference,
SellingDate = dOr.SellingDate,
IsSent = dOr.IsSent,
IsReturned = dOr.IsReturned
}).FirstOrDefault();
}
return result;
}
public static List <ItemViewModel> GetAll()
{
List <ItemViewModel> result = new List <ItemViewModel>();
using (var db = new BuahSayurContext())
{
result = (from i in db.Items
select new ItemViewModel
{
Id = i.Id,
Code = i.Code,
Name = i.Name,
Price_Selling = i.Price_Selling,
Price_Purchasing = i.Price_Purchasing,
Category = i.Category,
Stock = i.Stock,
Weight = i.Weight,
Size = i.Size,
IsActivated = i.IsActivated
}).ToList();
}
return(result);
}
public static ItemViewModel GetById(int id)
{
ItemViewModel result = new ItemViewModel();
using (var db = new BuahSayurContext())
{
result = (from i in db.Items
where i.Id == id
select new ItemViewModel
{
Id = i.Id,
Code = i.Code,
Name = i.Name,
Price_Selling = i.Price_Selling,
Price_Purchasing = i.Price_Purchasing,
Category = i.Category,
Stock = i.Stock,
Weight = i.Weight,
Size = i.Size,
IsActivated = i.IsActivated
}).FirstOrDefault();
}
return(result);
}
