public static LeadDataForCARLogService GetDataForCARLogService(string ticketId, SLMDBEntities slmdb)
{
try
{
string slmdbName = AppConstant.SLMDBName;
string sql = @"SELECT lead.slm_TicketId AS TicketId, prelead.slm_Prelead_Id AS PreleadId, lead.slm_CampaignId AS CampaignId, cam.slm_CampaignName AS CampaignName, lead.slm_ChannelId AS ChannelId, lead.slm_Product_Group_Id AS ProductGroupId, lead.slm_Product_Id AS ProductId
, lead.slm_Status AS StatusCode, opt.slm_OptionDesc AS StatusName, lead.slm_SubStatus AS SubStatusCode, lead.slm_ExternalSubStatusDesc AS SubStatusName
, cus.slm_CitizenId AS CitizenId, cus.slm_CardType AS CardTypeId, cardtype.slm_CardTypeName AS CardTypeName, pg.product_name AS ProductGroupName, mp.sub_product_name AS ProductName
, renew.slm_LicenseNo AS LicenseNo, renew.slm_ContractNo AS ContractNo, ISNULL(title.slm_TitleName, '') + ISNULL(lead.slm_Name, '') + ' ' + ISNULL(lead.slm_LastName, '') AS CustomerName
, LEAD.slm_Owner AS Owner, LEAD.slm_Delegate AS Delegate
, insurcom.slm_InsNameTh AS InsuranceCompany, renew.slm_PolicyStartCoverDate AS PolicyStartCoverDate, renew.slm_PolicyEndCoverDate AS PolicyEndCoverDate
, renew.slm_PolicyNo AS PolicyNo, cardtype.slm_CIFSubscriptTypeId AS SubScriptionTypeId, renew.slm_ActNo AS ActNo
, renew.slm_IncentiveFlag AS IncentiveFlag, renew.slm_ActIncentiveFlag AS IncentiveFlagAct, renew.slm_ReceiveNo AS ReceiveNo, renew.slm_ActSendDate AS ActSendDate
, renew.slm_ActStartCoverDate AS ActStartCoverDate, renew.slm_ActEndCoverDate AS ActEndCoverDate
FROM " + slmdbName + @".dbo.kkslm_tr_lead LEAD
LEFT JOIN " + slmdbName + @".dbo.kkslm_tr_cusinfo cus ON cus.slm_TicketId = lead.slm_ticketId
LEFT JOIN " + slmdbName + @".dbo.kkslm_tr_renewinsurance renew ON renew.slm_TicketId = lead.slm_ticketId
LEFT JOIN " + slmdbName + @".dbo.kkslm_ms_option opt ON lead.slm_Status = opt.slm_OptionCode AND opt.slm_OptionType = 'lead status' AND opt.is_Deleted = '0'
LEFT JOIN " + slmdbName + @".dbo.kkslm_ms_campaign cam ON cam.slm_CampaignId = lead.slm_CampaignId
LEFT JOIN " + slmdbName + @".dbo.kkslm_ms_cardtype cardtype ON cardtype.slm_CardTypeId = cus.slm_CardType
LEFT JOIN " + slmdbName + @".dbo.CMT_MS_PRODUCT_GROUP pg ON lead.slm_Product_Group_Id = pg.product_id
LEFT JOIN " + slmdbName + @".dbo.CMT_MAPPING_PRODUCT mp ON mp.sub_product_id = lead.slm_Product_Id
LEFT JOIN " + AppConstant.OPERDBName + @".dbo.kkslm_ms_ins_com insurcom ON insurcom.slm_Ins_Com_Id = renew.slm_InsuranceComId
LEFT JOIN " + slmdbName + @".dbo.kkslm_tr_prelead prelead ON prelead.slm_TicketId = lead.slm_TicketId
LEFT JOIN " + slmdbName + @".dbo.kkslm_ms_title title ON title.slm_TitleId = lead.slm_TitleId AND title.is_Deleted = 0
WHERE LEAD.slm_ticketId = '" + ticketId + "' ";
return(slmdb.ExecuteStoreQuery <LeadDataForCARLogService>(sql).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
}
public static List <ProvinceData> GetProvinceDataList(SLMDBEntities slmdb)
{
try
{
string sql = @"select pro.slm_ProvinceId AS ProvinceId, pro.slm_ProvinceCode AS ProvinceCode, pro.slm_ProvinceNameTH AS ProvinceName
, am.slm_AmphurId AS AmphurId, am.slm_AmphurCode AS AmphurCode, am.slm_AmphurNameTH AS AmphurName
, tam.slm_TambolId AS TambolId, tam.slm_TambolCode AS TambolCode, tam.slm_TambolNameTH AS TambolName
from " + AppConstant.SLMDBName + @".dbo.kkslm_ms_province pro
left join " + AppConstant.SLMDBName + @".dbo.kkslm_ms_amphur am on am.slm_ProvinceCode = pro.slm_ProvinceCode
left join " + AppConstant.SLMDBName + @".dbo.kkslm_ms_tambol tam on tam.slm_ProvinceCode = am.slm_ProvinceCode and tam.slm_AmphurCode = am.slm_AmphurCode
order by pro.slm_ProvinceNameTH, am.slm_AmphurNameTH, tam.slm_TambolNameTH ";
return(slmdb.ExecuteStoreQuery <ProvinceData>(sql).ToList());
}
catch (Exception ex)
{
throw ex;
}
}
