/// <summary>
/// Loads client balances details by project id
/// </summary>
/// <param name="startRow">starting row for grid view</param>
/// <param name="pageSize">Sets the page size for grid view.</param>
/// <param name="forceRefresh">Gets records if forcefresh is true else gets records from cacahe.</param>
/// <returns>Retrieves client balances details by project id</returns>
public BalancesSearchItem[] LoadClientBalancesDetails(int startRow, int pageSize, bool forceRefresh)
{
AccountsServiceClient accountsService = null;
BalancesSearchItem[] clientBalances = null;
try
{
if (Session[SessionName.ProjectId] != null)
{
accountsService = new AccountsServiceClient();
CollectionRequest collectionRequest = new CollectionRequest();
collectionRequest.ForceRefresh = forceRefresh;
collectionRequest.StartRow = startRow;
collectionRequest.RowCount = pageSize;
Guid projectId = (Guid)Session[SessionName.ProjectId];
Guid logonId = ((LogonReturnValue)HttpContext.Current.Session[SessionName.LogonSettings]).LogonId;
BalancesSearchReturnValue returnValue = accountsService.GetClientBalancesDetails(logonId, collectionRequest, projectId);
if (returnValue.Success)
{
_clientRowCount = returnValue.Balances.TotalRowCount;
clientBalances = returnValue.Balances.Rows;
}
else
{
_lblMessage.CssClass = "errorMessage";
_lblMessage.Text = returnValue.Message;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (accountsService != null)
{
if (accountsService.State != System.ServiceModel.CommunicationState.Faulted)
{
accountsService.Close();
}
}
}
return(clientBalances);
}
/// <summary>
/// Gets offices balances by project id.
/// </summary>
/// <param name="oHostSecurityToken">HostSecurityToken obtained when security provider of IWS is called</param>
/// <param name="collectionRequest">Gets collection of office balances by project id</param>
/// <param name="projectId">Project id required to get office balances details</param>
/// <returns>Retrieves office balances by project id</returns>
public BalancesSearchReturnValue GetOfficeBalancesDetails(HostSecurityToken oHostSecurityToken, CollectionRequest collectionRequest, Guid projectId)
{
BalancesSearchReturnValue returnValue = null;
if (Functions.ValidateIWSToken(oHostSecurityToken))
{
oAccountService = new AccountsService();
returnValue = oAccountService.GetOfficeBalancesDetails(Functions.GetLogonIdFromToken(oHostSecurityToken), collectionRequest, projectId);
}
else
{
returnValue = new BalancesSearchReturnValue();
returnValue.Success = false;
returnValue.Message = "Invalid Token";
}
return(returnValue);
}
/// <summary>
/// Gets offices balances by project id.
/// </summary>
/// <param name="logonId">Logon id obtained when logging on to the logon service</param>
/// <param name="collectionRequest">Gets collection of office balances by project id</param>
/// <param name="projectId">Project id required to get office balances details</param>
/// <returns>Retrieves office balances by project id</returns>
public BalancesSearchReturnValue GetOfficeBalancesDetails(Guid logonId, CollectionRequest collectionRequest, Guid projectId)
{
BalancesSearchReturnValue returnValue = new BalancesSearchReturnValue();
try
{
// Get the logged on user from the current logons and add their
// ApplicationSettings the list of concurrent sessions.
Host.LoadLoggedOnUser(logonId);
try
{
Functions.RestrictRekoopIntegrationUser(UserInformation.Instance.DbUid);
switch (UserInformation.Instance.UserType)
{
case DataConstants.UserType.Staff:
// Can do everything
break;
case DataConstants.UserType.Client:
case DataConstants.UserType.ThirdParty:
throw new Exception("Access denied");
default:
throw new Exception("Access denied");
}
// Create a data list creator for a list of deposit made by client
DataListCreator<BalancesSearchItem> dataListCreator = new DataListCreator<BalancesSearchItem>();
// Declare an inline event (annonymous delegate) to read the
// dataset if it is required
dataListCreator.ReadDataSet += delegate(object Sender, ReadDataSetEventArgs e)
{
DataSet dsOfficeBalances = this.GetOfficeBalances(projectId);
// Creates new dataset of same schema as of office balances
DataSet dsNewOfficeBalances = dsOfficeBalances.Clone();
DataView dvOfficeBalances = new DataView(dsOfficeBalances.Tables["OfficeBalances"]);
if (dvOfficeBalances.Count != 0)
{
string columnOrderByPostingDate = Convert.ToString(dsOfficeBalances.Tables["OfficeBalances"].Columns["PostingDate"]);
dvOfficeBalances.Sort = columnOrderByPostingDate + " " + "asc";
decimal debit = decimal.Zero;
decimal credit = decimal.Zero;
decimal balance = decimal.Zero;
foreach (DataRowView officeBalancesRowView in dvOfficeBalances)
{
int postingId = Convert.ToInt32(officeBalancesRowView.Row["PostingId"].ToString().Trim());
DateTime postingDate = (DateTime)officeBalancesRowView.Row["PostingDate"];
string postingReference = (string)officeBalancesRowView.Row["PostingReference"].ToString();
string postingType = (string)officeBalancesRowView.Row["PostingType"].ToString(); ;
string postingDescription = (string)officeBalancesRowView.Row["PostingDescription"].ToString();
string postingBank = (string)officeBalancesRowView.Row["PostingBank"].ToString();
string postingBankRef = (string)officeBalancesRowView.Row["PostingBankRef"].ToString();
debit = (decimal)officeBalancesRowView.Row["Debit"];
credit = (decimal)officeBalancesRowView.Row["Credit"];
balance = balance + debit - credit;
balance = Decimal.Round(balance, 2);
// Adds deposit details to dataset after calculations
dsNewOfficeBalances.Tables["OfficeBalances"].Rows.Add(postingId,
postingDate,
postingReference,
postingType,
postingDescription,
postingBank,
postingBankRef,
debit.ToString("0.00"),
credit.ToString("0.00"),
balance.ToString("0.00"));
}
}
e.DataSet = dsNewOfficeBalances;
};
// Create the data list
returnValue.Balances = dataListCreator.Create(logonId,
// Give the query a name so it can be cached
"GetOfficeBalancesDetails",
// Tell it the query criteria used so if the cache is accessed
// again it knows if it is the same query
projectId.ToString(),
collectionRequest,
// Import mappings to map the dataset row fields to the data
// list entity properties
new ImportMapping[] {
new ImportMapping("PostingId", "PostingId"),
new ImportMapping("PostingDate", "PostingDate"),
new ImportMapping("PostingReference", "PostingReference"),
new ImportMapping("PostingType", "PostingType"),
new ImportMapping("PostingDescription","PostingDescription"),
new ImportMapping("PostingBank", "PostingBank"),
new ImportMapping("PostingBankRef", "PostingBankRef"),
new ImportMapping("Debit","Debit"),
new ImportMapping("Credit","Credit"),
new ImportMapping("Balance","Balance")
}
);
returnValue.Success = true;
}
finally
{
// Remove the logged on user's ApplicationSettings from the
// list of concurrent sessions
Host.UnloadLoggedOnUser();
}
}
catch (System.Data.SqlClient.SqlException)
{
returnValue.Success = false;
returnValue.Message = Functions.SQLErrorMessage;
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Message = ex.Message;
}
return returnValue;
}
/// <summary>
/// Gets client balance details by project id.
/// </summary>
/// <param name="logonId">Logon id obtained when logging on to the logon service</param>
/// <param name="collectionRequest">Gets collection of client balances by project id</param>
/// <param name="projectId">Project id required to get client balances details</param>
/// <returns>Retrieves client balances by project id</returns>
public BalancesSearchReturnValue GetClientBalancesDetails(Guid logonId, CollectionRequest collectionRequest, Guid projectId)
{
BalancesSearchReturnValue returnValue = new BalancesSearchReturnValue();
try
{
// Get the logged on user from the current logons and add their
// ApplicationSettings the list of concurrent sessions.
Host.LoadLoggedOnUser(logonId);
try
{
Functions.RestrictRekoopIntegrationUser(UserInformation.Instance.DbUid);
switch (UserInformation.Instance.UserType)
{
case DataConstants.UserType.Staff:
// Can do everything
break;
case DataConstants.UserType.Client:
case DataConstants.UserType.ThirdParty:
throw new Exception("Access denied");
default:
throw new Exception("Access denied");
}
// Create a data list creator for a list of deposit made by client
DataListCreator<BalancesSearchItem> dataListCreator = new DataListCreator<BalancesSearchItem>();
// Declare an inline event (annonymous delegate) to read the
// dataset if it is required
dataListCreator.ReadDataSet += delegate(object Sender, ReadDataSetEventArgs e)
{
DsCledgerTransactions dsClientLedgerTransactions = SrvCledgerLookup.GetCledgerTransactions(projectId);
DataSet dsClientBalances = new DataSet();
dsClientBalances.Tables.Add("ClientBalances");
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingId", typeof(int));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingDate", typeof(DateTime));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingReference", typeof(string));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingType", typeof(string));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingDescription", typeof(string));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingBank", typeof(string));
dsClientBalances.Tables["ClientBalances"].Columns.Add("PostingBankRef", typeof(string));
dsClientBalances.Tables["ClientBalances"].Columns.Add("Debit", typeof(decimal));
dsClientBalances.Tables["ClientBalances"].Columns.Add("Credit", typeof(decimal));
dsClientBalances.Tables["ClientBalances"].Columns.Add("Balance", typeof(decimal));
DataView dvClientBalances = new DataView(dsClientLedgerTransactions.uvw_CledgerTransactions);
if (dvClientBalances.Count != 0)
{
string columnOrderByPostingDate = Convert.ToString(dsClientLedgerTransactions.uvw_CledgerTransactions.Columns["PostingDetailsDate"]);
dvClientBalances.Sort = columnOrderByPostingDate + " " + "asc";
string postingBank = string.Empty;
string postingBankRef = string.Empty;
decimal debit = decimal.Zero;
decimal credit = decimal.Zero;
decimal balance = decimal.Zero;
foreach (DataRowView clientBalancesRowView in dvClientBalances)
{
bool addRecord = true;
if ((int)clientBalancesRowView.Row["bankTypeID"] != 1 && (int)clientBalancesRowView.Row["PostingTypeId"] != 3)
{
if ((int)clientBalancesRowView.Row["PostingTypeId"] != 3)
{
addRecord = false;
}
if ((int)clientBalancesRowView.Row["PostingTypeId"] == 33)
{
addRecord = true;
}
if ((int)clientBalancesRowView.Row["PostingTypeId"] == 41)
{
addRecord = true;
}
if ((int)clientBalancesRowView.Row["PostingTypeId"] == 69 && (int)clientBalancesRowView.Row["bankTypeID"] == 0)
{
addRecord = true;
}
}
if (addRecord)
{
int postingId = Convert.ToInt32(clientBalancesRowView.Row["PostingId"].ToString().Trim());
DateTime postingDate = (DateTime)clientBalancesRowView.Row["PostingDetailsDate"];
string postingReference = (string)clientBalancesRowView.Row["PostingDetailsRef"].ToString();
string postingType = (string)clientBalancesRowView.Row["PostingTypesRef"].ToString(); ;
string postingDescription = (string)clientBalancesRowView.Row["PostingDetailsDescription"].ToString();
if ((string)clientBalancesRowView.Row["bankRef"].ToString().Trim() == "0" ||
(int)clientBalancesRowView.Row["bankTypeID"] == 3)
{
postingBank = "N/A";
postingBankRef = "N/A";
}
else
{
int bankId = Convert.ToInt32(clientBalancesRowView.Row["bankId"]);
postingBank = this.GetBankByBankId(bankId);
postingBankRef = (string)clientBalancesRowView.Row["bankRef"].ToString();
}
if ((decimal)clientBalancesRowView.Row["CledgerMasterAmount"] < Decimal.Zero)
{
// Negative Posting Amount
debit = 0 - (decimal)clientBalancesRowView.Row["CledgerMasterAmount"];
credit = Decimal.Zero;
balance = balance - debit;
}
else
{
// Positive Posting Amount
credit = (decimal)clientBalancesRowView.Row["CledgerMasterAmount"];
debit = Decimal.Zero;
balance = balance + credit;
}
debit = Decimal.Round(debit, 2);
credit = Decimal.Round(credit, 2);
balance = Decimal.Round(balance, 2);
// setup credit/debit/blance decimal to diaplsy as 0.00 in dataset
string strDebit = string.Empty;
string strCredit = string.Empty;
string strBalance = string.Empty;
if (debit == Decimal.Zero)
{
strDebit = "0.00";
}
else
{
strDebit = debit.ToString();
}
if (credit == Decimal.Zero)
{
strCredit = "0.00";
}
else
{
strCredit = credit.ToString();
}
if (balance == Decimal.Zero)
{
strBalance = "0.00";
}
else
{
strBalance = balance.ToString();
}
// Adds deposit details to dataset after calculations
dsClientBalances.Tables["ClientBalances"].Rows.Add(postingId,
postingDate,
postingReference,
postingType,
postingDescription,
postingBank,
postingBankRef,
strDebit,
strCredit,
strBalance);
}
}
}
e.DataSet = dsClientBalances;
};
// Create the data list
returnValue.Balances = dataListCreator.Create(logonId,
// Give the query a name so it can be cached
"LoadClientBalancesDetails",
// Tell it the query criteria used so if the cache is accessed
// again it knows if it is the same query
projectId.ToString(),
collectionRequest,
// Import mappings to map the dataset row fields to the data
// list entity properties
new ImportMapping[] {
new ImportMapping("PostingId", "PostingId"),
new ImportMapping("PostingDate", "PostingDate"),
new ImportMapping("PostingReference", "PostingReference"),
new ImportMapping("PostingType", "PostingType"),
new ImportMapping("PostingDescription","PostingDescription"),
new ImportMapping("PostingBank", "PostingBank"),
new ImportMapping("PostingBankRef", "PostingBankRef"),
new ImportMapping("Debit","Debit"),
new ImportMapping("Credit","Credit"),
new ImportMapping("Balance","Balance")
}
);
returnValue.Success = true;
}
finally
{
// Remove the logged on user's ApplicationSettings from the
// list of concurrent sessions
Host.UnloadLoggedOnUser();
}
}
catch (System.Data.SqlClient.SqlException)
{
returnValue.Success = false;
returnValue.Message = Functions.SQLErrorMessage;
}
catch (Exception ex)
{
returnValue.Success = false;
returnValue.Message = ex.Message;
}
return returnValue;
}
/// <summary>
/// Gets offices balances by project id.
/// </summary>
/// <param name="oHostSecurityToken">HostSecurityToken obtained when security provider of IWS is called</param>
/// <param name="collectionRequest">Gets collection of office balances by project id</param>
/// <param name="projectId">Project id required to get office balances details</param>
/// <returns>Retrieves office balances by project id</returns>
public BalancesSearchReturnValue GetOfficeBalancesDetails(HostSecurityToken oHostSecurityToken, CollectionRequest collectionRequest, Guid projectId)
{
BalancesSearchReturnValue returnValue = null;
if (Functions.ValidateIWSToken(oHostSecurityToken))
{
oAccountService = new AccountsService();
returnValue = oAccountService.GetOfficeBalancesDetails(Functions.GetLogonIdFromToken(oHostSecurityToken), collectionRequest, projectId);
}
else
{
returnValue = new BalancesSearchReturnValue();
returnValue.Success = false;
returnValue.Message = "Invalid Token";
}
return returnValue;
}
