1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
def self.default_fields
      fields = ActiveSupport::OrderedHash.new
      fields['Country_Name'] = 'country.AlternativeForm'      
      fields['Region']       = 'region.gwi_region_code'
      fields['SubRegion']         = 'subregion.gwi_subregion_code'
      fields['Location']          = 'location.Town'
      fields['Location_Type']     = 'locationtype.LocationType'
      fields['Project_Name']      = 'trim(project.ProjectName)'
      fields['Capacity']          = 'project.OutputWaterFrom'
      fields['Size']              = 'IF(project.OutputWaterFrom != 0,IF(project.OutputWaterFrom < 999,"S", IF(project.OutputWaterFrom >= 50000,"XL",IF(project.OutputWaterFrom >= 1000 AND project.OutputWaterFrom < 10000,"M",  IF(project.OutputWaterFrom >= 10000 AND project.OutputWaterFrom < 50000,"L","")))),"")'
      fields['MGD_Capacity']      = 'IF(project.OutputWaterFrom != 0,ROUND(project.OutputWaterFrom / 3785,2),"") '
      fields['MIGD_Capacity']     = 'IF(project.OutputWaterFrom != 0,ROUND(project.OutputWaterFrom / 4546,2),"")'
      fields['Units']             = 'project.TotalUnits'
      fields['Unit_Size']         = 'IF(project.TotalUnits != 0 ,ROUND(project.OutputWaterFrom / project.TotalUnits,2) , "")'
      fields['MGD_Unit_Size']     = 'IF(project.TotalUnits != 0 ,ROUND((project.OutputWaterFrom / 3785) / project.TotalUnits,2) , "")'
      fields['MIGD_Unit_Size']    = 'IF(project.TotalUnits != 0 ,ROUND((project.OutputWaterFrom / 4546) / project.TotalUnits,2) , "")'
      fields['Technology']        = 'technology.Technology'
      fields['Thermal_Design']     = 'IF(technology.TechnologyID  = 3, med_design.name, IF(technology.TechnologyID = 2,msf_design.name     , ""   ) )'
      fields['Thermal_Equipment']   = 'IF(technology.TechnologyID  = 3, med_equip.name, IF(technology.TechnologyID = 2,msf_equip.name , ""   ) )'
      fields['RO_Energy_Recovery']  = 'IF(technology.TechnologyID  = 4 , ro_ener.ro_energyrecovery , IF(technology.TechnologyID = 5   , ed_type.ed_type  , IF(technology.TechnologyID = 7 , edi_type.edi_type  ,"")    ))'      
      fields['RO_Membrane_Type']    = 'IF(technology.TechnologyID  = 4 , ro_mem.ro_membranetype , IF(technology.TechnologyID = 5 ,ed_equip.ed_equipment ,IF(technology.TechnologyID = 7 , ed_equip.ed_equipment,"") )  )'
      fields['Raw_Water_Type']      = 'rawwatertype.RawWaterType'      
      fields['Award_Date']          = 'project.AwardDate'
      fields['Online_Date']         = 'project.OnlineDate'
      fields['PlantSuppliers']      =  '""'
      fields['HoldingCompany']      =  '""'
      fields['Website']             =  '""'
      fields['MembraneSuppliers']   =  '""'
      fields['Consultant']          =  '""'
      fields['User_Category']       = 'usercategory.UserCategory'
      fields['Customer']            = '""'
      fields['Plant_Status']        = 'plantstatus.PlantStatus'
      fields['Project_ID']          = 'project.ProjectID'
      fields['Epc']   =  '""'
      fields['Epc2']  =  '""'
      fields['Epc3']  =  '""'
      fields['Epc4']  =  '""'
      fields['Epc5']  =  '""'
      fields['PrivateFinance'] = '""'
      fields['Developer'] = '""'
      fields['Developer2'] ='""'
      fields['Developer3'] = '""'
      fields['Developer4'] = '""'
      fields['Developer5'] ='""'
      fields['Developer6'] ='""'
      fields['EPCPrice'] ='""'
      fields['WaterPrice'] = 'project.WaterPrice'
      fields['Date_Added']  = 'project.Registered'
      fields
  end

