// 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);
}
