public void Execute(IJobExecutionContext context)
{
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis开始运行...");
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var datetime = DateTime.Now.ToShortDateString();
var et = DateTime.Now.AddDays(1).ToShortDateString();
var state = $"{ OrderStatusEnum.待评价.GetHashCode()},{OrderStatusEnum.已付款.GetHashCode()},{OrderStatusEnum.已确认.GetHashCode()}, {OrderStatusEnum.已完成.GetHashCode()},{ OrderStatusEnum.待收货.GetHashCode()}";
var sql = $"select OrderId,Phone from Orders where OrderState in ({state}) and OrderAddTime>='{datetime}' and OrderAddTime <'{et}' and type=2";
var idval = RedisHelper.Get("FirstOrderPhone_proint");
if (string.IsNullOrEmpty(idval))
{
sql = $"select OrderId,Phone from Orders where OrderState in ({state}) and type=2";
}
var orderTable = db.ExecuteTable(sql);
var temp = "";
var phone = "";
var list = new List <string>();
if (orderTable.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(idval))
{
list.AddRange(idval.Split(','));
}
for (int i = 0; i < orderTable.Rows.Count; i++)
{
phone = orderTable.Rows[i]["Phone"].ToString();
if (string.IsNullOrEmpty(phone))
{
continue;
}
temp = $"{phone}_{orderTable.Rows[i]["OrderId"].ToString()}";
//检查是否已经把改手机号放到redis了，如果放到了就更新后面的ID值
if (list.Where(l => l == temp).Any())
{
list.RemoveAll(l => l == temp);
list.Add(temp);
}
else
{
list.Add(temp);
}
}
idval = string.Join(",", list.ToArray());
RedisHelper.Set("FirstOrderPhone_proint", idval);
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis成功,一共插入{list.Count},当前插入{orderTable.Rows.Count}");
}
else
{
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis无数据");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis出现异常{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"InsertFirstOrderJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
//处理业务逻辑 收花时间2天后子订单状态改成完成状态
var day = 3;//判断条件几天后修改
var childState = OrderChildStatusEnum.送货中.GetHashCode();
var sql = $"select Id from OrderChild where Status={childState} and DATEDIFF(DAY, sendtime, GETDATE()) >= {day}";
var orderIdTable = db.ExecuteTable(sql);
var OrderChildId = 0;
var OrderChildIdStr = string.Empty;
var orderStatusSql = string.Empty;
if (orderIdTable.Rows.Count > 0)
{
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
OrderChildId = orderIdTable.Rows[i]["Id"].ToString().ToInt32();
orderStatusSql += $"insert into OrderChildStatus (LastStatus,CurrentStatus,ChangeTime,OrderChild_Id)values(3,{OrderChildStatusEnum.完成.GetHashCode()},GETDATE(),{OrderChildId})";
OrderChildIdStr = OrderChildIdStr == "" ? OrderChildId.ToString() : $"{OrderChildIdStr},{OrderChildId}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
//修改主订单状态为待评价
var orderChildUpdateSql = $"update OrderChild set Status={OrderChildStatusEnum.完成.GetHashCode()} where Id in ({OrderChildIdStr})";
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入子订单状态改变记录
}
var orderChildCount = 0;
if (orderIdTable.Rows.Count > 0)
{
orderChildCount = db.ExecuteSql(orderChildUpdateSql);
}
_logger.InfoFormat($"任务名:OrderChildCompleteJob-批量修改子订单状态为已完成成功,{orderChildCount}个子订单.");
}
else
{
_logger.InfoFormat("任务名:OrderChildCompleteJob-修改订单状态为子订单状态为已完成-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"任务名:OrderChildCompleteJob-修改子订单状态为完成报错-{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderChildCompleteJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var sql = $"select o.OrderSerialNumber,o.ReciveTime,o.OrderId,o.OrderState,o.Person,o.Phone,o.Province,o.City,o.Area,o.AddressLongLat from OrderGoodss og join Orders o on og.OrderId=o.OrderId where o.type=2 and o.OrderState in(2,3,4,5) and not exists(select Orders_OrderId from OrderChild where Orders_OrderId=o.OrderId)";
var orderIdTable = db.ExecuteTable(sql);
var insertSql = "insert into OrderChild(SendTime,Person,Phone,Province,City,Area,AddressLongLat,Status,Times,Orders_OrderId) values";
var orderStr = "";
if (orderIdTable.Rows.Count > 0)
{
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
//var SendTime = "2017/2/14 0:00:00";
var Person = orderIdTable.Rows[i]["Person"].ToString();
var Phone = orderIdTable.Rows[i]["Phone"].ToString();
var Province = orderIdTable.Rows[i]["Province"].ToString();
var City = orderIdTable.Rows[i]["City"].ToString();
var Area = orderIdTable.Rows[i]["Area"].ToString();
var AddressLongLat = orderIdTable.Rows[i]["AddressLongLat"].ToString();
var Status = 1;
var Orders_OrderId = orderIdTable.Rows[i]["OrderId"].ToString().ToInt32();
var ReciveTime = orderIdTable.Rows[i]["ReciveTime"].ToString().ToDateTime();
orderStr += orderIdTable.Rows[i]["OrderSerialNumber"].ToString() + ",";
if (i == 0)
{
insertSql += $"('{ReciveTime}','{Person}','{Phone}','{Province}','{City}','{Area}','{AddressLongLat}',{Status},1,{Orders_OrderId})";
}
else
{
insertSql += $",('{ReciveTime}','{Person}','{Phone}','{Province}','{City}','{Area}','{AddressLongLat}',{Status},1,{Orders_OrderId})";
}
//for (int j = 2; j < 5; j++)
//{
// ReciveTime = ReciveTime.AddDays(7);
// insertSql += $",('{ReciveTime}','{Person}','{Phone}','{Province}','{City}','{Area}','{AddressLongLat}',{Status},{j},{Orders_OrderId})";
//}
}
var count = db.ExecuteSql(insertSql);
_logger.InfoFormat($"当前共处理{count}个订单共{orderIdTable.Rows.Count}个异常订单，订单号:{orderStr}");
}
else
{
_logger.InfoFormat("没有需要处理的异常订单");
}
}
catch (Exception ex)
{
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"BugJobs错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
_logger.InfoFormat($"WangdianGuanJiaSendOrderJob-网店管家发货同步开始...");
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var datetime = DateTime.Now.AddDays(-1).ToShortDateString();
var et = DateTime.Now.AddDays(2).ToShortDateString();
var sql = $@"select * from (
select oc.Times,type,o.lat,expressNumber,expressName,oce.Number,oce.Company from Orders o left join orderchild oc on o.orderid = oc.orders_orderid left join OrderChildExpress oce on oc.Id = oce.OrderChild_Id where ShopId in(4) and orderstate > 2 and OrderState <7 and o.ReciveTime >='{datetime}' and o.ReciveTime <'{et}')
tempTab where (times = 1 or Times is null)";
var table = db.ExecuteTable(sql);
var order = string.Empty;
for (int i = 0; i < table.Rows.Count; i++)
{
var lat = string.IsNullOrEmpty(table.Rows[i]["lat"].ToString()) ? "" : table.Rows[i]["lat"].ToString();
var expressNumber = string.IsNullOrEmpty(table.Rows[i]["expressNumber"].ToString()) ? table.Rows[i]["Number"].ToString() : table.Rows[i]["expressNumber"].ToString();
var expressName = string.IsNullOrEmpty(table.Rows[i]["expressName"].ToString()) ? table.Rows[i]["Company"].ToString() : table.Rows[i]["expressName"].ToString();
if (string.IsNullOrEmpty(lat))
{
continue;
}
if (string.IsNullOrEmpty(expressNumber))
{
continue;
}
if (string.IsNullOrEmpty(expressName))
{
continue;
}
if (string.IsNullOrEmpty(order))
{
order = $"{lat}_{expressNumber}_{expressName}";
}
else
{
order += $"&{lat}_{expressNumber}_{expressName}";
}
}
TaoBaoOperHelper.sendConfim(order);//网店管家发货同步
_logger.InfoFormat($"WangdianGuanJiaSendOrderJob-网店管家发货同步成功。。。");
}
catch (Exception ex)
{
_logger.InfoFormat($"WangdianGuanJiaSendOrderJob-网店管家发货同步异常...");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"WangdianGuanJiaSendOrderJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
_logger.InfoFormat($"自动任务ConsolidatedOrderJob-组合订单数据开始运行...");
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var sql = $"select * from (select addresslonglat, count(1) b, value = stuff((SELECT ',' + convert(varchar, Id) FROM[tinghua].[dbo].[PrintOrderSet] AS t WHERE t.addresslonglat = p.addresslonglat and t.State = 1 and t.type in (6,7) and t.Person = p.Person and t.Phone = p.Phone and t.Province = p.Province and t.City = p.City and t.Area = p.Area FOR xml path('')), 1, 1, '') from[tinghua].[dbo].[PrintOrderSet] p where type in (6,7) and state = 1 group by Person,Phone,Province,City,Area,addresslonglat) a where a.b > 1";
var resultTable = db.ExecuteTable(sql);
if (resultTable.Rows.Count > 0)
{
List <PrintOrderSet> printOrderSetList = new List <PrintOrderSet>(); //除开第一条数据之外的数据集合
var ids = ""; //存放非第一条数据的订单ID
var count = 0;
for (int i = 0; i < resultTable.Rows.Count; i++)
{
PrintOrderSet newOrder = new PrintOrderSet();//第一条数据
var value = Convert.ToString(resultTable.Rows[i]["value"]);
var sonSql = $"select * from PrintOrderSet where Id in({value})";
var orderTable = db.ExecuteTable(sonSql);
for (int j = 0; j < orderTable.Rows.Count; j++)
{
if (j > 0)
{
PrintOrderSet order = new PrintOrderSet()
{
Id = Convert.ToInt32(orderTable.Rows[j]["Id"]),
State = Convert.ToInt32(orderTable.Rows[j]["State"]),
GoodssTitle = Convert.ToString(orderTable.Rows[j]["GoodssTitle"]),
NoteStr = Convert.ToString(orderTable.Rows[j]["NoteStr"])
};
printOrderSetList.Add(order);
}
else
{
newOrder.Id = Convert.ToInt32(orderTable.Rows[j]["Id"]);
newOrder.State = Convert.ToInt32(orderTable.Rows[j]["State"]);
newOrder.GoodssTitle = Convert.ToString(orderTable.Rows[j]["GoodssTitle"]);
newOrder.NoteStr = Convert.ToString(orderTable.Rows[j]["NoteStr"]);
}
}
if (printOrderSetList.Count > 0)
{
var noteStr = newOrder.GoodssTitle.Split('#').ToList(); //第一条
var newGoodssTitle = ""; //最后结果（商品名称与数量组成的字符串）
List <Product> productList = new List <Product>(); //最后得出来的商品和对应商品数量的集合，
List <string> str = new List <string>(); //累计叠加的商品名称，用于循环比较商品名称是否再这个集合中从而确定productList集合中是否已经存在了此商品
#region 第一条数据
if (noteStr.Count > 0)
{
noteStr.ForEach(p => //第一条
{
var od = p.Split('*'); //第一条
productList = Calculation(productList, ref str, od);
});
}
#endregion
#region 其它条数据
foreach (var mo in printOrderSetList) //其它条数据组成的集合
{
ids = ids + $"{mo.Id},";
if (!string.IsNullOrEmpty(mo.GoodssTitle))
{
var newNoteStr = mo.GoodssTitle.Split('#').ToList(); //第n条商品名称和数据量
newNoteStr.ForEach(o => //第n条
{
var newOd = o.Split('*'); //第n条
List <Product> newProductList = Calculation(productList, ref str, newOd);
});
}
}
#endregion
if (productList.Count > 0)
{
foreach (var model in productList)
{
newGoodssTitle = newGoodssTitle + $"{model.Name}*{model.Number}#";
}
}
if (!string.IsNullOrEmpty(newGoodssTitle))
{
newOrder.GoodssTitle = newGoodssTitle;
}
}
if (!string.IsNullOrEmpty(newOrder.GoodssTitle) && !string.IsNullOrEmpty(ids))
{
if (string.IsNullOrEmpty(newOrder.NoteStr))
{
newOrder.NoteStr = ids;
}
else
{
newOrder.NoteStr = newOrder.NoteStr + "," + ids;
}
if (!string.IsNullOrEmpty(newOrder.NoteStr))
{
newOrder.NoteStr = newOrder.NoteStr.TrimEnd(',');
}
newOrder.GoodssTitle = newOrder.GoodssTitle.TrimEnd('#').TrimEnd(',');
var goodssList = newOrder.GoodssTitle.Split('#').ToList();
var GoodssCount = goodssList.Count;
if (goodssList.Count == 1)
{
GoodssCount = int.Parse(goodssList[0].Split('*')[1]);
}
var IsSpecial = 0;
if (GoodssCount > 1)
{
IsSpecial = 1;
}
var updateSql = $"update [tinghua].[dbo].[PrintOrderSet] set [GoodssTitle]='{newOrder.GoodssTitle}',IsSpecial={IsSpecial},GoodssCount={GoodssCount},[NoteStr]='{newOrder.NoteStr}'where [Id] = {newOrder.Id};";
if (!string.IsNullOrEmpty(ids))
{
ids = ids.TrimEnd(',');
updateSql = updateSql + $"update [tinghua].[dbo].[PrintOrderSet] set [state]=-1,[MergeType]=0 where [Id] in ({ids});";
}
db.ExecuteSql(updateSql);
}
printOrderSetList.Clear();
ids = string.Empty;
count++;
}
_logger.InfoFormat($"ConsolidatedOrderJob-组合订单数据到打单记录表执行完毕共修改{count}条数据...");
}
else
{
_logger.InfoFormat($"自动任务ConsolidatedOrderJob-组合订单数据读取数据时无数据");
}
}
catch (Exception e)
{
_logger.InfoFormat($"ConsolidatedOrderJob-读取订单数据到打单记录表出现异常...{e.Message}");
var fullMesage = ErrorHelper.FullException(e);
_errLog.ErrorFormat($"ConsolidatedOrderJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
Sql.SqlServerClient <OrderStatus> OrderStatusDB = Sql.SqlServerClientSingleton <OrderStatus> .Instance;
Sql.SqlServerClient <OrderChildStatus> OrderChildStatusDB = Sql.SqlServerClientSingleton <OrderChildStatus> .Instance;
//处理业务逻辑 收花时间2天后订单状态改成待评价状态
var day = 30;//判断条件几天后修改
var orderState = OrderStatusEnum.待收货.GetHashCode();
var childState = OrderChildStatusEnum.完成.GetHashCode();
var sql = $"select o.OrderId,o.Type,o.OrderState from Orders o left join OrderChild oc on o.OrderId=oc.Orders_OrderId where((select COUNT(*) times from OrderChild where Orders_OrderId = o.OrderId) = (select COUNT(*) times from OrderChild where Orders_OrderId = o.OrderId and Status={childState})) and o.OrderState = {orderState} and ((o.Type!={OrderTypeEnum.包月.GetHashCode()} and DATEDIFF(DAY, case when o.ReciveTime is null then DATEADD(DAY,2,o.OrderAddTime) ELSE o.ReciveTime END, GETDATE()) >= {day}) or (oc.Times = (select top 1 times from OrderChild where Orders_OrderId = o.OrderId order by SendTime desc) and oc.Status = {childState} and DATEDIFF(DAY, oc.SendTime, GETDATE()) >= {day} and o.Type={OrderTypeEnum.包月.GetHashCode()}))";
var orderIdTable = db.ExecuteTable(sql);
if (orderIdTable.Rows.Count > 0)
{
var ids = new List <int>();
var orderIdStr = string.Empty;
var OrderChildStatusSql = string.Empty; //需要插入到子订单状态表的sql
var orderStatusSql = string.Empty; //需要插入到主订单状态表的sql
int orderid;
int Type;
int state;
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
orderid = orderIdTable.Rows[i]["OrderId"].ToString().ToInt32();
Type = orderIdTable.Rows[i]["Type"].ToString().ToInt32();
state = orderIdTable.Rows[i]["OrderState"].ToString().ToInt32();
orderStatusSql += $"insert into OrderStatus (LastStatus,CurrentStatus,ChangeTime,Reason,Orders_OrderId)values({state},{OrderStatusEnum.待评价.GetHashCode()},GETDATE(),'自动任务修改',{orderid})";
ids.Add(orderid);
orderIdStr = orderIdStr == "" ? orderid.ToString() : $"{orderIdStr},{orderid}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
j = 0;
//获取所有子订单，用于插入状态改变记录
var childSql = $"select Orders_OrderId,Id,Status from OrderChild where Orders_OrderId in ({orderIdStr}) ";
if (!string.IsNullOrEmpty(orderIdStr))
{
var childTable = db.ExecuteTable(childSql);
for (int i = 0; i < childTable.Rows.Count; i++)
{
j++;
orderid = childTable.Rows[i]["Id"].ToString().ToInt32();
state = childTable.Rows[i]["Status"].ToString().ToInt32();
OrderChildStatusSql += $"insert into OrderChildStatus (LastStatus,CurrentStatus,ChangeTime,OrderChild_Id)values({state},{OrderChildStatusEnum.完成.GetHashCode()},GETDATE(),{orderid})";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(OrderChildStatusSql);
OrderChildStatusSql = string.Empty;
}
}
}
//修改主订单状态为待评价
var orderUpdateSql = $"update Orders set OrderState={OrderStatusEnum.待评价.GetHashCode()} where OrderId in ({orderIdStr})";
var orderChildUpdateSql = $"update OrderChild set Status={OrderChildStatusEnum.完成.GetHashCode()} where Orders_OrderId in ({orderIdStr})";
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
if (!string.IsNullOrEmpty(OrderChildStatusSql))
{
db.ExecuteSql(OrderChildStatusSql);//插入子订单状态改变记录
}
var orderCount = 0;
var orderChildCount = 0;
if (orderIdTable.Rows.Count > 0)
{
orderCount = db.ExecuteSql(orderUpdateSql);
orderChildCount = db.ExecuteSql(orderChildUpdateSql);
}
_logger.InfoFormat($"批量修改订单状态为待评价成功,本次修改了{orderCount}个主订单,{orderChildCount}个子订单.");
}
else
{
_logger.InfoFormat("修改订单状态为待评价-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"修改订单状态为待评价报错-{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderConfirmJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
_logger.InfoFormat($"OrderIsGiveJob-处理赠送花瓶开始执行...");
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
#region 把下单并且发货的用户的手机号放入redis里面去，用于后续做是否首次下单判断.
var state = $"{ OrderStatusEnum.待评价.GetHashCode()},{OrderStatusEnum.已付款.GetHashCode()},{OrderStatusEnum.已确认.GetHashCode()}, {OrderStatusEnum.已完成.GetHashCode()},{ OrderStatusEnum.待收货.GetHashCode()}";
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis开始运行...");
try
{
var datetimes = DateTime.Now.AddDays(-35).ToShortDateString();
var et = DateTime.Now.AddDays(1).ToShortDateString();
var sqls = $"select OrderId,Phone from Orders where OrderState in ({state}) and type=2";
var idval = RedisHelper.Get("FirstOrderPhone_proint");
var orderTable = db.ExecuteTable(sqls);
var temp = "";
var phone = "";
var list = new List <string>();
if (orderTable.Rows.Count > 0)
{
for (int i = 0; i < orderTable.Rows.Count; i++)
{
phone = orderTable.Rows[i]["Phone"].ToString().Trim();
if (string.IsNullOrEmpty(phone))
{
continue;
}
if (phone.Length > 11)//如果手机号码大于了就提取正确的手机号码。
{
var ary = System.Text.RegularExpressions.Regex.Matches(phone, @"1[345789]\d{9}").Cast <System.Text.RegularExpressions.Match>().Select(t => t.Value).ToArray();
if (ary.Count() > 0)
{
phone = ary[0];
}
}
temp = $"{phone}_{orderTable.Rows[i]["OrderId"].ToString()}";
//检查是否已经把改手机号放到redis了，如果放到了就更新后面的ID值
if (list.Where(l => l == temp).Any())
{
list.RemoveAll(l => l == temp);
list.Add(temp);
}
else
{
list.Add(temp);
}
}
idval = string.Join(",", list.ToArray());
RedisHelper.Set("FirstOrderPhone_proint", idval);
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis成功,一共插入{list.Count},当前插入{orderTable.Rows.Count}");
}
else
{
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis无数据");
}
}
catch (Exception e)
{
_logger.InfoFormat($"自动任务InsertFirstOrderJob-读取订单数据到redis出现异常{e.Message}");
}
#endregion
var orderIsGiveTable = db.ExecuteTable("select p.id,p.GoodAttrId,o.phone,o.userid,o.OrderId,p.OrderChild_Id,o.OrderSerialNumber from printorderset p,Orders o where p.orders_orderid=o.orderid and Times =1 and State=1 and isgive!=1");
if (orderIsGiveTable.Rows.Count > 0)
{
var orderidList = new List <string>();
var userIdList = new List <string>();
for (int i = 0; i < orderIsGiveTable.Rows.Count; i++)
{
orderidList.Add(orderIsGiveTable.Rows[i]["OrderId"].ToString());
userIdList.Add(orderIsGiveTable.Rows[i]["phone"].ToString());
}
var userIds = string.Join(",", userIdList.Distinct());
var orderIds = string.Join(",", orderidList.Distinct());
//获取所有需要赠送花瓶的订单
var GiveOrder = HttpHelper.Post("http://admin.listenflower.com/Open/OrderGive", new { Phone = userIds, orderid = orderIds }).Split(',').ToArray();
var giveList = new List <int>();
var keys = string.Empty;
var sql = string.Empty;
for (int i = 0; i < orderIsGiveTable.Rows.Count; i++)
{
keys = $"{orderIsGiveTable.Rows[i]["userid"].ToString()}{orderIsGiveTable.Rows[i]["OrderId"].ToString()}{orderIsGiveTable.Rows[i]["OrderChild_Id"].ToString()}";
if (GiveOrder.Contains(keys))
{
giveList.Add(orderIsGiveTable.Rows[i]["id"].ToString().ToInt32());
}
else
{
var bl = false; //表示该订单里面是否购买了mini包月商品。
var miniGoodssid = RedisHelper.Get($"TingHua_miniGoodssid"); //mini商品ID。配送方式需要改变一下。一周送2束 隔周配送
miniGoodssid += $",{RedisHelper.Get($"TingHua_ZhuTiGoodssid")}";
var GoodAttrId = string.IsNullOrEmpty(orderIsGiveTable.Rows[i]["GoodAttrId"].ToString()) ? "" : orderIsGiveTable.Rows[i]["GoodAttrId"].ToString();
var goodAttrIdArr = Array.ConvertAll <string, int>(GoodAttrId.Split('#'), l => int.Parse(l));
if (!string.IsNullOrEmpty(miniGoodssid))
{
var arr1 = Array.ConvertAll <string, int>(miniGoodssid.Split(',').ToArray(), l => int.Parse(l));
foreach (var item in goodAttrIdArr)
{
if (arr1.Contains(item))
{
bl = true;
break;
}
}
}
if (bl)
{
sql = $"select count(1) miniCount from Orders o,OrderGoodss og where o.OrderId=og.OrderId and og.goodsid in ({miniGoodssid}) and o.orderstate in({state}) and o.Phone='{orderIsGiveTable.Rows[i]["Phone"].ToString()}' and o.OrderId <{orderIsGiveTable.Rows[i]["OrderId"]}";
var dS = db.ExecuteTable(sql);
if (dS.Rows[0]["miniCount"].ToString().ToInt32() == 0)
{
giveList.Add(orderIsGiveTable.Rows[i]["id"].ToString().ToInt32());
}
}
}
}
if (giveList.Count > 0)
{
var printorderIds = string.Join(",", giveList);
var giveSql = $"update printorderset set IsGive=1 where id in ({printorderIds})";
var count = db.ExecuteSql(giveSql);
_logger.InfoFormat($"OrderIsGiveJob-处理赠送花瓶成功共修改了{count}数据...");
}
else
{
_logger.InfoFormat($"OrderIsGiveJob-处理赠送花瓶暂无数据...");
}
}
}
catch (Exception ex)
{
_logger.InfoFormat($"OrderIsGiveJob-处理赠送花瓶出现异常...");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderIsGiveJob错误信息;{fullMesage}");
}
}
public bool InserOrder(NewTradeModel model, Sql.SqlServerClient <Orders> db, NewYouZanModel YouZanConfig, int OrderStatus, int OrderChildStatus, string ZhuangTai)
{
string sql = string.Empty;
DateTime ReciveTime = DateTime.Now;
int OrderID = 0;
StringBuilder sqls = new StringBuilder();
for (int j = 0; j < model.Ordes.Count; j++)
{
if (string.IsNullOrEmpty(model.Ordes[j].ID))
{
continue;
}
var nids = new List <int>();
var goodsDaylist_IDAttr = model.Ordes[j].ID.Split('&');
var dicAttr = new Dictionary <int, int>();//用来保存skuID后面数量的键值对 比如 342*2&342*3_1,
for (int kl = 0; kl < goodsDaylist_IDAttr.Length; kl++)
{
var goodsDaylist_IDAttrs = goodsDaylist_IDAttr[kl].Split('*');
nids.Add(goodsDaylist_IDAttrs[0].ToInt32());
if (goodsDaylist_IDAttrs.Length > 1)
{
dicAttr.Add(goodsDaylist_IDAttrs[0].ToInt32(), goodsDaylist_IDAttrs[1].ToInt32());
}
else
{
dicAttr.Add(goodsDaylist_IDAttrs[0].ToInt32(), 1);
}
}
string nid = string.Join(",", nids);
sql = $"SELECT gs.Id,bs.Name,bs.AttributeShow_Id,gs.type,GoodsId,ShopId,CategoryId,CategoryFatherId,GoodsTitle,GoodsTitleSub,GoodsImageUrl,GoodsPrice"
+ " FROM GoodsAttributeSet AS gs INNER JOIN AttributeSet as bs ON gs.Attribute_Id = bs.Id "
+ " inner join AttributeShowSet ss on ss.Id = bs.AttributeShow_Id INNER JOIN Goodss AS g ON ss.Goodss_GoodsId = g.GoodsId WHERE gs.Id in (" + nid + ")";
var goodInfoTable = db.ExecuteTable(sql);
if (goodInfoTable == null || goodInfoTable.Rows.Count < 1)
{
OrderStatusList.Add(new OrderStatus
{
GoodsID = nid,
OrderID = model.ID,
Status = false
});
continue;
}
//数据库读取商品信息
int type = 0;
var orderType = OrderTypeEnum.单束鲜花.GetHashCode();
try
{
DataRow goodItem = null;// goodInfoTable.Rows[0];
if (goodInfoTable.Select("CategoryFatherId in (25)").Count() > 0)
{
type = 25;
goodItem = goodInfoTable.Select("CategoryFatherId in (25)")[0];
}
else if (goodInfoTable.Select("CategoryFatherId in (1)").Count() > 0)
{
type = 1;
goodItem = goodInfoTable.Select("CategoryFatherId in (1)")[0];
}
else if (goodInfoTable.Select("CategoryFatherId in (3)").Count() > 0)
{
type = 3;
goodItem = goodInfoTable.Select("CategoryFatherId in (3)")[0];
}
else
{
goodItem = goodInfoTable.Rows[0];
type = int.Parse(goodItem["CategoryFatherId"].ToString());
}
int WeekDay = GetDayOfWeek(model.Created);
int AddDay = 0;
//获取母亲节特定商品。然后这些特定商品可以开放购买。
var MothersDayGoodss = RedisHelper.Get("TingHua_MothersDayGoodss");
if (string.IsNullOrEmpty(MothersDayGoodss))
{
RedisHelper.Set("TingHua_MothersDayGoodss", "441");
MothersDayGoodss = "441";
}
int GoodsNumber = model.Ordes[j].Num;
switch (type)
{
case 25:
orderType = OrderTypeEnum.包月.GetHashCode();
break;
case 1:
orderType = OrderTypeEnum.单束鲜花.GetHashCode();
break;
case 3:
orderType = OrderTypeEnum.绿植出售.GetHashCode();
break;
default:
orderType = OrderTypeEnum.普通商品.GetHashCode();
break;
}
bool isShouCode = false;
if (model.Ordes[j].Day != 0 && model.Ordes[j].Day.ToString().Length == 1)//只有当是这种格式(341_1)才能进入这个里面收花时间
{
isShouCode = true;
AddDay = model.Ordes[j].Day - WeekDay; //假如不是包月商品
if (AddDay <= 0)
{
if (orderType == OrderTypeEnum.包月.GetHashCode())
{
switch (AddDay)
{
case -6: AddDay = 8; break;
//case -5: AddDay = 9; break;
//case -4: AddDay = 10; break;
//case -3: AddDay = 11; break;//提前3天下单把这个删掉
default:
AddDay = 7 - Math.Abs(AddDay);
break;
}
}
else
{
AddDay = 7 - Math.Abs(AddDay);
}
}
else if (AddDay < 2 && MothersDayGoodss.Contains(nid.ToString()) && orderType == 2)
{
//母亲节特定商品开放提前一天购买。不用+7天
}
//else if (AddDay < 4 && orderType == 2) 提前3天下单用这个
else if (AddDay < 2 && orderType == 2)
{
AddDay = 7 + AddDay; //提前3天下单
//if (AddDay ==3)
//{
// var userCreateTime=DateTime.Parse(tradeJObject["created"].ToString()).Hour;
// if (userCreateTime>=16)
// {
// AddDay = 7 + AddDay;
// }
//}
//else
//{
// AddDay = 7 + AddDay;
//}
}
}
var dayStr = model.Ordes[j].Day.ToString();
if (dayStr.Length > 2 && dayStr.Length < 5)
{
var month = "01";
var recDay = "01";
if (dayStr.Length == 3)
{
month = $"0{dayStr.Substring(0, 1)}";
recDay = dayStr.Substring(1, 2);
}
else
{
month = dayStr.Substring(0, 2);
recDay = dayStr.Substring(2, 2);
}
ReciveTime = new DateTime(DateTime.Now.Year, month.ToInt32(), recDay.ToInt32());
}
else
{
ReciveTime = (AddDay > 0 ? model.Created.AddDays(AddDay) : orderType == 3 ? model.Ordes[j].ReceiveDate : model.Ordes[j].ReceiveDate.AddDays(1));
}
//收花时间顺延,概述：收花期间，为了确保用户在家能收到花（国庆，元旦出去玩），所以，这里在导入订单的时候，需要动态的修改收花时间。 因为用户每月的收花时间是固定的，定在每周几，所以顺延--一周--，7天是固定的。
//因为国庆，元旦或这劳动节，放假时长不一样，假如有一个20天的假期，我在这20天之内肯定不可以给客户送货，所以可能会顺延若干次，直到不在该假期之内，但是仅限于 包月鲜花，因为只有--包月鲜花--才有这个需求。（但是后期可能会有修改的可能）
if (!string.IsNullOrEmpty(RedisHelper.Get("Tinghua_limitEndReciveTime")) && !string.IsNullOrEmpty(RedisHelper.Get("Tinghua_limitBeginReciveTime")) && !string.IsNullOrEmpty(RedisHelper.Get("Tinghua_limitReciveTime")))
{
#region 2017年双十一收货日期更改逻辑：1某些固定类型可以不用延期；2：包月的延期跟之前的逻辑一样不用更改；3：不是包月的：如果goods_code里面有带“_”的要跟包月的延期一样，如果没有则更之前逻辑一样
var TingHuaOpenApiOpenController_OrderType = RedisHelper.Get("TingHuaOpenApiOpenController_OrderType"); //某些类型可以不用延期
bool isDay = false; //当前orderType是不是属于某些可以不用延期固定类型；是:true
if (TingHuaOpenApiOpenController_OrderType != null)
{
List <string> notTypeList = new List <string>();
notTypeList = TingHuaOpenApiOpenController_OrderType.Replace('，', ',').Split(',').ToList();
if (notTypeList.Contains(orderType.ToString()))
{
isDay = true;
}
}
#region 之前原来基础延期逻辑
if (orderType == OrderTypeEnum.包月.GetHashCode() && !isDay)
{
//是包月鲜花。
DateTime orderReciveTime = System.Convert.ToDateTime(ReciveTime);
//开始时间。
DateTime beginDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitBeginReciveTime"));
//结束时间。
DateTime endDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitEndReciveTime"));
//顺延日期。
delaySomeDate(ref orderReciveTime, 7, beginDate, endDate);
//重新设置订单的时间。
ReciveTime = orderReciveTime;
}
else
{
if (!isDay)
{
if (isShouCode)
{
//是包月鲜花。
DateTime orderReciveTime = System.Convert.ToDateTime(ReciveTime);
//开始时间。
DateTime beginDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitBeginReciveTime"));
//结束时间。
DateTime endDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitEndReciveTime"));
//顺延日期。
delaySomeDate(ref orderReciveTime, 7, beginDate, endDate);
//重新设置订单的时间。
ReciveTime = orderReciveTime;
}
else
{
DateTime beginDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitBeginReciveTime"));
DateTime endDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitEndReciveTime"));
//不是包月鲜花，但是收花日期正好落在了假期内。
if (beginDate < ReciveTime && ReciveTime < endDate)
{
ReciveTime = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitReciveTime"));
}
}
}
}
#endregion
//}
#endregion
#region 原来的延期代码
//if (orderType == OrderTypeEnum.包月.GetHashCode())
//{
// //是包月鲜花。
// DateTime orderReciveTime = System.Convert.ToDateTime(order.ReciveTime);
// //开始时间。
// DateTime beginDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitBeginReciveTime"));
// //结束时间。
// DateTime endDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitEndReciveTime"));
// //顺延日期。
// YouzanControllerTools.delaySomeDate(ref orderReciveTime, 7, beginDate, endDate);
// //重新设置订单的时间。
// order.ReciveTime = orderReciveTime;
//}
//else
//{
// DateTime beginDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitBeginReciveTime"));
// DateTime endDate = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitEndReciveTime"));
// //不是包月鲜花，但是收花日期正好落在了假期内。
// if (beginDate < order.ReciveTime && order.ReciveTime < endDate)
// {
// order.ReciveTime = System.Convert.ToDateTime(RedisHelper.Get("Tinghua_limitReciveTime"));
// }
//}
#endregion
}
#region 情人节获取 固定收花时间 玫瑰单品+单品包月
var activeGoodssid = RedisHelper.Get($"TingHua_activeGoodssid{nid}");
//if (!string.IsNullOrEmpty(activeGoodssid) && !string.IsNullOrEmpty(model.Ordes[j].shopCode))
//{
// if (model.Ordes[j].shopCode.Split('&').Length >= 5)
// {
// ReciveTime = new DateTime(DateTime.Now.Year, 8, 28);
// }
//}
#endregion
object ret = db.ExecuteScalar("INSERT Orders(UserId, OrderSerialNumber, OrderState, Person, Phone, Province, City, Area, AddressLongLat, AddressNumber, Long, Lat, ShopId, OrderAddTime, PayType, PayMent, OrderRemark, MoneyPayable, MoneyPack, MoneyExpress, CouponName, MoneyCoupon, MoneyReality, efficiencyNumber, courtesyNumber, expressName, expressNumber, UserAgent, Type, ReciveTime, Mobi, DonotWant, ContactsName, ContactMobile, Remark, expressAddressCode, DescriptionNumber, ChargeMoney, DeliveryReminder, ReceivingReminder, RoyaltyRatio, IsGroup, GoodsId, GroupId, FirstScale, TwoScale, IsPresent)"
+ $"VALUES(9476,'{(model.ID + (model.Ordes.Count > 1 ? "_" + (j + 1) : ""))}',{OrderStatus}, '{model.Receiver_Name}', '{model.Receiver_Mobile}', '{model.Receiver_State}', '{model.Receiver_City}', '{model.Receiver_District}', '{model.Receiver_Address}', NULL, NULL, NULL, {YouZanHelper.ConvertShop(YouZanConfig)}, '{model.Created}',"
+ $" { PayTypeEnum.有赞.GetHashCode()}, { PaymentEnum.有赞.GetHashCode()}, NULL, {model.Ordes[j].Total_Fee}, NULL, NULL, NULL, {Math.Round(model.Ordes[j].Total_Fee - (model.Total_Fee > 0 ? model.Payment / model.Total_Fee * model.Ordes[j].Total_Fee : 0), 2)}, { Math.Round((model.Payment / model.Ordes.Count), 2)}, NULL, NULL, NULL, NULL, '{ "有赞_" + YouZanConfig.Name}', {orderType.ToShort()}, '{ReciveTime}', NULL, '', NULL, NULL, '{(!string.IsNullOrEmpty(model.Buyer_Message) ? "买家留言：" + model.Buyer_Message : "") + (!string.IsNullOrEmpty(model.Trade_Memo) ? ";卖家备注：" + model.Trade_Memo : "")}', NULL, NULL, 0, NULL, NULL, 0.1, 0, NULL, NULL, 0.4, 0.6, 0);SELECT OrderId FROM Orders WHERE @@ROWCOUNT > 0 AND[OrderId] = scope_identity()");
OrderID = ret != null && ret.ToString() != "" ? int.Parse(ret.ToString()) : 0;
if (orderType == OrderTypeEnum.包月.GetHashCode())
{
var orderChild = new List <OrderChild>();
var flowerNumber = 0; //购买次数（每月4次 月*4）
switch ((GoodsTypeEnum)int.Parse(goodItem["type"].ToString()))
{
case GoodsTypeEnum.半月:
flowerNumber = 2;
break;
case GoodsTypeEnum.一个月:
flowerNumber = 4;
break;
case GoodsTypeEnum.个月:
flowerNumber = 12;
break;
case GoodsTypeEnum.半年:
flowerNumber = 24;
break;
case GoodsTypeEnum.全年:
flowerNumber = 48;
break;
case GoodsTypeEnum.单次:
flowerNumber = 1;
break;
}
//获取活动特定商品。然后这些特定商品可以赠送不同的次数
if (!string.IsNullOrEmpty(activeGoodssid) && !string.IsNullOrEmpty(model.Ordes[j].shopCode))
{
flowerNumber = activeGoodssid.ToInt32();
}
flowerNumber = flowerNumber * model.Ordes[j].Num;
var bl = false; //是不是mini包月
var miniGoodssid = RedisHelper.Get($"TingHua_miniGoodssid"); //mini商品ID。配送方式需要改变一下。一周送2束 隔周配送
if (!string.IsNullOrEmpty(miniGoodssid))
{
var miniGoodsssAttr = miniGoodssid.Split(',').ToArray();
for (int i = 0; i < nid.Count(); i++)
{
if (miniGoodsssAttr.Contains(nid[i].ToString()))
{
bl = true;
}
}
//错误代码
//if (miniGoodssid.Split(',').ToArray().Contains(nid.ToString()))
//{
// flowerNumber = flowerNumber / 2;
//}
}
if (bl)
{
flowerNumber = flowerNumber / 2;
}
var shopCode = "";
for (int i = 1; i <= flowerNumber; i++) //乘上购买数量
{
if (!string.IsNullOrEmpty(model.Ordes[j].shopCode))
{
var shopCodeArray = model.Ordes[j].shopCode.Split('&');
switch (shopCodeArray.Length)
{
case 2:
if ((i % 4) == 1 || (i % 4) == 2)
{
shopCode = shopCodeArray[0];
}
else
{
shopCode = shopCodeArray[1];
}
break;
case 3:
if ((i % 4) == 1 || (i % 4) == 2)
{
shopCode = shopCodeArray[0];
}
else if ((i % 4) == 3)
{
shopCode = shopCodeArray[1];
}
else
{
shopCode = shopCodeArray[2];
}
break;
case 4:
if ((i % 4) == 1)
{
shopCode = shopCodeArray[0];
}
else if ((i % 4) == 2)
{
shopCode = shopCodeArray[1];
}
else if ((i % 4) == 3)
{
shopCode = shopCodeArray[3];
}
else
{
shopCode = shopCodeArray[4];
}
break;
case 5:
switch (i)
{
case 1:
shopCode = shopCodeArray[0];
break;
case 2:
shopCode = shopCodeArray[1];
break;
case 3:
shopCode = shopCodeArray[2];
break;
case 4:
shopCode = shopCodeArray[3];
break;
case 5:
shopCode = shopCodeArray[4];
break;
default:
break;
}
break;
default:
break;
}
}
ReciveTime = ReciveTime.AddDays((i - 1) * 7);
if (bl)
{
ReciveTime = ReciveTime.AddDays(7);
}
sqls.Append("INSERT OrderChild(SendTime, Person, Phone, Province, City, Area, AddressLongLat, Status, CompleteTime, EvaluateText, EvaluateNum, SpeedNum, ExpressNum, Times, Long, Lat, AddressNumber, EvaluationPicture, DeliveryReminder, ReceivingReminder, Orders_OrderId, Courier_Id,shopCode)"
+
$"VALUES('{ReciveTime}', '{model.Receiver_Name}', '{model.Receiver_Mobile}', '{model.Receiver_State}', '{model.Receiver_City}', '{model.Receiver_District}', '{model.Receiver_Address}', {OrderChildStatus}, NULL, NULL, NULL, NULL, NULL, {i}, NULL, NULL, NULL, NULL, NULL, NULL, {OrderID}, NULL,'{shopCode}');");
}
}
for (int u = 0; u < goodInfoTable.Rows.Count; u++)
{
var goodsid = int.Parse(goodItem["id"].ToString());
goodItem = goodInfoTable.Rows[u];
sqls.Append("INSERT OrderGoodss(OrderId, GoodsId, GoodsImageUrl, GoodsTitle, GoodsTitleSub, GoodsNumber, GoodsPrice, EvaluateNumber, evaluateText, AttributeName, EvaluationPicture)"
+ $"VALUES({OrderID}, {goodsid},'{goodItem["GoodsImageUrl"].ToString()}','{goodItem["GoodsTitle"].ToString()}','{ goodItem["GoodsTitleSub"].ToString()}', {GoodsNumber*dicAttr[goodsid]}, {model.Ordes[j].Price}, NULL, NULL, '{goodItem["Name"].ToString()}', NULL);");
}
}
catch (Exception e)
{
return(false);
}
}
int retNum = 0;
if (sqls.Length < 1)
{
YouZanHelper.UpdateMemo(YouZanConfig.Name, model.ID, "tongbu:" + ZhuangTai + "_Fail");
return(false);
}
else
{
retNum = db.ExecuteSql(sqls.ToString());
sqls.Clear();
if (retNum > 0)
{
YouZanHelper.UpdateMemo(YouZanConfig.Name, model.ID, "tongbu:" + ZhuangTai + "_OK");
}
else
{
YouZanHelper.UpdateMemo(YouZanConfig.Name, model.ID, "tongbu:" + ZhuangTai + "_Fail");
}
}
return(retNum > 0);
}
public void Execute(IJobExecutionContext context)
{
try
{
#region lgq
var datetime = DateTime.Now.AddDays(-1).ToShortDateString();
var et = DateTime.Now.ToShortDateString();
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var sql = $"select og.GoodsId,o.Phone,og.OrderGoodsID from orders o,ordergoodss og where o.orderid=og.orderid and og.GoodsId in (415,416) and o.OrderState >1 and OrderAddTime >= '{datetime}' and OrderAddTime < '{et}' order by OrderGoodsID asc ";
//var sql = $"select og.GoodsId,o.Phone from orders o,ordergoodss og where o.orderid=og.orderid and og.GoodsId in (415,416) and o.OrderState >1 and OrderAddTime < '2017-04-23'";
var orderIdTable = db.ExecuteTable(sql);
sql = "select o.Phone into #TmpPhoneLin from orders o,ordergoodss og where o.orderid=og.orderid "
+ " and og.GoodsId in (415,416) and o.OrderState > 1 and OrderAddTime >='" + datetime + "' and OrderAddTime < '" + et + "' group by o.Phone;"
+ " select count(0) num, o.Phone from orders o inner join #TmpPhoneLin s on o.Phone=s.Phone"
+ " inner join ordergoodss og on o.orderid = og.orderid"
+ " where OrderAddTime < '" + datetime + "' and og.GoodsId in (415, 416)"
+ " group by o.Phone; drop table #TmpPhoneLin ";
var historyTable = db.ExecuteTable(sql);
var phoneList = new List <string>();
var historyList = new Dictionary <string, int>();
var orderListOne5 = new List <string>();
var orderListOne6 = new List <string>();
var orderListTwo5 = new List <string>();
var orderListTwo6 = new List <string>();
if (orderIdTable != null && orderIdTable.Rows.Count > 0)
{
var phone = "";
int num = 0;
int OrderGoodsID = 0;
if (historyTable != null && historyTable.Rows.Count > 0)
{
phone = "";
foreach (DataRow item in historyTable.Rows)
{
phone = item["Phone"].ToString();
if (string.IsNullOrEmpty(phone))
{
continue;
}
historyList.Add(phone, int.Parse(item["num"].ToString()));
}
}
foreach (DataRow item in orderIdTable.Rows)
{
phone = item["Phone"].ToString();
OrderGoodsID = int.Parse(item["OrderGoodsID"].ToString());
if (string.IsNullOrEmpty(phone) || (historyList.ContainsKey(phone) && historyList[phone] > 1))
{
continue;
}
else if (historyList.ContainsKey(phone) && historyList[phone] == 1)
{
num =
orderIdTable.Select()
.Where(
o =>
o["phone"].ToString() == phone && int.Parse(o["OrderGoodsID"].ToString()) < OrderGoodsID)
.Count();
switch (num)
{
case 0:
if (item["GoodsId"].ToString() == "415")
{
orderListTwo5.Add(phone);
}
else
{
orderListTwo5.Add(phone);
}
break;
}
}
else
{
num =
orderIdTable.Select()
.Where(
o =>
o["phone"].ToString() == phone && int.Parse(o["OrderGoodsID"].ToString()) < OrderGoodsID)
.Count();
switch (num)
{
case 0:
if (item["GoodsId"].ToString() == "415")
{
orderListOne5.Add(phone);
}
else
{
orderListOne6.Add(phone);
}
break;
case 1:
if (item["GoodsId"].ToString() == "415")
{
orderListTwo5.Add(phone);
}
else
{
orderListTwo6.Add(phone);
}
break;
}
}
}
orderListOne5.ToList().ForEach(l =>
{
//发送短信
new SendMessageService().SendSmsMessage(phone, "170058", null);
});
orderListOne6.ToList().ForEach(l =>
{
//发送短信
new SendMessageService().SendSmsMessage(phone, "170059", null);
});
orderListTwo5.ToList().ForEach(l =>
{
//发送短信
new SendMessageService().SendSmsMessage(phone, "169770", null);
});
orderListTwo6.ToList().ForEach(l =>
{
//发送短信
new SendMessageService().SendSmsMessage(phone, "169768", null);
});
_logger.InfoFormat($"自动任务LimitShopSendMsgJob_{orderIdTable.Rows.Count}个订单,排除重复短信发送成功{orderListOne5.Count + orderListOne6.Count+ orderListTwo6.Count+ orderListTwo5.Count}");
}
else
{
_logger.InfoFormat("自动任务LimitShopSendMsgJob_没有需要发送优惠码的订单");
}
#endregion
#region
//var datetime = DateTime.Now.AddDays(-1).ToShortDateString();
//var et = DateTime.Now.ToShortDateString();
//Sql.SqlServerClient<Orders> db = Sql.SqlServerClientSingleton<Orders>.Instance;
//var sql = $"select og.GoodsId,o.Phone from orders o,ordergoodss og where o.orderid=og.orderid and og.GoodsId in (415,416) and o.OrderState >1 and OrderAddTime >= '{datetime}' and OrderAddTime < '{et}'";
////var sql = $"select og.GoodsId,o.Phone from orders o,ordergoodss og where o.orderid=og.orderid and og.GoodsId in (415,416) and o.OrderState >1 and OrderAddTime < '2017-04-23'";
//var orderIdTable = db.ExecuteTable(sql);
//var orderList5 = new List<string>();
//var orderList6 = new List<string>();
//if (orderIdTable.Rows.Count >0)
//{
// var phone = "";
// for (int i = 0; i < orderIdTable.Rows.Count; i++)
// {
// phone = orderIdTable.Rows[i]["Phone"].ToString();
// if (string.IsNullOrEmpty(phone))
// {
// continue;
// }
// if (orderIdTable.Rows[i]["GoodsId"].ToString()== "415")
// {
// if (!(orderList5.Where(l=>l==phone).Any()))
// {
// orderList5.Add(phone);
// }
// }
// else if (orderIdTable.Rows[i]["GoodsId"].ToString() == "416")
// {
// if (!(orderList6.Where(l => l == phone).Any()))
// {
// orderList6.Add(phone);
// }
// }
// }
// orderList5.Distinct().ToList().ForEach(l => {
// //发送短信
// new SendMessageService().SendSmsMessage(phone, "170058", null);
// });
// orderList6.Distinct().ToList().ForEach(l => {
// //发送短信
// new SendMessageService().SendSmsMessage(phone, "170059", null);
// });
// _logger.InfoFormat($"自动任务LimitShopSendMsgJob_{orderIdTable.Rows.Count}个订单,排除重复短信发送成功{orderList5.Count+orderList6.Count}");
//}
//else
//{
// _logger.InfoFormat("自动任务LimitShopSendMsgJob_没有需要发送优惠码的订单");
//}
#endregion
}
catch (Exception ex)
{
_logger.InfoFormat($"自动任务LimitShopSendMsgJob_出现异常{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"BugJobs错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
_logger.InfoFormat($"KuaiDiNiaoSubscribeExpressJob-订阅快递跟踪信息开始处理");
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var sql = $@"select Person,Phone,Province,City,Area,AddressLongLat,ManagerId,CPCode,cpnumber from printorderset
where CPCode is not null and state = 2
and cpcode not in ('SF','JD','spd','thld','wtp','KSD','QSD','KSDTMS')
and (IsSubscribeExpress <> 'true' or IsSubscribeExpress is null)
and DATEDIFF(DD, PrintTime, GETDATE()) < 30
group by Person,Phone,Province,City,Area,AddressLongLat,ManagerId,CPCode,cpnumber";
var resultTable = db.ExecuteTable(sql);
var provinceSql = "select Name from provinceset";
var provinceTable = db.ExecuteTable(provinceSql);
var provinceList = new List <string>();
for (int j = 0; j < provinceTable.Rows.Count; j++)
{
provinceList.Add(provinceTable.Rows[j]["Name"].ToString());
}
var citySql = "select Name from cityset";
var cityTable = db.ExecuteTable(citySql);
var cityList = new List <string>();
var successCount = 0;
var failCount = 0;
for (int j = 0; j < cityTable.Rows.Count; j++)
{
cityList.Add(cityTable.Rows[j]["Name"].ToString());
}
var updatePrintOrderSql = string.Empty;
try
{
if (resultTable.Rows.Count > 0)
{
for (int i = 0; i < resultTable.Rows.Count; i++)
{
var Person = resultTable.Rows[i]["Person"].ToString().Trim();
var Phone = resultTable.Rows[i]["Phone"].ToString().Trim();
var Province = resultTable.Rows[i]["Province"].ToString().Trim();
if (Province.Length <= 2)
{
Province = provinceList.Where(l => l.Contains(Province)).FirstOrDefault();
}
var City = resultTable.Rows[i]["City"].ToString().Trim();
if (City.Length <= 2)
{
City = cityList.Where(l => l.Contains(City)).FirstOrDefault();
}
var Area = resultTable.Rows[i]["Area"].ToString().Trim();
var AddressLongLat = resultTable.Rows[i]["AddressLongLat"].ToString().Trim();
var ManagerId = resultTable.Rows[i]["ManagerId"].ToString().Trim();
var CPCode = resultTable.Rows[i]["CPCode"].ToString().Trim();
var cpnumber = resultTable.Rows[i]["cpnumber"].ToString().Trim();
var subscribeResult = KuaiDiHelper.ExpressSubscribe(CPCode, cpnumber, ManagerId, Phone, Person, Province, City, Area, AddressLongLat);
if (subscribeResult)
{
updatePrintOrderSql += $"update printorderset set IsSubscribeExpress='true',SubscribeExpressTime=getdate() where cpnumber='{cpnumber}' and CPCode='{CPCode}'";
successCount++;
}
else
{
updatePrintOrderSql += $"update printorderset set IsSubscribeExpress='false',SubscribeExpressTime=getdate() where cpnumber='{cpnumber}' and CPCode='{CPCode}'";
failCount++;
}
if (i % 50 == 0 && !string.IsNullOrWhiteSpace(updatePrintOrderSql))
{
db.ExecuteSql(updatePrintOrderSql);
updatePrintOrderSql = string.Empty;
}
Thread.Sleep(1300);//快递鸟那边接口并发 30/s
}
if (!string.IsNullOrWhiteSpace(updatePrintOrderSql))
{
db.ExecuteSql(updatePrintOrderSql);
updatePrintOrderSql = string.Empty;
}
_logger.InfoFormat($"KuaiDiNiaoSubscribeExpressJob-订阅快递跟踪信息处理成功，处理成功:{successCount} 失败{failCount}");
}
else
{
_logger.InfoFormat($"KuaiDiNiaoSubscribeExpressJob-订阅快递跟踪信息没有需要处理的信息");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"KuaiDiNiaoSubscribeExpressJob-订阅快递跟踪信息错误{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"KuaiDiNiaoSubscribeExpressJob-订阅快递跟踪信息错误;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
//处理业务逻辑 收花时间7天后订单状态改成已完成状态
var day = 15;//判断条件几天后修改
var orderState = OrderStatusEnum.待评价.GetHashCode();
var sql = $"select o.OrderId,o.Type,o.OrderState from Orders o where o.OrderState = {orderState} and DATEDIFF(DAY, case when o.ReciveTime is null then DATEADD(DAY,2,o.OrderAddTime) ELSE o.ReciveTime END, GETDATE()) >= {day}";
var orderIdTable = db.ExecuteTable(sql);
if (orderIdTable.Rows.Count > 0)
{
var ids = new List <int>();
var orderIdStr = string.Empty;
var MonthOrderIdStr = string.Empty; //包月订单ID
var orderStatusSql = string.Empty; //需要插入到主订单状态表的sql
int orderid;
int Type;
int state;
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
orderid = orderIdTable.Rows[i]["OrderId"].ToString().ToInt32();
Type = orderIdTable.Rows[i]["Type"].ToString().ToInt32();
state = orderIdTable.Rows[i]["OrderState"].ToString().ToInt32();
orderStatusSql += $"insert into OrderStatus (LastStatus,CurrentStatus,ChangeTime,Reason,Orders_OrderId)values({state},{OrderStatusEnum.已完成.GetHashCode()},GETDATE(),'自动任务修改',{orderid});";
ids.Add(orderid);
if (Type != OrderTypeEnum.包月.GetHashCode())
{
orderIdStr = orderIdStr == "" ? orderid.ToString() : $"{orderIdStr},{orderid}";
}
else
{
MonthOrderIdStr = MonthOrderIdStr == "" ? orderid.ToString() : $"{MonthOrderIdStr},{orderid}";
}
if (Type != OrderTypeEnum.绿植补发.GetHashCode())
{
var orderSQl = $@"select OrderLeaseId from OrderLeaseSerialNumberSet a
left join Orders b on a.OrderSerialNumber = b.OrderSerialNumber
where b.OrderId = {orderid}";
var orderTalbe = db.ExecuteTable(orderSQl);
for (int k = 0; k < orderTalbe.Rows.Count; k++)
{
orderStatusSql += $"insert OrderRecordSet(Content,CreateTime,IsState,OrderLeaseId,RecordState,OperationUserId) values ('您的补发商品已签收，感谢您对听花的支持。',getdate(),1,{orderTalbe.Rows[k]["OrderLeaseId"]},4,9476);";
}
}
else if (Type != OrderTypeEnum.绿植换货.GetHashCode())
{
var orderSQl = $@"select OrderLeaseId from OrderLeaseSerialNumberSet a
left join Orders b on a.OrderSerialNumber = b.OrderSerialNumber
where b.OrderId = {orderid}";
var orderTalbe = db.ExecuteTable(orderSQl);
for (int k = 0; k < orderTalbe.Rows.Count; k++)
{
orderStatusSql += $"insert OrderRecordSet(Content,CreateTime,IsState,OrderLeaseId,RecordState,OperationUserId) values ('您申请的需要更换的商品已签收，感谢您对听花的支持。',getdate(),1,{orderTalbe.Rows[k]["OrderLeaseId"]},4,9476);";
}
}
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
//修改主订单状态为完成并评价
var orderUpdateSql = $"update Orders set OrderState={OrderStatusEnum.已完成.GetHashCode()},efficiencyNumber=5,courtesyNumber=5,DescriptionNumber=5 where OrderId in ({orderIdStr});update OrderGoodss set evaluateText='默认好评' where OrderId in ({orderIdStr})";
var orderMonthUpdateSql = $"update Orders set OrderState={OrderStatusEnum.已完成.GetHashCode()} where OrderId in ({MonthOrderIdStr});update OrderChild set EvaluateNum = 5,SpeedNum = 5,ExpressNum = 5,EvaluateText = '默认好评' where Orders_OrderId in ({MonthOrderIdStr})";
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
var orderCount = 0;
if (orderIdTable.Rows.Count > 0)
{
if (orderIdStr != string.Empty)
{
orderCount = db.ExecuteSql(orderUpdateSql);
}
if (MonthOrderIdStr != string.Empty)
{
orderCount += db.ExecuteSql(orderMonthUpdateSql);
}
}
_logger.InfoFormat($"批量修改订单状态为完成订单成功,本次修改了{orderCount}个主订单.订单号:");
}
else
{
_logger.InfoFormat("修改订单状态为完成订单-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"修改订单状态为完成订单报错-{ex.InnerException}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderCompleteJobs错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
_logger.InfoFormat($"ConsolidateOrderStateRepair-修复合并订单打印后订单状态修改失败的订单.开始运行");
var sql = "select NoteStr,cpnumber,cpcode,PrintTime,ManagerId from printorderset where notestr is not null and MergeType is null and PrintTime is not null and DATEDIFF(DD, PrintTime, GETDATE()) < 2";
var errorTable = db.ExecuteTable(sql);
var printOrderIds = string.Empty;
var cpnumber = string.Empty;
var cpcode = string.Empty;
var PrintTime = string.Empty;
var ManagerId = string.Empty;
var printSql = string.Empty;
var ordersql = string.Empty;
var orderIds = string.Empty;
var updatePrintSql = string.Empty; //保存需要修改打印订单表的sql
var updateOrderSql = string.Empty; //保存需要修改订单表的sql
for (int i = 0; i < errorTable.Rows.Count; i++)
{
printOrderIds = errorTable.Rows[i]["NoteStr"].ToString().Trim(); //打印订单表合单后。保存合并了哪些数据的此表主键ID
cpnumber = errorTable.Rows[i]["cpnumber"].ToString().Trim(); //快递单号
cpcode = errorTable.Rows[i]["cpcode"].ToString().Trim(); //快递编码
PrintTime = errorTable.Rows[i]["PrintTime"].ToString().Trim();
ManagerId = errorTable.Rows[i]["ManagerId"].ToString().Trim();
printSql = $"select ID,CPCode,Orders_OrderId from PrintOrderSet where Id in ({printOrderIds})";
var printTable = db.ExecuteTable(printSql);
var printIds = string.Empty; //需要修改打印订单表的数据
var updateOrderIds = string.Empty; //保存需要修改订单表数据的订单ID
for (int j = 0; j < printTable.Rows.Count; j++)
{
if (j == 0)
{
orderIds = printTable.Rows[j]["Orders_OrderId"].ToString();
}
else
{
orderIds += $",{printTable.Rows[j]["Orders_OrderId"].ToString()}";
}
if (string.IsNullOrWhiteSpace(printTable.Rows[j]["CPCode"].ToString()))
{
if (string.IsNullOrWhiteSpace(printIds))
{
printIds = printTable.Rows[j]["ID"].ToString();
}
else
{
printIds += $",{printTable.Rows[j]["ID"].ToString()}";
}
}
}
if (!string.IsNullOrWhiteSpace(printIds))
{
updatePrintSql += $"UPDATE PrintOrderSet SET CPCode='{cpcode}',CPNumber='{cpnumber}',ManagerId={ManagerId},PrintTime='{PrintTime}' where Id in ({printIds});";
}
ordersql = $"select OrderState,OrderId from orders where orderid in ({orderIds})";
var orderTable = db.ExecuteTable(ordersql);
for (int j = 0; j < orderTable.Rows.Count; j++)
{
if (orderTable.Rows[j]["OrderState"].ToString() == "3")//如果订单状态为已确认。那么就修改订单状态为待收货
{
if (string.IsNullOrWhiteSpace(updateOrderIds))
{
updateOrderIds = orderTable.Rows[j]["OrderId"].ToString();
}
else
{
updateOrderIds += $",{orderTable.Rows[j]["OrderId"].ToString()}";
}
updateOrderSql += $"insert LogSet (type,Content,OrderId,createTime) values (4,'自动任务在修复合单的时候修改还修改了快递信息',{orderTable.Rows[j]["OrderId"].ToString()},getdate())";
}
}
if (!string.IsNullOrWhiteSpace(updateOrderIds))
{
updateOrderSql += $"update orders set expressName='{cpcode}',expressNumber='{cpnumber}',OrderState=4 where orderid in ({updateOrderIds});";
}
if (i % 50 == 0)
{
if (!string.IsNullOrWhiteSpace(updatePrintSql))
{
db.ExecuteSql(updatePrintSql);
}
if (!string.IsNullOrWhiteSpace(updateOrderSql))
{
db.ExecuteSql(updateOrderSql);
}
updateOrderSql = string.Empty;
updatePrintSql = string.Empty;
}
}
if (!string.IsNullOrWhiteSpace(updatePrintSql))
{
db.ExecuteSql(updatePrintSql);
}
if (!string.IsNullOrWhiteSpace(updateOrderSql))
{
db.ExecuteSql(updateOrderSql);
}
_logger.InfoFormat($"ConsolidateOrderStateRepair-修复合并订单打印后订单状态修改失败的订单运行结束.");
}
catch (Exception ex)
{
_logger.InfoFormat($"ConsolidateOrderStateRepair-读取订单数据到redis出现异常{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"ConsolidateOrderStateRepair错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
Sql.SqlServerClient <OrderStatus> OrderStatusDB = Sql.SqlServerClientSingleton <OrderStatus> .Instance;
Sql.SqlServerClient <OrderChildStatus> OrderChildStatusDB = Sql.SqlServerClientSingleton <OrderChildStatus> .Instance;
var sqlTxt = $"select OrderId,OrderAddTime,Type from Orders where OrderState={OrderStatusEnum.已付款.GetHashCode()} and (Person!='' and Person is not null) and (Phone!='' and Phone is not null)and (Province!='' and Province is not null)and (City!='' and City is not null)and (Area!='' and Area is not null)and (AddressLongLat!='' and AddressLongLat is not null and AddressLongLat not like '%测试%') and (ReciveTime!='' and ReciveTime is not null or Type in ({OrderTypeEnum.普通商品.GetHashCode()},{OrderTypeEnum.单束鲜花.GetHashCode()},{OrderTypeEnum.绿植出售.GetHashCode()},{OrderTypeEnum.绿植租赁.GetHashCode()}))";
var orderTable = db.ExecuteTable(sqlTxt);
if (orderTable.Rows.Count > 0)
{
var ids = new List <int>();
var orderIdStr = string.Empty;
var OrderChildStatusSql = string.Empty; //需要插入到子订单状态表的sql
var orderStatusSql = string.Empty; //需要插入到主订单状态表的sql
int orderid;
int Type;
int state = OrderStatusEnum.已付款.GetHashCode();
int j = 0;
for (int i = 0; i < orderTable.Rows.Count; i++)
{
j++;
orderid = orderTable.Rows[i]["OrderId"].ToString().ToInt32();
Type = orderTable.Rows[i]["Type"].ToString().ToInt32();
orderStatusSql += $"insert into OrderStatus (LastStatus,CurrentStatus,ChangeTime,Reason,Orders_OrderId)values({state},{OrderStatusEnum.已确认.GetHashCode()},GETDATE(),'自动任务修改',{orderid})";
ids.Add(orderid);
orderIdStr = orderIdStr == "" ? orderid.ToString() : $"{orderIdStr},{orderid}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
j = 0;
//获取所有子订单，用于插入状态改变记录
var childSql = $"select Orders_OrderId,Id,Status from OrderChild where Orders_OrderId in ({orderIdStr}) ";
if (!string.IsNullOrEmpty(orderIdStr))
{
var childTable = db.ExecuteTable(childSql);
for (int i = 0; i < childTable.Rows.Count; i++)
{
j++;
orderid = childTable.Rows[i]["Id"].ToString().ToInt32();
state = childTable.Rows[i]["Status"].ToString().ToInt32();
OrderChildStatusSql += $"insert into OrderChildStatus (LastStatus,CurrentStatus,ChangeTime,OrderChild_Id)values({state},{OrderChildStatusEnum.已确认.GetHashCode()},GETDATE(),{orderid})";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(OrderChildStatusSql);
OrderChildStatusSql = string.Empty;
}
}
}
//修改主订单状态为已确认
var orderUpdateSql = $"update Orders set OrderState={OrderStatusEnum.已确认.GetHashCode()} where OrderId in ({orderIdStr})";
var orderChildUpdateSql = $"update OrderChild set Status={OrderChildStatusEnum.已确认.GetHashCode()} where Orders_OrderId in ({orderIdStr})";
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
if (!string.IsNullOrEmpty(OrderChildStatusSql))
{
db.ExecuteSql(OrderChildStatusSql);//插入子订单状态改变记录
}
var orderCount = 0;
var orderChildCount = 0;
if (orderTable.Rows.Count > 0)
{
orderCount = db.ExecuteSql(orderUpdateSql);
orderChildCount = db.ExecuteSql(orderChildUpdateSql);
}
_logger.InfoFormat($"批量修改订单状态为已确认成功,本次修改了{orderCount}个主订单,{orderChildCount}个子订单.订单号:{orderIdStr}");
}
else
{
_logger.InfoFormat("修改订单状态为已确认-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"修改订单状态为已确认报错-{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderAutoConfirmJob错误信息;{fullMesage}");
}
}
public void Execute(IJobExecutionContext context)
{
try
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
//处理业务逻辑 收花时间2天后订单状态改成待评价状态
var day = 30;//判断条件几分钟后修改
var orderState = OrderStatusEnum.未付款.GetHashCode();
var sql = $"select o.OrderId,o.Type,o.OrderState,t.Orders_OrderId,t.Id from Orders o left join Ticket t on o.OrderId=t.Orders_OrderId where o.OrderState = {orderState} and DATEDIFF(MI, o.OrderAddTime, GETDATE()) > {day}";
var orderIdTable = db.ExecuteTable(sql);
if (orderIdTable.Rows.Count > 0)
{
var ids = new List <int>();
var orderIdStr = string.Empty;
var orderStatusSql = string.Empty;//需要插入到主订单状态表的sql
int orderid;
int Type;
int state;
object ticketOrderId;
var ticketIds = string.Empty;
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
orderid = orderIdTable.Rows[i]["OrderId"].ToString().ToInt32();
Type = orderIdTable.Rows[i]["Type"].ToString().ToInt32();
state = orderIdTable.Rows[i]["OrderState"].ToString().ToInt32();
ticketOrderId = orderIdTable.Rows[i]["Orders_OrderId"];
if (!string.IsNullOrEmpty(ticketOrderId.ToString()))
{
ticketIds = ticketIds == string.Empty ? orderIdTable.Rows[i]["Id"].ToString() : $"{ticketIds},{orderIdTable.Rows[i]["Id"].ToString()}";
}
orderStatusSql += $"insert into OrderStatus (LastStatus,CurrentStatus,ChangeTime,Reason,Orders_OrderId)values({state},{OrderStatusEnum.订单关闭.GetHashCode()},GETDATE(),'自动任务修改',{orderid})";
ids.Add(orderid);
orderIdStr = orderIdStr == "" ? orderid.ToString() : $"{orderIdStr},{orderid}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
//修改主订单状态为待评价
var orderUpdateSql = $"update Orders set OrderState={OrderStatusEnum.订单关闭.GetHashCode()} where OrderId in ({orderIdStr})";
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
if (ticketIds != string.Empty)
{
var ticketSql = $"update Ticket set Orders_OrderId=null,Status=2 where Id in ({ticketIds})";
db.ExecuteSql(ticketSql);//执行返回已使用的优惠券
}
var orderCount = 0;
if (orderIdTable.Rows.Count > 0)
{
orderCount = db.ExecuteSql(orderUpdateSql);
}
_logger.InfoFormat($"批量修改未付款订单状态为用户取消成功,本次修改了{orderCount}个主订单.");
}
else
{
_logger.InfoFormat("未付款超过30分钟后取消订单-没有需要处理的订单");
}
}
catch (Exception ex)
{
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"OrderCancelJob错误信息;{fullMesage}");
_logger.InfoFormat($"未付款超过30分钟后取消订单报错-{ex.Message}");
}
}
public void Execute(IJobExecutionContext context)
{
Sql.SqlServerClient <Orders> db = Sql.SqlServerClientSingleton <Orders> .Instance;
var DeliveryCount = 0;
//需要发送发货提醒
var OrderTable = db.ExecuteTable("select ocp.Number,oc.phone,oc.id from OrderChild oc,OrderChildExpress ocp where oc.Id=ocp.OrderChild_Id and SendTime>='2017-03-29'and SendTime<CONVERT(varchar(100), getdate()+2, 23) and ocp.Number is not null and (oc.DeliveryReminder is null or oc.DeliveryReminder='')");
//需要发送收货提醒的
var ReceivingReminderOrder = db.ExecuteTable("select ocp.Number,oc.phone,oc.id,o.UserAgent from OrderChild oc,OrderChildExpress ocp,Orders o where oc.Id=ocp.OrderChild_Id and oc.Orders_OrderId=o.OrderId and ocp.Number is not null and oc.DeliveryReminder='已发' and (oc.ReceivingReminder is null or oc.ReceivingReminder='')");
#region 处理需要发送发货提醒的订单
if (OrderTable.Rows.Count > 0)
{
var orderIdStr = string.Empty; //需要修改已发发货提醒的订单
for (int i = 0; i < OrderTable.Rows.Count; i++)
{
var Number = OrderTable.Rows[i]["Number"].ToString();
var phone = OrderTable.Rows[i]["phone"].ToString();
var id = OrderTable.Rows[i]["id"].ToString();
if (id.ToInt() % 2 != 0)
{
continue; //执行偶数
}
var url = string.Format("http://m.kuaidi100.com/query?type=shunfeng&postid={0}&id=1&valicode=1&temp=0.6158711992580131", Number);
var obj = (JObject)JsonHelper.Deserialize(HttpHelper.Get(url, new { }));
//var obj = (JObject)JsonHelper.Deserialize(sendMsg(Number));
if (obj["status"].ToString() == "200" && (obj["state"].ToString() == "0" || obj["state"].ToString() == "1")) //说明已签收
{
orderIdStr = orderIdStr == "" ? id.ToString() : $"{orderIdStr},{id}";
//发送短信
new SendMessageService().SendSmsMessage(phone, "162029", null);
}
else
{
Console.WriteLine($"{i}/{OrderTable.Rows.Count}:子订单ID{id}-运单号:{Number}--查询快递返回结果message{obj["message"].ToString()}");
}
//if (obj["message"].ToString().Trim()=="ok")//说明已揽件
//{
// if (obj["status"].ToString() == "200" && (obj["state"].ToString() == "0" || obj["state"].ToString() == "1"))
// {
// orderIdStr = orderIdStr == "" ? id.ToString() : $"{orderIdStr},{id}";
// //发送短信
// new SendMessageService().SendSmsMessage(phone, "162029", null);
// }
//}
//else
//{
// Console.WriteLine($"{i}/{OrderTable.Rows.Count}:子订单ID{id}-运单号:{Number}--查询快递返回结果message{obj["message"].ToString()}");
//}
System.Threading.Thread.Sleep(6);//程序休眠6S然后在查询 防止IP被封
}
if (!string.IsNullOrEmpty(orderIdStr))
{
DeliveryCount = db.ExecuteSql($"update OrderChild set DeliveryReminder='已发' where id in ({orderIdStr})");
_logger.InfoFormat($"共查询到{OrderTable.Rows.Count}个需要发发货提醒,发送短信的子订单ID为{orderIdStr}");
}
else
{
_logger.InfoFormat($"共查询到{OrderTable.Rows.Count}个需要发收货提醒但没有符合发送短信条件的");
}
}
#endregion
#region 处理需要发送收货提醒的
if (ReceivingReminderOrder.Rows.Count > 0)
{
var orderIdStr = string.Empty;//需要修改已发发货提醒的订单
for (int i = 0; i < ReceivingReminderOrder.Rows.Count; i++)
{
var Number = ReceivingReminderOrder.Rows[i]["Number"].ToString();
var phone = ReceivingReminderOrder.Rows[i]["phone"].ToString();
var id = ReceivingReminderOrder.Rows[i]["id"].ToString();
var UserAgent = ReceivingReminderOrder.Rows[i]["UserAgent"].ToString();
if (id.ToInt() % 2 != 0)
{
continue; //执行偶数
}
var url = string.Format("http://m.kuaidi100.com/query?type=shunfeng&postid={0}&id=1&valicode=1&temp=0.6158711992580131", Number);
var obj = (JObject)JsonHelper.Deserialize(HttpHelper.Get(url, new { }));
// var obj = (JObject)JsonHelper.Deserialize(sendMsg(Number));
if (obj["status"].ToString() == "200" && obj["state"].ToString() == "3")//说明已签收
{
orderIdStr = orderIdStr == "" ? id.ToString() : $"{orderIdStr},{id}";
//发送短信
switch (UserAgent)
{
case "淘宝":
new SendMessageService().SendSmsMessage(phone, "162498", null);
break;
case "iOS":
new SendMessageService().SendSmsMessage(phone, "162031", null);
break;
case "Android":
new SendMessageService().SendSmsMessage(phone, "162031", null);
break;
case "微信":
new SendMessageService().SendSmsMessage(phone, "162032", null);
break;
case "":
new SendMessageService().SendSmsMessage(phone, "162032", null);
break;
default:
new SendMessageService().SendSmsMessage(phone, "162032", null);
break;
}
Console.WriteLine($"{i}/{ReceivingReminderOrder.Rows.Count}:子订单ID{id}-运单号:{Number}短信已发送成功");
}
else
{
Console.WriteLine($"{i}/{ReceivingReminderOrder.Rows.Count}:子订单ID{id}-运单号:{Number}--查询快递返回结果status:{obj["status"].ToString()}-state:{obj["state"].ToString()}");
}
System.Threading.Thread.Sleep(6);//程序休眠6S然后在查询 防止IP被封
}
if (!string.IsNullOrEmpty(orderIdStr))
{
DeliveryCount = db.ExecuteSql($"update OrderChild set ReceivingReminder='已发' where id in ({orderIdStr})");
_logger.InfoFormat($"共查询到{ReceivingReminderOrder.Rows.Count}个需要发收货提,发送短信的子订单ID为{orderIdStr}");
}
else
{
_logger.InfoFormat($"共查询到{ReceivingReminderOrder.Rows.Count}个需要发收货提醒但没有符合发送短信条件的");
}
}
#endregion
if (OrderTable.Rows.Count <= 0 && ReceivingReminderOrder.Rows.Count <= 0)
{
_logger.InfoFormat("没有需要处理的发货和收货短信提醒");
}
}
void IJob.Execute(IJobExecutionContext context)
{
_logger.InfoFormat($"TriggerRepairJob-修复触发器异常数据开始运行...");
#region 非包月订单修复逻辑(排除了合并订单)
Sql.SqlServerClient <Tinghua.Management.Model.Orders> db = Sql.SqlServerClientSingleton <Tinghua.Management.Model.Orders> .Instance;
var SQL = @"select p.id,(case o.orderstate when 4 then 2 when 5 then 2 when 6 then 2 when 2 then 1 when 3 then 1 when 14 then 1 else -1 end) as State,o.Person,o.Phone,o.Province,o.City,o.Area,o.AddressLongLat,CONVERT(varchar(100),(case when o.ReciveTime is null then DATEADD(DAY,2,o.OrderAddTime) else o.ReciveTime end), 120) as ReciveTime from printorderset p,Orders o
where o.orderid=p.orders_orderid and o.type != 2 and MergeType is null and NoteStr is null and o.orderstate=3
and(o.Person != p.Person or o.Phone != p.Phone or o.Province != p.Province or o.City != p.City or o.Area != p.Area or RTRIM(replace(o.AddressLongLat, CHAR(10), '')) != RTRIM(replace(o.AddressLongLat, CHAR(10), ''))
or(CONVERT(varchar(100), p.ReciveTime, 120) != CONVERT(varchar(100), (case when o.ReciveTime is null then DATEADD(DAY, 2, o.OrderAddTime) else o.ReciveTime end), 120))
or p.State != (case o.orderstate when 4 then 2 when 5 then 2 when 6 then 2 when 2 then 1 when 3 then 1 when 14 then 1 else -1 end)
)";
var orderIdTable = db.ExecuteTable(SQL);
try
{
if (orderIdTable.Rows.Count > 0)
{
var orderStatusSql = string.Empty;//需要插入到主订单状态表的sql
var Person = string.Empty;
var Phone = string.Empty;
var Province = string.Empty;
var City = string.Empty;
var Area = string.Empty;
var AddressLongLat = string.Empty;
var State = string.Empty;
var ReciveTime = string.Empty;
var id = 0;
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
id = orderIdTable.Rows[i]["id"].ToString().ToInt32();
Person = orderIdTable.Rows[i]["Person"].ToString();
Phone = orderIdTable.Rows[i]["Phone"].ToString();
Province = orderIdTable.Rows[i]["Province"].ToString();
City = orderIdTable.Rows[i]["City"].ToString();
Area = orderIdTable.Rows[i]["Area"].ToString();
AddressLongLat = orderIdTable.Rows[i]["AddressLongLat"].ToString();
State = orderIdTable.Rows[i]["State"].ToString();
ReciveTime = orderIdTable.Rows[i]["ReciveTime"].ToString();
orderStatusSql += $"update printorderset set Person='{Person}',Phone='{Phone}',Province='{Province}',City='{City}',Area='{Area}',AddressLongLat='{AddressLongLat}',State={State},ReciveTime='{ReciveTime}' where id={id}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
//修改主订单状态为待评价
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
_logger.InfoFormat($"TriggerRepairJob-修复触发器异常数据之非包月订单修复,本次修改了{orderIdTable.Rows.Count}个.");
}
else
{
_logger.InfoFormat("TriggerRepairJob-修复触发器异常数据之非包月订单修复-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"TriggerRepairJob-修复触发器异常数据修复之非包月订单修复报错-{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"TriggerRepairJob错误信息;{fullMesage}");
}
#endregion
#region 包月订单修复逻辑
var MonthSQL = @"select p.id,(case o.Status when 3 then 2 when 4 then 2 when 2 then 1 when 1 then 1 else -1 end) as State,o.Person,o.Phone,o.Province,o.City,o.Area,o.AddressLongLat,o.SendTime ReciveTime from printorderset p,orderchild o
where o.Id=p.OrderChild_Id and o.Status=1
and(o.Person != p.Person or o.Phone != p.Phone or o.Province != p.Province or o.City != p.City or o.Area != p.Area or RTRIM(replace(o.AddressLongLat, CHAR(10), '')) != RTRIM(replace(o.AddressLongLat, CHAR(10), ''))
or(CONVERT(varchar(100), p.ReciveTime, 120) != CONVERT(varchar(100), SendTime, 120))
or p.State != (case o.Status when 3 then 2 when 4 then 2 when 2 then 1 when 1 then 1 else -1 end)
)";
orderIdTable = db.ExecuteTable(MonthSQL);
try
{
if (orderIdTable.Rows.Count > 0)
{
var orderStatusSql = string.Empty;//需要插入到主订单状态表的sql
var Person = string.Empty;
var Phone = string.Empty;
var Province = string.Empty;
var City = string.Empty;
var Area = string.Empty;
var AddressLongLat = string.Empty;
var State = string.Empty;
var ReciveTime = string.Empty;
var id = 0;
int j = 0;
for (int i = 0; i < orderIdTable.Rows.Count; i++)
{
j++;
id = orderIdTable.Rows[i]["id"].ToString().ToInt32();
Person = orderIdTable.Rows[i]["Person"].ToString();
Phone = orderIdTable.Rows[i]["Phone"].ToString();
Province = orderIdTable.Rows[i]["Province"].ToString();
City = orderIdTable.Rows[i]["City"].ToString();
Area = orderIdTable.Rows[i]["Area"].ToString();
AddressLongLat = orderIdTable.Rows[i]["AddressLongLat"].ToString();
State = orderIdTable.Rows[i]["State"].ToString();
ReciveTime = orderIdTable.Rows[i]["ReciveTime"].ToString();
orderStatusSql += $"update printorderset set Person='{Person}',Phone='{Phone}',Province='{Province}',City='{City}',Area='{Area}',AddressLongLat='{AddressLongLat}',State={State},ReciveTime='{ReciveTime}' where id={id}";
//一次执行50个
if (j >= 50)
{
j = 1;
db.ExecuteSql(orderStatusSql);
orderStatusSql = string.Empty;
}
}
//修改主订单状态为待评价
if (!string.IsNullOrEmpty(orderStatusSql))
{
db.ExecuteSql(orderStatusSql);//插入主订单状态改变记录
}
_logger.InfoFormat($"TriggerRepairJob-修复触发器异常数据之包月订单修复,本次修改了{orderIdTable.Rows.Count}个.");
}
else
{
_logger.InfoFormat("TriggerRepairJob-修复触发器异常数据之包月订单修复-没有需要处理的订单");
}
}
catch (Exception ex)
{
_logger.InfoFormat($"TriggerRepairJob-修复触发器异常数据修复之包月订单修复报错-{ex.Message}");
var fullMesage = ErrorHelper.FullException(ex);
_errLog.ErrorFormat($"TriggerRepairJob错误信息;{fullMesage}");
}
#endregion
}