Problem with uploading data into tables

[Anita Headley]

I have an upload that reads a csv file and puts data into a temporary table, validates the fields and then loads the data into the two (master/detail) regular data tables.

The problem: Sometimes (not consistent), it duplicates the detail data. I have not been able to track down why this is happening.

I use the built in file upload on the latest version of Alpha Anywhere with a SQL Server database. After the file is loaded, it calls the following xbasic function to put it into a table for validation. After the data is validated, it allows the user to click a button to load or cancel for any data that does not have field validation errors. If the user chooses to overwrite data that already exists, the program first deletes the existing data, then loads the new data into the tables.

TOCAS Upload screen.png


function xb_save_file_to_ticket_upload_table as C (e as p)

dim cn as SQL::Connection
dim ins as SQL::InsertStatement
dim args as sql::arguments
dim err_args as sql::arguments
dim rs as SQL::ResultSet
dim flagresult as l

dim ls_sqlerr as C
dim ls_sql as C
dim ls_insert as C
dim ls_values as C
dim newdata as C
dim ls_filename as C
dim ls_mime as C
dim ls_filedata as C
dim stringarray as C
dim dataarray[0] as C
dim recarray[0] as C
dim ls_record as C
dim ls_rec_list as C
dim ls_today as C
dim ls_chkdate as C
dim ls_user as C
dim ls_usertribe as C
dim ls_dealer as C
dim ls_tribe as C
dim ls_indian_id as C
dim ls_species as C
dim ls_gear as C
dim ls_disp as C
dim ls_area as C
dim ls_species_bad as C
dim ls_gear_bad as C
dim ls_disp_bad as C
dim ls_area_bad as C
dim ls_dealer_bad as C
dim ls_ticket_bad as C
dim ls_date_bad as C
dim ls_filter as C
dim ls_table as C
dim ls_js as C
dim ls_err_insert as C
dim ls_err_values as C
dim ls_err_msg as C
dim ls_ticket as C
dim ls_fatal_err as C
dim ls_wdfw_insert as C
dim ls_wdfw_values as C
dim ls_cnts1 as C
dim ls_cnts2 as C

dim li_status as N
dim li_cnt as N
dim li_fldcnt as N
dim numrows as N
dim idx2 as N
dim numrows2 as N
dim idx as N
dim numreadyload as N
dim numexisting as N
dim numexistwrongtribe as N
dim numwrongtribe as N
dim numduplicates as N
dim numfuturedate as N
dim numbaddisp as N
dim numbadgear as N
dim numbadspecies as N
dim numbadarea as N
dim numbaddealer as N
dim numbadticketnum as N
dim totalrecs as N
dim li_fileid as N
dim li_err_cnt as N
dim li_numlbs as N
dim li_numfish as N

dim ldt_date as T
dim ldt_rpt_date as T

'debug(1)
ls_today = CDATE(date())

' data not in TICKET_UPLOAD yet, just in temp storage (e.fileArray[1] which is a datastring that includes CR-LF between data rows and commas between fields)
' create args, parse data into args, create insert SQL statement
li_fileid = get_next_file_id_num()
ls_filename = e.fileArray[1].fileName
ls_mime = e.fileArray[1].file.contentType
ls_filedata = e.fileArray[1].Data
newdata = e.fileArray[1].Data
stringarray = filter_string(newdata,",",chr(13) + chr(10),.F.)

dataarray.initialize(newdata) ' data now in array of records

numrows = dataarray.size()

flagresult = cn.Open("::Name::tocas")
debug(1)
if flagresult then
' check if file has been uploaded before
' clear upload table
ls_user = SESSION.UPLOAD_USER
ls_usertribe = SESSION.USER_TRIBE

args.Add("userid", ls_user)
args.Add("ls_file", ls_filename)
ls_sql = "SELECT COUNT(*) FROM tocas_dba.uploaded_files where upload_file_name = :ls_file;"
flagresult = cn.Execute(ls_sql,args)
rs = cn.ResultSet
li_cnt = rs.data(1)
cn.FreeResult()

if li_cnt > 0 then
ls_js = "alert('This file has already been uploaded; Change name to upload again. ');"
else

