supplier_system/src/dashoo.cn/backend/api/business/tmpzcgf/tmpzcgfSession.go

package tmpzcgf

import (
	"dashoo.cn/backend/api/business/oilsupplier/suppliercert"
	"dashoo.cn/backend/api/business/oilsupplier/suppliercertappend"
	. "dashoo.cn/backend/api/mydb"
	"fmt"
	"github.com/go-xorm/xorm"
	"math"
	"strconv"
	"strings"
)

/** type TmpzcgtSession struct {
	MySessionBase
}

func GetTmpzcgtService(session *xorm.Session) *TmpzcgtSession {
	s := new(TmpzcgtSession)
	s.Session = session
	return s
} */

type TmpzcgtSession struct {
	MyServiceBase
}

func GetTmpzcgtService(xormEngine *xorm.Engine) *TmpzcgtSession {
	s := new(TmpzcgtSession)
	s.DBE = xormEngine
	return s
}

func (s *TmpzcgtSession) InsertSupplier() error {
	sql := `INSERT INTO OilSupplier (
	SupplierName,
	Grade,
	OperType,
	Country,
	MaunAgent,
	ConstructTeam,
	CommercialNo,
	OrganCode,
	CountryTaxNo,
	LocalTaxNo,
	Address,
	ZipCode,
	QualitySystemCert,
	ProductQualityCert,
	MaunLicense,
	QualifCert,
	QualifCertLevel,
	SafetyLicense,
	TechServiceLic,
	TJInNotify,
	SpecIndustryCert,
	LegalPerson,
	CategoryCode,
	CategoryName,
	RegCapital,
	Currency,
	ContactName,
	CompanyType,
	DepositBank,
	BankAccount,
	EMail,
	BankCreditRating,
	Mobile,
	Telphone,
	Fax,
	CompanyTel,
	QQ,
	CompanyUrl,
	Remark,
	CreateBy
) SELECT
	企业名称 AS SupplierName,
	级别 AS Grade,
	经营方式 AS OperType,
	国家 AS Country,
	所代理制造商名称 AS MaunAgent,
	施工队伍名称 AS ConstructTeam,
	TRIM(工商注册号) AS CommercialNo,
	组织机构代码 AS OrganCode,
	税务登记证国税编号 AS CountryTaxNo,
	税务登记证地税编号 AS LocalTaxNo,
	单位地址 AS Address,
	邮编 AS ZipCode,
	质量管理体系认证情况及认证机构 AS QualitySystemCert,
	产品质量认证情况及认证机构 AS ProductQualityCert,
	生产制造许可证获证情况及编号 AS MaunLicense,
	企业资质证书编号 AS QualifCert,
	企业资质证书级别 AS QualifCertLevel,
	安全生产许可证 AS SafetyLicense,
	技术服务类准入许可证 AS TechServiceLic,
	外地企业进津备案通知书 AS TJInNotify,
	行业特殊要求的认证证书 AS SpecIndustryCert,
	法定代表人姓名 AS LegalPerson,
	行业类别代码 AS CategoryCode,
	行业类别名称 AS CategoryName,
	注册资本 AS RegCapital,
	币种 AS Currency,
	联系人姓名 AS ContactName,
	公司类型 AS CompanyType,
	开户银行 AS DepositBank,
	银行账号 AS BankAccount,
	电子邮箱 AS EMail,
	银行信用等级 AS BankCreditRating,
	移动电话 AS Mobile,
	固定电话 AS Telphone,
	传真 AS Fax,
	公司电话 AS CompanyTel,
	QQ号码 AS QQ,
	公司网址 AS CompanyUrl,
	备注 AS Remark,
	'导入' As CreateBy
FROM 
	T_资格预审表 WHERE ID IN (SELECT MAX(ID) FROM T_资格预审表 GROUP BY 企业名称);` //WHERE 准入标识 != '取消'
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) InsertSupplierCert() error {
	s.DBE.Query("SET unique_checks=0;")
	sql := "INSERT INTO OilSupplierCert (SupplierId,OutsideFlog, `Status` , Step, WorkerTotal, AccessCardNo,SupplierTypeName,InFlag,EffectStartTime,EffectEndTime,ApplyTime,Remark,CreateBy,GfID) " +
		"SELECT -1 As SupplierId,企业名称 AS OutsideFlog,'8' As `Status`, 3 as Step, 1 as WorkerTotal, 准入证号 As AccessCardNo,准入类别 AS SupplierTypeName,准入标识 AS InFlag, STR_TO_DATE(IF(有效期起='', NULL, 有效期起),'%Y年%m月%d日') AS EffectStartTime, STR_TO_DATE(IF(有效期止='', NULL, 有效期止),'%Y年%m月%d日') AS EffectEndTime,(SELECT STR_TO_DATE(IF(MAX(年审日期)='', NULL, MAX(年审日期)),'%Y-%m-%d') from T_年审 b WHERE b.企业名称 = a.企业名称 AND b.状态标识='办理完毕') AS ApplyTime,备注 AS Remark,'导入' As CreateBy,ID " +
		"FROM T_资格预审表 a ;" //WHERE 准入标识 != '取消'
	// s.DBE.Query("SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'")
	_, err := s.DBE.Query(sql)
	s.DBE.Query("SET unique_checks=1;")
	return err
}

