IronPython, SQL Server and Crystal Report to PDF Format.

Using IronPython to manipulate SQL Server and convert a crystal report to pdf format.

This is my two week hack. IDE using Visual Studio 2010, .NET 4, SQL Server 2005 and Crystal Report 10.

import clr
clr.AddReference('System.Data')
clr.AddReference('Microsoft.VisualBasic')
clr.AddReference('CrystalDecisions.CrystalReports.Engine')
clr.AddReference('CrystalDecisions.Shared')

from System.Data.OleDb import OleDbConnection
from System import DateTime
from Microsoft.VisualBasic.Strings import Format
from datetime import datetime
from CrystalDecisions.CrystalReports.Engine import ReportDocument
from CrystalDecisions.Shared import ExportDestinationType
from CrystalDecisions.Shared import DiskFileDestinationOptions
from CrystalDecisions.Shared import ExportFormatType
# from CrystalDecisions.Shared import ConnectionInfo
# from CrystalDecisions.Shared import TableLogOnInfo
import os
import random

def read_ini_file(filename):
    '''
    from
    STDLOCATION=Provider=sqloledb;Data Source=GODELL-SPPB2;Initial Catalog=MDKL_IEMS;User ID=sa;password=xxxxxx;
    USERID=SA
    PASS=xxxxxx
    FIRSTPATH=C:\PDF\
    SECONDPATH=C:\PDF\
    THIRDPATH=C:\PDF\
    FINALPATH=C:\PDF\
    RPTPATH=C:\LETTER\
    FTNTDAYS=13
    SDNTDAYS=13
    TRDTDAYS=13
    
    into
    {'USERID': 'SA\n', 'THIRDPATH': 'C:\\PDF\\\n', 'FTNTDAYS': '13\n', 'FIRSTPATH':
    'C:\\PDF\\\n', 'SECONDPATH': 'C:\\PDF\\\n', 'RPTPATH': 'C:\\LETTER\\\n', 'FINALP
    ATH': 'C:\\PDF\\\n', 'SDNTDAYS': '13\n', 'PASS': 'xxxxxx\n', 'STDLOCATION': 'Pro
    vider=sqloledb;Data Source=GODELL-SPPB2;Initial Catalog=MDKL_IEMS;User ID=sa;pas
    sword=g0dell;\n', 'TRDTDAYS': '13\n'}
    '''
    file = open(filename,'r')
    para = dict( [(line[:line.find('=')],line[line.find('=')+1:].replace('\n','')) for line in file] ) 
    file.close()
    return para    

def get_server_datetime(conn):
    cmd = conn.CreateCommand()
    cmd.CommandText = 'Select Getdate()'
    reader = cmd.ExecuteReader()
    reader.Read()
    with_format = Format(reader[0],'yyyyMMddHHmmssmmm')
    reader.Close()
    return with_format

def get_total_loop(conn,np2):
    cmd = conn.CreateCommand()
    cmd.CommandText = "select count(CompoundNo) as NewCompoundNo from tblCompound " + \
        "Where(IsCancel = 0 And (FirstNotice is not null) and (SecondNotice is not null) and ThirdNotice Is Null)" + \
        "and convert(varchar,compounddate,112) > '20100222'" + \
        "and status = 'NP2'" + \
        "and (datediff(day,secondnotice,getdate())>14 )" + \
        "and vehicleownericno in ('%s')" % np2
    reader = cmd.ExecuteReader()
    reader.Read()
    total_loop = int(reader['NewCompoundNo'])
    if total_loop % 200 == 0:
        total_loop = total_loop / 200
    else:
        total_loop = total_loop / 200 + 1
    reader.Close()
    return total_loop

