public static USR_DvdInfoList QueryDvdScanInfos(
string sTitleSubstring,
string sSummarySubstring,
DateTime?dttmSince)
{
SqlWhere sqlw = new SqlWhere();
sqlw.AddAliases(s_mpDvdAlias);
if (!string.IsNullOrEmpty(sTitleSubstring))
{
sqlw.Add(String.Format("$$upc_dvd$$.Title like '%{0}%'", Sql.Sqlify(sTitleSubstring)), SqlWhere.Op.And);
}
if (!string.IsNullOrEmpty(sSummarySubstring))
{
sqlw.Add(String.Format("$$upc_dvd$$.Summary like '%{0}%'", Sql.Sqlify(sSummarySubstring)), SqlWhere.Op.And);
}
if (dttmSince != null)
{
sqlw.Add(String.Format("$$upc_codes$$.FirstScanDate > '{0}'", UpcBook.ToSqlDateTime(dttmSince.Value)), SqlWhere.Op.And);
}
string sFullQuery = String.Format("SELECT {0}", sqlw.GetWhere(s_sQueryDvd));
return(Shared.DoGenericQueryDelegateRead(sFullQuery, ReaderGetDvdScanInfoListDelegate, USR_DvdInfoList.FromTCSR));
}
/*----------------------------------------------------------------------------
* %%Function: GetBookScanInfosFromTitle
* %%Qualified: UpcApi.UpcBook.GetBookScanInfosFromTitle
*
* Get the list of matching bookInfo items for the given title substring
* ----------------------------------------------------------------------------*/
public static USR_BookInfoList QueryBookScanInfos(
string sTitleSubstring,
string sAuthorSubstring,
string sSeriesSubstring,
string sSummarySubstring,
DateTime?dttmSince)
{
SqlWhere sqlw = new SqlWhere();
sqlw.AddAliases(s_mpBookAlias);
if (sTitleSubstring != null)
{
sqlw.Add(String.Format("$$upc_books$$.Title like '%{0}%'", Sql.Sqlify(sTitleSubstring)), SqlWhere.Op.And);
}
if (sAuthorSubstring != null)
{
sqlw.Add(String.Format("$$upc_books$$.Author like '%{0}%'", Sql.Sqlify(sAuthorSubstring)), SqlWhere.Op.And);
}
if (sSeriesSubstring != null)
{
sqlw.Add(String.Format("$$upc_books$$.Series like '%{0}%'", Sql.Sqlify(sSeriesSubstring)), SqlWhere.Op.And);
}
if (sSummarySubstring != null)
{
sqlw.Add(String.Format("$$upc_books$$.Summary like '%{0}%'", Sql.Sqlify(sSummarySubstring)), SqlWhere.Op.And);
}
if (dttmSince != null)
{
sqlw.Add(String.Format("$$upc_codes$$.FirstScanDate > '{0}'", ToSqlDateTime(dttmSince.Value)), SqlWhere.Op.And);
}
string sFullQuery = String.Format("SELECT {0}", sqlw.GetWhere(s_sQueryBook));
return(Shared.DoGenericQueryDelegateRead(sFullQuery, ReaderGetBookScanInfoListDelegate, USR_BookInfoList.FromTCSR));
}
/* L O A D R W P T F R O M C S V */
/*----------------------------------------------------------------------------
* %%Function: LoadRwptFromCsv
* %%Qualified: RwpSvc.Practice:RwpSlots:CsvSlots.LoadRwptFromCsv
* %%Contact: rlittle
*
* ----------------------------------------------------------------------------*/
public RSR LoadRwpsFromCsv(string sLine, Sql sql, out RwpSlot rwps, out bool fAdd, out List <string> plsDiff)
{
string [] rgs = LineToArray(sLine);
SqlWhere sw = new SqlWhere();
fAdd = true;
plsDiff = new List <string>();
rwps = null;
if (rgs[0] == "")
{
return(RSR.Success());
}
rwps = new RwpSlot();
sw.AddAliases(RwpSlot.s_mpAliases);
try
{
rwps.Slot = GetIntVal(rgs, "SLOTNO");
sw.Add(String.Format("$$rwllpractice$$.SlotNo = {0}", rwps.Slot), SqlWhere.Op.And);
SqlReader sqlr = new SqlReader(sql);
if (sqlr.FExecuteQuery(sw.GetWhere(RwpSlot.s_sSqlQueryString), _sResourceConnString) &&
sqlr.Reader.Read())
{
sqlr.Close();
// found a match. for now, this is an error
throw new Exception(String.Format("slot {0} already exists", rwps.Slot));
}
sqlr.Close();
rwps.Slot = GetIntVal(rgs, "SLOTNO");
rwps.Week = GetDoubleVal(rgs, "WEEK");
rwps.Status = GetStringVal(rgs, "STATUS");
rwps.Venue = GetStringVal(rgs, "VENUE");
rwps.Field = GetStringVal(rgs, "FIELD");
rwps.SlotDate = GetDateVal(rgs, "DATE");
rwps.Weekday = GetStringVal(rgs, "WEEKDAY");
rwps.StartTime = GetStringVal(rgs, "STARTTIME");
rwps.EndTime = GetStringVal(rgs, "ENDTIME");
rwps.Hours = GetStringVal(rgs, "HOURS");
rwps.Reserved = GetStringVal(rgs, "RESERVED");
rwps.Divisions = GetStringVal(rgs, "DIVISIONS");
rwps.ReservedDate = GetDateValNullable(rgs, "RESERVEDATETIME");
rwps.Type = GetStringVal(rgs, "TYPE");
rwps.Released = GetDateValNullable(rgs, "RELEASEDATETIME");
rwps.ReleaseTeam = GetStringValNullable(rgs, "RELEASETEAM");
}
catch (Exception e)
{
return(RSR.Failed(e));
}
return(RSR.Success());
}
/*----------------------------------------------------------------------------
* %%Function: GetDvdScanInfosFromTitle
* %%Qualified: UpcSvc.UpcSvc.GetDvdScanInfosFromTitle
* %%Contact: rlittle
*
* Get the list of matching dvdInfo items for the given title substring
* ----------------------------------------------------------------------------*/
public static USR_DvdInfoList GetDvdScanInfosFromTitle(string sTitleSubstring)
{
SqlWhere sqlw = new SqlWhere();
sqlw.AddAliases(s_mpDvdAlias);
sqlw.Add(String.Format("$$upc_dvd$$.Title like '%{0}%'", Sql.Sqlify(sTitleSubstring)), SqlWhere.Op.And);
string sFullQuery = String.Format("SELECT {0}", sqlw.GetWhere(s_sQueryDvd));
return(Shared.DoGenericQueryDelegateRead(sFullQuery, ReaderGetDvdScanInfoListDelegate, USR_DvdInfoList.FromTCSR));
}
/*----------------------------------------------------------------------------
* %%Function: GetFullDvdScanInfo
* %%Qualified: UpcSvc.UpcSvc.GetFullDvdScanInfo
*
* Get the information for the DVD for the given scancode
* ----------------------------------------------------------------------------*/
public static USR_DvdInfoEx GetFullDvdScanInfo(string sScanCode)
{
SqlWhere sqlw = new SqlWhere();
sqlw.AddAliases(s_mpDvdAlias);
sqlw.Add(String.Format("$$upc_codes$$.ScanCode='{0}'", Sql.Sqlify(sScanCode)), SqlWhere.Op.And);
string sFullQuery = String.Format("SELECT {0}", sqlw.GetWhere(s_sQueryDvdEx));
return(Shared.DoGenericQueryDelegateRead(sFullQuery, ReaderGetDvdScanInfoExDelegate, USR_DvdInfoEx.FromTCSR));
}
public static RSR_CalItems GetCalendarItemsForTeam(string sLinkID)
{
SqlWhere sw = new SqlWhere();
RSR rsr;
RSR_CalItems rci;
RwpSlots slots = new RwpSlots();
sw.AddAliases(RwpSlot.s_mpAliases);
try
{
sw.Add(String.Format("$$rwllpractice$$.Reserved = (select TeamID from rwllcalendarlinks where linkid='{0}')", Sql.Sqlify(sLinkID)), SqlWhere.Op.And);
rsr = RSR.FromSR(Sql.ExecuteQuery(null, sw.GetWhere(RwpSlot.s_sSqlQueryString), slots,
Startup._sResourceConnString));
if (!rsr.Succeeded)
{
rci = RSR_CalItems.FromRSR(rsr);
rci.Reason = String.Format("{0} {1}", rci.Reason, Startup._sResourceConnString);
return(rci);
}
rci = RSR_CalItems.FromRSR(RSR.FromSR(SR.Success()));
List <CalItem> plci = new List <CalItem>();
if (slots.Slots != null)
{
foreach (RwpSlot slot in slots.Slots)
{
CalItem ci = new CalItem();
ci.Start = slot.SlotStart;
ci.End = slot.SlotStart.AddMinutes(slot.SlotLength);
ci.Location = String.Format("{0}: {1}", slot.Venue, slot.Field);
ci.Title = String.Format("Team Practice: {0}", slot.Reserved);
ci.Description =
String.Format("Redmond West Little League team practice at {0} ({1}), for team {2}",
slot.Venue, slot.Field, slot.Reserved);
ci.UID = String.Format("{0}-rwllpractice-{1}", slot.Slot, slot.SlotStart.ToString("yyyyMMdd"));
plci.Add(ci);
}
}
rci.TheValue = plci;
return(rci);
}
catch (Exception e)
{
rci = RSR_CalItems.FromRSR(RSR.Failed(e));
rci.Reason = String.Format("{0} ({1})", rci.Reason, sLinkID);
return(rci);
}
}
/*----------------------------------------------------------------------------
* %%Function: GetCalendarLinksForTeam
* %%Qualified: RwpApi.CalendarLinks.GetCalendarLinksForTeam
* %%Contact: rlittle
*
* ----------------------------------------------------------------------------*/
public static RSR_CalendarLinks GetCalendarLinksForTeam(string sTeam)
{
SqlWhere sw = new SqlWhere();
RSR rsr;
RSR_CalendarLinks rci;
CalendarLinks links = new CalendarLinks();
sw.AddAliases(CalendarLinkItem.s_mpAliases);
try
{
sw.Add(String.Format("$$rwllcalendarlinks$$.TeamID = '{0}'", Sql.Sqlify(sTeam)), SqlWhere.Op.And);
rsr = RSR.FromSR(Sql.ExecuteQuery(null, sw.GetWhere(RwpSlots.RwpSlot.s_sSqlQueryString), links,
Startup._sResourceConnString));
if (!rsr.Succeeded)
{
rci = RSR_CalendarLinks.FromRSR(rsr);
rci.Reason = String.Format("{0} {1}", rci.Reason, Startup._sResourceConnString);
return(rci);
}
rci = RSR_CalendarLinks.FromRSR(RSR.FromSR(SR.Success()));
List <CalendarLink> plcall = new List <CalendarLink>();
if (links.Links != null)
{
foreach (CalendarLinkItem linkItem in links.Links)
{
CalendarLink link = new CalendarLink()
{
Link = linkItem.Link, Team = linkItem.Team, Authority = linkItem.Authority,
CreateDate = linkItem.CreateDate, Comment = linkItem.Comment
};
plcall.Add(link);
}
}
rci.TheValue = plcall;
return(rci);
}
catch (Exception e)
{
rci = RSR_CalendarLinks.FromRSR(RSR.Failed(e));
rci.Reason = String.Format("{0} ({1})", rci.Reason, sTeam);
return(rci);
}
}
/* L O A D R W P T F R O M C S V */
/*----------------------------------------------------------------------------
* %%Function: LoadRwptFromCsv
* %%Qualified: RwpSvc.Practice:Teams:CsvTeams.LoadRwptFromCsv
* %%Contact: rlittle
*
* ----------------------------------------------------------------------------*/
public RSR LoadRwptFromCsv(string sLine, TCore.Sql sql, out RwpTeam rwpt, out bool fAdd, out List <string> plsDiff)
{
string [] rgs = LineToArray(sLine);
SqlWhere sw = new SqlWhere();
fAdd = true;
rwpt = new RwpTeam();
plsDiff = new List <string>();
sw.AddAliases(RwpTeam.s_mpAliases);
try
{
rwpt.Name = GetStringVal(rgs, "TEAMNAME");
if (rwpt.Name == "")
{
return(RSR.Success());
}
sw.Add(String.Format("$$rwllteams$$.TeamName = '{0}'", Sql.Sqlify(rwpt.Name)), SqlWhere.Op.And);
SqlReader sqlr = new SqlReader(sql);
if (sqlr.FExecuteQuery(sw.GetWhere(RwpTeam.s_sSqlQueryString), _sResourceConnString) &&
sqlr.Reader.Read())
{
sqlr.Close();
// found a match. for now, this is an error
throw new Exception(String.Format("team name {0} already exists", rwpt.Name));
}
sqlr.Close();
rwpt.Division = GetStringValNullable(rgs, "DIVISION");
rwpt.Password = GetStringValNullable(rgs, "PW");
rwpt.Created = GetDateValNullable(rgs, "DATECREATED");
rwpt.Updated = GetDateValNullable(rgs, "DATEUPDATED");
rwpt.FieldsReleased = GetIntVal(rgs, "FIELDSRELEASECOUNT");
rwpt.CagesReleased = GetIntVal(rgs, "CAGESRELEASECOUNT");
rwpt.FieldsReleasedToday = GetIntVal(rgs, "RELEASEDFIELDSTODAY");
rwpt.CagesReleasedToday = GetIntVal(rgs, "RELEASEDCAGESTODAY");
rwpt.ReleasedFieldsDate = GetDateValNullable(rgs, "RELEASEDFIELDSDATE");
rwpt.ReleasedCagesDate = GetDateValNullable(rgs, "RELEASEDCAGESDATE");
rwpt.Email1 = GetStringValNullable(rgs, "EMAIL1");
rwpt.Email2 = GetStringValNullable(rgs, "EMAIL2");
}
catch (Exception e)
{
return(RSR.Failed(e));
}
return(RSR.Success());
}
/* L O A D R W P T F R O M C S V */
/*----------------------------------------------------------------------------
%%Function: LoadRwptFromCsv
%%Qualified: RwpSvc.Practice:Teams:CsvTeams.LoadRwptFromCsv
%%Contact: rlittle
----------------------------------------------------------------------------*/
public RSR LoadRwptFromCsv(string sLine, TCore.Sql sql, out RwpTeam rwpt, out bool fAdd, out List<string> plsDiff)
{
string []rgs = LineToArray(sLine);
SqlWhere sw = new SqlWhere();
fAdd = true;
rwpt = new RwpTeam();
plsDiff = new List<string>();
sw.AddAliases(RwpTeam.s_mpAliases);
try
{
rwpt.Name = GetStringVal(rgs, "TEAMNAME");
if (rwpt.Name == "")
return RSR.Success();
sw.Add(String.Format("$$rwllteams$$.TeamName = '{0}'", Sql.Sqlify(rwpt.Name)), SqlWhere.Op.And);
SqlReader sqlr = new SqlReader(sql);
if (sqlr.FExecuteQuery(sw.GetWhere(RwpTeam.s_sSqlQueryString), _sResourceConnString)
&& sqlr.Reader.Read())
{
sqlr.Close();
// found a match. for now, this is an error
throw new Exception(String.Format("team name {0} already exists", rwpt.Name));
}
sqlr.Close();
rwpt.Division = GetStringValNullable(rgs, "DIVISION");
rwpt.Password = GetStringValNullable(rgs, "PW");
rwpt.Created = GetDateValNullable(rgs, "DATECREATED");
rwpt.Updated = GetDateValNullable(rgs, "DATEUPDATED");
rwpt.FieldsReleased = GetIntVal(rgs, "FIELDSRELEASECOUNT");
rwpt.CagesReleased = GetIntVal(rgs, "CAGESRELEASECOUNT");
rwpt.FieldsReleasedToday = GetIntVal(rgs, "RELEASEDFIELDSTODAY");
rwpt.CagesReleasedToday = GetIntVal(rgs, "RELEASEDCAGESTODAY");
rwpt.ReleasedFieldsDate = GetDateValNullable(rgs, "RELEASEDFIELDSDATE");
rwpt.ReleasedCagesDate = GetDateValNullable(rgs, "RELEASEDCAGESDATE");
rwpt.Email1 = GetStringValNullable(rgs, "EMAIL1");
rwpt.Email2 = GetStringValNullable(rgs, "EMAIL2");
}
catch (Exception e)
{
return RSR.Failed(e);
}
return RSR.Success();
}
// 0 1 2 3 4 5 6 7 8
public void DoUpdate(string sConnectionString)
{
// get the set of books that we want to update
TCore.Sql sql;
SqlSelect sqls = new SqlSelect(s_sBaseQuery, s_mpAliases);
SqlWhere swInnerJoin = new SqlWhere();
swInnerJoin.Add("$$upc_dvd$$.ScanCode = $$upc_codes$$.ScanCode", SqlWhere.Op.And);
sqls.AddInnerJoin(new SqlInnerJoin("$$#upc_codes$$", swInnerJoin));
UpdateStatus flagsToUpdate = UpdateStatus.MediaType | UpdateStatus.Title | UpdateStatus.Categories | UpdateStatus.CoverSrc | UpdateStatus.Summary;
if (m_config.ForceUpdateSummary)
{
flagsToUpdate |= UpdateStatus.Summary;
}
// we want to match any entry that hasn't tried to update ALL of our fields. (if they have tried some,
// but not others, then try again. after all, we might know more fields now that we want to update.
sqls.Where.Add($"$$upc_dvd$$.UpdateStatus & {(int)flagsToUpdate} <> {(int)flagsToUpdate}", SqlWhere.Op.And);
sqls.Where.StartGroup(SqlWhere.Op.And);
sqls.Where.Add(String.Format("$$upc_dvd$$.LastUpdate < '{0}'", DateTime.Now.ToString("d")), SqlWhere.Op.And);
sqls.Where.Add("$$upc_dvd$$.LastUpdate IS NULL", SqlWhere.Op.Or);
sqls.Where.EndGroup();
// and lets update the latest scanned items first
sqls.AddOrderBy("$$upc_codes$$.LastScanDate DESC");
SR sr = Sql.OpenConnection(out sql, sConnectionString);
if (!sr.Succeeded)
{
throw new Exception(sr.Reason);
}
// from this point on, we have to release the sql connection!!
int cFailed = 0;
try
{
sr = Sql.ExecuteQuery(sql, sqls.ToString(), this, null);
if (!sr.Succeeded)
{
throw new Exception(sr.Reason);
}
foreach (DvdElementEx dvdex in m_plDvd)
{
string sError;
Console.WriteLine($"Trying to scrape dvd {dvdex.ScanCode}: {dvdex.Title}...");
DVD.ScrapeSet scrapedSet = 0;
if (m_config.ForceUpdateSummary)
{
dvdex.Summary = null;
}
if (DVD.FScrapeDvd(dvdex, out scrapedSet, out sError))
{
// we might not have scraped everything, but we scraped something...
if (scrapedSet.HasFlag(DVD.ScrapeSet.CoverSrc) && !DownloadCoverForDvd(dvdex, m_config.LocalCoverRoot, "covers", out sError))
{
Console.WriteLine($"FAILED: to download cover art: {sError}");
LogDvdUpdateError(dvdex, scrapedSet, sError);
}
else
{
Console.WriteLine(
$"SUCCEEDED: MediaType: {dvdex.MediaType}, Classification: {dvdex.Classification}");
AddDVDToUpdateQueue(dvdex, scrapedSet);
}
}
else
{
Console.WriteLine($"FAILED: {sError}");
LogDvdUpdateError(dvdex, scrapedSet, sError);
cFailed++;
if (cFailed > 150)
{
throw new Exception("giving up");
}
}
}
}
finally
{
sql.Close();
}
}