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 ? ", (Г)" : ", Г") : "")}";