func (s *TmpzcgtSession) UpdateSupplierCert() error {
	sql := "UPDATE `OilSupplierCert` b SET b.SupplierId=IFNULL((SELECT a.Id FROM OilSupplier a WHERE a.SupplierName = b.OutsideFlog AND a.Id IS NOT NULL  limit 1), 0)"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) UpdateSupplierTypeCodeAndInFlag() error {
	sql1 := "update `OilSupplierCert` set SupplierTypeCode = '01' where SupplierTypeName='物资类'"
	_, err := s.DBE.Query(sql1)
	if err != nil {
		return err
	}
	sql2 := "UPDATE `OilSupplierCert` SET SupplierTypeCode = '02' WHERE SupplierTypeName='基建类'"
	_, err = s.DBE.Query(sql2)
	if err != nil {
		return err
	}
	sql3 := "UPDATE `OilSupplierCert` SET SupplierTypeCode = '03' WHERE SupplierTypeName='服务类'"
	_, err = s.DBE.Query(sql3)
	if err != nil {
		return err
	}
	sql31 := "UPDATE `OilSupplierCert` SET SupplierTypeCode = '03', SupplierTypeName='服务类' WHERE SupplierTypeName='技术服务类'"
	_, err = s.DBE.Query(sql31)
	if err != nil {
		return err
	}
	sql4 := "UPDATE `OilSupplierCert` SET InFlag = '1' WHERE InFlag='准入'"
	_, err = s.DBE.Query(sql4)
	if err != nil {
		return err
	}
	sql5 := "UPDATE `OilSupplierCert` SET InFlag = '2' WHERE InFlag='暂停'"
	_, err = s.DBE.Query(sql5)
	if err != nil {
		return err
	}
	sql6 := "UPDATE `OilSupplierCert` SET InFlag = '3' WHERE InFlag='取消'"
	_, err = s.DBE.Query(sql6)
	return err

}

