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