xiaoshu-app/scripts/sql/store-payroll.sql

-- Independent store batches keep company-wide once-only lesson/reward indexes.
BEGIN;
SELECT set_config('xiaoshu.payroll_write','yes',true);
ALTER TABLE "PayrollBatch" ADD COLUMN IF NOT EXISTS "scopeStoreId" numeric NOT NULL DEFAULT 0;
ALTER TABLE "PayrollBatch" ALTER COLUMN "scopeStoreId" SET DEFAULT 0;
UPDATE "PayrollBatch" SET "scopeStoreId"=0 WHERE "scopeStoreId" IS NULL;
ALTER TABLE "PayrollBatch" ALTER COLUMN "scopeStoreId" SET NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS payroll_scope_month ON "PayrollBatch" (company,month,"scopeStoreId");
DROP INDEX IF EXISTS payroll_company_month_once;
CREATE OR REPLACE FUNCTION xs_payroll_source_store(co text,s jsonb) RETURNS numeric LANGUAGE sql STABLE AS $$
 SELECT COALESCE(NULLIF(COALESCE(NULLIF(s->>'storeId','0'),NULLIF(s->>'szmdid','0')),'')::numeric,
 (SELECT NULLIF(u."legacyUserData"->>'ParentUserID','')::numeric FROM "_User" u WHERE u.company=co AND u."legacyUserId"::text=COALESCE(NULLIF(s->>'coachId','0'),s->>'jsmz') LIMIT 1),0)