def self.download_projects(params)
    
    fields = params[:fields]
    query = []
    self.default_fields.each{|kv| query.push(kv[1] + " as #{kv[0]} ") if fields.include?(kv[0]) }
    query.push(
       'GROUP_CONCAT(DISTINCT CONCAT("{\"OrganisationID\":\"",IFNULL(s.OrganisationID,""),"\",","\"OrganisationName\":\"" ,IFNULL(s.OrganisationName,""),"\",","\"NodeLevel\": \"",s.NodeLevel,"\",","\"LeftVal\": \"",s.LeftVal,"\",","\"RightVal\":\"",s.RightVal,"\",","\"AssignmentID\" : \"",IFNULL(r.AssignmentID,""),"\",","\"GroupTopID\":","\"",IFNULL(s.GroupTopID,s.OrganisationID),"\"","}")                                
            ORDER BY s.OrganisationName ASC SEPARATOR ","
         ) as Companies'
    )
      query = 'SELECT SQL_CACHE ' + query.join(",") + ' FROM 
      tbl_projmain project
      LEFT JOIN tbl_counmain  country ON project.CountryID      = country.CountryID 
      LEFT JOIN locationtype          locationtype ON project.LocationType   = locationtype.LocationTypeID 
      LEFT JOIN tbl_projtechnology    technology ON project.TechnologyID   = technology.TechnologyID 
      LEFT JOIN tbl_projrawwatertype  rawwatertype ON project.RawWaterType   = rawwatertype.RawWaterTypeID 
      LEFT JOIN tbl_projusercategory  usercategory ON project.UserCategory   = usercategory.UserCategoryID 
      LEFT JOIN tbl_projplantstatus   plantstatus ON project.PlantStatus    = plantstatus.PlantStatusID 
      LEFT JOIN tbl_projscope         h ON project.ProjectScope   = h.ProjectScopeID
      LEFT JOIN tbl_projcontracttype  i ON project.ContractType   = i.ContractTypeID 
      LEFT JOIN tbl_projdealstructure j ON project.DealStructure  = j.DealStructureID 
      LEFT JOIN tbl_projcontractscope k ON project.ContractScope  = k.ContractScopeID 
      LEFT JOIN tbl_projscopetype     l ON project.ScopeType      = l.ProjectTypeID 
      LEFT JOIN tbl_projplanttype     m ON project.PlantType      = m.PlantTypeID 
      LEFT JOIN tbl_globtown          location ON project.TownID         = location.TownID 
      LEFT JOIN gwi_subregions        subregion ON country.GWISubRegionID = subregion.gwi_subregion_id 
      LEFT JOIN gwi_regions           region ON subregion.gwi_region_id  = region.gwi_region_id 
      LEFT JOIN tbl_projorg           r ON project.ProjectID      = r.ProjectID 
      LEFT JOIN tbl_orgmain           s ON r.OrganisationID = s.OrganisationID 
      LEFT JOIN tbl_projdesal_td   td   ON project.ProjectID = td.ProjectID 
      LEFT JOIN tbl_projdesal_ro  ro    ON ro.desal_id = td.desal_id 
      LEFT JOIN ro_membranetype ro_mem  on ro.ROMembraneType1 = ro_mem.ro_membranetypeid 
      LEFT JOIN ro_energyrecovery ro_ener  on ro.ROEnergyRecovery1 = ro_ener.ro_energyrecoveryid 
      LEFT JOIN tbl_projdesal_ed  ed   on ed.desal_id = td.desal_id 
      LEFT JOIN ed_type on ed_type.ed_typeid = ed.EDType 
      LEFT JOIN ed_equipment ed_equip on ed_equip.ed_equipmentid = ed.EDEquipment 
      LEFT JOIN tbl_projdesal_edi edi  on edi.desal_id = td.desal_id 
      LEFT JOIN edi_equipment edi_equip  on edi_equip.edi_equipmentid = edi.EDIEquipment 
      LEFT JOIN edi_type  on edi_type.edi_typeid = edi.EDIType 
      LEFT JOIN tbl_projdesal_med med  on med.desal_id = td.desal_id 
      LEFT JOIN thermal_design  med_design  on med.MEDDesign = med_design.id 
      LEFT JOIN thermal_equipment   med_equip  on med.MEDEquipment = med_equip.id 
      LEFT JOIN tbl_projdesal_msf msf  on msf.desal_id = td.desal_id 
      LEFT JOIN thermal_equipment  msf_equip  on msf.MSFEquipment = msf_equip.id 
      LEFT JOIN thermal_design  msf_design  on msf.MSFDesign = msf_design.id 
      WHERE project.RecordStatus in (1,4) GROUP BY project.ProjectID LIMIT 500'      
    
    @parent_companies = Hash.new
    Rails.cache.fetch('parent_companies') do 
      Organisation.all(:conditions=>['rightval-leftval>0'],:select => "OrganisationID,OrganisationName,GroupTopID")
      end.map do |org|
      id = org.GroupTopID.nil? or org.GroupTopID.blank? ? org.OrganisationID : org.GroupTopID
      @parent_companies[id] = org
    end      
    projects = Project.find_by_sql(query)
    #process the company (in json format and attach it to the placeholder columns)
    #self.summarize_roles(projects)    
  end
 
  
  def self.get_ultimate_parent(group_id)
    if(@parent_companies.include?(group_id))
       company = @parent_companies[group_id]
       if(company.OrganisationID != company.GroupTopID)
         self.get_ultimate_parent(company.GroupTopID)
       else 
         company
       end                
    end
  end
  
  
  
  def self.summarize_roles2(project)

      unless project['Companies'].nil?
        ActiveSupport::JSON.decode("[" + project['Companies'] + "]").each{ |company|
          if(!company['OrganisationID'].nil?  and !company['AssignmentID'].nil? )
            asg = company['AssignmentID'].to_i
            name = company['OrganisationName']
            
            if(asg ==22)
              project['Customer']   <<  "#{project['Customer'].empty? ? "" : " / "}"  + name
            elsif(asg == 26)
              project['Consultant'] <<  "#{project['Consultant'].empty? ? "" : " / "}"  + name
            elsif(asg == 36)
              project['MembraneSuppliers'] <<  "#{project['MembraneSuppliers'].empty? ? "" : " / "}"  + name
            elsif(asg == 1)
              project['Developer'] << "#{project['Developer'].empty? ? "" : "_"}"  + name
            elsif(asg == 6)
              project['PlantSuppliers'] << "#{project['PlantSuppliers'].empty? ? "" : " / "}"  + name
             
              if(company['NodeLevel'].to_i == 1) #this is the ultimate parent company
                if(project['Epc'].nil?)
                  project['Epc'] = name
                else
                  project['Epc'] << "_#{name}"
                end
              else
                #derive the parent from the list of parent_companies                
                parent_company = self.get_ultimate_parent(company['GroupTopID'])
                if(!parent_company.nil?)
                  if(project['Epc'].nil?)
                    project['Epc'] = parent_company.OrganisationName
                  else
                    project['Epc'] << "_#{parent_company.OrganisationName}"
                  end
                  
                end
              end
            end
   
          end  
        }
     
      end 
       #this doesn't work at all    
       if(!project['Epc'].nil?)
          epcs =  project['Epc'].split(/_/).to_a
          project['HoldingCompany'] <<  epcs.count > 1 ? "FindHoldingCompany" : project['Epc'].to_s 
          epcs.each_with_index do |v,k|
            i =  k == 0 ? "" : k+1
            project["Epc#{i.to_s}"] << v
            
          end
        end
     
      project 
  end