public static List<EventGroup> GetChildrenOf(EventGroup parent, SqlConnection conn) { string sql = "SELECT * FROM REQ_EventGroups WHERE ParentId "; if (parent == null) sql += "IS NULL OR ParentID=0"; else sql += " = " + parent.ID.ToString(); SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); List<EventGroup> output = new List<EventGroup>(); while (dr.Read()) { EventGroup newGroup = LoadFromReader(dr); newGroup.Parent = parent; output.Add(newGroup); } dr.Close(); foreach (EventGroup eventGroup in output) eventGroup.Children = GetChildrenOf(eventGroup, conn); return output; }
//get all the Event Group in SAP catalog private void LoadEventGroupSAP() { string EventGroupID = string.Empty; string ParentID = string.Empty; string Title = string.Empty; List<EventGroup> groups = new List<EventGroup>(); string sXMLInput = string.Empty; //DateTime dBeginDate = new DateTime(DateTime.Now.Year,1, 1); //DateTime dEndDate = new DateTime(9999, 12, 31); //string trxId = Request.QueryString["ID"].ToString(); // string sProfileID = LoadProfile(HttpContext.Current.User.Identity.Name, trxId); string sResultFromSAP = string.Empty; try { sXMLInput = "<COMM>" + "<REMARK>BAPI_BUS_EVENTGROUP_LIST</REMARK>" + "<UID>" + ConfigurationManager.AppSettings["UsernameAdmin"].ToString() + "</UID>" + "<PF>" + sProfileID + "</PF>" + "<RFC>BAPI_BUS_EVENTGROUP_LIST</RFC>" + "<R_IF>1</R_IF>" + "<R_OF>1</R_OF>" + "<R_IT>0</R_IT>" + "<R_OT>1</R_OT>" + "<INPUT>" + "<IFLD>" + "<OBJID>" + ConfigurationManager.AppSettings["TrainingGroup"].ToString() + "</OBJID>" + "<BEGIN_DATE>" + ConfigurationManager.AppSettings["BeginDate"].ToString() + "</BEGIN_DATE>" + "<END_DATE>" + ConfigurationManager.AppSettings["EndDate"].ToString() + "</END_DATE>" + "<PLVAR>01</PLVAR>" + "</IFLD>" + "<OFLD>" + "<RETURN>" + "<TRIGGER>1</TRIGGER>" + "</RETURN>" + "</OFLD>" + "<ITBL></ITBL>" + "<OTBL>" + "<EVENTGROUP_LIST></EVENTGROUP_LIST>" + "</OTBL>" + "</INPUT>" + "</COMM>"; oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventGroupSAP", sXMLInput, "BAPI_BUS_EVENTGROUP_LIST"); if (oSAP.ExeProc(sXMLInput, ConfigurationManager.ConnectionStrings["ConnStr1"].ToString()) == true) { sResultFromSAP = oSAP.RETMSG.ToString(); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.LoadXml(sResultFromSAP); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/COMM/OTBL/EVENTGROUP_LIST/DATA"); string sTYPE = xmlDocument.GetElementsByTagName("TYPE").Item(0).InnerText.ToUpper(); if (sTYPE.ToUpper() != "E") //SUCCESSFULL { if (xmlNodeList.Count > 0) { foreach (XmlNode xmlNode in xmlNodeList) { EventGroupID = xmlNode["EGRID"].InnerText.ToString(); ParentID = xmlNode["FATHER"].InnerText.ToString(); Title = xmlNode["EGSTX"].InnerText.ToString(); EventGroup newGroup = new EventGroup(Convert.ToInt32(EventGroupID), Convert.ToInt32(ParentID), Title); groups.Add(newGroup); } } using (UtilityDb db = new UtilityDb()) { db.OpenConnectionESS(); db.PrepareInsert("REQ_EventGroups"); foreach (EventGroup item in groups) { DataRow row = db.Insert(null); row["ID"] = item.ID; row["TITLE"] = item.Title.ToString(); if (item.ParentID.HasValue) row["ParentID"] = item.ParentID.Value; else row["ParentID"] = DBNull.Value; db.Insert(row); } db.EndInsert(); } } } } catch (Exception ex) { oCore.LogEvent("ImportSAPTraining.aspx", "LoadEventGroupSAP", "Catch", "BAPI_BUS_EVENTGROUP_LIST"); } finally { } }
private static EventGroup LoadFromReader(SqlDataReader dr) { int id = 0; int parentID = 0; string text = ""; EventGroup output = new EventGroup(id, parentID, text); output.ID = (int)dr["ID"]; output.Title = dr["Title"].ToString(); return output; }
/// <summary> /// Get all available events in a eventObj group. /// </summary> /// <param name="eventGroup"></param> /// <returns></returns> public static List<TrainingEvent> GetAvailableEvents(EventGroup eventGroup, string staffUsername, string searchTitle, bool checkAgainstPrebookingTable) { List<int> eventGroupIds = new List<int>(); CollectTreeIds(eventGroupIds, eventGroup); string sql = "SELECT * FROM REQ_Events AS C WHERE EXISTS " + "(SELECT * FROM REQ_EventsInGroups AS CG WHERE CG.EventId = C.ID "; // add in clause for the event groups if (eventGroupIds.Count > 0) { string inClause = ""; int i = 0; foreach (int groupId in eventGroupIds) { if (i > 0) inClause += ","; inClause += groupId.ToString(); i++; } inClause = "(" + inClause + ")"; sql += " AND CG.GroupId IN " + inClause; } sql += ") "; // if search term is specified if (!String.IsNullOrEmpty(searchTitle)) { sql += " AND C.Title LIKE '%" + searchTitle + "%' "; } // filter out events that have been pre-booked by this staff if (checkAgainstPrebookingTable) { string bookingTable = "REQ_PrebookedEvents"; if (!string.IsNullOrEmpty(staffUsername)) { sql += string.Format(" AND NOT EXISTS(SELECT * FROM {0} AS PB WHERE PB.StaffUsername='******' AND PB.EventID=C.ID) ", bookingTable, staffUsername); } sql += " ORDER BY C.Title"; } // fetch output from database from the sql generated above. List<TrainingEvent> output = new List<TrainingEvent>(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { TrainingEvent eventObj = new TrainingEvent(); eventObj.LoadFromReader(dr); output.Add(eventObj); } } return output; }
/// <summary> /// Collect all Ids in a eventgroup and its children and grandchildren /// </summary> /// <param name="eventGroupIds"></param> /// <param name="eventGroup"></param> private static void CollectTreeIds(List<int> eventGroupIds, EventGroup eventGroup) { eventGroupIds.Add(eventGroup.ID); foreach (EventGroup child in eventGroup.Children) CollectTreeIds(eventGroupIds, child); }
internal static List<TrainingEvent> GetAvailableEventsWithDates(EventGroup selectedEventGroup, string staffUsername, string searchTitle, DateTime fromDate, DateTime toDate) { List<TrainingEvent> output = GetAvailableEvents(selectedEventGroup, staffUsername, searchTitle, false); // attach dates to the events foreach (TrainingEvent parentEvent in output) parentEvent.AttachEventDates(fromDate, toDate, null, false, true, staffUsername); return output; }
public static List<TrainingEvent> GetByDate(EventGroup group, DateTime? dateFrom, DateTime? dateTo) { List<TrainingEvent> output = new List<TrainingEvent>(); return output; }