public ResponseSOAPModel ImportScanData(ImportDataModel postData)
{
var res = new ResponseSOAPModel()
{
Status = "201",
Message = "",
};
// check role, login info.
var user = _context.TblUsers.FirstOrDefault(o => o.UserName == postData.User);
#region Validate
if (user == null)
{
res.Message = "Account with user name " + postData.User + " is not existed";
res.Status = "500";
return(res);
}
if (user.Token != postData.Token)
{
res.Message = "Token is not correct";
res.Status = "500";
return(res);
}
if (user.RoleId != Constant.Users.Admin && user.RoleId != Constant.Users.User && user.RoleId != Constant.Users.Superadmin)
{
res.Message = "You dont have permission to do.";
res.Status = "500";
return(res);
}
#endregion
try
{
Guid g;
g = Guid.NewGuid();
var x = new TblScanData
{
ScanId = g.ToString(),
Uid = user.Id,
CreatedOn = DateTime.UtcNow,
StationName = postData.StationName,
Payload = postData.ScanData.Payload,
DataType = postData.ScanData.DataType,
FileName = postData.ScanData.FileName,
Status = postData.ScanData.Status,
DeletedOn = null,
};
_context.TblScanData.Add(x);
_context.SaveChanges();
x.U = null;
res.Message = "Scan data is created";
//res.Data = x;
}
catch (Exception ex)
{
res.Status = "500";
res.Message = ex.Message;
}
return(res);
}
public ActionResult Index(ImportDataModel importModel)
{
var validImageTypes = new []
{
"application/vnd.ms-excel"
};
bool errorExcel;
if (importModel.UploadFile != null && importModel.UploadFile.ContentLength > 0)
{
errorExcel = !validImageTypes.Contains(importModel.UploadFile.ContentType);
}
else
{
errorExcel = true;
}
if (errorExcel)
{
ModelState.AddModelError("UploadFile", "Por favor seleccione un archivo de Excel");
}
var academicYear = _academicYearRepository.GetByFields(importModel.Year, importModel.GradeImport, importModel.Section);
if (academicYear == null)
{
ModelState.AddModelError("Year", "No existe ese año academico");
}
ViewBag.GradeId = new SelectList(_gradeRepository.Query(x => x), "Id", "Name", 0);
if (!ModelState.IsValid)
{
return(View(importModel));
}
var myDataSet = _importDataRepository.GetDataSetFromExcelFile(importModel.UploadFile);
try
{
_importDataRepository.Import(myDataSet, academicYear);
}
catch (Exception ex)
{
_viewMessageLogic.SetNewMessage("Error!", ex.Message, ViewMessageType.ErrorMessage);
return(RedirectToAction("Index"));
}
const string title = "Importación de Datos Correcta";
var content = string.Format("Se importaron datos para el año: {0}, grado: {1} y sección: {2}"
, importModel.Year // 0
, importModel.GradeImport // 1
, importModel.Section // 2
);
_viewMessageLogic.SetNewMessage(title, content, ViewMessageType.InformationMessage);
return(RedirectToAction("Index"));
}
public ActionResult Index()
{
_viewMessageLogic.SetViewMessageIfExist();
var importModel = new ImportDataModel();
ViewBag.GradeId = new SelectList(_gradeRepository.Query(x => x), "Id", "Name", 0);
ViewBag.Year = new SelectList(_academicYearRepository.Filter(x => x.IsActive).Select(x => x.Year).Distinct().ToList());
ViewBag.Section = new SelectList(new List <string> {
"A", "B", "C"
}, "A");
return(View(importModel));
}
public ActionResult ImportData()
{
ImportDataModel model = new ImportDataModel {
};
model.UploadFile = new FileUpload1 {
SelectButtonText = this.__ResStr("btnImport", "Import Data..."),
SaveURL = GetActionUrl("ImportPackageData", new { __ModuleGuid = Module.ModuleGuid }),
RemoveURL = GetActionUrl("RemovePackageData", new { __ModuleGuid = Module.ModuleGuid }),
};
return(View(model));
}
public IActionResult ImportScanData([FromBody] ImportDataModel tblscandata)
{
return(Ok(_IScanDataService.ImportScanData(tblscandata)));
}
public string ReadData(string filePath)
{
if (System.IO.File.Exists(filePath)) //dont use Full qualified namespaces//
{
string extension = Path.GetExtension(filePath);
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
//connExcel.Close();
conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
String TruncateQuery = "DELETE FROM PtacDealer_TB";
SqlCommand command = new SqlCommand(TruncateQuery, con);
con.Open();
command.ExecuteNonQuery();
con.Close();
//Read Data from First Sheet.
//connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var importData = new ImportDataModel();
importData.Description = row["SUBFDESC"].ToString().Trim();
var formattedData = ConvertToPascalCase(importData.Description);
importData.Address = row["SUBFADR1"].ToString().Trim();
importData.City = row["SUBFCITY"].ToString().Trim();
importData.State = row["SUBFSTATE"].ToString().Trim();
importData.ZipCode = row["SUBFZIP"].ToString().Trim();
importData.PhoneNumber = row["PhoneNum"].ToString().Trim();
var data = GetPhoneNumber(importData.PhoneNumber);
importData.PhoneNumber2 = row["PhoneNum2"].ToString().Trim();
var data1 = GetPhoneNumber(importData.PhoneNumber2);
String query = "INSERT INTO dbo.PtacDealer_TB (Description,Address,City,State,ZipCode,PhoneNumber,PhoneNumber2) VALUES ('" + formattedData.Replace("'", "''") + "','" + importData.Address.Replace("'", "''") + "','" + importData.City.Replace("'", "''") + "','" + importData.State + "','" + importData.ZipCode + "','" + data + "','" + data1 + "')";
SqlCommand command1 = new SqlCommand(query, con);
con.Open();
command1.ExecuteNonQuery();
con.Close();
//command.ExecuteNonQuery();
connExcel.Close();
}
}
}
}
}
}
return(filePath);
}
public static ImportDataModel GetImportModels(string path)
{
try
{
var model = new ImportDataModel();
var stream = File.OpenRead(path);
IExcelDataReader excelReader = null;
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null)
{
return(model);
}
var pageIndex = 0;
do
{
pageIndex++;
var items = new List <ImportModel>();
var category = (CategoryCode)pageIndex;
var rowIndex = 0;
while (excelReader.Read())
{
rowIndex++;
if (rowIndex < StartRowIndex)
{
continue;
}
if (excelReader.IsDBNull(3))
{
continue;
}
var item = new ImportModel();
item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0);
item.V2 = excelReader.GetString(1);
item.V3 = excelReader.GetString(2);
item.V4 = excelReader.GetString(3);
item.V5 = excelReader.GetString(4);
item.V6 = excelReader.GetString(5);
item.V7 = excelReader.GetString(6);
item.V8 = excelReader.GetString(7);
item.V9 = excelReader.GetString(8);
item.V10 = excelReader.GetString(9);
item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10);
item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11);
item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12);
item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13);
item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14);
item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15);
item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16);
item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17);
item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18);
item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19);
item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20);
item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21);
item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22);
item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23);
item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24);
item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25);
item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26);
item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27);
item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28);
item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29);
item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30);
item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31);
item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32);
item.V34 = excelReader.GetString(33);
item.V35 = excelReader.GetString(34);
item.V36 = excelReader.GetString(35);
item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36);
item.V38 = excelReader.GetString(37);
item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38);
item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39);
items.Add(item);
}
model.Items.Add(category, items);
model.PageRows.Add(category, rowIndex);
} while (excelReader.NextResult());
excelReader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
