public DataTable GetEmailTargetCountWithSpeciesAndBreedProvided(string hid, string speciesids, string breedIds) { BusinessLogicLayer bll = new BusinessLogicLayer(); StringBuilder sb = new StringBuilder(); if (breedIds != "'{}'") { sb.Append("select p.hid as out_hid, p.client_id as out_client_id, p.id as out_patient_id, c.first_name as out_first_name, c.last_name as out_last_name, c.email as email"); sb.Append(" from public.patient p join public.client c on c.id = p.client_id and c.hid = p.hid "); sb.Append(" where email.is_patient_active(p.hid, p.id, p.client_id) = 1"); sb.Append(" and campaign_targeting.is_patient_email_contactable(p.hid, p.id) = 1"); sb.Append(" and campaign_targeting.patient_species_check(p.hid, p.id, " + speciesids + ") = 1"); sb.Append(" and campaign_targeting.species_breed_check(p.hid, p.id, " + speciesids + " , " + breedIds + ") = 1"); sb.Append(" and p.hid = " + hid); } else { sb.Append("select p.hid as out_hid, p.client_id as out_client_id, p.id as out_patient_id, c.first_name as out_first_name, c.last_name as out_last_name, c.email as email"); sb.Append(" from public.patient p join public.client c on c.id = p.client_id and c.hid = p.hid "); sb.Append(" where email.is_patient_active(p.hid, p.id, p.client_id) = 1"); sb.Append(" and campaign_targeting.is_patient_email_contactable(p.hid, p.id) = 1"); sb.Append(" and campaign_targeting.patient_species_check(p.hid, p.id, " + speciesids + ") = 1"); sb.Append(" and p.hid = " + hid); } DataAccessLayer objDAL = new DataAccessLayer(); DataTable dt = objDAL.GenericExecution_Source(sb.ToString()); return(dt); }
public DataTable Targeting(CustomEmail3_Targeting obj) { BusinessLogicLayer bll = new BusinessLogicLayer(); string myQuery = ""; myQuery = myQuery + "select * from email.get_targated_audience"; myQuery = myQuery + "("; myQuery = myQuery + obj.in_hid + ","; myQuery = myQuery + obj.in_editor_verion_id + ","; myQuery = myQuery + obj.in_marketing_msg_id + ","; myQuery = myQuery + obj.in_species_ids + ","; myQuery = myQuery + obj.is_other_species + ","; myQuery = myQuery + obj.is_all_breed + ","; myQuery = myQuery + obj.in_breed_ids + ","; myQuery = myQuery + obj.in_is_age_in_range + ","; myQuery = myQuery + obj.in_is_specific_age + ","; myQuery = myQuery + obj.in_specific_age + ","; myQuery = myQuery + obj.in_age_range_from + ","; myQuery = myQuery + obj.in_age_range_to + ","; myQuery = myQuery + obj.in_last_service + ","; myQuery = myQuery + obj.in_last_service_from + ","; myQuery = myQuery + obj.in_last_service_to + ","; myQuery = myQuery + obj.in_included_service_ids + ","; myQuery = myQuery + obj.in_match_any_included_service + ","; myQuery = myQuery + obj.in_match_any_excluded_service + ","; myQuery = myQuery + obj.in_included_last_service + ","; myQuery = myQuery + obj.in_excluded_service_ids + ","; myQuery = myQuery + obj.in_excluded_last_service + ""; myQuery = myQuery + ")"; DataAccessLayer objDAL = new DataAccessLayer(); DataTable dt = objDAL.GenericExecution_Source(myQuery); return(dt); }
public DataTable GetActivePatients_Assert(int hid, string rundate, string interval) { List <public_lineitem> lstLineItem = _objDataGen.GenerateLineItem(); BusinessLogicLayer objBusiness = new BusinessLogicLayer(); DataTable dt = objBusiness.CreateDataTable(lstLineItem); return(dt); }
public void InsertDatatoLineItem() { TruncateTable_Destination("public.lineitem"); List <public_lineitem> lstLineItem = _objDataGen.GenerateLineItem(); BusinessLogicLayer objBusiness = new BusinessLogicLayer(); DataTable dt = objBusiness.CreateDataTable(lstLineItem); //INSERT objBusiness.InsertDatatoTable("public.lineitem", dt); }
public int Patient_Exclusion_Check(int hid, long patientid, bool matchanyservice, string excludedServiceIds, string excludedInterval) { BusinessLogicLayer bll = new BusinessLogicLayer(); int retVal = 0; string myQuery = ""; myQuery = "select * from email.get_patient_by_excluded_list(" + hid + "," + patientid + "," + matchanyservice + "," + bll.ReturnNullIfEmpty(excludedServiceIds) + "," + bll.ReturnNullIfEmpty(excludedInterval) + ")"; DataAccessLayer objDAL = new DataAccessLayer(); DataTable dt = objDAL.GenericExecution_Source(myQuery); if (dt != null && dt.Rows.Count > 0) { return(Convert.ToInt32(dt.Rows[0][0])); } return(retVal); }
public int Patient_Last_Visit_Check(int hid, long patientid, string lastvisit, string lastvisitFrom, string lastvisitTo) { BusinessLogicLayer bll = new BusinessLogicLayer(); int retVal = 0; string myQuery = ""; myQuery = "select * from email.get_patient_by_last_visit(" + hid + "," + patientid + "," + bll.ReturnNullIfEmpty(lastvisit) + "," + bll.ReturnNullIfEmpty(lastvisitFrom) + "," + bll.ReturnNullIfEmpty(lastvisitTo) + ", false)"; DataAccessLayer objDAL = new DataAccessLayer(); DataTable dt = objDAL.GenericExecution_Source(myQuery); if (dt != null && dt.Rows.Count > 0) { return(Convert.ToInt32(dt.Rows[0][0])); } return(retVal); }
public DataTable GetEmailTargetCountWithSpeciesBreedAgeAndLastVisitRange(string hid, string speciesids, string breedIds, string startDate, string endDate, string lastVisitFrom, string lastVisitTo) { BusinessLogicLayer bll = new BusinessLogicLayer(); StringBuilder sb = new StringBuilder(); sb.Append("select p.hid as out_hid, p.client_id as out_client_id, p.id as out_patient_id, c.first_name as out_first_name, c.last_name as out_last_name, c.email as email"); sb.Append(" from public.patient p join public.client c on c.id = p.client_id and c.hid = p.hid "); sb.Append(" where email.is_patient_active(p.hid, p.id, p.client_id) = 1"); sb.Append(" and campaign_targeting.is_patient_email_contactable(p.hid, p.id) = 1"); sb.Append(" and campaign_targeting.patient_species_check(p.hid, p.id, " + speciesids + ") = 1"); sb.Append(" and campaign_targeting.species_breed_check(p.hid, p.id, " + speciesids + " , " + breedIds + ") = 1"); sb.Append(" and campaign_targeting.patient_age_criteria_check(p.hid, p.id, " + startDate + ", " + endDate + ") = 1"); sb.Append(" and email.get_patient_by_last_visit(p.hid, p.id, null, " + lastVisitFrom + ", " + lastVisitTo + ", false) = 1"); sb.Append(" and p.hid = " + hid); DataAccessLayer objDAL = new DataAccessLayer(); DataTable dt = objDAL.GenericExecution_Source(sb.ToString()); return(dt); }