/*----------------------------------------------------------------------------
* %%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));
}
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));
}
/* 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());
}
/* G E T C S V */
/*----------------------------------------------------------------------------
* %%Function: GetCsv
* %%Qualified: RwpSvc.Practice:RwpSlots.GetCsv
* %%Contact: rlittle
*
* ----------------------------------------------------------------------------*/
public SR GetCsv(Stream stm)
{
CsvSlots csvs = new CsvSlots();
SqlWhere sw = new SqlWhere();
TextWriter tw = new StreamWriter(stm);
sw.AddAliases(RwpSlot.s_mpAliases);
m_plrwps = new List <RwpSlot>();
SR sr = Sql.ExecuteQuery(sw.GetWhere(RwpSlot.s_sSqlQueryString), this, _sResourceConnString);
tw.WriteLine(csvs.Header());
foreach (RwpSlot rwps in m_plrwps)
{
tw.WriteLine(csvs.CsvMake(rwps));
}
tw.Flush();
return(SR.Success());
}
/* G E T C S V */
/*----------------------------------------------------------------------------
* %%Function: GetCsv
* %%Qualified: RwpSvc.Practice:Teams.GetCsv
* %%Contact: rlittle
*
* ----------------------------------------------------------------------------*/
public RSR GetCsv(Stream stm)
{
CsvTeams csvt = new CsvTeams();
SqlWhere sw = new SqlWhere();
TextWriter tw = new StreamWriter(stm);
sw.AddAliases(RwpTeam.s_mpAliases);
m_plrwpt = new List <RwpTeam>();
// StringBuilder sb = new StringBuilder(4096);
RSR sr = RSR.FromSR(Sql.ExecuteQuery(sw.GetWhere(RwpTeam.s_sSqlQueryString), this, Startup._sResourceConnString));
tw.WriteLine(csvt.Header());
foreach (RwpTeam rwpt in m_plrwpt)
{
tw.WriteLine(csvt.CsvMake(rwpt));
}
tw.Flush();
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();
}
/* G E T C S V */
/*----------------------------------------------------------------------------
%%Function: GetCsv
%%Qualified: RwpSvc.Practice:Teams.GetCsv
%%Contact: rlittle
----------------------------------------------------------------------------*/
public RSR GetCsv(Stream stm)
{
CsvTeams csvt = new CsvTeams();
SqlWhere sw = new SqlWhere();
TextWriter tw = new StreamWriter(stm);
sw.AddAliases(RwpTeam.s_mpAliases);
m_plrwpt = new List<RwpTeam>();
// StringBuilder sb = new StringBuilder(4096);
RSR sr = RSR.FromSR(Sql.ExecuteQuery(sw.GetWhere(RwpTeam.s_sSqlQueryString), this, _sResourceConnString));
tw.WriteLine(csvt.Header());
foreach (RwpTeam rwpt in m_plrwpt)
tw.WriteLine(csvt.CsvMake(rwpt));
tw.Flush();
return RSR.Success();
}