public static void ExportFailedDataExcel(HttpResponse response, string fileName, List <OrginalDataField> listData)
{
string strColumnNames = "Line\tError Description\tID #\tFirst Name\tLast Name\tOrganization Name\tWebsite\tPhone Number\tEmail Address\tType of Pollinator\tOrganization Description\tSize of Pollinator Location\tAddress\tCity\tState\tZip\tCountry\tGeolocation";
var columnNames = strColumnNames.Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
double[] columnWidths = new double[] { 15, 35, 15, 25, 20, 30, 20, 20, 28, 25, 30, 20, 20, 20, 20, 20, 20, 19.3 };
DataTable dt = ImportExportUltility.ToDataTable(listData, fileName, columnNames);
ImportExportUltility.ExportExcel(response, fileName, dt, columnWidths);
}
private static string DataToCsvString(List <ImportExportFields> listData)
{
StringBuilder builder = new StringBuilder();
string strColumnNames = "ID #,First Name,Last Name,Organization Name,Website,Phone Number,Email Address,Type of Pollinator,Organization Description,Size of Pollinator Location,Address,City,State,Zip,Country,Geolocation";
//write header
builder.Append(strColumnNames);
builder.Append(Environment.NewLine);
int numRecord = listData.Count;
string geolocation;
//write the data
for (int i = 0; i < numRecord; i++)
{
var rowData = listData[i];
ImportExportUltility.WriteCSVData(builder, rowData.UserId.ToString());
ImportExportUltility.WriteCSVData(builder, rowData.FirstName);
ImportExportUltility.WriteCSVData(builder, rowData.LastName);
ImportExportUltility.WriteCSVData(builder, rowData.OrganizationName);
ImportExportUltility.WriteCSVData(builder, rowData.Website);
ImportExportUltility.WriteCSVData(builder, rowData.PhoneNumber);
ImportExportUltility.WriteCSVData(builder, rowData.Email);
ImportExportUltility.WriteCSVData(builder, rowData.PollinatorTypeName);
ImportExportUltility.WriteCSVData(builder, rowData.OrganizationDescription);
ImportExportUltility.WriteCSVData(builder, rowData.PollinatorSizeName);
ImportExportUltility.WriteCSVData(builder, rowData.LandscapeStreet);
ImportExportUltility.WriteCSVData(builder, rowData.LandscapeCity);
ImportExportUltility.WriteCSVData(builder, rowData.LandscapeState);
ImportExportUltility.WriteCSVData(builder, rowData.LandscapeZipcode);
ImportExportUltility.WriteCSVData(builder, rowData.LandscapeCountryName);
geolocation = string.Empty;
if (rowData.Latitude.HasValue && rowData.Longitude.HasValue)
{
geolocation = rowData.Latitude.ToString() + "; " + rowData.Longitude.ToString();
}
ImportExportUltility.WriteCSVData(builder, geolocation, true);
}
return(builder.ToString());
}
public static List <OrginalDataField> ReadCsv(string filePath, out int numFieldOfCurrentFile)
{
numFieldOfCurrentFile = 0;
string content = File.ReadAllText(filePath);// Convert content to list string
Regex csvSplitLine = new Regex("\r\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
string[] lines = csvSplitLine.Split(content);
List <OrginalDataField> listData = new List <OrginalDataField>();
//store data to list
OrginalDataField orginalDataField;
int rowNo = 0;
foreach (var line in lines)
{
//not get line 1(header line)
if (rowNo == 0)
{
numFieldOfCurrentFile = ImportExportUltility.GetCsvRecord(line).Length;//get num of column
if (numFieldOfCurrentFile != numFieldOfImportFile)
{
break;
}
rowNo++;
continue;
}
//not add line is empty
if (string.IsNullOrEmpty(line))
{
rowNo++;
continue;
}
//add data records
orginalDataField = new OrginalDataField();
orginalDataField.LineNumber = rowNo + 1;
string[] valueOfFields = ImportExportUltility.GetCsvRecord(line.ToString());
//not add line is empty
if (string.IsNullOrEmpty(valueOfFields[0]) && string.IsNullOrEmpty(valueOfFields[1]) && string.IsNullOrEmpty(valueOfFields[2]) &&
string.IsNullOrEmpty(valueOfFields[3]) && string.IsNullOrEmpty(valueOfFields[4]) && string.IsNullOrEmpty(valueOfFields[5]) &&
string.IsNullOrEmpty(valueOfFields[6]) && string.IsNullOrEmpty(valueOfFields[7]) && string.IsNullOrEmpty(valueOfFields[8]) &&
string.IsNullOrEmpty(valueOfFields[9]) && string.IsNullOrEmpty(valueOfFields[10]) && string.IsNullOrEmpty(valueOfFields[11]) &&
string.IsNullOrEmpty(valueOfFields[12]) && string.IsNullOrEmpty(valueOfFields[13]) && string.IsNullOrEmpty(valueOfFields[14]) &&
string.IsNullOrEmpty(valueOfFields[15]))
{
rowNo++;
continue;
}
orginalDataField.ID = valueOfFields[0].ToString().Trim();
orginalDataField.FirstName = valueOfFields[1].ToString().Trim();
orginalDataField.LastName = valueOfFields[2].ToString().Trim();
orginalDataField.OrganizationName = valueOfFields[3].ToString().Trim();
orginalDataField.Website = valueOfFields[4].ToString().Trim();
orginalDataField.PhoneNumber = valueOfFields[5].ToString().Trim();
orginalDataField.Email = valueOfFields[6].ToString().Trim();
orginalDataField.PollinatorType = valueOfFields[7].ToString().Trim();
orginalDataField.OrganizationDescription = valueOfFields[8].ToString().Trim();
orginalDataField.PollinatorSize = valueOfFields[9].ToString().Trim();
orginalDataField.Address = valueOfFields[10].ToString().Trim();
orginalDataField.City = valueOfFields[11].ToString().Trim();
orginalDataField.State = valueOfFields[12].ToString().Trim();
orginalDataField.ZIP = valueOfFields[13].ToString().Trim();
orginalDataField.Country = valueOfFields[14].ToString().Trim();
orginalDataField.Geolocation = valueOfFields[15].ToString().Trim();
//add to list
listData.Add(orginalDataField);
//asc 1 unit
rowNo++;
}
return(listData);
}
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
byte? membeshipLevel = null;
string filename = "AllUsers";
if (drMemberShip.SelectedValue == "0")//Share User
{
membeshipLevel = 0;
filename = "ShareUsers";
}
else if (drMemberShip.SelectedValue == "1")//BFF User
{
membeshipLevel = 1;
filename = "BffUsers";
}
//Query data
PollinatorEntities mydb = new PollinatorEntities();
var listData = (from ud in mydb.UserDetails.Where(ud => !membeshipLevel.HasValue || ud.MembershipLevel == membeshipLevel || (ud.MembershipLevel > membeshipLevel && membeshipLevel == 1))
join user in mydb.Memberships
on ud.UserId equals user.UserId
into group1
from ui in group1.DefaultIfEmpty()
join pi in mydb.PolinatorInformations
on ud.UserId equals pi.UserId
join py in mydb.PollinatorTypes
on pi.PollinatorType equals py.ID
into group2
from uit in group2.DefaultIfEmpty()
join ps in mydb.PollinatorSizes
on pi.PollinatorSize equals ps.ID
join c in mydb.Countries
on pi.LandscapeCountry equals c.ID
into group3
from ex in group3.DefaultIfEmpty()
orderby pi.IsApproved, pi.LastUpdated descending
select new ImportExportFields
{
UserId = ud.UserId,
FirstName = ud.FirstName,
LastName = ud.LastName,
OrganizationName = pi.OrganizationName != null ? pi.OrganizationName : string.Empty,
Website = pi.Website != null ? pi.Website : string.Empty,
PhoneNumber = ud.PhoneNumber != null ? ud.PhoneNumber : string.Empty,
Email = (ui != null && ui.Email != null) ? ui.Email : string.Empty,
PollinatorTypeName = (uit != null && uit.Name != null) ? uit.Name : (ud.MembershipLevel == 0) ? string.Empty : "Bee Friendly Farmers",
OrganizationDescription = pi.Description != null ? pi.Description : string.Empty,
PollinatorSizeName = ps.Name,
LandscapeStreet = pi.LandscapeStreet != null ? pi.LandscapeStreet : string.Empty,
LandscapeCity = pi.LandscapeCity != null ? pi.LandscapeCity : string.Empty,
LandscapeState = pi.LandscapeState != null ? pi.LandscapeState : string.Empty,
LandscapeZipcode = pi.LandscapeZipcode != null ? pi.LandscapeZipcode : string.Empty,
LandscapeCountryName = (ex != null && ex.Name != null) ? ex.Name : string.Empty,
Latitude = pi.Latitude,
Longitude = pi.Longitude,
}).ToList();
this.EnableViewState = false;
if (drExportFormat.SelectedValue == "Excel")
{
filename = filename + System.DateTime.Now.ToString("_MMddyyyy_HHmm") + ".xlsx";
ImportExportUltility.ExportExcel(Response, filename, listData);
}
else
{
filename = filename + System.DateTime.Now.ToString("_MMddyyyy_HHmm") + ".csv";
ImportExportUltility.ExportCSV(Response, filename, listData);
}
}
catch (Exception ex)
{
Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_ExportData).Name + "btnExport_Click(). Exception:", ex);
}
}
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);
* }*/
}
