public static DBSql getInstance()
{
if (instance == null)
{
instance = new DBSql();
}
return(instance);
}
/// <summary>
/// 获取订单清单中未付款的订单并判断是否超时取消
/// </summary>
private void CheckOrder()
{
try
{
//获取超过卖家设置时间还未支付的订单并取消
string appkey = "12223169";
string secret = "ff3d3442ab809930d187623ffad8e91e";
string session = string.Empty;
string minute = "8";
DBSql db = DBSql.getInstance();
string sql = "SELECT * FROM TopGroupBuy WHERE DATEDIFF(s,GETDATE(),starttime) < 0 AND DATEDIFF(s,GETDATE(),endtime) > 0 AND isdelete = 0 AND tagid <> 1";
//textBox2.AppendText("\r\n" + sql);
DataTable dtGroup = db.GetTable(sql);
for (int q = 0; q < dtGroup.Rows.Count; q++)
{
//判断如果没有合适的需要检查的订单则下一个
sql = "SELECT o.*,d.buynick FROM TopGroupBuyDetailOrder o INNER JOIN TopGroupBuyDetail d ON d.id = o.detailid WHERE o.groupbuyid = " + dtGroup.Rows[q]["id"].ToString() + " AND o.status = 'WAIT_BUYER_PAY' AND DATEDIFF(n,o.addtime,GETDATE()) > " + minute;
//textBox2.AppendText("\r\n" + sql);
DataTable dt = db.GetTable(sql);
if (dt.Rows.Count == 0)
{
continue;
}
//通过接口将该用户加入人群
sql = "SELECT session FROM TopTaobaoShop WHERE nick = '" + dtGroup.Rows[q]["nick"].ToString() + "'";
//textBox2.AppendText("\r\n" + sql);
DataTable dtnick = db.GetTable(sql);
if (dtnick.Rows.Count != 0)
{
session = db.GetTable(sql).Rows[0][0].ToString();
}
else
{
//如果查不到记录则继续
continue;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
//查询该订单的状态
IDictionary <string, string> param = new Dictionary <string, string>();
param.Add("fields", "status,pay_time");
param.Add("tid", dt.Rows[i]["orderid"].ToString());
string resultpro = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.trade.fullinfo.get", session, param);
//textBox2.AppendText("\r\n" + resultpro);
Regex reg = new Regex(@"<status>([^<]*)</status>", RegexOptions.IgnoreCase);
MatchCollection match = reg.Matches(resultpro);
if (match[0].Groups[1].ToString() == "WAIT_SELLER_SEND_GOODS" || match[0].Groups[1].ToString() == "WAIT_BUYER_CONFIRM_GOODS" || match[0].Groups[1].ToString() == "TRADE_FINISHED")
{
string paytime = Regex.Match(resultpro, @"<pay_time>([^<]*)</pay_time>").Groups[1].ToString();
//如果支付则更改订单状态并记录支付成功时间
sql = "UPDATE TopGroupBuyDetailOrder SET status = '" + match[0].Groups[1].ToString() + "',paytime = '" + paytime + "' WHERE id = " + dt.Rows[i]["id"].ToString();
//textBox2.AppendText("\r\n" + sql);
db.ExecSql(sql);
}
else
{
//向接口发送取消请求
param = new Dictionary <string, string>();
param.Add("tid", dt.Rows[i]["orderid"].ToString());
param.Add("close_reason", "拍下团购商品后在指定时间内没有付款");
resultpro = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.trade.close", session, param);
//textBox2.AppendText("\r\n" + resultpro);
//取消改用户购买资格
param = new Dictionary <string, string>();
param.Add("tag_id", dtGroup.Rows[q]["tagid"].ToString());
param.Add("nick", dt.Rows[i]["buynick"].ToString());
resultpro = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.marketing.taguser.delete", session, param);
textBox2.AppendText("\r\n" + resultpro);
//如果没有支付则取消该订单并减少团购数量
sql = "UPDATE TopGroupBuyDetailOrder SET status = 'TRADE_CLOSED_BY_TAOBAO',canceltime = GETDATE() WHERE id = " + dt.Rows[i]["id"].ToString();
//textBox2.AppendText("\r\n" + sql);
db.ExecSql(sql);
//减少团购详细里面记录的数量
sql = "UPDATE TopGroupBuyDetail SET count = count - " + dt.Rows[i]["count"].ToString() + " WHERE id = " + dt.Rows[i]["detailid"].ToString();
//textBox2.AppendText("\r\n" + sql);
db.ExecSql(sql);
//减少团购总表里面记录的数量
sql = "UPDATE TopGroupBuy SET buycount = buycount - " + dt.Rows[i]["count"].ToString() + " WHERE id = " + dt.Rows[i]["groupbuyid"].ToString();
//textBox2.AppendText("\r\n" + sql);
db.ExecSql(sql);
//更新团购加入记录并置为不可用
sql = "UPDATE TopGroupBuyDetail SET iscancel = 1 WHERE id = " + dt.Rows[i]["detailid"].ToString();
//textBox2.AppendText("\r\n" + sql);
db.ExecSql(sql);
}
}
}
//textBox2.AppendText("\r\n\r\n**********************************************************");
//休息后继续循环-默认10分钟一次
Thread.Sleep(60000);
Thread newThread1 = new Thread(CheckOrder);
newThread1.Start();
}
catch (Exception e)
{
MessageBox.Show("\r\n" + e.StackTrace);
}
}
/// <summary>
/// 记录参团人数到数据库
/// </summary>
/// <param name="buynick"></param>
/// <param name="groupbuyid"></param>
/// <param name="status"></param>
/// <param name="num"></param>
/// <param name="orderid"></param>
private void RecordBuyData(string buynick, string groupbuyid, string status, string num, string orderid, string detailid, string created)
{
DBSql db = DBSql.getInstance();
//判断该订单是否插入过
string sql = "SELECT COUNT(*) FROM TopGroupBuyDetailOrder WHERE orderid = '" + orderid + "'";
WriteLog(sql, "");
string count = db.GetTable(sql).Rows[0][0].ToString();
if (count != "0")
{
return;
}
//判断该用户是否有订购记录
if (detailid == "0")
{
sql = "SELECT COUNT(*) FROM TopGroupBuyDetail WHERE buynick = '" + buynick + "' AND groupbuyid = " + groupbuyid;
WriteLog(sql, "");
count = db.GetTable(sql).Rows[0][0].ToString();
if (count == "0")
{
//插入记录
sql = "INSERT INTO TopGroupBuyDetail (" +
"groupbuyid," +
"buynick" +
" ) VALUES ( " +
" '" + groupbuyid + "'," +
" '" + buynick + "'" +
") ";
WriteLog(sql, "");
db.ExecSql(sql);
//返回ID
sql = "SELECT TOP 1 id FROM TopGroupBuyDetail ORDER BY id DESC";
WriteLog(sql, "");
detailid = db.GetTable(sql).Rows[0][0].ToString();
}
else
{
sql = "SELECT id FROM TopGroupBuyDetail WHERE buynick = '" + buynick + "' AND groupbuyid = " + groupbuyid;
detailid = db.GetTable(sql).Rows[0][0].ToString();
}
}
//更新详细购买记录
sql = "UPDATE TopGroupBuyDetail SET count = count + " + num + " WHERE id = " + detailid + "";
WriteLog(sql, "");
db.ExecSql(sql);
//更新团购总购买记录
sql = "UPDATE TopGroupBuy SET buycount = buycount + " + num + " WHERE id = " + groupbuyid + "";
WriteLog(sql, "");
db.ExecSql(sql);
//插入团购关联淘宝订单表数据
sql = "INSERT INTO TopGroupBuyDetailOrder (detailid,orderid,status,addtime,count,groupbuyid) VALUES ('" + detailid + "','" + orderid + "','" + status + "','" + created + "','" + num + "','" + groupbuyid + "')";
WriteLog(sql, "");
db.ExecSql(sql);
}
/// <summary>
///
/// </summary>
private void DoMyJob()
{
try
{
//获取正在进行中的团购项目
string appkey = "12287381"; //"12287381";
string secret = "d3486dac8198ef01000e7bd4504601a4"; //d3486dac8198ef01000e7bd4504601a4";
string session = string.Empty;
//获取已经结束的团购活动并取消
DBSql db = DBSql.getInstance();
string sql = "SELECT * FROM TopGroupBuy WHERE DATEDIFF(s,GETDATE(),endtime) < 0 AND promotionid <> 0 AND isdelete = 0";
#region 取消活动 删除该活动关联的用户群 将该团购标志为已结束 这段代码由tetegroupbuyRemove服务代替
////WriteLog(sql, "");
//DataTable enddt = db.GetTable(sql);
////通过接口将该用户加入人群
//for (int y = 0; y < enddt.Rows.Count; y++)
//{
// sql = "SELECT session FROM TopTaobaoShop WHERE nick = '" + enddt.Rows[y]["nick"].ToString() + "'";
// WriteLog("清除代码:" + sql, "");
// DataTable dtnick = db.GetTable(sql);
// if (dtnick.Rows.Count != 0)
// {
// session = db.GetTable(sql).Rows[0][0].ToString();
// }
// //取消该活动
// IDictionary<string, string> paramnew = new Dictionary<string, string>();
// paramnew.Add("promotion_id", enddt.Rows[y]["promotionid"].ToString());
// string resultnew = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.marketing.promotion.delete", session, paramnew);
// WriteLog("清除代码:" + resultnew, "");
// //删除该活动关联的用户群
// //paramnew = new Dictionary<string, string>();
// //if (enddt.Rows[y]["tagid"].ToString() != "1")
// //{
// // paramnew.Add("tag_id", enddt.Rows[y]["tagid"].ToString());
// //}
// //resultnew = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.marketing.tag.delete", session, paramnew);
// //WriteLog("清除代码:" + resultnew, "");
// //将该团购标志为已结束
// sql = "UPDATE TopGroupBuy SET isdelete = 1 WHERE id = " + enddt.Rows[y]["id"].ToString();
// //textBox2.AppendText("\r\n" + sql);
// db.ExecSql(sql);
//}
#endregion
//获取正在进行中的团购活动
sql = "SELECT * FROM TopGroupBuy WHERE DATEDIFF(s,GETDATE(),starttime) < 0 AND DATEDIFF(s,GETDATE(),endtime) > 0 AND promotionid <> 0 AND isdelete = 0";
DataTable dt = db.GetTable(sql);
for (int k = 0; k < dt.Rows.Count; k++)
{
sql = "SELECT session FROM TopTaobaoShop WHERE nick = '" + dt.Rows[k]["nick"].ToString() + "'";
DataTable dtnick = db.GetTable(sql);
if (dtnick.Rows.Count != 0)
{
session = db.GetTable(sql).Rows[0][0].ToString();
}
else
{
//如果查不到记录则继续
continue;
}
//变量赋值
string groupproductid = dt.Rows[k]["productid"].ToString();
DateTime startdate = new DateTime();
DateTime enddate = new DateTime();
//获取该团购上次更新时间
if (dt.Rows[k]["updatedate"] == DBNull.Value)
{
//如果是第一次检测
startdate = DateTime.Parse(dt.Rows[k]["starttime"].ToString());
}
else
{
//如果不是第一次检测
startdate = DateTime.Parse(dt.Rows[k]["updatedate"].ToString());
}
enddate = startdate.AddHours(23);
//判断结束时间是否大于现在
if (enddate > DateTime.Now.AddMinutes(-2))
{
enddate = DateTime.Now.AddMinutes(-2);
}
//更新数据中记录的判断时间
sql = "UPDATE TopGroupBuy SET updatedate = '" + enddate.ToString() + "' WHERE id = " + dt.Rows[k]["id"].ToString();
db.ExecSql(sql);
//通过接口获取用户下单信息(此处使用查询效率最高的30分钟间距作为查询条件)
IDictionary <string, string> param = new Dictionary <string, string>();
param.Add("fields", "buyer_nick,tid,status,created");
param.Add("start_modified", startdate.ToString("yyyy-MM-dd HH:mm:ss"));
param.Add("end_modified", enddate.ToString("yyyy-MM-dd HH:mm:ss"));
string result = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.trades.sold.increment.get", session, param);
WriteLog(result, "");
Regex reg = new Regex(@"<buyer_nick>([^<]*)</buyer_nick><created>([^<]*)</created><status>([^<]*)</status><tid>([^<]*)</tid>", RegexOptions.IgnoreCase);
MatchCollection match = reg.Matches(result);
WriteLog(result, "");
WriteLog(match.Count.ToString(), "");
for (int i = 0; i < match.Count; i++)
{
//判断该用户的订单是否包含该团购商品
param = new Dictionary <string, string>();
param.Add("fields", "orders.num_iid,orders.num");
param.Add("tid", match[i].Groups[4].ToString());
string resultpro = Post("http://gw.api.taobao.com/router/rest", appkey, secret, "taobao.trade.fullinfo.get", session, param);
//判断该订单里面是否包含团购商品并统计订购数量
Regex regpro = new Regex(@"<num>([^<]*)</num><num_iid>([^<]*)</num_iid>", RegexOptions.IgnoreCase);
MatchCollection matchpro = regpro.Matches(resultpro);
WriteLog(resultpro, "");
for (int j = 0; j < matchpro.Count; j++)
{
WriteLog(matchpro[j].Groups[2].ToString() + "-" + groupproductid, "");
//判断该订单是否包含团购商品
if (matchpro[j].Groups[2].ToString() == groupproductid)
{
//如果包含则记录到本地数据库
RecordBuyData(match[i].Groups[1].ToString(), dt.Rows[k]["id"].ToString(), match[i].Groups[3].ToString(), matchpro[j].Groups[1].ToString(), match[i].Groups[4].ToString(), "0", match[i].Groups[2].ToString());
}
else
{
//取消
continue;
}
}
}
}
dt.Dispose();
WriteLog("**********************************************************", "");
//休息后继续循环-默认15分钟一次
Thread.Sleep(600000);
Thread newThread = new Thread(DoMyJob);
newThread.Start();
}
catch (Exception e)
{
WriteLog("**********************************************************" + e.StackTrace + "----error!!!", "1");
//MessageBox.Show("\r\n" + e.StackTrace);
Thread newThread = new Thread(DoMyJob);
//休息后继续循环-默认10分钟一次
Thread.Sleep(900000);
newThread.Start();
}
}