def import_bookings
file_path = "/home/amura/carcanet_booking_test.xls"
file = File.open(file_path)
#client = Client.find("54a7eb6ca7a03936ab000d89")
client = Client.find("56a6097eaa8fea9c2200019e")
book = Spreadsheet.open(file)
field_order = {"0" => "lead_crm_id", "1" => "Booking name 1", "2" => "Booking Name 2", "3" => "Booking Name 3","4"=>"booking_date","5"=>"email id","6"=>"contact no","7"=>"project_unit_id","8"=>"project_unit_name","9"=>"primary_post_sales_email","10"=>"water_connection_due_after_days","11"=>"mseb_connection_due_after_days","12"=>"corpus_charges_due_after_days","13"=>"registration_due_after_days","14"=>"stamp_duty_due_after_days","15"=>"received amount","16"=>"agreement_amount_due_after_0_ WEEKS","17"=>"15/Aug","18"=>"30/Aug","19"=>"15/Sep","20"=>"30/Sep","21"=>"15/Oct","22"=>"30/Oct","23"=>"15/Nov","24"=>"30/Nov","25"=>"15/Dec","26"=>"30/Dec","27"=>"15/Jan","28"=>"30/Jan","29"=>"sum","30"=>"agreement cost"}
count = 0
additional_cost_names = {}
sheet = book.worksheet 0
sheet.each_with_index do |row,i|
if i > 0
lead_crm_id = row[field_order.key("lead_crm_id").to_i].to_s

lead_phone = row[field_order.key("contact no").to_i].to_s.strip
lead_email = row[field_order.key("email id").to_i].to_s.strip.downcase
booking_name1 = row[field_order.key("Booking name 1").to_i].to_s.strip rescue ""
booking_name2 = row[field_order.key("Booking Name 2").to_i].to_s.strip rescue ""
booking_name3 = row[field_order.key("Booking Name 3").to_i].to_s.strip rescue ""
project_unit_id = row[field_order.key("project_unit_id").to_i].to_s.strip
project_unit = ProjectUnit.find project_unit_id
puts "unit found-------------", project_unit.name
#project_tower_id = "576396ba3bb2f8a972000287" #name: 106 acre
project_tower_id = "56a60be7aa8fea2ac7000281"
project_tower = ProjectTower.find project_tower_id
puts "tower name---------", project_tower.name

#project_id = "54c10aafa7a0395bc8000274" #name: Royal Purandar
project_id ="56a60b97aa8feaba9d000272"
project = Project.find project_id
puts "proj name*************", project.name

#post_sales_user = client.users.find "568d19f3a7a0396a66000031" #user sonam
post_sales_user = client.users.find "56fa7f01aa8fea29c0000021"
puts "ps name*************", post_sales_user.name

start_date = Date.parse("01/08/2016")
booking_date = Date.strptime(row[field_order.key("booking_date").to_i].to_s.strip, "%d.%m.%y")
puts "booking date----------------", booking_date

#dl_template_id = "568f7adfa7a039e1700006f5" #Dl template 1
dl_template_id = "56a60b98aa8feaba9d000274"

if lead_crm_id.present?
lead_crm_id = lead_crm_id.split("#")[1].strip
lead = client.leads.where(lead_id: lead_crm_id).first
else
lead = client.leads.new
lead.primary_email = EmailAddress.new(email: lead_email) if lead_email.present?
lead.lead_profile = LeadProfile.new()

# phone = PhoneNumberFormatter.format_number(lead_phone.strip) rescue nil
# lead.secondary_phones = phone.ph_number
# puts "lead phone****************8", lead.primary_phone

if booking_name1.present?
name_array = booking_name1.split(" ")
lead.first_name = name_array.first
lead.last_name = name_array.last
end
lead.sales = client.sales.first
lead.currently_in = lead.sales.department if lead.sales.present?
puts "lead******************valid-----", lead.valid?
lead.save!
end
puts "lead id****************", lead.id, lead.name
if lead.present?
puts "lead present*******************", lead.id

#create schedule
payment_schedule = client.payment_schedules.new
payment_schedule.payment_scheduler = project_tower
payment_schedule.name = project_unit.name + "_schedule"
payment_schedule.status = "approved"
payment_schedule.floor_wise_type = "custom"
payment_schedule.date_wise_schedule = true
payment_schedule.schedule = []

water_date = start_date + row[field_order.key("water_connection_due_after_days").to_i].to_s.strip.to_i.weeks
water_date = water_date.strftime("%d/%m/%Y")

mseb_date = start_date + row[field_order.key("mseb_connection_due_after_days").to_i].to_s.strip.to_i.weeks
mseb_date = mseb_date.strftime("%d/%m/%Y")

corpus_date = start_date + row[field_order.key("corpus_charges_due_after_days").to_i].to_s.strip.to_i.weeks
corpus_date = corpus_date.strftime("%d/%m/%Y")

