Confused about Graphing Coordinates

[winuser2003]

1 is from the android app - the other you know where its from.  I think on his side he just took out the brackets because the DB is no longer showing the brackets from his side which is allowing the code to work. Let me know if that makes sense.

[Barand]

I changed the function to work with or without the brackets in that last version I posted, so it doesn't matter either way.

A pretty good match between those those two image versions.

[winuser2003]

ahh ok ok, should I still do an array though?

[Barand]

Why?

The function is doing that for you.

[winuser2003]

okay, so looks like we can conclude? I appreciate everything.  I will be doing more testing and keep you updated as far as progress... I do appreciate everything and I want to keep learning.  First time I ever graphed from mysql. I know 30.00 is not alot, and hopefully in the future we can work together more because I would really like to compensate you alot more for all the help you have given.... One question... are you really 71?

[Barand]

71 years 4 weeks tomorrow

My career summary

[winuser2003]

Man I hope you live another 70 plus years   thats incredible!  I am honored to be talking to you right now... I hope we work again in the future and thank you again for all the time / help / and support!  just wow!! god bless and may you continue to live healthy!

[Barand]

You're welcome. Glad to have been of help.

[winuser2003]

are you familiar with AJAX?   I need some advice for another project but if its possible, I am willing to compensate for the help and assistance.  Just mainly looking for guidance.

[Barand]

14 minutes ago, winuser2003 said:

are you familiar with AJAX?

Yes

[winuser2003]

OKay I will create a new topic about my project and let me know what you think. I appreciate you looking at this project with me, its pretty big and involves node.js. 

[Barand]

22 hours ago, winuser2003 said:

okay, so looks like we can conclude?

Maybe not!

Take the case of John Dott. His signature is

When signing, his signature has three distinct paths

1. draws the "J"
2. then "Doll"
3. Crosses the "t"s

so his pen leaves the paper twice. At present the sigs are assumed to be one continuous path from start to end, which probably is rarely the case.

The sign_x and sign_y data needs to contain something to indicate these breaks.

Edited February 19, 2020 by Barand

[winuser2003]

Good Point, let me look at that

 

Update: Your right look at the Screenshot

 

How would that be fixed code wise?

Edited February 19, 2020 by winuser2003

[Barand]

1 hour ago, winuser2003 said:

How would that be fixed code wise?

I have been thinking about since I realised it was a problem. It seems to me there are two approaches

