Vet Drug Check

Use this workflow to audit each house's daily medication usage against the built-in rule table.

Inputs

Prepare these sources first:

• Daily production report (image or sheet) with house ID, female/male stock, female/male weight, daily water intake, and drug usage amounts.
• Built-in rule file: drug-rules.csv (usage method, numeric dosage limits, contraindications).

Workbook-scope rule:

• Treat every newly supplied workbook/spreadsheet as a brand-new source. Do not reuse findings, parsed rows, date ranges, or assumptions from any previously checked workbook.
• For workbook inputs, check all date blocks in that workbook by default, even when the filename looks like a single-day report such as 5.10.xlsx. Only narrow to one date or date range when the user explicitly says to do that for the current file.

If a required field is missing, state the gap and mark that item as cannot determine.

Do not query an external workbook for routine checks. Use external workbook lookup only if:

• a drug is not found in drug-rules.csv, or
• user explicitly asks to refresh rules from a new source file.

Dosage limits and contraindications must use 《兽药使用标准》 as the source of truth, as represented by references/drug-rules.csv or a user-supplied refreshed standard table. User corrections in conversation may define calculation口径 (for example, which denominator to use), but must not replace a numeric dosage limit unless the user explicitly identifies it as coming from 《兽药使用标准》.

Autonomous repair rule: if a workbook parser, denominator mapping, rule alias, unit conversion, contraindication match, or final/candidate classification is obviously wrong, fix the script/rule locally and rerun without asking the user for confirmation. Ask only when a required source file/login is missing, live source files would be modified, or the evidence is still insufficient after parser repair.

Workflow

1. Parse daily rows by house.
• For XHPS-style production daily sheets, date block height varies by template. Locate blocks from the date cell and column headers, then map house rows as one female row and one male row per house.
• For workbook inputs, scan the whole workbook sequentially for every date block and process each date block independently. Do not infer scope from the filename or from a previous file's output.
• Prefer header labels over fixed column numbers: 当日饮水量(L), 当日料量 kg, stock, actual weight, and repeated 药品/疫苗 + 用药量/千克/瓶/升 pairs.
• Use the female row's daily water intake as the house water denominator.
• For feed-based or mix-feed (混饲) drugs, use only the female row's daily feed as the feed denominator. Do not add the male row's feed.
• For body-weight-based drugs, use only female_count * female_avg_weight as the body-weight denominator. Do not include male stock or male body weight. If the female body weight is blank, mark the item cannot determine rather than using male weight as a fallback.
• When the daily report keeps the same body-weight value for a full 7-day week and only updates it on the first day of the next week, calculate the daily actual body weight by linear interpolation before overdose checks: daily_weight = current_week_weight + (next_week_weight - current_week_weight) / 7 * week_day, where week_day = age_day % 7 and 0 means the 7th day. Example: age day 9 uses week_day = 2.
• Default dosage calculation is female-only. If the report explicitly writes a separate rooster/cock (公鸡) drug amount, split the total by sex and calculate female and male usage separately with their own stock, body weight, and feed denominators. Do not add male usage into the female actual amount, and do not use male denominators unless male usage is explicitly recorded.
• For stock-count-based drugs or vaccines, use total stock (female_count + male_count) unless the rule explicitly limits to female birds.
• Regular drug slots are drug name + amount pairs across the row. Also scan the block's total/remark row and any lower continuation area for extra same-day drugs.
• Remark text such as H1-H12栋...1.5kg/栋/天, 1-12栋各用..., 5栋、11栋各用..., or 8/11栋各用... must be expanded into per-house drug records before calculating.
• Summary-row remarks such as AO=备注 and AP=H5 H7 H9 注射用头孢噻呋（0.5g/支）各64.00支 are valid same-day drug-use evidence. Parse the house list, strip parenthetical specs, and create one per-house drug record before overdose, continuous-use, and contraindication checks.
• For remark patterns like H1-H18 药品名 各1.00千克, H5 H7 H9 药品名 各64.00支, or H5,H7,H9 药品名 每栋64支, treat 各/每栋/每舍 as a per-house amount, not a total amount.
• If a remark amount omits a unit, infer it only from the same drug's repeated units elsewhere in the workbook; otherwise mark it cannot determine.
• Specifications directly attached to the veterinary drug name are part of the name/specification only and must not be used as house numbers, actual dosage, denominator, or limit calculation values. Examples include 10% in 10%盐酸左旋咪唑粉, 0.5g/支, and 1g/支.
• Do not use a later date's remark to fill an earlier date's stock, water, feed, weight, or diagnosis baseline. Exact-date missing denominators remain cannot determine.
2. For each house/day, collect all used drugs and actual amounts.
• Mandatory: every triggered check must include both overdose calculation and same-house same-day contraindication matching, even if the user only asks for one type of issue or does not mention contraindications.
3. For each drug, match the rule in drug-rules.csv by exact name first, then by closest unambiguous alias.
4. Determine dosage mode from the rule text or numeric limit columns:
• by_water: phrases like per 1L water or a per-liter-water limit.
• by_feed: phrases like per 1000kg feed or a feed-per-ton limit.
• by_weight: phrases like per 1kg body weight.
• by_stock: phrases like per bird or by stock count.
• by_feed_or_water_*: rules that explicitly allow either drinking water or feed; use the matching water/feed scale from the rule text (per 100L, per 1000kg, per kg, or separate water/feed ton limits).
• If a rule says per bird, per feather, or by stock count, use the stock formula even when the route also says drinking water.
• If a water rule is written per 100L, 1000L, or one ton of water, divide the dosage to a per-liter basis before comparing.
• If a rule allows both drinking water and feed but the report does not specify the route, compute both denominators and use the larger allowed amount to avoid false overdose findings.
• Treat limit_per_liter_g_or_ml as a structured normalized limit column: pure numeric powder limits default to g/L, liquid limits whose usage text says ml or 毫升 default to ml/L, and explicit units in the limit cell override the default. Do not infer mg from usage text like 600mg when the normalized column stores 0.6.
5. Compute maximum allowed usage with the matching denominator.
6. Convert units before comparing actual vs maximum.
7. Flag overdose when actual_converted > max_allowed.
• Vaccine exception: vaccines are normally used by whole bottles. If a vaccine's actual usage exceeds the calculated limit by no more than 1 bottle, do not report it as an overdose. Convert the excess back to bottles using the product package size; report only when the excess is greater than 1 bottle.
• If the denominator is zero or missing (for example a copied drug line on an empty house), do not report overdose; mark cannot determine or ignore the empty copied row if it clearly has no birds/feed/water.
8. Check continuous-use days.
• If the standard table has a numeric 连用天数上限, group usage by same house and same canonical drug.
• Count uninterrupted calendar-day runs; duplicate same-day entries count as one day.
• Report only when the consecutive run length is greater than the numeric upper limit.
• Ignore nonnumeric, blank, -, or clearly non-day values below 1.
9. Build same-house same-day drug set and run contraindication matching. Do not skip this step when no overdose is found.
10. Output per-house conclusions and a short summary. Always state the contraindication result as either concrete findings or 配伍禁忌未检出.

