public void ReadCSVFile_Test()
{
var filePath = @".\IOTest\CSVTest\SampleFiles\Kline_1h_201808_btc_usdt.csv";
var dataList = CSVHelper.ReadCSVFile(filePath);
var count = dataList.Count;
if (count > 0)
{
Assert.True(true);
}
else
{
Assert.False(true);
}
}
public (bool, List <UserFromExcel>) GetUsers(IFormFile file)
{
var csvData = CSVHelper.ReadCSVFile(file);
var rowWiseData = csvData.Split('\n');
if (!ValidateUserFile(rowWiseData))
{
return(false, null);
}
int rowNumber = 1;
var userlist = new List <UserFromExcel>();
foreach (string row in rowWiseData)
{
if (row == null)
{
break;
}
if (rowNumber == 1)
{
rowNumber++;
continue;
}
if (!string.IsNullOrEmpty(row))
{
var rowData = row.Split(',');
userlist.Add(new UserFromExcel
{
UserName = rowData[0],
FullName = rowData[1].TrimEnd('\r'),
Email = rowData[2].TrimEnd('\r'),
DepartmentName = rowData[3].TrimEnd('\r'),
RoleRightsName = rowData[4].TrimEnd('\r'),
});
}
rowNumber++;
}
return(true, userlist);
}
void SwitchExcel(string file)
{
if (string.IsNullOrEmpty(file))
{
rtbProcess.Text += Lang.LblClearData;
return;
}
//参数提取
int size = 0;
if (!int.TryParse(txtNumber.Text, out size))
{
size = 30000;
}
CSVHelper.ReadCSVFile(selectFile.SelectFileFullName, size);
//ExcelHelper.ExcelCuttingPage(selectFile.SelectFileFullName, 0, size, CallBack);
//进行excel页分割动作
}
public (bool, List <DepartmentFromExcel>) GetDepartments(IFormFile file)
{
var csvData = CSVHelper.ReadCSVFile(file);
var rowWiseData = csvData.Split('\n');
if (!ValidateFile(rowWiseData))
{
return(false, null);
}
var rowNumber = 1;
var departmentlist = new List <DepartmentFromExcel>();
foreach (string row in rowWiseData)
{
if (row == null)
{
break;
}
if (rowNumber == 1)
{
rowNumber++;
continue;
}
if (!string.IsNullOrEmpty(row))
{
var rowData = row.Split(',');
departmentlist.Add(new DepartmentFromExcel
{
DepartmentName = rowData[0],
ParentDepartmentName = rowData[1]?.TrimEnd('\r'),
});
}
rowNumber++;
}
return(true, departmentlist);
}
public async Task <ActionResult> ViewSalesData()
{
try
{
HttpPostedFileBase salesDataFile = Request.Files.Count > 0 ? Request.Files[0] : null;
IList <string> csvData = CSVHelper.ReadCSVFile(salesDataFile);
if (csvData == null)
{
ViewBag.Errors = new[] { "Unable to read CSV file." };
}
IList <VehicleDetail> vehicleDetails = CSVHelper.ConvertToModel(csvData);
ViewBag.MostSoldVehicle = vehicleDetails.GroupBy(vd => vd.Vehicle).GetHighesCountGroup();
return(View("Results", vehicleDetails));
}
catch (Exception ex)
{
Response.StatusCode = 500;
return(View("Error"));
}
}
private void processClaimData()
{
Claim claim = null;
CRM.Data.Entities.LeadPolicy policy = null;
Leads lead = null;
DataTable importTable = null;
lblMessage.Text = string.Empty;
lblMessage.CssClass = string.Empty;
// intial phase
try {
// load file for processing
string filepath = Server.MapPath("~//Temp//" + ViewState["fileName"].ToString());
// load csv file into a table
importTable = CSVHelper.ReadCSVFile(filepath);
// get collection of mapped fields
mappedFields = getMappedFields();
}
catch (Exception ex) {
lblMessage.Text = "Initial phase of failed.";
lblMessage.CssClass = "error";
Core.EmailHelper.emailError(ex);
return;
}
// second phase - process each record
using (TransactionScope scope = new TransactionScope()) {
try {
foreach (DataRow dataRow in importTable.Rows)
{
lead = createLead(dataRow);
if (lead == null)
{
continue;
}
policy = createPolicy(lead.LeadId, dataRow);
claim = createClaim(policy.Id, dataRow);
}
scope.Complete();
lblMessage.Text = "Import completed successfully.";
lblMessage.CssClass = "ok";
}
catch (Exception ex) {
lblMessage.Text = "Import not completed successfully.";
lblMessage.CssClass = "error";
Core.EmailHelper.emailError(ex);
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string fileExtension = null;
AdjusterMaster adjuster = null;
string firstName = null;
string lastName = null;
AdjusterServiceArea serviceArea = null;
AdjusterLicenseAppointmentType licenseAppointmentType = null;
string licenseNumber = null;
string licenseType = null;
string licenseEffectiveDate = null;
string licenseExpireDate = null;
string appointmentType = null;
StateMaster stateMaster = null;
string str = null;
lblMessage.Text = string.Empty;
if (!fileUpload.HasFile)
{
lblMessage.Text = "No file selected.";
lblMessage.CssClass = "error";
return;
}
// validate file
fileExtension = System.IO.Path.GetExtension(fileUpload.PostedFile.FileName);
if (!fileExtension.Equals(".csv", StringComparison.OrdinalIgnoreCase))
{
lblMessage.Text = "CSV file format allowed only.";
lblMessage.CssClass = "error";
return;
}
try {
//save file in temp folder
string destinationPath = Server.MapPath(string.Format("~//Temp//{0}.csv", Guid.NewGuid()));
fileUpload.SaveAs(destinationPath);
// load file for processing
//DataTable table = CSVReader.ReadCSVFile(Server.MapPath("~//Temp//" + fileUpload.FileName), true);
DataTable table = CSVHelper.ReadCSVFile(destinationPath);
using (TransactionScope scope = new TransactionScope()) {
for (int i = 0; i < table.Rows.Count; i++)
{
#region process each row
adjuster = new AdjusterMaster();
adjuster.ClientId = clientID;
adjuster.Status = true;
adjuster.InsertBy = userID;
adjuster.InsertDate = DateTime.Now;
if (table.Rows[i]["First_Name"] != string.Empty)
{
firstName = table.Rows[i]["First_Name"].ToString();
}
if (table.Rows[i]["Last_Name"] != string.Empty)
{
lastName = table.Rows[i]["Last_Name"].ToString();
}
// skip blank rows
if (string.IsNullOrEmpty(lastName) && string.IsNullOrEmpty(firstName))
{
continue;
}
adjuster.FirstName = firstName.Length < 50 ? firstName : firstName.Substring(0, 50);
adjuster.LastName = lastName.Length < 50 ? lastName : lastName.Substring(0, 50);
if (table.Rows[i]["Middle_Initial"] != string.Empty)
{
str = table.Rows[i]["Middle_Initial"].ToString();
adjuster.MiddleInitial = str.Substring(0, 1);
}
adjuster.AdjusterName = string.Format("{0} {1} {2}", firstName, adjuster.MiddleInitial ?? "", lastName);
if (table.Rows[i]["Suffix"] != string.Empty)
{
adjuster.Suffix = table.Rows[i]["Suffix"].ToString().Substring(0, 1);
}
if (table.Rows[i]["Cell_Phone_Number"] != string.Empty)
{
str = table.Rows[i]["Cell_Phone_Number"].ToString();
if (str.Length > 20)
{
adjuster.PhoneNumber = str.Substring(0, 20);
}
else
{
adjuster.PhoneNumber = str;
}
}
if (table.Rows[i]["Email"] != string.Empty)
{
str = table.Rows[i]["Email"].ToString();
adjuster.email = str.Length < 100 ? str : str.Substring(0, 100);
}
if (table.Rows[i]["Address_Line1"] != string.Empty)
{
str = table.Rows[i]["Address_Line1"].ToString();
adjuster.Address1 = str.Length < 100 ? str : str.Substring(0, 100);
}
if (table.Rows[i]["City"] != string.Empty)
{
str = table.Rows[i]["City"].ToString();
adjuster.Address1 = str.Length < 50 ? str : str.Substring(0, 50);
}
if (table.Rows[i]["State_Code"] != string.Empty)
{
str = table.Rows[i]["State_Code"].ToString();
stateMaster = State.Getstateid(str);
if (stateMaster.StateId > 0)
{
adjuster.StateID = stateMaster.StateId;
}
else
{
adjuster.StateID = null;
}
}
if (table.Rows[i]["ZIP_Code"] != string.Empty)
{
str = table.Rows[i]["ZIP_Code"].ToString();
adjuster.Address1 = str.Length < 10 ? str : str.Substring(0, 10);
}
// save adjuster
adjuster = AdjusterManager.Save(adjuster);
if (table.Rows[i]["License_Type"] != string.Empty)
{
licenseType = table.Rows[i]["License_Type"].ToString();
}
if (table.Rows[i]["License_Number"] != string.Empty)
{
licenseNumber = table.Rows[i]["License_Number"].ToString();
}
if (table.Rows[i]["License_Issue_Date"] != string.Empty)
{
licenseEffectiveDate = table.Rows[i]["License_Issue_Date"].ToString();
}
if (table.Rows[i]["License_Expires_Date"] != string.Empty)
{
licenseExpireDate = table.Rows[i]["License_Expires_Date"].ToString();
}
if (table.Rows[i]["Appointment_Type"] != string.Empty)
{
appointmentType = table.Rows[i]["Appointment_Type"].ToString();
if (!string.IsNullOrEmpty(appointmentType))
{
licenseAppointmentType = AdjusterLicenseAppointmentTypeManager.Get(appointmentType, clientID);
if (licenseAppointmentType == null)
{
licenseAppointmentType = new AdjusterLicenseAppointmentType();
licenseAppointmentType.IsActive = true;
licenseAppointmentType.LicenseAppointmentType = appointmentType;
licenseAppointmentType.ClientID = clientID;
licenseAppointmentType = AdjusterLicenseAppointmentTypeManager.Save(licenseAppointmentType);
}
}
}
if (!string.IsNullOrEmpty(licenseType) && !string.IsNullOrEmpty(licenseNumber) && !string.IsNullOrEmpty(licenseEffectiveDate) &&
!string.IsNullOrEmpty(licenseExpireDate) && !string.IsNullOrEmpty(appointmentType))
{
serviceArea = new AdjusterServiceArea();
serviceArea.AdjusterID = adjuster.AdjusterId;
serviceArea.StateID = adjuster.StateID;
serviceArea.LicenseNumber = licenseNumber.Length < 50 ? licenseNumber : licenseNumber.Substring(0, 50);
serviceArea.LicenseType = licenseType.Length < 20 ? licenseType : licenseType.Substring(0, 20);
DateTime effectiveDate = DateTime.MaxValue;
DateTime expireDate = DateTime.MaxValue;
if (DateTime.TryParse(licenseEffectiveDate, out effectiveDate))
{
serviceArea.LicenseEffectiveDate = effectiveDate;
}
if (DateTime.TryParse(licenseExpireDate, out expireDate))
{
serviceArea.LicenseExpirationDate = expireDate;
}
if (licenseAppointmentType != null)
{
serviceArea.AppointmentTypeID = licenseAppointmentType.LicenseAppointmentTypeID;
}
AdjusterStateLicenseManager.Save(serviceArea);
}
// create notes
if (table.Rows[i]["Adjuster_Status_Code"] != string.Empty)
{
string adjuster_Status_Code = table.Rows[i]["Adjuster_Status_Code"].ToString();
addNote(adjuster.AdjusterId, adjuster_Status_Code);
}
if (table.Rows[i]["Adjuster_Reason_Code"] != string.Empty)
{
string sdjuster_Reason_Code = table.Rows[i]["Adjuster_Reason_Code"].ToString();
addNote(adjuster.AdjusterId, sdjuster_Reason_Code);
}
#endregion
}
scope.Complete();
lblMessage.Text = "Import completed successfully.";
lblMessage.CssClass = "ok";
}
}
catch (Exception ex) {
lblMessage.Text = "Import not completed successfully.";
lblMessage.CssClass = "error";
Core.EmailHelper.emailError(ex);
}
}
public IEnumerable <IAddress> GetAddresses()
{
string[] addressCSV = CSVHelper.ReadCSVFile(_appSettings.AddressCSVFilePath);
return(CSVHelper.MapAddressCSVToObject(addressCSV));
}
public IEnumerable <IBaseProperty> GetBaseProperties()
{
string[] propertyCSV = CSVHelper.ReadCSVFile(_appSettings.PropertyCSVFilePath);
return(CSVHelper.MapPropertyCSVToObject(propertyCSV).ToList());
}