/// <summary>
/// Method to get CabCapacity
/// </summary>
/// <returns></returns>
public List<CabCapacity> CABTYPELIST()
{
List<CabCapacity > lstcabcapacity = null;
string proc_name = ConstantsDLL.usp_GETCABCAPACITY ;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbCABCPC = ds.Tables[0];
lstcabcapacity = tbCABCPC.AsEnumerable().Select(CabCpcty => new CabCapacity
{
capacity = Convert.ToString(CabCpcty["cabcapacity"])
}).ToList();
}
}
}
}
return lstcabcapacity;
}
/// <summary>
/// Method to get CabProperty
/// </summary>
public List<CabProperty> GetCabProperty()
{
List<CabProperty> lstgetcabproperty = null;
string proc_name = ConstantsDLL.USP_GETCABPROPERTY;
using(SqlHelper.SqlHelper db=new SqlHelper.SqlHelper() )
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbCabProperty = ds.Tables[0];
lstgetcabproperty = tbCabProperty.AsEnumerable().Select(cabProperty => new CabProperty {
PropertyId = Convert.ToInt32(cabProperty["PropertyId"]),
PropertyName = Convert.ToString(cabProperty["PropertyName"]),
IsCompulsory=Convert.ToBoolean(cabProperty["IsCompulsory"])
}).ToList();
}
}
}
}
return lstgetcabproperty;
}
/// <summary>
/// Method to get RoleDetail
/// </summary>
/// <returns></returns>
public List<RoleDetail> GetRoleDetail()
{
List<RoleDetail> lstroledetail = null;
string proc_name = ConstantsDLL.USP_GETROLEDETAIL;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbRoleDeatil = ds.Tables[0];
lstroledetail = tbRoleDeatil.AsEnumerable().Select(roledetail => new RoleDetail
{
RoleId = Convert.ToInt32(roledetail["RoleId"]),
RoleName = Convert.ToString(roledetail["RoleName"]),
RoleAccess = Convert.ToString(roledetail["RoleAccess"]),
CreatedDate = Convert.ToString(roledetail["CreatedDate"]),
CreatedBy = Convert.ToString(roledetail["CreatedBy"]),
ModifiedDate = Convert.ToString(roledetail["ModifiedDate"]),
ModifiedBy = Convert.ToString(roledetail["ModifiedBy"]),
IsActive = Convert.ToBoolean(roledetail["IsActive"])
}).ToList();
}
}
}
}
return lstroledetail;
}
/// <summary>
/// Method to get DC list
/// </summary>
/// <param name="VendorId"></param>
/// <param name="IsActive"></param>
/// <returns></returns>
public List<DC> GetDCList()
{
List<DC> lstDC = null;
string proc_name = ConstantsDLL.USP_GETDCLIST;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbDCs = ds.Tables[0];
lstDC = tbDCs.AsEnumerable().Select(vendor => new DC
{
DCID = Convert.ToInt32(vendor["DCID"]),
DCName = Convert.ToString(vendor["DCName"]),
IsActive = Convert.ToInt32(vendor["IsActive"]),
CreatedDate = Convert.ToString(vendor["CreatedDate"]),
CreatedBy = Convert.ToString(vendor["CreatedBy"]),
ModifiedDate = Convert.ToString(vendor["ModifiedDate"]),
ModifiedBy = Convert.ToString(vendor["ModifiedBy"])
}).ToList();
}
}
}
}
return lstDC;
}
/// <summary>
/// Method to get CabType
/// </summary>
/// <param name="CabName"></param>
/// <param name="IsActive"></param>
/// <returns></returns>
public List<CabType> CABTYPELIST( bool? IsActive)
{
List<CabType> lstcabtype = null;
string proc_name = ConstantsDLL.USP_GETCABTYPE;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbCABTYPE = ds.Tables[0];
lstcabtype = tbCABTYPE.AsEnumerable().Select(Cabtype => new CabType
{
CABNAME = Convert.ToString(Cabtype["CABNAME"])
}).ToList();
}
}
}
}
return lstcabtype;
}
public List<Shift> GetShiftList(Shift objShift)
{
List<Shift> lstShift = null;
string proc_name = ConstantsDLL.USP_GETSHIFTINFO;
SqlParameter[] param = new SqlParameter[4];
if(objShift.ShiftId==0)
{
param[0] = new SqlParameter("@ShiftId", null);
}
else
{
param[0] = new SqlParameter("@ShiftId", objShift.ShiftId);
}
param[1] = new SqlParameter("@ShiftType", objShift.ShiftType);
param[2] = new SqlParameter("@ShiftCategory", objShift.ShiftCategory);
param[3] = new SqlParameter("@IsActive", objShift.IsActive);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbShift = ds.Tables[0];
lstShift = tbShift.AsEnumerable().Select(shift => new Shift
{
ShiftId = Convert.ToInt32(shift["ShiftId"]),
ShiftType = Convert.ToString(shift["ShiftType"]),
ShiftCategory = Convert.ToString(shift["ShiftCategory"]),
ShiftTime =DateTime.Parse(shift["ShiftTime"].ToString()).ToString("HH:mm"),
DcName = Convert.ToString(shift["DCName"]),
IsActive=Convert.ToBoolean(shift["IsActive"])
}).ToList();
}
}
}
}
return lstShift;
}
/// <summary>
/// Method to get Backend Record For Validate Roster
/// </summary>
/// <returns></returns>
public DataSet RecordForValidateRoster()
{
DataSet RecordForValidateDS = new DataSet();
string proc_name = ConstantsDLL.USP_GETRECORDFORROSTER;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
RecordForValidateDS = ds;
}
}
}
return RecordForValidateDS;
}
public List<RouteMaster> GetAllPointsByRoute(string RouteName)
{
try
{
List<RouteMaster> list = null;
SqlParameter[] opara =
{
new SqlParameter("@RouteName",RouteName)
};
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(ConstantsDLL.USP_GETPOINTSBYROUTE, opara))
{
if (ds != null)
{
list = ds.Tables[0].AsEnumerable().Select(x => new RouteMaster
{
RouteID = (Int32)(x["RouteID"]),
//RouteName = (string)(x["RouteName"] ?? ""),
DropPoint = (string)(x["DropPoint"] == null || x["DropPoint"] == DBNull.Value ? string.Empty : x["DropPoint"].ToString()),
//DCID = (Int32)(x["DCID"] ?? ""),
// DCName = (string)(x["DCName"] ?? ""),
//IsActive = (Boolean)(x["IsActive"] ?? "")
}).ToList();
return list;
}
else
{
return list;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Method to get vendor list by their status
/// </summary>
/// <param name="VendorId"></param>
/// <param name="IsActive"></param>
/// <returns></returns>
public List<Vendor> GetVendorList(Int32? VendorId, bool? IsActive)
{
List<Vendor> lstVendor = null;
string proc_name = ConstantsDLL.USP_GETVENDORLIST;
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@VendorID", VendorId);
param[1] = new SqlParameter("@IsActive", IsActive);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if(ds.Tables[0].Rows.Count>0)
{
DataTable tbVendor = ds.Tables[0];
lstVendor = tbVendor.AsEnumerable().Select(vendor => new Vendor
{
VendorID = Convert.ToInt32(vendor["VendorID"]),
VendorName = Convert.ToString(vendor["VendorName"]),
Address = Convert.ToString(vendor["Address"]),
City = Convert.ToString(vendor["City"]),
EmpanelDate = (Convert.ToDateTime(vendor["EmpanelDate"])).ToString("MMM dd,yyyy"),
DCName = Convert.ToString(vendor["DCName"]),
Comment = Convert.ToString(vendor["Comment"]),
CreatedBy = Convert.ToString(vendor["CreatedBy"]),
CreatedDate = Convert.ToDateTime(vendor["CreatedDate"]),
IsActive = Convert.ToBoolean(vendor["IsActive"])
}).ToList();
}
}
}
}
return lstVendor;
}
public List<Vendor> GetVendorList(Int32? VendorId, bool? IsActive,int DCID)
{
List<Vendor> lstVendor = null;
string proc_name = ConstantsDLL.USP_GETVENDORLISTACCTODC;
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@VendorID", VendorId);
param[1] = new SqlParameter("@IsActive", IsActive);
param[2] = new SqlParameter("@DCID", DCID);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbVendor = ds.Tables[0];
lstVendor = tbVendor.AsEnumerable().Select(vendor => new Vendor
{
VendorID = Convert.ToInt32(vendor["VendorID"]),
VendorName = Convert.ToString(vendor["VendorName"]),
IsActive = Convert.ToBoolean(vendor["IsActive"])
}).ToList();
}
}
}
}
return lstVendor;
}
public DataSet CheckRecords(string EmailId, int RouteId, DateTime RequestedDate)
{
DataSet lstCabdetail = null;
string proc_name = ConstantsDLL.usp_CheckRecords;
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@UserCode", EmailId);
param[1] = new SqlParameter("@RouteId", RouteId);
param[2] = new SqlParameter("@RequestedDate", RequestedDate);
//string Query = "select * from OnDemandRequests where OndemandRequestId=(select max(OndemandRequestId) from OnDemandRequests where EmailId='" + EmailId + "')";
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
lstCabdetail = db.ExecDataSetProc(proc_name, param);
}
return lstCabdetail;
}
public DataSet GetRoosterWiseRecords(List<string> roosterNumbers)
{
var roosternumbers = string.Join(",", roosterNumbers);
DataSet Roosterdetails = null;
string proc_name = ConstantsDLL.USP_GETROOSTERWISERECORDS;
SqlParameter[] param = new SqlParameter[1];
param[0] = (roosternumbers == string.Empty) ? new SqlParameter("@Prefix", null) : new SqlParameter("@roosternumbers", roosternumbers);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, roosternumbers))
{
if (ds != null)
{
}
}
}
return Roosterdetails;
}
public List<Shift> GetShiftListByDC(int DCId, string ShiftType, string RequestType)
{
List<Shift> lstShift = null;
string proc_name = ConstantsDLL.USP_GETSHIFTDETAILBYDC;
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@DCID", DCId);
param[1] = new SqlParameter("@ShiftType", ShiftType);
param[2] = new SqlParameter("@RequestType", RequestType);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbShift = ds.Tables[0];
lstShift = tbShift.AsEnumerable().Select(shift => new Shift
{
ShiftId = Convert.ToInt32(shift["ShiftId"]),
ShiftCategory = Convert.ToString(shift["ShiftCategory"]), //corrected by piyush
ShiftType = Convert.ToString(shift["ShiftType"]),
ShiftTime = DateTime.Parse(shift["ShiftTime"].ToString()).ToString("HH:mm"),
}).ToList();
}
}
}
}
return lstShift;
}
public List<RouteMaster> GetAllRoute(Int32? RouteId,Int32? DCID,bool? IsActive)
{
try
{
List<RouteMaster> list = null;
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@RouteID", RouteId);
param[1] = new SqlParameter("@DCID",DCID);
param[2] = new SqlParameter("@IsActive",IsActive);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(ConstantsDLL.USP_GetAllRoutes, param))
{
if (ds != null && ds.Tables != null && ds.Tables[0].Rows.Count > 0)
{
list = new List<RouteMaster>();
for(int i = 0 ;i < ds.Tables[0].Rows.Count; i++)
{
var row = ds.Tables[0].Rows[i];
var rm = new RouteMaster();
rm.DPID = (string)(row["DPID"] == null || row["DPID"] == DBNull.Value ? string.Empty : row["DPID"].ToString());
rm.RouteID = (Int32)(row["RouteID"]);
rm.RouteName = (string)(row["RouteName"] ?? "");
rm.DropPoint = row["DropPoint"] == null || row["DropPoint"] == DBNull.Value ? string.Empty : row["DropPoint"].ToString();
rm.SortOrder = Convert.ToString(row["SortOrder"]);
rm.DCID = (Int32)(row["DCID"]);
rm.DCName = (string)(row["DCName"] ?? "");
rm.IsActive = Convert.ToBoolean(row["IsActive"]);
list.Add(rm);
}
return list;
}
else
{
return list;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List<RouteMaster> GetSelectedrouteAccordingToDC(RouteMaster ObjRouteMaster)
{
try
{
// string qry = "usp_selectRoute";
var list = new List<RouteMaster>();
SqlParameter[] opara =
{
new SqlParameter("@DCID",SqlDbType.Int)
};
opara[0].Value = ObjRouteMaster.DCID;
opara[0].Size = 255;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(ConstantsDLL.USP_GetSelectedrouteAccordingToDC, opara))
{
if (ds != null)
{
list = ds.Tables[0].AsEnumerable().Select(x => new RouteMaster
{
RouteID = (Int32)(x["RouteID"]),
RouteName = (string)(x["RouteName"] ?? ""),
DropPoint = (string)(x["DropPoint"] == null || x["DropPoint"] == DBNull.Value ? string.Empty : x["DropPoint"].ToString()),
SortOrder = Convert.ToString(x["SortOrder"]),
DCID = (Int32)(x["DCID"] ?? ""),
DCName = (string)(x["DCName"] ?? ""),
IsActive = (Boolean)(x["IsActive"] ?? "")
}).ToList();
return list;
}
else
{
return list;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List<DC> GetDCList()
{
try
{
// string qry = "usp_GetDCList";
var list = new List<DC>();
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(ConstantsDLL.USP_GetDCList, null))
{
if (ds != null)
{
list = ds.Tables[0].AsEnumerable().Select(x => new DC
{
DCID = (Int32)(x["DCID"]),
DCName = (string)(x["DCName"] ?? "")
}).ToList();
return list;
}
else
{
return list;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Method to get RoleType
/// </summary>
/// <param name="RoleId"></param>
/// <param name="RoleName"></param>
/// <returns></returns>
public List<RoleType> GetRoleType()
{
List<RoleType> lstroletype = null;
string proc_name = ConstantsDLL.USP_GETROLETYPE;
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, null))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbRoleType = ds.Tables[0];
lstroletype = tbRoleType.AsEnumerable().Select(roletype => new RoleType
{
RoleTypeId = Convert.ToInt32(roletype["RoleTypeId"]),
RoleTypes = Convert.ToString(roletype["RoleType"])
}).ToList();
}
}
}
}
return lstroletype;
}
/// <summary>
/// Method to Get List of Driver DETAIL
/// </summary>
/// <returns></returns>
///
public List<DriverDetail> GetDriverDetails(int DCID, bool ? IsActive)
{
List<DriverDetail> lstdriverdetails = null;
string proc_name = ConstantsDLL.usp_GetDriverDetailsAccToDC;
SqlParameter[] param = new SqlParameter[2];
//if(DriverCode==string.Empty)
//{
// param[0] = new SqlParameter("@DriverCode", null);
//}
//else
//{
// param[0] = new SqlParameter("@DriverCode", DriverCode);
//}
param[0] = new SqlParameter("@DCID", DCID);
param[1] = new SqlParameter("@IsActive", IsActive);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbDriverDetails = ds.Tables[0];
lstdriverdetails = tbDriverDetails.AsEnumerable().Select(DriverDetails => new DriverDetail
{
DriverId =Convert.ToInt32( DriverDetails["DriverId"].ToString()),
DriverCode = DriverDetails["DriverCode"].ToString(),
DriverName = DriverDetails["DriverName"].ToString(),
DCName= DriverDetails["DCName"].ToString(),
//EmpanelDate = (Convert.ToDateTime(DriverDetails["EmpanelDate"])).ToString("MMM dd,yyyy"),
//CreatedBy = DriverDetails["CreatedBy"].ToString(),
//ModifyDate = (Convert.ToDateTime(DriverDetails["ModifyDate"])).ToString("MMM dd,yyyy"),
//ModifyBy = DriverDetails["ModifyBy"].ToString(),
IsActive =Convert.ToBoolean(DriverDetails["IsActive"].ToString())
}).ToList();
}
}
}
return lstdriverdetails;
}
}
public List<OnDemandRequest> GetUserDetails(string UserCode, bool? IsActive)
{
List<OnDemandRequest> lstCabdetail = null;
string proc_name = ConstantsDLL.USP_GetUserDetails;
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserCode", UserCode);
param[1] = new SqlParameter("@IsActive", IsActive);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable tbCABDETAIL = ds.Tables[0];
lstCabdetail = tbCABDETAIL.AsEnumerable().Select(Cabdetail => new OnDemandRequest
{
OndemandRequestId = Convert.ToInt32(Cabdetail["OndemandRequestId"].ToString()),
EmailId = Cabdetail["EmailId"].ToString(),
BookingType = Cabdetail["BookingType"].ToString(),
Gender = Cabdetail["Gender"].ToString(),
RouteId =Convert.ToInt32(Cabdetail["RouteId"].ToString()),
RequestedDate = Cabdetail["RequestedDate"].ToString(),
RequestedTime =Cabdetail["RequestedTime"].ToString(),
Address = Cabdetail["Address"].ToString(),
RequestRemarks = Cabdetail["RequestRemarks"].ToString(),
IsApprovedStatus = Convert.ToInt32(Cabdetail["isapprovedstatus"]),
}).ToList();
}
}
}
}
return lstCabdetail;
}
public DataSet GetRecords(string EmailId)
{
DataSet lstCabdetail = null;
string proc_name = ConstantsDLL.usp_GetLatestRecords;
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@UserCode", EmailId);
//string Query = "select * from OnDemandRequests where OndemandRequestId=(select max(OndemandRequestId) from OnDemandRequests where EmailId='" + EmailId + "')";
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
lstCabdetail = db.ExecDataSetProc(proc_name,param);
}
return lstCabdetail;
}
//method to get rooster details on the basis of some parameter
public List<Roster> GetRoasterDetail(string Prefix)
{
List<Roster> Roosterdetails = null;
try
{
string proc_name = ConstantsDLL.USP_GETROOSTERDETAIL;
SqlParameter[] param = new SqlParameter[1];
param[0] = (Prefix == string.Empty) ? new SqlParameter("@Prefix", null) : new SqlParameter("@Prefix", Prefix);
using (SqlHelper.SqlHelper db = new SqlHelper.SqlHelper())
{
using (DataSet ds = db.ExecDataSetProc(proc_name, param))
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dtRoosterDetails = ds.Tables[0];
Roosterdetails = dtRoosterDetails.AsEnumerable().Select(RosterDetails => new Roster
{
EmployeeNo = RosterDetails["EmployeeNumber"].ToString(),
ShiftTime = RosterDetails["ShiftTimings"].ToString(),
Cabno = RosterDetails["CabNo"].ToString(),
RoosterNumber = RosterDetails["RosterNo"].ToString(),
RousterDate = Convert.ToDateTime(RosterDetails["PickupDropDate"]).ToShortDateString(),
EmployeeName = RosterDetails["EmployeeName"].ToString(),
EmployeeGender = RosterDetails["Gender"].ToString(),
Address = RosterDetails["Address"].ToString(),
Landmark = RosterDetails["LandmarkName"].ToString(),
Contact = RosterDetails["Contact"].ToString(),
PickUpOrder = RosterDetails["Pickupdroporder"].ToString(),
PickupTime = RosterDetails["PickupDropTime"].ToString(),
Vendor = RosterDetails["Vendor"].ToString(),
CabType = RosterDetails["CabType"].ToString()
}).ToList();
}
}
}
}
}
catch(Exception ex){
throw;
}
return Roosterdetails;
}