$scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
if (-not $scriptDir) { $scriptDir = Get-Location }
$breachFile = Join-Path $scriptDir "解约校招生.xlsx"
$internDbFile = Join-Path $scriptDir "入职过的实习生名单.xlsx"
$outputFile = Join-Path $scriptDir "解约分析看板.xlsx"

if (-not (Test-Path $breachFile)) { Write-Host "错误: 找不到 解约校招生.xlsx"; pause; exit }
if (-not (Test-Path $internDbFile)) { Write-Host "提示: 找不到 入职过的实习生名单.xlsx (仅使用解约名单自身日期)"; $internDbFile = $null }

Write-Host "========================================="
Write-Host "  校招实习生解约分析工具 v1.0"
Write-Host "========================================="

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false; $excel.DisplayAlerts = $false

# === 1. 读取解约名单 ===
Write-Host "[1/4] 读取解约名单..."
$wb1 = $excel.Workbooks.Open($breachFile)
$breachList = @{}

# Sheet1
$ws1 = $wb1.Worksheets.Item(1)
$lr1 = $ws1.UsedRange.Rows.Count
$d1 = $ws1.Range($ws1.Cells(2,1), $ws1.Cells($lr1,7)).Value2
for ($i = 1; $i -le $d1.GetLength(0); $i++) {
    $idNo = ""; if ($d1[$i,4]) { $idNo = $d1[$i,4].ToString().Trim() }
    if ($idNo.Length -lt 5) { continue }
    $name = if ($d1[$i,3]) { $d1[$i,3].ToString() } else { "" }
    $city = if ($d1[$i,2]) { $d1[$i,2].ToString() } else { "" }
    $status = if ($d1[$i,5]) { $d1[$i,5].ToString() } else { "" }
    $hd = ""; $ld = ""
    if ($d1[$i,6] -and $d1[$i,6].ToString() -notmatch "N/A|#N/A") { $hd = $d1[$i,6].ToString() }
    if ($d1[$i,7] -and $d1[$i,7].ToString() -notmatch "N/A|#N/A") { $ld = $d1[$i,7].ToString() }
    if (-not $breachList[$idNo]) {
        $breachList[$idNo] = @{ name=$name; idNo=$idNo; city=$city; status=$status; hireDate=$hd; leaveDate=$ld; dept1=""; dept2=""; dept3="" }
    } else {
        if ($hd -and (-not $breachList[$idNo].hireDate)) { $breachList[$idNo].hireDate=$hd; $breachList[$idNo].leaveDate=$ld }
    }
}

# Sheet2
if ($wb1.Worksheets.Count -ge 2) {
    $ws2 = $wb1.Worksheets.Item(2)
    $lr2 = $ws2.UsedRange.Rows.Count
    $d2 = $ws2.Range($ws2.Cells(2,1), $ws2.Cells($lr2,9)).Value2
    for ($i = 1; $i -le $d2.GetLength(0); $i++) {
        $idNo = ""; if ($d2[$i,3]) { $idNo = $d2[$i,3].ToString().Trim() }
        if ($idNo.Length -lt 5) { continue }
        $name = if ($d2[$i,2]) { $d2[$i,2].ToString() } else { "" }
        $city = if ($d2[$i,1]) { $d2[$i,1].ToString() } else { "" }
        $status = if ($d2[$i,4]) { $d2[$i,4].ToString() } else { "" }
        $hd = ""; $ld = ""
        if ($d2[$i,5] -and $d2[$i,5].ToString() -notmatch "N/A|#N/A") { $hd = $d2[$i,5].ToString() }
        if ($d2[$i,6] -and $d2[$i,6].ToString() -notmatch "N/A|#N/A") { $ld = $d2[$i,6].ToString() }
        $de1 = if ($d2[$i,7]) { $d2[$i,7].ToString() } else { "" }
        $de2 = if ($d2[$i,8]) { $d2[$i,8].ToString() } else { "" }
        $de3 = if ($d2[$i,9]) { $d2[$i,9].ToString() } else { "" }
        if (-not $breachList[$idNo]) {
            $breachList[$idNo] = @{ name=$name; idNo=$idNo; city=$city; status=$status; hireDate=$hd; leaveDate=$ld; dept1=$de1; dept2=$de2; dept3=$de3 }
        } else {
            if ($hd -and (-not $breachList[$idNo].hireDate)) { $breachList[$idNo].hireDate=$hd; $breachList[$idNo].leaveDate=$ld }
            if ($de1 -and (-not $breachList[$idNo].dept1)) { $breachList[$idNo].dept1=$de1; $breachList[$idNo].dept2=$de2; $breachList[$idNo].dept3=$de3 }
        }
    }
}
$wb1.Close($false)
Write-Host ("  解约人数(去重): " + $breachList.Count)

