新特性

明细导出+样式优化+删除上传文件

核心代码

    
    
    阿滚专属计算利润
    
	
    
	
	
    
      
    



    
	1. 选择订单EXCEL
	
	
	
	    
		   👩‍💼当前用户
		  
			
	        阿滚
		  
		
		
		
		  🔏输入密码
		  
			
		  
		
	
	
	
	
	
	  📗文件上传
	  
		
	  
	  
		❌删除文件
	  
	  
		📘导出交易汇总
	  
	
	
    
	
	
	
    
        
            
                商品名称
				商品属性
                成交金额总计(元)
                成交数总计(个)
                退款金额总计(元)
                退款数总计(个)
                成交人数预估(人)
                商品客单价(元)
                商品采购公斤价(元)
                商品重量(克)
            
        
        
    

	
	
	2. 利润计算
	
	
	2.1 计算解释
	毛利=（单场成交GMV-退款金额）-原料成本-（成交人数*快递单价）-（成交单数*包材单价）-（成交单数*技术服务费单价）-（成交单数*运费险单价） 
	原料成本=包数*商品克价*规格克重       
	包数=（成交金额-退款金额）/客单价      
	克价=商品公斤价/1000  
	技术服务费=成交金额*0.02  
	运费险=(成交单数-退款单数)*0.14  

	2.2 费用配置
	
	
	  🕵️‍♂️成交人数
	  
		
	  
	
	
	
	  💼快递单价
	  
		
	  
	


	
	 -->
	 -->
     				
    🏅计算利润
    📘导出利润明细
    
    2.3 利润汇总
	总利润=商品利润之和-(成交人数*快递单价)
	
	
	
    
        
            
                商品名称
				商品属性
                包数
                克价
                技术服务费
                运费险
                利润
				利润明细=(成交订单金额总计-退款订单金额总计）-原料成本-（成交单数*包材单价）-（成交单数*技术服务费单价）
            
        
        
    

    

    
	function exportOrderSummary() {
		exportTableToExcel('dataTable', '交易汇总');
	}
	function exportTableProfit() {
		exportTableToExcel('costTable', '利润明细');
	}
	
	// 表格导出到 EXCEL
	function exportTableToExcel(tableId, tableName) {
		 // 获取表格元素
		  var table = document.getElementById(tableId);
		  
		  // 将表格转换为工作簿
		  var ws = XLSX.utils.table_to_sheet(table);
		  var wb = XLSX.utils.book_new();
		  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
		  
		  // 为文件名添加时间戳
		  var timestamp = new Date().toISOString().replace(/[:\.]/g, '-');
		  let filename = tableName+"_"+ timestamp + '.xlsx';
		  
		  // 使用JSZip生成Excel文件
		  XLSX.writeFile(wb, filename);
	}
	

	// 删除文件的函数
	function deleteFile() {
	    var fileInput = document.getElementById('fileInput');
		// 清空文件输入框的值
		fileInput.value = '';
		// 可以在这里添加额外的逻辑，比如更新界面显示等
	}

	    function calculateMD5(input) {
		    if(input && input != "") {
				return CryptoJS.MD5(input).toString();
			}
			return "";
		}
		function isExpire() {
			const today = new Date();
			const targetDate = new Date('2024-10-31');

			if (today > targetDate) {
			    alert('账户已过期，请联系技术管理员!');
				return true;
			} else {
				return false;
			}
		}

	    function checkPassword() {
		    //expire
			if(isExpire()) {
				return false;
			}
			
			let passwordElement = document.getElementById('password');
			let pwdMd5 = calculateMD5(passwordElement.value);
			if ('d5792307ca46844fd13e0ee78c858a11' == pwdMd5) {
				return true;
			}
			alert('账户或者密码错误!');
			return false;
		}
		
        document.getElementById('fileInput').addEventListener('change', function(event) {
		    let valid = checkPassword();
			if(!valid) {
				return;
			}
		
            const file = event.target.files[0];
            const reader = new FileReader();

            reader.onload = function(e) {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, { type: 'array' });
                const firstSheetName = workbook.SheetNames[0];
                const worksheet = workbook.Sheets[firstSheetName];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

                generateProductTable(jsonData);
            };

            reader.readAsArrayBuffer(file);
        });
		
		// 提取重量
		function extraWeight(description) {
			  // 定义正则表达式，用于匹配数字、克或g以及可能的乘法
			  const weightRegex = /(\d+)(克|g)/g;
			  const multiplierRegex = /\*(\d+)/g;

			  // 查找所有匹配的重量
			  let weightMatches = description.match(weightRegex);
			  let multiplierMatches = description.match(multiplierRegex);

			  // 如果没有找到匹配项，返回0
			  if (!weightMatches) {
				return 0;
			  }

			  // 初始化总重量
			  let totalWeight = 0;

			  // 遍历所有匹配的重量
			  weightMatches.forEach((match, index) => {
				// 提取数字
				let weight = parseInt(match.match(/\d+/)[0], 10);

				// 如果存在乘数，并且是当前重量的乘数，则乘以乘数
				if (multiplierMatches && multiplierMatches[index]) {
				  let multiplier = parseInt(multiplierMatches[index].match(/\d+/)[0], 10);
				  weight *= multiplier;
				}

				// 将提取的重量加到总重量上
				totalWeight += weight;
			  });

			  // 返回总重量
			  return totalWeight;
		}

        function generateProductTable(data) {
            const productStats = {};
            const headers = data[0];

            const productNameIndex = headers.indexOf('商品名称');
			const productPropIndex = headers.indexOf('商品属性');
			const productPriceIndex = headers.indexOf('商品价格');
            const orderAmountIndex = headers.indexOf('订单实际支付金额');
            const refundedAmountIndex = headers.indexOf('商品已退款金额');
            const recipientNameIndex = headers.indexOf('收件人姓名');
            const recipientAddressIndex = headers.indexOf('收件人地址');
            const recipientPhoneIndex = headers.indexOf('收件人手机');

            let  totalSuccessPerson = 0;
            for (let i = 1; i  0) {
                        productStats[productName+productProp].totalOrderAmount += orderAmount;
                        productStats[productName+productProp].totalOrders += 1;
                        productStats[productName+productProp].uniqueCustomers.add(recipientKey);
                    }

                    if (refundedAmount > 0) {
                        productStats[productName+productProp].totalRefundedAmount += refundedAmount;
                        productStats[productName+productProp].totalRefundedOrders += 1;
                    }
                }
            }

            const dataBody = document.getElementById('dataBody');
            dataBody.innerHTML = '';

            for (const [productName, stats] of Object.entries(productStats)) {
                const tr = document.createElement('tr');

                const nameCell = document.createElement('td');
                nameCell.textContent = stats.productName;
                tr.appendChild(nameCell);

				const propCell = document.createElement('td');
                propCell.textContent = stats.productProp;
                tr.appendChild(propCell);
				
                const orderAmountCell = document.createElement('td');
                orderAmountCell.textContent = stats.totalOrderAmount.toFixed(2);
                tr.appendChild(orderAmountCell);

                const orderCountCell = document.createElement('td');
                orderCountCell.textContent = stats.totalOrders;
                tr.appendChild(orderCountCell);

                const refundedAmountCell = document.createElement('td');
                refundedAmountCell.textContent = stats.totalRefundedAmount.toFixed(2);
                tr.appendChild(refundedAmountCell);

                const refundedCountCell = document.createElement('td');
                refundedCountCell.textContent = stats.totalRefundedOrders;
                tr.appendChild(refundedCountCell);

                const uniqueCustomersCell = document.createElement('td');
                uniqueCustomersCell.textContent = stats.uniqueCustomers.size;
                tr.appendChild(uniqueCustomersCell);
				
				// 更新总人数
				totalSuccessPerson += stats.uniqueCustomers.size;

                const priceCell = document.createElement('td');
                const inputPrice = document.createElement('input');
                inputPrice.type = 'number';
                inputPrice.placeholder = '客单价';
				inputPrice.value= stats.productPrice.toFixed(2);
                priceCell.appendChild(inputPrice);
                tr.appendChild(priceCell);

                const weightCell = document.createElement('td');
                const inputWeight = document.createElement('input');
                inputWeight.type = 'number';
                inputWeight.placeholder = '公斤价';
				inputWeight.value=10;
                weightCell.appendChild(inputWeight);
                tr.appendChild(weightCell);

                const customWeightCell = document.createElement('td');
                const inputCustomWeight = document.createElement('input');
                inputCustomWeight.type = 'number';
                inputCustomWeight.placeholder = '商品重量';
				inputCustomWeight.value=extraWeight(stats.productProp);
				if(inputCustomWeight.value == 0) {
				   inputCustomWeight.style.color = 'red';
				}
                customWeightCell.appendChild(inputCustomWeight);
                tr.appendChild(customWeightCell);

                dataBody.appendChild(tr);
            }

            document.getElementById('configSuccessPersonCount').value = totalSuccessPerson;
            document.getElementById('calculateProfit').classList.remove('hidden');
        }

        document.getElementById('calculateProfit').addEventListener('click', function() {
		    let valid = checkPassword();
			if(!valid) {
				return;
			}
			
            const rows = document.querySelectorAll('#dataBody tr');
            const costBody = document.getElementById('costBody');
            costBody.innerHTML = ''; // 清空之前的内容
            let totalProfit = 0;

            for (const row of rows) {
                const cells = row.querySelectorAll('td');
                const productName = cells[0].textContent;
                const productProp = cells[1].textContent;
                const totalOrderAmount = parseFloat(cells[2].textContent);
                const totalRefundedAmount = parseFloat(cells[4].textContent);
                const totalOrders = parseInt(cells[3].textContent);
                const totalRefundedOrders = parseInt(cells[5].textContent);
                const uniqueCustomersCount = parseInt(cells[6].textContent);
                const inputPrice = cells[7].querySelector('input');
                const inputWeight = cells[8].querySelector('input');
                const inputCustomWeight = cells[9].querySelector('input');

                const userPrice = parseFloat(inputPrice.value);
                const userKgPrice = parseFloat(inputWeight.value);
                const userCustomWeight = parseFloat(inputCustomWeight.value);

                if (!userPrice || userPrice ${productName}
					${productProp}
                    ${packageCount}
                    ${gramPrice.toFixed(4)}
                    ${technicalServiceFee.toFixed(2)}
                    ${shippingInsurance.toFixed(2)}
					${profit.toFixed(2)}
                    (${totalOrderAmount.toFixed(2)} - ${totalRefundedAmount.toFixed(2)}) - ${rawMaterialCost.toFixed(2)} - (${technicalServiceFee.toFixed(2)}) - (${shippingInsurance.toFixed(2)})
                `;
                costBody.appendChild(tr);
                totalProfit += profit;
            }

            let configSuccessPersonCount = document.getElementById('configSuccessPersonCount').value;
            let configKuaiduFeePerson = document.getElementById('configKuaiduFeePerson').value;
			let totalPureProfit = totalProfit - configSuccessPersonCount * configKuaiduFeePerson;
            document.getElementById('totalProfit').innerHTML = `总利润: ${totalPureProfit.toFixed(2)}=${totalProfit.toFixed(2)}-(${configSuccessPersonCount}*${configKuaiduFeePerson})`;
            document.getElementById('totalProfit').classList.remove('hidden');
            document.getElementById('costTable').classList.remove('hidden');
        });

总结

TODO：

1. 默认的商品+属性

2. 默认的商品采购价格

参考资料