// public int[] GetUnbalancedVerificationNumbers() // { // string sql = @" // select No // from // Verifications // where // ( // select isnull(sum(Amount), 0) // from AccountTransactionsView // where //Year([TransactionTime]) = Verifications.Year and //VerificationSerialNo = Verifications.SerialNo // ) // - // ( // select isnull(sum(Amount), 0) // from CashBookTransactionsView // where //Year([TransactionTime]) = Verifications.Year and //VerificationSerialNo = Verifications.SerialNo // ) <> 0"; // DataTable dataTable = new DataTable(); // Connection.GetTable(dataTable, sql); // return (from DataRow row in dataTable.Rows // select (int)row["No"]).ToList().ToArray(); // } public VerificationCollection GetUnbalancedAndEmptyVerifications(int accountingYear) { string sql = string.Format(@" select * from Verifications where Year = {0} and ( ( select isnull(sum(Amount), 0) from AccountTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) - ( select isnull(sum(Amount), 0) from CashBookTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) <> 0 or ( select count(*) from AccountTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) + ( select count(*) from CashBookTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) = 0 )", accountingYear); VerificationTable verificationTable = new VerificationTable(); Connection.GetTable(verificationTable, sql); return(VerificationCollection.CreateVerificationCollection(verificationTable)); }
/// <summary> /// Gets the last verification for the given year. /// </summary> /// <param name="year">The year to get the last verification from.</param> /// <returns>Last verification. null if there is no verification in the database for the given year.</returns> public Verification GetLastVerification(int year) { string sql = string.Format(@" select top 1 * from Verifications where Year = {0} order by No desc", year); VerificationTable resultDataTable = new VerificationTable(); Connection.GetTable(resultDataTable, sql); if (resultDataTable.Rows.Count > 0) { return(new Verification(resultDataTable[0])); } else { return(null); } }
public Verification GetFirstUnbalancedVerification(int year) { string sql = string.Format(@" select top 1 * from Verifications where Year = {0} and ( select isnull(sum(Amount), 0) from AccountTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) - ( select isnull(sum(Amount), 0) from CashBookTransactionsView where Year([TransactionTime]) = Verifications.Year and VerificationSerialNo = Verifications.SerialNo ) <> 0 order by Verifications.No asc", year); VerificationTable verificationsTable = new VerificationTable(); Connection.GetTable(verificationsTable, sql); if (verificationsTable.Rows.Count > 0) { return(new Verification(verificationsTable[0])); } return(null); }