<br/>===Start time =02:49:23pm===<br/>Database connected<br>Current date : 2025-12-12 14 49 23<br><br>Database name : microlabslive_summary<br><br></br>2025</br>12</br>11</br>2025</br>25microlabslive_summary<br>current date-time:2025-12-12 14:49:23<br>Firstdate=2025-11-01<br>Lastdate=2025-11-30<br>Financial year=2025-26<br><br>delete from reporting_summary where month ='11' and year ='2025' <br><br>insert into reporting_summary
select '' as pid, microlablive.users.division as divisionid,'' as zonecode,'' as districtcode,'' as areacode,
patchsapcode as territorycode,'T' as codeidentifier, user_name as username,
id as userid, reports_to_id,'11' as month,'2025' as year,0000 as fieldwork_days,0000 as admin_days,
0000 as leave_days,0000 as not_filled_days,
0000 as total_drs_met,0000 as total_chem_met,0000 as total_drs_list,
0000 as total_spl_dr_list, 00 as sun_hol, 00 as other_activity_day,30 as total_days,
0000 as total_rpl_dr_list,0000 as total_tpl_dr_list,0000 as total_kol_dr_list,
0000 as total_core_dr_list,0000 as total_imp_dr_list,000 as dr_met_once,0000 as dr_met_twice,0000 as dr_met_thrice,
0000 as dr_met_more_than_thrice,0000 as dr_met_4times,0000 as dr_met_more_than_4times,0000 as missed_doctor_count,
0 as jw_abm_days,'' as jw_abm_dates,0 as jw_rbm_days,'' as jw_rbm_dates,0 as jw_zbm_days,'' as jw_zbm_dates,
0000 as total_doctor_calls,0000 as total_chemist_calls,
0000 as dr_avg,0000.00 as chemist_avg,000.00  as totalcoverage, 000.00 as totalcomplaince,
00000.00 as totalwapcompliance,00000.00 as totpob,00000.00 as totrcpa,00000 as noofdr_0_brand,00000 as noofdr_1_brand,
00000 as noofof_2_brand,00000 as noofdr_3more_brand,00000 as highthrust_rcpa,0000 as brandindex,
00000.00 as totpob_cf,00000.00 as totrcpa_cf,00000 as noofdr_0_brand_cf,00000 as noofdr_1_brand_cf,
00000 as noofdr_2_brand_cf,00000 as noofdr_3more_brand_cf, 0000 as brandindex_cf,
0000 as goods_return,'0000-00-00' as lastdcrdate,0000 as my40_drmet,0000 as my40list, 0000.00 as my40_coverage,
00 as target_value,00 as primary_value,00 as secondary_value,0 as tp_deviated_days,0 as flag,
0000 as missed_calls,
00000.00 as compliance_sum, 
00000.00 as compliance_suma,
00000.00 as compliance_sumaplus,
00000.00 as compliance_sumb,
00000.00 as compliance_sumc,
00000.00 as compliance_sumkol,
00000.00 as compliance_sumts,
00000.00 as compliance_sumuc,
00000.00 as compliance_a,
00000.00 as compliance_aplus,
00000.00 as compliance_b,
00000.00 as compliance_c,
00000.00 as compliance_kol,
00000.00 as compliance_ts,
00000.00 as compliance_uc,
0000 as totalclassa,
0000 as totalclassaplus,
0000 as totalclassb, 
0000 as totalclassc,
0000 as totalclasskol,
0000 as totalclassts,
0000 as totalclassuc,
0000 as totalclassamet,
0000 as totalclassaplusmet,
0000 as totalclassbmet,
0000 as totalclasscmet,
0000 as totalclasskolmet,
0000 as totalclasstsmet,
0000 as totalclassucmet,
000000.00 as total_coveragea,
000000.00 as total_coverageaplus,
000000.00 as total_coverageb,
000000.00 as total_coveragec,
000000.00 as total_coveragekol,
000000.00 as total_coveragets,
000000.00 as total_coverageuc,
0 as doc_met1,
0 as doc_met2,
0 as doc_met3,
0 as doc_missed1,
0 as doc_missed2,
0 as doc_missed3
from microlablive.users, microlablive.user2role, microlablive.role2profile, microlablive.patches
where microlablive.users.id=microlablive.user2role.userid
and microlablive.user2role.roleid=microlablive.role2profile.roleid
and profileid='5' 
and microlablive.users.deleted =0
and microlablive.patches.patchid= microlablive.users.patch
and microlablive.patches.patch_deleted =0
and microlablive.users.joining_date <= '2025-11-30' 
order by microlablive.users.division, employeecode<br>============================================<br><br>update reporting_summary,(
  select p2.smownerid as id,11 as month,2025 as year,sum(if(substring(p2.pid_new,(length(p2.pid_new)),1)=1,1,0)) as cnt1,
  sum(if(substring(p2.pid_new,(length(p2.pid_new)),1)=2,1,0)) as cnt2,
  sum(if(substring(p2.pid_new,(length(p2.pid_new)),1)=0,1,0)) as cnt3
   from (
  select p1.smownerid,p1.contactid,max(p1.pid_new) as pid_new from (
  select smownerid, microlablive.contactdetails.contactid,microlablive.contactdetails.frequency,microlablive.contact_frequency_changes.frequencyid,
  concat(microlablive.contact_frequency_changes.pid,mod(microlablive.visitfrequency.frequency,3)) as pid_new,
  microlablive.contact_frequency_changes.pid as pid,applicable_from_date,
  if(applicable_to_date='0000-00-00','2025-11-30',applicable_to_date) as applicable_to_date,frequencyname,
  concat(microlablive.contactdetails.contactid,microlablive.contact_frequency_changes.frequencyid) as freq,
  space(10) as details
  from microlablive.contactdetails,microlablive.contact_frequency_changes,microlablive.visitfrequency,microlablive.users,microlablive.user2role,microlablive.role2profile
  where  microlablive.contactdetails.contactid=microlablive.contact_frequency_changes.contactid 
  and microlablive.contact_frequency_changes.frequencyid=microlablive.visitfrequency.frequencyid 
  and microlablive.contactdetails.smownerid=microlablive.users.id
  and microlablive.users.id=microlablive.user2role.userid
  and microlablive.user2role.roleid=microlablive.role2profile.roleid
  and profileid='5' 
  and applicable_from_date<='2025-11-30'
  and microlablive.visitfrequency.fyear='2025-26'
  and  (microlablive.contactdetails.created_date<='2025-11-30'  or microlablive.contactdetails.created_date='0000-00-00') 
    and ((microlablive.contactdetails.deleted=1 and deleted_date>'2025-11-30') or (microlablive.contactdetails.deleted_date='0000-00-00' and microlablive.contactdetails.deleted=0) ) 
  and cast('2025-11-30' as date) between applicable_from_date and if(applicable_to_date='0000-00-00','2025-11-30',applicable_to_date)) p1
  group by 1,2) p2 group by 1) p3
  set reporting_summary.total_drs_list = p3.cnt1+p3.cnt2+p3.cnt3,
  reporting_summary.total_spl_dr_list = p3.cnt1,
  reporting_summary.total_rpl_dr_list = p3.cnt2,
  reporting_summary.total_tpl_dr_list =p3.cnt3
  where reporting_summary.userid = p3.id
  and reporting_summary.month =p3.month
  and reporting_summary.year = p3.year;<br><br>update reporting_summary,(
  select p2.smownerid,11 as month,2025 as year,sum(if(p2.cnt=1,1,0)) as cnt_1, sum(if(p2.cnt=2,1,0)) as cnt_2, sum(if(p2.cnt=3,1,0)) as cnt_3, 
   sum(if(p2.cnt >3,1,0)) as cn_more_3,   sum(if(p2.cnt =4,1,0)) as cn_4,sum(if(p2.cnt > 4,1,0)) as cn_more_4 from (
  select p1.smownerid,p1.contactid,p1.pid_new,count(p1.pid_new) as cnt from (
       select microlablive.dcrs_main.smownerid,microlablive.contactdetails.frequency,microlablive.dcrs_contact.contactid,microlablive.dcrs_main.date,microlablive.contact_frequency_changes.pid,
        if(mod(microlablive.visitfrequency.frequency,3)=0,3,mod(microlablive.visitfrequency.frequency,3)) as pid_new,
       applicable_from_date,applicable_to_date ,microlablive.contact_frequency_changes.frequencyid
                                    from microlablive.dcrs_main, microlablive.dcrs_contact,microlablive.dcrs,microlablive.contactdetails,microlablive.contact_frequency_changes,microlablive.visitfrequency
                                    where microlablive.dcrs_main.deleted=0 
                                    and microlablive.dcrs_main.month=11
                                    and microlablive.dcrs_main.year=2025
                                    and microlablive.dcrs_contact.contacttype ='D' 
                                    and microlablive.dcrs_main.mainid=microlablive.dcrs.mainid
                                    and microlablive.dcrs.pid=microlablive.dcrs_contact.mainid
                    and microlablive.dcrs_contact.deleted =0
                                    and microlablive.dcrs.deleted =0
                    and microlablive.dcrs_main.smownerid=microlablive.contactdetails.smownerid
                                    and microlablive.dcrs_contact.contactid = microlablive.contactdetails.contactid
                                    and  microlablive.contactdetails.contactid= microlablive.contact_frequency_changes.contactid
                    and microlablive.contact_frequency_changes.frequencyid=microlablive.visitfrequency.frequencyid 
                    and microlablive.visitfrequency.fyear='2025-26'
                                    and microlablive.contact_frequency_changes.applicable_from_date <= '2025-11-30'
                                    and (created_date<='2025-11-30' or microlablive.contactdetails.created_date='0000-00-00')
                                    and ((microlablive.contactdetails.deleted=1 and microlablive.contactdetails.deleted_date> '2025-11-30') 
                                    or(microlablive.contactdetails.deleted_date='0000-00-00' and microlablive.contactdetails.deleted=0))
                                    and microlablive.dcrs.deleted=0 and microlablive.dcrs.status='Pending' 
                                    and microlablive.dcrs_main.status='Pending' and microlablive.dcrs_contact.deleted=0 
                                    and cast('2025-11-30' as date) between applicable_from_date 
                                    and if(applicable_to_date='0000-00-00','2025-11-30',applicable_to_date) 
                                    group by microlablive.dcrs_main.smownerid,microlablive.dcrs_contact.contactid, microlablive.dcrs_main.date) p1
                        group by 1,2,3 ) p2
  group by 1 ) p3
  set reporting_summary.total_drs_met =p3.cnt_1+p3.cnt_2+p3.cnt_3+p3.cn_more_3,
  reporting_summary.dr_met_once =p3.cnt_1,
  reporting_summary.dr_met_twice =p3.cnt_2,
  reporting_summary.dr_met_thrice=p3.cnt_3,
  reporting_summary.dr_met_more_than_thrice =p3.cn_more_3,
  reporting_summary.dr_met_more_than_4times =p3.cn_more_4
  where reporting_summary.userid = p3.smownerid
  and reporting_summary.month=p3.month
  and reporting_summary.year = p3.year<br><br>update reporting_summary , (
  select p1.smownerid,count(p1.contactid) as cnt
                           from (select microlablive.dcrs_main.smownerid,microlablive.dcrs_main.date,microlablive.dcrs_contact.contactid
                                    from microlablive.dcrs_main, microlablive.dcrs_contact,microlablive.dcrs,microlablive.bcntscontactdetails
                                    where microlablive.dcrs_main.deleted=0 
                                    and microlablive.dcrs_main.month='11'
                                    and microlablive.dcrs_main.year='2025'
                                    and microlablive.dcrs_contact.contacttype ='C' 
                                    and microlablive.dcrs_main.mainid=microlablive.dcrs.mainid
                                    and microlablive.dcrs.pid=microlablive.dcrs_contact.mainid
                                    and microlablive.dcrs_contact.contactid =microlablive.bcntscontactdetails.contactid
                                    and (microlablive.bcntscontactdetails.created_date<='2025-11-30' or microlablive.bcntscontactdetails.created_date='0000-00-00')
                                    and ((microlablive.bcntscontactdetails.deleted=1 and microlablive.bcntscontactdetails.deleted_date> '2025-11-30') 
                                             or (microlablive.bcntscontactdetails.deleted_date='0000-00-00' and microlablive.bcntscontactdetails.deleted=0))
                                    group by microlablive.dcrs_main.smownerid,microlablive.dcrs_contact.contactid) p1
                        group by p1.smownerid
  ) p2
  set reporting_summary.total_chem_met = p2.cnt
  where reporting_summary.userid= p2.smownerid
  and reporting_summary.month='11'
  and reporting_summary.year ='2025'<br>============================================<br><br>update reporting_summary, (select p1.smownerid,sum(if(p1.type='DCRS',1,0))-round(sum(if(p1.type='ACTIVITY',1,0)) /2,2) as cnt from (
  select dcrs_main.mainid,dcrs.type,dcrs_main.smownerid   
  from microlablive.dcrs_main, microlablive.dcrs 
  where month=month('2025-11-30') 
  and year=year('2025-11-30') 
  and dcrs_main.deleted=0
  and dcrs_main.mainid = dcrs.mainid
  and dcrs.deleted=0 
  and dcrs.status='Pending' 
  and dcrs_main.status='Pending' 
  and ( dcrs.fullday_type is null or dcrs.fullday_type ='0')
  group by dcrs_main.mainid,dcrs.type 
) p1
group by p1.smownerid) t2
set reporting_summary.fieldwork_days = t2.cnt
where reporting_summary.userid= t2.smownerid
and reporting_summary.month='11'
and reporting_summary.year ='2025'<br><br>update reporting_summary , (select p1.smownerid,count(p1.contactid) as cnt
 from (select microlablive.dcrs_main.smownerid,microlablive.dcrs_main.date,microlablive.dcrs_contact.contactid
 from microlablive.dcrs_main, microlablive.dcrs_contact, microlablive.dcrs
 where microlablive.dcrs_main.deleted=0
  and microlablive.dcrs_main.month='11'
  and microlablive.dcrs_main.year='2025'
  and microlablive.dcrs_contact.contacttype ='D'
  and microlablive.dcrs_main.mainid=microlablive.dcrs.mainid
  and microlablive.dcrs.pid=microlablive.dcrs_contact.mainid
  and microlablive.dcrs_contact.deleted =0
  and microlablive.dcrs.deleted =0
  group by microlablive.dcrs_main.smownerid,microlablive.dcrs_main.date,microlablive.dcrs_contact.contactid) p1
  group by p1.smownerid) p2 
  set reporting_summary.total_doctor_calls = p2.cnt,