def generate_np3_pdf(conn,filename, np2, day):
    sql_statement = "Update tblCompound " + \
        "Set ThirdNotice='%s' " + \
        ",TotalCharge=oc.ThirdTotalCharge " + \
        ",ThirdNoticeBy='AutoGenerate',status='NP3' " + \
        ",ThirdNoticeFileName='%s' " + \
        "from admoffencecode oc " + \
        "Where oc.code=tblCompound.offencecode and CompoundNo in (Select Top 200 CompoundNo " + \
        "From tblCompound " + \
        "Where (IsCancel=0 and FirstNotice is not null and SecondNotice is not null ) " + \
        "   And ThirdNotice Is Null " + \
        "   And convert(varchar,compounddate,112) > '20100222'" + \
        "   And Status = 'NP2' " + \
        "   And (datediff(day,secondnotice,getdate())>14 ) " + \
        "   And vehicleownericno in ('%s') " + \
        "   Order By vehicleownericno, CompoundNo) "
    cmd = conn.CreateCommand()
    current_date = DateTime.Now
    date = DateTime(2011,9,day,current_date.Hour,current_date.Minute,current_date.Second)
    cmd.CommandText = sql_statement % (date.ToString(), filename, np2)
    result = cmd.ExecuteNonQuery()
    return result

def calc_running_no(conn, np):
    sql_statement = "SELECT TOP 1 RunningNo FROM tblNoticeLog WHERE(PdfType = '%s') ORDER BY RunningNo DESC"
    cmd = conn.CreateCommand()
    cmd.CommandText = sql_statement % np
    reader = cmd.ExecuteReader()
    if reader.HasRows:
        reader.Read()
        oRunningNo = int(reader['RunningNo']) + 1
    else:
        oRunningNo = 1
    reader.Close()
    return oRunningNo

def insert_third_notice(conn,filename):
    sql_statement = "Select Top 200 CompoundNo From tblCompound Where " + \
        "ThirdNoticeFileName = '%s'"
    cmd = conn.CreateCommand()
    cmd.CommandText = sql_statement % filename
    reader = cmd.ExecuteReader()
    
    compound_num = []
    if reader.HasRows:
        while reader.Read():
            # compound_num = reader['CompoundNo']
            compound_num.append(reader['CompoundNo'])
            
    reader.Close()
            
    for c in compound_num:
        running_no = calc_running_no(conn,'NP3')
        sql_statement = "Insert into tblNoticeLog (PdfFileName,CompoundNo," + \
            "ReferenceNo,RunningNo,PdfType) values('%s','%s','%d',%d,'NP3')"
        cmd.CommandText = sql_statement % (filename, c, running_no, running_no)
        cmd.ExecuteNonQuery()

def export_third_pdf(conn,para,filename,directory, day):
    orOrder = ReportDocument()
    rpt_path = para['RPTPATH'] + 'ThirdNoticeCampaign.rpt'
    print('     Report path is %s' % rpt_path)
    orOrder.Load(rpt_path)
    orOrder.SetParameterValue('@1', filename)
    orOrder.SetParameterValue('@2', DateTime(2011,9,day))
    strpdfdes = directory + '\\' + filename
    print('     Disk File name is %s' % strpdfdes)
    expo = orOrder.ExportOptions
    oDfDopt = DiskFileDestinationOptions()
    oDfDopt.DiskFileName = strpdfdes
    expo.ExportDestinationType = ExportDestinationType.DiskFile
    expo.ExportFormatType = ExportFormatType.PortableDocFormat
    expo.ExportDestinationOptions = oDfDopt
    # ci = ConnectionInfo()
    # ci.ServerName = 'GODELL-SPPB2'
    # ci.DatabaseName = 'MDKL_IEMS'
    # ci.UserID = para['USERID']
    # ci.Password = para['PASS']
    # logininfo = TableLogOnInfo()
    # logininfo.ConnectionInfo = ci
    # for i in orOrder.Database.Tables:
    #     print('     Set table connection')
    #     i.LogOnInfo.ConnectionInfo = ci
    #     i.ApplyLogOnInfo(logininfo)
    orOrder.SetDatabaseLogon(para['USERID'], para['PASS'])
    print('     Start exporting')
    orOrder.Export()
    orOrder.Close()