args.Set("userid", ls_user)
args.Add("tribe", ls_usertribe)
'debug(1)
flagresult = cn.execute("exec tocas_dba.delete_old_import_data :userid, :tribe ", args)
' flagresult = cn.execute(ls_sql)

' set argument variables
args.Add("tick_id")
args.Add("dealer")
args.Add("ticketnum")
args.Add("landingdate")
args.Add("disp")
args.Add("fishertype")
args.Add("tribe")
args.Add("gear")
args.Add("daysfished")
args.Add("data_srce")
args.Add("comments")
args.Add("catch_area")
args.Add("subarea")
args.Add("species")
args.Add("numfish")
args.Add("numlbs")
args.Add("lbstype")
args.Add("landing_cnt")
args.Add("rpt_date")
args.Add("indian_id")
args.Add("boat")
args.Add("buyer")
args.Add("grade")
args.Add("price_amt")
args.Add("create_date")
args.Add("update_status")

ls_insert = "INSERT INTO TICKET_PRE_UPLOAD (TICKET_UPLOAD_ID, DEALER_ID, FISH_TICKET_NUM, LANDING_DATE, DISPOSITION_CODE, FISHER_TYPE_CODE, TRIBE_NUM, GEAR_CODE, DAYS_FISHED_CNT, DATA_SOURCE_CODE, COMMENTS, SPECIES_CODE, RPTD_CNT, RPTD_LBS_QTY, RPTD_LBS_TYPE, RPTD_PRICE_AMT, LANDING_CNT, CREATE_DATE, CREATE_USERID, UPDATE_STATUS, CATCH_AREA, SUBAREA, REPORT_DATE, INDIAN_ID, BOAT_NUMBER, BUYER_ID, GRADE, UPLOAD_USER)"
ls_values = "VALUES(:tick_id, :dealer, :ticketnum, :landingdate, :disp, :fishertype, :tribe, :gear, :daysfished, :data_srce, :comments, :species, :numfish, :numlbs, :lbstype, :price_amt, :landing_cnt, :create_date, :userid, :update_status, :catch_area, :subarea, :rpt_date, :indian_id, :boat, :buyer, :grade, :userid);"

ls_wdfw_insert = "INSERT INTO TICKET_PRE_UPLOAD (TICKET_UPLOAD_ID, DEALER_ID, FISH_TICKET_NUM, LANDING_DATE, DISPOSITION_CODE, FISHER_TYPE_CODE, TRIBE_NUM, GEAR_CODE, DAYS_FISHED_CNT, DATA_SOURCE_CODE, COMMENTS, CATCH_AREA, SUBAREA, SPECIES_CODE, RPTD_CNT, RPTD_LBS_QTY, RPTD_LBS_TYPE, LANDING_CNT, CREATE_DATE, CREATE_USERID, UPDATE_STATUS, UPLOAD_USER)"
ls_wdfw_values = "VALUES(:tick_id, :dealer, :ticketnum, :landingdate, :disp, :fishertype, :tribe, :gear, :daysfished, :data_srce, :comments, :catch_area, :subarea, :species, :numfish, :numlbs, :lbstype, :landing_cnt, :create_date, :userid, :update_status, :userid);"

err_args.Add("id")
err_args.Add("log")
err_args.Add("upload_date")
err_args.Add("user")
err_args.Add("errmsg")

ls_err_insert = "INSERT INTO tocas_dba.UPLOAD_ERROR (ID, LOG, TIME, USERNAME, TESTVALUE)"
ls_err_values = "VALUES(:id, :log, :upload_date, :user, :errmsg);"

li_err_cnt = 0

for idx = 2 to numrows
ls_species_bad = "n"
ls_gear_bad = "n"
ls_disp_bad = "n"
ls_area_bad = "n"
ls_dealer_bad = "n"
ls_ticket_bad = "n"
ls_date_bad = "n"
ls_fatal_err = "n"
ls_err_msg = ""

args.Set("tick_id", idx)

ls_record = dataarray[idx] ' get 1 row of data
ls_rec_list = comma_to_crlf(ls_record) ' convert to list of fields
recarray.initialize(ls_rec_list) ' record now in array of fields
li_fldcnt = recarray.size()

