Exemple #1
0
        private DataColumns AddDataColumns(String prefix, String valueName)
        {
            var dataColumns = new DataColumns()
            {
                ValueColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText =
                        prefix + valueName,
                    SortMode =
                        DataGridViewColumnSortMode.
                        Automatic,
                    DefaultCellStyle = { Format = "0.####" },
                },
                StdDevColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = prefix + "StdDev " + valueName,
                    SortMode   =
                        DataGridViewColumnSortMode.Automatic,
                    DefaultCellStyle = { Format = "0.####" },
                },
                StdErrColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = prefix + "StdErr " + valueName,
                    SortMode   =
                        DataGridViewColumnSortMode.Automatic,
                    DefaultCellStyle = { Format = "0.####" },
                },
            };

            dataGridView1.Columns.Add(dataColumns.ValueColumn);
            dataGridView1.Columns.Add(dataColumns.StdDevColumn);
            dataGridView1.Columns.Add(dataColumns.StdErrColumn);
            return(dataColumns);
        }
Exemple #2
0
 internal override void Reinit()
 {
     base.Reinit();
     CalculationGroup.RenewMetadataObject();
     CalculationGroup.Reinit();
     Field = new CalculationGroupAttribute(DataColumns.First());
 }
        private void LoadDataColumns()
        {
            // create data columns by using the headers
            List <string> colHeaders = GetDataColumnHeaders();

            foreach (string colHeader in colHeaders)
            {
                DataColumns.Add(new DataColumn(colHeader));
            }

            // load all datacolumns
            Range usedRange = DataWorksheet.UsedRange;
            bool  first     = true;

            foreach (Range row in usedRange.Rows)
            {
                if (first)
                {
                    first = false;
                    continue;
                }
                LoadDataColumn(row);
            }
            Console.WriteLine("Loaded Data Columns. Found " + DataColumns.Count.ToString() + " columns");
        }
Exemple #4
0
            public DataFrame(IEnumerable <TColumn> columnHeaders, IEnumerable <ImmutableList <double> > dataColumns)
            {
                ColumnHeaders = ImmutableList.ValueOf(columnHeaders);
                DataColumns   = ImmutableList.ValueOf(dataColumns);
                if (ColumnHeaders.Count == 0)
                {
                    throw new ArgumentException(nameof(columnHeaders));
                }
                if (ColumnHeaders.Count != DataColumns.Count)
                {
                    throw new ArgumentException(@"Wrong number of data columns", nameof(dataColumns));
                }
                if (DataColumns.Select(col => col.Count).Distinct().Count() > 1)
                {
                    throw new ArgumentException(@"All data columns must have the same number of rows", nameof(dataColumns));
                }

                for (int iColumn = 0; iColumn < DataColumns.Count; iColumn++)
                {
                    var col = DataColumns[iColumn];
                    for (int iRow = 0; iRow < col.Count; iRow++)
                    {
                        var value = col[iRow];
                        if (double.IsNaN(value) || double.IsInfinity(value))
                        {
                            throw new ArgumentException(string.Format(@"{0} found at row {1} column {2}", value, iRow, iColumn), nameof(dataColumns));
                        }
                    }
                }
            }
        /// <summary>
        /// 获取格式表
        /// DataColumns,则以其为准,否则,以DataColumns为准
        /// </summary>
        /// <returns>格式表</returns>
        public IEnumerable <DataField> GetColumns()
        {
            List <DataField> dataColumns = new List <DataField>();;

            if (DataFetcher.IsNotNull())
            {
                var schemaTable = DataFetcher.Value.GetDataSchema();
                var fields      = DataPublic.GetDataFieldsByTable(schemaTable);
                dataColumns.AddRange(fields);
            }

            if (DataColumns.IsNotNullOrEmpty())
            {
                foreach (DataField dataColumn in DataColumns)
                {
                    var foundColumn = dataColumns.Where(p => p.Name == dataColumn.Name).FirstOrDefault();
                    if (foundColumn != null)
                    {
                        dataColumns.Remove(foundColumn);
                    }
                    dataColumns.Add(dataColumn);
                }
            }
            return(dataColumns);
        }
        public bool Load(string fileName)
        {
            try
            {
                OpenWorkbook(fileName);
                Console.WriteLine("Opened and initialized file : " + fileName);

                LoadRules();
                LoadDataColumns();
                // get the data now so the actual values can be calculated
                foreach (RuleDefinition rule in Rules)
                {
                    List <DataColumn> dataCols = new List <DataColumn>();
                    foreach (string colName in rule.ColumnsToUse)
                    {
                        dataCols.Add(DataColumns.Where(dc => colName == dc.ColumnName).First());
                    }
                    rule.DataColumns = dataCols;
                }

                Console.WriteLine("Loaded excel file : " + fileName);
                return(true);
            } catch (Exception e)
            {
                Console.WriteLine(e.StackTrace);
                return(false);
            } finally
            {
                CloseWorkbook();
            }
        }
 private DataColumns AddDataColumns(String prefix, String valueName)
 {
     var dataColumns = new DataColumns()
                           {
                               ValueColumn = new DataGridViewTextBoxColumn
                                                 {
                                                     HeaderText =
                                                         prefix + valueName,
                                                     SortMode =
                                                         DataGridViewColumnSortMode.
                                                         Automatic,
                                                     DefaultCellStyle = {Format = "0.####"},
                                                 },
                               StdDevColumn= new DataGridViewTextBoxColumn
                               {
                                   HeaderText = prefix + "StdDev " + valueName,
                                   SortMode =
                                       DataGridViewColumnSortMode.Automatic,
                                   DefaultCellStyle = { Format = "0.####" },
                               },
                               StdErrColumn = new DataGridViewTextBoxColumn
                               {
                                   HeaderText = prefix + "StdErr " + valueName,
                                   SortMode =
                                       DataGridViewColumnSortMode.Automatic,
                                   DefaultCellStyle = { Format = "0.####" },
                               },
                           };
     dataGridView1.Columns.Add(dataColumns.ValueColumn);
     dataGridView1.Columns.Add(dataColumns.StdDevColumn);
     dataGridView1.Columns.Add(dataColumns.StdErrColumn);
     return dataColumns;
 }
