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));
}