' dealer
ls_dealer = ALLTRIM(recarray[1])
if (len(ls_dealer) <= 0) then
ls_dealer_bad = "y"
ls_fatal_err = "y"
ls_err_msg = "dealer missing; "
elseif (.NOT. isnumber(ls_dealer)) then
ls_dealer_bad = "y"
ls_fatal_err = "y"
ls_err_msg = "invalid dealer or dealer missing; "
elseif (len(ls_dealer) < 4) then
ls_dealer = padl(ls_dealer,4,"0")
end if
'debug(1)
' ticket number
ls_ticket = ALLTRIM(recarray[2])
if (len(ls_ticket) <= 0) then
ls_ticket_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "ticket# missing; "
elseif (len(ls_ticket) > 8) then
ls_ticket_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "ticket# too long; "
elseif (len(ls_ticket) < 7) then
ls_ticket_bad = "y"
ls_err_msg = ls_err_msg + "ticket# too short; "
end if
args.Set("dealer", ls_dealer)
args.Set("ticketnum", ls_ticket)
'ldt_date = convert_type(recarray[3], "T")

' date landed
if (isdate(recarray[3])) then
args.Set("landingdate", recarray[3])
else
ls_date_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "Invalid LandingDate; "
end if

' disposition
ls_disp = alltrim(recarray[4])
if (len(ls_disp) < 1) then
ls_disp_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "Missing Disposition; "
elseif (len(ls_disp) > 4) then
ls_disp_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "Disposition too long; "
end if

args.Set("userid", ls_user)
args.Set("usertribe", ls_usertribe)

'debug(1) ' tribe
ls_tribe = ALLTRIM(recarray[6])
if (len(ls_tribe) < 1) then
'' ls_tribe = "99"
ls_tribe = ls_usertribe
elseif (len(ls_tribe) = 1) then
ls_tribe = "0" + ls_tribe
end if
' ls_table = "tocas_dba.tribe_lookup"
' ls_filter = "tribe_code = '" + ls_tribe + "'"
' li_cnt = sql_lookup(cn,ls_table, ls_filter, "count(*)" )
' if li_cnt = 0 then
' ls_tribe_bad = "y"
'' ls_err_msg = ls_err_msg + "Invalid Tribe; "
' end if

' catch_area
ls_area = ALLTRIM(recarray[11])
if (len(ls_area) < 1) then
ls_area_bad = "y"
ls_fatal_err = "y"
ls_err_msg = ls_err_msg + "Missing Catch Area; "
else
if (substr(ls_area,1,1) = "0") then
ls_area = substr(ls_area, 2)
end if
if len(ls_area) < 3 then
if len(ls_area) < 2 then
ls_area = " " + ls_area
else
if .not. isnumber(substr(ls_area,2,1)) then
ls_area = " " + ls_area
end if
end if
end if
end if

' species
ls_species = alltrim(recarray[13])
if (len(ls_species) < 3) then
ls_species = padl(ls_species,3,"0")
end if
li_numlbs = recarray[15]
li_numfish = recarray[14]

if ((ls_species >= "521") .AND. (ls_species <= "525")) then
if ((.not. isnull(STR(li_numlbs))) .and. (isnull(STR(li_numfish)) .or. (li_numfish = 0))) then
li_numfish = li_numlbs
li_numlbs = null_value()
end if
end if

if (ls_fatal_err = "n") then

args.Set("disp", ls_disp)
args.Set("fishertype", recarray[5])
args.Set("tribe", ls_tribe)
args.Set("gear", recarray[7])
args.Set("daysfished", recarray[8])
args.Set("data_srce", recarray[9])
args.Set("comments", recarray[10])
args.Set("catch_area", ls_area)
args.Set("subarea", recarray[12])
args.Set("species", ls_species)
args.Set("numfish", li_numfish)
args.Set("numlbs", li_numlbs)
args.Set("lbstype", recarray[16])

if (li_fldcnt > 16) then ' dealer not null
args.Set("landing_cnt", recarray[17])
end if ' if fldcnt > 16

