#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <netinet/in.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>
 
#include "./include/db_adaptor.h"

extern _db_cfg  db_cfg;

#define	ARRAY_SIZE	1024
#define   MAX_PREFIX_ROW    2048

#define  PREFIX_TABLE_FILE "/usr/local/agt/etc/tariff/prefix_table.txt"

extern char *GetAsciiTime();
extern void crm_prov_hlr_auc_vms_account(char sub_data[][ARRAY_SIZE]);
extern int GetMonthDay();
extern int pstn_get_krone_pair_resource_id();
extern int pstn_get_ssentry_resource_id();
extern int dba_db_sql_query_only_execute_real(const char *sql, char result[][1024], int MAX_ROWS, int MAX_FIELDS);

typedef enum CRM_CLI_ST{
	CRM_CLI_ST_READ_CMD,
	CRM_CLI_ST_HSS_PROV,
	CRM_CLI_ST_HSS_PROV_RES,
	CRM_CLI_ST_AUC_PROV,
	CRM_CLI_ST_AUC_PROV_RES,
	CRM_CLI_ST_OCS_PROV,
	CRM_CLI_ST_OCS_PROV_RES,
	CRM_CLI_ST_VMS_PROV,
	CRM_CLI_ST_VMS_PROV_RES,	
	CRM_CLI_ST_PCRF_PROV,
	CRM_CLI_ST_PCRF_PROV_RES,
	CRM_CLI_ST_PSTN_PROV,
	CRM_CLI_ST_PSTN_PROV_RES,
}_CRM_CLI_ST;

typedef enum CRM_SCAN_ST{
	CRM_SCAN_ST_NULL,
	CRM_SCAN_ST_START,
	CRM_SCAN_ST_END,	
}_CRM_SCAN_ST;

typedef struct dba_crm_thread
{
	int	state;
	int	state_timer;
	int	sub_state;
	int	sub_state_timer;
	int	key_id;
	int   resend;
}_dba_crm_thread;

_dba_crm_thread  crm_smcli_thread, crm_scan_bundle_thread;

 /*---
to check if any pending subs, provisoning command
to AUC, HLR/HSS, OCS, VMS or PSTN
the cli command is configurable
-----*/
typedef enum cmd_type
{
CT_CREATE_NA,
CT_CREATE_SUB,
CT_REPLACE_SUB,
CT_SUSPEND_ACCOUNT,
CT_DISABLE_ACCOUNT,
CT_RESUME_ACCOUNT,
CT_DISCONNECT_ACCOUNT,
CT_UPDATE_SUB,
CT_MAX,
}cmd_type;

typedef enum node_type
{
NT_NA,
NT_OCS,
NT_HSS,
NT_AUC,
NT_VMS,
NT_PSTN,
NT_MSAN,
NT_ADSL,
NT_PCRF,
NT_MAX,
}node_type;

char node_name[NT_MAX][128]={"NULL","OCS","HSS","AUC","VMS","PSTN","MSAN","ADSL","PCRF"};
/*-------------------------------------------------*/
int dba_crm_dump_prefix_table_into_file()
{
	char sql[256];
	int rc;
	FILE *fp=NULL;
	
	sprintf(sql, "SELECT area_code, area_name FROM config_area");

	fp = fopen(PREFIX_TABLE_FILE, "w");
	
	rc = dba_db_sql_execute_crm(sql, NULL, MAX_PREFIX_ROW, 3, 1, fp);

	if(rc != 0)
	{	
		dba_crm_set_prefix_table_created();
	}

	if(fp != NULL)
		fclose(fp);
	
	return 1;
}

int dba_crm_get_product_rent(int from, int package_id)
{
	char sql[1024];
	char rent_des[1][ARRAY_SIZE];
	int	rent = 0;
	int rc;

/*	
	sprintf(sql, 
"SELECT f.FEE_VALUE rentValueFROM tb_prd_ofr_detail_inst_551 d, tb_prd_ofr_inst_551 i, tb_prd_ofr o, tb_prd_prd_once_fee f\
 WHERE d.PRD_INST_ID = %d\  
 AND d.EXP_DATE > CURRENT_TIMESTAMP  AND d.EFF_DATE <= CURRENT_TIMESTAMP  AND i.OFR_INST_ID = d.OFR_INST_ID \
 AND o.OFR_ID = i.OFR_ID  AND o.OFR_TYPE_ID = 1  AND f.PRD_ID = o.OFR_ID  AND f.TYPE = 2  AND f.FEE_TYPE = 5", 
package_id);
*/
	sprintf(sql, 
"SELECT f.FEE_VALUE rentValue FROM tb_prd_prd_once_fee f WHERE f.PRD_ID = %d AND f.TYPE = 2  AND f.FEE_TYPE = 5", package_id);

	if(from == 0)//crm
		rc = dba_db_sql_execute_crm(sql, rent_des, 1, 1, 0, NULL);
	else
		rc = dba_db_sql_execute_ocs(sql, rent_des, 1, 1, 0, NULL);
	
	if(rc != 0)
	{
		if(rent_des[0][0] == 0)
			rent = 0;
		else
			rent = atoi(rent_des[0]);
	}
	
	return rent;
}

int dba_crm_get_vc_key_with_seq(int seq_id, char *result)
{
	char sql[256];
	char key[1][ARRAY_SIZE];
	int rc;
	
	sprintf(sql, "SELECT IMPORT_KEY from card_seq_info where SEQ_ID=%d", seq_id);

	rc = dba_db_sql_execute_ocs(sql, key, 1, 1, 0, NULL);

	if(rc != 0)
	{
		strcpy(result, key[0]);
	}
	
	return rc;
}

int dba_crm_get_vc_info_with_enc_pwd(const char *enc_vc_pwd, char result[][ARRAY_SIZE])
{
	char sql[256];
	int rc;
	
	sprintf(sql, "SELECT BALANCE, STATE, UNIX_TIMESTAMP(EXP_DATE) from card_info_20c where PASSWD='%s'", enc_vc_pwd);

	rc = dba_db_sql_execute_ocs(sql, result, 3, 64, 0, NULL);
	
	return rc;
}

int dba_crm_get_vc_validity_days(int face_value)
{
	char sql[256];
	char valid_day_buf[1][ARRAY_SIZE];
	int rc, valid_day=1;
	
	sprintf(sql, "select VALID_EXTEND from tb_bil_recharge_valid where min_value=%d", face_value);

	rc = dba_db_sql_execute_ocs(sql, valid_day_buf, 1, 1, 0, NULL);

	if(rc != 0)
	{
		valid_day = atoi(valid_day_buf[0]);
	}

	return valid_day;
}

int dba_crm_update_vc_info_as_used_with_enc_pwd(const char *msisdn, const char *enc_vc_pwd, const char *status)
{
	char sql[256];
	int rc;

	sprintf(sql, "UPDATE card_info_20c set STATE='%s', STATE_DATE=NOW() where PASSWD='%s'", status, enc_vc_pwd);
	
//	sprintf(sql, "UPDATE card_info_20c set STATE='%s', STATE_DATE=NOW(), SERVICE_NBR='%s' where PASSWD='%s'", status, enc_vc_pwd, msisdn);

	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
	
	return rc;
}

int dba_crm_insert_recharge_record_in_db(long account_id, const char *msisdn, int pay_amount, int balance)
{
	char sql[512];
	char acct_id[1][ARRAY_SIZE];
	int op=17;
	char state_done[]={"C0C"};
	int rc;

	sprintf(sql, "select acct_id from tb_prd_prd_inst_551 where PRD_INST_STAS_ID IN ('1001','1201') and service_nbr='%s'", msisdn);
	rc = dba_db_sql_execute_ocs(sql, acct_id, 1, 1);
	if(rc != 0)
		sprintf(sql, "INSERT INTO tb_bil_pay_log_551 set OPER_LATN_ID=551, OPERATION_TYPE=%d, STATE='%s', CREATED_DATE=NOW(), STATE_DATE=NOW(), OBJ_ID='%s', OBJ_TYPE=0, \
			PRE_BALANCE=%d, PAY_AMOUNT=%d, LEFT_BALANCE=%d, SERVICE_NBR='%s', PAY_METHOD=17", 
			op, state_done, acct_id[0], balance, pay_amount, balance+pay_amount, msisdn);
	else
		sprintf(sql, "INSERT INTO tb_bil_pay_log_551 set OPER_LATN_ID=551, OPERATION_TYPE=%d, STATE='%s', CREATED_DATE=NOW(), STATE_DATE=NOW(), OBJ_ID=%ld, OBJ_TYPE=0, \
			PRE_BALANCE=%d, PAY_AMOUNT=%d, LEFT_BALANCE=%d, SERVICE_NBR='%s',  PAY_METHOD=17", 
			op, state_done, account_id, balance, pay_amount, balance+pay_amount, msisdn);

	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
	
	return rc;
}

