/// <summary>
/// 获取记录
/// </summary>
/// <param name="pin"></param>
/// <returns></returns>
public UserInfoModel Get(string pin)
{
string sql = string.Format(@"
select *
from UserInfo
where PIN=@PIN
");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@PIN", pin)
};
DataTable dt = SqliteHelper.GetDataTable(sql, parameters);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
DataRow row = dt.Rows[0];
UserInfoModel user = new UserInfoModel();
user.ID = Tools.TryConvertToInt32(row["ID"]);
user.PIN = row["PIN"].ToString();
user.UserName = row["UserName"].ToString();
user.Passwd = row["Passwd"].ToString();
user.IDCard = row["IDCard"].ToString();
user.Grp = row["Grp"].ToString();
user.TZ = row["TZ"].ToString();
user.Pri = row["Pri"].ToString();
user.DevSN = row["DevSN"].ToString();
return(user);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pins"></param>
/// <returns></returns>
public List <TmpFPModel> Get(string pin)
{
string sql = string.Format(@"
select * from TmpFP where pin = '{0}';", pin);
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(new List <TmpFPModel>());
}
List <TmpFPModel> fps = new List <TmpFPModel>();
foreach (DataRow row in dt.Rows)
{
TmpFPModel fp = new TmpFPModel();
fp.ID = Convert.ToInt32(row["ID"].ToString());
fp.Pin = row["PIN"].ToString();
fp.Fid = row["FID"].ToString();
fp.Size = Convert.ToInt32(row["Size"].ToString());
fp.Valid = row["Valid"].ToString();
fp.Tmp = row["Tmp"].ToString();
fp.MajorVer = row["MajorVer"].ToString();
fp.MinorVer = row["MinorVer"].ToString();
fp.Duress = row["Duress"].ToString();
fps.Add(fp);
}
return(fps);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pins"></param>
/// <param name="ver"></param>
/// <returns></returns>
public List <TmpFveinModel> Get(List <string> pins, string ver)
{
string sql = string.Format(@"
select * from TmpFvein where pin in ({0}) and ver='{1}';", Tools.UnionString(pins), ver);
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
List <TmpFveinModel> fps = new List <TmpFveinModel>();
foreach (DataRow row in dt.Rows)
{
TmpFveinModel fp = new TmpFveinModel();
fp.ID = Convert.ToInt32(row["ID"].ToString());
fp.Pin = row["Pin"].ToString();
fp.Fid = row["Fid"].ToString();
fp.Index = row["Index"].ToString();
fp.Size = Convert.ToInt32(row["Size"].ToString());
fp.Valid = row["Valid"].ToString();
fp.Tmp = row["Tmp"].ToString();
fp.Ver = row["Ver"].ToString();
fp.Duress = row["Duress"].ToString();
fps.Add(fp);
}
return(fps);
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="pins"></param>
/// <returns></returns>
public List <UserInfoModel> Get(List <string> pins)
{
string sql = string.Format(@"
select ID,PIN,UserName,Passwd,IDCard,Grp,TZ,Pri,DevSN
from UserInfo
where PIN in ({0})
", Tools.UnionString(pins));
DataTable dt = SqliteHelper.GetDataTable(sql);
List <UserInfoModel> list = new List <UserInfoModel>();
if (dt == null || dt.Rows.Count == 0)
{
return(list);
}
foreach (DataRow row in dt.Rows)
{
UserInfoModel user = new UserInfoModel();
user.ID = Tools.TryConvertToInt32(row["ID"].ToString());
user.PIN = row["PIN"].ToString();
user.UserName = row["UserName"].ToString();
user.Passwd = row["Passwd"].ToString();
user.IDCard = row["IDCard"].ToString();
user.Grp = row["Grp"].ToString();
user.TZ = row["TZ"].ToString();
user.Pri = row["Pri"].ToString();
user.DevSN = row["DevSN"].ToString();
list.Add(user);
}
return(list);
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="workCode"></param>
/// <returns></returns>
public WorkCodeModel GetByWorkCode(string workCode)
{
string sql = string.Format(@"
select * from WorkCode
where workcode=@workcode
");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@workcode", workCode)
};
DataTable dt = SqliteHelper.GetDataTable(sql, parameters);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
WorkCodeModel model = new WorkCodeModel();
DataRow row = dt.Rows[0];
model.ID = Tools.TryConvertToInt32(row["ID"].ToString());
model.WorkCode = row["workcode"].ToString();
model.WorkName = row["workname"].ToString();
return(model);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pins"></param>
/// <param name="ver"></param>
/// <returns></returns>
public List <TmpBioDataModel> Get(string pin, string type)
{
string sql = string.Format(@"
select * from TmpBioData where pin = '{0}' and type='{1}';", pin, type);
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(new List <TmpBioDataModel>());
}
List <TmpBioDataModel> bioList = new List <TmpBioDataModel>();
foreach (DataRow row in dt.Rows)
{
TmpBioDataModel bio = new TmpBioDataModel();
bio.ID = Tools.TryConvertToInt32(row["ID"].ToString());
bio.Pin = row["PIN"].ToString();
bio.No = row["No"].ToString();
bio.Index = row["Index"].ToString();
bio.Valid = row["Valid"].ToString();
bio.Duress = row["Duress"].ToString();
bio.Type = row["Type"].ToString();
bio.MajorVer = row["MajorVer"].ToString();
bio.MinorVer = row["MinorVer"].ToString();
bio.Format = row["Format"].ToString();
bio.Tmp = row["Tmp"].ToString();
bioList.Add(bio);
}
return(bioList);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pins"></param>
/// <param name="ver"></param>
/// <returns></returns>
public List <TmpUserPicModel> Get(List <string> pins)
{
string sql = string.Format(@"
select * from TmpUserPic where pin in ({0});", Tools.UnionString(pins));
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
List <TmpUserPicModel> fps = new List <TmpUserPicModel>();
foreach (DataRow row in dt.Rows)
{
TmpUserPicModel fp = new TmpUserPicModel();
fp.ID = Convert.ToInt32(row["ID"].ToString());
fp.Pin = row["PIN"].ToString();
fp.FileName = row["FileName"].ToString();
fp.Size = Convert.ToInt32(row["Size"].ToString());
fp.Content = row["Content"].ToString();
fps.Add(fp);
}
return(fps);
}
/// <summary>
/// IsExist
/// </summary>
/// <param name="pin"></param>
/// <param name="no"></param>
/// <param name="index"></param>
/// <param name="type"></param>
/// <param name="majorVer"></param>
/// <param name="minorVer"></param>
/// <returns></returns>
public bool IsExist(string pin, string no, string index, string type, string majorVer, string minorVer)
{
string sql = string.Format(@"
select 1 from TmpBioData
where [pin]=@Pin
and [no]=@No
and [index]=@Index
and [Type]=@Type
and [MajorVer]=@MajorVer
and [MinorVer]=@MinorVer
");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@Pin", pin),
new SQLiteParameter("@No", no),
new SQLiteParameter("@Index", index),
new SQLiteParameter("@Type", type.ToLower()),
new SQLiteParameter("@MajorVer", majorVer.ToLower()),
new SQLiteParameter("@MinorVer", minorVer.ToLower())
};
DataTable dt = SqliteHelper.GetDataTable(sql, parameters);
if (dt != null && dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pins"></param>
/// <param name="ver"></param>
/// <returns></returns>
public List <TmpBioPhotoModel> Get(string pin, string type)
{
string sql = string.Format(@"
select * from TmpBioPhoto where pin ='{0}' and type in ({1});", pin, type);
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(new List <TmpBioPhotoModel>());
}
List <TmpBioPhotoModel> fps = new List <TmpBioPhotoModel>();
foreach (DataRow row in dt.Rows)
{
TmpBioPhotoModel fp = new TmpBioPhotoModel();
fp.ID = Convert.ToInt32(row["ID"].ToString());
fp.Pin = row["PIN"].ToString();
fp.FileName = row["FileName"].ToString();
fp.Type = row["Type"].ToString();
fp.Size = Convert.ToInt32(row["Size"].ToString());
fp.Content = row["Content"].ToString();
fps.Add(fp);
}
return(fps);
}
/// <summary>get all record
/// </summary>
public DataTable GetAll()
{
string sql = string.Format(@"
select * from DeviceCmds order by CommitTime desc
");
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>GetAll
/// </summary>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public DataTable GetAll(string sqlWhere)
{
sqlWhere = string.IsNullOrEmpty(sqlWhere) ? "" : "where " + sqlWhere;
string sql = string.Format(@"
select * from Device {0};", sqlWhere);
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>GetAll
/// </summary>
/// <param name="sqlWhere"></param>
/// <returns></returns>
public DataTable GetAll(string sqlWhere)
{
sqlWhere = string.IsNullOrEmpty(sqlWhere) ? "" : "where " + sqlWhere;
string sql = string.Format(@"
select [ID] ,[SMSId] ,[Type] , [ValidTime] ,[BeginTime] ,[UserID] ,[Content]
,case [Type]
when 253 then 'Common'
when 254 then 'User'
when 255 then 'Reserved'
else '' end as TypeName
from SMS {0};", sqlWhere);
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>
/// get record
/// </summary>
/// <param name="devSN"></param>
/// <returns></returns>
public DataTable GetAll(string devSN)
{
string whereDevSN = (string.IsNullOrEmpty(devSN)) ? "" : " where DeviceID=@DevSN";
string sql = string.Format(@"
select * from Errorlog
{0}
order by id desc
", whereDevSN);
SQLiteParameter[] sQLiteParameters =
{
new SQLiteParameter("@DevSN", devSN)
};
return(SqliteHelper.GetDataTable(sql.ToString(), sQLiteParameters));
}
/// <summary>send to device
/// </summary>
/// <param name="DevSN"></param>
/// <returns></returns>
public string Send(string DevSN)
{
string sql = string.Format(@"
select ID, DevSN, Content, CommitTime, TransTime, ResponseTime, ReturnValue
from DeviceCmds
where DevSN=@DevSN and (ReturnValue is null or length(ReturnValue)=0) limit 200
");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@DevSN", DevSN)
};
DataTable dtCmd = SqliteHelper.GetDataTable(sql, parameters);
if (dtCmd == null || dtCmd.Rows.Count == 0)
{
return("OK");
}
StringBuilder sbCmd = new StringBuilder();
StringBuilder sbCmdID = new StringBuilder();
foreach (DataRow row in dtCmd.Rows)
{
string currCmd = string.Format("C:{0}:{1}\n", row["ID"], row["Content"]);
int currCmdLen = Encoding.UTF8.GetByteCount(currCmd);
int strOrdersLen = Encoding.UTF8.GetByteCount(sbCmd.ToString());
if ((currCmdLen + strOrdersLen) > MaxBufferCmd)
{
break;
}
sbCmd.Append(currCmd);
sbCmdID.Append(row["ID"]).Append(",");
}
string ids = sbCmdID.ToString().Trim(',');
if (ids.Length > 0)
{
sql = string.Format("update DeviceCmds set TransTime='{0}' where ID in ({1})", Tools.GetDateTimeNowString(), ids);
SqliteHelper.ExecuteNonQuery(sql);
}
return((sbCmd.Length > 0) ? sbCmd.ToString() : "OK");
}
/// <summary>get record by options
/// </summary>
public DataTable GetByTime(DateTime startTime, DateTime endTime, string devSN)
{
string whereDevSN = (string.IsNullOrEmpty(devSN)) ? "" : " and DevSN=@DevSN";
string sql = string.Format(@"
select * from DeviceCmds
where CommitTime>@CommitTime1 and CommitTime<@CommitTime2
{0}
order by CommitTime desc
", whereDevSN);
SQLiteParameter[] parameters =
{
new SQLiteParameter("@CommitTime1", startTime),
new SQLiteParameter("@CommitTime2", endTime),
new SQLiteParameter("@DevSN", devSN)
};
return(SqliteHelper.GetDataTable(sql, parameters));
}
/// <summary>
/// 获取所有的DevSN
/// </summary>
/// <returns></returns>
public List <string> GetAllDevSN()
{
string sql = string.Format(@"select DevSN from Device;");
List <string> listDevSN = new List <string>();
DataTable dt = SqliteHelper.GetDataTable(sql);
if (dt == null)
{
return(listDevSN);
}
foreach (DataRow row in dt.Rows)
{
listDevSN.Add(row[0].ToString());
}
return(listDevSN);
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <param name="devsn"></param>
/// <returns></returns>
public DataTable GetOplogByTime(DateTime starttime, DateTime endtime, string devSN)
{
string whereDevSN = (string.IsNullOrEmpty(devSN)) ? "" : " and DeviceID=@DevSN";
string sql = string.Format(@"
select * from OpLog
where OpTime>@OpTime1 and OpTime<@OpTime2
{0}
order by OpTime desc
", whereDevSN);
SQLiteParameter[] parameters =
{
new SQLiteParameter("@OpTime1", starttime),
new SQLiteParameter("@OpTime2", endtime),
new SQLiteParameter("@DevSN", devSN)
};
return(SqliteHelper.GetDataTable(sql, parameters));
}
/// <summary>
/// Obtener registros de asistencia
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="userID"></param>
/// <param name="devSN"></param>
/// <returns></returns>
public DataTable GetByTime(DateTime startTime, DateTime endTime, string userID, string devSN)
{
string wherePin = (string.IsNullOrEmpty(userID)) ? "" : " and a.PIN=@PIN";
string whereDevSN = (string.IsNullOrEmpty(devSN)) ? "" : " and DeviceID=@DevSN";
string sql = string.Format(@"
select a.*,w.workname from AttLog a left join WorkCode w on a.workcode = w.workcode
where a.PIN <> '' and a.AttTime>@AttTime1 and a.AttTime<@AttTime2
{0}
{1}
order by a.AttTime desc
", wherePin, whereDevSN);
SQLiteParameter[] parameters =
{
new SQLiteParameter("@AttTime1", startTime),
new SQLiteParameter("@AttTime2", endTime),
new SQLiteParameter("@PIN", userID),
new SQLiteParameter("@DevSN", devSN)
};
return(SqliteHelper.GetDataTable(sql, parameters));
}
/// <summary>
/// 获取所有记录
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
string sql = string.Format(@"
SELECT u.*
,ifnull(fp.FP9Count,0) as FP9Count
,ifnull(fp.FP10Count,0) as FP10Count
,ifnull(fp.FP12Count,0) as FP12Count
,ifnull(bd.PalmCount,0) as PalmCount
,ifnull(f.FaceCount,0)+ifnull(bp.BioPhotoCount,0) as FaceCount
FROM UserInfo u
LEFT JOIN (
select pin
,sum(case when MajorVer='9' then 1 else 0 end) as FP9Count
,sum(case when MajorVer='10' then 1 else 0 end) as FP10Count
,sum(case when MajorVer='12' then 1 else 0 end) as FP12Count
from TmpFP GROUP BY PIN) fp ON u.PIN = fp.PIN
LEFT JOIN (select pin,count(id) as FaceCount from TmpFace GROUP BY PIN) f ON u.PIN = f.PIN
LEFT JOIN (select pin,count(id) as BioPhotoCount from TmpBioPhoto where type='9' or type='0' GROUP BY PIN) bp ON u.PIN = bp.PIN
LEFT JOIN (select pin,count(id) as PalmCount from TmpBioData where type='8' GROUP BY PIN) bd ON u.PIN = bd.PIN
ORDER BY u.pin
");
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>
/// Obtener registros de asistencia
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
string sql = "select * from AttLog order by AttTime desc";
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>
/// 获取记录
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
string sql = string.Format(@"select * from OpLog order by OpTime desc");
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>
/// 获取记录
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
string sql = "select * from WorkCode";
return(SqliteHelper.GetDataTable(sql));
}