if (li_fldcnt > 17) then ' tribal import
' reporting date
if (isdate(recarray[18])) then
args.Set("rpt_date", recarray[18])
else
ldt_rpt_date = date()
args.Set("rpt_date", ldt_rpt_date)
end if ' reporting date null
ls_indian_id = alltrim(recarray[19])
if (len(ls_indian_id) < 5) then
ls_indian_id = padl(ls_indian_id, 5, "0")
end if
args.Set("indian_id", ls_indian_id)
args.Set("boat", recarray[20])
args.Set("buyer", recarray[21])
args.Set("grade", recarray[22])
args.Set("price_amt", recarray[23])
end if ' if fldcnt > 17
end if ' if fatal error

args.Set("create_date", date())
args.Set("userid", ls_user)

ls_chkdate = CDATE(CTOD(recarray[3]))
if ls_chkdate > ls_today then
li_status = 5 ' future date
elseif ls_disp_bad = "y" then
li_status = 6 ' bad disposition
elseif ls_gear_bad = "y" then
li_status = 7 ' bad gear
elseif ls_species_bad = "y" then
li_status = 8 ' bad species
elseif ls_area_bad = "y" then
li_status = 9 ' bad area
elseif ls_dealer_bad = "y" then
li_status = 10 ' bad dealer
elseif ls_ticket_bad = "y" then
li_status = 11 ' bad ticket #
else
li_status = 0
end if

args.Set("update_status", li_status)
if (ls_fatal_err = "y") then
' debug(1)
li_err_cnt = li_err_cnt + 1
err_args.Set("id",li_err_cnt)
err_args.Set("log", ls_record)
err_args.Set("upload_date", date())
err_args.Set("user", ls_user)
err_args.Set("errmsg", ls_err_msg)

ls_sql = ls_err_insert + ls_err_values
flagresult = cn.Execute(ls_sql,err_args)
else
if (li_fldcnt > 17) then
ls_sql = ls_insert + ls_values
else
ls_sql = ls_wdfw_insert + ls_wdfw_values
end if
flagresult = cn.Execute(ls_sql,args)

end if ' if fatal error

ls_sqlerr = cn.CallResult.text
numrows2 = cn.CallResult.RowsAffected

next

args.Set("userid", ls_user)
args.Set("usertribe", ls_usertribe)

flagresult = cn.execute("exec tocas_dba.tocas_import_processing :userid , :usertribe", args)

' ls_js = xbasic_refresh_all_counts(e)

totalrecs = (numrows - 1)

numreadyload = xbasic_count_records(0)
numexisting = xbasic_count_records(1)
numexistwrongtribe = xbasic_count_records(2)
numduplicates = xbasic_count_records(3)
numwrongtribe = xbasic_count_records(4)
numfuturedate = xbasic_count_records(5)

numbaddisp = xbasic_count_records(6)
numbadgear = xbasic_count_records(7)
numbadspecies = xbasic_count_records(8)
numbadarea = xbasic_count_records(9)
numbaddealer = xbasic_count_records(10)
numbadticketnum = xbasic_count_records(11)

' pass numbers back to javascript funtion
ls_cnts1 = totalrecs + "," + li_err_cnt + "," + numreadyload + "," + numexisting + "," + numexistwrongtribe + "," + numwrongtribe + "," + numduplicates + ","
ls_cnts2 = numfuturedate + "," + numbaddisp + "," + numbadgear + "," + numbadspecies + "," + numbadarea + "," + numbaddealer + "," + numbadticketnum
ls_js = "window.parent.js_count_tickets(" + li_fileid + ",'" + ls_user + "','" + ls_filename + "','" + ls_mime + "'," + ls_cnts1 + ls_cnts2 + ");"

end if ' if file not already uploaded
end if ' if data put in temp load area

cn.Close()
e.javascript = ls_js

end function


'////////////////////////////////////////////////////////////////////////////////

function xb_load_tickets_data(e as p)

write_data_to_tables(0)

xb_load_tickets_data = "alert('Data Loaded');{dialog.EmbeddedGrid_READYTOLOADGRID}.refresh();"
end function

'//////////////////////////////////////////////////////////////////////////////////

