erp/SiteCore/Handler/sync2.prod.cs

using SiteCore.helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace SiteCore.Handler
{
    public partial class sync2
    {
        double comLng = 117.07548;
        double comLat = 25.135471;

        //117.07548|25.135471"
        public void get_prod()
        {
            string sql = "select con,uptime from c_json";
            DataTable dt = ErpSqlHelper.ExecuteDataSet(sql).Tables[0];
            string str = "";
            string dtime = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                str = dr["con"].ToString();
                str = str.ToString().Replace("§", "'");//№※
                dtime = Convert.ToDateTime(dr["uptime"]).ToString("yy-MM-dd HH:mm:ss");
            }
            else
            {
                //dtime = DateTime.Now.ToString("yy-MM-dd HH:mm:ss");
            }

            con.Response.Write("{\"type\":\"success\",\"result\":[" + str.ToString() + "],\"date\":\"" + dtime + "\"}");
            //returnSuccess("["+str.ToString()+"]");
        }


        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,0.0 as total,0.0 as deli,0.0 as volume,0.0 as truckcount from c_prod  group by sitename,project,cgrade ;");//where truckcode='32'
            //Total,Deli,volume,TruckCount,
            sql.Append("select a.autoid,a.sitename,a.project,a.cgrade,a.truckcode,a.total,a.deli,a.volume,a.truckcount, '' as lat,'' as lng,'' as flag,0.0 as rp,b.isarrive,b.arrivetime,b.isback,b.backtime,a.pdate,a.completetime from c_prod as a left join c_prodstate as b on a.autoid=b.prodid ;");//where  a.truckcode='018'
            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 = "";
            string cid = "";
            double enLng = 0;
            double enLat = 0;
            double dist = 0;
            double dist2 = 0;
            int p = 0;
            List<string> finLst = new List<string>();
            //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"];
                    cid = cdrv["id"].ToString();
                    if (cdrv["lng"].ToString() != "" && cdrv["lat"].ToString() != "")
                    {
                        enLng = Convert.ToDouble(cdrv["lng"]);
                        enLat = Convert.ToDouble(cdrv["lat"]);
                    }
                }
                prodStr = new StringBuilder();
                //if(dr["sitename"].ToString()== "东新水沟(马头宫庙-龙津河)黑臭水体改造工程")
                //{
                //    int c = 0;
                //}
                prodDv.RowFilter = "sitename='" + dr["sitename"] + "' and project='" + dr["project"] + "' and cgrade='" + dr["cgrade"] + "'";
                prodDv.Sort = "pdate desc";
                if (prodDv.Count > 0)
                {
                    //0.0 as deli,0.0 as volume,0.0 as truckcount
                    //这里把最新的方量信息填入
                    dr["total"] = prodDv[0]["total"];
                    dr["deli"] = prodDv[0]["deli"];
                    dr["volume"] = prodDv[0]["volume"];
                    dr["truckcount"] = prodDv[0]["truckcount"];
                }
                y = 0;
                bool isArrive = false;
                bool isBack = false;
                object arrivetime = "";
                object backtime = "";
                foreach (DataRowView drv in prodDv)
                {
                    if (drv["truckcode"].ToString() == "") continue;
                    if (drv["isback"] != DBNull.Value && Convert.ToInt32(drv["isback"]) == 1)
                    {
                        if (backtime.ToString() == "" || (backtime.ToString() != "" && DateTime.Now.Subtract(Convert.ToDateTime(backtime)).TotalMinutes >= 5))
                        {
                            continue;
                        }
                    }
                    arrivetime = "";
                    backtime = "";
                    isBack = false;
                    if (Convert.ToDateTime(drv["completetime"]).ToString("yyyy") == "1900")
                    {
                        isArrive = false;
                    }
                    else
                    {
                        if (drv["isArrive"] == DBNull.Value || Convert.ToInt32(drv["isArrive"]) == 0)//未到达
                        {
                            isArrive = checkIsArrive(Convert.ToDateTime(drv["completetime"]).ToString("yyyy-MM-dd HH:mm:ss"), enLng, enLat, drv["autoid"], drv["truckcode"], out arrivetime);
                        }
                        else
                        {
                            arrivetime = drv["arrivetime"];
                            isArrive = true;
                        }
                    }
                    //如果回厂了
                    if (isArrive)
                    {
                        isBack = checkIsBack(Convert.ToDateTime(arrivetime).ToString("yyyy-MM-dd HH:mm:ss"), comLng, comLat, drv["autoid"], drv["truckcode"], out backtime);
                    }
                    if (isBack)
                    {
                        if (backtime.ToString() == "" || (backtime.ToString() != "" && DateTime.Now.Subtract(Convert.ToDateTime(backtime)).TotalMinutes >= 5))
                        {
                            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() != "")
                    {
                        //if (psDv.Count >0 && Convert.ToInt32(drv["isBack"])==1)
                        //{
                        //    continue;
                        //}
                        //如果发车时间小于等于25分钟，则出厂
                        //如果前10次经纬度的值，是回来的，则是回厂

                        if (!isArrive)// 
                        {
                            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
                            {
                                pgSql.AppendFormat("if (select count(0) from c_prodstate where prodid={0})>0 begin ", drv["autoid"]);
                                pgSql.AppendFormat("update c_prodstate set isarrive=1, arrivetime=getdate() where isarrive=0 and prodid={0} ", drv["autoid"]);
                                pgSql.Append(" end else begin ");
                                pgSql.AppendFormat("insert into c_prodstate(prodid,isarrive,arrivetime)values({0},{1},getdate()) ", drv["autoid"], 1);
                                pgSql.Append(" end ");
                            }
                            //prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"1\"", drv["truckcode"], p).Append("}");
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"1\",\"tcount\":\"{2}\",\"volume\":\"{3}\"",
                                drv["truckcode"], p, drv["truckcount"], drv["volume"]).Append("}");
                            y++;
                        }
                        else if (enLat > 0 && isArrive)//
                        {
                            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 c_prodstate set isback=1,backtime=getdate() where isarrive=1 and prodid={0} ;", drv["autoid"]);
                            }
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"{1}\",\"to\":\"0\",\"tcount\":\"{2}\",\"volume\":\"{3}\"",
                                drv["truckcode"], p, drv["truckcount"], drv["volume"]).Append("}");
                            y++;
                        }

                    }
                    else
                    {
                        if (DateTime.Now.Subtract(Convert.ToDateTime(drv["completetime"])).TotalHours < 6)
                        {
                            //prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"-1\",\"to\":\"1\"", drv["truckcode"]).Append("}");
                            prodStr.Append("{").AppendFormat("\"cnum\":\"{0}\",\"pg\":\"-1\",\"to\":\"0\",\"tcount\":\"{1}\",\"volume\":\"{2}\"",
                                drv["truckcode"], p, drv["truckcount"], drv["volume"]).Append("}");
                            y++;
                        }
                    }
                    //y++;
                }
                if (pgSql.Length > 0)
                {
                    ErpSqlHelper.ExecuteNonQuery(pgSql.ToString());
                }
                if (prodStr.Length > 0)
                {
                    if (k > 0) str.Append(",");
                    str.Append("{").AppendFormat("\"sitename\":\"{0}\",\"project\":\"{1}\",\"cgrade\":\"{2}\",\"sitemileage\":\"{3}\",\"address\":\"{4}\",\"lnglat\":\"{5}\",\"id\":\"{6}\",\"data\":{7},\"total\":\"{8}\",\"deli\":\"{9}\",\"truckcount\":\"{10}\"",
                        dr["sitename"], dr["project"], dr["cgrade"], sitemileage, siteaddress, sitelnglat, cid, "[" + prodStr.ToString().TrimEnd(',') + "]", dr["total"], dr["deli"], dr["truckcount"]).Append("}");
                    k++;
                }
            }
            con.Response.Write("{\"type\":\"success\",\"result\":[" + str.ToString() + "],\"date\":\"" + DateTime.Now.ToString("yy-MM-dd HH:mm:ss") + "\"}");
            // returnSuccess("[" + str.ToString() + "]");
            //con.Response.Write();

            //writeGridDataTableJson(dt.Rows.Count, dt);
        }

        public bool checkIsArrive(string sdate, double enlng, double enlat, object autoid, object cnum, out object gpstime)
        {
            gpstime = "";
            string sql = string.Format("select * from c_cargps where platenum='{1}' and datediff(minute,'{0}',gpstime)>=25", sdate, cnum + "号");
            DataTable dt = ErpSqlHelper.ExecuteDataSet(sql).Tables[0];
            double dist = 0;
            StringBuilder pgSql = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                dist = GetDistance(enlat, enlng, Convert.ToDouble(dr["latitude"]), Convert.ToDouble(dr["longitude"]));
                if (Math.Abs(dist) <= 800)
                {
                    gpstime = dr["gpstime"];
                    pgSql.AppendFormat("insert into c_prodstate(prodid,isarrive,arrivetime)values({0},{1},'{2}') ;", autoid, 1, dr["gpstime"]);
                    ErpSqlHelper.ExecuteNonQuery(pgSql.ToString());
                    return true;
                    //break;
                }
            }
            return false;
        }

        public bool checkIsBack(string sdate, double lng, double lat, object autoid, object cnum, out object gpstime)
        {
            gpstime = "";
            string sql = string.Format("select * from c_cargps where platenum='{1}' and datediff(minute,'{0}',gpstime)>=10", sdate, cnum + "号");
            DataTable dt = ErpSqlHelper.ExecuteDataSet(sql).Tables[0];
            double dist = 0;
            StringBuilder pgSql = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                dist = GetDistance(lat, lng, Convert.ToDouble(dr["latitude"]), Convert.ToDouble(dr["longitude"]));
                if (Math.Abs(dist) <= 800)
                {
                    gpstime = dr["gpstime"];
                    pgSql.AppendFormat("update c_prodstate set isback=1,backtime='{1}' where isarrive=1 and prodid={0} ;", autoid, dr["gpstime"]);
                    ErpSqlHelper.ExecuteNonQuery(pgSql.ToString());
                    return true;
                    //break;
                }
            }
            return false;
        }

        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);
            writeGridDataTableJson(dStruct.TotalCount, 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(',');

                double[] llArr = GpsUtil.Gcj02ToBd09(Convert.ToDouble(tlArr[1]), Convert.ToDouble(tlArr[0]));


                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}",
                    llArr[1], llArr[0], 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

    }
}