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