using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using Swarmops.Basic.Types; using Swarmops.Basic.Types.Financial; namespace Swarmops.Database { public partial class SwarmDb { #region Field reading code private const string salaryFieldSequence = " SalaryId,PayrollItemId,PayoutDate,Salaries.BaseSalaryCents,NetSalaryCents," + // 0-4 "SubtractiveTaxCents,AdditiveTaxCents,Attested,NetPaid,TaxPaid," + // 5-9 "Salaries.Open " + // 10 "FROM Salaries "; private static BasicSalary ReadSalaryFromDataReader (IDataRecord reader) { int salaryId = reader.GetInt32 (0); int payrollItemId = reader.GetInt32 (1); DateTime payoutDate = reader.GetDateTime (2); Int64 baseSalaryCents = reader.GetInt64 (3); Int64 netSalaryCents = reader.GetInt64 (4); Int64 subtractiveTaxCents = reader.GetInt64 (5); Int64 additiveTaxCents = reader.GetInt64 (6); bool attested = reader.GetBoolean (7); bool netPaid = reader.GetBoolean (8); bool taxPaid = reader.GetBoolean (9); bool open = reader.GetBoolean (10); return new BasicSalary (salaryId, payrollItemId, payoutDate, baseSalaryCents, netSalaryCents, subtractiveTaxCents, additiveTaxCents, attested, netPaid, taxPaid, open); } #endregion #region Record reading - SELECT statements /// /// Gets a salary from the database. /// /// The salary database identity. /// The requested salary. /// Thrown if there is no such identity. public BasicSalary GetSalary (int salaryId) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ( "SELECT" + salaryFieldSequence + "WHERE SalaryId=" + salaryId + ";", connection); using (DbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { return ReadSalaryFromDataReader (reader); } throw new ArgumentException ("Unknown Salary Id: " + salaryId); } } } /// /// Gets salaries from the database. /// /// /// An optional combination of a Person and/or Organization object and/or DatabaseCondition /// specifiers. /// /// A list of matching salaries. public BasicSalary[] GetSalaries (params object[] conditions) { List result = new List(); using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ( "SELECT" + salaryFieldSequence + "JOIN Payroll USING (PayrollItemId)" + ConstructWhereClause ("Salaries", conditions), connection); using (DbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { result.Add (ReadSalaryFromDataReader (reader)); } return result.ToArray(); } } } #endregion #region Creation and manipulation - stored procedures public int CreateSalary (int payrollItemId, DateTime payoutDate, Int64 baseSalaryCents, Int64 netSalaryCents, Int64 subtractiveTaxCents, Int64 additiveTaxCents) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ("CreateSalaryPrecise", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName (command, "payrollItemId", payrollItemId); AddParameterWithName (command, "payoutDate", payoutDate); AddParameterWithName (command, "baseSalaryCents", baseSalaryCents); AddParameterWithName (command, "netSalaryCents", netSalaryCents); AddParameterWithName (command, "subtractiveTaxCents", subtractiveTaxCents); AddParameterWithName (command, "additiveTaxCents", additiveTaxCents); return Convert.ToInt32 (command.ExecuteScalar()); } } public void SetSalaryNetPaid (int salaryId, bool netPaid) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ("SetSalaryNetPaid", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName (command, "salaryId", salaryId); AddParameterWithName (command, "netPaid", netPaid); command.ExecuteNonQuery(); // "Open" is set in the stored procedure to "NOT (TaxPaid AND NetPaid)". // So if both are paid, Open is set to false. } } public void SetSalaryNetSalary (int salaryId, Int64 netSalaryCents) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ("SetSalaryNetSalaryPrecise", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName (command, "salaryId", salaryId); AddParameterWithName (command, "netSalaryCents", netSalaryCents); command.ExecuteNonQuery(); } } public void SetSalaryTaxPaid (int salaryId, bool taxPaid) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand ("SetSalaryTaxPaid", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName (command, "salaryId", salaryId); AddParameterWithName (command, "taxPaid", taxPaid); command.ExecuteNonQuery(); // "Open" is set in the stored procedure to "NOT (TaxPaid AND NetPaid)". // So if both are paid, Open is set to false. } } public void SetSalaryAttested(int salaryId, bool attested) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand("SetSalaryAttested", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName(command, "salaryId", salaryId); AddParameterWithName(command, "attested", attested); command.ExecuteNonQuery(); } } public void SetSalaryOpen(int salaryId, bool attested) { using (DbConnection connection = GetMySqlDbConnection()) { connection.Open(); DbCommand command = GetDbCommand("SetSalaryOpen", connection); command.CommandType = CommandType.StoredProcedure; AddParameterWithName(command, "salaryId", salaryId); AddParameterWithName(command, "open", attested); command.ExecuteNonQuery(); } } #endregion } }