# === 2. 读取入职数据库(可选) ===
$internDbIds = @{}
if ($internDbFile -and (Test-Path $internDbFile)) {
    Write-Host "[2/4] 读取入职数据库..."
    $wb3 = $excel.Workbooks.Open($internDbFile)
    $ws3 = $wb3.Worksheets.Item(1)
    $lr3 = $ws3.UsedRange.Rows.Count
    $arr3 = $ws3.Range($ws3.Cells(2,2), $ws3.Cells($lr3,3)).Value2
    for ($i = 1; $i -le $arr3.GetLength(0); $i++) {
        $did = ""; if ($arr3[$i,1]) { $did = $arr3[$i,1].ToString().Trim() }
        if ($did.Length -ge 5 -and -not $internDbIds.ContainsKey($did)) { $internDbIds[$did] = $true }
    }
    $wb3.Close($false)
    Write-Host ("  数据库人数: " + $internDbIds.Count)
} else {
    Write-Host "[2/4] 跳过入职数据库(未找到)"
}

# === 3. 分类分析 ===
Write-Host "[3/4] 分类分析..."
$results = [System.Collections.ArrayList]::new()
$interned = 0; $notInterned = 0
$deptStats = @{}; $cityStats = @{}; $statusStats = @{}

foreach ($kv in $breachList.GetEnumerator()) {
    $idNo = $kv.Key; $b = $kv.Value
    $hasDate = $b.hireDate.Length -gt 0
    $inDb = $internDbIds.ContainsKey($idNo)
    $didIntern = $hasDate -or $inDb
    
    if ($didIntern) {
        $interned++
        $hasLabel = "是"
        $finalHD = $b.hireDate
        $finalLD = $b.leaveDate
    } else {
        $notInterned++
        $hasLabel = "否"
        $finalHD = ""; $finalLD = ""
    }
    
    $days = ""
    if ($finalHD -and $finalLD -and $finalHD.Length -ge 10 -and $finalLD.Length -ge 10) {
        try {
            $h = [datetime]::ParseExact($finalHD.Substring(0,10), "yyyy-MM-dd", $null)
            $l = [datetime]::ParseExact($finalLD.Substring(0,10), "yyyy-MM-dd", $null)
            $days = ($l - $h).Days
        } catch {}
    }
    
    $dept = $b.dept1; if (-not $dept) { $dept = "(未获取)" }
    
    [void]$results.Add([PSCustomObject]@{
        IDNo=$idNo; Name=$b.name; City=$b.city; BreachStatus=$b.status
        HasInterned=$hasLabel; HireDate=$finalHD; LeaveDate=$finalLD
        InternDays=$days; Dept1=$dept; Dept2=$b.dept2; Dept3=$b.dept3
    })
    
    if ($didIntern) { 
        if (-not $deptStats[$dept]) { $deptStats[$dept] = 0 }; $deptStats[$dept]++
    }
    if ($b.city) { if (-not $cityStats[$b.city]) { $cityStats[$b.city] = 0 }; $cityStats[$b.city]++ }
    if ($b.status) { if (-not $statusStats[$b.status]) { $statusStats[$b.status] = 0 }; $statusStats[$b.status]++ }
}

# === 4. 输出Excel ===
Write-Host "[4/4] 生成Excel..."
$total = $breachList.Count
$iRate = [Math]::Round($interned / [Math]::Max(1,$total) * 100, 1)
$nRate = [Math]::Round($notInterned / [Math]::Max(1,$total) * 100, 1)

$wbOut = $excel.Workbooks.Add()

