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 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."); }