/// <summary>
/// 更新来访车牌
/// </summary>
/// <param name="idNumber"></param>
/// <param name="data45"></param>
/// <returns></returns>
public static string updateData45(string idNumber, string data45)
{
//1.查询出数据。
//2. 符合条件则更新数据
string returnValue = "";
flow_data_362 model = findByIdNumber(idNumber);
if (model == null)
{
return("");
}
//并没有建立数据库连接
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open(); //建立连接，打开数据库
idNumber = idNumber.Replace("'", "‘"); //防一下sql注入
data45 = data45.Replace("'", "‘");
string today = DateTime.Today.ToString("yyyy-MM-dd"); //日期格式要确保与mysql存储格式一致。
//string sqlstr = "update flow_data_362 set data_45 ='" + data45 + "' where data_11 = '" + idNumber + "' and left(data_6,10) = '" + today + "'"; //SQL语句
string sqlstr = "update flow_data_362 set data_45 ='" + data45 + "' where data_11 = '" + model.data_11 + "' and left(data_6,10) = '" + today + "'"; //SQL语句
MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
int i = cmd.ExecuteNonQuery();
if (i > 0) //遍历表中数据,只取第一条记录
{
returnValue = data45;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close(); //关闭连接
}
return(returnValue);
}
/// <summary>
/// 更新入厂 来访卡号、日期
/// </summary>
/// <param name="idNumber"></param>
/// <param name="card_no"></param>
/// <returns></returns>
public static int updateInTime(string idNumber, string card_no)
{
//1.查询出数据。
//2. 符合条件则更新数据
int returnValue = 0;
flow_data_362 model = findByIdNumber(idNumber);
if (model == null)
{
return(0);
}
//要不要先判断此来访申请单的状态、此来访卡的状态？ 、、暂时先不判断
//并没有建立数据库连接
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open(); //建立连接，打开数据库
idNumber = idNumber.Replace("'", "‘"); //防一下sql注入
string today = DateTime.Today.ToString("yyyy-MM-dd"); //日期格式要确保与mysql存储格式一致。
//进厂时间
string data40 = DateTime.Now.ToString();
//string sqlstr = "update flow_data_362 set data_44 ='" + card_no + "',data_40 = '" + data40 + "' where data_11 = '" + idNumber + "' and left(data_6,10) = '" + today + "'"; //SQL语句
string sqlstr = "update flow_data_362 set data_44 ='" + card_no + "',data_40 = '" + data40 + "' where data_11 = '" + model.data_11 + "' and left(data_6,10) = '" + today + "'"; //SQL语句
MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
returnValue = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close(); //关闭连接
}
return(returnValue);
}
//更新离场日期
public static int updateOutTime(string card_no)
{
//1.查询出数据。
//2. 符合条件则更新数据
int returnValue = 0;
flow_data_362 model = findByCard(card_no);
if (model == null)
{
return(0);
}
//并没有建立数据库连接
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open(); //建立连接，打开数据库
card_no = card_no.Replace("'", "‘"); //防一下sql注入
string today = DateTime.Today.ToString("yyyy-MM-dd"); //日期格式要确保与mysql存储格式一致。
//离厂时间
string data33 = DateTime.Now.ToString();
string sqlstr = "update flow_data_362 set data_33 ='" + data33 + "' where data_44 = '" + card_no + "' and LENGTH(data_33) < 1 "; //SQL语句
MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
returnValue = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close(); //关闭连接
}
return(returnValue);
}
/// <summary>
/// 根据 来访登记卡，查询来访纪录. 登记完用于显示
/// </summary>
/// <param name="card_no"></param>
/// <returns></returns>
public static flow_data_362 findByCardAndDate(string card_no)
{
flow_data_362 model;
//并没有建立数据库连接
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open(); //建立连接，打开数据库
card_no = card_no.Replace("'", "‘"); //防一下sql注入
string today = DateTime.Today.ToString("yyyy-MM-dd"); //日期格式要确保与mysql存储格式一致。
string sqlstr = "select * from flow_data_362 where data_44 = '" + card_no + "' and left(data_6,10) = '" + today + "' ORDER BY run_id desc "; //SQL语句
MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
//相当于数据读出流 理解为一本书
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) //遍历表中数据,只取第一条记录
{
//封装属性
model = new flow_data_362
{
#region 封装属性
id = reader.GetInt32("id"),
run_id = reader.GetInt32("run_id"),
begin_time = reader.GetDateTime("begin_time"),
begin_user = reader.GetString("begin_user"),
data_10 = reader.GetString("data_10"),
data_11 = reader.GetString("data_11"),
data_12 = reader.GetString("data_12"),
data_13 = reader.GetString("data_13"),
data_14 = reader.GetString("data_14"),
data_15 = reader.GetString("data_15"),
data_16 = reader.GetString("data_16"),
data_17 = reader.GetString("data_17"),
data_18 = reader.GetString("data_18"),
data_19 = reader.GetString("data_19"),
data_20 = reader.GetString("data_20"),
data_21 = reader.GetString("data_21"),
data_22 = reader.GetString("data_22"),
data_22_key = reader.GetString("data_22_key"),
data_23 = reader.GetString("data_23"),
data_24 = reader.GetString("data_24"),
data_25 = reader.GetString("data_25"),
data_27 = reader.GetString("data_27"),
data_29 = reader.GetString("data_29"),
data_31 = reader.GetString("data_31"),
data_32 = reader.GetString("data_32"),
data_33 = reader.GetString("data_33"),
data_36 = reader.GetString("data_36"),
data_38 = reader.GetString("data_38"),
data_4 = reader.GetString("data_4"),
data_40 = reader.GetString("data_40"),
data_41 = reader.GetString("data_41"),
data_43 = reader.GetString("data_43"),
data_44 = reader.GetString("data_44"),
data_45 = reader.GetString("data_45"),
data_5 = reader.GetString("data_5"),
data_6 = reader.GetString("data_6"),
data_8 = reader.GetString("data_8"),
data_9 = reader.GetString("data_9"),
flow_auto_num = reader.GetInt32("flow_auto_num"),
flow_auto_num_month = reader.GetInt32("flow_auto_num_month"),
flow_auto_num_year = reader.GetInt32("flow_auto_num_year"),
run_name = reader.GetString("run_name")
#endregion
};
}
else
{
model = null;
}
if (!reader.IsClosed)
{
reader.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
finally
{
conn.Close(); //关闭连接
}
return(model);
}
//根据证件号，查询来访纪录
public static flow_data_362 findByIdNumber(string idNumber)
{
flow_data_362 model;
//证件号码最少输入6位
if (idNumber.Length < 6)
{
return(null);
}
//并没有建立数据库连接
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open(); //建立连接，打开数据库
idNumber = idNumber.Replace("'", "‘"); //防一下sql注入
string today = DateTime.Today.ToString("yyyy-MM-dd"); //日期格式要确保与mysql存储格式一致。
// string sqlstr = "select * from flow_data_362 where data_11 = '" + idNumber + "' and left(data_6,10) = '" + today + "'"; //SQL语句
string sqlstr = "select * from flow_data_362 where data_11 like '%" + idNumber + "%' and left(data_6,10) = '" + today + "'"; //SQL语句
MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
//相当于数据读出流 理解为一本书
MySqlDataReader reader = cmd.ExecuteReader();
//reader.Read(); //读取下一页数据 ，读取成功，返回true，下一页没有数据则返回false表示到了最后一页
if (reader.Read()) //遍历表中数据,只取第一条记录
{
//Console.WriteLine(reader.GetInt32("id") + reader.GetString("name") + reader.GetString("age"));
//封装属性
model = new flow_data_362
{
#region 封装属性
id = reader.GetInt32("id"),
run_id = reader.GetInt32("run_id"),
begin_time = reader.GetDateTime("begin_time"),
begin_user = reader.GetString("begin_user"),
data_10 = reader.GetString("data_10"),
data_11 = reader.GetString("data_11"),
data_12 = reader.GetString("data_12"),
data_13 = reader.GetString("data_13"),
data_14 = reader.GetString("data_14"),
data_15 = reader.GetString("data_15"),
data_16 = reader.GetString("data_16"),
data_17 = reader.GetString("data_17"),
data_18 = reader.GetString("data_18"),
data_19 = reader.GetString("data_19"),
data_20 = reader.GetString("data_20"),
data_21 = reader.GetString("data_21"),
data_22 = reader.GetString("data_22"),
data_22_key = reader.GetString("data_22_key"),
data_23 = reader.GetString("data_23"),
data_24 = reader.GetString("data_24"),
data_25 = reader.GetString("data_25"),
data_27 = reader.GetString("data_27"),
data_29 = reader.GetString("data_29"),
data_31 = reader.GetString("data_31"),
data_32 = reader.GetString("data_32"),
data_33 = reader.GetString("data_33"),
data_36 = reader.GetString("data_36"),
data_38 = reader.GetString("data_38"),
data_4 = reader.GetString("data_4"),
data_40 = reader.GetString("data_40"),
data_41 = reader.GetString("data_41"),
data_43 = reader.GetString("data_43"),
data_44 = reader.GetString("data_44"),
data_45 = reader.GetString("data_45"),
data_5 = reader.GetString("data_5"),
data_6 = reader.GetString("data_6"),
data_8 = reader.GetString("data_8"),
data_9 = reader.GetString("data_9"),
flow_auto_num = reader.GetInt32("flow_auto_num"),
flow_auto_num_month = reader.GetInt32("flow_auto_num_month"),
flow_auto_num_year = reader.GetInt32("flow_auto_num_year"),
run_name = reader.GetString("run_name")
#endregion
};
}
else
{
model = null;
}
if (!reader.IsClosed)
{
reader.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
finally
{
conn.Close(); //关闭连接
}
return(model);
}