import pyodbc
import sqlite3
import connection_script

ms_con, ms_cur = connection_script.connect_to_mssql()
lite_con, lite_cur = connection_script.connect_to_sqlite()
lite_cur.execute("select prod_cd from inv_data_bk order by prod_cd")
rows = lite_cur.fetchall()
errors = 0
done = 0

for row in rows:
    prod_cd = row[0]
    ms_cur.execute("select sales_cost from inv where prod_cd='%s';" % (prod_cd))
    ms_row = ms_cur.fetchone()
    lite_cur.execute("update inv_data_bk set sales_cost=%s where prod_cd='%s';" % (ms_row.sales_cost, prod_cd))
    if lite_cur.rowcount:
        done += 1
        print "affected rows:", lite_cur.rowcount
        print str("item=%s, cost=%s, rows done=%s" % (prod_cd, ms_row.sales_cost, float(done * 100) / 7106))

    else:
        errors += 1
        print str("error item=%s, cost=%s" % (prod_cd, ms_row.sales_cost))
lite_con.commit()
lite_con.close()
ms_con.close()
def main():
	now= datetime.datetime.now()
	timestamp= now.strftime("%Y%m%d%H%M%S")#getting the timestamp 
	#creating log file
	logging.basicConfig(filename='db_pass_script.log')
	logging.info('begin at'+timestamp)
	try:
		lite_con,lite_cur=connection_script.connect_to_sqlite()#connect to sqlite3
		ms_con, ms_cur=connection_script.connect_to_mssql()#connect to ms sql
		ssh=connection_script.connect_to_webserver()# connect to unix box
		total_tries=0
		
		#  picking up the newly updated and inserted data from the sqlite3 DB
		lite_cur.execute("select prod_cd,status from inv_data_bk where status<>'Y' and status<>'V' and status<>'E';")
		lite_rows=lite_cur.fetchall()
		for lite_row in lite_rows:
			pass_prod_cd=lite_row[0]
			if pass_prod_cd.find('('):
				pass_prod_cd=pass_prod_cd.replace('(','_-')
			if pass_prod_cd.find(')'):
					pass_prod_cd=pass_prod_cd.replace(')','-_')
					pass_prod_cd=str(pass_prod_cd)
			# get the data from MS SQL
			ms_qry="SELECT i.prod_cd prod_cd,\
				  i.descrip,\
				  i.image_nm pics,\
				  i.class_cd class_cd,\
				  i.sales_cost sales_cost,				  \
				  d.in_stock in_stock,\
				  d.order_qty order_qty\
				FROM omsdata.dbo.inv i,\
				  omsdata.dbo.inv_data d\
				WHERE d.prod_cd=i.prod_cd\
				AND i.prod_cd='%s';"%(lite_row[0])
			ms_cur.execute(ms_qry)
			ms_row=ms_cur.fetchone()#fetch the row
			row_status=lite_row[1]#variable to identify if the row is newly inserted or updated
			cmd='python2.7 '+ settings_script.remote_script 
			i=0# a temporary counter
			#print row_status, 'row status'
			if row_status=='D':
				cmd,i=add_to_cmd(cmd,i,'program_mode','delete')
				cmd,i=add_to_cmd(cmd,i,'prod_cd',pass_prod_cd)
				
			else:
				if row_status=='N':
					cmd,i=add_to_cmd(cmd,i,'program_mode','insert')
				elif row_status=='U':
					cmd,i=add_to_cmd(cmd,i,'program_mode','update')
				#generating command line arguments        
				cmd,i=add_to_cmd(cmd,i,'prod_cd',pass_prod_cd)  
				cmd,i=add_to_cmd(cmd,i,'in_stock',ms_row.in_stock)
				cmd,i=add_to_cmd(cmd,i,'sales_cost',ms_row.sales_cost)
				cmd,i=add_to_cmd(cmd,i,'order_qty',ms_row.order_qty)
				cmd,i=add_to_cmd(cmd,i,'class_cd',ms_row.class_cd)
				cmd,i=add_to_cmd(cmd,i,'descrip','"'+ms_row.descrip+'"')
				cmd=re.sub(' +',' ',cmd)
				
			#executing the linux script
			stdin, stdout, stderr = ssh.exec_command(cmd)
			if stdout:
				stdoutput=stdout.read()
				print stdoutput
				
				prod_cd=re.sub(r'\s', '', ms_row.prod_cd)
				#print stdoutput.find(prod_cd) ,  stdoutput.find('created')
				
				
				
				#check if the output has the string inserted in it just to be sure. 
                                #The script there should not be modified without unless it gives update in the same way
				if (stdoutput.find(prod_cd)!=-1 and  stdoutput.find('created')!=-1):
					lite_cur.execute("update inv_data_bk set  status='Y' where prod_cd =%s"%('"' +lite_row[0]+'"'))
					rc=lite_cur.rowcount
					if rc==1:
							lite_con.commit()
							total_tries+=1
							logging.info('created/updated table:'+lite_row[0])
							print 'created/updated table:'+lite_row[0]
					else:
							print 'unexpected rownum',rc
				elif (stdoutput.find(prod_cd)!=1) and (stdoutput.find('removed from products and products categories')!=-1):
					lite_cur.execute("update inv_data_bk set  status='V' where prod_cd =%s"%('"' +lite_row[0]+'"'))
					rc=lite_cur.rowcount
					if rc==1:
							lite_con.commit()
							total_tries+=1
							logging.info('created/updated table:'+lite_row[0])
							print 'created/updated table:'+lite_row[0]
					else:
							print 'unexpected rownum',rc
					
					
				else:
					print stdoutput
					logging.info('unable to update status:'+lite_row[0]+stdoutput)
					print 'unable ',stdoutput
				

			if stderr:
							print stderr.read()
		#close connections        
		lite_con.close()
		ms_con.close()
		ssh.close()
		print 'total number of updates, inserts  tried:',total_tries
		
		return total_tries
	except KeyboardInterrupt:
                sys.exit()
		raise
	except:
		print 'Unexpected error:',sys.exc_info()
		raise
#       Sync_script.py