Gsmarket/Sources/GS-MarketMG-2018/Gs.DataPush.WebApi/ApiService/SzytDataService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using Gs.DataPush.WebApi.SzytDataInfrace;
using Newtonsoft.Json;
using SysBaseLibs;
using SysDataLibs.TableClass;

namespace Gs.DataPush.WebApi.ApiService
{
	public class SzytDataService
	{
		public static SzytDataService Instance { get; }=new SzytDataService();
		private static string Token { get; set; } = "";
	    private bool FisrtPush { get; set; } = true;
        public string GetToken()
		{
			string lcToken = "";
			sDataInfraceSoapClient loYtInterface = new sDataInfraceSoapClient();
		   GetYtApiName(out string name,out string password);

			//string userInfo = SysDataLibs.AppEnv.SysSetObj.GetString("YTInterfaceLoginInfo");
			string userInfo = "<webService><request><name>" + name + "</name><password>" + password +
		                      "</password></request></webService>";
		    this.LogInfo("【YT-GetToken-Start:" + userInfo);
			string lcTokenInfo = loYtInterface.checkIn(userInfo);
			this.LogInfo("YT-GetToken-END】:"+ lcTokenInfo);
			rsXmlNode loMainNode = rsXmlNode.ParseGenericXml(lcTokenInfo);
			rsXmlNode loResponseNode = loMainNode?.GetChildNode("response");
			if (loResponseNode != null)
			{
				lcToken = loResponseNode.GetChildValue("tokenNo");
			}
			return lcToken;
		}
		public string PushData(List<CheckData> poCheckDatas)
		{
			List<QuickCheckItemJC> loDataList = DataTrans(poCheckDatas);
			head loHead = GetHead(poCheckDatas.FirstOrDefault()?.MarketID);
			string lcRetval = PushData(new webService(){head = loHead,request = new request(){dataList = loDataList}});
			return lcRetval;
		}
		public string PushData(webService obj)
		{
			string xmlStr = SerializerHelper.SerializerToString(obj);
			string lcRetval = PushData(xmlStr);
			return lcRetval;
		}
		public string PushData(string xmlStr)
		{
			sDataInfraceSoapClient loYtInterface = new sDataInfraceSoapClient();
			this.LogInfo("【YT-QuickCheckItemInfo-Start:" + xmlStr);
			string lcRetval = loYtInterface.saveQuickCheckItemInfo(xmlStr);
			this.LogInfo("YT-QuickCheckItemInfo-END】:" + lcRetval);

			if (FisrtPush && lcRetval.Contains("令牌号已过期"))
			{
				FisrtPush = false;
				string lcToken = GetToken();
			    xmlStr = xmlStr.Contains("<tokenNo />") ? xmlStr.Replace("<tokenNo />", "<tokenNo>" + lcToken + "</tokenNo>") : xmlStr.Replace("<tokenNo>" + Token + "</tokenNo>", "<tokenNo>" + lcToken + "</tokenNo>");
                Token = lcToken;
                lcRetval = PushData(xmlStr);
			}
			else
			{
				FisrtPush = true;
			}

			return lcRetval;
		}

