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>
/// <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);
}
}
public static List <FavoritedProduct> GetFavoritedProducts()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(LOCAL_DB_PATH))
{
return(conn.CreateCommand("select * from FavoritedProduct").ExecuteDeferredQuery <FavoritedProduct>().ToList());
}
}
public Model.ModelLifeCounter UpdateLifeCounter(Model.ModelLifeCounter data)
{
conn.CreateCommand("Select * From ModelLifeCounter where Id = " + data.Id.ToString());
var model = conn.Query <Model.ModelLifeCounter>("Select * From ModelLifeCounter where Id = " + data.Id.ToString()).SingleOrDefault();
model.Deleted = false;
model.CommanderDamageCounter = data.CommanderDamageCounter;
model.Life = data.Life;
model.PoisonDamageCounter = data.PoisonDamageCounter;
model.Updated = true;
model.UpdateDate = DateTime.Now;
model.User = "******";
conn.Update(model);
conn.Commit();
Model.ModelLifeCounter updatedModel = new Model.ModelLifeCounter();
updatedModel.Id = model.Id;
updatedModel.Inserted = model.Inserted;
updatedModel.CommanderDamageCounter = model.CommanderDamageCounter;
updatedModel.CreateDate = model.CreateDate;
updatedModel.Deleted = model.Deleted;
updatedModel.Life = model.Life;
updatedModel.PoisonDamageCounter = model.PoisonDamageCounter;
updatedModel.Updated = model.Updated;
updatedModel.UpdateDate = model.UpdateDate;
updatedModel.User = model.User;
return(updatedModel);
}
/// <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();
}
}
}
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);
}
/// <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>
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);
}
}
/// <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>
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);
}
}
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="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.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>
/// <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>
/// <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.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);
}
}
/// <summary>
/// 查询病人的查房日志
/// </summary>
/// <param name="InhosID"></param>
/// <returns></returns>
public DoctorCheckLog QueryLocalCheckLog(string DCLID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [DoctorCheckLog] Where[DCLID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DCLID);
var Result = cmd.ExecuteQuery <DoctorCheckLog>();
if (Result != null && Result.Count > 0)
{
return(Result[0]);
}
return(null);
}
}
/// <summary>
/// 从本地数据库获取当前设备信息
/// </summary>
/// <param name="DeviceID"></param>
/// <returns></returns>
public iDevice QueryLocalDeviceInfo(string DeviceID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iDevice] Where [DeviceID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DeviceID);
var Result = cmd.ExecuteQuery <iDevice>();
if (Result.Count > 0)
{
return(Result[0]);
}
return(null);
}
}
/// <summary>
/// 查询本地病人病历文书信息
/// </summary>
/// <param name="InhosID">住院流水号</param>
public Data.PatientEmrDoc QueryPatientEmrDocDetail(string InhosID, string SectionNo)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select pa.* From [PatientEmrDoc] pa Where pa.[InhosID]=? And pa.[SectionNo]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID, SectionNo);
var Result = cmd.ExecuteQuery <PatientEmrDoc>();
if (Result.Count > 0)
{
return(Result[0]);
}
return(null);
}
}
public int[] QueryPatientCount()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select Count(1) From [Patient] p Where p.[BedDoctor]=? And p.[InhosStatus]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, iCommon.DoctorID, "正住院");
var MyPatientCount = cmd.ExecuteScalar <string>();
SqlText = @"Select Count(1) From [Patient] p Where p.[OfficeID]=? And p.[InhosStatus]=?";
cmd.CommandText = SqlText;
cmd.ClearBindings();
cmd.Bind(iCommon.DeptID);
cmd.Bind("正住院");
var OfficePatientCount = cmd.ExecuteScalar <string>();
SqlText = @"Select Count(1) From [DoctorPatients] dp Where dp.[DoctorID]=? And dp.[DPType]=?";
cmd.CommandText = SqlText;
cmd.ClearBindings();
cmd.Bind(iCommon.DoctorID);
cmd.Bind("值班病人");
var DutyPatientCount = cmd.ExecuteScalar <string>();
cmd.ClearBindings();
cmd.Bind(iCommon.DoctorID);
cmd.Bind("近期病人");
var TodayPatientCount = cmd.ExecuteScalar <string>();
SqlText = @"Select Count(1) From [Patient] p Where p.[BedDoctor]=? And p.[InhosStatus]=?";
cmd.CommandText = SqlText;
cmd.ClearBindings();
cmd.Bind(iCommon.DoctorID);
cmd.Bind("已出院");
var LeavehosPatientCount = cmd.ExecuteScalar <string>();
SqlText = @"select count(1) from [iAdvice] ia Where ia.[AdviceType] not in (901001,901002,901003)";
cmd.ClearBindings();
cmd.CommandText = SqlText;
var NoPharmAdviceCount = cmd.ExecuteScalar <string>();
SqlText = @"select count(1) from [iAdvice] ia Where ia.[AdviceType] in (901001,901002,901003)";
cmd.CommandText = SqlText;
var PharmAdviceCount = cmd.ExecuteScalar <string>();
return(new int[] { Convert.ToInt32(MyPatientCount), Convert.ToInt32(OfficePatientCount), Convert.ToInt32(DutyPatientCount),
Convert.ToInt32(TodayPatientCount), Convert.ToInt32(LeavehosPatientCount), Convert.ToInt32(NoPharmAdviceCount),
Convert.ToInt32(PharmAdviceCount) });
}
}
/// <summary>
/// 保存设备到本地
/// </summary>
/// <param name="device"></param>
public void SaveLocalDeviceInfo(Data.iDevice device)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select * From [iDevice] Where [DeviceID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, device.DeviceID);
var Result = cmd.ExecuteQuery <iDevice>();
if (Result.Count == 1)
{
conn.Update(device);
}
else
{
conn.Insert(device);
}
}
}
public static void InsertScores(int Id, string Username, int TotalScore, DateTime DateOfScore, int AGGRScore, int AGORScore, int ANXIScore, int COGNScore, int MOODscore, int SOMAScore, int SOPHScore, int VITAScore, int WORKScore)
{
using (var connection = new SQLite.SQLiteConnection("GGzDB.db"))
{
var statement = connection.CreateCommand(@"INSERT INTO tbl_Score(
Id, Username, TotalScore,
DateOfScore, AGGRScore, AGORScore,
ANXIScore, COGNScore, MOODScore,
SOMAScore, SOPHScore, VITAScore, WORKScore)
VALUES(? , ? , ? , ? , ?, ?, ?, ?, ?, ?, ?, ?, ?);",
Id, Username, TotalScore,
DateOfScore, AGGRScore, AGORScore,
ANXIScore, COGNScore, MOODscore,
SOMAScore, SOPHScore, VITAScore, WORKScore);
statement.ExecuteNonQuery();
}
}
public void DeleteCheckLog(Data.DoctorCheckLog CheckLog)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlCheckLog = @"Delete From [DoctorCheckLog] Where [DCLID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlCheckLog, CheckLog.DCLID);
cmd.ExecuteNonQuery();
Data.SyncLog slCheckLog = new SyncLog()
{
TableName = "[DoctorCheckLog]",
KeyField = "DCLID",
KeyValue = CheckLog.DCLID,
ChangeType = "DELETE"
};
SaveSyncLog(conn, slCheckLog);
}
}