function write_data_to_tables as c (fa_status as N)
dim cn as SQL::Connection
dim args as SQL::Arguments
dim rs as SQL::ResultSet
dim flagresult as l
dim ls_sql as C
dim ls_user as C
dim ls_det_field_list as C
dim ls_master_field_list as C
dim ls_select as C
dim ls_from as C
dim ls_where as C
dim ls_det_var_list as C
dim ls_master_var_list as C
dim ls_ticket as C
dim ls_tickyear as C
dim ls_old_ticket as C
dim ll_nwifc_num as N
dim ll_tickrow as N
dim numrows as N
dim idx as N
dim ll_curr_year as N
dim ll_prev_year as N
dim li_mgmt_week as N
dim li_year as N

args.Add("ls_ticknum")
args.Add("ldt_landing_date")

flagresult = cn.Open("::Name::tocas")
'debug(1)
'll_nwifc_num = get_next_nwifc_num()
ls_user = SESSION.UPLOAD_USER
ls_usertribe = SESSION.USER_TRIBE
'ls_user = "pkairis"
'ls_usertribe = "29"

args.Add("tbl_id", "T")
args.Add("ls_upload_user",ls_user)
args.Add("li_update_status",fa_status)
args.Add("reconciled", "n")
ls_old_ticket = ""
ll_prev_year = 0
ls_det_field_list = "(nwifc_ticket_id, catch_area_code, species_code, rptd_cnt, rptd_lbs_qty, rptd_lbs_type, landing_cnt, create_date, rptd_price_amt, create_userid, tribal_subunit_code, disposition_code, gear_code, grade)"
ls_master_field_list = "(report_date, dealer_license_id, fish_ticket_num, landing_date, fisher_type_code, tribe_num, days_fished_cnt, region_code, data_source_code, comments, create_date, create_userid, indian_id, boat_number, buyer_id, tbl_id, catch_year, ticket_year, reconciled_flag)"

ls_select = " SELECT DISTINCT report_date, dealer_id, fish_ticket_num, landing_date, fisher_type_code, tribe_num, days_fished_cnt, region_code, data_source_code, comments, create_date, create_userid, indian_id, boat_number, buyer_id, :tbl_id, YEAR(landing_date), (fish_ticket_num + convert(varchar(4),YEAR(landing_date))), :reconciled "
ls_from = " FROM tocas_dba.TICKET_UPLOAD WHERE update_status = :li_update_status and tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :ls_upload_user);"
'debug(1)
ls_sql = "INSERT INTO tocas_dba.NWIFC_FISH_TICKET " + ls_master_field_list + ls_select + ls_from
flagresult = cn.Execute(ls_sql,args)
if flagresult then

ls_select = " SELECT n.nwifc_ticket_id, u.catch_area, u.species_code, u.rptd_cnt, u.rptd_lbs_qty, u.rptd_lbs_type, u.landing_cnt, u.create_date, u.rptd_price_amt, u.create_userid, u.subarea, u.disposition_code, u.gear_code, u.grade"
ls_from = " FROM tocas_dba.ticket_upload u, tocas_dba.NWIFC_FISH_TICKET n "
ls_where = " where u.update_status = :li_update_status and n.fish_ticket_num = u.fish_ticket_num and n.landing_date = u.landing_date and u.tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :ls_upload_user);"
ls_sql = "INSERT INTO tocas_dba.NWIFC_FISH_TICKET_DETAIL " + ls_det_field_list + ls_select + ls_from + ls_where
args.Set("ls_upload_user",ls_user)
args.Set("li_update_status",fa_status)
flagresult = cn.Execute(ls_sql,args)
cn.FreeResult()
cn.Close()
end if

delete_from_upload_table(fa_status)

end function


function clear_existing_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(1)
' debug(1)
ls_js = xbasic_refresh_all_counts(e)
' clear_existing_grid = "{dialog.EmbeddedGrid_EXISTTICKETSGRID}.refresh(); {dialog.object}.setValue('existing',0,true); " + ls_js
clear_existing_grid = "{dialog.EmbeddedGrid_EXISTTICKETSGRID}.refresh(); {dialog.object}.setValue('existing',0,true); "
end function


function clear_duplicates_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(3)

ls_js = xbasic_refresh_all_counts(e)
clear_duplicates_grid = "{dialog.EmbeddedGrid_DUPLICATETICKETSGRID}.refresh(); {dialog.object}.setValue('duplicates',0,true); "
end function

function clear_wrongtribe_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(4)

