public static void Save(Database db, Purchase purchase)
{
var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE UniqueId=@0 ", purchase.UniqueId);
if (existing != null)
db.Update("purchases", "PurchaseId", existing);
else
db.Insert("purchases", "PurchaseId", purchase);
}
public static Tag GetTag(int id)
{
var db = new Database("umbracoDbDSN");
var tag = db.SingleOrDefault<Tag>("where id = @0", id);
if (tag != null)
return tag;
throw new ArgumentNullException(string.Format("No tag found with id = {0}", id));
}
public IHttpActionResult GetStory(string id)
{
var db = new PetaPoco.Database("AGSoftware");
Entities.Storytime storytime = db.SingleOrDefault<Entities.Storytime>("Select * From StoryTime Where StorytimeId = @0", id);
if (storytime != null)
return Ok(storytime);
else
return NotFound();
}
public IEnumerable<Tuple<string, string>> GetPolygon(int segmentId)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
var str = db.SingleOrDefault<string>("select Polylines.ToString() from MR.tSegment where SegmentId = @0 ", segmentId);
return str.Substring("MULTIPOINT (".Length).Replace("(", "").Replace(")", "").Split(',')
.Select(s =>
{
var p = s.Trim().Split(' ');
return Tuple.Create(p[1], p[0]);
});
}
public FrmNewBloodCleanupProcess(Int64 cleanup_id)
{
InitializeComponent();
//string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
db = new Database("XE");
_cleanupID = cleanup_id;
process.BLOODCLEANUP_ID = _cleanupID;
process.OPERATOR = ClsFrmMng.WorkerID;
bLOODCLEANUPPROCESSBindingSource.DataSource = process;
BLOODCLEANUP_TEMP bt = db.SingleOrDefault<BLOODCLEANUP_TEMP>("where BLOOD_CLEANUP_ID = @0", _cleanupID);
DEVICECOMMUNICATION_LOG log = db.SingleOrDefault<DEVICECOMMUNICATION_LOG>("where REMOTE_IP=@0 and rownum = 1 order by ID DESC", bt.SERIAL_PORT_NUM);
if (log != null)
{
string sDataTmp = log.MSG.Substring(0, log.MSG.Length - 4);
string sTmp = sDataTmp.Substring(log.MSG.LastIndexOf('F') + 1, 5);
string sVp = sDataTmp.Substring(log.MSG.LastIndexOf('H') + 1, 5);
string sBf = sDataTmp.Substring(log.MSG.LastIndexOf('D') + 1, 5);
string sMaxBp = sDataTmp.Substring(log.MSG.LastIndexOf('N') + 1, 5);
string sMinBp = sDataTmp.Substring(log.MSG.LastIndexOf('O') + 1, 5);
string sPulse = sDataTmp.Substring(log.MSG.LastIndexOf('P') + 1, 5);
string sTotalUFAmount = sDataTmp.Substring(log.MSG.LastIndexOf('B') + 1, 5);
string sDC = sDataTmp.Substring(log.MSG.LastIndexOf('G') + 1, 5);
string sTMP = sDataTmp.Substring(log.MSG.LastIndexOf('J') + 1, 5);
process.ANA_TIME = log.RECEIVE_TIME;
process.TEMP = decimal.Parse(sTmp);
process.VENOUS_PRESSURE = decimal.Parse(sVp);
process.BLOOD_FLOW = decimal.Parse(sBf);
process.BP = decimal.Parse(sMaxBp).ToString() + "~" + decimal.Parse(sMinBp);
process.P = decimal.Parse(sPulse);
process.ULTRAFILTRATION = decimal.Parse(sTotalUFAmount);
process.CONDUCTIVITY = sDC;
process.ARTERIAL_PRESSURE = decimal.Parse(sTMP);
}
}
public static string CrearCiudad(Ciudad ciudad)
{
try {
using (var db = new Database("riesgos")) {
var cuenta = db.SingleOrDefault<int>("select count(id) from ciudad where nombre = @0", ciudad.Nombre);
if (cuenta > 0) return "Ya existe una ciudad con el mismo nombre";
db.Save("ciudad", "id", ciudad);
return "";
}
} catch (Exception ex) {
return "Error de ejecución: " + ex.Message;
}
}
public async Task <bool> Demo(string id)
{
var data = Db.SingleOrDefault <DatabaseInfo>($"select DemoStatus from DemoData where id = {id}");
await Task.Delay(10);
if (data.DemoStatus == 0)
{
return(true);
}
else
{
return(false);
}
}
public IHttpActionResult GetStory(string id)
{
var db = new PetaPoco.Database("AGSoftware");
Entities.Storytime storytime = db.SingleOrDefault <Entities.Storytime>("Select * From StoryTime Where StorytimeId = @0", id);
if (storytime != null)
{
return(Ok(storytime));
}
else
{
return(NotFound());
}
}
/// <summary>
/// 获取用户全部信息
/// </summary>
/// <param name="UserCode"></param>
/// <param name="UserPass"></param>
/// <returns></returns>
public Models.User UserInfo(string UserCode, string UserPass)
{
Models.User MUser;
using (SqlConnection sqlConnection = new SqlConnection(SystemSqlConn))
{
sqlConnection.Open();
using (Database db = new PetaPoco.Database(sqlConnection))
{
MUser = db.SingleOrDefault <Models.User>(@"
SELECT * FROM Tb_User WHERE UserCode = @0
AND ISNULL(UserIsLocked,0) = 0
AND UserPass = @1", UserCode, UserPass);
}
return(MUser);
}
}
/// <summary>
/// 每次进入窗体时，验证Token
/// </summary>
/// <param name="msg"></param>
/// <returns></returns>
public Token ValidateToken(string UserGuid, string ParamSqlConn)
{
Token token;
using (SqlConnection sqlConnection = new SqlConnection(ParamSqlConn))
{
sqlConnection.Open();
using (Database db = new PetaPoco.Database(sqlConnection))
{
token = db.SingleOrDefault <Token>(@"SELECT
TokenCode,
TokenLastDate,
TokenFalseDate,
UserGuid
FROM Tb_Token
WHERE UserGuid = @0", UserGuid);
}
return(token);
}
}
public static BoolMessage PpRetrieveOne <T>(string tableName, string condition)
{
_checkVal.Data = null;
try
{
if (string.IsNullOrEmpty(_cstring))
{
throw new Exception("No connection string provided");
}
_checkVal.Data = _pp.SingleOrDefault <T>(string.Format("SELECT * FROM [{0}] WHERE {1}", tableName, condition));
_checkVal.Success = true;
_checkVal.Message = string.Format("Successfully returned one item from table {0}", tableName);
}
catch (Exception ex)
{
_checkVal.Data = null;
_checkVal.Success = false;
_checkVal.Message = ex.Message;
}
return(_checkVal);
}
protected string DelUniqueId(string UniqueId)
{
try
{
// Create a PetaPoco database object
var db = new PetaPoco.Database("Shell_AutoArchDBConnectionString");
// Get a record
var a = db.SingleOrDefault <planning>("SELECT * FROM shell_Planning WHERE UniqueId=@0", UniqueId);
//delete
db.Delete(a);
return("OK");
}
catch (Exception eg)
{
divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>";
return("Error");
}
}
public Guid AuthUser(string email, string passwordHash)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.SingleOrDefault<Guid>("select AuthId from MR.tUser where Email = @0 AND Passwd = @1", email, passwordHash);
}
private string WinFlagColumn = "sWin"; // 中獎識別欄位
#endregion Fields
#region Methods
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query<DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return result;
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return result;
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return result;
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query<DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return result;
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return result;
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return result;
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query<DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return result;
}
}
public Guid GetAuthId(string email)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.SingleOrDefault<Guid>("select AuthId from MR.tUser where Email = @0", email);
}
public User GetUser(string email)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.SingleOrDefault<User>("select * from MR.tUser where Email = @0", email);
}
protected void SaveNewUpd(bool flgChkAuto)
{
bool flgNew = false;
bool flgValid = false;
try
{
//Validate first...
flgValid = validateFields();
//
if (!flgValid)
{
return;
}
// Create a PetaPoco database object
var db = new PetaPoco.Database("Shell_AutoArchDBConnectionString");
// Get a record
var a = db.SingleOrDefault <planning>("SELECT * FROM shell_Planning WHERE UniqueId=@0", txt_uniqueid.Text);
//Test if record exists...
if (null == a)
{
a = new planning();
flgNew = true;
}
//if update, check if status is not equal to CVR... or Closed...
else if (flgChkAuto)
{
if (txt_status.Text.IndexOf("Archival Order Closed") == -1)
{
if ((a.Status.IndexOf("Rejected") >= 0) || (a.Status.IndexOf("CVR Created") >= 0)) //Rejected
{
ClrFields(false);
return;
}
}
}
// Change it
//
if (txt_actaoenddate.Text != a.ActAOEndDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.ActAOEndDate = DateTime.Parse(txt_actaoenddate.Text);
}
//
if (txt_actaoreceivedate.Text != a.ActAOReceiveDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.ActAOReceiveDate = DateTime.Parse(txt_actaoreceivedate.Text);
}
//
if (txt_actaostartdate.Text != a.ActAOStartDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.ActAOStartDate = DateTime.Parse(txt_actaostartdate.Text);
}
//
if (txt_colsitenum.Text != "")
{
if (Convert.ToInt16(txt_colsitenum.Text) != a.ColSiteNum)
{
a.ColSiteNum = Convert.ToInt16(txt_colsitenum.Text);
}
}
//
if (txt_comment.Text != a.Comment)
{
a.Comment = txt_comment.Text;
}
//
if (chk_dataexpctrchk.Checked)
{
a.DataExpCtrChk = 1;
}
else
{
a.DataExpCtrChk = 0;
}
//
if (txt_duname.Text != a.DUName)
{
a.DUName = txt_duname.Text;
}
//
//
if ((txt_actaostartdate.Text != "") && (txt_actaoenddate.Text != ""))
{
DateTime dtf = DateTime.Parse(txt_actaostartdate.Text);
DateTime dtt = DateTime.Parse(txt_actaoenddate.Text);
a.DurationWorkDays = GetWorkingDays(dtf, dtt);
}
else if (Convert.ToInt16(txt_durationworkdays.Text) != a.DurationWorkDays)
{
a.DurationWorkDays = Convert.ToInt16(txt_durationworkdays.Text);
}
//
//
if (txt_operatorname.Text != a.OperatorName)
{
a.OperatorName = txt_operatorname.Text;
}
//
if (txt_planaoenddate.Text != a.PlanAOEndDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.PlanAOEndDate = DateTime.Parse(txt_planaoenddate.Text);
}
//
if (txt_planaoreceivedate.Text != a.PlanAOReceiveDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.PlanAOReceiveDate = DateTime.Parse(txt_planaoreceivedate.Text);
}
//
if (txt_planaostartdate.Text != a.PlanAOStartDate.ToString("yyyy-MM-dd HH:mm:ss.ttt"))
{
a.PlanAOStartDate = DateTime.Parse(txt_planaostartdate.Text);
}
//
if (txt_status.Text != a.Status)
{
a.Status = txt_status.Text;
}
//
if (txt_instanceid.Text != a.InstanceId)
{
a.InstanceId = txt_instanceid.Text;
}
//
if ((float)Convert.ToDouble(txt_volumegb.Text) != a.VolumeGB)
{
a.VolumeGB = (float)Convert.ToDouble(txt_volumegb.Text);
}
//
if (chk_waiverchk.Checked)
{
a.WaiverChk = 1;
}
else
{
a.WaiverChk = 0;
}
//
string msg = "Archiving Order updated! - ";
if (flgNew)
{
a.UniqueId = txt_uniqueid.Text;
msg = "New Archiving Order inserted! - ";
db.Save(a);
}
else
{
db.Update(a);
}
if (flgChkAuto)
{
divMessage.InnerHtml += "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + msg + txt_uniqueid.Text + " </span><br>";
}
else
{
divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + msg + txt_uniqueid.Text + " </span>";
}
}
catch (Exception eg)
{
if (flgChkAuto)
{
divMessage.InnerHtml += "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>";
}
else
{
divMessage.InnerHtml = "<span id='msg' style='color:#FF3300;font-size:Smaller;font-weight:bold;'>" + "Error Message: " + eg.Message + " </span>";
}
}
}
public FrmNewBloodCleanBase(Int64 base_id, Int64 reg_id, Int64 machineCheckID, decimal pt_path_type)
{
InitializeComponent();
//string sPwd = Des.Decrypt(ClsFrmMng.KEY, ConfigurationManager.AppSettings["DbPwd"]);
db = new Database("XE");
_regID = reg_id;
_baseID = base_id;
blookCaeanup.REG_ID = _regID;
blookCaeanup.BASE_INFO_ID = _baseID;
blookCaeanup.OPERATOR = ClsFrmMng.WorkerID;
blookCaeanup.ANA_DATE = DateTime.Now;
// 查询该患者所签到的透析机机位, 透析机型号
ms = db.SingleOrDefault<MACHINE_SCHEDULE>(machineCheckID);
string sFloor = db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", ms.FLOOR_ID);
string sArea = db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", ms.AREA_ID);
blookCaeanup.MACH_POS = sFloor + " " + sArea + " " + ms.BED_NO + "#";
MACHINE_INFO info = db.SingleOrDefault<MACHINE_INFO>(ms.MACHINE_INFO_ID);
if (info != null && info.MODEL != null)
blookCaeanup.MACH_TYP = info.MODEL.ToString();
// 血管通路类型
blookCaeanup.FISTULA_TYPE = pt_path_type;
if (pt_path_type == 519 || pt_path_type == 520)
{
ItemForAPPLICATOR.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForAPPLICATOR_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForHEPARIN_CAP.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForHEPARIN_CAP_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
// 获取该患者最近一次, 当通路类型与目前匹配时使用的耗材型号与数据
BLOODCLEANUP bc1 = db.SingleOrDefault<BLOODCLEANUP>("where FISTULA_TYPE = @0 and BASE_INFO_ID = @1 AND rownum = 1 order by ID DESC", new object[] { pt_path_type, base_id });
if (bc1 != null)
{
blookCaeanup.FISTULA_NEEDLE = bc1.FISTULA_NEEDLE;
blookCaeanup.FISTULA_NEEDLE_NUM = bc1.FISTULA_NEEDLE_NUM;
blookCaeanup.FISTULA_CARE_PACKAGES = bc1.FISTULA_CARE_PACKAGES;
blookCaeanup.FISTULA_CARE_PACKAGES_NUM = bc1.FISTULA_CARE_PACKAGES_NUM;
}
}
else if (pt_path_type == 704 || pt_path_type == 705)
{
ItemForFISTULA_NEEDLE.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForFISTULA_NEEDLE_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForFISTULA_CARE_PACKAGES.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
ItemForFISTULA_CARE_PACKAGES_NUM.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Never;
// 获取该患者最近一次, 当通路类型与目前匹配时使用的耗材型号与数据
BLOODCLEANUP bc1 = db.SingleOrDefault<BLOODCLEANUP>("where FISTULA_TYPE = @0 and BASE_INFO_ID = @1 AND rownum = 1 order by ID DESC", new object[] { pt_path_type, base_id });
if (bc1 != null)
{
blookCaeanup.APPLICATOR = bc1.APPLICATOR;
blookCaeanup.APPLICATOR_NUM = bc1.APPLICATOR_NUM;
blookCaeanup.HEPARIN_CAP = bc1.HEPARIN_CAP;
blookCaeanup.HEPARIN_CAP_NUM = bc1.HEPARIN_CAP_NUM;
}
}
// 查询该患者上一次血液净信息
BLOODCLEANUP bc = db.SingleOrDefault<BLOODCLEANUP>("where BASE_INFO_ID = @0 AND rownum = 1 order by ID DESC", base_id);
if (bc != null)
{
blookCaeanup.WEIGHT = bc.WEIGHT;
blookCaeanup.MACH = bc.MACH; // 透析器
blookCaeanup.PIPELINE = bc.PIPELINE; // 管路
}
// 查询该患者医嘱(长期及临时医嘱)中的药品
List<DOC_ADVICE> lstAdv = db.Fetch<DOC_ADVICE>("where BASE_INFO_ID = @0 and (ADVICE_TYPE = 0 or ADVICE_TYPE = 9) AND IS_DEL = 0 order by log_time desc", base_id);
string sDose = string.Empty;
for (int i = 0; i < lstAdv.Count; i++)
{
sDose += db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", lstAdv[i].M_NAME) +", ";
}
lstAdv = db.Fetch<DOC_ADVICE>("where reg_id = @0 and ADVICE_TYPE = 1 AND IS_DEL = 0 order by log_time desc", reg_id);
for (int i = 0; i < lstAdv.Count; i++)
{
sDose += db.ExecuteScalar<string>("select DSP_MEMBER from VALUE_CODE where VALUE_MEMBER = @0", lstAdv[i].M_NAME) + ", ";
}
sDose = sDose.TrimEnd(new char[] { ',', ' ' });
blookCaeanup.EPO = sDose;
bLOODCLEANUPBindingSource.DataSource = blookCaeanup;
//EPOTextEdit.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
EPOTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("");
EPOTextEdit.Properties.DisplayMember = "DSP_MEMBER";
EPOTextEdit.Properties.ValueMember = "VALUE_MEMBER";
MACH_TYPTextEdit.Properties.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0 or GROUPNAME = @1", new object[] { 3, 162 });
MACH_TYPTextEdit.Properties.DisplayMember = "DSP_MEMBER";
MACH_TYPTextEdit.Properties.ValueMember = "VALUE_MEMBER";
vALUECODEBindingSource.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 101);
vALUECODEBindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 44);
vALUECODEBindingSource2.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 102);
vALUECODEBindingSource3.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 161);
aCCOUNTBindingSource.DataSource = db.Fetch<ACCOUNT>("");
bindingSource1.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 193); // 通路类型
bindingSource2.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 47); // 管路
bindingSource3.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 43); // 穿刺针
bindingSource4.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 171); // 敷贴
bindingSource5.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 166); // 护理包
bindingSource6.DataSource = db.Fetch<VALUE_CODE>("where GROUPNAME = @0", 169); // 肝素帽
}
//public static string GetPhrase(string language, string phrase)
public string GetPhrase(string language, string phrase)
{
// TODO: Fetch the translatedtext for the specified term with the specified language
string[] param = {phrase, language};
Database db = new Database("Translation");
Translation t = new Translation();
t=db.SingleOrDefault<Translation>("select * from Translation where Phrase=@0 and LanguageCode=@1", param);
return t.TranslatedText;
//throw new NotImplementedException();
}
public static sanpham ChiTietSanPham(string id)
{
var db = new PetaPoco.Database("DienThoaiShopConnection");
return(db.SingleOrDefault <sanpham>("Select * from sanpham WHERE MaSanPham=@0", id));
}
public User GetUserByApiKey(string apiKey)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.SingleOrDefault<User>("select * from MR.tUser where ApiKey = @0", apiKey);
}
static void Main(string[] args)
{
//var table = new PowerPlants();
//var plants = table.All();
var point = SqlGeography.Point(47.8315, -121.626, 4326);
//var param = new SqlParameter("@point", point);
//param.UdtTypeName = "geography";
//param.SqlDbType = SqlDbType.Udt;
////var plantsNearBy = table.All(where: "WHERE geom.STDistance(geom.STDistance(@point) < 25", args: param);
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Spatial"].ConnectionString))
//using (SqlCommand cmd = new SqlCommand("Select * from PowerPlants where geom.STDistance(@point) < 25 * 1609.344", conn))
//{
// SqlParameter p = cmd.Parameters.Add("@point", sqlDbType: SqlDbType.Udt);
// p.UdtTypeName = "geography";
// SqlGeography geog = SqlGeography.Point(47.8315, -121.626, 4326);
// p.Value = geog;
// conn.Open();
// using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
// {
// var result = reader.ToExpandoList();
// }
//}
var db = new PetaPoco.Database("Spatial");
long count = db.ExecuteScalar<long>("SELECT Count(*) FROM PowerPlants");
var a = db.SingleOrDefault<PowerPlant>("WHERE PLANT_ID = @0", 72712);
var b = db.Fetch<PowerPlant>(@"SELECT * FROM PowerPlants where geom.STDistance(@0) < 25 * 1609.344", point);
var x = db.Fetch<PowerPlant>(PetaPoco.Sql.Builder.Append("WHERE geom.STDistance(@point) < @miles * 1609.344",
new { point = point, miles = 50 }));
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
Int64 iTicks = 1;
byte[] by = new byte[] { 0x4b, 0x0d, 0x0a };
// 打开所有串口
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
item.Value.StartListening(item.Key, 9600, System.IO.Ports.Parity.None, 8, System.IO.Ports.StopBits.One);
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("初始化串口" + item.Key + "发生异常: " + err.Message + Environment.NewLine);
});
}
}
while (isRun)
{
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
item.Value.SendMsg(by);
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("向串口" + item.Key + "发送数据失败: " + err.Message);
});
}
}
tbData.SafeCall(delegate()
{
tbData.Clear();
});
foreach (KeyValuePair<string, SerialPortManager> item in dicSerialPort)
{
try
{
string sData = item.Value.ReadData();
tbData.SafeCall(delegate()
{
tbData.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + item.Key + ": " + sData + Environment.NewLine);
});
if (sData.Length > 20)
{
try
{
string sDataTmp = sData.Substring(0, sData.Length - 4);
// 写入数据库
PetaPoco.Database db = new Database("XE");
DEVICECOMMUNICATION_LOG log = new DEVICECOMMUNICATION_LOG();
log.MSG = sData.Length > 3999 ? sData.Substring(0, 3999) : sData;
log.REMOTE_IP = toolStripTextBox1.Text + item.Key;
//log.REMOTE_PORT = remotePort;
log.RECEIVE_TIME = DateTime.Now;
db.Insert(log);
// 写入到台湾人要的表
var log1 = GetLog1Entity(sDataTmp, item.Key);
db.Insert(log1);
if (iTicks % 60 == 0)
{
BLOODCLEANUP_TEMP bt = new BLOODCLEANUP_TEMP();
bt = db.SingleOrDefault<BLOODCLEANUP_TEMP>("where to_char( ANA_DATE, 'yyyy-mm-dd') = @0 and SERIAL_PORT_NUM = @1",
new object[] { DateTime.Now.ToString("yyyy-MM-dd"), toolStripTextBox1.Text + item.Key });
if (bt != null)
{
string sTmp = sDataTmp.Substring(log.MSG.LastIndexOf('F') + 1, 5);
string sVp = sDataTmp.Substring(log.MSG.LastIndexOf('H') + 1, 5);
string sBf = sDataTmp.Substring(log.MSG.LastIndexOf('D') + 1, 5);
string sMaxBp = sDataTmp.Substring(log.MSG.LastIndexOf('N') + 1, 5);
string sMinBp = sDataTmp.Substring(log.MSG.LastIndexOf('O') + 1, 5);
string sPulse = sDataTmp.Substring(log.MSG.LastIndexOf('P') + 1, 5);
string sTotalUFAmount = sDataTmp.Substring(log.MSG.LastIndexOf('B') + 1, 5);
string sDC = sDataTmp.Substring(log.MSG.LastIndexOf('G') + 1, 5);
string sTMP = sDataTmp.Substring(log.MSG.LastIndexOf('J') + 1, 5);
BLOODCLEANUP_PROCESS proc = new BLOODCLEANUP_PROCESS();
proc.ANA_TIME = log.RECEIVE_TIME;
proc.TEMP = decimal.Parse(sTmp);
proc.VENOUS_PRESSURE = decimal.Parse(sVp);
proc.BLOOD_FLOW = decimal.Parse(sBf);
proc.BP = decimal.Parse(sMaxBp).ToString() + "~" + decimal.Parse(sMinBp);
proc.P = decimal.Parse(sPulse);
proc.ULTRAFILTRATION = decimal.Parse(sTotalUFAmount);
proc.CONDUCTIVITY = sDC;
proc.ARTERIAL_PRESSURE = decimal.Parse(sTMP);
proc.LOG_TIME = DateTime.Now;
proc.BLOODCLEANUP_ID = bt.BLOOD_CLEANUP_ID;
db.Insert(proc);
//proc.OPERATOR = ClsFrmMng.WorkerID;
}
}
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("写数据失败: " + err.Message + Environment.NewLine);
});
}
}
}
catch (Exception err)
{
tbData.SafeCall(delegate()
{
tbData.AppendText("读取串口" + item.Key + "数据失败: " + err.Message + Environment.NewLine);
});
}
}
Thread.Sleep(3000);
iTicks++;
}
}
public static T GetById(int id)
{
using (var connection = new PetaPoco.Database("myConnectionString"))
//connection.Delete<T>(id);
return(connection.SingleOrDefault <T>(id));
}
/// <summary>
/// Gets the user by their ID
/// </summary>
public RegisteredUser GetById(int id)
{
return(_database.SingleOrDefault <RegisteredUser>("where userId=@0", id));
}
private static void TransactionTest(Database db)
{
// Transaktio
using (var tx = new TransactionScope())
{
// Lähderivi
var source = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287350");
// Kohderivi
var target = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348");
target.CustName = source.CustName;
// Vars. muutos: "taulu", "avain", data
db.Update("customer", "custid", target);
// Luetaan testimielessä likainen rivi
var dirty = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348");
Console.WriteLine("dirty: {0} - {1}", dirty.CustId, dirty.CustName);
Console.WriteLine();
}
// Ei committia, eli ei tallentunut
var rolledback = db.SingleOrDefault<Customer>("SELECT * FROM Customer WITH(NOLOCK) WHERE CustId = 287348");
Console.WriteLine("eiku: {0} - {1}", rolledback.CustId, rolledback.CustName);
Console.WriteLine();
}
private static void addTagToTopic(int topicId, Tag tag)
{
var db = new Database("umbracoDbDSN");
if (tag.Id == 0)
{
db.Insert(tag);
}
var topicTag = db.SingleOrDefault<TopicTag>("WHERE tagId = @0 and topicId = @1", tag.Id, topicId);
if (topicTag == null)
{
topicTag = new TopicTag(tag.Id, topicId, tag.Weight);
db.Insert(topicTag);
}
}
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query <DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return(result);
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return(result);
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return(result);
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query <DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return(result);
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return(result);
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return(result);
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query <DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return(result);
}
}
public User GetUser(Guid authId)
{
var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
return db.SingleOrDefault<User>("select * from MR.tUser where AuthId = @0", authId);
}
public RetreaveIndex GetById(int id)
{
var result = _database.SingleOrDefault <RetreaveIndex>("where indexId=@0", id);
return(FillResult(result));
}
private static void UspQuery(Database db)
{
// Kysely uspilla
var customerId = 287350;
var viaUsp = db.SingleOrDefault<Customer>("execute [usp_MLYLdaGetCustomer] @0", customerId);
Console.WriteLine("{0} - {1}", viaUsp.CustId, viaUsp.CustName);
Console.WriteLine();
}
public static Topic GetTopic(int topicId)
{
var db = new Database("umbracoDbDSN");
var topic = db.SingleOrDefault<Topic>("SELECT * FROM forumTopics WHERE id = @0", topicId);
return topic;
}
public static Topic GetTopic(int topicId, bool getSpamTopic)
{
var db = new Database("umbracoDbDSN");
var topic = db.SingleOrDefault<Topic>(string.Format("SELECT * FROM forumTopics WHERE {0} id = @0", getSpamTopic ? "" : " (forumTopics.isSpam IS NULL OR forumTopics.isSpam != 1) AND "), topicId);
return topic;
}
