/// <summary>
///
/// </summary>
/// <param name="intNarrIdno"></param>
/// <param name="strOwnerName"></param>
/// <returns></returns>
public IList SelectForSearch(string strOwnerName, Int64 PartyName, string strLorryNo, string strPanNo)
{
using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString()))
{
var lst = (from mast in db.LorryMasts
join obj in db.AcntMasts on mast.Prty_Idno equals obj.Acnt_Idno
join obj1 in db.DriverMasts on mast.Driver_Idno equals obj1.Driver_Idno into Cidl
from mappingsCidl in Cidl.DefaultIfEmpty()
join obj2 in
(from DR in db.AcntMasts where DR.Acnt_Type == 9 select DR) on mast.Driver_Idno equals obj2.Acnt_Idno into Cidd
from mappingsCidd in Cidd.DefaultIfEmpty()
orderby obj.Acnt_Name
select new
{
prty_Name = obj.Acnt_Name,
LorryIdno = mast.Lorry_Idno,
OwnerName = mast.Owner_Name,
LorryMake = mast.Lorry_Make,
PanNo = mast.Pan_No,
ChasisNo = mast.Chassis_no,
EngineNo = mast.Eng_No,
LorryNo = mast.Lorry_No,
DriverName = (mast.Lorry_Type == 0) ? (string.IsNullOrEmpty(mappingsCidd.Acnt_Name) ? "N/A" : mappingsCidd.Acnt_Name) : (string.IsNullOrEmpty(mappingsCidl.Driver_Name) ? "N/A" : mappingsCidl.Driver_Name),
LorryType = (mast.Lorry_Type == 0 ? "Own" : "Hire"),
Status = mast.Status,
Party_Idno = mast.Prty_Idno,
LowRate = mast.LowRateWise,
MobileNo = mast.Owner_Mobile,
}).ToList();
if (PartyName != 0)
{
lst = (from I in lst where I.Party_Idno == PartyName select I).ToList();
}
if (strOwnerName != "")
{
lst = (from I in lst where I.OwnerName.ToLower().Contains(strOwnerName.ToLower()) select I).ToList();
}
if (strLorryNo != "")
{
lst = (from I in lst where I.LorryNo.ToLower().Contains(strLorryNo.ToLower()) select I).ToList();
}
if (strPanNo != "")
{
lst = (from I in lst where I.PanNo.ToLower().Contains(strPanNo.ToLower()) select I).ToList();
}
return(lst);
}
}
public IList SelectGR(int GrNo, DateTime?dtfrom, DateTime?dtto, int cityfrom, int citydely, int cityto, int sender, Int32 yearidno, Int64 UserIdno, string strGrPrefix)
{
using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString()))
{
var lst = (from hd in db.TblGrHeads
join LR in db.LorryMasts on hd.Lorry_Idno equals LR.Lorry_Idno
join cifrom in db.tblCityMasters on hd.From_City equals cifrom.City_Idno
join cito in db.tblCityMasters on hd.To_City equals cito.City_Idno
join cidVia in db.tblCityMasters on hd.DelvryPlce_Idno equals cidVia.City_Idno into Cidl
from mappingsCidl in Cidl.DefaultIfEmpty()
join cidVia in db.tblCityMasters on hd.Cityvia_Idno equals cidVia.City_Idno into ViaCity
from mappingsViaCity in ViaCity.DefaultIfEmpty()
join acnts in db.AcntMasts on hd.Sender_Idno equals acnts.Acnt_Idno
join acntss in db.AcntMasts on hd.Recivr_Idno equals acntss.Acnt_Idno
where hd.GR_Frm == "BK"
select new
{
hd.PrefixGr_No,
hd.DelvryPlce_Idno,
hd.From_City,
hd.To_City,
hd.Gr_Date,
hd.GR_Idno,
hd.Gr_No,
hd.Sender_Idno,
hd.Remark,
hd.Shipment_No,
GR_Typ = ((hd.GR_Typ == 1) ? "Paid GR" : (hd.GR_Typ == 2) ? "TBB GR" : "To Pay GR"),
hd.Recivr_Idno,
hd.Gross_Amnt,
hd.Net_Amnt,
CityTo = cito.City_Name,
CityFrom = cifrom.City_Name,
CityVia = mappingsViaCity.City_Name,
CityDely = mappingsCidl.City_Name,
Sender = acnts.Acnt_Name,
Receiver = acntss.Acnt_Name,
Year_Idno = hd.Year_Idno,
Lorry_No = LR.Lorry_No,
Owner_Name = LR.Owner_Name,
hd.Tot_KM,
Qty = ((from N in db.TblGrDetls where N.GrHead_Idno == hd.GR_Idno select N.Qty).Sum())
//Qty = 1
}).ToList();
if (GrNo > 0)
{
lst = lst.Where(l => l.Gr_No == GrNo).ToList();
}
if (strGrPrefix != null && strGrPrefix != "")
{
lst = lst.Where(l => l.PrefixGr_No.Contains(strGrPrefix)).ToList();
}
if (dtto != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.Gr_Date).Date <= Convert.ToDateTime(dtto).Date).ToList();
}
if (dtfrom != null)
{
lst = lst.Where(l => Convert.ToDateTime(l.Gr_Date).Date >= Convert.ToDateTime(dtfrom).Date).ToList();
}
if (cityto > 0)
{
lst = lst.Where(l => l.To_City == cityto).ToList();
}
if (citydely > 0)
{
lst = lst.Where(l => l.DelvryPlce_Idno == citydely).ToList();
}
if (sender > 0)
{
lst = lst.Where(l => l.Sender_Idno == sender).ToList();
}
if (yearidno > 0)
{
lst = lst.Where(l => l.Year_Idno == yearidno).ToList();
}
if (cityfrom > 0)
{
lst = lst.Where(l => l.From_City == cityfrom).ToList();
}
else if (UserIdno > 0)
{
var CityLst = db.tblFrmCityDetls.Where(U => U.User_Idno == UserIdno).Select(p => p.FrmCity_Idno).ToList();
lst = lst.Where(o => CityLst.Contains(o.From_City)).ToList();
}
return(lst);
}
}
