<cfinclude template="header.cfm">


<div id="content">
  <div id="content-header">
  </div>
  <div class="container-fluid" style="margin-left:200px;">
      
      <cfsetting requesttimeout="600">
<cftry>
    <!--- Check if file was uploaded --->
    <cfif structKeyExists(form, "csvfile")>
        <!--- Save the uploaded file to a temporary directory --->
        <cffile action="upload" filefield="csvfile" destination="/home/home2/cgny/public_html/2019/newadmin/" nameconflict="overwrite" result="uploadResult">
        
        <!--- Read and parse the CSV file --->
        <cfset csvData = []>
        <cffile action="read" file="#uploadResult.serverFile#" variable="fileContent">
        <cfset csvLines = listToArray(fileContent, chr(10))>
  
<cfloop index="i" from="2" to="#arrayLen(csvLines)#">
    <cfset rowData = listToArray(csvLines[i])>
    
    <!--- Debug: Output the raw data for each line --->
 
    
    <!--- Ensure row has the correct number of columns --->
    <cfif arrayLen(rowData) EQ 8>
        <!--- Trim all fields --->
        <cfset rowData = arrayMap(rowData, function(val) { return trim(val); })>
        
        <!--- Debug: Output trimmed data --->
      
        <!--- Validate required fields (First Name, Last Name, and Email must not be empty) --->
        <cfif len(rowData[2]) AND len(rowData[3]) AND len(rowData[4])>
            <!--- Clean numeric fields --->
            <cfset rowData[5] = reReplace(rowData[5], "[^0-9.]", "", "all")> <!-- Points -->
            <cfset rowData[7] = reReplace(rowData[7], "[^0-9.]", "", "all")> <!-- Total Earned -->
            <cfset rowData[8] = reReplace(rowData[8], "[^0-9.]", "", "all")> <!-- Redeemable -->
            
      
            <!--- Validate numeric fields --->
            <cfif isNumeric(rowData[5]) AND isNumeric(rowData[7]) AND isNumeric(rowData[8])>
                <cfset csvData.append(rowData)>
            <cfelse>
                <cfoutput>
                    <p>Warning: Skipping line #i#: Invalid numeric values in Points, Total Earned, or Redeemable. Data: #arrayToList(rowData, ", ")#</p>
                </cfoutput>
            </cfif>
        <cfelse>
            <cfoutput>
                <p>Warning: Skipping line #i#: Missing First Name, Last Name, or Email. Data: #arrayToList(rowData, ", ")#</p>
            </cfoutput>
        </cfif>
    <cfelse>
        <cfoutput>
            <p>Warning: Skipping line #i#: Invalid column count. Data: #arrayToList(rowData, ", ")#</p>
        </cfoutput>
    </cfif>
</cfloop>

       <Cftry><cfquery name="dropme" datasource="#ds#">
truncate table temp_table        </cfquery>
        <cfcatch></cfcatch>
        
        </Cftry> 
        
      
        
        <!--- Insert CSV data into the temporary table --->
        <cfloop index="i" from="1" to="#arrayLen(csvData)#">
            <cfquery name="insertData" datasource="#ds#">
                INSERT INTO temp_table (event_name, first_name, last_name, email, points, point_for,Total_Earned,redeemable)
                VALUES (
                    <cfqueryparam value="#csvData[i][1]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][2]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][3]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][4]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][5]#" cfsqltype="CF_SQL_INTEGER">,
                    <cfqueryparam value="#csvData[i][6]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][7]#" cfsqltype="CF_SQL_VARCHAR">,
                    <cfqueryparam value="#csvData[i][8]#" cfsqltype="CF_SQL_VARCHAR">
                );
            </cfquery>
        </cfloop>
        
        
        <p><a href="emailall.cfm" onclick="return confirm('Are you sure?');">Email Everyone</a>
            <br>
        </p>
        <!--- Retrieve and display user data with cumulative points and breakdown --->
        <cfquery name="getUserData" datasource="#ds#">
            SELECT first_name, last_name, email, SUM(points) as total_points
            FROM temp_table
            GROUP BY email, first_name, last_name
            ORDER BY last_name, first_name;
        </cfquery>

        <cfoutput>
<table id="example23" class="display nowrap table table-hover table-striped table-bordered" cellspacing="0" width="p0-100%">
                <tr>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email</th>
                    <th>Total Points</th>
                    <th>Action</th>
                </tr>
                <cfloop query="getUserData">
                    <tr>
                        <td>#first_name#</td>
                        <td>#last_Name#</td>
                        <td>#email#</td>
                        <td>#total_points#</td>
                        <td><a href="email.cfm?email=#urlEncodedFormat(email)#">Send Email</a></td>
                    </tr>
                </cfloop>
            </table>
        </cfoutput>

    <cfelse>
        <cfoutput>
            <p>No file uploaded. Please go back and try again.</p>
            <p><a href="index.cfm">Back to Upload</a></p>
        </cfoutput>
    </cfif>

    <cfcatch>
        <cfoutput>
            <p>Error: #cfcatch.message#</p>
            <p><a href="index.cfm">Back to Upload</a></p>
        </cfoutput>
    </cfcatch>
</cftry>



   </div>
<!--Footer-part-->
<br><br><br><br>

<cfinclude template="footer.cfm">