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