
// AppController.j
// RMSchedulerController
//
// Created by Alonso Villanueva

@import <Foundation/Foundation.j>
@import <AppKit/AppKit.j>
@import <MapKit/MKMapView.j>
@import <AppKit/CPTextField.j>
@import <AppKit/CPApplication.j>
@import <Foundation/CPURLRequest.j>
@import "AppController.j"
@import "JasperSoftReporter.j"



// var appCont; //comment 2017-03-03





@implementation RMSReportPersonnelController : RMControllerObject {


	CPPanel repoPane;
	
	id persPopu;	
	id rolePopu;
	
	id persArra;
	
	id dateStarText;
	id dateEndText;
	id datePickStarPane;
	id datePickEndPane;
	id idenOrki;
	
	id idenPers;
	id namePers;
	id lastNamePers;
	id emaiPers;
	
	id jasp;
  	id dictRepo;
  	id urlRepo;
  	
  	id emaiComm;
  	
}



- (id) init:(id)paraAppCont {
	[super init];
 	
 	appCont = paraAppCont;
	dataBase = [appCont getDataBase];
	repoPane = NULL;
	rolePopu = NULL;
	dateStarText = NULL;
	dateEndText = NULL;
	idenOrki = "orkin_cr";
	idenPers = NULL;
	namePers = NULL;
	lastNamePers =NULL;
	emaiPers  = NULL;
	//jasp = [[JasperSoftReporter alloc] init];
	jasp = [[JasperSoftReporter alloc] init:appCont];	
	[jasp organizationCodeSet:[appCont getOrgaCode] ];
	[jasp organizationIdSet:[appCont organizationId] ];
	[jasp cgiSetHost:[appCont getcgiHost] server:[appCont getcgiServ]];
	dictRepo = NULL;
	urlRepo = NULL;
	persArra = NULL;
	emaiComm = "";

	return self;
}




- (void)  applicationQuit {

	if ( repoPane ) {
		[repoPane orderOut:0];
		[repoPane release];
	}
	if (persPopu) { [persPopu release];}
	if (rolePopu) { [rolePopu  release];}
	if (persArra) { [persArra  release];}
	if (dateStarText) { [dateStarText  release];}
	if (dateEndText) {dateEndText [dateEndText  release];}
	if (datePickStarPane) { [datePickStarPane  release];}
	if (datePickEndPane) { [datePickEndPane  release];}
	if (idenOrki) { [ idenOrki release];}
	if (idenPers) { [ idenPers release];}
	if (namePers) { [namePers  release];}
	if (lastNamePers) { [lastNamePers  release];}
	if (emaiPers) { [emaiPers  release];}	
	if (dictRepo) { [ dictRepo release];}
	if (urlRepo) { [urlRepo  release];}
	if (emaiComm) { [ emaiComm release];}
	[jasp applicationQuit];
	if (jasp) { [jasp  release];}
	
}





//





- (void) datePickerStartPanel:(id)sender {

	if ( datePickStarPane ) {
		[datePickStarPane orderFront:self];
	}else{
	
	var fram = CGRectMake(350,150,144,148);
	datePickStarPane = [[CPPanel alloc] initWithContentRect:fram styleMask:CPHUDBackgroundWindowMask|CPClosableWindowMask]; 
	[datePickStarPane setFloatingPanel:YES];
	[datePickStarPane setTitle:@"Fecha Inicio"];
	[datePickStarPane orderFront:self];

	var paneContView = [datePickStarPane contentView];

	fram = CGRectMake(0,0,144,148);
	var datePick = [[CPDatePicker alloc] initWithFrame:fram];
	[datePick setBackgroundColor:[CPColor whiteColor]];
	[datePick setTimeZone:[CPTimeZone defaultTimeZone]];
	[datePick setDatePickerElements:128];
	var dpe = [datePick datePickerElements];
	[datePick setDatePickerStyle:1];
	var dps = [datePick datePickerStyle];
	[datePick setDateValue:[CPDate date]];
	[datePick setObjectValue:[CPDate date]];
	[datePick setTarget:self];
	[datePick setAction:@selector(datePickerStartDateChange:)];
	//alert("dpe="+dpe+"  dps="+dps);
	
	[paneContView addSubview:datePick];
	}
}



