void WriteToPRM(InvoiceLine line)
{
WriteToPRM("Line:{");
WriteToPRM("LineNo", line.LineNo, ",");
WriteToPRM("Delivery", line.Delivery, ",");
WriteToPRM("ReferenceType", line.ReferenceType, ",");
WriteToPRM("ReferenceNumber", line.ReferenceNumber.ToString(), ",");
WriteToPRM("ReferenceDate", line.ReferenceDate.ToShortDateString(), ",");
WriteToPRM("PaymentTerms", line.PaymentTerms, ",");
WriteToPRM("UnitQuantity", line.UnitQuantity.ToString(), ",");
WriteToPRM("ItemPriceBruto", line.ItemPriceBruto.ToString(), ",");
WriteToPRM("LineSum", line.LineSum.ToString(), ",");
WriteToPRM("ItemDescription", line.ItemDescription, ",");
WriteToPRM("PartNumber", line.PartNumber, ",");
WriteToPRM("ItemBarcode", line.ItemBarcode, ",");
WriteToPRM("CustomerBarcode", line.CustomerBarcode, ",");
WriteToPRM("SalesOrder", line.SalesOrder, "}\r\n");
}
// 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);
}
public void Add(InvoiceLine line)
{
flexLines.Add(line);
}
