public ActionResult TgtVsAchFiltered(string CustomerId, string CustomerType, string MonthVal, string YearVal)
{
TgtVsAchViewModel objData = new TgtVsAchViewModel();
try
{
if (CustomerType.Contains("#"))
{
CustomerType = CustomerType.Remove(CustomerType.Length - 1, 1);
}
var objCust = CDR.GetCustomerDetail(CustomerId, CustomerType);
objCust.CustomerCategory = "Participant";
Session["ChitaleUser"] = objCust;
CustomerDetail UserSession = new CustomerDetail();
UserSession = (CustomerDetail)Session["ChitaleUser"];
objData.objOverAll = PLR.GetOverallData(UserSession.CustomerId, MonthVal, YearVal);
objData.objCategory = PLR.GetCategoryData(UserSession.CustomerId, MonthVal, YearVal);
objData.objSubCategory = PLR.GetSubCategoryData(UserSession.CustomerId, MonthVal, YearVal);
objData.objProducts = PLR.GetProductData(UserSession.CustomerId, MonthVal, YearVal);
}
catch (Exception ex)
{
newexception.AddException(ex);
}
return(PartialView("_TgtVsAchList", objData));
}
public ActionResult TgtVsAch(string CustomerId, string CustomerType)
{
TgtVsAchViewModel objData = new TgtVsAchViewModel();
try
{
var objCust = CDR.GetCustomerDetail(CustomerId, CustomerType);
objCust.CustomerCategory = "Participant";
Session["ChitaleUser"] = objCust;
CustomerDetail UserSession = new CustomerDetail();
UserSession = (CustomerDetail)Session["ChitaleUser"];
objData.objOverAll = PLR.GetOverallData(UserSession.CustomerId, "", "");
objData.objCategory = PLR.GetCategoryData(UserSession.CustomerId, "", "");
objData.objSubCategory = PLR.GetSubCategoryData(UserSession.CustomerId, "", "");
objData.objProducts = PLR.GetProductData(UserSession.CustomerId, "", "");
string[] names = DateTimeFormatInfo.CurrentInfo.MonthNames;
List <SelectListItem> MonthItems = new List <SelectListItem>();
int Month = 1;
foreach (var item in names)
{
MonthItems.Add(new SelectListItem
{
Text = Convert.ToString(item),
Value = Convert.ToString(Month)
});
Month++;
}
MonthItems.RemoveAt(12);
objData.MonthItems = MonthItems;
List <SelectListItem> YearItems = new List <SelectListItem>();
for (int i = 0; i <= 10; i++)
{
int year = DateTime.Now.Year;
YearItems.Add(new SelectListItem
{
Text = Convert.ToString(year - i),
Value = Convert.ToString(year - i)
});
}
objData.YearItems = YearItems;
}
catch (Exception ex)
{
newexception.AddException(ex);
}
return(View(objData));
}
public ActionResult ExportFocusVsAch(string Month, string Year)
{
try
{
System.Data.DataTable table = new System.Data.DataTable();
System.Data.DataTable tableToExport = new System.Data.DataTable();
tableToExport.Columns.Add("Name");
tableToExport.Columns.Add("Type");
tableToExport.Columns.Add("VolumeFocus");
tableToExport.Columns.Add("VolumeAch");
tableToExport.Columns.Add("VolumeAch%");
tableToExport.Columns.Add("VolumePoints");
tableToExport.Columns.Add("ValueFocus");
tableToExport.Columns.Add("ValueAch");
tableToExport.Columns.Add("ValueAch%");
tableToExport.Columns.Add("ValuePoints");
CustomerDetail UserSession = new CustomerDetail();
UserSession = (CustomerDetail)Session["ChitaleUser"];
TgtVsAchViewModel objData = new TgtVsAchViewModel();
objData.objOverAll = PLR.GetOverallData(UserSession.CustomerId, Month, Year);
objData.objCategory = PLR.GetCategoryData(UserSession.CustomerId, Month, Year);
objData.objSubCategory = PLR.GetSubCategoryData(UserSession.CustomerId, Month, Year);
objData.objProducts = PLR.GetProductData(UserSession.CustomerId, Month, Year);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(TgtvsAchMaster));
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(objData.objOverAll) ?? DBNull.Value;
}
table.Rows.Add(row);
foreach (TgtvsAchMaster item1 in objData.objCategory)
{
DataRow row1 = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row1[prop.Name] = prop.GetValue(item1) ?? DBNull.Value;
}
table.Rows.Add(row1);
foreach (TgtvsAchMaster item2 in objData.objSubCategory)
{
if (item1.CategoryCode == item2.CategoryCode)
{
DataRow row2 = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row2[prop.Name] = prop.GetValue(item2) ?? DBNull.Value;
}
table.Rows.Add(row2);
foreach (TgtvsAchMaster item3 in objData.objProducts)
{
if (item2.SubCategoryCode == item3.SubCategoryCode)
{
DataRow row3 = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row3[prop.Name] = prop.GetValue(item3) ?? DBNull.Value;
}
table.Rows.Add(row3);
}
}
}
}
}
foreach (DataRow item in table.Rows)
{
DataRow newDr = tableToExport.NewRow();
newDr["Name"] = item["Name"];
newDr["Type"] = item["ProductType"];
newDr["VolumeFocus"] = item["VolumeTgt"];
newDr["VolumeAch"] = item["VolumeAch"];
newDr["VolumeAch%"] = item["VolumeAchPercentage"];
newDr["VolumePoints"] = item["VolumePoints"];
newDr["ValueFocus"] = item["ValueTgt"];
newDr["ValueAch"] = item["ValueAch"];
newDr["ValueAch%"] = item["ValueAchPercentage"];
newDr["ValuePoints"] = item["ValuePoints"];
tableToExport.Rows.Add(newDr);
}
string ReportName = "Focus Vs Ach";
string fileName = ReportName + ".xlsx";
using (XLWorkbook wb = new XLWorkbook())
{
tableToExport.TableName = ReportName;
wb.Worksheets.Add(tableToExport);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
}
}
catch (Exception ex)
{
newexception.AddException(ex);
return(null);
}
}
