//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 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 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);
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);
}
}
//Added by Pom 20/05/2016
public static bool CreateActivityLog(CARServiceData data, Int64 batchMonitorId, string batchCode, string ticketId, string logfilename)
{
string errMsg = "";
try
{
SLMBatch.WebService.CARServiceProxy.LogServiceHeader header = new SLMBatch.WebService.CARServiceProxy.LogServiceHeader()
{
ReferenceNo = data.ReferenceNo,
SecurityKey = data.SecurityKey,
ServiceName = AppConstant.CARLogService.CARCreateActivityLogServiceName,
SystemCode = data.SystemCode,
TransactionDateTime = data.TransactionDateTime
};
data.ServiceName = AppConstant.CARLogService.CARCreateActivityLogServiceName;
//Activity Info
List <SLMBatch.WebService.CARServiceProxy.DataItem> actInfoList = new List <SLMBatch.WebService.CARServiceProxy.DataItem>();
foreach (CARService.DataItem info in data.ActivityInfoList)
{
actInfoList.Add(new SLMBatch.WebService.CARServiceProxy.DataItem()
{
SeqNo = info.SeqNo, DataLabel = info.DataLabel, DataValue = info.DataValue
});
}
//Customer Info
List <SLMBatch.WebService.CARServiceProxy.DataItem> cusInfoList = new List <SLMBatch.WebService.CARServiceProxy.DataItem>();
foreach (CARService.DataItem info in data.CustomerInfoList)
{
cusInfoList.Add(new SLMBatch.WebService.CARServiceProxy.DataItem()
{
SeqNo = info.SeqNo, DataLabel = info.DataLabel, DataValue = info.DataValue
});
}
//Product Info
List <SLMBatch.WebService.CARServiceProxy.DataItem> prodInfoList = new List <SLMBatch.WebService.CARServiceProxy.DataItem>();
foreach (CARService.DataItem info in data.ProductInfoList)
{
prodInfoList.Add(new SLMBatch.WebService.CARServiceProxy.DataItem()
{
SeqNo = info.SeqNo, DataLabel = info.DataLabel, DataValue = info.DataValue
});
}
//Contact Info
List <SLMBatch.WebService.CARServiceProxy.DataItem> contInfoList = new List <SLMBatch.WebService.CARServiceProxy.DataItem>();
foreach (CARService.DataItem info in data.ContractInfoList)
{
contInfoList.Add(new SLMBatch.WebService.CARServiceProxy.DataItem()
{
SeqNo = info.SeqNo, DataLabel = info.DataLabel, DataValue = info.DataValue
});
}
//Officer Info
List <SLMBatch.WebService.CARServiceProxy.DataItem> offInfoList = new List <SLMBatch.WebService.CARServiceProxy.DataItem>();
foreach (CARService.DataItem info in data.OfficerInfoList)
{
offInfoList.Add(new SLMBatch.WebService.CARServiceProxy.DataItem()
{
SeqNo = info.SeqNo, DataLabel = info.DataLabel, DataValue = info.DataValue
});
}
SLMBatch.WebService.CARServiceProxy.CreateActivityLogData logdata = new SLMBatch.WebService.CARServiceProxy.CreateActivityLogData()
{
ActivityInfoList = actInfoList.ToArray(),
CustomerInfoList = cusInfoList.ToArray(),
ProductInfoList = prodInfoList.ToArray(),
ContractInfoList = contInfoList.ToArray(),
OfficerInfoList = offInfoList.ToArray(),
ActivityDateTime = DateTime.Now,
CampaignID = data.CampaignId,
ChannelID = data.ChannelId,
LeadID = data.PreleadId,
ProductGroupID = data.ProductGroupId,
ProductID = data.ProductId,
Status = data.Status,
SubStatus = data.SubStatus,
TicketID = data.TicketId,
SubscriptionTypeID = data.SubscriptionTypeId,
SubscriptionID = data.SubscriptionId,
TypeID = data.TypeId,
AreaID = data.AreaId,
SubAreaID = data.SubAreaId,
ActivityTypeID = data.ActivityTypeId,
ContractID = data.ContractNo
};
SLMBatch.WebService.CARServiceProxy.CASLogServiceSoapClient service = new SLMBatch.WebService.CARServiceProxy.CASLogServiceSoapClient();
service.InnerChannel.OperationTimeout = new TimeSpan(0, 0, AppConstant.CARLogService.CARTimeout);
var result = service.CreateActivityLog(header, logdata);
if (result.ResponseStatus.ResponseCode == "CAS-I-000")
{
return(true);
}
else
{
string message = "Call CAR WS Failure " + result.ResponseStatus.ResponseCode + ": " + result.ResponseStatus.ResponseMessage;
BizUtil.InsertLog(batchMonitorId, ticketId, "", message);
Util.WriteLogFile(logfilename, batchCode, message);
if (!AddCARLog(LogType.Error, result.ResponseStatus.ResponseCode, result.ResponseStatus.ResponseMessage, data, out errMsg))
{
BizUtil.InsertLog(batchMonitorId, ticketId, "", "Insert kkslm_tr_cas_resend failed: " + errMsg);
Util.WriteLogFile(logfilename, batchCode, "Insert kkslm_tr_cas_resend failed: " + errMsg);
}
return(false);
//AddCARBatch(data, data.SystemCode, out errMsg, batchMonitorId, batchCode, ticketId);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
message = "Call CAR WS Failure = " + message;
BizUtil.InsertLog(batchMonitorId, ticketId, "", message);
Util.WriteLogFile(logfilename, batchCode, message);
if (!AddCARLog(LogType.Fail, "", message, data, out errMsg))
{
BizUtil.InsertLog(batchMonitorId, ticketId, "", "Insert kkslm_tr_cas_resend failed: " + errMsg);
Util.WriteLogFile(logfilename, batchCode, "Insert kkslm_tr_cas_resend failed: " + errMsg);
}
return(false);
//AddCARBatch(data, data.SystemCode, out errMsg, batchMonitorId, batchCode, ticketId);
}
}
public bool UpdateBatchCARInsertStatus(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
bool ret = false;
try {
ErrorStep = "Set start time";
_logger.Info("I:-- Start Batch --:--UpdateBatchCARInsertStatus--" + batchCode);
batchMonitorId = BizUtil.SetStartTime(batchCode);
ErrorStep = "GetFileList";
_logger.Info(_logMsg.Clear().Add("GetFileList", batchCode).ToInputLogString());
//1. ดึง List ของไฟล์ที่มีใน FTPs โดยเอาไฟล์ที่สร้างมาตั้งแต่ เมื่อวาน วันนี้ และวันพรุ่งนี้ โดยดูจาก FileName
IEnumerable <string> files = GetFileList();
if (files.Count <string>() > 0)
{
ret = true;
ErrorStep = "Validate Text History " + files.Count <string>().ToString() + " file(s)";
//2. เก็บชื่อไฟล์ลง DB เพื่อเตรียมทำการ Process กรณีมีไฟล์อยู่แล้ว ก็ไม่ต้องทำซ้ำ
List <kkslm_ext_sys_status_cbs_file> fileList = new List <kkslm_ext_sys_status_cbs_file>();
foreach (string f in files)
{
FileInfo fInfo = new FileInfo(f);
ErrorStep = "Validate Text History " + fInfo.Name;
_logger.Info(_logMsg.Clear().Add("Validate Text History", fInfo.Name).ToInputLogString());
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
var file = checkFileHistory(fInfo.Name, slmdb);
if (file != null)
{
file.kkslm_filename = fInfo.Name;
file.kkslm_filepath = f;
file.kkslm_file_created_date = fInfo.CreationTime;
file.kkslm_file_process_time = DateTime.Now;
file.kkslm_process_status = AppConstant.InProcess;
if (file.kkslm_ext_sys_status_cbs_file_id == 0)
{
slmdb.kkslm_ext_sys_status_cbs_file.AddObject(file);
_logger.Info(_logMsg.Clear().Add("Process New File", fInfo.Name).ToInputLogString());
}
slmdb.SaveChanges();
fileList.Add(file);
}
}
foreach (kkslm_ext_sys_status_cbs_file file in fileList)
{
//3. เช็ค Format TextFile ทุก Record
ErrorStep = "Validate Text Format " + file.kkslm_filename;
_logger.Info(_logMsg.Clear().Add("Validate Text Format", file.kkslm_filename).ToInputLogString());
ValidateTextfileData validTextFormat = ValidateTextFileFormat(file.kkslm_filepath);
if (validTextFormat.IsValid == false)
{
ErrorStep = "Invalid Textfile " + file.kkslm_filename;
_logger.Error(_logMsg.Clear().Add("Invalid Textfile", file.kkslm_filename).Add("ErrorMessage", validTextFormat.ErrorMessage).ToInputLogString());
UpdateFileStatus(file.kkslm_ext_sys_status_cbs_file_id, AppConstant.Fail, "Invalid Textfile " + file.kkslm_filename + Environment.NewLine + validTextFormat.ErrorMessage);
break;
}
//4. Process Text File ตาม Requirement
ErrorStep = "GetEBatchCARInsertStatusList " + file.kkslm_filename;
_logger.Info(_logMsg.Clear().Add("GetEBatchCARInsertStatusList", "").Add("FileName", file.kkslm_filename).ToInputLogString());
var lists = GetEBatchCARInsertStatusList(file.kkslm_filepath, file.kkslm_ext_sys_status_cbs_file_id);
if (lists != null)
{
bool isSuccess = true;
totalRecord = lists.Count;
foreach (BatchCARInsertStatusData data in lists)
{
try
{
SLMDBEntities slmdb = new SLMDBEntities();
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
}))
{
ErrorStep = "Find TicketId = " + data.RefSystemId;
var lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == data.RefSystemId && p.is_Deleted == 0).FirstOrDefault();
if (lead == null)
{
_logger.ErrorFormat("Ticket Id {0} not found in SLM", data.RefSystemId);
throw new Exception("Ticket Id " + data.RefSystemId + " not found in SLM");
}
ErrorStep = "CheckStatus TicketId = " + data.RefSystemId;
_logger.Info(_logMsg.Clear().Add("Check Ticket Status", " Ticket Id:" + data.RefSystemId));
CheckStatus(slmdb, lead, data, batchCode);
ErrorStep = "CheckPhoneCallHistory TicketId = " + data.RefSystemId;
_logger.Info(_logMsg.Clear().Add("Check Phone Call History", " Ticket Id:" + data.RefSystemId));
CheckPhoneCallHistory(slmdb, lead, data);
slmdb.SaveChanges();
totalSuccess += 1;
_logger.Info(_logMsg.Clear().Add("TicketId", data.RefSystemId + ": SUCCESS").ToInputLogString());
ts.Complete();
}
}
catch (Exception ex)
{
totalFail += 1;
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
string errorDetail = "TicketId=" + data.RefSystemId + ", Error=" + message;
BizUtil.InsertLog(batchMonitorId, data.RefSystemId, "", errorDetail);
_logger.Error(_logMsg.Clear().Add("TicketId", data.ReferenceNo + ": FAIL").ToInputLogString());
}
}
ErrorStep = "Set end time";
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
//5. นำข้อมูลใน Text File เก็บลงใน Archives
int dataRow = 0;
ErrorStep = "InsertArchiveData";
_logger.Info(_logMsg.Clear().Add("InsertArchiveData", "TABLE : kkslm_ext_sys_status_cbs_file_data").ToInputLogString());
foreach (BatchCARInsertStatusData list in lists)
{
SLMDBEntities slmdb = new SLMDBEntities();
kkslm_ext_sys_status_cbs_file_data data = new kkslm_ext_sys_status_cbs_file_data
{
kkslm_ext_sys_status_cbs_file_id = file.kkslm_ext_sys_status_cbs_file_id,
kkslm_reference_code = list.HeaderData.ReferenceCode,
kkslm_file_name = list.HeaderData.FileName,
kkslm_create_date = list.HeaderData.CreateDate,
kkslm_current_sequence = Convert.ToInt16(list.HeaderData.CurrentSequence),
kkslm_total_sequence = Convert.ToInt16(list.HeaderData.TotalSequence),
kkslm_total_record = Convert.ToInt16(list.HeaderData.TotalRecord),
kkslm_system_code = list.HeaderData.SystemCode,
kkslm_reference_no = list.ReferenceNo,
kkslm_channel_id = list.ChannelID,
kkslm_status_date_time = list.StatusDateTime,
kkslm_subscription_id = list.SubscriptionID,
kkslm_subscription_cus_type = list.SubscriptionCusType,
kkslm_subscription_card_type = list.SubscriptionCardType,
kkslm_owner_system_id = list.OwnerSystemId,
kkslm_owner_system_code = list.OwnerSystemCode,
kkslm_ref_system_id = list.RefSystemId,
kkslm_ref_system_code = list.RefSystemCode,
kkslm_status = list.Status,
kkslm_status_name = list.StatusName
};
dataRow += 1;
slmdb.kkslm_ext_sys_status_cbs_file_data.AddObject(data);
isSuccess = (slmdb.SaveChanges() > 0);
if (isSuccess == false)
{
ErrorStep = "Error InsertArchiveData at row " + dataRow.ToString();
break;
}
}
if (isSuccess == true)
{
ErrorStep = "MoveBatchFileToArchive";
_logger.Info(_logMsg.Clear().Add("MoveBatchFileToArchive", "Filename:" + file.kkslm_filename));
if (MoveBatchFileToArchive(file.kkslm_filepath) == true)
{
ErrorStep = "UpdateSuccessStatus Filename:" + file.kkslm_filename;
_logger.Info(_logMsg.Clear().Add("UpdateSuccessStatus", "Filename:" + file.kkslm_filename));
UpdateFileStatus(file.kkslm_ext_sys_status_cbs_file_id, AppConstant.Success, "");
}
}
}
}
ErrorStep = "Send Mail";
SendMail(totalRecord);
}
else
{
ret = false;
}
}
catch (Exception ex) {
_logger.Error("Exception occur:\n", ex);
ret = false;
}
return(ret);
}
public void UpdateStatusBackEnd(string batchCode)
{
Int64 batchMonitorId = 0;
int totalRecord = 0;
int totalSuccess = 0;
int totalFail = 0;
DataTable dtSuccess = new DataTable("Success");
DataTable dtFail = new DataTable("Fail");
dtSuccess.Columns.AddRange(new DataColumn[] {
new DataColumn("TicketID", typeof(string)),
new DataColumn("Name", typeof(string)),
new DataColumn("Surname", typeof(string)),
new DataColumn("Channel", typeof(string)),
new DataColumn("Campaign", typeof(string)),
new DataColumn("Detail", typeof(string)),
new DataColumn("Status", typeof(string)),
new DataColumn("SubStatus", typeof(string)),
new DataColumn("UpdatedDate", typeof(DateTime)),
new DataColumn("System", typeof(string))
});
dtFail.Columns.AddRange(new DataColumn[] {
new DataColumn("TicketID", typeof(string)),
new DataColumn("Status", typeof(string)),
new DataColumn("Reason", typeof(string)),
new DataColumn("UpdatedDate", typeof(DateTime)),
new DataColumn("System", typeof(string))
});
try
{
ErrorStep = "Set Start Time";
batchMonitorId = BizUtil.SetStartTime(batchCode);
ErrorStep = "GetExtSystemCurrentStatusViewList";
var list = GetExtSystemCurrentStatusViewList();
totalRecord = list.Count;
foreach (ExtSystemCurrentStatusData data in list)
{
try
{
SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
ErrorStep = "Find TicketId = " + data.TicketId;
var lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == data.TicketId && p.is_Deleted == 0).FirstOrDefault();
if (lead == null)
{
throw new Exception("Ticket Id " + data.TicketId + " not found in SLM");
}
ErrorStep = "CheckStatus TicketId = " + data.TicketId;
CheckStatus(slmdb, lead, data, batchCode);
ErrorStep = "CheckPhoneCallHistory TicketId = " + data.TicketId;
CheckPhoneCallHistory(slmdb, lead, data);
slmdb.SaveChanges();
dtSuccess.Rows.Add("'" + data.TicketId, data.Name, data.LastName, data.Channel, data.Campaign, data.StatusDesc, data.SlmStatus, String.IsNullOrEmpty(data.SubStatusCode) ? data.StatusName : data.StatusName + " - " + data.SubStatusCode, data.StatusDate, data.StatusSystem);
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;
dtFail.Rows.Add("'" + data.TicketId, "Update Fail", "\"" + message + "\"", data.StatusDate, data.StatusSystem);
BizUtil.InsertLog(batchMonitorId, data.TicketId, "", errorDetail);
Util.WriteLogFile(logfilename, batchCode, errorDetail);
Console.WriteLine("TicketId " + data.TicketId + ": FAIL");
}
}
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
ErrorStep = "Export CSV Success";
if (dtSuccess.Rows.Count > 0)
{
SaveCSVToSharePath(GenerateString(dtSuccess), "SLM-LotusNotes_Report_Success_" + String.Format("{0:yyyyMMdd }", DateTime.Now));
}
if (dtFail.Rows.Count > 0)
{
SaveCSVToSharePath(GenerateString(dtFail), "SLM-LotusNotes_Report_Fail_" + String.Format("{0:yyyyMMdd }", DateTime.Now));
}
TotalExportExcel = totalSuccess + totalFail;
ErrorStep = "Send Mail";
SendMail();
dtSuccess.Dispose();
dtFail.Dispose();
}
catch (Exception ex)
{
Console.WriteLine("All FAIL");
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
dtFail.Rows.Add("#", "Fail", "Fail", DateTime.Now);
Util.WriteLogFile(logfilename, batchCode, message);
BizUtil.InsertLog(batchMonitorId, "", "", message);
BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
dtSuccess.Dispose();
dtFail.Dispose();
}
}
private void CreateCASActivityLog(kkslm_tr_renewinsurance reins, SLMDBEntities slmdb, string batchCode, Int64 batchMonitorId, int phonecall_id, LeadDataForCARLogService data)
{
try
{
string policyStartCoverDate = "";
string policyEndCoverDate = "";
string actStartCoverDate = "";
string actEndCoverDate = "";
string preleadId = "";
if (data != null)
{
preleadId = data.PreleadId != null?data.PreleadId.Value.ToString() : "";
if (data.PolicyStartCoverDate != null)
{
policyStartCoverDate = data.PolicyStartCoverDate.Value.ToString("dd-MM-") + data.PolicyStartCoverDate.Value.Year.ToString();
}
if (data.PolicyEndCoverDate != null)
{
policyEndCoverDate = data.PolicyEndCoverDate.Value.ToString("dd-MM-") + data.PolicyEndCoverDate.Value.Year.ToString();
}
if (data.ActStartCoverDate != null)
{
actStartCoverDate = data.ActStartCoverDate.Value.ToString("dd-MM-") + data.ActStartCoverDate.Value.Year.ToString();
}
if (data.ActEndCoverDate != null)
{
actEndCoverDate = data.ActEndCoverDate.Value.ToString("dd-MM-") + data.ActEndCoverDate.Value.Year.ToString();
}
}
//Activity Info
List <CARService.DataItem> actInfoList = new List <CARService.DataItem>();
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 1, DataLabel = "เลขที่สัญญา", DataValue = data != null ? data.ContractNo : ""
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 2, DataLabel = "บริษัทประกันภัยรถยนต์", DataValue = data != null ? data.InsuranceCompany : ""
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 3, DataLabel = "เลขเล่มกรมธรรม์", DataValue = data != null ? data.PolicyNo : ""
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 4, DataLabel = "เลขลงทะเบียน", DataValue = ""
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 5, DataLabel = "วันที่คุ้มครอง", DataValue = policyStartCoverDate
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 6, DataLabel = "วันที่สิ้นสุดกรมธรรม์", DataValue = policyEndCoverDate
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 7, DataLabel = "เลขที่พรบ.", DataValue = data != null ? data.ActNo : ""
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 8, DataLabel = "วันที่เริ่มต้นพรบ.", DataValue = actStartCoverDate
});
actInfoList.Add(new CARService.DataItem()
{
SeqNo = 9, DataLabel = "วันที่สิ้นสุดพรบ.", DataValue = actEndCoverDate
});
//Customer Info
List <CARService.DataItem> cusInfoList = new List <CARService.DataItem>();
cusInfoList.Add(new CARService.DataItem()
{
SeqNo = 1, DataLabel = "Subscription ID", DataValue = data != null ? data.CitizenId : ""
});
cusInfoList.Add(new CARService.DataItem()
{
SeqNo = 2, DataLabel = "Subscription Type", DataValue = data != null ? data.CardTypeName : ""
});
cusInfoList.Add(new CARService.DataItem()
{
SeqNo = 3, DataLabel = "ชื่อ-นามสกุลของลูกค้า", DataValue = data != null ? data.CustomerName : ""
});
//Product Info
List <CARService.DataItem> prodInfoList = new List <CARService.DataItem>();
prodInfoList.Add(new CARService.DataItem()
{
SeqNo = 1, DataLabel = "Product Group", DataValue = data != null ? data.ProductGroupName : ""
});
prodInfoList.Add(new CARService.DataItem()
{
SeqNo = 2, DataLabel = "Product", DataValue = data != null ? data.ProductName : ""
});
prodInfoList.Add(new CARService.DataItem()
{
SeqNo = 3, DataLabel = "Campaign", DataValue = data != null ? data.CampaignName : ""
});
//Contract Info
List <CARService.DataItem> contInfoList = new List <CARService.DataItem>();
contInfoList.Add(new CARService.DataItem()
{
SeqNo = 1, DataLabel = "เลขที่สัญญา", DataValue = data != null ? data.ContractNo : ""
});
contInfoList.Add(new CARService.DataItem()
{
SeqNo = 2, DataLabel = "ระบบที่บันทึกสัญญา", DataValue = preleadId != "" ? "HP" : ""
});
contInfoList.Add(new CARService.DataItem()
{
SeqNo = 3, DataLabel = "ทะเบียนรถ", DataValue = data != null ? data.LicenseNo : ""
});
//Officer Info
List <CARService.DataItem> offInfoList = new List <CARService.DataItem>();
offInfoList.Add(new CARService.DataItem()
{
SeqNo = 1, DataLabel = "Officer", DataValue = "SYSTEM"
});
CARService.CARServiceData logdata = new CARService.CARServiceData()
{
ReferenceNo = reins.slm_RenewInsureId.ToString(),
SecurityKey = preleadId != "" ? AppConstant.CARLogService.OBTSecurityKey : AppConstant.CARLogService.SLMSecurityKey,
ServiceName = "CreateActivityLog",
SystemCode = preleadId != "" ? AppConstant.CARLogService.CARLoginOBT : AppConstant.CARLogService.CARLoginSLM, //ถ้ามี preleadid ให้ถือว่าเป็น OBT ทั้งหมด ถึงจะมี TicketId ก็ตาม
TransactionDateTime = DateTime.Now,
ActivityInfoList = actInfoList,
CustomerInfoList = cusInfoList,
ProductInfoList = prodInfoList,
ContractInfoList = contInfoList,
OfficerInfoList = offInfoList,
ActivityDateTime = DateTime.Now,
CampaignId = data != null ? data.CampaignId : "",
ChannelId = data != null ? data.ChannelId : "",
PreleadId = preleadId,
ProductGroupId = data != null ? data.ProductGroupId : "",
ProductId = data != null ? data.ProductId : "",
Status = data != null ? data.StatusName : "",
SubStatus = data != null ? data.SubStatusName : "",
TicketId = data != null ? data.TicketId : "",
SubscriptionId = data != null ? data.CitizenId : "",
TypeId = AppConstant.CARLogService.Data.TypeId,
AreaId = AppConstant.CARLogService.Data.AreaId,
SubAreaId = AppConstant.CARLogService.Data.SubAreaId,
ActivityTypeId = AppConstant.CARLogService.Data.ActivityType.TodoId,
ContractNo = data != null ? data.ContractNo : ""
};
if (data != null && !string.IsNullOrEmpty(data.SubScriptionTypeId))
{
logdata.SubscriptionTypeId = data.SubScriptionTypeId;
}
bool ret = CARService.CreateActivityLog(logdata, batchMonitorId, batchCode, reins.slm_TicketId, logfilename);
AppUtil.UpdatePhonecallCASFlag(slmdb, phonecall_id, ret ? "1" : "2");
}
catch (Exception ex)
{
AppUtil.UpdatePhonecallCASFlag(slmdb, phonecall_id, "2");
//Error ให้ลง Log ไว้ ไม่ต้อง Throw
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
BizUtil.InsertLog(batchMonitorId, reins.slm_TicketId, "", message);
Util.WriteLogFile(logfilename, batchCode, message);
}
}
protected bool ExportExcel(string batchCode)
{
bool ret;
Util.WriteLogFile(logfilename, batchCode, _excelTemplateName);
try
{
_batchMonitorId = BizUtil.SetStartTime(batchCode);
_errorStep = $"Load {_exportModuleName} data";
//Console.WriteLine(_errorStep);
DataTable dt = DataAccess.LoadData();
_totalExportExcel = dt.Rows.Count;
if (_totalExportExcel > 0)
{
SaveExcelToSharePath(dt);
}
else
{
//Console.WriteLine($"{_exportModuleName} data not found.");
}
}
catch (Exception ex)
{
_errorDetail = ex.ToString();
}
if (_errorDetail.Trim() != string.Empty)
{
_totalExportExcel = 0;
}
else
{
DataAccess.FeedbackData();
}
BizUtil.SetEndTime(batchCode, _batchMonitorId
, (_errorDetail.Trim() == string.Empty ? AppConstant.Success : AppConstant.Fail)
, _totalExportExcel
, (_errorDetail.Trim() == string.Empty ? _totalExportExcel : 0)
, (_errorDetail.Trim() == string.Empty ? 0 : _totalExportExcel));
if (_errorDetail.Trim() != string.Empty)
{
Util.WriteLogFile(logfilename, batchCode, _errorDetail);
BizUtil.InsertLog(_batchMonitorId, "", "", _errorDetail);
ret = false;
}
else
{
ret = true;
}
try
{
//Console.WriteLine("Send Mail");
SendMail();
}
catch (Exception ex)
{
//Console.WriteLine(ex.ToString());
Util.WriteLogFile(logfilename, batchCode, ex.ToString());
BizUtil.InsertLog(_batchMonitorId, "", "", ex.ToString());
}
return(ret);
}
/// <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);
}
private void DoProcess(SLMDBEntities slmdb, kkslm_tr_renewinsurance reins, List <ContractInsuranceData> contractInsureList, List <InsuranceCompanyData> insurComList, List <CoverageTypeData> coverageList)
{
try
{
string errMessage = "";
bool doUpdatePolicy = false;
bool doUpdateAct = false;
DateTime createdDate = DateTime.Now;
var lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == reins.slm_TicketId).FirstOrDefault();
if (lead == null)
{
throw new Exception("ไม่พบข้อมูล Lead TicketId " + reins.slm_TicketId + " ในระบบ");
}
int countPolicy = slmdb.kkslm_tr_renewinsurance_compare.Count(p => p.slm_RenewInsureId == reins.slm_RenewInsureId && p.slm_Selected == true);
int countAct = slmdb.kkslm_tr_renewinsurance_compare_act.Count(p => p.slm_RenewInsureId == reins.slm_RenewInsureId && p.slm_ActPurchaseFlag == true);
ContractInsuranceData data = null;
if (countPolicy > 0)
{
if (string.IsNullOrWhiteSpace(reins.slm_ReceiveNo))
{
InsertPolicyActPending(contractInsureList, reins.slm_ContractNo, slmdb, AppConstant.PendingType.ReceiveNoNotFound, "");
errMessage = string.Format("ContractNo : {0}, TicketId : {1}, GoToPending, Error : ไม่พบข้อมูลเลขที่รับแจ้ง", reins.slm_ContractNo, reins.slm_TicketId);
Util.WriteLogFile(logfilename, BatchCode, errMessage);
BizUtil.InsertLog(BatchMonitorId, lead.slm_ticketId, "", errMessage);
//Console.WriteLine("ContractNo " + reins.slm_ContractNo + ": PENDING");
return;
}
data = contractInsureList.Where(p => p.PolicyType == "V").FirstOrDefault();
if (data != null)
{
if (string.IsNullOrWhiteSpace(reins.slm_PolicyNo))
{
doUpdatePolicy = true;
}
else
{
if (reins.slm_PolicyNo.Trim().ToLower() != data.PolicyNo.ToLower())
{
doUpdatePolicy = true;
}
}
if (doUpdatePolicy)
{
reins.slm_PolicyNo = data.PolicyNo;
UpdateStatusAndOwnerLogging(slmdb, lead, createdDate, "10", "30", AppConstant.LoggingType.EODUpdateCurrent);
UpdatePending(slmdb, reins.slm_ContractNo, "V", createdDate);
}
}
data = contractInsureList.Where(p => p.PolicyType == "C").FirstOrDefault();
if (data != null)
{
if (countAct > 0)
{
if (string.IsNullOrWhiteSpace(reins.slm_ActNo))
{
doUpdateAct = true;
}
else
{
if (reins.slm_ActNo.Trim().ToLower() != data.PolicyNo.ToLower())
{
doUpdateAct = true;
}
}
if (doUpdateAct)
{
reins.slm_ActNo = data.PolicyNo;
UpdatePending(slmdb, reins.slm_ContractNo, "C", createdDate);
}
}
else
{
InsertPolicyActPending(contractInsureList, reins.slm_ContractNo, slmdb, AppConstant.PendingType.PurchaseDetailNotFound, "C");
errMessage = string.Format("ContractNo : {0}, TicketId : {1}, GoToPending, Error : ไม่พบข้อมูลการซื้อพรบ. แต่มีเลขพรบ.เข้าระบบ", reins.slm_ContractNo, reins.slm_TicketId);
Util.WriteLogFile(logfilename, BatchCode, errMessage);
BizUtil.InsertLog(BatchMonitorId, lead.slm_ticketId, "", errMessage);
//Console.WriteLine("ContractNo " + reins.slm_ContractNo + ": PENDING");
}
}
}
else if (countPolicy == 0 && countAct > 0)
{
data = contractInsureList.Where(p => p.PolicyType == "C").FirstOrDefault();
if (data != null)
{
if (string.IsNullOrWhiteSpace(reins.slm_ActNo))
{
doUpdateAct = true;
}
else
{
if (reins.slm_ActNo.Trim().ToLower() != data.PolicyNo.ToLower())
{
doUpdateAct = true;
}
}
if (doUpdateAct)
{
reins.slm_ActNo = data.PolicyNo;
UpdateStatusAndOwnerLogging(slmdb, lead, createdDate, "10", "31", AppConstant.LoggingType.EODUpdateCurrent);
UpdatePending(slmdb, reins.slm_ContractNo, "C", createdDate);
}
}
data = contractInsureList.Where(p => p.PolicyType == "V").FirstOrDefault();
if (data != null)
{
InsertPolicyActPending(contractInsureList, reins.slm_ContractNo, slmdb, AppConstant.PendingType.PurchaseDetailNotFound, "V");
errMessage = string.Format("ContractNo : {0}, TicketId : {1}, GoToPending, Error : ไม่พบข้อมูลการซื้อประกัน แต่มีเลขกรมธรรม์เข้าระบบ", reins.slm_ContractNo, reins.slm_TicketId);
Util.WriteLogFile(logfilename, BatchCode, errMessage);
BizUtil.InsertLog(BatchMonitorId, lead.slm_ticketId, "", errMessage);
//Console.WriteLine("ContractNo " + reins.slm_ContractNo + ": PENDING");
}
}
else
{
InsertPolicyActPending(contractInsureList, reins.slm_ContractNo, slmdb, AppConstant.PendingType.PurchaseDetailNotFound, "");
Util.WriteLogFile(logfilename, BatchCode, string.Format("ContractNo : {0}, Error : ไม่พบข้อมูลการซื้อขายประกันและพรบ.", reins.slm_ContractNo));
BizUtil.InsertLog(BatchMonitorId, lead.slm_ticketId, "", string.Format("ContractNo : {0}, Error : ไม่พบข้อมูลการซื้อขายประกันและพรบ.", reins.slm_ContractNo));
//Console.WriteLine("ContractNo " + reins.slm_ContractNo + ": PENDING");
return; //ถ้าตก Pending ไม่มีการซื้อประกันและพรบ. ไม่ต้องลงบันทึกผลการติดต่อและไม่ส่ง CAR
}
if (doUpdatePolicy || doUpdateAct)
{
reins.slm_UpdatedBy = "SYSTEM";
reins.slm_UpdatedDate = createdDate;
slmdb.SaveChanges();
var carData = AppUtil.GetDataForCARLogService(reins.slm_TicketId, slmdb);
var phonecall = InsertPhonecallHistory(slmdb, reins.slm_TicketId, createdDate, carData);
if (doUpdatePolicy && AppConstant.SendSMSPolicyNo)
{
InsertPrepareSMS(slmdb, lead, reins, insurComList, coverageList, createdDate);
}
CreateCASActivityLog(reins, slmdb, BatchCode, BatchMonitorId, phonecall.slm_PhoneCallId, carData);
}
}
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);
}
}
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();
}
}
}
