public ActionResult GetAllUnProcessedRecords(List <RoboticsUnProcessedModel> lstRobotics)
{
try
{
RoboticsRepository objrep = new RoboticsRepository();
if (lstRobotics.Count > 0)
{
var lst = lstRobotics.Where(t => t.IsManualProcessed == true).ToList();
foreach (var q in lst)
{
objrep.updateManualProcessedData(q.Id, q.ConnectionType);
}
}
List <RoboticsUnProcessedModel> lstunprocessData = new List <RoboticsUnProcessedModel>();
lstunprocessData = objrep.GetUnProcessedData();
RoboticProcessViewModel objviewModel = new RoboticProcessViewModel();
string message = string.Empty;
message = objviewModel.ExportInRoboticFormat(lstunprocessData);
ViewBag.Message = message;
lstunprocessData = new List <RoboticsUnProcessedModel>();
lstunprocessData = objrep.GetUnProcessedData();
ModelState.Clear();
return(View(lstunprocessData));
}
catch (Exception ex)
{
return(View());
}
}
// GET: RoboticsProcess
public ActionResult GetAllUnProcessedRecords()
{
try
{
RoboticsRepository objrep = new RoboticsRepository();
List <RoboticsUnProcessedModel> lstunprocessData = new List <RoboticsUnProcessedModel>();
lstunprocessData = objrep.GetUnProcessedData();
return(View(lstunprocessData));
}
catch (Exception ex)
{
return(View());
}
}
public ActionResult ExportToExcel()
{
try
{
RoboticsRepository objRep = new RoboticsRepository();
DataSet dsInvalidData = new DataSet();
dsInvalidData = objRep.GetInvalidData();
using (ExcelPackage pck = new ExcelPackage())
{
DataTable dt = new DataTable();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Invalid_Other_Data");
if (dsInvalidData.Tables.Contains("Invalid_Other_Data"))
{
ws.Cells["A1"].LoadFromDataTable(dsInvalidData.Tables[0], true, TableStyles.Medium15);
}
else
{
ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
}//You can Use TableStyles property of your desire.
//Read the Excel file in a byte array
ExcelWorksheet ws1 = pck.Workbook.Worksheets.Add("Invalid_Hardware_Only_Data");
if (dsInvalidData.Tables.Contains("Invalid_Hardware_Only_Data"))
{
ws1.Cells["A1"].LoadFromDataTable(dsInvalidData.Tables[1], true, TableStyles.Medium15);
}
else
{
ws1.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
}
Byte[] fileBytes = pck.GetAsByteArray();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=Invalid_Data" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(fileBytes);
Response.End();
}
return(View());
}
catch (Exception ex)
{
return(View());
}
}
public string Export_Add_CTN(List <RoboticsUnProcessedModel> lstUnprocessedData)
{
try
{
DataTable dt = new DataTable();
dt.TableName = "Add CTN";
dt.Clear();
dt.Columns.Add("Connection Type");
dt.Columns.Add("Ref Id");
dt.Columns.Add("Subscription");
dt.Columns.Add("Account Name");
dt.Columns.Add("BAN");
dt.Columns.Add("Tariff Code");
dt.Columns.Add("Commitment Length (Months)");
dt.Columns.Add("Commitment Start Date");
dt.Columns.Add("SOC Code 1");
dt.Columns.Add("SOC Code 2");
dt.Columns.Add("SOC Code 3");
dt.Columns.Add("SOC Code 4");
dt.Columns.Add("International Bar Remove?");
dt.Columns.Add("PAC");
dt.Columns.Add("Port In Date");
dt.Columns.Add("Accessory SKU");
dt.Columns.Add("Accessory Variant");
dt.Columns.Add("Accessory Charge");
dt.Columns.Add("Charging Account (Hardware/Airtime)");
dt.Columns.Add("Sim Card Required");
dt.Columns.Add("Sim Card SKU Code");
dt.Columns.Add("Existing SIM No.");
dt.Columns.Add("Delivery Address");
dt.Columns.Add("Delivery Type");
dt.Columns.Add("Delivery Charge(£)");
dt.Columns.Add("Discount Amount Per Month (£)");
dt.Columns.Add("Discount Length(Months)");
foreach (var q in lstUnprocessedData)
{
DataRow dr = dt.NewRow();
dr["Connection Type"] = q.ConnectionType;
dr["Ref Id"] = q.ReferenceNumber;
dr["Subscription"] = q.SubmissionDate;
dr["Account Name"] = q.AccountName;
dr["BAN"] = q.BAN;
dr["Tariff Code"] = q.TariffCode;
dr["Commitment Length (Months)"] = q.CommitmentLength;
dr["Commitment Start Date"] = q.CommitmentStartDate;
dr["SOC Code 1"] = q.SOC1Code;
dr["SOC Code 2"] = q.SOC2Code;
dr["SOC Code 3"] = q.SOC3Code;
dr["SOC Code 4"] = q.SOC4Code;
dr["International Bar Remove?"] = q.InternationalBarRemove == true ? "Yes" : "No";
dr["PAC"] = q.PACCode;
dr["Port In Date"] = q.PortDate;
dr["Accessory SKU"] = q.AccessorySKU;
dr["Accessory Variant"] = q.AccessoryVariant;
dr["Accessory Charge"] = q.AccessoryCharge;
dr["Charging Account (Hardware/Airtime)"] = q.ChargingAccount;
dr["Sim Card Required"] = q.SimRequired;
dr["Sim Card SKU Code"] = q.SimCardSKUCode;
dr["Existing SIM No."] = q.ExistingSIMNo;
dr["Delivery Address"] = q.DeliveryAddress;
dr["Delivery Type"] = q.DeliveryType;
dr["Delivery Charge(£)"] = q.DeliveryCharge;
dr["Discount Amount Per Month (£)"] = q.Discount;
dr["Discount Length(Months)"] = q.DiscountLength;
dt.Rows.Add(dr);
}
string message = string.Empty;
using (ExcelPackage objExcelPackage = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dt.TableName);
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
objWorksheet.Cells["A1"].LoadFromDataTable(dt, true);
objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10));
objWorksheet.Cells.AutoFitColumns();
//Format the header
using (ExcelRange objRange = objWorksheet.Cells[1, dt.Columns.Count])
{
objRange.Style.Font.Bold = true;
objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
objRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
objRange.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
string AddCTNFilePath = ConfigurationManager.AppSettings["AddCTNFilePath"].ToString();
//Write it back to the client
if (File.Exists(AddCTNFilePath))
{
message = "File is already exist to process for 'ADD CTN'";
return(message);
}
//Create excel file on physical disk
FileStream objFileStrm = File.Create(AddCTNFilePath);
objFileStrm.Close();
//Write content to excel file
File.WriteAllBytes(AddCTNFilePath, objExcelPackage.GetAsByteArray());
}
message = "File 'Add CTN' has processed succussfully.";
//processed data in database
RoboticsRepository objrep = new RoboticsRepository();
objrep.UpdateProcessedDataforAddCTN(lstUnprocessedData);
return(message);
}
catch (Exception ex)
{
return(ex.ToString() + " Method Name: Export_Add_CTN");
}
}
public string Export_Hardware_Credit(List <RoboticsUnProcessedModel> lstUnprocessedData)
{
try
{
DataTable dt = new DataTable();
dt.TableName = "Hardware Credit";
dt.Clear();
dt.Columns.Add("Connection Type");
dt.Columns.Add("Ref Id");
dt.Columns.Add("Airtime BAN");
dt.Columns.Add("Hardware BAN");
dt.Columns.Add("Hardware Fund (£)");
dt.Columns.Add("Airtime Fund (£)");
dt.Columns.Add("Buy Out Credit(VAT Exempt)(£)");
foreach (var q in lstUnprocessedData)
{
DataRow dr = dt.NewRow();
dr["Connection Type"] = q.ConnectionType;
dr["Ref Id"] = q.ReferenceNumber;
dr["Airtime BAN"] = q.AirtimeBAN;
dr["Hardware BAN"] = q.HardwareBAN;
dr["Hardware Fund (£)"] = q.HardwareFund;
dr["Airtime Fund (£)"] = q.AirtimeFund;
dr["Buy Out Credit(VAT Exempt)(£)"] = q.BuyOutCredit;
dt.Rows.Add(dr);
}
string message = string.Empty;
using (ExcelPackage objExcelPackage = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dt.TableName);
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
objWorksheet.Cells["A1"].LoadFromDataTable(dt, true);
objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10));
objWorksheet.Cells.AutoFitColumns();
//Format the header
using (ExcelRange objRange = objWorksheet.Cells[1, dt.Columns.Count])
{
objRange.Style.Font.Bold = true;
objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
objRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
objRange.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
string hardwareCreditPath = ConfigurationManager.AppSettings["HardwareCreditFilePath"].ToString();
//Write it back to the client
if (File.Exists(hardwareCreditPath))
{
message = "File is already exist to process for 'Hardware Credit'";
return(message);
}
//Create excel file on physical disk
FileStream objFileStrm = File.Create(hardwareCreditPath);
objFileStrm.Close();
//Write content to excel file
File.WriteAllBytes(hardwareCreditPath, objExcelPackage.GetAsByteArray());
}
message = "File 'Hardware Credit' has processed succussfully.";
//processed data in database
RoboticsRepository objrep = new RoboticsRepository();
objrep.UpdateProcessedDataforHardwareCredit(lstUnprocessedData);
return(message);
}
catch (Exception ex)
{
return(ex.ToString() + " Method Name: Export_Hardware_Credit");
}
}