reporting_summary.dr_avg=if(reporting_summary.fieldwork_days <> 0, round(p2.cnt/reporting_summary.fieldwork_days,2),0)
where reporting_summary.userid= p2.smownerid
and reporting_summary.month='11'
and reporting_summary.year ='2025';<br><br>update reporting_summary set missed_doctor_count=total_drs_list - total_drs_met<br><br>update reporting_summary,(
  select smownerid, microlablive.users.user_name, max(date) as lastdcrdate 
  from microlablive.dcrs_main, microlablive.users
  where microlablive.dcrs_main.deleted = 0 
  and microlablive.dcrs_main.smownerid = microlablive.users.id
  and month = '11'
  and year = '2025'
  group by 1) p1
  set reporting_summary.lastdcrdate = p1.lastdcrdate
  where reporting_summary.userid = p1.smownerid
  and reporting_summary.month = '11'
  and reporting_summary.year = '2025';<br>============================================<br><br>update reporting_summary ,(
  select p2.smownerid, '11' as month, '2025' as year, sum(p2.cnt) as cnt from(
  select p1.smownerid,count(p1.contactid) as cnt,p1.dd,p1.month,p1.year
                           from (select microlablive.dcrs_main.smownerid,microlablive.dcrs_contact.contactid,day(microlablive.dcrs_main.date) as dd,microlablive.dcrs_main.month,microlablive.dcrs_main.year
                                    from microlablive.dcrs_main, microlablive.dcrs_contact,microlablive.dcrs
                                    where microlablive.dcrs_main.deleted=0 
                                    and microlablive.dcrs_main.month= '11'
                                    and microlablive.dcrs_main.year='2025'
                                    and microlablive.dcrs_contact.contacttype ='C' 
                                    and microlablive.dcrs_main.mainid=microlablive.dcrs.mainid
                                    and microlablive.dcrs.pid=microlablive.dcrs_contact.mainid
                                    and microlablive.dcrs.deleted =0
                                    and microlablive.dcrs_contact.deleted =0
                                  group by microlablive.dcrs_main.smownerid,microlablive.dcrs_contact.contactid,day(microlablive.dcrs_main.date) ) p1
                     group by p1.smownerid,p1.dd) p2
  group by 1) p3
    set reporting_summary.total_chemist_calls = p3.cnt,
  reporting_summary.chemist_avg=if(reporting_summary.fieldwork_days <> 0, round(p3.cnt/
  reporting_summary.fieldwork_days,2),0)
  where reporting_summary.userid= p3.smownerid
  and reporting_summary.month='11'
  and reporting_summary.year ='2025';<br><br>update reporting_summary ,(
  select p1.smownerid, sum(if(p1.fullday_type<> '0',1,0))+sum(if(p1.firsthalf_type <> '0',0.5,0))+sum(if(p1.secondhalf_type <> '0',0.5,0)) as cnt from (
      select dcrs_main.mainid, dcrs.type, dcrs_main.smownerid, dcrs.fullday_type, dcrs.firsthalf_type, dcrs.secondhalf_type  
      from microlablive.dcrs_main, microlablive.dcrs 
      where month='11' 
      and year='2025'
      and dcrs_main.deleted=0
      and dcrs_main.mainid = dcrs.mainid
      and dcrs.deleted=0
      and dcrs.fullday_type is not null 
      and dcrs.status='Pending' 
      and dcrs_main.status='Pending' 
      group by dcrs_main.mainid, dcrs.type 
  ) p1
  group by p1.smownerid) t2
  set reporting_summary.admin_days = t2.cnt
  where reporting_summary.userid= t2.smownerid
  and reporting_summary.month ='11' 
  and reporting_summary.year ='2025'<br><br>update reporting_summary,(
  select p2.smownerid,11 as month,2025 as year,sum(if(p2.pid_new=1,1,0)) as cnt_1, 
  sum(if(p2.pid_new=2,1,0)) as cnt_2, sum(if(p2.pid_new=3,1,0)) as cnt_3, 
   sum(if(p2.pid_new  >0,1,0)) as tot_cnt  from (
  select p1.smownerid,p1.contactid,p1.pid_new,count(p1.pid_new) as cnt from (
       select microlablive.contactdetails.smownerid,microlablive.contactdetails.frequency,microlablive.dcrs_contact.contactid,microlablive.dcrs_main.date,microlablive.contact_frequency_changes.pid,
        if(mod(microlablive.visitfrequency.frequency,3)=0,3,mod(microlablive.visitfrequency.frequency,3)) as pid_new,
       applicable_from_date,applicable_to_date ,microlablive.contact_frequency_changes.frequencyid
                                    from microlablive.dcrs_main, microlablive.dcrs_contact, microlablive.dcrs,microlablive.contactdetails,microlablive.contact_frequency_changes,microlablive.visitfrequency
                                    where microlablive.dcrs_main.deleted=0 
                                    and microlablive.dcrs_main.month='11'
                                    and microlablive.dcrs_main.year='2025'
                                    and microlablive.dcrs_contact.contacttype ='D' 
                                    and microlablive.dcrs_main.mainid=microlablive.dcrs.mainid
                                    and microlablive.dcrs.pid=microlablive.dcrs_contact.mainid
                                    and microlablive.dcrs_contact.deleted =0
                                    and microlablive.dcrs.deleted =0
                                    and microlablive.dcrs_contact.contactid = microlablive.contactdetails.contactid
                     and microlablive.dcrs_main.smownerid=microlablive.contactdetails.smownerid  
                                    and  microlablive.contactdetails.contactid= microlablive.contact_frequency_changes.contactid
                     and microlablive.contact_frequency_changes.frequencyid=microlablive.visitfrequency.frequencyid 
                    and microlablive.visitfrequency.fyear='2025-26'
                                    and microlablive.contact_frequency_changes.applicable_from_date <= '2025-11-30'
                                    and (created_date<='2025-11-30' or microlablive.contactdetails.created_date='0000-00-00')
                                    and ((microlablive.contactdetails.deleted=1 and microlablive.contactdetails.deleted_date> '2025-11-30' ) 
  or(microlablive.contactdetails.deleted_date='0000-00-00' and microlablive.contactdetails.deleted=0))
                                    and microlablive.dcrs.deleted=0 and microlablive.dcrs.status='Pending' and microlablive.dcrs_main.status='Pending' and microlablive.dcrs_contact.deleted=0 
                                    and cast('2025-11-30' as date) between applicable_from_date 
                                          and if(applicable_to_date='0000-00-00','2025-11-30',applicable_to_date) 
                                    group by microlablive.dcrs_main.smownerid,microlablive.dcrs_contact.contactid, microlablive.dcrs_main.date) p1
                        group by 1,2,3 ) p2
  group by 1 ) p3 
  set reporting_summary.doc_met1 = p3.cnt_1,
  reporting_summary.doc_met2 = p3.cnt_2,
  reporting_summary.doc_met3= p3.cnt_3
     where reporting_summary.userid =p3.smownerid
     and reporting_summary.month=11
     and reporting_summary.year = 2025<br><br>update reporting_summary
   set reporting_summary.doc_missed1=(reporting_summary.total_spl_dr_list-reporting_summary.doc_met1) 
   where reporting_summary.month=11 and reporting_summary.year=2025  
   and reporting_summary.total_spl_dr_list>reporting_summary.doc_met1Query 35 Failed: Table 'microlabslive_summary.reporting_summary' doesn't exist