def get_crmcaselist(request):
bytype = request.GET['bytype']
company = request.GET['company']
storecode = request.GET['storecode']
status = request.GET['status']
if bytype == 'vipcrmcase':
vipuuid = request.GET['vipuuid']
if company in common.constants.COMPANYLIST_WITHOUT_MTCODE:
sql = " select a.uuid uuid, casetype, c.viptype viptye, vipuuid,c.vcode vcode,c.vname vname, '' mtcode,c.birth,c.indate," \
" b.ecode ecode, b.ename ename, a.status status, finishedate, planbegindate, planfinishdate,casedesc, vsdate" \
" from crmcase a, empl b, vip c" \
" where a.company = b.company and a.ecode = b.ecode " \
" and a.vipuuid = c.uuid and a.company =%s and a.storecode =%s and a.status=%s and a.vipuuid=%s" \
" order by casetype, planfinishdate"
else:
sql = " select a.uuid uuid, casetype, c.viptype viptye, vipuuid,c.vcode vcode,c.vname vname, c.mtcode mtcode,c.birth,c.indate," \
" b.ecode ecode, b.ename ename, a.status status, finishedate, planbegindate, planfinishdate,casedesc, vsdate" \
" from crmcase a, empl b, vip c" \
" where a.company = b.company and a.ecode = b.ecode " \
" and a.vipuuid = c.uuid and a.company =%s and a.storecode =%s and a.status=%s and a.vipuuid=%s" \
" order by casetype, planfinishdate"
params = (company + ' ' + storecode + ' ' + status + ' ' +
vipuuid).split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(json_data)
return HttpResponse(json_data, content_type="application/json")
if bytype == 'crmcase':
ecode = request.GET['ecode']
planbegindate = request.GET['planbegindate']
if company in common.constants.COMPANYLIST_WITHOUT_MTCODE:
sql = " select a.uuid uuid, casetype, c.viptype viptye, vipuuid,c.vcode vcode,c.vname vname, '' mtcode,c.birth,c.indate," \
" b.ecode ecode, b.ename ename," \
" a.status status, finishedate, planbegindate,planfinishdate, casedesc, vsdate" \
" from crmcase a, empl b, vip c" \
" where a.company = b.company and a.ecode = b.ecode " \
" and a.vipuuid = c.uuid and a.company =%s and a.storecode =%s and a.ecodelist like concat('%%',%s,'%%') and a.planbegindate <= %s and a.planfinishdate >= %s and a.status=%s" \
" order by casetype, planfinishdate"
else:
sql = " select a.uuid uuid, casetype, c.viptype viptye, vipuuid,c.vcode vcode,c.vname vname, c.mtcode mtcode,c.birth,c.indate," \
" b.ecode ecode, b.ename ename," \
" a.status status, finishedate, planbegindate,planfinishdate, casedesc, vsdate" \
" from crmcase a, empl b, vip c" \
" where a.company = b.company and a.ecode = b.ecode " \
" and a.vipuuid = c.uuid and a.company =%s and a.storecode =%s and a.ecodelist like concat('%%',%s,'%%') and a.planbegindate <= %s and a.planfinishdate >= %s and a.status=%s" \
" order by casetype, planfinishdate"
params = (company + ' ' + storecode + ' ' + ecode + ' ' +
planbegindate + ' ' + planbegindate + ' ' + status).split()
print(sql, params)
json_data = sql_to_json(sql, params)
# print(json_data)
return HttpResponse(json_data, content_type="application/json")
def get_vipconsumelist(request):
company = request.GET['company']
vipuuid = request.GET['vipuuid'].replace('-', '')
try:
fromdate = request.GET['fromdate']
except:
fromdate = '20180101'
try:
todate = request.GET['todate']
except:
todate = '20991231'
fromdate = '201801011'
todate = '20991231'
sql = " select a.uuid transuuid, a.ccode ccode, getcardtypename(getcardtype(a.ccode,a.company),a.company) cardname, a.vsdate vsdate, "\
" f_getnamebysrvcode(b.srvcode, b.ttype, b.company) itemname, b.s_qty s_qty, b.s_price s_price, b.SECDISC secdisc, "\
" b.s_mount amount, b.pmcode pmcode, b.ASSCODE1 seccode, b.ASSCODE2 thrcode, "\
" getemplinfo(a.company,b.pmcode,'ename') pmname,getemplinfo(a.company,b.asscode1,'ename') secname, getemplinfo(a.company,b.asscode2,'ename') thrname,"\
" ( case b.ttype when 'S' then '服务' when 'G' then '商品' when 'C' then '售卡' when 'I' then '充值' else '其他' end ) ttype," \
" ( case b.stype when 'N' then '正常' when 'P' then '赠送' else '其他' end ) stype ,Getexptxpaydesc2(a.uuid, a.storecode, a.company) exptxpaydesc" \
" from expvstoll a, expense b, vip c"\
" where 1=1 and a.valiflag='Y' "\
" and a.uuid = b.transuuid and a.vipuuid = c.uuid"\
" and a.company = %s AND a.vipuuid = %s and vsdate between %s and %s" \
" order by a.vsdate desc, a.ccode "
params = (company + ' ' + vipuuid + ' ' + fromdate + ' ' + todate).split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(json_data)
return HttpResponse(json_data, content_type="application/json")
def get_planvipcasedetail_byecode(request):
company = request.GET.get('company', 'demo')
print('company=', company)
try:
vipuuid = request.GET.get('vipuuid', '').replace('-', '')
print('vipuuid=', vipuuid)
except:
vipuuid = ''
try:
nextecode = request.GET.get('ecode', '')
except:
nextecode = ''
nextdate_s = request.GET.get('nextdate', '')
print('nextdate_s', nextdate_s)
nextdate = datetime.datetime.strptime(nextdate_s, '%Y-%m-%d')
print('nextdate', nextdate)
sql = " select a.uuid , a.casetype , a.detail, a.ecode ,DATE(a.create_time) created_date, TIME (a.create_time) created_time,nextdate,nextecode,a.status," \
" a.vipuuid, b.vcode,b.vname, b.mtcode "\
" from vipcasedetail a, vip b"\
" where 1=1 and a.flag='Y' and b.flag='Y' " \
" and a.company=b.company and a.vipuuid = b.uuid"\
" and a.company = %s AND a.nextecode = %s and nextdate=date(%s)" \
" order by a.create_time desc "\
params = (company + ' ' + nextecode + ' ' + nextdate_s).split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(json_data)
return HttpResponse(json_data, content_type="application/json")
def get_onlinegoodslist(self, **kwargs):
self.brand = kwargs.get('brand', '%')
sql = "select uuid, gcode, gname, brand, price,small_image, large_image from goods where flag='Y' and saleflag='Y' and company=%s AND brand = %s and saleschannels like '%20%' "
params = (self.company + ' ' + self.brand).split()
print('sql', sql)
json_data = sql_to_json(sql, params)
return json_data
def Get_WechatApp_Function(request):
where = '1=1 '
try:
appcode = request.GET['appcode']
except:
appcode = '100'
try:
company = request.GET['company']
# where = ' and company='+company
except:
company = common.constants.COMPANYID
# where = ' and company='+company
try:
storecode = request.GET['storecode']
except:
storecode = ''
try:
functiontype = request.GET['type']
except:
functiontype = '100'
try:
ecode = request.GET['ecode']
except:
ecode = ''
try:
wxusertype = request.GET['wxusertype']
except:
wxusertype = '100'
try:
functionid = request.GET['functionid']
except:
functionid = ''
print('Get_WechatApp_Function request,company=', company, ',appcode=',
appcode, wxusertype, functiontype, functionid)
# functions = json.dumps(WechatAppFunctions.objects.filter(flag='Y',valiflag='Y',company=company,functionid=functionid).values('id','text','url','image'))
# print('functions=',functions)
sql = " select id,text,url,image" \
" from WechatAppFunctions " \
" where flag='Y' and valiflag ='Y' and company=%s AND appcode=%s and wxusertype = %s and functionid=%s" \
" order by id asc"
params = (company + ' ' + appcode + ' ' + wxusertype + ' ' +
functionid).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
def get_crmsubreport(request):
company = request.GET['company']
crmsubreport = CrmSubReport.objects.filter(company=company,
flag='Y').values_list(
'id', 'crmsubreportName')
sql = " select id, crmsubreportname from crmsubreport " \
" where flag='Y' and company = %s "
params = (company + ' ').split()
print('sql:', sql, 'params:', params)
json_data = sql_to_json(sql, params)
print('json_data', json_data)
return HttpResponse(json_data, content_type="application/json")
def get_vipcasedetail(request):
uuid = request.GET.get('uuid')
sql = " select a.uuid , a.casetype , a.detail, a.ecode ,DATE(a.create_time) created_date, TIME (a.create_time) created_time,nextdate,nextecode,status"\
" from vipcasedetail a"\
" where 1=1 and a.flag='Y' "\
" and a.uuid = %s " \
" order by a.create_time desc "\
params = (uuid).split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(json_data)
return HttpResponse(json_data, content_type="application/json")
def get_crmcasedetail_bycaseid(request):
company = request.GET['company']
uuid = request.GET['uuid']
# fromdate = request.GET['fromdate']
# todate = request.GET['todate']
sql = " SELECT a.uuid crmcaseuuid, a.vipuuid vipuuid, a.casedesc, b.uuid uuid, b.create_time,b.creater, detaildescription ,c.vname vname"\
" FROM crmcase a, crmcasedetail b, vip c"\
" where 1=1 and a.uuid = b.caseid_id AND a.vipuuid = c.uuid"\
" and a.company = %s and b.uuid = %s "\
" order by b.create_time"
params = (company + ' ' + uuid).split()
print('sql:', sql, 'params:', params)
json_data = sql_to_json(sql, params)
print('json_data', json_data)
return HttpResponse(json_data, content_type="application/json")
def get_vip_crmcasedetail(request):
company = request.GET['company']
vipuuid = request.GET['vipuuid']
# fromdate = request.GET['fromdate']
# todate = request.GET['todate']
sql = " SELECT b.uuid crmcasedetailuuid, c.vcode, c.vname, b.storecode storeocode, casetype, a.status casestatus, planbegindate,planfinishdate, finishedate, casedesc, b.create_time,b.creater, detaildescription "\
" FROM crmcase a, crmcasedetail b, vip c, empl d "\
" where a.uuid = b.caseid_id and a.vipuuid = c.uuid and a.ecode = d.ecode "\
" and a.company=d.company and a.company = %s and a.vipuuid = %s "\
" order by b.create_time"
params = (company + ' ' + vipuuid).split()
print('sql:', sql, 'params:', params)
json_data = sql_to_json(sql, params)
print('json_data', json_data)
return HttpResponse(json_data, content_type="application/json")
def get_vipcasedetail_byvipuuid(request):
company = request.GET.get('company', 'demo')
print('company=', company)
vipuuid = request.GET.get('vipuuid', '').replace('-', '')
print('vipuuid=', vipuuid)
sql = " select a.uuid , a.casetype , a.detail, a.ecode ,DATE(a.create_time) created_date, TIME (a.create_time) created_time,nextdate,nextecode,status"\
" from vipcasedetail a"\
" where 1=1 and a.flag='Y' "\
" and a.company = %s AND a.vipuuid = %s " \
" order by a.create_time desc "\
params = (company + ' ' + vipuuid).split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(json_data)
return HttpResponse(json_data, content_type="application/json")
def report1(self):
goodsrpt3s = Appoption.objects.filter(seg='goodsrpt3').values(
'itemname', 'itemvalues').order_by('itemname')
for goodsrpt3 in goodsrpt3s:
item = goodsrpt3['itemname']
sql = " select a.storecode, g.rptcode1, getappoptionvalue(g.company,'goodsrpt1',g.rptcode1) rptname1, g.rptcode2,getappoptionvalue(g.company,'goodsrpt2',g.rptcode2) rptname2,"\
" g.rptcode3, getappoptionvalue(g.company,'goodsrpt3',g.rptcode3) rptname3, "\
" count(distinct a.vipuuid) vipcnt "\
" from expvstoll a, expense b, goods g "\
" where 1=1 and a.valiflag='Y' and a.uuid = b.transuuid and b.srvcode=g.gcode "\
" and a.vsdate>= '20181201' and a.vsdate<= '20190701' "\
" and g.rptcode1= '2019' "\
" and g.rptcode3 = %s" \
" and a.vipuuid in ( "\
" select distinct vipuuid from expvstoll "\
" where 1=1 "\
" and valiflag = 'Y' " \
" and vsdate >= '20191201' " \
" and vsdate <= '20200701' " \
" ) "\
" and a.vipuuid not in ( "\
" select distinct a.vipuuid "\
" from expvstoll a, expense b, goods g "\
" where 1=1 "\
" and a.valiflag='Y' "\
" and a.uuid = b.transuuid "\
" and b.srvcode=g.gcode "\
" and a.vsdate>='20191201' "\
" and a.vsdate<='20200701' "\
" and g.rptcode1='2020' " \
" and g.rptcode3 = %s" \
" ) "\
" group by a.storecode, g.rptcode1, g.rptcode2, g.rptcode3"
params = (item + ' ' + item).split()
# print(sql, params)
json_data = sql_to_json(sql, params)
# print(item,type(item),json_data)
filename = 'c:/tmp/' + goodsrpt3[
'itemvalues'] + '_19年购买，20年到店未购买的客户数量.xls'
json_to_excel(json_data, filename)
def report2(self):
goodsrpt3s = Appoption.objects.filter(
seg='goodsrpt3',
itemname__in=[
'301', '302', '303', '304', '305', '306', '307', '308', '309',
'310', '311', '312', '313'
]).values('itemname', 'itemvalues')
for goodsrpt3 in goodsrpt3s:
item = goodsrpt3['itemname']
sql = " select a.storecode, b.rptcode1,b.rptname1, b.rptcode2, b.rptname2, b.rptcode3, b.rptname3, count(distinct a.vipuuid) vipcnt "\
" from ( "\
" select distinct a.storecode, a.vipuuid , g.rptcode1, getappoptionvalue(g.company,'goodsrpt1',g.rptcode1) rptname1, g.rptcode2, getappoptionvalue(g.company,'goodsrpt2',g.rptcode2) rptname2 "\
" from expvstoll a, expense b, goods g where 1=1 and a.valiflag='Y' and a.uuid = b.transuuid "\
" and b.srvcode=g.gcode and a.vsdate>= '20181201' and a.vsdate<= '20190701' "\
" and g.rptcode1= '2019' and g.rptcode2 = '10' "\
" ) a, "\
" ( "\
" select distinct a.storecode, a.vipuuid , g.rptcode1, getappoptionvalue(g.company,'goodsrpt1',g.rptcode1) rptname1, g.rptcode2, getappoptionvalue(g.company,'goodsrpt2',g.rptcode2) rptname2, g.rptcode3, getappoptionvalue(g.company,'goodsrpt3',g.rptcode3) rptname3 "\
" from expvstoll a, expense b, goods g "\
" where 1=1 and a.valiflag='Y' and a.uuid = b.transuuid and b.srvcode=g.gcode "\
" and a.vsdate>= '20181201' and a.vsdate<= '20190701' "\
" and g.rptcode1= '2019' and g.rptcode3 = %s "\
" ) b "\
" where a.vipuuid = b.vipuuid "\
" and a.vipuuid not in ( "\
" select distinct vipuuid from expvstoll "\
" where 1=1 and valiflag = 'Y' "\
" and vsdate >= '20191201' and vsdate <= '20200701' "\
" ) "\
" group by a.storecode, b.rptcode1, b.rptcode2, b.rptcode3"
params = (item + ' ').split()
print(sql, params)
json_data = sql_to_json(sql, params)
print(item, type(item), json_data)
filename = 'c:/tmp/' + goodsrpt3[
'itemvalues'] + '_19年即购买方位又购买属相，20年未到店客户数量.xls'
json_to_excel(json_data, filename)
def get_emplarch_bymonth(request):
company = request.GET['company']
storecode = request.GET['storecode']
month = request.GET['month']
ecode = request.GET['ecode']
delta = timedelta(days=-90)
print(delta)
now = datetime.now()
fromdate = datetime.strftime((now + delta),'%Y%m%d')
todate = datetime.strftime( datetime.now() ,'%Y%m%d')
print('fromdate:',fromdate,'todate:',todate)
# if request.method == 'GET':
if company=='yfy':
sql = EMPL_ARCHEMENT_BYMONTH_YFY
else:
sql = EMPL_ARCHEMENT_BYMONTH_YFY
params = (company + ' ' + storecode + ' ' + month + ' ' + ecode + ' '+company + ' ' + storecode + ' ' + month + ' ' + ecode +' '+ company + ' ' + storecode + ' ' + month + ' ' + ecode ).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
def get_viplist_bycrmrptid(request):
# datedelta = datetime.datedelta(days=-7)
delta = datetime.timedelta(days=-90)
now = datetime.datetime.now()
defaultfromdate = datetime.datetime.strftime((now + delta), '%Y%m%d')
defaulttodate = datetime.datetime.strftime(now, '%Y%m%d')
try:
company = request.GET['company']
except:
company = ''
try:
storecode = request.GET['storecode']
except:
storecode = ''
try:
fromdate = request.GET['fromdate'].replace('-', '')
if len(fromdate) == 0:
fromdate = defaultfromdate
except:
fromdate = defaultfromdate
try:
todate = request.GET['todate'], replace('-', '')
if len(todate) == 0:
todate = defaulttodate
except:
todate = defaulttodate
try:
minileftmoney = request.GET['minileftmoney']
except:
minileftmoney = 0
try:
maxleftmoney = request.GET['maxleftmoney']
except:
maxleftmoney = 5000
try:
ecode = request.GET['ecode']
except:
ecode = ''
try:
crmrptid = request.GET['crmrptid']
except:
crmrptid = '1'
print('crmrptid=', crmrptid)
if crmrptid == '1':
sql = crm.crmsql.CRM_SQL101
print('sql=', sql)
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
if crmrptid == '2':
sql = crm.crmsql.CRM_SQL102
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
if crmrptid == '3':
sql = crm.crmsql.CRM_SQL103
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
if crmrptid == '4':
sql = crm.crmsql.CRM_SQL104
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
# 客人卡余额查询
if crmrptid == '5':
sql = crm.crmsql.CRM_SQL105
params = (company + ' ' + storecode + ' ' + str(minileftmoney) + ' ' +
str(maxleftmoney) + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
if crmrptid == '6':
sql = crm.crmsql.CRM_SQL105
params = (company + ' ' + storecode + ' ' + str(minileftmoney) + ' ' +
str(maxleftmoney) + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
# 客人生日
if crmrptid == '8':
fromdate = str(fromdate).replace('-', '')
todate = str(todate).replace('-', '')
sql = crm.crmsql.CRM_SQL108
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
# 客人生日
if crmrptid == '9':
fromdate = str(fromdate).replace('-', '')
todate = str(todate).replace('-', '')
sql = crm.crmsql.CRM_SQL109
params = (company + ' ' + storecode + ' ' + fromdate + ' ' +
todate + ' ' + ecode).split()
print(sql, params)
json_data = sql_to_json(sql, params)
return HttpResponse(json_data, content_type="application/json")
def get_onlineshowtype(self):
# onlineshowtypes = onlineShowType.objects.filter(company=self.company,flag='Y').values_list('id','showname','showimage','showurl').order_by('orderno')
sql = "select showtypecode, showtypename, showtypeimage,orderno from onlineShowType where ttype='G' and company = %s order by orderno "
params = (self.company + ' ').split()
json_data = sql_to_json(sql, params)
return json_data