ls_js = xbasic_refresh_all_counts(e)
clear_wrongtribe_grid = "{dialog.EmbeddedGrid_WRONGTRIBEGRID}.refresh(); {dialog.object}.setValue('wrongtribe',0,true); "
end function

function clear_existingwrongtribe_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(2)

ls_js = xbasic_refresh_all_counts(e)
clear_existingwrongtribe_grid = "{dialog.EmbeddedGrid_EXISTINGWRONGTRIBEGRID}.refresh(); {dialog.object}.setValue('existingwrongtribe',0,true); "
end function

function clear_futuredate_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(5)

ls_js = xbasic_refresh_all_counts(e)
clear_futuredate_grid = "{dialog.EmbeddedGrid_FUTUREDATEGRID}.refresh(); {dialog.object}.setValue('futuredate',0,true); "
end function

function clear_baddisp_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(6)

ls_js = xbasic_refresh_all_counts(e)
clear_baddisp_grid = "{dialog.EmbeddedGrid_BADDISPGRID}.refresh(); {dialog.object}.setValue('baddisp',0,true); "
end function

function clear_badgear_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(7)

ls_js = xbasic_refresh_all_counts(e)
clear_badgear_grid = "{dialog.EmbeddedGrid_BADGEARGRID}.refresh(); {dialog.object}.setValue('badgear',0,true); "
end function

function clear_badspecies_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(8)

ls_js = xbasic_refresh_all_counts(e)
clear_badspecies_grid = "{dialog.EmbeddedGrid_BADSPECIESGRID}.refresh(); {dialog.object}.setValue('badspecies',0,true); "
end function

function clear_badarea_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(9)

ls_js = xbasic_refresh_all_counts(e)
clear_badarea_grid = "{dialog.EmbeddedGrid_BADAREAGRID}.refresh(); {dialog.object}.setValue('badarea',0,true); "
end function

function clear_baddealer_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(10)

ls_js = xbasic_refresh_all_counts(e)
clear_baddealer_grid = "{dialog.EmbeddedGrid_BADDEALERGRID}.refresh(); {dialog.object}.setValue('baddealer',0,true); "
end function

function clear_badticketnum_grid as C (e as p)
dim ls_js as C
delete_from_upload_table(11)

ls_js = xbasic_refresh_all_counts(e)
clear_badticketnum_grid = "{dialog.EmbeddedGrid_BADTICKETNUMGRID}.refresh(); {dialog.object}.setValue('badticketnum',0,true); "
end function


function delete_from_upload_table as C (fa_status as N)

dim cn as SQL::Connection
dim args as SQL::Arguments
dim rs as SQL::ResultSet
dim flagresult as l
dim ls_sql as C
dim ls_user as C

ls_user = SESSION.UPLOAD_USER
ls_usertribe = SESSION.USER_TRIBE
'ls_user = "jjohnson"
'ls_usertribe = "10"
args.Add("ls_upload_user",ls_user)
args.Add("li_update_status",fa_status)

flagresult = cn.Open("::Name::tocas")
'debug(1)
' ls_sql = "DELETE FROM tocas_dba.ticket_upload where update_status = :li_update_status and tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :ls_upload_user)"
ls_sql = "DELETE FROM tocas_dba.ticket_upload where update_status = :li_update_status and upload_user = :ls_upload_user"
flagresult = cn.Execute(ls_sql,args)
cn.FreeResult()

args.Set("ls_upload_user",ls_user)
args.Set("li_update_status",fa_status)

ls_sql = "DELETE FROM dbo.ticket_pre_upload where update_status = :li_update_status and upload_user = :ls_upload_user"
flagresult = cn.Execute(ls_sql,args)
cn.FreeResult()
cn.Close()

end function


'///////////////////////////////////////////////////////////////////////////////////////////////

function overwrite_db_tickets as c (e as p)

dim cn as SQL::Connection
dim args as SQL::Arguments
dim rs as SQL::ResultSet
dim flagresult as l
dim ls_sql as C
dim ls_ticket as C
dim ls_user as C
dim ll_nwifc_num as N
dim numrows as N
dim idx as N
dim li_year as N
dim li_stat as N

