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