protected void Button3_Click(object sender, EventArgs e)
{
ComSqlHelper csh = new ComSqlHelper();
DataTable contentData = csh.Query("SELECT * FROM [Qc_CarRunStatus]", "HRBKY").Tables[0];
contentData.Columns.Remove("CID");
contentData.Columns.Add("StartAddress");
contentData.Columns.Add("EndAddress");
List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>();
for (int i = 0; i < contentData.Rows.Count; i++)
{
string sc = contentData.Rows[i]["StartCoord"].ToString();
sc = string.IsNullOrEmpty(sc) ? "0-0" : sc;
string slng = sc.Split('-')[1];
string slat = sc.Split('-')[0];
string ec = contentData.Rows[i]["EndCoord"].ToString();
ec = string.IsNullOrEmpty(ec) ? "0-0" : ec;
string elng = ec.Split('-')[1];
string elat = ec.Split('-')[0];
CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat();
sdl.Lat = double.Parse(slat);
sdl.Lng = double.Parse(slng);
CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat();
edl.Lat = double.Parse(elat);
edl.Lng = double.Parse(elng);
corrds.Add(sdl);
corrds.Add(edl);
}
string[] address = AddressConvert.AddConvertBatch(corrds);
for (int i = 0; i < contentData.Rows.Count; i++)
{
contentData.Rows[i]["StartAddress"] = address[i * 2];
contentData.Rows[i]["EndAddress"] = address[i * 2 + 1];
}
NPOIHelper npoiHelper = new NPOIHelper();
string[] headerDataArray = { "车牌号", "所属企业", "车辆用途", "运营线路", "开始时间", "开始速度", "结束时间", "结束速度", "开始经纬度", "结束经纬度", "开始地址", "结束地址" };
string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData);
npoiHelper.WorkbookName = "车辆运行统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
// 设置导入内容
npoiHelper.HeaderData = headerDataArray;
npoiHelper.ContentData = contentDataArray;
string basepath = HttpRuntime.AppDomainAppPath.ToString();
string filePath = @"UI\Excel\Query\";
string sd = basepath + filePath;
npoiHelper.saveExcel(sd);
}
public ResponseResult getOfflineStatisticDetailOutput(Dictionary <string, string> inparams)
{
ResponseResult Result = null;
string stime;
string etime;
string sysflag;
string num;
string cid;
if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("cid") || !inparams.Keys.Contains("sysflag") || !inparams.Keys.Contains("num"))
{
Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null);
return(Result);
}
try
{
if (inparams["stime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null);
return(Result);
}
if (inparams["etime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null);
return(Result);
}
if (inparams["sysflag"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null);
return(Result);
}
stime = inparams["stime"];
etime = inparams["etime"];
sysflag = inparams["sysflag"];
num = inparams["num"];
cid = inparams["cid"];
DataTable dtStatDetail = GetStatDetailTable(sysflag, cid, stime, etime);
DataTable dtDetailOrg = OrgnizeTableDetailNew(dtStatDetail);
List <OfflineStatDetail> offlineStatDetailList = OrgnizeOfflineStatDetail(dtDetailOrg, Int32.Parse(num));
DataTable contentData = ListToDetailDataTable(offlineStatDetailList);
contentData.Columns.Remove("CID");
// 添加列
contentData.Columns.Add("DurationStr");
// 处理时间，转换为“时、分、秒”
foreach (DataRow row in contentData.Rows)
{
if ((int)row["OffDuration"] != 0)
{
row["DurationStr"] = AnalysisDuration((int)row["OffDuration"]);
}
else
{
row["DurationStr"] = "0";
}
}
contentData.Columns.Remove("OffDuration");
contentData.Columns.Add("StartAddress");
contentData.Columns.Add("EndAddress");
List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>();
for (int i = 0; i < contentData.Rows.Count; i++)
{
string sc = contentData.Rows[i]["FromAddress"].ToString();
sc = string.IsNullOrEmpty(sc) ? "0,0" : sc;
string slng = sc.Split(',')[0];
string slat = sc.Split(',')[1];
string ec = contentData.Rows[i]["ToAddress"].ToString();
ec = string.IsNullOrEmpty(ec) ? "0,0" : ec;
string elng = ec.Split(',')[0];
string elat = ec.Split(',')[1];
CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat();
sdl.Lat = double.Parse(slat);
sdl.Lng = double.Parse(slng);
corrds.Add(sdl);
CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat();
edl.Lat = double.Parse(elat);
edl.Lng = double.Parse(elng);
corrds.Add(edl);
}
string[] address = AddressConvert.AddConvertBatch(corrds);
for (int i = 0; i < contentData.Rows.Count; i++)
{
contentData.Rows[i]["StartAddress"] = address[i * 2];
contentData.Rows[i]["EndAddress"] = address[i * 2 + 1];
}
contentData.Columns.Remove("FromAddress");
contentData.Columns.Remove("ToAddress");
NPOIHelper npoiHelper = new NPOIHelper();
string[] headerDataArray = { "车牌号", "所属企业", "车辆用途", "运营线路", "开始时间", "结束时间", "离线时长", "开始地址", "结束地址" };
string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData);
npoiHelper.WorkbookName = "车辆离线统计明细" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
// 设置导入内容
npoiHelper.HeaderData = headerDataArray;
npoiHelper.ContentData = contentDataArray;
string basepath = HttpRuntime.AppDomainAppPath.ToString();
string filePath = @"UI\Excel\Query\";
string sd = basepath + filePath;
npoiHelper.saveExcel(sd);
Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName);
}
catch (Exception ex)
{
Result = new ResponseResult(ResState.OperationFailed, ex.Message, "");
}
return(Result);
}
public ResponseResult DetailExport(Dictionary <string, string> inparams)
{
ResponseResult Result = null;
string stime;
string etime;
string sysflag;
string sysuid;
string cid;
if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("cid") || !inparams.Keys.Contains("sysflag"))
{
Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null);
return(Result);
}
try
{
if (inparams["stime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null);
return(Result);
}
if (inparams["etime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null);
return(Result);
}
if (inparams["sysflag"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null);
return(Result);
}
stime = inparams["stime"];
etime = inparams["etime"];
cid = inparams["cid"];
sysflag = inparams["sysflag"];
//调用存储过程查询能耗分析明细表
//todo
DataTable contentData = EnergyAnalysisDetail(sysflag, cid, stime, etime);
contentData.Columns.Remove("CID");
contentData.Columns.Add("StartAddress");
contentData.Columns.Add("EndAddress");
// 添加列
contentData.Columns.Add("DurationStr");
// 处理时间，转换为“时、分、秒”
foreach (DataRow row in contentData.Rows)
{
if ((int)row["Duration"] != 0)
{
row["DurationStr"] = AnalysisDuration((long)row["Duration"]);
}
else
{
row["DurationStr"] = "0";
}
}
contentData.Columns.Remove("Duration");
List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>();
for (int i = 0; i < contentData.Rows.Count; i++)
{
string sc = contentData.Rows[i]["FromLatLng"].ToString();
sc = string.IsNullOrEmpty(sc) ? "0,0" : sc;
string slng = sc.Split(',')[1];
string slat = sc.Split(',')[0];
string ec = contentData.Rows[i]["ToLatLng"].ToString();
ec = string.IsNullOrEmpty(ec) ? "0,0" : ec;
string elng = ec.Split(',')[1];
string elat = ec.Split(',')[0];
CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat();
sdl.Lat = double.Parse(slat);
sdl.Lng = double.Parse(slng);
CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat();
edl.Lat = double.Parse(elat);
edl.Lng = double.Parse(elng);
corrds.Add(sdl);
corrds.Add(edl);
}
string[] address = AddressConvert.AddConvertBatch(corrds);
for (int i = 0; i < contentData.Rows.Count; i++)
{
contentData.Rows[i]["StartAddress"] = address[i * 2];
contentData.Rows[i]["EndAddress"] = address[i * 2 + 1];
}
NPOIHelper npoiHelper = new NPOIHelper();
string[] headerDataArray = { "车牌号", "开始时间", "结束时间", "开始位置坐标", "结束位置坐标", "行驶里程", "油耗", "尿素消耗", "平均速度", "开始地址", "结束地址", "行驶时长" };
string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData);
npoiHelper.WorkbookName = "能耗分析明细表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
// 设置导入内容
npoiHelper.HeaderData = headerDataArray;
npoiHelper.ContentData = contentDataArray;
string basepath = HttpRuntime.AppDomainAppPath.ToString();
string filePath = @"UI\Excel\";
string sd = basepath + filePath;
npoiHelper.saveExcel(sd);
Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName);
return(Result);
}
catch (Exception ex)
{
Result = new ResponseResult(ResState.OperationFailed, ex.Message, "");
}
return(Result);
}
/// <summary>
/// 车辆异常下线统计导出
/// </summary>
/// <param name="inparams"></param>
/// <returns></returns>
public ResponseResult getAbnormalOfflineStatisticsoutput(Dictionary <string, string> inparams)
{
ResponseResult Result = null;
string stime;
string etime;
string CarNum;
string CUID;
string CarOwnName;
string Line;
string sysuid;
string sysflag;
string onecaruser;
string num;
if (!inparams.Keys.Contains("stime") || !inparams.Keys.Contains("etime") || !inparams.Keys.Contains("CarNum") || !inparams.Keys.Contains("sysflag") || !inparams.Keys.Contains("CUID") || !inparams.Keys.Contains("CarOwnName") || !inparams.Keys.Contains("Line"))
{
Result = new ResponseResult(ResState.ParamsImperfect, "缺少参数", null);
return(Result);
}
try
{
if (inparams["stime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "开始时间不能为空", null);
return(Result);
}
if (inparams["etime"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "结束时间不能为空", null);
return(Result);
}
if (inparams["sysflag"] == "")
{
Result = new ResponseResult(ResState.ParamsImperfect, "系统标识错误", null);
return(Result);
}
stime = inparams["stime"];
etime = inparams["etime"];
CarNum = inparams["CarNum"];
CUID = inparams["CUID"];
CarOwnName = inparams["CarOwnName"];
Line = inparams["Line"];
sysflag = inparams["sysflag"];
sysuid = inparams["sysuid"];
onecaruser = inparams["onecaruser"];
num = inparams["num"];
//调用存储过程查询警情数据
//todo
DataTable contentData = GetAbnormalStatistics(sysflag, sysuid, stime, etime, CarNum, CUID, CarOwnName, Line, onecaruser, num);
//contentData.Columns.Remove("CID");
//contentData.Columns.Add("StartAddress");
// contentData.Columns.Add("EndAddress");
List <CommLibrary.AddressConvert.DLngLat> corrds = new List <AddressConvert.DLngLat>();
for (int i = 0; i < contentData.Rows.Count; i++)
{
//string sc = contentData.Rows[i]["StartCoord"].ToString();
//sc = string.IsNullOrEmpty(sc) ? "0-0" : sc;
//string slng = sc.Split('-')[1];
//string slat = sc.Split('-')[0];
string ec = contentData.Rows[i]["OffCoord"].ToString();
ec = string.IsNullOrEmpty(ec) ? "0-0" : ec;
string elng = ec.Split(',')[1];
string elat = ec.Split(',')[0];
//CommLibrary.AddressConvert.DLngLat sdl = new AddressConvert.DLngLat();
//sdl.Lat = double.Parse(slat);
//sdl.Lng = double.Parse(slng);
CommLibrary.AddressConvert.DLngLat edl = new AddressConvert.DLngLat();
edl.Lat = double.Parse(elat);
edl.Lng = double.Parse(elng);
//corrds.Add(sdl);
corrds.Add(edl);
}
string[] address = AddressConvert.AddConvertBatch(corrds);
for (int i = 0; i < contentData.Rows.Count; i++)
{
//contentData.Rows[i]["StartAddress"] = address[i * 2];
contentData.Rows[i]["address"] = address[i];
}
NPOIHelper npoiHelper = new NPOIHelper();
string[] headerDataArray = { "车牌号", "车辆用途", "所属企业", "下线时间", "下线经纬度", "下线地址", "运营线路" };
string[][] contentDataArray = npoiHelper.convertDataTableToStringArray(contentData);
npoiHelper.WorkbookName = "车辆异常下线统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
// 设置导入内容
npoiHelper.HeaderData = headerDataArray;
npoiHelper.ContentData = contentDataArray;
string basepath = HttpRuntime.AppDomainAppPath.ToString();
string filePath = @"UI\Excel\Query\";
string sd = basepath + filePath;
npoiHelper.saveExcel(sd);
Result = new ResponseResult(ResState.Success, "", filePath + npoiHelper.WorkbookName);
return(Result);
}
catch (Exception ex)
{
Result = new ResponseResult(ResState.OtherError, "", ex.Message + ex.StackTrace);
return(Result);
}
}