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());
}
