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