Пример #1
0
        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
               {
               }
        }
Пример #3
0
 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;
 }
Пример #4
0
        /// <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;
        }
Пример #5
0
 /// <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);
 }
Пример #6
0
        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;
        }
Пример #7
0
        public static List<TrainingEvent> GetByDate(EventGroup group, DateTime? dateFrom, DateTime? dateTo)
        {
            List<TrainingEvent> output = new List<TrainingEvent>();

            return output;
        }