public ActionResult GetOutsourceUserList() { string sql = string.Empty; DataTable data = new DataTable(); DataItemDetailBLL pdata = new DataItemDetailBLL(); var list = pdata.GetDataItemListByItemCode("KbshomeCount"); if (list != null) { var item = list.Where(a => a.EnabledMark == 1).FirstOrDefault(); if (item.ItemValue == "EquipmentManage") { //关联设备进出记录 sql = string.Format(@"select d.deptname,count(1) as num from bis_hikinoutlog d join base_department t on d.deptid=t.departmentid and t.nature='承包商' and d.isout=0 group by d.deptid,d.deptname"); } else if (item.ItemValue == "LableManage") {//关联标签 sql = string.Format(@"select d.deptname,count(1) as num from bis_lableonlinelog d join base_department t on d.deptid=t.departmentid and t.nature='承包商' and d.isout=0 group by d.deptid,d.deptname"); } } if (!string.IsNullOrEmpty(sql)) { data = Opertickebll.GetDataTable(sql); } return(Content(data.ToJson())); }
// GET: HJBPerson/PersonList /// <summary> /// 人员进出视图 /// </summary> /// <returns></returns> public ActionResult Index() { string sql = string.Format(@"select count(distinct(a.deptname)) as num from bis_hikinoutlog a left join V_USERINFO b on a.deptid = b.departmentid where nature = '承包商' and length(encode) <= 23 and b.ISEPIBOLY = '是' and a.INOUT = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE+0 > a.CREATEDATE+0)"); DataTable dt = operticketmanagerbll.GetDataTable(sql); ViewBag.outsourcing = dt.Rows[0]["num"].ToString(); string sql2 = string.Format(@"select count(distinct(a.userid)) as num from bis_hikinoutlog a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE+0 > a.CREATEDATE+0)"); DataTable dt2 = operticketmanagerbll.GetDataTable(sql2); ViewBag.allPerson = dt2.Rows[0]["num"].ToString(); string sql3 = string.Format(@"select count(distinct(a.userid)) as num from bis_hikinoutlog a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE+0 > a.CREATEDATE+0) and b.ISEPIBOLY = '否' and b.DEPTTYPE is null"); DataTable dt3 = operticketmanagerbll.GetDataTable(sql3); ViewBag.inPerson = dt3.Rows[0]["num"].ToString(); string sql4 = string.Format(@"select count(distinct(a.userid)) as num from bis_hikinoutlog a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE+0 > a.CREATEDATE+0) and b.DEPTTYPE is not null"); DataTable dt4 = operticketmanagerbll.GetDataTable(sql4); ViewBag.outPerson = dt4.Rows[0]["num"].ToString(); return(View()); }
public ActionResult GetForeignUserNum() { string sql = string.Format("select (select count(1) from bis_persongps s where TO_CHAR(intime,'yyyy-MM-dd') >= '{0}' ) as SumNum,(select count(1) from bis_persongps s1 where s1.state='0' and TO_CHAR(intime,'yyyy-MM-dd') >= '{0}' ) as Num from bis_persongps d where rownum<2", DateTime.Now.ToString("yyyy-MM-dd")); var data = Opertickebll.GetDataTable(sql); List <string> list = new List <string>(); string num = "0"; string num1 = "0"; if (data.Rows.Count > 0) { num = data.Rows[0][0].ToString(); num1 = data.Rows[0][1].ToString(); } list.Add(num); list.Add(num1); return(Content(list.ToJson())); }
public ActionResult GetWorkWarningGroupJson(string type) { DateTime stime = LinqHelper.Getmondydate(DateTime.Now); DateTime etime = LinqHelper.GetSundayDate(DateTime.Now); string res = string.Empty; if (type == "本周") { res = " and TO_CHAR(createdate,'yyyy-MM-dd')>='" + stime.ToString("yyyy-MM-dd") + "' and TO_CHAR(createdate,'yyyy-MM-dd')<='" + etime.ToString("yyyy-MM-dd") + "'"; } else { res = " and TO_CHAR(createdate,'yyyy-MM')='" + DateTime.Now.ToString("yyyy-MM") + "' "; } string sql = string.Format("select d.deptname,count(1) as num from bis_earlywarning d where d.type=0 {0} group by d.deptname,d.deptcode", res); var dt = Opertickebll.GetDataTable(sql); return(Content(dt.ToJson())); }
/// <summary> /// 打印视图 /// </summary> /// <returns></returns> public ActionResult Stamp(string keyValue) { string sql = string.Format("select sum(netwneight) from wl_calculate where isdelete='1' and baseid='{0}'", keyValue); DataTable dt = operticketmanagerbll.GetDataTable(sql); if (dt.Rows.Count > 0) {//净重 ViewBag.weight = dt.Rows[0][0].ToString(); } return(View()); }
/// <summary> /// 车辆出厂时对应称重记录不能再修改 /// </summary> /// <param name="keyValue"></param> public void UpdateCalculate(string keyValue) { string sql = string.Format("select id from wl_calculate d where d.baseid='{0}' and d.isdelete='1'", keyValue); DataTable dt = operticketmanagerbll.GetDataTable(sql); if (dt.Rows.Count > 0) { var data = calculatebll.GetEntity(dt.Rows[0][0].ToString()); if (data != null) { data.IsOut = 1; calculatebll.SaveForm(data.ID, data); } } }
public ActionResult GetCarUserList(string userids, string type = "0") { string[] users = userids.Split(','); string userid = ""; for (int i = 0; i < users.Length; i++) { if (i == 0) { userid = "'" + users[i] + "'"; } else { userid += ",'" + users[i] + "'"; } #region 临时人员判断 if (type == "1") { var tempentity = new TemporaryGroupsBLL().GetEmptyUserEntity(users[i]); if (tempentity == null) { var Us = userBLL.GetEntity(users[i]); if (Us != null) { List <TemporaryUserEntity> list = new List <TemporaryUserEntity>(); //如果不存在于临时列表则新增一条数据 TemporaryUserEntity inserttuser = new TemporaryUserEntity(); inserttuser.Tel = Us.Account; inserttuser.ComName = ""; inserttuser.CreateDate = Us.CreateDate; inserttuser.CreateUserId = Us.CreateUserId; inserttuser.USERID = Us.UserId; inserttuser.Gender = Us.Gender; inserttuser.ISDebar = 0; inserttuser.Istemporary = 0; inserttuser.Identifyid = Us.IdentifyID; inserttuser.Postname = Us.DutyName; inserttuser.UserName = Us.RealName; inserttuser.Groupsid = Us.DepartmentId; inserttuser.startTime = Us.CreateDate; var dept1 = departmentBLL.GetEntity(Us.DepartmentId); if (dept1 != null) { inserttuser.GroupsName = dept1.FullName; } list.Add(inserttuser); new TemporaryGroupsBLL().SaveTemporaryList("", list); } } } #endregion } string sql = string.Empty; if (type == "1") { sql = string.Format(@" select d.userid as id, realname as username from v_userinfo d where d.userid in ({0})", userid); } else { sql = string.Format(@" select d.id,d.userimg,d.username,d.baseid,d.imgdata from bis_usercarfileimg d where d.id in ({0})", userid); } var data = Opertickebll.GetDataTable(sql); return(ToJsonResult(data)); }
/// <summary> /// 获取人员进出门禁记录表 /// </summary> /// <param name="pagination"></param> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult GetTable(Pagination pagination, string queryJson) { var watch = CommonHelper.TimerStart(); pagination.p_kid = "v_info.userid"; pagination.p_fields = ""; pagination.p_tablename = "v_userinfo v_info left join base_department d on v_info.departmentid = d.departmentid left join (select * from HJB_PERSONSET where MODULETYPE = 1) t on v_info.userid = t.userid"; pagination.conditionJson = "Account!='System'"; var data = hikinoutlogbll.GetTableUserRole(pagination, queryJson); var queryParam = queryJson.ToJObject(); foreach (DataRow dr in data.Rows) { if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组") { //DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from base_department where encode=(select encode from base_department t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"])); //if (dt.Rows.Count > 0) //{ // string name = ""; // foreach (DataRow dr1 in dt.Rows) // { // name += dr1["fullname"].ToString() + "/"; // } // dr["dutyname"] = name.TrimEnd('/'); //} dr["dutyname"] = dr["dutyname"] + "(" + dr["DEPTNAME"] + ")"; } if (dr["userid"] != null) { string sTime = queryParam["sTime"].ToString(); string eTime = queryParam["eTime"].ToString(); string userId = dr["userid"].ToString(); string sql = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd') and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20)) else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId); DataTable dt = operticketmanagerbll.GetDataTable(sql); if (dt.Rows.Count > 0) { dr["bminnum"] = dt.Rows[0]["num"].ToString(); } string sql2 = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 1 and a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd') and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20)) else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId); DataTable dt2 = operticketmanagerbll.GetDataTable(sql2); if (dt2.Rows.Count > 0) { dr["bmoutnum"] = dt2.Rows[0]["num"].ToString(); } } } var JsonData = new { rows = data, total = pagination.total, page = pagination.page, records = pagination.records, costtime = CommonHelper.TimerEnd(watch) }; return(Content(JsonData.ToJson())); }
public object GetUserInformationList(string json) { try { //string res = //json.Value<string>("json");//[FromBody]JObject dynamic dy = JsonConvert.DeserializeObject <ExpandoObject>(json); string userId = dy.userid; string username = dy.data.username; //姓名 string jobnumber = dy.data.jobnumber; //手机号 //获取用户基本信息 OperatorProvider.AppUserId = userId; //设置当前用户 Operator curUser = OperatorProvider.Provider.Current(); if (null == curUser) { curUser = GetOperator(dy.userid); if (curUser == null) { return(new { code = -1, count = 0, info = "当前用户不存在,请核对用户信息!" }); } } Pagination pagination = new Pagination(); pagination.p_kid = "id"; pagination.p_fields = @" d.username,d.vid,d.gpsid,d.gpsname,d.intime,d.outtime,d.state,'' as phone,'' as comname "; pagination.p_tablename = "bis_persongps d"; pagination.conditionJson = " state=0 "; //pagination.sidx = pagination.sidx + " " + pagination.sord + ",id"; if (!string.IsNullOrEmpty(username)) { //姓名 pagination.conditionJson += " and username like '%" + username.Trim() + "%'"; } if (!string.IsNullOrEmpty(dy.data.starttime)) {// pagination.conditionJson += " and username like '" + jobnumber.Trim() + "%'"; } //获取页数和条数 int page = Convert.ToInt32(dy.data.pagenum), rows = Convert.ToInt32(dy.data.pagesize); pagination.page = page; //页数 pagination.rows = rows; //行数 pagination.sidx = "createdate"; //排序字段 pagination.sord = "desc"; //排序方式 DataTable data = operticketmanagerbll.GetPageList(pagination, null); if (data.Rows.Count > 0) { foreach (DataRow Rows in data.Rows) { string pid = Rows[2].ToString(); string sql = string.Format("select d.dirver,d.phone,CONCAT(CONCAT(d.visitdept,'/'),d.carno) as purpose from bis_usercar d where d.id='{0}' ", pid); string sql1 = string.Format("select d.dirver,d.phone,CONCAT(CONCAT(d.visitdept,'/'),d.carno) as purpose from bis_visitcar d where d.id='{0}' ", pid); string sql2 = string.Format("select Phone,TheCompany,d.dirver from bis_hazardouscar d where d.id='{0}' ", pid); DataTable dt = operticketmanagerbll.GetDataTable(sql); DataTable dt1 = operticketmanagerbll.GetDataTable(sql1); DataTable dt2 = operticketmanagerbll.GetDataTable(sql2); if (dt.Rows.Count > 0) {//拜访(无车) if (Rows[1].ToString() == dt.Rows[0][0].ToString()) { Rows["phone"] = dt.Rows[0][1].ToString(); } Rows["comname"] = dt.Rows[0][2].ToString(); } else if (dt1.Rows.Count > 0) {//拜访(有车) if (Rows[1].ToString() == dt1.Rows[0][0].ToString()) { Rows["phone"] = dt1.Rows[0][1].ToString(); } Rows["comname"] = dt1.Rows[0][2].ToString(); } else if (dt2.Rows.Count > 0) {//拜访(危化品) if (Rows[1].ToString() == dt2.Rows[0][2].ToString()) { Rows["phone"] = dt2.Rows[0][0].ToString(); } Rows["comname"] = dt2.Rows[0][1].ToString(); } dt.Dispose(); dt1.Dispose(); dt2.Dispose(); } } return(new { code = 0, info = "获取数据成功", count = pagination.records, data = data.ToJson() }); } catch (Exception ex) { return(new { code = -1, count = 0, info = ex.Message }); } }