Example #1
0
        //        public async Task<ActionResult<Person>> GetPerson(int id)
        public string Get(string id) //********************************************************
        {
            if (id == null)
            {
                return("Bad Get id");
            }

            id = id.Replace("@@", "*");

            String MatchingChurchesStr   = "Matching_Churches:";
            int    MatchingChurchesIndex = id.IndexOf(MatchingChurchesStr);

            if (MatchingChurchesIndex >= 0)
            {
                id = id.Trim();
                String    ChurchHint = id.Substring(MatchingChurchesIndex + MatchingChurchesStr.Length).Trim();
                ChurchMgr cmgr       = new ChurchMgr(entities);
                return(cmgr.FindChurches(ChurchHint));
            }

            String MatchingEventsStr   = "Matching_Events:";
            int    MatchingEventsIndex = id.IndexOf(MatchingEventsStr);

            if (MatchingEventsIndex >= 0)
            {
                id = id.Trim();
                String   EventHint = id.Substring(MatchingEventsIndex + MatchingEventsStr.Length).Trim();
                EventMgr emgr      = new EventMgr(entities);
                return(emgr.FindEvents(EventHint));
            }

            String DeleteEventStr   = "Delete_Event:";
            int    DeleteEventIndex = id.IndexOf(DeleteEventStr);

            if (DeleteEventIndex >= 0)
            {
                id = id.Trim();
                String   idStr = id.Substring(DeleteEventIndex + DeleteEventStr.Length).Trim();
                EventMgr emgr  = new EventMgr(entities);
                if (idStr.Length > 0)
                {
                    int idVal = Int32.Parse(idStr);
                    return(emgr.DeleteEvent(idVal));
                }
                else
                {
                    return("Missing Event id to Delete");
                }
            }

            // FindAttendance:id=

            // Matching_Attendees:eid=
            // Matching_Attendees:" + "eyear = " + ystr + "; etype = " + DescStr;
            String MatchingAttendeesStr   = "Matching_Attendees:";
            int    MatchingAttendeesIndex = id.IndexOf(MatchingAttendeesStr);

            if (MatchingAttendeesIndex >= 0)
            {
                id = id.Trim();
                String        AttendeeHint = id.Substring(MatchingAttendeesIndex + MatchingAttendeesStr.Length).Trim();
                AttendanceMgr amgr         = new AttendanceMgr(entities);
                return(amgr.FindAttendees(AttendeeHint));
            }

            String DeleteChurchStr   = "Delete_Church:";
            int    DeleteChurchIndex = id.IndexOf(DeleteChurchStr);

            if (DeleteChurchIndex >= 0)
            {
                id = id.Trim();
                String    idStr = id.Substring(DeleteChurchIndex + DeleteChurchStr.Length).Trim();
                ChurchMgr cmgr  = new ChurchMgr(entities);
                if (idStr.Length > 0)
                {
                    int idVal = Int32.Parse(idStr);
                    return(cmgr.DeleteChurch(idVal));
                }
                else
                {
                    return("Missing Church id to Delete");
                }
            }

            String MatchingNamesStr   = "Matching_Names:";
            int    MatchingNamesIndex = id.IndexOf("Matching_Names:");

            if (MatchingNamesIndex >= 0)
            {
                id = id.Trim();
                String FNTarget = id.Substring(MatchingNamesIndex + MatchingNamesStr.Length).Trim();
                String LNTarget;
                if (FNTarget.Length == 0)
                {
                    return("No Name to Match");
                }
                int SPIndex = FNTarget.LastIndexOf(' ');
                if (SPIndex != -1)
                {
                    LNTarget = FNTarget.Substring(SPIndex + 1).Trim();
                    FNTarget = FNTarget.Substring(0, SPIndex).Trim();
                }
                else
                {
                    LNTarget = "";
                }

                List <PInfo> PInfoList = new List <PInfo>();
                String       falias    = "F:" + FNTarget;
                if (LNTarget.Length > 0)
                {
                    String lalias      = "L:" + LNTarget;
                    var    PInfoListFL = (from p in entities.Person
                                          where (p.FirstName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias))) &&
                                          (p.LastName.ToLower().StartsWith(LNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == lalias)))
                                          join a in entities.Address on
                                          p.AddressId equals a.Id
                                          join c in entities.Church on
                                          p.ChurchId equals c.Id into pc
                                          from pcn in pc.DefaultIfEmpty()
                                          select new { p.Id, p.FirstName, p.LastName, p.Ssnum, a.ZipCode, pcn.Name, p.Alias }).ToList();

                    var jsonPG = JsonConvert.SerializeObject(PInfoListFL, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    return(jsonPG.ToString());
                }
                else
                {
                    var PInfoListF = (from p in entities.Person
                                      where (p.FirstName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias))) ||
                                      (p.LastName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias)))
                                      join a in entities.Address on
                                      p.AddressId equals a.Id
                                      join c in entities.Church on
                                      p.ChurchId equals c.Id into pc
                                      from pcn in pc.DefaultIfEmpty()
                                      select new { p.Id, p.FirstName, p.LastName, p.Ssnum, a.ZipCode, pcn.Name }).ToList();

                    var jsonPG = JsonConvert.SerializeObject(PInfoListF, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    return(jsonPG.ToString());
                }
            }

            return(Person.FindPeople(entities, id));
        }
