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;
}