public void DeleteTempNotifyPremium() { try { using (SLMDBEntities slmdb = AppUtil.GetSlmDbEntities()) { int numOfDay; var tmp = slmdb.kkslm_ms_option.Where(p => p.slm_OptionCode == "notify_premium_temp_day" && p.is_Deleted == 0).Select(p => p.slm_OptionDesc).FirstOrDefault(); if (!int.TryParse(tmp, out numOfDay)) { numOfDay = 60; } DateTime date = DateTime.Now.AddDays(-numOfDay); var strDate = date.Year.ToString() + date.ToString("-MM-dd"); string sql = $"DELETE FROM kkslm_tr_notify_premium_temp WHERE slm_CreatedDate < '{strDate}' "; slmdb.ExecuteStoreCommand(sql); } } catch (Exception ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; string errorDetail = "Purge data in kkslm_tr_notify_premium_temp failed, Error=" + message; Util.WriteLogFile(logfilename, BatchCode, errorDetail); BizUtil.InsertLog(BatchMonitorId, "", "", errorDetail); } }
public void ResetRunningOfTicketId(string batchCode) { Int64 batchMonitorId = 0; int totalRecord = 0; int totalSuccess = 0; int totalFail = 0; try { totalRecord = 1; batchMonitorId = BizUtil.SetStartTime(batchCode); SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); var curr_year = slmdb.kkslm_ms_current_year.FirstOrDefault(); if (curr_year != null) { int year = curr_year.slm_year != null ? curr_year.slm_year.Value : 0; if (year != 0) { if (DateTime.Now.Year > year) { using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted })) { curr_year.slm_year = DateTime.Now.Year; slmdb.SaveChanges(); slmdb.ExecuteStoreCommand("DBCC CHECKIDENT ('dbo.kkslm_gen_identity', RESEED, 0)"); ts.Complete(); } } totalSuccess = 1; BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail); } else { throw new Exception("ไม่พบข้อมูล current year ในตาราง kkslm_ms_current_year"); } } else { throw new Exception("ไม่พบข้อมูล current year ในตาราง kkslm_ms_current_year"); } } catch (Exception ex) { totalFail = 1; Console.WriteLine("All FAIL"); string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; Util.WriteLogFile(logfilename, batchCode, message); BizUtil.InsertLog(batchMonitorId, "", "", message); BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail); } }
public void ExportWSLog(string batchCode) { try { batchMonitorId = BizUtil.SetStartTime(batchCode); ErrorStep = "Select Data from DB"; Console.WriteLine(ErrorStep); DataTable dt = Database.LoadWSLogData("INS"); TotalExportExcel = dt.Rows.Count; if (TotalExportExcel > 0) { SaveExcelToSharePath(dt); if (ErrorDetail.Trim() != string.Empty) { TotalExportExcel = 0; } } else { ErrorDetail = "WSLog data not found."; Console.WriteLine(ErrorDetail); } } catch (Exception ex) { ErrorDetail = ex.ToString(); } 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); } try { Console.WriteLine("Send Mail"); SendMail(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); Util.WriteLogFile(logfilename, batchCode, ex.ToString()); BizUtil.InsertLog(batchMonitorId, "", "", ex.ToString()); } }
public void CalculateJobOnHand(string batchCode) { Int64 batchMonitorId = 0; int totalRecord = 0; int totalSuccess = 0; int totalFail = 0; try { batchMonitorId = BizUtil.SetStartTime(batchCode); List <JobOnHandData> list = GetJobOnHand(); totalRecord = list.Count; foreach (JobOnHandData data in list) { try { SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); string sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff SET slm_JobOnHand = '" + (data.AmountOwnerJob + data.AmountDelegateJob).ToString() + @"' WHERE slm_UserName = '******'"; slmdb.ExecuteStoreCommand(sql); totalSuccess += 1; Console.WriteLine("Usrename " + data.Username + ": SUCCESS"); } catch (Exception ex) { totalFail += 1; string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; string errorDetail = "Username="******", Error=" + message; BizUtil.InsertLog(batchMonitorId, "", "", errorDetail); Util.WriteLogFile(logfilename, batchCode, errorDetail); Console.WriteLine("Usrename " + data.Username + ": FAIL"); } } BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail); } catch (Exception ex) { Console.WriteLine("All FAIL"); string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; Util.WriteLogFile(logfilename, batchCode, message); BizUtil.InsertLog(batchMonitorId, "", "", message); BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail); } }
private void ValidateList(List <ContractInsuranceData> contractInsureList) { try { var removeList = new List <ContractInsuranceData>(); contractInsureList.ForEach(p => { bool isRemoved = false; if (string.IsNullOrWhiteSpace(p.ContractNo)) { isRemoved = true; } if (string.IsNullOrWhiteSpace(p.PolicyType)) { isRemoved = true; } else { if (p.PolicyType.ToUpper() != "V" && p.PolicyType.ToUpper() != "C") { isRemoved = true; } } if (string.IsNullOrWhiteSpace(p.PolicyNo)) { isRemoved = true; } if (isRemoved) { removeList.Add(p); } }); foreach (ContractInsuranceData data in removeList) { contractInsureList.Remove(data); Util.WriteLogFile(logfilename, BatchCode, string.Format("ContractNo : {0}, PolicyNo : {1}, PolicyType : {2}, Error : ข้อมูลนำเข้า ไม่สมบูรณ์", data.ContractNo, data.PolicyNo, data.PolicyType)); BizUtil.InsertLog(BatchMonitorId, "", "", string.Format("ContractNo : {0}, PolicyNo : {1}, PolicyType : {2}, Error : ข้อมูลนำเข้า ไม่สมบูรณ์", data.ContractNo, data.PolicyNo, data.PolicyType)); } } catch (Exception ex) { throw ex; } }
private void RollbackHP(Int64 batchMonitorId, string batchCode, List <TempDataPro03> tempList, SQLServerDB hpdb) { try { string sql = ""; foreach (TempDataPro03 data in tempList) { sql = @"DELETE FROM Import_MKT_Data_OBT WHERE year = '" + data.Year + "' AND month = '" + data.Month + "' AND contract_no = '" + data.ContractNo + "' AND mkt_code = '" + data.EmpCode + "' "; hpdb.ExecuteNonQuery(sql); } } catch (Exception ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; BizUtil.InsertLog(batchMonitorId, "", "", message); Util.WriteLogFile(logfilename, batchCode, message); } }
private void RollBackPrelead(Int64 batchMonitorId, string batchCode, List <TempDataPro03> tempList, SQLServerDB slmdb) { try { string sql = ""; foreach (TempDataPro03 data in tempList) { sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead SET slm_ObtPro03Flag = 0, slm_ObtPro03FlagDate = NULL WHERE slm_Prelead_Id = '" + data.PreleadId.ToString() + "'"; slmdb.ExecuteNonQuery(sql); } } catch (Exception ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; BizUtil.InsertLog(batchMonitorId, "", "", message); Util.WriteLogFile(logfilename, batchCode, message); } }
//public void CalculatePerformance(string batchCode) //{ // Int64 batchMonitorId = 0; // int totalRecord = 0; // int totalSuccess = 0; // int totalFail = 0; // try // { // batchMonitorId = BizUtil.SetStartTime(batchCode); // BizUtil.CheckPrerequisite(batchCode); // int day = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month); // DateTime lastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, day); // string year = lastDay.Year.ToString(); // string month = lastDay.Month.ToString("00"); // List<TelesalesPerformanceData> list = GetPerformanceData(year, month, lastDay); // totalRecord = list.Count; // foreach (TelesalesPerformanceData data in list) // { // try // { // DateTime createDate = DateTime.Now; // SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); // kkslm_tr_performance perform = new kkslm_tr_performance() // { // slm_TeamTelesales_Code = data.TelesalesTeam, // slm_EmpCode = data.EmpCode, // slm_EmpName = data.Fullname, // slm_Performance = data.PerformanceAmount, // slm_Year = year, // slm_Month = month, // slm_Level = data.LevelId, // slm_CreatedBy = "SYSTEM", // slm_CreatedDate = createDate, // slm_UpdatedBy = "SYSTEM", // slm_UpdatedDate = createDate, // is_Deleted = false // }; // slmdb.kkslm_tr_performance.AddObject(perform); // slmdb.SaveChanges(); // totalSuccess += 1; // Console.WriteLine("Owner " + data.Username + ": SUCCESS"); // } // catch (Exception ex) // { // totalFail += 1; // string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; // string errorDetail = "Owner=" + data.Username + ", Error=" + message; // BizUtil.InsertLog(batchMonitorId, "", "", errorDetail); // Util.WriteLogFile(logfilename, batchCode, errorDetail); // Console.WriteLine("Owner " + data.Username + ": FAIL"); // } // } // BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Success, totalRecord, totalSuccess, totalFail); // } // catch (Exception ex) // { // Console.WriteLine("All FAIL"); // string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; // Util.WriteLogFile(logfilename, batchCode, message); // BizUtil.InsertLog(batchMonitorId, "", "", message); // BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail); // throw ex; // } //} #endregion #region Backup 2017-03-22 // /// <summary> // /// คำนวณ Performance รายเดือนของ Telesales // /// </summary> // /// <param name="batchCode"></param> // public void CalculatePerformance(string batchCode) // { // Int64 batchMonitorId = 0; // int totalRecord = 0; // int totalSuccess = 0; // int totalFail = 0; // bool successFlag = true; // List<decimal> tempList = new List<decimal>(); // try // { // batchMonitorId = BizUtil.SetStartTime(batchCode); // BizUtil.CheckPrerequisite(batchCode); // int day = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month); // DateTime lastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, day); // string year = lastDay.Year.ToString(); // string month = lastDay.Month.ToString("00"); // List<TelesalesPerformanceData> list = GetPerformanceData(year, month, lastDay); // totalRecord = list.Count; // foreach (TelesalesPerformanceData data in list) // { // try // { // DateTime createDate = DateTime.Now; // SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); // kkslm_tr_performance perform = new kkslm_tr_performance() // { // slm_TeamTelesales_Code = data.TelesalesTeam, // slm_EmpCode = data.EmpCode, // slm_EmpName = data.Fullname, // slm_Performance = data.PerformanceAmount, // slm_Year = year, // slm_Month = month, // slm_Level = data.LevelId, // slm_CreatedBy = "SYSTEM", // slm_CreatedDate = createDate, // slm_UpdatedBy = "SYSTEM", // slm_UpdatedDate = createDate, // is_Deleted = false // }; // slmdb.kkslm_tr_performance.AddObject(perform); // slmdb.SaveChanges(); // tempList.Add(perform.slm_Performance_Id); // totalSuccess += 1; // Console.WriteLine("Owner " + data.Username + ": SUCCESS"); // } // catch (Exception ex) // { // successFlag = false; // Rollback(batchMonitorId, batchCode, tempList); // string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; // string errorDetail = "Owner=" + data.Username + ", Error=" + message; // BizUtil.InsertLog(batchMonitorId, "", "", errorDetail); // Util.WriteLogFile(logfilename, batchCode, errorDetail); // successFlag = false; // totalSuccess = 0; // totalFail = totalRecord; // break; // //Console.WriteLine("Owner " + data.Username + ": FAIL"); // } // } // BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), totalRecord, totalSuccess, totalFail); // } // catch (Exception ex) // { // Console.WriteLine("All FAIL"); // string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; // Util.WriteLogFile(logfilename, batchCode, message); // BizUtil.InsertLog(batchMonitorId, "", "", message); // BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, totalRecord, totalSuccess, totalFail); // } // } // private List<TelesalesPerformanceData> GetPerformanceData(string year, string month, DateTime lastDay) // { // try // { // if (lastDay == DateTime.Today) // { //// string sql = @"SELECT lead.slm_Owner AS [Username], staff.slm_EmpCode AS EmpCode, staff.slm_StaffNameTH AS Fullname //// , staff.slm_Team AS TelesalesTeam, staff.slm_Level AS LevelId, lev.slm_LevelName AS LevelName //// , SUM(reins.slm_PolicyGrossPremium + reins.slm_ActNetPremium) AS PerformanceAmount //// FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead //// INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_renewinsurance reins ON reins.slm_TicketId = lead.slm_ticketId //// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff staff ON staff.slm_UserName = lead.slm_Owner //// LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_level lev ON lev.slm_LevelId = staff.slm_Level //// WHERE lead.is_Deleted = 0 AND reins.slm_IncentiveFlag = 1 AND YEAR(reins.slm_IncentiveDate) = " + year + @" AND MONTH(reins.slm_IncentiveDate) = " + month + @" //// GROUP BY lead.slm_Owner, staff.slm_EmpCode, staff.slm_StaffNameTH, staff.slm_Team, staff.slm_Level, lev.slm_LevelName "; // string sql = @"SELECT lead.slm_Owner AS [Username], staff.slm_EmpCode AS EmpCode, staff.slm_StaffNameTH AS Fullname // , team.slm_TeamTelesales_Code AS TelesalesTeam, staff.slm_Level AS LevelId, lev.slm_LevelName AS LevelName // , SUM(ISNULL(reins.slm_PolicyGrossPremium, 0) + ISNULL(reins.slm_ActGrossPremium, 0)) AS PerformanceAmount // FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead // INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_renewinsurance reins ON reins.slm_TicketId = lead.slm_ticketId // LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff staff ON staff.slm_UserName = lead.slm_Owner // LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_level lev ON lev.slm_LevelId = staff.slm_Level // LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_teamtelesales team ON team.slm_TeamTelesales_Id = staff.slm_TeamTelesales_Id // WHERE lead.is_Deleted = 0 AND // ( // (reins.slm_IncentiveFlag = 1 AND YEAR(reins.slm_IncentiveDate) = '" + year + @"' AND MONTH(reins.slm_IncentiveDate) = '" + month + @"') OR // (reins.slm_ActIncentiveFlag = 1 AND YEAR(reins.slm_ActIncentiveDate) = '" + year + @"' AND MONTH(reins.slm_ActIncentiveDate) = '" + month + @"') // ) // GROUP BY lead.slm_Owner, staff.slm_EmpCode, staff.slm_StaffNameTH, team.slm_TeamTelesales_Code, staff.slm_Level, lev.slm_LevelName "; // SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); // return slmdb.ExecuteStoreQuery<TelesalesPerformanceData>(sql).ToList(); // } // else // return new List<TelesalesPerformanceData>(); // } // catch (Exception ex) // { // throw ex; // } // } // private void Rollback(Int64 batchMonitorId, string batchCode, List<decimal> tempList) // { // try // { // SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); // string sql = ""; // foreach (decimal id in tempList) // { // sql = "DELETE FROM " + AppConstant.SLMDBName + ".dbo.kkslm_tr_performance WHERE slm_Performance_Id = '" + id.ToString() + "'"; // slmdb.ExecuteStoreCommand(sql); // } // } // catch (Exception ex) // { // string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; // BizUtil.InsertLog(batchMonitorId, "", "", message); // Util.WriteLogFile(logfilename, batchCode, message); // } // } #endregion public void CalculatePerformance(string batchCode) { Int64 batchMonitorId = 0; bool successFlag = true; try { batchMonitorId = BizUtil.SetStartTime(batchCode); BizUtil.CheckPrerequisite(batchCode); int iDataCount = database.CalculatePerformance(); BizUtil.SetEndTime(batchCode, batchMonitorId, (successFlag ? AppConstant.Success : AppConstant.Fail), iDataCount, iDataCount, 0); } catch (Exception ex) { Console.WriteLine("All FAIL"); string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; Util.WriteLogFile(logfilename, batchCode, message); BizUtil.InsertLog(batchMonitorId, "", "", message); BizUtil.SetEndTime(batchCode, batchMonitorId, AppConstant.Fail, 0, 0, 0); } }
private void SnapDataSource(List <ContractInsuranceData> contractInsurList) { try { DateTime createdDate = DateTime.Now; using (var slmdb = AppUtil.GetSlmDbEntities()) { foreach (var data in contractInsurList) { var snap = new kkslm_tr_renewinsurance_hp_policyno_actno_snap { slm_Contract_Number = data.ContractNo, slm_Policy_Year = data.PolicyYear, slm_Policy_Type = data.PolicyType, slm_Policy_No = data.PolicyNo, slm_Main_By = data.MainBy, slm_Main_Date = data.MainDate, slm_Main_Time = data.MainTime, slm_Create_By = data.CreateBy, slm_Create_Date = data.CreateDate, slm_Create_Time = data.CreateTime, slm_Source = data.DataSource, slm_CreatedDate = createdDate, slm_CreatedBy = "SYSTEM" }; slmdb.kkslm_tr_renewinsurance_hp_policyno_actno_snap.AddObject(snap); } slmdb.SaveChanges(); } } catch (Exception ex) { string message = "Snap datasource failed because " + (ex.InnerException != null ? ex.InnerException.Message : ex.Message); Util.WriteLogFile(logfilename, BatchCode, message); BizUtil.InsertLog(BatchMonitorId, "", "", message); } }
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); } }
//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 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); } }
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(); } }
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 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 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); } }
/// <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 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); } }
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); }
//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 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(); } } }