public JsonResult <HeroRespond> GetAllHeroes() { HeroRespond heroRespond = new HeroRespond(); using (var db = new pubsEntities()) { try { var heroes = db.heroes.ToList(); List <hero> heroList = heroes.ToList(); if (heroList.Count() > 0) { heroRespond.Massage = "Success"; heroRespond.Status = "200"; heroRespond.Heroes = heroList; } else { heroRespond.Massage = "Fail"; heroRespond.Status = "-1"; heroRespond.Heroes = null; } } catch (Exception e) { heroRespond.Massage = "Error: " + e.Message; heroRespond.Status = "-404"; heroRespond.Heroes = null; } return(Json(heroRespond)); } }
public static publisher GetSinglePub(string pubID) { using (pubsEntities context = new pubsEntities()) { return(context.publishers.FirstOrDefault(x => x.pub_id == pubID)); } }
// GET: Report2 public ActionResult Index(DateTime?date_from, DateTime?date_to, string store) { string mindt_string = "04/01/1980 00:00:00 GMT+0530 (India Standard Time)"; DateTime maxdt = DateTime.Now; DateTime mindt; DateTime.TryParseExact(mindt_string, "mm/dd/yyyy hh:mm:ss 'GMT'zzz '(India Standard Time)'", CultureInfo.InvariantCulture, DateTimeStyles.None, out mindt); pubsEntities db = new pubsEntities(); List <stores> storesname = db.stores.ToList(); List <sales> salesname = db.sales.ToList(); List <titles> titlesname = db.titles.ToList(); var m = from a in storesname join b in salesname on a.stor_id equals b.stor_id join c in titlesname on b.title_id equals c.title_id where ((b.ord_date >= date_from && b.ord_date <= date_to && a.stor_name.Contains(store)) || (b.ord_date >= date_from && b.ord_date <= maxdt && a.stor_name.Contains(store)) || (b.ord_date >= mindt && b.ord_date <= date_to && a.stor_name.Contains(store)) || (date_from == null && date_to == null && store == null)) select new Report2 { storesdetails = a, salesdetails = b, titledetails = c }; return(View(m.ToList())); }
public static void AlertSMS(string Message) { pubsEntities dbctx = new pubsEntities(); try { int AlertUsersCount = Convert.ToInt16(System.Configuration.ConfigurationSettings.AppSettings["AlertUsers"]); string AlertMSISDNKey = "AlertMSISDN"; List <string> AlertMSISDNs = new List <string>(); for (int i = 1; i <= AlertUsersCount; i++) { string AlertMSISDN = System.Configuration.ConfigurationSettings.AppSettings[AlertMSISDNKey + i.ToString()]; AlertMSISDNs.Add(AlertMSISDN); } foreach (string MSISDN in AlertMSISDNs) { dbctx.receives.Add(new receive { prefix = "RG", message = Message, scode = "96900", sender = MSISDN, dates = DateTime.Now.ToShortDateString(), times = DateTime.Now.ToShortTimeString(), flag = "NO" }); } dbctx.SaveChanges(); } catch (Exception ex) { log.Warn(ex.Message + " " + (ex.InnerException != null ? ex.InnerException.ToString() : "") + " " + " Some Issue at the Database Interaction. Alert SMS not sent!"); } }
public void SavePublisher(string pubID, string name, string city) { Validate._Error e = new Validate._Error(); e.Msg = string.Empty; e.Caught = false; if (!Validate.IsFieldPopulated(name.Trim())) { e = Validate.UpdateErrorObject(e, "Publisher name cannot be blank.", true); } else { if (!Validate.IsStringShortEnough(name, 40)) { e = Validate.UpdateErrorObject(e, "Publisher name cannot be longer than 40 characters.", true); } } if (!Validate.IsStringShortEnough(city, 20)) { e = Validate.UpdateErrorObject(e, "City cannot be more than 20 characters.", true); } //If errors caught then throw exception with error message if (e.Caught) { throw new Exception(e.Msg); } //if we made it this far then no errors found using (pubsEntities context = new pubsEntities()) { publisher pub = context.publishers.FirstOrDefault(x => x.pub_id == pubID); pub.pub_name = name.Trim(); pub.city = city.Trim(); context.publishers.ApplyCurrentValues(pub); context.SaveChanges(); } }
/// <summary> /// Retrieves a list of publishers from the database /// </summary> /// <returns>List collection of type publisher</returns> public static List <publisher> GetPublishers() { using (pubsEntities context = new pubsEntities()) { IEnumerable <publisher> pList = context.publishers.OrderBy(x => x.pub_name); return(pList.ToList()); } }
public ActionResult RestoreDatabase() { string dbPath = Server.MapPath("~/BackupDB/DBBackup.bak"); using (var db = new pubsEntities()) { var cmd = String.Format("USE master restore DATABASE pubs from DISK='{0}' WITH REPLACE;", dbPath); db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, cmd); } return(View()); }
// GET: BackUp public ActionResult BackupDatabase() { string dbPath = Server.MapPath("~/BackupDB/DBBackup.bak"); using (var db = new pubsEntities()) { var cmd = String.Format("BACKUP DATABASE {0} TO DISK='{1}' WITH FORMAT, MEDIANAME='DbBackups', MEDIADESCRIPTION='Media set for {0} database';" , "pubs", dbPath); db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, cmd); } return(new FilePathResult(dbPath, "application/octet-stream")); }
/// <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()); }
public static void AddPub(string pubName, string city, string state) { StringBuilder error = new StringBuilder(); bool eCaught = false; if (pubName.Trim().Length > 40 || pubName.Trim().Length == 0) { eCaught = true; error.Append("Publisher Name cannot be blank and cannot be longer than 40 characters. "); } if (city.Trim().Length > 20) { eCaught = true; error.Append("City cannot be longer than 20 characters."); } if (eCaught) { throw new Exception(error.ToString()); } using (pubsEntities context = new pubsEntities()) { publisher pub = new publisher(); //pub_id code must be a 4 digit code beginning with 99. get list of existing and find next available code int nextCode = 9900; string strNextCode = nextCode.ToString(); while (context.publishers.Any(x => x.pub_id == strNextCode)) { nextCode++; strNextCode = nextCode.ToString(); } pub.pub_id = strNextCode; pub.pub_name = pubName.Trim(); pub.city = city.Trim(); pub.state = state; try { context.publishers.AddObject(pub); context.SaveChanges(); } catch { throw new Exception("Sorry, the maximum number of publishers has been exceeded. Your publisher cannot be added."); } } }
/// <summary> /// Method for retrieving a given publisher's PR and sales contacts /// </summary> /// <param name="pubID">Publisher's ID</param> /// <returns>List of type _Contact</returns> public static List <_Contact> GetContacts(string pubID) { List <_Contact> contacts = new List <_Contact>(); using (pubsEntities context = new pubsEntities()) { IEnumerable <employee> employees = context.employees.Where(x => x.pub_id == pubID && (x.job_id == 8 || x.job_id == 13)); foreach (employee e in employees) { _Contact c = new _Contact(); c.JobTitle = e.job.job_desc; c.Name = e.lname + ", " + e.fname; contacts.Add(c); } return(contacts.OrderBy(x => x.JobTitle).ThenBy(x => x.Name).ToList()); } }
public ActionResult Index(DateTime?date_from, DateTime?date_to, int t = 100) { string mindt_string = "04/01/1980 00:00:00 GMT+0530 (India Standard Time)"; DateTime maxdt = DateTime.Now; DateTime mindt; DateTime.TryParseExact(mindt_string, "mm/dd/yyyy hh:mm:ss 'GMT'zzz '(India Standard Time)'", CultureInfo.InvariantCulture, DateTimeStyles.None, out mindt); pubsEntities db = new pubsEntities(); List <authors> authorsname = db.authors.ToList(); List <titleauthor> titleauthorsname = db.titleauthor.ToList(); List <titles> titlesname = db.titles.ToList(); var m = (from a in authorsname join b in titleauthorsname on a.au_id equals b.au_id join c in titlesname on b.title_id equals c.title_id where (c.pubdate >= date_from && c.pubdate <= date_to) || (c.pubdate >= date_from && c.pubdate <= maxdt) || (c.pubdate >= mindt && c.pubdate <= date_to) || (date_from == null && date_to == null) orderby c.ytd_sales descending select new Report1 { authorsdetails = a, titleauthordetails = b, titledetails = c }).Take(t); return(View(m.ToList())); }
public ActionResult DownloadBackupFile() { if (LoginController.shouldRedirectToLogin(this)) { return(RedirectToAction("Index", "Login")); } string backup_dir = Path.Combine(Path.GetTempPath(), "mvc_ergasia_db"); string backup_file = "pubs-" + DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".Bak"; Directory.CreateDirectory(backup_dir); pubsEntities db = new pubsEntities(); SqlConnection sqlCon = (SqlConnection)(db.Database.Connection); sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand("backup database pubs to disk='" + Path.Combine(backup_dir, backup_file) + "'", sqlCon); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); db.Dispose(); byte[] fileBytes = System.IO.File.ReadAllBytes(Path.Combine(backup_dir, backup_file)); return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, backup_file)); }
public HomeController() { db = new pubsEntities(); }