/// <summary> /// Returns a JSON string based on the contents of the database and user input /// </summary> /// <param name="sProcName">the name of the stored proc to invoke.</param> /// <param name="filterValue">The value to feed to the stored proc</param> /// <param name="fieldNameForDisplayedValue">The name of the field to read from the datareader; /// This value is what will be displayed in the auto-complete box. This is the column whose matches /// you are expecting from the datatable. /// If you only provide this value, you get: /// ["value_0",...,"value_N-1"]</param> /// <param name="fieldNameForInsertedValue">Optional. The name of the field to read from the datareader; /// This value is what will be inserted into the auto-complete box when you select fieldNameForDisplayedValue. /// When your provide this value, you get: /// [ /// {label:"dR[fieldNameForDisplayedValue]_0", value:"dR[fieldNameForInsertedValue]_0"}, /// {label:"dR[fieldNameForDisplayedValue]_N-1", value:"dR[fieldNameForInsertedValue]_N-1"}, /// ]</param> /// <param name="ps">List of sql parameters required by the stored procedure. /// If you use this parameter, you are responsible for providing all parameters required by the stored procedure, /// including @filter. In this scenario, parameters filterValue and pName are ignored.</param> /// <param name="pName">Parameter name; default is @filter</param> /// <returns></returns> public string[] GetJSONFromDB(string sProcName, string filterValue, string fieldNameForDisplayedValue, string fieldNameForInsertedValue = "", string pName = "@filter", List <SqlParameter> ps = null) { try { clsDB xdB = new clsDB(); SqlCommand cmd = new SqlCommand(); if (ps == null) { ps = new List <SqlParameter>(); ps.Add(new SqlParameter("@filter", filterValue)); } List <string> lstResult = new List <string>(); using (xdB.OpenConnection()) { using (SqlDataReader dR = (SqlDataReader)xdB.ExecuteSP(sProcName, ps, clsDB.SPExMode.READER, ref cmd)) { if (dR != null && dR.HasRows) { while (dR.Read()) { if (fieldNameForInsertedValue == "") { lstResult.Add(xdB.Fld2Str(dR[fieldNameForDisplayedValue])); } else { lstResult.Add("{label:" + xdB.Fld2Str(dR[fieldNameForDisplayedValue]) + ", value:" + xdB.Fld2Str(dR[fieldNameForInsertedValue]) + "}"); } } } } } if (lstResult.Count > 0) { return(lstResult.ToArray()); } else { return(new string[0]); } } catch (Exception ex) { string strErr = ex.Message + ex.StackTrace; return(new string[0]); } }
///Returns list of all addresses in the database public static string getAddressList(string input) { try { clsDB myDB = new clsDB(); SqlCommand cmd = new SqlCommand(); StringBuilder sB = new StringBuilder(); using (myDB.OpenConnection()) { using (SqlDataReader dR = (SqlDataReader)myDB.ExecuteSP(DBK.SP.spGETKVPFULLADDRESS, new List <SqlParameter>(), clsDB.SPExMode.READER, ref cmd)) { if (dR != null && dR.HasRows) { while (dR.Read()) { sB.Append("<p>" + myDB.Fld2Str(dR[DBK.valDISPLAYEDVALUE]) + "</p>"); } } else { sB.Append("<p>No addresses found when executing stored procedure " + DBK.SP.spGETKVPFULLADDRESS + " in WebMethod getAddressList.</p>"); } } } return(sB.ToString()); } catch (Exception ex) { return((ex.Message + ex.StackTrace).Replace(AAAK.vbCRLF, DynControls.html_linebreak_string())); } }
/// <summary> /// Looks for searchVal in d; if not found, looks in database. If found, records value in d; if not, returns default value /// </summary> /// <param name="d">Dictionary that we search for first. </param> /// <param name="searchVal">The value (key) in the dictionary we are searching for; if found, we get d(key)</param> /// <param name="spName">The stored proc to use if searchVal is not in the dictionary</param> /// <param name="fldName_key">The database field name to use as the dictionary key</param> /// <param name="fldName_value">The database field name to use as the dictionary value</param> /// <param name="defaultValue">The default value to return if not searchVal not found in either the dictioanry /// or the database</param> /// <param name="spParamName">Name of the parameter in the stored procedure. Leave this blank, and the function will use /// @[fldName_key]</param> /// <param name="putDefaultInDictionary">If not found in the dictionary or in the database, then if this set TRUE, searchVal /// is put in the database with the default value, so that you don't query the database for the same searchVal next time this /// function is called</param> /// <returns></returns> public string GetDBValueFromDictionary(ref Dictionary <string, string> d, string searchVal, string spName, string fldName_key, string fldName_value, string defaultValue, string spParamName = "", Boolean putDefaultInDictionary = true) { try { if (d.ContainsKey(searchVal)) { return(d[searchVal]); } else { clsDB xDB = new clsDB(); List <SqlParameter> ps = new List <SqlParameter>(); SqlCommand cmd = new SqlCommand(); if (spParamName == "") { spParamName = "@" + fldName_key; } ps.Add(new SqlParameter(spParamName, searchVal)); using (xDB.OpenConnection()) { using (SqlDataReader dR = (SqlDataReader)xDB.ExecuteSP(spName, ps, clsDB.SPExMode.READER, ref cmd)) { if (dR != null && dR.HasRows) { dR.Read(); string foundVal = xDB.Fld2Str(dR[fldName_value]); d.Add(searchVal, foundVal); return(foundVal); } } } } //if we made it this far, we should return the default value. BUT: if (putDefaultInDictionary) { d.Add(searchVal, defaultValue); } return(defaultValue); } catch (Exception ex) { return(defaultValue); } }
/// <summary> /// Creates a new Assy BOM Object; /// If successful, look at property BOM; if it fails, check property ErrorMsg /// Call method Upload to upload the object to the database /// </summary> /// <param name="topLevelName">aka the Product Name</param> /// <param name="assyPN">Part Number of the top level name; leave this blank, and the constructor will use /// the first AssyPN it encounters i the file specified in filePath as the Part Number for the assembly.</param> /// <param name="assyRev">Revision of the Assembly</param> /// <param name="uploaderID">ID of the user uploading the BOM</param> /// <param name="assyBU">BU of the BOM</param> /// <param name="bomRev">Revision of the Assembly BOM</param> /// <param name="reasonForRev">reason for the revision; OK to leave this blank, in which case /// the constructor will use the default key for Initial Release.</param> /// <param name="filePath">Path and file of the tsv file containing BOM information.</param> public clsAssyBOM(string topLevelName, string assyPN, string assyRev, int uploaderID, int assyBU, int bomRev, string reasonForRev, string filePath) { try { StringBuilder sB = new StringBuilder(); m_topLevelName = topLevelName.ToUpper(); m_assyPN = assyPN.ToUpper(); m_assyRev = assyRev.ToUpper(); m_uploadedByID = uploaderID; m_assyBUID = assyBU; m_bomRev = bomRev; m_ReasonForRev = reasonForRev; clsFileUtil f = new clsFileUtil(filePath); m_BOM = new Dictionary <string, Dictionary <string, AssyBomLineItem> >(); using (StreamReader sR = f.OpenAndRead()) { string l = sR.ReadLine(); //First line is the header row; position of the headers tells us //which columns to look at int colAssyPN = -1; int colRefDes = -1; int colPN = -1; int colQ = -1; int colBOMNotes = -1; int colInum = -1; string[] arr = l.Split('\t'); for (int i = 0; i <= arr.GetUpperBound(0); i++) { switch (arr[i].ToLower()) { case "bom item id": colAssyPN = i; break; case "ref designator": colRefDes = i; break; case "component id": colPN = i; break; case "qty per assy": colQ = i; break; case "bom notes": colBOMNotes = i; break; case "i#": colInum = i; break; } } //Loop the remainder of the file while (!sR.EndOfStream) { l = sR.ReadLine(); arr = l.Split('\t'); string aPN = arr[colAssyPN]; if (!m_BOM.ContainsKey(aPN)) { Dictionary <string, AssyBomLineItem> d = new Dictionary <string, AssyBomLineItem>(); m_BOM.Add(aPN, d); if (m_assyPN == "") { m_assyPN = aPN; } } m_BOM[aPN].Add(arr[colPN], new AssyBomLineItem(arr[colPN], -1, arr[colRefDes].ToString().Replace(AAAK.DQ, ""), arr[colBOMNotes], int.Parse(arr[colQ]))); sB.Append(arr[colPN] + ","); } string csvPN = sB.ToString().Substring(0, sB.Length - 1); //Execute the stored procedure to: //1) Determine if there are any invalid part numbers //2) Get the ID for each PN List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(new SqlParameter("@csvPN", csvPN)); using (xDB.OpenConnection()) { using (SqlDataReader dR = (SqlDataReader)xDB.ExecuteSP(DBK.SP.spOTSGETPNIDS, ps, clsDB.SPExMode.READER, ref cmd)) { if (dR != null && dR.HasRows) { while (dR.Read()) { if (xDB.Fld2Str(dR[DBK.ID]) == "") { m_lstUndefinedPNs.Add(dR[DBK.SP_COLALIAS.PN].ToString()); } else { foreach (string assyKy in m_BOM.Keys) { if (m_BOM[assyKy].ContainsKey(xDB.Fld2Str(dR[DBK.SP_COLALIAS.PN]))) { m_BOM[assyKy][dR[DBK.SP_COLALIAS.PN].ToString()].PNID = Int64.Parse(dR[DBK.ID].ToString()); } } if (int.Parse(dR[DBK.keyPARTSTATUS].ToString()) == 3) { m_lstObsoleteParts.Add(dR[DBK.SP_COLALIAS.PN].ToString()); } } } } } } } f.Delete(); } catch (Exception ex) { m_errMsg.Append("Error in clsAssyBOM.New:" + AAAK.vbCRLF + ex.Message + AAAK.vbCRLF + ex.StackTrace); } }
protected void Page_Load(object sender, EventArgs e) { //check for any url encoded parameters string foundIDData = ""; if (Request.QueryString.HasKeys()) { try { CustomCode x = new CustomCode(); string targetID = Request.QueryString["ID"]; //The following is a bogus division that will go at the end of the displayed page so that the client can't obtain the ID string htmlForID = "<div " + DynControls.encodeProperty("id", "x_" + targetID) + DynControls.encodeProperty("class", "getID") + " ></div>"; //Get the PN associated with the targetID clsDB myDB = new clsDB(); SqlCommand cmd = new SqlCommand(); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(new SqlParameter("@pnID", Int64.Parse(targetID))); string pageHeader = ""; using (myDB.OpenConnection()) { using (SqlDataReader dR = (SqlDataReader)myDB.ExecuteSP(DBK.SP.spOTSGETPNINFO, ps, clsDB.SPExMode.READER, ref cmd)) { if (dR != null && dR.HasRows) { dR.Read(); pageHeader = myDB.Fld2Str(dR[DBK.strPARTNUMBER]); } } } if (Request.QueryString["INV"] != null) { pageHeader = DynControls.html_header_string(pageHeader + " Inventory", 1); foundIDData = pageHeader + x.InvForPN(targetID) + htmlForID; } else if (Request.QueryString["INVH"] != null) { pageHeader = DynControls.html_header_string(pageHeader + " Inventory History", 1); foundIDData = pageHeader + x.MakePartNumberInventoryHistoryTable(Int64.Parse(targetID)) + htmlForID; } else { foundIDData = x.getHTMLForPartNumberID(targetID); } otsdivs.Controls.Add(new LiteralControl(foundIDData)); foreach (Control c in divMenuButtons.Controls) { divMenuButtons.Controls.Remove(c); } return; } catch (Exception ex) { string x = ex.Message + ex.StackTrace; } } //Create three panels and add them to the existing div Panel divOTSNew = new Panel(); divOTSNew.ID = "divOTSNew"; divOTSNew.Style.Add(HtmlTextWriterStyle.Display, "none"); otsdivs.Controls.Add(divOTSNew); //There are two panels in divOTSNew: divOTSNewIn and divOTSNewOut Panel divOTSNewIn = new Panel(); divOTSNewIn.ID = "divOTSNewIn"; //Get the input controls from the database for divOTSNewIn... DynControls.GenerateControlsFromDatabase(DBK.AppKeys.GET_NEWOTSPN, divOTSNewIn); //Add a Submit button divOTSNewIn.Controls.Add(DynControls.html_button("btnOTSNewIn", "SUBMIT", "inputButton", true, AAAK.DISPLAYTYPES.BLOCK, "Create an OTS Part Number", "frmNewOTS")); //...and add a div for the ajax output Panel divOTSNewOut = new Panel(); divOTSNewOut.ID = "divOTSNewOut"; divOTSNewOut.Controls.Add(new LiteralControl("<p>Enter the information on the left, then press Submit to get your new OTS Part Number.</p>")); //Add these two sub panels divOTSNew.Controls.Add(divOTSNewIn); divOTSNew.Controls.Add(divOTSNewOut); //***** End divOTSNew //***** Start divOTSFind Panel divOTSFind = new Panel(); divOTSFind.ID = "divOTSFind"; divOTSFind.Style.Add(HtmlTextWriterStyle.Display, "none"); //divOTSFind.Style.Add(HtmlTextWriterStyle.OverflowX, "auto"); otsdivs.Controls.Add(divOTSFind); //Now: The html in this div is simple, but as the user make selections, the AJAX calls increase the //complexity of the children's html. //This div has 3 divs. ///divOTSFind Child Div 1: Panel divSearch = new Panel(); divSearch.ID = "divSearch"; DynControls.GenerateControlsFromDatabase(DBK.AppKeys.SEARCH_OTS, divSearch, null, "", -1, true); //Create the search button string btnSearchHtmlString = DynControls.html_button_string("btnLook", "SEARCH", "searchButton", true, AAAK.DISPLAYTYPES.BLOCK, form: "frmSearchOTS"); divSearch.Controls.Add(new LiteralControl(btnSearchHtmlString)); ///divOTSFind Child Div 2: ///The message div Panel divSearchMsg = new Panel(); divSearchMsg.Style.Add(HtmlTextWriterStyle.Display, "block"); divSearchMsg.ID = "divMessage"; divSearchMsg.Controls.Add(new LiteralControl("<p " + DynControls.encodeProperty("id", "searchmsg") + ">" + "Enter search criteria above to find Part Number Information.</p>")); ///divOTSFind Child Div 3: /// Panel divLook = new Panel(); divLook.Style.Add(HtmlTextWriterStyle.Display, "block"); divLook.Style.Add(HtmlTextWriterStyle.OverflowX, "auto"); divLook.ID = "divLook"; /// divLook's html will be determine when user presses SEARCH ///Add the three divs to divOTSFind divOTSFind.Controls.Add(divSearch); divOTSFind.Controls.Add(new LiteralControl("<div></div>")); divOTSFind.Controls.Add(divSearchMsg); divOTSFind.Controls.Add(divLook); Panel divOTSAdmin = new Panel(); divOTSAdmin.ID = "divOTSAdmin"; divOTSAdmin.Controls.Add(new LiteralControl("<p>Admin</p>")); divOTSAdmin.Style.Add(HtmlTextWriterStyle.Display, "none"); otsdivs.Controls.Add(divOTSAdmin); }