00001
00002 #include "mythcontext.h"
00003 #include "mythdbcon.h"
00004 #include "programinfo.h"
00005 #include "programdata.h"
00006
00007 bool operator<(const ProgInfo &a, const ProgInfo &b)
00008 {
00009 return (a.start < b.start);
00010 }
00011
00012 bool operator>(const ProgInfo &a, const ProgInfo &b)
00013 {
00014 return (a.start > b.start);
00015 }
00016
00017 bool operator<=(const ProgInfo &a, const ProgInfo &b)
00018 {
00019 return (a.start <= b.start);
00020 }
00021
00022 static bool conflict(ProgInfo &a, ProgInfo &b)
00023 {
00024 if ((a.start <= b.start && b.start < a.end) ||
00025 (b.end <= a.end && a.start < b.end))
00026 return true;
00027 return false;
00028 }
00029
00030 void ProgramData::clearDataByChannel(int chanid, QDateTime from, QDateTime to)
00031 {
00032 int secs;
00033 QDateTime newFrom, newTo;
00034
00035 MSqlQuery query(MSqlQuery::InitCon());
00036
00037 query.prepare("SELECT tmoffset FROM channel where chanid = :CHANID ;");
00038 query.bindValue(":CHANID", chanid);
00039 query.exec();
00040 if (!query.isActive() || query.size() != 1)
00041 {
00042 MythContext::DBError("clearDataByChannel", query);
00043 return;
00044 }
00045 query.next();
00046 secs = query.value(0).toInt();
00047
00048 secs *= 60;
00049 newFrom = from.addSecs(secs);
00050 newTo = to.addSecs(secs);
00051
00052 query.prepare("DELETE FROM program "
00053 "WHERE starttime >= :FROM AND starttime < :TO "
00054 "AND chanid = :CHANID ;");
00055 query.bindValue(":FROM", newFrom);
00056 query.bindValue(":TO", newTo);
00057 query.bindValue(":CHANID", chanid);
00058 query.exec();
00059
00060 query.prepare("DELETE FROM programrating "
00061 "WHERE starttime >= :FROM AND starttime < :TO "
00062 "AND chanid = :CHANID ;");
00063 query.bindValue(":FROM", newFrom);
00064 query.bindValue(":TO", newTo);
00065 query.bindValue(":CHANID", chanid);
00066 query.exec();
00067
00068 query.prepare("DELETE FROM credits "
00069 "WHERE starttime >= :FROM AND starttime < :TO "
00070 "AND chanid = :CHANID ;");
00071 query.bindValue(":FROM", newFrom);
00072 query.bindValue(":TO", newTo);
00073 query.bindValue(":CHANID", chanid);
00074 query.exec();
00075
00076 query.prepare("DELETE FROM programgenres "
00077 "WHERE starttime >= :FROM AND starttime < :TO "
00078 "AND chanid = :CHANID ;");
00079 query.bindValue(":FROM", newFrom);
00080 query.bindValue(":TO", newTo);
00081 query.bindValue(":CHANID", chanid);
00082 query.exec();
00083 }
00084
00085 void ProgramData::clearDataBySource(int sourceid, QDateTime from, QDateTime to)
00086 {
00087 MSqlQuery query(MSqlQuery::InitCon());
00088 query.prepare("SELECT chanid FROM channel WHERE "
00089 "sourceid = :SOURCE ;");
00090 query.bindValue(":SOURCE", sourceid);
00091
00092 if (!query.exec())
00093 MythContext::DBError("Selecting channels per source", query);
00094
00095 if (query.isActive() && query.size() > 0)
00096 {
00097 while (query.next())
00098 {
00099 int chanid = query.value(0).toInt();
00100 clearDataByChannel(chanid, from, to);
00101 }
00102 }
00103 }
00104
00105 void ProgramData::fixProgramList(QValueList<ProgInfo> *fixlist)
00106 {
00107 qHeapSort(*fixlist);
00108
00109 QValueList<ProgInfo>::iterator i = fixlist->begin();
00110 QValueList<ProgInfo>::iterator cur;
00111 while (1)
00112 {
00113 cur = i;
00114 i++;
00115
00116 if ((*cur).endts == "" || (*cur).startts > (*cur).endts)
00117 {
00118 if (i != fixlist->end())
00119 {
00120 (*cur).endts = (*i).startts;
00121 (*cur).end = (*i).start;
00122 }
00123 else
00124 {
00125 (*cur).end = (*cur).start;
00126 if ((*cur).end < QDateTime((*cur).end.date(), QTime(6, 0)))
00127 {
00128 (*cur).end.setTime(QTime(6, 0));
00129 }
00130 else
00131 {
00132 (*cur).end.setTime(QTime(0, 0));
00133 (*cur).end.setDate((*cur).end.date().addDays(1));
00134 }
00135
00136 (*cur).endts = (*cur).end.toString("yyyyMMddhhmmss").ascii();
00137 }
00138 }
00139 if (i == fixlist->end())
00140 break;
00141
00142 if (conflict(*cur, *i))
00143 {
00144 QValueList<ProgInfo>::iterator tokeep, todelete;
00145
00146 if ((*cur).end <= (*cur).start)
00147 tokeep = i, todelete = cur;
00148 else if ((*i).end <= (*i).start)
00149 tokeep = cur, todelete = i;
00150 else if ((*cur).subtitle != "" && (*i).subtitle == "")
00151 tokeep = cur, todelete = i;
00152 else if ((*i).subtitle != "" && (*cur).subtitle == "")
00153 tokeep = i, todelete = cur;
00154 else if ((*cur).desc != "" && (*i).desc == "")
00155 tokeep = cur, todelete = i;
00156 else if ((*i).desc != "" && (*cur).desc == "")
00157 tokeep = i, todelete = cur;
00158 else
00159 tokeep = i, todelete = cur;
00160
00161
00162 VERBOSE(VB_XMLTV,
00163 QString("Removing conflicting program: %1 - %2 %3 %4")
00164 .arg((*todelete).start.toString(Qt::ISODate))
00165 .arg((*todelete).end.toString(Qt::ISODate))
00166 .arg((*todelete).channel)
00167 .arg((*todelete).title));
00168
00169 VERBOSE(VB_XMLTV,
00170 QString("Conflicted with : %1 - %2 %3 %4")
00171 .arg((*tokeep).start.toString(Qt::ISODate))
00172 .arg((*tokeep).end.toString(Qt::ISODate))
00173 .arg((*tokeep).channel)
00174 .arg((*tokeep).title));
00175
00176 if (todelete == i)
00177 i = cur;
00178 fixlist->erase(todelete);
00179 }
00180 }
00181 }
00182
00183 void ProgramData::handlePrograms(
00184 int id, QMap<QString, QValueList<ProgInfo> > *proglist)
00185 {
00186 int unchanged = 0, updated = 0;
00187 QMap<QString, QValueList<ProgInfo> >::Iterator mapiter;
00188
00189 for (mapiter = proglist->begin(); mapiter != proglist->end(); ++mapiter)
00190 {
00191 MSqlQuery query(MSqlQuery::InitCon()), chanQuery(MSqlQuery::InitCon());
00192
00193 if (mapiter.key() == "")
00194 continue;
00195
00196 int chanid = 0;
00197
00198 chanQuery.prepare("SELECT chanid FROM channel WHERE sourceid = :ID AND "
00199 "xmltvid = :XMLTVID;");
00200 chanQuery.bindValue(":ID", id);
00201 chanQuery.bindValue(":XMLTVID", mapiter.key());
00202
00203 chanQuery.exec();
00204
00205 if (!chanQuery.isActive() || chanQuery.size() <= 0)
00206 {
00207 VERBOSE(VB_IMPORTANT, QString("Unknown xmltv channel identifier: "
00208 "%1 - Skipping channel.")
00209 .arg(mapiter.key()));
00210 continue;
00211 }
00212
00213 while (chanQuery.next())
00214 {
00215 chanid = chanQuery.value(0).toInt();
00216
00217 if (chanid == 0)
00218 {
00219 VERBOSE(VB_IMPORTANT, QString("Unknown xmltv channel "
00220 "identifier: %1 - Skipping "
00221 "channel.")
00222 .arg(mapiter.key()));
00223 continue;
00224 }
00225
00226 QValueList<ProgInfo> *sortlist = &((*proglist)[mapiter.key()]);
00227
00228 fixProgramList(sortlist);
00229
00230 QValueList<ProgInfo>::iterator i = sortlist->begin();
00231 for (; i != sortlist->end(); i++)
00232 {
00233 query.prepare("SELECT * FROM program WHERE "
00234 "chanid=:CHANID AND starttime=:START AND "
00235 "endtime=:END AND title=:TITLE AND "
00236 "subtitle=:SUBTITLE AND description=:DESC AND "
00237 "category=:CATEGORY AND "
00238 "category_type=:CATEGORY_TYPE AND "
00239 "airdate=:AIRDATE AND "
00240 "stars >= (:STARS - 0.001) AND stars <= (:STARS + 0.001) AND "
00241 "previouslyshown=:PREVIOUSLYSHOWN AND "
00242 "title_pronounce=:TITLE_PRONOUNCE AND "
00243 "audioprop=:AUDIOPROP AND "
00244 "videoprop=:VIDEOPROP AND "
00245 "subtitletypes=:SUBTYPES AND "
00246 "partnumber=:PARTNUMBER AND "
00247 "parttotal=:PARTTOTAL AND "
00248 "seriesid=:SERIESID AND "
00249 "showtype=:SHOWTYPE AND "
00250 "colorcode=:COLORCODE AND "
00251 "syndicatedepisodenumber=:SYNDICATEDEPISODENUMBER AND "
00252 "programid=:PROGRAMID;");
00253 query.bindValue(":CHANID", chanid);
00254 query.bindValue(":START", (*i).start);
00255 query.bindValue(":END", (*i).end);
00256 query.bindValue(":TITLE", (*i).title.utf8());
00257 query.bindValue(":SUBTITLE", (*i).subtitle.utf8());
00258 query.bindValue(":DESC", (*i).desc.utf8());
00259 query.bindValue(":CATEGORY", (*i).category.utf8());
00260 query.bindValue(":CATEGORY_TYPE", (*i).catType.utf8());
00261 query.bindValue(":AIRDATE", (*i).airdate.utf8());
00262 query.bindValue(":STARS", (*i).stars.utf8());
00263 query.bindValue(":PREVIOUSLYSHOWN", (*i).previouslyshown);
00264 query.bindValue(":TITLE_PRONOUNCE", (*i).title_pronounce.utf8());
00265 query.bindValue(":AUDIOPROP", (*i).audioproperties);
00266 query.bindValue(":VIDEOPROP", (*i).videoproperties);
00267 query.bindValue(":SUBTYPES", (*i).subtitletype);
00268 query.bindValue(":PARTNUMBER", (*i).partnumber);
00269 query.bindValue(":PARTTOTAL", (*i).parttotal);
00270 query.bindValue(":SERIESID", (*i).seriesid);
00271 query.bindValue(":SHOWTYPE", (*i).showtype);
00272 query.bindValue(":COLORCODE", (*i).colorcode);
00273 query.bindValue(":SYNDICATEDEPISODENUMBER", (*i).syndicatedepisodenumber);
00274 query.bindValue(":PROGRAMID", (*i).programid);
00275 query.exec();
00276
00277 if (query.isActive() && query.size() > 0)
00278 {
00279 unchanged++;
00280 continue;
00281 }
00282
00283 query.prepare("SELECT title,starttime,endtime FROM program "
00284 "WHERE chanid=:CHANID AND starttime>=:START AND "
00285 "starttime<:END;");
00286 query.bindValue(":CHANID", chanid);
00287 query.bindValue(":START", (*i).start);
00288 query.bindValue(":END", (*i).end);
00289 query.exec();
00290
00291 if (query.isActive() && query.size() > 0)
00292 {
00293
00294 while (query.next())
00295 {
00296 VERBOSE(VB_XMLTV,
00297 QString("Removing existing program: %1 - %2 %3 %4")
00298 .arg(query.value(1).toDateTime().toString(Qt::ISODate))
00299 .arg(query.value(2).toDateTime().toString(Qt::ISODate))
00300 .arg((*i).channel)
00301 .arg(QString::fromUtf8(query.value(0).toString())));
00302 }
00303
00304 VERBOSE(VB_XMLTV,
00305 QString("Inserting new program : %1 - %2 %3 %4")
00306 .arg((*i).start.toString(Qt::ISODate))
00307 .arg((*i).end.toString(Qt::ISODate))
00308 .arg((*i).channel)
00309 .arg((*i).title));
00310
00311 MSqlQuery subquery(MSqlQuery::InitCon());
00312 subquery.prepare("DELETE FROM program WHERE "
00313 "chanid=:CHANID AND starttime>=:START "
00314 "AND starttime<:END;");
00315 subquery.bindValue(":CHANID", chanid);
00316 subquery.bindValue(":START", (*i).start);
00317 subquery.bindValue(":END", (*i).end);
00318
00319 subquery.exec();
00320
00321 subquery.prepare("DELETE FROM programrating WHERE "
00322 "chanid=:CHANID AND starttime>=:START "
00323 "AND starttime<:END;");
00324 subquery.bindValue(":CHANID", chanid);
00325 subquery.bindValue(":START", (*i).start);
00326 subquery.bindValue(":END", (*i).end);
00327
00328 subquery.exec();
00329
00330 subquery.prepare("DELETE FROM credits WHERE "
00331 "chanid=:CHANID AND starttime>=:START "
00332 "AND starttime<:END;");
00333 subquery.bindValue(":CHANID", chanid);
00334 subquery.bindValue(":START", (*i).start);
00335 subquery.bindValue(":END", (*i).end);
00336
00337 subquery.exec();
00338 }
00339
00340 query.prepare("INSERT INTO program (chanid,starttime,endtime,"
00341 "title,subtitle,description,category,"
00342 "category_type,airdate,stars,previouslyshown,"
00343 "title_pronounce,stereo,hdtv,"
00344 "audioprop,videoprop,subtitletypes,subtitled,"
00345 "closecaptioned,partnumber,parttotal,"
00346 "seriesid,originalairdate,showtype,colorcode,"
00347 "syndicatedepisodenumber,programid) "
00348 "VALUES(:CHANID,:STARTTIME,:ENDTIME,:TITLE,"
00349 ":SUBTITLE,:DESCRIPTION,:CATEGORY,:CATEGORY_TYPE,"
00350 ":AIRDATE,:STARS,:PREVIOUSLYSHOWN,"
00351 ":TITLE_PRONOUNCE,:STEREO,:HDTV,"
00352 ":AUDIOPROP,:VIDEOPROP,:SUBTYPES,:SUBTITLED,"
00353 ":CLOSECAPTIONED,:PARTNUMBER,:PARTTOTAL,"
00354 ":SERIESID,:ORIGINALAIRDATE,:SHOWTYPE,:COLORCODE,"
00355 ":SYNDICATEDEPISODENUMBER,:PROGRAMID);");
00356 query.bindValue(":CHANID", chanid);
00357 query.bindValue(":STARTTIME", (*i).start);
00358 query.bindValue(":ENDTIME", (*i).end);
00359 query.bindValue(":TITLE", (*i).title.utf8());
00360 query.bindValue(":SUBTITLE", (*i).subtitle.utf8());
00361 query.bindValue(":DESCRIPTION", (*i).desc.utf8());
00362 query.bindValue(":CATEGORY", (*i).category.utf8());
00363 query.bindValue(":CATEGORY_TYPE", (*i).catType.utf8());
00364 query.bindValue(":AIRDATE", (*i).airdate.utf8());
00365 query.bindValue(":STARS", (*i).stars.utf8());
00366 query.bindValue(":PREVIOUSLYSHOWN", (*i).previouslyshown);
00367 query.bindValue(":TITLE_PRONOUNCE", (*i).title_pronounce.utf8());
00368 query.bindValue(":AUDIOPROP", (*i).audioproperties);
00369 query.bindValue(":VIDEOPROP", (*i).videoproperties);
00370 query.bindValue(":SUBTYPES", (*i).subtitletype);
00371 query.bindValue(":HDTV", (*i).videoproperties & VID_HDTV ? true : false);
00372 query.bindValue(":STEREO", (*i).audioproperties & AUD_STEREO ? true : false);
00373 query.bindValue(":SUBTITLED", (*i).subtitletype & SUB_NORMAL ? true : false);
00374 query.bindValue(":CLOSECAPTIONED", (*i).subtitletype & SUB_HARDHEAR ? true : false);
00375 query.bindValue(":PARTNUMBER", (*i).partnumber);
00376 query.bindValue(":PARTTOTAL", (*i).parttotal);
00377 query.bindValue(":SERIESID", (*i).seriesid);
00378 query.bindValue(":ORIGINALAIRDATE", (*i).originalairdate);
00379 query.bindValue(":SHOWTYPE", (*i).showtype);
00380 query.bindValue(":COLORCODE", (*i).colorcode);
00381 query.bindValue(":SYNDICATEDEPISODENUMBER", (*i).syndicatedepisodenumber);
00382 query.bindValue(":PROGRAMID", (*i).programid);
00383 if (!query.exec())
00384 MythContext::DBError("program insert", query);
00385
00386 updated++;
00387
00388 QValueList<ProgRating>::iterator j = (*i).ratings.begin();
00389 for (; j != (*i).ratings.end(); j++)
00390 {
00391 query.prepare("INSERT INTO programrating (chanid,starttime,"
00392 "system,rating) VALUES (:CHANID,:START,:SYS,"
00393 ":RATING);");
00394 query.bindValue(":CHANID", chanid);
00395 query.bindValue(":START", (*i).start);
00396 query.bindValue(":SYS", (*j).system.utf8());
00397 query.bindValue(":RATING", (*j).rating.utf8());
00398
00399 if (!query.exec())
00400 MythContext::DBError("programrating insert", query);
00401 }
00402
00403 QValueList<ProgCredit>::iterator k = (*i).credits.begin();
00404 for (; k != (*i).credits.end(); k++)
00405 {
00406 query.prepare("SELECT person FROM people WHERE "
00407 "name = :NAME;");
00408 query.bindValue(":NAME", (*k).name.utf8());
00409 if (!query.exec())
00410 MythContext::DBError("person lookup", query);
00411
00412 int personid = -1;
00413 if (query.isActive() && query.size() > 0)
00414 {
00415 query.next();
00416 personid = query.value(0).toInt();
00417 }
00418
00419 if (personid < 0)
00420 {
00421 query.prepare("INSERT INTO people (name) VALUES "
00422 "(:NAME);");
00423 query.bindValue(":NAME", (*k).name.utf8());
00424 if (!query.exec())
00425 MythContext::DBError("person insert", query);
00426
00427 query.prepare("SELECT person FROM people WHERE "
00428 "name = :NAME;");
00429 query.bindValue(":NAME", (*k).name.utf8());
00430 if (!query.exec())
00431 MythContext::DBError("person lookup", query);
00432
00433 if (query.isActive() && query.size() > 0)
00434 {
00435 query.next();
00436 personid = query.value(0).toInt();
00437 }
00438 }
00439
00440 if (personid < 0)
00441 {
00442 VERBOSE(VB_IMPORTANT, "Error inserting person");
00443 continue;
00444 }
00445
00446 query.prepare("INSERT INTO credits (chanid,starttime,"
00447 "role,person) VALUES "
00448 "(:CHANID, :START, :ROLE, :PERSON);");
00449 query.bindValue(":CHANID", chanid);
00450 query.bindValue(":START", (*i).start);
00451 query.bindValue(":ROLE", (*k).role.utf8());
00452 query.bindValue(":PERSON", personid);
00453 if (!query.exec())
00454 {
00455
00456 query.prepare("UPDATE credits SET "
00457 "role = concat(role,',:ROLE'), "
00458 "starttime = :START "
00459 "WHERE chanid = :CHANID AND "
00460 "starttime = :START2 and person = :PERSON");
00461 query.bindValue(":ROLE", (*k).role.utf8());
00462 query.bindValue(":START", (*i).start);
00463 query.bindValue(":CHANID", chanid);
00464 query.bindValue(":START2", (*i).start);
00465 query.bindValue(":PERSON", personid);
00466
00467 if (!query.exec())
00468 MythContext::DBError("credits update", query);
00469 }
00470 }
00471 }
00472 }
00473 }
00474
00475 VERBOSE(VB_GENERAL,
00476 QString("Updated programs: %1 Unchanged programs: %2")
00477 .arg(updated)
00478 .arg(unchanged));
00479 }
00480
00481 int ProgramData::fix_end_times(void)
00482 {
00483 int count = 0;
00484 QString chanid, starttime, endtime, querystr;
00485 MSqlQuery query1(MSqlQuery::InitCon()), query2(MSqlQuery::InitCon());
00486
00487 querystr = "SELECT chanid, starttime, endtime FROM program "
00488 "WHERE (DATE_FORMAT(endtime,'%H%i') = '0000') "
00489 "ORDER BY chanid, starttime;";
00490
00491 if (!query1.exec(querystr))
00492 {
00493 VERBOSE(VB_IMPORTANT,
00494 QString("fix_end_times query failed: %1").arg(querystr));
00495 return -1;
00496 }
00497
00498 while (query1.next())
00499 {
00500 starttime = query1.value(1).toString();
00501 chanid = query1.value(0).toString();
00502 endtime = query1.value(2).toString();
00503
00504 querystr = QString("SELECT chanid, starttime, endtime FROM program "
00505 "WHERE (DATE_FORMAT(starttime, '%%Y-%%m-%%d') = "
00506 "'%1') AND chanid = '%2' "
00507 "ORDER BY starttime LIMIT 1;")
00508 .arg(endtime.left(10))
00509 .arg(chanid);
00510
00511 if (!query2.exec(querystr))
00512 {
00513 VERBOSE(VB_IMPORTANT,
00514 QString("fix_end_times query failed: %1").arg(querystr));
00515 return -1;
00516 }
00517
00518 if (query2.next() && (endtime != query2.value(1).toString()))
00519 {
00520 count++;
00521 endtime = query2.value(1).toString();
00522 querystr = QString("UPDATE program SET starttime = '%1', "
00523 "endtime = '%2' WHERE (chanid = '%3' AND "
00524 "starttime = '%4');")
00525 .arg(starttime)
00526 .arg(endtime)
00527 .arg(chanid)
00528 .arg(starttime);
00529
00530 if (!query2.exec(querystr))
00531 {
00532 VERBOSE(VB_IMPORTANT,
00533 QString("fix_end_times query failed: %1").arg(querystr));
00534 return -1;
00535 }
00536 }
00537 }
00538
00539 return count;
00540 }
