def datetimestr():
    return datetime.now().strftime('%Y%m%d-%H%M%S')+'>>>'   

def update_dcData(req):
    
    log_path='apps/dc/l10n_reports'
    update_dcData_log=open(log_path+'updateDcdataLog.log','w+')

    sql_getProjectIDs='select a.project_id from'                        ' (select count(*) num,project_id from dc_data where lableName=%s group by project_id) a,'                        ' (select count(*) num ,project_id from management_project_target_lang group by project_id) b'                        ' where a.project_id=b.project_id and a.num!=b.num order by project_id'
    
    sql_getAllProjectIDs='select project_id from management_project'
    
    sql_getLanguageSections='select b.name from management_project_target_lang a,management_l10nlanguage b'                             ' where a.project_id=%s and a.l10nlanguage_id=b.id and b.id!="1"'
                            
    sql_getRecords='select id, languageSection,value from dc_data where lableName =%s and project_id=%s and important="1"'
    
    sql_addRecordByID='insert into dc_data(lableName,languageSection,type,value,project_id,task_id,'                 'important,unit,settlement,workload) '                 'select lableName,languageSection,type,value,project_id,task_id,important,unit,settlement,workload '                 'from dc_data where id=%s' 
    sql_updateLgs='update dc_data set languageSection=%s where id=%s'
    
    sql_getLableNames='select lableName from dc_data where lableName like "%%_all" group by lableName'
    
    update_dcData_log.write(datetimestr()+'sql_getLableNames'+'>>>'+sql_getLableNames+'\n')
    update_dcData_log.write(datetimestr()+'sql_getRecords'+'>>>'+sql_getRecords+'\n')
    update_dcData_log.write(datetimestr()+'sql_getProjectIDs'+'>>>'+sql_getProjectIDs+'\n')
    update_dcData_log.write(datetimestr()+'sql_getLanguageSections'+'>>>'+sql_getLanguageSections+'\n')    
    update_dcData_log.write(datetimestr()+'sql_addRecordByID'+'>>>'+sql_addRecordByID+'\n')    
    update_dcData_log.write(datetimestr()+'sql_updateLgs'+'>>>'+sql_updateLgs+'\n')    
    context=Context({'msg':'Success'})
    resp=render_to_response("report/clean_data.html", context, 
                              context_instance=RequestContext(req))    
    cursor=connection.cursor()
    try:
        cursor.execute(sql_getLableNames)
        lableNames=cursor.fetchall()
    except Exception,e:
        update_dcData_log.write(datetimestr()+'execute sql_getLableNames error '+str(e)+'\n')
        context=Context({'msg':'Error'})
        return render_to_response("report/clean_data.html", context, 
                              context_instance=RequestContext(req)) 
    for lableName in lableNames:
        try:
            cursor.execute(sql_getProjectIDs,[lableName[0]])
            projectIDs=cursor.fetchall()
        except Exception,e:
            update_dcData_log.write(datetimestr()+'execute sql_getProjectIDs error '+str(e)+'\n')
            context=Context({'msg':'Error'})
            return render_to_response("report/clean_data.html", context, 
                              context_instance=RequestContext(req)) 
        for pid in projectIDs:
            try:
                cursor.execute(sql_getRecords,[lableName[0],str(pid[0])])
                records=cursor.fetchall()
                cursor.execute(sql_getLanguageSections,[str(pid[0])])
                languageSections=cursor.fetchall()
            except Exception,e:
                update_dcData_log.write(datetimestr()+'execute sql_getRecords or sql_getLanguageSections error '+str(e)+'\n')
                context=Context({'msg':'Error'})
                return render_to_response("report/clean_data.html", context, 
                              context_instance=RequestContext(req)) 
            values,lgs=[],[]
            baseValue=str(records[0][2])
            baseID=str(records[0][0])
            for item in records:
                lgs.append(str(item[1]))
                values.append(str(item[2]))
                if baseValue!=str(item[2]):
                    baseValue='false'
            targetLgs=[str(item[0]) for item in languageSections]
            if len(lgs)<1 or len(targetLgs)<1:
                baseValue=='false'
            if 'all' not in lgs:
                try:
                    cursor.execute(sql_addRecordByID,[baseID])
                    cursor.execute(sql_updateLgs,['all',baseID])
                    transaction.commit_unless_managed()
                except Exception,e:
                    update_dcData_log.write(datetimestr()+'execute sql_addRecordByID or sql_updateLgs error (all)'+str(e)+'\n')
                    context=Context({'msg':'Error'})
                    return render_to_response("report/clean_data.html", context, 
                                  context_instance=RequestContext(req)) 
                        
                    update_dcData_log.write(datetimestr()+"all record is add into dc_data,the lableName and projectID were "+str(lableName[0])+'-'+str(pid[0])+'\n')
            
            if baseValue=='false':
                update_dcData_log.write(datetimestr()+"please update this record mutually,the lableName and projectID were "+str(lableName[0])+'-'+str(pid[0])+'\n')
            else:
                if len(lgs)>len(targetLgs):
                    update_dcData_log.write(datetimestr()+"the lableName languageSection length is longer than target numbers lableName and projectID were "+str(lableName[0])+'-'+str(pid[0])+'\n')
                else:
                    for lg in targetLgs:
                        if lg not in lgs:
                            try:
                                cursor.execute(sql_addRecordByID,[baseID])
                                cursor.execute(sql_updateLgs,[lg,baseID])
                                transaction.commit_unless_managed()
                            except Exception,e:
                                update_dcData_log.write(datetimestr()+'execute sql_addRecordByID or sql_updateLgs error (lg)'+str(e)+'\n')
                                context=Context({'msg':'Error'})
                                return render_to_response("report/clean_data.html", context, 
                                  context_instance=RequestContext(req)) 
                                
                            update_dcData_log.write(datetimestr()+lg+" record is add into dc_data,the lableName and projectID were "+str(lableName[0])+'-'+str(pid[0])+'\n')
                        
                    
    update_dcData_log.close() 
            
            
    return  resp