/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  [OrgID]
      ,[Name]
      ,[Amount]
      ,[CerNo]
      ,[CardNo]
      ,[SubjectMatter]
      ,[PayTime]
      ,[BankName]
  FROM [CountInsurance].[dbo].[TempRecord1023]

  SELECT * FROM dbo.SysOrganization WHERE ShortName='惠济'

    SELECT * FROM dbo.SysOrganization WHERE ShortName LIKE '%上街营销部%'


	SELECT b.ShortName,a.* FROM [TempRecord1023] a 
	LEFT JOIN dbo.SysOrganization b ON a.OrgID=b.ShortName


	SELECT a.OrgID FROM [TempRecord1023] a
	GROUP BY OrgID

	-------------更新临时表的机构简称
	UPDATE [TempRecord1023] SET OrgID='盛大高新' WHERE OrgID='高新区'
    UPDATE [TempRecord1023] SET OrgID='盛大二七' WHERE OrgID='二七'
    UPDATE [TempRecord1023] SET OrgID='保尔新郑' WHERE OrgID='新郑'

    UPDATE [TempRecord1023] SET OrgID='义和荥阳' WHERE OrgID='荥阳'
	UPDATE [TempRecord1023] SET OrgID='义和中原（老黄河）' WHERE OrgID='中原'
	UPDATE [TempRecord1023] SET OrgID='人保惠济' WHERE OrgID='惠济'

	 UPDATE [TempRecord1023] SET OrgID='盛大管城' WHERE OrgID='管城'
	 UPDATE [TempRecord1023] SET OrgID='年安金水（老郑汴）' WHERE OrgID='金水'

	UPDATE [TempRecord1023] SET OrgID='上街营销部' WHERE OrgID='上街区营销部'

	SELECT v.*,s.GID FROM 

	(SELECT a.*,b.ID,c.id AS payID FROM [TempRecord1023] a LEFT JOIN dbo.SysOrganization b ON a.OrgiD=b.ShortName LEFT JOIN dbo.BCdZhongXinBankPayConfig c ON a.BankName=c.merchantName
	) v 
	LEFT JOIN   dbo.BOCommissionExtractRecord s 
	ON   v.ID=s.OrgID AND v.amount=s.Amount AND v.subjectmatter=s.SubjectMatter AND s.Status=1 AND s.ApprovalStatus='02' AND s.PayChannel=v.payID AND s.IsShow=1
	     AND v.Cardno=s.BankCardNo
	WHERE v.OrgID IS NOT NULL
	ORDER BY s.GID
	
	 






	 SELECT * FROM TempRecord1023 WHERE policyno IS NOT NULL
	  SELECT * FROM TempRecord1023 WHERE policyno IS  NULL


	  SELECT a.*,b.Amount,b.BankUserName,b.PaymentTime,b.SubjectMatter FROM TempRecord1023 a LEFT JOIN dbo.BOCommissionExtractRecord b ON a.policyno=b.GID
	  WHERE b.GID IS NOT NULL


UPDATE s1  SET RecordID=exgid  FROM [TempRecord1023] s1 LEFT JOIN 


(
	 SELECT 
	
	   t.[OrgID]
      ,t.[Name]
      ,t.[Amount]
      ,t.[CerNo]
      ,t.[CardNo]
      ,t.[SubjectMatter]
      ,t.[PayTime]
      ,t.[BankName],COUNT(t.GID) AS recordgid
	  ,MAX(t.id) AS rowid
	  ,MAX(t.GID) AS exgid
	  ,MAX(t.repolicyno) AS policyno FROM(

	SELECT v.*,s.PolicyNo AS repolicyno,s.GID FROM 

	(SELECT a.*,b.ID AS orid,c.id AS payID FROM [TempRecord1023] a LEFT JOIN dbo.SysOrganization b ON a.OrgiD=b.ShortName LEFT JOIN dbo.BCdZhongXinBankPayConfig c ON a.BankName=c.merchantName
	) v 
	LEFT JOIN   dbo.BOCommissionExtractRecord s 
	ON   v.orid=s.OrgID AND v.amount=s.Amount AND v.subjectmatter=s.SubjectMatter AND s.Status=1 AND s.ApprovalStatus='02' AND s.PayChannel=v.payID AND s.IsShow=1
	     AND v.Cardno=s.BankCardNo AND FORMAT(v.Paytime,'yyyy-MM-dd') =FORMAT(s.PaymentTime,'yyyy-MM-dd')
	WHERE v.OrgID IS NOT NULL
	) t GROUP BY t.[OrgID]
      ,t.[Name]
      ,t.[Amount]
      ,t.[CerNo]
      ,t.[CardNo]
      ,t.[SubjectMatter]
      ,t.[PayTime]
      ,t.[BankName]
	  HAVING COUNT(t.GID)=1 
	  --ORDER BY t.Name
	  ) s2  ON s1.id=s2.rowid



	  SELECT QueryPaymentTime,ApprovalStatus,IsSummary,QueryPaymentTime,SubjectMatter,IsShow,* FROM dbo.BOCommissionExtractRecord WHERE BankUserName='李亚红' AND Amount=3280.91



	  	  SELECT QueryPaymentTime,PaymentTime,ApprovalStatus,IsSummary,QueryPaymentTime,SubjectMatter,IsShow,* FROM dbo.BOCommissionExtractRecord WHERE BankUserName='程鹏' AND Amount=12114.02



	  SELECT * FROM dbo.BCdZhongXinBankPayConfig WHERE id='252'

	  SELECT * FROM dbo.SysOrganization WHERE ID='G4000096'

	  SELECT * FROM [TempRecord1023] WHERE id='1302'

	  ALTER TABLE [TempRecord1023] ADD RecordID NVARCHAR(32)


	  
   SELECT * FROM dbo.BOCommissionExtractRecordDetails WHERE ExtractID='690dd12f9d7240ab87c27ea946ec5ffd'

   SELECT * FROM dbo.BOCommissionExtractRecord WHERE  GID='690dd12f9d7240ab87c27ea946ec5ffd'


   ----------统一处理脚本-----------
   UPDATE  a SET a.PaymentDocumentNo='因业务需要退款清单2024-10-24',a.Status=3,a.IsTreat=1   FROM dbo.BOCommissionExtractRecord a LEFT JOIN dbo.TempRecord1023 b ON a.GID=b.RecordID
   WHERE b.RecordID IS NOT NULL