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; }