int dba_verify_vc_with_pwd(const char *vc_pwd, char card_info[][ARRAY_SIZE], char *enc_password)
{
	char enc_vc_pwd[ARRAY_SIZE], asc_vc_pwd_buf[ARRAY_SIZE];
	char md5_key[ARRAY_SIZE]={"11111111"};
	char vc_info[32][ARRAY_SIZE];
	/* the MD5 key is fetch with vc sequnce info: 2/5/8/11 digits of the input PWD */
	int seq_id = 0;
	int len;
	int i;
	int rc = 0;
	
	if(vc_pwd == NULL || enc_vc_pwd == NULL)
		return 0;
	len = strlen(vc_pwd);
	if(len < 12)
		return 0;
	seq_id = (vc_pwd[2] - 0x30)*1000 +
			 (vc_pwd[5] - 0x30)*100 +
			  (vc_pwd[8] - 0x30)*10 +
			   (vc_pwd[11] - 0x30)*1;

	if(dba_crm_get_vc_key_with_seq(seq_id, md5_key) == 0)
	{
		myLOG_D("DBA: Can't get key with seq_id=%d\n", seq_id);
		return 0;
	}
	else
	{
		myLOG_D("DBA: Get key=%s with seq_id=%d\n", md5_key, seq_id)	;
	}
	
	hmac_md5(vc_pwd, strlen(vc_pwd), md5_key, 8, enc_vc_pwd);
	len = 16;
	BcdToAscii(asc_vc_pwd_buf, enc_vc_pwd, len*2);
	printf("Enc pwd: %s\n", asc_vc_pwd_buf);

	rc = dba_crm_get_vc_info_with_enc_pwd(asc_vc_pwd_buf, vc_info);
	if(rc != 0)
	{
		strcpy(enc_password, asc_vc_pwd_buf);
		if(card_info != NULL)
		{
			for(i=0; i<3; i++)
				strcpy(card_info[i], vc_info[i]);
		}
	}
	
	return rc;
}

static int log_file_counter=0;
static char sql_head_of_data[4096], sql_head_of_cs[4096];
static char date_time_str[24];
void get_cur_data_time_str()
{
    time_t time_ptr;
    struct tm *tmp_ptr = NULL;
    int year,month,day,hour,minute,sec;


    time(&time_ptr);

    tmp_ptr = localtime(&time_ptr);

    year = tmp_ptr->tm_year + 1900;
    month = tmp_ptr->tm_mon + 1;
    day = tmp_ptr->tm_mday;

    hour = tmp_ptr->tm_hour;
    minute = tmp_ptr->tm_min;
    sec = tmp_ptr->tm_sec;
    
    sprintf(date_time_str, "%04d%02d%02d%02d%02d%02d",year,month,day,hour,minute,sec);
    return 0;
}

