Jump to content

dstaton

Members
 View Profile  See their activity

  • Posts

    24

  • Joined

  • Last visited

    Never

 Content Type 

Everything posted by dstaton

  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?
  16. dstaton
    What do you mean " escaping the newline? What does "SEPARATOR '\N'" do?
  17. dstaton
    I apologize for being so green at this. I am using TOAD to run this query. The database is Oracle. I can't get around the "missing right parenthesis" error. I fixed the "as occurs" problem (I knew that, just wasn't paying attention). select group_concat(programme_name SEPARATOR '\n') as title, count(*) as occurs, title_id, year_of_production from title GROUP BY SOUNDEX(programme_name) HAVING occurs > 1 So the replace is just to get "Like" titles, it doesn't do anything in the database.
  18. dstaton
    When I try to run the following: select group_concat(programme_name SEPARATOR '\n') as title, count(*), title_id, year_of_production as occurs from title GROUP BY SOUNDEX(programme_name) HAVING occurs > 1 I get ORA-00907: missing right parenthesis I don't see where I am missing a right parenthesis. Also, in the other example of the code, what does the following mean? GROUP BY SOUNDEX(replace(replace(lower(programme_name), ' the', ''), ' and', ''))
  19. dstaton
    That's the problem. I am very new to this. I don't know how to define the rules.
  20. dstaton
    When I run my query I get over 8000 rows. For the titles beginning with "A", I get 546 titles (rows). There are 12 possible duplicates. This is the results I want to see. TITLE_ID PROGRAMME_NAME YEAR OF PRODUCTION 59055 Abandoned 1949 81474 Abandoned, The 2006 16912 Ablaze 0 21468 Ablaze 2001 18971 Addams Family 1991 15668 Addams' Family, The 1991 19267 After Dark, My Sweet 1990 78865 After Dark, My Sweet 1990 18786 Airplane 0 17740 Airplane! 1980 22643 Alvin & The Chipmunks: Meet The Wolfman 2000 22479 Alvin And The Chipmunks Meet The Wolfman 2000 I don't know how to define the Programme_Name in the where clause in order to get only possible duplicates.
  21. dstaton
    I have an Oracle Database of Movies. I need to find duplicate "titles" using a SQL Query. Every Title has a unique Title_ID, but there are some duplicates Programme_Names. I need to find the duplicates in order to merge them in the actual database through the merge feature in there. I don't know how to ask for duplicate records in a column. Also, there could be slight differences in the titles. One could have been input as "The Darkling" and the other as "Darkling". Any input would be greatly appreciated.
  22. dstaton
    Thanks. But somehow I have to define what "All Channels" means.
  23. dstaton
    Thanks. That added my "All Channels" selection. Next problem is that within the actual report process that runs once you make your selections, it stipulates that one channel must be selected. The "All Channels" doesn't apparently qualify for that. How can I make the "All Channels" be included as an acceptable "Channel"? $channelselected = false; foreach ($_POST as $key=>$value){ if (substr($key,0,4) == 'Chan') $channelselected = true; } if (!$channelselected){ $errfound = true; $errstring = 'At least one channel must be selected\n'; } if (!$errfound){ $reportlist = array(); $reportindex = 0; foreach ($_POST as $key=>$value){ if (substr($key,0,4) == 'Chan'){ $channelcode = substr($key,5,4); $channelrights = str_replace('Chan_'.$channelcode.'_','',$key); $reportindex++; $reportlist[$reportindex] = GenerateReport($reportstartdate,$reportenddate,$channelcode,$conn,&$ErrorArray,&$WebServerPath,&$ErrFilePath,&$ErrFileName);
  24. dstaton
    I am modifying a form from a previous employee. I am new to PHP and am not sure where to start. We have an Oracle database that we pull reports from using PHP and SQL. In the PHP form, I have selections for Channels (each individual channel) and Dates. I need to add a selection of "All Channels". I can't figure out how to do that. Can you please help? This is a portion of the current form that gives me each individual channel. How can I add at the bottom of this list of channels the option for "All Channels"? <td class=Header valign=top>Choose Channel(s)</td> <td class=Header><? $qry = 'SELECT CHANNEL_CODE,CHANNEL_NAME,RIGHTS_TYPE FROM LAPTV.PRIMARY_CHANNEL_LIST'; $stmt = ociparse($conn,$qry); ociexecute($stmt); while (OCIFetchInto($stmt,$res,OCI_ASSOC+OCI_RETURN_NULLS)){?> <input type=checkbox name="Chan_<?echo $res['CHANNEL_CODE']?>_<?echo $res['RIGHTS_TYPE'];?>" id="Chan_<?echo $res['CHANNEL_CODE']?>_<?echo $res['RIGHTS_TYPE'];?>"> <label for="Chan_<?echo $res['CHANNEL_CODE']?>_<?echo $res['RIGHTS_TYPE'];?>"><?echo $res['CHANNEL_NAME'];?></label><br /> <? }?> </td>
×
×
  • 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.