/// <summary> /// update /// </summary> /// <param name="AllInOne_UserInfo"></param> /// <returns></returns> public void Update(AllInOne_UserInfo model, SqlConnection connection = null, SqlTransaction transaction = null) { var str = GetUpdateStr(model); var dict = GetParameters(model); SqlHelper.Instance.ExcuteNon(str, dict, connection, transaction); }
/// <summary> /// 根据设备号获取设备视图 /// </summary> /// <param name="no"></param> /// <param name="user"></param> /// <returns></returns> public DeviceView GetViewByNo(int no, AllInOne_UserInfo user) { //var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var lastCId = ""; if ((bool)user.isStaff) { lastCId = AllInOne_UserInfoOper.Instance.GetFatherLastCId(user); } else { lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); } var dict = new Dictionary <string, string> { { "@no", no.ToString() }, { "@lastcid", lastCId }, }; string str = $"select * from deviceview where meterNo=@no and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; var temp = SqlHelper.Instance.ExecuteGetDt <DeviceView>(str, dict); if (temp.Count == 0) { return new DeviceView { meterNo = 0 } } ; //str = $"select * from deviceInfo where meterNo={no}"; //var r = SqlHelper.Instance.ExecuteGetDt<DeviceView>(str, new Dictionary<string, string>()); return(temp.FirstOrDefault()); }
public int DeleteByCustomerNo(string customerNo, AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var dict = new Dictionary <string, string> { { "@no", customerNo }, { "@lastcid", lastCId }, }; string str2 = $"select * from CustomerView where customerNo=@no and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; var list = SqlHelper.Instance.ExecuteGetDt <CustomerView>(str2, dict); if (list.Count == 0) { return(0); } else { if (list.First().useState == 1) { return(2); } string str = "DELETE from customerInfo where customerNo=" + customerNo; var r = SqlHelper.Instance.ExcuteNonQuery(str, new Dictionary <string, string>()); if (r > 0) { str = "delete from allinone_customer_user where customerId=" + customerNo; SqlHelper.Instance.ExcuteNonQuery(str, new Dictionary <string, string>()); return(1); } return(0); } }
/// <summary> /// add /// </summary> /// <param name="AllInOne_UserInfo"></param> /// <returns></returns> public int Add(AllInOne_UserInfo model, SqlConnection connection = null, SqlTransaction transaction = null) { var str = GetInsertStr(model) + " select @@identity"; var dict = GetParameters(model); return(Convert.ToInt32(SqlHelper.Instance.ExecuteScalar(str, dict, connection, transaction))); }
/// <summary> /// 开户,绑定设备和用户 /// </summary> /// <param name="user"></param> /// <param name="customer"></param> /// <param name="device"></param> /// <param name="conn"></param> /// <param name="tran"></param> /// <returns></returns> public bool BindInfo(AllInOne_UserInfo user, CustomerInfo customer, DeviceInfo device, SqlConnection conn, SqlTransaction tran, out int meterno) { customer.useState = 1; device.customerNo = customer.customerNo; device.openState = 1; if (CustomerInfoOper.Instance.UpdateC(customer, conn, tran) == 0) { CustomerInfoOper.Instance.Add(customer, conn, tran); //AllInOne_Customer_UserOper.Instance.AddCU(customer, user, conn, tran); AllFunc.Instance.AddOperateRecord($"添加用户{customer.customerNo}", user.id);//-txy } if (UpdateC(device, conn, tran) == 0) { var id = Add(device, conn, tran); //AllInOne_Device_AreaOper.Instance.AddDeviceWithArea(user, id, conn, tran); meterno = id; AllFunc.Instance.AddOperateRecord($"添加设备{id}", user.id);//-txy } else { meterno = device.meterNo; } return(true); }
public int DeleteByMeterNo(int meterNo, AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; var condition = $" meterNo={meterNo} and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid )"; string str2 = $"select * from deviceView where {condition}"; var list = SqlHelper.Instance.ExecuteGetDt <DeviceView>(str2, dict); if (list.Count == 0) { return(0); } else { if (list.First().openState == 1) { return(2); } string str = "DELETE from DeviceInfo where meterNo=" + meterNo; var r = SqlHelper.Instance.ExcuteNonQuery(str, new Dictionary <string, string>()); if (r > 0) { str = "delete from allinone_device_area where deviceId=" + meterNo; SqlHelper.Instance.ExcuteNonQuery(str, new Dictionary <string, string>()); return(1); } return(0); } }
/// <summary> /// 获取自己范围内的操作者,包括自己 /// </summary> /// <param name="user"></param> /// <returns></returns> public List <AllInOne_UserInfo> GetMyOpertor(AllInOne_UserInfo user) { var list = GetSon(user); list.Add(user); return(list); }
/// <summary> /// 登录账户和新增的客户建立联系 /// </summary> /// <param name="customer"></param> /// <param name="user"></param> /// <param name="conn"></param> /// <param name="tran"></param> public void AddCU(CustomerInfo customer, AllInOne_UserInfo user, SqlConnection conn = null, SqlTransaction tran = null) { AllInOne_Customer_User acu = new AllInOne_Customer_User(); acu.customerId = customer.customerNo; acu.userId = user.id; Add(acu, conn, tran); }
public List <AllInOne_AlarmInfo> GetNewAlarmMeterNos(AllInOne_UserInfo user, int count) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; string str = $@"select top {count} devid,alarmContent from DeviceAlarmView where 1=1 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) order by id desc"; return(SqlHelper.Instance.ExecuteGetDt <AllInOne_AlarmInfo>(str, dict)); }
public List <CustomerView> GetListByUser(AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; string str2 = $"select * from CustomerView where 1=1 and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; return(SqlHelper.Instance.ExecuteGetDt <CustomerView>(str2, dict)); }
/// <summary> /// 获取自己及子孙的账号、员工 /// </summary> /// <param name="user"></param> /// <returns></returns> public List <AllInOne_UserInfo> GetSonAndGrandSon(AllInOne_UserInfo user) { var lastcid = GetLastCId(user); var dict = new Dictionary <string, string> { { "@lastcid", lastcid }, }; var str = $" select * from AllInOne_UserInfo where isdeleted=0 and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; return(SqlHelper.Instance.ExecuteGetDt <AllInOne_UserInfo>(str, dict)); }
/// <summary> /// 添加设备时,要添加设备所在区域 /// </summary> /// <param name="user"></param> /// <param name="deviceId"></param> public void AddDeviceWithArea(AllInOne_UserInfo user, int deviceId, SqlConnection conn = null, SqlTransaction tran = null) { //-txy 超级管理员添加设备不添加区域不记得是为什么。 //if (user.level != 100) //{ AllInOne_Device_Area ada = new AllInOne_Device_Area(); ada.areaId = user.areaId; ada.deviceId = deviceId; Add(ada, conn, tran); //} }
/// <summary> /// 根据设备号获取报警信息列表 /// </summary> /// <param name="meterNo"></param> /// <returns></returns> public List <AllInOne_AlarmInfo> GetByMeterNo(int meterNo, AllInOne_UserInfo user) { var size = 99; if (DeviceInfoOper.Instance.ConfirmUser(meterNo, user)) { return(SqlHelper.Instance.GetViewPaging <AllInOne_AlarmInfo>("AllInOne_AlarmInfo", " select * from AllInOne_AlarmInfo ", $" devid={meterNo}", 1, size, " order by id desc", new Dictionary <string, string>())); } else { return(new List <AllInOne_AlarmInfo>()); } }
public List <Counts> GetSafeAndAlarmCount(AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; string str = $@"select count(*) as counts from DeviceAlarmView where DealFlag=1 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) UNION select count(*) as counts from DeviceAlarmView where DealFlag=0 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid)"; return(SqlHelper.Instance.ExecuteGetDt <Counts>(str, dict)); }
public List <AllInOne_UserInfo> GetSonsByFatherName(string name, AllInOne_UserInfo user) { var lastCId = GetLastCId(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, { "@name", name }, }; string str = $@"select * from AllInOne_UserInfo where parentId in ( select id from AllInOne_UserInfo where name=@name and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid))"; return(SqlHelper.Instance.ExecuteGetDt <AllInOne_UserInfo>(str, dict)); }
/// <summary> /// 如果是员工,则找他公司的范围 /// </summary> /// <param name="user"></param> /// <returns></returns> public string DeviceGetLastCID(AllInOne_UserInfo user) { var lastCId = ""; if ((bool)user.isStaff) { lastCId = GetFatherLastCId(user); } else { lastCId = GetLastCId(user); } return(lastCId); }
/// <summary> /// 获取用户的最后一个cid /// </summary> /// <param name="user"></param> /// <returns></returns> public String GetLastCId(AllInOne_UserInfo user) { if (user.cId4 != null) { return(user.cId4); } if (user.cId3 != null) { return(user.cId3); } if (user.cId2 != null) { return(user.cId2); } return(user.cId1); }
/// <summary> /// 员工账号获取公司的cid /// </summary> /// <param name="user"></param> /// <returns></returns> public string GetFatherLastCId(AllInOne_UserInfo user) { if (user.cId4 != null) { return(user.cId3); } if (user.cId3 != null) { return(user.cId2); } if (user.cId2 != null) { return(user.cId1); } return("impossible"); }
/// <summary> /// 根据设备号获取设备视图 /// </summary> /// <param name="no"></param> /// <param name="user"></param> /// <returns></returns> public OneFLMeterDataView GetOneFLMeterViewViewByNo(int no, AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@no", no.ToString() }, { "@lastcid", lastCId }, }; string str = $"select * from OneFLMeterDataView where flmeterNo=@no and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; var temp = SqlHelper.Instance.ExecuteGetDt <OneFLMeterDataView>(str, dict); if (temp.Count == 0) { return(new OneFLMeterDataView()); } //str = $"select * from deviceInfo where meterNo={no}"; //var r = SqlHelper.Instance.ExecuteGetDt<DeviceView>(str, new Dictionary<string, string>()); return(temp.FirstOrDefault()); }
public List <DeviceAlarmView> GetNewAlarm(AllInOne_UserInfo user, int oldLastId) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; //string str = $@"select count(*) as counts from DeviceAlarmView where DealFlag=1 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) //UNION //select count(*) as counts from DeviceAlarmView where DealFlag=0 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid)"; var str = $@"select * from DeviceAlarmView where ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid ) and id>{oldLastId}"; //var r = SqlHelper.Instance.ExecuteScalar(str, dict); //if (r == null) // return 0; //return Convert.ToInt32(r); return(SqlHelper.Instance.ExecuteGetDt <DeviceAlarmView>(str, dict)); }
/// <summary> /// 获取区域列表总数 /// </summary> /// <param name="req"></param> /// <returns></returns> public int GetCount(OperRecordReq req, AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user);//-txy 迷 var search = req.search ?? ""; var dict = new Dictionary <string, string> { { "@search", $"%{search}%" }, { "@lastcid", lastCId }, }; var condition = " 1=1 and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; if (!search.IsNullOrEmpty()) { condition += " and (operatorName like @search or content like @search)"; } var list = SqlHelper.Instance.GetDistinctCount <AllInOne_OperateRecordView>("AllInOne_OperateRecordView", condition, dict); return(list.Count); }
public CustomerView GetViewByNo(string no, AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var dict = new Dictionary <string, string> { { "@no", no }, { "@lastcid", lastCId }, }; string str = $"select * from CustomerView where customerNo=@no and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) "; var temp = SqlHelper.Instance.ExecuteGetDt <CustomerView>(str, dict); if (temp.Count == 0) { return(new CustomerView()); } //str = $"select * from CustomerInfo where customerNo=@no"; //var list = SqlHelper.Instance.ExecuteGetDt<CustomerView>(str, dict); return(temp.FirstOrDefault()); }
/// <summary> /// 验证当前用户有没有权限搞这个设备 /// </summary> /// <param name="meterNo"></param> /// <param name="user"></param> /// <returns></returns> public bool ConfirmUser(int meterNo, AllInOne_UserInfo user) { var lastCId = ""; if ((bool)user.isStaff) { lastCId = AllInOne_UserInfoOper.Instance.GetFatherLastCId(user); } else { lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); } var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; var condition = $" meterNo={meterNo} and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid )"; string str = $"select * from deviceView where {condition}"; return(SqlHelper.Instance.ExecuteGetDt <DeviceView>(str, dict).Count() > 0); }
public List <Counts> GetSafeAndAlarmCountMonth(AllInOne_UserInfo user) { var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; var now = DateTime.Now; var year = now.Year; var month = now.Month; var dt = new DateTime(year, month, 1, 0, 0, 0); string str = $@"select count(*) as counts from DeviceAlarmView where DealFlag=1 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) and dealTime>'{dt}' UNION select count(*) as counts from DeviceAlarmView where DealFlag=0 and ( cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid)"; return(SqlHelper.Instance.ExecuteGetDt <Counts>(str, dict)); }
/// <summary> /// 用户的cids,补全 /// </summary> /// <param name="user"></param> /// <param name="pUser"></param> /// <returns></returns> public AllInOne_UserInfo CompleteCIds(AllInOne_UserInfo user, AllInOne_UserInfo pUser) { if (pUser.cId1 != null) { user.cId1 = pUser.cId1; } else { user.cId1 = $"{pUser.id}-{DateTime.Now.ToString("yyyyMMddHHMmmss")}"; return(user); } if (pUser.cId2 != null) { user.cId2 = pUser.cId2; } else { user.cId2 = $"{pUser.id}-{DateTime.Now.ToString("yyyyMMddHHMmmss")}"; return(user); } if (pUser.cId3 != null) { user.cId3 = pUser.cId3; } else { user.cId3 = $"{pUser.id}-{DateTime.Now.ToString("yyyyMMddHHMmmss")}"; return(user); } if (pUser.cId4 != null) { user.cId4 = pUser.cId4; } else { user.cId4 = $"{pUser.id}-{DateTime.Now.ToString("yyyyMMddHHMmmss")}"; return(user); } return(user); }
/// <summary> /// 获取自己有关的设备的meterNo /// </summary> /// <param name="lastCId"></param> /// <returns></returns> public List <int> GetDeviceIds(AllInOne_UserInfo user) { var lastCId = ""; if ((bool)user.isStaff) { lastCId = AllInOne_UserInfoOper.Instance.GetFatherLastCId(user); } else { lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); } var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; string str = "select meterNo from deviceView where cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid "; var list = SqlHelper.Instance.ExecuteGetDt <DeviceView>(str, dict); return(list.Select(p => p.meterNo).ToList()); }
/// <summary> /// 获取尚未开户的设备,和自己有关的,nbc的表不需要 /// </summary> /// <param name="lastCId"></param> /// <returns></returns> public List <DeviceView> GetNotOpen(AllInOne_UserInfo user) { //var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var lastCId = ""; if ((bool)user.isStaff) { lastCId = AllInOne_UserInfoOper.Instance.GetFatherLastCId(user); } else { lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); } var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, }; string str = "select * from DeviceView where defineNo1!='NBCivil' and customerno='' and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid) order by meterNo desc"; return(SqlHelper.Instance.ExecuteGetDt <DeviceView>(str, dict)); //return SqlHelper.Instance.GetByCondition<DeviceInfo>("deviceInfo"," openstate=0 "); }
public List <DeviceView> GetDeviceViewByCompanyName(string name, AllInOne_UserInfo user) { //var lastCId = AllInOne_UserInfoOper.Instance.GetLastCId(user); var lastCId = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); var cid = AllInOne_UserInfoOper.Instance.GetLastCIdByName(name); var dict = new Dictionary <string, string> { { "@lastcid", lastCId }, { "@cid", cid } }; var condition = ""; if (name != null) { //var cid = AllInOne_UserInfoOper.Instance.GetLastCIdByName(name); //dict.Add("@name",name); //condition += $" and username=@name"; //var cid = AllInOne_UserInfoOper.Instance.DeviceGetLastCID(user); if (cid != null) { //dict.Add("@cid", cid); condition += $" and (cid1=@cid or cid2=@cid or cid3=@cid or cid4=@cid) "; } else { return(new List <DeviceView>()); } } string str = "select distinct customerNo,customerName from deviceView where customerno!='' and (cid1=@lastcid or cid2=@lastcid or cid3=@lastcid or cid4=@lastcid ) " + condition; return(SqlHelper.Instance.ExecuteGetDt <DeviceView>(str, dict)); }
/// <summary> /// 插入 /// </summary> /// <param name="allinone_userinfo"></param> /// <returns>是否成功</returns> public string GetInsertStr(AllInOne_UserInfo allinone_userinfo) { StringBuilder part1 = new StringBuilder(); StringBuilder part2 = new StringBuilder(); if (allinone_userinfo.name != null) { part1.Append("name,"); part2.Append("@name,"); } if (allinone_userinfo.account != null) { part1.Append("account,"); part2.Append("@account,"); } if (allinone_userinfo.pwd != null) { part1.Append("pwd,"); part2.Append("@pwd,"); } if (allinone_userinfo.level != null) { part1.Append("level,"); part2.Append("@level,"); } if (allinone_userinfo.jurisdiction != null) { part1.Append("jurisdiction,"); part2.Append("@jurisdiction,"); } if (allinone_userinfo.isDeleted != null) { part1.Append("isDeleted,"); part2.Append("@isDeleted,"); } if (allinone_userinfo.parentId != null) { part1.Append("parentId,"); part2.Append("@parentId,"); } if (allinone_userinfo.isStaff != null) { part1.Append("isStaff,"); part2.Append("@isStaff,"); } if (allinone_userinfo.areaId != null) { part1.Append("areaId,"); part2.Append("@areaId,"); } if (allinone_userinfo.cId1 != null) { part1.Append("cId1,"); part2.Append("@cId1,"); } if (allinone_userinfo.cId2 != null) { part1.Append("cId2,"); part2.Append("@cId2,"); } if (allinone_userinfo.cId3 != null) { part1.Append("cId3,"); part2.Append("@cId3,"); } if (allinone_userinfo.cId4 != null) { part1.Append("cId4,"); part2.Append("@cId4,"); } if (allinone_userinfo.phone != null) { part1.Append("phone,"); part2.Append("@phone,"); } if (allinone_userinfo.openIds != null) { part1.Append("openIds,"); part2.Append("@openIds,"); } StringBuilder sql = new StringBuilder(); sql.Append("insert into allinone_userinfo(").Append(part1.ToString().Remove(part1.Length - 1)).Append(") values (").Append(part2.ToString().Remove(part2.Length - 1)).Append(")"); return(sql.ToString()); }
/// <summary> /// 更新 /// </summary> /// <param name="allinone_userinfo"></param> /// <returns>是否成功</returns> public string GetUpdateStr(AllInOne_UserInfo allinone_userinfo) { StringBuilder part1 = new StringBuilder(); part1.Append("update allinone_userinfo set "); if (allinone_userinfo.name != null) { part1.Append("name = @name,"); } if (allinone_userinfo.account != null) { part1.Append("account = @account,"); } if (allinone_userinfo.pwd != null) { part1.Append("pwd = @pwd,"); } if (allinone_userinfo.level != null) { part1.Append("level = @level,"); } if (allinone_userinfo.jurisdiction != null) { part1.Append("jurisdiction = @jurisdiction,"); } if (allinone_userinfo.isDeleted != null) { part1.Append("isDeleted = @isDeleted,"); } if (allinone_userinfo.parentId != null) { part1.Append("parentId = @parentId,"); } if (allinone_userinfo.isStaff != null) { part1.Append("isStaff = @isStaff,"); } if (allinone_userinfo.areaId != null) { part1.Append("areaId = @areaId,"); } if (allinone_userinfo.cId1 != null) { part1.Append("cId1 = @cId1,"); } if (allinone_userinfo.cId2 != null) { part1.Append("cId2 = @cId2,"); } if (allinone_userinfo.cId3 != null) { part1.Append("cId3 = @cId3,"); } if (allinone_userinfo.cId4 != null) { part1.Append("cId4 = @cId4,"); } if (allinone_userinfo.phone != null) { part1.Append("phone = @phone,"); } if (allinone_userinfo.openIds != null) { part1.Append("openIds = @openIds,"); } int n = part1.ToString().LastIndexOf(","); part1.Remove(n, 1); part1.Append(" where id= @id "); return(part1.ToString()); }