Core Formulas

Use these exact formulas:

• total_stock = female_count + male_count
• female_avg_weight_g = female row's actual/average body weight in grams per bird
• body_weight_kg = female_count * female_avg_weight_g / 1000; do not include male stock or male body weight. If female_avg_weight_g is blank, mark cannot determine.
• feed_kg = female_feed_kg for feed-based or mix-feed (混饲) rules; do not add male feed.
• max_allowed = water_L * dose_per_L
• max_allowed = feed_kg / 1000 * dose_per_ton_feed
• max_allowed = water_L / 100 * dose_per_100L
• max_allowed = body_weight_kg * dose_per_kg
• max_allowed = total_stock * dose_per_bird
• max_allowed = total_stock * per-bird kg/L stock-note limit

Known calculation rules from current farm standards:

• For 饲料添加剂葡萄糖氧化酶 and 饲料添加剂葡萄糖氧化酶（液体）, use the numeric upper limit recorded in 《兽药使用标准》 / drug-rules.csv; apply the feed denominator with female_feed_kg only. Do not replace the standard-table limit with an informal oral limit.
• For 地美硝唑预混剂, use 2.5kg per 1000kg feed.
• For all feed-based or mix-feed (混饲) dosage checks, feed_kg means the female row's daily feed only. Male feed is excluded even when the daily sheet has a male row, male feed amount, or a combined total feed value.
• For 混合型饲料添加剂 溶菌酶, 畜禽复合预混合饲料（澳龙营养）, and 禽用强力拜固舒S, if the standard allows both drinking water and feed and the report route is unclear, compute both paths and use the larger allowed value.
• For 百霉克, use the feed upper limit 2.5kg per ton feed.
• For any daily name containing 维乐多80, match 复合维生素维乐多80.
• For any daily name containing 雅士勇, match 复合预混合饲料 多种氨基酸和维生素 雅士勇.
• For 注射用头孢噻呋 and 注射用头孢噻呋钠, use the body-weight limit recorded in 《兽药使用标准》 / drug-rules.csv, with package-size conversion when the daily report uses bottles/vials.
• If daily-report remarks record 注射用头孢噻呋 or 注射用头孢噻呋钠 in 支 while another source or calculation path uses 瓶, treat 支 and 瓶 as 1:1 count units before applying package-size/body-weight conversion.
• For Step 4 pending-unit classification, 复合预混合饲料 科维抗, 替米考星溶液, and 托曲珠利溶液 are volume products recorded in L; infer L when the daily row has an amount but omits the unit. For 托曲珠利溶液, convert 1瓶 to 1L. Do not leave these products as pending solely because the row uses L or 瓶.
• For 替米考星溶液, use the limits recorded in 《兽药使用标准》 / drug-rules.csv; when the standard limit is based on active ingredient and the spec is 25%, convert product usage to active ingredient by actual_product_ml * 0.25. If the report route is unclear and the standard allows multiple routes, calculate each allowed route and use the larger allowed amount.
• For 酒石酸泰万菌素可溶性粉, use the rule recorded in 《兽药使用标准》 / drug-rules.csv; when the standard limit is based on active ingredient, convert product usage according to the product spec.
• For 盐酸左旋咪唑粉, use the body-weight limit recorded in 《兽药使用标准》 / drug-rules.csv; if the standard row writes the limit in mg or g, convert units rather than changing the numeric standard.
• For 盐酸林可霉素硫酸大观霉素粉 / 盐酸林可霉素硫酸大观霉素可溶性粉, apply the age-specific body-weight limit in the standard: 1-4 weeks old uses 0.15g/kg body weight; older than 4 weeks uses 0.075g/kg body weight. Do not apply the 4-weeks-plus limit to birds whose daily report shows week age 1-4.
• For 复方磺胺嘧啶混悬液, use the drinking-water upper limit 0.4ml/L. When the daily report records the amount in bottles, convert by 1 bottle = 100ml unless a same-source product label explicitly proves a different package size for that record.
• Do not match a daily generic name such as 液态酸度调和剂 to unrelated 混合型饲料添加剂 rows. If the exact acidifier product is not identifiable, list it under cannot determine / needs confirmation.
• If the matched standard row has no numeric upper limit, such as a row that only says 推荐添加量, mark the item cannot determine instead of inventing a limit.
• For the following note-table products, override the standard route and calculate by stock count using max_allowed = total_stock * per-bird limit; keep the result unit as kg for powders/additives recorded by mass and L for oral liquids/solutions recorded by volume: 荆防败毒散 0.003, 清瘟败毒散 0.003, 清瘟解毒口服液 0.0018, 双黄连口服液 0.001, 转移因子口服溶液 0.0005, 白头翁口服液 0.003, 白头翁散 0.003, 混合型饲料添加剂液态嗜酸乳杆菌 0.000025, 混合型饲料添加剂 液态牛磺酸 0.002.

