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