public void UnifiedOrder(string deviceid, string tariffid, string tradetype)
{
int tariff_id = int.Parse(tariffid);
SQLServerOperating s = new SQLServerOperating();
DataTable dt = s.Selects(" select ID, TariffName, Price, OldPrice, BuyCount, Type from TariffPackages where ID=@tariff_id", new SqlParameter[] { new SqlParameter("tariff_id", tariff_id) });
if (dt.Rows.Count <= 0)
{
return;
}
s.ExecuteSql("update TariffPackages set BuyCount=BuyCount+cast( ceiling(rand()*100) as int) where ID=@ID", new SqlParameter[] { new SqlParameter("ID", tariff_id) });
s.Selects(" select ID, TariffName, Price, OldPrice, BuyCount, Type from TariffPackages where ID=@tariff_id", new SqlParameter[] { new SqlParameter("tariff_id", tariff_id) });
string total_fee = dt.Rows[0]["Price"].toStringEmpty();
string tariff_name = dt.Rows[0]["TariffName"].toStringEmpty();
string device_name = s.Select("select case when DeviceName='' then SerialNumber else DeviceName end from Devices where deviceid=@deviceid", new SqlParameter[] { new SqlParameter("deviceid", deviceid) });
if (string.IsNullOrEmpty(device_name))
{
return;
}
tariff_name = "GPS移动流量-" + device_name + " 充值" + tariff_name;
JsApiPay jsApiPay = new JsApiPay();
jsApiPay.user_id = int.Parse(myHeader.UserID);
jsApiPay.openid = "";
jsApiPay.total_fee = jsApiPay.user_id == 6 || jsApiPay.user_id == 7 ? new Random().Next(1, 10) : int.Parse(total_fee);
jsApiPay.device_id = int.Parse(deviceid);
jsApiPay.tariff_id = tariff_id;
jsApiPay.product_body = tariff_name;
WxPayData unifiedOrderResult = jsApiPay.GetUnifiedOrderResult();
jsApiPay.InsertMgooOrder();
// AddOrder(myHeader.UserID,deviceid,"",);
}
/// <summary>
/// 获取所有的报警类型
/// </summary>
/// <returns></returns>
public List <Dictionary <string, string> > GetExceptionType()
{
SQLServerOperating s = new SQLServerOperating();
String strSql = "select Message,NotificationType as ID from ExceptionMessage group by Message,NotificationType";
return(s.Selects(strSql).toListDictionary());
}
private static void OffLineMessagePush(string date)
{
try
{
//AccountID 1有线，2无线
string strSql = @"select em.ExceptionID,d.DeviceID,em.NotificationType,em.Message,em.Created,em.OLat,em.OLng ,d.UserID ,
case when d.DeviceName ='' then d.SerialNumber else d.DeviceName end DeviceName
from ExceptionMessage em inner join Devices d on d.DeviceID=em.DeviceID inner join Dictionary di on di.DataValue =d.Model
where convert(varchar(16), dateadd(HH, 8, em.Created), 20) = convert(varchar(16), dateadd(mi,case when di.AccountID=1 then -di.SortOrder else 1450 end ,@date),20) and notificationtype = 9 ";
SQLServerOperating s = new SQLServerOperating(connectionString);
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("date", date) });
WeixinOper wo = new WeixinOper(s);
int index = 0;
// Console.WriteLine(dt.Rows.Count);
foreach (DataRow row in dt.Rows)
{
index++;
string res = wo.StartPush(row["UserID"].ToString(), row["ExceptionID"].ToString(), row["DeviceName"].ToString(), row["Message"].ToString(), row["Created"].ToString());
// Console.WriteLine(index+":"+res);
}
}
catch (Exception ex)
{
Utils.log("OffLineMessagePush Error:" + ex.Message + " StackTrace:" + ex.StackTrace + " Source:" + ex.Source, log_name);
}
}
public List <Dictionary <string, string> > GetMessageTypeList()
{
List <Dictionary <string, string> > list = new List <Dictionary <string, string> >();
try
{
string strSql = " select [Message],[NotificationType] as ID from ExceptionMessage e inner join Devices d on d.DeviceID=e.DeviceID where d.UserID=@UserID group by [Message],[NotificationType] order by ID";
SQLServerOperating s = new SQLServerOperating();
DataTable msgTypeDataTable = s.Selects(strSql, new SqlParameter[] { new SqlParameter("UserID", myHeader.UserID) });
string pushMsgType = s.Select("select PushMsgType from [UsersConfig] where UserID=@UserID", new SqlParameter[] { new SqlParameter("UserID", myHeader.UserID) });
string[] msgList = pushMsgType.Split(',');
foreach (DataRow row in msgTypeDataTable.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
foreach (DataColumn col in msgTypeDataTable.Columns)
{
dic[col.ColumnName] = row[col.ColumnName].ToString();
}
if (msgList.Contains(row["ID"].ToString()))
{
dic["IsPush"] = "0";//推送
}
else
{
dic["IsPush"] = "1";//不推送
}
list.Add(dic);
}
}
catch (Exception e)
{
Utils.log("GetMessageTypeList Error:" + e.Message);
}
return(list);
}
public List <Dictionary <string, string> > GetOrderList(int userid, string orderno = null)
{
var list = new List <Dictionary <string, string> >();
try
{
List <SqlParameter> pars = new List <SqlParameter>();
string where = "";
if (!string.IsNullOrEmpty(orderno))
{
where = " and OrderNo=@OrderNo";
pars.Add(new SqlParameter("OrderNo", orderno));
}
string strSql = "select OrderID, o.UserID,d.DeviceName,d.SerialNumber, o.DeviceID, OpenID, OrderNo, TransactionNo, ProductBody, FeeType ," +
"TotalFee, o.Created, OrderDate, OrderExpire, PayDate, TradeType, o.Status, BankType, TariffID, BillCreateIP " +
" from Orders o inner join devices d on o.DeviceID=d.DeviceID " +
"where o.userid = @userid " + where + " order by orderID desc";
pars.Add(new SqlParameter("userid", userid));
SQLServerOperating s = new SQLServerOperating();
list = s.Selects(strSql, pars.ToArray()).toListDictionary();
return(list);
}
catch (Exception ex)
{
Common.Log.Error(this, ex);
return(list);
}
}
public void PaySuccessPush(string OpenID, string DeviceName, string Fee, string PayDate, string Bank, string OrderNo)
{
string rulst = "";
try
{
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;
SQLServerOperating s = new SQLServerOperating();
Dictionary <string, string> dic = s.Selects("select ProductBody,tp.RenewalDate from Orders o inner join TariffPackages tp on tp.id=o.TariffID where OrderNo = @OrderNo", new SqlParameter[] { new SqlParameter("OrderNo", OrderNo) }).toDictionary();
string pushContent = wo.GetPaySuccessText(OpenID, DeviceName, Fee, PayDate, OrderNo, Bank, dic["ProductBody"], dic["RenewalDate"]);
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"))
{
Utils.log("充值成功提醒推送成功:" + DeviceName);
}
else
{
Utils.log("充值成功微信推送失败" + rulst, "WeChatExpirePush.log");
}
}
catch (Exception ex)
{
Utils.log("PaySuccessPush Error:" + ex.Message + ",StackTrace:" + ex.StackTrace + ",Source:" + ex.Source + ",rulst：" + rulst);
}
}
private string GetDeviceInfo(int DeviceID)
{
string strSql = @"select d.DeviceID, SerialNumber, DeviceName,CarUserName,PhoneNum, CarNum, CellPhone,ActiveDate,HireExpireDate,di.DataText
from Devices d inner join Dictionary di on d.Model = di.DataValue where d.Deleted = 0 and d.DeviceID = @DeviceID ";
SQLServerOperating sqlHelper = new SQLServerOperating();
return(Utils.ToJson(sqlHelper.Selects(strSql, new SqlParameter[] { new SqlParameter("DeviceID", DeviceID) }).toDictionary()));
}
private string UnlockList(int DeviceID)
{
string strSql = @" select row_number() over(order by CreateDate desc) rowIndex,DeviceID SerialNumber,CreateDate,IsResponse,CommandName from CarCommandQueue
where deviceid = (select SerialNumber from devices where deviceid = @deviceid )";
SQLServerOperating sqlHelper = new SQLServerOperating();
var list = sqlHelper.Selects(strSql, new SqlParameter[] { new SqlParameter("deviceid", DeviceID) }).toListDictionary();
return(Utils.ToJson(list));
}
public Dictionary <string, string> GetExceptionByID(string ExceptionID)
{
string strSql = @"select d.UserID,u.UserName,ISNULL(d.ServerID2,1)ServerID2,case when d.DeviceName = '' then d.SerialNumber else d.DeviceName end DeviceName,em.DeviceID,d.Model,d.CellPhone DeviceCellPhone,d.SerialNumber,
case when geo.FenceName is null then em.Message else em.Message+':'+geo.FenceName end Message,em.NotificationType,DATEADD(HH,8,em.Created) Created,em.OLat,em.OLng ,u.CellPhone
from ExceptionMessage em inner join Devices d on em.DeviceID=d.DeviceID inner join Users u on u.UserID=d.UserID left join GeoFence geo on geo.GeofenceID=em.GeoFenceID where ExceptionID = @ExceptionID";
SQLServerOperating s = GetSQLServerOperating();
return(s.Selects(strSql, new SqlParameter[] { new SqlParameter("ExceptionID", ExceptionID) }).toDictionary());
}
public void push(string UserID)
{
try
{
string strSql = "select ClientID,Token,AppID,AppKey,MasterSecret,AppSecret,PackageName,m.OS,uc.PushShock,uc.PushAudio from MobileAppInfo m inner join apps a on m.AppsID=a.ID inner join usersconfig uc on uc.UserID=m.UserID where m.UserID=@userid and a.PackageName!='HBuilder' and m.LastDate>dateadd(MM,-1, getdate())";
SQLServerOperating s = GetSQLServerOperating();
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("userid", UserID) });
int len = dt.Rows.Count;
if (len > 0)
{
// GetuiPush.Push push = new GetuiPush.Push();
XiaoMiPush.Push push = new XiaoMiPush.Push();
push.PushAudio = dt.Rows[0]["PushAudio"].toStringEmpty() == "0";
push.PushShock = dt.Rows[0]["PushShock"].toStringEmpty() == "0";
// if (push.PushShock && push.PushShock)
push.DefaulSound = -1; //声音 并且 震动
if (push.PushAudio && !push.PushShock)
{
push.DefaulSound = 1; //声音，不震动
}
else if (!push.PushAudio && !push.PushShock)
{
push.DefaulSound = 2; //只震动
}
else if (!push.PushAudio && !push.PushShock)
{
push.DefaulSound = 4; //LED灯光提示，没有声音没有震动
}
List <string> regids = new List <string>();
List <string> packages = new List <string>();
List <Task> taskList = new List <Task>();
TaskFactory taskFactory = new TaskFactory();
for (int i = 0; i < len; i++)
{
int index = i;
Task task = taskFactory.StartNew(() =>
{
DataRow row = dt.Rows[index];
regids.Add(row["ClientID"].ToString());
packages.Add(row["PackageName"].ToString());
push.PushMessageToSingle(row["ClientID"].ToString(), Title, this.Text, row["AppSecret"].ToString(), row["PackageName"].ToString(), this.NotificationType, row["OS"].ToString().ToLower(), this.ExceptionID);
});
taskList.Add(task);
}
//Task.WaitAll(taskList.ToArray());
}
}
catch (Exception ex)
{
// Console.WriteLine(ex.Message);
Utils.log("AppPush push Error:" + ex.Message + ",Source:" + ex.Source + ",StackTrace" + ex.StackTrace);
}
}
public LoginUserInfo Login_Imei(string LoginName, string PassWord, string Identifies)
{
try
{
string strSql = "select DeviceID,SerialNumber, DeviceName, UserID,DevicePassword from devices where serialnumber = @serialnumber and deleted =0";
SQLServerOperating s = new SQLServerOperating();
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("serialnumber", LoginName) });
if (dt.Rows.Count > 0)
{
DataRow loginUserDic = dt.Rows[0];
// 加密
//string EncryptPWD = FormsAuthentication.HashPasswordForStoringInConfigFile(loginUserDic["DevicePassword"].toStringEmpty(), "MD5");
string EncryptPWD = Utils.GetMD5(loginUserDic["DevicePassword"].toStringEmpty());
if (EncryptPWD.Equals(PassWord.ToLower()))
{
LoginUserInfo _loginUserInfo = new LoginUserInfo();
_loginUserInfo.DeviceID = loginUserDic["DeviceID"].toStringEmpty();
_loginUserInfo.UserID = loginUserDic["UserID"].toStringEmpty();
_loginUserInfo.UserName = loginUserDic["DeviceName"].toStringEmpty();
_loginUserInfo.DeviceName = loginUserDic["DeviceName"].toStringEmpty();
_loginUserInfo.LoginTime = DateTime.Now;
_loginUserInfo.Identifies = Identifies;
_loginUserInfo.LoginType = LoginType.Imei;
_loginUserInfo.ToKen = Guid.NewGuid().toStringEmpty().Replace("-", "").ToLower();
if (Identifies.Split('@').Length == 2)
{
string mt = Identifies.Split('@')[1];
if (mt.ToUpper() == "BAIDU")
{
_loginUserInfo.MapType = MapType.BAIDU;
}
else
{
_loginUserInfo.MapType = MapType.AMAP;
}
}
else
{
_loginUserInfo.MapType = MapType.AMAP;
}
return(_loginUserInfo);
}
}
}
catch (Exception ex)
{
Utils.log("Login_Imei Error:" + ex.Message);
}
return(null);
}
public bool FilterMsgType(string UserID, string NotificationType)
{
try
{
string strSql = string.Format(" select PushAudio,PushShock,PushPeriod from UsersConfig where UserID=@UserID and (PushMsgType like '%,{0},%')", NotificationType);
SQLServerOperating s = GetSQLServerOperating();
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("UserID", UserID) });
if (dt.Rows.Count > 0)
{
string period = dt.Rows[0]["PushPeriod"].ToString();
//1 是全天接收
if (period == "1")
{
return(true);
}
var now = DateTime.Now;
var date1 = default(DateTime);
var date2 = default(DateTime);
//晚上接收
if (period == "2")
{
date1 = now.ToString("yyyy-MM-dd 20:00:00").toDateTime();
date2 = now.AddDays(1).ToString("yyyy-MM-dd 07:59:59").toDateTime();
if (now >= date1 && now <= date2)
{
return(true);
}
Utils.log("免打扰时间-晚上接收: UserID:" + UserID + ",NotificationType:" + NotificationType);
}
//白天接收
if (period == "3")
{
date1 = now.ToString("yyyy-MM-dd 08:00:00").toDateTime();
date2 = now.ToString("yyyy-MM-dd 19:59:59").toDateTime();
if (now >= date1 && now <= date2)
{
return(true);
}
Utils.log("免打扰时间-白天接收: UserID:" + UserID + ",NotificationType:" + NotificationType);
}
return(false);
}
}
catch (Exception ex)
{
Utils.log("FilterMsgType Error:" + ex.Message);
}
return(false);
}
public Dictionary <string, string> GetMessageInfoByID(string eid)
{
try
{
SQLServerOperating s = new SQLServerOperating();
string strSql = @"select CASE when d.devicename='' then d.SerialNumber else d.DeviceName end DeviceName,dateadd(HH,8, em.created) Created,em.OLat,em.OLng ,
case when geo.FenceName is null then em.Message else em.Message+':'+geo.FenceName end Message
from ExceptionMessage em inner join Devices d on d.DeviceID=em.DeviceID
left join GeoFence geo on geo.GeofenceID=em.GeoFenceID
where ExceptionID=@ExceptionID";
DataTable table = s.Selects(strSql, new SqlParameter[] { new SqlParameter("ExceptionID", eid) });
Dictionary <string, string> dic = new Dictionary <string, string>();
Geocoding geo = GetCurrentMapType();
foreach (DataRow row in table.Rows)
{
foreach (DataColumn dc in table.Columns)
{
dic[dc.ColumnName] = row[dc.ColumnName].toStringEmpty();
}
}
Mgoo.Position.IGeocoding geocoding = null;
if (geo.GetType().Name.ToLower() == "baidu")
{
geocoding = new Mgoo.Position.Geocod.Baidu();
}
else if (geo.GetType().Name.ToLower() == "amap")
{
geocoding = new Mgoo.Position.Geocod.Amap();
}
else
{
geocoding = new Mgoo.Position.Geocod.Google();
}
var point = geocoding.Translate(dic["OLat"].toDouble(), dic["OLng"].toDouble());
var task = Task.Run(() => {
return(geocoding.GetAddress(point));
});
dic["OLat"] = point.Lat.ToString();
dic["OLng"] = point.Lng.ToString();
dic["Address"] = task.Result;
return(dic);
}
catch (Exception ex)
{
Utils.log("Message>GetMessageInfoByID ERROR:" + ex.Message);
return(new Dictionary <string, string>());
}
}
public List <Dictionary <string, string> > GetMessageByDeviceID(int DeviceID, int second)
{
try
{
string strSql = $"select top 5 ExceptionID,DeviceID,Message,Created from [ExceptionMessage] where DeviceID=@DeviceID and created > dateadd(ss,@second,dateadd(HH,-8, getdate()))";
SQLServerOperating s = new SQLServerOperating();
SqlParameter[] par = new SqlParameter[] { new SqlParameter("DeviceID", DeviceID), new SqlParameter("second", second - second * 2) };
List <Dictionary <string, string> > list = s.Selects(strSql, par).toListDictionary();
return(list);
}
catch (Exception ex)
{
Utils.log("GetMessageByDeviceID:" + ex.Message);
return(new List <Dictionary <string, string> >());
}
}
public List <Dictionary <string, string> > GetMessageByDeviceID(string currentIndex, string pageCount, string deviceid, string type)
{
try
{
if (string.IsNullOrEmpty(currentIndex) || currentIndex == "undefined")
{
return(new List <Dictionary <string, string> >());
}
if (string.IsNullOrEmpty(type))
{
type = "2";
}
string where = "";
if (type.Equals("0")) //查看未删除的
{
where = " and ex.Deleted = 0 ";
}
else if (type.Equals("1")) //查看已删除的
{
where = " and ex.Deleted = 1 ";
}
else if (type.Equals("2"))//查看全部报警信息
{
}
else
{
return(new List <Dictionary <string, string> >());
}
string strSql = string.Format(@"select top {0} * from(
select row_number() over(order by ex.[Created] desc) rowIndex, ex.ExceptionID, CASE when d.devicename = '' then d.SerialNumber else d.DeviceName end DeviceName,
d.SerialNumber, d.DeviceID,case when geo.FenceName is null then ex.Message else ex.Message+':'+geo.FenceName end Message, dateadd(HH, 8, ex.created) Created ,ex.ClearBy,ex.ClearDate
from ExceptionMessage ex inner join devices d on d.deviceid = ex.deviceid
left join GeoFence geo on geo.GeofenceID=ex.GeoFenceID
where d.DeviceID = @DeviceID " + where + @"
) t where rowIndex > {0} * ({1} - 1) order by Created desc", pageCount, currentIndex);
SQLServerOperating s = new SQLServerOperating();
return(s.Selects(strSql, new SqlParameter[] { new SqlParameter("DeviceID", deviceid) }).toListDictionary());
}
catch (Exception ex)
{
Utils.log("Message>GetMessageByDeviceID ERROR:" + ex.Message);
return(new List <Dictionary <string, string> >());
}
}
public string GetUsersInfoByID(string userid)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
try
{
if (myHeader.UserID.Equals(userid))
{
string strSql = "select UserName,LoginName,FirstName,CellPhone,PrimaryEmail,Address1 [Address] from users where userid=@userid";
SQLServerOperating s = new SQLServerOperating();
dic = s.Selects(strSql, new SqlParameter[] { new SqlParameter("userid", userid) }).toDictionary();
}
return(Utils.ToJson(dic));
}
catch (Exception)
{
return(Utils.ToJson(dic));
}
}
public Dictionary <string, string> GetUsersConfig()
{
Dictionary <string, string> dic = new Dictionary <string, string>();
try
{
string strSql = @"select ISNULL(uc.PushAudio,0) Audio,ISNULL(uc.PushShock,0) Shock,ISNULL(ShockSens,0) ShockSens ,ISNULL(PushPeriod,1) Period
from users u left join UsersConfig uc on uc.UserID=u.UserID
where u.userid = @userid";
SQLServerOperating s = new SQLServerOperating();
List <Dictionary <string, string> > list = s.Selects(strSql, new SqlParameter[] { new SqlParameter("userid", myHeader.UserID) }).toListDictionary();
dic = list[0];
}
catch (Exception ex)
{
Utils.log("GetUsersConfig Error:" + ex.Message);
}
return(dic);
}
private string GetMessageList(string currentIndex, string pageCount, int deviceid, string type)
{
var list = new List <Dictionary <string, string> > ();
try
{
if (string.IsNullOrEmpty(type))
{
type = "2";
}
string where = "";
if (type.Equals("0")) //查看未删除的
{
where = " and e.Deleted = 0 ";
}
else if (type.Equals("1")) //查看已删除的
{
where = " and e.Deleted = 1 ";
}
else if (type.Equals("2"))//查看全部报警信息
{
}
else
{
return(Utils.ToJson(list));
}
string strSql = string.Format(@"select top {0} * from(
select row_number() over(order by e.[Created] desc) rowIndex,
e.[ExceptionID], case when geo.FenceName is null then e.Message else e.Message+':'+geo.FenceName end Message , dateadd(HH,8, e.[Created])[Created], d.DeviceName, d.SerialNumber, e.ClearDate, e.ClearBy
from ExceptionMessage e inner join Devices d on e.DeviceID = d.DeviceID
left join GeoFence geo on geo.GeofenceID=e.GeoFenceID
where d.deleted =0 and d.DeviceID=@DeviceID " + where + @"
) t where rowIndex > {0} * ({1} - 1) order by Created desc" , pageCount, currentIndex);
SQLServerOperating s = new SQLServerOperating();
return(Utils.ToJson(s.Selects(strSql, new SqlParameter[] { new SqlParameter("DeviceID", deviceid) }).toListDictionary()));
}
catch (Exception ex)
{
// Utils.log("GetMessageList Error:" + ex.Message);
return(Utils.ToJson(list));
}
}
private string GetHistory(int deviceid, string startdate, string enddate)
{
var list = new List <Dictionary <string, string> >();
string speedfilter = "2";
if (string.IsNullOrEmpty(deviceid.ToString()) || string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate))
{
return(Utils.ToJson(list));
}
if (string.IsNullOrEmpty(speedfilter))
{
speedfilter = Utils.SpeedFilter.toStringEmpty();
}
try
{
DateTime startTime = Convert.ToDateTime(startdate);
DateTime endTime = Convert.ToDateTime(enddate);
if (startTime >= endTime)
{
return(Utils.ToJson(list));
}
TimeSpan ts = endTime - startTime;
double days = Math.Ceiling(ts.TotalDays);
//一次最多只能看5天的数据
if (days > 5)
{
return(Utils.ToJson(list));
}
string DataBaseBefore = "YWData";// ConfigurationManager.AppSettings["DataBaseName"].ToStringEmpty();
StringBuilder strSql = new StringBuilder();
string startTimeUtc = startTime.AddHours(-8).ToString("yyyy-MM-dd HH:mm:ss");
string endTimeUtc = endTime.AddHours(-8).ToString("yyyy-MM-dd HH:mm:ss");
strSql.Append(" select DeviceTime, OLat, OLng, Speed, Course from ( ");
string where = " where speed > @speedfilter and deviceid=@deviceid and DeviceUTCTime>@startTimeUtc and DeviceUTCTime<@endTimeUtc";
for (int i = 0; i < days; i++)
{
string DateBase = DataBaseBefore + startTime.ToString("yyyyMM");
int TableName = Convert.ToInt32(startTime.ToString("dd"));
strSql.Append("select dateadd(HH,8,DeviceUTCTime)DeviceTime, OLat, OLng, Speed, Course from [" + DateBase + "].[dbo].[Location" + TableName + @"]");
strSql.Append(where);
if (i != days - 1)
{
strSql.Append(" union all ");
}
startTime = startTime.AddDays(1);
}
strSql.Append(" )t order by DeviceTime");
SQLServerOperating s = new SQLServerOperating();
string model = s.Select("select di.DataText from Devices d inner join Dictionary di on di.DataValue=d.Model where DeviceID=@DeviceID", new SqlParameter[] { new SqlParameter("DeviceID", deviceid) });
if (model.EndsWith("W") || model.EndsWith("WD") || model.EndsWith("WF"))
{
speedfilter = "-1";
}
SqlParameter[] pars = new SqlParameter[] {
new SqlParameter("speedfilter", speedfilter),
new SqlParameter("deviceid", deviceid),
new SqlParameter("startTimeUtc", startTimeUtc),
new SqlParameter("endTimeUtc", endTimeUtc)
};
DataTable dt = s.Selects(strSql.ToString(), pars);
//List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
Geocoding geo = new Amap();
foreach (DataRow row in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
foreach (DataColumn dc in dt.Columns)
{
dic[dc.ColumnName] = row[dc.ColumnName].toStringEmpty();
}
Gps gps = geo.Translate(dic["OLat"], dic["OLng"], false);
var listWhere = list.Where(l => l.ContainsValue(gps.getWgLat().ToString()) && l.ContainsValue(gps.getWgLon().ToString()));
if (listWhere.Count() > 0)
{
continue;
}
dic["OLat"] = gps.getWgLat().toStringEmpty();
dic["OLng"] = gps.getWgLon().toStringEmpty();
list.Add(dic);
}
return(Utils.ToJson(list));
}
catch (Exception ex)
{
Utils.log("GetHistoryLocus Error2:" + ex.Message + ",堆栈信息:" + ex.StackTrace + "," + deviceid + "-" + startdate + "-" + enddate);
return(Utils.ToJson(list));
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
userid = Request.QueryString["userid"];
total_fee = Request.QueryString["total_fee"];
deviceid = Request.QueryString["deviceid"];
string openid = Request.QueryString["openid"];
type = Request.QueryString["type"];
imei = Request.QueryString["imei"];
string tid = Request.QueryString["tariff_id"];
if (type == "1")
{
tid = "7";
userid = "7";
total_fee = "1";
}
else
{
type = "2";
}
//检测是否给当前页面传递了相关参数string.IsNullOrEmpty(openid) ||
if (string.IsNullOrEmpty(total_fee) || string.IsNullOrEmpty(deviceid) || string.IsNullOrEmpty(tid) || string.IsNullOrEmpty(openid) || string.IsNullOrEmpty(userid))
{
state = "页面传参出错,请返回重试";
//Response.Write("<span style='color:#FF0000;font-size:20px'>" + "页面传参出错,请返回重试" + "</span>");
Log.Error(this.GetType().ToString(), "This page have not get params, cannot be inited, exit...");
//submit.Visible = false;
return;
}
int tariff_id = int.Parse(tid);
SQLServerOperating s = new SQLServerOperating();
// DataTable dt= s.Selects(" select ID, TariffName, Price, OldPrice, BuyCount, Type from TariffPackages where ID=@tariff_id", new SqlParameter[] { new SqlParameter("tariff_id", tariff_id) });
string strSql = @"update TariffPackages set BuyCount=BuyCount+cast( ceiling(rand()*100) as int) where ID=@tariff_id;
select ID, TariffName, Price, OldPrice, BuyCount, Type,
(select case when DeviceName='' then SerialNumber else DeviceName end from Devices where deviceid=@deviceid) DeviceName
from TariffPackages where ID=@tariff_id ";
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("tariff_id", tariff_id), new SqlParameter("deviceid", deviceid) });
if (dt.Rows.Count <= 0)
{
state = "页面传参出错,请返回重试";
return;
}
//s.ExecuteSql("update TariffPackages set BuyCount=BuyCount+cast( ceiling(rand()*100) as int) where ID=@ID", new SqlParameter[] { new SqlParameter("ID", tariff_id) });
total_fee = dt.Rows[0]["Price"].toStringEmpty();
tariff_name = dt.Rows[0]["TariffName"].toStringEmpty();
device_name = dt.Rows[0]["DeviceName"].toStringEmpty(); // s.Select("", new SqlParameter[] { new SqlParameter("deviceid", deviceid) });
if (string.IsNullOrEmpty(device_name))
{
return;
}
tariff_name = "GPS移动流量-" + device_name + " 充值" + tariff_name;
//若传递了相关参数，则调统一下单接口，获得后续相关接口的入口参数
JsApiPay jsApiPay = new JsApiPay(this);
//JSAPI支付预处理
try
{
// total_fee = ( type == "1" ? int.Parse(total_fee) :int.Parse( total_fee) )+"";
jsApiPay.user_id = int.Parse(userid);
jsApiPay.openid = openid;
jsApiPay.total_fee = jsApiPay.user_id == 6 || jsApiPay.user_id == 7 ? new Random().Next(1, 10) : Convert.ToInt32(total_fee);
jsApiPay.device_id = int.Parse(deviceid);
jsApiPay.tariff_id = tariff_id;
jsApiPay.product_body = tariff_name;
jsApiPay.device_name = device_name;
WxPayData unifiedOrderResult = jsApiPay.GetUnifiedOrderResult();
wxJsApiParam = jsApiPay.GetJsApiParameters();//获取H5调起JS API参数
jsApiPay.InsertMgooOrder();
//下单成功后的内部订单号
order_no = jsApiPay.order_no;
callback_url = "http://m.mgoogps.com:8070/pay/pay_success.aspx?no=" + order_no.ToString() + "&deviceid=" + deviceid + "&t=" + DateTime.Now.Ticks;
Log.Debug(this.GetType().ToString(), "wxJsApiParam : " + wxJsApiParam);
//在页面上显示订单信息
// Response.Write("<span style='color:#00CD00;font-size:20px' id='payRes'>订单详情：</span><br/>");
// Response.Write("<span style='color:#00CD00;font-size:20px'>" + unifiedOrderResult.ToPrintStr() + "</span>");
}
catch (System.Net.WebException ex)
{
//state = ex.Message;
state = "网络繁忙,请稍后再试！";
}
catch (Exception ex)
{
state = "下单失败,请稍后再试！";
//state = ex.Message;
Utils.log("下单失败:userid:" + userid + ",deviceid：" + deviceid + "，total_fee：" + Convert.ToInt32(total_fee) + "，openid：" + openid + "，tariff_id：" + tariff_id + "，tariff_name：" + tariff_name + "，device_name：" + device_name + "，type：" + type);
//Response.Write("<span style='color:#FF0000;font-size:20px'>" + "下单失败，请返回重试" + "</span>");
// submit.Visible = false;
}
}
}
public string Pushed(string UserID, string DeviceName, string Message, string Date, string Lat, string Lng, string Remark, string exceptionid, string DeviceID)
{
try
{
int tryIndex = 0;
SQLServerOperating s = GetSQLServerOperating();
string strSql = "select ID, UserID,LoginName, OpenID, CreateTime, UpdateTime from WechatUsers where UserID = @UserID and Deleted=0";
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("UserID", UserID) });
if (dt.Rows.Count > 0)
{
string _lat = Lat;
string _lng = Lng;
MgoogpsWebClient mwc = new MgoogpsWebClient();
string access_token = AccessToken();
if (string.IsNullOrEmpty(access_token))
{
return("未获取到access_token.");
}
int count = 0;
string rulst = "";
string key = Utils.GetAmapKey();
string logName = "PushedMessage" + DateTime.Now.ToString("yyyyMM") + ".log";
if (Lat.toDouble() == -1.0 && Lng.toDouble() == -1.0)
{
Dictionary <string, string> dic = s.Selects("select OLat,OLng from lklocation where deviceid=@DeviceID", new SqlParameter[] { new SqlParameter("DeviceID", DeviceID) }).toDictionary();
Lat = dic["OLat"];
Lng = dic["OLng"];
}
Gps g = Utils.gps84_To_Gcj02(Lat, Lng, key);
mwc.RequestUrl = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + access_token;
string time = Date.toDateTime().ToString("yyyy-MM-dd HH:mm:ss");
//List<Task> taskList = new List<Task>();
// TaskFactory taskFactory = new TaskFactory();
// List<string> openids = new List<string>();
foreach (DataRow item in dt.Rows)
{
DataRow row = item;
//Task task = taskFactory.StartNew(() =>
// {
string openid = row["OpenID"].toStringEmpty();
string pushContent = GetAlarmPushText(openid, DeviceName, Message, time, g.Address, Remark, exceptionid);
mwc.RequestPostData = Encoding.UTF8.GetBytes(pushContent);
rulst = mwc.RequestSend();
Dictionary <string, string> res = Utils.ToDictionary(rulst);
Utils.log(string.Format("{0},{1},{2},{3},{4},{5}", UserID, row["LoginName"], DeviceName, openid, time, Message), logName);
if (res["errcode"].Equals("0") && res["errmsg"].Equals("ok"))
{
count++;
continue;
//return Utils.GetResult("发送成功.", statusCode.Code.success);
}
else if (res["errcode"] == "40001") //获取access_token时AppSecret错误，或者access_token无效。请开发者认真比对AppSecret的正确性，或查看是否正在为恰当的公众号调用接口
{
if (tryIndex < 1)
{
Utils.SetCache("access_token", "");
AccessToken();
tryIndex++;
return(Pushed(UserID, DeviceName, Message, time, _lat, _lng, Remark, exceptionid, DeviceID));
}
}
else if (res["errcode"] == "43004") //接收者没有关注公众号
{
string sql = "delete from wechatusers where OpenID=@OpenID";
s.ExecuteSql(sql, new SqlParameter[] { new SqlParameter("OpenID", openid) });
}
Utils.log(string.Format("----发送失败 ：{0},{1},{2},{3},{4},{5}", UserID, row["LoginName"], DeviceName, openid, time, Message), logName);
Utils.log("----rulst ：" + rulst, logName);
// openids.Add(openid);
// return Utils.GetResult("发送失败.", statusCode.Code.failure);
// });
// taskList.Add(task);
}
//Task.WaitAll(taskList.ToArray());
if (count > 0)
{
return(Utils.GetResult("发送成功.", statusCode.Code.success));
}
else
{
return(Utils.GetResult("发送失败.", statusCode.Code.failure, rulst));
}
}
return(Utils.GetResult("该用户未绑定微信.", statusCode.Code.failure));
}
catch (Exception ex)
{
Utils.log("Pushed ERROR:" + ex.Message + ",堆栈:" + ex.StackTrace);
throw ex;
}
}
public void SendMail(string OrderNo, string mailTitle = null)
{
System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
msg.To.Add("*****@*****.**"); // 张飞鸿
msg.To.Add("*****@*****.**"); //罗坤
msg.To.Add("*****@*****.**"); //谢春丽
// msg.To.Add("*****@*****.**"); //阿成
try
{
SQLServerOperating s = new SQLServerOperating();
string strSql = @"select u.UserName, d.DeviceID,d.DeviceName,d.SerialNumber,o.OrderNo,o.ProductBody,o.PayDate,o.TradeType,d.PhoneNum ,d.HireExpireDate,cast(CONVERT(int, o.TotalFee)/100.0 as numeric(6,2)) TotalFee ,tp.TariffName
from orders o inner join Devices d on d.DeviceID=o.DeviceID inner join Users u on u.UserID=o.UserID inner join TariffPackages tp on tp.ID=o.TariffID
where o.Status = @Status and o.OrderNo = @OrderNo";
DataTable dt = s.Selects(strSql, new SqlParameter[] { new SqlParameter("OrderNo", OrderNo), new SqlParameter("Status", OrderStatus.已付款) });
DataRow row = dt.Rows[0];
msg.From = new MailAddress("*****@*****.**", "美谷充值", System.Text.Encoding.UTF8);
/* 上面3个参数分别是发件人地址（可以随便写），发件人姓名，编码*/
if (string.IsNullOrEmpty(mailTitle))
{
mailTitle = "设备充值成功";
}
msg.Subject = mailTitle; // "设备充值成功";//邮件标题
msg.SubjectEncoding = System.Text.Encoding.UTF8; //邮件标题编码
StringBuilder sbBody = new StringBuilder();
sbBody.Append("用户名称：" + row["UserName"] + " <br />");
sbBody.Append("充值设备IMEI：" + row["SerialNumber"] + " <br />");
sbBody.Append("充值设备名称：" + row["DeviceName"] + " <br />");
sbBody.Append("订单号：" + row["OrderNo"] + " <br />");
sbBody.Append("充值套餐：" + row["TariffName"] + " <br />");
sbBody.Append("充值金额：" + row["TotalFee"] + " <br />");
sbBody.Append("下次到期时间：" + row["HireExpireDate"] + " <br />");
sbBody.Append("交易时间：" + row["PayDate"] + " <br />");
sbBody.Append("流量卡号：" + row["PhoneNum"] + " <br />");
sbBody.Append("请尽快给该设备充值流量！");
msg.Body = sbBody.ToString(); //邮件内容
msg.BodyEncoding = System.Text.Encoding.UTF8; //邮件内容编码
msg.Priority = MailPriority.High; //邮件优先级
msg.IsBodyHtml = true; //是否是HTML邮件
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "mgoo123"); //登录密码 86768770
client.Host = "smtp.163.com";
client.Port = 25;
//启用ssl,也就是安全发送
client.EnableSsl = true;
object userState = msg;
try
{
//client.SendAsync(msg, userState);
client.Send(msg);
//简单一点儿可以client.Send(msg);
Utils.log("邮件发送成功:订单号：" + OrderNo);
}
catch (System.Net.Mail.SmtpException ex)
{
Console.WriteLine(ex.Message);
Utils.log("SendMailUseZj1 Error：" + ex.Message + ",body:" + sbBody.ToString() + "，副标题：" + msg.Subject);
throw ex;
}
}
catch (Exception ex)
{
Utils.log("SendMailUseZj2 Error:" + ex.Message + ",发送邮件失败。。。 订单号：" + OrderNo, "PaySuccessEmailFailure.log");
}
}
public LoginUserInfo SystemLogin_Bll(string loginName, string passWord, string identifies, string loginType)
{
try
{
string strSql = "select UserName,UserID,LoginName,UserType,SuperAdmin,PassWord from users where Deleted=0 and LoginName=@LoginName";
SqlParameter[] parameter = new SqlParameter [] { new SqlParameter("LoginName", loginName) };
SQLServerOperating s = new SQLServerOperating();
DataTable dt = s.Selects(strSql, parameter);
if (dt.Rows.Count <= 0)
{
return(null);
}
else
{
DataRow loginUserDic = dt.Rows[0];
// 加密
//string EncryptPWD = FormsAuthentication.HashPasswordForStoringInConfigFile(loginUserDic["PassWord"].toStringEmpty(), "MD5");
string EncryptPWD = Utils.GetMD5(loginUserDic["PassWord"].toStringEmpty());
// = BitConverter.ToString(MD5.Create().ComputeHash(Encoding.UTF8.GetBytes())).Replace("-", "").ToUpper();
if (EncryptPWD.Equals(passWord.ToLower()))
{
LoginUserInfo _loginUserInfo = new LoginUserInfo();
_loginUserInfo.UserID = loginUserDic["UserID"].toStringEmpty();
_loginUserInfo.UserName = loginUserDic["UserName"].toStringEmpty();
_loginUserInfo.LoginName = loginUserDic["LoginName"].toStringEmpty();
_loginUserInfo.UserType = loginUserDic["UserType"].toStringEmpty();
_loginUserInfo.SuperAdmin = loginUserDic["SuperAdmin"].toStringEmpty();
_loginUserInfo.LoginTime = DateTime.Now;
_loginUserInfo.LoginType = LoginType.User;
_loginUserInfo.ToKen = Guid.NewGuid().ToString().Replace("-", "").ToLower();
_loginUserInfo.Identifies = identifies;
if (identifies.Split('@').Length == 2)
{
string mt = identifies.Split('@')[1];
if (mt.ToUpper() == "BAIDU")
{
_loginUserInfo.MapType = MapType.BAIDU;
}
else
{
_loginUserInfo.MapType = MapType.AMAP;
}
}
else
{
_loginUserInfo.MapType = MapType.AMAP;
}
//string ip = Utils.GetIP();
//HttpRuntime.Cache.Insert("ip_"+_loginUserInfo.ToKen, ip, null, DateTime.Now.AddMinutes(20), TimeSpan.Zero);
//SessionOper.SetSession(_loginUserInfo, SessionOper.SessionName);
return(_loginUserInfo);
}
return(null);
}
}
catch (Exception ex)
{
Utils.log("登录出错：" + loginName + "," + passWord + " --异常信息：" + ex.Message + ",堆栈信息：" + ex.StackTrace);
return(null);
}
}
