API tools faq
paste
Guest User

Untitled

a guest
Oct 17th, 2018
122
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.13 KB | None | 0 0
raw download clone embed print report
  1. # -*- coding: utf-8 -*-
  2. from django.utils.encoding import smart_str
  3. import datetime
  4. from visit.settings import PAYMENT_TYPES
  5. from django.conf import settings
  6. from django.db import connection
  7. from amqplib.client_0_8.method_framing import defaultdict
  8. from operator import itemgetter
  9. from reporting.models import Report as Config
  10. #from models import GROUP_SERVICE_UZI, GROUP_SERVICE_LAB
  11.  
  12. try:
  13. GROUP_SERVICE_UZI = settings.GROUP_SERVICE_UZI
  14. GROUP_SERVICE_LAB = settings.GROUP_SERVICE_LAB
  15. GROUP_SERVICE_RADIO = settings.GROUP_SERVICE_RADIO
  16. except:
  17. raise Exception, u"Нет настроек групп услуг"
  18.  
  19. class Report():
  20. """
  21. """
  22. query_str = u''
  23. verbose_name = u''
  24. base_wuery_from_where = u"\
  25. FROM \
  26. public.visit_visit Tvis \
  27. left outer join public.visit_orderedservice TTvis on TTvis.order_id = Tvis.id \
  28. left outer join public.state_state Tstate on Tstate.id = TTvis.execution_place_id \
  29. left outer join public.service_baseservice Tserv on Tserv.id = TTvis.service_id \
  30. left outer join public.staff_position Tpstf on Tpstf.id = TTvis.staff_id \
  31. left outer join public.staff_staff Tstaff on Tstaff.id = Tpstf.staff_id \
  32. left outer join public.state_department Tdpr on Tdpr.id = Tpstf.department_id \
  33. left outer join public.patient_patient Tpnt on Tpnt.id = Tvis.patient_id \
  34. left outer join public.visit_referral Trefrl on Trefrl.id = Tvis.referral_id \
  35. left outer join public.visit_referralagent Trefrlagent on Trefrlagent.id = Trefrl.agent_id \
  36. left outer join public.reporting_stategroup_state TTstgr on TTstgr.state_id = TTvis.execution_place_id \
  37. left outer join public.reporting_stategroup Tstgr on Tstgr.id = TTstgr.stategroup_id \
  38. left outer join public.patient_insurancepolicy Tpolis on Tpolis.id = Tvis.insurance_policy_id \
  39. left outer join public.reporting_servicegroup_baseservice TTrepbsgp on TTrepbsgp.baseservice_id = Tserv.id \
  40. left outer join public.reporting_servicegroup Trepbsgp on Trepbsgp.id = TTrepbsgp.servicegroup_id \
  41. WHERE \
  42. TTvis.count is not null and TTvis.price is not null and \
  43. to_char(Tvis.created,'YYYY-MM-DD') BETWEEN '%s' and '%s' \
  44. %s %s %s %s %s %s %s %s %s %s"
  45.  
  46. bq_exists_uzi = u"\
  47. and exists (SELECT * \
  48. FROM \
  49. public.reporting_servicegroup Tsg \
  50. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  51. Where \
  52. Tsg.name = '%s'\
  53. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_UZI)
  54.  
  55. bq_notexists_uzi = u"\
  56. and not exists (SELECT * \
  57. FROM \
  58. public.reporting_servicegroup Tsg \
  59. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  60. Where \
  61. Tsg.name = '%s'\
  62. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_UZI)
  63.  
  64. bq_exists_lab = u"\
  65. and exists (SELECT * \
  66. FROM \
  67. public.reporting_servicegroup Tsg \
  68. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  69. Where \
  70. Tsg.name = '%s'\
  71. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_LAB)
  72.  
  73. bq_notexists_lab = u"\
  74. and not exists (SELECT * \
  75. FROM \
  76. public.reporting_servicegroup Tsg \
  77. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  78. Where \
  79. Tsg.name = '%s'\
  80. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_LAB)
  81.  
  82. bq_exists_radio = u"\
  83. and exists (SELECT * \
  84. FROM \
  85. public.reporting_servicegroup Tsg \
  86. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  87. Where \
  88. Tsg.name = '%s'\
  89. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_RADIO)
  90.  
  91. bq_notexists_radio = u"\
  92. and not exists (SELECT * \
  93. FROM \
  94. public.reporting_servicegroup Tsg \
  95. join public.reporting_servicegroup_baseservice Tsg_Tbs on Tsg_Tbs.servicegroup_id = Tsg.id \
  96. Where \
  97. Tsg.name = '%s'\
  98. and Tsg_Tbs.baseservice_id = TTvis.service_id)" % (GROUP_SERVICE_RADIO)
  99.  
  100. def __init__(self, request,slug):
  101. """
  102. """
  103. self.request = request
  104. self.params = dict(self.request.GET.items())
  105. try:
  106. self.config = Config.objects.get(slug = slug)
  107. self.query_str = self.config.sql_query.sql
  108. except:
  109. print 'Report not found'
  110. pass
  111. self.trim_params = dict(filter(lambda x: x[1] is not u'',self.params.items()))
  112. self.results = self.prep_data()
  113. self.make()
  114.  
  115. def prep_data(self):
  116. cursor = connection.cursor()
  117. cursor.execute(self.prep_query_str())
  118. results = cursor.fetchall()
  119. cursor.close ()
  120. return results
  121.  
  122.  
  123. def fmap(self,l):
  124. return map(lambda x: [x[0],x[1:]],l)
  125.  
  126. def sort(self,d):
  127. keys = d.keys()
  128. keys.sort()
  129. return [[item,d[item]] for item in keys]
  130.  
  131. def dict(self,vl):
  132. d = {}
  133. for k, v in vl:
  134. d.setdefault(k, []).append(v)
  135. return d
  136.  
  137. def struct(self,l):
  138. return self.sort(self.dict(self.fmap(l)))
  139.  
  140. def struct_and_chkeys(self,l,d):
  141. fm = self.fmap(l)
  142. dv = self.dict(fm)
  143. dv = self.chkeys(dv,d)
  144. return(self.sort(dv))
  145.  
  146. def chkeys(self,d1,d2):
  147. return dict((d2[key], value) for (key, value) in d1.items())
  148.  
  149. def prep_query_str(self):
  150. order__cls = ''
  151. if self.params['order__cls'] is not u'':
  152. order__cls = u"and Tvis.cls = '%s'"% (self.params['order__cls'])
  153.  
  154. order__patient = ''
  155. if self.params['order__patient'] is not u'':
  156. order__patient = u"and Tvis.patient_id = '%s'"% (self.params['order__patient'])
  157.  
  158. staff__staff = ''
  159. if self.params['staff__staff'] is not u'':
  160. staff__staff = u"and Tstaff.id = '%s'"% (self.params['staff__staff'])
  161.  
  162. staff__department = ''
  163. if self.params['staff__department'] is not u'':
  164. staff__department = u"and Tdpr.id = '%s'"% (self.params['staff__department'])
  165.  
  166. order__referral = ''
  167. if self.params['order__referral'] is not u'':
  168. order__referral = u"and Tvis.referral_id = '%s'"% (self.params['order__referral'])
  169.  
  170. from_place_filial = ''
  171. if self.params['from_place_filial'] is not u'':
  172. from_place_filial = u"and Tvis.office_id = '%s'"% (self.params['from_place_filial'])
  173.  
  174. from_lab = ''
  175. if self.params['from_lab'] is not u'':
  176. from_lab = u"and Tvis.source_lab_id = '%s'"% (self.params['from_lab'])
  177.  
  178. execution_place_office = ''
  179. if self.params['execution_place_office'] is not u'':
  180. execution_place_office = u"and Tstgr.id = '%s'"% (self.params['execution_place_office'])
  181.  
  182. execution_place_filial = ''
  183. if self.params['execution_place_filial'] is not u'':
  184. execution_place_filial = u"and TTvis.execution_place_id = '%s'"% (self.params['execution_place_filial'])
  185.  
  186. order__payment_type = ''
  187. if self.params['order__payment_type'] is not u'':
  188. order__payment_type = u"and Tvis.payment_type = '%s'"% (self.params['order__payment_type'])
  189.  
  190. return self.query_str % (self.params['start_date']
  191. ,self.params['end_date']
  192. ,order__cls
  193. ,order__patient
  194. ,staff__staff
  195. ,staff__department
  196. ,order__referral
  197. ,from_place_filial
  198. ,from_lab
  199. ,execution_place_office
  200. ,execution_place_filial
  201. ,order__payment_type
  202. )
  203. def make(self):
  204. field_list = map(lambda x:x['name'] if isinstance(x,dict) else x,self.fields)
  205. dict_result = [dict(zip(field_list,record)) for record in self.results]
  206. group_list = map(lambda x:x['name'] if isinstance(x,dict) else x,self.groups)
  207. sorted(dict_result, key=itemgetter(*group_list))
  208. root_node = RootNode(dict_result)
  209. root_node.groups = self.make_groups(root_node.data,self.groups)
  210. pdb.set_trace()
  211. return root_node
  212.  
  213. def make_groups(self,data,groups):
  214. if not len(groups):
  215. return []
  216. curr_group = groups[0]
  217. field_name = isinstance(curr_group,dict) and curr_group['name'] or curr_group
  218. # print field_name
  219. group_items = []
  220. gr = defaultdict(list)
  221. while len(data):
  222. rec = data.pop()
  223. gr[rec[field_name]].append(rec)
  224. for key in gr.keys():
  225. node = Node(key,gr[key])
  226. node.value = field_name
  227. aggrs = isinstance(curr_group,dict) and curr_group['aggr'] or []
  228. aggrs_data = [aggr for aggr in aggrs if aggr['scope']=='data']
  229. aggrs_group = [aggr for aggr in aggrs if aggr['scope']=='group']
  230. for aggr in aggrs_data:
  231. node.do_aggr_func(aggr)
  232. node.groups = self.make_groups(node.data,groups[1:])
  233. for aggr in aggrs_group:
  234. node.do_aggr_func(aggr)
  235. group_items.append(node)
  236. return group_items
  237.  
  238. import pdb
  239. def sum_field(node,field,name,scope='group'):
  240. """
  241. """
  242. # print node.aggr_val
  243. # print node.groups
  244. # if node.aggr_val.has_key(name):
  245. # return node.aggr_val[name]
  246. if node.data:
  247. s = sum(map(lambda v:v[field] if v.has_key(field) and v[field] else 0,node.data))
  248. else:
  249. s = sum([sum_field(gr,field,name,scope) for gr in node.groups])
  250. aggr_val = node.aggr_val.copy()
  251. aggr_val[name] = s
  252. node.aggr_val = aggr_val
  253.  
  254. return s
  255.  
  256. def min_field(node,field,name,scope='group'):
  257. """
  258. """
  259. # if node.aggr_val.has_key(name):
  260. # return node.aggr_val[name]
  261. if node.data:
  262. s = min(map(lambda v:v[field] if v.has_key(field) and v[field] else 0,node.data))
  263. else:
  264. s = min([min_field(gr,field,name,scope) for gr in node.groups])
  265. aggr_val = node.aggr_val.copy()
  266. aggr_val[name] = s
  267. node.aggr_val = aggr_val
  268.  
  269. return s
  270.  
  271. def max_field(node,field,name,scope='group'):
  272. """
  273. """
  274. # if node.aggr_val.has_key(name):
  275. # return node.aggr_val[name]
  276. if node.data:
  277. s = max(map(lambda v:v[field] if v.has_key(field) and v[field] else 0,node.data))
  278. else:
  279. s = max([max_field(gr,field,name,scope) for gr in node.groups])
  280. aggr_val = node.aggr_val.copy()
  281. aggr_val[name] = s
  282. node.aggr_val = aggr_val
  283.  
  284. return s
  285.  
  286. class Node():
  287. data = []
  288. groups = []
  289. name = u''
  290. value = u''
  291. aggr_fn = {
  292. 'sum':sum_field,
  293. 'min':min_field,
  294. 'max':max_field,
  295.  
  296. }
  297. aggr_val = {}
  298.  
  299. def __init__(self,name, data):
  300. self.data = data
  301. self.name = name
  302.  
  303.  
  304.  
  305. def do_aggr_func(self,aggr):
  306. if self.aggr_fn.has_key(aggr['func']):
  307. return self.aggr_fn[aggr['func']](self,aggr['field'],aggr['name'])
  308.  
  309. class RootNode(Node):
  310.  
  311. totals = {
  312. 'verbose':u'ИТОГО:',
  313. 'aggr':[]
  314. }
  315.  
  316. def __init__(self,data):
  317. self.data = data
  318. self.name = 'rootNode'
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!