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);
}
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");
}
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;
}
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> >);
}
/// <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);
}
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();
}
}
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}"
});
}
private void SetDataColumnVisibility(DataColumns dataColumns, bool baseVisible)
{
dataColumns.ValueColumn.Visible = baseVisible;
dataColumns.StdDevColumn.Visible = baseVisible && cbxShowStdDev.Checked;
dataColumns.StdErrColumn.Visible = baseVisible && cbxShowStdErr.Checked;
}
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);
}
}
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);
}
/// <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;
}
public DataFrame ReorderRows(IList <int> newOrdering)
{
return(new DataFrame(ColumnHeaders, DataColumns.Select(col => ImmutableList.ValueOf(Reorder(col, newOrdering)))));
}
public IDataColumn? this[string name] => DataColumns.SingleOrDefault(c => c.Name == name);
public bool HasColumn(string name) => DataColumns.Count(c => c.Name == name) == 1;
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);
}
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;
}
