
    -nj+:                         d Z ddlZddlmZmZ ddlmZ ddlmZ ddl	m
Z
  ej                  e      ZddZd	 Zd
 ZddZddZd Zd Zd Zd Zd Zd Zy)z
Shared utilities for bulk Excel member import.

Used by both the management command (import_members_excel) and the
API endpoint (MemberViewSet.import_excel).
    N)DecimalInvalidOperation)User)transaction   )Memberc                     | |S t        | t              r1| j                         } | j                  d      s| s|S 	 t	        |       S 	 t	        |       S # t
        $ r |cY S w xY w# t        t
        f$ r |cY S w xY w)zu
    Return a numeric value from a cell that might contain an Excel formula
    string, None, or a valid number.
    =)
isinstancestrstrip
startswithfloat
ValueError	TypeError)valuedefaults     7/var/www/kalinger_portal/trust-backend/members/utils.py_safe_numericr      s    
 }%C N	<U|  	N	 z" s#   
A 
A# A A #A76A7c                    | y	 t        | t        t        f      rt        t        |             }nt        |       j	                         }dD ]  }|j                  |d      } |sy|j                  d      rt        |      dk(  r|dd }|dd }|j                         rt        |      dk7  ry|S # t
        t        f$ r t        |       j	                         }Y w xY w)	z
    Normalize a phone value from Excel (integer like 919841344883) to a
    10-digit string.  Returns None if the value is unusable.
    N) -()+ 91      i
   )r   intr   r   r   r   r   replacer   lenisdigit)	raw_phone	phone_strchars      r   normalize_phoner(   )   s    
 +i#u.C	N+II,,.I
 * 0%%dB/	0  D!c)n&:abM	 #$I#i.B"6) 	" +	N((*	+s   AB/ /(CCc                    t        |       dk  rt        |       dgdt        |       z
  z  z   } | d   }|r t        |t              r|j	                         syt        |      j	                         }| d   t        | d         j	                         nd}t        | d   d      }| d	   rt        | d	         j	                         nd}t        | d
   d      }t        | d   d      }t        | d   d      }t        | d         }|||||||t        t        |            t        t        |            t        t        |            d
S )aa  
    Parse a single openpyxl row (tuple of cell values, values_only=True)
    from the Tamil Excel format into a clean dict.

    Expected column layout (0-indexed):
        0: Serial number (ignored)
        1: Member name
        2: Reference ID (Column 3)
        3: Tax Count (Column 4)
        4: City / Town (Column 5)
        5: Old Balance (Column 6)
        6: Debit Amount (Column 7)
        7: Credit Amount (Column 8)
        8: Ignored / Balance due hint
        9: Phone number (Column 10, integer with 91 prefix)

    Returns a dict with parsed values or None if the row should be skipped.
    r    Nr   r   r            ?)r         g              	   )
namename_taphonereference_id	tax_countaddress_cityaddress_city_taold_balancedebit_amountcredit_amount)r#   listr   r   r   r   r(   r   )	rowr1   r4   r5   cityr8   r9   r:   r3   s	            r   parse_member_rowr>   M   s?   ( 3x"}3i4&BSM22q6DJtS)$**,t9??D +.a&*<3s1v;$$&"L c!fc2I #&a&3s1v;bD  A5K !Q6L "#a&$7M CF#E $s;/0L 12 ]!34     c                 >   ddl m} ddlm}m} ddlm} | j                  d      }| j                  dd      }|r:t        j                  j                  |      j                         rd	d
d| d| dfS |s~t        j                  j                  d      j                         xsN t        j                  j                  d      j                         xs t        j                  j                         }|s't        j                  j                  dddd      \  }}	 t        j                          5  t        d7i dd	d|d| j                  dd      d|xs d	d| j                  dd      d| j                  dd      d| j                  dd      d| j                  dd      d| j                  d |d            ddd| j                  dd      d | j                  dd      d!dd" |d      d# |d      d$dd%dd&dd'd
}	|	j#                          |j                  j                  d(d)d*dd+d,-      \  }
}| j                  d. |d            }|dkD  rd|j                  j%                  |
d/||j'                         j)                         d0|	|	j*                  |	j,                  |	j.                  xs dd1|2       | j                  d3 |d            }|dkD  rd|j                  j%                  |
d4||j'                         j)                         d5|	|	j*                  |	j,                  |	j.                  xs dd6|2       |	j1                          |	j#                          d	d	d	       	dd	fS # 1 sw Y   xY w# t2        $ r}d	d
t5        |      fcY d	}~S d	}~ww xY w)8a  
    Create a Django User + Member from a parsed dict.
    Also creates debit/credit transactions if amounts are specified.

    Returns (member, created, reason):
        - (member, True, None)  on success
        - (None, False, reason) when skipped or errored
    r   )r   )AccountHeadAccountTransaction)timezoner3   r1   r   )r3   NFzPhone z already exists, skipping ''T)is_superuser)is_staffsystem)rF   rE   )usernamedefaultsuserr2   r4   r5   r+   r6   r7   r8   z0.00date_of_birthz
