ICollection Items_CreateDataSource()
{
// Items Show begin
Items_sSQL = "";
Items_sCountSQL = "";
string sWhere = "", sOrder = "";
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
if (Utility.GetParam("FormItems_Sorting").Length > 0 && !IsPostBack)
{
ViewState["SortColumn"] = Utility.GetParam("FormItems_Sorting");
ViewState["SortDir"] = "ASC";
}
if (ViewState["SortColumn"] != null)
{
sOrder = "";
}
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("category_id"))
{
string temp = Utility.GetParam("category_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("category_id", temp);
}
if (!Params.ContainsKey("is_recommended"))
{
string temp = Utility.GetParam("is_recommended");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("is_recommended", temp);
}
if (Params["category_id"].Length > 0)
{
HasParam = true;
sWhere += "i.[category_id]=" + Params["category_id"];
}
if (Params["is_recommended"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[is_recommended]=" + Params["is_recommended"];
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Items_sSQL = "select [i].[author] as i_author, " +
"[i].[category_id] as i_category_id, " +
"[i].[is_recommended] as i_is_recommended, " +
"[i].[item_id] as i_item_id, " +
"[i].[name] as i_name, " +
"[i].[price] as i_price, " +
"[c].[category_id] as c_category_id, " +
"[c].[name] as c_name " +
" from [items] i, [categories] c" +
" where [c].[category_id]=i.[category_id] ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Items_sSQL = Items_sSQL + sWhere + sOrder;
if (Items_sCountSQL.Length == 0)
{
int iTmpI = Items_sSQL.ToLower().IndexOf("select ");
int iTmpJ = Items_sSQL.ToLower().LastIndexOf(" from ") - 1;
Items_sCountSQL = Items_sSQL.Replace(Items_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) ");
iTmpI = Items_sCountSQL.ToLower().IndexOf(" order by");
if (iTmpI > 1)
{
Items_sCountSQL = Items_sCountSQL.Substring(0, iTmpI);
}
}
//-------------------------------
OleDbDataAdapter command = new OleDbDataAdapter(Items_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, (i_Items_curpage - 1) * Items_PAGENUM, Items_PAGENUM, "Items");
OleDbCommand ccommand = new OleDbCommand(Items_sCountSQL, Utility.Connection);
int PageTemp = (int)ccommand.ExecuteScalar();
Items_Pager.MaxPage = (PageTemp % Items_PAGENUM) > 0?(int)(PageTemp / Items_PAGENUM) + 1:(int)(PageTemp / Items_PAGENUM);
bool AllowScroller = Items_Pager.MaxPage == 1?false:true;
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Items_no_records.Visible = true;
AllowScroller = false;
}
else
{
Items_no_records.Visible = false;
AllowScroller = AllowScroller && true;
}
Items_Pager.Visible = AllowScroller;
return(Source);
// Items Show end
}
ICollection Orders_CreateDataSource()
{
// Orders Show begin
Orders_sSQL = "";
Orders_sCountSQL = "";
string sWhere = "", sOrder = "";
bool bReq = true;
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
if (Utility.GetParam("FormOrders_Sorting").Length > 0 && !IsPostBack)
{
ViewState["SortColumn"] = Utility.GetParam("FormOrders_Sorting");
ViewState["SortDir"] = "ASC";
}
if (ViewState["SortColumn"] != null)
{
sOrder = " ORDER BY " + ViewState["SortColumn"].ToString() + " " + ViewState["SortDir"].ToString();
}
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("member_id"))
{
string temp = Utility.GetParam("member_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("member_id", temp);
}
if (Params["member_id"].Length > 0)
{
HasParam = true;
sWhere += "o.[member_id]=" + Params["member_id"];
}
else
{
bReq = false;
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Orders_sSQL = "select [o].[item_id] as o_item_id, " +
"[o].[member_id] as o_member_id, " +
"[o].[order_id] as o_order_id, " +
"[o].[quantity] as o_quantity, " +
"[i].[item_id] as i_item_id, " +
"[i].[name] as i_name " +
" from [orders] o, [items] i" +
" where [i].[item_id]=o.[item_id] ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Orders_sSQL = Orders_sSQL + sWhere + sOrder;
//-------------------------------
if (!bReq)
{
Orders_no_records.Visible = true;
return(null);
}
OleDbDataAdapter command = new OleDbDataAdapter(Orders_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, 0, Orders_PAGENUM, "Orders");
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Orders_no_records.Visible = true;
}
else
{
Orders_no_records.Visible = false;
}
return(Source);
// Orders Show end
}
ICollection Items_CreateDataSource()
{
// Items Show begin
Items_sSQL = "";
Items_sCountSQL = "";
string sWhere = "", sOrder = "";
bool bReq = true;
bool HasParam = false;
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("UserID"))
{
object Temp = Session["UserID"];
string temp;
if (Temp == null)
{
temp = "";
}
else
{
temp = Temp.ToString();
}
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("UserID", temp);
}
if (Params["UserID"].Length > 0)
{
HasParam = true;
sWhere += "[member_id]=" + Params["UserID"];
}
else
{
bReq = false;
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Items_sSQL = "SELECT order_id, name, price, quantity, member_id, quantity*price as sub_total FROM items, orders WHERE orders.item_id=items.item_id";
sOrder = " ORDER BY order_id";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Items_sSQL = Items_sSQL + sWhere + sOrder;
//-------------------------------
if (!bReq)
{
Items_no_records.Visible = true;
return(null);
}
OleDbDataAdapter command = new OleDbDataAdapter(Items_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, 0, Items_PAGENUM, "Items");
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Items_no_records.Visible = true;
}
else
{
Items_no_records.Visible = false;
}
return(Source);
// Items Show end
}
ICollection Orders_CreateDataSource()
{
// Orders Show begin
Orders_sSQL = "";
Orders_sCountSQL = "";
string sWhere = "", sOrder = "";
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
sOrder = " order by o.order_id Asc";
if (Utility.GetParam("FormOrders_Sorting").Length > 0 && !IsPostBack)
{
ViewState["SortColumn"] = Utility.GetParam("FormOrders_Sorting");
ViewState["SortDir"] = "ASC";
}
if (ViewState["SortColumn"] != null)
{
sOrder = "ORDER BY @SortColumn @SortDir";
}
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("item_id"))
{
string temp = Utility.GetParam("item_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("item_id", temp);
}
if (!Params.ContainsKey("member_id"))
{
string temp = Utility.GetParam("member_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("member_id", temp);
}
if (Params["item_id"].Length > 0)
{
HasParam = true;
sWhere += "o.[item_id]=" + Params["item_id"];
}
if (Params["member_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "o.[member_id]=" + Params["member_id"];
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Orders_sSQL = "select [o].[item_id] as o_item_id, " +
"[o].[member_id] as o_member_id, " +
"[o].[order_id] as o_order_id, " +
"[o].[quantity] as o_quantity, " +
"[i].[item_id] as i_item_id, " +
"[i].[name] as i_name, " +
"[m].[member_id] as m_member_id, " +
"[m].[member_login] as m_member_login " +
" from [orders] o, [items] i, [members] m" +
" where [i].[item_id]=o.[item_id] and [m].[member_id]=o.[member_id] ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Orders_sSQL = Orders_sSQL + sWhere + sOrder;
if (Orders_sCountSQL.Length == 0)
{
int iTmpI = Orders_sSQL.ToLower().IndexOf("select ");
int iTmpJ = Orders_sSQL.ToLower().LastIndexOf(" from ") - 1;
Orders_sCountSQL = Orders_sSQL.Replace(Orders_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) ");
iTmpI = Orders_sCountSQL.ToLower().IndexOf(" order by");
if (iTmpI > 1)
{
Orders_sCountSQL = Orders_sCountSQL.Substring(0, iTmpI);
}
}
//-------------------------------
OleDbDataAdapter command = new OleDbDataAdapter(Orders_sSQL, Utility.Connection);
command.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("@SortColumn", viewstate["SortColumn"]));
command.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("@SortDir", viewstate["SortDir"]));
DataSet ds = new DataSet();
command.Fill(ds, (i_Orders_curpage - 1) * Orders_PAGENUM, Orders_PAGENUM, "Orders");
OleDbCommand ccommand = new OleDbCommand(Orders_sCountSQL, Utility.Connection);
int PageTemp = (int)ccommand.ExecuteScalar();
Orders_Pager.MaxPage = (PageTemp % Orders_PAGENUM) > 0?(int)(PageTemp / Orders_PAGENUM) + 1:(int)(PageTemp / Orders_PAGENUM);
bool AllowScroller = Orders_Pager.MaxPage == 1?false:true;
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Orders_no_records.Visible = true;
AllowScroller = false;
}
else
{
Orders_no_records.Visible = false;
AllowScroller = AllowScroller && true;
}
Orders_Pager.Visible = AllowScroller;
return(Source);
// Orders Show end
}
ICollection Results_CreateDataSource()
{
// Results Show begin
Results_sSQL = "";
Results_sCountSQL = "";
string sWhere = "", sOrder = "";
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
sOrder = " order by i.name Asc";
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("author"))
{
string temp = Utility.GetParam("author");
Params.Add("author", temp);
}
if (!Params.ContainsKey("category_id"))
{
string temp = Utility.GetParam("category_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("category_id", temp);
}
if (!Params.ContainsKey("name"))
{
string temp = Utility.GetParam("name");
Params.Add("name", temp);
}
if (!Params.ContainsKey("pricemax"))
{
string temp = Utility.GetParam("pricemax");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("pricemax", temp);
}
if (!Params.ContainsKey("pricemin"))
{
string temp = Utility.GetParam("pricemin");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, FieldTypes.Number);
}
else
{
temp = "";
}
Params.Add("pricemin", temp);
}
if (Params["author"].Length > 0)
{
HasParam = true;
sWhere += "i.[author] like '%" + Params["author"].Replace("'", "''") + "%'";
}
if (Params["category_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[category_id]=" + Params["category_id"];
}
if (Params["name"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[name] like '%" + Params["name"].Replace("'", "''") + "%'";
}
if (Params["pricemax"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[price]<" + Params["pricemax"];
}
if (Params["pricemin"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[price]>" + Params["pricemin"];
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Results_sSQL = "select [i].[author] as i_author, " +
"[i].[category_id] as i_category_id, " +
"[i].[image_url] as i_image_url, " +
"[i].[item_id] as i_item_id, " +
"[i].[name] as i_name, " +
"[i].[price] as i_price, " +
"[c].[category_id] as c_category_id, " +
"[c].[name] as c_name " +
" from [items] i, [categories] c" +
" where [c].[category_id]=i.[category_id] ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Results_sSQL = Results_sSQL + sWhere + sOrder;
if (Results_sCountSQL.Length == 0)
{
int iTmpI = Results_sSQL.ToLower().IndexOf("select ");
int iTmpJ = Results_sSQL.ToLower().LastIndexOf(" from ") - 1;
Results_sCountSQL = Results_sSQL.Replace(Results_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) ");
iTmpI = Results_sCountSQL.ToLower().IndexOf(" order by");
if (iTmpI > 1)
{
Results_sCountSQL = Results_sCountSQL.Substring(0, iTmpI);
}
}
//-------------------------------
OleDbDataAdapter command = new OleDbDataAdapter(Results_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, (i_Results_curpage - 1) * Results_PAGENUM, Results_PAGENUM, "Results");
OleDbCommand ccommand = new OleDbCommand(Results_sCountSQL, Utility.Connection);
int PageTemp = (int)ccommand.ExecuteScalar();
Results_Pager.MaxPage = (PageTemp % Results_PAGENUM) > 0?(int)(PageTemp / Results_PAGENUM) + 1:(int)(PageTemp / Results_PAGENUM);
bool AllowScroller = Results_Pager.MaxPage == 1?false:true;
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Results_no_records.Visible = true;
AllowScroller = false;
}
else
{
Results_no_records.Visible = false;
AllowScroller = AllowScroller && true;
}
Results_Pager.Visible = AllowScroller;
return(Source);
// Results Show end
}