Exemplo n.º 1
0
        //Heidi: Get the bill report for the payments made to the conference.
        //Returns the transaction id, payment date, payment ammount, payment method, name, usertype, affiliation, email, address, and phone number.
        public ReportQuery getBillReportList()
        {
            ReportQuery b = new ReportQuery();
            String csv = "";

            try
            {
                using (conferenceadminContext context = new conferenceadminContext())
                {
                    //Get registration payments, complimentary payments, and sponsor payments (in the order mentioned)
                    var payments = (from s in context.registrations
                                    from bill in context.paymentbills
                                    where (s.payment.deleted != true && s.paymentID == bill.paymentID && bill.completed != false)
                                    select new BillQuery
                                        {
                                            transactionID = bill.transactionid,
                                            paymentDate = bill.payment.creationDate.ToString(),
                                            amountPaid = bill.AmountPaid,
                                            paymentMethod = bill.methodOfPayment == "default" ? "" : bill.methodOfPayment,
                                            name = s.user.firstName + " " + s.user.lastName,
                                            email = s.user.membership.email == "default" ? "" : s.user.membership.email,
                                            affiliation = s.user.affiliationName == "Paradigm Innovation" ? "" : s.user.affiliationName,
                                            userType = s.user.usertype.userTypeName,
                                            phoneNumber = bill.telephone == "default" ? "" : bill.telephone,
                                            address1 = s.user.address.line1 == "default" ? "" : s.user.address.line1,
                                            address2 = s.user.address.line2 == "default" ? "" : s.user.address.line2,
                                            city = s.user.address.city == "default" ? "" : s.user.address.city,
                                            state = s.user.address.state == "default" ? "" : s.user.address.state,
                                            country = s.user.address.country == "default" ? "" : s.user.address.country,
                                            zipCode = s.user.address.line1 == "default" ? "" : s.user.address.zipcode

                                        }).Concat((from s in context.registrations
                                                   from bill in context.paymentcomplementaries
                                                   where (s.payment.deleted != true && s.paymentID == bill.paymentID)
                                                   select new BillQuery
                                                        {
                                                            transactionID = "N/A",
                                                            paymentDate = bill.payment.creationDate.ToString(),
                                                            amountPaid = 0,
                                                            paymentMethod = "Key:" + bill.complementarykey.key,
                                                            name = s.user.firstName + " " + s.user.lastName,
                                                            email = s.user.membership.email == "default" ? "" : s.user.membership.email,
                                                            affiliation = s.user.affiliationName == "Paradigm Innovation" ? "" : s.user.affiliationName,
                                                            userType = s.user.usertype.userTypeName,
                                                            phoneNumber = s.user.phone == "default" ? "" : s.user.phone,
                                                            address1 = s.user.address.line1 == "default" ? "" : s.user.address.line1,
                                                            address2 = s.user.address.line2 == "default" ? "" : s.user.address.line2,
                                                            city = s.user.address.city == "default" ? "" : s.user.address.city,
                                                            state = s.user.address.state == "default" ? "" : s.user.address.state,
                                                            country = s.user.address.country == "default" ? "" : s.user.address.country,
                                                            zipCode = s.user.address.line1 == "default" ? "" : s.user.address.zipcode

                                                        })).Concat((from s in context.sponsor2
                                                                    from bill in context.paymentbills
                                                                    where (s.payment.deleted != true && s.paymentID == bill.paymentID && s.sponsorID != 1 && s.paymentID != 1 && bill.completed != false && s.active != false)
                                                                    select new BillQuery
                                                                    {
                                                                         transactionID = bill.transactionid,
                                                                         paymentDate = bill.payment.creationDate.ToString(),
                                                                         amountPaid = bill.AmountPaid,
                                                                         paymentMethod = bill.methodOfPayment,
                                                                         name = s.user.firstName + " " + s.user.lastName,
                                                                         email = s.byAdmin == false ? s.user.membership.email : s.emailInfo,
                                                                         affiliation = s.user.affiliationName,
                                                                         userType = "Sponsor",
                                                                         phoneNumber = bill.telephone == "default" ? "" : bill.telephone,
                                                                         address1 = s.user.address.line1 == "default" ? "" : s.user.address.line1,
                                                                         address2 = s.user.address.line2 == "default" ? "" : s.user.address.line2,
                                                                         city = s.user.address.city == "default" ? "" : s.user.address.city,
                                                                         state = s.user.address.state == "default" ? "" : s.user.address.state,
                                                                         country = s.user.address.country == "default" ? "" : s.user.address.country,
                                                                         zipCode = s.user.address.line1 == "default" ? "" : s.user.address.zipcode

                                                                    }));

                    if (payments.Count() > 0)
                    {
                        //Create csv string: Column titles
                            csv += ("\"Transaction ID\"," +
                                    "\"Payment Date\"," +
                                    "\"Amount Paid\"," +
                                    "\"Payment Method\"," +
                                    "\"Name\"," +
                                    "\"Email\"," +
                                    "\"Affiliation\"," +
                                    "\"User Type\"," +
                                    "\"Phone Number\"," +
                                    "\"Address Line 1\"," +
                                    "\"Address Line 2\"," +
                                    "\"City\"," +
                                    "\"State\"," +
                                    "\"Country\"," +
                                    "\"Zip Code\"\r\n");

                        foreach (var p in payments)
                        {
                            //Append to the csv string each record
                            csv += ("\"" + p.transactionID + "\"," +
                                    "\"" + p.paymentDate + "\"," +
                                    "\"" + p.amountPaid + "\"," +
                                    "\"" + p.paymentMethod + "\"," +
                                    "\"" + p.name + "\"," +
                                    "\"" + p.email + "\"," +
                                    "\"" + p.affiliation + "\"," +
                                    "\"" + p.userType + "\"," +
                                    "\"" + p.phoneNumber + "\"," +
                                    "\"" + p.address1 + "\"," +
                                    "\"" + p.address2 + "\"," +
                                    "\"" + p.city + "\"," +
                                    "\"" + p.state + "\"," +
                                    "\"" + p.country + "\"," +
                                    "\"" + p.zipCode + "\"\r\n");
                        }

                        b.results = csv;
                    }
                }

                return b;

            }
            catch (Exception ex)
            {
                Console.Write("WebManager.getBillReport error " + ex);
                return null;
            }
        }
