@Override
public ArrayList<GearBoxOil> getGearBoxOils(
int currentPage,
int elementsInList,
LinkedList<ManufacturerSelected> manufacturersSelected,
LinkedList<GearBoxType> gearBoxTypeFilter,
LinkedList<OilStuff> oilStuffFilter,
HashMap<String, Boolean> viscosityFilter,
double minPrice,
double maxPrice,
double currentMinValueFilter,
double currentMaxValueFilter,
double currentMinJudgementFilter,
double currentMaxJudgementFilter,
String searchField) {
StringBuilder strManufacturerFilter = new StringBuilder();
if (manufacturersSelected.size() > 0) {
boolean bFind = false;
for (ManufacturerSelected currentMan : manufacturersSelected) {
if (currentMan.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (ManufacturerSelected currentMan : manufacturersSelected) {
if (currentMan.isSelected()) {
if (strManufacturerFilter.length() != 0) {
strManufacturerFilter.append(", ");
}
strManufacturerFilter.append(currentMan.getId());
}
}
strManufacturerFilter.insert(0, " and (man.id IN (");
strManufacturerFilter.append("))");
}
}
StringBuilder strGearBoxTypeFilter = new StringBuilder();
if (gearBoxTypeFilter.size() > 0) {
boolean bFind = false;
for (GearBoxType current : gearBoxTypeFilter) {
if (current.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (GearBoxType current : gearBoxTypeFilter) {
if (current.isSelected()) {
if (strGearBoxTypeFilter.length() != 0) {
strGearBoxTypeFilter.append(", ");
}
strGearBoxTypeFilter.append(current.getId());
}
}
strGearBoxTypeFilter.insert(0, " and (gbt.id IN (");
strGearBoxTypeFilter.append("))");
}
}
StringBuilder strOilStuffFilter = new StringBuilder();
if (oilStuffFilter.size() > 0) {
boolean bFind = false;
for (OilStuff current : oilStuffFilter) {
if (current.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (OilStuff current : oilStuffFilter) {
if (current.isSelected()) {
if (strOilStuffFilter.length() != 0) {
strOilStuffFilter.append(", ");
}
strOilStuffFilter.append(current.getId());
}
}
strOilStuffFilter.insert(0, " and (os.id IN (");
strOilStuffFilter.append("))");
}
}
StringBuilder strViscosityFilter = new StringBuilder();
if (viscosityFilter.size() > 0) {
boolean bFind = false;
for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
if (current.getValue()) {
bFind = true;
break;
}
}
if (bFind) {
for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
if (current.getValue()) {
if (strViscosityFilter.length() != 0) {
strViscosityFilter.append(", ");
}
strViscosityFilter.append("'" + current.getKey() + "'");
}
}
strViscosityFilter.insert(0, " and (go.viscosity IN (");
strViscosityFilter.append("))");
}
}
String sqlQuery =
"select * from"
+ "(select go.id as id, go.name as name, go.price as price"
+ ", go.gearboxtype AS gearboxtype, gbt.name AS gbt_name, go.oilstuff AS oilstuff, os.name AS os_name"
+ ", go.description AS description, go.judgement AS judgement, go.instock AS instock, go.discount AS discount"
+ ", go.photo AS photo, go.specification AS specification, go.viscosity AS viscosity"
+ ", go.value AS value, go.manufacturer AS manufacturer, man.name as man_name "
+ " from gearboxoils as go"
+ " LEFT JOIN oilstuff AS os ON (go.oilstuff=os.id)"
+ " LEFT JOIN gearboxtype AS gbt ON (go.gearboxtype=gbt.id)"
+ " left join manufacturer as man on go.manufacturer=man.id "
+ " where (go.price>=:minPrice) and (go.price<=:maxPrice)"
+ " and (go.value>=:currentMinValueFilter) and (go.value<=:currentMaxValueFilter) "
+ " and (go.Judgement>=:currentMinJudgementFilter) and (go.Judgement<=:currentMaxJudgementFilter)"
+ strGearBoxTypeFilter
+ strOilStuffFilter
+ strManufacturerFilter
+ strViscosityFilter
+ strSearchFilter(searchField)
+ " ORDER BY go.name ) as rez LIMIT :firstRow, :number";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("firstRow", (currentPage - 1) * elementsInList);
params.addValue("number", elementsInList);
params.addValue("minPrice", minPrice);
params.addValue("maxPrice", maxPrice);
params.addValue("currentMinValueFilter", currentMinValueFilter);
params.addValue("currentMaxValueFilter", currentMaxValueFilter);
params.addValue("currentMinJudgementFilter", currentMinJudgementFilter);
params.addValue("currentMaxJudgementFilter", currentMaxJudgementFilter);
try {
return (ArrayList<GearBoxOil>)
jdbcTemplate.query(sqlQuery, params, new GearBoxOilRowMapper());
} catch (EmptyResultDataAccessException e) {
return new ArrayList<GearBoxOil>();
}
}
@Override
public int getCountRows(
int currentPage,
int elementsInList,
LinkedList<ManufacturerSelected> manufacturersSelected,
LinkedList<GearBoxType> gearBoxTypeFilter,
LinkedList<OilStuff> oilStuffFilter,
HashMap<String, Boolean> viscosityFilter,
double minPrice,
double maxPrice,
double currentMinValueFilter,
double currentMaxValueFilter,
double currentMinJudgementFilter,
double currentMaxJudgementFilter,
String searchField) {
int result = 0;
StringBuilder strManufacturerFilter = new StringBuilder();
if (manufacturersSelected.size() > 0) {
boolean bFind = false;
for (ManufacturerSelected currentMan : manufacturersSelected) {
if (currentMan.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (ManufacturerSelected currentMan : manufacturersSelected) {
if (currentMan.isSelected()) {
if (strManufacturerFilter.length() != 0) {
strManufacturerFilter.append(", ");
}
strManufacturerFilter.append(currentMan.getId());
}
}
strManufacturerFilter.insert(0, " and (man.id IN (");
strManufacturerFilter.append("))");
}
}
StringBuilder strGearBoxTypeFilter = new StringBuilder();
if (gearBoxTypeFilter.size() > 0) {
boolean bFind = false;
for (GearBoxType current : gearBoxTypeFilter) {
if (current.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (GearBoxType current : gearBoxTypeFilter) {
if (current.isSelected()) {
if (strGearBoxTypeFilter.length() != 0) {
strGearBoxTypeFilter.append(", ");
}
strGearBoxTypeFilter.append(current.getId());
}
}
strGearBoxTypeFilter.insert(0, " and (gbt.id IN (");
strGearBoxTypeFilter.append("))");
}
}
StringBuilder strOilStuffFilter = new StringBuilder();
if (oilStuffFilter.size() > 0) {
boolean bFind = false;
for (OilStuff current : oilStuffFilter) {
if (current.isSelected()) {
bFind = true;
break;
}
}
if (bFind) {
for (OilStuff current : oilStuffFilter) {
if (current.isSelected()) {
if (strOilStuffFilter.length() != 0) {
strOilStuffFilter.append(", ");
}
strOilStuffFilter.append(current.getId());
}
}
strOilStuffFilter.insert(0, " and (os.id IN (");
strOilStuffFilter.append("))");
}
}
StringBuilder strViscosityFilter = new StringBuilder();
if (viscosityFilter.size() > 0) {
boolean bFind = false;
for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
if (current.getValue()) {
bFind = true;
break;
}
}
if (bFind) {
for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
if (current.getValue()) {
if (strViscosityFilter.length() != 0) {
strViscosityFilter.append(", ");
}
strViscosityFilter.append("'" + current.getKey() + "'");
}
}
strViscosityFilter.insert(0, " and (go.viscosity IN (");
strViscosityFilter.append("))");
}
}
String sqlQuery =
"select count(*) from"
+ "(select go.id as id from gearboxoils as go"
+ " LEFT JOIN oilstuff AS os ON (go.oilstuff=os.id)"
+ " LEFT JOIN gearboxtype AS gbt ON (go.gearboxtype=gbt.id)"
+ " left join manufacturer as man on go.manufacturer=man.id "
+ " where (go.price>=:minPrice) and (go.price<=:maxPrice)"
+ " and (go.Value>=:currentMinValueFilter) and (go.Value<=:currentMaxValueFilter)"
+ " and (go.Judgement>=:currentMinJudgementFilter) and (go.Judgement<=:currentMaxJudgementFilter)"
+ " "
+ strGearBoxTypeFilter
+ strOilStuffFilter
+ strManufacturerFilter
+ strViscosityFilter
+ strSearchFilter(searchField)
+ " ORDER BY go.name ) as rez";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("minPrice", minPrice);
params.addValue("maxPrice", maxPrice);
params.addValue("currentMinValueFilter", currentMinValueFilter);
params.addValue("currentMaxValueFilter", currentMaxValueFilter);
params.addValue("currentMinJudgementFilter", currentMinJudgementFilter);
params.addValue("currentMaxJudgementFilter", currentMaxJudgementFilter);
try {
result = jdbcTemplate.queryForObject(sqlQuery, params, Integer.class);
return result;
} catch (EmptyResultDataAccessException e) {
return 0;
}
}