/* 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()); }
/* S R F R O M P L S */ /*---------------------------------------------------------------------------- * %%Function: SRFromPls * %%Qualified: RwpSvc.Practice:Teams:RwpTeam.SRFromPls * %%Contact: rlittle * * ----------------------------------------------------------------------------*/ public static RSR SRFromPls(string sReason, List <string> plsDiff) { string s = sReason; foreach (string sItem in plsDiff) { s += sItem + ", "; } return(RSR.Failed(s)); }
public static RSR_CalItems GetCalendarItemsForTeam(string sTeam) { 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 = '{0}'", Sql.Sqlify(sTeam)), SqlWhere.Op.And); rsr = RSR.FromSR(Sql.ExecuteQuery(null, sw.GetWhere(RwpSlot.s_sSqlQueryString), slots, _sResourceConnString)); if (!rsr.Succeeded) { rci = RSR_CalItems.FromRSR(rsr); rci.Reason = String.Format("{0} {1}", rci.Reason, _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 = StartTimeFromDateAndTime(slot.SlotDate, slot.StartTime); ci.End = StartTimeFromDateAndTime(slot.SlotDate, slot.EndTime); 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.SlotDate.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, 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()); }
/* R E A D H E A D E R F R O M S T R I N G */ /*---------------------------------------------------------------------------- * %%Function: ReadHeaderFromString * %%Qualified: tw.twsvc:TwUser:Csv.ReadHeaderFromString * %%Contact: rlittle * * m_plsHeadings has the current set of headings that the database supports * * read in the heading line from the CSV file and validate what it wants * to upload * ----------------------------------------------------------------------------*/ public RSR ReadHeaderFromString(string sLine) { string[] rgs = LineToArray(sLine); m_mpColHeader = new Dictionary <int, string>(); m_mpHeaderCol = new Dictionary <string, int>(); int i = 0; foreach (string s in rgs) { if (!m_mpHeadingsMatch.ContainsKey(s.ToUpper())) { return(RSR.Failed(String.Format("header {0} not in database", s))); } m_mpColHeader.Add(i, s.ToUpper()); m_mpHeaderCol.Add(s.ToUpper(), i); i++; } return(RSR.Success()); }
/* I M P O R T C S V */ /*---------------------------------------------------------------------------- * %%Function: ImportCsv * %%Qualified: RwpSvc.Practice:Teams.ImportCsv * %%Contact: rlittle * * ----------------------------------------------------------------------------*/ public static RSR ImportCsv(Stream stm) { RSR sr; Sql sql; CsvTeams csv = new CsvTeams(); Random rnd = new Random(System.Environment.TickCount); TextReader tr = new StreamReader(stm); // process each line sr = RSR.FromSR(Sql.OpenConnection(out sql, _sResourceConnString)); if (!sr.Result) { return(sr); } sr = RSR.FromSR(sql.BeginTransaction()); if (!sr.Result) { return(sr); } bool fHeadingRead = false; string sLine; int iLine = 0; RwpTeam rwpt; bool fAdd; List <string> plsDiff; try { while ((sLine = tr.ReadLine()) != null) { iLine++; if (sLine.Length < 2) { continue; } if (!fHeadingRead) { sr = csv.ReadHeaderFromString(sLine); if (!sr.Result) { throw new Exception(String.Format("Failed to make heading at line {0}: {1}", iLine - 1, sr.Reason)); } fHeadingRead = true; continue; } sr = csv.LoadRwptFromCsv(sLine, sql, out rwpt, out fAdd, out plsDiff); if (!sr.Result) { throw new Exception(String.Format("Failed to process line {0}: {1}", iLine - 1, sr.Reason)); } if (rwpt.Name == "") { continue; } // at this point, rwpt is a fully loaded team; check for errors and generate a passowrd if necessary sr = rwpt.Preflight(sql); if (!sr.Result) { throw new Exception(String.Format("Failed to preflight line {0}: {1}", iLine - 1, sr.Reason)); } if (string.IsNullOrEmpty(rwpt.Password)) { rwpt.Password = RwpTeam.SGenRandomPassword(rnd, rwpt.Name); } if (rwpt.Created == null) { rwpt.Created = DateTime.Now; } if (rwpt.Updated == null) { rwpt.Updated = rwpt.Created; } if (rwpt.ReleasedCagesDate == null) { rwpt.ReleasedCagesDate = DateTime.Parse("1/1/2013"); } if (rwpt.ReleasedFieldsDate == null) { rwpt.ReleasedFieldsDate = DateTime.Parse("1/1/2013"); } // at this point, we would insert... string sInsert = rwpt.SGenerateUpdateQuery(sql, fAdd); SqlCommand sqlcmd = sql.CreateCommand(); sqlcmd.CommandText = sInsert; sqlcmd.Transaction = sql.Transaction; sqlcmd.ExecuteNonQuery(); } } catch (Exception e) { sql.Rollback(); sql.Close(); return(RSR.Failed(e)); } sql.Commit(); sql.Close(); return(RSR.Success()); }
/* I M P O R T C S V */ /*---------------------------------------------------------------------------- * %%Function: ImportCsv * %%Qualified: RwpSvc.Practice:RwpSlots.ImportCsv * %%Contact: rlittle * * ----------------------------------------------------------------------------*/ public static RSR ImportCsv(Stream stm) { RSR sr; Sql sql; CsvSlots csv = new CsvSlots(); TextReader tr = new StreamReader(stm); // process each line sr = RSR.FromSR(Sql.OpenConnection(out sql, _sResourceConnString)); if (!sr.Result) { return(sr); } sr = RSR.FromSR(sql.BeginTransaction()); if (!sr.Result) { return(sr); } bool fHeadingRead = false; string sLine; int iLine = 0; RwpSlot rwps; bool fAdd; List <string> plsDiff; try { while ((sLine = tr.ReadLine()) != null) { iLine++; if (sLine.Length < 2) { continue; } if (!fHeadingRead) { sr = csv.ReadHeaderFromString(sLine); if (!sr.Result) { throw new Exception(String.Format("Failed to make heading at line {0}: {1}", iLine - 1, sr.Reason)); } fHeadingRead = true; continue; } sr = csv.LoadRwpsFromCsv(sLine, sql, out rwps, out fAdd, out plsDiff); if (!sr.Result) { throw new Exception(String.Format("Failed to process line {0}: {1}", iLine - 1, sr.Reason)); } if (rwps == null) // this means it was an empty csv line { continue; } // at this point, rwps is a fully loaded team; check for errors and generate a passowrd if necessary sr = rwps.Preflight(sql); if (!sr.Result) { throw new Exception(String.Format("Failed to preflight line {0}: {1}", iLine - 1, sr.Reason)); } // at this point, we would insert... string sInsert = rwps.SGenerateUpdateQuery(sql, fAdd); SqlCommand sqlcmd = sql.CreateCommand(); sqlcmd.CommandText = sInsert; sqlcmd.Transaction = sql.Transaction; sqlcmd.ExecuteNonQuery(); } } catch (Exception e) { sql.Rollback(); sql.Close(); return(RSR.Failed(e)); } sql.Commit(); sql.Close(); return(RSR.Success()); }