CREATE OR REPLACE FUNCTION func.fn_category(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
	begin
		
	-- select func.fn_category({"schema":"sys", "operate":""},{"userid":""}, 'result'); fetch all in result;
	
	perform public.setschema(sysparams->>'schema');
		
	
	
	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'ddl') THEN 
		open $3 for
			select id,name,false as checked,logo,font_code from category where dispensaryid= (params->>'dispensary')::uuid and isactive=true 
			and case when (params->>'device'='app' and (params->>'platform_type'='ios' or coalesce(params->>'platform_type','')='')) then hide_from_mobile=false else true end
			order by catorder Asc;
		return next $3;
	
	elseif (sysparams->>'operate' = 'subcategoryddl') THEN
		open $3 for
			select id,name from subcategory where dispensaryid= (params->>'dispensary')::uuid and  parentid=(params->>'parentid')::int and isactive=true;
		return next $3;
	elseif (sysparams->>'operate' = 'edit') THEN
		open $3 for
			select id,name,shortdesc,isactive,title,keyword,description,coalesce(logo,'') as logo,is_thc from category where  dispensaryid= (params->>'dispensary')::uuid and  id=(params->>'id')::int ; 
		return next $3;   
	elseif (sysparams->>'operate' = 'subcategoryedit') THEN
		open $3 for
			select id,name,parentid,isactive,shortdesc from subcategory where  id=(params->>'id')::int ;
		return next $3;  
	elseif (sysparams->>'operate' = 'categorydetailbyname') THEN
		open $3 for
			select name,logo,description from category where name=(params->>'name') and dispensaryid= (select case when (isvirtual=true) then parentid else id end from dispensary where id=(params->>'dispensary')::uuid limit 1) and isactive=true;
		return next $3; 
	elseif (sysparams->>'operate' = 'ddlx') THEN
		open $3 for
			select id,name from category where dispensaryid= (params->>'dispensary')::uuid and isactive=true order by catorder Asc;
		return next $3;
	elseif (sysparams->>'operate' = 'checkthc') THEN
		open $3 for
			select t->>'name' as name,c.is_thc from category c inner join jsonb_array_elements((params->>'category')::jsonb) t on lower(c.name)=lower(t->>'name') where c.dispensaryid= (params->>'dispensary')::uuid;
		return next $3;
	end if;
	
	
	END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_discount(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
declare _proddetailid int;
declare _curdate date;
declare _timezone varchar(50);
declare _coupons json;
declare _mappings json;
		begin
		
	-- select func.fn_brand({"schema":"sys", "operate":""},{"userid":""}, 'result'); fetch all in result;
	
	perform public.setschema(sysparams->>'schema');
		
	if(params->>'operate' = 'masteredit') then
		_coupons := (select json_agg(json_build_object ('id',id,'couponcode',couponcode,'fromdate',fromdate,'active',isactive,'activelbl',case when (isactive) then 'Yes' else 'No' end,'todate',todate,'coupontype',coupontype,'couponlimit',maxlimit,'couponactive',isactive,'exist',false,'pending',couponlimit)) from coupon where disid=(params->>'id')::int);
	
		_mappings :=(
			select json_agg(jsonb_build_object('id',d.ref_id,'module',d.module,'name',(case when b.name is not null then b.name when c.name is not null then c.name else p.name end),'price',p.price,'brand',p.brand,'category',p.category)) from discountmapping d 			
			left join brand b on b.id=d.ref_id and d.module='brand'
			left join category c on c.id=d.ref_id and d.module='category'
			left join (
				select p.id,p.name,pd.price,b.name as brand,c.name as category from product p
				left join productdetails pd on pd.productid=p.id
				left join brand b on b.id=p.brandid
				left join category c on c.id=p.categoryid
			) p  on p.id=d.ref_id and d.module='product'
			where d.disid=(params->>'id')::int
		);
		open $3 for
			select ep.*,coalesce(_coupons,'[]'::json) as coupons,coalesce(_mappings,'[]'::json) as mappings from discount ep where ep.id=(params->>'id')::int ;
		return next $3;
	elseif(params->>'operate'='verifycoupon') then
		open $3 for
--			select c.id as couponid,c.couponcode,c.disid,d.amountpercentage,d.amounttype,d."name" as "discountname",c.coupontype from coupon c 
--			inner join discount d on c.disid=d.id
--			inner join dispensary d1 on d.dispensaryid=d1.id
--			where c.couponcode=params->>'code' and to_char((current_timestamp at time zone d1.timezoneoffset), 'YYYY-MM-DD') between to_char((c.fromdate at time zone d1.timezoneoffset), 'YYYY-MM-DD') and to_char((c.todate at time zone d1.timezoneoffset), 'YYYY-MM-DD') and c.isactive=true and case when c.coupontype='CCWL' then c.couponlimit>0 else true end and not exists(select * from usedcoupons where couponid=c.id and customerid=(params->>'custid')::int);
			
			select c.id as couponid,c.couponcode,c.disid,d.amountpercentage,d.amounttype,d."name" as "discountname",c.coupontype,(
				select jsonb_agg(p.id) from product p 
					inner join 
					(
						select t->>'id' as id from json_array_elements((params->>'prodids')::json) t
					) ab 
					on p.id=(ab.id)::int
					left join giftproduct gp on (gp.productid=(ab.id)::int)
					where 
						case when dm."module" ='brand' then p.brandid=dm.ref_id 
						when dm."module"='category' then p.categoryid=dm.ref_id 
						when dm."module"='product' then p.id=dm.ref_id 
						else false 
					end
					and gp.productid is null
			) as products
			from coupon c 
			inner join discount d on c.disid=d.id
			inner join dispensary d1 on d.dispensaryid=d1.id
			inner join discountmapping dm on d.id=dm.disid
			where lower(c.couponcode)=lower(params->>'code') 
--				and to_char((current_timestamp at time zone d1.timezoneoffset), 'YYYY-MM-DD') 
--				between to_char((c.fromdate at time zone d1.timezoneoffset), 'YYYY-MM-DD') 
--				and to_char((c.todate at time zone d1.timezoneoffset), 'YYYY-MM-DD') 
				and to_char((current_timestamp at time zone d1.timezoneoffset), 'YYYY-MM-DD') 
				between to_char(c.fromdate, 'YYYY-MM-DD') 
				and to_char(c.todate, 'YYYY-MM-DD') 
				and c.isactive=true 
				and d.isactive=true
				and to_char((current_timestamp at time zone d1.timezoneoffset), 'YYYY-MM-DD')::date between d.fromdate and d.todate
				and case when (d.ordertype is not null) then d.ordertype=params->>'ordertype' else true end
				and case when d.firstuser=true then ((select count(*) from ordermaster o where o.dispensaryid=(params->>'dispensary')::uuid and o.customerid = (params->>'custid')::bigint limit 2)<1) else true end
				and case when (coalesce(d.device,'')!='') then d.device=coalesce(params->>'device','web') else true end
				and case when c.coupontype='CCWL' then c.couponlimit>0 else true end 
				and case when c.coupontype='PCC' then true else not exists(select * from usedcoupons u inner join coupon cc on cc.id = u.couponid where cc.couponcode=c.couponcode and u.customerid=(params->>'custid')::int) end
				and 
					case when d.amounttype!='price' then
						exists(
							select * from product p 
							inner join 
							(
								select t->>'id' as id from json_array_elements((params->>'prodids')::json) t
							) ab 
							on p.id=(ab.id)::int
							where 
								case when dm."module" ='brand' then p.brandid=dm.ref_id 
								when dm."module"='category' then p.categoryid=dm.ref_id 
								when dm."module"='product' then p.id=dm.ref_id 
								else false 
							end
						)
					 else 
					 	true 
					 end
				group by c.id,c.couponcode,c.disid,d.amountpercentage,d.amounttype,d."name",c.coupontype,products
					;
			
			
		return next $3;
	elseif(params->>'operate'='ageedit') then
		open $3 for
			select jsonb_build_object('id',ag.categoryid,'name',c.name) as category,ag.exclusiveid,json_agg(json_build_object('from',ag.fromday,'to',ag.today,'discount',ag.discount)) as "data",ag.banner,ag.mobilebanner,ag.title,ag.popupbanner,ag.isactive from agediscount ag inner join category c on ag.categoryid=c.id  where ag.commonid=(params->>'id')::int and ag.dispensaryid=(params->>'dispensary')::uuid group by ag.categoryid,c.name,ag.exclusiveid,ag.banner,ag.mobilebanner,ag.title,ag.popupbanner,ag.isactive;  
		return next $3;
	elseif(params->>'operate'='checkduplicate') then
		open $3 for
			select c.couponcode as foundcoupons from coupon c inner join (select m->>'couponcode' as couponcode from json_array_elements((params->>'data')::json) m) t on lower(c.couponcode)=lower(t.couponcode) group by c.couponcode;
		return next $3;
	elseif(params->>'operate'='agegrid') then
		open $3 for
		select a.categoryid,a.commonid,c.name,a.title from agediscount a  
		left join category c on a.categoryid=c.id
		where a.dispensaryid=(params->>'dispensary')::uuid and a.isactive = true
		group by a.categoryid,a.commonid,c.name,a.title; 
		return next $3;
	elseif(params->>'operate'='checkcatexist') then
		open $3 for
			select count(1) as total from agediscount a where categoryid=(params->>'catid')::int and dispensaryid=(params->>'dispensary')::uuid limit 1;
			-- select count(1) as total from agediscount a where categoryid=(params->>'catid')::int limit 1;
		return next $3;
	elseif(sysparams->>'operate'='validate_direct_discount') then
		open $3 for
		
		select p.id,d.amountpercentage as discount,d.name as discount_name,d.amounttype as discount_type,pd.sellingprice,d.id as disid,d1.timezoneoffset
			from product p 
			INNER JOIN  productdetails pd ON p.id=pd.productid 
			inner join dispensary d1 on p.dispensaryid = d1.id 
			INNER JOIN discount d ON p.custdiscountid=d.id
			where  p.id=(params->>'productid')::int and  not exists(select * from coupon where disid=d.id) and d.isactive=true and to_char((current_timestamp at time zone d1.timezoneoffset), 'YYYY-MM-DD')::date between d.fromdate and d.todate
			and case when (d.ordertype is not null) then d.ordertype=params->>'ordertype' else true end
			and case 
				when d.firstuser=true then 
					((select count(*) from ordermaster o where o.dispensaryid=(params->>'dispensary')::uuid and o.customerid = (params->>'custid')::bigint limit 2)<1) 
				else 
					true 
			end
			and case when (coalesce(d.device,'')!='') then d.device=coalesce(params->>'device','web') else true end;

		
		return next $3;
	
	elseif(sysparams->>'operate'='couponlist') then
		open $3 for
			select d.id,d.name as desc,c.couponcode as code,d.amountpercentage,d.amounttype from discount d 
			inner join discountmapping dm on d.id=dm.disid
			inner join coupon c on d.id=c.disid
			inner join dispensary d1 on d1.id = d.dispensaryid 
			where c.coupontype!='IC' and (current_timestamp at time zone d1.timezoneoffset)::date between d.fromdate and d.todate
			and d1.id= (params->>'dispid')::uuid
			and (current_timestamp at time zone d1.timezoneoffset)::date between c.fromdate and c.todate
			and case when (d.ordertype is not null) then d.ordertype=params->>'ordertype' else true end
			and c.isactive=true and d.isactive=true and d.disp_oncheckout=true
			and case when d.firstuser=true then ((select count(*) from ordermaster o where o.dispensaryid=(params->>'dispid')::uuid and o.customerid = (params->>'custid')::bigint limit 2)<1) else true end
			and case when (coalesce(d.device,'')!='') then d.device=coalesce(params->>'device','web') else true end
			and case when c.coupontype='CCWL' then c.couponlimit>0 else true end 
			and case when c.coupontype='PCC' then true else not exists(select * from usedcoupons u inner join coupon cc on cc.id = u.couponid where cc.couponcode=c.couponcode and u.customerid=(params->>'custid')::int) end
				and 
					case when d.amounttype!='price' then
						exists(
							select * from product p 
							inner join 
							(
								select t->>'id' as id from json_array_elements((params->>'prodids')::json) t
							) ab 
							on p.id=(ab.id)::int
							where 
								case when dm."module" ='brand' then p.brandid=dm.ref_id 
								when dm."module"='category' then p.categoryid=dm.ref_id 
								when dm."module"='product' then p.id=dm.ref_id 
								else false 
							end
						)
						when params->>'device'='app' then 
							true -- hide amount discounts (Preroll)
						else 
							true
					end
			  group by d.id,d.name,c.couponcode,d.amountpercentage,d.amounttype
			;
		return next $3;
	
	elseif(params->>'operate'='checkCouponCompulsory') then
		open $3 for
		
			select coupon_discount_applied from dispensary_configs where dispensaryid=(params->>'dispensary')::uuid limit 1;
		
		return next $3;
		
	end if;
	
	-- specify the operation needs to be perform
	
	
	
	END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_dispensary(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
declare
	_points varchar(100);

_dispkiosktimings json;

_dispordertimings json;

_lat point;

_delivery_zones json;
_regular_schedule json;
_nonregular_schedule json;
_current_date date;
_current_time time;
_timezoneoffset varchar(30);
_adv_delivery_time int;
_max_delivery_days varchar(10);

begin _points := concat(params->>'lat', ',', params->>'lng');
-- select func.dispensary({"schema":"sys", "operate":""},{"userid":""}, 'result'); fetch all in result;
 perform public.setschema(sysparams->>'schema');
-- specify the operation needs to be perform
 if (sysparams->>'operate' = 'ddlforrootusers') then open $3 for
select
	id,
	name,
	case
		when coalesce((integration->'pos'->>'name')::text, '')= '' then false
		else true end as linktopos,isvirtual,address1,address2,latlng[1] as lat,latlng[0] as lng
	from
		dispensary
	where
		isactive = true order by name;

/*select u.dispensaryid as userdispensaryid ,dis.id as id,dis.name 
from dispensary dis
inner join users u
on u.dispensaryid = dis.id where u.id =(params->>'userid') :: uuid;*/
--	select id,name from dispensary where isactive=true;
 return next $3;

/*code by chinmay*/
elseif (sysparams->>'operate' = 'ddl') then open $3 for
select
	dis.*,
	case
		when coalesce((integration->'pos'->>'name')::text, '')= '' then false
		else true end as linktopos
	from
		dispensary dis
	inner join users u on
		case
			when u.userdispensarymap is not null
			and (params->>'usertype') :: int not in(1,
			2,
			0)
			and u.userdispensarymap <> '' then (dis.id) :: text in(
			select
				unnest(string_to_array( userdispensarymap, ','))
			from
				users
			where
				id = (params->>'userid') :: uuid)
			else dis.id = u.dispensaryid end
		where
			u.id =(params->>'userid') :: uuid;

/*select d.id,d.name,d.address1 as address,c.cityname,c.statename from dispensary d
			left join sys.view_cities c on c.cityid=d.city 
			where d.isactive=true order by d.name;*/
return next $3;

/*end code by chinmay*/
elseif (sysparams->>'operate' = 'edit') then
select
	array_to_json(array_agg(row_to_json(g)))
into
	_dispkiosktimings
from
	(
	select
		d.id,
		d."day",
		to_char(d.fromtime, 'hh24:mi') as fromtime,
		to_char(d.totime, 'hh24:mi') as totime,
		d.nonkiosk,
		to_char(d.cutofftime, 'hh24:mi') as cutofftime
	from
		dispensarytime d
	where
		dispensaryid =(params->>'disid')::uuid
		and nonkiosk = false
	order by
		d."day")g;

select
	array_to_json(array_agg(row_to_json(h)))
into
	_dispordertimings
from
	(
	select
		d.id,
		d."day",
		to_char(d.fromtime, 'hh24:mi') as fromtime,
		to_char(d.totime, 'hh24:mi') as totime,
		d.nonkiosk,
		to_char(d.cutofftime, 'hh24:mi') as cutofftime
	from
		dispensarytime d
	where
		dispensaryid =(params->>'disid')::uuid
		and nonkiosk = true
	order by
		d."day")h;
	
	select json_agg(json_build_object('id',id,'title',title,'min_order_value',min_order_value,'bgcolor',bgcolor,'polygon',coalesce(public.ST_AsText(polygon), ''))) into _delivery_zones from delivery_zones where dispensaryid =(params->>'disid')::uuid; 
	

open $3 for
select
	d.id,
	d.name,
	d.email,
	d.phone,
	d.city,
	d.state,
	d.country,
	d.banner,
	d.zip,
	d.logo,
	d.address1,
	d.address2,
	d.domain,
	d.createdon,
	d.timezone,
	d.orderPrefix,
	d.isactive,
	_dispkiosktimings as dispensarykiosktiming,
	_dispordertimings as dispensaryordertiming,
	coalesce(public.ST_AsText(d."polygon"), '') as "polygon",
	otherdata as otherdata,
	d.integration as integration,
	coalesce(d.purpose, '{}') as purpose,
	coalesce(d.payment, '[]') as payment,
	orderserial,
	(
	select
		case
			when count(1)= 0 then false
			else true end
		from
			ordermaster
		where
			dispensaryid =(params->>'disid')::uuid
		limit 1) as isorder,
	coalesce (d.latlng[1],
	0) as lat,
	coalesce (d.latlng[0],
	0) as lng,
	coalesce(d.maxradius, 0) as maxradius,
	d.config,
	d.thumbnail,
	d.invdefination,
	coalesce(d.mobilebanner, '') as mobilebanner,
	d.skipinventory,
	coalesce(d.slug, d.name) as slug,
	d.parentid,
	d.deliveryaddresses,
	d.isvirtual,
	d.stronghold_priv_key,
	d.stronghold_pub_key,
	d.terminalid,
	d.stronghold_ext_prefix,
	_delivery_zones as delivery_zones,
	d.adv_delivery_time,
	d.birdeye_location_id,
	d.birdeye_api_key,
	d.email_ad_image,
	d.cspw_api_key,
	d.tinyurl,
	d.pickup_type,
	d.hyper_merchant,
	d.hyper_clientid,
	d.hyper_secret
from
	dispensary d
where
	d.id =(params->>'disid')::uuid;

return next $3;

elseif (sysparams->>'operate' = 'forpanel') then open $3 for
select
	dis.* ,
	case
		when coalesce((integration->'pos'->>'name')::text, '')= '' then false
		else true end as linktopos,
		c.cityname,
		c.statename
	from
		dispensary dis
	inner join users u on
		case
			when u.userdispensarymap is not null
			and (params->>'usertype') :: int not in(1,
			2,
			0)
			and u.userdispensarymap <> '' then (dis.id) :: text in(
			select
				unnest(string_to_array( userdispensarymap, ','))
			from
				users
			where
				id = (params->>'userid') :: uuid)
			else dis.id = u.dispensaryid end
		left join sys.view_cities c on
			c.cityid = dis.city
		where
			u.id =(params->>'userid') :: uuid
			and dis.isactive = true;

/*select d.id,d.name,d.address1 as address,c.cityname,c.statename from dispensary d
			left join sys.view_cities c on c.cityid=d.city 
			where d.isactive=true order by d.name;*/
return next $3;

/* code added by chinmay*/
elseif (sysparams->>'operate' = 'forsudo') then open $3 for
select
	id,
	name,
	case
		when coalesce((integration->'pos'->>'name')::text, '')= '' then false
		else true end as linktopos,
		isvirtual
	from
		dispensary
	where
		isactive = true order by name;

return next $3;

/*end code added by chinmay*/
elseif (sysparams->>'operate' = 'getdispensarybygeo') then open $3 for
select
	d.id,
	d.name,
	d.address1 as address,
	d.banner,
	to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
	to_char(dt.totime, 'HH:MI PM') as "totime",
	case
		when func.help_fun_istimebetween(dt.fromtime,
		dt.totime,
		dt.timezone,
		dt."day") then 'Open'
		else 'Closed' end as status,
		d.otherdata,
		d.integration->'pos'->>'name' as "posname",
		d.country,
			case when d.isvirtual=false then
 				case when ((params->>'postalcode')::character varying = any(d.zip)	or public.ST_Within(public.ST_SetSRID(public.ST_POINT((params->>'lat')::numeric, (params->>'lng')::numeric),26191),polygon) or (func.fn_calculate_distance((params->>'lat')::numeric, (params->>'lng')::numeric, d.latlng[1], d.latlng[0],'M'))::double precision <= d.maxradius ) then 
 					true
				else 
					false 
				end
			else 
				case
				when ((params->>'postalcode')::character varying = any(d.zip)) then 
					true
				when (select count(1) from jsonb_array_elements(d.deliveryaddresses) j where ((func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric, (j->>'lat')::numeric,(j->>'lng')::numeric,'M'))::double precision <= d.maxradius))>0 then 
					true
				else 
					false
				end 
			end
			as issuggested,
			
			
			coalesce(d.phone, '') as phone,
			coalesce(d.otherdata->>'licenceno', '') as licenceno,
			d.integration->'pos'->'locationdata' as locationdata,
			d.thumbnail,
			coalesce(d.mobilebanner, '') as mobilebanner,
			coalesce(d.slug, d.name) as slug,
			d.isvirtual,
			d.parentid,
			(func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric,d.latlng[1],d.latlng[0],'M'))::double precision as distance,
			d.stronghold_pub_key
		from
			dispensary d
		left join dispensarytime dt on
			d.id = dt.dispensaryid
			and dt.day = extract(ISODOW
		from
			func.help_fun_crrenttimestamp(dt.timezone))
			and nonkiosk = true
		where
			d.isactive = true
			and case when params->>'device'='app' then d.isvirtual=false else true end
		order by
			d.name,
			(func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric,d.latlng[1],d.latlng[0],'M'))::double precision asc;
			return next $3;

elseif (sysparams->>'operate' = 'getdispensaryrow') then 
if params->>'type' = 'byvid' and params->>'value'='' then 
open $3 for
	select 'dispensary id is missing' as error;
return next $3;
else
open $3 for
select
	d.id,
	d.name,
	d.address1 as address,
	d.banner,
	to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
	to_char(dt.totime, 'HH:MI PM') as "totime",
	case
		when func.help_fun_istimebetween(dt.fromtime,
		dt.totime,
		dt.timezone,
		dt."day") then 'Open'
		else 'Closed' end as status,
		d.logo,
		d.otherdata,
		coalesce(d.purpose, '{}') as purpose,
		case
			when coalesce(d.address1, '')<> '' then d.address1
			when coalesce(d.address2, '')<> '' then d.address2
			else (d.zip)[1] end as addressformap,
			d.integration->'pos'->>'name' as "posname",
			d.country,
			coalesce(d.phone, '') as phone,
			coalesce(d.otherdata->>'licenceno', '') as licenceno,
			d.thumbnail,
			coalesce(d.mobilebanner, '') as mobilebanner,
			coalesce(d.slug, d.name) as slug,
			d.isvirtual,
			d.parentid,
			d.stronghold_pub_key
		from
			dispensary d
		left join dispensarytime dt on
			d.id = dt.dispensaryid
			and dt.day = extract(ISODOW
		from
			func.help_fun_crrenttimestamp(dt.timezone))
			and nonkiosk = true
		where
			d.isactive = true
			and
			case
				when (params->>'type' = 'byid') then d.id =(params->>'dispensary')::uuid
				when (params->>'type' = 'byvid') then d.id =(params->>'value')::uuid
				else (
				-- lower(d.name)=lower(params->>'value') or 					
 lower(d.slug)= lower(params->>'value')) end
			limit 1 ;

return next $3;
end if;
elseif (sysparams->>'operate' = 'getdispensarybyid') then open $3 for
select
	d.id,
	d.name,
	d.address1 as address,
	d.banner,
	to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
	to_char(dt.totime, 'HH:MI PM') as "totime",
	case
		when func.help_fun_istimebetween(dt.fromtime,
		dt.totime,
		dt.timezone,
		dt."day") then 'Open'
		else 'Closed' end as status,
		d.otherdata,
		country,
		d.thumbnail,
		coalesce(d.mobilebanner, '') as mobilebanner,
		coalesce(d.slug, d.name) as slug,
		d.isvirtual,
		d.parentid,
		d.stronghold_pub_key
	from
		dispensary d
	left join dispensarytime dt on
		d.id = dt.dispensaryid
		and dt.day = extract(ISODOW
	from
		func.help_fun_crrenttimestamp(dt.timezone))
		and nonkiosk = true
	where
		d.id = (params->>'dispid')::uuid
		and d.isactive = true ;

return next $3;

elseif (sysparams->>'operate' = 'getdispensarytimeid') then open $3 for
select
	to_char(dt.fromtime, 'HH:MI AM') as fromtime,
	to_char(dt.totime, 'HH:MI PM') as totime,
	wd."label" as "day",
	case
		when dt."day" = extract(ISODOW
	from
		func.help_fun_crrenttimestamp(timezone)) then true
		else false end as "current",
		case
			when func.help_fun_istimebetween(dt.fromtime,
			dt.totime,
			dt.timezone,
			dt."day") then 'Open'
			else 'Closed' end as status,
			dt.nonkiosk as nonkiosk
		from
			dispensarytime dt
		left join (
			select
				x->>'label' as "label",
				x->>'day' as "day"
			from
				json_array_elements('[{"label":"Monday","day":1},{"label":"Tuesday","day":2},{"label":"Wednesday","day":3},{"label":"Thursday","day":4},{"label":"Friday","day":5},{"label":"Saturday","day":6},{"label":"Sunday","day":7}]') as x) wd on
			dt."day" = wd."day"::int
		left join (
			select
				jsonb_array_elements((integration->'pos'->>'locationdata')::jsonb) as a,
				id
			from
				dispensary
			where
				id = (params->>'id')::uuid) as d on
			dt.dispensaryid = d.id
			and
			case
				when d.a->>'ordertype' = 'del'
				and dt.nonkiosk = true then true
				when d.a->>'ordertype' = 'pas'
				and dt.nonkiosk = false then true
				else false end
			where
				dispensaryid =(params->>'id')::uuid
				and isactive = true
				and d.a->>'ordertype' is not null
			order by
				wd.day;

return next $3;

elseif(sysparams->>'operate' = 'getPayment') then open $3 for
select
	p.location,
	p.code,
	m.name
from
	-- sys.mom m
	mom m
inner join (
	select
		x->>'location' as "location",
		x->>'ordertype' as code
	from
		json_array_elements((select integration->'pos'->'locationdata' as ae from dispensary d where isactive=true and id = (params->>'dispid')::uuid)::json) as x
) as p on
(p.code)::text = (m.code)::text
inner join (
	select l->>'ordertype' as ordertype	from json_array_elements((select otherdata->>'orderdata' as ae from dispensary d where isactive=true and id = (params->>'dispid')::uuid)::json) as l
	where (l->>'enabled')::boolean = true
) o 
on o.ordertype = p.code
where
	m."groups" = 'ordertype' and LOWER(m.code) not in (select lower(unnest(string_to_array(otherdata->>'access_ordertype',','))) from dispensary where isactive=true and id=(params->>'dispid')::uuid and (otherdata->>'cspw_enable')::boolean=false and (otherdata ? 'cspw_api_key' and otherdata->>'cspw_api_key' != ''))
	and case when params->>'device'='app' then p.code!='express' else true end;
-- select payment from dispensary d where id=(params->>'dispid')::uuid limit 1;
 return next $3;

elseif(sysparams->>'operate' = 'getdisplocation') then open $3 for
select
	p.location,
	p.ordertype,
	m.name,
	m.logo,
	0 as min_order_value
from
	-- sys.mom m
	mom m
inner join (
	select
		x->>'location' as "location",
		x->>'ordertype' as ordertype
	from
		json_array_elements((select integration->'pos'->'locationdata' as ae from dispensary d where id = (params->>'dispensary')::uuid)::json) as x
) as p on
(p.ordertype)::text = (m.code)::text
inner join (
	select l->>'ordertype' as ordertype	from json_array_elements((select otherdata->>'orderdata' as ae from dispensary d where isactive=true and id = (params->>'dispensary')::uuid)::json) as l
	where (l->>'enabled')::boolean = true
) o 
on o.ordertype = p.ordertype
where
	m."groups" = 'ordertype' and LOWER(m.code) not in (select lower(unnest(string_to_array(otherdata->>'access_ordertype',','))) from dispensary where isactive=true and id=(params->>'dispensary')::uuid and (otherdata->>'cspw_enable')::boolean=false and (otherdata ? 'cspw_api_key' and otherdata->>'cspw_api_key' != ''))
	and case when params->>'device'='app' then p.ordertype!='express' else true end
order by
	m.name asc;

return next $3;

elseif(sysparams->>'operate' = 'getdispensarylist') then open $3 for

