/// <summary>
/// 将地图坐标转换成与车机一致的
/// </summary>
/// <param name="lng"></param>
/// <param name="lat"></param>
/// <returns></returns>
public static PointModel NetDecryFixFalse(double lng, double lat)
{
Rectify.Gcj02_To_Wgs84(ref lat, ref lng);
//NetDecry.Fix(ref lng, ref lat, false);
PointModel point = new PointModel()
{
Longitude = lng, Latitude = lat
};
return(point);
}
/// <summary>
/// 高德地图坐标和车机坐标互转
/// 地图上获取的是地图坐标，存入数据库时需要转换成车机坐标
/// 数据库查询出来的坐标是车机坐标，地图显示时候需要转换成地图坐标
/// </summary>
/// <param name="efType">坐标数据是圆形或者矩形、多边形，圆形和矩形、多边形的存储方式有别，需要分开处理</param>
/// <param name="efInfo">坐标数据</param>
/// <param name="changeType">1--地图坐标转车机坐标，2--车机坐标转地图坐标</param>
/// <returns></returns>
public static string ChangeCoordinateSystem(int?efType, string efInfo, int changeType)
{
string[] _tempEfInfo;
string _rsEfInfo = "";
if (efType == 1) //圆形
{
_tempEfInfo = efInfo.Split(';');
for (int i = 0; i < _tempEfInfo.Length - 1; i++) //最后一个是圆形半径，去掉
{
string[] _tempCoordinate = _tempEfInfo[i].Split(',');
double _lng = double.Parse(_tempCoordinate[0]);
double _lat = double.Parse(_tempCoordinate[1]);
if (changeType == 1)
{
//NetDecry.Fix(ref _lng, ref _lat, false);
Rectify.Gcj02_To_Wgs84(ref _lat, ref _lng);
} //将地图坐标置成车机坐标
else if (changeType == 2)
{
//NetDecry.Fix(ref _lng, ref _lat, true);
Rectify.Wgs84_To_Gcj02(ref _lat, ref _lng);
} //将车机坐标置成地图坐标
_rsEfInfo += _lng + "," + _lat + ";";
}
_rsEfInfo += _tempEfInfo[_tempEfInfo.Length - 1]; //加上半径信息
}
else if (efType == 2 || efType == 3) //矩形、多边形
{
_tempEfInfo = efInfo.Split(';');
for (int i = 0; i < _tempEfInfo.Length; i++)
{
string[] _tempCoordinate = _tempEfInfo[i].Split(',');
double _lng = double.Parse(_tempCoordinate[0]);
double _lat = double.Parse(_tempCoordinate[1]);
if (changeType == 1)
{
//NetDecry.Fix(ref _lng, ref _lat, false);
Rectify.Gcj02_To_Wgs84(ref _lat, ref _lng);
} //将地图坐标置成车机坐标
else if (changeType == 2)
{
//NetDecry.Fix(ref _lng, ref _lat, true);
Rectify.Wgs84_To_Gcj02(ref _lat, ref _lng);
} //将车机坐标置成地图坐标
_rsEfInfo += _lng + "," + _lat + ";";
}
_rsEfInfo = _rsEfInfo.Substring(0, _rsEfInfo.Length - 1); //去掉最后一个";"
}
return(_rsEfInfo);
}
public static OperationResult AddMapLines(MapLinesAddModel model, int currentUserID, int currentStrucID)
{
int len = model.MapLinesDetails.Count + 1;
string[] sqls = new string[len];
SqlParameter[][] linesParas = new SqlParameter[len][];
int num = 1;
#region 主表添加
sqls[0] = @"INSERT INTO dbo.MapLinesList
( LinesType ,
LinesName ,
StartTime ,
EndTime ,
Status ,
CreateTime ,
UpdateTime ,
CreateUserID,
UpdateUserID,
StrucID
)
VALUES ( @LinesType ,
@LinesName,
@StartTime ,
@EndTime ,
@Status,
@CreateTime ,
@UpdateTime,
@CreateUserID,
@UpdateUserID,
@StrucID
);SELECT SCOPE_IDENTITY();";
linesParas[0] = new SqlParameter[10];
linesParas[0][0] = new SqlParameter()
{
ParameterName = "@LinesType",
SqlDbType = SqlDbType.Int,
Value = 4
};
linesParas[0][1] = new SqlParameter()
{
ParameterName = "@LinesName",
SqlDbType = SqlDbType.NVarChar,
Size = 50,
Value = model.LinesName
};
if (string.IsNullOrEmpty(model.StartTime))
{
linesParas[0][2] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.DateTime,
Value = DBNull.Value
};
}
else
{
linesParas[0][2] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.DateTime,
Value = model.StartTime
};
}
if (string.IsNullOrEmpty(model.EndTime))
{
linesParas[0][3] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.DateTime,
Value = DBNull.Value
};
}
else
{
linesParas[0][3] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.DateTime,
Value = model.EndTime
};
}
linesParas[0][4] = new SqlParameter()
{
ParameterName = "@Status",
SqlDbType = SqlDbType.Int,
Value = 0
};
linesParas[0][5] = new SqlParameter()
{
ParameterName = "@CreateTime",
SqlDbType = SqlDbType.DateTime,
Value = DateTime.Now
};
linesParas[0][6] = new SqlParameter()
{
ParameterName = "@UpdateTime",
SqlDbType = SqlDbType.DateTime,
Value = DateTime.Now
};
linesParas[0][7] = new SqlParameter()
{
ParameterName = "@CreateUserID",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
linesParas[0][8] = new SqlParameter()
{
ParameterName = "@UpdateUserID",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
linesParas[0][9] = new SqlParameter()
{
ParameterName = "@StrucID",
SqlDbType = SqlDbType.Int,
Value = currentStrucID
};
#endregion
#region 明细表添加
int orderID = 0;//定位点序号
for (int i = 0; i < model.MapLinesDetails.Count; i++)
{
string tempSql = string.Empty;
tempSql = @"INSERT INTO dbo.MapLinesDetails( LinesID, OrderID, Latitude, Longitude, RoadWidth, IsCheckTime, MaxSecond, MinSecond, IsCheckSpeed, SpeedLimit, OverSpeedDuration ) VALUES ( @LinesID,@OrderID,@Latitude,@Longitude,@RoadWidth,@IsCheckTime,@MaxSecond,@MinSecond,@IsCheckSpeed,@SpeedLimit,@OverSpeedDuration)";
sqls[num] = tempSql;
linesParas[num] = new SqlParameter[11];
linesParas[num][0] = new SqlParameter {
ParameterName = "@LinesID", SqlDbType = SqlDbType.BigInt
};
linesParas[num][1] = new SqlParameter()
{
ParameterName = "@OrderID",
SqlDbType = SqlDbType.TinyInt,
Value = orderID
};
//纠偏
double lat = double.Parse(model.MapLinesDetails[i].Latitude.ToString());
double lng = double.Parse(model.MapLinesDetails[i].Longitude.ToString());
//NetDecry.Fix(ref lng, ref lat, false);
Rectify.Gcj02_To_Wgs84(ref lat, ref lng);
linesParas[num][2] = new SqlParameter()
{
ParameterName = "@Latitude",
SqlDbType = SqlDbType.Float,
Value = lat
};
linesParas[num][3] = new SqlParameter()
{
ParameterName = "@Longitude",
SqlDbType = SqlDbType.Float,
Value = lng
};
if (model.MapLinesDetails[i].RoadWidth == null)
{
linesParas[num][4] = new SqlParameter()
{
ParameterName = "@RoadWidth",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][4] = new SqlParameter()
{
ParameterName = "@RoadWidth",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].RoadWidth
};
}
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = false
};
if (model.MapLinesDetails[i].MaxSecond == null)
{
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].MaxSecond
};
}
if (model.MapLinesDetails[i].MinSecond == null)
{
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].MinSecond
};
}
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = false
};
if (model.MapLinesDetails[i].SpeedLimit == null)
{
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].SpeedLimit
};
}
if (model.MapLinesDetails[i].OverSpeedDuration == null)
{
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].OverSpeedDuration
};
}
//判断行驶时间选中，限速、最高速度、超速持续时间控件不可用，数据未传递过来
if (model.MapLinesDetails[i].IsCheckTime == true)
{
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = true
};
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].MaxSecond
};
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].MinSecond
};
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = false
};
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
//限速选中，判断行驶时间、路段行驶过长、路段行驶不足控件不可用，数据未传递过来
if (model.MapLinesDetails[i].IsCheckSpeed == true)
{
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = false
};
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = true
};
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].SpeedLimit
};
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].OverSpeedDuration
};
}
num++;
orderID++;
}
#endregion
bool result = MSSQLHelper.ExecuteIdentityIncludeTransaction(CommandType.Text, sqls, linesParas) != 0;
return(new OperationResult()
{
Success = result,
Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError
});
}
public static OperationResult EditMapLines(MapLinesEditModel model, int currentUserID, int currentStrucID)
{
int result = 0;
#region 更新主表
var updateSql = @"UPDATE dbo.MapLinesList
SET LinesName = @LinesName ,
StartTime = @StartTime ,
EndTime = @EndTime ,
UpdateTime = GetDate() ,
UpdateUserID = @UpdateUserID
WHERE ID = @ID ";
SqlParameter[] updateParas = new SqlParameter[5];
updateParas[0] = new SqlParameter()
{
ParameterName = "@LinesName",
SqlDbType = SqlDbType.NVarChar,
Size = 50,
Value = model.LinesName
};
if (string.IsNullOrEmpty(model.StartTime))
{
updateParas[1] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.DateTime,
Value = DBNull.Value
};
}
else
{
updateParas[1] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.DateTime,
Value = model.StartTime
};
}
if (string.IsNullOrEmpty(model.EndTime))
{
updateParas[2] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.DateTime,
Value = DBNull.Value
};
}
else
{
updateParas[2] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.DateTime,
Value = model.EndTime
};
}
updateParas[3] = new SqlParameter()
{
ParameterName = "@UpdateUserID",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
updateParas[4] = new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
int updateResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, updateSql, updateParas);
#endregion
#region 除
var delSql = @"DELETE FROM dbo.MapLinesDetails WHERE LinesID=@LinesID";
SqlParameter[] delParas = new SqlParameter[1];
delParas[0] = new SqlParameter()
{
ParameterName = "@LinesID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
int delResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, delSql, delParas);
#endregion
#region 添加明细表
int orderID = 0;//定位点序号
int len = model.MapLinesDetails.Count;
string[] sqls = new string[len];
SqlParameter[][] linesParas = new SqlParameter[len][];
int num = 0;
for (int i = 0; i < len; i++)
{
string tempSql = string.Empty;
tempSql = @"INSERT INTO dbo.MapLinesDetails( LinesID, OrderID, Latitude, Longitude, RoadWidth, IsCheckTime, MaxSecond, MinSecond, IsCheckSpeed, SpeedLimit, OverSpeedDuration ) VALUES ( @LinesID,@OrderID,@Latitude,@Longitude,@RoadWidth,@IsCheckTime,@MaxSecond,@MinSecond,@IsCheckSpeed,@SpeedLimit,@OverSpeedDuration)";
sqls[num] = tempSql;
linesParas[num] = new SqlParameter[11];
linesParas[num][0] = new SqlParameter()
{
ParameterName = "@LinesID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
linesParas[num][1] = new SqlParameter()
{
ParameterName = "@OrderID",
SqlDbType = SqlDbType.TinyInt,
Value = orderID
};
//纠偏
double lat = double.Parse(model.MapLinesDetails[i].Latitude.ToString());
double lng = double.Parse(model.MapLinesDetails[i].Longitude.ToString());
//NetDecry.Fix(ref lng, ref lat, false);
Rectify.Gcj02_To_Wgs84(ref lat, ref lng);
linesParas[num][2] = new SqlParameter()
{
ParameterName = "@Latitude",
SqlDbType = SqlDbType.Float,
Value = lat
};
linesParas[num][3] = new SqlParameter()
{
ParameterName = "@Longitude",
SqlDbType = SqlDbType.Float,
Value = lng
};
if (model.MapLinesDetails[i].RoadWidth == null)
{
linesParas[num][4] = new SqlParameter()
{
ParameterName = "@RoadWidth",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][4] = new SqlParameter()
{
ParameterName = "@RoadWidth",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].RoadWidth
};
}
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = false
};
if (model.MapLinesDetails[i].MaxSecond == null)
{
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].MaxSecond
};
}
if (model.MapLinesDetails[i].MinSecond == null)
{
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].MinSecond
};
}
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = false
};
if (model.MapLinesDetails[i].SpeedLimit == null)
{
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
}
else
{
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].SpeedLimit
};
}
if (model.MapLinesDetails[i].OverSpeedDuration == null)
{
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].OverSpeedDuration
};
}
//判断行驶时间选中，限速、最高速度、超速持续时间控件不可用，数据未传递过来
if (model.MapLinesDetails[i].IsCheckTime == true)
{
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = true
};
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].MaxSecond
};
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].MinSecond
};
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = false
};
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
//限速选中，判断行驶时间、路段行驶过长、路段行驶不足控件不可用，数据未传递过来
if (model.MapLinesDetails[i].IsCheckSpeed == true)
{
linesParas[num][5] = new SqlParameter()
{
ParameterName = "@IsCheckTime",
SqlDbType = SqlDbType.Bit,
Value = false
};
linesParas[num][6] = new SqlParameter()
{
ParameterName = "@MaxSecond",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
linesParas[num][7] = new SqlParameter()
{
ParameterName = "@MinSecond",
SqlDbType = SqlDbType.Float,
Value = DBNull.Value
};
linesParas[num][8] = new SqlParameter()
{
ParameterName = "@IsCheckSpeed",
SqlDbType = SqlDbType.Bit,
Value = true
};
linesParas[num][9] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.MapLinesDetails[i].SpeedLimit
};
linesParas[num][10] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.MapLinesDetails[i].OverSpeedDuration
};
}
num++;
orderID++;
}
bool insertResult = MSSQLHelper.ExecuteTransaction(CommandType.Text, sqls, linesParas);
if (updateResult > 0 && delResult != 0 && insertResult == true)
{
result = 1;
}
#endregion
string msg = string.Empty;
switch (result)
{
case 1:
msg = PromptInformation.OperationSuccess;
break;
case 0:
msg = PromptInformation.NotExists;
break;
case -1:
msg = PromptInformation.DBError;
break;
}
return(new OperationResult()
{
Success = result > 0,
Message = msg
});
}
/// <summary>
/// 根据区域类别 分圆形、矩形、多边形三种情况添加
/// </summary>
/// <param name="model"></param>
/// <param name="currentUserID"></param>
/// <param name="currentStrucID"></param>
/// <returns></returns>
public static OperationResult AddMapRegions(MapRegionsAddModel model, int currentUserID, int currentStrucID)
{
#region 参数
//纠偏
double centerLng = double.Parse(model.CenterLongitude.ToString());
double centerLat = double.Parse(model.CenterLatitude.ToString());
Rectify.Gcj02_To_Wgs84(ref centerLat, ref centerLng);
//NetDecry.Fix(ref centerLng, ref centerLat, false);
double leftLng = double.Parse(model.LeftUpperLongitude.ToString());
double leftLat = double.Parse(model.LeftUpperLatitude.ToString());
double rightLng = double.Parse(model.RightLowerLongitude.ToString());
double rightLat = double.Parse(model.RightLowerLatitude.ToString());
Rectify.Gcj02_To_Wgs84(ref leftLat, ref leftLng);
Rectify.Gcj02_To_Wgs84(ref rightLat, ref rightLng);
//NetDecry.Fix(ref leftLng, ref leftLat, false);
//NetDecry.Fix(ref rightLng, ref rightLat, false);
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@RegionsType", SqlDbType.Int),
new SqlParameter("@RegionsName", SqlDbType.NVarChar, 100),
new SqlParameter("@CenterLatitude", SqlDbType.Float),
new SqlParameter("@CenterLongitude", SqlDbType.Float),
new SqlParameter("@LeftUpperLatitude", SqlDbType.Float),
new SqlParameter("@LeftUpperLongitude", SqlDbType.Float),
new SqlParameter("@RightLowerLatitude", SqlDbType.Float),
new SqlParameter("@RightLowerLongitude", SqlDbType.Float),
new SqlParameter("@Radius", SqlDbType.Float),
new SqlParameter("@StartDate", SqlDbType.Date),
new SqlParameter("@StartTime", SqlDbType.Time),
new SqlParameter("@EndDate", SqlDbType.Date),
new SqlParameter("@EndTime", SqlDbType.Time),
new SqlParameter("@Periodic", SqlDbType.Bit),
new SqlParameter("@SpeedLimit", SqlDbType.Float),
new SqlParameter("@OverSpeedDuration", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 1000),
new SqlParameter("@Status", SqlDbType.TinyInt),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@UpdateTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int),
new SqlParameter("@StrucID", SqlDbType.Int),
};
paras[0].Value = model.RegionsType;
paras[1].Value = model.RegionsName;
paras[2].Value = centerLng;
paras[3].Value = centerLat;
paras[4].Value = model.LeftUpperLatitude;
paras[5].Value = model.LeftUpperLongitude;
paras[6].Value = model.RightLowerLatitude;
paras[7].Value = model.RightLowerLongitude;
paras[8].Value = model.Radius;
if (string.IsNullOrEmpty(model.StartDate) || model.Periodic == true)
{
paras[9].Value = DBNull.Value;
}
else
{
paras[9].Value = model.StartDate;
}
if (string.IsNullOrEmpty(model.StartTime))
{
paras[10].Value = DBNull.Value;
}
else
{
paras[10].Value = model.StartTime;
}
if (string.IsNullOrEmpty(model.EndDate) || model.Periodic == true)
{
paras[11].Value = DBNull.Value;
}
else
{
paras[11].Value = model.EndDate;
}
if (string.IsNullOrEmpty(model.EndTime))
{
paras[12].Value = DBNull.Value;
}
else
{
paras[12].Value = model.EndTime;
}
paras[13].Value = model.Periodic;
paras[14].Value = model.SpeedLimit;
paras[15].Value = model.OverSpeedDuration;
if (string.IsNullOrEmpty(model.Remark))
{
paras[16].Value = DBNull.Value;
}
else
{
paras[16].Value = model.Remark;
}
paras[17].Value = 0;
paras[18].Value = DateTime.Now;
paras[19].Value = DateTime.Now;
paras[20].Value = currentUserID;
paras[21].Value = currentStrucID;
#endregion
string sql = string.Empty;
bool result = false;
#region SQL 圆形区域 中心点经纬度和半径
if (model.RegionsType == 1)
{
sql = @"INSERT INTO dbo.MapRegionsList
( RegionsType ,
RegionsName ,
StrucID,
CenterLatitude ,
CenterLongitude ,
Radius ,
StartDate ,
StartTime ,
EndDate ,
EndTime ,
Periodic ,
SpeedLimit ,
OverSpeedDuration ,
Remark ,
Status ,
CreateTime ,
UpdateTime ,
CreateUserID
)
VALUES ( @RegionsType ,
@RegionsName,
@StrucID,
@CenterLatitude ,
@CenterLongitude,
@Radius ,
@StartDate,
@StartTime ,
@EndDate,
@EndTime ,
@Periodic,
@SpeedLimit ,
@OverSpeedDuration,
@Remark ,
@Status,
@CreateTime ,
@UpdateTime,
@CreateUserID
)";
result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0;
}
#endregion
#region SQL 矩形区域 左上点、右下点经纬度
if (model.RegionsType == 2)
{
sql = @"INSERT INTO dbo.MapRegionsList
( RegionsType ,
RegionsName ,
StrucID,
LeftUpperLatitude ,
LeftUpperLongitude ,
RightLowerLatitude ,
RightLowerLongitude ,
StartDate ,
StartTime ,
EndDate ,
EndTime ,
Periodic ,
SpeedLimit ,
OverSpeedDuration ,
Remark ,
Status ,
CreateTime ,
UpdateTime ,
CreateUserID
)
VALUES ( @RegionsType ,
@RegionsName,
@StrucID,
@LeftUpperLatitude ,
@LeftUpperLongitude,
@RightLowerLatitude ,
@RightLowerLongitude ,
@StartDate,
@StartTime ,
@EndDate,
@EndTime ,
@Periodic,
@SpeedLimit ,
@OverSpeedDuration,
@Remark ,
@Status,
@CreateTime ,
@UpdateTime,
@CreateUserID
)";
result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0;
}
#endregion
#region SQL 多边形区域 关联地图区域明细表 对应多个定位点、经纬度
if (model.RegionsType == 3)
{
sql = @"INSERT INTO dbo.MapRegionsList
( RegionsType ,
RegionsName ,
StartDate ,
StartTime ,
EndDate ,
EndTime ,
Periodic ,
SpeedLimit ,
OverSpeedDuration ,
Remark ,
Status ,
CreateTime ,
UpdateTime ,
CreateUserID,
StrucID
)
VALUES ( @RegionsType ,
@RegionsName,
@StartDate,
@StartTime ,
@EndDate,
@EndTime ,
@Periodic,
@SpeedLimit ,
@OverSpeedDuration,
@Remark ,
@Status,
@CreateTime ,
@UpdateTime,
@CreateUserID,
@StrucID
);SELECT SCOPE_IDENTITY();";
//插数据到地图区域明细表（多边形点位）
//string[] polygons = ((string[])(model.PolygonList))[0].Split(',');
string[] polygons = model.PolygonList[0].Split(',');
int len = polygons.Length / 2;
int sqlLen = len + 1;
string[] sqls = new string[sqlLen];
SqlParameter[][] polygonsParas = new SqlParameter[sqlLen][];
int num = 1;//sqls长度
sqls[0] = sql;
polygonsParas[0] = new SqlParameter[15];
polygonsParas[0][0] = new SqlParameter()
{
ParameterName = "@RegionsType",
SqlDbType = SqlDbType.Int,
Value = model.RegionsType
};
polygonsParas[0][1] = new SqlParameter()
{
ParameterName = "@RegionsName",
SqlDbType = SqlDbType.NVarChar,
Size = 50,
Value = model.RegionsName
};
if (string.IsNullOrEmpty(model.StartDate) || model.Periodic == true)
{
polygonsParas[0][2] = new SqlParameter()
{
ParameterName = "@StartDate",
SqlDbType = SqlDbType.Date,
Value = DBNull.Value
};
}
else
{
polygonsParas[0][2] = new SqlParameter()
{
ParameterName = "@StartDate",
SqlDbType = SqlDbType.Date,
Value = model.StartDate
};
}
if (string.IsNullOrEmpty(model.StartTime))
{
polygonsParas[0][3] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.Time,
Value = DBNull.Value
};
}
else
{
polygonsParas[0][3] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.Time,
Value = model.StartTime
};
}
if (string.IsNullOrEmpty(model.EndDate) || model.Periodic == true)
{
polygonsParas[0][4] = new SqlParameter()
{
ParameterName = "@EndDate",
SqlDbType = SqlDbType.Date,
Value = DBNull.Value
};
}
else
{
polygonsParas[0][4] = new SqlParameter()
{
ParameterName = "@EndDate",
SqlDbType = SqlDbType.Date,
Value = model.EndDate
};
}
if (string.IsNullOrEmpty(model.EndTime))
{
polygonsParas[0][5] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.Time,
Value = DBNull.Value
};
}
else
{
polygonsParas[0][5] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.Time,
Value = model.EndTime
};
}
polygonsParas[0][6] = new SqlParameter()
{
ParameterName = "@Periodic",
SqlDbType = SqlDbType.Bit,
Value = model.Periodic
};
polygonsParas[0][7] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.SpeedLimit
};
polygonsParas[0][8] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.OverSpeedDuration
};
if (string.IsNullOrEmpty(model.Remark))
{
polygonsParas[0][9] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Size = 500,
Value = DBNull.Value
};
}
else
{
polygonsParas[0][9] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Size = 500,
Value = model.Remark
};
}
polygonsParas[0][10] = new SqlParameter()
{
ParameterName = "@Status",
SqlDbType = SqlDbType.Int,
Value = 0
};
polygonsParas[0][11] = new SqlParameter()
{
ParameterName = "@CreateTime",
SqlDbType = SqlDbType.DateTime,
Value = DateTime.Now
};
polygonsParas[0][12] = new SqlParameter()
{
ParameterName = "@UpdateTime",
SqlDbType = SqlDbType.DateTime,
Value = DateTime.Now
};
polygonsParas[0][13] = new SqlParameter()
{
ParameterName = "@CreateUserID",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
polygonsParas[0][14] = new SqlParameter()
{
ParameterName = "@StrucID",
SqlDbType = SqlDbType.Int,
Value = currentStrucID
};
int orderID = 0;//定位点序号
for (int i = 0; i < polygons.Length; i++)
{
string tempSql = string.Empty;
tempSql = @"INSERT INTO dbo.MapRegionsDetails( RegionsID, OrderID, Longitude, Latitude ) VALUES ( @RegionsID,@OrderID,@Longitude,@Latitude)";
List <SqlParameter> tempParas = new List <SqlParameter>()
{
new SqlParameter("@RegionsID", SqlDbType.BigInt),
new SqlParameter("@OrderID", SqlDbType.TinyInt),
new SqlParameter("@Longitude", SqlDbType.Float),
new SqlParameter("@Latitude", SqlDbType.Float),
};
sqls[num] = tempSql;
polygonsParas[num] = new SqlParameter[4];
polygonsParas[num][0] = new SqlParameter {
ParameterName = "@RegionsID", SqlDbType = SqlDbType.BigInt
};
//纠偏
double lng = double.Parse(polygons[i].ToString());
double lat = double.Parse(polygons[i + 1].ToString());
//NetDecry.Fix(ref lng, ref lat, false);
Rectify.Gcj02_To_Wgs84(ref lat, ref lng);
polygonsParas[num][1] = new SqlParameter()
{
ParameterName = "@OrderID",
SqlDbType = SqlDbType.TinyInt,
Value = orderID
};
polygonsParas[num][2] = new SqlParameter()
{
ParameterName = "@Longitude",
SqlDbType = SqlDbType.Float,
Value = lng
};
polygonsParas[num][3] = new SqlParameter()
{
ParameterName = "@Latitude",
SqlDbType = SqlDbType.Float,
Value = lat
};
i++;
num++;
orderID++;
}
result = MSSQLHelper.ExecuteIdentityIncludeTransaction(CommandType.Text, sqls, polygonsParas) != 0;
}
#endregion
//bool result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0;
return(new OperationResult()
{
Success = result,
Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError
});
}
public static OperationResult EditMapRegions(MapRegionsEditModel model, int currentUserID, int currentStrucID)
{
#region 参数
//纠偏
double centerLng = double.Parse(model.CenterLongitude.ToString());
double centerLat = double.Parse(model.CenterLatitude.ToString());
//NetDecry.Fix(ref centerLng, ref centerLat, false);
Rectify.Gcj02_To_Wgs84(ref centerLat, ref centerLng);
double leftLng = double.Parse(model.LeftUpperLongitude.ToString());
double leftLat = double.Parse(model.LeftUpperLatitude.ToString());
double rightLng = double.Parse(model.RightLowerLongitude.ToString());
double rightLat = double.Parse(model.RightLowerLatitude.ToString());
//NetDecry.Fix(ref leftLng, ref leftLat, false);
//NetDecry.Fix(ref rightLng, ref rightLat, false);
Rectify.Gcj02_To_Wgs84(ref leftLat, ref leftLng);
Rectify.Gcj02_To_Wgs84(ref rightLat, ref rightLng);
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@RegionsName", SqlDbType.NVarChar, 100),
new SqlParameter("@CenterLatitude", SqlDbType.Float),
new SqlParameter("@CenterLongitude", SqlDbType.Float),
new SqlParameter("@LeftUpperLatitude", SqlDbType.Float),
new SqlParameter("@LeftUpperLongitude", SqlDbType.Float),
new SqlParameter("@RightLowerLatitude", SqlDbType.Float),
new SqlParameter("@RightLowerLongitude", SqlDbType.Float),
new SqlParameter("@Radius", SqlDbType.Float),
new SqlParameter("@StartDate", SqlDbType.Date),
new SqlParameter("@StartTime", SqlDbType.Time),
new SqlParameter("@EndDate", SqlDbType.Date),
new SqlParameter("@EndTime", SqlDbType.Time),
new SqlParameter("@Periodic", SqlDbType.Bit),
new SqlParameter("@SpeedLimit", SqlDbType.Float),
new SqlParameter("@OverSpeedDuration", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 1000),
new SqlParameter("@UpdateUserID", SqlDbType.Int),
new SqlParameter("@ID", SqlDbType.BigInt),
};
paras[0].Value = model.RegionsName;
paras[1].Value = centerLat;
paras[2].Value = centerLng;
paras[3].Value = leftLat;
paras[4].Value = leftLng;
paras[5].Value = rightLat;
paras[6].Value = rightLng;
paras[7].Value = model.Radius;
if (string.IsNullOrEmpty(model.StartDate) || model.Periodic == true)
{
paras[8].Value = DBNull.Value;
}
else
{
paras[8].Value = model.StartDate;
}
if (string.IsNullOrEmpty(model.StartTime))
{
paras[9].Value = DBNull.Value;
}
else
{
paras[9].Value = model.StartTime;
}
if (string.IsNullOrEmpty(model.EndDate) || model.Periodic == true)
{
paras[10].Value = DBNull.Value;
}
else
{
paras[10].Value = model.EndDate;
}
if (string.IsNullOrEmpty(model.EndTime))
{
paras[11].Value = DBNull.Value;
}
else
{
paras[11].Value = model.EndTime;
}
paras[12].Value = model.Periodic;
paras[13].Value = model.SpeedLimit;
paras[14].Value = model.OverSpeedDuration;
if (string.IsNullOrEmpty(model.Remark))
{
paras[15].Value = DBNull.Value;
}
else
{
paras[15].Value = model.Remark;
}
paras[16].Value = currentUserID;
paras[17].Value = model.ID;
#endregion
string sql = string.Empty;
int result = 0;
#region SQL 圆形区域 中心点经纬度和半径
if (model.RegionsType == 1)
{
sql = @"UPDATE dbo.MapRegionsList
SET RegionsName = @RegionsName ,
CenterLatitude = @CenterLatitude ,
CenterLongitude = @CenterLongitude ,
Radius = @Radius ,
StartDate = @StartDate ,
StartTime = @StartTime ,
EndDate = @EndDate ,
EndTime = @EndTime ,
Periodic = @Periodic ,
SpeedLimit = @SpeedLimit ,
OverSpeedDuration = @OverSpeedDuration ,
Remark = @Remark ,
UpdateTime = GetDate() ,
UpdateUserID = @UpdateUserID
WHERE ID = @ID ";
result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray());
}
#endregion
#region SQL 矩形区域 左上点、右下点经纬度
if (model.RegionsType == 2)
{
sql = @"UPDATE dbo.MapRegionsList
SET RegionsName = @RegionsName ,
LeftUpperLatitude = @LeftUpperLatitude ,
LeftUpperLongitude = @LeftUpperLongitude ,
RightLowerLatitude = @RightLowerLatitude ,
RightLowerLongitude = @RightLowerLongitude ,
StartDate = @StartDate ,
StartTime = @StartTime ,
EndDate = @EndDate ,
EndTime = @EndTime ,
Periodic = @Periodic ,
SpeedLimit = @SpeedLimit ,
OverSpeedDuration = @OverSpeedDuration ,
Remark = @Remark ,
UpdateTime = GetDate() ,
UpdateUserID = @UpdateUserID
WHERE ID = @ID ";
result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray());
}
#endregion
#region SQL 多边形区域 关联地图区域明细表 对应多个定位点、经纬度
if (model.RegionsType == 3)
{
#region 更新主表
sql = @"UPDATE dbo.MapRegionsList
SET RegionsName = @RegionsName ,
StartDate = @StartDate ,
StartTime = @StartTime ,
EndDate = @EndDate ,
EndTime = @EndTime ,
Periodic = @Periodic ,
SpeedLimit = @SpeedLimit ,
OverSpeedDuration = @OverSpeedDuration ,
Remark = @Remark ,
UpdateTime = GetDate() ,
UpdateUserID = @UpdateUserID
WHERE ID = @ID ";
var updateSqls = sql;
SqlParameter[] updatePolygonsParas = new SqlParameter[11];
updatePolygonsParas[0] = new SqlParameter()
{
ParameterName = "@RegionsName",
SqlDbType = SqlDbType.NVarChar,
Size = 50,
Value = model.RegionsName
};
if (string.IsNullOrEmpty(model.StartDate) || model.Periodic == true)
{
updatePolygonsParas[1] = new SqlParameter()
{
ParameterName = "@StartDate",
SqlDbType = SqlDbType.Date,
Value = DBNull.Value
};
}
else
{
updatePolygonsParas[1] = new SqlParameter()
{
ParameterName = "@StartDate",
SqlDbType = SqlDbType.Date,
Value = model.StartDate
};
}
if (string.IsNullOrEmpty(model.StartTime))
{
updatePolygonsParas[2] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.Time,
Value = DBNull.Value
};
}
else
{
updatePolygonsParas[2] = new SqlParameter()
{
ParameterName = "@StartTime",
SqlDbType = SqlDbType.Time,
Value = model.StartTime
};
}
if (string.IsNullOrEmpty(model.EndDate) || model.Periodic == true)
{
updatePolygonsParas[3] = new SqlParameter()
{
ParameterName = "@EndDate",
SqlDbType = SqlDbType.Date,
Value = DBNull.Value
};
}
else
{
updatePolygonsParas[3] = new SqlParameter()
{
ParameterName = "@EndDate",
SqlDbType = SqlDbType.Date,
Value = model.EndDate
};
}
if (string.IsNullOrEmpty(model.EndTime))
{
updatePolygonsParas[4] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.Time,
Value = DBNull.Value
};
}
else
{
updatePolygonsParas[4] = new SqlParameter()
{
ParameterName = "@EndTime",
SqlDbType = SqlDbType.Time,
Value = model.EndTime
};
}
updatePolygonsParas[5] = new SqlParameter()
{
ParameterName = "@Periodic",
SqlDbType = SqlDbType.Bit,
Value = model.Periodic
};
updatePolygonsParas[6] = new SqlParameter()
{
ParameterName = "@SpeedLimit",
SqlDbType = SqlDbType.Float,
Value = model.SpeedLimit
};
updatePolygonsParas[7] = new SqlParameter()
{
ParameterName = "@OverSpeedDuration",
SqlDbType = SqlDbType.Int,
Value = model.OverSpeedDuration
};
if (string.IsNullOrEmpty(model.Remark))
{
updatePolygonsParas[8] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Size = 500,
Value = DBNull.Value
};
}
else
{
updatePolygonsParas[8] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Size = 500,
Value = model.Remark
};
}
updatePolygonsParas[9] = new SqlParameter()
{
ParameterName = "@UpdateUserID",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
updatePolygonsParas[10] = new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
int updateResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, updateSqls, updatePolygonsParas);
#endregion
#region 除
var delSql = @"DELETE FROM dbo.MapRegionsDetails WHERE RegionsID=@RegionsID";
SqlParameter[] delPolygonsParas = new SqlParameter[1];
delPolygonsParas[0] = new SqlParameter()
{
ParameterName = "@RegionsID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
int delResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, delSql, delPolygonsParas);
#endregion
#region 添加
//插数据到地图区域明细表（多边形点位）
string[] polygons = model.PolygonList[0].Split(',');
int len = polygons.Length / 2;
string[] insertSqls = new string[len];
SqlParameter[][] insertPolygonsParas = new SqlParameter[len][];
int num = 0; //sqls长度
int orderID = 0; //定位点序号
for (int i = 0; i < polygons.Length; i++)
{
string tempSql = string.Empty;
tempSql = @"INSERT INTO dbo.MapRegionsDetails( RegionsID, OrderID, Longitude, Latitude ) VALUES ( @RegionsID,@OrderID,@Longitude,@Latitude)";
insertSqls[num] = tempSql;
insertPolygonsParas[num] = new SqlParameter[4];
insertPolygonsParas[num][0] = new SqlParameter()
{
ParameterName = "@RegionsID",
SqlDbType = SqlDbType.BigInt,
Value = model.ID
};
//纠偏
double lng = double.Parse(polygons[i].ToString());
double lat = double.Parse(polygons[i + 1].ToString());
//NetDecry.Fix(ref lng, ref lat, false);
Rectify.Gcj02_To_Wgs84(ref lat, ref lng);
insertPolygonsParas[num][1] = new SqlParameter()
{
ParameterName = "@OrderID",
SqlDbType = SqlDbType.TinyInt,
Value = orderID
};
insertPolygonsParas[num][2] = new SqlParameter()
{
ParameterName = "@Longitude",
SqlDbType = SqlDbType.Float,
Value = lng
};
insertPolygonsParas[num][3] = new SqlParameter()
{
ParameterName = "@Latitude",
SqlDbType = SqlDbType.Float,
Value = lat
};
i++;
num++;
orderID++;
}
#endregion
bool insertResult = MSSQLHelper.ExecuteTransaction(CommandType.Text, insertSqls, insertPolygonsParas);
if (updateResult == 1 && delResult != 0 && insertResult == true)
{
result = 1;
}
}
#endregion
//int result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray());
string msg = string.Empty;
switch (result)
{
case 1:
msg = PromptInformation.OperationSuccess;
break;
case 0:
msg = PromptInformation.NotExists;
break;
case -1:
msg = PromptInformation.DBError;
break;
}
return(new OperationResult()
{
Success = result > 0,
Message = msg
});
}