# -*- coding: utf-8 -*- class ContractsController < ApplicationController BOM = "\377\376" #Byte Order Mark # Авторизация (файл: controllers/application.rb) before_filter :isAuthorization # Права доступа (файл: controllers/application.rb) # around_filter :isAccessSuper before_filter :construct def construct # Устанавливаем сессионную куку. cookies[:sys_dep] = { :value => 'mod' } # Сотрудники $employeesAll = EmployeesDB.getEmployees("*") $employeesGroup = EmployeesDB.getEmployees(session[:user_department]) # Формируем сессию params.each { |key, value| session[""+key+""] = value } $session = session end def ks @contract = Contract.find(params[:id]) @date_from = Date.today.last_month.beginning_of_month @date_to = Date.today.last_month.end_of_month render :update do |page| page.show 'window2' page.replace_html 'yield2', :partial => 'ks' end end def ks_download require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' @from = params[:date_from].to_date @to = params[:date_to].to_date @contract = Contract.find(params[:id]) title = "КС #{@contract.name} за период: #{Russian::strftime(@from)} - #{Russian::strftime(@to)}" filename = "ks.csv" @works = OfWork.find_by_sql("SELECT r.name as login, m.name as measure_name, ow.id, ow.number as number, ow.name as of_name, ow.sum as price, SUM(dw.count) as count FROM done_works dw LEFT OUTER JOIN works ws ON dw.work_id = ws.id LEFT OUTER JOIN of_works ow ON ow.id = ws.of_work_id LEFT OUTER JOIN reports r ON dw.report_id = r.id LEFT OUTER JOIN measures m ON ws.measure = m.id WHERE r.closed_at IS NULL AND DATE(r.date) BETWEEN ('#{@from}') AND ('#{@to}') AND r.category IN (#{@contract.categories.select{|i| i.department != 'alp'}.map{|i| i.id}.join(', ')}) AND ow.number IS NOT NULL GROUP BY r.name, ow.id ORDER BY login, number") @materials = DoneMaterial.find(:all, :include => ["report", "material_detail"], :conditions => ["material_details.show IS TRUE AND reports.category IN (?) AND DATE(reports.date) BETWEEN (?) AND (?)", @contract.categories.select{|i| i.department != 'alp'}.map{|i| i.id}.join(', '), @from, @to ], :group => "reports.name, done_materials.material_id") @logins = (@works.map{|c| c.login} + @materials.map{|c| c.report.name}).uniq book = Spreadsheet::Workbook.new dataBook1 = book.create_worksheet :name => title dataBook1.column(0).width = 11; dataBook1.column(1).width = 13; dataBook1.column(2).width = 50; format = Spreadsheet::Format.new :weight => :bold, :size => 12, :horizontal_align => :centre format_small = Spreadsheet::Format.new :weight => :bold, :horizontal_align => :centre, :text_wrap => true i = 0 row = dataBook1.row(i) row.default_format = format dataBook1.row(i).height = 15 dataBook1.merge_cells(i, 0, i, 7) row[0] = title i += 1 # row = dataBook1.row(i) dataBook1.row(i).default_format = format_small dataBook1.row(i).height = 30 dataBook1.row(i).push "Номер", "", "Наименование работ", "Обосно-вание единиц расценок", "Ед. изм.", "Выполнено работ" if @contract.categories.select{|al| al.department == 'alp'}.size > 0 @alp_works = RepLogin.find(:all, :include => "report", :conditions => ["reports.category IN (?) AND reports.date BETWEEN (?) AND (?)", @contract.categories.select{|al| al.department == 'alp'}.map{|al| al.id}.join(', '), @from, @to ], :group => "rep_logins.from_id, rep_logins.to_id, rep_logins.material_id") for alp_w in @alp_works do #### У АЛЬПОВ СВОЙ ВАРИАНТ i += 1 if alp_w.login_from login_from = alp_w.login_from.full_address else login_from = "Не задано" end if alp_w.login_to login_to = alp_w.login_to.full_address else login_to = "Не задано" end dataBook1.merge_cells(i, 0, i, 7) dataBook1.row(i).push login_from + " - " + login_to dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 i += 1; dataBook1.row(i).push "", "1", "Строительно-монтажные работы" i += 1; dataBook1.row(i).push "", "1.1", "Провес оптического кабеля", "-", "м", (alp_w.cable_length.to_i - alp_w.attic_length.to_i), "115".to_f, a =(alp_w.cable_length.to_i - alp_w.attic_length.to_i)*115 i += 1; dataBook1.row(i).push "", "1.2", "Прокладка оптического кабеля по техническим помещениям", "-", "м", (alp_w.attic_length.to_i), "50".to_f, b=(alp_w.attic_length.to_i)*50 i += 1; dataBook1.row(i).push "", "", "Итого сумма за работы без НДС:", "", "", "", (a+b).to_f dataBook1.row(i).default_format = format_small i += 1; dataBook1.row(i).push "", "2", "Материалы Заказчика" dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 m2=0;m3=0 if alp_w.done_material.material_detail_section == 2 num = alp_w.done_material.material_detail_section.to_s+"."+alp_w.done_material.material_detail_number.to_s i += 1; dataBook1.row(i).push num, num, alp_w.done_material.material_name, "-", "м", alp_w.cable_length, alp_w.done_material.material_detail_sum, m2=(alp_w.cable_length.to_i* alp_w.done_material.material_detail_sum.to_f) end i += 1; dataBook1.row(i).push "", "3", "Материалы Исполнителя" dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 if alp_w.done_material.material_detail_section == 3 num = alp_w.done_material.material_detail_section.to_s+"."+alp_w.done_material.material_detail_number.to_s i += 1; dataBook1.row(i).push num, num, alp_w.done_material.material_name, "-", "м", alp_w.cable_length, alp_w.done_material.material_detail_sum, m3=(alp_w.cable_length.to_i* alp_w.done_material.material_detail_sum.to_f) end i += 1; dataBook1.row(i).push "", "", "Итого сумма за материалы без НДС:", "", "", (m2+m3).to_f dataBook1.row(i).default_format = format_small end end @adresses = [] @logins.each do |login| i += 1 row = dataBook1.row(i) dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 login_n = LoginRoot.find(:first, :conditions =>{:loginroot => login}) if login_n login_name = login_n.full_address else login_n = Login.find(:first, :conditions =>{:ulogin => login}) unless login_n if login_n login_name = login_n.full_address else login_name = login end login_name = "г. Москва " + login_name end dataBook1.merge_cells(i, 0, i, 7) row[0] = login_name @adresses << login_name dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 i += 1 row = dataBook1.row(i) row[1] = "1" row.default_format = format dataBook1.row(i).height = 15 row[2] = "Строительно-монтажные работы" log_works = @works.select{|w| w.login == login} sum_r = 0 for w in log_works do i += 1 dataBook1.row(i).push "1."+w.number.to_s, "1."+w.number.to_s, w.of_name, '-', w.measure_name, w.count, w.price.to_f, s = w.price.to_f * w.count.to_f sum_r += s end i += 1; dataBook1.row(i).push "", "", "Итого сумма за работы без НДС:", "", "", "","", sum_r.to_f; dataBook1.row(i).default_format = format_small log_materials = @materials.select{|w| w.report.name == login && w.material_detail_section == 2 && w.material_detail_number != nil}.sort_by{|w| w.material_detail_number} i += 1 dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 dataBook1.row(i).push '', '2', 'Материалы Заказчика' sum_m = 0 for w in log_materials do i += 1 count_n = w.material_detail_section.to_s+"."+w.material_detail_number.to_s dataBook1.row(i).push count_n, count_n, w.material_name, '-', 'шт.', w.count, w.material_detail_sum.to_f, s=(w.count.to_i*w.material_detail_sum.to_f).to_f sum_m += s end log_materials = @materials.select{|w| w.report.name == login && w.material_detail_section == 3 && w.material_detail_number != nil}.sort_by{|w| w.material_detail_number} i += 1 dataBook1.row(i).default_format = format dataBook1.row(i).height = 15 dataBook1.row(i).push '', '3', 'Материалы Исполнителя' for w in log_materials do i += 1 count_n = w.material_detail_section.to_s+"."+w.material_detail_number.to_s dataBook1.row(i).push count_n, count_n, w.material_name, '-', 'шт.', w.count, w.material_detail_sum.to_f, s=(w.count.to_i*w.material_detail_sum.to_f).to_f sum_m += s end i += 1; dataBook1.row(i).push "", "", "Итого сумма за материалы без НДС:", "", "", "", "", sum_m.to_f; dataBook1.row(i).default_format = format_small end # dataBook1.row(i).push '', '2', 'Материалы Заказчика' all_sum = @materials.map{|w| w.count.to_f * w.material_detail_sum.to_f}.sum + @works.map{|w| w.count.to_f*w.price.to_f}.sum if @alp_works all_sum += @alp_works.map{|w| (w.cable_length.to_i- alp_w.attic_length.to_i)*115}.sum + @alp_works.map{|w| alp_w.attic_length.to_i*50}.sum + @alp_works.map{|w| w.cable_length.to_f * w.done_material.material_detail_sum.to_f}.sum() end format2 = Spreadsheet::Format.new :weight => :bold, :size => 12, :horizontal_align => :right i += 1; dataBook1.merge_cells(i, 2, i, 6); dataBook1.row(i).height = 15 dataBook1.row(i).push "", "", "Итого за работы и материалы без НДС 18%:", "", "","","", all_sum; dataBook1.row(i).default_format = format2 i += 1; dataBook1.merge_cells(i, 2, i, 6); dataBook1.row(i).height = 15 dataBook1.row(i).push "", "", "НДС 18%:", "", "","","", all_sum*18/100; dataBook1.row(i).default_format = format2 i += 1; dataBook1.merge_cells(i, 2, i, 6); dataBook1.row(i).height = 15 dataBook1.row(i).push "", "", "Итого за работы и материалы с НДС 18%:", "", "","","", all_sum*18/100+all_sum; dataBook1.row(i).default_format = format2 fmt = Spreadsheet::Format.new(:number_format => "0.00") 1000.times {|j| dataBook1.row(j).set_format(6, fmt); dataBook1.row(j).set_format(7, fmt)} ### Отдельный лист ИТОГО по работам и материалам. dataBook2 = book.create_worksheet :name => "ИТОГО" dataBook2.column(0).width = 11; dataBook2.column(1).width = 50; dataBook2.column(2).width = 13; i = 1 dataBook2.row(i).default_format = format dataBook2.row(i).height = 15 dataBook2.row(i).push "", "Строительно-монтажные работы" if @alp_works i += 1; dataBook2.row(i).push "1.1", "Провес оптического кабеля", "м", m1=@alp_works.map{|alp_w| alp_w.cable_length.to_i - alp_w.attic_length.to_i}.sum(), "115",m1*115 i += 1; dataBook2.row(i).push "1.2", "Прокладка оптического кабеля по техническим помещениям", "м", m2=@alp_works.map{|alp_w| alp_w.attic_length.to_i}.sum(), "50", m2*50 end for work_name in @works.map{|w| w.of_name}.uniq!.to_a do i += 1 works = @works.select{|w| w.of_name == work_name} count_n = "1."+works.first.number.to_s dataBook2.row(i).push count_n, work_name, works.first.measure_name, count=works.map{|w| w.count.to_f}.sum(), sum=works.first.price.to_f, s=count*sum end i += 1 dataBook2.row(i).default_format = format dataBook2.row(i).height = 15 dataBook2.row(i).push "", "Материалы Заказчика" if @alp_works ws = @alp_works.select{|aw|aw.done_material.material_detail_section == 2} for material_name in ws.map{|aw| aw.material_name}.uniq.to_a do materials = ws.select{|aw| aw.material_name == material_name} num = "2."+materials.first.done_material.material_detail_number.to_s i += 1; dataBook2.row(i).push num, material_name, "м", length=materials.map{|a| a.cable_length.to_i}.sum, price=materials.first.done_material.material_detail_sum.to_f, (length*price).to_f end end materials = @materials.select{|w| w.material_detail_section == 2 && w.material_detail_number != nil}.sort_by{|w| w.material_detail_number} for material in materials.map{|m| m.material_name}.uniq!.to_a do i += 1 mat = materials.select{|m| m.material_name == material} count_n = mat.first.material_detail_section.to_s+"."+mat.first.material_detail_number.to_s dataBook2.row(i).push count_n, material, 'шт.', count=mat.map{|w| w.count.to_f}.sum, sum= mat.first.material_detail_sum.to_f, (count*sum).to_f end i += 1 dataBook2.row(i).default_format = format dataBook2.row(i).height = 15 dataBook2.row(i).push "", "Материалы Исполнителя" if @alp_works ws = @alp_works.select{|aw|aw.done_material.material_detail_section == 3} for material_name in ws.map{|aw| aw.material_name}.uniq!.to_a do materials = ws.select{|aw| aw.material_name == material_name} num = "3."+materials.first.done_material.material_detail_number.to_s i += 1; dataBook2.row(i).push num, material_name, "м", length=materials.map{|aw| aw.cable_length.to_i}.sum, price=materials.first.done_material.material_detail_sum.to_f, length*price end end materials = @materials.select{|w| w.material_detail_section == 3 && w.material_detail_number != nil}.sort_by{|w| w.material_detail_number} for material in materials.map{|m| m.material_name}.uniq.to_a do i += 1 mat = materials.select{|m| m.material_name == material} count_n = mat.first.material_detail_section.to_s+"."+mat.first.material_detail_number.to_s c=mat.map{|w| w.count.to_f}.sum() p=mat.first.material_detail_sum.to_f dataBook2.row(i).push count_n, material, 'шт.', c, p, c*p end ### Отдельный лист - список объектов. dataBook3 = book.create_worksheet :name => "Список адресов" dataBook3.column(0).width = 50; format = Spreadsheet::Format.new :weight => :bold, :size => 12 i = 0 for adress in @adresses do dataBook3.row(i).default_format = format dataBook3.row(i).height = 15 dataBook3.row(i).push adress i += 1 end # Write book @file = 'excel-' + rand().to_s + '.xls' book.write 'public/'+@file # debugger render :template => 'contracts/ks_download.html.erb', :layout => false end def index @contracts = Contract.all end def new @contract = Contract.new @categories = Category.find(:all, :order => "department, name") end def create @contract = Contract.create(params[:contract]) if @contract.new_record? flash[:error] = "Ошибка" end respond_to do |format| unless flash.size > 0 flash[:notice] = 'Запись обновлена' format.html { redirect_to contracts_path} format.xml { head :ok } else format.html { render :action => "new", :contract => @contract, :categories => Category.find(:all, :order => "department, name") } end end end def edit @contract = Contract.find(params[:id]) @categories = Category.find(:all, :order => "department, name") end def update @contract = Contract.find(params[:id]) unless @contract.update_attributes(params[:contract]) flash[:error] = "Ошибка" end respond_to do |format| unless flash.size > 0 flash[:notice] = 'Запись обновлена' format.html { redirect_to contracts_path} format.xml { head :ok } else format.html { render :action => "edit", :contract => @contract, :categories => Category.find(:all, :order => "department, name") } end end end end