
    *HhE                     6    d dl Z d dlZd dlZd dlmZ d ZddZy)    N)parserc                     | j                  dd      j                  dd      } t        j                  dd| j                         j	                         j                  d            S )z=Normalize column headers and strings for consistent matching.u   ﻿ u   ï»¿z\s+ )replaceresubstriplowerlstrip)texts    (/var/www/html/allocaisaas/app/allocai.py	normalizer      sL    <<"%--h;D66&#tzz|113::8DEE    c                    t        j                  d      }|j                         }|j                  d       t	        g d      }t        | d      5 }t        j                  |      }t        |      }|}	t        |      D 
ci c]  \  }
}t        |      |
 }}
}|	D ]8  }t        |      j                  dd      }||vs#	 |j                  d| d	       : g d
g dg dg dg dg dd}t        |      D 
ci c]  \  }
}t        |      |
 }}
}i }|j                         D ]2  \  }}|D ]  }t        |      }||v s||   ||<    % t        d|        d}|D ]  }||d      }	 t!        j"                  ||d            }|j%                  |      }	 t!        j"                  ||d            }|j%                  |      }||d      }||d      }||d      }|j                  dd      j                  dd      j)                         }|rt+        |      nd}|r|r
k  rdnd} nd} ||d      }!	 t!        j"                  |!      }"|"j%                  d      }||d      }$	 t!        j"                  |$      }%|%j%                  d      }|j                  d"||||||| f       i }&|j                         D ]-  \  }'}
t        |'      j                  dd      }||vs&||
   |&|<   / |&j                         D ]  \  }}(|j                  d#| d$|(||f         |j/                           |j                  d%       |j1                         })|)D ]&  \  }|j                  d&|f       |j3                         \  }*|j                  d'|*|f       |j                  d(|f       |j                  d)|f       |j3                         \  }+|+d*k(  r>|j                  d+|f       |j3                         },|,r|,d*   }-|j                  d,||-f       |j                  d)|f       |j3                         \  }.|.d-kD  rd|.z  }/|j                  d.|/|f       |j                  d/|f       |j3                         }0|0ra|0d*   r\|0d*   }1|j                  d0||1f       |j                  d1|f       |j3                         \  }2|2d-kD  rd|2z  }3|j                  d2|3|f       |j                  d3|f       |j                  d4|f       |j3                         \  }4|4d-kD  rd|4z  }5|j                  d5|5|f       |d6k(  r|j                  d7       n-|d8k(  r|j                  d9       n|d:k(  r|j                  d;       |j                  d<       |)D ]=  \  }|j                  d=|f       |j3                         \  }6|j                  d>|6|f       ? |j                  d?       |j                  d@       |)D ]=  \  }|j                  dA|f       |j3                         \  }7|j                  dB|7|f       ? |j                  dC       |)D ]=  \  }|j                  dD|f       |j3                         \  }8|j                  dE|8|f       ? ) 	 ddd       |j                  dF       |j1                         D 9cg c]  }9|9d-   	 }:}9g dG};|;D <cg c]	  }<|<|:v s|< }=}<t        |dHddI      5 }>t        j4                  |>      }?|?j7                  |=       |j                  dJdKj9                  |=       dL       |j1                         D ]g  }9t;        |9      }9t        |=      D ];  \  }@}A|AdMv s|9@   s	 t!        j"                  |9@         j%                  dN      |9|@<   = |?j7                  |9       i 	 ddd       |j=                          yc c}}
w # t         j                  $ r Y 5w xY wc c}}
w # t&        $ r d}Y Rw xY w# t&        $ r d}Y 7w xY w# t&        $ r}#t-        d|! d |#        d}Y d}#~#d}#~#ww xY w# t&        $ r}#t-        d!|$ d |#        d}Y d}#~#d}#~#ww xY w# 1 sw Y   xY wc c}9w c c}<w # t&        $ r Y ,w xY w# 1 sw Y   xY w)Oz<Process an attribution CSV file and output the enhanced CSV.z:memory:aZ  
    CREATE TABLE Alloc_Calcs (
        opportunity_name  TEXT,
        campaign_influence_date   TEXT,
        opportunity_created_date    TEXT,
        campaign_name  TEXT,
        is_primary_campaign  TEXT,
        opportunity_amount FLOAT,
        opportunity_amount_check FLOAT,
        campaign_count  INTEGER,
        
        eligible_campaign_flag FLOAT,    
        influence_campaign_flag FLOAT,
        real_primary_campaign_flag FLOAT,
        first_touch_flag FLOAT,
        primary_campaign_percent FLOAT,
        first_touch_percent FLOAT,
        influence_campaign_pct FLOAT,
        total_influence_pct FLOAT,
        total_opportunity_influence_pct FLOAT,
        
        addn1_influence_campaign_pct FLOAT,
        total2_influence_pct FLOAT,
        total2_opportunity_influence_pct FLOAT,
        total_allocation_amt FLOAT
    )
    )opportunity_namecampaign_influence_dateopportunity_created_datecampaign_nameis_primary_campaignopportunity_amountzutf-8)encodingr   _z$ALTER TABLE Alloc_Calcs ADD COLUMN "z" TEXT)opportunity namezopp nameopportunitynameopp_name)campaign influence datezinfluence datezcampaign date)opportunity created datezcreated datezopp created)campaign namecampaignzcampaign title)is primary campaignprimaryzprimary campaign)opportunity amountamountz
