public static string setMarketPlace(string model_id, string model_name, string category_id) { string request = ""; var _sql = new StringBuilder(); if (!string.IsNullOrEmpty(model_name) && !string.IsNullOrEmpty(category_id)) { try { _sql.Clear(); _sql.AppendLine(string.Format("DELETE FROM Category_Model where model_name='{0}' ", model_name)); _sql.AppendLine(" and Category_id in('7cb22a45-ffb1-4bf6-928e-4e743bf87c15','5e4cbeae-f003-41d1-b9cf-1dc0baefa4ec','fed9a697-c31c-4fee-b112-74ad00b2815f','3a7cf93c-0bd4-4fa3-93b1-1345682be707')"); var _request1 = SqlProvider.dbExecuteNoQuery("PIS", _sql.ToString()); var dt_seq = SqlProvider.dbGetDataTable("PIS", string.Format("select isnull(max(SEQ),'') as seq from Category_Model where Category_id='{0}'", category_id)); var dt_ID = SqlProvider.dbGetDataTable("PIS", "select isnull(max(ID),'') as ID from Category_Model"); int ID = string.IsNullOrEmpty(dt_ID.Rows[0][0].ToString()) ? 0 + 1 : Convert.ToInt32(dt_ID.Rows[0][0]) + 1; int seq = string.IsNullOrEmpty(dt_seq.Rows[0][0].ToString()) ? 0 + 1 : Convert.ToInt32(dt_seq.Rows[0][0]) + 1; _sql.Clear(); _sql.AppendLine(string.Format("INSERT INTO Category_Model VALUES ({0},'{1}','{2}','{3}'", ID, model_id, model_name, category_id)); _sql.AppendLine(string.Format(",{0},GETDATE(),'*****@*****.**','')", seq)); var _request = SqlProvider.dbExecuteNoQuery("PIS", _sql.ToString()); } catch (Exception e) { request = "setMarketPlace failed : " + e.ToString(); } } else { request = "setMarketPlace is failed : model name: " + model_name + " & category id: " + category_id; } return(request); }
public static string DeleteNode(string GUID, string NodeType) { UpdateDBResult res = new UpdateDBResult(); try { CategoryTypes type = CategoryTypes.Root; if (Enum.TryParse <CategoryTypes>(NodeType, out type) == true) { switch (type) { case CategoryTypes.Category: case CategoryTypes.Component: SqlProvider.dbExecuteNoQuery("CBOMV2", "delete from CBOM_CATALOG_V2 where ID = '" + GUID + "'"); break; case CategoryTypes.Root: default: break; } } res.IsUpdated = true; } catch (Exception ex) { res.IsUpdated = false; res.ServerMessage = ex.Message; } return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); }
public static string ReOrderBySeq(string GUID) { UpdateDBResult res = new UpdateDBResult(); String select_str = String.Empty; try { select_str = " DECLARE @Child hierarchyid " + " SELECT @Child = HIE_ID FROM CBOM_CATALOG_V2 " + " WHERE ID = '" + GUID + "' " + " SELECT * FROM CBOM_CATALOG_V2 " + " WHERE HIE_ID.GetAncestor(1) = @Child " + " ORDER BY SEQ_NO "; DataTable dt = SqlProvider.dbGetDataTable("CBOMV2", select_str); if (dt != null && dt.Rows.Count > 0) { int seq = 0; String str1 = String.Empty; List <String> str2 = new List <string>(); foreach (DataRow d in dt.Rows) { str1 += " WHEN '" + d["CATALOG_NAME"].ToString() + "' THEN '" + seq.ToString() + "' "; str2.Add("'" + d["CATALOG_NAME"].ToString() + "'"); seq++; } String update_str = " UPDATE CBOM_CATALOG_V2 " + " SET SEQ_NO = CASE CATALOG_NAME " + str1 + " ELSE SEQ_NO " + " END " + " WHERE CATALOG_NAME IN " + "(" + String.Join(", ", str2.ToArray()) + ")" + "; "; SqlProvider.dbExecuteNoQuery("CBOMV2", update_str); res.IsUpdated = true; } else { res.IsUpdated = false; res.ServerMessage = "Children nodes not found."; } } catch (Exception ex) { res.IsUpdated = false; res.ServerMessage = ex.Message; } return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); }
public static string DropTreeNode(string parentid, string currentid, string currentseq, string targetid, string targetseq, string point) { UpdateDBResult res = new UpdateDBResult(); int FinalSeq = 0; try { if (Convert.ToInt32(targetseq) < Convert.ToInt32(currentseq)) { FinalSeq = point.Equals("top", StringComparison.OrdinalIgnoreCase) ? Convert.ToInt32(targetseq) : Convert.ToInt32(targetseq) + 1; } else if (Convert.ToInt32(targetseq) > Convert.ToInt32(currentseq)) { FinalSeq = point.Equals("top", StringComparison.OrdinalIgnoreCase) ? Convert.ToInt32(targetseq) - 1 : Convert.ToInt32(targetseq); } else { res.IsUpdated = false; res.ServerMessage = "Invalid operation, please try again."; return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); } // final seq = itself, no need to do anything, return false. if (FinalSeq == Convert.ToInt32(currentseq)) { res.IsUpdated = false; res.ServerMessage = "Invalid operation - moving to current sequence."; return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); } String str = " DECLARE @ID hierarchyid " + " SELECT @ID = HIE_ID " + " FROM CBOM_CATALOG_V2 WHERE ID = '" + parentid + "' " + " update CBOM_CATALOG_V2 SET SEQ_NO = SEQ_NO " + (FinalSeq > Convert.ToInt32(currentseq) ? " -1 " : " +1 ") + " WHERE HIE_ID.GetAncestor(1) = @ID " + " AND SEQ_NO >= '" + (FinalSeq > Convert.ToInt32(currentseq) ? Convert.ToInt32(currentseq) : FinalSeq) + "'" + " AND SEQ_NO <= '" + (FinalSeq > Convert.ToInt32(currentseq) ? FinalSeq : Convert.ToInt32(currentseq)) + "' "; SqlProvider.dbExecuteNoQuery("CBOMV2", str); SqlProvider.dbExecuteNoQuery("CBOMV2", String.Format(" update CBOM_CATALOG_V2 SET SEQ_NO = " + FinalSeq + " where ID = '" + currentid + "' ")); res.IsUpdated = true; } catch (Exception ex) { res.IsUpdated = false; res.ServerMessage = ex.Message; } return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); }
public static string UpdateSelectedNode(string GUID, string CategoryID, string Desc) { UpdateDBResult res = new UpdateDBResult(); try { String str = "update CBOM_CATALOG_V2 set CATALOG_NAME = N'" + CategoryID + "', CATALOG_DESC = N'" + Desc + "' where ID = N'" + GUID + "'"; SqlProvider.dbExecuteNoQuery("CBOMV2", str); res.IsUpdated = true; } catch (Exception ex) { res.IsUpdated = false; res.ServerMessage = ex.Message; } return(Newtonsoft.Json.JsonConvert.SerializeObject(res)); }