def get_np2_list(conn):
    sql_statement = "select vehicleownericno, vehicleownername, count(id) as compound_total " + \
        "from tblcompound where status = 'NP2' " + \
        "and convert(varchar,compounddate,112) > '20100222' " + \
        "and (datediff(day,secondnotice,getdate())>14 ) " + \
        "group by vehicleownericno, vehicleownername " + \
        "order by compound_total desc"
    cmd = conn.CreateCommand()
    cmd.CommandText = sql_statement
    reader = cmd.ExecuteReader()
    if reader.HasRows:
        vehicle_num = []
        while reader.Read():
            vehicle_num.append(reader['vehicleownericno'])
            
    reader.Close()
    return vehicle_num

def processing_np3(conn, para, np3_directory, np2, day):
    
    total_loop = get_total_loop(conn,np2)
    
    for loop in range(total_loop):
        print('Generate pdf loop %d for compound %s' % ((loop + 1), np2))
        # server_time = get_server_datetime(conn)
        # print(' Server time is %s' % server_time)
        server_time = datetime.now().replace(day=day)
        np3_pdf_filename = 'THIRDNOTICE%s.pdf' % server_time.strftime('%Y%m%d%I%M%S')
        print(' File name is %s' % np3_pdf_filename)
        result = generate_np3_pdf(conn,np3_pdf_filename,np2,day)
        print(' Result generate np3 is %s' % result)
        if result > 0:
            print(' Start to insert third notice')
            insert_third_notice(conn,np3_pdf_filename)
            print(' Done insert third notice')
            print(' Start to export third pdf')
            export_third_pdf(conn,para,np3_pdf_filename,np3_directory, day)
            print(' Done export third pdf')

def main():
    para = read_ini_file('c:\\GSetting.ini')
    conn = OleDbConnection(para['STDLOCATION'])
    conn.Open()
        
    np2_list = get_np2_list(conn)
    for np2 in np2_list:
        day = random.randrange(19,31)
        if day == 24: day = day - 1 # Saturday
        if day == 25: day = day + 1 # Sunday
        np3_directory = para['THIRDPATH'] + 'THIRDNOTICE' + \
            datetime.now().strftime('%Y%m') + '\\' + str(day)
        if not os.path.exists(np3_directory):
            os.makedirs(np3_directory)
        processing_np3(conn, para, np3_directory, np2, day)
    
    conn.Close()

if __name__ == '__main__':
    main()
    # for r in clr.References:
    #     print r
    i = raw_input('Press any button to continue')

Python read excel and put into MySQL

This program received an excel filename, sheetname, start and end row plus type( for the program to process it correctly).

Usage:

read_excel_to_mysql.py "filename.xls" Sheet1 8 300 new

Browse code below:

import xlrd # http://www.python-excel.org/
import datetime
import MySQLdb # http://sourceforge.net/projects/mysql-python/
import sys
import argparse # http://docs.python.org/library/argparse.html#module-argparse
import traceback

ROW_START = 7
HOST = 'localhost'
USERNAME = 'mrs'
PASSWORD = 'mrs'
DB = 'dbmrsv2'

def xldate_as_date(xld, datemode):
 '''
 i.e 40269.0 -> '2010-04-01 00:00:00'
 '''
 return str(datetime.datetime(
   *xlrd.xldate_as_tuple(
    xld, datemode)))

def xldate_as_time(xld, datemode):
 '''
 i.e '0.4534722222222222' -> '10:53:00'
 %02d will pad with 0X if X is single number
 '''
 t = xlrd.xldate_as_tuple(xld, datemode)
 return "%02d:%02d:%02d" % (t[3],t[4],t[5]) 

def pnd_no(raw_pnd):
 '''
 499.0 -> 0449
 '''
 return "%04d" % int(raw_pnd)