opp amount)r   r   r   r   r!   r#   zMissing expected column: z%Y-%m-%dr   r   Nr   r   r!   r#   $r   ,g        g      ?z)[ERROR] Invalid campaign_influence_date: u    → z*[ERROR] Invalid opportunity_created_date: a  INSERT OR REPLACE INTO Alloc_Calcs
                (opportunity_name, campaign_influence_date, opportunity_created_date, campaign_name,
                is_primary_campaign, opportunity_amount, eligible_campaign_flag) 
                VALUES (?, ?, ?, ?, ?, ?, ?)z-
                    UPDATE Alloc_Calcs SET "z[" = ?
                    WHERE opportunity_name = ? AND campaign_name = ?
                z1SELECT DISTINCT opportunity_name FROM Alloc_Calcsz;SELECT COUNT(*) FROM Alloc_Calcs WHERE opportunity_name = ?zDUPDATE Alloc_Calcs SET campaign_count = ? WHERE opportunity_name = ?z
                UPDATE Alloc_Calcs
                SET real_primary_campaign_flag = 1.0
                WHERE opportunity_name = ? AND UPPER(is_primary_campaign) = 'TRUE'
            z
                SELECT COUNT(*) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND real_primary_campaign_flag = 1.0
            r   z
                    SELECT campaign_influence_date FROM Alloc_Calcs
                    WHERE opportunity_name = ? AND eligible_campaign_flag = 1.0
                    ORDER BY campaign_influence_date DESC LIMIT 1
                z
                        UPDATE Alloc_Calcs
                        SET real_primary_campaign_flag = 1.0
                        WHERE opportunity_name = ? AND campaign_influence_date = ? AND eligible_campaign_flag = 1.0
                       z
                    UPDATE Alloc_Calcs
                    SET real_primary_campaign_flag = ?
                    WHERE opportunity_name = ? AND real_primary_campaign_flag = 1.0
                z
                SELECT MIN(campaign_influence_date) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND eligible_campaign_flag = 1.0
            z
                    UPDATE Alloc_Calcs
                    SET first_touch_flag = 1.0
                    WHERE opportunity_name = ? AND campaign_influence_date = ? AND eligible_campaign_flag = 1.0
                z
                    SELECT COUNT(*) FROM Alloc_Calcs
                    WHERE opportunity_name = ? AND first_touch_flag = 1.0
                z
                        UPDATE Alloc_Calcs
                        SET first_touch_flag = ?
                        WHERE opportunity_name = ? AND first_touch_flag = 1.0
                    a.  
                UPDATE Alloc_Calcs
                SET influence_campaign_flag = 1.0
                WHERE opportunity_name = ? AND
                    eligible_campaign_flag = 1.0 AND
                    real_primary_campaign_flag IS NULL AND
                    first_touch_flag IS NULL
            z
                SELECT COUNT(*) FROM Alloc_Calcs
                WHERE opportunity_name = ? AND influence_campaign_flag = 1.0
            z
                    UPDATE Alloc_Calcs
                    SET influence_campaign_flag = ?
                    WHERE opportunity_name = ? AND influence_campaign_flag = 1.0
                FTz
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = 0.0,
                        first_touch_percent = COALESCE(first_touch_flag, 0) * 1.0,
                        influence_campaign_pct = 0.0
                    LTa  
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = COALESCE(real_primary_campaign_flag, 0) * 1.0,
                        first_touch_percent = 0.0,
                        influence_campaign_pct = 0.0
                    MTaI  
                    UPDATE Alloc_Calcs
                    SET primary_campaign_percent = COALESCE(real_primary_campaign_flag, 0) * 0.4,
                        first_touch_percent = COALESCE(first_touch_flag, 0) * 0.4,
                        influence_campaign_pct = COALESCE(influence_campaign_flag, 0) * 0.2
                z
            UPDATE Alloc_Calcs
            SET total_influence_pct = 
                COALESCE(primary_campaign_percent, 0) +
                COALESCE(first_touch_percent, 0) +
                COALESCE(influence_campaign_pct, 0)
            z
                    SELECT SUM(total_influence_pct) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                z
                    UPDATE Alloc_Calcs
                    SET total_opportunity_influence_pct = ?
                    WHERE opportunity_name = ?
                a  
            UPDATE Alloc_Calcs
            SET addn1_influence_campaign_pct = 
                CASE
                    WHEN total_opportunity_influence_pct = 0.8 AND total_influence_pct = 0.8 THEN 0.2
                    WHEN total_opportunity_influence_pct = 0.8 AND total_influence_pct = 0.4 THEN 0.1
                    WHEN total_opportunity_influence_pct = 0.4 AND total_influence_pct = 0.4 THEN 0.6
                    ELSE 0.0
                END
            z
            UPDATE Alloc_Calcs
            SET total2_influence_pct = 
                COALESCE(total_influence_pct, 0) + 
                COALESCE(addn1_influence_campaign_pct, 0)
            z
                    SELECT SUM(total2_influence_pct) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                z
                    UPDATE Alloc_Calcs
                    SET total2_opportunity_influence_pct = ?
                    WHERE opportunity_name = ?
                    z
            UPDATE Alloc_Calcs
            SET total_allocation_amt = COALESCE(total2_influence_pct, 0) * COALESCE(opportunity_amount, 0)
            z
                    SELECT SUM(total_allocation_amt) FROM Alloc_Calcs
                    WHERE opportunity_name = ?
                z
                    UPDATE Alloc_Calcs
                    SET opportunity_amount_check = ?
                    WHERE opportunity_name = ?
                zPRAGMA table_info(Alloc_Calcs))r   opportunity_stager   r   opportunity_close_dateopportunity_ownerlead_sourceaccount_nameopportunity_typer   campaign_typer   r   revenue_sharetotal_allocation_amtw)newliner   zSELECT z, z FROM Alloc_Calcs)r   r,   r   z%d-%b-%Y)sqlite3connectcursorexecutescriptsetopencsvreadernext	enumerater   r   executeOperationalErroritems