- (void) datePickerStartDateChange:(id)sender {
	[dateStarText setStringValue:[[[sender dateValue] description] substringToIndex:10]];
	[datePickStarPane close];
}



- (void) datePickerEndPanel:(id)sender {

	if ( datePickEndPane ) {
		[datePickEndPane orderFront:self];
	}else{
	var fram = CGRectMake(350,150,144,148);
	datePickEndPane = [[CPPanel alloc] initWithContentRect:fram styleMask:CPHUDBackgroundWindowMask|CPClosableWindowMask]; 
	[datePickEndPane setFloatingPanel:YES];
	[datePickEndPane setTitle:@"Fecha Final"];
	[datePickEndPane orderFront:self];

	var paneContView = [datePickEndPane contentView];

	fram = CGRectMake(0,0,144,148);
	var datePick = [[CPDatePicker alloc] initWithFrame:fram];
	[datePick setBackgroundColor:[CPColor whiteColor]];
	[datePick setTimeZone:[CPTimeZone defaultTimeZone]];
	[datePick setDatePickerElements:128];
	var dpe = [datePick datePickerElements];
	[datePick setDatePickerStyle:1];
	var dps = [datePick datePickerStyle];
	[datePick setDateValue:[CPDate date]];
	[datePick setObjectValue:[CPDate date]];
	[datePick setTarget:self];
	[datePick setAction:@selector(datePickerEndDateChange:)];
	//alert("dpe="+dpe+"  dps="+dps);
	
	[paneContView addSubview:datePick];
	}
}



- (void) datePickerEndDateChange:(id)sender {
	[dateEndText setStringValue:[[[sender dateValue] description] substringToIndex:10]];
	[datePickEndPane close];
}



