public ActionResult AcknowledgementForm(AcknowledgementModel avm, FormCollection fc)
{
BCBSClient client = new BCBSClient();
string removedProjects = fc["RemovedProjects"].ToString();
string removedServices = fc["RemovedServices"].ToString();
List<string> removedProjectList = new List<string>();
if (!string.IsNullOrEmpty(removedProjects))
{
removedProjectList = removedProjects.Split(',').ToList();
}
List<string> removedServiceList = new List<string>();
if (!string.IsNullOrEmpty(removedServices))
{
removedServiceList = removedServices.Split(',').ToList();
}
long ackId = client.InsertCustomerAcknoeledgement(avm.CustomerId);
if (ackId > 0)
{
if (avm.Projects != null)
{
for (int i = 0; i < avm.Projects.Count(); i++)
{
if (removedProjectList.Count > 0)
{
var r = removedProjectList.Where(x => x.Equals(avm.Projects[i].Project.Id + "@" + i));
if (r != null)
{
if (r.Count() > 0)
{
continue;
}
}
}
if (avm.Projects[i].Services != null)
{
for (int j = 0; j < avm.Projects[i].Services.Count; j++)
{
var r = removedServiceList.Where(x => x.Equals(i + "_" + j));
if (r != null)
{
if (r.Count() > 0)
{
continue;
}
}
long ackServiceId = client.InsertAcknowledgementServices(ackId, avm.Projects[i].Project.Id, avm.Projects[i].Services[j].Id, avm.Projects[i].Services[j].Total, avm.Projects[i].Services[j].NewVolume, avm.Projects[i].FromDate, avm.Projects[i].EndDate, avm.Projects[i].Services[j].FeesType);
}
}
}
string fileName = "Ack_" + DateTime.Now.ToString("MM_dd_yyyy_HH_mm_ss");
string retunfileName = CreateAcknowledgementExcel(avm, fileName, removedProjects, removedServices);
if (!string.IsNullOrEmpty(retunfileName))
{
string path = Path.Combine(Server.MapPath("~/UploadDocuments/Acknowledgements"), retunfileName);
return File(path, "text/csv", retunfileName);
}
else
{
TempData["Message"] = "Customer grand bill generated successfully..!";
//string fileName = "SBF_" + invoiceModel.InvoiceNumber + "_" + DateTime.Now.ToString("MM-dd-yyyy");
//
return RedirectToAction("acknowledgements", "customer");
}
//TempData["Message"] = "Acknowledgement Bill generated successfully..";
}
}
return View();
}
public ActionResult ViewAcknowledgeForm(AcknowledgementModel avm, FormCollection fc)
{
bool isResult = false;
if (avm.Id > 0)
{
BCBSClient client = new BCBSClient();
List<CustomerAcknowledgementServicesModel> ackService = new List<CustomerAcknowledgementServicesModel>();
string dataResult = client.GetAcknowledgementServicesbyAcknowledgemetnId(Convert.ToInt64(avm.Id));
if (!string.IsNullOrEmpty(dataResult))
{
ackService = JsonConvert.DeserializeObject<List<CustomerAcknowledgementServicesModel>>(dataResult);
}
if (ackService.Count > 0)
{
foreach (CustomerAcknowledgementServicesModel ack in ackService)
{
string ContractCode = GenerateUniqueContractCode().ToUpper();
long id = client.Insertcontract(ack.CustomerId, ack.ServiceId, ack.FromDate, ack.EndDate, true, false, "Active", ack.Volume, ack.Total, ack.ProjectId, "", ContractCode, "", ack.FeesType);
//long id = client.Insertcontract(ack.CustomerId, ack.ServiceId, ack.FromDate, ack.EndDate, true, false, "Active", "", ack.Total, ack.ProjectId, "", ContractCode, "", "");
if (id > 0)
{
isResult = true;
}
else
{
break;
}
}
if (isResult == true)
{
long id = client.AcknowledgementApprove(Convert.ToInt64(avm.Id));
TempData["Message"] = "Acknowledgement Approved ! Contract Added Successfully!!";
return RedirectToAction("acknowledgements", "customer");
}
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 = "";
}
string servicelist = client.GetServiceTypeList();
if (!string.IsNullOrEmpty(servicelist))
{
ViewBag.Services = JsonConvert.DeserializeObject<List<ServiceModel>>(servicelist).Select(x => new { x.Id, x.Name });
}
else
{
ViewBag.Services = "";
}
string projectList = client.GetProjectList();
if (!string.IsNullOrEmpty(projectList))
{
ViewBag.Projects = JsonConvert.DeserializeObject<List<ProjectModel>>(projectList).Select(x => new { x.Id, x.Name });
}
else
{
ViewBag.Projects = "";
}
}
}
}
//BCBSClient client = new BCBSClient();
//string removedProjects = fc["RemovedProjects"].ToString();
//string removedServices = fc["RemovedServices"].ToString();
//List<string> removedProjectList = new List<string>();
//if (!string.IsNullOrEmpty(removedProjects))
//{
// removedProjectList = removedProjects.Split(',').ToList();
//}
//List<string> removedServiceList = new List<string>();
//if (!string.IsNullOrEmpty(removedServices))
//{
// removedServiceList = removedServices.Split(',').ToList();
//}
//long ackid = avm.Id;
////long ackId = client.InsertCustomerAcknoeledgement(avm.CustomerId);
//if (ackid > 0)
//{
// if (avm.Projects != null)
// {
// for (int i = 0; i < avm.Projects.Count(); i++)
// {
// if (removedProjectList.Count > 0)
// {
// var r = removedProjectList.Where(x => x.Equals(avm.Projects[i].Project.Id + "@" + i));
// if (r != null)
// {
// if (r.Count() > 0)
// {
// continue;
// }
// }
// }
// if (avm.Projects[i].Services != null)
// {
// for (int j = 0; j < avm.Projects[i].Services.Count; j++)
// {
// var r = removedServiceList.Where(x => x.Equals(i + "_" + j));
// if (r != null)
// {
// if (r.Count() > 0)
// {
// continue;
// }
// }
// long ackserviceId = avm.Projects[i].Services[j].Id;
// //long ackServiceId = client.InsertAcknowledgementServices(ackid, avm.Projects[i].Project.Id, avm.Projects[i].Services[j].Id, avm.Projects[i].Services[j].Total, avm.Projects[i].FromDate, avm.Projects[i].EndDate);
// }
// }
// }
// string fileName = "Ack_" + DateTime.Now.ToString("MM_dd_yyyy_HH_mm_ss");
// string retunfileName = CreateAcknowledgementExcel(avm, fileName, removedProjects, removedServices);
// if (!string.IsNullOrEmpty(retunfileName))
// {
// string path = Path.Combine(Server.MapPath("~/UploadDocuments/Acknowledgements"), retunfileName);
// //return File(path, "text/csv", retunfileName);
// }
// else
// {
// TempData["Message"] = "Customer grand bill generated successfully..!";
// //string fileName = "SBF_" + invoiceModel.InvoiceNumber + "_" + DateTime.Now.ToString("MM-dd-yyyy");
// //
// return RedirectToAction("acknowledgements", "customer");
// }
// //TempData["Message"] = "Acknowledgement Bill generated successfully..";
// }
//}
return View(avm);
}
public string CreateAcknowledgementExcel(AcknowledgementModel avm, string fileName, string removedProjects, string removedServices)
{
string path = Path.Combine(Server.MapPath("~/UploadDocuments/Acknowledgements"));
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;
titleHeader = "BILLING ACKNOWLEDGEMENT 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;
CustomerModel customer = new CustomerModel();
BCBSClient client = new BCBSClient();
customer = JsonConvert.DeserializeObject<CustomerModel>(client.GetcustomerById(avm.CustomerId));
worksheet.Cells["B1"].Value = titleHeader;
worksheet.Cells["B3"].Value = "PLAN/ORGANIZATION:";
worksheet.Cells["c3"].Value = customer.Name;
worksheet.Cells["C3"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
//worksheet.Cells["F4"].Value = "(will be provided by Finance)";
worksheet.Cells["B5"].Value = "ATTENTION:";
worksheet.Cells["C5"].Value = customer.FirstName + " " + customer.LastName;
worksheet.Cells["C5"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["B7"].Value = "ADDRESS:";
worksheet.Cells["C7"].Value = customer.CustomerAddress;
//worksheet.Cells["C7"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["C8"].Value = customer.City;
//worksheet.Cells["C8"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
worksheet.Cells["C9"].Value = customer.State + " " + customer.Country + " " + customer.PostalCode;
//worksheet.Cells["C9"].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
int last = 11;
int tableinit = 11;
int tableend = 0;
for (int i = 0; i < avm.Projects.Count(); i++)
{
List<string> removedProjectList = new List<string>();
if (!string.IsNullOrEmpty(removedProjects))
{
removedProjectList = removedProjects.Split(',').ToList();
}
if (removedProjectList.Count > 0)
{
var r = removedProjectList.Where(x => x.Equals(avm.Projects[i].Project.Id + "@" + i));
if (r != null)
{
if (r.Count() > 0)
{
continue;
}
}
}
last++;
worksheet.Cells["B" + last].Value = avm.Projects[i].Project.Name;
tableinit = last;
int totalinit = 0;
int totalend = 0;
for (int j = 0; j < avm.Projects[i].Services.Count; j++)
{
last++;
if (j == 0)
{
worksheet.Cells["C" + last].Value = "Service Name";
worksheet.Cells["D" + last].Value = "Volume";
worksheet.Cells["E" + last].Value = "Rate";
worksheet.Cells["F" + last].Value = "Total";
last++;
totalinit = last;
}
List<string> removedServiceList = new List<string>();
if (!string.IsNullOrEmpty(removedServices))
{
removedServiceList = removedServices.Split(',').ToList();
}
var r = removedServiceList.Where(x => x.Equals(i + "_" + j));
if (r != null)
{
if (r.Count() > 0)
{
last--;
continue;
}
}
worksheet.Cells["C" + last].Value = avm.Projects[i].Services[j].Name;
worksheet.Cells["D" + last].Value = avm.Projects[i].Services[j].NewVolume;
if (avm.Projects[i].Services[j].FeesType == "Transaction")
{
worksheet.Cells["E" + last].Value = avm.Projects[i].Services[j].Total.ToString("C") + " / " + avm.Projects[i].Services[j].NewVolume + " " + avm.Projects[i].Services[j].FeesType;//avm.Projects[i].Services[j].Volume;
}
else
{
worksheet.Cells["E" + last].Value = avm.Projects[i].Services[j].Total.ToString("C") + "/ " + avm.Projects[i].Services[j].FeesType;
}
worksheet.Cells["F" + last].Value = avm.Projects[i].Services[j].Total;
worksheet.Cells["F" + last].Style.Numberformat.Format = "$#,###0.00";
}
totalend = last;
tableend = last;
last++;
worksheet.Cells["F" + last].Formula = "=SUM(F" + totalinit + ":F" + totalend + ")";
worksheet.Cells["F" + last].Calculate();
worksheet.Cells["F" + last].Style.Numberformat.Format = "$#,###0.00";
last++;
using (var range = worksheet.Cells[tableinit, 2, tableend, 6])
{
range.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
}
}
package.Save();
}
return newFile.Name;
}
else
{
return null;
}
}
public ActionResult ViewAcknowledgeForm(string Id)
{
BCBSClient client = new BCBSClient();
AcknowledgementModel avm = new AcknowledgementModel();
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 = "";
}
string servicelist = client.GetServiceTypeList();
if (!string.IsNullOrEmpty(servicelist))
{
ViewBag.Services = JsonConvert.DeserializeObject<List<ServiceModel>>(servicelist).Select(x => new { x.Id, x.Name });
}
else
{
ViewBag.Services = "";
}
string projectList = client.GetProjectList();
if (!string.IsNullOrEmpty(projectList))
{
ViewBag.Projects = JsonConvert.DeserializeObject<List<ProjectModel>>(projectList).Select(x => new { x.Id, x.Name });
}
else
{
ViewBag.Projects = "";
}
if (!string.IsNullOrEmpty(Id))
{
List<CustomerAcknowledgementServicesModel> ackService = new List<CustomerAcknowledgementServicesModel>();
string dataResult = client.GetAcknowledgementServicesbyAcknowledgemetnId(Convert.ToInt64(Id));
if (!string.IsNullOrEmpty(dataResult))
{
ackService = JsonConvert.DeserializeObject<List<CustomerAcknowledgementServicesModel>>(dataResult);
if (ackService.Count > 0)
{
avm.Id = ackService[0].AcknowledementId;
avm.CustomerId = ackService[0].CustomerId;
avm.Status = ackService[0].Status;
}
}
if (ackService.Count > 0)
{
List<long> Projects = ackService.Select(x => x.ProjectId).Distinct().ToList();
var ServicebyProjects = ackService.ToList();
ViewBag.SelectedProjects = JsonConvert.SerializeObject(Projects);
ViewBag.ProjectServices = dataResult;
}
}
return View(avm);
}
