/***************encrypt hcase data*************/
/* title, id, yy, case_judge*/
UPDATE `hcase` SET `black_id`=AES_ENCRYPT(`black_id`,'a2f3e71'), `black_yy`=AES_ENCRYPT(`black_yy`,'a2f3e71'), `case_judge`=AES_ENCRYPT(`case_judge`,'a2f3e71'), `doc_running`=AES_ENCRYPT(`doc_running`,'a2f3e71'), `lrun_id`=AES_ENCRYPT(`lrun_id`,'a2f3e71') WHERE 1;

/* update barcode */
UPDATE hcase hc
INNER JOIN hmt_title ht ON hc.case_type = ht.case_type
AND hc.black_title = ht.title
AND ht.title_flag =1
SET hc.black_barcode=CONCAT( hc.court_running, hc.case_type, ht.title_barcode, LPAD( AES_DECRYPT( hc.black_id,  'a2f3e71' ) , 5,  '0' ) , AES_DECRYPT( hc.black_yy,  'a2f3e71' ) ) ;

/********************************************/


/****************Update work flow *************/

UPDATE `hcase` hc
INNER JOIN hmt_judge hj ON AES_DECRYPT( hc.case_judge,  'a2f3e71' ) = hj.judge_id
INNER JOIN hcase_work_flow hf ON hc.run_id = hf.run_id
AND hf.flow_id =6
SET hf.respons_id=hj.judge_id, hf.respons_name=hj.judge_name;

UPDATE `hcase_work_flow` SET `round`=1, `times`=1 WHERE `create_user` LIKE 'transfer%';

UPDATE hcase_judge SET judge_id=AES_ENCRYPT(judge_id ,'a2f3e71') WHERE DATE( create_date ) =  '2015-08-15';

/*SELECT hp.point_count, hf.points_this_month
FROM  `hcase_work_flow` hf
INNER JOIN  `hmt_casepoint` hp ON hf.point_id = hp.point_id
WHERE hf.points_this_month IS NULL 
AND hf.point_id IS NOT NULL;*/

UPDATE `hcase_work_flow` hf
INNER JOIN  `hmt_casepoint` hp ON hf.point_id = hp.point_id
SET hf.points_this_month=hp.point_count
WHERE hf.points_this_month IS NULL 
AND hf.point_id IS NOT NULL;

/********************************************/


/************Update request barcode ***********/

UPDATE hrequest hr
INNER JOIN hmt_request_title ht ON hr.req_run_title = ht.req_title
SET req_barcode=CONCAT( ht.req_title_barcode, hr.req_run_id, hr.req_run_yy );

/********************************************/


/************Update judgement_times************/

/*SELECT AES_DECRYPT( hc.case_judge,  'a2f3e71' ) 
FROM hcase hc
INNER JOIN hjudgement hj ON hc.run_id = hj.run_id
WHERE hj.case_judge IS NULL */

UPDATE hcase hc
INNER JOIN hjudgement hj ON hc.run_id = hj.run_id
SET hj.case_judge=AES_DECRYPT( hc.case_judge,  'a2f3e71' ) 
WHERE hj.case_judge IS NULL;

UPDATE hjudgement_times SET result_red=AES_ENCRYPT(`result_red`,'a2f3e71');


/***************·Ó·ÕËÅѧÊØ´******************/

/*SELECT hh.lcourt_doc_running, ht.send_doc_running
FROM hcase_historical hh
INNER JOIN hjudgement hj ON hh.run_id=hj.run_id
INNER JOIN hjudgement_times ht ON hj.red_running=ht.red_running
WHERE 1 AND hh.event_type=4;*/



UPDATE hcase_historical hh
INNER JOIN hdocument hd ON hh.lcourt_doc_running = hd.doc_running
INNER JOIN hjudgement hj ON hh.run_id = hj.run_id
INNER JOIN hjudgement_times ht ON hj.red_running = ht.red_running
SET ht.send_doc_running=hd.doc_running, ht.send_read_date=hd.send_rcv_date
WHERE 1 
AND hh.event_type =4;

SELECT hd.doc_running
FROM hcase_historical hh
INNER JOIN hcase hc ON hh.lrun_id=AES_DECRYPT(hc.lrun_id, 'a2f3e71')
INNER JOIN hdocument hd ON hh.lcourt_doc_running = hd.doc_running
INNER JOIN hjudgement hj ON hc.run_id = hj.run_id
INNER JOIN hjudgement_times ht ON hj.red_running = ht.red_running
WHERE 1 
AND hh.event_type =4;

UPDATE hcase_historical hh
INNER JOIN hcase hc ON AES_ENCRYPT( hh.lrun_id,  'a2f3e71' ) = hc.lrun_id
INNER JOIN hdocument hd ON hh.lcourt_doc_running = hd.doc_running
INNER JOIN hjudgement hj ON hc.run_id = hj.run_id
INNER JOIN hjudgement_times ht ON hj.red_running = ht.red_running
SET ht.send_doc_running=hd.doc_running, ht.send_read_date=hd.send_rcv_date
WHERE 1 
AND hh.event_type =4
AND ht.send_doc_running IS NULL;


UPDATE hjudgement_times ht 
INNER JOIN hdocument hd ON ht.send_doc_running= hd.doc_running
SET ht.send_read_date=hd.send_rcv_date
WHERE 1;