Ejemplo n.º 1
0
        public bool UpdateVendorUser(VendorUser entity)
        {
            DataCommand dc = DataCommandManager.GetDataCommand("External_Update_VendorUser");

            ////2015.8.19为重置密码添加 John
            if (entity.Pwd != null)
            {
                dc = DataCommandManager.GetDataCommand("External_ResetPwd_VendorUser");
                dc.SetParameterValue("@Pwd", entity.Pwd);
                dc.SetParameterValue("@SysNo", entity.SysNo);
            }
            else
            {
                dc.SetParameterValue("@SysNo", entity.SysNo);
                dc.SetParameterValue("@VendorSysNo", entity.VendorSysNo);
                dc.SetParameterValue("@UserID", entity.UserID);
                dc.SetParameterValue("@UserName", entity.UserName);
                dc.SetParameterValue("@Email", entity.Email);
                dc.SetParameterValue("@Phone", entity.Phone);
                dc.SetParameterValue("@APIStatus", LegacyEnumMapper.ConvertValidStatus(entity.APIStatus));
                dc.SetParameterValue("@APIKey", entity.APIKey);
                dc.SetParameterValue("@Note", entity.Note);
                dc.SetParameterValue("@Status", LegacyEnumMapper.ConvertValidStatus(entity.Status));
                dc.SetParameterValue("@EditUser", entity.EditUser);
            }


            return(dc.ExecuteNonQuery() > 0);
        }
Ejemplo n.º 2
0
        public void UpdateVendorUserStatus(List <int> sysNos, ECCentral.BizEntity.ExternalSYS.ValidStatus status, string editUser)
        {
            DataCommand dc = DataCommandManager.GetDataCommand("External_Update_VendorUserStatus");

            dc.SetParameterValue("@Status", LegacyEnumMapper.ConvertValidStatus(status));
            dc.SetParameterValue("@EditUser", editUser);
            dc.ReplaceParameterValue("#SysNos#", string.Join(",", sysNos));
            dc.ExecuteNonQuery();
        }
Ejemplo n.º 3
0
        public VendorUser InsertVendorUser(VendorUser entity)
        {
            DataCommand dc = DataCommandManager.GetDataCommand("External_Insert_VendorUser");

            dc.SetParameterValue("@SysNo", entity.SysNo);
            dc.SetParameterValue("@VendorSysNo", entity.VendorSysNo);
            dc.SetParameterValue("@UserID", entity.UserID);
            dc.SetParameterValue("@UserNum", entity.UserNum);
            dc.SetParameterValue("@UserName", entity.UserName);
            dc.SetParameterValue("@Pwd", entity.Pwd);
            dc.SetParameterValue("@Email", entity.Email);
            dc.SetParameterValue("@Phone", entity.Phone);

            dc.SetParameterValue("@Note", entity.Note);
            dc.SetParameterValue("@Status", LegacyEnumMapper.ConvertValidStatus(entity.Status));
            dc.SetParameterValue("@InUser", entity.InUser);
            dc.SetParameterValue("@EditUser", entity.EditUser);
            dc.SetParameterValue("@CompanyCode", entity.CompanyCode);

            dc.ExecuteNonQuery();

            entity.SysNo = Convert.ToInt32(dc.GetParameterValue("@SysNo"));
            return(entity);
        }
