var con= new Npgsql.NpgsqlConnection(strConstring);
con.Open();
var command = new Npgsql.NpgsqlCommand("MyFunction", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
var adapter= new Npgsql.NpgsqlDataAdapter(command);
var ds = new System.Data.DataSet();
adapter.Fill(ds);
Imports System.ComponentModel
Imports System.Linq
Public Class ExportResultData
Public Shared Function Create(raw_data As String) As IEnumerable(Of ExportResultData)
Dim dataResult = New List(Of ExportResultData)()
Dim tokens = raw_data.Replace("\", String.Empty).Replace("""", String.Empty).Split(New String() {Environment.NewLine, "(", ")"}, StringSplitOptions.RemoveEmptyEntries)
tokens = tokens.Where(Function(n, i) (i Mod 2 <> 0)).ToArray()
For Each token As String In tokens
Dim values = token.Split(New Char() {"{"c, "}"c}, StringSplitOptions.RemoveEmptyEntries)
Dim list = New List(Of String)()
If values.Length = 1 Then
list.AddRange(values(0).Split(New Char() {","c}, StringSplitOptions.None))
Else
Dim vals = values(0).Split(New Char() {","c}, StringSplitOptions.None)
list.AddRange(vals.Take(vals.Length - 1))
list.Add(values(1))
vals = values(2).Split(New Char() {","c}, StringSplitOptions.None)
list.AddRange(vals.Skip(1))
End If
dataResult.Add(New ExportResultData() With { _
.account_no = TryParse(Of Integer)("account_no", list(0)), _
.lastname = list(1), _
.firstname = list(2), _
.address = list(3), _
.address_no = list(4), _
.zip = list(5), _
.city = list(6), _
.reference = list(7), _
.rf_tags_id = TryParseArray(Of Integer)("rf_tags_id", list(8)), _
.ecowaste_units = TryParse(Of Integer)("ecowaste_units", list(9)), _
.masse_en_kg = TryParse(Of Decimal)("masse_en_kg", list(10)), _
.cout_absolut = TryParse(Of Decimal)("cout_absolut", list(11)), _
.cout_arrondi = TryParse(Of Decimal)("cout_arrondi", list(12)) _
})
Next
Return dataResult
End Function
Public Property account_no() As System.Nullable(Of Integer)
Get
Return m_account_no
End Get
Private Set(value As System.Nullable(Of Integer))
m_account_no = value
End Set
End Property
Private m_account_no As System.Nullable(Of Integer)
Public Property lastname() As String
Get
Return m_lastname
End Get
Private Set(value As String)
m_lastname = value
End Set
End Property
Private m_lastname As String
Public Property firstname() As String
Get
Return m_firstname
End Get
Private Set(value As String)
m_firstname = value
End Set
End Property
Private m_firstname As String
Public Property address() As String
Get
Return m_address
End Get
Private Set(value As String)
m_address = value
End Set
End Property
Private m_address As String
Public Property address_no() As String
Get
Return m_address_no
End Get
Private Set(value As String)
m_address_no = value
End Set
End Property
Private m_address_no As String
Public Property zip() As String
Get
Return m_zip
End Get
Private Set(value As String)
m_zip = value
End Set
End Property
Private m_zip As String
Public Property city() As String
Get
Return m_city
End Get
Private Set(value As String)
m_city = value
End Set
End Property
Private m_city As String
Public Property reference() As String
Get
Return m_reference
End Get
Private Set(value As String)
m_reference = value
End Set
End Property
Private m_reference As String
Public Property rf_tags_id() As IEnumerable(Of Integer)
Get
Return m_rf_tags_id
End Get
Private Set(value As IEnumerable(Of Integer))
m_rf_tags_id = value
End Set
End Property
Private m_rf_tags_id As IEnumerable(Of Integer)
Public Property ecowaste_units() As System.Nullable(Of Integer)
Get
Return m_ecowaste_units
End Get
Private Set(value As System.Nullable(Of Integer))
m_ecowaste_units = value
End Set
End Property
Private m_ecowaste_units As System.Nullable(Of Integer)
Public Property masse_en_kg() As System.Nullable(Of Decimal)
Get
Return m_masse_en_kg
End Get
Private Set(value As System.Nullable(Of Decimal))
m_masse_en_kg = value
End Set
End Property
Private m_masse_en_kg As System.Nullable(Of Decimal)
Public Property cout_absolut() As System.Nullable(Of Decimal)
Get
Return m_cout_absolut
End Get
Private Set(value As System.Nullable(Of Decimal))
m_cout_absolut = value
End Set
End Property
Private m_cout_absolut As System.Nullable(Of Decimal)
Public Property cout_arrondi() As System.Nullable(Of Decimal)
Get
Return m_cout_arrondi
End Get
Private Set(value As System.Nullable(Of Decimal))
m_cout_arrondi = value
End Set
End Property
Private m_cout_arrondi As System.Nullable(Of Decimal)
Private Shared Function TryParseArray(Of T As Structure)(field_name As String, input As String) As IEnumerable(Of T)
If String.IsNullOrEmpty(input) Then
Return Nothing
End If
Return input.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries).[Select](Function(n)
Return TryParse(Of T)(field_name, n).Value
End Function)
End Function
Private Shared Function TryParse(Of T As Structure)(field_name As String, input As String) As System.Nullable(Of T)
If String.IsNullOrEmpty(input) Then
Return New Nullable(Of T)()
End If
Try
Return DirectCast(TypeDescriptor.GetConverter(GetType(T)).ConvertFromString(input), T)
Catch ex As Exception
Throw New Exception(String.Format("Could not convert input [{0}] for field {1} typeof {2}", input, field_name, GetType(T)), ex)
End Try
End Function
End Class
"{\"(10004,Aeby,Aloys,\\\"Rte du Vignoble\\\",33,1175,lala, \\\"\\\",\ \\"{1,2}\\ \",968,48. 4000015,26 .6200008,0 )\",\"(100 05,Aeby,\\ \"Andr¿ Christian\\\",\\\"Route de St-Livres\\\",7,1175,lala, AEBYAN000, \\\"{3,4}\ \\",1466,7 3.3000031, 40.3150024 ,0)\",\"(1 0006,Aeby, Freddy,\\\ "Route du Moulin Martinet\\\",11,1175,lala, AEBYFR000, ,,,,)\",\" (10009,All emand,\\\" Pierre Ambroise\\\",\\\"Rte du Vignoble\\\",3,1175,Lavign y,ALLEPI00 0,\\\"{7,8 }\\\",3590 ,179.5,98. 7249985,0) \"}"
----------------------------------------
--- INIT DELETING function and type ---
----------------------------------------
CREATE OR REPLACE FUNCTION reset() RETURNS void AS
$$
BEGIN
-- Drop FUNCTION IF EXISTS create_types();
Drop FUNCTION IF EXISTS create_types();
-- Drop IF EXISTS FUNCTION exporter_consommation_compte(ind_compte integer);
Drop FUNCTION IF EXISTS exporter_consommation_compte(id_compte integer);
Drop FUNCTION IF EXISTS exporter_consommation_liste_compte();
Drop FUNCTION IF EXISTS exporter_consomation_tous_compte();
--Drop IF EXISTS TYPE an_account_communal_export_line;
Drop TYPE IF EXISTS export_result;
-- Drop FUNCTION IF EXISTS eval_card(integer);
Drop FUNCTION IF EXISTS eval_cost(integer[]);
Drop FUNCTION IF EXISTS eval_units(integer[]);
END;
$$
LANGUAGE plpgsql;
SELECT reset();
----------------------------------------
--- TYPES DECLARATION -------------
----------------------------------------
CREATE TYPE export_result AS
(
account_no int,
lastname character varying(100),
firstname character varying(100),
address character varying(100),
address_no character varying(100),
zip character varying(200),
city character varying(100),
reference character varying(100),
rf_tags_id int[],
ecowaste_units integer,
masse_en_kg real,
cout_absolut real,
cout_arrondi real
);
----------------------------------------
--- FUNCTION DECLARATION -------------
----------------------------------------
CREATE FUNCTION create_types() RETURNS void AS
$$
DECLARE
nbr integer:= 0;
BEGIN
--IF NOT EXISTS TYPE an_account_communal_export_line THEN
--IF NOT EXISTS an_account_communal_export_line THEN
-- ELSE
-- END IF;
IF EXISTS export_result THEN
RAISE NOTICE 'le type - % - existe', export_result;
ELSE
RAISE NOTICE 'le type - % ¯';
END IF;
END;
$$
LANGUAGE plpgsql;
----------------------------------------
--- HELPERS ---------------------------
----------------------------------------
CREATE OR REPLACE FUNCTION eval_units(theCards integer[]) RETURNS integer AS
$$
DECLARE
_cardUnits integer;
_totalUnits integer := 0 ;
i integer;
BEGIN
FOREACH i IN array theCards
LOOP
SELECT SUM(weight) from trash_bag WHERE rf_card_id=i INTO _cardUnits;
IF _cardUnits is not null THEN
_totalUnits = _totalUnits + _cardUnits;
END IF;
END LOOP;
return _totalUnits;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION eval_cost(cardsList integer[]) RETURNS REAL AS
$$
DECLARE
i integer;
_totalCost real := 0.0;
_cardCost real := 0.0;
--trashes trash_bag%RECORD;
trashes trash_bag%ROWTYPE;
BEGIN
-- chercher les déchets des cartes
-- chercher les prix
-- calculer le cout
FOREACH i IN array cardsList
LOOP
SELECT SUM(price) from trash_bag WHERE rf_card_id=i INTO _cardCost;
--raise notice 'on calcule la carte id : % , cout % ', i, _cardCost;
IF _cardCost is not null THEN
_totalCost = _totalCost + _cardCost;
END IF;
END LOOP;
return _totalCost;
END;
$$
LANGUAGE plpgsql;
----------------------------------------
--- MEAT -----------------------------
----------------------------------------
CREATE FUNCTION exporter_consommation_compte(id_compte integer) RETURNS export_result AS
$$
DECLARE
--up_record RECORD; -- ligne de la table user_person
up_record user_person%ROWTYPE;
-- my cards_id as an array
--theCards integer[]:=ARRAY[1,2,3];
theCards integer[]; -- tab pour les cartes du compte
tempCard integer; -- valeur de recherche
-- trashes for cards.
-- recover prices per waste
result export_result;
theCost real;
BEGIN
-- info output
-- RAISE NOTICE 'debut du traitement pour le compte no % ', id_compte;
-- recupérer les données - user_person - de l'utilisateur du compte
SELECT INTO up_record * FROM user_person WHERE user_person_id = id_compte;
-- info output
--RAISE NOTICE 'client: %s %s', up_record.firstname, up_record.lastname;
-- result compte user info
result.account_no = id_compte;
result.firstname = up_record.firstname;
result.lastname = up_record.lastname;
result.address = up_record.address;
result.address_no = up_record.address_no;
result.zip = up_record.zip;
result.city = up_record.city;
result.reference = up_record.reference;
FOR tempCard IN (SELECT rf_card_id FROM rf_card WHERE account_id = id_compte) LOOP
IF tempCard is not null THEN
theCards := array_append(theCards, tempCard) ;
-- recupérer les cartes pour le comte
-- RAISE NOTICE ' carte id: % ', tempCard;
END IF;
END LOOP;
IF theCards is not null THEN
-- on demande le calcul des couts.
result.rf_tags_id = theCards;
result.ecowaste_units = eval_units(theCards); -- todo: le tout d'une fonction, l'éclater ici
result.cout_absolut = eval_cost(theCards);
result.masse_en_kg = result.ecowaste_units * 0.05;
result.cout_arrondi = 0.00;
END IF;
return result;
END ;
$$
LANGUAGE plpgsql;
--CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte(AnIdAccountlist integer[]) RETURNS setof export_result AS
-- CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte() RETURNS setof export_result AS
CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte() RETURNS export_result[] AS
$$
DECLARE
comptes integer[] := array[10004, 10005, 10006, 10009];
i integer;
temp RECORD;
result export_result[];
BEGIN
-- for i in 1 .. array_upper(comptes, 1)
for i in 1 .. array_upper(comptes, 1)
LOOP
-- SELECT * FROM exporter_consommation_compte(comptes[i]) into temp;
-- SELECT INTO result * FROM exporter_consommation_compte(comptes[i]) ;
-- result := exporter_consommation_compte(comptes[i]);
result[i] = exporter_consommation_compte(comptes[i]);
-- return next result[i];
-- Copy exporter_consommation_compte(comptes[i]) To 'C:/temp/postgres/test.csv' With CSV;
-- COPY result[i] TO 'c:/temp/postgres/test.csv' WITH CSV;
END LOOP;
-- on écrit dans un fichier csv pour l'export
-- Copy (Select * From result) To 'C:/temp/postgres/test.csv' With CSV;
-- Copy result[1] To 'C:/temp/postgres/test.csv' With CSV;
-- Copy result To 'C:/temp/postgres/test.csv' With CSV;
-- Copy 'yeahhh! ' To 'C:/temp/postgres/test.csv' With CSV;
RETURN result;
END;
$$
LANGUAGE plpgsql;
/*
CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte() RETURNS export_result[] AS
$$
DECLARE
row_account user_person%ROWTYPE;
result export_result[];
i integer := 0;
BEGIN
FOR row_account IN SELECT * FROM user_person WHERE user_person_id < 1000000
LOOP
-- RAISE NOTICE '%', exporter_consommation_compte(row_account.user_person_id);
result[i] = exporter_consommation_compte(row_account.user_person_id);
i := i+1;
END LOOP;
RETURN result;
END;
$$
LANGUAGE plpgsql;
*/
CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte() RETURNS setof export_result AS
$$
DECLARE
row_account user_person%ROWTYPE;
result export_result[];
i integer := 0;
aRow export_result;
BEGIN
-- temp table to be returned
CREATE TEMPORARY TABLE export_result_table(
account_no int,
lastname character varying(100),
firstname character varying(100),
address character varying(100),
address_no character varying(100),
zip character varying(200),
city character varying(100),
reference character varying(100),
rf_tags_id int[],
ecowaste_units integer,
masse_en_kg real,
cout_absolut real,
cout_arrondi real
) on commit drop;
FOR row_account IN SELECT * FROM user_person WHERE user_person_id < 1000000
LOOP
-- RAISE NOTICE '%', exporter_consommation_compte(row_account.user_person_id);
-- result[i] = exporter_consommation_compte(row_account.user_person_id);
aRow = exporter_consommation_compte(row_account.user_person_id);
-- INSERT INTO temp_payroll_val VALUES(
INSERT INTO export_result_table VALUES(
aRow.account_no,
aRow.lastname,
aRow.firstname,
aRow.address ,
aRow.address_no,
aRow.zip,
aRow.city,
aRow.reference,
aRow.rf_tags_id ,
aRow.ecowaste_units,
aRow.masse_en_kg ,
aRow.cout_absolut,
aRow.cout_arrondi
);
-- i := i+1;
-- RETURN NEXT export_result_table ;
END LOOP;
-- RETURN result;
-- RETURN temp_payroll_val;
RETURN export_result_table;
END;
$$
LANGUAGE plpgsql;
--- adding a row of type export_result
--- adding the tmeporary table
--- "on commit drop" at the end of the table
---
/*
utilisation de la procedure
*/
--SELECT create_types();
--SELECT * FROM exporter_consommation_compte(10015);
-- NOTE: test court
--SELECT * FROM exporter_consommation_liste_compte();
-- SELECT exporter_consommation_liste_compte();
--
SELECT exporter_consommation_tous_compte();
--SELECT * FROM exporter_consommation_tous_compte();
--
-- NOTE: test long final
--SELECT * FROM exporter_consomation_tous_compte();
--
--
--
--
RETURN NEXT export_result_table ;
try:RETURN QUERY(
select * from export_result_table
);
i can "serialize" this string into an object if you could tell me what is the structure.
cheers