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