public static DataSet loadTxtToDataset(string path, string name) { // Setup the DataSet DataSet ds = new DataSet(); // Use Generic Txt Parser to load csv to dataset using (GenericParserAdapter parser = new GenericParserAdapter()) { parser.SetDataSource(path); parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = true; parser.TextQualifier = '\"'; ds = parser.GetDataSet(); } ds.Tables[0].TableName = name; return ds; }
private void btnRun_Click(object sender, EventArgs e) { //Column header references for Alpha extract file, 0-indexed. Update as needed. int alphaDBIRDCol = 0; int alphaDBFirstNameCol = 1; int alphaDBSurnameCol = 2; int alphaDBMobileCol = 6; int alphaDBEmailCol = 7; int alphaDBStatusCol = 8; int alphaDBAddress1Col = 10; int alphaDBAddress2Col = 11; int alphaDBSuburbCol = 12; int alphaDBCityCol = 13; int alphaDBPostcodeCol = 14; //Column header references for MYOB extract file, 0-indexed. Update as needed. int myobIRDCol = 0; int myobNameCol = 1; int myobClientIDCol = 2; //Column header references for Client List file, 0-indexed. These are the Mail Merge field names. Update as needed string clientListIRD = "IRDNum"; string clientListFirstName = "FirstName"; string clientListSurname = "Surname"; string clientListEmail = "Email"; string clientListCellphone = "Cellphone"; string clientListAddress1 = "Address1"; string clientListAddress2 = "Address2"; string clientListSuburb = "Suburb"; string clientListCity = "City"; string clientListPostcode = "Postcode"; //Record our sending priorities int letterPriority = cmbLetterPriorities.SelectedIndex + 1; int emailPriority = cmbEmailPriorities.SelectedIndex + 1; int cellPriority = cmbTxtPriorities.SelectedIndex + 1; if (!(letterPriority == 4 && emailPriority == 4 && cellPriority == 4)) { //Stash all Alpha extract info in a DataTable, and clean the IRD numbers. Don't bother cleaning anything else till later - we'll only clean if we want that client's info GenericParserAdapter alphaDBParser = new GenericParserAdapter(); alphaDBParser.SetDataSource(tbAlphaDB.Text); alphaDBParser.FirstRowHasHeader = false; alphaDBParser.ColumnDelimiter = ','; System.Data.DataTable alphaDBTable = alphaDBParser.GetDataTable(); //This column gets a name because we need to do SELECT statements using this later alphaDBTable.Columns[alphaDBIRDCol].ColumnName = clientListIRD; //Stash all MYOB (NZAccountant) extract info in a DataTable GenericParserAdapter myobDBParser = new GenericParserAdapter(); System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceNames(); myobDBParser.SetDataSource((new DirectoryInfo(Assembly.GetExecutingAssembly().Location).Parent.FullName) + "\\Resources\\20120309_myob_client_dump.csv"); myobDBParser.FirstRowHasHeader = true; myobDBParser.ColumnDelimiter = ','; System.Data.DataTable myobDBTable = myobDBParser.GetDataTable(); //This column gets a name because we need to do SELECT statements using this later myobDBTable.Columns[myobIRDCol].ColumnName = clientListIRD; foreach (DataRow currRow in alphaDBTable.Rows) { string irdNumString = Regex.Replace((string)currRow[alphaDBIRDCol], "\\D", ""); //TODO: This can cause exceptions. Really should handle this int extractedIRDNum = 0; bool extractedIRDBool = Int32.TryParse(irdNumString, out extractedIRDNum); currRow[alphaDBIRDCol] = extractedIRDBool ? extractedIRDNum.ToString() : irdNumString; } //Put all the supplied ird numbers for clients we're distributing to into a DataTable GenericParserAdapter clientListParser = new GenericParserAdapter(); clientListParser.SetDataSource(tbClientNums.Text); clientListParser.FirstRowHasHeader = false; clientListParser.ColumnDelimiter = ','; clientListParser.ExpectedColumnCount = 1; System.Data.DataTable clientListTable = clientListParser.GetDataTable(); clientListTable.Columns[0].ColumnName = clientListIRD; clientListTable.Columns.Add(clientListFirstName); clientListTable.Columns.Add(clientListSurname); clientListTable.Columns.Add(clientListEmail); clientListTable.Columns.Add(clientListCellphone); clientListTable.Columns.Add(clientListAddress1); clientListTable.Columns.Add(clientListAddress2); clientListTable.Columns.Add(clientListSuburb); clientListTable.Columns.Add(clientListCity); clientListTable.Columns.Add(clientListPostcode); //We're going to need a DataTable for reporting errors too System.Data.DataTable errorTable = new System.Data.DataTable(); errorTable.Columns.Add("IRD Number"); errorTable.Columns.Add("Error Description"); //For each client we're distributing to, retrieve (and clean) their information from the alphaDbTable Regex emailRegex = new Regex(@"^(([^<>()[\]\\.,;:\s@\""]+" + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@" + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+" + @"[a-zA-Z]{2,}))$"); //http://geekswithblogs.net/VROD/archive/2007/03/16/109007.aspx System.Data.DataTable postListTable = clientListTable.Clone(); System.Data.DataTable emailListTable = clientListTable.Clone(); System.Data.DataTable cellListTable = clientListTable.Clone(); foreach (DataRow currClient in clientListTable.Rows) { //Check if it's an MYOB client. If so, this is an error if (myobDBTable.Select(clientListIRD + " = \'" + ((string)currClient[clientListIRD]) + "\'").Length == 0) { DataRow[] alphaClientDetailsArray; alphaClientDetailsArray = alphaDBTable.Select(clientListIRD + " = \'" + ((string)currClient[clientListIRD]) + "\'"); //"IRDNum = [search term]" DataColumnCollection test = alphaDBTable.Columns; //If we successfully retrieved the client's details, clean and record them. If not, mark this as an error client if (alphaClientDetailsArray.Length > 0) { //Ignore multiple results, just take the first match. DataRow alphaClientDetails = alphaClientDetailsArray[0]; //If successfully retrieved data, check if Address Line 1 is a dud. If so, mark this as an error client currClient[clientListFirstName] = EncapsulateSpeech(ToTitleCase((string)alphaClientDetails[alphaDBFirstNameCol])); currClient[clientListSurname] = EncapsulateSpeech(ToTitleCase((string)alphaClientDetails[alphaDBSurnameCol])); currClient[clientListEmail] = emailRegex.IsMatch((string)alphaClientDetails[alphaDBEmailCol]) ? EncapsulateSpeech((string)alphaClientDetails[alphaDBEmailCol]) : ""; string tempCellphone = EncapsulateSpeech((string)alphaClientDetails[alphaDBMobileCol]); currClient[clientListCellphone] = cleanCellphone(ref tempCellphone) ? EncapsulateSpeech((string)tempCellphone) : ""; currClient[clientListAddress1] = EncapsulateSpeech(ToTitleCase((string)alphaClientDetails[alphaDBAddress1Col])); currClient[clientListAddress2] = EncapsulateSpeech(ToTitleCase((string)alphaClientDetails[alphaDBAddress2Col])); //If the city is blank (after removing whitespace), but there's a postcode, then put the postcode on the end of the suburb string tempCity = Regex.Replace((string)alphaClientDetails[alphaDBCityCol], @"\s+", ""); string tempPostCode = (string)alphaClientDetails[alphaDBPostcodeCol]; try { tempPostCode = Int32.Parse(tempPostCode) == 0 ? tempPostCode = "" : ((tempPostCode).Length < 4 && (tempPostCode).Length > 0 ? (tempPostCode).PadLeft(4, '0') : tempPostCode); //TRAP! If inside an if :) } catch (Exception postCodeFailed) //catch all :) { tempPostCode = ""; } string tempSuburb = (string)alphaClientDetails[alphaDBSuburbCol]; if (tempCity == "" && tempPostCode.Length > 0) { tempSuburb = tempSuburb + " " + tempPostCode; tempCity = ""; tempPostCode = ""; } currClient[clientListSuburb] = EncapsulateSpeech(ToTitleCase(tempSuburb)); currClient[clientListCity] = EncapsulateSpeech(ToTitleCase(tempCity)); currClient[clientListPostcode] = EncapsulateSpeech(tempPostCode); //Decide whether this is a mail client, an email client, or a cell client - then put into appropo DataTable // //TODO: This whole section is hideous and needs rewriting bool firstLevelFailure = false; bool secondLevelFailure = false; bool thirdLevelFailure = false; string tempTestAddress1 = (string)currClient[clientListAddress1]; string tempTestEmail = (string)currClient[clientListEmail]; string tempTestCellphone = (string)currClient[clientListCellphone]; switch (letterPriority) { case 1: firstLevelFailure = ((tempTestAddress1 == "" || tempTestAddress1 == "\"\"" || tempTestAddress1 == "0") && firstLevelFailure != true); break; case 2: secondLevelFailure = ((tempTestAddress1 == "" || tempTestAddress1 == "\"\"" || tempTestAddress1 == "0") && secondLevelFailure != true); break; case 3: thirdLevelFailure = ((tempTestAddress1 == "" || tempTestAddress1 == "\"\"" || tempTestAddress1 == "0") && thirdLevelFailure != true); break; default: break; } switch (emailPriority) { case 1: firstLevelFailure = ((tempTestEmail == "" || tempTestEmail == "\"\"" || tempTestEmail == "0") && firstLevelFailure != true); break; case 2: secondLevelFailure = ((tempTestEmail == "" || tempTestEmail == "\"\"" || tempTestEmail == "0") && secondLevelFailure != true); break; case 3: thirdLevelFailure = ((tempTestEmail == "" || tempTestEmail == "\"\"" || tempTestEmail == "0") && thirdLevelFailure != true); break; default: break; } switch (cellPriority) { case 1: firstLevelFailure = ((tempTestCellphone == "" || tempTestCellphone == "\"\"" || tempTestCellphone == "0") && firstLevelFailure != true); break; case 2: secondLevelFailure = ((tempTestCellphone == "" || tempTestCellphone == "\"\"" || tempTestCellphone == "0") && secondLevelFailure != true); break; case 3: thirdLevelFailure = ((tempTestCellphone == "" || tempTestCellphone == "\"\"" || tempTestCellphone == "0") && thirdLevelFailure != true); break; default: break; } bool actuallySentSomething = false; if (firstLevelFailure == false) { if (letterPriority == 1) { postListTable.ImportRow(currClient); actuallySentSomething = true; } if(emailPriority == 1) { emailListTable.ImportRow(currClient); actuallySentSomething = true; } if (cellPriority == 1) { cellListTable.ImportRow(currClient); actuallySentSomething = true; } } else if (secondLevelFailure == false) { if (letterPriority == 2) { postListTable.ImportRow(currClient); actuallySentSomething = true; } if (emailPriority == 2) { emailListTable.ImportRow(currClient); actuallySentSomething = true; } if (cellPriority == 2) { cellListTable.ImportRow(currClient); actuallySentSomething = true; } } else if (thirdLevelFailure == false) { if (letterPriority == 3) { postListTable.ImportRow(currClient); actuallySentSomething = true; } if (emailPriority == 3) { emailListTable.ImportRow(currClient); actuallySentSomething = true; } if (cellPriority == 3) { cellListTable.ImportRow(currClient); actuallySentSomething = true; } } else //Whelp, we've completely failed. { DataRow newErrorRow = errorTable.NewRow(); newErrorRow["IRD Number"] = currClient[clientListIRD]; newErrorRow["Error Description"] = "No valid contact details that match with what you want to send"; errorTable.Rows.Add(newErrorRow); } //Just in case we didn't find anything we could send to, but didn't have a failure at each level. Not sure this actually reachable, but being safe :) if (!actuallySentSomething) { DataRow newErrorRow = errorTable.NewRow(); newErrorRow["IRD Number"] = currClient[clientListIRD]; newErrorRow["Error Description"] = "No valid contact details that match with what you want to send. You should contact the client to update details or try and send your message by a different method."; errorTable.Rows.Add(newErrorRow); } } else { DataRow newErrorRow = errorTable.NewRow(); newErrorRow["IRD Number"] = currClient[clientListIRD]; newErrorRow["Error Description"] = "Could not find the client in the database. Please check you entered this correctly. If you did then check with NZAccountant and PBA. Tell developer/manager/team leader if not a client of either of them."; errorTable.Rows.Add(newErrorRow); } } else { DataRow newErrorRow = errorTable.NewRow(); newErrorRow["IRD Number"] = currClient[clientListIRD]; newErrorRow["Error Description"] = "This appears to be an NZAccountant Client; check with them. Tell developer/manager/team leader if not a client of NZA."; errorTable.Rows.Add(newErrorRow); } } //Create a temp file for the Error data, dump it out, and open it in Excel String errorFileLocation = System.IO.Path.GetTempFileName() + ".csv"; FileStream errorOutputStream = File.Create(errorFileLocation); if (errorTable.Rows.Count > 0) { //write headers int errorColumnCount = errorTable.Columns.Count; for (int i = 0; i < errorColumnCount; i++) { AddText(errorOutputStream, errorTable.Columns[i].ColumnName); if (i != errorColumnCount - 1) AddText(errorOutputStream, ","); } AddText(errorOutputStream, Environment.NewLine); //write data foreach (DataRow currClient in errorTable.Rows) { for (int i = 0; i < errorColumnCount; i++) { AddText(errorOutputStream, (string)currClient[i]); if (i != errorColumnCount - 1) AddText(errorOutputStream, ","); } AddText(errorOutputStream, Environment.NewLine); } errorOutputStream.Close(); } //Create a temp file for the Alpha import data, dump it out, and open it in Excel String alphaFileLocation = System.IO.Path.GetTempFileName() + ".csv"; FileStream alphaOutputStream = File.Create(alphaFileLocation); //write data foreach (DataRow currClient in postListTable.Rows) { AddText(alphaOutputStream, (string)currClient[clientListIRD] + ","); AddText(alphaOutputStream, EncapsulateSpeech(tbAlphaNotes.Text + ". Sent by Post")); AddText(alphaOutputStream, Environment.NewLine); } foreach (DataRow currClient in emailListTable.Rows) { AddText(alphaOutputStream, (string)currClient[clientListIRD] + ","); AddText(alphaOutputStream, EncapsulateSpeech(tbAlphaNotes.Text + ". Sent by Email")); AddText(alphaOutputStream, Environment.NewLine); } foreach (DataRow currClient in cellListTable.Rows) { AddText(alphaOutputStream, (string)currClient[clientListIRD] + ","); AddText(alphaOutputStream, EncapsulateSpeech(tbAlphaNotes.Text + ". Sent by Text Message")); AddText(alphaOutputStream, Environment.NewLine); } alphaOutputStream.Close(); //Make the error file visible Excel._Application oExcel = new Excel.Application(); oExcel.Visible = true; oExcel.ScreenUpdating = true; if (errorTable.Rows.Count > 0) { oExcel.Workbooks.Open(errorFileLocation, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //Make the notes file visible oExcel.Workbooks.Open(alphaFileLocation, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Send emails. //COMMENTED OUT BEHAVIOUR: If no emails, let the user know if (emailListTable.Rows.Count > 0) { int columnCount = emailListTable.Columns.Count; StreamReader myEmailStream = new System.IO.StreamReader(@tbEmailTemplate.Text); string myEmailString = myEmailStream.ReadToEnd(); myEmailStream.Close(); foreach (DataRow currClient in emailListTable.Rows) { string tempEmailString = myEmailString; tempEmailString = tempEmailString.Replace("[[[[FIRST_NAME]]]]", RemoveSpeech((string)currClient[clientListFirstName])); tempEmailString = tempEmailString.Replace("[[[[IRD_NUMBER]]]]", RemoveSpeech((string)currClient[clientListIRD])); tempEmailString = tempEmailString.Replace(Environment.NewLine, "<br/>"); tempEmailString = HTMLEncodeSpecialChars(tempEmailString); //myEmailString = myEmailString.Replace("[[[[FULL_NAME]]]]", (string)currClient[clientListIRD]); tempEmailString = Resources.EmailContent.Replace("[[[[EMAIL_CONTENT]]]]", tempEmailString); SendEmail( RemoveSpeech((string)currClient[clientListEmail]), tempEmailString,tbSubject.Text.Replace("<FirstName>",RemoveSpeech((string)currClient[clientListFirstName])).Replace("<LastName>",RemoveSpeech((string)currClient[clientListSurname])), BodyType.HTML); } } else { //MessageBox.Show("No emails were sent"); } //Send texts. //COMMENTED OUT BEHAVIOUR: If no emails, let the user know if (cellListTable.Rows.Count > 0) { int columnCount = cellListTable.Columns.Count; StreamReader myEmailStream = new System.IO.StreamReader(tbTextTemplate.Text); string myEmailString = myEmailStream.ReadToEnd(); myEmailStream.Close(); foreach (DataRow currClient in cellListTable.Rows) { string tempEmailString = myEmailString; tempEmailString = tempEmailString.Replace("[[[[FIRST_NAME]]]]", RemoveSpeech((string)currClient[clientListFirstName])); tempEmailString = tempEmailString.Replace("[[[[IRD_NUMBER]]]]", RemoveSpeech((string)currClient[clientListIRD])); SendEmail( RemoveSpeech((string)currClient[clientListCellphone] + "@sms.tnz.co.nz"), tempEmailString, "", BodyType.Text); } } else { //MessageBox.Show("No emails were sent"); } //Create a temp file for the mail merge data, and dump it all out. //COMMENTED OUT BEHAVIOUR: If no letters, let the user know if (postListTable.Rows.Count > 0) { String mergeFileLocation = System.IO.Path.GetTempFileName() + ".csv"; FileStream mergeOutputStream = File.Create(mergeFileLocation); //write headers int columnCount = postListTable.Columns.Count; for (int i = 0; i < columnCount; i++) { AddText(mergeOutputStream, postListTable.Columns[i].ColumnName); if (i != columnCount - 1) AddText(mergeOutputStream, ","); } AddText(mergeOutputStream, Environment.NewLine); //write data foreach (DataRow currClient in postListTable.Rows) { for (int i = 0; i < columnCount; i++) { AddText(mergeOutputStream, (string)currClient[i]); if (i != columnCount - 1) AddText(mergeOutputStream, ","); } AddText(mergeOutputStream, Environment.NewLine); } mergeOutputStream.Close(); //Run the mail merge Object oMailMergeFile = tbLetterTemplate.Text; Object oMissing = System.Reflection.Missing.Value; Object oFalse = false; Object oTrue = true; Object oSql = "Select * from [Table1$]"; Word._Application oWord = new Word.Application(); oWord.Visible = false; oWord.ScreenUpdating = false; Word._Document myDoc = oWord.Documents.Add(ref oMailMergeFile, ref oMissing, ref oMissing, ref oMissing); myDoc.MailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters; myDoc.MailMerge.OpenDataSource(@mergeFileLocation, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oSql, ref oMissing, ref oMissing, ref oMissing); myDoc.MailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument; myDoc.MailMerge.Execute(ref oFalse); saveMergeDocs(oWord); myDoc.Close(ref oFalse, ref oMissing, ref oMissing); //Make the merge visible oWord.Visible = true; oWord.ScreenUpdating = true; oWord.Activate(); File.Delete(mergeFileLocation); } else { //MessageBox.Show("No letters were printed"); } //DEBUG: comment the following out for production //oExcel.Workbooks.Open(mergeFileLocation, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { MessageBox.Show("Please set either email, text, or letter to something other than \"Do Not Send\""); } }
private void shipOrders_Click(object sender, EventArgs e) { using (GenericParserAdapter parser = new GenericParserAdapter()) { try { parser.SetDataSource(tbShippingFile.Text); parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = true; DataTable parsedData = parser.GetDataTable(); string inventory = "Mylan - Epipen.com"; int currentLine = 0; foreach (DataRow row in parsedData.Rows) { string error = ""; currentLine++; XmlDocument orderPrintConfirmXMLDoc = new XmlDocument(); bool bOrderPrintConfirmXmlSuccess = BuildOrderPrintConfirmXmlDoc(row["Reference2"].ToString(), ref orderPrintConfirmXMLDoc); if (bOrderPrintConfirmXmlSuccess) { bool bOrderPrintConfirmRequestSuccess = GetDirectResponseOrderPrintConfirmWebRequest(orderPrintConfirmXMLDoc, inventory, row["Reference2"].ToString(), ref error); if (bOrderPrintConfirmRequestSuccess) { XmlDocument orderXMLDoc = new XmlDocument(); bool bShipOrderXmlSuccess = BuildOrderShipXmlDoc(row["Reference2"].ToString(), DateTime.Today, ref orderXMLDoc); if (bShipOrderXmlSuccess) { bool bShipOrderRequestSuccess = GetDirectResponseOrderShipWebRequest(orderXMLDoc, inventory, row["Reference2"].ToString(), ref error); } else { MessageBox.Show("Error occurred building ship order XML request document for order " + row["Reference2"].ToString() + ". Line number " + currentLine); break; } } else { MessageBox.Show(error + Environment.NewLine + "Line number " + currentLine); break; } } else { MessageBox.Show("Error occurred building order print confirm XML request document for order " + row["Reference2"].ToString() + ". Line number " + currentLine); break; } } MessageBox.Show("All orders are shipped."); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
private void createDistroLists_Click(object sender, EventArgs e) { using (GenericParserAdapter parser = new GenericParserAdapter()) { try { parser.SetDataSource(tbOrderList.Text); parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = true; DataTable parsedData = parser.GetDataTable(); DataTable uniqueKits = parsedData.DefaultView.ToTable(true, "Description"); foreach (DataRow row in uniqueKits.Rows) { DataTable kits = parsedData.Select("[Description] = '" + row["Description"].ToString() + "'").CopyToDataTable(); DataTable distro = new DistroList().CreateDistroTable(); foreach (DataRow kitRecord in kits.Rows) { DataRow newRecord = distro.NewRow(); newRecord["Address1"] = kitRecord["Address Line 1"]; newRecord["Address2"] = kitRecord["Address Line 2"]; newRecord["Address3"] = kitRecord["Address Line 3"]; newRecord["Attention"] = kitRecord["Ship To"]; newRecord["Company"] = kitRecord["Company"]; newRecord["City"] = kitRecord["City"]; newRecord["State"] = kitRecord["State"]; newRecord["Zip"] = kitRecord["Zip"].ToString().Substring(0, 5); newRecord["Telephone"] = kitRecord["Ship Phone"]; newRecord["Reference1"] = kitRecord["Item #"]; newRecord["Reference2"] = kitRecord["Order #"]; distro.Rows.Add(newRecord); } WriteDistroCSV(ConfigurationManager.AppSettings["OutputPath"] + DateTime.Today.ToString("MM-dd-yy") + "_" + row["Description"].ToString() + ".csv", distro); } MessageBox.Show("Distro lists successfully created."); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
private void ProfanityCheck(object sender, DoWorkEventArgs e) { CheckForIllegalCrossThreadCalls = false; //bad practice, but don't care :) BackgroundWorker worker = sender as BackgroundWorker; //Set up the profanity checker GenericParserAdapter profanityFileParser = new GenericParserAdapter(); profanityFileParser.SetDataSource(tbProfanityList.Text); profanityFileParser.FirstRowHasHeader = true; profanityFileParser.ColumnDelimiter = ','; System.Data.DataTable profanityFileTable = profanityFileParser.GetDataTable(); List<string> profanityList = new List<string>(); foreach(DataRow currentRow in profanityFileTable.Rows) { profanityList.Add((string)currentRow[0]); } Censor myProfanityCensor = new Censor(profanityList); //Set up the file to be checked for profanity GenericParserAdapter checkFileParser = new GenericParserAdapter(); checkFileParser.SetDataSource(tbCheckFile.Text); checkFileParser.FirstRowHasHeader = true; checkFileParser.ColumnDelimiter = ','; System.Data.DataTable checkFileTable = checkFileParser.GetDataTable(); //Set up various ouput files FileStream nonProfaneStream = File.Create(tbCheckFile.Text + ".clean.csv"); FileStream profaneStream = File.Create(tbCheckFile.Text + ".dirty.csv"); //Run the profanity check, outputting data as we go int totalSteps = checkFileTable.Rows.Count; int iterations = 0; foreach (DataRow currentRow in checkFileTable.Rows) { iterations += 1; bool taintedRow = false; String profanity = ""; String profanityContext = ""; //Check for profanity foreach (String currItem in currentRow.ItemArray) { //if profane, break the loop and proceed to file output if (myProfanityCensor.ContainsProfanity(currItem,ref profanity)) { taintedRow = true; profanityContext = currItem; break; } } //Output to appropriate file; if (taintedRow) { AddText(profaneStream, profanity + ","); AddText(profaneStream, profanityContext + ","); for (int i = 0; i < currentRow.ItemArray.Length - 1; i++) { AddText(profaneStream, EncapsulateSpeech(currentRow.ItemArray[i].ToString()) + ","); } if (currentRow.ItemArray.Length >= 1) AddText(profaneStream, EncapsulateSpeech( currentRow.ItemArray[currentRow.ItemArray.Length-1].ToString()) + Environment.NewLine); } else { for (int i = 0; i < currentRow.ItemArray.Length - 1; i++) { AddText(nonProfaneStream, EncapsulateSpeech(currentRow.ItemArray[i].ToString()) + ","); } if (currentRow.ItemArray.Length >= 1) AddText(nonProfaneStream, EncapsulateSpeech(currentRow.ItemArray[currentRow.ItemArray.Length - 1].ToString()) + Environment.NewLine); } //Report progress to UI worker.ReportProgress((int)(((double)iterations / (double)totalSteps) * 100)); } //Tidy up profaneStream.Close(); nonProfaneStream.Close(); }
private void btnRunAFIGeneration_Click(object sender, EventArgs e) { decimal allToFeesThreshold = nudAllToFeeThreshold.Value; decimal stdAdminFee = nudStdAdminFee.Value; string originatorName = tbOriginatorName.Text; //Read in the source file GenericParserAdapter myParser = new GenericParserAdapter(); myParser.SetDataSource(tbAFISource.Text); myParser.FirstRowHasHeader = true; myParser.ColumnDelimiter = ','; System.Data.DataTable myDataTable = myParser.GetDataTable(); //Begin constructing the AFI files FileStream afiStream = File.Create(tbAFIOutput.Text); FileStream csvStreamBankNotes = File.Create(tbBankNotesFile.Text); //Add the Header record to our output AddText(afiStream, "1,,,,0208280053460003,7,120330,120330,\r\n"); decimal txnCount = 0.00M; string irdBankAccount = "0300490001100027"; decimal hashSectionOfIRDAccount = decimal.Parse(irdBankAccount.Substring(2, 11)); decimal sumDollars = 0.00M; decimal sumAdminDollars = 0.00M; decimal sumReturnedDollars = 0.00M; //Loop over each row, converting into the required format and writing to file foreach (DataRow currRow in myDataTable.Rows) { decimal amtInDollars = decimal.Parse((string)currRow["Amount"]); decimal returnedDollars = 0.00M; decimal adminDollars = 0.00M; //If under the "all to fees" threshold, the whole amount goes towards admin fees if (amtInDollars < allToFeesThreshold) { returnedDollars = 0.00M; adminDollars = amtInDollars; sumAdminDollars += adminDollars; sumReturnedDollars += returnedDollars; sumDollars += amtInDollars; //Make appropriate notes to go on file AddText(csvStreamBankNotes, currRow["FoundIRD"] + ", [Imported Note] Cheque for $" + String.Format("{0:0.00}", amtInDollars) + " issued on " + currRow["ChequeDate"] + " has gone stale. Is not being returned to IRD as amount is too small. Full amount of $" + String.Format("{0:0.00}", amtInDollars) + " will be retained for admin work.\r\n"); } else //If over the "all to fees" threshold, standard admin charges apply { adminDollars = stdAdminFee; returnedDollars = (amtInDollars - stdAdminFee); sumAdminDollars += adminDollars; sumReturnedDollars += returnedDollars; sumDollars += amtInDollars; //Build the transaction StringBuilder currTxn = new StringBuilder(); currTxn.Append("2,"); //Transaction record currTxn.Append(irdBankAccount + ","); //Other party bank acct currTxn.Append("50,"); //Transaction Code currTxn.Append(String.Format("{0:0.00}", returnedDollars).Replace(".", "") + ","); //Amount to be returned to IRD in cents, as a string currTxn.Append("IRD,"); //Other party name currTxn.Append(currRow["FoundIRD"] + ","); //Other Party Reference currTxn.Append(","); //Other Party Code currTxn.Append(","); //Other Party Alpha Reference currTxn.Append("INC 31032012,"); //Other Party Particulars currTxn.Append(originatorName + ","); //Originator Name currTxn.Append(","); //Originator Code currTxn.Append(","); //Originator Reference currTxn.Append(""); //Originator Particulars = blank, last column, so no comma follows currTxn.Append("\r\n"); //Terminate the record with forced CrLf, no deferring to Environment //Record constructed, write it out AddText(afiStream, currTxn.ToString()); //Make appropriate notes to be imported AddText(csvStreamBankNotes, currRow["FoundIRD"] + ", [Imported Note] Cheque for $" + String.Format("{0:0.00}", amtInDollars) + " issued on " + currRow["ChequeDate"] + " is being returned to IRD tonight as has gone stale. $" + String.Format("{0:0.00}", returnedDollars) + " will be returned to IRD and $" + String.Format("{0:0.00}", adminDollars) + " will be retained for admin work.\r\n"); txnCount += 1; currTxn = null; } } //Calculate the hash string hashTotal = String.Format("{0:0}",(hashSectionOfIRDAccount * txnCount)); if (hashTotal.Length < 11) hashTotal = hashTotal.PadLeft(11, '0'); else if (hashTotal.Length > 11) hashTotal = hashTotal.Substring(hashTotal.Length - 11); //Add the Trailer record to our output AddText(afiStream, "3," + String.Format("{0:0.00}", sumReturnedDollars).Replace(".", "") + "," + String.Format("{0:0}",txnCount) + "," + hashTotal + "\r\n"); //Close the file, we're done :) afiStream.Close(); afiStream = null; csvStreamBankNotes.Close(); csvStreamBankNotes = null; //Verify the transactions sum to correct amounts MessageBox.Show("Transaction total = $" + String.Format("{0:0.00}",sumDollars) + " = Returned amount + Admin fees?: " + (sumDollars == (sumAdminDollars + sumReturnedDollars))); MessageBox.Show("Write this number down and give to Accounts. This is admin fees: $" + sumAdminDollars); }
private void MatchChequesAlt(object sender, DoWorkEventArgs e) { CheckForIllegalCrossThreadCalls = false; //bad practice, but don't care :) BackgroundWorker worker = sender as BackgroundWorker; string delimiter = ","; GenericParserAdapter myParser = new GenericParserAdapter(); myParser.SetDataSource(tbStaleChequeList.Text); myParser.FirstRowHasHeader = true; myParser.ColumnDelimiter = ','; System.Data.DataTable myDataTable = myParser.GetDataTable(); SortedList<double,Tuple<double,float>> chequeList = new SortedList<double,Tuple<double,float>>(); //<Cheque, <IRDNumber, amount>> FileStream errorStream = File.Create(tbOutputLocation.Text + ".errors.csv"); int errorCount = 0; //Loop over each row/cheque, first checking if stale and if so, adding it to a list to find foreach (DataRow currentRow in myDataTable.Rows) { try { chequeList.Add(Double.Parse((string)currentRow["ChequeNum"]), new Tuple<double, float>(Double.Parse((string)currentRow["FoundIRD"]), float.Parse((string)currentRow["Amount"]))); } catch (Exception staleParseFailure) { AddText(errorStream, "Shit went wrong. Here's some useful data: " + currentRow["FoundIRD"] + "; " +currentRow["Amount"] + ". Exception: \"" + staleParseFailure.ToString() + "\"" + Environment.NewLine); } } //Open each file which potentially contains what we want, and search for each cheque number. If found, dump the line and filename into a csv FileStream outputFileStream = File.Create(tbOutputLocation.Text); /*AddText(outputFileStream, "\"ChequeNum\"" + delimiter); AddText(outputFileStream, "\"Date\"" + delimiter); AddText(outputFileStream, "\"Amount\"" + Environment.NewLine);*/ var filesToCheck = System.IO.Directory.EnumerateFiles(@tbRootCHQFolder.Text, "*", SearchOption.AllDirectories); int totalSteps = filesToCheck.Count(); Excel._Application oExcel = new Excel.Application(); oExcel.Visible = false; oExcel.ScreenUpdating = false; oExcel.DisplayAlerts = false; int iterations = 0; foreach (var currentFile in filesToCheck) { lock (thisLock) { iterations += 1; try { Excel.Workbook myWorkBook = oExcel.Workbooks.Open(currentFile); //Check each worksheet in the book foreach (Excel.Worksheet currWorksheet in myWorkBook.Worksheets) { Excel.Range lastCell = currWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); //if there's something in the worksheet then try and find the cheque if (lastCell.Column > 1) { Excel.Range firstCell = currWorksheet.get_Range("A1", Type.Missing); Excel.Range entireSheetRange = currWorksheet.get_Range(firstCell, lastCell); //foreach (var currCheque in chequeList) //I want to remove items as I'm going so am using the iterate backwards method, and using an enumerator causes errors! for (int currChequeIndex = chequeList.Count - 1; currChequeIndex > -1; currChequeIndex--) { double currChequeKey = chequeList.Keys[currChequeIndex]; Tuple<double,float> currChequeValues = chequeList.Values[currChequeIndex]; Excel.Range currentFind = null; //find the $ value of the cheque in the sheet, if poss currentFind = entireSheetRange.Find(currChequeValues.Item2, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); //Found the cheque, write out the info we want if (currentFind != null) { //Remove the cheque from our list, as we're now wasting time looking for it //chequeList.Remove(currChequeKey); AddText(outputFileStream, "\"" + currChequeKey.ToString() + "\"" + delimiter); AddText(outputFileStream, "\"" + currChequeValues.Item1 + "\"" + delimiter); AddText(outputFileStream, "\"" + currChequeValues.Item2 + "\"" + delimiter); AddText(outputFileStream, "\"" + currentFile + "\"" + delimiter); //dump out the whole line we found the match on int numCols = currentFind.EntireRow.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column; for (int i = 1; i <= numCols; i++) { if (i != numCols) { AddText(outputFileStream, "\"" + currentFind.EntireRow.Cells[1, i].Text + "\"" + delimiter); } else { AddText(outputFileStream, "\"" + currentFind.EntireRow.Cells[1, i].Text + "\"" + Environment.NewLine); } } } currentFind = null; //find the IRD# in the sheet, if poss, as well currentFind = entireSheetRange.Find(currChequeValues.Item1, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); //Found the cheque, write out the info we want if (currentFind != null) { //Remove the cheque from our list, as we're now wasting time looking for it //chequeList.Remove(currChequeKey); AddText(outputFileStream, "\"" + currChequeKey.ToString() + "\"" + delimiter); AddText(outputFileStream, "\"" + currChequeValues.Item1 + "\"" + delimiter); AddText(outputFileStream, "\"" + currChequeValues.Item2 + "\"" + delimiter); AddText(outputFileStream, "\"" + currentFile + "\"" + delimiter); //dump out the whole line we found the match on int numCols = currentFind.EntireRow.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column; for (int i = 1; i <= numCols; i++) { if (i != numCols) { AddText(outputFileStream, "\"" + currentFind.EntireRow.Cells[1, i].Text + "\"" + delimiter); } else { AddText(outputFileStream, "\"" + currentFind.EntireRow.Cells[1, i].Text + "\"" + Environment.NewLine); } } } } } } myWorkBook.Close(false, false, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkBook); //http://support.microsoft.com/kb/317109 } catch (Exception excelOpenFailure) { AddText(errorStream, "\"Excel operation error occurred. Trying to continue. Exception message: " + excelOpenFailure.Message + "\"" + Environment.NewLine); errorCount += 1; lblErrorCount.Text = ("Excel Error Count: " + errorCount); /*//if we fail, try and close the Excel instance if poss, and recreate it! If this completely fails, crash inelegantly :( try { oExcel.Visible = false; oExcel.ScreenUpdating = false; oExcel.DisplayAlerts = false; oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); //http://support.microsoft.com/kb/317109 } catch (Exception doublecrash) { //Do nothing }*/ oExcel = null; oExcel = new Excel.Application(); oExcel.Visible = false; oExcel.ScreenUpdating = false; oExcel.DisplayAlerts = false; } //Report progress worker.ReportProgress((int)(((double)iterations / (double)totalSteps) * 100)); } } //Dump unfound cheques FileStream outputFileStream2 = File.Create(tbOutputLocation.Text + ".faileddump.csv"); if (chequeList.Count != 0) { foreach (var currCheque in chequeList) { AddText(outputFileStream2, "\"" + currCheque.Key.ToString() + "\"" + Environment.NewLine); } } //Shut down everything outputFileStream.Close(); outputFileStream = null; outputFileStream2.Close(); outputFileStream2 = null; errorStream.Close(); errorStream = null; oExcel.Visible = true; oExcel.ScreenUpdating = true; oExcel.DisplayAlerts = true; oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); //http://support.microsoft.com/kb/317109 }
// Unused for Now, could be useful in teh future public static DataSet loadExcelData() { // Setup the DataSet DataSet ds = new DataSet(); // Use Generic Txt Parser to load source_sphere.csv to a dataset. using (GenericParserAdapter parser = new GenericParserAdapter()) { parser.SetDataSource(Path.Combine(root, @".\data\source_sphere.csv")); parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = true; parser.TextQualifier = '\"'; ds = parser.GetDataSet(); } ds.Tables[0].TableName = "data"; return ds; }
/// <summary> /// Method to process all Query plugins. /// </summary> private void QueryPlugins(string query, List<string> types, bool sensitive) { foreach (string tType in types) //Cycle through a List<string> { foreach (var qPlugins in this.QPlugins) //Cycle through all query plugins { foreach (string qType in qPlugins.TypesAccepted) //Cycle though a List<string> within the IQueryPlugin interface AcceptedTypes { if (qType == tType) //Match the two List<strings>, one is the AcceptedTypes and the other is the one returned from ITypeQuery { using (GenericParserAdapter parser = new GenericParserAdapter()) { using (TextReader sr = new StringReader(qPlugins.Result(query, qType, sensitive))) { Random rNum = new Random(); parser.SetDataSource(sr); parser.ColumnDelimiter = Convert.ToChar(","); parser.FirstRowHasHeader = true; parser.MaxBufferSize = 4096; parser.MaxRows = 500; parser.TextQualifier = '\"'; DataTable tempTable = parser.GetDataTable(); tempTable.TableName = qPlugins.Name.ToString(); if (!tempTable.Columns.Contains("Query")) { DataColumn tColumn = new DataColumn("Query"); tempTable.Columns.Add(tColumn); tColumn.SetOrdinal(0); } foreach (DataRow dr in tempTable.Rows) { dr["Query"] = query; } if (!resultDS.Tables.Contains(qPlugins.Name.ToString())) { resultDS.Tables.Add(tempTable); } else { resultDS.Tables[qPlugins.Name.ToString()].Merge(tempTable); } pluginsLB.DataContext = resultDS.Tables.Cast<DataTable>().Select(t => t.TableName).ToList(); } } } } } } }