#!/usr/bin/python
import xlrd
import csv
import datetime
import os
#----------------------------------------------------------------------------------+
# FUNCTION TO GET DATA FROM XLS FILE
#----------------------------------------------------------------------------------+
def cellval(cell, datemode):
    if cell.ctype == xlrd.XL_CELL_DATE:
        datetuple = xlrd.xldate_as_tuple(cell.value,datemode)
        if datetuple[3:] == (0,0,0):
            return datetime.date(datetuple[0],datetuple[1],datetuple[2])
        return datetime.date(datetuple[0],datetuple[1],datetuple[2],datetuple[3],datetuple[4],datetuple[5])
        if cell.ctype == xlrd.XL_CELL_EMPTY:
            return None
        if cell.ctype == xlrd.XL_CELL_BOOLEAN:
            return cell.value == 1
        return cell.value
#----------------------------------------------------------------------------------+
# FLOW 
#----------------------------------------------------------------------------------+
def create_csv_file_flow(cs_code_id):
    file_name = '/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/flow/'+cs_code_id+'.csv'
    with open(file_name,'wb') as csvfile:
        csvfileWriter = csv.writer(csvfile)
    csvfile.close()
#----------------------------------------------------------------------------------+
def write_to_flow_file(cs_code_id,ml_dt,ml_flow):
    file_name = '/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/flow/'+cs_code_id+'.csv'
    with open(file_name,'a') as csvfile:
        csvfileWriter = csv.writer(csvfile)
        ml_data = [ml_dt,ml_flow]
        csvfileWriter.writerow(ml_data)
    csvfile.close()
#----------------------------------------------------------------------------------+
def create_csv_file_stage(cs_code_id):
    file_name = '/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/stage/'+cs_code_id+'.csv'
    with open(file_name,'wb') as csvfile:
        csvfileWriter = csv.writer(csvfile)
    csvfile.close()
#----------------------------------------------------------------------------------+
def write_to_stage_file(cs_code_id,ml_dt,ml_stage):
    file_name = '/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/stage/'+cs_code_id+'.csv'
    with open(file_name,'a') as csvfile:
        csvfileWriter = csv.writer(csvfile)
        ml_data = [ml_dt,ml_stage]
        csvfileWriter.writerow(ml_data)
    csvfile.close()
#----------------------------------------------------------------------------------+
cross_section_list = ['1220698','1220165','1219064','1218713','1218016','1217008','1215987','1214992','1214087','1212668','1211536','1209193','1207009','1204989','1203249','1201209','1199804','1195710','1192688','1189457','1186742','1183635','1181801','1179469','1173629','1171419','1169968','1168237','1167233','1166705','1166027','1165778','1165340','1164184','1162497','1160860','1159837','1158408','1156528','1154694','1152892','1151131','1149794','1147954','1146096','1144367','1142258','1140337','1138928','1137286','1135717','1133771','1131423','1127713','1124061','1121158','1117157','1112287','1107702','1104448','1101920','1099162','1095711','1092800','1089907','1086841','1083888','1081723','1079469','1077015','1074097','1070377','1067958','1066145','1063546','1061063','1058008','1050584','1049224','1043919','1036598','1032268','1027668','1022432','1015596','1010611','1003221','997484','990491','985692','980464','973797','965743','961050','956022','951925','948198','944043','939963','936374','933406','929691','925970','922089','918888','915455','912566','909242','907343','906852','906189','905454','904730','903618','901048','899145','897068','894433','891577','888921','885765','881780','879257','876210','873704','868943','864026','859687','856875','853530','851329','848466','845446','842459','837936','835164','833529','833038','832151','828481','827306','826585','825687','824250','820482','818636','817569','816315','814826','813652','812321','811042','810073','809120','808044','806833','801523','799880','796759','793046','791031','789260','787855','784963','779686','761809','759226','756421','753564','751302','749364','747751','745699','743921','742504','741499','740544','739231','738029','737187','736043','735252','734484','733260','732560','731468','730592','729523','728612','727589','726919','726199','725378','723747','721501','720321','718948','716603','714736','713165','711987','708454','704005','702155','700116','698862','697727','696256','693163','689682','679070','675456','667378','664339','661585','658870','653769','650558','642264','638271','634686','632871','630178','628487','627733','626766','625822','624518','623228','621907','620497','609958','608191','604124','601544','598285','596412','594410','591443','589004','584365','579580','574111','570225','567471','562120','554108','551809','549704','546957','543775','541895','539132','537725','535303','529999','527348','524923','522010','514686','513562','506884','504539','502775','500565','497911','495921','493084','490364','488270','485953','484700','482341','479423','477601','473092','470766','468451','465070','462555','458305','456746','454590','452402','450640','448812','446584','444514','440792','439772','435550','430588','426763','416845','409150','406359','400615','398253','395874','393588','390717','382229','378034','374730','369801','366252','361428','357779','353038','346049','341144','337872','329116','320428','310190','306802','301615','297430','293925','286716','284554','281642','279486','277144','275395','274797','274180','272609','271837','270689','269931','268490','266766','265695','264099','261897','260254','258024','255410','253684','251126','247733','242585','234797','230847','221486','218815','216421','211790','209974','207050','203254','200056','192559','186457','182451','179355','177976','173885','165929','163416','161127','155954','146342','142681','135539','132777','128073','125300','117218','111490','107770','103850','97004','91287','87252','82954','79058','75922','71837','67355','63827','56051','42776','38937','36046','33969','49955','49107','46897','45583','43100','40785','36411','34234','31161','28600','25582','22576','20641','18648','15639','11773','7658','5048','3094','1298']
#----------------------------------------------------------------------------------+
# FLOW
#----------------------------------------------------------------------------------+
for i in range(len(cross_section_list)):
    create_csv_file_flow(cross_section_list[i])
    xl_file = xlrd.open_workbook('/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/flow/'+cross_section_list[i]+'.xls')
    # GET FIRST WORKSHEET
    wsheet = xl_file.sheet_by_index(0)
    for rx in range(wsheet.nrows):
        if rx > 6:
            dt = cellval(wsheet.cell(rx,1),xl_file.datemode)
            flow = round(wsheet.cell(rx,2).value,1)
            new_dt = dt.strftime('%Y-%m-%d')
            write_to_flow_file(cross_section_list[i],new_dt,round((flow*35.3147),2))
    xl_file.release_resources()
