示例#1
0
    public bool UpdateProduct(Guid eventId)
    {
        var sql = @" UPDATE WP 
                            SET WP07 =  ProductStatus
                            FROM WP
                            INNER JOIN [ScheduleEvent].[dbo].ProductStatusUpdate PSU ON PSU.ProductId = WP.WP01
                            INNER JOIN [ScheduleEvent].[dbo].BulkUpdateEvent BUE ON BUE.SysId = PSU.EventId
                            WHERE BUE.SysId = @EventId
                            AND BUE.Status = 1
                            AND PSU.Status = 1 ";

        var cmd = new SqlCommand {
            CommandText = sql
        };
        var cmdUpdateStatus = new SqlCommand {
            CommandText = "UPDATE [ScheduleEvent].[dbo].BulkUpdateEvent   SET Status = 2  WHERE SysId = @EventId"
        };

        cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));
        cmdUpdateStatus.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));

        var cmdList = new List <SqlCommand>();

        cmdList.Add(cmd);
        cmdList.Add(cmdUpdateStatus);

        return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList) == 1);
    }
    private static BulkUpdateEvent GetBulkUpdateEvent(Guid id)
    {
        var sql = "SELECT SysId, EventName,ExecuteStartDate, ExecuteEndDate,EventType FROM [ScheduleEvent].[dbo].BulkUpdateEvent WHERE SysId=@id ";
        var cmd = new SqlCommand {
            CommandText = sql
        };

        cmd.Parameters.Add(SafeSQL.CreateInputParam("@id", SqlDbType.UniqueIdentifier, id));
        var dt          = SqlDbmanager.queryBySql(cmd);
        var updateEvent = new BulkUpdateEvent();

        foreach (DataRow dr in dt.Rows)
        {
            updateEvent.SysId            = Guid.Parse(dr["SysId"].ToString());
            updateEvent.EventName        = dr["EventName"].ToString();
            updateEvent.ExecuteStartDate = DateTime.Parse(dr["ExecuteStartDate"].ToString());
            updateEvent.EventType        = int.Parse(dr["EventType"].ToString());

            if (!string.IsNullOrEmpty(dr["ExecuteEndDate"].ToString()))
            {
                updateEvent.ExecuteEndDate = DateTime.Parse(dr["ExecuteEndDate"].ToString());
            }
        }

        return(updateEvent);
    }
    private SqlCommand GetWhereStatement(BulkUpdateSearch updateSearch)
    {
        var cmd = new SqlCommand();

        if (updateSearch.ExecuteStartDate.HasValue && updateSearch.ExecuteStartDate > DateTime.MinValue)
        {
            cmd.CommandText += " AND BUE.ExecuteStartDate>=@SDate";
            cmd.Parameters.Add(SafeSQL.CreateInputParam("@SDate", SqlDbType.DateTime, updateSearch.ExecuteStartDate));
        }
        if (updateSearch.ExecuteEndDate.HasValue && updateSearch.ExecuteEndDate > DateTime.MinValue)
        {
            cmd.CommandText += " AND   BUE.ExecuteStartDate<=@EDate";
            cmd.Parameters.Add(SafeSQL.CreateInputParam("@EDate", SqlDbType.DateTime, updateSearch.ExecuteEndDate));
        }
        if (updateSearch.ProductID > 0)
        {
            cmd.CommandText += " AND  ( PEU.ProductID=@ProductID OR  " +
                               "PSU.ProductID=@ProductID  OR  " +
                               "PSOU.ProductID=@ProductID  OR " +
                               "PDU.ProductID=@ProductID  OR " +
                               "PPU.ProductID=@ProductID )";
            cmd.Parameters.Add(SafeSQL.CreateInputParam("@ProductID", SqlDbType.Int, updateSearch.ProductID));
        }
        if (!string.IsNullOrEmpty(updateSearch.EventName))
        {
            cmd.CommandText += " AND  BUE.EventName LIKE '%'+ @EventName+ '%' ";
            cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventName", SqlDbType.NVarChar, updateSearch.EventName));
        }
        return(cmd);
    }
示例#4
0
        private TransactionAPIResponse SetMemberInfo(TransactionAPIResponse transactionAPIResponse)
        {
            string strSql = @"SELECT  MI02 AS MemberId, MI07 As MemberName
                            FROM    GoodMaji.dbo.CollectionOfMoney 
                            INNER JOIN GoodMaji.dbo.MemberAccount ON MA02=COM04
                            INNER JOIN GoodMaji.dbo.MemberInfo ON MI02=MA05
                            WHERE COM02=@OrderID ";
            var    cmd    = new SqlCommand {
                CommandText = strSql
            };

            cmd.Parameters.Add(SafeSQL.CreateInputParam("@OrderID", System.Data.SqlDbType.VarChar, transactionAPIResponse.OrderID));
            var dt = SqlDbmanager.queryBySql(cmd);

            if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
            {
                return(transactionAPIResponse);
            }

            var dr = dt.Rows[0];

            transactionAPIResponse.MemberId   = dr["MemberId"].ToString();
            transactionAPIResponse.MemberName = dr["MemberName"].ToString();

            return(transactionAPIResponse);
        }