Reference with worked examples: formulas.md

Unit Conversion

Normalize to the rule result unit before comparison:

• Mass: kg -> g multiply by 1000; mg -> g divide by 1000.
• Volume: L -> ml multiply by 1000; ml -> L divide by 1000.
• Product-specific volume defaults: 复合预混合饲料 科维抗, 替米考星溶液, and 托曲珠利溶液 use L; 托曲珠利溶液 瓶 -> L uses 1瓶=1L.
• Keep consistent units across actual and max_allowed.

If a product is recorded in % concentration while rule limit is based on active ingredient, mark needs manual active-ingredient confirmation.

Contraindication Check

For each drug with contraindications:

1. Split contraindication text by commas, ideographic commas, list separators, and newline.
2. Trim empty tokens.
3. Match each token against same-house same-day drug names.
4. If any hit exists, flag compatibility contraindication found; otherwise none.

When the contraindication is a drug class (for example penicillin class), treat class-name matches as hits.

Apply this explicit exception (override):

• If same-house same-day drug set contains both doxycycline hydrochloride soluble powder and ceftiofur, do not flag contraindication for this pair.
• This override has higher priority than generic class matching rules.

Output Format

If the user asks for results only, do not create or return a workbook. Use concise Chinese issue statements in this pattern:

日期 + 栋舍 + 使用的兽药存在超量情况，限量...，原因：...。

Return results in this order:

1. Overdose findings by house and drug.
2. Continuous-use findings where same house and same drug exceed 连用天数上限.
3. Contraindication findings by house.
4. Uncertain items (rule missing, unit mismatch, cannot determine).

Mandatory reporting rule:

