public bool MobileInfo(string AppID, string AppKey, string ClientID, string Token, string Model, string Vendor, string IMEI, string UUID, string IMSI, string Resolution, string DPI, string OS, string OSVersion, string UserID, string APPVersion)
{
try
{
if (string.IsNullOrEmpty(ClientID) || string.IsNullOrEmpty(Token) || string.IsNullOrEmpty(AppID) || string.IsNullOrEmpty(AppKey) || string.IsNullOrEmpty(OS))
{
return(false);
}
if (ClientID.ToLower() == "null" || Token.ToLower() == "null" || AppID.ToLower() == "null" || AppKey.ToLower() == "null" || OS.ToLower() == "null" || ClientID.ToLower() == "(null)")
{
return(false);
}
string strSql = "select COUNT(*) from MobileAppInfo where ClientID=@ClientID";
SQLServerOperating s = new SQLServerOperating();
SqlParameter[] pars = new SqlParameter[] { new SqlParameter("ClientID", ClientID) };
string count = s.Select(strSql, pars);
strSql = "select ID from apps where AppID=@AppID and AppKey=@AppKey";
string appsid = s.Select(strSql, new SqlParameter[] { new SqlParameter("AppID", AppID), new SqlParameter("AppKey", AppKey) });
if (Convert.ToInt32(count) > 0)
{
strSql = "update MobileAppInfo set UserID=@UserID,LastDate=GETDATE(),OS=@OS,OSVersion=@OSVersion,AppsID=@AppsID,APPVersion=@APPVersion where ClientID=@ClientID";
pars = new SqlParameter[] {
new SqlParameter("ClientID", ClientID),
new SqlParameter("UserID", UserID),
new SqlParameter("OS", OS),
new SqlParameter("OSVersion", OSVersion),
new SqlParameter("AppsID", appsid),
new SqlParameter("APPVersion", APPVersion)
};
s.ExecuteSql(strSql, pars);
}
else
{
strSql = @"Insert into MobileAppInfo(UserID, ClientID, Token, Model, Vendor, IMEI, UUID, IMSI, Resolution, DPI, OS,OSVersion, Created, LastDate,AppsID,APPVersion)
values(@UserID, @ClientID, @Token, @Model, @Vendor, @IMEI, @UUID,@IMSI,@Resolution,@DPI, @OS,@OSVersion, GETDATE(), GETDATE(),@AppsID,@APPVersion)";
pars = new SqlParameter[] { new SqlParameter("ClientID", ClientID),
new SqlParameter("UserID", UserID),
new SqlParameter("Token", Token),
new SqlParameter("Model", Model),
new SqlParameter("Vendor", Vendor),
new SqlParameter("IMEI", IMEI),
new SqlParameter("UUID", UUID),
new SqlParameter("IMSI", IMSI),
new SqlParameter("Resolution", Resolution),
new SqlParameter("DPI", DPI),
new SqlParameter("OS", OS),
new SqlParameter("OSVersion", OSVersion),
new SqlParameter("AppsID", appsid),
new SqlParameter("APPVersion", APPVersion) };
s.ExecuteSql(strSql, pars);
}
return(true);
}
catch (Exception ex)
{
Utils.log("MobileInfo Error:" + ex.Message + ",StackTrace" + ex.StackTrace);
return(false);
}
}
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);
}
/// <summary>
/// 获取微信推送通知的报警类型
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public string GetPushMsgByUserID(string UserID)
{
string strSql = "select PushMsgType from UsersConfig where UserID=@UserID";
SQLServerOperating s = new SQLServerOperating();
return(s.Select(strSql, new SqlParameter[] { new SqlParameter("UserID", UserID) }));
}
public bool AddOpenID(string OpenID, string UserID, string LoginName)
{
//StringBuilder sb = new StringBuilder();
try
{
SQLServerOperating s = new SQLServerOperating();
string strSql = "select count(-1) from WechatUsers where OpenID=@OpenID";
string exist = s.Select(strSql, new SqlParameter[] { new SqlParameter("OpenID", OpenID), new SqlParameter("UserID", UserID) });
if (Convert.ToInt32(exist) == 0)
{
//DataTable dt = s.Selects(" select NotificationType from ExceptionMessage group by NotificationType");
//foreach (DataRow row in dt.Rows)
//{
// sb.Append(row["NotificationType"]+",");
//}
//sb = sb.Length > 0 ? sb.Remove(sb.Length-1, 1) : sb;
strSql = "insert into WechatUsers (UserID,LoginName, OpenID, CreateTime, UpdateTime,PushMsg)values(@UserID,@LoginName,@OpenID,GETDATE(),GETDATE(),@PushMsg)";
return(s.ExecuteSql(strSql, new SqlParameter[] { new SqlParameter("UserID", UserID), new SqlParameter("OpenID", OpenID), new SqlParameter("LoginName", LoginName), new SqlParameter("PushMsg", "") }) > 0);
}
else
{
strSql = "update WechatUsers set UpdateTime=GETDATE(),UserID=@UserID,LoginName=@LoginName, Deleted=0 where OpenID=@OpenID";
return(s.ExecuteSql(strSql, new SqlParameter[] { new SqlParameter("UserID", UserID), new SqlParameter("OpenID", OpenID), new SqlParameter("LoginName", LoginName) }) > 0);
}
}
catch (Exception ex)
{
Utils.log("AddOpenID ERROR：" + ex.Message + ",StackTrace:" + ex.StackTrace + ",OpenID:" + OpenID + ",UserID:" + UserID + ",LoginName:" + LoginName);
return(false);
}
}
public string AddGroups(string userid, string groupname)
{
try
{
if (string.IsNullOrEmpty(groupname))
{
return(Utils.GetResult("分组名字不能为空.", statusCode.Code.failure));
}
string strSql = "insert into groups(GroupName, UserID, Username, Description, Created, GroupType, AccountID, Deleted)values( @groupname, @userid, '', '', GETDATE(), -1, -1, 0) select @@IDENTITY";
SQLServerOperating s = new SQLServerOperating();
string status = s.Select(strSql, new SqlParameter[] { new SqlParameter("groupname", groupname), new SqlParameter("userid", userid) });
if (status != string.Empty)
{
return(Utils.GetResult("添加成功.", statusCode.Code.success, status));
}
else
{
return(Utils.GetResult("添加失败", statusCode.Code.failure, ""));
}
}
catch (Exception ex)
{
Utils.log("AddGroups ERROR:" + ex.Message);
return(Utils.GetResult("添加失败", statusCode.Code.error));
}
}
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,"",);
}
private string AddFence(int DeviceID, int Radius)
{
try
{
//添加围栏前，先把这个设备的围栏删除，一台设备只有一个围栏
string strSql = @"delete from [GeoFence] where DeviceID=75539;
insert into GeoFence(FenceName, Latitude, Longitude, [Entry], [Exit], Radius, IsInclusion, Created, Deleted, Lat1, Lng1, FenceType, Width, UserID, DeviceID, [Description])
select @FenceName,l.OLat,l.OLng,0, 0,@Radius, -1, GETDATE(), 0, -1.00000000000000000000, -1.00000000000000000000, 0, -1,UserID, d.DeviceID, ''
from Devices d inner join Lklocation l on l.DeviceID=d.DeviceID where d.DeviceID=@DeviceID
select @@IDENTITY as gid";
SQLServerOperating sqlHelper = new SQLServerOperating();
var pars = new SqlParameter[] {
new SqlParameter("DeviceID", DeviceID),
new SqlParameter("FenceName", "一键围栏-" + DateTime.Now.ToString("yyyyMMdd")),
new SqlParameter("Radius", Radius)
};
int state = Convert.ToInt32(sqlHelper.Select(strSql, pars));
if (state > 0)
{
return(Utils.GetResult("围栏已开启.", statusCode.Code.success, state.ToString()));
}
else
{
return(Utils.GetResult("围栏开启失败.", statusCode.Code.failure));
}
}
catch (Exception ex)
{
return(Utils.GetResult(ex.Message, statusCode.Code.failure));
}
}
public string QueryOrder(string OrderNo, string transactionid)
{
if (!string.IsNullOrEmpty(OrderNo))
{
string strSql = "select [Status] from orders where OrderNo = @OrderNo";
SQLServerOperating s = new SQLServerOperating();
return(s.Select(strSql, new SqlParameter[] { new SqlParameter("OrderNo", OrderNo) }));
}
else if (!string.IsNullOrEmpty(transactionid))
{
}
return(string.Empty);
}
public ajaxResult RetrievePassword(string phone, string password)
{
try
{
string strSql = "select COUNT(UserID) from users where deleted = 0 and loginname=@loginname";
strSql = @"if exists(select UserID from Users where deleted=0 and loginname=@loginname)
begin
update Users set password=@password where deleted=0 and loginname=@loginname select 1
end
else
select -2";
SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("loginname", phone), new SqlParameter("password", password) };
ajaxResult ar = new MG_BLL.ajaxResult();
SQLServerOperating s = new SQLServerOperating();
string count = s.Select(strSql, parameter);
if (count.Equals("1"))
{
ar.StatusCode = statusCode.Code.success;
ar.Message = "密码重设成功.";
ar.Result = "";
//strSql = "update Users set password=@password where loginname=@loginname";
//parameter = new SqlParameter[] { new SqlParameter("loginname", phone), new SqlParameter("password", password) };
//int status = s.ExecuteSql(strSql, parameter);
//if (status > 0)
//{
// return true;
//}
}
else
{
ar.StatusCode = statusCode.Code.failure;
ar.Message = "密码重设失败.";
ar.Result = "";
}
return(ar);
}
catch (Exception ex)
{
throw ex;
}
}
public int MobileApps(string AppID, string AppKey, string PackageName, string OS)
{
try
{
if (string.IsNullOrEmpty(AppID) || string.IsNullOrEmpty(AppKey) || string.IsNullOrEmpty(PackageName))
{
return(-1);
}
if (AppID == "null" || AppKey == "null")
{
return(-1);
}
string strSql = "select count(*) from apps where AppID=@AppID and AppKey=@AppKey";
SQLServerOperating s = new SQLServerOperating();
string count = s.Select(strSql, new SqlParameter[] { new SqlParameter("AppID", AppID), new SqlParameter("AppKey", AppKey) });
if (Convert.ToInt32(count) <= 0)
{
strSql = @"insert into Apps(AppID, AppKey, PackageName, OS)
values(@AppID, @AppKey,@PackageName, @OS)";//AppSecret,MasterSecret, ,@AppSecret @MasterSecret,
return(s.ExecuteSql(strSql, new SqlParameter[] {
new SqlParameter("AppID", AppID),
// new SqlParameter("AppSecret", AppSecret),
new SqlParameter("AppKey", AppKey),
// new SqlParameter("MasterSecret",MasterSecret),
new SqlParameter("PackageName", PackageName),
new SqlParameter("OS", OS)
}));
}
else
{
return(2);
}
}
catch (Exception ex)
{
Utils.log("MobileApps Error:" + ex.Message);
return(-3);
}
}
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));
}
}