public void DeleteTempNotifyPremium()
{
try
{
using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities())
{
int numOfDay;
var tmp = slmdb.kkslm_ms_option.Where(p => p.slm_OptionCode == "notify_premium_temp_day" && p.is_Deleted == 0).Select(p => p.slm_OptionDesc).FirstOrDefault();
if (!int.TryParse(tmp, out numOfDay))
{
numOfDay = 60;
}
DateTime date = DateTime.Now.AddDays(-numOfDay);
var strDate = date.Year.ToString() + date.ToString("-MM-dd");
string sql = $"DELETE FROM kkslm_tr_notify_premium_temp WHERE slm_CreatedDate < '{strDate}' ";
slmdb.ExecuteStoreCommand(sql);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = "Purge data in kkslm_tr_notify_premium_temp failed, Error=" + message;
Util.WriteLogFile(logfilename, BatchCode, errorDetail);
BizUtil.InsertLog(BatchMonitorId, "", "", errorDetail);
}
}
public void ResetRunningOfTicketId(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
try
{
totalRecord = 1;
batchMonitorId = BizUtil.SetStartTime(batchCode);
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
var curr_year = slmdb.kkslm_ms_current_year.FirstOrDefault();
if (curr_year != null)
{
int year = curr_year.slm_year != null ? curr_year.slm_year.Value : 0;
if (year != 0)
{
if (DateTime.Now.Year > year)
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
curr_year.slm_year = DateTime.Now.Year;
slmdb.SaveChanges();
slmdb.ExecuteStoreCommand("DBCC CHECKIDENT ('dbo.kkslm_gen_identity', RESEED, 0)");
ts.Complete();
}
}
totalSuccess = 1;
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
else
{
throw new Exception("ไม่พบข้อมูล current year ในตาราง kkslm_ms_current_year");
}
}
else
{
throw new Exception("ไม่พบข้อมูล current year ในตาราง kkslm_ms_current_year");
}
}
catch (Exception ex)
{
totalFail = 1;
Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, batchCode, message);
BizUtil.InsertLog(batchMonitorId, "", "", message);
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}
private void UpdatePreleadSMSFlag(SLMDBEntities slmdb, decimal preleadId, bool flag)
{
string sql = string.Format("UPDATE kkslm_tr_prelead SET {0} = {1} WHERE slm_Prelead_Id = {2}"
, (BatchCode == "OBT_PRO_32" ? "slm_IsSMS70" : "slm_IsSMS8")
, (flag ? "1" : "0")
, preleadId.ToString());
slmdb.ExecuteStoreCommand(sql);
}
public void CalculateJobOnHand(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
List <JobOnHandData> list = GetJobOnHand();
totalRecord = list.Count;
foreach (JobOnHandData data in list)
{
try
{
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
string sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff
SET slm_JobOnHand = '" + (data.AmountOwnerJob + data.AmountDelegateJob).ToString() + @"'
WHERE slm_UserName = '******'";
slmdb.ExecuteStoreCommand(sql);
totalSuccess += 1;
Console.WriteLine("Usrename " + data.Username + ": SUCCESS");
}
catch (Exception ex)
{
totalFail += 1;
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = "Username="******", Error=" + message;
BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
Util.WriteLogFile(logfilename, batchCode, errorDetail);
Console.WriteLine("Usrename " + data.Username + ": FAIL");
}
}
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
catch (Exception ex)
{
Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, batchCode, message);
BizUtil.InsertLog(batchMonitorId, "", "", message);
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}
private void DeleteExistingData()
{
try
{
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
string sql = "TRUNCATE TABLE " + AppConstant.SLMDBName + ".dbo.kkslm_tr_renewinsurance_view";
slmdb.ExecuteStoreCommand(sql);
}
catch (Exception ex)
{
throw ex;
}
}
//public void PurgeData(string batchCode)
//{
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// DateTime purgeDate = DateTime.Today.AddDays(-AppConstant.PurgeNotificationNumOfDay);
// string str_purgeDate = purgeDate.Year.ToString() + purgeDate.ToString("-MM-dd");
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// string sql = "SELECT COUNT(slm_NotificationId) FROM " + AppConstant.SLMDBName + ".dbo.kkslm_tr_notification WHERE CONVERT(DATE, slm_CreatedDate) < '" + str_purgeDate + "'";
// totalRecord = slmdb.ExecuteStoreQuery<int>(sql).FirstOrDefault();
// string del = "DELETE FROM " + AppConstant.SLMDBName + ".dbo.kkslm_tr_notification WHERE CONVERT(DATE, slm_CreatedDate) < '" + str_purgeDate + "'";
// totalSuccess = slmdb.ExecuteStoreCommand(del);
// BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
// }
// catch (Exception ex)
// {
// totalFail = totalRecord;
// Console.WriteLine("All FAIL");
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// Util.WriteLogFile(logfilename, batchCode, message);
// BizUtil.InsertLog(batchMonitorId, "", "", message);
// BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
// }
//}
#endregion
/// <summary>
/// Purge Notification Data
/// </summary>
/// <param name="batchCode"></param>
public void PurgeData(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
int currentTotal = 0;
DateTime purgeDate = new DateTime();
string strPurgeDate = "";
string sql = "";
string del = "";
string slmdbName = AppConstant.SLMDBName;
Dictionary <int, int> totalList = new Dictionary <int, int>();
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities())
{
var configList = slmdb.kkslm_ms_config_purge_day.Where(p => p.slm_BatchCode == batchCode && p.is_Deleted == false).ToList();
var typeList = slmdb.kkslm_tr_notification.Where(p => p.slm_NotificationType != null).Select(p => p.slm_NotificationType.Value).Distinct().OrderBy(p => p).ToList();
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
foreach (int type in typeList)
{
currentTotal = 0;
var numOfDay = configList.Where(p => p.slm_Type == type.ToString()).Select(p => p.slm_NumOfDay).FirstOrDefault();
if (numOfDay != null && numOfDay >= 0)
{
purgeDate = DateTime.Today.AddDays(-numOfDay.Value);
strPurgeDate = purgeDate.Year.ToString() + purgeDate.ToString("-MM-dd");
}
else
{
purgeDate = DateTime.Today.AddDays(-AppConstant.PurgeNotificationNumOfDay);
strPurgeDate = purgeDate.Year.ToString() + purgeDate.ToString("-MM-dd");
}
sql = "SELECT COUNT(slm_NotificationId) FROM " + slmdbName + ".dbo.kkslm_tr_notification WHERE slm_NotificationType = '" + type.ToString() + "' AND CONVERT(DATE, slm_CreatedDate) < '" + strPurgeDate + "'";
totalRecord += slmdb.ExecuteStoreQuery <int>(sql).FirstOrDefault();
del = "DELETE FROM " + slmdbName + ".dbo.kkslm_tr_notification WHERE slm_NotificationType = '" + type.ToString() + "' AND CONVERT(DATE, slm_CreatedDate) < '" + strPurgeDate + "'";
currentTotal = slmdb.ExecuteStoreCommand(del);
totalList.Add(type, currentTotal);
totalSuccess += currentTotal;
}
ts.Complete();
}
}
//Summary Log
if (totalList.Count > 0)
{
string msg = "";
foreach (KeyValuePair <int, int> data in totalList)
{
msg += (msg != "" ? ", " : "") + "Type " + data.Key.ToString() + " (" + data.Value.ToString("#,##0") + " records)";
}
msg = "Success : " + msg;
Util.WriteLogFile(logfilename, batchCode, msg);
BizUtil.InsertLog(batchMonitorId, "", "", msg);
}
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
catch (Exception ex)
{
totalFail = totalRecord;
totalSuccess = 0;
Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, batchCode, message);
BizUtil.InsertLog(batchMonitorId, "", "", message);
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}