export_csv_then_email.ps1

#Connection Strings
$Database = "Database"
$Server = "SQLServer"
#SMTP Relay Server
$SMTPServer = "smtp.domain.com"
#Export File
$AttachmentPath = "C:\SQLData.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT * FROM dbo.Test_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $AttachmentPath
#Send SMTP Message
$Mailer = new-object Net.Mail.SMTPclient($SMTPServer)
$From = "[email protected]"
$To = "[email protected]"
$Subject = "Test Subject"
$Body = "Body Test"
$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($AttachmentPath)
$Msg.attachments.add($Attachment)
$Mailer.send($Msg)

gas_log_generator.py

import random
import datetime
from math import sin

gm = ('SJB', 'JBS', 'KUA', 'DOA', 'ROK', 'SPU', 'WUK', 'COM', 'GLS', 'SUV')

d = datetime.timedelta(seconds=5 * 60)  # setiap 5 menit

p = lambda: 1025.125 + sin(random.randint(0, 360))  # Pressure
t = lambda: 97.756 + sin(random.randint(0, 360))  # Temperature
f = lambda: 322.356 + sin(random.randint(0, 360))  # Flow rate
g = lambda: 331.221 + sin(random.randint(0, 360))  # Energy rate

v = lambda x: x + abs(sin(random.randint(0, 360)))  # Volume
e = lambda x: x + abs(sin(random.randint(0, 360)))  # Energy

params = []

for m in gm:
    mulai = datetime.datetime(2013, 9, 1, 0, 0, 0, 0)
    vol = 222147.5; ene = 226484.796875
    while mulai < datetime.datetime(2013, 10, 15, 0, 0, 0, 0):
        mulai += d
        vol = v(vol); ene = e(ene)
        params.append((m, mulai, p(), t(), f(), g(), vol, ene))
        
# output dari script ini variable params
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server=WIDOYO\SQLEXPRESS;Database=PERTAGAS-SBU;UID=sa;PWD=password')

cursor.executemany("""INSERT INTO energy(gas_meter_id, waktu, pressure, temp, flow_rate, energy_rate, volume, energy) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)""", params)
conn.commit()

models.py

# vim: tabstop=8 expandtab shiftwidth=4 softtabstop=4 autoindent
import os
import datetime

from flask_peewee.auth import BaseUser
from flask_peewee.utils import slugify
import peewee as pw
from peewee import fn
from werkzeug import secure_filename

from app import db, app


class User(BaseUser, db.Model):
    username = pw.CharField()
    password = pw.CharField()
    email = pw.CharField()
    join_date = pw.DateTimeField(default=datetime.datetime.now)
    active = pw.BooleanField(default=True)
    admin = pw.BooleanField(default=False)
    last_login = pw.DateTimeField(required=False)


    def __unicode__(self):
        return self.username

    class Meta:
        db_table = 'authuser'


class BadanUsaha(db.Model):
    '''
    Badan usaha baik sebagai Transpoter maupun Shipper
    '''
    name = pw.CharField(max_length=50)

    def __unicode__(self):
        return self.name

    class Meta:
        db_table = 'badanusaha'


class Node(db.Model):
    '''
    Data Node, obyek simpul ketemuan Ruas
    '''
    owner = pw.ForeignKeyField(BadanUsaha, null=True)
    name = pw.CharField(max_length=50)
    sid = pw.CharField(max_length=20, null=True, verbose_name="Site ID")  # Site ID
    ll = pw.CharField(max_length=75, null=True, verbose_name="Lon Lat")
    installed = pw.DateField(null=True)  # dipasang pada tanggal
    pic = pw.CharField(max_length=50, null=True)  # Petugas lapangan
    desc = pw.TextField(null=True)
    kota = pw.CharField(max_length=50, null=True)
    lokasi = pw.CharField(max_length=50, null=True)
    launched =pw.DateField(null=True)

    def __unicode__(self):
        return self.name

    @property
    def get_absolute_url(self):
        return '/node/%s' % self.id

    class Meta:
        db_table = 'node'


class Transmisi(db.Model):
    '''
    Transmisi terdiri dari ruas-ruas
    '''
    name = pw.CharField(max_length=50)  # Nama Transmisi

    def __unicode__(self):
        return self.name

    class Meta:
        db_table = 'transmisi'