args.Add("ls_ticknum")
args.Add("li_catch_year")
args.Add("nwifc_num")
args.Add("ls_upload_user")
args.Add("li_update_status")

flagresult = cn.Open("::Name::tocas")
ls_user = SESSION.UPLOAD_USER
ls_usertribe = SESSION.USER_TRIBE
'ls_user = "jjohnson"
'ls_usertribe = "10"

li_stat = 1
args.Set("ls_upload_user",ls_user)
args.Set("ls_upload_tribe", ls_usertribe)
args.Set("li_update_status",li_stat)
'debug(1)
ls_sql = "DELETE FROM tocas_dba.NWIFC_FISH_TICKET WHERE fish_ticket_num IN (SELECT fish_ticket_num from tocas_dba.ticket_upload where (update_status = :li_update_status) and (upload_user = :ls_upload_user) "
ls_sql = ls_sql + " and tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :ls_upload_user) )"

flagresult = cn.Execute(ls_sql,args)

ls_sql = "DELETE FROM tocas_dba.NWIFC_FISH_TICKET_DETAIL WHERE nwifc_ticket_id NOT IN (SELECT nwifc_ticket_id from tocas_dba.NWIFC_FISH_TICKET)"
flagresult = cn.Execute(ls_sql,args)

write_data_to_tables(1)
cn.Close()
'clear_existing_grid()
'xbasic_refresh_all_counts(e)
overwrite_db_tickets = "alert('Data Overwritten'); {dialog.EmbeddedGrid_EXISTTICKETSGRID}.refresh();"

end function

FUNCTION get_next_file_id_num AS N ()

dim ll_max_file_num as N
dim cn as SQL::Connection
dim flagresult as l
dim ls_sql as C
dim ls_check as C

flagresult = cn.Open("::Name::tocas")
ls_tablename = "tocas_dba.uploaded_files" ' table name
ls_filter = "id IN (SELECT max(id) FROM tocas_dba.uploaded_files)"
ls_result = "id" ' column name
ll_max_file_num = sql_lookup(cn, ls_tablename, ls_filter, ls_result)
ls_check = STR(ll_max_file_num)
IF isnull(ls_check) THEN
ll_max_file_num = 1
ELSE
ll_max_file_num = ll_max_file_num + 1
END IF
cn.Close()
get_next_file_id_num = ll_max_file_num 'return results
END FUNCTION

FUNCTION xbasic_count_records as N (fa_status as N)

dim cn as SQL::Connection
dim args as SQL::Arguments
dim rs as SQL::ResultSet
dim flagresult as l
dim ls_sql as C
dim ls_user as C
dim ls_usertribe as C
dim ll_numrows as N

flagresult = cn.Open("::Name::tocas")
' debug(1)
if flagresult then
' check if file has been uploaded before
' clear upload table
ls_user = SESSION.UPLOAD_USER
ls_usertribe = SESSION.USER_TRIBE
'ls_user = "jjohnson"
'ls_usertribe = "10"
args.Set("userid", ls_user)
args.Set("update_status", fa_status)

ls_sql = "SELECT COUNT(*) FROM tocas_dba.ticket_upload where update_status = :update_status and tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :userid);"
cn.Execute(ls_sql,args)
rs = cn.ResultSet
ll_numrows = rs.data(1)
cn.FreeResult()

end if

cn.Close()

xbasic_count_records = ll_numrows
END FUNCTION

FUNCTION xbasic_refresh_all_counts as C (e as p)
dim numreadyload as N
dim numexisting as N
dim numexistwrongtribe as N
dim numwrongtribe as N
dim numduplicates as N
dim numfuturedate as N
dim numbaddisp as N
dim numbadgear as N
dim numbadspecies as N
dim numbadarea as N
dim numbaddealer as N
dim numbadticketnum as N
dim totalrecs as N

dim ls_cnts1 as C
dim ls_cnts2 as C
dim ls_js as C

numreadyload = xbasic_count_records(0)
numexisting = xbasic_count_records(1)
numexistwrongtribe = xbasic_count_records(2)
numduplicates = xbasic_count_records(3)
numwrongtribe = xbasic_count_records(4)
numfuturedate = xbasic_count_records(5)

