private bool TestBuilding(string dataPath)
{
try
{
string odbcQuery = "select ID from [DataSet].LedgerParameters";
odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath);
foreach (DataRow row in PervasiveSqlUtilities.FetchPervasiveData(odbcQuery).Rows)
{
return(true); //data found
}
throw new Exception("LedgerParameters returned zero rows");
}
catch (Exception e)
{
_Context.SystemLogSet.Add(new Data.Log.SystemLog()
{
EventTime = DateTime.Now,
Message = "ODBC Connection Test " + e.Message,
StackTrace = e.StackTrace
});
}
return(false);
}
public List <CustomerCategory> GetCustomerCategories(string buildPath)
{
bool isRental = buildPath.ToUpper().StartsWith("RENTAL");
string qry = "select CCCode,CCDesc from [DataSet].CustomerCategories";
if (isRental)
{
qry = qry + " where CCCode > 100 or CCCode = 0 ";
}
qry = qry + " Order by CCCode";
qry = PervasiveSqlUtilities.SetDataSource(qry, buildPath);
try
{
List <CustomerCategory> result = new List <CustomerCategory>();
var data = PervasiveSqlUtilities.FetchPervasiveData(qry);
foreach (DataRow row in data.Rows)
{
CustomerCategory c = new CustomerCategory(row);
result.Add(c);
}
return(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -> [" + qry + "]");
}
}
private int GetBuildingPeriod(DateTime startDate, string dataPath)
{
DateTime dDate = new DateTime(startDate.Year, startDate.Month, 1);
if (_periodItem == null)
{
string sqlPeriodConfig = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.PeriodParameters.sql");
sqlPeriodConfig = SetDataSource(sqlPeriodConfig, dataPath);
var periodData = PervasiveSqlUtilities.FetchPervasiveData(sqlPeriodConfig);
foreach (DataRow row in periodData.Rows)
{
_periodItem = new PeriodDataItem(row);
break;
}
}
int period = 0;
try
{
period = _periodItem.PeriodNumberLookup(dDate);
}
catch (Exception err)
{
throw err;
}
return(period);
}
private List <PastelMaintenanceTransaction> FetchPastelMaintTransactions()
{
List <PastelMaintenanceTransaction> result = new List <PastelMaintenanceTransaction>();
string accountList = string.Empty;
foreach (var config in _buildingConfig)
{
if (!string.IsNullOrWhiteSpace(accountList))
{
accountList = accountList + " or t.AccNumber = '" + config.PastelAccountNumber + "'";
}
else
{
accountList = " t.AccNumber = '" + config.PastelAccountNumber + "'";
}
}
string dataPath = _building.DataPath;
string sqlMaintenanceRecords = PervasiveSqlUtilities.ReadResourceScript("Astrodon.DataProcessor.Scripts.MaintenanceRecordList.sql");
sqlMaintenanceRecords = PervasiveSqlUtilities.SetDataSource(sqlMaintenanceRecords, dataPath);
sqlMaintenanceRecords = sqlMaintenanceRecords.Replace("[AccountList]", accountList);
foreach (DataRow row in PervasiveSqlUtilities.FetchPervasiveData(sqlMaintenanceRecords).Rows)
{
result.Add(new PastelMaintenanceTransaction(row, dataPath));
}
return(result);
}
public static List <TransactionDataItem> SearchPastel(string buildingPath,
DateTime fromDate, DateTime toDate,
string reference,
string description,
decimal?minimumAmount,
decimal?maximumAmount)
{
List <TransactionDataItem> result = new List <TransactionDataItem>();
string sqlQuery = PervasiveSqlUtilities.ReadResourceScript("Astrodon.TransactionSearch.TransactionSearch.sql");
sqlQuery = PervasiveSqlUtilities.SetDataSource(sqlQuery, buildingPath);
if (!string.IsNullOrWhiteSpace(reference))
{
sqlQuery = sqlQuery = sqlQuery + " and Refrence like '%" + reference + "%' ";
}
if (!string.IsNullOrWhiteSpace(description))
{
sqlQuery = sqlQuery = sqlQuery + " and description like '%" + description + "%' ";
}
if (minimumAmount != null)
{
sqlQuery = sqlQuery = sqlQuery + " and Abs(Amount) >= " + minimumAmount.Value.ToString("#0.00", CultureInfo.InstalledUICulture);
}
if (maximumAmount != null)
{
sqlQuery = sqlQuery = sqlQuery + " and Abs(Amount) <= " + maximumAmount.Value.ToString("#0.00", CultureInfo.InstalledUICulture);
}
var p1 = new OdbcParameter("FromDate", OdbcType.Date);
var p2 = new OdbcParameter("ToDate", OdbcType.Date);
p1.Value = fromDate.Date;
p2.Value = toDate.Date;
List <OdbcParameter> parameters = new List <OdbcParameter>()
{
p1,
p2
};
var dt = PervasiveSqlUtilities.FetchPervasiveData(sqlQuery, parameters);
foreach (DataRow row in dt.Rows)
{
result.Add(new TransactionDataItem(row, buildingPath));
}
return(result.OrderBy(a => a.TransactionDate).ToList());
}
public List <PastelMaintenanceTransaction> FetchAndLinkMaintenanceTransactions(DateTime fromDate, DateTime toDate)
{
List <PastelMaintenanceTransaction> pastelTransactions = new List <PastelMaintenanceTransaction>();
string accountList = string.Empty;
foreach (var config in _buildingConfig)
{
if (!string.IsNullOrWhiteSpace(accountList))
{
accountList = accountList + " or t.AccNumber = '" + config.PastelAccountNumber + "'";
}
else
{
accountList = " t.AccNumber = '" + config.PastelAccountNumber + "'";
}
}
string dataPath = _building.DataPath;
string sqlMaintenanceRecords = PervasiveSqlUtilities.ReadResourceScript("Astrodon.DataProcessor.Scripts.MaintenanceRecordListBetweenDates.sql");
sqlMaintenanceRecords = PervasiveSqlUtilities.SetDataSource(sqlMaintenanceRecords, dataPath);
sqlMaintenanceRecords = sqlMaintenanceRecords.Replace("[AccountList]", accountList);
sqlMaintenanceRecords = sqlMaintenanceRecords.Replace("[FromDate]", "'" + fromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "'");
sqlMaintenanceRecords = sqlMaintenanceRecords.Replace("[ToDate]", "'" + toDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "'");
foreach (DataRow row in PervasiveSqlUtilities.FetchPervasiveData(sqlMaintenanceRecords).Rows)
{
pastelTransactions.Add(new PastelMaintenanceTransaction(row, dataPath));
}
var minDate = fromDate.Date.AddDays(-7);
var maxDate = toDate.Date.AddDays(8);
if (pastelTransactions.Count > 0)
{
minDate = pastelTransactions.Min(a => a.TransactionDate).Date.AddDays(-7);
maxDate = pastelTransactions.Max(a => a.TransactionDate).Date.AddDays(8).AddSeconds(-1);
var reqList = (from r in _context.tblRequisitions
where r.trnDate >= minDate && r.trnDate <= maxDate &&
r.building == _buildingId
select r).ToList();
//match requisition transactions to pastel transactions and update changes
return(CalculateMatches(pastelTransactions, reqList));
}
return(pastelTransactions);
}
private List <PervasiveAccount> LoadAccountValues(DateTime startDate, DateTime endDate, string dataPath, string[] accountNumbers)
{
string accQry = "";
if (accountNumbers.Length == 1)
{
accQry = " = '" + accountNumbers[0] + "'";
}
else
{
accQry = " in (";
foreach (var s in accountNumbers)
{
if (accQry.EndsWith("("))
{
accQry = accQry + "'" + s + "'";
}
else
{
accQry = accQry + ",'" + s + "'";
}
}
accQry = accQry + ")";
}
string qry = "select * from [DataSet].LedgerMaster where AccNumber " + accQry;
qry = SetDataSource(qry, dataPath);
var accountData = PervasiveSqlUtilities.FetchPervasiveData(qry);
List <PervasiveAccount> accountList = new List <PervasiveAccount>();
foreach (DataRow row in accountData.Rows)
{
var dt = startDate;
while (dt <= endDate)
{
accountList.Add(new PervasiveAccount(row, GetBuildingPeriod(dt, dataPath), dt));
dt = dt.AddMonths(1);
}
}
return(accountList);
}
private List <CustomerCategory> GetCustomerCategories(string buildPath)
{
string qry = "select CCCode,CCDesc from [DataSet].CustomerCategories Order by CCCode";
qry = PervasiveSqlUtilities.SetDataSource(qry, buildPath);
List <CustomerCategory> result = new List <CustomerCategory>();
var data = PervasiveSqlUtilities.FetchPervasiveData(qry);
foreach (DataRow row in data.Rows)
{
CustomerCategory c = new CustomerCategory(row);
result.Add(c);
}
return(result);
}
public List <PeriodItem> CustomerStatementParameterLookup(int buildingId, string customerCode, DateTime processMonth, int numberOfMonths)
{
var building = _DataContext.tblBuildings.Single(a => a.id == buildingId);
var dDate = new DateTime(processMonth.Year, processMonth.Month, 1);
string sqlPeriodConfig = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.PeriodParameters.sql");
sqlPeriodConfig = SetDataSource(sqlPeriodConfig, building.DataPath);
var periodData = PervasiveSqlUtilities.FetchPervasiveData(sqlPeriodConfig);
string sqlCustomerBalances = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.CustomerBalance.sql");
sqlCustomerBalances = SetDataSource(sqlCustomerBalances, building.DataPath);
sqlCustomerBalances = sqlCustomerBalances.Replace("@CUSTOMERCODE", customerCode.Trim());
var customerBalanceData = PervasiveSqlUtilities.FetchPervasiveData(sqlCustomerBalances);
CustomerBalance custBalance = new CustomerBalance(customerBalanceData.Rows[0]);
PeriodDataItem periodItem = null;
foreach (DataRow row in periodData.Rows)
{
periodItem = new PeriodDataItem(row);
break;
}
List <PeriodItem> result = new List <PeriodItem>();
var lastPeriod = periodItem.PeriodLookup(dDate);
var firstPeriod = periodItem.PeriodLookup(lastPeriod.Start.Value.AddMonths((numberOfMonths * -1) + 1));
lastPeriod.OpeningBalance = Convert.ToDouble(custBalance.CalcOpening(lastPeriod.PeriodNumber));
lastPeriod.ClosingBalance = Convert.ToDouble(custBalance.CalcOpening(lastPeriod.PeriodNumber));
firstPeriod.OpeningBalance = Convert.ToDouble(custBalance.CalcOpening(firstPeriod.PeriodNumber));
firstPeriod.ClosingBalance = Convert.ToDouble(custBalance.CalcOpening(firstPeriod.PeriodNumber));
result.Add(firstPeriod);
result.Add(lastPeriod);
return(result);
}
private List <PaymentTransaction> FetchPaymentTransactions()
{
List <PaymentTransaction> result = new List <PaymentTransaction>();
string dataPath = _building.DataPath;
string sqlPaymentRecords = string.Empty;
sqlPaymentRecords = PervasiveSqlUtilities.ReadResourceScript("Astrodon.DataProcessor.Scripts.PaymentTransactionList.sql");
sqlPaymentRecords = PervasiveSqlUtilities.SetDataSource(sqlPaymentRecords, dataPath);
foreach (DataRow row in PervasiveSqlUtilities.FetchPervasiveData(sqlPaymentRecords).Rows)
{
result.Add(new PaymentTransaction(row, dataPath));
}
return(result);
}
public byte[] RunReport(DateTime processMonth, string buildingName, string dataPath, bool includeSundries)
{
DateTime dDate = new DateTime(processMonth.Year, processMonth.Month, 1);
int period;
List <LevyRollDataItem> data = LoadReportData(processMonth, dataPath, null, out period);
List <SundryDataItem> sundries = new List <SundryDataItem>();
if (includeSundries)
{
string sundriesQry = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.Sundries.sql");
sundriesQry = SetDataSource(sundriesQry, dataPath);
var reportDB = PervasiveSqlUtilities.FetchPervasiveData(sundriesQry, new OdbcParameter("@PPeriod", period));
foreach (DataRow row in reportDB.Rows)
{
sundries.Add(new SundryDataItem(row));
}
}
return(RunReportToPdf(data, sundries, dDate, buildingName));
}
public List <LevyRollDataItem> LoadReportData(DateTime processMonth, string dataPath, List <string> customers, out int period)
{
string customerCodeFilter = "";
if (customers != null && customers.Count > 0)
{
for (int x = 0; x < customers.Count; x++)
{
if (x == 0)
{
customerCodeFilter = " m.CustomerCode in ('" + customers[x] + "'";
}
else
{
customerCodeFilter = customerCodeFilter + ",'" + customers[x] + "'";
}
}
customerCodeFilter = customerCodeFilter + ")";
}
List <LevyRollDataItem> data;
var dDate = new DateTime(processMonth.Year, processMonth.Month, 1);
string sqlPeriodConfig = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.PeriodParameters.sql");
sqlPeriodConfig = SetDataSource(sqlPeriodConfig, dataPath);
var periodData = PervasiveSqlUtilities.FetchPervasiveData(sqlPeriodConfig);
PeriodDataItem periodItem = null;
foreach (DataRow row in periodData.Rows)
{
periodItem = new PeriodDataItem(row);
break;
}
period = 0;
try
{
period = periodItem.PeriodNumberLookup(dDate);
}
catch (Exception err)
{
throw err;
}
//run the main report query
string sqlQuery = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.LevyRollAllCustomers.sql");
sqlQuery = SetDataSource(sqlQuery, dataPath);
if (!string.IsNullOrWhiteSpace(customerCodeFilter))
{
sqlQuery = sqlQuery.Replace(" %CUSTOMERCODEFILTER%", " WHERE " + customerCodeFilter);
}
else
{
sqlQuery = sqlQuery.Replace(" %CUSTOMERCODEFILTER%", "");
}
var allMasterAccounts = PervasiveSqlUtilities.FetchPervasiveData(sqlQuery);
sqlQuery = PervasiveSqlUtilities.ReadResourceScript("Astrodon.Reports.Scripts.LevyRoll.sql");
sqlQuery = SetDataSource(sqlQuery, dataPath);
if (!string.IsNullOrWhiteSpace(customerCodeFilter))
{
sqlQuery = sqlQuery.Replace(" %CUSTOMERCODEFILTER%", " AND " + customerCodeFilter);
}
else
{
sqlQuery = sqlQuery.Replace(" %CUSTOMERCODEFILTER%", "");
}
var reportDB = PervasiveSqlUtilities.FetchPervasiveData(sqlQuery, new OdbcParameter("@PPeriod", period));
data = new List <LevyRollDataItem>();
foreach (DataRow row in reportDB.Rows)
{
data.Add(new LevyRollDataItem(row, period));
}
foreach (DataRow row in allMasterAccounts.Rows)
{
var rowItem = new LevyRollDataItem(row, period);
var currentCustomer = data.Where(a => a.CustomerCode == rowItem.CustomerCode).FirstOrDefault();
if (currentCustomer == null)
{
data.Add(rowItem);
}
}
return(data);
}
private string SetDataSource(string sqlQuery, string dataPath)
{
return(PervasiveSqlUtilities.SetDataSource(sqlQuery, dataPath));
}
private bool FixPastelCustomerCategories(string dataPath, List <CustomerCategory> categories)
{
try
{
var allowedIds = categories.Select(a => a.CategoryId).ToArray();
string ids = "(" + string.Join(",", allowedIds) + ")";
var catListOnDB = GetCustomerCategories(dataPath);
var listToUpdate = new List <CustomerCategory>();
var listToInsert = new List <CustomerCategory>();
foreach (var cat in categories)
{
var curr = catListOnDB.FirstOrDefault(a => a.CategoryId == cat.CategoryId);
if (curr == null)
{
listToInsert.Add(cat);
}
else if (curr.CategoryName != cat.CategoryName)
{
listToUpdate.Add(cat);
}
}
foreach (var i in listToInsert)
{
string q = "Insert into [DataSet].CustomerCategories (CCCode,CCDesc) values (" + i.CategoryId.ToString() + ",'" + i.CategoryName + "')";
q = PervasiveSqlUtilities.SetDataSource(q, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(q);
}
foreach (var i in listToUpdate)
{
Console.WriteLine("Update " + i.CategoryName + " for " + dataPath);
string q = "Update [DataSet].CustomerCategories set CCDesc = '" + i.CategoryName + "' where CCCode = " + i.CategoryId.ToString();
q = PervasiveSqlUtilities.SetDataSource(q, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(q);
}
string odbcQuery = "update [DataSet].CustomerMaster set Category = 0 where Category not in " + ids;
odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery);
odbcQuery = "delete from [DataSet].CustomerCategories where CCCode not in " + ids;
odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery);
return(true);
}
catch (Exception e)
{
_Context.SystemLogSet.Add(new Data.Log.SystemLog()
{
EventTime = DateTime.Now,
Message = e.Message,
StackTrace = e.StackTrace
});
}
return(false);
}
public void UpdateNoneCategory(string sql)
{
PervasiveSqlUtilities.ExecuteSQLCommand(sql);
}
