public IEnumerable <ComboBoxResult> GetJobInvoiceHelpListForProduct(int Id, string term)
{
var JobInvoiceHeader = new JobInvoiceReturnHeaderService(db).Find(Id);
var settings = db.JobInvoiceSettings
.Where(m => m.DocTypeId == JobInvoiceHeader.DocTypeId && m.DivisionId == JobInvoiceHeader.DivisionId && m.SiteId == JobInvoiceHeader.SiteId).FirstOrDefault();
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" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
return(from VB in db.ViewJobInvoiceBalance
join L in db.JobInvoiceLine on VB.JobInvoiceLineId equals L.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
where JobInvoiceLineTab.JobInvoiceHeader.JobWorkerId == JobInvoiceHeader.JobWorkerId &&
(string.IsNullOrEmpty(settings.filterContraSites) ? VB.SiteId == CurrentSiteId : contraSites.Contains(VB.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? VB.DivisionId == CurrentDivisionId : contraDivisions.Contains(VB.DivisionId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobInvoiceHeader.DocNo.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobInvoiceHeader.DocType.DocumentTypeShortName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.ProductUid.ProductUidName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.Product.ProductName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.Dimension1.Dimension1Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.Dimension2.Dimension2Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.Dimension3.Dimension3Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : JobInvoiceLineTab.JobReceiveLine.Dimension4.Dimension4Name.ToLower().Contains(term.ToLower())
)
select new ComboBoxResult
{
id = VB.JobInvoiceLineId.ToString(),
text = JobInvoiceLineTab.JobReceiveLine.ProductUid != null ? (JobInvoiceLineTab.JobReceiveLine.ProductUid.ProductUidName + " : " + JobInvoiceLineTab.JobReceiveLine.Product.ProductName) : JobInvoiceLineTab.JobReceiveLine.Product.ProductName,
TextProp1 = "Balance :" + VB.BalanceQty,
TextProp2 = "Date :" + JobInvoiceLineTab.JobInvoiceHeader.DocDate,
AProp1 = JobInvoiceLineTab.JobInvoiceHeader.DocType.DocumentTypeShortName + "-" + JobInvoiceLineTab.JobInvoiceHeader.DocNo,
AProp2 = ((JobInvoiceLineTab.JobReceiveLine.Dimension1.Dimension1Name == null) ? "" : JobInvoiceLineTab.JobReceiveLine.Dimension1.Dimension1Name) +
((JobInvoiceLineTab.JobReceiveLine.Dimension2.Dimension2Name == null) ? "" : "," + JobInvoiceLineTab.JobReceiveLine.Dimension2.Dimension2Name) +
((JobInvoiceLineTab.JobReceiveLine.Dimension3.Dimension3Name == null) ? "" : "," + JobInvoiceLineTab.JobReceiveLine.Dimension3.Dimension3Name) +
((JobInvoiceLineTab.JobReceiveLine.Dimension4.Dimension4Name == null) ? "" : "," + JobInvoiceLineTab.JobReceiveLine.Dimension4.Dimension4Name)
});
}
public JobInvoiceReturnLineViewModel GetJobInvoiceReturnLine(int id)
{
return((from L in db.JobInvoiceReturnLine
join Jil in db.JobInvoiceLine on L.JobInvoiceLineId equals Jil.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
join VJil in db.ViewJobInvoiceBalance on L.JobInvoiceLineId equals VJil.JobInvoiceLineId into ViewJobInvoiceBalanceTable
from ViewJobInvoiceBalanceTab in ViewJobInvoiceBalanceTable.DefaultIfEmpty()
join Jrl in db.JobReceiveLine on JobInvoiceLineTab.JobReceiveLineId equals Jrl.JobReceiveLineId into JobReceiveLineTable
from JobReceiveLineTab in JobReceiveLineTable.DefaultIfEmpty()
join Jirh in db.JobInvoiceReturnHeader on L.JobInvoiceReturnHeaderId equals Jirh.JobInvoiceReturnHeaderId into JobInvoiceReturnHeaderTable
from JobInvoiceReturnHeaderTab in JobInvoiceReturnHeaderTable.DefaultIfEmpty()
join Jih in db.JobInvoiceHeader on JobInvoiceLineTab.JobInvoiceHeaderId equals Jih.JobInvoiceHeaderId into JobInvoiceHeaderTable
from JobInvoiceHeaderTab in JobInvoiceHeaderTable.DefaultIfEmpty()
where L.JobInvoiceReturnLineId == id
select new JobInvoiceReturnLineViewModel
{
JobWorkerId = JobInvoiceReturnHeaderTab.JobWorkerId,
ProductId = JobReceiveLineTab.ProductId,
JobInvoiceLineId = L.JobInvoiceLineId,
JobInvoiceHeaderDocNo = JobInvoiceHeaderTab.DocNo,
JobInvoiceReturnHeaderId = L.JobInvoiceReturnHeaderId,
JobInvoiceReturnLineId = L.JobInvoiceReturnLineId,
Rate = L.Rate,
Amount = L.Amount,
UnitConversionMultiplier = L.UnitConversionMultiplier,
DealQty = L.DealQty,
DealUnitId = L.DealUnitId,
Qty = L.Qty,
InvoiceBalQty = ((L.JobInvoiceLineId == null || JobReceiveLineTab == null) ? L.Qty : L.Qty + (ViewJobInvoiceBalanceTab == null ? 0 : ViewJobInvoiceBalanceTab.BalanceQty)),
Remark = L.Remark,
UnitId = JobReceiveLineTab.Product.UnitId,
Dimension1Id = JobReceiveLineTab.Dimension1Id,
Dimension1Name = JobReceiveLineTab.Dimension1.Dimension1Name,
Dimension2Id = JobReceiveLineTab.Dimension2Id,
Dimension2Name = JobReceiveLineTab.Dimension2.Dimension2Name,
Dimension3Id = JobReceiveLineTab.Dimension3Id,
Dimension3Name = JobReceiveLineTab.Dimension3.Dimension3Name,
Dimension4Id = JobReceiveLineTab.Dimension4Id,
Dimension4Name = JobReceiveLineTab.Dimension4.Dimension4Name,
Specification = JobReceiveLineTab.Specification,
LotNo = JobReceiveLineTab.LotNo,
LockReason = L.LockReason,
Nature = JobInvoiceReturnHeaderTab.Nature,
ProductUidId = JobReceiveLineTab.ProductUidId,
ProductUidName = JobReceiveLineTab.ProductUid.ProductUidName,
SalesTaxGroupPersonId = JobInvoiceReturnHeaderTab.SalesTaxGroupPersonId,
SalesTaxGroupProductId = L.SalesTaxGroupProductId,
CostCenterId = L.CostCenterId,
CostCenterName = L.CostCenter.CostCenterName
}).FirstOrDefault());
}
public IEnumerable <JobInvoiceReturnLineIndexViewModel> GetLineListForIndex(int HeaderId)
{
return(from L in db.JobInvoiceReturnLine
join Jil in db.JobInvoiceLine on L.JobInvoiceLineId equals Jil.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
join Jrl in db.JobReceiveLine on JobInvoiceLineTab.JobReceiveLineId equals Jrl.JobReceiveLineId into JobReceiveLineTable
from JobReceiveLineTab in JobReceiveLineTable.DefaultIfEmpty()
join Jrh in db.JobReceiveHeader on JobReceiveLineTab.JobReceiveHeaderId equals Jrh.JobReceiveHeaderId into JobReceiveHeaderTable
from JobReceiveHeaderTab in JobReceiveHeaderTable.DefaultIfEmpty()
join P in db.Product on JobReceiveLineTab.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
where L.JobInvoiceReturnHeaderId == HeaderId
orderby L.Sr
select new JobInvoiceReturnLineIndexViewModel
{
ProductName = ProductTab.ProductName,
Qty = L.Qty,
JobInvoiceReturnLineId = L.JobInvoiceReturnLineId,
UnitId = ProductTab.UnitId,
Specification = JobReceiveLineTab.Specification,
Dimension1Name = JobReceiveLineTab.Dimension1.Dimension1Name,
Dimension2Name = JobReceiveLineTab.Dimension2.Dimension2Name,
Dimension3Name = JobReceiveLineTab.Dimension3.Dimension3Name,
Dimension4Name = JobReceiveLineTab.Dimension4.Dimension4Name,
LotNo = JobReceiveLineTab.LotNo,
JobGoodsRecieptHeaderDocNo = JobReceiveHeaderTab.DocNo,
JobInvoiceHeaderDocNo = JobInvoiceLineTab.JobInvoiceHeader.DocNo,
DealQty = L.DealQty,
DealUnitId = L.DealUnitId,
unitDecimalPlaces = ProductTab.Unit.DecimalPlaces,
DealunitDecimalPlaces = L.DealUnit.DecimalPlaces,
Rate = L.Rate,
Amount = L.Amount,
Remark = L.Remark,
ProductUidName = JobReceiveLineTab.ProductUid.ProductUidName,
UnitName = ProductTab.Unit.UnitName,
DealUnitName = L.DealUnit.UnitName,
JobInvoiceDocTypeId = L.JobInvoiceLine.JobInvoiceHeader.DocTypeId,
JobInvoiceHeaderId = L.JobInvoiceLine.JobInvoiceHeaderId,
});
}
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 JobInvoiceLineViewModel GetJobInvoiceLineBalance(int id)
{
var temp = (from VJIB in db.ViewJobInvoiceBalance
join Jil in db.JobInvoiceLine on VJIB.JobInvoiceLineId equals Jil.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
join Jrl in db.JobReceiveLine on JobInvoiceLineTab.JobReceiveLineId equals Jrl.JobReceiveLineId into JobReceiveLineTable
from JobReceiveLineTab in JobReceiveLineTable.DefaultIfEmpty()
join Jih in db.JobInvoiceHeader on JobInvoiceLineTab.JobInvoiceHeaderId equals Jih.JobInvoiceHeaderId into JobInvoiceHeaderTable
from JobInvoiceHeaderTab in JobInvoiceHeaderTable.DefaultIfEmpty()
join Jrh in db.JobReceiveHeader on JobReceiveLineTab.JobReceiveHeaderId equals Jrh.JobReceiveHeaderId into JobReceiveHeaderTable
from JobReceiveHeaderTab in JobReceiveHeaderTable.DefaultIfEmpty()
where JobInvoiceLineTab.JobInvoiceLineId == id
select new JobInvoiceLineViewModel
{
JobWorkerId = JobInvoiceHeaderTab.JobWorkerId.Value,
Amount = JobInvoiceLineTab.Amount,
ProductId = JobReceiveLineTab.ProductId,
ProductName = JobReceiveLineTab.Product.ProductName,
JobReceiveLineId = JobInvoiceLineTab.JobReceiveLineId,
JobReceiveDocNo = JobReceiveHeaderTab.DocNo,
JobInvoiceHeaderId = JobInvoiceLineTab.JobInvoiceHeaderId,
JobInvoiceLineId = JobInvoiceLineTab.JobInvoiceLineId,
InvoiceDocNo = JobInvoiceLineTab.JobInvoiceHeader.DocNo,
Qty = VJIB.BalanceQty,
Rate = JobInvoiceLineTab.Rate,
Remark = JobInvoiceLineTab.Remark,
UnitConversionMultiplier = JobInvoiceLineTab.UnitConversionMultiplier,
DealUnitId = JobInvoiceLineTab.DealUnitId,
DealQty = JobInvoiceLineTab.DealQty,
UnitId = JobReceiveLineTab.Product.UnitId,
Dimension1Id = JobReceiveLineTab.Dimension1Id,
Dimension1Name = JobReceiveLineTab.Dimension1.Dimension1Name,
Dimension2Id = JobReceiveLineTab.Dimension2Id,
Dimension2Name = JobReceiveLineTab.Dimension2.Dimension2Name,
Dimension3Id = JobReceiveLineTab.Dimension3Id,
Dimension3Name = JobReceiveLineTab.Dimension3.Dimension3Name,
Dimension4Id = JobReceiveLineTab.Dimension4Id,
Dimension4Name = JobReceiveLineTab.Dimension4.Dimension4Name,
Specification = JobReceiveLineTab.Specification,
LotNo = JobReceiveLineTab.LotNo,
SalesTaxGroupPersonId = JobInvoiceLineTab.JobInvoiceHeader.SalesTaxGroupPersonId,
SalesTaxGroupProductId = JobInvoiceLineTab.SalesTaxGroupProductId,
CostCenterId = JobInvoiceLineTab.CostCenterId,
CostCenterName = JobInvoiceLineTab.CostCenter.CostCenterName,
//DiscountPer = p.DiscountPer
Weight = JobReceiveLineTab.Qty == 0 ? 0 : (JobReceiveLineTab.Weight / JobReceiveLineTab.Qty) * VJIB.BalanceQty,
}).FirstOrDefault();
var JobInvoiceLineId = (from p in db.JobInvoiceLine
where p.JobInvoiceLineId == temp.JobInvoiceLineId
select new { LineId = p.JobInvoiceLineId, HeaderId = p.JobInvoiceHeaderId }).FirstOrDefault();
var Charges = (from p in db.JobInvoiceLineCharge
where p.LineTableId == JobInvoiceLineId.LineId
join t in db.Charge on p.ChargeId equals t.ChargeId
select new LineCharges
{
ChargeCode = t.ChargeCode,
Rate = p.Rate,
LedgerAccountCrId = p.LedgerAccountCrId,
LedgerAccountDrId = p.LedgerAccountDrId,
}).ToList();
var HeaderCharges = (from p in db.JobInvoiceHeaderCharges
where p.HeaderTableId == JobInvoiceLineId.HeaderId
join t in db.Charge on p.ChargeId equals t.ChargeId
select new HeaderCharges
{
ChargeCode = t.ChargeCode,
Rate = p.Rate,
}).ToList();
temp.RHeaderCharges = HeaderCharges;
temp.RLineCharges = Charges;
return(temp);
}