SEGMENT_CATEGORY = (
        ('1', 'Open Access'),
        ('2', 'Dedicated Hulu'),
        ('3', 'Dedicated Hilir'),
)


class Segment(db.Model):
    '''
    Ruas pipa, yang diindentifikasi dari titik terima ke titik serah
    '''
    owner = pw.ForeignKeyField(BadanUsaha)
    transmisi = pw.ForeignKeyField(Transmisi, null=True)
    receiving = pw.ForeignKeyField(Node, related_name='receiving')  # Receiving Point
    delivery = pw.ForeignKeyField(Node, related_name='delivery')  # Delivery Point
    diameter = pw.IntegerField(null=True)
    panjang = pw.FloatField(null=True)
    kategori = pw.CharField(max_length=1, choices=SEGMENT_CATEGORY)
    kapasitas = pw.FloatField(null=True)  # satuan MMSCFD
    ids = pw.CharField(max_length=12, verbose_name="ID Segment", null=True)  # ID 
    tebal = pw.IntegerField(null=True)
    standar = pw.CharField(max_length=30, null=True)  # mis.: "API 5L-X65"
    cdate = pw.DateTimeField(default=datetime.datetime.now)
    mdate = pw.DateTimeField(default=datetime.datetime.now)
    mu = pw.ForeignKeyField(User, related_name='segment_modifier')
    cu = pw.ForeignKeyField(User, related_name='segment_creator')
    slug = pw.CharField()

    def save(self, *args, **kwargs):
        self.slug = slugify(self.__unicode__())
        super(Segment, self).save(*args, **kwargs)

    def __unicode__(self):
        return "%s to %s" % (self.receiving.name, self.delivery.name)

    @property
    def get_absolute_url(self):
        return "/segment/%s-%s/" % (self.id, self.__unicode__())

    class Meta:
        db_table = 'segment'


class SegmentPoint(db.Model):
    segment = pw.ForeignKeyField(Segment)
    ll = pw.CharField()
    step = pw.IntegerField()  # untuk urutan, dari Receiving Point (kecil) ke Delivery Point (besar)
    name = pw.CharField(max_length=50, null=True)

    def __unicode__(self):
        return "%s %s" % (self.segment, self.step)

    class Meta:
        db_table = 'segment_point'


METER_POINT_CHOICES = (
        ('RP', 'Receipt Point'),
        ('F', 'Fuel'),  # untuk bahan bakar compressor
        ('DP', 'Delivery Point'),
)

class GasMeter(db.Model):
    '''
    Object Metering Station
    '''
    bu_id = pw.CharField(max_length=20, null=True)  # kode Gas Meter pada Badan Usaha
    segment = pw.ForeignKeyField(Segment)  # Gas Meter ini nempel pada Segment mana
    kategori = pw.CharField(max_length=2, choices=METER_POINT_CHOICES)
    ll = pw.CharField(max_length=75, null=True, verbose_name="Lon Lat")

    def __unicode__(self):
        return self.bu_id

    class Meta:
        db_table = 'gasmeter'


class Contract(db.Model):
    pihak_1 = pw.ForeignKeyField(BadanUsaha, related_name='contract_pihak_1')
    pihak_2 = pw.ForeignKeyField(BadanUsaha, related_name='contract_pihak_2')
    no = pw.CharField(max_length=20)
    tanggal = pw.DateField()
    akhir = pw.DateField()

    class Meta:
        db_table = 'contract'


class Mlog(db.Model):
    '''
    Measurement Log
    Catatan hasil pengukuran, pada Metering Station
    '''
    ms = pw.ForeignKeyField(GasMeter)  # Ujung yang mana (RP & DP)
    pressure = pw.FloatField(default=0)
    temperature = pw.FloatField(default=0)
    flow_rate = pw.FloatField(default=0)
    energy_rate = pw.FloatField(default=0)
    volume = pw.FloatField(default=0)
    energy = pw.FloatField(default=0)
    waktu = pw.DateTimeField()
    cdate = pw.DateTimeField(default=datetime.datetime.now)

    class Meta:
        db_table = 'mlog'


class TransportLog(db.Model):
    '''
    BU (Transporter, Shipper, Offtaker) 
    yang terlibat pada transportasi gas ini
    '''
    segment = pw.ForeignKeyField(Segment)
    start = pw.DateTimeField()  # Waktu Mulai
    stop = pw.DateTimeField()  # Waktu Akhir

    class Meta:
        db_table = 'tlog'