• On input
  • The input algorithm detects the breaks and inserts a "flag" to indicate the break  EG (1,2,3,4,#,5 6 7,8)
• On output
  • Consecutive points are very close together. The output function could look for consecutive points more than N pixels apart and assume there is a break.

The first I can do nothing about, and may be more difficult (I don't know). The second I could do, but it may not be as accurate as the first option but is worth a try as it is (probably) the easier option.

As I don't have means of generating the sign_x and sign_y data myself I would need you to provide me some test data (like that used for the example in your post).

[winuser2003]

so we pulled some paper docs and did notice that signatures were primarlily on average one path meaning.... like a doctor or what I call "chicken scratch."   Over 90% of the documentation would have reference 1 path so I think we are fine the way we are.  Not many signatures have 2 names it looks like.  You made the comment "its rare"  and your right.  

Next Question:

How do I get this time stamp to look a bit more correct?

I know ( correct me if I am wrong ) there is CSS styling... but for the life of me I cannot get the timestamp to look correct.

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost", "winuser2003", "homer123", "pmapp");
$tdata = '';
$res = $conn->query("SELECT id, first_name, last_name, tech_phonenum, store_name, store_num, store_address, manager_name, robot_num, checkin_time, item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine, item_ten, item_eleven, item_twlve, item_thirteen, item_fourteen, item_fifteen, item_sixteen, item_seventenn, item_eighteen, item_nineteen, item_twenty, item_twentyone, item_twentytwo, tech_general_comment, checkout_time, manager_sign_x, manager_sign_y FROM pmvisit");
foreach ($res as $r) {
    $tdata .= "<tr><td>{$r['id']}</td>
                   <td>{$r['first_name']}</td>
                   <td>{$r['last_name']}</td>
                   <td>{$r['tech_phonenum']}</td>
                   <td>{$r['store_name']}</td>
                   <td>{$r['store_num']}</td>
                   <td>{$r['store_address']}</td>
                   <td>{$r['manager_name']}</td>
                   <td>{$r['robot_num']}</td>
                   <td>{$r['checkin_time']}</td>
                   <td>{$r['item_one']}</td>
                   <td>{$r['item_two']}</td>
                   <td>{$r['item_three']}</td>
                   <td>{$r['item_four']}</td>
                   <td>{$r['item_five']}</td>
                   <td>{$r['item_six']}</td>
                   <td>{$r['item_seven']}</td>
                   <td>{$r['item_eight']}</td>
                   <td>{$r['item_nine']}</td>
                   <td>{$r['item_ten']}</td>
                   <td>{$r['item_eleven']}</td>
                   <td>{$r['item_twlve']}</td>
                   <td>{$r['item_thirteen']}</td>
                   <td>{$r['item_fourteen']}</td>
                   <td>{$r['item_fifteen']}</td>
                   <td>{$r['item_sixteen']}</td>
                   <td>{$r['item_seventenn']}</td>
                   <td>{$r['item_eighteen']}</td>
                   <td>{$r['item_nineteen']}</td>
                   <td>{$r['item_twenty']}</td>
                   <td>{$r['item_twentyone']}</td>
                   <td>{$r['item_twentytwo']}</td>
                   <td>{$r['tech_general_comment']}</td>
                   <td>{$r['checkout_time']}</td>
                   <td>" . drawImage($r['manager_sign_x'], $r['manager_sign_y']) . "</td>\n";

}

function drawImage($xcoords, $ycoords)
{
    $xa = $xcoords[0]=='[' ? json_decode($xcoords, 1) : explode(',', $xcoords);               // put x coords into an array
    $ya = $ycoords[0]=='[' ? json_decode($ycoords, 1) : explode(',', $ycoords);               // put x coords into an array
    $w = max($xa)+1;                                                            // get the max coord values so we know the size
    $h = max($ya)+1;
    
                                                                                // define the path
    $path = "M $xa[0] $ya[0] ";                                                    // move to the first x,y position
    unset($xa[0], $ya[0]);                                                         // remove the the first items from the array
    foreach ($xa as $i => $x) {                                                    // loop through arrays
        $y = $ya[$i];                                                              // pairing the ith x with the ith y
        $path .= "L $x $y ";                                                       // define line to x y
    }
                                                                                // create svg object $w x $h to display image
    $im = "<svg width='$w' height='$h'>                                         
           <path d='$path' stroke='#000' stroke-width='2' fill='none'/>
           </svg>";
    return $im;  
}
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 60%;
    }
    th {
        background-color: blue;
        color: white;
        padding: 10px;
        text-align: left;
    }
    td {
        padding: 4px 60px;;
    }
    
</style>
</head>
<body>
<table border="1" style="width: 60%;">
    <tr><th>ID</th>
        <th>First name</th>
        <th>Last name</th>
        <th>Tech Phone Number</th>
        <th>Store Name</th>
        <th>Store Number</th>
        <th>Store Address</th>
        <th>Manager's Name</th>
        <th>Robot Number</th>
        <th>Checkin Time</th>
        <th>Bumper Tie Rod Mounting Screws</th>
        <th>Inspect/Clean Caster and Drv Wheel</th>
        <th>Inspect Bumper Damage</th>
        <th>Remove Motor Cble Ties / inspect frayed cables</th>
        <th>Inspect Screws/Parts in tray area</th>
        <th>Vaccum Cooling Air Inlets for PC</th>
        <th>Visual Cable Inspection / Connections</th>
        <th>12V Power Port Inspection</th>
        <th>Lidar Drv Belt Inspect</th>
        <th>Backdoor Alignment Check</th>
        <th>Charging Connector Contact Condition</th>
        <th>Trim lwr right cover under lidar</th>
        <th>Tighten GO Button nut</th>
        <th>Align WiFi Antenna</th>
        <th>Center Camera Alignment Check</th>
        <th>Lidar Rotation Check</th>
        <th>Googly Eyes Check</th>
        <th>Cleaned Non-Optical Cover Surfaces</th>
        <th>Clean all Optical Imaging Components</th>
        <th>Inspect Dock Connector / Height / Contact Condition</th>
        <th>Dock and Fudicals Cleaned</th>
        <th>Dock and Park Tape</th>
        <th>Technician General Comments</th>
        <th>Checkout Time</th>
        <th>Manager Signature</th>
    <tr>
    <?=$tdata?>
</table>
</body>
</html>

Next:

I would like to add arrows for filters / and maybe build a search out... would you be able to assist me with this?  I will be glad to donate more for your assistance.

 

 

[Barand]

Nothing screams "bad design" more than a set of columns in a table like these...

item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine, item_ten, item_eleven, item_twlve, item_thirteen, item_fourteen, item_fifteen, item_sixteen, item_seventenn, item_eighteen, item_nineteen, item_twenty, item_twentyone, item_twentytwo,

 

except, maybe, datetimes that are stored in "mm-dd-yyyy - hh-mm" format.

 

13 minutes ago, winuser2003 said:

td { padding: 4px 60px;; }

If you reduce the horizontal cell padding from 60px there may be room for the date on a single line.

 

16 minutes ago, winuser2003 said:

I would like to add arrows for filters / and maybe build a search out... would you be able to assist me with this?  I will be glad to donate more for your assistance.

Send me a PM with your requirements and I'll look at it.

[winuser2003]

57 minutes ago, Barand said:

Nothing screams "bad design" more than a set of columns in a table like these...

Yes yes I know, the collegue working on the mobile app did it that way, and for the life of me I have no clue why.  Probably too lazy to make it right.

57 minutes ago, Barand said:

td { padding: 4px 60px;; }

this my output with the code

?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 60%;
    }
    th {
        background-color: blue;
        color: white;
        padding: 10px;
        text-align: left;
    }
    td {
        padding: 4px 20px;;

 

Result :

Checkin time a bit off 

Checkout time a bit off....

I made the adjustment from 60 to 20, I think its too much data. Correct me please if I am wrong.

 

[Barand]

Here's my 0.02 worth.

Take out the itemXXX columns from the report. Add a clickable link to allow viewing of the items separately. Give those rows where one or more items contain a negative outcome/fault a different background color so you know which ones a worth clicking.

Of course the way you have it, this means 22 separate checks.

If done properly there is a single check...

SELECT id
     , first_name
     , last_name
     , SUM(i.pass_fail=0) as `Defect Count`
     , ...
     , manager_sign_x
     , manager_sign_y
FROM pmvisit v
     LEFT JOIN pmvisit_item i 
            ON v.id = i.visit_id AND pass_fail = 0
GROUP BY v.id

+-----------------------+                                                   +-----------------+
|     pmvisit           |                                                   |     item        |
+-----------------------+                                                   +-----------------+
| id                    |-----+                                     +-------| item_no         |
| first_name            |     |                                     |       | description     |
| last_name             |     |      +-----------------------+      |       +-----------------+
| tech_phonenum         |     |      | pmvisit_item          |      |
| store_name            |     |      +-----------------------+      |
| store_num             |     +-----<| visit_id              |      |
| store_address         |            | item_no               |>-----+
| manager_name          |            | pass_fail (1 or 0)    |
| robot_num             |            | comment   (if fail)   |
| checkin_time          |            +-----------------------+
| tech_general_comment  |
| checkout_time         |
| manager_sign_x        |
| manager_sign_y        |
+-----------------------+


Item table would contain:
+----------+--------------------------------------------------------+
| item_no  |  description                                           |
+----------+--------------------------------------------------------+
|      1   | Bumper Tie Rod Mounting Screws                         |
|      2   | Inspect/Clean Caster and Drv Wheel                     |
|      3   | Inspect Bumper Damage                                  |
|      4   | Remove Motor Cble Ties / inspect frayed cables         |
|      5   | Inspect Screws/Parts in tray area                      |
|      6   | Vaccum Cooling Air Inlets for PC                       |
|      7   | Visual Cable Inspection / Connections                  |
|      8   | 12V Power Port Inspection                              |
|      9   | Lidar Drv Belt Inspect                                 |
|     10   | Backdoor Alignment Check                               |
|     11   | Charging Connector Contact Condition                   |
|     12   | Trim lwr right cover under lidar                       |
|     13   | Tighten GO Button nut                                  |
|     14   | Align WiFi Antenna                                     |
|     15   | Center Camera Alignment Check                          |
|     16   | Lidar Rotation Check                                   |
|     17   | Googly Eyes Check                                      |
|     18   | Cleaned Non-Optical Cover Surfaces                     |
|     19   | Clean all Optical Imaging Components                   |
|     20   | Inspect Dock Connector / Height / Contact Condition    |
|     21   | Dock and Fudicals Cleaned                              |
|     22   | Dock and Park Tape                                     |
+----------+--------------------------------------------------------+

Dates should always be stored in yyyy-mm-dd format. Other formats cannot be successfully compared, so cannot be sorted, cannot be selected by date range and do not work with the dozens of date/time functions without prior conversion.

Edited February 19, 2020 by Barand

[winuser2003]

Okay let me refactor the DB labels a little better, I think he did that to test the connection to the mobile app.  I will update you when I complete this task

[winuser2003]

I took out the items and I probably messed this up bad, I am trying though... Here is what I did...

Code Change:

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost", "winuser2003", "homer123", "pmapp");
$tdata = '';
$res = $conn->query("SELECT id, first_name, last_name, SUM(i.pass_fail=0) as `Defect Count`, ..., manager_sign_x, manager_sign_y FROM pmvisit");
foreach ($res as $r) {
    $tdata .= "<tr><td>{$r['id']}</td>
                   <td>{$r['first_name']}</td>
                   <td>{$r['last_name']}</td>
                   <td>{$r['tech_phonenum']}</td>
                   <td>{$r['store_name']}</td>
                   <td>{$r['store_num']}</td>
                   <td>{$r['store_address']}</td>
                   <td>{$r['manager_name']}</td>
                   <td>{$r['robot_num']}</td>
                   <td>{$r['checkin_time']}</td>
                   <td>{$r['tech_general_comment']}</td>
                   <td>{$r['manager_comments']}</td>
                   <td>{$r['checkout_time']}</td>
                   <td>" . drawImage($r['manager_sign_x'], $r['manager_sign_y']) . "</td>\n";

}

LEFT JOIN pmvisit_item i
ON v.id = i.visit_id AND pass_fail = 0
GROUP BY v.id

function drawImage($xcoords, $ycoords)
{
    $xa = $xcoords[0]=='[' ? json_decode($xcoords, 1) : explode(',', $xcoords);               // put x coords into an array
    $ya = $ycoords[0]=='[' ? json_decode($ycoords, 1) : explode(',', $ycoords);               // put x coords into an array
    $w = max($xa)+1;                                                            // get the max coord values so we know the size
    $h = max($ya)+1;
    
                                                                                // define the path
    $path = "M $xa[0] $ya[0] ";                                                    // move to the first x,y position
    unset($xa[0], $ya[0]);                                                         // remove the the first items from the array
    foreach ($xa as $i => $x) {                                                    // loop through arrays
        $y = $ya[$i];                                                              // pairing the ith x with the ith y
        $path .= "L $x $y ";                                                       // define line to x y
    }
                                                                                // create svg object $w x $h to display image
    $im = "<svg width='$w' height='$h'>                                         
           <path d='$path' stroke='#000' stroke-width='2' fill='none'/>
           </svg>";
    return $im;  
}
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 60%;
    }
    th {
        background-color: blue;
        color: white;
        padding: 10px;
        text-align: left;
    }
    td {
        padding: 4px 20px;;
    }

