import { Proxy as FacilityProxy } from '@labshare/facility';
import { flatMapDeep, sortBy } from 'lodash';
import { BillingConstants } from '../constants/billing.constants';
import { ExcelItem, ExcelParams, RequestBody } from './models/billing';
const excelJs = require('exceljs');
import { getLabShareConfiguration } from './common';
const conf = getLabShareConfiguration();
export class CreateExcel {

  public buildBillingExcel(excelParams: ExcelParams) {
    const { excelItems, generateBilled, requestBody, loginName } = excelParams;
    let workbook = new excelJs.Workbook();
    let requestHyperlink;
    let projectHyperlink;
    workbook.views = [
      {
        x: 0, y: 0, width: 10000, height: 20000,
        firstSheet: 0, activeTab: 1, visibility: 'visible'
      }
    ];
    const headerFill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'b7b7b7b7' }
    };
    const date = requestBody && requestBody.endDate ? new Date(requestBody.endDate) : new Date();
    const month = date.getUTCMonth() + 1;
    const year = date.getUTCFullYear();
    const billedMonth = month < 10 ? `0${month}` : month;
    let sectionNumber = 0;
    for (const excelItem of excelItems) {
      const sortedItems = sortBy(excelItem, ['CAN']);
      const fileName = `${sortedItems[0].Section}-${year}.${billedMonth}.xlsx`;
      const worksheet = workbook.addWorksheet(sortedItems[0].listName);
      worksheet.columns = [
        { header: 'CAN', key: 'A' },
        { header: 'Lab', key: 'B' }
      ];
      worksheet.getCell('A1').fill = headerFill;
      worksheet.getCell('B1').fill = headerFill;
      worksheet.getCell('A1').font = { bold: true };
      worksheet.getCell('B1').font = { bold: true };
      worksheet.getCell('A1').alignment = {
        vertical: 'middle',
        horizontal: 'center'
      };
      worksheet.getCell('B1').alignment = {
        vertical: 'middle',
        horizontal: 'center'
      };
      worksheet.addRow([sortedItems[0].CAN, sortedItems[0].Lab]);
      worksheet.addRow([
        'Request Date',
        'Request Type',
        'Request',
        'Project',
        'Client',
        'LabPIName',
        'RTB_Cost',
        'SS_Cost'
      ]);
      let row = worksheet.lastRow;
      row.eachCell((cell, colNumber) => {
        cell.fill = headerFill;
        cell.font = { bold: true };
        cell.alignment = {
          vertical: 'middle',
          horizontal: 'center'
        };
        if (cell._value.value === 'Request' || cell._value.value === 'Project' || cell._value.value === 'Client' || cell._value.value === 'LabPIName') {
          worksheet.getColumn(cell._column._number).width = 35;
        } else {
          worksheet.getColumn(cell._column._number).width = 13;
        }

      });
      worksheet.getColumn(7).numFmt = '$#,##0.00;[Red]-$#,##0.00';
      worksheet.getColumn(8).numFmt = '$#,##0.00;[Red]-$#,##0.00';
      let firstRow = worksheet.lastRow.getCell('B')._row._number + 1;

      for (const item in sortedItems) {
        if (!sortedItems.hasOwnProperty(item)) {
          return;
        }
        const currentListName = sortedItems[Number(item)] && sortedItems[Number(item)].CAN;
        const nextListName = sortedItems[Number(item) + 1] && sortedItems[Number(item) + 1].CAN;
        if (!currentListName && !sortedItems[Number(item)].RequestID && sortedItems.length <= 1) {
          worksheet.addRow(['No requests found.']);
          continue;
        }
        if (generateBilled) {
          projectHyperlink = sortedItems[item].Project ? {
            text: sortedItems[item].Project.split(',')[0],
            hyperlink: sortedItems[item].Project.split(',')[1],
            tooltip: sortedItems[item].Project.split(',')[1]
          } : '';
        } else {
          requestHyperlink = sortedItems[item].request ? {
            text: sortedItems[item].request,
            hyperlink: `https://${conf.billing.facilityID}.ncats.nih.gov/${sortedItems[item].Section}/Lists/${sortedItems[item].Request_x0020_Type}/DispForm.aspx?ID=${sortedItems[item].RequestID}`,
            tooltip: `https://${conf.billing.facilityID}.ncats.nih.gov/${sortedItems[item].Section}/Lists/${sortedItems[item].Request_x0020_Type}/DispForm.aspx?ID=${sortedItems[item].RequestID}`
          } : '';
          projectHyperlink = sortedItems[item].Project ? {
            text: sortedItems[item].Project,
            hyperlink: `https://${conf.billing.facilityID}.ncats.nih.gov/${sortedItems[item].Section}/Lists/Projects/DispForm.aspx?ID=${sortedItems[item].projectID}`,
            tooltip: `https://${conf.billing.facilityID}.ncats.nih.gov/${sortedItems[item].Section}/Lists/Projects/DispForm.aspx?ID=${sortedItems[item].projectID}`
          } : '';
        }

        worksheet.addRow([
          sortedItems[item].Request_x0020_Date,
          sortedItems[item].Request_x0020_Type,
          requestHyperlink,
          projectHyperlink,
          sortedItems[item].Client,
          sortedItems[item].LabPIName,
          Number(sortedItems[item].RTB_Cost) || 0,
          Number(sortedItems[item].SS_Cost) || 0
        ]);

        if (currentListName !== nextListName) {
          const lastRow = worksheet.lastRow.getCell('B')._row._number;
          const rowValues = [];
          rowValues[7] = firstRow === lastRow ? worksheet.getCell(`G${firstRow}`).value : { formula: `SUM(G${firstRow}:G${lastRow})`, result: 0 };
          rowValues[8] = firstRow === lastRow ? worksheet.getCell(`H${firstRow}`).value : { formula: `SUM(H${firstRow}:H${lastRow})`, result: 0 };
          worksheet.addRow(rowValues);
          const nextRow = worksheet.lastRow;
          nextRow.eachCell((cell, colNumber) => {
            cell.fill = headerFill;
            cell.font = { bold: true };
            cell.alignment = {
              vertical: 'middle',
              horizontal: 'center'
            };
            cell.border = {
              top: { style: 'thin' },
              bottom: { style: 'double' }
            };
          });
        }
        if ((currentListName !== nextListName) && nextListName) {
          worksheet.addRow();
          worksheet.addRow(['CAN', 'Lab']);
          row = worksheet.lastRow;
          row.eachCell((cell, colNumber) => {
            cell.fill = headerFill;
            cell.font = { bold: true };
            cell.alignment = {
              vertical: 'middle',
              horizontal: 'center'
            };
          });
          worksheet.addRow([sortedItems[Number(item) + 1].CAN, sortedItems[Number(item) + 1].Lab]);
          worksheet.addRow([
            'Request Date',
            'Request Type',
            'Request',
            'Project',
            'Client',
            'LabPIName',
            'RTB_Cost',
            'SS_Cost'
          ]);
          row = worksheet.lastRow;
          row.eachCell((cell, colNumber) => {
            cell.fill = headerFill;
            cell.font = { bold: true };
            cell.alignment = {
              vertical: 'middle',
              horizontal: 'center'
            };
          });
          firstRow = worksheet.lastRow.getCell('B')._row._number + 1;
        }
      }

      const currentSection = excelItems[sectionNumber] && excelItems[sectionNumber][0] && excelItems[sectionNumber][0].Section;
      const nextSection = excelItems[sectionNumber + 1] && excelItems[sectionNumber + 1][0] && excelItems[sectionNumber + 1][0].Section;
      if ((currentSection !== nextSection)) {
        workbook.xlsx.writeBuffer().then(dataExport => {
          try {
            const listName = generateBilled ? BillingConstants.BILLED_REPORTS : BillingConstants.RTB_REPORTS;
            this.uploadTOSharePointList(dataExport, loginName, fileName, listName);
          } catch (err) {
            console.error(err);
          }
        }).catch(err => {
          console.error(err);
        });
      }
      if ((currentSection !== nextSection) && !!nextSection) {
        workbook = new excelJs.Workbook();
      }
      sectionNumber++;
    }
  }

  public buildGenBillingSheet(excelParams: ExcelParams) {
    const {requestBody, excelItems, loginName} = excelParams;
    const workbook = new excelJs.Workbook();
    workbook.views = [
      {
        x: 0, y: 0, width: 10000, height: 20000,
        firstSheet: 0, activeTab: 1, visibility: 'visible'
      }
    ];
    const date = requestBody && requestBody.endDate ? new Date(requestBody.endDate) : new Date();
    const month = date.getUTCMonth() + 1;
    const year = date.getUTCFullYear();
    const billedMonth = month < 10 ? `0${month}` : month;
    const fileName: any = `RTB-Charges-${year}.${billedMonth}.xlsx`;
    const worksheet = workbook.addWorksheet();
    worksheet.columns = [
      { header: 'CAN', key: 'A' },
      { header: 'Billing Date', key: 'B' },
      { header: 'Category', key: 'C' },
      { header: 'Amount', key: 'D' }
    ];
    const row = worksheet.lastRow;
    row.eachCell((cell, colNumber) => {
      cell.font = { bold: true };
      cell.alignment = {
        vertical: 'middle',
        horizontal: 'center'
      };
      worksheet.getColumn(cell._column._number).width = 20;
    });
    worksheet.getColumn(4).numFmt = '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)';
    for (const excelItem of excelItems) {
      const sortedItems = sortBy(excelItem, ['CAN']);
      let rtbCost = 0;
      for (const item in sortedItems) {
        const pastListName = sortedItems[Number(item) - 1] && sortedItems[Number(item) - 1].CAN;
        const currentListName = sortedItems[Number(item)] && sortedItems[Number(item)].CAN;
        const nextListName = sortedItems[Number(item) + 1] && sortedItems[Number(item) + 1].CAN;
        if (currentListName === nextListName) {
          rtbCost += Number(sortedItems[item].RTB_Cost);
          continue;
        } else if (currentListName === pastListName) {
          rtbCost += Number(sortedItems[item].RTB_Cost);
          if (rtbCost === 0 || sortedItems[item].CAN.toLowerCase() === 'staff') { continue; }
          worksheet.addRow([
            sortedItems[item].CAN,
            sortedItems[item].Request_x0020_Date,
            sortedItems[item].Section.toUpperCase(),
            rtbCost,
          ]);
          rtbCost = 0;
        } else {
          if (Number(sortedItems[item].RTB_Cost) === 0 || sortedItems[item].CAN.toLowerCase() === 'staff') { continue; }
          worksheet.addRow([
            sortedItems[item].CAN,
            sortedItems[item].Request_x0020_Date,
            sortedItems[item].Section.toUpperCase(),
            Number(sortedItems[item].RTB_Cost),
          ]);
        }

        const currentRow = worksheet.lastRow;
        currentRow.eachCell((cell, colNumber) => {
          cell.alignment = {
            vertical: 'middle',
            horizontal: 'center'
          };
        });
      }
    }
    const ssItems = this.getSSItems(excelItems);
    for (const items of ssItems) {
      if (items.SS_Cost === 0 || items.CAN.toLowerCase() === 'staff') { continue; }
      worksheet.addRow([
        items.CAN,
        `${month}/${year}`,
        items.Section,
        items.SS_Cost,
      ]);
      const lastRow = worksheet.lastRow;
      lastRow.eachCell((cell, colNumber) => {
        cell.alignment = {
          vertical: 'middle',
          horizontal: 'center'
        };
      });
    }
    workbook.xlsx.writeBuffer().then(dataExport => {
      try {
        this.uploadTOSharePointList(dataExport, loginName, fileName, BillingConstants.BILLING_SHEETS);
      } catch (err) {
        console.error(err);
      }
    }).catch(err => {
      console.error(err);
    });
  }

  public getSSItems(excelItems = []) {
    const flatItems = flatMapDeep(excelItems);
    const sortedItems: any = sortBy(flatItems, ['CAN']);
    let rtbCost = 0;
    const ssArray = [];
    let ssObject = {};
    for (const item in sortedItems) {
      const pastListName = sortedItems[Number(item) - 1] && sortedItems[Number(item) - 1].CAN;
      const currentListName = sortedItems[Number(item)] && sortedItems[Number(item)].CAN;
      const nextListName = sortedItems[Number(item) + 1] && sortedItems[Number(item) + 1].CAN;
      if (currentListName === nextListName) {
        rtbCost += Number(sortedItems[item].SS_Cost);
        continue;
      } else if (currentListName === pastListName) {
        rtbCost += Number(sortedItems[item].SS_Cost);
        ssObject = {
          CAN: sortedItems[item].CAN,
          Request_x0020_Date: sortedItems[item].Request_x0020_Date,
          Section: 'RTB-SS',
          SS_Cost: rtbCost
        };
        ssArray.push(ssObject);
        rtbCost = 0;
      } else {
        ssObject = {
          CAN: sortedItems[item].CAN,
          Request_x0020_Date: sortedItems[item].Request_x0020_Date,
          Section: 'RTB-SS',
          SS_Cost: Number(sortedItems[item].SS_Cost)
        };
        ssArray.push(ssObject);
      }

    }
    return ssArray;
  }

  public async uploadTOSharePointList(buffer: Buffer, loginName: string, fileName: string, listName: string) {
    const proxyItems = new FacilityProxy.Items({
      facilityId: conf.billing.facilityID + BillingConstants.MYRTB_BILLING,
      loginName,
      listName,
    });
    try {
      proxyItems.upload({
        buffer,
        originalname: fileName
      }, {
        optionalFields: null,
        members: JSON.stringify({ submitter: '' })
      });
    } catch (err) {
      console.error(err);
    }

  }
}