func (s *TmpzcgtSession) InsterAnnual() error {

	sql := "INSERT INTO OilAnnualAudit (Status,SupplierTypeName, SupplierName, CreateOn, CreateBy, RecUnitId, ApplyTime, AccessCardNo, OldId) " +
		"SELECT '11' as Status ,(CASE 准入类别 WHEN '物资类' THEN '01' WHEN '基建类' THEN '02' WHEN '服务类' THEN '03' WHEN '技术服务类' THEN '03' END ) SupplierTypeName, " +
		"企业名称 AS SupplierName, STR_TO_DATE(IF(申请日期='', NULL, 申请日期),'%Y-%m-%d') AS CreateOn, 录入员 AS CreateBy, 推荐单位编码 AS RecUnitId, STR_TO_DATE(IF(年审日期='', NULL, 年审日期),'%Y-%m-%d') AS ApplyTime, 准入证号 AS AccessCardNo, id AS oldId " +
		"FROM T_年审"

	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) InsertOilPaymentInfo() error {
	sql := "INSERT INTO `OilPaymentInfo` ( " +
		"ID_GF," +
		"SupplierName," +
		"PayDate," +
		"Amount," +
		"IsPay," +
		" PayType," +
		"Remark," +
		"PayMode," +
		"CreateBy" +
		")" +
		"SELECT ID_GF, 企业名称 AS SupplierName ,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(新准入='', 0, 新准入)  AS Amount, '2' AS IsPay,'1' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 新准入 IS NOT NULL and 新准入<>''" +
		" UNION" +
		" SELECT ID_GF, 企业名称 AS SupplierName ,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(换证='', 0, 换证) AS Amount , '2' AS IsPay, '4' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 换证 IS NOT NULL and 换证<>''" +
		" UNION" +
		" SELECT ID_GF, 企业名称 AS SupplierName ,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(增项='', 0, 增项) AS Amount , '2' AS IsPay, '3' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 增项 IS NOT NULL and 增项<>''" +
		" UNION" +
		" SELECT ID_GF, 企业名称 AS SupplierName,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(年审='', 0, 年审) AS Amount , '2' AS IsPay, '2' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 年审 IS NOT NULL and 年审<>''" +
		" UNION" +
		" SELECT ID_GF, 企业名称 AS SupplierName,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(罚款='', 0, 罚款) AS Amount , '2' AS IsPay, '5' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 罚款 IS NOT NULL and 罚款<>''" +
		" UNION" +
		" SELECT ID_GF, 企业名称 AS SupplierName,IF(交费日期='', '1970-01-01', 交费日期) AS PayDate, IF(其它='', 0, 其它) AS Amount , '2' AS IsPay, '6' AS PayType , 备注 AS Remark, 交款方式 AS PayMode,收款人 AS CreateBy FROM T_交费记录 WHERE 其它 IS NOT NULL and 其它<>''"

	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) UpdateOilPaymentInfo() error {
	// sql := "UPDATE OilPaymentInfo b SET b.SupplierId= (SELECT  DISTINCT(a.SupplierId) FROM `OilSupplierCert` a WHERE  a.GfId = b.ID_GF )"
	sql := "UPDATE OilPaymentInfo b left join `OilSupplierCert` a ON b.ID_GF=a.GfId SET b.SupplierId=a.SupplierId,b.SupplierCertId=a.Id,b.IsInvoice=1"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) InsterBadRecord() error {
	sql := "INSERT INTO BadRecord (" +
		"Bak1," +
		"CreateOn, " +
		"Record," +
		"CompanyName, " +
		"CompanyCode, " +
		"CategoryName, " +
		"CategoryCode " +
		") " +
		"SELECT " +
		"gfid AS Bak1 , " +
		"STR_TO_DATE(IF(日期='', NULL, 日期),IF(LENGTH(日期)>8, '%Y-%m-%d', '%Y%m%d')) AS CreateOn, " +
		"内容 AS Record, " +
		"'' AS CompanyName, " +
		"'' AS CompanyCode, " +
		"'' AS CategoryName, " +
		"'' AS CategoryCode " +
		"FROM T_不良行为记录 WHERE 内容 IS NOT NULL"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) UpdateBadRecord() error {
	/** sql := "UPDATE BadRecord b SET " +
		"b.CompanyCode = (SELECT DISTINCT(a.SupplierId) FROM `OilSupplierCert` a WHERE  a.GfId = b.Bak1 AND a.SupplierId IS NOT NULL)," +
		"b.CompanyName = (SELECT DISTINCT(a.OutsideFlog) FROM `OilSupplierCert` a WHERE  a.GfId = b.Bak1 AND a.OutsideFlog IS NOT NULL) " */

	sql := "UPDATE BadRecord b left join `OilSupplierCert` a ON a.GfId = b.Bak1 SET " +
		"b.CompanyCode = a.SupplierId," +
		"b.CompanyName = a.OutsideFlog " +
		" where a.OutsideFlog IS NOT NULL and a.SupplierId IS NOT NULL"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) UpdateOilAnnualAudit() error {
	/** sql := "UPDATE OilAnnualAudit b SET b.SupplierId = (SELECT a.SupplierId FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeName )," +
		"b.CerId = (SELECT a.Id FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeName )," +
		"b.AccessCardNo = (SELECT a.AccessCardNo FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeName )" */
	sql := "UPDATE OilAnnualAudit b left join OilSupplierCert a ON (a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeName) SET b.SupplierId = a.SupplierId," +
		"b.CerId = a.Id," +
		"b.AccessCardNo = a.AccessCardNo"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) InsertCertSub() error {

	countSql := "SELECT count(*) FROM T_资格预审表_JRMX t "

	countStr, err := s.DBE.Query(countSql)
	var total int64
	if len(countStr) > 0 {
		results := countStr[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			break
		}
		n := math.Ceil(float64(total) / 50000)

		/*s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_typecode;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_code;")
		s.DBE.Query("SET autocommit=0;")*/
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_code ;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_supplierid ;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_suppliercertid ;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_name ;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` drop INDEX index_supplierappendid ;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` DISABLE KEYS;")
		s.DBE.Query("SET unique_checks=0;")
		fmt.Println("循环总数-"+strconv.Itoa(int(n)))
		for i:=0; i < int(n); i++ {
			start := strconv.Itoa(i * 50000)
			end := strconv.Itoa(50000)
			sql := "INSERT INTO `OilSupplierCertSub` (" +
				"OldId," +
				"AppendStatus," +
				"`Type`," +
				"CreateBy," +
				"Remark," +
				"IsManufacturer," +
				"GoodsLevel," +
				"`Code`," +
				"`Name`" +
				")  " +
				` SELECT t.id AS OldId ,
					'1' AS AppendStatus,
					'1' AS Type,
					'导入1' AS CreateBy,
					t.备注 As Remark,
					t.制造产品 AS IsManufacturer,
					t.物资级别 AS GoodsLevel,
					t.物资编码 AS code,
					t.产品名称 AS name
					FROM T_资格预审表_JRMX t
					where t.auto_id >= (SELECT auto_id FROM T_资格预审表_JRMX ORDER BY auto_id LIMIT ` + start + ", 1)" +
				" limit " + end
				/*
				ORDER BY t.auto_id  +
				" limit " + start + "," +  end*/
			_, err = s.DBE.Query(sql)
			/*time.Sleep(time.Duration(500)*time.Millisecond)
			s.DBE.Query("COMMIT;")*/
			fmt.Println("循环次数-"+strconv.Itoa(i))
			//time.Sleep(time.Duration(500)*time.Millisecond)
		}
		// s.DBE.Query("SET autocommit=1;")
		s.DBE.Query("SET unique_checks=1;")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ENABLE KEYS;")
		/*
		//s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_typecode ( `SupplierTypeCode` );")
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_code ( `Code` );")*/
	}
	return err
}

