Create PROCEDURE [dbo].[spLatinName] AS

declare @stCommand Varchar(600)
--1--------------------ريختن نام اول كالا در ساير اسامي در صورت نبودن --------------------

Set @stCommand = 'Insert into tblNameKala '+
                '(K_Code , NameKala) ' +
                'Select K_Code , Name1 '+
                'From KalaId '+
                'Where NOT Exists(Select K_Code From tblNameKala Where KalaId.K_Code = K_Code  AND KalaId.Name1 = NameKala) '
Exec (@stCommand)
print 'Step 1 Success !!!'
--2-------------------------------- ريختن نام لاتين در نام اول ----------------------------
Set @stCommand ='Update KalaId '+
				'set name1 = XLSData.LName , '+
				'Barcode   = XLSData.InternalCode , '+
				'Name2     = XLSData.Inn '+
				'From Kalaid '+
				'Inner join '+ 
				'('+
				  'Select LName , InternalCode , Inn , column2 '+
				  'From LatinXLS...[codes$] as codes  Where column2 Is Not Null '+
				') as XLSData '+
				'On KalaId.K_Code = XLSData.column2 '+
				'Where Ltrim(Rtrim(XLSData.LName)) not in (Select Ltrim(Rtrim(Name1)) From Kalaid)-- where K_Code = XLSData.column2 AND Name1 = XLSData.LName) '
Exec (@stCommand)
print 'Step 2 Success !!!'
--3----------------------- اضافه كردن داروهايي كه تعريف نشده اند -----------------------
Set @stCommand = 'Insert into KalaId ' +
				 '(K_Code , Name1 , Barcode , Meli_Code , Name2 , User_Id , U_Code1) ' +
				 'Select InternalCode , LName , InternalCode , InternalCode , Inn , 10 , 1 '  +
				 'From LatinXLS...[codes$] ' +
				 'Where column2 Is  Null AND LName IS NOT NULL ' +
				 'AND LName NOT in (Select Name1 From KalaId)'
Exec (@stCommand)
print 'Step 3 Success !!!'
--4---------------------- ريختن شكل دارو از اكسل در جدول گروه ----------------------------
Set @stCommand ='Insert Into Group1 '+
				'(Gname ) '+
				'Select RTrim(LTrim(Form)) '+
				'From  LatinXLS...[codes$] '+
				'Where Form Is Not Null and RTrim(LTrim(Form)) Not in (Select RTrim(LTrim(GName)) From Group1) '+
				'Group By Form'
Exec (@stCommand)
print 'Step 4-1 Success !!!'
           --** * * * * * * * ---
Set @stCommand ='Update KalaId '+
 				'Set Gid_Fk = tbl1.GID '+
				'From ( ' +
				  	 'SELECT GID , GName , Column2, Form ' +
					 'From  Group1 '+ 
				     'Inner Join LatinXLS...[codes$] LatinData On RTrim(LTrim(LatinData.Form)) = RTrim(LTrim(Group1.GName)) '+
				     'Where Form Is Not Null AND Column2 IS NOT NULL '+
 				     ') as tbl1 '+
				'Inner Join KalaId ON tbl1.Column2 = KalaId.K_Code AND ISNULL(Gid_Fk,0) <> ISNULL(tbl1.GID ,0) '
Exec (@stCommand)
           --** * * * * * * * ---
print 'Step 4-2 Success !!!'
Set @stCommand ='Update KalaId ' +
				'Set Gid_Fk = tbl1.GID ' +
				'From ( '+
				  	 'SELECT GID , GName , InternalCode, Form '+
					 'From  Group1 '+
				     'Inner Join LatinXLS...[codes$] LatinData On RTrim(LTrim(LatinData.Form)) = RTrim(LTrim(Group1.GName)) '+
				     'Where Form Is Not Null AND Column2 IS  NULL '+
				     ') as tbl1 ' +
				'Inner Join KalaId ON tbl1.InternalCode = KalaId.K_Code AND ISNULL(Gid_Fk,0) <> ISNULL(tbl1.GID ,0)  '
Exec (@stCommand)
print 'Step 4-3 Success !!!'
--5---------------------- حذف كاراكترهاي اضافي -------------------------------
Update KalaId
Set Name1 = Upper(Replace(Name1 , '®',' ' ) )
print 'Step 5 Success !!!'