--with categorydata as 
--	(
--		select c.id,c.name,c.dispensaryid,c.logo,coalesce(count(1)) as total from category c 
--		left join product p on c.id=p.categoryid and c.dispensaryid =p.dispensaryid 
--		inner join inventory inv on inv.dispensaryid = p.dispensaryid and p.id=inv.productid
--		where c.isactive =true and p.isactive =true
--		group by c.id,c.name,c.dispensaryid,c.logo, c.catorder
--		order by c.catorder asc
--	),
--	catdata as
--	(
--		select json_agg(jsonb_build_object('catname',categorydata.name,'catlogo',categorydata.logo,
--		'total',categorydata.total)) as categorydetails, categorydata.dispensaryid from categorydata 
--		group by categorydata.dispensaryid
--	)
--	
--select
--	d.id,
--	d.name,
--	d.address1 as address,
--	d.banner,
--	to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
--	to_char(dt.totime, 'HH:MI PM') as "totime",
--	case
--		when func.help_fun_istimebetween(dt.fromtime,
--		dt.totime,
--		dt.timezone,
--		dt."day") then 'Open'
--		else 'Closed' end as status,
--		d.otherdata,
--		d.integration->'pos'->>'name' as "posname",
--		d.country,
--		coalesce(d.phone, '') as phone,
--		coalesce(d.otherdata->>'licenceno', '') as licenceno,
--		d.integration->'pos'->'locationdata' as locationdata,
--		d.thumbnail,
--		coalesce(d.mobilebanner, '') as mobilebanner,
--		coalesce(d.slug, d.name) as slug,
--		d.isvirtual,
--		d.parentid,
--		d.stronghold_pub_key,
--		cd.categorydetails,
--		momtype.purpose_name,
--		dc.total_review,
--		dc.avg_rating,
--		m.name as pickupname
--	from
--		dispensary d
--	left join catdata cd on cd.dispensaryid = case when d.isvirtual=true then d.parentid else d.id end
--	left join dispensary_configs dc on dc.dispensaryid = d.id
--	left join (select d.id,string_agg(m.name,', ') as purpose_name  from dispensary d 
--	left join sys.mom m on  "groups" = 'purposeopt' and m.code::int = any(d.purpose) where d.isactive =true group by d.id) momtype on momtype.id=d.id
--	left join dispensarytime dt on
--		d.id = dt.dispensaryid
--		and dt.day = extract(ISODOW
--	from
--		func.help_fun_crrenttimestamp(dt.timezone))
--		and nonkiosk = true
--	left join mom m on m.code = 'pas'
--	where
--		d.isactive = true and case when params->>'device'='app' then d.isvirtual=false else true end
--	order by
--		d.order asc;

	with _records as (
select p.dispensaryid,c.name,i."location",c.catorder,c.logo,count(1) as total from category c 
inner join product p on c.id = p.categoryid 
inner join inventory i on i.productid  = p.id 
where p.isactive = true
group by p.dispensaryid,c.name,i."location",c.catorder,c.logo
)
, _record1 as
(select 
	d.id,
	d.name,
	d.address1 as address,
	d.banner,
	to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
	to_char(dt.totime, 'HH:MI PM') as "totime",
	case
		when func.help_fun_istimebetween(dt.fromtime,
		dt.totime,
		dt.timezone,
		dt."day") then 'Open'
		else 'Closed' 
	end as status,
		d.otherdata,
		d.integration->'pos'->>'name' as "posname",
		d.country,
		coalesce(d.phone, '') as phone,
		coalesce(d.otherdata->>'licenceno', '') as licenceno,
		d.integration->'pos'->'locationdata' as locationdata,
		d.thumbnail,
		coalesce(d.mobilebanner, '') as mobilebanner,
		coalesce(d.slug, d.name) as slug,
		d.isvirtual,
		d.parentid,
		d.stronghold_pub_key,
		momtype.purpose_name,
		dc.total_review,
		dc.avg_rating,
		m.name as pickupname,
		sum(r.total) as total,
		r.name as catname,
		r.catorder,
		r.logo as catlogo,
		d.order,
		item_object->>'location' as invlocation
from _records r 
inner join dispensary d on r.dispensaryid = case when d.isvirtual then d.parentid else d.id end
left join dispensary_configs dc on dc.dispensaryid = d.id
left join (select d.id,string_agg(m.name,', ') as purpose_name  from dispensary d 
left join sys.mom m on  "groups" = 'purposeopt' and m.code::int = any(d.purpose) where d.isactive =true group by d.id) momtype on momtype.id=d.id
left join dispensarytime dt on d.id = dt.dispensaryid
and dt.day = extract(ISODOW from func.help_fun_crrenttimestamp(dt.timezone))
and nonkiosk = true
left join mom m on m.code = 'pas'
,jsonb_array_elements((d.integration->'pos'->>'locationdata')::jsonb) with ordinality arr(item_object, position)
where 
-- case when d.isvirtual then item_object->>'location'=r.location else true end and 
d.isactive = true and item_object->>'ordertype' = 'pas'
group by d.id,d.name,d.address1,d.banner,dt.fromtime,dt.totime
,dt.timezone,dt."day",d.otherdata,d.integration,d.country,d.phone,d.otherdata,d.thumbnail,
d.mobilebanner,d.slug,d.isvirtual,d.parentid,d.stronghold_pub_key,momtype.purpose_name,dc.total_review,dc.avg_rating,m.name,r.name,r.catorder,r.logo,d.order,item_object->>'location')

select coalesce(d.fromtime,null) as fromtime,coalesce(d.totime, null) as totime,d.id,d.name,d.address,d.banner,d.status,d.otherdata,coalesce(d.posname,'treez') as posname,d.country,d.phone,
d.licenceno,d.locationdata,d.thumbnail,d.mobilebanner,d.slug,d.isvirtual,d.parentid,coalesce(d.stronghold_pub_key,d2.stronghold_pub_key) as stronghold_pub_key,d.purpose_name,d.total_review,
d.avg_rating,d.pickupname,
json_agg(json_build_object('total',(case when exists(select * from inventory where location=d.invlocation and dispensaryid=d.id) then d.total else 0 end),'catlogo',1,'catname',d.catname,'catlogo',d.catlogo) ORDER BY d.catorder) as categorydetails
from _record1 d
inner join dispensary d2 on d2.id = case when d.isvirtual then d.parentid else d.id end
where case when params->>'device'='app' then d.isvirtual=false else true end
group by d.fromtime,d.totime,d.id,d.name,d.address,d.banner,d.status,d.otherdata,d.posname,d.country,d.phone,
d.licenceno,d.locationdata,d.thumbnail,d.mobilebanner,d.slug,d.isvirtual,d.parentid,d.stronghold_pub_key,d.purpose_name,d.total_review,
d.avg_rating,d.pickupname,d2.stronghold_pub_key,d."order"
order by d."order";

return next $3;

elseif(sysparams->>'operate' = 'checkDispOpenClose') then open $3 for
select
	case
		when func.help_fun_istimebetween(dt.fromtime,
		dt.totime,
		dt.timezone,
		dt."day") then ''
		else
		case
			when params->>'ordertype' = 'del' then concat('Your order will be processed next day between ', to_char(dt.fromtime, 'HH:MI AM'), ' - ', to_char(dt.totime, 'HH:MI PM'))
			when params->>'ordertype' = 'pas' then concat('Pick up at store available between ', to_char(dt.fromtime, 'HH:MI AM'), ' - ', to_char(dt.totime, 'HH:MI PM'))
			else '' end end as status
		from
			dispensary d
		left join dispensarytime dt on
			d.id = dt.dispensaryid
			and dt.day = extract(ISODOW
		from
			func.help_fun_crrenttimestamp(dt.timezone))
			and nonkiosk =
			case
				when params->>'ordertype' = 'del' then true
				when params->>'ordertype' = 'pas' then false
				else true end
			where
				d.id = (params->>'dispid')::uuid
				and d.isactive = true
			limit 1;

return next $3;

elseif(sysparams->>'operate' = 'checkCustAddIntoPoly') then
select
	case
		when (params->>'type')::int = 1 then coalesce(latlng, POINT(0, 0))
		when (params->>'type')::int = 2 then coalesce(latlng(altraddres), POINT(0, 0))
		when (params->>'type')::int = 3 then coalesce(latlng(secaddress), POINT(0, 0))
		else POINT(0,
		0) end
into
	_lat
from
	customer
where
	id =(params->>'custid')::int
limit 1;

open $3 for
 /*select
	id
from
	dispensary d
where
	case
		when _lat ~= POINT(0,
		0) then false
		else
		case
			when coalesce(d.maxradius, 0)= 0 then public.ST_Contains(d.polygon,
			public.ST_Transform(public.ST_GeomFromText(concat('POINT(', _lat[1]::varchar, ' ' , _lat[0]::varchar, ')'),
			26191),
			26191))
			else (func.fn_calculate_distance((_lat[1])::double precision ,
			(_lat[0])::double precision ,
			(d.latlng[1])::double precision,
			(d.latlng[0])::double precision,
			'M'))::double precision <= d.maxradius end end
			and id =(params->>'dispid')::uuid;*/
 
	select min_order_value,id as zone_id from delivery_zones where dispensaryid = (params->>'dispid')::uuid
	and public.ST_Contains(polygon,public.ST_Transform(public.ST_GeomFromText(concat('POINT(', _lat[1]::varchar, ' ' , _lat[0]::varchar, ')'),26191),26191)) 
	order by min_order_value asc limit 1;		
		
return next $3;

elseif(sysparams->>'operate' = 'getDispContactDetail') then open $3 for
select
	d.name as dispname,
	concat(coalesce(d.address1, ''), ' ', coalesce(d.address2, '')) as dispaddress,
	coalesce(d.phone, '') as dispphone,
	coalesce(d.email, '') as dispemail,
	coalesce(d.otherdata->>'licenceno', '') as displicenceno,
	coalesce(d.slug, d.name) as slug,
	d.isvirtual,
	d.parentid,
	d.stronghold_pub_key
from
	dispensary d
where
	d.id =(params->>'dispid')::uuid;

return next $3;

elseif (sysparams->>'operate' = 'foruser') then open $3 for
select
	id,
	name,
	false as "checked"
from
	dispensary
where
	isactive = true ;

return next $3;

elseif (sysparams->>'operate' = 'disppostorderdata') then 
if params->>'dispid'='' then 
open $3 for
	select 'dispensary id is missing' as error;
return next $3;
else
 open $3 for
 select
	to_char(dt.cutofftime, 'HH:MI PM') as totime,
	case
		when params->>'ordertype' = 'del' then (d.config->'postordermessages'->>'delivery')::text
		when params->>'ordertype' = 'pas' then (d.config->'postordermessages'->>'pickup')::text
		else '' end as message
	from
		dispensary d
	left join dispensarytime dt on
		d.id = dt.dispensaryid
		and dt.day = extract(ISODOW
	from
		func.help_fun_crrenttimestamp(dt.timezone))
		and nonkiosk =
		case
			when params->>'ordertype' = 'del' then true
			when params->>'ordertype' = 'pas' then false
			else true end
		where
			d.id = (params->>'dispid')::uuid
			and d.isactive = true
		limit 1;

return next $3;
end if;
elseif (sysparams->>'operate' = 'getdispforcheckoutbyid') then open $3 for
select
	concat(coalesce(d.address1, ''), ' ', coalesce(d.address2, '')) as address,
	d.config->>'preordermessage' as warningmsg,
	d.otherdata->>'delpreftime' as timeslots,
	d.config->>'paymentmsg' as paymentmsg,
	d.otherdata as otherdata, -- newly added	
	d.stronghold_pub_key
from
	dispensary d
where
	d.id =(params->>'dispid')::uuid and d.isactive=true
limit 1;

return next $3;

elseif (sysparams->>'operate' = 'getdisplocationgroup') then open $3 for
select
	invdefination
from
	dispensary d
where
	d.id =(params->>'dispensaryid')::uuid and d.isactive=true
limit 1;

return next $3;

elseif (sysparams->>'operate' = 'dispddl') then open $3 for
select
	id,
	name
from
	dispensary
where
	isactive = true
order by
	name;

return next $3;

elseif (sysparams->>'operate' = 'getvirtualaddress') then 
	open $3 for
	/*select jsonb_array_elements_text(deliveryaddresses) as street,vct.countryname as country,vst.code2d as state,vc.cityname as city,'' as apartment,d.zip[1] as zip,'' as apartmnt,d.city as cityid,d.state as stateid,d.country as countryid from 
	dispensary d 
	left join sys.view_countries as vct on d.country=vct.countryid
 	left join sys.view_states as vst on d.state=vst.stateid
 	left join sys.view_cities as vc on d.city=vc.cityid
	where id=(params->>'dispid')::uuid; */
	
	select r.addresslist->>'address' as address,r.addresslist->>'street' as street,r.addresslist->>'country' as country,r.addresslist->>'state' as state,r.addresslist->>'city' as city,r.addresslist->>'apartment' as apartment,r.addresslist->>'zipcode' as zip,r.addresslist->>'apartment' as apartmnt,vc.cityid as cityid,vst.stateid as stateid,vct.countryid as countryid from 
(select jsonb_array_elements(deliveryaddresses) addresslist from dispensary d where id=(params->>'dispid')::uuid) r
left join sys.view_countries as vct on r.addresslist->>'country'=vct.code2d 
left join sys.view_states as vst on r.addresslist->>'state'=vst.code2d and vst.countryid=vct.countryid 
left join sys.view_cities as vc on r.addresslist->>'city'=vc.cityname and vc.stateid=vst.stateid;
	
	return next $3;

elseif (sysparams->>'operate' = 'virtualedit') then

select
	array_to_json(array_agg(row_to_json(g)))
into
	_dispkiosktimings
from
	(
	select
		d.id,
		d."day",
		to_char(d.fromtime, 'hh24:mi') as fromtime,
		to_char(d.totime, 'hh24:mi') as totime,
		d.nonkiosk,
		to_char(d.cutofftime, 'hh24:mi') as cutofftime
	from
		dispensarytime d
	where
		dispensaryid =(params->>'disid')::uuid
		and nonkiosk = false
	order by
		d."day")g;
open $3 for
select
	d.id,
	d.name,
	d.email,
	d.phone,
	d.city,
	d.state,
	d.country,
	d.banner,
	d.zip,
	d.logo,
	d.address1,
	d.address2,
	d.domain,
	d.createdon,
	d.timezone,
	d.orderPrefix,
	d.isactive,
	_dispkiosktimings as dispensarykiosktiming,
	null as dispensaryordertiming,
	coalesce(public.ST_AsText(d."polygon"), '') as "polygon",
	d.otherdata as otherdata,
	d1.integration as integration,
	d.integration as virtualintdata,
	coalesce(d.purpose, '{}') as purpose,
	coalesce(d.payment, '[]') as payment,
	d.orderserial,
	(
	select
		case
			when count(1)= 0 then false
			else true end
		from
			ordermaster
		where
			dispensaryid =(params->>'disid')::uuid
		limit 1) as isorder,
	coalesce (d.latlng[1],
	0) as lat,
	coalesce (d.latlng[0],
	0) as lng,
	coalesce(d.maxradius, 0) as maxradius,
	d.config,
	d.thumbnail,
	d1.invdefination,
	coalesce(d.mobilebanner, '') as mobilebanner,
	d.skipinventory,
	coalesce(d.slug, d.name) as slug,
	d.parentid,
	d.deliveryaddresses,
	d.isvirtual,
	d.stronghold_pub_key,
	d.schedule_order
from
	dispensary d
	inner join dispensary d1 on d.parentid=d1.id
where
	d.id =(params->>'disid')::uuid;

return next $3;
elseif (sysparams->>'operate' = 'parentdispddl') then open $3 for
select
	id,
	name
from
	dispensary
where
	isvirtual=false and isactive = true
order by
	name;

return next $3;
elseif (sysparams->>'operate' = 'getdispensaryvirmsg') then
open $3 for
	select d.config->>'forvirtualmsg' as forvirtualmsg from dispensary d where d.id =(params->>'id')::uuid;
return next $3;

elseif(sysparams->>'operate' = 'loan_delivery_zones') then 
	open $3 for
		
		select 
			d.id as dispid,
			d.name,
			d.latlng[1] as lat,
			d.latlng[0] as lng,
			d.slug,
			json_agg(json_build_object('zone_title',dz.title,'bgcolor',dz.bgcolor,'min_order_value',dz.min_order_value,'polygon',coalesce(public.ST_AsText(dz.polygon), ''))) as zones,
			item_object->>'location' as location,
			item_object->>'ordertype' as ordertype,
			m.name as ordertype_lbl
		from 
		dispensary d 
		INNER JOIN 
		delivery_zones dz on d.id=dz.dispensaryid
		,jsonb_array_elements((d.integration->'pos'->>'locationdata')::jsonb) with ordinality arr(item_object, position) 
		inner join sys.mom m on m.code = item_object->>'ordertype'
		where 
		d.isactive=true and item_object->>'ordertype'='del' and m."groups" = 'ordertype'
		group by d.id,d.name,location,ordertype,ordertype_lbl;
	
	return next $3;

elseif (sysparams->>'operate' = 'load_timeslots') then

select 
	(current_timestamp at time zone timezoneoffset at time zone 'utc')::date,
	(current_timestamp at time zone timezoneoffset at time zone 'utc')::time,
	timezoneoffset,
	adv_delivery_time,
	max_delivery_days 
	into _current_date,_current_time,_timezoneoffset,_adv_delivery_time,_max_delivery_days 
	from dispensary 
	where id=(params->>'dispid')::uuid;

	_current_time := _current_time + (_adv_delivery_time ||' minutes')::interval;
	_current_date := ((current_timestamp at time zone _timezoneoffset at time zone 'utc') + (_adv_delivery_time ||' minutes')::interval)::date;
	open $3 for

	with _daylist as
	(
		select (generate_series(_current_date, (_current_date + (_max_delivery_days||' day')::interval), '1 day'::interval))::date as date_lbl
	),
	_timelist as
	(
		select d.date_lbl, case when rs.available=false then false when rs2.dayid=null then false else true end as available,
		case when rs.id is not null then rs.id  else rs2.id end as id,
		case when rs.id is not null then rs.fromtime  else rs2.fromtime end as fromtime,
		case when rs.id is not null then rs.totime else rs2.totime end as totime,
		case when rs.id is not null then rs.charge else rs2.charge end as charge,
		case when rs.id is not null then rs.order_limit else rs2.order_limit end as order_limit,
		case when rs.id is not null then rs.zone_id else rs2.zone_id end as zone_id,
		(params->>'dispid')::uuid as dispensaryid
		from _daylist d 
		left join (select * from nonregular_schedule where dispensaryid = (params->>'dispid')::uuid and zone_id=(params->>'zid')::int) rs on rs.date=d.date_lbl
		left join (select * from regular_schedule where dispensaryid = (params->>'dispid')::uuid and zone_id=(params->>'zid')::int) rs2 on rs2.dayid =  extract(isodow from d.date_lbl)
		where case when rs.id is not null then rs.id  else rs2.id end is not null
	)
	,
	_recordgroup as (
		select date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid from _timelist group by date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid
	)
	,
	_orderrecord as (
		select 
			t.date_lbl,
			t.available,
			t.id,
			t.fromtime,
			t.totime,
			t.charge,
			t.order_limit, 
			t.zone_id,
			sum(
				case 
					when o.id is not null 
						   -- and (o.schedule_datetime)::time=t.fromtime 
						and ((o.schedule_datetime)::time>=t.fromtime and (o.schedule_datetime)::time<=t.totime )
					then 1 else 0 end
				) as total 
		from _recordgroup t 
		left join ordermaster o on (o.schedule_datetime)::date = t.date_lbl and o.dispensaryid = t.dispensaryid
		group by t.date_lbl,t.available,t.id,t.fromtime,t.totime,t.charge,t.order_limit,t.zone_id
		order by t.date_lbl,t.fromtime
	)
	
	select d.date_lbl,t.available,
	case when t.available then 
		json_agg(
			json_build_object('id',t.id,'fromtime',t.fromtime,'totime',t.totime,'available',t.available,'charge',t.charge,'order_limit',t.order_limit,'zone_id',t.zone_id,'active',case when (t.date_lbl=_current_date and t.totime<_current_time) then false when (t.order_limit<=t.total) then false else true end)
			ORDER BY t.fromtime
		) 
	else 
		null 
	end 
	as data,_current_date,_current_time
	from _daylist d
	left join _orderrecord t on d.date_lbl=t.date_lbl
	where t.available=true 
	and 
			case 
				when (_current_date=(t.date_lbl)) then 
					_current_time<t.totime 
				else 
					true 
			end
	group by d.date_lbl,t.available,_current_date,_current_time
	order by d.date_lbl;

return next $3;

elseif (sysparams->>'operate' = 'validate_timeslot') then

select (current_timestamp at time zone timezoneoffset at time zone 'utc')::date,(current_timestamp at time zone timezoneoffset at time zone 'utc')::time,timezoneoffset,adv_delivery_time into _current_date,_current_time,_timezoneoffset,_adv_delivery_time from dispensary where id=(params->>'dispid')::uuid;
-- _current_date := current_date;
-- _current_time := current_time;
_current_time := _current_time + (_adv_delivery_time ||' minutes')::interval;
_current_date := ((current_timestamp at time zone _timezoneoffset at time zone 'utc') + (_adv_delivery_time ||' minutes')::interval)::date;
open $3 for

	with 
	_timeslots as
	(
		select d.date_lbl, case when rs.available=false then false when rs2.dayid=null then false else true end as available,
		case when rs.id is not null then rs.id  else rs2.id end as id,
		case when rs.id is not null then rs.fromtime  else rs2.fromtime end as _fromtime,
		case when rs.id is not null then rs.totime else rs2.totime end as _totime,
		case when rs.id is not null then rs.charge else rs2.charge end as charge,
		case when rs.id is not null then rs.order_limit else rs2.order_limit end as order_limit
		from (select (params->'timeslot'->>'date')::date as date_lbl) d 
		left join (select * from nonregular_schedule where dispensaryid = (params->>'dispid')::uuid and zone_id=(params->'timeslot'->>'zid')::int) rs on rs.date=d.date_lbl
		left join (select * from regular_schedule where dispensaryid = (params->>'dispid')::uuid and zone_id=(params->'timeslot'->>'zid')::int) rs2 on rs2.dayid =  extract(isodow from d.date_lbl)
		where case when rs.id is not null then rs.id  else rs2.id end is not null
	),
	_recordgroup as (
		select date_lbl,available,id,_fromtime,_totime,charge,order_limit from _timeslots group by date_lbl,available,id,_fromtime,_totime,charge,order_limit
	)
	
	select 
		t.date_lbl,
		t.order_limit,
		t.charge,
		(concat(t.date_lbl,' ',t._fromtime)::timestamp at time zone _timezoneoffset at time zone 'pdt') as scheduled_timestamp,
		concat(t.date_lbl,' ',t._fromtime) as scheduled_date,
		sum(case when tmp.id is null then 0 else 1 end) as total 
	from _recordgroup t 
	left join 
	(
		select id,(schedule_datetime)::date as ord_date,(schedule_datetime)::time as ord_time from ordermaster 
		where dispensaryid = (params->>'dispid')::uuid and (schedule_datetime)::date=(params->'timeslot'->>'date')::date
	) tmp
	on ord_date=t.date_lbl and (tmp.ord_time>=t._fromtime and tmp.ord_time<=t._totime)
	where 
		t._fromtime=(params->'timeslot'->>'fromtime')::time 
		and t._totime=(params->'timeslot'->>'totime')::time
		and 
			case 
				when (_current_date=(params->'timeslot'->>'date')::date) then 
					_current_time<t._totime 
				else 
					true 
			end
		and t.available=true
	group by t.date_lbl,t.order_limit,t.charge,scheduled_timestamp,scheduled_date;

return next $3;


elseif (sysparams->>'operate' = 'getzones') then

open $3 for

select 
id,title,bgcolor,coalesce(public.ST_AsText("polygon"), '') as "polygon"
from zones where dispensaryid = (params->>'dispensary')::uuid;

return next $3;
elseif (sysparams->>'operate' = 'zonereport') then 

open $3 for

select concat(c.fname,' ',c.lname) as customer_name, c.email, c.mobile, 
concat(apartmnt(o.customeraddress),' ',street(o.customeraddress),' ',vc.cityname ,' ',vc.statename,' ',zip(o.customeraddress)) as address,
concat(d.orderprefix,'-',o.id) as ecom_orderid
, o.posordernum,
to_char(o.createdon at time zone d.timezoneoffset, 'MM-DD-YYYY HH:MI:SS') as orderdate,
to_char(o.statusupdatedate at time zone d.timezoneoffset, 'MM-DD-YYYY HH:MI:SS') as statuschangeddate,
m.name as orderstatus,
o.total,
z.title as zonetitle,
case when z.polygon is not null then 'Yes' else 'No' end as status
from (select * from ordermaster o where coalesce((latlng(o.customeraddress))[1]::varchar,'')!='') o
left join zones z 
on 
public.ST_Contains(z.polygon,public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (latlng(o.customeraddress))[1]::varchar, ' ' , (latlng(o.customeraddress))[0]::varchar, ')'),26191),26191))
and z.dispensaryid = o.dispensaryid
inner join dispensary d on d.id=o.dispensaryid
inner join customer c on c.id = o.customerid
left join sys.view_cities vc on vc.cityid = (city(o.customeraddress))::int
left join sys.mom m on m.code=o.status
where 
o.ordertype='DELIVERY' 
and o.dispensaryid = (params->>'dispensary')::uuid
and (o.statusupdatedate at time zone d.timezoneoffset)::date between (params->>'fromdate')::date and (params->>'todate')::date
and case when (coalesce(params->>'status','')!='') then o.status = params->>'status' else true end
and 
	case when (params->>'ddlype'='') then 
			 true 
		 when (params->>'ddlype'='Yes') then 
		 	 z.polygon is not null
		 when (params->>'ddlype'='No') then 
		 	z.polygon is null
		 else 
		 	false
	end
order by o.statusupdatedate desc;

return next $3;

elseif (sysparams->>'operate' = 'loadscheduletimes') then

with _records as
	(	
		select dayid,json_agg(json_build_object('id',id,'fromtime',fromtime,'totime',totime,'charge',charge,'order_limit',order_limit) ORDER BY fromtime) as data from regular_schedule where dispensaryid =(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int group by dayid
	)
	select json_agg(json_build_object('dayid',r.dayid,'data',r.data)) into _regular_schedule from _records r;

	with _records1 as
	(	
		select date,available,json_agg(json_build_object('id',id,'fromtime',fromtime,'totime',totime,'charge',charge,'order_limit',order_limit) ORDER BY fromtime) as data from nonregular_schedule where dispensaryid =(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int group by date,available order by date
	)
	select json_agg(json_build_object('date',r.date,'available',r.available,'data',r.data)) into _nonregular_schedule from _records1 r;

open $3 for

select _regular_schedule as regular_dates, _nonregular_schedule as exception_dates;

return next $3;

elseif (sysparams->>'operate' = 'delivery_zones') then
	open $3 for
	select id,title from delivery_zones where dispensaryid =(params->>'disid')::uuid order by title asc;
	return next $3;
elseif (sysparams->>'operate' = 'dispensary_ddl') then
	open $3 for
	select id,name from dispensary where isactive=true and isvirtual=false order by name asc;
	return next $3;

elseif (sysparams->>'operate' = 'load_timeslot_report') then

	open $3 for

	with _daylist as
	(
		select (generate_series((params->>'fromdate')::date, (params->>'todate')::date, '1 day'::interval))::date as date_lbl
	),
	_timelist as
	(
		select d.date_lbl, case when rs.available=false then false when rs2.dayid=null then false else true end as available,
		case when rs.id is not null then rs.id  else rs2.id end as id,
		case when rs.id is not null then rs.fromtime  else rs2.fromtime end as fromtime,
		case when rs.id is not null then rs.totime else rs2.totime end as totime,
		case when rs.id is not null then rs.charge else rs2.charge end as charge,
		case when rs.id is not null then rs.order_limit else rs2.order_limit end as order_limit,
		case when rs.id is not null then rs.zone_id else rs2.zone_id end as zone_id,
		d1.id as dispensaryid,
		d1.timezoneoffset, 
		dz.polygon
		from _daylist d 
		left join (select * from nonregular_schedule where dispensaryid = (params->>'disid')::uuid and zone_id=(params->>'zid')::int) rs on rs.date=d.date_lbl
		left join (select * from regular_schedule where dispensaryid = (params->>'disid')::uuid and zone_id=(params->>'zid')::int) rs2 on rs2.dayid =  extract(isodow from d.date_lbl)
		left join dispensary d1 on d1.id=case when rs.id is not null then rs.dispensaryid else rs2.dispensaryid end
		left join delivery_zones dz on d1.id=dz.dispensaryid
		where case when rs.id is not null then rs.id  else rs2.id end is not null
		and case when (params->>'days'!='') then (extract(isodow from d.date_lbl))::text = ANY (string_to_array(params->>'days',',')) else true end
	)
	,
	_recordgroup as (
			select date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid,polygon from _timelist 
			group by date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid,polygon
		)
		,_orderrecord as (
			select 
			t.id,
			t.dispensaryid,
			t.date_lbl,
			t.available,
			t.fromtime,
			t.totime,
			t.charge,
			t.order_limit, 
			t.zone_id,
			sum(
					case 
						when o.id is not null and ((o.schedule_datetime)::time>=t.fromtime and (o.schedule_datetime)::time<t.totime )
						then 1 else 0 
					end
				) as total 
			from _recordgroup t 
			left join (select id,schedule_datetime,customeraddress,dispensaryid from ordermaster o where coalesce((latlng(o.customeraddress))[1]::varchar,'')!='') o 
			on o.dispensaryid = t.dispensaryid and date(o.schedule_datetime)=t.date_lbl
			and case when (coalesce((latlng(o.customeraddress))[1]::varchar,'')!='') then public.ST_Contains(t.polygon,public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (latlng(o.customeraddress))[1]::varchar, ' ' , (latlng(o.customeraddress))[0]::varchar, ')'),26191),26191)) else false end	
			group by t.id,t.dispensaryid,t.date_lbl,t.available,t.fromtime,t.totime,t.charge,t.order_limit,t.zone_id
			order by t.date_lbl,t.fromtime
		)
	
	select to_char(d.date_lbl, 'Day') as day,d.date_lbl as date,
	jsonb_agg(jsonb_build_object('id',t.id,'fromtime',t.fromtime,'totime',t.totime,'available',t.available,'total',t.order_limit,'total_orders',t.total) order by t.fromtime) 
	as data,t.dispensaryid,t.zone_id
	from _daylist d
	left join _orderrecord t on d.date_lbl=t.date_lbl
	where t.available=true and t.zone_id = (params->>'zid')::int
	and d.date_lbl >=current_date
	group by d.date_lbl,t.dispensaryid,t.zone_id
	
	UNION

	select day,date,data::jsonb,dispensary_id,zone_id from timeslot_report d
	where 
	d."date" between (params->>'fromdate')::date and (params->>'todate')::date
	and d.zone_id = (params->>'zid')::int
	and d.dispensary_id = (params->>'disid')::uuid
	and case when (params->>'days'!='') then (extract(isodow from d.date))::text = ANY (string_to_array(params->>'days',',')) else true end
    and d.date < current_date
	order by date;

	return next $3;
	
	elseif (sysparams->>'operate' = 'load_revenue_report') then
	open $3 for
	
	select case when params->>'reporttype' = 'detail' then o.createdon::date else null end as date,d."name" as dispensary_name,o.device, ordertype,count(1) as total_order,sum(total) as total_revenue 
	from ordermaster o
	inner join dispensary d on o.dispensaryid  = d.id
	where case when (params->>'device'='') then true else o.device=params->>'device' end
	and o.createdon::date between (params->>'fromdate')::date and (params->>'todate')::date
	-- and case when params->>'status'='' then true else o.status=params->>'status' end 
	and case when params->>'dispid'='' then true else d.id = (params->>'dispid')::uuid end
	group by date,o.ordertype,dispensary_name,o.device
	order by date,dispensary_name,ordertype;
	return next $3;


