public IQueryable <ComboBoxResult> GetCustomPerson(int Id, string term)
{
int DocTypeId = Id;
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(DocTypeId, DivisionId, SiteId);
string[] PersonRoles = null;
if (!string.IsNullOrEmpty(settings.filterPersonRoles))
{
PersonRoles = settings.filterPersonRoles.Split(",".ToCharArray());
}
else
{
PersonRoles = new string[] { "NA" };
}
string DivIdStr = "|" + DivisionId.ToString() + "|";
string SiteIdStr = "|" + SiteId.ToString() + "|";
var list = (from p in db.Persons
join bus in db.BusinessEntity on p.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join pp in db.PersonProcess on p.PersonID equals pp.PersonId into PersonProcessTable
from PersonProcessTab in PersonProcessTable.DefaultIfEmpty()
join pr in db.PersonRole on p.PersonID equals pr.PersonId into PersonRoleTable from PersonRoleTab in PersonRoleTable.DefaultIfEmpty()
where PersonProcessTab.ProcessId == settings.ProcessId &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (p.Name.ToLower().Contains(term.ToLower()) || p.Code.ToLower().Contains(term.ToLower()))) &&
(string.IsNullOrEmpty(settings.filterPersonRoles) ? 1 == 1 : PersonRoles.Contains(PersonRoleTab.RoleDocTypeId.ToString())) &&
BusinessEntityTab.DivisionIds.IndexOf(DivIdStr) != -1 &&
BusinessEntityTab.SiteIds.IndexOf(SiteIdStr) != -1 &&
(p.IsActive == null ? 1 == 1 : p.IsActive == true)
group new { p } by new { p.PersonID } into Result
orderby Result.Max(m => m.p.Name)
select new ComboBoxResult
{
id = Result.Key.PersonID.ToString(),
text = Result.Max(m => m.p.Name + ", " + m.p.Suffix + " [" + m.p.Code + "]"),
}
);
return(list);
}
public string ValidateCostCenter(int DocTypeId, int HeaderId, int JobWorkerId, string CostCenterName)
{
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var Settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(DocTypeId, DivisionId, SiteId);
var LedgerAccountId = new LedgerAccountService(_unitOfWork).GetLedgerAccountByPersondId(JobWorkerId).LedgerAccountId;
string ValidationMsg = "";
if (Settings.IsPersonWiseCostCenter == true)
{
var CostCenter = (db.CostCenter.AsNoTracking().Where(m => m.CostCenterName == CostCenterName &&
m.ReferenceDocTypeId == DocTypeId && m.SiteId == SiteId && m.DivisionId == DivisionId).FirstOrDefault());
if (CostCenter != null)
{
if (CostCenter.LedgerAccountId != LedgerAccountId)
{
ValidationMsg += "CostCenter belongs to a different person. ";
}
}
}
if (Settings.isUniqueCostCenter == true)
{
var CostCenter = db.CostCenter.AsNoTracking().Where(m => m.CostCenterName == CostCenterName &&
m.ReferenceDocTypeId == DocTypeId && m.SiteId == SiteId && m.DivisionId == DivisionId).FirstOrDefault();
if (CostCenter != null)
{
var UniqueCostCenter = (from p in db.SaleQuotationHeader
where p.CostCenterId == CostCenter.CostCenterId && p.SaleQuotationHeaderId != HeaderId && p.DocTypeId == DocTypeId &&
p.SiteId == SiteId && p.DivisionId == DivisionId
select p
).FirstOrDefault();
if (UniqueCostCenter != null)
{
ValidationMsg += "CostCenter Already exists";
}
}
}
return(ValidationMsg);
}
public IQueryable <ComboBoxResult> GetCustomProductGroups(int Id, string term)
{
var SaleQuotation = new SaleQuotationHeaderService(_unitOfWork).Find(Id);
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotation.DocTypeId, SaleQuotation.DivisionId, SaleQuotation.SiteId);
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
string[] ProductGroups = null;
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
ProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
}
else
{
ProductGroups = new string[] { "NA" };
}
return(from p in db.ProductGroups
where (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : ProductGroups.Contains(p.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductGroupName.ToLower().Contains(term.ToLower()))
orderby p.ProductGroupName
select new ComboBoxResult
{
id = p.ProductGroupId.ToString(),
text = p.ProductGroupName,
});
}
public IEnumerable <SaleEnquiryHeaderListViewModel> GetPendingSaleEnquiriesWithPatternMatch(int Id, string term, int Limiter)
{
var SaleQuotationHeader = new SaleQuotationHeaderService(_unitOfWork).Find(Id);
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotationHeader.DocTypeId, SaleQuotationHeader.DivisionId, SaleQuotationHeader.SiteId);
string settingProductTypes = "";
string settingProductDivision = "";
string settingProductCategory = "";
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
settingProductTypes = "|" + settings.filterProductTypes.Replace(",", "|,|") + "|";
}
if (!string.IsNullOrEmpty(settings.FilterProductDivision))
{
settingProductDivision = "|" + settings.FilterProductDivision.Replace(",", "|,|") + "|";
}
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
string[] contraSites = null;
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
contraSites = settings.filterContraSites.Split(",".ToCharArray());
}
else
{
contraSites = new string[] { "NA" };
}
string[] contraDivisions = null;
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
}
else
{
contraDivisions = new string[] { "NA" };
}
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settingProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
string[] ProductDivision = null;
if (!string.IsNullOrEmpty(settings.FilterProductDivision))
{
ProductDivision = settingProductDivision.Split(",".ToCharArray());
}
else
{
ProductDivision = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
join Pt in db.Product on p.ProductId equals Pt.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
join Fp in db.FinishedProduct on p.ProductId equals Fp.ProductId into FinishedProductTable from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
where (
string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension3Tab.Dimension3Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension4Tab.Dimension4Name.ToLower().Contains(term.ToLower())
) && p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains("|" + ProductTab.ProductGroup.ProductTypeId.ToString() + "|")) &&
(string.IsNullOrEmpty(settings.FilterProductDivision) ? 1 == 1 : ProductDivision.Contains("|" + ProductTab.DivisionId.ToString() + "|"))
orderby p.SaleEnquiryNo
select new SaleEnquiryHeaderListViewModel
{
DocNo = p.SaleEnquiryNo,
SaleEnquiryLineId = p.SaleEnquiryLineId,
ProductName = ProductTab.ProductName,
Dimension1Name = Dimension1Tab.Dimension1Name,
Dimension2Name = Dimension2Tab.Dimension2Name,
Dimension3Name = Dimension3Tab.Dimension3Name,
Dimension4Name = Dimension4Tab.Dimension4Name,
}).Take(Limiter);
return(list);
}
public IQueryable <ComboBoxResult> GetPendingSaleEnquiryHelpList(int Id, string term)
{
var SaleQuotationHeader = new SaleQuotationHeaderService(_unitOfWork).Find(Id);
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotationHeader.DocTypeId, SaleQuotationHeader.DivisionId, SaleQuotationHeader.SiteId);
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
string[] contraSites = null;
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
contraSites = settings.filterContraSites.Split(",".ToCharArray());
}
else
{
contraSites = new string[] { "NA" };
}
string[] contraDivisions = null;
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
}
else
{
contraDivisions = new string[] { "NA" };
}
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
join t in db.Persons on p.BuyerId equals t.PersonID into table
from tab in table.DefaultIfEmpty()
join Pt in db.Product on p.ProductId equals Pt.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join Fp in db.FinishedProduct on p.ProductId equals Fp.ProductId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join D in db.DocumentType on p.DocTypeId equals D.DocumentTypeId into DocumentTypeTable
from DocumentTyoeTab in DocumentTypeTable.DefaultIfEmpty()
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(ProductTab.ProductGroup.ProductTypeId.ToString()))
group new { p, tab.Code, DocumentTyoeTab } by p.SaleEnquiryHeaderId into g
orderby g.Max(m => m.p.EnquiryDate)
select new ComboBoxResult
{
text = g.Max(m => m.DocumentTyoeTab.DocumentTypeShortName) + "-" + g.Max(m => m.p.SaleEnquiryNo) + " {" + g.Max(m => m.Code) + "}",
id = g.Key.ToString(),
});
return(list);
}
public IEnumerable <SaleQuotationLineViewModel> GetSaleEnquiriesForFilters(SaleQuotationLineFilterViewModel vm)
{
byte?UnitConvForId = new SaleQuotationHeaderService(_unitOfWork).Find(vm.SaleQuotationHeaderId).UnitConversionForId;
var SaleQuotation = new SaleQuotationHeaderService(_unitOfWork).Find(vm.SaleQuotationHeaderId);
var Settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotation.DocTypeId, SaleQuotation.DivisionId, SaleQuotation.SiteId);
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(vm.ProductId))
{
ProductIdArr = vm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(vm.SaleEnquiryHeaderId))
{
SaleOrderIdArr = vm.SaleEnquiryHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] Dimension1 = null;
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Dimension1 = vm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1 = new string[] { "NA" };
}
string[] Dimension2 = null;
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Dimension2 = vm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2 = new string[] { "NA" };
}
string[] Dimension3 = null;
if (!string.IsNullOrEmpty(vm.Dimension3Id))
{
Dimension3 = vm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dimension3 = new string[] { "NA" };
}
string[] Dimension4 = null;
if (!string.IsNullOrEmpty(vm.Dimension4Id))
{
Dimension4 = vm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dimension4 = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(vm.ProductGroupId))
{
ProductGroupIdArr = vm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
if (!string.IsNullOrEmpty(vm.DealUnitId))
{
Unit Dealunit = new UnitService(_unitOfWork).Find(vm.DealUnitId);
var temp = (from p in db.ViewSaleEnquiryBalanceForQuotation
join t in db.SaleEnquiryHeader on p.SaleEnquiryHeaderId equals t.SaleEnquiryHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.SaleEnquiryLine on p.SaleEnquiryLineId equals t1.SaleEnquiryLineId into table1
from tab1 in table1.DefaultIfEmpty()
from tab2 in table2.DefaultIfEmpty()
join t3 in db.UnitConversion on new { p1 = p.ProductId, DU1 = vm.DealUnitId, U1 = UnitConvForId ?? 0 } equals new { p1 = t3.ProductId ?? 0, DU1 = t3.ToUnitId, U1 = t3.UnitConversionForId } into table3
join FP in db.FinishedProduct on p.ProductId equals FP.ProductId into tableFinishedProduct
from tabFinishedProduct in tableFinishedProduct.DefaultIfEmpty()
from tab3 in table3.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.SaleEnquiryHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleEnquiryHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension1Id) ? 1 == 1 : Dimension1.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension2Id) ? 1 == 1 : Dimension2.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension3Id) ? 1 == 1 : Dimension3.Contains(p.Dimension3Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension4Id) ? 1 == 1 : Dimension4.Contains(p.Dimension4Id.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby tab.DocDate, tab.DocNo
select new SaleQuotationLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Dimension3Name = tab1.Dimension3.Dimension3Name,
Dimension4Name = tab1.Dimension4.Dimension4Name,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Specification = tab1.Specification,
SaleEnquiryBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
Rate = vm.Rate,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
SaleQuotationHeaderId = vm.SaleQuotationHeaderId,
SaleEnquiryLineId = p.SaleEnquiryLineId,
UnitId = tab2.UnitId,
SaleEnquiryDocNo = p.SaleEnquiryNo,
DealUnitId = (vm.DealUnitId),
UnitConversionMultiplier = Math.Round((tab3 == null ? 1 : tab3.ToQty / tab3.FromQty), (tab3 == null ? tab2.Unit.DecimalPlaces : Dealunit.DecimalPlaces)),
UnitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealUnitDecimalPlaces = (tab3 == null ? tab2.Unit.DecimalPlaces : Dealunit.DecimalPlaces)
});
return(temp);
}
else
{
var temp = (from p in db.ViewSaleEnquiryBalanceForQuotation
join t in db.SaleEnquiryHeader on p.SaleEnquiryHeaderId equals t.SaleEnquiryHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.SaleEnquiryLine on p.SaleEnquiryLineId equals t1.SaleEnquiryLineId into table1
from tab1 in table1.DefaultIfEmpty()
from tab2 in table2.DefaultIfEmpty()
join FP in db.FinishedProduct on p.ProductId equals FP.ProductId into tableFinishedProduct
from tabFinishedProduct in tableFinishedProduct.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.SaleEnquiryHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleEnquiryHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension1Id) ? 1 == 1 : Dimension1.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension2Id) ? 1 == 1 : Dimension2.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension3Id) ? 1 == 1 : Dimension3.Contains(p.Dimension3Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension4Id) ? 1 == 1 : Dimension4.Contains(p.Dimension4Id.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new SaleQuotationLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension1Id = p.Dimension1Id,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Dimension2Id = p.Dimension2Id,
Dimension3Name = tab1.Dimension3.Dimension3Name,
Dimension3Id = p.Dimension3Id,
Dimension4Name = tab1.Dimension4.Dimension4Name,
Dimension4Id = p.Dimension4Id,
Specification = tab1.Specification,
SaleEnquiryBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
Rate = vm.Rate,
SaleEnquiryDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
SaleQuotationHeaderId = vm.SaleQuotationHeaderId,
SaleEnquiryLineId = p.SaleEnquiryLineId,
UnitId = tab2.UnitId,
DealUnitId = tab2.UnitId,
UnitConversionMultiplier = 1,
UnitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealUnitDecimalPlaces = tab2.Unit.DecimalPlaces,
}
);
return(temp);
}
}