reg_buffer = row[field_order.key("registration_due_after_days").to_i].to_s.strip.to_i
if reg_buffer == -1
reg_buffer = 0
#create new recipt obj
end
registration_date = start_date + reg_buffer.weeks
registration_date = registration_date.strftime("%d/%m/%Y")

stamp_duty_buffer = row[field_order.key("stamp_duty_due_after_days").to_i].to_s.strip.to_i
if stamp_duty_buffer == -1
stamp_duty_buffer = 0
#create new receipt_object
end
stamp_duty_date = start_date + stamp_duty_buffer.weeks
stamp_duty_date = stamp_duty_date.strftime("%d/%m/%Y")

payment_schedule.schedule << {"order"=>"1", "key"=>"#{water_date}", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"water_connection", "template_id"=> dl_template_id}
payment_schedule.schedule << {"order"=>"2", "key"=>"#{mseb_date}", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"mseb_connection", "template_id"=> dl_template_id}
payment_schedule.schedule << {"order"=>"3", "key"=>"#{corpus_date}", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"corpus_charges", "template_id"=> dl_template_id}
payment_schedule.schedule << {"order"=>"4", "key"=>"#{registration_date}", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"registration_charges", "template_id"=> dl_template_id}
payment_schedule.schedule << {"order"=>"5", "key"=>"#{stamp_duty_date}", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"stamp_duty", "template_id"=> dl_template_id}
count = 6
agreement_first_amount = row[field_order.key("agreement_amount_due_after_0_ WEEKS").to_i].to_s.strip
aug_15 = row[field_order.key("15/Aug").to_i].to_s.strip
aug_30 = row[field_order.key("30/Aug").to_i].to_s.strip
sep_15 = row[field_order.key("15/Sep").to_i].to_s.strip
sep_30 = row[field_order.key("30/Sep").to_i].to_s.strip
oct_15 = row[field_order.key("15/Oct").to_i].to_s.strip
oct_30 = row[field_order.key("30/Oct").to_i].to_s.strip
nov_15 = row[field_order.key("15/Nov").to_i].to_s.strip
nov_30 = row[field_order.key("30/Nov").to_i].to_s.strip
dec_15 = row[field_order.key("15/Dec").to_i].to_s.strip
dec_30 = row[field_order.key("30/Dec").to_i].to_s.strip
jan_15 = row[field_order.key("15/Jan").to_i].to_s.strip
jan_30 = row[field_order.key("30/Jan").to_i].to_s.strip

if agreement_first_amount.present?
payment_schedule.schedule  << {"order"=>"#{count}", "key"=>start_date.strftime("%d/%m/%Y"), "op1"=>agreement_first_amount, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1

end

if aug_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/08/2016", "op1"=>aug_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if aug_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/08/2016", "op1"=>aug_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end

if sep_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/09/2016", "op1"=>sep_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if sep_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/09/2016", "op1"=>sep_30, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end

if oct_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/10/2016", "op1"=>oct_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if oct_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/10/2016", "op1"=>oct_30, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end

if nov_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/11/2016", "op1"=>nov_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if nov_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/11/2016", "op1"=>nov_30, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end

if dec_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/12/2016", "op1"=>dec_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if dec_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/11/2016", "op1"=>dec_30, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end

if jan_15.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"15/01/2017", "op1"=>jan_15, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
count += 1
end
if jan_30.present?
payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/01/2017", "op1"=>jan_30, "calculation_type"=>"absolute", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
else
	payment_schedule.schedule << {"order"=>"#{count}", "key"=>"30/01/2017", "op1"=>"100", "calculation_type"=>"calculate", "cost_type"=>"agreement_value", "template_id"=> dl_template_id}
end
count += 1
if payment_schedule.save
names = []
names << lead.name if lead.name.present?
names << booking_name1 if booking_name1.present?
names << booking_name2 if booking_name2.present?
names << booking_name3 if booking_name3.present?
names = names.flatten.compact

booking_detail = BookingDetail.new
booking_detail.lead = lead
booking_detail.client_id = lead.client_id
booking_detail.sales_id = lead.sales.id
booking_detail.primary_post_sales_id = post_sales_user.id
booking_detail.email = lead.p_em.present? ? lead.p_em : lead_email
booking_detail.phone = lead.p_ph.present? ? lead.p_ph : lead_phone
booking_detail.booking_names = names.clone
booking_detail.stage = "tentative"
booking_detail.payment_schedule = payment_schedule
booking_detail.project_unit_id = project_unit_id
booking_detail.build_cost_sheet_with_costs({})
if booking_detail.valid?
booking_detail.save
booking_detail.stage = "confirmed"
booking_detail.save
else
puts "Booking invalid----------------", booking_detail.errors.full_messages
end

else
puts "error in saving schedule-------------------", payment_schedule.errors.full_messages
end
end
end#End if i>0
end#end of loop
end