2000-01-01address
address_tafather_name
annual_taxamount_paidpassword_reset_requiredis_family_head	is_active
is_expiredz
Kodai Variu   கொடை வரிKodaiRevenue)r2   	head_typerS   account_type)r1   rI   r9   DEBITCreditzKodai Vari Debit (Excel Import))account_headtransaction_typeamounttransaction_datepayment_modemember
donor_namedonor_name_tadonor_contactpurpose
entered_byr:   CREDITCashz Kodai Vari Credit (Excel Import) )decimalr   accounting.modelsrA   rB   django.utilsrC   getr   objectsfilterexistsr   firstget_or_creater   atomicsavecreatenowdater1   r2   r3   refresh_from_db	Exceptionr   )datarJ   r   rA   rB   rC   r3   r1   _r`   r[   	debit_amt
credit_amtexcs                 r   create_member_from_dictr~      s     A%HHWE88FBD &&U&3::<eveW,GvQOPP ||"""5;;=  DATAT^bATAcAiAiAk  Doso{o{  pB  pB  pD,,,,hVZlpIq,raN'! I	  B/ mt	
 "XXnb9 ((;4 "XXnb9 !%):B ? !HH]GFOD + 4  88$5r:  #6?  $FO!" )-#$  $%& '( !)F, KKM *11??!7!(!%$-	 @ OL! AI1}"**11!-%,$%-\\^%8%8%:!)!%{{"(.."(,,"4"=# 2  /76?CJA~"**11!-%-%%-\\^%8%8%:!'!%{{"(.."(,,"4"># 2  ""$KKMSI	V d##WI	 I	X  'eSX&&'s7   M; 2H0M/"M; /M84M; ;	NNNNc                    | j                   }d}d}g }t               }t        |j                  d            D ]  \  }}|dk  rt	        |      }	|	|	j                  dd      }
|	j                  d      }|r7||v r"|j                  |dz   |
d	| d
d       |dz  }e|j                  |       t        |	|      \  }}}|r|dz  }|dz  }|j                  |dz   |
|xs dd        |||dS )z
    Process an openpyxl Workbook and return import results.

    Returns a dict:
        {
            "created": int,
            "skipped": int,
            "errors": [{"row": int, "name": str, "reason": str}, ...]
        }
    r   T)values_onlyr.   r1   r   r3   r   zDuplicate phone z in this file)r<   r1   reasonrJ   Unknown)createdskippederrors)	activeset	enumerate	iter_rowsr>   rl   appendaddr~   )wbrJ   wsr   r   r   seen_phonesidxr<   parsedr1   r3   r`   was_createdr   s                  r   process_excel_workbookr      s-    
BGGF%Kblltl<= "S7!#&>zz&"%