func (s *TmpzcgtSession) UpdateCertSub() error {
	/*sql := "UPDATE OilSupplierCertSub b SET b.SupplierId = IFNULL((SELECT a.SupplierId FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeCode AND a.SupplierId IS NOT NULL limit 1 ), 0)," +
		"b.SupplierCertId = IFNULL((SELECT a.Id FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeCode limit 1), 0) " +
		" where b.Type = '1' and b.CreateBy='导入1'"*/
	var certList [] suppliercert.OilSupplierCert
	//svc.GetEntitysByWhere(OilSupplierCertAppendSubName, where, &list)
	s.GetEntitysByWhere("OilSupplierCert", "", &certList)

	var err error

	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_oldid ( `OldId` );")
	s.DBE.Query("SET autocommit=0;")
	for _, cert := range certList {
		sql := " UPDATE OilSupplierCertSub b SET b.SupplierId = '" + strconv.Itoa(cert.SupplierId) + "'," +
			" b.SupplierCertId='"+ strconv.Itoa(cert.Id) + "',"+
			" b.SupplierTypeCode='" + cert.SupplierTypeCode + "'" +
			" where b.OldId = '" + strconv.Itoa(cert.GfId) + "'"

		   // "and b.Type = '1' and b.CreateBy='导入1'"

		_, err = s.DBE.Query(sql)
		s.DBE.Query("COMMIT;")
	}
	s.DBE.Query("SET autocommit=1;")
	s.DBE.Query("alter table OilSupplierCertSub drop index index_oldid ;")
	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_code ( `Code` );")
	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_supplierid ( `SupplierId` );")
	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_suppliercertid ( `SupplierCertId` );")
	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_name ( `Name` );")
	return err
}

func (s *TmpzcgtSession) UpdateCertSubCaseWhen() error {
	/*sql := "UPDATE OilSupplierCertSub b SET b.SupplierId = IFNULL((SELECT a.SupplierId FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeCode AND a.SupplierId IS NOT NULL limit 1 ), 0)," +
	"b.SupplierCertId = IFNULL((SELECT a.Id FROM OilSupplierCert a WHERE a.GfId =b.OldId AND a.SupplierTypeCode = b.SupplierTypeCode limit 1), 0) " +
	" where b.Type = '1' and b.CreateBy='导入1'"*/

	//svc.GetEntitysByWhere(OilSupplierCertAppendSubName, where, &list)
	//s.GetEntitysByWhere("OilSupplierCert", "", &certList)
	//s.GetEntitysByOrderbyWhere ("OilSupplierCert", "", "Gfid ASC", &certList)

	countSql := "SELECT count(*) FROM OilSupplierCert "
	var err error

	countStr, err := s.DBE.Query(countSql)
	var total int64
	if len(countStr) > 0 {
		results := countStr[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			break
		}
		n := math.Ceil(float64(total) / 10)
		s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_oldid ( `OldId` );")
		// s.DBE.Query("SET autocommit=0;")
		for i:=0; i < int(n); i++ {
			when1 := ""
			when2 := ""
			when3 := ""
			oldId := 0
			var certList [] suppliercert.OilSupplierCert
			s.GetPagingEntitiesWithOrderBytbl("", int64(i - 1), 10, "Gfid", true, &certList, "1=1")
			for _, cert := range certList {
				when1 += " WHEN " + strconv.Itoa(cert.GfId) + " THEN " + strconv.Itoa(cert.SupplierId)
				when2 += " WHEN " + strconv.Itoa(cert.GfId) + " THEN " + strconv.Itoa(cert.Id)
				when3 += " WHEN " + strconv.Itoa(cert.GfId) + " THEN '" + cert.SupplierTypeCode + "'"
				oldId = cert.GfId
			}
			sql1 := " UPDATE OilSupplierCertSub b SET b.SupplierId = CASE b.OldId " + when1 + " END, " +
				" b.SupplierCertId = CASE b.OldId " + when2 + " END, " +
				" b.SupplierTypeCode = CASE b.OldId " + when3 + " END " +
				" where b.OldId <= " + strconv.Itoa(oldId) + " and b.SupplierId = 0 "
			_, err = s.DBE.Query(sql1)
			// s.DBE.Query("COMMIT;")
		}
		// s.DBE.Query("SET autocommit=1;")
		s.DBE.Query("alter table OilSupplierCertSub drop index index_oldid ;")
	}
	return err
}

