public IActionResult Count([FromQuery] StaffCondition c)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var count =
c == null?db.Staff.Count() :
db.Staff.Count(predicate: c.CreatePredicate());
return(Ok(count));
}
}
public int Count([FromUri] AddressCondition c)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var count =
c == null?db.Address.Count() :
db.Address.Count(predicate: c.CreatePredicate());
return(count);
}
}
public IActionResult Remove(string teacherNo)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var count = db.Teacher
.Where(_ => _.teacher_no == teacherNo)
.Set(_ => _.modified_by, CurrentAccountId)
.Set(_ => _.removed_at, Sql.CurrentTimestampUtc)
.Update();
return(Ok(count));
}
}
public IActionResult Remove([FromQuery] AccountCondition c)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var count = db.Account
.Where(c.CreatePredicate())
.Set(_ => _.modified_by, CurrentAccountId)
.Set(_ => _.removed_at, Sql.CurrentTimestampUtc)
.Update();
return(Ok(count));
}
}
public IActionResult Merge([FromBody] IEnumerable <Test> os)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
var count = db.Merge <Test>(os);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Modify(int uid, [FromBody] Test o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
var count = db.Update <Test>(o);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Remove(int sexTypeId)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var count = db.SexType
.Where(_ => _.sex_type_id == sexTypeId)
.Set(_ => _.modified_by, CurrentAccountId)
.Set(_ => _.removed_at, Sql.CurrentTimestampUtc)
.Update();
return(Ok(count));
}
}
public IActionResult Create([FromBody] Test o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
o.uid = db.InsertWithInt32Identity <Test>(o);
return(CreatedAtAction(nameof(Get), new { uid = o.uid }, o));
}
}
return(BadRequest());
}
public IActionResult Search([FromQuery] TestCondition c, [FromQuery] string[] order, int currentPage = 1, int pageSize = 10, DateTime?p_when = null)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Test
;
var filtered = c == null ? q : q.Where(c.CreatePredicate());
var ordered = order.Any() ? filtered.SortBy(order) : filtered;
var result = ordered.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
return(Ok(result));
}
}
public IActionResult Modify(int accountId, string roleId, [FromBody] AccountRole o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
o.modified_by = CurrentAccountId;
var count = db.Update <AccountRole>(o);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Modify(int userType, string genericUserNo, int seq, [FromBody] PersonName o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
o.modified_by = CurrentAccountId;
var count = db.Update <PersonName>(o);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Get(int userType, string genericUserNo, int seq, [FromQuery] bool with_PersonNameType, [FromQuery] bool with_Staff, [FromQuery] bool with_Teacher)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.PersonName
.LoadWith(with_PersonNameType, _ => _.PersonNameType)
.LoadWith(with_Staff, _ => _.Staff)
.LoadWith(with_Teacher, _ => _.Teacher)
;
var o = q.Find(userType, genericUserNo, seq);
return(o == null ? (IActionResult)NotFound() : Ok(o));
}
}
static void Main(string[] args)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB("peppaConnectionString"))
{
db.Insert <AddressType>(new AddressType
{
address_type_id = 0,
name = "現住所",
display_order = 0,
});
var q =
from a in db.AddressType
select new
{
a.address_type_id,
a.name,
a.created_at,
a.created_by,
a.modified_at,
a.modified_by,
a.row_version,
};
foreach (var a in q)
{
Console.WriteLine(a);
Debug.WriteLine(a);
}
var cond = new AddressTypeCondition
{
name_eq = "現住所"
};
var pred = cond.CreatePredicate();
var list = db.AddressType.Where(pred).ToList();
foreach (var x in list)
{
Console.WriteLine(x);
Debug.WriteLine(x);
}
}
}
public IActionResult Create([FromBody] PersonName o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
o.created_by = CurrentAccountId;
o.modified_by = CurrentAccountId;
o.uid = db.InsertWithInt32Identity <PersonName>(o);
return(CreatedAtAction(nameof(Get), new { userType = o.user_type, genericUserNo = o.generic_user_no, seq = o.seq }, o));
}
}
return(BadRequest());
}
public IActionResult Create([FromBody] AccountRole o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
o.created_by = CurrentAccountId;
o.modified_by = CurrentAccountId;
o.uid = db.InsertWithInt32Identity <AccountRole>(o);
return(CreatedAtAction(nameof(Get), new { accountId = o.account_id, roleId = o.role_id }, o));
}
}
return(BadRequest());
}
public IActionResult GetFull(string staffNo)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var o = db.Staff
.LoadWith(_ => _.SexType)
.LoadWith(_ => _.NameList.First().PersonNameType)
.LoadWith(_ => _.AddressList.First().AddressType)
.LoadWith(_ => _.ContactList.First().ContactType)
.Find(staffNo);
return(o == null ? (IActionResult)NotFound() : Ok(o));
}
}
public IActionResult Get(int accountId, [FromQuery] bool with_Staff, [FromQuery] bool with_Teacher, [FromQuery] bool with_User, [FromQuery] bool with_AccountRoleList, [FromQuery] bool with_PasswordList)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Account
.LoadWith(with_Staff, _ => _.Staff)
.LoadWith(with_Teacher, _ => _.Teacher)
.LoadWith(with_User, _ => _.User)
.LoadWith(with_AccountRoleList, _ => _.AccountRoleList)
.LoadWith(with_PasswordList, _ => _.PasswordList)
;
var o = q.Find(accountId);
return(o == null ? (IActionResult)NotFound() : Ok(o));
}
}
public IActionResult Get(string userNo, [FromQuery] bool with_SexType, [FromQuery] bool with_AccountList, [FromQuery] bool with_NameList, [FromQuery] bool with_AddressList, [FromQuery] bool with_ContactList)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.User
.LoadWith(with_SexType, _ => _.SexType)
.LoadWith(with_AccountList, _ => _.AccountList)
.LoadWith(with_NameList, _ => _.NameList)
.LoadWith(with_AddressList, _ => _.AddressList)
.LoadWith(with_ContactList, _ => _.ContactList)
;
var o = q.Find(userNo);
return(o == null ? (IActionResult)NotFound() : Ok(o));
}
}
public IActionResult SearchFull([FromQuery] StaffCondition c, [FromQuery] string[] order, int currentPage = 1, int pageSize = 10)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Staff
.LoadWith(_ => _.SexType)
.LoadWith(_ => _.NameList.First().PersonNameType)
.LoadWith(_ => _.AddressList.First().AddressType)
.LoadWith(_ => _.ContactList.First().ContactType);
var filtered = c == null ? q : q.Where(c.CreatePredicate());
var ordered = order.Any() ? filtered.SortBy(order) : filtered;
return(Ok(ordered.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList()));
}
}
public IActionResult Search([FromQuery] PersonNameCondition c, [FromQuery] bool with_PersonNameType, [FromQuery] bool with_Staff, [FromQuery] bool with_Teacher, [FromQuery] string[] order, int currentPage = 1, int pageSize = 10, DateTime?p_when = null)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.PersonName
.LoadWith(with_PersonNameType, _ => _.PersonNameType)
.LoadWith(with_Staff, _ => _.Staff)
.LoadWith(with_Teacher, _ => _.Teacher)
.IsActiveAt(p_when)
;
var filtered = c == null ? q : q.Where(c.CreatePredicate());
var ordered = order.Any() ? filtered.SortBy(order) : filtered;
var result = ordered.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
return(Ok(result));
}
}
public Role Get([FromUri] bool with_RolePermissionList, string roleId)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Role;
#region LoadWith
if (with_RolePermissionList)
{
q = q.LoadWith(_ => _.RolePermissionList);
}
#endregion
var o = q.Find(roleId);
return(o);
}
}
public IEnumerable <Role> Search([FromUri] bool with_RolePermissionList, [FromUri] RoleCondition c)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Role;
#region LoadWith
if (with_RolePermissionList)
{
q = q.LoadWith(_ => _.RolePermissionList);
}
#endregion
var list = (c == null ? q : q.Where(c.CreatePredicate())).ToList();
return(list);
}
}
public IActionResult Upsert([FromBody] Account o)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
if (o.uid == 0)
{
o.created_by = CurrentAccountId;
}
o.modified_by = CurrentAccountId;
int count = db.InsertOrReplace <Account>(o);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Search([FromQuery] TeacherCondition c, [FromQuery] bool with_WorkStyle, [FromQuery] bool with_Position, [FromQuery] bool with_TeacherLisence, [FromQuery] bool with_SexType, [FromQuery] bool with_AccountList, [FromQuery] bool with_NameList, [FromQuery] bool with_AddressList, [FromQuery] bool with_ContactList, [FromQuery] string[] order, int currentPage = 1, int pageSize = 10, DateTime?p_when = null)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
using (var db = new peppaDB())
{
var q = db.Teacher
.LoadWith(with_WorkStyle, _ => _.WorkStyle)
.LoadWith(with_Position, _ => _.Position)
.LoadWith(with_TeacherLisence, _ => _.TeacherLisence)
.LoadWith(with_SexType, _ => _.SexType)
.LoadWith(with_AccountList, _ => _.AccountList)
.LoadWith(with_NameList, _ => _.NameList)
.LoadWith(with_AddressList, _ => _.AddressList)
.LoadWith(with_ContactList, _ => _.ContactList)
.IsActiveAt(p_when)
;
var filtered = c == null ? q : q.Where(c.CreatePredicate());
var ordered = order.Any() ? filtered.SortBy(order) : filtered;
var result = ordered.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
#region アソシエーションでLoadWithしたものもフィルタする
if (p_when != null)
{
result.ForEach(_ =>
{
_.AccountList = _.AccountList?.Where(_ => _.IsActiveAt(p_when));
_.NameList = _.NameList?.Where(_ => _.IsActiveAt(p_when));
_.AddressList = _.AddressList?.Where(_ => _.IsActiveAt(p_when));
_.ContactList = _.ContactList?.Where(_ => _.IsActiveAt(p_when));
});
}
#endregion
return(Ok(result));
}
}
public IActionResult MassiveCreate([FromBody] IEnumerable <Account> os)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
foreach (var o in os)
{
o.created_by = CurrentAccountId;
o.modified_by = CurrentAccountId;
}
var ret = db.BulkCopy <Account>(os);
return(Ok(ret));
}
}
return(BadRequest());
}
public IActionResult Change([FromBody] ChangePasswordInputModel inputModel)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
var q = new PasswordCondition
{
account_id_eq = CurrentAccountId,
};
var pw = db.Password.SingleOrDefault(q.CreatePredicate());
if (pw != null)
{
var new_password = pw.Encrypt(inputModel.Method, inputModel.NewPassword);
var new_life = pw.NewLifeExpectancy;
var ret = db.Password
.Where(q.CreatePredicate())
.Update(_ => new Password
{
HashType = inputModel.Method,
password_hash = new_password,
expiration_on = new_life,
modified_by = CurrentAccountId,
});
return(Ok(ret));
}
}
}
return(BadRequest());
}
public IActionResult Merge([FromBody] IEnumerable <SexType> os)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
foreach (var o in os)
{
if (o.uid == 0)
{
o.created_by = CurrentAccountId;
}
o.modified_by = CurrentAccountId;
}
var count = db.Merge <SexType>(os);
return(Ok(count));
}
}
return(BadRequest());
}
public IActionResult Token([FromBody] TokenInputModel inputModel)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (msg, context) => Debug.WriteLine(msg, context);
#endif
if (ModelState.IsValid)
{
using (var db = new peppaDB())
{
var now = DateTime.UtcNow;
var accs = db.Account
.LoadWith(_ => _.PasswordList)
.GetAccounts(inputModel.UserType, inputModel.ID)
.ToList();
var validAccPw = accs.SelectMany(a => a.PasswordList.Select(p => (Acc: a, Pw: p))).FirstOrDefault(_ => _.Pw.IsActive(now));
if (validAccPw.Pw != null)
{
if (validAccPw.Pw.Authenticate(inputModel.Password, now))
{
// 連続失敗回数は初期化
if (validAccPw.Pw.fail_times != 0)
{
var ret = db.Password
.Where(_ => _.uid == validAccPw.Pw.uid)
.Update(_ => new Password
{
fail_times = 0,
});
}
var token = CreateJwtSecurityToken(new Auth
{
ID = validAccPw.Acc.AccountID,
Name = validAccPw.Acc.AccountID,
});
return(Ok(new TokenViewModel
{
Token = new JwtSecurityTokenHandler().WriteToken(token),
Expiration = token.ValidTo,
}));
}
else
{
// 連続失敗回数をインクリしつつ回数上限に達していたらロックフラグも立てる
var ret = db.Password
.Where(_ => _.uid == validAccPw.Pw.uid)
.Update(_ => new Password
{
fail_times = Math.Min(_.can_fail_times, _.fail_times + 1),
lock_flg = _.can_fail_times <= _.fail_times + 1 ? 1 : 0,
});
return(Unauthorized());
}
}
return(Unauthorized());
}
}
return(BadRequest());
}