7##7  0}E 
 1OOE"&=f4&P#VqLGqLGMMQw -I ="J  r?   c                 J   | j                   r| j                  rt        d      S t        d      }| j                  r;| j                  dk(  r,| j                  dk(  r|t        d      z  }n|t        d      z  }| j
                  j                         D ]  }|t        |      z  } |S )zv
    Recursively sum the tax contributions of a member and their descendants
    who are not active family heads.
    0.0Male	Unmarriedz0.51.0)rR   rS   r   gendermarital_statuschildren_setallget_member_and_descendants_tax)r`   taxchilds      r   r   r   4  s    
 !1!1u~
%.C==F"$$3wu~%wu~%$$((* 5-e445 Jr?   c                    | j                   r| j                  st        d      S t        d      }| j                  rrt        j
                  j                  | j                        j                  | j                        }|D ])  }|j                   r|j                  r|t        |      z  }+ | j                  j                         D ]  }|t        |      z  } |S )a  
    Calculate the tax count multiplier for an active family head.
    Base is 1.0 for the head.
    Plus contributions from dependent unpromoted siblings and their offspring recursively.
    Plus contributions from their own unpromoted children and offspring recursively.
    r   r   )father)id)rR   rS   r   r   r   rm   rn   excluder   r   r   r   )r`   r5   siblingssiblingr   s        r   calculate_member_tax_countr   J  s     !!f&6&6u~I }}>>(((>FF&))FT 	AG%%'*;*;7@@I	A $$((* ;3E::	; r?   c                 z   | j                   r.| j                  s | j                  }|s| j                          | j                  }|sy|}d}t        j
                  j                  |      j                         r;| d| }|dz  }t        j
                  j                  |      j                         r;| j                  r| j                  nd}t        j
                  j                  ||      }t        j
                  j                  | j                        j                  |d	       || _        d| _        |S yy)
ze
    Generate login credentials for a member promoted to family head.
    Username is member_id.
    Nr   )rH   rz   zsra@123)rH   passwordpkT)rJ   rQ   )rR   rJ   	member_idrs   r   rm   rn   ro   r3   create_userr   r   updaterQ   )r`   rH   base_usernamecounterdefault_passwordrJ   s         r   provision_credentialsr   e  s   
 V[[##KKM''H !ll!!8!4;;=''3HqLG ll!!8!4;;= ,2<<6<<Y||''DT'U 	+22VZ2[)-&1 &1r?   c                     | j                   r^| j                   }t        j                  j                  | j                        j                  d       d| _         |j                          yy)z@
    Safely delete the login user of a demoted family head.
    r   Nr   )rJ   r   rm   rn   r   r   delete)r`   rJ   s     r   revoke_credentialsr     sP     {{{{+222=	 r?   c                     t         j                  j                  | d      j                  d      }|j                  d      j	                         }|s|j	                         }|rd|_        |j                          yy)z
    Automated succession workflow on expiration of a family head.
    Locates active children, sorts chronologically (seniority), and promotes
    the elder son (or oldest active child if no male child is found).
    T)r   rS   rK   r   )r   N)r   rm   rn   order_byrp   rR   rs   )expired_memberchildren	elder_sons      r   trigger_successionr     sm     ~~$$Nd$KTTUdeH v.446INN$	#'	  r?   c                    ddl }t        | |j                         r| j                         } n1t        | t              r!ddlm}  ||       }|r|} nt        d|        | j                  dk\  rA|j                  | j                  dd      }|j                  | j                  dz   dd      }||fS |j                  | j                  dz
  dd      }|j                  | j                  dd      }||fS )	z
    Given a date or datetime object, returns (start_date, end_date) as datetime.date objects.
    Tax year starts on August 1st and ends on July 31st of the following calendar year.
    r   N)
parse_datezInvalid date string:    r   r/      )	datetimer   rv   r   django.utils.dateparser   r   monthyear)rv   r   r   r   
start_dateend_dates         r   getCurrentTaxYearRanger     s    
 $))*yy{	D#	5D!D4TF;<<zzQ]]499a3
==Q26 x ]]499q=!Q7
==Ar2xr?   )r   )N)__doc__loggingri   r   r   django.contrib.auth.modelsr   	django.dbr   modelsr   	getLogger__name__loggerr   r(   r>   r~   r   r   r   r   r   r   r   rh   r?   r   <module>r      sj     - + ! 			8	$,!H=@h'V9x,6@$ r?   