lyfz_repos/铂金版源码/LYFZ.WeixinServiceDate/DAL/DAL_DZKJ_TaskExecutionStatistics.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace LYFZ.WeixinServiceDate.DAL
{
    public class DAL_DZKJ_TaskExecutionStatistics : BaseDataOperate
    {
        public DAL_DZKJ_TaskExecutionStatistics()
        {
            this.TableFieldNameString = "";
        }


        #region 属性和字段

        string _tableName = "tb_DZKJ_TaskExecutionStatistics";
        /// <summary>
        /// 获取数据表名
        /// </summary>
        public override string TableName
        {
            get { return _tableName; }
            set { this._tableName = value; }
        }

        /// <summary>
        /// 获取当前新的数据表模型对象
        /// </summary>
        public override object ObjModel
        {
            get
            {
                return this.CurrentModel;
            }
        }
        /// <summary>
        /// 获取当前新的MOdel
        /// </summary>
        public Model.Model_DZKJ_TaskExecutionStatistics CurrentModel
        {
            get { return new Model.Model_DZKJ_TaskExecutionStatistics(); }
        }


        string _tableFieldNameString = "";
        /// <summary>
        /// 数据表字段名数组
        /// </summary>
        public override string TableFieldNameString
        {
            get { return this._tableFieldNameString; }

            set { this._tableFieldNameString = value; }
        }

        #endregion

        #region 检查记录
        //基类已经实现
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns></returns>
        public bool Add(Model.Model_DZKJ_TaskExecutionStatistics model)
        {
            return base.Add(model);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Add(Model.Model_DZKJ_TaskExecutionStatistics model, string overlookFieldList = "ID")
        {
            return base.Add(model, overlookFieldList);
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Delete(Model.Model_DZKJ_TaskExecutionStatistics model)
        {
            return base.Delete(model.ID);
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.Model_DZKJ_TaskExecutionStatistics model)
        {
            return base.Update(model);
        }

        /// <summary>
        /// 根据筛选字段和SQL筛选运算符号更新数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Update(Model.Model_DZKJ_TaskExecutionStatistics model, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID")
        {
            return base.Update(model, filterFieldName, operators, overlookFieldList);
        }
        #endregion

        #region 查询数据
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.Model_DZKJ_TaskExecutionStatistics GetModel(long ID)
        {
            return DataRowToModel(GetDataRow(ID));
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public Model.Model_DZKJ_TaskExecutionStatistics DataRowToModel(DataRow row)
        {
            return DataRowToModelObject(row) as Model.Model_DZKJ_TaskExecutionStatistics;
        }

        public bool UpdateLatestLoginTime(string Account, string ip, string LatestLoginTime)
        {
            string updateSql = "update [tb_DZKJ_CustomerAccount] set ip='" + ip + "',LatestLoginTime='" + LatestLoginTime + "' where Account='" + Account + "'";
            if (LYFZ.Helper.SQLHelper.ExecuteSql(updateSql) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取点赞科技爆客系统功能执行统计
        /// </summary>
        /// <param name="type"></param>
        /// <param name="time"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public DataTable GetTaskExecutionStatisticDataTable(int type, DateTime time, string keyword)
        {
            try
            {
                string ExecutionTimeString = "";
                switch (type)
                {
                    case 0:
                        ExecutionTimeString = "ExecutionTime>='" + Convert.ToDateTime(time.Year + "-01-01").ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + Convert.ToDateTime(time.Year + "-01-01").AddYears(1).ToString("yyyy-MM-dd") + "'";
                        break;
                    case 1:
                        ExecutionTimeString = "ExecutionTime>='" + Convert.ToDateTime(time.ToString("yyyy-MM") + "-01").ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + Convert.ToDateTime(time.ToString("yyyy-MM") + "-01").AddMonths(1).ToString("yyyy-MM-dd") + "'";
                        break;
                    case 2:
                        ExecutionTimeString = "ExecutionTime>='" + time.ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + time.AddDays(1).ToString("yyyy-MM-dd") + "'";
                        break;
                    default:
                        ExecutionTimeString = "ExecutionTime>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + "'";
                        break;
                }
                string whereString = "";
                if (!string.IsNullOrEmpty(keyword.Trim()))
                {
                    whereString = "where [tb_DZKJ_CustomerAccount].Account like '%" + keyword + "%' or [CompanyName] like '%" + keyword + "%'";
                }


                string sql = " with t as"
                            + "  ("
                            + "   SELECT  [Account]" 
                            + "     ,sum([ExecutionCount]) as ExecutionCount "
                            + " FROM [dbo].[tb_DZKJ_TaskExecutionStatistics] "
                            + " where " + ExecutionTimeString + " "
                            + " group by [Account]"
                            + " )"
                            + " select top 5000 [CompanyName],[tb_DZKJ_CustomerAccount].Account,ManagerProgramVersion,SmallEifVersion,ExecutionCount "
                            + " from [tb_DZKJ_CustomerAccount] "
                            + " left join "
                            + " t on [tb_DZKJ_CustomerAccount].Account=t.Account  "
                            + whereString + "   order by ExecutionCount desc";

                return LYFZ.Helper.SQLHelper.Query(sql).Tables[0];
            }
            catch {
                return null;
            }
        }

        public DataTable GetTaskExecutionOneDataTable(int type, DateTime time, string Account = "")
        {
            try
            {
                string ExecutionTimeString = "";
                switch (type)
                {
                    case 0:
                        ExecutionTimeString = " and  (ExecutionTime>='" + Convert.ToDateTime(time.Year + "-01-01").ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + Convert.ToDateTime(time.Year + "-01-01").AddYears(1).ToString("yyyy-MM-dd") + "')";
                        break;
                    case 1:
                        ExecutionTimeString = " and  (ExecutionTime>='" + Convert.ToDateTime(time.ToString("yyyy-MM") + "-01").ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + Convert.ToDateTime(time.ToString("yyyy-MM") + "-01").AddMonths(1).ToString("yyyy-MM-dd") + "')";
                        break;
                    case 2:
                        ExecutionTimeString = " and  (ExecutionTime>='" + time.ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + time.AddDays(1).ToString("yyyy-MM-dd") + "')";
                        break;
                    default:
                        ExecutionTimeString = " and  (ExecutionTime>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + "')";
                        break;
                }

                string sql = "with t as"
                            + " ("
                            + "   SELECT  "
                            + "  max([Account]) as Account "
                            + " ,MAX(Features) as Features "
                            + "   ,sum([ExecutionCount]) as ExecutionCount "
                            + " FROM [dbo].[tb_DZKJ_TaskExecutionStatistics] "
                            + "   where  [Account]='" + Account + "' " + ExecutionTimeString+" "
                            + "  group by GroupType "
                            + "  ) "
                            + "  select Account,Features,ExecutionCount "
                            + " from t " 
                            + " order by ExecutionCount desc ";

                return LYFZ.Helper.SQLHelper.Query(sql).Tables[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion


        /// <summary>
        /// 更新点赞营销功能使用次数统计
        /// </summary>
        /// <param name="_Account"></param>
        /// <param name="_Features"></param>
        /// <returns></returns>
        public static int UpdateTaskExecutionStatistics(string _Account, string _Features)
        {
            /*
               Declare @return_value nvarchar(50)
            EXEC	@return_value = [dbo].[PROCE_UpdateTaskExecutionStatistics]
		    @ID = 3,
		    @DayKey = N'md512345655553',
		    @Account = N'test',
		    @Features = N'转发朋友圈',
		    @GroupType = N'md333333'
             */
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.BigInt),
                    new SqlParameter("@DayKey" , SqlDbType.VarChar , 50),    
                    new SqlParameter("@Account", SqlDbType.VarChar , 50 ),
                    new SqlParameter("@Features", SqlDbType.VarChar, 100),
                    new SqlParameter("@GroupType", SqlDbType.VarChar , 50 )
                    };
                long _ID = LYFZ.WinAPI.CustomPublicMethod.GenerateId();
                string _DayKey = LYFZ.WinAPI.SDKSecurity.MD5Encrypt(_Account.Trim() + _Features.Trim() + DateTime.Now.ToString("yyyy-MM-dd"));
                string _GroupType = LYFZ.WinAPI.SDKSecurity.MD5Encrypt(_Account.Trim() + _Features.Trim());
                parameters[0].Value = _ID;
                parameters[1].Value = _DayKey;
                parameters[2].Value = _Account.Trim();
                parameters[3].Value = _Features.Trim();
                parameters[4].Value = _GroupType;
                int retRowCount = 0;
                LYFZ.Helper.SQLHelper.RunProcedure("PROCE_UpdateTaskExecutionStatistics", parameters, out retRowCount);
                return retRowCount;
            }
            catch {
                return 0;
            }
        }
        /// <summary>
        /// 获取任务执行次数
        /// </summary>
        /// <param name="_Account"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static DataTable GetTaskExecutionStatistics(string _Account, DateTime t)
        {
            try
            {
                string sql = "select max([Features]) as Features,sum([ExecutionCount]) as ExecutionCount from [tb_DZKJ_TaskExecutionStatistics] "
                          + " where Account='" + _Account + "' and (ExecutionTime>='" + t.ToString("yyyy-MM-dd") + "' and ExecutionTime <'" + t.AddDays(1).ToString("yyyy-MM-dd") + "')"
                          + " group by GroupType";
                DataSet ds = LYFZ.Helper.SQLHelper.Query(sql);
                return ds.Tables[0];
            }
            catch {
                return null;
            }
        }

        #region 数据分页
        //基类已实现相关方法
        #endregion
    }
}