private void PrepareViewBag(DesignConsumptionLineViewModel svm)
{
var ProductFaceContentGroups = from p in db.Product
join pg in db.ProductGroups on p.ReferenceDocId equals pg.ProductGroupId into ProductGroupTable
from ProductGroupTab in ProductGroupTable.DefaultIfEmpty()
join fp in db.FinishedProduct on ProductGroupTab.ProductGroupId equals fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join pcl in db.ProductContentLine on FinishedProductTab.FaceContentId equals pcl.ProductContentHeaderId into ProductContentLineTable
from ProductContentLineTab in ProductContentLineTable.DefaultIfEmpty()
where p.ProductId == svm.BaseProductId && ((int?)ProductContentLineTab.ProductGroupId ?? 0) != 0
group new { ProductContentLineTab } by new { ProductContentLineTab.ProductGroupId } into Result
select new
{
ProductGroupId = Result.Key.ProductGroupId
};
var TotalMainContents = (from L in db.BomDetail
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == svm.BaseProductId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) != 0
group new { L } by new { L.BaseProductId } into Result
select new
{
TotalQty = Result.Sum(i => i.L.Qty)
}).FirstOrDefault();
var TotalOtherContents = (from L in db.BomDetail
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == svm.BaseProductId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) == 0
group new { L } by new { L.BaseProductId } into Result
select new
{
TotalQty = Result.Sum(i => i.L.Qty)
}).FirstOrDefault();
if (TotalMainContents != null && svm.Weight != 0)
{
Decimal TotalMainContentPercentage = Math.Round(TotalMainContents.TotalQty * 100 / svm.Weight, 2);
ViewBag.LastTransaction = TotalMainContentPercentage + "% Main Contents filled, " + (100 - TotalMainContentPercentage) + " remaining.";
}
if (TotalOtherContents != null && svm.Weight != 0)
{
Decimal TotalOtherContentPercentage = Math.Round(TotalOtherContents.TotalQty * 100 / svm.Weight, 2);
ViewBag.LastTransaction = ViewBag.LastTransaction + (TotalOtherContentPercentage + "% Other Contents filled.").ToString();
}
}
public JsonResult IsProductContent(int BaseProductId, int ProductId)
{
bool IsContent = true;
var ProductFaceContentGroups = from p in db.Product
join pg in db.ProductGroups on p.ReferenceDocId equals pg.ProductGroupId into ProductGroupTable
from ProductGroupTab in ProductGroupTable.DefaultIfEmpty()
join fp in db.FinishedProduct on ProductGroupTab.ProductGroupId equals fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join pcl in db.ProductContentLine on FinishedProductTab.FaceContentId equals pcl.ProductContentHeaderId into ProductContentLineTable
from ProductContentLineTab in ProductContentLineTable.DefaultIfEmpty()
where p.ProductId == BaseProductId && ((int?)ProductContentLineTab.ProductGroupId ?? 0) != 0
group new { ProductContentLineTab } by new { ProductContentLineTab.ProductGroupId } into Result
select new
{
ProductGroupId = Result.Key.ProductGroupId
};
var temp = (from p in db.Product
join pcon in ProductFaceContentGroups on p.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where p.ProductId == ProductId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) != 0
select new
{
ProductId = p.ProductId
}).FirstOrDefault();
if (temp != null)
{
IsContent = true;
}
else
{
IsContent = false;
}
return(Json(IsContent));
}
public JsonResult GetConsumptionTotalQty(int BaseProductId, Decimal TotalWeight, Decimal BomQty, int BomDetailId, int BaseProcessId)
{
var ProductFaceContentGroups = from p in db.Product
join pg in db.ProductGroups on p.ReferenceDocId equals pg.ProductGroupId into ProductGroupTable
from ProductGroupTab in ProductGroupTable.DefaultIfEmpty()
join fp in db.FinishedProduct on ProductGroupTab.ProductGroupId equals fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join pcl in db.ProductContentLine on FinishedProductTab.FaceContentId equals pcl.ProductContentHeaderId into ProductContentLineTable
from ProductContentLineTab in ProductContentLineTable.DefaultIfEmpty()
where p.ProductId == BaseProductId && ((int?)ProductContentLineTab.ProductGroupId ?? 0) != 0
group new { ProductContentLineTab } by new { ProductContentLineTab.ProductGroupId } into Result
select new
{
ProductGroupId = Result.Key.ProductGroupId
};
Decimal TotalFillQty = 0;
var temp = (from L in db.BomDetail
join p in db.Product on L.ProductId equals p.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == BaseProductId && L.BomDetailId != BomDetailId && L.BaseProcessId == BaseProcessId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) != 0
group(L) by(L.BaseProductId) into Result
select new
{
TotalQty = Result.Sum(i => i.Qty)
}).FirstOrDefault();
if (temp != null)
{
TotalFillQty = temp.TotalQty;
}
return(Json(TotalFillQty));
}
public IQueryable <CarpetIndexViewModel> GetCarpetListForIndex(bool sample)
{
//return (from p in db.Products
// join t in db.ProductCategory on p.ProductCategoryId equals t.ProductCategoryId into table
// from tab in table.DefaultIfEmpty()
// join t1 in db.ProductTypes on tab.ProductTypeId equals t1.ProductTypeId into table1
// from tab1 in table1.DefaultIfEmpty()
// where tab1.IsCustomUI != null
// orderby p.ProductName, p.ProductGroup.ProductGroupName
// select new CarpetIndexViewModel
// {
// ProductId = p.ProductId,
// ProductName = p.ProductName,
// ProductGroupName = p.ProductGroup.ProductGroupName,
// ProductCategoryId = tab.ProductCategoryId,
// ProductCategoryName = tab.ProductCategoryName,
// ProductGroupId = p.ProductGroupId,
// ProductDesignName=p.ProductDesign.ProductDesignName,
// }
// );
//return (from p in db.ProductGroups
// join t in db.FinishedProduct on p.ProductGroupId equals t.ProductGroupId into FinishedProductTable
// from FinishedProductTab in FinishedProductTable
// join t1 in db.ProductCategory on FinishedProductTable.FirstOrDefault().ProductCategoryId equals t1.ProductCategoryId into table1
// from tab1 in table1
// join t2 in db.ProductTypes on tab1.ProductTypeId equals t2.ProductTypeId into table2
// from tab2 in table2
// where tab2.ProductTypeName==ProductTypeConstants.Rug
// orderby p.ProductGroupName
// select new CarpetIndexViewModel
// {
// ProductId = FinishedProductTab.ProductId,
// ProductName = FinishedProductTab.ProductName,
// ProductGroupName = p.ProductGroupName,
// ProductCategoryId = tab1.ProductCategoryId,
// ProductCategoryName = tab1.ProductCategoryName,
// ProductGroupId = p.ProductGroupId,
// ProductDesignName = FinishedProductTab.ProductDesign.ProductDesignName,
// }
// );
int typeid = new ProductTypeService(_unitOfWork).GetProductTypeByName(ProductTypeConstants.Rug).ProductTypeId;
//return (from p in db.ProductGroups
// join t in db.FinishedProduct on p.ProductGroupId equals t.ProductGroupId
// where p.ProductTypeId == typeid
// group t by new { t.ProductGroupId, t.ProductGroup.ProductGroupName } into table
// orderby table.Key.ProductGroupName
// select new CarpetIndexViewModel
// {
// ProductGroupId = table.Key.ProductGroupId ?? 0,
// ProductGroupName = table.Key.ProductGroupName,
// ProductCategoryName = table.FirstOrDefault().ProductCategory.ProductCategoryName,
// ProductDesignName = table.FirstOrDefault().ProductDesign.ProductDesignName,
// ProductCollectionName = table.FirstOrDefault().ProductCollection.ProductCollectionName
// }
// );
var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
var temp = from Pg in db.ProductGroups
join Fp in db.FinishedProduct on Pg.ProductGroupId equals Fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join Pc in db.ProductCategory on FinishedProductTab.ProductCategoryId equals Pc.ProductCategoryId into ProductCategoryTable
from ProductCategoryTab in ProductCategoryTable.DefaultIfEmpty()
join Pd in db.ProductDesigns on FinishedProductTab.ProductDesignId equals Pd.ProductDesignId into ProductDesignTable
from ProductDesignTab in ProductDesignTable.DefaultIfEmpty()
join PCol in db.ProductCollections on FinishedProductTab.ProductCollectionId equals PCol.ProductCollectionId into ProductCollectionTable
from ProductCollectionTab in ProductCollectionTable.DefaultIfEmpty()
where Pg.ProductTypeId == typeid && (FinishedProductTab.IsSample == sample || FinishedProductTab == null) && (FinishedProductTab == null || FinishedProductTab.DivisionId == DivisionId)
group new { Pg, ProductCategoryTab, ProductDesignTab, ProductCollectionTab } by new { Pg.ProductGroupId, Pg.ProductGroupName } into table
orderby table.Key.ProductGroupName
select new CarpetIndexViewModel
{
ProductGroupId = table.Key.ProductGroupId,
ProductGroupName = table.Key.ProductGroupName,
ProductCategoryName = table.Max(m => m.ProductCategoryTab.ProductCategoryName),
ProductDesignName = table.Max(m => m.ProductDesignTab.ProductDesignName),
ProductCollectionName = table.Max(m => m.ProductCollectionTab.ProductCollectionName)
};
return(temp);
}
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);
}