</style>
</head>
<body>
<table border="1" style="width: 60%;">
    <tr><th>ID</th>
        <th>First name</th>
        <th>Last name</th>
        <th>Tech Phone Number</th>
        <th>Store Name</th>
        <th>Store Number</th>
        <th>Store Address</th>
        <th>Manager's Name</th>
        <th>Manager's Comments</th>
        <th>Robot Number</th>
        <th>Checkin Time</th>
        <th>Technician General Comments</th>
        <th>Checkout Time</th>
        <th>Manager Signature</th>
    <tr>
    <?=$tdata?>
</table>
</body>
</html>

Database Update :

I appreciate you helping me with this... your method from what I can tell from your post would make it easier... just need help getting there.

[Barand]

You still need the data for those items. However the data should be normalised so that instead of sevveral columns in the visit table they are moved to a separate table (pmvisit_item) with a separate row for each item.

At present your data is similar to this (only some of the columns and 5 items shown for brevity)

+-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+
| id  |  first_name  | last_name  | item_1     | item_2     | item_3     | item_4     | item_5     | sign_x   | sign_y    |
+-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+
|  1  | Tom          | Di Canari  | Done       | Screw loose| Done       |  Done      |Screw loose | [1,2,3,4]| [[1,2,3,4]|
|  2  | Laura        | Norder     | FUBAR      | Done       | Done       |  Done      |Screw loose | [1,2,3,4]| [[1,2,3,4]|
+-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+

As shown in the relationship diagram in my earlier post, above this data should be stored as

TABLE : pmvisit                                                      TABLE : pmvisit_item         (1 row per visit/item)
+-----+--------------+------------+----------+-----------+           +----------+-------+-------|-------------------------+
| id  |  first_name  | last_name  | sign_x   | sign_y    |           | visit_id | item  |  pass | fail_comment            |  
+-----+--------------+------------+----------+-----------+           +----------+-------+-------+-------------------------+
|  1  | Tom          | Di Canari  | [1,2,3,4]| [[1,2,3,4]|           |     1    |   1   |   1   |                         |
|  2  | Laura        | Norder     | [1,2,3,4]| [[1,2,3,4]|           |     1    |   2   |   0   | Screw loose             |
+-----+--------------+------------+----------+-----------+           |     1    |   3   |   1   |                         |
                                                                     |     1    |   4   |   1   |                         |
                                                                     |     1    |   5   |   0   | Screw loose             |
                                                                     |     2    |   1   |   0   | FUBAR                   |
                                                                     |     2    |   2   |   1   |                         |
                                                                     |     2    |   3   |   1   |                         |
                                                                     |     2    |   4   |   1   |                         |
                                                                     |     2    |   5   |   0   | Screw loose             |
                                                                     +----------+-------+-------+-------------------------+
	

 

One way to achieve this is to use you current pmvisit table as an interface and have a script which runs regularly (daily?) to extract new data and reformat to new tables that you would use from then on for reporting, querying etc

[winuser2003]

Okay just so I am on the same page - create another table under the same DB with the items I took out to reference those? correct?

[winuser2003]

Hope I did this right...

[Barand]

3 hours ago, Barand said:

separate table (pmvisit_item) with a separate row for each item.

You have put all the items in a single row again.

Should be

CREATE TABLE `visit_item` (
  `visit_id` int(11) NOT NULL,
  `item_no` int(11) NOT NULL,
  `pass_fail` tinyint(4) DEFAULT NULL,
  `comment` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`visit_id`,`item_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

There is more normalization required. In every visit record you have "store_number", "store_name", "store_loc". There should be a "store" table where the name and location are stored once foe each store. Only the store number should be in the visit record.

[winuser2003]

Completed