public Companies() { StringBuilder __sb = new StringBuilder(); __sb.Append("select * from VW_DRUGMEMBER;"); DataTable dt; using (MSDBHelper db = new MSDBHelper(__sb.ToString(), util.strDBConn)) { dt = db.GetDataTable(); } _companylist = new ObservableCollection<Company>( (from __row in dt.AsEnumerable() select new Company() { memuid = __row[0].ToString() , comname = __row[1].ToString() , comnum = __row[2].ToString() , comhg = __row[3].ToString() , comtel = __row[4].ToString() }).ToList<Company>() ); }
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { string _tel = value.ToString(); if (!string.IsNullOrEmpty(_tel)) { DataTable __dt = null; StringBuilder _sql = new StringBuilder(); _sql.AppendFormat(" select company from VW_DRUGMEMBER where replace(phone, '-', '') = '{0}' ", _tel.Trim()); using (MSDBHelper db = new MSDBHelper(_sql.ToString(), util.strDBConn)) { try { __dt = db.GetDataTable(); } catch (System.Data.SqlClient.SqlException __se) { } } if (__dt.Rows.Count > 0) { _tel = string.Format("{0}({1})", __dt.Rows[0][0].ToString(), _tel); } } return _tel; }
public DataTable GetList() { DataTable dt; StringBuilder sb = new StringBuilder(); sb.Append(" select idx, callfor, callernum, calleenum "); sb.Append(" , convert(varchar(10), CALL_SDATE, 120) as syymmdd "); sb.Append(" , convert(varchar(10), CALL_EDATE, 120) as eyymmdd "); sb.Append(" , convert(varchar(8), CALL_SDATE, 114) as shhmmss "); sb.Append(" , convert(varchar(8), CALL_EDATE, 114) as ehhmmss "); sb.Append(" , convert(varchar(10), REGDATE, 120) as regyymmdd "); sb.Append(" , convert(varchar(8), REGDATE, 114) as reghhmmss "); sb.Append(" , call_time "); sb.Append(" , case "); sb.Append(" when call_status = 0 then '대기중' "); sb.Append(" when call_status = 1 then '부재중' "); sb.Append(" when call_status = 2 then '통화중' "); sb.Append(" when call_status = 3 then '통화완료' "); sb.Append(" end callstatus "); sb.Append(" from IPCC_CALL_LIST "); sb.Append(" where year(regdate)=year(getdate()) "); sb.Append(" and month(regdate)=month(getdate()) "); sb.Append(" and day(regdate)=day(getdate()) "); sb.Append(" order by regdate desc; "); using (MSDBHelper db = new MSDBHelper(strConn)) { try { db.Sql = sb.ToString(); dt = db.GetDataTable(); } catch (System.Data.SqlClient.SqlException e) { throw e; } } return dt; }
public DataTable GetList(string sdate, string edate, bool? isallList) { DataTable dt; dt = util.CreateDT2SP(); dt.Rows.Add("@TOKEN", isallList); dt.Rows.Add("@SDATE", sdate); dt.Rows.Add("@EDATE", edate); using (MSDBHelper db = new MSDBHelper(strConn)) { try { dt = db.GetDataTableSP("IPCC_GET_CALL_LIST", dt); } catch(System.Data.SqlClient.SqlException e) { throw e; } } return dt; }
public DataTable GetCallStaticByTime(int yy, int mm, int dd, int gubun) { DataTable data; StringBuilder sb = new StringBuilder(); if (gubun == 0 || gubun == 1) { sb.Append(" select isnull(count(idx), 0) callcount "); sb.Append(" , isnull(sum(call_time), 0) calltimesec "); sb.Append(" , convert(float, "); sb.Append(" convert(varchar(10), isnull(sum(call_time), 0)/60) "); sb.Append(" + '.' "); sb.Append(" + case "); sb.Append(" when len(isnull(sum(call_time), 0)%60)=1 then '0'+convert(char(1),isnull(sum(call_time), 0)%60) "); sb.Append(" else convert(char(2),isnull(sum(call_time), 0)%60) "); sb.Append(" end "); sb.Append(" ) calltimemin "); sb.Append(" , b.wtime worktime "); sb.Append(" , ltrim(str(b.wtime)) + '시' worktimetxt "); sb.Append(" from ipcc_call_list a right join ipcc_tbl_worktime b "); sb.Append(" on datepart(hh, a.regdate)=b.wtime "); sb.AppendFormat(" and datepart(yy, a.regdate)={0} ", yy); sb.AppendFormat(" and datepart(mm, a.regdate)={0} ", mm); sb.AppendFormat(" and datepart(dd, a.regdate)={0} ", dd); sb.AppendFormat(" group by b.wtime "); sb.AppendFormat(" order by b.wtime desc; "); } else if (gubun == 2) { sb.Append(" select isnull(count(idx), 0) callcount "); sb.Append(" , isnull(sum(call_time), 0) calltimesec "); sb.Append(" , convert(float, "); sb.Append(" convert(varchar(10), isnull(sum(call_time), 0)/60) "); sb.Append(" + '.' "); sb.Append(" + case "); sb.Append(" when len(isnull(sum(call_time), 0)%60)=1 then '0'+convert(char(1),isnull(sum(call_time), 0)%60) "); sb.Append(" else convert(char(2),isnull(sum(call_time), 0)%60) "); sb.Append(" end "); sb.Append(" ) calltimemin "); sb.Append(" , b.wtime worktime "); sb.Append(" , ltrim(str(b.wtime)) + '시' worktimetxt "); sb.Append(" from ipcc_call_list a right join ipcc_tbl_worktime b "); sb.Append(" on datepart(hh, a.regdate)=b.wtime "); sb.AppendFormat(" and datepart(yy, a.regdate)={0} ", yy); sb.AppendFormat(" and datepart(mm, a.regdate)={0} ", mm); //sb.AppendFormat(" --and datepart(dd, a.regdate)={0} ", dd); sb.AppendFormat(" group by b.wtime "); sb.AppendFormat(" order by b.wtime desc; "); } using (MSDBHelper db = new MSDBHelper(strConn)) { try { db.CommandType = CommandType.Text; db.Sql = sb.ToString(); data = db.GetDataTable(); } catch (System.Data.SqlClient.SqlException e) { throw e; } } return data; }
private void CheckRoonetsDB() { var watch = Stopwatch.StartNew(); DataTable dt = null; // Check IN/OUT using (MSDBHelper db = new MSDBHelper(DBServer)) { try { db.Sql = "select T1_ID, T1_SITE, T1_ROOM, T1_CODE, T1_PERIOD, T1_PROOM, T1_DATE, T1_READ, T1_READ2, T1_TXT2 from INF_CT01 where T1_READ=0 or T1_READ2=0 order by T1_DATE asc"; db.Open(); dt = db.GetDataTable(); } catch (SqlException e) { util.WriteLog(e.Message); } } List<TCT01> tempdata = new List<TCT01> (from DataRow row in dt.AsEnumerable() select new TCT01 { T_ID = row[0].ToString(), T_SITE = row[1].ToString(), T_ROOM = row[2].ToString(), T_CODE = row[3].ToString(), T_PERIOD = row[4].ToString(), T_PROOM = row[5].ToString(), T_DATE = (DateTime)row[6], T_READ = int.Parse(row[7].ToString()), T_READ2 = int.Parse(row[8].ToString()), T_TXT2 = row[9].ToString() }).ToList<TCT01>(); foreach (TCT01 item in tempdata) { string roomnumberext = string.IsNullOrEmpty(item.T_ROOM) == true ? string.Empty : int.Parse(item.T_ROOM).ToString(); if (string.IsNullOrEmpty(roomnumberext)) continue; _cgi_pms_data_type original_data = new _cgi_pms_data_type(); original_data = h2.GetPolicy(roomnumberext); bool result = false; int count = 0; if (item.T_CODE.Equals("3")) { if (item.T_ROOM.Equals(item.T_PROOM)) { // 일자변경 result = h2.SetSystem("5", roomnumberext, item.T_PERIOD, item.T_TXT2); } else { roomnumberext = string.IsNullOrEmpty(item.T_PROOM) == true ? string.Empty : int.Parse(item.T_PROOM).ToString(); result = h2.SetSystem("0", roomnumberext, item.T_PERIOD, item.T_TXT2); roomnumberext = string.IsNullOrEmpty(item.T_ROOM) == true ? string.Empty : int.Parse(item.T_ROOM).ToString(); if (item.T_PERIOD.Equals("0")) { result = h2.SetSystem("1", roomnumberext, item.T_PERIOD, item.T_TXT2); } else { result = h2.SetSystem("2", roomnumberext, item.T_PERIOD, item.T_TXT2); } } } else { result = h2.SetSystem(item.T_CODE, roomnumberext, item.T_PERIOD, item.T_TXT2); } if (item.T_CODE.Equals("O")) { using (MSDBHelper db = new MSDBHelper(DBServer)) { try { db.Sql = string.Format("update INF_CT01 set T1_READ=1, T1_READ2=1 where T1_READ2={0} and T1_SITE='{1}' and T1_ROOM='{2}' and T1_DATE=cast('{3}' as datetime)", item.T_READ2, item.T_SITE, item.T_ROOM, item.T_DATE.ToString("yyyy-MM-dd HH:mm:ss.fff")); db.Open(); db.BeginTran(); count = db.GetEffectedCount(); db.Commit(); item.T_READ = 1; item.T_READ2 = 1; } catch (SqlException e) { db.Rollback(); result = h2.RestoreSystem(original_data); } } } else { using (MSDBHelper db = new MSDBHelper(DBServer)) { try { db.Sql = string.Format("update INF_CT01 set T1_READ=1, T1_READ2=1 where T1_SITE='{0}' and T1_ROOM='{1}' and T1_DATE=cast('{2}' as datetime)", item.T_SITE, item.T_ROOM, item.T_DATE.ToString("yyyy-MM-dd HH:mm:ss.fff")); db.Open(); db.BeginTran(); count = db.GetEffectedCount(); db.Commit(); item.T_READ = 1; item.T_READ2 = 1; } catch (SqlException e) { db.Rollback(); result = h2.RestoreSystem(original_data); } } } } // Check House Keeping dt = null; using (MSDBHelper db = new MSDBHelper(DBServer)) { try { db.Sql = "select T3_ID, T3_SITE, T3_ROOM, T3_CODE, T3_READ, T3_TXT1, T3_DATE from INF_CT03 where T3_READ=0;"; db.Open(); dt = db.GetDataTable(); } catch (SqlException e) { util.WriteLog(e.Message); } } List<TCT03> tempdata2 = new List<TCT03>(from DataRow row in dt.AsEnumerable() select new TCT03 { T_ID = row[0].ToString(), T_SITE = row[1].ToString(), T_ROOM = row[2].ToString(), T_CODE = row[3].ToString(), T_READ = int.Parse(row[4].ToString()), T_TXT = row[5].ToString(), T_DATE = (DateTime)row[6], }).ToList<TCT03>(); //try //{ foreach (TCT03 item in tempdata2) { string roomnumberext = string.IsNullOrEmpty(item.T_ROOM) == true ? string.Empty : int.Parse(item.T_ROOM).ToString(); _cgi_pms_data_type original_data = new _cgi_pms_data_type(); original_data = h2.GetPolicy(roomnumberext); bool result = false; int count = 0; result = h2.SetHouseKeep(item.T_CODE, roomnumberext, item.T_TXT); using (MSDBHelper db = new MSDBHelper(DBServer)) { try { db.Sql = string.Format("update INF_CT03 set T3_READ=1 where T3_SITE='{0}' and T3_ROOM='{1}' and T3_DATE=cast('{2}' as datetime)", item.T_SITE, item.T_ROOM, item.T_DATE.ToString("yyyy-MM-dd HH:mm:ss.fff")); db.Open(); db.BeginTran(); count = db.GetEffectedCount(); db.Commit(); } catch (SqlException e) { db.Rollback(); result = h2.RestoreSystem(original_data); continue; } } } //} //catch (Exception e) //{ // util.WriteLog(e.Message); //} watch.Stop(); var elapsedMs = watch.ElapsedMilliseconds; Debug.WriteLine(">>>>>>>>>>>>>>>>>>>>>>>>>>>>> CheckRoonetsDB was done in " + elapsedMs + " mil."); }
private void H2_PassDevice2PmsEvent(object sender, _pms_data_type pmsdata) { if (Device2CorePmsEvent != null) Device2CorePmsEvent(this, pmsdata); if (RunType == RunningType.CORETREE) return; string t2_code = string.Empty; string t2_emer = "0"; string t2_dnd = "0"; string t2_mur = "0"; string t2_txt1 = string.Empty; switch (pmsdata.cmd) { case STRUCTS.PMS_SET_MORNING_CALL_REQ: t2_code = "C"; DateTime nowdate = DateTime.Now; DateTime mcalldate = DateTime.Parse(string.Format("{0}-{1}-{2} {3:D2}:{4:D2}:00", nowdate.Year, nowdate.Month, nowdate.Day, pmsdata.hour, pmsdata.minutes)); TimeSpan ts = mcalldate - nowdate; if (ts.TotalSeconds >= 0) { t2_txt1 = mcalldate.ToString("yyyyMMddHHmm"); } else { nowdate.AddSeconds(-(ts.TotalSeconds)); t2_txt1 = nowdate.ToString("yyyyMMddHHmm"); } break; //case STRUCTS.PMS_CLEAR_MORNING_CALL_REQ: // t2_code = "D"; // break; case STRUCTS.PMS_SET_LANGUAGE_REQ: break; case STRUCTS.PMS_REPORT_FUNCTION_KEY_REQ: if (pmsdata.function_key.Equals(fk_cleanroom)) { switch (pmsdata.function_key_cmd) { case 1: t2_mur = "1"; break; case 2: default: t2_mur = "0"; break; } } else if (pmsdata.function_key.Equals(fk_dnd)) { switch (pmsdata.function_key_cmd) { case 1: t2_dnd = "1"; break; case 2: default: t2_dnd = "0"; break; } } else if (pmsdata.function_key.Equals(fk_laundry)) { switch (pmsdata.function_key_cmd) { case 1: t2_code = "G"; break; case 2: default: t2_code = "H"; break; } } else if (pmsdata.function_key.Equals(fk_roomservice)) { switch (pmsdata.function_key_cmd) { case 1: t2_code = "E"; break; case 2: default: t2_code = "F"; break; } } else if (pmsdata.function_key.Equals(fk_cleaningroom_complete)) { switch (pmsdata.function_key_cmd) { case 1: case 2: default: t2_code = "0"; break; } } else if (pmsdata.function_key.Equals(fk_cleaningroom_inspection)) { switch (pmsdata.function_key_cmd) { case 1: case 2: default: t2_code = "9"; break; } } else if (pmsdata.function_key.Equals(fk_emergency)) { switch (pmsdata.function_key_cmd) { case 1: t2_emer = "1"; break; case 2: default: t2_emer = "0"; break; } } break; case STRUCTS.PMS_REPORT_MAKEUP_STATUS_REQ: switch (pmsdata.makeup_room_status) { case 1: t2_code = "0"; break; case 2: t2_code = "2"; break; case 3: t2_code = "9"; break; case 4: t2_code = "2"; break; } break; default: // Debug.WriteLine("Pmsserver_ReqPMSSetEvent: " + pmsdata.function_key + " // " + pmsdata.function_key_cmd); break; } using (MSDBHelper db = new MSDBHelper(DBServer)) { string ext = string.Format("{0:D4}", int.Parse(pmsdata.extension)); string sql = string.Format("insert into INF_CT02 ( T2_ROOM, T2_CODE, T2_EMER, T2_DND, T2_MUR, T2_DATE, T2_TXT1 ) values ( '{0}', '{1}', '{2}', '{3}', '{4}', getdate(), '{5}' )", sitecode + ext, t2_code, t2_emer, t2_dnd, t2_mur, t2_txt1); db.Sql = sql; try { db.Open(); db.BeginTran(); int count = db.GetEffectedCount(); db.Commit(); h2.Send(pmsdata); } catch (SqlException e) { util.WriteLog(e.Message); db.Rollback(); pmsdata.status = STRUCTS.PMS_STATUS_FAIL; h2.Send(pmsdata); } catch (System.Net.Sockets.SocketException ex) { util.WriteLog(ex.Message); } } }
public bool DeleteList(string curcallidx) { bool rtnBool = false; StringBuilder sb = new StringBuilder(); sb.Append(" delete from ipcc_call_list "); sb.AppendFormat(" where idx={0} ", curcallidx); int chk = 0; using (MSDBHelper db = new MSDBHelper(strConn)) { try { db.Sql = sb.ToString(); db.BeginTran(); chk = db.GetEffectedCount(); db.Commit(); rtnBool = true; } catch(System.Data.SqlClient.SqlException e) { db.Rollback(); rtnBool = false; } } return rtnBool; }