LIM: Example of Month, Day, or Year Logic in the Register, Spreadsheet, File and Connector

Here is sample code referenced above:

Import Register Connector

public object ImportRegisterData(SessionInfo si, BRGlobals globals, object api, DashboardExtenderArgs args)
		{
		    try
		    {
				// Run the connector to build the DataTable
        		var dt = RunPLPConnector(si, (object)api);
		
		        // Passing the DataTable to the planning controller will import the data into the table
		        var result = _plnController.ImportRegisterDataViaRegisterConnector(args, dt);
				
				// Customizations to XFW_PLN_StageRegister
        		populatePeriods(si, globals, args);
				
		        return result;
		    }
		    catch (Exception ex)
		    {
		        throw ErrorHandler.LogWrite(si, new XFException(si, ex));
		    }
		}

		private DataTable RunPLPConnector(SessionInfo si, object api)
		{
		    try
		    {
		        string sql = $@"
							SELECT {string.Join(", ", GetFieldNames())}
		   					FROM dbo.vStageSourceAndAttributeData
							WHERE Si = 'lim_PLP_test'
		     				";
		
		        DataTable initialData;
		        using (var dbConn = BRApi.Database.CreateApplicationDbConnInfo(si))
		        {
		            initialData = BRApi.Database.ExecuteSql(dbConn, sql, true);
		        }
		
		        // Transform initial data table into register format
		        var transformData = RunConnectorProcess(si, initialData, api);
		        return transformData;
		    }
		    catch (Exception ex)
		    {
		        throw ErrorHandler.LogWrite(si, new XFException(si, ex));
		    }
		}

		private List<string> GetFieldNames() => new()
		{
		    "Si","Lb","Et","U1","A1","A2","A3","A4","A5","A6","A7","A8","Am"
		};

		private DataTable RunConnectorProcess(SessionInfo si, DataTable dt, object api)
		{
		    try
		    {
		        using var dbConn = BRApi.Database.CreateApplicationDbConnInfo(si);
				string dataTableName = "plnb2.XFW_PLN_Register";
		        var plpTable = BRApi.Database.ExecuteSql(dbConn, $"SELECT TOP 1 * FROM {dataTableName}", true);
		        plpTable.Clear();
		
		        foreach (DataRow stageRow in dt.Rows)
		        {
		            var row = plpTable.NewRow();
		
		            // Mapping of target (Register Field Name) = source system's field name (should match what was defined in GetFieldNames)
		            row["EmployeeID"] = stageRow["Lb"];
		            row["Instance"] = stageRow["A1"];
		            row["LastName"] = stageRow["A2"];
		            row["FirstName"] = stageRow["A3"];
		            row["Status"] = stageRow["A4"];
		            row["HireDate"] = stageRow["A5"];
		            row["TermDate"] = stageRow["A6"];
		            row["Salary"] = stageRow["Am"];
		            row["Entity"] = stageRow["Et"];
		            row["CostCenter"] = stageRow["U1"];
		            row["JobCode"] = stageRow["A7"];
		            row["FTE"] = stageRow["A8"];
		
		            plpTable.Rows.Add(row);
		        }
		        return plpTable;
		    }
		    catch (Exception ex)
		    {
		        throw new XFUserMsgException(si, new Exception("Exception in Running Connector: " + ex.Message));
		    }
		}
		
		private static void populatePeriods(SessionInfo si, BRGlobals brGlobals, DashboardExtenderArgs args)
		{
		    var sql = new StringBuilder();
		    sql.Append(@$"
		        UPDATE [plnb2].XFW_PLN_StageRegister
		        SET 
		            HirePeriod = MONTH(HireDate),
		            HireYear = YEAR(HireDate),
		            TermPeriod = MONTH(TermDate),
		            TermYear = YEAR(TermDate)
		    ");
		
		    BRApi.Database.ExecuteSql(BRApi.Database.CreateApplicationDbConnInfo(si), sql.ToString(), false);

WsCompenentService:

#region "AfterEvent Specific Logics"		
		private static void populatePeriods(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult)
        {
			var sql = new StringBuilder();
				sql.Append(@$"
					UPDATE [plnb2].XFW_PLN_StageRegister
					SET 
						HirePeriod = MONTH(HireDate),
						HireYear = Year(HireDate),
						TermPeriod = MONTH(TermDate),
						TermYear = Year(TermDate)
				");
				BRApi.Database.ExecuteSql(BRApi.Database.CreateApplicationDbConnInfo(si), sql.ToString(), false);
        }
#endregion

WsDynamicGridService:

#region	"BeforeEvent - populatePeriods"
	private static void populatePeriods(SessionInfo si, DashboardDynamicGridArgs args) //Had to add in DashboardDynamicGridArgs args
		{
            if (args.SaveDataArgs != null)
			{
				foreach (XFEditedDataRow row in args.SaveDataArgs.EditedDataRows)
				{
					//if (row.InsertUpdateOrDelete == DbInsUpdateDelType.Update)
					if (row.InsertUpdateOrDelete != DbInsUpdateDelType.Insert && row.InsertUpdateOrDelete != DbInsUpdateDelType.Update) continue;
					{
						bool hireDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("HireDate") ? row.ModifiedDataRow.Items["HireDate"]?.ToString() : null, out DateTime hireDate);
						bool termDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("TermDate") ? row.ModifiedDataRow.Items["TermDate"]?.ToString() : null, out DateTime termDate);
						int hirePeriodInt = 0;
						int hireYearInt = 0;
						int termPeriodInt = 0;
						int termYearInt = 0;
						
						//if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert
						if (hireDateParsed)
						{
							hirePeriodInt = hireDate.Month; // month 
							hireYearInt = hireDate.Year; // year
							
							row.ModifiedDataRow.Items["HirePeriod"] = hirePeriodInt;
							row.ModifiedDataRow.Items["HireYear"] = hireYearInt;
						}
						
						if (termDateParsed)
						{
							termPeriodInt = termDate.Month; // month 
							termYearInt = termDate.Year; // year
							
							row.ModifiedDataRow.Items["TermPeriod"] = termPeriodInt;
							row.ModifiedDataRow.Items["TermYear"] = termYearInt;
						}
					}
				}
			}
        }
#endregion

#region "BeforeEvent - populatePeriods"
		private static void populatePeriods(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, TableView tableView)
		{
			foreach (TableViewRow tableViewRow in tableView.Rows)
			{
				var employeeId = tableViewRow.Items["EmployeeID"]?.OriginalValue?.ToString();
				
				if (tableViewRow.IsHeader) continue; // Skip header row
				if (string.IsNullOrWhiteSpace(employeeId)) continue; // Skip blank or whitespace-only rows

				bool hireDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("HireDate") ? 
										tableViewRow.Items["HireDate"]?.Value?.ToString() : null, out DateTime hireDate);
				bool termDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("TermDate") ? 
										tableViewRow.Items["TermDate"]?.Value?.ToString() : null, out DateTime termDate);
				int hirePeriodInt = 0;
				int hireYearInt = 0;
				int termPeriodInt = 0;
				int termYearInt = 0;
				bool hireDateupdate = tableViewRow.Items["HireDate"].IsDirty();
				bool termDateupdate = tableViewRow.Items["TermDate"].IsDirty();
				
				//if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert
				if (hireDateupdate && hireDateParsed)
				{
					hirePeriodInt = hireDate.Month; // month 
					hireYearInt = hireDate.Year; // year
					
					tableViewRow.Items["HirePeriod"].Value = hirePeriodInt.ToString();
					tableViewRow.Items["HireYear"].Value = hireYearInt.ToString();
				}
						
				if (termDateupdate && termDateParsed)
				{
					termPeriodInt = termDate.Month; // month 
					termYearInt = termDate.Year; // year
					
					tableViewRow.Items["TermPeriod"].Value = termPeriodInt.ToString();
					tableViewRow.Items["TermYear"].Value = termYearInt.ToString();
				}
			}
		}
#endregion