public List <ExpBankVoucher> GetTransactionInfoForSearch()
{
using (_context)
{
var Data = (from t in _context.EXP_BankVoucher.AsEnumerable()
join b in _context.Sys_Bank on t.BankID equals b.BankID into Banks
from b in Banks.DefaultIfEmpty()
join br in _context.Sys_Branch on t.BranchID equals br.BranchID into Branches
from br in Branches.DefaultIfEmpty()
join ci in _context.EXP_CI on(t.CIID == null?null:t.CIID) equals ci.CIID into CIs
from ci in CIs.DefaultIfEmpty()
orderby t.BVID descending
select new ExpBankVoucher
{
BVID = t.BVID,
RefBVNo = t.RefBVNo,
BVDate = (Convert.ToDateTime(t.BVDate)).ToString("dd'/'MM'/'yyyy"),
BVType = (t.BVType == "DR"? "Debit": "Credit"),
BankID = t.BankID,
BankName = b == null?null:b.BankName,
BranchID = t.BranchID,
BranchName = br == null ? null : br.BranchName,
ACNo = t.ACNo,
ACName = t.ACName,
Remarks = t.Remarks,
RecordStatus = DalCommon.ReturnRecordStatus(t.RecordStatus)
}).ToList();
return(Data);
}
}
public PRQChemicalPI GetPIDetailsAfterSearch(long PIID)
{
using (_context)
{
var model = new PRQChemicalPI();
#region PI Information
var PIInfo = (from p in _context.PRQ_ChemicalPI.AsEnumerable()
where p.PIID == PIID
select p).FirstOrDefault();
model.PIID = PIInfo.PIID;
model.PINo = PIInfo.PINo;
model.PIDate = (Convert.ToDateTime(PIInfo.PIDate)).ToString("dd'/'MM'/'yyyy");
model.PICategory = PIInfo.PICategory;
model.OrderID = PIInfo.OrderID;
model.OrderNo = PIInfo.OrderNo;
model.SupplierID = Convert.ToInt32(PIInfo.SupplierID);
model.SupplierCode = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.SupplierID
select s.SupplierCode).FirstOrDefault();
model.SupplierName = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.SupplierID
select s.SupplierName).FirstOrDefault();
model.SupplierAddress = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.SupplierID && s.IsActive && !s.IsDelete
select s.Address).FirstOrDefault();
model.SupplierContactNumber = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.SupplierID && s.IsActive && !s.IsDelete
select s.ContactNumber).FirstOrDefault();
model.LocalAgent = Convert.ToInt32(PIInfo.LocalAgent);
model.LocalAgentCode = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.LocalAgent
select s.SupplierCode).FirstOrDefault();
model.LocalAgentName = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.LocalAgent
select s.SupplierName).FirstOrDefault();
model.LocalAgentAddress = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.LocalAgent && s.IsActive && !s.IsDelete
select s.Address).FirstOrDefault();
model.LocalAgentContactNumber = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.LocalAgent && s.IsActive && !s.IsDelete
select s.ContactNumber).FirstOrDefault();
model.ForeignAgent = Convert.ToInt32(PIInfo.ForeignAgent);
model.ForeignAgentCode = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.ForeignAgent
select s.SupplierCode).FirstOrDefault();
model.ForeignAgentName = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.ForeignAgent
select s.SupplierName).FirstOrDefault();
model.ForeignAgentAddress = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.ForeignAgent && s.IsActive && !s.IsDelete
select s.Address).FirstOrDefault();
model.ForeignAgentContactNumber = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.ForeignAgent && s.IsActive && !s.IsDelete
select s.ContactNumber).FirstOrDefault();
model.BuyerID = Convert.ToInt32(PIInfo.PIBeneficiary);
model.BuyerCode = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.PIBeneficiary
select s.SupplierCode).FirstOrDefault();
model.BuyerName = (from s in _context.Sys_Supplier.AsEnumerable()
where s.SupplierID == PIInfo.PIBeneficiary
select s.SupplierName).FirstOrDefault();
model.BuyerAddressID = Convert.ToInt32(PIInfo.BeneficiaryAddressID);
model.Address = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.PIBeneficiary && s.IsActive && !s.IsDelete
select s.Address).FirstOrDefault();
model.BuyerContactNumber = (from s in _context.Sys_SupplierAddress.AsEnumerable()
where s.SupplierID == PIInfo.PIBeneficiary && s.IsActive && !s.IsDelete
select s.ContactNumber).FirstOrDefault();
model.ExchangeCurrency = Convert.ToByte(PIInfo.ExchangeCurrency);
model.PICurrency = Convert.ToByte(PIInfo.PICurrency);
model.ExchangeRate = Convert.ToDecimal(PIInfo.ExchangeRate);
model.ExchangeValue = Convert.ToDecimal(PIInfo.ExchangeValue);
model.PaymentTerm = PIInfo.PaymentTerm;
model.PaymentMode = PIInfo.PaymentMode;
model.DeferredDays = PIInfo.DeferredDays;
model.CountryOforigin = Convert.ToInt32(PIInfo.CountryOforigin);
model.CountryOforiginName = (from c in _context.Sys_Country
where c.CountryID == PIInfo.CountryOforigin
select c.CountryName).FirstOrDefault();
model.BeneficiaryBank = Convert.ToInt32(PIInfo.BeneficiaryBank);
model.BeneficiaryBankName = (from b in _context.Sys_Branch
where b.BranchID == PIInfo.BeneficiaryBank
join ba in _context.Sys_Bank on b.BankID equals ba.BankID into Banks
from ba in Banks.DefaultIfEmpty()
select b.BranchName + "," + ba.BankName).FirstOrDefault();
model.FreightCharge = PIInfo.FreightCharge;
model.FreightChargeExtra = PIInfo.FreightChargeExtra;
model.DeliveryTerm = PIInfo.DeliveryTerm;
model.DeliveryMode = PIInfo.DeliveryMode;
model.PortofLoading = Convert.ToInt32(PIInfo.PortofLoading);
model.PortofLoadingName = (from p in _context.Sys_Port
where p.PortID == PIInfo.PortofLoading
select p.PortName).FirstOrDefault();
model.PortofDischarge = Convert.ToInt32(PIInfo.PortofDischarge);
model.PortofDischargeName = (from p in _context.Sys_Port
where p.PortID == PIInfo.PortofDischarge
select p.PortName).FirstOrDefault();
model.Transshipment = PIInfo.Transshipment;
model.PartialShipment = PIInfo.PartialShipment;
model.GoodDelivery = PIInfo.GoodDelivery;
model.ShippingMark = PIInfo.ShippingMark;
model.ExpectedShipmentTime = Convert.ToInt32(PIInfo.ExpectedShipmentTime);
model.Packing = PIInfo.Packing;
model.PreShipmentIns = PIInfo.PreShipmentIns;
model.OfferValidityDays = Convert.ToInt32(PIInfo.OfferValidityDays);
model.OfferValidityNote = PIInfo.OfferValidityNote;
model.RecordStatus = PIInfo.RecordStatus;
model.ApprovalAdvice = PIInfo.ApprovalAdvice;
#endregion
var PIItemList = (from pi in _context.PRQ_ChemicalPIItem.AsEnumerable()
where pi.PIID == PIID
from i in _context.Sys_ChemicalItem
where i.ItemID == pi.ItemID
from ou in _context.Sys_Unit
where ou.UnitID == pi.OrderUnit
from ps in _context.Sys_Size
where ps.SizeID == pi.PackSize
from su in _context.Sys_Unit
where su.UnitID == pi.SizeUnit
from piu in _context.Sys_Unit
where piu.UnitID == pi.PIUnit
select new PRQChemicalPIItem
{
PIID = Convert.ToInt32(pi.PIID),
PIItemID = pi.PIItemID,
ItemID = pi.ItemID,
ItemName = i.ItemName,
HSCode = i.HSCode,
OrderQty = Convert.ToDecimal(pi.OrderQty),
OrderUnitID = pi.OrderUnit,
OrderUnitName = ou.UnitName,
PackSizeID = pi.PackSize,
PackSizeName = ps.SizeName,
SizeUnitID = pi.SizeUnit,
SizeUnitName = su.UnitName,
PackQty = Convert.ToInt32(pi.PackQty),
PIQty = pi.PIQty,
PIUnitID = pi.PIUnit,
PIUnitName = piu.UnitName,
PIUnitPrice = Convert.ToDecimal(pi.PIUnitPrice),
PITotalPrice = Convert.ToDecimal(pi.PITotalPrice),
SupplierID = Convert.ToInt32(pi.SupplierID)
}).ToList();
model.PIItemList = PIItemList;
return(model);
}
}