Exemplo n.º 2
0
        //Heidi: get record of users that have registered for the conference
        //Returns registrationID, name, email, phone, usertype, dates of attendance, affiliation, address and special notes
        public ReportQuery getAttendanceReport()
        {
            ReportQuery b = new ReportQuery();
            String csv = "";
            RegistrationManager registration = new RegistrationManager();
            List<String> conferenceDates = registration.getDates();

            try
            {
                using (conferenceadminContext context = new conferenceadminContext())
                {
                    //Get conference registrations A.Get adults, B. Get minors
                    var registrationList = new List<RegisteredUserInformation>();
                    registrationList = (from reg in context.registrations
                                        where reg.deleted == false && context.minors.Where(m => m.userID == reg.userID).Select(m=>m.userID).Count() == 0
                                        select new RegisteredUserInformation
                                        {
                                            registrationID = reg.registrationID,
                                            name = reg.user.firstName+ " " +reg.user.lastName,
                                            email = reg.byAdmin == true ? "" : reg.user.membership.email,
                                            phone = reg.byAdmin == true ? "" : reg.user.phone,
                                            usertypeid = reg.user.usertype.userTypeName,
                                            companion = "",
                                            date1 = reg.date1,
                                            date2 = reg.date2,
                                            date3 = reg.date3,
                                            affiliationName = reg.user.affiliationName,
                                            line1 = reg.byAdmin == true ? "" : reg.user.address.line1,
                                            line2 = reg.byAdmin == true ? "" : reg.user.address.line2,
                                            city = reg.byAdmin == true ? "" : reg.user.address.city,
                                            state = reg.byAdmin == true ? "" : reg.user.address.state,
                                            country = reg.byAdmin == true ? "" : reg.user.address.country,
                                            zipCode = reg.byAdmin == true ? "" : reg.user.address.zipcode,
                                            notes = reg.note,
                                            usertype = reg.user.usertype.userTypeName

                                        }).Concat((from reg in context.registrations
                                                   from c in context.companionminors
                                                   from m in context.minors
                                                   where reg.deleted == false && m.minorsID == c.minorID && reg.userID == m.userID
                                                   select new RegisteredUserInformation
                                                   {
                                                       registrationID = reg.registrationID,
                                                       name = reg.user.firstName + " " + reg.user.lastName,
                                                       email = reg.byAdmin == true ? "" : reg.user.membership.email,
                                                       phone = reg.byAdmin == true ? "" : reg.user.phone,
                                                       usertypeid = reg.user.usertype.userTypeName,
                                                       companion = c.companion.user.firstName+" "+ c.companion.user.lastName,
                                                       date1 = reg.date1,
                                                       date2 = reg.date2,
                                                       date3 = reg.date3,
                                                       affiliationName = reg.user.affiliationName,
                                                       line1 = reg.byAdmin == true ? "" : reg.user.address.line1,
                                                       line2 = reg.byAdmin == true ? "" : reg.user.address.line2,
                                                       city = reg.byAdmin == true ? "" : reg.user.address.city,
                                                       state = reg.byAdmin == true ? "" : reg.user.address.state,
                                                       country = reg.byAdmin == true ? "" : reg.user.address.country,
                                                       zipCode = reg.byAdmin == true ? "" : reg.user.address.zipcode,
                                                       notes = reg.note,
                                                       usertype = reg.user.usertype.userTypeName

                                                   })).OrderBy(f => f.registrationID).ToList();

                    //Converto to csv string
                    if (registrationList.Count() > 0)
                    {
                            //Column titles
                            csv += ("\"Registration ID\"," +
                                    "\"Name\"," +
                                    "\"Email\"," +
                                    "\"Phone Number\"," +
                                    "\"User Type\"," +
                                    "\"Companion\",");

                            //Add each date of the conference as column title
                            if (conferenceDates.Count() > 0)
                            {
                                string[] date = conferenceDates[0].Split(',');
                                if(date.Count() ==3)
                                csv += ("\""+date[1]+","+date[2]+"\",");
                            }
                            if (conferenceDates.Count() > 1)
                            {
                                string[] date = conferenceDates[1].Split(',');
                                if (date.Count() == 3)
                                csv += ("\"" + date[1] + "," + date[2] + "\",");
                            }
                            if (conferenceDates.Count() > 2)
                            {
                                string[] date = conferenceDates[2].Split(',');
                                if (date.Count() == 3)
                                csv += ("\"" + date[1] + "," + date[2] + "\",");
                            }

                            csv += ("\"Affiliation\"," +
                                    "\"Address Line 1\"," +
                                    "\"Address Line 2\"," +
                                    "\"City\"," +
                                    "\"State\"," +
                                    "\"Country\"," +
                                    "\"Zip Code\"," +
                                    "\"Notes\"\r\n");

                            //Append each registration to csv string.
                            foreach (var p in registrationList)
                            {
                            csv += ("\"" + p.registrationID + "\"," +
                                    "\"" + p.name + "\"," +
                                    "\"" + p.email + "\"," +
                                    "\"" + p.phone + "\"," +
                                    "\"" + p.usertype + "\"," +
                                    "\"" + p.companion + "\",");

                            //Mark days the user will attend the conference.
                            if (conferenceDates.Count() > 0 && conferenceDates[0].Split(',').Count() ==3)
                            {
                                string date = p.date1 == true ? "X" : "";
                                csv += "\"" + date + "\",";
                            }
                            if (conferenceDates.Count() > 1 && conferenceDates[1].Split(',').Count() ==3)
                            {
                                string date = p.date2 == true ? "X" : "";
                                csv += "\"" + date + "\",";
                            }
                            if (conferenceDates.Count() > 2 && conferenceDates[2].Split(',').Count() == 3)
                            {
                                string date = p.date3 == true ? "X" : "";
                                csv += "\"" + date + "\",";
                            }

                            csv +=  ("\"" + p.affiliationName + "\"," +
                                    "\"" + p.line1 + "\"," +
                                    "\"" + p.line2 + "\"," +
                                    "\"" + p.city + "\"," +
                                    "\"" + p.state + "\"," +
                                    "\"" + p.country + "\"," +
                                    "\"" + p.zipCode + "\"," +
                                    "\"" + p.notes + "\"\r\n");
                        }

                        b.results = csv;
                    }
                }

                return b;

            }
            catch (Exception ex)
            {
                Console.Write("WebManager.getAttendanceReport error " + ex);
                return null;
            }
        }