/// <summary>
/// Loads the encrypted part of the .mrb file into the Club
/// </summary>
public void LoadEncryptedSection()
{
/**********************************
* HOW ENCRYPTION IN MARIMBA WORKS*
* One part of the .mrb file is encrypted, the other is not
* The part that is not encrypted is login information
* A user will try to login with their password
* The double-hash of the password is used to check the hash is correct
* If correct, login will retrieve the key the rest of the file is encrypted with
* That key is stored by being xor'd with the single hash of the password
* As long as the password is unknown and SHA-256 is preimage resistant, this should be secure
* The rest of the file is encrypted in 256-bit AES
* The key is retreived, and the rest of the file is decrypted
* The file is loaded into Marimba as normally would be
* If a user changes their password, the key xor'd with the single hash is redone
* If an admin updates the key, everyone gets their key/hash thing updated
* This can easily be done if the key is known since xor is linear
* */
// DECRYPT ENCRYPTED SECTION
using (Aes aesAlg = Aes.Create())
{
aesAlg.Key = this.aesInfo.Key;
aesAlg.IV = this.aesInfo.IV;
// Create a decrytor to perform the stream transform.
ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);
// Create the streams used for decryption.
using (MemoryStream memoryStream = new MemoryStream(cipherText))
{
using (CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
{
using (StreamReader reader = new StreamReader(cryptoStream))
{
// read the membership list
iMember = Convert.ToInt16(reader.ReadLine());
for (int i = 0; i < iMember; i++)
members[i] = new Member(reader);
short numTerms = Convert.ToInt16(reader.ReadLine());
listTerms = new List<Term>(numTerms);
for (int i = 0; i < numTerms; i++)
{
Term nextTerm = new Term(reader);
listTerms.Add(nextTerm);
}
// read the budget stuff
int iBudget = Convert.ToInt32(reader.ReadLine());
this.budget = new List<BudgetItem>(iBudget + BudgetBuffer);
List<int> assetToDepIndices = new List<int>(iBudget);
for (int i = 0; i < iBudget; i++)
{
BudgetItem newItem = new BudgetItem();
newItem.value = Convert.ToDouble(reader.ReadLine());
newItem.name = reader.ReadLine();
newItem.dateOccur = new DateTime(Convert.ToInt64(reader.ReadLine()));
newItem.dateAccount = new DateTime(Convert.ToInt64(reader.ReadLine()));
newItem.cat = reader.ReadLine();
newItem.type = (TransactionType)Convert.ToInt32(reader.ReadLine());
newItem.term = Convert.ToInt32(reader.ReadLine());
newItem.comment = ClsStorage.ReverseCleanNewLine(reader.ReadLine());
budget.Add(newItem);
assetToDepIndices.Add(Convert.ToInt32(reader.ReadLine()));
}
int k = 0;
foreach (BudgetItem item in this.budget)
{
int assetToDepIndex = assetToDepIndices[k];
item.depOfAsset = (assetToDepIndex == -1) ? null : this.budget[assetToDepIndex];
k++;
}
int iHistory = Convert.ToInt32(reader.ReadLine());
historyList = new List<HistoryItem>(iHistory + HistoryBuffer);
for (int i = 0; i < iHistory; i++)
{
HistoryItem nextItem = new HistoryItem(reader);
historyList.Add(nextItem);
}
// read email
if (fileVersion >= 2)
{
emailAddress = reader.ReadLine();
imapServerAddress = reader.ReadLine();
this.bImap = Convert.ToBoolean(reader.ReadLine());
smptServerAddress = reader.ReadLine();
smtpRequiresSSL = Convert.ToInt32(reader.ReadLine());
imapRequiresSSL = Convert.ToBoolean(reader.ReadLine());
}
}
}
}
// remove cipherText from storage
cipherText = null;
}
this.clubEmail = new Email(emailAddress, Properties.Settings.Default.emailPassword, imapServerAddress, this.bImap, smptServerAddress, smtpRequiresSSL, imapRequiresSSL);
}
/// <summary>
/// Determines if asset has any value after depreciation
/// </summary>
/// <param name="asset">the asset to check</param>
/// <param name="beforeDate">Only include depreciation before this date</param>
/// <returns>Returns true is the depreciation on the asset is at least the value of the asset</returns>
public bool DetermineIsFullyDepreciated(BudgetItem asset, DateTime? beforeDate = null)
{
if (beforeDate == null)
{
beforeDate = DateTime.MaxValue;
}
// don't even to guess if the asset being depreciated hasn't been marked
if (!ClsStorage.currentClub.budget.Contains(asset))
{
return false;
}
// sum up all of the depreciation against this asset
double amountDepreciated = 0;
foreach (BudgetItem currentItem in this.budget)
{
if (currentItem.type == TransactionType.Depreciation && currentItem.depOfAsset == asset && currentItem.dateOccur <= beforeDate)
{
amountDepreciated += currentItem.value;
}
}
return amountDepreciated >= asset.value;
}
/// <summary>Adds an item to the club's budget </summary>
/// <param name="val">Value of item</param>
/// <param name="strName">Description for this budget item</param>
/// <param name="dtDateOccur">Date of event</param>
/// <param name="dtDateAccount">Date as per account</param>
/// <param name="strCategory">Category of item</param>
/// <param name="type">Whether this item is a revenue/expense/etc.</param>
/// <param name="termIndex">Index of relevant term</param>
/// <param name="strComment">Any comments from the user</param>
/// <param name="asset">Index of the asset</param>
public void AddBudget(double val, string strName, DateTime dtDateOccur, DateTime dtDateAccount, string strCategory, TransactionType type, int termIndex, string strComment, BudgetItem asset = null)
{
BudgetItem newItem = new BudgetItem();
newItem.value = val;
newItem.name = strName;
newItem.dateOccur = dtDateOccur;
newItem.dateAccount = dtDateAccount;
newItem.cat = strCategory;
newItem.type = type;
newItem.term = termIndex;
newItem.comment = strComment;
// if depreciation
if (type == TransactionType.Depreciation)
newItem.depOfAsset = asset;
this.budget.Add(newItem);
}
public double CalculateValueAfterDepreciation(BudgetItem asset)
{
double dDep = 0;
// sum up all of the depreciation against this asset
foreach (BudgetItem itemIterator in this.budget)
if (itemIterator.type == TransactionType.Depreciation && itemIterator.depOfAsset == asset)
dDep += itemIterator.value;
return asset.value - dDep;
}
/// <summary>
/// Loads edits made to .mrb file in Excel
/// </summary>
/// <param name="location">Location of .xlsx file</param>
/// <param name="newLocation">Location of the new .mrb file</param>
/// <param name="currentClub">Current club</param>
/// <returns>Club with edits</returns>
public static Club loadFromExcel(string location, string newLocation, Club currentClub)
{
// open the Excel file
var workbook = new XLWorkbook(location);
var worksheet = workbook.Worksheet("General");
double version = Convert.ToDouble(worksheet.Cell(1, 2).Value);
// check to see if this file is designed to work with this version
if (version >= 2)
{
Club output = ClsStorage.currentClub.CloneClub(newLocation);
output.fileVersion = version;
output.strName = (string)worksheet.Cell(2, 2).Value;
int iUser = Convert.ToInt16(worksheet.Cell(3, 2).Value);
output.strUsers = new List<string[]>(iUser);
output.emailAddress = (string)worksheet.Cell(4, 2).Value;
output.imapServerAddress = (string)worksheet.Cell(5, 2).Value;
output.bImap = (Boolean)worksheet.Cell(6, 2).Value;
output.smptServerAddress = (string)worksheet.Cell(7, 2).Value;
output.smtpRequiresSSL = Convert.ToInt16(worksheet.Cell(8, 2).Value);
output.imapRequiresSSL = (Boolean)worksheet.Cell(9, 2).Value;
// load Users
for (int i = 0; i < iUser; i++)
{
string[] newUser = new string[4];
for (int j = 0; j < 4; j++)
newUser[j] = (string)worksheet.Cell(i + 10, j + 1).Value;
output.strUsers.Add(newUser);
}
// Members tab
// load sheet and data
worksheet = workbook.Worksheet("Members");
output.iMember = Convert.ToInt16(worksheet.Cell(1, 2).Value);
// load Members
bool[] tempMultipleInstruments = new bool[Enum.GetValues(typeof(Member.Instrument)).Length];
for (int i = 0; i < output.iMember; i++)
{
// if the member does not play multiple instruments
if (!(bool)worksheet.Cell(i + 3, 12).Value)
output.members[i] = new Member(
(string)worksheet.Cell(i + 3, 1).Value,
(string)worksheet.Cell(i + 3, 2).Value,
(Member.MemberType)Convert.ToInt32(worksheet.Cell(i + 3, 3).Value),
Convert.ToUInt32(worksheet.Cell(i + 3, 4).Value),
Convert.ToInt32(worksheet.Cell(i + 3, 5).Value),
(string)worksheet.Cell(i + 3, 6).Value,
(string)worksheet.Cell(i + 3, 7).Value,
(string)worksheet.Cell(i + 3, 8).Value,
Convert.ToInt16(worksheet.Cell(i + 3, 9).Value),
DateTime.ParseExact((string)worksheet.Cell(i + 3, 10).Value, "dd/MM/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture),
Convert.ToInt32(worksheet.Cell(i + 3, 11).Value));
else
{
// the member plays multiple instruments
// create their array of instruments they play first
for (int j = 0; j < Enum.GetValues(typeof(Member.Instrument)).Length; j++)
tempMultipleInstruments[j] = Convert.ToBoolean(worksheet.Cell(i + 3, 13 + j).Value);
output.members[i] = new Member(
(string)worksheet.Cell(i + 3, 1).Value,
(string)worksheet.Cell(i + 3, 2).Value,
(Member.MemberType)Convert.ToInt32(worksheet.Cell(i + 3, 3).Value),
Convert.ToUInt32(worksheet.Cell(i + 3, 4).Value),
Convert.ToInt32(worksheet.Cell(i + 3, 5).Value),
(string)worksheet.Cell(i + 3, 6).Value,
(string)worksheet.Cell(i + 3, 7).Value,
(string)worksheet.Cell(i + 3, 8).Value,
Convert.ToInt16(worksheet.Cell(i + 3, 9).Value),
DateTime.ParseExact((string)worksheet.Cell(i + 3, 10).Value, "dd/MM/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture),
Convert.ToInt32(worksheet.Cell(i + 3, 11).Value),
tempMultipleInstruments);
}
}
// Terms tab
worksheet = workbook.Worksheet("Terms");
int row = 2;
short numTerms = Convert.ToInt16(worksheet.Cell(1, 2).Value);
output.listTerms = new List<Term>(numTerms);
for (int i = 0; i < numTerms; i++)
{
Term termToAdd = new Term();
termToAdd.strName = (string)worksheet.Cell(row, 2).Value;
row++;
termToAdd.numMembers = Convert.ToInt16(worksheet.Cell(row, 2).Value);
row++;
termToAdd.termIndex = Convert.ToInt16(worksheet.Cell(row, 2).Value);
row++;
for (int j = 0; j < termToAdd.numMembers; j++)
termToAdd.members[j] = Convert.ToInt16(worksheet.Cell(row, j + 2).Value);
row++;
termToAdd.startDate = (worksheet.Cell(row, 2).Value is DateTime) ?
(DateTime)worksheet.Cell(row, 2).Value :
DateTime.ParseExact((string)worksheet.Cell(row, 2).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
row++;
termToAdd.endDate = (worksheet.Cell(row, 2).Value is DateTime) ?
(DateTime)worksheet.Cell(row, 2).Value :
DateTime.ParseExact((string)worksheet.Cell(row, 2).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
row++;
termToAdd.numRehearsals = Convert.ToInt16(worksheet.Cell(row, 2).Value);
row += 2;
// load rehearsal dates
termToAdd.rehearsalDates = new DateTime[termToAdd.numRehearsals];
for (int j = 0; j < termToAdd.numRehearsals; j++)
termToAdd.rehearsalDates[j] = (worksheet.Cell(row, j + 2).Value is DateTime) ?
(DateTime)worksheet.Cell(row, j + 2).Value :
DateTime.ParseExact((string)worksheet.Cell(row, j + 2).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
row++;
// load attendance
termToAdd.attendance = new bool[120, termToAdd.numRehearsals];
for (int j = 0; j < termToAdd.numMembers; j++)
{
for (int k = 0; k < termToAdd.numRehearsals; k++)
termToAdd.attendance[j, k] = (bool)worksheet.Cell(row, k + 2).Value;
row++;
}
// load fees
termToAdd.numOtherFees = Convert.ToInt32(worksheet.Cell(row, 2).Value);
row++;
// membership fee
termToAdd.membershipFees = (double)worksheet.Cell(row + 1, 2).Value;
// other fees
termToAdd.otherFeesNames = new string[termToAdd.numOtherFees];
termToAdd.otherFeesAmounts = new double[termToAdd.numOtherFees];
for (int j = 0; j < termToAdd.numOtherFees; j++)
{
termToAdd.otherFeesNames[j] = (string)worksheet.Cell(row, 4 + j * 2).Value;
termToAdd.otherFeesAmounts[j] = (double)worksheet.Cell(row + 1, 4 + j * 2).Value;
}
row += 2;
// load who has paid
termToAdd.feesPaid = new double[120, termToAdd.numOtherFees + 1];
termToAdd.feesPaidDate = new DateTime[120, termToAdd.numOtherFees + 1];
for (int j = 0; j < termToAdd.numMembers; j++)
{
for (int k = 0; k < termToAdd.numOtherFees + 1; k++)
{
termToAdd.feesPaid[j, k] = (double)worksheet.Cell(row, 2 + k * 2).Value;
if (termToAdd.feesPaid[j, k] != 0)
termToAdd.feesPaidDate[j, k] = (worksheet.Cell(row, 3 + k * 2).Value is DateTime) ?
(DateTime)worksheet.Cell(row, 3 + k * 2).Value :
DateTime.ParseExact((string)worksheet.Cell(row, 3 + k * 2).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
row++;
}
output.listTerms.Add(termToAdd);
}
// Budget tab
// load sheet and data
worksheet = workbook.Worksheet("Budget");
int iBudget = Convert.ToInt32(worksheet.Cell(1, 2).Value);
output.budget = new List<BudgetItem>(iBudget);
List<int> indicesOfDepreciators = new List<int>(iBudget);
List<int> indicesOfDepreciatedAssets = new List<int>(iBudget);
for (int i = 0; i < iBudget; i++)
{
BudgetItem newItem = new BudgetItem();
newItem.value = (double)worksheet.Cell(i + 3, 2).Value;
newItem.name = (string)worksheet.Cell(i + 3, 1).Value;
newItem.dateOccur = (worksheet.Cell(i + 3, 3).Value is DateTime) ?
(DateTime)worksheet.Cell(i + 3, 3).Value :
DateTime.ParseExact((string)worksheet.Cell(i + 3, 3).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
newItem.dateAccount = (worksheet.Cell(i + 3, 4).Value is DateTime) ?
(DateTime)worksheet.Cell(i + 3, 4).Value :
DateTime.ParseExact((string)worksheet.Cell(i + 3, 4).Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
newItem.cat = (string)worksheet.Cell(i + 3, 5).Value;
newItem.type = (TransactionType)Convert.ToInt32(worksheet.Cell(i + 3, 6).Value);
newItem.term = Convert.ToInt32(worksheet.Cell(i + 3, 7).Value);
newItem.comment = (string)worksheet.Cell(i + 3, 8).Value;
newItem.depOfAsset = null;
output.budget.Add(newItem);
// if depreciation
if (output.budget[i].type == TransactionType.Depreciation)
{
indicesOfDepreciators.Add(i);
indicesOfDepreciatedAssets.Add(Convert.ToInt32(worksheet.Cell(i + 3, 9).Value));
}
}
for (int i = 0; i < indicesOfDepreciators.Count; i++)
{
int index = indicesOfDepreciators[i];
BudgetItem depreciatedAsset = output.budget[indicesOfDepreciatedAssets[i]];
output.budget[index].depOfAsset = depreciatedAsset;
}
// copy the history from the current club file
output.historyList = new List<HistoryItem>(currentClub.historyList.Count);
foreach (HistoryItem item in currentClub.historyList)
{
output.historyList.Add(item);
}
return output;
}
else
{
System.Windows.Forms.MessageBox.Show("The .xlsx file loaded is not designed to work with this version of Marimba.", "Failed to load changes");
return null;
}
}