func (s *TmpzcgtSession) UpdateCertSub01classId(where string) error {

	sql1 := "UPDATE OilSupplierCertSub b left join OilGoodsAptitudeClass a ON a.Code = b.Code SET b.SubClassId = a.Id " +
		"WHERE b.SupplierTypeCode = '01'" + where
	_, err := s.DBE.Query(sql1)
	return err
}

func (s *TmpzcgtSession) UpdateCertSub02classId(where string) error {
	sql2 := "UPDATE OilSupplierCertSub b left join OilBasisBuild a ON a.Code = b.Code SET b.SubClassId = a.Id " +
		"WHERE b.SupplierTypeCode = '02'" + where
	_, err := s.DBE.Query(sql2)
	return err

}

func (s *TmpzcgtSession) UpdateCertSub03classId(where string) error {
	/** sql3 := "UPDATE OilSupplierCertSub b SET b.SubClassId = (SELECT a.Id FROM OilBasisBuild a WHERE a.Code = b.Code ) " +
		"WHERE b.SupplierTypeCode = '03'" + where */
	sql3 := "UPDATE OilSupplierCertSub b left join OilTechnologyServiceClass a ON a.Code = b.Code SET b.SubClassId = a.Id " +
		"WHERE b.SupplierTypeCode = '03'" + where
	_, err := s.DBE.Query(sql3)
	return err
}

func (s *TmpzcgtSession) InsertOilSupplierCertAppend() error {
	//sql := " INSERT INTO OilSupplierCertAppend (OldId,CreateOn,AppendType,SupplierName,CreateBy,RecUnitId,ApplyDate) " +
	//"select Max(id) as OldId,申请日期 as CreateOn, (case 准入类别 when '物资类' then '01' when '基建类' then '02' when '服务类' then '03' WHEN '技术服务类' THEN '03' end) AppendType, "+
	//	"企业名称 as SupplierName, 录入员 as CreateBy, 推荐单位编码 as RecUnitId, 申请日期 as ApplyDate "+
	//	"from tmp_zengxiang "+
	//	"group by id,申请日期,企业名称,录入员,推荐单位编码,申请日期,准入类别"
	// countSql := "SELECT count(*) from (select a.申请日期 from tmp_zengxiang a WHERE 企业名称 != '' GROUP BY a.`申请日期`, a.`企业名称`, a.`准入类别`) b"
	sqlAlterRemark := "ALTER TABLE `OilSupplierCertAppend` CHANGE COLUMN `Remark` `Remark` MEDIUMTEXT NULL COMMENT '备注' COLLATE 'utf8_general_ci' AFTER `Status`;"
	_, err := s.DBE.Query(sqlAlterRemark)

	// s.DBE.Query("ALTER TABLE `T_增项` ADD INDEX index_import ( `申请日期`, `企业名称`, `准入类别` );")

	s.DBE.Query("SET GLOBAL group_concat_max_len=1024000;")

	/*countSql := "select count(distinct a.`申请日期`, a.`企业名称`, a.`准入类别`) from T_增项 a WHERE 企业名称 != ''"
	countStr, err := s.DBE.Query(countSql)
	var total int64
	if len(countStr) > 0 {
		results := countStr[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			break
		}
		n := math.Ceil(float64(total) / 2000)
		for i:=0; i <= int(n); i++ {
			start := strconv.Itoa(i * 2000)
			end := strconv.Itoa(2000)

		}
	}*/

	s.DBE.Query("SET unique_checks=0;")

	sql := "INSERT INTO OilSupplierCertAppend " +
		"(OldId,Status,CreateOn,SupplierName,AppendType,Remark,CreateBy,ApplyDate) " +
		"SELECT Max(id) as OldId, '11' as Status,STR_TO_DATE(IF( a.`申请日期`='', NULL,  a.`申请日期`),'%Y-%m-%d') AS CreateOn, a.`企业名称` AS SupplierName, (CASE 准入类别 WHEN '物资类' THEN '01' WHEN '基建类' THEN '02' WHEN '服务类' THEN '03' WHEN '技术服务类' THEN '03' END) AppendType, " +
		"group_concat(a.`物资编码`) as Remark, '导入' AS CreateBy, STR_TO_DATE(IF( a.`申请日期`='', NULL,  a.`申请日期`),'%Y-%m-%d') AS ApplyDate " +
		"FROM T_增项 a WHERE 企业名称 != '' GROUP BY a.`申请日期`, a.`企业名称`, a.`准入类别` "
	// " ORDER BY a.`申请日期`, a.`企业名称`, a.`准入类别` "
	_, err = s.DBE.Query(sql)

	// s.DBE.Query("COMMIT;")
	s.DBE.Query("SET unique_checks=1;")

	// s.DBE.Query("ALTER TABLE `T_增项` drop INDEX index_import;")

	return err
}

