public IList SelectTyreMovementReport(DateTime?dtfrom, DateTime?dtto, Int32 yearidno, Int64 ItemIdno, string serailNum)
{
using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString()))
{
var lst = (from stc in db.Stckdetls
join loc in db.tblCityMasters on stc.Loc_Idno equals loc.City_Idno
join Itm in db.tblItemMastPurs on stc.ItemIdno equals Itm.Item_Idno
join mat in db.MatIssHeads on stc.MtrlIssue_Idno equals mat.MatIss_Idno into material
from MTRL in material.DefaultIfEmpty()
join Lm in db.LorryMasts on MTRL.Truck_Idno equals Lm.Lorry_Idno into LRY
from Lorry in LRY.DefaultIfEmpty()
join PBH in db.tblPBillHeads on stc.PBillIdno equals PBH.PBillHead_Idno
join acnt in db.AcntMasts on Lorry.Driver_Idno equals acnt.Acnt_Idno into driver
from DRVR in driver.DefaultIfEmpty()
select new
{
SerialNum = stc.SerialNo,
ItemName = Itm.Item_Name,
ItemIdno = stc.ItemIdno,
LorryNum = Lorry.Lorry_No,
//LorryNum = Lm.Lorry_No,
DrvrName = DRVR.Acnt_Name,
DocNo = PBH.PBillHead_No,
DocDate = PBH.PBillHead_Date,
YearIdno = PBH.Year_Idno,
//Location = loc.Loc_Name,
Location = loc.City_Name,
StckOut = ((MTRL.MatIss_Typ == 1) ? "Material Issue" : "NA"),
StckIn = stc.Item_from
}).ToList();
if (yearidno > 0)
{
lst = lst.Where(l => l.YearIdno == yearidno).ToList();
}
if (dtto != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.DocDate).Date <= Convert.ToDateTime(dtto).Date).ToList();
}
if (dtfrom != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.DocDate).Date >= Convert.ToDateTime(dtfrom).Date).ToList();
}
if (serailNum != "")
{
lst = lst.Where(l => l.SerialNum == serailNum).ToList();
}
if (ItemIdno > 0)
{
lst = lst.Where(l => l.ItemIdno == ItemIdno).ToList();
}
return(lst);
}
}
public Int64 TotalRecords(DateTime?dtfrom, DateTime?dtto, Int32 yearidno, Int64 ItemIdno, string serailNum)
{
using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString()))
{
int Count = 0;
var lst = (from stc in db.Stckdetls
join loc in db.tblCityMasters on stc.Loc_Idno equals loc.City_Idno
join Itm in db.tblItemMastPurs on stc.ItemIdno equals Itm.Item_Idno
join mat in db.MatIssHeads on stc.MtrlIssue_Idno equals mat.MatIss_Idno into material
from MTRL in material.DefaultIfEmpty()
join Lm in db.LorryMasts on MTRL.Truck_Idno equals Lm.Lorry_Idno into LRY
from Lorry in LRY.DefaultIfEmpty()
join PBH in db.tblPBillHeads on stc.PBillIdno equals PBH.PBillHead_Idno
join acnt in db.AcntMasts on Lorry.Driver_Idno equals acnt.Acnt_Idno into driver
from DRVR in driver.DefaultIfEmpty()
select new
{
stc.ItemIdno,
DocDate = PBH.PBillHead_Date,
YearIdno = PBH.Year_Idno,
SerialNum = stc.SerialNo
}).ToList();
if (yearidno > 0)
{
Count = lst.Where(l => l.YearIdno == yearidno).Count();
}
if (dtto != null)
{
Count = lst.Where(l => Convert.ToDateTime(l.DocDate).Date <= Convert.ToDateTime(dtto).Date).Count();
}
if (dtfrom != null)
{
Count = lst.Where(l => Convert.ToDateTime(l.DocDate).Date >= Convert.ToDateTime(dtfrom).Date).Count();
}
if (serailNum != "")
{
Count = lst.Where(l => l.SerialNum == serailNum).Count();
}
if (ItemIdno > 0)
{
Count = lst.Where(l => l.ItemIdno == ItemIdno).Count();
}
return(Count);
}
}