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