/// <summary> /// Calculates a set of royalties per title/order, per author /// </summary> /// <param name="pubID">The ID of the publisher</param> /// <returns>List collection of type _Royalty</returns> public static List <_Royalty> GetRoyalties(string pubID) { List <_Royalty> royalties = new List <_Royalty>(); using (pubsEntities context = new pubsEntities()) { //First get the publisher selected publisher pub = context.publishers.FirstOrDefault(x => x.pub_id == pubID); //Get list of all sales for the selected pub IEnumerable <sale> orders = context.sales.Where(x => x.title.pub_id == pubID); //Iterate through the orders and calculate the royalty for each foreach (sale o in orders) { IEnumerable <titleauthor> tauthors = context.titleauthors.Where(x => x.title_id == o.title_id); foreach (titleauthor ta in tauthors) { _Royalty r = new _Royalty(); r.StoreName = o.store.stor_name; r.OrderNumber = o.ord_num; r.OrderDate = o.ord_date.ToShortDateString(); r.Title = o.title.title1; r.Title = ta.title.title1; r.Author = ta.author.au_lname + ", " + ta.author.au_fname; //Now calculate the royalty for the current order, as long as the price and royalty are not missing for the order title if (o.title.price != null && o.title.royalty != null) { decimal saleAmount = (decimal)o.qty * Convert.ToDecimal(o.title.price); //I'm making the assumption that the Royalty field in table title will get updated automatically from table roysched when the next title sales qty threshold is reached decimal royaltyRate = Convert.ToDecimal(o.title.royalty) / 100; decimal royaltyPercent = Convert.ToDecimal(ta.royaltyper) / 100; //calculate the royalty amount and convert it to a string formatted as currency //r.Royalty = String.Format("{0:C}", (saleAmount * royaltyRate * royaltyPercent)); r.Royalty = (saleAmount * royaltyRate * royaltyPercent).ToString("C"); } else { //No price for the current title is set in the db, so royalty cannot be computed r.Royalty = "Not available. No price set."; } //add the r object to the list royalties.Add(r); } } } //sort the list by author, before returning return(royalties.OrderBy(x => x.StoreName).ThenBy(x => x.OrderNumber).ThenBy(x => x.Author).ToList()); }
/// <summary> /// Calculates a set of royalties per title/order, per author /// </summary> /// <param name="pubID">The ID of the publisher</param> /// <returns>List collection of type _Royalty</returns> public static List<_Royalty> GetRoyalties(string pubID) { List<_Royalty> royalties = new List<_Royalty>(); using (pubsEntities context = new pubsEntities()) { //First get the publisher selected publisher pub = context.publishers.FirstOrDefault(x => x.pub_id == pubID); //Get list of all sales for the selected pub IEnumerable<sale> orders = context.sales.Where(x => x.title.pub_id == pubID); //Iterate through the orders and calculate the royalty for each foreach (sale o in orders) { IEnumerable<titleauthor> tauthors = context.titleauthors.Where(x => x.title_id == o.title_id); foreach (titleauthor ta in tauthors) { _Royalty r = new _Royalty(); r.StoreName = o.store.stor_name; r.OrderNumber = o.ord_num; r.OrderDate = o.ord_date.ToShortDateString(); r.Title = o.title.title1; r.Title = ta.title.title1; r.Author = ta.author.au_lname + ", " + ta.author.au_fname; //Now calculate the royalty for the current order, as long as the price and royalty are not missing for the order title if (o.title.price != null && o.title.royalty != null) { decimal saleAmount = (decimal)o.qty * Convert.ToDecimal(o.title.price); //I'm making the assumption that the Royalty field in table title will get updated automatically from table roysched when the next title sales qty threshold is reached decimal royaltyRate = Convert.ToDecimal(o.title.royalty) / 100; decimal royaltyPercent = Convert.ToDecimal(ta.royaltyper) / 100; //calculate the royalty amount and convert it to a string formatted as currency //r.Royalty = String.Format("{0:C}", (saleAmount * royaltyRate * royaltyPercent)); r.Royalty = (saleAmount * royaltyRate * royaltyPercent).ToString("C"); } else { //No price for the current title is set in the db, so royalty cannot be computed r.Royalty = "Not available. No price set."; } //add the r object to the list royalties.Add(r); } } } //sort the list by author, before returning return royalties.OrderBy(x=>x.StoreName).ThenBy(x=>x.OrderNumber).ThenBy(x => x.Author).ToList(); }