public List <ProductMasterListViewModel> GetList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProducts
join sn in db.tblProductScientificNames on r.ScientificNameID equals sn.ProductScientificNameID into joinsn
from rsn in joinsn.DefaultIfEmpty()
join cn in db.tblProductCommonNames on r.CommonNameID equals cn.ProductCommonNameID into joincn
from rcn in joincn.DefaultIfEmpty()
join size in db.tblProductSizes on r.SizeID equals size.ProductSizeID into joinsize
from rsize in joinsize.DefaultIfEmpty()
join ct in db.tblProductCultivationTypes on r.CultivationTypeID equals ct.ProductCultivationTypeID into joinct
from rct in joinct.DefaultIfEmpty()
orderby rsn.ProductScientificName, rcn.ProductCommonName, r.Descr, rsize.ProductSizeName, rct.ProductCultivationTypeName
select new ProductMasterListViewModel()
{
ProductID = r.ProductID,
ProductCode = r.ProductCode,
ScientificName = (rct != null ? rsn.ProductScientificName : ""),
CommonName = (rcn != null ? rcn.ProductCommonName : ""),
Descr = r.Descr,
SizeName = (rsize != null ? rsize.ProductSizeName : ""),
CultivationTypeName = (rct != null ? rct.ProductCultivationTypeName : ""),
Rate = r.Rate,
RateUplift = r.RateUpliftPerc,
CurrentStock = r.CurrentStock,
IsQtyReq = rsize.QuanReq ?? false
}).ToList());
}
}
public CompanyViewModel GetFirstCompanyDetail()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
tblCompany SaveModel = db.tblCompanies.FirstOrDefault();
if (SaveModel == null)
{
return(null);
}
return(new CompanyViewModel()
{
CompanyID = SaveModel.CompanyID,
CompanyName = SaveModel.CompanyName,
Address = SaveModel.Address,
PhoneNo = SaveModel.PhoneNo,
FaxNo = SaveModel.FaxNo,
EMailID = SaveModel.EMailID,
Website = SaveModel.Website,
Slogan = SaveModel.Slogan,
noreplyEmailID = SaveModel.noreplyEmailID,
noreplyPassword = SaveModel.noreplyEmailIDPassword,
noreplyOutgoingSMTPServerName = SaveModel.noreplyOutgoingSMTPServerName,
noreplyOutgoingSMTPPortNo = SaveModel.noreplyOutgoingSMTPPort
});
}
}
public SavingResult DeleteRecord(int ID)
{
SavingResult res = new SavingResult();
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var RecordToDelete = db.tblProducts.Find(ID);
if (RecordToDelete == null)
{
res.ExecutionResult = eExecutionResult.ValidationError;
res.ValidationError = "Selected record is already deleted or changed over network. Record not found.";
return(res);
}
db.tblProducts.Remove(RecordToDelete);
//--
try
{
db.SaveChanges();
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
ex = Common.Functions.FindFinalError(ex);
res.ExecutionResult = eExecutionResult.ErrorWhileExecuting;
res.Exception = ex;
}
}
return(res);
}
public List <SaleInvoiceListViewModel> GetList(int?CustomerID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblSaleInvoices
join grs in db.tblSaleInvoiceStatus on r.SaleInvoiceStatusID equals grs.SaleInvoiceStatusID into groupRS
from rs in groupRS.DefaultIfEmpty()
where CustomerID == null || r.CustomerID == CustomerID
select new SaleInvoiceListViewModel()
{
SaleInvoiceID = r.SaleInvoiceID,
InvoiceDate = r.InvoiceDate,
InvoiceNo = r.InvoiceNo,
SaleInvoiceStatusID = r.SaleInvoiceStatusID,
SaleInvoiceStatus = (rs != null ? rs.SaleInvoiceStatusName : "None"),
CustomerID = r.CustomerID,
BusinessName = r.BusinessName,
ShippingDate = r.ShippingDate,
ArrivalDate = r.ArrivalDate,
DomesticFlight = r.DomesticFlight,
InternationalFlight = r.InternationalFlight,
TotalQty = r.TotalQuan,
EstBoxes = r.EstBoxes
}).ToList());
}
}
public SaleInvoiceListViewModel FindByIDGetListViewModel(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var r = db.tblSaleInvoices.Find(ID);
if (r == null)
{
return(null);
}
else
{
return(new SaleInvoiceListViewModel()
{
SaleInvoiceID = r.SaleInvoiceID,
InvoiceDate = r.InvoiceDate,
InvoiceNo = r.InvoiceNo,
SaleInvoiceStatusID = r.SaleInvoiceStatusID,
SaleInvoiceStatus = (r.tblSaleInvoiceStatu != null ? r.tblSaleInvoiceStatu.SaleInvoiceStatusName : "None"),
CustomerID = r.CustomerID,
BusinessName = r.BusinessName,
ShippingDate = r.ShippingDate,
ArrivalDate = r.ArrivalDate,
DomesticFlight = r.DomesticFlight,
InternationalFlight = r.InternationalFlight,
TotalQty = r.TotalQuan,
EstBoxes = r.EstBoxes
});
}
}
}
public bool CheckDuplicate(int ID, int SONo)
{
using (DAL.dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(CheckDuplicate(ID, SONo, db));
}
}
public CustomerViewModel FindByID(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var r = db.tblUsers.Find(ID);
if (r == null)
{
return(null);
}
return(new CustomerViewModel()
{
UserID = r.UserID,
BusinessName = r.BusinessName,
ContactName = r.ContactName,
EMailID = r.EmailID,
Address = r.Address,
City = r.City,
Postcode = r.PostCode,
Country = r.Country,
IntPhoneNo = r.IntPhoneNo,
AirportDestCity = r.AirportDestCity,
IsApproved = r.IsApproved
});
}
}
public List <SaleInvoiceProducDetailReportModel> GetSaleInvoiceReportProductDetail(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var SaveModel = db.tblSaleInvoices.Find(ID);
if (SaveModel == null)
{
return(null);
}
else
{
List <SaleInvoiceProducDetailReportModel> ViewModel = SaveModel.tblSaleInvoiceProductDetails.Select <tblSaleInvoiceProductDetail, SaleInvoiceProducDetailReportModel>(rp => new SaleInvoiceProducDetailReportModel()
{
ProductID = rp.ProductID,
ScientificName = rp.tblProduct.tblProductScientificName.ProductScientificName,
CommonName = rp.tblProduct.tblProductCommonName.ProductCommonName,
Descr = rp.tblProduct.Descr,
SizeName = rp.tblProduct.tblProductSize.ProductSizeName,
//CultivationTypeName = rp.tblProduct.tblProductCultivationType.ProductCultivationTypeName,
Rate = rp.Rate,
//CurrentStock = rp.tblProduct.CurrentStock,
Qty = rp.Quan,
Amt = rp.Amt
}).ToList();
return(ViewModel);
}
}
}
public bool CheckDuplicate(int ID, int ScientificNameID, int CommonNameID, string Descr, int SizeID, int CultivationTypeID)
{
using (DAL.dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(CheckDuplicate(ID, ScientificNameID, CommonNameID, Descr, SizeID, CultivationTypeID, db));
}
}
public ProductMasterListViewModel FindByIDGetListViewModel(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProducts
join sn in db.tblProductScientificNames on r.ScientificNameID equals sn.ProductScientificNameID into joinsn
from rsn in joinsn.DefaultIfEmpty()
join cn in db.tblProductCommonNames on r.CommonNameID equals cn.ProductCommonNameID into joincn
from rcn in joincn.DefaultIfEmpty()
join size in db.tblProductSizes on r.SizeID equals size.ProductSizeID into joinsize
from rsize in joinsize.DefaultIfEmpty()
join ct in db.tblProductCultivationTypes on r.CultivationTypeID equals ct.ProductCultivationTypeID into joinct
from rct in joinct.DefaultIfEmpty()
where r.ProductID == ID
select new ProductMasterListViewModel()
{
ProductID = r.ProductID,
ProductCode = r.ProductCode,
ScientificName = (rct != null ? rsn.ProductScientificName : ""),
CommonName = (rcn != null ? rcn.ProductCommonName : ""),
Descr = r.Descr,
SizeName = (rsize != null ? rsize.ProductSizeName : ""),
CultivationTypeName = (rct != null ? rct.ProductCultivationTypeName : ""),
Rate = r.Rate,
RateUplift = r.RateUpliftPerc,
CurrentStock = r.CurrentStock
}).FirstOrDefault());
}
}
public BeforeDeleteValidationResult ValidateBeforeDelete(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(ValidateBeforeDelete(ID, db));
}
}
public bool CheckDuplicateEmailID(int ID, string EMailID)
{
using (DAL.dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(CheckDuplicateEmailID(ID, EMailID, db));
}
}
public ProductMasterViewModel FindByID(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var r = db.tblProducts.Find(ID);
if (r == null)
{
return(null);
}
return(new ProductMasterViewModel()
{
ProductID = r.ProductID,
ProductCode = r.ProductCode,
ScientificNameID = r.ScientificNameID,
CommonNameID = r.CommonNameID,
Descr = r.Descr,
SizeID = r.SizeID,
CultivationTypeID = r.CultivationTypeID,
Rate = r.Rate,
RateUplift = r.RateUpliftPerc,
CurrentStock = r.CurrentStock
});
}
}
public int GenerateNewProductCode()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((db.tblProducts.Max(r => (int?)r.ProductCode) ?? 0) + 1);
}
}
public List <Models.Inventory.UpdateStockViewModel> GetStockList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProducts
join sn in db.tblProductScientificNames on r.ScientificNameID equals sn.ProductScientificNameID into joinsn
from rsn in joinsn.DefaultIfEmpty()
join cn in db.tblProductCommonNames on r.CommonNameID equals cn.ProductCommonNameID into joincn
from rcn in joincn.DefaultIfEmpty()
join size in db.tblProductSizes on r.SizeID equals size.ProductSizeID into joinsize
from rsize in joinsize.DefaultIfEmpty()
join ct in db.tblProductCultivationTypes on r.CultivationTypeID equals ct.ProductCultivationTypeID into joinct
from rct in joinct.DefaultIfEmpty()
select new Models.Inventory.UpdateStockViewModel()
{
ProductID = r.ProductID,
ProductCode = r.ProductCode,
ScientificName = (rct != null ? rsn.ProductScientificName : ""),
CommonName = (rcn != null ? rcn.ProductCommonName : ""),
Descr = r.Descr,
Size = (rsize != null ? rsize.ProductSizeName : ""),
CultivationType = (rct != null ? rct.ProductCultivationTypeName : ""),
Rate = r.Rate,
CurrentStock = (int)r.CurrentStock
}).ToList());
}
}
public bool CheckDuplicate(int ID, string Value)
{
using (DAL.dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(CheckDuplicate(ID, Value, db));
}
}
public int GenerateNextSONo()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(GenerateNextSONo(db));
}
}
public SaleOrderViewModel FindByIDWithAllProducts(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var SO = db.tblSaleOrders.Find(ID);
if (SO == null)
{
return(null);
}
else
{
SaleOrderViewModel ViewModel = new SaleOrderViewModel()
{
SaleOrderID = SO.SaleOrderID,
SODate = SO.SODate,
SONo = SO.SONo,
CustomerID = SO.CustomerID,
BusinessName = SO.BusinessName,
ContactName = SO.ContactName,
Address = SO.Address,
City = SO.City,
Postcode = SO.Postcode,
Country = SO.Country,
AirportDestCity = SO.AirportDestCity,
IntPhoneNo = SO.IntPhoneNo,
EstDelDate = SO.EstDelDate,
};
ViewModel.Products = (from r in db.tblProducts
join sn in db.tblProductScientificNames on r.ScientificNameID equals sn.ProductScientificNameID into joinsn
from rsn in joinsn.DefaultIfEmpty()
join cn in db.tblProductCommonNames on r.CommonNameID equals cn.ProductCommonNameID into joincn
from rcn in joincn.DefaultIfEmpty()
join size in db.tblProductSizes on r.SizeID equals size.ProductSizeID into joinsize
from rsize in joinsize.DefaultIfEmpty()
join ct in db.tblProductCultivationTypes on r.CultivationTypeID equals ct.ProductCultivationTypeID into joinct
from rct in joinct.DefaultIfEmpty()
join sopd in
(from saleordprod in db.tblSaleOrderProductDetails where saleordprod.SaleOrderID == ID select saleordprod)
on r.ProductID equals sopd.ProductID into joinsopd
from rsopd in joinsopd.DefaultIfEmpty()
select new SaleOrderProducDetailViewModel()
{
ProductID = r.ProductID,
ScientificName = (rct != null ? rsn.ProductScientificName : ""),
CommonName = (rcn != null ? rcn.ProductCommonName : ""),
Descr = r.Descr,
SizeName = (rsize != null ? rsize.ProductSizeName : ""),
CultivationTypeName = (rct != null ? rct.ProductCultivationTypeName : ""),
CurrentStock = (rsopd != null ? r.CurrentStock : 0),
Rate = (rsopd != null ? rsopd.Rate : 0),
OrderQty = (rsopd != null ? rsopd.Quan : 0)
}).ToList();
return(ViewModel);
}
}
}
public bool CheckDuplicate(int ID, int ScientificNameID, int CommonNameID, string Descr, int SizeID, int CultivationTypeID,
dbUltraCoralEntities db)
{
return(db.tblProducts.FirstOrDefault(r => r.ProductID != ID &&
r.ScientificNameID == ScientificNameID &&
r.CommonNameID == CommonNameID &&
r.Descr == Descr &&
r.SizeID == SizeID &&
r.CultivationTypeID == CultivationTypeID) != null);
}
public SavingResult UpdateRateUplift(int ProductID, decimal RateUplift)
{
SavingResult res = new SavingResult();
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
tblProduct SaveModel = db.tblProducts.Find(ProductID);
if (SaveModel == null)
{
res.ExecutionResult = eExecutionResult.ValidationError;
res.ValidationError = "Product not found";
return(res);
}
SaveModel = db.tblProducts.FirstOrDefault(r => r.ProductID == ProductID);
if (SaveModel == null)
{
res.ExecutionResult = eExecutionResult.ValidationError;
res.ValidationError = "Selected user has been deleted over network. Can not find product details. Please retry.";
return(res);
}
if (SaveModel.RateUpliftPerc == RateUplift) // if quantity has not changed then don't update quantity.
{
return(new SavingResult()
{
ExecutionResult = eExecutionResult.NotExecutedYet
});
}
SaveModel.RateUpliftPerc = RateUplift;
SaveModel.redt = DateTime.Now;
//SaveModel.reuid = Common.Props.LoginUser.UserID;
db.tblProducts.Attach(SaveModel);
db.Entry(SaveModel).State = System.Data.Entity.EntityState.Modified;
//--
try
{
db.SaveChanges();
res.PrimeKeyValue = SaveModel.ProductID;
res.ExecutionResult = eExecutionResult.CommitedSucessfuly;
}
catch (Exception ex)
{
ex = Common.Functions.FindFinalError(ex);
res.ExecutionResult = eExecutionResult.ErrorWhileExecuting;
res.Exception = ex;
}
}
return(res);
}
public List <Models.Users.UserApprovalViewModel> GetPendingApprovalUserList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return(db.tblUsers.Where(r => !(r.IsApproved ?? false)).Select <tblUser, Models.Users.UserApprovalViewModel>(r =>
new Models.Users.UserApprovalViewModel()
{
UserID = r.UserID,
FullName = r.FullName,
EMailID = r.EmailID
}).ToList());
}
}
public List <ProductCultivationTypeViewModel> GetList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProductCultivationTypes
orderby r.ProductCultivationTypeName
select new ProductCultivationTypeViewModel()
{
ProductCultivationTypeID = r.ProductCultivationTypeID,
ProductCultivationTypeName = r.ProductCultivationTypeName
}).ToList());
}
}
public List <ProductScientificNameViewModel> GetList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProductScientificNames
orderby r.ProductScientificName
select new ProductScientificNameViewModel()
{
ProductScientificNameID = r.ProductScientificNameID,
ProductScientificName = r.ProductScientificName,
IsAlive = r.IsAlive
}).ToList());
}
}
public List <Models.Users.UserAdminListViewModel> GetAdminUsers()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
int AdminRoleID = (int)eUserRoleID.Admin;
return(db.tblUsers.Where(r => r.UserRoleID == AdminRoleID && (r.IsApproved ?? false)).Select <tblUser, Models.Users.UserAdminListViewModel>(r =>
new Models.Users.UserAdminListViewModel()
{
UserID = r.UserID,
FullName = r.FullName,
EMailID = r.EmailID
}).ToList());
}
}
public static SelectList GetSelectList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var list = (from r in db.tblProductCommonNames
orderby r.ProductCommonName
select new ProductCommonNameSelectListViewModel()
{
ProductCommonNameID = r.ProductCommonNameID,
ProductCommonName = r.ProductCommonName
}).ToList();
return(new SelectList(list, "ProductCommonNameID", "ProductCommonName"));
}
}
public List <ProductSizeViewModel> GetList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
return((from r in db.tblProductSizes
orderby r.ProductSizeName
select new ProductSizeViewModel()
{
ProductSizeID = r.ProductSizeID,
ProductSizeName = r.ProductSizeName,
QuanReq = r.QuanReq ?? false
}).ToList());
}
}
public static SelectList GetSelectionList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var list = (from r in db.tblSaleInvoiceStatus
select new SaleInvoiceStatusSelectListModel()
{
SaleInvoiceStatusID = r.SaleInvoiceStatusID,
SaleInvoiceStatusName = r.SaleInvoiceStatusName
}).ToList();
return(new SelectList(list, "SaleInvoiceStatusID", "SaleInvoiceStatusName"));
}
}
public List <SaleInvoiceReportModel> GetSaleInvoiceReportHeader(int ID)
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var SaveModel = db.tblSaleInvoices.Find(ID);
if (SaveModel == null)
{
return(null);
}
else
{
SaleInvoiceReportModel ViewModel = new SaleInvoiceReportModel()
{
SaleInvoiceID = SaveModel.SaleInvoiceID,
InvoiceDate = SaveModel.InvoiceDate,
InvoiceNo = SaveModel.InvoiceNo,
CustomerID = SaveModel.CustomerID,
BusinessName = SaveModel.BusinessName,
Address = SaveModel.Address,
City = SaveModel.City,
Postcode = SaveModel.Postcode,
Country = SaveModel.Country,
ContactName = SaveModel.ContactName,
AirportDestCity = SaveModel.AirportDestCity,
IntPhoneNo = SaveModel.IntPhoneNo,
EmailContact = SaveModel.EmailContact,
//EstDelDate = SaveModel.EstDelDate,
//AWBNo = SaveModel.FreightAWBNo,
//Flight1 = SaveModel.FreightFlight1,
//Flight2 = SaveModel.FreightFlight2,
TotalQuan = SaveModel.TotalQuan,
TotalGAmt = SaveModel.TotalGAmt,
DomesticFreightCharges = SaveModel.DomesticFreightCharges,
BoxCharges = SaveModel.BoxCharges,
IntFreightCharges = SaveModel.IntFreightCharges,
TTFee = SaveModel.TTFee,
PreviousCredit = SaveModel.PreviousCredit,
TotalFreight = SaveModel.TotalFreight,
TotalPayableAmt = SaveModel.TotalPayableAmt,
};
return(new List <SaleInvoiceReportModel>()
{
ViewModel
});
}
}
}
public BeforeDeleteValidationResult ValidateBeforeDelete(int ID, dbUltraCoralEntities db)
{
BeforeDeleteValidationResult res = new BeforeDeleteValidationResult()
{
IsValidForDelete = true
};
//if (db.tblSaleInvoices.FirstOrDefault(r => r.SaleOrderID == ID) != null)
//{
// res.IsValidForDelete = false;
// res.ValidationMessage = "Already selected in invoice.";
//}
return(res);
}
public static SelectList GetSelectList()
{
using (dbUltraCoralEntities db = new dbUltraCoralEntities())
{
var list = (from r in db.tblSaleOrders
select new SaleOrderSelectListViewModel()
{
SaleOrderID = r.SaleOrderID,
SONo = r.SONo,
SODate = r.SODate
}).ToList();
return(new SelectList(list, "SaleOrderID", "SONo"));
}
}