public IEnumerable <PurchaseQuotationLineViewModel> GetPurchaseIndentForFilters(PurchaseQuotationLineFilterViewModel vm)
{
byte?UnitConvForId = db.PurchaseQuotationHeader.Find(vm.PurchaseQuotationHeaderId).UnitConversionForId;
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.PurchaseIndentHeaderId))
{
SaleOrderIdArr = vm.PurchaseIndentHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = 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.ViewPurchaseIndentBalance
join t in db.PurchaseIndentHeader on p.PurchaseIndentHeaderId equals t.PurchaseIndentHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.PurchaseIndentLine on p.PurchaseIndentLineId equals t1.PurchaseIndentLineId 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
from tab3 in table3.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.PurchaseIndentHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseIndentHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new PurchaseQuotationLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Dimension1Id = tab1.Dimension1Id,
Dimension2Id = tab1.Dimension2Id,
Specification = tab1.Specification,
IndentBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
Rate = vm.Rate,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseQuotationHeaderId = vm.PurchaseQuotationHeaderId,
PurchaseIndentLineId = p.PurchaseIndentLineId,
UnitId = tab2.UnitId,
PurchaseIndentDocNo = p.PurchaseIndentNo,
DealUnitId = (tab3 == null ? tab2.UnitId : vm.DealUnitId),
UnitConversionMultiplier = (tab3 == null ? 1 : tab3.ToQty / tab3.FromQty),
UnitConversionException = tab3 == null ? true : false,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = (tab3 == null ? tab2.Unit.DecimalPlaces : Dealunit.DecimalPlaces),
}
);
return(temp);
}
else
{
var temp = (from p in db.ViewPurchaseIndentBalance
join t in db.PurchaseIndentHeader on p.PurchaseIndentHeaderId equals t.PurchaseIndentHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.PurchaseIndentLine on p.PurchaseIndentLineId equals t1.PurchaseIndentLineId into table1
from tab1 in table1.DefaultIfEmpty()
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.PurchaseIndentHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseIndentHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new PurchaseQuotationLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension1Id = tab1.Dimension1Id,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Dimension2Id = tab1.Dimension2Id,
Specification = tab1.Specification,
IndentBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
Rate = vm.Rate,
PurchaseIndentDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseQuotationHeaderId = vm.PurchaseQuotationHeaderId,
PurchaseIndentLineId = p.PurchaseIndentLineId,
UnitId = tab2.UnitId,
DealUnitId = tab2.UnitId,
UnitConversionMultiplier = 1,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab2.Unit.DecimalPlaces,
}
);
return(temp);
}
}
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);
}
}