Ejemplo n.º 4
0
        public DataTable UserQuery(VendorUserQueryFilter filter, out int dataCount)
        {
            if (filter.PagingInfo.SortBy != null)
            {
                string sortCondition = filter.PagingInfo.SortBy.Trim();

                Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase);
                if (match.Groups["SortColumn"].Success)
                {
                    string sortColumn = match.Groups["SortColumn"].Value;
                    string sortType   = match.Groups["SortType"].Success ?
                                        match.Groups["SortType"].Value : "DESC";
                    #region switch
                    switch (sortColumn)
                    {
                    case "SysNo":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.SysNo", sortType);
                        break;

                    case "RoleName":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.RoleName", sortType);
                        break;

                    case "Status":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.Status", sortType);
                        break;

                    case "InUser":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.InUser", sortType);
                        break;

                    case "InDate":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.InDate", sortType);
                        break;

                    case "EditUser":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.EditUser", sortType);
                        break;

                    case "EditDate":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.EditDate", sortType);
                        break;

                    case "VendorSysNo":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "a.VendorSysNo", sortType);
                        break;

                    case "VendorName":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "b.VendorName", sortType);
                        break;

                    case "VendorStatus":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "b.Status", sortType);
                        break;

                    case "Rank":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "b.Rank", sortType);
                        break;

                    case "IsConsign":
                        filter.PagingInfo.SortBy =
                            String.Format("{0} {1}", "b.IsConsign", sortType);
                        break;
                    }
                    #endregion
                }
            }
            CustomDataCommand command = DataCommandManager.
                                        CreateCustomDataCommandFromConfig("External_Query_Vendor");
            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
                       command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
            {
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "a.SysNo", DbType.Int32,
                                                          "@SysNo", QueryConditionOperatorType.Equal,
                                                          filter.SysNo);

                if (!string.IsNullOrEmpty(filter.SerialNum))
                {
                    string[] serialNum = filter.SerialNum.Split('-');

                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                              "a.VendorSysNo", DbType.Int32,
                                                              "@VendorSysNo", QueryConditionOperatorType.Equal,
                                                              serialNum[0]);
                    if (serialNum.Length > 1)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                  "a.UserNum", DbType.Int32,
                                                                  "@UserNum", QueryConditionOperatorType.Equal,
                                                                  Convert.ToInt32(serialNum[1]).ToString());
                    }
                }
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "a.UserName", DbType.String,
                                                          "@UserName", QueryConditionOperatorType.LeftLike,
                                                          filter.UserName);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "a.UserID", DbType.String,
                                                          "@UserID", QueryConditionOperatorType.LeftLike,
                                                          filter.UserID);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "a.Status", DbType.AnsiStringFixedLength,
                                                          "@Status", QueryConditionOperatorType.Equal,
                                                          LegacyEnumMapper.ConvertValidStatus(filter.UserStatus));

                if (filter.RoleSysNo.HasValue)
                {
                    builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                    ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
                                                                                                             @"SELECT TOP 1 1 
            FROM (SELECT 
                                    UserSysNo
                                FROM [IPP3].[dbo].[VendorUser_User_Role]  WITH(NOLOCK)
                                WHERE 
                                    RoleSysNo=@RoleSysNo
                        ) AS RESULT 
            WHERE 
                RESULT.[UserSysNo]=a.[SysNo]");

                    builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.OR, null, QueryConditionOperatorType.Exist,
                                                                      @"SELECT TOP 1 1 
            FROM (SELECT 
                                    UserSysNo
                                FROM [IPP3].[dbo].[VendorUser_RoleMapping]  WITH(NOLOCK)
                                WHERE 
                                    RoleSysNo=@RoleSysNo
                        ) AS RESULT 
            WHERE 
                RESULT.[UserSysNo]=a.[SysNo]");
                    command.AddInputParameter("@RoleSysNo", DbType.Int32, filter.RoleSysNo);
                    builder.ConditionConstructor.EndGroupCondition();
                }

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "a.VendorSysNo", DbType.Int32,
                                                          "@VendorSysNo", QueryConditionOperatorType.Equal,
                                                          filter.VendorSysNo);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.VendorName", DbType.String,
                                                          "@VendorName", QueryConditionOperatorType.Like,
                                                          filter.VendorName);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.ExpiredDate", DbType.DateTime,
                                                          "@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual,
                                                          filter.ExpiredDateFrom);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.ExpiredDate", DbType.DateTime,
                                                          "@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual,
                                                          filter.ExpiredDateTo);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.Contact", DbType.String,
                                                          "@Contact", QueryConditionOperatorType.LeftLike,
                                                          filter.Contact);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.Phone", DbType.String,
                                                          "@Phone", QueryConditionOperatorType.LeftLike,
                                                          filter.Phone);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.Address", DbType.String,
                                                          "@Address", QueryConditionOperatorType.LeftLike,
                                                          filter.Address);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.Status", DbType.Int32,
                                                          "@VendorStatus", QueryConditionOperatorType.Equal,
                                                          filter.VendorStatus);
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.Rank", DbType.String,
                                                          "@Rank", QueryConditionOperatorType.Equal,
                                                          filter.Rank);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "b.IsConsign", DbType.Int32,
                                                          "@IsConsign", QueryConditionOperatorType.Equal,
                                                          filter.ConsignType);

                if (filter.ManufacturerSysNo.HasValue ||
                    (!string.IsNullOrEmpty(filter.AgentLevel) &&
                     filter.AgentLevel.Trim() != "") ||
                    filter.C1SysNo.HasValue)
                {
                    string subCond = @"SELECT distinct [VendorSysNo]
                                   FROM dbo.Vendor_Manufacturer  WITH(NOLOCK) ";

                    string strWhere = string.Empty;

                    //代理品牌 二级类 三级类 代理级别
                    if (filter.ManufacturerSysNo.HasValue)
                    {
                        strWhere += "ManufacturerSysNo=@ManufacturerSysNo";
                        command.AddInputParameter("@ManufacturerSysNo",
                                                  DbType.Int32, filter.ManufacturerSysNo);
                    }
                    if (!string.IsNullOrEmpty(filter.AgentLevel) &&
                        filter.AgentLevel.Trim() != "")
                    {
                        if (strWhere != string.Empty)
                        {
                            strWhere += " AND ";
                        }
                        strWhere += "AgentLevel=@AgentLevel";
                        command.AddInputParameter("@AgentLevel",
                                                  DbType.String, filter.AgentLevel);
                    }

                    if (filter.C3SysNo.HasValue)
                    {
                        if (strWhere != string.Empty)
                        {
                            strWhere += " AND ";
                        }
                        strWhere += "C3SysNo=@C3SysNo";
                        command.AddInputParameter("@C3SysNo",
                                                  DbType.Int32, filter.C3SysNo);
                    }
                    else
                    {
                        if (filter.C2SysNo.HasValue)
                        {
                            if (strWhere != string.Empty)
                            {
                                strWhere += " AND ";
                            }
                            strWhere += "C2SysNo=@C2SysNo";
                            command.AddInputParameter("@C2SysNo",
                                                      DbType.Int32, filter.C2SysNo);
                        }
                        else
                        {
                            if (filter.C1SysNo.HasValue)
                            {
                                if (strWhere != string.Empty)
                                {
                                    strWhere += " AND ";
                                }
                                strWhere += @"C2SysNo in (SELECT 
                [Category2Sysno]  
            FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo]  WITH(NOLOCK)
            WHERE 
                [Category1Sysno]=@C1SysNo)";
                                command.AddInputParameter("@C1SysNo",
                                                          DbType.Int32, filter.C1SysNo);
                            }
                        }
                    }
                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        subCond += "WHERE status=0 and " + strWhere;
                    }
                    builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.SysNo",
                                                                      QueryConditionOperatorType.In, subCond);
                }

                command.CommandText = builder.BuildQuerySql();
                var            dt          = command.ExecuteDataTable();
                EnumColumnList enumColList = new EnumColumnList();
                enumColList.Add("IsConsign", typeof(ECCentral.BizEntity.PO.VendorConsignFlag));
                enumColList.Add("Status", typeof(ECCentral.BizEntity.ExternalSYS.ValidStatus));
                enumColList.Add("VendorStatus", typeof(VendorStatus));
                command.ConvertEnumColumn(dt, enumColList);
                dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }