erp/SiteCore/Handler/app.huarun.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SiteCore.Handler
{
    public partial class app
    {
        double comLng = 117.075137;
        double comLat = 25.135572;

        public void get_prod()
        {
            //DataStruct dStruct = GetStruct();
            //dStruct.MainWhere = "p1userid=" + CurrentUser.UserID + " and state=0 and isdel=0";
            //dStruct.Order = "createtime desc";
            //DataTable dt = WebCache.GetData("c_user", dStruct);

            StringBuilder sql = new StringBuilder();
            sql.Append("select sitename,project,cgrade from c_prod group by sitename,project,cgrade ;");
            sql.Append("select autoid,sitename,project,cgrade,truckcode,'' as lat,'' as lng,'' as flag,0.0 as rp,(select isback from c_prodstate where prodid=c_prod.autoid ) as isback from c_prod;");
            sql.Append("select * from c_cargps where id in(select max(id) from c_cargps where replace(platenum,'号','') in (select truckcode from c_prod) group by platenum) ;");
            sql.Append("select * from c_cust where sitename in (select sitename from c_prod group by sitename) ;");
            sql.Append("select * from c_prodstate where prodid in (select autoid from c_prod) ;");
            DataSet ds = ErpSqlHelper.ExecuteDataSet(sql.ToString());

            DataTable pgDt = ds.Tables[0];
            DataTable prodDt = ds.Tables[1];
            DataTable gpsDt = ds.Tables[2];
            DataView gpsDv = new DataView(gpsDt);
            DataTable custDt = ds.Tables[3];
            DataView custDv = new DataView(custDt);
            DataView psDv = new DataView(ds.Tables[4]);
            foreach (DataRow dr in prodDt.Rows)
            {
                gpsDv.RowFilter = "PlateNum='" + dr["truckcode"] + "号'";
                if (gpsDv.Count > 0)
                {
                    dr["lat"] = gpsDv[0]["Latitude"];
                    dr["lng"] = gpsDv[0]["Longitude"];
                    dr["flag"] = gpsDv[0]["StateFlag"];
                    //dr["GpsTime"]= gpsDv[0]["GpsTime"];
                }
            }
            DataView prodDv = new DataView(prodDt);
            StringBuilder str = new StringBuilder();
            StringBuilder prodStr = new StringBuilder();
            int k = 0;
            int y = 0;
            double sitemileage = 0;
            double enLng = 0;
            double enLat = 0;
            double dist = 0;
            int p = 0;
            //bool is
            StringBuilder pgSql = new StringBuilder();
            foreach (DataRow dr in pgDt.Rows)
            {

                custDv.RowFilter = "sitename='" + dr["sitename"] + "'";
                if (custDv.Count > 0)
                {
                    DataRowView cdrv = custDv[0];
                    sitemileage = Convert.ToDouble(cdrv["sitemileage"]);
                    if (cdrv["lng"].ToString() != "" && cdrv["lat"].ToString() != "")
                    {
                        enLng = Convert.ToDouble(cdrv["lng"]);
                        enLat = Convert.ToDouble(cdrv["lat"]);
                    }
                }
                prodStr = new StringBuilder();
                prodDv.RowFilter = "sitename='" + dr["sitename"] + "' and project='" + dr["project"] + "' and cgrade='" + dr["cgrade"] + "'";
                y = 0;
                foreach (DataRowView drv in prodDv)
                {
                    if (drv["truckcode"].ToString() == "") continue;
                    if (drv["isback"] != DBNull.Value && Convert.ToInt32(drv["isback"]) == 1) continue;
                    //if(drv["truckcode"].ToString()=="21")
                    //{
                    //    int c = 1;
                    //}
                    if (y > 0) prodStr.Append(",");
                    p = 0;
                    if (sitemileage > 0 && drv["lat"].ToString() != "" && drv["lng"].ToString() != "")
                    {
                        //psDv.RowFilter = "prodid=" + drv["autoid"];
                        //if (psDv.Count >0 && Convert.ToInt32(drv["isBack"])==1)
                        //{
                        //    continue;
                        //}
                        if (psDv.Count < 1 || Convert.ToInt32(drv["isArrive"]) == 0)
                        {
                            dist = GetDistance(comLat, comLng, Convert.ToDouble(drv["lat"]), Convert.ToDouble(drv["lng"]));
                            p = Convert.ToInt32((dist / (sitemileage * 1000.0)) * 10);
                            if ((dist - sitemileage * 1000.0) >= -500) //5-5.0>=-500
                            {
                                if (psDv.Count < 1)
                                {
                                    pgSql.AppendFormat("insert into c_prodstate(prodid,isarrive)values({0},{1}) ;", drv["autoid"], 1);
                                }
                                else
                                {
                                    pgSql.AppendFormat("update set c_prodstate set isarrive=1 where isarrive=0 and prodid={0} ;", drv["autoid"]);
                                }
                            }
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"1\"", drv["truckcode"], p).Append("}");
                        }
                        else if (enLat > 0 && Convert.ToInt32(drv["isArrive"]) == 1)
                        {
                            dist = GetDistance(enLat, enLng, Convert.ToDouble(drv["lat"]), Convert.ToDouble(drv["lng"]));
                            p = Convert.ToInt32((dist / (sitemileage * 1000.0)) * 10);
                            if ((dist - sitemileage * 1000.0) >= -500) //5-5.0>=-500
                            {
                                pgSql.AppendFormat("update set c_prodstate set isback=1 where isarrive=1 and prodid={0} ;", drv["autoid"]);
                            }
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"0\"", drv["truckcode"], p).Append("}");
                        }

                    }
                    else {
                        prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"-1\",\"to\":\"1\"", drv["truckcode"]).Append("}");
                    }
                    y++;
                }
                if (pgSql.Length > 0)
                {
                    SqlHelper.ExecuteNonQuery(pgSql.ToString());
                }
                if (k > 0) str.Append(",");
                str.Append("{").AppendFormat("\"sitename\":\"{0}\",\"project\":\"{1}\",\"cgrade\":\"{2}\",\"sitemileage\":\"{3}\",\"data\":{4}", dr["sitename"], dr["project"], dr["cgrade"], sitemileage, "[" + prodStr.ToString() + "]").Append("}");
                k++;
            }
            returnSuccess("[" + str.ToString() + "]");
            //con.Response.Write();

            //writeGridJson(dt.Rows.Count, Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt));
        }


        public void get_fastprod()
        {
            //DataStruct dStruct = GetStruct();
            //dStruct.MainWhere = "p1userid=" + CurrentUser.UserID + " and state=0 and isdel=0";
            //dStruct.Order = "createtime desc";
            //DataTable dt = WebCache.GetData("c_user", dStruct);

            StringBuilder sql = new StringBuilder();
            sql.Append("select sitename,project,cgrade from c_prod group by sitename,project,cgrade ;");
            sql.Append("select autoid,sitename,project,cgrade,truckcode,'' as lat,'' as lng,'' as flag,0.0 as rp,(select isback from c_prodstate where prodid=c_prod.autoid ) as isback from c_prod;");
            sql.Append("select * from c_cargps where id in(select max(id) from c_cargps where replace(platenum,'号','') in (select truckcode from c_prod) group by platenum) ;");
            sql.Append("select * from c_cust where sitename in (select sitename from c_prod group by sitename) ;");
            sql.Append("select * from c_prodstate where prodid in (select autoid from c_prod) ;");
            DataSet ds = ErpSqlHelper.ExecuteDataSet(sql.ToString());

            DataTable pgDt = ds.Tables[0];
            DataTable prodDt = ds.Tables[1];
            DataTable gpsDt = ds.Tables[2];
            DataView gpsDv = new DataView(gpsDt);
            DataTable custDt = ds.Tables[3];
            DataView custDv = new DataView(custDt);
            DataView psDv = new DataView(ds.Tables[4]);
            foreach (DataRow dr in prodDt.Rows)
            {
                gpsDv.RowFilter = "PlateNum='" + dr["truckcode"] + "号'";
                if (gpsDv.Count > 0)
                {
                    dr["lat"] = gpsDv[0]["Latitude"];
                    dr["lng"] = gpsDv[0]["Longitude"];
                    dr["flag"] = gpsDv[0]["StateFlag"];
                    //dr["GpsTime"]= gpsDv[0]["GpsTime"];
                }
            }
            DataView prodDv = new DataView(prodDt);
            StringBuilder str = new StringBuilder();
            StringBuilder prodStr = new StringBuilder();
            int k = 0;
            int y = 0;
            double sitemileage = 0;
            string siteaddress = "";
            string sitelnglat = "";
            double enLng = 0;
            double enLat = 0;
            double dist = 0;
            double dist2 = 0;
            int p = 0;
            //bool is
            StringBuilder pgSql = new StringBuilder();
            foreach (DataRow dr in pgDt.Rows)
            {

                custDv.RowFilter = "sitename='" + dr["sitename"] + "'";
                if (custDv.Count > 0)
                {
                    DataRowView cdrv = custDv[0];
                    sitemileage = Convert.ToDouble(cdrv["sitemileage"]);
                    siteaddress = cdrv["siteaddress"].ToString();
                    sitelnglat = cdrv["lng"] + "|" + cdrv["lat"];
                    if (cdrv["lng"].ToString() != "" && cdrv["lat"].ToString() != "")
                    {
                        enLng = Convert.ToDouble(cdrv["lng"]);
                        enLat = Convert.ToDouble(cdrv["lat"]);
                    }
                }
                prodStr = new StringBuilder();
                prodDv.RowFilter = "sitename='" + dr["sitename"] + "' and project='" + dr["project"] + "' and cgrade='" + dr["cgrade"] + "'";
                y = 0;
                foreach (DataRowView drv in prodDv)
                {
                    if (drv["truckcode"].ToString() == "") continue;
                    if (drv["isback"] != DBNull.Value && Convert.ToInt32(drv["isback"]) == 1) continue;
                    //if(drv["truckcode"].ToString()=="21")
                    //{
                    //    int c = 1;
                    //}
                    if (y > 0) prodStr.Append(",");
                    p = 0;
                    if (sitemileage > 0 && drv["lat"].ToString() != "" && drv["lng"].ToString() != "")
                    {
                        //psDv.RowFilter = "prodid=" + drv["autoid"];
                        //if (psDv.Count >0 && Convert.ToInt32(drv["isBack"])==1)
                        //{
                        //    continue;
                        //}
                        if (psDv.Count < 1 || Convert.ToInt32(drv["isArrive"]) == 0)
                        {
                            dist = GetDistance(comLat, comLng, Convert.ToDouble(drv["lat"]), Convert.ToDouble(drv["lng"]));
                            dist2 = GetDistance(enLat, enLng, Convert.ToDouble(drv["lat"]), Convert.ToDouble(drv["lng"]));
                            p = Convert.ToInt32((dist / (sitemileage * 1000.0)) * 10);
                            if ((dist - sitemileage * 1000.0) >= -500) //5-5.0>=-500
                            {
                                if (psDv.Count < 1)
                                {
                                    pgSql.AppendFormat("insert into c_prodstate(prodid,isarrive)values({0},{1}) ;", drv["autoid"], 1);
                                }
                                else
                                {
                                    pgSql.AppendFormat("update set c_prodstate set isarrive=1 where isarrive=0 and prodid={0} ;", drv["autoid"]);
                                }
                            }
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"1\"", drv["truckcode"], p).Append("}");
                        }
                        else if (enLat > 0 && Convert.ToInt32(drv["isArrive"]) == 1)
                        {
                            dist = GetDistance(enLat, enLng, Convert.ToDouble(drv["lat"]), Convert.ToDouble(drv["lng"]));
                            p = Convert.ToInt32((dist / (sitemileage * 1000.0)) * 10);
                            if ((dist - sitemileage * 1000.0) >= -500) //5-5.0>=-500
                            {
                                pgSql.AppendFormat("update set c_prodstate set isback=1 where isarrive=1 and prodid={0} ;", drv["autoid"]);
                            }
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"0\"", drv["truckcode"], p).Append("}");
                        }

                    }
                    else {
                        prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"-1\",\"to\":\"1\"", drv["truckcode"]).Append("}");
                    }
                    y++;
                }
                if (pgSql.Length > 0)
                {
                    SqlHelper.ExecuteNonQuery(pgSql.ToString());
                }
                if (k > 0) str.Append(",");
                str.Append("{").AppendFormat("\"sitename\":\"{0}\",\"project\":\"{1}\",\"cgrade\":\"{2}\",\"sitemileage\":\"{3}\",\"address\":\"{4}\",\"lnglat\":\"{5}\",\"data\":{6}", dr["sitename"], dr["project"], dr["cgrade"], sitemileage, siteaddress, sitelnglat, "[" + prodStr.ToString() + "]").Append("}");
                k++;
            }
            returnSuccess("[" + str.ToString() + "]");
            //con.Response.Write();

            //writeGridJson(dt.Rows.Count, Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt));
        }

        public void get_cust()
        {
            DataStruct dStruct = GetPostStruct();
            List<string> lw = new List<string>();
            string key = GetPostString("key");
            if (key.Length > 0) lw.Add(string.Format("(customername like '%{0}%' or sitename like '%{0}%')", key));
            dStruct.Order = "siteid desc";
            dStruct.MainWhere = string.Join(" and ", lw.ToArray());
            DataTable dt = WebCache.GetErpData("c_cust", dStruct);
            writeGridJson(dStruct.TotalCount, Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt));
        }

        public void save_siteposition()
        {
            if (UrlPostParmsCheck("cid,lnglat,tenlnglat"))
            {
                int cid = GetPostInt("cid");
                string lnglat = GetPostString("lnglat");
                string[] lArr = lnglat.Split(',');
                string tenlnglat = GetPostString("tenlnglat");
                string[] tlArr = tenlnglat.Split(',');

                string address = GetPostString("address");
                string mileage = GetPostString("mileage");

                string sql = string.Format("update c_cust set lng='{0}',lat='{1}',tenlng='{2}',tenlat='{3}',siteaddress='{4}',sitemileage={5} where id={6}",
                    lArr[0], lArr[1], tlArr[0], tlArr[1], address, mileage, cid);
                ErpSqlHelper.ExecuteNonQuery(sql);
                returnSuccessMsg("保存成功!");
            }
        }

        #region 距离
        //地球半径，单位米
        private const double EARTH_RADIUS = 6378137;
        /// <summary>
        /// 计算两点位置的距离，返回两点的距离，单位 米
        /// 该公式为GOOGLE提供，误差小于0.2米
        /// </summary>
        /// <param name="lat1">第一点纬度</param>
        /// <param name="lng1">第一点经度</param>
        /// <param name="lat2">第二点纬度</param>
        /// <param name="lng2">第二点经度</param>
        /// <returns></returns>
        public static double GetDistance(double lat1, double lng1, double lat2, double lng2)
        {
            double radLat1 = Rad(lat1);
            double radLng1 = Rad(lng1);
            double radLat2 = Rad(lat2);
            double radLng2 = Rad(lng2);
            double a = radLat1 - radLat2;
            double b = radLng1 - radLng2;
            double result = 2 * Math.Asin(Math.Sqrt(Math.Pow(Math.Sin(a / 2), 2) + Math.Cos(radLat1) * Math.Cos(radLat2) * Math.Pow(Math.Sin(b / 2), 2))) * EARTH_RADIUS;
            return result;
        }

        /// <summary>
        /// 经纬度转化成弧度
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        private static double Rad(double d)
        {
            return (double)d * Math.PI / 180d;
        }
        #endregion
    }
}