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);
}
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));
}
}
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="InhosID">住院流水号</param>
public List <Data.AdviceTypeGroup> QueryPatientAdvicesTypeList(string InhosID, string StandingFlag)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select [AdviceType],Count(1) As GroupCount From [PatientAdvices] pa Where pa.[InhosID]=? And pa.[StandingFlag]=? Group By [AdviceType]";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID, StandingFlag);
var DataList = cmd.ExecuteQuery <AdviceTypeGroup>();
DataList.Insert(0, new AdviceTypeGroup()
{
AdviceType = "全部", GroupCount = "0"
});
SqlText = @"Select Count(1) As GroupCount From [PatientAdvices] pa Where pa.[InhosID]=? And pa.[StandingFlag]=?";
cmd.CommandText = SqlText;
var Result = cmd.ExecuteScalar <string>();
DataList[0].GroupCount = Result;
return(DataList);
}
}
/// <summary>
/// 保存近期查房记录
/// </summary>
/// <param name="InhosID">住院流水号</param>
public void SaveTodayCheckPatient(string InhosID)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlText = @"Select Count(1) From [DoctorPatients] Where [DPID]=? And [CreateDate]=? And [DPType]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, iCommon.DoctorID + InhosID, iCommon.Today, "近期病人");
var Count = cmd.ExecuteScalar <string>();
if (Count == "0")
{
Data.DoctorPatients dp = new DoctorPatients();
dp.DPID = iCommon.DoctorID + InhosID;
dp.DoctorID = iCommon.DoctorID;
dp.InhosID = InhosID;
dp.DPType = "近期病人";
dp.CreateDate = iCommon.Today;
conn.Insert(dp);
}
}
}