• Always perform and report overdose checks and contraindication checks for every request handled by this skill, regardless of whether the user explicitly asks for both.
• If a user correction, parser fix, rule update, denominator change, sex-split rule, unit/spec correction, or any other recheck changes the issue set, immediately rerun from the current source file(s) and resend the updated issue list in chat. Treat earlier issue lists as superseded; do not wait for a separate "send again" request.
• When sending overdose findings or overdose calculation details, every overdose issue must start with the issue time/date range and the complete issue sentence, then provide the detailed calculation process. Include the formula used, actual amount and unit conversion, denominator source/value (water, feed, body weight, or stock), calculated maximum allowed amount, and the final comparison (actual > max_allowed). If sex-split dosing or weekly body-weight interpolation was used, show those intermediate values explicitly. For grouped findings, keep the time/date range with each issue block and summarize per-date denominators/limits instead of omitting the calculation.
• If no overdose is found, state 未发现确定超量项.
• If no contraindication is found, state 配伍禁忌未检出.
• Do not omit either section from the final answer unless the source data is too incomplete to perform the check; in that case, state what data is missing under uncertain items.

For each checked drug, include:

• house_id
• drug_name
• actual
• max_allowed
• status (compliant or overdose)
• contraindication_status

Rule Refresh

When user asks to update built-in rules:

1. Import the latest workbook.
2. Run:

python scripts/refresh_drug_rules.py

1. Keep the same output filename (references/drug-rules.csv) so routine checks continue without workflow changes.

Known Daily-Name Aliases

Use these aliases before marking a daily drug name as rule missing:

• 硫酸粘菌素可溶性粉 = 硫酸黏菌素可溶性粉.
• 水溶性维生素E(50%) = DL-α-生育酚乙酸酯粉（维生素E）.
• 水溶性复合维生素 = 畜禽复合预混合饲料（澳龙营养）.
• 百霉克 = 混合型饲料添加剂酿酒酵母、海藻酸钠百奥明-百霉克.
• 强力拜固舒S and 强力拜固舒 = 禽用复合预混合饲料（禽用强力拜固舒S）.
• 盐酸林可霉素硫酸大观霉素粉 = 盐酸林可霉素硫酸大观霉素可溶性粉.
• Daily names containing 维乐多80 = 复合维生素维乐多80.
• Daily names containing 雅士勇 = 复合预混合饲料 多种氨基酸和维生素 雅士勇.
• Daily names containing 禽脑、鸡痘二联活疫苗 = 禽脑脊髓炎、鸡痘二联活疫苗(YBF02株+鹌鹑化弱毒株).
• Daily names containing 鸡新、支二联活疫苗 = 鸡新城疫、传染性支气管炎二联活疫苗(LaSota株+H120株).
• Daily names containing 鸡新流腺(I群4型)三联灭活疫苗 = 鸡新城疫、禽流感(H9亚型)、禽腺病毒病(I群4型)三联灭活疫苗(La Sota株+YBF13株+YBAV-4株).
• Daily names containing 新、法、关 = 鸡新城疫、传染性法氏囊病、病毒性关节炎三联灭活疫苗(La Sota株+B87株+S1133株).
• Daily names containing 鸡新、关二联灭活疫苗 = 鸡新城疫、病毒性关节炎二联灭活疫苗(La Sota株+AV2311株).

Alias matching removes rule missing, but it does not override missing numeric limits. If the aliased standard row still lacks a numeric maximum or has incompatible units, report it under uncertain items for user confirmation.

Toolbox

Use the local toolbox script for repeatable lookups and calculations:

• Script: scripts/vet_drug_toolbox.py
• Example input: references/toolbox-input-example.json

Query drug rules by name:

python scripts/vet_drug_toolbox.py query --name "doxycycline"

Evaluate overdose + contraindication for one house JSON payload:

python scripts/vet_drug_toolbox.py evaluate --input references/toolbox-input-example.json

Input fields for evaluate:

• house_id
• water_l (optional for non-water drugs)
• female_count or female_stock (preferred for body-weight checks; do not include male stock)
• male_count or male_stock (used only for stock-count rules/vaccines unless a rule explicitly says otherwise)
• female_avg_weight_kg (preferred for body-weight checks; average body weight per bird)
• female_weight_kg (legacy alias for female_avg_weight_kg; values over 100 are treated as an already-calculated body-weight denominator)
• male_weight_kg (do not use for body-weight checks; keep only as non-body-weight source data if present)
• total_weight_kg (legacy alias for body-weight checks; if used, it must mean female-only total body weight, never female+male combined total weight)
• total_stock (optional for non-stock drugs)
• female_feed_kg (optional for non-feed drugs; preferred for feed-based or mix-feed checks)
• feed_kg (legacy alias for female_feed_kg; if used, it must already exclude male feed)
• drugs[] with name, actual_value, actual_unit