void dba_init_tariff_sql()
{
	int holidy_benefis_support = 1;
	
if(holidy_benefis_support == 0)
{
strcpy(sql_head_of_data,
"SELECT \
 IFNULL(hr.holiday_discount,100) as holiday_discount, \
 g.TARIFF_ID, \
 p.PRD_INST_ID, \
 b.OFR_INST_ID, \
 rr.FREE_VALUE AS ratableValue, \
 rr.VALUE usedRatable, \
 g.ACCT_ITEM_TYPE_ID acctItemType, \
 g.SCALED_RATE_VALUE_ID fee, \
 g.rate_unit rateUnit, \
 IFNULL( rr.id, 0 ) \
FROM \
 tb_prd_ofr_detail_inst_551 a \ 
 INNER JOIN tb_prd_prd_inst_551 p ON a.ofr_detail_inst_ref_id = p.PRD_INST_ID \
 INNER JOIN tb_prd_ofr_inst_551 b ON a.ofr_inst_id = b.ofr_inst_id \
 INNER JOIN tb_prd_ofr c ON b.ofr_id = c.ofr_id \
 INNER JOIN tb_bil_pricing_combine_group d ON c.pricing_plan_id = d.pricing_plan_id \
 INNER JOIN tb_bil_pricing_combine_gp_list e ON d.pricing_combine_group_id = e.pricing_combine_group_id \
 INNER JOIN tb_bil_pricing_section f ON e.event_pricing_strategy_id = f.event_pricing_strategy_id \
 AND f.TARIFF_ID \
 IS NOT NULL INNER JOIN tb_bil_tariff g ON f.pricing_section_id = g.pricing_section_id \
 INNER JOIN tb_bil_evt_pricing_strategy h ON e.event_pricing_strategy_id = h.event_pricing_strategy_id \
 LEFT JOIN ratable_history rr ON rr.OWNER_ID = p.PRD_INST_ID \ 
 AND rr.PRICING_SUB_SECTION_ID = g.PRICING_SECTION_ID \
"
 );

	
strcpy(sql_head_of_cs,
"SELECT \
 IFNULL(hr.holiday_discount,100) as holiday_discount, \
 g.TARIFF_ID, \
 p.PRD_INST_ID, \
 b.OFR_INST_ID, \
 rr.FREE_VALUE AS ratableValue, \
 rr.VALUE usedRatable, \
 g.ACCT_ITEM_TYPE_ID acctItemType, \
 g.SCALED_RATE_VALUE_ID fee, \
 g.rate_unit rateUnit, \
 IFNULL( rr.id, 0 ) \
FROM \
 tb_prd_ofr_detail_inst_551 a \ 
 INNER JOIN tb_prd_prd_inst_551 p ON a.ofr_detail_inst_ref_id = p.PRD_INST_ID \
 INNER JOIN tb_prd_ofr_inst_551 b ON a.ofr_inst_id = b.ofr_inst_id \
 INNER JOIN tb_prd_ofr c ON b.ofr_id = c.ofr_id \
 INNER JOIN tb_bil_pricing_combine_group d ON c.pricing_plan_id = d.pricing_plan_id \
 INNER JOIN tb_bil_pricing_combine_gp_list e ON d.pricing_combine_group_id = e.pricing_combine_group_id \
 INNER JOIN tb_bil_pricing_section f ON e.event_pricing_strategy_id = f.event_pricing_strategy_id \
 AND f.TARIFF_ID \
 IS NOT NULL INNER JOIN tb_bil_tariff g ON f.pricing_section_id = g.pricing_section_id \
 INNER JOIN tb_bil_evt_pricing_strategy h ON e.event_pricing_strategy_id = h.event_pricing_strategy_id \
 INNER JOIN TB_BIL_PRICING_AREA ar ON ar.STRATEGY_ID = h.event_pricing_strategy_id \
 INNER JOIN config_area ca ON ca.area_id = ar.area_id \
 LEFT JOIN ratable_history rr ON rr.OWNER_ID = p.PRD_INST_ID \ 
 AND rr.PRICING_SUB_SECTION_ID = g.PRICING_SECTION_ID \
"
);
}
else
{
	strcpy(sql_head_of_data,
"SELECT \
 IFNULL(hr.holiday_discount,100) as holiday_discount, \
 g.TARIFF_ID, \
 p.PRD_INST_ID, \
 b.OFR_INST_ID, \
 rr.FREE_VALUE AS ratableValue, \
 rr.VALUE usedRatable, \
 g.ACCT_ITEM_TYPE_ID acctItemType, \
 g.SCALED_RATE_VALUE_ID fee, \
 g.rate_unit rateUnit, \
 IFNULL( rr.id, 0 ) \
FROM \
 tb_prd_ofr_detail_inst_551 a \ 
 INNER JOIN tb_prd_prd_inst_551 p ON a.ofr_detail_inst_ref_id = p.PRD_INST_ID \
 INNER JOIN tb_prd_ofr_inst_551 b ON a.ofr_inst_id = b.ofr_inst_id \
 INNER JOIN tb_prd_ofr c ON b.ofr_id = c.ofr_id \
 INNER JOIN tb_bil_pricing_combine_group d ON c.pricing_plan_id = d.pricing_plan_id \
 INNER JOIN tb_bil_pricing_combine_gp_list e ON d.pricing_combine_group_id = e.pricing_combine_group_id \
 INNER JOIN tb_bil_pricing_section f ON e.event_pricing_strategy_id = f.event_pricing_strategy_id \
 AND f.TARIFF_ID \
 IS NOT NULL INNER JOIN tb_bil_tariff g ON f.pricing_section_id = g.pricing_section_id \
 INNER JOIN tb_bil_evt_pricing_strategy h ON e.event_pricing_strategy_id = h.event_pricing_strategy_id \
 LEFT JOIN ratable_history rr ON rr.OWNER_ID = p.PRD_INST_ID \ 
 AND rr.PRICING_SUB_SECTION_ID = g.PRICING_SECTION_ID \
 LEFT JOIN (SELECT t.HOLIDAY_GROUP FROM ( \
SELECT hd.HOLIDAY_GROUP, hd.HOLIDAY_PRIORITY, COUNT(1) AS HOLIDAY_COUNT FROM tb_bil_holiday hd \
 WHERE ((hd.HOLIDAY_TYPE = '02' AND hd.HOLIDAY_BEGIN_DATE >= (WEEKDAY(SYSDATE()) + 1) AND hd.holiday_end_date <= (WEEKDAY(SYSDATE()) + 1)) \
     OR (hd.holiday_type = '03' AND DATE_FORMAT(SYSDATE(),'%m%d') >= DATE_FORMAT(CONCAT('2020', hd.HOLIDAY_BEGIN_DATE), '%m%d') AND DATE_FORMAT(SYSDATE(),'%m%d') <= DATE_FORMAT(CONCAT('2020', hd.holiday_end_date), '%m%d')) \
     OR (hd.holiday_type = '04' AND DATE_FORMAT(CURTIME(), '%H%i%s') >= DATE_FORMAT(STR_TO_DATE(CONCAT('20200101', hd.HOLIDAY_BEGIN_DATE), '%Y%m%d%H%i%s'),'%H%i%s') \
     AND DATE_FORMAT(CURTIME(), '%H%i%s') <= DATE_FORMAT(STR_TO_DATE(CONCAT('20200101', hd.holiday_end_date), '%Y%m%d%H%i%s'),'%H%i%s')) \
		 and hd.STATE='L0R') \
GROUP BY hd.HOLIDAY_PRIORITY, hd.HOLIDAY_GROUP) t \
WHERE t.HOLIDAY_COUNT = (SELECT COUNT(1) FROM tb_bil_holiday w WHERE t.HOLIDAY_GROUP = w.HOLIDAY_GROUP) \
ORDER BY t.HOLIDAY_PRIORITY LIMIT 1) hd ON 1 = 1 \
 LEFT JOIN tb_bil_holiday_rel hr ON ( hr.state='1' and hr.tariff_id = g.TARIFF_ID AND g.tariff_seq = hr.tariff_seq AND hd.HOLIDAY_GROUP = hr.holiday_group ) \ "
 );

	
strcpy(sql_head_of_cs,
"SELECT \
 IFNULL(hr.holiday_discount,100) as holiday_discount, \
 g.TARIFF_ID, \
 p.PRD_INST_ID, \
 b.OFR_INST_ID, \
 rr.FREE_VALUE AS ratableValue, \
 rr.VALUE usedRatable, \
 g.ACCT_ITEM_TYPE_ID acctItemType, \
 g.SCALED_RATE_VALUE_ID fee, \
 g.rate_unit rateUnit, \
 IFNULL( rr.id, 0 ) \
FROM \
 tb_prd_ofr_detail_inst_551 a \ 
 INNER JOIN tb_prd_prd_inst_551 p ON a.ofr_detail_inst_ref_id = p.PRD_INST_ID \
 INNER JOIN tb_prd_ofr_inst_551 b ON a.ofr_inst_id = b.ofr_inst_id \
 INNER JOIN tb_prd_ofr c ON b.ofr_id = c.ofr_id \
 INNER JOIN tb_bil_pricing_combine_group d ON c.pricing_plan_id = d.pricing_plan_id \
 INNER JOIN tb_bil_pricing_combine_gp_list e ON d.pricing_combine_group_id = e.pricing_combine_group_id \
 INNER JOIN tb_bil_pricing_section f ON e.event_pricing_strategy_id = f.event_pricing_strategy_id \
 AND f.TARIFF_ID \
 IS NOT NULL INNER JOIN tb_bil_tariff g ON f.pricing_section_id = g.pricing_section_id \
 INNER JOIN tb_bil_evt_pricing_strategy h ON e.event_pricing_strategy_id = h.event_pricing_strategy_id \
 INNER JOIN TB_BIL_PRICING_AREA ar ON ar.STRATEGY_ID = h.event_pricing_strategy_id \
 INNER JOIN config_area ca ON ca.area_id = ar.area_id \
 LEFT JOIN ratable_history rr ON rr.OWNER_ID = p.PRD_INST_ID \ 
 AND rr.PRICING_SUB_SECTION_ID = g.PRICING_SECTION_ID \
 LEFT JOIN (SELECT t.HOLIDAY_GROUP FROM ( \
SELECT hd.HOLIDAY_GROUP, hd.HOLIDAY_PRIORITY, COUNT(1) AS HOLIDAY_COUNT FROM tb_bil_holiday hd \
 WHERE ((hd.HOLIDAY_TYPE = '02' AND hd.HOLIDAY_BEGIN_DATE >= (WEEKDAY(SYSDATE()) + 1) AND hd.holiday_end_date <= (WEEKDAY(SYSDATE()) + 1)) \
     OR (hd.holiday_type = '03' AND DATE_FORMAT(SYSDATE(),'%m%d') >= DATE_FORMAT(CONCAT('2020', hd.HOLIDAY_BEGIN_DATE), '%m%d') AND DATE_FORMAT(SYSDATE(),'%m%d') <= DATE_FORMAT(CONCAT('2020', hd.holiday_end_date), '%m%d')) \
     OR (hd.holiday_type = '04' AND DATE_FORMAT(CURTIME(), '%H%i%s') >= DATE_FORMAT(STR_TO_DATE(CONCAT('20200101', hd.HOLIDAY_BEGIN_DATE), '%Y%m%d%H%i%s'),'%H%i%s') \
     AND DATE_FORMAT(CURTIME(), '%H%i%s') <= DATE_FORMAT(STR_TO_DATE(CONCAT('20200101', hd.holiday_end_date), '%Y%m%d%H%i%s'),'%H%i%s')) \
		 and hd.STATE='L0R') \
GROUP BY hd.HOLIDAY_PRIORITY, hd.HOLIDAY_GROUP) t \
WHERE t.HOLIDAY_COUNT = (SELECT COUNT(1) FROM tb_bil_holiday w WHERE t.HOLIDAY_GROUP = w.HOLIDAY_GROUP) \
ORDER BY t.HOLIDAY_PRIORITY LIMIT 1) hd ON 1 = 1 \
 LEFT JOIN tb_bil_holiday_rel hr ON ( hr.state='1' and hr.tariff_id = g.TARIFF_ID AND g.tariff_seq = hr.tariff_seq AND hd.HOLIDAY_GROUP = hr.holiday_group ) \ "
 );
}

}

int dba_query_tariff_record(char *msisdn, int call_type, char *called_number, char tariff[][1024])
{
	char sql_head[4096], sql_tail[2048], sql_tail1[2048];
	char sql[8192];
	int rc;
	int now_sec;
	struct timeval tv;
	
	gettimeofday(&tv, NULL);
	now_sec = tv.tv_sec % 86400;

if(call_type==2)//data
{
	
sprintf(sql_tail, 
" WHERE g.MEASURE_DOMAIN = '%02d' \ 
 AND p.PRD_INST_STAS_ID = '1001' \ 
 AND a.EXP_DATE > SYSDATE( ) \
 AND ((c.OFR_TYPE_ID = '3' AND rr.OFR_INST_ID = b.OFR_INST_ID AND rr.ID > 0 ) OR ( c.ofr_type_id = '1' )) \
 AND TIME_TO_SEC( g.START_REF_VALUE ) <= %d AND TIME_TO_SEC( g.END_REF_VALUE ) >= %d \
 AND p.SERVICE_NBR = '%s' ",
     call_type,  now_sec, now_sec, msisdn);
 
strcpy(sql_tail1,
"AND ((rr.`ID` > 0 AND STR_TO_DATE(rr.END_TIME, '%Y%m%d%H%i%s') >= SYSDATE() AND rr.`FREE_VALUE` > rr.`VALUE`) OR (rr.`ID` IS NULL)) \
ORDER BY c.CALC_PRIORITY, c.OFR_TYPE_ID DESC,h.EVENT_PRIORITY DESC, a.CRT_DATE, rr.FREE_VALUE ASC \
LIMIT 1" );

	sprintf(sql, "%s%s%s", sql_head_of_data, sql_tail, sql_tail1);
	
}
else
{
	

sprintf(sql_tail, 
" WHERE ca.area_code = '%s' \ 
 AND g.MEASURE_DOMAIN = '%02d' \ 
 AND p.PRD_INST_STAS_ID = '1001' \ 
 AND a.EXP_DATE > SYSDATE( ) \
 AND ((c.OFR_TYPE_ID = '3' AND rr.OFR_INST_ID = b.OFR_INST_ID AND rr.ID > 0 ) OR ( c.ofr_type_id = '1' )) \
 AND TIME_TO_SEC( g.START_REF_VALUE ) <= %d AND TIME_TO_SEC( g.END_REF_VALUE ) >= %d \
 AND p.SERVICE_NBR = '%s' ", 
 called_number, call_type,  now_sec, now_sec, msisdn);

strcpy(sql_tail1, 
"AND ((rr.`ID` > 0 AND STR_TO_DATE(rr.END_TIME, '%Y%m%d%H%i%s') >= SYSDATE() AND rr.`FREE_VALUE` > rr.`VALUE`) OR (rr.`ID` IS NULL)) \
ORDER BY c.CALC_PRIORITY DESC,h.EVENT_PRIORITY DESC, a.CRT_DATE, rr.FREE_VALUE ASC \
LIMIT 1");

	sprintf(sql, "%s%s%s", sql_head_of_cs, sql_tail, sql_tail1);

}


	if(0)
      {
                FILE *fp=NULL;
		   char file_name[128];
		   
		   sprintf(file_name,"./log/sql_%04d.txt", log_file_counter++%1000);
		   
                fp = fopen(file_name,"w");
                fprintf(fp,"%s", sql);

		   fclose(fp);
       }

	if(0)
	{
		rc= dba_db_sql_query_only_execute_real(sql, tariff, 1, 10);
	}
	else
	{
		rc = dba_db_sql_execute_ocs(sql, tariff, 1, 10, 0, NULL);
	}
	
	return rc;

}

