public Dictionary <int, SampleTableModel> ScanForColumnsAndParseTable(IFormFile file, ImportViewModel model, string password) { try { ExcelPackage package = _validatior.GetExcelPackage(file, password); ExcelWorksheet sheet = _validatior.GetExcelWorksheet(package, _sheetName, out ParseException exception); if (sheet == null) { //Cannot find sheet throw error to the controller throw new ImportException(exception); } ParsedTable <SampleTableModel> parsedTable = _tableParser.ScanForColumnsAndParseTable(GetTableColumnTemplates(), sheet); if (parsedTable.Exceptions.Any(x => x.Value.Severity == ParseExceptionSeverity.Error)) { //If we encountered any errors in the parse lets throw them to the controller throw new ImportException(parsedTable.Exceptions); } return(parsedTable.Rows); } catch (ImportException) { //Cannot find an excel file throw; } }
private ParsedTable CreateParsedTable(string ExcelFile) { var fstream = new FileStream(ExcelFile, FileMode.Open); var workbook = new Aspose.Cells.Workbook(fstream); var sheet = workbook.Worksheets[0]; var pt = new ParsedTable(sheet); fstream.Close(); return(pt); }
private ParsedTable CreateParsedTable(string ExcelFile) //File is downloaded, put into excel workbook format, workbook put into a parsed Table a table defined by another programmer { var fstream = new FileStream(ExcelFile, FileMode.Open); var workbook = new Aspose.Cells.Workbook(fstream); var sheet = workbook.Worksheets[0]; var pt = new ParsedTable(sheet); fstream.Close(); return(pt); }
public TableParser(IExtensions excelExtensions, IParser parser) { _excelExtensions = excelExtensions; _parser = parser; _model = Activator.CreateInstance <T>(); _singleRowErrors = new List <KeyValuePair <int, ParseException> >(); _requiredFieldsColumnLocations = new List <string>(); _parseResults = new ParsedTable <T>(); _requiredFieldMissingMessages = new List <ParseException>(); }
public void Visit(ParsedTable pars) { AppendEverything(pars); foreach (var field in pars.Fields) { AppendEverything(field); } foreach (var index in pars.Indexes) { AppendEverything(index); } }
/// <summary> /// Returns the list of fields for a given table (it can also be a temp table!) /// </summary> /// <param name="table"></param> /// <returns></returns> public static List <CompletionItem> GetFieldsList(ParsedTable table) { var output = new List <CompletionItem>(); if (table == null) { return(output); } output.AddRange(table.Fields.Select(field => new CompletionItem { DisplayText = field.Name, Type = (field.Flag.HasFlag(ParsedFieldFlag.Primary)) ? CompletionType.FieldPk : CompletionType.Field, FromParser = false, SubString = field.Type.ToString(), Ranking = AutoComplete.FindRankingOfDatabaseItem(field.Name), Flag = (field.Flag.HasFlag(ParsedFieldFlag.Mandatory) ? ParseFlag.Mandatory : 0) | (field.Flag.HasFlag(ParsedFieldFlag.Index) ? ParseFlag.Index : 0) | (field.Flag.HasFlag(ParsedFieldFlag.Extent) ? ParseFlag.Extent : 0), ParsedItem = table })); return(output); }
/// <summary> /// This method parses the output of the .p procedure that exports the database info /// and fills _dataBases /// It then updates the parser with the new info /// </summary> private static void Read(string filePath) { if (!File.Exists(filePath)) { return; } _dataBases.Clear(); _sequences.Clear(); var defaultToken = new TokenEos(null, 0, 0, 0, 0); ParsedDataBase currentDb = null; ParsedTable currentTable = null; Utils.ForEachLine(filePath, null, (i, items) => { var splitted = items.Split('\t'); switch (items[0]) { case 'H': // base //#H|<Dump date ISO 8601>|<Dump time>|<Logical DB name>|<Physical DB name>|<Progress version> if (splitted.Count() != 6) { return; } currentDb = new ParsedDataBase( splitted[3], splitted[4], splitted[5], new List <ParsedTable>()); _dataBases.Add(currentDb); break; case 'S': if (splitted.Count() != 3 || currentDb == null) { return; } _sequences.Add(new CompletionItem { DisplayText = splitted[1], Type = CompletionType.Sequence, SubString = currentDb.LogicalName }); break; case 'T': // table //#T|<Table name>|<Table ID>|<Table CRC>|<Dump name>|<Description> if (splitted.Count() != 6 || currentDb == null) { return; } currentTable = new ParsedTable( splitted[1], defaultToken, splitted[2], splitted[3], splitted[4], splitted[5], "", false, new List <ParsedField>(), new List <ParsedIndex>(), new List <ParsedTrigger>() , "", ""); currentDb.Tables.Add(currentTable); break; case 'X': // trigger //#X|<Parent table>|<Event>|<Proc name>|<Trigger CRC> if (splitted.Count() != 5 || currentTable == null) { return; } currentTable.Triggers.Add(new ParsedTrigger( splitted[2], splitted[3])); break; case 'I': // index //#I|<Parent table>|<Index name>|<Primary? 0/1>|<Unique? 0/1>|<Index CRC>|<Fileds separated with %> if (splitted.Count() != 7 || currentTable == null) { return; } var flag = splitted[3].Equals("1") ? ParsedIndexFlag.Primary : ParsedIndexFlag.None; if (splitted[4].Equals("1")) { flag = flag | ParsedIndexFlag.Unique; } currentTable.Indexes.Add(new ParsedIndex( splitted[2], flag, splitted[6].Split('%').ToList())); break; case 'F': // field //#F|<Parent table>|<Field name>|<Type>|<Format>|<Order #>|<Mandatory? 0/1>|<Extent? 0/1>|<Part of index? 0/1>|<Part of PK? 0/1>|<Initial value>|<Desription> if (splitted.Count() != 12 || currentTable == null) { return; } var flag2 = splitted[6].Equals("1") ? ParsedFieldFlag.Mandatory : ParsedFieldFlag.None; if (splitted[7].Equals("1")) { flag2 = flag2 | ParsedFieldFlag.Extent; } if (splitted[8].Equals("1")) { flag2 = flag2 | ParsedFieldFlag.Index; } if (splitted[9].Equals("1")) { flag2 = flag2 | ParsedFieldFlag.Primary; } var curField = new ParsedField( splitted[2], splitted[3], splitted[4], int.Parse(splitted[5]), flag2, splitted[10], splitted[11], ParsedAsLike.None); curField.Type = ParserHandler.ConvertStringToParsedPrimitiveType(curField.TempType, false); currentTable.Fields.Add(curField); break; } }); }
/// <summary> /// Matches a new definition /// </summary> private void CreateParsedDefine(Token defineToken, bool isDynamic) { /* * all DEFINE and CREATE statement */ // info we will extract from the current statement : string name = ""; ParseFlag flags = isDynamic ? ParseFlag.Dynamic : 0; ParsedAsLike asLike = ParsedAsLike.None; ParseDefineType type = ParseDefineType.None; string tempPrimitiveType = ""; string viewAs = ""; string bufferFor = ""; int extent = 0; _lastTokenWasSpace = true; StringBuilder left = new StringBuilder(); // for temp tables: string likeTable = ""; bool isTempTable = false; var fields = new List <ParsedField>(); ParsedField currentField = new ParsedField("", "", "", 0, 0, "", "", ParsedAsLike.None); StringBuilder useIndex = new StringBuilder(); // for tt indexes var indexList = new List <ParsedIndex>(); string indexName = ""; var indexFields = new List <string>(); ParsedIndexFlag indexFlags = ParsedIndexFlag.None; var indexSort = "+"; // + for ascending, - for descending Token token; int state = 0; do { token = PeekAt(1); // next token if (token is TokenEos) { break; } if (token is TokenComment) { continue; } string lowerToken; bool matchedLikeTable = false; switch (state) { case 0: // matching until type of define is found if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); switch (lowerToken) { case "buffer": case "browse": case "stream": case "button": case "dataset": case "frame": case "query": case "event": case "image": case "menu": case "rectangle": case "property": case "sub-menu": case "parameter": if (!Enum.TryParse(lowerToken, true, out type)) { type = ParseDefineType.None; } state++; break; case "data-source": type = ParseDefineType.DataSource; state++; break; case "var": case "variable": type = ParseDefineType.Variable; state++; break; case "temp-table": case "work-table": case "workfile": isTempTable = true; state++; break; case "new": flags |= ParseFlag.New; break; case "global": flags |= ParseFlag.Global; break; case "shared": flags |= ParseFlag.Shared; break; case "private": flags |= ParseFlag.Private; break; case "protected": flags |= ParseFlag.Protected; break; case "public": flags |= ParseFlag.Public; break; case "static": flags |= ParseFlag.Static; break; case "abstract": flags |= ParseFlag.Abstract; break; case "override": flags |= ParseFlag.Override; break; case "serializable": flags |= ParseFlag.Serializable; break; case "input": flags |= ParseFlag.Input; break; case "return": flags |= ParseFlag.Return; break; case "output": flags |= ParseFlag.Output; break; case "input-output": flags |= ParseFlag.InputOutput; break; /*default: * ParseFlag parsedFlag; * if (Enum.TryParse(lowerToken, true, out parsedFlag)) * flags |= parsedFlag; * break;*/ } break; case 1: // matching the name if (!(token is TokenWord)) { break; } name = token.Value; if (type == ParseDefineType.Variable) { state = 10; } if (type == ParseDefineType.Buffer) { tempPrimitiveType = "buffer"; state = 81; } if (type == ParseDefineType.Parameter) { lowerToken = token.Value.ToLower(); switch (lowerToken) { case "buffer": tempPrimitiveType = lowerToken; type = ParseDefineType.Buffer; flags |= ParseFlag.Parameter; state = 80; break; case "table": case "table-handle": case "dataset": case "dataset-handle": tempPrimitiveType = lowerToken; state = 80; break; default: state = 10; break; } } if (isTempTable) { state = 20; } if (state != 1) { break; } state = 99; break; case 10: // define variable : match as or like if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); if (lowerToken.Equals("as")) { asLike = ParsedAsLike.As; } else if (lowerToken.Equals("like")) { asLike = ParsedAsLike.Like; } if (asLike != ParsedAsLike.None) { state = 11; } break; case 11: // define variable : match a primitive type or a field in db if (!(token is TokenWord)) { break; } tempPrimitiveType = token.Value; state = 12; break; case 12: // define variable : match a view-as (or extent) AddTokenToStringBuilder(left, token); if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); if (lowerToken.Equals("view-as")) { state = 13; } if (lowerToken.Equals("extent")) { extent = GetExtentNumber(2); } break; case 13: // define variable : match a view-as AddTokenToStringBuilder(left, token); if (!(token is TokenWord)) { break; } viewAs = token.Value; state = 99; break; case 20: // define temp-table if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); switch (lowerToken) { case "field": // matches FIELD state = 22; break; case "index": // matches INDEX state = 25; break; case "use-index": // matches USE-INDEX (after a like/like-sequential, we can have this keyword) state = 26; break; case "help": // a field has a help text: state = 27; break; case "initial": // a field has an initial value state = 29; break; case "format": // a field has a format state = 30; break; case "extent": // a field is extent: currentField.Extent = GetExtentNumber(2); break; default: // matches a LIKE table // ReSharper disable once ConditionIsAlwaysTrueOrFalse, resharper doesn't get this one if ((lowerToken.Equals("like") || lowerToken.Equals("like-sequential")) && !matchedLikeTable) { state = 21; } // After a USE-UNDEX and the index name, we can match a AS PRIMARY for the previously defined index if (lowerToken.Equals("primary") && useIndex.Length > 0) { useIndex.Append("!"); } break; } break; case 21: // define temp-table : match a LIKE table, get the table name in asLike // ReSharper disable once RedundantAssignment matchedLikeTable = true; if (!(token is TokenWord)) { break; } likeTable = token.Value.ToLower(); state = 20; break; case 22: // define temp-table : matches a FIELD name if (!(token is TokenWord)) { break; } currentField = new ParsedField(token.Value, "", "", 0, 0, "", "", ParsedAsLike.None); state = 23; break; case 23: // define temp-table : matches a FIELD AS or LIKE if (!(token is TokenWord)) { break; } currentField.AsLike = token.Value.EqualsCi("like") ? ParsedAsLike.Like : ParsedAsLike.As; state = 24; break; case 24: // define temp-table : match a primitive type or a field in db if (!(token is TokenWord)) { break; } currentField.TempPrimitiveType = token.Value; // push the field to the fields list fields.Add(currentField); state = 20; break; case 25: // define temp-table : match an index name if (!(token is TokenWord)) { break; } indexName = token.Value; state = 28; break; case 28: // define temp-table : match the definition of the index if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); if (lowerToken.Equals("unique")) { indexFlags = indexFlags | ParsedIndexFlag.Unique; } else if (lowerToken.Equals("primary")) { indexFlags = indexFlags | ParsedIndexFlag.Primary; } else if (lowerToken.Equals("word-index")) { indexFlags = indexFlags | ParsedIndexFlag.WordIndex; } else if (lowerToken.Equals("ascending")) { // match a sort order for a field indexSort = "+"; var lastField = indexFields.LastOrDefault(); if (lastField != null) { indexFields.RemoveAt(indexFields.Count - 1); indexFields.Add(lastField.Replace("-", "+")); } } else if (lowerToken.Equals("descending")) { // match a sort order for a field indexSort = "-"; var lastField = indexFields.LastOrDefault(); if (lastField != null) { indexFields.RemoveAt(indexFields.Count - 1); indexFields.Add(lastField.Replace("+", "-")); } } else if (lowerToken.Equals("index")) { // matching a new index if (!String.IsNullOrEmpty(indexName)) { indexList.Add(new ParsedIndex(indexName, indexFlags, indexFields.ToList())); } indexName = ""; indexFields.Clear(); indexFlags = ParsedIndexFlag.None; indexSort = "+"; state = 25; } else { // Otherwise, it's a field name indexFields.Add(token.Value + indexSort); } break; case 26: // define temp-table : match a USE-INDEX name if (!(token is TokenWord)) { break; } useIndex.Append(","); useIndex.Append(token.Value); state = 20; break; case 27: // define temp-table : match HELP for a field if (!(token is TokenString)) { break; } currentField.Description = GetTokenStrippedValue(token); state = 20; break; case 29: // define temp-table : match INITIAL for a field if (!(token is TokenWhiteSpace)) { currentField.InitialValue = GetTokenStrippedValue(token); state = 20; } break; case 30: // define temp-table : match FORMAT for a field if (!(token is TokenWhiteSpace)) { currentField.Format = GetTokenStrippedValue(token); state = 20; } break; case 80: // define parameter : match a temptable, table, dataset or buffer name if (!(token is TokenWord)) { break; } if (token.Value.ToLower().Equals("for")) { break; } name = token.Value; state++; break; case 81: // match the table/dataset name that the buffer or handle is FOR if (!(token is TokenWord)) { break; } lowerToken = token.Value.ToLower(); if (lowerToken.Equals("for") || lowerToken.Equals("temp-table")) { break; } bufferFor = lowerToken; state = 99; break; case 99: // matching the rest of the define AddTokenToStringBuilder(left, token); break; } } while (MoveNext()); if (state <= 1) { return; } if (!string.IsNullOrEmpty(indexName)) { indexList.Add(new ParsedIndex(indexName, indexFlags, indexFields)); } if (isTempTable) { // TEMP-TABLE var newTable = new ParsedTable(name, defineToken, ParsedTableType.TT, null, null, name, null, likeTable, fields, indexList, new List <ParsedTrigger>(), useIndex.ToString(), false, false) { // = end position of the EOS of the statement EndPosition = token.EndPosition, Flags = flags }; AddParsedItem(newTable, defineToken.OwnerNumber); } else { // other DEFINE var newDefine = NewParsedDefined(name, flags, defineToken, token, asLike, left.ToString(), type, tempPrimitiveType, viewAs, bufferFor, extent); AddParsedItem(newDefine, defineToken.OwnerNumber); // case of a parameters, add it to the current scope (if procedure) var currentScope = GetCurrentBlock <ParsedScopeBlock>() as ParsedProcedure; if (type == ParseDefineType.Parameter && currentScope != null) { if (currentScope.Parameters == null) { currentScope.Parameters = new List <ParsedDefine>(); } currentScope.Parameters.Add(newDefine); } } }
/// <summary> /// This method parses the output of the .p procedure that exports the database info /// and fills _dataBases /// It then updates the parser with the new info /// </summary> private void Read(string filePath) { if (!File.Exists(filePath)) { return; } _dataBases.Clear(); _sequences.Clear(); var defaultToken = new TokenEos(null, 0, 0, 0, 0); ParsedDataBase currentDb = null; ParsedTable currentTable = null; Utils.ForEachLine(filePath, null, (i, items) => { var splitted = items.Split('\t'); switch (items[0]) { case 'H': // base //#H|<Dump date ISO 8601>|<Dump time>|<Logical DB name>|<Physical DB name>|<Progress version> if (splitted.Length != 6) { return; } currentDb = new ParsedDataBase( splitted[3], splitted[4], splitted[5], new List <ParsedTable>(), null); _dataBases.Add(currentDb); break; case 'S': if (splitted.Length != 3 || currentDb == null) { return; } _sequences.Add(new ParsedSequence { SeqName = splitted[1], DbName = currentDb.Name }); break; case 'T': // table //#T|<Table name>|<Table ID>|<Table CRC>|<Dump name>|<Description>|<Hidden? 0/1>|<Frozen? 0/1>|<Table type> if (splitted.Length != 9 || currentDb == null) { return; } ParsedTableType tblType; if (!Enum.TryParse(splitted[8].Trim(), true, out tblType)) { tblType = ParsedTableType.T; } currentTable = new ParsedTable( splitted[1], defaultToken, tblType, splitted[2], splitted[3], splitted[4], splitted[5], null, new List <ParsedField>(), new List <ParsedIndex>(), new List <ParsedTrigger>(), null, splitted[6].Equals("1"), splitted[7].Equals("1") ); currentDb.Tables.Add(currentTable); break; case 'X': // trigger //#X|<Parent table>|<Event>|<Proc name>|<Trigger CRC> if (splitted.Length != 5 || currentTable == null) { return; } currentTable.Triggers.Add(new ParsedTrigger( splitted[2], splitted[3])); break; case 'I': // index //#I|<Parent table>|<Index name>|<Primary? 0/1>|<Unique? 0/1>|<Index CRC>|<Fileds separated with %> if (splitted.Length != 7 || currentTable == null) { return; } var flag = splitted[3].Equals("1") ? ParsedIndexFlag.Primary : ParsedIndexFlag.None; if (splitted[4].Equals("1")) { flag = flag | ParsedIndexFlag.Unique; } currentTable.Indexes.Add(new ParsedIndex( splitted[2], flag, splitted[6].Split('%').ToList())); break; case 'F': // field //#F|<Parent table>|<Field name>|<Type>|<Format>|<Order #>|<Mandatory? 0/1>|<Extent? 0/x>|<Part of index? 0/1>|<Part of PK? 0/1>|<Initial value>|<Desription> if (splitted.Length != 12 || currentTable == null) { return; } var flags = splitted[6].Equals("1") ? ParseFlag.Mandatory : 0; if (splitted[8].Equals("1")) { flags = flags | ParseFlag.Index; } if (splitted[9].Equals("1")) { flags = flags | ParseFlag.Primary; } var curField = new ParsedField( splitted[2], splitted[3], splitted[4], int.Parse(splitted[5]), flags, splitted[10], splitted[11], ParsedAsLike.None); curField.Extent = int.Parse(splitted[7]); curField.PrimitiveType = ParserVisitor.ConvertStringToParsedPrimitiveType(curField.TempPrimitiveType); currentTable.Fields.Add(curField); break; } }); // copy the databases for each aliases foreach (var aliasCreation in GetAliasesList.Trim().Trim(';').Split(';')) { var splitted = aliasCreation.Split(','); if (splitted.Length == 2) { if (!string.IsNullOrWhiteSpace(splitted[0]) && !string.IsNullOrWhiteSpace(splitted[1])) { var foundDb = FindDatabaseByName(splitted[1].Trim()); if (foundDb != null) { _dataBases.Add(new ParsedDataBase( splitted[0].Trim(), foundDb.PhysicalName, foundDb.ProgressVersion, foundDb.Tables, foundDb.Name)); } } } } // sort all fields by primary then by name foreach (var dataBase in _dataBases) { foreach (var table in dataBase.Tables) { table.Fields.Sort((x, y) => { var compare = y.Flags.HasFlag(ParseFlag.Primary).CompareTo(x.Flags.HasFlag(ParseFlag.Primary)); if (compare != 0) { return(compare); } return(string.Compare(x.Name, y.Name, StringComparison.CurrentCultureIgnoreCase)); }); } } }
public BrokerErrorCode GetEquitySpread(string stockCode, out EquitySymbolSpread[] info) { BrokerErrorCode errorCode = BrokerErrorCode.Success; info = new EquitySymbolSpread[2]; string quoteData = null; int retryCount = 0; do { quoteData = HttpHelper.GetWebPageResponse( URL_ICICI_EQT_SPREAD + stockCode.ToUpper(), null, null, mCookieContainer); retryCount++; } while (quoteData == null && retryCount < 5); // web problems, slow connection, server down etc. if (string.IsNullOrEmpty(quoteData) || quoteData.IndexOf("entered is not valid") > -1) { return(BrokerErrorCode.NullResponse); } ParsedTable table = (ParsedTable)HtmlTableParser.ParseHtmlIntoTables(quoteData, true); // NSE price info info[0] = new EquitySymbolSpread(); info[0].Symbol = stockCode; info[0].Exchange = Exchange.NSE; string tempStr = ParsedTable.GetValue(table, new int[] { 0, 3, 0, 1 }); DateTime lastTradeTime = DateTime.Parse(tempStr); tempStr = ParsedTable.GetValue(table, new int[] { 0, 3, 1, 1 }); lastTradeTime += TimeSpan.Parse(tempStr); info[0].QuoteTime = lastTradeTime; info[0].TotalBidQty = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, 7, 1 })); info[0].TotalOfferQty = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, 8, 1 })); for (int i = 0; i < 5; i++) { info[0].BestBidQty[i] = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 0 })); info[0].BestBidPrice[i] = MarketUtils.GetPrice(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 1 })); info[0].BestOfferQty[i] = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 2 })); info[0].BestOfferPrice[i] = MarketUtils.GetPrice(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 3 })); } // BSE price info info[1] = new EquitySymbolSpread(); info[1].Symbol = stockCode; info[1].Exchange = Exchange.BSE; tempStr = ParsedTable.GetValue(table, new int[] { 0, 3, 0, 3 }); lastTradeTime = DateTime.Parse(tempStr); tempStr = ParsedTable.GetValue(table, new int[] { 0, 3, 1, 3 }); lastTradeTime += TimeSpan.Parse(tempStr); info[1].QuoteTime = lastTradeTime; info[1].TotalBidQty = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, 7, 3 })); info[1].TotalOfferQty = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, 8, 3 })); for (int i = 0; i < 5; i++) { info[1].BestBidQty[i] = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 4 })); info[1].BestBidPrice[i] = MarketUtils.GetPrice(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 5 })); info[1].BestOfferQty[i] = MarketUtils.GetVolume(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 6 })); info[1].BestOfferPrice[i] = MarketUtils.GetPrice(ParsedTable.GetValue(table, new int[] { 0, 5, i + 2, 7 })); } return(errorCode); }
/// <summary> /// Defined Temptables /// </summary> public void Visit(ParsedTable pars) { if (!string.IsNullOrEmpty(pars.TempLikeTable)) { pars.LikeTable = FindAnyTableByName(pars.TempLikeTable); } foreach (var field in pars.Fields) { field.PrimitiveType = ConvertStringToParsedPrimitiveType(field.TempPrimitiveType, field.AsLike == ParsedAsLike.Like); } // temp table is LIKE another table? copy fields if (pars.LikeTable != null) { // add the fields of the found table (minus the primary information) foreach (var field in pars.LikeTable.Fields) { pars.Fields.Add( new ParsedField(field.Name, field.TempPrimitiveType, field.Format, field.Order, 0, field.InitialValue, field.Description, field.AsLike) { PrimitiveType = field.PrimitiveType }); } // handles the use-index if (!string.IsNullOrEmpty(pars.UseIndex)) { // add only the indexes that are used foreach (var index in pars.UseIndex.Split(',')) { var foundIndex = pars.LikeTable.Indexes.Find(index2 => index2.Name.EqualsCi(index.Replace("!", ""))); if (foundIndex != null) { pars.Indexes.Add(new ParsedIndex(foundIndex.Name, foundIndex.Flag, foundIndex.FieldsList.ToList())); // if one of the index used is marked as primary if (index.ContainsFast("!")) { pars.Indexes.ForEach(parsedIndex => parsedIndex.Flag &= ~ParsedIndexFlag.Primary); pars.Indexes.Last().Flag |= ParsedIndexFlag.Primary; } } } } else { // if there is no "use index" and we didn't define new indexes, the tt uses the same index as the original table if (pars.Indexes == null || pars.Indexes.Count == 0) { pars.Indexes = pars.LikeTable.Indexes.ToList(); } } } // browse all the indexes and set the according flags to each field of the index foreach (var index in pars.Indexes) { foreach (var fieldName in index.FieldsList) { var foundfield = pars.Fields.Find(field => field.Name.EqualsCi(fieldName.Substring(0, fieldName.Length - 1))); if (foundfield != null) { if (index.Flag.HasFlag(ParsedIndexFlag.Primary)) { foundfield.Flags |= ParseFlag.Primary; } foundfield.Flags |= ParseFlag.Index; } } } string subStr = string.IsNullOrEmpty(pars.TempLikeTable) ? "" : (pars.LikeTable != null ? @"Like " + pars.LikeTable.Name : @"Like ??"); // to auto completion var parsedTable = new TempTableCompletionItem { DisplayText = pars.Name, Flags = pars.Flags, SubText = subStr, ChildSeparator = '.' }; parsedTable.Children = pars.Fields.Select(field => { var curField = CompletionItem.Factory.New(field.Flags.HasFlag(ParseFlag.Primary) ? CompletionType.FieldPk : CompletionType.Field); curField.DisplayText = field.Name.ConvertCase(Config.Instance.AutoCompleteDatabaseWordCaseMode); curField.ParsedBaseItem = field; curField.FromParser = true; curField.SubText = field.PrimitiveType.ToString(); curField.Ranking = AutoCompletion.FindRankingOfParsedItem(field.Name); curField.Flags = field.Flags & ~ParseFlag.Primary; curField.ParentItem = parsedTable; return(curField); }).ToList(); PushToAutoCompletion(parsedTable, pars); // to code explorer PushToCodeExplorer( GetExplorerListNode("Defined temp-tables", CodeExplorerIconType.DefinedTempTable), new TempTableCodeItem { DisplayText = pars.Name, Flags = pars.Flags, SubText = subStr, DocumentOwner = pars.FilePath, GoToLine = pars.Line, GoToColumn = pars.Column }); }
private List <ParsingReturnStructure> ReadData(ParsedTable pt, string fileId) { PeriodTypes2017 frequency = PeriodTypes2017.Annual; List <Regions> regions = new List <Regions>(); List <Crimes> crimes = new List <Crimes>(); List <Footnotes> footnotes = new List <Footnotes>(); List <string> metaData = new List <string>(); List <ParsingReturnStructure> prsList = new List <ParsingReturnStructure>(); //Since there is a table made per region, instead of retruning one parsing return structure we are returning a list of them bool underArea = false; int categoryRow = 0; for (int row = 0; row <= 213; row++) //Loop through the all the rows on sheet { int num; if (row <= 202 && pt.Vals[row, 0] != null && underArea == true) //If excel cell is not null and the range of rows occurs before footnotes { regions.Add(new Regions(pt.Vals[row, 0].ToString(), row)); } if (pt.Vals[row, 0] != null && pt.Vals[row, 0].ToString() == "Area") //If row is under the Area topic, underArea is true and indicates the starting row where regions start { underArea = true; categoryRow = row; } if (pt.Vals[row, 0] != null && Int32.TryParse(pt.Vals[row, 0].ToString().Substring(0, 1), out num)) //Puts footnote id value and footnote into footnote object { footnotes.Add(new Footnotes(num, pt.Vals[row, 0].ToString().Substring(1, pt.Vals[row, 0].ToString().Length - 1))); } if (pt.Vals[row, 0] != null && pt.Vals[row, 0].ToString().Substring(0, 4) == "NOTE") { metaData.Add(pt.Vals[row, 0].ToString()); } } for (int col = 2; col < 22; col++) //iterates through all of crime columns and if it is not an empty cell adds to the crime object { if (pt.Vals[categoryRow, col] != null) { crimes.Add(new Crimes(pt.Vals[categoryRow, col].ToString(), col)); } } foreach (var region in regions) //Iterates through all of the regions, with a nested loop going through all of the crimes for each region { var dataPointList = new DataPointList(); //The Datapoint List stores a list of datapoints ParsingReturnStructure prs = new ParsingReturnStructure(); //A parsing return structure's datapoint value adds datapoint list List <int> regionFootnotes = new List <int>(); if (region.RegionName.Any(c => char.IsDigit(c))) //Determines if region name contains a footnote, if so adds footnote id number { regionFootnotes = FormatFootnoteNumbers(region.RegionName); } foreach (var crime in crimes) { List <int> crimeFootnotes = new List <int>(); if (crime.CrimeName.Any(c => char.IsDigit(c))) //Determines if crime name contains a footnote, if so adds footnote id number { crimeFootnotes = FormatFootnoteNumbers(crime.CrimeName); } var neumonic = SeriesToNeumonic(FormatString(region.RegionName) + " - " + FormatString(crime.CrimeName)); //Formats the neumonic name for the datapoint appends the region name to the crime name Guid seriesId = _nk.GetValue(neumonic); _nk.AddSeriesName(seriesId, region.RegionName); /* foreach (var regionNote in regionFootnotes) //this commented out section will add the footnotes into the into the prslist once other code that manipulates footnotes is changed * { * foreach (var note in footnotes) * { * if (regionNote == note.FootnoteId) * { * _nk.AddFootnote(note.Value, seriesId); * } * } * } * foreach (var crimeNote in crimeFootnotes) * { * foreach (var note in footnotes) * { * if (crimeNote == note.FootnoteId) * { * _nk.AddFootnote(note.Value, seriesId); * } * } * }*/ int numLoops = 1; if (crime.CrimeName.Contains("Population")) //Population category doesnt contain extra column pertaining to the rate of population committing a crime by 100k { numLoops = 0; } for (int col = crime.StartingCol; col <= crime.StartingCol + numLoops; col++) { string tempDate = ""; for (int row = region.StartingRow; row <= region.StartingRow + 1; row++) { if (row == region.StartingRow) //Dates only have year values must be modified to into a DateTime format { tempDate = "12/31/2015"; } else if (row == region.StartingRow + 1) { tempDate = "12/31/2016"; } var dp = new DataPoint2017(DateTime.Parse(tempDate), frequency, decimal.Parse(pt.Vals[row, col])); //Datapoint value takes to values a Datetime value and decimal number value dp.Neum = neumonic; //Data point takes a neumonic value as well as the parent series id, same as neumonic value unless datapoint has a parent dp.ParentSeriesId = seriesId; dataPointList.AddPoint(dp); //Data point is added to the datapoint list } if (numLoops == 1) //if on second colummn of crime there is a given rate of crime per a 100k people { neumonic = SeriesToNeumonic(neumonic + "(RatePer100k)"); seriesId = _nk.GetValue(neumonic); _nk.AddSeriesName(seriesId, region.RegionName); } } BGTableInfo tableHere = new BGTableInfo(); //BGTableInfo contains relevant information for adding prs into the database at a later date tableHere.TableName = region.RegionName; prs.TableInfos.Add(tableHere); BGTableLineInformation tableLineInfo = new BGTableLineInformation(); tableLineInfo.linelabel = region.RegionName; tableLineInfo.tablelineindents = 0; tableLineInfo.tablelinenum = 1; tableLineInfo.objectID = seriesId; tableHere.Add(tableLineInfo); } prs.DataPoints.AddList(dataPointList); //prs adds the data point list that has accumulated all of the data points for the per one region prsList.Add(prs); // prs for one region is added prsList, prsList is what is returned } return(prsList); }
// IBroker.GetEquityOrderBook public BrokerErrorCode GetEquityOrderBookToday(bool newOrdersOnly, bool bOnlyOutstandingOrders, string stockCode, out Dictionary <string, EquityOrderBookRecord> orders) { orders = new Dictionary <string, EquityOrderBookRecord>(); // Login If needed BrokerErrorCode errorCode = CheckAndLogInIfNeeded(false); if (errorCode != BrokerErrorCode.Success) { return(errorCode); } string postData = "pgname=eqOrdBook&ismethodcall=0&mthname="; string orderBookData = IciciGetWebPageResponse(URL_ICICI_EQT_ORDERBOOK, postData, URL_ICICI_REFERRER, mCookieContainer, out errorCode); if (errorCode.Equals(BrokerErrorCode.Success) && !orderBookData.Contains("No matching Record")) { // Trim it orderBookData = orderBookData.Trim(' ', '\t', '\r', '\n'); orderBookData = HtmlUtilities.EnsureHtmlParsable(orderBookData); string subOrderBookData = StringParser.GetStringBetween(orderBookData, 0, "<thead>", "</table>", new string[] { }); ParsedTable table = (ParsedTable)HtmlTableParser.ParseHtmlIntoTables("<table>" + subOrderBookData + "</table>", true); //orders = new Dictionary<string, EquityOrderBookRecord>(); for (int i = 1; i < table.RowCount; i++) { EquityOrderBookRecord info = new EquityOrderBookRecord(); info.StockCode = table[i, 3].ToString().Trim(); info.StockCode = StringParser.GetStringBetween(info.StockCode, 0, "GetQuote('", "'", null); // If stockCode parameter is empty/null i.e. all stocks are intended // and if stock specific orders are intended and current element's stockCode doesnt match then iterate to next element if (!string.IsNullOrEmpty(stockCode) && !info.StockCode.Equals(stockCode, StringComparison.OrdinalIgnoreCase)) { continue; } var temp = StringParser.GetStringBetween(table[i, 0].ToString(), 0, "nowrap;\">", "<", null); DateTime.TryParse(temp, out info.Date); string orderRefString = table[i, 14].ToString(); orderRefString = StringParser.GetStringBetween(orderRefString, 0, "FML_ORD_ORDR_RFRNC=", "&", null); // Trim it orderRefString = orderRefString.Trim();//(' ', '\t', '\r', '\n'); info.OrderRefenceNumber = orderRefString; string tempStr = table[i, 4].ToString().ToUpperInvariant(); info.Direction = (OrderDirection)Enum.Parse(typeof(OrderDirection), tempStr); info.Quantity = int.Parse(table[i, 5].ToString()); string price = StringParser.GetStringBetween(table[i, 3].ToString(), 0, ">", "<", new string[] { "onclick", "font" }); /* * string price = StringParser.GetStringBetween(table[i, 3].ToString(), * 0, * "<font color=\"blue\">", * "</font>", * new string[] { }); * * if (String.IsNullOrEmpty(price)) * { * price = StringParser.GetStringBetween(table[i, 6].ToString(), * 0, * "<font color=\"black\">", * "</font>", * new string[] { }); * } * * if (String.IsNullOrEmpty(price)) * { * price = StringParser.GetStringBetween(table[i, 6].ToString(), * 0, * "\">", * "</a>", * new string[] { "onclick"}); * } */ info.Price = double.Parse(price); tempStr = table[i, 6].ToString().ToUpperInvariant(); // Executed orders have different string format, so ignore the clutter if (tempStr.Contains("EXECUTED")) { tempStr = "EXECUTED"; } else { tempStr = tempStr.Remove(tempStr.IndexOf("&")); } info.Status = (OrderStatus)Enum.Parse(typeof(OrderStatus), tempStr); info.OpenQty = int.Parse(table[i, 8].ToString()); info.ExecutedQty = int.Parse(table[i, 9].ToString()); // Only add valid outstanding orders if bOnlyOutstandingOrders is true // PARTEXEC is considered OUTSTANDING (i.e. not considered EXEC until fully executed) if (!bOnlyOutstandingOrders || info.Status == OrderStatus.PARTEXEC || info.Status == OrderStatus.QUEUED || info.Status == OrderStatus.REQUESTED || info.Status == OrderStatus.ORDERED) { lock (lockObjectEquity) { if (mEquityOrderBook.ContainsKey(orderRefString)) { if (newOrdersOnly) { } else { orders.Add(orderRefString, info); } // Update the order mEquityOrderBook[orderRefString] = info; } else { mEquityOrderBook.Add(orderRefString, info); orders.Add(orderRefString, info); } } } } } return(errorCode); }
// IBroker.GetEquityTradeBook // Get trade book for a date range and for specific stock if stockCode is valid // else gets complete trade book fro the date range public BrokerErrorCode GetEquityTradeBookToday(bool newTradesOnly, string stockCode, out Dictionary <string, EquityTradeBookRecord> trades) { trades = new Dictionary <string, EquityTradeBookRecord>(); // Login If needed BrokerErrorCode errorCode = CheckAndLogInIfNeeded(false); if (errorCode != BrokerErrorCode.Success) { return(errorCode); } string postData = "pgname=eqTrdBook&ismethodcall=0&mthname="; string tradeBookData = IciciGetWebPageResponse(URL_ICICI_EQT_TRADEBOOK, postData, URL_ICICI_REFERRER, mCookieContainer, out errorCode); if (errorCode.Equals(BrokerErrorCode.Success) && !tradeBookData.Contains("No matching Trades")) { // Trim it tradeBookData = tradeBookData.Trim(' ', '\t', '\r', '\n'); tradeBookData = HtmlUtilities.EnsureHtmlParsable(tradeBookData); string subTradeBookData = StringParser.GetStringBetween(tradeBookData, 0, "<thead>", "</table>", new string[] { }); ParsedTable table = (ParsedTable)HtmlTableParser.ParseHtmlIntoTables("<table>" + subTradeBookData + "</table>", true); //trades = new Dictionary<string, EquityTradeBookRecord>(); for (int i = 1; i < table.RowCount - 1; i++) { EquityTradeBookRecord info = new EquityTradeBookRecord(); info.StockCode = table[i, 1].ToString().Trim(); // If stockCode parameter is not null i.e. all stocks are intended // and if stock specific orders are intended and current element's stockCode doesnt match then iterate to next element if (!string.IsNullOrEmpty(stockCode) && !info.StockCode.Equals(stockCode, StringComparison.OrdinalIgnoreCase)) { continue; } var temp = StringParser.GetStringBetween(table[i, 0].ToString(), 0, "nowrap;\">", "<", null); DateTime.TryParse(temp, out info.Date); //if (bSuccess) //{ // info[0].UpdateTime = result; //} string tempStr = table[i, 2].ToString().ToUpperInvariant().StartsWith("B") ? "BUY" : "SELL"; info.Direction = (OrderDirection)Enum.Parse(typeof(OrderDirection), tempStr); info.Quantity = int.Parse(table[i, 3].ToString()); info.Price = double.Parse(table[i, 4].ToString()); info.TradeValue = double.Parse(table[i, 5].ToString()); //tempStr = StringParser.GetStringBetween(table[i, 6].ToString(), 0, "\">", "</a>", null); info.Brokerage = double.Parse(table[i, 6].ToString()); info.SettlementNumber = StringParser.GetStringBetween(table[i, 7].ToString(), 0, "setno=", "&", null); string orderRefString = StringParser.GetStringBetween(table[i, 10].ToString(), 0, "FML_ORD_ORDR_RFRNC=", "&", null); info.OrderRefenceNumber = orderRefString.Trim(); lock (lockObjectEquity) { if (mEquityTradeBook.ContainsKey(info.OrderRefenceNumber)) { if (newTradesOnly) { } else { trades.Add(info.OrderRefenceNumber, info); } // Update the trade // update required because PartExec may have become full exec mEquityTradeBook[info.OrderRefenceNumber] = info; } else { mEquityTradeBook.Add(info.OrderRefenceNumber, info); trades.Add(info.OrderRefenceNumber, info); } } } } return(errorCode); }
public static ParsedField FindFieldByName(string name, ParsedTable table) { return(table.Fields.Find(field => field.Name.EqualsCi(name))); }
private ParsingReturnStructure ReadData(ParsedTable pt, string fileId) { int firstDateRow = 0; PeriodTypes2017 frequency = PeriodTypes2017.Monthly; string seriesName = ""; var increment = 1; //get high level info from the first few rows for (int row = 0; row <= pt.Vals.GetUpperBound(0); row++) { var cell = pt.Vals[row, 0].ToString(); if (cell.Contains(fileId)) { seriesName = pt.Vals[row, 1].ToString().Replace(",", " "); //TODO - probably need to shorten this } if (cell.Contains("Frequency")) { if (cell.Contains("Monthly")) { frequency = PeriodTypes2017.Monthly; } else if (cell.Contains("Quarterly")) { frequency = PeriodTypes2017.Quarterly; } else if (cell.Contains("Weekly")) { frequency = GetDailyFrequency(pt.Vals[row + 2, 0]); //actual dates start two rows down ; } else if (cell.Contains("Daily")) { frequency = GetDailyFrequency(pt.Vals[row + 2, 0]); //actual dates start two rows down increment = 5; } else { //TODO - handle more cases Debug.Assert(false, "unknown frequency"); } } DateTime dateResult; if (DateTime.TryParse(pt.Vals[row, 0], out dateResult)) { firstDateRow = row; break; } } var neumonic = SeriesToNeumonic(seriesName); seriesName = neumonic; Guid seriesId = _nk.GetValue(neumonic); _nk.AddSeriesName(seriesId, seriesName); var dataPointList = new DataPointList(); for (int row = firstDateRow; row <= pt.Vals.GetUpperBound(0); row += increment) { var dp = new DataPoint2017(DateTime.Parse(pt.Vals[row, 0]), frequency, decimal.Parse(pt.Vals[row, 1])); dp.Neum = neumonic; dp.ParentSeriesId = seriesId; dataPointList.AddPoint(dp); } var prs = new ParsingReturnStructure(); prs.DataPoints = dataPointList; BGTableInfo bgTableInfo = new BGTableInfo(); /// bgTableInfo.TableName = seriesName; prs.TableInfos.Add(bgTableInfo); BGTableLineInformation tableLineInfo = new BGTableLineInformation(); tableLineInfo.linelabel = seriesName; tableLineInfo.tablelineindents = 0; tableLineInfo.tablelinenum = 1; tableLineInfo.objectID = seriesId; bgTableInfo.Add(tableLineInfo); return(prs); }
////////////////////////////////////////// ////// GET EQUITY QUOTE ////// //////////////////////////////////////// // NOTE: We dont want to use IciciGetWebPageResponse here since it updates the login refresh time // whereas getting equity quote doesnt actually refresh contact time with server // it doesnt even need us to be logged in public BrokerErrorCode GetEquityQuote(string stockCode, out EquitySymbolQuote[] info) { BrokerErrorCode errorCode = BrokerErrorCode.Success; info = new EquitySymbolQuote[2]; string quoteData = null; int retryCount = 0; do { quoteData = HttpHelper.GetWebPageResponse( URL_ICICI_EQT_QUOTE + stockCode.ToUpper(), null, null, mCookieContainer); retryCount++; } while (quoteData == null && retryCount < 5); if (string.IsNullOrEmpty(quoteData) || quoteData.IndexOf("entered is not valid") > -1) { // web problems, slow connection, server down etc. return(BrokerErrorCode.NullResponse); } quoteData = quoteData.Substring(quoteData.IndexOf("Best 5 Bids/Offers", 0)); string subQuoteData = StringParser.GetStringBetween(quoteData, 0, "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" class=\"smallfont1\">", "</table>", new string[] { }); ParsedTable table = (ParsedTable)HtmlTableParser.ParseHtmlIntoTables("<table>" + subQuoteData + "</table>", true); DateTime result = DateTime.Now; bool bSuccess = false; // NSE price info info[0] = new EquitySymbolQuote(); info[0].StockCode = stockCode; info[0].Exchange = Exchange.NSE; bSuccess = DateTime.TryParse(table[1, 4] + " " + table[2, 4], out result); if (bSuccess) { info[0].QuoteTime = result; } info[0].LastTradePrice = table[1, 1].ToString().Trim(); info[0].OpenPrice = table[3, 1].ToString().Trim(); info[0].HighPrice = table[4, 1].ToString().Trim(); info[0].LowPrice = table[5, 1].ToString().Trim(); info[0].PreviousClosePrice = table[6, 1].ToString().Trim(); info[0].PercentageChange = table[8, 1].ToString().Trim(); info[0].VolumeTraded = table[11, 1].ToString().Trim(); info[0].BestBidPrice = table[3, 4].ToString().Trim(); info[0].BestOfferPrice = table[4, 4].ToString().Trim(); info[0].BestBidQty = table[5, 4].ToString().Trim(); info[0].BestOfferQty = table[6, 4].ToString().Trim(); info[0].Price52WkHigh = table[7, 4].ToString().Trim(); info[0].Price52WkLow = table[8, 4].ToString().Trim(); // BSE price info info[1] = new EquitySymbolQuote(); info[1].StockCode = stockCode; info[1].Exchange = Exchange.BSE; bSuccess = DateTime.TryParse(table[1, 5] + " " + table[2, 5], out result); if (bSuccess) { info[1].QuoteTime = result; } info[1].LastTradePrice = table[1, 2].ToString(); info[1].OpenPrice = table[3, 2].ToString(); info[1].HighPrice = table[4, 2].ToString(); info[1].LowPrice = table[5, 2].ToString(); info[1].PreviousClosePrice = table[6, 2].ToString(); info[1].PercentageChange = table[8, 2].ToString(); info[1].VolumeTraded = table[11, 2].ToString(); info[1].BestBidPrice = table[3, 5].ToString(); info[1].BestOfferPrice = table[4, 5].ToString(); info[1].BestBidQty = table[5, 5].ToString(); info[1].BestOfferQty = table[6, 5].ToString(); info[1].Price52WkHigh = table[7, 5].ToString(); info[1].Price52WkLow = table[8, 5].ToString(); return(errorCode); }