#----------------------------------------------------------------------------------+
# STAGE
#----------------------------------------------------------------------------------+
for i in range(len(cross_section_list)):
    create_csv_file_stage(cross_section_list[i])
    xl_file = xlrd.open_workbook('/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/stage/'+cross_section_list[i]+'.xls')
    # GET FIRST WORKSHEET
    wsheet = xl_file.sheet_by_index(0)
    for rx in range(wsheet.nrows):
        if rx > 6:
            dt = cellval(wsheet.cell(rx,1),xl_file.datemode)
            flow = round(wsheet.cell(rx,2).value,1)
            new_dt = dt.strftime('%Y-%m-%d')
            write_to_stage_file(cross_section_list[i],new_dt,round((flow*3.28084),2))
    xl_file.release_resources()
#----------------------------------------------------------------------------------+
#barrage_list=['1220698','903618','733260','274180','49955']
#barrage_name=['Taunsa','Guddu','Sukkur','Kotri','Punjnad']
barrage_name=['Taunsa','Guddu','Sukkur','Kotri','Punjnad']
#----------------------------------------------------------------------------------+
# BARRAGE STAGE
#----------------------------------------------------------------------------------+
for i in range(len(barrage_name)):
    create_csv_file_stage(barrage_name[i])
    xl_file = xlrd.open_workbook('/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/stage/'+barrage_name[i]+'.xls')
    wsheet = xl_file.sheet_by_index(0)
    for rx in range(wsheet.nrows):
        if rx > 6:
            dt = cellval(wsheet.cell(rx,1),xl_file.datemode)
            flow = round(wsheet.cell(rx,2).value,1)
            new_dt = dt.strftime('%Y-%m-%d')
            write_to_stage_file(barrage_name[i],new_dt,round((flow*3.28084),2))
    xl_file.release_resources()
#----------------------------------------------------------------------------------+
# BARRAGE FLOW
#----------------------------------------------------------------------------------+
for i in range(len(barrage_name)):
    create_csv_file_flow(barrage_name[i])
    xl_file = xlrd.open_workbook('/home/sindh/www-htdocs/pakistan_dss/DATA/ML/SCRIPTS/Kot_Alam_Model/cross_section_output/flow/'+cross_section_list[i]+'.xls')
    # GET FIRST WORKSHEET
    wsheet = xl_file.sheet_by_index(0)
    for rx in range(wsheet.nrows):
        if rx > 6:
            dt = cellval(wsheet.cell(rx,1),xl_file.datemode)
            flow = round(wsheet.cell(rx,2).value,1)
            new_dt = dt.strftime('%Y-%m-%d')
            write_to_flow_file(barrage_name[i],new_dt,round((flow*35.3147),2))
    xl_file.release_resources()
#----------------------------------------------------------------------------------+