Jump to content

kat35601

Members
 View Profile  See their activity

  • Posts

    182

  • Joined

  • Last visited

 Content Type 

Everything posted by kat35601

  1. kat35601
    I have a web page that I get data from my sql server and I want to link to a report from the output of my main report. I have included a picture of my report and circled a couple of the items I want the user to click on and then show a report with details of what that summary number is. so in the link I will need to pass the schedule number and the column that they click on to the sub report. below is my code <html> <head> <title>Status Screen</title> </head> <body> <p> Status Screen </p> <?php $connect =odbc_connect("Removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT distinct WIP_master.uompScheduleNumber ,CASE WIP_master.uompScheduleColor WHEN 'NEON' then 'ORANGERED' WHEN 'PINK' THEN 'FUCHSIA' ELSE WIP_master.uompScheduleColor end AS COLOR ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (WIP_master1.itemtype ='DR' and WIP_master1.status = '2') ) as PRT_DR ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='DDF' ) as PRT_DDF ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='FR' ) as PRT_FR ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype not in ('DR','DDF','FR') ) as PRT_Other ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (WIP_master1.itemtype ='DR' and WIP_master1.status = '3') ) as DR ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='DDF' ) as DDF ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='FR' ) as FR ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype not in ('DR','DDF','FR') ) as Other /***************************************************************************************************/ ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and (WIP_master1.itemtype ='DR' and WIP_master1.status = '4') ) as DR_PRP ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='DDF' ) as DDF_PRP ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='FR' ) as FR_PRP ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype not in ('DR','DDF','FR') ) as Other_PRP /******************************************************************************************************/ ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '9' ) as Shortage ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '10' ) as Shortage_Prt ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '11' ) as Red ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '12' ) as Neon ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '20' ) as QC_Hold ,s1.UOMPBUILDDATE FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations ON WIP_master.location = WIP_locations.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status ON WIP_master.STATUS = WIP_status.STATUS left join m1_DC.dbo.SalesOrders s1 on s1.UOMPSCHEDULENUMBER=WIP_master.uompScheduleNumber and s1.ompSalesOrderID=WIP_master.ompSalesOrderID where s1.UOMPBUILDDATE >= GETDATE()-7 order by WIP_master.uompScheduleNumber " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table cellpadding='pixels'cellspacing='10'><tr>"; echo "<th>ScheduleNumber</th>"; echo "<th>PRT_DR</th>"; echo "<th>PRT_DDF</th>"; echo "<th>PRT_FR</th>"; echo "<th>ScanDR</th>"; echo "<th>ScanDDF</th>"; echo "<th>ScanFR</th>"; echo "<th>PrpDR</th>"; echo "<th>PrpDDF</th>"; echo "<th>PrpFR</th>"; while (odbc_fetch_row($result)) { $bgcolor = odbc_result($result, "COLOR"); $uompScheduleNumber=odbc_result($result,"uompScheduleNumber"); $uompScheduleColor=odbc_result($result,"Color"); $PRT_DR=odbc_result($result,"PRT_DR"); $PRT_DDF=odbc_result($result,"PRT_DDF"); $PRT_FR=odbc_result($result,"PRT_FR"); $DR=odbc_result($result,"DR"); $DDF=odbc_result($result,"DDF"); $FR=odbc_result($result,"FR"); $DR_PRP=odbc_result($result,"DR_PRP"); $DDF_PRP=odbc_result($result,"DDF_PRP"); $FR_PRP=odbc_result($result,"FR_PRP"); $Other=odbc_result($result,"Other"); echo "<tr><td bgcolor=$bgcolor >$uompScheduleNumber</td>"; echo "<td style='text-align:center'>$PRT_DR</td>"; echo "<td style='text-align:center'>$PRT_DDF</td>"; echo "<td style='text-align:center'>$PRT_FR</td>"; echo "<td style='text-align:center'>$DR</td>"; echo "<td style='text-align:center'>$DDF</td>"; echo "<td style='text-align:center'>$FR</td>"; echo "<td style='text-align:center'>$DR_PRP</td>"; echo "<td style='text-align:center'>$DDF_PRP</td>"; echo "<td style='text-align:center'>$FR_PRP</td>"; } odbc_close($connect); ?> </body> </html>
  2. kat35601
    Ok error reporting is on and I have some what working page. Just know working update box. but I think I am close. so whats next please. <html> <head> <title>Status Screen</title> </head> <body> <p> Status Screen </p> <?php $schedule=$_POST["schedule"]; $connect =odbc_connect("orbe","sa","Jazz2008"); $serial=''; $short = ""; $red = ""; $prepaint =""; if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT uompscheduleColor,jmaPartID,ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,uomlStyleGroup FROM WIP_master where uompScheduleNumber='$schedule' and status='2' and itemtype <> 'FR' Order By uomlSorP,uomlStyleGroup,itemtype,jmaPartID " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>$schedule<th><tr>"; echo "<th>PartID</th>"; echo "<th>OrderID</th>"; echo "<th>SorP</th>"; echo "<th>Serial</th>"; echo "<th>Rail</th>"; echo "<th>Panel</th>"; echo "<th>Description</th>"; echo "<th>ItemType</th>"; echo "<th>Stile</th>"; echo "<th>Short</th>"; echo "<th>Red</th>"; $sql_short="update wip_master set status='9' where uompScheduleNumber='$schedule' and serial='$serial'"; while (odbc_fetch_row($result)) { $bgcolor= odbc_result($result, "uompscheduleColor"); $jmaPartID= odbc_result($result, "jmaPartID"); $ompSalesOrderID= odbc_result($result, "ompSalesOrderID"); $uomlSorP= odbc_result($result, "uomlSorP"); $serial= odbc_result($result, "serial"); $rail= odbc_result($result, "rail"); $panel= odbc_result($result, "panel"); $stile= odbc_result($result, "stile"); $upsdescription= odbc_result($result, "upsdescription"); $itemtype = odbc_result($result, "itemtype"); $uomlStyleGroup = odbc_result($result, "uomlStyleGroup"); echo "<tr><td bgcolor=$bgcolor >$jmaPartID</td>"; echo "<td style='text-align:center'>$ompSalesOrderID</td>"; echo "<td style='text-align:center'>$uomlSorP</td>"; echo "<td style='text-align:center'>$serial</td>"; echo "<td style='text-align:center'> $panel</td>"; echo "<td style='text-align:center'> $stile</td>"; echo "<td style='text-align:center'> $upsdescription</td>"; echo "<td style='text-align:center'> $itemtype</td>"; echo "<td style='text-align:center'>$uomlStyleGroup</td>"; echo "<td style='text-align:center'>$short</td>"; echo "<td style='text-align:center'>$red</td>"; echo "<td style='text-align:center'>$prepaint</td>"; echo"<td><input class='cb' type='checkbox' name='short[]' value='$serial'> </td>"; } if(isset($_POST['short'])){ $short = 1; } $update=odbc_exec($connect,$sql_short); if(!$result){ exit("Error in SQL"); } ?> </body> </html>
  3. kat35601
    I still can't get it to update with the check box. What step am I missing? <html> <head> <title>Status Screen</title> </head> <body> <p> Status Screen </p> <?php $schedule=$_POST["schedule"]; $connect =odbc_connect("orbe","sa","Jazz2008"); $serial=''; $short = ""; $red = ""; $prepaint =""; if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT uompscheduleColor,jmaPartID,ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,uomlStyleGroup FROM WIP_master where uompScheduleNumber='$schedule' and status='2' and itemtype <> 'FR' Order By uomlSorP,uomlStyleGroup,itemtype,jmaPartID " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>$schedule<th><tr>"; echo "<th>PartID</th>"; echo "<th>OrderID</th>"; echo "<th>SorP</th>"; echo "<th>Serial</th>"; echo "<th>Rail</th>"; echo "<th>Panel</th>"; echo "<th>Description</th>"; echo "<th>ItemType</th>"; echo "<th>Stile</th>"; echo "<th>Short</th>"; echo "<th>Red</th>"; while (odbc_fetch_row($result)) { $bgcolor= odbc_result($result, "uompscheduleColor"); $jmaPartID= odbc_result($result, "jmaPartID"); $ompSalesOrderID= odbc_result($result, "ompSalesOrderID"); $uomlSorP= odbc_result($result, "uomlSorP"); $serial= odbc_result($result, "serial"); $rail= odbc_result($result, "rail"); $panel= odbc_result($result, "panel"); $stile= odbc_result($result, "stile"); $upsdescription= odbc_result($result, "upsdescription"); $itemtype = odbc_result($result, "itemtype"); $uomlStyleGroup = odbc_result($result, "uomlStyleGroup"); echo "<tr><td bgcolor=$bgcolor >$jmaPartID</td>"; echo "<td style='text-align:center'>$ompSalesOrderID</td>"; echo "<td style='text-align:center'>$uomlSorP</td>"; echo "<td style='text-align:center'>$serial</td>"; echo "<td style='text-align:center'> $panel</td>"; echo "<td style='text-align:center'> $stile</td>"; echo "<td style='text-align:center'> $upsdescription</td>"; echo "<td style='text-align:center'> $itemtype</td>"; echo "<td style='text-align:center'>$uomlStyleGroup</td>"; echo "<td style='text-align:center'>$short</td>"; echo "<td style='text-align:center'>$red</td>"; echo "<td style='text-align:center'>$prepaint</td>"; echo"<td><input class='cb' type='checkbox' name='short[]' value='$serial'> </td>"; if(isset($_POST['short'])) $short = 1; $update=odbc_exec($connect,$sql_short); $sql_short="update wip_master set status='9' where uompScheduleNumber='$schedule' and serial='$serial'"; if(!$update); { exit("Error in SQL"); } odbc_close($connect); ?> </body> </html>
  4. kat35601
    echo"<td><input class='cb' type='checkbox' name='short[]' value='$serial' </input></td>"; if(isset($_POST['short'])) { $short = 1; $update=odbc_exec($connect,$sql_short); $sql_short="update wip_master set status='3' where uompScheduleNumber='$schedule' and serial='$serial'"; if(!$update){ exit("Error in SQL"); I can't get it figured out please point out my errors...Thanks
  5. kat35601
    echo "<td><input class='cb' type = 'checkbox' name='Scan[]' id='$serial' value='<?echo ['id']; ?>' </input></td>"; I want to use serial as the id but I did not do it right.What am I doing wrong here.thanks for the help
  6. kat35601
    I need to have a check box on each row that when it is check it will update the status field on that record to '10' (see comments in code) <html> <head> <title>Status Screen</title> </head> <body> <p> Schedule Items Not Scanned </p> <?php $schedule=$_POST["schedule"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT uompscheduleColor,jmaPartID,ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype ,case uomlStyleGroup When 'Slab' Then 'Veneer' else uomlStyleGroup end as 'uomlStyleGroup' FROM WIP_master where uompScheduleNumber='$schedule' and status='2' and itemtype in ('DR','DDF') and jmaPartID not like '%311%' Order By uomlSorP,uomlStyleGroup,itemtype,jmaPartID"; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } $data = ''; while (odbc_fetch_row($result)) { $bgcolor= odbc_result($result, "uompscheduleColor"); $jmaPartID= odbc_result($result, "jmaPartID"); $ompSalesOrderID= odbc_result($result, "ompSalesOrderID"); $uomlSorP= odbc_result($result, "uomlSorP"); $serial= odbc_result($result, "serial"); $rail= odbc_result($result, "rail"); $panel= odbc_result($result, "panel"); $stile= odbc_result($result, "stile"); $upsdescription= odbc_result($result, "upsdescription"); $itemtype = odbc_result($result, "itemtype"); $uomlStyleGroup = odbc_result($result, "uomlStyleGroup"); // I want to add a check box that is on every row that when I check it then it runs this sql statment to update the status // of the item and then change the color of the row. // update wip_master set status = '10' where uompScheduleNumber='$schedule' and serial="""the serial number of the Item I clicked the check box on"""" // Then the change the color of the row to red where status='10' $data .= "<tr><td>$jmaPartID</td>"; $data .= "<td style='text-align:center'>$ompSalesOrderID</td>"; $data .= "<td style='text-align:center'>$uomlSorP</td>"; $data .= "<td style='text-align:center'>$serial</td>"; $data .= "<td style='text-align:center'> $panel</td>"; $data .= "<td style='text-align:center'> $stile</td>"; $data .= "<td style='text-align:center'> $rail</td>"; $data .= "<td style='text-align:center'> $upsdescription</td>"; $data .= "<td style='text-align:center'> $itemtype</td>"; $data .= "<td style='text-align:center'>$uomlStyleGroup</td>"; } echo "<table><tr>"; echo date('l jS \of F Y h:i:s A'); echo "<th bgcolor=$bgcolor>$schedule<th><tr>"; echo "<th>PartID</th>"; echo "<th>OrderID</th>"; echo "<th>SorP</th>"; echo "<th>Serial</th>"; echo "<th>Panel</th>"; echo "<th>Stile</th>"; echo "<th>Rail</th>"; echo "<th>Description</th>"; echo "<th>ItemType</th>"; echo "<th>Style</th>"; echo $data; odbc_close($connect); ?> </body> </html>
  7. kat35601
    I have this report that I have written and it looks fine on the screen but the printed output is to big for the page and very ugly. How would I reformat it so it prints better. <html> <head> <title>Load Report</title> </head> <body> <h1>Load Report</h1> <?php $load=$_POST["Load_ID"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT m1_DC.dbo.SalesOrders.UOMPDROPSEQUENCE ,m1_DC.dbo.organizationlocations.cmlPhoneNumber ,m1_DC.dbo.organizationlocations.cmlName ,m1_DC.dbo.organizationlocations.UCMLSTOREHOURSTXT ,m1_DC.dbo.SalesOrders.OMPSHIPPINGMETHODID ,m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER ,m1_DC.dbo.SalesOrders.ompCustomerPO ,m1_DC.dbo.SalesOrders.UOMPTOTALBOXCOUNT ,m1_DC.dbo.SalesOrders.UOMPVOLUMETOTAL ,m1_DC.dbo.organizationlocations.CMLADDRESSLINE1 ,m1_DC.dbo.organizationlocations.CMLADDRESSLINE2 ,m1_DC.dbo.organizationlocations.CMLCITY ,m1_DC.dbo.organizationlocations.CMLSTATE ,m1_DC.dbo.organizationlocations.CMLPOSTCODE ,m1_DC.dbo.SalesOrders.OMPREQUESTEDSHIPDATE ,m1_DC.dbo.SalesOrders.ompSalesOrderID ,m1_DC.dbo.SalesOrders.ompOrderTotalBase ,convert(varchar(10),m1_DC.dbo.SalesOrders.uompbuilddate,110) as BuildDate FROM m1_DC.dbo.SalesOrders LEFT OUTER JOIN m1_DC.dbo.organizationlocations ON ompshiporganizationid = cmlorganizationid AND ompshiplocationid = cmllocationid WHERE m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER='$load' Union All SELECT m1_KF.dbo.SalesOrders.UOMPDROPSEQUENCE ,m1_KF.dbo.organizationlocations.cmlPhoneNumber ,m1_KF.dbo.organizationlocations.cmlName ,m1_KF.dbo.organizationlocations.UCMLSTOREHOURSTXT ,m1_KF.dbo.SalesOrders.OMPSHIPPINGMETHODID ,m1_KF.dbo.SalesOrders.UOMPTRUCKNUMBER ,m1_KF.dbo.SalesOrders.ompCustomerPO ,m1_KF.dbo.SalesOrders.UOMPTOTALBOXCOUNT ,m1_KF.dbo.SalesOrders.UOMPVOLUMETOTAL ,m1_KF.dbo.organizationlocations.CMLADDRESSLINE1 ,m1_KF.dbo.organizationlocations.CMLADDRESSLINE2 ,m1_KF.dbo.organizationlocations.CMLCITY ,m1_KF.dbo.organizationlocations.CMLSTATE ,m1_KF.dbo.organizationlocations.CMLPOSTCODE ,m1_KF.dbo.SalesOrders.OMPREQUESTEDSHIPDATE ,m1_KF.dbo.SalesOrders.ompSalesOrderID ,m1_KF.dbo.SalesOrders.ompOrderTotalBase ,convert(varchar(10),m1_KF.dbo.SalesOrders.uompbuilddate,110) FROM m1_KF.dbo.SalesOrders LEFT OUTER JOIN m1_KF.dbo.organizationlocations ON ompshiporganizationid = cmlorganizationid AND ompshiplocationid = cmllocationid WHERE m1_KF.dbo.SalesOrders.UOMPTRUCKNUMBER='$load' order by UOMPDROPSEQUENCE"; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } $data= ''; while (odbc_fetch_row($result)) { $UOMPDROPSEQUENCE=odbc_result($result,"UOMPDROPSEQUENCE"); $cmlName=odbc_result($result,"cmlName"); $cmlPhoneNumber=odbc_result($result,"cmlPhoneNumber"); $UCMLSTOREHOURSTXT=odbc_result($result,"UCMLSTOREHOURSTXT"); $ompCustomerPO=odbc_result($result,"ompCustomerPO"); $UOMPTOTALBOXCOUNT=odbc_result($result,"UOMPTOTALBOXCOUNT"); $UOMPVOLUMETOTAL=odbc_result($result,"UOMPVOLUMETOTAL"); $CMLADDRESSLINE1=odbc_result($result,"CMLADDRESSLINE1"); $CMLADDRESSLINE2=odbc_result($result,"CMLADDRESSLINE2"); $CMLCITY=odbc_result($result,"CMLCITY"); $CMLSTATE=odbc_result($result,"CMLSTATE"); $CMLPOSTCODE=odbc_result($result,"CMLPOSTCODE"); $ompSalesOrderID=odbc_result($result,"ompSalesOrderID"); $ompOrderTotalBase=odbc_result($result,"ompOrderTotalBase"); $uompbuilddate=odbc_result($result,"BuildDate"); { { if($UOMPDROPSEQUENCE % 2 == 0 ) {$style = '#FFFAFA';} else {$style = '#D3D3D3';}} } $data .= "<tr bgcolor=$style><th>$UOMPDROPSEQUENCE</th>"; $data .= "<th style='text-align:left' height='10'>$cmlName</th>"; $data .= "<td style='text-align:left' height='10'>$UCMLSTOREHOURSTXT</td>"; $data .= "<td style='text-align:center' height='10'>$ompCustomerPO</td>"; $data .= "<td style='text-align:center' height='10'>$UOMPTOTALBOXCOUNT</td>"; $data .= "<td style='text-align:center' height='10'> $UOMPVOLUMETOTAL</td>"; $data .= "<td style='text-align:center' height='10'> $CMLADDRESSLINE1</td>"; $data .= "<td style='text-align:center' height='10'> $CMLADDRESSLINE2</td>"; $data .= "<td style='text-align:center' height='10'> $CMLCITY</td>"; $data .= "<td style='text-align:center' height='10'> $CMLSTATE</td>"; $data .= "<td style='text-align:center' height='10'> $CMLPOSTCODE</td>"; $data .= "<td style='text-align:center' height='10'>$ompSalesOrderID</td>"; $data .= "<td style='text-align:center' height='10'>$ompOrderTotalBase</td>"; //$data .= "<td style='text-align:center'>$uompbuilddate</td>"; $data .= "<td style='text-align:center' height='10'>$cmlPhoneNumber</td></tr>"; } echo "<table><tr>"; echo "<th>Load $load</th><tr>"; echo "<th>Drop</th>"; echo "<th>Name</th>"; echo "<th>Note</th>"; echo "<th>PO</th>"; echo "<th>Box_count</th>"; echo "<th>Cubes</th>"; echo "<th>ADDRESSLINE</th>"; echo "<th>ADDRESS2</th>"; echo "<th>CITY</th>"; echo "<th>STATE</th>"; echo "<th>ZIP</th>"; echo "<th>Order_ID</th>"; echo "<th>Order_Total</th>"; //echo "<th>Build-Date</th>"; echo "<th> Phone </th></tr>"; echo $data; odbc_close($connect); ?> </body> </html>
  8. kat35601
    I need to email this report but I don't know where to start I have included the report code. <html> <head> <title>Customer Load Report</title> </head> <body> <h1>Customer Load Report</h1> <?php $custid=$_POST["Cust_ID"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="SELECT m1_DC.dbo.organizationlocations.cmlName ,m1_DC.dbo.SalesOrders.OMPSHIPPINGMETHODID ,m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER ,m1_DC.dbo.SalesOrders.ompCustomerPO ,m1_DC.dbo.SalesOrders.UOMPTOTALBOXCOUNT ,m1_DC.dbo.SalesOrders.UOMPVOLUMETOTAL ,m1_DC.dbo.organizationlocations.CMLADDRESSLINE1 ,m1_DC.dbo.organizationlocations.CMLADDRESSLINE2 ,m1_DC.dbo.organizationlocations.CMLCITY ,m1_DC.dbo.organizationlocations.CMLSTATE ,m1_DC.dbo.organizationlocations.CMLPOSTCODE ,m1_DC.dbo.SalesOrders.OMPREQUESTEDSHIPDATE ,m1_DC.dbo.SalesOrders.ompSalesOrderID ,convert(varchar(10),m1_DC.dbo.SalesOrders.ompRequestedShipDate,110) as ShipDate FROM m1_DC.dbo.SalesOrders LEFT OUTER JOIN m1_DC.dbo.organizationlocations ON ompshiporganizationid = cmlorganizationid AND ompshiplocationid = cmllocationid WHERE m1_DC.dbo.SalesOrders.ompCustomerOrganizationID='$custid' and (ompOrderDate>=CONVERT(VARCHAR(10),DATEADD(day, -7, getdate()),110) or convert(varchar(10),m1_DC.dbo.SalesOrders.ompRequestedShipDate,110) >=getdate())"; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } $data= ''; while (odbc_fetch_row($result)) { $cmlName=odbc_result($result,"cmlName"); $ompCustomerPO=odbc_result($result,"ompCustomerPO"); $UOMPTOTALBOXCOUNT=odbc_result($result,"UOMPTOTALBOXCOUNT"); $UOMPVOLUMETOTAL=odbc_result($result,"UOMPVOLUMETOTAL"); $CMLADDRESSLINE1=odbc_result($result,"CMLADDRESSLINE1"); $CMLADDRESSLINE2=odbc_result($result,"CMLADDRESSLINE2"); $CMLCITY=odbc_result($result,"CMLCITY"); $CMLSTATE=odbc_result($result,"CMLSTATE"); $CMLPOSTCODE=odbc_result($result,"CMLPOSTCODE"); $ompSalesOrderID=odbc_result($result,"ompSalesOrderID"); $ompRequestedShipDate=odbc_result($result,"ShipDate"); $data .= "<td style='text-align:left' > $ompCustomerPO</td>"; $data .= "<td style='text-align:left'> $UOMPTOTALBOXCOUNT</td>"; $data .= "<td style='text-align:left'> $UOMPVOLUMETOTAL</td>"; $data .= "<td style='text-align:left'> $CMLADDRESSLINE1</td>"; $data .= "<td style='text-align:left'> $CMLADDRESSLINE2</td>"; $data .= "<td style='text-align:left'> $CMLCITY</td>"; $data .= "<td style='text-align:left'> $CMLSTATE</td>"; $data .= "<td style='text-align:left'> $CMLPOSTCODE</td>"; $data .= "<td style='text-align:left'> $ompSalesOrderID</td>"; $data .= "<td style='text-align:left'> $ompRequestedShipDate</td></tr>"; } echo "<table><tr>"; echo "<th>PO</th>"; echo "<th>Box_count</th>"; echo "<th>Cubes</th>"; echo "<th>ADDRESSLINE</th>"; echo "<th>ADDRESS2</th>"; echo "<th>CITY</th>"; echo "<th>STATE</th>"; echo "<th>ZIP</th>"; echo "<th>Order_ID</th>"; echo "<th>Ship Date</th></tr>"; echo $data; odbc_close($connect); ?> </body> </html>
  9. kat35601
    This is how I colored the background of my rows based on odd or even. Not sure if it is the right way but it works great. { { if($UOMPDROPSEQUENCE % 2 == 0 ) {$style = '#ffa500';} else {$style = '#fffafa';}} // Then echo "<tr bgcolor=$style><th>$UOMPDROPSEQUENCE</th>";
  10. kat35601
    I want to change the background to a light gray if the value of the $UOMPDROPSEQUENCE field is an odd number. What would be the best way to do this please keep in mind I am very new to programming and PHP Thanks Below is a snippet of my code. $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Drop</th>"; echo "<th>Name</th>"; echo "<th>Note</th>"; echo "<th>PO</th>"; echo "<th>Box_count</th>"; echo "<th>Cubes</th>"; echo "<th>ADDRESSLINE</th>"; echo "<th>ADDRESS2</th>"; echo "<th>CITY</th>"; echo "<th>STATE</th>"; echo "<th>ZIP</th>"; echo "<th>Order_ID</th>"; echo "<th> Phone </th>"; while (odbc_fetch_row($result)) { $UOMPDROPSEQUENCE=odbc_result($result,"UOMPDROPSEQUENCE"); $cmlName=odbc_result($result,"cmlName"); $UCMLSTOREHOURSTXT=odbc_result($result,"UCMLSTOREHOURSTXT"); $ompCustomerPO=odbc_result($result,"ompCustomerPO"); $UOMPTOTALBOXCOUNT=odbc_result($result,"UOMPTOTALBOXCOUNT"); $UOMPVOLUMETOTAL=odbc_result($result,"UOMPVOLUMETOTAL"); $CMLADDRESSLINE1=odbc_result($result,"CMLADDRESSLINE1"); $CMLADDRESSLINE2=odbc_result($result,"CMLADDRESSLINE2"); $CMLCITY=odbc_result($result,"CMLCITY"); $CMLSTATE=odbc_result($result,"CMLSTATE"); $CMLPOSTCODE=odbc_result($result,"CMLPOSTCODE"); $ompSalesOrderID=odbc_result($result,"ompSalesOrderID"); $cmlPhoneNumber=odbc_result($result,"cmlPhoneNumber"); echo "<tr><td>$UOMPDROPSEQUENCE </td>"; echo "<td align='center'>$cmlPhoneNumber</td>"; echo "<td align='center'>$UCMLSTOREHOURSTXT</td>"; echo "<td align='center'>$ompCustomerPO</td>"; echo "<td align='center'>$UOMPTOTALBOXCOUNT</td>"; echo "<td align='center'>$UOMPVOLUMETOTAL</td>"; echo "<td align='center'>$CMLADDRESSLINE1</td>"; echo "<td align='center'>$CMLADDRESSLINE2</td>"; echo "<td align='center'>$CMLCITY</td>"; echo "<td align='center'>$CMLSTATE</td>"; echo "<td align='center'>$CMLPOSTCODE</td>"; echo "<td align='center'>$ompSalesOrderID</td>"; echo "<td align='center'> $cmlPhoneNumber</td></tr>"; } odbc_close($connect);
  11. kat35601
    Works great Thanks
  12. kat35601
    Notice: Undefined variable: gr_total in /var/www/html/kk_orders_entered_by.php on line 37 Entered Orders Total EWOODY 16 3415.17 JDODSON 3 1509.01 REVANS 3 0.00 TMOTES 9 2760.57 $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Entered</th>"; echo "<th>Orders</th>"; echo "<th>Total</th>"; //**************************************************************** while ($row = odbc_fetch_array($result)) { echo "<tr><td>" . $row['ompCreatedBy'] ."</td>"; echo "<td>" .$row['orders'] ."</td>"; echo "<td>" . $row['total'] ."</td></tr>"; $gr_total = $gr_total + $row['total']; } //******************************************************************
  13. kat35601
    <?php $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="select SO.ompCreatedBy, count(SO.ompSalesOrderID) as orders,round(sum(SO.ompOrderTotalBase),2) as total FROM m1_DC.dbo.SalesOrders SO Where SO.ompOrderDate=CONVERT(varchar,GETDATE(),101) Group by SO.ompCreatedBy " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Entered</th>"; echo "<th>Orders</th>"; echo "<th>Total</th>"; while (odbc_fetch_row($result)) { $EnteredBy= odbc_result($result,"ompCreatedBy"); $Orders= odbc_result($result, "orders"); $Total = odbc_result($result, "total"); echo "<tr><td>$EnteredBy</td>"; echo "<td>$Orders</td>"; echo "<td>$Total</td>"; } odbc_close($connect); ?> you 2 - you really should grab your results row by row instead of individually by column. It's a waste of resources the way you are doing it. me 2> first way I got it to work
  14. kat35601
    1> $sql="select SO.ompCreatedBy, count(SO.ompSalesOrderID) as orders,round(sum(SO.ompOrderTotalBase),2) as total FROM m1_DC.dbo.SalesOrders SO Where SO.ompOrderDate=CONVERT(varchar,GETDATE(),101) Group by SO.ompCreatedBy"; 2> first way I got it to work
  15. kat35601
    Hello I am very new to php and programming and I need a grand total of "$Total = odbc_result($result, "total");" but not sure of how to create it. $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>Entered</th>"; echo "<th>Orders</th>"; echo "<th>Total</th>"; while (odbc_fetch_row($result)) { $EnteredBy= odbc_result($result,"ompCreatedBy"); $Orders= odbc_result($result, "orders"); $Total = odbc_result($result, "total"); //Grand total of total needed echo "<tr><td>$EnteredBy</td>"; echo "<td>$Orders</td>"; echo "<td>$Total</td>"; echo "<td>$Grand_Total</td>"; } odbc_close($connect); ?> </body> </html>
  16. kat35601
    I want to add a space after $item in the below statement so that there is more space between the output of Item and Qty. echo "<tr><td>$Item </td>"; echo "<td>$Qty</td></tr>"; example 245-01 1 345-97 17 212-11 3 would become 245-01 1 345-97 17 212-11 3 Thanks
  17. kat35601
    worked perfect thanks
  18. kat35601
    I need help changing the back ground color of a field. the below code sets the background color of my field to red(I have highlighted the code in red that does this.) What I want to do is change that color when the sql field WIP_master.uompScheduleColor changes.So i think I would want an if statement to do this but I am not sure where to start since the field I will be looking at is in the sql statment. WIP_master.uompScheduleColor data looks like this: CRIMSON NEON BLUE PINK SILVER GREEN YELLOW my code so far: <html> <head> <title>PHP SQL Test</title> </head> <body> <p> Status Screen </p> <?php $bgcolor="#FF0000"; $connect =odbc_connect("REMOVED"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" SELECT distinct WIP_master.uompScheduleNumber,WIP_master.uompScheduleColor ,WIP_locations.location_name ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' ) as Printed ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' ) as Audited ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '9' ) as Shortage ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '10' ) as Shortage_Prt ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '11' ) as Red ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '12' ) as Neon ,( SELECT COUNT(*) FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1 LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '20' ) as QC_Hold FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations ON WIP_master.location = WIP_locations.location LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status ON WIP_master.STATUS = WIP_status.STATUS order by WIP_master.uompScheduleNumber " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>ScheduleNumber</th>"; echo "<th>Location</th>"; echo "<th>Printed</th>"; echo "<th>Audited</th>"; echo "<th>Shortage</th>"; echo "<th>Shortage_Prt</th>"; echo "<th>Red</th>"; echo "<th>Neon</th>"; echo "<th>QC_Hold</th>"; while (odbc_fetch_row($result)) { $uompScheduleNumber=odbc_result($result,"uompScheduleNumber"); $location_name=odbc_result($result,"location_name"); $Printed=odbc_result($result,"Printed"); $Audited=odbc_result($result,"Audited"); $Shortage=odbc_result($result,"Shortage"); $Shortage_Prt=odbc_result($result,"Shortage_Prt"); $Red=odbc_result($result,"Red"); $Neon=odbc_result($result,"Neon"); $QC_Hold=odbc_result($result,"QC_Hold"); echo "<tr><td bgcolor=$bgcolor >$uompScheduleNumber</td>"; echo "<td>$location_name</td>"; echo "<td>$Printed</td>"; echo "<td> $Audited</td>"; echo "<td> $Shortage</td>"; echo "<td> $Shortage_Prt</td>"; echo "<td> $Red</td>"; echo "<td> $Neon</td>"; echo "<td> $QC_Hold</td>"; } odbc_close($connect); ?> </body> </html>
  19. kat35601
    I installed php5-odbc and now I get Resource id#3. So it looks like its working I just now have to figure out how to get the results out. Thanks PHP SQL Test Resource id #3 End PHP SQL Test
  20. kat35601
    error message Fatal error: Call to undefined function odbc_connect() in /var/www/html/sql.php on line 9 I am running this from http://localhost/sql.php
  21. kat35601
    when I <?php I only get PHP SQL Test and no SQL output or END PHP SQL Test bythe way this is my first ever PHP code and I had to google long open tags to see what you meant. Thanks
  22. kat35601
    I created this to test the connection to my ms sql server with a web page. I can connect with isql with out fail. when I open this I get PHP SQL Test END PHP SQL Test I was expecting to get sql data between the above lines. What am I doing wrong. <html> <head> <title>PHP SQL Test</title> </head> <body> <p> PHP SQL Test </p> <? $conn =odbc_connect("datasource","user","password"); if(!$conn) { exit("Connection Failed: " . $conn); } $sql="SELECT top 10 * from WIP_master"; $rs =odbc_exec($conn,$sql); print_r($rs); if(!$rs){ exit("Error in SQL"); } odbc_close($conn); ?> <p> End PHP SQL Test </p> </body> </html>
  23. kat35601

    Php Sql

    kat35601 posted a topic in PHP Coding Help

    I have a little over one million rows of data that I want to insert into a mysql database and I need some help with the correct code for sql in PHP to do this Please. I can get my data with this but how would I convert the echo to an SQL Insert statment $file = fopen("data.txt", "r") or exit("Fail!"); while(!feof($file)){ $string = fgets($file); $array = explode('|',$string); echo $array[0]." - ".$array[1]." - ".$array[4]." - ".$array[5]." - ".$array[6]." - ".$array[7]." - ".$array[8]." - ".$array[9]." - ".$array[10]." - ".$array[15]." - ".$array[16]." - ".$array[17]." - ".$array[18]." - ".$array[21]." - ".$array[22]." - ".$array[23]." "; } fclose($file); ?> iNSERT INTO publicEN( unique_system_identifiern, uls_file_number, ebf_number, call_sign, entity_type, licensee_id, entity_name, first_name, mi, last_name, suffix, phone, fax, email, street_address, city, state, zip_code, po_box, attention_line, sgin, frn, applicant_type_code, applicant_type_other, status_code, status_date) VALUES ($array[0],$array[1],$array[4],$array[5],$array[6],$array[7],$array[8],$array[9],$array[10],$array[15],$array[16],$array[17],$array[18],$array[21],$array[22],$array[23])
  24. kat35601
    I have a file with a little over one million rows and would like to put it in a mysql database. But I am not sure of the best and easyist way. MY Table: create table dbo.PUBACC_EN ( record_type char(2) not null, unique_system_identifier numeric(9,0) not null, uls_file_number char(14) null, ebf_number varchar(30) null, call_sign char(10) null, entity_type char(2) null, licensee_id char(9) null, entity_name varchar(200) null, first_name varchar(20) null, mi char(1) null, last_name varchar(20) null, suffix char(3) null, phone char(10) null, fax char(10) null, email varchar(50) null, street_address varchar(60) null, city varchar(20) null, state char(2) null, zip_code char(9) null, po_box varchar(20) null, attention_line varchar(35) null, sgin char(3) null, frn char(10) null, applicant_type_code char(1) null, applicant_type_other char(40) null, status_code char(1) null, status_date datetime null I attached a file with sample data. data.txt
×
×
  • 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.