private void Form1_Load(object sender, EventArgs e)
{
var db = new PetaPoco.Database("tencentcloud");
//To query a scalar
foreach(var a in db.Query<article>("select * from articles"))
{
listBox1.Items.Add(string.Format("{0}-{1}", a.article_id, a.title));
}
listBox1.Items.Add("\r\n");
long count = db.ExecuteScalar<long>("select Count(*) from articles");
listBox1.Items.Add(string.Format("count: {0}",count ));
listBox1.Items.Add("\r\n");
//@0 代表占位符 SingleOrDefault
var abc = db.SingleOrDefault<article>("select * from articles where article_id=@0",1);
listBox1.Items.Add(abc);
listBox1.Items.Add("\r\n");
//Paged Fetches 分页
var result = db.Page<article>(1, 3, "select * from articles where draft=1 order by date_created ");
foreach (var temp in result.Items)
{
listBox1.Items.Add(string.Format("title: {0}", temp.title));
}
listBox1.Items.Add("\r\n");
listBox1.Items.Add("结束");
}
public IHttpActionResult AddActivity([FromBody] PPTExercise model)
{
var userName = User.Identity.Name;
try
{
var db = new PetaPoco.Database("AuthContext");
var id = db.SingleOrDefault <string>("SELECT Id from AspNetUsers where Username=@0", userName);
var key = db.SingleOrDefault <string>("SELECT ProviderKey from AspNetUserLogins where UserId=@0", id);
var result = HealthGraphInteractions.AddActivity(model, key);
if (result)
{
var user = _repo.FindByUsername(userName);
user.CompletedExercises.Add(new PPTExerciseViewModel(model));
_repo.SaveChanges();
return(Ok());
}
return(InternalServerError());
}
catch (Exception e)
{
Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(e));
return(null);
}
}
public void QuestionUpdateOnDbSide(Model.Question q)
{
var a = _db.SingleOrDefault <Question>($"SELECT * FROM {_tblQ} WHERE question_id={q.Id}");
a.hidden_from_ui = q.HiddenFromUi;
a.shown_only_for_doctors = q.ShownOnlyForDoctors;
a.text = q.Text;
_db.Update(a);
}
/// <summary>
/// 新增任务
/// </summary>
/// <param name="taskRequestData">新增的数据</param>
/// <param name="page"></param>
/// <returns></returns>
public async Task <ResponseData> AddTask(TaskRequestData taskRequestData, Page page)
{
if (!helperFunction.CheckForNull(taskRequestData))
{
string Message = "任务名字 和 任务类型 和 业务类型 和 预定时间 不能为空";
return(new ResponseData(Message, "", StatusCode.Fail));
}
var taskData = mapper.Map <TaskData>(taskRequestData);
var NameIsExists = Db.SingleOrDefault <TaskData>("where Task_Name = @0", taskData.Task_Name);
var TimeSpanDate = Convert.ToDateTime(taskRequestData.Task_PresetTime) - DateTime.Now;
if (NameIsExists != null)
{
return(new ResponseData("任务名，已存在", "", StatusCode.Fail));
}
if (Convert.ToInt32(TimeSpanDate.Seconds) < 0)
{
return(new ResponseData("执行时间 必须大于 当前时间", "", StatusCode.Fail));
}
switch (taskData.Task_TaskType)
{
// 添加一次性任务
case "OneOff":
taskData.Task_CreateTime = DateTime.Now.ToString("F");
taskData.Task_PresetTime = DateTime.Now.ToString("F");
taskData.Task_ExecuteReuslt = 0;
taskData.Task_Isexists = 1;
await Db.InsertAsync(taskData);
await formalBusiness.AddOneOffTask(taskData);
return(new ResponseData("增加一次性任务成功", await Db.PageAsync <TaskData>(page.PageNumber, page.PageSize, "where Task_Isexists = 1"), StatusCode.Success));
// 添加定时任务
case "TimedTask":
if (string.IsNullOrEmpty(taskData.Task_PresetTime))
{
return(new ResponseData("增加任务失败,必须指定执行时间", "", StatusCode.Success));
}
var dateTimeMap = await formalBusiness.AddTimedTask(taskData);
taskData.Task_CreateTime = DateTime.Now.ToString("F");
var TimeDifference = (dateTimeMap.ExDateTime - DateTime.Now).TotalSeconds;
//taskData.Task_IntervalTime = helperFunction.CheckDateTime(TimeDifference);
taskData.Task_ExecuteReuslt = 0;
taskData.Task_Isexists = 1;
await Db.InsertAsync(taskData);
return(new ResponseData("增加定时任务成功", await Db.PageAsync <TaskData>(page.PageNumber, page.PageSize, "where Task_Isexists = 1"), StatusCode.Success));
default:
return(new ResponseData("增加任务失败，请明确任务类型", "", StatusCode.Success));
}
}
private static void MigrateProductTypeJson()
{
Console.WriteLine("Migrate producttype json");
using (var db = new PetaPoco.Database(Environments.Current.Connection, PetaPoco.Database.MsSqlClientProvider))
{
var defaultLanguageID = db.SingleOrDefault <int?>(@"select LanguageID from Language where Name = @0", _defaultLanguageName);
if (defaultLanguageID == null)
{
Console.WriteLine(string.Format("Language {0} does not exist", _defaultLanguageName));
Console.Read();
return;
}
var vendorSetting = db.SingleOrDefault <string>("Select Value from VendorSetting where VendorID = @0 and SettingKey = @1", _vendorID, _settingKeyProductType);
if (vendorSetting == null)
{
Console.WriteLine(string.Format("Vendorsetting {0} for vendor {1} does not exist", _settingKeyProductType, _vendorID));
Console.Read();
return;
}
var productTypeDescriptionValue = vendorSetting;
var oldModel = productTypeDescriptionValue.FromJson <List <ProductTypeModelOld> >();
var newList = new List <ProductTypeModelNew>();
foreach (var value in oldModel)
{
var newObject = new ProductTypeModelNew();
newObject.Type = value.Type;
newObject.IsBra = value.IsBra;
newObject.ProductType = value.ProductType;
newObject.Translations = new Dictionary <int, string>();
newObject.Translations.Add(defaultLanguageID.Value, value.Translation);
newList.Add(newObject);
}
var newJsonValue = newList.ToJson();
db.Execute("Update VendorSetting set Value = @0 where SettingKey = @1 and VendorID = @2", newJsonValue, _settingKeyProductType, _vendorID);
Console.WriteLine("Done");
}
}
private void NotifyVote()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query <Entities.Vote>("Select * From Vote v Where v.UserNotified = 0"))
{
var b = db2.SingleOrDefault <Entities.StorytimePost>("Select * from Storytimepost Where StorytimePostId = @0", a.StorytimePostId);
var c = db2.SingleOrDefault <Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", b.StorytimeId);
var d = db2.SingleOrDefault <Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", c.StorytimeId);
if (c.StorytimeTypeId == 1)
{
var e = db2.SingleOrDefault <Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", d.UserGroupId);
foreach (var f in db3.Query <Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", e.UserGroupId))
{
if (f != null)
{
var g = db4.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);
if (g != null && g.DeviceToken != null)
{
string notificationtext = newvote + " " + g.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, g.DeviceToken);
}
}
}
}
else if (c.StorytimeTypeId == 2)
{
foreach (var h in db3.Query <Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", c.StorytimeId))
{
var i = db4.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", h.UserId);
if (i != null && i.DeviceToken != null)
{
string notificationtext = newvote + " " + i.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, i.DeviceToken);
}
}
}
a.UserNotified = true;
db2.Update(a);
}
}
//
// GET: /Classes/Details/5
public ActionResult Details(int id)
{
var db = new PetaPoco.Database("MissNancy");
var myClass = db.SingleOrDefault<Classes>("SELECT * FROM tblClasses WHERE ClassKey = @0", id);
return View(myClass);
}
/// <summary>
/// 根据登录名
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static User GetUser(string userName)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.SingleOrDefault<Models.User>("WHERE username=@0 ", userName);
}
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var b = db.SingleOrDefault <Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", id);
if (b != null)
{
System.Collections.Generic.List <Entities.AspNetUsers> grouplist = new List <Entities.AspNetUsers>();
foreach (var c in db.Query <Entities.ContactList>("Select * from UserGroupUser Where UserGroupId = @0", b.UserGroupId))
{
//todo see if there is a better way to do this with normalization and/or one connection.
var db2 = new PetaPoco.Database("AGSoftware");
var d = db2.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", c.UserId);
grouplist.Add(d);
}
return(Ok(grouplist));
}
else
{
return(NotFound());
}
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List<Entities.StorytimePost> storytimepostlist = new List<Entities.StorytimePost>();
string UserId = "";
foreach (Entities.StorytimePost c in db.Query<Entities.StorytimePost>("Select * From StorytimePost Where SeriesId = @0 Order By DateCreated Desc", id))
{
c.ImagePath = Providers.ImageHelper.GetImagePath(c.ImagePath);
c.ImagePath = c.ImagePath.Replace(@"\", @"/");
UserId = Providers.UserHelper.GetUserId(this.User.Identity.Name);
var voted = db2.SingleOrDefault<Entities.Vote>("Select * From Vote Where StorytimePostId = @0 And UserId = @1", new object []{c.StorytimePostId, UserId});
if (voted != null)
c.Voted = true;
else
c.Voted = false;
if (c.UserId == UserId)
c.UserPostedImage = true;
c.PhoneNumber = Providers.UserHelper.GetPhoneNumberById(c.UserId);
storytimepostlist.Add(c);
}
if (storytimepostlist.Count > 0)
return Ok(storytimepostlist);
else
return NotFound();
}
public IHttpActionResult Post([FromUri] string id, [FromBody]System.Collections.Generic.List<Entities.Contact> contactlist)
{
if (contactlist != null)
{
var db = new PetaPoco.Database("AGSoftware");
List<Entities.Contact> newcontactlist = new List<Entities.Contact>();
foreach (Entities.Contact contact in contactlist)
{
var iscontact = db.SingleOrDefault<Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", contact.PhoneNumber);
if (iscontact != null)
{
newcontactlist.Add(new Entities.Contact(iscontact.Id, iscontact.PhoneNumber, contact.FirstName, contact.LastName));
}
}
if (newcontactlist.Count > 0)
return Ok(newcontactlist);
else
return NotFound();
}
else
{
return BadRequest("Contact List was empty");
}
}
private void button3_Click(object sender, EventArgs e)
{
try
{
// Create a PetaPoco database object
var db = new PetaPoco.Database("sqlite");
// find the (presumably) most recently created foo
int id = db.ExecuteScalar<int>("SELECT max(id) from foo");
// Get a record
var foo = db.SingleOrDefault<foo>("SELECT * FROM foo WHERE Id=@0", id);
// Change it
foo.name = "PetaPoco changed your name!";
// Save it
db.Update("foo", "Id", foo);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
}
this.fooQuery1.Refresh();
}
public static Models.Category GetCategory(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.SingleOrDefault <Models.Category>("WHERE cid=@0", id));
}
}
private static void Crud()
{
var petaPoco = new PetaPoco.Database("Chinook");
var customer = new Customer { FirstName = "Ian", LastName = "Russell", Email = "*****@*****.**" };
petaPoco.Insert(customer);
var id = customer.CustomerId;
customer = petaPoco.Single<Customer>(id);
ObjectDumper.Write(customer);
customer.Country = "United Kingdom";
petaPoco.Update(customer);
customer = petaPoco.Single<Customer>(id);
ObjectDumper.Write(customer);
petaPoco.Delete<Customer>(id);
customer = petaPoco.SingleOrDefault<Customer>(id);
ObjectDumper.Write(customer);
}
/// <summary>
/// 获取配置
/// </summary>
/// <param name="title"></param>
/// <returns></returns>
public static Settings GetSetting(string title)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.SingleOrDefault <Models.Settings>("WHERE title=@0", title));
}
}
private static void Crud()
{
var petaPoco = new PetaPoco.Database("Chinook");
var customer = new Customer {
FirstName = "Ian", LastName = "Russell", Email = "*****@*****.**"
};
petaPoco.Insert(customer);
var id = customer.CustomerId;
customer = petaPoco.Single <Customer>(id);
ObjectDumper.Write(customer);
customer.Country = "United Kingdom";
petaPoco.Update(customer);
customer = petaPoco.Single <Customer>(id);
ObjectDumper.Write(customer);
petaPoco.Delete <Customer>(id);
customer = petaPoco.SingleOrDefault <Customer>(id);
ObjectDumper.Write(customer);
}
public static User GetUserByOpenID(string openid)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.SingleOrDefault<Models.User>("WHERE openid=@0 ", openid);
}
}
public static Models.UserGroup GetUserGroup(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.SingleOrDefault <Models.UserGroup>("WHERE gid=@0 ", id));
}
}
/// <summary>
/// 获取配置
/// </summary>
/// <param name="title"></param>
/// <returns></returns>
public static Settings GetSetting(string title)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.SingleOrDefault<Models.Settings>("WHERE title=@0", title);
}
}
public static Models.UserGroup GetUserGroup(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.SingleOrDefault<Models.UserGroup>("WHERE gid=@0 ", id);
}
}
public static User GetUserByOpenID(string openid)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.SingleOrDefault <Models.User>("WHERE openid=@0 ", openid));
}
}
/// <summary>
/// 根据登录名
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static User GetUser(string userName)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return(db.SingleOrDefault <Models.User>("WHERE username=@0 ", userName));
}
}
public IHttpActionResult Post([FromUri] string id, [FromBody] System.Collections.Generic.List <Entities.Contact> contactlist)
{
if (contactlist != null)
{
var db = new PetaPoco.Database("AGSoftware");
List <Entities.Contact> newcontactlist = new List <Entities.Contact>();
foreach (Entities.Contact contact in contactlist)
{
var iscontact = db.SingleOrDefault <Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", contact.PhoneNumber);
if (iscontact != null)
{
newcontactlist.Add(new Entities.Contact(iscontact.Id, iscontact.PhoneNumber, contact.FirstName, contact.LastName));
}
}
if (newcontactlist.Count > 0)
{
return(Ok(newcontactlist));
}
else
{
return(NotFound());
}
}
else
{
return(BadRequest("Contact List was empty"));
}
}
// GET: Admin/QuanLySanPham/Edit/5
public ActionResult Edit(string id)
{
var dataContext = new PetaPoco.Database("DienThoaiShopConnection");
var employee = dataContext.SingleOrDefault <sanpham>("Select * from sanpham where MaSanPham=@0",
id);
return(View(employee));
}
//LoggingOperations logOps = new LoggingOperations();
public Models.DigiSignature loadSignature(string residentId)
{
var db = new PetaPoco.Database("Olgarsconnection");
Models.DigiSignature ApplicationSignatureDetail = new Models.DigiSignature();
ApplicationSignatureDetail = db.SingleOrDefault <Models.DigiSignature>("select * from DigiSignature where ResidentId = @0", residentId);
return(ApplicationSignatureDetail);
}
public Movie GetMovieDetail(int id)
{
using (var db = new PetaPoco.Database(ConnectionString, providerName))
{
Movie movie = db.SingleOrDefault <Movie>(id);
return(movie);
}
}
private void NotifyStoryWinner()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query <Entities.Storytime>("select * from storytime s where (select count(*) from storytimeseries ss where ss.storytimeid = s.storytimeid) = 10 and s.UsersNotified = 0"))
{
var b = db2.SingleOrDefault <Entities.StoryWinner>("Select top 1 sum(sp.votes) as votes, u.username, s.storytimetitle from storytimeseries ss inner join storytimepost sp on ss.StorytimeSeriesId = sp.SeriesId inner join AspNetUsers u on u.id = sp.UserId inner join storytime s on s.storytimeid = ss.storytimeid where ss.storytimeid = @0 group by u.username, s.storytimetitle order by votes desc", a.StorytimeId);
if (a.StorytimeTypeId == 1)
{
var c = db2.SingleOrDefault <Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
var d = db2.SingleOrDefault <Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
foreach (var e in db3.Query <Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
if (e != null)
{
var f = db4.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.DeviceToken != null)
{
CreatePushNotification(storywinner + f.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", f.DeviceToken);
}
}
}
}
else if (a.StorytimeTypeId == 2)
{
foreach (var g in db3.Query <Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
{
var h = db4.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", g.UserId);
if (h != null && h.DeviceToken != null)
{
CreatePushNotification(storywinner + h.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", h.DeviceToken);
}
}
}
a.UsersNotified = true;
db4.Update(a);
}
}
private static Entities.AspNetUsers GetUser(string username)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where UserName = @0", username);
return(a);
}
private static Entities.AspNetUsers GetUser(string username)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where UserName = @0", username);
return a;
}
private static Entities.AspNetUsers GetUserById(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", id);
return(a);
}
public Models.CurrentUser getDetailsWithEmail(string UserEmail)
{
var db = new PetaPoco.Database("Olgarsconnection");
Models.CurrentUser currentUserDetail = new Models.CurrentUser();
currentUserDetail = db.SingleOrDefault <Models.CurrentUser>("select UserId,UserTypeId,FirstName,LastName,EmailAddress,UserPassword,UserTitle,ProfilePic,AuthenticationType from [User] where EmailAddress = @0 and SysStatus = 0 ", UserEmail);
return(currentUserDetail);
}
private static Entities.AspNetUsers GetUserById(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", id);
return a;
}
/// <summary>
/// Gets a snapshot for a give ID if one exists
/// </summary>
/// <param name="aggregateRootId">Aggregate root ID</param>
/// <returns>Snapshot details</returns>
public SnapshotDetail GetSnapshot(Guid aggregateRootId)
{
CreateSnapshotTable();
using (var db = new PetaPoco.Database(_snapshotConnectionStringName))
{
return(db.SingleOrDefault <SnapshotDetail>(TopOneSnapshot, aggregateRootId));
}
}
/// <summary>
/// Gets a snapshot for a give ID if one exists
/// </summary>
/// <param name="aggregateRootId">Aggregate root ID</param>
/// <returns>Snapshot details</returns>
public SnapshotDetail GetSnapshot(Guid aggregateRootId)
{
CreateSnapshotTable();
using (var db = new PetaPoco.Database(_snapshotConnectionStringName))
{
return db.SingleOrDefault<SnapshotDetail>(TopOneSnapshot, aggregateRootId);
}
}
public async Task <IHttpActionResult> Register(RegisterBindingModel model)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var user = new ApplicationUser()
{
UserName = model.Email, Email = model.Email, PhoneNumber = model.PhoneNumber, FirstName = model.FirstName, LastName = model.LastName, DateCreated = System.DateTime.Now, UDID = model.UDID, DeviceToken = model.DeviceToken
};
//todo generate random sms code to send to user. they must then verify before registration is complete.
Random generator = new Random();
int r = generator.Next(100000, 999999);
user.SMSCode = r.ToString();
// todo figure out how to incorporate this into the built in identity model
var db = new PetaPoco.Database("AGSoftware");
var iscontact = db.SingleOrDefault <Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", model.PhoneNumber);
if (iscontact != null)
{
ModelState.AddModelError("", "Phone number " + model.PhoneNumber + " is already taken.");
return(BadRequest(ModelState));
}
IdentityResult result = await UserManager.CreateAsync(user, model.Password);
if (!result.Succeeded)
{
return(GetErrorResult(result));
}
//todo Implement Twilio for SMS verification: No leading “0”s, no international trunk code, just a “+” followed by the country code, followed by the rest of the number.
//string AccountSid = "AC45c8ffca432aa4de083ae282f69b90e6";
//string AuthToken = "8f66389155f70a75e6a07b547e3e1606";
//var twilio = new TwilioRestClient(AccountSid, AuthToken);
//var message = twilio.SendMessage("+12033499233", "+19542785589", "Code:" + r.ToString() , "");
string token;
using (WebClient client = new WebClient())
{
client.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
string server = "http://" + ConfigurationManager.AppSettings["Server"] + "/Token";
token = client.UploadString(server, "POST", "grant_type=password&username="******"&password=" + model.Password);
}
Newtonsoft.Json.Linq.JObject tokenjson = Newtonsoft.Json.Linq.JObject.Parse(token);
return(Ok(tokenjson));
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where UserId = @0", id);
if (a != null)
return Ok(a);
else
return NotFound();
}
public static User GetUser(int uid)
{
User user = null;
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
user = db.SingleOrDefault<Models.User>("WHERE uid=@0 ", uid);
}
if (user == null)
user = new User();
return user;
}
public void DeleteMovie(int id)
{
using (var db = new PetaPoco.Database(ConnectionString, providerName))
{
var Movie = db.SingleOrDefault <Movie>(id);
if (Movie != null)
{
db.Delete(Movie);
}
}
}
/// <summary>
/// 获取对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tbName"></param>
/// <param name="strWhere"></param>
/// <returns></returns>
public T GetModel <T>(string tbName, string strWhere)
{
T _tRlt;
string _strSql = "select * from " + tbName;
if (!string.IsNullOrEmpty(strWhere))
{
_strSql += "" + strWhere;
}
_tRlt = db.SingleOrDefault <T>(_strSql);
return(_tRlt);
}
private void NotifyNewStory()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query <Entities.StorytimeUserList>("Select * From StorytimeUserList Where UserNotified = 0"))
{
var b = db2.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.UserId);
var storytime = db2.SingleOrDefault <Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", a.StorytimeId);
if (storytime.UserId != b.Id && b.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, b.DeviceToken);
}
a.UserNotified = true;
db2.Update(a);
}
foreach (var c in db.Query <Entities.StorytimeGroup>("Select * from StorytimeGroup Where UsersNotified = 0"))
{
var d = db2.SingleOrDefault <Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
var storytime = db2.SingleOrDefault <Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", c.StorytimeId);
foreach (var e in db3.Query <Entities.UserGroupUser>("Select * from UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
var f = db2.SingleOrDefault <Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.Id != storytime.UserId && f.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, f.DeviceToken);
}
}
c.UsersNotified = true;
db3.Update(c);
}
}
public Sensor Get(Guid id)
{
var sensorResult = db.SingleOrDefault <aasensor>("SELECT id, uri, type, lastval, time, locguid FROM aasensor WHERE id = @0", id.ToString("N"));
if (sensorResult != null)
{
return(aasensortoSensor(sensorResult));
}
else
{
return(null);
}
}
public static Models.Forums GetForum(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
var f = db.SingleOrDefault <Models.Forums>("WHERE fid=@0", id);
if (f != null)
{
f.Views++;
db.Update(f, new string[] { "views" });
}
return(f);
}
}
public Connector GetConnectorByID(int connectorID)
{
using (var db = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
var connector = db.SingleOrDefault <Connector>(string.Format(@"
SELECT *
FROM Connector
WHERE ConnectorID = {0}
", connectorID));
return(connector);
}
}
public int GetProductIDByVendorItemNumber(string vendorItemNumber)
{
using (var db = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
Int32 productID = db.SingleOrDefault <Int32>(string.Format(@"
SELECT ProductID
FROM Product
WHERE VendorItemNumber = '{0}'
", vendorItemNumber.Replace("@", "@@").Replace("'", "''")));
return(productID);
}
}
private void NotifyNewStory()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.StorytimeUserList>("Select * From StorytimeUserList Where UserNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.UserId);
var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", a.StorytimeId);
if (storytime.UserId != b.Id && b.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, b.DeviceToken);
}
a.UserNotified = true;
db2.Update(a);
}
foreach (var c in db.Query<Entities.StorytimeGroup>("Select * from StorytimeGroup Where UsersNotified = 0"))
{
var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", c.StorytimeId);
foreach (var e in db3.Query<Entities.UserGroupUser>("Select * from UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
var f = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.Id != storytime.UserId && f.DeviceToken != null)
{
CreatePushNotification(newstory + storytime.StorytimeTitle, f.DeviceToken);
}
}
c.UsersNotified = true;
db3.Update(c);
}
}
public Location Get(Guid id)
{
var result = db.SingleOrDefault <aalocguid>("SELECT id, loclocid FROM aalocguid WHERE id = @0", id.ToString("N"));
if (result != null)
{
return(aalocguidtoLocation(result));
}
else
{
return(null);
}
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.StorytimePost>("Select * from StorytimePost Where StorytimePostId = @0 Order By DateCreated Desc", id);
if (a != null)
{
a.ImagePath = Providers.ImageHelper.GetImagePath(a.ImagePath);
a.ImagePath = a.ImagePath.Replace(@"\", @"/");
return Ok(a);
}
else
{
return NotFound();
}
}
/// <summary>
/// 插入标签
/// </summary>
/// <param name="targTitle"></param>
/// <param name="fid"></param>
public static void AddTarg(string targTitle, int fid)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
Models.Targs tag = db.SingleOrDefault<Models.Targs>("WHERE tag_title=@0", targTitle);
if (tag == null)
{
tag = new Models.Targs();
tag.Forums = fid;
tag.Title = targTitle;
db.Save(tag);
}
Models.TargsRelation tagr = new Models.TargsRelation();
tagr.TagID = tag.TagID;
tagr.ForumsId = fid;
db.Insert(tagr);
}
}
public IHttpActionResult AddNewGroupUsers([FromBody]Entities.UserGroup usergroup)
{
var db = new PetaPoco.Database("AGSoftware");
var b = db.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", usergroup.UserGroupId);
Entities.UserGroupUser usergroupuser = null;
foreach (Entities.AspNetUsers user in usergroup.Users)
{
//todo see if there is a better way to do this with normalization and/or one connection.
var db2 = new PetaPoco.Database("AGSoftware");
usergroupuser = new Entities.UserGroupUser();
usergroupuser.UserGroupId = usergroup.UserGroupId;
usergroupuser.UserId = db2.SingleOrDefault<Entities.AspNetUsers>("Select Id from AspNetUsers where Id = @0", user.Id).Id; ;
db2.Insert(usergroupuser);
}
return Ok();
}
public IHttpActionResult Post([FromUri] string id, [FromBody]Entities.UserGroup usergroup)
{
var db = new PetaPoco.Database("AGSoftware");
usergroup.UserId = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);
usergroup.DateCreated = System.DateTime.Now;
db.Insert(usergroup);
Entities.UserGroupUser usergroupuser = null;
foreach (Entities.AspNetUsers user in usergroup.Users)
{
//todo see if there is a better way to do this with normalization and/or one connection.
var db2 = new PetaPoco.Database("AGSoftware");
usergroupuser = new Entities.UserGroupUser();
usergroupuser.UserGroupId = usergroup.UserGroupId;
usergroupuser.UserId = db2.SingleOrDefault<Entities.AspNetUsers>("Select Id from AspNetUsers where Id = @0", user.Id).Id; ;
db2.Insert(usergroupuser);
}
return Ok(usergroup.UserGroupId);
}
public IHttpActionResult Post([FromUri] string id, [FromBody]string emailaddress)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Email = @0", emailaddress);
if (a != null)
{
Email email = new Email(ConfigurationManager.AppSettings["EmailHost"]);
email.To.Add(a.Email);
email.From = "*****@*****.**";
email.Subject = "Username/Password";
email.Body = "<p><strong>Hello</strong></p><p>Username: "******"</p><p>Password: "******"</p>";
email.Send();
return Ok();
}
else
{
return NotFound();
}
}
public IHttpActionResult Post([FromUri] string id, [FromBody]Entities.Contact contact)
{
var db = new PetaPoco.Database("AGSoftware");
var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where PhoneNumber = @0", contact.PhoneNumber);
if (a != null)
{
Entities.ContactList contactlist = new Entities.ContactList();
contactlist.UserId = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);
contactlist.ContactId = a.Id;
contactlist.DateCreated = System.DateTime.Now;
db.Insert(contactlist);
return Ok();
}
else
{
return NotFound();
}
}
public IHttpActionResult Get(string id)
{
var db = new PetaPoco.Database("AGSoftware");
System.Collections.Generic.List<Entities.AspNetUsers> contactlist = new List<Entities.AspNetUsers>();
foreach (var a in db.Query<Entities.ContactList>("Select * from ContactList Where UserId = @0", Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name)))
{
//todo see if there is a better way to do this with normalization and/or one connection.
var db2 = new PetaPoco.Database("AGSoftware");
var b = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.ContactId);
contactlist.Add(b);
}
if (contactlist.Count > 0)
{
return Ok(contactlist);
}
else
{
return NotFound();
}
}
private void NotifySeriesWinner()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.SeriesWinner>("Select ss.StorytimeSeriesId, s.StorytimeTypeId, s.StorytimeId, s.StorytimeTitle, ss.SeriesText, u.Username, sp1.Votes From StorytimeSeries ss inner join Storytime s on s.StorytimeId = ss.StorytimeId inner join AspNetUsers u on ss.UserId = u.Id inner join StorytimePost sp1 on sp1.SeriesId = ss.StorytimeSeriesId And sp1.Votes = (Select MAX(sp2.Votes) From StorytimePost sp2 Where sp2.StorytimeId = sp1.StorytimeId) Where ss.UsersNotified = 0 And ss.DateCreated < DateAdd(hh, -24, GetDate())"))
{
if (a.StorytimeTypeId == 1)
{
var b = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
var c = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", b.UserGroupId);
foreach (var d in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", c.UserGroupId))
{
if (d != null)
{
var e = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", d.UserId);
if (e != null && e.DeviceToken != null)
{
string notificationtext = string.Empty;
if (a.Votes > 0)
notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
else
notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
CreatePushNotification(notificationtext, e.DeviceToken);
}
}
}
}
else if (a.StorytimeTypeId == 2)
{
foreach (var f in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
{
var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);
if (g != null && g.DeviceToken != null)
{
string notificationtext = string.Empty;
if (a.Votes > 0)
notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
else
notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
CreatePushNotification(notificationtext, g.DeviceToken);
}
}
}
var storytimeseries = db4.SingleOrDefault<Entities.StorytimeSeries>("Select * from StorytimeSeries Where StorytimeSeriesId = @0", a.StorytimeSeriesId);
storytimeseries.UsersNotified = true;
db4.Update(storytimeseries);
}
}
private void NotifyVote()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.Vote>("Select * From Vote v Where v.UserNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.StorytimePost>("Select * from Storytimepost Where StorytimePostId = @0", a.StorytimePostId);
var c = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", b.StorytimeId);
var d = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", c.StorytimeId);
if (c.StorytimeTypeId == 1)
{
var e = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", d.UserGroupId);
foreach(var f in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", e.UserGroupId))
{
if (f != null)
{
var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);
if (g != null && g.DeviceToken != null)
{
string notificationtext = newvote + " " + g.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, g.DeviceToken);
}
}
}
}
else if(c.StorytimeTypeId == 2)
{
foreach (var h in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", c.StorytimeId))
{
var i = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", h.UserId);
if (i != null && i.DeviceToken != null)
{
string notificationtext = newvote + " " + i.UserName + " has voted for " + b.PostText;
CreatePushNotification(newvote, i.DeviceToken);
}
}
}
a.UserNotified = true;
db2.Update(a);
}
}
private void NotifyStoryWinner()
{
var db = new PetaPoco.Database("AGSoftware");
var db2 = new PetaPoco.Database("AGSoftware");
var db3 = new PetaPoco.Database("AGSoftware");
var db4 = new PetaPoco.Database("AGSoftware");
foreach (var a in db.Query<Entities.Storytime>("select * from storytime s where (select count(*) from storytimeseries ss where ss.storytimeid = s.storytimeid) = 10 and s.UsersNotified = 0"))
{
var b = db2.SingleOrDefault<Entities.StoryWinner>("Select top 1 sum(sp.votes) as votes, u.username, s.storytimetitle from storytimeseries ss inner join storytimepost sp on ss.StorytimeSeriesId = sp.SeriesId inner join AspNetUsers u on u.id = sp.UserId inner join storytime s on s.storytimeid = ss.storytimeid where ss.storytimeid = @0 group by u.username, s.storytimetitle order by votes desc", a.StorytimeId);
if (a.StorytimeTypeId == 1)
{
var c = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
foreach (var e in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", d.UserGroupId))
{
if (e != null)
{
var f = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);
if (f != null && f.DeviceToken != null)
{
CreatePushNotification(storywinner + f.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", f.DeviceToken);
}
}
}
}
else if (a.StorytimeTypeId == 2)
{
foreach (var g in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
{
var h = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", g.UserId);
if (h != null && h.DeviceToken != null)
{
CreatePushNotification(storywinner + h.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", h.DeviceToken);
}
}
}
a.UsersNotified = true;
db4.Update(a);
}
}
public async Task<IHttpActionResult> Register(RegisterBindingModel model)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var user = new ApplicationUser() { UserName = model.Email, Email = model.Email, PhoneNumber = model.PhoneNumber, FirstName = model.FirstName, LastName = model.LastName, DateCreated = System.DateTime.Now, UDID = model.UDID, DeviceToken = model.DeviceToken };
//todo generate random sms code to send to user. they must then verify before registration is complete.
Random generator = new Random();
int r = generator.Next(100000, 999999);
user.SMSCode = r.ToString();
// todo figure out how to incorporate this into the built in identity model
var db = new PetaPoco.Database("AGSoftware");
var iscontact = db.SingleOrDefault<Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", model.PhoneNumber);
if (iscontact != null)
{
ModelState.AddModelError("", "Phone number " + model.PhoneNumber + " is already taken.");
return BadRequest(ModelState);
}
IdentityResult result = await UserManager.CreateAsync(user, model.Password);
if (!result.Succeeded)
{
return GetErrorResult(result);
}
//todo Implement Twilio for SMS verification: No leading “0”s, no international trunk code, just a “+” followed by the country code, followed by the rest of the number.
//string AccountSid = "AC45c8ffca432aa4de083ae282f69b90e6";
//string AuthToken = "8f66389155f70a75e6a07b547e3e1606";
//var twilio = new TwilioRestClient(AccountSid, AuthToken);
//var message = twilio.SendMessage("+12033499233", "+19542785589", "Code:" + r.ToString() , "");
string token;
using (WebClient client = new WebClient())
{
client.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
string server = "http://" + ConfigurationManager.AppSettings["Server"] + "/Token";
token = client.UploadString(server, "POST", "grant_type=password&username="******"&password=" + model.Password);
}
Newtonsoft.Json.Linq.JObject tokenjson = Newtonsoft.Json.Linq.JObject.Parse(token);
return Ok(tokenjson);
}
public static Models.Forums GetForum(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
var f = db.SingleOrDefault<Models.Forums>("WHERE fid=@0", id);
if (f != null)
{
f.Views++;
db.Update(f, new string[] { "views" });
}
return f;
}
}
public ActionResult Edit(int id, FormCollection collection)
{
var db = new PetaPoco.Database("MissNancy");
var myClass = db.SingleOrDefault<Classes>("SELECT * FROM tblClasses WHERE ClassKey = @0", id);
try
{
UpdateModel(myClass, collection.ToValueProvider());
db.Save("tblClasses", "ClassKey", myClass);
return RedirectToAction("Index");
}
catch (Exception e)
{
return View(myClass);
}
}
public static Models.Category GetCategory(int id)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
return db.SingleOrDefault<Models.Category>("WHERE cid=@0", id);
}
}
