/// <summary>
/// Addres search
/// </summary>
/// <param name="name_startsWith"></param>
/// <returns></returns>
public List <LocationAddress> GetAddressesSearch(Ctx ctx, string name_startsWith)
{
List <LocationAddress> obj = new List <LocationAddress>();
//string sqlquery = " SELECT * FROM (SELECT (NVL(cn.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
// "cn.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
// " l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
// " LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID WHERE l.ISACTIVE='Y') qb1" +
// " WHERE LOWER(qb1.address) LIKE LOWER('" + name_startsWith + "%') or LOWER(qb1.address) LIKE LOWER('%" + name_startsWith + "%') or LOWER(qb1.address) LIKE LOWER('%" + name_startsWith + "')";
string sqlquery = " SELECT * FROM (SELECT (NVL(cn.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
"cn.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
" l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
" LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID WHERE l.ISACTIVE='Y') qb1";
sqlquery = MRole.GetDefault(ctx).AddAccessSQL(sqlquery, "C_Location", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
sqlquery = "SELECT * FROM (" + sqlquery + " ) fltr WHERE LOWER(fltr.address) LIKE LOWER('" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "')";
var ds = new DataSet();
ds = DB.ExecuteDataset(sqlquery);
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
LocationAddress ladd = new LocationAddress
{
ADDRESS = Convert.ToString(ds.Tables[0].Rows[i]["address"]),
COUNTRYNAME = Convert.ToString(ds.Tables[0].Rows[i]["Name"]),
ADDRESS1 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS1"]),
ADDRESS2 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS2"]),
ADDRESS3 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS3"]),
ADDRESS4 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS4"]),
CITYNAME = Convert.ToString(ds.Tables[0].Rows[i]["CITY"]),
STATENAME = Convert.ToString(ds.Tables[0].Rows[i]["REGIONNAME"]),
ZIPCODE = Convert.ToString(ds.Tables[0].Rows[i]["POSTAL"]),
AD_CLIENT_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_CLIENT_ID"]),
AD_ORG_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_ORG_ID"]),
// C_CITY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_CITY_ID"]),
C_COUNTRY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_COUNTRY_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_COUNTRY_ID"]),
C_LOCATION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_LOCATION_ID"]),
C_REGION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_REGION_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_REGION_ID"])
};
obj.Add(ladd);
}
}
ds = null;
return(obj);
}
/// <summary>
/// Addres search
/// </summary>
/// <param name="name_startsWith"></param>
/// <returns></returns>
public List <LocationAddress> GetAddressesSearch(Ctx ctx, string name_startsWith)
{
List <LocationAddress> obj = new List <LocationAddress>();
string sqlquery = "";
#region Commentedny Mohit
//if (Env.IsBaseLanguage(ctx, ""))
//{
// sqlquery = " SELECT * FROM (SELECT (NVL(cn.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
// "cn.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
// " l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
// " LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID WHERE l.ISACTIVE='Y') qb1";
//}
//else
//{
// // Check applied by mohit - Picked data from translation tab - if base language
// sqlquery = " SELECT * FROM (SELECT (NVL(CNTRL.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
// " cnTRL.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
// " l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
// " LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID INNER JOIN C_Country_Trl CNTRL ON (cn.C_Country_ID=CNTRL.C_Country_ID) WHERE l.ISACTIVE='Y' AND cnTRL.AD_Language='" + ctx.GetAD_Language() + "') qb1";
//}
#endregion
// Check applied by mohit - asked by mukesh sir - to check if login langauge is base language - then pick non translated data.
if (Env.IsBaseLanguage(ctx, ""))
{
sqlquery = " SELECT (NVL(cn.Name,'')||' '|| NVL(C_Location.ADDRESS1,'') ||' '|| NVL(C_Location.ADDRESS2,'') ||' '|| NVL(C_Location.ADDRESS3,'') ||' '|| NVL(C_Location.ADDRESS4,'') ||' '|| NVL(C_Location.CITY,'') ||' '|| NVL(C_Location.REGIONNAME,'') ||' '|| NVL(C_Location.POSTAL,'') ||' '|| NVL(C_Location.POSTAL_ADD,'')) as address," +
"cn.Name, C_Location.ADDRESS1 , C_Location.ADDRESS2 , C_Location.ADDRESS3 , C_Location.ADDRESS4 , C_Location.CITY , C_Location.REGIONNAME , C_Location.POSTAL , C_Location.POSTAL_ADD," +
" C_Location.AD_CLIENT_ID,C_Location.AD_ORG_ID,C_Location.C_CITY_ID,C_Location.C_COUNTRY_ID,C_Location.C_LOCATION_ID,C_Location.C_REGION_ID FROM C_Location C_Location" +
" LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=C_Location.C_COUNTRY_ID WHERE C_Location.ISACTIVE='Y'" +
" AND Lower( (NVL(cn.Name,'') ||' ' || NVL(C_Location.ADDRESS1,'') ||' ' || NVL(C_Location.ADDRESS2,'') ||' ' || NVL(C_Location.ADDRESS3,'') ||' ' || NVL(C_Location.ADDRESS4,'') ||' ' || NVL(C_Location.CITY,'')" +
" ||' ' || NVL(C_Location.REGIONNAME,'') ||' ' || NVL(C_Location.POSTAL,'') ||' ' || NVL(C_Location.POSTAL_ADD,''))) like LOWER ('%" + name_startsWith + "%') AND rownum < 500";
}
else
{
// Check applied by mohit - Picked data from translation tab - if base language
sqlquery = " SELECT (NVL(CNTRL.Name,'')||' '|| NVL(C_Location.ADDRESS1,'') ||' '|| NVL(C_Location.ADDRESS2,'') ||' '|| NVL(C_Location.ADDRESS3,'') ||' '|| NVL(C_Location.ADDRESS4,'') ||' '|| NVL(C_Location.CITY,'') ||' '|| NVL(C_Location.REGIONNAME,'') ||' '|| NVL(C_Location.POSTAL,'') ||' '|| NVL(C_Location.POSTAL_ADD,'')) as address," +
" cnTRL.Name, C_Location.ADDRESS1 , C_Location.ADDRESS2 , C_Location.ADDRESS3 , C_Location.ADDRESS4 , C_Location.CITY , C_Location.REGIONNAME , C_Location.POSTAL , C_Location.POSTAL_ADD," +
" C_Location.AD_CLIENT_ID,C_Location.AD_ORG_ID,C_Location.C_CITY_ID,C_Location.C_COUNTRY_ID,C_Location.C_LOCATION_ID,C_Location.C_REGION_ID FROM C_Location C_Location" +
" LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=C_Location.C_COUNTRY_ID INNER JOIN C_Country_Trl CNTRL ON (cn.C_Country_ID=CNTRL.C_Country_ID) WHERE C_Location.ISACTIVE='Y' AND cnTRL.AD_Language='" + ctx.GetAD_Language() + "'" +
" AND Lower((NVL(CNTRL.Name,'') ||' ' || NVL(C_Location.ADDRESS1,'') ||' ' || NVL(C_Location.ADDRESS2,'') ||' ' || NVL(C_Location.ADDRESS3,'') ||' ' || NVL(C_Location.ADDRESS4,'') ||' ' " +
" || NVL(C_Location.CITY,'') ||' ' || NVL(C_Location.REGIONNAME,'') ||' ' || NVL(C_Location.POSTAL,'') ||' ' || NVL(C_Location.POSTAL_ADD,''))) like Lower('%" + name_startsWith + "%') AND rownum <500";
}
sqlquery = MRole.GetDefault(ctx).AddAccessSQL(sqlquery, "C_Location", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
//sqlquery = "SELECT * FROM (" + sqlquery + " ) fltr WHERE LOWER(fltr.address) LIKE LOWER('" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "')";
var ds = new DataSet();
ds = DB.ExecuteDataset(sqlquery);
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
LocationAddress ladd = new LocationAddress
{
ADDRESS = Convert.ToString(ds.Tables[0].Rows[i]["address"]),
COUNTRYNAME = Convert.ToString(ds.Tables[0].Rows[i]["Name"]),
ADDRESS1 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS1"]),
ADDRESS2 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS2"]),
ADDRESS3 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS3"]),
ADDRESS4 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS4"]),
CITYNAME = Convert.ToString(ds.Tables[0].Rows[i]["CITY"]),
STATENAME = Convert.ToString(ds.Tables[0].Rows[i]["REGIONNAME"]),
ZIPCODE = Convert.ToString(ds.Tables[0].Rows[i]["POSTAL"]),
AD_CLIENT_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_CLIENT_ID"]),
AD_ORG_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_ORG_ID"]),
// C_CITY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_CITY_ID"]),
C_COUNTRY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_COUNTRY_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_COUNTRY_ID"]),
C_LOCATION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_LOCATION_ID"]),
C_REGION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_REGION_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_REGION_ID"])
};
obj.Add(ladd);
}
}
ds = null;
return(obj);
}