//public void CalculatePerformance(string batchCode)
//{
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// int day = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
// DateTime lastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, day);
// string year = lastDay.Year.ToString();
// string month = lastDay.Month.ToString("00");
// List<TelesalesPerformanceData> list = GetPerformanceData(year, month, lastDay);
// totalRecord = list.Count;
// foreach (TelesalesPerformanceData data in list)
// {
// try
// {
// DateTime createDate = DateTime.Now;
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// kkslm_tr_performance perform = new kkslm_tr_performance()
// {
// slm_TeamTelesales_Code = data.TelesalesTeam,
// slm_EmpCode = data.EmpCode,
// slm_EmpName = data.Fullname,
// slm_Performance = data.PerformanceAmount,
// slm_Year = year,
// slm_Month = month,
// slm_Level = data.LevelId,
// slm_CreatedBy = "SYSTEM",
// slm_CreatedDate = createDate,
// slm_UpdatedBy = "SYSTEM",
// slm_UpdatedDate = createDate,
// is_Deleted = false
// };
// slmdb.kkslm_tr_performance.AddObject(perform);
// slmdb.SaveChanges();
// totalSuccess += 1;
// Console.WriteLine("Owner " + data.Username + ": SUCCESS");
// }
// catch (Exception ex)
// {
// totalFail += 1;
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// string errorDetail = "Owner=" + data.Username + ", Error=" + message;
// BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// Console.WriteLine("Owner " + 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);
// throw ex;
// }
//}
#endregion
#region Backup 2017-03-22
// /// <summary>
// /// คำนวณ Performance รายเดือนของ Telesales
// /// </summary>
// /// <param name="batchCode"></param>
// public void CalculatePerformance(string batchCode)
// {
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// bool successFlag = true;
// List<decimal> tempList = new List<decimal>();
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// int day = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
// DateTime lastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, day);
// string year = lastDay.Year.ToString();
// string month = lastDay.Month.ToString("00");
// List<TelesalesPerformanceData> list = GetPerformanceData(year, month, lastDay);
// totalRecord = list.Count;
// foreach (TelesalesPerformanceData data in list)
// {
// try
// {
// DateTime createDate = DateTime.Now;
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// kkslm_tr_performance perform = new kkslm_tr_performance()
// {
// slm_TeamTelesales_Code = data.TelesalesTeam,
// slm_EmpCode = data.EmpCode,
// slm_EmpName = data.Fullname,
// slm_Performance = data.PerformanceAmount,
// slm_Year = year,
// slm_Month = month,
// slm_Level = data.LevelId,
// slm_CreatedBy = "SYSTEM",
// slm_CreatedDate = createDate,
// slm_UpdatedBy = "SYSTEM",
// slm_UpdatedDate = createDate,
// is_Deleted = false
// };
// slmdb.kkslm_tr_performance.AddObject(perform);
// slmdb.SaveChanges();
// tempList.Add(perform.slm_Performance_Id);
// totalSuccess += 1;
// Console.WriteLine("Owner " + data.Username + ": SUCCESS");
// }
// catch (Exception ex)
// {
// successFlag = false;
// Rollback(batchMonitorId, batchCode, tempList);
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// string errorDetail = "Owner=" + data.Username + ", Error=" + message;
// BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// successFlag = false;
// totalSuccess = 0;
// totalFail = totalRecord;
// break;
// //Console.WriteLine("Owner " + data.Username + ": FAIL");
// }
// }
// BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), 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 List<TelesalesPerformanceData> GetPerformanceData(string year, string month, DateTime lastDay)
// {
// try
// {
// if (lastDay == DateTime.Today)
// {
//// string sql = @"SELECT lead.slm_Owner AS [Username], staff.slm_EmpCode AS EmpCode, staff.slm_StaffNameTH AS Fullname
//// , staff.slm_Team AS TelesalesTeam, staff.slm_Level AS LevelId, lev.slm_LevelName AS LevelName
//// , SUM(reins.slm_PolicyGrossPremium + reins.slm_ActNetPremium) AS PerformanceAmount
//// FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead
//// INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_renewinsurance reins ON reins.slm_TicketId = lead.slm_ticketId
//// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff staff ON staff.slm_UserName = lead.slm_Owner
//// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_level lev ON lev.slm_LevelId = staff.slm_Level
//// WHERE lead.is_Deleted = 0 AND reins.slm_IncentiveFlag = 1 AND YEAR(reins.slm_IncentiveDate) = " + year + @" AND MONTH(reins.slm_IncentiveDate) = " + month + @"
//// GROUP BY lead.slm_Owner, staff.slm_EmpCode, staff.slm_StaffNameTH, staff.slm_Team, staff.slm_Level, lev.slm_LevelName ";
// string sql = @"SELECT lead.slm_Owner AS [Username], staff.slm_EmpCode AS EmpCode, staff.slm_StaffNameTH AS Fullname
// , team.slm_TeamTelesales_Code AS TelesalesTeam, staff.slm_Level AS LevelId, lev.slm_LevelName AS LevelName
// , SUM(ISNULL(reins.slm_PolicyGrossPremium, 0) + ISNULL(reins.slm_ActGrossPremium, 0)) AS PerformanceAmount
// FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead
// INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_renewinsurance reins ON reins.slm_TicketId = lead.slm_ticketId
// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff staff ON staff.slm_UserName = lead.slm_Owner
// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_level lev ON lev.slm_LevelId = staff.slm_Level
// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_teamtelesales team ON team.slm_TeamTelesales_Id = staff.slm_TeamTelesales_Id
// WHERE lead.is_Deleted = 0 AND
// (
// (reins.slm_IncentiveFlag = 1 AND YEAR(reins.slm_IncentiveDate) = '" + year + @"' AND MONTH(reins.slm_IncentiveDate) = '" + month + @"') OR
// (reins.slm_ActIncentiveFlag = 1 AND YEAR(reins.slm_ActIncentiveDate) = '" + year + @"' AND MONTH(reins.slm_ActIncentiveDate) = '" + month + @"')
// )
// GROUP BY lead.slm_Owner, staff.slm_EmpCode, staff.slm_StaffNameTH, team.slm_TeamTelesales_Code, staff.slm_Level, lev.slm_LevelName ";
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// return slmdb.ExecuteStoreQuery<TelesalesPerformanceData>(sql).ToList();
// }
// else
// return new List<TelesalesPerformanceData>();
// }
// catch (Exception ex)
// {
// throw ex;
// }
// }
// private void Rollback(Int64 batchMonitorId, string batchCode, List<decimal> tempList)
// {
// try
// {
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// string sql = "";
// foreach (decimal id in tempList)
// {
// sql = "DELETE FROM " + AppConstant.SLMDBName + ".dbo.kkslm_tr_performance WHERE slm_Performance_Id = '" + id.ToString() + "'";
// slmdb.ExecuteStoreCommand(sql);
// }
// }
// catch (Exception ex)
// {
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// BizUtil.InsertLog(batchMonitorId, "", "", message);
// Util.WriteLogFile(logfilename, batchCode, message);
// }
// }
#endregion
public void CalculatePerformance(string batchCode)
{
Int64 batchMonitorId = 0;
bool successFlag = true;
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
int iDataCount = database.CalculatePerformance();
BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), iDataCount, iDataCount, 0);
}
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, 0, 0, 0);
}
}
//public void InsertCoverageType(string batchCode)
//{
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// List<int> idList = new List<int>();
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// DataTable dtCoverage = GetCoverageTypeFromBDW();
// totalRecord = dtCoverage.Rows.Count;
// foreach (DataRow dr in dtCoverage.Rows)
// {
// try
// {
// if (dr["COVERAGE_NO"].ToString().Trim() == "")
// throw new Exception("COVERAGE_NO is null or empty");
// if (dr["COVERAGE_DESC"].ToString().Trim() == "")
// throw new Exception("COVERAGE_DESC is null or empty");
// int coverateTypeId = int.Parse(dr["COVERAGE_NO"].ToString().Trim());
// idList.Add(coverateTypeId);
// DateTime createdDate = DateTime.Now;
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// var coverage = slmdb.kkslm_ms_coveragetype.Where(p => p.slm_CoverageTypeId == coverateTypeId).FirstOrDefault();
// if (coverage == null)
// {
// kkslm_ms_coveragetype objInsert = new kkslm_ms_coveragetype()
// {
// slm_CoverageTypeId = coverateTypeId,
// slm_ConverageTypeName = dr["COVERAGE_DESC"].ToString().Trim(),
// slm_CreatedBy = "SYSTEM",
// slm_CreatedDate = createdDate,
// slm_UpdatedBy = "SYSTEM",
// slm_UpdatedDate = createdDate,
// is_Deleted = false
// };
// slmdb.kkslm_ms_coveragetype.AddObject(objInsert);
// }
// else
// {
// coverage.slm_ConverageTypeName = dr["COVERAGE_DESC"].ToString().Trim();
// coverage.slm_UpdatedBy = "SYSTEM";
// coverage.slm_UpdatedDate = createdDate;
// coverage.is_Deleted = false;
// }
// slmdb.SaveChanges();
// totalSuccess += 1;
// Console.WriteLine("CoverageNo " + dr["COVERAGE_NO"].ToString() + ": SUCCESS");
// }
// catch (Exception ex)
// {
// totalFail += 1;
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// string errorDetail = "CoverageNo=" + dr["COVERAGE_NO"].ToString() + ", Error=" + message;
// BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// Console.WriteLine("CoverageNo " + dr["COVERAGE_NO"].ToString() + ": FAIL");
// }
// }
// SetFlagIsDeleted(idList);
// 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);
// throw ex;
// }
//}
#endregion
/// <summary>
/// Update ข้อมูลประเภทประกันภัยและประเภทความคุ้มครอง จาก DataWarehouse ไปที่ OBT
/// </summary>
/// <param name="batchCode"></param>
public bool InsertCoverageType(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
List <int> idList = new List <int>();
bool ret = false;
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
DataTable dtCoverage = GetCoverageTypeFromBDW(batchMonitorId, batchCode);
totalRecord = dtCoverage.Rows.Count;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
foreach (DataRow dr in dtCoverage.Rows)
{
if (dr["COVERAGE_NO"].ToString().Trim() == "")
{
throw new Exception("COVERAGE_NO is null or empty");
}
if (dr["COVERAGE_DESC"].ToString().Trim() == "")
{
throw new Exception("COVERAGE_DESC is null or empty");
}
int coverateTypeId = int.Parse(dr["COVERAGE_NO"].ToString().Trim());
idList.Add(coverateTypeId);
DateTime createdDate = DateTime.Now;
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
var coverage = slmdb.kkslm_ms_coveragetype.Where(p => p.slm_CoverageTypeId == coverateTypeId).FirstOrDefault();
if (coverage == null)
{
kkslm_ms_coveragetype objInsert = new kkslm_ms_coveragetype()
{
slm_CoverageTypeId = coverateTypeId,
slm_ConverageTypeName = dr["COVERAGE_DESC"].ToString().Trim(),
slm_CreatedBy = "SYSTEM",
slm_CreatedDate = createdDate,
slm_UpdatedBy = "SYSTEM",
slm_UpdatedDate = createdDate,
is_Deleted = false
};
slmdb.kkslm_ms_coveragetype.AddObject(objInsert);
}
else
{
coverage.slm_ConverageTypeName = dr["COVERAGE_DESC"].ToString().Trim();
coverage.slm_UpdatedBy = "SYSTEM";
coverage.slm_UpdatedDate = createdDate;
coverage.is_Deleted = false;
}
slmdb.SaveChanges();
totalSuccess += 1;
//Console.WriteLine("CoverageNo " + dr["COVERAGE_NO"].ToString() + ": SUCCESS");
}
SetFlagIsDeleted(idList);
ts.Complete();
}
ret = true;
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
catch (Exception ex)
{
ret = false;
//Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, batchCode, message);
BizUtil.InsertLog(batchMonitorId, "", "", message);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
return(ret);
}
//public void InsertReinsuranceView(string batchCode)
//{
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// List<RenewInsuranceViewData> list = RenewInsuranceDataList();
// totalRecord = list.Count;
// DeleteExistingData();
// foreach (RenewInsuranceViewData data in list)
// {
// try
// {
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// DateTime createDate = DateTime.Now;
// kkslm_tr_renewinsurance_view view = new kkslm_tr_renewinsurance_view()
// {
// slm_TicketId = data.TicketId,
// slm_CampaignId = data.CampaignId,
// slm_Product_Id = data.ProductId,
// slm_TitleName = data.TitleName,
// slm_Name = data.FirstName,
// slm_LastName = data.LastName,
// slm_TelNo_1 = data.TelNo1,
// slm_CardTypeName = data.CardTypeName,
// slm_CitizenId = data.CitizenId,
// slm_Birthdate = data.BirthDate,
// slm_MaritalStatus = data.MaritalStatus,
// slm_OccupationNameTH = data.OccupationName,
// slm_ContractNo = data.ContractNo,
// slm_PolicyNo = data.PolicyNo,
// slm_PolicyStartCoverDate = data.PolicyStartDate,
// slm_PolicyEndCoverDate = data.PolicyEndDate,
// slm_PolicyCost = data.PolicyCost,
// slm_PolicyGrossPremium = data.PolicyGrossPremium,
// slm_PolicyDiscountAmt = data.PolicyDiscount,
// slm_ActNo = data.ActNo,
// slm_ActStartCoverDate = data.ActStartDate,
// slm_ActEndCoverDate = data.ActEndDate,
// slm_ActGrossPremium = data.ActGrossPremium,
// slm_ActVat = data.ActVat,
// slm_ActStamp = data.ActStamp,
// slm_ActNetPremium = data.ActNetPremium,
// slm_CreatedBy = "SYSTEM",
// slm_CreatedDate = createDate,
// slm_UpdatedBy = "SYSTEM",
// slm_UpdatedDate = createDate,
// };
// slmdb.kkslm_tr_renewinsurance_view.AddObject(view);
// slmdb.SaveChanges();
// totalSuccess += 1;
// Console.WriteLine("TicketId " + data.TicketId + ": SUCCESS");
// }
// catch (Exception ex)
// {
// totalFail += 1;
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// string errorDetail = "TicketId=" + data.TicketId + ", Error=" + message;
// BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// Console.WriteLine("TicketId " + data.TicketId + ": 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);
// throw ex;
// }
//}
#endregion
public void InsertReinsuranceView(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
bool successFlag = true;
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
List <RenewInsuranceViewData> list = RenewInsuranceDataList();
totalRecord = list.Count;
DeleteExistingData();
foreach (RenewInsuranceViewData data in list)
{
try
{
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
DateTime createDate = DateTime.Now;
kkslm_tr_renewinsurance_view view = new kkslm_tr_renewinsurance_view()
{
slm_TicketId = data.TicketId,
slm_CampaignId = data.CampaignId,
slm_Product_Id = data.ProductId,
slm_TitleName = data.TitleName,
slm_Name = data.FirstName,
slm_LastName = data.LastName,
slm_TelNo_1 = data.TelNo1,
slm_CardTypeName = data.CardTypeName,
slm_CitizenId = data.CitizenId,
slm_Birthdate = data.BirthDate,
slm_MaritalStatus = data.MaritalStatus,
slm_OccupationNameTH = data.OccupationName,
slm_ContractNo = data.ContractNo,
slm_PolicyNo = data.PolicyNo,
slm_PolicyStartCoverDate = data.PolicyStartDate,
slm_PolicyEndCoverDate = data.PolicyEndDate,
slm_PolicyCost = data.PolicyCost,
slm_PolicyGrossPremium = data.PolicyGrossPremium,
slm_PolicyDiscountAmt = data.PolicyDiscount,
slm_ActNo = data.ActNo,
slm_ActStartCoverDate = data.ActStartDate,
slm_ActEndCoverDate = data.ActEndDate,
slm_ActGrossPremium = data.ActGrossPremium,
slm_ActVat = data.ActVat,
slm_ActStamp = data.ActStamp,
slm_ActNetPremium = data.ActNetPremium,
slm_CreatedBy = "SYSTEM",
slm_CreatedDate = createDate,
slm_UpdatedBy = "SYSTEM",
slm_UpdatedDate = createDate,
};
slmdb.kkslm_tr_renewinsurance_view.AddObject(view);
slmdb.SaveChanges();
totalSuccess += 1;
Console.WriteLine("TicketId " + data.TicketId + ": SUCCESS");
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = "TicketId=" + data.TicketId + ", Error=" + message;
BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
Util.WriteLogFile(logfilename, batchCode, errorDetail);
successFlag = false;
totalSuccess = 0;
totalFail = totalRecord;
DeleteExistingData();
break;
}
}
BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), 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);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}
public void GenerateSMS(string batchCode)
{
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
try
{
BatchCode = batchCode;
BatchMonitorId = BizUtil.SetStartTime(BatchCode);
BizUtil.CheckPrerequisite(BatchCode);
using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities())
{
List <kkslm_tr_renewinsurance> renewList = slmdb.kkslm_tr_renewinsurance
.Where(p => p.slm_GenSMSReceiveNo == null || p.slm_GenSMSReceiveNo == false)
.Where(p => p.slm_ReceiveNo != null && p.slm_ReceiveNo != "")
.ToList();
totalRecord = renewList.Count;
List <InsuranceCompanyData> companyList = GetInsuranceCompany(slmdb);
List <kkslm_ms_coveragetype> coverageList = slmdb.kkslm_ms_coveragetype.ToList();
foreach (kkslm_tr_renewinsurance renew in renewList)
{
string smsMessage = "";
try
{
renew.slm_GenSMSReceiveNo = true;
renew.slm_GenSMSReceiveNoDate = DateTime.Now;
smsMessage = InsertPrepareSMS(slmdb, renew, companyList, coverageList);
slmdb.SaveChanges();
totalSuccess += 1;
//Debug.WriteLine($"smsMessage: {smsMessage}");
//Console.WriteLine("TicketId " + renew.slm_TicketId + ": SUCCESS");
}
catch (Exception ex)
{
totalFail += 1;
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = string.Format("TicketId={0}, SMSMessage={1}, Error={2}", renew.slm_TicketId, smsMessage, message);
BizUtil.InsertLog(BatchMonitorId, renew.slm_TicketId, "", errorDetail);
Util.WriteLogFile(logfilename, BatchCode, errorDetail);
}
}
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);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}
public void GenerateSMS(string batchCode)
{
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
try
{
BatchCode = batchCode;
BatchMonitorId = BizUtil.SetStartTime(BatchCode);
BizUtil.CheckPrerequisite(BatchCode);
List <OBT_PRO_32_DataAccess.SMSPaymentDueMessage> dueList = DA.LoadRenewForSMS();
totalRecord = dueList.Count;
foreach (OBT_PRO_32_DataAccess.SMSPaymentDueMessage due in dueList)
{
string smsMessage = "";
try
{
if (string.IsNullOrWhiteSpace(due.TelNo))
{
throw new ArgumentNullException("TelNo", "TelNo is null or empty");
}
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities())
{
smsMessage = InsertPrepareSMS(slmdb, due);
slmdb.SaveChanges();
if (due.PreleadId != null)
{
UpdatePreleadSMSFlag(slmdb, due.PreleadId.Value, true);
}
totalSuccess += 1;
//Debug.WriteLine($"smsMessage: {smsMessage}");
//Console.WriteLine("TicketId " + due.slm_TicketId + ": SUCCESS");
}
ts.Complete();
}
}
catch (Exception ex)
{
totalFail += 1;
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = string.Format("PreleadId={0}, TicketId={1}, ExpiredDate={2}, LicenseNo={3}, TelNo={4}, SMSMessage={5}, Error={6}",
due.PreleadId != null ? due.PreleadId.ToString() : "",
due.TicketId,
due.ExpiredDate != null ? due.ExpiredDate.Value.ToString("dd/MM/") + due.ExpiredDate.Value.Year.ToString() : "",
due.LicenseNo,
due.TelNo,
smsMessage,
message);
BizUtil.InsertLog(BatchMonitorId, due.TicketId, "", errorDetail);
if (AppConstant.OBT_PRO_32_LogSwitch != "Y")
{
errorDetail = string.Format("PreleadId={0}, TicketId={1}, Error={2}",
due.PreleadId != null ? due.PreleadId.ToString() : "",
due.TicketId,
message);
}
Util.WriteLogFile(logfilename, BatchCode, errorDetail);
}
}
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);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
}
/// <summary>
/// Update ข้อมูลเลขเล่มกรมธรรม์, เลขพรบ. จาก DataWarehouse ไปที่ OBT
/// </summary>
/// <param name="batchCode"></param>
public bool UpdatePolicyNo(string batchCode)
{
// รอเทส SIT2
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
string ticketId = "";
string[] statusExclude = new string[] { "08", "09", "10" };
bool ret = false;
try
{
BatchCode = batchCode;
BatchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
DataTable dtPolicy = GetPolicyNoFromBDW(BatchMonitorId, batchCode);
string contractListBDW = string.Join(",", dtPolicy.AsEnumerable().Select(y => y.Field <string>("CONTRACT_NUMBER")).Distinct().ToArray());
var pendingList = new List <kkslm_tr_renewinsurance_hp_policyno_actno_pending>();
using (SLMDBEntities db = AppUtil.GetSlmDbEntities())
{
pendingList = db.kkslm_tr_renewinsurance_hp_policyno_actno_pending.Where(p => p.is_Deleted == false && !contractListBDW.Contains(p.slm_Contract_Number)).ToList();
}
//Move data from pending to precess again
foreach (var item in pendingList)
{
DataRow dr = dtPolicy.NewRow();
dr[eBDW.CONTRACT_NUMBER.ToString()] = item.slm_Contract_Number;
dr[eBDW.POLICY_YEAR.ToString()] = item.slm_Policy_Year;
dr[eBDW.POLICY_NO.ToString()] = item.slm_Policy_No;
dr[eBDW.POLICY_TYPE.ToString()] = item.slm_Policy_Type;
dr[eBDW.MAIN_BY.ToString()] = item.slm_Main_By;
dr[eBDW.MAIN_DATE.ToString()] = item.slm_Main_Date;
dr[eBDW.MAIN_TIME.ToString()] = item.slm_Main_Time;
dr[eBDW.CREATE_BY.ToString()] = item.slm_Create_By;
dr[eBDW.CREATE_DATE.ToString()] = item.slm_Create_Date;
dr[eBDW.CREATE_TIME.ToString()] = item.slm_Create_Time;
dr[eBDW.DATA_SOURCE.ToString()] = "PENDING";
dtPolicy.Rows.Add(dr);
}
var contractInsureList = ConvretToList(dtPolicy);
int countTotalBDW = contractInsureList.Count(p => p.DataSource == "BDW");
int countTotalPending = contractInsureList.Count(p => p.DataSource == "PENDING");
//Snap DataSource
SnapDataSource(contractInsureList);
//Validate List
ValidateList(contractInsureList);
//Log amount of actual data to process
int countBDW = contractInsureList.Count(p => p.DataSource == "BDW");
int countPending = contractInsureList.Count(p => p.DataSource == "PENDING");
string msg = string.Format("Data from BDW {0}/{1} (Total/To be Processed) records, Data to process from Pending {2}/{3} (Total/To be Processed) records", countTotalBDW.ToString(), countBDW.ToString(), countTotalPending.ToString(), countPending.ToString());
Util.WriteLogFile(logfilename, BatchCode, msg);
BizUtil.InsertLog(BatchMonitorId, "", "", msg);
totalRecord = contractInsureList.Count;
var contractNoList = contractInsureList.Select(p => p.ContractNo).Distinct().ToList();
var insurComList = GetInsuranceCompany();
var coverageList = GetCoverageTypeList();
foreach (string contractNo in contractNoList)
{
ticketId = "";
try
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities())
{
var renewList = (from r in slmdb.kkslm_tr_renewinsurance
join l in slmdb.kkslm_tr_lead on r.slm_TicketId equals l.slm_ticketId
where r.slm_ContractNo == contractNo && l.is_Deleted == 0 &&
statusExclude.Contains(l.slm_Status) == false
select r).ToList();
if (renewList.Count > 0)
{
if (renewList.Count == 1)
{
var list = contractInsureList.Where(p => p.ContractNo == contractNo).ToList();
var renew = renewList.FirstOrDefault();
ticketId = renew.slm_TicketId;
DoProcess(slmdb, renew, list, insurComList, coverageList);
}
else
{
InsertPolicyActPending(contractInsureList, contractNo, slmdb, AppConstant.PendingType.DuplicateTicketId, "");
string ticketIds = "";
renewList.ForEach(p => { ticketIds += (ticketIds != "" ? ", " : "") + p.slm_TicketId; });
string errMessage = string.Format("ContractNo : {0}, GoToPending : มี TicketId มากกว่า 1 รายการ ({1})", contractNo, ticketIds);
Util.WriteLogFile(logfilename, BatchCode, errMessage);
BizUtil.InsertLog(BatchMonitorId, "", "", errMessage);
//Console.WriteLine("ContractNo " + contractNo + ": PENDING");
}
}
else
{
string errMsg = "";
AppConstant.PendingType pendingType;
var leads = (from r in slmdb.kkslm_tr_renewinsurance
join l in slmdb.kkslm_tr_lead on r.slm_TicketId equals l.slm_ticketId
where r.slm_ContractNo == contractNo && l.is_Deleted == 0 &&
statusExclude.Contains(l.slm_Status) == true
select l).ToList();
if (leads.Count > 0)
{
pendingType = AppConstant.PendingType.InappropriateLeadStatus;
errMsg = string.Format("ContractNo : {0}, GoToPending : สถานะของ Lead in Cancel, Reject or Success", contractNo);
}
else
{
int count = count = slmdb.kkslm_tr_prelead.Where(p => p.slm_Contract_Number == contractNo).Count();
if (count > 0)
{
pendingType = AppConstant.PendingType.ContractNoInPreleadOnly;
errMsg = string.Format("ContractNo : {0}, GoToPending : มี ContractNo ใน Prelead แต่ไม่มีใน Lead", contractNo);
}
else
{
pendingType = AppConstant.PendingType.ContractNoNotFound;
errMsg = string.Format("ContractNo : {0}, GoToPending : ไม่พบ ContractNo ในระบบ", contractNo);
}
}
InsertPolicyActPending(contractInsureList, contractNo, slmdb, pendingType, "");
Util.WriteLogFile(logfilename, BatchCode, errMsg);
BizUtil.InsertLog(BatchMonitorId, "", "", errMsg);
//Console.WriteLine("ContractNo " + contractNo + ": PENDING");
}
}
ts.Complete();
totalSuccess += contractInsureList.Count(p => p.ContractNo == contractNo);
}
}
catch (Exception ex)
{
totalFail += contractInsureList.Count(p => p.ContractNo == contractNo);
string message = "ContractNo " + contractNo + ", Error=" + (ex.InnerException != null ? ex.InnerException.Message : ex.Message);
Util.WriteLogFile(logfilename, BatchCode, message);
BizUtil.InsertLog(BatchMonitorId, ticketId, "", message);
//Console.WriteLine("ContractNo " + contractNo + ": FAIL");
}
}
ret = true;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
catch (Exception ex)
{
ret = false;
//Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, BatchCode, message);
BizUtil.InsertLog(BatchMonitorId, "", "", message);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
return(ret);
}
//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);
}
}
public bool ImportNotifyPremium(string batchCode)
{
bool ret = false;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
List <kkslm_tr_notify_premium_temp> tempList;
List <kkslm_ms_repairtype> repairTypeList;
try
{
BatchCode = batchCode;
BatchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
var comList = GetInsuranceCompany();
using (var slmdb = AppUtil.GetSlmDbEntities())
{
tempList = slmdb.kkslm_tr_notify_premium_temp.Where(p => p.slm_ImportFlag == "0").OrderBy(p => p.slm_Id).ToList();
totalRecord = tempList.Count;
repairTypeList = slmdb.kkslm_ms_repairtype.ToList();
}
int i = 0;
foreach (var itm in tempList)
{
i += 1;
Console.Write("\r{0}/{1}", i.ToString(), totalRecord.ToString());
try
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
using (var slmdb = AppUtil.GetSlmDbEntities())
{
bool result = InsertNotifyPremium(slmdb, itm, comList, repairTypeList);
if (!result)
{
BizUtil.InsertLog(BatchMonitorId, "", "", $"TempId={itm.slm_Id}, Error={ErrorMessage}");
}
var obj = slmdb.kkslm_tr_notify_premium_temp.FirstOrDefault(p => p.slm_Id == itm.slm_Id);
if (obj != null)
{
obj.slm_ImportFlag = result ? "1" : "2";
obj.slm_ImportDate = DateTime.Now;
slmdb.SaveChanges();
}
ts.Complete();
totalSuccess += 1;
}
}
}
catch (Exception ex)
{
totalFail += 1;
string message = $"TempId={itm.slm_Id.ToString()}, Error={(ex.InnerException != null ? ex.InnerException.Message : ex.Message)}";
Util.WriteLogFile(logfilename, BatchCode, message);
BizUtil.InsertLog(BatchMonitorId, "", "", message);
}
}
Console.WriteLine("");
ret = true;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
}
catch (Exception ex)
{
ret = false;
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, BatchCode, message);
BizUtil.InsertLog(BatchMonitorId, "", "", message);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(BatchCode, BatchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
finally
{
DeleteTempNotifyPremium();
}
return(ret);
}
// public void InsertTelesalesOwnertoHP(string batchCode)
// {
// //year ที่ insert ลง HP เป็น พ.ศ. (confirmed)
// //year, month คือ ปี เดือน ที่ update ข้อมูลไป HP
// //รอเทสที่ SIT2
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// SQLServerDB hpdb = null;
// SQLServerDB slmdb = null;
// string sql = "";
// string year = "";
// string month = "";
// string processState = "";
// try
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.CheckPrerequisite(batchCode);
// List<UpdateTelesalesOwnerData> list = GetTelesalesOwner();
// totalRecord = list.Count;
// if (list.Count > 0)
// {
// hpdb = new SQLServerDB(AppConstant.HPConnectionString);
// slmdb = new SQLServerDB(AppConstant.SLMDBConnectionString);
// year = (DateTime.Now.Year + 543).ToString();
// month = DateTime.Now.Month.ToString("00");
// foreach (UpdateTelesalesOwnerData data in list)
// {
// try
// {
// //Insert to HP
// processState = "HP";
// sql = @"INSERT INTO Import_MKT_Data_OBT(year, month, contract_no, mkt_code)
// VALUES('" + year + "','" + month + "','" + data.ContractNo + "','" + data.EmpCode + "')";
// hpdb.ExceuteNonQuery(sql);
// //Update Flag at SLM
// processState = "SLM";
// sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead
// SET slm_ObtPro03Flag = 1
// WHERE slm_Prelead_Id = '" + data.PreleadId.ToString() + "'";
// slmdb.ExceuteNonQuery(sql);
// totalSuccess += 1;
// Console.WriteLine("ContractNo " + data.ContractNo + ": SUCCESS");
// }
// catch (Exception ex)
// {
// totalFail += 1;
// string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
// string errorDetail = "";
// if (processState == "HP")
// errorDetail = "Cannot Update Telesales Owner to HP, ContractNo=" + data.ContractNo + ", Error=" + message;
// else if (processState == "SLM")
// errorDetail = "Cannot Update slm_ObtPro03Flag=true (kkslm_tr_prelead) to SLM, ContractNo=" + data.ContractNo + ", Error=" + message;
// else
// errorDetail = "ContractNo=" + data.ContractNo + ", Error=" + message;
// BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// Console.WriteLine("ContractNo " + data.ContractNo + ": 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);
// throw ex;
// }
// finally
// {
// if (hpdb != null)
// hpdb.CloseConnection();
// if (slmdb != null)
// slmdb.CloseConnection();
// }
// }
#endregion
/// <summary>
/// Update ข้อมูล Telesales Owner จาก OBT ไปที่ HP
/// </summary>
/// <param name="batchCode"></param>
public void InsertTelesalesOwnertoHP(string batchCode)
{
//year ที่ insert ลง HP เป็น พ.ศ. (confirmed)
//year, month คือ ปี เดือน ที่ update ข้อมูลไป HP
//รอเทสที่ SIT2
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
SQLServerDB hpdb = null;
SQLServerDB slmdb = null;
string sql = "";
string year = "";
string month = "";
string processState = "";
bool successFlag = true;
List <TempDataPro03> tempList = new List <TempDataPro03>();
try
{
batchMonitorId = BizUtil.SetStartTime(batchCode);
BizUtil.CheckPrerequisite(batchCode);
List <UpdateTelesalesOwnerData> list = GetTelesalesOwner();
totalRecord = list.Count;
if (list.Count > 0)
{
hpdb = new SQLServerDB(AppConstant.HPConnectionString);
slmdb = new SQLServerDB(SLMDBConnectionString);
year = (DateTime.Now.Year + 543).ToString();
month = DateTime.Now.Month.ToString("00");
foreach (UpdateTelesalesOwnerData data in list)
{
try
{
//Insert to HP
processState = "HP";
//เก็บไว้ในกรณีมี row fail ให้นำค่าใน list ไปลบออกจากเบส HP
tempList.Add(new TempDataPro03()
{
Year = year, Month = month, ContractNo = data.ContractNo, EmpCode = data.EmpCode, PreleadId = data.PreleadId
});
sql = @"INSERT INTO Import_MKT_Data_OBT(year, month, contract_no, mkt_code)
VALUES('" + year + "','" + month + "','" + data.ContractNo + "','" + data.EmpCode + "')";
hpdb.ExecuteNonQuery(sql);
//Update Flag at SLM
processState = "SLM";
sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead
SET slm_ObtPro03Flag = 1, slm_ObtPro03FlagDate = GETDATE()
WHERE slm_Prelead_Id = '" + data.PreleadId.ToString() + "'";
slmdb.ExecuteNonQuery(sql);
totalSuccess += 1;
Console.WriteLine("ContractNo " + data.ContractNo + ": SUCCESS");
}
catch (Exception ex)
{
successFlag = false;
RollbackHP(batchMonitorId, batchCode, tempList, hpdb);
RollBackPrelead(batchMonitorId, batchCode, tempList, slmdb);
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = "";
if (processState == "HP")
{
errorDetail = "Cannot Update Telesales Owner to HP, ContractNo=" + data.ContractNo + ", Error=" + message;
}
else if (processState == "SLM")
{
errorDetail = "Cannot Update slm_ObtPro03Flag=true (kkslm_tr_prelead) to SLM, ContractNo=" + data.ContractNo + ", Error=" + message;
}
else
{
errorDetail = "ContractNo=" + data.ContractNo + ", Error=" + message;
}
BizUtil.InsertLog(batchMonitorId, "", "", errorDetail);
Util.WriteLogFile(logfilename, batchCode, errorDetail);
successFlag = false;
totalSuccess = 0;
totalFail = totalRecord;
break;
//Console.WriteLine("ContractNo " + data.ContractNo + ": FAIL");
}
}
}
BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), 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);
totalSuccess = 0;
totalFail = totalRecord;
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
}
finally
{
if (hpdb != null)
{
hpdb.CloseConnection();
}
if (slmdb != null)
{
slmdb.CloseConnection();
}
}
}