DateTime shouldBeGiveCoinDateTime = DateTime.Now.AddDays(-14);
return dbContext.ViewOrderForCoinCronJob.AsNoTracking().Where(x =>
x.CompletedAt <= shouldBeGiveCoinDateTime
&& x.CreatedAt >= DateTime.Now.AddYears(-1) // -> 這裡轉換mysql指令會出錯
&& x.DeletedAt == null
&& x.CoinAmount > 0
&& x.Status == (byte)ORDER_STATUS.COMPLETED
&& dbContext.PackingMain.Any(y => y.OrderId == x.Id)
&& x.IsGiveCoin != 0
&& !dbContext.CoinLog.Any(y =>
y.OrderId == x.Id
&& y.StrKey.Contains(COIN_LOG.STRING_KEY_PREFIX.ORDER_ACCUMULATION)
)
).ToList();
DateTime shouldBeGiveCoinDateTime = DateTime.Now.AddDays(-14);
DateTime createDateStartAt = DateTime.Now.AddYears(-1); // -> 先算出日期
return dbContext.ViewOrderForCoinCronJob.AsNoTracking().Where(x =>
x.CompletedAt <= shouldBeGiveCoinDateTime
&& x.CreatedAt >= createDateStartAt // -> 帶入算好的日期
&& x.DeletedAt == null
&& x.CoinAmount > 0
&& x.Status == (byte)ORDER_STATUS.COMPLETED
&& dbContext.PackingMain.Any(y => y.OrderId == x.Id)
&& x.IsGiveCoin != 0
&& !dbContext.CoinLog.Any(y =>
y.OrderId == x.Id
&& y.StrKey.Contains(COIN_LOG.STRING_KEY_PREFIX.ORDER_ACCUMULATION)
)
).ToList();
using Ds;
using Ds.Gv;
using iText.Kernel.Geom;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using NPOI.SS.Formula.Functions;
using NPOI.SS.Util;
using Su;
using System.Linq.Expressions;
namespace CallCampaign.Api
{
/// <summary>
/// 行銷活動
/// </summary>
[Route("api/call-campaign")]
[ApiController]
[SetAuthorizationFilter(Sh.AuthCode.不設限)]
public class ReserveCampaignController : Controller
{
/// <summary>
/// 取得行銷活動列表
/// </summary>
/// <param name="reserveCampaignName"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="orderByName"></param>
/// <param name="sort"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
[HttpGet("")]
public async Task<object> ListAsync([FromQuery] string reserveCampaignName = "", [FromQuery] int? currentPage = 1, [FromQuery] int? pageSize = 20, [FromQuery] string orderByName = "OrderNo", [FromQuery] string sort = "asc")
{
if (pageSize > 500)
{
pageSize = 500;
}
if (!(sort == "asc" || sort == "desc"))
{
throw new CustomException(System.Net.HttpStatusCode.BadRequest, "sort只能是asc或desc");
}
var temp = new V_ReserveCampaign().GetType().GetProperty(orderByName);
if (temp == null)
{
throw new CustomException(System.Net.HttpStatusCode.BadRequest, "不存在欄位");
}
Expression<Func<V_ReserveCampaign, bool>> q = p => p.Is_Deleted == "N"
&& (string.IsNullOrEmpty(reserveCampaignName) || (p.ReserveCampaignName != null && p.ReserveCampaignName.Contains(reserveCampaignName)))
;
if (orderByName.ToLower().Trim() != "id")
{
orderByName += " " + sort + ", id desc";
}
else
{
orderByName += " " + sort;
}
var ct = NewContext.GvContext;
var list = await ct.GetPageListAsync(q, columns: "Id, ReserveCampaignName, OrderNo, StartAt, EndAt, ModifierName, ModifyDate, CreatorName, CreateDate", page: currentPage ?? 1, pageSize: pageSize ?? 20, orderByName);
//var list = await ct.GetPageListAsync(q, page: currentPage ?? 1, pageSize: pageSize ?? 20, orderByName + " " + sort);
return list;
}
/// <summary>
/// 取得行銷活動
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
[HttpGet("get")]
public async Task<dynamic> GetAsync([FromQuery] int Id)
{
var res = await Ds.NewContext.GvContext.ReserveCampaigns.Where(r => r.Id == Id)
.FirstOrDefaultAsync();
if (res == null)
{
throw new CustomException(System.Net.HttpStatusCode.BadRequest, "查無資料 " + Id.ToString());
}
return res;
}
/// <summary>
/// 建立行銷活動
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpPost("")]
public async Task<object> CreateAsync(Dtos.CreateReserveCampaign dto)
{
var ct = NewContext.GvContext;
var res = await Models.ReserveCampaignHelper.CreateReserveCampaignAsync(ct, dto);
return res;
}
/// <summary>
/// 編輯行銷活動
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpPatch("")]
public async Task<object> UpdateAsync(Dtos.UpdateReserveCampaign dto)
{
var ct = NewContext.GvContext;
var res = await Models.ReserveCampaignHelper.UpdateReserveCampaignAsync(ct, dto);
return res;
}
/// <summary>
/// 刪除行銷活動
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpDelete("")]
public async Task<object> DeleteAsync([FromQuery] int id)
{
var res = await Ds.NewContext.GvContext.MarkDeleteAsync<Ds.Gv.ReserveCampaign>(id, Sh.ModifyInfo);
return res;
}
}
}
/// <summary>
/// 取得列表
/// </summary>
/// <param name="name"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="orderByName"></param>
/// <param name="sort"></param>
/// <returns></returns>
[HttpGet("")]
public object List([FromQuery] string name = "", [FromQuery] int? currentPage = 1, [FromQuery] int? pageSize = 20, [FromQuery] string orderByName = "OrderNo", [FromQuery] string sort = "asc")
{
return "";
}
/// <summary>
/// 取得明細資料
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
[HttpGet("get")]
public object Get([FromQuery] int id)
{
return "";
}
/// <summary>
/// 建立
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpPost("")]
public object Create(Dtos.PhysicalCheckUpType dto)
{
return "";
}
/// <summary>
/// 編輯
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpPatch("")]
public object Update(Dtos.PhysicalCheckUpType dto)
{
return "";
}
/// <summary>
/// 刪除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="CustomException"></exception>
[HttpDelete("")]
public object Delete([FromQuery] int id)
{
return 1;
}
public ConcurrentBag<V2SalesOrderGetDataList> ConcurencyGetApp91OrderDetails(List<string> TMCodes, int shopId, ref ConcurrentBag<string> orderErrMsgs)
{
#region 平行查詢
ConcurrentBag<V2SalesOrderGetDataList> result = new ConcurrentBag<V2SalesOrderGetDataList>();
ConcurrentBag<string> errs = new ConcurrentBag<string>();
ConcurrentQueue<string> TMCodeQueues = new ConcurrentQueue<string>();
TMCodes.ForEach(x => TMCodeQueues.Enqueue(x));
Action searchOrderDetail = () =>
{
if (!TMCodeQueues.IsEmpty)
{
string TMCode = string.Empty;
while (TMCodeQueues.TryDequeue(out TMCode))
{
V2SalesOrderGetReqModel reqModel = new V2SalesOrderGetReqModel()
{
ShopId = shopId,
TGCode = null,
TMCode = TMCode,
TSCode = null,
};
try
{
V2SalesOrderGet91API req = new V2SalesOrderGet91API();
var resp = req.Execute(reqModel, shopId);
if (resp.Status == "Success")
{
foreach (var l in resp.Data.List)
{
result.Add(l);
}
}
else
{
//主單編號 , 錯誤原因
errs.Add(TMCode + " , " + resp.ErrorMessage);
}
}
catch (Exception ex)
{
errs.Add(TMCode + " , " + ex.Message);
}
}
}
};
//指派Thread
Parallel.Invoke(searchOrderDetail, searchOrderDetail);
#endregion
//查詢失敗
orderErrMsgs = errs;
return result;
}
<ItemGroup>
<PackageReference Include="sqlite-net-pcl" Version="1.8.116" />
<PackageReference Include="SQLiteNetExtensions.Async" Version="2.1.0" />
<PackageReference Include="SQLitePCLRaw.bundle_green" Version="2.1.4" />
<PackageReference Include="SQLitePCLRaw.core" Version="2.1.4" />
<PackageReference Include="SQLitePCLRaw.lib.e_sqlite3" Version="2.1.4" />
<PackageReference Include="SQLitePCLRaw.provider.dynamic_cdecl" Version="2.1.4" />
<PackageReference Include="System.Data.DataSetExtensions" Version="4.5.0" />
</ItemGroup>
int tries = 0;
while (tries < Su.PgSql.maxTransactionAborted)
{
try
{
using (var scop = new System.Transactions.TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
var context = ShopBandContext.Context;
updateCount = await context.UpdateAsync<MarketDiscount>(dto, Sc.ModifyInfo,
onlyColumns: "StartAt,EndAt,NewPrice,FrontEndMemo,BackEndMemo");
scop.Complete();
break; //這個 Break 很重要
}
}
catch (Exception ex)
{
// 我發現因為是非同步模式，所以要檢查很多層的 InnerException
if (tries < Su.PgSql.maxTransactionAborted
&& ((ex.InnerException != null && ex.InnerException is Npgsql.PostgresException && ((Npgsql.PostgresException)ex.InnerException).SqlState == "40001")
|| (ex.InnerException.InnerException != null && ex.InnerException.InnerException is Npgsql.PostgresException && ((Npgsql.PostgresException)ex.InnerException.InnerException).SqlState == "40001")))
{
//隨機休息 10 ~ 20 ms, 等另一個 job 完工
System.Threading.Thread.Sleep(Su.MathUtil.Random.Next(10, 20));
tries++;
}
else
{
throw;
}
}
}