public bool VerificationPhone(string phone)
{
MG_DAL.SQLServerOperating s = new MG_DAL.SQLServerOperating();
string exist = s.Select("select count(*) from Users where deleted=0 and loginname=@loginname", new SqlParameter[] { new SqlParameter("loginname", phone) });
return(Convert.ToInt32(exist) > 0);
}
public List <Dictionary <string, string> > GetPriceList(string Model)
{
List <Dictionary <string, string> > PriceList = new List <Dictionary <string, string> >();
try
{
int type = 0;
if (Model.StartsWith("MG-X1") || Model.StartsWith("MG-X8") || Model.StartsWith("MG-X50"))
{
type = 1;
}
else
{
type = 2;
}
MG_DAL.SQLServerOperating s = new MG_DAL.SQLServerOperating();
string strSql = "select ID,TariffName,Price,OldPrice,BuyCount from TariffPackages where [Type]=@type";
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("type", type) });
foreach (DataRow row in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
dic["TariffName"] = row["TariffName"].ToString();
dic["Price"] = row["Price"].ToString();
dic["BuyCount"] = row["BuyCount"].ToString();
dic["OldPrice"] = row["OldPrice"].ToString();
dic["ID"] = row["ID"].ToString();
PriceList.Add(dic);
}
}
catch (Exception)
{
}
return(PriceList);
}
/// <summary>
/// 添加订单
/// </summary>
/// <param name="UserID">用户ID</param>
/// <param name="DeviceID">设备ID</param>
/// <param name="OpenID">用户的OpenID</param>
/// <param name="OrderNo">订单号</param>
/// <param name="TransacionNo">微信支付单号，根据此单号去微信API查询订单状态（支付成功后返回回调URL里面）</param>
/// <param name="ProductBody">商品描述</param>
/// <param name="FeeType">货币币种</param>
/// <param name="TotalFee">金额</param>
/// <param name="OrderDate">订单生成时间</param>
/// <param name="OrderExpire">订单失效时间</param>
/// <param name="TariffID">充值的套餐ID</param>
public void AddOrder(int UserID, int DeviceID, string OpenID, string OrderNo, string ProductBody, string FeeType, int TotalFee, DateTime OrderDate, DateTime OrderExpire, int TariffID)
{
try
{
string strSql = @"insert into orders( UserID, DeviceID, OpenID, OrderNo,TransactionNo, ProductBody, FeeType, TotalFee, Created, OrderDate,OrderExpire, TariffID, Status, BillCreateIP)
values(@UserID, @DeviceID, @OpenID, @OrderNo,@TransactionNo,@ProductBody,@FeeType, @TotalFee, GETDATE(), @OrderDate,@OrderExpire, @TariffID, @OrderStatus, '') ";
SQLServerOperating s = new MG_DAL.SQLServerOperating();
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("UserID", UserID),
new SqlParameter("DeviceID", DeviceID),
new SqlParameter("OpenID", OpenID),
new SqlParameter("OrderNo", OrderNo),
new SqlParameter("TransactionNo", OrderNo),/// 为了不违反TransactionNo唯一约束,在订单支付成功之前,TransactionNo值暂且用orderNo填充,在支付成功之后将改为微信的订单号
new SqlParameter("ProductBody", ProductBody),
new SqlParameter("FeeType", FeeType),
new SqlParameter("TotalFee", TotalFee),
new SqlParameter("OrderDate", OrderDate.ToString("yyyy-MM-dd HH:mm:ss")),
new SqlParameter("OrderExpire", OrderExpire.ToString("yyyy-MM-dd HH:mm:ss")),
new SqlParameter("TariffID", TariffID),
new SqlParameter("OrderStatus", OrderStatus.已提交)
};
s.ExecuteSql(strSql, pars);
Task.Run(() => {
Utils.SendTcpCmd("VTR-UpdateHireExpireDate-" + DeviceID);
});
}
catch (Exception ex)
{
Utils.log("MG_BLL.Pay.MgooOrders.Orders > AddOrder Error: " + ex.Message);
}
}
private string SendUnlockCommand(int DeviceID, string Password)
{
ajaxResult ar = new ajaxResult();
ar.Result = "3";
if (!string.IsNullOrEmpty(Password) && DeviceID > 0 && Password.Length > 0 && Password.Length < 10)
{
string strSql = "select DevicePassword,SerialNumber from devices where deviceid=@DeviceID and deleted=0";
MG_DAL.SQLServerOperating sqlHelper = new MG_DAL.SQLServerOperating();
var dic = sqlHelper.Selects(strSql, new SqlParameter[] { new SqlParameter("DeviceID", DeviceID) }).toDictionary();
if (dic["DevicePassword"].Equals(Password))
{
string imei = dic["SerialNumber"];
List <Task> listTask = new List <Task>();
string cmdid = "0";
Task task = Task.Factory.StartNew(() =>
{
strSql = @" insert into CarCommandQueue ( DeviceID, CommandText, CreateDate, IsSend, SendDate, IsResponse, ResponseDate, ResponseText, CommandName, IsOfflineSend, Infos, SendCount)
values(@SerialNumber, 'KM', getdate(), 0, getdate(), 0, '1900-01-01 00:00:00.000', '', '指令开锁', 0, '', 0) select @@IDENTITY CommandID";
cmdid = sqlHelper.Select(strSql, new SqlParameter[] { new SqlParameter("SerialNumber", imei) });
});
listTask.Add(task);
task = Task.Factory.StartNew(() =>
{
ar.Result = Utils.SendTcpCmd("VTR-Command-" + dic["SerialNumber"] + "-KM");
});
listTask.Add(task);
Task.WaitAll(listTask.ToArray());
if (ar.Result.Equals("1"))
{
Task.Factory.StartNew(() => {
strSql = " update CarCommandQueue set IsResponse=1 ,ResponseDate=getdate(),ResponseText='Success!' where id=" + cmdid;
sqlHelper.ExecuteSql(strSql);
});
}
// status = Utils.SendTcpCmd("VTR-Command-" + SerialNumber + "-DY");
// if (status .Equals("1"))
//{
//Task.Run(() => {
// System.Threading.Thread.Sleep(3000);
// var cmdRes = Utils.SendTcpCmd("VTR-Command-" + SerialNumber + "-TY");
// while (cmdRes!= "1")
// {
// System.Threading.Thread.Sleep(3000);
// cmdRes = Utils.SendTcpCmd("VTR-Command-" + SerialNumber + "-TY");
// }
//});
// }
}
else
{
ar.Result = "2";
}
}
return(Utils.ToJson(ar));
}
/// <summary>
/// 修改内部订单系统的订单状态
/// </summary>
public bool ModifyOrderStatus(string transaction_id, string fee_type, string time_end, string bank_type, string trade_type, string trade_no, bool updateExpireDate = true)
{
try
{
string strSql = "update Orders set TransactionNo=@TransactionNo,FeeType=@FeeType,PayDate=@PayDate,Status=@Status,BankType=@BankType,TradeType=@TradeType where OrderNo=@OrderNo and Status=@Status1";
MG_DAL.SQLServerOperating s = new MG_DAL.SQLServerOperating();
int reslut = s.ExecuteSql(strSql,
new SqlParameter[]
{
new SqlParameter("TransactionNo", transaction_id),
new SqlParameter("FeeType", fee_type),
new SqlParameter("Status", MgooOrders.OrderStatus.已付款),
new SqlParameter("PayDate", time_end),
new SqlParameter("BankType", bank_type),
new SqlParameter("TradeType", trade_type),
new SqlParameter("OrderNo", trade_no),
new SqlParameter("Status1", MgooOrders.OrderStatus.已提交)
});
if (reslut > 0)
{
try
{
if (updateExpireDate)
{
strSql = "update Devices set HireExpireDate=DATEADD(YEAR,(select RenewalDate from TariffPackages where id =(select TariffID from Orders where OrderNo=@OrderNo)),HireExpireDate) where DeviceID=(select deviceid from Orders where OrderNo=@OrderNo)";
reslut = s.ExecuteSql(strSql, new SqlParameter[] { new SqlParameter("OrderNo", trade_no) });
if (reslut <= 0)
{
Utils.log("修改设备过期时间失败 ModifyOrderStatus Error......." + reslut);
return(false);
}
}
return(true);
}
catch (Exception e)
{
Utils.log("ModifyOrderStatus Error:" + e.Message);
}
}
else
{
Utils.log("未找到该订单:" + trade_no);
}
}
catch (Exception ex)
{
Utils.log("ModifyOrderStatus Error:" + ex.Message);
}
return(false);
}
private static void QueryExpireDevices()
{
//30天内一个星期推送一次，7天内一天推送一次
try
{
//发送推送给30天内过期并且在七天内没有发送过推送的设备，APP平台
MG_DAL.SQLServerOperating s = new MG_DAL.SQLServerOperating(connectionString);
string strSql = @"SELECT d.UserID, d.DeviceID, d.DeviceName, d.SerialNumber, d.HireExpireDate, ma.ClientID, ma.OS, a.PackageName, a.AppID, a.AppKey, a.AppSecret, epr.AppLastPushTime
FROM dbo.Devices AS d INNER JOIN
dbo.MobileAppInfo AS ma ON ma.UserID = d.UserID INNER JOIN
dbo.Apps AS a ON a.ID = ma.AppsID LEFT OUTER JOIN
dbo.ExpiredPushRecord AS epr ON epr.UserID = d.UserID
WHERE d.Deleted=0
AND d.HireExpireDate > GETDATE()
AND(a.PackageName <> 'HBuilder')
AND ma.LastDate>DATEADD(MM,-2,GETDATE()) ";
//AND d.UserID in (6,7)
//一个月内过期的设备并且是7天内没有推送过期通知的设备
string monthWhere = " AND d.HireExpireDate < DATEADD(MM, 1, GETDATE()) AND(DATEDIFF(day, epr.AppLastPushTime, GETDATE()) > 7 or epr.AppLastPushTime IS NULL) ";
//7天内过期的设备，一天推送一次
string weekWhere = " AND d.HireExpireDate < DATEADD(DAY, 7, GETDATE()) AND(DATEDIFF(day, epr.AppLastPushTime, GETDATE()) >= 1 or epr.AppLastPushTime IS NULL) ";
/// 查询7天内过期的设备进行APP推送
DataTable dt = s.Selects(strSql + weekWhere);
MG_BLL.App.XiaoMiPush.Push push = new MG_BLL.App.XiaoMiPush.Push();
foreach (DataRow row in dt.Rows)
{
string DeviceName = string.IsNullOrEmpty(row["DeviceName"].ToString()) ? row["SerialNumber"].ToString() : row["DeviceName"].ToString();
push.ExpiredPush(row["PackageName"].ToString(), row["AppSecret"].ToString(), row["ClientID"].ToString(), row["OS"].ToString(), row["DeviceID"].ToString(), DeviceName, row["HireExpireDate"].ToString());
ExpiredPushRecord(row["UserID"].ToString(), row["DeviceID"].ToString(), row["HireExpireDate"].ToString(), "app");
}
//查询30天内过期的设备并且在7天内没有进行过推送的设备进行APP推送
dt = s.Selects(strSql + monthWhere);
foreach (DataRow row in dt.Rows)
{
string DeviceName = string.IsNullOrEmpty(row["DeviceName"].ToString()) ? row["SerialNumber"].ToString() : row["DeviceName"].ToString();
push.ExpiredPush(row["PackageName"].ToString(), row["AppSecret"].ToString(), row["ClientID"].ToString(), row["OS"].ToString(), row["DeviceID"].ToString(), DeviceName, row["HireExpireDate"].ToString());
ExpiredPushRecord(row["UserID"].ToString(), row["DeviceID"].ToString(), row["HireExpireDate"].ToString(), "app");
}
}
catch (Exception ex)
{
Utils.log("QueryExpireDevices Error:" + ex.Message, log_name);
}
}
private string GetDeviceTracking(int DeviceID)
{
MG_DAL.SQLServerOperating sqlHelper = new MG_DAL.SQLServerOperating();
string strSql = @"select d.DeviceID,d.SerialNumber,DeviceName,l.OLat,l.OLng,l.LastCommunication DeviceDate,l.Speed,l.Course,l.DataContext ,g.Radius,g.Latitude,g.LongItude,g.GeofenceID
from devices d inner join LKLocation l on l.DeviceID=d.DeviceiD left join GeoFence g on g.DeviceID=d.DeviceID where d.DeviceID =@DeviceID and d.deleted=0";// model=213 and
Dictionary <string, string> dic = sqlHelper.Selects(strSql, new SqlParameter[] { new SqlParameter("DeviceID", DeviceID) }).toDictionary();
Geocoding geo = new Amap();
Gps gps = geo.Translate(dic["OLat"], dic["OLng"]);
//Gps gps = Utils.gps84_To_Gcj02(dic["OLat"], dic["OLng"]);
dic["OLat"] = gps.getWgLat().ToString();
dic["OLng"] = gps.getWgLon().ToString();
dic["Address"] = gps.Address;
gps = geo.Translate(dic["Latitude"], dic["LongItude"], false);
dic["Latitude"] = gps.getWgLat().ToString();
dic["LongItude"] = gps.getWgLon().ToString();
dic["CourseName"] = Utils.GetCoureName(dic["Course"]);
var dc = dic["DataContext"];
var doorStatus = "未知";
if (dc.Split('-').Length > 3 && !string.IsNullOrEmpty(dc.Split('-')[3]))
{
dc = dc.Split('-')[3];
if (dc.Equals("0"))
{
doorStatus = "打开";
}
else
{
doorStatus = "关闭";
}
}
dic["DataContext"] = doorStatus;// = dic["DataContext"].Split('-')[3]; //0--- 主电断开, 1-----主电连接
return(Utils.ToJson(dic));
}
private static void WeChatExpirePush()
{
try
{
MG_DAL.SQLServerOperating s = new MG_DAL.SQLServerOperating(connectionString);
string strSql = @"select chat.OpenID,chat.UserID,d.DeviceName,d.DeviceID,d.SerialNumber,d.HireExpireDate,epr.WeChatLastPushTime from
WeChatUsers chat inner join Devices d on d.UserID=chat.UserID
LEFT OUTER JOIN dbo.ExpiredPushRecord AS epr ON epr.DeviceID = d.DeviceID
where d.Deleted = 0
AND d.HireExpireDate > GETDATE()
AND chat.UpdateTime>DATEADD(MM,-2,GETDATE())
";/// AND d.UserID in (6,7)
//一个月内过期的设备并且是7天内没有推送过期通知的设备
string monthWhere = " AND d.HireExpireDate < DATEADD(MM, 1, GETDATE()) AND(DATEDIFF(day, epr.WeChatLastPushTime, GETDATE()) >= 7 or epr.WeChatLastPushTime IS NULL) ";
//7天内过期的设备，一天推送一次
string weekWhere = " AND d.HireExpireDate < DATEADD(DAY, 7, GETDATE()) AND(DATEDIFF(day, epr.WeChatLastPushTime, GETDATE()) >= 1 or epr.WeChatLastPushTime IS NULL) ";
string logText = "";
DataTable dt = s.Selects(strSql + weekWhere);
logText = "7天内过期设备： " + dt.Rows.Count + " 台";
WeixinOper wo = new WeixinOper();
string access_token = wo.AccessToken();
MgoogpsWebClient mwc = new MgoogpsWebClient();
mwc.RequestUrl = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + access_token;
string rulst = "";
foreach (DataRow row in dt.Rows)
{
string DeviceName = string.IsNullOrEmpty(row["DeviceName"].ToString()) ? row["SerialNumber"].ToString() : row["DeviceName"].ToString();
string pushContent = wo.GetExpiresPushText(row["OpenID"].ToString(), row["DeviceID"].ToString(), DeviceName, row["HireExpireDate"].ToString());
mwc.RequestPostData = Encoding.UTF8.GetBytes(pushContent);
rulst = mwc.RequestSend();
Console.WriteLine(rulst);
Dictionary <string, string> res = Utils.ToDictionary(rulst);
if (res["errcode"].Equals("0") && res["errmsg"].Equals("ok"))
{
ExpiredPushRecord(row["UserID"].ToString(), row["DeviceID"].ToString(), row["HireExpireDate"].ToString(), "wechat");
}
else
{
Utils.log("7天内过期推送失败" + rulst, log_name);
}
}
dt = s.Selects(strSql + monthWhere);
logText += " 30天内过期设备： " + dt.Rows.Count + " 台";
foreach (DataRow row in dt.Rows)
{
string DeviceName = string.IsNullOrEmpty(row["DeviceName"].ToString()) ? row["SerialNumber"].ToString() : row["DeviceName"].ToString();
string pushContent = wo.GetExpiresPushText(row["OpenID"].ToString(), row["DeviceID"].ToString(), DeviceName, row["HireExpireDate"].ToString());
mwc.RequestPostData = Encoding.UTF8.GetBytes(pushContent);
rulst = mwc.RequestSend();
Dictionary <string, string> res = Utils.ToDictionary(rulst);
if (res["errcode"].Equals("0") && res["errmsg"].Equals("ok"))
{
ExpiredPushRecord(row["UserID"].ToString(), row["DeviceID"].ToString(), row["HireExpireDate"].ToString(), "wechat");
}
else
{
Utils.log("30天内过期推送失败" + rulst, log_name);
}
}
Utils.log(logText, log_name);
}
catch (Exception ex)
{
Utils.log("WeChatExpirePush Error:" + ex.Message, log_name);
}
}