<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Report_generate extends CI_Controller
{
  public $page_data = array();

  function __construct()
  {
    parent::__construct();
    $this->initial();
  }

  public function initial()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");
    
    $this->load->model('User_model', 'User');
    $this->load->model('Work_model', 'Work');
    $this->load->model('Client_model', 'Client');
    $this->load->model('Supplier_model', 'Supplier');
    $this->load->model('Purchase_bill_model', 'Purchases');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->model('Material_model', 'Material');
    $this->load->model('Subcontracts_model', 'Subcontracts');
    $this->load->model('Labour_model', 'Labour');
    $this->load->model('Labour_wages_model', 'Labour_wages');
    $this->load->model('Account_model', 'Account');
    $this->load->model('Settings_model', 'Settings');
    $this->load->model('Bills_model', 'Bills');
    $this->load->library('Accounts_management','accounts_management');
    $this->load->model('Salary_slips_model', 'Salary_slips');
    $this->load->model('Vehicle_model', 'Vehicle');
    $this->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $this->load->model('Loan_model', 'Loan');

    //        File Directory
    $this->page_data['directory'] = "reports";

  }

  public function index()
  {

  }

  public function send_email($report_type,$date='')
  {
    $email_body='';
    if($report_type=='labour_attendance'){
      if($date!=''){
        $data["lw.work_date"]= $date;
      }else{
        $data["lw.work_date"]= date('Y-m-d');
      }
      $email_body.='<h3 style="padding: 20px; border: 2px dashed black;text-align:center"><strong>Labour Attendance Report on '.date('d-m-Y',strtotime($data["lw.work_date"])).'</strong></h3><br/></br/>';
      $previous_labour_group=$sl_no=0;
      $result	= $this->Labour_wages->select_labour_wages("lw.*,wt.type_name,l.labour_group,l.labour_name,wt.type_name,u.user_alias,wc.work_name",$data,"","","","","");
      if($result->num_rows()>0){
        $email_body.='<table>
        <tr>
        <th>SL No</th>
        <th>Labour</th>
        <th>Work Type</th>
        <th>Project</th>
        </tr><tbody>';
        foreach($result->result() as $row):
          $sl_no++;

          if(($row->labour_group!=0 && $row->labour_group!=$previous_labour_group) || ($row->labour_group==0)){
            $tr_style='style="border-top: 1px #c3c3c3 solid;border-bottom: 1px #c3c3c3 solid;"';
          }else{
            $tr_style='';
          }
          $email_body.='<tr '.$tr_style.'>';
          $previous_labour_group = $row->labour_group;

          $email_body.='<td>'.$sl_no.'</td>';
          $email_body.='<td>'.$row->labour_name.'</td>';
          $email_body.='<td>'.$row->type_name.'</td>';
          $email_body.='<td>'.$row->work_name.'</td>';

          $email_body.='</tr>';
        endforeach;
        $email_body.='<tr>';
        $email_body.='<td><strong>Total</strong></td>';
        $email_body.='<td><strong>'.$result->num_rows().'</strong></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='</tr>';
        $email_body.='</tbody></table>';

      }else{
        $email_body.='<h4 style="text-align:center"><strong>Sorry. No Labour Attendance Registered Till Now !!</strong></h4>';
      }
      $data['subject'] = 'Labour Attendance Report on '.date('d-m-Y',strtotime($data["lw.work_date"]));
      $data['message'] = $email_body;
      $this->mail($data);
    }elseif($report_type=='labour_wage_slip_and_payments'){
      if($date!=''){
        $data["lw.work_date"]= $date;
      }else{
        $data["lw.work_date"]= date('Y-m-d');
      }
      $email_body.='<h3 style="padding: 20px; border: 2px dashed black;text-align:center"><strong>Labour Wage Slip Report on '.date('d-m-Y',strtotime($data["lw.work_date"])).'</strong></h3><br/></br/>';
      $previous_labour_group=$sl_no=0;
      $total_work_days=$total_wage_per_day=$total_extra_wage=$total_overtime_wage=$total_wage_amount=$total_no_of_labours=$grand_total_balance=0;
      $result	= $this->Labour_wages->select_labour_wages("lw.*,l.group_position,l.opening_balance,wt.type_name,l.labour_group,l.labour_type,l.labour_name,wt.type_name,u.user_alias,wc.work_name",$data,"","","","","");
      if($result->num_rows()>0){
        $email_body.='<table>
        <tr>
        <th>SL No</th>
        <th>Labour</th>
        <th>Work Type</th>
        <th>Project</th>
        <th>No .of Days</th>
        <th>Wage/Day</th>
        <th>Overtime</th>
        <th>Extra</th>
        <th>Total Payable</th>
        <th>Remarks</th>
        </tr><tbody>';
        foreach($result->result() as $row):
          $wage_data = $payment_data =  array();
          if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
            $wage_data['l.labour_group'] = $row->labour_group;
          }else{
            $wage_data['lw.labour_id'] = $row->labour_id;
          }
          $total_wage = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total",$wage_data)->row()->total;
          $total_wage_to_pay = $total_wage + $row->opening_balance;

          if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
            $payment_data['l.labour_group'] = $row->labour_group;
          }else{
            $payment_data['l.labour_id'] = $row->labour_id;
          }
          $payment_data['ats.tr_type'] = 'labour_transaction';
          $payment_data['ats.reference_type'] = 'labour';
          $total_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$payment_data,'labour')->row()->total_amount;
          $total_balance = $total_wage_to_pay-$total_paid;

          $sl_no++;
          $email_body.='<tr>';
          if(($row->labour_group!=0 && $row->labour_group!=$previous_labour_group) || ($row->labour_group==0)){
            $tr_style='style="border-top: 1px #c3c3c3 solid;border-bottom: 1px #c3c3c3 solid;"';
          }else{
            $tr_style='';
          }
          $email_body.='<tr '.$tr_style.'>';
          $previous_labour_group = $row->labour_group;

          $email_body.='<td>'.$sl_no.'</td>';
          $email_body.='<td>'.$row->labour_name.'</td>';
          $email_body.='<td>'.$row->type_name.'</td>';
          $email_body.='<td>'.$row->work_name.'</td>';
          $email_body.='<td>'.$row->work_days.'</td>';
          $email_body.='<td>'.$row->wage_per_day.'</td>';
          $email_body.='<td>'.$row->overtime_wage.'</td>';
          $email_body.='<td>'.$row->extra_wage.'</td>';
          $email_body.='<td>'.$row->total_wage.'</td>';
          $email_body.='<td>'.$row->remarks.'</td>';
          // if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
          //   $email_body.='<td>'.$total_balance-($row->total_wage).'</td>';
          //   $email_body.='<td>'.$total_balance.'</td>';
          // }elseif($this->config->item('Labour_group_member_payment')==1){
          //   $email_body.='<td>'.$total_balance-($row->total_wage).'</td>';
          //   $email_body.='<td>'.$total_balance.'</td>';
          // }elseif($row->labour_type==0){
          //   $email_body.='<td>'.$total_balance-($row->total_wage).'</td>';
          //   $email_body.='<td>'.$total_balance.'</td>';
          // }

          $email_body.='</tr>';

          $total_work_days+=$row->work_days;
          $total_wage_per_day+=$row->wage_per_day;
          $total_overtime_wage+=$row->overtime_wage;
          $total_extra_wage+=$row->extra_wage;
          $total_wage_amount+=$row->total_wage;
          $total_no_of_labours+=$row->no_of_labours;
          // if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
          //   $grand_total_balance+=$total_balance;
          // }elseif($this->config->item('Labour_group_member_payment')==1){
          //   $grand_total_balance+=$total_balance;
          // }elseif($row->labour_type==0){
          //   $grand_total_balance+=$total_balance;
          // }

        endforeach;
        $email_body.='<tr>';
        $email_body.='<td><strong>Total</strong></td>';
        $email_body.='<td><strong>'.$result->num_rows().'</strong></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='<td><strong>'.$total_work_days.'</strong></td>';
        $email_body.='<td></td>';
        $email_body.='<td><strong>'.$total_overtime_wage.'</strong></td>';
        $email_body.='<td><strong>'.$total_extra_wage.'</strong></td>';
        $email_body.='<td><strong>'.$total_wage_amount.'</strong></td>';
        $email_body.='<td></td>';
        // $email_body.='<td></td>';
        // $email_body.='<td><strong>'.$grand_total_balance.'</strong></td>';
        $email_body.='</tr>';
        $email_body.='</tbody></table>';

      }else{
        $email_body.='<h4 style="text-align:center"><strong >Sorry. No Labour Wage Slip Registered Till Now!!</strong></h4>';
      }

      //    Labour Payments
      $sl_no2=$total_paid_amount=0;
      $reference_type = 'labour';
      if($date!=''){
        $td_data['ats.tr_date']= $date;
      }else{
        $td_data['ats.tr_date'] = date('Y-m-d');
      }
      $td_data['ats.reference_type'] = $reference_type;
      $td_data['ats.tr_type'] = $reference_type."_transaction";
      $email_body.='<br/></br/><h3 style="padding: 20px; border: 2px dashed black;text-align:center"><strong>Labour Payment Report on '.date('d-m-Y',strtotime($td_data['ats.tr_date'])).'</strong></h3><br/></br/>';
      $transaction_result	= $this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user,l.labour_name,wt.type_name,l.labour_group,l.labour_id",$td_data,$reference_type);
      if($transaction_result->num_rows()>0){
        $email_body.='<table>
        <tr>
        <th>SL No</th>
        <th>Labour</th>
        <th>Work Type</th>
        <th>Paid Amount</th>
        <th>Debit Acc</th>
        <th>Payment Mode</th>
        <th>TR Ref No</th>
        <th>TR Ref Date</th>
        <th>Remarks</th>
        </tr><tbody>';
        foreach($transaction_result->result() as $row2):
          $sl_no2++;
          $email_body.='<tr>';
          // if(($row->labour_group!=0 && $row->labour_group!=$previous_labour_group) || ($row->labour_group==0)){
          //   // Add a Blank Row
          //   $email_body.='<tr style="border-top: 1px #c3c3c3 solid;border-bottom: 1px #c3c3c3 solid;">';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='<td></td>';
          //   $email_body.='</tr>';
          // }
          $email_body.='<tr>';
          $previous_labour_group = $row->labour_group;

          $email_body.='<td>'.$sl_no2.'</td>';
          $email_body.='<td>'.$row2->labour_name.'</td>';
          $email_body.='<td>'.$row2->type_name.'</td>';
          $email_body.='<td>'.$row2->tr_amount.'</td>';
          $email_body.='<td>'.$row2->debit_account.'</td>';
          $email_body.='<td>'.$row2->tr_mode.'</td>';
          $email_body.='<td>'.$row2->tr_reference_no.'</td>';
          if($row2->tr_reference_date!='0000-00-00'){
            $email_body.='<td>'.date('d-m-Y',strtotime($row->tr_reference_date)).'</td>';
          }else{
            $email_body.='<td></td>';
          }
          $email_body.='<td>'.$row2->tr_remarks.'</td>';
          $email_body.='</tr>';

          $total_paid_amount+=$row2->tr_amount;

        endforeach;
        $email_body.='<tr>';
        $email_body.='<td></td>';
        $email_body.='<td><strong>Total</strong></td>';
        $email_body.='<td></td>';
        $email_body.='<td><strong>'.$total_paid_amount.'</strong></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='<td></td>';
        $email_body.='</tr>';
        $email_body.='</tbody></table>';

      }else{
        $email_body.='<h4 style="text-align:center"><strong >Sorry. No Labour Payment Registered Till Now!!</strong></h4>';
      }

      $data['subject'] = 'Labour Wage Slip & Payment Report on '.date('d-m-Y',strtotime($data["lw.work_date"]));
      $data['message'] = $email_body;
      $this->mail($data);
    }elseif($report_type=='labour_summary'){
      $email_body.='<h3 style="padding: 20px; border: 2px dashed black;text-align:center"><strong>Labour Wage And Payment Summary Upto '.date('d-m-Y').'</strong></h3><br/></br/>';
      $json_data=array();
      $j=$slno=0;
      $total_worked_days=$total_basic_wage=$total_overtime_wage=$total_extra_wage=$total_op_balance=$total_wage_amount=$total_paid_amount=$total_balance_amount=0;
      $data=array();
      $data['l.delete_status'] = 0;
      if($this->config->item('Labour_group_member_payment')==0){
        $this->db->where('(l.labour_type=0 or (l.labour_type=1 and group_position=1))');
      }
      $result	= $this->Labour->select_labours("l.*,wt.type_name,u.user_alias",$data);
      if($result->num_rows()>0){
        $email_body.='<table>
        <tr>
        <th>Sl No</th>
        <th>Labour Name</th>
        <th>Contact</th>
        <th>Work Type</th>
        <th>Op. Balance</th>
        <th>Total Worked Days</th>
        <th>Total Basic Wage</th>
        <th>Total Overtime Wage</th>
        <th>Total Extra Wage</th>
        <th>Total Wage to Pay</th>
        <th>Total Paid</th>
        <th>Total Balance</th>
        </tr><tbody>';
        foreach($result->result() as $row):
          $labour_name = $row->labour_name;
          if($row->labour_type==0){
            $labour_name .= "<br/>Individual";
          }else{
            if($row->group_position==1){
              $labour_name .= "<br/><span style='margin-top: 5px' class='m-badge m-badge--success m-badge--wide'> Team Leader</span>";
            }else{
              $labour_name .= "<br/><span style='margin-top: 5px' class='m-badge m-badge--info m-badge--wide'> Team Member</span>";
            }
          }
          $wage_data = $payment_data =  array();
          if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
            $wage_data['l.labour_group'] = $row->labour_group;
          }else{
            $wage_data['lw.labour_id'] = $row->labour_id;
          }

          $worked_days = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.work_days),0) as total",$wage_data)->row()->total;
          $basic_wage = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.work_days*lw.wage_per_day),0) as total",$wage_data)->row()->total;
          $overtime_wage = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.overtime_wage),0) as total",$wage_data)->row()->total;
          $extra_wage = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.extra_wage),0) as total",$wage_data)->row()->total;
          $total_wage = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total",$wage_data)->row()->total;
          $total_wage_to_pay = $total_wage + $row->opening_balance;

          if($this->config->item('Labour_group_member_payment')==0 && $row->labour_type==1 && $row->group_position==1){
            $payment_data['l.labour_group'] = $row->labour_group;
          }else{
            $payment_data['l.labour_id'] = $row->labour_id;
          }
          $payment_data['ats.tr_type'] = 'labour_transaction';
          $payment_data['ats.reference_type'] = 'labour';
          $total_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$payment_data,'labour')->row()->total_amount;
          $total_balance = sprintf("%+d",$total_wage_to_pay-$total_paid);

          $slno++;
          $email_body.='<tr>';
          $email_body.='<td>'.$slno.'</td>';
          $email_body.='<td>'.$labour_name.'</td>';
          $email_body.='<td>'.$row->labour_contact.'</td>';
          $email_body.='<td>'.$row->type_name.'</td>';
          $email_body.='<td>'.$row->opening_balance.'</td>';
          $email_body.='<td>'.$worked_days.'</td>';
          $email_body.='<td>'.$basic_wage.'</td>';
          $email_body.='<td>'.$overtime_wage.'</td>';
          $email_body.='<td>'.$extra_wage.'</td>';
          $email_body.='<td>'.$total_wage_to_pay.'</td>';
          $email_body.='<td>'.$total_paid.'</td>';
          $email_body.='<td>'.$total_balance.'</td>';
          $email_body.='</tr>';

          $total_op_balance+=$row->opening_balance;
          $total_worked_days+=$worked_days;
          $total_basic_wage+=$basic_wage;
          $total_overtime_wage+=$overtime_wage;
          $total_extra_wage+=$extra_wage;
          $total_wage_amount+=$total_wage_to_pay;
          $total_paid_amount+=$total_paid;
          $total_balance_amount+=$total_balance;

          $j++;
        endforeach;

        if($result->num_rows()>0){
          $email_body.='<tr>';
          $email_body.='<td></td>';
          $email_body.='<td></td>';
          $email_body.='<td></td>';
          $email_body.="<td><strong>Total</strong>";
          $email_body.="<td><strong>".$total_op_balance."</strong></td>";
          $email_body.="<td><strong>".$total_worked_days."</strong></td>";
          $email_body.="<td><strong>".$total_basic_wage."</strong></td>";
          $email_body.="<td><strong>".$total_overtime_wage."</strong></td>";
          $email_body.="<td><strong>".$total_extra_wage."</strong></td>";
          $email_body.="<td><strong>".$total_wage_amount."</strong></td>";
          $email_body.="<td><strong>".$total_paid_amount."</strong></td>";
          $email_body.="<td><strong>".sprintf("%+d",$total_balance_amount)."</strong></td>";
          $email_body.='</tr>';
        }
      }else{
        $email_body.='<h4 style="text-align:center"><strong >Sorry. No Labours Found!!</strong></h4>';
      }
      $email_data['subject'] = 'Labour Wage & Payment Summary Upto '.date('d-m-Y');
      $email_data['message'] = $email_body;
      $this->mail($email_data);
    }elseif($report_type=='tools_rent_transfer'){

    }
  }
  public function mail($data){
    $style='<style>
    table {
      border-collapse: collapse;
      width: 100%;
    }
    table {
      border: 1px solid black;
    }
    th {
      text-align: left;
    }
    td {
      vertical-align: bottom;
    }
    th, td {
      padding: 15px;
      text-align: left;
    }
    tr:hover {background-color: #f5f5f5;}
    tr:nth-child(even) {background-color: #f2f2f2;}
    th {
      background-color: #a04caf;
      color: white;
    }
    </style>';
    $settings = $this->Settings->select_settings("",array("settings_category"=>"SMTP"))->result();
    foreach ($settings as $key => $value) {
      ${$value->settings_name} = $value->value;
    }

    $company_settings = $this->Settings->select_settings("",array("settings_category"=>"Company Details"))->result();
    foreach ($company_settings as $key => $value) {
      ${$value->settings_name} = $value->value;
    }

    $subject = $data['subject'];
    $to=$this->Settings->select_settings("",array("settings_name"=>"email_for_alerts"))->row()->value;
    $message = "<html><head>".$style."</head><body>".$data['message']."</body></html>";
    $this->load->library('My_PHPMailer');
    $mail = new PHPMailer();
    $mail->IsSMTP(); // we are going to use SMTP
    $mail->SMTPAuth   = true; // enabled SMTP authentication
    $mail->SMTPSecure = "ssl";  // prefix for secure protocol to connect to the server
    $mail->Host       = $smtp_host;      // setting GMail as our SMTP server
    $mail->Port       = $smtp_port;                   // SMTP port to connect to GMail
    $mail->Username   = $smtp_username;  // user email address
    $mail->Password   = $smtp_password;            // password in GMail
    $mail->SetFrom($smtp_username, $company_name);  //Who is sending the email
    $mail->Subject    = $subject;
    $mail->IsHTML(true);
    $mail->Body      = $message;
    $mail->AltBody    = "Plain text message";
    $destino = $to; // Who is addressed the email to
    $mail->AddAddress($destino);
    $mail=$mail->Send();  //Sending Mail
    return $mail;

    // echo $message;
  }
}
?>
