public ActionResult Index(string CompanyCode)
{
ViewBag.Company = CompanyCode;
List <SqlParameter> lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("Company", CompanyCode));
DataTable dtSubDetail = GNF.ExceuteStoredProcedure("SP_Validate_Roy_FireOffStart", lstParam3);
lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("Company", CompanyCode));
DataTable dtSubDetail2 = GNF.ExceuteStoredProcedure("SP_Validate_Roy_Result_Error", lstParam3);
DataTable dtSubDetail3 = GNF.ExceuteStoredProcedure("[SP_Validate_Roy_Data]");
//ViewBag.subDetail = dtSubDetail;// ConvertDataTableToHTML(dtSubDetail);
using (XLWorkbook wb = new XLWorkbook())
{
dtSubDetail3.TableName = "Data1";
wb.Worksheets.Add(dtSubDetail3);
//wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//wb.Style.Font.Bold = true;
//wb.find.Style.Fill.BackgroundColor=XLColor.LightGreen;
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx"));
}
}
return(View(dtSubDetail3));
}
public JsonResult GetCompany()
{
try
{
DataTable dt = GNF.ExceuteStoredProcedure("SP_SelectCompany");
return(Json(GNF.ConvertDataTabletoString(dt), JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(ex.ToString(), JsonRequestBehavior.AllowGet));
}
}
public ActionResult Export(string CompanyCode)
{
List <SqlParameter> lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("Company", CompanyCode));
DataTable dtSubDetail = GNF.ExceuteStoredProcedure("SP_Validate_Roy_Data");
//ViewBag.subDetail = dtSubDetail;// ConvertDataTableToHTML(dtSubDetail);
using (XLWorkbook wb = new XLWorkbook())
{
dtSubDetail.TableName = "Data1";
wb.Worksheets.Add(dtSubDetail);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx"));
}
}
return(RedirectToAction("Index"));
}
public ActionResult MailOut(string Company)
{
try
{
ViewBag.Message = "";
string FilePath = "E:\\IIS_Published_Websites\\RoyaltyErrorDataReports_Publish\\temp\\";
DirectoryInfo di = new DirectoryInfo(FilePath);
if (!di.Exists)
{
di.Create();
}
DataTable dtCompanies = GNF.ExceuteStoredProcedure("SP_RoyaltyErrorDataReports_Companies");
if (dtCompanies != null && dtCompanies.Rows.Count > 0)
{
foreach (DataRow dr in dtCompanies.Rows)
{
List <SqlParameter> lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("Company", dr["Company_Code"]));
DataTable dtSubDetail = GNF.ExceuteStoredProcedure("SP_Validate_Roy_FireOffStart", lstParam3);
if (dtSubDetail != null && dtSubDetail.Rows.Count > 0)
{
lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("Company", dr["Company_Code"]));
DataTable dtSubDetail2 = GNF.ExceuteStoredProcedure("SP_Validate_Roy_Result_Error", lstParam3);
DataTable dtSubDetail3 = GNF.ExceuteStoredProcedure("[SP_Validate_Roy_Data]");
//ViewBag.subDetail = dtSubDetail;// ConvertDataTableToHTML(dtSubDetail);
using (XLWorkbook wb = new XLWorkbook())
{
dtSubDetail3.TableName = "Data1";
wb.Worksheets.Add(dtSubDetail3);
//wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//wb.Style.Font.Bold = true;
//wb.find.Style.Fill.BackgroundColor=XLColor.LightGreen;
string FileName = "File-" + DateTime.Now.ToString("MMddyyyyHHmmsstt") + ".xlsx";
string path = FilePath + FileName;
wb.SaveAs(path);
lstParam3 = new List <SqlParameter>();
lstParam3.Add(new SqlParameter("CompanyCode", dr["Company_Code"]));
DataTable dtSubDetail4 = GNF.ExceuteStoredProcedure("SP_RoyaltyErrorDataReports_Email", lstParam3);
if (dtSubDetail4 != null && dtSubDetail4.Rows.Count > 0)
{
foreach (DataRow dr4 in dtSubDetail4.Rows)
{
List <Attachment> lstAttachment = new List <Attachment>();
FileInfo fi = new FileInfo(path);
string newFileName = (FilePath + (fi.Name.Replace(".xlsx", Guid.NewGuid().ToString() + ".xlsx")));
fi.CopyTo(newFileName);
lstAttachment.Add(new Attachment(newFileName));
string Subject = "Please be advised that the attached file contains errors on item setup. This is for Company '" + dr["Company_Code"].ToString() + "'";
string Body = "Please fix these errors within (3) business days upon receipt of this email. Please reach out to Eli Maiman with any questions or concerns";
NotificationHelper.SendMail(dr4["Email"].ToString(), Subject, Body, true, lstAttachment);
}
}
}
}
else
{
ViewBag.Message = "No data to sent in subDetail";
}
}
ViewBag.Message = "MailOut completed successfully";
}
else
{
ViewBag.Message = "No data to sent";
}
}
catch (Exception ex)
{
ViewBag.Message = ex.Message;
NotificationHelper.SendMail("*****@*****.**", "Mailout >> Exception occured", ex.Message + "<br>" + ex.StackTrace, true, null);
NotificationHelper.SendMail("*****@*****.**", "Mailout >> Exception occured", ex.Message + "<br>" + ex.StackTrace, true, null);
}
return(View());
}
