reports

---- PAN Status Report -----
select b."PAN", get_nep_date(br."EntryDate"::date) as Date, count(brd."Id") as fileCount, fus."Name" as Status
from "BillingRegister"."BillingRegisters" br
join "BillingRegister"."Branches" b
	on b."Id" = br."BranchId"
join "BillingRegister"."BillingRegisterDetails" brd
	on brd."BillingRegisterId" = br."Id"
join "BillingRegister"."FileUploadStatus" fus
	on fus."Id" = br."BRStatusId"
group by b."PAN", br."EntryDate", fus."Name"



----- File Status Report ------
select fus."Id", fus."Name" as "Status", count(brd."Id")
from "BillingRegister"."BillingRegisterDetails" brd
join "BillingRegister"."FileUploadStatus" fus
	on brd."StatusId" = fus."Id"
group by fus."Id", fus."Name"


----- BillingRegister Report ------

select br."Id"
from "BillingRegister"."BillingRegisters" br
join "BillingRegister"."Branches" b
	on b."Id" = br."BranchId"
where b."PAN" in (
	WITH RECURSIVE q AS (
		SELECT "OfficeCode"
		from "Security"."Offices"
		where "OfficeCode" = :offcode
		UNION ALL
		SELECT t1."OfficeCode"
		from q c
		JOIN "Security"."Offices" t1
			ON t1."ParentId" = c."OfficeCode"
	)
	select op."Pan"::varchar(9) from q
	join "Security"."OfficePan" op
		on op."OfficeCode" = q."OfficeCode"
)
and br."MonthId" = 4
and br."Id" not in (select "BillingRegisterId" from "BillingRegister"."BillingRegisterDetails")



select
	get_nep_date(entry_date::date), count(pan) as uploaded_pan,
	sum(tot_uploaded_files) as tot_uploaded_files,
	sum(pending) as pending,
	sum(processing) as processing,
	sum(success) as success,
	sum(err) as err,
	sum(reuploaded) as reuploaded,
	sum(
		case
			when sales >= 1 and purchase >= 1 then 1
			else 0
		end
	) as uploaded_both_files
from (
WITH RECURSIVE q AS (
	SELECT "OfficeCode"
	from "Security"."Offices"
	where "OfficeCode" = :offcode
	UNION ALL
	SELECT t1."OfficeCode"
	from q c
	JOIN "Security"."Offices" t1
		ON t1."ParentId" = c."OfficeCode"
)
select br."EntryDate"::varchar(10) as entry_date, b."PAN" as pan, sum(
	case brd."StatusId"
		when 7 then 0
		else 1
	end
) as tot_uploaded_files,
sum(
	case brd."StatusId"
		when 1 then 1
		else 0
	end
) as pending,
sum(
	case brd."StatusId"
		when 2 then 1
		else 0
	end
) as processing,
sum(
	case brd."StatusId"
		when 4 then 1
		else 0
	end
) as success,
sum(
	case brd."StatusId"
		when 5 then 1
		else 0
	end
) as err,
sum(
	case brd."StatusId"
		when 7 then 1
		else 0
	end
) as reuploaded,
sum(
	case brd."BillingRegisterTypeId"
		when 1 then 1
		else 0
	end
) as sales,
sum(
	case brd."BillingRegisterTypeId"
		when 2 then 1
		else 0
	end
) as purchase
	from "BillingRegister"."BillingRegisters" br
	join "BillingRegister"."BillingRegisterDetails" brd
		on brd."BillingRegisterId" = br."Id"
	join "BillingRegister"."Branches" b
		on b."Id" = br."BranchId"
	join "Security"."OfficePan" op
		on op."Pan"::varchar(9) = b."PAN"
	join q
		on q."OfficeCode" = op."OfficeCode"
	where br."FiscalYearId" = :fyid
	and (:monthid is null or br."MonthId" = :monthid)
	group by br."EntryDate", b."PAN"
) as tbl
group by entry_date
order by entry_date asc;


----------- pan wise report ----------

select
	pan,
	sales_status."Name" as "SalesStatus",
	"EntryDate" as "SalesDate",
	purchase_status."Name" as "PurchaseStatus",
	"EntryDate" as "PurchaseDate",
	sales_ret_status."Name" as "SalesReturnStatus",
	"EntryDate" as "SalesReturnDate",
	purchase_ret_status."Name" as "PurchaseReturnStatus",
	"EntryDate" as "PurchaseReturnDate"
