Create email notification in C#.net

We have a script which runs each day to send out notifications and we want to add a new email to this. This will be a fairly complicated notification, the email will be sent out only on specific days depending on user settings and will need to include attachments based on user settings. Details: - use /emailalerts.cshtml. This is where our other notifications currently are. - Your script should start by cycling through all of the available loans (these are in the database table 'loans'). Use loans where: Status = 1 AND Deleted = 0 - The email is sent to the Originator assigned to the loan. In the database loans > OriginatorId (UserId in table UserProfile). If this is 0 or null no email is sent - The email is sent out only on specific days based on the loan settings. Settings are in the database table 'loans'. The fields are AlertOptOut, AlertMon, AlertTues, AlertWed, AlertThurs, AlertFri. If a field = 1 send the email. If it= 0 don't send. If AlertOptOut = 1 then no emails should be sent for any days. ie. If a AlertMon = 1 and AlertThurs = 1, then the emails are sent on Mondays and Thursdays. - Sometimes a loan will not be setup yet and the fields will be null. If the fields are null check the table 'UserProfile' for the same settings. To know which user to check use the loanID to check for the assigned Originator. SQL for that is: SELECT * FROM UserProfile WHERE UserId = @0", [login to view URL] - The email subject should be: Your ALEX Notification for {LoanName} - The body of the email is: This email contains the outstanding documents for: Loan Name: {LoanName} Loan Number: {LoanNumber} Loan Percent Completion: {[login to view URL](loanid_int_percent);} Brokers/Borrowers: {list of borrowers assigned to this loan} SQL is like: select [login to view URL], [login to view URL], [login to view URL] from loan_user_access lua inner join UserProfile u on [login to view URL] = [login to view URL] where [login to view URL] = @0 {Borrower Name #1 : Borrower Email #1} {Borrower Name #2 : Borrower Email #2} {etc.} Outstanding Documents: {table of all outstanding documents, these are in the table 'documents' WHERE doc_deleted = 0 AND doc_active = 1 AND doc_approval != 'Approved' AND doc_type != 'Screening' AND doc_type != 'Legal' AND doc_type != 'Loan App'} {Doc #1 Name} {Doc #1 Borrower Name(doc_userid field in db)} {Doc #1 Status} {Doc #1 Rejection Reason (doc_rejection)} {Doc #2 Name} {Doc #2 Borrower Name(doc_userid field in db)} {Doc #2 Status} {Doc #2 Rejection Reason (doc_rejection)} {etc.} {Sort the table my Borrower name, then doc_type} - There is another setting in the database called 'AlertExcludePDF' if this = 0 then the email will need to have attachments. For any outstanding documents we want to attached a blank PDF form. These are in the table documents > doc_pdf_download - Most documents don't have a PDF and won't need the attachment. - The attachments should be renamed to include the name of the borrower + the document name, ie. {borrower name}+{doc_name}.pdf. Some document names may have illegal characters like spaces or backslashes. Replace any illegal characters with underscore (_) before attaching. - There should be one other attachment called {LoanName}-Summary.pdf. This will be a generated PDF which shows all of the outstanding documents (same as the table in the body of the email). There is a very similar script which you can use to build this PDF at /administrator/tasks/convertpdf-report.cshtml. That report is almost the same, except it only includes 1 borrower, the new script will need to include all borrowers assigned to the loan. This generated PDF should be deleted from the server after the email is sent, we don't want a folder full of unused PDF files. There is already an email alert very similar to this one already build in /emailalerts.cshtml. You can reuse a lot of code from that. Please let me know if you have any questions.