Exemple #8
0
        public void PopulateData()
        {
            try
            {
                using (var conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (var cmd = new SqlCommand(SQL, conn))
                    {
                        var reader = cmd.ExecuteReader();

                        DataColumns.Clear();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            DataColumns.Add(reader.GetName(i));
                        }

                        OnPropertyChanged("DataColumns");
                        OnPropertyChanged("DataItemCount");
                    }
                }
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        private IEnumerable <IEnumerable <string> > GetColumnsForRule(RuleDefinition rule)
        {
            var results = rule.ColumnsToUse
                          .Select(colName => DataColumns.Find(dc => colName == dc.ColumnName))
                          .Select(dc => dc.ColData);

            return(results as IEnumerable <IEnumerable <string> >);
        }
Exemple #10
0
        /// <summary>
        /// Determines whether [contains] [the specified columns].
        /// </summary>
        /// <param name="columns">The columns.</param>
        /// <param name="name">The name.</param>
        /// <returns>
        /// <c>true</c> if [contains] [the specified columns]; otherwise, <c>false</c>.
        /// </returns>
        private static bool Contains(DataColumns columns, string name)
        {
            for (var i = 0; i < columns.Count; i++)
            {
                if (columns.Item(i).Name.Equals(name))
                {
                    return(true);
                }
            }

            return(false);
        }
Exemple #11
0
 public void GenerateGrid()
 {
     if (Entries.Count != 0)
     {
         var columns = Entries[0]?.Cells;
         for (int i = 0; i < columns.Count; i++)
         {
             Binding            b    = new Binding(string.Format("Cells[{0}].Value", i));
             DataGridTextColumn text = new DataGridTextColumn();
             text.Header  = columns[i].Cond1.ToString();
             text.Binding = b;
             DataColumns.Add(text);
         }
     }
 }
        public void DeleteRow(DataGridViewRow row)
        {
            if (!DataColumns.Contains("ID"))
            {
                return;
            }
            string recordID = row.Cells["ID"].Value.ToString();

            if (MessageBox.Show("确认删除已选的记录吗?", "确认?", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                if (GoalTables.Contains(this.TableName))
                {
                    DBHelper.GetDBHelper().DelDataFromTable(this.TableName + "DataDetail", recordID);
                }
                DBHelper.GetDBHelper().DelDataFromTable(this.TableName, recordID);
                DoSearch();
            }
        }
Exemple #13
0
        protected override void OnDefineColumns()
        {
            GroupColumns.Add(new Column()
            {
                Name = "Table", FieldAccessor = r => r.TableName
            });
            GroupColumns.Add(new Column()
            {
                Name = "Field", FieldAccessor = r => r.FieldName
            });

            DataColumns.Add(new Column()
            {
                Name = "Value", FieldAccessor = r => r.Name
            });

            AggregateColumns.Add(new Column()
            {
                Name = "# Records", FieldAccessor = r => r.RecordCount
            });
        }
        protected override void OnDefineColumns()
        {
            GroupColumns.Add(new Column()
            {
                Name = "Order Year", FieldAccessor = r => r.OrderYear
            });
            GroupColumns.Add(new Column()
            {
                Name = "Order Month", FieldAccessor = r => r.OrderMonth
            });

            DataColumns.Add(new Column()
            {
                Name = "Order #", FieldAccessor = r => r.OrderNumber
            });
            DataColumns.Add(new Column()
            {
                Name = "Order Date", FieldAccessor = r => r.OrderDateTimeUtc, Format = "{0:d}"
            });
            DataColumns.Add(new Column()
            {
                Name = "Status Date", FieldAccessor = r => r.StatusDateTimeUtc, Format = "{0:d}"
            });

            AggregateColumns.Add(new Column()
            {
                Name = "Cash (1000)", FieldAccessor = r => r.Cash_1000, Format = "{0:c}"
            });
            AggregateColumns.Add(new Column()
            {
                Name = "Income Receivable (1100)", FieldAccessor = r => r.IncomeReceivableBalance_1100, Format = "{0:c}"
            });
            AggregateColumns.Add(new Column()
            {
                Name = "Square Fees (5100)", FieldAccessor = r => r.SquareFees_5100, Format = "{0:c}"
            });
        }
Exemple #15
0
 private void SetDataColumnVisibility(DataColumns dataColumns, bool baseVisible)
 {
     dataColumns.ValueColumn.Visible  = baseVisible;
     dataColumns.StdDevColumn.Visible = baseVisible && cbxShowStdDev.Checked;
     dataColumns.StdErrColumn.Visible = baseVisible && cbxShowStdErr.Checked;
 }
Exemple #16
0
        public void rePopCalndar(int roomID)
        {
            // gets the date of 1st Jan 1970 as datetime format
            DateTime earlyDate = Convert.ToDateTime("01/01/1970");

            // clear all rows upon refresh
            calAllBookings.Calendar.Rows.Clear();

            // create an entry in calview on 1st Jan 1970 5 times for each 5 periods
            // this is done to ensure that all 5 period rows are shown at all points
            // (rows must have at least 1 item to show)
            for (int n = 1; n < 6; n++)
            {
                // empty
                WeekPlannerItemCollection ic = new WeekPlannerItemCollection();
                WeekPlannerItem           i  = new WeekPlannerItem();
                i.StartDate = earlyDate;
                i.EndDate   = earlyDate;
                i.Subject   = "-";
                i.BackColor = System.Drawing.Color.YellowGreen;
                i.bookingid = -2;
                i.userid    = -2;
                ic.Add(i);

                // return the bookings from the database into a List with the booking class
                List <booking> bookinglist = db.populateCalendar(roomID, n);

                // for each booking, add it to the calview
                foreach (booking bk in bookinglist)
                {
                    // create new weekplanneritem object
                    WeekPlannerItem item = new WeekPlannerItem();
                    // set the dates
                    item.StartDate = bk.date;
                    item.EndDate   = bk.date;

                    // set the ids
                    item.bookingid = bk.id;
                    item.userid    = bk.UserID;

                    // set the subject as the user's username
                    item.Subject = db.getUsername(item.userid);

                    // if the booking user's ID is the current user
                    if (bk.UserID == session.userID)
                    {
                        // set colour of item to red
                        item.BackColor = System.Drawing.Color.Red;
                    }
                    else
                    {
                        // set colour of item to cadetblue
                        item.BackColor = System.Drawing.Color.CadetBlue;
                    }
                    // add the items to the calview
                    ic.Add(item);
                }

                // add columns to the calview
                DataColumns cr = new DataColumns(calAllBookings.Calendar);
                cr["clmPeriod"].Data.Add(String.Format("Period {0}", n.ToString()));
                calAllBookings.Rows.Add(cr, ic);
            }
        }
Exemple #17
0
        public Boolean RunImport(Options options)
        {
            var importPortfolio = options.ImportPortfolio;
            var retVal          = false;
            var checkStatus     = false;
            var badSubItemType  = false;
            var subItemKeys     = new List <String>();

            try
            {
                if (Properties.Settings.Default.Properties["ImportPortfolio"] != null)
                {
                    importPortfolio = Properties.Settings.Default.ImportPortfolio;
                }

                var goodToGo      = true;
                var xlsxDataTable = new DataTable();
                using (var wb = new XLWorkbook(options.XlsxDataFileName))
                {
                    NLogger.Info("Processing workbook {0}", options.XlsxDataFileName);

                    var wsData = wb.Worksheets.ToList().Find(w => w.Name.IsEqualTo(options.XlsxDataSheetName, true));
                    if (wsData == null)
                    {
                        NLogger.Error("Unable to find a Data worksheet named {0}", options.XlsxDataSheetName);
                        goodToGo = false;
                    }
                    if (goodToGo)
                    {
                        xlsxDataTable = wsData.ToDataTable();
                    }
                }
                if (!goodToGo)
                {
                    return(false);
                }

                var xlsxMapTable = new DataTable();
                using (var wb = new XLWorkbook(options.XlsxMapFileName))
                {
                    var wsMap = wb.Worksheets.ToList().Find(w => w.Name.IsEqualTo(options.XlsxMapSheetName, true));
                    if (wsMap == null)
                    {
                        NLogger.Error("Unable to find a Map worksheet named {0}", options.XlsxMapSheetName);
                        goodToGo = false;
                    }

                    if (goodToGo)
                    {
                        xlsxMapTable = wsMap.ToDataTable();
                    }
                }
                if (!goodToGo)
                {
                    return(false);
                }

                //
                // Datatables are easier to use than worksheets so just convert it to a datatable
                //
                if (!xlsxDataTable.Rows.HasItems())
                {
                    NLogger.Error("Either there was no rows on {0} or we had an error converting the Data worksheet to a datatable.", options.XlsxDataSheetName);
                    return(false);
                }


                if (!xlsxMapTable.Rows.HasItems())
                {
                    NLogger.Error("Either there was no rows on {0} or we had an error converting the Map worksheet to a datatable.", options.XlsxMapSheetName);
                    return(false);
                }

                //
                // Build our map list
                //
                var dataColumns = new DataColumns();
                foreach (DataRow row in xlsxMapTable.Rows)
                {
                    var columnMap = new CategoryMap
                    {
                        ColumnName     = row.Field <String>(0),
                        ColumnLetter   = row.Field <String>(1),
                        CategoryName   = row.Field <String>(2),
                        ColumnFlagType = row.Field <String>(3)
                    };
                    if (columnMap.ColumnName.IsNullOrEmpty())
                    {
                        continue;
                    }
                    if (columnMap.CategoryName.IsTrimEqualTo("status", true))
                    {
                        checkStatus = true;
                    }
                    dataColumns.Add(columnMap);
                }

                //
                // OK lets log into OPPM
                //
                var oppm = new Oppm(options.OppmUser, options.OppmPassword, options.OppmHost, options.UseSsl);
                if (options.UseCert)
                {
                    oppm = new Oppm(options.OppmUser, options.OppmPassword, options.OppmHost, options.UseCert, Certificate);
                }
                var loggedin = oppm.Login();

                if (!loggedin)
                {
                    NLogger.Error("Unable to log into {0} with {1}", options.OppmHost, options.OppmUser);
                    return(false);
                }
                NLogger.Info("Successful login with {0}", options.OppmUser);

                //
                // Check to make sure all the categories exists and we have an item name column
                //
                if (!(dataColumns.ItemNameMapExits || dataColumns.UciMapExits))
                {
                    NLogger.Error("Unable to find a ItemName either by marking the row with a Yes or UCI (blank mapping) in the mapping worksheet: {0}", options.XlsxMapSheetName);
                    return(false);
                }

                //
                // Now check to make sure all the categories exists
                //
                var categoryInfoList    = new List <psPortfoliosCategoryInfo>();
                var valueListDictionary = new Dictionary <String, List <String> >();
                var badCategoryList     = new List <String>();
                foreach (var categoryMap in dataColumns.CategoryMapList)
                {
                    if (categoryMap.IsItemNameMap || categoryMap.CategoryName.IsNullOrEmpty())
                    {
                        continue;                                                                        // A blank mapping uses this column as the name
                    }
                    var portfolioCategoryInfo = oppm.SeCategory.GetCategoryInfo(categoryMap.CategoryName);
                    if (portfolioCategoryInfo != null)
                    {
                        categoryInfoList.Add(portfolioCategoryInfo);
                        if (portfolioCategoryInfo.ValueListName.IsNotNullOrEmpty())
                        {
                            categoryMap.ValueListName = portfolioCategoryInfo.ValueListName;
                            var values = oppm.SeValueList.GetValueListText(categoryMap.ValueListName);
                            if (valueListDictionary.ContainsKey(categoryMap.ValueListName))
                            {
                                continue;
                            }
                            valueListDictionary.Add(categoryMap.ValueListName, values);
                        }
                        continue;
                    }
                    badCategoryList.Add(categoryMap.CategoryName);
                }

                //
                // Now check the values list values
                //
                var valueListCategories = dataColumns.CategoryMapList.FindAll(x => x.ValueListName.IsNotNullOrEmpty());
                var badValuesList       = new List <String>();
                if (valueListCategories.HasItems())
                {
                    foreach (DataRow row in xlsxDataTable.Rows)
                    {
                        foreach (var valueListCategory in valueListCategories)
                        {
                            // Get our category value for a value list category
                            var value = row[valueListCategory.DataColumnNumber].ToString();
                            if (value.IsNullOrEmpty())
                            {
                                continue;
                            }
                            if (!valueListDictionary.ContainsKey(valueListCategory.ValueListName))
                            {
                                continue;                                                                    // should never happen
                            }
                            // Get our value list values
                            var validValueList = valueListDictionary[valueListCategory.ValueListName];
                            if (validValueList.Contains(value))
                            {
                                continue;
                            }
                            var badValueString = String.Format("ValueList {0} on Category {1} does not contain value: {2}", valueListCategory.ValueListName, valueListCategory.CategoryName, value);
                            if (badValuesList.Contains(badValueString))
                            {
                                continue;
                            }
                            badValuesList.Add(badValueString);
                        }
                    }
                }

                //
                // Now check to see if
                //
                if (options.SubItemType.IsNotNullOrEmpty())
                {
                    badSubItemType = !oppm.SeValueList.HasValue("Dynamic List Types", options.SubItemType);
                    subItemKeys    = dataColumns.GetSubItemKeys;
                }

                //
                // See if we have bad values for the value list categories
                //
                var foundError = false;

                //
                // Did we get any bad categories
                //
                if (badCategoryList.HasItems())
                {
                    foreach (var badCategory in badCategoryList)
                    {
                        NLogger.Warn("Category {0} doesn't exists.", badCategory);
                    }
                    NLogger.Warn("Correct category mapping errors in the Map worksheet: {0}", options.XlsxMapSheetName);
                    foundError = true;
                }

                //
                // Did we get any bad values
                //
                if (badValuesList.HasItems())
                {
                    foreach (var value in badValuesList)
                    {
                        NLogger.Warn(value);
                    }
                    NLogger.Warn("Correct value list values in the Data worksheet: {0}", options.XlsxDataSheetName);
                    foundError = true;
                }

                //
                // Bad subitem type list value -- didn't find it in Dynamic List Types
                //
                if (badSubItemType)
                {
                    NLogger.Warn("Unable to find {0} subitem type in Dynamic List Types valuelist", options.SubItemType);
                    foundError = true;
                }

                if (options.SubItemType.IsNotNullOrEmpty() && !subItemKeys.HasItems())
                {
                    NLogger.Warn("SubItem loading required at least one SubItemKey column to be defined on the map sheet for {0}", options.SubItemType);
                    foundError = true;
                }

                //
                // The user needs to fix the errors we found.
                //
                if (foundError)
                {
                    return(false);
                }


                //
                // Look for our import portfolio.  If we don't find it create it.
                //
                var msg = "Found";
                var importPortfolioId = oppm.SeItem.GetItemIdByName(importPortfolio);
                if (importPortfolioId.IsNullOrEmpty())
                {
                    var importPortfolioInfo = new psPortfoliosItemInfo
                    {
                        Name             = importPortfolio,
                        Status           = psOPEN_CLOSED_STATUS.OCSTS_OPEN,
                        PortfolioType    = psPORTFOLIO_TYPE.PTYP_PROJECTS,
                        Description      = "This is the import portfolio that was created by OppmUtility",
                        IsContainerRoot  = true,
                        CalculationLevel = psCALCULATION_LEVEL.CL_NOT_CALCULATED
                    };
                    var psReturnValues = oppm.SePorfolio.AddPortfolio(importPortfolioInfo);

                    if (psReturnValues == wsPortfoliosPortfolio.psRETURN_VALUES.ERR_OK)
                    {
                        importPortfolioId = oppm.SeItem.GetItemIdByName(importPortfolio);
                    }
                    msg = "Created ";
                }
                if (importPortfolioId.IsNullOrEmpty())
                {
                    NLogger.Fatal("Unable to create import portfolio {0}", importPortfolio);
                    return(false);
                }
                NLogger.Info("{0} import porfolio {1}", msg, importPortfolio);

                //
                // OK lets do some work
                //
                var rowCnt         = 1;
                var itemNameUciMap = dataColumns.GetItemNameUciMap;
                var categories     = dataColumns.CategoryMapList.Where(x => x.IsItemNameMap != true && x.CategoryName.IsNotNullOrEmpty()).Select(x => x.CategoryName).ToList();
                NLogger.Trace("itemNameUciMap = {0}", itemNameUciMap);
                foreach (DataRow row in xlsxDataTable.Rows)
                {
                    var itemNameUciValue = row.Field <String>(itemNameUciMap.DataColumnNumber);
                    NLogger.Trace("itemNameUciValue = {0}", itemNameUciValue);
                    var status = wsPortfoliosItem.psOPEN_CLOSED_STATUS.OCSTS_OPEN;

                    var importItemCellInfoList    = new List <Cell.psPortfoliosCellInfo>();
                    var importSubItemCellInfoList = new List <SubItem.psPortfoliosCellInfo>();
                    var subItemID = String.Empty;

                    foreach (var categoryMap in dataColumns.CategoryMapList)
                    {
                        //var columnValue = row.Field<String>(categoryMap.DataColumnNumber);
                        //NLogger.Trace("categoryMap = {0}", categoryMap.ToString());
                        if (categoryMap.IsItemNameMap || categoryMap.UseUciMap)
                        {
                            continue;                                                     //we already got the name
                        }
                        if (categoryMap.ColumnFlagType.IsEqualTo("UseAsSubItemId", true))
                        {
                            subItemID = row.Field <String>(categoryMap.DataColumnNumber);
                            continue;
                        }

                        //
                        // check to see if we need to set the item status.
                        //
                        NLogger.Trace("categoryMap.CategoryName = {0}", categoryMap.CategoryName);
                        if (checkStatus && categoryMap.CategoryName.IsTrimEqualTo("status", true))
                        {
                            var columnStatus = row.Field <String>(categoryMap.DataColumnNumber);
                            if (columnStatus.IsTrimEqualTo("CLOSED", true))
                            {
                                status = wsPortfoliosItem.psOPEN_CLOSED_STATUS.OCSTS_CLOSED;
                            }
                            if (columnStatus.IsTrimEqualTo("CANDIDATE", true))
                            {
                                status = wsPortfoliosItem.psOPEN_CLOSED_STATUS.OCSTS_CANDIDATE;
                            }
                            continue;
                        }

                        //
                        // Find our category create our cellinfo and add it to the cellinfo list depending if item or subitem.
                        //
                        var portfolioCategoryInfo = categoryInfoList.Find(x => x.Name.IsTrimEqualTo(categoryMap.CategoryName, true));
                        if (portfolioCategoryInfo == null)
                        {
                            NLogger.Trace("portfolioCategoryInfo is null");
                            continue;
                        }


                        NLogger.Trace("portfolioCategoryInfo.Name = {0}", portfolioCategoryInfo.Name);
                        //if (portfolioCategoryInfo.ValueListName.IsNotNullOrEmpty() && columnValue.IsNullOrEmpty()) continue;
                        if (options.SubItemType.IsNotNullOrEmpty())
                        {
                            importSubItemCellInfoList.Add(HelperFunctions.BuildSubItemCellInfo(portfolioCategoryInfo, row.Field <String>(categoryMap.DataColumnNumber)));
                            NLogger.Trace("importSubItemCellInfoList = {0}", "Added");
                            continue;
                        }
                        importItemCellInfoList.Add(HelperFunctions.BuildItemCellInfo(portfolioCategoryInfo, row.Field <String>(categoryMap.DataColumnNumber)));
                        NLogger.Trace("importItemCellInfoList = {0}", "Added");
                    }

                    //
                    // Make sure we have a set of subitem keys
                    //

                    /*
                     * if (importItemCellInfoList.HasItems())
                     * {
                     *  var validKeyCount = 0;
                     *  foreach (var subItemKey in subItemKeys)
                     *  {
                     *      var cell = importItemCellInfoList.Find(x => x.CategoryName.IsTrimEqualTo(subItemKey, true));
                     *      if (cell == null || cell.CellDisplayValue.IsNullOrEmpty()) continue;
                     *      validKeyCount++;
                     *  }
                     *  if (!validKeyCount.Equals(subItemKeys.Count))
                     *  {
                     *      NLogger.Warn("Invalid subItem key on line {0} for item {1}", rowCnt++, itemNameUciValue);
                     *  }
                     * }
                     */


                    //
                    // Check to see if we need to add the item.
                    //
                    var msgOne = String.Format("Process row {0} out of {1} ", rowCnt++, xlsxDataTable.Rows.Count);
                    msgOne = String.Format(itemNameUciMap.IsItemNameMap ? "{0} with ItemName {1}" : "{0} with {2} {1}", msgOne, itemNameUciValue, itemNameUciMap.CategoryName);
                    NLogger.Info(msgOne);

                    if (!options.Commit)
                    {
                        continue;
                    }

                    //
                    // OK lets get our item that we want to write this data to.
                    //
                    NLogger.Trace("Check Existance for {0}", itemNameUciValue);
                    wsPortfoliosItem.psPortfoliosItemInfo itemInfo = null;

                    // if we are using item name
                    if (itemNameUciMap.IsItemNameMap)
                    {
                        itemInfo = oppm.SeItem.GetItemInfoByName(itemNameUciValue);
                        if (itemInfo == null)
                        {
                            if (!options.NewItem)
                            {
                                NLogger.Info("Item {0} doesn't exists", itemNameUciValue);
                                continue;
                            }

                            // Create a new item.
                            var itemId = oppm.SeItem.AddNewEx(itemNameUciValue, importPortfolio);
                            itemInfo = oppm.SeItem.GetItemInfo(itemId);
                            NLogger.Info("Added {0} to portfolio {1} with id {2}", itemInfo.Name, importPortfolio, itemInfo.ProSightID);
                        }
                    }

                    // if we are using uci
                    if (itemNameUciMap.UseUciMap)
                    {
                        itemInfo = oppm.SeItem.GetItemInfo(itemNameUciMap.CategoryName, itemNameUciValue);
                        if (itemInfo == null)
                        {
                            NLogger.Info("UCI {0} doesn't exists", itemNameUciValue);
                            continue;
                        }
                    }

                    // We didn't find the item -- bummer!
                    if (itemInfo == null)
                    {
                        NLogger.Warn("Unable to find an item base on either the ItemName or UCI for row {0}", rowCnt - 1);
                        continue;
                    }

                    //
                    // See if the status has changed
                    //
                    if (checkStatus && itemInfo.Status != status)
                    {
                        NLogger.Trace("Checking status of {0}", itemInfo.Name);
                        itemInfo.Status = status;
                        oppm.SeItem.UpdateEx(itemInfo, 32);
                    }

                    //
                    // If subitem type is null then update our import item cells.
                    //
                    if (options.SubItemType.IsNullOrEmpty())
                    {
                        // Process it as an Item
                        NLogger.Trace("Doing Item UpdateMultipleCellsEx");
                        var updateStatusList = oppm.SeCell.UpdateMultipleCellsEx(itemInfo.ProSightID, importItemCellInfoList);

                        NLogger.Info("Update item cells on {0}", itemNameUciValue);
                        if (!updateStatusList.HasItems())
                        {
                            continue;
                        }

                        //
                        // Let see if the update when well
                        //
                        NLogger.Trace("Logging update status");
                        foreach (var portfolioCellUpdateStatus in updateStatusList)
                        {
                            NLogger.Warn("On item {0}, the following cell didn't update {1}.  Error {2}", itemNameUciValue, portfolioCellUpdateStatus.CategoryName, portfolioCellUpdateStatus.ErrorText);
                        }

                        //
                        // Do the next item cell import
                        //
                        continue;
                    }

                    //
                    // Process subitem cells based on the subItemKeys
                    //
                    NLogger.Trace("Doing subitem SyncSubItemsAsOfToday");

                    //
                    // Get our subitems base on the type from our current item.
                    //
                    var asOf         = DateTime.Now;
                    var subItemInfos = oppm.SeSubItem.GetSubItemListAsOf(String.Empty, itemInfo.ProSightID.ToString(CultureInfo.InvariantCulture), options.SubItemType, 0, categories, false, DateTime.Now);
                    NLogger.Info("Updating {0} subItemInfos on {1}", subItemInfos.Count, itemInfo.Name);


                    //
                    // Check for an existing subitme based on the subitems keys
                    //
                    var doSubItemSync = true;
                    SubItem.psPortfoliosSubItemInfo foundKeyedSubItem = null;
                    var subItemUciList = new List <String>(subItemInfos.Select(x => x.SubItemUCI));

                    if (subItemID.IsNotNullOrEmpty())
                    {
                        NLogger.Trace("Doing UseAsSubItemID Sync");

                        doSubItemSync = false;
                        var subItemInfo = subItemInfos.Find(x => x.SubItemUCI.IsTrimEqualTo(subItemID, true));
                        if (subItemInfo != null)
                        {
                            NLogger.Trace("Found subItemInfo {0}", subItemInfo.SubItemUCI);
                            foundKeyedSubItem = subItemInfo;
                        }
                    }
                    else
                    {
                        foreach (var subItemUci in subItemUciList)
                        {
                            var subItemInfo = subItemInfos.Find(x => x.SubItemUCI.IsTrimEqualTo(subItemUci, true));

                            var subItemKeyValueList = new MapList();
                            foreach (var subItemKey in subItemKeys)
                            {
                                var subItemKeyMap = new SubKeyMap {
                                    Category = subItemKey, KeyValue = String.Empty, CellValue = String.Empty
                                };
                                var subItemKeyCategory = subItemInfo.CategoryValues.ToList().Find(x => x.CategoryName.IsTrimEqualTo(subItemKey, true));
                                if (subItemKeyCategory != null)
                                {
                                    subItemKeyMap.KeyValue = subItemKeyCategory.CellDisplayValue.IsNotNullOrEmpty() ? subItemKeyCategory.CellDisplayValue : String.Empty;
                                }

                                var existItemKeyCategory = importSubItemCellInfoList.Find(x => x.CategoryName.IsTrimEqualTo(subItemKey, true));
                                if (existItemKeyCategory != null)
                                {
                                    subItemKeyMap.CellValue = existItemKeyCategory.CellDisplayValue.IsNotNullOrEmpty() ? existItemKeyCategory.CellDisplayValue : String.Empty;
                                }

                                subItemKeyValueList.KeyMaps.Add(subItemKeyMap);
                            }

                            if (!subItemKeyValueList.CheckMap)
                            {
                                continue;
                            }
                            foundKeyedSubItem = subItemInfo;
                            break;
                        }
                    }

                    //
                    // if we didn't find a matching subitem just add a new one
                    //
                    if (foundKeyedSubItem != null)
                    {
                        doSubItemSync = false;
                        foreach (var subItemCell in foundKeyedSubItem.CategoryValues)
                        {
                            var importSubItemCell = importSubItemCellInfoList.Find(x => x.CategoryName.IsTrimEqualTo(subItemCell.CategoryName, true));
                            if (importSubItemCell == null)
                            {
                                continue;
                            }
                            if (!subItemCell.CellDisplayValue.IsNotEqualTo(importSubItemCell.CellDisplayValue, false))
                            {
                                continue;
                            }
                            subItemCell.CellDisplayValue = importSubItemCell.CellDisplayValue;
                            subItemCell.CellAsOf         = DateTime.Now.ToString("MM/dd/yyyy");
                            doSubItemSync = true;
                        }
                    }
                    else
                    {
                        var newSerialNumber = 1;
                        if (subItemInfos.HasItems())
                        {
                            newSerialNumber = subItemInfos.Max(x => x.SubItemSerial) + 1;
                        }

                        var newSubItem = new SubItem.psPortfoliosSubItemInfo
                        {
                            SubItemName    = Guid.NewGuid().ToString(),
                            SubItemSerial  = newSerialNumber,
                            CategoryValues = importSubItemCellInfoList.ToArray()
                        };
                        subItemInfos.Add(newSubItem);
                    }

                    //
                    // Sync the subitems as of today
                    //
                    if (!doSubItemSync)
                    {
                        NLogger.Trace("Update item cells on {0}", itemNameUciValue);
                        continue;
                    }
                    var subItemUpdateStatuses = oppm.SeSubItem.SyncSubItemsAsOf(itemInfo.ProSightID, options.SubItemType, 0, subItemInfos, asOf, false);

                    NLogger.Info("Update item cells on {0}", itemNameUciValue);
                    if (!subItemUpdateStatuses.HasItems())
                    {
                        continue;
                    }

                    //
                    // Let see if the subitem sync when well
                    //
                    NLogger.Trace("Logging subItem update status");
                    foreach (var subItemUpdateStatus in subItemUpdateStatuses)
                    {
                        NLogger.Warn("On item {0}, the following subItem {1}.  Error {2}", itemNameUciValue, subItemUpdateStatus.SubItemSerial, subItemUpdateStatus.ErrorText);
                    }
                }
                retVal = true;
            }
            catch (Exception ex)
            {
                NLogger.Fatal(ex.Message);
            }
            return(retVal);
        }
Exemple #18
0
        /// <summary>
        /// Fills the schema of the table consulting the schema of the sharepoint list.
        /// </summary>
        /// <param name="table">the DataTable object to change</param>
        /// <param name="connection">an open Sharepoint connection</param>
        protected void FillSchemaFromSharepoint(DataTable table, SpConnection connection)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }

            ViewDef viewDef = null;
            ListDef listDef = null;

            if (this.ViewName != null)
            {
                viewDef = connection.GetViewSchema(this.ListName, this.ViewName);
                listDef = viewDef.ListDef;
            }
            else
            {
                listDef = connection.GetListSchema(this.ListName);
            }

            IList <DataColumn> primaryColumns = new List <DataColumn>();

            foreach (Field field in listDef.Fields)
            {
                DataColumn column;

                if (viewDef != null &&
                    viewDef.ViewFields.Count > 0 &&
                    !ContainsFieldRefWithName(viewDef.ViewFields, field.Name))
                {
                    continue;
                }

                if (!table.Columns.Contains(field.Name))
                {
                    column = new DataColumn();
                    table.Columns.Add(column);
                }
                else
                {
                    column = table.Columns[field.Name];
                }

                MapFromFieldToColumn(column, field);

                // sharepoint returns as primary key the ID
                // which is relevant to sharepoint list.
                if (field.IsPrimaryKey)
                {
                    primaryColumns.Add(column);
                }
            }

            bool primaryRemoved = false;

            if (DataColumns.Count > 0)
            {
                List <string> columnsToRemove = new List <string>();
                foreach (DataColumn tableColumn in table.Columns)
                {
                    if (!DataColumns.Contains(tableColumn.ColumnName))
                    {
                        columnsToRemove.Add(tableColumn.ColumnName);

                        if (primaryColumns.Contains(tableColumn))
                        {
                            primaryRemoved = true;
                        }
                    }
                }

                foreach (string tableColumn2 in columnsToRemove)
                {
                    table.Columns.Remove(tableColumn2);
                }
            }

            if (primaryColumns.Count > 0 && !primaryRemoved)
            {
                DataColumn[] primaryKey = new DataColumn[primaryColumns.Count];
                primaryColumns.CopyTo(primaryKey, 0);
                table.PrimaryKey = primaryKey;
            }

            table.TableName = this.TableName;
        }
