public string ExtractText(Stream file)
{
Guid guid = Guid.NewGuid();
String fName = guid.ToString();
//FileInfo fi = new FileInfo(fName);
string folderPath = Config.StorageFolder;
//string saveAsPath = @"C:\Users\Huynh Binh PC\AppData\Local\Packages\2ca0072b-e230-42c2-a5f2-6ee47ccce84d_yekwsnrkhg0pr\LocalState\" + fi.Name;
string fileType = ".png";
string imageName = fName + fileType;
string imagePath = folderPath + imageName;
string txtResultPath = imagePath + ".txt";
MultipartParser parser = new MultipartParser(file);
Image image;
int minSize = 40;
int maxSize = 2600;
if (parser.Success)
{
//SaveFile(parser.Filename, parser.ContentType, parser.FileContents);
MemoryStream ms = new MemoryStream(parser.FileContents);
image = Image.FromStream(ms);
if (image.Height < minSize || image.Height > maxSize || image.Width < minSize || image.Width > maxSize)
{
return "Image size must be between 40 - 2600 pixel";
}
try
{
image.Save(imagePath);
}
catch (Exception ex)
{
return "Get image failed. Try another image PNG 40 - 2600 px";
}
}
else
{
return "Rest service failed. Try another image PNG 40 - 2600 px";
}
try
{
OcrCommandLineCaller.StartOcr(imageName);
}
catch (Exception ex)
{
return "Exception";
}
if (File.Exists(txtResultPath))
{
File.Delete(imagePath);
string result = File.ReadAllText(txtResultPath);
File.Delete(txtResultPath);
return result;
}
else
{
return "Failed to extract text";
}
}
public Response UploadRoutes(Stream fileStream, string username)
{
Response theResponse = new Response();
string cdcName = "";
string currentPath = HttpContext.Current.Server.MapPath(".");
long currentTime = DateTime.Now.ToFileTimeUtc();
string fileName = "routes_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
MultipartParser parser = new MultipartParser(fileStream);
if (parser.Success)
{
fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
fileToUpload.Close();
fileToUpload.Dispose();
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to parse input data";
return theResponse;
}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False\"";
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Route> routes = new List<Route>();
int rowCounter = 0;
while (oleReader.Read())
{
rowCounter += 1;
if (oleReader[0].ToString().Equals("Transaction Type") || oleReader[1].ToString().Equals("CDC"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (cdcName == "")
cdcName = oleReader[1].ToString();
if (oleReader[0].ToString().ToUpper().Equals("ADD"))
{
recordsFound++;
if (!doesCDCExist(oleReader[1].ToString()))
{
feedback.Add("CDC " + oleReader[1].ToString() + " does not exist");
continue;
}
if (doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " already exists");
continue;
}
Route thisRoute = new Route();
thisRoute.cdc = new CDC();
thisRoute.cdc.name = oleReader[1].ToString();
thisRoute.cdc.id = getCDCIDForCDCName(oleReader[1].ToString());
thisRoute.routeName = oleReader[2].ToString();
int numberOfStopsForThisRoute = oleReader.FieldCount;
List<Store> stops = new List<Store>();
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
stops.Add(thisStore);
}
else
{
if (!thisStoreNumber.Equals("0") && !thisStoreNumber.Equals(""))
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
if (stops.Count > 0)
{
thisRoute.stores = stops;
feedback.Add("Route " + thisRoute.routeName + " has " + thisRoute.stores.Count + " stops.");
routes.Add(thisRoute);
}
else
{
feedback.Add("The Route " + thisRoute.routeName + " could not be added as none of the stores listed against this route are present in the database");
}
}
else if (oleReader[0].ToString().ToUpper().Equals("UPDATE"))
{
recordsFound++;
if (!doesRouteExist(oleReader[2].ToString()))
{
feedback.Add("The route " + oleReader[2].ToString() + " does not exist thus cannot be updated");
continue;
}
else
{
string thisRouteName = oleReader[2].ToString();
ResponseRouteList thisRouteDetail = GetRouteDetail(thisRouteName);
bool validRoute = false;
int numberOfStops = 0;
if (thisRouteDetail != null)
{
validRoute = true;
if (thisRouteDetail.routes != null)
{
numberOfStops = thisRouteDetail.routes[0].stores.Count;
}
}
if (validRoute)
{
openDataConnection();
SqlCommand cmdDisableMappingsForRoute = new SqlCommand("DisableCurrentMappingsForRouteName", theConnection);
cmdDisableMappingsForRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdDisableMappingsForRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numMappingsDisabled = cmdDisableMappingsForRoute.ExecuteNonQuery();
closeDataConnection();
if (numMappingsDisabled >= numberOfStops)
{
List<Store> newStops = new List<Store>();
int numberOfStopsForThisRoute = oleReader.FieldCount;
for (int i = 3; i < numberOfStopsForThisRoute; i++)
{
Store thisStore = new Store();
string thisStoreNumber = oleReader[i].ToString();
if (thisStoreNumber != null && !thisStoreNumber.Equals(""))
{
thisStore.storeNumber = thisStoreNumber;
thisStore.storeID = getStoreIDForStoreNumber(thisStoreNumber);
if (thisStore.storeID > 0)
{
newStops.Add(thisStore);
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " was not found in the database");
}
}
}
openDataConnection();
foreach (Store aStore in newStops)
{
SqlCommand cmdAddStoreToRoute = new SqlCommand("AddStoreToRoute", theConnection);
cmdAddStoreToRoute.Parameters.AddWithValue("@routeName", thisRouteName);
cmdAddStoreToRoute.Parameters.AddWithValue("@storeID", aStore.storeID);
cmdAddStoreToRoute.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedForAddStoreToRoute = cmdAddStoreToRoute.ExecuteNonQuery();
feedback.Add("Added Store " + aStore.storeNumber + " to Route " + thisRouteName);
}
feedback.Add("Updated Route " + thisRouteName);
closeDataConnection();
recordsUpdated++;
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " had " + numberOfStops + " mappings but only " + numMappingsDisabled + " were disabled and this route cannot be updated");
continue;
}
}
else
{
feedback.Add("The route " + oleReader[2].ToString() + " does not seem to be valid and thus cannot be updated");
continue;
}
}
}
}
for (int i = 0, l = routes.Count; i < l; i++)
{
Response createResponse = CreateRoute(routes[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " Line Number: " + _exception.StackTrace;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " route records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Routes", username, emailString, cdcName);
}
return theResponse;
}
public MyResponse UploadStores(Stream fileStream, string username)
{
MyResponse theResponse = new MyResponse();
// MYRESPONSE theResponses = new MYRESPONSE();
string currentPath = HttpContext.Current.Server.MapPath(".");
long currentTime = DateTime.Now.ToFileTimeUtc();
string fileName = "stores_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
MultipartParser parser = new MultipartParser(fileStream);
if (parser.Success)
{
fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
fileToUpload.Close();
fileToUpload.Dispose();
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to parse input data";
return theResponse;
}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;ReadOnly=False\"";
List<string> feedback = new List<string>();
int currentRowPointer = 1;
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Store> stores = new List<Store>();
while (oleReader.Read())
{
currentRowPointer++;
if (oleReader[0].ToString().Equals("Store #"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
string thisStoreNumber = oleReader[0].ToString();
if (doesStoreExist(thisStoreNumber))
{
Store thisStoreForUpdate = new Store();
thisStoreForUpdate.storeID = getStoreIDForStoreNumber(thisStoreNumber);
thisStoreForUpdate.storeNumber = oleReader[0].ToString();
thisStoreForUpdate.storeName = oleReader[1].ToString();
thisStoreForUpdate.storeAddress = oleReader[2].ToString();
thisStoreForUpdate.storeCity = oleReader[3].ToString();
thisStoreForUpdate.storeZip = oleReader[4].ToString();
thisStoreForUpdate.storeState = oleReader[5].ToString();
thisStoreForUpdate.storePhone = oleReader[6].ToString();
thisStoreForUpdate.storeManagerName = oleReader[7].ToString();
thisStoreForUpdate.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStoreForUpdate.storeOwnershipType = oleReader[9].ToString();
}
Response updateStoreResponse = UpdateStore(thisStoreForUpdate);
if (updateStoreResponse.statusCode == 0)
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record was updated.");
recordsUpdated++;
}
else
{
feedback.Add("The Store Number " + thisStoreNumber + " already exists in the database. The record could not be updated.");
}
recordsFound++;
continue;
}
Store thisStore = new Store();
thisStore.storeNumber = oleReader[0].ToString();
thisStore.storeName = oleReader[1].ToString();
thisStore.storeAddress = oleReader[2].ToString();
thisStore.storeCity = oleReader[3].ToString();
thisStore.storeZip = oleReader[4].ToString();
thisStore.storeState = oleReader[5].ToString();
thisStore.storePhone = oleReader[6].ToString();
thisStore.storeManagerName = oleReader[7].ToString();
thisStore.storeEmailAddress = oleReader[8].ToString();
if (oleReader.FieldCount > 9)
{
thisStore.storeOwnershipType = oleReader[9].ToString();
}
stores.Add(thisStore);
recordsFound++;
}
for (int i = 0, l = stores.Count; i < l; i++)
{
Response createResponse = CreateStore(stores[i]);
if (createResponse.statusCode == 0)
{
recordsAdded++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message + " - " + _exception.StackTrace + " - Last Row Pointer was at " + currentRowPointer;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " store records in the file. Added " + recordsAdded + " records to the database.Updated " + recordsUpdated + " records in the database.";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "";
theResponse.statusDescription += "Feedback:";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += feedback[i];
emailString += feedback[i];
}
//theResponse.statusDescription;
//emailString ;
SendEmailForUploadErrors("Stores", username, emailString, "");
}
// theResponse = new Response();
// theResponse.statusCode = 0;
theResponse.statusDescription = "Response added successfully";
theResponse.statusCode = 0;
WebOperationContext.Current.OutgoingResponse.ContentType = "text/html";
return theResponse;
}
public Response UploadOps(Stream fileStream, string username)
{
Response theResponse = new Response();
string currentPath = HttpContext.Current.Server.MapPath(".");
long currentTime = DateTime.Now.ToFileTimeUtc();
string fileName = "ops_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
MultipartParser parser = new MultipartParser(fileStream);
if (parser.Success)
{
fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
fileToUpload.Close();
fileToUpload.Dispose();
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Unable to parse input data";
return theResponse;
}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;ReadOnly=False\"";
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
List<Op> opsToBeUpdated = new List<Op>();
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Op> ops = new List<Op>();
while (oleReader.Read())
{
if (oleReader.FieldCount < 18)
{
continue;
}
if (oleReader[0].ToString().Equals("Transaction Type"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (oleReader[0].ToString().ToUpper().Equals("ADD") || oleReader[0].ToString().ToUpper().Equals("UPDATE"))
{
recordsFound++;
string thisStoreNumber = oleReader[17].ToString();
if (!doesStoreExist(thisStoreNumber))
{
feedback.Add("The Store Number " + thisStoreNumber + " does not exist in the database");
continue;
}
int thisStoreID = getStoreIDForStoreNumber(thisStoreNumber);
Op thisOp = new Op();
thisOp.storeID = thisStoreID;
thisOp.division = oleReader[1].ToString();
thisOp.divisionName = oleReader[2].ToString();
thisOp.dvpOutlookname = oleReader[3].ToString();
thisOp.dvpEmailAddress = oleReader[4].ToString();
thisOp.region = oleReader[5].ToString();
thisOp.regionName = oleReader[6].ToString();
thisOp.rvpOutlookName = oleReader[7].ToString();
thisOp.rvpEmailAddress = oleReader[8].ToString();
thisOp.area = oleReader[9].ToString();
thisOp.areaName = oleReader[10].ToString();
thisOp.rdOutlookName = oleReader[11].ToString();
thisOp.rdEmailAddress = oleReader[12].ToString();
thisOp.district = oleReader[13].ToString();
thisOp.districtName = oleReader[14].ToString();
thisOp.dmOutlookName = oleReader[15].ToString();
thisOp.dmEmailAddress = oleReader[16].ToString();
if (oleReader[0].ToString().ToUpper().Equals("ADD") && !doesStoreExistInOps(thisOp.storeID.ToString()))
{
ops.Add(thisOp);
}
else if (oleReader[0].ToString().Trim().ToUpper().Equals("UPDATE"))
{
opsToBeUpdated.Add(thisOp);
}
}
}
for (int i = 0, l = ops.Count; i < l; i++)
{
Response opCreationResponse = AddOp(ops[i]);
if (opCreationResponse.statusCode == 0)
{
recordsAdded++;
}
}
for (int i = 0; i < opsToBeUpdated.Count; i++)
{
Response opUpdateResponse = UpdateOp(opsToBeUpdated[i]);
if (opUpdateResponse.statusCode == 0)
{
recordsUpdated++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " Op hierarchy records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Ops Hierarchy", username, emailString, "");
}
return theResponse;
}