/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <param name="ARow"></param>
private void ShowDetailsManual(SUserRow ARow)
{
CheckForCurrentUser(ARow);
string currentPermissions = String.Empty;
if (ARow != null)
{
FMainDS.SUserModuleAccessPermission.DefaultView.RowFilter =
String.Format("{0}='{1}'",
SUserModuleAccessPermissionTable.GetUserIdDBName(),
ARow.UserId);
foreach (DataRowView rv in FMainDS.SUserModuleAccessPermission.DefaultView)
{
SUserModuleAccessPermissionRow permission = (SUserModuleAccessPermissionRow)rv.Row;
if (permission.CanAccess)
{
currentPermissions = StringHelper.AddCSV(currentPermissions, permission.ModuleId);
}
}
// If a password has been saved for a user it can be changed using btnResetPassword.
// If a password has not been saved then it can be added using txtDetailPasswordHash.
if (string.IsNullOrEmpty(ARow.PasswordHash) || (string.IsNullOrEmpty(ARow.PasswordSalt) && (ARow.RowState != DataRowState.Unchanged)))
{
btnResetPassword.Enabled = false;
txtDetailPasswordHash.Enabled = true;
if (FNewRecordBeingAdded)
{
FNewRecordBeingAdded = false;
FTipPwd.Show(Catalog.GetString(
"A 'random, secure' password got assigned.\r\nCopy it with the provided hyperlink!"),
txtDetailPasswordHash, 0, 25, 5000);
}
else
{
FTipPwd.Hide(txtDetailPasswordHash);
}
llbCopyPassword.Visible = true;
}
else
{
btnResetPassword.Enabled = true;
txtDetailPasswordHash.Enabled = false;
llbCopyPassword.Visible = false;
FTipPwd.Hide(txtDetailPasswordHash);
}
}
clbUserGroup.SetCheckedStringList(currentPermissions);
clbUserGroup.AutoResizeGrid();
}
private void GetDetailDataFromControlsManual(SUserRow ARow)
{
ARow.UserId = ARow.UserId.ToUpperInvariant();
FMainDS.SUserModuleAccessPermission.DefaultView.RowFilter =
String.Format("{0}='{1}'",
SUserModuleAccessPermissionTable.GetUserIdDBName(),
ARow.UserId);
string currentPermissions = clbUserGroup.GetCheckedStringList();
StringCollection CSVValues = StringHelper.StrSplit(currentPermissions, ",");
foreach (DataRowView rv in FMainDS.SUserModuleAccessPermission.DefaultView)
{
SUserModuleAccessPermissionRow permission = (SUserModuleAccessPermissionRow)rv.Row;
if (permission.CanAccess)
{
if (!CSVValues.Contains(permission.ModuleId))
{
permission.CanAccess = false;
}
else
{
CSVValues.Remove(permission.ModuleId);
}
}
else if (!permission.CanAccess && CSVValues.Contains(permission.ModuleId))
{
permission.CanAccess = true;
CSVValues.Remove(permission.ModuleId);
}
}
// add new permissions
foreach (string module in CSVValues)
{
SUserModuleAccessPermissionRow newRow = FMainDS.SUserModuleAccessPermission.NewRowTyped();
newRow.UserId = ARow.UserId;
newRow.ModuleId = module;
newRow.CanAccess = true;
FMainDS.SUserModuleAccessPermission.Rows.Add(newRow);
}
}
private void ShowDetailsManual(SUserRow ARow)
{
string currentPermissions = String.Empty;
if (ARow != null)
{
FMainDS.SUserModuleAccessPermission.DefaultView.RowFilter =
String.Format("{0}='{1}'",
SUserModuleAccessPermissionTable.GetUserIdDBName(),
ARow.UserId);
foreach (DataRowView rv in FMainDS.SUserModuleAccessPermission.DefaultView)
{
SUserModuleAccessPermissionRow permission = (SUserModuleAccessPermissionRow)rv.Row;
if (permission.CanAccess)
{
currentPermissions = StringHelper.AddCSV(currentPermissions, permission.ModuleId);
}
}
// If a password has been saved for a user it can be changed using btnChangePassword.
// If a password has not been saved then it can be added using txtDetailPasswordHash.
if (string.IsNullOrEmpty(ARow.PasswordHash) || (string.IsNullOrEmpty(ARow.PasswordSalt) && (ARow.RowState != DataRowState.Unchanged)))
{
btnChangePassword.Enabled = false;
txtDetailPasswordHash.Enabled = true;
}
else
{
btnChangePassword.Enabled = true;
txtDetailPasswordHash.Enabled = false;
}
}
clbUserGroup.SetCheckedStringList(currentPermissions);
}
private DataTable GetFoundationOwnerListTable(TDBTransaction AReadTransaction, string ATableName)
{
// Used in Foundation Details screen.
SUserTable TmpUserTable = new SUserTable();
TmpUserTable = (SUserTable)DBAccess.GDBAccessObj.SelectDT(TmpUserTable, "SELECT " + SUserTable.GetPartnerKeyDBName() + ',' +
SUserTable.GetUserIdDBName() + ',' +
SUserTable.GetFirstNameDBName() + ',' +
SUserTable.GetLastNameDBName() + ' ' +
"FROM PUB_" + SUserTable.GetTableDBName() + ' ' +
"WHERE " + SUserTable.GetPartnerKeyDBName() + " <> 0 " +
"AND " + SUserTable.GetUserIdDBName() +
" IN (SELECT " + SUserModuleAccessPermissionTable.GetUserIdDBName() + ' ' +
"FROM PUB_" + SUserModuleAccessPermissionTable.GetTableDBName() + ' ' +
"WHERE " + SUserModuleAccessPermissionTable.GetModuleIdDBName() +
" = 'DEVUSER')" + "AND " + SUserTable.GetRetiredDBName() +
" = FALSE", AReadTransaction, null, -1, -1);
SUserRow EmptyDR = TmpUserTable.NewRowTyped(false);
EmptyDR.PartnerKey = 0;
EmptyDR.UserId = "";
TmpUserTable.Rows.InsertAt(EmptyDR, 0);
return(TmpUserTable);
}
public static bool CreateUser(string AUsername, string APassword, string AFirstName, string AFamilyName, string AModulePermissions)
{
TDBTransaction ReadTransaction = null;
TDBTransaction SubmitChangesTransaction = null;
bool UserExists = false;
bool SubmissionOK = false;
// TODO: check permissions. is the current user allowed to create other users?
SUserTable userTable = new SUserTable();
SUserRow newUser = userTable.NewRowTyped();
newUser.UserId = AUsername;
newUser.FirstName = AFirstName;
newUser.LastName = AFamilyName;
if (AUsername.Contains("@"))
{
newUser.EmailAddress = AUsername;
newUser.UserId = AUsername.Substring(0, AUsername.IndexOf("@")).
Replace(".", string.Empty).
Replace("_", string.Empty).ToUpper();
}
// Check whether the user that we are asked to create already exists
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref ReadTransaction,
delegate
{
if (SUserAccess.Exists(newUser.UserId, ReadTransaction))
{
TLogging.Log("Cannot create new user as a user with User Name '" + newUser.UserId + "' already exists!");
UserExists = true;
}
});
if (UserExists)
{
return(false);
}
userTable.Rows.Add(newUser);
string UserAuthenticationMethod = TAppSettingsManager.GetValue("UserAuthenticationMethod", "OpenPetraDBSUser", false);
if (UserAuthenticationMethod == "OpenPetraDBSUser")
{
if (APassword.Length > 0)
{
newUser.PasswordSalt = PasswordHelper.GetNewPasswordSalt();
newUser.PasswordHash = PasswordHelper.GetPasswordHash(APassword, newUser.PasswordSalt);
newUser.PasswordNeedsChange = true;
}
}
else
{
try
{
IUserAuthentication auth = TUserManagerWebConnector.LoadAuthAssembly(UserAuthenticationMethod);
if (!auth.CreateUser(AUsername, APassword, AFirstName, AFamilyName))
{
newUser = null;
}
}
catch (Exception e)
{
TLogging.Log("Problem loading user authentication method " + UserAuthenticationMethod + ": " + e.ToString());
return(false);
}
}
if (newUser != null)
{
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction, ref SubmissionOK,
delegate
{
SUserAccess.SubmitChanges(userTable, SubmitChangesTransaction);
List <string> modules = new List <string>();
if (AModulePermissions == DEMOMODULEPERMISSIONS)
{
modules.Add("PTNRUSER");
modules.Add("FINANCE-1");
ALedgerTable theLedgers = ALedgerAccess.LoadAll(SubmitChangesTransaction);
foreach (ALedgerRow ledger in theLedgers.Rows)
{
modules.Add("LEDGER" + ledger.LedgerNumber.ToString("0000"));
}
}
else
{
string[] modulePermissions = AModulePermissions.Split(new char[] { ',' });
foreach (string s in modulePermissions)
{
if (s.Trim().Length > 0)
{
modules.Add(s.Trim());
}
}
}
SUserModuleAccessPermissionTable moduleAccessPermissionTable = new SUserModuleAccessPermissionTable();
foreach (string module in modules)
{
SUserModuleAccessPermissionRow moduleAccessPermissionRow = moduleAccessPermissionTable.NewRowTyped();
moduleAccessPermissionRow.UserId = newUser.UserId;
moduleAccessPermissionRow.ModuleId = module;
moduleAccessPermissionRow.CanAccess = true;
moduleAccessPermissionTable.Rows.Add(moduleAccessPermissionRow);
}
SUserModuleAccessPermissionAccess.SubmitChanges(moduleAccessPermissionTable, SubmitChangesTransaction);
// TODO: table permissions should be set by the module list
// TODO: add p_data_label... tables here so user can generally have access
string[] tables = new string[] {
"p_bank", "p_church", "p_family", "p_location",
"p_organisation", "p_partner", "p_partner_location",
"p_partner_type", "p_person", "p_unit", "p_venue",
"p_data_label", "p_data_label_lookup", "p_data_label_lookup_category", "p_data_label_use", "p_data_label_value_partner",
};
SUserTableAccessPermissionTable tableAccessPermissionTable = new SUserTableAccessPermissionTable();
foreach (string table in tables)
{
SUserTableAccessPermissionRow tableAccessPermissionRow = tableAccessPermissionTable.NewRowTyped();
tableAccessPermissionRow.UserId = newUser.UserId;
tableAccessPermissionRow.TableName = table;
tableAccessPermissionTable.Rows.Add(tableAccessPermissionRow);
}
SUserTableAccessPermissionAccess.SubmitChanges(tableAccessPermissionTable, SubmitChangesTransaction);
SubmissionOK = true;
});
return(true);
}
return(false);
}
public static bool CreateUser(string AUsername, string APassword, string AFirstName, string AFamilyName, string AModulePermissions)
{
TDBTransaction ReadTransaction = null;
TDBTransaction SubmitChangesTransaction = null;
bool UserExists = false;
bool SubmissionOK = false;
// TODO: check permissions. is the current user allowed to create other users?
SUserTable userTable = new SUserTable();
SUserRow newUser = userTable.NewRowTyped();
newUser.UserId = AUsername;
newUser.FirstName = AFirstName;
newUser.LastName = AFamilyName;
if (AUsername.Contains("@"))
{
newUser.EmailAddress = AUsername;
newUser.UserId = AUsername.Substring(0, AUsername.IndexOf("@")).
Replace(".", string.Empty).
Replace("_", string.Empty).ToUpper();
}
// Check whether the user that we are asked to create already exists
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref ReadTransaction,
delegate
{
if (SUserAccess.Exists(newUser.UserId, ReadTransaction))
{
TLogging.Log("Cannot create new user as a user with User Name '" + newUser.UserId + "' already exists!");
UserExists = true;
}
});
if (UserExists)
{
return false;
}
userTable.Rows.Add(newUser);
string UserAuthenticationMethod = TAppSettingsManager.GetValue("UserAuthenticationMethod", "OpenPetraDBSUser", false);
if (UserAuthenticationMethod == "OpenPetraDBSUser")
{
if (APassword.Length > 0)
{
newUser.PasswordSalt = PasswordHelper.GetNewPasswordSalt();
newUser.PasswordHash = PasswordHelper.GetPasswordHash(APassword, newUser.PasswordSalt);
newUser.PasswordNeedsChange = true;
}
}
else
{
try
{
IUserAuthentication auth = TUserManagerWebConnector.LoadAuthAssembly(UserAuthenticationMethod);
if (!auth.CreateUser(AUsername, APassword, AFirstName, AFamilyName))
{
newUser = null;
}
}
catch (Exception e)
{
TLogging.Log("Problem loading user authentication method " + UserAuthenticationMethod + ": " + e.ToString());
return false;
}
}
if (newUser != null)
{
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction, ref SubmissionOK,
delegate
{
SUserAccess.SubmitChanges(userTable, SubmitChangesTransaction);
List <string>modules = new List <string>();
if (AModulePermissions == DEMOMODULEPERMISSIONS)
{
modules.Add("PTNRUSER");
modules.Add("FINANCE-1");
ALedgerTable theLedgers = ALedgerAccess.LoadAll(SubmitChangesTransaction);
foreach (ALedgerRow ledger in theLedgers.Rows)
{
modules.Add("LEDGER" + ledger.LedgerNumber.ToString("0000"));
}
}
else
{
string[] modulePermissions = AModulePermissions.Split(new char[] { ',' });
foreach (string s in modulePermissions)
{
if (s.Trim().Length > 0)
{
modules.Add(s.Trim());
}
}
}
SUserModuleAccessPermissionTable moduleAccessPermissionTable = new SUserModuleAccessPermissionTable();
foreach (string module in modules)
{
SUserModuleAccessPermissionRow moduleAccessPermissionRow = moduleAccessPermissionTable.NewRowTyped();
moduleAccessPermissionRow.UserId = newUser.UserId;
moduleAccessPermissionRow.ModuleId = module;
moduleAccessPermissionRow.CanAccess = true;
moduleAccessPermissionTable.Rows.Add(moduleAccessPermissionRow);
}
SUserModuleAccessPermissionAccess.SubmitChanges(moduleAccessPermissionTable, SubmitChangesTransaction);
// TODO: table permissions should be set by the module list
string[] tables = new string[] {
"p_bank", "p_church", "p_family", "p_location",
"p_organisation", "p_partner", "p_partner_location",
"p_partner_type", "p_person", "p_unit", "p_venue"
};
SUserTableAccessPermissionTable tableAccessPermissionTable = new SUserTableAccessPermissionTable();
foreach (string table in tables)
{
SUserTableAccessPermissionRow tableAccessPermissionRow = tableAccessPermissionTable.NewRowTyped();
tableAccessPermissionRow.UserId = newUser.UserId;
tableAccessPermissionRow.TableName = table;
tableAccessPermissionTable.Rows.Add(tableAccessPermissionRow);
}
SUserTableAccessPermissionAccess.SubmitChanges(tableAccessPermissionTable, SubmitChangesTransaction);
SubmissionOK = true;
});
return true;
}
return false;
}
public static bool CreateUser(string AUsername, string APassword, string AFirstName, string AFamilyName,
string AModulePermissions, string AClientComputerName, string AClientIPAddress, TDBTransaction ATransaction = null)
{
TDataBase DBConnectionObj = DBAccess.GetDBAccessObj(ATransaction);
TDBTransaction ReadWriteTransaction = null;
bool SeparateDBConnectionEstablished = false;
bool NewTransaction;
bool SubmissionOK = false;
// TODO: check permissions. is the current user allowed to create other users?
SUserTable userTable = new SUserTable();
SUserRow newUser = userTable.NewRowTyped();
newUser.UserId = AUsername;
newUser.FirstName = AFirstName;
newUser.LastName = AFamilyName;
if (AUsername.Contains("@"))
{
newUser.EmailAddress = AUsername;
newUser.UserId = AUsername.Substring(0, AUsername.IndexOf("@")).
Replace(".", string.Empty).
Replace("_", string.Empty).ToUpper();
}
if (DBConnectionObj == null)
{
// ATransaction was null and GDBAccess is also null: we need to establish a DB Connection manually here!
DBConnectionObj = DBAccess.SimpleEstablishDBConnection("CreateUser");
SeparateDBConnectionEstablished = true;
}
ReadWriteTransaction = DBConnectionObj.GetNewOrExistingTransaction(
IsolationLevel.Serializable, out NewTransaction, "CreateUser");
try
{
// Check whether the user that we are asked to create already exists
if (SUserAccess.Exists(newUser.UserId, ReadWriteTransaction))
{
TLogging.Log("Cannot create new user because a user with User Name '" + newUser.UserId + "' already exists!");
return(false);
}
newUser.PwdSchemeVersion = TPasswordHelper.CurrentPasswordSchemeNumber;
userTable.Rows.Add(newUser);
string UserAuthenticationMethod = TAppSettingsManager.GetValue("UserAuthenticationMethod", "OpenPetraDBSUser", false);
if (UserAuthenticationMethod == "OpenPetraDBSUser")
{
if (APassword.Length > 0)
{
SetNewPasswordHashAndSaltForUser(newUser, APassword, AClientComputerName, AClientIPAddress, ReadWriteTransaction);
if (AModulePermissions != TMaintenanceWebConnector.DEMOMODULEPERMISSIONS)
{
newUser.PasswordNeedsChange = true;
}
}
}
else
{
try
{
IUserAuthentication auth = TUserManagerWebConnector.LoadAuthAssembly(UserAuthenticationMethod);
if (!auth.CreateUser(AUsername, APassword, AFirstName, AFamilyName))
{
newUser = null;
}
}
catch (Exception e)
{
TLogging.Log("Problem loading user authentication method " + UserAuthenticationMethod + ": " + e.ToString());
return(false);
}
}
if (newUser != null)
{
SUserAccess.SubmitChanges(userTable, ReadWriteTransaction);
List <string> modules = new List <string>();
if (AModulePermissions == DEMOMODULEPERMISSIONS)
{
modules.Add("PTNRUSER");
modules.Add("FINANCE-1");
ALedgerTable theLedgers = ALedgerAccess.LoadAll(ReadWriteTransaction);
foreach (ALedgerRow ledger in theLedgers.Rows)
{
modules.Add("LEDGER" + ledger.LedgerNumber.ToString("0000"));
}
}
else
{
string[] modulePermissions = AModulePermissions.Split(new char[] { ',' });
foreach (string s in modulePermissions)
{
if (s.Trim().Length > 0)
{
modules.Add(s.Trim());
}
}
}
SUserModuleAccessPermissionTable moduleAccessPermissionTable = new SUserModuleAccessPermissionTable();
foreach (string module in modules)
{
SUserModuleAccessPermissionRow moduleAccessPermissionRow = moduleAccessPermissionTable.NewRowTyped();
moduleAccessPermissionRow.UserId = newUser.UserId;
moduleAccessPermissionRow.ModuleId = module;
moduleAccessPermissionRow.CanAccess = true;
moduleAccessPermissionTable.Rows.Add(moduleAccessPermissionRow);
}
SUserModuleAccessPermissionAccess.SubmitChanges(moduleAccessPermissionTable, ReadWriteTransaction);
// TODO: table permissions should be set by the module list
// TODO: add p_data_label... tables here so user can generally have access
string[] tables = new string[] {
"p_bank", "p_church", "p_family", "p_location",
"p_organisation", "p_partner", "p_partner_location",
"p_partner_type", "p_person", "p_unit", "p_venue",
"p_data_label", "p_data_label_lookup", "p_data_label_lookup_category", "p_data_label_use", "p_data_label_value_partner",
};
SUserTableAccessPermissionTable tableAccessPermissionTable = new SUserTableAccessPermissionTable();
foreach (string table in tables)
{
SUserTableAccessPermissionRow tableAccessPermissionRow = tableAccessPermissionTable.NewRowTyped();
tableAccessPermissionRow.UserId = newUser.UserId;
tableAccessPermissionRow.TableName = table;
tableAccessPermissionTable.Rows.Add(tableAccessPermissionRow);
}
SUserTableAccessPermissionAccess.SubmitChanges(tableAccessPermissionTable, ReadWriteTransaction);
TUserAccountActivityLog.AddUserAccountActivityLogEntry(newUser.UserId,
TUserAccountActivityLog.USER_ACTIVITY_USER_RECORD_CREATED,
String.Format(Catalog.GetString("The user record for the new user {0} got created by user {1}. "),
newUser.UserId, UserInfo.GUserInfo.UserID) +
String.Format(ResourceTexts.StrRequestCallerInfo, AClientComputerName, AClientIPAddress),
ReadWriteTransaction);
SubmissionOK = true;
return(true);
}
}
finally
{
if (NewTransaction)
{
if (SubmissionOK)
{
ReadWriteTransaction.DataBaseObj.CommitTransaction();
}
else
{
ReadWriteTransaction.DataBaseObj.RollbackTransaction();
}
if (SeparateDBConnectionEstablished)
{
DBConnectionObj.CloseDBConnection();
}
}
}
return(false);
}
private static void GenerateRegistrationOffices(string ACountryName, Int64 APartnerKey, string ACountryCode)
{
if (PUnitAccess.Exists(APartnerKey, null))
{
TLogging.Log("Office with key " + APartnerKey.ToString() + " already exists.");
return;
}
PartnerEditTDS MainDS = new PartnerEditTDS();
PPartnerRow partnerRow = MainDS.PPartner.NewRowTyped(true);
partnerRow.PartnerKey = APartnerKey;
partnerRow.PartnerShortName = ACountryName;
partnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT;
partnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
partnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_ORGANISATION;
MainDS.PPartner.Rows.Add(partnerRow);
PUnitRow unitRow = MainDS.PUnit.NewRowTyped(true);
unitRow.PartnerKey = partnerRow.PartnerKey;
unitRow.UnitName = partnerRow.PartnerShortName;
unitRow.CountryCode = ACountryCode;
unitRow.UnitTypeCode = MPartnerConstants.UNIT_TYPE_FIELD;
MainDS.PUnit.Rows.Add(unitRow);
PLocationRow locationRow = MainDS.PLocation.NewRowTyped();
locationRow.SiteKey = partnerRow.PartnerKey;
locationRow.LocationKey = 0;
locationRow.StreetName = "No valid address on file";
locationRow.CountryCode = ACountryCode;
MainDS.PLocation.Rows.Add(locationRow);
PPartnerLocationRow partnerlocationRow = MainDS.PPartnerLocation.NewRowTyped();
partnerlocationRow.PartnerKey = partnerRow.PartnerKey;
partnerlocationRow.SiteKey = locationRow.SiteKey;
partnerlocationRow.LocationKey = locationRow.LocationKey;
MainDS.PPartnerLocation.Rows.Add(partnerlocationRow);
PPartnerTypeRow partnertypeRow = MainDS.PPartnerType.NewRowTyped();
partnertypeRow.PartnerKey = partnerRow.PartnerKey;
partnertypeRow.TypeCode = MPartnerConstants.PARTNERTYPE_LEDGER;
MainDS.PPartnerType.Rows.Add(partnertypeRow);
UmUnitStructureRow unitStructureRow = MainDS.UmUnitStructure.NewRowTyped();
unitStructureRow.ParentUnitKey = 1000000;
unitStructureRow.ChildUnitKey = partnerRow.PartnerKey;
MainDS.UmUnitStructure.Rows.Add(unitStructureRow);
PartnerEditTDSAccess.SubmitChanges(MainDS);
string sqlInsertModule =
String.Format("INSERT INTO PUB_{0}({1}, {2}) VALUES ('REG-{3:0000000000}','Registration {4}')",
SModuleTable.GetTableDBName(),
SModuleTable.GetModuleIdDBName(),
SModuleTable.GetModuleNameDBName(),
APartnerKey,
ACountryName);
string sqlInsertModulePermissions =
String.Format("INSERT INTO PUB_{0}({1}, {2}, {3}) VALUES ('DEMO', 'REG-{4:0000000000}',true)",
SUserModuleAccessPermissionTable.GetTableDBName(),
SUserModuleAccessPermissionTable.GetUserIdDBName(),
SUserModuleAccessPermissionTable.GetModuleIdDBName(),
SUserModuleAccessPermissionTable.GetCanAccessDBName(),
APartnerKey);
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlInsertModule, Transaction);
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlInsertModulePermissions, Transaction);
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception e)
{
TLogging.Log(e.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
public static bool CreateNewLedger(Int32 ANewLedgerNumber,
String ALedgerName,
String ACountryCode,
String ABaseCurrency,
String AIntlCurrency,
DateTime ACalendarStartDate,
Int32 ANumberOfPeriods,
Int32 ACurrentPeriod,
Int32 ANumberOfFwdPostingPeriods,
bool IchIsAsset,
bool AActivateGiftProcessing,
Int32 AStartingReceiptNumber,
bool AActivateAccountsPayable,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
bool AllOK = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
// check if such a ledger already exists
ALedgerTable tempLedger = ALedgerAccess.LoadByPrimaryKey(ANewLedgerNumber, Transaction);
if (tempLedger.Count > 0)
{
AVerificationResult = new TVerificationResultCollection();
string msg = String.Format(Catalog.GetString(
"There is already a ledger with number {0}. Please choose another number."), ANewLedgerNumber);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Ledger"), msg, TResultSeverity.Resv_Critical));
return false;
}
if ((ANewLedgerNumber <= 1) || (ANewLedgerNumber > 9999))
{
// ledger number 1 does not work, because the root unit has partner key 1000000.
AVerificationResult = new TVerificationResultCollection();
string msg = String.Format(Catalog.GetString(
"Invalid number {0} for a ledger. Please choose a number between 2 and 9999."), ANewLedgerNumber);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Ledger"), msg, TResultSeverity.Resv_Critical));
return false;
}
Int64 PartnerKey = Convert.ToInt64(ANewLedgerNumber) * 1000000L;
GLSetupTDS MainDS = new GLSetupTDS();
ALedgerRow ledgerRow = MainDS.ALedger.NewRowTyped();
ledgerRow.LedgerNumber = ANewLedgerNumber;
ledgerRow.LedgerName = ALedgerName;
ledgerRow.CurrentPeriod = ACurrentPeriod;
ledgerRow.NumberOfAccountingPeriods = ANumberOfPeriods;
ledgerRow.NumberFwdPostingPeriods = ANumberOfFwdPostingPeriods;
ledgerRow.BaseCurrency = ABaseCurrency;
ledgerRow.IntlCurrency = AIntlCurrency;
ledgerRow.ActualsDataRetention = 11;
ledgerRow.GiftDataRetention = 11;
ledgerRow.BudgetDataRetention = 2;
ledgerRow.CountryCode = ACountryCode;
ledgerRow.ForexGainsLossesAccount = "5003";
ledgerRow.PartnerKey = PartnerKey;
if (ANumberOfPeriods == 12)
{
ledgerRow.CalendarMode = true;
}
else
{
ledgerRow.CalendarMode = false;
}
MainDS.ALedger.Rows.Add(ledgerRow);
PPartnerRow partnerRow;
if (!PPartnerAccess.Exists(PartnerKey, Transaction))
{
partnerRow = MainDS.PPartner.NewRowTyped();
ledgerRow.PartnerKey = PartnerKey;
partnerRow.PartnerKey = PartnerKey;
partnerRow.PartnerShortName = ALedgerName;
partnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
partnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT;
MainDS.PPartner.Rows.Add(partnerRow);
// create or use addresses (only if partner record is created here as
// otherwise we assume that Partner has address already)
PLocationRow locationRow;
PLocationTable LocTemplateTable;
PLocationTable LocResultTable;
PLocationRow LocTemplateRow;
StringCollection LocTemplateOperators;
// find address with country set
LocTemplateTable = new PLocationTable();
LocTemplateRow = LocTemplateTable.NewRowTyped(false);
LocTemplateRow.SiteKey = 0;
LocTemplateRow.StreetName = Catalog.GetString("No valid address on file");
LocTemplateRow.CountryCode = ACountryCode;
LocTemplateOperators = new StringCollection();
LocResultTable = PLocationAccess.LoadUsingTemplate(LocTemplateRow, LocTemplateOperators, Transaction);
if (LocResultTable.Count > 0)
{
locationRow = (PLocationRow)LocResultTable.Rows[0];
}
else
{
// no location record exists yet: create new one
locationRow = MainDS.PLocation.NewRowTyped();
locationRow.SiteKey = 0;
locationRow.LocationKey = (int)DBAccess.GDBAccessObj.GetNextSequenceValue(
TSequenceNames.seq_location_number.ToString(), Transaction);
locationRow.StreetName = Catalog.GetString("No valid address on file");
locationRow.CountryCode = ACountryCode;
MainDS.PLocation.Rows.Add(locationRow);
}
// now create partner location record
PPartnerLocationRow partnerLocationRow = MainDS.PPartnerLocation.NewRowTyped();
partnerLocationRow.SiteKey = locationRow.SiteKey;
partnerLocationRow.PartnerKey = PartnerKey;
partnerLocationRow.LocationKey = locationRow.LocationKey;
partnerLocationRow.DateEffective = DateTime.Today;
MainDS.PPartnerLocation.Rows.Add(partnerLocationRow);
}
else
{
// partner record already exists in database -> update ledger name
PPartnerAccess.LoadByPrimaryKey(MainDS, PartnerKey, Transaction);
partnerRow = (PPartnerRow)MainDS.PPartner.Rows[0];
partnerRow.PartnerShortName = ALedgerName;
}
PPartnerTypeAccess.LoadViaPPartner(MainDS, PartnerKey, Transaction);
PPartnerTypeRow partnerTypeRow;
// only create special type "LEDGER" if it does not exist yet
if (MainDS.PPartnerType.Rows.Find(new object[] { PartnerKey, MPartnerConstants.PARTNERTYPE_LEDGER }) == null)
{
partnerTypeRow = MainDS.PPartnerType.NewRowTyped();
partnerTypeRow.PartnerKey = PartnerKey;
partnerTypeRow.TypeCode = MPartnerConstants.PARTNERTYPE_LEDGER;
MainDS.PPartnerType.Rows.Add(partnerTypeRow);
}
if (!PUnitAccess.Exists(PartnerKey, Transaction))
{
PUnitRow unitRow = MainDS.PUnit.NewRowTyped();
unitRow.PartnerKey = PartnerKey;
unitRow.UnitName = ALedgerName;
MainDS.PUnit.Rows.Add(unitRow);
}
String ModuleId = "LEDGER" + ANewLedgerNumber.ToString("0000");
if (!SModuleAccess.Exists(ModuleId, Transaction))
{
SModuleRow moduleRow = MainDS.SModule.NewRowTyped();
moduleRow.ModuleId = ModuleId;
moduleRow.ModuleName = moduleRow.ModuleId;
MainDS.SModule.Rows.Add(moduleRow);
}
// if this is the first ledger, make it the default site
SSystemDefaultsTable systemDefaults = SSystemDefaultsAccess.LoadByPrimaryKey("SiteKey", Transaction);
if (systemDefaults.Rows.Count == 0)
{
SSystemDefaultsRow systemDefaultsRow = MainDS.SSystemDefaults.NewRowTyped();
systemDefaultsRow.DefaultCode = SharedConstants.SYSDEFAULT_SITEKEY;
systemDefaultsRow.DefaultDescription = "there has to be one site key for the database";
systemDefaultsRow.DefaultValue = PartnerKey.ToString("0000000000");
MainDS.SSystemDefaults.Rows.Add(systemDefaultsRow);
}
//TODO: Calendar vs Financial Date Handling - Need to review this
// create calendar
// at the moment we only support financial years that start on the first day of a month
// and currently only 12 or 13 periods are allowed and a maximum of 8 forward periods
DateTime periodStartDate = ACalendarStartDate;
for (Int32 periodNumber = 1; periodNumber <= ANumberOfPeriods + ANumberOfFwdPostingPeriods; periodNumber++)
{
AAccountingPeriodRow accountingPeriodRow = MainDS.AAccountingPeriod.NewRowTyped();
accountingPeriodRow.LedgerNumber = ANewLedgerNumber;
accountingPeriodRow.AccountingPeriodNumber = periodNumber;
accountingPeriodRow.PeriodStartDate = periodStartDate;
if ((ANumberOfPeriods == 13)
&& (periodNumber == 12))
{
// in case of 12 periods the second last period represents the last month except for the very last day
accountingPeriodRow.PeriodEndDate = periodStartDate.AddMonths(1).AddDays(-2);
}
else if ((ANumberOfPeriods == 13)
&& (periodNumber == 13))
{
// in case of 13 periods the last period just represents the very last day of the financial year
accountingPeriodRow.PeriodEndDate = periodStartDate;
}
else
{
accountingPeriodRow.PeriodEndDate = periodStartDate.AddMonths(1).AddDays(-1);
}
accountingPeriodRow.AccountingPeriodDesc = periodStartDate.ToString("MMMM");
MainDS.AAccountingPeriod.Rows.Add(accountingPeriodRow);
periodStartDate = accountingPeriodRow.PeriodEndDate.AddDays(1);
}
// mark cached table for accounting periods to be refreshed
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
AAccountingSystemParameterRow accountingSystemParameterRow = MainDS.AAccountingSystemParameter.NewRowTyped();
accountingSystemParameterRow.LedgerNumber = ANewLedgerNumber;
accountingSystemParameterRow.ActualsDataRetention = ledgerRow.ActualsDataRetention;
accountingSystemParameterRow.GiftDataRetention = ledgerRow.GiftDataRetention;
accountingSystemParameterRow.NumberFwdPostingPeriods = ledgerRow.NumberFwdPostingPeriods;
accountingSystemParameterRow.NumberOfAccountingPeriods = ledgerRow.NumberOfAccountingPeriods;
accountingSystemParameterRow.BudgetDataRetention = ledgerRow.BudgetDataRetention;
MainDS.AAccountingSystemParameter.Rows.Add(accountingSystemParameterRow);
// activate GL subsystem (this is always active)
ASystemInterfaceRow systemInterfaceRow = MainDS.ASystemInterface.NewRowTyped();
systemInterfaceRow.LedgerNumber = ANewLedgerNumber;
systemInterfaceRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
systemInterfaceRow.SetUpComplete = true;
MainDS.ASystemInterface.Rows.Add(systemInterfaceRow);
ATransactionTypeRow transactionTypeRow;
// TODO: this might be different for other account or costcentre names
transactionTypeRow = MainDS.ATransactionType.NewRowTyped();
transactionTypeRow.LedgerNumber = ANewLedgerNumber;
transactionTypeRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
transactionTypeRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.ALLOC.ToString();
transactionTypeRow.DebitAccountCode = "BAL SHT";
transactionTypeRow.CreditAccountCode = "BAL SHT";
transactionTypeRow.TransactionTypeDescription = "Allocation Journal";
transactionTypeRow.SpecialTransactionType = true;
MainDS.ATransactionType.Rows.Add(transactionTypeRow);
transactionTypeRow = MainDS.ATransactionType.NewRowTyped();
transactionTypeRow.LedgerNumber = ANewLedgerNumber;
transactionTypeRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
transactionTypeRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.REALLOC.ToString();
transactionTypeRow.DebitAccountCode = "BAL SHT";
transactionTypeRow.CreditAccountCode = "BAL SHT";
transactionTypeRow.TransactionTypeDescription = "Reallocation Journal";
transactionTypeRow.SpecialTransactionType = true;
MainDS.ATransactionType.Rows.Add(transactionTypeRow);
transactionTypeRow = MainDS.ATransactionType.NewRowTyped();
transactionTypeRow.LedgerNumber = ANewLedgerNumber;
transactionTypeRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
transactionTypeRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.REVAL.ToString();
transactionTypeRow.DebitAccountCode = "5003";
transactionTypeRow.CreditAccountCode = "5003";
transactionTypeRow.TransactionTypeDescription = "Foreign Exchange Revaluation";
transactionTypeRow.SpecialTransactionType = true;
MainDS.ATransactionType.Rows.Add(transactionTypeRow);
transactionTypeRow = MainDS.ATransactionType.NewRowTyped();
transactionTypeRow.LedgerNumber = ANewLedgerNumber;
transactionTypeRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
transactionTypeRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
transactionTypeRow.DebitAccountCode = MFinanceConstants.ACCOUNT_BAL_SHT;
transactionTypeRow.CreditAccountCode = MFinanceConstants.ACCOUNT_BAL_SHT;
transactionTypeRow.TransactionTypeDescription = "Standard Journal";
transactionTypeRow.SpecialTransactionType = false;
MainDS.ATransactionType.Rows.Add(transactionTypeRow);
AValidLedgerNumberTable validLedgerNumberTable = AValidLedgerNumberAccess.LoadByPrimaryKey(ANewLedgerNumber, PartnerKey, Transaction);
if (validLedgerNumberTable.Rows.Count == 0)
{
AValidLedgerNumberRow validLedgerNumberRow = MainDS.AValidLedgerNumber.NewRowTyped();
validLedgerNumberRow.PartnerKey = PartnerKey;
validLedgerNumberRow.LedgerNumber = ANewLedgerNumber;
// TODO can we assume that ledger 4 is used for international clearing?
// but in the empty database, that ledger and therefore p_partner with key 4000000 does not exist
// validLedgerNumberRow.IltProcessingCentre = 4000000;
validLedgerNumberRow.CostCentreCode = (ANewLedgerNumber * 100).ToString("0000");
MainDS.AValidLedgerNumber.Rows.Add(validLedgerNumberRow);
}
ACostCentreTypesRow costCentreTypesRow = MainDS.ACostCentreTypes.NewRowTyped();
costCentreTypesRow.LedgerNumber = ANewLedgerNumber;
costCentreTypesRow.CostCentreType = "Local";
costCentreTypesRow.Deletable = false;
MainDS.ACostCentreTypes.Rows.Add(costCentreTypesRow);
costCentreTypesRow = MainDS.ACostCentreTypes.NewRowTyped();
costCentreTypesRow.LedgerNumber = ANewLedgerNumber;
costCentreTypesRow.CostCentreType = "Foreign";
costCentreTypesRow.Deletable = false;
MainDS.ACostCentreTypes.Rows.Add(costCentreTypesRow);
ImportDefaultAccountHierarchy(ref MainDS, ANewLedgerNumber, ref AVerificationResult);
ImportDefaultCostCentreHierarchy(ref MainDS, ANewLedgerNumber, ALedgerName);
ImportDefaultMotivations(ref MainDS, ANewLedgerNumber);
ImportDefaultAdminGrantsPayableReceivable(ref MainDS, ANewLedgerNumber);
// TODO: modify UI navigation yml file etc?
// TODO: permissions for which users?
GLSetupTDSAccess.SubmitChanges(MainDS);
// activate gift processing subsystem
if (AActivateGiftProcessing)
{
ActivateGiftProcessingSubsystem(ANewLedgerNumber, AStartingReceiptNumber);
}
// activate accounts payable subsystem
if (AActivateAccountsPayable)
{
ActivateAccountsPayableSubsystem(ANewLedgerNumber);
}
// give the current user access permissions to this new ledger
SUserModuleAccessPermissionTable moduleAccessPermissionTable = new SUserModuleAccessPermissionTable();
SUserModuleAccessPermissionRow moduleAccessPermissionRow = moduleAccessPermissionTable.NewRowTyped();
moduleAccessPermissionRow.UserId = UserInfo.GUserInfo.UserID;
moduleAccessPermissionRow.ModuleId = "LEDGER" + ANewLedgerNumber.ToString("0000");
moduleAccessPermissionRow.CanAccess = true;
moduleAccessPermissionTable.Rows.Add(moduleAccessPermissionRow);
SUserModuleAccessPermissionAccess.SubmitChanges(moduleAccessPermissionTable, Transaction);
// create system analysis types for the new ledger
AAnalysisTypeAccess.LoadAll(MainDS, Transaction);
AAnalysisTypeTable NewAnalysisTypes = new AAnalysisTypeTable();
foreach (AAnalysisTypeRow AnalysisTypeRow in MainDS.AAnalysisType.Rows)
{
if (AnalysisTypeRow.SystemAnalysisType
&& !NewAnalysisTypes.Rows.Contains(new Object[] { ANewLedgerNumber, AnalysisTypeRow.AnalysisTypeCode }))
{
AAnalysisTypeRow NewAnalysisType = NewAnalysisTypes.NewRowTyped();
NewAnalysisType.ItemArray = (object[])AnalysisTypeRow.ItemArray.Clone();
NewAnalysisType.LedgerNumber = ANewLedgerNumber;
NewAnalysisTypes.Rows.Add(NewAnalysisType);
}
}
AAnalysisTypeAccess.SubmitChanges(NewAnalysisTypes, Transaction);
AllOK = true;
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the creation of a new Ledger:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
finally
{
if (AllOK)
{
DBAccess.GDBAccessObj.CommitTransaction();
//
// If the user has specified that ICH is an asset,
// I need to re-write it into the hierarchy:
if (IchIsAsset)
{
RewireIchIsAsset(ANewLedgerNumber);
}
}
else
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return AllOK;
}