- (void) generateReport:(id)sender {
	
	var jaspTabl = "jsprFinalReport";
	var striStartDate = [[dateStarText stringValue] substringToIndex:10];
	var striEndDate = [[dateEndText stringValue] substringToIndex:10];
	var randCode =  [ [CPString UUID] substringToIndex:12];
	
	urlRepo = NULL;
	var persIdenTemp = lastNamePers + ", " + namePers;
	var titleRepo = "";
	var titleHeadList = "";
	
	
	var inde = [rolePopu indexOfSelectedItem];
	if (inde == 0) {
		return;
	}else if ( inde == 1){				
			persIdenTemp += " [S]:";
			//titleRepo = "Supervisor: " 
			titleHeadList = "Fecha:sc:Hora:sc:Lugar:sc:Asistente:sc:Vehiculo:sc:Actividad:sc:Comentarios";	
	}else if ( inde == 2){				
			persIdenTemp += " [E]:";
			//titleRepo = "Ejecutivo: " 
			titleHeadList = "Fecha:sc:Hora:sc:Lugar:sc:Asistente:sc:Vehiculo:sc:Actividad:sc:Comentarios";
	}else if (inde == 3){
			persIdenTemp += " [A]:";
			//titleRepo = "Asistente: ";  
			titleHeadList = "Fecha:sc:Hora:sc:Lugar:sc:Ejecutivo:sc:Vehiculo:sc:Actividad:sc:Comentarios";
	}
	
	//titleRepo = namePers + " " + lastNamePers + " " +striStartDate + " - " +striEndDate;
	titleRepo = "Programacion: "+ striStartDate + " - " + striEndDate + ":sc:" +  namePers + " " + lastNamePers; 
 	//Programación: startDate - EndDate:sc:Name
	
	persIdenTemp += idenPers;
	
	/* 
   var quer = "SELECT "; 
   quer += "SUBSTRING(" + dataBase + ".eventsPermanent.start_date,1,10), "; 
   quer +=  "SUBSTRING(" + dataBase + ".eventsPermanent.start_date,11), ";
   quer +=  "location,calendar,vehicle,IF(ISNULL(eventComm),' ',eventComm),IF(ISNULL(serieComm),' ',serieComm) ";
   quer += "FROM " + dataBase + ".eventsPermanent "
   quer +=  "WHERE personnel LIKE '%|" + persIdenTemp  + "|%'  ";
   quer += "AND  start_date >= '" + striStartDate + "'";
   quer +=  "AND end_date <= '" + striEndDate + "'";
   */
   
   var quer = "SELECT ";    
   quer += "SUBSTRING(" + dataBase + ".eventsPermanent.start_date,1,10) AS _date, "; 
   quer += "SUBSTRING(" + dataBase + ".eventsPermanent.start_date,12) AS _hour, ";
   
   quer += "SUBSTR(" + dataBase + ".organizationLocations.name, ";
   quer += "LOCATE('|'," + dataBase + ".organizationLocations.name)+1, ";
   quer += "(CHAR_LENGTH(" + dataBase + ".organizationLocations.name) - ";
   quer += "LOCATE(':',REVERSE(" + dataBase + ".organizationLocations.name)) ";
   quer += "- LOCATE('|'," + dataBase + ".organizationLocations.name))) AS nameLoca, ";
   
   quer += "SUBSTR(" + dataBase + ".eventsPermanent.calendar, ";
   quer += "LOCATE('|'," + dataBase + ".eventsPermanent.calendar)+1, ";
   quer += "(CHAR_LENGTH(" + dataBase + ".eventsPermanent.calendar) - ";
   quer += "LOCATE('|',REVERSE(" + dataBase + ".eventsPermanent.calendar)) ";
   quer += "- LOCATE('|'," + dataBase + ".eventsPermanent.calendar))) AS acti, ";
   
   quer += "SUBSTR(" + dataBase + ".eventsPermanent.vehicle, ";
   quer += "LOCATE('|'," + dataBase + ".eventsPermanent.vehicle)+1, ";
   quer += "(CHAR_LENGTH(" + dataBase + ".eventsPermanent.vehicle) - ";
   quer += "LOCATE('|',REVERSE(" + dataBase + ".eventsPermanent.vehicle)) ";
   quer += "- LOCATE('|'," + dataBase + ".eventsPermanent.vehicle))) AS vehi, ";
   
   quer += "IF(ISNULL(eventComm),' ',eventComm) AS even ,IF(ISNULL(serieComm),' ',serieComm) AS seri, ";
   quer += "personnel "

   quer += "FROM " + dataBase + ".eventsPermanent Inner Join "+dataBase+".eventPerson on eventsPermanent.id = eventPerson.idEvent ";
   quer += " INNER JOIN  organizationLocations on eventsPermanent.location = organizationLocations.id ";
   quer += "WHERE eventPerson.idPerson = " + idenPers  + "  ";
   quer += "AND  start_date >= (('" + striStartDate + " 00:00:00' - interval 1 day ) + interval 23 hour + interval 59 minute + interval 59 second ) "; 
   quer += "AND start_date <= '" + striEndDate + " 23:59:59' ";
   quer += "ORDER BY  start_date";
   console.log("query " + quer);
    var arraAux = [self query:quer];
 	var arraTempRepo = [CPArray array]; 		
 	
 	for (var i = 0; i < [ arraAux count]; i++){
 		var valu = [ [arraAux objectAtIndex:i] valueList ];
 		var date     = valu[0];		
		var hour     = valu[1];
		var loca     = valu[2];				
		var acti     = valu[3];
		acti = [acti stringByReplacingOccurrencesOfString:"|" withString:@", "];		
		var vehi     = valu[4];	
		var comm     = valu[5] + " " + valu[6];
		var pers     = valu[7];		
		pers = [pers stringByReplacingOccurrencesOfString:"|" + persIdenTemp + "|" withString:@""];	
		pers = [pers stringByReplacingOccurrencesOfString:"[S]:"  withString:@""];
		pers = [pers stringByReplacingOccurrencesOfString:"[E]:"  withString:@""];			
		pers = [pers stringByReplacingOccurrencesOfString:"[A]:"  withString:@""];			
		pers = [pers stringByReplacingOccurrencesOfString:"|"  withString:@" "];			
		for (var j = 0; j < 9; j++){
			pers = [pers stringByReplacingOccurrencesOfString:"" + j   withString:@""];
		}			
		var stri = " ('" + randCode + "','" + date + "','" + hour + "','" + loca + "','"  + pers + "','" + vehi + "','" + acti + "','" + comm + "')";
		[arraTempRepo addObject:stri];
	}
   
   var coun = [arraTempRepo count];
   
   if (coun == 0){
       	//alert("No se encontraron datos");
       	[glblAlerPane showWithMessage:@"Atencion" info:"No se encontraron datos"];
   		return;
   }
     
   var begiSql = "INSERT INTO " + dataBase + "." + jaspTabl + " (reportCode,FIELD01,FIELD02,FIELD03,FIELD04,FIELD05,FIELD06,FIELD07) VALUES " ;
   var middSql = "";
   
   if ( coun > 10 ){   
  		while( coun > 10){
   		   for (var i =0; i < 10; i++){  
   				var valu =  [arraTempRepo objectAtIndex:0];
				var temp  = i + 1;	
				if(temp == 10){
					middSql += valu;
				} else{
					middSql += valu + ",";
			    }
			    [arraTempRepo removeObjectAtIndex:0];
   				coun -=1;
   		   }

   	    quer =  begiSql + middSql ;
	    //[self query:quer];
	    //alert("Insert 1 " + quer);
	    [self query:quer];
    	middSql = "";

     }
   }
   
   console.log("query " + quer);
    coun = [arraTempRepo count];
    begiSql = "INSERT INTO " + dataBase + "." + jaspTabl + " (reportCode,FIELD01,FIELD02,FIELD03,FIELD04,FIELD05,FIELD06,FIELD07) VALUES " ;
    middSql = "";
    
    
    if (coun >= 1) {	   
   	for (var i =0; i < coun; i++){
   		var valu = [arraTempRepo objectAtIndex:i] ;
		var temp = i + 1;	
		if(temp == coun){
			middSql += valu; 
		} else{
			middSql += valu + ",";
		}
    }
    
     quer =  begiSql + middSql ;
    // alert("Insert 2 " + quer);
    [self query:quer];
 }
    console.log("query " + quer);
    
    //alert("repoCode" + randCode + "title " + titleRepo);
    
    dictRepo = [[CPDictionary alloc] init];
    [dictRepo setObject:titleRepo  forKey:"title" ];
    [dictRepo setObject:titleHeadList forKey:"headList" ];
    [dictRepo setObject:randCode forKey:"repoCode" ];
    [dictRepo setObject:" " forKey:"comment" ];
    [dictRepo setObject:"ORKIN de Costa Rica is an independent franchise of Orkin Systems, LLC" forKey:"testFoot"];
	[dictRepo setObject:dataBase forKey:"sourceOfData" ];
	[dictRepo setObject:"EjecutivoOrkinReport" forKey:"myModel" ];
	var jsprMsql = "b14_2"; 
	var myPathTemp = "/reports/Orkin";
	[dictRepo setObject:myPathTemp forKey:"myPath" ];
	
    urlRepo = [jasp  buildJasperURL:dictRepo];
   //   [jasp showPreviousView:urlRepo]; 
    var uri =  encodeURI(urlRepo);
	window.open(uri);
  
    
}


