Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        /// <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);
            }
        }