# --- 看板 ---
$wsD = $wbOut.Worksheets.Item(1)
$wsD.Name = "看板"
$wsD.Cells.Item(1,2) = "校招解约分析看板"
$wsD.Range("B1:D1").Merge(); $wsD.Range("B1:D1").Font.Bold=$true; $wsD.Range("B1:D1").Font.Size=16

$r=3
$items = @(
    @("一、概览", ""),
    @("解约总人数", "$total"),
    @("曾入职过", "${interned}人 (${iRate}%)"),
    @("未入职过", "${notInterned}人 (${nRate}%)")
)
foreach ($it in $items) {
    $wsD.Cells.Item($r,2)=$it[0]; $wsD.Cells.Item($r,3)=$it[1]
    if ($r -eq 3) { $wsD.Range("B$r:C$r").Font.Bold=$true }
    $r++
}
$r++

$wsD.Range("B$r:C$r").Value = "二、签约状态分布"; $wsD.Range("B$r:C$r").Font.Bold=$true; $r++
foreach ($s in ($statusStats.GetEnumerator()|Sort Value -Desc)) {
    $wsD.Cells.Item($r,2)=$s.Key; $wsD.Cells.Item($r,3)=$s.Value; $r++
}
$r++

$wsD.Range("B$r:C$r").Value = "三、工作城市分布"; $wsD.Range("B$r:C$r").Font.Bold=$true; $r++
foreach ($c in ($cityStats.GetEnumerator()|Sort Value -Desc)) {
    $wsD.Cells.Item($r,2)=$c.Key; $wsD.Cells.Item($r,3)=$c.Value; $r++
}
$r++

$wsD.Range("B$r:C$r").Value = "四、已入职解约者-部门分布"; $wsD.Range("B$r:C$r").Font.Bold=$true; $r++
foreach ($d in ($deptStats.GetEnumerator()|Sort Value -Desc)) {
    $wsD.Cells.Item($r,2)=$d.Key; $wsD.Cells.Item($r,3)=$d.Value; $r++
}

$wsD.Columns(2).ColumnWidth=35; $wsD.Columns(3).ColumnWidth=20

# --- 明细 ---
$wsM = $wbOut.Worksheets.Add()
$wsM.Name = "明细"
$hdrs = @("序号","证件号","姓名","城市","签约状态","是否入职过","入职日期","离职日期","实习天数","一级部门","二级部门","三级部门")
for ($ci=0; $ci -lt $hdrs.Count; $ci++) { $wsM.Cells.Item(1,$ci+1) = $hdrs[$ci] }
$wsM.Range("A1:L1").Font.Bold=$true

$di=1
foreach ($rx in $results) {
    $wsM.Cells.Item($di+1,1)=$di; $wsM.Cells.Item($di+1,2)=("'" + $rx.IDNo)
    $wsM.Cells.Item($di+1,3)=$rx.Name; $wsM.Cells.Item($di+1,4)=$rx.City
    $wsM.Cells.Item($di+1,5)=$rx.BreachStatus; $wsM.Cells.Item($di+1,6)=$rx.HasInterned
    $wsM.Cells.Item($di+1,7)=$rx.HireDate; $wsM.Cells.Item($di+1,8)=$rx.LeaveDate
    $wsM.Cells.Item($di+1,9)=$(if ($rx.InternDays -ne "") { $rx.InternDays } else { "" })
    $wsM.Cells.Item($di+1,10)=$rx.Dept1; $wsM.Cells.Item($di+1,11)=$rx.Dept2
    $wsM.Cells.Item($di+1,12)=$rx.Dept3
    $di++
}
$wsM.Columns.AutoFit()

$wbOut.SaveAs($outputFile)
$wbOut.Close($false)
$excel.Quit()

Write-Host ""
Write-Host "==================== 分析完成 ===================="
Write-Host ("解约总人数(去重): " + $total)
Write-Host ("曾入职过: " + $interned + " 人 (" + $iRate + "%)")
Write-Host ("未入职过: " + $notInterned + " 人 (" + $nRate + "%)")
Write-Host ""
Write-Host "输出文件: " + $outputFile
Write-Host "================================================="
pause


<script defer data-domain="html.cafe" src="https://milkymouse.com/js/script.js"></script>