Jump to content

kat35601

Members
  • Posts

    169
  • Joined

  • Last visited

Recent Profile Visitors

2,420 profile views

kat35601's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

  1. Today they use a excel spreadsheet that someone keys this information in everyday several time a day that has a check box and note field. I would like some help in adding a checkbox and note field to this code that has the key of uomptrucknumber. and save it so when someone pulls it up that the checkbox and note field are there.
  2. uomptrucknumber is unique every-time never duplicated. the data here is only good for thirty days when the uompturcknumber falls off of the grid/list I would want to remove it from the json file. The date and time move constantly so I would not want that in the json file. This is for what we call pickups we don't handle the logistic for these other companies do and they are a lot of no shows and moving these around.All of this is onetime information never needed again.
  3. Three people see this list/grid. When the truck is ready to load we have a person that will print the truck paperwork from our erp system not from this list/grid and the note is to tell the person printing the paper work if they need two copies or a seal or lock etc..... Right now they email this information and email sometime gets over looked with this its up on there screen. As far as the table goes I think all I need is the uomptrucknumber, checkbox and note fields. I was thinking json because I was hoping it would have low overhead verse writing back to my MSSQL Plus I wanted to learn how to create,write and update a json file.
  4. I want to add a note field and a checkbox to my list/grid page and save this information to a json file using uomptrucknumber as the key. The note information will be information for the whole truck not by order. The check box will let them know that the truck is ready to have paper work printed. I have not add the note field or checkbox because I did not want to start off wrong. echo "<table>"; echo "<tr style='background:#82fbfd'>"; echo "<th>ShipDate</th>"; echo "<th>ShipMeth</th>"; echo "<th>Name</th>"; echo "<th>LoadID</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>Status</th>"; echo "<th>FGI</th>"; echo "<th>FGM</th>"; echo "<th>TotalBox</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th>"; echo "<th>Notes \n</th>"; //echo "<table>"; echo "<tr style='background:#82fbfd'>"; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } function ShowDetail($row){ if($row['ompclosed']==-1){ $theColor="#AAF0D2"; }else{ $theColor="#FFFFFF"; } if($row['ompclosed']==0 && $row['date'] < date("m/d/Y")) { $theColor="#FF0000"; } echo "<tr style='background-color :$theColor'><td>" .$row['date'] ."</td>"; echo "<td>" .$row['ompshippingmethodid'] ."</td>"; echo "<td style='text-align:left'>" .$row['cmoname'] ."</td>"; echo "<td>" .$row['uomptrucknumber'] ."</td>"; echo "<td>" .$row['uompdropsequence'] ."</td>"; echo "<td>" .$row['ompsalesorderid'] ."</td>"; echo "<td>" .$row['ompclosed'] ."</td>"; echo "<td>" .round($row['FGIqty'],0) ."</td>"; echo "<td>" .round($row['FGMqty'],0) ."</td>"; echo "<td>" .round($row['uomptotalboxcount'],0) ."</td>"; echo "<td>" . number_format($row['uompvolumetotal'],2) ."</td>"; echo "<td>" . number_format($row['ompordertotalbase'],2) ."</td>"; echo "<td style='text-align:left'>" .$row['ompordercommentstext'] ."</td></tr>" ; } //**************************************************************** while ($row = odbc_fetch_array($result)) { if($row['ompclosed']==-1){ $theColor="#AAF0D2"; }else{ $theColor="#FFFFFF"; } if($row['ompclosed']==0 && $row['date'] < $today) { $theColor="#FF0000"; } if($row['uomptrucknumber'] != $loadid) { if ($loadid !== 0) { echo "<tr style='background:#eee'> <td colspan='7'>TOTAL</td> <td>$TotalFGI</td> <td>$TotalFGM</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>$loadTotal</td> </tr>"; } $TotalFGI=0; $TotalFGM=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['uomptrucknumber']; $TotalFGI+=round($row['FGIqty'],0); $TotalFGM+=round($row['FGMqty'],0); $loadTotal +=$row['ompordertotalbase']; } echo "<tr style='background:#eee'> <td colspan='7'>TOTAL</td> <td>$TotalFGI</td> <td>$TotalFGM</td> <td>&nbsp;</td> <td>&nbsp;</td> <td>$loadTotal</td> </tr> </table>"; odbc_close($connect); ?>
  5. I did get the totals on there now how would I move them to the bottom of each truck and put them under the totals. See attached while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th>"; echo "<td><br>$loadTotal</td></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; }echo "<td>$loadTotal</td>"; echo "</table>";
  6. Yes I would like them on separate rows and I would like a total for each truck not just the first one and last one. Thanks
  7. SHow the first and last totals $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } function ShowDetail($row){ echo "<tr><td>".$row['ShipDate']."</td>"; echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>"; echo "<td>" .$row['ompSalesOrderID']."</td>"; echo "<td>" .$row['ompCustomerOrganizationID']."</td>"; echo "<td>" .round($row['QTY'],0)."</td>"; echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>"; echo "<td align = 'right'>" .$row['total']."</td>"; } //**************************************************************** while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { echo "<th>$loadTotal</th>"; } echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "</table>"; //****************************************************************** echo "<td>$loadTotal</td>"; ?>
  8. I always have trouble formatting my HTML in PHP. I want to create a total at the bottom of each uomptrucknumber to show the total for all the orders on that truck. I have included a screen shot of the output. So I want to put a row that adds up the order totals and show that total for every truck number. before the next truck number. <html> <head> <title>Loads Between Dates</title> </head> <body> <style> table { border: 1px solid #B0CBEF; border-width: 1px 0px 0px 1px; font-size: 14pt; font-family: Calibri; font-weight: 100; border-spacing: 0px; border-collapse: collapse; } TH { background-image: url(excel-2007-header-bg.gif); background-repeat: repeat-x; font-weight: normal; font-size: 17px; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; height: 17px; } TD { border: 0px; padding: 0px 4px 0px 2px; border: 1px solid #D0D7E5; border-width: 0px 1px 1px 0px; width: 100px; } TD B { border: 0px; background-color: white; font-weight: bold; } TD.heading { background-color: #E4ECF7; text-align: center; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; } </style> <?php $loadid = '0'; $loadTotal=0; $beginDate=$_POST["begin"]; $endDate=$_POST["end"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $gr_total = 0; $sql="select * from (select convert(varchar,ompRequestedShipDate,101) as ShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID , ompCustomerOrganizationID, sum(omlOrderQuantity) as QTY, UOMPVOLUMETOTAL, sum(omlExtendedPriceBase) as total from m1_kf.dbo.SalesOrders left outer join m1_kf.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID where ompClosed !=-1 and ompRequestedShipDate >= '$beginDate' and ompRequestedShipDate <= '$endDate' and ompCustomerPO not like '%void%' and UOMPTRUCKNUMBER !='' group by ompRequestedShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID,ompCustomerOrganizationID,UOMPVOLUMETOTAL) as cte order by UOMPTRUCKNUMBER ,UOMPDROPSEQUENCE,ompSalesOrderID,ompCustomerOrganizationID" ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } function ShowDetail($row){ echo "<tr><td>".$row['ShipDate']."</td>"; echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>"; echo "<td>" .$row['ompSalesOrderID']."</td>"; echo "<td>" .$row['ompCustomerOrganizationID']."</td>"; echo "<td>" .round($row['QTY'],0)."</td>"; echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>"; echo "<td align = 'right'>" .$row['total']."</td>"; } //**************************************************************** while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "</table>"; //****************************************************************** ?>
  9. How do I pass date variable to SQL Server pivot query correctly it works in the where clause but not the Pivot IN statement I think it's because the date there should be in Brackets. I also need help once that is working to display the qty data for each date column. Thanks <html> <head> </head> <body> <style> table { border: 1px solid #B0CBEF; border-width: 1px 0px 0px 1px; font-size: 14pt; font-family: Calibri; font-weight: 100; border-spacing: 0px; border-collapse: collapse; } TH { background-image: url(excel-2007-header-bg.gif); background-repeat: repeat-x; font-weight: normal; font-size: 17px; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; height: 17px; } TD { border: 0px; padding: 0px 4px 0px 2px; border: 1px solid #D0D7E5; border-width: 0px 1px 1px 0px; } TD B { border: 0px; background-color: white; font-weight: bold; } TD.heading { background-color: #E4ECF7; text-align: center; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; } </style> <?php #$date=$_POST['date']; $date1=date('m/d/Y'); $date2= Date('m/d/Y', strtotime('+2 days')); $date3=Date('m/d/Y', strtotime('+3 days')); $date4= Date('m/d/Y', strtotime('+4 days')); $date5= Date('m/d/Y', strtotime('+5 days')); $date6= Date('m/d/Y', strtotime('+6 days')); $date7= Date('m/d/Y', strtotime('+7 days')); $date8= Date('m/d/Y', strtotime('+8 days')); $date9= Date('m/d/Y', strtotime('+9 days')); $date10= Date('m/d/Y', strtotime('+10 days')); $date11= Date('m/d/Y', strtotime('+11 days')); $date12= Date('m/d/Y', strtotime('+12 days')); $date13= Date('m/d/Y', strtotime('+13 days')); $date14= Date('m/d/Y', strtotime('+14 days')); $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT * FROM ( select omlPartID , ISNULL(omlOrderQuantity,0) AS qty , CONVERT(VARCHAR, ompRequestedShipDate , 101) AS shipdate FROM M1_KF.dbo.salesorders LEFT OUTER JOIN M1_KF.dbo.salesorderlines on omlSalesOrderID=ompSalesOrderID LEFT OUTER JOIN M1_KF.dbo.PartRevisions on imrPartID=omlPartID LEFT OUTER JOIN M1_KF.dbo.Parts on impPartID=omlPartID WHERE ompclosed !=- 1 and UOMPTRUCKNUMBER !='' AND ompshippingmethodid != 'DC' and impPartClassID='FGM' and ompRequestedShipDate>=$date1 and ompRequestedShipDate<=$date14 ) as test pivot( sum(qty) for shipdate IN ($date1,$date2,$date3,$date4,$date5,$date6,$date7,$date8,$date9,$date10,$date11,$date12,$date13,$date14) ) as pvt "; echo "<table><tr>"; echo "<th>ITEM</th>"; echo "<th>".$date1."</th>"; echo "<th>".$date2."</th>"; echo "<th>".$date3."</th>"; echo "<th>".$date4."</th>"; echo "<th>".$date5."</th>"; echo "<th>".$date6."</th>"; echo "<th>".$date7."</th>"; echo "<th>".$date8."</th>"; echo "<th>".$date9."</th>"; echo "<th>".$date10."</th>"; echo "<th>".$date11."</th>"; echo "<th>".$date12."</th>"; echo "<th>".$date13."</th>"; echo "<th>".$date14."</th>"; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } while ($row = odbc_fetch_array($result)) { echo "<tr><td>" . $row['omlPartID']."</td>"; # how to output the qty data for each date column echo "<td>" .$row['qty']."</td></tr>"; } odbc_close($connect); ?> </body> </html>
  10. I want it to break like this so somewhere I have to say when it gets to the end of the state to echo the total and then start the next state.
×
×
  • 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.