func (s *TmpzcgtSession) InsertOilInfoChange() error {
	s.DBE.Query("SET unique_checks=0;")
	// s.DBE.Query("SET autocommit=0;")
	sql := "INSERT INTO OilInfoChange (Status,AccessCardNo,SupplierName,OldSupplierName,SupplierTypeName,SupplierTypeCode,OilCertificateNo,Grade,OperType,Country,MaunAgent,ConstructTeam,CommercialNo,OrganCode,CountryTaxNo,LocalTaxNo, " +
		"Address,ZipCode,QualitySystemCert,ProductQualityCert,MaunLicense,QualifCert,QualifCertLevel,SafetyLicense,TechServiceLic,TJInNotify, " +
		"SpecIndustryCert,LegalPerson,CategoryCode,CategoryName,RegCapital,Currency,ContactName,CompanyType,DepositBank,BankAccount,EMail, " +
		"BankCreditRating,Mobile,Telphone,Fax,CompanyTel,QQ,CompanyUrl,Remark,CreateOn,CreateBy,HouseNo) " +
		"select '11' as Status, 准入证号 as AccessCardNo, 企业名称 as SupplierName, 曾用名 as OldSupplierName, 准入类别 as SupplierTypeName,(case 准入类别 when '物资类' then '01' when '基建类' then '02' when '服务类' then '03' WHEN '技术服务类' THEN '03' end) SupplierTypeCode, " +
		"中石油供应商证书号 as OilCertificateNo, 级别 as Grade, 经营方式 as OperType, 国家 as Country, 所代理制造商名称 as MaunAgent, 施工队伍名称 as ConstructTeam, 工商注册号 as CommercialNo, 组织机构代码 as OrganCode, " +
		"税务登记证国税编号 as CountryTaxNo, 税务登记证地税编号 as LocalTaxNo, 单位地址 as Address, 邮编 AS ZipCode, 质量管理体系认证情况及认证机构 AS QualitySystemCert, 产品质量认证情况及认证机构 AS ProductQualityCert, " +
		"生产制造许可证获证情况及编号 AS MaunLicense, 企业资质证书编号 AS QualifCert, 企业资质证书级别 AS QualifCertLevel, 安全生产许可证 AS SafetyLicense, 技术服务类准入许可证 AS TechServiceLic, 外地企业进津备案通知书 AS TJInNotify, " +
		"行业特殊要求的认证证书 AS SpecIndustryCert, 法定代表人姓名 AS LegalPerson, 行业类别代码 AS CategoryCode, 行业类别名称 AS CategoryName, 注册资本 AS RegCapital, 币种 AS Currency, 联系人姓名 AS ContactName, 公司类型 AS CompanyType, " +
		"开户银行 AS DepositBank, 银行账号 AS BankAccount, 电子邮箱 AS EMail, 银行信用等级 AS BankCreditRating, 移动电话 AS Mobile, 固定电话 AS Telphone, 传真 AS Fax, 公司电话 AS CompanyTel, QQ号码 AS QQ, 公司网址 AS CompanyUrl, " +
		"申请变更原因及内容 AS Remark, 审核日期 as  CreateOn, 录入员 as CreateBy,ID AS HouseNo from T_变更信息 "
	_, err := s.DBE.Query(sql)
	// s.DBE.Query("commit;")
	s.DBE.Query("SET unique_checks=1;")
	// s.DBE.Query("SET autocommit=1;")
	return err
}

