public void InsertData(LeadData leadData, string createByUsername, DateTime createDate)
{
try
{
kkslm_tr_lead lead = new kkslm_tr_lead();
lead.slm_ticketId = leadData.TicketId;
lead.slm_Name = leadData.Name;
lead.slm_LastName = leadData.LastName;
lead.slm_TelNo_1 = leadData.TelNo_1;
//lead.slm_Ext_1 = leadData.Ext_1;
lead.slm_CampaignId = leadData.CampaignId;
if (!string.IsNullOrEmpty(leadData.Owner_Branch))
{
lead.slm_Owner_Branch = leadData.Owner_Branch;
}
if (!string.IsNullOrEmpty(leadData.Owner))
{
lead.slm_Owner = leadData.Owner;
lead.slm_Owner_Position = GetPositionId(leadData.Owner, slmdb);
}
lead.slm_Delegate = leadData.Delegate;
lead.slm_Status = "00";
lead.slm_StatusBy = createByUsername;
lead.slm_StatusDate = createDate;
lead.slm_AvailableTime = leadData.AvailableTime;
if (leadData.StaffId != null)
{
lead.slm_StaffId = Convert.ToInt32("0" + leadData.StaffId);
}
if (leadData.counting != null)
{
lead.slm_Counting = Convert.ToDecimal(leadData.counting);
}
lead.slm_EmailFlag = leadData.EmailFlag;
lead.slm_SmsFlag = leadData.SmsFlag;
lead.slm_ChannelId = leadData.ChannelId;
lead.slm_CreatedBy = createByUsername;
lead.slm_CreatedBy_Position = GetPositionId(createByUsername, slmdb);
lead.slm_CreatedDate = createDate;
lead.slm_CreatedBy_Branch = leadData.CreatedBy_Branch;
lead.slm_UpdatedBy = createByUsername;
lead.slm_UpdatedDate = createDate;
lead.slm_Delegate_Flag = 0;
lead.slm_AssignedFlag = "0";
lead.slm_Product_Group_Id = leadData.ProductGroupId;
lead.slm_Product_Id = leadData.ProductId;
lead.slm_Product_Name = leadData.ProductName;
lead.slm_ContractNoRefer = leadData.ContractNoRefer;
slmdb.kkslm_tr_lead.AddObject(lead);
slmdb.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
private void CheckPhoneCallHistory(SLMDBEntities slmdb, kkslm_tr_lead lead, ExtSystemCurrentStatusData data)
{
try
{
if (!string.IsNullOrEmpty(data.StatusDesc))
{
if (data.StatusDesc.Trim().Length > 4000)
{
throw new Exception("Field kkslm_ext_StatusDesc exceeds 4000 character limitation");
}
kkslm_phone_call pc = new kkslm_phone_call()
{
slm_TicketId = lead.slm_ticketId,
slm_ContactDetail = data.StatusDesc.Trim(),
slm_CreateBy = "SYSTEM",
slm_CreatedBy_Position = null,
slm_CreateDate = DateTime.Now,
slm_Status = lead.slm_Status,
slm_Owner = lead.slm_Owner,
slm_Owner_Position = lead.slm_Owner_Position,
slm_ContactPhone = null,
is_Deleted = 0
};
slmdb.kkslm_phone_call.AddObject(pc);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertData(string ticketId, string new_ticketId, ProductData productData, string username, DateTime createdDate, string channelId)
{
try
{
var leadData = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == ticketId).FirstOrDefault();
if (leadData != null)
{
kkslm_tr_lead new_lead = new kkslm_tr_lead();
new_lead.slm_ticketId = new_ticketId;
new_lead.slm_Name = leadData.slm_Name;
new_lead.slm_LastName = leadData.slm_LastName;
new_lead.slm_TelNo_1 = leadData.slm_TelNo_1;
new_lead.slm_Status = "00";
new_lead.slm_StatusDate = createdDate;
new_lead.slm_StatusBy = username;
new_lead.slm_AvailableTime = leadData.slm_AvailableTime;
new_lead.slm_ChannelId = channelId;
new_lead.slm_CreatedBy = username;
new_lead.slm_CreatedBy_Position = GetPositionId(username, slmdb);
new_lead.slm_CreatedDate = createdDate;
new_lead.slm_UpdatedBy = username;
new_lead.slm_UpdatedDate = createdDate;
new_lead.slm_Counting = 0;
new_lead.slm_Delegate_Flag = 0;
new_lead.slm_AssignedFlag = "0";
new_lead.slm_ContractNoRefer = leadData.slm_ContractNoRefer;
//ข้อมูลใหม่
new_lead.slm_Owner = username;
new_lead.slm_Owner_Position = GetPositionId(username, slmdb);
var staff = slmdb.kkslm_ms_staff.Where(p => p.slm_UserName == new_lead.slm_Owner).FirstOrDefault();
if (staff != null)
{
new_lead.slm_StaffId = staff.slm_StaffId;
new_lead.slm_Owner_Branch = staff.slm_BranchCode;
new_lead.slm_CreatedBy_Branch = staff.slm_BranchCode;
}
new_lead.slm_CampaignId = productData.CampaignId;
new_lead.slm_Product_Group_Id = productData.ProductGroupId;
new_lead.slm_Product_Id = productData.ProductId;
new_lead.slm_Product_Name = productData.ProductName;
slmdb.kkslm_tr_lead.AddObject(new_lead);
}
else
{
throw new Exception("ไม่พบ Ticket Id " + ticketId + " ใน Table kkslm_tr_lead");
}
}
catch (Exception ex)
{
throw ex;
}
}
private string GetTelNoSMS(kkslm_tr_lead lead)
{
try
{
using (var slmdb = AppUtil.GetSlmDbEntities())
{
var telNoSms = slmdb.kkslm_tr_cusinfo.Where(p => p.slm_TicketId == lead.slm_ticketId).Select(p => p.slm_TelNoSms).FirstOrDefault();
return(string.IsNullOrWhiteSpace(telNoSms) ? lead.slm_TelNo_1 : telNoSms);
}
}
catch
{
throw;
}
}
private string InsertPrepareSMS(SLMDBEntities slmdb, kkslm_tr_renewinsurance renew, List <InsuranceCompanyData> companyList, List <kkslm_ms_coveragetype> coverageList)
{
try
{
if (string.IsNullOrEmpty(AppConstant.SMSTemplatePathReceiveNo))
{
throw new Exception($"ไม่พบ Config {nameof(AppConstant.SMSTemplatePathReceiveNo)} ใน Configuration File");
}
InsuranceCompanyData insuranceCompany = companyList.FirstOrDefault(p => p.InsComId == renew.slm_InsuranceComId);
if (insuranceCompany == null)
{
throw new Exception("ไม่พบข้อมูลบริษัทประกัน Id " + renew.slm_InsuranceComId + " ในระบบ");
}
kkslm_ms_coveragetype coverage = coverageList.FirstOrDefault(p => p.slm_CoverageTypeId == renew.slm_CoverageTypeId);
if (coverage == null)
{
throw new Exception("ไม่พบข้อมูลประเภทประกัน Id " + renew.slm_CoverageTypeId + " ในระบบ");
}
kkslm_tr_lead lead = slmdb.kkslm_tr_lead.FirstOrDefault(p => p.slm_ticketId == renew.slm_TicketId);
if (lead == null)
{
throw new Exception("ไม่พบข้อมูล Lead ในระบบ, TicketId " + renew.slm_TicketId);
}
string telNoSms = slmdb.kkslm_tr_cusinfo.Where(p => p.slm_TicketId == lead.slm_ticketId).Select(p => p.slm_TelNoSms).FirstOrDefault();
if (string.IsNullOrWhiteSpace(telNoSms))
{
telNoSms = lead.slm_TelNo_1;
}
string template = File.ReadAllText(AppConstant.SMSTemplatePathReceiveNo, Encoding.UTF8);
string smsMessage = template
.Replace("%CoverageType%", coverage.slm_ConverageTypeName?.Trim() ?? "")
.Replace("%CarLicenseNo%", renew.slm_LicenseNo?.Trim() ?? "")
.Replace("%InsuranceCompany%", insuranceCompany.InsNameTH?.Trim() ?? "")
.Replace("%ReceiveNo%", renew.slm_ReceiveNo?.Trim() ?? "")
.Replace("%InsuranceTelNo%", insuranceCompany.TelContact?.Trim() ?? "");
//kkslm_tr_prelead_prepare_sms sms = InitSMS(smsMessage, telNoSms, renew.slm_TicketId, _dbNow);
var sms = new kkslm_tr_prelead_prepare_sms
{
slm_ticketId = renew.slm_TicketId,
slm_Prelead_Id = null,
slm_Message = smsMessage,
slm_Message_CreatedBy = "SYSTEM",
slm_MessageStatusId = "1",
slm_PhoneNumber = telNoSms,
slm_QueueId = "6",
slm_RequestDate = DateTime.Now,
slm_RuleActivityId = 0,
slm_ExportStatus = "0",
slm_RefId = null,
slm_SendingStatusCode = null,
slm_SendingStatus = null,
slm_ErrorCode = null,
slm_ErrorReason = null,
slm_CAS_Flag = null,
slm_CAS_Date = null,
slm_FlagType = "1",
slm_CreatedBy = "SYSTEM",
slm_CreatedDate = DateTime.Now,
slm_UpdatedBy = "SYSTEM",
slm_UpdatedDate = DateTime.Now,
is_Deleted = 0
};
slmdb.kkslm_tr_prelead_prepare_sms.AddObject(sms);
return(smsMessage);
}
catch
{
throw;
}
}
public void UpdateStatusAndOwnerLogging(SLMDBEntities slmdb, kkslm_tr_lead lead, DateTime createdDate, string newStatusCode, string newSubStatusCode, string loggingType)
{
try
{
string oldStatus = lead.slm_Status;
string oldSubStatus = string.IsNullOrEmpty(lead.slm_SubStatus) ? null : lead.slm_SubStatus;
string oldExternalSubStatusDesc = string.IsNullOrEmpty(lead.slm_ExternalSubStatusDesc) ? null : lead.slm_ExternalSubStatusDesc;
string oldExternalSystem = lead.slm_ExternalSystem;
string oldExternalStatus = lead.slm_ExternalStatus;
string oldExternalSubStatus = lead.slm_ExternalSubStatus;
newSubStatusCode = string.IsNullOrEmpty(newSubStatusCode) ? null : newSubStatusCode;
if (oldStatus != newStatusCode || oldSubStatus != newSubStatusCode)
{
string newSubStatusDesc = GetSubStatusName(slmdb, lead.slm_CampaignId, lead.slm_Product_Id, newStatusCode, newSubStatusCode);
kkslm_tr_activity act = new kkslm_tr_activity()
{
slm_TicketId = lead.slm_ticketId,
slm_OldStatus = oldStatus,
slm_OldSubStatus = oldSubStatus,
slm_NewStatus = newStatusCode,
slm_NewSubStatus = newSubStatusCode,
slm_CreatedBy = "SYSTEM",
slm_CreatedBy_Position = null,
slm_CreatedDate = createdDate,
slm_Type = loggingType,
slm_SystemAction = "SLM",
slm_SystemActionBy = "SLM",
slm_ExternalSystem_Old = oldExternalSystem,
slm_ExternalStatus_Old = oldExternalStatus,
slm_ExternalSubStatus_Old = oldExternalSubStatus,
slm_ExternalSubStatusDesc_Old = oldExternalSubStatusDesc,
slm_ExternalStatus_New = null,
slm_ExternalSubStatus_New = null,
slm_ExternalSystem_New = null,
slm_ExternalSubStatusDesc_New = newSubStatusDesc
};
slmdb.kkslm_tr_activity.AddObject(act);
AppUtil.CalculateTotalSLA(slmdb, lead, act, createdDate, logfilename, BatchCode);
lead.slm_Status = newStatusCode;
lead.slm_SubStatus = newSubStatusCode;
lead.slm_StatusDate = createdDate;
lead.slm_StatusDateSource = createdDate;
lead.slm_StatusBy = "SYSTEM";
lead.slm_Counting = 0;
lead.slm_ExternalSystem = null;
lead.slm_ExternalStatus = null;
lead.slm_ExternalSubStatus = null;
lead.slm_ExternalSubStatusDesc = newSubStatusDesc;
lead.slm_UpdatedBy = "SYSTEM";
lead.slm_UpdatedDate = createdDate;
}
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertPhoneCallHistory(string ticketId, string cardType, string cardId, string leadStatusCode, string oldstatus, string ownerBranch, string owner, string oldOwner, string delegateLeadBranch, string delegateLead, string oldDelegateLead, string contactPhone, string contactDetail, string createBy)
{
try
{
kkslm_tr_lead lead = null;
DateTime createDate = DateTime.Now;
int? createdByPositionId = null;
int? ownerPositionId = null;
var cusinfo = slmdb.kkslm_tr_cusinfo.Where(p => p.slm_TicketId == ticketId).FirstOrDefault();
if (cusinfo != null)
{
if (cardType != "")
{
cusinfo.slm_CardType = int.Parse(cardType);
}
else
{
cusinfo.slm_CardType = null;
}
string oldCitizenId = string.IsNullOrEmpty(cusinfo.slm_CitizenId) ? null : cusinfo.slm_CitizenId;
cusinfo.slm_CitizenId = cardId != "" ? cardId : null;
if (oldCitizenId != cusinfo.slm_CitizenId)
{
KKSlmTrHistoryModel.InsertHistory(slmdb, ticketId, SLMConstant.HistoryTypeCode.UpdateCardId, oldCitizenId, cusinfo.slm_CitizenId, createBy, createDate);
}
}
kkslm_phone_call phone = new kkslm_phone_call();
phone.slm_TicketId = ticketId;
phone.slm_ContactPhone = contactPhone;
phone.slm_ContactDetail = contactDetail;
phone.slm_Status = leadStatusCode;
phone.slm_Owner = owner;
ownerPositionId = GetPositionId(owner);
phone.slm_Owner_Position = ownerPositionId;
phone.slm_CreateDate = createDate;
phone.slm_CreateBy = createBy;
createdByPositionId = GetPositionId(createBy);
phone.slm_CreatedBy_Position = createdByPositionId;
phone.is_Deleted = 0;
slmdb.kkslm_phone_call.AddObject(phone);
if (lead == null)
{
lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == ticketId).FirstOrDefault();
}
if (leadStatusCode != oldstatus)
{
kkslm_tr_activity activity = new kkslm_tr_activity();
activity.slm_TicketId = ticketId;
activity.slm_OldStatus = oldstatus;
activity.slm_NewStatus = leadStatusCode;
activity.slm_CreatedBy = createBy;
activity.slm_CreatedBy_Position = createdByPositionId;
activity.slm_CreatedDate = createDate;
activity.slm_Type = SLMConstant.ActionType.ChangeStatus; //02
activity.slm_SystemAction = SLMConstant.SystemName; //System ที่เข้ามาทำ action (19/03/2015)
activity.slm_SystemActionBy = SLMConstant.SystemName; //action เกิดขึ้นที่ระบบอะไร (19/03/2015)
activity.slm_ExternalSystem_Old = lead.slm_ExternalSystem; //add 14/10/2015
activity.slm_ExternalStatus_Old = lead.slm_ExternalStatus; //add 14/10/2015
activity.slm_ExternalSubStatus_Old = lead.slm_ExternalSubStatus; //add 14/10/2015
activity.slm_ExternalSubStatusDesc_Old = lead.slm_ExternalSubStatusDesc; //add 14/10/2015
lead.slm_ExternalSystem = null; //add 14/10/2015
lead.slm_ExternalStatus = null; //add 14/10/2015
lead.slm_ExternalSubStatus = null; //add 14/10/2015
lead.slm_ExternalSubStatusDesc = null; //add 14/10/2015
slmdb.kkslm_tr_activity.AddObject(activity);
if (lead == null)
{
lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == ticketId).FirstOrDefault();
}
if (lead != null)
{
lead.slm_Status = leadStatusCode;
lead.slm_StatusBy = createBy;
lead.slm_StatusDate = createDate;
lead.slm_Counting = 0;
lead.slm_UpdatedBy = createBy;
lead.slm_UpdatedDate = createDate;
}
KKSlmTrHistoryModel.InsertHistory(slmdb, ticketId, SLMConstant.HistoryTypeCode.UpdateStatus, oldstatus, leadStatusCode, createBy, createDate);
}
if (owner != oldOwner)
{
kkslm_tr_activity activity = new kkslm_tr_activity();
activity.slm_TicketId = ticketId;
if (!string.IsNullOrEmpty(oldOwner))
{
activity.slm_OldOwner = oldOwner;
activity.slm_OldOwner_Position = GetPositionId(oldOwner);
}
activity.slm_NewOwner = owner;
activity.slm_NewOwner_Position = ownerPositionId;
activity.slm_CreatedBy = createBy;
activity.slm_CreatedBy_Position = createdByPositionId;
activity.slm_CreatedDate = createDate;
activity.slm_Type = SLMConstant.ActionType.ChangeOwner;
activity.slm_SystemAction = SLMConstant.SystemName; //System ที่เข้ามาทำ action (19/03/2015)
activity.slm_SystemActionBy = SLMConstant.SystemName; //action เกิดขึ้นที่ระบบอะไร (19/03/2015)
slmdb.kkslm_tr_activity.AddObject(activity);
if (lead == null)
{
lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == ticketId).FirstOrDefault();
}
if (lead != null)
{
lead.slm_StaffId = slmdb.kkslm_ms_staff.Where(p => p.slm_UserName == owner).Select(p => p.slm_StaffId).FirstOrDefault();
lead.slm_Owner = owner;
lead.slm_Owner_Branch = ownerBranch;
lead.slm_Owner_Position = ownerPositionId;
lead.slm_AssignedFlag = "0";
lead.slm_AssignedDate = null;
lead.slm_AssignedBy = null;
lead.slm_OldOwner = oldOwner;
}
KKSlmTrHistoryModel.InsertHistory(slmdb, ticketId, SLMConstant.HistoryTypeCode.UpdateOwner, oldOwner, owner, createBy, createDate);
}
if (delegateLead != oldDelegateLead)
{
kkslm_tr_activity activity = new kkslm_tr_activity();
activity.slm_TicketId = ticketId;
if (!string.IsNullOrEmpty(oldDelegateLead))
{
activity.slm_OldDelegate = oldDelegateLead;
activity.slm_OldDelegate_Position = GetPositionId(oldDelegateLead);
}
activity.slm_NewDelegate = delegateLead;
activity.slm_NewDelegate_Position = GetPositionId(delegateLead);
activity.slm_CreatedBy = createBy;
activity.slm_CreatedBy_Position = createdByPositionId;
activity.slm_CreatedDate = createDate;
activity.slm_Type = SLMConstant.ActionType.Delegate;
activity.slm_SystemAction = SLMConstant.SystemName; //System ที่เข้ามาทำ action (19/03/2015)
activity.slm_SystemActionBy = SLMConstant.SystemName; //action เกิดขึ้นที่ระบบอะไร (19/03/2015)
slmdb.kkslm_tr_activity.AddObject(activity);
if (lead == null)
{
lead = slmdb.kkslm_tr_lead.Where(p => p.slm_ticketId == ticketId).FirstOrDefault();
}
if (lead != null)
{
lead.slm_Delegate_Flag = string.IsNullOrEmpty(delegateLead) ? 0 : 1;
if (!string.IsNullOrEmpty(delegateLead))
{
lead.slm_DelegateDate = createDate;
}
else
{
lead.slm_DelegateDate = null;
}
lead.slm_Delegate = string.IsNullOrEmpty(delegateLead) ? null : delegateLead;
lead.slm_Delegate_Branch = string.IsNullOrEmpty(delegateLead) ? null : delegateLeadBranch;
lead.slm_Delegate_Position = string.IsNullOrEmpty(delegateLead) ? null : GetPositionId(delegateLead);
}
KKSlmTrHistoryModel.InsertHistory(slmdb, ticketId, SLMConstant.HistoryTypeCode.UpdateDelegate, oldDelegateLead, delegateLead, createBy, createDate);
}
slmdb.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
//public static void UpdateStatusBackEnd(string batchCode)
//{
// Int64 batchMonitorId = 0;
// int totalRecord = 0;
// int totalSuccess = 0;
// int totalFail = 0;
// int i = 1;
// int countCheck = GetCountCheck();
// int interval_second = (GetInterval() * 60) * 1000;
// bool success = false;
// try
// {
// while (i <= countCheck && success == false)
// {
// if (CheckViewReady())
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// var list = GetExtSystemCurrentStatusViewList();
// totalRecord = list.Count;
// foreach (ExtSystemCurrentStatusData data in list)
// {
// try
// {
// SLMDBEntities slmdb = AppUtil.GetSlmDbEntities();
// 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"); }
// CheckStatus(slmdb, lead, data);
// 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, data.TicketId, "", errorDetail);
// Util.WriteLogFile(logfilename, batchCode, errorDetail);
// Console.WriteLine("TicketId " + data.TicketId + ": FAIL");
// }
// }
// BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail);
// success = true;
// }
// else
// {
// System.Threading.Thread.Sleep(interval_second);
// }
// i += 1;
// }
// if (success == false)
// {
// batchMonitorId = BizUtil.SetStartTime(batchCode);
// BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail);
// BizUtil.InsertLog(batchMonitorId, "", "", "View v_kkslm_ext_sys_status is not ready");
// Util.WriteLogFile(logfilename, batchCode, "View v_kkslm_ext_sys_status is not ready");
// }
// }
// 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;
// }
//}
private void CheckStatus(SLMDBEntities slmdb, kkslm_tr_lead lead, ExtSystemCurrentStatusData data, string batchCode)
{
try
{
string newExternalSystem = "";
string newExternalStatus = "";
string newExternalSubStatus = "";
string newExternalSubStatusDesc = "";
string oldExternalSystem = "";
string oldExternalStatus = "";
string oldExternalSubStatus = "";
string oldExternalSubStatusDesc = "";
string slmOldStatus = "";
slmOldStatus = lead.slm_Status;
oldExternalSystem = lead.slm_ExternalSystem; //add 15/10/2015
oldExternalStatus = lead.slm_ExternalStatus; //add 15/10/2015
oldExternalSubStatus = string.IsNullOrEmpty(lead.slm_ExternalSubStatus) ? null : lead.slm_ExternalSubStatus; //add 15/10/2015
oldExternalSubStatusDesc = lead.slm_ExternalSubStatusDesc; //add 15/10/2015
if (string.IsNullOrEmpty(data.StatusSystem))
{
throw new Exception("System Name in view is null or blank");
}
string slmNewStatus = GetSlmStatusCode(slmdb, data.StatusSystem, data.StatusCode, data.SubStatusCode, lead.slm_Product_Id);
if (slmNewStatus == "")
{
throw new Exception("Cannot find slmStatusCode from the specified " + data.StatusSystem + " mapping statusCode=" + data.StatusCode + ", subStatusCode=" + data.SubStatusCode + ", slmProductId=" + lead.slm_Product_Id);
}
var statusBy_username = slmdb.kkslm_ms_staff.Where(p => p.slm_EmpCode == data.StatusBy && p.is_Deleted == 0).Select(p => p.slm_UserName).FirstOrDefault();
//Change 15/10/2015
newExternalSystem = data.StatusSystem.Trim().ToUpper();
newExternalStatus = data.StatusCode;
newExternalSubStatus = string.IsNullOrEmpty(data.SubStatusCode) ? null : data.SubStatusCode;
if (!string.IsNullOrEmpty(data.StatusName) && !string.IsNullOrEmpty(data.SubStatusCode))
{
newExternalSubStatusDesc = data.StatusName + " - " + data.SubStatusCode;
}
else if (!string.IsNullOrEmpty(data.StatusName))
{
newExternalSubStatusDesc = data.StatusName;
}
else if (!string.IsNullOrEmpty(data.SubStatusCode))
{
newExternalSubStatusDesc = data.SubStatusCode;
}
if (slmNewStatus != slmOldStatus || newExternalStatus != oldExternalStatus || newExternalSubStatus != oldExternalSubStatus || newExternalSystem != oldExternalSystem)
{
DateTime updateDate = DateTime.Now;
kkslm_tr_activity activity = new kkslm_tr_activity()
{
slm_TicketId = data.TicketId,
slm_OldStatus = slmOldStatus,
slm_NewStatus = slmNewStatus,
slm_CreatedBy = "SYSTEM",
slm_CreatedBy_Position = null,
slm_CreatedDate = updateDate,
slm_Type = "09",
slm_SystemAction = data.StatusSystem.Trim().ToUpper(), //System ที่เข้ามาทำ action (19/03/2015)
slm_SystemActionBy = "SLM", //action เกิดขึ้นที่ระบบอะไร (19/03/2015)
slm_ExternalSystem_Old = oldExternalSystem, //add 15/10/2015
slm_ExternalStatus_Old = oldExternalStatus, //add 15/10/2015
slm_ExternalSubStatus_Old = oldExternalSubStatus, //add 15/10/2015
slm_ExternalSubStatusDesc_Old = oldExternalSubStatusDesc, //add 15/10/2015
slm_ExternalSystem_New = newExternalSystem, //add 15/10/2015
slm_ExternalStatus_New = newExternalStatus, //add 15/10/2015
slm_ExternalSubStatus_New = newExternalSubStatus, //add 15/10/2015
slm_ExternalSubStatusDesc_New = newExternalSubStatusDesc //add 15/10/2015
};
slmdb.kkslm_tr_activity.AddObject(activity);
AppUtil.CalculateTotalSLA(slmdb, lead, activity, data.StatusDate.Value, logfilename, batchCode); //add 25/03/2016
lead.slm_Status = slmNewStatus;
lead.slm_StatusBy = (!string.IsNullOrEmpty(statusBy_username) ? statusBy_username : data.StatusBy);
lead.slm_StatusDate = data.StatusDate;
lead.slm_StatusDateSource = data.StatusDate;
lead.slm_Counting = 0;
lead.slm_UpdatedBy = "SYSTEM";
lead.slm_UpdatedDate = updateDate;
lead.slm_ExternalSystem = newExternalSystem;
lead.slm_ExternalStatus = newExternalStatus;
lead.slm_ExternalSubStatus = newExternalSubStatus;
lead.slm_ExternalSubStatusDesc = newExternalSubStatusDesc;
}
}
catch (Exception ex)
{
throw ex;
}
}
private void CheckStatus(SLMDBEntities slmdb, kkslm_tr_lead lead, BatchCARInsertStatusData data, string batchCode)
{
try
{
string newExternalSystem = "";
string newExternalStatus = "";
string newExternalSubStatus = "";
string newExternalSubStatusDesc = "";
string oldExternalSystem = "";
string oldExternalStatus = "";
string oldExternalSubStatus = "";
string oldExternalSubStatusDesc = "";
string slmOldStatus = "";
slmOldStatus = lead.slm_Status;
oldExternalSystem = lead.slm_ExternalSystem;
oldExternalStatus = lead.slm_ExternalStatus;
oldExternalSubStatus = string.IsNullOrEmpty(lead.slm_ExternalSubStatus) ? null : lead.slm_ExternalSubStatus;
oldExternalSubStatusDesc = lead.slm_ExternalSubStatusDesc;
if (string.IsNullOrEmpty(data.OwnerSystemCode))
{
throw new Exception("System Name in view is null or blank");
}
string slmNewStatus = GetSlmStatusCode(slmdb, data.OwnerSystemCode, data.Status, "", lead.slm_Product_Id);
if (slmNewStatus == "")
{
throw new Exception("Cannot find slmStatusCode from the specified " + data.Status + " mapping statusCode=" + data.Status + ", slmProductId=" + lead.slm_Product_Id);
}
newExternalSystem = data.OwnerSystemCode.Trim().ToUpper();
newExternalStatus = data.Status;
newExternalSubStatus = data.Status;
newExternalSubStatusDesc = data.StatusName;
if (slmNewStatus != slmOldStatus || newExternalStatus != oldExternalStatus || newExternalSubStatus != oldExternalSubStatus || newExternalSystem != oldExternalSystem)
{
DateTime updateDate = DateTime.Now;
kkslm_tr_activity activity = new kkslm_tr_activity()
{
slm_TicketId = data.RefSystemId,
slm_OldStatus = slmOldStatus,
slm_NewStatus = slmNewStatus,
slm_CreatedBy = "SYSTEM",
slm_CreatedBy_Position = null,
slm_CreatedDate = updateDate,
slm_Type = "09",
slm_SystemAction = data.OwnerSystemCode.Trim().ToUpper(), //System ที่เข้ามาทำ action (18/10/2017)
slm_SystemActionBy = "SLM", //action เกิดขึ้นที่ระบบอะไร (18/10/2017)
slm_ExternalSystem_Old = oldExternalSystem,
slm_ExternalStatus_Old = oldExternalStatus,
slm_ExternalSubStatus_Old = oldExternalSubStatus,
slm_ExternalSubStatusDesc_Old = oldExternalSubStatusDesc,
slm_ExternalSystem_New = newExternalSystem,
slm_ExternalStatus_New = newExternalStatus,
slm_ExternalSubStatus_New = newExternalSubStatus,
slm_ExternalSubStatusDesc_New = newExternalSubStatusDesc
};
slmdb.kkslm_tr_activity.AddObject(activity);
DateTime StatusDateTime = data.StatusDateTime.ConvertStringToStatusDateTime();
AppUtil.CalculateTotalSLA(slmdb, lead, activity, StatusDateTime, logfilename, batchCode);
lead.slm_Status = slmNewStatus;
lead.slm_StatusDate = StatusDateTime;
lead.slm_StatusDateSource = StatusDateTime;
lead.slm_Counting = 0;
lead.slm_UpdatedBy = "SYSTEM";
lead.slm_UpdatedDate = updateDate;
lead.slm_ExternalSystem = newExternalSystem;
lead.slm_ExternalStatus = newExternalStatus;
}
}
catch (Exception ex)
{
throw ex;
}
}
private void InsertPrepareSMS(SLMDBEntities slmdb, kkslm_tr_lead lead, kkslm_tr_renewinsurance renew, List <InsuranceCompanyData> insurComList, List <CoverageTypeData> coverageList, DateTime createdDate)
{
try
{
string filePath = AppConstant.SMSTemplatePathPolicyNo;
if (string.IsNullOrWhiteSpace(filePath))
{
throw new Exception("ไม่พบ Config SMSTemplatePathPolicyNo ใน Configuration File");
}
var paidAmount = CalculateTotalPaidAmount(slmdb, renew.slm_TicketId, AppConstant.PaymentCode.Policy);
var coverageTypeName = coverageList.Where(p => p.CoverageTypeId == renew.slm_CoverageTypeId).Select(p => p.CoverageTypeName).FirstOrDefault();
var insurTelNo = insurComList.Where(p => p.InsComId == renew.slm_InsuranceComId).Select(p => p.TelContact).FirstOrDefault();
var insNameAbb = insurComList.Where(p => p.InsComId == renew.slm_InsuranceComId).Select(p => p.InsNameAbb).FirstOrDefault();
string policyStartCoverDate = "";
if (renew.slm_PolicyStartCoverDate != null)
{
policyStartCoverDate = renew.slm_PolicyStartCoverDate.Value.ToString("dd/MM/") + (renew.slm_PolicyStartCoverDate.Value.Year + 543).ToString().Substring(2, 2);
}
string template = File.ReadAllText(filePath, Encoding.UTF8);
template = template.Replace("%InsNameAbb%", string.IsNullOrWhiteSpace(insNameAbb) ? "" : insNameAbb)
.Replace("%CoverageType%", string.IsNullOrWhiteSpace(coverageTypeName) ? "" : coverageTypeName)
.Replace("%PolicyPremiumPaid%", paidAmount.ToString("#,##0.00"))
.Replace("%PolicyNo%", renew.slm_PolicyNo)
.Replace("%PolicyStartCoverDate%", policyStartCoverDate)
//.Replace("%CarLicenseNo%", renew.slm_LicenseNo)
.Replace("%InsuranceTelNo%", string.IsNullOrWhiteSpace(insurTelNo) ? "" : insurTelNo);
kkslm_tr_prelead_prepare_sms sms = new kkslm_tr_prelead_prepare_sms()
{
slm_ticketId = renew.slm_TicketId,
slm_Message = template,
slm_Message_CreatedBy = "SYSTEM",
slm_MessageStatusId = "1",
slm_PhoneNumber = GetTelNoSMS(lead),
slm_QueueId = "6",
slm_RequestDate = createdDate,
slm_RuleActivityId = 0,
slm_ExportStatus = "0",
slm_RefId = null,
slm_SendingStatusCode = null,
slm_SendingStatus = null,
slm_ErrorCode = null,
slm_ErrorReason = null,
slm_CAS_Flag = null,
slm_CAS_Date = null,
slm_FlagType = "2",
slm_CreatedBy = "SYSTEM",
slm_CreatedDate = createdDate,
slm_UpdatedBy = "SYSTEM",
slm_UpdatedDate = createdDate,
is_Deleted = 0
};
slmdb.kkslm_tr_prelead_prepare_sms.AddObject(sms);
slmdb.SaveChanges();
}
catch
{
throw;
}
}
/// <summary>
///
/// </summary>
/// <param name="slmdb"></param>
/// <param name="lead"></param>
/// <param name="act"></param>
/// <param name="newStatusDate">newStatusDate คือ วันเวลาที่เปลี่ยนสถานะใหม่</param>
public static void CalculateTotalSLA(SLMDBEntities slmdb, kkslm_tr_lead lead, kkslm_tr_activity act, DateTime newStatusDate, string logfilename, string batchCode)
{
try
{
//Initial variables
DateTime currentSla = new DateTime();
DateTime currentDate = DeleteSeconds(newStatusDate);
int thisWork = lead.slm_ThisWork != null?Convert.ToInt32(lead.slm_ThisWork) : 0;
int slaCounting = lead.slm_Counting != null?Convert.ToInt32(lead.slm_Counting.Value) : 0;
int workingMinPerDay = 0;
int startTimeHour = 0;
int startTimeMin = 0;
int endTimeHour = 0;
int endTimeMin = 0;
var calendarTab = slmdb.kkslm_ms_calendar_branch.Where(p => p.slm_BranchCode == lead.slm_Owner_Branch && p.is_Deleted == false).ToList();
var branch = slmdb.kkslm_ms_branch.Where(p => p.slm_BranchCode == lead.slm_Owner_Branch).FirstOrDefault();
if (branch == null)
{
throw new Exception("ไม่พบข้อมูลสาขา BranchCode:" + lead.slm_Owner_Branch);
}
if (string.IsNullOrEmpty(branch.slm_StartTime_Hour) || string.IsNullOrEmpty(branch.slm_StartTime_Minute) ||
string.IsNullOrEmpty(branch.slm_EndTime_Hour) || string.IsNullOrEmpty(branch.slm_EndTime_Minute))
{
string start = slmdb.kkslm_ms_option.Where(p => p.slm_OptionCode == "startTime").Select(p => p.slm_OptionDesc).FirstOrDefault();
string end = slmdb.kkslm_ms_option.Where(p => p.slm_OptionCode == "endTime").Select(p => p.slm_OptionDesc).FirstOrDefault();
if (start != null)
{
string[] str = start.Split(':');
if (str.Count() == 2 && str[0].Trim() != "" && str[1].Trim() != "")
{
startTimeHour = Convert.ToInt32(str[0]);
startTimeMin = Convert.ToInt32(str[1]);
}
else
{
throw new Exception("ไม่พบเวลาเปิดสาขา: " + branch.slm_BranchName);
}
}
else
{
throw new Exception("ไม่พบเวลาเปิดสาขา: " + branch.slm_BranchName);
}
if (end != null)
{
string[] str = end.Split(':');
if (str.Count() == 2 && str[0].Trim() != "" && str[1].Trim() != "")
{
endTimeHour = Convert.ToInt32(str[0]);
endTimeMin = Convert.ToInt32(str[1]);
}
else
{
throw new Exception("ไม่พบเวลาปิดสาขา: " + branch.slm_BranchName);
}
}
else
{
throw new Exception("ไม่พบเวลาปิดสาขา: " + branch.slm_BranchName);
}
}
else
{
startTimeHour = int.Parse(branch.slm_StartTime_Hour);
endTimeMin = int.Parse(branch.slm_StartTime_Minute);
endTimeHour = int.Parse(branch.slm_EndTime_Hour);
endTimeMin = int.Parse(branch.slm_EndTime_Minute);
DateTime tmpStart = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, startTimeHour, startTimeMin, 0);
DateTime tmpEnd = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, endTimeHour, endTimeMin, 0);
TimeSpan tmpTs = tmpEnd.Subtract(tmpStart);
workingMinPerDay = Convert.ToInt32(tmpTs.TotalMinutes); //ได้เวลาที่ต้องทำงานในแต่ละวันของสาขา หน่วยเป็นนาที
}
if (slaCounting == 0) //กรณีทำงานเสร็จก่อน sla เตือน
{
//เผื่อในกรณี slm_StatusDate เป็น null, ซึ่งน่าจะไม่มีโอกาสเกิดเคสนี้ ยกเว้น คีย์ข้อมูลลงเบสตรง
currentSla = lead.slm_StatusDate != null?DeleteSeconds(lead.slm_StatusDate.Value) : currentDate;
}
else
{
currentSla = lead.slm_CurrentSLA != null?DeleteSeconds(lead.slm_CurrentSLA.Value) : currentDate;
}
//ปรับเวลาในกรณี currentSla อยู่นอกเวลาทำงาน, หรือถ้าปรับแล้วยังไปตกวันหยุดก็ต้องให้เลื่อนไปจนถึงวันทำงาน
int checkStartTime = Convert.ToInt32(startTimeHour.ToString("00") + startTimeMin.ToString("00"));
int checkEndTime = Convert.ToInt32(endTimeHour.ToString("00") + endTimeMin.ToString("00"));
int timeToCheck = Convert.ToInt32(currentSla.Hour.ToString("00") + currentSla.Minute.ToString("00"));
if (timeToCheck < checkStartTime || timeToCheck > checkEndTime) //ถ้าเวลาของ currentSla ไม่ได้อยู่ในช่วงเวลาทำงาน
{
if (timeToCheck >= checkEndTime && timeToCheck <= 2359)
{
currentSla = currentSla.AddDays(1);
currentSla = new DateTime(currentSla.Year, currentSla.Month, currentSla.Day, startTimeHour, startTimeMin, 0);
}
else
{
currentSla = new DateTime(currentSla.Year, currentSla.Month, currentSla.Day, startTimeHour, startTimeMin, 0);
}
}
while (calendarTab.Where(p => p.slm_HolidayDate.Date == currentSla.Date).Count() > 0)
{
currentSla = currentSla.AddDays(1);
}
//ปรับเวลาในกรณี currentDate อยู่นอกเวลาทำงาน, หรือถ้าปรับแล้วยังไปตกวันหยุดก็ต้องให้เลื่อนไปจนถึงวันทำงาน
timeToCheck = Convert.ToInt32(currentDate.Hour.ToString("00") + currentDate.Minute.ToString("00"));
if (timeToCheck < checkStartTime || timeToCheck > checkEndTime) //ถ้าเวลาของ currentDate ไม่ได้อยู่ในช่วงเวลาทำงาน
{
if (timeToCheck >= checkEndTime && timeToCheck <= 2359)
{
currentDate = currentDate.AddDays(1);
currentDate = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, startTimeHour, startTimeMin, 0);
}
else
{
currentDate = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, startTimeHour, startTimeMin, 0);
}
}
while (calendarTab.Where(p => p.slm_HolidayDate.Date == currentDate.Date).Count() > 0)
{
currentDate = currentDate.AddDays(1);
}
//คำนวณ TotalAlert, TotalWork
if (currentSla.Date == currentDate.Date)
{
TimeSpan ts = currentDate.Subtract(currentSla);
thisWork += Convert.ToInt32(ts.TotalMinutes);
}
else
{
//หาวันที่อยู่ตรงกลางระหว่าง currentSla กับ currentDate
//เช่น currentSla = 12/04/2016, currentDate = 16/04/2016 ให้หาวันที่ 13, 14, 15 ออกมาเพื่อเช็กว่าเป็นวันทำงานหรือวันหยุด ถ้าเป็นวันทำงานให้เก็บจำนวนนาทีที่ต้องทำงานต่อวัน
DateTime startDate = currentSla.Date;
DateTime endDate = currentDate.Date;
DateTime tmpDate = startDate.AddDays(1);
while (tmpDate < endDate)
{
if (calendarTab.Where(p => p.slm_HolidayDate == tmpDate).Count() == 0)
{
thisWork += workingMinPerDay;
}
tmpDate = tmpDate.AddDays(1);
}
//ให้คำนวณเวลาที่เหลือในส่วนของวัน startDate และ endDate
DateTime endWorkTime_for_currentSla = new DateTime(currentSla.Year, currentSla.Month, currentSla.Day, endTimeHour, endTimeMin, 0);
DateTime startWorkTime_for_currentDate = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, startTimeHour, startTimeMin, 0);
TimeSpan ts = endWorkTime_for_currentSla.Subtract(currentSla);
thisWork += Convert.ToInt32(ts.TotalMinutes);
ts = currentDate.Subtract(startWorkTime_for_currentDate);
thisWork += Convert.ToInt32(ts.TotalMinutes);
}
lead.slm_TotalAlert = (lead.slm_TotalAlert != null ? Convert.ToInt32(lead.slm_TotalAlert) : 0) + slaCounting;
lead.slm_TotalWork = (lead.slm_TotalWork != null ? Convert.ToInt32(lead.slm_TotalWork) : 0) + thisWork;
lead.slm_Counting = 0;
lead.slm_ThisWork = 0;
lead.slm_CurrentSLA = null;
//OwnerLoggin
act.slm_ThisAlert = slaCounting;
act.slm_ThisWork = thisWork;
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
Util.WriteLogFile(logfilename, batchCode, message);
}
}