numbaddisp = xbasic_count_records(6)
numbadgear = xbasic_count_records(7)
numbadspecies = xbasic_count_records(8)
numbadarea = xbasic_count_records(9)
numbaddealer = xbasic_count_records(10)
numbadticketnum = xbasic_count_records(11)

totalrecs = (numreadyload + numexisting + numexistwrongtribe + numduplicates + numwrongtribe + numfuturedate + numbaddisp + numbadgear + numbadspecies + numbadarea + numbaddealer + numbadticketnum )
'debug(1)
' pass numbers back to javascript funtion
ls_cnts1 = totalrecs + "," + numreadyload + "," + numexisting + "," + numexistwrongtribe + "," + numwrongtribe + "," + numduplicates + ","
ls_cnts2 = numfuturedate + "," + numbaddisp + "," + numbadgear + "," + numbadspecies + "," + numbadarea + "," + numbaddealer + "," + numbadticketnum
ls_js = "window.parent.js_refresh_counts(" + ls_cnts1 + ls_cnts2 + ");"

xbasic_refresh_all_counts = ls_js

END FUNCTION

FUNCTION xbasic_delete_temp_import AS C (ls_user AS C )
dim cn as SQL::Connection
dim args as SQL::Arguments
dim connectflag as L
dim flag as L
dim ls_sql as C

connectflag = cn.Open("::Name::TOCAS")
args.Add("qryowner",ls_user)

' delete from temp import tables
ls_sql = "DELETE FROM TOCAS_DBA.TICKET_UPLOAD WHERE UPLOAD_USER = :qryowner"

flag = cn.Execute(ls_sql,args)
debug(1)
cn.FreeResult()

args.Set("qryowner",ls_user)

' delete from temp import tables
ls_sql = "DELETE FROM TICKET_PRE_UPLOAD WHERE UPLOAD_USER = :qryowner"

flag = cn.Execute(ls_sql,args)

cn.FreeResult()

args.Set("qryowner",ls_user)

cn.Close()

END FUNCTION

[lgrupido]

Re: Problem with uploading data into tables

I could look at that code for a LONG time and not come up with an answer for you, there is a whole lot of it.

What I would do is save the insert code to file for each insert and then check the file when you get duplicates to see what happenned.

But one way to "fix" the issue is:

If you set a primary Key or unique index in your detail table that is data based, then you won't be able to enter the detail record twice. The insert will automatically fail when it tries to enter the record a second time because it will fail the uniqueness or Key test.

It would make the procedure quicker if you can figure out why it is entering some records twice, but that is something you could work out over time.

[Anita Headley]

Re: Problem with uploading data into tables

Can't set a data -based key on the detail table because there is no set of fields that is consistently unique and not null. I will try breaking it up and looking at the data in intermediate stages. Part of the problem is that I can't seem to duplicate the problem with test uploads, yet duplicates keep appearing in user's uploaded data. Not every time, but only sometimes. I have tried asking the users what they are doing, but they reply that they are doing it just the way the manual tells them to. I have looked at the upload files to make sure it does not contain duplicates.

[lgrupido]

Re: Problem with uploading data into tables

Then the issue is likely in your join statement:

ls_select = " SELECT n.nwifc_ticket_id, u.catch_area, u.species_code, u.rptd_cnt, u.rptd_lbs_qty, u.rptd_lbs_type, u.landing_cnt, u.create_date, u.rptd_price_amt, u.create_userid, u.subarea, u.disposition_code, u.gear_code, u.grade"
ls_from = " FROM tocas_dba.ticket_upload u, tocas_dba.NWIFC_FISH_TICKET n "
ls_where = " where u.update_status = :li_update_status and n.fish_ticket_num = u.fish_ticket_num and n.landing_date = u.landing_date and u.tribe_num in (select tribe_code from tocas_dba.user_tribe where username = :ls_upload_user);"

Since you have non-unique data to begin with, when you join the tables, you are likely to get records duplicated by the join. Being unfamiliar with your tables and the data in them, I can's say this for sure, but it's a likely candidate.

If the issue with non-unique data has to do with nulls only, then turn the nulls into empty strings "" where necessary to get uniqueness.

Or assign an arbitrary key when you pull in the data to the temporary table and use THAT as the key in the final data - That will also stop duplication.