private void INIT()
{
DataTable dt = null;
StringBuilder sb = new StringBuilder();
sb.Append("select idx, title from FIRMS order by title asc");
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
dt = db.GetDataTable();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
util.WriteLog(string.Format("{0} INIT ERR : {1}", this.GetType(), fex.Message));
return;
}
}
_list = new List<Firm>(
(from _row in dt.AsEnumerable()
select new Firm()
{
idx = int.Parse(_row["idx"].ToString())
,
title = _row["title"].ToString()
}
).ToList<Firm>()
);
}
private void INIT()
{
DataTable dt = null;
StringBuilder sb = new StringBuilder();
sb.Append("select innertel, title from EXCEPTIONS order by innertel asc");
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
dt = db.GetDataTable();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
util.WriteLog(string.Format("{0} INIT ERR : {1}", this.GetType(), fex.Message));
return;
}
}
_list = new ObservableCollection<BILLException>(
(from _row in dt.AsEnumerable()
select new BILLException()
{
innertel = _row[0].ToString()
, title = _row[1].ToString()
}
).ToList<BILLException>()
);
}
public AlarmBirths()
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strDBConn))
{
DataTable dt = util.MakeDataTable2Proc();
DataRow dr = dt.NewRow();
dr["DataName"] = "@i_com_idx";
dr["DataValue"] = util.Userinfo.COM_IDX;
dt.Rows.Add(dr);
try
{
dt = db.GetDataTableSP("GET_ALARM_BIRTH", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
throw fe;
}
_obslist = new ObservableCollection<AlarmBirth>(
(from myRow in dt.AsEnumerable()
select new AlarmBirth()
{
Idx = myRow["o_idx"].ToString()
, Name = myRow["o_name"].ToString()
, Birth = myRow["o_birth"].ToString()
, Birth_Luna = myRow["o_birth_luna"].ToString()
, Cstg_Name = myRow["o_cstg_name"].ToString()
, Remain_Birth = myRow["o_remain_birth"].ToString()
}).ToList<AlarmBirth>());
}
}
public AlarmAttentions()
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strDBConn))
{
DataTable dt = util.MakeDataTable2Proc();
DataRow dr = dt.NewRow();
dr["DataName"] = "@i_com_idx";
dr["DataValue"] = util.Userinfo.COM_IDX;
dt.Rows.Add(dr);
try
{
dt = db.GetDataTableSP("GET_ALARM_ATTENTION", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
throw fe;
}
_obslist = new ObservableCollection<AlarmAttention>(
(from myRow in dt.AsEnumerable()
select new AlarmAttention()
{
Ch_Idx = myRow["o_ch_idx"].ToString()
, Ch_Name = myRow["o_ch_name"].ToString()
, Evt_Title = myRow["o_evt_title"].ToString()
, Evt_Memo = myRow["o_evt_memo"].ToString()
, Evt_Sdate = myRow["o_evt_sdate"].ToString()
}).ToList<AlarmAttention>());
}
}
public Interviewes(int _id, string _sdate, string _edate, string _txt2, string _txt3)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@iseq", _id);
dt.Rows.Add("@isdate", _sdate);
dt.Rows.Add("@iedate", _edate);
dt.Rows.Add("@itxt2", _txt2);
dt.Rows.Add("@itxt3", _txt3);
using ( FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_INTERVIEWS", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
_list = new ObservableCollection<Interview>(
(from __row in dt.AsEnumerable()
select new Interview()
{
seq = long.Parse(__row[0].ToString())
,
fseq = int.Parse(string.IsNullOrEmpty(__row[1].ToString()) ? "0" : __row[1].ToString())
,
forward = __row[2].ToString()
,
extention = __row[3].ToString()
,
peernum = __row[4].ToString()
,
recfile = __row[5].ToString()
,
regdate = DateTime.Parse(__row[6].ToString())
,
regyymmdd = __row[7].ToString()
,
reghhmmss = __row[8].ToString()
,
fnlen = double.Parse(__row[9].ToString())
,
tellername = __row[10].ToString()
}).ToList<Interview>()
);
}
public Consultants(string _chk, string _sdate, string _edate, string _teller)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@I_CHK", _chk);
dt.Rows.Add("@I_SDATE", _sdate);
dt.Rows.Add("@I_EDATE", _edate);
dt.Rows.Add("@I_TELLER", _teller);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_LIST_BY_TELNUM", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
_list = new ObservableCollection<Consultant>(
(from _row in dt.AsEnumerable()
select new Consultant()
{
telnum = _row["O_TELNUM"].ToString()
,
tellername = _row["O_TELLERNAME"].ToString()
,
calledsec = int.Parse(_row["O_CALLEDSEC"].ToString())
,
callednum = int.Parse(_row["O_CALLEDNUM"].ToString())
,
succeed = int.Parse(_row["O_SUCCEED"].ToString())
,
absence = int.Parse(_row["O_ABSENCE"].ToString())
,
failed = int.Parse(_row["O_FAILED"].ToString())
,
avgcalledsec = int.Parse(_row["O_AVGCALLEDSEC"].ToString())
}).ToList<Consultant>()
);
}
public FamilyRoles()
{
DataTable dt = null;
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strDBConn))
{
try
{
dt = db.GetDataTableSP("GET_FAMILY_ROLE");
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
FamilyRole fr = new FamilyRole() {
Fr_Idx = "0"
, Fr_Name = util.LoadProjectResource("TEXT_CB_FIRSTFIELD", "COMMONRES", "").ToString()
, Fr_Sort = "0"
, Fr_Use = "1"
};
_roles.Add(fr);
foreach (DataRow myRow in dt.Rows)
{
fr = new FamilyRole()
{
Fr_Idx = myRow["o_fr_idx"].ToString()
,
Fr_Name = myRow["o_fr_name"].ToString()
,
Fr_Sort = myRow["o_fr_sort"].ToString()
,
Fr_Use = myRow["o_fr_use"].ToString()
};
_roles.Add(fr);
}
}
public CDRLists(string _sdate, string _edate, string _gubun, string _teller)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@i_sdate", _sdate);
dt.Rows.Add("@i_edate", _edate);
dt.Rows.Add("@i_gubun", _gubun);
dt.Rows.Add("@i_teller", _teller);
using ( FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_CDR_LIST_BY_DATE2", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
_list = new ObservableCollection<CDRs>(
(from _row in dt.AsEnumerable()
select new CDRs()
{
startdate = _row[2].ToString()
,
enddate = _row[3].ToString()
,
callernum = _row[5].ToString()
,
callername = _row[9].ToString()
,
calleenum = _row[10].ToString()
}).ToList<CDRs>()
);
}
public Accounts()
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@I_ID", util.userid);
try
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
DataTable _dt = db.GetDataTableSP("GET_USRS", dt);
_list = new ObservableCollection<Account>(
(from _row in _dt.AsEnumerable()
select new Account()
{
ID = _row[0].ToString()
,
NAME = _row[1].ToString()
}
).ToList<Account>()
);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe1)
{
//throw fe;
}
}
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe0)
{
_list = new ObservableCollection<Account>();
MessageBox.Show("Database 접속에 문제가 발생하였습니다.\r\n \"도구 → 환경설정 → 서버주소\"을 확인 후 다시 실행해 주세요.");
}
}
public void ADD(Firm item)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into FIRMS");
sb.Append("( title )");
sb.Append(" values ");
sb.AppendFormat("( {0} )", item.title);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
int count = db.GetEffectedCount();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} ADD ERR : {1}", this.GetType(), fex.Message));
return;
}
}
INIT();
}
public RealStatusList(int _chk, string _sdate, string _edate)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@i_chk", _chk);
dt.Rows.Add("@i_sdate", _sdate);
dt.Rows.Add("@i_edate", _edate);
try
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_REALTIME_STATUS", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe1)
{
//throw fe;
}
}
Items = new List<StatisticRealStatus>(
(from _row in dt.AsEnumerable()
select new StatisticRealStatus()
{
Label = _row[0].ToString()
,
Value1 = int.Parse(_row[1].ToString())
}
)
);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe0)
{
}
}
private async void ShowLoginDialog()
{
//this.MetroDialogOptions.ColorScheme = UseAccentForDialogsMenuItem.IsChecked ? MetroDialogColorScheme.Accented : MetroDialogColorScheme.Theme;
LoginDialogData result = await this.ShowLoginAsync("Authentication", "Enter your credentials", new LoginDialogSettings { ColorScheme = this.MetroDialogOptions.ColorScheme, InitialUsername = string.Empty });
if (result == null)
{
//User pressed cancel
this.Close();
}
else
{
//MessageDialogResult messageResult = await this.ShowMessageAsync("Authentication Information", String.Format("Username: {0}\nPassword: {1}", result.Username, result.Password));
DataTable dt = null;
StringBuilder query = new StringBuilder();
query.Append(" select idx, identity, pwd, firm_idx, role_idx from users ");
query.AppendFormat(" where identity='{0}' and pwd='{1}'", result.Username.Trim(), util.GetSHA1(result.Password));
using (FirebirdDBHelper db = new FirebirdDBHelper(query.ToString(), util.strDBConn))
{
try
{
dt = db.GetDataTable();
}
catch
{
dt = null;
}
}
if (dt == null || dt.Rows.Count == 0)
{
// do when login failed
MessageDialogResult messageResult = await this.ShowMessageAsync("Authentication", "Logon failed.\r\nPlease, identify your information one more time.");
ShowLoginDialog();
}
else
{
// succeed
}
}
}
public void REMOVE(DOMESTICRATE item)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from DOMESTIC_RATE where prefix={0}", item.prefix);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} REMOVE ERR : {1}", this.GetType(), fex.Message));
return;
}
}
_list.Remove(item);
//INIT();
}
private void FileName2DB(double idx, string fn, double fnlen, string ext, string peernum)
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.GetFbDbStrConn()))
{
db.SetParameters("@EXTENTION", FbDbType.VarChar, ext);
db.SetParameters("@PEERNUMBER", FbDbType.VarChar, peernum);
db.SetParameters("@FN", FbDbType.VarChar, fn);
db.SetParameters("@FNLEN", FbDbType.Double, fnlen);
try
{
db.BeginTran();
db.ExcuteSP("INS_RECINFO3");
db.Commit();
}
catch (FbException e)
{
util.WriteLog(string.Format("SQL INS ERROR (INS_RECINFO3)\r\nMessage : {0}", e.Message));
db.Rollback();
}
}
}
public InnerTels()
{
DataTable dt = null;
try
{
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_INNERTELS");
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe1)
{
//throw fe;
MessageBox.Show("test");
}
}
_list = new ObservableCollection<InnerTel>(
(from __row in dt.AsEnumerable()
select new InnerTel()
{
Seq = int.Parse(__row[0].ToString())
,
Telnum = " " + __row[1].ToString()
,
TellerName = __row[2].ToString()
}
).ToList<InnerTel>()
);
//InnerTel _tmptel = new InnerTel() { Seq = 0, Telnum = "전체" };
//_list.Insert(0, _tmptel);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe0)
{
_list = new ObservableCollection<InnerTel>();
MessageBox.Show("Database 접속에 문제가 발생하였습니다.\r\n \"도구 → 환경설정 → 서버주소\"을 확인 후 다시 실행해 주세요.");
}
}
// 체크아웃
private void contextmenu4_Click(object sender, RoutedEventArgs e)
{
MenuItem menuItem = (MenuItem)e.Source;
ContextMenu contextMenu = (ContextMenu)menuItem.Parent;
selectedListBox = (ListBox)contextMenu.PlacementTarget;
List<RoomItem> _seccessRoom = new List<RoomItem>();
string _failRoom = string.Empty;
foreach (RoomItem item in selectedListBox.SelectedItems)
{
var pmsdata = item.PMSDATA;
//pmsdata.allowedPrefix = string.Empty;
//pmsdata.forbiddenPrefix = "all";
//pmsdata.hour = -1;
//pmsdata.minutes = -1;
//pmsdata.try_interval = 3;
//pmsdata.repeat_times = 5;
//pmsdata.ring_duration = 120;
//pmsdata.language = 0;
//pmsdata.week = string.Empty;
//pmsdata.cmd = STRUCTS.PMS_SET_ALL_REQ;
bool _result = false;
using (HotelHelper hh = new HotelHelper(util.PBXIP))
{
_result = hh.CheckOut(pmsdata.extension);
}
if (_result)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@I_ROOM", item.RoomNum);
dt.Rows.Add("@I_STATUS", "0");
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strDBConn))
{
try
{
db.BeginTran();
db.ExcuteSP("UDT_ROOM_STATUS", dt);
db.Commit();
item.PMSDATA = pmsdata;
item.IsChecked = false;
item.Languages = "0";
item.Hour = -1;
item.Minutes = -1;
item.States = "0";
_seccessRoom.Add(item);
util.Log2DB(item.RoomNum, string.Format("CHECK OUT : {0}", item.RoomNum), "0");
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("update hroom set states_clean=0, states_laundary=0, states_parcel=0 where room={0}", item.RoomNum);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int c = db.GetEffectedCount();
db.Commit();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
}
else
{
item.IsChecked = false;
if (string.IsNullOrEmpty(_failRoom))
_failRoom = item.RoomNum;
else
_failRoom += string.Format(",{0}", item.RoomNum);
}
}
var lbs = this.FindChild<ListBox>("listrooms");
foreach (var itm in _seccessRoom)
{
lbs.SelectedItems.Remove(itm);
}
string alertmsg = string.Empty;
int alertsec = 0;
if (string.IsNullOrEmpty(_failRoom))
{
alertmsg = "체크아웃 설정이 완료 되었습니다.";
alertsec = (int)AlertDelaySec.Success;
}
else
{
alertmsg = string.Format("체크아웃 실패\r\n방번호 : {0}", _failRoom);
alertsec = (int)AlertDelaySec.Fail;
}
ShowAlertDialog(alertmsg, alertsec);
}
private void INIT()
{
DataTable dt = null;
StringBuilder sb = new StringBuilder();
sb.Append("select idx, areacode, nation_num, nation_local_num, natione, nationk, lm from INTERNATIONAL order by nationk asc");
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
dt = db.GetDataTable();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
util.WriteLog(string.Format("{0} INIT ERR : {1}", this.GetType(), fex.Message));
return;
}
}
_list = new ObservableCollection<INTERNATIONAL>(
(from _row in dt.AsEnumerable()
select new INTERNATIONAL()
{
idx = long.Parse(_row["idx"].ToString())
,
areacode = int.Parse(_row["areacode"].ToString())
,
nation_num = _row["nation_num"].ToString()
,
nation_local_num = _row["nation_local_num"].ToString()
,
natione = _row["natione"].ToString()
,
nationk = _row["nationk"].ToString()
,
lm = _row["lm"].ToString()
}
).ToList<INTERNATIONAL>()
);
}
public void MODIFY(Firm item)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("update FIRMS set title={0} where idx={1}", item.title, item.idx);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
int count = db.GetEffectedCount();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} MODIFY ERR : {1}", this.GetType(), fex.Message));
return;
}
}
INIT();
}
private void CdrToDB(CdrRequest_t _cdr, CdrList _cdrdata)
{
CDRData _cdrd = new CDRData() {
OFFICE_NAME = _cdrdata.office_name
,
STARTDATE = new DateTime(_cdrdata.start_yyyy, _cdrdata.start_month, _cdrdata.start_day, _cdrdata.start_hour, _cdrdata.start_min, _cdrdata.start_sec)
,
ENDDATE = new DateTime(_cdrdata.end_yyyy, _cdrdata.end_month, _cdrdata.end_day, _cdrdata.end_hour, _cdrdata.end_min, _cdrdata.end_sec)
,
CALLER = _cdrdata.caller
,
CALLER_TYPE = _cdrdata.caller_type
,
CALLER_IPN_NUMBER = _cdrdata.caller_ipn_number
,
CALLER_GROUP_CODE = _cdrdata.caller_group_code
,
CALLER_GROUP_NAME = _cdrdata.caller_group_name
,
CALLER_HUMAN_NAME = _cdrdata.caller_human_name
,
CALLEE = _cdrdata.callee
,
CALLEE_TYPE = _cdrdata.callee_type
,
CALLEE_IPN_NUMBER = _cdrdata.callee_ipn_number
,
CALLEE_GROUP_CODE = _cdrdata.callee_group_code
,
CALLEE_GROUP_NAME = _cdrdata.callee_group_name
,
CALLEE_HUMAN_NAME = _cdrdata.callee_human_name
,
RESULT = _cdrdata.result
,
SEQ = _cdrdata.seq
};
StringBuilder __slqsb = new StringBuilder();
__slqsb.Append(" insert into CDRINFO ");
__slqsb.Append(" ( ");
__slqsb.Append(" OFFICE_NAME ");
__slqsb.Append(" , STARTDATE ");
__slqsb.Append(" , ENDDATE ");
__slqsb.Append(" , CALLER ");
__slqsb.Append(" , CALLER_TYPE ");
__slqsb.Append(" , CALLER_IPN_NUMBER ");
__slqsb.Append(" , CALLER_GROUP_CODE ");
__slqsb.Append(" , CALLER_GROUP_NAME ");
__slqsb.Append(" , CALLER_HUMAN_NAME ");
__slqsb.Append(" , CALLEE ");
__slqsb.Append(" , CALLEE_TYPE ");
__slqsb.Append(" , CALLEE_IPN_NUMBER ");
__slqsb.Append(" , CALLEE_GROUP_CODE ");
__slqsb.Append(" , CALLEE_GROUP_NAME ");
__slqsb.Append(" , CALLEE_HUMAN_NAME ");
__slqsb.Append(" , RESULT ");
__slqsb.Append(" , SEQ ");
__slqsb.Append(" ) values ( ");
__slqsb.AppendFormat(" '{0}' ", _cdrd.OFFICE_NAME);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.STARTDATE.ToString("yyyy-MM-dd HH:mm:ss"));
__slqsb.AppendFormat(" , '{0}' ", _cdrd.ENDDATE.ToString("yyyy-MM-dd HH:mm:ss"));
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER_TYPE);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER_IPN_NUMBER);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER_GROUP_CODE);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER_GROUP_NAME);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLER_HUMAN_NAME);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE_TYPE);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE_IPN_NUMBER);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE_GROUP_CODE);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE_GROUP_NAME);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.CALLEE_HUMAN_NAME);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.RESULT);
__slqsb.AppendFormat(" , '{0}' ", _cdrd.SEQ);
__slqsb.Append(" ); ");
using (FirebirdDBHelper db = new FirebirdDBHelper(__slqsb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int __count = db.GetEffectedCount();
db.Commit();
CdrResponse_t __cdrRes = this.GetCdrRtnMsg(_cdr);
byte[] __buffer = util.GetBytes(__cdrRes);
try
{
_sockCdrSrv.SendTo(__buffer, SocketFlags.None, _remoteep);
}
catch (SocketException __se)
{
util.WriteLog(string.Format("Socket send message error : {0}", __se.Message));
}
}
catch (FirebirdSql.Data.FirebirdClient.FbException __fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} : {1}\r\nMessage : {2}", "Rollback Exception Type", __fex.GetType(), __fex.Message));
}
catch(SocketException __se)
{
util.WriteLog(string.Format("Socket send message error : {0}", __se.Message));
}
}
}
public void Modify(InnerTel _itel)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@seq", _itel.Seq);
dt.Rows.Add("@telnum", _itel.Telnum);
dt.Rows.Add("@tellername", _itel.TellerName);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
db.BeginTran();
db.ExcuteSP("UDT_INNERTELS", dt);
db.Commit();
InnerTel __obj = _list.FirstOrDefault(x => x.Telnum == _itel.Telnum);
__obj.TellerName = _itel.TellerName;
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
}
public void Add(InnerTel _itel)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@telnum", _itel.Telnum);
dt.Rows.Add("@tellername", _itel.TellerName);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
db.BeginTran();
db.ExcuteSP("INS_INNERTELS", dt);
db.Commit();
_list.Add(_itel);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
}
public void REMOVE(INTERNATIONAL item)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from INTERNATIONAL where idx={0}", item.idx);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} REMOVE ERR : {1}", this.GetType(), fex.Message));
return;
}
}
INIT();
}
public void MODIFY(INTERNATIONAL item)
{
StringBuilder sb = new StringBuilder();
sb.Append("update INTERNATIONAL set");
sb.AppendFormat(" areacode={0}, nation_num='{1}', nation_local_num='{2}', natione='{3}', nationk='{4}', lm='{5}' where idx={6}", item.areacode, item.nation_num, item.nation_local_num, item.natione, item.nationk, item.lm, item.idx);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} MODIFY ERR : {1}", this.GetType(), fex.Message));
return;
}
}
INIT();
}
public void ADD(INTERNATIONAL item)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into INTERNATIONAL");
sb.Append("( areacode, nation_num, nation_local_num, natione, nationk, lm )");
sb.Append(" values ");
sb.AppendFormat("( {0}, '{1}', '{2}', '{3}', '{4}', '{5}' )", item.areacode, item.nation_num, item.nation_local_num, item.natione, item.nationk, item.lm);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
}
catch (FirebirdSql.Data.FirebirdClient.FbException fex)
{
db.Rollback();
util.WriteLog(string.Format("{0} ADD ERR : {1}", this.GetType(), fex.Message));
return;
}
}
INIT();
}
private void UpdateLaundaryStates(RoutedEventArgs e, string states)
{
// 프런트 > 세탁
// states 1:요청, 0:완료
MenuItem menuItem = (MenuItem)e.Source;
ContextMenu contextMenu = (ContextMenu)((MenuItem)menuItem.Parent).Parent;
selectedListBox = (ListBox)contextMenu.PlacementTarget;
List<RoomItem> _successRoom = new List<RoomItem>();
string _failRoom = string.Empty;
foreach (RoomItem item in selectedListBox.SelectedItems)
{
StringBuilder sb = new StringBuilder();
sb.Append("update hroom set");
sb.AppendFormat(" states_laundary={0}", states);
sb.AppendFormat(" where room='{0}'", item.RoomNum);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
item.States_Laundary = states;
string _msg = string.Empty;
if (states == "1")
{
_msg = "REQUESTED";
}
else if (states == "0")
{
_msg = "COMPLETED";
}
util.Log2DB(item.RoomNum, string.Format("LAUNDARY {0} : {1}", _msg, item.RoomNum), "1");
}
catch
{
db.Rollback();
}
}
}
}
public Calltimes(string _chk, string _sdate, string _edate, string _teller)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@I_CHK", _chk);
dt.Rows.Add("@I_SDATE", _sdate);
dt.Rows.Add("@I_EDATE", _edate);
dt.Rows.Add("@I_TELLER", _teller);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt = db.GetDataTableSP("GET_LIST_BY_HOURS_BAK3", dt);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
List<Calltime> _oblist = new List<Calltime>(
(from _row in dt.AsEnumerable()
select new Calltime()
{
total = int.Parse(_row["O_TOTAL"].ToString())
,
txt0 = _row["O_TXT0"].ToString()
,
calledsec = int.Parse(_row["O_CALLED"].ToString())
,
txt1 = _row["O_TXT1"].ToString()
,
avgcalledsec = int.Parse(_row["O_AVGCALLED"].ToString())
}).ToList<Calltime>()
);
int _counterm = 8;
int _avgcount = _oblist.Count() / _counterm;
_list = new ObservableCollection<CalltimePivot>();
for (int i = 0; i < _oblist.Count(); i += _counterm)
{
int k = 0;
CalltimePivot _callpivot = new CalltimePivot();
for (int j = i; j < i+_counterm; j++)
{
_callpivot.timerange = _oblist[j].txt1;
_callpivot.totalnum = _oblist[j].total;
_callpivot.colavg = _oblist[j].avgcalledsec;
switch (k)
{
case 0:
_callpivot.col30 = _oblist[j].calledsec;
break;
case 1:
_callpivot.col60 = _oblist[j].calledsec;
break;
case 2:
_callpivot.col180 = _oblist[j].calledsec;
break;
case 3:
_callpivot.col300 = _oblist[j].calledsec;
break;
case 4:
_callpivot.col600 = _oblist[j].calledsec;
break;
case 5:
_callpivot.col1800 = _oblist[j].calledsec;
break;
case 6:
_callpivot.col3600 = _oblist[j].calledsec;
break;
case 7:
_callpivot.colall = _oblist[j].calledsec;
break;
}
k++;
}
_list.Add(_callpivot);
}
}
private void UpdateParcelStates(RoutedEventArgs e, ParcelStates states)
{
// 프런트 > 우편물
// states 1:요청, 0:완료
// 우편물 수령 요청 (PMS > PBX > 객실전화)
MenuItem menuItem = (MenuItem)e.Source;
ContextMenu contextMenu = (ContextMenu)((MenuItem)menuItem.Parent).Parent;
selectedListBox = (ListBox)contextMenu.PlacementTarget;
List<RoomItem> _successRoom = new List<RoomItem>();
string _failRoom = string.Empty;
foreach (RoomItem item in selectedListBox.SelectedItems)
{
bool result = false;
using (HotelHelper hh = new HotelHelper(util.PBXIP))
{
result = hh.SetParcel(item.RoomNum, (int)states);
}
if (result)
{
StringBuilder sb = new StringBuilder();
sb.Append("update hroom set");
sb.AppendFormat(" states_parcel={0}", (int)states);
sb.AppendFormat(" where room='{0}'", item.RoomNum);
using (FirebirdDBHelper db = new FirebirdDBHelper(sb.ToString(), util.strDBConn))
{
try
{
db.BeginTran();
int count = db.GetEffectedCount();
db.Commit();
item.States_Parcel = ((int)states).ToString();
_successRoom.Add(item);
}
catch
{
db.Rollback();
}
}
string _msg = string.Empty;
if (states == ParcelStates.GETREQUESTED)
{
_msg = "REQUESTED";
}
else if (states == ParcelStates.NONE)
{
_msg = "DELIVERED";
}
util.Log2DB(item.RoomNum, string.Format("PARCEL {0} : {1}", _msg, item.RoomNum), "1");
}
else
{
if (string.IsNullOrEmpty(_failRoom))
_failRoom = item.RoomNum;
else
_failRoom += string.Format(",{0}", item.RoomNum);
}
}
}
public Calltimes(string _chk, string _sdate, string _edate, string _teller)
{
DataTable dt_worktime = null;
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt_worktime = db.GetDataTableSP("GET_LIST_WORKTIME");
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
DataTable dt_cdrs = util.CreateDT2SP();
dt_cdrs.Rows.Add("@I_SDATE", _sdate);
dt_cdrs.Rows.Add("@I_EDATE", _edate);
dt_cdrs.Rows.Add("@I_TELLER", _teller);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
dt_cdrs = db.GetDataTableSP("GET_CDR_LIST_BY_DATE", dt_cdrs);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
//throw fe;
}
}
var _cdrs = dt_cdrs.AsEnumerable().ToList();
#if true // foreach 사용
_list = new List<CalltimePivot>();
foreach (var wtime in dt_worktime.AsEnumerable())
{
var _lcdrs = _cdrs.Where(p => ((DateTime)p[3]).Hour == int.Parse(wtime[0].ToString()));
//((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString())
CalltimePivot _pivot = new CalltimePivot() {
timerange = wtime[1].ToString()
,
totalnum = _lcdrs.Count(x => ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0)
,
col30 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 0 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 30)
,
col60 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 30 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 60)
,
col180 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 60 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 180)
,
col300 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 180 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 300)
,
col600 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 300 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 600)
,
col1800 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 600 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 1800)
,
col3600 = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 1800 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 3600)
,
colall = _lcdrs.Count(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 3600)
,
colavg = _lcdrs.Count(x => ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0) == 0 ? 0 : (int)_lcdrs.Sum(x => ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds) / _lcdrs.Count(x => ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0)
};
_list.Add(_pivot);
}
#endif
#if false // lamda 사용
_list = new List<CalltimePivot>(
(from wtime in dt_worktime.AsEnumerable()
select new CalltimePivot()
{
timerange = wtime[1].ToString()
,
totalnum = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0)
,
col30 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 0 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 30)
,
col60 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 30 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 60)
,
col180 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 60 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 180)
,
col300 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 180 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 300)
,
col600 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 300 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 600)
,
col1800 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 600 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 1800)
,
col3600 = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 1800 && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds <= 3600)
,
colall = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds > 3600)
,
colavg = _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0) == 0 ? 0 : (int)_cdrs.Where(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString())).Sum(x => (((DateTime)x[4]) - ((DateTime)x[3])).TotalSeconds) / _cdrs.Count(x => ((DateTime)x[3]).Hour == int.Parse(wtime[0].ToString()) && ((DateTime)x[4] - (DateTime)x[3]).TotalSeconds > 0)
}).ToList<CalltimePivot>()
);
#endif
/*
List<CDR> _cdrs = new List<CDR>(
(from cdrRow in dt_cdrs.AsEnumerable()
select new CDR() {
index = string.IsNullOrEmpty(cdrRow["IDX"].ToString()) ? 0 : int.Parse(cdrRow["IDX"].ToString())
,
inner_fseq = string.IsNullOrEmpty(cdrRow["INNER_FSEQ"].ToString()) ? 0 : int.Parse(cdrRow["INNER_FSEQ"].ToString())
,
office_name = cdrRow["OFFICE_NAME"].ToString()
,
startdate = DateTime.Parse(cdrRow["STARTDATE"].ToString())
,
enddate = DateTime.Parse(cdrRow["ENDDATE"].ToString())
,
caller_type = string.IsNullOrEmpty(cdrRow["CALLER_TYPE"].ToString()) ? 0 : int.Parse(cdrRow["CALLER_TYPE"].ToString())
,
caller = cdrRow["CALLER"].ToString()
,
caller_ipn_number = cdrRow["CALLER_IPN_NUMBER"].ToString()
,
caller_group_code = cdrRow["CALLER_GROUP_CODE"].ToString()
,
caller_group_name = cdrRow["CALLER_GROUP_NAME"].ToString()
,
caller_human_name = cdrRow["CALLER_HUMAN_NAME"].ToString()
,
callee = cdrRow["CALLEE"].ToString()
,
callee_type = string.IsNullOrEmpty(cdrRow["CALLEE_TYPE"].ToString()) ? 0 : int.Parse(cdrRow["CALLEE_TYPE"].ToString())
,
callee_ipn_number = cdrRow["CALLEE_IPN_NUMBER"].ToString()
,
callee_group_code = cdrRow["CALLEE_GROUP_CODE"].ToString()
,
callee_group_name = cdrRow["CALLEE_GROUP_NAME"].ToString()
,
callee_human_name = cdrRow["CALLEE_HUMAN_NAME"].ToString()
,
result = string.IsNullOrEmpty(cdrRow["RESULT"].ToString()) ? -1 : int.Parse(cdrRow["RESULT"].ToString())
,
seq = string.IsNullOrEmpty(cdrRow["SEQ"].ToString()) ? 0 : int.Parse(cdrRow["SEQ"].ToString())
}).ToList()
);
_list = new List<CalltimePivot>(
(from wtime in dt_worktime.AsEnumerable()
select new CalltimePivot()
{
timerange = wtime[1].ToString()
,
totalnum = _cdrs.Count(x => x == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 0)
,
col30 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 0 && (x.enddate - x.startdate).TotalSeconds <= 30)
,
col60 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 30 && (x.enddate - x.startdate).TotalSeconds <= 60)
,
col180 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 60 && (x.enddate - x.startdate).TotalSeconds <= 180)
,
col300 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 180 && (x.enddate - x.startdate).TotalSeconds <= 300)
,
col600 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 300 && (x.enddate - x.startdate).TotalSeconds <= 600)
,
col1800 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 600 && (x.enddate - x.startdate).TotalSeconds <= 1800)
,
col3600 = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 1800 && (x.enddate - x.startdate).TotalSeconds <= 3600)
,
colall = _cdrs.Count(x => x.startdate.Hour == int.Parse(wtime[0].ToString()) && (x.enddate - x.startdate).TotalSeconds > 3600)
,
colavg = (int)_cdrs.Where(x => x.startdate.Hour == int.Parse(wtime[0].ToString())).Sum(x => (x.enddate - x.startdate).TotalSeconds)
}).ToList<CalltimePivot>()
);
*/
}
private void btn_checkin_save_Click(object sender, RoutedEventArgs e)
{
List<RoomItem> _seccessRoom = new List<RoomItem>();
string _failRoom = string.Empty;
foreach (RoomItem item in checkinFlyout_lb.ItemsSource)
{
var pmsdata = item.PMSDATA;
pmsdata.cmd = STRUCTS.CGI_PMS_SET_ALL_REQ;
pmsdata.repeat_times = 5;
pmsdata.ring_duration = 120;
pmsdata.try_interval = 3;
bool _result = false;
using (HotelHelper hh = new HotelHelper(util.PBXIP))
{
_result = hh.CheckIn(pmsdata);
}
if (_result)
{
if (NOWACT == PMSBEH.DO_MODIFY)
{
item.PMSDATA = pmsdata;
item.IsChecked = false;
item.Languages = pmsdata.language.ToString();
item.Hour = pmsdata.hour;
item.Minutes = pmsdata.minutes;
item.States = "1";
util.Log2DB(item.RoomNum, string.Format("MODIFY : {0}", item.RoomNum), "2");
continue;
}
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@I_ROOM", item.RoomNum);
dt.Rows.Add("@I_STATUS", "1");
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strDBConn))
{
try
{
db.BeginTran();
db.ExcuteSP("UDT_ROOM_STATUS", dt);
db.Commit();
item.PMSDATA = pmsdata;
item.IsChecked = false;
item.Languages = pmsdata.language.ToString();
item.Hour = pmsdata.hour;
item.Minutes = pmsdata.minutes;
item.States = "1";
_seccessRoom.Add(item);
util.Log2DB(item.RoomNum, string.Format("CHECK IN : {0}", item.RoomNum), "1");
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
}
else
{
item.IsChecked = false;
if (string.IsNullOrEmpty(_failRoom))
_failRoom = item.RoomNum;
else
_failRoom += string.Format(",{0}", item.RoomNum);
}
}
var lbs = this.FindChild<ListBox>("listrooms");
foreach (var itm in _seccessRoom)
{
lbs.SelectedItems.Remove(itm);
}
ToggleFlyout(0);
string alertmsg = string.Empty;
int alertsec = 0;
if (string.IsNullOrEmpty(_failRoom))
{
if (NOWACT == PMSBEH.DO_CHECKIN)
{
alertmsg = "체크인 설정이 완료 되었습니다.";
}
else
{
alertmsg = "설정 수정이 완료 되었습니다.";
}
alertsec = (int)AlertDelaySec.Success;
}
else
{
if (NOWACT == PMSBEH.DO_CHECKIN)
{
alertmsg = string.Format("체크인 실패\r\n방번호 : {0}", _failRoom);
}
else
{
alertmsg = string.Format("실패\r\n방번호 : {0}", _failRoom);
}
alertsec = (int)AlertDelaySec.Success;
}
ShowAlertDialog(alertmsg, alertsec);
if (NOWACT == PMSBEH.DO_CHECKIN)
{
NOWACT = PMSBEH.NONE;
}
else if (NOWACT == PMSBEH.DO_MODIFY)
{
NOWACT = PMSBEH.NONE;
}
}
public void Remove(InnerTel _itel)
{
DataTable dt = util.CreateDT2SP();
dt.Rows.Add("@seq", _itel.Seq);
using (FirebirdDBHelper db = new FirebirdDBHelper(util.strFBDBConn2))
{
try
{
db.BeginTran();
db.ExcuteSP("RMV_INNERTELS", dt);
db.Commit();
InnerTel __obj = _list.FirstOrDefault(x => x.Seq == _itel.Seq);
_list.Remove(__obj);
}
catch (FirebirdSql.Data.FirebirdClient.FbException fe)
{
db.Rollback();
}
}
}