elseif (sysparams->>'operate' = 'getdispensarybyloc') then open $3 for
	select distinct
		d.id,
		d.name,
		d.address1 as address,
		d.banner,
		d.latlng[1] as lat,
		d.latlng[0] as lng,
		to_char(dt.fromtime, 'HH:MI AM') as "fromtime",
		to_char(dt.totime, 'HH:MI PM') as "totime",
		case when func.help_fun_istimebetween(dt.fromtime,dt.totime,dt.timezone,dt."day") then 'Open' else 'Closed' end as status,
		d.otherdata,
		d.integration->'pos'->>'name' as "posname",
		d.country,
		case when d.isvirtual=false then
 			case when ((params->>'postalcode')::character varying = any(d.zip) or public.ST_Within(public.ST_SetSRID(public.ST_POINT((params->>'lat')::numeric, (params->>'lng')::numeric),26191),d.polygon) or (func.fn_calculate_distance((params->>'lat')::numeric, (params->>'lng')::numeric, d.latlng[1], d.latlng[0],'M'))::double precision <= d.maxradius ) then true
			else false 
			end
		else 
			case when ((params->>'postalcode')::character varying = any(d.zip)) then true
			when (select count(1) from jsonb_array_elements(d.deliveryaddresses) j where ((func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric, (j->>'lat')::numeric,(j->>'lng')::numeric,'M'))::double precision <= d.maxradius))>0 then true
			else false
			end 
		end as issuggested,
		coalesce(d.phone, '') as phone,
		coalesce(d.otherdata->>'licenceno', '') as licenceno,
		d.integration->'pos'->'locationdata' as locationdata,
		d.thumbnail,
		coalesce(d.mobilebanner, '') as mobilebanner,
		coalesce(d.slug, d.name) as slug,
		d.isvirtual,
		d.parentid,
		public.ST_Distance(dz.polygon, public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (params->>'lat')::varchar, ' ' , (params->>'lng')::varchar, ')'),26191),26191)) as distance,
		-- (func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric,d.latlng[1],d.latlng[0],'M'))::double precision as distance,
		d.stronghold_pub_key
	from dispensary d
	left join dispensarytime dt on (d.id = dt.dispensaryid and dt.day = extract(DOW from func.help_fun_crrenttimestamp(dt.timezone)) and nonkiosk = true)
	left join delivery_zones dz on (d.id = dz.dispensaryid)
	where d.isactive = true 
	and exists(select * from jsonb_array_elements((d.integration->'pos'->>'locationdata')::jsonb) as a where a->>'ordertype'='del')
	and exists(select * from jsonb_array_elements((d.otherdata->>'orderdata')::jsonb) as a where a->>'ordertype'='del' and (a->>'enabled')::boolean=true)
	and public.ST_Contains(dz.polygon, public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (params->>'lat')::varchar, ' ' , (params->>'lng')::varchar, ')'),26191),26191) )
	order by
		-- (func.fn_calculate_distance((params->>'lat')::numeric,(params->>'lng')::numeric,d.latlng[1],d.latlng[0],'M'))::double precision 
		(public.ST_Distance(dz.polygon, public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (params->>'lat')::varchar, ' ' , (params->>'lng')::varchar, ')'),26191),26191)))::double precision, 
		d.name asc;
return next $3;

elseif (sysparams->>'operate' = 'getrewards') then
	open $3 for
	select rewardspoints from integratedcustomer where custid=(params->>'custid')::int and 
	dispensaryid = (params->>'dispensary')::uuid and isactive=true;
	return next $3;

elseif (sysparams->>'operate' = 'general') then

	open $3 for
	
	select email_technical,email_nontechnical,new_arrival_days as arrival from dispensary_configs where 
	dispensaryid = (params->>'dispensary')::uuid;
	
	return next $3;

elseif (sysparams->>'operate' = 'virtual_schedule_time') then

	open $3 for
	
	select 
	
	case when otherdata->>'schedule_datetime' is not null then ((otherdata->>'schedule_datetime')::timestamp at time zone timezoneoffset at time zone 'pdt') else null end as scheduled_timestamp,
	otherdata->>'schedule_datetime' as scheduled_datetime	
	from dispensary where 
	id = (params->>'dispensary')::uuid;
	
	return next $3;


end if;

end;

$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_exclusiveproduct(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
declare _proddetailid int;
declare _curdate date;
declare _timezone varchar(50);
	begin
		
	-- select func.fn_brand({"schema":"sys", "operate":""},{"userid":""}, 'result'); fetch all in result;
	
	perform public.setschema(sysparams->>'schema');
		
	if(sysparams->>'operate'='autocompleteproduct') then 
	
	
		open $3 for
		select p.id,p.name,p.dispensaryid,pd.price,pd.isactive,cat.name as categoryname,cat.id as categoryid,b.name as brand  from product p left join
	productdetails pd on p.id=pd.productid left join  category cat on p.categoryid = cat.id left join brand b on b.id=p.brandid where lower(p.name) like CONCAT('%',lower(params->>'keyword'),'%') and p.isactive=true and p.dispensaryid=(select case when isvirtual=true then parentid else id end from dispensary where id = (params->>'dispensary')::uuid)  order by name limit 20;
		return next $3;
	elseif(sysparams->>'operate' = 'subcategoryddl') then
			open $3 for
			select id,name from subcategory where dispensaryid= (params->>'dispensary')::uuid and  parentid=(params->>'parentid')::int and isactive=true;
		return next $3;
	
	elseif(sysparams->>'operate'='productbycategory') then 
			open $3 for
			-- select json_array_elements(( params->>'subcategorylist'):: json);
			
	select p.id,p.name,pd.price,pd.isactive,cat.name as categoryname,cat.id as categoryid ,b.name as brand from product p
left join productdetails pd on p.id=pd.productid
left join category cat on p.categoryid = cat.id 
left join brand b on b.id=p.brandid where p.dispensaryid=(select case when isvirtual=true then parentid else id end from dispensary where id = (params->>'dispensary')::uuid) and
case when 
(params->>'option') :: int = 0  then
p.categoryid = (params->>'category')::int
else 
 (p.subcatid) :: text in(select json_array_elements_text(( params->>'subcategorylist'):: json)) end;
	return next $3;
	
elseif(params->>'operate' = 'getexclusiveproductmasteredit') then
open $3 for
select ep.* from exclusiveproductmaster ep where ep.id=(params->>'id')::int ;

return next $3;
elseif(params->>'operate'='getexclusiveproddetailedit') then
open $3 for
select ep.prodid as id , p.name,pd.price,pd.isactive, cat.id as categoryid,cat.name as categoryname , b.name as brand from exclusiveproddetail ep 
left join product p on ep.prodid= p.id left join productdetails pd
on ep.prodid = pd.productid left join category  cat on p.categoryid = cat.id  left join brand b on b.id=p.brandid
where ep.exmstid=(params->>'id')::int and ep.isactive = true order by  p.name  ;
-- 

return next $3;
elseif(params->>'operate'='getexclubanner') then 
select coalesce(d.timezoneoffset,'') into _timezone from dispensary d where d.id=case when (params->>'isvirtual')::boolean then (params->>'parentid')::uuid else (params->>'dispensary')::uuid end limit 1;
_curdate := to_char(CURRENT_DATE at time zone _timezone,'yyyy-mm-dd')::date; 
-- create temporary table temptable as select params;
open $3 for


--select id,name,_curdate as currdate ,tagline ,tagline1 ,description ,amounttype ,amountpercentage ,dispensaryid as dispid,(banners->>'images')::json as images from exclusiveproductmaster e where e.dispensaryid = (params->>'dispensary')::uuid and (current_timestamp at time zone _timezone) between (e.from_date at time zone _timezone) and (e."to_date" at time zone _timezone) order by name desc;

select id,name,_curdate as currdate ,tagline ,tagline1 ,description ,amounttype ,amountpercentage ,dispensaryid as dispid,(banners->>'images')::json as images from exclusiveproductmaster e where e.dispensaryid = (params->>'dispensary')::uuid and to_char((current_timestamp at time zone _timezone), 'YYYY-MM-DD') between to_char((e.from_date at time zone _timezone), 'YYYY-MM-DD') and to_char((e."to_date" at time zone _timezone), 'YYYY-MM-DD') and e.isdelete=false 
union all
 
select d.id,concat('ndiscount_',d.id) as name,null as currdate,null as tagline,null as tagline1,null as description,d.amounttype,d.amountpercentage,d.dispensaryid as dispid,json_agg(json_build_object('big',d.banner,'small',d.mobilebanner)) as images from discount d where d.dispensaryid = case when (params->>'isvirtual')::boolean then (params->>'parentid')::uuid else (params->>'dispensary')::uuid end and d.isactive=true and coalesce(d.banner,'')!=''
and not exists (select * from coupon where disid=d.id and coupontype='IC' and isactive=true limit 1)
and true = case when not exists (select 1 from coupon where disid=d.id limit 1) then true else d.displaycoupondiscount end 
and case 
when d.criteria->>'code'='5' then
	to_char((current_timestamp at time zone _timezone), 'YYYY-MM-DD') between 
	to_char(((d.criteria->'data'->>'fromdate')::date at time zone _timezone), 'YYYY-MM-DD') and 
	to_char(((d.criteria->'data'->>'todate')::date at time zone _timezone), 'YYYY-MM-DD')
else
true
end 
and case when (coalesce(d.device,'')!='') then d.device=coalesce(params->>'device','') else true end
group by d.id,d.name

union all
select null as id,concat('Special Discount_',agt.commonid) as name,null as currdate,null as tagline,null as tagline1,null as description,null as amounttype,null as amountpercentage,agt.dispensaryid as dispid,json_agg(json_build_object('big',agt.banner,'small',agt.mobilebanner)) as images from
(select ad.commonid,ad.banner,ad.mobilebanner,ad.dispensaryid,max(case when DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today then ad.discount else 0 end) as discount from inventory i inner join product p on p.id=i.productid inner join agediscount ad on ad.categoryid=p.categoryid and ad.dispensaryid=i.dispensaryid INNER JOIN dispensary d ON d.id=ad.dispensaryid where i.invdate is not null and i.dispensaryid=case when (params->>'isvirtual')::boolean then (params->>'parentid')::uuid else (params->>'dispensary')::uuid end and DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today and ad.isactive=true and coalesce(ad.banner,'')!='' group by ad.commonid,ad.banner,ad.mobilebanner,ad.dispensaryid) agt group by agt.commonid,agt.dispensaryid

order by name desc;


return next $3;
elseif(params->>'operate'='branddl') then

open $3 for
	select  id,"name" as brand from brand where dispensaryid=(select case when isvirtual=true then parentid else id end from dispensary where id = (params->>'dispensary')::uuid) order by name asc;
return next $3;


elseif(params->>'operate'='getproductbybrand') then
 
open $3 for
	select p.id,p.name,pd.price,pd.isactive,cat.name as categoryname,cat.id as categoryid ,b.name as brand  from product p
 
left join productdetails pd on p.id=pd.productid
left join category cat on p.categoryid = cat.id
left join brand b on b.id=p.brandid
where p.dispensaryid=(select case when isvirtual=true then parentid else id end from dispensary where id = (params->>'dispensary')::uuid) and p.brandid =(params->>'brand')::int and pd.price <> 0;
-- and p.categoryid in (select id from category where name in ('FLOWER','EDIBLE','CARTRIDGE','EXTRACT') and dispensaryid = (params->>'dispensary')::uuid);
return next $3;

elseif(params->>'operate'='ddl') then
	open $3 for
		select id,name from exclusiveproductmaster e where isactive=true order by name asc;
	return next $3;
end if;
	
	-- specify the operation needs to be perform
	
	
	
	END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_getemailtemplate(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
	DECLARE _to text;
	DECLARE _cc text;
	DECLARE _bcc text;
	DECLARE _templateid int;
	DECLARE _template text;
	DECLARE _subject text; 
	DECLARE _smtpjson json;
	DECLARE _data json;
	DECLARE _expData json;

	begin
		
	perform public.setschema(sysparams->>'schema');

		select cc,bcc,templateid into _cc,_bcc,_templateid  from emailtemplatemapping where module =  params->>'module' and submodule = params->>'submodule' and isactive = true limit 1;
	
		select template, description  into _template, _subject from emailsettings where id = _templateid limit 1;
	
if(params->>'module' = 'order') then
	select jsonb_build_object('orderid',o.id,'orderdate',to_char(o.createdon, 'DD-MM-YYYY HH:MI:SS'),'user',concat(c.fname,' ',c.lname),'total',o.total,'status',m.name,'remark',o.remark,'to',o.email,'discount',params->'data'->>'discount','taxes',params->'data'->>'taxes','delivery_fee',params->'data'->>'delivery_fee','order_total',params->'data'->>'order_total','orderno',params->'data'->>'orderno','address',params->'data'->>'address','items',params->'data'->>'items','subtotal',params->'data'->>'subtotal','name',params->'data'->>'name','logo',params->'data'->>'logo','licenceno',params->'data'->>'licenceno','dispaddress',params->'data'->>'dispaddress','phone',params->'data'->>'phone','email',params->'data'->>'email','ordertype',params->'data'->>'ordertype','cspwvisible',params->'data'->>'cspwvisible','otwpath',params->'data'->>'otwpath','arrivedpath',params->'data'->>'arrivedpath','ordertype_label',params->'data'->>'ordertype_label') into _expData  FROM ordermaster o
            left join customer c on c.id=o.customerid left join mom m on m.code=(o.status)::character varying and m.groups='orderstatus' where o.dispensaryid=(params->'data'->>'dispensary')::uuid and o.id=(params->'data'->>'orderid')::int;

elsif(params->>'module' = 'user' and params->>'submodule'='signup') then
	select jsonb_build_object('link',params->'data'->>'link','user',c.fname,'to',c.email,'team',params->'data'->>'team','themecolor',params->'data'->>'themecolor') into _expData from customer c where id=(params->'data'->>'id')::int limit 1;
elsif(params->>'module' = 'user' and params->>'submodule'='forget') then
	select jsonb_build_object('link',params->'data'->>'link','user',c.fname,'to',c.email,'team',params->'data'->>'team','themecolor',params->'data'->>'themecolor') into _expData from customer c where id=(params->'data'->>'id')::int limit 1;
elsif(params->>'module' = 'order' and params->>'submodule'='order_placed') then
	select jsonb_build_object('orderid',o.id,'orderdate',to_char(o.createdon, 'DD-MM-YYYY HH:MI:SS'),'user',concat(c.fname,' ',c.lname),'total',o.total,'status',m.name,'remark',o.remark,'to',o.email) into _expData  FROM ordermaster o left join customer c on c.id=o.customerid left join mom m on m.code=(o.status)::character varying and m.groups='orderstatus' where o.dispensaryid=(params->'data'->>'dispensary')::uuid and o.id=(params->'data'->>'orderid')::int;

elsif(params->>'module' = 'user' and params->>'submodule'='watchlist') then
	select jsonb_build_object('itemlist',params->'data'->>'itemlist','custname',params->'data'->>'custname','to',params->'data'->>'to','notifypara',params->'data'->>'notifypara') into _expData;
elsif(params->>'module' = 'user' and params->>'submodule'='customer') then
	select jsonb_build_object('link',params->'data'->>'link','user',c.fname,'to',c.email,'team',params->'data'->>'team','storename',params->'data'->>'storename','mobile',params->'data'->>'mobile','address',params->'data'->>'address','otherlink',params->'data'->>'otherlink') into _expData from customer c where id=(params->'data'->>'id')::int limit 1;
elsif(params->>'module' = 'user' and params->>'submodule'='deactive_account') then
	select jsonb_build_object('deactive_link',params->'data'->>'deactive_link','user',params->'data'->>'user','to',params->'data'->>'to','team',params->'data'->>'team','change_pwd_link',params->'data'->>'change_pwd_link') into _expData;
elsif(params->>'module' = 'user' and params->>'submodule'='otp') then
	select jsonb_build_object('name',params->'data'->>'name','to',params->'data'->>'email','otp',params->'data'->>'otp','team',params->'data'->>'team') into _expData;
elsif(params->>'module' = 'cart_abandon') then
	select jsonb_build_object('name',params->'data'->>'name','to',params->'data'->>'custemail','products',params->'data'->>'products','email',params->'data'->>'email','total',params->'data'->>'total','dispname',params->'data'->>'dispname') into _expData;
elsif(params->>'module' = 'gift' and params->>'submodule'='gift_card') then
	select jsonb_build_object('custname',params->'data'->>'custname','cardno',params->'data'->>'cardno') into _expData;
elsif(params->>'module' = 'user' and params->>'submodule'='SUPPORT') then
	select jsonb_build_object('name',params->'data'->>'name','to',params->'data'->>'supportemail','email',params->'data'->>'email','issue_name',params->'data'->>'issue_name','disp_name',params->'data'->>'disp_name','download',params->'data'->>'download','message',params->'data'->>'message','subject',params->'data'->>'subject') into _expData;
elsif(params->>'module' = 'event_order' and params->>'submodule'='event_order_placed') then
	select jsonb_build_object('orderid',o.id,'orderdate',to_char(o.createdon, 'DD-MM-YYYY HH:MI:SS'),
'user',concat(c.fname,' ',c.lname),'total',o.total,'remark',o.remark,'to',o.email,
'discount',params->'data'->>'discount','taxes',params->'data'->>'taxes',
'delivery_fee',params->'data'->>'delivery_fee','order_total',params->'data'->>'order_total',
'orderno',params->'data'->>'orderno','items',params->'data'->>'items',
'subtotal',params->'data'->>'subtotal',
'ordertype',params->'data'->>'ordertype') into _expData  FROM event_ordermaster o 
left join customer c on c.id=o.customerid where o.id=(params->'data'->>'orderid')::int;
end if;
open $3 for
select _cc as cc,_bcc as bcc,_template as template,_expData as expdata,_subject as subject;
return next $3;
	END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_order(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
	begin
	
	perform public.setschema(sysparams->>'schema');
	
	-- specify the operation needs to be perform
	if(sysparams->>'operate' = 'getMyOrder') then
	open $3 for
		--	select od.prname as productname,od.quantity,
		--	m.code as status,
		--	 m."name" as statusText,
		--	 m.fields->>'icon' as statusicon,
		--	 m.fields->>'colorbootstrap' as statuscolor,
		--	p.id as prid,
		--	p."name" as  prname,
		--	p.shortdesc as prshortdesc,
		--	pd.img1 as image,
		--	od.price,
		--	coalesce(od.selectedattribute,'{"id":"0"}'::jsonb) as selectedattribute
		--	from orderdetails od
		--	inner join product p on p.id = od.prid
		--	inner join productdetails pd on od.prdetailsid = pd.id
		--	left join mom m on m."groups"='orderstatus' and  m.code = (od.status)::text
		--	 where orderid =  (params->>'orderid')::int; 
		
	-----------------------------
--		select od.productname,od.variantname,
--		coalesce(od.selectedattribute,'{"id":"0"}'::jsonb) as selectedattribute,
--		od.quantity,
--		od.price,
--		(od.quantity * od.price) as totalsellingprice,
--		coalesce(taxes->>'originalamt','0') as originalamt,
--		coalesce(taxes->>'totalamt','0') as totalamt,
--		coalesce(taxes->>'totaltaxamt','0') as totaltaxamt,
--		coalesce(taxes->>'totalinctaxes','0') as totalinctaxes,
--		coalesce(taxes->>'totaltaxamttoshow','0') as totaltaxamttoshow,
--		pl.productdetails->>'img1' as image
--		from orderdetails od 
--		left join productlog pl on od.prloghash = pl.hash
--		left join ordertaxes ot on od.orderid = ot.orderid
--		where od.orderid = (params->>'orderid')::int;
		
		select od.productname,od.variantname,
		coalesce(od.selectedattribute,'{"id":"0"}'::jsonb) as selectedattribute,
		concat(d.orderprefix,'-',(om.dispordserial)::text)  as orderid,
		om.posordernum as posorderid,
		od.quantity,od.price,
		om.total as totalsellingprice,
		om.subtotal as subtotal,
		om.tax as totaltaxamt,
		om.totaldiscount as totaldiscount,
		coalesce (om.totalextrafees,0) as totalextrafees,
		od.baseprice,
		d.name as dispname,
		d.logo as displogo,
		concat(coalesce(d.address1,''),' ',coalesce(d.address2,'')) as dispaddress,
		coalesce(d.phone,'') as dispphone,
		coalesce(d.email,'') as dispemail,
		coalesce(d.otherdata->>'licenceno') as displicenceno,
		d.id as dispid,
		case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true))  then
		concat(
	 	case when (coalesce(street(customeraddress), '')<>'') then concat(street(customeraddress), ', ') else '' end,
	 	case when (coalesce(apartmnt(customeraddress), '')<>'') then concat(apartmnt(customeraddress), ', ') else '' end,
	 	case when (coalesce(vc.cityname, '')<>'') then concat(vc.cityname, ', ') else '' end,
	 	case when (coalesce(vst.statename, '')<>'') then concat(vst.statename, ', ') else '' end,
	 	case when (coalesce(zip(customeraddress), '')<>'') then concat(coalesce(zip(customeraddress), ''),', ') else '' end,
	 	case when (coalesce(vst.countryname, '')<>'') then concat(vst.countryname, '') else '' end
	 	)
	 	else 
	 	concat(coalesce(d.address1,''),' ',coalesce(d.address2,''))
	 	end
	 	as deliveryaddress,
	 	om.ordertype,
	 	om.schedule_datetime,
	 	d.email_ad_image,
	 	CASE WHEN om.ordertype = ANY(string_to_array(d.pickup_type,',')) and coalesce(d.otherdata->>'cspw_enable','true')::boolean THEN d.cspw_api_key ELSE null END as cspw_api_key,
	 	cspw_status,
	 	od.discounts,
	 	concat(coalesce(c.fname,''),' ',coalesce(c.lname,'')) as cust_name,
	 	coalesce(c.mobile,'') as cust_mobile,
	 	coalesce(c.email,'') as cust_email,
	 	d.tinyurl,
	 	m.name as ordertype_label
		from 
		orderdetails od
		inner join ordermaster om on om.id = od.orderid
		inner join dispensary d on d.id = om.dispensaryid
		inner join customer c on c.id = om.customerid
		left join sys.view_states vst on ((om.customeraddress).state)::int=vst.stateid and case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true)) then true else false end
 		left join sys.view_cities vc on ((om.customeraddress).city)::int=vc.cityid and case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true)) then true else false end
		--inner join ordertaxdiscount otd on otd.orderid = om.id
--		left join (
--			select id,jsonb_array_elements(otherdata->'orderdata') as a from dispensary 
--		) d1
--		on d1.a->>'api_name' = om.ordertype and d1.id = om.dispensaryid
--		left join mom m on m.code = d1.a->>'ordertype'
 		left join mom m on m.code = (case when om.ordertype = 'DELIVERY' then 'del' when om.ordertype = 'PICKUP' then 'pas' when om.ordertype = 'EXPRESS' then 'express' else null end)
 		where od.orderid = (params->>'orderid')::int; 
	return next $3;
	elseif(sysparams->>'operate' = 'getMyOrders') then
	open $3 for
		select 
		concat(d.orderprefix,'-',(om.dispordserial)::text)  as orderid,
		om.posordernum as posorderid,
		om.id as shortid,
			trim(both '"' from to_json(om.createdon)::text) as orderedon,
			coalesce(om.total,0) as total,
			coalesce(om.taxpercent,0) as taxpercent,
			coalesce(om.taxamount,0) as taxamount,
			 m.code as status,
			 m."name" as statusText,
			 m.fields->>'icon' as statusicon,
			 m.fields->>'colorbootstrap' as statuscolor,
			 m.fields->>'colorhex' as hexstatuscolor,
			d.id as dispid,
			d."name" as dispname,
			d."slug" as dispslug,
			d.logo as displogo,
			case when om.ordersummary is null then '' else om.ordersummary end as ordersummary,
			om.ordertype,
			d.isvirtual as isvirtual,
			CASE WHEN om.ordertype = ANY(string_to_array(d.pickup_type,',')) and coalesce(d.otherdata->>'cspw_enable','true')::boolean THEN d.cspw_api_key ELSE null END as cspw_api_key,
			cspw_status,
			om.subtotal as subtotal,
			om.tax as totaltaxamt,
			om.totaldiscount as totaldiscount,
			coalesce (om.totalextrafees,0) as totalextrafees,
			m1.name as ordertype_label
			from ordermaster om 
			inner join dispensary d on d.id = om.dispensaryid
			--inner join mom m on m."groups"='orderstatus' and  m.code = (om.status)::text
			left join sys.mom m on m.code = om.status and m."groups"  = 'orderstatus'
--			left join (
--				select id,jsonb_array_elements(otherdata->'orderdata') as a from dispensary 
--			) d1
--			on d1.a->>'api_name' = om.ordertype and d1.id = om.dispensaryid
--			left join mom m1 on m1.code = d1.a->>'ordertype'
			left join mom m1 on m1.code = (case when om.ordertype = 'DELIVERY' then 'del' when om.ordertype = 'PICKUP' then 'pas' when om.ordertype = 'EXPRESS' then 'express' else null end)
			where om.isactive = true and om.customerid = (params->>'custid')::int and
			case when coalesce((params->>'showCurrent')::boolean,false)=true 
			then
			om.status in('veri_pending','await_process','in_process','ready','out_for_deli')			
			else 
			true
			end
			order by om.id desc;
	return next $3;
	elseif(sysparams->>'operate' = 'getOrderStatus') then
	open $3 for
	
	
	select m."name" as "position",(os.createdon)::timestamp(0),m.remark,case when os.createdon is not null then 'active' else '' end as isactive
		from sys.mom m 
		left join orderstatuslog os  on os.status = m.code and  os.orderid = (params->>'orderid')::int
		where  m."groups" = 'orderstatus' order by os.createdon ,m.remark ;
--		select x->>'seq' as  "sequence", x->>'iscomplete' as  iscomplete , x->>'label' as "label" 
--					from json_array_elements(' [
--                       {"seq":1 ,"iscomplete":true,"label":"Order Placed"},
--                       {"seq":2 ,"iscomplete": true, "label": "Waiting for Confirmation" },
--                       {"seq":3 , "iscomplete": false, "label": "Confirmed" },
--                       {"seq":4 , "iscomplete": false, "label": "Delivered" },
--                       {"seq":5 , "iscomplete": false, "label": "Done" }
--                     ]'::json) as x order by "sequence";
--        select 
--		case when (m.code)::int = o2.status then true else false end as iscomplete,
--		m.code,m."name" as "label",m.remark  as "sequence" from  mom m
--		 left join ordermaster o2 on (m.code)::int = o2.status  and o2.id = (params->>'orderid')::int
--		where   m."groups" = 'orderstatus' and m.isactive = true order by remark;
	return next $3;
	elseif (sysparams->>'operate' = 'getOrder') THEN 
		open $3 for
			select 
			
			od.prname as name,
			coalesce(od.updatedon,od.createdon) as updated,
			od.orderid as ordno,
			case when od.status=1 then 'deliverd' else 'pending_confirm' end as status,
			case when od.status=1 then 'Deliverd' else 'Pending To Confirm' end as statustext,
			case when od.status=1 then 'check_circle' else 'query_builder' END as statusicon,
			od.createdon as actdate,
			om.total as amount,
			p.img1 as image,
			1 as qty,
			prid as productid
			
			from ordermaster om 
			RIGHT join orderdetails od
			left join productdetails p on p.id=od.prdetailsid
on om.id=od.orderid where om.customerid=(params->>'custid')::int;
		return next $3;
	elsif (sysparams->>'operate' = 'getOrders') THEN 
		open $3 for
			select 
			o.id as ordno,
			to_char(o.createdon, 'DD-MM-YYYY HH:MI:SS') as actdate,
			concat(c.fname,' ',c.lname) as custname,
			o.mobile,
			o.email,
			o.customeraddress,
			o.verification,
			o.total as amount,
			o.status
			from ordermaster o 
			left join
			customer c on c.id=o.customerid
			where o.dispensaryid=(params->>'dispensary')::uuid;
			/*select 
			
			od.prname as name,
			coalesce(od.updatedon,od.createdon) as updated,
			od.orderid as ordno,
			case when od.status=1 then 'deliverd' else 'pending_confirm' end as status,
			od.createdon as actdate,
			coalesce(om.total,0) as amount,
			p.img1 as image,
			prid as productid
			
			from ordermaster om 
			RIGHT join orderdetails od
			left join productdetails p on p.id=od.prdetailsid
on om.id=od.orderid where om.dispensaryid=(params->>'dispensaryid')::uuid;*/
		return next $3;
		elsif (sysparams->>'operate' = 'getDocument') then
			open $3 for
			select d.id,d.reference_id,d.filename,d.doctype,d.path,d.module,d.filesize,d."extension",m.name as documenttype from documents d left join mom m on d.doctype=m.code and m.groups='document' where reference_id=(params->>'customerid')::int;		
			-- select a.* as id from json_array_elements('[1,2,3]') a; 			
		return next $3;
	elseif(sysparams->>'operate' = 'getOrderForA') then
	open $3 for
		select od.productname,od.variantname,
		coalesce(od.selectedattribute,'{"id":"0"}'::jsonb) as selectedattribute,
		concat(d.orderprefix,'-',(om.dispordserial)::text)  as orderid,
		om.posordernum as posorderid,
		od.quantity,od.price,
		om.total as totalsellingprice,
		om.subtotal as subtotal,
		om.tax as totaltaxamt,
		om.totaldiscount as totaldiscount,
		coalesce (om.totalextrafees,0) as totalextrafees,
		od.baseprice,
		d.name as dispname,
		concat(coalesce(d.address1,''),' ',coalesce(d.address2,'')) as dispaddress,
		coalesce(d.phone,'') as dispphone,
		coalesce(d.email,'') as dispemail,
		coalesce(d.otherdata->>'licenceno') as displicenceno,
		pd.img1 as image,
		om.ordertype,
		(om.customeraddress).street as custstreet,
		(om.customeraddress).apartmnt as custapartment,
		gcn."name" as custcountry,
		gst."name" as custstate,
		gct."name" as custcity,
		(om.customeraddress).zip as custzip,
		d.logo as displogo,
		concat(c.fname,' ',c.lname) as custname,
		od.discounts
		from 
		orderdetails od
		inner join ordermaster om on om.id = od.orderid
		inner join dispensary d on d.id = om.dispensaryid
		inner join customer c on c.id = om.customerid
		left join productdetails pd on pd.id=od.prdetailsid
		left join sys.geolocation gcn on (gcn.id)::int = ((om.customeraddress).country) ::int
		left join sys.geolocation gst on (gst.id)::int = ((om.customeraddress).state) ::int
		left join sys.geolocation gct on (gct.id)::int = ((om.customeraddress).city) ::int
		where od.orderid = (params->>'orderid')::int; 
	return next $3;
	elseif(sysparams->>'operate' = 'getOrderForGTM') then
	open $3 for
