public int GetTotalRecordsInTable(SQLite.SQLiteConnection m_dbConnection, string tableName, Logger logger)
{
int records = 0;// new DataSet();
try
{
string query = $"SELECT count(1) TotalRecords FROM {tableName};";
//records = m_dbConnection.Query<ProcessedDetails1>(query).Count;
SQLite.SQLiteCommand command = new SQLite.SQLiteCommand(m_dbConnection);
command.CommandText = query;
records = command.ExecuteScalar <int>();
//SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
//m_dbConnection.Open();
//SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(m_dbConnection);
////myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//DataSet myDataSet = new DataSet();
//myAdapter.Fill(myDataSet, "Records");
//if (myDataSet.Tables[0].Rows.Count > 0)
//{
// records = Convert.ToInt32(myDataSet.Tables[0].Rows[0][0].ToString());
//}
//m_dbConnection.Close();
}
catch (Exception excp)
{
logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
}
return(records);
}
/// <summary>
/// 保存一个查房日志到本地
/// </summary>
/// <param name="CheckLog"></param>
public void SaveCheckLogToLocal(Data.DoctorCheckLog CheckLog)
{//To do:修改病人的同步标志，标识需同步查房日志
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlCheckLog = @"Select * From [DoctorCheckLog] Where [DCLID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlCheckLog, CheckLog.DCLID);
Data.DoctorCheckLog existCheckLog = cmd.ExecuteQuery <DoctorCheckLog>().SingleOrDefault();
Data.SyncLog slCheckLog = new SyncLog()
{
TableName = "[DoctorCheckLog]",
KeyField = "DCLID",
KeyValue = CheckLog.DCLID,
ChangeType = "INSERT"
};
if (existCheckLog == null)
{
conn.Insert(CheckLog);
}
else
{
conn.Update(CheckLog);
slCheckLog.ChangeType = "UPDATE";
}
SaveSyncLog(conn, slCheckLog);
}
}
private async Task <bool> SyncLocalData(SQLite.SQLiteConnection conn, Data.SyncLog sl)
{
SQLite.SQLiteCommand cmd = conn.CreateCommand(String.Format(SqlText, sl.TableName, sl.KeyField), sl.KeyValue);
dynamic Result;
switch (sl.TableName)
{
case "DoctorCheckLog": Result = cmd.ExecuteQuery <Data.DoctorCheckLog>().SingleOrDefault(); break;
case "PadAdvice": Result = cmd.ExecuteQuery <Data.PadAdvice>().SingleOrDefault(); break;
default: return(false);
}
if (Result != null)
{
object[] Data = sl.ChangeType != "DELETE" ? Result.Data : null;
string[] Fields = Result.FieldArray;
object SyncResult = await RemoteExcute(new object[] { sl.DeviceID, sl.InhosID, sl.TableName, sl.KeyField, sl.KeyValue, sl.ChangeType, sl.ChangeDate, Fields, Data });
if (SyncResult != null && (bool)SyncResult)
{
conn.Delete(sl);
}
}
return(true);
}
public bool InsertRecord(SQLite.SQLiteConnection m_dbConnection, string query, Logger logger)
{
bool isRecordInserted = false;
try
{
//SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
//m_dbConnection.Open();
//string isErrorString = IsError ? "1" : "0";
//string query = $"INSERT INTO {tableName}(FlatFileRowNumber, IsError) VALUES({flatFileRecordNumber},{isErrorString})";
SQLite.SQLiteCommand command = new SQLite.SQLiteCommand(m_dbConnection);
command.CommandText = query;
command.ExecuteNonQuery();
//m_dbConnection.Close();
isRecordInserted = true;
}
catch (Exception excp)
{
logger.Error("Error while inserting sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
}
return(isRecordInserted);
}
private EventArgs UpdateLocalData(SQLite.SQLiteConnection conn, string SqlDelete, string SqlInsert, List <Data.DataObject> DataList, string TableName, string PrimaryKey)
{
SQLite.SQLiteCommand cmdDelete = conn.CreateCommand(SqlDelete);
SQLite.SQLiteCommand cmdInsert = conn.CreateCommand(SqlInsert);
dynamic arg = null;
if (TableName == "DoctorCheckLog")
{
arg = new CheckLogSyncEventArgs();
}
foreach (Data.DataObject data in DataList)
{
cmdDelete.ClearBindings();
cmdInsert.ClearBindings();
cmdDelete.Bind(data.GetExtValue(PrimaryKey));
cmdDelete.ExecuteNonQuery();
if (data.GetExtValue("ChangeType") != "DELETE")
{
for (int d = 0; d < data.Data.Length - 1; d++)
{
cmdInsert.Bind(data.Data[d]);
}
cmdInsert.ExecuteNonQuery();
}
if (TableName == "DoctorCheckLog")
{
arg.DCLID.Add(data.GetExtValue(PrimaryKey));
arg.InhosID.Add(data.GetExtValue("InhosID"));
arg.ChangeType.Add(data.GetExtValue("ChangeType"));
arg.MedicalLog.Add(data.GetExtBytes("MedicalLog"));
arg.PhotoLog.Add(data.GetExtBytes("Photo"));
}
}
return(arg);
}
/// <summary>
/// 查询有变化的数据表
/// </summary>
/// <returns></returns>
private async Task <bool> QueryDeviceChangeTable()
{
string isInitData;
List <string> TableList = new List <string>();
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iDeviceTableChange]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText);
List <Data.iDeviceTableChange> TableChangeList = cmd.ExecuteQuery <Data.iDeviceTableChange>();
foreach (Data.iDeviceTableChange itc in TableChangeList)
{
TableList.Add(itc.TableName + "|" + itc.LastChangeDate);
}
isInitData = TableChangeList.Count == 0 ? "0" : "1"; //0:表示初始数据（新安装）1：表示正常数据同步
}
object Result = await RemoteExcute(new object[] { iCommon.DeviceID, TableList.ToArray() });
List <Data.iDeviceTableChange> ChangeTableList = Deserialize <Data.iDeviceTableChange>(Result as byte[]);
if (ChangeTableList == null)
{
return(false);
}
return(await SyncDeviceDataFromServer(ChangeTableList, isInitData));
}
/// <summary>
/// 保存本地设备所属科室信息
/// </summary>
public void SaveLocalDeviceOffice(string DeviceID, List <string> OfficeIDs, List <string> OfficeNames)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
conn.BeginTransaction();
try
{
string SqlText = @"Delete From [iDeviceOffice] Where [DeviceID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DeviceID);
cmd.ExecuteNonQuery();
SqlText = @"Insert Into [iDeviceOffice] ([DOID],[DeviceID], [OfficeID],[OfficeName],[CreateDate]) Values (@1,@2,@3,@4,@5)";
cmd.CommandText = SqlText;
for (int i = 0; i < OfficeIDs.Count; i++)
{
cmd.ClearBindings();
cmd.Bind(Guid.NewGuid().ToString());
cmd.Bind(DeviceID);
cmd.Bind(OfficeIDs[i]); cmd.Bind(OfficeNames[i]);
cmd.Bind(iCommon.DateNow);
cmd.ExecuteNonQuery();
}
conn.Commit();
}
catch
{
conn.Rollback();
}
}
}
/// <summary>
/// 查询本地病人LIS结果
/// </summary>
/// <param name="SampleNo">样本号</param>
/// <param name="TestDate">检验日期</param>
public List <Data.LisResult> QueryLocalLisResult(string IllcaseNo)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [LisResult] Where [IllcaseNo]=? Order By [ReportDate] Desc";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, IllcaseNo);
var Result = cmd.ExecuteQuery <LisResult>();
return(Result);
}
}
/// <summary>
/// 查询本地病人住院医嘱信息
/// </summary>
/// <param name="InhosID">住院流水号</param>
public List <Data.PatientAdvices> QueryLocalPatientAdvices(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select pa.* From [PatientAdvices] pa Where pa.[InhosID]=? Order By [ListDate]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID);
var Result = cmd.ExecuteQuery <PatientAdvices>();
return(Result);
}
}
/// <summary>
/// 查询本地医嘱子类别
/// </summary>
public List <Data.iType2> QueryLocalSubType(string AdviceTypeID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iType2] Where [AdviceTypeID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, AdviceTypeID);
var Result = cmd.ExecuteQuery <iType2>();
return(Result);
}
}
/// <summary>
/// 查询本地医嘱类别
/// </summary>
public List <Data.iCode> QueryLocalAdviceType()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iCode] Where [GroupName]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, "医嘱类别");
var Result = cmd.ExecuteQuery <iCode>();
return(Result);
}
}
/// <summary>
/// 查询病人的查房日志
/// </summary>
/// <param name="InhosID"></param>
/// <returns></returns>
public List <DoctorCheckLog> QueryLocalPatientCheckPhoto(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [DoctorCheckLog] Where[InhosID]=? And [LogType]=? Order By CheckDate||CheckTime Desc";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID, "照片");
var Result = cmd.ExecuteQuery <DoctorCheckLog>();
return(Result);
}
}
/// <summary>
/// 获取本地值班病人
/// </summary>
/// <param name="DoctorID"></param>
/// <returns></returns>
public List <Patient> QueryLocalTodayPatientList(string DoctorID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [Patient] p Where Exists (Select 1 From [DoctorPatients] dp Where dp.[DoctorID]=? And dp.[InhosID] = p.[InhosID] And dp.[DPType] = ? And dp.[CreateDate]=?)";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DoctorID, "近期病人", iCommon.Today);
var Result = cmd.ExecuteQuery <Patient>();
return(Result);
}
}
/// <summary>
/// 查询本地检查结果
/// </summary>
public List <Data.CheckResult> QueryLocalCheckResult(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select cr.* From [CheckApply] ca,[CheckResult] cr Where ca.[InhosID] = @1 and ca.[ApplyNo] = cr.[ApplyNo] Order By [SerialNo]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID);
var Result = cmd.ExecuteQuery <CheckResult>();
return(Result);
}
}
/// <summary>
/// 查询本地检查申请
/// </summary>
/// <param name="InhosID"></param>
public List <Data.CheckApply> QueryLocalCheckApply(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [CheckApply] Where [InhosID]=? Order By [ReportDate] Desc";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID);
var Result = cmd.ExecuteQuery <CheckApply>();
return(Result);
}
}
/// <summary>
/// 获取本地科室病人
/// </summary>
/// <param name="OfficeID"></param>
/// <returns></returns>
public List <Patient> QueryLocalOfficePatientList(string OfficeID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select p.* From [Patient] p Where p.[OfficeID]=? And p.[InhosStatus]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, OfficeID, "正住院");
var Result = cmd.ExecuteQuery <Patient>();
return(Result);
}
}
/// <summary>
/// 查询所有本地代码信息
/// </summary>
public List <Data.iCode> QueryLocalCodeList()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iCode]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText);
var Result = cmd.ExecuteQuery <iCode>();
return(Result);
}
}
/// <summary>
/// 获取本地科室病人
/// </summary>
/// <param name="DoctorID"></param>
/// <returns></returns>
public List <Patient> QueryLocalHistoryPatientList(string DoctorID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [Patient] p Where p.[BedDoctor]=? And p.[InhosStatus]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DoctorID, "已出院");
var Result = cmd.ExecuteQuery <Patient>();
return(Result);
}
}
/// <summary>
/// 查询本地病人病历文书信息
/// </summary>
/// <param name="InhosID">住院流水号</param>
public List <Data.PatientEmrDoc> QueryLocalEmrDocHeader(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select [InhosID],[SectionNo],[DocTypeID],[DocTypeName],[Title],[Creator],[CreateDate],[CheckDate] From [PatientEmrDoc] pa Where pa.[InhosID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID);
var Result = cmd.ExecuteQuery <PatientEmrDoc>();
return(Result);
}
}
public int QueryLoaclDataCount()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select Count(1) From [Patient]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText);
var Result = cmd.ExecuteScalar <string>();
return(Convert.ToInt32(Result));
}
}
/// <summary>
/// 查询本地病人预交款
/// </summary>
/// <param name="InhosID">住院流水号</param>
public List <Data.PatientPrepay> QueryLocalPrepay(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [PatientPrepay] Where [InhosID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID);
var Result = cmd.ExecuteQuery <PatientPrepay>();
return(Result);
}
}
/// <summary>
/// 查询本地设备所属科室信息
/// </summary>
public List <Data.iDeviceOffice> QueryLocalDeviceOffice(string DeviceID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iDeviceOffice] Where [DeviceID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DeviceID);
var Result = cmd.ExecuteQuery <iDeviceOffice>();
return(Result);
}
}
/// <summary>
/// 查询本地医嘱子类别
/// </summary>
public List <Data.iAdvice> QueryLocalAdvice(string StandingFlag, string AdviceTypeID, string AdviceFilter)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iAdvice] Where [AdviceType]=? And [AdviceFilter] Like '%'||?||'%' And ([StandingType]='" + StandingFlag + "' Or [StandingType]='9')";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, AdviceTypeID, AdviceFilter);
var Result = cmd.ExecuteQuery <iAdvice>();
return(Result);
}
}
public void RememberCity(string name)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblCity set name='" + name + "' where cityID=1";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void AddNote(string title, string description)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "insert into tblNotes(title,description) values ('" + title + "','" + description + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void EditNote(string title, string description, int noteid)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblNotes set title='" + title + "', description='" + description + "' where noteID='" + noteid + "'";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void DeleteNote(int noteid)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "delete from tblNotes where noteID='" + noteid + "'";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void EditItem(string title, string details, int listid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblQuickMemo set Title='" + title + "', Details='" + details + "' where Listid=" + listid;
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void DeleteItem(int listid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "delete from tblQuickMemo where Listid = " + listid;
cmd.ExecuteNonQuery();
}
} catch (Exception ex) {
Console.WriteLine("Error:" + ex.Message);
}
}
public void AddItem(string title, string details)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "insert into tblQuickMemo(Title,Details) values('" + title + "','" + details + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
