using IOM_cs.irisDb.model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace IOM_cs.irisDb.service.mySqlImpl
{
public class UserIrisService_MysqlImpl:IUserIrisService
{
#region person
/// <summary>
/// 获取数据库中的注册人员总数
/// </summary>
/// <returns></returns>
public int GetCountOfPerson()
{
int count = 0;
try
{
MySqlDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_person where DELFLAG=0";
MySqlCommand cmd = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
count = aReader.GetInt32(0);
}
aReader.Close();
}
catch (SqlException s)
{ MessageBox.Show(s.Message.ToString()); }
return count;
}
/// <summary>
/// 判断此证件号码是否已存在
/// </summary>
public int IsIDCardNoPresent(string sIDCardNo)
{
//加密
//sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);
int mun = 0;
try
{
string sQry = "SELECT COUNT(*) FROM sys_person WHERE ID_CARD_NO=@IDCardNo and DELFLAG=0";
MySqlCommand MySqlCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
MySqlCommand.Parameters.Add
(
"@IDCardNo",
MySqlDbType.VarChar,
100
).Value = sIDCardNo.Trim();
MySqlDataReader aReader = MySqlCommand.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
if (!aReader.IsDBNull(0)) mun = aReader.GetInt16(0);
}
aReader.Close();
MySqlCommand.Dispose();
}
catch (SqlException e)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
}
MessageBox.Show("IsIDCardNoPresentException:" + e.Message);
}
return mun;
}
/// <summary>
/// sys_person,sys_person_ext新增人员
/// </summary>
public int AddPerson(Person person, PersonExt personExt)
{
int re = -1;
MySqlTransaction tx = null;
try
{
//sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);//加密
tx = _MySqlHelper.mySqlConnection.BeginTransaction();
string sQry =
"INSERT INTO sys_person (ID,DELFLAG,CREATETIME,NAME,SEX,ID_CARD_NO,REMARKS,NATION,BIRTHDAY,CARD_TYPE)" +
"values (@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10)";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Transaction = tx; //绑定事务
aCommand.Parameters.AddWithValue("@var1", person.id);
aCommand.Parameters.AddWithValue("@var2", "0");
aCommand.Parameters.AddWithValue("@var3", DateTime.Now);
aCommand.Parameters.AddWithValue("@var4", person.name);
aCommand.Parameters.AddWithValue("@var5", person.sex);
aCommand.Parameters.AddWithValue("@var6", person.idCardNo);
aCommand.Parameters.AddWithValue("@var7", person.remarks);
aCommand.Parameters.AddWithValue("@var8", person.nation);
aCommand.Parameters.AddWithValue("@var9", person.birthday);
aCommand.Parameters.AddWithValue("@var10", person.cardType);
aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry =
"INSERT INTO sys_person_ext(ID, TARGET_ID, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6, COLUMN_7)" +
"values (@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9)";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@var1", personExt.id);
aCommand.Parameters.AddWithValue("@var2", personExt.targetId);
aCommand.Parameters.AddWithValue("@var3", personExt.column1);
aCommand.Parameters.AddWithValue("@var4", personExt.column2);
aCommand.Parameters.AddWithValue("@var5", personExt.column3);
aCommand.Parameters.AddWithValue("@var6", personExt.column4);
aCommand.Parameters.AddWithValue("@var7", personExt.column5);
aCommand.Parameters.AddWithValue("@var8", personExt.column6);
aCommand.Parameters.AddWithValue("@var9", personExt.column7);
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (MySqlException ex)
{
tx.Rollback();
re = -1;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("AddPerson EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
/// <summary>
/// 删除人员，sys_person、iris_data、iris_data_coll物理删除
/// </summary>
public int DeletePerson(Int64 personId)
{
int iRetval = -1;
try
{
string sQry = "delete FROM sys_person WHERE ID=@Id";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry = "DELETE FROM iris_data WHERE S_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry = "DELETE FROM iris_data_coll WHERE S_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
iRetval = 0;
}
catch (Exception ex)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("EXCEPTION: " + ex.Message);
iRetval = -1;
}
return iRetval;
}
/// <summary>
/// 获取人员详情,返回Person, IrisDataColl, IrisData三个对象
/// </summary>
public int GetDetail(Int64 personId, ref Person person, ref PersonExt personExt, ref IrisDataColl irisDataColl, ref IrisData irisData)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "SELECT * FROM sys_person WHERE ID=@Id";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = aCommand;
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
person = ConvertToEntity<Person>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// sys_person表
sQry = "SELECT * FROM sys_person_ext WHERE TARGET_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new MySqlDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
personExt = ConvertToEntity<PersonExt>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// iris_data_coll表
sQry = "SELECT * FROM iris_data_coll WHERE S_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new MySqlDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
irisDataColl = ConvertToEntity<IrisDataColl>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// iris_data表
sQry = "SELECT * FROM iris_data WHERE S_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new MySqlDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
irisData = ConvertToEntity<IrisData>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("GetDetail EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("GetDetail EXCEPTION: " + ex.Message);
}
return iRetval;
}
/// <summary>
/// 保存编辑，更新sys_person、sys_person_ext、iris_data_coll表
/// </summary>
public int Update(Person person, PersonExt personExt, IrisDataColl irisDataColl)
{
int iRetval = -1;
try
{
// sys_person表
string sQry = "UPDATE sys_person set NAME=@var1,SEX=@var2,ID_CARD_NO=@var3, REMARKS = @var4,NATION=@var5, BIRTHDAY=@var6,CARD_TYPE=@var7 WHERE ID=@Id";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", person.id);
aCommand.Parameters.AddWithValue("@var1", person.name);
aCommand.Parameters.AddWithValue("@var2", person.sex);
aCommand.Parameters.AddWithValue("@var3", person.idCardNo);
aCommand.Parameters.AddWithValue("@var4", person.remarks);
aCommand.Parameters.AddWithValue("@var5", person.nation);
aCommand.Parameters.AddWithValue("@var6", person.birthday);
aCommand.Parameters.AddWithValue("@var7", person.cardType);
aCommand.ExecuteNonQuery();
aCommand.Dispose();
// sys_person_ext表
sQry = "UPDATE sys_person_ext set COLUMN_1=@var1,COLUMN_2=@var2,COLUMN_3=@var3, COLUMN_4=@var4,COLUMN_5=@var5,COLUMN_6=@var6 WHERE TARGET_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", person.id);
aCommand.Parameters.AddWithValue("@var1", personExt.column1);
aCommand.Parameters.AddWithValue("@var2", personExt.column2);
aCommand.Parameters.AddWithValue("@var3", personExt.column3);
aCommand.Parameters.AddWithValue("@var4", personExt.column4);
aCommand.Parameters.AddWithValue("@var5", personExt.column5);
aCommand.Parameters.AddWithValue("@var6", personExt.column6);
aCommand.ExecuteNonQuery();
aCommand.Dispose();
// iris_data_coll表
sQry = "UPDATE iris_data_coll set TEL1=@var1,TEL2=@var2,REASON_COLL_DESC=@var3, FLG_FOCUS=@var4,FOCUS_END_DATE=@var5,FOCUS_REMARKS=@var6,COLL_PLACE=@var7,FLG_WO=@var8,FLG_FORCE_PERSON =@var9 WHERE S_ID=@Id";
aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.Parameters.AddWithValue("@Id", person.id);
aCommand.Parameters.AddWithValue("@var1", irisDataColl.tel1);
aCommand.Parameters.AddWithValue("@var2", irisDataColl.tel2);
aCommand.Parameters.AddWithValue("@var3", irisDataColl.reasonCollDesc);
aCommand.Parameters.AddWithValue("@var4", irisDataColl.flgFocus);
aCommand.Parameters.AddWithValue("@var5", irisDataColl.focusEndDate);
aCommand.Parameters.AddWithValue("@var6", irisDataColl.focusRemarks);
aCommand.Parameters.AddWithValue("@var7", irisDataColl.collPlace);
aCommand.Parameters.AddWithValue("@var8", irisDataColl.flgWo);
aCommand.Parameters.AddWithValue("@var9", irisDataColl.flgForcePerson);
aCommand.ExecuteNonQuery();
aCommand.Dispose();
iRetval = 0;
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
iRetval = -1;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
iRetval = -1;
System.Windows.Forms.MessageBox.Show("Update EXCEPTION: " + ex.Message);
}
return iRetval;
}
#endregion
#region iris_data_coll
/// <summary>
/// iris_data_coll新增采集信息
/// </summary>
public int AddIrisDataColl(IrisDataColl irisDataColl)
{
int re = -1;
MySqlTransaction tx = null;
try
{
tx = _MySqlHelper.mySqlConnection.BeginTransaction();
string sQry =
"INSERT INTO iris_data_coll(IR_ID,S_ID,TEL1,TEL2,FLG_CER,COLL_NAME,COLL_ID,Q_L,Q_R,REASON_COLL_DESC,COLL_TYPE,COLL_REASON,DEF_REASON,FLG_FORCE,FLG_SYNC,FLG_UPLOAD,FLG_FOCUS,FOCUS_END_DATE,FOCUS_REMARKS,COLL_PLACE,FLG_WO,COLL_WAY,IS_CER,COLL_TIME,ST_NAME, ST_TYPE,DEV_TYPE,DEV_CODE,DEV_FIRM,COLL_NUM,COLL_SPEND,FLG_FORCE_PERSON)" +
"values(@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11, @var12, @var13, @var14, @var15, @var16, @var17, @var18, @var19, @var20, @var21, @var22, @var23, @var24, @var25, @var26, @var27, @var28, @var29, @var30, @var31, @var32)";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
//绑定事务
aCommand.Transaction = tx;
aCommand.Parameters.AddWithValue("@var1", irisDataColl.irId);
aCommand.Parameters.AddWithValue("@var2", irisDataColl.sId);
aCommand.Parameters.AddWithValue("@var3", irisDataColl.tel1);
aCommand.Parameters.AddWithValue("@var4", irisDataColl.tel2);
aCommand.Parameters.AddWithValue("@var5", irisDataColl.flgCer);
aCommand.Parameters.AddWithValue("@var6", irisDataColl.collName);
aCommand.Parameters.AddWithValue("@var7", irisDataColl.collId);
aCommand.Parameters.AddWithValue("@var8", irisDataColl.qL);
aCommand.Parameters.AddWithValue("@var9", irisDataColl.qR);
aCommand.Parameters.AddWithValue("@var10", irisDataColl.reasonCollDesc);
aCommand.Parameters.AddWithValue("@var11", irisDataColl.collType);
aCommand.Parameters.AddWithValue("@var12", irisDataColl.collReason);
aCommand.Parameters.AddWithValue("@var13", irisDataColl.defReason);
aCommand.Parameters.AddWithValue("@var14", irisDataColl.flgForce);
aCommand.Parameters.AddWithValue("@var15", irisDataColl.flgSync);
aCommand.Parameters.AddWithValue("@var16", irisDataColl.flgUpload);
aCommand.Parameters.AddWithValue("@var17", irisDataColl.flgFocus);
aCommand.Parameters.AddWithValue("@var18", irisDataColl.focusEndDate);
aCommand.Parameters.AddWithValue("@var19", irisDataColl.focusRemarks);
aCommand.Parameters.AddWithValue("@var20", irisDataColl.collPlace);
aCommand.Parameters.AddWithValue("@var21", irisDataColl.flgWo);
aCommand.Parameters.AddWithValue("@var22", irisDataColl.collWay);
aCommand.Parameters.AddWithValue("@var23", irisDataColl.isCer);
aCommand.Parameters.AddWithValue("@var24", irisDataColl.collTime);
aCommand.Parameters.AddWithValue("@var25", irisDataColl.stName);
aCommand.Parameters.AddWithValue("@var26", irisDataColl.stType);
aCommand.Parameters.AddWithValue("@var27", irisDataColl.devType);
aCommand.Parameters.AddWithValue("@var28", irisDataColl.devCode);
aCommand.Parameters.AddWithValue("@var29", irisDataColl.devFirm);
aCommand.Parameters.AddWithValue("@var30", irisDataColl.collNum);
aCommand.Parameters.AddWithValue("@var31", irisDataColl.collSpend);
aCommand.Parameters.AddWithValue("@var32", irisDataColl.flgForcePerson);
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (Exception ex)
{
re = -1; tx.Rollback();
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("AddIrisDataColl EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
#endregion
#region iris_data_coll
/// <summary>
/// iris_data新增虹膜数据
/// </summary>
public int AddIrisData(IrisData irisData)
{
int re = -1;
MySqlTransaction tx = null;
try
{
tx = _MySqlHelper.mySqlConnection.BeginTransaction();
string sQry =
"INSERT INTO iris_data(IR_ID,S_ID,IRIS_L,IRIS_R,PATH_VIDEO,IMAGE_L1,IMAGE_L2,IMAGE_R1,IMAGE_R2,IMAGE_L1_DATA,IMAGE_R1_DATA)" +
"values(@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11)";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
//绑定事务
aCommand.Transaction = tx;
aCommand.Parameters.AddWithValue("@var1", irisData.irId);
aCommand.Parameters.AddWithValue("@var2", irisData.sId);
aCommand.Parameters.AddWithValue("@var3", irisData.irisL1);
aCommand.Parameters.AddWithValue("@var4", irisData.irisR1);
aCommand.Parameters.AddWithValue("@var5", irisData.pathVideo);
aCommand.Parameters.AddWithValue("@var6", irisData.imageL1Data);
//aCommand.Parameters.AddWithValue("@var7", irisData.imageL2);
//aCommand.Parameters.AddWithValue("@var8", irisData.imageR1);
//aCommand.Parameters.AddWithValue("@var9", irisData.imageR2);
aCommand.Parameters.AddWithValue("@var10", irisData.imageL1Data);
aCommand.Parameters.AddWithValue("@var11", irisData.imageR1Data);
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (Exception ex)
{
tx.Rollback();
re = -1;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
#endregion
/* /// <summary>
/// 文本加密
/// </summary>
private string TextEncrypt(string content, string secretKey)
{
char[] data = content.ToCharArray();
char[] key = secretKey.ToCharArray();
string s = "";
for (int i = 0; i < data.Length; i++)
{
data[i] ^= key[i % key.Length];
s = s + data[i].ToString();
}
return s;
}
*/
/// <summary>
/// DataTable转换为实体对象
/// </summary>
private T ConvertToEntity<T>(DataTable dt, DataRow row) where T : new()
{
T t = new T();
try
{
System.Data.DataColumnCollection columns = dt.Columns;
int iColumnCount = columns.Count;
int i;
int j;
Type elementType;
elementType = t.GetType();
System.Reflection.PropertyInfo[] publicProperties = elementType.GetProperties();
//if (!(publicProperties.Length > iColumnCount))
//{
for (i = 0; i < iColumnCount; i++)
{
for (j = 0; j < publicProperties.Length; j++)
{
if (columns[i].ColumnName.ToLower().Replace("_", "") == publicProperties[j].Name.ToLower().Replace("_", ""))
{
if (publicProperties[j].PropertyType == typeof(int))
{
int num = 0;
try
{
num = Convert.ToInt32(row[i]);
}
catch
{
}
publicProperties[j].SetValue(t, num, null);
}
else
if (publicProperties[j].PropertyType == typeof(string) && row[i] == System.DBNull.Value)
{
publicProperties[j].SetValue(t, "", null);
}
else
{
object value = row[i] == System.DBNull.Value ? null : row[i];
publicProperties[j].SetValue(t, value, null);
}
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return t;
}
/// <summary>
/// 生成采集编号
/// </summary>
public string getCollNum()
{
string collNum = "";
string preStr = "HM" + ConfigHelper.GetAppConfig("DeptCode").ToString().Trim();
MySqlDataReader aReader = null;
try
{
// sys_person表
string sQry = "select max(COLL_NUM) from iris_data_coll";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.ExecuteNonQuery();
aReader = aCommand.ExecuteReader(CommandBehavior.Default);
if (aReader.Read() && !aReader.IsDBNull(0))
{
string date = aReader.GetString(0).Substring(14, 8);
if (date == DateTime.Now.ToString("yyyyMMdd"))
{
//string index = (Convert.ToInt64(aReader.GetString(0).Substring(22, 6)) + 1).ToString();
//if (index.Length != 6)
//{ index =}
collNum = preStr + date + (Convert.ToInt64(aReader.GetString(0).Substring(22, 6)) + 1).ToString("000000");
}
else
{
collNum = preStr + DateTime.Now.ToString("yyyyMMdd") + "000001";
}
}
else
{
collNum = preStr + DateTime.Now.ToString("yyyyMMdd") + "000001";
}
}
catch (Exception e)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
}
MessageBox.Show("getCollNum catch error:" + e.Message);
}
finally
{
if (aReader != null)
{
aReader.Close();
aReader.Dispose();
}
}
return collNum;
}
}
}
