DELIMITER $$
DROP PROCEDURE IF EXISTS DMS_SP_CopyGlobalTemplates;
CREATE PROCEDURE `DMS_SP_CopyGlobalTemplates` (IN GlobalTemplateID bigint)
BEGIN 
  DECLARE UserID BIGINT;
  DECLARE FieldID BIGINT;
	SET UserID = (select ID from dms_prod.users where usertypeid=68 limit 1); 
  # documenttemplate 
  INSERT INTO dms_prod.documenttemplate 
              ( 
                          templatename, 
                          description, 
                          templatefilename, 
                          accessibility, 
                          documenttype, 
                          documentpath, 
                          sourceproductid, 
                          templatetypeid, 
                          templateversion, 
                          templatedocumentsource, 
                          templatestatusid, 
                          templatestartdate, 
                          templateenddate, 
                          createdby, 
                          companyid, 
                          updatedby, 
                          dateupdated, 
                          datecreated, 
                          uploaddate, 
                          uploadedby, 
                          isarchived, 
                          esigneddocument, 
                          completedwithin, 
                          isdeleted, 
                          languageid, 
                          templatefiletypeid 
              ) 
  SELECT templatename, 
         description, 
         templatefilename, 
         accessibility, 
         documenttype, 
         documentpath, 
         sourceproductid, 
         templatetypeid, 
         templateversion, 
         templatedocumentsource, 
         templatestatusid, 
         templatestartdate, 
         templateenddate, 
         UserID AS createdby, 
         companyid, 
         UserID             AS updatedby, 
         CURRENT_TIMESTAMP() AS dateupdated, 
         CURRENT_TIMESTAMP() AS datecreated, 
         uploaddate, 
         uploadedby, 
         isarchived, 
         esigneddocument, 
         completedwithin, 
         isdeleted, 
         languageid, 
         templatefiletypeid 
  FROM   dms_master.documenttemplate 
  WHERE  id = GlobalTemplateID; 
  
   SET @InsertedTemplateID = LAST_INSERT_ID();
  # documenttemplatefields 
  INSERT INTO dms_prod.documenttemplatefields 
              ( 
                          templateid, 
                          templatefieldid, 
                          templatefieldname, 
                          fieldtype, 
                          createdby, 
                          datecreated, 
                          companyid, 
                          updatedby, 
                          dateupdated, 
                          templatefieldfontname, 
                          templatefieldfontsize 
              ) 
  SELECT @InsertedTemplateID as templateid, 
         templatefieldid, 
         templatefieldname, 
         fieldtype, 
         UserID             AS createdby, 
         CURRENT_TIMESTAMP() AS datecreated, 
         companyid, 
         UserID             AS updatedby, 
         CURRENT_TIMESTAMP() AS dateupdated, 
         templatefieldfontname, 
         templatefieldfontsize 
  FROM   dms_master.documenttemplatefields 
  WHERE  templateid = GlobalTemplateID; 
   

SET @MaxCount=( SELECT MAX(ID) from dms_master.documenttemplatefields where templateid=GlobalTemplateID);
SET @MinCount=( SELECT MIN(ID) from dms_master.documenttemplatefields where templateid=GlobalTemplateID);

WHILE @MinCount<=@MaxCount DO

	SET FieldID = (select DocumentTemplateFieldID from dms_master.documenttemplatefieldmappings where DocumentTemplateFieldID=@MinCount);

		IF FieldID is not NULL THEN
			# documenttemplatefieldmappings 
            
            SET @ProdFieldID = (select PDF.ID 
								from dms_prod.documenttemplatefields PDF 
                                inner join dms_master.documenttemplatefields PREDF 
								on PDF.TemplateFieldID=PREDF.TemplateFieldID 
                                and PDF.TemplateFieldName=PREDF.TemplateFieldName 
                                where PDF.templateid=@InsertedTemplateID 
                                and PREDF.templateid=GlobalTemplateID and PREDF.ID=FieldID);
				INSERT INTO dms_prod.documenttemplatefieldmappings 
						  ( 
									  documenttemplatefieldid, 
									  dmsfieldid, 
									  sequencenumber, 
									  createdby, 
									  datecreated, 
									  companyid, 
									  updatedby, 
									  dateupdated 
						  ) 
			  SELECT @ProdFieldID as documenttemplatefieldid, 
					 dmsfieldid, 
					 sequencenumber, 
					 UserID             AS createdby, 
					 CURRENT_TIMESTAMP() AS datecreated, 
					 companyid, 
					 UserID             AS updatedby, 
					 CURRENT_TIMESTAMP() AS dateupdated 
			  FROM   dms_master.documenttemplatefieldmappings 
			  WHERE  documenttemplatefieldid = FieldID; 
				  
                 # documenttemplatefieldsrights 
					  INSERT INTO dms_prod.documenttemplatefieldsrights 
								  ( 
											  documenttemplatefieldsid, 
											  accessrightsid, 
											  usertypeid, 
											  created_datetime, 
											  createdby_userid, 
											  lastupdate_datetime, 
											  lastupdatedby_userid 
								  ) 
					  SELECT @ProdFieldID as documenttemplatefieldsid, 
							 accessrightsid, 
							 usertypeid, 
							 CURRENT_TIMESTAMP() AS created_datetime, 
							 UserID             AS createdby_userid, 
							 CURRENT_TIMESTAMP() AS  lastupdate_datetime, 
							 UserID             AS lastupdatedby_userid 
					  FROM   dms_master.documenttemplatefieldsrights 
					  WHERE  documenttemplatefieldsid = FieldID;

		END IF;
	SET @MinCount = @MinCount + 1;
END WHILE;
   

END