from (
WITH RECURSIVE q AS (
	SELECT "OfficeCode"
	from "Security"."Offices"
	where "OfficeCode" = :offcode
	UNION ALL
	SELECT t1."OfficeCode"
	from q c
	JOIN "Security"."Offices" t1
		ON t1."ParentId" = c."OfficeCode"
)
select
	b."PAN" as pan,
	max(case
		when brd."BillingRegisterTypeId" = 1 then brd."Id"
		else null
	end) as "Sales",
	max(case
		when brd."BillingRegisterTypeId" = 2 then brd."Id"
		else null
	end) as "Purchase",
	max(case
		when brd."BillingRegisterTypeId" = 3 then brd."Id"
		else null
	end) as "SalesReturn",
	max(case
		when brd."BillingRegisterTypeId" = 4 then brd."Id"
		else null
	end) as "PurchaseReturn",
	br."EntryDate"
	from "BillingRegister"."BillingRegisters" br
	left outer join "BillingRegister"."BillingRegisterDetails" brd
		on brd."BillingRegisterId" = br."Id"
	left outer join "BillingRegister"."FileUploadStatus" fus
		on fus."Id" = brd."StatusId"
	join "BillingRegister"."Branches" b
		on b."Id" = br."BranchId"
	join "Security"."OfficePan" op
		on op."Pan"::varchar(9) = b."PAN"
	join q
		on q."OfficeCode" = op."OfficeCode"
	where br."FiscalYearId" = :fyid
	and (:monthid is null or br."MonthId" = :monthid)
	and brd."StatusId" <> 7
	group by b."PAN", br."EntryDate"
) as tbl
left outer join "BillingRegister"."BillingRegisterDetails" sales
	on sales."Id" = tbl."Sales"
left outer join "BillingRegister"."FileUploadStatus" sales_status
	on sales."StatusId" = sales_status."Id"
left outer join "BillingRegister"."BillingRegisterDetails" purchase
	on purchase."Id" = tbl."Purchase"
left outer join "BillingRegister"."FileUploadStatus" purchase_status
	on purchase."StatusId" = purchase_status."Id"
left outer join "BillingRegister"."BillingRegisterDetails" sales_ret
	on sales_ret."Id" = tbl."SalesReturn"
left outer join "BillingRegister"."FileUploadStatus" sales_ret_status
	on sales_ret."StatusId" = sales_ret_status."Id"
left outer join "BillingRegister"."BillingRegisterDetails" purchase_ret
	on purchase_ret."Id" = tbl."PurchaseReturn"
left outer join "BillingRegister"."FileUploadStatus" purchase_ret_status
	on purchase_ret."StatusId" = purchase_ret_status."Id";







----------------------------xxxxxxxxxxxxxxxx

show data_directory;

select "SellerPAN", "UUID", "InvoiceNo", get_nep_date("BillDate"::date), "TotalAmount", "TotalPayableAmount" from "BillingRegister"."SalesRegisters" sr
where "BranchId" = 1;



select top 1 "EntryDate") from "BillingRegister"."BillingRegisters" br
order by "EntryDate"  desc;



select * from "BillingRegister"."BillingRegisterDetails" brd
where "BillingRegisterId" = 555;


select * from "BillingRegister"."PurchaseRegisterDumps" prd
where "BillingRegisterDetailId" = 941;

select * from "BillingRegister"."PurchaseRegisters" pr
where "BRDetId" = 1409

delete from "BillingRegister"."BillingRegisters"
where "Id" = 225;

select * from "BillingRegister"."BillingRegisterDetails" brd



select * from "BillingRegister"."BillingRegisterDetails" brd
join "BillingRegister"."SalesRegisterDumps" srd
	on srd."BillingRegisterDetailId" = brd."Id"
where brd."StatusId" = 2


select * from "BillingRegister"."BillingRegisterDetails" brd
where "StatusId" = 1
order by "Id";


select * from "BillingRegister"."BillingRegisters" br
where "BRStatusId" = 4


select * from "BillingRegister"."BillingRegisterDetails" brd
where "StatusId" = 2



select * from "BillingRegister"."SalesRegisterDumps"
where "BillingRegisterDetailId" in (
	578,
	639,
	698,
	725
)

update "BillingRegister"."BillingRegisterDetails"
set "StatusId" = 1
where "Id" <= 546;


update "BillingRegister"."BillingRegisters" set "BRStatusId" = 1 where "Id" <= 456


select * from "BillingRegister"."BillingRegisterDetails" brd
where "BillingRegisterId" = 285

select * from "BillingRegister"."BillingRegisters" br where "Id" = 479


select "FileName" from "BillingRegister"."BillingRegisterDetails" brd


select * from "BillingRegister"."BillingRegisters" br
where "Id" = 502


select count("Id") from "BillingRegister"."BillingRegisterDetails" brd
where "StatusId" = 1

insert into "BillingRegister"."BillingRegisterDetails"
("FileName", "BillingRegisterTypeId", "BillingRegisterId", "StatusId", "Name", "Issues")
values ('a65c211c-091e-4ed2-9eed-1058d7d1223a.xlsx', 1, 171, 1, null, null);

select * from "BillingRegister"."BillingRegisterDetails" brd where "FileName" = 'a65c211c-091e-4ed2-9eed-1058d7d1223a.xlsx'

select * from "BillingRegister"."BillingRegisters" where "Id" = 171;


select * from "BillingRegister"."BillingRegisters" br
where "BranchId" in (select "Id" from "BillingRegister"."Branches" b where b."PAN" = '601178395')


select * from "BillingRegister"."BillingRegisters" br
join "BillingRegister"."Branches" b
	on b."Id" = br."BranchId"
where b."PAN" = '301342340'

select * from "BillingRegister"."BillingRegisters" br
join "BillingRegister"."Branches" b
	on b."Id" = br."BranchId"
where b."PAN" = '601178395'


update "BillingRegister"."BillingRegisterDetails" set


select * from