- (void) persSelectionPopUp:(id)sender {
	var inde = [persPopu indexOfSelectedItem];
	if (inde >= 0) {
		//inde --;
		var valuList = [persArra[inde] valueList];
		namePers     = valuList[0];
		lastNamePers = valuList[1];
		emaiPers     = valuList[2];	
		idenPers     = valuList[3];	
		//alert ("NAME "+  namePers + lastNamePers +  "emai "+  emaiPers + "iden "+  idenPers);
		
	}
	
}

- (void) reportsPanel {
	return repoPane;
}


- (void) reportsPanel:(id)sender {

	var horiPos1 = 10;
	var horiPos2 = horiPos1 + 100;
	var horiPos3 = 390;
	var horiPos4 = horiPos3 + 100;

	var horiTextWidt = 200;
	var horiPosiOffs = -4;
	var horiPosi = 200;

	var vertPosi = 20;
	var vertPosiOffs = -4;
	var vertIncr = 26;
	var vertLineHeig = 30;

	var paneWidt = 400;
	var paneHeig = 310;
	var tablWidt = paneWidt - 20;
	var tablHeig = 150;
	
	var x = 10 + Math.random()*40;
	var y = 60 + Math.random()*40;
	var fram = CGRectMake(x,y, paneWidt,paneHeig);

	repoPane = [[CPPanel alloc] initWithContentRect:fram styleMask:CPHUDBackgroundWindowMask|CPClosableWindowMask|CPResizableWindowMask]; 
	[repoPane setFloatingPanel:YES]; 
	[repoPane orderFront:self];
	[repoPane setTitle:@"Panel Reportes Personal"];
	
	var paneContView = [repoPane contentView];
	[paneContView setBackgroundColor:coloDefaLite];
	[self addBanners:paneContView heightBlue:1. heightOrange:4.];

	var view = paneContView;
	
	//Role
	var labe= [self addTextField:@"Posición:" TFPosX:horiPos1 TFPosY:vertPosi TFWidth:0 TFHeight:0 VView:view Editable:NO];
	[labe setTextColor:[CPColor whiteColor]];
	
	rolePopu = [self addPopupButton:@"Posición" TFPosX:horiPos2 TFPosY:vertPosi TFWidth:200 TFHeight:24  VView:view pullsDown:NO];
	[rolePopu addItemWithTitle:"Supervisor"];
	[rolePopu addItemWithTitle:"Ejecutivo"];
	[rolePopu addItemWithTitle:"Asistente"];
	[rolePopu setTarget:self];
	[rolePopu setAction:@selector(roleSelectionPopUp:)];
	
	//Persons
	vertPosi += 40;	
	labe= [self addTextField:@"Empleados:" TFPosX:horiPos1 TFPosY:vertPosi TFWidth:0 TFHeight:0 VView:view Editable:NO];
	[labe setTextColor:[CPColor whiteColor]];
	
	persPopu = [self addPopupButton:@"Empleados" TFPosX:horiPos2 TFPosY:vertPosi TFWidth:200 TFHeight:24  VView:view pullsDown:NO];
	[persPopu setTarget:self];
	[persPopu setAction:@selector(persSelectionPopUp:)];
		
	vertPosi += 40;
	labe= [self addTextField:@"Fecha Inicio:" TFPosX:horiPos1 TFPosY:vertPosi TFWidth:0 TFHeight:0 VView:view Editable:NO];
	[labe setTextColor:[CPColor whiteColor]];
	dateStarText = [self addTextField:[[[CPDate date] description] substringToIndex:10] TFPosX:horiPos2-4 TFPosY:vertPosi-4 TFWidth:85 TFHeight:30 VView:view Editable:YES];
	[self addButton:@"DP" TFPosX:horiPos2 + 90 TFPosY:vertPosi-2 TFWidth:50 TFHeight:24  VView:view sizeToFit:NO Action:@selector(datePickerStartPanel:)];
	vertPosi += 40;
	labe= [self addTextField:@"Fecha Final:" TFPosX:horiPos1 TFPosY:vertPosi TFWidth:0 TFHeight:0 VView:view Editable:NO];
	[labe setTextColor:[CPColor whiteColor]];
	dateEndText = [self addTextField:[[[CPDate date] description] substringToIndex:10] TFPosX:horiPos2-4 TFPosY:vertPosi-4 TFWidth:85 TFHeight:30 VView:view Editable:YES];
	[self addButton:@"DP" TFPosX:horiPos2 + 90 TFPosY:vertPosi-2 TFWidth:50 TFHeight:24  VView:view sizeToFit:NO Action:@selector(datePickerEndPanel:)];

	//Comment
	vertPosi += 40;
	labe = [self addTextField:@"Comentario\n email:" TFPosX:horiPos1 TFPosY:vertPosi TFWidth:0 TFHeight:15 VView:view Editable:NO];
	[labe setTextColor:[CPColor whiteColor]];
	emaiComm = [self addTextField:@" " TFPosX:horiPos2 TFPosY:vertPosi-4 TFWidth:200 TFHeight:2*vertLineHeig VView:view Editable:YES];
    [emaiComm setLineBreakMode:CPLineBreakByWordWrapping];
    
	vertPosi += 70;
	[self addButton:@"Generar" TFPosX:horiPos2  TFPosY:vertPosi-2 TFWidth:80 TFHeight:24  VView:view sizeToFit:NO Action:@selector(generateReport:)];
	horiPos2 += 90;
	[self addButton:@"Enviar" TFPosX:horiPos2  TFPosY:vertPosi-2 TFWidth:80 TFHeight:24  VView:view sizeToFit:NO Action:@selector(sendReportByEmail:)];
}