ValueErrorr   parsestrftime	Exceptionr
   floatprintcommitfetchallfetchonewriterwriterowjoinlistclose)B	input_csv
output_csvmodelconncurfixed_fieldshandler=   headersoriginal_headersidxheadernormalized_headerscolexpected_columnsheader_indexstandard_namealiasesaliasalias_normalizeddate_format_outpiecesr   r   campaign_influence_date_strr   opportunity_created_date_strr   r   
raw_amountclean_amountr   eligible_campaign_flagraw_infl_dateparsed_infl_dateeraw_created_dateparsed_created_dateextra_valueshvalopportunity_namescountprimary_countlatest_date_rowlatest_date
flag_count
equal_flagearliest_date_rowearliest_dateft_countequal_ft	inf_count	equal_inftotal_pct_sumtotal2_pct_sum	alloc_sumrowcolumnsfinal_columnscexport_columnsfrL   col_idxcol_namesB                                                                     r   process_allocai_csvr      sH
    ??:&D
++-C   	8   L 
i'	* E4fF#v," IRRYHZ[fi/4[[ ' 	FF#++C5C,&KK"Fse6 RS		 !`'e(cL#Y"P
 IRRYHZ[fi/4[[&6&<&<&> 	N"M7  N#,U#3 #'992DEU2VL/	N !#<]O!LMM	N % Y	F%l3E&FG3*0,,vlKd>e7f*g'.E.N.N._+4+1<<|Lf?g8h+i(/G/P/PQ`/a, #<#@AM"(6K)L"M  -A BCJ%--c26>>sBGMMOL8D|!4# +/K0GKc0cil&),&
 #<0I#JKM3#)<<#> .>.G.G
.S+  &l3M&NO4&,ll3C&D#/B/K/KJ/W, KK 0 "#>@\24FH^`	a L,224 43l**34l*(.sL%4" )..0 =S !--0E 2 +];== KKMsY	x 	GHLLN#4 	4KKUXhWjk||~HUKK^afhx`yz KK  #$	& KK  #$&  #||~]!  '(	*
 #&,,.""1!"4KKK ! +K8	: KK  #$&  LLNMZA~ :-
  !"23	5 KK  #$& !$ %6q%9 1! 4  '6	8   '(* "llna<"X~HKK ! #$45	7 KK  #$& KK  #$& <<>LY1})O	   !12	4 }   $   $   KK   (9 8#!  '(* $'<<>   $%56	88 KK 	 	 KK   (9 =#!  '(* %(LLN!  )*:;	== KK  
 (9 4#!  '(*  #||~   !12	44i	4ME4P
 KK01!$0#s1v0G0M$ "/?A!w,a?N?	j#rG	< !A'gdii788IJK<<> 	!Cs)C%.~%> !ppux  zA  vB'-||CL'A'J'J:'VG OOC 	!!  	JJLU \ //  \&  3.2+3  4/3,4<  3A-PUVWUXYZ.2+3  4BCSBTTYZ[Y\]^/3,4uE4 E4R
 1* @ % ! !s<  1a^6'a^<%a;_/a*a,,_,_.A/a5&` a$&`*
AaNa"a!8	a&a&A>a;a;*a+a;6a<_a_	a_+'a*_++a._=9a<_==a 	`'	`"a"`''a*	a3aaaaa+	a8	4a;7a8	8a;;b)r(   )r6   r<   r   dateutilr   r   r    r   r   <module>r      s     
 	 F
Zr   