WebServer实现SQL数据库百度坐标转换转化的算法
数据格式：
gis://23.140343;113.327019;0;2013-06-05 09:44:32;64;null
格式说明：
<!--gis://纬度;经度;星数;年-月-日 时:分:秒;定位方式（0 GPS 1 公司基站 2 免费驴博士 4 Google 8 收费驴博士 16 mock 32 iphone 64 百度）;地址-->
表名：TBasStore
字段：Col3\Col4\Col5分别是经度纬度，定位类型
表名：TPlaActualVisit
字段：PositioningType 采集字段
连接调用方式：
http://localhost:11209/Services/TransformSqlLatLng.asmx
http://localhost:11209/Services/TransformSqlLatLng.asmx/ConvertSqlLatLng?datasourceid=d5bfbe03-867e-4e32-b2dc-d87eb384f5cf&entnumber=1001051
http://localhost:11209/Services/TransformSqlLatLng.asmx/ConvertSqlLatLng?datasourceid=940c8bb2-3453-457e-831d-65ce167fe26c&entnumber=1002420
原始数据：
select StoreID,Col3,Col4,Col5 from TBasStore where col5=0
75849 113.970263 22.558992 0
75850 113.969306 22.562244 0
75851 113.970596 22.561417 0
75852 113.970604 22.561618 0
75867 113.971877 22.540232 0
75869 113.968206 22.540837 0
75870 113.968148 22.540745 0
75871 113.970669 22.5587 0
75872 113.970261 22.558932 0
75873 113.97043 22.558823 0
75879 113.97261 22.557824 0
75880 113.97263 22.557924 0
75882 113.940676 22.554949 0
转换后的数据：
75849 113.98166958385 22.56213701767 64
75850 113.98071725529 22.565370537455 64
75851 113.98200483986 22.56456919428 64
75852 113.98201288564 22.564770379133 64
75867 113.98326646456 22.54340440827 64
75869 113.97960033913 22.543936063106 64
75870 113.97954218276 22.543843065589 64
75871 113.98207480847 22.561852886284 64
75872 113.98166763098 22.562076751591 64
75873 113.98183621594 22.56197113887 64
75879 113.98401271458 22.561014719663 64
75880 113.98403282185 22.56111516862 64
75882 113.95211178699 22.557607236411 64
原始点：（75849 113.970263 22.558992 0）
修正后：（75849 113.98166958385 22.56213701767 64）
修正误差对比：
整个算法流程：
/*
* Created by SharpDevelop.
* User: Administrator
* Date: 2013/6/3
* Time: 17:01
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Web.Services;
using System.Data;
using XuanWu.Software.EasyInfo.BLL;
using XuanWu.Software.EasyInfo.ECModel;
using System.Collections;
using System.Collections.Generic;
using XuanWu.Software.EasyInfo.EnterpriseClient.Common;
using System.Threading.Tasks;
using System.Threading;
using System.Text;
namespace XuanWu.Software.EasyInfo.EnterpriseClient.Services
{
/// <summary>
/// 数据坐标转换服务(只对TPlaActualVisit表和TBasStore两个表进行)
/// </summary>
[WebService
( Name = "TransformSqlLatLng",
Description = "TransformSqlLatLng",
Namespace = "http://www.TransformSqlLatLng.example"
)
]
public class TransformSqlLatLng : WebService
{
[WebMethod]
public void ConvertSqlLatLng(string datasourceid,int entnumber)
{
try {
Transform _TBasStore=new Transform_TBasStore(new Guid(datasourceid),entnumber);
Transform _TPlaActualVisit=new Transform_TPlaActualVisit(new Guid(datasourceid),entnumber);
_TBasStore.ConvertLatLng();
_TPlaActualVisit.ConvertLatLng();
} catch (Exception ex) {
Common.LogBll.Error(ex.Message.ToString());
}
}
}
/// <summary>
/// 坐标转换基类
/// </summary>
public abstract class Transform
{
/// <summary>
/// 数据源ID
/// </summary>
protected Guid datasourceid;
/// <summary>
/// 企业E号
/// </summary>
protected int EnterpriseNumber;
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected abstract InitObj GetInitObj(Object enumer);
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected abstract List<DataSourceInfo> GetDatasource();
/// <summary>
/// 从数据库读取数据
/// </summary>
/// <returns></returns>
protected abstract DataTable GetDBDataToDataTable(DataSourceInfo _datasource);
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected abstract StringBuilder BuildUpdateSql(List<InitObj> InitObjects);
/// <summary>
/// 数据源BLL
/// </summary>
protected BLL.DataSource mDataSource=new BLL.DataSource(ECConfiguration.DalProvider, ECConfiguration.ConnectionString);
/// <summary>
/// 转换逻辑
/// </summary>
/// <param name="datasourceid"></param>
public void ConvertLatLng(){
try {
List<DataSourceInfo> _datasources=GetDatasource();
_datasources.ForEach(_datasource=>{
//1.查询采集表数据进行坐标转换
DataTable _upinfo=GetDBDataToDataTable(_datasource);
if(_upinfo!=null&&_upinfo.Rows.Count>0){
//定义分组规则，限定每次转换多少个坐标点
var _count=_upinfo.Rows.Count;
var _loopcount=_count/20;//定义查询次数
var _modcount=_count%20;//余数
//装载源坐标数据
var _emumer=_upinfo.Rows.GetEnumerator();
List<InitObj> _current=new List<InitObj>();
while (_emumer.MoveNext()) {
var _currow=_emumer.Current;
var _initObj=GetInitObj(_currow);
_current.Add(_initObj);
}
//分组请求，每组20个坐标点，请求结束后返回坐标点，然后并接语句更新采集表数据库（每组20条update语句）。
for (int i = 0; i<_loopcount+1; i++) {
List<InitObj> _sPoints=new List<InitObj>();
if(i==_loopcount&&_modcount>0)
_sPoints= _current.GetRange(i*20,_modcount);//源坐标对象,最后一次取
else if(i<_loopcount)
_sPoints= _current.GetRange(i*20,(i+1)*20);//源坐标对象，整次取
else
return;
var _tPoints=new StringBuilder();//实际转换坐标
var _tfrom="";
List<string> _tlats=new List<string>();
List<string> _tlngs=new List<string>();
_sPoints.ForEach(c=>{
_tlats.Add(c._lat);
_tlngs.Add(c._lng);
_tfrom=c._from;
});
//执行算法
List<ResulteObj> _results=this.TransformLatLngs<ResulteObj>(_tfrom.ToString(),string.Join(",",_tlats),string.Join(",",_tlngs));
//数据转换后进行对接
var _lastIndex=_sPoints.Count-1;
_results.ForEach(r=>{
var _index=_results.IndexOf(r);
if(_index<=_lastIndex&&r.error==0){
InitObj _obj=_sPoints[_index];
_obj.lat=r.x;
_obj.lng=r.y;
}
});
//并接SQL语句精心更新
StringBuilder _updateSql=BuildUpdateSql(_sPoints);
//执行更新
_datasource.description=_updateSql.ToString();
Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
}
}
});
} catch (Exception ex) {
Common.LogBll.Error(ex.Message.ToString());
}
}
/// <summary>
/// 转换算法
/// </summary>
/// <param name="from"></param>
/// <param name="lats"></param>
/// <param name="lngs"></param>
/// <returns></returns>
protected dynamic TransformLatLngs<T>(string from,string lats,string lngs)
{
if(string.IsNullOrEmpty(from))
return null;
System.Net.Http.HttpClient _client=new System.Net.Http.HttpClient();
_client.Timeout=new TimeSpan(0,0,0,0,500);
var _from=Convert.ToInt32(from);
var _lat=lats;
var _lng=lngs;
var _url=string.Format("http://api.map.baidu.com/ag/coord/convert?from={0}&to=4&mode=1&x={1}&y={2}",_from,_lat,_lng);
var _task=_client.GetStringAsync(_url);
var str=string.Empty;
var _resulte=string.Empty;
List<ResulteObj> _arrys=new List<ResulteObj>();
try {
_task.Wait();
} catch (AggregateException ex) {
var _error="坐标转换异常日志:\r\n";
foreach (var _ex in ex.InnerExceptions) {
Common.LogBll.Error(string.Format("{0}{1}",_error,_ex.Message.ToString()));
}
}
finally{
switch (_task.Status) {
case TaskStatus.Created:
break;
case TaskStatus.WaitingForActivation:
break;
case TaskStatus.WaitingToRun:
break;
case TaskStatus.Running:
break;
case TaskStatus.WaitingForChildrenToComplete:
break;
case TaskStatus.RanToCompletion:
str="请求成功！";
_arrys= Newtonsoft.Json.JsonConvert.DeserializeObject<List<ResulteObj>>(_task.Result);
_arrys.ForEach(r=>{
if(r.error==0){
r.x= Encoding.Default.GetString(Convert.FromBase64String(r.x));
r.y= Encoding.Default.GetString(Convert.FromBase64String(r.y));
}
});
_resulte = _arrys != null && _arrys.Count > 0 ? Ext.Net.JSON.Serialize(_arrys) : string.Empty;
break;
case TaskStatus.Canceled:
str="由于超时等原因，任务被取消！";
break;
case TaskStatus.Faulted:
str="由于给定的连接无响应或者不存在，任务执行失败！";
break;
default:
str="Invalid value for TaskStatus！";
break;
}
}
Type type = typeof(T);
switch (type.FullName)
{
case "System.String":
return _resulte;
default:
return _arrys;
}
}
}
/// <summary>
/// 转换基础信息表
/// </summary>
public class Transform_TBasStore:Transform
{
public Transform_TBasStore(Guid datasourceid,int entnumber)
{
this.datasourceid=datasourceid;
this.EnterpriseNumber=entnumber;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="_datasource"></param>
/// <returns></returns>
protected override DataTable GetDBDataToDataTable(DataSourceInfo _datasource)
{
DataTable _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
return _data;
}
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected override InitObj GetInitObj(Object enumer)
{
var _currow=enumer as DataRow;
InitObj _initObj=new InitObj();
_initObj._majorkey=_currow["StoreID"].ToString();
if(_currow["Col3"]!=null&&_currow["Col4"]!=null&&_currow["Col5"]!=null)
{
_initObj._lat=_currow["Col3"].ToString();
_initObj._lng=_currow["Col4"].ToString();
_initObj._from=_currow["Col5"].ToString();
}
return _initObj;
}
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected override List<DataSourceInfo> GetDatasource()
{
var _datasource=mDataSource.GetDataSourceByID(datasourceid);
List<DataSourceInfo> lst=new List<DataSourceInfo>();
_datasource.description="select StoreID,Col3,Col4,Col5 from TBasStore where col5=0";//查询GPS坐标SQL
_datasource.datasourcetype=1;//默认全部是SQL
lst.Add(_datasource);
//_datasource.description="";//查询GOOGLE坐标SQL
//lst.Add(_datasource);
return lst;
}
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
{
StringBuilder _updateSql=new StringBuilder();
InitObjects.ForEach(s=>{
if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&!string.IsNullOrEmpty(s._majorkey))
_updateSql.AppendFormat(@"UPDATE TBasStore SET Col3='{0}',Col4='{1}',Col5='64' WHERE StoreID='{2}';",s.lat,s.lng,s._majorkey);
});
return _updateSql;
}
}
/// <summary>
/// 转换信息采集表
/// </summary>
public class Transform_TPlaActualVisit:Transform
{
public Transform_TPlaActualVisit(Guid datasourceid,int entnumber)
{
this.datasourceid=datasourceid;
this.EnterpriseNumber=entnumber;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="_datasource"></param>
/// <returns></returns>
protected override DataTable GetDBDataToDataTable(DataSourceInfo _datasource)
{
DataTable _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
//筛选出真正需要转换的数据行
DataTable _resulte=_data.Clone();
foreach (DataRow _row in _data.Rows) {
if(_row["PositioningType"]!=null&&_row["LatLng"]!=null){
string[] _slatlng=_row["PositioningType"].ToString().Split(';');
string[] _pionts=_row["LatLng"].ToString().Split('|');
if(_slatlng.Length==6&&_pionts.Length==2&&_pionts[0]!="0"&&_pionts[1]!="0"&&_slatlng[4]=="0"){
_resulte.ImportRow(_row);
}
}
}
return _resulte;
}
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected override InitObj GetInitObj(Object enumer)
{
var _currow=enumer as DataRow;
InitObj _initObj=new InitObj();
_initObj._majorkey=_currow["ActualVisitID"].ToString();
if(_currow["LatLng"]!=null&&_currow["PositioningType"]!=null)
{
string[] _slatlng=_currow["PositioningType"].ToString().Split(';');
var _from=_slatlng[4];
var _latlng=_currow["LatLng"].ToString().Split('|');
_initObj._lat=_latlng[0];
_initObj._lng=_latlng[1];
_initObj._from=_from;
_initObj._source="gis://{0};"+string.Format("{0};{1};64;{2}",_slatlng[2],_slatlng[3],_slatlng[5]);
}
return _initObj;
}
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected override List<DataSourceInfo> GetDatasource()
{
var _datasource=mDataSource.GetDataSourceByID(datasourceid);
List<DataSourceInfo> lst=new List<DataSourceInfo>();
_datasource.description="select ActualVisitID,LatLng,PositioningType from TPlaActualVisit";//查询GPS坐标SQL
_datasource.datasourcetype=1;//默认全部是SQL
lst.Add(_datasource);
//_datasource.description="";//查询GOOGLE坐标SQL
//lst.Add(_datasource);
return lst;
}
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
{
StringBuilder _updateSql=new StringBuilder();
InitObjects.ForEach(s=>{
if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&
s._from=="0"&&
!string.IsNullOrEmpty(s._majorkey)&&!string.IsNullOrEmpty(s._source))
{
var _latlng=string.Format("{0}|{1}",s.lat,s.lng);
var _slatlng=string.Format(s._source,string.Format("{0};{1}",s.lng,s.lat));
_updateSql.AppendFormat(@"UPDATE TPlaActualVisit SET LatLng='{0}',PositioningType='{1}' WHERE ActualVisitID='{2}';",_latlng,_slatlng,s._majorkey);
}
});
return _updateSql;
}
}
/// <summary>
/// 转换结果类
/// </summary>
public class ResulteObj
{
/// <summary>
/// 结果编码
/// </summary>
public int error { get; set; }
/// <summary>
/// 转换后的纬度
/// </summary>
public string x { get; set; }
/// <summary>
/// 转换后的经度
/// </summary>
public string y { get; set; }
}
/// <summary>
/// 原始对象
/// </summary>
public class InitObj{
/// <summary>
/// 数据库主键
/// </summary>
public string _majorkey { get; set; }
/// <summary>
/// 采集原始数据
/// </summary>
public string _source{ get; set; }
/// <summary>
/// 数据库坐标定位来源
/// </summary>
public string _from { get; set; }
/// <summary>
/// 数据库源纬度
/// </summary>
public string _lng { get; set; }
/// <summary>
/// 数据库源经度
/// </summary>
public string _lat { get; set; }
/// <summary>
/// 转换后的纬度
/// </summary>
public string lng { get; set; }
/// <summary>
/// 转换后的经度
/// </summary>
public string lat { get; set; }
}
}
* Created by SharpDevelop.
* User: Administrator
* Date: 2013/6/3
* Time: 17:01
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Web.Services;
using System.Data;
using XuanWu.Software.EasyInfo.BLL;
using XuanWu.Software.EasyInfo.ECModel;
using System.Collections;
using System.Collections.Generic;
using XuanWu.Software.EasyInfo.EnterpriseClient.Common;
using System.Threading.Tasks;
using System.Threading;
using System.Text;
namespace XuanWu.Software.EasyInfo.EnterpriseClient.Services
{
/// <summary>
/// 数据坐标转换服务(只对TPlaActualVisit表和TBasStore两个表进行)
/// </summary>
[WebService
( Name = "TransformSqlLatLng",
Description = "TransformSqlLatLng",
Namespace = "http://www.TransformSqlLatLng.example"
)
]
public class TransformSqlLatLng : WebService
{
[WebMethod]
public void ConvertSqlLatLng(string datasourceid,int entnumber)
{
try {
Transform _TBasStore=new Transform_TBasStore(new Guid(datasourceid),entnumber);
Transform _TPlaActualVisit=new Transform_TPlaActualVisit(new Guid(datasourceid),entnumber);
_TBasStore.ConvertLatLng();
_TPlaActualVisit.ConvertLatLng();
} catch (Exception ex) {
Common.LogBll.Error(ex.Message.ToString());
}
}
}
/// <summary>
/// 坐标转换基类
/// </summary>
public abstract class Transform
{
/// <summary>
/// 数据源ID
/// </summary>
protected Guid datasourceid;
/// <summary>
/// 企业E号
/// </summary>
protected int EnterpriseNumber;
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected abstract InitObj GetInitObj(Object enumer);
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected abstract List<DataSourceInfo> GetDatasource();
/// <summary>
/// 从数据库读取数据
/// </summary>
/// <returns></returns>
protected abstract DataTable GetDBDataToDataTable(DataSourceInfo _datasource);
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected abstract StringBuilder BuildUpdateSql(List<InitObj> InitObjects);
/// <summary>
/// 数据源BLL
/// </summary>
protected BLL.DataSource mDataSource=new BLL.DataSource(ECConfiguration.DalProvider, ECConfiguration.ConnectionString);
/// <summary>
/// 转换逻辑
/// </summary>
/// <param name="datasourceid"></param>
public void ConvertLatLng(){
try {
List<DataSourceInfo> _datasources=GetDatasource();
_datasources.ForEach(_datasource=>{
//1.查询采集表数据进行坐标转换
DataTable _upinfo=GetDBDataToDataTable(_datasource);
if(_upinfo!=null&&_upinfo.Rows.Count>0){
//定义分组规则，限定每次转换多少个坐标点
var _count=_upinfo.Rows.Count;
var _loopcount=_count/20;//定义查询次数
var _modcount=_count%20;//余数
//装载源坐标数据
var _emumer=_upinfo.Rows.GetEnumerator();
List<InitObj> _current=new List<InitObj>();
while (_emumer.MoveNext()) {
var _currow=_emumer.Current;
var _initObj=GetInitObj(_currow);
_current.Add(_initObj);
}
//分组请求，每组20个坐标点，请求结束后返回坐标点，然后并接语句更新采集表数据库（每组20条update语句）。
for (int i = 0; i<_loopcount+1; i++) {
List<InitObj> _sPoints=new List<InitObj>();
if(i==_loopcount&&_modcount>0)
_sPoints= _current.GetRange(i*20,_modcount);//源坐标对象,最后一次取
else if(i<_loopcount)
_sPoints= _current.GetRange(i*20,(i+1)*20);//源坐标对象，整次取
else
return;
var _tPoints=new StringBuilder();//实际转换坐标
var _tfrom="";
List<string> _tlats=new List<string>();
List<string> _tlngs=new List<string>();
_sPoints.ForEach(c=>{
_tlats.Add(c._lat);
_tlngs.Add(c._lng);
_tfrom=c._from;
});
//执行算法
List<ResulteObj> _results=this.TransformLatLngs<ResulteObj>(_tfrom.ToString(),string.Join(",",_tlats),string.Join(",",_tlngs));
//数据转换后进行对接
var _lastIndex=_sPoints.Count-1;
_results.ForEach(r=>{
var _index=_results.IndexOf(r);
if(_index<=_lastIndex&&r.error==0){
InitObj _obj=_sPoints[_index];
_obj.lat=r.x;
_obj.lng=r.y;
}
});
//并接SQL语句精心更新
StringBuilder _updateSql=BuildUpdateSql(_sPoints);
//执行更新
_datasource.description=_updateSql.ToString();
Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
}
}
});
} catch (Exception ex) {
Common.LogBll.Error(ex.Message.ToString());
}
}
/// <summary>
/// 转换算法
/// </summary>
/// <param name="from"></param>
/// <param name="lats"></param>
/// <param name="lngs"></param>
/// <returns></returns>
protected dynamic TransformLatLngs<T>(string from,string lats,string lngs)
{
if(string.IsNullOrEmpty(from))
return null;
System.Net.Http.HttpClient _client=new System.Net.Http.HttpClient();
_client.Timeout=new TimeSpan(0,0,0,0,500);
var _from=Convert.ToInt32(from);
var _lat=lats;
var _lng=lngs;
var _url=string.Format("http://api.map.baidu.com/ag/coord/convert?from={0}&to=4&mode=1&x={1}&y={2}",_from,_lat,_lng);
var _task=_client.GetStringAsync(_url);
var str=string.Empty;
var _resulte=string.Empty;
List<ResulteObj> _arrys=new List<ResulteObj>();
try {
_task.Wait();
} catch (AggregateException ex) {
var _error="坐标转换异常日志:\r\n";
foreach (var _ex in ex.InnerExceptions) {
Common.LogBll.Error(string.Format("{0}{1}",_error,_ex.Message.ToString()));
}
}
finally{
switch (_task.Status) {
case TaskStatus.Created:
break;
case TaskStatus.WaitingForActivation:
break;
case TaskStatus.WaitingToRun:
break;
case TaskStatus.Running:
break;
case TaskStatus.WaitingForChildrenToComplete:
break;
case TaskStatus.RanToCompletion:
str="请求成功！";
_arrys= Newtonsoft.Json.JsonConvert.DeserializeObject<List<ResulteObj>>(_task.Result);
_arrys.ForEach(r=>{
if(r.error==0){
r.x= Encoding.Default.GetString(Convert.FromBase64String(r.x));
r.y= Encoding.Default.GetString(Convert.FromBase64String(r.y));
}
});
_resulte = _arrys != null && _arrys.Count > 0 ? Ext.Net.JSON.Serialize(_arrys) : string.Empty;
break;
case TaskStatus.Canceled:
str="由于超时等原因，任务被取消！";
break;
case TaskStatus.Faulted:
str="由于给定的连接无响应或者不存在，任务执行失败！";
break;
default:
str="Invalid value for TaskStatus！";
break;
}
}
Type type = typeof(T);
switch (type.FullName)
{
case "System.String":
return _resulte;
default:
return _arrys;
}
}
}
/// <summary>
/// 转换基础信息表
/// </summary>
public class Transform_TBasStore:Transform
{
public Transform_TBasStore(Guid datasourceid,int entnumber)
{
this.datasourceid=datasourceid;
this.EnterpriseNumber=entnumber;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="_datasource"></param>
/// <returns></returns>
protected override DataTable GetDBDataToDataTable(DataSourceInfo _datasource)
{
DataTable _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
return _data;
}
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected override InitObj GetInitObj(Object enumer)
{
var _currow=enumer as DataRow;
InitObj _initObj=new InitObj();
_initObj._majorkey=_currow["StoreID"].ToString();
if(_currow["Col3"]!=null&&_currow["Col4"]!=null&&_currow["Col5"]!=null)
{
_initObj._lat=_currow["Col3"].ToString();
_initObj._lng=_currow["Col4"].ToString();
_initObj._from=_currow["Col5"].ToString();
}
return _initObj;
}
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected override List<DataSourceInfo> GetDatasource()
{
var _datasource=mDataSource.GetDataSourceByID(datasourceid);
List<DataSourceInfo> lst=new List<DataSourceInfo>();
_datasource.description="select StoreID,Col3,Col4,Col5 from TBasStore where col5=0";//查询GPS坐标SQL
_datasource.datasourcetype=1;//默认全部是SQL
lst.Add(_datasource);
//_datasource.description="";//查询GOOGLE坐标SQL
//lst.Add(_datasource);
return lst;
}
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
{
StringBuilder _updateSql=new StringBuilder();
InitObjects.ForEach(s=>{
if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&!string.IsNullOrEmpty(s._majorkey))
_updateSql.AppendFormat(@"UPDATE TBasStore SET Col3='{0}',Col4='{1}',Col5='64' WHERE StoreID='{2}';",s.lat,s.lng,s._majorkey);
});
return _updateSql;
}
}
/// <summary>
/// 转换信息采集表
/// </summary>
public class Transform_TPlaActualVisit:Transform
{
public Transform_TPlaActualVisit(Guid datasourceid,int entnumber)
{
this.datasourceid=datasourceid;
this.EnterpriseNumber=entnumber;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="_datasource"></param>
/// <returns></returns>
protected override DataTable GetDBDataToDataTable(DataSourceInfo _datasource)
{
DataTable _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
//筛选出真正需要转换的数据行
DataTable _resulte=_data.Clone();
foreach (DataRow _row in _data.Rows) {
if(_row["PositioningType"]!=null&&_row["LatLng"]!=null){
string[] _slatlng=_row["PositioningType"].ToString().Split(';');
string[] _pionts=_row["LatLng"].ToString().Split('|');
if(_slatlng.Length==6&&_pionts.Length==2&&_pionts[0]!="0"&&_pionts[1]!="0"&&_slatlng[4]=="0"){
_resulte.ImportRow(_row);
}
}
}
return _resulte;
}
/// <summary>
/// 枚举原始对象
/// </summary>
/// <param name="enumer"></param>
/// <returns></returns>
protected override InitObj GetInitObj(Object enumer)
{
var _currow=enumer as DataRow;
InitObj _initObj=new InitObj();
_initObj._majorkey=_currow["ActualVisitID"].ToString();
if(_currow["LatLng"]!=null&&_currow["PositioningType"]!=null)
{
string[] _slatlng=_currow["PositioningType"].ToString().Split(';');
var _from=_slatlng[4];
var _latlng=_currow["LatLng"].ToString().Split('|');
_initObj._lat=_latlng[0];
_initObj._lng=_latlng[1];
_initObj._from=_from;
_initObj._source="gis://{0};"+string.Format("{0};{1};64;{2}",_slatlng[2],_slatlng[3],_slatlng[5]);
}
return _initObj;
}
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="datasourceid"></param>
/// <returns></returns>
protected override List<DataSourceInfo> GetDatasource()
{
var _datasource=mDataSource.GetDataSourceByID(datasourceid);
List<DataSourceInfo> lst=new List<DataSourceInfo>();
_datasource.description="select ActualVisitID,LatLng,PositioningType from TPlaActualVisit";//查询GPS坐标SQL
_datasource.datasourcetype=1;//默认全部是SQL
lst.Add(_datasource);
//_datasource.description="";//查询GOOGLE坐标SQL
//lst.Add(_datasource);
return lst;
}
/// <summary>
/// 并接更新语句
/// </summary>
/// <param name="InitObjects"></param>
/// <returns></returns>
protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
{
StringBuilder _updateSql=new StringBuilder();
InitObjects.ForEach(s=>{
if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&
s._from=="0"&&
!string.IsNullOrEmpty(s._majorkey)&&!string.IsNullOrEmpty(s._source))
{
var _latlng=string.Format("{0}|{1}",s.lat,s.lng);
var _slatlng=string.Format(s._source,string.Format("{0};{1}",s.lng,s.lat));
_updateSql.AppendFormat(@"UPDATE TPlaActualVisit SET LatLng='{0}',PositioningType='{1}' WHERE ActualVisitID='{2}';",_latlng,_slatlng,s._majorkey);
}
});
return _updateSql;
}
}
/// <summary>
/// 转换结果类
/// </summary>
public class ResulteObj
{
/// <summary>
/// 结果编码
/// </summary>
public int error { get; set; }
/// <summary>
/// 转换后的纬度
/// </summary>
public string x { get; set; }
/// <summary>
/// 转换后的经度
/// </summary>
public string y { get; set; }
}
/// <summary>
/// 原始对象
/// </summary>
public class InitObj{
/// <summary>
/// 数据库主键
/// </summary>
public string _majorkey { get; set; }
/// <summary>
/// 采集原始数据
/// </summary>
public string _source{ get; set; }
/// <summary>
/// 数据库坐标定位来源
/// </summary>
public string _from { get; set; }
/// <summary>
/// 数据库源纬度
/// </summary>
public string _lng { get; set; }
/// <summary>
/// 数据库源经度
/// </summary>
public string _lat { get; set; }
/// <summary>
/// 转换后的纬度
/// </summary>
public string lng { get; set; }
/// <summary>
/// 转换后的经度
/// </summary>
public string lat { get; set; }
}
}
