Beispiel #1
0
        public ActionResult RegistrationExcel(Guid? id, int? oid)
        {
            if (!id.HasValue)
                return Content("no query");

            var peopleQuery = DbUtil.Db.PeopleQuery(id.Value);
            if (!oid.HasValue)
                oid = DbUtil.Db.CurrentOrgId;
            var results = (from p in peopleQuery
                           let rr = p.RecRegs.SingleOrDefault() ?? new RecReg()
                           let headOfHousehold = p.Family.HeadOfHousehold
                           let headOfHouseholdSpouse = p.Family.HeadOfHouseholdSpouse
                           orderby p.Name2
                           select new
                           {
                               Person = p,
                               RecReg = rr,
                               HeadOfHousehold = headOfHousehold,
                               HeadOfHouseholdSpouse = headOfHouseholdSpouse,
                               OrgMembers = p.OrganizationMembers.SingleOrDefault(om => om.OrganizationId == oid),
                               p.OrganizationMembers.SingleOrDefault(om => om.OrganizationId == oid).Organization,
                           }).ToList();

            if (!results.Any())
                return Content("no results");

            var table = new DataTable();

            foreach (var x in results)
            {
                Settings setting = null;
                if (x.Organization != null)
                    setting = DbUtil.Db.CreateRegistrationSettings(x.Organization.OrganizationId);

                var row = table.NewRow();

                AddValue(table, row, "Name", x.Person.Name);
                AddValue(table, row, "PrimaryAddress", x.Person.PrimaryAddress);
                AddValue(table, row, "PrimaryAddress2", x.Person.PrimaryAddress2);
                AddValue(table, row, "CityStateZip", x.Person.CityStateZip);
                AddValue(table, row, "EmailAddress", x.Person.EmailAddress);

                if (x.Person.HomePhone.HasValue())
                    AddValue(table, row, "HomePhone", x.Person.HomePhone.FmtFone("H"));
                if (x.Person.CellPhone.HasValue())
                    AddValue(table, row, "CellPhone", x.Person.CellPhone.FmtFone("C"));

                AddValue(table, row, "DOB", x.Person.DOB);

                AddValue(table, row, "HeadOfHouseholdName", x.HeadOfHousehold?.Name);
                if (!string.IsNullOrEmpty(x.HeadOfHousehold?.CellPhone))
                    AddValue(table, row, "HeadOfHouseholdCellPhone", x.HeadOfHousehold?.CellPhone.FmtFone("C"));
                if (!string.IsNullOrEmpty(x.HeadOfHousehold?.HomePhone))
                    AddValue(table, row, "HeadOfHouseholdHomePhone", x.HeadOfHousehold?.HomePhone.FmtFone("H"));

                AddValue(table, row, "HeadOfHouseholdSpouseName", x.HeadOfHouseholdSpouse?.Name);
                if (!string.IsNullOrEmpty(x.HeadOfHouseholdSpouse?.CellPhone))
                    AddValue(table, row, "HeadOfHouseholdSpouseCellPhone", x.HeadOfHouseholdSpouse?.CellPhone.FmtFone("C"));
                if (!string.IsNullOrEmpty(x.HeadOfHouseholdSpouse?.HomePhone))
                    AddValue(table, row, "HeadOfHouseholdSpouseHomePhone", x.HeadOfHouseholdSpouse?.HomePhone.FmtFone("H"));

                if (x.Organization == null || SettingVisible(setting, "AskSize"))
                    AddValue(table, row, "ShirtSize", x.RecReg.ShirtSize);

                AddValue(table, row, "MedicalDescription", x.RecReg.MedicalDescription);

                if (x.Organization == null || SettingVisible(setting, "AskTylenolEtc"))
                {
                    AddValue(table, row, "Tylenol", x.RecReg.Tylenol);
                    AddValue(table, row, "Advil", x.RecReg.Advil);
                    AddValue(table, row, "Robitussin", x.RecReg.Robitussin);
                    AddValue(table, row, "Maalox", x.RecReg.Maalox);
                }

                if (x.Organization == null || SettingVisible(setting, "AskEmContact"))
                {
                    AddValue(table, row, "Emcontact", x.RecReg.Emcontact);
                    AddValue(table, row, "Emphone", x.RecReg.Emphone.FmtFone());
                }

                if (x.Organization == null || SettingVisible(setting, "AskInsurance"))
                {
                    AddValue(table, row, "Insurance", x.RecReg.Insurance);
                    AddValue(table, row, "Policy", x.RecReg.Policy);
                }

                if (x.Organization == null || SettingVisible(setting, "AskDoctor"))
                {
                    AddValue(table, row, "Doctor", x.RecReg.Doctor);
                    AddValue(table, row, "Docphone", x.RecReg.Docphone.FmtFone());
                }

                if (x.Organization == null || SettingVisible(setting, "AskParents"))
                {
                    AddValue(table, row, "Mname", x.RecReg.Mname);
                    AddValue(table, row, "Fname", x.RecReg.Fname);
                }

                if (x.OrgMembers?.OnlineRegData != null)
                {
                    var qlist = from qu in DbUtil.Db.ViewOnlineRegQAs
                                where qu.OrganizationId == x.OrgMembers.OrganizationId
                                where qu.Type == "question" || qu.Type == "text"
                                where qu.PeopleId == x.OrgMembers.PeopleId
                                select qu;
                    var counter = 0;
                    foreach (var qu in qlist)
                    {
                        AddValue(table, row, $"Question{counter}", qu.Question);
                        AddValue(table, row, $"Answer{counter}", qu.Answer);
                        counter++;
                    }

                    if (x.OrgMembers?.UserData != null)
                    {
                        var a = Regex.Split(x.OrgMembers.UserData, @"\s*--Add comments above this line--\s*", RegexOptions.Multiline);
                        if (a.Length > 0)
                        {
                            AddValue(table, row, "Comments", a[0]);
                        }
                    }

                    if (x.OrgMembers != null)
                    {
                        var groups = string.Join(", ", x.OrgMembers.OrgMemMemTags.Select(om => om.MemberTag.Name).ToArray());
                        AddValue(table, row, "Groups", groups);
                    }
                }

                table.Rows.Add(row);
            }

            return table.ToExcel(filename: "Registrations.xlsx");
        }
Beispiel #2
0
 public static EpplusResult ToExcel(this IDataReader rd, string filename = null, bool useTable = false)
 {
     var dt = new DataTable();
     dt.Load(rd);
     return dt.ToExcel(filename, useTable);
 }
Beispiel #3
0
 /// <summary>
 /// DataTable导出EXCEL文件
 /// </summary>
 /// <param name="dt">DataTable</param>
 public void ToExcel(DataTable dt) {
     dt.ToExcel(fileName);
 }