Jump to content

Confused about Graphing Coordinates


winuser2003

Recommended Posts

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?

Link to comment
Share on other sites

22 hours ago, winuser2003 said:

okay, so looks like we can conclude?

Maybe not!

Take the case of John Dott. His signature is image.png.2dd7f49fa792814b0c4bc2df4f333c22.png

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, image.png.efaf6caa0fa38419ad5d624ab6b0f0a4.png which probably is rarely the case.

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

Edited by Barand
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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?

image.thumb.png.61f8508946231b0b252354f8b40d7ca9.png

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :

image.png.121e88dc5fe9804e6f5259c70e4903fd.png

Checkin time a bit off 

image.png.b420c56ab450061c716da1cc49f0af42.png

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.

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 :

image.thumb.png.34bec5123cd03d3d3c61ff350a67d12c.png

image.png.c4f070e37342d72baa218d1c9936d87d.png

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

image.thumb.png.29dfc7743b96606b870cf317df9ad4f1.png

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.