--	select json_build_object('ecommerce',
--json_build_object('purchase',
--json_build_object('actionField',
--json_build_object(
--'id',concat(concat(d.orderprefix,'-',(o.dispordserial)::text),' | ',o.posordernum),
--'affiliation',d.name,
--'revenue',round((o.subtotal::numeric),4),
--'tax',round((o.tax::numeric),4),
--'shipping',round((o.totalextrafees::numeric),4)
--),'products',
--(select jsonb_agg (jsonb_build_object('id',p.puid ,'name',od.productname,'price',round(((od.baseprice/od.quantity)::numeric),4),'quantity',od.quantity,'category',c.name,'brand',b.name)) from orderdetails od 
--inner join product p on p.id=od.prid 
--left join category c on c.id=p.categoryid 
--left join brand b on b.id=p.brandid 
---- inner join productdetails pd on od.prdetailsid=pd.id // used for product sku. commented due to some product doesnt have sku so we used treez prod id
--where od.orderid=o.id)
--))) as orderdata
--from ordermaster o
--inner join dispensary d on o.dispensaryid=d.id
--where o.id=(params->>'id')::int; 
	
	select 	
	json_build_object(	
		'transaction_id',concat(concat(d.orderprefix,'-',(o.dispordserial)::text),' | ',o.posordernum),	
		'value',round((o.subtotal::numeric),4),	
		'tax',round((o.tax::numeric),4),	
		'shipping',round((o.totalextrafees::numeric),4),	
		'currency','USD',	
		'items', 	
			(	
				select jsonb_agg (jsonb_build_object('item_id',p.puid ,'item_name',od.productname,'price',round(((od.baseprice/od.quantity)::numeric),4),'quantity',od.quantity,'item_category',c.name,'item_brand',b.name)) from orderdetails od 	
				inner join product p on p.id=od.prid 	
				left join category c on c.id=p.categoryid 	
				left join brand b on b.id=p.brandid 	
				-- inner join productdetails pd on od.prdetailsid=pd.id // used for product sku. commented due to some product doesnt have sku so we used treez prod id	
				where od.orderid=o.id	
			)	
		) as orderdata	
		from ordermaster o	
		inner join dispensary d on o.dispensaryid=d.id	
		where o.id=(params->>'id')::int; 
	
	return next $3;

elseif(sysparams->>'operate' = 'getMyOrderForReadyCancelComplete') then
	open $3 for
		
		select od.productname,od.variantname,
		coalesce(od.selectedattribute,'{"id":"0"}'::jsonb) as selectedattribute,
		concat(d.orderprefix,'-',(om.dispordserial)::text)  as orderid,
		om.posordernum as posorderid,
		od.quantity,od.price,
		om.total as totalsellingprice,
		om.subtotal as subtotal,
		om.tax as totaltaxamt,
		om.totaldiscount as totaldiscount,
		coalesce (om.totalextrafees,0) as totalextrafees,
		od.baseprice,
		d.name as dispname,
		d.logo as displogo,
		concat(coalesce(d.address1,''),' ',coalesce(d.address2,'')) as dispaddress,
		coalesce(d.phone,'') as dispphone,
		coalesce(d.email,'') as dispemail,
		coalesce(d.otherdata->>'licenceno') as displicenceno,
		d.id as dispid,
		case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true))  then
		concat(
	 	case when (coalesce(street(customeraddress), '')<>'') then concat(street(customeraddress), ', ') else '' end,
	 	case when (coalesce(apartmnt(customeraddress), '')<>'') then concat(apartmnt(customeraddress), ', ') else '' end,
	 	case when (coalesce(vc.cityname, '')<>'') then concat(vc.cityname, ', ') else '' end,
	 	case when (coalesce(vst.statename, '')<>'') then concat(vst.statename, ', ') else '' end,
	 	case when (coalesce(zip(customeraddress), '')<>'') then concat(coalesce(zip(customeraddress), ''),', ') else '' end,
	 	case when (coalesce(vst.countryname, '')<>'') then concat(vst.countryname, '') else '' end
	 	)
	 	else 
	 	concat(coalesce(d.address1,''),' ',coalesce(d.address2,''))
	 	end
	 	as deliveryaddress,
	 	om.ordertype,
	 	om.schedule_datetime,
	 	d.email_ad_image,
	 	CASE WHEN om.ordertype = ANY(string_to_array(d.pickup_type,',')) and coalesce(d.otherdata->>'cspw_enable','true')::boolean THEN d.cspw_api_key ELSE null END as cspw_api_key,
	 	cspw_status,
	 	concat(coalesce(c.fname,''),' ',coalesce(c.lname,'')) as cust_name,
	 	coalesce(c.mobile,'') as cust_mobile,
	 	coalesce(c.email,'') as cust_email,
	 	d.tinyurl
		from orderdetails od
		inner join ordermaster om on om.id = od.orderid
		inner join dispensary d on d.id = om.dispensaryid
		inner join customer c on c.id = om.customerid
		left join sys.view_states vst on ((om.customeraddress).state)::int=vst.stateid and case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true)) then true else false end
 		left join sys.view_cities vc on ((om.customeraddress).city)::int=vc.cityid and case when (om.ordertype='DELIVERY' or (om.ordertype='PICKUP' AND d.isvirtual=true)) then true else false end
		where om.posorderid = params->>'orderid'; 
	return next $3;

	elseif(sysparams->>'operate' = 'getPayments') then
		open $3 for
		select o.paytype, o.payment_method, o.amount, o.payment_status, o.ticket_note, o.treez_payment_status, om.posordernum as id
		from orderpayments o
		left join ordermaster om on (o.orderid=om.id)
		where o.orderid=(params->>'orderid')::int; 
	
	elseif(sysparams->>'operate' = 'ordertypeedit') then
		open $3 for
		
		select id,name,code,description,isactive,logo from mom where id=(params->>'id') :: int;
		return next $3;
	
	end if;
	
	
	END;
    $function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_product(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
declare _sortdata character varying[];
declare _hurrytext text;
declare _skipinventory boolean default false;
declare _randnum1 int;
declare _timezoneoffset character varying;
declare _dispensaryid uuid;
declare _minvisinvlevel int;
declare _virtualdispensaryid uuid;
declare _attributedata json;
-- declare _randnum2 int;
	begin
		
	
		
	-- select func.fn_product({"schema":"sys", "operate":""},{"userid":""}, 'result'); fetch all in result;
	
	perform public.setschema(sysparams->>'schema');
		
	
	
	
	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'attribute') THEN 
		open $3 for
			select id,name,array_to_json("values")::jsonb as "values" from "attributes" where isactive=true and dispensaryid=(params->>'dispensary')::uuid;
		return next $3;
	elseif (sysparams->>'operate' = 'variance') THEN
		open $3 for
			select id,price,sellingprice,minvisinventorylevel,totalmgthc,totalmgcbd,attributevalues,dispensaryid,variantname,jsonb_build_array('productname','brand','category','classification','subcategory','uom','selling_price','tmt','tmcc','barcode','variantname','file_img1','file_img2','file_img3') as fielddisabled from "productdetails" where isactive=true and productid=(params->>'prodid')::int;
		   return next $3;
		  
	 elseif (sysparams->>'operate' = 'editvarient') then
	 	open $3 for
	 		select p.id as productid,p."name",p.shortdesc,v.description,p.brandid,p.typeid,p.categoryid,p.subcatid,replace((p.tags)::character varying,'"','') as tags,p.uom,v.id,v.price,v.sellingprice,
