DS7/JobReqWebData/JobGetCarrierData.cs

using log4net;
using Newtonsoft.Json;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace JobReqWebData
{
    public class JobGetCarrierData : IJob
    {
        private ILog log = LogManager.GetLogger(typeof(JobGetCarrierData));

        public void Execute(IJobExecutionContext context)
        {
            try
            {
                string connStr = context.JobDetail.JobDataMap.GetString("ConnectString");
                string reqUrl = context.JobDetail.JobDataMap.GetString("ReqUrl");
                string customer = context.JobDetail.JobDataMap.GetString("Customer");
                string password = context.JobDetail.JobDataMap.GetString("Password");
                int reqTimeout = Convert.ToInt32(context.JobDetail.JobDataMap.GetString("RequestTimeout"));

                log.Debug($"连接字符串：{connStr}");
                log.Debug($"请求URL：{reqUrl}");
                log.Debug($"用户：{customer}");
                log.Debug($"密码：{password}");
                log.Debug($"请求超时：{reqTimeout}");

                //访问url获取爬取的数据
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("custname", customer);
                dic.Add("psw", password);

                log.Debug($"准备请求数据：{dic.Count}");

                string rtn = WebRequestHelper.DoPost(reqUrl, dic, reqTimeout*1000);

                log.Debug($"数据返回：{rtn}");

                //测试
                //string rtn = File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "response.json"));

                #region 模板
                //var jsonTemplate = new
                //{
                //    TD = new dynamic[]
                //    {
                //        new
                //        {
                //            GID = "",
                //            CZNAME = "",
                //            YARDNAME = "",
                //            ChuanMing_En = "",
                //            ChuanMing_Cn = "",
                //            HangCi = "",
                //            HaiGuanHangCi = "",
                //            HangXian = "",
                //            MBLNO = "",
                //            XiangXing = "",
                //            XiangLiang = "",
                //            MuDiGang = "",
                //            ZhongZhuanGang = "",
                //            JiGangKaiShiShiJian = "",
                //            JiGangJieShuShiJian = "",
                //            ChuanGongSi = "",
                //            TongGuanFangShi = "",
                //            CreateTime = DateTime.Now,
                //            XieHuoGang = "",
                //            KaoGangMaTou = "",
                //            ZhuangChuanMaTou = "",
                //            SongHuoDiZhi = "",
                //            LiHuoSongHuoChangQu = "",
                //            JieGangShiJian = ""
                //        }
                //    },
                //    XD = new dynamic[]
                //    {
                //        new
                //        {
                //            GID = "",
                //            MBLNO = "",
                //            YARDNAME = "",
                //            XiangHao = "",
                //            XiangXing = "",
                //            FengHao = "",
                //            NeiDianWaiDian = "",
                //            XiangPiZhong = "",
                //            HuoZhong = "",
                //            ZongZhong = "",
                //            VGM = "",
                //            CheHao = "",
                //            TiXiangShiJian = "",
                //            FanChangShiJian = "",
                //            JiGangShiJian = "",
                //            HuaBo = "",
                //            ZhiDingXiangHaoTiXiangChangQu = "",
                //            ShangJianXiangHao = "",
                //            CreateTime = DateTime.Now
                //        }
                //    },
                //    FD = new dynamic[]
                //    {
                //        new
                //        {
                //            GID = "",
                //            MBLNO = "",
                //            YARDNAME = "",
                //            FDH = "",
                //            XiangHao = "",
                //            XiangXing = "",
                //            QianFeng = "",
                //            JianShu = "",
                //            BaoZhuang = "",
                //            ZhongLiang = "",
                //            ChiMa = "",
                //            CreateTime = DateTime.Now
                //        }
                //    },
                //    WL = new dynamic[]
                //    {
                //        new
                //        {
                //            GID = "",
                //            MBLNO = "",
                //            CZNAME = "",
                //            HuoWuZhuangTai = "",
                //            ShenBaoXiangShu = "",
                //            JianShu = "",
                //            ZhongLiang = "",
                //            YuPeiShiJian = "",
                //            YuPeiJieGuo = "",
                //            YunDiShiJian = "",
                //            YunDiJieGuo = "",
                //            FangXingShiJian = "",
                //            FangXingZhuangTai = "",
                //            ZhuangZaiShiJian = "",
                //            ZhuangZaiJieGuo = "",
                //            CreateTime = DateTime.Now
                //        }
                //    }
                //};
                #endregion

                var parseJsonObj = JsonConvert.DeserializeObject<JsonYZResponse>(rtn);


                //入库
                using (SqlConnection dbcon = new SqlConnection(connStr))
                {
                    dbcon.Open();
                    using (SqlTransaction trans = dbcon.BeginTransaction())
                    {
                        #region 提单
                        //foreach (var item in parseJsonObj.TD)
                        //{
                        //    string sql = ExecuteCommand(item.GetType(), item, item.MBLNO);

                        //    SqlCommand cmd = new SqlCommand(sql, dbcon, trans);
                        //    cmd.ExecuteNonQuery();
                        //}
                        #endregion

                        var nowdatetime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                        #region 运踪
                        foreach (var item in parseJsonObj.YZ)
                        {
                            string sql = ExecuteCommand(item.GetType(), item, item.MBLNO, item.XiangHao, nowdatetime, "MBLNO","XiangHao", "DongTaiShiJian");

                            SqlCommand cmd = new SqlCommand(sql, dbcon, trans);
                            cmd.ExecuteNonQuery();
                        }
                        #endregion


                        trans.Commit();
                    }
                    dbcon.Close();
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                log.Error(ex.StackTrace);
            }
        }

        private string ExecuteCommand(Type itemType, object item, string mblno, string Xianghao,string DongTaiShiJian,string mblnoColName = "MBLNO", string XiangHaoColName = "XiangHao", string DongTaiShiJianColName = "DongTaiShiJian")
        {
            string sqlDel = $"delete {itemType.Name} where {mblnoColName}='{mblno}' and {XiangHaoColName}='{Xianghao}' and downloadtime='{DongTaiShiJian}' ";

            string colStr = string.Empty;
            string valStr = string.Empty;
            PropertyInfo[] propertys = itemType.GetProperties();
            foreach (var prop in propertys)
            {
                if (colStr.Length > 0)
                {
                    colStr += ",";
                    valStr += ",";
                }
                colStr += prop.Name;
                valStr += $"'{prop.GetValue(item, null).ToString().Replace("'","")}'";
            }
            colStr += ",downloadtime";
            valStr += ",'"+ DongTaiShiJian + "'";
            string sqlIns = $"insert into {item.GetType().Name}({colStr}) values({valStr});";

            return sqlDel + sqlIns;
        }
    }
}