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 void DeleteLedgerTransaction(int JobInvoiceHeaderId)
{
var Header = new JobInvoiceHeaderService(_unitOfWork).Find(JobInvoiceHeaderId);
int TanaPaymentDueDocTypeId = new DocumentTypeService(_unitOfWork).Find(TanaPaymentDueDocType).DocumentTypeId;
int TdsCollectionDocTypeId = new DocumentTypeService(_unitOfWork).Find(TdsCollectionDocType).DocumentTypeId;
var LedgerHeaderList = (from H in db.LedgerHeader
where H.ReferenceDocId == Header.JobInvoiceHeaderId && H.ReferenceDocTypeId == Header.DocTypeId &&
(H.DocTypeId == TanaPaymentDueDocTypeId || H.DocTypeId == TdsCollectionDocTypeId)
select H).ToList();
foreach (var LedgerHeader in LedgerHeaderList)
{
var ExisitingLedgers = (from p in db.Ledger
where p.LedgerHeaderId == LedgerHeader.LedgerHeaderId
select p).ToList();
var LedgerLines = (from p in db.LedgerLine
where p.LedgerHeaderId == LedgerHeader.LedgerHeaderId
select p).ToList();
foreach (var item in ExisitingLedgers)
{
var LedgerAdjs = (from p in db.LedgerAdj
where p.CrLedgerId == item.LedgerId || p.DrLedgerId == item.LedgerId
select p).ToList();
foreach (var item2 in LedgerAdjs)
{
new LedgerAdjService(_unitOfWork).Delete(item2.LedgerAdjId);
//item2.ObjectState = Model.ObjectState.Deleted;
//db.LedgerAdj.Remove(item2);
}
//item.ObjectState = Model.ObjectState.Deleted;
//db.Ledger.Remove(item);
new LedgerService(_unitOfWork).Delete(item.LedgerId);
}
foreach (var item in LedgerLines)
{
var RefLines = (from p in db.LedgerLineRefValue
where p.LedgerLineId == item.LedgerLineId
select p).ToList();
foreach (var RefLine in RefLines)
{
//RefLine.ObjectState = Model.ObjectState.Deleted;
//db.LedgerLineRefValue.Remove(RefLine);
new LedgerLineRefValueService(_unitOfWork).Delete(RefLine.LedgerLineRefValueId);
}
//item.ObjectState = Model.ObjectState.Deleted;
//db.LedgerLine.Remove(item);
new LedgerLineService(_unitOfWork).Delete(item.LedgerLineId);
}
//LedgerHeader.ObjectState = Model.ObjectState.Deleted;
//db.LedgerHeader.Remove(LedgerHeader);
new LedgerHeaderService(_unitOfWork).Delete(LedgerHeader.LedgerHeaderId);
}
var LedgerAdj = (from L in db.JobInvoiceLine
join H in db.JobInvoiceHeader on L.JobInvoiceHeaderId equals H.JobInvoiceHeaderId into JobInvoiceHeaderTable from JobInvoiceHeaderTab in JobInvoiceHeaderTable.DefaultIfEmpty()
join Ld in db.Ledger on new { A1 = L.JobInvoiceLineId, A2 = JobInvoiceHeaderTab.DocTypeId } equals new { A1 = Ld.ReferenceDocLineId ?? 0, A2 = Ld.ReferenceDocTypeId ?? 0 } into LedgerTable from LedgerTab in LedgerTable.DefaultIfEmpty()
join LAd in db.LedgerAdj on LedgerTab.LedgerId equals LAd.CrLedgerId into LedgerAdjTable from LedgerAdjTab in LedgerAdjTable.DefaultIfEmpty()
where L.JobInvoiceHeaderId == JobInvoiceHeaderId &&
LedgerAdjTab.Adj_Type == "Advance Adjustment"
select LedgerAdjTab).ToList();
foreach (var item in LedgerAdj)
{
//item.ObjectState = Model.ObjectState.Deleted;
//db.LedgerAdj.Remove(item);
new LedgerAdjService(_unitOfWork).Delete(item.LedgerAdjId);
}
}
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);
}