public static void InsertSession(string username, Guid sessionId) { try { SLMDBEntities slmdb = new SLMDBEntities(); var session = slmdb.kkcoc_tr_session.Where(p => p.coc_UserName == username).FirstOrDefault(); if (session == null) { kkcoc_tr_session data = new kkcoc_tr_session() { coc_UserName = username, coc_SessionGuid = sessionId, coc_SessionCreatedDate = DateTime.Now }; slmdb.kkcoc_tr_session.AddObject(data); } else { session.coc_SessionGuid = sessionId; session.coc_SessionCreatedDate = DateTime.Now; } slmdb.SaveChanges(); } catch (Exception ex) { throw ex; } }
private void CheckPhoneCallHistory(SLMDBEntities slmdb, kkslm_tr_lead lead, ExtSystemCurrentStatusData data) { try { if (!string.IsNullOrEmpty(data.StatusDesc)) { if (data.StatusDesc.Trim().Length > 4000) { throw new Exception("Field kkslm_ext_StatusDesc exceeds 4000 character limitation"); } kkslm_phone_call pc = new kkslm_phone_call() { slm_TicketId = lead.slm_ticketId, slm_ContactDetail = data.StatusDesc.Trim(), slm_CreateBy = "SYSTEM", slm_CreatedBy_Position = null, slm_CreateDate = DateTime.Now, slm_Status = lead.slm_Status, slm_Owner = lead.slm_Owner, slm_Owner_Position = lead.slm_Owner_Position, slm_ContactPhone = null, is_Deleted = 0 }; slmdb.kkslm_phone_call.AddObject(pc); } } catch (Exception ex) { throw ex; } }
private List <UpdateTelesalesOwnerData> GetTelesalesOwner() { try { SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); // string sql = @"SELECT slm_Prelead_Id AS PreleadId, slm_Contract_Number AS ContractNo, slm_Owner AS EmpCode // FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead // WHERE is_Deleted = 0 and slm_AssignFlag = '1' and slm_Assign_Status = '1' and (slm_ObtPro03Flag IS NULL Or slm_ObtPro03Flag = 0) "; string sql = @"SELECT prelead.slm_Prelead_Id AS PreleadId, prelead.slm_Contract_Number AS ContractNo, prelead.slm_Owner AS EmpCode FROM ( SELECT slm_Contract_Number AS ContractNo, MAX(slm_Prelead_Id) AS PreleadId from " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead WHERE is_Deleted = 0 and slm_AssignFlag = '1' and slm_Assign_Status = '1' and (slm_ObtPro03Flag IS NULL Or slm_ObtPro03Flag = 0) GROUP BY slm_Contract_Number ) A INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead prelead ON prelead.slm_Prelead_Id = A.PreleadId WHERE prelead.is_Deleted = 0 and prelead.slm_AssignFlag = '1' and prelead.slm_Assign_Status = '1' and (prelead.slm_ObtPro03Flag IS NULL Or prelead.slm_ObtPro03Flag = 0) "; return(slmdb.ExecuteStoreQuery <UpdateTelesalesOwnerData>(sql).ToList()); } catch (Exception ex) { throw ex; } }
public static void CheckPrerequisite(string batchCode) { try { SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); var batch = slmdb.kkslm_ms_batch.Where(p => p.slm_BatchCode == batchCode).FirstOrDefault(); //Check Prerequisite if (batch != null && !string.IsNullOrEmpty(batch.slm_PreRequisite)) { string[] pre_batchs = batch.slm_PreRequisite.Split(','); foreach (string prebatch in pre_batchs) { string pre_batchcode = prebatch.Trim(); var obj = slmdb.kkslm_ms_batch.Where(p => p.slm_BatchCode == pre_batchcode).FirstOrDefault(); if (obj != null) { if (obj.slm_Status == "1" && obj.slm_EndTime != null) { //OK, Do Nothing } else { //Prerequisite not finish yet throw new Exception("Prerequisite batch (" + batch.slm_PreRequisite + ") has not finished yet"); } } } } } catch { throw; } }
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 static StaffDataManagement GetStaff(int staffId) { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_staff.Where(p => p.slm_StaffId == staffId).AsEnumerable().Select(p => new StaffDataManagement { StaffId = p.slm_StaffId, Username = p.slm_UserName, EmpCode = p.slm_EmpCode, MarketingCode = p.slm_MarketingCode, StaffNameTH = p.slm_StaffNameTH, TelNo = p.slm_TellNo, StaffEmail = p.slm_StaffEmail, StaffTypeId = p.slm_StaffTypeId, Team = p.slm_Team, BranchCode = p.slm_BranchCode, HeadStaffId = p.slm_HeadStaffId, PositionId = p.slm_Position_id.ToString(), PositionName = p.slm_PositionName, DepartmentId = p.slm_DepartmentId, CocTeam = p.coc_Team, Is_Deleted = p.is_Deleted }).FirstOrDefault()); }
public static string GetPrivilegeNCB(string productId, decimal staffTypeId) { SLMDBEntities slmdb = new SLMDBEntities(); var privilegeNCB = slmdb.kkcoc_ms_aol.Where(p => p.is_Deleted == 0 && p.coc_Product_Id == productId && p.coc_StaffTypeId == staffTypeId).Select(p => p.coc_Privilege_NBC).FirstOrDefault(); return(privilegeNCB != null ? privilegeNCB : ""); }
private List <JobOnHandData> GetJobOnHand() { try { SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); string sql = @"SELECT staff.slm_UserName AS Username, ( SELECT COUNT(*) AS NUM FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead WHERE lead.is_Deleted = 0 AND lead.slm_AssignedFlag = '1' AND lead.slm_Status NOT IN ('08','09','10') AND lead.slm_Owner = staff.slm_UserName AND lead.slm_Delegate IS NULL) AS AmountOwnerJob, ( SELECT COUNT(*) AS NUM FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead WHERE lead.is_Deleted = 0 AND lead.slm_Delegate_Flag = '0' AND lead.slm_Status NOT IN ('08','09','10') AND lead.slm_Delegate = staff.slm_UserName) AS AmountDelegateJob FROM " + AppConstant.SLMDBName + @".dbo.kkslm_ms_staff staff WHERE staff.is_Deleted = 0 "; return(slmdb.ExecuteStoreQuery <JobOnHandData>(sql).ToList()); } catch (Exception ex) { throw ex; } }
private kkslm_ext_sys_status_cbs_file checkFileHistory(string FileName, SLMDBEntities slmdb) { kkslm_ext_sys_status_cbs_file ret = null; try { var query = from f in slmdb.kkslm_ext_sys_status_cbs_file where f.kkslm_filename == FileName select f; if (query.Any() == true) { ret = query.Take(1).Where(x => x.kkslm_process_status == AppConstant.Fail).FirstOrDefault(); } else { ret = new kkslm_ext_sys_status_cbs_file(); } } catch (Exception ex) { throw ex; } return(ret); }
private List <RenewInsuranceViewData> RenewInsuranceDataList() { try { SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); string sql = @"SELECT lead.slm_ticketId AS TicketId, lead.slm_CampaignId AS CampaignId, lead.slm_Product_Id AS ProductId , title.slm_TitleName AS TitleName, lead.slm_Name AS FirstName, lead.slm_LastName AS LastName, lead.slm_TelNo_1 AS TelNo1 , ct.slm_CardTypeName AS CardTypeName, cus.slm_CitizenId AS CitizenId, cus.slm_Birthdate AS BirthDate, cus.slm_MaritalStatus AS MaritalStatus , occ.slm_OccupationNameTH AS OccupationName, reins.slm_ContractNo AS ContractNo, reins.slm_PolicyNo AS PolicyNo, reins.slm_PolicyStartCoverDate AS PolicyStartDate , reins.slm_PolicyEndCoverDate AS PolicyEndDate, reins.slm_PolicyCost AS PolicyCost, reins.slm_PolicyGrossPremium AS PolicyGrossPremium, reins.slm_PolicyDiscountAmt AS PolicyDiscount , reins.slm_ActNo AS ActNo, reins.slm_ActStartCoverDate AS ActStartDate, reins.slm_ActEndCoverDate AS ActEndDate , reins.slm_ActGrossPremium AS ActGrossPremium, reins.slm_ActVat AS ActVat, reins.slm_ActStamp AS ActStamp, reins.slm_ActNetPremium AS ActNetPremium FROM " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_cusinfo cus ON cus.slm_TicketId = lead.slm_ticketId LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_title title ON title.slm_TitleId = lead.slm_TitleId LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_cardtype ct ON ct.slm_CardTypeId = cus.slm_CardType LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_occupation occ ON cus.slm_Occupation = occ.slm_OccupationId INNER JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_renewinsurance reins ON reins.slm_TicketId = lead.slm_ticketId --inner เอาเฉพาะ renew insurance WHERE lead.is_Deleted = 0 "; return(slmdb.ExecuteStoreQuery <RenewInsuranceViewData>(sql).ToList()); } catch (Exception ex) { throw ex; } }
/// <summary> /// GetPositionList Flag 1=Active Branch, 2=Inactive Branch, 3=All /// </summary> /// <param name="flag"></param> /// <returns></returns> public static List <ControlListData> GetPositionList(int flag) { List <ControlListData> list = null; SLMDBEntities slmdb = new SLMDBEntities(); if (flag == COCConstant.Position.Active) { list = slmdb.kkslm_ms_position.Where(p => p.is_Deleted == false).OrderBy(p => p.slm_PositionNameTH).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_PositionNameTH, ValueField = p.slm_Position_id.ToString() }).ToList(); } else if (flag == COCConstant.Position.InActive) { list = slmdb.kkslm_ms_position.Where(p => p.is_Deleted == true).OrderBy(p => p.slm_PositionNameTH).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_PositionNameTH, ValueField = p.slm_Position_id.ToString() }).ToList(); } else if (flag == COCConstant.Position.All) { list = slmdb.kkslm_ms_position.OrderBy(p => p.slm_PositionNameTH).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_PositionNameTH, ValueField = p.slm_Position_id.ToString() }).ToList(); } else { list = new List <ControlListData>(); } return(list); }
public static List <UserMonitoringData> GetUserMonotoringStaffList(string available, string cocTeam, string teamList) { //แก้ไข Position แล้ว SLMDBEntities slmdb = new SLMDBEntities(); string sql = @"SELECT staff.coc_Team AS Team , CASE WHEN pos.slm_PositionNameAbb IS NULL THEN staff.slm_StaffNameTH ELSE pos.slm_PositionNameAbb + ' - ' + staff.slm_StaffNameTH END AS StaffFullname , staff.slm_IsActive AS Active, staff.slm_EmpCode AS EmpCode , 0 AS AmountNewJobNew, 0 AS AmountNewJobOnHand, 0 AS AmountNewJobAll , 0 AS AmountDoneJobForward, 0 AS AmountDoneJobRouteBackCoc, 0 AS AmountDoneJobRouteBackMkt, 0 AS AmountDoneJobAll, 0 AS AmountAllJob FROM " + SLMDBName + @".dbo.kkslm_ms_staff staff LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_position pos ON staff.slm_Position_id = pos.slm_Position_id WHERE staff.is_Deleted = 0 AND staff.coc_Team IS NOT NUll AND RTRIM(LTRIM(staff.coc_Team)) <> '' AND staff.coc_Team <> 'MARKETING' AND staff.coc_Team IN (" + teamList + ") "; string whr = ""; whr += (available == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_IsActive = '" + available + "' "); whr += (cocTeam == "" ? "" : (whr == "" ? "" : " AND ") + " staff.coc_Team = '" + cocTeam + "' "); sql += (whr == "" ? "" : " AND " + whr); sql += " ORDER BY staff.coc_Team, staff.slm_StaffNameTH "; return(slmdb.ExecuteStoreQuery <UserMonitoringData>(sql).ToList()); }
public static List <ControlListData> GetChannelList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_channel.Where(p => p.is_Deleted == 0).OrderBy(p => p.slm_ChannelDesc).Select(p => new ControlListData { TextField = p.slm_ChannelDesc, ValueField = p.slm_ChannelId }).ToList()); }
public static List <ControlListData> GetScreenList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_screen.Where(p => p.slm_System == "COC").OrderBy(p => p.slm_Description).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_Description, ValueField = p.slm_ScreenId.ToString() }).ToList()); }
public static List <ControlListData> GetCocSubStatusList(string optionType, string cocStatusCode) { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_option.Where(p => p.slm_OptionType == optionType && p.is_Deleted == 0 && p.slm_OptionCode == cocStatusCode && p.slm_OptionSubCode != null).OrderBy(p => p.slm_Seq).Select(p => new ControlListData { TextField = p.slm_OptionDesc, ValueField = p.slm_OptionSubCode }).ToList()); }
public static List <ControlListData> GetWebserviceList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkcoc_ms_ws.Where(p => p.coc_WebserviceSystem == "COC").OrderBy(p => p.coc_Seq).Select(p => new ControlListData { TextField = p.coc_WebserviceName, ValueField = p.coc_WebserviceName }).ToList()); }
public static CampaignWSData GetCampaignDesc(string campaignId) { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_campaign.Where(p => p.slm_CampaignId == campaignId).Select(p => new CampaignWSData { CampaignName = p.slm_CampaignName, CampaignDetail = p.slm_Offer + " : " + p.slm_criteria }).FirstOrDefault()); }
public static List <ControlListData> GetDepartmentList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_department.Where(p => p.is_Deleted == 0).OrderBy(p => p.slm_DepartmentName).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_DepartmentName, ValueField = p.slm_DepartmentId.ToString() }).ToList()); }
public static List <ControlListData> GetBranchList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_branch.OrderBy(p => p.slm_BranchName).Select(p => new ControlListData { TextField = p.slm_BranchName, ValueField = p.slm_BranchCode }).ToList()); }
public static List <StaffData> GetStaffList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_staff.Where(p => p.is_Deleted == 0).Select(p => new StaffData { UserName = p.slm_UserName, StaffId = p.slm_StaffId, HeadStaffId = p.slm_HeadStaffId, EmpCode = p.slm_EmpCode, StaffTypeId = p.slm_StaffTypeId, CocTeam = p.coc_Team }).ToList()); }
public static List <ControlListData> GetOptionList(string optionType) { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkslm_ms_option.Where(p => p.slm_OptionType == optionType && p.is_Deleted == 0 && (p.slm_SystemView == null || p.slm_SystemView == "COC")).OrderBy(p => p.slm_Seq).Select(p => new ControlListData { TextField = p.slm_OptionDesc, ValueField = p.slm_OptionCode }).ToList()); }
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); } }
private List <ExtSystemCurrentStatusData> GetExtSystemCurrentStatusViewList() { try { DateTime batchDate = GetBatchRunningDate(); SLMDBEntities slmdb = AppUtil.GetSlmDbEntities(); int numOfDay = AppConstant.HPAOLNumOfDay - 1; DateTime startDate = new DateTime(); if (numOfDay > 0) { startDate = batchDate.AddDays(-numOfDay); } else { startDate = batchDate; } string end_date = batchDate.Year.ToString() + batchDate.ToString("-MM-dd"); string start_date = startDate.Year.ToString() + startDate.ToString("-MM-dd"); // string sql = @"SELECT Ticket_Id AS TicketId, Campaign_Id AS CampaignId, Status_System AS StatusSystem, [Status] AS StatusCode, SubStatus AS SubStatusCode // , Status_Name AS StatusName, Status_By AS StatusBy, Status_Date AS StatusDate // FROM " + AppConstant.SLMDBName + @".dbo.V_EXT_System_Current_Status // WHERE CONVERT(DATE, Status_Date) BETWEEN '" + start_date + "' AND '" + end_date + @"' // AND Ticket_Id IS NOT NULL"; //string sql = @"SELECT kkslm_ext_TicketId AS TicketId, kkslm_ext_CampaignId AS CampaignId, kkslm_ext_SystemName AS StatusSystem, kkslm_ext_Status AS StatusCode, kkslm_ext_SubStatus AS SubStatusCode // , kkslm_ext_StatusName AS StatusName, kkslm_ext_UpdatedBy AS StatusBy, kkslm_ext_UpdatedDate AS StatusDate, kkslm_ext_StatusDesc AS StatusDesc // FROM " + AppConstant.SLMDBName + @".dbo.v_kkslm_ext_sys_status // WHERE CONVERT(DATE, kkslm_ext_UpdatedDate) BETWEEN '" + start_date + "' AND '" + end_date + @"' // AND kkslm_ext_TicketId IS NOT NULL"; string sql = @"SELECT kkslm_ext_TicketId AS TicketId, kkslm_ext_CampaignId AS CampaignId, kkslm_ext_SystemName AS StatusSystem, kkslm_ext_Status AS StatusCode, kkslm_ext_SubStatus AS SubStatusCode , kkslm_ext_StatusName AS StatusName, kkslm_ext_UpdatedBy AS StatusBy, kkslm_ext_UpdatedDate AS StatusDate, REPLACE(REPLACE(kkslm_ext_StatusDesc, CHAR(13), ''), CHAR(10), '') AS StatusDesc , cam.slm_CampaignName AS Campaign, cha.slm_ChannelDesc AS Channel , opt.slm_OptionDesc AS SlmStatus, lead.slm_Name AS Name, cus.slm_LastName AS LastName FROM " + AppConstant.SLMDBName + @".dbo.v_kkslm_ext_sys_status LEFT join " + AppConstant.SLMDBName + @".dbo.kkslm_tr_lead lead ON kkslm_ext_TicketId = lead.slm_ticketId LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_campaign cam ON lead.slm_CampaignId = cam.slm_CampaignId LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_mapping_status map ON kkslm_ext_Status = map.slm_Mapping_Status_Code AND isnull(kkslm_ext_SubStatus,'zz') = isnull(map.slm_Mapping_SubStatus_Code,'zz') AND map.slm_Product_Id = lead.slm_Product_Id LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_option opt ON map.slm_Status_Code = opt.slm_OptionCode AND opt.slm_OptionType = 'lead status' LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_tr_cusinfo cus ON kkslm_ext_TicketId = cus.slm_TicketId LEFT JOIN " + AppConstant.SLMDBName + @".dbo.kkslm_ms_channel cha ON lead.slm_ChannelId = cha.slm_ChannelId WHERE CONVERT(DATE, kkslm_ext_UpdatedDate) BETWEEN '" + start_date + "' AND '" + end_date + @"' AND kkslm_ext_TicketId IS NOT NULL"; return(slmdb.ExecuteStoreQuery <ExtSystemCurrentStatusData>(sql).ToList()); } catch (Exception ex) { throw ex; } }
public static List <ControlListData> GetStaffTyeList() { SLMDBEntities slmdb = new SLMDBEntities(); decimal[] dec = { COCConstant.StaffType.ITAdministrator }; return(slmdb.kkslm_ms_staff_type.Where(p => p.is_Deleted == 0 && dec.Contains(p.slm_StaffTypeId) == false).OrderBy(p => p.slm_StaffTypeDesc).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_StaffTypeDesc, ValueField = p.slm_StaffTypeId.ToString() }).ToList()); }
private void UpdatePreleadSMSFlag(SLMDBEntities slmdb, decimal preleadId, bool flag) { string sql = string.Format("UPDATE kkslm_tr_prelead SET {0} = {1} WHERE slm_Prelead_Id = {2}" , (BatchCode == "OBT_PRO_32" ? "slm_IsSMS70" : "slm_IsSMS8") , (flag ? "1" : "0") , preleadId.ToString()); slmdb.ExecuteStoreCommand(sql); }
public static List <ControlListData> GetTeamList() { SLMDBEntities slmdb = new SLMDBEntities(); return(slmdb.kkcoc_ms_team.Where(p => p.is_Deleted == 0 && p.is_ViewMonitoring == true).OrderBy(p => p.coc_TeamId).Select(p => new ControlListData { TextField = p.coc_TeamId, ValueField = p.coc_TeamId }).ToList()); //return slmdb.kkslm_ms_staff.Where(p => p.is_Deleted == 0 && p.coc_Team != null && p.coc_Team != "").Select(p => new ControlListData { TextField = p.coc_Team, ValueField = p.coc_Team }).Distinct().ToList(); }
public static StaffData GetStaff(string username) { SLMDBEntities slmdb = new SLMDBEntities(); string sql = @"SELECT staff.slm_EmpCode AS EmpCode, staff.slm_StaffTypeId AS StaffTypeId, stafftype.slm_StaffTypeDesc AS StaffTypeDesc FROM " + SLMDBName + @".dbo.kkslm_ms_staff staff LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff_type stafftype ON staff.slm_StaffTypeId = stafftype.slm_StaffTypeId WHERE staff.slm_UserName = '******'"; return(slmdb.ExecuteStoreQuery <StaffData>(sql).FirstOrDefault()); }
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); } }
public static void SetCurrentStatus(string username, int status) { SLMDBEntities slmdb = new SLMDBEntities(); var staff = slmdb.kkslm_ms_staff.Where(p => p.slm_UserName == username && (p.slm_StaffTypeId == COCConstant.StaffType.Supervisor || p.slm_StaffTypeId == COCConstant.StaffType.Telesales) && p.is_Deleted == 0).FirstOrDefault(); if (staff != null) { staff.slm_IsActive = status; slmdb.SaveChanges(); } }
public static StaffData GetStaffInfo(string username) { SLMDBEntities slmdb = new SLMDBEntities(); string sql = @"SELECT staff.slm_UserName AS UserName, staff.slm_StaffNameTH AS StaffNameTH, chan.slm_ChannelId AS ChannelId, branch.slm_BranchName AS BranchName,chan.slm_ChannelDesc AS ChannelDesc FROM " + SLMDBName + @".dbo.kkslm_ms_staff staff LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff_type st ON staff.slm_StaffTypeId = st.slm_StaffTypeId AND st.is_Deleted = 0 LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_branch branch ON staff.slm_BranchCode = branch.slm_BranchCode LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_channel chan ON st.slm_ChannelId = chan.slm_ChannelId WHERE staff.slm_UserName = '******' AND staff.is_Deleted = 0 "; return(slmdb.ExecuteStoreQuery <StaffData>(sql).FirstOrDefault()); }