int dba_update_plan_info(int service_type, int plan_id, long long plan_used_value, int update_expiry_date, long long plan_total_value)
{
	char sql[8192];
	int rc;

	if(update_expiry_date)
	{
		//get_cur_data_time_str();
		//sprintf(sql, "UPDATE ratable_history SET `VALUE`=%lld, END_TIME='%s' WHERE ID=%d", plan_total_value, date_time_str, plan_id);// 20200831000000
		sprintf(sql, "UPDATE ratable_history SET `VALUE`=%lld WHERE ID=%d", plan_total_value, plan_id);
	}
	else
	{
		sprintf(sql, "UPDATE ratable_history SET `VALUE`=%lld WHERE ID=%d", plan_used_value, plan_id);
	}
	
	
	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

	return rc;
}

int dba_update_plan_data_info(int plan_id, long long volume_used_value, int op_flag)
{
	char sql[8192];
	int rc;

	if(op_flag == 0)
		sprintf(sql, "UPDATE ratable_history SET `VALUE`= VALUE+%lld  WHERE ID=%d", volume_used_value, plan_id);
	else
		sprintf(sql, "UPDATE ratable_history SET `VALUE`= VALUE-%lld  WHERE ID=%d", volume_used_value, plan_id);
	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

	return rc;
}

int dba_send_plan_notification_info(int account_id, int plan_id, int service_type, int update_expiry_date, int used_rate)
{
	char sql[8192];
	int rc;
	int rate = used_rate;
	int rate_type = service_type;
	
	if(update_expiry_date)
	{
		rate = 100;
	}
	/*
	if(rate_type == 2)
		rate_type = 3;
	else if(rate_type == 3)
		rate_type = 2;
	*/
	
	sprintf(sql, "INSERT into tb_sync_msg SET msg_type=2, rate_type=%d,  acct_id=%d, rate_value=%d, state=1, ofr_inst_id=%d" ,rate_type, account_id, rate,plan_id);

	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

	return rc;
}

int dba_send_expired_notification_info(int account_id, int plan_id, long expired_date)
{
	char sql[8192];
	int rc;
	
	sprintf(sql, "INSERT into tb_sync_msg SET msg_type=1, acct_id=%d, ofr_inst_id=%d, exp_date=FROM_UNIXTIME(%ld), state=1", account_id, plan_id, expired_date);

	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

	return rc;
}

/*
typedef enum account_status {
    T_FRESH=0,         
    T_NORMAL,         
    T_SUSPEND,
    T_BLACKLIST,
    T_RELEASED,
    T_OPRTRIAL,
    T_DISABLE,
    MAX_USER_TYPE,

    T_EXTERNAL=10,
} _account_status;

*/
int dba_update_user_status_in_crm(int account_id, int account_status)
{
	char sql[1024];
	int status_in_crm=1001;
	int rc;
	
	switch(account_status)
	{
		case 1:
			status_in_crm = 1001;
		break;
		case 2:
			status_in_crm = 1205;			
		break;
		case 4:
			status_in_crm = 1101;
		break;
		case 6:
			status_in_crm = 1202;			
		break;
	}
	if(0)
	{
		sprintf(sql, "update tb_prd_prd_inst_551 set PRD_INST_STAS_ID=%d, MOD_DATE=NOW() where ACCT_ID=%d", status_in_crm, account_id);
		rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
	}
	
	if(account_status == 4)//disconnection
	{
		if(1)
		{
			sprintf(sql, "INSERT INTO tb_mobile_disc_cmd set ACCT_ID=%d, ACCT_STATUS=0, UPDATE_DATE=NOW()",  account_id);
			rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
		}

		mobile_delete_handle(account_id);
	}
	
}

int 	dba_send_update_hlr_tele_service(char *msisdn, int account_status)
{
	char cmd_update_user[1024];
	char sql[1024];
	int rc;

	switch(account_status)
	{
		case 1://normal
			sprintf(cmd_update_user,"modify subscriber -msisdn %s -odb disable -telephony enable -sms Both", msisdn);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_RESUME_ACCOUNT, NT_HSS, node_name[NT_HSS], msisdn,
				cmd_update_user);
		break;
		case 2: //suspended
			sprintf(cmd_update_user,"modify subscriber -msisdn %s -odb enable -baoc enable -telephony enable - sms Mobile_Term", msisdn);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_SUSPEND_ACCOUNT, NT_HSS, node_name[NT_HSS], msisdn,
				cmd_update_user);
		break;
		case 6://Disable
		sprintf(cmd_update_user,"modify subscriber -msisdn %s-odb disable -telephony disable -sms Mobile_Term", msisdn);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_DISABLE_ACCOUNT, NT_HSS, node_name[NT_HSS], msisdn,
				cmd_update_user);
			break;
		case 4: //Disconnect
			sprintf(cmd_update_user,"delete subscriber -msisdn %s ", msisdn);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
					CT_DISCONNECT_ACCOUNT, NT_HSS, node_name[NT_HSS], msisdn,
					cmd_update_user);
		break;
	}		

	rc = dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

	return rc;
}



int dba_crm_read_bundle_info(int sn)
{
	char sql[8192];
	int rc;
	char bundle_info[20][1024];

	sprintf(sql, "SELECT i.OFR_INST_ID, a.ACCT_ID, p.SERVICE_NBR, i.EXP_DATEFROM tb_prd_ofr_detail_inst_551 i, \
		tb_prd_prd_inst_551 p, tb_prd_ofr o, tb_bil_acct_551 a WHERE o.OFR_TYPE_ID = 3 \
		AND p.PRD_INST_STAS_ID='1001' AND p.PRD_INST_ID = i.OFR_DETAIL_INST_REF_ID  \
		AND a.ACCT_ID = p.ACCT_ID  AND a.PREPAY = 1  AND o.OFR_ID = i.OFR_ID  AND datediff( i.EXP_DATE,  CURRENT_TIME)=3 limit %d,1", sn);

	rc = dba_db_sql_execute_crm(sql, bundle_info, 1, 10, 0, NULL);
	
	if(rc != 0)
	{
		sprintf(sql, "INSERT into tb_sync_msg SET msg_type=1, state=1, acct_id=%d, ofr_inst_id=%d" ,atoi(bundle_info[1]), atoi(bundle_info[0]));
		dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
	}
	
	return rc;

}

int dba_crm_scan_bundle_thread(unsigned int now_sec)
{
	static int scan_limit_counter = 1;
	int state = 0, timer = 0;
	int	rc=0;
	char result[256];
	
	state = crm_scan_bundle_thread.state;
	
	switch(state)
	{
		case CRM_SCAN_ST_NULL:
			if((now_sec-28800)%86400<20)//08:00:20 AM start to scan
			{
				crm_scan_bundle_thread.state = CRM_SCAN_ST_START;
				scan_limit_counter = 1;
			}
		break;
		case CRM_SCAN_ST_START:
			rc = dba_crm_read_bundle_info(scan_limit_counter++);
			if(rc == 0)
			{
				crm_scan_bundle_thread.state = CRM_SCAN_ST_END;
				crm_scan_bundle_thread.state_timer = 0;
			}
		break;
		case CRM_SCAN_ST_END:
			if(crm_scan_bundle_thread.state_timer++>3600)//1hour later
			{
				crm_scan_bundle_thread.state = CRM_SCAN_ST_NULL;			
			}
		break;
	}

	return 1;
}