示例#5
0
    private List <SqlParameter> GetUpdateParam(ProductStatusUpdate productSort)
    {
        var cmdparams = new List <SqlParameter>();

        cmdparams.Add(SafeSQL.CreateInputParam("@SysId", SqlDbType.UniqueIdentifier, productSort.SysId));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, productSort.EventId));
        cmdparams.Add(SafeSQL.CreateInputParam("@ProductId", SqlDbType.Int, productSort.ProductId));
        cmdparams.Add(SafeSQL.CreateInputParam("@ProductStatus", SqlDbType.Int, productSort.ProductStatus));
        return(cmdparams);
    }
    private List <SqlParameter> GetUpdateParam(ProductEventUpdate productEvent)
    {
        var cmdparams = new List <SqlParameter>();

        cmdparams.Add(SafeSQL.CreateInputParam("@SysId", SqlDbType.UniqueIdentifier, productEvent.SysId));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, productEvent.EventId));
        cmdparams.Add(SafeSQL.CreateInputParam("@ProductId", SqlDbType.Int, productEvent.ProductId));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventNameCN", SqlDbType.VarChar, productEvent.EventNameCN));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventNameEN", SqlDbType.VarChar, productEvent.EventNameEN));
        return(cmdparams);
    }
示例#7
0
    private List <SqlParameter> GetUpdateParam(ProductPreOrderUpdate preorder)
    {
        var cmdparams = new List <SqlParameter>();

        cmdparams.Add(SafeSQL.CreateInputParam("@SysId", SqlDbType.UniqueIdentifier, preorder.SysId));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, preorder.EventId));
        cmdparams.Add(SafeSQL.CreateInputParam("@ProductId", SqlDbType.Int, preorder.ProductId));
        cmdparams.Add(SafeSQL.CreateInputParam("@PreOrderQuantity", SqlDbType.Int, preorder.PreOrderQuantity));
        cmdparams.Add(SafeSQL.CreateInputParam("@PreOrderStatus", SqlDbType.Int, preorder.PreOrderStatus));
        return(cmdparams);
    }
示例#8
0
    private List <SqlParameter> GetUpdateParam(ProductDateUpdate productEvent)
    {
        var cmdparams = new List <SqlParameter>();

        cmdparams.Add(SafeSQL.CreateInputParam("@SysId", SqlDbType.UniqueIdentifier, productEvent.SysId));
        cmdparams.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, productEvent.EventId));
        cmdparams.Add(SafeSQL.CreateInputParam("@ProductId", SqlDbType.Int, productEvent.ProductId));
        cmdparams.Add(SafeSQL.CreateInputParam("@StartDate", SqlDbType.DateTime, productEvent.StartDate));
        cmdparams.Add(SafeSQL.CreateInputParam("@EndDate", SqlDbType.DateTime, productEvent.EndDate));
        return(cmdparams);
    }
示例#9
0
    public List <UpdateDetails> GetBulkUpdate(Guid eventId)
    {
        var sql = @"SELECT* FROM [ScheduleEvent].[dbo].ProductStatusUpdate  WHERE EventId = @EventId AND Status=1 ";
        var cmd = new SqlCommand {
            CommandText = sql
        };

        cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));
        var dt   = SqlDbmanager.queryBySql(cmd);
        var list = new List <UpdateDetails>();

        foreach (DataRow dr in dt.Rows)
        {
            var pStatus = new ProductStatusUpdate();
            pStatus.ProductId     = int.Parse(dr["ProductId"].ToString());
            pStatus.ProductStatus = int.Parse(dr["ProductStatus"].ToString());
            list.Add(pStatus);
        }
        return(list);
    }
示例#10
0
    public List <SqlCommand> EditBulkUpdate(List <UpdateDetails> updateDetails)
    {
        var cmdList  = new List <SqlCommand>();
        var sql      = "Update [ScheduleEvent].[dbo].ProductStatusUpdate set Status=0 where EventId = @EventId and status =1 ";
        var resetCmd = new SqlCommand {
            CommandText = sql
        };

        resetCmd.Parameters.Add(SafeSQL.CreateInputParam(@"EventId", SqlDbType.UniqueIdentifier, updateDetails.FirstOrDefault().EventId));
        cmdList.Add(resetCmd);
        foreach (ProductStatusUpdate status in updateDetails)
        {
            var cmd = new SqlCommand {
                CommandText = GetUpdateSQL()
            };
            cmd.Parameters.AddRange(GetUpdateParam(status).ToArray());
            cmdList.Add(cmd);
        }
        return(cmdList);
    }