public IList <dynamic> StaffListQueryFname(JObject filter)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sqlNO = "";
if (!string.IsNullOrWhiteSpace(filter["name"].ToString()))
{
sqlNO = $" AND (csL.FNAME LIKE '%{filter["name"].ToString()}%' OR cs.FMOBILE LIKE '%{filter["name"].ToString()}%' ) ";
}
string sql = @" select * from ( SELECT cs.FID,cs.KHNAME,csl.FNAME, cs.FMOBILE,FTELE,FQQ,FWECHAT,FENABLE,csl.FJOB,tcl.FCHANNELID,TCL.FNAME FCHANNELNAME,ROW_NUMBER() over(order by cs.FID desc) XH FROM T_ESS_CHANNELSTAFF cs
LEFT OUTER JOIN T_ESS_CHANNELSTAFF_L csl ON (cs.FID = csl.FID AND csl.FLOCALEID = 2052)
LEFT OUTER JOIN T_ESS_CHANNEL_L tcl ON tcl.FCHANNELID=CS.FCHANNELID AND TCL.FLOCALEID=2052 where 1=1 " + sqlNO + " AND tcl.FCHANNELID like :p1 AND tcl.FCHANNELID <> 27) t ";
//sql += $" order by t.FID desc offset { (Convert.ToInt32(filter["page"]) - 1) * Convert.ToInt32(filter["limit"]) } rows fetch next { Convert.ToInt32(filter["limit"]) } rows only";
sql += $"where XH > { (Convert.ToInt32(filter["page"]) - 1) * Convert.ToInt32(filter["limit"]) } and XH <= { (Convert.ToInt32(filter["page"])) * Convert.ToInt32(filter["limit"]) }";
var list = session
.CreateSQLQuery(sql)
.SetParameter("p1", "%" + filter["channelId"] + "%")
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(list);
}
public IList <dynamic> GetSelectUser(string sqlNO, JObject filter)
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @" SELECT * FROM (SELECT ROW_NUMBER() over(order by T_ESS_CHANNELSTAFF_L.FROLEID desc) XH,
T_ESS_CHANNELSTAFF.FID, T_ESS_CHANNELSTAFF.KHNAME, T_ESS_CHANNELSTAFF.FMOBILE,
T_ESS_CHANNELSTAFF_L.FJOB, T_ESS_CHANNELSTAFF_L.FROLEID, T_ESS_CHANNELSTAFF.FENABLE,
T_ESS_CHANNELSTAFF.FQQ, T_ESS_CHANNELSTAFF.FTELE, A_ROLE.FPERMISSIONS,
T_ESS_CHANNELSTAFF_RelationShip.StaffID, T_ESS_CHANNELSTAFF_1.KHNAME AS KFNAME
FROM T_ESS_CHANNELSTAFF T_ESS_CHANNELSTAFF_1 INNER JOIN
T_ESS_CHANNELSTAFF_RelationShip ON
T_ESS_CHANNELSTAFF_1.FID = T_ESS_CHANNELSTAFF_RelationShip.StaffID RIGHT OUTER JOIN
T_ESS_CHANNELSTAFF INNER JOIN
T_ESS_CHANNELSTAFF_L ON T_ESS_CHANNELSTAFF.FID = T_ESS_CHANNELSTAFF_L.FID INNER JOIN
A_ROLE ON T_ESS_CHANNELSTAFF_L.FROLEID = A_ROLE.FID ON
T_ESS_CHANNELSTAFF_RelationShip.CustomerID = T_ESS_CHANNELSTAFF.FID " + sqlNO + ") t ";
sql += $"where XH > { (Convert.ToInt32(filter["page"]) - 1) * Convert.ToInt32(filter["limit"]) } and XH <= { (Convert.ToInt32(filter["page"])) * Convert.ToInt32(filter["limit"]) }";
//ISession session = NHSessionProvider.SessionFactory.OpenSession();
//var list = session.CreateSQLQuery(sql).List();//执行查询
IList <dynamic> activicyLists = session.CreateSQLQuery(sql)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(activicyLists);
}
public dynamic QueryWxappUserByPhoneNumber(string phoneNumber)
{
ISession session = NHSessionProvider.GetCurrentSession();
// string sql = @"SELECT A.FID, A.ISNEW, A.A3ID, l.FNUMBER FCHANNELCODE,A.FCHANNELID,A.FMOBILE,A.SALT,A.PASSWORD,B.FNAME, B.FJOB,B.FROLEID, C.FNAME AS CHANNELNAME,L.FCUSTOMERID,FCHANNELTYPEID,TTL.FNAME FCHANNELTYPENAME,CA.PICTURE,A.KHNAME
// FROM T_ESS_CHANNELSTAFF A LEFT JOIN T_ESS_CHANNELSTAFF_L B ON B.FID = A.FID
// LEFT JOIN T_ESS_CHANNEL L ON L.FCHANNELID = A.FCHANNELID INNER JOIN T_ESS_CHANNEL_L C ON A.FCHANNELID = C.FCHANNELID
//LEFT JOIN dbo.T_ESS_CHANNELSTAFF_AVATAR CA ON CA.STAFFID = A.FID
// LEFT JOIN T_ESS_CHANNELTYPE_L TTL ON (TTL.FTYPEID=L.FCHANNELTYPEID AND TTL.FLOCALEID=2052 ) WHERE A.FENABLE = 1 AND ISNULL(A.FWXOPENID,'') <> '' AND A.FMOBILE = :P1";
string sql = @"SELECT A.FID,A.FWXOPENID, A.ISNEW, A.A3ID, l.FNUMBER FCHANNELCODE,A.FCHANNELID,A.FMOBILE,A.SALT,A.PASSWORD,B.FNAME, B.FJOB,B.FROLEID, C.FNAME AS CHANNELNAME,L.FCUSTOMERID,FCHANNELTYPEID,TTL.FNAME FCHANNELTYPENAME,CA.PICTURE,A.KHNAME,A.XCXOPENID
FROM T_ESS_CHANNELSTAFF A LEFT JOIN T_ESS_CHANNELSTAFF_L B ON B.FID = A.FID
LEFT JOIN T_ESS_CHANNEL L ON L.FCHANNELID = A.FCHANNELID INNER JOIN T_ESS_CHANNEL_L C ON A.FCHANNELID = C.FCHANNELID
left join A_ROLE R on R.FID = B.FROLEID
LEFT JOIN dbo.T_ESS_CHANNELSTAFF_AVATAR CA ON CA.STAFFID = A.FID
LEFT JOIN T_ESS_CHANNELTYPE_L TTL ON (TTL.FTYPEID=L.FCHANNELTYPEID AND TTL.FLOCALEID=2052 ) WHERE A.FMOBILE = :P1";
var staff = session
.CreateSQLQuery(sql)
.SetParameter("P1", phoneNumber)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>()
.FirstOrDefault();
return(staff);
}
/// <summary>
/// 保存职务信息
/// </summary>
/// <param name="aactivity">职务信息</param>
public void Edit(ARole aactivity)
{
ISession session = NHSessionProvider.GetCurrentSession();
session.Update(aactivity);
session.Flush();
}
/// <summary>
/// 保存门店职员信息
/// </summary>
/// <param name="staff">门店职员信息</param>
public void Save(ESSChannelStaff staff)
{
ISession session = NHSessionProvider.GetCurrentSession();
session.Save(staff);
session.Flush();
}
public Response Relation([FromUri] int FID)
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @"SELECT derivedtbl_1.FNAME, derivedtbl_2.KHNAME, derivedtbl_2.createTime, derivedtbl_1.FPKID,
derivedtbl_2.FWXOPENID AS 外部人员openid, derivedtbl_1.FWXOPENID AS 内部人员openid
FROM T_ESS_CHANNELSTAFF_RelationShip INNER JOIN
(SELECT T_ESS_CHANNELSTAFF_L.FPKID, T_ESS_CHANNELSTAFF_L.FNAME, A_ROLE.FPERMISSIONS,
T_ESS_CHANNELSTAFF.FWXOPENID
FROM A_ROLE INNER JOIN
T_ESS_CHANNELSTAFF_L ON A_ROLE.FID = T_ESS_CHANNELSTAFF_L.FROLEID INNER JOIN
T_ESS_CHANNELSTAFF ON T_ESS_CHANNELSTAFF_L.FPKID = T_ESS_CHANNELSTAFF.FID
WHERE(A_ROLE.FPERMISSIONS = '1')) derivedtbl_1 ON T_ESS_CHANNELSTAFF_RelationShip.StaffID = derivedtbl_1.FPKID INNER JOIN
(SELECT T_ESS_CHANNELSTAFF_1.FID, T_ESS_CHANNELSTAFF_1.KHNAME,
T_ESS_CHANNELSTAFF_1.FWXOPENID, news.createTime
FROM T_ESS_CHANNELSTAFF T_ESS_CHANNELSTAFF_1 INNER JOIN
(SELECT FromUserName, MAX(Id) AS id, MAX(CreateTime) AS createTime
FROM T_CUS_SERVER_MSG
GROUP BY FromUserName) news ON T_ESS_CHANNELSTAFF_1.FWXOPENID = news.FromUserName)
derivedtbl_2 ON T_ESS_CHANNELSTAFF_RelationShip.CustomerID = derivedtbl_2.FID
WHERE(derivedtbl_1.FPKID = :p1)";
var Relation = session.CreateSQLQuery(sql).SetParameter("p1", FID)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(new Response
{
Result = Relation
});
}
/// <summary>
/// 删除门店职员
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int Delete(int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql2 = "DELETE FROM ESSChannelStaff WHERE FID =:p1 ";
string sql1 = "DELETE FROM ESSChannelStaff_L WHERE FID =:p1 ";
using (ITransaction transaction = session.BeginTransaction())
{
try
{
int result1 = session
.CreateQuery(sql2)
.SetParameter("p1", id)
.ExecuteUpdate();
int result = session
.CreateQuery(sql1)
.SetParameter("p1", id)
.ExecuteUpdate();
transaction.Commit();
return(result);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
/// <summary>
/// 保存职务信息
/// </summary>
/// <param name="aactivity">职务信息</param>
public void Save(ARole aactivity)
{
ISession session = NHSessionProvider.GetCurrentSession();
session.Save(aactivity);
session.Flush();
}
/// <summary>
/// 更改门店职员副表信息
/// </summary>
/// <param name="staffL">门店职员副表信息</param>
public void EditEntry(ESSChannelStaff_L staffL)
{
ISession session = NHSessionProvider.GetCurrentSession();
session.SaveOrUpdate(staffL);
session.Flush();
}
public Response Responses([FromUri] int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @"SELECT ROW_NUMBER() over(order by dbo.T_ESS_CHANNELSTAFF.FID desc) XH,T_ESS_CHANNELSTAFF.FID, T_ESS_CHANNELSTAFF.KHNAME, T_ESS_CHANNELSTAFF.FMOBILE,
T_ESS_CHANNELSTAFF_L.FJOB, T_ESS_CHANNELSTAFF_L.FROLEID, T_ESS_CHANNELSTAFF.FENABLE,
T_ESS_CHANNELSTAFF.FQQ, T_ESS_CHANNELSTAFF.FTELE, A_ROLE.FPERMISSIONS,
T_ESS_CHANNELSTAFF_1.FID AS KFFID
FROM T_ESS_CHANNELSTAFF T_ESS_CHANNELSTAFF_1 INNER JOIN
T_ESS_CHANNELSTAFF_RelationShip ON
T_ESS_CHANNELSTAFF_1.FID = T_ESS_CHANNELSTAFF_RelationShip.StaffID RIGHT OUTER JOIN
T_ESS_CHANNELSTAFF INNER JOIN
T_ESS_CHANNELSTAFF_L ON T_ESS_CHANNELSTAFF.FID = T_ESS_CHANNELSTAFF_L.FID INNER JOIN
A_ROLE ON T_ESS_CHANNELSTAFF_L.FROLEID = A_ROLE.FID ON
T_ESS_CHANNELSTAFF_RelationShip.CustomerID = T_ESS_CHANNELSTAFF.FID
WHERE(T_ESS_CHANNELSTAFF.FID = :p1)";
IList <dynamic> activicyLists = session.CreateSQLQuery(sql)
.SetParameter("p1", id)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(new Response
{
Result = activicyLists,
});
}
public Response ResponsesssS([FromUri] int id, [FromUri] int FID)
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @"SELECT dbo.A_ROLE.* FROM dbo.A_ROLE where FID=" + id;
IList <dynamic> activicyLists = session.CreateSQLQuery(sql)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
var sql1 = @"SELECT ROW_NUMBER() over(order by dbo.T_ESS_CHANNELSTAFF.FID desc) XH,
dbo.T_ESS_CHANNELSTAFF.FID, dbo.T_ESS_CHANNELSTAFF.KHNAME, dbo.T_ESS_CHANNELSTAFF.FMOBILE,
dbo.T_ESS_CHANNELSTAFF_L.FJOB, dbo.T_ESS_CHANNELSTAFF_L.FROLEID, dbo.T_ESS_CHANNELSTAFF.FENABLE,
dbo.T_ESS_CHANNELSTAFF.FQQ, dbo.T_ESS_CHANNELSTAFF.FTELE, dbo.A_ROLE.FPERMISSIONS
FROM dbo.T_ESS_CHANNELSTAFF INNER JOIN
dbo.T_ESS_CHANNELSTAFF_L ON dbo.T_ESS_CHANNELSTAFF.FID = dbo.T_ESS_CHANNELSTAFF_L.FID INNER JOIN
dbo.A_ROLE ON dbo.T_ESS_CHANNELSTAFF_L.FROLEID = dbo.A_ROLE.FID where dbo.T_ESS_CHANNELSTAFF.FID =" + FID;
IList <dynamic> activicyLists1 = session.CreateSQLQuery(sql1)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(new Response
{
Result = new
{
activicyLists,
activicyLists1
}
});
}
public int QueryMaxFnumber(string tableName, string years, string months)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql2 = $"SELECT isnull(right(max(FNUMBER),4),0)+1 FROM {tableName} where left(right(FNUMBER,10),4)={years} and left(right(FNUMBER,6),2) ={months} ";
int id = session.CreateSQLQuery(sql2).List <int>().FirstOrDefault();
return(id);
}
public ARole QueryCustomerServiceRole()
{
ISession session = NHSessionProvider.GetCurrentSession();
return(session
.QueryOver <ARole>()
.And(x => x.FNAME == "客服")
.List()
.FirstOrDefault());
}
/// <summary>
/// 添加用户和管理者的关联
/// </summary>
/// <param name="FWXOPENID">用户id</param>
public void ZXKH_AddShip(string FWXOPENID, long FPKID)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = @"insert into T_ESS_CHANNELSTAFF_RelationShip values(:p2,(select FID from T_ESS_CHANNELSTAFF WHERE XCXOPENID = :p1), DATEDIFF(second, '1970-01-01 08:00:00', GETDATE()))";
session.CreateSQLQuery(sql)
.SetParameter("p1", FWXOPENID)
.SetParameter("p2", FPKID)
.ExecuteUpdate();
}
public void ZXKH_Delete_Customers(string FWXOPENID)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql =
@"delete from T_ESS_CHANNELSTAFF_RelationShip where CustomerID = (select fid from T_ESS_CHANNELSTAFF where XCXOPENID=:p1)";
session.CreateSQLQuery(sql)
.SetParameter("p1", FWXOPENID)
.ExecuteUpdate();
}
public void UpdateIsNewStatus(int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = "UPDATE T_ESS_CHANNELSTAFF SET ISNEW = 1 WHERE FID = :p1";
session
.CreateSQLQuery(sql)
.SetParameter("p1", id)
.ExecuteUpdate();
}
public void QueryWxappUserEditFwxOpenid(string phoneNumber, string wxopenid)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = @"update T_ESS_CHANNELSTAFF set FWXOPENID = :P2 where FMOBILE = :P1";
session.CreateSQLQuery(sql)
.SetParameter("P1", phoneNumber)
.SetParameter("P2", wxopenid)
.ExecuteUpdate();
}
/// <summary>
/// 获取门店职员最大号
/// </summary>
/// <returns></returns>
public int StaffMaxId()
{
ISession session = NHSessionProvider.GetCurrentSession();
var maxId = session
.CreateCriteria(typeof(ESSChannelStaff))
.SetProjection(Projections.Max("FID"))
.UniqueResult();
return(maxId == null ? 100001 : int.Parse(maxId.ToString()) + 1);
}
/// <summary>
/// 获取门店职员副表信息
/// </summary>
/// <returns></returns>
public IList <ESSChannelStaff_L> DetailEntryById(int Id)
{
ISession session = NHSessionProvider.GetCurrentSession();
var esschannelStaffs = session
.QueryOver <ESSChannelStaff_L>()
.Where(x => x.FID == Id)
.List <ESSChannelStaff_L>();
return(esschannelStaffs);
}
/// <summary>
/// 客户与客服是否有关联
/// </summary>
/// <param name="FWXOPENID">用户openid</param>
/// <returns></returns>
public IList <dynamic> ZXKH_Staff_Customers(string FWXOPENID)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql =
@"select ShipID,StaffID,CustomerID from T_ESS_CHANNELSTAFF_RelationShip where CustomerID = (select fid from T_ESS_CHANNELSTAFF where XCXOPENID=:p1)";
return(session.CreateSQLQuery(sql)
.SetParameter("p1", FWXOPENID)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>());
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public ESSChannelStaff Detail(int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
var staff = session
.QueryOver <ESSChannelStaff>()
.Where(x => x.FID == id)
.List <ESSChannelStaff>().FirstOrDefault();
return(staff);
}
/// <summary>
/// 获取职务信息
/// </summary>
/// <returns></returns>
public IList <ARole> DetailById(int Id)
{
ISession session = NHSessionProvider.GetCurrentSession();
var aRoles = session
.QueryOver <ARole>()
.Where(x => x.FID == Id)
.List <ARole>();
return(aRoles);
}
/// <summary>
/// 批量修改客服
/// </summary>
/// <param name="id"></param>
/// <param name="fID"></param>
public void DatchEditKF(int id, string FID)
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @"update T_ESS_CHANNELSTAFF_RelationShip set StaffID=:p1 where CustomerID=:p2";
session.CreateSQLQuery(sql)
.SetParameter("p1", id)
.SetParameter("p2", FID)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
}
/// <summary>
/// 查询所有门店
/// </summary>
public IList <dynamic> SelectAllStore()
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = "SELECT A.FCHANNELID,B.FNAME FROM T_ESS_CHANNEL A INNER JOIN T_ESS_CHANNEL_L B ON A.FCHANNELID = B.FCHANNELID WHERE A.FENABLE = 1";
IList <dynamic> stores = session
.CreateSQLQuery(sql)
.List <dynamic>();
return(stores);
}
/// <summary>
/// 根据wxopenid查询客户信息
/// </summary>
/// <param name="wxopenid"></param>
/// <returns></returns>
public ESSChannelStaff QueryCustomerByWxopenid(string wxopenid)
{
ISession session = NHSessionProvider.GetCurrentSession();
var customer = session
.QueryOver <ESSChannelStaff>()
.And(x => x.XCXOPENID == wxopenid)
.List()
.FirstOrDefault();
return(customer);
}
/// <summary>
/// 查询客户头像
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ESSChannelStaffAvatar QueryCustomerAvatar(int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
var avatar = session
.QueryOver <ESSChannelStaffAvatar>()
.And(x => x.StaffId == id)
.List()
.FirstOrDefault();
return(avatar);
}
/// <summary>
/// 查询职务权限
/// </summary>
/// <param name="roleId"></param>
public IList <string> QueryRoleHasPermissions(int roleId)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = "SELECT FNAME FROM A_ROLE_PERMISSION A INNER JOIN A_PERMISSION B ON A.FPERID = B.FID WHERE A.FROLEID = :P1";
IList <string> pers = session
.CreateSQLQuery(sql)
.SetParameter("P1", roleId)
.List <string>();
return(pers);
}
/// <summary>
/// 获取当前职务拥有的模块
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IList <CommonBO> QueryRoleHasModules(int id)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = "SELECT A.FID,A.FNAME,B.FROLEID AS PARENTID FROM A_PERGROUP A INNER JOIN A_ROLE_PERGROUP B ON A.FID = B.FPERGROUPID WHERE A.FENABLED = 1 AND B.FROLEID = :P1";
var result = session.CreateSQLQuery(sql)
.SetParameter("P1", id)
.SetResultTransformer(Transformers.AliasToBean(typeof(CommonBO)))
.List <CommonBO>();
return(result);
}
public void UpdateCustomerA3Id(int id, long a3id)
{
ISession session = NHSessionProvider.GetCurrentSession();
string sql = "UPDATE T_ESS_CHANNELSTAFF SET A3ID = :p1 WHERE FID = :p2";
session
.CreateSQLQuery(sql)
.SetParameter("p1", a3id)
.SetParameter("p2", id)
.ExecuteUpdate();
}
public Response Responsess()
{
ISession session = NHSessionProvider.GetCurrentSession();
var sql = @"SELECT dbo.A_ROLE.* FROM dbo.A_ROLE";
IList <dynamic> activicyLists = session.CreateSQLQuery(sql)
.SetResultTransformer(new AliasToEntityMapResultTransformer())
.List <dynamic>();
return(new Response
{
Result = activicyLists,
});
}