int dba_get_user_rent(char *msisdn)
{
	char sql[8192];
	int rc, rent_charge=0 ;
	char rent[1][1024];
	
	sprintf(sql, "SELECT f.prd_id, f.FEE_VALUE, 2 AS product_type, p.SERVICE_NBR, \
		s.PRD_INST_ID, p.PRD_INST_ID MAIN_PRD_INST_ID, s.CRT_DATE \
		FROM tb_prd_sub_prd_inst_551 s, tb_prd_prd_once_fee f, \
		tb_prd_prd_inst_551 p WHERE s.MAIN_PRD_INST_ID = p.PRD_INST_ID  \
		AND f.PRD_ID = s.OFR_ID  AND f.FEE_TYPE = 5 \
		AND p.SERVICE_NBR = '%s'  AND p.PRD_INST_STAS_ID = '1001'  AND s.EXP_DATE > CURRENT_TIMESTAMP AND s.EFF_DATE <= CURRENT_TIMESTAMP", msisdn);

	rc = dba_db_sql_execute_ocs(sql, rent, 1, 1, 0, NULL);
	if(rent[0][0] != 0x00)
		rent_charge = atoi(rent[0]);

	return rent_charge;
}


 
int   subs_provisioning_update_result(char *sql)
{

	int rc;
 
	rc = dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

	return rc;
}

int  mobile_provisioning_command_process() 
{
	char sql[8192];
	char sub_data[32][ARRAY_SIZE];
	int rc;
	int mobile_type = 0;
	int oper_type =0 ;
	
	sprintf(sql, 
		"select  PRE_ID, OPER_TYPE, IMSI, SERVICE_NBR, KI, OPC, CUST_ID, ACCT_ID, PRD_INST_ID, MOBILE_TYPE, VMS_FLAG, BIRTH_DATE, BALANCE, \
		BALANCE_EXP_DATE,  OFR_ID, EXP_DATE, CUG_ID from tb_sync_mobile  where STATE=1 limit 1");
	
	rc = dba_db_sql_execute_crm(sql, sub_data, 1, 32,0, NULL);
	if(rc == 0)
		return 0;
	
	//if(rc != 0)
	{
		oper_type = atoi(sub_data[1]);

		if(0)
		{
			sprintf(sql, "update tb_sync_mobile set STATE=3, UPDATE_DATE=NOW() where PRE_ID=%d", atoi(sub_data[0]));//treating to avoid re-create
			subs_provisioning_update_result(sql);
			
			if(1)//mobile
			{

				pthread_t tid;
	       		pthread_create(&tid, NULL, (void *)crm_prov_hlr_auc_vms_account, sub_data);
			
				//crm_prov_hlr_auc_vms_account(sub_data);
				
				mobile_type = atoi(sub_data[9]);//0=postpaid, 1=prepaid
				if(mobile_type == 1)
				{
					crm_prov_mobile_prepaid_account( sub_data);
				}
			}
		}
		else
		{
			char	cmd_create_user[256];

			oper_type = atoi(sub_data[1]);
			

			if(oper_type == 2)//replace sim card
			{//delete old imsi from HSS first , then add new IMSI in HSS/AUC..
				char sql_tmp[1024];
				char cmd_delete_user[1024];
				char old_imsi_info[3][ARRAY_SIZE];
				int   rc0=0;
				
				sprintf(sql_tmp, 
					"select  PRE_ID, IMSI, PRD_INST_ID from tb_sync_mobile WHERE SERVICE_NBR='%s' and STATE>1 ORDER BY PRE_ID DESC limit 1", sub_data[3]);
				rc0 = dba_db_sql_execute_crm(sql_tmp, old_imsi_info, 1, 32,2, NULL);
				if(rc0==0) return 0;
				
				//if(rc0 !=0)
				{
					sprintf(cmd_delete_user,"delete subscriber -imsi %s ", old_imsi_info[1]);
					sprintf(sql_tmp, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
							CT_DISCONNECT_ACCOUNT, NT_HSS, node_name[NT_HSS], old_imsi_info[2],
							cmd_delete_user);
					dba_db_sql_execute_crm(sql_tmp, NULL, 0, 0, 0, NULL);

					sprintf(cmd_delete_user,"delete aucSubscriber -imsi %s ", old_imsi_info[1]);
					sprintf(sql_tmp, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
						CT_DISCONNECT_ACCOUNT, NT_AUC, node_name[NT_AUC], old_imsi_info[2],
						cmd_delete_user);
					dba_db_sql_execute_crm(sql_tmp, NULL, 0, 0, 0, NULL);

					sprintf(sql_tmp, "UPDATE tb_sync_mobile set STATE=6 where PRE_ID=%d", atoi(old_imsi_info[0]));
					dba_db_sql_execute_crm(sql_tmp, NULL, 0, 0, 0, NULL);
					
				}
					//create
				sprintf(cmd_create_user,"create subscriber -imsi %s -msisdn %s -eps_user_tpl def_eps -impi %s@ims.mnc010.mcc505.3gppnetwork.org",
						sub_data[2], sub_data[3], sub_data[2]);
				sprintf(sql_tmp, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
						CT_CREATE_SUB, NT_HSS, node_name[NT_HSS], sub_data[8],
						cmd_create_user);
				dba_db_sql_execute_crm(sql_tmp, NULL, 0, 0, 0, NULL);
					
				if(sub_data[5][0] == 0)
					sprintf(cmd_create_user,"create aucSubscriber -imsi %s -ki %s", sub_data[2], sub_data[4]);
				else
					sprintf(cmd_create_user,"create aucSubscriber -imsi %s -ki %s -opc %s", sub_data[2], sub_data[4], sub_data[5]);
				sprintf(sql_tmp, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1,  result=0, timestamp=NOW()",
					CT_CREATE_SUB, NT_AUC, node_name[NT_AUC], sub_data[8],
					cmd_create_user);	
				dba_db_sql_execute_crm(sql_tmp, NULL, 0, 0, 0, NULL);
				
				goto prov_done;
			}
			
			sprintf(cmd_create_user,"create subscriber -imsi %s -msisdn %s -eps_user_tpl def_eps -impi %s@ims.mnc010.mcc505.3gppnetwork.org",
				sub_data[2], sub_data[3], sub_data[2]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_CREATE_SUB, NT_HSS, node_name[NT_HSS], sub_data[8],
				cmd_create_user);
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			
			if(sub_data[5][0] == 0)
				sprintf(cmd_create_user,"create aucSubscriber -imsi %s -ki %s", sub_data[2], sub_data[4]);
			else
				sprintf(cmd_create_user,"create aucSubscriber -imsi %s -ki %s -opc %s", sub_data[2], sub_data[4], sub_data[5]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1,  result=0, timestamp=NOW()",
				CT_CREATE_SUB, NT_AUC, node_name[NT_AUC], sub_data[8],
				cmd_create_user);	
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

			if(atoi(sub_data[10]))//VMS enabled
			{
				sprintf(cmd_create_user,"create vmsSubscriber -msisdn %s", sub_data[3]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
					CT_CREATE_SUB, NT_VMS, node_name[NT_VMS], sub_data[8],
					cmd_create_user);	
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			}	

			mobile_type = atoi(sub_data[9]);//0=postpaid, 1=prepaid
			if(mobile_type == 1)
			{
				char rent_data[2][ARRAY_SIZE];
				char user_class_data[2][ARRAY_SIZE];
				int		cug_id = 0, user_class=1;//local user default
				int		rc1=0;
				
				sprintf(sql,  "select OFR_ID from tb_prd_sub_prd_inst_551 where MAIN_PRD_INST_ID=%d limit 1", atoi(sub_data[8]));
				rc1 = dba_db_sql_execute_crm(sql, rent_data, 1, 1,2, NULL);

				if(rc1 != 0)
				{
					if(rent_data[0][0] !=0x00)
						cug_id = atoi(rent_data[0]);
				}

				sprintf(sql,  "select OFR_LEVEL from tb_prd_ofr where OFR_ID=%d limit 1", atoi(sub_data[14]));
				rc1 = dba_db_sql_execute_crm(sql, user_class_data, 1, 1,2, NULL);

				if(rc1 != 0)
				{
					if(user_class_data[0][0] !=0x00)
						user_class = atoi(user_class_data[0]);
				}
	
				crm_prov_mobile_prepaid_account(sub_data, cug_id, user_class);

				sprintf(cmd_create_user,"create ppsSubscriber -msisdn %s -PRE_ID %ld", sub_data[3]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=2, result=1, cause='000:Command Successful', timestamp=NOW()",
					CT_CREATE_SUB, NT_OCS, node_name[NT_OCS], sub_data[8],
					cmd_create_user);	
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);				
			}

		}
	}

prov_done:
		sprintf(sql, "update tb_sync_mobile set STATE=2, UPDATE_DATE=NOW() where PRE_ID=%d", atoi(sub_data[0]));			
		subs_provisioning_update_result(sql);

	return rc;
}

