public bool Delete(string documentNo, DbTransaction parentTransaction)
{
var GLtransactionItem = new GLTransaction {
DocumentNo = documentNo
};
return(Delete(GLtransactionItem, parentTransaction));
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
Trx = new GLTransaction();
for (int i = 0; i < 2; i++)
{
Trx.Distributions.Add(new GLDistribution());
}
dgTransaction.ItemsSource = Trx.Distributions;
}
public ActionResult AddTransactionsDetails(string accountCode, string amount, string remarks, string debitcredit)
{
GLTransaction glTransaction = new GLTransaction();
glTransaction.AccountCode = accountCode;
glTransaction.Remark = remarks;
if (debitcredit == "CREDIT")
{
glTransaction.CreditAmount = Convert.ToDecimal(amount);
}
else
{
glTransaction.DebitAmount = Convert.ToDecimal(amount);
}
return(Json(new { Message = "Success", GLTransactionDetails = glTransaction }, JsonRequestBehavior.AllowGet));
}
public GLTransaction GetGLTransaction(GLTransaction item)
{
return((GLTransaction)gltransactionDAL.GetItem <GLTransaction>(item));
}
public bool DeleteGLTransaction(GLTransaction item)
{
return(gltransactionDAL.Delete(item));
}
public bool SaveGLTransaction(GLTransaction newItem)
{
return(gltransactionDAL.Save(newItem));
}
public GeneralLedgerTransaction(Guid guid, GLTransaction transaction)
{
Guid = guid;
Transaction = transaction;
}
public bool Save <T>(T item) where T : IContract
{
var result = 0;
var cbreceipt = (CBReceipt)(object)item;
if (currentTransaction == null)
{
connection = db.CreateConnection();
connection.Open();
}
var transaction = (currentTransaction == null ? connection.BeginTransaction() : currentTransaction);
try
{
var savecommand = db.GetStoredProcCommand(DBRoutine.SAVECBRECEIPT);
db.AddInParameter(savecommand, "DocumentNo", System.Data.DbType.String, cbreceipt.DocumentNo);
db.AddInParameter(savecommand, "DocumentDate", System.Data.DbType.DateTime, cbreceipt.DocumentDate);
db.AddInParameter(savecommand, "BranchID", System.Data.DbType.Int16, cbreceipt.BranchID);
db.AddInParameter(savecommand, "ReceiptType", System.Data.DbType.String, cbreceipt.ReceiptType);
db.AddInParameter(savecommand, "BankCode", System.Data.DbType.String, cbreceipt.BankCode);
db.AddInParameter(savecommand, "DebtorCode", System.Data.DbType.String, cbreceipt.DebtorCode);
db.AddInParameter(savecommand, "PayerName", System.Data.DbType.String, cbreceipt.PayerName ?? "");
db.AddInParameter(savecommand, "BankAccount", System.Data.DbType.String, cbreceipt.BankAccount ?? "");
db.AddInParameter(savecommand, "BankChargesAccount", System.Data.DbType.String, cbreceipt.BankChargesAccount ?? "");
db.AddInParameter(savecommand, "DebtorAccount", System.Data.DbType.String, cbreceipt.DebtorAccount ?? "");
db.AddInParameter(savecommand, "ChequeNo", System.Data.DbType.String, cbreceipt.ChequeNo ?? "");
db.AddInParameter(savecommand, "ChequeDate", System.Data.DbType.DateTime, cbreceipt.ChequeDate.ToString("dd-MM-yyyy") == "01-01-0001" ? (object)DBNull.Value : cbreceipt.ChequeDate);
db.AddInParameter(savecommand, "CurrencyCode", System.Data.DbType.String, cbreceipt.CurrencyCode ?? "THB");
db.AddInParameter(savecommand, "ExchangeRate", System.Data.DbType.Decimal, cbreceipt.ExchangeRate);
db.AddInParameter(savecommand, "BaseReceiptAmount", System.Data.DbType.Decimal, cbreceipt.BaseReceiptAmount);
db.AddInParameter(savecommand, "BaseApplyAmount", System.Data.DbType.Decimal, cbreceipt.BaseApplyAmount);
db.AddInParameter(savecommand, "LocalReceiptAmount", System.Data.DbType.Decimal, cbreceipt.LocalReceiptAmount);
db.AddInParameter(savecommand, "LocalApplyAmount", System.Data.DbType.Decimal, cbreceipt.LocalApplyAmount);
db.AddInParameter(savecommand, "LocalBankChargesAmount", System.Data.DbType.Decimal, cbreceipt.LocalBankChargesAmount);
db.AddInParameter(savecommand, "Remark", System.Data.DbType.String, cbreceipt.Remark ?? "");
db.AddInParameter(savecommand, "AccountDate", System.Data.DbType.DateTime, cbreceipt.AccountDate.ToString("dd-MM-yyyy") == "01-01-0001" ? (object)DBNull.Value : cbreceipt.AccountDate);
db.AddInParameter(savecommand, "Source", System.Data.DbType.String, cbreceipt.Source ?? "CB");
db.AddInParameter(savecommand, "IsWHTax", System.Data.DbType.Boolean, cbreceipt.IsWHTax);
db.AddInParameter(savecommand, "WHPercent", System.Data.DbType.Int16, cbreceipt.WHPercent);
db.AddInParameter(savecommand, "WHAmount", System.Data.DbType.Decimal, cbreceipt.WHAmount);
db.AddInParameter(savecommand, "IsVAT", System.Data.DbType.Boolean, cbreceipt.IsVAT);
db.AddInParameter(savecommand, "TaxAmount", System.Data.DbType.Decimal, cbreceipt.TaxAmount);
db.AddInParameter(savecommand, "TotalAmount", System.Data.DbType.Decimal, cbreceipt.TotalAmount);
db.AddInParameter(savecommand, "CreatedBy", System.Data.DbType.String, cbreceipt.CreatedBy);
db.AddInParameter(savecommand, "ModifiedBy", System.Data.DbType.String, cbreceipt.ModifiedBy);
db.AddInParameter(savecommand, "PrintRemarks", System.Data.DbType.String, cbreceipt.PrintRemarks ?? "");
db.AddOutParameter(savecommand, "NewDocumentNo", System.Data.DbType.String, 25);
result = db.ExecuteNonQuery(savecommand, transaction);
if (result > 0)
{
var cbReceiptDetailsItemDAL = new CBReceiptDetailDAL();
var cbReceiptDetailsSetOffItemDAL = new CBReceiptSetOffDetailDAL();
var cbreceiptgldetailsItemDAL = new CBReceiptGlDetailDAL();
var cbreceiptgltransactionItemDAL = new GLTransactionDAL();
short detailitem = 1;
cbreceipt.DocumentNo = savecommand.Parameters["@NewDocumentNo"].Value.ToString();
if (cbreceipt.CBReceiptSetOffDetails != null)
{
result = Convert.ToInt16(cbReceiptDetailsSetOffItemDAL.Delete(cbreceipt.DocumentNo, transaction));
cbreceipt.CBReceiptSetOffDetails.ForEach(dt =>
{
dt.DocumentNo = cbreceipt.DocumentNo;
dt.CreatedBy = cbreceipt.CreatedBy;
dt.ModifiedBy = cbreceipt.ModifiedBy;
dt.CurrencyCode = cbreceipt.CurrencyCode;
dt.MatchDocumentDate = cbreceipt.DocumentDate;
dt.DebtorCode = cbreceipt.DebtorCode;
dt.SetOffDate = cbreceipt.DocumentDate;
dt.ItemNo = detailitem++;
});
result = cbReceiptDetailsSetOffItemDAL.SaveList(cbreceipt.CBReceiptSetOffDetails, transaction) == true ? 1 : 0;
}
/* Save CBPaymentGLDetails if the CBPayment Type ='CREDITOR' */
detailitem = 1;
if (cbreceipt.CBReceiptGLDetails != null)
{
result = Convert.ToInt16(cbreceiptgldetailsItemDAL.Delete(cbreceipt.DocumentNo, transaction));
cbreceipt.CBReceiptGLDetails.ForEach(dt =>
{
dt.DocumentNo = cbreceipt.DocumentNo;
dt.TransactionType = "REC";
dt.ItemNo = detailitem++;
});
result = cbreceiptgldetailsItemDAL.SaveList(cbreceipt.CBReceiptGLDetails, transaction) == true ? 1 : 0;
}
if (cbreceipt.ReceiptType == "DEBTOR")
{
cbreceipt.GLTransactionDetails = new List <GLTransaction>();
cbreceipt.CBReceiptGLDetails.ForEach(gl =>
{
var gltransaction = new GLTransaction();
gltransaction.AccountCode = gl.AccountCode;
gltransaction.AccountDate = cbreceipt.DocumentDate;
gltransaction.BankInSlipNo = "";
gltransaction.BankStatementPgNo = 0;
gltransaction.BankStatementTotalPgNo = 0;
gltransaction.BaseAmount = gl.TotalAmount;
gltransaction.BranchID = cbreceipt.BranchID;
gltransaction.ChequeNo = "";
gltransaction.CreditAmount = 0;
gltransaction.CreditorCode = "";
gltransaction.CurrencyCode = cbreceipt.CurrencyCode;
gltransaction.DebitAmount = 0;
gltransaction.DebitCredit = "";
gltransaction.DebtorCode = cbreceipt.DebtorCode;
gltransaction.DetailRemark = cbreceipt.Remark;
gltransaction.DocumentType = "REC";
gltransaction.DocumentNo = cbreceipt.DocumentNo;
gltransaction.DocumentDate = cbreceipt.DocumentDate;
gltransaction.Amount = gl.TotalAmount;
gltransaction.Remark = cbreceipt.Remark;
gltransaction.ItemNo = Convert.ToInt16(gl.ItemNo);
gltransaction.Source = "CB";
gltransaction.AccountDate = cbreceipt.DocumentDate;
gltransaction.ExchangeRate = 0;
gltransaction.Status = true;
gltransaction.Year = 0;
cbreceipt.GLTransactionDetails.Add(gltransaction);
});
}
if (cbreceipt.CBReceiptDetails != null)
{
result = Convert.ToInt16(cbReceiptDetailsItemDAL.Delete(cbreceipt.DocumentNo, transaction));
detailitem = 1;
cbreceipt.CBReceiptDetails.ForEach(dt =>
{
dt.ItemNo = detailitem++;
dt.DocumentNo = cbreceipt.DocumentNo;
});
result = cbReceiptDetailsItemDAL.SaveList(cbreceipt.CBReceiptDetails, transaction) == true ? 1 : 0;
}
if (cbreceipt.GLTransactionDetails != null)
{
detailitem = 1;
if (cbreceipt.ReceiptType != "DEBTOR")
{
cbreceipt.GLTransactionDetails.ForEach(dt =>
{
dt.DocumentNo = cbreceipt.DocumentNo;
dt.CurrencyCode = cbreceipt.CurrencyCode;
dt.DebtorCode = cbreceipt.DebtorCode;
dt.DocumentDate = cbreceipt.DocumentDate;
dt.BankInSlipNo = "";
dt.Amount = dt.CreditAmount > 0 ? dt.CreditAmount : dt.DebitAmount;
dt.BaseAmount = dt.CreditAmount > 0 ? dt.CreditAmount : dt.DebitAmount;
dt.Remark = dt.Remark ?? dt.DetailRemark;
dt.ItemNo = detailitem++;
dt.Source = "CB";
dt.BranchID = cbreceipt.BranchID;
dt.AccountDate = cbreceipt.DocumentDate;
dt.DocumentType = "REC";
});
}
//result = Convert.ToInt16(cbPaymentgltransactionItemDAL.Delete(cbpayment.DocumentNo, transaction));
result = cbreceiptgltransactionItemDAL.SaveList(cbreceipt.GLTransactionDetails, transaction) == true ? 1 : 0;
}
}
if (result > 0)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
}
catch (Exception ex)
{
if (currentTransaction == null)
{
transaction.Rollback();
}
throw;
}
return(result > 0 ? true : false);
}
private List <JournalEntryLine> GetGlEntries(RockContext rockContext, FinancialBatch financialBatch, string journalCode, int period, ref string debugLava, string DescriptionLava = "")
{
if (string.IsNullOrWhiteSpace(DescriptionLava))
{
DescriptionLava = "{{ Batch.Id }}: {{ Batch.Name }}";
}
//
// Group/Sum Transactions by Account and Project since Project can come from Account or Transaction Details
//
var batchTransactions = new List <GLTransaction>();
var registrationLinks = new List <RegistrationInstance>();
var groupMemberLinks = new List <GroupMember>();
foreach (var transaction in financialBatch.Transactions)
{
transaction.LoadAttributes();
foreach (var transactionDetail in transaction.TransactionDetails)
{
transactionDetail.LoadAttributes();
transactionDetail.Account.LoadAttributes();
var detailProject = transactionDetail.GetAttributeValue("rocks.kfs.ShelbyFinancials.Project").AsGuidOrNull();
var transactionProject = transaction.GetAttributeValue("rocks.kfs.ShelbyFinancials.Project").AsGuidOrNull();
var accountProject = transactionDetail.Account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Project").AsGuidOrNull();
var projectCode = string.Empty;
if (detailProject != null)
{
projectCode = DefinedValueCache.Get(( Guid )detailProject).Value;
}
else if (transactionProject != null)
{
projectCode = DefinedValueCache.Get(( Guid )transactionProject).Value;
}
else if (accountProject != null)
{
projectCode = DefinedValueCache.Get(( Guid )accountProject).Value;
}
if (transactionDetail.EntityTypeId.HasValue)
{
var registrationEntityType = EntityTypeCache.Get(typeof(Rock.Model.Registration));
var groupMemberEntityType = EntityTypeCache.Get(typeof(Rock.Model.GroupMember));
if (transactionDetail.EntityId.HasValue && transactionDetail.EntityTypeId == registrationEntityType.Id)
{
foreach (var registration in new RegistrationService(rockContext)
.Queryable().AsNoTracking()
.Where(r =>
r.RegistrationInstance != null &&
r.RegistrationInstance.RegistrationTemplate != null &&
r.Id == transactionDetail.EntityId))
{
registrationLinks.Add(registration.RegistrationInstance);
}
}
if (transactionDetail.EntityId.HasValue && transactionDetail.EntityTypeId == groupMemberEntityType.Id)
{
foreach (var groupMember in new GroupMemberService(rockContext)
.Queryable().AsNoTracking()
.Where(gm =>
gm.Group != null &&
gm.Id == transactionDetail.EntityId))
{
groupMemberLinks.Add(groupMember);
}
}
}
var transactionItem = new GLTransaction()
{
Amount = transactionDetail.Amount,
FinancialAccountId = transactionDetail.AccountId,
Project = projectCode
};
batchTransactions.Add(transactionItem);
}
}
var batchTransactionsSummary = batchTransactions
.GroupBy(d => new { d.FinancialAccountId, d.Project })
.Select(s => new GLTransaction
{
FinancialAccountId = s.Key.FinancialAccountId,
Project = s.Key.Project,
Amount = s.Sum(f => ( decimal? )f.Amount) ?? 0.0M
})
.ToList();
var batchSummary = new List <GLBatchTotals>();
foreach (var summary in batchTransactionsSummary)
{
var account = new FinancialAccountService(rockContext).Get(summary.FinancialAccountId);
account.LoadAttributes();
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("Account", account);
mergeFields.Add("Summary", summary);
mergeFields.Add("Batch", financialBatch);
mergeFields.Add("Registrations", registrationLinks);
mergeFields.Add("GroupMembers", groupMemberLinks);
mergeFields.Add("JournalCode", journalCode);
mergeFields.Add("Period", period);
var batchSummaryItem = new GLBatchTotals()
{
CompanyNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Company"),
RegionNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Region"),
SuperFundNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.SuperFund"),
FundNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Fund"),
LocationNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Location"),
CostCenterNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.CostCenter"),
DepartmentNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.Department"),
CreditAccountNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.CreditAccount"),
DebitAccountNumber = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.DebitAccount"),
AccountSub = account.GetAttributeValue("rocks.kfs.ShelbyFinancials.AccountSub"),
Amount = summary.Amount,
Project = summary.Project,
JournalNumber = financialBatch.Id,
JournalDescription = DescriptionLava.ResolveMergeFields(mergeFields),
Date = financialBatch.BatchStartDateTime ?? RockDateTime.Now,
Note = financialBatch.Note
};
if (debugLava.Length < 6 && debugLava.AsBoolean())
{
debugLava = mergeFields.lavaDebugInfo();
}
batchSummary.Add(batchSummaryItem);
}
return(GenerateLineItems(batchSummary));
}
public static List <GLTransaction> GetTransactionSummary(FinancialBatch financialBatch, RockContext rockContext, out List <RegistrationInstance> registrationLinks, out List <GroupMember> groupMemberLinks)
{
//
// Group/Sum Transactions by Debit/Bank Account and Project since Project can come from Account or Transaction Details
//
var batchTransactions = new List <GLTransaction>();
registrationLinks = new List <RegistrationInstance>();
groupMemberLinks = new List <GroupMember>();
foreach (var transaction in financialBatch.Transactions)
{
transaction.LoadAttributes();
var gateway = transaction.FinancialGateway;
var gatewayDefaultFeeAccount = string.Empty;
var processTransactionFees = 0;
if (gateway != null)
{
gateway.LoadAttributes();
gatewayDefaultFeeAccount = transaction.FinancialGateway.GetAttributeValue("rocks.kfs.Intacct.DEFAULTFEEACCOUNTNO");
var gatewayFeeProcessing = transaction.FinancialGateway.GetAttributeValue("rocks.kfs.Intacct.FEEPROCESSING").AsIntegerOrNull();
if (gatewayFeeProcessing != null)
{
processTransactionFees = gatewayFeeProcessing.Value;
}
}
foreach (var transactionDetail in transaction.TransactionDetails)
{
transactionDetail.LoadAttributes();
transactionDetail.Account.LoadAttributes();
var detailProject = transactionDetail.GetAttributeValue("rocks.kfs.Intacct.PROJECTID").AsGuidOrNull();
var accountProject = transactionDetail.Account.GetAttributeValue("rocks.kfs.Intacct.PROJECTID").AsGuidOrNull();
var transactionFeeAccount = transactionDetail.Account.GetAttributeValue("rocks.kfs.Intacct.FEEACCOUNTNO");
if (string.IsNullOrWhiteSpace(transactionFeeAccount))
{
transactionFeeAccount = gatewayDefaultFeeAccount;
}
var projectCode = string.Empty;
if (detailProject != null)
{
projectCode = DefinedValueCache.Get(( Guid )detailProject).Value;
}
else if (accountProject != null)
{
projectCode = DefinedValueCache.Get(( Guid )accountProject).Value;
}
if (transactionDetail.EntityTypeId.HasValue)
{
var registrationEntityType = EntityTypeCache.Get(typeof(Rock.Model.Registration));
var groupMemberEntityType = EntityTypeCache.Get(typeof(Rock.Model.GroupMember));
if (transactionDetail.EntityId.HasValue && transactionDetail.EntityTypeId == registrationEntityType.Id)
{
foreach (var registration in new RegistrationService(rockContext)
.Queryable().AsNoTracking()
.Where(r =>
r.RegistrationInstance != null &&
r.RegistrationInstance.RegistrationTemplate != null &&
r.Id == transactionDetail.EntityId))
{
registrationLinks.Add(registration.RegistrationInstance);
}
}
if (transactionDetail.EntityId.HasValue && transactionDetail.EntityTypeId == groupMemberEntityType.Id)
{
foreach (var groupMember in new GroupMemberService(rockContext)
.Queryable().AsNoTracking()
.Where(gm =>
gm.Group != null &&
gm.Id == transactionDetail.EntityId))
{
groupMemberLinks.Add(groupMember);
}
}
}
var transactionItem = new GLTransaction()
{
Payer = transaction.AuthorizedPersonAlias.Person.FullName,
Amount = transactionDetail.Amount,
FinancialAccountId = transactionDetail.AccountId,
Project = projectCode,
TransactionFeeAmount = transactionDetail.FeeAmount != null && transactionDetail.FeeAmount.Value > 0 ? transactionDetail.FeeAmount.Value : 0.0M,
TransactionFeeAccount = transactionFeeAccount,
ProcessTransactionFees = processTransactionFees
};
batchTransactions.Add(transactionItem);
}
}
var batchTransactionList = batchTransactions
.GroupBy(d => new { d.FinancialAccountId, d.Project, d.TransactionFeeAccount, d.ProcessTransactionFees })
.Select(s => new GLTransaction
{
Payer = "Rock Import",
FinancialAccountId = s.Key.FinancialAccountId,
Project = s.Key.Project,
Amount = s.Sum(f => ( decimal? )f.Amount) ?? 0.0M,
TransactionFeeAmount = s.Sum(f => ( decimal? )f.TransactionFeeAmount) ?? 0.0M,
TransactionFeeAccount = s.Key.TransactionFeeAccount,
ProcessTransactionFees = s.Key.ProcessTransactionFees
})
.ToList();
return(batchTransactionList);
}
