//Reference : SlmScr003Biz,RoleBiz
public List <SearchRankingResult> SearchRankingData()
{
string sql = "";
sql = @" SELECT [coc_RankingId],[coc_Name],[coc_Seq],[coc_SkipDate],[coc_IsAllDealer],[Is_Delete],isnull(sc.[slm_PositionName] + '-','') + isnull (sc.[slm_StaffNameTH],'')
[coc_CreatedBy],[coc_CreatedDate],isnull (su.[slm_PositionName] + '-','') + isnull (su.[slm_StaffNameTH],'') [coc_UpdatedBy],[coc_UpdatedDate]
from kkcoc_tr_ranking r
left join kkslm_ms_staff sc on r.coc_CreatedBy=sc.slm_UserName
left join kkslm_ms_staff su on r.coc_UpdatedBy=su.slm_UserName";
sql += " where Is_Delete is null or Is_Delete = 0 ";
sql += " ORDER BY coc_seq";
return(slmdb.ExecuteStoreQuery <SearchRankingResult>(sql).ToList());
}
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;
}
}
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 List <RankingDealerData> GetRankingDealerList(string rankingId)
{
try
{
string sql = @"SELECT *
FROM kkcoc_tr_ranking_dealer rc
WHERE rc.coc_RankingId = '" + rankingId + @"'
ORDER BY rc.coc_DealerCode, rc.coc_DealerName ";
return(slmdb.ExecuteStoreQuery <RankingDealerData>(sql).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
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;
}
}
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());
}
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 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 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());
}
public static ScreenPrivilegeData GetScreenPrivilege(string username, string screenDesc)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT V.slm_StaffTypeId AS StaffTypeId, V.is_Save AS IsSave, V.is_View AS IsView, S.slm_ScreenDesc AS ScreenDesc
FROM " + SLMDBName + @".dbo.kkslm_ms_validate V
INNER JOIN " + SLMDBName + @".dbo.kkslm_screen S ON V.slm_ScreenId = S.slm_ScreenId
INNER JOIN " + SLMDBName + @".dbo.kkslm_ms_staff staff ON staff.slm_StaffTypeId = V.slm_StaffTypeId
WHERE V.is_Deleted = 0 AND S.is_Deleted = 0
AND staff.slm_UserName = '******' AND S.slm_ScreenDesc = '" + screenDesc + "'";
return(slmdb.ExecuteStoreQuery <ScreenPrivilegeData>(sql).FirstOrDefault());
}
public static List <FlowLogData> GetFlowLogList(DateTime action_date)
{
SLMDBEntities slmdb = new SLMDBEntities();
string actionDate = action_date.Year.ToString() + action_date.ToString("-MM-dd");
string sql = @"SELECT flowlog.coc_FlowLogId AS FlowLogId, flowlog.coc_TicketId AS TicketId, flowlog.coc_TeamFrom AS TeamFrom, flowlog.coc_TeamTo AS TeamTo
, flowlog.coc_FlowType AS FlowType, flowlog.coc_ActionDate AS ActionDate, flowlog.coc_EmpCodeTeamFrom AS TeamFromEmpCode
FROM " + SLMDBName + @".dbo.kkcoc_tr_flowlog flowlog
INNER JOIN " + SLMDBName + @".dbo.kkslm_tr_lead lead ON lead.slm_ticketId = flowlog.coc_TicketId AND lead.is_Deleted = '0'
WHERE CONVERT(DATE, flowlog.coc_ActionDate) = '" + actionDate + @"'
ORDER BY flowlog.coc_TicketId, flowlog.coc_ActionDate ";
return(slmdb.ExecuteStoreQuery <FlowLogData>(sql).ToList());
}
private List <InsuranceCompanyData> GetInsuranceCompany(SLMDBEntities slmdb)
{
try
{
string sql = @"SELECT slm_Ins_Com_Id AS InsComId, slm_InsCode AS InsComCode, slm_InsNameTh AS InsNameTH, slm_TelContact AS TelContact
FROM " + AppConstant.OPERDBName + ".dbo.kkslm_ms_ins_com";
return(slmdb.ExecuteStoreQuery <InsuranceCompanyData>(sql).ToList());
}
catch
{
//Console.WriteLine(ex);
throw;
}
}
public static List <ForecastReportData> GetDataForForecastReport(string teamList)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT team.coc_TeamId AS Team, ISNULL(team.coc_SLA_Minute_Forcast, 0) AS Sla, ISNULL(A.AmountAvailable, 0) AS AmountOfAvailableStaff
FROM " + SLMDBName + @".dbo.kkcoc_ms_team team
LEFT JOIN(
SELECT coc_Team AS Team, COUNT(*) AS AmountAvailable
FROM " + SLMDBName + @".dbo.kkslm_ms_staff
WHERE is_Deleted = 0 AND slm_IsActive = '1' AND coc_Team IS NOT NULL
GROUP BY coc_Team) A ON team.coc_TeamId = A.Team
WHERE team.is_Deleted = 0 AND team.is_ViewMonitoring = 1
AND team.coc_TeamId IN (" + teamList + @")
ORDER BY coc_Seq";
return(slmdb.ExecuteStoreQuery <ForecastReportData>(sql).ToList());
}
public List <CampaignMasterData> GetCampaignMasterList()
{
try
{
string sql = @"select * from kkslm_ms_campaign_master";
string whr = @"Is_Deleted = 0";
sql += (whr == "" ? "" : " WHERE " + whr);
//sql += " ORDER BY seq";
return(slmdb.ExecuteStoreQuery <CampaignMasterData>(sql).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
public static List <UserRoleMatrixData> GetUserRoleMatrixList(string screenId)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT CONVERT(varchar, ROW_NUMBER() OVER(ORDER BY slm_StaffTypeDesc ASC)) AS CNT,
MT.slm_StaffTypeDesc AS StaffTypeName ,CONVERT(varchar, Z.is_View) AS isView
,convert(varchar,Z.slm_ScreenId) AS ScreenId
,convert(varchar,Z.slm_ValidateId) AS ValidateId
,convert(varchar,MT.slm_StaffTypeId) AS StaffTypeId
FROM kkslm_ms_staff_type MT LEFT JOIN
(SELECT MV.slm_StaffTypeId,MV.is_View,mv.slm_ScreenId ,sc.slm_Description ,MV.slm_ValidateId
FROM kkslm_ms_validate MV INNER JOIN kkslm_screen SC ON SC.slm_ScreenId = MV.slm_ScreenId
WHERE SC.slm_System = 'COC' AND SC.slm_ScreenId = '" + screenId + @"') AS Z ON Z.slm_StaffTypeId = MT.slm_StaffTypeId
ORDER BY MT.slm_StaffTypeDesc " ;
return(slmdb.ExecuteStoreQuery <UserRoleMatrixData>(sql).ToList());
}
public decimal CalculateTotalPaidAmount(SLMDBEntities slmdb, string ticketId, string paymentCode)
{
try
{
string sql = @"SELECT SUM(detail.slm_RecAmount) AS TotalAmount
FROM dbo.kkslm_tr_renewinsurance_receipt receipt
INNER JOIN dbo.kkslm_tr_renewinsurance_receipt_detail detail ON receipt.slm_RenewInsuranceReceiptId = detail.slm_RenewInsuranceReceiptId
WHERE receipt.slm_ticketId = '" + ticketId + @"' AND detail.slm_PaymentCode = '" + paymentCode + @"' AND detail.is_Deleted = 0 ";
var amount = slmdb.ExecuteStoreQuery <decimal?>(sql).FirstOrDefault();
return(amount != null ? amount.Value : 0);
}
catch
{
throw;
}
}
public static List <ControlListData> GetStaffHeadList(string branchCode)
{
//SLMDBEntities slmdb = new SLMDBEntities();
//decimal[] dec = { COCConstant.StaffType.ITAdministrator };
//return slmdb.kkslm_ms_staff.Where(p => p.slm_BranchCode == branchCode && p.is_Deleted == 0 && dec.Contains(p.slm_StaffTypeId) == false).OrderBy(p => p.slm_StaffNameTH).AsEnumerable().Select(p => new ControlListData { TextField = p.slm_PositionName + " - " + p.slm_StaffNameTH, ValueField = p.slm_StaffId.ToString() }).ToList();
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT CASE WHEN pos.slm_PositionNameAbb IS NULL THEN staff.slm_StaffNameTH
ELSE pos.slm_PositionNameAbb + ' - ' + staff.slm_StaffNameTH END AS TextField, CONVERT(VARCHAR, staff.slm_StaffId) AS ValueField
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.slm_BranchCode = '" + branchCode + @"' AND staff.is_Deleted = '0'
AND staff.slm_StaffTypeId <> '" + COCConstant.StaffType.ITAdministrator + @"'
ORDER BY staff.slm_StaffNameTH ";
return(slmdb.ExecuteStoreQuery <ControlListData>(sql).ToList());
}
public static List <ControlListData> GetStaffListByStaffTypeId(decimal staffTypeId, string recursiveList)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT CASE WHEN pos.slm_PositionNameAbb IS NULL THEN staff.slm_StaffNameTH
ELSE pos.slm_PositionNameAbb + ' - ' + staff.slm_StaffNameTH END AS TextField, staff.slm_EmpCode AS ValueField
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.slm_StaffTypeId = '" + staffTypeId + "' AND staff.is_Deleted = 0 ";
if (recursiveList != "")
{
sql += " AND staff.slm_EmpCode IN (" + recursiveList + ") ";
}
sql += " ORDER BY staff.slm_StaffNameTH ";
return(slmdb.ExecuteStoreQuery <ControlListData>(sql).ToList());
}
public static List <AppInfoData> GetSnapWaitDetailList(DateTime assignDateFrom, DateTime assignDateTo, string teamList)
{
string datefrom = assignDateFrom.Year.ToString() + assignDateFrom.ToString("-MM-dd");
string dateto = assignDateTo.Year.ToString() + assignDateTo.ToString("-MM-dd");
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT coc_Team AS Team, SUM(coc_Pool_New_Job) AS AppInPoolNewJob, SUM(coc_Pool_Old_Job) AS AppInPoolOldJob, SUM(coc_Pool_Total) AS AppInPoolAllJob
, SUM(coc_Wait_Receive_New_Job) AS AppWaitAssignNewJob, SUM(coc_Wait_Receive_Old_Job) AS AppWaitAssignOldJob, SUM(coc_Wait_Receive_Total) AS AppWaitAssignAllJob
, SUM(coc_Receive_New_Job) AS AppAssignedNewJob, SUM(coc_Receive_Old_Job) AS AppAssignedOldJob, SUM(coc_Receive_Total) AS AppAssignedAllJob
, SUM(coc_WaitApp_Total) AS AppTotalAllJob
FROM " + SLMDBName + @".dbo.kkcoc_tr_snap_waitapp_detail
WHERE CONVERT(DATE, coc_Date) BETWEEN '" + datefrom + "' AND '" + dateto + @"'
AND coc_Team IN (" + teamList + @")
GROUP BY coc_Team
ORDER BY coc_Team ";
return(slmdb.ExecuteStoreQuery <AppInfoData>(sql).ToList());
}
public static List <UserMonitoringSnapData> GetSnapUserMonitoringList(DateTime assignDateFrom, DateTime assignDateTo, string teamList)
{
string datefrom = assignDateFrom.Year.ToString() + assignDateFrom.ToString("-MM-dd");
string dateto = assignDateTo.Year.ToString() + assignDateTo.ToString("-MM-dd");
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT coc_Team AS Team, coc_EmpName AS StaffFullname
, SUM(coc_OnHand_New_Job) AS AmountNewJobNew, SUM(coc_OnHand_Current) AS AmountNewJobOnHand, SUM(coc_OnHand_Total) AS AmountNewJobAll
, SUM(coc_Finish_Forward) AS AmountDoneJobForward, SUM(coc_Finish_RouteBack_COC) AS AmountDoneJobRouteBackCoc, SUM(coc_Finish_RouteBack_MKT) AS AmountDoneJobRouteBackMkt
, SUM(coc_Finish_Total) AS AmountDoneJobAll, SUM(coc_Total) AS AmountAllJob
, SUM(coc_Working_Sec) AS WorkingSec, '0.00' AS AvgSuccessPerHour, '0.00' AS AvgTotalPerHour
FROM " + SLMDBName + @".dbo.kkcoc_tr_snap_monitoring_detail
WHERE CONVERT(DATE, coc_Date) BETWEEN '" + datefrom + "' AND '" + dateto + @"'
AND coc_Team IN (" + teamList + @")
GROUP BY coc_Team, coc_EmpName
ORDER BY coc_Team, coc_EmpName ";
return(slmdb.ExecuteStoreQuery <UserMonitoringSnapData>(sql).ToList());
}
public static List <ControlListData> GetStaffByTeam(string cocTeam)
{
//SLMDBEntities slmdb = new SLMDBEntities();
//if (!string.IsNullOrEmpty(cocTeam))
// return slmdb.kkslm_ms_staff.Where(p => p.coc_Team == cocTeam && p.is_Deleted == 0).OrderBy(p => p.slm_StaffNameTH).Select(p => new ControlListData { TextField = p.slm_PositionName + " - " + p.slm_StaffNameTH, ValueField = p.slm_EmpCode }).ToList();
//else
// return new List<ControlListData>();
//แก้ไข Position แล้ว
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT CASE WHEN pos.slm_PositionNameAbb IS NULL THEN staff.slm_StaffNameTH
ELSE pos.slm_PositionNameAbb + ' - ' + staff.slm_StaffNameTH END AS TextField, staff.slm_EmpCode AS ValueField
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.coc_Team = '" + cocTeam + @"' AND staff.is_Deleted = '0'
ORDER BY staff.slm_StaffNameTH ";
return(slmdb.ExecuteStoreQuery <ControlListData>(sql).ToList());
}
private static OwnerDelegateEmailData GetOwnerOrDelegateEmail(SLMDBEntities slmdb, string ticketId)
{
try
{
string sql = @"SELECT LEAD.slm_Owner AS Owner, STAFF.slm_StaffEmail AS OwnerEmail, LEAD.slm_Delegate AS Delegate, STAFF2.slm_StaffEmail AS DelegateEmail
, STAFF3.slm_UserName AS MarketingOwner, STAFF3.slm_StaffEmail AS MarketingOwnerEmail
, STAFF4.slm_UserName AS LastOwner, STAFF4.slm_StaffEmail AS LastOwnerEmail
FROM " + SLMDBName + @".dbo.kkslm_tr_lead LEAD
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff STAFF ON LEAD.slm_Owner = STAFF.slm_UserName AND STAFF.is_Deleted = 0
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff STAFF2 ON LEAD.slm_Delegate = STAFF2.slm_UserName AND STAFF2.is_Deleted = 0
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff STAFF3 ON LEAD.coc_MarketingOwner = STAFF3.slm_EmpCode AND STAFF3.is_Deleted = 0
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff STAFF4 ON LEAD.coc_LastOwner = STAFF4.slm_EmpCode AND STAFF4.is_Deleted = 0
WHERE LEAD.slm_ticketId = '" + ticketId + "' AND LEAD.is_Deleted = 0";
return(slmdb.ExecuteStoreQuery <OwnerDelegateEmailData>(sql).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
}
public static List <MonitoringWSData> GetWSLog(DateTime datefrom, DateTime dateto, string wsname, string status)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT coc_OperationDate AS OperationDate
,coc_Operation AS WSName
,coc_ChannelId AS Channel
,coc_TicketId AS TicketId
,coc_ResponseCode AS ResponseCode
,coc_ResponseDesc AS ResponseDesc
,coc_ResponseDate AS ResponseDate
,coc_ResponseTime AS ResponseTime
,coc_CauseError AS CauseError
FROM " + SLMDBName + @".dbo.kkcoc_ws_log ";
string whr = "";
string whrDateFrom = datefrom.Year != 1 ? datefrom.Year + datefrom.ToString("-MM-dd") : string.Empty;
string whrDateTo = dateto.Year != 1 ? dateto.Year + dateto.ToString("-MM-dd") : string.Empty;
whr += (whrDateFrom == "" || whrDateTo == "" ? "" : (whr == "" ? "" : " AND ") + " CONVERT(DATE, coc_OperationDate) Between '" + whrDateFrom + "' and '" + whrDateTo + "' ");
if (wsname != "")
{
whr += (string.IsNullOrEmpty(wsname) ? "" : (whr == "" ? "" : " AND ") + " UPPER(coc_Operation) = '" + wsname.ToUpper() + "' ");
}
if (status == "SUCCESS")
{
whr += (whr == "" ? "" : " AND ") + " UPPER(coc_ResponseDesc) = '" + status.ToUpper() + "' ";
}
else if (status == "NOTSUCCESS")
{
whr += (whr == "" ? "" : " AND ") + " UPPER(coc_ResponseDesc) <> 'SUCCESS' ";
}
sql += (whr == "" ? "" : " WHERE " + whr);
sql += " ORDER BY coc_OperationDate DESC";
return(slmdb.ExecuteStoreQuery <MonitoringWSData>(sql).ToList());
}
public static List <StaffDataManagement> SearchStaffList(string username, string branchCode, string empCode, string marketingCode, string staffNameTH, string positionId, string staffTypeId, string team, string departmentId, string cocTeam)
{
SLMDBEntities slmdb = new SLMDBEntities();
string sql = @"SELECT staff.slm_StaffId AS StaffId, staff.slm_EmpCode AS EmpCode, staff.slm_MarketingCode AS MarketingCode, staff.slm_UserName AS Username, staff.slm_StaffNameTH AS StaffNameTH
, pos.slm_PositionNameTH AS PositionName, staff.slm_StaffTypeId AS StaffTypeId, st.slm_StaffTypeDesc AS StaffTypeDesc, staff.slm_Team AS Team, staff.slm_BranchCode AS BranchCode, branch.slm_BranchName AS BranchName
, staff.slm_DepartmentId AS DepartmentId, dep.slm_DepartmentName AS DepartmentName, staff.is_Deleted AS Is_Deleted,staff.slm_UpdateStatusDate AS UpdateStatusDate
FROM " + SLMDBName + @".dbo.kkslm_ms_staff staff
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff_type st ON staff.slm_StaffTypeId = st.slm_StaffTypeId
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_branch branch ON staff.slm_BranchCode = branch.slm_BranchCode
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_department dep ON staff.slm_DepartmentId = dep.slm_DepartmentId
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_position pos ON staff.slm_Position_id = pos.slm_Position_id ";
string whr = "";
whr += (username == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_UserName LIKE @UserName ");
whr += (branchCode == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_BranchCode = '" + branchCode + "' ");
whr += (empCode == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_EmpCode LIKE @EmpCode ");
whr += (marketingCode == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_MarketingCode LIKE @MarketingCode ");
whr += (staffNameTH == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_StaffNameTH LIKE @FullName ");
whr += (positionId == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_Position_id = '" + positionId + "' ");
whr += (staffTypeId == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_StaffTypeId = '" + staffTypeId + "' ");
whr += (team == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_Team LIKE @Team ");
whr += (departmentId == "" ? "" : (whr == "" ? "" : " AND ") + " staff.slm_DepartmentId = '" + departmentId + "' ");
whr += (cocTeam == "" ? "" : (whr == "" ? "" : " AND ") + " staff.coc_Team = '" + cocTeam + "' ");
sql += (whr == "" ? "" : " WHERE " + whr);
sql += " ORDER BY staff.slm_EmpCode ";
object[] param = new object[]
{
new SqlParameter("@UserName", (username != null ? "%" + username + "%" : "")),
new SqlParameter("@EmpCode", (empCode != null ? "%" + empCode + "%" : "")),
new SqlParameter("@MarketingCode", (marketingCode != null ? "%" + marketingCode + "%" : "")),
new SqlParameter("@FullName", (staffNameTH != null ? "%" + staffNameTH + "%" : "")),
new SqlParameter("@Team", (team != null ? "%" + team + "%" : ""))
};
return(slmdb.ExecuteStoreQuery <StaffDataManagement>(sql, param).ToList());
}
//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);
}
}
private static string GetEmailTemplate(SLMDBEntities slmdb, string ticketId, string noteDetail)
{
try
{
string template = "";
string filePath = System.Configuration.ConfigurationManager.AppSettings["EmailTemplatePath"];
if (filePath == null)
{
throw new Exception("ไม่พบ Config EmailTemplatePath ใน Configuration File");
}
string sql = @"SELECT lead.slm_ticketId AS TicketId, cam.slm_CampaignName AS CampaignName, lead.slm_ChannelId AS Channel, lead.slm_Name AS Firstname, lead.slm_LastName AS Lastname, own.slm_StaffNameTH AS OwnerName
, lead.slm_AssignedDate AS AssignedDate, del.slm_StaffNameTH AS DelegateName, lead.slm_DelegateDate AS DelegateDate, ISNULL(cre.slm_StaffNameTH, lead.slm_CreatedBy) AS CreatedBy, lead.slm_CreatedDate AS CreatedDate
,lead.slm_AvailableTime AS AvailableTime, opt.slm_OptionDesc AS StatusDesc, lead.slm_Product_Name AS ProductName, pg.product_name AS ProductGroupName, lead.slm_TelNo_1 AS TelNo1, prod.slm_LicenseNo AS LicenseNo
,opt2.slm_OptionDesc AS CocStatusDesc, mktowner.slm_StaffNameTH AS MarketingOwnerName, lastowner.slm_StaffNameTH AS LastOwnerName
FROM " + SLMDBName + @".dbo.kkslm_tr_lead lead
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_campaign cam ON cam.slm_CampaignId = lead.slm_CampaignId
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff own ON own.slm_UserName = lead.slm_Owner
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff del ON del.slm_UserName = lead.slm_Delegate
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff cre ON cre.slm_UserName = lead.slm_CreatedBy
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_option opt ON opt.slm_OptionCode = lead.slm_Status AND opt.slm_OptionType = 'lead status'
LEFT JOIN " + SLMDBName + @".dbo.CMT_MS_PRODUCT_GROUP pg ON pg.product_id = lead.slm_Product_Group_Id
LEFT JOIN " + SLMDBName + @".dbo.kkslm_tr_productinfo prod ON prod.slm_TicketId = lead.slm_ticketId
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_option opt2 ON opt2.slm_OptionCode = lead.coc_Status AND ISNULL(opt2.slm_OptionSubCode, '0123456789') = ISNULL(lead.coc_SubStatus, '0123456789') AND opt2.slm_OptionType = 'coc_status'
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff mktowner ON mktowner.slm_EmpCode = lead.coc_MarketingOwner
LEFT JOIN " + SLMDBName + @".dbo.kkslm_ms_staff lastowner ON lastowner.slm_EmpCode = lead.coc_LastOwner
WHERE lead.slm_ticketId = '" + ticketId + "'";
var data = slmdb.ExecuteStoreQuery <EmailTemplateData>(sql).FirstOrDefault();
if (data != null)
{
template = File.ReadAllText(filePath);
template = template.Replace("%Note%", noteDetail)
.Replace("%TicketId%", ticketId)
.Replace("%Campaign%", data.CampaignName)
.Replace("%Channel%", data.Channel)
.Replace("%ProductGroupName%", data.ProductGroupName)
.Replace("%ProductName%", data.ProductName)
.Replace("%LeadStatus%", data.StatusDesc)
.Replace("%CustomerName%", data.Firstname)
.Replace("%CustomerLastName%", data.Lastname)
.Replace("%OwnerName%", data.OwnerName)
.Replace("%AssignedDate%", data.AssignedDate != null ? data.AssignedDate.Value.Year.ToString() + data.AssignedDate.Value.ToString("-MM-dd HH:mm:ss") : "")
.Replace("%DelegateName%", data.DelegateName)
.Replace("%DelegateDate%", data.DelegateDate != null ? data.DelegateDate.Value.Year.ToString() + data.DelegateDate.Value.ToString("-MM-dd HH:mm:ss") : "")
.Replace("%CreatedBy%", data.CreatedBy)
.Replace("%CreatedDate%", data.CreatedDate != null ? data.CreatedDate.Value.Year.ToString() + data.CreatedDate.Value.ToString("-MM-dd HH:mm:ss") : "")
.Replace("%TelNo1%", data.TelNo1)
.Replace("%LicenseNo%", data.LicenseNo)
.Replace("%CocStatusDesc%", data.CocStatusDesc)
.Replace("%MarketingOwnerName%", data.MarketingOwnerName)
.Replace("%LastOwnerName%", data.LastOwnerName);
if (data.AvailableTime != null && data.AvailableTime.Length == 6)
{
template = template.Replace("%AvailableTime%", data.AvailableTime.Substring(0, 2) + ":" + data.AvailableTime.Substring(2, 2) + ":" + data.AvailableTime.Substring(4, 2));
}
else
{
template = template.Replace("%AvailableTime%", "");
}
}
return(template != "" ? template : noteDetail);
}
catch (Exception ex)
{
throw ex;
}
}