-
Notifications
You must be signed in to change notification settings - Fork 0
/
animal-list.php
122 lines (103 loc) · 4.08 KB
/
animal-list.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
<?php
include 'conn.php';
// Ortalama bulma
//echo "<pre>";
$sql = "SELECT transaction_date, AVG(milk_quantity) as milk_quantity FROM `milk`
GROUP BY transaction_date
ORDER BY `milk`.`transaction_date` DESC LIMIT 1";
$consult = $conn->query($sql);
$result = array();
while ($extractdata = $consult->fetch_assoc()) {
$result[] = $extractdata;
}
$average = json_encode(floatval($result[0]["milk_quantity"]));
$date = json_encode($result[0]["transaction_date"]);
// Ortalamanın üstünde değerleri listesini alma
$results = array();
$sql = "SELECT animal_id, conductivity ,milk_quantity FROM `milk` WHERE milk_quantity > '" . $average . "' and transaction_date = " . $date . " ORDER BY `milk`.`milk_quantity` DESC LIMIT 20 ";
$consult = $conn->query($sql);
$result = array();
while ($extractdata = $consult->fetch_assoc()) {
$result[] = $extractdata["animal_id"];
}
$results["high_yield"] = listDetail($result, $conn);
$sql = "SELECT animal_id, conductivity ,milk_quantity FROM `milk` WHERE milk_quantity < '" . $average . "' and transaction_date = " . $date . " ORDER BY `milk`.`milk_quantity` ASC LIMIT 20 ";
$consult = $conn->query($sql);
$result = array();
while ($extractdata = $consult->fetch_assoc()) {
$result[] = $extractdata["animal_id"];
}
$results["low_yield"] = listDetail($result, $conn);
$sql = "SELECT DISTINCT animal_id FROM `milk` where farm_id = 1";
$consult = $conn->query($sql);
$result = array();
while ($extractdata = $consult->fetch_assoc()) {
$result[] = $extractdata["animal_id"];
}
$sql = "SELECT DISTINCT animal_id FROM `movement` where farm_id = 1";
$consult = $conn->query($sql);
while ($extractdata = $consult->fetch_assoc()) {
if (!array_search($extractdata["animal_id"], $result))
$result[] = $extractdata["animal_id"];
}
$final_result = array();
foreach ($result as $item) {
$sql = "SELECT milk_quantity FROM `milk` WHERE animal_id = " . $item . " ORDER BY `milk`.`transaction_date` DESC";
$list = array();
$consult = $conn->query($sql);
while ($extractdata = $consult->fetch_assoc()) {
$list[] = $extractdata["milk_quantity"];
}
$last_day_milk = $list[0];
$milk_ss = std_deviation($list);
$average_milk = array_sum($list) / count($list);
if ($last_day_milk <= $average_milk - (2 * $milk_ss)) {
$final_result[] = $item;
} else {
$sql = "SELECT movement FROM `movement` WHERE animal_id = " . $item . " ORDER BY `movement`.`transaction_date` DESC";
$list = array();
$consult = $conn->query($sql);
while ($extractdata = $consult->fetch_assoc()) {
$list[] = $extractdata["movement"];
}
$last_day_milk = $list[0];
$milk_ss = std_deviation($list);
$average_milk = array_sum($list) / count($list);
if ($last_day_milk >= $average_milk + (2 * $milk_ss)) {
$final_result[] = $item;
}
}
}
$results["anomaly_list"] = listDetail($final_result, $conn);
print_r(json_encode($results, JSON_PRETTY_PRINT));
function listDetail($final_result, $conn)
{
$items = array();
foreach ($final_result as $item) {
$arr = array();
$result = array();
$sql = "SELECT * FROM `milk` WHERE animal_id = " . $item . " ORDER BY `milk`.`transaction_date` DESC LIMIT 1";
$consult = $conn->query($sql);
$result = $consult->fetch_assoc();
$arr["animal_id"] = $item;
$arr["transaction_date"] = $result["transaction_date"];
$arr["conductivity"] = $result["conductivity"];
$arr["milk_quantity"] = $result["milk_quantity"];
$sql = "SELECT * FROM `movement` WHERE animal_id = " . $item . " ORDER BY `movement`.`transaction_date` DESC LIMIT 1";
$consult = $conn->query($sql);
$result = $consult->fetch_assoc();
$arr["movement"] = $result["movement"];
$items[] = $arr;
}
return $items;
}
function std_deviation($my_arr)
{
$no_element = count($my_arr);
$var = 0.0;
$avg = array_sum($my_arr) / $no_element;
foreach ($my_arr as $i) {
$var += pow(($i - $avg), 2);
}
return (float)sqrt($var / $no_element);
}