Arka Guptta Blog: PUNCH MAIL PROCDURE

--- EXEC punch_mail

CREATE procedure punch_mail
AS
BEGIN

CREATE TABLE #Punch_Table_Som1
(
C03_Organisation_code GLCOMPANY NULL,
C29_Location_code GLLOCN NULL,
H01_Emp_Num empnum NULL,
C02_Function_Code FUNCODE NULL,
C02_Function_Desc FUNCDESC NULL,
C12_Positioncode POS NULL,
C12_Positiondesc POSDESC NULL,
C08_Catg_code CATG NULL,
C09_Grade GRA NULL,
C10_Job_code JOB NULL,
H10_Effective_Date_From Datetime NULL,
Late_Cat Varchar(5) NULL,
Type Varchar(20) NULL,
Time1 varchar(10) NULL,
Time2 varchar(10) NULL,
Time3 varchar(10) NULL,
Time4 varchar(10) NULL,
Time5 varchar(10) NULL,
Time6 varchar(10) NULL,
Time7 varchar(10) NULL,
Time8 varchar(10) NULL,
Time9 varchar(10) NULL,
Time10 varchar(10) NULL,
Time11 varchar(10) NULL,
Time12 varchar(10) NULL,
Time13 varchar(10) NULL,
Time14 varchar(10) NULL,
Time15 varchar(10) NULL,
Time16 varchar(10) NULL,
Time17 varchar(10) NULL,
Time18 varchar(10) NULL,
Time19 varchar(10) NULL,
Time20 varchar(10) NULL,
Time21 varchar(10) NULL,
Time22 varchar(10) NULL,
Time23 varchar(10) NULL,
Time24 varchar(10) NULL,
Time25 varchar(10) NULL,
Time26 varchar(10) NULL,
Time27 varchar(10) NULL,
Time28 varchar(10) NULL,
Time29 varchar(10) NULL,
Time30 varchar(10) NULL,
Time31 varchar(10) NULL,
Attnd varchar(10) NULL,
Name varchar(60) NULL,
Sort varchar(2) NULL,
From_Date Datetime,
To_date Datetime,
Total_day cmn_Pqty,
Process_Month varchar(20),
Late_Ded Numeric(28,8) NULL
)

Declare @Max_date datetime,
@H01_emp_num empnum,
@Email_Id varchar(50)

Select @Max_date = (select MAX(IN_DATE) from attendance_f)

Declare @Process_Month varchar(20)

Select @Process_Month = (Select P03_Process_period_desc from P03_Process_Periods where @Max_date between P03_Period_From_date and P03_Period_To_date)

DECLARE H01_emp_num_CURSOR CURSOR FOR
Select DISTINCT H01_emp_num from Punch_Email where enable = 1

OPEN H01_emp_num_CURSOR
FETCH NEXT
From H01_emp_num_CURSOR into @H01_emp_num

Select @Email_Id = (Select Email_Id from Punch_Email where H01_emp_num = @H01_emp_num)

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Punch_Table_Som1
EXEC Punch_Report_Som1 'KSLTDJMD','KSSD','1',@Process_Month,@H01_emp_num,@H01_emp_num,'1.ALL','Department'
---------------------------------------------------------------------
DECLARE @tableHTML1 as NVARCHAR(MAX),
@subjec1 as varchar(50)

Select @subjec1 = 'Card Punching Report for the month of '+@Process_Month

Set @tableHTML1 =
N' Dear Sir,
' +
N' Please find the daily punching report as follows;
' +
    N'' +
    N' ' +

CAST ( ( SELECT distinct td = a.H01_Emp_Num,'',
                    td = a.Name,'',
     td = a.C02_Function_Desc,'',
     td = a.C12_Positiondesc,'',
     td = a.C10_Job_code,''
              FROM #Punch_Table_Som1 as a
    where a.H01_Emp_Num = @H01_emp_num
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
Code Name Department Designation job Code
' +
    N'
' +
N'

' +
    N'' +
    N' ' +
    CAST ( ( SELECT td = a.Type,'',
                    td = a.Time1,'',
     td = a.Time2,'',
     td = a.Time3,'',
     td = a.Time4,'',
     td = a.Time5,'',
     td = a.Time6,'',
     td = a.Time7,'',
     td = a.Time8,'',
     td = a.Time9,'',
     td = a.Time10,'',
     td = a.Time11,'',
     td = a.Time12,'',
     td = a.Time13,'',
     td = a.Time14,'',
     td = a.Time15,'',
     td = a.Time16,'',
     td = a.Time17,'',
     td = a.Time18,'',
     td = a.Time19,'',
     td = a.Time20,'',
     td = a.Time21,'',
     td = a.Time22,'',
     td = a.Time23,'',
     td = a.Time24,'',
     td = a.Time25,'',
     td = a.Time26,'',
     td = a.Time27,'',
     td = a.Time28,'',
     td = a.Time29,'',
     td = a.Time30,'',
     td = a.Time31,''
              FROM #Punch_Table_Som1 as a
    where a.H01_Emp_Num = @H01_emp_num
    ORDER BY a.Sort
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
Type 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18



19 20 21 22 23 24 25 26 27 28 29 30 31
' +
    N'
' +
    N'

' +
N' With Best Regards,
' +
N' Kohinoor Steel (P) Ltd.
'+
N'' +
N'

' +
N' *******This is an automated message. Please do not reply*******
'



EXEC msdb.dbo.sp_send_dbmail
    @profile_name='ERP',
    @recipients=@Email_Id,
    --@copy_recipients = 'priyanka@kohinoorindia.in;avinash@kohinoorsteel.com',
    --@blind_copy_recipients = 'erp@kohinoorindia.in',
    @subject =@subjec1,
    @body = @tableHTML1,
    @body_format = 'HTML'


---------------------------------------------------------------------


FETCH NEXT
From H01_emp_num_CURSOR into @H01_emp_num
END
CLOSE H01_emp_num_CURSOR
DEALLOCATE H01_emp_num_CURSOR
END

/*INSERT INTO PUNCH_EMAIL
VALUES('31095','erp@kohinoorindia.in',1)
INSERT INTO PUNCH_EMAIL
VALUES('31251','autogenerated@kohinoorindia.in',1)
Select * from PUNCH_EMAIL*/

-- Exec punch_mail
-------------------------------------------------------------------------------------------------------------

Arka Guptta Blog

Monday, January 28, 2013

PUNCH MAIL PROCDURE

Dear Sir,

Please find the daily punching report as follows;

With Best Regards,

Kohinoor Steel (P) Ltd.

This is an automated message. Please do not reply

No comments:

Post a Comment

Arka Gupta's Blog

Blog Archive