public ActionResult EditColumn(string tableName, SqliteDBHelper.ColumnInfo ct)
{
try
{
var columns = new List <SqliteDBHelper.ColumnInfo>();
var clts = SqliteDBHelper.GetColumnInfos(tableName);
if (clts != null && clts.Count > 0)
{
foreach (var item in clts)
{
if (item.Ordinal == ct.Ordinal)
{
columns.Add(ct);
}
else
{
columns.Add(item);
}
}
}
columns = columns.OrderBy(b => b.Ordinal).ToList();
SqliteDBHelper.AlterColumns(tableName, columns);
}
catch { }
return(Redirect("/Manager/SqliteDataBase/EditTable/" + tableName));
}
public string QueryFrequence()
{
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryFrequence);
return(TypeChange.DataTableToXml(dt));
}
}
public string PharmAdviceCount()
{
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryPharmAdviceCount);
return(TypeChange.DataTableToXml(dt));
}
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult CreateRecord(string id)
{
ViewBag.TableName = id;
if (!string.IsNullOrEmpty(id))
{
id = id.Replace("'", "").Replace("%", "");
}
return(View(SqliteDBHelper.GetRecord(id, 0)));
}
public string QueryCodeListByGroup(string GroupName)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[1];
sqlpar[0] = new SQLiteParameter("1", GroupName);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryCodeListByGroup, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryCheckApply(string InhosID)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[1];
sqlpar[0] = new SQLiteParameter("1", InhosID);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryCheckApply, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryLoaclDataCount()
{
string SqlText = @"Select Count(1) LoacalDataCount From [Patient]";
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlText);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryLisResult(string IllcaseNo)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[1];
sqlpar[0] = new SQLiteParameter("1", IllcaseNo);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryLisResult, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryPatientEmrDocDetail(string InhosID, string SectionNo)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", InhosID);
sqlpar[1] = new SQLiteParameter("2", SectionNo);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryPatientEmrDocDetail, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryDutyResentPatientList(string DoctorID, string DPTypes)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", DoctorID);
sqlpar[1] = new SQLiteParameter("2", DPTypes);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryDutyResentPatientList, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryDeptPatientCount(string DeptId, string InhosStatus)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", DeptId);
sqlpar[1] = new SQLiteParameter("2", InhosStatus);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryDeptPatientCount, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryCheckLogDetail(string InhosID, string LogType)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", InhosID);
sqlpar[1] = new SQLiteParameter("2", LogType);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryCheckLogDetail, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string QueryResentPatientCount(string DPID, string CreateDate, string DPType)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[3];
sqlpar[0] = new SQLiteParameter("1", DPID);
sqlpar[1] = new SQLiteParameter("2", CreateDate);
sqlpar[2] = new SQLiteParameter("3", DPType);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
string str = sqldb.QueryScalar(SqlTools.SqlQueryRecentPatientCount, sqlpar).ToString();
return(TypeChange.StringToXml("RecentPatientCount", str));
}
}
/// <summary>
/// 编辑记录
/// </summary>
/// <param name="id"></param>
/// <param name="key"></param>
/// <returns></returns>
public ActionResult EditRecord(string id, int key)
{
ViewBag.TableName = id;
if (!string.IsNullOrEmpty(id))
{
id = id.Replace("'", "").Replace("%", "");
}
return(View(SqliteDBHelper.GetRecords(id, new DictionaryEntry()
{
Key = "ID", Value = key
})));
}
public string QueryMyPatientList(string DoctorID, string InhosStatus)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", DoctorID);
sqlpar[1] = new SQLiteParameter("2", InhosStatus);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryMyPatient, sqlpar);
string str = TypeChange.DataTableToXml(dt);
return(str);
}
}
public string QueryAdviceByLike(string AdviceType, string AdviceFilter, string StandingType)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[3];
sqlpar[0] = new SQLiteParameter("1", AdviceType);
sqlpar[1] = new SQLiteParameter("2", "%" + AdviceFilter + "%");
sqlpar[2] = new SQLiteParameter("3", StandingType);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryAdviceByLike, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Records(string id, int?pageIndex, int?pageSize)
{
ViewBag.TableName = id;
if (!string.IsNullOrEmpty(id))
{
id = id.Replace("'", "").Replace("%", "");
}
ViewData["column"] = SqliteDBHelper.GetMap(id);
return(View(SqliteDBHelper.GetRecords(id).ToPagedList(pageIndex ?? 1, pageSize ?? 12)));
}
/// <summary>
/// 编辑列
/// </summary>
/// <param name="id"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public ActionResult EditColumn(string id, string columnName)
{
SqliteDBHelper.ColumnInfo ct = new SqliteDBHelper.ColumnInfo();
ViewBag.TableName = id;
ViewBag.ColumnName = columnName;
try
{
var clts = SqliteDBHelper.GetColumnInfos(id);
ct = clts.Where(b => b.Name == columnName).First();
}
catch { }
return(View(ct));
}
public string QueryAdviceCount(string InhosID, string StandingFlag)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[4];
sqlpar[0] = new SQLiteParameter("1", InhosID);
sqlpar[1] = new SQLiteParameter("2", StandingFlag);
sqlpar[2] = new SQLiteParameter("3", InhosID);
sqlpar[3] = new SQLiteParameter("4", StandingFlag);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(SqlTools.SqlQueryAdviceCount, sqlpar);
return(TypeChange.DataTableToXml(dt));
}
}
public string GetUser(string userCode, string pwd)
{
string sql = @"select * from User where code=:1 and password=:2";
SQLiteParameter[] sqlpar = new SQLiteParameter[2];
sqlpar[0] = new SQLiteParameter("1", userCode);
sqlpar[1] = new SQLiteParameter("2", pwd);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
DataTable dt = sqldb.Query(sql, sqlpar);
string str = TypeChange.DataTableToXml(dt);
return(TypeChange.DataTableToXml(dt));
}
}
public string InsertDoctorPatients(string DPID, string DoctorID, string InhosID, string DPType, string CreateDate)
{
SQLiteParameter[] sqlpar = new SQLiteParameter[5];
sqlpar[0] = new SQLiteParameter("1", DPID);
sqlpar[1] = new SQLiteParameter("2", DoctorID);
sqlpar[2] = new SQLiteParameter("3", InhosID);
sqlpar[3] = new SQLiteParameter("4", DPType);
sqlpar[4] = new SQLiteParameter("5", CreateDate);
using (SqliteDBHelper sqldb = new SqliteDBHelper())
{
string str = sqldb.Execute(SqlTools.SqlInsertDoctorPatients, sqlpar) > 0 ? "true" : "false";
//string a = TypeChange.StringToXml("isInsert", str);
return(TypeChange.StringToXml("isInsert", str));
}
}
public ActionResult EditTable(string tableName, string oldTableName,
int[] Ordinal,
string[] Name,
bool[] IsPrimaryKey,
string[] DataTypeName,
int[] MaxLength,
bool[] AllowDBNull,
FormCollection collection)
{
ViewBag.TableName = tableName;
if (!string.IsNullOrEmpty(tableName) && Name != null)
{
var tlts = new List <SqliteDBHelper.ColumnInfo>();
for (int i = 0; i < Name.Length; i++)
{
SqliteDBHelper.ColumnInfo ct = new SqliteDBHelper.ColumnInfo();
ct.Ordinal = Ordinal[i];
ct.Name = Name[i];
ct.IsPrimaryKey = IsPrimaryKey[i];
ct.DataTypeName = DataTypeName[i];
ct.MaxLength = MaxLength[i];
ct.AllowDBNull = AllowDBNull[i];
tlts.Add(ct);
}
if (SqliteDBHelper.ExistsTable(oldTableName))
{
if (tableName != oldTableName && !SqliteDBHelper.ExistsTable(tableName))
{
if (SqliteDBHelper.ReNameTable(tableName, oldTableName))
{
SqliteDBHelper.CreateTable(tableName, tlts);
}
}
else
{
SqliteDBHelper.CreateTable(tableName, tlts);
}
}
}
return(Redirect("/Manager/SqliteDataBase/Tables"));
}
public ActionResult ExecuteSQL(string sql, bool?IsExport)
{
try
{
if (IsExport.HasValue && IsExport.Value)
{
ExcelHelper excelHelper = new ExcelHelper();
excelHelper.AddSheet("Sheet1", SqliteDBHelper.Query(sql).Tables[0]);
excelHelper.Export();
return(Content(string.Empty));
}
else
{
SqliteDBHelper.ExecuteSql(sql);
}
ViewBag.Message = "SQL执行成功";
ViewBag.Access = "True";
}
catch (Exception ex)
{
ViewBag.Message = "执行错误" + ex.Message;
}
return(View());
}
public ActionResult CreateTable(string tableName,
bool basicFiled,
int[] Ordinal,
string[] Name,
bool[] IsPrimaryKey,
string[] DataTypeName,
int[] MaxLength,
bool[] AllowDBNull,
FormCollection collection)
{
if (!string.IsNullOrEmpty(tableName) && Name != null)
{
var tlts = new List <SqliteDBHelper.ColumnInfo>();
if (basicFiled)
{
tlts.AddRange(new List <SqliteDBHelper.ColumnInfo>()
{
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "ID", IsPrimaryKey = true, AutoIncrement = true, DataTypeName = SqliteDBHelper.SqliteDBDataType.INTEGER.ToString(), AllowDBNull = false
},
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "CreateUserID", IsPrimaryKey = false, DataTypeName = SqliteDBHelper.SqliteDBDataType.INTEGER.ToString(), AllowDBNull = false
},
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "LastUpdateUserID", IsPrimaryKey = false, DataTypeName = SqliteDBHelper.SqliteDBDataType.INTEGER.ToString(), AllowDBNull = false
},
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "CreateDate", IsPrimaryKey = false, DataTypeName = SqliteDBHelper.SqliteDBDataType.DATETIME.ToString(), AllowDBNull = false
},
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "LastUpdateDate", IsPrimaryKey = false, DataTypeName = SqliteDBHelper.SqliteDBDataType.DATETIME.ToString(), AllowDBNull = false
},
new SqliteDBHelper.ColumnInfo()
{
Ordinal = 0, Name = "IsDeleted", IsPrimaryKey = false, DataTypeName = SqliteDBHelper.SqliteDBDataType.BIT.ToString(), AllowDBNull = false
}
});
}
for (int i = 0; i < Name.Length; i++)
{
SqliteDBHelper.ColumnInfo ct = new SqliteDBHelper.ColumnInfo();
ct.Ordinal = Ordinal[i];
ct.Name = Name[i];
ct.IsPrimaryKey = IsPrimaryKey[i];
ct.DataTypeName = DataTypeName[i];
ct.MaxLength = MaxLength[i];
ct.AllowDBNull = AllowDBNull[i];
tlts.Add(ct);
}
if (!SqliteDBHelper.ExistsTable(tableName))
{
SqliteDBHelper.CreateTable(tableName, tlts);
}
}
return(Redirect("/Manager/SqliteDataBase/Tables"));
}
/// <summary>
/// 清空表数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Truncate(string id)
{
SqliteDBHelper.Trancate(id);
SqliteDBHelper.ShrinkDatabase();
return(Redirect("/Manager/SqliteDataBase/Records/" + id));
}
/// <summary>
/// 删除记录
/// </summary>
/// <param name="id"></param>
/// <param name="key"></param>
/// <returns></returns>
public ActionResult DeleteRecord(string id, int key)
{
SqliteDBHelper.DeleteRecord(id, key);
return(Redirect("/Manager/SqliteDataBase/Records/" + id));
}
public ActionResult EditRecord(int id, string tableName, FormCollection collection)
{
SqliteDBHelper.UpdateRecord(id, tableName, collection, SqliteDBHelper.SqliteEnumOperation.UPDATE);
return(Redirect("/Manager/SqliteDataBase/Records/" + tableName));
}
public ActionResult CreateRecord(string tableName, FormCollection collection)
{
SqliteDBHelper.UpdateRecord(null, tableName, collection, SqliteDBHelper.SqliteEnumOperation.INSERT);
return(Redirect("/Manager/SqliteDataBase/Records/" + tableName));
}
//
// GET: /DataBase/
#region 表结构操作
/// <summary>
/// 获取全部表
/// </summary>
/// <returns></returns>
public ActionResult Tables()
{
return(PartialView(SqliteDBHelper.GetTables()));
}
/// <summary>
/// 删除表
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult DropTable(string id)
{
SqliteDBHelper.DropTable(id);
SqliteDBHelper.ShrinkDatabase();
return(Redirect("/Manager/SqliteDataBase/Tables"));
}