class TransItem(db.Model):
    tlog = pw.ForeignKeyField(TransportLog)
    shipper = pw.ForeignKeyField(BadanUsaha, related_name='item_shipper')
    offtaker = pw.ForeignKeyField(BadanUsaha, related_name='item_offtaker')
    nominasi = pw.FloatField()  # Nominasi Quantity
    proportion = pw.FloatField()  # musthinya dalam persen
    contract = pw.ForeignKeyField(Contract)

    class Meta:
        db_table = 'titem'

OBJECT_TYPE = (
        (1, 'Node'),
        (2, 'Segment'),
        (3, 'GasMeter'),
        (4, 'Transmisi'),
        )

class Note(db.Model):
    '''
    Catatan terhadap 'object'
    '''
    object_type = pw.IntegerField()  # refer ke OBJECT_TYPE
    object_id = pw.IntegerField()
    note = pw.TextField()
    user = pw.ForeignKeyField(User)
    event_time = pw.DateTimeField(default=datetime.datetime.now)

    class Meta:
        db_table = 'note'


class Ofile(db.Model):
    '''
    Object File (berkas)
    Memuat foto Site, dokumen kontrak
    '''
    object_type = pw.IntegerField()  # refer ke OBJECT_TYPE
    object_id = pw.IntegerField()
    image = pw.CharField()
    desc = pw.TextField(null=True)

    def __unicode__(self):
        return self.image

    def save_image(self, file_obj):
        self.image = secure_filename(file_obj.filename)
        full_path = os.path.join(app.config['MEDIA_ROOT'], self.image)
        file_obj.save(full_path)
        self.save()

    def url(self):
        return os.path.join(app.config['MEDIA_URL'], self.image)

    class Meta:
        db_table = 'ofile'


class Wilayah(db.Model):
    '''
    Data bantu untuk View Wilayah dg Google Map 
    '''
    name = pw.CharField()
    zoom = pw.IntegerField()  
    center = pw.CharField()

    def __unicode__(self):
        return self.name

    class Meta:
        db_table = 'map_wilayah'

to_bphmigas.py

'''
Mengambil data gas meter

output file "gm_20130901.csv"

Widoyo

'''
import os
import datetime
import csv
import pyodbc


SERVER = 'WIDOYO\SQLEXPRESS'
DB_NAME = 'PERTAGAS-SBU'
UID = 'sa'
PWD = 'password'

DATA_PATH = 'C:/Users/widoyo/'

def ambil_gas_meter_kemarin():
    '''
    Mengambmbil data
    '''
    kemarin = datetime.date.today() - datetime.timedelta(days=1)
    filename = kemarin.strftime('%Y%m%d')
    
    conn = pyodbc.connect(
        'Driver={SQL Server};Server=%s;Database=%s;UID=%s;PWD=%s' % (
            SERVER, DB_NAME, UID, PWD))
    cursor = conn.cursor()
    sql = """
        SELECT
            gas_meter_id,
            waktu,
            pressure,
            temp,
            flow_rate,
            energy_rate,
            volume,
            energy
        FROM
            energy
        WHERE
            (DATEPART(year, waktu) = ?) AND
            (DATEPART(month, waktu) = ?) AND
            (DATEPART(day, waktu) = ?)
        """
    params = (kemarin.year, kemarin.month, kemarin.day)
    cursor.execute(sql, params)

    file_output = os.path.join(DATA_PATH, 'gm_%s.csv' % filename)
    writer = csv.writer(open(file_output, 'w'))
    for row in cursor.fetchall():
        writer.writerow(row)


if __name__ == '__main__':
    ambil_gas_meter_kemarin()

upload_SFTP.ps1

#Snapin : cmdlet untuk komunikasi dengan SFTP Server.
# download:
# http://www.k-tools.nl/index.php/download-sftp-powershell-snap-in/

#Add the SFTP snap-in
Add-PSSnapin KTools.PowerShell.SFTP

#Define some variables
$sftpHost = "bphmigas.waditra.com"
$userName = ""
$userPassword = ""
$localFile = "C:\Users\File\gm_20130901.csv"

#Open the SFTP connection
$sftp = Open-SFTPServer -serverAddress $sftpHost -userName $userName -userPassword $userPassword

#Upload the local file to the root folder on the SFTP server
$sftp.Put($localFile)

#Close the SFTP connection
$sftp.Close()