void CreateQueryString(string query)
{
if (query == null || query.Length == 0)
{
query_string = new QueryParamCollection();
return;
}
query_string = new QueryParamCollection();
if (query[0] == '?')
{
query = query.Substring(1);
}
string[] components = query.Split('&');
foreach (string kv in components)
{
int pos = kv.IndexOf('=');
if (pos == -1)
{
query_string.Add(null, WebUtility.UrlDecode(kv));
}
else
{
string key = WebUtility.UrlDecode(kv.Substring(0, pos));
string val = WebUtility.UrlDecode(kv.Substring(pos + 1));
query_string.Add(key, val);
}
}
}
public object ReturnSqlValue(string table, object fieldIn, object valueIn, string fieldOut, object fieldIn2 = null, object valueIn2 = null)
{
string query = "";
QueryParamCollection queryParams = new QueryParamCollection();
if (fieldIn2 != null && valueIn2 != null)
{
query = "SELECT TOP 1 " + fieldOut + " FROM " + table;
queryParams.Add(fieldIn.ToString(), valueIn.ToString(), true);
queryParams.Add(fieldIn2.ToString(), valueIn2.ToString(), true);
}
else
{
query = "SELECT TOP 1 " + fieldOut + " FROM " + table;
queryParams.Add(fieldIn.ToString(), valueIn.ToString(), true);
}
using (var cmd = GetSqlCommandFromParams(query, queryParams.Parameters))
using (var conn = cmd.Connection)
{
cmd.Connection.Open();
return(cmd.ExecuteScalar());
}
}
private async void EmpNameSearch(string name)
{
try
{
string columns = "a_employee_number,a_name_last,a_name_first,a_org_primary,a_object_primary,a_location_primary,a_location_p_desc,a_location_p_short";
string query = "SELECT TOP " + maxResults + " " + columns + " FROM pr_employee_master";
var searchParams = new QueryParamCollection();
searchParams.Add("a_name_last", name.ToUpper(), false, "OR");
searchParams.Add("a_name_first", name.ToUpper(), false, "OR");
SetWorking(true);
var comms = new MunisComms();
using (var cmd = comms.GetSqlCommandFromParams(query, searchParams.Parameters))
using (var results = await comms.ReturnSqlTableFromCmdAsync(cmd))
{
if (results.Rows.Count > 0)
{
MunisResults.DataSource = null;
MunisResults.DataSource = results;
MunisResults.ClearSelection();
}
}
}
catch (Exception ex)
{
ErrorHandling.ErrHandle(ex, System.Reflection.MethodBase.GetCurrentMethod());
}
finally
{
SetWorking(false);
}
}
private static async Task <DataTable> GetReqLineItemsFromReqNum(string reqNumber, string fiscalYr)
{
if (reqNumber == "" || fiscalYr == "")
{
return(null);
}
var vendorNum = await GetVendorNumberFromReqNumber(reqNumber, fiscalYr);
if ((string)vendorNum == "")
{
return(null);
}
string nendorName = await munisComms.ReturnSqlValueAsync("ap_vendor", "a_vendor_number", vendorNum, "a_vendor_name");
string query = "SELECT TOP " + intMaxResults + @" dbo.rq_gl_info.rg_fiscal_year, dbo.rq_gl_info.a_requisition_no, dbo.rq_gl_info.rg_org, dbo.rq_gl_info.rg_object, dbo.rq_gl_info.a_org_description, dbo.rq_gl_info.a_object_desc,
'" + nendorName + "' AS a_vendor_name, '" + vendorNum + @"' AS a_vendor_number, dbo.rqdetail.rqdt_pur_no, dbo.rqdetail.rqdt_pur_dt, dbo.rqdetail.rqdt_lin_no, dbo.rqdetail.rqdt_uni_pr, dbo.rqdetail.rqdt_net_pr, dbo.rqdetail.rqdt_qty_no, dbo.rqdetail.rqdt_des_ln, dbo.rqdetail.rqdt_vdr_part_no
FROM dbo.rq_gl_info INNER JOIN
dbo.rqdetail ON dbo.rq_gl_info.rg_line_number = dbo.rqdetail.rqdt_lin_no AND dbo.rq_gl_info.a_requisition_no = dbo.rqdetail.rqdt_req_no AND dbo.rq_gl_info.rg_fiscal_year = dbo.rqdetail.rqdt_fsc_yr";
QueryParamCollection searchParams = new QueryParamCollection();
searchParams.Add("dbo.rq_gl_info.a_requisition_no", reqNumber, true);
searchParams.Add("dbo.rq_gl_info.rg_fiscal_year", fiscalYr, true);
var reqTable = await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(query, searchParams.Parameters));
if (reqTable.Rows.Count > 0)
{
return(reqTable);
}
else
{
return(null);
}
}
public async Task <string> ReturnSqlValueAsync(string table, object fieldIn, object valueIn, string fieldOut, object fieldIn2 = null, object valueIn2 = null)
{
string query = "";
QueryParamCollection queryParams = new QueryParamCollection();
if (fieldIn2 != null && valueIn2 != null)
{
query = "SELECT TOP 1 " + fieldOut + " FROM " + table;
queryParams.Add(fieldIn.ToString(), valueIn.ToString(), true);
queryParams.Add(fieldIn2.ToString(), valueIn2.ToString(), true);
}
else
{
query = "SELECT TOP 1 " + fieldOut + " FROM " + table;
queryParams.Add(fieldIn.ToString(), valueIn.ToString(), true);
}
using (var cmd = GetSqlCommandFromParams(query, queryParams.Parameters))
using (var conn = cmd.Connection)
{
await cmd.Connection.OpenAsync();
var Value = await cmd.ExecuteScalarAsync();
if (Value != null)
{
return(Value.ToString());
}
}
return(string.Empty);
}
public static async void NewOrgObjView(string org, string obj, string fy, ExtendedForm parentForm)
{
try
{
parentForm.Waiting();
GridForm newGridForm = new GridForm(parentForm, "Org/Obj Info");
string glColumns = " glma_org, glma_obj, glma_desc, glma_seg5, glma_bud_yr, glma_orig_bud_cy, glma_rev_bud_cy, glma_encumb_cy, glma_memo_bal_cy, glma_rev_bud_cy-glma_encumb_cy-glma_memo_bal_cy AS 'Funds Available' ";
string glMasterQry = "Select TOP " + intMaxResults + " " + glColumns + "FROM glmaster";
QueryParamCollection glParams = new QueryParamCollection();
glParams.Add("glma_org", org, true);
if (!string.IsNullOrEmpty(obj)) //Show Rollup info for Object
{
glParams.Add("glma_obj", obj, true);
string rollUpCode = await munisComms.ReturnSqlValueAsync("gl_budget_rollup", "a_org", org, "a_rollup_code");
string rollUpByCodeQry = "SELECT TOP " + intMaxResults + " * FROM gl_budget_rollup WHERE a_rollup_code = '" + rollUpCode + "'";
string budgetQry = "SELECT TOP " + intMaxResults + " a_projection_no,a_org,a_object,db_line,db_bud_desc_line1,db_bud_reason_desc,db_bud_req_qty5,db_bud_unit_cost,db_bud_req_amt5,a_account_id FROM gl_budget_detail_2"; // WHERE a_projection_no='" & FY & "' AND a_org='" & Org & "' AND a_object='" & Obj & "'"
QueryParamCollection budgetParams = new QueryParamCollection();
budgetParams.Add("a_projection_no", fy, true);
budgetParams.Add("a_org", org, true);
budgetParams.Add("a_object", obj, true);
newGridForm.AddGrid("OrgGrid", "GL Info:", await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(glMasterQry, glParams.Parameters)));
newGridForm.AddGrid("RollupGrid", "Rollup Info:", await munisComms.ReturnSqlTableAsync(rollUpByCodeQry));
newGridForm.AddGrid("BudgetGrid", "Budget Info:", await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(budgetQry, budgetParams.Parameters)));
}
else // Show Rollup info for all Objects in Org
{
string rollUpAllQry = "SELECT TOP " + intMaxResults + " * FROM gl_budget_rollup";
QueryParamCollection rollUpParams = new QueryParamCollection();
rollUpParams.Add("a_org", org, true);
newGridForm.AddGrid("OrgGrid", "GL Info:", await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(glMasterQry, glParams.Parameters))); //MunisComms.Return_MSSQLTableAsync(Qry))
newGridForm.AddGrid("RollupGrid", "Rollup Info:", await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(rollUpAllQry, rollUpParams.Parameters))); //MunisComms.Return_MSSQLTableAsync("SELECT TOP " & intMaxResults & " * FROM gl_budget_rollup WHERE a_org = '" & Org & "'"))
}
newGridForm.Show();
}
catch (Exception ex)
{
ErrorHandling.ErrHandle(ex, System.Reflection.MethodBase.GetCurrentMethod());
}
finally
{
parentForm.DoneWaiting();
}
}
/// <summary>
/// Queries the database for a list of results that contains the employee name result and computed Levenshtein distance to the search string.
/// </summary>
/// <param name="tableName"></param>
/// <param name="searchEmpName"></param>
/// <param name="empNameColumn"></param>
/// <param name="empNumColumn"></param>
/// <returns></returns>
private static List <SmartEmpSearchInfo> GetEmpSearchResults(string tableName, string searchEmpName, string empNameColumn, string empNumColumn)
{
var tmpResults = new List <SmartEmpSearchInfo>();
var searchParams = new QueryParamCollection();
// Parameterize the input.
searchParams.Add(empNameColumn, searchEmpName, false);
// Query the DB for the search value.
using (DataTable data = DBFactory.GetDatabase().DataTableFromParameters("SELECT * FROM " + tableName + " WHERE", searchParams.Parameters))
{
foreach (DataRow row in data.Rows)
{
var searchValue = searchEmpName.ToUpper();
var dbValue = row[empNameColumn].ToString().ToUpper();
// Get the distance between the search value and DB value.
var distance = Fastenshtein.Levenshtein.Distance(searchValue, dbValue);
var employee = new MunisEmployee(row[empNameColumn].ToString(), row[empNumColumn].ToString());
// Add new entry to the list.
tmpResults.Add(new SmartEmpSearchInfo(employee, searchEmpName, distance));
}
}
return(tmpResults);
}
protected void AddQueryParameterIfNotNull <T>(QueryParamCollection queryParams, string name, T value)
{
if (value != null)
{
queryParams.Add(name, value);
}
}
private QueryParamCollection BuildSearchListNew()
{
var searchParams = new QueryParamCollection();
searchParams.Add(SibiRequestCols.RTNumber, RTNumberTextBox.Text.Trim(), false);
searchParams.Add(SibiRequestCols.Description, DescriptionTextBox.Text.Trim(), false);
searchParams.Add(SibiRequestCols.PO, POTextBox.Text, false);
searchParams.Add(SibiRequestCols.RequisitionNumber, ReqNumberTextBox.Text, false);
//Filter out unpopulated fields.
var popSearchParams = new QueryParamCollection();
foreach (var param in searchParams.Parameters)
{
if (param.Value.ToString() != "")
{
popSearchParams.Add(param);
}
}
return(popSearchParams);
}
private static async Task <DataTable> GetReqHeaderFromReqNum(string reqNumber, string fiscalYr)
{
if (reqNumber == "" || fiscalYr == "")
{
return(null);
}
string query = "SELECT TOP " + intMaxResults + " * FROM rqheader";
QueryParamCollection searchParams = new QueryParamCollection();
searchParams.Add("rqhd_req_no", reqNumber, true);
searchParams.Add("rqhd_fsc_yr", fiscalYr, true);
DataTable results = await munisComms.ReturnSqlTableFromCmdAsync(munisComms.GetSqlCommandFromParams(query, searchParams.Parameters));
if (results.Rows.Count > 0)
{
return(results);
}
return(null);
}
private static async void NewMunisEmployeeSearch(string name, ExtendedForm parentForm)
{
try
{
parentForm.Waiting();
string columns = "e.a_employee_number,e.a_name_last,e.a_name_first,e.a_org_primary,e.a_object_primary,e.a_location_primary,e.a_location_p_desc,e.a_location_p_short,e.e_work_location,m.a_employee_number as sup_employee_number,m.a_name_first as sup_name_first,m.a_name_last as sup_name_last";
string query = "SELECT TOP " + intMaxResults + " " + columns + @"
FROM pr_employee_master e
INNER JOIN pr_employee_master m on e.e_supervisor = m.a_employee_number";
QueryParamCollection searchParams = new QueryParamCollection();
searchParams.Add("e.a_name_last", name.ToUpper(), "OR");
searchParams.Add("e.a_name_first", name.ToUpper(), "OR");
using (var cmd = munisComms.GetSqlCommandFromParams(query, searchParams.Parameters))
using (var results = await munisComms.ReturnSqlTableFromCmdAsync(cmd))
{
if (HasResults(results, parentForm))
{
parentForm.DoneWaiting();
GridForm newGridForm = new GridForm(parentForm, "MUNIS Employee Info");
newGridForm.AddGrid("EmpGrid", "MUNIS Info:", results);
newGridForm.Show();
}
}
}
catch (Exception ex)
{
ErrorHandling.ErrHandle(ex, System.Reflection.MethodBase.GetCurrentMethod());
}
finally
{
parentForm.DoneWaiting();
}
}
public static string GetSqlValue(string table, string fieldIn, string valueIn, string fieldOut)
{
string sqlQRY = "SELECT " + fieldOut + " FROM " + table + " WHERE ";
QueryParamCollection searchParam = new QueryParamCollection();
searchParam.Add(fieldIn, valueIn, true);
var result = DBFactory.GetDatabase().ExecuteScalarFromCommand(DBFactory.GetDatabase().GetCommandFromParams(sqlQRY, searchParam.Parameters));
if (result != null)
{
return(result.ToString());
}
else
{
return(string.Empty);
}
}
private QueryParamCollection BuildSearchList()
{
var searchParams = new QueryParamCollection();
using (var controlParser = new DBControlParser(this))
{
foreach (var ctl in controlParser.GetDBControls(this))
{
var ctlValue = controlParser.GetDBControlValue(ctl);
if (ctlValue != DBNull.Value && !string.IsNullOrEmpty(ctlValue.ToString()))
{
var dbInfo = (DBControlInfo)ctl.Tag;
bool isExact = false;
switch (dbInfo.ColumnName)
{
//case DevicesCols.OSVersion:
// IsExact = true;
// break;
case DevicesCols.EQType:
isExact = true;
break;
case DevicesCols.Location:
isExact = true;
break;
case DevicesCols.Status:
isExact = true;
break;
default:
isExact = false;
break;
}
searchParams.Add(dbInfo.ColumnName, ctlValue, isExact);
}
}
return(searchParams);
}
}
public static async void NewMunisPOSearch(string po, ExtendedForm parentForm)
{
try
{
parentForm.Waiting();
if (po == "")
{
return;
}
string query = "SELECT TOP " + intMaxResults + @" pohd_pur_no, pohd_fsc_yr, pohd_req_no, pohd_gen_cm, pohd_buy_id, pohd_pre_dt, pohd_exp_dt, pohd_sta_cd, pohd_vnd_cd, pohd_dep_cd, pohd_shp_cd, pohd_tot_amt, pohd_serial
FROM poheader";
QueryParamCollection searchParams = new QueryParamCollection();
searchParams.Add("pohd_pur_no", po, true);
GridForm newGridForm = new GridForm(parentForm, "MUNIS PO Info");
using (var cmd = munisComms.GetSqlCommandFromParams(query, searchParams.Parameters))
{
using (var results = await munisComms.ReturnSqlTableFromCmdAsync(cmd))
{
if (HasResults(results, parentForm))
{
newGridForm.AddGrid("POGrid", "PO Info:", results);
newGridForm.Show();
}
}
}
}
catch (Exception ex)
{
ErrorHandling.ErrHandle(ex, System.Reflection.MethodBase.GetCurrentMethod());
}
finally
{
parentForm.DoneWaiting();
}
}
public static Device FindDeviceFromAssetOrSerial(string searchValue, FindDevType type)
{
try
{
if (type == FindDevType.AssetTag)
{
QueryParamCollection searchParam = new QueryParamCollection();
searchParam.Add(DevicesCols.AssetTag, searchValue, true);
return(new Device(DBFactory.GetDatabase().DataTableFromParameters(Queries.SelectDevicesPartial, searchParam.Parameters)));
}
else if (type == FindDevType.Serial)
{
QueryParamCollection searchParam = new QueryParamCollection();
searchParam.Add(DevicesCols.Serial, searchValue, true);
return(new Device(DBFactory.GetDatabase().DataTableFromParameters(Queries.SelectDevicesPartial, searchParam.Parameters)));
}
return(null);
}
catch (Exception ex)
{
ErrorHandling.ErrHandle(ex, System.Reflection.MethodBase.GetCurrentMethod());
return(null);
}
}
internal static void FillFromString(QueryParamCollection nvc, string s, bool urlencoded, Encoding encoding)
{
int l = (s != null) ? s.Length : 0;
int i = (s.Length > 0 && s[0] == '?') ? 1 : 0;
while (i < l)
{
// find next & while noting first = on the way (and if there are more)
int si = i;
int ti = -1;
while (i < l)
{
char ch = s[i];
if (ch == '=')
{
if (ti < 0)
{
ti = i;
}
}
else if (ch == '&')
{
break;
}
i++;
}
// extract the name / value pair
string name = null;
string value = null;
if (ti >= 0)
{
name = s.Substring(si, ti - si);
value = s.Substring(ti + 1, i - ti - 1);
}
else
{
value = s.Substring(si, i - si);
}
// add name / value pair to the collection
if (urlencoded)
{
nvc.Add(
name == null ? null : UrlDecodeStringFromStringInternal(name, encoding),
UrlDecodeStringFromStringInternal(value, encoding));
}
else
{
nvc.Add(name, value);
}
// trailing '&'
if (i == l - 1 && s[i] == '&')
{
nvc.Add(null, "");
}
i++;
}
}
internal static void ParseQueryString(string query, Encoding encoding, QueryParamCollection result)
{
if (query.Length == 0)
{
return;
}
string decoded = HtmlDecode(query);
int decodedLength = decoded.Length;
int namePos = 0;
bool first = true;
while (namePos <= decodedLength)
{
int valuePos = -1, valueEnd = -1;
for (int q = namePos; q < decodedLength; q++)
{
if (valuePos == -1 && decoded[q] == '=')
{
valuePos = q + 1;
}
else if (decoded[q] == '&')
{
valueEnd = q;
break;
}
}
if (first)
{
first = false;
if (decoded[namePos] == '?')
{
namePos++;
}
}
string name, value;
if (valuePos == -1)
{
name = null;
valuePos = namePos;
}
else
{
name = UrlDecode(decoded.Substring(namePos, valuePos - namePos - 1), encoding);
}
if (valueEnd < 0)
{
namePos = -1;
valueEnd = decoded.Length;
}
else
{
namePos = valueEnd + 1;
}
value = UrlDecode(decoded.Substring(valuePos, valueEnd - valuePos), encoding);
result.Add(name, value);
if (namePos == -1)
{
break;
}
}
}
