public EseUser loginChk(AccountLoginModel loginModel)
{
string sqlQuery = "";
sqlQuery += " SELECT esu.SEQNO, esu.EST_CODE, esu.ESE_CODE, esu.EMAIL, esu.USERNAME, esu.GROUP_ID, esu.STATUS as chkSTATUS , est.STATUS ";
sqlQuery += " FROM ese_user esu LEFT OUTER JOIN est_sender est on esu.ESE_CODE = est.ESE_CODE ";
sqlQuery += " WHERE EMAIL = '" + loginModel.Email + "' AND PASSWD = '" + AESEncrypt_256("etomarsPw", loginModel.Password) + "' ";
string resultStr = "";
DataTable dt = GetDataTableMySQL(sqlQuery, out resultStr);
EseUser model = new EseUser();
if (dt != null && dt.Rows.Count != 0)
{
model.SEQNO = int.Parse(dt.Rows[0]["SEQNO"].ToString().Trim());
model.EST_CODE = dt.Rows[0]["EST_CODE"].ToString().Trim();
model.ESE_CODE = dt.Rows[0]["ESE_CODE"].ToString().Trim();
model.EMAIL = dt.Rows[0]["EMAIL"].ToString().Trim();
model.USERNAME = dt.Rows[0]["USERNAME"].ToString().Trim();
model.GROUP_ID = int.Parse(dt.Rows[0]["GROUP_ID"].ToString().Trim());
model.chkSTATUS = int.Parse(dt.Rows[0]["chkSTATUS"].ToString().Trim());
model.STATUS = int.Parse(dt.Rows[0]["STATUS"].ToString().Trim());
}
else
{
model = null;
}
return(model);
}
public EseUser GetEseManagerView(EseManagerModels getModel)
{
string errorStr = "";
EseUser model = new EseUser();
string listQuery = " SELECT SEQNO, EMAIL, PASSWD, USERNAME, TELNO, GROUP_ID, CREATETIME, DEPARTMENT, POSITION, MEMO, STATUS FROM ese_user WHERE SEQNO = " + getModel.act_key;
DataTable listDt = getQueryResult(listQuery, out errorStr);
if (listDt != null && listDt.Rows.Count != 0)
{
model.SEQNO = int.Parse(listDt.Rows[0]["SEQNO"].ToString().Trim());
model.EMAIL = listDt.Rows[0]["EMAIL"].ToString().Trim();
//model.PASSWD = listDt.Rows[0]["PASSWD"].ToString().Trim();
model.USERNAME = listDt.Rows[0]["USERNAME"].ToString().Trim();
model.TELNO = listDt.Rows[0]["TELNO"].ToString().Trim();
model.GROUP_ID = int.Parse(listDt.Rows[0]["GROUP_ID"].ToString().Trim());
model.CREATETIME = listDt.Rows[0]["CREATETIME"].ToString().Trim();
model.DEPARTMENT = listDt.Rows[0]["DEPARTMENT"].ToString().Trim();
model.POSITION = listDt.Rows[0]["POSITION"].ToString().Trim();
model.MEMO = listDt.Rows[0]["MEMO"].ToString().Trim();
model.STATUS = int.Parse(listDt.Rows[0]["STATUS"].ToString().Trim());
}
return(model);
}
//ESE SENDER ESE 정보 관리 -> ESE 기본 정보
public EseUser GetEstIframeInfo(EseIframeInfoModels getModel, string eseCode)
{
string errorStr = "";
EseUser model = new EseUser();
string listQuery = " SELECT SEQNO , " + string.Join(",", selectColumn_EseInfo) + " FROM ese_user WHERE ESE_CODE = '" + eseCode + "' ";
DataTable listDt = getQueryResult(listQuery, out errorStr);
if (listDt != null && listDt.Rows.Count != 0)
{
model.SEQNO = int.Parse(listDt.Rows[0]["SEQNO"].ToString().Trim());
model.EST_CODE = listDt.Rows[0]["EST_CODE"].ToString().Trim();
model.ESE_CODE = listDt.Rows[0]["ESE_CODE"].ToString().Trim();
model.EMAIL = listDt.Rows[0]["EMAIL"].ToString().Trim();
model.PASSWD = listDt.Rows[0]["PASSWD"].ToString().Trim();
model.USERNAME = listDt.Rows[0]["USERNAME"].ToString().Trim();
model.TELNO = listDt.Rows[0]["TELNO"].ToString().Trim();
model.GROUP_ID = int.Parse(listDt.Rows[0]["GROUP_ID"].ToString().Trim());
model.CREATETIME = listDt.Rows[0]["CREATETIME"].ToString().Trim();
model.DEPARTMENT = listDt.Rows[0]["DEPARTMENT"].ToString().Trim();
model.POSITION = listDt.Rows[0]["POSITION"].ToString().Trim();
model.MEMO = listDt.Rows[0]["MEMO"].ToString().Trim();
model.STATUS = int.Parse(listDt.Rows[0]["STATUS"].ToString().Trim());
}
return(model);
}
//WEB_ESE 계정관리 => 계정 관리
public EseManagerModels GetEseManagerList(EseManagerModels model)
{
HttpContext context = HttpContext.Current;
string ESE_CODE = context.Session["ESE_CODE"].ToString();
string errorStr = "";
string listQuery = "";
listQuery = " SELECT SEQNO, EMAIL, PASSWD, USERNAME, TELNO, eu.GROUP_ID,IFNULL(eg.GROUP_NAME, 'MASTER') as GROUP_NAME,CREATETIME,DEPARTMENT,POSITION,MEMO,STATUS ";
listQuery += " FROM ese_user eu left outer join ese_group eg on eu.GROUP_ID = eg.GROUP_ID WHERE eu.ESE_CODE = '" + ESE_CODE + "' ORDER BY SEQNO";
DataTable listDt = getQueryResult(listQuery, out errorStr);
if (listDt != null && listDt.Rows.Count != 0)
{
for (int i = 0; i < listDt.Rows.Count; i++)
{
EseUser temp = new EseUser();
temp.SEQNO = int.Parse(listDt.Rows[i]["SEQNO"].ToString().Trim());
temp.EMAIL = listDt.Rows[i]["EMAIL"].ToString().Trim();
temp.PASSWD = listDt.Rows[i]["PASSWD"].ToString().Trim();
temp.USERNAME = listDt.Rows[i]["USERNAME"].ToString().Trim();
temp.TELNO = listDt.Rows[i]["TELNO"].ToString().Trim();
temp.GROUP_ID = int.Parse(listDt.Rows[i]["GROUP_ID"].ToString().Trim());
temp.GROUP_NAME = listDt.Rows[i]["GROUP_NAME"].ToString().Trim();
temp.CREATETIME = listDt.Rows[i]["CREATETIME"].ToString().Trim();
temp.DEPARTMENT = listDt.Rows[i]["DEPARTMENT"].ToString().Trim();
temp.POSITION = listDt.Rows[i]["POSITION"].ToString().Trim();
temp.MEMO = listDt.Rows[i]["MEMO"].ToString().Trim();
temp.STATUS = int.Parse(listDt.Rows[i]["STATUS"].ToString().Trim());
temp.STATUS_TEXT = "미사용";
if (temp.STATUS == 0)
{
temp.STATUS_TEXT = "사용";
}
model.Items.Add(temp);
model.chkCnt = i;
}
}
//계정 등급이 하나도 등록 되어 있지 않은 경우를 체크를 위해 계정 등급의 카운트를 가져옴
listQuery = " SELECT count(*) as cnt FROM ese_group WHERE ESE_CODE = '" + ESE_CODE + "' ";
model.chkGRADE = getQueryCnt(listQuery, out errorStr);
return(model);
}
//ESE SENDER ESE 정보 관리 -> 계정 관리
public EseUserModels GetEseUserList(EseUserModels model, string eseCode)
{
string errorStr = "";
string listQuery = " SELECT SEQNO, EMAIL,PASSWD,USERNAME,TELNO,eu.GROUP_ID,eg.GROUP_NAME,CREATETIME,DEPARTMENT,POSITION,MEMO,STATUS FROM ese_user eu left outer join ese_group eg on eu.GROUP_ID = eg.GROUP_ID ORDER BY SEQNO ";
DataTable listDt = getQueryResult(listQuery, out errorStr);
if (listDt != null && listDt.Rows.Count != 0)
{
for (int i = 0; i < listDt.Rows.Count; i++)
{
EseUser temp = new EseUser();
temp.SEQNO = int.Parse(listDt.Rows[i]["SEQNO"].ToString().Trim());
//temp.EST_CODE = listDt.Rows[i]["EST_CODE"].ToString().Trim();
//temp.ESE_CODE = listDt.Rows[i]["ESE_CODE"].ToString().Trim();
temp.EMAIL = listDt.Rows[i]["EMAIL"].ToString().Trim();
temp.PASSWD = listDt.Rows[i]["PASSWD"].ToString().Trim();
temp.USERNAME = listDt.Rows[i]["USERNAME"].ToString().Trim();
temp.TELNO = listDt.Rows[i]["TELNO"].ToString().Trim();
temp.GROUP_ID = int.Parse(listDt.Rows[i]["GROUP_ID"].ToString().Trim());
temp.GROUP_NAME = listDt.Rows[i]["GROUP_NAME"].ToString().Trim();
temp.CREATETIME = listDt.Rows[i]["CREATETIME"].ToString().Trim();
temp.DEPARTMENT = listDt.Rows[i]["DEPARTMENT"].ToString().Trim();
temp.POSITION = listDt.Rows[i]["POSITION"].ToString().Trim();
temp.MEMO = listDt.Rows[i]["MEMO"].ToString().Trim();
temp.STATUS = int.Parse(listDt.Rows[i]["STATUS"].ToString().Trim());
model.Items.Add(temp);
}
}
return(model);
}
//회원가입 setRegister
public bool setRegister(AccountRegistrationModel model)
{
string errorStr = "";
EseUser setModel = new EseUser();
GlobalFunction gblFunction = new GlobalFunction();
//================================================================================
//ESE코드 생성
string sqlQuery = " SELECT IFNULL(MAX(ESE_CODE),0) as ESE_CODE FROM ese_user ";
DataTable dt = GetDataTableMySQL(sqlQuery, out errorStr);
int esecode = 0;
if (dt != null && dt.Rows.Count != 0)
{
esecode = int.Parse(dt.Rows[0]["ESE_CODE"].ToString().Trim());
}
string ESE_CODE = string.Format("{0:D8}", esecode + 1);
//================================================================================
//================================================================================
//ESE정보 등록
List <string> queryList = new List <string>();
string exeQueryStr = "";
/*
* // 1. ESE 계정 권한 등록
* string exeQueryStr = "INSERT INTO ese_group (GROUP_NAME, ESE_CODE) values (";
* exeQueryStr += " '최고관리자' ";
* exeQueryStr += " ,'" + ESE_CODE + "' ";
* exeQueryStr += " ) ";
*
* queryList.Add(exeQueryStr);
*
*
* // 2. ESE 계정 개별 권한 등록
* CommFunction com = new CommFunction();
* List<schTypeArray> tmpGrade = com.GetGradeList();
* foreach (schTypeArray temp in tmpGrade)
* {
* exeQueryStr = " INSERT INTO ese_group_permission ( GROUP_ID , ESE_CODE, MENU_ID, PER_SELECT, PER_INSERT, PER_UPDATE, PER_DELETE )VALUES( ";
* exeQueryStr += " ( SELECT MAX(GROUP_ID) FROM ese_group ) ";// + model.Item.GROUP_ID;
* exeQueryStr += " , '" + ESE_CODE + "'";
* exeQueryStr += " , '" + temp.opt_key + "'";
* exeQueryStr += " , 1";
* exeQueryStr += " , 1";
* exeQueryStr += " , 1";
* exeQueryStr += " , 1";
* exeQueryStr += " ) ";
*
* queryList.Add(exeQueryStr);
* }
*/
// 3. ESE 계정 등록
exeQueryStr = "INSERT INTO ese_user (EST_CODE, ESE_CODE, EMAIL, PASSWD, GROUP_ID, STATUS) values (";
if (string.IsNullOrEmpty(model.EST_CODE))
{
exeQueryStr += " ''";
}
else
{
exeQueryStr += " (SELECT IFNULL(EST_CODE, '') FROM esm_station WHERE USERINPUTCODE = '" + model.EST_CODE + "') ";
}
exeQueryStr += " ,'" + ESE_CODE + "' ";
exeQueryStr += " ,'" + model.Email + "' ";
exeQueryStr += " ,'" + AESEncrypt_256("etomarsPw", model.Password) + "' ";
exeQueryStr += " , 0";
exeQueryStr += " , 0 ";
exeQueryStr += " ) ";
queryList.Add(exeQueryStr);
// 4. ESE 기본정보 등록
if (string.IsNullOrEmpty(model.EST_CODE))
{
queryList.Add(" INSERT INTO est_sender (EST_CODE, ESE_CODE,STATUS) VALUES ( '" + model.EST_CODE + "', '" + ESE_CODE + "', 2)");
}
else
{
queryList.Add(" INSERT INTO est_sender (EST_CODE, ESE_CODE,STATUS) VALUES ( (SELECT IFNULL(EST_CODE, '') FROM esm_station WHERE USERINPUTCODE = '" + model.EST_CODE + "'), '" + ESE_CODE + "', 2)");
}
// 5. ESE 계좌정보 등록
string[] EseActOPT_KEY =
{
"setting_SwiftCode",
"setting_BankAddr",
"setting_AccountNum",
"setting_ReceiverName_en",
"setting_Memo"
};
for (int i = 0; i < EseActOPT_KEY.Length; i++)
{
queryList.Add("INSERT INTO ese_settings (SET_KEY, SET_VALUE, ESE_CODE) VALUES ('" + EseActOPT_KEY[i] + "', '', '" + ESE_CODE + "') ");
}
return(exeQuery(queryList, out errorStr));
}
public async Task <ActionResult> Login(AccountLoginModel viewModel)
{
if (!ModelState.IsValid)
{
return(View(viewModel));
}
string resultStr = "";
EseUser model = new EseUser();
model = act.loginChk(viewModel); //로그인 체크
if (model == null) //아이디 페스워드 체크
{
resultStr = "이메일 또는 비밀번호를 확인해 주시기 바랍니다.";
ViewBag.PublicMsg = resultStr;
return(View(viewModel));
}
if (model.chkSTATUS == 1) //사용 여부 체크
{
resultStr = "사용이 정지된 계정입니다.";
ViewBag.PublicMsg = resultStr;
return(View(viewModel));
}
if (model.STATUS == 1) //사용 여부 체크
{
resultStr = "사용이 정지된 ESE 계정입니다.";
ViewBag.PublicMsg = resultStr;
return(View(viewModel));
}
if (model.STATUS == 2) //사용 여부 체크
{
//resultStr = "[문구 수정 필요]승인되지 않은 사용자 입니다. 기본정보를 입력해주시면 빠른시일에 승인 처리 해드리겠습니다.";
//ViewBag.PublicMsg = resultStr;
}
FormsAuthentication.SetAuthCookie(viewModel.Email, false);
Session["MANAGE_NO"] = model.SEQNO;
Session["MANAGE_GRADE"] = model.GROUP_ID;
Session["CURRENT_LOGIN_EMAIL"] = model.EMAIL;
Session["EST_CODE"] = model.EST_CODE;
Session["ESE_CODE"] = model.ESE_CODE;
Session["STATUS"] = model.STATUS;
//로그인 유지 체크 시 쿠키 설정
if (viewModel.RememberMe)
{
Response.Cookies["CHK_LOGIN_REMEMBER"].Value = "CHK_LOGIN_REMEMBER";
Response.Cookies["MANAGE_NO"].Value = model.SEQNO.ToString();
Response.Cookies["MANAGE_GRADE"].Value = model.GROUP_ID.ToString();
Response.Cookies["CURRENT_LOGIN_EMAIL"].Value = model.EMAIL;
Response.Cookies["EST_CODE"].Value = model.EST_CODE;
Response.Cookies["ESE_CODE"].Value = model.ESE_CODE;
Response.Cookies["STATUS"].Value = model.STATUS.ToString();
}
else
{
//로그인 유지 체크 헤제 시 쿠키 삭제
if (Request.Cookies["CHK_LOGIN_REMEMBER"] != null)
{
Response.Cookies["CHK_LOGIN_REMEMBER"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["MANAGE_NO"] != null)
{
Response.Cookies["MANAGE_NO"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["MANAGE_GRADE"] != null)
{
Response.Cookies["MANAGE_GRADE"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["CURRENT_LOGIN_EMAIL"] != null)
{
Response.Cookies["CURRENT_LOGIN_EMAIL"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["EST_CODE"] != null)
{
Response.Cookies["EST_CODE"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["ESE_CODE"] != null)
{
Response.Cookies["ESE_CODE"].Expires = DateTime.Now.AddDays(-1);
}
if (Request.Cookies["STATUS"] != null)
{
Response.Cookies["STATUS"].Expires = DateTime.Now.AddDays(-1);
}
}
//로그인 기록 데이터 세팅
CommLoginLog clh = new CommLoginLog();
clh.ESE_CODE = model.ESE_CODE;
clh.EMAIL = viewModel.Email;
clh.IPADDR = Request.UserHostAddress;
act.loginHis(clh); //로그인 로그 기록
return(RedirectToLocal(viewModel.ReturnUrl));
//return RedirectToAction("Index", "Home");
}
public EseInfoModels GetEseInfoList(EseInfoModels model)
{
string errorStr = "";
string listQuery = " SELECT SEQNO , " + string.Join(",", selectColumn_EseInfo);
string cntQuery = " SELECT count(*) as cnt ";
string baseQuery = " FROM ese_user WHERE 1=1 ";
if (!String.IsNullOrEmpty(model.schType)) //국가
{
baseQuery += " AND NATION_CODE = " + model.schType.Trim();
}
if (!String.IsNullOrEmpty(model.station)) //STATION
{
baseQuery += " AND EST_CODE = " + model.station.Trim();
}
if (!String.IsNullOrEmpty(model.station)) //SENDER
{
baseQuery += " AND ESE_CODE = " + model.station.Trim();
}
if (!String.IsNullOrEmpty(model.schSdt)) //생성날짜 (시작일)
{
baseQuery += " AND CREATETIME >= '" + model.schSdt.Trim() + "'";
}
if (!String.IsNullOrEmpty(model.schEdt)) //생성날짜 (종료일)
{
baseQuery += " AND CREATETIME <= '" + model.schEdt.Trim() + " 23:59:59'";
}
if (!String.IsNullOrEmpty(model.schTypeTxt) && !String.IsNullOrEmpty(model.schTxt)) //검색조건 검색어
{
baseQuery += " AND " + model.schTypeTxt.Trim() + " like '%" + model.schTxt.Trim() + "%' ";
}
string endQuery = " ORDER BY " + model.sortKey.ToString().Trim() + " DESC limit " + ((model.Paging.page - 1) * model.Paging.pageNum) + " , " + model.Paging.pageNum; //정렬
cntQuery += baseQuery; //토탈 카운트 쿼리
listQuery += baseQuery + endQuery; //리스트 쿼리
int totCnt = getQueryCnt(cntQuery, out errorStr); //전체 리스트 갯수 구하기
model.Paging.pageTotNum = (totCnt / model.Paging.pageNum) + 1; //총 페이징 갯수 구하기
DataTable listDt = getQueryResult(listQuery, out errorStr);
if (listDt != null && listDt.Rows.Count != 0)
{
for (int i = 0; i < listDt.Rows.Count; i++)
{
EseUser temp = new EseUser();
temp.SEQNO = int.Parse(listDt.Rows[i]["SEQNO"].ToString().Trim());
temp.EST_CODE = listDt.Rows[i]["EST_CODE"].ToString().Trim();
temp.ESE_CODE = listDt.Rows[i]["ESE_CODE"].ToString().Trim();
temp.EMAIL = listDt.Rows[i]["EMAIL"].ToString().Trim();
temp.PASSWD = listDt.Rows[i]["PASSWD"].ToString().Trim();
temp.USERNAME = listDt.Rows[i]["USERNAME"].ToString().Trim();
temp.TELNO = listDt.Rows[i]["TELNO"].ToString().Trim();
temp.GROUP_ID = int.Parse(listDt.Rows[i]["GROUP_ID"].ToString().Trim());
temp.CREATETIME = listDt.Rows[i]["CREATETIME"].ToString().Trim();
temp.DEPARTMENT = listDt.Rows[i]["DEPARTMENT"].ToString().Trim();
temp.POSITION = listDt.Rows[i]["POSITION"].ToString().Trim();
temp.MEMO = listDt.Rows[i]["MEMO"].ToString().Trim();
temp.STATUS = int.Parse(listDt.Rows[i]["STATUS"].ToString().Trim());
//Comnation 에서 국가 목록을 다 긁어와서 넣어줘야 한다.
model.Items.Add(temp);
}
}
return(model);
}
