public List <AutocompleteInfo> Get_Roles_By_Name(string role_Name)
{
List <AutocompleteInfo> autoCompletes = new List <AutocompleteInfo>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@Role_Name", role_Name));
DataTable dt = sqlRepo.ExecuteDataTable(sqlParams, StoredProcedures.Get_Role_By_Name_Sp.ToString(), CommandType.StoredProcedure);
if (dt != null && dt.Rows.Count > 0)
{
List <DataRow> drList = new List <DataRow>();
if (dt != null && dt.Rows.Count > 0)
{
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo autoComplete = new AutocompleteInfo();
autoComplete.Value = Convert.ToInt32(dr["Role_Id"]);
autoComplete.Label = Convert.ToString(dr["Role_Name"]);
autoCompletes.Add(autoComplete);
}
}
}
return(autoCompletes);
}
public List <AutocompleteInfo> Get_PArticles_By_Full_Code(string full_Code)
{
List <AutocompleteInfo> autoCompletes = new List <AutocompleteInfo>();
List <SqlParameter> sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter("@full_Code", full_Code));
DataTable dt = _sqlRepo.ExecuteDataTable(sqlparam, StoredProcedures.Get_P_Articles_By_Full_Code_sp.ToString(), CommandType.StoredProcedure);
List <DataRow> drList = new List <DataRow>();
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo auto = new AutocompleteInfo();
auto.Value = Convert.ToInt32(dr["P_Article_Id"]);
auto.Label = Convert.ToString(dr["Full_Code"]);
autoCompletes.Add(auto);
}
return(autoCompletes);
}
public List <AutocompleteInfo> Get_Customer_AutoComplete(string Customer_Name)
{
List <AutocompleteInfo> customer_List = new List <AutocompleteInfo>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@Customer_Name", Customer_Name));
DataTable dt = _sqlRepo.ExecuteDataTable(sqlParams, StoredProcedures.Get_Customer_By_Name_Sp.ToString(), CommandType.StoredProcedure);
if (dt != null && dt.Rows.Count > 0)
{
List <DataRow> drList = new List <DataRow>();
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo auto = new AutocompleteInfo();
auto.Label = Convert.ToString(dr["Customer_Name"]);
auto.Value = Convert.ToInt32(dr["Customer_Id"]);
customer_List.Add(auto);
}
}
return(customer_List);
}
//Autocomplete
public List <AutocompleteInfo> Get_Sample_No_AutoComplete(string sample_No)
{
List <AutocompleteInfo> vendors = new List <AutocompleteInfo>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@Sample_No", sample_No));
DataTable dt = _sqlRepo.ExecuteDataTable(sqlParams, StoredProcedures.Get_Sample_By_No_Sp.ToString(), CommandType.StoredProcedure);
if (dt != null && dt.Rows.Count > 0)
{
List <DataRow> drList = new List <DataRow>();
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo auto = new AutocompleteInfo();
auto.Label = Convert.ToString(dr["Sample_No"]);
auto.Value = Convert.ToInt32(dr["Sample_Id"]);
vendors.Add(auto);
}
}
return(vendors);
}
public List <AutocompleteInfo> Get_Work_Centers_By_Code_Purpose(string work_Center_Code)
{
List <AutocompleteInfo> autoCompletes = new List <AutocompleteInfo>();
List <SqlParameter> sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter("@work_Center_Code", work_Center_Code));
sqlparam.Add(new SqlParameter("@purpose", Convert.ToInt32(ArticleType.YArticle)));
DataTable dt = _sqlRepo.ExecuteDataTable(sqlparam, StoredProcedures.Get_Work_Centers_By_Code_Purpose_Sp.ToString(), CommandType.StoredProcedure);
List <DataRow> drList = new List <DataRow>();
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo auto = new AutocompleteInfo();
auto.Value = Convert.ToInt32(dr["Work_Center_Id"]);
auto.Label = Convert.ToString(dr["Work_Center_Code"]);
autoCompletes.Add(auto);
}
return(autoCompletes);
}
private AutocompleteInfo Get_Autocomplete_Values(DataRow dr)
{
AutocompleteInfo autocomplete = new AutocompleteInfo();
autocomplete.AutocompleteId = Convert.ToInt32(dr["AutocompleteId"]);
autocomplete.PageName = Convert.ToString(dr["PageName"]);
autocomplete.ControlName = Convert.ToString(dr["ControlName"]);
autocomplete.TableName = Convert.ToString(dr["TableName"]);
autocomplete.TextFieldName = Convert.ToString(dr["TextFieldName"]);
autocomplete.IdFieldName = Convert.ToString(dr["IdFieldName"]);
autocomplete.DependentControlName = Convert.ToString(dr["DependentControlName"]);
autocomplete.DependentFieldName = Convert.ToString(dr["DependentFieldName"]);
autocomplete.ExtraFields = Convert.ToString(dr["ExtraFields"]);
if (dr["IsMultiselect"] != DBNull.Value)
{
autocomplete.IsMultiselect = Convert.ToBoolean(dr["IsMultiselect"]);
}
if (dr["CreatedBy"] != DBNull.Value)
{
autocomplete.CreatedBy = Convert.ToInt32(dr["CreatedBy"]);
}
if (dr["CreatedDate"] != DBNull.Value)
{
autocomplete.CreatedDate = Convert.ToDateTime(dr["CreatedDate"]);
}
if (dr["UpdatedBy"] != DBNull.Value)
{
autocomplete.UpdatedBy = Convert.ToInt32(dr["UpdatedBy"]);
}
if (dr["UpdatedDate"] != DBNull.Value)
{
autocomplete.UpdatedDate = Convert.ToDateTime(dr["UpdatedDate"]);
}
return(autocomplete);
}
public static bool Matches(AutocompleteInfo info, string space, string test)
{
if (test != "" && info.Type == Type.Space)
return false;
if (info.Space != "" && space != "")
for (int i = 0; i < space.Length; i++)
if (info.Space.ToLower()[i] != space[i])
return false;
if (test != "")
for (int i = 0; i < test.Length; i++)
if (info.Shortcut.ToLower()[i] != test[i])
return false;
return true;
}
//autocomplete
public List <AutocompleteInfo> Get_Event_By_Name_Autocomplete(string Event_Name)
{
List <AutocompleteInfo> autoList = new List <AutocompleteInfo>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@Event_Name", Event_Name));
DataTable dt = _sqlRepo.ExecuteDataTable(sqlParams, StoredProcedures.Get_Event_By_Name_Autocomplete_Sp.ToString(), CommandType.StoredProcedure);
List <DataRow> drList = new List <DataRow>();
drList = dt.AsEnumerable().ToList();
foreach (DataRow dr in drList)
{
AutocompleteInfo autoData = new AutocompleteInfo();
autoData.Label = Convert.ToString(dr["Event_Name"]);
autoData.Value = Convert.ToInt32(dr["Event_Id"]);
autoList.Add(autoData);
}
return(autoList);
}
private void LoadDataList()
{
//get HTTP Request vars
String term = Request["term"];
string cat = Request.QueryString["cat"];
string[] mode = null;
if (Request.QueryString["mode"] != null)
{
mode = new string[1];
mode[0] = "Product_Number";
}
if (term != null)//
{
//URl encode to protect against SQL injection attracts
term = Server.HtmlEncode(term);
cat = Server.HtmlEncode(cat);
SqlDataReader reader = null;
List <AutocompleteInfo> data = new List <AutocompleteInfo>();
try
{
//establish an connection to the SQL server
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Kinexus Protein ProductDBConnectionStringServer"].ConnectionString);
SqlCommand command = new SqlCommand(
BuildSQL(term, cat, mode), connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
AutocompleteInfo info = new AutocompleteInfo();
string value = reader[0].ToString();
//string lable = reader[1].ToString();
if (value.Contains(Alias_Delim + " "))
{
StringCollection aliasCollection = tokenizeStringToCollection(Alias_Delim, value);
foreach (string alias in aliasCollection)
{
if (alias.StartsWith(term))
{
info.value = alias;
data.Add(info);
}
}
}
else
{
info.value = value;
data.Add(info);
}
}
}
catch (Exception e) {
//do noting
}
finally
{
//Close when done reading.
reader.Close();
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
output.Text += serializer.Serialize(data);
}
else
{
//force the use to the home page
output.Text += "<meta http-equiv=\"refresh\" content=\"0;url=/\">";
}
}
