# -*- coding: utf-8 -*- module Handling def self.salaries_bug plus = 0 ps=Payroll.find(:all, :include =>"period_zp", :conditions =>"period_zps.period_id >= 2340 && zp_type_id = 1") for p in ps do period = p.period shifts = period.work_shifts.select{|i| i.status.to_i == 1 && i.employee_id == p.employee_id} if shifts days = WorkingDay.for_month(period.month).first working_days = days.working_days empl = p.employee salary = Salary.find(:first, :conditions =>["DATE(date_from) <= DATE('#{period.month}') AND static_sum IS NOT TRUE AND position_id = ?", empl.position_id], :order =>'date_from DESC') if salary salary_for_day = salary.sum / working_days salary_for_empl = shifts.map{ |i| i.shift_date.to_date}.uniq.size * salary_for_day if salary_for_empl.to_i != p.sum.to_i p.update_attributes(:sum => salary_for_empl.to_i) p.period_zp.update_attributes(:payroll_sum => p.period_zp.payrolls.sum(:sum)) plus += 1 end end end end return plus end # Доблавяем оклады def self.add_salaries Salary.destroy_all positions = Employee.all.map{|i| i.position}.uniq.map{|i| i.id} @date = Date.today.beginning_of_year for p in positions do s = Salary.create(:division_id => 68, :position_id => p, :date_from => @date, :sum => 0) end end def self.change_payrolls # Удаляем лишние Payroll.connection.execute("DELETE payrolls FROM payrolls LEFT JOIN periods ON periods.id = payrolls.period_zp_id WHERE periods.department != 'construction'") ps=Payroll.connection.execute("DELETE FROM periods WHERE periods.department != 'construction'") # изменяем period_Id на period_zp_id for payroll in Payroll.all do per = Period.find payroll.period_zp_id empl = payroll.employee_id payroll.update_attributes(:period_zp => PeriodZp.for_period_and_employee(per.id, empl.id)) end end def self.history_for_period_zp # Закрываем старые периоды, на каждый создаём Payroll periods = Period.actives.find(:all, :conditions =>"month < '2021-12-01'") for period in periods do @payrolls = Payroll.calculate_for_period(period) for payroll in @payrolls do payroll.save_with_check end end # на сумму period_zp.payroll_sum создаем выплаты на каждый месяц. for pz in PeriodZp.find(:all, :conditions =>"payroll_sum > 0") do payment = pz.payments.build(:operation_date => pz.period.month.end_of_month, :sum => pz.payroll_sum, :employee_id => pz.employee_id, :description =>"Автоматически созданные документы.") payment.save_with_check end end def self.compact_materials reports = Report.find(:all, :include => :done_materials).select{|i| i.done_materials.size > 1} for report in reports do dm = report.done_materials bad_dms = dm.group_by{|i| [i.employee_id, i.material_id]}.select{|k, v| v.size > 1} bad_dms.each do |kn, vn| count = vn.map{|i| i.count.to_i}.sum main_dm = vn[0] main_dm.count = count main_dm.save (vn - [main_dm]).each{|i| i.destroy} end end end def self.soft Report.connection.execute("DELETE FROM reports WHERE department != 'soft'") DoneMaterial.connection.execute("DELETE FROM done_materials WHERE department != 'soft'") DoneEmployee.connection.execute("DELETE FROM done_employees WHERE department != 'soft'") DoneEmployee.find(:all, :include => "employee").map{|i| i.employee}.uniq # empls = Employee.find(:all, :conditions => ["nic IN (?)", DoneEmployee.find(:all, :select => "empl_id, empl_name").map{|i| i.empl_name}.uniq]) empls = Employee.all - [Employee.find_by_nic "mihas'"] for emp in empls do # Материалы и работы DoneEmployee.connection.execute("UPDATE done_employees SET empl_id = '#{emp.id}' WHERE empl_name = '#{emp.nic}'") DoneMaterial.connection.execute("UPDATE done_materials SET employee_id = '#{emp.id}' WHERE employee_name = '#{emp.nic}'") # Update права DoneMaterial.connection.execute("UPDATE access SET empl_id = '#{emp.id}' WHERE empl_name = '#{emp.nic}'") puts emp.nic end # Обновить division in pricelist # [152, 251, 151, 155, 158, 241, 168] PricelistDivision.connection.execute("UPDATE pl_item_divisions SET division_id = '5' WHERE division_id = '251'") managers = Report.find(:all, :conditions => "manager_id IS NOT NULL").group_by{|i| i.manager_id} #map{|i| i.manager_id}.flatten.uniq.compact managers.each do |manager_id, reports| nic = (Person.find manager_id).login new_id = (Employee.find_by_nic nic).id DoneMaterial.connection.execute("UPDATE reports SET manager_id = '#{new_id}' WHERE id IN (#{reports.map{|i| i.id}.join(', ')})") end end def self.soft_3 Shift.connection.execute("DELETE FROM shifts WHERE department != 'soft'") Shift.connection.execute("DELETE FROM regular_shifts WHERE department != 'soft'") shifts = Shift.find(:all, :conditions => "employee_id IS NOT NULL").group_by{|i| i.employee_id} shifts.each do |employee_id, shfs| if Person.exists? employee_id nic = (Person.find employee_id).login else nic = nil end if nic new_id = (Employee.find_by_nic nic).id Shift.connection.execute("UPDATE shifts SET employee_id = '#{new_id}' WHERE id IN (#{shfs.map{|i| i.id}.join(', ')})") else puts employee_id end end end def self.soft_2 # update_user_id good = 0 bad = [] @reports=Report.find_by_sql("SELECT id, update_user_id FROM reports WHERE department = 'soft'").group_by{|i| i.update_user_id} @reports.each do |u, reps| person_old = Person.find u if(u && (Person.exists? u)) if person_old new_person = Employee.find_by_nic person_old.login if new_person Report.connection.execute("UPDATE reports SET update_user_id = '#{new_person.id}' WHERE id IN (#{reps.map{|i| i.id}.join(', ')})") good += 1 puts good else bad << person_old.login puts person_old.login end else bad << u puts u end end return "Good #{good.to_s}. Bad #{bad.join(', ')}" # id to store end def self.multiaccess @accesses=Access.find_by_sql("SELECT COUNT(id) c, empl_id, department FROM access GROUP BY empl_id, department HAVING c > 1") for acc in @accesses do empl_accs=Access.find(:all, :conditions =>"empl_id = #{acc.empl_id} AND department = '#{acc.department}'") empl_accs[0, empl_accs.size-1].each{|i| i.destroy} end end def self.regs_for_alps 7.times do |i| r=RegularManagerShift.new r.department = 'alp' r.employee_id = 2299 r.day_of_week = i+1 r.save end end def constr # @reps = Report.for_department("construction").select{|i| i.date.to_date != i.date_stop.to_date} # periods=@reps.map{|i| i.period}.flatten.uniq.select{|i| i.closed == true} # for p in periods do # p.update_attributes(:closed => false) # end # for r in @reps do # date_stop = r.date_stop # date_from = r.date # r.update_attributes(:date => date_stop, :date_stop => date) # end @date_from = '01.06.2016'.to_date; @date_to = Date.today rs = Report.for_department("construction").in_period(@date_from, @date_to).map{|i| i.done_employees}.flatten.select{|i| i.sum.to_i > 0}.map{|i| i.report} for r in rs do r.calculate r.save end end def self.store2 # Закрыть все периоды по июнь pers = Period.find(:all, :conditions =>"closed = false AND month < '2016-06-01'") for per in pers do per.update_attributes(:closed => true) end # Создать периоды на июнь если нет divs = Division.all_for_form.map{|i, c| c} for d in divs do p = Period.find(:all, :conditions =>"department = '#{d}' AND month = '2016-06-01'") if p.size == 0 Period.create(:closed => false, :department => d, :month => '2016-06-01') end end end def self.new_store # У альпов меняем категории r2 = Report.find(:all, :conditions =>"category = 62");1 r3 = Report.find(:all, :conditions =>"category = 63");1 Report.update_all("category = '2'", ["id IN (?)", r2.map(&:id)]) Report.update_all("category = '3'", ["id IN (?)", r3.map(&:id)]) Handling.alps # Установка в офис у софтов в сделке, там должен быть логин сотрудника (проходимся по отчетам, с даты, для которой нужно будет перепровести операции по новому складу) # date = Date.today.beginning_of_month # ИЗМЕНИТЬ!!!!! # rs = Report.find(:all, :conditions =>"DATE(date) >= DATE('#{date}') AND category = 26") # Этот список предъявить софтам, пусть проставят правильные # Для тестирования везде ставлю себя # for r in rs do # r.update_attributes(:name => "annet17", :dhcp_login => "new2") # r.update_attributes(:dhcp_login => "") # end # Ищем все отчеты, которые нужно перепровести по новому складу # ДЕЛАЕМ В ТЕРМИНАЛЕ ЧТОБЫ ВСЕ ВИДЕТЬ, ПЕРЕД ПРОВОДКОЙ изменяем строки в report.rb № 314 на 315 # Audit.find(:all, :conditions => "") # Вариант более правильный, заработает с 1 июня # rs = Audit.find(:all, :include => :report, :conditions =>"comment = 'Добавлен материал' AND DATE(created_at)>= DATE('#{date}')").map{ |i| i.report}.flatten.uniq.compact.select{|i| i.done_materials.size > 0} # rrs = rs.select{|i| i.done_materials.select{|i| i.date.to_date < date}.size > 0} # ## rrs должно быть ноль, это отчеты, материалы у которых были частично списаны до даты Х. # for r in rs do # r.save # end end def self.set_price ds=DoneWork.find(:all, :conditions =>"department='private' AND date_create_report > '2015-01-01' AND get_money = 0").select{|i| i.work && i.work.price.to_i > 0} for d in ds do d.update_attributes(:get_money =>d.work.price) end end def self.set_old_material_purchase_price require "json" f = File.open('result_new.json') ar = [] f.each do |l| ar << JSON.parse(l) end hs = ar[0] ids = [] str = "CASE store_id" hs.each do |id, val| puts "D" if val[0].nil? str +=" WHEN " +id.to_s + " THEN '" + val[0]["price"].to_s+ "'" unless val[0].nil? ids << id.to_s end str += " END" DoneMaterial.connection.execute("UPDATE done_materials SET purchase_price = (#{str}) WHERE store_id IN (#{ids.join(', ')})") end def self.create_periods_for_new_department(dep) pers = (Date.new(2008, 01)..Date.new(2015, 12)).select {|d| d.day == 1} for month in pers do if month >= '01.06.2015'.to_date cl = false else cl = true end Period.create(:month => month, :department => dep, :closed => cl) end end def self.alps Report.connection.execute("UPDATE reports SET status = 2 WHERE department = 'alp' AND (status != '2' OR status IS NULL)") periods = Period.find(:all, :conditions =>"department = 'alp'") for per in periods do per.update_attributes(:closed => false) end rep_logins = RepLogin.all errors = "" good = 0 for rl in rep_logins do rn = Report.new rn.attributes = rl.report.attributes for de in rl.report.done_employees do nde = DoneEmployee.new nde.attributes = de.attributes rn.done_employees << nde end if rl.done_material rn.done_materials << rl.done_material end rn.dhcp_login = 'new2' rn.login_from = rl.from_name rn.name = rl.to_name rn.get_money_sum = rl.sum rn.sum = rn.get_money_sum if rn.save good += 1 else rn.errors.each_full {|msg| errors += msg; puts msg} end end return good.to_s old_reps = Report.find(:all, :conditions =>"department = 'alp' AND (dhcp_login != 'new2' OR dhcp_login IS NULL)") for r in old_reps do r.destroy end end def self.ams # Делаем все отчеты утвержденными Report.connection.execute("UPDATE reports SET status = 2 WHERE department = 'ams' AND (status != '2' OR status IS NULL)") cat_mod = Category.find 20 # cat_mod_adm = Category.new; cat_mod_adm.attributes = cat_mod.attributes; cat_mod_adm.name = "Модернизация - хотелки администрации"; cat_mod_adm.save # cat_mod_uzl = Category.new; cat_mod_uzl.attributes = cat_mod.attributes; cat_mod_uzl.name = "Модернизация узла";cat_mod_uzl.save # cat_mod_vn = Category.new; cat_mod_vn.attributes = cat_mod.attributes; cat_mod_vn.name = "Модернизация абонентских выносов"; cat_mod_vn.save Report.connection.execute("UPDATE reports SET category = #{cat_mod_adm.id} WHERE department = 'ams' AND category = 20 AND client_type = 1") Report.connection.execute("UPDATE reports SET category = #{cat_mod_uzl.id} WHERE department = 'ams' AND category = 20 AND (client_type = 2 OR client_type = 0)") Report.connection.execute("UPDATE reports SET category = #{cat_mod_vn.id} WHERE department = 'ams' AND category = 20 AND client_type = 3") cat = Category.find 18 cat.update_attributes(:show => false) cat_mod.update_attributes(:show => false) end def self.recalc_old_reports(department, new_or_old, date_from, date_to) if new_or_old == 'new' new_attr = "AND dhcp_login = 'new'" else new_attr = "AND dhcp_login != 'new'" end rs=Report.find(:all, :conditions =>"department = '#{department}' #{new_attr} AND DATE(date) BETWEEN DATE('#{date_from}') AND DATE('#{date_to}')") for r in rs do for w in r.done_works do w.update_attributes(:rate => w.work.rate) end r.request_closed = true r.save end end def self.set_rate DoneWork.update_all("rate = 0", "department IN ('ams', 'welder', 'alp')") dws=DoneWork.find(:all, :conditions => "(department IN ('private', 'mod'))", :include =>["work"]); dws+=DoneWork.find(:all, :conditions => "reports.department = 'corp' AND ((reports.dhcp_login IS NULL OR reports.dhcp_login != 'new') OR (reports.category NOT IN (12, 14)))", :include =>["report"]); from = 0; shag = (dws.size / 100) sql = ActiveRecord::Base.connection() 100.times do |c| dw = dws[from, shag] from += shag str = "CASE id" dw.map{|v| str+=" WHEN " + v.id.to_s + " THEN '" + (v.pricelist == 1 ? (v.pricelist2.nil? ? '0' : v.pricelist2.rate.to_s) : v.work2.nil? ? '0' : v.work2.rate.to_s)+ "'"} str += " END" sql.execute("UPDATE done_works dw SET dw.rate = #{str} WHERE id IN (#{dw.map{|i| i.id}.join(', ')})") str = nil end sql.execute("COMMIT") dw_copr_percent = DoneWork.find(:all, :conditions => "reports.category IN (12, 14) AND dhcp_login = 'new'", :include => "report") for dw in dw_copr_percent do dw.rate = (dw.work.price.to_f * dw.report.categor.master_percent.to_f) / 100 if dw.work # Учитываем скидку if dw.report.discount_works.to_i != 0 work_percent = dw.rate.to_f * dw.count.to_i * 100 / (dw.report.total_sum_works.to_f + dw.report.discount_works.to_f) work_discount_sum = dw.report.discount_works.to_f * work_percent / 100 dw.rate -= work_discount_sum end dw.rate = 0 unless dw.rate.to_f > 0 dw.save end rs = Report.soft for r in rs do r.save end return true end end