public DataTable FilterListCompany(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT t.ID, t.Name AS ឈ្មោះ, t.Gender AS ភេទ, s.Name AS ឯកទេស, t.Phone AS ទូរស័ព្ទ, t.Active AS មានសកម្មភាព,")
.AppendFormat("t.[CreatedBy] AS បង្កើតដោយ,t.[CreatedDate] AS ថ្ងៃបង្កើត, t.[UpdatedBy] AS កែប្រែដោយ,t.[UpdatedDate] AS ថ្ងៃកែប្រែ ")
.AppendFormat("FROM tbTeacher t INNER JOIN tbSubject s ON t.SubjectID=s.ID ")
.AppendFormat("WHERE t.Active = '{0}'", filterEntity.Active);
if (!string.IsNullOrWhiteSpace(filterEntity.Keyword))
{
query.AppendFormat(" AND (t.Name LIKE N'%{0}%' ", filterEntity.Keyword)
.AppendFormat("OR s.Name LIKE '%{0}%') ", filterEntity.Keyword);
}
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("AND (CONVERT(DATE, t.CreatedDate) >= CONVERT(DATE, '{0}') ", filterEntity.FromDate)
.AppendFormat(" AND CONVERT(DATE, t.CreatedDate) <= CONVERT(DATE, '{0}')) ", filterEntity.ToDate);
}
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
public static DataTable FilterListBranch(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM BRANCH ")
.AppendFormat("WHERE Active = '{0}' ", filterEntity.Active);
if (!string.IsNullOrWhiteSpace(filterEntity.Keyword))
{
query.AppendFormat("AND Name LIKE N'%{0}%' ", filterEntity.Keyword);
}
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("AND ((CONVERT(DATE, CreatedDate) >= CONVERT(DATE, '{0}')) ", filterEntity.FromDate)
.AppendFormat("AND (CONVERT(DATE, CreatedDate) <= CONVERT(DATE, '{0}'))) ", filterEntity.ToDate);
}
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
if (Connect.ToDatabase().State != ConnectionState.Closed)
{
Connect.ToDatabase().Close();
}
}
return(dataTable);
}
public static DataTable FilterListProduct(FilterEntity filterEntity)
{
DataTable dataTable = new DataTable();
StringBuilder query = new StringBuilder();
query.Append("SELECT p.*, c.CategoryName FROM PRODUCT p INNER JOIN CATEGORY c ON c.ID=p.CategoryID ")
.AppendFormat("WHERE (p.ProductName LIKE N'%{0}%' OR c.CategoryName LIKE N'%{0}%' ", filterEntity.Keyword)
.AppendFormat("OR p.Price LIKE '%{0}%' ", filterEntity.Keyword)
.AppendFormat("OR p.CreatedBy LIKE '%{0}%' ", filterEntity.Keyword);
/* .AppendFormat("OR (CONVERT(DATE, MadeDate) >= CONVERT(DATE, '{0}') ", filterEntity.Keyword)
* .AppendFormat("OR (CONVERT(DATE, ExpireDate) >= CONVERT(DATE, '{0}') ", filterEntity.Keyword);*/
if (filterEntity.FromDate != null && filterEntity.ToDate != null)
{
query.AppendFormat("OR (CONVERT(DATE, p.CreatedDate) >= CONVERT(DATE, '{0}') ", filterEntity.FromDate)
.AppendFormat(" AND CONVERT(DATE, p.CreatedDate) <= CONVERT(DATE, '{0}')) ", filterEntity.ToDate);
}
query.AppendFormat("AND p.Active = '{0}')", filterEntity.Active);
try
{
SqlCommand cmd = new SqlCommand(query.ToString(), Connect.ToDatabase());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString(), @"Check you SQL",
MessageBoxButtons.RetryCancel);
}
finally
{
Connect.Close();
}
return(dataTable);
}
public void LoadData()
{
FilterEntity filterEntity = new FilterEntity();
if (rdoAllDays.Checked)
{
filterEntity.FromDate = filterEntity.ToDate = null;
}
else if (rdoByDate.Checked)
{
filterEntity.FromDate = dtpFrom.Value;
filterEntity.ToDate = dtpTo.Value;
}
if (rdoActive.Checked)
{
filterEntity.Active = true;
}
else if (rdoInActive.Checked)
{
filterEntity.Active = false;
}
filterEntity.Keyword = txtKeyword.Text;
//I added it
DataTable data = new DataTable();
data = ProductDao.FilterListProduct(filterEntity);
gridList.DataSource = data;
/*int count = data.Rows.Count;
* if (count == 0)
* {
* MessageBox.Show("Search not found!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
* }*/
// gridList.Refresh();
}
private async void UserControl_Initialized(object sender, EventArgs e)
{
progressBar.IsIndeterminate = true;
statusLabel.Content = "Application is ready";
_filterInfo = new FilterEntity()
{
Value = 8000000
};
var db = new MyShopEntities();
await Task.Run(() =>
{
Thread.Sleep(2000);
});
progressBar.IsIndeterminate = false;
categoriesComboBox.ItemsSource = db.Categories.ToList();
categoriesComboBox.SelectedIndex = 0;
filterPanel.DataContext = _filterInfo;
}
public static Control Convert(FilterEntity filter)
{
if (filter.ControlType == EnumControlType._TextBox)
{
//var c= new TextBox();
return(new UcTextBox());
}
else if (filter.ControlType == EnumControlType._NumBox)
{
var c = new NumericUpDown();
return(c);
}
else if (filter.ControlType == EnumControlType._DateTimepicker)
{
var c = new DateTimePicker();
c.Format = DateTimePickerFormat.Custom;
c.CustomFormat = "yyyy-MM-dd";
return(new UcDateTimePicker());
}
else if (filter.ControlType == EnumControlType._Combox)
{
var c = new ComboBox();
c.DataSource = filter.Items;
c.DisplayMember = "key";
c.ValueMember = "value";
return(new UcComBox());
}
else if (filter.ControlType == EnumControlType._RadioButton)
{
var c = new RadioButton();
c.Text = filter.ParamLabel;
return(c);
}
return(null);
}
private IList <IDataTableEntity> getList(FilterEntity filters)
{
using (MarsDBDataContext db = new MarsDBDataContext()) {
IList <IDataTableEntity> l = new List <IDataTableEntity>();
try
{
// this is volatile code
// Gets reservations based on selected criteria
var q = (from p in db.Reservations
join startloc in db.LOCATIONs on p.RENT_LOC equals startloc.dim_Location_id
join startCmsLoc in db.CMS_LOCATION_GROUPs on startloc.cms_location_group_id equals
startCmsLoc.cms_location_group_id
join startCmsP in db.CMS_POOLs on startCmsLoc.cms_pool_id equals startCmsP.cms_pool_id
join startCtry in db.COUNTRies on startCmsP.country equals startCtry.country1
// Return Location
join returnloc in db.LOCATIONs on p.RTRN_LOC equals returnloc.dim_Location_id
join returnCmsLoc in db.CMS_LOCATION_GROUPs on returnloc.cms_location_group_id equals
returnCmsLoc.cms_location_group_id
join returnCmsP in db.CMS_POOLs on returnCmsLoc.cms_pool_id equals returnCmsP.cms_pool_id
join returnCtry in db.COUNTRies on returnCmsP.country equals returnCtry.country1
// Car details
join carGp in db.CAR_GROUPs on p.GR_INCL_GOLDUPGR equals carGp.car_group_id
join carCs in db.CAR_CLASSes on carGp.car_class_id equals carCs.car_class_id
join carS in db.CAR_SEGMENTs on carCs.car_segment_id equals carS.car_segment_id
// ======
where
(p.COUNTRY != returnCmsP.country) // used the country from the join
&&
(filters.OwnCountry == p.COUNTRY || filters.OwnCountry == "" || filters.OwnCountry == null) &&
(startCmsP.cms_pool1 == filters.Pool || filters.Pool == "" || filters.Pool == null)
&&
(startCmsLoc.cms_location_group1 == filters.Location || filters.Location == "" ||
filters.Location == null)
&&
(p.ReservedCarGroup == filters.CarGroup || filters.CarGroup == "" ||
filters.CarGroup == null) &&
(startCtry.active) && // only corporate countries
(filters.CarSegment == carS.car_segment1 || string.IsNullOrEmpty(filters.CarSegment)) &&
(filters.CarClass == carCs.car_class1 || string.IsNullOrEmpty(filters.CarClass)) &&
(filters.CarGroup == carGp.car_group1 || string.IsNullOrEmpty(filters.CarGroup))
&&
(p.RS_ARRIVAL_DATE >= filters.ReservationStartDate &&
p.RS_ARRIVAL_DATE <= filters.ReservationEndDate) // reservation start dates
// ====== filtering for the destination(start) location
&&
(returnCmsP.country.Equals(filters.DueCountry) || string.IsNullOrEmpty(filters.DueCountry)) &&
(returnCmsP.cms_pool1.Equals(filters.DuePool) || string.IsNullOrEmpty(filters.DuePool))
&&
(returnCmsLoc.cms_location_group1.Equals(filters.DueLocationGroup) ||
string.IsNullOrEmpty(filters.DueLocationGroup))
// ======
select new { p.RES_ID_NBR, startCtry, returnCtry }).Distinct();
// selecting RES_ID_NUMBER is to ensure all entries are unique
var grp = from p in q
group p by new { o = p.startCtry.country_description, d = p.returnCtry.country_description }
into g
select new { ownwwd = g.Key.o, duewwd = g.Key.d, sum = g.Key.o.Count() };
foreach (var item in grp)
{
l.Add(new DataTableEntity
{
header = item.ownwwd,
rowDefinition = item.duewwd,
theValue = item.sum.ToString()
});
}
}
catch
{
// do nothing
}
return(l);
}
}
public override void SetFilterInfo(FilterEntity entity)
{
base.SetFilterInfo(entity);
}
public object BindModel(ControllerContext controllerContext,
ModelBindingContext bindingContext)
{
if (controllerContext == null)
{
throw new ArgumentNullException("controllerContext", "controllerContext is null.");
}
if (bindingContext == null)
{
throw new ArgumentNullException("bindingContext", "bindingContext is null.");
}
//bindingContext.ModelType
//nuc_view_search_name
var valueProvider = bindingContext.ValueProvider;
log.DebugFormat("Type valueProvider : {0}", valueProvider.GetType());
//TODO: nuc_view_search_name, colocar en constante.....
var value = valueProvider.GetValue("nuc_view_search_name");
if (value == null)
{
return(null);
}
var view_search_name = value.AttemptedValue;
if (string.IsNullOrWhiteSpace(view_search_name))
{
return(null);
}
//TODO: throw not found view ???
//Registrar excepction only
var view = _viewService.Get(view_search_name);
if (view == null)
{
return(null);
}
var searchView = view.Layout as Search;
if (searchView == null)
{
log.DebugFormat("View {0} not type search, type is {1}", view.Name, view.Layout.GetType());
return(null);
}
var filters = new List <FilterEntity>();
//TODO: Agregar los filtros que estan asociados a la view search...
//Create list FilterEntity
foreach (var item in searchView.Fields)
{
//Recuperar un valor, si existe el nombre del campo de la vista "search" en el listado
//de valores enviados desde el cliente... (formularios, string_query, etc...)
value = valueProvider.GetValue(item.Name);
log.DebugFormat("ValueProvider.GetValue({0})", item.Name);
if (value != null && !string.IsNullOrEmpty(value.AttemptedValue))
{
//log.DebugFormat("ValueProvider.AttemptedValue : {0} ", value.AttemptedValue);
//log.DebugFormat("ValueProvider.RawValue : {0} ", value.RawValue);
var filter = new FilterEntity();
filter.Field = item.Name;
filter.Operator = item.Operator;
//TODO: Convertir Value a type Field ??
filter.Value = value.AttemptedValue;
filters.Add(filter);
}
}
//TODO: Process Filter
filters = filters.ProcessFilter(view.ModelType);
return(filters);
}
public List <CarSearchDataEntity> getVehicleDetailsIdle(FilterEntity filters, CarFilterEntity cf, int nonRev, string sortExpression)
{
//
using (MarsDBDataContext db = new MarsDBDataContext()) {
List <CarSearchDataEntity> l = new List <CarSearchDataEntity>();
// Convert mileage to an integer or -1 if not a string
int mileage = Int32.TryParse(cf.Mileage, out mileage) ? mileage : -1;
// Exactly the same query as in FleetEntity but not grouped - Also CarFilters added
var q = from p in db.FLEET_EUROPE_ACTUALs
//join loc in db.LOCATIONs on p.DUEAREA equals loc.ownarea
join clg in db.CMS_LOCATION_GROUPs on p.LOC_GROUP equals clg.cms_location_group_id
join tp in db.CMS_POOLs on clg.cms_pool_id equals tp.cms_pool_id
join tc1 in db.COUNTRies on p.COUNTRY equals tc1.country1
//join tc2 in db.COUNTRies on p.DUEWWD.Substring(0, 2) equals tc2.country1
where
((p.FLEET_RAC_TTL ?? false) || (p.FLEET_CARSALES ?? false)) &&
(p.COUNTRY != p.LSTWWD.Substring(0, 2) &&
((p.OVERDUE == 1 && p.COUNTRY != p.DUEWWD.Substring(0, 2)) ||
(p.LSTOORC != "O"))) &&
(tc1.active) && // Only for Active corporate countries
(filters.OwnCountry.Equals(p.COUNTRY) || String.IsNullOrEmpty(filters.OwnCountry)) &&
(filters.DueCountry.Equals(p.LSTWWD.Substring(0, 2)) || String.IsNullOrEmpty(filters.DueCountry)) &&
(tp.cms_pool1 == filters.Pool || filters.Pool == "" || filters.Pool == null) &&
(clg.cms_location_group1 == filters.Location || filters.Location == "" || filters.Location == null) &&
(p.VC == filters.CarGroup || filters.CarGroup == "" || filters.CarGroup == null) &&
((from cg in db.CAR_GROUPs
from cc in db.CAR_CLASSes
where cg.car_class_id == cc.car_class_id &&
cg.car_group1 == p.VC
select cc.car_class1).Contains(filters.CarClass) || filters.CarClass == "" || filters.CarClass == null) &&
((from cg in db.CAR_GROUPs
from cc in db.CAR_CLASSes
from cs in db.CAR_SEGMENTs
where cs.car_segment_id == cc.car_segment_id &&
cg.car_class_id == cc.car_class_id &&
cg.car_group1 == p.VC
select cs.car_segment1).Contains(filters.CarSegment) || filters.CarSegment == "" || filters.CarSegment == null) &&
(p.UNIT.Contains(cf.Unit) || cf.Unit == "" || cf.Unit == null) &&
(p.LICENSE.Contains(cf.License) || cf.License == "" || cf.License == null) &&
(p.MODEL.Contains(cf.Model) || cf.Model == "" || cf.Model == null) &&
(p.MODDESC.Contains(cf.ModelDesc) || cf.ModelDesc == "" || cf.ModelDesc == null) &&
(p.SERIAL.Contains(cf.Vin) || cf.Vin == "" || cf.Vin == null) &&
(p.DRVNAME.Contains(cf.Name) || cf.Name == "" || cf.Name == null) &&
(p.COLOR.Contains(cf.Colour) || cf.Colour == "" || cf.Colour == null) &&
(p.LSTMLG > mileage || mileage == -1) &&
(p.DAYSREV >= nonRev) && // non Revenue selection
(p.MOVETYPE.Contains(filters.MoveType) || string.IsNullOrEmpty(filters.MoveType)) &&
(p.OPERSTAT.Contains(filters.Operstat) || string.IsNullOrEmpty(filters.Operstat))
select p;
// the sorting by column (there must be an easier way!)
switch (sortExpression)
{
case "Lstwwd": q = q.OrderBy(p => p.LSTWWD); break;
case "Lstwwd DESC": q = q.OrderByDescending(p => p.LSTWWD); break;
case "Lstdate": q = q.OrderBy(p => p.LSTDATE); break;
case "Lstdate DESC": q = q.OrderByDescending(p => p.LSTDATE); break;
case "Vc": q = q.OrderBy(p => p.VC); break;
case "Vc DESC": q = q.OrderByDescending(p => p.VC); break;
case "Unit": q = q.OrderBy(p => p.UNIT); break;
case "Unit DESC": q = q.OrderByDescending(p => p.UNIT); break;
case "License": q = q.OrderBy(p => p.LICENSE); break;
case "License DESC": q = q.OrderByDescending(p => p.LICENSE); break;
case "Model": q = q.OrderBy(p => p.MODEL); break;
case "Model DESC": q = q.OrderByDescending(p => p.MODEL); break;
case "Moddesc": q = q.OrderBy(p => p.MODDESC); break;
case "Moddesc DESC": q = q.OrderByDescending(p => p.MODDESC); break;
case "Duewwd": q = q.OrderBy(p => p.DUEWWD); break;
case "Duewwd DESC": q = q.OrderByDescending(p => p.DUEWWD); break;
case "Duedate": q = q.OrderBy(p => p.DUEDATE); break;
case "Duedate DESC": q = q.OrderByDescending(p => p.DUEDATE); break;
case "Duetime": q = q.OrderBy(p => p.DUETIME); break;
case "Duetime DESC": q = q.OrderByDescending(p => p.DUETIME); break;
case "Op": q = q.OrderBy(p => p.OPERSTAT); break;
case "Op DESC": q = q.OrderByDescending(p => p.OPERSTAT); break;
case "Mt": q = q.OrderBy(p => p.MOVETYPE); break;
case "Mt DESC": q = q.OrderByDescending(p => p.MOVETYPE); break;
case "Nr": q = q.OrderBy(p => p.DAYSREV); break;
case "Nr DESC": q = q.OrderByDescending(p => p.DAYSREV); break;
case "Driver": q = q.OrderBy(p => p.DRVNAME); break;
case "Driver DESC": q = q.OrderByDescending(p => p.DRVNAME); break;
case "Doc": q = q.OrderBy(p => p.LSTNO); break;
case "Doc DESC": q = q.OrderByDescending(p => p.LSTNO); break;
case "Lstmlg": q = q.OrderBy(p => p.LSTMLG); break;
case "Lstmlg DESC": q = q.OrderByDescending(p => p.LSTMLG); break;
case "NonRev": q = q.OrderBy(p => p.DAYSREV); break;
case "NonRev DESC": q = q.OrderByDescending(p => p.DAYSREV); break;
}
foreach (var item in q)
{
l.Add(new CarSearchDataEntity {
Lstwwd = item.LSTWWD ?? "",
Lstdate = item.LSTDATE,
Vc = item.VC ?? "",
Unit = item.UNIT ?? "",
License = item.LICENSE ?? "",
Model = item.MODEL ?? "",
Moddesc = item.MODDESC ?? "",
Duewwd = item.DUEWWD ?? "",
Duedate = item.DUEDATE,
Duetime = item.DUETIME == null ? "" : item.DUETIME.Value.ToShortTimeString(),
Op = item.OPERSTAT ?? "",
Mt = item.MOVETYPE ?? "",
Hold = item.CARHOLD1 ?? "",
Nr = item.DAYSREV == null ? "" : item.DAYSREV.ToString(),
Driver = item.DRVNAME ?? "",
Doc = item.LSTNO ?? "",
Lstmlg = (int?)item.LSTMLG ?? 0,
Remarks = "", // not used
Charged = item.RC ?? "",
Nonrev = item.DAYSREV ?? 0,
Regdate = item.IDATE == null ? "" : item.IDATE.Value.ToShortDateString(),
Ownarea = item.OWNAREA ?? "",
Remarkdate = "",
Bddays = item.BDDAYS == null ? "" : item.BDDAYS.ToString(),
Mmdays = item.MMDAYS == null ? "" : item.MMDAYS.ToString(),
Prevwwd = item.PREVWWD ?? "",
OwnCountry = item.COUNTRY ?? ""
});
}
cf.NoRecords = l.Count();
return(l);
}
}
public List <CarSearchDataEntity> getVehicleDetails(string dueCountry, int vehiclePredicament,
string ownCountry, string pool, string locationGroup,
string carSegment, string carClass, string carGroup,
string unit, string license, string model, string modelDescription,
string vin, string customerName, string colour, string mileage,
int nonRev, string operstat, string moveType,
string nonRevArg,
string sortExpression)
{
if (!string.IsNullOrEmpty(nonRevArg))
{
NonrevArgument = nonRevArg ?? "";
dueCountry = VehiclesAbroadWorker.NonRevDueCountry;
ownCountry = VehiclesAbroadWorker.NonRevOwnCountry;
}
dueCountry = processString(dueCountry);
ownCountry = processString(ownCountry);
VehiclesAbroadWorker.NonRevDueCountry = "";
VehiclesAbroadWorker.NonRevOwnCountry = "";
//// Presentation logic
pool = dueCountry == "" ? "" : processString(pool);
locationGroup = pool == "" ? "" : processString(locationGroup);
carSegment = ownCountry == "" ? "" : processString(carSegment);
carClass = carSegment == "" ? "" : processString(carClass);
carGroup = carClass == "" ? "" : processString(carGroup);
operstat = string.IsNullOrEmpty(operstat) ? "" : processString(operstat);
moveType = string.IsNullOrEmpty(moveType) ? "" : processString(moveType);
FilterEntity fe = new FilterEntity {
DueCountry = dueCountry,
VehiclePredicament = vehiclePredicament,
OwnCountry = ownCountry,
Pool = pool,
Location = locationGroup,
CarSegment = carSegment,
CarClass = carClass,
CarGroup = carGroup,
Operstat = operstat,
MoveType = moveType
};
CarFilterEntity cfe = new CarFilterEntity {
Unit = unit,
License = license,
Model = model,
ModelDesc = modelDescription,
Vin = vin,
Name = customerName,
Colour = colour,
Mileage = mileage
};
switch (vehiclePredicament)
{
case 0: // all
return(getVehicleDetailsAll(fe, cfe, nonRev, sortExpression));
case 1: // On rent owning country to foreign country
return(getVehicleDetails(fe, cfe, nonRev, sortExpression));
case 2:
return(getVehicleDetails(fe, cfe, nonRev, sortExpression));
case 3:
return(getVehicleDetailsIdle(fe, cfe, nonRev, sortExpression));
case 4:
return(getVehicleDetails(fe, cfe, nonRev, sortExpression));
case 5:
return(getVehicleDetails(fe, cfe, nonRev, sortExpression));
case 6:
return(getVehicleDetails(fe, cfe, nonRev, sortExpression));
case 7: // non rev
return(getVehicleDetailsNonrev(fe, cfe, nonRev, sortExpression));
default:
return(getVehicleDetailsIdle(fe, cfe, nonRev, sortExpression));
}
}
private IList <IDataTableEntity> getOldList(FilterEntity filters)
{
using (MarsDBDataContext db = new MarsDBDataContext())
{
IList <IDataTableEntity> l = new List <IDataTableEntity>();
try
{ // this is volatile code
var q = (from p in db.RESERVATIONS_EUROPE_ACTUALs
join tPool in db.CMS_POOLs on p.CMS_POOL equals tPool.cms_pool_id.ToString()
join tLoc in db.CMS_LOCATION_GROUPs on p.CMS_LOC_GRP equals tLoc.cms_location_group_id.ToString()
join tc1 in db.COUNTRies on p.COUNTRY equals tc1.country1
join tc2 in db.COUNTRies on p.RTRN_LOC.Substring(0, 2) equals tc2.country1
// ====== the joins for the destination(return) using rtrn_loc
join returnLocations in db.LOCATIONs on p.RTRN_LOC equals returnLocations.location1
join returnLocationGroups in db.CMS_LOCATION_GROUPs on returnLocations.cms_location_group_id equals returnLocationGroups.cms_location_group_id
join returnPools in db.CMS_POOLs on returnLocationGroups.cms_pool_id equals returnPools.cms_pool_id
join carSegment in db.CAR_SEGMENTs on p.CAR_SEGMENT equals carSegment.car_segment_id.ToString()
join carClass in db.CAR_CLASSes on p.CAR_CLASS equals carClass.car_class_id.ToString()
//// ======
where
(p.RS_ARRIVAL_DATE >= filters.ReservationStartDate && p.RS_ARRIVAL_DATE <= filters.ReservationEndDate) // reservation start dates
&&
(p.COUNTRY != returnPools.country) && // used the country from the join
(filters.OwnCountry == p.COUNTRY || filters.OwnCountry == "" || filters.OwnCountry == null) &&
(tPool.cms_pool1 == filters.Pool || filters.Pool == "" || filters.Pool == null) &&
(tLoc.cms_location_group1 == filters.Location || filters.Location == "" || filters.Location == null) &&
(p.RES_VEH_CLASS == filters.CarGroup || filters.CarGroup == "" || filters.CarGroup == null) &&
(tc1.active) && // only corporate countries
(filters.CarSegment == carSegment.car_segment1 || string.IsNullOrEmpty(filters.CarSegment)) &&
(filters.CarClass == carClass.car_class1 || string.IsNullOrEmpty(filters.CarClass)) &&
(filters.CarGroup == p.GR_INCL_GOLDUPGR || string.IsNullOrEmpty(filters.CarGroup))
// ====== filtering for the destination(start) location
&& (returnPools.country.Equals(filters.DueCountry) || string.IsNullOrEmpty(filters.DueCountry)) &&
(returnPools.cms_pool1.Equals(filters.DuePool) || string.IsNullOrEmpty(filters.DuePool)) &&
(returnLocationGroups.cms_location_group1.Equals(filters.DueLocationGroup) || string.IsNullOrEmpty(filters.DueLocationGroup))
// ======
select new { p.RES_ID_NBR, tc1, tc2 }).Distinct(); // selecting RES_ID_NUMBER is to ensure all entries are unique
var grp = from p in q
group p by new { o = p.tc1.country_description, d = p.tc2.country_description } into g
select new { ownwwd = g.Key.o, duewwd = g.Key.d, sum = g.Key.o.Count() };
//var grp = from p in q
// group p by new { o = p.tc1.country_description, d = p.tc2.country_description } into g
// select new { ownwwd = g.Key.o, duewwd = g.Key.d, sum = g.Key.o.Count() };
foreach (var item in grp)
{
l.Add(new DataTableEntity {
header = item.ownwwd, rowDefinition = item.duewwd, theValue = item.sum.ToString()
});
}
}
catch
{
// do nothing
}
return(l);
}
}
protected IQueryable <T> SkipAndTake(IQueryable <T> source, FilterEntity FilterEntity)
{
source = source.Skip(FilterEntity.Skip).Take(FilterEntity.Take);
return(source);
}
public List <ReservationMatchEntity> getVehicleDetailsWithOldQuery(string dueCountry, int vehiclePredicament,
string ownCountry, string pool, string locationGroup,
string carSegment, string carClass, string carGroup,
string unit, string license, string model, string modelDescription,
string vin, string customerName, string colour, string mileage,
string destinationPool, string destinationLocationGroup,
string reservationStartdate, string reservationEnddate,
string sortExpression)
{
dueCountry = processString(dueCountry);
ownCountry = processString(ownCountry);
//// Presentation logic
pool = ownCountry == "" ? "" : processString(pool);
locationGroup = pool == "" ? "" : processString(locationGroup);
carSegment = ownCountry == "" ? "" : processString(carSegment);
carClass = carSegment == "" ? "" : processString(carClass);
carGroup = carClass == "" ? "" : processString(carGroup);
FilterEntity fe = conditionFilters(dueCountry,
0,
ownCountry,
pool,
locationGroup,
carSegment,
carClass,
carGroup,
destinationPool,
destinationLocationGroup
);
// check the dates are valid
if (string.IsNullOrEmpty(reservationStartdate) || string.IsNullOrEmpty(reservationEnddate))
{
fe.ReservationStartDate = DateTime.Now.Date;
fe.ReservationEndDate = DateTime.Now.AddDays(2).Date;
}
else
{
fe.ReservationStartDate = Convert.ToDateTime(reservationStartdate);
fe.ReservationEndDate = Convert.ToDateTime(reservationEnddate);
}
CarFilterEntity cfe = new CarFilterEntity
{
Unit = unit,
License = license,
Model = model,
ModelDesc = modelDescription,
Vin = vin,
Name = customerName,
Colour = colour,
Mileage = mileage
};
if (cfe.ModelDesc != "ResDetails")
{
return(getReservationDetailsMatches(fe, cfe, sortExpression));
}
else
{
return(OldgetReservationDetails(fe, cfe, sortExpression));
}
}
private List <ReservationMatchEntity> OldgetReservationDetails(FilterEntity filters, CarFilterEntity cf, string sortExpression)
{
using (MarsDBDataContext db = new MarsDBDataContext())
{
try
{ // volatile db code
var q = from p in db.Reservations
join startloc in db.LOCATIONs on p.RENT_LOC equals startloc.dim_Location_id
// Return Location
join returnloc in db.LOCATIONs on p.RTRN_LOC equals returnloc.dim_Location_id
join returnCmsLoc in db.CMS_LOCATION_GROUPs on returnloc.cms_location_group_id equals
returnCmsLoc.cms_location_group_id
join returnCmsP in db.CMS_POOLs on returnCmsLoc.cms_pool_id equals returnCmsP.cms_pool_id
join returnCtry in db.COUNTRies on returnCmsP.country equals returnCtry.country1
// Car details
join carGp in db.CAR_GROUPs on p.GR_INCL_GOLDUPGR equals carGp.car_group_id
join carCs in db.CAR_CLASSes on carGp.car_class_id equals carCs.car_class_id
join carS in db.CAR_SEGMENTs on carCs.car_segment_id equals carS.car_segment_id
// ======
where
(p.COUNTRY != returnCmsP.country) // used the country from the join
&&
(filters.OwnCountry == p.COUNTRY || filters.OwnCountry == "" || filters.OwnCountry == null) &&
(returnCmsP.cms_pool1 == filters.Pool || filters.Pool == "" || filters.Pool == null)
&&
(returnCmsLoc.cms_location_group1 == filters.Location || filters.Location == "" ||
filters.Location == null)
&&
(p.ReservedCarGroup == filters.CarGroup || filters.CarGroup == "" ||
filters.CarGroup == null) &&
(returnCtry.active) && // only corporate countries
(filters.CarSegment == carS.car_segment1 || string.IsNullOrEmpty(filters.CarSegment)) &&
(filters.CarClass == carCs.car_class1 || string.IsNullOrEmpty(filters.CarClass)) &&
(filters.CarGroup == carGp.car_group1 || string.IsNullOrEmpty(filters.CarGroup))
&&
(p.RS_ARRIVAL_DATE >= filters.ReservationStartDate &&
p.RS_ARRIVAL_DATE <= filters.ReservationEndDate) // reservation start dates
&&
(returnCmsP.country.Equals(filters.DueCountry) || string.IsNullOrEmpty(filters.DueCountry)) &&
(returnCmsP.cms_pool1.Equals(filters.DuePool) || string.IsNullOrEmpty(filters.DuePool))
&&
(returnCmsLoc.cms_location_group1.Equals(filters.DueLocationGroup) ||
string.IsNullOrEmpty(filters.DueLocationGroup))
select new ReservationMatchEntity
{
ResLocation = startloc.served_by_locn,
ResGroup = carGp.car_group1,
ResCheckoutDate =
new DateTime(p.RS_ARRIVAL_DATE.Value.Year, p.RS_ARRIVAL_DATE.Value.Month,
p.RS_ARRIVAL_DATE.Value.Day, p.RS_ARRIVAL_TIME.Value.Hour,
p.RS_ARRIVAL_TIME.Value.Minute, 0),
ResCheckoutLoc = startloc.served_by_locn,
ResCheckinLoc = returnloc.served_by_locn,
ResId = p.RES_ID_NBR,
ResNoDaysUntilCheckout = p.CO_DAYS.ToString(),
ResNoDaysReserved = ((int)p.RES_DAYS).ToString(),
ResDriverName = p.CUST_NAME,
Matches = ""
};
switch (sortExpression)
{
case "ResLocation": q = q.OrderBy(p => p.ResLocation); break;
case "ResLocation DESC": q = q.OrderByDescending(p => p.ResLocation); break;
case "ResGroup": q = q.OrderBy(p => p.ResGroup); break;
case "ResGroup DESC": q = q.OrderByDescending(p => p.ResGroup); break;
case "ResCheckoutDate": q = q.OrderBy(p => p.ResCheckoutDate); break;
case "ResCheckoutDate DESC": q = q.OrderByDescending(p => p.ResCheckoutDate); break;
case "ResCheckinLoc": q = q.OrderBy(p => p.ResCheckinLoc); break;
case "ResCheckinLoc DESC": q = q.OrderByDescending(p => p.ResCheckinLoc); break;
case "ResNoDaysUntilCheckout": q = q.OrderBy(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysUntilCheckout DESC": q = q.OrderByDescending(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysReserved": q = q.OrderBy(p => p.ResNoDaysReserved); break;
case "ResNoDaysReserved DESC": q = q.OrderByDescending(p => p.ResNoDaysReserved); break;
case "ResDriverName": q = q.OrderBy(p => p.ResDriverName); break;
case "ResDriverName DESC": q = q.OrderByDescending(p => p.ResDriverName); break;
case "ResId": q = q.OrderBy(p => p.ResId); break;
case "ResId DESC": q = q.OrderByDescending(p => p.ResId); break;
case "Matches": q = q.OrderBy(p => p.Matches); break;
case "Matches DESC": q = q.OrderByDescending(p => p.Matches); break;
default: q = q.OrderBy(p => p.ResNoDaysUntilCheckout).ThenBy(p => p.ResLocation); break;
}
return(q.ToList());
}
catch (Exception ex)
{
// _logger.Error("Exception thrown in ReservationDetails Model, exception = " + ex);
return(new List <ReservationMatchEntity>());
}
}
}
private List <ReservationMatchEntity> getReservationDetailsMatches(FilterEntity filters, CarFilterEntity cf, string sortExpression)
{
return(new ReservationFleetRepository().GetList(filters, cf, sortExpression).ToList());
}