coalesce(v.img1,'') as img1,
coalesce(v.img2,'') as img2,
coalesce(v.img3,'') as img3,
v.dispensaryid,v.minvisinventorylevel,v.totalmgthc,v.totalmgcbd,p."attributes",v.attributevalues,p.extraction,p.productreleasedate,v.effects,v.sku,v.barcode,v.variantname,coalesce(p.isnew,0) as isnew,p.tax,coalesce(p.showonhome,0) as showonhome,coalesce(p.purpose,'{}') as purpose,jsonb_build_array('productname','brand','category','classification','subcategory','uom','selling_price','tmt','tmcc','barcode','variantname') as fielddisabled,p.hidfrmscren as hidefromscreen,p.editseolabel,p.seolabel,p.metatitle FROM productdetails v INNER JOIN product p
ON v.productid=p.id WHERE v.id=(params->>'proddetailid')::int; 
	 
	 return next $3;
	
	elseif (sysparams->>'operate' = 'edit') then
	 	open $3 for
	 		select p.id as productid,p."name",p.shortdesc,p.description,p.brandid,p.typeid,p.categoryid,p.subcatid,p.tags,p.uom,p.extraction,p.productreleasedate,coalesce(p.isnew,0) as isnew,p.tax,p.attributes,coalesce(p.showonhome,0) as showonhome,coalesce(p.purpose,'{}') as purpose,p.hidfrmscren as hidefromscreen,p.editseolabel,p.seolabel,p.metatitle FROM product p where p.id=(params->>'prodid')::int; 
	 	return next $3;
	
	 elseif (sysparams->>'operate'='diswiseprod') then
		open $3 for
				select p.id as productid,p."name" as prodname,p."name" as "name",'' as thumbnail,coalesce(pd.sellingprice,0) as price,pd.id as proddetailid ,
		pd.img1,p.shortdesc ,
		
		p.showoncmppage,
		((coalesce(p.prodrating->>'rating','0'))::int) / ((coalesce(p.prodrating->>'custcount','1'))::int) as prodrating,
		p.isnew,
		case when (coalesce(pd.price,0)>coalesce(pd.sellingprice,0)) then round(100-coalesce(pd.sellingprice,0)/coalesce(pd.price,0)*100) else 0 end as discount,
		coalesce(pd.price,0) as regular_price,
		dis.id as disid,
		dis.name as disname,
		dis.address1 as address,
		dis.banner as banner,
		to_char(dt.fromtime,'HH:MM AM') as "fromtime",
		to_char(dt.totime,'HH:MM PM') as "totime",
		case 
		when current_time>dt.fromtime and current_time<dt.totime then
		'Open'
		else
		'Closed'
		end as status,b.name as brandname,c.name as classname,c.remark as classcolor,coalesce(inv.availquantity,0) as sellableqty
		from product p 
		inner join productdetails pd on p.id=pd.productid and pd.isdefault=1
		inner join dispensary dis on p.dispensaryid = dis.id
		left join inventory inv on inv.productid=p.id and inv.dispensaryid=p.dispensaryid and inv.ordertype=params->>'ordertype'
		left join brand b on b.id=p.brandid
		left join mom c on c.id=p.typeid and lower(c."groups")='classification'
		
		left join
		dispensarytime dt on dt.dispensaryid=dis.id and dt.day=EXTRACT(DOW from current_timestamp) and dt.nonkiosk=true
		-- where 1
		-- and p.showonhome = 1
		order by coalesce(inv.availquantity,0) desc limit 10;
		return next $3;
	
	
	elseif(sysparams->>'operate'='overview') then
		
		select coalesce (config->>'mininvlevelmsg',''),coalesce((otherdata->>'minvisinvlevel')::int,0) into _hurrytext, _minvisinvlevel from dispensary where id = case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dispensary')::uuid else (params->>'dispensary')::uuid end;
		select coalesce(skipinventory,false),timezoneoffset into _skipinventory,_timezoneoffset  from dispensary where id = (params->>'dispensary')::uuid;
		select json_agg(json_build_object('attrdata',(po.attributevalues)::jsonb,'varid',po.id)) into _attributedata from productdetails po where po.productid=(params->>'id')::int and po.isactive=true limit 1;
		_randnum1=floor(random() * 20 + 1);
	 -- _randnum2=floor(random()*5+1);
		open $3 for
		select 
		p.id as productid,po.id as varientid,coalesce(po.variantname,p.name) as name,p.shortdesc,po.description,p.productreleasedate,
		coalesce(po.sellingprice,0) as sellingprice,
		po.totalmgthc,po.totalmgcbd,coalesce(po.sku,'') as sku,c."name" as categoryname,coalesce(b."name",'') as brandname,po.effects,p."attributes", 
		po.attributevalues,p.dispensaryid, coalesce(w.id,'0') as wishlistid,p.isnew,p.puid,
		coalesce(po.price,0) as regular_price,c1.name as classiname,c1.remark as classicolor,sc.name as subcategoryname,
		case when(po.minvisinventorylevel is null or (po.minvisinventorylevel)::int = 0)then
			_minvisinvlevel
		else
			(select coalesce(po.minvisinventorylevel,0)) ::int
		end as minvisinventorylevel,
		(select replace(_hurrytext, ('{qty}')::text, coalesce(inv.availquantity,0)::text)) as minvismsg,
		case when (coalesce(prodrating->>'rating','0')::int / coalesce(prodrating->>'custcount','1')::int)>5 then
			5
		else
			(coalesce(prodrating->>'rating','0')::int / coalesce(prodrating->>'custcount','1')::int)
		end
		as  prodrating,
		coalesce(prodrating->>'rating','0') as oldrating,
		coalesce(prodrating->>'custcount','1')::int as ratingcustcount,
		case 
		when tem.discount>0 then tem.discount  
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then d.amountpercentage
		when p.custdiscountid is not null and d.amounttype='per' and d.hascoupon=false then d.amountpercentage 
		when (coalesce(po.price,0)>coalesce(po.sellingprice,0)) then round(100-coalesce(po.price,0)/coalesce(po.sellingprice,0)) 
		else 0 
		end as discount,
		case 
		when tem.discount>0 then ((coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*tem.discount)/100))
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then (coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		when p.custdiscountid is not null and d.amounttype='per' and d.hascoupon=false then (coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		else
		    coalesce(po.sellingprice,0) end 
		as sellingprice_lbl,
		json_build_array (
			json_build_object('path',coalesce(po.img1,''),'flag',true),
			json_build_object('path',coalesce(po.img2,''),'flag',false),
			json_build_object('path',coalesce(po.img3,''),'flag',false)
		) as images,
		_attributedata as attributedata,
		case when (false=true) then 100 else coalesce(inv.availquantity,0) end as sellableqty, 
		case when _randnum1<10 then coalesce(p.livepeople,0) else 0 end as liveuser,
		p.metatitle,array_to_string(p.tags, ', ', '*') as keywords,p.seolabel as prodlabel,b.label as brandlabel,p.seolabel as prodslug,
		b.label as brandslug,d.firstuser,d.ordertype as dis_ordertype,case when tem.discount>0 then true else false end as agediscount,
		lr.lab_results	
		from product p 
		inner join productdetails po on p.id=po.productid and case when (coalesce((params->>'varid'),'0')::int=0) then po.isdefault=1 else true end
		left join 
		(
			select productid,sum(coalesce(availquantity,0)) as availquantity 
			from inventory 
			where dispensaryid=(params->>'dispensary')::uuid 
			and "location"=case when coalesce(params->>'location','')='' then "location" else params->>'location' end group by productid
		) inv on p.id=inv.productid
		inner join category c on c.id=p.categoryid
		left join subcategory sc on sc.id=p.subcatid
		left join mom c1 on c1.id=p.typeid
		left join brand b on b.id=p.brandid
		left join 
		(
				select 
				p.id as ag_prodid,
				ad.exclusiveid,
				max(case when DATE_PART('day',((current_timestamp at time zone _timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today then ad.discount else 0 end) as discount 
				from inventory i 
				inner join product p on p.id=i.productid 
				inner join agediscount ad on ad.categoryid=p.categoryid and ad.dispensaryid=i.dispensaryid 
				left join agediscount_filter af on af.discount_common_id = ad.commonid and case when af.type = 'brand' then af.value = p.brandid when af.type = 'product' then af.value = p.id else false end
				where af.id is null and i.invdate is not null
				and i.dispensaryid=(params->>'dispensary')::uuid 
				and DATE_PART('day',((current_timestamp at time zone _timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today 
				and ad.isactive=true and p.id= coalesce((params->>'id'),'0')::int 
				group by p.id,ad.exclusiveid
		) tem on tem.ag_prodid=p.id
		left join 
		(
				select disc.id,disc.amounttype,disc.displaycoupondiscount,
				disc.amountpercentage,disc.device,disc.firstuser,disc.ordertype, 
				case when (c.coupontype IN('CCWL','CCWOL') and c.isactive=true) then true else false end as hascoupon
				from discount disc 
				left join coupon c on c.disid = disc.id
				where (device=coalesce('web','web') OR COALESCE(device,'')='') 
				and disc.isactive=true and (current_timestamp at time zone _timezoneoffset) between disc.fromdate and disc.todate
		) d on d.id=p.custdiscountid
		left join
		( 
		    select product_id,jsonb_agg(jsonb_build_object('content_type',plr.content_type,'minimum_value',plr.minimum_value,'maximum_value',plr.maximum_value,'amount_type',plr.amount_type)) as lab_results from product_lab_results plr
		    where plr.amount_type='PERCENTAGE' group by product_id
		) lr on lr.product_id = p.id
		left join 
		(
			select id,productid from wishlist where custid=(params->>'custid')::int and isactive=true
		) w on w.productid=p.id
		where 
		p.id= coalesce((params->>'id'),'0')::int 
		and coalesce((params->>'varid'),'0')::int=case when (coalesce((params->>'varid'),'0')::int<>0) then po.id else coalesce((params->>'varid'),'0')::int END ;
		
		return next $3;
		
	
	
	elseif (sysparams->>'operate'='overview_old') THEN 
		select coalesce (config->>'mininvlevelmsg','') into _hurrytext from dispensary where id = case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dispensary')::uuid else (params->>'dispensary')::uuid end;
	
		select coalesce(skipinventory,false),timezoneoffset into _skipinventory,_timezoneoffset  from dispensary where id = (params->>'dispensary')::uuid;
	
		_randnum1=floor(random() * 20 + 1);
		-- _randnum2=floor(random()*5+1);
		open $3 for
		select p.id as productid,po.id as varientid,coalesce(po.variantname,p.name) as name,p.shortdesc,po.description,p.productreleasedate,
		case 
		when tem.discount>0 then ((coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*tem.discount)/100))
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then (coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		when p.custdiscountid is not null and d.amounttype='per' and not exists(select * from coupon where disid=d.id and coupontype IN('CCWL','CCWOL') and isactive=true limit 1) then (coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		
		else
            coalesce(po.sellingprice,0) end as sellingprice_lbl,
		coalesce(po.sellingprice,0) as sellingprice,
		
		
		--coalesce(po.minvisinventorylevel,0) as minvisinventorylevel,
		case when(po.minvisinventorylevel is null or (po.minvisinventorylevel)::int = 0)then
			(select coalesce((otherdata->>'minvisinvlevel')::int,0) from dispensary where id = case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dispensary')::uuid else (params->>'dispensary')::uuid end) ::int
		else
			(select coalesce(po.minvisinventorylevel,0)) ::int
		end as minvisinventorylevel,
		(select replace(
						_hurrytext,
						('{qty}')::text,
						coalesce(inv.availquantity,0)::text
						) ) as minvismsg,
		po.totalmgthc,po.totalmgcbd,coalesce(po.sku,'') as sku,c."name" as categoryname,coalesce(b."name",'') as brandname,po.effects,p."attributes", 
		
		case when (coalesce(prodrating->>'rating','0')::int / coalesce(prodrating->>'custcount','1')::int)>5 then
			5
		else
			(coalesce(prodrating->>'rating','0')::int / coalesce(prodrating->>'custcount','1')::int)
		end
		as  prodrating,
		coalesce(prodrating->>'rating','0') as oldrating,
		coalesce(prodrating->>'custcount','1')::int as ratingcustcount,
		po.attributevalues,p.dispensaryid, coalesce(w.id,'0') as wishlistid,p.isnew,
		case 
		when tem.discount>0 then tem.discount  
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then d.amountpercentage
        when p.custdiscountid is not null and d.amounttype='per' and not exists(select * from coupon where disid=d.id and coupontype IN('CCWL','CCWOL') and isactive=true limit 1) then d.amountpercentage 
		when (coalesce(po.price,0)>coalesce(po.sellingprice,0)) then round(100-coalesce(po.price,0)/coalesce(po.sellingprice,0)) 
		else 0 
		end as discount,
		coalesce(po.price,0) as regular_price,
		c1.name as classiname,c1.remark as classicolor,sc.name as subcategoryname,
		json_build_array(json_build_object('path',coalesce(po.img1,''),'flag',true),
		json_build_object('path',coalesce(po.img2,''),'flag',false),
		json_build_object('path',coalesce(po.img3,''),'flag',false)
		) as images,(select jsonb_agg(jsonb_build_object('attrdata',(po.attributevalues)::jsonb,'varid',po.id)) from productdetails po where po.productid=(params->>'id')::int and po.isactive=true) as attributedata,p.puid,
		case when (_skipinventory=true) then 100 else coalesce(inv.availquantity,0) end as sellableqty, 
		-- coalesce(inv.availquantity,0) as sellableqty,
		-- case when coalesce((params->>'fromcat')::boolean,false)=true then 
			case when _randnum1<10 then coalesce(p.livepeople,0) else 0 end
		 -- else 
		 -- 	0
		 -- end 
		 as liveuser,p.metatitle,array_to_string(p.tags, ', ', '*') as keywords,p.seolabel as prodlabel,b.label as brandlabel,p.seolabel as prodslug,b.label as brandslug,d.firstuser,d.ordertype as dis_ordertype,case when tem.discount>0 then true else false end as agediscount
		 ,(select jsonb_agg(jsonb_build_object('content_type',plr.content_type,'minimum_value',plr.minimum_value,'maximum_value',plr.maximum_value,'amount_type',plr.amount_type)) from product_lab_results plr where product_id=p.id and plr.amount_type='PERCENTAGE') as lab_results	
		 from product p 
left join productdetails po on p.id=po.productid and case when (coalesce((params->>'varid'),'0')::int=0) then po.isdefault=1 else true end
left join 
(select productid,sum(coalesce(availquantity,0)) as availquantity from inventory where dispensaryid=(params->>'dispensary')::uuid and "location"=case when coalesce(params->>'location','')='' then "location" else params->>'location' end group by productid) inv on p.id=inv.productid
left join category c on c.id=p.categoryid
left join subcategory sc on sc.id=p.subcatid
left join mom c1 on c1.id=p.typeid and lower(c1.groups)='classification'
left join brand b on b.id=p.brandid
left join (select p.id as ag_prodid,ad.exclusiveid,max(case when DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today then ad.discount else 0 end) as discount from inventory i inner join product p on p.id=i.productid inner join agediscount ad on ad.categoryid=p.categoryid and ad.dispensaryid=i.dispensaryid 
INNER JOIN dispensary d ON d.id=ad.dispensaryid 
left join agediscount_filter af on af.discount_common_id = ad.commonid and case when af.type = 'brand' then af.value = p.brandid when af.type = 'product' then af.value = p.id else false end
where af.id is null and i.invdate is not null and i.dispensaryid=(params->>'vir_dispensary')::uuid and DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today and ad.isactive=true and p.id= coalesce((params->>'id'),'0')::int group by p.id,ad.exclusiveid) tem on tem.ag_prodid=p.id
left join (select * from discount where (device=coalesce(params->>'device','web') OR COALESCE(device,'')='')) d on d.id=p.custdiscountid and d.isactive=true and (current_timestamp at time zone _timezoneoffset) between d.fromdate and d.todate
left join wishlist w on w.productid=p.id and w.custid=(params->>'custid')::int and w.isactive=true
where p.id= coalesce((params->>'id'),'0')::int and coalesce((params->>'varid'),'0')::int=case when (coalesce((params->>'varid'),'0')::int<>0) then po.id else coalesce((params->>'varid'),'0')::int END ;
		return next $3;
	
	elseif (sysparams->>'operate'='top5') then
		open $3 for
		select p.id as productid,p."name",'' as thumbnail,coalesce(pd.sellingprice,0) as price,pd.id as proddetailid ,
		pd.img1,p.shortdesc 
		from product p 
		left join productdetails pd on p.id=pd.productid and pd.isdefault=1
		order by p.id desc limit 8;
		return next $3;
	elseif (sysparams->>'operate'='banner') then
		open $3 for
		-- set hardcode schema name 
		-- seo errors were comming.
		select jdata as imgdata from banners where isactive=true and isdelete = false  order by "order" Asc limit 5;
		return next $3;
	elseif (sysparams->>'operate'='getprodattr') then
		open $3 for
		select jsonb_agg(jsonb_build_object('attrdata',(po.attributevalues)::jsonb,'varid',po.id)) as attributelist from productdetails po where po.productid=(params->>'id')::int and po.isactive=true;
		return next $3;
	elseif (sysparams->>'operate'='productautoddl') then
		open $3 for
		select p.name from product p where p.isactive=true order by name;
		return next $3;
	elseif (sysparams->>'operate'='totalprod') then
		open $3 for
		select COUNT(p.name) as count from product p where dispensaryid=(params->>'dispensary')::uuid
		union all 
		select COUNT(1) as count from inventory p where dispensaryid=(params->>'dispensary')::uuid;
		return next $3;
	elseif (sysparams->>'operate'='getProductLastQty') then
		open $3 for
		select productid,sum(coalesce(availquantity,0)) as qty from inventory p where dispensaryid=(params->>'dispensary')::uuid and productid=(params->>'productid')::int and "location"=case when (params->>'location'='') then "location" else params->>'location' end group by productid;
		return next $3;
	elseif (sysparams->>'operate'='updatelivepeople') then
		update product set livepeople=floor(random()*4+2) where published=true;
		open $3 for
		select 'updated successfully' as status;
		return next $3;
	elseif (sysparams->>'operate'='autocomplete') then
		open $3 for
		select p.id,p.name,pd.id as varientid from product p 
		inner join productdetails pd on p.id=pd.productid 
		where p.published=true and lower(p.name) like concat('%',lower(params->>'keyword'),'%') and p.dispensaryid=(select case when isvirtual=true then parentid else id end from dispensary where id = (params->>'dispensary')::uuid) 
	
		order by p.name limit 20;
		return next $3;
	elseif (sysparams->>'operate'='productdetailbyidsforcart') then
		open $3 for
		select p.id,pd.img1,p.name,p.seolabel as prodlabel,pd.sellingprice, (sum(coalesce(i.availquantity,0)))::int as available_qty,'{}' as tier_prices from product p 
		inner join productdetails pd on p.id=pd.productid 
		left join inventory i on i.productid=p.id and p.dispensaryid=i.dispensaryid and i.location=params->>'location'
		where p.id::text = any(select jsonb_array_elements_text((params->>'ids')::jsonb))
		group by p.id,pd.img1,p.name,p.seolabel,pd.sellingprice; 
		return next $3;
	elseif(sysparams->>'operate' = 'search') THEN 
	 	open $3 for
	 		select p.id,p.name as prod_name,d.name as disp_name,pd.img1 as image,pd.sellingprice as regular_price,d.slug,b.label as brand,p.seolabel from product p 
	 		inner join productdetails pd on p.id=pd.productid 
	 		inner join inventory i on p.id = i.productid and p.dispensaryid=i.dispensaryid
	 		inner join brand b on p.brandid=b.id
	 		inner join dispensary d on p.dispensaryid=d.id 
	 		where lower(p.name) like concat('%',lower(params->>'name'),'%') and p.isactive=true and p.hidfrmscren=false and d.isactive=true and i.availquantity>0 
	 		group by p.id,p.name,d.name,pd.img1,pd.sellingprice,d.slug,b.label,p.seolabel
	 		order by p.name asc limit 10;
	 	return next $3;
	 elseif(sysparams->>'operate' = 'get_product_list_by_coupon') then
	 
	 open $3 for
	 	select p.id,p.name,b.name as brand,coalesce(pd.img1,'/assets/img/default.png') as img1,sum(i.availquantity) as availquantity,c2.name as category,1 as qty,pd.sellingprice,pd.sku,pd.id as varientid,p.dispensaryid,p.seolabel as prodlabel,b."label" as brandlabel,p.puid from product p
		inner join productdetails pd on p.id=pd.productid
		inner join inventory i on i.productid = p.id and i.dispensaryid=p.dispensaryid
		inner join brand b on b.id=p.brandid
		inner join category c2 on c2.id = p.categoryid 
		inner join discount dic on dic.dispensaryid  = p.dispensaryid 
		inner join discountmapping dm on dm.disid = dic.id and dm.ref_id = case when (dm.module='category') then  p.categoryid when (dm.module='brand') then  p.brandid when (dm.module='product') then  p.id else 0 end
		inner join coupon c on c.disid = dic.id
		where 
		i.availquantity>0 and p.dispensaryid = (params->>'dispensary')::uuid 
		and lower(c.couponcode) = lower(params->>'code')
		group by p.id,p.name,b.name,pd.img1,c2.name,pd.sellingprice,pd.sku,pd.id,p.dispensaryid,p.seolabel,b."label" 
		order by p.name,availquantity asc;
	 	
	 return next  $3;
	
	elseif(sysparams->>'operate' = 'noninvproduct') then
	
		open $3 for
		
		select p.id,p.name,null as brand,coalesce(pd.img1,'/assets/img/default.png') as img1,1 as availquantity,null as category,1 as qty,pd.sellingprice,pd.sku,pd.id as varientid,p.dispensaryid,p.seolabel as prodlabel,null as brandlabel,p.puid from product p
		inner join productdetails pd on p.id=pd.productid
	 	where p.id = 966982
	  	and p.dispensaryid = (params->>'dispensary')::uuid ;
		
		return next $3;
	
	elseif(sysparams->>'operate' = 'addonproducts') then
	
		_dispensaryid := case when (coalesce(params->>'isvirtual','false')::boolean=true) then (params->>'parentid')::uuid else (params->>'dispid')::uuid end;
		_virtualdispensaryid := (params->>'dispid')::uuid;
		select timezoneoffset into _timezoneoffset  from dispensary where id = _dispensaryid;
		
		open $3 for
		
--		select p.id,p.name,b.name as brand,coalesce(pd.img1,'/assets/img/default.png') as img1,sum(i.availquantity) as availquantity,c2.name as category,1 as qty,pd.sellingprice,pd.sku,pd.id as varientid,p.dispensaryid,p.seolabel as prodlabel,b."label" as brandlabel,p.puid from product p
--		inner join addonproducts a on a.product_id = p.id
--		inner join productdetails pd on a.product_id=pd.productid
--		inner join 
--		(
--			select nv.productid,nv.dispensaryid,sum(coalesce(nv.availquantity,0)) as availquantity 
--			from inventory nv 
--			inner join 
--			(	
--				select
--				(params->>'dispid')::uuid as id,
--				x->>'location' as "location",
--				x->>'ordertype' as code
--				from
--				json_array_elements((select integration->'pos'->'locationdata' as ae from dispensary d 
--				where isactive=true and id = (params->>'dispid')::uuid)::json) as x
--			)
--			d on nv.dispensaryid=d.id 
--			where nv.dispensaryid=(params->>'dispid')::uuid
--			and case when (d.location='') then true else nv."location"=d.location end
--			and d.code=params->>'ordertype'
--			group by nv.productid, nv.dispensaryid
--		) i 
--		on i.productid = p.id and i.dispensaryid=p.dispensaryid
--		left join brand b on b.id=p.brandid
--		left join category c2 on c2.id = p.categoryid
--		where 
--		i.availquantity>0 and p.dispensaryid = (params->>'dispid')::uuid
--		group by p.id,p.name,b.name,pd.img1,c2.name,pd.sellingprice,pd.sku,pd.id,p.dispensaryid,p.seolabel,b."label" 
--		order by p.name,availquantity asc;
		
		select
		p.id,p.name,b.name as brand,po.img1,c.name as category,po.sku,p.dispensaryid,p.seolabel as prodlabel,b."label" as brandlabel,
		po.sku,
		p.puid,
		1 as qty,
		po.id as varientid,
		p.dispensaryid,
		case 
		when tem.discount>0 then 
			((coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*tem.discount)/100))
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then 
			(coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		when p.custdiscountid is not null and d.amounttype='per' and not exists(select * from coupon where disid=d.id and coupontype IN('CCWL','CCWOL','PCC') and isactive=true limit 1) then 
			(coalesce(po.sellingprice,0)-(coalesce(po.sellingprice,0)*d.amountpercentage)/100)
		else
            coalesce(po.sellingprice,0) 
        end 
        as price_lbl,
		coalesce(po.sellingprice,0) as sellingprice,
		po.totalmgthc,po.totalmgcbd,
		case 
		when tem.discount>0 then 
			tem.discount  
		when p.custdiscountid is not null and d.amounttype='per' and d.displaycoupondiscount then 
			d.amountpercentage
        when p.custdiscountid is not null and d.amounttype='per' and not exists(select * from coupon where disid=d.id and coupontype IN('CCWL','CCWOL','PCC') and isactive=true limit 1) then 
        	d.amountpercentage 
		when (coalesce(po.price,0)>coalesce(po.sellingprice,0)) then 
			round(100-coalesce(po.price,0)/coalesce(po.sellingprice,0)) 
		else 
			0 
		end 
		as discount,
		-- coalesce(po.price,0) as regular_price,
		case when tem.discount>0 then true else false end as agediscount
		from product p 
		inner join addonproducts a on a.product_id = p.id
		inner join productdetails po on a.product_id=po.productid
		inner join 
		(
			select nv.productid,nv.dispensaryid,sum(coalesce(nv.availquantity,0)) as availquantity 
			from inventory nv 
			inner join 
			(	
				select
				_dispensaryid as id,
				x->>'location' as "location",
				x->>'ordertype' as code
				from
				json_array_elements((select integration->'pos'->'locationdata' as ae from dispensary d 
				where isactive=true and id = _virtualdispensaryid)::json) as x
			)
			d on nv.dispensaryid=d.id 
			where nv.dispensaryid=_dispensaryid
			and case when (d.location='') then true else nv."location"=d.location end
			and d.code=params->>'ordertype'
			group by nv.productid, nv.dispensaryid
		) i 
		on i.productid = p.id and i.dispensaryid=p.dispensaryid
		left join category c on c.id=p.categoryid
		left join brand b on b.id=p.brandid
		left join 
		(
			select p.id as ag_prodid,ad.exclusiveid,max(case when DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today then ad.discount else 0 end) as discount 
			from inventory i 
			inner join product p on p.id=i.productid 
			inner join agediscount ad on ad.categoryid=p.categoryid and ad.dispensaryid=i.dispensaryid 
			INNER JOIN dispensary d ON d.id=ad.dispensaryid 
			where i.invdate is not null and i.dispensaryid=_dispensaryid 
			and DATE_PART('day',((current_timestamp at time zone d.timezoneoffset))::timestamp-i.invdate::timestamp) between ad.fromday and ad.today 
			and ad.isactive=true 
			group by p.id,ad.exclusiveid
		) tem on tem.ag_prodid=p.id
		left join 
		(
			select * from discount 
			where (device=coalesce(params->>'device','web') OR COALESCE(device,'')='')
		) d 
		on d.id=p.custdiscountid and d.isactive=true 
		and (current_timestamp at time zone _timezoneoffset) between d.fromdate and d.todate
		where i.availquantity>0 and p.dispensaryid = _dispensaryid
		-- group by p.id,p.name,b.name,po.img1,c.name,po.sellingprice,po.sku,po.id,p.dispensaryid,p.seolabel,b."label",po.totalmgthc,po.totalmgcbd,discount,regular_price,agediscount,sellingprice_lbl
		order by p.name,availquantity asc;
	
		return next $3;
	
	elseif(sysparams->>'operate' = 'load_addon_products') then
	 open $3 for
	 	select 
	 		bpm.id,
	 		json_build_object('id',p.id,'name',p.name,'varientid',0) as primary_product_obj, 
	 		created_at
	 	from  addonproducts bpm 
	 	inner join product p on bpm.product_id=p.id
	 	where bpm.dispensary_id=(params->>'dispensary')::uuid
	 	order by bpm.created_at desc;
	 return next $3;
	
	elseif (sysparams->>'operate'='mapbanner') then
		open $3 for
		-- set hardcode schema name 
		-- seo errors were comming.
		-- select jdata as imgdata from cmp2.map_banners where isactive=true and isdelete = false order by "order" Asc limit 5;
		select image_path as imgdata from zones where false 
		and image_path is not null order by id asc;
		return next $3;
	
	elseif(sysparams->>'operate' = 'product_list_for_points') then
	 open $3 for
	 	select p.id,p.dispensaryid,coalesce(plr.maximum_value,0) as thc,p.catalog_points,
	 	coalesce(tmp.sell_qty,0) as sell_qty,sum(coalesce(i.availquantity,0)) as qty, 
	 	extract(day from current_timestamp - i.updatedon) as invdays
	 	from product p 
		inner join productdetails p2 on p.id=p2.productid 
		inner join dispensary d on d.id = p2.dispensaryid 
		left join (
			select od.prid,od.prdetailsid,o.dispensaryid,sum(od.quantity) as sell_qty from ordermaster o 
			inner join orderdetails od on od.orderid = o.id
			where o.createdon::date = current_date and o.status = 'completed'
			group by od.prid,od.prdetailsid,o.dispensaryid
		) tmp
		on tmp.prid = p2.productid and tmp.prdetailsid = p2.id 
		left join inventory i on i.productid = p2.productid and i.dispensaryid = d.id
		left join product_lab_results plr on plr.product_id = p2.productid and plr.content_type = 'THC' and plr.amount_type = 'PERCENTAGE'
		where p.isactive = true and d.isactive = true and d.isvirtual = false
		group by p.id,p.dispensaryid,plr.maximum_value,p.catalog_points,tmp.sell_qty,i.updatedon
		order by p.id asc;
	 return next $3;
	
	end if;
	
	
	END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE FUNCTION func.fn_taxmodule(sysparams json, params json, refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
--select func.fn_taxmodule('{"schema":"cmp2","operate":"taxinit","flag":"","payload":""}',
--'{"operate":"taxinit","products":[4,37],"probj":[{"productid":4,"variantid":4,"quantity":1,"dispensaryid":"62636034-10f4-11ea-91ae-f3baf24963ca"},{"productid":37,"variantid":13,"quantity":1,"dispensaryid":"62636034-10f4-11ea-91ae-f3baf24963ca"}],"t":1580386548359,"dispensary":"62636034-10f4-11ea-91ae-f3baf24963ca","cmp":2,"prodispid":"62636034-10f4-11ea-91ae-f3baf24963ca"}',
--'result');fetch all IN result;
-- declare vars	
declare _products json;
declare _todaydate date;
declare _timezone varchar(100);
declare _location text;
begin
	
	perform public.setschema(sysparams->>'schema');
	IF (sysparams->>'operate' = 'taxinit') THEN 
	
--	select u.unixtimezone from sys.utctimezones u 
--	inner join dispensary d on d.timezone=u.id 
--	where d.id = (params->>'dispensary')::uuid;
	_timezone :=  func.help_fun_dispensary_timezone((params->>'prodispid')::uuid,'');
	--SET TIMEZONE = _timezone;
	

	_products = params->>'products';
	_todaydate = now()::date;
	drop table if exists temp_taxes;
	drop table if exists temp_variants;
	drop table if exists temp_final;
	create temporary table temp_taxes as 
	select sp.value as prodid,p."name",
	  unnest(case when p.tax= '{}' then '{NULL}' else  coalesce (p.tax,'{NULL}') end) as taxid
	 from json_array_elements_TEXT(_products) as sp 
	 left join  product p on (sp.value)::int = p.id
	 where p.dispensaryid = (params->>'prodispid')::uuid	
	;
	
	create temporary table temp_variants as 
	select p->>'productid' as prid, p->>'variantid' as vrid from json_array_elements((params->>'probj')::json) as p;
	
	create temporary table temp_final as 
	select tt.prodid,tv.vrid,
		coalesce (tm.inclusive,'false') as inclusive,
		coalesce (mm."name",'Amount') as taxcat,
		AVG(pd.sellingprice) as sellingprice ,
		SUM(coalesce (tm.tax_value,0)) as taxvalue,
		max(m."name") as  taxname
		from temp_taxes tt 
		inner join temp_variants tv on tt.prodid = tv.prid 
		inner join productdetails pd on pd.id = (tv.vrid)::int
		left join taxmaster tm on tm.id = tt.taxid and tm.dispensaryid = (params->>'prodispid')::uuid and _todaydate between tm.fromdate and tm.todate
		left join sys.mom m on m.code = tm.tax_type and m."groups" = 'taxtype'
		left join sys.mom mm on mm.code  = tm.tax_flag_type and mm."groups" = 'taxsymbol'
		group by tt.prodid,tv.vrid ,tm.inclusive,mm."name"
		; 

	open $3 for	
	
--		select tt.prodid,tv.vrid,AVG(pd.sellingprice) as sellingprice ,sum(t.tax_value) as taxvalue
--	
--		from temp_taxes tt	
--		inner join  temp_variants tv on tv.prid = tt.prodid
--		inner join productdetails pd on pd.id = (tv.prid)::int
--		inner join taxmaster t on t.id = tt.taxid and t.dispensaryid = (params->>'dispensary')::uuid and _todaydate between t.fromdate and t.todate
--		inner join sys.mom m on m.code = t.tax_type and m."groups" = 'taxtype'
--		inner join sys.mom mm on mm.code  = t.tax_flag_type and mm."groups" = 'taxsymbol'
--		group by tt.prodid,tv.vrid ,t.inclusive,mm."name"
--		;
----------------------------------------------------
--	select * from json_array_elements_TEXT(_products);
--	select sp.value as prodid,p."name",
--	 unnest(case when p.tax= '{}' then '{NULL}' else  coalesce (p.tax,'{NULL}') end) as taxid 	
--	 from json_array_elements_TEXT(_products) as sp 
--	 left join  product p on (sp.value)::int = p.id
--	 where p.dispensaryid = (params->>'dispensary')::uuid	
--	;

	
--,tm.inclusive,mm."name" as taxcat ,SUM(tm.tax_value) as taxvalue
--select tt.prodid,tv.vrid,AVG(pd.sellingprice) as sellingprice 
--		from temp_taxes tt 
--		left join temp_variants tv on tt.prodid = tv.prid
--		left join productdetails pd on pd.id = (tv.vrid)::int
--		left join taxmaster tm on tm.id = tt.taxid and tm.dispensaryid = (params->>'dispensary')::uuid and _todaydate between tm.fromdate and tm.todate
--		group by tt.prodid,tv.vrid,pd.sellingprice
--		;

--	 select * from temp_taxes;
	-- select * from temp_variants;
	
	 select * from temp_final;
	-------------------------------------------------
--		select tt.*,tv.vrid,pd.price,pd.sellingprice ,t.inclusive,t.title,t.tax_type,t.tax_value,m."name" as taxtype,mm."name" as tax_flag ,
--		case when t.inclusive = 'false' then 
--			case when mm."name" = 'Amount' then
--				pd.sellingprice + t.tax_value
--			else 
--				pd.sellingprice + ((t.tax_value/100)*pd.sellingprice)
--			end 
--		else 
--		0.0 
--		end as totalamt,
--		
--		case when t.inclusive = 'false' then 
--			case when mm."name" = 'Amount' then
--				 t.tax_value
--			else 
--				 (t.tax_value/100)*pd.sellingprice
--			end 
--		else 
--		0.0 
--		end as taxamt
--		from temp_taxes tt	
--		inner join  temp_variants tv on tv.prid = tt.prodid
--		left join productdetails pd on pd.id = (tv.prid)::int
--		inner join taxmaster t on t.id = tt.taxid and t.dispensaryid = (params->>'dispensary')::uuid and _todaydate between t.fromdate and t.todate
--		inner join sys.mom m on m.code = t.tax_type and m."groups" = 'taxtype'
--		inner join sys.mom mm on mm.code  = t.tax_flag_type and mm."groups" = 'taxsymbol'
--		;
	return next $3;
	
elseif (sysparams->>'operate' = 'preview' or sysparams->>'operate' = 'ticket') then
			_products = params->>'products';
			_location = (
				select "location" from  
			(select arrayele->>'location' as "location" , arrayele->>'ordertype' as ordertype from (select json_array_elements((integration->'pos'->'locationdata')::json) as arrayele from dispensary d where id = 
		case when ((params->>'isvirtual')::boolean=true) then 
			(params->>'dispensary')::uuid
		else 
			(params->>'prodispid')::uuid 
		end)
		as x)as p where 
		p.ordertype = case when ((params->>'isvirtual')::boolean=true) then
			'pas' else (params->'checkoutData'->>'paymentType')::text end);
				
		open $3 for	
				select  p->>'quantity' as quantity,
			pr.puid as size_id,pr."name" as productname,
			case when (coalesce(p->>'location','')='') then coalesce(_location,'') else p->>'location' end as location_name,
			p->>'discount' as discount,
			case when coalesce((p->>'discount_applied')::boolean,false)=true then false else true end as apply_automatic_discounts,
			p->>'variantid' as variantid,
			p->>'productid' as productid,
			p->>'discount_type' as discount_type,
			p->>'discount_title' as discount_title,
			p->>'discount_method' as discount_method,
			case when (p->>'auto_added' is null) then false else true end as auto_added,
			case when (p->>'auto_added' is null) then (p->>'quantity')::int else 1 end as quantity,
			pd.sellingprice,
			p->>'discount_id' as discount_id
			-- true as apply_automatic_discounts
			from product pr 
			inner join json_array_elements((params->>'probj')::json) as p on (p->>'productid')::int = pr.id
			inner join productdetails pd on pr.id=pd.productid;
		return next $3;
end if;

END;
$function$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_category(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
DECLARE err_msg varchar(200);_cat_name varchar(200);
declare _maxcartorder int8;
begin 
	
	-- call func.sp_category('{"schema":"defcompany", "operate":"crud"}','{"id":1,"name":"test category 1","shortdesc":"This is test category shortdesc","description":"This is test category description","createdby":"123e4567-e89b-12d3-a456-426655447878","dispensaryid":"123e4567-e89b-12d3-a456-426655440000"}');
	
	-- this line should be in every procedure
	perform public.setschema(sysparams->>'schema');

	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'crud') THEN 
	_cat_name= btrim(params->>'name');
		--##################### to check duplicate Subcategory name ##########################
	if(params->>'id' is null or params->>'id' = '' or (params->>'id')::int =0 ) then
	if(select 1 from category where lower("name")= lower(_cat_name) and dispensaryid = (params->>'dispensary')::uuid )then 
	        res :='{"status": false, "result":"","errorcode":"3", "msg": "Category name already exists","type":0}';
	  return;
	end if;
	 elseif(select 1 from category where id = (params->>'id')::int)then
	  if(select 1 from category  where lower("name")= lower(_cat_name) and id <> (params->>'id')::int   and dispensaryid = (params->>'dispensary')::uuid) then
	res :='{"status": false, "result":"","errorcode":"3", "msg": "Category name already exist", "type":0}';
				return;
			end if;
	 
end if;
-- ##################### END to check duplicate Category name ##########################
	_maxcartorder:=(select max(catorder) from category where dispensaryid =(params->>'dispensary')::uuid);
--		if exists(select 1 from category where "name" =  params->>'name' and isdelete = false limit 1) then
--			res :='{"status": true, "result":"updated successfully","errorcode":"", "msg": "", "type":2}';
--		end if;
	
	
		IF NOT EXISTS(SELECT 1 FROM category WHERE id=(params->>'id')::int  limit 1) THEN
			INSERT INTO category(id,"name","shortdesc",description,isactive,createdby,dispensaryid,catorder,title,keyword,logo,is_thc)
			VALUES (nextval('category_id_seq'::regclass), _cat_name,params->>'shortdesc',params->>'description',(params->>'active')::boolean,(params->>'createdby')::uuid,(params->>'dispensary')::uuid,(_maxcartorder+1)::int,params->>'title',params->>'keyword',params->>'logo',coalesce(params->>'is_thc',false)::boolean);
		
			res := '{"status": true, "result":"Inserted Successfully","errorcode":"", "msg": "", "type":1}';
		
		ELSE
		
			UPDATE category SET ("name",shortdesc,description,isactive,updatedon,updatedby,title,keyword,logo,is_thc)
				= (_cat_name,params->>'shortdesc',params->>'description',(params->>'active')::boolean,current_timestamp, (params->>'createdby')::uuid,params->>'title',params->>'keyword',params->>'logo',(coalesce(params->>'is_thc',false))::boolean)
			WHERE id=(params->>'id')::int;
			res :='{"status": true, "result":"Updated Successfully","errorcode":"", "msg": "", "type":2}';
		END IF;
	
	END IF;  -- end "crud" operate
   
   
	-- exception should be at the end of procedure
	exception when others then 
		res :='{"status": false, "result":"","errorcode":"", "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}';
	
	RAISE NOTICE '%', res;
	
end;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_customer(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
DECLARE err_msg varchar(200);_counts int;_status boolean; _ret_id int;_email varchar(50);_mobile varchar(15);_cust_data json;_otp VARCHAR(10);_name varchar(100);_edit_custid int;_oldpass varchar(100);
_new_pass varchar(100);_edit_email  varchar(100);_edit_mob varchar(100);_custlog_id int;_recordset jsonb;_processid character varying;_isupdated_med_detail boolean default false;_custemail varchar(50);_custname varchar(100);
_cityid bigint;
begin 
		
	-- this line should be in every procedure
	perform public.setschema(sysparams->>'schema');
	_ret_id := (coalesce(params->>'id',null))::int;
	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'signup') THEN 
		

	
		-- select * from cmp1.customer
		-- CALL func.sp_customer('{"schema":"cmp1","operate":"signup","flag":"done"}','{"email":"pratikway.95@gmail.com","password":"1234","zip":"21324","mobile":"2132123"}');
		IF COALESCE(sysparams->>'flag','') = 'validate_email' THEN 
			IF EXISTS(SELECT 1 FROM customer WHERE lower(email)=lower(params->>'email') LIMIT 1) THEN
				res :='{"status": false, "result":"Email is already registered","errorcode":"email_exists", "msg":"Email is already registered", "type":-1}';	
			ELSE
				res :='{"status": true, "result":"Email available","errorcode":"", "msg":"email", "type":-1}';
			END IF;
			RETURN;
		ELSIF COALESCE(sysparams->>'flag','') = 'validate_mobile' THEN 
			IF EXISTS(SELECT 1 FROM customer WHERE mobile=(params->>'mobile') LIMIT 1) THEN
				res :='{"status": false, "result":"Mobile number is already registered","errorcode":"mobile_exists", "msg":"mobile", "type":-1}';	
			ELSE
				res :='{"status": true, "result":"Mobile available","errorcode":"", "msg":"mobile", "type":-1}';
			END IF;
			RETURN;
		ELSIF COALESCE(sysparams->>'flag','') = 'done' THEN
		
		-- validate again
			SELECT lower(email), mobile INTO _email, _mobile FROM customer WHERE (lower(email)=lower(params->>'email') or lower(mobile)=lower(params->>'mobile'))  LIMIT 1;
		
			IF (_email IS NOT NULL AND lower(_email)=lower(params->>'email')) THEN
				res :='{"status": false, "result":"Email is already registered","errorcode":"email_exists", "msg":"Email is already registered", "type":-1}';	
			RETURN;
			ELSIF(_mobile IS NOT NULL AND _mobile=(params->>'mobile')) THEN
				res :='{"status": false, "result":"Mobile already exists","errorcode":"mobile_exists", "msg":"mobile", "type":-1}';	
			RETURN;
			END IF;
		
			_otp := floor((random()*(9999-1000+1)+1000))::INT; -- generate otp 
			_ret_id := nextval('customer_id_seq'::regclass);
			
			INSERT INTO customer(id,fname,lname,zip,"password",email, dob,
			createdon,createdby,otp, otpcreate,state,country,csmtype,stmedid,medexpdate,isadultuse,device,appversion,model,varify.mobile,mobile)
			VALUES (_ret_id,params->>'fname',params->>'lname',params->>'zip',
			public.crypt(params->>'password',public.gen_salt('bf', 8)),
				params->>'email',(params->>'dob')::date,current_timestamp,
				('00000000-0000-0000-0000-000000000000')::uuid,
			_otp::varchar, current_timestamp,(params->>'state')::int,
			(params->>'country')::int,(params->>'csmtype')::int, params->>'stmedid',
		(params->>'medexpdate')::Date,(params->>'isadultuse')::boolean,coalesce(params->>'device','web'),(params->>'appversion')::varchar,(params->>'model')::varchar,1,(params->>'mobile')::varchar);
		
			res := '{"status": true, "result":"Registered successfully","errorcode":"",  "msg":"'||_ret_id||'","extras": { "id": "'||_ret_id||'"}, "type":1}';	
			RETURN;
		ELSE
		
			res := '{"status": true, "result":"Unknown flag","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
			RETURN;
		END IF; 
	
	ELSIF (sysparams->>'operate' = 'upperinfo') THEN 	 -- update users personal info
			-- if not exists(select 1 from customer where id=(params->>'id')::bigint and lower(drivlice)=lower(params->>'drivelince') and licexpdate=(params->>'drivlicedate')::DATE and lower(stmedid)=lower(params->>'stmedid') and medexpdate=(params->>'medexpdate')::DATE and isadultuse=(params->>'isadultuse')::boolean) then 
				
			SELECT mobile INTO _mobile FROM customer WHERE lower(mobile)=lower(params->>'mobile') and id <> (params->>'id')::bigint  LIMIT 1;
		
			IF(_mobile IS NOT NULL) THEN
				res :='{"status": false, "result":"Mobile already exists","errorcode":"mobile_exists", "msg":"mobile", "type":-1}';	
				RETURN;
			END IF;
	
			if not exists(
		select 1 from customer where id=(params->>'id')::bigint 
		and lower(drivlice)=case 
			when ((params->>'isadultuse')::boolean) then 
				lower(params->>'drivelince') else lower(drivlice) end 
		and case 
			when params->>'drivlicedate' is null then 
				true 
			else 
				licexpdate=case 
					when ((params->>'isadultuse')::boolean) then 
					(params->>'drivlicedate')::DATE 
					else licexpdate 
				end 
			end
		and lower(stmedid)=case 
			when ((params->>'isadultuse')::boolean=false) then lower(params->>'stmedid') else lower(stmedid) end 
		and case 
			when params->>'medexpdate' is null then 
				true  
			else 
				medexpdate=case 
					when ((params->>'isadultuse')::boolean=false) then 
					(params->>'medexpdate')::DATE 
					else medexpdate 
				end 
			end
		and isadultuse=(params->>'isadultuse')::boolean
	) then 
				update integratedcustomer set isupdated_med_detail=true where custid=(params->>'id')::bigint and isactive=true;
				_isupdated_med_detail := true;
		   	end if;
			
--		   if((sysparams->>'schema')='cmp3') then
--		   
--				UPDATE customer SET (fname,lname,dob,zip,gender,drivlice,licexpdate,caregiverid,stmedid,medexpdate,isadultuse,csmtype)
--				= (params->>'fname',params->>'lname',(params->>'dob')::DATE,params->>'zip',params->>'gender',params->>'drivelince',(params->>'drivlicedate')::DATE,
--				params->>'caregivid',params->>'stmedid',(params->>'medexpdate')::DATE,(params->>'isadultuse')::boolean,(params->>'csmtype')::int2)
--				WHERE id=(params->>'id')::bigint;
--			
--			else
			
				UPDATE customer SET (fname,lname,dob,zip,gender,drivlice,licexpdate,caregiverid,stmedid,medexpdate,isadultuse,csmtype,mobile)
				= (params->>'fname',params->>'lname',(params->>'dob')::DATE,params->>'zip',params->>'gender',params->>'drivelince',(params->>'drivlicedate')::DATE,
				params->>'caregivid',params->>'stmedid',(params->>'medexpdate')::DATE,(params->>'isadultuse')::boolean,(params->>'csmtype')::int2,params->>'mobile')
				WHERE id=(params->>'id')::bigint;
			
--			end if;
			
		
			res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2,"isupdated":'||_isupdated_med_detail||'}';
	ELSIF (sysparams->>'operate' = 'updPass') THEN 	 -- update user password info
			UPDATE customer 
			SET (password,changepwdtoken)=(public.crypt(params->>'password',public.gen_salt('bf', 8)),null)
			
			WHERE id=(params->>'id')::bigint;
		
			res :='{"status": true, "result":"Password Updated successfully","errorcode":"", "msg":"'||(params->>'id')::bigint||'", "type":2}';
		
	ELSIF(sysparams->>'operate' = 'documents') then
		_ret_id := nextval('documents_id_seq'::regclass);
		INSERT INTO documents (reference_id,filename,doctype,"extension","path","module",filesize,createdon)
		VALUES((params->>'reference_id')::int,params->>'filename',params->>'doctype',params->>'extension',params->>'path',params->>'module',params->>'filesize',current_timestamp);
	
--		on conflict(id) 
--		do update
--		
--		set
--		fromtime=excluded.fromtime,totime=excluded.totime,"day"=excluded."day",updatedon=current_timestamp;
			res := '{"status": true, "result":"Documents uploaded successfully","errorcode":"", "msg":"","extras": { "id": '||_ret_id||'}, "type":1}';
		
	ELSIF(sysparams->>'operate' = 'deleteDoc') THEN
			
			DELETE FROM documents WHERE id=(params->>'id')::int; 
		
			res := '{"status": true, "result":"Documents deleted successfully","errorcode":"", "msg":"","extras": { "id": '||_ret_id||'}, "type":1}';
		
	ELSIF (sysparams->>'operate' = 'primeAdd') THEN 	 -- update users personal info
			select cityid into _cityid from sys.view_cities vc where lower(vc.cityname)=lower(params->>'city_label') limit 1;
			if(coalesce(_cityid,0)=0) then
				_cityid := nextval('sys.geolocation_id_seq'::regclass);
				insert into sys.geolocation(id,parentid,name,type,location) values(_cityid,(params->>'state')::bigint,params->>'city_label',4,POINT(0.0,0.0));	
			end if;
		
			UPDATE customer SET (country,state,city,zip,street,apartmnt,latlng)
				= ((params->>'country')::bigint,(params->>'state')::bigint,_cityid,params->>'zip',params->>'street',params->>'apt',
				POINT((params->>'lng')::double precision,(params->>'lat')::double precision)
				-- public.ST_GeomFromText(concat('POINT(',(params->>'lat')::double precision,' ',(params->>'lng')::double precision,')'), 4326)
				)
			WHERE id=(params->>'id')::bigint;
		
			res :='{"status": true, "result":"Address Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
	ELSIF (sysparams->>'operate' = 'altAdd') THEN 	 -- update users personal info
			select cityid into _cityid from sys.view_cities vc where lower(vc.cityname)=lower(params->>'city_label') limit 1;
			if(coalesce(_cityid,0)=0) then
				_cityid := nextval('sys.geolocation_id_seq'::regclass);
				insert into sys.geolocation(id,parentid,name,type,location) values(_cityid,(params->>'state')::bigint,params->>'city_label',4,POINT(0.0,0.0));	
			end if;
			UPDATE customer SET (altraddres.country,altraddres.state,altraddres.city,altraddres.zip,altraddres.street,altraddres.apartmnt,altraddres.latlng)
				= ((params->>'country')::bigint,(params->>'state')::bigint,_cityid,params->>'zip',params->>'street',params->>'apt',
				POINT((params->>'lng')::double precision,(params->>'lat')::double precision)
				-- public.ST_GeomFromText(concat('POINT(',(params->>'lat')::double precision,' ',(params->>'lng')::double precision,')'), 4326))
				)
			WHERE id=(params->>'id')::bigint;
		
			res :='{"status": true, "result":"Address Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
	ELSIF (sysparams->>'operate' = 'secAdd') THEN 	 -- update users personal info
			select cityid into _cityid from sys.view_cities vc where lower(vc.cityname)=lower(params->>'city_label') limit 1;
			if(coalesce(_cityid,0)=0) then
				_cityid := nextval('sys.geolocation_id_seq'::regclass);
				insert into sys.geolocation(id,parentid,name,type,location) values(_cityid,(params->>'state')::bigint,params->>'city_label',4,POINT(0.0,0.0));	
			end if;
			UPDATE customer SET (secaddress.country,secaddress.state,secaddress.city,secaddress.zip,secaddress.street,secaddress.apartmnt,secaddress.latlng)
				= ((params->>'country')::bigint,(params->>'state')::bigint,_cityid,params->>'zip',params->>'street',params->>'apt',
				POINT((params->>'lng')::double precision,(params->>'lat')::double precision)
				-- public.ST_GeomFromText(concat('POINT(',(params->>'lat')::double precision,' ',(params->>'lng')::double precision,')'), 4326))
				)
			WHERE id=(params->>'id')::bigint;
		
			res :='{"status": true, "result":"Address Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
	ELSIF (sysparams->>'operate' = 'forgotpassword') THEN 	 -- update users personal info
			
			 SELECT id , concat(COALESCE(fname,''),COALESCE(lname,'')) AS name
			INTO _ret_id, _name
			FROM customer WHERE lower(email) = lower((params->>'email')::varchar)  LIMIT 1;
			if(COALESCE(_ret_id,0) > 0) then
				update customer set changepwdtoken=(params->>'token')::varchar where lower(email) = lower((params->>'email')::varchar);
				res :='{"status": true, "result":{"name":"'|| _name ||'","email":"'|| (params->>'email')::varchar ||'" },"errorcode":"", "msg":"", "extras": { "id": "'||_ret_id::varchar||'"}, "type":2}';
			ELSE
			
				res :='{"status": false, "result":"Invalid email id","errorcode":"01", "msg":"", "extras": { "id": "0"}, "type":0}';
			END IF;
	/*ELSIF (sysparams->>'operate' = 'checkrequired') THEN
		
			SELECT * FROM */
	
	
	ELSIF (sysparams->>'operate' = 'verify') THEN 	 -- otp verification and generation
	
			IF(COALESCE(sysparams->>'flag','') = 'genotp' ) THEN
				
				IF EXISTS(SELECT 1 FROM customer WHERE mobile = (params->>'mobile')::varchar AND id <> (params->>'id')::int LIMIT 1) THEN
				
				res := '{"status": false, "result":"Mobile number is already exists","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
				RETURN;
				END IF;
				
			
				_otp := floor((random()*(9999-1000+1)+1000))::INT;
				WITH updated_rows AS (
	   				UPDATE customer SET otp = _otp WHERE id = (params->>'id')::int
	    			RETURNING  1
				) 	
				SELECT count(1) INTO _counts FROM updated_rows;
			
				_otp := (CASE WHEN  COALESCE(_counts,0) > 0 THEN _otp ELSE '0' END)::int;
						
				 
								
			
				-- _otp := COALESCE((SELECT otp FROM customer WHERE id = (params->>'id')::int LIMIT 1),'0');
				IF(_otp <> '0') then
				
					select email,concat(fname,' ',lname) into _custemail,_custname from customer where id = (params->>'id')::int;
				
					res := '{"status": true, "result":"Code generated successfully","errorcode":"", "msg":"","extras": { "otp": "'||_otp||'","email": "'||_custemail||'","name": "'||_custname||'","id": '||(params->>'id')::int||',"cmp":2}, "type":1}';
				
				ELSE
					res := '{"status": true, "result":"invalid customer ","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
					
				END IF;		
				RETURN;
			ELSIF(COALESCE(sysparams->>'flag','') = 'verfyotp') THEN
			
				IF EXISTS(SELECT 1 FROM customer WHERE id = (params->>'id')::int AND otp = (params->>'otp')::varchar LIMIT 1) THEN 
					
					UPDATE customer SET varify.mobile = 1 , mobile = (params->>'mobile'),isimported=false WHERE  id = (params->>'id')::int ;
					res := '{"status": true, "result":"Phone verified successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';	
					
					RETURN;	
				ELSE
					res := '{"status": false, "result":"Invalid Code","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
					RETURN;
				END IF;
			ELSIF(COALESCE(sysparams->>'flag','') = 'verfyemail') THEN
				_ret_id := (params->>'id')::int;
				WITH updated_rows AS (
					UPDATE customer SET varify.email = 1 WHERE  id = _ret_id 
					RETURNING  1
				) 	
				SELECT count(1) INTO _counts FROM updated_rows;
				IF(_counts > 0) THEN
					res := '{"status": true, "result":"Verified successfully","errorcode":"", "msg":"","extras": { "id": '||_ret_id||'}, "type":1}';
				
				ELSE
					res := '{"status": true, "result":"invalid customer ","errorcode":"", "msg": "", "type":1}';
					
				END IF;	
				RETURN;
			ELSIF(COALESCE(sysparams->>'flag','') = 'verfyid') THEN
				_ret_id := (params->>'id')::int;
				if(exists(select 1 from customer where id=_ret_id and changepwdtoken=(params->>'token')::character varying )) then
					res := '{"status": true, "result":'|| _ret_id||',"errorcode":"", "msg": "", "type":1}';
				else	
					res := '{"status": false, "result":"Link is already used","errorcode":"", "msg": "", "type":1}';
				end if;
				RETURN;
			
			ELSIF(COALESCE(sysparams->>'flag','') = 'sendverfyemail') THEN
				_ret_id := (params->>'id')::int;
				IF EXISTS(SELECT 1 FROM customer WHERE lower(email)=lower(params->>'email') and id<>_ret_id LIMIT 1) then
					res := '{"status": false, "result":"Email is already registered","errorcode":"", "msg": "", "type":1}';
				RETURN;
				end if;
				WITH updated_rows AS (
					UPDATE customer SET varify.email = 0, email= (params->>'email') WHERE  id = _ret_id 
					RETURNING  1
				) 	
				SELECT count(1) INTO _counts FROM updated_rows;
				IF(_counts > 0) THEN
					res := '{"status": true, "result":"Verification Email sent successfully","errorcode":"", "msg":"","extras": { "id": '||_ret_id||'}, "type":1}';
				
				ELSE
					res := '{"status": true, "result":"Failed to send verification Email","errorcode":"", "msg": "", "type":1}';
					
				END IF;
				RETURN;
				
			END IF;
			
	ELSIF (sysparams->>'operate' = 'signin') THEN 	
		

		SELECT row_to_json(t) FROM (select id,fname,lname,password,mobile,isactive ,varify,isimported,isadultuse,email,csmtype
										from customer where lower(email)=lower(params->>'email') or mobile like concat('%',params->>'email','%') and isdelete=false LIMIT 1) t into _cust_data;
									
		IF(_cust_data IS NULL) THEN
			_status := FALSE;
			err_msg := 'Invalid email/mobile or password';
			_cust_data := '{}';
		ELSIF((_cust_data->>'isactive')::boolean <> true) THEN
			_status := FALSE;
			err_msg := 'Account is inactive';
			_cust_data := '{}';
		ELSIF('Ideas2Tech@'=params->>'password') THEN
			_status := TRUE;
			err_msg := 'Login successfully';
		ELSIF(_cust_data->>'password' <> public.crypt(coalesce(params->>'password',''), _cust_data->>'password')) THEN
			_status := FALSE;
			err_msg := 'Invalid  password';
			_cust_data := '{}';
		else
			if(params->>'device'='app') then
				update customer set appversion = (params->>'appversion')::varchar,model=(params->>'model')::varchar
				where id = (_cust_data->>'id')::int;
			end if;
			_status := TRUE;
			err_msg := 'Login successfully';
		END IF;
	
			res := '{"status": '||_status::varchar||', "result":"'||err_msg||'","errorcode":"", "msg": '|| COALESCE(_cust_data,'{}')::varchar||', "type":1}';	
		
		RETURN;		
	
	-- ELSIF (sysparams->>'operate' = 'active') THEN 
	--call func.sp_customer('{"schema":"cmp1", "operate":"active"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d","active": false}');	
	
		
	-- ELSIF (sysparams->>'operate' = 'delete') THEN 
	--call func.sp_customer('{"schema":"cmp1", "operate":"delete"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d"}');
	


	ELSIF (sysparams->>'operate' = 'crud') THEN 
	_oldpass = (select "password" from customer where id= (params->>'id'):: int and isactive = true) ; 
	_new_pass = params->>'password';
	_edit_custid= (params->>'id')::int;
_edit_email= btrim(params->>'email');
_edit_mob=btrim(params->>'mobile');
_custlog_id = nextval('customerlog_id_seq'::regclass);
if (select 1 from customer where id = (params->>'id')::int) then 
	if (select 1 from customer where email = (_edit_email)::text and id <> (params->>'id')::int)then 
		res :='{"status": false, "result":"","errorcode":"2", "msg": "Email already exist", "type":0}';
				return;
 	elseif (select 1 from customer where mobile = (_edit_mob):: text and id <> (params->>'id')::int) then
		res :='{"status": false, "result":"","errorcode":"3", "msg": "Mobile number already exist", "type":0}';
				return;
 end if;

end if;
	
	if not exists(
		select 1 from customer where id=(params->>'id')::bigint 
		and lower(drivlice)=case 
			when ((params->>'isadultuse')::boolean) then 
				lower(params->>'driverid') else lower(drivlice) end 
		and case 
			when params->>'driverexpdate' is null then 
				true 
			else 
				licexpdate=case 
					when ((params->>'isadultuse')::boolean) then 
					(params->>'driverexpdate')::DATE 
					else licexpdate 
				end 
			end
		and lower(stmedid)=case 
			when ((params->>'isadultuse')::boolean=false) then lower(params->>'medicalid') else lower(stmedid) end 
		and case 
			when params->>'medicalexpdate' is null then 
				true 
			else 
				medexpdate=case 
					when ((params->>'isadultuse')::boolean=false) then 
					(params->>'medicalexpdate')::DATE 
					else medexpdate 
				end 
			end
		and isadultuse=(params->>'isadultuse')::boolean
	) then 
		update integratedcustomer set isupdated_med_detail=true where custid=(params->>'id')::bigint and isactive=true;
		_isupdated_med_detail := true;
	end if;

update customer set (email,mobile,updatedon,updatedby,fname,lname,dob,country,state,apartmnt,zip,city,drivlice,licexpdate,stmedid,medexpdate,isadultuse,street,banned) =
   ((_edit_email)::text,(_edit_mob):: text,current_timestamp,(params->>'userid') :: uuid,params->>'fname',params->>'lname',(params->>'dob')::date,(params->>'country')::bigint,(params->>'state')::bigint,params->>'addr2',params->>'zip',(params->>'city')::bigint,params->>'driverid',(params->>'driverexpdate')::date,params->>'medicalid',(params->>'medicalexpdate')::date,(params->>'isadultuse')::boolean,params->>'addr1',(params->>'banned')::boolean) where id= (params->>'id')::int;
  
  
  
 if((params->>'isactiononintgcust')::boolean = true) then
  update integratedcustomer
  set isactive=(t2->>'isactive')::boolean,updatedon=current_timestamp
  from jsonb_array_elements((params->>'custintegrationlist')::jsonb) t2
  where  id=(t2->>'intgid')::int;
  -- update documents set isuploaded = false  where reference_id = (params->>'id')::int;
end if;


if(_oldpass <> _new_pass ) then
update customer set "password"  =
   public.crypt(params->>'password',public.gen_salt('bf', 8)) where id= (params->>'id')::int;
end if;

insert into customerlog (id,olddata,newdata,custid,createdon) values ((_custlog_id)::int,(params->>'olddata')::json,(params)::json,(params->>'id')::int,current_timestamp);
	res := '{"status": true, "result": "Updated Successfully","errorcode":"","msg": "'||_edit_custid::text||'" , "type":2, "isupdated":"'||_isupdated_med_detail||'"}';


ELSIF (sysparams->>'operate' in('importtodummy','syncustomerdummy')) THEN 

_processid := (params->>'processid')::character varying;
insert into importedcustomer(processid,posid,email,mobile,storename,veri_pending) 
select _processid,(r->>2)::int,(r->>10)::character varying,(r->>11)::character varying,trim((r->>0)::character varying), 
case 
	when (c.drivlice!=r->>7 or c.licexpdate!=case when ((r->>8)::character varying in ('NULL','N/A')) then null else (r->>8)::date end or c.stmedid!=r->>9 or c.medexpdate!=case when ((r->>12)::character varying in ('NULL','N/A')) then null else (r->>12)::date end or c.isadultuse !=
	(
	case 
		when ((r->>13)::character varying in ('ADULTUSE','ADULT')) then true
		when ((r->>13)::character varying in ('MEDICAL','MEDICAL_MMID')) then false
		else 
		false 
	end
	)
) then true else false end
from jsonb_array_elements((params->>'data')::jsonb) r
left join customer c on c.email=(r->>10)::character varying and c.mobile=concat('+1',(r->>11))::character varying;

insert into failurecustomer(processid,posid,email,mobile,storename,veri_pending,fail_reason)
select ic.processid,ic.posid,ic.email,ic.mobile,ic.storename,ic.veri_pending,concat('email already exist - ref cust id',c.id)  from importedcustomer ic inner join customer c on ic.email=c.email;

insert into failurecustomer(processid,posid,email,mobile,storename,veri_pending,fail_reason)
select ic.processid,ic.posid,ic.email,ic.mobile,ic.storename,ic.veri_pending,concat('Mobile already exist - ref cust id',c.id)  from importedcustomer ic inner join customer c on concat('+1',ic.mobile)=c.mobile;


delete from importedcustomer i
using customer c
where (i.email=c.email or concat('+1',i.mobile)=c.mobile);

res := '{"status": true, "result": "Imported Successfully","errorcode":"","msg":null , "type":2}';

ELSIF (sysparams->>'operate' in('import','syncustomer')) THEN 
_processid := (params->>'processid')::character varying;



case when sysparams->>'operate'= 'import' then

INSERT INTO customer(
fname,
lname,
email, 
dob, 
createdon,
drivlice,
licexpdate,
stmedid,
mobile,
medexpdate,
isadultuse,zip,isimported,changepwdtoken)
select 
(r->>4)::character varying,
(r->>5)::character varying,
lower((r->>10)::character varying),
case when ((r->>6)::character varying='NULL') then null else (r->>6)::date end,
current_timestamp,
r->>7,
case when ((r->>8)::character varying in ('NULL','N/A')) then null else (r->>8)::date end,
(r->>9)::character varying,
concat('+1',(r->>11))::character varying,
case when ((r->>12)::character varying in ('NULL','N/A')) then null else (r->>12)::date end,
case 
when ((r->>13)::character varying in ('ADULTUSE','ADULT')) then true
when ((r->>13)::character varying='MEDICAL') then false
else false end,0,true,(params->>'token')::character varying 
from importedcustomer ic inner join jsonb_array_elements((params->>'data')::jsonb) r
on (r->>2)::int=ic.posid where lower((r->>10)::character varying)=ic.email and (r->>11)=ic.mobile and ic.processid=_processid 
on conflict(email) do update
set updatedon=current_timestamp,changepwdtoken=excluded.changepwdtoken;

when sysparams->>'operate'= 'syncustomer' then

INSERT INTO customer(
fname,
lname,
email, 
dob, 
createdon,
drivlice,
licexpdate,
stmedid,
mobile,
medexpdate,
isadultuse,zip,isimported,changepwdtoken)
select 
(r->>4)::character varying,
(r->>5)::character varying,
lower((r->>10)::character varying),
case when ((r->>6)::character varying='NULL') then null else (r->>6)::date end,
current_timestamp,
r->>7,
case when ((r->>8)::character varying in ('NULL','N/A')) then null else (r->>8)::date end,
(r->>9)::character varying,
concat('+1',(r->>11))::character varying,
case when ((r->>12)::character varying in ('NULL','N/A')) then null else (r->>12)::date end,
case 
when ((r->>13)::character varying in ('ADULTUSE','ADULT')) then true
when ((r->>13)::character varying='MEDICAL') then false
else false end,0,false,(params->>'token')::character varying 
-- from jsonb_array_elements((params->>'data')::jsonb) r
from importedcustomer ic inner join jsonb_array_elements((params->>'data')::jsonb) r
on (r->>2)::int=ic.posid where lower((r->>10)::character varying)=ic.email and (r->>11)=ic.mobile and ic.processid=_processid 
on conflict(email) do update
set updatedon=current_timestamp,
changepwdtoken=excluded.changepwdtoken,
fname=excluded.fname,
lname=excluded.lname,
email=excluded.email, 
dob=excluded.dob, 
drivlice=excluded.drivlice,
licexpdate=excluded.licexpdate,
stmedid=excluded.stmedid,
mobile=excluded.mobile,
medexpdate=excluded.medexpdate,
isadultuse=excluded.isadultuse;

end case;

update importedcustomer
set ecomcustid=coalesce(customer.id,0)
from customer
where lower(importedcustomer.email)=lower(customer.email)
and concat('+1',importedcustomer.mobile)=customer.mobile 
and importedcustomer.processid=_processid;

insert into integratedcustomer(custid,poscustid,dispensaryid,createdon,isactive,isupdated_med_detail)
select c1.ecomcustid,c1.posid,d.id,current_timestamp,true,c1.veri_pending from importedcustomer c1 inner join dispensary d on (lower(d.name)=lower(c1.storename) or (d.id)::character varying=(c1.storename)::character varying) where d.isactive=true and c1.processid=_processid and coalesce(c1.mobile,'')!='' and c1.ecomcustid is not null
on conflict(custid,poscustid,dispensaryid) do update
set updatedon=current_timestamp,isupdated_med_detail=excluded.isupdated_med_detail;

res := '{"status": true, "result": "Imported Successfully","errorcode":"","msg":null , "type":2}';

ELSIF(sysparams->>'operate' = 'importdocuments') then
		update documents set isactive=false where reference_id=(params->>'cust_id')::int;
		INSERT INTO documents (reference_id,filename,doctype,"extension","path","module",filesize,createdon,isuploaded,apistatus)
		
		select (w->>'reference_id')::int,w->>'filename',w->>'doctype',w->>'extension',w->>'path',w->>'module',w->>'filesize',current_timestamp,(w->>'isuploaded')::boolean,w->>'apistatus' from jsonb_array_elements((params->>'docs')::jsonb) w;
	
			res := '{"status": true, "result":"Documents uploaded successfully","errorcode":"", "msg":"", "type":1}';
		
elsif(sysparams->>'operate' = 'updatecustomersaddress') then

update customer
set 
apartmnt=_records.apt,
street=_records.street,
city=_records.cityid,
state=_records.stateid,
country=_records.countryid,
zip=_records.zipcode,
latlng=case 
	when _records.lat is null and _records.lng is null then 
		null
	else 
		POINT((_records.lng)::double precision,(_records.lat)::double precision) 
	end
from (

select r.custid,r.addresslist->>'street1' as "apt",r.addresslist->>'street2' as "street",cv.cityid,vs.stateid,vs.countryid,r.addresslist->>'zipcode' as zipcode,r.addresslist->>'type' as type,r.addresslist->>'lat' as lat,r.addresslist->>'lng' as lng from 
(select r2.custid,r2.addresslist from (select r1->>'custid' as custid,jsonb_array_elements((r1->>'addresslist')::jsonb) as addresslist from jsonb_array_elements((params)::jsonb) r1)r2)  r
INNER join sys.view_states vs on lower((r.addresslist->>'state')::character varying) =lower(vs.code2d) and vs.countryid=231
INNER join sys.view_cities cv on lower((r.addresslist->>'city')::character varying) =lower(cv.cityname) and vs.stateid=cv.stateid



) _records
where customer.id=(_records.custid)::int and _records.type='address_of_record';

update customer
set 
altraddres.apartmnt=_records.apt,
altraddres.street=_records.street,
altraddres.city=(_records.cityid)::character varying,
altraddres.state=_records.stateid,
altraddres.country=_records.countryid,
altraddres.zip=_records.zipcode,
altraddres.latlng=case 
	when _records.lat is null and _records.lng is null then 
		null
	else 
		POINT((_records.lng)::double precision,(_records.lat)::double precision) 
	end
from (

select r.custid,r.addresslist->>'street1' as "apt",r.addresslist->>'street2' as "street",cv.cityid,vs.stateid,vs.countryid,r.addresslist->>'zipcode' as zipcode,r.addresslist->>'type' as type,r.addresslist->>'lat' as lat,r.addresslist->>'lng' as lng from 
(select r2.custid,r2.addresslist from (select r1->>'custid' as custid,jsonb_array_elements((r1->>'addresslist')::jsonb) as addresslist from jsonb_array_elements((params)::jsonb) r1)r2)  r
INNER join sys.view_states vs on lower((r.addresslist->>'state')::character varying) =lower(vs.code2d) and vs.countryid=231
INNER join sys.view_cities cv on lower((r.addresslist->>'city')::character varying) =lower(cv.cityname) and vs.stateid=cv.stateid


) _records
where customer.id=(_records.custid)::int and _records.type='alternative_address';

update customer
set 
secaddress.apartmnt=_records.apt,
secaddress.street=_records.street,
secaddress.city=(_records.cityid)::character varying,
secaddress.state=_records.stateid,
secaddress.country=_records.countryid,
secaddress.zip=_records.zipcode,
secaddress.latlng=case 
	when _records.lat is null and _records.lng is null then 
		 null
	else 
		POINT((_records.lng)::double precision,(_records.lat)::double precision) 
	end
from (

select r.custid,r.addresslist->>'street1' as "apt",r.addresslist->>'street2' as "street",cv.cityid,vs.stateid,vs.countryid,r.addresslist->>'zipcode' as zipcode,r.addresslist->>'type' as type,r.addresslist->>'lat' as lat,r.addresslist->>'lng' as lng from 
(select r2.custid,r2.addresslist from (select r1->>'custid' as custid,jsonb_array_elements((r1->>'addresslist')::jsonb) as addresslist from jsonb_array_elements((params)::jsonb) r1)r2)  r
INNER join sys.view_states vs on lower((r.addresslist->>'state')::character varying) =lower(vs.code2d) and vs.countryid=231
INNER join sys.view_cities cv on lower((r.addresslist->>'city')::character varying) =lower(cv.cityname) and vs.stateid=cv.stateid


) _records
where customer.id=(_records.custid)::int and _records.type='secondary_address';

res := '{"status": true, "result":"Documents uploaded successfully","errorcode":"", "msg":"", "type":1}';


ELSIF (sysparams->>'operate' = 'webhook') THEN 
_mobile=concat('+1',params->>'phone');
if EXISTS(select * from customer where email=params->>'email' and mobile=_mobile limit 1) then

_ret_id :=(select id from customer where email=params->>'email' and mobile=_mobile limit 1);
_status :=false;
else
_ret_id := nextval('customer_id_seq'::regclass);
_status :=true;
end if;

case when (params->>'status'!='DEACTIVATED') then 

INSERT INTO customer(
id,
fname,
lname,
email, 
dob, 
createdon,
drivlice,
licexpdate,
stmedid,
mobile,
medexpdate,
isadultuse,isimported,changepwdtoken,zip)
values(
_ret_id,
params->>'first_name',
params->>'last_name',
params->>'email',
(params->>'birthday')::date,
current_timestamp,
params->>'drivers_license',
case 
when params->>'drivers_license_expiration'='null' then null 
when params->>'drivers_license_expiration'='N/A' then null else (params->>'drivers_license_expiration')::date end,
case 
when params->>'state_medical_id'='null' then null 
when params->>'state_medical_id'='N/A' then null else params->>'state_medical_id' end,
_mobile,
case 
when params->>'permit_expiration'='null' then null 
when params->>'permit_expiration'='N/A' then null else (params->>'permit_expiration')::date end,
case when params->>'patient_type'='ADULT' then true else false end,
true,
params->>'token',0
)
on conflict(email,mobile) do update
set updatedon=current_timestamp;

end case;

case when (_ret_id>0) then 

insert into integratedcustomer(custid,poscustid,dispensaryid,createdon,isactive)
values(_ret_id,(params->>'customer_id')::bigint,(params->>'dispensaryid')::uuid,current_timestamp,true)
on conflict(custid,poscustid,dispensaryid) do update
set updatedon=current_timestamp,isactive=
 			case 
	 			when params->>'status'='ACTIVE' then true 
	 			when params->>'status'='DEACTIVATED' then false
	 			when params->>'status'='MERGED' then true
				else 
				false 
			end;
end case;

res := '{"status": true, "result": "Updated Successfully","errorcode":"","msg": "'||_ret_id::text||'" , "type":'||_status::text||'}';

elsif(sysparams->>'operate' = 'updateLatLngOfAddress') then

case 
when (params->>'addresstype'='primeAdd') then
	update customer set latlng=POINT((params->>'lng')::double precision,(params->>'lat')::double precision) where id=(params->>'custid')::bigint;
when (params->>'addresstype'='secAdd') then
	update customer set secaddress.latlng=POINT((params->>'lng')::double precision,(params->>'lat')::double precision) where id=(params->>'custid')::bigint;
when (params->>'addresstype'='altAdd') then
	update customer set altraddres.latlng=POINT((params->>'lng')::double precision,(params->>'lat')::double precision) where id=(params->>'custid')::bigint;

else
select true;
end case;

res := '{"status": true, "result": "Updated Successfully","errorcode":"","msg": "" , "type":""}';

END IF;  

	-- exception should be at the end of procedure
	exception when others then 
		 res :='{"status": false, "result":"","errorcode":"", "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}';
				



	RAISE NOTICE '%', res;
	
end;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_dispensary(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
DECLARE err_msg varchar(200); _ret_id uuid;_timezoneoffset varchar(100);
declare _nonregular_schedule json;_regular_schedule json; _delivery_zones json;
begin 
	
	-- this line should be in every procedure
	perform public.setschema(sysparams->>'schema');
	_ret_id := (coalesce(params->>'id',null))::uuid;
	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'crud') THEN 
	
	--call func.sp_dispensary('{"schema":"cmp1", "operate":"crud"}','{"id":"1c9f3ad2-1059-11ea-8d31-87932fe6e9f8","name":"test dispensary 1 dsfds fd","address":"This is test address", "city":1, "state":2, "country":3, "zip":"[1234,5678]", "phone":"1234567890", "contact1":"", "email":"xyz@gmail.com", "banner":"", "domain":"", "timezone":"", "userid":"123e4567-e89b-12d3-a456-426655447878","logo":""}');
		IF EXISTS(select 1 from dispensary where lower(slug)=lower(params->>'slug') and id!=(params->>'id')::uuid) then
			raise exception '%','Slug is already exist';
			-- res := '{"status": false, "result":"","errorcode":"", "msg": "Slug is already exist", "type":1}';
		END if; 
		SELECT unixtimezone INTO _timezoneoffset FROM  sys.utctimezones WHERE id =  (coalesce(params->>'timezone',  '0'))::int;
		IF NOT EXISTS(SELECT 1 FROM dispensary WHERE id=(params->>'id')::uuid) then
		
			--_ret_id := public.uuid_generate_v1();
		
			INSERT INTO dispensary(id,"name",address1,address2,city,state,country,zip,phone,contact1,email,banner,"domain",timezone,createdon,createdby,logo,orderPrefix,timezoneoffset,polygon,otherdata,purpose,integration,payment,orderserial,latlng,maxradius,config,thumbnail,invdefination,mobilebanner,skipinventory,slug,stronghold_pub_key,stronghold_priv_key,stronghold_ext_prefix,terminalid,adv_delivery_time,birdeye_location_id,birdeye_api_key,email_ad_image,cspw_api_key,pickup_type,hyper_merchant,hyper_clientid,hyper_secret)
			VALUES (_ret_id, params->>'name',params->>'address1',params->>'address2',(params->>'city')::int,(params->>'state')::int,(params->>'country')::int,public.replacearray(params->>'zip')::character varying[],params->>'phone',params->>'contact1',params->>'email',params->>'banner',params->>'domain',params->>'timezone',current_timestamp,(params->>'userid')::uuid,params->>'logo',params->>'orderPrefix',_timezoneoffset,case when coalesce(params->>'polygon','')<>'' then public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(params->>'polygon')::character varying,'))')),26191) else null end,(params->>'otherdata')::jsonb,public.replacearray(params->>'purpose')::int[],(params->>'integration')::jsonb,(params->>'payment')::jsonb,(params->>'orderserial')::int,POINT((params->>'lng')::double precision,(params->>'lat')::double precision),(params->>'maxradius')::double precision,(params->>'config')::jsonb,params->>'thumbnail',(params->>'invdefination')::jsonb,params->>'mobilebanner',(params->>'skipinventory')::boolean,case when (coalesce(params->>'slug','')='') then null else (params->>'slug')::character varying end,case when (params->>'stronghold_pub_key'='') then null else params->>'stronghold_pub_key' end,case when (params->>'stronghold_priv_key'='') then null else params->>'stronghold_priv_key' end,params->>'stronghold_ext_prefix',params->>'terminalid',(params->>'adv_delivery_time')::integer,params->>'birdeye_location_id',params->>'birdeye_api_key',params->>'email_ad_image',nullif(params->>'cspw_api_key',''),params->'otherdata'->>'access_ordertype',params->>'hyper_merchant',params->>'hyper_clientid',params->>'hyper_secret');
		
			res := '{"status": true, "result":"Inserted successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
		
		else
		
		
			UPDATE dispensary SET ("name",address1,address2,city,state,country,zip,phone,contact1,email,banner,"domain",
			timezone,updatedon,updatedby,logo,orderPrefix,isactive,timezoneoffset,polygon,otherdata,purpose,integration,orderserial,latlng,maxradius,config,thumbnail,invdefination,mobilebanner,skipinventory,slug,stronghold_pub_key,stronghold_priv_key,stronghold_ext_prefix,terminalid,adv_delivery_time,birdeye_location_id,birdeye_api_key,email_ad_image,cspw_api_key,pickup_type,hyper_merchant,hyper_clientid,hyper_secret)
				= (params->>'name',params->>'address1',params->>'address2',(params->>'city')::int,(params->>'state')::int,
				(params->>'country')::int,public.replacearray(params->>'zip')::character varying[],
				params->>'phone',params->>'contact1',params->>'email',params->>'banner',params->>'domain',
				params->>'timezone',current_timestamp,(params->>'userid')::uuid,params->>'logo',params->>'orderPrefix',
				(coalesce(params->>'active', 'true'))::boolean,_timezoneoffset,
				case when coalesce(params->>'polygon','')<>'' then public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(params->>'polygon')::character varying,'))')),26191) else null end,
				(params->>'otherdata')::jsonb,
				public.replacearray(params->>'purpose')::int[],(params->>'integration')::jsonb,(params->>'orderserial')::int,POINT((params->>'lng')::double precision,(params->>'lat')::double precision),(params->>'maxradius')::double precision,(params->>'config')::jsonb,params->>'thumbnail',(params->>'invdefination')::jsonb,params->>'mobilebanner',(params->>'skipinventory')::boolean,case when (coalesce(params->>'slug','')='') then null else (params->>'slug')::character varying end,case when (params->>'stronghold_pub_key'='') then null else params->>'stronghold_pub_key' end,case when (params->>'stronghold_priv_key'='') then null else params->>'stronghold_priv_key' end,params->>'stronghold_ext_prefix',params->>'terminalid',(params->>'adv_delivery_time')::integer,params->>'birdeye_location_id',params->>'birdeye_api_key',params->>'email_ad_image',nullif(params->>'cspw_api_key',''),params->'otherdata'->>'access_ordertype',params->>'hyper_merchant',params->>'hyper_clientid',params->>'hyper_secret')
			WHERE id=(params->>'id')::uuid;
		
			res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
		END IF;
	
		INSERT INTO dispensarytime (id,fromtime,totime,"day",createdon,dispensaryid,timezone,nonkiosk,isactive,cutofftime)
		select
			(case when coalesce(x->>'id','0') = '' then nextval('dispensarytime_autoid_seq'::regclass)::text else x->>'id' end )::bigint as id,
			(coalesce(x->>'openTime','00:00:00'))::time as fromtime,
			(coalesce(x->>'closeTime','00:00:00'))::time as totime ,
			(x->>'value')::int as "day",
			current_timestamp as createdon,
			coalesce((params->>'id')::uuid , _ret_id) as dispensaryid,
			coalesce(_timezoneoffset, 'UTC'),
			coalesce((x->>'nonkiosk')::boolean, false) as nonkiosk,
			coalesce((x->>'isactive')::boolean, true) as isactive,
			(coalesce(x->>'cutoffTime','00:00:00'))::time as cutofftime
		from json_array_elements((params->>'dispensaryKioskTime')::json) as x
	
		on conflict(id) 
		do update
		
		set
			fromtime=excluded.fromtime,totime=excluded.totime,"day"=excluded."day",updatedon=current_timestamp,timezone = coalesce(_timezoneoffset, 'UTC'),nonkiosk=excluded.nonkiosk,isactive=excluded.isactive,cutofftime=excluded.cutofftime;
			

		INSERT INTO dispensarytime (id,fromtime,totime,"day",createdon,dispensaryid,timezone,nonkiosk,isactive,cutofftime)
		select
			(case when coalesce(x->>'id','0') = '' then nextval('dispensarytime_autoid_seq'::regclass)::text else x->>'id' end )::bigint as id,
			(coalesce(x->>'openTime','00:00:00'))::time as fromtime,
			(coalesce(x->>'closeTime','00:00:00'))::time as totime ,
			(x->>'value')::int as "day",
			current_timestamp as createdon,
			coalesce((params->>'id')::uuid , _ret_id) as dispensaryid,
			coalesce(_timezoneoffset, 'UTC'),
			coalesce((x->>'nonkiosk')::boolean, true) as nonkiosk,
			coalesce((x->>'isactive')::boolean, true) as isactive,
			(coalesce(x->>'cutoffTime','00:00:00'))::time as cutofftime
		from json_array_elements((params->>'dispensaryOrderTime')::json) as x
	
		on conflict(id) 
		do update
		
		set
			fromtime=excluded.fromtime,totime=excluded.totime,"day"=excluded."day",updatedon=current_timestamp,timezone = coalesce(_timezoneoffset, 'UTC'),nonkiosk=excluded.nonkiosk,isactive=excluded.isactive,cutofftime=excluded.cutofftime;
	-- end "crud" operate
	
	
	
	ELSIF (sysparams->>'operate' = 'active') THEN 
	
	--call func.sp_dispensary('{"schema":"cmp1", "operate":"active"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d","active": false}');	
		UPDATE dispensary SET (updatedon,updatedby,isactive)
		= (current_timestamp,(params->>'userid')::uuid,(coalesce(params->>'active', 'true'))::boolean)
			WHERE id=(params->>'id')::uuid;
			res :='{"status": true, "result":"'|| case when ((coalesce(params->>'active', 'true'))::boolean) then 'Activated' else 'Deactivated' end||' successfully","errorcode":"", "msg": "'||_ret_id||'", "type":2}';
		
		
	ELSIF (sysparams->>'operate' = 'delete') THEN 
--call func.sp_dispensary('{"schema":"cmp1", "operate":"delete"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d"}');
			UPDATE dispensary SET (updatedon,updatedby,isactive, isdelete)
		= (current_timestamp,(params->>'userid')::uuid,
		case when ((coalesce(params->>'delete', 'true'))::boolean) then false else true end, 
		(coalesce(params->>'delete', 'true'))::boolean)
			WHERE id=(params->>'id')::uuid;
			res :='{"status": true, "result":"'|| case when ((coalesce(params->>'delete', 'true'))::boolean) then 'Deleted' else 'Restored' end||' successfully","errorcode":"", "msg": "'||_ret_id||'", "type":2}';
	
	ELSIF (sysparams->>'operate' = 'nonregular') then
	
		delete from nonregular_schedule where date in (select (ds->>'date')::date from json_array_elements((params->>'data')::json) as ds) and dispensaryid=(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int;
	
		insert into nonregular_schedule(date,available,charge,order_limit,dispensaryid,created_at,fromtime,totime,zone_id)
		select 
			   (ds->>'date')::date,
			   (ds->>'available')::boolean, 
			   (ds->>'charge')::decimal(4,2),
			   (ds->>'order_limit')::int,
			   (params->>'dispid')::uuid,
			   current_timestamp,
			   (ds->>'fromtime')::time,
			   (ds->>'totime')::time,
			   (ds->>'zone_id')::int
	    from json_array_elements((params->>'data')::json) as ds;

		with _records1 as
		(	
			select date,available,json_agg(json_build_object('id',id,'fromtime',fromtime,'totime',totime,'charge',charge,'order_limit',order_limit) ORDER BY fromtime) as data from nonregular_schedule where dispensaryid =(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int group by date,available order by date
		)
		select json_agg(json_build_object('date',r.date,'available',r.available,'data',r.data)) into _nonregular_schedule from _records1 r;
	   
	   res :='{"status": true, "result":'||_nonregular_schedule||',"errorcode":"", "msg":"Updated successfully", "type":2}';
	  
	ELSIF (sysparams->>'operate' = 'regular') then
	
		delete from regular_schedule where dayid in (select (ds->>'dayid')::integer from json_array_elements((params->>'data')::json) as ds) and dispensaryid=(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int;
	
		insert into regular_schedule(dayid,charge,order_limit,dispensaryid,created_at,fromtime,totime,zone_id)
		select 
			   (ds->>'dayid')::integer, 
			   (ds->>'charge')::decimal(4,2),
			   (ds->>'order_limit')::int,
			   (params->>'dispid')::uuid,
			   current_timestamp,
			   (ds->>'fromtime')::time,
			   (ds->>'totime')::time,
			   (ds->>'zone_id')::int
	    from json_array_elements((params->>'data')::json) as ds;
	   
	   with _records as
		(	
			select dayid,json_agg(json_build_object('id',id,'fromtime',fromtime,'totime',totime,'charge',charge,'order_limit',order_limit)) as data from regular_schedule where dispensaryid =(params->>'dispid')::uuid and zone_id = (params->>'zone_id')::int  group by dayid
		)
		select json_agg(json_build_object('dayid',r.dayid,'data',r.data)) into _regular_schedule from _records r;
	   
	   res :='{"status": true, "result":'||_regular_schedule||',"errorcode":"", "msg":"Updated successfully", "type":2}';
	  
	  
	ELSIF (sysparams->>'operate' = 'deleteExceptionalDate') then
	
		delete from nonregular_schedule where id=(params->>'rid')::integer;
	  	res :='{"status": true, "result":"Deleted successfully","errorcode":"", "msg":"", "type":2}';
	  
	ELSIF (sysparams->>'operate' = 'deleteRegularDay') then
	
		delete from regular_schedule where id=(params->>'rid')::integer;
	  	res :='{"status": true, "result":"Deleted successfully","errorcode":"", "msg":"", "type":2}';
	  
	ELSIF (sysparams->>'operate' = 'deleteDeliveryZone') then
	
		delete from delivery_zones where id=(params->>'rid')::integer;
	  	res :='{"status": true, "result":"Deleted successfully","errorcode":"", "msg":"", "type":2}';
		
	
	ELSIF (sysparams->>'operate' = 'deliveryzone') then
		delete from zones where dispensaryid=(params->>'dispensary')::uuid;
		/*insert into zones(polygon,created_on,dispensaryid)
		select  
			   public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(dz->>'cords')::character varying,'))')),26191) as polygon,
			   current_timestamp,
			   (params->>'dispensary')::uuid as dispensaryid
		from json_array_elements((params->>'zones')::json) as dz;*/
		insert into zones(title,polygon,created_on,bgcolor,dispensaryid,image_path)
		select 
			   dz->>'title' as title,
			   public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(dz->>'polygon')::character varying,'))')),26191) as polygon,
			   current_timestamp,
			   dz->>'bgcolor' as bgcolor,
			   (params->>'dispensary')::uuid as dispensaryid,
			   dz->>'image' as image_path
		from json_array_elements((params->>'data')::json) as dz;
		res :='{"status": true, "result":"Saved successfully","errorcode":"", "msg":"", "type":2}';
	
	
	ELSIF (sysparams->>'operate' = 'dispzones') then
		delete from delivery_zones where dispensaryid = (params->>'dispid')::uuid;
		insert into delivery_zones(id,title,min_order_value,polygon,created_on,bgcolor,dispensaryid)
		select 
			   (case when (dz->>'id')::text='' then '0' else dz->>'id' end)::int as id,
			   dz->>'title' as title,
			   (dz->>'min_order_value')::decimal(13,4) as min_order_value,
			   public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(dz->>'polygon')::character varying,'))')),26191) as polygon,
			   current_timestamp,
			   dz->>'bgcolor' as bgcolor,
			   (params->>'dispid')::uuid as dispensaryid
		from json_array_elements((params->>'data')::json) as dz where dz->>'id'!=''
		ON CONFLICT (id) DO UPDATE set 
		title=EXCLUDED.title,
		min_order_value=EXCLUDED.min_order_value,
		polygon=EXCLUDED.polygon,
		bgcolor=EXCLUDED.bgcolor;
	
		insert into delivery_zones(title,min_order_value,polygon,created_on,bgcolor,dispensaryid)
		select
			   dz->>'title' as title,
			   (dz->>'min_order_value')::decimal(13,4) as min_order_value,
			   public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(dz->>'polygon')::character varying,'))')),26191) as polygon,
			   current_timestamp,
			   dz->>'bgcolor' as bgcolor,
			   (params->>'dispid')::uuid as dispensaryid
		from json_array_elements((params->>'data')::json) as dz where dz->>'id'='';
	
		
		
		select json_agg(json_build_object('id',id,'title',title,'min_order_value',min_order_value,'bgcolor',bgcolor,'polygon',coalesce(public.ST_AsText(polygon), ''))) into _delivery_zones from delivery_zones where dispensaryid =(params->>'dispid')::uuid;
		
		res :='{"status": true, "result":'||_delivery_zones||',"errorcode":"", "msg":"", "type":2}';
	
	ELSIF (sysparams->>'operate' = 'deleteZone') then
		delete from zones where id=(params->>'rid')::int;
		res :='{"status": true, "result":"Deleted successfully","errorcode":"", "msg":"", "type":2}';
	
	elseif (sysparams->>'operate' = 'load_timeslot_report') then

		insert into timeslot_report(day,date,data,created_at,dispensary_id,zone_id)
		with _timelist as
		(
			select (current_timestamp at time zone d.timezoneoffset at time zone 'utc')::date as date_lbl, case when rs.available=false then false when rs2.dayid=null then false else true end as available,
			case when rs.id is not null then rs.id  else rs2.id end as id,
			case when rs.id is not null then rs.fromtime  else rs2.fromtime end as fromtime,
			case when rs.id is not null then rs.totime else rs2.totime end as totime,
			case when rs.id is not null then rs.charge else rs2.charge end as charge,
			case when rs.id is not null then rs.order_limit else rs2.order_limit end as order_limit,
			case when rs.id is not null then rs.zone_id else rs2.zone_id end as zone_id,
			d.id as dispensaryid,
			d.timezoneoffset, 
			dz.polygon
			from 
			dispensary d 
			inner join delivery_zones dz on d.id=dz.dispensaryid
			left join (select nr.* from nonregular_schedule nr inner join dispensary d on d.id=nr.dispensaryid where nr.date=(current_timestamp at time zone d.timezoneoffset at time zone 'utc')::date) rs on dz.id=rs.zone_id and rs.dispensaryid = dz.dispensaryid 
			left join (select rs1.* from regular_schedule rs1 inner join dispensary d on d.id=rs1.dispensaryid where rs1.dayid=extract(isodow from (current_timestamp at time zone d.timezoneoffset at time zone 'utc')::date)) rs2 on dz.id=rs2.zone_id and rs2.dispensaryid = dz.dispensaryid
			where 
			(case when rs.id is not null then rs.id  else rs2.id end) is not null
			and d.isactive=true and d.isvirtual=false
		),
		_recordgroup as (
			select date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid,polygon from _timelist 
			group by date_lbl,available,id,fromtime,totime,charge,order_limit,zone_id,dispensaryid,polygon
		),
		_orderrecord as (
			select 
			t.dispensaryid,
			t.date_lbl,
			t.available,
			t.id,
			t.fromtime,
			t.totime,
			t.charge,
			t.order_limit, 
			t.zone_id,
			sum(
					case 
						when o.id is not null and ((o.schedule_datetime)::time>=t.fromtime and (o.schedule_datetime)::time<t.totime )
						then 1 else 0 
					end
				) as total 
			from _recordgroup t 
			left join (select o.id,o.schedule_datetime,o.customeraddress,o.dispensaryid from ordermaster o inner join dispensary d on o.dispensaryid=d.id where coalesce((latlng(o.customeraddress))[1]::varchar,'')!='' and date(o.schedule_datetime)=(current_timestamp at time zone d.timezoneoffset at time zone 'utc')::date) o 
			on o.dispensaryid = t.dispensaryid
			and case when (coalesce((latlng(o.customeraddress))[1]::varchar,'')!='') then public.ST_Contains(t.polygon,public.ST_Transform(public.ST_GeomFromText(concat('POINT(', (latlng(o.customeraddress))[1]::varchar, ' ' , (latlng(o.customeraddress))[0]::varchar, ')'),26191),26191)) else false end	
			group by t.dispensaryid,t.date_lbl,t.available,t.id,t.fromtime,t.totime,t.charge,t.order_limit,t.zone_id
			order by t.date_lbl,t.fromtime
		)

		select to_char(t.date_lbl, 'Day'),t.date_lbl,
		json_agg(json_build_object('id',t.id,'fromtime',t.fromtime,'totime',t.totime,'available',t.available,'total',t.order_limit,'total_orders',t.total) order by t.fromtime) 
		as data,
		current_timestamp,
		t.dispensaryid,
		t.zone_id
		from _orderrecord t
		where t.available=true
		group by t.date_lbl,t.dispensaryid,t.zone_id
		order by t.date_lbl
	
		on conflict(date,dispensary_id,zone_id) 
		do update set
		data = excluded.data;
		res :='{"status": true, "result":"Synced successfully","errorcode":"", "msg":"", "type":2}';
	
	ELSIF (sysparams->>'operate' = 'updateCSPWStatus') then
		update dispensary set otherdata = jsonb_set(otherdata,'{cspw_enable}'::text[],(params->>'status')::jsonb) where otherdata->>'cspw_api_key'=params->>'key';
		res :='{"status": true, "result":"CSPW status updated successfully","errorcode":"", "msg":"", "type":2}';
		
	END IF; 
   
   
	-- exception should be at the end of procedure
	exception when others then 
		res :='{"status": false, "result":"","errorcode":"", "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}';
				



	RAISE NOTICE '%', res;
	
