public List <PRQChemFrgnPurcOrdr> GetOrderInformationForLOV()
{
var Orders = (from o in _context.PRQ_ChemFrgnPurcOrdr.AsEnumerable()
where o.RecordStatus == "CNF" & o.OrderState == "RNG" & o.OrderCategory == "FPO"
from s1 in _context.Sys_Supplier.Where(x => x.SupplierID == o.SupplierID).DefaultIfEmpty()
join sa in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.SupplierID equals sa.SupplierID into SupplierAddress
from sa2 in SupplierAddress.DefaultIfEmpty()
from s2 in _context.Sys_Supplier.Where(x => x.SupplierID == o.LocalAgent).DefaultIfEmpty()
join la in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.LocalAgent equals la.SupplierID into LocalAgentAddress
from la2 in LocalAgentAddress.DefaultIfEmpty()
from s3 in _context.Sys_Supplier.Where(x => x.SupplierID == o.ForeignAgent).DefaultIfEmpty()
join fa in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.ForeignAgent equals fa.SupplierID into ForeignAgentAddress
from fa2 in ForeignAgentAddress.DefaultIfEmpty()
orderby o.OrderID descending
select new PRQChemFrgnPurcOrdr
{
OrderID = o.OrderID,
OrderNo = o.OrderNo,
OrderTo = DalCommon.ReturnOrderTo(o.OrderTo),
OrderDate = (Convert.ToDateTime(o.OrderDate)).ToString("dd'/'MM'/'yyyy"),
OrderType = DalCommon.ReturnOrderType(o.OrderType),
SupplierID = Convert.ToInt32((s1 == null ? null : (s1.SupplierID).ToString())),
SupplierName = (s1 == null ? null : s1.SupplierName),
SupplierCode = (s1 == null ? null : s1.SupplierCode),
SupplierAddress = (sa2 == null? null: sa2.Address),
SupplierContactNumber = (sa2 == null ? null : sa2.ContactNumber),
LocalAgent = Convert.ToInt32((la2 == null ? null : (la2.SupplierID).ToString())),
LocalAgentName = (s2 == null ? null : s2.SupplierName),
LocalAgentCode = (s2 == null ? null : s2.SupplierCode),
LocalAgentAddress = (la2 == null ? null : la2.Address),
LocalAgentContactNumber = (la2 == null ? null : la2.ContactNumber),
ForeignAgent = Convert.ToInt32((s3 == null ? null : (s3.SupplierID).ToString())),
ForeignAgentName = (s3 == null ? null : s3.SupplierName),
ForeignAgentCode = (s3 == null ? null : s3.SupplierCode),
ForeignAgentAddress = (fa2 == null ? null : fa2.Address),
ForeignAgentContactNumber = (fa2 == null ? null : fa2.ContactNumber),
OrderNote = o.OrderNote
}).ToList();
return(Orders);
}
//For Requisition LOV
public List <PRDChemProdReq> GetRequisitionFromFixedStore(string _RequisitionFrom)
{
var Data = (from r in _context.PRD_ChemProdReq.AsEnumerable()
where (r.RequisitionTo).ToString() == _RequisitionFrom && r.RequisitionState == "RNG" && r.RecordStatus == "APV"
join s in _context.SYS_Store on r.RequisitionFrom equals s.StoreID into Stores
from s in Stores.DefaultIfEmpty()
orderby r.RequisitionID descending
select new PRDChemProdReq
{
RequisitionID = r.RequisitionID,
RequisitionNo = r.RequisitionNo,
RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory),
RequisitionType = DalCommon.ReturnOrderType(r.RequisitionType),
RequiredByTime = Convert.ToByte(r.RequiredByTime),
RequisitionFrom = r.RequisitionFrom,
RequisitionFromName = (s == null? "": s.StoreName),
ReqRaisedOn = Convert.ToDateTime(r.ReqRaisedOn).ToString("dd'/'MM'/'yyyy"),
}).ToList();
return(Data);
}
public INVStoreTrans GetTransactionDetailsAfterSearch(long _TransactionID)
{
var model = new INVStoreTrans();
using (_context)
{
var RequisitionID = (from r in _context.INV_StoreTransRequest
where r.TransactionID == _TransactionID
select r.RequisitionID
).FirstOrDefault();
if (RequisitionID != null)
{
var TransactionInfo = (from t in _context.INV_StoreTrans.AsEnumerable()
where t.TransactionID == _TransactionID
join req in _context.INV_StoreTransRequest on t.TransactionID equals req.TransactionID into Requests
from req in Requests.DefaultIfEmpty()
//from req in _context.INV_StoreTransRequest.Where(x => x.TransactionID == _TransactionID).DefaultIfEmpty()
join r in _context.PRD_ChemProdReq on RequisitionID equals r.RequisitionID into Requisitions
from r in Requisitions.DefaultIfEmpty()
//from r in _context.PRD_ChemProdReq.Where(x => x.RequisitionID == RequisitionID).DefaultIfEmpty()
join u in _context.Users on(r == null ? null : r.ReqRaisedBy) equals u.UserID into UserInfo
from u in UserInfo.DefaultIfEmpty()
//from u in _context.Users.Where(x => x.UserID == r.ReqRaisedBy).DefaultIfEmpty()
select new INVStoreTrans
{
TransactionID = t.TransactionID,
TransactionNo = t.TransactionNo,
IssueFrom = t.TransactionFrom,
IssueTo = t.TransactionTo,
TransactionDate = (Convert.ToDateTime(t.TransactionDate)).ToString("dd'/'MM'/'yyyy"),
RecordStatus = t.RecordStatus,
RequisitionID = (r == null ? 0 : r.RequisitionID),
RequisitionNo = (r == null ? null : r.RequisitionNo),
RequisitionCategory = (r == null ? null : DalCommon.ReturnRequisitionCategory(r.RequisitionCategory)),
RequisitionType = (r == null ? null : DalCommon.ReturnOrderType(r.RequisitionType)),
RequiredByTime = (r == null ? null : (r.RequiredByTime).ToString()),
ReqRaisedOn = (r == null ? null : (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy")),
ReqRaisedByName = (u == null ? null : (u.FirstName + " " + u.MiddleName + " " + u.LastName)),
JobOrderNo = (r == null ? null : r.JobOrderNo),
Remark = (req == null ? null : req.Remark),
TransactionType = t.TransactionType
}).FirstOrDefault();
model.TransactionInfo = TransactionInfo;
var TransactionItemList = GetTransactionItemList(_TransactionID, TransactionInfo.IssueFrom);
model.TransactionItemList = TransactionItemList;
return(model);
}
else
{
var TransactionInfo = (from t in _context.INV_StoreTrans.AsEnumerable()
where t.TransactionID == _TransactionID
join req in _context.INV_StoreTransRequest on t.TransactionID equals req.TransactionID into Requests
from req in Requests.DefaultIfEmpty()
//from req in _context.INV_StoreTransRequest.Where(x => x.TransactionID == _TransactionID).DefaultIfEmpty()
select new INVStoreTrans
{
TransactionID = t.TransactionID,
TransactionNo = t.TransactionNo,
IssueFrom = t.TransactionFrom,
IssueTo = t.TransactionTo,
TransactionDate = (Convert.ToDateTime(t.TransactionDate)).ToString("dd'/'MM'/'yyyy"),
RecordStatus = t.RecordStatus,
Remark = (req == null ? null : req.Remark),
ReqToDate = (Convert.ToDateTime(req.ReqToDate)).ToString("dd'/'MM'/'yyyy"),
ReqFromDate = (Convert.ToDateTime(req.ReqFromDate)).ToString("dd'/'MM'/'yyyy"),
TransactionType = t.TransactionType
}).FirstOrDefault();
model.TransactionInfo = TransactionInfo;
if (TransactionInfo.TransactionType == "ADI")
{
var TransactionItemList = GetTransactionItemListForStockAdjust(_TransactionID, TransactionInfo.IssueFrom);
model.TransactionItemList = TransactionItemList;
}
else
{
var TransactionItemList = GetTransactionItemList(_TransactionID, TransactionInfo.IssueFrom);
model.TransactionItemList = TransactionItemList;
}
return(model);
}
}
}
// After Requisition LOV
public PRDChemProdReq GetRequisitionDetailsFromFixedStore(int _RequisitionID, byte _RequisitionAt)
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_RequisitionAt);
var model = new PRDChemProdReq();
var RequisitionInfo = (from r in _context.PRD_ChemProdReq.AsEnumerable()
where r.RequisitionID == _RequisitionID
from u in _context.Users.Where(x => x.UserID == r.ReqRaisedBy).DefaultIfEmpty()
select new PRDChemProdReq
{
RequisitionID = r.RequisitionID,
RequisitionNo = r.RequisitionNo,
JobOrderID = Convert.ToInt16(r.JobOrderID),
JobOrderNo = r.JobOrderNo,
ReqRaisedOn = (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy"),
ReqRaisedBy = Convert.ToInt16(r.ReqRaisedBy),
ReqRaisedByName = (u == null ? null : (u.FirstName + " " + u.MiddleName + " " + u.LastName)),
RequiredByTime = Convert.ToByte(r.RequiredByTime),
RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory),
RequisitionType = DalCommon.ReturnOrderType(r.RequisitionType),
IssueTo = r.RequisitionFrom,
IssueFrom = _RequisitionAt
}).FirstOrDefault();
model.RequisitionInfo = RequisitionInfo;
var RequisitionItemList = (from i in _context.PRD_ChemProdReqItem.AsEnumerable()
where i.RequisitionID == _RequisitionID
from it in _context.Sys_ChemicalItem.Where(x => x.ItemID == i.ItemID).DefaultIfEmpty()
join s in FinalStock on i.ItemID equals s.ItemID into badhon
from item in badhon.DefaultIfEmpty()
join su in _context.Sys_Unit on(item == null? 0: item.UnitID) equals su.UnitID into StockUnits
from su in StockUnits.DefaultIfEmpty()
join sup in _context.Sys_Supplier on(item == null ? 0 : item.SupplierID) equals sup.SupplierID into badhon2
from finalitem in badhon2.DefaultIfEmpty()
from siu in _context.Sys_Unit.Where(x => x.UnitID == i.SizeUnit).DefaultIfEmpty()
from ru in _context.Sys_Unit.Where(x => x.UnitID == i.RequisitionUnit).DefaultIfEmpty()
from ps in _context.Sys_Size.Where(x => x.SizeID == i.PackSize).DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = i.ItemID,
ItemName = (it == null ? null : it.ItemName),
StockQty = (item == null ? 0 : item.ClosingQty),
StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName),
PackSizeName = (ps == null ? null : ps.SizeName),
SizeUnitName = (siu == null ? null : siu.UnitName),
PackQty = Convert.ToInt16(i.PackQty),
RequsitionQty = i.RequsitionQty,
RequisitionUnitName = (ru == null ? null : ru.UnitName),
IssuePackSizeName = "Press F9",
IssueUnitName = "",
//IssuePackSize = Convert.ToByte(i.PackSize),
//IssuePackSizeName = (ps == null ? null : ps.SizeName),
//IssueSizeUnit = Convert.ToByte(i.SizeUnit),
//IssueSizeUnitName = (siu == null ? null : siu.UnitName),
//IssuePackQty = Convert.ToInt16(i.PackQty),
//IssueQty = i.RequsitionQty,
//IssueUnit = i.RequisitionUnit,
//IssueUnitName = (ru == null ? null : ru.UnitName),
ItemSource = "Via Requisition"
}).ToList();
model.RequisitionItemList = RequisitionItemList;
return(model);
}
