def get_positions_count(userids):
dtt = form.getvalue("dt","-1");
dttt = []
dttt = dtt.split("mm")
dtt = "between '"+py_db.sh_to_mil(dttt[0])+" "+dttt[1]+":00' and '"+py_db.sh_to_mil(dttt[2])+" "+dttt[3]+":00' "
sqlst = "select count(*) from `tbl_gps_positions` WHERE g_sat_count > 5 and g_user_id in ({1}) and g_date {0} ".format(dtt,userids)
print 'Content-Type: text/html; charset=utf-8'
print ""
print py_db.executeAndReturnRows(sqlst)[0][0].__str__()
def create_docx():
rows = py_db.executeAndReturnRows("select * from tbl_irib_olaviats order by io_id")
str = ""
for row in rows:
str = str+"<tr class='tr__1'><td colspan=3>{1}</td></tr>".format(row[0],row[1])
_rows = py_db.executeAndReturnRows("select * from tbl_irib_olaviats_mozoo where iom_link = '{0}' order by iom_id".format(row[0]))
for _row in _rows:
str = str+"<tr class='tr__2'><td>{0}</td><td colspan=2>{1}</td></tr>".format(_row[0],_row[1])
__rows = py_db.executeAndReturnRows("select * from tbl_irib_olaviats_mozoo_mehvarz where iomm_link = '{0}' order by iomm_id".format(_row[0]))
for __row in __rows:
str = str+"<tr class='tr__3'><td>{0}</td><td>{1}</td><td>{2}</td></tr>".format(_row[0],__row[0],__row[1])
str = "<table class='tbl_report'><tbody>{0}</tbody></table>".format(str)
print str
def checkifu_comp_name_is(param5):
rows = py_db.executeAndReturnRows('''
select count(*) from tbl_users where u_complete_name='{0}' '''.format(param5))
print 'Content-Type: text/html; charset=utf-8'
print ''
if rows[0][0]==1:print "0"
if rows[0][0]==0:print "1"
def checkif_doc_scanned(_param14):
row = py_db.executeAndReturnRows("select lp_address from tbl_letters_papers where lp_type=2 and lp_link_to_letters="+_param14)
str="0"
if len(row) <> 0: str=row[0][0]
print 'Content-Type: text/html; charset=utf-8'
print ''
print str
def sendonedate(uname):
sqlstring='''
select
`u_name`,
`u_pass`,
`u_complete_name`,
`u_org_post`,
`u_active`,
`u_has_assist`,
(select t2.u_complete_name from tbl_users t2 where t2.u_id=t1.u_link_to_user_assistant) as cmname ,
`u_direct`,
u_secretariat_group_link
from tbl_users t1 where u_name = '{0}' limit 1
'''.format(uname)
rows = py_db.executeAndReturnRows(sqlstring)
str=""
for row in rows:
for r in row:
str=str+"::"+r.__str__()
str=str.replace("::None","::")
print 'Content-Type: text/html; charset=utf-8'
print ''
print str
def send_users_list():
sql='''
SELECT
u_id,
u_complete_name,
u_org_post,
u_has_assist,
(SELECT `u_complete_name` FROM `tbl_users` t2 WHERE t2.u_id= t1.u_link_to_user_assistant) AS sssdsd
FROM tbl_users t1
where u_id not in ( select uh_user_id from tbl_users_hidden )
and u_id in (SELECT DISTINCT t1.`urgm_link_to_users` FROM tbl_users_related_groups_member t1 WHERE t1.urgm_link_to_groups IN (SELECT DISTINCT t2.urgm_link_to_groups FROM tbl_users_related_groups_member t2 WHERE t2.`urgm_link_to_users`={0}))
and u_id not in ({0})
order by u_complete_name
'''.format(py_db.user_id)
print sql
rows = py_db.executeAndReturnRows(sql)
txt = ""
for row in rows:
txt=txt+"|"
for r in row:
txt = txt+"::"+r.__str__()
print 'Content-Type: text/html; charset=utf-8'
print ''
print txt
def send_usr_premission_lst(param):
py_db.checkAccessCookie()
py_db.checkuser(5,py_db.username)
sqlstr = """
SELECT
t1.m_id,
t1.m_page_title ,
CASE
(SELECT t2.m_type_of_view
FROM tbl_module_premission t2
WHERE t2.m_user_link = {0} AND t2.m_module_link= t1.`m_id`)
WHEN 1 THEN "true"
ELSE "false"
END AS sss
FROM tbl_modules t1
""".format( py_db.get_userID_from_uname(param) )
rows = py_db.executeAndReturnRows(sqlstr)
str = ""
for row in rows:
str = str+"::"+row[0].__str__()+"pp"+row[1].__str__()+"pp"+row[2].__str__()
print 'Content-Type: text/html; charset=utf-8'
print ''
print str
def insert(str):
_str = str.split("__")
uid = -1
if _str[12].split("ffdd")[1] == "private":
uid = py_db.user_id
sqlstr = """
INSERT INTO `automation`.`tbl_tels`
(
`t_sazman`,
`t_name`,
`t_semat`,
`t_mozoo`,
`t_tel`,
`t_fax`,
`t_mob`,
`t_email`,
`t_site`,
`t_address`,
`t_exp`,
t_private_user_id)
VALUES (
'{0}',
'{1}',
'{2}',
'{3}',
'{4}',
'{5}',
'{6}',
'{7}',
'{8}',
'{9}',
'{10}',
'{11}'
);
""".format(_str[1].split("ffdd")[1],
_str[2].split("ffdd")[1],
_str[3].split("ffdd")[1],
_str[4].split("ffdd")[1],
_str[5].split("ffdd")[1],
_str[6].split("ffdd")[1],
_str[7].split("ffdd")[1],
_str[8].split("ffdd")[1],
_str[9].split("ffdd")[1],
_str[10].split("ffdd")[1],
_str[11].split("ffdd")[1],
uid,
)
# print sqlstr
py_db.executesql(sqlstr)
ss = "select t_id from tbl_tels where t_sazman = '{0}' order by t_id desc limit 1".format(_str[1].split("ffdd")[1])
print "ok__"+py_db.executeAndReturnRows(ss)[0][0].__str__()
def get_company_names():
rows = py_db.executeAndReturnRows("SELECT `scn_name` FROM `tbl_sale_company_names` ORDER BY SCN_NAME DESC")
s = ""
for row in rows:
s = s + "jotl" + row.__str__()
print "Content-Type: text/html"
print
print s
def GetVar():
strlist=""
rows = py_db.executeAndReturnRows("select dsc_name from tbl_variables where dsc_type=7 order by dsc_name")
for row in rows :
if strlist == "" :
strlist = row[0]
else:
strlist = strlist+"zzhtc"+row[0]
rows = py_db.executeAndReturnRows('SELECT DISTINCT `i_order` FROM `tbl_inbox` WHERE i_prev_active_user_link='+py_db.user_id+' AND i_order <>"" ORDER BY i_id desc limit 5')
for row in rows :
if strlist == "" :
strlist = row[0]
else:
strlist = strlist+"zzhtc"+row[0]
print 'Content-Type: text/html; charset=utf-8'
print ''
print strlist
def getErjaList(param2):
rows = py_db.executeAndReturnRows(
'''
SELECT
t1.i_date_of_create,
t1.i_date_of_seen,
(SELECT t2.u_complete_name FROM tbl_users t2 WHERE t2.u_id=t1.i_active_user_link LIMIT 1) AS current,
(SELECT t2.u_complete_name FROM tbl_users t2 WHERE t2.u_id=t1.`i_prev_active_user_link` LIMIT 1) AS lastuser,
t1.i_order,
CASE TRUE
WHEN t1.`i_erja_type` = 1 THEN "معمولی"
WHEN t1.i_erja_type = 2 THEN "فوری"
WHEN t1.i_erja_type = 3 THEN "خیلی فوری"
END AS hello
,
CASE TRUE
WHEN t1.i_position = 1 THEN "خوانده نشده"
WHEN t1.i_position = 2 THEN "خوانده شده"
WHEN t1.i_position = 3 THEN "ارجاع شده"
WHEN t1.i_position = 4 THEN "بایگانی شده"
END AS hello
FROM tbl_inbox t1
WHERE
t1.i_letter_link = {0} AND
t1.i_erja_type=1
ORDER BY t1.i_id asc
'''.format(param2)
);
i=0
str = "<div>"
for row in rows:
s=""
if i==0:
s="<div>"+"ثبت در سیستم مورخه "+":"+py_db.mil_to_sh_with_time(row[0].strftime("%Y-%m-%d %H:%M:%S"))+"<br>"+" توسط "+ row[2]+":"+ "</div>"+"<hr>"
s=s
else:
s="<div> با هامش "+":"+row[4]+"<br>"+" ارسال شده به "+":"+row[2]+"<br>"+" در تاریخ "+":"+ py_db.mil_to_sh_with_time(row[0].strftime("%Y-%m-%d %H:%M:%S"))+"<br>"+" وضعیت "+":"+row[6]+"</div>ارجاع کننده : "+row[3]+"<hr>"
s=s
#dd =
str=str+s
i=i+1
str=str+"</div>"
print 'Content-Type: text/html; charset=utf-8'
print ''
print str
def send_table_data():
rows = py_db.executeAndReturnRows("select * from tbl_tels where (t_private_user_id = '{0}' or t_private_user_id = '-1') order by t_sazman,t_name".format(py_db.user_id))
tbl_str = ''
for row in rows:
if tbl_str == '':
tbl_str = '{0},,,{1},,,{2},,,{3},,,{4},,,{5},,,{6},,,{7},,,{8},,,{9},,,{10},,,{11},,,{12}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],)
else: tbl_str = tbl_str+"li_"+'{0},,,{1},,,{2},,,{3},,,{4},,,{5},,,{6},,,{7},,,{8},,,{9},,,{10},,,{11},,,{12}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],)
tbl_str = tbl_str.replace("None","")
print tbl_str
def getall():
_str=""
rows = py_db.executeAndReturnRows("select u_complete_name,u_id,u_org_post from tbl_users order by u_name")
for row in rows:
if _str<>"":
_str=_str+"dressa"+row[0].__str__()+"sxww"+row[1].__str__()+"sxww"+row[2].__str__()
else:
_str="dressa"+row[0].__str__()+"sxww"+row[1].__str__()+"sxww"+row[2].__str__()
print 'Content-Type: text/html; charset=utf-8'
print ''
print _str
def sendusersum():
py_db.checkAccessCookie()
sqlstr = """
SELECT COUNT(*) FROM tbl_inbox WHERE `i_position`=1 AND `i_active_user_link`={0}
""".format(py_db.user_id)
row = py_db.executeAndReturnRows(sqlstr)
print 'Content-Type: text/html; charset=utf-8'
print ''
print row[0][0]
def isItOkToErja(param3):
py_db.checkAccessCookie()
userID = py_db.user_id;
sqlstr = """
select i_position from tbl_inbox where i_letter_link = {0} and i_active_user_link={1} order by i_id desc limit 1
""".format(param3,userID)
rows = py_db.executeAndReturnRows(sqlstr)
str = ""
str = rows[0][0].__str__()
print 'Content-Type: text/html; charset=utf-8'
print ''
print str
def answ(name,items):
py_db.executesql("insert into tbl_users_related_groups (urg_title) values ('{0}');".format(name))
row = py_db.executeAndReturnRows("select urg_id from tbl_users_related_groups where urg_title='{0}'".format(name));
f=[]
f=items.split("gfhjdg");
print 'Content-Type: text/html; charset=utf-8'
print ''
for item in f:
py_db.executesql("insert into tbl_users_related_groups_member (urgm_link_to_users,urgm_link_to_groups) values ({0},{1})".format(item,row[0][0]))
# print("insert into tbl_users_related_groups_member (urgm_link_to_users,urgm_link_to_groups) values ({0},{1})".format(item,row[0][0]))
print "ok"
def get_negahban_list():
rows = py_db.executeAndReturnRows("SELECT * FROM `tbl_users` WHERE `u_org_post` LIKE '%نگهبان%'")
s=""
i=0
for row in rows:
if i==0:
s=row[0].__str__()+"="+row[4].__str__()
else:
s=s+"vfdd"+row[0].__str__()+"="+row[4].__str__()
i+=1
print 'Content-Type: text/html; charset=utf-8'
print ''
print s
def get_sec_list():
rows = py_db.executeAndReturnRows("""
SELECT DISTINCT(s_group),`s_group_title`
FROM `tbl_secretariat`
"""
);
stt = ""
for row in rows:
stt = stt+":::"+row[0].__str__()+"ppp"+row[1].__str__()
print 'Content-Type: text/html; charset=utf-8'
print ''
print stt
def get_pos(sel2):
dtt = form.getvalue("dt2","-1");
ttl = form.getvalue("totalrows","-1")
dttt = []
dttt = dtt.split("mm")
dtt = "between '"+py_db.sh_to_mil(dttt[0])+" "+dttt[1]+":00' and '"+py_db.sh_to_mil(dttt[2])+" "+dttt[3]+":00' "
sqlst ="""SELECT w.* FROM (SELECT @row := @row + 1 AS _ROW, t.* FROM `tbl_gps_positions` t,(SELECT @row:=0) r WHERE g_sat_count > 5 and g_user_id IN ({0}) AND g_date {1} ORDER BY g_date DESC ) w WHERE MOD(_ROW,{2})=0 """.format(sel2,dtt,ttl)
rows = py_db.executeAndReturnRows(sqlst)
str = ""
i=0
for row in rows :
if i==0:
str = "{0}!{1}!{2}!{3}!{4}".format(row[2].__str__(),row[3].__str__(),row[4].__str__(), py_db.mil_to_sh_with_time(row[6].__str__()),row[7].__str__())
else:
str = str+"cz{0}!{1}!{2}!{3}!{4}".format(row[2].__str__(),row[3].__str__(),row[4].__str__(),py_db.mil_to_sh_with_time(row[6].__str__()),row[7].__str__())
i+=1
print 'Content-Type: text/html; charset=utf-8'
print ""
print str
cgitb.enable(display=0, logdir="c:\cgilog.txt")
def is_number(s):
try:
float(s)
return True
except ValueError:
return False
rows = py_db.executeAndReturnRows("select * from sheet2")
b=0
i=0
for row in rows:
if row[4] is not None:
if len(row[4].__str__())>3:
py_db.executesql("update sheet2 set cd3='{0}' , num4='1' where id = '{1}'".format(row[4],row[0]))
i=i+1
print "update sheet2 set cd3='{0}' where id = '{1}'".format(row[4],row[0])
#
#for row in rows:
# dr = []
# dr = row[1].split('_')
#
def handlethis(param1,param2):
rows = py_db.executeAndReturnRows("select count(*) from tbl_inbox where i_active_user_link = (select u_id from tbl_users where u_name='{0}' and u_pass = '******') and i_position=1".format(param1,param2))
print 'Content-Type: text/html; charset=utf-8'
print ''
print rows[0][0].__str__()
def do_insert(_param):
print "Content-Type: text/html"
print
sqlstr = ""
main_rows = _param.split("[[[a")
data_field = main_rows[0].split("91A1")
sqlstr = "'{0}','{1}','{2}','{3}'".format(
chk_n(data_field[0]), chk_n(data_field[1]), chk_n(data_field[2]), chk_n(data_field[3])
)
fs = """
INSERT INTO `automation`.`tbl_sale_kharidha`
(`sk_link_to_company`,
`sk_namayande_sherkat`,
`sk_link_faaliat`,
`sk_tel_no`)
VALUES (
{0});
""".format(
sqlstr
)
py_db.executesql(fs)
new_id = py_db.executeAndReturnRows("select sk_id from tbl_sale_kharidha order by sk_id desc limit 1")[0][
0
].__str__()
sqlstr_details = " DELIMITER ; "
if main_rows[1] == "":
print "no_item"
return
for row in main_rows[1].split("al12c"):
row_arr = row.split(",")
py_db.executesql(
"""
insert into `automation`.`tbl_sale_kharidha_details`
(
`skd_link_to_kharid_ha`,
`skd_zekhamate_varagh`,
`skd_arz`,
`skd_tool`,
`skd_mizan`,
`skd_zekhamte_ghal`,
`skd_shekle_varagh`,
skd_date_kharid)
values (
'{0}',
'{1}',
'{2}',
'{3}',
'{4}',
'{5}',
'{6}',
'{7}');
""".format(
new_id,
chk_n(row_arr[0]),
chk_n(row_arr[1]),
chk_n(row_arr[2]),
chk_n(row_arr[3]),
chk_n(row_arr[4]),
chk_n(row_arr[5]),
chk_n(row_arr[6]),
)
)
# al12c
print "ok"
def create_excel():
from tempfile import TemporaryFile
from xlwt import Workbook
from xlwt import *
book = Workbook()
sheet1 = book.add_sheet("Sheet 1")
book.add_sheet("Sheet 2")
sheet1.write(0, 0, "نام شرکت")
sheet1.write(0, 1, "تاریخ خرید")
sheet1.write(0, 2, "ضخامت")
sheet1.write(0, 3, "عرض")
sheet1.write(0, 4, "طول")
sheet1.write(0, 5, "میزان کیلوگرم")
sheet1.write(0, 6, "ضخامت قلع")
sheet1.write(0, 7, "شکل ورق")
rows = py_db.executeAndReturnRows(
"""
SELECT
(SELECT `sk_link_to_company` FROM `tbl_sale_kharidha` WHERE `sk_id`=`skd_link_to_kharid_ha`) AS kha,
`skd_date_kharid`,
`skd_zekhamate_varagh`,
`skd_arz`,
`skd_tool`,
`skd_mizan`,
`skd_zekhamte_ghal`,
`skd_shekle_varagh`
FROM `tbl_sale_kharidha_details`
ORDER BY kha, skd_date_kharid
"""
)
i = 1
for row in rows:
row1 = sheet1.row(i)
row1.write(0, row[0].__str__())
row1.write(1, row[1].__str__())
row1.write(2, Formula("VALUE(" + row[2].__str__() + ")"))
row1.write(3, Formula("VALUE(" + row[3].__str__() + ")"))
row1.write(4, Formula("VALUE(" + row[4].__str__() + ")"))
row1.write(5, Formula("VALUE(" + row[5].__str__() + ")"))
row1.write(6, Formula("VALUE(" + row[6].__str__() + ")"))
row1.write(7, Formula("VALUE(" + row[7].__str__() + ")"))
i += 1
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, "Sheet 2 A1")
sheet2.row(0).write(1, "Sheet 2 B1")
sheet2.flush_row_data()
sheet2.write(1, 0, "Sheet 2 A3")
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save("simple.xls")
book.save(TemporaryFile())
print "Content-Type: text/html"
print
print "ok"
def checkdupusername(param4):
rows = py_db.executeAndReturnRows('''
select count(*) from tbl_users where u_name='{0}' '''.format(param4))
print 'Content-Type: text/html; charset=utf-8'
print ''
print rows[0][0]