Example #2
0
        // GET api/values/5
        public string Get(string id) //********************************************************
        {
            if (id == null)
            {
                return("Bad Get id");
            }

            id = id.Replace("@@", "*");

            String MatchingChurchesStr   = "Matching_Churches:";
            int    MatchingChurchesIndex = id.IndexOf(MatchingChurchesStr);

            if (MatchingChurchesIndex >= 0)
            {
                id = id.Trim();
                String    ChurchHint = id.Substring(MatchingChurchesIndex + MatchingChurchesStr.Length).Trim();
                ChurchMgr cmgr       = new ChurchMgr(entities);
                return(cmgr.FindChurches(ChurchHint));
            }

            String MatchingEventsStr   = "Matching_Events:";
            int    MatchingEventsIndex = id.IndexOf(MatchingEventsStr);

            if (MatchingEventsIndex >= 0)
            {
                id = id.Trim();
                String   EventHint = id.Substring(MatchingEventsIndex + MatchingEventsStr.Length).Trim();
                EventMgr emgr      = new EventMgr(entities);
                return(emgr.FindEvents(EventHint));
            }

            String DeleteEventStr   = "Delete_Event:";
            int    DeleteEventIndex = id.IndexOf(DeleteEventStr);

            if (DeleteEventIndex >= 0)
            {
                id = id.Trim();
                String   idStr = id.Substring(DeleteEventIndex + DeleteEventStr.Length).Trim();
                EventMgr emgr  = new EventMgr(entities);
                if (idStr.Length > 0)
                {
                    int idVal = Int32.Parse(idStr);
                    return(emgr.DeleteEvent(idVal));
                }
                else
                {
                    return("Missing Event id to Delete");
                }
            }

            // FindAttendance:id=

            // Matching_Attendees:eid=
            // Matching_Attendees:" + "eyear = " + ystr + "; etype = " + DescStr;
            String MatchingAttendeesStr   = "Matching_Attendees:";
            int    MatchingAttendeesIndex = id.IndexOf(MatchingAttendeesStr);

            if (MatchingAttendeesIndex >= 0)
            {
                id = id.Trim();
                String        AttendeeHint = id.Substring(MatchingAttendeesIndex + MatchingAttendeesStr.Length).Trim();
                AttendanceMgr amgr         = new AttendanceMgr(entities);
                return(amgr.FindAttendees(AttendeeHint));
            }

            String DeleteChurchStr   = "Delete_Church:";
            int    DeleteChurchIndex = id.IndexOf(DeleteChurchStr);

            if (DeleteChurchIndex >= 0)
            {
                id = id.Trim();
                String    idStr = id.Substring(DeleteChurchIndex + DeleteChurchStr.Length).Trim();
                ChurchMgr cmgr  = new ChurchMgr(entities);
                if (idStr.Length > 0)
                {
                    int idVal = Int32.Parse(idStr);
                    return(cmgr.DeleteChurch(idVal));
                }
                else
                {
                    return("Missing Church id to Delete");
                }
            }

            String MatchingNamesStr   = "Matching_Names:";
            int    MatchingNamesIndex = id.IndexOf("Matching_Names:");

            if (MatchingNamesIndex >= 0)
            {
                id = id.Trim();
                String FNTarget = id.Substring(MatchingNamesIndex + MatchingNamesStr.Length).Trim();
                String LNTarget;
                if (FNTarget.Length == 0)
                {
                    return("No Name to Match");
                }
                int SPIndex = FNTarget.LastIndexOf(' ');
                if (SPIndex != -1)
                {
                    LNTarget = FNTarget.Substring(SPIndex + 1).Trim();
                    FNTarget = FNTarget.Substring(0, SPIndex).Trim();
                }
                else
                {
                    LNTarget = "";
                }

                List <PInfo> PInfoList = new List <PInfo>();
                String       falias    = "F:" + FNTarget;
                if (LNTarget.Length > 0)
                {
                    String lalias      = "L:" + LNTarget;
                    var    PInfoListFL = (from p in entities.Person
                                          where (p.FirstName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias))) &&
                                          (p.LastName.ToLower().StartsWith(LNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == lalias)))
                                          join a in entities.Address on
                                          p.AddressId equals a.Id
                                          join c in entities.Church on
                                          p.ChurchId equals c.Id into pc
                                          from pcn in pc.DefaultIfEmpty()
                                          select new { p.Id, p.FirstName, p.LastName, p.Ssnum, a.ZipCode, pcn.Name, p.Alias }).ToList();

                    var jsonPG = JsonConvert.SerializeObject(PInfoListFL, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    return(jsonPG.ToString());
                }
                else
                {
                    var PInfoListF = (from p in entities.Person
                                      where (p.FirstName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias))) ||
                                      (p.LastName.ToLower().StartsWith(FNTarget.ToLower()) || ((p.Alias != null) && (p.Alias == falias)))
                                      join a in entities.Address on
                                      p.AddressId equals a.Id
                                      join c in entities.Church on
                                      p.ChurchId equals c.Id into pc
                                      from pcn in pc.DefaultIfEmpty()
                                      select new { p.Id, p.FirstName, p.LastName, p.Ssnum, a.ZipCode, pcn.Name }).ToList();

                    var jsonPG = JsonConvert.SerializeObject(PInfoListF, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    return(jsonPG.ToString());
                }
            }

            String target       = "Lookup Person:";
            int    lookup_index = id.IndexOf(target);

            if (lookup_index >= 0)
            {
                String pidStr = id.Substring(lookup_index + target.Length);
                int    pid    = Int32.Parse(pidStr);

                String PG1Str = null, PG2Str = null;
                int    PG1_id = 0, PG2_id = 0;

                //var PList = from p in entities.People
                //            where p.id == pid
                //            join a in entities.Addresses on
                //            p.Address_id equals a.id
                //            join c in entities.Churches on
                //            p.Church_id equals c.id into pc
                //            from pcn in pc.DefaultIfEmpty()
                //            select p;

                var PList = from p in entities.Person
                            where p.Id == pid
                            join a in entities.Address on
                            p.AddressId equals a.Id into pa
                            from a in pa.DefaultIfEmpty()
                            join c in entities.Church on
                            p.ChurchId equals c.Id into pc
                            from c in pc.DefaultIfEmpty()
                            from pcn in pc.DefaultIfEmpty()
                            select p;

                var pgObj  = new PG();
                var MPList = new List <MinPerson>();

                foreach (Person pr in PList)
                {
                    pr.FirstName = PersonMgr.MakeFirstName(pr);
                    pr.LastName  = PersonMgr.MakeLastName(pr);

                    // TEST pr.PG1_Person_id = 3;
                    // TEST pr.PG2_Person_id = 4;

                    if (pr.AddressId != null)
                    {
                        pgObj.Adr_id = (int)pr.AddressId;
                    }

                    Person p1 = entities.Person.FirstOrDefault(p => p.Id == pr.Pg1PersonId);
                    if (p1 != null)
                    {
                        p1.FirstName = PersonMgr.MakeFirstName(p1);
                        p1.LastName  = PersonMgr.MakeLastName(p1);
                        PG1Str       = p1.FirstName + " " + p1.LastName;
                        PG1_id       = p1.Id;
                    }

                    Person p2 = entities.Person.FirstOrDefault(p => p.Id == pr.Pg2PersonId);
                    if (p2 != null)
                    {
                        p2.FirstName = PersonMgr.MakeFirstName(p2);
                        p2.LastName  = PersonMgr.MakeLastName(p2);
                        PG2Str       = p2.FirstName + " " + p2.LastName;
                        PG2_id       = p2.Id;
                    }

                    if ((PG1Str != null) || (PG2Str != null))
                    {
                        if (PG1Str != null)
                        {
                            pgObj.PG1Name = PG1Str;
                            pgObj.PG1_id  = PG1_id;
                        }
                        if (PG2Str != null)
                        {
                            pgObj.PG2Name = PG2Str;
                            pgObj.PG2_id  = PG2_id;
                        }

                        var jsonPG = JsonConvert.SerializeObject(pgObj, Formatting.None, new JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                        });
                        pr.ContactPref = jsonPG.ToString();
                    }

                    var m = new MinPerson();
                    m.FirstName     = pr.FirstName;
                    m.LastName      = pr.LastName;
                    m.id            = pr.Id;
                    m.Alias         = pr.Alias;
                    m.DOB           = pr.Dob;
                    m.Gender        = pr.Gender;
                    m.Status        = pr.Status;
                    m.SSNum         = pr.Ssnum;
                    m.Address_id    = pr.AddressId;
                    m.PG1_Person_id = pr.Pg1PersonId;
                    m.PG2_Person_id = pr.Pg2PersonId;
                    m.Church_id     = pr.ChurchId;
                    m.SkillSets     = pr.SkillSets;
                    m.CellPhone     = pr.CellPhone;
                    m.EMail         = pr.Email;
                    m.ContactPref   = pr.ContactPref;
                    m.Notes         = pr.Notes;
                    if (pr.Address != null)
                    {
                        m.Address.id      = pr.Address.Id;
                        m.Address.Street  = pr.Address.Street;
                        m.Address.Town    = pr.Address.Town;
                        m.Address.State   = pr.Address.State;
                        m.Address.ZipCode = pr.Address.ZipCode;
                        m.Address.Phone   = pr.Address.Phone;
                        m.Address.Fax     = pr.Address.Fax;
                        m.Address.Country = pr.Address.Country;
                        m.Address.WebSite = pr.Address.WebSite;
                    }
                    MPList.Add(m);
                }

                if (MPList != null)
                {
                    var json = JsonConvert.SerializeObject(MPList, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    string jstr = json.ToString();
                    return(jstr);
                }
                else
                {
                    return("Lookup Failed with id:" + pid);
                }
            }

            //******************************************************
            // MULTI_FIELD FIND PEOPLE SEARCH
            //******************************************************

            // Generate Key/Value Pairs to update person
            char[] outerDelims = { ';', '~', '&' };
            char[] innerDelims = { ':', '=' };

            string[] pairs = id.Split(outerDelims);

            List <KeyValuePair <string, string> > props = new List <KeyValuePair <string, string> >();

            foreach (string nvs in pairs)
            {
                string[] nvp = nvs.Split(innerDelims);
                if ((nvp.Length == 2) && (nvp[1].Length > 0))
                {
                    props.Add(new KeyValuePair <string, string>(nvp[0], nvp[1]));
                }
            }

            // SQL Based Query

            String sel       = "Select * from Person FULL OUTER JOIN Address ON (Person.Address_id = Address.id)";
            int    orgSelLen = sel.Length;
            bool   bFiltered = false;

            PersonMgr pmgr = new PersonMgr(entities);
            Person    fp;
            Address   fa;

            if (pmgr.Update(ref props, true, true, out fp, out fa) != MgrStatus.Add_Update_Succeeded)
            {
                return("Invalid Search Criteria");
            }

            PersonMgr.AddToSelect(ref sel, "Person.FirstName", fp.FirstName, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.LastName", fp.LastName, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Address.Street", fa.Street, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Address.Town", fa.Town, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.CellPhone", fp.CellPhone, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.EMail", fp.Email, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.Church_id", fp.ChurchId, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.SSNum", fp.Ssnum, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Address.State", fa.State, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.Gender", fp.Gender.GetValueOrDefault(0), ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.SkillSets", fp.SkillSets, ref bFiltered);
            PersonMgr.AddToSelect(ref sel, "Person.Status", fp.Status, ref bFiltered);
            if (!fp.Dob.GetValueOrDefault().Equals(new DateTime(1900, 1, 1)))
            {
                PersonMgr.AddToSelect(ref sel, "Person.DOB", fp.Dob.GetValueOrDefault(), true, ref bFiltered);
            }

            if (sel.Length == orgSelLen)
            {
                sel += "order by Address.Street ASC";
            }

            //                Also, it is recommended to use ISO8601 format YYYY - MM - DDThh:mm: ss.nnn[Z], as this one will not depend on your server's local culture.
            //SELECT*
            //FROM TABLENAME
            //WHERE
            //    DateTime >= '2011-04-12T00:00:00.000' AND
            //    DateTime <= '2011-05-25T03:53:04.000'

            try
            {
                var PList = entities.Person.FromSqlRaw(sel).ToList <Person>();

                var MPList = new List <MinPerson>();

                if (PList != null)
                {
                    foreach (var p in PList)
                    {
                        if (p == null)
                        {
                            continue;
                        }
                        var m = new MinPerson();
                        m.FirstName     = p.FirstName;
                        m.LastName      = p.LastName;
                        m.id            = p.Id;
                        m.Alias         = p.Alias;
                        m.DOB           = p.Dob;
                        m.Gender        = p.Gender;
                        m.Status        = p.Status;
                        m.SSNum         = p.Ssnum;
                        m.Address_id    = p.AddressId;
                        m.PG1_Person_id = p.Pg1PersonId;
                        m.PG2_Person_id = p.Pg2PersonId;
                        m.Church_id     = p.ChurchId;
                        m.SkillSets     = p.SkillSets;
                        m.CellPhone     = p.CellPhone;
                        m.EMail         = p.Email;
                        m.ContactPref   = p.ContactPref;
                        m.Notes         = p.Notes;
                        if (p.Address != null)
                        {
                            m.Address.id      = p.Address.Id;
                            m.Address.Street  = p.Address.Street;
                            m.Address.Town    = p.Address.Town;
                            m.Address.State   = p.Address.State;
                            m.Address.ZipCode = p.Address.ZipCode;
                            m.Address.Phone   = p.Address.Phone;
                            m.Address.Fax     = p.Address.Fax;
                            m.Address.Country = p.Address.Country;
                            m.Address.WebSite = p.Address.WebSite;
                        }
                        else
                        {
                            m.Address.Street  = "";
                            m.Address.Town    = "";
                            m.Address.State   = "";
                            m.Address.ZipCode = "";
                            m.Address.Phone   = "";
                            m.Address.Fax     = "";
                            m.Address.Country = "";
                            m.Address.WebSite = "";
                        }
                        MPList.Add(m);
                    }
                }

                if (MPList.Count > 0)
                {
                    var json = JsonConvert.SerializeObject(MPList, Formatting.None, new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
                    string jstr = json.ToString();
                    return(jstr);
                }
            }
            catch (Exception)
            {
            }

            return("ERROR~:No one found with Name");
        }