Hello,
I will try to explain my question broadly so that we don’t get misunderstood and waste time.
I need to make a very conditional inquiry, but my knowledge is not enough, I will be very glad if you help.
The code below we created a long time ago with their help in a forum.
$girler = array();
$entries = "9,10";
$girler = explode(",", $entries);
$multiswitch_giris = current($girler);
$entry_stop = end($girler);
$query1 = $connection->prepare("
SELECT DISTINCT
brands.product_brand
FROM multiswitch
JOIN brands ON multiswitch.product_brand = brands.id
ORDER BY brands.product_brand ASC");
$query1->execute();
$piece = $query1->rowCount();
$brandarray = array();
if ($piece > 0) {
while ($row = $query1->fetch()){
$brandarray[] = $row['product_brand'];
}
}
$last_entries = array();
if(is_array($girler)){
foreach($girler as $giri)
{
if ($giri >= $multiswitch_giris) {
$last_entries[] = $giri;
}
}
}
unset($girler);
switch($number_subscribers){
case $number_subscribers <= 15 : $target_option= array(
"16", "8-8"); break;
case $number_subscribers <= 20 : $target_option= array(
"20", "12-12"); break;
case $number_subscribers <= 25 : $target_option= array(
"32", "16-16"); break;
case $number_subscribers <= 32 : $target_option= array(
"32", "16-16"); break;
}
foreach($brandarray as $brand) {
foreach($last_entries as $lastentr) {
unset($finite_array);
unset($cascade_array);
unset($hsecs);
unset($hsecsay);
unset($fids);
unset($ids);
$query11 = $connection->prepare("SELECT
multiswitch.product_id,
multiswitch.product_type,
multiswitch.product_input,
multiswitch.product_output,
multiswitch_type.multi_type,
brands.product_brand
FROM multiswitch
INNER JOIN multiswitch_type ON multiswitch.product_type = multiswitch_type.id
WHERE multiswitch.product_input=? AND brands.product_brand=? AND multiswitch_type.multi_type=? AND multiswitch.product_stok=?");
$query11->execute([$lastentr, $brand, 'FINITE', '1']);
$query12 = $connection->prepare("SELECT
multiswitch.product_id,
multiswitch.product_type,
multiswitch.product_input,
multiswitch.product_output,
multiswitch_type.multi_type,
brands.product_brand
FROM multiswitch
INNER JOIN multiswitch_type ON multiswitch.product_type = multiswitch_type.id
WHERE multiswitch.product_input=? AND brands.product_brand=? AND multiswitch_type.multi_type=? AND multiswitch.product_stok=?");
$query12->execute([$lastentr, $brand, 'CASCADE', '1']);
$piece = $query11->rowCount();
if ($piece > 0) {
while ($row = $query11->fetch()){
$fids[] = $row["product_id"];
$finite_array[] = $row["product_output"];
}
}
$piece = $query12->rowCount();
if ($piece > 0) {
while ($row = $query12->fetch()){
$ids[] = $row["product_id"];
$cascade_array[] = $row["product_output"];
}
}
if (!isset($finite_array) && $lastentr == $entry_stop) {
show_if_out_of_stock($brand);continue 2;
}
if (!isset($finite_array)) {
continue;
}
for ($vi = 0; $vi < count($target_option); $vi++) {
$hsecs = explode("-", $target_option[$vi]);
$hsecsay = count($hsecs);
if ($hsecsay == 1){
$varsingle = array_search($hsecs[0], $finite_array);
if ($varsingle !== false) {
show_if_the_found_product_is_one(
$total_output_found = $hsecs[0],
$found_brand = $brand, $number_subscribers,
$found_finite_id = $fids[$varsingle]
);
break 2;
}
}
if (!isset($cascade_array) && $lastentr == $entry_stop) {show_if_out_of_stock($brand);continue 3;}
if (!isset($cascade_array)) {continue 2;}
if ($hsecsay == 2){
$var2finite = array_search($hsecs[0], $finite_array);
$var2cascade = array_search($hsecs[1], $cascade_array);
if (($var2finite !== false) && ($var2cascade !== false)) {
Show_if_more_than_one_product_found(
$total_output_found = $hsecs[0]+$hsecs[1],
$found_brand = $brand, $number_subscribers,
$found_finite_id = $fids[$var2finite],
$found_cascade_ids = array($ids[$var2cascade])
);
break 2;
}
$var2finite = array_search($hsecs[1], $finite_array);
$var2cascade = array_search($hsecs[0], $cascade_array);
if (($var2finite !== false) && ($var2cascade !== false)) {
Show_if_more_than_one_product_found(
$total_output_found = $hsecs[0]+$hsecs[1],
$found_brand = $brand, $number_subscribers,
$found_finite_id = $fids[$var2finite],
$found_cascade_ids = array($ids[$var2cascade])
);
break 2;
}
}
if ($vi == (count($target_option) -1) && $lastentr == $entry_stop) {show_if_out_of_stock($brand);}
} //for
} // foreach
} // foreach...
function show_if_the_found_product_is_one($total_output_found, $found_brand, $number_subscribers, $found_finite_id) {
// HTML table for output
}
function Show_if_more_than_one_product_found($total_output_found, $found_brand, $number_subscribers, $found_finite_id, $found_cascade_ids) {
// HTML table for output
}
function show_if_out_of_stock($brand) {
// HTML table for output
}
What kind of product is queried with this code?
Product input options: 5,9,10,13,17
Output options for product subscribers: 8,12,16,20,24,32
Also this product has 2 types: CASCADE and FINITE
CASCADE feature has outputs under as inputs
FINITE feature does not have outputs under
Example:
It is necessary to use two products to achieve 64 subscriber output. The incoming signal is entered into the input of the 32 subscriber CASCADE product. The output below is connected to the input of the FINITE product with 32 subscribers, and we get a Multiswitch with 64 subscriber outputs. So, the desired number of subscribers is reached by connecting serially.
If the desired subscriber is reached with one product with this code, only one FINITE product is given. If the desired subscriber is reached with more than one product, it gives one FINITE product and gives the other products as CASCADE, so the desired subscriber is reached by connecting in series.
As in this screenshot, it lists each brand with a separate table. 105 subscriber system was requested and 108 subscriber outputs were obtained.
CASCADE serial connection system is not a good system due to the decrease in the signal in case of multiple subscribers. So now it has to have the system as I am trying to do.
Now what are the rules of the query I’m trying to do:
Thanks to the products that have developed over time, the system types have increased.
- CASCADE system
- TAP OFF system
- COMPACT system
In the picture above, there is a building with 20 floors and 40 apartments. When two cables go to each flat, 80 subscribers are reached. Cables are carried in a shaft.
On the 18th, 13th, 7th and 3rd floors, there are panels where the cables are collected. There are 15 cables on the 18th floor, 25 on the 13th floor, 13 on the 7th floor, 27 cables on the 3rd floor and this block is fed by an optical cable.
The two screenshots above are the same as the first screenshot. It just shows that the number of cables in the panels is equal. “20” or “20,20,20,20” means these two are the same.
In this screenshot, 2 Optics are selected. So it says: I want to use optics in two places on 1 shaft. In other words, optic cable will go to the 18th and 7th floors. In this case, if the CASCADE system is preferred, the Cascade output will go from the 18th floor to the 13th floor and will end here. It will not go to the 7th floor because there is another optical entrance on the 7th floor.
In this screen shot, 4 optics are selected, which means that even if the CASCADE system is preferred, the signal will not be transmitted between the panels, as optical cables will go to each panel.
In this screenshot, 3 optical cables are selected and there are 4 panels. The rule here is: fiber optic will go to other panels, excluding the panel with the fewest cables. Optic cable will go to the 18th, 13th and 3rd floors, but the optic cable will not go to the 7th floor, because the number of cables there is the least.
In this screenshot, 2 shafts are selected. In other words, cables are carried from 2 shafts in the block and all the rules are valid, since there are only 2 shafts, the products will be calculated as x2.
Note: Blocks can be multiple. The information of the blocks may differ.
Now I want to query like below:
Creating a table with all products for each brand as in the screenshot below.
For example, if I make a query with existing code:
There are 5 panels in X block. Let’s assume that there are 35, 44, 39, 52, 23 cables from the top floor to the bottom.
Desired system type is COMPACT or TAP OFF
If I query one by one, it will give the following result:
- 36 subscriber outputs are obtained by giving 10x20 CASCADE + 10x16 FINITE for 35 cables.
- 44 subscriber outputs are obtained by giving 10x24 CASCADE + 10x20 FINITE for 44 cables.
- 40 subscriber outputs are obtained by giving 10x20 CASCADE + 10x20 FINITE for 39 cables.
- 52 subscriber outputs are obtained by giving 10x32 CASCADE + 10x20 FINITE for 52 cables.
- 24 subscriber outputs are obtained by giving 10x24 FINITE for 23 cables.
I want to query this not individually, but as a whole. Query should be made according to the number of cables in each panel. CASCADE products should be FINITE instead.
Another important issue is that in a multi-block and multi-panel system, we can select all products by enclosing them in a table. However, it cannot be understood which of these products belongs to which block and which product belongs to which panel. To fix this problem, I want to write a file like below in the background.
Note: I need to add previously selected products and mandatory products to the panels without querying.
I want to receive such a file so that it would be clear which products belong to which panel.
If the desired system is CASCADE:
Again, starting from the panel on the top floor, by making a separate query for each panel, it will continue with CASCADE for the next panel and finish by giving one FINITE to the last panel.
If there is more than one optic in the shaft (I explained the optic distribution in pictures 4, 5 and 6), it will finish by giving FINITE on the panel before the optic.
Example resource
$floors_with_boards_in_the_apartment = [
"1" => "18,13,7,3",
"2" => "18,13,7,3",
"3" => "18,13,7,3",
"4" => "18,13,7,3",
"5" => "18,13,7,3",
"6" => "18,13,7,3",
"7" => "18,13,7,3",
"8" => "18,13,7,3",
"9" => "18,13,7,3",
"10" => "18,13,7,3"
];
$number_of_cables_in_panels = [
"1" => "25,20,15,20",
"2" => "25,20,15,20",
"3" => "25,20,15,20",
"4" => "65,49,80,90",
"5" => "25,20,15,20",
"6" => "25,20,15,20",
"7" => "25,20,15,20",
"8" => "25,20,15,20",
"9" => "25,20,15,20",
"10" => "25,20,15,20"
];
$number_of_optics_per_shaft =
[
"1" => "4",
"2" => "1",
"3" => "4",
"4" => "4",
"5" => "3",
"6" => "4",
"7" => "4",
"8" => "2",
"9" => "4",
"10" => "4"
];
$number_of_shafts_in_the_apartment =
[
"1" => "2",
"2" => "1",
"3" => "2",
"4" => "2",
"5" => "2",
"6" => "1",
"7" => "1",
"8" => "2",
"9" => "2",
"10" => "1"
];
$alphabet = ["1"=>"A","2"=>"B","3"=>"C","4"=>"D","5"=>"E","6"=>"F","7"=>"G","8"=>"H","9"=>"I","10"=>"J","11"=>"K","12"=>"L","13"=>"M","14"=>"N","15"=>"O","16"=>"P","17"=>"Q","18"=>"R","19"=>"S","20"=>"T","21"=>"U","22"=>"V","23"=>"W","24"=>"X","25"=>"Y","26"=>"Z"];
Thank you very much in advance
