private static void AddDefNodeToTree(List <InfoRow> defList, int parentId, DefectTreeNode parent) { foreach (var t in defList) { if (t.ParentId != parentId) { continue; } var child = parent.AddChild(new InfoRow("", t.Description, t.ParentId, t.CGrConstr, t.CountOfColumns)); AddDefNodeToTree(defList, t.CGrConstr, child); } }
private static DefectTreeNode BuildDefectTree() { try { var defList = new List <InfoRow>(); using (var connection = new SqliteConnection(ConnectionClass.NewDatabasePath)) { /** 1) Построение дерева по типам конструкций*/ var command = connection.CreateCommand(); command.CommandText = string.Format("select TABLE_NAMES.C_GR_CONSTR, TABLE_NAMES.SYS_NAME, TABLE_NAMES.DESCRIPTION, " + " TABLE_NAMES.PARENT_VIEW, PARENT_ID, S_GR_CONSTR.ITEM_TYPE from TABLE_NAMES\n" + " left outer join S_GR_CONSTR on S_GR_CONSTR.C_GR_CONSTR=TABLE_NAMES.C_GR_CONSTR\n" + " where TABLE_NAMES.PARENT_VIEW != -1 and TABLE_NAMES.C_GR_CONSTR in (select distinct C_GR_CONSTR from S_DEF4CONSTR where C_GR_CONSTR < 100000 " + " and C_GR_CONSTR > 0\n" + " union all\n" + " select distinct PARENT_GROUP from S_GR_CONSTR where PARENT_GROUP < 100000 and PARENT_GROUP > 0)\n" + " group by TABLE_NAMES.C_GR_CONSTR, TABLE_NAMES.SYS_NAME, TABLE_NAMES.DESCRIPTION, TABLE_NAMES.PARENT_VIEW, PARENT_ID, ITEM_TYPE"); command.CommandTimeout = 30; command.CommandType = System.Data.CommandType.Text; connection.Open(); using (var datareader = command.ExecuteReader()) { if (datareader.HasRows) { while (datareader.Read()) { defList.Add(new InfoRow("", Convert.ToString(datareader["DESCRIPTION"]), Convert.ToInt32(datareader["PARENT_ID"]), Convert.ToInt32(datareader["C_GR_CONSTR"]), datareader["ITEM_TYPE"] != DBNull.Value ? Convert.ToInt32(datareader["ITEM_TYPE"]) : -100)); } } } connection.Close(); /** 2) Добавление дефектов к конструкциям */ command = connection.CreateCommand(); command.CommandText = "select C_GR_CONSTR, TEXT, PARENT_GROUP, ITEM_TYPE from S_GR_CONSTR where C_GR_CONSTR > 100000"; command.CommandTimeout = 30; command.CommandType = System.Data.CommandType.Text; connection.Open(); using (var datareader = command.ExecuteReader()) { if (datareader.HasRows) { while (datareader.Read()) { defList.Add(new InfoRow("", Convert.ToString(datareader["TEXT"]), Convert.ToInt32(datareader["PARENT_GROUP"]), Convert.ToInt32(datareader["C_GR_CONSTR"]), Convert.ToInt32(datareader["ITEM_TYPE"]))); } } } connection.Close(); } DefectTreeNode root = null; var parentId = -1; foreach (var row in defList) { if (row.CGrConstr != 10) { continue; } root = new DefectTreeNode(new InfoRow("", row.Description, row.ParentId, row.CGrConstr, row.CountOfColumns)); parentId = row.CGrConstr; break; } foreach (var t in defList) { if (t.ParentId != parentId) { continue; } if (root == null) { continue; } var child = root.AddChild(new InfoRow("", t.Description, t.ParentId, t.CGrConstr, t.CountOfColumns)); AddDefNodeToTree(defList, t.CGrConstr, child); } return(root); } catch (Exception exception) { Debug.WriteLine(exception.Message, exception.InnerException, exception.StackTrace); return(null); } }
public static DefectTreeNode GetDefectTree() { return(_defectTreeNode ?? (_defectTreeNode = BuildDefectTree())); }
private List <Ais7IssoDefect> CreateDefectList(int cIsso) { try { // Параметр который будем возвращать var defectsList = new List <Ais7IssoDefect>(); using (var connection = new SqliteConnection(ConnectionClass.NewDatabasePath)) { var command = connection.CreateCommand(); command.CommandText = "select i_defect.c_isso, i_defect.n_def, i_defect.c_defect, i_defect.c_gr_constr, " + "coalesce(ord, s_gr_constr.MAIN_CONSTR_ID) as ord, i_defect.n_constr, i_def_mod.date, " + "i_defect.B, i_defect.B1, i_defect.D, i_defect.D1, i_defect.R, i_defect.R1, i_defect.G, i_defect.G1, " + "i_defect.DATE as date_defect, i_defect.DATEF as date_end, i_def_mod.l_def, i_def_mod.w_def, s_defect.n_defect, n_rem, " + "sn_unit_dime, v_rem, count(i_foto_def.n_def) as fotocount, s_gr_constr.MAIN_CONSTR_ID " + "from i_defect " + "left outer join i_def_mod on " + "i_def_mod.c_isso = i_defect.c_isso and " + "i_def_mod.n_def = i_defect.n_def and " + "i_def_mod.date = (select max(date) from i_def_mod d2 where " + "d2.c_isso = i_defect.c_isso and " + "d2.n_def = i_defect.n_def) " + "left outer join s_defect on s_defect.c_defect = i_defect.c_defect " + "left outer join s_rem on s_rem.c_rem = i_def_mod.c_rem " + "left outer join s_unit_dimension on s_unit_dimension.c_unit_dimen = s_rem.ind_unit " + "left outer join i_foto_def on i_foto_def.c_isso = i_defect.c_isso and i_foto_def.n_def = i_defect.n_def and " + "i_foto_def.date = i_def_mod.date " + "left outer join s_gr_constr on s_gr_constr.C_GR_CONSTR = i_defect.C_GR_CONSTR " + $"where i_defect.c_isso = {cIsso} " + "group by i_defect.c_isso, i_defect.n_def, i_defect.c_defect, i_defect.c_gr_constr, i_defect.n_constr, i_def_mod.date, " + "i_defect.B, i_defect.B1, i_defect.D, i_defect.D1, i_defect.R, i_defect.R1, i_defect.G, i_defect.G1, " + "i_defect.DATE, i_defect.DATEF, i_def_mod.l_def, n_rem, sn_unit_dime, v_rem, l_def, i_def_mod.w_def, n_defect, " + "s_gr_constr.c_gr_constr, ord, s_gr_constr.MAIN_CONSTR_ID order by ord, i_defect.n_constr, i_defect.n_def"; command.CommandTimeout = 30; command.CommandType = System.Data.CommandType.Text; var commandParam = connection.CreateCommand(); commandParam.CommandText = "select i_defect.n_def, s_defparam.c_defparam, " + " n_defparam, category, value, sn_unit_dime, min(s_defparamvalue.c_unit_dimen) c_unit_dimen " + "from i_def_descr left outer join i_def_mod on i_def_descr.c_isso = i_def_mod.c_isso and i_def_descr.n_def = " + " i_def_mod.n_def and i_def_descr.date=i_def_mod.date " + " left outer join i_defect on i_def_mod.c_isso = i_defect.c_isso and i_def_mod.n_def = i_defect.n_def " + " left outer join s_defparam on s_defparam.c_defparam = i_def_descr.c_defparam " + " left outer join s_defparamvalue on s_defparamvalue.c_defect=i_defect.c_defect and " + " s_defparamvalue.c_gr_constr = i_defect.c_gr_constr and " + " s_defparamvalue.c_defparam = i_def_descr.c_defparam " + " left outer join s_unit_dimension on s_defparamvalue.c_unit_dimen = s_unit_dimension.c_unit_dimen " + "where i_def_mod.date = (select max(date) from i_def_mod d2 where " + " d2.c_isso = i_defect.c_isso and " + $" d2.n_def = i_defect.n_def) and i_def_descr.c_isso = {cIsso}" + " group by i_defect.n_def, s_defparam.c_defparam, n_defparam, category, value, sn_unit_dime"; commandParam.CommandTimeout = 30; commandParam.CommandType = System.Data.CommandType.Text; connection.Open(); using (var datareader = command.ExecuteReader()) { if (datareader.HasRows) { var indexDef = 1; while (datareader.Read()) { var issoDefect = new Ais7IssoDefect { Ord = indexDef, NDef = Convert.ToInt16(datareader["N_DEF"]), HasPhoto = datareader["fotocount"] != DBNull.Value && Convert.ToInt16(datareader["fotocount"]) > 0 }; var cGrConstr = Convert.ToInt32(datareader["C_GR_CONSTR"]); DefectTreeNode.Path.Clear(); issoDefect.IsDefCompleted = datareader["date_end"] != DBNull.Value; issoDefect.MainConstrId = Convert.ToInt32(datareader["MAIN_CONSTR_ID"]); var cConstr = datareader["N_CONSTR"] != DBNull.Value ? Convert.ToInt16(datareader["N_CONSTR"]) : -100; var constr = DefectTreeNode.GetConstrFullName(DefectTreeNode, cGrConstr, cConstr); var lDef = datareader["L_DEF"] != DBNull.Value ? Convert.ToString(datareader["L_DEF"]) : ""; var mainC = DefectTreeNode.GetConstrMainName(DefectTreeNode, cGrConstr, cConstr); //issoDefect.Location = defTree.getConstrFullName(defTree, CGrConstr, c_constr); issoDefect.Location = mainC + (constr.Equals("") ? "" : ". " + constr) + (lDef.Equals("") ? "" : ". " + lDef); issoDefect.NameConstr = DefectTreeNode.GetConstrMainName(DefectTreeNode, cGrConstr, cConstr); var wDef = datareader["W_DEF"] != DBNull.Value ? Convert.ToString(datareader["W_DEF"]) : ""; issoDefect.NDefect = Convert.ToString(datareader["N_DEFECT"]) + (!wDef.Equals("") ? ". " + wDef : ""); var b = datareader["B"] != DBNull.Value ? Convert.ToInt16(datareader["B"]) : 0; var b1 = datareader["B1"] != DBNull.Value ? Convert.ToInt16(datareader["B1"]) : 0; var d = datareader["D"] != DBNull.Value ? Convert.ToInt16(datareader["D"]) : 0; var d1 = datareader["D1"] != DBNull.Value ? Convert.ToInt16(datareader["D1"]) : 0; var r = datareader["R"] != DBNull.Value ? Convert.ToInt16(datareader["R"]) : 0; var r1 = datareader["R1"] != DBNull.Value ? Convert.ToInt16(datareader["R1"]) : 0; var g = Convert.ToInt16(datareader["G"]) == 1; var g1 = Convert.ToInt16(datareader["G1"]) == 1; issoDefect.Category = $"Б{(b == b1 ? Convert.ToString(b) : $"{b}/{b1}")}, Д{(d == d1 ? Convert.ToString(d) : $"{d}/{d1}")}, Р{(r == r1 ? Convert.ToString(r) : $"{r}/{r1}")}{(g ? (!g1 ? ", (Г)" : ", Г") : "")}";