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