/*
SQLyog Community v13.1.9 (64 bit)
MySQL - 10.1.48-MariaDB-0ubuntu0.18.04.1 : Database - db_hsp_khush_sap
*********************************************************************
*/



/*Table structure for table `sps_api_data_field_mappings` */


CREATE TABLE `sps_api_data_field_mappings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `output_key` varchar(100) DEFAULT NULL,
  `type` enum('exact','sum','formula') DEFAULT 'exact',
  `source_keys` text,
  `formula_logic` text,
  `status` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4;

/*Data for the table `sps_api_data_field_mappings` */

insert  into `sps_api_data_field_mappings`(`id`,`output_key`,`type`,`source_keys`,`formula_logic`,`status`) values 
(1,'2W_QTY','exact','twoWhQty',NULL,1),
(2,'4W_QTY','exact','fourWhQty',NULL,1),
(3,'OTHER_QTY','exact','',NULL,1),
(4,'2W_REV','exact','twoWhRev',NULL,1),
(5,'4W_REV','exact','fourWhRev',NULL,1),
(6,'OTHER_REV','exact','',NULL,1),
(7,'2W_MEM_QTY','exact','memb2wQty',NULL,1),
(8,'4W_MEM_QTY','exact','memb4wQty',NULL,1),
(9,'OTHER_MEM_QTY','exact','',NULL,1),
(10,'2W_ISSUERENW_QTY','exact','twoWhIssueRenwQty',NULL,1),
(11,'2W_ISSUERENW_REV','exact','twoWhIssueRenwRev',NULL,1),
(12,'2W_SECUDEPS','exact','twoWhSecuDeps',NULL,1),
(13,'4W_ISSUERENW_QTY','exact','fourWhIssueRenwQty',NULL,1),
(14,'4W_ISSUERENW_REV','exact','fourWhIssueRenwRev',NULL,1),
(15,'4W_SECUDEPS','exact','fourWhSecuDeps',NULL,1),
(16,'OTHER_ISSUERENW_QTY','exact','',NULL,1),
(17,'OTHER_ISSUERENW_REV','exact','',NULL,1),
(18,'OTHER_SECUDEPS','exact','',NULL,1),
(19,'2W_FOC_QTY','exact','foc2wQty',NULL,1),
(20,'2W_FOC_AMT','exact','foc2wAmt',NULL,1),
(21,'4W_FOC_QTY','exact','foc4wQty',NULL,1),
(22,'4W_FOC_AMT','exact','foc4wAmt',NULL,1),
(23,'OTHER_FOC_QTY','exact','',NULL,1),
(24,'OTHER_FOC_AMT','exact','',NULL,1),
(25,'2W_VOUCHER_QTY','exact','voucher2wQty',NULL,1),
(26,'2W_VOUCHER_AMT','exact','voucher2wAmt',NULL,1),
(27,'4W_VOUCHER_QTY','exact','voucher4wQty',NULL,1),
(28,'4W_VOUCHER_AMT','exact','voucher4wAmt',NULL,1),
(29,'OTHER_VOUCHER_QTY','exact','',NULL,1),
(30,'OTHER_VOUCHER_AMT','exact','',NULL,1),
(31,'TOTAL_COLLECTION','exact','totalCollection',NULL,1),
(32,'CASH','exact',NULL,NULL,1),
(33,'UPI','exact',NULL,NULL,1),
(34,'CARD','exact',NULL,NULL,1),
(35,'FASTTAG','exact',NULL,NULL,1),
(36,'DATE','exact','saleDate',NULL,1),
(37,'LOCATION_CODE','exact','siteId',NULL,1),
(38,'SITE_NAME','exact','siteName',NULL,1);

/*Table structure for table `sps_vehicle_types_mapping` */


CREATE TABLE `sps_vehicle_types_mapping` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `main_type` varchar(50) NOT NULL,
  `vehicle_type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

/*Data for the table `sps_vehicle_types_mapping` */

insert  into `sps_vehicle_types_mapping`(`id`,`main_type`,`vehicle_type`) values 
(1,'4W',NULL),
(2,'2W',NULL),
(3,'other',NULL);



ALTER TABLE `sps_reports` ADD COLUMN `sap_daily_reveune` VARCHAR(500) NULL AFTER `location_id`, ADD COLUMN `sap_daily_collection` VARCHAR(500) NULL AFTER `sap_daily_reveune`; 

ALTER TABLE `sps_reports` CHANGE `sap_daily_reveune` `sap_daily_reveune` TEXT CHARSET utf8 COLLATE utf8_general_ci NULL, CHANGE `sap_daily_collection` `sap_daily_collection` TEXT CHARSET utf8 COLLATE utf8_general_ci NULL;