/*func (s *TmpzcgtSession) InsertOilSupplierCertSub() error {

	countSql := "SELECT count(*) from tmp_zengxiang"
	countStr, err := s.DBE.Query(countSql)
	var total int64
	if len(countStr) > 0 {
		results := countStr[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			break
		}
		n := math.Ceil(float64(total) / 2000)
		for i:=0; i <= int(n); i++ {
			start := strconv.Itoa(i * 2000)
			end := strconv.Itoa(2000)
			sql := "insert into OilSupplierCertSub (ModifiedBy,Type, OldId, SupplierTypeCode, Code, Name, Remark, CreateOn, CreateBy) " +
				"select 企业名称 as ModifiedBy,'3' as Type, id as OldId, ( case 准入类别 when '物资类' then '01' when '基建类' then '02' when '服务类' then '03' WHEN '技术服务类' THEN '03' end ) SupplierTypeCode, 物资编码 as Code, 产品名称 as Name, 备注 as Remark, 申请日期 as CreateOn, 录入员 as CreateBy " +
				"from tmp_zengxiang " +
				"limit " + start + "," +  end
			_, err = s.DBE.Query(sql)
			// s.UpdateCertSubIsType2()
		}
	}

	return err
}*/

func (s *TmpzcgtSession) UpdateOilInfoChange() error {
	// sql := "UPDATE OilInfoChange b SET b.SupplierId=IFNULL((SELECT a.SupplierId FROM OilSupplierCert a WHERE a.GfId = b.HouseNo limit 1), 0)"
	sql := "UPDATE OilInfoChange b SET b.SupplierId=IFNULL((SELECT a.SupplierId FROM OilSupplierCert a WHERE a.GfId = b.HouseNo limit 1), 0)"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *TmpzcgtSession) UpOilSupplierCertAppend() error {
	/** sql := "UPDATE OilSupplierCertAppend b SET " +
		"b.SupplierId = IFNULL((SELECT a.`SupplierId` FROM OilSupplierCert a WHERE a.`GfId` = b.OldId AND a.SupplierTypeCode = b.AppendType limit 1), 0), " +
		"b.SupplierCertId = IFNULL((SELECT a.`Id` FROM OilSupplierCert a WHERE a.`GfId` = b.OldId AND a.SupplierTypeCode = b.AppendType limit 1), 0) " */
	s.DBE.Query("SET unique_checks=0;")
	s.DBE.Query("SET autocommit=0;")

	sql := "UPDATE OilSupplierCertAppend b left join OilSupplierCert a ON (a.`GfId` = b.OldId AND a.SupplierTypeCode = b.AppendType) SET " +
		"b.SupplierId = IFNULL(a.`SupplierId`, 0), " +
		"b.SupplierCertId = IFNULL(a.`Id`, 0) "
	_, err := s.DBE.Query(sql)

	s.DBE.Query("commit;")

	s.DBE.Query("SET unique_checks=1;")
	s.DBE.Query("SET autocommit=1;")
	return err
}

// 更新增项准入编码 UpdateCertSubIsType2
func (s *TmpzcgtSession) UpdateOilSupplierCertAppendSub() error {
	/*sql := "UPDATE OilSupplierCertSub b SET " +
		"b.SupplierId = IFNULL((SELECT a.`SupplierId` FROM OilSupplierCertAppend a WHERE a.`OldId` = b.OldId AND a.AppendType = b.SupplierTypeCode AND a.ApplyDate = b.CreateOn limit 1), 0), " +
		"b.SupplierCertId = IFNULL((SELECT a.`SupplierCertId` FROM OilSupplierCertAppend a WHERE a.`OldId` = b.OldId AND a.AppendType = b.SupplierTypeCode AND a.ApplyDate = b.CreateOn limit 1), 0), " +
		"b.SupplierCertAppendId = IFNULL((SELECT a.`Id` FROM OilSupplierCertAppend a WHERE a.`OldId` = b.OldId AND a.AppendType = b.SupplierTypeCode AND a.ApplyDate = b.CreateOn limit 1), 0) " +
		"where b.Type = '3' and (b.SupplierId is null or b.SupplierId = 0)"*/

	/*sql := "UPDATE OilSupplierCertSub b left join OilSupplierCertAppend a ON (a.`OldId` = b.OldId AND a.AppendType = b.SupplierTypeCode AND a.ApplyDate = b.CreateOn) SET " +
		"b.SupplierId = IFNULL(a.`SupplierId`, 0), " +
		"b.SupplierCertId = IFNULL(a.`SupplierCertId`, 0), " +
		"b.SupplierCertAppendId = IFNULL(a.`Id`, 0) " +
		"where b.Type = '3' and (b.SupplierId is null or b.SupplierId = 0)"*/
	svc := suppliercertappend.GetOilSupplierCertAppendService(s.DBE)
	var certAppendList []suppliercertappend.OilSupplierCertAppend
	svc.GetEntitysByWhere("OilSupplierCertAppend", "", &certAppendList)
	var err error
	s.DBE.Query("SET unique_checks=0;")
	s.DBE.Query("SET autocommit=0;")
	for _, certAppend := range certAppendList {
		appendCodeList := strings.Split(certAppend.Remark, ",")
		appendCodeString := "("
		for _, appendCode := range appendCodeList {
			if len(appendCode) < 8 {
				appendCodeString += " (a.Code LIKE '"  + appendCode + "%') or"
			} else {
				appendCodeString += " (a.Code = '"  + appendCode + "') or"
			}
		}
		code := strings.TrimRight(appendCodeString, "or")
		sql := "UPDATE OilSupplierCertSub a SET a.SupplierCertAppendId=" + strconv.Itoa(certAppend.Id) +
			", a.`Type`=3 WHERE a.SupplierCertId=" + strconv.Itoa(certAppend.SupplierCertId) +
			" AND"  + code + ") and a.SupplierCertAppendId=0"
		_, err = s.DBE.Query(sql)
		s.DBE.Query("commit;")
	}
	s.DBE.Query("ALTER TABLE `OilSupplierCertSub` ADD INDEX index_supplierappendid ( `SupplierAppendId` );")
	s.DBE.Query("SET unique_checks=1;")
	s.DBE.Query("SET autocommit=1;")
	return err
}

