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