$$;
CREATE OR REPLACE FUNCTION xs_payroll_scope_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN IF NEW."scopeStoreId" IS DISTINCT FROM OLD."scopeStoreId" THEN RAISE EXCEPTION '不能修改工资批次门店归属';END IF;RETURN NEW;END $$;
DROP TRIGGER IF EXISTS payroll_scope_immutable ON "PayrollBatch";
CREATE TRIGGER payroll_scope_immutable BEFORE UPDATE ON "PayrollBatch" FOR EACH ROW EXECUTE FUNCTION xs_payroll_scope_immutable();
CREATE OR REPLACE FUNCTION xs_payroll_command(co text,actor text,op text,target text,req jsonb,idem text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE b "PayrollBatch"; previous jsonb; saved "PayrollCommand"; line jsonb; candidate "PayrollLine"; key text; result jsonb; u "_User"; st "_User"; actor_store text; scope_id numeric:=COALESCE((req->>'scopeStoreId')::numeric,0); source_row jsonb;
BEGIN
 IF co IS NULL OR length(COALESCE(idem,''))<8 OR length(trim(COALESCE(req->>'reason','')))<2 THEN RAISE EXCEPTION '缺少帐套、操作原因或请求编号'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('xs-payroll:'||co));
 PERFORM set_config('xiaoshu.payroll_write','yes',true);
 IF EXISTS(SELECT 1 FROM "_User" WHERE company=co AND "objectId"=actor AND ("identityType" IN ('store','store-admin') OR "identityType" IS NULL AND "legacyGroupId"=2)) THEN
  actor_store:=xs_store_actor(co,actor);
  SELECT * INTO st FROM "_User" WHERE "objectId"=actor_store;
  IF scope_id IS DISTINCT FROM st."legacyUserId" THEN RAISE EXCEPTION '工资门店归属无效'; END IF;
  IF op<>'create' AND NOT EXISTS(SELECT 1 FROM "PayrollBatch" WHERE company=co AND "objectId"=target AND "scopeStoreId"=scope_id) THEN RAISE EXCEPTION '工资单不存在或不属于本店'; END IF;
 ELSIF scope_id<>0 THEN RAISE EXCEPTION '请使用对应门店账号创建门店工资'; END IF;
 IF op<>'create' AND NOT EXISTS(SELECT 1 FROM "PayrollBatch" WHERE company=co AND "objectId"=target AND "scopeStoreId"=scope_id) THEN RAISE EXCEPTION '工资单不属于当前结算范围'; END IF;
 req:=req||jsonb_build_object('actor',actor,'operation',op,'target',target);
 SELECT * INTO saved FROM "PayrollCommand" WHERE "company"=co AND "requestId"=idem;
 IF FOUND THEN IF saved."request" IS DISTINCT FROM (req-'lines'-'sourceVersion'-'cutoffVersion') THEN RAISE EXCEPTION '请求编号已用于不同操作'; END IF;RETURN saved."result";END IF;
 IF op='create' THEN
  IF COALESCE(req->>'month','') !~ '^\d{4}-(0[1-9]|1[0-2])$' THEN RAISE EXCEPTION '工资月份无效'; END IF;
  INSERT INTO "PayrollBatch" ("objectId","company","sourceKey","month","status","createdAt","updatedAt","createdBy","scopeStoreId") VALUES(xs_object_id(),co,'payroll:'||co||':'||scope_id||':'||(req->>'month'),req->>'month','draft',now(),now(),actor,scope_id) RETURNING * INTO b;
 ELSE
  SELECT * INTO b FROM "PayrollBatch" WHERE "company"=co AND "objectId"=target FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION '工资单不存在'; END IF;
  IF req->>'version' IS NULL OR (req->>'version')::int<>b."version" THEN RAISE EXCEPTION '工资单已更新，请刷新后重试'; END IF;
 END IF;
 previous:=to_jsonb(b);
 IF op IN ('create','refresh','review') THEN
  IF b."status"<>'draft' THEN RAISE EXCEPTION '工资单已锁定'; END IF;
  IF req->>'sourceVersion' IS DISTINCT FROM xs_payroll_source_version(co) THEN RAISE EXCEPTION '计薪来源已更新，请重新核对'; END IF;
  IF to_regprocedure('xs_payroll_assert_cutoff(text,text,text,text)') IS NOT NULL THEN PERFORM xs_payroll_assert_cutoff(co,b."month",op,req->>'cutoffVersion'); END IF;
  -- Only automated lines are rebuilt; explicit exclusions and adjustments survive.
  DELETE FROM "PayrollLine" WHERE "company"=co AND "batchId"=b."objectId" AND "kind" IN ('lesson','review_reward') AND NOT COALESCE("excluded",false);
  FOR line IN SELECT value FROM jsonb_array_elements(COALESCE(req->'lines','[]')) LOOP
   IF line->>'kind' NOT IN ('lesson','review_reward') THEN RAISE EXCEPTION '自动明细类型无效'; END IF;
   IF line->>'kind'='lesson' AND EXISTS(SELECT 1 FROM "PayrollLine" WHERE "company"=co AND "batchId"=b."objectId" AND "standardLessonKey"=line->>'standardLessonKey' AND COALESCE("excluded",false)) THEN CONTINUE; END IF;
   IF actor_store IS NOT NULL THEN
    IF COALESCE((line->>'storeId')::numeric,0)<>scope_id THEN RAISE EXCEPTION '工资明细不属于本店'; END IF;
    IF line->>'kind'='lesson' THEN
     SELECT s INTO source_row FROM xs_payroll_sources(co) s WHERE s->>'standardLessonKey'=line->>'standardLessonKey';
     IF source_row IS NULL OR xs_payroll_source_store(co,source_row)<>scope_id THEN RAISE EXCEPTION '计薪课次不属于本店'; END IF;
    ELSIF NOT EXISTS(SELECT 1 FROM "ReviewRewardIncome" r WHERE r.company=co AND r."objectId"=line->>'reviewRewardId' AND r."storeId"=scope_id) THEN RAISE EXCEPTION '复习工资不属于本店'; END IF;
   END IF;
   line:=line||jsonb_build_object('objectId',xs_object_id(),'company',co,'batchId',b."objectId",'settlementMonth',b."month",'createdAt',now(),'updatedAt',now(),'excluded',false,'sourceKey','payroll-line:'||xs_object_id());
   candidate:=jsonb_populate_record(NULL::"PayrollLine",line); INSERT INTO "PayrollLine" SELECT candidate.*;
  END LOOP;
  IF op='review' THEN b."status":='reviewed'; END IF;
 ELSIF op='adjustment' THEN
  IF b."status"<>'draft' THEN RAISE EXCEPTION '只有草稿可以调整'; END IF;
  IF (req->>'amount')::numeric=0 OR abs((req->>'amount')::numeric)>1000000000 OR round((req->>'amount')::numeric,2)<>(req->>'amount')::numeric THEN RAISE EXCEPTION '调整金额无效，最多两位小数'; END IF;
  SELECT * INTO u FROM "_User" WHERE "company"=co AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=req->>'coachId' AND ("identityType"='coach' OR "identityType" IS NULL AND COALESCE("legacyGroupId"::text,"legacyUserData"->>'GroupID')='3');
  IF NOT FOUND THEN RAISE EXCEPTION '陪练不存在'; END IF;
  IF actor_store IS NOT NULL AND u."legacyUserData"->>'ParentUserID' IS DISTINCT FROM scope_id::text THEN RAISE EXCEPTION '陪练不属于本店'; END IF;
  SELECT * INTO st FROM "_User" WHERE "company"=co AND COALESCE("legacyUserId"::text,"legacyUserData"->>'UserID')=u."legacyUserData"->>'ParentUserID' LIMIT 1;
  INSERT INTO "PayrollLine" ("objectId","company","batchId","sourceKey","kind","coachId","coachObjectId","coachName","storeId","storeName","amount","rateAmount","durationHours","originalMonth","settlementMonth","description","exception","createdAt","updatedAt")
  VALUES(xs_object_id(),co,b."objectId",'adjustment:'||idem,'adjustment',(req->>'coachId')::numeric,u."objectId",COALESCE(NULLIF(u."realName",''),NULLIF(u."nickname",''),u."username"),COALESCE(st."legacyUserId",0),COALESCE(NULLIF(st."realName",''),NULLIF(st."nickname",''),st."username",'未绑定门店'),(req->>'amount')::numeric,0,0,b."month",b."month",req->>'reason','',now(),now());
 ELSIF op='exclude' THEN
  UPDATE "PayrollLine" SET "excluded"=true,"exclusionReason"=req->>'reason',"updatedAt"=now() WHERE "company"=co AND "batchId"=b."objectId" AND "objectId"=req->>'lineId' AND "kind"='lesson' AND COALESCE("exception",'')<>'';
  IF NOT FOUND THEN RAISE EXCEPTION '只能排除异常课次'; END IF;
 ELSIF op='mark-paid' THEN
  IF b."status"<>'reviewed' THEN RAISE EXCEPTION '只有已审核工资单可登记发放'; END IF;b."status":='paid';
 ELSE RAISE EXCEPTION '工资操作无效'; END IF;
 UPDATE "PayrollBatch" SET "status"=b."status","sourceRefreshedAt"=CASE WHEN op IN ('create','refresh','review') THEN now() ELSE "sourceRefreshedAt" END,
 "reviewedAt"=CASE WHEN op='review' THEN now() ELSE "reviewedAt" END,"reviewedBy"=CASE WHEN op='review' THEN actor ELSE "reviewedBy" END,
 "paidAt"=CASE WHEN op='mark-paid' THEN now() ELSE "paidAt" END,"paidBy"=CASE WHEN op='mark-paid' THEN actor ELSE "paidBy" END,
 "paymentMethod"=CASE WHEN op='mark-paid' THEN req->>'paymentMethod' ELSE "paymentMethod" END,"paymentReference"=CASE WHEN op='mark-paid' THEN req->>'paymentReference' ELSE "paymentReference" END
 WHERE "company"=co AND "objectId"=b."objectId" RETURNING to_jsonb("PayrollBatch".*) INTO result;
 PERFORM xs_payroll_audit(co,actor,op,b."objectId",req->>'reason',previous,result);
 INSERT INTO "PayrollCommand" ("company","requestId","request","result") VALUES(co,idem,req-'lines'-'sourceVersion'-'cutoffVersion',result);
 RETURN result;
END $$;

COMMIT;