protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { int userID = BLL.Util.GetLoginUserID(); if (!BLL.Util.CheckRight(userID, "SYS024BUT500605")) { Response.Write(BLL.Util.GetNotAccessMsgPage("您没有访问该页面的权限")); Response.End(); } Entities.ProjectInfo info = BLL.ProjectInfo.Instance.GetProjectInfo(CommonFunction.ObjectToInteger(ProjectID)); if (info == null) { return; } QueryYTGActivityTaskInfo query = new QueryYTGActivityTaskInfo(); query.ProjectID = CommonFunction.ObjectToInteger(ProjectID); query.LoginID = BLL.Util.GetLoginUserID(); query.TaskCBeginTime = taskcreatestart; query.TaskCEndTime = taskcreateend; query.Subbegintime = tasksubstart; query.Subendtime = tasksubend; DataTable dt = BLL.YTGActivityTask.Instance.GetYTGProjectTasksForExport(query); if (dt != null) { BLL.Util.ExportToCSV(info.Name + "的任务" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss"), dt); } } }
public DataTable GetYTGProjectTasksForExport(QueryYTGActivityTaskInfo query) { string c1 = Util.GetCaseWhenByEnum(typeof(YTGActivityPlanBuyCarTime)); string c2 = Util.GetCaseWhenByEnum(typeof(YTGActivityTaskFailReason)); string c3 = Util.GetCaseWhenByEnum(typeof(YTGActivityTaskStatus)); return(Dal.YTGActivityTask.Instance.GetYTGProjectTasksForExport(query, c1, c2, c3)); }
private string GetWhere(QueryYTGActivityTaskInfo query) { string where = ""; #region 数据权限判断 if (query.LoginID != Constant.INT_INVALID_VALUE) { where = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("pf", "lt", "BGID", "AssignUserID", query.LoginID); } #endregion if (query.TaskID != Constant.STRING_INVALID_VALUE) { where += " and lt.TaskID='" + StringHelper.SqlFilter(query.TaskID) + "'"; } if (query.Status != Constant.INT_INVALID_VALUE) { where += " and lt.Status=" + query.Status; } if (query.AssignUserID != Constant.INT_INVALID_VALUE) { where += " and lt.AssignUserID=" + query.AssignUserID; } if (query.ProjectName != Constant.STRING_INVALID_VALUE) { where += " and pf.Name like '%" + StringHelper.SqlFilter(query.ProjectName) + "%'"; } if (query.ProjectID != Constant.INT_INVALID_VALUE) { where += " and lt.ProjectID=" + query.ProjectID; } if (query.IsSuccess != Constant.INT_INVALID_VALUE) { where += " and lt.IsSuccess=" + query.IsSuccess; } //生成任务时间 if (!string.IsNullOrEmpty(query.TaskCBeginTime)) { where += " and lt.CreateTime>='" + StringHelper.SqlFilter(query.TaskCBeginTime) + " 0:0:0'"; } if (!string.IsNullOrEmpty(query.TaskCEndTime)) { where += " and lt.CreateTime<='" + StringHelper.SqlFilter(query.TaskCEndTime) + " 23:59:59'"; } //提交时间 if (!string.IsNullOrEmpty(query.Subbegintime)) { where += " and lt.LastUpdateTime>='" + StringHelper.SqlFilter(query.Subbegintime) + " 0:0:0' and lt.Status=4"; } if (!string.IsNullOrEmpty(query.Subendtime)) { where += " and lt.LastUpdateTime<='" + StringHelper.SqlFilter(query.Subendtime) + " 23:59:59' and lt.Status=4"; } return(where); }
/// 任务导出 /// <summary> /// 任务导出 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetYTGProjectTasksForExport(QueryYTGActivityTaskInfo query, string c1, string c2, string c3) { string where = GetWhere(query); string sql = @"SELECT pf.Name AS [项目名称], lt.UserName AS [姓名], CASE lt.Sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '' END AS [性别], lt.Tel AS [电话], p.AreaName AS [下单省份], c.AreaName AS [下单城市], ay.ActivityName AS [关联活动主题], lt.OrderCreateTime AS [下单时间], b1.Name AS [下单品牌], c1.Name AS [下单车型], tp.AreaName AS [试驾省份], tc.AreaName AS [试驾城市], b2.Name AS [意向品牌], c2.Name AS [意向车型], CASE lt.PBuyCarTime " + c1 + @" ELSE '' END AS [预计购车时间], CASE lt.IsSuccess WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE '' END AS [是否成功], CASE lt.FailReason " + c2 + @" ELSE '' END AS [失败原因], lt.Remark AS [备注], lt.LastUpdateTime AS [操作时间], ui.TrueName AS [所属坐席], CASE lt.Status " + c3 + @" ELSE '' END AS [任务状态] FROM dbo.YTGActivityTask lt INNER JOIN dbo.ProjectInfo pf ON lt.ProjectID = pf.ProjectID INNER JOIN dbo.YTGActivity ay ON lt.ActivityID=ay.ActivityID LEFT JOIN CRM2009.dbo.AreaInfo p ON lt.ProvinceID=p.AreaID LEFT JOIN CRM2009.dbo.AreaInfo c ON lt.CityID=c.AreaID LEFT JOIN CRM2009.dbo.AreaInfo tp ON lt.TestDriveProvinceID=tp.AreaID LEFT JOIN CRM2009.dbo.AreaInfo tc ON lt.TestDriveCityID=tc.AreaID LEFT JOIN dbo.CarSerial c1 ON lt.OrderCarSerialID=c1.CSID LEFT JOIN dbo.CarBrand b1 ON c1.BrandID=b1.BrandID LEFT JOIN dbo.CarSerial c2 ON lt.DCarSerialID=c2.CSID LEFT JOIN dbo.CarBrand b2 ON c2.BrandID=b2.BrandID LEFT JOIN CRM2009.dbo.v_userinfo ui ON lt.AssignUserID = ui.UserID WHERE 1 = 1 " + where; return(SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.Text, sql).Tables[0]); }
/// <summary> /// 按照查询条件查询 /// </summary> /// <param name="query">查询条件</param> /// <param name="order">排序</param> /// <param name="currentPage">页号,-1不分页</param> /// <param name="pageSize">每页记录数</param> /// <param name="totalCount">总行数</param> /// <returns>集合</returns> public DataTable GetYTGLeadsTask(QueryYTGActivityTaskInfo query, string order, int currentPage, int pageSize, out int totalCount) { string where = GetWhere(query); DataSet ds; SqlParameter[] parameters = { new SqlParameter("@where", SqlDbType.NVarChar, 40000), new SqlParameter("@order", SqlDbType.NVarChar, 200), new SqlParameter("@pagesize", SqlDbType.Int, 4), new SqlParameter("@indexpage", SqlDbType.Int, 4), new SqlParameter("@totalRecorder", SqlDbType.Int, 4) }; parameters[0].Value = where; parameters[1].Value = order; parameters[2].Value = pageSize; parameters[3].Value = currentPage; parameters[4].Direction = ParameterDirection.Output; ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_YTGActivityTask_Select", parameters); totalCount = (int)(parameters[4].Value); return(ds.Tables[0]); }
/// <summary> /// 按照查询条件查询 /// </summary> /// <param name="query">查询条件</param> /// <param name="order">排序</param> /// <param name="currentPage">页号,-1不分页</param> /// <param name="pageSize">每页记录数</param> /// <param name="totalCount">总行数</param> /// <returns>集合</returns> public DataTable GetYTGTask(QueryYTGActivityTaskInfo query, string order, int currentPage, int pageSize, out int totalCount) { return(Dal.YTGActivityTask.Instance.GetYTGLeadsTask(query, order, currentPage, pageSize, out totalCount)); }