//Heidi: Get the Sponsor Payments //Returns the transaction id, payment date, payment method payment ammount, name, usertype, affiliation, email. public BillPagingQuery getSponsorPayments(int index) { BillPagingQuery page = new BillPagingQuery(); try { using (conferenceadminContext context = new conferenceadminContext()) { int pageSize = 10; //Get sponsor payments var query = (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(), name = s.user.firstName + " " + s.user.lastName, email = s.byAdmin == false ? s.user.membership.email : s.emailInfo, affiliation = s.user.affiliationName, userType = "Sponsor", sponsorType= s.sponsortype1.name, amountPaid = bill.AmountPaid, paymentMethod = bill.methodOfPayment }).OrderBy(x => x.name); //Paging->Filter results to a limit of 10 records per page page.rowCount = query.Count(); if (page.rowCount > 0) { page.maxIndex = (int)Math.Ceiling(page.rowCount / (double)pageSize); var registrationPayments = query.Skip(pageSize * index).Take(pageSize).ToList(); //Skip past rows and take new elements page.results = registrationPayments; } } return page; } catch (Exception ex) { Console.Write("WebManager.getSponsorPayments error " + ex); return null; } }
//Heidi: Search reports that meet specified criteria public BillPagingQuery searchReport(int index, String criteria) { BillPagingQuery b = new BillPagingQuery(); try { int pageSize = 10; using (conferenceadminContext context = new conferenceadminContext()) { //Get registrations, and sponsor payments var payments = (from s in context.registrations from bill in context.paymentbills where ((s.payment.deleted != true && s.paymentID == bill.paymentID) && ((s.user.firstName.ToLower() + " " + s.user.lastName.ToLower()).Contains(criteria.ToLower()) || s.user.membership.email.ToLower().Contains(criteria.ToLower()))) select new BillQuery { transactionID = bill.transactionid, paymentDate = bill.payment.creationDate.ToString(), name = s.user.firstName + " " + s.user.lastName, email= s.user.membership.email, affiliation = s.user.affiliationName, userType = s.user.usertype.userTypeName, amountPaid = bill.AmountPaid, paymentMethod = bill.methodOfPayment }).Concat((from s in context.registrations from bill in context.paymentcomplementaries where ((s.payment.deleted != true && s.paymentID == bill.paymentID) && ((s.user.firstName.ToLower() + " " + s.user.lastName.ToLower()).Contains(criteria.ToLower()) || s.user.membership.email.ToLower().Contains(criteria.ToLower()))) select new BillQuery { transactionID = "N/A", paymentDate = bill.payment.creationDate.ToString(), name = s.user.firstName + " " + s.user.lastName, email= s.user.membership.email, affiliation = s.user.affiliationName, userType = s.user.usertype.userTypeName, amountPaid = 0, paymentMethod = "Complimentary Key: " + bill.complementarykey.key })).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) && ((s.user.firstName.ToLower() + " " + s.user.lastName.ToLower()).Contains(criteria.ToLower()) || s.emailInfo.ToLower().Contains(criteria.ToLower()) || s.user.membership.email.ToLower().Contains(criteria.ToLower()))) select new BillQuery { transactionID = bill.transactionid, paymentDate = bill.payment.creationDate.ToString(), name = s.user.firstName + " " + s.user.lastName, email = s.byAdmin == false ? s.user.membership.email : s.emailInfo, affiliation = s.user.affiliationName, userType = "Sponsor", amountPaid = bill.AmountPaid, paymentMethod = bill.methodOfPayment })).OrderBy(x => x.name); //Paging->Filter results to a limit of 10 records per page if (payments.Count() > 0) { b.maxIndex = (int)Math.Ceiling(payments.Count() / (double)pageSize); var report = payments.Skip(pageSize * index).Take(pageSize).ToList(); //Skip past rows and take new elements b.results = report; } } return b; } catch (Exception ex) { Console.Write("WebManager.searchReport error " + ex); return null; } }
//Heidi: Get registrations //Returns the transaction id, payment date, payment method payment ammount, name, usertype, affiliation, email. public BillPagingQuery getRegistrationPayments(int index) { BillPagingQuery page = new BillPagingQuery(); try { using (conferenceadminContext context = new conferenceadminContext()) { int pageSize = 10; //Get registration both the paid registrations, and the complimentary registrations var query = (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(), name = s.user.firstName + " " + s.user.lastName, email=s.user.membership.email, affiliation = s.user.affiliationName, userType = s.user.usertype.userTypeName, amountPaid = 0, paymentMethod = "Complimentary Key: " + bill.complementarykey.key }).Concat((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(), name = s.user.firstName + " " + s.user.lastName, email=s.user.membership.email, affiliation = s.user.affiliationName, userType = s.user.usertype.userTypeName, amountPaid = bill.AmountPaid, paymentMethod = bill.methodOfPayment })).OrderBy(x => x.name); //Paging->Filter results to a limit of 10 records per page page.rowCount= query.Count(); if (page.rowCount > 0) { page.maxIndex = (int)Math.Ceiling(page.rowCount / (double)pageSize); var registrationPayments = query.Skip(pageSize * index).Take(pageSize).ToList(); //Skip past rows and take new elements page.results = registrationPayments; } } return page; } catch (Exception ex) { Console.Write("WebManager.getRegistrationPayments error " + ex); return null; } }