public void ExecuteSearch(SalesQuery qryObj)
{
sqlWhere = buildWhereStr(qryObj);
DataTable dt = new DataTable();
SqlConnection cn = null;
SqlDataAdapter da = null;
cn = new SqlConnection(conStr);
try
{
cn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
//String sqlStr = "";
//sqlStr = sqlStr + "select * from CAMVIEW_Sales ";
//sqlStr = sqlStr + buildWhereStr(qryObj);
String sqlStr = "SELECT * FROM CAMVIEW_PropertyCardSales WHERE pin in (";
sqlStr = sqlStr + "select pin from CAMVIEW_Sales ";
sqlStr = sqlStr + sqlWhere;
sqlStr = sqlStr + ") AND " + sqlWhere.Replace("WHERE", "");
SqlCommand cmd = new SqlCommand(sqlStr, cn);
da = new SqlDataAdapter(cmd);
dt = new DataTable();
DataSet dSet = new DataSet();
da.Fill(dt);
PrepResults(dt);
cn.Close();
}
public void ExecutePagedSearch(SalesQuery qryObj, int startrec, int endrec)
{
start_rec = startrec;
end_rec = endrec;
sqlWhere = buildWhereStr(qryObj);
DataTable dt = new DataTable();
SqlConnection cn = null;
SqlDataAdapter da = null;
cn = new SqlConnection(conStr);
try
{
cn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
//String sqlStr = "";
//sqlStr = sqlStr + "select * from CAMVIEW_Sales ";
//sqlStr = sqlStr + buildWhereStr(qryObj);
/*
* String sqlStr = "SELECT * FROM CAMVIEW_PropertyCardSales WHERE pin in (";
* sqlStr = sqlStr + "select pin from CAMVIEW_Sales ";
* sqlStr = sqlStr + buildWhereStr(qryObj);
* sqlStr = sqlStr + ")";
*/
String msqlStr = "SELECT * FROM CAMVIEW_PropertyCardSalesWebGIS WHERE pin in (";
msqlStr = msqlStr + "select pin from CAMVIEW_Sales ";
msqlStr = msqlStr + sqlWhere;
msqlStr = msqlStr + ") AND " + sqlWhere.Replace("WHERE", "");
String sqlStr = "";
sqlStr = sqlStr + " SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY PIN ) AS RowNum, * FROM ";
sqlStr = sqlStr + " (";
sqlStr = sqlStr + msqlStr;
sqlStr = sqlStr + " ) a";
sqlStr = sqlStr + " ) AS RowConstrainedResult ";
sqlStr = sqlStr + " WHERE RowNum >= " + startrec.ToString() + " AND RowNum <= " + endrec.ToString();
String csqlStr = "SELECT COUNT(*) FROM CAMVIEW_PropertyCardSalesWebGIS WHERE pin in (";
csqlStr = csqlStr + "select pin from CAMVIEW_Sales ";
csqlStr = csqlStr + sqlWhere;
csqlStr = csqlStr + ") AND " + sqlWhere.Replace("WHERE", "");
SqlCommand ccmd = new SqlCommand(csqlStr, cn);
try
{
rec_count = (int)ccmd.ExecuteScalar();
}
catch
{
rec_count = 0;
}
this.rawSQLQuery = msqlStr;
SqlCommand cmd = new SqlCommand(sqlStr, cn);
da = new SqlDataAdapter(cmd);
dt = new DataTable();
DataSet dSet = new DataSet();
da.Fill(dt);
PrepResults(dt);
cn.Close();
proplist.rec_count = rec_count;
proplist.search_type = "sales";
proplist.sqlWhere = sqlWhere;
proplist.start_rec = start_rec;
proplist.end_rec = end_rec;
}
private String buildWhereStr(SalesQuery qo)
{
String whereStr = "";
ArrayList al = new ArrayList();
sqlDateWhere = "";
if (qo.startAcreage >= 0 && qo.endAcreage > 0)
{
al.Add((String.Format(" acres >={0} AND acres <={1}", qo.startAcreage, qo.endAcreage)));
}
if (qo.startArea >= 0 && qo.endArea > 0)
{
al.Add((String.Format(" Heated_Sq_Ft >={0} AND Heated_Sq_Ft <={1}", qo.startArea, qo.endArea)));
}
if (qo.startPrice >= 0 && qo.endPrice > 0)
{
al.Add((String.Format(" SalePrice >={0} AND SalePrice <={1}", qo.startPrice, qo.endPrice)));
sqlDateWhere = String.Format(" SalePrice >={0} AND SalePrice <={1}", qo.startPrice, qo.endPrice);
}
if (qo.startDate != "" && qo.endDate != "")
{
al.Add((String.Format(" SaleDate >='{0}' AND SaleDate <='{1}'", qo.startDate, qo.endDate)));
sqlDateWhere = String.Format(" SaleDate >='{0}' AND SaleDate <='{1}'", qo.startDate, qo.endDate);
}
/*
* if (qo.subNumber != "")
* {
* al.Add((String.Format(" Subdivision_Code ='{0}' ", qo.subNumber)));
* }
*
*
* if (qo.subid != "")
* {
* al.Add((String.Format(" Subdivision ='{0}' ", qo.subid)));
* }
*/
if (qo.sub != "" && qo.sub != null)
{
al.Add((String.Format(" Subdivision ='{0}' ", qo.sub)));
}
if (qo.sectionValue != "")
{
//al.Add((String.Format(" Subdivision ='{0}' ", qo.subid)));
}
if (qo.townshipValue != "")
{
//al.Add((String.Format(" Subdivision ='{0}' ", qo.subid)));
}
if (qo.rangeValue != "")
{
//al.Add((String.Format(" Subdivision ='{0}' ", qo.subid)));
}
if (qo.saleQualification1 != "")
{
//al.Add((String.Format(" Sale_Qual ='{0}' ", qo.saleQualification1)));
}
if (qo.saleQualification != "")
{
//al.Add((String.Format(" Sale_Qual ='{0}' ", qo.saleQualification)));
}
if (qo.saleVacant1 != "")
{
//al.Add((String.Format(" Vacant_Impr ='{0}' ", qo.saleVacant1)));
}
if (qo.saleVacant2 != "")
{
//al.Add((String.Format(" Vacant_Impr ='{0}' ", qo.saleVacant2)));
}
if (qo.saleVacant != "")
{
//al.Add((String.Format(" Vacant_Impr ='{0}' ", qo.saleVacant)));
}
String[] als = (String[])al.ToArray(typeof(String));
whereStr = " WHERE " + String.Join(" AND ", als);
return(whereStr);
}
