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;
}
