Hi Willr
Thanks a lot for answer.
I have a form in which you can choose your product, you can choose your place code and the distance...now I need as result the products from the places in this distance....and the products have pictures
For the products I have a drop down solution with jquery, so you don't see a map there because of that..but it works.
Here is my code:
public function ProductsearchForm() {
$fields = new FieldList(
DropdownField::create(
'Productcategory',
'Produktkategorie'
),
DropdownField::create(
'Productsubcategory',
'Subkategorie'
),
DropdownField::create(
'Product',
'Produkt'
),
TextField::create(
'PLZ',
'PLZ'
),
TextField::create(
'Place',
'Ort'
),
TextField::create(
'Distance',
'Umkreis km'
)
);
$validator = new RequiredFields(
'Productcategory',
'Distance'
);
$actions = new FieldList(FormAction::create("submit")->setTitle("Suchen"));
return new Form($this, 'ProductsearchForm', $fields, $actions,$validator);
}
public function submit(array $data, Form $form) {
$plz = $data['PLZ'];
$ort = $data['Place'];
$distance = $data['Distance'];
$product = $data['Product'];
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('Customerproduct');
$sqlQuery->addWhere("Product = '$product'");
$sqlQuery->addLeftJoin('Customer', '"Customerproduct"."CustomerID" = "Customer"."ID"');
$sqlQuery->addWhere('PLZ = '.$plz);
$rawSQL = $sqlQuery->sql();
// Execute and return a Query object
$result = $sqlQuery->execute();
$lng = "";
$lat = "";
$userid = array();
$productid = array();
// Iterate over results
foreach($result as $row) {
$lng = $row["Longitude"] / 180 * M_PI;
$lat = $row["Latitude"] / 180 * M_PI;
$userid[$row['ID']] = $row['ID'];
};
$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('Customer');
$sqlQuery->selectField("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))* sin(".$lat."))))","Distanz");
$sqlQuery->addLeftJoin('Member','"Customer"."ID" = "Member"."ID"');
$sqlQuery->addWhere("Member.ID IN(".implode(",", $userid).")");
$sqlQuery->addLeftJoin('Customerproduct', '"Customer"."ID" = "Customerproduct"."CustomerID"');
$sqlQuery->addLeftJoin('Customerinfo','"Customer"."ID" = "Customerinfo"."HofinfophotoID"');
$sqlQuery->addWhere("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))*sin(".$lat."))) <= '".$distance."')");
$sqlQuery->setOrderBy('Distanz','ASC');
// Get the raw SQL (optional)
$rawSQL = $sqlQuery->sql();
// Execute and return a Query object
$result = $sqlQuery->execute();
$item = new ArrayList();
// Iterate over results
foreach($result as $row) {
$output =new ArrayData(array(
'Hof' => $row
));
$item->push($output);
}
$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('Customerproduct_Productphoto');
$sqlQuery->addLeftJoin('Customerproduct','"Customerproduct_Productphoto"."CustomerproductID" = "Customerproduct"."ID"');
$sqlQuery->addWhere("Customerproduct.CustomerID IN(".implode(",", $userid).")");
$rawSQL = $sqlQuery->sql();
// Execute and return a Query object
$result = $sqlQuery->execute();
$fotoid = array();
// Iterate over results
foreach($result as $row) {
$fotoid[$row['ImageID']] = $row['ImageID'];
};
$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('File');
$sqlQuery->addWhere("ID IN(".implode(",", $fotoid).")");
$rawSQL = $sqlQuery->sql();
// Execute and return a Query object
$result = $sqlQuery->execute();
$itembilder = new ArrayList();
// Iterate over results
foreach($result as $row) {
$output =new ArrayData(array(
'Productbilder' => $row
));
$itembilder->push($output);
};
$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('File');
$sqlQuery->addLeftJoin('Customerinfo','"File"."ID" = "Customerinfo"."HofinfophotoID"');
$sqlQuery->addWhere("Customerinfo.CustomerID IN(".implode(",", $userid).")");
$rawSQL = $sqlQuery->sql();
// Execute and return a Query object
$result = $sqlQuery->execute();
// Iterate over results
foreach($result as $row) {
$output =new ArrayData(array(
'Hofbild' => $row
));
$itembilder->push($output);
};
$hofdaten = array();
$hofdaten['Hofdaten'] = $item;
$bilder = array();
$bilder['Bilder'] = $itembilder;
$custom = array_merge($hofdaten,$bilder);
$count = count($item);
// Debug::show($custom);
if($count > 0) {
$this->setMessage('Success', 'Es konnten '.$count.' Treffer gefunden werden');
} else {
$this->setMessage('Error', 'Es konnten keine Treffer gefunden werden');
}
return $this->customise($custom);
}
my template looks like this, but the loop of Bilder does not work...:
<% if Hofdaten %>
<table class="searchtable">
<tr><th>Hofname</th><th>Adresse</th></tr>
<% loop Hofdaten %>
<tr height="5" bgcolor="#e9e9e9"><td colspan="2"></td></tr>
<tr bgcolor="#e9e9e9">
<td><a data-toggle="modal" href="#suchresultat$Pos" class="">-> $Hof.Hofname</a>
</td>
<td>
$Hof.Surname $Hof.FirstName<br />
$Hof.Street<br />
$Hof.PLZ $Hof.Place
</td>
</tr>
<tr height="1" bgcolor="#ccc"><td colspan="2"></td></tr>
<tr height="5" bgcolor="#e9e9e9"><td colspan="2">
<!-- Modal -->
<div class="modal hide fade" id="suchresultat$Pos">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<img src="$ThemeDir/images/logo_hofladen_klein.png" alt="Hofladen" /><br /><br />
</div>
<div class="modal-body">
<h4>$Hof.Hofname</h4>
<% loop Bilder %>
<img src="$Hofbild.Filename" />
<% end_loop %>
<p>$Hof.Surname $Hof.FirstName</p>
<p>$Hof.PLZ $Hof.Street</p>
<p>Telefon: $Hof.Phone</p>
<p>Email: $Hof.Email</p>
<p>Internet: <a href="$Hof.Homepage">$Hof.Homepage</a></p>
<br />
<br />
<h5>Aus unserem Sortiment</h5>
<p>
$Hof.Productcategory, $Hof.Productsubcategory, $Hof.Product, $Hof.Price / $Hof.Einheit<br />
$Hof.Description
<img src="$Productbilder.Filename" />
</p>
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->
</td></tr>
<% end_loop %>
</table>
<% end_if %>