kat35601
-
Posts
182 -
Joined
-
Last visited
Posts posted by kat35601
-
-
well it began as 31 individual reports , all day they run this detailed reports to glean a few numbers from so I created (5 or 6 years ago) a page that gathered all of the these numbers and summed them up to one report that they can run that has all the needed information. I would like to have a file that had all the queries and just call them to the page when it's run. That would make it easier to add or adjust queries as things change. I also think I would like to connect to the server one time run all the queries instead of connection at each query. who knows maybe I can learn something new.
What has sparked this is my old apache server has come to the end of it's life and as I move things to the new server I wanted to clean them up.
-
I have 31 sql server queries that are all different but on the same server.Some of the queries are similar but I don't want to combine them I have included some of my code and it is very ugly to me. How can I shorten it? Can I use one connect to the server and run all the queries? what is the best way? at the moment all these queries are on different pages and the main page call's each one. I have included a snippet of that as well. for each one of these there is a corresponding detail report as you can see in the snippet I was hoping to do away with the 31 summary pages and combine those into one page.
<?php $name = 'removed'; $user = 'removed'; $password = 'removed'; function shipped() { echo "Shipped" . "<br/>"; global $name, $user, $password; $connect = odbc_connect($name, $user, $password); if (!$connect) { exit("Connection Failed: " . $connect); } $shipped = "select impPartClassID, sum(omlOrderQuantity) as Qty from m1_kf.dbo.SalesOrders left outer join m1_kf.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID left outer join m1_kf.dbo.parts on impPartID=omlPartID where ompClosed !=-1 and omlPartID not like '%DC%' and impPartClassID!='EXP' group by impPartClassID order by impPartClassID "; $result = odbc_exec($connect, $shipped); if (!$result) { exit("Error in SQL"); } while ($row = odbc_fetch_array($result)) { echo "<tr><td>" . $row['impPartClassID'] . "</td>"; echo "<td>" . number_format($row['Qty'], 0) . "<br></td></tr>"; } odbc_close($connect); }; /* Production */ function productionday() { global $name, $user, $password; $grandTotal = 0; $num2 = 0; $num3 = 0; $connect = odbc_connect($name, $user, $password); if (!$connect) { exit("Connection Failed: " . $connect); } $productionday = " SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE ,ISNULL(trans, 'NON') AS trans ,ISNULL(Transactions.item, Snumbers.item) AS item ,count(serial) AS qty ,tuser ,sum(ISNULL(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice,0)) as TotalPrice FROM Orbedata.dbo.SNumbers LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial left JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate()) AND trans = 'fpr' GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) ,ISNULL(trans, 'NON') ,ISNULL(Transactions.item, Snumbers.item) ,tuser ,Orbedata.dbo.transactions.qty order by tuser,item "; $result = odbc_exec($connect, $productionday); if (!$result) { exit("Error in SQL"); } while (odbc_fetch_row($result)) { $scanner = odbc_result($result, "tuser"); $Item = odbc_result($result, "item"); $Qty = odbc_result($result, "qty"); $Price = odbc_result($result, "TotalPrice"); $num = number_format($Price, 2); $grandTotal += $Price; $num2 = number_format($grandTotal, 2); $num3 += $Qty; } odbc_close($connect); echo "Day" . "<br/>"; echo "$num3"; echo "<br>"; echo "$num2"; echo "<br>"; #------------------------------------------------- function productionnight() { global $name, $user, $password; $grandTotal1 = 0; $num4 = 0; $num5 = 0; $num6 = 0; $productionnight = "SELECT -- ISNULL(trans, 'NON') AS trans ISNULL(Transactions.item, Snumbers.item) AS item ,count(serial) AS qty ,tuser ,sum(ISNULL(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice,0)) as TotalPrice FROM Orbedata.dbo.SNumbers LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial left JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID WHERE tdate >= DATEADD(minute, 1020, DATEADD(day, DATEDIFF(day, '', GETDATE())-1, '')) and tdate <= DATEADD(HH, 5, CONVERT(DATETIME, CONVERT(date, GETDATE()))) AND trans = 'fpr' GROUP BY -- ISNULL(trans, 'NON') ISNULL(Transactions.item, Snumbers.item) ,tuser ,Orbedata.dbo.transactions.qty order by tuser,item"; $connect = odbc_connect($name, $user, $password); $result = odbc_exec($connect, $productionnight); if (!$result) { exit("Error in SQL"); } while (odbc_fetch_row($result)) { $scanner = odbc_result($result, "tuser"); $Item = odbc_result($result, "item"); $Qty = odbc_result($result, "qty"); $Price = odbc_result($result, "TotalPrice"); $num4 = number_format($Price, 2); $grandTotal1 += $Price; $num5 = number_format($grandTotal1, 2); $num6 += $Qty; } odbc_close($connect); echo "NightShift" . "<br/>"; echo "$num6"; echo "<br>"; echo "$num5"; } } /* Call */ shipped(); productionday(); productionnight(); ?>
The snippet
<div class="row"> <div class="col-md-2 "> <button type ="button" class="btn btn-info btn-lg btn-block "> <a href='/dashboard/kf_orders_shipped_detail.php'> <font font color="black">Shipped! <br> <?php include 'bs_kf_orders_shipped_sum_tom.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type="button" class="btn-danger btn-lg btn-block "> <a href='/dashboard/kf_production_price1.php'> <font font color="black">Production! <br> <?php include 'bs_kf_production_price_both.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type= "button" class="btn btn-primary btn-lg btn-block "> <a href='/dashboard/kf_orders_entered_by.php'> <font font color="black">Today's Orders! <br> <?php include 'bs_kf_orders_entered_by.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type ="button" class="btn btn-success btn-lg btn-block "> <a href='/dashboard/kf_orders_open_detail.php'> <font font color="black">Open! <br> <?php include 'bs_kf_orders_open_sum_tom.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type= "button" class="btn btn-warning btn-lg btn-block"> <a href='/dashboard/kf_employee_totalsshipp.php'> <font font color="black">ON Hold! <br> <?php include 'bs_kf_employee_totals.php';?> </font> </a> </button> </div> </div> <!-- Row 2 --> <div class="row"> <div class="col-md-2 "> <button type ="button" class="btn btn-info btn-lg btn-block "> <a href='/dashboard/kf_orders_shipped_week_detail.php'> <font font color="black">Week'sShipped! <br> <?php include 'bs_kf_orders_shipped_week_sum_tom.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type= "button" class="btn-danger btn-lg btn-block "> <a href='/dashboard/kf_production_price_week.php'> <font font color="black">Week'sProduction! <br> <?php include 'bs_kf_production_price_week.php';?> </font> </a> </button> </div> <div class="col-md-2 "> <button type= "button" class="btn btn-primary btn-lg btn-block "> <a href='/dashboard/kf_orders_entered_detail_by_week.php'> <font font color="black">Week's Orders! <br> <?php include 'bs_kf_orders_entered_by_week.php';?> </font>
-
I really would like to keep the two pages separate because the second page is a phpspreadsheet page and I will have 14 of them each for a different machine.
I can make it work by opening both pages in the browser then go to the first page and select the checkboxes and submit but not call the second page. Then I can go to the second page and refresh
it and it works. But if I call the second page it errors"Warning: Undefined array key "myarray" in /var/www/html/production/laminator.php on line 54"
-
I kind of have it working with $_SESSION ARRAY. My problem if I can explain it is I have page1 that contains my list of jobs with checkboxes. When I select boxes and submit I get an error on page2 that says "Warning: Undefined array key" but then if I use the back button and select checkboxes then the page2 will show the correct results but for the checkboxes selected the first time. So how do I fix that?
Page1
<?php session_start(); echo"<form method='post' action='laminator.php'>"; echo"<table>"; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1 order by jmpPartID "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Job</th>"; echo "<th> </th>"; echo "<th>Part</th>"; echo "<th> </th>"; echo "<th>Qty</th></th></tr>"; while (odbc_fetch_array($result)) { $job=odbc_result($result,"jmpjobid"); $Item=odbc_result($result,"jmpPartID"); $Qty=odbc_result($result,"jmpProductionQuantity"); $rQty=round($Qty, 0); echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>"; echo"<td> ◃</td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$rQty</td></tr>"; } echo"</table>"; echo"<input type='submit' value='Submit' name='submit'>"; echo"</form>"; if(isset($_POST['submit'])){ $_SESSION['myarray'] = $_POST['job']; } //print_r($_SESSION['myarray']); odbc_close($connect); ?>
Page2
<?php session_start(); $array=$_SESSION['myarray']; $job= "('" . implode ( "', '", $array ) . "')"; echo $job; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" select * from (select jmmPartID,sum(jmmEstimatedQuantity) as QTY,Color,sides from (SELECT jmmPartID,jmmEstimatedQuantity, Color = (select top 1 immPartShortDescription from PartMaterials pm where pm.immMethodID=ja.jmaSourceMethodID and immPartShortDescription like'%PAPER%'), sides =(select count(*) from PartOperations po where po.imoMethodID=ja.jmapartid and po.imoWorkCenterID='HL') From JobMaterials Right Join JobOperations on JobMaterials.jmmJobID = JobOperations.jmoJobID and jmmJobAssemblyID = jmoJobAssemblyID and jmmRelatedJobOperationID = jmoJobOperationID right JOIN JobAssemblies ja On JobOperations.jmoJobID = ja.jmaJobID and jmaJobAssemblyID = jmoJobAssemblyID left join Jobs on jmpJobID = jmoJobID where jmmjobid in $job and (left(jmmPartID, 3) = 'MDF' or left(jmmPartID, 3) = 'PB.')) as ctx group by color,jmmPartID,sides ) as thv "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Part</th>"; echo "<th> </th>"; echo "<th>QTY</th>"; echo "<th> </th>"; echo "<th>Color</th>"; echo "<th>Sides</th></th></tr>"; while (odbc_fetch_row($result)) { $item=odbc_result($result,"jmmPartID"); $qty=odbc_result($result,"QTY"); $color=odbc_result($result,"Color"); $sides=odbc_result($result,"sides"); $rQty=round($qty, 0); echo"<tr>"; echo"<td>$item</td>"; echo"<td> ◃</td>"; echo "<td>$rQty</td>"; echo "<td> ▹ </td>"; echo "<td>$color</td>"; echo "<td>$sides</td></tr>"; } odbc_close($connect); ?>
-
no I am finding
A411
C715002C
1000C
-
Headed in the right direction but my JOB numbers will not always be a number sometimes the will have a letter like 29216C .
$jobs = array_map('intval', $_POST['job'] ?? [0] ); // ensure they are all numbers
-
Laugh out loud, well I don't do a lot of HTML forms. that's why I ask for help.
-
How am I doing so far?????
<?php echo"</form>"; echo" <form action='laminator.php' method='post'>"; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1 order by jmpPartID "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Job</th>"; echo "<th> </th>"; echo "<th>Part</th>"; echo "<th> </th>"; echo "<th>Qty</th></th></tr>"; while (odbc_fetch_row($result)) { $job=odbc_result($result,"jmpjobid"); $Item=odbc_result($result,"jmpPartID"); $Qty=odbc_result($result,"jmpProductionQuantity"); $rQty=round($Qty, 0); echo"<tr>"; echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>"; echo"<td> ◃</td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$rQty</td></tr>"; } echo"<form>"; echo"<input type='submit' value='Submit the form'/>"; odbc_close($connect); ?> </body> </html>
-
How do I pass the checkboxed items to a new page and query?
-
so this produce a page with a list of jobs
<?php $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" select jmpjobid,jmpPartID,jmpProductionQuantity from M1_KF.dbo.jobs where jmpProductionComplete!=-1 order by jmpPartID "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Job</th>"; echo "<th> </th>"; echo "<th>Part</th>"; echo "<th> </th>"; echo "<th>Qty</th></th></tr>"; while (odbc_fetch_row($result)) { $job=odbc_result($result,"jmpjobid"); $Item=odbc_result($result,"jmpPartID"); $Qty=odbc_result($result,"jmpProductionQuantity"); $rQty=round($Qty, 0); echo"<tr>"; echo"<td><input type='checkbox' name='job[]' value=$job>$job</td>"; echo"<td> ◃</td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$rQty</td></tr>"; } odbc_close($connect); ?>
the results look like see picture
the next pages guery looks like
select jmmPartID,sum(jmmEstimatedQuantity) as QTY,Color,sides from (SELECT jmmPartID,jmmEstimatedQuantity, Color = (select top 1 immPartShortDescription from PartMaterials pm where pm.immMethodID=ja.jmaSourceMethodID and immPartShortDescription like'%PAPER%'), sides =(select count(*) from PartOperations po where po.imoMethodID=ja.jmapartid and po.imoWorkCenterID='HL') From JobMaterials Right Join JobOperations on JobMaterials.jmmJobID = JobOperations.jmoJobID and jmmJobAssemblyID = jmoJobAssemblyID and jmmRelatedJobOperationID = jmoJobOperationID right JOIN JobAssemblies ja On JobOperations.jmoJobID = ja.jmaJobID and jmaJobAssemblyID = jmoJobAssemblyID left join Jobs on jmpJobID = jmoJobID where jmmjobid in ('29316', '29317', '29318', '29319' ) and (left(jmmPartID, 3) = 'MDF' or left(jmmPartID, 3) = 'PB.')) as ctx group by color,jmmPartID,sidesreplacing the jmmjobid numbers with the results from the checkboxed items.
-
I was using:
$local_file = 'cit_order_download'. date('mdY_hia') .'.co'; $server_file = 'CCDATA.TXT'; $curl = curl_init(); $fh = fopen($local_file , 'w'); curl_setopt($curl, CURLOPT_URL, 'ftp://tf.ftpuat.removed.com/Outbox/' . $server_file); curl_setopt($curl, CURLOPT_USERPWD, "removed"); curl_setopt($curl, CURLOPT_PORT, 22); curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); $result = curl_exec($curl); fwrite($fh, $result); fclose($fh); curl_close($curl)I installed phpseclib but I can't make it work.
I tried:
$remoteDir = "/Outbox/"; $localDir = "./"; $connection = ssh2_connect($url); if (!ssh2_auth_password($connection, $username, $password)) throw new Exception('Unable to connect.'); if(!$stream = ssh2_sftp($connection)){ die("Unable to create stream"); } if(!$dir = opendir("ssh2.sftp://{$stream}/{$remoteDir}")){ die("Could not open dir"); } $file = 'ccdata.txt'; $remote = @fopen("ssh2.sftp://{$stream}/{$remoteDir}{$file}","r"); $local = @fopen($localDir . $localFile, "w"); $read = 0; $filesize = filesize("ssh2.sftp://{$stream}/{$remoteDir}{$file}"); while ($read < $filesize && ($buffer = fread($remote, $filesize - $read))){ $read += strlen($buffer); if(fwrite($local, $buffer) === false){ echo "Unable to write to local file: $file\n"; } fclose($local); fclose($remote); }but the filesize stat throws an error that I can't seem to get around. I use php because I download the file and read it and update our erp system with the data in the file.
-
Provider changed there security settings and now I am not able to connect. I get this error "ssh2_connect(): Error starting up SSH connection(-5): Unable to exchange encryption keys "
these are the keys they except now. how to I tell ssh_connect() to use one of these
try { $connection = ssh2_connect($db['cit_host'], $port); if(!$connection){ throw new \Exception("Could not connect to $host on port $port"); } $auth = ssh2_auth_password($connection, $user, $pass); if(!$auth){ throw new \Exception("Could not authenticate with username $user and password "); } $sftp = ssh2_sftp($connection); if(!$sftp){ throw new \Exception("Could not initialize SFTP subsystem."); } $stream = fopen("ssh2.sftp://" .(int)$sftp.'//Inbox//'.$remoteFile, 'w'); if (! $stream) { echo "<td>$sftp$remoteFile</td>"; throw new \Exception("Could not open file: "); } $file = file_get_contents($remoteFile); if (fwrite($stream, $file) ===FALSE){ echo "File Not Written";
-
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.
-
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.
-
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.
-
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> </td> <td> </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> </td> <td> </td> <td>$loadTotal</td> </tr> </table>"; odbc_close($connect); ?>
-
-
The total is only on the first and the last.
-
-
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>";
-
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
-
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>"; ?>
-
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>"; //****************************************************************** ?>
-
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>
Help me clean up my code please. I have multiple SQLServer queries and would like to see a better way.
in PHP Coding Help
Posted
I am connecting to Microsoft SqlServer. Wow mac_giver thank you.I agree with the DRY and KISS methods. I new there was a better way. I will work on the initialization section first.