- (void) roleSelectionPopUp:(id)sender {
	
	var inde = [rolePopu indexOfSelectedItem];
	var quer = "";

	
	if (inde == 0 ){
	
		[persPopu removeAllItems];
		[persArra removeAllObjects];
		[persPopu addItemWithTitle:"Empleados"];	
		namePers = lastNamePers = emaiPers  = NULL;
		
	} else {
			
		quer = "SELECT t2.NameFirst,t2.nameLast,t1.email,t2.id ";
		quer += "FROM " + dataBase + ".organizationPersons t1,  " + dataBase + ".persons t2 ";
	    quer += "WHERE active = 1 AND t1.personId = t2.id AND t1.organizationId='" + orgaId+ "'";
		[persPopu removeAllItems];
		//alert("query " + quer);
		
			if ( inde == 1){
				quer += " AND ( t2.role LIKE 'S%' OR t2.role LIKE 's%' ) ORDER BY t2.NameFirst,t2.nameLast";
			}else if ( inde == 2){				
				quer += " AND ( t2.role LIKE 'E%' OR t2.role LIKE 'e%' ) ORDER BY t2.NameFirst,t2.nameLast";
			}else if (inde == 3){
				quer += " AND ( t2.role LIKE 'A%' OR t2.role LIKE 'a%' ) ORDER BY t2.NameFirst,t2.nameLast";
			}
			
		persArra= [self query:quer];
		console.log ("quer " +  quer);
		var coun = [persArra count];
			
		for ( var i=0; i<coun; i++ ) {
			var valuList = [persArra[i] valueList];
			namePers     = valuList[0];
			lastNamePers = valuList[1];
			emaiPers     = valuList[2];	
			idenPers     = valuList[3];	
			[persPopu addItemWithTitle:namePers + " " + lastNamePers];
	  }
	}
	[persPopu selectItemAtIndex:(coun-1)];
		
}

- (void) sendReportByEmail:(id)sender {	
	
	urlRepo = NULL;

	var arra =  [CPArray array];
		if (emaiPers){
			[arra addObject:emaiPers];
		}

		if([arra count] > 0){

			if (!urlRepo){
	   			[self generateReport:sender];
			}
			if (urlRepo) {

				urlRepo = [urlRepo stringByReplacingOccurrencesOfString:@"192.168.2.252" withString:@"167.250.194.153"];
				var comm = [emaiComm stringValue]; 
				[jasp emailReportToList:arra :urlRepo :comm];

			}
		}
		
}








@end
