public Dictionary <string, List <decimal?> > GetTransactionsByDeptID(int?id, string Source)
{
string str = string.Concat(Source.Take(3));
Dictionary <string, List <decimal?> > byFundSource;
using (var context = new transcendenceEntities())
{
List <Transaction> allTransactions;
if (Source.Contains("--S"))
{
allTransactions = context.Transactions.Where(t => t.DeptID == id).OrderBy(m => m.TransDate).ToList();
var sum = allTransactions.Sum(g => g.TransAmount);
var getPercentages = allTransactions.GroupBy(m => m.Funding_Sources.FundCategory).Select(l => new DateObject()
{
fundName = l.Key, transAmount = ((l.Sum(k => k.TransAmount) / sum) * 100)
});
byFundSource = getPercentages.OrderBy(v => v.transAmount).GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
}
else
{
allTransactions = context.Transactions.Where(t => t.DeptID == id && t.Funding_Sources.FundCategory.Contains(str)).OrderBy(m => m.TransDate).ToList();
var sum = allTransactions.Sum(g => g.TransAmount);
var getPercentages = allTransactions.GroupBy(m => m.Funding_Sources.FundCodeName).Select(l => new DateObject()
{
fundName = l.Key, transAmount = ((l.Sum(k => k.TransAmount) / sum) * 100)
});
byFundSource = getPercentages.OrderBy(v => v.transAmount).GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
}
}
return(byFundSource);
}
public Dictionary <string, List <decimal?> > GetTransactionsByDeptID(int?id, string Source)
{
string str = string.Concat(Source.Take(3));
Dictionary <string, List <decimal?> > byFundSource;
using (var context = new transcendenceEntities())
{
List <Transaction> allTransactions;
if (Source.Contains("--S"))
{
allTransactions = context.Transactions.Where(t => t.DeptID == id).OrderBy(m => m.TransDate).ToList();
var byMonth = allTransactions.GroupBy(m => new { m.Funding_Sources.FundCategory, m.TransDate.Month }).Select(m => new DateObject()
{
fundName = m.Key.FundCategory, transAmount = m.Sum(k => k.TransAmount)
});
byFundSource = byMonth.GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
return(byFundSource);
}
else
{
allTransactions = context.Transactions.Where(t => t.DeptID == id && t.Funding_Sources.FundCategory.Contains(str)).OrderBy(m => m.TransDate).ToList();
var byMonth = allTransactions.GroupBy(m => new { m.Funding_Sources.FundCodeName, m.TransDate.Month }).Select(m => new DateObject()
{
fundName = m.Key.FundCodeName, transAmount = m.Sum(k => k.TransAmount)
});
byFundSource = byMonth.GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
return(byFundSource);
}
}
}
public IList <CategoryAmount> GetCategoriesForPie(int?year, int?id, string source, int?staff)
{
using (var context = new transcendenceEntities())
{
return(context.getCategoryPercents(year, id, source, staff));
}
}
// returns list of all years
public SelectList GetAllYears()
{
using (var db = new transcendenceEntities())
{
var allYears = db.Transactions.Select(m => new { value = m.TransDate.Year, id = m.TransDate.Year }).Distinct().ToList();
return(new SelectList(allYears, "id", "value", db.Transactions.Max(m => m.TransDate.Year)));
}
}
/* returns list of all departments by DeptID, and DeptName*/
public SelectList GetAllDepartments()
{
var allDepartments = new List <Department>();
using (var db = new transcendenceEntities())
{
allDepartments = db.Departments.ToList();
}
return(new SelectList(allDepartments, "DeptID", "DeptName"));
}
/* returns all staff that match deptID */
public List <Staff> GetStaffList(int id)
{
var allStaff = new List <Staff>();
using (var context = new transcendenceEntities())
{
allStaff = (from s in context.Staffs
where s.DeptID == id
select s).ToList();
}
return(allStaff);
}
/* returns all staff that match deptID */
public List <Staff> GetStaffList(int id)
{
var allStaff = new List <Staff>();
using (var context = new transcendenceEntities())
{
allStaff = (from s in context.Staffs
join staffdept in context.StaffDepts on s.StaffID equals staffdept.StaffID
where staffdept.DeptID == id
select s).ToList();
}
return(allStaff);
}
/* returns all funding sources that match dept ID */
public List <FundingCategoryObject> GetFundingCategoryList(int id)
{
var fundCategoryList = new List <FundingCategoryObject>();
using (var context = new transcendenceEntities())
{
var allFundSources = (from s in context.Transactions
where s.DeptID == id
select s).ToList();
fundCategoryList = allFundSources.GroupBy(g => new { text = g.Funding_Sources.FundCategory }).Select(m => new FundingCategoryObject {
value = m.Key.text, text = m.Key.text
}).ToList();
}
return(fundCategoryList);
}
public Dictionary <string, List <decimal?> > GetEmployeeTransactions(int?id, int?empl)
{
Dictionary <string, List <decimal?> > byFundSource;
using (var context = new transcendenceEntities())
{
var allTransactions = context.Transactions.Where(g => g.DeptID == id && g.StaffID == empl).OrderBy(m => m.TransDate).ToList();
var byMonth = allTransactions.GroupBy(m => new { m.Funding_Sources.FundCategory, m.TransDate.Month }).Select(m => new DateObject()
{
fundName = m.Key.FundCategory, transAmount = m.Sum(k => k.TransAmount)
});
byFundSource = byMonth.GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
}
return(byFundSource);
}
public Dictionary <string, List <decimal?> > GetAllTransactions()
{
Dictionary <string, List <decimal?> > test;
using (var context = new transcendenceEntities())
{
var allTransactions = context.Transactions.OrderBy(m => m.TransDate).ToList();
var byFundSource = allTransactions.GroupBy(m => new { m.Funding_Sources.FundCategory, m.TransDate.Month }).Select(m => new DateObject()
{
fundName = m.Key.FundCategory, transAmount = m.Sum(k => k.TransAmount)
});
test = byFundSource.GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
}
//var t = transactions.Select(m => new { value = m.FundMasterID, text = m.Funding_Sources.FundCodeName }).GroupBy(m => m.text);
return(test);
}
public List <FundingCategoryObject> GetFundCodeNameList(int id, string text)
{
string compare = string.Concat(text.Take(3));
var fundCodeNameList = new List <FundingCategoryObject>();
using (var context = new transcendenceEntities())
{
var allFundSources = (from s in context.Transactions
where s.DeptID == id && s.Funding_Sources.FundCategory.Contains(compare)
select s).ToList();
fundCodeNameList = allFundSources.GroupBy(g => new { value = g.FundMasterID, text = g.Funding_Sources.FundCodeName }).Select(m => new FundingCategoryObject {
value = m.Key.value, text = m.Key.text
}).ToList();
}
return(fundCodeNameList);
}
public Dictionary <string, List <decimal?> > GetEmployeeTransactions(int?id, int?empl)
{
Dictionary <string, List <decimal?> > byFundSource;
using (var context = new transcendenceEntities())
{
var allTransactions = context.Transactions.Where(g => g.DeptID == id && g.StaffID == empl).OrderBy(m => m.TransDate).ToList();
var sum = allTransactions.Sum(g => g.TransAmount);
var getPercentages = allTransactions.GroupBy(m => m.Funding_Sources.FundCategory).Select(l => new DateObject()
{
fundName = l.Key, transAmount = ((l.Sum(k => k.TransAmount) / sum) * 100)
});
byFundSource = getPercentages.OrderBy(v => v.transAmount).GroupBy(m => m.fundName).ToDictionary(t => t.Key, t => t.Select(g => g.transAmount).ToList());
}
return(byFundSource);
}
private string ProcessStaffBridge(Stream inputStream)
{
string rowsCopied;
Dictionary <string, KeyValuePair <string, Type> > columnMaps = new Dictionary <string, KeyValuePair <string, Type> > {
{ "staffcode_c", new KeyValuePair <string, Type>("StaffID", typeof(int)) },
{ "DeptName", new KeyValuePair <string, Type>("DeptID", typeof(int)) }
};
rowsCopied = Process(inputStream, "StaffDept", columnMaps.Values.ToDictionary(kvp => kvp.Key, kvp => kvp.Value), (csvreader, dataTable) =>
{
var row = dataTable.NewRow();
string deptName;
int staffID = 0, deptID = 0;
foreach (KeyValuePair <string, KeyValuePair <string, Type> > item in columnMaps)
{
if (item.Value.Key == "DeptID")
{
deptName = csvreader.GetField(item.Key);
deptID = _departments[deptName];
row[item.Value.Key] = deptID;
}
else
{
staffID = Cast(csvreader.GetField(item.Key), item.Value.Value);
row[item.Value.Key] = staffID;
}
}
using (var context = new transcendenceEntities())
if (context.StaffDepts.Where(sd => sd.DeptID == deptID && sd.StaffID == staffID).FirstOrDefault() == null)
{
dataTable.Rows.Add(row);
}
});
return(rowsCopied);
}
private string ProcessStaff(Stream inputStream)
{
string rowsCopied;
List <int> staffIDs;
Dictionary <string, KeyValuePair <string, Type> > columnMaps = new Dictionary <string, KeyValuePair <string, Type> > {
{ "staffcode_c", new KeyValuePair <string, Type>("StaffID", typeof(int)) },
{ "StaffName", new KeyValuePair <string, Type>("StaffName", typeof(string)) }
};
using (var context = new transcendenceEntities())
staffIDs = context.Staffs.Select(s => s.StaffID).ToList();
rowsCopied = Process(inputStream, "Staff", columnMaps.Values.ToDictionary(kvp => kvp.Key, kvp => kvp.Value), (csvreader, dataTable) =>
{
int staffID = Cast(csvreader.GetField("staffcode_c"), typeof(int));
if (!staffIDs.Contains(staffID))
{
var row = dataTable.NewRow();
foreach (KeyValuePair <string, KeyValuePair <string, Type> > item in columnMaps)
{
if (item.Value.Key == "StaffID")
{
row[item.Value.Key] = staffID;
staffIDs.Add(staffID);
}
else
{
row[item.Value.Key] = Cast(csvreader.GetField(item.Key), item.Value.Value);
}
}
dataTable.Rows.Add(row);
}
});
return(rowsCopied);
}
private string ProcessTransactions(Stream inputStream)
{
string rowsCopied;
//Mapping columns programmatically (need database tables to match csv columns)
//Dictionary<string, Type> columns = PropertiesToDictionary(typeof(Transaction), p => !p.GetGetMethod().IsVirtual && p.Name != "TransAmount");
//Map csv columns to sql table columns and data types (done manually for now)
Dictionary <string, KeyValuePair <string, Type> > columnMaps = new Dictionary <string, KeyValuePair <string, Type> > {
{ "uniqueid_c", new KeyValuePair <string, Type>("UniqueID", typeof(int)) },
{ "DeptName", new KeyValuePair <string, Type>("DeptID", typeof(int)) }, { "staffcode_c", new KeyValuePair <string, Type>("StaffID", typeof(int)) }, { "psplanmasterid_c", new KeyValuePair <string, Type>("FundMasterID", typeof(string)) },
{ "transactioncode_c", new KeyValuePair <string, Type>("TransType", typeof(string)) }, { "transactiondate_d", new KeyValuePair <string, Type>("TransDate", typeof(DateTime)) }, { "tranfer", new KeyValuePair <string, Type>("TransTransfer", typeof(decimal)) },
{ "adj", new KeyValuePair <string, Type>("TransAdjustment", typeof(decimal)) }, { "credit", new KeyValuePair <string, Type>("TransCredit", typeof(decimal)) }, { "charge", new KeyValuePair <string, Type>("TransCharge", typeof(decimal)) }
};
rowsCopied = Process(inputStream, "Transactions", columnMaps.Values.ToDictionary(kvp => kvp.Key, kvp => kvp.Value), (csvreader, dataTable) =>
{
var row = dataTable.NewRow();
string transType = "";
int uniqueID = 0;
DateTime transDate = new DateTime();
foreach (KeyValuePair <string, KeyValuePair <string, Type> > item in columnMaps)
{
if (item.Value.Key == "DeptID")
{
row[item.Value.Key] = _departments[csvreader.GetField(item.Key)];
}
else
{
var field = Cast(csvreader.GetField(item.Key), item.Value.Value);
switch (item.Value.Key)
{
case "UniqueID":
uniqueID = Cast(csvreader.GetField(item.Key), item.Value.Value);
break;
case "TransDate":
transDate = Cast(csvreader.GetField(item.Key), item.Value.Value);
break;
case "TransType":
transType = Cast(csvreader.GetField(item.Key), item.Value.Value);
break;
}
row[item.Value.Key] = field;
}
}
using (var context = new transcendenceEntities())
{
if (context.Transactions.Where(t => t.UniqueID == uniqueID && t.TransType == transType &&
t.TransDate == transDate).FirstOrDefault() != null)
{
throw new DuplicateNameException(string.Format("Transaction record with uniqueid ({0}), date ({1}), and code ({2}) already exists.", uniqueID.ToString(),
transDate.ToString("d"), transType));
}
}
dataTable.Rows.Add(row);
});
return(rowsCopied);
}
public ImportServices()
{
using (var context = new transcendenceEntities())
_departments = context.Departments.ToDictionary(d => d.DeptName.Replace("\r\n", "").Trim(), d => d.DeptID);
}
private string Process(Stream inputStream, string tableName, Dictionary <string, Type> columnMaps, ProcessDelegate processFile)
{
int readCount = 0, notifyCount = 0, notifyAfter = 10, batchSize = 500;
long rowsCopied = 0;
using (var context = new transcendenceEntities())
{
var conn = (SqlConnection)context.Database.Connection;
conn.Open();
using (var transaction = conn.BeginTransaction(IsolationLevel.Serializable))
{
using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.NotifyAfter = notifyAfter;
bulkCopy.SqlRowsCopied += (sender, e) =>
{
rowsCopied = e.RowsCopied;
notifyCount++;
e.Abort = IsCanceled;
};
List <DataColumn> dataColumns = new List <DataColumn>();
DataTable dataTable = new DataTable(tableName);
foreach (KeyValuePair <string, Type> item in columnMaps)
{
dataColumns.Add(new DataColumn(item.Key, item.Value));
bulkCopy.ColumnMappings.Add(item.Key, item.Key);
}
dataTable.Columns.AddRange(dataColumns.ToArray());
using (var csvreader = new CsvReader(new StreamReader(inputStream)))
{
bool empty = !csvreader.Read();
while (!empty)
{
try
{
//Parse through csv fields and store them
//Add range to the data table
processFile(csvreader, dataTable);
readCount++;
empty = !csvreader.Read();
if (readCount == batchSize || empty)
{
bulkCopy.WriteToServer(dataTable);
if (empty && dataTable.Rows.Count > 0)
{
rowsCopied += dataTable.Rows.Count - notifyAfter * notifyCount;
}
dataTable.Rows.Clear();
readCount = 0;
}
}
catch (Exception ex)
{
transaction.Rollback();
bulkCopy.Close();
dataTable.Rows.Clear();
conn.Close();
throw;
}
}
} //CsvReader closed
} //SqlBulkCopy closed
transaction.Commit();
conn.Close();
} //Transaction closed
} //dbcontext closed
return(rowsCopied.ToString());
} //function closed
