public ActionResult <QueryResult <CustomerLeadListItem> > SearchCustomersAndLeads(string lastname, string firstname, string customerCode, string xid, string voucherID, string phoneNumber, DateTime?birthdate, string shopCode, bool?IsLead, int pageSize = 20, int pageNumber = 0, bool SortAscending = true)
{
if (string.IsNullOrWhiteSpace(lastname) && string.IsNullOrWhiteSpace(xid) && string.IsNullOrWhiteSpace(customerCode) &&
string.IsNullOrWhiteSpace(voucherID) && string.IsNullOrWhiteSpace(phoneNumber) && !birthdate.HasValue)
{
throw new ArgumentException("At least one of the following parameters is needed to call this API: Lastname, CustomerCode, XID, VoucherID, PhoneNumber, Birthdate");
}
StringBuilder customerWhereCondition = new StringBuilder();
StringBuilder leadWhereCondition = new StringBuilder();
//Default search conditions for leads
QueryHelper.AddWhereCondition(leadWhereCondition, $"L.CUSTOMER_CODE IS NULL");
if (!string.IsNullOrWhiteSpace(lastname))
{
QueryHelper.AddWhereCondition(customerWhereCondition, $"A.LASTNAME LIKE'{lastname}%'");
QueryHelper.AddWhereCondition(leadWhereCondition, $"L.LASTNAME LIKE'{lastname}%'");
}
if (!string.IsNullOrWhiteSpace(firstname))
{
QueryHelper.AddWhereCondition(customerWhereCondition, $"A.FIRSTNAME LIKE'{firstname}%'");
QueryHelper.AddWhereCondition(leadWhereCondition, $"L.FIRSTNAME LIKE'{firstname}%'");
}
//Search from UI
if (!string.IsNullOrWhiteSpace(customerCode))
{
QueryHelper.AddWhereCondition(customerWhereCondition, $"A.CUSTOMER_CODE ='{customerCode}'");
QueryHelper.AddWhereCondition(leadWhereCondition, $"L.XID = '{customerCode}'");
//IsLead = false;
}
if (!string.IsNullOrWhiteSpace(xid))
{
QueryHelper.AddWhereCondition(leadWhereCondition, $"L.XID = '{xid}'");
IsLead = true;
}
if (!string.IsNullOrWhiteSpace(shopCode))
{
QueryHelper.AddWhereCondition(customerWhereCondition, $"AEXT.SHOP_CODE ='{shopCode}'");
IsLead = false;
}
if (!string.IsNullOrWhiteSpace(voucherID))
{
QueryHelper.AddWhereCondition(customerWhereCondition, $"V.VOUCHER_ID='{voucherID}'");
IsLead = false;
}
if (!string.IsNullOrWhiteSpace(phoneNumber))
{
QueryHelper.AddWhereCondition(customerWhereCondition, string.Format("(ADDR.PHONE1='{0}' OR ADDR.PHONE2='{0}' OR ADDR.PHONE3='{0}' OR ADDR.MOBILE='{0}')", phoneNumber));
QueryHelper.AddWhereCondition(leadWhereCondition, string.Format("(LADDR.PHONENUMBER='{0}' OR LADDR.PHONE2='{0}' OR LADDR.PHONE3='{0}' OR LADDR.MOBILENUMBER='{0}')", phoneNumber));
}
if (birthdate.HasValue)
{
QueryHelper.AddWhereCondition(customerWhereCondition, string.Format("A.BIRTHDATE='{0:yyyy-MM-dd}'", birthdate));
QueryHelper.AddWhereCondition(leadWhereCondition, string.Format("L.DATEOFBIRTH='{0:yyyy-MM-dd}'", birthdate));
}
if (IsLead.HasValue)
{
QueryHelper.AddWhereCondition(customerWhereCondition, string.Format("0={0}", IsLead.Value ? 1 : 0));
QueryHelper.AddWhereCondition(leadWhereCondition, string.Format("1={0}", IsLead.Value ? 1 : 0));
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(" SELECT A.COMPANY_CODE, A.DIVISION_CODE, AEXT.SHOP_CODE, A.CUSTOMER_CODE, NULL AS XID, FIRSTNAME, MIDDLENAME, LASTNAME, GENDER_CODE, BIRTHDATE, SALUTATION_CODE, A.STATUS_CODE, AEXT.TYPE_CODE, PREFERREDNAME, SOURCE_CODE, SUB_SOURCE_CODE, REF_SOURCE_CODE, 0 AS IS_LEAD, A.ROWGUID");
sqlCommand.Append(" FROM CU_B_ADDRESS_BOOK A");
sqlCommand.Append(" JOIN CU_B_ADDRESS ADDR ON A.COMPANY_CODE=ADDR.COMPANY_CODE AND A.DIVISION_CODE=ADDR.DIVISION_CODE AND A.CUSTOMER_CODE=ADDR.CUSTOMER_CODE");
sqlCommand.Append(" JOIN CU_B_ADDRESS_BOOK_EXT_AUS AEXT ON A.COMPANY_CODE=AEXT.COMPANY_CODE AND A.DIVISION_CODE=AEXT.DIVISION_CODE AND A.CUSTOMER_CODE=AEXT.CUSTOMER_CODE");
sqlCommand.Append(" LEFT JOIN CU_B_VOUCHER_EXT_AUS V ON A.COMPANY_CODE=V.COMPANY_CODE AND A.DIVISION_CODE=V.DIVISION_CODE AND A.CUSTOMER_CODE=V.CUSTOMER_CODE");
if (customerWhereCondition.Length > 0)
{
sqlCommand.Append(customerWhereCondition.ToString());
}
sqlCommand.Append(" UNION ");
sqlCommand.Append(" SELECT L.COMPANY_CODE, L.DIVISION_CODE, NULL AS SHOP_CODE, NULL AS CUSTOMER_CODE, L.XID, FIRSTNAME, MIDDLENAME, LASTNAME, GENDER, DATEOFBIRTH, SALUTATION, STATUS_CODE, FUNDINGTYPE, PREFERREDNAME, LEADSOURCE, LEADSUBSOURCE, REFERRALSOURCE, 1 AS IS_LEAD, L.ROWGUID");
sqlCommand.Append(" FROM CU_B_LEAD_EXT_AUS L");
sqlCommand.Append(" JOIN CU_B_LEAD_ADDRESS_EXT_AUS LADDR ON L.COMPANY_CODE=LADDR.COMPANY_CODE AND L.DIVISION_CODE=LADDR.DIVISION_CODE AND L.XID=LADDR.XID");
if (leadWhereCondition.Length > 0)
{
sqlCommand.Append(leadWhereCondition.ToString());
}
IQueryable <LEAD_CUSTOMER> qryCustomers = DBContext.LeadAndCustomers.FromSql(sqlCommand.ToString());
int RecordCount = qryCustomers.Count();
List <CustomerLeadListItem> Result = new List <CustomerLeadListItem>();
//foreach (LEAD_CUSTOMER Item in QueryHelper.GetPageItems<LEAD_CUSTOMER>(qryCustomers.Where(predicate), pageSize, pageNumber))
if (SortAscending)
{
qryCustomers = qryCustomers.OrderBy(E => E.LASTNAME).OrderBy(E => E.FIRSTNAME);
}
else
{
qryCustomers = qryCustomers.OrderByDescending(E => E.LASTNAME).OrderByDescending(E => E.FIRSTNAME);
}
foreach (LEAD_CUSTOMER Item in QueryHelper.GetPageItems <LEAD_CUSTOMER>(qryCustomers, pageSize, pageNumber).ToArray())
{
CustomerLeadListItem ResultItem = EntityMapper.Map <CustomerLeadListItem, LEAD_CUSTOMER>(DBContext, Item);
Result.Add(ResultItem);
}
return(new QueryResult <CustomerLeadListItem>(pageSize, RecordCount, pageNumber, Result));
}