// string column = "refid,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time,update_by,update_time";
//@refid,@LineID,@DeviceID,@IsDown,@Shift,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@status,@create_by,@create_time,@update_by,@update_time
public static string CreateNew(DTRecord data)
{
string sql = " insert into DTRecord(TagID,Value,PDate,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time)values(@TagID,@Value,@PDate,@LineID,@DeviceID,@IsDown,@Shift,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@status,@create_by,@create_time)";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@TagID", data.TagID),
new SqlParameter("@Value", data.Value),
new SqlParameter("@PDate", data.PDate),
new SqlParameter("@LineID", data.LineID),
new SqlParameter("@DeviceID", data.DeviceID),
new SqlParameter("@IsDown", data.IsDown),
new SqlParameter("@Shift", data.Shift),
new SqlParameter("@Model", data.Model),
new SqlParameter("@Start_time", data.Start_time),
new SqlParameter("@End_time", data.End_time),
new SqlParameter("@Duration", data.Duration),
new SqlParameter("@Category", data.Category),
new SqlParameter("@Lev1", data.Lev1),
new SqlParameter("@Lev2", data.Lev2),
new SqlParameter("@Brand", data.Brand),
new SqlParameter("@CBrand", data.CBrand),
new SqlParameter("@Ctype", data.Ctype),
new SqlParameter("@stand_time", data.stand_time),
new SqlParameter("@Reason", data.Reason),
new SqlParameter("@Memo", data.Memo),
new SqlParameter("@status", data.status),
new SqlParameter("@create_by", data.create_by),
new SqlParameter("@create_time", DateTime.Now)
};
foreach (SqlParameter parm in paras)
{
if (parm.Value == null)
{
parm.Value = DBNull.Value;
}
}
return(MSSql.ExecInsertParaTransStr(sql, paras));
}
public static DataTable QueryByCondition(DTRecord data)
{
StringBuilder sb = new StringBuilder();
string sqlstr = "select refid,PDate,LineID,DeviceID,IsDown,Shift,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,status,create_by,create_time,update_by,update_time from DTRecord where 1=1 ";
sb.Append(sqlstr);
if (!string.IsNullOrEmpty(data.LineID))
{
sb.AppendFormat(" and LineID like '%{0}%' ", data.LineID);
}
if (!string.IsNullOrEmpty(data.DeviceID))
{
sb.AppendFormat(" and DeviceID like '%{0}%' ", data.DeviceID);
}
if (!string.IsNullOrEmpty(data.IsDown))
{
sb.AppendFormat(" and IsDown like '%{0}%' ", data.IsDown);
}
if (!string.IsNullOrEmpty(data.Shift))
{
sb.AppendFormat(" and Shift like '%{0}%' ", data.Shift);
}
if (!string.IsNullOrEmpty(data.Model))
{
sb.AppendFormat(" and Model like '%{0}%' ", data.Model);
}
if (!string.IsNullOrEmpty(data.Category))
{
sb.AppendFormat(" and Category like '%{0}%' ", data.Category);
}
if (!string.IsNullOrEmpty(data.Lev1))
{
sb.AppendFormat(" and Lev1 like '%{0}%' ", data.Lev1);
}
if (!string.IsNullOrEmpty(data.Lev2))
{
sb.AppendFormat(" and Lev2 like '%{0}%' ", data.Lev2);
}
if (!string.IsNullOrEmpty(data.Brand))
{
sb.AppendFormat(" and Brand like '%{0}%' ", data.Brand);
}
if (!string.IsNullOrEmpty(data.CBrand))
{
sb.AppendFormat(" and CBrand like '%{0}%' ", data.CBrand);
}
if (!string.IsNullOrEmpty(data.Ctype))
{
sb.AppendFormat(" and Ctype like '%{0}%' ", data.Ctype);
}
if (!string.IsNullOrEmpty(data.stand_time))
{
sb.AppendFormat(" and stand_time like '%{0}%' ", data.stand_time);
}
if (!string.IsNullOrEmpty(data.Reason))
{
sb.AppendFormat(" and Reason like '%{0}%' ", data.Reason);
}
if (!string.IsNullOrEmpty(data.Memo))
{
sb.AppendFormat(" and Memo like '%{0}%' ", data.Memo);
}
if (string.IsNullOrEmpty(data.bgntime) || string.IsNullOrEmpty(data.endtime))
{
if (string.IsNullOrEmpty(data.bgntime) && string.IsNullOrEmpty(data.endtime))
{
}
else
{
if (string.IsNullOrEmpty(data.bgntime))
{
sb.AppendFormat(" and create_time between '2000-01-11 00:00:00' and '{0}'", data.endtime);
}
else
{
sb.AppendFormat(" and create_time between '{0}' and GETDATE()+1 ", data.bgntime);
}
}
}
else
{
sb.AppendFormat(" and create_time between '{0}' and '{1}'", data.bgntime, data.endtime);
}
return(MSSql.ExecuteQueryDataTable(sb.ToString()));
}
public static DTRecord GetObject(UInt64 refid)
{
DTRecord data = new DTRecord();
DataTable dt = DTRecord.QueryDataByRefID(refid);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
else
{
data.refid = Convert.ToUInt64(dt.Rows[0]["refid"]);
data.PDate = Convert.ToDateTime(dt.Rows[0]["PDate"].ToString());
data.LineID = dt.Rows[0]["LineID"].ToString();
data.DeviceID = dt.Rows[0]["DeviceID"].ToString();
data.IsDown = dt.Rows[0]["IsDown"].ToString();
data.Shift = dt.Rows[0]["Shift"].ToString();
data.Model = dt.Rows[0]["Model"].ToString();
if (dt.Rows[0]["Start_time"] != System.DBNull.Value)
{
data.Start_time = Convert.ToDateTime(dt.Rows[0]["Start_time"].ToString());
}
if (dt.Rows[0]["End_time"] != System.DBNull.Value)
{
data.End_time = Convert.ToDateTime(dt.Rows[0]["End_time"].ToString());
}
data.Duration = Convert.ToInt32(dt.Rows[0]["Duration"].ToString());
data.Category = dt.Rows[0]["Category"].ToString();
data.Lev1 = dt.Rows[0]["Lev1"].ToString();
data.Lev2 = dt.Rows[0]["Lev2"].ToString();
data.Brand = dt.Rows[0]["Brand"].ToString();
data.CBrand = dt.Rows[0]["CBrand"].ToString();
data.Ctype = dt.Rows[0]["Ctype"].ToString();
data.stand_time = dt.Rows[0]["stand_time"].ToString();
data.Reason = dt.Rows[0]["Reason"].ToString();
data.Memo = dt.Rows[0]["Memo"].ToString();
data.status = dt.Rows[0]["status"].ToString();
data.create_by = dt.Rows[0]["create_by"].ToString();
data.update_by = dt.Rows[0]["update_by"].ToString();
DateTime time;
if (dt.Rows[0]["create_time"] != System.DBNull.Value)
{
if (DateTime.TryParse(dt.Rows[0]["create_time"].ToString(), out time))
{
data.create_time = time;
}
}
else
{
data.create_time = null;
}
return(data);
}
}
public static string UpdateDataByRefID(DTRecord data)
{
const string sql = "update DTRecord set ";
StringBuilder sb = new StringBuilder();
sb.Append(sql);
if (!string.IsNullOrEmpty(data.DeviceID))
{
sb.AppendFormat(" DeviceID='{0}' ", data.DeviceID);
}
if (!string.IsNullOrEmpty(data.Shift))
{
sb.AppendFormat(" , Shift='{0}' ", data.Shift);
}
if (!string.IsNullOrEmpty(data.Model))
{
sb.AppendFormat(" , Model='{0}' ", data.Model);
}
if (!string.IsNullOrEmpty(data.Category))
{
sb.AppendFormat(" , Category='{0}' ", data.Category);
}
if (!string.IsNullOrEmpty(data.Lev1))
{
sb.AppendFormat(" , Lev1='{0}' ", data.Lev1);
}
if (!string.IsNullOrEmpty(data.Lev2))
{
sb.AppendFormat(" , Lev2='{0}' ", data.Lev2);
}
if (!string.IsNullOrEmpty(data.Brand))
{
sb.AppendFormat(" , Brand='{0}' ", data.Brand);
}
if (!string.IsNullOrEmpty(data.CBrand))
{
sb.AppendFormat(" , CBrand='{0}' ", data.CBrand);
}
if (!string.IsNullOrEmpty(data.Ctype))
{
sb.AppendFormat(" , Ctype='{0}' ", data.Ctype);
}
if (!string.IsNullOrEmpty(data.stand_time))
{
sb.AppendFormat(" , stand_time='{0}' ", data.stand_time);
}
if (!string.IsNullOrEmpty(data.Reason))
{
sb.AppendFormat(" , Reason='{0}' ", data.Reason);
}
if (!string.IsNullOrEmpty(data.Memo))
{
sb.AppendFormat(" , Memo='{0}' ", data.Memo);
}
if (!string.IsNullOrEmpty(data.status))
{
sb.AppendFormat(" , status='{0}' ", data.status);
}
if (!string.IsNullOrEmpty(data.update_by))
{
sb.AppendFormat(" , update_by='{0}' ", data.update_by);
}
sb.AppendFormat(" , update_time='{0}' ", DateTime.Now);
sb.AppendFormat(" where refid={0} ", data.refid);
return(MSSql.ExcuteNOQueryStrEx(sb.ToString()));
}
