/// <summary>
/// Function called to search the model for availability of the specified string.
/// </summary>
/// <param name="str">The search string</param>
/// <returns>True, if the string is contained in the model, else false</returns>
public override bool Contains(string str)
{
return((ContractNumber != null && ContractNumber.StartsWith(str, StringComparison.CurrentCultureIgnoreCase)) ||
(InvoiceCustomer != null && InvoiceCustomer.StartsWith(str, StringComparison.CurrentCultureIgnoreCase)) ||
(CompanyName != null && CompanyName.StartsWith(str, StringComparison.CurrentCultureIgnoreCase)) ||
(DivisionName != null && DivisionName.StartsWith(str, StringComparison.CurrentCultureIgnoreCase)) ||
(ActivityName != null && ActivityName.StartsWith(str, StringComparison.CurrentCultureIgnoreCase)) ||
(!string.IsNullOrEmpty(Convert.ToString(InvoiceDate)) && InvoiceDate.ToString(Constants.DATE_FORMAT).Contains(str)) ||
(!string.IsNullOrEmpty(Convert.ToString(Amount)) && Convert.ToString(Amount).StartsWith(str, StringComparison.CurrentCultureIgnoreCase)));
}
public override string ToString()
{
string output = "\r\n";
foreach (var i in InvoiceItems)
{
output += $" { i.ItemName } ({ i.Description }) - R{ i.Total.ToString("#,#", CultureInfo.InvariantCulture) }\r\n";
}
return($"Invoice Number: { InvoiceNumber }\r\n" +
$"Invoice Date: { InvoiceDate.ToString("d") }\r\n" +
$"Due Date: { DueDate.ToString("d") }\r\n" +
$"Invoice Items: { output }\r\n" +
$"Invoice Total: R{ InvoiceTotal.ToString("#,#", CultureInfo.InvariantCulture) }\r\n");
}
public StartRecord()
{
// Set default values.
StandardVersion = 4;
StandardSubversion = 2;
MessageCode = 117;
// Make this a test message by default.
Type = MessageType.Test;
InvoiceId = string.Empty;
CurrencyCode = "EUR";
// Define the fields for this record.
MapField(2, 3, "Code").Numeric().Getter(x => MessageCode.ToString("000"));
MapField(5, 2, "Versienummer berichtstandaard").Numeric().Getter(x => StandardVersion.ToString());
MapField(7, 2, "Subversienummer berichtstandaard").Numeric().Getter(x => StandardSubversion.ToString());
MapField(9, 1, "Soortbericht").Numeric().Getter(x =>
{
switch (Type)
{
case MessageType.Production:
return("P");
case MessageType.Test:
return("T");
default:
throw new InvalidOperationException("Message type is not specified.");
}
});
MapField(10, 6, "Code informatiesysteem softwareleverancier").Numeric();
MapField(26, 4, "UZOVI-nummer").Numeric().Getter(x => UzoviId.ToString());
MapField(30, 8, "Code servicebureau").Numeric();
MapField(38, 8, "Zorgverlenerscode").Numeric().Getter(x => CareProviderAgbId.ToString());
MapField(46, 8, "Praktijkcode").Numeric();
MapField(54, 8, "Instellingcode").Numeric();
MapField(62, 2, "Identificatiecode betaling aan").Numeric().Getter(x => PaymentRecipientCode.ToString("D"));
MapField(64, 8, "Begindatum declaratieperiode").Numeric().Getter(x => StartDate.ToString("yyyyMMdd"));
MapField(72, 8, "Einddatum declaratieperiode").Numeric().Getter(x => EndDate.ToString("yyyyMMdd"));
MapField(80, 12, "Factuurnummer declarant").Alphanumeric().Getter(x => InvoiceId);
MapField(92, 8, "Dagtekening factuur").Numeric().Getter(x => InvoiceDate.ToString("yyyyMMdd"));
MapField(114, 3, "Valutacode").Alphanumeric().Getter(x => CurrencyCode);
}
//TOSTRING (with delimitter)
public string ToString(string delim)
{
return(RecordType + delim +
VendorCode + delim +
LocationCode + delim +
PurchaseOrderNo + delim +
((Object)ExpectedDeliveryDate ?? " ") + delim +
VendorInvoiceNo + delim +
InvoiceDate.ToString("MM'/'dd'/'yyyy") + delim + //11/16/2016 (forces the "/" separator)
InvoiceTotal.ToString("0.00") + delim + //format (two decimal places)
(TaxGlDescription ?? " ") + delim +
((Object)TaxValue ?? " ") + delim +
(FreightShippingGlDescription ?? " ") + delim +
((Object)FreightShippingValue ?? " ") + delim +
Misc1GlDescription + delim +
Misc1Value.ToString("0.00"));
}
//TOSTRING OVERRIDE
public override string ToString()
{
return(RecordType + "\t" +
VendorCode + "\t" +
LocationCode + "\t" +
PurchaseOrderNo + "\t" +
((Object)ExpectedDeliveryDate ?? " ") + "\t" +
VendorInvoiceNo + "\t" +
InvoiceDate.ToString("MM'/'dd'/'yyyy") + "\t" + //11/16/2016 (forces the "/" separator)
InvoiceTotal.ToString("0.00") + "\t" + //format (two decimal places)
(TaxGlDescription ?? " ") + "\t" +
//(handle NULL value and converting (double? to double) in the correct format)
MyDoubleToString(TaxValue) + "\t" +
//((Object)TaxValue ?? " ") + "\t" +
(FreightShippingGlDescription ?? " ") + "\t" +
//(handle NULL value and converting (double? to double) in the correct format)
MyDoubleToString(FreightShippingValue) + "\t" +
//((Object)FreightShippingValue ?? " ") + "\t" +
Misc1GlDescription + "\t" +
Misc1Value.ToString("0.00"));
}
public override string ToString()
{
return($"Invoice ID: {Id} Customer Name:{CustomerFirstName} {CustomerLastName} Date:{InvoiceDate.ToString("dd/MM/yyyy")}");
}
/// <summary>
/// Outputs string containing the following (replace [] with actual values):
/// Invoice Number: [InvoiceNumber], InvoiceDate: [DD/MM/YYYY], LineItemCount: [Number of items in LineItems]
/// </summary>
public override string ToString()
{
return($"Invoice Number: {InvoiceNumber}, InvoiceDate: {(InvoiceDate == DateTime.MinValue ? string.Empty : InvoiceDate.ToString("dd/MM/yyyy")) }, LineItemCount: {LineItems.Count()}");
}
public string Log()
{
return(InvoiceDate.ToString());
}
// generatory zapytań MySQL
public string GenerateUpdateQuery()
{
return(String.Format("UPDATE faktura SET id_kontrahenta={0}, data_wystawienia='{1}', termin_platnosci='{2}', data_sprzedazy='{3}', forma_platnosci='{4}', zaplacona_kwota='{5}' WHERE numer='{6}'", CustomerID, InvoiceDate.ToString(), PaymentDate.ToString(), SellingDate.ToString(), Array.IndexOf(paymentMethods, PaymentMethod) + 1, AmountPaid, Number));
}
/// <summary>
/// Outputs string containing the following (replace [] with actual values):
/// Invoice Number: [InvoiceNumber], InvoiceDate: [DD/MM/YYYY], LineItemCount: [Number of items in LineItems]
/// </summary>
///
public override string ToString()
{
return($"Invoice Number: {InvoiceNumber}, InvoiceDate: {InvoiceDate.ToString("dd/MM/yyy")}, LineItemCount: {LineItems.Count}");
}
// public bool FetchFromDB(string dsn = "DSN=BAAN", bool debug = false)
public bool FetchFromDB(OdbcConnection DbConnection, bool debug = true)
{
if (debug)
{
Console.WriteLine("0");
}
// using (OdbcConnection DbConnection = new OdbcConnection(dsn))
{
if (debug)
{
Console.WriteLine("1");
}
//try
//{
// if (DbConnection.State != System.Data.ConnectionState.Open)
// {
// DbConnection.Close();
// DbConnection.Open();
// }
//}
//catch (Exception ex)
//{
// string msg = "Could not connect to BAANDB: " + ex.Message;
// if (debug)
// {
// Console.WriteLine(msg);
// }
// else
// {
// throw new System.Exception(msg);
// }
//}
//if (debug) Console.WriteLine("2");
//Query #1
using (OdbcCommand DbCommand = DbConnection.CreateCommand())
{
string cmd = "select t_ttyp, t_docd, t_rate, t_ccur, t_amth, t_amnt, t_vath from baandb.ttfacr200{0} where t_ninv={1} and t_tdoc=\"\"";
DbCommand.CommandText = String.Format(cmd, CompanyCode, InvoiceNumber);
OdbcDataReader reader;
if (debug)
{
Console.WriteLine("3");
}
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
string msg = "Query #1: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (debug)
{
Console.WriteLine("4");
}
if (reader.Read())
{
InvoiceType = reader.GetString(0);
InvoiceDate = reader.GetDateTime(1);
ExchangeRate = reader.GetDecimal(2);
Currentcy = reader.GetString(3);
CurrencyDocumentSum = reader.GetDecimal(4);
DocumentSum = reader.GetDecimal(5);
TaxSum = reader.GetDecimal(6);
LineSum = CurrencyDocumentSum - TaxSum;
if (debug)
{
Console.WriteLine("Invoice #: " + InvoiceNumber);
Console.WriteLine("InvoiceType: " + InvoiceType);
Console.WriteLine("InvoiceDate: " + InvoiceDate.ToShortDateString());
Console.WriteLine("ExchangeRate: " + ExchangeRate);
Console.WriteLine("Currentcy: " + Currentcy);
Console.WriteLine("CurrencyDocumentSum: " + CurrencyDocumentSum);
Console.WriteLine("DocumentSum: " + DocumentSum);
Console.WriteLine("LineSum: " + LineSum);
Console.WriteLine("TaxSum: " + TaxSum);
}
}
else
{
if (debug)
{
Console.WriteLine("5");
}
return(false);
}
reader.Close();
cmd = "select first 1 t_pvat from baandb.ttcmcs032{0} where t_edat<=\"{1}\" and t_cvat=\" 001\" order by t_edat desc";
if (debug)
{
Console.WriteLine("6");
}
string dt = InvoiceDate.ToString("d", CultureInfo.CreateSpecificCulture("en-US"));
DbCommand.CommandText = String.Format(cmd, CompanyCode, dt);
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
string msg = "Query #1: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine("7");
}
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (reader.Read())
{
if (debug)
{
Console.WriteLine("8");
}
TaxRate = reader.GetString(0);
if (debug)
{
Console.WriteLine("TaxRate: " + TaxRate);
}
}
else
{
if (debug)
{
Console.WriteLine("9");
}
return(false);
}
reader.Close();
//Query #2
cmd = "select t_nama, t_vatn from baandb.ttccom000{0} where t_ncmp={0}";
if (debug)
{
Console.WriteLine("10");
}
DbCommand.CommandText = String.Format(cmd, CompanyCode);
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
if (debug)
{
Console.WriteLine("11");
}
string msg = "Query #2: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
if (debug)
{
Console.WriteLine("12");
}
throw new System.Exception(msg);
}
return(false);
}
if (reader.Read())
{
if (debug)
{
Console.WriteLine("13");
}
SupplierName = reader.GetString(0);
SupplierPrivateCompanyCode = reader.GetString(1);
if (debug)
{
Console.WriteLine("SupplierName: " + SupplierName);
Console.WriteLine("SupplierPrivateCompanyCode: " + SupplierPrivateCompanyCode);
}
}
else
{
if (debug)
{
Console.WriteLine("14");
}
return(false);
}
reader.Close();
//Query #3
cmd = "select t_fnum from baandb.ttccom810{0} where t_cuno in (select t_cuno from baandb.ttdsls040{0} where t_orno in (select distinct t_orno from baandb.ttdsls045{0} as DSLS045 where DSLS045.t_invn={1}))";
if (debug)
{
Console.WriteLine("15");
}
DbCommand.CommandText = String.Format(cmd, CompanyCode, InvoiceNumber);
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
if (debug)
{
Console.WriteLine("16");
}
string msg = "Query #3: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (reader.Read())
{
if (debug)
{
Console.WriteLine("17");
}
Receiver = reader.GetString(0);
if (debug)
{
Console.WriteLine("Receiver: " + Receiver);
}
}
else
{
if (debug)
{
Console.WriteLine("18");
}
return(false);
}
reader.Close();
//Query #4
cmd = "select t_fovn , t_nama, t_namc, t_name, t_ccty, t_pstc from baandb.ttccom010{0} where t_cuno in (select t_cuno from baandb.ttdsls040{0} where t_orno in (select distinct t_orno from baandb.ttdsls045{0} as DSLS045 where DSLS045.t_invn={1}))";
if (debug)
{
Console.WriteLine("19");
}
DbCommand.CommandText = String.Format(cmd, CompanyCode, InvoiceNumber);
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
if (debug)
{
Console.WriteLine("20");
}
string msg = "Query #4: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (reader.Read())
{
if (debug)
{
Console.WriteLine("21");
}
RetailerPrivateCompanyCode = reader.GetString(0);
CompanyName = reader.GetString(1);
Address = reader.GetString(2);
City = reader.GetString(3);
Country = reader.GetString(4);
Zipcode = reader.GetString(5);
// Receiver = reader.GetString(6);
if (debug)
{
Console.WriteLine("Sender: " + Sender);
Console.WriteLine("Receiver: " + Receiver);
Console.WriteLine("RetailerPrivateCompanyCode: " + RetailerPrivateCompanyCode);
Console.WriteLine("CompanyName: " + CompanyName);
Console.WriteLine("Address: " + Address);
Console.WriteLine("City: " + City);
Console.WriteLine("Country: " + Country);
Console.WriteLine("Zipcode: " + Zipcode);
}
}
else
{
if (debug)
{
Console.WriteLine("22");
}
return(false);
}
reader.Close();
//Query #5
//select t_dsca from ttcmcs013400 where pay = ???
//cmd = "select DSLS041.t_epos, DSLS045.t_dqua, DSLS045.t_pric, DSLS045.t_amnt, DSLS045.t_item, IITM001.t_dsca, TTCMCS041.t_dsca, DSLS040.t_refa, DSLS040.t_orno, DSLS040.t_odat, TCMCS013.t_dsca, DSLS040.t_orno, DSLS045.t_damt_1 from baandb.ttdsls045{0} as DSLS045, baandb.ttiitm001{0} as IITM001, baandb.ttdsls040{0} as DSLS040, baandb.ttdsls041{0} as DSLS041, baandb.ttcmcs041{0} as TTCMCS041, baandb.ttcmcs013{0} as TCMCS013 where DSLS045.t_invn={1} and IITM001.t_item=DSLS045.t_item and DSLS041.t_item=DSLS045.t_item and DSLS040.t_orno=DSLS045.t_orno and (DSLS041.t_pono=DSLS045.t_pono and DSLS041.t_orno=DSLS045.t_orno) and TTCMCS041.t_cdec=DSLS040.t_cdec and TCMCS013.t_cpay=DSLS040.t_cpay";
cmd = "select DSLS041.t_epos, DSLS045.t_dqua, DSLS045.t_pric, DSLS045.t_amnt, DSLS045.t_item, IITM001.t_dsca, TTCMCS041.t_dsca, DSLS040.t_cotp, DSLS040.t_refa, DSLS040.t_odat, TCMCS013.t_dsca, DSLS040.t_orno, DSLS045.t_damt_1 from baandb.ttdsls045{0} as DSLS045, baandb.ttiitm001{0} as IITM001, baandb.ttdsls040{0} as DSLS040, baandb.ttdsls041{0} as DSLS041, baandb.ttcmcs041{0} as TTCMCS041, baandb.ttcmcs013{0} as TCMCS013 where DSLS045.t_invn={1} and IITM001.t_item=DSLS045.t_item and DSLS041.t_item=DSLS045.t_item and DSLS040.t_orno=DSLS045.t_orno and (DSLS041.t_pono=DSLS045.t_pono and DSLS041.t_orno=DSLS045.t_orno) and TTCMCS041.t_cdec=DSLS040.t_cdec and TCMCS013.t_cpay=DSLS040.t_cpay";
DbCommand.CommandText = String.Format(cmd, CompanyCode, InvoiceNumber);
if (debug)
{
Console.WriteLine("23");
}
try
{
reader = DbCommand.ExecuteReader();
}
catch (Exception ex)
{
if (debug)
{
Console.WriteLine("24");
}
string msg = "Query #5: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
int num_lines = 0;
if (debug)
{
Console.WriteLine("25");
}
OdbcDataReader readerCustBarcode;
OdbcCommand DbCommandCustBarcode;
while (reader.Read())
{
num_lines++;
InvoiceLine line = new InvoiceLine();
line.LineNo = reader.GetString(0);
if (String.IsNullOrEmpty(line.LineNo))
{
line.LineNo = "1";
}
//line.UnitQuantity = reader.GetInt32(1);
//line.UnitQuantity =Math.Round(reader.GetDouble(1),2);
string qty = reader.GetDouble(1).ToString();
if (qty.IndexOf('.') != -1)
{
line.UnitQuantity = qty.Substring(0, qty.IndexOf('.') + 3);
}
else
{
line.UnitQuantity = qty;
}
line.ItemPriceBruto = reader.GetDecimal(2);
line.LineSum = reader.GetDecimal(3);
line.ItemBarcode = reader.GetString(4);
line.ItemDescription = reader.GetString(5);
line.Delivery = reader.GetString(6);
line.ReferenceType = reader.GetString(7);
line.ReferenceNumber = reader.GetString(8);
line.ReferenceDate = reader.GetDate(9);
line.PaymentTerms = reader.GetString(10);
line.SalesOrder = reader.GetString(11);
line.Discount = reader.GetString(12);
line.PartNumber = "";
#region getCustomerBarcode
//Query #5.5 added by Liat for CustomerBarcode
cmd = "select TTII.t_mitm from baandb.ttiitm950{0} as TTII where TTII.t_mnum=\"999\" and TTII.t_item=\"{1}\" and TTII.t_cuno in (select t_cuno from baandb.ttccom810{0} where t_fnum={2})";
if (debug)
{
Console.WriteLine("26");
}
DbCommandCustBarcode = DbConnection.CreateCommand();
string itemBarcode = DbCommandCustBarcode.CommandText = String.Format(cmd, CompanyCode, line.ItemBarcode.PadLeft(16, ' '), Receiver);
try
{
readerCustBarcode = DbCommandCustBarcode.ExecuteReader();
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("error in query 5.5", ex.ToString());
if (debug)
{
Console.WriteLine("27");
}
string msg = "Query #5.5: " + DbCommand.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (readerCustBarcode.Read())
{
if (debug)
{
Console.WriteLine("28");
}
line.CustomerBarcode = readerCustBarcode.GetString(0);
System.Diagnostics.EventLog.WriteEntry("CustomerBarcode", "barcode exists: " + line.CustomerBarcode + "this is the query: " + DbCommand.CommandText);
if (debug)
{
Console.WriteLine("CustomerBarcode: " + line.CustomerBarcode);
}
}
else
{
System.Diagnostics.EventLog.WriteEntry("CustomerBarcode", "Customer Barcode wasnt found this is the query" + cmd);
if (debug)
{
Console.WriteLine("29");
}
// return false;
}
readerCustBarcode.Close();
//Query #6 added by Liat for TaxSumNIS
cmd = "select t_rate_c from baandb.ttfgld018{0} where t_docn={1} and t_ttyp=\"{2}\" ";
if (debug)
{
Console.WriteLine("30");
}
OdbcDataReader readerSumNIS;
OdbcCommand DbCommandSumNIS;
DbCommandSumNIS = DbConnection.CreateCommand();
DbCommandSumNIS.CommandText = String.Format(cmd, CompanyCode, InvoiceNumber, InvoiceType);
try
{
readerSumNIS = DbCommandSumNIS.ExecuteReader();
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("sum", ex.ToString());
System.Diagnostics.EventLog.WriteEntry("query", DbCommandSumNIS.CommandText);
string msg = "Query #6: " + DbCommandSumNIS.CommandText + "\n" + ex.Message;
if (debug)
{
Console.WriteLine("31");
}
if (debug)
{
Console.WriteLine(msg);
}
else
{
throw new System.Exception(msg);
}
return(false);
}
if (readerSumNIS.Read())
{
System.Diagnostics.EventLog.WriteEntry("TaxSumNIS-query", "TaxSumNIS exists: this is the query: " + DbCommandSumNIS.CommandText);
try
{
CurrencyRate = readerSumNIS.GetDecimal(0);
TaxSumNIS = CurrencyRate * TaxSum;
System.Diagnostics.EventLog.WriteEntry("TaxSumNIS", "CurrencyRate: " + CurrencyRate + "TaxSum: " + TaxSum + "TaxSumNIS: " + TaxSumNIS + "this is the query: " + DbCommandSumNIS.CommandText);
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("error in kefel", ex.ToString());
System.Diagnostics.EventLog.WriteEntry("error in kefel values", "CurrencyRate: " + CurrencyRate + "TaxSum: " + TaxSum + "TaxSumNIS: " + TaxSumNIS + "this is the query: " + DbCommandSumNIS.CommandText);
}
}
else
{
System.Diagnostics.EventLog.WriteEntry("TaxSumNIS", "TaxSumNIS doesnt exist this is the query: " + DbCommandSumNIS.CommandText);
if (debug)
{
Console.WriteLine("33");
}
return(false);
}
readerSumNIS.Close();
#endregion
Add(line);
if (debug)
{
Console.WriteLine("..................................................................................");
Console.WriteLine("Line #: " + num_lines);
Console.WriteLine("Customer lineNo: " + line.LineNo);
Console.WriteLine("UnitQuantity: " + line.UnitQuantity);
Console.WriteLine("ItemPriceBruto: " + line.ItemPriceBruto);
Console.WriteLine("LineSum: " + line.LineSum);
Console.WriteLine("PartNumber: " + line.PartNumber);
Console.WriteLine("ItemDescription: " + line.ItemDescription);
Console.WriteLine("Delivery: " + line.Delivery);
Console.WriteLine("ReferenceType: " + line.ReferenceType);
Console.WriteLine("ReferenceNumber: " + line.ReferenceNumber);
Console.WriteLine("ReferenceDate: " + line.ReferenceDate.ToShortDateString());
Console.WriteLine("PaymentTerms: " + line.PaymentTerms);
Console.WriteLine("SalesOrder: " + line.SalesOrder);
Console.WriteLine("Discount: " + line.Discount);
}
}
if (debug)
{
Console.WriteLine("..................................................................................");
}
TotalLines = num_lines;
reader.Close();
}
if (debug)
{
Console.WriteLine("exit");
}
}
return(true);
}
