public IEnumerable <PurchaseOrderCancelLineViewModel> GetPurchaseOrderLineForMultiSelect(PurchaseOrderCancelFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.PurchaseOrderId))
{
SaleOrderIdArr = svm.PurchaseOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
var temp = (from p in db.ViewPurchaseOrderBalance
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join t in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t.PurchaseOrderLineId into table1 from tab1 in table1.DefaultIfEmpty()
join t2 in db.PurchaseOrderHeader on tab1.PurchaseOrderHeaderId equals t2.PurchaseOrderHeaderId
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(svm.SupplierId == 0 ? 1 == 1 : p.SupplierId == svm.SupplierId) &&
(string.IsNullOrEmpty(svm.PurchaseOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && p.SupplierId == svm.SupplierId
orderby t2.DocDate, t2.DocNo, tab1.Sr
select new PurchaseOrderCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = 0,
PurchaseOrderDocNo = p.PurchaseOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseOrderCancelHeaderId = svm.PurchaseOrderCancelHeaderId,
PurchaseOrderLineId = p.PurchaseOrderLineId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab1.DealUnit.DecimalPlaces,
}
);
return(temp);
}
public IEnumerable <SaleOrderQtyAmendmentLineViewModel> GetPurchaseOrderLineForMultiSelect(SaleOrderAmendmentFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.SaleOrderHeaderId))
{
SaleOrderIdArr = svm.SaleOrderHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
var temp = (from p in db.SaleOrderHeader
join t1 in db.SaleOrderLine on p.SaleOrderHeaderId equals t1.SaleOrderHeaderId
join product in db.Product on t1.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(t1.ProductId.ToString())) &&
(svm.BuyerId == 0 ? 1 == 1 : p.SaleToBuyerId == svm.BuyerId) &&
(string.IsNullOrEmpty(svm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
t1.Qty > 0 && p.SaleToBuyerId == svm.BuyerId
select new SaleOrderQtyAmendmentLineViewModel
{
SaleOrderDocNo = p.DocNo,
CurrentQty = t1.Qty,
SaleOrderAmendmentHeaderDocNo = p.DocNo,
ProductName = tab2.ProductName,
ProductId = t1.ProductId,
SaleOrderAmendmentHeaderId = svm.SaleOrderAmendmentHeaderId,
SaleOrderLineId = t1.SaleOrderLineId
}
);
return(temp);
}
public IEnumerable <SaleDeliveryOrderLineViewModel> GetSaleOrderLineForMultiSelect(SaleDeliveryOrderFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.SaleOrderId))
{
SaleOrderIdArr = svm.SaleOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
var temp = (from p in db.ViewSaleOrderBalanceForCancellation
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(svm.BuyerId == 0 ? 1 == 1 : p.BuyerId == svm.BuyerId) &&
(string.IsNullOrEmpty(svm.SaleOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new SaleDeliveryOrderLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
SaleOrderDocNo = p.SaleOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
SaleDeliveryOrderHeaderId = svm.SaleDeliveryOrderHeaderId,
SaleOrderLineId = p.SaleOrderLineId,
UnitDecimalPlaces = tab2.Unit.DecimalPlaces,
});
return(temp);
}
public IEnumerable <SaleDispatchReturnLineViewModel> GetSaleOrderForFilters(SaleDispatchReturnLineFilterViewModel vm)
{
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.SaleOrderHeaderId))
{
SaleOrderIdArr = vm.SaleOrderHeaderId.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" };
}
var temp = (from p in db.ViewSaleDispatchBalance
join l in db.SaleDispatchLine on p.SaleDispatchLineId equals l.SaleDispatchLineId into linetable
from linetab in linetable.DefaultIfEmpty()
join t in db.SaleDispatchHeader on p.SaleDispatchHeaderId equals t.SaleDispatchHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.SaleDispatchLine on p.SaleDispatchLineId equals t1.SaleDispatchLineId into table1
from tab1 in table1.DefaultIfEmpty()
join t2 in db.PackingLine on tab1.PackingLineId equals t2.PackingLineId into InvoiceTab
from PackTab in InvoiceTab.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new SaleDispatchReturnLineViewModel
{
Dimension1Name = PackTab.Dimension1.Dimension1Name,
Dimension2Name = PackTab.Dimension2.Dimension2Name,
GoodsReceiptBalQty = p.BalanceQty,
Qty = p.BalanceQty,
SaleDispatchHeaderDocNo = tab.DocNo,
SaleOrderDocNo = PackTab.SaleOrderLine == null ?"" :PackTab.SaleOrderLine.SaleOrderHeader.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
SaleDispatchReturnHeaderId = vm.SaleDispatchReturnHeaderId,
SaleDispatchLineId = p.SaleDispatchLineId,
UnitId = tab2.UnitId,
//UnitConversionMultiplier = PackTab.UnitConversionMultiplier??0,
DealUnitId = PackTab.DealUnitId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = PackTab.DealUnit.DecimalPlaces,
}
);
return(temp);
}
public IEnumerable <RequisitionCancelLineViewModel> GetRequisitionLineForOrders(RequisitionCancelFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.RequisitionId))
{
SaleOrderIdArr = svm.RequisitionId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] CostCenterIdArr = null;
if (!string.IsNullOrEmpty(svm.CostCenterId))
{
CostCenterIdArr = svm.CostCenterId.Split(",".ToCharArray());
}
else
{
CostCenterIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
var Query = (from p in db.ViewRequisitionBalance
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join t in db.RequisitionLine on p.RequisitionLineId equals t.RequisitionLineId into table1
from tab1 in table1.DefaultIfEmpty()
join t2 in db.RequisitionHeader on tab1.RequisitionHeaderId equals t2.RequisitionHeaderId
where p.BalanceQty > 0 && p.PersonId == svm.PersonId
orderby t2.DocDate, t2.DocNo
select new
{
BalanceQty = p.BalanceQty,
Qty = 0,
RequisitionDocNo = p.RequisitionNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
RequisitionCancelHeaderId = svm.RequisitionCancelHeaderId,
RequisitionLineId = p.RequisitionLineId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
//ForFilters
PersonId = p.PersonId,
RequisitionHeaderId = p.RequisitionHeaderId,
CostCenterId = p.CostCenterId,
ProductGorupId = tab2.ProductGroupId,
Dimension1Id = tab1.Dimension1Id,
Dimension2Id = tab1.Dimension2Id,
}
);
if (!string.IsNullOrEmpty(svm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (svm.PersonId != 0)
{
Query = Query.Where(m => m.PersonId == svm.PersonId);
}
if (!string.IsNullOrEmpty(svm.RequisitionId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.RequisitionHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.CostCenterId))
{
Query = Query.Where(m => CostCenterIdArr.Contains(m.CostCenterId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGorupId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Query = Query.Where(m => Dimension1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Query = Query.Where(m => Dimension2IdArr.Contains(m.Dimension2Id.ToString()));
}
return(Query.Select(m => new RequisitionCancelLineViewModel
{
BalanceQty = m.BalanceQty,
Qty = 0,
RequisitionDocNo = m.RequisitionDocNo,
ProductName = m.ProductName,
ProductId = m.ProductId,
RequisitionCancelHeaderId = m.RequisitionCancelHeaderId,
RequisitionLineId = m.RequisitionLineId,
unitDecimalPlaces = m.unitDecimalPlaces,
UnitId = m.UnitId,
UnitName = m.UnitName,
Dimension1Name = m.Dimension1Name,
Dimension2Name = m.Dimension2Name,
Specification = m.Specification,
}));
}
public IEnumerable <JobReturnLineViewModel> GetJobOrderForFilters(JobReturnLineFilterViewModel vm)
{
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.JobOrderHeaderId))
{
SaleOrderIdArr = vm.JobOrderHeaderId.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" };
}
var temp = (from p in db.ViewJobReceiveBalance
join l in db.JobReceiveLine on p.JobReceiveLineId equals l.JobReceiveLineId into linetable
from linetab in linetable.DefaultIfEmpty()
join t in db.JobReceiveHeader on p.JobReceiveHeaderId equals t.JobReceiveHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.JobReceiveLine on p.JobReceiveLineId equals t1.JobReceiveLineId into table1
from tab1 in table1.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join line in db.JobOrderLine on p.JobOrderLineId equals line.JobOrderLineId into JOlinetable from JOlinetab in JOlinetable.DefaultIfEmpty()
join header in db.JobOrderHeader on JOlinetab.JobOrderHeaderId equals header.JobOrderHeaderId
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.JobOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby header.DocDate, header.DocNo, JOlinetab.Sr
select new JobReturnLineViewModel
{
Dimension1Name = tab1.JobOrderLine.Dimension1.Dimension1Name,
Dimension2Name = tab1.JobOrderLine.Dimension2.Dimension2Name,
Dimension3Name = tab1.JobOrderLine.Dimension3.Dimension3Name,
Dimension4Name = tab1.JobOrderLine.Dimension4.Dimension4Name,
Dimension1Id = tab1.JobOrderLine.Dimension1Id,
Dimension2Id = tab1.JobOrderLine.Dimension2Id,
Dimension3Id = tab1.JobOrderLine.Dimension3Id,
Dimension4Id = tab1.JobOrderLine.Dimension4Id,
Specification = tab1.JobOrderLine.Specification,
GoodsReceiptBalQty = p.BalanceQty,
Qty = p.BalanceQty,
JobReceiveHeaderDocNo = tab.DocNo,
JobOrderDocNo = tab1.JobOrderLine == null ? "" : tab1.JobOrderLine.JobOrderHeader.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
JobReturnHeaderId = vm.JobReturnHeaderId,
JobReceiveLineId = p.JobReceiveLineId,
UnitId = tab2.UnitId,
UnitConversionMultiplier = linetab.JobOrderLine.UnitConversionMultiplier,
DealUnitId = linetab.JobOrderLine.DealUnitId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = linetab.JobOrderLine.DealUnit.DecimalPlaces,
}
);
return(temp);
}
public IEnumerable <JobOrderCancelLineViewModel> GetJobOrderLineForMultiSelect(JobOrderCancelFilterViewModel svm)
{
JobOrderCancelHeader Header = db.JobOrderCancelHeader.Find(svm.JobOrderCancelHeaderId);
var Settings = new JobOrderSettingsService(_unitOfWork).GetJobOrderSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobOrderId))
{
SaleOrderIdArr = svm.JobOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dime1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dime1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dime1IdArr = new string[] { "NA" };
}
string[] Dime2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dime2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dime2IdArr = new string[] { "NA" };
}
string[] Dime3IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Dime3IdArr = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dime3IdArr = new string[] { "NA" };
}
string[] Dime4IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Dime4IdArr = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dime4IdArr = 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" };
}
var temp = (from p in db.ViewJobOrderBalance
join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(svm.JobWorkerId == 0 ? 1 == 1 : p.JobWorkerId == svm.JobWorkerId) &&
(string.IsNullOrEmpty(svm.JobOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dime1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dime2IdArr.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension3Id) ? 3 == 3 : Dime3IdArr.Contains(p.Dimension3Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension4Id) ? 3 == 3 : Dime4IdArr.Contains(p.Dimension4Id.ToString())) &&
(string.IsNullOrEmpty(Settings.filterContraSites) ? p.SiteId == Header.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(Settings.filterContraDivisions) ? p.DivisionId == Header.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString())) &&
p.BalanceQty > 0 && p.JobWorkerId == svm.JobWorkerId
orderby p.OrderDate, p.JobOrderNo, tab.Sr
select new JobOrderCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
JobOrderDocNo = p.JobOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
JobOrderCancelHeaderId = svm.JobOrderCancelHeaderId,
JobOrderLineId = p.JobOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Dimension1Name = p.Dimension1.Dimension1Name,
Dimension2Name = p.Dimension2.Dimension2Name,
Dimension3Name = p.Dimension3.Dimension3Name,
Dimension4Name = p.Dimension4.Dimension4Name,
Specification = tab.Specification,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab.DealUnit.DecimalPlaces,
ProductUidName = (tab.ProductUidHeaderId == null ? tab.ProductUid.ProductUidName : "")
});
return(temp);
}
public IEnumerable <MaterialPlanCancelLineViewModel> GetOrderPlanForFilters(MaterialPlanCancelFilterViewModel vm)
{
var Header = new MaterialPlanCancelHeaderService(_unitOfWork).Find(vm.MaterialPlanCancelHeaderId);
var Settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
contraDocTypes = Settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
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.MaterialPlanHeaderId))
{
SaleOrderIdArr = vm.MaterialPlanHeaderId.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" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Dimension1IdArr = vm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Dimension2IdArr = vm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] Dimension3IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension3Id))
{
Dimension3IdArr = vm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dimension3IdArr = new string[] { "NA" };
}
string[] Dimension4IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension4Id))
{
Dimension4IdArr = vm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dimension4IdArr = new string[] { "NA" };
}
string[] ProcessIdArr = null;
if (!string.IsNullOrEmpty(vm.ProcessId))
{
ProcessIdArr = vm.ProcessId.Split(",".ToCharArray());
}
else
{
ProcessIdArr = new string[] { "NA" };
}
var Query = db.ViewMaterialPlanBalance.AsQueryable()
.Join(db.MaterialPlanLine,
m => m.MaterialPlanLineId,
om => om.MaterialPlanLineId,
(m, p) => new { m, p })
.Join(db.Product, m => m.m.ProductId, t => t.ProductId,
(m, j) => new
{
MaterialPlanLineId = m.m.MaterialPlanLineId,
BalanceQty = m.m.BalanceQty,
MaterialPlanHeaderId = m.m.MaterialPlanHeaderId,
MaterialPlanNo = m.m.MaterialPlanNo,
ProductId = m.m.ProductId,
MaterialPlanDate = m.m.MaterialPlanDate,
DocTypeId = m.m.DocTypeId,
DocTypeName = m.m.DocType.DocumentTypeName,
ProductGroupId = j.ProductGroupId,
Dimension1Name = m.p.Dimension1.Dimension1Name,
Dimenstion2Name = m.p.Dimension2.Dimension2Name,
ProcessName = m.p.Process.ProcessName,
ProductName = j.ProductName,
Specification = m.p.Specification,
unitDecimalPlaces = j.Unit.DecimalPlaces,
UnitId = j.UnitId,
MaterialPlanDocNo = m.m.MaterialPlanNo,
UnitName = j.Unit.UnitName,
BuyerId = m.p.MaterialPlanHeader.BuyerId,
Dimension1Id = m.p.Dimension1Id,
Dimension2Id = m.p.Dimension2Id,
Dimension3Id = m.p.Dimension3Id,
Dimension4Id = m.p.Dimension4Id,
ProcessId = m.p.ProcessId,
});
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(vm.MaterialPlanHeaderId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.MaterialPlanHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Query = Query.Where(m => Dimension1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Query = Query.Where(m => Dimension2IdArr.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension3Id))
{
Query = Query.Where(m => Dimension3IdArr.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension4Id))
{
Query = Query.Where(m => Dimension4IdArr.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProcessId))
{
Query = Query.Where(m => ProcessIdArr.Contains(m.ProcessId.ToString()));
}
var Result = Query.Where(m => m.BuyerId == Header.BuyerId).Select(m => new MaterialPlanCancelLineViewModel
{
BalanceQty = m.BalanceQty,
Dimension1Name = m.Dimension1Name,
Dimension2Name = m.Dimenstion2Name,
MaterialPlanCancelHeaderDocNo = Header.DocNo,
MaterialPlanDocNo = m.MaterialPlanDocNo,
MaterialPlanCancelHeaderId = Header.MaterialPlanCancelHeaderId,
MaterialPlanLineId = m.MaterialPlanLineId,
ProcessName = m.ProcessName,
ProductName = m.ProductName,
Qty = m.BalanceQty,
Specification = m.Specification,
unitDecimalPlaces = m.unitDecimalPlaces,
UnitId = m.UnitId,
UnitName = m.UnitName
});
return(Result);
}
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);
}
}
public IEnumerable <ProdOrderCancelLineViewModel> GetProdOrderLineForMultiSelect(ProdOrderCancelFilterViewModel svm)
{
var Header = new ProdOrderCancelHeaderService(_unitOfWork).Find(svm.ProdOrderCancelHeaderId);
var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.ProdOrderId))
{
SaleOrderIdArr = svm.ProdOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
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" };
}
var temp = (from p in db.ViewProdOrderBalance
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join t3 in db.Units on tab2.UnitId equals t3.UnitId into table3
from tab3 in table3.DefaultIfEmpty()
join t2 in db.ProdOrderLine on p.ProdOrderLineId equals t2.ProdOrderLineId
where p.BalanceQty > 0
select new
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderNo = p.ProdOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
ProdOrderCancelHeaderId = svm.ProdOrderCancelHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
unitDecimalPlaces = (tab3 == null ? 0 : tab3.DecimalPlaces),
Dimension1Name = t2.Dimension1.Dimension1Name,
Dimension2Name = t2.Dimension2.Dimension2Name,
Specification = t2.Specification,
UnitName = tab2.Unit.UnitName,
ProdOrderHeaderId = p.ProdOrderHeaderId,
ProductGroupId = tab2.ProductGroupId,
OrderDocDate = p.IndentDate,
DocTypeId = p.DocTypeId,
SiteId = p.SiteId,
DivisionId = p.DivisionId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
});
if (!string.IsNullOrEmpty(svm.ProductId))
{
temp = temp.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
temp = temp.Where(m => Dimension2IdArr.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
temp = temp.Where(m => Dimension1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProdOrderId))
{
temp = temp.Where(m => SaleOrderIdArr.Contains(m.ProdOrderHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
temp = temp.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
temp = temp.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
temp = temp.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
temp = temp.Where(m => m.SiteId == Header.SiteId);
}
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
temp = temp.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
temp = temp.Where(m => m.DivisionId == Header.DivisionId);
}
if (svm.UpToDate.HasValue)
{
temp = temp.Where(m => m.OrderDocDate <= svm.UpToDate);
}
return(from p in temp
orderby p.OrderDocDate descending, p.ProdOrderNo descending
select new ProdOrderCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderNo = p.ProdOrderNo,
ProductName = p.ProductName,
ProductId = p.ProductId,
ProdOrderCancelHeaderId = svm.ProdOrderCancelHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
unitDecimalPlaces = p.unitDecimalPlaces,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
Specification = p.Specification,
UnitName = p.UnitName,
ProdOrderHeaderId = p.ProdOrderHeaderId,
ProductGroupId = p.ProductGroupId,
OrderDocDate = p.OrderDocDate,
});
}
public IEnumerable <PurchaseInvoiceReturnLineViewModel> GetPurchaseInvoiceForFilters(PurchaseInvoiceReturnLineFilterViewModel vm)
{
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.PurchaseInvoiceHeaderId))
{
SaleOrderIdArr = vm.PurchaseInvoiceHeaderId.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" };
}
//ToChange View to get purchaseorders instead of goodsreceipts
var temp = (from p in db.ViewPurchaseInvoiceBalance
join l in db.PurchaseInvoiceLine on p.PurchaseInvoiceLineId equals l.PurchaseInvoiceLineId into linetable
from linetab in linetable.DefaultIfEmpty()
join h in db.PurchaseInvoiceHeader on linetab.PurchaseInvoiceHeaderId equals h.PurchaseInvoiceHeaderId
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.PurchaseGoodsReceiptLine on p.PurchaseGoodsReceiptLineId equals t1.PurchaseGoodsReceiptLineId 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.PurchaseInvoiceHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseInvoiceHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby h.DocDate, h.DocNo, linetab.Sr
select new PurchaseInvoiceReturnLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
InvoiceBalQty = p.BalanceQty,
Qty = p.BalanceQty,
PurchaseInvoiceHeaderDocNo = p.PurchaseInvoiceNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseInvoiceReturnHeaderId = vm.PurchaseInvoiceReturnHeaderId,
PurchaseInvoiceLineId = p.PurchaseInvoiceLineId,
UnitId = tab2.UnitId,
UnitConversionMultiplier = linetab.UnitConversionMultiplier,
DealUnitId = linetab.DealUnitId,
Rate = linetab.Rate,
RateAfterDiscount = (linetab.Amount / linetab.DealQty),
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = linetab.DealUnit.DecimalPlaces,
DiscountPer = linetab.DiscountPer,
ProductUidName = tab1.ProductUid.ProductUidName,
}
);
return(temp);
}
public IEnumerable <PurchaseGoodsReturnLineViewModel> GetPurchaseOrderForFilters(PurchaseGoodsReturnLineFilterViewModel vm)
{
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.PurchaseOrderHeaderId))
{
SaleOrderIdArr = vm.PurchaseOrderHeaderId.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" };
}
var temp = (from p in db.ViewPurchaseGoodsReceiptBalance
join t1 in db.PurchaseGoodsReceiptLine on p.PurchaseGoodsReceiptLineId equals t1.PurchaseGoodsReceiptLineId into table1
from tab1 in table1.DefaultIfEmpty()
join t in db.PurchaseGoodsReceiptHeader on p.PurchaseGoodsReceiptHeaderId equals t.PurchaseGoodsReceiptHeaderId into table
from tab in table.DefaultIfEmpty()
join t2 in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t2.PurchaseOrderLineId into tabl2 from POLinetab in tabl2.DefaultIfEmpty()
join t3 in db.PurchaseOrderHeader on POLinetab.PurchaseOrderHeaderId equals t3.PurchaseOrderHeaderId
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.PurchaseOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby t3.DocDate, t3.DocNo, POLinetab.Sr
select new PurchaseGoodsReturnLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
GoodsReceiptBalQty = p.BalanceQty,
Qty = p.BalanceQty,
PurchaseGoodsReceiptHeaderDocNo = tab.DocNo,
PurchaseOrderDocNo = tab1.PurchaseOrderLine == null ?"" :tab1.PurchaseOrderLine.PurchaseOrderHeader.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseGoodsReturnHeaderId = vm.PurchaseGoodsReturnHeaderId,
PurchaseGoodsReceiptLineId = p.PurchaseGoodsReceiptLineId,
UnitId = tab2.UnitId,
UnitConversionMultiplier = tab1.UnitConversionMultiplier,
DealUnitId = tab1.DealUnitId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab1.DealUnit.DecimalPlaces,
}
);
return(temp);
}
public IEnumerable <JobOrderInspectionRequestLineViewModel> GetJobOrderLineForMultiSelect(JobOrderInspectionRequestFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobOrderId))
{
SaleOrderIdArr = svm.JobOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dime1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dime1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dime1IdArr = new string[] { "NA" };
}
string[] Dime2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dime2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dime2IdArr = new string[] { "NA" };
}
string[] Dime3IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Dime3IdArr = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dime3IdArr = new string[] { "NA" };
}
string[] Dime4IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Dime4IdArr = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dime4IdArr = new string[] { "NA" };
}
var Header = db.JobOrderInspectionRequestHeader.Find(svm.JobOrderInspectionRequestHeaderId);
var settings = new JobOrderInspectionRequestSettingsService(db).GetJobOrderInspectionRequestSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.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" };
}
var Query = (from p in db.ViewJobOrderBalanceForInspectionRequest
join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join jo in db.JobOrderHeader on p.JobOrderHeaderId equals jo.JobOrderHeaderId
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()
join uni in db.Units on tab2.UnitId equals uni.UnitId into unittable
from unittab in unittable.DefaultIfEmpty()
join dealunit in db.Units on tab.DealUnitId equals dealunit.UnitId into dealunittable
from dealunittab in dealunittable.DefaultIfEmpty()
where p.BalanceQty > 0 && p.JobWorkerId == svm.JobWorkerId && jo.ProcessId == Header.ProcessId
orderby p.OrderDate, p.JobOrderNo, tab.Sr
select new
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
JobOrderDocNo = p.JobOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
JobOrderInspectionRequestHeaderId = svm.JobOrderInspectionRequestHeaderId,
JobOrderLineId = p.JobOrderLineId,
Dimension1Id = Dimension1Tab.Dimension1Id,
Dimension2Id = Dimension2Tab.Dimension2Id,
Dimension3Id = Dimension3Tab.Dimension3Id,
Dimension4Id = Dimension4Tab.Dimension4Id,
Dimension1Name = Dimension1Tab.Dimension1Name,
Dimension2Name = Dimension2Tab.Dimension2Name,
Dimension3Name = Dimension3Tab.Dimension3Name,
Dimension4Name = Dimension4Tab.Dimension4Name,
Specification = tab.Specification,
UnitId = unittab.UnitId,
UnitName = unittab.UnitName,
unitDecimalPlaces = unittab.DecimalPlaces,
DealunitDecimalPlaces = dealunittab.DecimalPlaces,
ProductUidName = (tab.ProductUidHeaderId == null ? tab.ProductUid.ProductUidName : ""),
ProductUidId = tab.ProductUidId,
JobOrderHeaderId = p.JobOrderHeaderId,
ProdOrderLineId = tab.ProdOrderLineId,
ProductGroupId = tab2.ProductGroupId,
SiteId = jo.SiteId,
DivisionId = jo.DivisionId,
DocTypeId = jo.DocTypeId,
}
);
if (!string.IsNullOrEmpty(svm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(svm.JobOrderId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobOrderHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Query = Query.Where(m => Dime1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Query = Query.Where(m => Dime2IdArr.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Query = Query.Where(m => Dime1IdArr.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Query = Query.Where(m => Dime2IdArr.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
Query = Query.Where(m => m.SiteId == Header.SiteId);
}
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
Query = Query.Where(m => m.DivisionId == Header.DivisionId);
}
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
return((from p in Query
select new JobOrderInspectionRequestLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.Qty,
JobOrderDocNo = p.JobOrderDocNo,
ProductName = p.ProductName,
ProductId = p.ProductId,
JobOrderInspectionRequestHeaderId = p.JobOrderInspectionRequestHeaderId,
JobOrderLineId = p.JobOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
Dimension3Name = p.Dimension3Name,
Dimension4Name = p.Dimension4Name,
Specification = p.Specification,
UnitId = p.UnitId,
UnitName = p.UnitName,
unitDecimalPlaces = p.unitDecimalPlaces,
DealunitDecimalPlaces = p.DealunitDecimalPlaces,
ProductUidName = p.ProductUidName,
ProductUidId = p.ProductUidId,
JobOrderHeaderId = p.JobOrderHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
}).ToList());
}
public IEnumerable <SaleDispatchLineViewModel> GetSaleOrdersForFilters(SaleDispatchFilterViewModel vm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(vm.ProductId))
{
ProductIdArr = vm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Dimension1IdArr = vm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Dimension2IdArr = vm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(vm.SaleOrderHeaderId))
{
SaleOrderIdArr = vm.SaleOrderHeaderId.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" };
}
var temp = (from p in db.ViewSaleOrderBalance
join t in db.SaleOrderHeader on p.SaleOrderHeaderId equals t.SaleOrderHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.SaleOrderLine on p.SaleOrderLineId equals t1.SaleOrderLineId into table1
from tab1 in table1.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(vm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) &&
p.BalanceQty > 0
orderby p.SaleOrderLineId
select new SaleDispatchLineViewModel
{
//ProductUidIdName = tab1.ProductUid != null ? tab1.ProductUid.ProductUidName : "",
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
SaleOrderHeaderDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
SaleDispatchHeaderId = vm.SaleDispatchHeaderId,
SaleOrderLineId = p.SaleOrderLineId,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
DealUnitId = tab1.DealUnitId,
DealUnitName = tab1.DealUnit.UnitName,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealUnitDecimalPlaces = tab1.DealUnit.DecimalPlaces,
DealQty = (!tab1.UnitConversionMultiplier.HasValue || tab1.UnitConversionMultiplier <= 0) ? p.BalanceQty : p.BalanceQty * tab1.UnitConversionMultiplier.Value,
UnitConversionMultiplier = tab1.UnitConversionMultiplier,
}
);
return(temp);
}
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetSaleOrdersForFilters(MaterialPlanForLineFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.SaleOrderHeaderId))
{
SaleOrderIdArr = svm.SaleOrderHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] BuyerIdArr = null;
if (!string.IsNullOrEmpty(svm.BuyerId))
{
BuyerIdArr = svm.BuyerId.Split(",".ToCharArray());
}
else
{
BuyerIdArr = new string[] { "NA" };
}
var Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId);
var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
string ProcName = settings.PendingProdOrderList;
if (string.IsNullOrEmpty(ProcName))
{
throw new Exception("Pending ProdOrders Not Configured");
}
SqlParameter SqlParameterDocType = new SqlParameter("@PlanningDocumentType", svm.DocTypeId);
SqlParameter SqlParameterSite = new SqlParameter("@Site", Header.SiteId);
SqlParameter SqlParameterDivision = new SqlParameter("@Division", Header.DivisionId);
SqlParameter SqlParameterBuyer = new SqlParameter("@BuyerId", (Header.BuyerId.HasValue ? Header.BuyerId : (object)DBNull.Value));
IEnumerable <PendingSaleOrderFromProc> PendingSaleOrders = db.Database.SqlQuery <PendingSaleOrderFromProc>(" " + ProcName + " @PlanningDocumentType, @Site, @Division, @BuyerId", SqlParameterDocType, SqlParameterSite, SqlParameterDivision, SqlParameterBuyer).ToList();
//var ProductIds=(PendingSaleOrders.Select(m=>m.ProductId)).ToArray();
//var ProductGroupIds = (from p in db.Product.Where(m => ProductIdArr.Contains(m.ProductId.ToString()))
// select p.ProductGroupId).ToArray();
var resu = (from p in PendingSaleOrders
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(svm.BuyerId) ? 1 == 1 : BuyerIdArr.Contains(p.BuyerId.ToString())) &&
p.BalanceQty > 0
orderby p.Sr
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
SaleOrderDocNo = p.SaleOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
Dimension1Id = p.Dimension1Id,
Dimension1Name = p.Dimension1Name,
Dimension2Id = p.Dimension2Id,
Dimension2Name = p.Dimension2Name,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
SaleOrderLineId = p.SaleOrderLineId,
Specification = p.Specification,
UnitName = p.UnitName,
BomDetailExists = p.BomDetailExists,
}).ToList();
return(resu);
}
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetProdOrdersForFilters(MaterialPlanLineForProductionFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.ProdOrderHeaderId))
{
SaleOrderIdArr = svm.ProdOrderHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
var Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId);
string ProcName = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId).PendingProdOrderList;
SqlParameter SqlParameterProductId = new SqlParameter("@MaterialPlanHeaderId", svm.MaterialPlanHeaderId);
IEnumerable <ProdOrderBalanceViewModel> StockAvailableForPacking = db.Database.SqlQuery <ProdOrderBalanceViewModel>("" + ProcName + " @MaterialPlanHeaderId", SqlParameterProductId).ToList();
var ProductIds = StockAvailableForPacking.Select(m => m.ProductId).ToArray();
var ProdOrderLineIds = StockAvailableForPacking.Select(m => m.ProdOrderLineId).ToArray();
var temp1 = from p in StockAvailableForPacking
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1)
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderDocNo = p.ProdOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
UnitName = p.UnitName,
ProcessId = p.ProcessId,
ProcessName = p.ProcessName,
BomDetailExists = p.IsBomExist,
};
var temp2 = (from p in StockAvailableForPacking
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1)
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderDocNo = p.ProdOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
UnitName = p.UnitName,
ProcessId = p.ProcessId,
ProcessName = p.ProcessName,
BomDetailExists = p.IsBomExist,
}).ToList();
var DocTypeIds = StockAvailableForPacking.Select(m => m.DocTypeId).ToArray();
return(temp2);
}
public IEnumerable <SaleOrderCancelLineViewModel> GetSaleOrderLineForMultiSelect(SaleOrderCancelFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.SaleOrderId))
{
SaleOrderIdArr = svm.SaleOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
var temp = (from p in db.ViewSaleOrderBalanceForCancellation
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.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()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(svm.BuyerId == 0? 1 == 1 : p.BuyerId == svm.BuyerId) &&
(string.IsNullOrEmpty(svm.SaleOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) &&
p.BalanceQty > 0
select new SaleOrderCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
DocNo = p.SaleOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
Dimension1Name = Dimension1Tab.Dimension1Name,
Dimension1Id = p.Dimension1Id,
Dimension2Name = Dimension2Tab.Dimension2Name,
Dimension2Id = p.Dimension2Id,
SaleOrderCancelHeaderId = svm.SaleOrderCancelHeaderId,
SaleOrderLineId = p.SaleOrderLineId
});
return(temp);
}
public IEnumerable <PurchaseOrderRateAmendmentLineViewModel> GetPurchaseOrderLineForMultiSelect(PurchaseOrderAmendmentFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.PurchaseOrderId))
{
SaleOrderIdArr = svm.PurchaseOrderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dim1Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim1Id = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
Dim1Id = new string[] { "NA" };
}
string[] Dim2Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim2Id = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
Dim2Id = new string[] { "NA" };
}
var temp = (from p in db.ViewPurchaseOrderBalanceForInvoice
join t3 in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t3.PurchaseOrderLineId into table3
from tab3 in table3.DefaultIfEmpty()
join t2 in db.PurchaseOrderHeader on p.PurchaseOrderHeaderId equals t2.PurchaseOrderHeaderId
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join AL in db.PurchaseOrderRateAmendmentLine on p.PurchaseOrderLineId equals AL.PurchaseOrderLineId into ALTable
from AlTab in ALTable.DefaultIfEmpty()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.PurchaseOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dim1Id.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dim2Id.Contains(p.Dimension2Id.ToString())) &&
p.BalanceQty > 0 && ((svm.SupplierId.HasValue && svm.SupplierId.Value > 0) ? p.SupplierId == svm.SupplierId : 1 == 1) &&
(svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1)
orderby t2.DocDate, t2.DocNo, tab3.Sr
select new PurchaseOrderRateAmendmentLineViewModel
{
Dimension1Name = tab3.Dimension1.Dimension1Name,
Dimension2Name = tab3.Dimension2.Dimension2Name,
UnitName = tab2.Unit.UnitName,
DealUnitName = tab3.DealUnit.UnitName,
DealQty = p.BalanceQty * tab3.UnitConversionMultiplier,
UnitConversionMultiplier = tab3.UnitConversionMultiplier,
PurchaseOrderRate = p.Rate,
AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate),
Qty = p.BalanceQty,
PurchaseOrderDocNo = p.PurchaseOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseOrderAmendmentHeaderId = svm.PurchaseOrderAmendmentHeaderId,
PurchaseOrderLineId = p.PurchaseOrderLineId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
dealUnitDecimalPlaces = tab3.DealUnit.DecimalPlaces,
AAmended = (AlTab == null ? false : true)
}
);
return(temp);
}
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 <SaleInvoiceReturnLineViewModel> GetSaleReceiptForFilters(SaleInvoiceReturnLineFilterViewModel vm)
{
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.SaleDispatchHeaderId))
{
SaleOrderIdArr = vm.SaleDispatchHeaderId.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" };
}
var temp = (from p in db.ViewSaleInvoiceBalance
join l in db.SaleInvoiceLine on p.SaleInvoiceLineId equals l.SaleInvoiceLineId into linetable
from linetab in linetable.DefaultIfEmpty()
join t in db.SaleInvoiceHeader on p.SaleInvoiceHeaderId equals t.SaleInvoiceHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.SaleDispatchLine on p.SaleDispatchLineId equals t1.SaleDispatchLineId into table1
from tab1 in table1.DefaultIfEmpty()
join packtab in db.PackingLine on tab1.PackingLineId equals packtab.PackingLineId
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.SaleDispatchHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleDispatchHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
select new SaleInvoiceReturnLineViewModel
{
Dimension1Name = packtab.Dimension1.Dimension1Name,
Dimension2Name = packtab.Dimension2.Dimension2Name,
Specification = packtab.Specification,
InvoiceBalQty = p.BalanceQty,
Qty = p.BalanceQty,
SaleInvoiceHeaderDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
SaleInvoiceReturnHeaderId = vm.SaleInvoiceReturnHeaderId,
SaleInvoiceLineId = p.SaleInvoiceLineId,
UnitId = tab2.UnitId,
UnitConversionMultiplier = linetab.UnitConversionMultiplier ?? 0,
DealUnitId = linetab.DealUnitId,
Rate = linetab.Rate,
RateAfterDiscount = packtab.SaleOrderLine == null ? 0 : (packtab.SaleOrderLine.Amount / packtab.SaleOrderLine.DealQty),
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = linetab.DealUnit.DecimalPlaces,
DiscountPer = linetab.DiscountPer,
ProductUidName = packtab.ProductUid.ProductUidName,
}
);
return(temp);
}
public IEnumerable <PurchaseGoodsReceiptLineViewModel> GetPurchaseOrdersForFilters(PurchaseGoodsReceiptLineFilterViewModel vm)
{
var GoodsReceipt = new PurchaseGoodsReceiptHeaderService(_unitOfWork).Find(vm.PurchaseGoodsReceiptHeaderId);
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.PurchaseOrderHeaderId))
{
SaleOrderIdArr = vm.PurchaseOrderHeaderId.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" };
}
var temp = (from p in db.ViewPurchaseOrderBalance
join t in db.PurchaseOrderHeader on p.PurchaseOrderHeaderId equals t.PurchaseOrderHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t1.PurchaseOrderLineId 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.PurchaseOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && p.SupplierId == vm.SupplierId &&
p.SiteId == GoodsReceipt.SiteId && p.DivisionId == GoodsReceipt.DivisionId
orderby tab.DocDate, tab.DocNo, tab1.Sr
select new PurchaseGoodsReceiptLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
OrderBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
DocQty = p.BalanceQty,
PurchaseGoodsReceiptHeaderDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseGoodsReceiptHeaderId = vm.PurchaseGoodsReceiptHeaderId,
PurchaseOrderLineId = p.PurchaseOrderLineId,
UnitId = tab2.UnitId,
PurchaseOrderDocNo = p.PurchaseOrderNo,
DealUnitId = tab1.DealUnitId,
OrderDealQty = tab1.DealQty,
OrderQty = tab1.Qty,
DealunitDecimalPlaces = tab1.DealUnit.DecimalPlaces,
UnitConversionMultiplier = tab1.UnitConversionMultiplier,
DealQty = (tab1.UnitConversionMultiplier == null || tab1.UnitConversionMultiplier == 0) ? p.BalanceQty : p.BalanceQty * tab1.UnitConversionMultiplier,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
}
);
return(temp);
}
public IEnumerable <JobOrderInspectionRequestCancelLineViewModel> GetJobOrderInspectionRequestLineForMultiSelect(JobOrderInspectionRequestCancelFilterViewModel svm)
{
string[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobOrderInspectionRequestId))
{
SaleOrderIdArr = svm.JobOrderInspectionRequestId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dime1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dime1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dime1IdArr = new string[] { "NA" };
}
string[] Dime2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dime2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dime2IdArr = new string[] { "NA" };
}
string[] Dime3IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Dime3IdArr = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dime3IdArr = new string[] { "NA" };
}
string[] Dime4IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Dime4IdArr = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dime4IdArr = new string[] { "NA" };
}
var InsReqCanHeader = db.JobOrderInspectionRequestCancelHeader.Find(svm.JobOrderInspectionRequestCancelHeaderId);
var settings = new JobOrderInspectionRequestSettingsService(db).GetJobOrderInspectionRequestSettingsForDocument(InsReqCanHeader.DocTypeId, InsReqCanHeader.DivisionId, InsReqCanHeader.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" };
}
var Query = (from p in db.ViewJobOrderInspectionRequestBalance
join t in db.JobOrderInspectionRequestLine on p.JobOrderInspectionRequestLineId equals t.JobOrderInspectionRequestLineId into table
from tab in table.DefaultIfEmpty()
join jir in db.JobOrderInspectionRequestHeader on p.JobOrderInspectionRequestHeaderId equals jir.JobOrderInspectionRequestHeaderId
join Jol in db.JobOrderLine on tab.JobOrderLineId equals Jol.JobOrderLineId
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where p.BalanceQty > 0 && p.JobWorkerId == svm.JobWorkerId && jir.ProcessId == InsReqCanHeader.ProcessId
orderby p.RequestDate, p.JobOrderInspectionRequestNo, tab.Sr
select new
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
JobOrderInspectionRequestDocNo = p.JobOrderInspectionRequestNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
JobOrderInspectionRequestCancelHeaderId = svm.JobOrderInspectionRequestCancelHeaderId,
JobOrderInspectionRequestLineId = p.JobOrderInspectionRequestLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Dimension1Name = p.Dimension1.Dimension1Name,
Dimension2Name = p.Dimension2.Dimension2Name,
Dimension3Name = p.Dimension3.Dimension3Name,
Dimension4Name = p.Dimension4.Dimension4Name,
Specification = Jol.Specification,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = Jol.DealUnit.DecimalPlaces,
ProductUidName = (Jol.ProductUidHeaderId == null ? tab.ProductUid.ProductUidName : ""),
ProductUidId = tab.ProductUidId,
JobOrderInspectionRequestHeaderId = p.JobOrderInspectionRequestHeaderId,
ProdOrderLineId = Jol.ProdOrderLineId,
ProductGroupId = tab2.ProductGroupId,
SiteId = p.SiteId,
DivisionId = p.DivisionId,
DocTypeId = jir.DocTypeId,
});
if (!string.IsNullOrEmpty(svm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(svm.JobOrderInspectionRequestId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobOrderInspectionRequestHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Query = Query.Where(m => Dime1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Query = Query.Where(m => Dime2IdArr.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Query = Query.Where(m => Dime3IdArr.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Query = Query.Where(m => Dime4IdArr.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
Query = Query.Where(m => m.SiteId == InsReqCanHeader.SiteId);
}
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
Query = Query.Where(m => m.DivisionId == InsReqCanHeader.DivisionId);
}
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
return(from p in Query
select new JobOrderInspectionRequestCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
JobOrderInspectionRequestDocNo = p.JobOrderInspectionRequestDocNo,
ProductName = p.ProductName,
ProductId = p.ProductId,
JobOrderInspectionRequestCancelHeaderId = svm.JobOrderInspectionRequestCancelHeaderId,
JobOrderInspectionRequestLineId = p.JobOrderInspectionRequestLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
Dimension3Name = p.Dimension3Name,
Dimension4Name = p.Dimension4Name,
Specification = p.Specification,
UnitId = p.UnitId,
UnitName = p.UnitName,
unitDecimalPlaces = p.unitDecimalPlaces,
DealunitDecimalPlaces = p.DealunitDecimalPlaces,
ProductUidName = (p.ProductUidName),
ProductUidId = p.ProductUidId,
JobOrderInspectionRequestHeaderId = p.JobOrderInspectionRequestHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
});
}
public IEnumerable <ProdOrderLineViewModel> GetProdOrderForFilters(ProdOrderLineFilterViewModel vm)
{
var ProdOrderHeader = new ProdOrderHeaderService(_unitOfWork).Find(vm.ProdOrderHeaderId);
var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(ProdOrderHeader.DocTypeId, ProdOrderHeader.DivisionId, ProdOrderHeader.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[] filterProducts = null;
if (!string.IsNullOrEmpty(settings.filterProducts))
{
filterProducts = settings.filterProducts.Split(",".ToCharArray());
}
else
{
filterProducts = new string[] { "NA" };
}
string[] filterProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
filterProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
filterProductTypes = new string[] { "NA" };
}
string[] filterProductGroups = null;
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
filterProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
}
else
{
filterProductGroups = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
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.MaterialPlanHeaderId))
{
SaleOrderIdArr = vm.MaterialPlanHeaderId.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" };
}
var temp = (from p in db.ViewMaterialPlanBalance
join t in db.MaterialPlanHeader on p.MaterialPlanHeaderId equals t.MaterialPlanHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.MaterialPlanLine on p.MaterialPlanLineId equals t1.MaterialPlanLineId 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.MaterialPlanHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.MaterialPlanHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : filterProducts.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : filterProductGroups.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : filterProductTypes.Contains(tab2.ProductGroup.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? tab.SiteId == CurrentSiteId : contraSites.Contains(tab.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? tab.DivisionId == CurrentDivisionId : contraDivisions.Contains(tab.DivisionId.ToString()))
select new ProdOrderLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Dimension3Name = tab1.Dimension3.Dimension3Name,
Dimension4Name = tab1.Dimension4.Dimension4Name,
Specification = tab1.Specification,
Dimension1Id = tab1.Dimension1Id,
Dimension2Id = tab1.Dimension2Id,
Dimension3Id = tab1.Dimension3Id,
Dimension4Id = tab1.Dimension4Id,
PlanBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
MaterialPlanHeaderDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
ProdOrderHeaderId = vm.ProdOrderHeaderId,
MaterialPlanLineId = p.MaterialPlanLineId,
//UnitId = tab2.UnitId,
}
);
return(temp);
}
public IEnumerable <JobInvoiceRateAmendmentLineViewModel> GetJobInvoiceLineForMultiSelect(JobInvoiceAmendmentFilterViewModel svm)
{
var Header = db.JobInvoiceAmendmentHeader.Find(svm.JobInvoiceAmendmentHeaderId);
var Settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.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[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobInvoiceId))
{
SaleOrderIdArr = svm.JobInvoiceId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dim1Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim1Id = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dim1Id = new string[] { "NA" };
}
string[] Dim2Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim2Id = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dim2Id = new string[] { "NA" };
}
string[] Dim3Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim3Id = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dim3Id = new string[] { "NA" };
}
string[] Dim4Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim4Id = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dim4Id = new string[] { "NA" };
}
var Query = (from VJ in db.ViewJobInvoiceBalanceForRateAmendment
join p in db.JobInvoiceLine on VJ.JobInvoiceLineId equals p.JobInvoiceLineId
join t2 in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals t2.JobInvoiceHeaderId
join JR in db.JobReceiveLine on p.JobReceiveLineId equals JR.JobReceiveLineId
join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
join product in db.Product on JO.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join AL in db.JobInvoiceRateAmendmentLine on p.JobInvoiceLineId equals AL.JobInvoiceLineId into ALTable
from AlTab in ALTable.DefaultIfEmpty()
where
//(string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(JO.ProductId.ToString()))
//&& (string.IsNullOrEmpty(svm.JobInvoiceId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobInvoiceHeaderId.ToString()))
//&& (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString()))
//&& (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dim1Id.Contains(JO.Dimension1Id.ToString()))
//&& (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dim2Id.Contains(JO.Dimension2Id.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t2.DocTypeId.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraDivisions) ? t2.DivisionId == Header.DivisionId : contraDivisions.Contains(t2.DivisionId.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraSites) ? t2.SiteId == Header.SiteId : contraSites.Contains(t2.SiteId.ToString()))
//&& ((svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0) ? p.JobWorkerId == svm.JobWorkerId : 1 == 1)
//&& ((svm.OldRate > 0) ? p.Rate == svm.OldRate : 1 == 1)
//&& (svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1)
//&&
p.Qty > 0 && t2.ProcessId == Header.ProcessId
orderby t2.DocDate, t2.DocNo, p.Sr
select new
{
Dimension1Name = JO.Dimension1.Dimension1Name,
Dimension2Name = JO.Dimension2.Dimension2Name,
Dimension3Name = JO.Dimension3.Dimension3Name,
Dimension4Name = JO.Dimension4.Dimension4Name,
UnitName = tab2.Unit.UnitName,
DealUnitName = p.DealUnit.UnitName,
DealQty = (p.Qty) * p.UnitConversionMultiplier,
UnitConversionMultiplier = p.UnitConversionMultiplier,
JobInvoiceRate = p.Rate,
AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate),
Qty = (p.Qty),
JobInvoiceDocNo = t2.DocNo,
ProductName = tab2.ProductName,
ProductId = tab2.ProductId,
JobInvoiceAmendmentHeaderId = svm.JobInvoiceAmendmentHeaderId,
JobInvoiceLineId = p.JobInvoiceLineId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = p.DealUnit.DecimalPlaces,
JobWorkerId = p.JobWorkerId,
AAmended = (AlTab == null ? false : true),
JobInvoiceHeaderId = p.JobInvoiceHeaderId,
ProductGroupId = tab2.ProductGroupId,
Dimension1Id = JO.Dimension1Id,
Dimension2Id = JO.Dimension2Id,
Dimension3Id = JO.Dimension3Id,
Dimension4Id = JO.Dimension4Id,
DocTypeId = t2.DocTypeId,
DivisionId = t2.DivisionId,
SiteId = t2.SiteId,
DocDate = t2.DocDate,
});
if (!string.IsNullOrEmpty(svm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(svm.JobInvoiceId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobInvoiceHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Query = Query.Where(m => Dim1Id.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Query = Query.Where(m => Dim2Id.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Query = Query.Where(m => Dim3Id.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Query = Query.Where(m => Dim4Id.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(Settings.filterContraSites))
{
Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
Query = Query.Where(m => m.SiteId == Header.SiteId);
}
if (!string.IsNullOrEmpty(Settings.filterContraDivisions))
{
Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
Query = Query.Where(m => m.DivisionId == Header.DivisionId);
}
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (svm.OldRate > 0)
{
Query = Query.Where(m => m.JobInvoiceRate == svm.OldRate);
}
if (svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0)
{
Query = Query.Where(m => m.JobWorkerId == svm.JobWorkerId);
}
if (svm.UpToDate.HasValue)
{
Query = Query.Where(m => m.DocDate <= svm.UpToDate);
}
return(from p in Query
select new JobInvoiceRateAmendmentLineViewModel
{
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
Dimension3Name = p.Dimension3Name,
Dimension4Name = p.Dimension4Name,
UnitName = p.UnitName,
DealUnitName = p.UnitName,
DealQty = p.DealQty,
UnitConversionMultiplier = p.UnitConversionMultiplier,
JobInvoiceRate = p.JobInvoiceRate,
AmendedRate = p.AmendedRate,
Qty = (p.Qty),
JobInvoiceDocNo = p.JobInvoiceDocNo,
ProductName = p.ProductName,
ProductId = p.ProductId,
JobInvoiceAmendmentHeaderId = p.JobInvoiceAmendmentHeaderId,
JobInvoiceLineId = p.JobInvoiceLineId,
unitDecimalPlaces = p.unitDecimalPlaces,
DealunitDecimalPlaces = p.DealunitDecimalPlaces,
JobWorkerId = p.JobWorkerId,
AAmended = p.AAmended,
});
}
public IEnumerable <PurchaseInvoiceLineViewModel> GetPurchaseReceiptForFilters(PurchaseInvoiceLineFilterViewModel vm)
{
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.PurchaseGoodsReceiptHeaderId))
{
SaleOrderIdArr = vm.PurchaseGoodsReceiptHeaderId.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" };
}
var temp = (from p in db.ViewPurchaseGoodsReceiptBalance
join t in db.PurchaseGoodsReceiptHeader on p.PurchaseGoodsReceiptHeaderId equals t.PurchaseGoodsReceiptHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.PurchaseGoodsReceiptLine on p.PurchaseGoodsReceiptLineId equals t1.PurchaseGoodsReceiptLineId into table1
from tab1 in table1.DefaultIfEmpty()
join Pol in db.PurchaseOrderLine on tab1.PurchaseOrderLineId equals Pol.PurchaseOrderLineId into PurchaseOrderLineTable
from PurchaseOrderLineTab in PurchaseOrderLineTable.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.PurchaseGoodsReceiptHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseGoodsReceiptHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby tab.DocDate, tab.DocNo, tab1.Sr
select new PurchaseInvoiceLineViewModel
{
ProductUidName = tab1.ProductUid != null?tab1.ProductUid.ProductUidName:"",
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
ReceiptBalQty = p.BalanceQty,
Qty = p.BalanceQty,
DocQty = p.BalanceDocQty,
ReceiptBalDocQty = p.BalanceDocQty,
PurchaseGoodsReceiptHeaderDocNo = tab.DocNo,
ShortQty = p.BalanceDocQty - p.BalanceQty,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseInvoiceHeaderId = vm.PurchaseInvoiceHeaderId,
PurchaseGoodsReceiptLineId = p.PurchaseGoodsReceiptLineId,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
DealUnitId = tab1.DealUnitId,
DealUnitName = tab1.DealUnit.UnitName,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab1.DealUnit.DecimalPlaces,
ReceiptBalDealQty = p.BalanceDealQty,
DealQty = (tab1.UnitConversionMultiplier == null || tab1.UnitConversionMultiplier == 0)?p.BalanceQty:p.BalanceQty * tab1.UnitConversionMultiplier,
UnitConversionMultiplier = tab1.UnitConversionMultiplier,
Rate = tab1.PurchaseOrderLine == null ? 0 : tab1.PurchaseOrderLine.Rate ?? 0,
RateAfterDiscount = tab1.PurchaseOrderLine == null ? 0 : (tab1.PurchaseOrderLine.Amount / tab1.PurchaseOrderLine.DealQty) ?? 0,
DiscountPer = PurchaseOrderLineTab.DiscountPer
}
);
return(temp);
}
public IEnumerable <JobInvoiceReturnLineViewModel> GetJobReceiveForFilters(JobInvoiceReturnLineFilterViewModel vm)
{
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.JobReceiveHeaderId))
{
SaleOrderIdArr = vm.JobReceiveHeaderId.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" };
}
var temp = (from VJIB in db.ViewJobInvoiceBalance
join L in db.JobInvoiceLine on VJIB.JobInvoiceLineId equals L.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
join H in db.JobInvoiceHeader on VJIB.JobInvoiceHeaderId equals H.JobInvoiceHeaderId into JobInvoiceHeaderTable
from JobInvoiceHeaderTab in JobInvoiceHeaderTable.DefaultIfEmpty()
join Jrl in db.JobReceiveLine on VJIB.JobReceiveLineId equals Jrl.JobReceiveLineId into JobReceiveLineTable
from JobReceiveLineTab in JobReceiveLineTable.DefaultIfEmpty()
join Jrh in db.JobReceiveHeader on JobReceiveLineTab.JobReceiveHeaderId equals Jrh.JobReceiveHeaderId
join P in db.Product on VJIB.ProductId equals P.ProductId into table2
from ProductTab in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(VJIB.ProductId.ToString())) &&
(string.IsNullOrEmpty(vm.JobReceiveHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(VJIB.JobReceiveHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(ProductTab.ProductGroupId.ToString())) &&
VJIB.BalanceQty > 0
orderby Jrh.DocDate, Jrh.DocNo, JobReceiveLineTab.Sr
select new JobInvoiceReturnLineViewModel
{
Dimension1Name = JobReceiveLineTab.Dimension1.Dimension1Name,
Dimension2Name = JobReceiveLineTab.Dimension2.Dimension2Name,
Dimension3Name = JobReceiveLineTab.Dimension3.Dimension3Name,
Dimension4Name = JobReceiveLineTab.Dimension4.Dimension4Name,
Specification = JobReceiveLineTab.Specification,
InvoiceBalQty = VJIB.BalanceQty,
Qty = VJIB.BalanceQty,
JobInvoiceHeaderDocNo = JobInvoiceHeaderTab.DocNo,
ProductName = ProductTab.ProductName,
ProductId = VJIB.ProductId,
JobInvoiceReturnHeaderId = vm.JobInvoiceReturnHeaderId,
JobInvoiceLineId = VJIB.JobInvoiceLineId,
UnitId = ProductTab.UnitId,
UnitConversionMultiplier = JobInvoiceLineTab.UnitConversionMultiplier,
DealUnitId = JobInvoiceLineTab.DealUnitId,
Rate = JobInvoiceLineTab.Rate,
//RateAfterDiscount = (linetab.Amount / linetab.DealQty),
unitDecimalPlaces = ProductTab.Unit.DecimalPlaces,
DealunitDecimalPlaces = JobInvoiceLineTab.DealUnit.DecimalPlaces,
ProductUidName = JobReceiveLineTab.ProductUid.ProductUidName,
ProductUidId = JobReceiveLineTab.ProductUidId,
}
);
return(temp);
}
public IEnumerable <PurchaseIndentLineViewModel> GetPurchaseIndentForFilters(PurchaseIndentLineFilterViewModel vm)
{
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.MaterialPlanHeaderId))
{
SaleOrderIdArr = vm.MaterialPlanHeaderId.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" };
}
var temp = (from p in db.ViewMaterialPlanBalance
join t in db.MaterialPlanHeader on p.MaterialPlanHeaderId equals t.MaterialPlanHeaderId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
join t1 in db.MaterialPlanLine on p.MaterialPlanLineId equals t1.MaterialPlanLineId 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.MaterialPlanHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.MaterialPlanHeaderId.ToString())) &&
(string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
p.BalanceQty > 0
orderby tab.DocDate, tab.DocNo, tab1.Sr
select new PurchaseIndentLineViewModel
{
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
Specification = tab1.Specification,
Dimension1Id = tab1.Dimension1Id,
Dimension2Id = tab1.Dimension2Id,
PlanBalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
MaterialPlanHeaderDocNo = tab.DocNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
PurchaseIndentHeaderId = vm.PurchaseIndentHeaderId,
MaterialPlanLineId = p.MaterialPlanLineId,
UnitId = tab2.UnitId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
}
);
return(temp);
}