import * as XLSX from 'xlsx'; import { TrUtils } from '../../../../utils/tr-utils'; export class VenBalanceXlsxFileService { static wb: any = {}; static Row: any = 2; static MergeArray: any[] = []; static range = { s: { c: 0, r: 0 }, e: { c: 0, r: 0 } }; static ws: any = {}; static GetInvoiceWiseExcelData(MainData: any, EntitySettings: any) { this.wb={}; this.ws = {}; this.Row=0; this.range = { s: { c: 0, r: 0 }, e: { c: 0, r: 0 } }; this.MergeArray=[]; let ws_name = 'OUT STANDING REPORT'; this.wb.Sheets = {}; this.wb.Props = {}; this.wb.SSF = {}; this.wb.SheetNames = []; // MainData = this.GetTotals(MainData, WOTypes); // this.setHeadingInCell(); this.setInvoiceDetailsInCell(MainData, EntitySettings.DecimalsNumber); this.ws['!ref'] = XLSX.utils.encode_range(this.range); this.ws['!merges'] = this.MergeArray; this.wb.SheetNames.push(ws_name); this.wb.Sheets[ws_name] = this.ws; return this.wb; } // static GetTotals(MainData: any, WOTypes: any) { // MainData.forEach((BillInfo: any) => { // if (BillInfo.VIN == null) { // BillInfo.VIN = ''; // } // if (BillInfo.Name == null) { // BillInfo.Name = ''; // } // if (BillInfo.TeNa == null) { // BillInfo.TeNa = ''; // } // if (BillInfo.Type == null) { // BillInfo.TypeName = ''; // } else { // BillInfo.TypeName = this.GetWOTypeName(BillInfo.Type, WOTypes); // } // BillInfo.GSTAmount = // BillInfo.ItemsCGST + BillInfo.ItemsSGST + BillInfo.ItemsIGST; // BillInfo.TotalAmount = BillInfo.GSTAmount + BillInfo.ItemsNetAmt; // BillInfo.OpsGSTAmount = // BillInfo.OpsCGST + BillInfo.OpsSGST + BillInfo.OpsIGST; // BillInfo.TotalOpsAmount = // BillInfo.OpsGSTAmount + BillInfo.OpsNetAmt; // }); // return MainData; // } static GetWOTypeName(Type: any, WOTypes: any) { let Index: any = WOTypes.findIndex((wotype: any) => { return Type === wotype._id; }); if (Index !== -1) { return WOTypes[Index].Name; } else { return ''; } } static setHeadingInCell() { let MainHeadings: any[] = [ { text: 'SNo', ColRange: 1, bold: true, ChildHeadings: [], }, { text: 'Bill Number', ColRange: 1, bold: true, ChildHeadings: [ ], }, { text: 'Date', ColRange: 1, bold: true, ChildHeadings: [], }, { text: 'Total', ColRange: 1, bold: true, ChildHeadings: [], }, { text: 'Due', ColRange: 1, bold: true, ChildHeadings: [ ], }, ]; let MainColStart: any = 0; MainHeadings.forEach((MainHeader: any) => { this.SetDataInCell(MainHeader.text, MainColStart, this.Row); this.MergeArray.push({ s: { r: this.Row, c: MainColStart }, e: { r: this.Row, c: MainColStart + MainHeader.ColRange - 1 }, }); MainColStart += MainHeader.ColRange; }); this.Row += 1; } static setHeading1InCell(Name: any, DecimalsNumber: number) { let InvoiceData: any[] = [ { text: Name, ColRange: 5, IsString: true }, ]; let ColStart: any = 0; InvoiceData.forEach((InvData: any) => { InvData.text = this.ConvertToString(InvData.text, InvData.IsString, DecimalsNumber); this.SetDataInCell(InvData.text, ColStart, this.Row); this.MergeArray.push({ s: { r: this.Row, c: ColStart }, e: { r: this.Row, c: ColStart + InvData.ColRange - 1 }, }); ColStart += InvData.ColRange; }); this.Row += 1; } static setInvoiceFinalRowInCell(Due: any, DecimalsNumber: number) { let InvoiceData: any[] = [ { text: 'Total Due', ColRange: 1, IsString: true }, { text: Due, ColRange: 1, IsString: true }, ]; let ColStart: any = 3; InvoiceData.forEach((InvData: any) => { InvData.text = this.ConvertToString(InvData.text, InvData.IsString, DecimalsNumber); this.SetDataInCell(InvData.text, ColStart, this.Row); this.MergeArray.push({ s: { r: this.Row, c: ColStart }, e: { r: this.Row, c: ColStart + InvData.ColRange - 1 }, }); ColStart += InvData.ColRange; }); this.Row += 1; } static setInvoiceDetailsInCell(MainData: any, DecimalsNumber: number) { MainData.forEach((Record: any) => { this.setHeading1InCell(Record.Name, DecimalsNumber); // this.MergeArray.push({ // s: { r: this.Row, c: ColStart }, // e: { r: this.Row, c: ColStart + InvData.ColRange - 1 }, // }); // ColStart += InvData.ColRange; this.setHeadingInCell(); Record.Bills.forEach((Invoice: any, index: any) => { this.SetInvoiceDataInExcel(Invoice, index, DecimalsNumber); if (Record.Bills.length === (index + 1)) { this.setInvoiceFinalRowInCell(Record.Balance, DecimalsNumber); } }); this.Row += 1; }); } static SetInvoiceDataInExcel(BillInfo: any, index: any, DecimalsNumber: number) { // BillInfo.CrDate = this.ConvertDateToReadableFormat(BillInfo.CrDate); BillInfo.CrDate = this.ConvertDateToReadableFormat(BillInfo.CrDate); let InvoiceData: any = [ { text: index + 1, ColRange: 1, IsString: true }, { text: BillInfo.BNo, ColRange: 1, IsString: true }, { text: BillInfo.CrDate, ColRange: 1, IsString: true }, { text: BillInfo.Total, ColRange: 1, IsString: false }, { text: BillInfo.Due, ColRange: 1, IsString: false }, ]; let ColStart: any = 0; InvoiceData.forEach((InvData: any) => { InvData.text = this.ConvertToString(InvData.text, InvData.IsString, DecimalsNumber); this.SetDataInCell(InvData.text, ColStart, this.Row); this.MergeArray.push({ s: { r: this.Row, c: ColStart }, e: { r: this.Row, c: ColStart + InvData.ColRange - 1 }, }); ColStart += InvData.ColRange; }); this.Row += 1; } static ConvertToString(Text: any, IsString: any, DecimalsNumber: number) { if (IsString) { if (Text == null) { Text = ''; } } else { if (Text == null) { Text = 0; } } if (typeof Text === 'number') { return Number(TrUtils.FixedTo(Text, DecimalsNumber)); } else { return Text; } } static SetDataInCell(Data: any, ColRange: number, RowNum: any) { var cell = { v: Data }; var cell_ref = XLSX.utils.encode_cell({ c: ColRange, r: RowNum }); if (this.range.e.c < ColRange) { this.range.e.c = ColRange; } if (this.range.e.r < RowNum) { this.range.e.r = RowNum; } cell = this.getcelltype(cell); this.ws[cell_ref] = cell; } static getcelltype(cell: any) { if (typeof cell.v === 'number') cell.t = 'n'; else if (typeof cell.v === 'boolean') cell.t = 'b'; else cell.t = 's'; return cell; } static ConvertDateToReadableFormat(DateObject: any) { if (!TrUtils.IsEmpty(DateObject)) { let date: any = new Date(DateObject); let Month: any; let Day: any; let MonthName = new Array(); MonthName[0] = 'Jan'; MonthName[1] = 'Feb'; MonthName[2] = 'Mar'; MonthName[3] = 'Apr'; MonthName[4] = 'May'; MonthName[5] = 'Jun'; MonthName[6] = 'Jul'; MonthName[7] = 'Aug'; MonthName[8] = 'Sept'; MonthName[9] = 'Oct'; MonthName[10] = 'Nov'; MonthName[11] = 'Dec'; Month = MonthName[date.getMonth()]; if (date.getDate() < 10) { Day = '0' + date.getDate(); } else { Day = date.getDate(); } date = Day + '-' + Month + '-' + date.getFullYear(); return date; } else { return null; } } }