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 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 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 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 void Save() { // TODO: This should be fixed. int gebyrfm = 0; if (this._debitor.Url == string.Empty || this._debitor.Url == "") { gebyrfm = 30; } // If Order has not yet been saved, we need to insert a new record. // TEMP2 if (this._temp2) { int sequencenumber = C5.Helpers.NewSequenceNumber (); QueryBuilder qb = new QueryBuilder (QueryBuilderType.Insert); qb.Table ("ordkart"); qb.Columns ( "dataset", "lxbenummer", "sidstrettet", "lxs", "nummer", "sxgenavn", "oprettet", "leveres", "konto", "navn", "adresse1", "adresse2", "postby", "land", "attention", "telefon", "telefax", "fakturakonto", "gruppe", "fastrabat", "prisgruppe", "rabatgruppe", "kasserabat", "valuta", "sprog", "betaling", "levering", "spxrret", "sxlger", "moms", "beholdning", "afdeling", "gironummer", "momsnummer", "billede", "levering1", "levering2", "levering3", "levering4", "levland", "deresref", "vorref", "ordre", "momsberegnes", "momsberegnet", "rabat", "afgiftfm", "gebyrfm", "afrunding", "momsbelxb", "afgiftem", "gebyrem", "fakturatotal", "liniemoms", "transaktion", "sletstatistik", "slettransport", "godkendt", "lagerstatus", "fakturafxlgeseddel", "fakturafxlgeseddeldato", "kontant", "listekode", "linierabat", "varebelxb", "momsgrundlag", "handelskode", "transkode", "enummer", "email", "levemail", "betalingsid" ); qb.Values ( "DAT", // dataset sequencenumber, // lxbenummer String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)), // sidstrettet 0, // lxs this._id.PadLeft (10, ' '), // nummer "Webordre #"+ this._id, // sxgenavn 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)), // leveres this._debitor.Id.PadLeft (10, ' '), // konto this._debitor.Name, // navn this._debitor.Address1, // address1 this._debitor.Address2, // address2 this._debitor.PostCode +" "+ this._debitor.City, // postby this._debitor.Country, // land this._debitor.Attention, // attention this._debitor.Phone, // telefon this._debitor.Fax, // telefax this._debitor.Id.PadLeft (10, ' '), // fakturakonto string.Empty, // gruppe 0, // fastrabat "Salg", // prisgruppe string.Empty, // rabatgruppe string.Empty, // kasserabat "DKK", // valuta 0, // sprog this._debitor.CreditPolicy.Id, // betaling string.Empty, // levering 0, // spxrret string.Empty, //sxlger this._debitor.VatCode.Id, // moms 0, // beholdning string.Empty, // afdeling string.Empty, // gironummer this._debitor.VatNo, // momsnummer string.Empty, // billede string.Empty, // levering1 string.Empty, // levering2 string.Empty, // levering3 string.Empty, // levering4 string.Empty, // levland string.Empty, // deresref string.Empty, // vorref string.Empty, // ordre 0, // momsberegnes 0, // momsberegnet 0, // rabat 0, // afgiftfm gebyrfm, // gebyrfm 0, // afrunding 0, // momsbelxb 0, // afgiftem 0, // gebyrem 0, // fakturatotal 0, // liniemoms 0, // transaktion 0, // sletstatestik 0, // slettransport 1, // godkendt 2, // lagerstatus string.Empty, // fakturafxlgeseddel String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._createtimestamp)), // fakturafxlgeseddeldato 0, // kontant 0, // listekode 0, // linierabat 0, // varebelxb 0, // momsgrundlag string.Empty, // handelskode string.Empty, // transkode string.Empty, // enummer this._debitor.Email, // email string.Empty, // levmail string.Empty // betalingstid ); Console.WriteLine (qb.QueryString); Query query = Runtime.DBConnection.Query (qb.QueryString); if (query.AffectedRows == 0) { // Exception: OrderSave throw new Exception (string.Format (Strings.Exception.OrderSave, this._id)); } query.Dispose (); query = null; qb = null; // TEMP2 this._temp2 = false; } // If OrderLine has allready been saved before we only need to update the record. else { QueryBuilder qb = new QueryBuilder (QueryBuilderType.Update); qb.Table ("ordkart"); qb.Columns ( "sidstrettet", "konto", "navn", "adresse1", "adresse2", "postby", "land", "attention", "telefon", "telefax", "email", "fakturakonto", "betaling", "momsnummer", "moms", "gebyrfm" ); qb.Values ( String.Format ("{0:yyyy-MM-dd} 00:00:00.000", SNDK.Date.TimestampToDateTime (this._updatetimestamp)), // sidstrettet this._debitor.Id.PadLeft (10, ' '), // konto this._debitor.Name, // navn this._debitor.Address1, // address1 this._debitor.Address2, // address2 this._debitor.PostCode +" "+ this._debitor.City, // postby this._debitor.Country, // land this._debitor.Attention, // attention this._debitor.Phone, // telefon this._debitor.Fax, // telefax this._debitor.Email, // email this._debitor.Id.PadLeft (10, ' '), // fakturakonto this._debitor.CreditPolicy, // betaling this._debitor.VatNo, // momsnummer this._debitor.VatCode, // moms gebyrfm // gebyrfm ); qb.AddWhere ("nummer like '%"+ this._id +"'"); Query query = Runtime.DBConnection.Query (qb.QueryString); if (query.AffectedRows == 0) { // Exception: OrderSave throw new Exception (string.Format (Strings.Exception.OrderSave, this._id)); } query.Dispose (); query = null; qb = null; } // Save orderlines added. foreach (OrderLine line in this._orderlines) { line.Save (); } // Delete orderlines removed. // TEMP1 foreach (string orderlineid in this._temp1.Split (";".ToCharArray (), StringSplitOptions.RemoveEmptyEntries)) { try { OrderLine.Delete (orderlineid); } catch { // This will catch deletion of orderlines not yet saved. } } }
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; }
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; }
/// <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; }
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; }
// 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 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 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 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; }
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; }