Jump to content

dstaton

Members
 View Profile  See their activity

  • Posts

    24

  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

dstaton's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. dstaton
    In case anyone reading this wants to know how I fixed this since I didn't get any more input, I was correct about the where clause. So here is what I had to change the where clause to: "main_house_media(vc.version_copy_id) in upper('" . implode("','",$housenums) . "') " . Now this works perfectly. Thank you to those who started me on the right path. This was painful to figure out on my own since then, but I am sure it has been a good learning experience.
  2. dstaton
    I am still only getting results if I put in one house number. Do I need to reference something differently in the where clause: "main_house_media(vc.version_copy_id) in ('" . $_POST['housenum'] . "') " . I also need to add an error message if no house numbers are entered. When the script runs for multiple house numbers, here is what I get in my hidden echo screen: *** Post Array= Array ( [housenum] => CCB1261,MCP7938 [startDate] => May 1, 2007 (Tue) [EndDate] => May 31, 2007 (Thu) [submit1] => Generate Report ) ***'CCB1261','MCP7938' housenum value = on housenbrentered=1sheet 1 sheet 1 select t.programme_name as programme_name, main_house_media(vc.version_copy_id) as house_number, v.version_name as version_name, tx_tranob.description as tx_type, tstr(get_duration_sec(t.title_id,v.version_id)) as duration, ts.channel_code, to_char(to_date(ts.tx_date,'YYYYMMDD'),'Day') as day_of_week, ts.tx_date, laptv.fix_real_time(ts.real_time) as schedule_time from title t, version v, version_copy vc, tx_tranob, tx_schedule ts where t.title_id = v.title_id and v.version_id = vc.version_id and v.version_id = ts.version_id and t.tx_type = tx_tranob.tx_type and ts.tx_date <= '20070531' and ts.tx_date >= '20070501' and main_house_media(vc.version_copy_id) in ('CCB1261,MCP7938') order by channel_code, tx_date, schedule_time ociexecute time=00:12 repfile=D:\websites\ibms.laptv.com\Report_Output\PromoPlayHistoryReports\PromoPlayHistory-20070501-20070531-47565.xls Here is the code that I tried. I am sure it doesn't make sense. I am trying to modify a report written by a former employee while self teaching myself at the same time. It doesn't all make sense to me. Any help you can provide is greatly appreciated. $housenbrentered = false; $housenums = explode(',',$_POST['housenum']); foreach ($housenums as $key => $value) { $housenums[$key] = trim($value); } echo "'".implode("','",$housenums)."'"; foreach ($_POST as $key=>$value) { if (substr($key,0) == 'housenum') { if ($value > " ") { echo '<br />housenum value = on'; $housenbrentered = true; } } } echo '<br />housenbrentered=' . $housenbrentered; if (!$housenbrentered){ $errfound = true; $errstring = 'A House Number must be entered\n'; }
  3. dstaton
    Do I need to change anything in the where clause? I still get the same results when I add in your suggestion. Or does the code you suggested need to be in a different place then before the $housenbrentered = true where I have put it? Does that make sense?
  4. dstaton
    OK, I think I have this working for the most part. I changed the where line that contains the house numbers to: "main_house_media(vc.version_copy_id) in ('" . $_POST['housenum'] . "') " . It works perfectly for one house number. What I don't know how to do is explain in the code to add the single quotes around each house number. ex: user enters CCB1261, MCP7938. The above query should show it as 'CCB1261', 'MCP7938'. It shows it as 'CCB1261, MCP7938'. How do I get it to put quotes around each house number?
  5. dstaton
    I know that I am probably doing something stupid. Can you look over the code and see what it might be? Maybe I have not defined something correctly or at the wrong time. It is just sending me a blank report even though I know I should have at least 4 pages. <HTML> <HEAD> <LINK rel="stylesheet" type="text/css" href="/IncludeFiles/MainStyle.css"> </HEAD> <BODY> <script> var outfilelist = new Array(); </script> <?php set_time_limit(960); require_once ('xlwriter.php'); echo '<br />*** Post Array='; echo '<pre>'; print_r ($_POST); echo '</pre>***'; function GenerateReport($repstartdate,$rependdate,$connection,$housenbrmatch){; // Build the workbook // first, build the workbook name and path $time = time(); $time = substr($time,5); // only get the last 5 digits $ReportFileName = 'PromoPlayHistory-' . strftime('%Y%m%d',$repstartdate) . '-' . strftime('%Y%m%d',$rependdate) . '-' . $time . '.xls'; $ReportFilePath = '\\Report_Output\\PromoPlayHistoryReports\\'; //build the worksheet $wksheets['PromoPlayHistory'] = 'PromoPlayHistory'; $wksheetcnt = 1; // now, create the workbook $excelconn = sheetCreate($ReportFilePath, $ReportFileName, $wksheets); $allrowindex = 0; //create the styles to be used $title = titleStyleCreate($excelconn); $heading = headingStyleCreate($excelconn); $detail = detailStyleCreate($excelconn); $detailCenter = detailCenterStyleCreate($excelconn); $titlestyle = styleCreate($excelconn,array('HorizontalAlignment'=>2,'VerticalAlignment'=>1,'FontName'=>'Arial','FontSize'=>12,'Bold'=>1,'WrapText'=>1)); $headerstyle = styleCreate($excelconn,array('BorderLineStyle'=>2,'HorizontalAlignment'=>2,'FontName'=>'Arial','FontSize'=>10,'Bold'=>1,'WrapText'=>0)); $detailstyle = styleCreate($excelconn,array('BorderLineStyle'=>1,'HorizontalAlignment'=>1,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'WrapText'=>1)); $detailboldstyle = styleCreate($excelconn,array('HorizontalAlignment'=>1,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'Bold'=>1,'WrapText'=>1)); $detailcenterstyle = styleCreate($excelconn,array('BorderLineStyle'=>1,'HorizontalAlignment'=>2,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'WrapText'=>1)); // write the Title to the worksheet $allrowindex = 1; centerWorksheet($excelconn,1); repeatHeaderRows($excelconn,1,2,1); //write the Title to the worksheet mergeCells($excelconn,$allrowindex,1,1,1,9); rowHeight($excelconn,$allrowindex,1,40); titleStyle($excelconn,1,$allrowindex,1,$title); writeCell($excelconn,$allrowindex,1,1,'Promo Play History Summary Report for '. strftime('%B %d, %Y',$repstartdate) . ' through ' . strftime('%B %d, %Y',$rependdate) ); //write the header & footer lines $allrowindex++; writeCell($excelconn,1,$allrowindex,1,'Title',$headerstyle,20); writeCell($excelconn,1,$allrowindex,2,'House Number',$headerstyle); writeCell($excelconn,1,$allrowindex,3,'Version',$headerstyle); writeCell($excelconn,1,$allrowindex,4,'TX Type',$headerstyle); writeCell($excelconn,1,$allrowindex,5,'Duration',$headerstyle); writeCell($excelconn,1,$allrowindex,6,'Channel Code',$headerstyle); writeCell($excelconn,1,$allrowindex,7,'Day of Week',$headerstyle,11); writeCell($excelconn,1,$allrowindex,8,'TX Date',$headerstyle); writeCell($excelconn,1,$allrowindex,9,'Schedule Time',$headerstyle,11); /*--------------------------------------------------------------------------------------------------------------*/ $sheetnbr = 0; $sheetnbr++; centerWorksheet($excelconn,$sheetnbr); writeCell($excelconn,$sheetnbr,1,1,'Promo Play History Summary Report for ' . strftime('%B %d, %Y',$repstartdate) . ' through ' . strftime('%B %d, %Y',$rependdate) ); $qryString = "select " . "t.programme_name as programme_name, " . "main_house_media(vc.version_copy_id) as house_number, " . "v.version_name as version_name, " . "tx_tranob.description as tx_type, " . "tstr(get_duration_sec(t.title_id,v.version_id)) as duration, " . "ts.channel_code, " . "to_char(to_date(ts.tx_date,'YYYYMMDD'),'Day') as day_of_week, " . "ts.tx_date, " . "laptv.fix_real_time(ts.real_time) as schedule_time " . "from " . "title t, " . "version v, " . "version_copy vc, " . "tx_tranob, " . "tx_schedule ts " . "where " . "t.title_id = v.title_id and " . "v.version_id = vc.version_id and " . "v.version_id = ts.version_id and " . "t.tx_type = tx_tranob.tx_type and " . "ts.tx_date <= '" . strftime('%Y%m%d',$rependdate) . "' and ts.tx_date >= '" . strftime('%Y%m%d',$repstartdate) . "' and " . "main_house_media(vc.version_copy_id) like ('%" . $_POST["housenum"] . "%') " . "order by " . "channel_code, tx_date, schedule_time " ; echo '<br />' . $qryString; echo "<script>top.frames.Main.showStatus('Processing ...');</script>"; $housenbrmatch = false; $stmt = ociparse($connection,$qryString); $startexecute = time(); ociexecute($stmt); $endexecute = time(); echo '<br />ociexecute time=' . date("i:s",$endexecute-$startexecute); while (OCIFetchInto($stmt,$res,OCI_ASSOC+OCI_RETURN_NULLS)){ $housenbrmatch = true; $colnbr = 1; $allrowindex++; writeCell($excelconn,1,$allrowindex,1,$res['PROGRAMME_NAME'],$detailstyle); writeCell($excelconn,1,$allrowindex,2,$res['HOUSE_NUMBER'],$detailcenterstyle); writeCell($excelconn,1,$allrowindex,3,$res['VERSION_NAME'],$detailstyle); writeCell($excelconn,1,$allrowindex,4,$res['TX_TYPE'],$detailstyle); writeCell($excelconn,1,$allrowindex,5,$res['DURATION'],$detailcenterstyle); writeCell($excelconn,1,$allrowindex,6,$res['CHANNEL_CODE'],$detailcenterstyle); writeCell($excelconn,1,$allrowindex,7,$res['DAY_OF_WEEK'],$detailstyle); writeCell($excelconn,1,$allrowindex,8,$res['TX_DATE'],$detailcenterstyle); writeCell($excelconn,1,$allrowindex,9,$res['SCHEDULE_TIME'],$detailcenterstyle); } colAutoFit($excelconn,$sheetnbr,100); rowAutoFit($excelconn,1); echo "<script>top.frames.Main.hideStatus();</script>"; sheetClose($ReportFilePath, $ReportFileName, $wksheets, $excelconn); return $ReportFileName; } // End of GenerateReport function $errfound = false; $errstring = '' ; $basedirectory = 'D:/websites/ibms.laptv.com/Report_Output/'; $outputdirectory = $basedirectory . 'PromoPlayHistoryReports' . '/'; if (!VerifyDir($outputdirectory)){ $errfound = true; $errstring .= 'Output directory ' . $outputdirectory . ' cannot be created\n'; } $repstartdate = strtotime($_POST['StartDate']); $rependdate = strtotime($_POST['EndDate']); $housenbrmatch = ($_POST['housenum']); $housenbrentered = false; $channelselected = false; //check to see if a house number has been entered $house_num = explode(",", $_POST['housenum']); if (is_array($house_num)) { $housenum = ""; foreach ($house_num as $num) { $housenum .= "'" . trim($num) . "',"; } $housenum = substr($housenum, 0, -1); } $SQL = "SELECT * FROM main_house_media(vc.version_copy_id) in (" . $housenum . ") "; foreach ($_POST as $key=>$value){ echo '<br />key=' . $key . '...value=' . $value; if (substr($key,0, == 'housenum') { if ($value > " ") { echo '<br />housenum value = on'; $housenbrentered = true; } } } echo '<br />housenbrentered=' . $housenbrentered; if (!$housenbrentered){ $errfound = true; $errstring = 'A House Number must be entered\n'; } if (!$errfound){ $reportlist = array(); $reportindex = 0; $reportindex++; $reportlist[$reportindex] = GenerateReport($repstartdate,$rependdate,$conn,$housenbrmatch); //---------------------------------------------------------------------- //now, go through and create list of files to 'return' to parent window //---------------------------------------------------------------------- if ($housenbrmatch) { for ($i=1;$i<=$reportindex;$i++){ if (substr($reportlist[$i],5) == 'ERROR'){ $thisreport = $reportlist[$i]; } else { $reportlistedit[$i] = substr_replace($reportlist[$i], '', strlen($reportlist[$i])-10,-4) ; $thisreport = '<a href="/Report_Output/PromoPlayHistoryReports/' . $reportlist[$i] . '" target="new">' . $reportlistedit[$i] . "</a>"; } echo "<script> outfilelist[" . ($i-1) . "] = '" . $thisreport . "'; </script>"; }?> <script>top.frames.Main.reportComplete(outfilelist);</script><? } else { echo "<script>alert('No matching house numbers found');</script>"; } } else { echo "<script>alert('$errstring');</script>"; } ?> </BODY> </HTML>
  6. dstaton
    Why do I put $SQL = "SELECT * FROM main_house_media(vc.version_copy_id) in (" . $housenum . ") "; as well as where main_house_media(vc.version_copy_id) like ('%" . $_POST["housenum"] . "%')
  7. dstaton
    OK, I am pretty new to this. Basically our reports are generated from the following: Process.php and Select.php. The query is in the Process.php file. The user input (form) is in the Select.php file. So this line of code: $_POST['housenum'] = "MCP7938, CCB1261"; would be in the Process.php file. What would it actually look like since that doens't get input until the user queries for the House Number on the form? main_house_media is a function written within my company to pull the house number out of the database. It is not a PHP or SQL function.
  8. dstaton
    I guess I don't know how to "explode" from the textarea. However, this query doesn't work even when I only put in one house number.
  9. dstaton
    I am trying to add a textarea to a PHP form to query for House Numbers in a database. I was able to make this work with just a text field but need to allow my users to input several House Numbers in one query. Here is what I tried on the form: ]<td class=Header valign=top align=center>Enter House Number(s)<br>to Search for:</td> <td><TEXTAREA NAME="HouseNum" ROWS=2 COLS=40></TEXTAREA></td> Here is how I call that in the report: ...where upper(main_house_media(vc.version_copy_id)) in (upper('%" . $_POST["HouseNum"] . "%')) ...foreach ($_POST as $key=>$value){ echo '<br />key=' . $key . '...value=' . $value; if (substr($key,0, == 'HouseNum') { if ($value > " ") { echo '<br />HouseNum value = on'; $housenbrentered = true; } } } echo '<br />housenbrentered=' . $housenbrentered; if (!$housenbrentered){ $errfound = true; $errstring = 'A House Number must be entered\n'; } This works when I just use the text, but that isn't large enough. I need the users to be able to enter as many House Numbers to look for as they want. Some example House Numbers are 'CCB1261' and 'MCP7938'. I can't find much info about the textarea in regards to pulling info for a report. I can find plenty of textarea issues with updating a database, but that is not what I need. <td class=Header valign=top align=center>Enter House Number<br>(or portion of)<br>to Search for:</td> <td class=Header><input type=text name=HouseNum size=7 maxlength=7></td>
  10. dstaton
    Same results as before...it returns over 30,000 records. I am not getting results that closely match. Example: Brotherhood of Justice = Border, The Eating Raoul = Eyewitness I don't think the soundex thing is working for what I need.
  11. dstaton
    ARGGGG!!!! I am destined to never get this query to work. I never dreamed it would be this difficult. When running: SELECT a.title_id as ida, a.programme_name as titlea, a.year_of_production as yeara, b.title_id as idb, b.programme_name as titleb, b.year_of_production as yearb FROM title a INNER JOIN title b ON SOUNDEX(a.programme_name = b.programme_name) WHERE a.title_id < b.title_id I get ORA-00907: missing right parenthesis. This is the error I was getting with the query I tried back on May 4th.
  12. dstaton
    That returns over 38000 rows. It returns things like: Man With One Red Shoe = Manhattan Man With One Red Shoe matches 27 other titles that are really nothing like it. I do understand why it is returning them but I am really looking for something that matches more than one word in each title. Is there any other way to do this?
  13. dstaton
    I don't want to delete the titles. Some duplicates should be there (they are remakes or a TV Versions vs Theater Version). I just want to find them to determine if they are actually duplicates that need to be merged. There is a merge feature in the actual database.
  14. dstaton
    I have spent 3 days working on this exclusively. I believe you are right. When I do this: select year_of_production,programme_name, count(programme_name) FROM (select title_id, programme_name, year_of_production from title where title_type = 'P') GROUP BY programme_name, year_of_production HAVING count(*) > 1 I get: YEAR PROGRAMME_NAME COUNT 2004 Dirt 2 1999 Pups 2 2001 Torso 2 1991 Switch 2 1976 Network 2 1978 Wiz, The 2 1977 Slap Shot 2 1985 Turk 182! 2 2001 Wash, The 2 1988 Boost, The 2 0 Last Dance 2 1988 Masquerade 2 But that doesn't account for the titles that have slight differences. I also need Titles that have different Year of production (sometimes it is entered incorrectly). Any ideas you have would be greatly appreciated.
  15. dstaton
    I changed the query to: SELECT title_id, year_of_production, group_concat(programme_name,'\\n'), count(programme_name) AS occurs FROM title GROUP BY SOUNDEX(replace(replace(lower(programme_name), ' the', ''), ' and', '')) HAVING count(programme_name) > 1 Now I get an Oracle error message of ORA-00904: "GROUP_CONCAT": invalid identifier. Why does it think this is a column name?
×
×
  • 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.