fixing of charset of tables

json_dumps_invoiceItems.sql

/* leads charset fixation */
ALTER TABLE leads CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
/* create a view to write out table of requirement*/

/* -------------------------------------------------- */
CREATE VIEW `leadsJsonView_2` AS
select
  concat(
    '{',
    '"',
    'data',
    '"',
    ':',
    '[',
    regexp_replace(
      group_concat(
        json_object(
          'Name',
          cast(`leads`.`id` as char charset utf8mb4),
          'local_id',
          `leads`.`id`,
          'user_id',
          `leads`.`user_id`,
          'client_id',
          `leads`.`client_id`,
          'status',
          `leads`.`status`,
          'requirement',
          `leads`.`requirement`,
          'importance',
          `leads`.`importance`,
          'report_type',
          `leads`.`report_type`,
          'source',
          `leads`.`lead_source`,
          'causes',
          `leads`.`causes`,
          'invoice_id',
          `leads`.`invoice_id`,
          'temp',
          `leads`.`temp`,
          'opening_price',
          `leads`.`opening_price`,
          'closing_price',
          `leads`.`closing_price`,
          'time_from',
          date_format(`leads`.`time_from`, '%Y-%m-%d'),
          'time_to',
          date_format(`leads`.`time_to`, '%Y-%m-%d'),
          'added_on',
          concat(
            concat(
              replace(
                date_format(`leads`.`added_on`, '%Y-%m-%d %T'),
                ' ',
                'T'
              ),
              '+05:30'
            )
          )
        ) separator ','
      ),
      '(})',
      concat('}', '')
    ),
    ']',
    '}'
  ) AS `Name_exp_1`
from
  `leads`
  
  
/* -------------------------------------------------- */
/* write into json format*/
SELECT * from leadsJsonView_3 into outfile 'leadsJsonView_2.json';



/* leads charset invoice_items */
ALTER TABLE invoice_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
/* create a view to write out table of requirement*/
/* -------------------------------------------------- */

CREATE VIEW invoiceItemsJSON_1
AS
SELECT concat('{','"','data','"',':','[',
    REGEXP_REPLACE( 
        GROUP_CONCAT(json_object (
             'Name',CAST(id as CHAR),
             'local_id',id,
             'user_id',user_id,
             'data_status',data_status,
             'for_customization',for_customization,
             'payment_status',payment_status,
             'Amount',Amount,
             'Total_Months',Total_Months,
             'country',country,
             'direction',direction,
             'status',invoice_items.status,
          	 'invoice_id',invoice_id,
             'Mode_Of_Delivery',Mode_Of_Delivery,
             'Date_To',DATE_FORMAT(Date_To,'%Y-%m-%d'),
             'Date_From',DATE_FORMAT(Date_From,'%Y-%m-%d'),
             'added_on' , concat(CONCAT(replace(DATE_FORMAT(added_on,'%Y-%m-%d %T'),' ','T'),"+05:30")))), '(})', concat('}','')
        ) ,']' ,'}') from invoice_items ;
  
/* -------------------------------------------------- */

 /* write into json format*/
SELECT * from invoiceItemsJSON_1 into outfile 'invoiceItemsJson_1.json';