public static List <CPostal> Search(string searchString, int limit, int offset)
{
string sql = new StringBuilder(BSql)
.Append(" WHERE ")
.Append(searchString)
.ToString();
List <MySqlParameter> lp = new List <MySqlParameter>();
return(CPostal.ExecuteQuery(sql, lp, limit, offset));
}
public static List <CPostal> SerachByCliente(string cliente, int limit, int offset)
{
string sql = new StringBuilder(BSql)
.Append(" WHERE ")
.Append(string.Format("(`{0}` like @{0})", CPostal.Column.cliente.ToString()))
.ToString();
List <MySqlParameter> lp = new List <MySqlParameter>();
lp.Add(new MySqlParameter("@" + CPostal.Column.cliente.ToString(), "%" + cliente.Trim().Replace(' ', '%') + "%"));
return(CPostal.ExecuteQuery(sql, lp, limit, offset));
}
/// <summary>
/// Get a client from database
/// </summary>
/// <param name="client"></param>
/// <returns></returns>
public static List <CPostal> GetClient(string client)
{
string sql = new StringBuilder(BSql)
.Append(" WHERE ")
.Append(string.Format("(`{0}` = @{0})", CPostal.Column.cliente.ToString()))
.ToString();
List <MySqlParameter> lp = new List <MySqlParameter>();
lp.Add(new MySqlParameter("@" + CPostal.Column.cliente.ToString(), client));
return(CPostal.ExecuteQuery(sql, lp, 1, 0));
}
public static List <CPostal> SearchByCpostal(string cp, int limit, int offset)
{
string p1 = "p";
string sql = new StringBuilder(BSql)
.Append(" WHERE ")
.Append(string.Format("(CONCAT(`{0}`,`{1}`) like @{2})", CPostal.Column.cp4, CPostal.Column.cp3, p1))
.ToString();
List <MySqlParameter> lp = new List <MySqlParameter>();
lp.Add(new MySqlParameter("@" + p1, "%" + Regex.Replace(cp.Trim().Replace("-", "%").Replace(" ", "%"), @"\s?\D?", "") + "%"));
return(CPostal.ExecuteQuery(sql, lp, limit, offset));
}
public static List <CPostal> SearchByDisAddress(string distrito, string address, int limit, int offset)
{
string sql = new StringBuilder(BSql)
.Append(" WHERE ")
.Append(string.Format("(`{0}` like @{0})", CPostal.Column.morada.ToString()))
.Append(" AND ")
.Append(string.Format("(`{0}` = @{0})", CPostal.Column.distrito.ToString()))
.ToString();
List <MySqlParameter> lp = new List <MySqlParameter>();
lp.Add(new MySqlParameter("@" + CPostal.Column.morada.ToString(), "%" + address.Trim().Replace(' ', '%') + "%"));
lp.Add(new MySqlParameter("@" + CPostal.Column.distrito.ToString(), distrito));
return(CPostal.ExecuteQuery(sql, lp, limit, offset));
}
/// <summary>
/// Serach fpr distrito
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="limit"></param>
/// <param name="offset"></param>
/// <returns></returns>
protected static List <CPostal> ExecuteQuery(string sql, List <MySqlParameter> param, int limit, int offset)
{
List <CPostal> l = new List <CPostal>();
CpMySql my = new CpMySql();
MySqlDataReader r = my.ExecuteCommand(sql, param, limit, offset);
while (r.Read())
{
CPostal c = new CPostal
{
art_desig = r.GetString(r.GetOrdinal(CPostal.Column.art_desig.ToString())),
art_local = r.GetString(r.GetOrdinal(CPostal.Column.art_local.ToString())),
art_tipo = r.GetString(r.GetOrdinal(CPostal.Column.art_tipo.ToString())),
art_titulo = r.GetString(r.GetOrdinal(CPostal.Column.art_titulo.ToString())),
cc = r.GetString(r.GetOrdinal(CPostal.Column.cc.ToString())),
cliente = r.GetString(r.GetOrdinal(CPostal.Column.cliente.ToString())),
cod_arteria = r.GetString(r.GetOrdinal(CPostal.Column.cod_arteria.ToString())),
cod_localidade = r.GetString(r.GetOrdinal(CPostal.Column.cod_localidade.ToString())),
concelho = r.GetString(r.GetOrdinal(CPostal.Column.concelho.ToString())),
cp3 = r.GetString(r.GetOrdinal(CPostal.Column.cp3.ToString())),
cp4 = r.GetString(r.GetOrdinal(CPostal.Column.cp4.ToString())),
cpalf = r.GetString(r.GetOrdinal(CPostal.Column.cpalf.ToString())),
dd = r.GetString(r.GetOrdinal(CPostal.Column.dd.ToString())),
distrito = r.GetString(r.GetOrdinal(CPostal.Column.distrito.ToString())),
localidade = r.GetString(r.GetOrdinal(CPostal.Column.localidade.ToString())),
morada = r.GetString(r.GetOrdinal(CPostal.Column.morada.ToString())),
porta = r.GetString(r.GetOrdinal(CPostal.Column.porta.ToString())),
pri_prep = r.GetString(r.GetOrdinal(CPostal.Column.pri_prep.ToString())),
seg_prep = r.GetString(r.GetOrdinal(CPostal.Column.seg_prep.ToString())),
troco = r.GetString(r.GetOrdinal(CPostal.Column.troco.ToString()))
};
l.Add(c);
}
my.Command.Dispose();
r.Close();
my.Conection.Close();
return(l);
}
