public static void Set(string Url, string XmlResponse)
{
QueryBuilder qb;
qb = new QueryBuilder (QueryBuilderType.Insert);
qb.Table (DatabaseTableName);
qb.Columns ("id",
"createtimestamp",
"url",
"xmlresponse");
qb.Values (Guid.NewGuid (),
SNDK.Date.CurrentDateTimeToTimestamp (),
Url,
XmlResponse);
Query query = DbConnection.Query (qb.QueryString);
query.Dispose();
query = null;
qb = null;
Flush ();
}
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 bool GuidExists(SNDK.DBI.Connection DbConnection, string TableName, Guid Guid)
{
bool result = false;
QueryBuilder qb = new QueryBuilder(QueryBuilderType.Select);
qb.Table(TableName);
qb.Columns("id");
qb.AddWhere("id", "=", Guid);
Query query = DbConnection.Query(qb.QueryString);
if (query.Success)
{
if (query.NextRow())
{
result = true;
}
}
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;
}
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 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 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 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 void Save()
{
// If OrderLine is new, we need to fill in alot of initial data.
// TEMP1
if (this._temp1)
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Insert);
qb.Table ("ordlinie");
qb.Columns
(
"dataset",
"lxbenummer",
"sidstrettet",
"nummer",
"linienr",
"varenummer",
"lokation",
"antal",
"pris",
"rabat",
"belxb",
"tekst",
"enhed",
"moms",
"levernu",
"oprettet",
"levering",
"bekrxftet",
"konto",
"serienummer",
"leveret",
"faktureret",
"leveretdkk",
"transaktion",
"kostpris",
"sletstatistik",
"sletafgift",
"liniestatus",
"lagerstatus",
"medarbejder",
"samlerefid",
"ordreref",
"handelskode",
"antalfysisk",
"fjernlistekode",
"prisenhed"
);
qb.Values
(
"DAT", // datasaet
int.Parse (this._id), // lxbenummer
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)), // sidstrettet
this._orderid.PadLeft (10, ' '), // nummer
this._sort, // linienr
this._productid, // varenummer
string.Empty, // lokation
this._amount, // antal
this._price, // pris
0, // rabat
this._total, // belxb
this._text, // tekst
this._unit, // enhed
"U25", // moms
1, // levernu
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._createtimestamp)), // oprettet
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._createtimestamp)), // levering
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._createtimestamp)), // bekrxftet
string.Empty, // konto
string.Empty, // serienummer
0, // leveret
0, // faktureret
0, // leveretdkk
0, // transaktion
0, // kostpris
0, // sletstatistik
string.Empty, // sletafgift
2, // liniestatus
2, // lagerstatus
string.Empty, // medarbejder
0, // samlerefid
string.Empty, // ordreref
string.Empty, // handelskode
0, // antalfysisk
0, // fjernlistekode
1 // prisenhed
);
Query query = Runtime.DBConnection.Query (qb.QueryString);
Console.WriteLine (qb.QueryString);
if (query.AffectedRows == 0)
{
// Exception: OrderLineSave
throw new Exception (string.Format (Strings.Exception.OrderLineSave, this._id));
}
query.Dispose ();
query = null;
qb = null;
// TEMP1
this._temp1 = false;
}
// If OrderLine is not new, just update the fields needed.
else
{
this._updatetimestamp = SNDK.Date.CurrentDateTimeToTimestamp ();
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Update);
qb.Table ("ordlinie");
qb.Columns
(
"sidstrettet",
"varenummer",
"linienr",
"antal",
"pris",
"belxb",
"tekst",
"enhed"
);
qb.Values
(
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)), // sidstrettet
this._productid, // varenummer
this._sort, // linienr
this._amount, // antal
this._price, // pris
this._total, // belxb
this._text, // tekst
this._unit // enhed
);
qb.AddWhere ("lxbenummer like '%"+ this._id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.AffectedRows == 0)
{
// Exception: OrderLineSave
throw new Exception (string.Format (Strings.Exception.OrderLineSave, this._id));
}
query.Dispose ();
query = null;
qb = null;
}
// Save notes.
if (this._notes != string.Empty)
{
// Remove old notes.
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table ("notat");
qb.AddWhere ("notatrecid = '"+ this._id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
query.Dispose ();
query = null;
qb = null;
}
// Write new notes.
{
int lineno = 0;
foreach (string note in this._notes.Split ("\n".ToCharArray (), StringSplitOptions.RemoveEmptyEntries))
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Insert);
qb.Table ("notat");
qb.Columns
(
"dataset",
"lxbenummer",
"sidstrettet",
"notatfileid",
"notatrecid",
"linienummer",
"tekst",
"dato"
);
qb.Values
(
"DAT", // datasaet
Helpers.NewSequenceNumber (), // lxbenummer
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)), // sidstrettet
"128", // notatfileid
this._id, // notatrecid
lineno*2, // linienummer
note, // tekst
String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)) // dato
);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.AffectedRows == 0)
{
// Exception: OrderLineSave
// throw new Exception (string.Format (Strings.Exception.OrderLineSave, this._id));
}
query.Dispose ();
query = null;
qb = null;
}
}
}
// if (trim($line['note']) != ""){
// $split = explode("\n", $line['note']);
// $o = 1;
// foreach($split as $part){
// mssql_query("INSERT INTO notat VALUES ('DAT', '".$this->get_lxbenummer()."', '".strftime("%Y-%m-%d")." 00:00:00.000', 128, ".$LXBE.", ".($o*2).", '".$part."', '".strftime("%Y-%m-%d")." 00:00:00.000')");
// $o++;
// }
// }
}
public static void Delete(Guid Id)
{
try
{
Media media = Media.Load (Id);
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table (DatabaseTableName);
qb.AddWhere ("id", "=", Id);
Query query = Services.Database.Connection.Query (qb.QueryString);
if (query.AffectedRows > 0)
{
File.Delete (media.DataPath);
media = null;
}
query.Dispose ();
query = null;
qb = null;
}
catch
{
throw new Exception (string.Format (Strings.Exception.MediaDelete, Id));
}
}
// 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 ();
}
}
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;
}
public static void Delete(Guid id)
{
bool success = false;
foreach (Location location in Location.List ())
{
if (location._parentid == id)
{
Location.Delete (location._id);
}
}
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table (DatabaseTableName);
qb.AddWhere ("id", "=", id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.AffectedRows > 0)
{
success = true;
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.ManagementLocationDeleteGuid, id));
}
}
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 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<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;
}
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 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 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;
}
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 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;
}
internal static void ServicesSnapshotPurge()
{
foreach (Media media in Media.List ())
{
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table (DatabaseTableName);
qb.AddWhere ("id", "=", media.Id);
Query query = Services.Database.Connection.Query (qb.QueryString);
query.Dispose ();
query = null;
qb = null;
}
}
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;
}
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 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 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;
}
public static void Delete(Guid Id)
{
bool success = false;
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table (DatabaseTableName);
qb.AddWhere ("id", "=", Id);
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.AffectedRows > 0)
{
success = true;
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
throw new Exception (string.Format (Strings.Exception.SubscriptionItemDelete, Id));
}
}
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 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 void Delete(string Id)
{
bool success = false;
QueryBuilder qb = new QueryBuilder (QueryBuilderType.Delete);
qb.Table ("ordlinie");
qb.AddWhere ("lxbenummer like '"+ Id +"'");
Query query = Runtime.DBConnection.Query (qb.QueryString);
if (query.AffectedRows > 0)
{
success = true;
}
query.Dispose ();
query = null;
qb = null;
if (!success)
{
// Exception: OrderLineDelete
throw new Exception (string.Format (Strings.Exception.OrderLineDeleteId, Id));
}
}