public string Door_CS(string jsonText)
{
//jsonText = "[[{ \"number\": \"132\", \"name\": \"PO\", \"company\": \"TT4\" }]]";
string result = "";
JArray resultArray = (JArray)JsonConvert.DeserializeObject(jsonText);
JArray data = (JArray)resultArray[0];
for (int i = 0; i < data.Count; i++)
{
JObject jodata = (JObject)data[i];
string csNumber = jodata["number"].ToString();
string csName = jodata["name"].ToString();
string csCompany = jodata["company"].ToString();
csCompany = csCompany.Contains("厂商") ? csCompany : ("厂商_" + csCompany);
string sql_InsertCS = string.Format(@"declare @door_result varchar(10)
Exec [Door_InsertCS] '{0}','{1}',@door_result out
select @door_result ", csName, csCompany);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_InsertCS).Tables[0];
string sqlResult = Int32.Parse(dt_check.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
result = "0";
}
else
{
result = "厂商资料写入数据库失败，开单失败";
}
}
return(result);
}
public DataTable GetRechargeInfo(string empid, string startDate, string endDate)
{
//string empid = "0062815";
//对empid验证 goto
string sql_openAuthority = string.Format(@"select empid as 工号,je as 充值金额,sdate as 充值日期,stime as 充值时间 from xf_cz_log where empid='0062815'
and typeid=0 and sdate between '" + startDate + "' and '" + endDate + "'"); //
DataTable dt = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
}
return(dt);
}
public string GetBalance(string empid)
{
string result = "";
string sql_openAuthority = string.Format(@"select a.totalje+a.lastmonthye+a.addje-a.decje-a.useje as btye,
c.totalje-c.useje as czye from xf_bt a
full join
xf_cz c on a.empid=c.empid where a.empid='" + empid + "' and a.strmonth='2019-06'");
DataTable dt = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
if (dt.Rows.Count > 0)
{
string btye = dt.Rows[0]["btye"].ToString();
string czye = dt.Rows[0]["czye"].ToString();
result = btye + "|" + czye;
}
else
{
result = "没有查到相关信息";
}
return(result);
}
public string Login(string empid, string pwd)
{
string result = "";
string sql_openAuthority = string.Format(@"select count(*) from card_user where empid='" + empid + "' and passward='" + pwd + "'"); //
DataTable dt = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
if (dt.Rows[0][0].ToString() != "0")
{
string sql_query = string.Format(@"select empid,empname,deptname from card_user where empid='" + empid + "'"); //
DataTable dt2 = new MarkCoeno().ExecuteQuery(sql_query).Tables[0];
string empid1 = dt2.Rows[0]["empid"].ToString();
string empname = dt2.Rows[0]["empname"].ToString();
string deptname = dt2.Rows[0]["deptname"].ToString();
result = empid1 + "|" + empname + "|" + deptname;
}
else
{
result = "用户名或密码错误";
}
return(result);
}
public string GetEmpInfo(string empid)
{
string result = "";
//string empid = "0062815";
//对empid验证 goto
string sql_openAuthority = string.Format(@"select a.empid,a.empname,b.deptname from hr_emp a
join hr_dept b on a.deptid=b.deptid where a.empid='" + empid + "' and a.staid=0"); //
DataTable dt = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
if (dt.Rows.Count > 0)
{
string empid1 = dt.Rows[0]["empid"].ToString();
string empname = dt.Rows[0]["empname"].ToString();
string deptname = dt.Rows[0]["deptname"].ToString();
result = empid1 + "|" + empname + "|" + deptname;
}
else
{
result = "没有查到相关信息";
}
return(result);
}
public string Door_Authority_New(string jsonText)
{
string sqlResult = ""; //执行存储过程后返回值
int level = 0;
string result = "";
string doorType = ""; // D 部门门禁 F 人脸 S特殊门禁
JArray resultArray = (JArray)JsonConvert.DeserializeObject(jsonText);
//["0",[{ "empid": "0062815", "deptid":"28", "AccessLevelID": "3800", "deviceid":"10"}]]
//["0","",[{"empid":"0062815","doorname":"201-L40-DOOR06"}]]
//["1","权限5-2质量管理部(PQA)",[{"empid":"0062815","doorname":""},{"empid":"0060150","doorname":""}]]
//["0","大门门禁",[{"empid":"0062815","doorname":"门岗-ACS01A-DOOR01"}]]
//["1","3",[{"empid":"0062815","doorname":""}]]
//["1",[{ "empid": "0062815", "deptid":"9", "AccessLevelID": "3250","deviceid":"400" }]]
//["0",[{ "empid": "0062815", "deptid":"9", "AccessLevelID": "3280","deviceid":"300" }]]
string doortype = resultArray[0].ToString(); // 0 特殊 1 部门
JArray data = (JArray)resultArray[1]; //详细表
if (doortype == "1") //特殊门禁
{
for (int i = 0; i < data.Count; i++)
{
JObject jodata = (JObject)data[i];
string deptid = jodata.ContainsKey("deptid") ? jodata["deptid"].ToString() : "";
string deviceid = jodata.ContainsKey("deviceid") ? jodata["deviceid"].ToString() : "";
string levelid = jodata.ContainsKey("AccessLevelID") ? jodata["AccessLevelID"].ToString() : "";
level = Int32.Parse(levelid);
string empid = jodata.ContainsKey("empid") ? jodata["empid"].ToString() : ""; //jodata["empid"].ToString();
doorType = "S"; // special 特殊门禁
string sql_checkAuthority = string.Format(@"declare @door_result varchar(100)
Exec [Door_CheckAuthority_new] '{0}','{1}','{2}','S',@door_result out
select @door_result ", deptid, levelid, deviceid);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_checkAuthority).Tables[0];
sqlResult = dt_check.Rows[0][0].ToString().ToString();
if (sqlResult == "1")
{
string sql_openAuthority = string.Format(@"declare @return_value varchar(2) EXEC Door_OpenAuthority '{0}','{1}','{2}','{3}', @return_value output
SELECT @return_value ", empid, level, doorType, deviceid);
//存储过程逻辑： 根据传入工号获取卡内码 转码 查找该卡内码是否存在级别，没有则添加级别 再加门
//加门逻辑： 拿到级别号 根据特殊门字符串拿到编号 添加
DataTable dt_open = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
sqlResult = Int32.Parse(dt_open.Rows[0][0].ToString()).ToString();
if (sqlResult == "0") //成功
{
result = "0";
}
else //失败
{
result = result + empid + ",";
}
}
else
{
result = sqlResult;//"该门禁级别不在所选部门管辖范围内";
}
}
}
else if (doortype == "0") //部门门禁
{
for (int i = 0; i < data.Count; i++)
{
try
{
JObject jodata = (JObject)data[i];
string deptid = jodata.ContainsKey("deptid") ? jodata["deptid"].ToString() : "";
string levelid = jodata.ContainsKey("AccessLevelID") ? jodata["AccessLevelID"].ToString() : "";
string deviceid = jodata.ContainsKey("deviceid") ? jodata["deviceid"].ToString() : "";
level = Int32.Parse(levelid);
//点击提交按钮后 判断所选级别是不是该部门下
string sql_checkAuthority = string.Format(@"declare @door_result varchar(100)
Exec [Door_CheckAuthority_new] '{0}','{1}','{2}','D',@door_result out
select @door_result ", deptid, levelid, deviceid);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_checkAuthority).Tables[0];
sqlResult = dt_check.Rows[0][0].ToString().ToString();
if (sqlResult == "1")
{
if (level == 3569) //人脸识别-ACF管制口
{
level = 6; //重新赋值 人脸系统的groupid
doorType = "F"; //Face 人脸
}
else if (level == 3794) // 人脸识别-CELL管制口
{
level = 5;
doorType = "F";
}
else if (level == 3804) // 人脸识别-实验室DOOR100
{
level = 11;
doorType = "F";
}
else if (level == 3816) // 人脸识别-实验室DOOR46
{
level = 7;
doorType = "F";
}
else if (level == 3803) // 人脸识别-实验室DOOR53
{
level = 8;
doorType = "F";
}
else if (level == 3802) // 人脸识别-实验室DOOR58
{
level = 9;
doorType = "F";
}
else if (level == 3800) // 人脸识别-实验室DOOR63
{
level = 10;
doorType = "F";
}
else //门禁系统
{
doorType = "D"; //Door 普通门禁
}
string empid = jodata["empid"].ToString();
string sql_openAuthority = string.Format(@"declare @return_value varchar(2) EXEC Door_OpenAuthority '{0}','{1}','{2}','{3}', @return_value output
SELECT @return_value ", empid, level, doorType, "");
DataTable dt_open = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
sqlResult = Int32.Parse(dt_open.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
result = empid + "已存在该权限" + ",";
}
else if (sqlResult == "")
{
}
}
else
{
result = sqlResult;
}
}
catch (Exception ex)
{
result = ex.Message;
}
}
if (result != "")
{
result = result.TrimEnd(',');
}
else
{
result = "0";
}
}
return(result);
}
public string Door_Authority(string jsonText)
{
string sqlResult = ""; //执行存储过程后返回值
int level = 0;
string result = "";
string doorType = ""; // D 部门门禁 F 人脸 S特殊门禁
JArray resultArray = (JArray)JsonConvert.DeserializeObject(jsonText);
//["0","",[{"empid":"0062815","doorname":"201-L40-DOOR06"}]]
//["1","权限5-2质量管理部(PQA)",[{"empid":"0062815","doorname":""},{"empid":"0060150","doorname":""}]]
//["0","大门门禁",[{"empid":"0062815","doorname":"门岗-ACS01A-DOOR01"}]]
//["1","3",[{"empid":"0062815","doorname":""}]]
string doortype = resultArray[0].ToString(); // 0 特殊 1 部门
JArray data = (JArray)resultArray[2]; //详细表
int levelName = (int)resultArray[1]; //获取部门权限级别名称
string path = Server.MapPath("~/Door.xml");
//<1>实例化一个XML文档操作对象.
XmlDocument doc = new XmlDocument();
doc.Load(path);
XmlNodeList topM = doc.SelectNodes("//Authority");
string levelname = "";
foreach (XmlElement element in topM)
{
string id = element.GetElementsByTagName("Code")[0].InnerText;
if (id == levelName.ToString())
{
levelname = element.GetElementsByTagName("Name")[0].InnerText;
break;
}
}
if (doortype == "0") //特殊门禁
{
for (int i = 0; i < data.Count; i++)
{
JObject jodata = (JObject)data[i];
string doorName = jodata["doorname"].ToString();
if (doorName.Contains("101"))
{
doorName = doorName.Replace("101", "FAB");
}
if (doorName.Contains("201"))
{
doorName = doorName.Replace("201", "OFFICE");
}
if (doorName.Contains("电梯"))
{
doorName = "";
}
string insertStr = "%";
string isCS = jodata.ContainsKey("sfwcs") ? jodata["sfwcs"].ToString() : "";
if (isCS == "0" && isCS != "") //为厂商 新增资料到hr_emp
{
string csName = jodata["xm"].ToString();
string sql_InsertCS = string.Format(@"declare @door_result varchar(10)
Exec [Door_InsertCS] '{0}',@door_result out
select @door_result ", csName);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_InsertCS).Tables[0];
sqlResult = Int32.Parse(dt_check.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
result = "0";
}
else
{
result = "厂商资料写入数据库失败，开单失败";
}
return(result);
}
else if (isCS == "1" && isCS != "")
{
// 找到最后一个 -的位置，插入字符
doorName = doorName.Insert(doorName.LastIndexOf('-'), insertStr);
string empid = jodata["empid"].ToString();
doorType = "S"; // special 特殊门禁
//20190603 新增逻辑：如果该门属于所选部门级别 则起单 否则 不往下起单
string sql_checkAuthority = string.Format(@"declare @door_result varchar(10)
Exec [Door_CheckAuthority] '{0}','{1}',@door_result out
select @door_result ", doorName, levelname);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_checkAuthority).Tables[0];
sqlResult = Int32.Parse(dt_check.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
string sql_openAuthority = string.Format(@"declare @return_value varchar(2) EXEC Door_OpenAuthority '{0}','{1}','{2}','{3}', @return_value output
SELECT @return_value ", empid, level, doorType, doorName);
//存储过程逻辑： 根据传入工号获取卡内码 转码 查找该卡内码是否存在级别，没有则添加级别 再加门
//加门逻辑： 拿到级别号 根据特殊门字符串拿到编号 添加
DataTable dt_open = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
sqlResult = Int32.Parse(dt_open.Rows[0][0].ToString()).ToString();
if (sqlResult == "0") //成功
{
result = "0";
}
else //失败
{
result = result + empid + ",";
}
}
else
{
result = "该级别下没有查到此特殊门，开单失败";
}
}
}
}
else if (doortype == "1") //部门门禁
{
// string levelName = jodata["levelname"].ToString();
string sql_getLevel = string.Format(@"declare @return_value int EXEC Door_GetLevel '{0}',@return_value output
SELECT @return_value", levelname);
DataTable dt = new MarkCoeno().ExecuteQuery(sql_getLevel).Tables[0];
level = Int32.Parse(dt.Rows[0][0].ToString()); //门禁级别
if (level == 3569) //人脸识别-ACF管制口
{
level = 6; //重新赋值 人脸系统的groupid
doorType = "F"; //Face 人脸
}
else if (level == 3794) // 人脸识别-CELL管制口
{
level = 5;
doorType = "F";
}
else if (level == 3804) // 人脸识别-实验室DOOR100
{
level = 11;
doorType = "F";
}
else if (level == 3816) // 人脸识别-实验室DOOR46
{
level = 7;
doorType = "F";
}
else if (level == 3803) // 人脸识别-实验室DOOR53
{
level = 8;
doorType = "F";
}
else if (level == 3802) // 人脸识别-实验室DOOR58
{
level = 9;
doorType = "F";
}
else if (level == 3800) // 人脸识别-实验室DOOR63
{
level = 10;
doorType = "F";
}
else //门禁系统
{
doorType = "D"; //Door 普通门禁
}
for (int i = 0; i < data.Count; i++)
{
try
{
JObject jodata = (JObject)data[i];
string isCS = jodata["sfwcs"].ToString();
if (isCS == "0") //为厂商 新增资料到hr_emp
{
string csName = jodata["xm"].ToString();
string sql_InsertCS = string.Format(@"declare @door_result varchar(10)
Exec [Door_InsertCS] '{0}',@door_result out
select @door_result ", csName);
DataTable dt_check = new MarkCoeno().ExecuteQuery(sql_InsertCS).Tables[0];
sqlResult = Int32.Parse(dt_check.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
result = "0";
}
else
{
result = "厂商资料写入数据库失败，开单失败";
}
return(result);
}
else
{
string empid = jodata["empid"].ToString();
string sql_openAuthority = string.Format(@"declare @return_value varchar(2) EXEC Door_OpenAuthority '{0}','{1}','{2}','{3}', @return_value output
SELECT @return_value ", empid, level, doorType, "");
DataTable dt_open = new MarkCoeno().ExecuteQuery(sql_openAuthority).Tables[0];
sqlResult = Int32.Parse(dt_open.Rows[0][0].ToString()).ToString();
if (sqlResult == "1")
{
result = result + empid + ",";
}
else if (sqlResult == "")
{
}
}
}
catch (Exception ex)
{
result = ex.Message;
}
}
if (result != "")
{
result = result.TrimEnd(',');
}
else
{
result = "0";
}
}
return(result);
}