public List <StadiumInfo> InitStadiumInfos()
{
List <StadiumInfo> Infos = new List <StadiumInfo>();
StringBuilder sql_select = new StringBuilder();
sql_select.Append("select a.ID,b.Description,a.F_ZJRQ,a.F_Days,F_Columns from sys_biz_reminder_stadiumInfo as a,sys_biz_reminder_stadiummodel as b where a.ID = b.ID");
DataTable Data = GetDataTable(sql_select.ToString());
if (Data != null)
{
foreach (DataRow Row in Data.Rows)
{
String Index = Row["ID"].ToString();
String Description = Row["Description"].ToString();
String F_ZJRQ = Row["F_ZJRQ"].ToString();
String F_Days = Row["F_Days"].ToString();
String F_Columns = Row["F_Columns"].ToString();
StadiumInfo stadiumInfo = new StadiumInfo();
stadiumInfo.Index = Index;
stadiumInfo.Description = Description;
stadiumInfo.F_ZJRQ = F_ZJRQ;
stadiumInfo.F_List = F_Days;
stadiumInfo.F_Columns.AddRange(F_Columns.Split('^'));
Infos.Add(stadiumInfo);
}
}
return(Infos);
}
public StadiumInfo InitStadiumInfo(String Index)
{
StadiumInfo stadiumInfo = null;
StringBuilder sql_select = new StringBuilder();
sql_select.Append("select * from sys_biz_reminder_stadiumInfo");
DataTable Data = GetDataTable(sql_select.ToString());
if (Data != null)
{
foreach (DataRow Row in Data.Rows)
{
String ID = Row["ID"].ToString();
String Description = Row["Description"].ToString();
String F_ZJRQ = Row["F_ZJRQ"].ToString();
String F_Days = Row["F_Days"].ToString();
String F_Columns = Row["F_Columns"].ToString();
stadiumInfo = new StadiumInfo();
stadiumInfo.Index = ID;
stadiumInfo.Description = Description;
stadiumInfo.F_ZJRQ = F_ZJRQ;
stadiumInfo.F_List = F_Days;
stadiumInfo.F_Columns.AddRange(F_Columns.Split('^'));
}
}
return(stadiumInfo);
}
public ActionResult Fixturedetails()
{
// extract the stadium id
string stadium = Request.QueryString["stadium"];
string userorigin = Request.QueryString["location"];
string modeoftravel = Request.QueryString["travelmode"];
string href = Request.QueryString["href"];
// need to lookup the event info, keyed against the given href
Event e = this.LookupEvent(href);
if (e != null)
{
// convert to an int (since db lookup requires an int
int stadiumId = Convert.ToInt32(stadium);
StadiumInfo stadiuminfo = db.StadiumInfos.Find(stadiumId);
ViewBag.stadium = stadiuminfo;
ViewBag.userorigin = userorigin;
ViewBag.modeoftravel = modeoftravel;
ViewBag.e = e;
return(View());
}
else
{
// the event is null (wasn't found in the view.
// show the user some sort of error page?
return(RedirectToAction("home", "FixtureFinder"));
//return Home(); // redirect them to the home page!
}
}
public List <Event> populateStadiumInformation(List <Event> events)
{
FixtureFinderEntities1 premierleaguegrounds = new FixtureFinderEntities1();
// returns all of the stadiums
//List<StadiumInfo> stadiums = premierleaguegrounds.StadiumInfos.ToList();
// returns a list of stadiums where the home team is Liverpool
//var liverpoolStadiums = premierleaguegrounds.StadiumInfos.Where(stadiumInfo => stadiumInfo.TeamName == "Liverpool");
// returns a single stadium where the home team is Liverpool
//StadiumInfo liverpoolstadium = premierleaguegrounds.StadiumInfos.Single(s => s.TeamName == "Liverpool");
// scroll through and populate each event in the events list
foreach (Event eventObject in events)
{
// start of the try block. Any exceptions thrown in here ...
try
{
// searching for each stadium
System.Diagnostics.Debug.WriteLine("Searching for stadium of: " + eventObject.hometeam);
StadiumInfo stadium = premierleaguegrounds.StadiumInfos.Single(s => s.TeamName == eventObject.hometeam);
//StadiumInfo stadium = premierleaguegrounds.StadiumInfos.Single(s => s.TeamName.Contains(eventObject.hometeam));
//StadiumInfo stadium = premierleaguegrounds.StadiumInfos.Single(s => eventObject.hometeam.Contains(s.TeamName));
eventObject.location = stadium.StadiumName;
eventObject.locationLatitude = stadium.Latitude;
eventObject.locationLongitude = stadium.Longitude;
eventObject.homenickname = stadium.TeamNickName;
eventObject.stadiumcapacity = Convert.ToInt32(stadium.StadiumCapacity);
eventObject.description = stadium.StadiumDescription;
eventObject.picture = stadium.StadiumPicture;
eventObject.homecrest = stadium.ClubCrest;
eventObject.website = stadium.WebAddress;
eventObject.ticketoffice = stadium.TicketOffice;
eventObject.address = stadium.Address;
stadium = premierleaguegrounds.StadiumInfos.Single(s => s.TeamName == eventObject.awayteam);
eventObject.awaycrest = stadium.ClubCrest;
eventObject.awaynickname = stadium.TeamNickName;
// ... will be caught here ...
} catch (InvalidOperationException e) // this is saying catch InvalidOperationExceptions and assign the error to 'e' within the code (so you can reference it)
{
// catch block. This code will only run if an exception was thrown in the try block
System.Diagnostics.Debug.WriteLine("failed to find stadium for team: " + eventObject.hometeam + ". Are you sure that's how it's spelt in the database?");
eventObject.location = "Unknown"; // set the default stadium name here. Maybe null instead of Unknown?
}
}
return(events);
}
public Boolean UpdateStadiumInfo(StadiumInfo Info)
{
Boolean Result = false;
StringBuilder sql_select = new StringBuilder();
sql_select.Append("select * from sys_biz_reminder_stadiumInfo where ID='");
sql_select.Append(Info.Index);
sql_select.Append("'");
DataTable Data = GetDataTable(sql_select.ToString());
if (Data != null)
{
DataRow Row = null;
if (Data.Rows.Count > 0)
{
Row = Data.Rows[0];
}
else
{
Row = Data.NewRow();
Data.Rows.Add(Row);
}
Row["ID"] = Info.Index;
Row["SCTS"] = DateTime.Now.ToString();
Row["F_ZJRQ"] = Info.F_ZJRQ;
Row["F_Days"] = Info.F_List;
Row["F_Columns"] = string.Join("^", Info.F_Columns.ToArray());
try
{
int r = Update(Data);
Result = (r == 1);
}
catch
{
}
}
return(Result);
}
private StringBuilder GetSQLCommand(StadiumInfo Info, String TestRoomCode)
{
List <string> sql_Tables = new List <string>();
List <String> sql_Fields = new List <string>();
String[] Tokens;
foreach (String field in Info.F_Columns)
{
String ItemName = field.Substring(0, field.IndexOf(','));
String ItemValue = field.Substring(field.IndexOf(',')).Trim(',');
if (ItemValue != "")
{
if (ItemName != "试验项目")
{
Tokens = ItemValue.Split('.');
sql_Fields.Add(string.Format("[{0}].[{1}] as {2}", Tokens[0], Tokens[1], ItemName));
if (!sql_Tables.Contains(string.Format("[{0}]", Tokens[0])))
{
sql_Tables.Add(string.Format("[{0}]", Tokens[0]));
}
}
else
{
if (ItemValue.Trim().StartsWith("{") && ItemValue.Trim().EndsWith("}"))
{
StringBuilder sql_field_F_ItemId = new StringBuilder();
StringBuilder sql_field_F_Name = new StringBuilder();
sql_field_F_ItemId.Append("'ItemId' = Case ");
sql_field_F_Name.Append(string.Format("'{0}' = Case ", ItemName));
String[] itemIdList = ItemValue.Trim('{', '}').Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
String[] lqList = Info.F_List.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string lq in lqList)
{
int k = Array.IndexOf(lqList, lq);
sql_field_F_ItemId.Append(string.Format(" When {0}='{1}' Then '{2}' ", "Convert(varchar(10),DateDiff(Day," + Info.F_ZJRQ + ",GetDate()))", lq, itemIdList[k]));
sql_field_F_Name.Append(string.Format(" When {0}='{1}' Then (select ItemName from sys_biz_reminder_testitem where ID='{2}') ", "Convert(varchar(10),DateDiff(Day," + Info.F_ZJRQ + ",GetDate()))", lq, itemIdList[k]));
}
sql_field_F_ItemId.Append(" End ");
sql_field_F_Name.Append(" End ");
sql_Fields.Insert(0, sql_field_F_ItemId.ToString());
sql_Fields.Add(sql_field_F_Name.ToString());
}
else
{
Tokens = Info.F_List.Split('.');
if (!sql_Tables.Contains(string.Format("[{0}]", Tokens[0])))
{
sql_Tables.Add(string.Format("[{0}]", Tokens[0]));
}
sql_Fields.Insert(0, string.Format("{0} as ItemId", ItemValue));
sql_Fields.Add(string.Format("(select ItemName from sys_biz_reminder_testitem where ID=({0})) as {1}", ItemValue.Trim('\''), ItemName));
}
}
}
else
{
sql_Fields.Add(string.Format("'' as {0}", ItemName));
}
}
//Tokens = Info.F_ZJRQ.Split(new char[] { '.' });
//sql_Fields.Add(string.Format("[{0}].[{1}] as 试验日期", Tokens[0], Tokens[1]));
//if (!sql_Tables.Contains(string.Format("[{0}]", Tokens[0])))
// sql_Tables.Add(string.Format("[{0}]", Tokens[0]));
List <string> sql_Wheres = new List <string>();
Tokens = Info.F_List.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in Tokens)
{
sql_Wheres.Add(string.Format("DateDiff(Day,{0},GetDate()) = Convert(int,Replace({1},'d',''))", Info.F_ZJRQ, s));
}
List <string> sql_Relations = new List <string>();
if (sql_Tables.Count > 1)
{
string left = sql_Tables[0];
foreach (String table in sql_Tables)
{
if (table == left)
{
continue;
}
sql_Relations.Add(string.Format("{0}.ID = {1}.ID", left, table));
}
}
StringBuilder sql_select = new StringBuilder();
if (sql_Fields.Count > 0 && sql_Tables.Count > 0 && sql_Wheres.Count > 0)
{
String ID = String.Format("{0}.ID", sql_Tables[0]);
String ModelCode = String.Format("{0}.SCPT as ModelCode", sql_Tables[0]);
String ModelIndex = String.Format("'{0}' as ModelIndex", Info.Index);
String DateSpan = string.Format("DateDiff(Day,{0},GetDate()) as DateSpan", Info.F_ZJRQ);
String SCTS = String.Format("{0}.SCTS", sql_Tables[0]);
String Date = String.Format("({0} between DATEADD(\"Day\", -30,GETDATE()) and GetDate())", SCTS);
String TestRoom = String.Format("{0}.SCPT like '{1}____'", sql_Tables[0], TestRoomCode);
sql_select.Append("select ");
sql_select.Append(ID);
sql_select.Append(",");
sql_select.Append(ModelCode);
sql_select.Append(",");
sql_select.Append(ModelIndex);
sql_select.Append(",");
sql_select.Append(DateSpan);
sql_select.Append(",");
sql_select.Append(string.Join(",", sql_Fields.ToArray()));
sql_select.Append(" from ");
sql_select.Append(string.Join(",", sql_Tables.ToArray()));
//sql_select.Append(",v_codeName as a1,v_codeName as a2,v_codeName as a3 ");
sql_select.Append(" where ");
sql_select.Append(Date);
sql_select.Append(" and ");
sql_select.Append(string.Concat("(", string.Join(" or ", sql_Wheres.ToArray()), ")"));
sql_select.Append(" and ");
sql_select.Append(TestRoom);
if (sql_Relations.Count > 0)
{
sql_select.Append(" and ");
sql_select.Append(string.Join(" and ", sql_Relations.ToArray()));
}
}
return(sql_select);
}
public static Boolean UpdateStadiumInfo(StadiumInfo info)
{
return(Convert.ToBoolean(Agent.CallService("Yqun.BO.ReminderManager.dll", "UpdateStadiumInfo", new object[] { info })));
}