// 导入资质名称
func (s *TmpzcgtSession) InsertFileName() error {
	s.DBE.Query("SET unique_checks=0;")
	// s.DBE.Query("SET autocommit=0;")
	sql := "INSERT INTO `OilSupplierFile` (SupplierId, SupplierCertSubId, FileUrl, Remark, NeedFileType, OtherRemark, SupplierTypeCode,CreateBy) " +
		"SELECT 0 AS SupplierId, 0 AS SupplierCertSubId, concat('/upfile/gfzl_2014/', f.ID_GF, '-' , f.类别编号, IFNULL(f.文件类型, ''), '|', f.类别, IFNULL(f.文件类型, '')) AS FileUrl, z.企业名称 AS Remark ,f.类别 AS NeedFileType, f.说明 AS OtherRemark, " +
		"( CASE z.准入类别 WHEN '物资类' THEN '01' WHEN '基建类' THEN '02' WHEN '服务类' THEN '03' WHEN '技术服务类' THEN '03' else '000' END ) SupplierTypeCode, " +
		"'导入' AS CreateBy " +
		"FROM `T_供方资料_V2014` f " +
		"LEFT JOIN `T_资格预审表` z ON (f.ID_GF = z.ID) " +
		"WHERE z.企业名称 IS NOT NULL"
	_, err := s.DBE.Query(sql)

	// s.DBE.Query("commit;")
	s.DBE.Query("SET unique_checks=1;")
	// s.DBE.Query("SET autocommit=1;")
	return err
}

func (s *TmpzcgtSession) UpdateSupplierFileSId() error {
	sql := "UPDATE `OilSupplierFile` b left join `OilSupplier` a ON (a.SupplierName = b.Remark AND a.Id IS NOT NULL) SET b.SupplierId = IFNULL(a.Id, 0) "
	_, err := s.DBE.Query(sql)
	return err
}

// 导入目录
func (s *TmpzcgtSession) InsertOilCatalog() error {
	s.DBE.Query("SET unique_checks=0;")
	// s.DBE.Query("SET autocommit=0;")
	sql := "insert into `OilCatalog` (`CompanyName`, `CatalogType`, `RecordScope`, `ValidityTo`) select `名称`, "+
		"CASE `类别` WHEN '多经企业' THEN 2 WHEN '战略合作' THEN 4 WHEN '特别业务' THEN 5 WHEN '外部市场' THEN 6 ELSE 0 END as `类别`, `合作范围`, `有效期` "+
		"from `T_可不招标供方名录` b order by b.ID"
	_, err := s.DBE.Query(sql)

	// s.DBE.Query("commit;")
	s.DBE.Query("SET unique_checks=1;")
	// s.DBE.Query("SET autocommit=1;")
	return err
}

func (s *TmpzcgtSession) InsertOilCatalogSub() error {
	s.DBE.Query("SET unique_checks=0;")
	// s.DBE.Query("SET autocommit=0;")
	sql := "insert into `OilCatalogSub` (`Type`, `Dept`, `DutyDept`) select a.`类别`, `申请单位`, `责任部门` from `T_可不招标供方名录` a left join `OilCatalog` b on b.`CompanyName` = a.`名称` and b.`RecordScope` = a.`合作范围` order by a.ID"
	_, err := s.DBE.Query(sql)

	// s.DBE.Query("commit;")
	s.DBE.Query("SET unique_checks=1;")
	// s.DBE.Query("SET autocommit=1;")
	return err
}

func (s *TmpzcgtSession) UpdateOilCatalog() error {
	sql := "update `OilCatalog` set `SubId` = `Id`"
	_, err := s.DBE.Query(sql)
	return err
}