def insert_statement(raw, datemode):
 '''
 i.e:
 [1.0, 40269.0, 0.4534722222222222, u'Hanafi', u'PKR/1APRIL/0007',
 u'PKR', 449.0 ,u'DG', u'Jalan 21/19', 40269.0, 0.46527777777777773,
 0.4673611111111111, 0.4680555555555555, 0.0020833333333333814, 
 0.013888888888888895, 0.014583333333333282, 0.014583333333333282,
 1, u'Red Led', u'Battery Low', u'Replaced Battery', '', 1.0, '', 
 '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 
 1.0, '']

 into

 insert into tb_raw_data (
  date,time,contact_no,serial_no,sources,pnd_no,model,
  road_name,att_date,att_time_start,att_time_finished,
  feedback_time,repair_time,travel_time,down_time,
  response_time,complaint_type,nature,causes,action_taken,
  status,created_by,created_date,modified_by,modified_date 
 ) values (
  '2010-04-01 00:00:00','10:53:00','Hanafi','PKR/1APRIL/0007',
  'PKR','0449','DG','Jalan 21/19','2010-04-01 00:00:00','11:10:00',
  '11:13:00','11:14:00','00:03:00','00:20:00','00:21:00','00:21:00',
  1,'Red Led','Battery Low','Replaced Battery',1,0,
  '2011-06-20 17:59:00',0,'2011-06-20 17:59:00'
 )
 '''
 try:
  date = xldate_as_date(raw[1],datemode)
  time = xldate_as_time(raw[2],datemode)
  contact_no = str(raw[3])
  serial_no = str(raw[4])
  sources = str(raw[5])
  pndno = pnd_no(raw[6])
  qrt_charge = str(raw[7])
  model = str(raw[8])
  road_name = str(raw[9]).replace("'","''")
  att_date = xldate_as_date(raw[10],datemode)
  att_time_start = xldate_as_time(raw[11],datemode)
  att_time_finished = xldate_as_time(raw[12],datemode)
  feedback_time = xldate_as_time(raw[13],datemode)
  repair_time = xldate_as_time(raw[14],datemode)
  travel_time = xldate_as_time(raw[15],datemode)
  down_time = xldate_as_time(raw[16],datemode)
  response_time = xldate_as_time(raw[17],datemode)
  complaint_type = raw[18]
  nature = str(raw[19])
  causes = str(raw[20])
  action_taken = str(raw[21])
  status = 1
  created_by = 0
  created_date = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
  modified_by = 0
  modified_date = created_date

 except:
  raise

 final = """insert into tb_raw_data (date,time,contact_no,serial_no,sources,pnd_no,qrt_charge,model,road_name,att_date,att_time_start,att_time_finished,feedback_time,repair_time,travel_time,down_time,response_time,complaint_type,nature,causes,action_taken,status,created_by,created_date,modified_by,modified_date) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,%d,'%s',%d,'%s')""" % (date,time,contact_no,serial_no,sources,pndno,qrt_charge,model,road_name,att_date,att_time_start,att_time_finished,feedback_time,repair_time,travel_time,down_time,response_time,complaint_type,nature,causes,action_taken,status,created_by,created_date,modified_by,modified_date)

 return final