int  mobile_delete_handle(int account_id) 
{
	char sql[8192];
	char sub_data[32][ARRAY_SIZE];
	int rc;
	int mobile_type = 0;
	
	sprintf(sql, 
		"select  PRE_ID, OPER_TYPE, IMSI, SERVICE_NBR, KI, OPC, CUST_ID, STATE, PRD_INST_ID, MOBILE_TYPE, VMS_FLAG, BIRTH_DATE, BALANCE, \
		BALANCE_EXP_DATE,  OFR_ID, EXP_DATE, CUG_ID from tb_sync_mobile  where ACCT_ID=%d ORDER BY PRE_ID DESC limit 1", account_id);
	
	rc = dba_db_sql_execute_ocs(sql, sub_data, 1, 32,0, NULL);
	if(rc == 0)
		return 0;
	
	if(1)
	{
		char	cmd_delete_user[256];
			
		sprintf(cmd_delete_user,"delete subscriber -imsi %s ", sub_data[2]);
		sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_HSS, node_name[NT_HSS], sub_data[8],
				cmd_delete_user);
		dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
			
		sprintf(cmd_delete_user,"delete aucSubscriber -imsi %s", sub_data[2]);
		sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1,  result=0, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_AUC, node_name[NT_AUC], sub_data[8],
				cmd_delete_user);	
		dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);

		//if(atoi(sub_data[10]))//VMS enabled
		if(1)//VMS enabled
		{
			sprintf(cmd_delete_user,"delete vmsSubscriber -msisdn %s", sub_data[3]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_VMS, node_name[NT_VMS], sub_data[8],
				cmd_delete_user);	
			dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);
		}	

		mobile_type = atoi(sub_data[9]);//0=postpaid, 1=prepaid
		if(0)//no smcli connection to OCS
		{
			if(mobile_type == 1)
			{
				sprintf(cmd_delete_user,"delete ppsSubscriber -msisdn %s", sub_data[3]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=2, result=1, cause='000:Command Successful', timestamp=NOW()",
					CT_DISCONNECT_ACCOUNT, NT_OCS, node_name[NT_OCS], sub_data[8],
					cmd_delete_user);	
				dba_db_sql_execute_ocs(sql, NULL, 0, 0, 0, NULL);				
			}
		}
	}

	return rc;
}

void update_plan_quotation_when_on_boarding(int owner_id)
{
	char sql[8192];
	char cur_time[10];
	
	int rate = 100;
	int max_days = 31;
	
	GetCurrentTime(cur_time);
	switch(cur_time[1])
	{
		case 4:
		case 6:
		case 9:
		case 11:
			max_days =  30;
		break;
		case 2:
			if(cur_time[0]%4 == 0)
			{
				max_days = 29;
			}
			else
			{
				max_days = 28;
			}
		break;
	}
	
	rate = ((max_days - cur_time[2])+1) * 100 / max_days;
	
	sprintf(sql, "update ratable_history set FREE_VALUE=FREE_VALUE*%d/100 where OWNER_ID=%d", rate, owner_id);
	
	subs_provisioning_update_result(sql);

}

