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