public DataTable GetTaskList(string employeeNo, string taskName, string isFinished, string taskType, int pageIndex, int pageSize, out int totalAmount) { totalAmount = 0; StringBuilder strSql = new StringBuilder(@"select employeeNO,folder,case isfinished when '0' then '未完成' else '已完成' END isFinished, CASE taskType WHEN '0' THEN '普通任务' ELSE '售后' END taskType,expiredate from taskreminding tr where 1=1 "); if (!string.IsNullOrEmpty(employeeNo)) { strSql.AppendFormat(" and employeeno like '%{0}%'", employeeNo); } if (!string.IsNullOrEmpty(taskName)) { strSql.AppendFormat(" and folder like '%{0}%'", taskName); } if (!string.IsNullOrEmpty(isFinished)) { strSql.AppendFormat(" and isfinished='{0}'", isFinished); } if (!string.IsNullOrEmpty(taskType)) { strSql.AppendFormat(" and tasktype='{0}'", taskType); } strSql.AppendFormat(" order by employeeno,folder,expiredate desc LIMIT {0},{1}", (pageIndex - 1) * pageSize, pageSize); DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { totalAmount = ds.Tables[0].Rows.Count; return(ds.Tables[0]); } return(null); }
/// <summary> /// 判断员工编号是否存在 /// </summary> /// <param name="employeeNO">员工编号</param> /// <returns></returns> public bool IsExist(string employeeNO) { string sql = string.Format("SELECT COUNT(*) FROM EMPLOYEE WHERE EMPLOYEENO = '{0}'", employeeNO); DataSet ds = MySqlHelper.GetDataSet(sql); int amount = 0; if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { amount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); } return(amount > 0); }
/// <summary> /// 获取省份 /// </summary> /// <returns></returns> public DataTable GetProvince() { String strSql = @"SELECT `CODE`,`NAME` from region order by `code`"; DataSet ds = MySqlHelper.GetDataSet(strSql); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 根据配置名称获取配置 /// </summary> /// <returns></returns> public DataTable GetConfig() { String strSql = @"select configkey,configvalue,configtypename from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid"; DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 获取任务目标值 /// </summary> /// <returns></returns> public DataTable GetTaskObjectiveValue() { string sql = @"SELECT t.*,employeeno from taskobjectivevalue t INNER JOIN employee e on t.employeeid=e.id order by employeeno"; DataSet ds = MySqlHelper.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 获取默认提成比例 /// </summary> /// <returns></returns> public DataTable GetDefaultProportionConfig() { StringBuilder strSql = new StringBuilder(@" SELECT ct.ID, cv.CONFIGVALUE from configvalue cv INNER JOIN configtype ct on cv.CONFIGTYPEID = ct.CONFIGTYPEID WHERE ct.CONFIGTYPENAME = '任务默认提成比例';"); DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 根据配置名称获取配置 /// </summary> /// <param name="configType">配置名称</param> /// <returns></returns> public DataTable GetConfig(string configType) { StringBuilder strSql = new StringBuilder(@" select configkey,configvalue from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid WHERE ct.configtypename='" + configType + "'"); DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
public DataTable GetSpecialtyConfig(string employeeID, string employeeNo) { StringBuilder strSql = new StringBuilder(@" select '" + employeeID + @"' employeeID, '" + employeeNo + @"' employeeNo, 0 qualityScore, configkey specialtyKey,configvalue specialtyName, NULL available from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid WHERE ct.configtypename='专业类别'"); DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 获取最大编号值 /// </summary> /// <param name="userType"></param> /// <returns></returns> public string GetMaxEmployeeNO(string userType) { string strSql = string.Format(@"SELECT MAX(RIGHT(employeeno,3)) FROM employee WHERE available = 1 AND type='{0}'", userType); string maxNo = string.Empty; try { DataSet ds = MySqlHelper.GetDataSet(strSql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { maxNo = ds.Tables[0].Rows[0][0].ToString(); } return(maxNo); } catch (Exception) { return(maxNo); } }
/// <summary> /// 判断任务完成稿是否提醒,在用 /// </summary> /// <param name="userIDorEmpNo">userID</param> /// <param name="folder">目录名(即任务编号)</param> /// <param name="modifyFolder">修改任务名</param> /// <param name="taskType">任务类型:0,新任务;1,售后;2,倒计时1小时;3,新任务待分配</param> /// <param name="toUserType">发送到的用户的类型</param> /// <returns></returns> public bool IsExist(string userIDorEmpNo, string folder, string modifyFolder, string taskType, string toUserType) { string sql = string.Empty; //1是客服人员 if (toUserType == "1") { sql = string.Format(@"SELECT count(*) from taskreminding where enteringperson = '{0}' and folder = '{1}' and taskType = '{2}' AND TOUSERTYPE = {3}", userIDorEmpNo, folder, taskType, toUserType); //是修改任务的 if (!string.IsNullOrEmpty(modifyFolder)) { sql += string.Format(" and modifyFolder = '{0}'", modifyFolder); } } else if (toUserType == "2") //造价员 { sql = string.Format(@"SELECT count(*) from taskreminding where employeeNo = '{0}' and folder = '{1}' and taskType = '{2}' AND TOUSERTYPE = {3}", userIDorEmpNo, folder, taskType, toUserType); //是修改任务的 if (!string.IsNullOrEmpty(modifyFolder)) { sql += string.Format(" and modifyFolder = '{0}'", modifyFolder); } } else if (toUserType == "0") //管理员 { sql = string.Format(@"SELECT COUNT(*) FROM TASKREMINDING WHERE FOLDER = '{0}' AND TOUSERTYPE = '{1}'", folder, toUserType); } else { return(false); } DataSet ds = MySqlHelper.GetDataSet(sql); int amount = 0; if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { amount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); } return(amount > 0); }
public DataTable GetSystemLog(Dictionary <string, string> dicCondition, int pageIndex, int pageSize, out int totalAmount) { totalAmount = 0; StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"SELECT e.employeeno,l.createdate,l.ipaddress, l.operatecontent from systemlog l INNER JOIN employee e ON l.employeeid = e.id WHERE 1 = 1 "); if (dicCondition.ContainsKey("employeeNO")) { sbSql.AppendFormat(" AND employeeNO LIKE '%{0}%'", dicCondition["employeeNO"]); } if (dicCondition.ContainsKey("operateDateStart")) { sbSql.AppendFormat(" AND createDate >= '{0}'", dicCondition["operateDateStart"]); } if (dicCondition.ContainsKey("operateDateEnd")) { sbSql.AppendFormat(" AND createDate <= '{0}'", dicCondition["operateDateEnd"]); } if (dicCondition.ContainsKey("operateType")) { sbSql.AppendFormat(" AND operateType = '{0}'", dicCondition["operateType"]); } DataSet dsRowsCount = MySqlHelper.GetDataSet(sbSql.ToString()); if (dsRowsCount != null && dsRowsCount.Tables.Count > 0) { totalAmount = dsRowsCount.Tables[0].Rows.Count; } sbSql.AppendFormat(" ORDER BY l.createdate DESC LIMIT {0},{1}", (pageIndex - 1) * pageSize, pageSize); DataSet ds = DbHelperMySQL.Query(sbSql.ToString()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0]); } return(null); }
public DataTable GetShopKeys() { string sql = @"SELECT B.ID, A.configkey, A.configvalue, B.SHOPID, B.accessKey, B.secretKey, isActive FROM (select configkey,configvalue from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid WHERE ct.configtypename='店铺编号名称') A RIGHT JOIN ( SELECT * FROM SHOPKEYS ) B ON a.configkey = b.shopId"; DataSet ds = MySqlHelper.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 获取用户 /// </summary> /// <param name="userName"></param> /// <param name="passWord"></param> /// <returns></returns> public DataTable GetUser(string userName, string passWord) { string strSql = string.Format(@"SELECT emp.ID,emp.EMPLOYEENO,emp.`NAME`, emp.ISEXTERNAL, emp.TOREGULARDATE, r.ID roleID,r.ROLENAME, m.MENUNAME,m.MENUPATH from employee emp INNER JOIN employeerole emprole on emp.id=emprole.employeeid INNER JOIN role r on emprole.roleid=r.id INNER JOIN rolemenumap rmm ON r.ID=rmm.ROLEID INNER JOIN menu m ON rmm.MENUID=m.ID where EMPLOYEENO='{0}' and `PASSWORD`='{1}'" , userName, passWord); DataSet ds = MySqlHelper.GetDataSet(strSql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0]); } return(null); }
/// <summary> /// 获取日志列表 /// </summary> /// <param name="userID">用户ID</param> /// <param name="operateType">操作类型</param> /// <param name="content">记录内容</param> /// <param name="startDateTime">开始时间</param> /// <param name="endDateTime">结束时间</param> /// <param name="fileName">文件名</param> /// <returns></returns> public DataTable GetFileZilla(string userID, string operateType, string content, string startDateTime, string endDateTime, string fileName, int pageIndex, int pageSize, out int totalAmount) { totalAmount = 0; StringBuilder strSql = new StringBuilder(@" /* SELECT fz.OPERATEUSERID,fz.content,fz.operatedate,fz.filename,fz.ipaddress,cfg.configvalue operatetype FROM fileZillaLog fz LEFT JOIN ( select configkey,configvalue from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid ) cfg ON fz.operatetype=cfg.configkey WHERE 1=1 */ SELECT fz.OPERATEUSERID,fz.content,fz.operatedate,fz.filename,fz.ipaddress,cfg.configvalue operatetype FROM fileZillaLog fz LEFT JOIN ( select configkey,configvalue from configvalue cv left join configtype ct on cv.configtypeid=ct.configtypeid ) cfg ON fz.operatetype=cfg.configkey WHERE fz.ID in ( SELECT Id FROM ( SELECT ID FROM fileZillaLog fz WHERE 1 = 1 "); StringBuilder sbWhere = new StringBuilder(); if (!string.IsNullOrEmpty(userID)) { sbWhere.AppendFormat(" AND OPERATEUSERID='{0}'", userID); } if (!string.IsNullOrEmpty(operateType)) { sbWhere.AppendFormat(" AND OPERATETYPE='{0}'", operateType); } if (!string.IsNullOrEmpty(content)) { //sbWhere.AppendFormat(" AND CONTENT LIKE '%{0}%'", content); sbWhere.AppendFormat(" AND LOCATE('{0}', CONTENT) > 0 ", content); } if (!string.IsNullOrEmpty(startDateTime)) { sbWhere.AppendFormat(" AND OPERATEDATE>='{0}'", startDateTime); } if (!string.IsNullOrEmpty(endDateTime)) { sbWhere.AppendFormat(" AND OPERATEDATE<='{0}'", endDateTime); } if (!string.IsNullOrEmpty(fileName)) { //sbWhere.AppendFormat(" AND FILENAME LIKE '%{0}%'", fileName); sbWhere.AppendFormat(" AND LOCATE('{0}', FILENAME) > 0 ", fileName); } //DataSet dsRowsCount = MySqlHelper.GetDataSet(strSql.ToString()); //if (dsRowsCount != null && dsRowsCount.Tables.Count > 0) //{ // totalAmount = dsRowsCount.Tables[0].Rows.Count; //} totalAmount = GetTotalCount(sbWhere.ToString()); //strSql.AppendFormat(" ORDER BY OPERATEDATE DESC LIMIT {0},{1}", (pageIndex - 1) * pageSize, pageSize); strSql.Append(sbWhere).AppendFormat(" ORDER BY operatedate DESC LIMIT {0},{1}) as aa ) ", (pageIndex - 1) * pageSize, pageSize); DataSet ds = MySqlHelper.GetDataSet(strSql.ToString()); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } }