public ActionResult Index()
{
using (var r = new MySqlRepository()) {
ViewData ["Messages"] = r.GetTop100();
}
return(View());
}
public void GetById_Success()
{
// Arrange
var expected = new TestEntity {
Id = 22, Name = "HelloWorld"
};
var mockDbSet = new Mock <DbSet <TestEntity> >();
mockDbSet.Setup(d => d.Find(22)).Returns(expected);
var mockContext = new Mock <IContext>();
mockContext.Setup(c => c.Set <TestEntity>()).Returns(mockDbSet.Object);
var repository = new MySqlRepository <TestEntity>(mockContext.Object);
// Act
var actual = repository.GetById(22);
// Assert
mockDbSet.Verify(d => d.Find(22), Times.Once);
mockContext.Verify(c => c.Set <TestEntity>(), Times.Once);
Assert.AreEqual(expected, actual);
}
public ActionResult Index ()
{
using (var r = new MySqlRepository ()) {
ViewData ["Messages"] = r.GetTop100 ();
}
return View ();
}
public string GetCustomerList(string userId)
{
// 1. Get Query
string query = @"
SELECT
ur.usersName as SellerName,
cr.SellerID,
cr.StartDate,
cr.EndDate
FROM
customer_reservation as cr
left join
users as ur
on cr.SellerID = ur.usersId
WHERE
cr.CustomerID = @CustomerID
";
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
// 3. Get By Dapper
var data = connection.Query <CustomerMypage>(query, new { CustomerID = userId }).ToList();
return(Newtonsoft.Json.JsonConvert.SerializeObject(data));
}
static void Main(string[] args)
{
int c = 0;
Console.WriteLine("初始化....");
EFRepository Repository = new EFRepository();
MySqlRepository mySqlRepository = new MySqlRepository();
Import_Excel import_Excel = new Import_Excel();
Console.WriteLine("開啟檔案中....");
import_Excel.OpenFile(@"D:\DcTenXen0621\Data\School\107-1-SoftWare_Engerneering\FinalProject\Core\App_Data\客戶資料.xlsx");
List <Customer> result = new List <Customer>();
result = import_Excel.Get_Data(import_Excel.Get_WorkSheet(0));
Console.Write("正在上傳檔案置資料庫");
result.ForEach(x =>
{
if (++c > 50)
{
Console.WriteLine("");
c = 0;
}
Console.Write(".");
mySqlRepository.Insert(x);
});
Console.WriteLine("檔案上傳完成.");
Console.ReadKey();
}
public void GetVaccines_ReadsFromTestTable_ShouldSucceed()
{
using (MySqlRepository repository = new MySqlRepository())
{
var recommendations = repository.GetVaccines("Margaret-Ann_De_Luca");
Assert.IsTrue(recommendations.Count >= 8);
}
}
public void GetNumberOfRoomsAvailable_GetsMaxAvailableRooms_returnsInt()
{
using (MySqlRepository repository = new MySqlRepository())
{
var resources = repository.GetNumberOfResources(1);
Assert.AreEqual(0, 1);
}
}
public void On_GetAll_ReturnsCorrectResult()
{
var config = MySqlConnectionStringParser.Load(MySqlTestConstants.ConnectionConfigPath);
var logger = new StringBuilderLogger();
var sut = new MySqlRepository(config, logger);
sut.GetAll("residence");
}
public void Delete_EntityIsNull()
{
// Arrange
var repository = new MySqlRepository <TestEntity>(new Context(null));
// Act
repository.Delete(null);
// Assert
}
public void MakeReservation(int placeNumber, string customerID, int x, int y, int z, DateTime startDate, DateTime endDate)
{
// before Get
string sellerID = GetSellerByPlacenumber(placeNumber);
//
var query = @"
INSERT INTO `customer_reservation`
(
`ReservationID`,
`RoomID`,
`SellerID`,
`CustomerID`,
`sizeX`,
`sizeY`,
`sizeZ`,
`StartDate`,
`EndDate`,
`IsAccectReservation`)
VALUES
(
NULL,
@RoomID,
@SellerID,
@CustomerID,
@sizeX,
@sizeY,
@sizeZ,
@StartDate,
@EndDate,
@IsAccectReservation);
";
var obj = new
{
RoomID = placeNumber,
SellerID = sellerID,
CustomerID = customerID,
sizeX = x,
sizeY = y,
sizeZ = z,
StartDate = startDate,
EndDate = endDate,
IsAccectReservation = 0
};
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
// 3. Get By Dapper
connection.Execute(query, obj);
}
private void InsertToDb(string userName, string tel)
{
string sql = "insert user(UserName,Tel,CreateTime) values(@UserName,@Tel,@CreateTime)";
Array arr = new[] {
new MySqlParameter("UserName", userName),
new MySqlParameter("Tel", tel),
new MySqlParameter("CreateTime", DateTime.Now.ToString())
};
int effectRows = MySqlRepository.Execute(sql, arr);
}
public string GetLocation(decimal locX, decimal locY)
{
// 1. Get Select Query
string query = @"
select
sp.SellerID,
sp.LocationType,
sp.LocationX,
sp.LocationY,
sp.Description,
sp.SellerAddress,
sp.IsUseLocation,
sp.RoomSizeX,
sp.RoomSizeY,
sp.PlaceNumber,
sp.TimeStamp,
sp.RoomID,
us.usersName as UsersName,
ABS(sp.locationX - {locX}) as distunceX,
ABS(sp.locationY - {locY}) as distunceY
from
seller_places as sp
left join
users as us
on sp.SellerID = us.usersId
where
sp.IsUseLocation = 1
order by
distunceX,
distunceY
";
query = query.Replace("{locX}", locX.ToString());
query = query.Replace("{locY}", locY.ToString());
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
LocationInfo model = new LocationInfo
{
LocationX = locX,
LocationY = locY
};
// 3. Get By Dapper
var data = connection.Query <LocationInfo>(query, model).ToList();
return(Newtonsoft.Json.JsonConvert.SerializeObject(data));
}
private void InitTable()
{
string sql = @"
CREATE TABLE IF NOT EXISTS `User` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`UserName` nvarchar(64) NULL,
`Tel` varchar(24) NULL,
`CreateTime` datetime NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
";
int effectRows = MySqlRepository.Execute(sql);
}
public ActionResult Details(Guid id)
{
using (var r = new MySqlRepository()) {
var m = r.Get(id);
var content = "";
using (var w = new StringWriter()) {
using (var xw = new XmlTextWriter(w)) {
m.WriteXml(xw);
}
content = w.GetStringBuilder().ToString();
}
return(Content(content, "text/xml"));
}
}
public ActionResult Details (Guid id)
{
using (var r = new MySqlRepository ()) {
var m = r.Get (id);
var content = "";
using (var w = new StringWriter ()) {
using (var xw = new XmlTextWriter (w)) {
m.WriteXml (xw);
}
content = w.GetStringBuilder ().ToString ();
}
return Content (content, "text/xml");
}
}
public UserModel FindAccountInfo(string id, string password)
{
// 1. Get Select Query
var query = "SELECT * FROM users WHERE UsersId = @UsersId And UsersId = @UsersPw";
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
var serachModel = new SighInModel {
UsersId = id, UsersPw = password
};
// 3. Get By Dapper
var data = connection.Query <UserModel>(query, serachModel).FirstOrDefault();
return(data);
}
public ActionResult Index()
{
using (var r = new MySqlRepository()) {
var top = r.GetTop100();
SimulateLatency();
var content = "";
using (var w = new StringWriter()) {
using (var xw = new XmlTextWriter(w)) {
Message.WriteAllXml(top, xw);
}
content = w.GetStringBuilder().ToString();
}
return(Content(content, "text/xml"));
}
}
public ActionResult Index ()
{
using (var r = new MySqlRepository ()) {
var top = r.GetTop100 ();
SimulateLatency ();
var content = "";
using (var w = new StringWriter ()) {
using (var xw = new XmlTextWriter (w)) {
Message.WriteAllXml (top, xw);
}
content = w.GetStringBuilder ().ToString ();
}
return Content (content, "text/xml");
}
}
public void GetById_ReturnNull()
{
// Arrange
var mockDbSet = new Mock <DbSet <TestEntity> >();
var mockContext = new Mock <IContext>();
mockContext.Setup(c => c.Set <TestEntity>()).Returns(mockDbSet.Object);
var repository = new MySqlRepository <TestEntity>(mockContext.Object);
// Act
var actual = repository.GetById(-1);
// Assert
mockContext.Verify(c => c.Set <TestEntity>(), Times.Once);
Assert.IsNull(actual);
}
public IHttpActionResult Resources(string names)
{
string[] patientNames = names.Split(',');
try
{
using (MySqlRepository repository = new MySqlRepository())
{
var results = repository.GetNumberOfResources(patientNames.Length);
return(Ok(results));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public string GetSellerList(string sellerID)
{
string query = @"
SELECT
cr.CustomerID,
ur.usersName,
cr.StartDate,
cr.EndDate,
(
CASE
WHEN cr.sizeX + cr.sizeY + cr.sizeZ <= 178 THEN '소형'
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 178 && cr.sizeX + cr.sizeY + cr.sizeZ <= 330 THEN '중형'
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 331 && cr.sizeX + cr.sizeY + cr.sizeZ <= 419 THEN '대형'
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 419 THEN '특대형'
ELSE 'Fail'
END) AS GoodsSize,
(
CASE
WHEN cr.sizeX + cr.sizeY + cr.sizeZ <= 178 THEN 10000
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 178 && cr.sizeX + cr.sizeY + cr.sizeZ <= 330 THEN 15000
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 331 && cr.sizeX + cr.sizeY + cr.sizeZ <= 419 THEN 80000
WHEN cr.sizeX + cr.sizeY + cr.sizeZ > 419 THEN 150000
ELSE 0
END) AS Money
FROM
customer_reservation as cr
left join
users as ur
on
cr.CustomerID = ur.usersId
WHERE
cr.SellerID = @SellerID
";
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
// 3. Get By Dapper
var data = connection.Query <SellerLIst>(query, new { SellerID = sellerID }).ToList();
return(Newtonsoft.Json.JsonConvert.SerializeObject(data));
}
public ActionResult New (string @from, string text)
{
var m = new Message {
Id = Guid.NewGuid (),
From = @from,
Text = text,
Time = DateTime.UtcNow,
};
if (string.IsNullOrEmpty (m.From)) {
m.From = "Anonymous";
}
if (!string.IsNullOrEmpty (m.Text)) {
using (var r = new MySqlRepository ()) {
r.Add (m);
}
}
return RedirectToAction ("Index", "Home");
}
public IHttpActionResult Recommend(RecommendRequest request)
{
try
{
if (request == null)
{
return(BadRequest("The request body is null"));
}
if (string.IsNullOrEmpty(request.ApointmentTime))
{
return(BadRequest("No apointment set"));
}
if (string.IsNullOrEmpty(request.CallerName))
{
return(BadRequest("No caller name set"));
}
if (string.IsNullOrEmpty(request.PatientName))
{
return(BadRequest("No patient name set"));
}
DateTime appointmentTime;
if (DateTime.TryParse(request.ApointmentTime, out appointmentTime))
{
using (MySqlRepository repository = new MySqlRepository())
{
var results = repository.GetVaccines(request.CallerName);
return(Ok(results));
}
}
else
{
return(BadRequest("Could not parse date time"));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public string GetSellerByPlacenumber(int placeNumber)
{
var query = @"
select
SellerID
from
seller_places
where
RoomID = @RoomID
";
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
// 3. Get By Dapper
var data = connection.Query <string>(query, new { RoomID = placeNumber }).FirstOrDefault();
return(data);
}
public ActionResult New(string @from, string text)
{
var m = new Message {
Id = Guid.NewGuid(),
From = @from,
Text = text,
Time = DateTime.UtcNow,
};
if (string.IsNullOrEmpty(m.From))
{
m.From = "Anonymous";
}
if (!string.IsNullOrEmpty(m.Text))
{
using (var r = new MySqlRepository()) {
r.Add(m);
}
}
return(RedirectToAction("Index", "Home"));
}
public void UpdateRoomDisable(int placeNumber)
{
// 1. Get Insert Query
var query = @"
UPDATE
seller_places
SET
IsUseLocation = 0
WHERE
RoomID = {PlaceNumber} ;
";
query = query.Replace("{PlaceNumber}", placeNumber.ToString());
// 2. Get Mysql Object
var connection = MySqlRepository.GetConnetion();
// 3. Set By Dapper
connection.Execute(query);
}
public void Delete_Success()
{
// Arrange
var mockDbSet = new Mock <DbSet <TestEntity> >();
mockDbSet.Setup(d => d.Remove(It.IsAny <TestEntity>()));
var mockContext = new Mock <IContext>();
mockContext.Setup(c => c.Set <TestEntity>()).Returns(mockDbSet.Object);
mockContext.Setup(c => c.SaveChanges());
var repository = new MySqlRepository <TestEntity>(mockContext.Object);
// Act
repository.Delete(new TestEntity());
// Assert
mockDbSet.Verify(d => d.Remove(It.IsAny <TestEntity>()), Times.Once);
mockContext.Verify(c => c.Set <TestEntity>(), Times.Once);
mockContext.Verify(c => c.SaveChanges(), Times.Once);
}
static string GetNewCode(string tableName)
{
MySqlRepository res = new MySqlRepository();
using (var conn = res.GetConn())
{
string sql = $"select id from dbcode where tablename = '{tableName}'";
int numVal = conn.ExecuteScalar <int>(sql);
if (numVal == 0)
{
int num = 1;
string insertSql = $"insert into dbcode(tablename,id) values('{tableName}','{num}')";
int result = conn.Execute(insertSql);
if (result > 0)
{
return(num.ToString());
}
else
{
throw new Exception("新增code失败");
}
}
else
{
numVal = numVal + 1;
string updateSql = $"update dbcode set id = {numVal} where tablename = '{tableName}'";
int result = conn.Execute(updateSql);
if (result > 0)
{
return(numVal.ToString());
}
else
{
throw new Exception("更新code失败");
}
}
}
}
public BetTests()
{
repo = new MySqlRepository();
}
public RoomContainerTests()
{
repo = new MySqlRepository();
}
public List <RidePage> Get(Vehicle vehicle, int page = 0)
{
var repository = new MySqlRepository();
return(repository.GetVehicleRides(page, vehicle));
}
public UserTests()
{
repo = new MySqlRepository();
}
public void GetVaccines_ReadsFromTestTable_ShouldSucceed()
{
MySqlRepository repository = new MySqlRepository();
repository.GetVaccines("Pat");
}