def insert_statement_old(raw, datemode):
 '''
 i.e:
 [1.0, 40269.0, 0.4534722222222222, u'Hanafi', u'PKR/1APRIL/0007',
 u'PKR', 449.0 ,u'DG', u'Jalan 21/19', 40269.0, 0.46527777777777773,
 0.4673611111111111, 0.4680555555555555, 0.0020833333333333814, 
 0.013888888888888895, 0.014583333333333282, 0.014583333333333282,
 1, u'Red Led', u'Battery Low', u'Replaced Battery', '', 1.0, '', 
 '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 
 1.0, '']

 into

 insert into tb_raw_data (
  date,time,contact_no,serial_no,sources,pnd_no,model,
  road_name,att_date,att_time_start,att_time_finished,
  feedback_time,repair_time,travel_time,down_time,
  response_time,complaint_type,nature,causes,action_taken,
  status,created_by,created_date,modified_by,modified_date 
 ) values (
  '2010-04-01 00:00:00','10:53:00','Hanafi','PKR/1APRIL/0007',
  'PKR','0449','DG','Jalan 21/19','2010-04-01 00:00:00','11:10:00',
  '11:13:00','11:14:00','00:03:00','00:20:00','00:21:00','00:21:00',
  1,'Red Led','Battery Low','Replaced Battery',1,0,
  '2011-06-20 17:59:00',0,'2011-06-20 17:59:00'
 )
 '''
 try:
  date = xldate_as_date(raw[1],datemode)
  time = xldate_as_time(raw[2],datemode)
  contact_no = str(raw[3])
  serial_no = str(raw[4])
  sources = str(raw[5])
  pndno = pnd_no(raw[6])
  model = str(raw[7])
  road_name = str(raw[8]).replace("'","''")
  att_date = xldate_as_date(raw[9],datemode)
  att_time_start = xldate_as_time(raw[10],datemode)
  att_time_finished = xldate_as_time(raw[11],datemode)
  feedback_time = xldate_as_time(raw[12],datemode)
  repair_time = xldate_as_time(raw[13],datemode)
  travel_time = '00:00:00'
  down_time = xldate_as_time(raw[14],datemode)
  response_time = xldate_as_time(raw[15],datemode)
  complaint_type = raw[16]
  nature = str(raw[17])
  causes = str(raw[18])
  action_taken = str(raw[19])
  status = 1
  created_by = 0
  created_date = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
  modified_by = 0
  modified_date = created_date
 except:
  raise

 final = """insert into tb_raw_data (date,time,contact_no,serial_no,sources,pnd_no,model,road_name,att_date,att_time_start,att_time_finished,feedback_time,repair_time,travel_time,down_time,response_time,complaint_type,nature,causes,action_taken,status,created_by,created_date,modified_by,modified_date) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,%d,'%s',%d,'%s')""" % (date,time,contact_no,serial_no,sources,pndno,model,road_name,att_date,att_time_start,att_time_finished,feedback_time,repair_time,travel_time,down_time,response_time,complaint_type,nature,causes,action_taken,status,created_by,created_date,modified_by,modified_date)

 return final

def reg_callb_no(raw_rcn):
 '''
 10041.0 -> 010041
 '''
 return "%06d" % int(raw_rcn)

def insert_statement_pnd(raw):
 '''
 i.e:
 [1.0, 101.0, u'DG', 2000.0, 373653.0, u'Jalan Othman 3/14', 10041.0, 40687.0, u'
 Sek 1,3 & 4', u'MAYBAN FINANCE', u'MPPJ', 23, u'Rusdi', 23, '', '']

 into

 insert into tb_pnd (
  pnd_no,model,body_sn,address,reg_callb_no,area,landmark,council,status
  created_by,created_date,modified_by,modified_date
 ) values (
  '0101','DG','373653','Jalan Othman 3/14','010041','Sek 1,3 & 4','MAYBAN FINANCE','MBPJ',
  1,0,'2011-06-20 17:59:00',0,'2011-06-20 17:59:00'
 )
 '''
 try:
  pndno = pnd_no(raw[1])
  model = str(raw[2])
  body_sn = str(int(raw[4]))
  address = str(raw[5]).replace("'","''")
  lreg_callb_no = reg_callb_no(raw[6])
  area = str(raw[8])
  landmark = str(raw[9]).replace("'","''")
  council = str(raw[10])
  status = 1
  created_by = 0
  created_date = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
  modified_by = 0
  modified_date = created_date
 except:
  raise

 final = """insert into tb_pnd ( pnd_no,model,body_sn,address,reg_callb_no,area,landmark,council,status,created_by,created_date,modified_by,modified_date) values ('%s','%s','%s','%s','%s','%s','%s','%s',%d,%d,'%s',%d,'%s')""" % (pndno,model,body_sn,address,lreg_callb_no,area,landmark,council,status,created_by,created_date,modified_by,modified_date)

 return final