void pstn_provisioning_command_process() 
{
	char sql[8192];
	char cmd_create_user[256];
	char sub_data[32][ARRAY_SIZE], sub_data1[32][ARRAY_SIZE];
	int rc;
	
	sprintf(sql, 
		"select  POST_ID, SERVICE_NBR, CUST_NAME, VMS_FLAG, PRD_INST_ID,OFR_TYPE, ORDER_ID from tb_sync_pstn where STATE=1 limit 1");
	
	rc = dba_db_sql_execute_crm(sql, sub_data, 1, 32, 2, NULL);
	if(rc != 0)
	{
		int krone_pair_id=0, ssentry=1125;
		char res_data[32][ARRAY_SIZE];
		
		sprintf(sql, "update tb_sync_pstn set STATE=3, UPDATE_DATE=NOW() where POST_ID=%d", atoi(sub_data[0]));
		subs_provisioning_update_result(sql);

		sprintf(sql, 
			"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
			TB_PRD_RES_INST_DETAIL_551 B, \
			TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=%d \
			AND A.ORDER_ID=%s",  pstn_get_krone_pair_resource_id(), sub_data[6]);
		rc = dba_db_sql_execute_crm(sql, res_data, 1, 1, 2, NULL);
		if(rc != 0)
			krone_pair_id = atoi(res_data[0]);

		sprintf(sql, 
			"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
			TB_PRD_RES_INST_DETAIL_551 B, \
			TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=%d \
			AND A.ORDER_ID=%s",  pstn_get_ssentry_resource_id(), sub_data[6]);
		rc = dba_db_sql_execute_crm(sql, res_data, 1, 1, 2, NULL);
		if(rc != 0)
			ssentry = atoi(res_data[0]);

		if(1)
		{
			sprintf(cmd_create_user,"create pstn user isdn=%s, krone_pair_id=%d, ssentry=%d, user_name=%s", sub_data[1], krone_pair_id, ssentry, sub_data[2]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
					CT_CREATE_SUB, NT_PSTN, node_name[NT_PSTN], sub_data[4],
				cmd_create_user);	
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			if(0)
			{
				sprintf(cmd_create_user,"create pstn user isdn=%s, krone_pair_id=%d, ssentry=%d, user_name=%s", sub_data[1], krone_pair_id, ssentry, sub_data[2]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
						CT_CREATE_SUB, NT_MSAN, node_name[NT_MSAN], sub_data[4],
					cmd_create_user);	
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			}
		}
		
		//crm_prov_pstn_account(sub_data, ssentry,  krone_pair_id);

		if(atoi(sub_data[3]))//VMS enabled
		{
			sprintf(cmd_create_user,"create vmsSubscriber -msisdn %s", sub_data[1]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
					CT_CREATE_SUB, NT_VMS, node_name[NT_VMS], sub_data[4],
				cmd_create_user);	
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
		
			//crm_create_vms_account(sub_data[1]);
		}

		//update_plan_quotation_when_on_boarding(atoi(sub_data[4]));
	}
	else
	{
			sprintf(sql, "select cmd_id,  acct_id  from tb_pstn_disc_cmd where acct_status=0 limit 1");
			
			rc = dba_db_sql_execute_crm(sql, sub_data, 1, 32, 2, NULL);
			if(rc != 0)
			{
				sprintf(sql, "update tb_pstn_disc_cmd set  acct_status=1 where cmd_id='%s'", sub_data[0]);
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

				sprintf(sql,"select SERVICE_NBR from tb_prd_prd_inst_551 WHERE acct_id='%s'", sub_data[1]);
				rc = dba_db_sql_execute_crm(sql, sub_data1, 1, 32, 2, NULL);
				if(rc != 0)
				{	
					long account_id = atol(sub_data[1]);
					crm_delete_subscriber_profile(sub_data1[0], 2, account_id);
				}
			}
	}
}

int pstn_provisioning_node_process()
{
	char sql[8192];
	char data[32][ARRAY_SIZE];
	int rc;
	int krone_pair_id, ss_entry, pre_id;
	char isdn[32], user_name[64],state[64],buf[32];
	char *ptr=NULL, *ptr1=NULL;
	int cmd_type;
	
	sprintf(sql, "select  id, command, node_type,cmd_type  from tb_sync_cn_cmd where state=1 and node_type=%d limit 1", NT_PSTN);	
	rc = dba_db_sql_execute_crm(sql, data, 1, 32, 0, NULL);
	if(rc != 0)
	{
		pre_id = atoi(data[0]);
	
		sprintf(sql, "update tb_sync_cn_cmd set state=3, timestamp=NOW() where id=%d", pre_id);
		dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
		cmd_type = atoi(data[3]);
		if(1)//if(cmd_type == CT_CREATE_SUB || cmd_type == CT_UPDATE_SUB)
		{
		//command=create pstn user isdn=%s, krone_pair_id=%d, ss_entry=%d, user_name=

			    ptr = strstr(data[1], "isdn=");
	                  if(ptr != NULL)
                        {
                                ptr1 = strstr(data[1], ", krone_pair_id=");
                                if(ptr1 != NULL)
                                {
                                        memset(isdn, 0x00, 32);
                                        memcpy(isdn,ptr+5, ptr1-ptr-5);
                                }

                        }
                        if(ptr1 != NULL)
                        {
                                ptr = ptr1;
                                ptr1 = strstr(data[1], ", ssentry=");
                                if(ptr1 != NULL)
                                {
                                        memset(buf, 0x00, 32);
                                        memcpy(buf,ptr+16, ptr1-ptr-16);
                                        krone_pair_id = atoi(buf);
                                }
                        }
                        if(ptr1 != NULL)
                        {
                                ptr = ptr1;
					if(cmd_type == CT_CREATE_SUB)
                          	      ptr1 = strstr(data[1], ", user_name=");
					else 
                          	      ptr1 = strstr(data[1], ", state=");

					if(ptr1 != NULL)
                                {
                                        memset(buf, 0x00, 32);
                                        memcpy(buf,ptr+10, ptr1-ptr-10);
                                        ss_entry = atoi(buf);
                                }
                        }
						
                        if(ptr1 != NULL)
                        {
					if(cmd_type == CT_CREATE_SUB)
						strcpy(state, ptr1+12);
					else
		                         strcpy(state, ptr1+8);                  	
                        }
			

			sprintf(data[1], "%s", isdn);
			if(cmd_type == CT_CREATE_SUB)
				sprintf(data[2], "%s", user_name);
			else
				sprintf(data[2], "%s", state);
			
			sprintf(data[4], "%d", pre_id);

			if(cmd_type == CT_CREATE_SUB)	
				sprintf(data[5], "%d", 1); //create
			else
				sprintf(data[5], "%d", 2); //update
			
			crm_prov_pstn_account(data, ss_entry, krone_pair_id);
			
		}
	}
	else
	{
		sprintf(sql, "select  id, command  from tb_sync_cn_cmd where result=3 and node_type=%d limit 1", NT_PSTN);
		rc = dba_db_sql_execute_crm(sql, data, 1, 32, 0, NULL);
		if(rc != 0)
		{
			pre_id = atoi(data[0]);

			sprintf(sql, "update tb_sync_cn_cmd set state=3, result=4, timestamp=NOW() where id=%d", pre_id);
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
				
		}
	
	}
	
	return rc;

}

static char crm_user_prov_cmd[10][16]={"None","Create","Replace","Suspend","Disable","Resume","Disconnect"};

int pstn_service_control_process()
{
	char sql[8192];
	char cmd_update_user[1024];
	char data[32][ARRAY_SIZE], sync_data[32][ARRAY_SIZE], tmp_data[32][ARRAY_SIZE];
	int rc;
	int node_type, pre_id;
	int cmd_type;
	int krone_pair_id, ssentry;
		
	sprintf(sql, "select  id, service_nbr, type, SERVICE_TYPE from tb_service_control where state=1 limit 1");	
	rc = dba_db_sql_execute_crm(sql, data, 1, 32, 0, NULL);
	if(rc != 0)
	{
		pre_id = atoi(data[0]);
	
		sprintf(sql, "update tb_service_control set state=2, update_date=NOW() where id=%d", pre_id);
		dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

		node_type = atoi(data[3]);
		if(node_type == 1)//ADSL , do nothing
			return 1;
		
		cmd_type = atoi(data[2]);
		if(cmd_type>=10)
			return 0;
		
		if(node_type == 2)//PSTN
		{			

			sprintf(sql, "select  PRD_INST_ID, CUST_ID from tb_sync_pstn where SERVICE_NBR='%s' ORDER BY POST_ID DESC limit 1", data[1]);	
			rc = dba_db_sql_execute_crm(sql, sync_data, 1, 32, 0, NULL);
			if(rc == 0)//can't find on-boarding info
				return 0;

			sprintf(sql, 
				"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
				TB_PRD_RES_INST_DETAIL_551 B, \
				TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=%d \
				AND A.CUST_ID=%s",  pstn_get_krone_pair_resource_id(), sync_data[1]);
			rc = dba_db_sql_execute_crm(sql, tmp_data, 1, 1, 2, NULL);
			if(rc != 0)
				krone_pair_id = atoi(tmp_data[0]);

			sprintf(sql, 
				"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
				TB_PRD_RES_INST_DETAIL_551 B, \
				TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=%d \
				AND A.CUST_ID=%s",  pstn_get_ssentry_resource_id(), sync_data[1]);
			rc = dba_db_sql_execute_crm(sql, tmp_data, 1, 1, 2, NULL);
			if(rc != 0)
				ssentry = atoi(tmp_data[0]);
			
			sprintf(cmd_update_user,"update pstn user isdn=%s, krone_pair_id=%d, ssentry=%d, state=%s", data[1], krone_pair_id, ssentry, crm_user_prov_cmd[cmd_type]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				cmd_type, NT_PSTN, node_name[NT_PSTN], sync_data[0],
				cmd_update_user);
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
		}
		
	}
	
	return rc;
}

void pstn_update_provisiong_result(int result, int post_id, char *cause)
{

	char sql[1024];
	
	if(result == 1)
	{
			sprintf(sql, "update tb_sync_cn_cmd set state=2, result=5, timestamp=NOW() where id=%d",   post_id);
	}
	else
	{
			sprintf(sql, "update tb_sync_cn_cmd set state=2, result=6, cause='%s', timestamp=NOW() where id=%d", cause, post_id);
	}
	
	subs_provisioning_update_result(sql);

}

void adsl_provisioning_command_process()
{

	char sql[8192];
	char sub_data[32][ARRAY_SIZE];
	int rc;
	
	sprintf(sql, 
		"select  POST_ID, PRD_INST_ID  from tb_sync_adsl where STATE=1 limit 1");
	
	rc = dba_db_sql_execute_crm(sql, sub_data, 1, 32, 2, NULL);
	if(rc != 0)
	{
			sprintf(sql, "update tb_sync_adsl set STATE=2, UPDATE_DATE=NOW() where POST_ID=%d", atoi(sub_data[0]));
			subs_provisioning_update_result(sql);

			//update_plan_quotation_when_on_boarding(atoi(sub_data[1]));
			//CRM will assign plan benefits when do on-boarding
			
	}
}

int mobile_provisioning_node_process(int nt_type)
{
	char sql[8192];
	char data[32][ARRAY_SIZE];
	int rc;
	
	sprintf(sql, "select  id, command  from tb_sync_cn_cmd where state=1 and node_type=%d limit 1", nt_type);
	
	rc = dba_db_sql_execute_crm(sql, data, 1, 32, 0, NULL);
	if(rc != 0)
	{
		sprintf(sql, "update tb_sync_cn_cmd set state=3, timestamp=NOW() where id=%d", atoi(data[0]));
		dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			
		smcli_send_command(data[1]);
		crm_smcli_thread.resend = 0;
		crm_smcli_thread.key_id = atoi(data[0]);
	}
	else
	{
		sprintf(sql, "select  id, command  from tb_sync_cn_cmd where result=3 and node_type=%d limit 1", nt_type);
	
		rc = dba_db_sql_execute_crm(sql, data, 1, 32, 0, NULL);
		if(rc != 0)
		{
			sprintf(sql, "update tb_sync_cn_cmd set result=4, timestamp=NOW() where id=%d", atoi(data[0]));
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
				
			smcli_send_command(data[1]);
			crm_smcli_thread.resend = 1;
			crm_smcli_thread.key_id = atoi(data[0]);
		}
	
	}
	
	return rc;

}

void mobile_provisioning_node_update_result(int nt_type, int result, char *result_des)
{
	char sql[8192];

	if(crm_smcli_thread.resend == 0)
	{
		sprintf(sql, "update tb_sync_cn_cmd set state=2, result=%d, cause='%s', timestamp=NOW() where id=%d", result, result_des, crm_smcli_thread.key_id);
	}
	else
	{
		if(result ==  1)
			sprintf(sql, "update tb_sync_cn_cmd set state=2, result=5, timestamp=NOW() where id=%d",   crm_smcli_thread.key_id);
		else
			sprintf(sql, "update tb_sync_cn_cmd set state=2, result=6, cause='%s', timestamp=NOW() where id=%d", result_des, crm_smcli_thread.key_id);
			
	}
	dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

}

void crm_delete_subscriber_profile(char *service_num, int account_type, int account_id)
{//account_type=1/2/3 mobile/pstn/adsl
	char sql[8192];
	char sub_data[32][ARRAY_SIZE],sub_data1[32][ARRAY_SIZE],sub_data2[32][ARRAY_SIZE];
	int rc;
	int mobile_type = 0;
	int vms_flag =0 ;
	char	cmd_delete_user[1024];

	if(account_type == 1)//mobile
	{
		sprintf(sql, 
			"select  PRE_ID, IMSI, SERVICE_NBR, MOBILE_TYPE, VMS_FLAG, PRD_INST_ID from tb_sync_mobile  where  ACCT_ID=%d limit 1", account_id);
		//service number -> product_inst_id
		rc = dba_db_sql_execute_crm(sql, sub_data, 1, 32,2, NULL);
		if(rc != 0)
		{

			mobile_type = atoi(sub_data[3]);
			vms_flag = atoi(sub_data[4]);
			
			sprintf(cmd_delete_user,"delete subscriber -imsi %s ", sub_data[1]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_HSS, node_name[NT_HSS], sub_data[5],
				cmd_delete_user);
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

			sprintf(cmd_delete_user,"delete aucSubscriber -imsi %s ", sub_data[1]);
			sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_AUC, node_name[NT_AUC], sub_data[5],
				cmd_delete_user);
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);

			if(mobile_type == 1)//prepaid to OCS
			{
				sprintf(cmd_delete_user,"delete ppsSubscriber -msisdn %s ", sub_data[2]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=2, result=1, cause='0000:Command successful', timestamp=NOW()",
					CT_DISCONNECT_ACCOUNT, NT_OCS, node_name[NT_OCS], sub_data[5],
					cmd_delete_user);
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			}
			
			if(vms_flag == 1)//VMS enabled to VMS
			{
				sprintf(cmd_delete_user,"delete vmsSubscriber -msisdn %s ", sub_data[2]);
				sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=0, timestamp=NOW()",
					CT_DISCONNECT_ACCOUNT, NT_VMS, node_name[NT_VMS], sub_data[5],
					cmd_delete_user);
				dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
			}
		
		}
	}
	else if(account_type == 2)//pstn
	{
		int krone_pair_id=0,ssentry=0;
		

		sprintf(sql, 
			"select  ORDER_ID from tb_sync_pstn where SERVICE_NBR='%s'  ORDER by ORDER_ID desc limit 1", service_num);	
		rc = dba_db_sql_execute_crm(sql, sub_data, 1, 1, 2, NULL);

		sprintf(sql, 
			"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
			TB_PRD_RES_INST_DETAIL_551 B, \
			TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=1046 \
			AND A.ORDER_ID=%s", sub_data[0]);
		rc = dba_db_sql_execute_crm(sql, sub_data1, 1, 1, 2, NULL);
		if(rc != 0)
			krone_pair_id = atoi(sub_data1[0]);

		sprintf(sql, 
			"SELECT C.RESOURCE_EXAM_NUM FROM TB_PRD_RES_INST_551 A, \
			TB_PRD_RES_INST_DETAIL_551 B, \
			TB_RESOURCE_EXAM C WHERE A.RES_INST_ID=B.RES_INST_ID AND B.RESOURCE_EXAM_ID=C.RESOURCE_EXAM_ID AND C.RESOURCE_ID=%d \
			AND A.ORDER_ID=%s",  pstn_get_ssentry_resource_id(), sub_data[0]);
		rc = dba_db_sql_execute_crm(sql, sub_data2, 1, 1, 2, NULL);
		if(rc != 0)
			ssentry = atoi(sub_data2[0]);

		sprintf(cmd_delete_user,"delete pstn user isdn=%s, krone_pair_id=%d, ssentry=%d, state=Deleted, account_id=%d ", service_num,krone_pair_id, ssentry,account_id);
		sprintf(sql, "INSERT INTO tb_sync_cn_cmd SET cmd_type=%d, node_type=%d, node_name='%s', srv_num='%s', command='%s', state=1, result=1, timestamp=NOW()",
				CT_DISCONNECT_ACCOUNT, NT_PSTN, node_name[NT_PSTN], service_num,
				cmd_delete_user);
		dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
		
		//crm_prov_pstn_account_delete(service_num, krone_pair_id);

	}
}

void set_smcli_state(int new_state)
{
	//printf("smcli thread state %d->%d\r\n", crm_smcli_thread.state, new_state);
	
	crm_smcli_thread.state = new_state;
	crm_smcli_thread.state_timer = 0;
}

static int smcli_timer = 16; //8seconds
int   user_prov_smcli_thread()
{
	int state = 0, timer = 0;
	int	rc=0;
	char result[256];
	
	state = crm_smcli_thread.state;
	timer = crm_smcli_thread.state_timer ++;
	
	switch(state)
	{
		case CRM_CLI_ST_READ_CMD:
			rc = mobile_provisioning_command_process();
			if(rc !=0 )
			{
				set_smcli_state(CRM_CLI_ST_HSS_PROV);
			}
			else
			{
				set_smcli_state(CRM_CLI_ST_HSS_PROV);
			}
		break;
		case CRM_CLI_ST_HSS_PROV:
			rc = mobile_provisioning_node_process(NT_HSS);
			if(rc == 0)
			{
				set_smcli_state(CRM_CLI_ST_AUC_PROV);
			}
			else
			{
				set_smcli_state(CRM_CLI_ST_HSS_PROV_RES);
			}
		break;
		case CRM_CLI_ST_HSS_PROV_RES:
			if(timer>=smcli_timer)
			{
				mobile_provisioning_node_update_result(NT_HSS, 2, "timeout"); //failed
				set_smcli_state(CRM_CLI_ST_AUC_PROV);

			}
			else
			{
				rc = smcli_get_result(result);
				if(rc != 0)
				{
					mobile_provisioning_node_update_result(NT_HSS, rc, result);
					set_smcli_state(CRM_CLI_ST_AUC_PROV);				
				}

				
			}
		break;
		case CRM_CLI_ST_AUC_PROV:
			rc = mobile_provisioning_node_process(NT_AUC);
			if(rc == 0)
			{
				set_smcli_state(CRM_CLI_ST_OCS_PROV);
			}
			else
			{
				set_smcli_state(CRM_CLI_ST_AUC_PROV_RES);
			}			
		break;
		case CRM_CLI_ST_AUC_PROV_RES:
			if(timer>=smcli_timer)
			{
				mobile_provisioning_node_update_result(NT_AUC, 2, "timeout"); //failed
				set_smcli_state(CRM_CLI_ST_OCS_PROV);

			}
			else
			{
				rc = smcli_get_result(result);
				if(rc != 0)
				{
					mobile_provisioning_node_update_result(NT_AUC, rc, result);
					set_smcli_state(CRM_CLI_ST_OCS_PROV);
				}

			}
		break;
		case CRM_CLI_ST_OCS_PROV:
			rc = mobile_provisioning_node_process(NT_OCS);
			if(rc == 0)
			{
				set_smcli_state(CRM_CLI_ST_VMS_PROV);
			}
			else
			{
				set_smcli_state(CRM_CLI_ST_OCS_PROV_RES);
			}			
		break;
		case CRM_CLI_ST_OCS_PROV_RES:
			if(timer>=smcli_timer)//
			{
				mobile_provisioning_node_update_result(NT_OCS, 2, "timeout"); //failed
				set_smcli_state(CRM_CLI_ST_VMS_PROV);

			}
			else
			{
				rc = smcli_get_result(result);
				if(rc != 0)
				{
					mobile_provisioning_node_update_result(NT_OCS, rc, result);
					set_smcli_state(CRM_CLI_ST_VMS_PROV);
				}

			}

		break;		
		case CRM_CLI_ST_VMS_PROV:
			rc = mobile_provisioning_node_process(NT_VMS);
			if(rc == 0)
			{
				set_smcli_state(CRM_CLI_ST_READ_CMD);
			}
			else
			{
				set_smcli_state(CRM_CLI_ST_VMS_PROV_RES);
			}			
		break;
		case CRM_CLI_ST_VMS_PROV_RES:
			if(timer>=smcli_timer)
			{
				mobile_provisioning_node_update_result(NT_VMS, 2, "timeout"); //failed
				set_smcli_state(CRM_CLI_ST_READ_CMD);

			}
			else
			{
				rc = smcli_get_result(result);
				if(rc != 0)
				{
					mobile_provisioning_node_update_result(NT_VMS, rc, result);
					set_smcli_state(CRM_CLI_ST_READ_CMD);
				}

			}
		break;		
		case CRM_CLI_ST_PCRF_PROV:
		break;
	}
}

int	subs_provisioning_command_process() 
{	

	if(0)
		mobile_provisioning_command_process();
	else
		user_prov_smcli_thread();
	
	pstn_provisioning_command_process();
	pstn_provisioning_node_process();
	pstn_service_control_process();
	
	adsl_provisioning_command_process();

	return 1;
}

/*
+-------------------+--------------+------+-----+---------+--------------------------------+
| Field             | Type         | Null | Key | Default | Extra                          |
+-------------------+--------------+------+-----+---------+--------------------------------+
| sms_alert_id      | int(11)      | NO   | PRI | NULL    | auto_increment                 |
| sms_called_number | varchar(20)  | YES  |     | NULL    |                                |
| sms_content       | varchar(500) | YES  |     | NULL    |                                |
| sms_dcs           | int(1)       | YES  |     | 0       |                                |
| sms_state         | int(1)       | YES  |     | 1       |                                |
| sms_validity      | datetime(6)  | YES  |     | NULL    |                                |
| sms_crt_date      | datetime(6)  | YES  |     | NULL    | on update CURRENT_TIMESTAMP(6) |
| sms_module        | int(1)       | YES  |     | NULL    |                                |
+-------------------+--------------+------+-----+---------+--------------------------------+
*/
void sms_notification_handle()
{
	char sql[8192];
	char sms_data[8][ARRAY_SIZE];
	int rc;
	
	sprintf(sql, "select  *  from tb_sms_info where sms_state=1 limit 1");
	
	rc = dba_db_sql_execute_crm(sql, sms_data, 1, 32, 0, NULL);
	if(rc != 0)
	{
			sprintf(sql, "update tb_sms_info set sms_state=2 where sms_alert_id=%d", atoi(sms_data[0]));
			dba_db_sql_execute_crm(sql, NULL, 0, 0, 0, NULL);
	
			rest_crm_send_sms_notification(sms_data);
	}
	return 1;
}

int sms_notification_process()
{
	sms_notification_handle();
	
	return 1;
}