Exemple #19
0
 public DataFrame ReorderRows(IList <int> newOrdering)
 {
     return(new DataFrame(ColumnHeaders, DataColumns.Select(col => ImmutableList.ValueOf(Reorder(col, newOrdering)))));
 }
Exemple #20
0
 public IDataColumn? this[string name] => DataColumns.SingleOrDefault(c => c.Name == name);
Exemple #21
0
 public bool HasColumn(string name) => DataColumns.Count(c => c.Name == name) == 1;
Exemple #22
0
 private void SetColumnValues(DataColumns dataColumns, DataGridViewRow row, Statistics stats)
 {
     row.Cells[dataColumns.ValueColumn.Index].Value  = stats.Length > 0 ? stats.Mean() : (double?)null;
     row.Cells[dataColumns.StdDevColumn.Index].Value = stats.Length > 1 ? stats.StdDev() : (double?)null;
     row.Cells[dataColumns.StdErrColumn.Index].Value = stats.Length > 1 ? stats.StdErr() : (double?)null;
 }
 private void RemoveDataColumns(DataColumns dataColumns)
 {
     dataGridView1.Columns.Remove(dataColumns.ValueColumn);
     dataGridView1.Columns.Remove(dataColumns.StdDevColumn);
     dataGridView1.Columns.Remove(dataColumns.StdErrColumn);
 }
Exemple #24
0
 private void RemoveDataColumns(DataColumns dataColumns)
 {
     dataGridView1.Columns.Remove(dataColumns.ValueColumn);
     dataGridView1.Columns.Remove(dataColumns.StdDevColumn);
     dataGridView1.Columns.Remove(dataColumns.StdErrColumn);
 }
 private void SetColumnValues(DataColumns dataColumns, DataGridViewRow row, Statistics stats)
 {
     row.Cells[dataColumns.ValueColumn.Index].Value = stats.Length > 0 ? stats.Mean() : (double?) null;
     row.Cells[dataColumns.StdDevColumn.Index].Value = stats.Length > 1 ? stats.StdDev() : (double?) null;
     row.Cells[dataColumns.StdErrColumn.Index].Value = stats.Length > 1 ? stats.StdErr() : (double?) null;
 }
 private void SetDataColumnVisibility(DataColumns dataColumns, bool baseVisible)
 {
     dataColumns.ValueColumn.Visible = baseVisible;
     dataColumns.StdDevColumn.Visible = baseVisible && cbxShowStdDev.Checked;
     dataColumns.StdErrColumn.Visible = baseVisible && cbxShowStdErr.Checked;
 }