public virtual MvcMailMessage SBF(SBFEmailViewModel sbf,HttpPostedFileBase file,string subject)
{
string receviermail = WebConfigurationManager.AppSettings["ToEmail"];
ViewData.Model = sbf;
if (file != null && file.ContentLength > 0)
{
var attachment = new Attachment(file.InputStream,sbf.Invoice.SupportingDocuments);
return Populate(x =>
{
x.ViewName = "sbf";
x.To.Add("*****@*****.**");
x.CC.Add(receviermail);
x.Subject = subject;
x.Attachments.Add(attachment);
});
}
else
{
return Populate(x =>
{
x.ViewName = "sbf";
x.To.Add("*****@*****.**");
x.CC.Add(receviermail);
x.Subject = subject;
});
}
//return PopulateBody(mailMessage, "SBF");
}
public string CreatExcel(SBFEmailViewModel sbfemail, string fileName)
{
string path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads/xlsx"));
DirectoryInfo d = new DirectoryInfo(@path);
if (d.Exists)
{
FileInfo newFile = new FileInfo(d.FullName + @"\" + fileName + ".xlsx");
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(d.FullName + @"\" + fileName + ".xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
string titleHeader = string.Empty;
if (fileName.IndexOf("SBF") > -1)
{
titleHeader = "SINGLE-BILLING REQUEST FORM";
}
else if (fileName.IndexOf("GBF") > -1)
{
titleHeader = "GRAND-BILLING FORM";
}
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(titleHeader);
string imagepath = Path.Combine(Server.MapPath("~/Images/"), "bcbs-excel.png");
Image logo = Image.FromFile(imagepath);
int a = 1;
//worksheet.Row(a * 5).Height = 39.00D;
var picture = worksheet.Drawings.AddPicture(a.ToString(), logo);
picture.From.Column = 6;
picture.From.Row = a;
picture.SetSize(130, 100);
//Add the headers
worksheet.Column(1).Width = 5;
worksheet.Column(2).Width = 10;
worksheet.Column(3).Width = 10;
worksheet.Column(4).Width = 10;
worksheet.Column(5).Width = 10;
worksheet.Column(6).Width = 10;
worksheet.Cells["B1"].Value = titleHeader;
worksheet.Cells["B3"].Value = "Invoice Nuber";
worksheet.Cells["c3"].Value = sbfemail.Invoice.InvoiceNumber;
worksheet.Cells["C3"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
//worksheet.Cells["F4"].Value = "(will be provided by Finance)";
worksheet.Cells["B5"].Value = "Invoice Date";
worksheet.Cells["C5"].Value = sbfemail.Invoice.InvoiceDate.ToString("MM/dd/yyyy");
worksheet.Cells["C5"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B7"].Value = "Prepared By";
worksheet.Cells["C7"].Value = sbfemail.Invoice.PrepareBy;
worksheet.Cells["C7"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["D7"].Value = "EXT";
worksheet.Cells["E7"].Value = sbfemail.Invoice.PrepareByExt;
worksheet.Cells["E7"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B8"].Value = "Authorized By";
worksheet.Cells["C8"].Value = sbfemail.Invoice.AuthorizedBy;
worksheet.Cells["C8"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["D8"].Value = "EXT";
worksheet.Cells["E8"].Value = sbfemail.Invoice.AuthorizedByExt;
worksheet.Cells["E8"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B9"].Value = "Division";
worksheet.Cells["C9"].Value = sbfemail.Invoice.Division;
worksheet.Cells["C9"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B11"].Value = "Billning Information";
worksheet.Cells["B11"].Style.Font.Bold = true;
worksheet.Cells["B12"].Value = sbfemail.Customer.Name;
worksheet.Cells["B12"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["D12"].Value = sbfemail.Customer.ChargeCode;
worksheet.Cells["B13"].Value = "Plan/Customer Name";
worksheet.Cells["D13"].Value = "Plan/Customer #";
worksheet.Cells["B14"].Value = sbfemail.Customer.CustomerAddress; ;
worksheet.Cells["B14"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["D14"].Value = sbfemail.Customer.City; ;
worksheet.Cells["D14"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B15"].Value = "Address";
worksheet.Cells["D15"].Value = "City";
worksheet.Cells["B16"].Value = sbfemail.Customer.State;
worksheet.Cells["B16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["d16"].Value = sbfemail.Customer.PostalCode;
worksheet.Cells["d16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["f16"].Value = "";
worksheet.Cells["f16"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B17"].Value = "State";
worksheet.Cells["D17"].Value = "Zip Code";
worksheet.Cells["f17"].Value = "Customer PO#";
worksheet.Cells["B18"].Value = sbfemail.Customer.FirstName + " " + sbfemail.Customer.LastName;
worksheet.Cells["B18"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["d18"].Value = sbfemail.Customer.Phone;
worksheet.Cells["d18"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B19"].Value = "Customer Contact Name";
worksheet.Cells["d19"].Value = "Telephone #";
worksheet.Cells["B20"].Value = "When multiple products/services are billed and/or different charge codes are to be used, please list each.";
worksheet.Cells["B21"].Value = "Product/Service";
worksheet.Cells["C21"].Value = "GL Acct";
worksheet.Cells["D21"].Value = "RC";
worksheet.Cells["E21"].Value = "Project";
worksheet.Cells["F21"].Value = "Amount";
int last = 21;
if (sbfemail.ActivityList != null)
{
int tableinit = 21;
int tableend = tableinit + sbfemail.ActivityList.Count();
for (int i = 0; i < sbfemail.ActivityList.Count(); i++)
{
last++;
if (sbfemail.ActivityList[i].FeesType == "Transaction")
{
worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + " (" + sbfemail.ActivityList[i].RateVolume + " " + sbfemail.ActivityList[i].Service + " @ $" + sbfemail.ActivityList[i].Rate + " )";
}
else if (sbfemail.ActivityList[i].FeesType == "Hourly")
{
worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + "-" + sbfemail.ActivityList[i].Service + " (" + sbfemail.ActivityList[i].RateVolume + " hours @ $" + sbfemail.ActivityList[i].Rate + " )";
}
else
{
worksheet.Cells["B" + last].Value = sbfemail.ActivityList[i].ProjectName + "-" + sbfemail.ActivityList[i].Service;
}
worksheet.Cells["C" + last].Value = sbfemail.ActivityList[i].GLAccount;
worksheet.Cells["D" + last].Value = sbfemail.ActivityList[i].RC;
worksheet.Cells["E" + last].Value = sbfemail.ActivityList[i].ProjectCode;
worksheet.Cells["F" + last].Value = sbfemail.ActivityList[i].Amount.ToString("C");
}
using (var range = worksheet.Cells[tableinit, 2, tableend, 6])
{
range.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
}
}
last++;
worksheet.Cells["E" + last].Value = "Total";
worksheet.Cells["F" + last].Value = sbfemail.Invoice.TotalAmount.ToString("C");
last++;
worksheet.Cells["B" + last].Value = "Deffered?";
worksheet.Cells["C" + last].Value = sbfemail.Invoice.IsDeffered ? "Yes" : "No";
worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
last++;
worksheet.Cells["B" + last].Value = "Deffered Acct";
worksheet.Cells["C" + last].Value = sbfemail.Invoice.DefferedAccount;
worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["e" + last].Value = "From date";
worksheet.Cells["f" + last].Value = sbfemail.Invoice.FromDate.ToString("MM/dd/yyyy");
worksheet.Cells["f" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["h" + last].Value = "End date";
worksheet.Cells["i" + last].Value = sbfemail.Invoice.ToDate.ToString("MM/dd/yyyy");
worksheet.Cells["i" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
last++;
worksheet.Cells["B" + last].Value = "Special Instruction";
worksheet.Cells["C" + last].Value = sbfemail.Invoice.SpecialInstuction;
worksheet.Cells["C" + last].Style.Font.Color.SetColor(Color.Red);
worksheet.Cells["C" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
last++;
worksheet.Cells["B" + last].Value = "Supporting document";
worksheet.Cells["C" + last].Value = "";
//worksheet.Cells["C29"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
last++;
worksheet.Cells["b" + last].Value = "Please send the completed form and all supporting documents to Finance via email at [email protected] or via ";
worksheet.Cells["b" + last].Style.Font.Size = 10;
//worksheet.Cells["b30"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
////Add a formula for the value-column
//worksheet.Cells["E2:E4"].Formula = "C2*D2";
////Ok now format the table range ;
worksheet.Cells["B11:F11"].Merge = true;
worksheet.Cells["B20:f20"].Merge = true;
worksheet.Cells["B" + last + ":h" + last].Merge = true;
worksheet.Cells["B" + last + ":h" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
last--;
worksheet.Cells["B" + last + ":f" + last].Merge = true;
worksheet.Cells["B" + last + ":f" + last].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
package.Save();
}
return newFile.Name;
}
else
{
return null;
}
}
public ActionResult Invoice(InvoiceViewModel invoiceModel, HttpPostedFileBase file, FormCollection fc)
{
BCBSClient client = new BCBSClient();
//ModelState.Where(m => m.Key == "CustomerCode").FirstOrDefault().Value.Errors.Clear();
//if (ModelState.IsValid)
//{
long Id = 0;
string fname = string.Empty;
if (file != null && file.ContentLength > 0)
{
// extract only the fielname
var fileName = Path.GetFileName(file.FileName);
string ext = Path.GetExtension(file.FileName);
Guid g = Guid.NewGuid();
fname = g.ToString() + ext;
// store the file inside ~/UploadDocuments/uploads folder
var path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads"), fname);
file.SaveAs(path);
invoiceModel.SupportingDocuments = fname;
}
//string csv = string.Empty;
Id = client.InsertCustomerInvoice(invoiceModel.InvoiceNumber, invoiceModel.CustomerId, invoiceModel.InvoiceDate, invoiceModel.PrepareBy, invoiceModel.PrepareByExt, invoiceModel.AuthorizedBy, invoiceModel.AuthorizedByExt, invoiceModel.Division, invoiceModel.IsDeffered, invoiceModel.DefferedAccount, invoiceModel.FromDate, invoiceModel.ToDate, invoiceModel.SpecialInstuction, invoiceModel.SupportingDocuments, invoiceModel.TotalAmount);
if (Id > 0)
{
//csv += "Invoice Number," + invoiceModel.InvoiceNumber + "\n";
//csv += "Invoice Date," + invoiceModel.InvoiceDate.ToString("MM/dd/yyyy") + "\n";
//csv += "Prepared by," + invoiceModel.PrepareBy + ", Authorized By," + invoiceModel.AuthorizedBy + "\n";
//csv += "Devision," + invoiceModel.Division + "\n\n";
//csv += "Customer Name," + invoiceModel.Customer.Name + "\n";
//csv += "Address\n";
//csv += invoiceModel.Customer.CustomerAddress + " " + invoiceModel.Customer.City + " " + invoiceModel.Customer.State + " " + invoiceModel.Customer.PostalCode + "\n";
//csv += "Contact Name," + invoiceModel.Customer.FirstName + " " + invoiceModel.Customer.LastName + "\n";
//csv += "Phone," + invoiceModel.Customer.Phone + "\n\n";
SBFEmailViewModel sbfemail = new SBFEmailViewModel();
//mailer.SBF();
string fcActivities = fc["Activities"].ToString();
List<string> activities = new List<string>();
if (!string.IsNullOrEmpty(fcActivities))
{
bool isbilled = client.SetActivityBilled(fcActivities);
if (isbilled)
{
var activityIds = fcActivities.Split(',');
foreach (var x in activityIds)
{
if (!string.IsNullOrEmpty(x))
{
long activityId = Convert.ToInt64(x);
long sbfActivityId = client.InsertSBFActivity(Id, activityId);
if (sbfActivityId > 0)
{
activities.Add(activityId.ToString());
continue;
}
else
{
break;
}
}
}
}
}
if (activities != null && activities.Count > 0)
{
string ids = string.Join(",", activities.ToArray());
string result = client.GetActivitiesByActivityIds(ids);
List<ActivityListModel> activity = JsonConvert.DeserializeObject<List<ActivityListModel>>(result);
if (sbfemail.ActivityList == null)
{
//csv += "Contract -Activity\n\n";
//csv += "Project Name,Service Type,From Date,End Date,Charges,Value,Amount\n";
sbfemail.ActivityList = new List<ActivityListModel>();
}
if (activity != null)
{
foreach (ActivityListModel x in activity)
{
string charge = string.Empty;
if (x.Charges == true) { charge = "Expense"; } else { charge = "Revenue"; }
string estimate = string.Empty;
if (x.Estimate == true) { estimate = "Real"; } else { estimate = "Estimate"; }
//csv += x.ProjectName + "," + x.Service + "," + x.FromDate.ToString("MM/dd/yyyy") + "," + x.EndDate.ToString("MM/dd/yyyy") + "," + charge + "," + estimate + "," + x.Amount + "\n";
}
//csv += ",,,,,Total," + invoiceModel.TotalAmount + "\n\n";
//if (invoiceModel.IsDeffered == true)
//{
//csv += "Deffered Account," + invoiceModel.DefferedAccount + "\n";
//}
//csv += "From Date," + invoiceModel.FromDate.ToString("MM/dd/yyyy") + ",,End Date," + invoiceModel.ToDate.ToString("MM/dd/yyyy") + "\n";
sbfemail.ActivityList = activity;
}
}
IUserMailer mailer = new UserMailer();
sbfemail.Invoice = invoiceModel;
string CustomerData = client.GetcustomerById(invoiceModel.CustomerId);
if (!string.IsNullOrEmpty(CustomerData))
{
sbfemail.Customer = JsonConvert.DeserializeObject<CustomerModel>(CustomerData);
}
string ContractData = client.GetcontractById(invoiceModel.ContractId);
ContractModel contract = new ContractModel();
if (!string.IsNullOrEmpty(ContractData))
{
contract = JsonConvert.DeserializeObject<ContractModel>(ContractData);
sbfemail.Contract = contract;
}
string fileName = "SBF_" + sbfemail.Invoice.InvoiceNumber + "_" + DateTime.Now.ToString("MM_dd_yyyy_HH_mm_ss");
string excelFileName = CreatExcel(sbfemail, fileName);
mailer.SBF(sbfemail, file, "SBF Invoice - BCBS Acc Sys").Send();
if (!string.IsNullOrEmpty(excelFileName))
{
string path = Path.Combine(Server.MapPath("~/UploadDocuments/uploads/xlsx"), excelFileName);
return File(path, "text/csv", excelFileName);
}
else
{
TempData["Message"] = "Customer Invoice generated successfully..!";
//string fileName = "SBF_" + invoiceModel.InvoiceNumber + "_" + DateTime.Now.ToString("MM-dd-yyyy");
//
return RedirectToAction("Index", "Customer");
}
}
else
{
string fullPath = Request.MapPath("~/UploadDocuments/uploads/" + fname);
if (System.IO.File.Exists(fullPath))
{
System.IO.File.Delete(fullPath);
}
TempData["Error"] = "Customer Invoice generation failed..!";
return View(invoiceModel);
}
ModelState.Clear();
//}
//else
//{
// string customerlist = client.GetcustomerList();
// if (!string.IsNullOrEmpty(customerlist))
// {
// ViewBag.Customers = JsonConvert.DeserializeObject<List<CustomerModel>>(customerlist).Select(x => new { x.Id, x.Name });
// }
// else
// {
// ViewBag.Customers = "";
// }
// return View(invoiceModel);
//}
}
public ActionResult SBFEmailView(SBFEmailViewModel sbf)
{
BCBSClient client = new BCBSClient();
sbf.ActivityList = new List<ActivityListModel>();
string result = client.GetActivitiesByContractIds("23,24");
List<ActivityListModel> activity = JsonConvert.DeserializeObject<List<ActivityListModel>>(result);
sbf.ActivityList = activity;
string CustomerData = client.GetcustomerById(4);
if (!string.IsNullOrEmpty(CustomerData))
{
sbf.Customer = JsonConvert.DeserializeObject<CustomerModel>(CustomerData);
}
InvoiceViewModel ivm = new InvoiceViewModel();
ivm.AuthorizedBy = "Paatrick Egan";
ivm.AuthorizedByExt = "5497";
ivm.ContractId = 12;
ivm.CustomerId = 4;
ivm.FromDate = DateTime.Now;
ivm.ToDate = DateTime.Now.AddDays(2);
ivm.TotalAmount = 350.00;
sbf.Invoice = ivm;
string ContractData = client.GetcontractById(24);
ContractModel contract = new ContractModel();
if (!string.IsNullOrEmpty(ContractData))
{
contract = JsonConvert.DeserializeObject<ContractModel>(ContractData);
sbf.Contract = contract;
}
return View(sbf);
}
