public List <SysArticleChallan> GetSearchInformation()
{
using (_context)
{
var Data = (from c in _context.Sys_ArticleChallan.AsEnumerable()
join b in _context.Sys_Buyer on c.BuyerID equals b.BuyerID into Buyers
from b in Buyers.DefaultIfEmpty()
join a in _context.Sys_Article on c.ArticleID equals a.ArticleID into Articles
from a in Articles.DefaultIfEmpty()
orderby c.ArticleChallanID descending
select new SysArticleChallan
{
ArticleChallanID = c.ArticleChallanID,
ArticleChallanNo = c.ArticleChallanNo,
BuyerID = c.BuyerID,
BuyerCode = (b == null?null:b.BuyerCode),
BuyerName = (b == null ? null : b.BuyerName),
ArticleID = c.ArticleID,
ArticleName = (a == null?null:a.ArticleName),
PreparationDate = Convert.ToDateTime(c.PreparationDate).ToString("dd'/'MM'/'yyyy"),
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus),
ChallanNote = c.ChallanNote,
ArticleNote = c.ArticleNote,
ArticleArea = c.ArticleArea
}).ToList();
return(Data);
}
}
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 List <InvLeatherStockAdjustRequest> GetAllCNFRequestList()
{
var AllRequest = (from r in _context.Inv_LeatherStockAdjustRequest.AsEnumerable()
where r.RecordStatus == "CNF" || r.RecordStatus == "APV"
from s in _context.SYS_Store
where s.StoreID == r.StoreID
from t in _context.Sys_LeatherType
where t.LeatherTypeID == r.LeatherType
orderby r.RequestDate descending
select new InvLeatherStockAdjustRequest
{
RequestID = r.RequestID,
PurchaseYear = r.PurchaseYear,
RequestDate = (r.RequestDate).ToString("dd'/'MM'/'yyyy"),
LeatherType = r.LeatherType,
StoreName = s.StoreName,
LeatherTypeName = t.LeatherTypeName,
StoreID = r.StoreID,
RecordStatus = DalCommon.ReturnRecordStatus(r.RecordStatus)
}).ToList();
return(AllRequest);
}
public object GetFrghtBillDocs()
{
var billDocs = _context.EXP_FreightBill.ToList();
var results = billDocs.Select(
bill => new
{
bill.FreightBillID,
bill.FreightBillNo,
FreightBillDate = string.Format("{0:dd/MM/yyyy}", bill.FreightBillDate),
bill.FreightBillRef,
//bill.LCID,
//LCNo = bill.LCID == null ? "" : _context.EXP_LCOpening.FirstOrDefault(ob => ob.LCID == bill.LCID).LCNo,
//bill.PIID,
//PINo = bill.PIID == null ? "" : _context.EXP_LeatherPI.FirstOrDefault(ob => ob.PIID == bill.PIID).PINo,
bill.CIID,
CINo = bill.CIID == null ? "" : _context.EXP_CI.FirstOrDefault(ob => ob.CIID == bill.CIID).CINo,
CIRefNo = bill.CIID == null ? "" : _context.EXP_CI.FirstOrDefault(ob => ob.CIID == bill.CIID).CIRefNo,
bill.BLID,
BLNo = bill.BLID == null ? "" : _context.EXP_BillofLading.FirstOrDefault(ob => ob.BLID == bill.BLID).BLNo,
bill.FreightAgentID,
FreightAgentName = bill.FreightAgentID == null ? ""
: _context.Sys_Buyer.FirstOrDefault(ob => ob.BuyerID == bill.FreightAgentID).BuyerName,
RecordStatus = DalCommon.ReturnRecordStatus(bill.RecordStatus)
}).OrderByDescending(ob => ob.FreightBillID).ToList();
return(results);
}
public List <PrqPurchaseChallan> GetChallanList(long purchaseID)
{
var challanList = (from c in _context.Prq_PurchaseChallan.AsEnumerable()
where c.PurchaseID == purchaseID
from s in _context.Sys_Source
where s.SourceID == c.SourceID
from l in _context.Sys_Location
where l.LocationID == c.LocationID
orderby c.ChallanID
select new PrqPurchaseChallan
{
ChallanID = c.ChallanID,
ChallanNo = c.ChallanNo,
ChallanDate = (c.ChallanDate).ToString("dd'/'MM'/'yyyy"),
SourceID = c.SourceID,
SourceName = s.SourceName,
LocationID = c.LocationID,
LocationName = l.LocationName,
ChallanCategory = c.ChallanCategory,
ReceiveStore = DalCommon.GetStoreName(c.ReceiveStore),
ChallanNote = c.ChallanNote,
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus)
}).ToList();
return(challanList);
}
// Search List for Leather Stock Adjustment Request
public List <InvLeatherStockAdjustRequest> GetAllNCFRequestList()
{
var AllRequest = (from r in _context.Inv_LeatherStockAdjustRequest.AsEnumerable()
where r.RecordStatus != "APV"
from s in _context.SYS_Store
where s.StoreID == r.StoreID
from t in _context.Sys_LeatherType
where t.LeatherTypeID == r.LeatherType
//from i in _context.Inv_LeatherStockAdjustItem.Where(x=>x.RequestID==r.RequestID).DefaultIfEmpty()
//from sp in _context.Sys_Supplier.Where(x=>x.SupplierID== i.SupplierID).DefaultIfEmpty()
orderby r.RequestDate descending
select new InvLeatherStockAdjustRequest
{
RequestID = r.RequestID,
PurchaseYear = r.PurchaseYear,
RequestDate = (r.RequestDate).ToString("dd'/'MM'/'yyyy"),
LeatherType = r.LeatherType,
StoreName = s.StoreName,
LeatherTypeName = t.LeatherTypeName,
StoreID = r.StoreID,
RecordStatus = DalCommon.ReturnRecordStatus(r.RecordStatus)
//SupplierID= (i.SupplierID).ToString(),
//SupplierName= sp.SupplierName,
//ChallanID= (i.ChallanID).ToString()
}).ToList();
return(AllRequest);
}
public EXP_DeliveryChallanCI SetToDeliveryChallanCIModelObject(EXPDeliveryChallanCI model, int _userid)
{
EXP_DeliveryChallanCI entity = new EXP_DeliveryChallanCI();
entity.DeliverChallanID = model.DeliverChallanID;
entity.CIID = model.CIID;
entity.PLID = model.PLID;
entity.RecordStatus = DalCommon.ReturnRecordStatus(model.RecordStatus);
entity.SetOn = DateTime.Now;
entity.SetBy = _userid;
entity.RecordStatus = "NCF";
entity.IPAddress = GetIPAddress.LocalIPAddress();
return(entity);
}
private PrqChemLocalPurcBill SetToBillModel(PRQ_ChemLocalPurcBill entity)
{
var model = new PrqChemLocalPurcBill();
model.BillId = entity.BillID;
model.BillNo = entity.BillNo;
model.BillDate = string.Format("{0:dd/MM/yyyy}", entity.BillDate);
model.SupplierName = entity.SupplierID == null
? ""
: _unit.SysSupplierRepository.GetByID(entity.SupplierID).SupplierName;
model.SupplierBillNo = entity.SupplierBillNo;
model.RecordStatus = DalCommon.ReturnRecordStatus(entity.RecordStatus);
return(model);
}
public IEnumerable <PrdRecipe> GetAllNcfRecipeList()
{
#region Old Code
//var items = from r in _context.PRD_Recipe
// join p in _context.Sys_ProductionProces on r.RecipeFor equals p.ProcessID
// join c in _context.Sys_Color on r.ArticleColor equals c.ColorID
// join u in _context.Sys_Unit on r.BaseUnit equals u.UnitID
// select new PrdRecipe
// {
// RecipeID = r.RecipeID,
// RecipeName=r.RecipeName,
// RecipeNo = r.RecipeNo,
// ArticleNo=r.ArticleNo,
// ArticleName =r.ArticleName,
// ArticleColor = r.ArticleColor,
// ArticleColorName=c.ColorName,
// ArticleChallanNo=r.ArticleChallanNo,
// RecipeFor=r.RecipeFor,
// RecipeProcessName =p.ProcessName,
// CalculationBase=r.CalculationBase,
// BaseQuantity=r.BaseQuantity,
// BaseUnit=r.BaseUnit,
// UnitName=u.UnitName,
// RecordStatus = r.RecordStatus
// };
#endregion
var items = _unit.PrdRecipeRepository.Get()
.Select(r => new PrdRecipe()
{
RecipeID = r.RecipeID,
RecipeName = r.RecipeName,
RecipeNo = r.RecipeNo,
ArticleNo = r.ArticleNo,
ArticleName = r.ArticleName,
ArticleColor = r.ArticleColor,
ArticleColorName = r.ArticleColor != null ? _unit.SysColorRepository.GetByID(r.ArticleColor).ColorName : "",
ArticleChallanNo = r.ArticleChallanNo,
RecipeFor = r.RecipeFor,
RecipeProcessName = r.RecipeFor != null ? _unit.SysProductionProces.GetByID(r.RecipeFor).ProcessName : "",
CalculationBase = r.CalculationBase,
BaseQuantity = r.BaseQuantity,
BaseUnit = r.BaseUnit,
UnitName = _unit.SysUnitRepository.GetByID(r.BaseUnit).UnitName,
RecordStatus = DalCommon.ReturnRecordStatus(r.RecordStatus)
}).ToList();
return(items.ToList().OrderByDescending(o => o.RecipeID));
}
public object GetReq(string year, string month, string cat, byte?floor)
{
var query = new StringBuilder();
query.Append(" SELECT ISNULL(YMFRD.RequisitionDateID,0) RequisitionDateID, YMFRD.RequisitionNo, ");
query.Append(" CONVERT(NVARCHAR(12), YMFRD.RequiredDate,106) RequiredDate, YMFRD.RequisitionStatus, ");
query.Append(" YMFRD.RecordStatus, YM.ConcernStore IssueFrom, YM.ProductionFloor IssueTo");
query.Append(" FROM PRD_YearMonth YM ");
query.Append(" LEFT JOIN PRD_YearMonthSchedule YMS ON YMS.YearMonID=YM.YearMonID ");
query.Append(" LEFT JOIN PRD_YearMonthFinishReqDate YMFRD ON YMFRD.ScheduleID=YMS.ScheduleID ");
query.Append(" WHERE YM.ScheduleFor = 'CRR' AND YMFRD.RequisitionDateID != 0 ");
if (month != "0")
{
query.Append(" AND YM.ScheduleMonth='" + month + "' ");
}
if (year != "0")
{
query.Append(" AND YM.ScheduleYear='" + year + "' ");
}
if (floor != 0)
{
query.Append(" AND YM.ProductionFloor=" + floor);
}
//"SELECT YMFRD.RequisitionDateID, YMFRD.RequisitionNo, YMFRD.RequiredDate, YMFRD.RequisitionStatus, YMFRD.RecordStatus " +
//"FROM PRD_YearMonth YM " +
//"LEFT JOIN PRD_YearMonthSchedule YMS ON YMS.YearMonID=YM.YearMonID " +
//"LEFT JOIN PRD_YearMonthFinishReqDate YMFRD ON YMFRD.ScheduleID=YMS.ScheduleID " +
//"WHERE YM.ScheduleMonth=" + month + " AND YM.ScheduleYear=" + year + " AND YM.ScheduleFor='" + cat + "' AND YM.ProductionFloor=" + floor;
var items = _context.Database.SqlQuery <RequisitionVM>(query.ToString()).ToList();
var results = items.Select(item => new
{
item.RequisitionDateID,
item.RequisitionNo,
RequiredDate = string.Format("{0:dd/MM/yyyy}", item.RequiredDate),
RequisitionStatus = DalCommon.ReturnRequisitionStatus(item.RequisitionStatus),
RecordStatus = DalCommon.ReturnRecordStatus(item.RecordStatus),
item.IssueFrom,
item.IssueTo
}).ToList();
return(results.OrderByDescending(ob => ob.RequisitionDateID));
}
public List <PurchaseReceive> GetPurchaseInformation()
{
var allData = (from p in _context.Prq_Purchase
//where p.RecordStatus == "NCF"
from s in _context.Sys_Supplier
where s.SupplierID == p.SupplierID
from sa in _context.Sys_SupplierAddress
where sa.SupplierAddressID == p.SupplierAddressID
orderby p.PurchaseID descending
select new PurchaseReceive
{
PurchaseID = p.PurchaseID,
PurchaseNo = p.PurchaseNo,
SupplierID = p.SupplierID,
SupplierName = s.SupplierName,
Address = sa.Address,
SupplierAddressID = p.SupplierAddressID,
PurchaseCategory = p.PurchaseCategory,
PurchaseType = p.PurchaseType,
PurchaseYear = p.PurchaseYear,
TempPurchaseDate = (p.PurchaseDate),
RecordStatus = p.RecordStatus
}).ToList();
foreach (var Purchase in allData)
{
//decimal TotalQty = 0;
var Challan = (from c in _context.Prq_PurchaseChallan
where c.PurchaseID == Purchase.PurchaseID
join i in _context.Prq_PurchaseChallanItem on c.ChallanID equals i.ChallanID into Items
from i in Items.DefaultIfEmpty().AsEnumerable()
select i).ToList();
Purchase.TotalItem = Challan.Sum(p => p.ReceiveQty);
Purchase.PurchaseDate = (Purchase.TempPurchaseDate).ToString("dd'/'MM'/'yyyy");
Purchase.RecordStatus = DalCommon.ReturnRecordStatus(Purchase.RecordStatus);
}
return(allData);
}
public List <PrqPurchaseChallanItem> GetItemListForChallan(string ChallanID)
{
var ItemList = (from c in _context.Prq_PurchaseChallanItem.AsEnumerable()
where (c.ChallanID).ToString() == ChallanID
select new PrqPurchaseChallanItem
{
ChallanItemID = c.ChallanItemID,
ChallanID = c.ChallanID,
ItemTypeName = DalCommon.GetItemTypeName(c.ItemTypeID),
SizeName = DalCommon.GetSizeName(c.ItemSizeID),
UnitName = DalCommon.GetUnitName(c.UnitID),
Description = c.Description,
ChallanQty = c.ChallanQty,
ReceiveQty = c.ReceiveQty,
Remark = c.Remark,
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus)
});
return(ItemList.ToList());
}
//For Search Grid
public List <INVStoreTrans> GetTransactionInfoForSearch()
{
using (_context)
{
var Data = (from t in _context.INV_StoreTrans.AsEnumerable()
where t.TransactionCategory == "RCV" & t.TransactionType == "STR"
from tf in _context.SYS_Store.Where(x => (x.StoreID).ToString() == t.TransactionFrom).DefaultIfEmpty()
from tt in _context.SYS_Store.Where(x => (x.StoreID).ToString() == t.TransactionTo).DefaultIfEmpty()
orderby t.TransactionID descending
select new INVStoreTrans
{
TransactionID = t.TransactionID,
TransactionNo = t.TransactionNo,
TransactionDate = (Convert.ToDateTime(t.TransactionDate)).ToString("dd'/'MM'/'yyyy"),
TransactionFrom = (tf == null ? null : tf.StoreName),
TransactionTo = (tt == null ? null : tt.StoreName),
RecordStatus = DalCommon.ReturnRecordStatus(t.RecordStatus)
}).ToList();
return(Data);
}
}
public List <PrqPurchaseChallanItem> GetItemListForChallan2(string ChallanID)
{
using (var Context = new BLC_DEVEntities())
{
var ItemList = (from c in Context.Prq_PurchaseChallanItem.AsEnumerable()
where (c.ChallanID).ToString() == ChallanID
select new PrqPurchaseChallanItem
{
ChallanItemID = c.ChallanItemID,
ChallanID = c.ChallanID,
ItemTypeID = c.ItemTypeID.ToString(CultureInfo.InvariantCulture),
ItemSizeID = c.ItemSizeID.ToString(CultureInfo.InvariantCulture),
UnitID = c.UnitID.ToString(CultureInfo.InvariantCulture),
Description = c.Description,
ChallanQty = c.ChallanQty,
ReceiveQty = c.ReceiveQty,
Remark = c.Remark,
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus)
});
return(ItemList.ToList());
}
}
public List <PRQChemicalPI> GetPIInformationForSearch()
{
using (_context)
{
var Data = (from p in _context.PRQ_ChemicalPI.AsEnumerable()
//from s in _context.Sys_Supplier.Where(x => x.SupplierID == p.SupplierID).DefaultIfEmpty()
join s in _context.Sys_Supplier on(p.SupplierID == null? null: p.SupplierID) equals s.SupplierID into Suppliers
from s in Suppliers.DefaultIfEmpty()
//from la in _context.Sys_Supplier.Where(x => x.SupplierID == p.LocalAgent).DefaultIfEmpty()
join la in _context.Sys_Supplier on(p.LocalAgent == null ? null : p.SupplierID) equals la.SupplierID into LocalAgents
from la in LocalAgents.DefaultIfEmpty()
//from fa in _context.Sys_Supplier.Where(x => x.SupplierID == p.ForeignAgent).DefaultIfEmpty()
join fa in _context.Sys_Supplier on(p.ForeignAgent == null ? null : p.SupplierID) equals fa.SupplierID into ForeignAgents
from fa in ForeignAgents.DefaultIfEmpty()
orderby p.PIID descending
select new PRQChemicalPI
{
PIID = p.PIID,
PINo = p.PINo,
PIDate = (Convert.ToDateTime(p.PIDate)).ToString("dd'/'MM'/'yyyy"),
PICategory = (p.PICategory == "PI" ? "Proforma Invoice" : "Indent Order"),
SupplierName = (s == null ? null : s.SupplierName),
LocalAgentName = (la == null ? null : la.SupplierName),
ForeignAgentName = (fa == null ? null : fa.SupplierName),
RecordStatus = DalCommon.ReturnRecordStatus(p.RecordStatus)
}).ToList();
return(Data);
}
}
public PurchaseReceiveTest GetDetailPurchaseInformation(string PurchaseNumber)
{
var model = new PurchaseReceiveTest();
var PurchaseInfo = (from p in _context.Prq_Purchase.AsEnumerable()
where (p.PurchaseID).ToString() == PurchaseNumber
from s in _context.Sys_Supplier
where s.SupplierID == p.SupplierID
from sa in _context.Sys_SupplierAddress
where sa.SupplierID == p.SupplierID
select new PrqPurchase
{
SupplierID = p.SupplierID,
SupplierCode = s.SupplierCode,
SupplierName = s.SupplierName,
SupplierAddressID = p.SupplierAddressID,
Address = sa.Address,
ContactNumber = sa.ContactNumber,
ContactPerson = sa.ContactPerson,
PurchaseID = p.PurchaseID,
PurchaseNo = p.PurchaseNo,
PurchaseCategory = p.PurchaseCategory,
PurchaseType = p.PurchaseType,
PurchaseDate = (p.PurchaseDate).ToString("dd'/'MM'/'yyyy"),
PurchaseYear = p.PurchaseYear,
PurchaseNote = p.PurchaseNote,
RecordStatus = p.RecordStatus
}).FirstOrDefault();
model.PurchaseInformation = PurchaseInfo;
var ChallanInfo = (from c in _context.Prq_PurchaseChallan.AsEnumerable()
where (c.PurchaseID).ToString() == PurchaseNumber
from s in _context.Sys_Source
where s.SourceID == c.SourceID
from l in _context.Sys_Location
where l.LocationID == c.LocationID
orderby c.ChallanID
select new PrqPurchaseChallan
{
ChallanID = c.ChallanID,
ChallanNo = c.ChallanNo,
ChallanDate = (c.ChallanDate).ToString("dd'/'MM'/'yyyy"),
SourceID = c.SourceID,
SourceName = s.SourceName,
LocationID = c.LocationID,
LocationName = l.LocationName,
ChallanCategory = c.ChallanCategory,
ReceiveStore = DalCommon.GetStoreName(c.ReceiveStore),
ChallanNote = c.ChallanNote,
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus)
}).ToList();
model.ChallanList = ChallanInfo;
if (ChallanInfo.Count > 0)
{
if ((ChallanInfo.FirstOrDefault().ChallanID).ToString() != null)
{
var ChallanItemInfoTest = (from c in _context.Prq_PurchaseChallanItem.AsEnumerable()
where c.ChallanID == ChallanInfo.FirstOrDefault().ChallanID
select new PrqPurchaseChallanItem
{
ChallanItemID = c.ChallanItemID,
ChallanID = c.ChallanID,
ItemTypeName = DalCommon.GetItemTypeName(c.ItemTypeID),
SizeName = DalCommon.GetSizeName(c.ItemSizeID),
UnitName = DalCommon.GetUnitName(c.UnitID),
Description = c.Description,
ChallanQty = c.ChallanQty,
ReceiveQty = c.ReceiveQty,
Remark = c.Remark,
RecordStatus = DalCommon.ReturnRecordStatus(c.RecordStatus)
});
model.ChallanItemList = ChallanItemInfoTest.ToList();
}
}
return(model);
}