		#region 拼接webService对象（私有方法）
		private head GetHead(string marketId)
		{
			if (string.IsNullOrEmpty(marketId))
				return new head();
			head loHead = new head()
			{
				CardIndex = GetMarketCardIndex(marketId),
				marketCode = GetMarketLicenseNo(marketId),
				marketName = GetMarketName(marketId),
				tokenNo = Token
			};
			return loHead;
		}
		private QuickCheckItemJC DataTrans(CheckData poCheckData)
		{
			QuickCheckItemJC loQuickCheckItemJc = new QuickCheckItemJC()
			{
				JCCode = Guid.NewGuid().ToString().Replace("-", ""),
				//scLicenseNo = GetMarketSellerLicenseNo(poCheckData.MarketID,poCheckData.CorpName),
				//scUnitName = GetMarketSellerName(poCheckData.MarketID, poCheckData.CorpName),
				//DABH = GetMarketSellerDabh(poCheckData.MarketID, poCheckData.CorpName),
				//PositionNo = poCheckData.CorpName,
				//MarketType = "2",
				scCardIndex=poCheckData.CardIndex,
				scLicenseNo = poCheckData.LicenseNo,
				scUnitName = poCheckData.UnitName,
				DABH = poCheckData.IdCode,
				PositionNo = poCheckData.PositionNo,
				Name = poCheckData.CorpNameName,
				//MarketType = poCheckData.CorpKind,
				MarketType = "2",
				SubItemCode = GetSubItemCode(poCheckData.SampleId),
				SubItemName = poCheckData.SampleName,
				QuickCheckDate = poCheckData.UploadTime,
				QuickCheckItemCode = GetCheckItemCode(poCheckData.CheckProjectTypeID),
				QuickCheckSubItemCode = GetCheckSubItemCode(poCheckData.CheckProjectTypeID),
				QuickCheckResult = poCheckData.CheckResult == "合格" ? "-" : "+",
				QuickCheckResultValue = poCheckData.CheckValue,
				//QuickCheckResultValueUnit = "",
				QuickCheckUnitId = GetMarketLicenseNo(poCheckData.MarketID),
				QuickCheckUnitName = GetMarketLicenseName(poCheckData.MarketID),
				QuickCheckResultValueUnit = poCheckData.CheckValue,
				QuickCheckResultValueCKarea = poCheckData.CheckLimit,
				QuickCheckResultDependOn = "",
				QuickCheckRemarks = "",
				QuickChecker = poCheckData.CheckUser,
				QuickReChecker = poCheckData.CheckUser,
				JCManufactor = poCheckData.MachineMaker,
				JCModel = poCheckData.MachineID,
				JCSN = "",
				ReviewIs = "0"
			};
			//loQuickCheckItemJc.JCCode = Guid.NewGuid().ToString().Replace("-", "");                //检测编号
			////loQuickCheckItemJc.DABH = loStallInfo.IdCard;//"320502194701151534";             //经营户身份证号码
			//loQuickCheckItemJc.PositionNo = poCheckData.CorpName;// poCheckData.CorpName;       //摊位编号
			////loQuickCheckItemJc.Name = loStallInfo.MarketSellerName;//"韩国金";                           //经营户姓名              Y
			//loQuickCheckItemJc.SubItemCode = poCheckData.SampleId;                   //抽检的品种编码          Y
			//loQuickCheckItemJc.SubItemName = poCheckData.SampleName;                    //抽检的品种名称          Y
			//loQuickCheckItemJc.QuickCheckDate = poCheckData.UploadTime;     //检测时间                Y
			//loQuickCheckItemJc.QuickCheckItemCode = loCheckMapping.szCheckItemTypeGreatCode;               //检测项目分类编号        Y
			//loQuickCheckItemJc.QuickCheckSubItemCode = loCheckMapping.szCheckItemTypeLittleCode;          //检测项目小类编号        Y
			//loQuickCheckItemJc.QuickCheckResult = poCheckData.CheckResult == "合格" ? "-" : "+";                //检测项目结果（定性值）  Y （+ / -）
			//loQuickCheckItemJc.QuickCheckResultValue = poCheckData.CheckValue;              //检测项目结果值
			//loQuickCheckItemJc.QuickCheckResultValueUnit = "";          //实际值单位
			//loQuickCheckItemJc.QuickCheckResultValueCKarea = poCheckData.CheckLimit;        //检测项目结果值参考范围
			//loQuickCheckItemJc.QuickCheckResultDependOn = "";           //检测依据
			//loQuickCheckItemJc.QuickCheckRemarks = "";                  //检测备注
			//loQuickCheckItemJc.QuickChecker = poCheckData.CheckUser;                   //检测人姓名    Y
			//loQuickCheckItemJc.QuickReChecker = poCheckData.CheckUser;                 //复核人员姓名  Y
			//loQuickCheckItemJc.JCManufactor = poCheckData.MachineMaker;                       //检测设备厂家名
			//loQuickCheckItemJc.JCModel = poCheckData.MachineID;                            //检测设备型号
			//loQuickCheckItemJc.JCSN = "";
			return loQuickCheckItemJc;
		}
		private List<QuickCheckItemJC> DataTrans(List<CheckData> poCheckDatas)
		{
			List<QuickCheckItemJC> loRetval = new List<QuickCheckItemJC>();
			foreach (var d in poCheckDatas)
			{
				loRetval.Add(DataTrans(d));
			}
			Cache.Clear();
			return loRetval;
		}

