private void deleteDB()
{
String ConnectionString = @"isostore:/BillDB.sdf";
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
if (!context.DatabaseExists())
{
context.DeleteDatabase();
}
}
MessageBox.Show("Successfully deleted database.");
}
public MainPage()
{
InitializeComponent();
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
if (!context.DatabaseExists())
{
// create database if it does not exist
context.CreateDatabase();
}
}
//deleteIsolatedStorage();
}
public static int AddGroup(String group_name)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Group group = new Group();
group.Name = group_name;
group.Created = DateTime.Now;
context.Groups.InsertOnSubmit(group);
context.SubmitChanges();
return group.ID;
}
}
public static int AddMember(int group_id, String member_name, String email, string phone_number)
{
// Get associated Group for group_id
IList<Group> groupList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Group> query = from c in context.Groups where c.ID == group_id select c;
groupList = query.ToList();
Member member = new Member();
member.Name = member_name;
member.Active = true;
member.Email = email;
member.Phone = phone_number;
member.Group = groupList.FirstOrDefault();
context.Members.InsertOnSubmit(member);
context.SubmitChanges();
return member.ID;
}
}
public static int AddItem(int group_id, String item_name, String item_desc, DateTime due)
{
// Get associated Group for group_id
IList<Group> groupList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Group> query = from c in context.Groups where c.ID == group_id select c;
groupList = query.ToList();
Item item = new Item();
item.Title = item_name;
item.Description = item_desc;
item.Created = DateTime.Now;
item.Due = due;
item.Group = groupList.FirstOrDefault();
context.Items.InsertOnSubmit(item);
context.SubmitChanges();
return item.ID;
}
}
public static IList<Item> GetOwedItems(int member_id)
{
IList<Item> itemList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Item> query = from c in context.Transactions where (c.MemberID == member_id && c.Amount < 0) select c.Item;
itemList = query.ToList();
}
return itemList;
}
public static decimal GetMemberTotal(int member_id)
{
IList<Transaction> transactionList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions where c.MemberID == member_id select c;
transactionList = query.ToList();
}
decimal cost = 0;
foreach (Transaction transaction in transactionList)
{
cost += transaction.Amount;
}
return cost;
}
public static string GetMemberGroupName(int member_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
return (from c in context.Members where c.ID == member_id select c).FirstOrDefault().Group.Name;
}
}
public static IList<Member> GetAllMembers(int group_id)
{
IList<Member> memberList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Member> query = from c in context.Members where c.Group.ID == group_id select c;
memberList = query.ToList();
}
return memberList;
}
public static void setMemberActivity(int member_id, bool active)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Member member = (from c in context.Members where c.ID == member_id select c).Single();
member.Active = active;
context.SubmitChanges();
}
}
public static void removeItem(int item_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions where c.ItemID == item_id select c;
context.Transactions.DeleteAllOnSubmit(query);
Item item = (from c in context.Items where c.ID == item_id select c).Single();
context.Items.DeleteOnSubmit(item);
context.SubmitChanges();
}
}
public static void PrintItem(String group_name)
{
IList<Item> itemList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Item> query = from c in context.Items where c.Group.Name == group_name select c;
itemList = query.ToList();
}
StringBuilder messageBuilder = new StringBuilder();
messageBuilder.AppendLine("Items: ");
foreach (Item item in itemList)
{
messageBuilder.AppendLine(item.Title + " - " + item.Description);
}
MessageBox.Show(messageBuilder.ToString());
}
public static IList<Item> GetItems(int group_id)
{
IList<Item> itemList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Item> query = from c in context.Items orderby c.Created where c.GroupID == group_id select c;
itemList = query.ToList();
}
return itemList;
}
public static decimal GetItemCost(int item_id)
{
IList<Transaction> transactionList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions where c.ItemID == item_id select c;
transactionList = query.ToList();
}
decimal cost = 0;
foreach (Transaction transaction in transactionList)
{
if (transaction.Amount < 0)
cost += transaction.Amount;
}
return cost * -1;
}
public static Item GetItem(int item_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Item item = (from c in context.Items where c.ID == item_id select c).Single();
return item;
}
}
public static IList<Group> GetGroups()
{
IList<Group> groupList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Group> query = from c in context.Groups select c;
groupList = query.ToList();
}
return groupList;
}
/*******************************
* Groups
******************************* */
public static string GetGroupNameByGroupID(int group_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
return (from c in context.Groups where c.ID == group_id select c).Single().Name;
}
}
public static string GetGroupName(int item_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
return (from c in context.Items where c.ID == item_id select c).Single().Group.Name;
}
}
/*******************************
* Transactions
******************************* */
public static IList<Transaction> GetTransactions()
{
IList<Transaction> transactionList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions select c;
transactionList = query.ToList();
}
return transactionList;
}
public static bool IsSplitEvenly(int item_id)
{
IList<Transaction> transactionList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions where c.ItemID == item_id select c;
transactionList = query.ToList();
}
decimal cost = 0;
bool is_split_even = false;
int num_owers = 0;
foreach (Transaction transaction in transactionList)
{
if (!is_split_even && transaction.Amount < 0)
{ // if the transaction is for amount owned, hold onto the cost
cost = transaction.Amount;
is_split_even = true;
num_owers = 1;
}
else if (cost == transaction.Amount)
num_owers++;
}
if (num_owers == 1)
is_split_even = false;
return is_split_even;
}
public static IList<Item> GetItemsSortByDueDate()
{
IList<Item> itemList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Item> query = from c in context.Items orderby c.Due descending select c;
itemList = query.ToList();
}
return itemList;
}
public static IList<Transaction> GetItemTransactions(int item_id)
{
IList<Transaction> transactionList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Transaction> query = from c in context.Transactions where c.ItemID == item_id select c;
transactionList = query.ToList();
}
return transactionList;
}
public static int AddTransaction(int item_id, int member_id, decimal amount)
{
// Get associated Item for item_id and Member for member_id
IList<Item> itemList = null;
IList<Member> memberList = null;
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Item> query1 = from c in context.Items where c.ID == item_id select c;
itemList = query1.ToList();
IQueryable<Member> query2 = from c in context.Members where c.ID == member_id select c;
memberList = query2.ToList();
Transaction transaction = new Transaction();
transaction.Amount = amount;
transaction.Member = memberList.FirstOrDefault();
transaction.Item = itemList.FirstOrDefault();
context.Transactions.InsertOnSubmit(transaction);
context.SubmitChanges();
return transaction.ID;
}
}
/*******************************
* Misc Functions
******************************* */
public static void sendLedgerEmail(int group_id)
{
StringBuilder messageBuilder = new StringBuilder();
messageBuilder.AppendLine("Ledger for ");
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
IQueryable<Group> gquery = from c in context.Groups where c.ID == group_id select c;
IList<Group> group = gquery.ToList();
messageBuilder.AppendLine("Group Name: " + group.FirstOrDefault().Name + "\n==========================\n");
IQueryable<Member> mquery = from c in context.Members where c.Group.ID == group_id select c;
IList<Member> members = mquery.ToList();
string membernames = "";
foreach (Member member in members)
{
membernames = membernames + member.Name + ", ";
}
messageBuilder.AppendLine("Members:\n" + membernames + "\n==========================\n");
IQueryable<Item> iquery = from c in context.Items where c.Group.ID == group_id select c;
IList<Item> items = iquery.ToList();
foreach (Item item in items)
{
messageBuilder.AppendLine("\nItem: " + item.Title + " (" + item.ID.ToString() + ")");
messageBuilder.AppendLine("Transactions:\n");
IQueryable<Transaction> tquery = from c in context.Transactions where c.ItemID == item.ID orderby c.Member.Name select c;
IList<Transaction> transactions = tquery.ToList();
foreach (Transaction transaction in transactions)
{
messageBuilder.AppendLine("\t" + transaction.Member.Name + ": " + transaction.Amount);
}
}
}
EmailComposeTask email = new EmailComposeTask();
email.Body = messageBuilder.ToString();
email.Subject = "BillSync ledger for group ID " + group_id.ToString();
email.To = "*****@*****.**";
email.Show();
}
public static void EditItem(int item_id, String item_name, String item_desc, DateTime due)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Item item = (from c in context.Items where c.ID == item_id select c).Single();
item.Title = item_name;
item.Description = item_desc;
item.Due = due;
context.SubmitChanges();
}
}
public static void ChangeDate(int item_id, DateTime datetime)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Item item = (from c in context.Items where c.ID == item_id select c).Single();
item.Created = datetime;
context.SubmitChanges();
}
}
public static void EditMember(int member_id, String member_name, String email, string phone_number)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Member member = (from c in context.Members where c.ID == member_id select c).Single();
member.Name = member_name;
member.Email = email;
member.Phone = phone_number;
context.SubmitChanges();
}
}
public static Member GetMemberByGroupID(int group_id)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Member member = (from c in context.Members where c.Group.ID == group_id select c).Single();
return member;
}
}
public static void EditGroup(int group_id, String group_name)
{
using (GroupDataContext context = new GroupDataContext(ConnectionString))
{
Group group = (from c in context.Groups where c.ID == group_id select c).Single();
group.Name = group_name;
context.SubmitChanges();
}
}
