public LoginInfo GetLoginInfo(Guid token)
{
return(CacheHelper.Get <LoginInfo>(string.Format(_LoginInfoKeyFormat, token), () =>
{
using (var dbContext = new DaoBase())
{
//如果有超时的，启动超时处理
var timeoutList = dbContext.FindAll <LoginInfo>(p => DbFunctions.DiffMinutes(DateTime.Now, p.LastAccessTime) > _UserLoginTimeoutMinutes);
if (timeoutList.Count > 0)
{
foreach (var li in timeoutList)
{
dbContext.LoginInfoes.Remove(li);
}
}
dbContext.SaveChanges();
var loginInfo = dbContext.FindAll <LoginInfo>(l => l.LoginToken == token).FirstOrDefault();
if (loginInfo != null)
{
loginInfo.LastAccessTime = DateTime.Now;
dbContext.Update <LoginInfo>(loginInfo);
}
return loginInfo;
}
}));
}
/// <summary>
/// 備考リストを更新する
/// </summary>
/// <param name="remark">選択対象の備考</param>
/// <returns></returns>
private async Task UpdateRemarkListAsync(string remark = null)
{
if (this.WVM?.SelectedItemVM?.Id == null)
{
return;
}
ObservableCollection <string> remarkVMList = new ObservableCollection <string>()
{
string.Empty
};
string selectedRemark = remark ?? this.WVM.SelectedRemark ?? remarkVMList[0];
using (DaoBase dao = this.builder.Build()) {
DaoReader reader = await dao.ExecQueryAsync(@"
SELECT remark FROM hst_remark
WHERE del_flg = 0 AND item_id = @{0}
ORDER BY used_time DESC;", this.WVM.SelectedItemVM.Id);
reader.ExecWholeRow((count, record) => {
string tmp = record["remark"];
remarkVMList.Add(tmp);
return(true);
});
}
this.WVM.RemarkList = remarkVMList;
this.WVM.SelectedRemark = selectedRemark;
}
public static List <spSensusRIPerBulanPerKelas> GetspSensusRIPerBulanPerKelasList(String[] parameterField, String[] param)
{
List <spSensusRIPerBulanPerKelas> result = new List <spSensusRIPerBulanPerKelas>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(spSensusRIPerBulanPerKelas));
ctx.CommandText = "sprirpt_SensusRI_perBulan_perKelas";
ctx.CommandType = System.Data.CommandType.StoredProcedure;
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((spSensusRIPerBulanPerKelas)helper.IDataReaderToObject(reader, new spSensusRIPerBulanPerKelas()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
/// <summary>
/// 帳簿リストを更新する
/// </summary>
/// <param name="bookId">選択対象の帳簿ID</param>
/// <returns></returns>
private async Task UpdateBookListAsync(int?bookId = null)
{
// 帳簿を取得する
ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>();
BookViewModel selectedBookVM = null;
using (DaoBase dao = this.builder.Build()) {
DaoReader reader = await dao.ExecQueryAsync(@"
SELECT book_id, book_name FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;");
reader.ExecWholeRow((count, record) => {
BookViewModel vm = new BookViewModel()
{
Id = record.ToInt("book_id"), Name = record["book_name"]
};
bookVMList.Add(vm);
if (selectedBookVM == null || bookId == vm.Id)
{
selectedBookVM = vm;
}
return(true);
});
}
this.WVM.BookVMList = bookVMList;
this.WVM.SelectedBookVM = selectedBookVM;
}
/// <summary>
/// 一括チェック処理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void BulkCheckCommand_Executed(object sender, ExecutedRoutedEventArgs e)
{
Cursor cursor = this.Cursor;
this.Cursor = Cursors.Wait;
using (DaoBase dao = this.builder.Build()) {
await dao.ExecTransactionAsync(async() => {
foreach (var vm in this.WVM.CsvComparisonVMList)
{
if (vm.ActionId.HasValue)
{
await dao.ExecNonQueryAsync(@"
UPDATE hst_action
SET is_match = 1, update_time = 'now', updater = @{1}
WHERE action_id = @{0} AND is_match <> 1;", vm.ActionId, Updater);
}
}
});
}
await this.UpdateComparisonInfoAsync();
this.ActionsStatusChanged?.Invoke(this, new EventArgs());
this.Cursor = cursor;
}
public User GetUser(int id)
{
using (var dbContext = new DaoBase())
{
return(dbContext.Users.Include("Roles").Where(u => u.Id == id).SingleOrDefault());
}
}
/// <summary>
/// 店舗リストを更新する
/// </summary>
/// <param name="shopName">選択対象の店舗名</param>
/// <returns></returns>
private async Task UpdateShopListAsync(string shopName = null)
{
if (this.WVM.SelectedItemVM == null)
{
return;
}
ObservableCollection <string> shopNameVMList = new ObservableCollection <string>()
{
string.Empty
};
string selectedShopName = shopName ?? this.WVM.SelectedShopName ?? shopNameVMList[0];
using (DaoBase dao = this.builder.Build()) {
DaoReader reader = await dao.ExecQueryAsync(@"
SELECT shop_name FROM hst_shop
WHERE del_flg = 0 AND item_id = @{0}
ORDER BY used_time DESC;", this.WVM.SelectedItemVM.Id);
reader.ExecWholeRow((count, record) => {
string tmp = record["shop_name"];
shopNameVMList.Add(tmp);
return(true);
});
}
this.WVM.ShopNameList = shopNameVMList;
this.WVM.SelectedShopName = selectedShopName;
}
/// <summary>
/// カテゴリリストを更新する
/// </summary>
/// <param name="categoryId">選択対象のカテゴリID</param>
/// <returns></returns>
private async Task UpdateCategoryListAsync(int?categoryId = null)
{
ObservableCollection <CategoryViewModel> categoryVMList = new ObservableCollection <CategoryViewModel>()
{
new CategoryViewModel()
{
Id = -1, Name = "(指定なし)"
}
};
int?tmpCategoryId = categoryId ?? this.WVM.SelectedCategoryVM?.Id ?? categoryVMList[0].Id;
CategoryViewModel selectedCategoryVM = categoryVMList[0];
using (DaoBase dao = this.builder.Build()) {
DaoReader reader = await dao.ExecQueryAsync(@"
SELECT category_id, category_name FROM mst_category C
WHERE del_flg = 0 AND EXISTS (SELECT * FROM mst_item I WHERE I.category_id = C.category_id AND balance_kind = @{0} AND del_flg = 0
AND EXISTS (SELECT * FROM rel_book_item RBI WHERE book_id = @{1} AND RBI.item_id = I.item_id))
ORDER BY sort_order;", (int)this.WVM.SelectedBalanceKind, this.WVM.SelectedBookVM.Id);
reader.ExecWholeRow((count, record) => {
CategoryViewModel vm = new CategoryViewModel()
{
Id = record.ToInt("category_id"), Name = record["category_name"]
};
categoryVMList.Add(vm);
if (vm.Id == categoryId)
{
selectedCategoryVM = vm;
}
return(true);
});
}
this.WVM.CategoryVMList = categoryVMList;
this.WVM.SelectedCategoryVM = selectedCategoryVM;
}
public LoginInfo Login(string loginName, string password)
{
LoginInfo loginInfo = null;
password = Encrypt.MD5(password);
loginName = loginName.Trim();
using (var dbContext = new DaoBase())
{
var user = dbContext.Users.Include("Roles").Where(u => u.LoginName == loginName && u.Password == password && u.IsActive).FirstOrDefault();
if (user != null)
{
var ip = Fetch.UserIp;
loginInfo = dbContext.FindAll <LoginInfo>(p => p.LoginName == loginName && p.ClientIP == ip).FirstOrDefault();
if (loginInfo != null)
{
loginInfo.LastAccessTime = DateTime.Now;
}
else
{
loginInfo = new LoginInfo(user.Id, user.LoginName);
loginInfo.ClientIP = ip;
loginInfo.BusinessPermissionList = user.BusinessPermissionList;
dbContext.Insert <LoginInfo>(loginInfo);
}
}
}
return(loginInfo);
}
public static DataTable GetDataReport(string procedureName, string[] parameterField, string[] param)
{
DataTable result;
IDbContext ctx = DbFactory.Configure();
try
{
ctx.CommandText = procedureName;
ctx.CommandType = CommandType.StoredProcedure;
ctx.Clear();
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
//Get DataReader
result = DaoBase.GetDataTable(ctx);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <String> GetSysColumnsPKList(string tableName)
{
List <String> result = new List <String>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(SysColumns));
ctx.CommandText = string.Format("SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '{0}'", tableName);
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add(reader[0].ToString());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <spPharmacyStockCard> GetspPharmacyStockCardList(String[] parameterField, String[] param)
{
List <spPharmacyStockCard> result = new List <spPharmacyStockCard>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(spPharmacyStockCard));
ctx.CommandText = "spfmrpt_KartuPersediaanFarmasi";
ctx.CommandType = System.Data.CommandType.StoredProcedure;
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((spPharmacyStockCard)helper.IDataReaderToObject(reader, new spPharmacyStockCard()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <ReportParameterLabel> GetReportParameterLabelList(string filterExpression)
{
List <ReportParameterLabel> result = new List <ReportParameterLabel>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(ReportParameterLabel));
ctx.CommandText = helper.Select(filterExpression);
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((ReportParameterLabel)helper.IDataReaderToObject(reader, new ReportParameterLabel()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public Pasien Get(String NoRM)
{
_ctx.CommandText = _helper.GetRecord();
_ctx.Add(p_norm, NoRM);
DataRow row = DaoBase.GetDataRow(_ctx);
return((row == null) ? null : (Pasien)_helper.DataRowToObject(row, new Pasien()));
}
public ReportParameterLabel Get(Int32 id)
{
_ctx.CommandText = _helper.GetRecord();
_ctx.Add(p_Id, id);
DataRow row = DaoBase.GetDataRow(_ctx);
return((row == null) ? null : (ReportParameterLabel)_helper.DataRowToObject(row, new ReportParameterLabel()));
}
public App Get(String AppId)
{
_ctx.CommandText = _helper.GetRecord();
_ctx.Add(p_AppId, AppId);
DataRow row = DaoBase.GetDataRow(_ctx);
return((row == null) ? null : (App)_helper.DataRowToObject(row, new App()));
}
public Report Get(String reportID)
{
_ctx.CommandText = _helper.GetRecord();
_ctx.Add(p_ReportID, reportID);
DataRow row = DaoBase.GetDataRow(_ctx);
return((row == null) ? null : (Report)_helper.DataRowToObject(row, new Report()));
}
public SetVar Get(String app, String kode)
{
_ctx.CommandText = _helper.GetRecord();
_ctx.Add(p_AppId, app);
_ctx.Add(p_Kode, kode);
DataRow row = DaoBase.GetDataRow(_ctx);
return((row == null) ? null : (SetVar)_helper.DataRowToObject(row, new SetVar()));
}
/// <summary>
/// 一致フラグを更新する
/// </summary>
/// <param name="actionId">帳簿項目ID</param>
/// <param name="isMatch">一致フラグ</param>
private async Task ChangeIsMatchAsync(int actionId, bool isMatch)
{
using (DaoBase dao = this.builder.Build()) {
await dao.ExecNonQueryAsync(@"
UPDATE hst_action
SET is_match = @{0}, update_time = 'now', updater = @{1}
WHERE action_id = @{2};", isMatch? 1 : 0, Updater, actionId);
}
this.IsMatchChanged?.Invoke(this, new EventArgs <int>(actionId));
}
public DataTable GetResultData(string tableName, string sql)
{
using (DaoBase dao = GetDao(DbConnections.TargetDbConnection))
{
using (SqlCommand cmd = dao.CreateCommand())
{
cmd.CommandText = sql;
DataTable dtt = dao.ExecuteResultSet(cmd, tableName, true);
return(dtt);
}
}
}
public void Logout(Guid token)
{
using (var dbContext = new DaoBase())
{
var loginInfo = dbContext.FindAll <LoginInfo>(l => l.LoginToken == token).FirstOrDefault();
if (loginInfo != null)
{
dbContext.Delete <LoginInfo>(loginInfo);
}
}
CacheHelper.Remove(string.Format(_LoginInfoKeyFormat, token));
}
/// <summary>
/// 新しい採番された生産計画Noを取得する
/// </summary>
/// <param name="scheduledKeyNo">旧生産計画No</param>
/// <returns></returns>
public string GetProductionPlanNo(string scheduledKeyNo)
{
using (DaoBase dao = GetDao(DbConnections.SourceDbConnection))
{
using (SqlCommand cmd = dao.CreateCommand())
{
cmd.CommandText = Properties.Resources.GetNewProductionNo;
cmd.Parameters.AddWithValue("@SCHEDULED_KEY_NO", scheduledKeyNo);
string planNo = Utility.DBToString(dao.ExecuteScalar(cmd));
return(planNo);
}
}
}
/// <summary>
/// 比較情報を更新する
/// </summary>
private async Task UpdateComparisonInfoAsync()
{
// 指定された帳簿内で、日付、金額が一致する帳簿項目を探す
using (DaoBase dao = this.builder.Build()) {
foreach (var vm in this.WVM.CsvComparisonVMList)
{
// 前回の結果をクリアする
vm.ClearActionInfo();
DaoReader reader = await dao.ExecQueryAsync(@"
SELECT A.action_id, I.item_name, A.act_value, A.shop_name, A.remark, A.is_match
FROM hst_action A
INNER JOIN (SELECT * FROM mst_item WHERE del_flg = 0) I ON I.item_id = A.item_id
WHERE to_date(to_char(act_time, 'YYYY-MM-DD'), 'YYYY-MM-DD') = @{0} AND A.act_value = -@{1} AND book_id = @{2} AND A.del_flg = 0;", vm.Record.Date, vm.Record.Value, this.WVM.SelectedBookVM.Id);
reader.ExecWholeRow((count, record) => {
int actionId = record.ToInt("action_id");
string itemName = record["item_name"];
int outgo = Math.Abs(record.ToInt("act_value"));
string shopName = record["shop_name"];
string remark = record["remark"];
bool isMatch = record.ToInt("is_match") == 1;
// 帳簿項目IDが使用済なら次のレコードを調べるようにする
bool ans = this.WVM.CsvComparisonVMList.Where((tmpVM) => { return(tmpVM.ActionId == actionId); }).Count() != 0;
if (!ans)
{
vm.ActionId = actionId;
vm.ItemName = itemName;
vm.ShopName = shopName;
vm.Remark = remark;
vm.IsMatch = isMatch;
}
return(ans);
});
}
}
List <CsvComparisonViewModel> list = this.WVM.CsvComparisonVMList.ToList();
// 日付と帳簿項目IDでソートする
list.Sort((vm1, vm2) => {
int rslt = (int)((vm1.Record.Date - vm2.Record.Date).TotalDays);
if (rslt == 0)
{
rslt = (vm1.ActionId ?? 0) - (vm2.ActionId ?? 0);
}
return(rslt);
});
this.WVM.CsvComparisonVMList = new ObservableCollection <CsvComparisonViewModel>(list);
}
/// <summary>
/// 新しい採番された稼動計画Noを取得する
/// </summary>
/// <param name="facilityCode">旧設備コード</param>
/// <param name="operationDate">稼動日（yyyy/MM/dd）</param>
/// <returns></returns>
public string GetOperationPlanNo(string facilityCode, string operationDate)
{
using (DaoBase dao = GetDao(DbConnections.SourceDbConnection))
{
using (SqlCommand cmd = dao.CreateCommand())
{
cmd.CommandText = Properties.Resources.GetNewOperationNo;
cmd.Parameters.AddWithValue("@FACILITY_CODE", facilityCode);
cmd.Parameters.AddWithValue("@OPERATION_DATE", operationDate);
string planNo = Utility.DBToString(dao.ExecuteScalar(cmd));
return(planNo);
}
}
}
public TableLayoutInfo GetTableLayout(DbConnections conn, string tableName)
{
using (DaoBase dao = GetDao(conn))
{
using (SqlCommand cmd = dao.CreateCommand())
{
cmd.CommandText = Properties.Resources.GetTableLayout;
cmd.AddParameter("@tableName", tableName);
DataTable dtt = dao.ExecuteResultSet(cmd, tableName, false);
TableLayoutInfo tableInfo = new TableLayoutInfo(tableName, dtt);
return(tableInfo);
}
}
}
public RestfulArray <NoteView> GetNoteList(int page, int pageSize)
{
var noteList = new DaoBase <NoteView, int>().GetListPaged(page, pageSize, null, null);
foreach (var item in noteList)
{
item.imgList = new ImageDao().GetImageList(item.Id, 3);
}
return(new RestfulArray <NoteView>
{
data = noteList,
total = new AttractionDao().RecordCount()
});
}
public int Delete(Int32 id)
{
ReportParameterLabel record;
if (_ctx.Transaction == null)
{
record = new ReportParameterLabelDao().Get(id);
}
else
{
record = Get(id);
}
_helper.Delete(_ctx, record, _isAuditLog);
return(DaoBase.ExecuteNonQuery(_ctx));
}
public int Delete(String AppId)
{
App record;
if (_ctx.Transaction == null)
{
record = new AppDao().Get(AppId);
}
else
{
record = Get(AppId);
}
_helper.Delete(_ctx, record, _isAuditLog);
return(DaoBase.ExecuteNonQuery(_ctx));
}
public int Delete(String NoRM)
{
Pasien record;
if (_ctx.Transaction == null)
{
record = new PasienDao().Get(NoRM);
}
else
{
record = Get(NoRM);
}
_helper.Delete(_ctx, record, _isAuditLog);
return(DaoBase.ExecuteNonQuery(_ctx));
}
public int Delete(String app, String kode)
{
SetVar record;
if (_ctx.Transaction == null)
{
record = new SetVarDao().Get(app, kode);
}
else
{
record = Get(app, kode);
}
_helper.Delete(_ctx, record, _isAuditLog);
return(DaoBase.ExecuteNonQuery(_ctx));
}