		#region 获取信息
		private string GetMarketSellerLicenseNo(string pcMarketId,string pcStallName)
		{
			string key = ("MarketSellerInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketSellersInfo(pcMarketId, pcStallName, key);
			string lcRetval = loQuery?.LicenseNo ?? "";
			return lcRetval;
		}
		private string GetMarketSellerName(string pcMarketId, string pcStallName)
		{
			string key = ("MarketSellerInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketSellersInfo(pcMarketId, pcStallName, key);
			string lcRetval = loQuery?.Name ?? "";
			return lcRetval;
		}
		private string GetMarketSellerDabh(string pcMarketId, string pcStallName)
		{
			string key = ("MarketSellerInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketSellersInfo(pcMarketId, pcStallName, key);
			string lcRetval = loQuery?.IdCard ?? "";
			return lcRetval;
		}
		private string GetMarketLicenseNo(string pcMarketId)
		{
			string key = ("MarketInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketsInfo(pcMarketId, key);
			string lcRetval = loQuery?.LicenseNo ?? "";
			return lcRetval;
		}
	    private string GetMarketLicenseName(string pcMarketId)
		{
			string key = ("MarketInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketsInfo(pcMarketId, key);
			string lcRetval = loQuery?.LicenseName ?? "";
			return lcRetval;
		}
		private string GetMarketCardIndex(string pcMarketId)
		{
			string key = ("MarketInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketsInfo(pcMarketId, key);
			string lcRetval = loQuery?.CardIndex ?? "";
			return lcRetval;
		}
		private string GetMarketName(string pcMarketId)
		{
			string key = ("MarketInfo_" + pcMarketId).Trim();
			var loQuery = QueryMarketsInfo(pcMarketId, key);
			string lcRetval = loQuery?.Names ?? "";
			return lcRetval;
		}
		private string GetSubItemCode(string pcSampleId)
		{
			string key = ("CategoryInfo_" + pcSampleId).Trim();
			var loQuery = QueryCategoryInfo(pcSampleId, key);
			string lcRetval = loQuery?.LocalNo ?? "";
			return lcRetval;
		}
		private string GetCheckItemCode(string pcCheckProjectTypeId)
		{
			string key = ("CheckItemMap_" + pcCheckProjectTypeId).Trim();
			var loQuery = QueryCheckCodeInfo(pcCheckProjectTypeId, key);
			string lcRetval = loQuery?.szCheckItemTypeGreatCode ?? "";
			return lcRetval;
		}
		private string GetCheckSubItemCode(string pcCheckProjectTypeId)
		{
			string key = ("CheckItemMap_" + pcCheckProjectTypeId).Trim();
			var loQuery = QueryCheckCodeInfo(pcCheckProjectTypeId, key);
			string lcRetval = loQuery?.szCheckItemTypeLittleCode ?? "";
			return lcRetval;
		}

		#endregion

		#region 查询数据库信息
		private MarketSellers_info QueryMarketSellersInfo(string pcMarketId,string pcStallName, string key)
		{
			MarketSellers_info loInfo = null;
			if (Cache.ContainsKey(key))
			{
				loInfo = (MarketSellers_info)Cache[key];
			}
			else
			{
				string lcSql = "SELECT   m.SellerID, m.MarketID, m.Name, m.LicenseNo, m.LicenseName, m.CardIndex, st.IsLock FROM dbo.SellerHasStall AS sh LEFT OUTER JOIN dbo.MarketSellers AS m ON sh.SellerID = m.SellerID RIGHT OUTER JOIN dbo.Stalls AS st ON sh.StallID = st.StallID WHERE(m.IsLock = N'N') AND(st.IsLock = N'N') AND(st.MarketID ='" + pcMarketId + "') AND(st.StallNo='"+ pcStallName + "')";
				DBConnSql loDbConn = new DBConnSql();
				try
				{
					if (loDbConn.Open())
					{
						rsQuery loQuery = loDbConn.OpenQuery(lcSql);
						if (loQuery != null && loQuery.IsOpened && loQuery.RecCount == 1)
						{
							loInfo = new MarketSellers_info()
							{
								Name = loQuery.GetString("Name"),
								LicenseNo = loQuery.GetString("LicenseNo"),
								LicenseName = loQuery.GetString("LicenseName"),
								CardIndex = loQuery.GetString("CardIndex"),
								IdCard = loQuery.GetString("IdCard")
							};
							Cache.Add(key, loInfo);
						}
					}
				}
				catch (Exception err)
				{
					this.LogError(err);
				}
				finally
				{
					loDbConn.Close();
				}
			}
			return loInfo;
		}
		private Markets_info QueryMarketsInfo(string pcMarketId, string key)
		{
			Markets_info loInfo = null;
			if (Cache.ContainsKey(key))
			{
				loInfo = (Markets_info)Cache[key];
			}
			else
			{
				string lcSql = "SELECT [Names],[MarketTypeID],[LicenseNo],[LicenseName],[CardIndex] FROM " + Tn.Markets + " WHERE MarketID='" + pcMarketId + "'";
				DBConnSql loDbConn = new DBConnSql();
				try
				{
					if (loDbConn.Open())
					{
						rsQuery loQuery = loDbConn.OpenQuery(lcSql);
						if (loQuery != null && loQuery.IsOpened && loQuery.RecCount == 1)
						{
							loInfo = new Markets_info()
							{
								Names = loQuery.GetString("Names"),
								LicenseNo = loQuery.GetString("LicenseNo"),
								LicenseName = loQuery.GetString("LicenseName"),
								CardIndex = loQuery.GetString("CardIndex"),
								MarketTypeID = loQuery.GetString("MarketTypeID")
							};
							Cache.Add(key, loInfo);
						}
					}
				}
				catch (Exception err)
				{
					this.LogError(err);
				}
				finally
				{
					loDbConn.Close();
				}
			}
			return loInfo;
		}
		private VarietyCategory_info QueryCategoryInfo(string pcCategoryNo, string key)
		{
			VarietyCategory_info loInfo = null;
			if (Cache.ContainsKey(key))
			{
				loInfo = (VarietyCategory_info)Cache[key];
			}
			else
			{
				string lcSql = "SELECT [VarietyCategoryName],[LocalNo],[Path] FROM " + Tn.VarietyCategory + " WHERE [VarietyCategoryNo]='" + pcCategoryNo + "'";
				DBConnSql loDbConn = new DBConnSql();
				try
				{
					if (loDbConn.Open())
					{
						rsQuery loQuery = loDbConn.OpenQuery(lcSql);
						if (loQuery != null && loQuery.IsOpened && loQuery.RecCount == 1)
						{
							loInfo = new VarietyCategory_info()
							{
								VarietyCategoryNo = pcCategoryNo,
								VarietyCategoryName = loQuery.GetString("VarietyCategoryName"),
								LocalNo = loQuery.GetString("LocalNo"),
								//Path = loQuery.GetString("Path")
							};
							if (string.IsNullOrEmpty(loInfo.LocalNo))
							{
								loInfo.LocalNo = QueryCategoryLocalNo(loQuery.GetString("Path"));
							}
							Cache.Add(key, loInfo);
						}
					}
				}
				catch (Exception err)
				{
					this.LogError(err);
				}
				finally
				{
					loDbConn.Close();
				}
			}
			return loInfo;
		}
		/// <summary>
		/// 逐级获取父节点 LocalNo
		/// </summary>
		/// <param name="pcPath"></param>
		/// <returns></returns>
		private string QueryCategoryLocalNo(string pcPath)
		{
			string lcRetval = "";
			DBConnSql loDbConn = new DBConnSql();
			try
			{
				if (loDbConn.Open())
					return lcRetval;
				string[] laPaths = pcPath?.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
				if (laPaths == null || laPaths.Length <= 0)
					return lcRetval;
				for (int i = laPaths.Length - 2; i >= 0; i--)
				{
					string lcSql = "SELECT [LocalNo] FROM " + Tn.VarietyCategory + " WHERE [VarietyCategoryNo]='" + laPaths[i] + "'";
					rsQuery loQuery = loDbConn.OpenQuery(lcSql);
					if (loQuery != null && loQuery.IsOpened && loQuery.RecCount == 1)
					{
						lcRetval = loQuery.GetString("LocalNo");
						if (!string.IsNullOrEmpty(lcRetval))
							break;
					}
				}
			}
			catch (Exception err)
			{
				this.LogError(err);
			}
			finally
			{
				loDbConn.Close();
			}

			return lcRetval;
		}

		private CheckProjectTypeMapping_info QueryCheckCodeInfo(string pcCheckProjectId, string key)
		{
			CheckProjectTypeMapping_info loInfo = null;
			if (Cache.ContainsKey(key))
			{
				loInfo = (CheckProjectTypeMapping_info)Cache[key];
			}
			else
			{
				string lcSql = "SELECT m.CheckItemId , m.szCheckItemTypeLittleCode, m.szCheckItemTypeGreatCode FROM dbo.CheckProjectType AS p LEFT OUTER JOIN  dbo.CheckProjectTypeMapping AS m ON p.CheckItemId = m.CheckItemId WHERE CheckProjectTypeID='" + pcCheckProjectId + "'";
				DBConnSql loDbConn = new DBConnSql();
			    
                try
				{
					if (loDbConn.Open())
					{
						rsQuery loQuery = loDbConn.OpenQuery(lcSql);
						if (loQuery != null && loQuery.IsOpened && loQuery.RecCount == 1)
						{
							loInfo = new CheckProjectTypeMapping_info()
							{
								CheckItemId = loQuery.GetString("CheckItemId"),
								szCheckItemTypeLittleCode = loQuery.GetString("szCheckItemTypeLittleCode"),
								szCheckItemTypeGreatCode = loQuery.GetString("szCheckItemTypeGreatCode")
							};
							Cache.Add(key, loInfo);
						}
					}
				}
				catch (Exception err)
				{
					this.LogError(err);
				}
				finally
				{
					loDbConn.Close();
				}
			}
			return loInfo;
		}
		#endregion

		public static Dictionary<string, object> Cache = new Dictionary<string, object>();

		#endregion

		public string UpdateMarkets()
		{
			string lcSql = "";
			List<YtMarketInfo> markets = GetMarketInfos();
			foreach (var m in markets)
			{
				lcSql += "UPDATE [dbo].[Markets] SET [LicenseNo]='" + m.LicenseNo + "',[LicenseName]='" + m.MarketName +
				         "',[CardIndex]='" + m.CardIndex + "' WHERE ([Names] LIKE '%" + m.MarketName +
				         "%' OR [Names] LIKE '%" + m.Abbreviation +
				         "%') AND ([LicenseNo] IS NULL OR  [LicenseNo]='')\r\n";
			}
			ExcuteSqlTran(lcSql, out string msg);
			return msg;
		}
		public string UpdateMarketSellers()
		{
			string lcSql = "";
			List<YtMarketInfo> markets = GetMarketInfos();
			foreach (var m in markets)
			{
				lcSql += "UPDATE [dbo].[Markets] SET [LicenseNo]='" + m.LicenseNo + "',[LicenseName]='" + m.MarketName +
				         "',[CardIndex]='" + m.CardIndex + "' WHERE ([Names] LIKE '%" + m.MarketName +
				         "%' OR[Names] LIKE '%" + m.Abbreviation +
				         "%') AND ([LicenseNo] IS NOT NULL OR  [LicenseNo]!='')\r\n";
			}
			ExcuteSqlTran(lcSql, out string msg);
			return msg;
		}
		public List<YtMarketInfo> GetMarketInfos()
		{
			List<YtMarketInfo> markets = null;
			string lcRetval = GetAreaMarket();
			try
			{
				markets = JsonConvert.DeserializeObject<List<YtMarketInfo>>(lcRetval);
			}
			catch (Exception e)
			{
				this.LogError(e);
			}

			return markets;
		}
		public string GetAreaMarket()
		{
			GetYtApiName(out string name,out string password);
			string xmlStr = "<webService><request><name>" + name + "</name><password>" + password +
			                "</password></request></webService>";
			sDataInfraceSoapClient loYtInterface = new sDataInfraceSoapClient();
			this.LogInfo("【YT-GetAreaMarket-Start:" + xmlStr);
			string lcRetval = loYtInterface.GetAreaMarket(xmlStr);
			this.LogInfo("YT-GetAreaMarket-END】:" + lcRetval);
			return lcRetval;
		}

		public List<YtMarketSellerInfo> GetMarketSellerInfos()
		{
			List<YtMarketSellerInfo> marketSellers = null;
			string lcRetval = GetAreaSignContact();
			try
			{
				marketSellers = JsonConvert.DeserializeObject<List<YtMarketSellerInfo>>(lcRetval);
			}
			catch (Exception e)
			{
				this.LogError(e);
			}

			return marketSellers;
		}

		
		public string GetAreaSignContact()
		{
			string licenseNo = GetYtApiName(out string name, out string password);
			string xmlStr = "<webService><head><name>" + name + "</name><password>" + password +
			                "</password></head><request><LicenseNo>" + licenseNo +
			                "</LicenseNo></request></webService>";
			sDataInfraceSoapClient loYtInterface = new sDataInfraceSoapClient();
			this.LogInfo("【YT-GetAreaSignContact-Start:" + xmlStr);
			string lcRetval = loYtInterface.GetAreaMarket(xmlStr);
			this.LogInfo("YT-GetAreaSignContact-END】:" + lcRetval);
			return lcRetval;
		}

		/// <summary>
		/// 执行Sql语句
		/// </summary>
		/// <param name="pcSql"></param>
		/// <param name="msg"></param>
		/// <returns></returns>
		public bool ExcuteSqlTran(string pcSql, out string msg)
		{

			msg = "System Error![ DB ]";//"Sql Excute Failed";
			bool lbRetval = false;
			//return false;
			if (string.IsNullOrEmpty(pcSql))
			{
				msg = "Sql is empty.";
				return false;
			}
			DBConnSql dbConn = new DBConnSql();
			try
			{
				dbConn.Open();
				if (dbConn.ExcuteSqlTran(pcSql))
				{
					msg = "";
					lbRetval = true;
				}
			}
			catch (Exception e)
			{
				msg = e.Message;
				this.LogError(e);
			}
			finally
			{
				dbConn.Close();
			}
			return lbRetval;
		}

		private string GetYtApiName(out string name ,out string password)
		{
			string licenseNo = "";
			name = "";
			password = "";
			try
			{
				var xDoc = XDocument.Load(AppDomain.CurrentDomain.BaseDirectory + "Resources/AppInfos.xml");
				var app = (from r in xDoc.Descendants("app")
					where r.Attribute("id")?.Value.ToLower() == "yt"
					select r).FirstOrDefault();
				if (app != null)
				{
					name = app.Attribute("user")?.Value;
					password = app.Attribute("secret")?.Value;
					licenseNo = app.Attribute("licenseNo")?.Value;
				}
			}
			catch (Exception e)
			{
				this.LogError(e);
			}
			return licenseNo;
		}
	}


}