public static List<VatCode> List()
{
List<VatCode> result = new List<VatCode> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("moms");
qb.Columns ("moms");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetString (qb.ColumnPos ("moms"))));
}
catch
{
}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
public static string Get(string Url)
{
string result = string.Empty;
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("xmlresponse");
qb.AddWhere ("url", "=", Url);
Query query = DbConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result = query.GetString (qb.ColumnPos ("xmlresponse"));
}
}
query.Dispose();
query = null;
qb = null;
return result;
}
public static OrderLine Load(string Id)
{
bool success = false;
OrderLine result = new OrderLine ();
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordlinie");
qb.Columns
(
"sidstrettet",
"varenummer",
"linienr",
"antal",
"pris",
"belxb",
"tekst",
"enhed",
"oprettet"
);
qb.AddWhere ("lxbenummer like '"+ Id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = Id;
result._createtimestamp = SNDK.Date.DateTimeToTimestamp (query.GetDateTime (qb.ColumnPos ("oprettet")));
result._productid = query.GetString (qb.ColumnPos ("varenummer"));
result._sort = query.GetDecimal (qb.ColumnPos ("linienr"));
result._amount = query.GetDecimal (qb.ColumnPos ("antal"));
result._price = query.GetDecimal (qb.ColumnPos ("pris"));
result._total = query.GetDecimal (qb.ColumnPos ("belxb"));
result._text = query.GetString (qb.ColumnPos ("tekst"));
result._unit = query.GetString (qb.ColumnPos ("enhed"));
result._updatetimestamp = SNDK.Date.DateTimeToTimestamp (query.GetDateTime (qb.ColumnPos ("sidstrettet")));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
// Exception: Exception.OrderLineLoadId
throw new Exception (string.Format (Strings.Exception.OrderLineLoadId, Id));
}
}
// Load notes.
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("notat");
qb.Columns
(
"tekst"
);
qb.AddWhere ("notatrecid = '"+ Id +"'");
qb.OrderBy ("linienummer", QueryBuilderOrder.Accending);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
result._notes += query.GetString (qb.ColumnPos ("tekst")) +"\n";
}
}
query.Dispose ();
query = null;
qb = null;
result._notes = result._notes.TrimEnd ("\n".ToCharArray ());
}
// TEMP1
result._temp1 = false;
return result;
}
public static List<CountryCode> List(string Zone)
{
List<CountryCode> result = new List<CountryCode> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
if (Zone != string.Empty)
{
qb.AddWhere ("dialcodes", "like", "%;"+ Zone +"%");
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
public static Invoice Get(int invoice)
{
Invoice result = new Invoice ();
// GET JOURNAL
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("debjournal");
qb.Columns
(
"dato",
"forfald",
"saldodkk",
"momsberegnes",
"moms",
"transaktion"
);
qb.AddWhere ("faktura = "+ invoice.ToString ());
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._transactionid = query.GetInt (qb.ColumnPos ("transaktion"));
result._date = query.GetDateTime (qb.ColumnPos ("dato"));
result._duedate = query.GetDateTime (qb.ColumnPos ("forfald"));
result._total = query.GetDecimal (qb.ColumnPos ("saldodkk"));
result._subtotal = query.GetDecimal (qb.ColumnPos ("momsberegnes"));
result._vat = query.GetDecimal (qb.ColumnPos ("moms"));
}
}
query.Dispose ();
query = null;
qb = null;
}
// GET ORDKARTARKIV
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordkartarkiv");
qb.Columns
(
"konto",
"navn",
"adresse1",
"adresse2",
"postby",
"attention"
);
qb.AddWhere ("fakturafxlgeseddel = "+ invoice.ToString ());
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._accountid = query.GetString (qb.ColumnPos ("konto"));
result._name = query.GetString (qb.ColumnPos ("navn"));
result._address1 = query.GetString (qb.ColumnPos ("adresse1"));
result._address2 = query.GetString (qb.ColumnPos ("adresse2"));
result._zipcode = query.GetString (qb.ColumnPos ("postby"));
result._city = query.GetString (qb.ColumnPos ("postby"));
result._attention = query.GetString (qb.ColumnPos ("attention"));
}
}
query.Dispose ();
query = null;
qb = null;
}
// GET DEBPOST
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("debpost");
qb.Columns
(
"posttype"
);
qb.AddWhere ("bilag = "+ invoice.ToString ());
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
if (query.GetInt (qb.ColumnPos ("posttype")) == 1)
{
result._type = Enums.InvoiceType.Invoice;
}
else if (query.GetInt (qb.ColumnPos ("posttype")) == 2)
{
result._type = Enums.InvoiceType.InvoiceCredit;
}
}
}
query.Dispose ();
query = null;
qb = null;
}
// GET ORDLINEARKIV
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordliniearkiv");
qb.Columns
(
"linienr",
"varenummer",
"antal",
"enhed",
"pris",
"rabat",
"belxb",
"tekst",
"lxbenummer"
);
qb.AddWhere ("transaktion = "+ result._transactionid);
qb.OrderBy ("linienr", QueryBuilderOrder.Accending);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
InvoiceLine line = new InvoiceLine ();
line._linenumber = query.GetDecimal (qb.ColumnPos ("linienr"));
line._partnumber = query.GetString (qb.ColumnPos ("varenummer"));
line._amount = query.GetDecimal (qb.ColumnPos ("antal"));
line._price = query.GetDecimal (qb.ColumnPos ("pris"));
line._unit = query.GetString (qb.ColumnPos ("enhed"));
line._discount = query.GetDecimal (qb.ColumnPos ("rabat"));
line._total = query.GetDecimal (qb.ColumnPos ("belxb"));
line._text = query.GetString (qb.ColumnPos ("tekst"));
line._sequenceno = query.GetInt (qb.ColumnPos ("lxbenummer"));
// GET NOTAT
{
QueryBuilder qb2 = new QueryBuilder (QueryBuilderType.Select);
qb2.Table ("notat");
qb2.Columns
(
"tekst"
);
qb2.AddWhere ("notatrecid = "+ line._sequenceno.ToString ());
Query query2 = Runtime.DBConnection.Query (qb2.QueryString);
if (query2.Success)
{
if (query2.NextRow ())
{
line._note = query2.GetString (qb2.ColumnPos ("tekst"));
}
}
query2.Dispose ();
query2 = null;
qb2 = null;
}
result._lines.Add (line);
}
}
query.Dispose ();
query = null;
qb = null;
}
return result;
}
public static List<RangeGroup> List()
{
List<RangeGroup> result = new List<RangeGroup> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
public static List<SubscriptionItem> List(Guid subscription)
{
List<SubscriptionItem> result = new List<SubscriptionItem> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
if (subscription != Guid.Empty)
{
qb.AddWhere ("subscriptionid" ,"=", subscription);
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
private static Range Load(Guid id, string dialcode)
{
bool success = false;
Range result = new Range ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"countrycodeid",
"type",
"name",
"dialcodes",
"costpriceids"
);
if (id != Guid.Empty)
{
qb.AddWhere ("id", "=", id);
}
else if (dialcode != string.Empty)
{
qb.AddWhere ("dialcodes like '%"+ dialcode +";%'");
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._countrycodeid = query.GetGuid (qb.ColumnPos ("countrycodeid"));
result._type = query.GetEnum<Enums.NumberType> (qb.ColumnPos ("type"));
result._name = query.GetString (qb.ColumnPos ("name"));
result._dialcodes = SNDK.Convert.StringToList<string> (query.GetString (qb.ColumnPos ("dialcodes")));
result._costpriceids = SNDK.Convert.StringToList<Guid> (query.GetString (qb.ColumnPos ("costpriceids")));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.RangeLoad, id));
}
return result;
}
public static Order Load(string Id)
{
bool success = false;
Order result = new Order ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordkart");
qb.Columns
(
"sidstrettet",
"oprettet",
"konto"
);
qb.AddWhere ("nummer like '%"+ Id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = Id;
result._createtimestamp = SNDK.Date.DateTimeToTimestamp (query.GetDateTime (qb.ColumnPos ("oprettet")));
result._updatetimestamp = SNDK.Date.DateTimeToTimestamp (query.GetDateTime (qb.ColumnPos ("sidstrettet")));
result._debitor = C5.Debitor.Load (query.GetString (qb.ColumnPos ("konto")).Replace (" ", ""));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
// Exception: OrderLoadId
throw new Exception (string.Format (Strings.Exception.OrderLoadId, Id));
}
// Load orderlines.
result._orderlines = OrderLine.List (result);
// TEMP2
result._temp2 = false;
return result;
}
public static Product Load(Guid Id)
{
bool success = false;
Product result = new Product ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"text",
"price",
"erpid"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._text = query.GetString (qb.ColumnPos ("text"));
result._price = query.GetDecimal (qb.ColumnPos ("price"));
result._erpid = query.GetString (qb.ColumnPos ("erpid"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.ProductLoad, Id));
}
return result;
}
// 2600
public static void GetInvoice(int invoice)
{
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("debjournal");
qb.Columns
(
"dato",
"forfald",
"saldodkk",
"momsberegnes",
"moms",
"transaktion"
);
qb.AddWhere ("faktura = "+ invoice.ToString ());
Query query = qnaxLib.Runtime.C5Connection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
Console.WriteLine (query.GetDateTime (qb.ColumnPos ("dato")));
Console.WriteLine (query.GetDateTime (qb.ColumnPos ("forfald")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("saldodkk")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("momsberegnes")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("moms")));
Console.WriteLine (query.GetInt (qb.ColumnPos ("transaktion")));
}
}
query.Dispose ();
}
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordkartarkiv");
qb.Columns
(
"konto",
"navn",
"adresse1",
"adresse2",
"postby",
"attention"
);
qb.AddWhere ("fakturafxlgeseddel = "+ invoice.ToString ());
Query query = qnaxLib.Runtime.C5Connection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
Console.WriteLine (query.GetString (qb.ColumnPos ("konto")));
Console.WriteLine (query.GetString (qb.ColumnPos ("navn")));
Console.WriteLine (query.GetString (qb.ColumnPos ("adresse1")));
Console.WriteLine (query.GetString (qb.ColumnPos ("adresse2")));
Console.WriteLine (query.GetString (qb.ColumnPos ("postby")));
Console.WriteLine (query.GetString (qb.ColumnPos ("attention")));
}
}
query.Dispose ();
}
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("debpost");
qb.Columns
(
"posttype"
);
qb.AddWhere ("bilag = "+ invoice.ToString ());
Query query = qnaxLib.Runtime.C5Connection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
Console.WriteLine (query.GetInt (qb.ColumnPos ("posttype")));
}
}
query.Dispose ();
}
// $nquery = mssql_query("SELECT * FROM ORDLINIEARKIV WHERE TRANSAKTION = ".$data['TRANSAKTION']." ORDER BY LINIENR ASC");
// while($ndata = mssql_fetch_array($nquery)){
// $fquery = mssql_query("SELECT * FROM NOTAT WHERE NOTATRECID=".$ndata['LXBENUMMER']);
// while($fdata = mssql_fetch_array($fquery)){
// if (substr($fdata['TEKST'], 1, 1) == ""){
// $notelines[] = "";
// } else {
// $notelines[] = $fdata['TEKST'];
// }
// $totalcount++;
// }
// $note = @implode("\n", $notelines);
//
// if (substr($ndata['ENHED'], 1) == ""){
// $enhed = "";
// } else {
// $enhed = $ndata['ENHED'];
// }
//
// $line = array(
// "id" => $ndata['LINIENR'],
// "itemnumber" => $ndata['VARENUMMER'],
// "itemamount" => $ndata['ANTAL'],
// "itemprice" => $ndata['PRIS'],
// "itemdiscount" => $ndata['RABAT'],
// "linetotal" => $ndata['BELXB'],
// "linetext" => $ndata['TEKST'],
// "linenote" => $note,
// "itemunit" => $enhed
// );
// $pricetotal = $pricetotal + $ndata['BELXB'];
// $totalcount++;
// $return['lines'][] = $line;
// unset($notelines);
// }
// $return['totalcount'] = $totalcount;
// return $return;
// 11808
{
// $nquery = mssql_query("SELECT * FROM ORDLINIEARKIV WHERE TRANSAKTION = ".$data['TRANSAKTION']." ORDER BY LINIENR ASC");
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordliniearkiv");
qb.Columns
(
"linienr",
"varenummer",
"antal",
"enhed",
"pris",
"rabat",
"belxb",
"tekst",
"lxbenummer"
);
qb.AddWhere ("transaktion = "+ "11808");
qb.OrderBy ("linienr", QueryBuilderOrder.Accending);
Query query = qnaxLib.Runtime.C5Connection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
Console.WriteLine ("---");
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("linienr")));
Console.WriteLine (query.GetString (qb.ColumnPos ("varenummer")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("antal")));
Console.WriteLine (query.GetString (qb.ColumnPos ("enhed")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("pris")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("rabat")));
Console.WriteLine (query.GetDecimal (qb.ColumnPos ("belxb")));
Console.WriteLine (query.GetString (qb.ColumnPos ("tekst")));
Console.WriteLine (query.GetInt (qb.ColumnPos ("lxbenummer")));
int test = query.GetInt (qb.ColumnPos ("lxbenummer"));
QueryBuilder qb2 = new QueryBuilder (QueryBuilderType.Select);
qb2.Table ("notat");
qb2.Columns
(
"tekst"
);
qb2.AddWhere ("notatrecid = "+ test.ToString ());
Query query2 = qnaxLib.Runtime.C5Connection.Query (qb2.QueryString);
if (query2.Success)
{
if (query2.NextRow ())
{
Console.WriteLine ("\t "+ query2.GetString (qb2.ColumnPos ("tekst")));
}
}
query2.Dispose ();
Console.WriteLine ("---");
}
}
query.Dispose ();
}
}
/// <summary>
/// Load a <see cref="CDRLib.SIPAccount"/> instance from database using a <see cref="System.Guid"/> identifier.
/// </summary>
public static SIPAccount Load(Guid Id)
{
bool success = false;
SIPAccount result = new SIPAccount ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"name",
"numbers"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._name = query.GetString (qb.ColumnPos ("name"));
foreach (string number in query.GetString (qb.ColumnPos ("numbers")).Split (";".ToCharArray (), StringSplitOptions.RemoveEmptyEntries))
{
// Console.WriteLine (number);
result._numbers.Add (new Number (SNDK.Convert.StringToEnum<Enums.NumberType> (number.Split ("|".ToCharArray ())[0]), number.Split ("|".ToCharArray ())[1], SNDK.Convert.StringToBool (number.Split ("|".ToCharArray ())[2])));
}
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.SIPAccountLoad, Id));
}
return result;
}
public static Location Load(Guid Id)
{
bool success = false;
Location result = new Location ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"parentid",
"title"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._parentid = query.GetGuid (qb.ColumnPos ("parentid"));
result._title = query.GetString (qb.ColumnPos ("title"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.ManagementLocationLoadGuid, Id));
}
return result;
}
public static Subscription Load(Guid Id)
{
bool success = false;
Subscription result = new Subscription ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"type",
"customerid",
"title",
"nextbilling"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._type = SNDK.Convert.StringToEnum<Enums.SubscriptionType> (query.GetString (qb.ColumnPos ("type")));
result._customerid = query.GetGuid (qb.ColumnPos ("customerid"));
result._title = query.GetString (qb.ColumnPos ("title"));
result._nextbilling = query.GetInt (qb.ColumnPos ("nextbilling"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
result._items = SubscriptionItem.List (result);
if (!success)
{
throw new Exception (string.Format (Strings.Exception.SubscriptionLoad, Id));
}
return result;
}
public static RangePrice Load(Guid Id)
{
bool success = false;
RangePrice result = new RangePrice ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"type",
"validfrom",
"validto",
"callcharge",
"price",
"hourbegin",
"hourend",
"weekdays"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
// result._validfrom = query.GetDateTime (qb.ColumnPos ("validfrom"));
// result._validto = query.GetDateTime (qb.ColumnPos ("validto"));
result._type = query.GetEnum<Enums.RangePriceType> (qb.ColumnPos ("type"));
result._validfrom = SNDK.Date.TimestampToDateTime (query.GetInt (qb.ColumnPos ("validfrom")));
result._validto = SNDK.Date.TimestampToDateTime (query.GetInt (qb.ColumnPos ("validto")));
result._callcharge = query.GetDecimal (qb.ColumnPos ("callcharge"));
result._price = query.GetDecimal (qb.ColumnPos ("price"));
result._hourbegin = query.GetString (qb.ColumnPos ("hourbegin"));
result._hourend = query.GetString (qb.ColumnPos ("hourend"));
result._weekdays = query.GetEnum<Enums.Weekday> (qb.ColumnPos ("weekdays"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.RangePriceGroupLoad, Id));
}
return result;
}
internal static List<Order> List(string DebitorId)
{
List<Order> result = new List<Order> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordkart");
qb.Columns ("nummer");
if (DebitorId != string.Empty)
{
qb.AddWhere ("konto like '%"+ DebitorId +"'");
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetString (qb.ColumnPos ("nummer")).Replace (" ", "")));
}
catch
{
// This will catch load exceptions while adding orders to the list. That way corrupt orders will be ommited.
}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
internal static List<OrderLine> List(string OrderId)
{
List<OrderLine> result = new List<OrderLine> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("ordlinie");
qb.Columns ("lxbenummer");
if (OrderId != string.Empty)
{
qb.AddWhere ("nummer like '%"+ OrderId +"'");
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetInt (qb.ColumnPos ("lxbenummer")).ToString ()));
}
catch
{
}
}
}
query.Dispose ();
query = null;
qb = null;
result.Sort (delegate (OrderLine ol1, OrderLine ol2) { return ol1._sort.CompareTo (ol2._sort); });
return result;
}
public static VatCode Load(string Id)
{
VatCode result = new VatCode ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table ("moms");
qb.Columns
(
"tekst"
);
qb.AddWhere ("moms = '"+ Id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = Id;
result._text = query.GetString (qb.ColumnPos ("tekst"));
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
public static List<Range> List(CountryCode countrycode)
{
List<Range> result = new List<Range> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
if (countrycode != null)
{
qb.AddWhere ("countrycodeid", "=", countrycode.Id);
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
public static void Test()
{
List<Usage> result = new List<Usage> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
int duration = 0;
foreach (Usage usage in result)
{
if (usage._anumber == "30336439")
{
duration += usage._duration;
Console.WriteLine (usage._bnumber);
}
// Console.WriteLine (usage._anumber);
}
Console.WriteLine (duration);
// return result;
}
public static List<Media> List()
{
List<Media> result = new List<Media>();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table(SorentoLib.Media.DatabaseTableName);
qb.Columns("id");
SNDK.DBI.Query query = SorentoLib.Services.Database.Connection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow())
{
try
{
result.Add(SorentoLib.Media.Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{
SorentoLib.Services.Logging.LogDebug (string.Format (SorentoLib.Strings.LogError.MediaList, qb.ColumnPos ("id")));
}
}
}
query.Dispose();
query = null;
qb = null;
return result;
}
public static CountryCode Load(Guid Id)
{
bool success = false;
CountryCode result = new CountryCode ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"name",
"alternativnames",
"dialcodes"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._name = query.GetString (qb.ColumnPos ("name"));
result._alternativnamesasstring = query.GetString (qb.ColumnPos ("alternativnames"));
result._dialcodesasstring = query.GetString (qb.ColumnPos ("dialcodes"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.CountryCodeLoad, Id));
}
return result;
}
internal void _load(Guid Id)
{
bool success = false;
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"type",
"locationid",
"name",
"notes"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
this._id = query.GetGuid (qb.ColumnPos ("id"));
this._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
this._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
this._type = query.GetEnum<Enums.AssetType> (qb.ColumnPos ("type"));
this._locationid = query.GetGuid (qb.ColumnPos ("locationid"));
this._name = query.GetString (qb.ColumnPos ("name"));
this._notes = query.GetString (qb.ColumnPos ("notes"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.AssetLoad, Id));
}
}
public static SorentoLib.Media Load(Guid Id)
{
bool success = false;
SorentoLib.Media result = new SorentoLib.Media ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id",
"createtimestamp",
"updatetimestamp",
"path",
"mimetype",
"size",
"status",
"accesslevel",
"usergroupids",
"description",
"copyright",
"variantids");
qb.AddWhere ("id", "=", Id);
Query query = SorentoLib.Services.Database.Connection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._currentpath = query.GetString (qb.ColumnPos ("path"));
result._mimetype = query.GetString (qb.ColumnPos ("mimetype"));
result._size = query.GetLong (qb.ColumnPos ("size"));
result._currentstatus = query.GetEnum<SorentoLib.Enums.MediaStatus> (qb.ColumnPos ("status"));
result._accesslevel = query.GetEnum<SorentoLib.Enums.Accesslevel> (qb.ColumnPos ("accesslevel"));
result._usergroupids = query.GetString (qb.ColumnPos ("usergroupids"));
result._description = query.GetString (qb.ColumnPos ("description"));
result._copyright = query.GetString (qb.ColumnPos ("copyright"));
result._variantids = query.GetString (qb.ColumnPos ("variantids"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.MediaLoad, Id));
}
return result;
}
private static List<Usage> List(string Number, int From, int To)
{
List<Usage> result = new List<Usage> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("id");
if ((From > 0) && (To > 0))
{
qb.AddWhere ("'"+ From +"' <= createtimestamp");
qb.AddWhereAND ();
qb.AddWhere ("createtimestamp <= '"+ To +"'");
if (Number != string.Empty)
{
qb.AddWhereAND ();
}
}
if (Number != string.Empty)
{
// A:004588334660|B:004550460609|45|2|1307363676|1:
qb.AddWhere ("type", "=", "1");
qb.AddWhereAND ();
qb.AddWhere ("data", "like", "%|A:"+ Number +"|%");
}
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
try
{
result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
}
catch
{}
}
}
query.Dispose ();
query = null;
qb = null;
return result;
}
internal static void ServiceGarbageCollector()
{
// SorentoLib.Services.Logging.LogDebug (Strings.LogError.MediaGarbageCollector);
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table(SorentoLib.Media.DatabaseTableName);
qb.Columns ("id", "updatetimestamp");
qb.AddWhere ("status", "=", (int)SorentoLib.Enums.MediaStatus.Temporary);
qb.AddWhereOR ();
qb.AddWhere ("status", "=", (int)SorentoLib.Enums.MediaStatus.PublicTemporary);
Query query = SorentoLib.Services.Database.Connection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
if ((SNDK.Date.CurrentDateTimeToTimestamp () - query.GetInt (qb.ColumnPos ("updatetimestamp"))) > SorentoLib.Services.Config.Get<int> (Enums.ConfigKey.media_tempmaxage))
{
Delete (query.GetGuid (qb.ColumnPos ("id")));
}
}
}
query.Dispose ();
query = null;
qb = null;
}
public static RangeGroup Load(Guid Id)
{
bool success = false;
RangeGroup result = new RangeGroup ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"name",
"rangeids",
"countrycodeids",
"costpriceids",
"retailpriceids"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._name = query.GetString (qb.ColumnPos ("name"));
result._rangeids = SNDK.Convert.StringToList<Guid> (query.GetString (qb.ColumnPos ("rangeids")));
result._countrycodeids = SNDK.Convert.StringToList<Guid> (query.GetString (qb.ColumnPos ("countrycodeids")));
result._costpriceids = SNDK.Convert.StringToList<Guid> (query.GetString (qb.ColumnPos ("costpriceids")));
result._retailpriceids = SNDK.Convert.StringToList<Guid> (query.GetString (qb.ColumnPos ("retailpriceids")));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.RangeGroupLoad, Id));
}
return result;
}
private static string FixPath(string Path)
{
string result = Path;
string path = System.IO.Path.GetDirectoryName (Path) +"/";
string filename = System.IO.Path.GetFileNameWithoutExtension (Path);
string extension = System.IO.Path.GetExtension (Path);
List<string> files = new List<string> ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns ("path");
qb.AddWhere ("path", "like", "%"+ path +"%");
Query query = SorentoLib.Services.Database.Connection.Query (qb.QueryString);
if (query.Success)
{
while (query.NextRow ())
{
files.Add (query.GetString (qb.ColumnPos ("path")));
}
}
int increment = 1;
while (files.Contains (result))
{
result = path + filename +"("+ increment +")" + extension;
increment++;
}
return result;
}
public static SubscriptionItem Load(Guid Id)
{
bool success = false;
SubscriptionItem result = new SubscriptionItem ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"createtimestamp",
"updatetimestamp",
"subscriptionid",
"erpid",
"recurrencetype",
"recurrencecount",
"text",
"unit",
"price",
"notes"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._createtimestamp = query.GetInt (qb.ColumnPos ("createtimestamp"));
result._updatetimestamp = query.GetInt (qb.ColumnPos ("updatetimestamp"));
result._subscriptionid = query.GetGuid (qb.ColumnPos ("subscriptionid"));
result._erpid = query.GetString (qb.ColumnPos ("erpid"));
result._recurrencetype = SNDK.Convert.IntToEnum<Enums.ItemRecurrenceType> (query.GetInt (qb.ColumnPos ("recurrencetype")));
result._recurrencecount = query.GetInt (qb.ColumnPos ("recurrencecount"));
result._text = query.GetString (qb.ColumnPos ("text"));
result._unit = query.GetString (qb.ColumnPos ("unit"));
result._price = query.GetDecimal (qb.ColumnPos ("price"));
result._notes = query.GetString (qb.ColumnPos ("notes"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.SubscriptionItemLoad, Id));
}
return result;
}
public static OS Load(Guid Id)
{
bool success = false;
OS result = new OS ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
qb.Table (DatabaseTableName);
qb.Columns
(
"id",
"name"
);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.Success)
{
if (query.NextRow ())
{
result._id = query.GetGuid (qb.ColumnPos ("id"));
result._name = query.GetString (qb.ColumnPos ("name"));
success = true;
}
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.CountryCodeLoad, Id));
}
return result;
}