/************************************************************************/
/* 根据传入参数存入数据库
* dt 时间
* carID 车组编号
* opname 操作人员
* fs 故障描述
* resovle 解决方法
* runresove 运行解决方法
* NP 现场照片list集合 (作为 static 可以直接用)
* EP 电路图照片list 集合 (同上)
* PP 部件照片集合list (同上)
/************************************************************************/
public int AddRecord(DateTime dt, string carID, string opname, string fs, string resovel, string runresovle)
{
Operator op = new Operator();
op.record.time = dt;
op.record.carID = carID;
op.record.worker = opname;
op.fault.fault = fs;
op.solution.solution = resovel;
op.solution.runSolution = runresovle;
foreach (NP n in NP)
{
op.fault.faultImgs.Add(n.src);
}
foreach (EP e in EP)
{
op.solution.criImgs.Add(e.src);
}
foreach (PP p in PP)
{
Parts ps = new Parts();
ps.partsName = p.name;
ps.partsImgs.Add(p.src);
op.parts.Add(ps);
}
//op.fault.faultKey = "null";
//string key = fs.Substring(fs.IndexOf("代码"));
string key = getValue(fs);
op.fault.faultKey = key;
SqlHelper.addAll(op);
PP.Clear();
NP.Clear();
EP.Clear();
return 0;
}
public static List<Operator> findAllByTime(DateTime start, DateTime end)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record where time between @start and @end";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
SqlDataReader reader = cmd.ExecuteReader();
List<Operator> opList = new List<Operator>();
while (reader.Read())
{
Operator op = new Operator();
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.record.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
opList.Add(op);
}
return opList;
}
finally
{
conn.Close();
}
}
public static List<Operator> findAllByFaultKey(string key)
{
key = "%" + key + "%";
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record R left join fault F on R.fault_ID=F.fault_ID where fault_key like @key";
cmd.Parameters.AddWithValue("@key", key);
SqlDataReader reader = cmd.ExecuteReader();
List<Operator> opList = new List<Operator>();
while (reader.Read())
{
Operator op = new Operator();
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.record.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
opList.Add(op);
}
return opList;
}
finally
{
conn.Close();
}
}
public static Operator findAllByRecordID(int recordID)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record where record_ID=@recordID";
cmd.Parameters.AddWithValue("@recordID", recordID);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Operator op = new Operator();
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.record.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
return op;
}
return null;
}
finally
{
conn.Close();
}
}
public static List<Operator> findAllByCarID(string carID)
{
SqlConnection conn = null;
SqlCommand cmd = null;
carID = "%" + carID + "%";
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record where car_ID like @carID";
cmd.Parameters.AddWithValue("@carID", carID);
SqlDataReader reader = cmd.ExecuteReader();
List<Operator> opList = new List<Operator>();
while (reader.Read())
{
Operator op = new Operator();
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.record.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
opList.Add(op);
}
return opList;
}
finally
{
conn.Close();
}
}
public static List<Operator> findAll(string carID, DateTime start, DateTime end, string key, string parts)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
int t = 0;
string s1 = " R.car_ID like @carID";
string s2 = " R.time between @start and @end";
string s3 = " F.fault_key like @key";
string s4 = " P.parts_name=@parts";
string select = "select * from record R left join fault F on R.fault_ID=F.fault_ID left join solution S on R.solution_ID=S.solution_ID right join solution_parts SP on S.solution_ID=SP.solution_ID right join parts P on P.parts_ID=SP.Parts_ID";
if (!(carID == "" && (start.Equals(new DateTime(1900, 1, 1))) && key == "" && parts == ""))
{
select += " where";
if (carID != null && carID != "")
{
if (t != 0) select += " and";
select += s1;
t++;
}
if (!start.Equals(new DateTime(1900, 1, 1)))
{
if (t != 0) select += " and";
select += s2;
t++;
}
if (key != null && key != "")
{
if (t != 0) select += " and";
select += s3;
t++;
}
if (parts != null && parts != "")
{
if (t != 0) select += " and";
select += s4;
t++;
}
}
select += " order by R.record_ID asc";
cmd.CommandText = select;
if (carID != null && carID != "")
{
carID = "%" + carID + "%";
cmd.Parameters.AddWithValue("@carID", carID);
}
if (start != null)
{
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
}
if (key != null && key != "")
{
key = "%" + key + "%";
cmd.Parameters.AddWithValue("@key", key);
}
if (parts != null && parts != "")
{
cmd.Parameters.AddWithValue("@parts", parts);
}
string te = cmd.CommandText;
int i1 = -1;
SqlDataReader reader = cmd.ExecuteReader();
List<Operator> opList = new List<Operator>();
while (reader.Read())
{
Operator op = new Operator();
if (!reader.IsDBNull(1))
{
int i2 = reader.GetInt32(reader.GetOrdinal("record_ID"));
if (i1 != i2)
{
i1 = i2;
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.solution.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
opList.Add(op);
}
}
}
return opList;
}
finally
{
conn.Close();
}
}
public static int addAll(Operator op)
{
int faultID = 0;
int solutionID = 0;
int i = 0;
addSolution(op.solution);
solutionID = findSolutionID();
op.record.solutionID = solutionID;
foreach (Parts p in op.parts)
{
SolutionParts sp = new SolutionParts();
addParts(p);
sp.solutionID = solutionID;
sp.partsID = findPartsByPartsName(p.partsName).partsID;
op.solutionParts.Add(sp);
}
foreach (SolutionParts sp in op.solutionParts)
{
addSolutionParts(sp);
}
addFault(op.fault);
faultID = findFaultID();
op.record.faultID = faultID;
i = addRecord(op.record);
return i;
}
public static int updateAll(Operator op1)
{
Operator op2 = findAllByRecordID(op1.record.recordID);
int i = 1;
deleteAll(op2.record.recordID);
i = addAll(op1);
if (i != 0)
{
return 0;
}
else
{
return 1;
}
}
public static List<Operator> getAll()
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record";
SqlDataReader reader = cmd.ExecuteReader();
List<Operator> opList = new List<Operator>();
while (reader.Read())
{
Operator op = new Operator();
op.record = findRecordByRecordID(reader.GetInt32(reader.GetOrdinal("record_ID")));
op.fault = findFaultByFaultID(op.record.faultID);
op.solution = findSolutionBySolutionID(op.record.solutionID);
op.solutionParts = findSolutionPartsBySolutionID(op.solution.solutionID);
op.parts = findPartsBySolutionID(op.record.solutionID);
opList.Add(op);
}
return opList;
}
finally
{
conn.Close();
}
}
