B
    燱e                 @   sl   d Z ddlZddlmZ ddlZddlmZ ddlZddl	m
Z
 ejdddd	d
Ze ZG dd dZdS )z5
Created on Wed Feb  6 15:48:44 2019

@author: cpace
    N)stats)strptime)relativedeltaz a2nlmysql13plsk.secureserver.net	coreypaceAud12reyFarmBusiness)hostuserpasswddbc               @   st   e Zd Zdd Zdd Zdd Zdd Zd	d
 Zdd Zdd Z	dd Z
dd Zdd Zdd Zdd Zdd ZdS )pnpc             C   s   t t|djd d }t|t|djdtdd | _t|t|djd| _| ||||t | j| _	| 
||| j|| _| | j	| j| _| | j	| j| _| |||| _| |||| _| |||| _| |||| j| _d S )Nz%b   z-22)months)strr   tm_mondatetimedater   	startDateendDategetdatadatagetstartprice
startPricegetmaxBestFit
maxBestFitgetminBestFit
minBestFitgetdownBestFitHighdownBestFitHighgetupBestFitLowupBestFitLowgetlatestdate
latestDategetlatestpricelatestPrice)selfcropmonthyear
expiration r+   -C:\xampp\htdocs\Contracts\ajax\Modules\pnp.py__init__   s    "zpnp.__init__c       	      C   s"   t ||\}}}}}|||  S )N)r   Z
linregress)	r&   xyZcurpriceslopeZ	interceptZr_valueZp_valueZstd_errr+   r+   r,   regress&   s    zpnp.regressc             C   s   dt | d t | d t | d | d | d | d t | d t | d	 t | d t | d | d | d | d
 t | d	 t | d t | d }t| ttt S )Nzselect minmax.year, minmax.minimum, minmax.maximum, startclose.start, stopclose.stop from (select year, min(close) as minimum, max(close) as maximum from MarketHistory where Contract = 'z' and Crop = 'z' and Date >= (concat(year-z+year('z'),'-',month('z'),'-',day('z%'))) and Date <= (concat(year,'-' , 'zx')) and year >= 1900 group by year) as minmax, (select year, close as start, close from MarketHistory where contract = 'z' and crop = 'z'))) and year >= 1900 group by year order by date asc) as startclose, (select year, close as stop, close from MarketHistory where contract = 'z"' and Date >= (concat(year,'-' , 'z')) group by year order by date desc) as stopclose where startclose.year = minmax.year and stopclose.year = minmax.year order by year asc;)r   pnpcurexecutenpasarraylistfetchall)r&   contractmonthr'   contractyearr*   	startdatesqlr+   r+   r,   r   *   s    
zpnp.getdatac             C   sP   d| d t | d | d t | d }t| ttt d d S )Nz9select min(date), close from MarketHistory where crop = 'z' and year = 'z' and contract = 'z' and date>='z';r   r   )r   r2   r3   r4   r5   r6   r7   )r&   r'   r)   r:   r(   r;   r+   r+   r,   r   6   s    ,
zpnp.getstartpricec             C   s   d| d t | d | d }t| tt d d }|| jk rL|S d| d t | d | d t | j d }t| tt d d }|S d S )Nz2select max(date) from MarketHistory where crop = 'z' and year = 'z' and contract = 'z';r   z' and date <= ')r   r2   r3   r6   r7   r   )r&   r'   r)   r(   r;   r   r+   r+   r,   r"   ;   s     

.
zpnp.getlatestdatec             C   sP   d| d t | d | d t | d }t| ttt d d S )Nz.select close from MarketHistory where crop = 'z' and year = 'z' and contract = 'z' and date = 'z';r   )r   r2   r3   r4   r5   r6   r7   )r&   r'   r)   r(   
latestdater;   r+   r+   r,   r$   G   s    ,
zpnp.getlatestpricec             C   s2   |  |d d df t|d d df t|S )N      )r1   astypefloat)r&   r   
startpricer+   r+   r,   r   L   s    zpnp.getmaxBestFitc             C   s2   |  |d d df t|d d df t|S )Nr=   r   )r1   r?   r@   )r&   r   rA   r+   r+   r,   r   O   s    zpnp.getminBestFitc             C   s6   |t |d d df |d d df k d d d f S )Nr=      r   )r4   where)r&   r   r+   r+   r,   
getupyearsR   s    zpnp.getupyearsc             C   s6   |t |d d df |d d df kd d d f S )Nr=   rB   r   )r4   rC   )r&   r   r+   r+   r,   getdownyearsU   s    zpnp.getdownyearsc          	   C   s"   |  | | j| ||| j|S )N)r   rE   r   r   r   )r&   r'   r(   r)   r+   r+   r,   r   X   s    zpnp.getdownBestFitHighc          	   C   s"   |  | | j| ||| j|S )N)r   rD   r   r   r   )r&   r'   r(   r)   r+   r+   r,   r    [   s    zpnp.getupBestFitLowc             C   s   t  S )N)pnpdbclose)r&   r+   r+   r,   	closeconn^   s    zpnp.closeconnN)__name__
__module____qualname__r-   r1   r   r   r"   r$   r   r   rD   rE   r   r    rH   r+   r+   r+   r,   r      s   r   )__doc__MySQLdbscipyr   numpyr4   timer   r   dateutil.relativedeltar   connectrF   cursorr2   r   r+   r+   r+   r,   <module>   s   