public async Task <IActionResult> Import(IFormFile file)
{
int TOTAL_INSERT = 0;
var uploads = System.IO.Path.Combine(_environment.WebRootPath, "temp");
file = Request.Form.Files[0];
using (var fileStream = new FileStream(System.IO.Path.Combine(uploads, file.FileName),
FileMode.OpenOrCreate,
FileAccess.ReadWrite,
FileShare.ReadWrite))
{
await file.CopyToAsync(fileStream);
try
{
using (var Stream = new FileStream(System.IO.Path.Combine(uploads, file.FileName),
FileMode.OpenOrCreate,
FileAccess.ReadWrite,
FileShare.ReadWrite))
{
using (ExcelPackage package = new ExcelPackage(Stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
for (int row = 2; row <= rowCount; row++)
{
if (worksheet.Cells[row, 1].Value != null &&
worksheet.Cells[row, 2].Value != null &&
worksheet.Cells[row, 3].Value != null &&
worksheet.Cells[row, 4].Value != null &&
worksheet.Cells[row, 5].Value != null &&
worksheet.Cells[row, 6].Value != null &&
worksheet.Cells[row, 7].Value != null)
{
var Type = worksheet.Cells[row, 1].Value.ToString();
var ServicePakcCategory = worksheet.Cells[row, 2].Value.ToString();
var SSOW = worksheet.Cells[row, 3].Value.ToString();
var OnCallNormal = worksheet.Cells[row, 4].Value != null ? worksheet.Cells[row, 4].Value.ToString() : "0";
var OnShiftNormal = worksheet.Cells[row, 5].Value != null ? worksheet.Cells[row, 5].Value.ToString() : "0";
var OnCallHoliday = worksheet.Cells[row, 6].Value != null ? worksheet.Cells[row, 6].Value.ToString() : "0";
var OnShiftHoliday = worksheet.Cells[row, 7].Value != null ? worksheet.Cells[row, 7].Value.ToString() : "0";
var Remark = worksheet.Cells[row, 8].Value.ToString();
if (Type != null)
{
if (_excel.TruncateString(Type).Equals(_excel.TruncateString("A")) ||
_excel.TruncateString(Type).Equals(_excel.TruncateString("B")) ||
_excel.TruncateString(Type).Equals(_excel.TruncateString("FSO")))
{
ServicePackCategory Category = ServicePackCategory
.GetAll()
.FirstOrDefault(x => _excel.TruncateString(x.Name) == _excel.TruncateString(ServicePakcCategory));
ServicePack SerPack = servicePack.GetAll()
.Where(x => _excel.TruncateString(x.Name) == _excel.TruncateString(SSOW) && x.ServicePackCategory == Category)
.FirstOrDefault();
if (SerPack != null)
{
AllowanceList inAllowanceList = new AllowanceList
{
ServicePack = SerPack,
OnCallNormal = decimal.Parse(OnCallNormal),
OnCallHoliday = decimal.Parse(OnCallHoliday),
ShiftNormal = decimal.Parse(OnShiftNormal),
ShiftHoliday = decimal.Parse(OnShiftHoliday),
OtherInfo = Remark
};
Service.Add(inAllowanceList);
TOTAL_INSERT++;
}
}
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
TempData["Messages"] = "Total Inserted " + TOTAL_INSERT;
System.IO.File.Delete(System.IO.Path.Combine(uploads, file.FileName));
return(RedirectToAction("Index"));
}
public async Task <IActionResult> Import(IFormFile file)
{
int TOTAL_INSERT = 0;
var uploads = System.IO.Path.Combine(_environment.WebRootPath, "temp");
file = Request.Form.Files[0];
using (var fileStream = new FileStream(System.IO.Path.Combine(uploads, file.FileName),
FileMode.OpenOrCreate,
FileAccess.ReadWrite,
FileShare.ReadWrite))
{
await file.CopyToAsync(fileStream);
try
{
using (var Stream = new FileStream(System.IO.Path.Combine(uploads, file.FileName),
FileMode.OpenOrCreate,
FileAccess.ReadWrite,
FileShare.ReadWrite))
{
using (ExcelPackage package = new ExcelPackage(Stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
for (int row = 2; row <= rowCount; row++)
{
if (worksheet.Cells[row, 1].Value != null &&
worksheet.Cells[row, 2].Value != null &&
worksheet.Cells[row, 3].Value != null &&
worksheet.Cells[row, 4].Value != null &&
worksheet.Cells[row, 5].Value != null &&
worksheet.Cells[row, 6].Value != null &&
worksheet.Cells[row, 7].Value != null &&
worksheet.Cells[row, 8].Value != null &&
worksheet.Cells[row, 9].Value != null &&
worksheet.Cells[row, 10].Value != null &&
worksheet.Cells[row, 11].Value != null)
{
var Type = worksheet.Cells[row, 1].Value;
var Package = worksheet.Cells[row, 2].Value;
var SSOName = worksheet.Cells[row, 3].Value;
var ServiceCode = worksheet.Cells[row, 4].Value;
var MontlyRate = worksheet.Cells[row, 5].Value;
var HourlyRate = worksheet.Cells[row, 6].Value;
var SumTwo = worksheet.Cells[row, 7].Value;
var SumThree = worksheet.Cells[row, 8].Value;
var SumFour = worksheet.Cells[row, 9].Value;
var LaptopAllowance = worksheet.Cells[row, 10].Value;
var USIM = worksheet.Cells[row, 11].Value;
ServicePackCategory Category = servicePackCategoryService
.GetAll()
.FirstOrDefault(x => _excel.TruncateString(x.Name) == _excel.TruncateString(Package.ToString()));
if (Category == null)
{
ServicePackCategory NewCategory = new ServicePackCategory {
Name = Package.ToString()
};
Category = servicePackCategoryService.Add(NewCategory);
}
//ServicePack FindSSO = Service
// .GetAll()
// .FirstOrDefault(x =>
// x.Type == (PackageTypes)Enum.Parse(typeof(PackageTypes), Type.ToString()) &&
// x.ServicePackCategory == Category &&
// _excel.TruncateString(x.Name) == _excel.TruncateString(SSOName.ToString()) &&
// _excel.TruncateString(x.Code) == _excel.TruncateString(ServiceCode.ToString()) &&
// _excel.TruncateString(x.Rate.ToString()) == _excel.TruncateString(MontlyRate.ToString()) &&
// _excel.TruncateString(x.Hourly.ToString()) == _excel.TruncateString(HourlyRate.ToString()) &&
// _excel.TruncateString(x.Otp20.ToString()) == _excel.TruncateString(SumTwo.ToString()) &&
// _excel.TruncateString(x.Otp30.ToString()) == _excel.TruncateString(SumThree.ToString()) &&
// _excel.TruncateString(x.Otp40.ToString()) == _excel.TruncateString(SumFour.ToString()) &&
// _excel.TruncateString(x.Laptop.ToString()) == _excel.TruncateString(LaptopAllowance.ToString()) &&
// _excel.TruncateString(x.Usin.ToString()) == _excel.TruncateString(USIM.ToString()));
//if (FindSSO == null)
//{
// ServicePack Sp = new ServicePack
// {
// Type = (PackageTypes)Enum.Parse(typeof(PackageTypes), Type.ToString()),
// ServicePackCategory = Category,
// Name = SSOName.ToString(),
// Code = ServiceCode.ToString(),
// Rate = decimal.Parse(MontlyRate.ToString()),
// Hourly = decimal.Parse(HourlyRate.ToString()),
// Otp20 = decimal.Parse(SumTwo.ToString()),
// Otp30 = decimal.Parse(SumThree.ToString()),
// Otp40 = decimal.Parse(SumFour.ToString()),
// Laptop = decimal.Parse(LaptopAllowance.ToString()),
// Usin = decimal.Parse(USIM.ToString()),
// };
// Service.Add(Sp);
// TOTAL_INSERT++;
//}
ServicePack Sp = new ServicePack
{
Type = (PackageTypes)Enum.Parse(typeof(PackageTypes), Type.ToString()),
ServicePackCategory = Category,
Name = SSOName.ToString(),
Code = ServiceCode.ToString(),
Rate = decimal.Parse(MontlyRate.ToString()),
Hourly = decimal.Parse(HourlyRate.ToString()),
Otp20 = decimal.Parse(SumTwo.ToString()),
Otp30 = decimal.Parse(SumThree.ToString()),
Otp40 = decimal.Parse(SumFour.ToString()),
Laptop = decimal.Parse(LaptopAllowance.ToString()),
Usin = decimal.Parse(USIM.ToString()),
};
Service.Add(Sp);
TOTAL_INSERT++;
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
TempData["Messages"] = "Total Inserted = " + TOTAL_INSERT;
System.IO.File.Delete(System.IO.Path.Combine(uploads, file.FileName));
return(RedirectToAction("Index"));
}