Jump to content

kat35601

Members
  • Posts

    182
  • Joined

  • Last visited

Recent Profile Visitors

3,314 profile views

kat35601's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

  1. 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.
  2. 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.
  3. 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>
  4. 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"
  5. 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> &#x25c3;</td>"; echo "<td>$Item</td>"; echo "<td> &#x25b9; </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> &#x25c3;</td>"; echo "<td>$rQty</td>"; echo "<td> &#x25b9; </td>"; echo "<td>$color</td>"; echo "<td>$sides</td></tr>"; } odbc_close($connect); ?>
  6. 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
  7. 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> &#x25c3;</td>"; echo "<td>$Item</td>"; echo "<td> &#x25b9; </td>"; echo "<td>$rQty</td></tr>"; } echo"<form>"; echo"<input type='submit' value='Submit the form'/>"; odbc_close($connect); ?> </body> </html>
  8. 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> &#x25c3;</td>"; echo "<td>$Item</td>"; echo "<td> &#x25b9; </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,sides replacing the jmmjobid numbers with the results from the checkboxed items.
  9. 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.
  10. 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";
  11. 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.
  12. 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.
×
×
  • 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.