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