/// <summary>
/// Imports directly into a DbSet. ExistingItem should be overridden if records can
/// be updated, otherwise records are added.
/// </summary>
/// <param name="table"></param>
public void Import(DbSet <T> table)
{
var newRecs = new List <T>();
var usedFields = ImportFields.Where(f => f.ImportTarget != null && !f.ImportTarget.IsKeyField && f.ImportTarget.Prop != null).ToList();
var keyField = ImportFields.FirstOrDefault(f => f.ImportTarget != null && f.ImportTarget.IsKeyField && f.ImportTarget.Prop != null);
int rowNbr = 1;
foreach (var row in _importFile.GetRecords())
{
T rec = new T();
T exist = null;
if (keyField != null)
{
keyField.Import(rec, row, rowNbr);
exist = ExistingItem(table, rec);
if (exist != null)
{
rec = exist;
}
}
foreach (var f in usedFields)
{
f.Import(rec, row, rowNbr);
}
if (exist == null && Include(rec))
{
newRecs.Add(rec);
}
rowNbr++;
}
table.AddRange(newRecs);
}
public static string GetImportField(RestCommand command, int importFieldID)
{
ImportField importField = ImportFields.GetImportField(command.LoginUser, importFieldID);
if (importField.OrganizationID != command.Organization.OrganizationID)
{
throw new RestException(HttpStatusCode.Unauthorized);
}
return(importField.GetXml("ImportField", true));
}
public static string GetImportFields(RestCommand command)
{
ImportFields importFields = new ImportFields(command.LoginUser);
importFields.LoadByOrganizationID(command.Organization.OrganizationID);
if (command.Format == RestFormat.XML)
{
return(importFields.GetXml("ImportFields", "ImportField", true, command.Filters));
}
else
{
throw new RestException(HttpStatusCode.BadRequest, "Invalid data format");
}
}
protected void btnZipCode_Click(object sender, EventArgs e)
{
string[] stringSeparators = new string[] { "\r\n" };
//Read highchart city
string content1 = File.ReadAllText(@"D:\Working\EC\usacity\cityCSV.csv");
// Parse content
string[] lines1 = content1.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
ImportFields data;
List<ImportFields> listData1 = new List<ImportFields>();
int numRecord1 = lines1.Length;
for (int i = 1; i < numRecord1; i++)
{
string[] values = ImportExportUltility.GetCsvRecord(lines1[i]);
data = new ImportFields();
data.State = values[0].Trim();
data.CityName = values[1].Trim();
data.CityKey = values[2].Trim();
listData1.Add(data);
}
//Read zicode file
string content2 = File.ReadAllText(@"D:\Working\EC\usacity\zip_code_database.csv");
string[] lines2 = content2.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
List<ImportFields> listData2 = new List<ImportFields>();
int numRecord2 = lines2.Length;
string sqlLines = "";
string csvLines = "hc-key,zip,type,primary_city,acceptable_cities,unacceptable_cities,state,county,timezone,area_codes,latitude,longitude,world_region,country,decommissioned,estimated_population,notes";
for (int i = 1; i < numRecord2; i++)
{
string[] values = ImportExportUltility.GetCsvRecord(lines2[i]);
data = new ImportFields();
data.ZipCode = values[0].Trim();
data.Type= values[1].Trim();
data.PrimatyCity= values[2].Trim();
data.State = values[5].Trim();
data.County = values[6].Trim();
data.TimeZone = values[7].Trim();
//Tim hc-key
var hightchartdata = (from pi in listData1
where pi.CityName == data.PrimatyCity
select pi).FirstOrDefault();
if (hightchartdata != null)
data.CityKey = hightchartdata.CityKey;
else
{
hightchartdata = (from pi in listData1
where data.County.Contains(pi.CityName)
select pi).FirstOrDefault();
if (hightchartdata != null)
data.CityKey = hightchartdata.CityKey;
else
{
hightchartdata = (from pi in listData1
where data.TimeZone.Contains(pi.CityName)
select pi).FirstOrDefault();
if (hightchartdata != null)
data.CityKey = hightchartdata.CityKey;
}
}
if (!String.IsNullOrEmpty(data.CityKey))
{
sqlLines += "INSERT INTO `zip_code` (`zip_code`, `area_id`) " +
"SELECT '" + data.ZipCode + "', id FROM area where geo_code='" + data.CityKey + "' ;\r\n";
}
csvLines += data.CityKey + "," + values[0] + "," + values[1] + "," + values[2] + "," + values[3] + "," + values[4] + "," + values[5] + "," + values[6] + "," + values[7] + "," + values[8] + "," + values[9] + "," + values[10] + "," + values[11] + "," + values[12] + "," + values[13] + "," + values[14] + "\r\n";
listData2.Add(data);
}
// Write the string to a file.
System.IO.StreamWriter fileSql = new System.IO.StreamWriter(@"D:\Working\EC\usacity\zipcode.sql");
fileSql.WriteLine(sqlLines);
fileSql.Close();
System.IO.StreamWriter fileCSV = new System.IO.StreamWriter(@"D:\Working\EC\usacity\megerzipcodeCSV.csv");
fileCSV.WriteLine(csvLines);
fileCSV.Close();
}
/// <summary>
/// обновление зависимых контролов
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void controlUpdate_Event(object sender, EventArgs e)
{
if (sender == null)
{
return;
}
//установка изменившегося параметра
string name = (sender as Control).Name;
switch (name)
{
case "textBoxDelimeter":
importer.Delimeter = importer.FileFormat == FileFormats.CSV ? textBoxDelimeter.Text : ";";
break;
case "numericUpDownStartLine":
importer.StartLine = (int)numericUpDownStartLine.Value;
break;
case "comboBoxEncoding":
importer.Encoding = Encoding.GetEncoding(comboBoxEncoding.SelectedItem as string);
break;
case "textBoxTrimmers":
importer.Trimmers = textBoxTrimmers.Text.ToCharArray();
break;
case "numericUpDownColDate":
case "numericUpDownColSpeed":
case "numericUpDownColTemper":
case "numericUpDownColWet":
case "numericUpDownColDirect":
case "numericUpDownColPress":
var contr = sender as NumericUpDown;
ImportFields fld = (ImportFields)contr.Tag;
if (contr.Enabled)
{
if (importer.Columns.ContainsKey(fld))
{
importer.Columns[fld] = (int)contr.Value;
}
else
{
importer.Columns.Add(fld, (int)contr.Value);
}
}
else
{
_ = importer.Columns.Remove(fld);
}
break;
case "comboBoxDirectUnit":
importer.DirectionUnit = (DirectionUnits)(new EnumTypeConverter <DirectionUnits>().ConvertFrom(comboBoxDirectUnit.SelectedItem));
break;
case "comboBoxPressUnit":
importer.PressureUnit = (PressureUnits)(new EnumTypeConverter <PressureUnits>().ConvertFrom(comboBoxPressUnit.SelectedItem));
break;
case "comboBoxWetUnit":
importer.WetnessUnit = (WetnessUnits)(new EnumTypeConverter <WetnessUnits>().ConvertFrom(comboBoxWetUnit.SelectedItem));
break;
case "checkBoxFindNearestMS":
importer.BindNearestMS = checkBoxFindNearestMS.Checked;
break;
default: throw new Exception("Этот контрол не реализован");
}
updateUI(); //пересобираем окно
}
//2.Convert dữ liệu thật từ hơn 600 bản ghi của hệ thống cũ.
protected void btnImportOld_Click(object sender, EventArgs e)
{
// try
// {
if (!FileUpload1.HasFile || !FileUpload2.HasFile)
{
return;
}
//Upload file to server
string uploadFolder = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
string sDirPath = Request.PhysicalApplicationPath + uploadFolder;
string path1 = sDirPath + "\\" + FileUpload1.FileName;
FileUpload1.SaveAs(path1);
string path2 = sDirPath + "\\" + FileUpload2.FileName;
FileUpload2.SaveAs(path2);
string[] lines1 = File.ReadAllLines(path1);
// Parse content
string content2 = File.ReadAllText(path2);
string[] stringSeparators = new string[] { "\r\n" };
string[] lines2 = content2.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
// string[] lines2 = File.ReadAllLines(path2);
File.Delete(path1);
File.Delete(path2);
ImportFields data;
List<ImportFields> listData1 = new List<ImportFields>();
int numRecord = lines1.Length;
if (numRecord > 662)
numRecord = 662;
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines1[i]);
if (values.Length > 12)
{
data.FirstName = values[0].Trim();
data.PollinatorSize = Int32.Parse(values[2].Trim());
data.PollinatorType = Int32.Parse(values[3].Trim());
data.LandscapeCity = values[5].Trim();
data.LandscapeCountry = values[6].Trim();
if (data.LandscapeCountry == "US")
data.LandscapeCountry = "USA";
else if (data.LandscapeCountry == "BR")
data.LandscapeCountry = "BRA";
data.PhotoUrl = values[9].Trim();
data.Latitude = double.Parse(values[10].Trim());
data.Longitude = double.Parse(values[11].Trim());
data.Premium = 0;
listData1.Add(data);
}
}
var listData1D = listData1.GroupBy(d => new
{
d.FirstName,
d.PollinatorSize,
d.PollinatorType,
d.LandscapeCity,
d.PhotoUrl,
// d.Latitude,
// d.Longitude,
d.Premium,
}).Select(group => group.First()).ToList();
List<ImportFields> listData2 = new List<ImportFields>();
numRecord = lines2.Length;
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines2[i]);
//{
// if (values.Length > 8 && !string.IsNullOrEmpty(values[1]))
data.FirstName = values[0].Trim();
data.OrganizationName = values[1].Trim();
data.Email = values[2].Trim();
data.LandscapeCity = values[3].Trim();
data.LandscapeState = values[4].Trim();
data.LandscapeCountry = values[5].Trim();
if (data.LandscapeCountry == "Brazil")
data.LandscapeCountry = "BRA";
else if (data.LandscapeCountry == "Canada")
data.LandscapeCountry = "CA";
else if (data.LandscapeCountry == "France")
data.LandscapeCountry = "FR";
data.LandscapeZipcode = values[6].Trim();
data.LandscapeStreet = values[7].Trim();
if (values[9].Contains("Small planter"))
data.PollinatorSize = 1;
else if (values[9].Contains("Small garden"))
data.PollinatorSize = 2;
else if (values[9].Contains("Large garden"))
data.PollinatorSize = 3;
else if (values[9].Contains("Small Yard"))
data.PollinatorSize = 4;
else if (values[9].Contains("Medium Yard"))
data.PollinatorSize = 5;
else if (values[9].Contains("Large Yard"))
data.PollinatorSize = 6;
else if (values[9].Contains("Large Filed"))
data.PollinatorSize = 8;
else if (values[9].Contains("Filed"))
data.PollinatorSize = 7;
else
data.PollinatorSize = 9;
// data.PhotoUrl = values[10];
data.PollinatorType = 0;//temp set because no data
listData2.Add(data);
// }
}
var listData2D = listData2.GroupBy(d => new
{
d.FirstName,
d.OrganizationName,
d.Email,
d.LandscapeCity,
d.LandscapeState,
d.LandscapeZipcode,
d.LandscapeCountry,
d.PollinatorSize
}).Select(group => group.First()).ToList();
var mergelist = (from pi1 in listData2D
join pi2 in listData1D
on new { pi1.FirstName, pi1.LandscapeCity, pi1.LandscapeCountry } equals new { pi2.FirstName, pi2.LandscapeCity, pi2.LandscapeCountry }
into ords
from pi2 in ords.DefaultIfEmpty()
// from pi2 in listData2
// where pi1.FirstName == pi2.FirstName
select new ImportFields
{
FirstName = pi1.FirstName,
Email = pi1.Email,
OrganizationName = pi1.OrganizationName,
PhotoUrl = pi1.PhotoUrl,
LandscapeStreet = pi1.LandscapeStreet,
LandscapeCity = pi1.LandscapeCity,
LandscapeState = pi1.LandscapeState,
LandscapeCountry = pi1.LandscapeCountry,
LandscapeZipcode = pi1.LandscapeZipcode,
PollinatorSize = pi1.PollinatorSize,
PollinatorType = pi2 != null ? pi2.PollinatorType : pi1.PollinatorType,
Premium = pi1.Premium,
Latitude = pi2 != null ? pi2.Latitude : null,
Longitude = pi2 != null ? pi2.Longitude : null,
}
).ToList();
/* var mergelistD = mergelist.GroupBy(d => new
{
d.FirstName,
d.OrganizationName,
d.LandscapeCity,
d.LandscapeState,
d.LandscapeZipcode,
d.PollinatorSize,
d.PollinatorType,
// d.PhotoUrl,
d.Premium,
d.Latitude,
d.Longitude,
})
.Select(group => group.First()).ToList();*/
WriteCsvFile(listData1D, sDirPath, @"\file1.csv");
WriteCsvFile(listData2D, sDirPath, @"\file2.csv");
WriteCsvFile(mergelist, sDirPath, @"\mergefile.csv");
// WriteCsvFile(mergelistD, sDirPath, @"\mergefileD.csv");
if (IS_GET_LOCATION)
{
mergelist = GetLocationForOldData(mergelist);
WriteCsvFile(mergelist, sDirPath, @"\mergefile2.csv");
}
WriteDB(mergelist, "OldSystem");
GoToAlertMessage(panelSuccessMessage);
/* }
catch (Exception ex)
{
Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_ImportData).Name + ".btnImportOld_Click(). Exception:", ex);
GoToAlertMessage(panelErrorMessage);
}*/
}
private List<ImportFields> ParseDemoInfo(string filePath, string sDirPath)
{
string content = File.ReadAllText(filePath);
File.Delete(filePath);
// Parse content
string[] stringSeparators = new string[] { "\r\n" };
string[] lines = content.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
int numRecord = lines.Length;
List<ImportFields> listData = new List<ImportFields>();
List<ImportFields> notConvertList = new List<ImportFields>();
ImportFields data;
IGeoCoder geoCoder = new GoogleGeoCoder();
string nameList = "Tom, Marry, Adinath, Ajitesh, Akshaj, Akshobhya, Ameyatma, Badri, Badrinath, Bhudhav, Chakradev, Chhatrabhuj, Eashan, Eha, Eka, Ekana, Evyavan, Harinarayan, Hemang, Ijay, Indivar, Ish, Jaipal, Jaithra, Kamalakar, Kamalkant, Kamalnath, Lakshmidhar, Lakshmigopal, Lakshmiraman, Liladhar, Lohitaksh, Lohitaksha, Loknath, Lokranjan, Madhuban, Mahakram, Mahatru, Namish, Narahari, Nityanta, Padmanabha, Padmapati, Padmesh, Parmesh, Phanindranath, Pramodan, Rakshan, Ramakaant, Ramashray, Ranganath, Ratannabha, Ratnabhu, Ratnanidhi, Sadabindu, Sadru, Sahishnu, Samarendra, Samarendu, Samarjit, Samavart, Samendu, Saprathas, Satanand, Satkartar, Satveer,Tommy, Denny, Herry, Nate, Yathavan, David, Aadinath, Aaditeya, Zacharry, Aamod, Zuhayr";
string[] firstnames = nameList.Split(new char[] { '\n', ',' });
string orgNameList = "Coca, Pollinator, Friendly Farmers, Microsoft, Hobbyist, Beekeeper, Gardener";
string[] orgNames = orgNameList.Split(new char[] { '\n', ',' });
int numFirstName = firstnames.Length;
string physicalLocation;
Random random = new Random();
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines[i]);
if (values.Length > 2)
{
physicalLocation = values[1].Replace("See map: Google Maps ", "");
string[] sAddress = physicalLocation.Split(new char[] { '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
int len = sAddress.Length;
int startDataIndex = 0;
while (string.IsNullOrWhiteSpace(sAddress[startDataIndex]) && startDataIndex < len)
{
startDataIndex++;
}
if (startDataIndex<len)
data.LandscapeStreet = sAddress[startDataIndex].Trim();
if (startDataIndex+1 < len)
data.LandscapeZipcode = sAddress[startDataIndex + 1].Trim();
if (startDataIndex +2< len)
data.LandscapeCity = sAddress[startDataIndex + 2].Trim();
data.LandscapeState = values[2].Trim();
data.Premium = 1;
data.PollinatorSize = random.Next(1, 8);
data.PollinatorType = random.Next(1, 9);
data.FirstName = firstnames[random.Next(1, numFirstName - 1)];
data.OrganizationName = orgNames[random.Next(1, 8)];
data.PhotoUrl = "";//UploadFiles/458586204/Burts Bees logo.png";
if (IS_GET_LOCATION)
{
//Get location from address
physicalLocation = physicalLocation.Replace("\n", "").Trim();
geoCoder = new GoogleGeoCoder();
bool getLocation = false;
Address[] matchAddress = null;
int numTry = 0;
while (!getLocation && numTry < 5)
{
try
{
matchAddress = geoCoder.GeoCode(physicalLocation).ToArray();
getLocation = true;
numTry++;
break;
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(2000);
// Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_DataMigration).Name + "Get location from address. Exception:", ex);
}
}
if (matchAddress != null && matchAddress.Length > 0)
{
data.Latitude = matchAddress[0].Coordinates.Latitude;
data.Longitude = matchAddress[0].Coordinates.Longitude;
listData.Add(data);
}
else
{
data.LineNumber = (i + 1).ToString();
data.PhysicalLocation = physicalLocation;
notConvertList.Add(data);
}
}
else
{
if (i != 29 && i != 53)
{
listData.Add(data);
}
}
}
}
WriteCsvFile(listData, sDirPath, @"\output.csv");
if (IS_GET_LOCATION)
{
//write file to check error line (can't get location)
var csv = new StringBuilder();
string newLine = "LineNumber,PhysicalLocation,OrganizationName,PollinatorSize,PollinatorType, LandscapeCity,LandscapeState,LandscapeZipcode," + Environment.NewLine;
csv.Append(newLine);
for (int i = 0; i < notConvertList.Count; i++)
{
ImportFields writedata = notConvertList.ElementAt(i);
newLine = string.Format("{0}, \"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",{8}",
writedata.LineNumber,
writedata.PhysicalLocation,
writedata.OrganizationName,
writedata.PollinatorSize,
writedata.PollinatorType,
writedata.LandscapeCity,
writedata.LandscapeState,
writedata.LandscapeZipcode,
Environment.NewLine);
csv.Append(newLine);
}
File.WriteAllText(sDirPath + @"\output_error.csv", csv.ToString());
}
return listData;
}
protected void btnImportDemo_Click(object sender, EventArgs e)
{
if (!fileDemo.HasFile)
{
return;
}
// try
// {
List<ImportFields> listData = new List<ImportFields>();
//Upload file to server
string uploadFolder = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
string sDirPath = Request.PhysicalApplicationPath + uploadFolder;
string sfilePath = sDirPath + "\\" + fileDemo.FileName;
if (!File.Exists(sfilePath))
fileDemo.SaveAs(sfilePath);
if (!fileDemo.FileName.Contains("outputfull"))//not parse yet, pasrse data
{
listData = ParseDemoInfo(sfilePath, sDirPath);
}
else//has parse, just read parsed data
{
string[] lines = File.ReadAllLines(sfilePath);
int numRecord = lines.Length;
ImportFields data;
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines[i]);
if (values.Length > 11)
{
data.FirstName = values[0].Trim();
data.OrganizationName = values[1].Trim();
data.PollinatorSize = Int32.Parse(values[3].Trim());
data.PollinatorType = Int32.Parse(values[4].Trim());
data.LandscapeStreet = values[5].Trim();
data.LandscapeCity = values[6].Trim();
data.LandscapeState = values[7].Trim();
data.LandscapeCountry = values[8].Trim();
data.LandscapeZipcode = values[9].Trim();
data.PhotoUrl = values[10].Trim();
data.Latitude = double.Parse(values[11].Trim());
data.Longitude = double.Parse(values[12].Trim());
data.FuzyLocation = bool.Parse(values[13].Trim());
data.Premium =byte.Parse(values[14].Trim());
if (values.Length > 15)
{
// data.RowId = int.Parse(values[12]);
data.UserId = values[15];
}
listData.Add(data);
}
}
}
WriteDB(listData, "Demo");
GoToAlertMessage(panelSuccessMessage);
/* }
catch (Exception ex)
{
Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_DataMigration).Name + "btnImportDemo_Click(). Exception:", ex);
GoToAlertMessage(panelErrorMessage);
}*/
}
private List <ImportFields> ParseDemoInfo(string filePath, string sDirPath)
{
string content = File.ReadAllText(filePath);
File.Delete(filePath);
// Parse content
string[] stringSeparators = new string[] { "\r\n" };
string[] lines = content.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
int numRecord = lines.Length;
List <ImportFields> listData = new List <ImportFields>();
List <ImportFields> notConvertList = new List <ImportFields>();
ImportFields data;
IGeoCoder geoCoder = new GoogleGeoCoder();
string nameList = "Tom, Marry, Adinath, Ajitesh, Akshaj, Akshobhya, Ameyatma, Badri, Badrinath, Bhudhav, Chakradev, Chhatrabhuj, Eashan, Eha, Eka, Ekana, Evyavan, Harinarayan, Hemang, Ijay, Indivar, Ish, Jaipal, Jaithra, Kamalakar, Kamalkant, Kamalnath, Lakshmidhar, Lakshmigopal, Lakshmiraman, Liladhar, Lohitaksh, Lohitaksha, Loknath, Lokranjan, Madhuban, Mahakram, Mahatru, Namish, Narahari, Nityanta, Padmanabha, Padmapati, Padmesh, Parmesh, Phanindranath, Pramodan, Rakshan, Ramakaant, Ramashray, Ranganath, Ratannabha, Ratnabhu, Ratnanidhi, Sadabindu, Sadru, Sahishnu, Samarendra, Samarendu, Samarjit, Samavart, Samendu, Saprathas, Satanand, Satkartar, Satveer,Tommy, Denny, Herry, Nate, Yathavan, David, Aadinath, Aaditeya, Zacharry, Aamod, Zuhayr";
string[] firstnames = nameList.Split(new char[] { '\n', ',' });
string orgNameList = "Coca, Pollinator, Friendly Farmers, Microsoft, Hobbyist, Beekeeper, Gardener";
string[] orgNames = orgNameList.Split(new char[] { '\n', ',' });
int numFirstName = firstnames.Length;
string physicalLocation;
Random random = new Random();
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines[i]);
if (values.Length > 2)
{
physicalLocation = values[1].Replace("See map: Google Maps ", "");
string[] sAddress = physicalLocation.Split(new char[] { '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
int len = sAddress.Length;
int startDataIndex = 0;
while (string.IsNullOrWhiteSpace(sAddress[startDataIndex]) && startDataIndex < len)
{
startDataIndex++;
}
if (startDataIndex < len)
{
data.LandscapeStreet = sAddress[startDataIndex].Trim();
}
if (startDataIndex + 1 < len)
{
data.LandscapeZipcode = sAddress[startDataIndex + 1].Trim();
}
if (startDataIndex + 2 < len)
{
data.LandscapeCity = sAddress[startDataIndex + 2].Trim();
}
data.LandscapeState = values[2].Trim();
data.Premium = 1;
data.PollinatorSize = random.Next(1, 8);
data.PollinatorType = random.Next(1, 9);
data.FirstName = firstnames[random.Next(1, numFirstName - 1)];
data.OrganizationName = orgNames[random.Next(1, 8)];
data.PhotoUrl = "";//UploadFiles/458586204/Burts Bees logo.png";
if (IS_GET_LOCATION)
{
//Get location from address
physicalLocation = physicalLocation.Replace("\n", "").Trim();
geoCoder = new GoogleGeoCoder();
bool getLocation = false;
Address[] matchAddress = null;
int numTry = 0;
while (!getLocation && numTry < 5)
{
try
{
matchAddress = geoCoder.GeoCode(physicalLocation).ToArray();
getLocation = true;
numTry++;
break;
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(2000);
// Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_DataMigration).Name + "Get location from address. Exception:", ex);
}
}
if (matchAddress != null && matchAddress.Length > 0)
{
data.Latitude = matchAddress[0].Coordinates.Latitude;
data.Longitude = matchAddress[0].Coordinates.Longitude;
listData.Add(data);
}
else
{
data.LineNumber = (i + 1).ToString();
data.PhysicalLocation = physicalLocation;
notConvertList.Add(data);
}
}
else
{
if (i != 29 && i != 53)
{
listData.Add(data);
}
}
}
}
WriteCsvFile(listData, sDirPath, @"\output.csv");
if (IS_GET_LOCATION)
{
//write file to check error line (can't get location)
var csv = new StringBuilder();
string newLine = "LineNumber,PhysicalLocation,OrganizationName,PollinatorSize,PollinatorType, LandscapeCity,LandscapeState,LandscapeZipcode," + Environment.NewLine;
csv.Append(newLine);
for (int i = 0; i < notConvertList.Count; i++)
{
ImportFields writedata = notConvertList.ElementAt(i);
newLine = string.Format("{0}, \"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",{8}",
writedata.LineNumber,
writedata.PhysicalLocation,
writedata.OrganizationName,
writedata.PollinatorSize,
writedata.PollinatorType,
writedata.LandscapeCity,
writedata.LandscapeState,
writedata.LandscapeZipcode,
Environment.NewLine);
csv.Append(newLine);
}
File.WriteAllText(sDirPath + @"\output_error.csv", csv.ToString());
}
return(listData);
}
//2.Convert dữ liệu thật từ hơn 600 bản ghi của hệ thống cũ.
protected void btnImportOld_Click(object sender, EventArgs e)
{
// try
// {
if (!FileUpload1.HasFile || !FileUpload2.HasFile)
{
return;
}
//Upload file to server
string uploadFolder = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
string sDirPath = Request.PhysicalApplicationPath + uploadFolder;
string path1 = sDirPath + "\\" + FileUpload1.FileName;
FileUpload1.SaveAs(path1);
string path2 = sDirPath + "\\" + FileUpload2.FileName;
FileUpload2.SaveAs(path2);
string[] lines1 = File.ReadAllLines(path1);
// Parse content
string content2 = File.ReadAllText(path2);
string[] stringSeparators = new string[] { "\r\n" };
string[] lines2 = content2.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
// string[] lines2 = File.ReadAllLines(path2);
File.Delete(path1);
File.Delete(path2);
ImportFields data;
List <ImportFields> listData1 = new List <ImportFields>();
int numRecord = lines1.Length;
if (numRecord > 662)
{
numRecord = 662;
}
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines1[i]);
if (values.Length > 12)
{
data.FirstName = values[0].Trim();
data.PollinatorSize = Int32.Parse(values[2].Trim());
data.PollinatorType = Int32.Parse(values[3].Trim());
data.LandscapeCity = values[5].Trim();
data.LandscapeCountry = values[6].Trim();
if (data.LandscapeCountry == "US")
{
data.LandscapeCountry = "USA";
}
else if (data.LandscapeCountry == "BR")
{
data.LandscapeCountry = "BRA";
}
data.PhotoUrl = values[9].Trim();
data.Latitude = double.Parse(values[10].Trim());
data.Longitude = double.Parse(values[11].Trim());
data.Premium = 0;
listData1.Add(data);
}
}
var listData1D = listData1.GroupBy(d => new
{
d.FirstName,
d.PollinatorSize,
d.PollinatorType,
d.LandscapeCity,
d.PhotoUrl,
// d.Latitude,
// d.Longitude,
d.Premium,
}).Select(group => group.First()).ToList();
List <ImportFields> listData2 = new List <ImportFields>();
numRecord = lines2.Length;
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines2[i]);
//{
// if (values.Length > 8 && !string.IsNullOrEmpty(values[1]))
data.FirstName = values[0].Trim();
data.OrganizationName = values[1].Trim();
data.Email = values[2].Trim();
data.LandscapeCity = values[3].Trim();
data.LandscapeState = values[4].Trim();
data.LandscapeCountry = values[5].Trim();
if (data.LandscapeCountry == "Brazil")
{
data.LandscapeCountry = "BRA";
}
else if (data.LandscapeCountry == "Canada")
{
data.LandscapeCountry = "CA";
}
else if (data.LandscapeCountry == "France")
{
data.LandscapeCountry = "FR";
}
data.LandscapeZipcode = values[6].Trim();
data.LandscapeStreet = values[7].Trim();
if (values[9].Contains("Small planter"))
{
data.PollinatorSize = 1;
}
else if (values[9].Contains("Small garden"))
{
data.PollinatorSize = 2;
}
else if (values[9].Contains("Large garden"))
{
data.PollinatorSize = 3;
}
else if (values[9].Contains("Small Yard"))
{
data.PollinatorSize = 4;
}
else if (values[9].Contains("Medium Yard"))
{
data.PollinatorSize = 5;
}
else if (values[9].Contains("Large Yard"))
{
data.PollinatorSize = 6;
}
else if (values[9].Contains("Large Filed"))
{
data.PollinatorSize = 8;
}
else if (values[9].Contains("Filed"))
{
data.PollinatorSize = 7;
}
else
{
data.PollinatorSize = 9;
}
// data.PhotoUrl = values[10];
data.PollinatorType = 0; //temp set because no data
listData2.Add(data);
// }
}
var listData2D = listData2.GroupBy(d => new
{
d.FirstName,
d.OrganizationName,
d.Email,
d.LandscapeCity,
d.LandscapeState,
d.LandscapeZipcode,
d.LandscapeCountry,
d.PollinatorSize
}).Select(group => group.First()).ToList();
var mergelist = (from pi1 in listData2D
join pi2 in listData1D
on new { pi1.FirstName, pi1.LandscapeCity, pi1.LandscapeCountry } equals new { pi2.FirstName, pi2.LandscapeCity, pi2.LandscapeCountry }
into ords
from pi2 in ords.DefaultIfEmpty()
// from pi2 in listData2
// where pi1.FirstName == pi2.FirstName
select new ImportFields
{
FirstName = pi1.FirstName,
Email = pi1.Email,
OrganizationName = pi1.OrganizationName,
PhotoUrl = pi1.PhotoUrl,
LandscapeStreet = pi1.LandscapeStreet,
LandscapeCity = pi1.LandscapeCity,
LandscapeState = pi1.LandscapeState,
LandscapeCountry = pi1.LandscapeCountry,
LandscapeZipcode = pi1.LandscapeZipcode,
PollinatorSize = pi1.PollinatorSize,
PollinatorType = pi2 != null ? pi2.PollinatorType : pi1.PollinatorType,
Premium = pi1.Premium,
Latitude = pi2 != null ? pi2.Latitude : null,
Longitude = pi2 != null ? pi2.Longitude : null,
}
).ToList();
/* var mergelistD = mergelist.GroupBy(d => new
* {
* d.FirstName,
* d.OrganizationName,
* d.LandscapeCity,
* d.LandscapeState,
* d.LandscapeZipcode,
* d.PollinatorSize,
* d.PollinatorType,
* // d.PhotoUrl,
* d.Premium,
* d.Latitude,
* d.Longitude,
* })
* .Select(group => group.First()).ToList();*/
WriteCsvFile(listData1D, sDirPath, @"\file1.csv");
WriteCsvFile(listData2D, sDirPath, @"\file2.csv");
WriteCsvFile(mergelist, sDirPath, @"\mergefile.csv");
// WriteCsvFile(mergelistD, sDirPath, @"\mergefileD.csv");
if (IS_GET_LOCATION)
{
mergelist = GetLocationForOldData(mergelist);
WriteCsvFile(mergelist, sDirPath, @"\mergefile2.csv");
}
WriteDB(mergelist, "OldSystem");
GoToAlertMessage(panelSuccessMessage);
/* }
* catch (Exception ex)
* {
* Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_ImportData).Name + ".btnImportOld_Click(). Exception:", ex);
* GoToAlertMessage(panelErrorMessage);
* }*/
}
protected void btnImportDemo_Click(object sender, EventArgs e)
{
if (!fileDemo.HasFile)
{
return;
}
// try
// {
List <ImportFields> listData = new List <ImportFields>();
//Upload file to server
string uploadFolder = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
string sDirPath = Request.PhysicalApplicationPath + uploadFolder;
string sfilePath = sDirPath + "\\" + fileDemo.FileName;
if (!File.Exists(sfilePath))
{
fileDemo.SaveAs(sfilePath);
}
if (!fileDemo.FileName.Contains("outputfull")) //not parse yet, pasrse data
{
listData = ParseDemoInfo(sfilePath, sDirPath);
}
else //has parse, just read parsed data
{
string[] lines = File.ReadAllLines(sfilePath);
int numRecord = lines.Length;
ImportFields data;
for (int i = 1; i < numRecord; i++)
{
data = new ImportFields();
string[] values = ImportExportUltility.GetCsvRecord(lines[i]);
if (values.Length > 11)
{
data.FirstName = values[0].Trim();
data.OrganizationName = values[1].Trim();
data.PollinatorSize = Int32.Parse(values[3].Trim());
data.PollinatorType = Int32.Parse(values[4].Trim());
data.LandscapeStreet = values[5].Trim();
data.LandscapeCity = values[6].Trim();
data.LandscapeState = values[7].Trim();
data.LandscapeCountry = values[8].Trim();
data.LandscapeZipcode = values[9].Trim();
data.PhotoUrl = values[10].Trim();
data.Latitude = double.Parse(values[11].Trim());
data.Longitude = double.Parse(values[12].Trim());
data.FuzyLocation = bool.Parse(values[13].Trim());
data.Premium = byte.Parse(values[14].Trim());
if (values.Length > 15)
{
// data.RowId = int.Parse(values[12]);
data.UserId = values[15];
}
listData.Add(data);
}
}
}
WriteDB(listData, "Demo");
GoToAlertMessage(panelSuccessMessage);
/* }
* catch (Exception ex)
* {
* Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_DataMigration).Name + "btnImportDemo_Click(). Exception:", ex);
* GoToAlertMessage(panelErrorMessage);
* }*/
}