def process_excel(filename, sheet, start_row, end_row, extype):
 '''
 Open excel book and travers through the row own by the sheet name. Then
 send to get the insert statement to be inserted into mysql.
 '''
 wb = xlrd.open_workbook(filename)
 sh = wb.sheet_by_name(sheet)
 clear = True
 
 if sh.nrows > 6:
  db = MySQLdb.connect(host = HOST,user = USERNAME,passwd = PASSWORD,db = DB)
  cur = db.cursor()
  start_row = int(start_row) - 1
  end_row = int(end_row) 
  for rownum in range(start_row,end_row):
   raw = sh.row_values(rownum)
   try:
    if extype == 'new':
     statement = insert_statement(raw, wb.datemode)
    elif extype == 'old':
     statement = insert_statement_old(raw, wb.datemode)
    elif extype == 'pnd':
     statement = insert_statement_pnd(raw)

    cur.execute(statement)
   except:
    db.rollback()
    print ('Point of error is here >>> \n%s' % raw)
    traceback.print_exc()
    clear = False
    break
  if clear: 
   db.commit()

  cur.close()
  db.close()

def main():
 if (len(sys.argv) > 1):
  process_excel(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4], sys.argv[5]) 
 else:
  pass

if __name__ == '__main__':
 main()

VB net console parameter's check sample.

This post is one of my best ways to check console parameter. Check out below:

Module Module1

    Const PARAMVALUECOUNT As Integer = 10 'Refer to function DisplayHelp with param value pair.

    Private username As String
    Private password As String
    Private host As String
    Private catalog As String
    Private files() As String

    Sub Main()

        Dim args() As String = System.Environment.GetCommandLineArgs

        If Not args_is_ok(args) Then
            DisplayHelp()
        Else
            ProcessArgs(args)
            'test_args()

        End If

        Console.ReadLine()

    End Sub

    Sub test_args()

        'TESTING THE ARGS
        Console.WriteLine("Username = " & username)
        Console.WriteLine("Password = " & password)
        Console.WriteLine("Host = " & host)
        Console.WriteLine("Catalog = " & catalog)

        For Each Item As String In files
            Console.WriteLine(Item)
        Next

    End Sub

    Function args_is_ok(ByVal args() As String)

        Dim result As Boolean = False

        If args.Length < PARAMVALUECOUNT Then
            result = False
        Else
            'Refer to function DisplayHelp with param value pair.
            If args.Contains("-u") And args.Contains("-p") And _
                args.Contains("-h") And args.Contains("-f") And _
                args.Contains("-c") Then
                result = True
            Else
                result = False
            End If
        End If

        Return result

    End Function

    Sub ProcessArgs(ByVal args() As String)

        username = args(Array.FindIndex(args, Function(s) s.Contains("-u")) + 1)
        password = args(Array.FindIndex(args, Function(s) s.Contains("-p")) + 1)
        host = args(Array.FindIndex(args, Function(s) s.Contains("-h")) + 1)
        catalog = args(Array.FindIndex(args, Function(s) s.Contains("-c")) + 1)

        Dim filestr As String = args(Array.FindIndex(args, Function(s) s.Contains("-f")) + 1)
        files = filestr.Split(",")

        For index As Integer = 0 To files.Length - 1
            files(index) = Trim(files(index))
        Next

    End Sub

    Sub DisplayHelp()

        Dim help_string As String

        help_string = vbCrLf & "Please follow parameters below:" & vbCrLf
        help_string = help_string & "-u         SQL Server user id" & vbCrLf
        help_string = help_string & "-p         SQL Server user password" & vbCrLf
        help_string = help_string & "-h         SQL Server host name" & vbCrLf
        help_string = help_string & "-c         SQL Server catalog name" & vbCrLf
        help_string = help_string & "-f         file(s) name(s)" & vbCrLf
        help_string = help_string & "" & vbCrLf
        help_string = help_string & "example usage: -u sa -p password -h localhost -c MDKL_IEMS -f ""filename1.txt""" & vbCrLf
        help_string = help_string & "example usage: -u sa -p password -h localhost -c MDKL_IEMS -f ""filename1.txt, filename2.txt""" & vbCrLf

        Console.Write(help_string)

    End Sub


End Module