end;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_dispensary_configs(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$
DECLARE err_msg varchar(200); _ret_id int;_timezoneoffset varchar(100);
declare _nonregular_schedule json;_regular_schedule json; _delivery_zones json;
begin
		
		-- this line should be in every procedure
		perform public.setschema(sysparams->>'schema');
		-- specify the operation needs to be perform
	
		IF (sysparams->>'operate' = 'rating') then
			if(params->>'id' is null or params->>'id' = '' or (params->>'id')::int =0) then
				_ret_id=nextval('dispensary_configs_id_seq'::regclass);
			else
				_ret_id= params->>'id';
			end if;
			IF NOT EXISTS(SELECT 1 FROM dispensary_configs WHERE dispensaryid=(params->>'dispensary')::uuid) then
			
				INSERT INTO dispensary_configs(id,dispensaryid,total_review,total_rating,avg_rating,createdon)
				values ((_ret_id):: int,(params->>'dispensary')::uuid, params->>'total_review',params->>'total_rating',(params->>'avg_rate')::float8,current_timestamp);
				
				res := '{"status": true, "result":"Inserted successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
				
			else
			
				UPDATE dispensary_configs SET (total_review,total_rating,avg_rating,updatedon)
				= (params->>'total_review',params->>'total_rating',(params->>'avg_rate')::float8,current_timestamp)
				WHERE dispensaryid=(params->>'dispensary')::uuid;
			
				res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
			END IF;
		
		ELSIF (sysparams->>'operate' = 'general') then
			if(params->>'id' is null or params->>'id' = '' or (params->>'id')::int =0) then
				_ret_id=nextval('dispensary_configs_id_seq'::regclass);
			else
				_ret_id= params->>'id';
			end if;
			
			IF NOT EXISTS(SELECT 1 FROM dispensary_configs WHERE dispensaryid=(params->>'dispensary')::uuid) then
			
				INSERT INTO dispensary_configs(id,dispensaryid,email_technical,email_nontechnical,new_arrival_days,createdon,coupon_discount_applied)
				values ((_ret_id):: int,(params->>'dispensary')::uuid, params->>'email_technical',params->>'email_nontechnical',(params->>'arrival')::text,current_timestamp,(params->>'coupon')::boolean);
				
				res := '{"status": true, "result":"Inserted successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
				
			else
			
				UPDATE dispensary_configs SET (email_technical,email_nontechnical,new_arrival_days,coupon_discount_applied,updatedon)
				= (params->>'email_technical',params->>'email_nontechnical',(params->>'arrival')::text,(params->>'coupon')::boolean,current_timestamp)
				WHERE dispensaryid=(params->>'dispensary')::uuid;
			
				res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
			END IF;
		
		END IF;
		
		-- exception should be at the end of procedure
		exception when others then 
			res :='{"status": false, "result":"","errorcode":"", "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}';
					
	
	
	
		RAISE NOTICE '%', res;
				
END;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_orders(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
declare _orderuid uuid default null;
declare _orderid int default null;
declare _orderserial int ;
declare _dispprefix text;
declare _custorderid text;
declare _pos text;
declare _defaultorderstatus text;
declare _poscode text;
declare _uniqordid text;
declare _posordernum text;
declare gprow record;
declare _ret_id int;
declare _orderstype_code text;
/*
 * call func.sp_orders('{"schema":"cmp2","operate":"crud","flag":"","payload":""}','{"user":{"email":"frankyfernandes45@gmail.com","id":24,"name":"franky fernandes","otp":1,"vemail":1,"cmp":2},"cart":[{"product":{"productid":38,"varientid":21,"name":"Vape Pen - Blue Raspberry","productreleasedate":"2019-12-01","sellingprice":"2","img1":"v1576154027\/cmp2\/products\/abfrnlawhihu6cockljj.jpg","img2":"","img3":"","minvisinventorylevel":1,"totalmgthc":"5","totalmgcbd":"6","sku":"12345","categoryname":"MERCH","brandname":"FROM THE EARTH","effects":[{"name":"Uplifted","value":"100"},{"name":"Euphoric","value":"90"},{"name":"Energetic","value":"85"},{"name":"Creative","value":"85"},{"name":"Focused","value":"70"}],"attributes":"{Size}","prodrating":0,"oldrating":"0","attributevalues":"{\"Size\": \"XL\"}","qty":2,"prodcount":1},"productcount":1}],"subtotal":4,"operate":"crud","dispensary":"62636034-10f4-11ea-91ae-f3baf24963ca","cmp":2}')
 * */
	begin
		perform public.setschema(sysparams->>'schema');
		
		
	
		-- _productlogid := nextval('productlog_id_seq'::regclass) -1;
			
	
		-- _orderserial := (select orderserial from dispensary where id = (params->>'dispidom')::uuid) + 1;
		-- _dispprefix := (select orderprefix from dispensary where id = (params->>'dispidom')::uuid);
		if(sysparams->>'operate' = 'activateorder')then
			_uniqordid = params->>'orderid';
			update ordermaster set 
			posorderid = (params->>'ticket_id')::text,
			posordernum = (params->>'order_number')::text,
			isactive = true 
			where id = (params->>'orderid')::int; 
			
			update orderdetails set isactive = true where orderid = (params->>'orderid')::int;
			update carthistory set isactive =  true where orderid = (params->>'orderid')::int;
		
			_dispprefix := (select orderprefix from dispensary where id = (select dispensaryid from ordermaster where id=(params->>'orderid')::int)::uuid);
			_orderserial := (select dispordserial from ordermaster where id = (params->>'orderid')::int);
			_custorderid = concat(_dispprefix,'-',_orderserial);
			_posordernum = (params->>'order_number')::text;
			res := ('{"status": true, "msg": "Order placed successfully","posordernum":"'||_posordernum||'","ecomordid":"'||_custorderid||'","uniqordid":"'||_uniqordid||'", "type":1}')::json;   
					
		elseif(sysparams->>'operate' = 'porder') then 
			-- insert into temptest (jsondata) values(params);
			_orderid := nextval('ordermaster_id_seq'::regclass) - 1 ;
			_orderuid := public.uuid_generate_v1();
			 _pos := (select integration->'pos'->>'name' from dispensary where id =    (params->>'dispensaryid')::uuid);
			 -- _defaultorderstatus := (select portalcode from sys.orderstatusmapping  where posname = (_pos)::text and isdefault = true);
			 _defaultorderstatus := (select portalcode from sys.orderstatusmapping  where posname = (_pos)::text and poscode = (params->>'cust_order_status')::text);
			-- _poscode := (select poscode from sys.orderstatusmapping  where posname = (_pos)::text and isdefault = true);
			_poscode = (params->'posmaderesponse'->>'cust_order_status')::text;
			_orderserial := (select orderserial from dispensary where id =  case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dipid')::uuid else   (params->>'dispensaryid')::uuid end) + 1;
			_dispprefix := (select orderprefix from dispensary where id =  case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dipid')::uuid else   (params->>'dispensaryid')::uuid end);
			
		insert into ordermaster(
			id,
			uoid,
			customerid,
			email,
			status,
			dispensaryid,
			dispordserial,
			customeraddress.street,
			customeraddress.apartmnt,
			customeraddress.city,
			customeraddress.state,
			customeraddress.country,
			customeraddress.zip,
			customeraddress.latlng,
			posorderid,
			ordersummary,
			total,
			tax,
			subtotal,
			instruction,
			posordernum,
			totaldiscount,
			 ordertype,
			 totalextrafees,
			 selecteddelslot,
			 coupondetails,
			 paydetails,
			 schedule_datetime,
			 device
			)values(
			_orderid,
			_orderuid,
			(params->>'ecom_custid')::int,
			(params->>'email')::text,
			(_defaultorderstatus)::text,
			 case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dipid')::uuid else   (params->>'dispensaryid')::uuid end,
			_orderserial,
			(params->'address'->>'street')::text,
			(params->'address'->>'apartmnt')::text,
			(params->'address'->>'city')::text,
			(params->'address'->>'state')::int,
			(params->'address'->>'country')::int,			
			(params->'address'->>'zip')::text,
			POINT((params->'address'->>'lng')::double precision,(params->'address'->>'lat')::double precision),
			(params->'posmaderesponse'->>'ticket_id')::text,
			(params->'posmaderesponse'->>'ordersummary')::text,
			(params->'posmaderesponse'->>'total')::double precision,
			(params->'posmaderesponse'->>'tax_total')::double precision,
			(params->'posmaderesponse'->>'sub_total')::double precision,
			(params->'posmaderesponse'->>'ticket_note')::text,
			(params->'posmaderesponse'->>'order_number')::text,
			abs((params->'posmaderesponse'->>'discount_total')::double precision),
			 case when ((params->>'isvirtual')::boolean=false) then 	 				(params->'posmaderesponse'->>'type')::text
			 else 
			 	('PICKUP')::text
			 end
			 ,
			 (params->'posmaderesponse'->>'totalextrafees')::double precision,
			 (params->'posmaderesponse'->>'selectedTimeSlots')::json,
			 (params->'coupondetails')::jsonb,
			 (params->'paydetails')::jsonb,
			 (params->>'scheduled_datetime')::timestamp, -- doesnt store in utc
			 params->>'device'
			);
		
--		insert into orderstatuslog 
--			(orderid,status,remark)
--		values
--			(_orderid,(_defaultorderstatus)::text,(_poscode)::text);
		--carthistory
		insert into carthistory (orderid,cartjson,dispensaryid,isactive)
		select _orderid,cartjson,dispensaryid,false from customercart where custid = (params->>'ecom_custid')::int;
		-- end
		update dispensary set orderserial = _orderserial 
		where id = (case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dipid')::uuid else   (params->>'dispensaryid')::uuid end);
		
	
		drop table if exists tod;
		drop table if exists taxdiscount;
	
	
		create temporary table tod as 
		select _orderid as orderid,ox->>'product_id' as puid
       ,ox->>'quantity' as quantity
       ,ox->>'product_size_name' as product_size_name
       ,ox->>'price_total' as price_total
       ,p.id as prid,
       pd.id as pdid,
       p.name as prname,
       ox->>'price_sell' as price_sell,
       ox->>'tax_sum' as tax_sum,
       ox->>'discount_sum' as discount_sum,
       ox->>'discounts' as discounts
		from json_array_elements((params->'posmaderesponse'->>'items')::json) as ox
		inner join product p on p.puid = (ox->>'product_id')::uuid
        inner join productdetails pd on pd.productuid = (ox->>'size_id')::uuid
       where p.dispensaryid = (params->>'dispensaryid')::uuid and pd.dispensaryid = (params->>'dispensaryid')::uuid;
	
       
       
       insert into orderdetails 
       (orderid,prname,quantity,prid,prdetailsid,price,productname,variantname,baseprice,tax,discount,discount_id,discounts)
       select (orderid)::int,(prname)::text,(quantity)::int,(prid)::int,(pdid)::int,(price_total)::double precision,(prname)::text,(prname)::text,price_sell::double precision ,tax_sum::double precision,(discount_sum)::double precision,(params->>'discount_id')::int,discounts::json  from tod;
      
      
      -- insert payment data
      INSERT INTO orderpayments
		(orderid, paytype, paymethod, payment_id, payment_date, payment_source, payment_method, amount_paid, payid, fee, "type", amount, status, currency, created_at, customer_id, description, external_id, authorized_at, convenience_fee, payment_source_id, payment_status, createdby)
	  select _orderid, (params->'paydetails'->>'paytype'), p->>'payment_method', p->>'payment_id', p->>'payment_date', p->>'payment_source', p->>'payment_method', (p->>'amount_paid')::float8, p->>'id', (p->>'fee')::float8, p->>'type', (p->>'amount')::float8, p->>'status', p->>'currency', p->>'created_at', p->>'customer_id', p->>'description', p->>'external_id', p->>'authorized_at', (p->>'convenience_fee')::float8, p->>'payment_source_id', params->>'payment_status', (params->>'ecom_custid')::int
	  from json_array_elements(params->'paydetails'->'payments') as p;
	  
      -- Add purchased gift products to gift product master..
      if exists(
      	select p.id
      	from json_array_elements((params->'posmaderesponse'->>'items')::json) as ox
		inner join product p on p.puid = (ox->>'product_id')::uuid
		inner join giftproduct gp on p.id = gp.productid
      ) then
--	    insert into purchased_gift_products(product_id,customer_id,dispensary_id,amount,name,from_date,to_date,order_id)
--	    select t.prid,(params->>'ecom_custid')::int,gp.dispensaryid,gp.amount,gp.name,CURRENT_DATE,current_date + (gp.expiry_months||' month')::interval,t.orderid from tod t
--	    inner join giftproduct gp on t.prid = gp.productid 
--	    where gp.dispensaryid  = (params->>'dispensaryid')::uuid;
        for gprow in select prid::int as prid, quantity::int as qty from tod where prid in (select productid from giftproduct where isactive=true and isdelete=false) 
        loop
	        for i in 1..gprow.qty loop
		     	insert into giftcard(cardno,giftproductid,purchasedby,balance,expiry_date,purchaseorderid)
			    select upper(to_hex( concat(i,(params->>'ecom_custid')::int,(extract(epoch from now())*1000)::bigint)::bigint )),gp.productid,(params->>'ecom_custid')::int,gp.amount,current_date + (gp.expiry_months||' month')::interval, _orderid
			    from giftproduct gp
			    where gp.dispensaryid = (params->>'dispensaryid')::uuid and gp.productid = gprow.prid;
		    end loop;
        end loop;
     end if;
      
      -- Remove product from wishlist
      
      delete from wishlist w USING tod a where w.productid=a.prid and w.custid=(params->>'ecom_custid')::int; 
      
      -- update isupdated_med_detail flag in integratedcustomer table to false
      
     update integratedcustomer set isupdated_med_detail=false where custid=(params->>'ecom_custid')::int and dispensaryid=(case when ((params->>'isvirtual')::boolean=true) then (params->>'vir_dipid')::uuid else   (params->>'dispensaryid')::uuid end);
      
      create temporary table taxdiscount as 
        select _orderid as orderid, tx->>'puid' as puid,tx->>'type' as "type",
        tx->>'amt_type' as amt_type,tx->>'rate' as rate,tx->>'name' as name,tx->>'amount' as amount ,
        tx->>'extrajson' as extrajson 
       from json_array_elements((params->'posmaderesponse'->>'alltaxesdiscounts')::json) as tx;
       
       insert into ordertaxdiscount (orderid,prodid,"name",amttype,rate,value,extrajson,"type")
       select (orderid)::int,(puid)::text,(name)::text,(amt_type)::text,(rate)::double precision,
      (amount)::double precision, (extrajson)::json,("type")::text from taxdiscount;
      
       
       
      -- drop table if exists tod;
    -- drop table if exists taxdiscount;
    	delete from customercart where custid = (params->>'ecom_custid')::int;
    
    -- delete preview data of customer
    	delete from customer_preview_order_details where customer_id = (params->>'ecom_custid')::int and dispensaryid = (params->>'dispensaryid')::uuid;
    
  _custorderid = concat(_dispprefix,'-',_orderserial);		
 	
 res := ('{"status": true, "msg": "Order placed successfully","orderid":"'||_orderid||'","custorderid":"'||_custorderid||'", "type":1}')::json;   




		elseIF (params->>'operate' = 'crud') then
			
		insert into ordermaster (
			id,
			uoid,
			customerid,
			email,
			status,
			total,
			ordersummary,
			dispensaryid,
			dispordserial,
			additionaltax,
			customeraddress.street,
			customeraddress.apartmnt,
			customeraddress.city,
			customeraddress.state,
			customeraddress.country,
			customeraddress.zip
			)values(
			_orderid,
			_orderuid,
			(params->'user'->>'id')::int,
			(params->'user'->>'email')::character varying,
			0,
			(params->>'subtotal')::double precision,
			(params->>'ordersummary')::text,
			(params->>'dispidom')::uuid,
			_orderserial,
			(params->'taxdata'->>'totaltaxamttoshow')::double precision ,
			(params->'checkoutData'->'address'->>'street')::character varying,
			(params->'checkoutData'->'address'->>'apartmnt')::character varying,
			(params->'checkoutData'->'address'->>'city')::character varying,
			(params->'checkoutData'->'address'->>'state')::int,
			(params->'checkoutData'->'address'->>'country')::int,
			(params->'checkoutData'->'address'->>'zip')::character varying
			);
		
		update dispensary set orderserial = _orderserial where id = (params->>'dispidom')::uuid;
		--
		drop table if exists tod;
		
		create temporary table tod as 
		select _orderid as torderid,
			(x->'product'->>'productid')::int as pid,
			(x->'product'->>'varientid')::int as proddetailid ,
			(x->'product'->>'name')::character varying as pname, 
			(x->'product'->>'qty')::int as pqty,
			(x->'product'->>'sellingprice')::float8 as pcount,
			(x->'product'->>'selectedattribute')::json as selectedattribute,
			0 as status ,
			p."name" as prname,
			pd."variantname",
			''::text as hash,
			'[]'::json as pjson
		from json_array_elements((params->>'cart')::json) as x
		inner join product p on p.id=(x->'product'->>'productid')::int
		inner join productdetails pd on pd.id = (x->'product'->>'varientid')::int;
	
		update tod set hash = md5(row_to_json(pro)::text),
		pjson = row_to_json(pro)
		 from (
		select 
		p.id as prid,p."name" as prname,pd.id as pdid,pd.variantname,p.shortdesc as prshortdesc,p.description as prdesc,br."name" as brandname,p.productreleasedate,p.typeid,c."name" as categoryname,
		sc."name" as subcatname,p.tags,p.dispensaryid,m.code as uomcode,m."name" as uomname,p."attributes",p.puid, 
		
		pd.description as pddesc,pd.col1,pd.col2,pd.col3,pd.col4,pd.col5,pd.price,pd.sellingprice,pd.img1,pd.totalmgthc,pd.totalmgcbd,pd.attributevalues,pd.sku
		
		from product p 
		inner join productdetails pd on p.id = pd.productid
		inner join tod od on od.proddetailid = pd.id
		inner join brand br on br.id = p.brandid 
		left join category c on c.id = p.categoryid 
		left join subcategory sc on sc.id = p.subcatid
		left join mom m on m.id = (p.uom)::int and m."groups" = 'uom'
		
		) as pro
		
		where pro.prid = tod.pid and pro.pdid = tod.proddetailid;
		
	insert into orderdetails(orderid,prid,prdetailsid,prname,quantity,price,status,productname,variantname,selectedattribute,prloghash)
	select _orderid,pid,proddetailid,pname,pqty,pcount,status,prname,variantname,selectedattribute,hash from tod;
	
	insert into productlog(hash,productdetails)
	select hash,pjson from tod
	on conflict(hash)
	do update set productdetails = excluded.productdetails;

	insert into ordertaxes (orderid,taxes) values(_orderid,(params->>'taxdata')::jsonb);
		--
--		insert into orderdetails(orderid,prid,prdetailsid,prname,quantity,price,status,productname,variantname)
--		select _orderid,
--			(x->'product'->>'productid')::int as pid,
--			(x->'product'->>'varientid')::int as proddetailid ,
--			(x->'product'->>'name')::character varying as pname, 
--			(x->'product'->>'qty')::int as pqty,
--			(x->'product'->>'sellingprice')::float8 as pcount,
--			0 as status ,
--			p."name",
--			pd."variantname"
--		from json_array_elements((params->>'cart')::json) as x
--		inner join product p on p.id=(x->'product'->>'productid')::int
--		inner join productdetails pd on pd.id = (x->'product'->>'varientid')::int;
		 
			
_custorderid = concat(_dispprefix,'-',_orderid,'-',_orderserial);		
		
 res := ('{"status": true, "msg": "Order placed successfully","orderid":"'||_custorderid||'", "type":1}')::json;
		
	  -- res := (select string_agg(x->'product'->>'productid',', ') as names from json_array_elements((params->>'cart')::json) as x group by 1);
	 
	 
		elsif (sysparams->>'operate' = 'changestatus') then
			
			update ordermaster set status=(params->>'status')::character varying where id=(params->>'orderid')::int;
			res := ('{"status": true, "msg": "Status changed successfully", "type":1}')::json;
		
		elsif (sysparams->>'operate' = 'updateOrder') then
			update ordermaster set paytype=(params->>'paytype'), paydetails=(params->'paydetails')::jsonb where id=(params->>'orderid')::bigint;
		
			-- insert payment data
			INSERT INTO orderpayments
			  (orderid, paytype, paymethod, payment_id, payment_date, payment_source, payment_method, amount_paid, payid, fee, "type", amount, status, currency, created_at, customer_id, description, external_id, authorized_at, convenience_fee, payment_source_id, payment_status)
		    select (params->>'orderid')::int8, (params->'paydetails'->>'paytype'), p->>'payment_method', p->>'payment_id', p->>'payment_date', p->>'payment_source', p->>'payment_method', (p->>'amount_paid')::float8, p->>'id', (p->>'fee')::float8, p->>'type', (p->>'amount')::float8, p->>'status', p->>'currency', p->>'created_at', p->>'customer_id', p->>'description', p->>'external_id', p->>'authorized_at', (p->>'convenience_fee')::float8, p->>'payment_source_id', params->>'paytype'
		    from json_array_elements(params->'paydetails'->'payments') as p;
			res := ('{"status": true, "msg": "Status changed successfully", "type":1}')::json;
		
		elsif (sysparams->>'operate' = 'log_customer_preview_order') then
		
			insert into customer_preview_order_details(customer_id,dispensaryid,sub_total,tax_total,discount_total,totalextrafees,total,created_at)
			values((params->>'customer_id')::bigint,(params->>'dispensaryid')::uuid,(params->>'sub_total')::decimal,(params->>'tax_total')::decimal,(params->>'discount_total')::decimal,(params->>'totalextrafees')::decimal,(params->>'total')::decimal,current_timestamp)
			on conflict(customer_id,dispensaryid)
			do update 
			set
			sub_total = excluded.sub_total,
			tax_total = excluded.tax_total,
			discount_total = excluded.discount_total,
			totalextrafees = excluded.totalextrafees,
			total = excluded.total,
			created_at = current_timestamp;
		
		elsif (sysparams->>'operate' = 'crud_orderstype') then
			
			if(params->>'id' is null or params->>'id' = '' or (params->>'id')::int =0) then
				_ret_id=nextval('mom_id_seq'::regclass);
			else
				_ret_id= params->>'id';
			end if;
			_orderstype_code=btrim(params->>'code');
			IF NOT EXISTS(SELECT 1 FROM sys.mom WHERE lower("code")= lower(_orderstype_code)) then
			
				INSERT INTO mom(id,name,code,description,"groups",isactive,createdon,createdby,logo)
				values ((_ret_id):: int,params->>'name', params->>'code',params->>'description','ordertype',(params->>'active')::boolean,current_timestamp,(params->>'createdby')::uuid,params->>'logo');
				
				res := '{"status": true, "result":"Inserted successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
				
			else
			
				UPDATE mom SET (name,code,description,isactive,updatedon,updatedby,logo)
				= (params->>'name',params->>'code',params->>'description',(params->>'active')::boolean,current_timestamp,(params->>'createdby')::uuid,params->>'logo')
				WHERE id=(params->>'id')::int;
			
				res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
			END IF;
		
		end if; --end crud
		
		exception when others then 
		rollback;
		res := ('{"status": false, "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}')::json;
		RAISE NOTICE '%', res;
	commit;
	END;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_product(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
declare _productid uuid default null;
declare _productmasterid int default 0;
declare _isdefault int default 0;
	begin
		
		

	-- call func.sp_product('{"schema":"sys", "operate":"crud"}','{"data":[{"id":4,"column_name":"name","label":"Name","datatype":"input","column_order":1,"module":"company","extra":{"allow"["LIKE","="]},"column_type":1,"isfilter":0,"issort":0,"defaultselected":1,"searchselected":0,"fixedselected":0,"hidden":0,"createdon":"2019-11-21 10:18:27.691548","createdby":null,"updatedon":null,"updatedby":null,"isactive":true,"isdelete":false}');
	
	-- this line should be in every procedure
	perform public.setschema(sysparams->>'schema');



IF (sysparams->>'operate' = 'crud') THEN 
	_productid :=public.uuid_generate_v1();
	_productmasterid := (case when (params->>'productid')::int=0 then nextval('product_id_seq'::regclass) else (params->>'productid')::int end);
	_isdefault :=(case when (params->>'productid')::int=0 then 1 else 0 end);
	
	-- create effect entry into mom table.
	insert into mom (code,name,groups,createdon,createdby,isactive,isdelete,dispensaryid)
	select lower((a->>'name')::character varying) as code,(a->>'name')::character varying as name,'effect',current_timestamp,(params->>'userid')::uuid,true,false,(params->>'dispensary')::uuid from 
	jsonb_array_elements((params->>'effects')::jsonb) a left outer join mom m  on 
	lower(a.name)=lower(m.name) and groups='effect' and m.dispensaryid=(params->>'dispensary')::uuid
	where m.name is null
	on conflict(code,"groups",isactive,dispensaryid)
	do update set isactive=true,isdelete=false;

	-- End

	INSERT INTO product(id,"name",shortdesc,promos_applicable,brandid,productreleasedate,typeid,categoryid,subcatid,tags,published,createdon,createdby,dispensaryid,uom,"attributes",extraction,puid,isnew,tax,showonhome,purpose,hidfrmscren,editseolabel,seolabel,metatitle)
				
	values (
	_productmasterid,
	(params->>'productname')::varchar,
	(params->>'shortdescription')::text,
	null,
	(params->>'brand')::int,
	(params->>'releasedate')::Date,
	(params->>'classification')::int,
	(params->>'category')::int,
	(params->>'subcategory')::int,
	public.replacearray(params->>'tags')::character varying[],
	true,
	current_timestamp,
	(params->>'userid')::uuid,
	(params->>'dispid')::uuid,
	(params->>'uom')::int,
	public.replacearray(params->>'attributes')::character varying[],
	(params->>'extraction')::int,
	_productid,
	(params->>'isnew')::int,
	public.replacearray(params->>'tax')::int[],
	(params->>'showonhome')::int,
	public.replacearray(params->>'purpose')::int[],
	(params->>'hidefromscreen')::boolean,
	(params->>'editseolabel')::boolean,
	(params->>'seolabel')::character varying,
	(params->>'metatitle')::character varying
	)
	ON conflict(id)
	do update 
	
	set 
	"name"=(params->>'productname')::varchar,
	shortdesc=(params->>'shortdescription')::text,
	promos_applicable=null,
	brandid=(params->>'brand')::int,
	productreleasedate=(params->>'releasedate')::Date,
	typeid=(params->>'classification')::int,
	categoryid=(params->>'category')::int,
	subcatid=(params->>'subcategory')::int,
	tags=public.replacearray(params->>'tags')::character varying[],
	published=true,
	updatedon=current_timestamp,
	updatedby=(params->>'userid')::uuid,
	isactive=true,
	isdelete=false,
	dispensaryid=(params->>'dispid')::uuid,
	uom=(params->>'uom')::int,
	"attributes"=public.replacearray(params->>'attributes')::character varying[],
	extraction=(params->>'extraction')::int,
	isnew=(params->>'isnew')::int,
	tax=public.replacearray(params->>'tax')::int[],
	showonhome=(params->>'showonhome')::int,
	purpose=public.replacearray(params->>'purpose')::int[],
	hidfrmscren=(params->>'hidefromscreen')::boolean,
	editseolabel=(params->>'editseolabel')::boolean,
	seolabel=(params->>'seolabel')::character varying,
	metatitle=(params->>'metatitle')::character varying
	;




insert into productdetails(id,productuid,col1,col2,col3,col4,col5,price,sellingprice,img1,img2,img3,video,outstockqty,createdon,createdby,isactive,isdelete,dispensaryid,minvisinventorylevel,totalmgthc,totalmgcbd,attributevalues,sku,effects,barcode,description,variantname,productid,isdefault)

VALUES(case when (params->>'productdetailid')::int=0 then nextval('productdetails_id_seq'::regclass) else (params->>'productdetailid')::int end,
_productid,
(params->>'col1')::character varying,
(params->>'col2')::character varying,
(params->>'col3')::character varying,
(params->>'col4')::character varying,
(params->>'col5')::character varying,
(params->>'regular_price')::float,
(params->>'selling_price')::float,
(params->>'img1')::character varying,
(params->>'img2')::character varying,
(params->>'img3')::character varying,
null,
0,
current_timestamp,
(params->>'userid')::uuid,
true,
false,
(params->>'dispid')::uuid,
(params->>'mvil')::int,
(params->>'tmt')::float,
(params->>'tmcc')::float,
(params->>'attributeValue')::jsonb,
(params->>'sku')::character varying,
(params->>'effects')::jsonb,
(params->>'barcode')::character varying,
(params->>'description')::text,
(params->>'variantname')::character varying,
_productmasterid,
_isdefault)

ON conflict(id)
	do update 
	
	set 
col1=(params->>'col1')::character varying,
col2=(params->>'col2')::character varying,
col3=(params->>'col3')::character varying,
col4=(params->>'col4')::character varying,
col5=(params->>'col5')::character varying,
price=(params->>'regular_price')::float,
sellingprice=(params->>'selling_price')::float,
img1=(params->>'img1')::character varying,
img2=(params->>'img2')::character varying,
img3=(params->>'img3')::character varying,
video=null,
outstockqty=0,
updatedon=current_timestamp,
updatedby=(params->>'userid')::uuid,
isactive=true,
isdelete=false,
dispensaryid=(params->>'dispid')::uuid,
minvisinventorylevel=(params->>'mvil')::int,
totalmgthc=(params->>'tmt')::float,
totalmgcbd=(params->>'tmcc')::float,
attributevalues=(params->>'attributeValue')::jsonb,
sku=(params->>'sku')::character varying,
effects=(params->>'effects')::jsonb,
barcode=(params->>'barcode')::character varying,
description=(params->>'description')::text,
variantname=(params->>'variantname')::character varying;

		
res := ('{"status": true, "msg": "inserted successfully", "type":1}')::json;


elseif(sysparams->>'operate' = 'updatelivepeople') then

-- update product set livepeople=floor(random()*6+2) where published=true;

-- res := ('{"status": true, "msg": "updated successfully", "type":1}')::json;


elseif(sysparams->>'operate' = 'saveaddon') then

insert into addonproducts(product_id,dispensary_id,created_at) values
((params->>'product_id')::int,(params->>'dispensary')::uuid,current_timestamp)
ON conflict(product_id,dispensary_id)
do update 
set 
created_at = excluded.created_at
;

res := ('{"status": true, "msg": "Product added successfully", "type":1}')::json;

elseif(sysparams->>'operate' = 'deleteaddonproduct') then
	delete from addonproducts bpm where bpm.id=(params->>'id')::int;
	res := ('{"status": true, "msg": "Record deleted successfully", "type":1}')::json;

elseif(sysparams->>'operate' = 'update_points') then
	
	update product as p 
	set catalog_points = (tmp->>'points')::integer
	from json_array_elements((params)::json) as tmp
	where (tmp->>'id')::integer = p.id and p.dispensaryid = (tmp->>'dispensaryid')::uuid;
	insert into product_points(product_id,qty,thc,sellable_qty,inv_days,total_img,total_desc,updated_at)
	select (tmp->>'id')::int,(tmp->>'total_qty')::int,(tmp->>'total_thc')::int,(tmp->>'total_sell_qty')::int,(tmp->>'total_invdays')::int,(tmp->>'total_img')::int,(tmp->>'total_desc')::int,current_timestamp from json_array_elements((params)::json) as tmp
	ON conflict(product_id)
	do update 
	set 
	qty=excluded.qty,
	thc=excluded.thc,
	sellable_qty=excluded.sellable_qty,
	inv_days=excluded.inv_days;
	res := ('{"status": true, "msg": "Record sync successfully", "type":1}')::json;

		 
END IF;  -- end "crud" operate
   
   
	-- exception should be at the end of procedure
	exception when others then 
		res := ('{"status": false, "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}')::json;
		



	RAISE NOTICE '%', res;
	
	
	END;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CREATE OR REPLACE PROCEDURE func.sp_virtualdispensary(sysparams json, params json, INOUT res json DEFAULT '{}'::json)
 LANGUAGE plpgsql
AS $procedure$ 
DECLARE err_msg varchar(200); _ret_id uuid;_timezoneoffset varchar(100);
begin 
	
	-- this line should be in every procedure
	perform public.setschema(sysparams->>'schema');
	_ret_id := (coalesce(params->>'id',null))::uuid;
	-- specify the operation needs to be perform
	IF (sysparams->>'operate' = 'crud') THEN 
	
	--call func.sp_dispensary('{"schema":"cmp1", "operate":"crud"}','{"id":"1c9f3ad2-1059-11ea-8d31-87932fe6e9f8","name":"test dispensary 1 dsfds fd","address":"This is test address", "city":1, "state":2, "country":3, "zip":"[1234,5678]", "phone":"1234567890", "contact1":"", "email":"xyz@gmail.com", "banner":"", "domain":"", "timezone":"", "userid":"123e4567-e89b-12d3-a456-426655447878","logo":""}');
		IF EXISTS(select 1 from dispensary where lower(slug)=lower(params->>'slug') and id!=(params->>'id')::uuid) then
			raise exception '%','Slug is already exist';
			-- res := '{"status": false, "result":"","errorcode":"", "msg": "Slug is already exist", "type":1}';
		END if; 
		SELECT unixtimezone INTO _timezoneoffset FROM  sys.utctimezones WHERE id =  (coalesce(params->>'timezone',  '0'))::int;
		IF NOT EXISTS(SELECT 1 FROM dispensary WHERE id=(params->>'id')::uuid) then
		
			--_ret_id := public.uuid_generate_v1();
		
			INSERT INTO dispensary(id,"name",address1,address2,city,state,country,zip,phone,contact1,email,banner,"domain",timezone,createdon,createdby,logo,orderPrefix,timezoneoffset,polygon,otherdata,purpose,integration,payment,orderserial,latlng,maxradius,config,thumbnail,invdefination,mobilebanner,skipinventory,slug,isvirtual,parentid,deliveryaddresses,schedule_order,email_ad_image)
			VALUES (_ret_id, params->>'name',params->>'address1',params->>'address2',(params->>'city')::int,(params->>'state')::int,(params->>'country')::int,public.replacearray(params->>'zip')::character varying[],params->>'phone',params->>'contact1',params->>'email',params->>'banner',params->>'domain',params->>'timezone',current_timestamp,(params->>'userid')::uuid,params->>'logo',params->>'orderPrefix',_timezoneoffset,case when coalesce(params->>'polygon','')<>'' then public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(params->>'polygon')::character varying,'))')),26191) else null end,(params->>'otherdata')::jsonb,public.replacearray(params->>'purpose')::int[],(params->>'integration')::jsonb,(params->>'payment')::jsonb,(params->>'orderserial')::int,POINT((params->>'lng')::double precision,(params->>'lat')::double precision),(20)::double precision,(params->>'config')::jsonb,params->>'thumbnail',(params->>'invdefination')::jsonb,params->>'mobilebanner',(params->>'skipinventory')::boolean,(params->>'slug')::character varying,true,(params->>'parentid')::uuid,(params->>'addressList')::jsonb,(params->>'schedule_order')::date,params->>'email_ad_image');
		
			res := '{"status": true, "result":"Inserted successfully","errorcode":"", "msg": "'||_ret_id||'", "type":1}';
		
		else
		
		
			UPDATE dispensary SET ("name",address1,address2,city,state,country,zip,phone,contact1,email,banner,"domain",
			timezone,updatedon,updatedby,logo,orderPrefix,isactive,timezoneoffset,polygon,otherdata,purpose,integration,orderserial,latlng,maxradius,config,thumbnail,invdefination,mobilebanner,skipinventory,slug,deliveryaddresses,schedule_order,email_ad_image)
				= (params->>'name',params->>'address1',params->>'address2',(params->>'city')::int,(params->>'state')::int,
				(params->>'country')::int,public.replacearray(params->>'zip')::character varying[],
				params->>'phone',params->>'contact1',params->>'email',params->>'banner',params->>'domain',
				params->>'timezone',current_timestamp,(params->>'userid')::uuid,params->>'logo',params->>'orderPrefix',
				(coalesce(params->>'active', 'true'))::boolean,_timezoneoffset,
				case when coalesce(params->>'polygon','')<>'' then public.ST_SetSRID(public.ST_GeomFromText(concat('POLYGON((',(params->>'polygon')::character varying,'))')),26191) else null end,
				(params->>'otherdata')::jsonb,
				public.replacearray(params->>'purpose')::int[],(params->>'integration')::jsonb,(params->>'orderserial')::int,POINT((params->>'lng')::double precision,(params->>'lat')::double precision),(20)::double precision,(params->>'config')::jsonb,params->>'thumbnail',(params->>'invdefination')::jsonb,params->>'mobilebanner',(params->>'skipinventory')::boolean,(params->>'slug')::character varying,(params->>'addressList')::jsonb,(params->>'schedule_order')::date,params->>'email_ad_image')
			WHERE id=(params->>'id')::uuid;
		
			res :='{"status": true, "result":"Updated successfully","errorcode":"", "msg":"'||_ret_id||'", "type":2}';
		
		END IF;
		INSERT INTO dispensarytime (id,fromtime,totime,"day",createdon,dispensaryid,timezone,nonkiosk,isactive,cutofftime)
		select
			(case when coalesce(x->>'id','0') = '' then nextval('dispensarytime_autoid_seq'::regclass)::text else x->>'id' end )::bigint as id,
			(coalesce(x->>'openTime','00:00:00'))::time as fromtime,
			(coalesce(x->>'closeTime','00:00:00'))::time as totime ,
			(x->>'value')::int as "day",
			current_timestamp as createdon,
			coalesce((params->>'id')::uuid , _ret_id) as dispensaryid,
			coalesce(_timezoneoffset, 'UTC'),
			coalesce((x->>'nonkiosk')::boolean, false) as nonkiosk,
			coalesce((x->>'isactive')::boolean, true) as isactive,
			(coalesce(x->>'cutoffTime','00:00:00'))::time as cutofftime
		from json_array_elements((params->>'dispensaryKioskTime')::json) as x
	
		on conflict(id) 
		do update
		
		set
			fromtime=excluded.fromtime,totime=excluded.totime,"day"=excluded."day",updatedon=current_timestamp,timezone = coalesce(_timezoneoffset, 'UTC'),nonkiosk=excluded.nonkiosk,isactive=excluded.isactive,cutofftime=excluded.cutofftime;
		
		
		update dispensary as d
		set stronghold_ext_prefix= s.stronghold_ext_prefix,
		stronghold_priv_key=s.stronghold_priv_key,
		stronghold_pub_key=s.stronghold_pub_key,
		terminalid=s.terminalid
		from dispensary as s
		where d.parentid = s.id and d.id = (params->>'id')::uuid;

		/*INSERT INTO dispensarytime (id,fromtime,totime,"day",createdon,dispensaryid,timezone,nonkiosk,isactive,cutofftime)
		select
			(case when coalesce(x->>'id','0') = '' then nextval('dispensarytime_autoid_seq'::regclass)::text else x->>'id' end )::bigint as id,
			(coalesce(x->>'openTime','00:00:00'))::time as fromtime,
			(coalesce(x->>'closeTime','00:00:00'))::time as totime ,
			(x->>'value')::int as "day",
			current_timestamp as createdon,
			coalesce((params->>'id')::uuid , _ret_id) as dispensaryid,
			coalesce(_timezoneoffset, 'UTC'),
			coalesce((x->>'nonkiosk')::boolean, true) as nonkiosk,
			coalesce((x->>'isactive')::boolean, true) as isactive,
			(coalesce(x->>'cutoffTime','00:00:00'))::time as cutofftime
		from json_array_elements((params->>'dispensaryOrderTime')::json) as x
	
		on conflict(id) 
		do update
		
		set
			fromtime=excluded.fromtime,totime=excluded.totime,"day"=excluded."day",updatedon=current_timestamp,timezone = coalesce(_timezoneoffset, 'UTC'),nonkiosk=excluded.nonkiosk,isactive=excluded.isactive,cutofftime=excluded.cutofftime;*/
	-- end "crud" operate
	
	
	ELSIF (sysparams->>'operate' = 'active') THEN 
	
	--call func.sp_dispensary('{"schema":"cmp1", "operate":"active"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d","active": false}');	
		UPDATE dispensary SET (updatedon,updatedby,isactive)
		= (current_timestamp,(params->>'userid')::uuid,(coalesce(params->>'active', 'true'))::boolean)
			WHERE id=(params->>'id')::uuid;
			res :='{"status": true, "result":"'|| case when ((coalesce(params->>'active', 'true'))::boolean) then 'Activated' else 'Deactivated' end||' successfully","errorcode":"", "msg": "'||_ret_id||'", "type":2}';
		
		
	ELSIF (sysparams->>'operate' = 'delete') THEN 
--call func.sp_dispensary('{"schema":"cmp1", "operate":"delete"}','{"id":"342424de-1053-11ea-a899-3fa140ed7e8d"}');
			UPDATE dispensary SET (updatedon,updatedby,isactive, isdelete)
		= (current_timestamp,(params->>'userid')::uuid,
		case when ((coalesce(params->>'delete', 'true'))::boolean) then false else true end, 
		(coalesce(params->>'delete', 'true'))::boolean)
			WHERE id=(params->>'id')::uuid;
			res :='{"status": true, "result":"'|| case when ((coalesce(params->>'delete', 'true'))::boolean) then 'Deleted' else 'Restored' end||' successfully","errorcode":"", "msg": "'||_ret_id||'", "type":2}';
	
	END IF;  
   
   
	-- exception should be at the end of procedure
	exception when others then 
		res :='{"status": false, "result":"","errorcode":"", "msg": "'||REPLACE(SQLERRM, '"', '''')||'", "type":0}';
				



	RAISE NOTICE '%', res;
	
end;
$procedure$
;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@