public string CreateMultiSampleInvoice(string arlNumbers, Identification identification)
{
int invoiceNumber = 0;
List<string> tests = new List<string>();
string testIds = null;
List<string> charges = new List<string>();
string chargeIds = null;
try
{
//Data for invoice
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetMultipleInvoicableSamples";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumbers", System.Data.SqlDbType.NVarChar, 4000).Value = arlNumbers;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
Invoice invoice = new Invoice();
foreach (DataRow row in returnDT.Rows)
{
invoice.ARLNumber = Convert.ToInt32(row["ARLNumber"]);
invoice.ClientName = row["ClientName"].ToString();
invoice.AccountingId = row["AccountingId"].ToString();
invoice.PONumber = row["PONumber"].ToString();
invoice.TermName = row["TermName"].ToString();
invoice.IncludeYN = (bool)false;
}
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspGetMultipleInvoicableSampleItems";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ARLNumbers", System.Data.SqlDbType.NVarChar, 4000).Value = arlNumbers;
DataTable returnDT2 = DbConnection.ExecuteQuery(dbCommand);
SmartCollection<InvoiceItem> itemList = new SmartCollection<InvoiceItem>();
foreach (DataRow row in returnDT2.Rows)
{
InvoiceItem invoiceItem = new InvoiceItem();
invoiceItem.AccountingCode = row["AccountingCode"].ToString();
invoiceItem.Class = row["DepartmentName"].ToString();
invoiceItem.Other1 = row["TestName"].ToString();
invoiceItem.Other2 = row["AnalystName"].ToString();
invoiceItem.Description = row["Description"].ToString();
if (row["BasePrice"] != DBNull.Value)
invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]);
if (row["Ordering"].ToString() != "5" && !tests.Contains(row["SampleTestId"].ToString()))
tests.Add(row["SampleTestId"].ToString());
if (row["Ordering"].ToString() == "5" && !charges.Contains(row["SampleTestId"].ToString()))
charges.Add(row["SampleTestId"].ToString());
itemList.Add(invoiceItem);
}
invoice.InvoiceItems = itemList;
//SessionManager sessionManager = SessionManager.getInstance();
QBSessionManager sessionManager = new QBSessionManager();
sessionManager.OpenConnection2("", "LIMS2", ENConnectionType.ctLocalQBD);
sessionManager.BeginSession("", ENOpenMode.omDontCare);
//IMsgSetRequest requestMsgSet = sessionManager.getMsgSetRequest();
//IMsgSetRequest requestMsgSet = sessionManager.CreateMsgSetRequest("US", 13, 0);
IMsgSetRequest requestMsgSet = getLatestMsgSetRequest(sessionManager);
requestMsgSet.Attributes.OnError = ENRqOnError.roeContinue;
// ERROR RECOVERY:
// All steps are described in QBFC Developers Guide, on pg 41
// under section titled "Automated Error Recovery"
// (1) Set the error recovery ID using ErrorRecoveryID function
// Value must be in GUID format
// You could use c:\Program Files\Microsoft Visual Studio\Common\Tools\GuidGen.exe
// to create a GUID for your unique ID
string errecid = "{C5F2272F-4DAD-435F-8ABB-EB1AC0802D52}";
sessionManager.ErrorRecoveryID.SetValue(errecid);
// (2) Set EnableErrorRecovery to true to enable error recovery
sessionManager.EnableErrorRecovery = true;
// (3) Set SaveAllMsgSetRequestInfo to true so the entire contents of the MsgSetRequest
// will be saved to disk. If SaveAllMsgSetRequestInfo is false (default), only the
// newMessageSetID will be saved.
sessionManager.SaveAllMsgSetRequestInfo = true;
// (4) Use IsErrorRecoveryInfo to check whether an unprocessed response exists.
// If IsErrorRecoveryInfo is true:
if (sessionManager.IsErrorRecoveryInfo())
{
//string reqXML;
//string resXML;
IMsgSetRequest reqMsgSet = null;
IMsgSetResponse resMsgSet = null;
// a. Get the response status, using GetErrorRecoveryStatus
resMsgSet = sessionManager.GetErrorRecoveryStatus();
// resXML = resMsgSet.ToXMLString();
// MessageBox.Show(resXML);
if (resMsgSet.Attributes.MessageSetStatusCode.Equals("600"))
{
// This case may occur when a transaction has failed after QB processed
// the request but client app didn't get the response and started with
// another company file.
// MessageBox.Show("The oldMessageSetID does not match any stored IDs, and no newMessageSetID is provided.");
int i = 0;
}
else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9001"))
{
// MessageBox.Show("Invalid checksum. The newMessageSetID specified, matches the currently stored ID, but checksum fails.");
int i = 0;
}
else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9002"))
{
// Response was not successfully stored or stored properly
// MessageBox.Show("No stored response was found.");
int i = 0;
}
// 9003 = Not used
else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9004"))
{
// MessageSetID is set with a string of size > 24 char
//MessageBox.Show("Invalid MessageSetID, greater than 24 character was given.");
int i = 0;
}
else if (resMsgSet.Attributes.MessageSetStatusCode.Equals("9005"))
{
//MessageBox.Show("Unable to store response.");
int i = 0;
}
else
{
IResponse res = resMsgSet.ResponseList.GetAt(0);
int sCode = res.StatusCode;
//string sMessage = res.StatusMessage;
//string sSeverity = res.StatusSeverity;
//MessageBox.Show("StatusCode = " + sCode + "\n" + "StatusMessage = " + sMessage + "\n" + "StatusSeverity = " + sSeverity);
if (sCode == 0)
{
//MessageBox.Show("Last request was processed and Invoice was added successfully!");
int i = 0;
}
else if (sCode > 0)
{
//MessageBox.Show("There was a warning but last request was processed successfully!");
int i = 0;
}
else
{
//MessageBox.Show("It seems that there was an error in processing last request");
// b. Get the saved request, using GetSavedMsgSetRequest
reqMsgSet = sessionManager.GetSavedMsgSetRequest();
//reqXML = reqMsgSet.ToXMLString();
//MessageBox.Show(reqXML);
// c. Process the response, possibly using the saved request
resMsgSet = sessionManager.DoRequests(reqMsgSet);
IResponse resp = resMsgSet.ResponseList.GetAt(0);
int statCode = resp.StatusCode;
if (statCode == 0)
{
string resStr = null;
IInvoiceRet invRet = resp.Detail as IInvoiceRet;
resStr = resStr + "Following invoice has been successfully submitted to QuickBooks:\n\n\n";
if (invRet.TxnNumber != null)
resStr = resStr + "Txn Number = " + Convert.ToString(invRet.TxnNumber.GetValue()) + "\n";
} // if (statusCode == 0)
} // else (sCode)
} // else (MessageSetStatusCode)
// d. Clear the response status, using ClearErrorRecovery
sessionManager.ClearErrorRecovery();
//MessageBox.Show("Proceeding with current transaction.");
}
IInvoiceAdd invAdd = requestMsgSet.AppendInvoiceAddRq();
invAdd.TemplateRef.FullName.SetValue("Copy 2 : Intuit Service Invoice");
// CustomerRef -> FullName
if (invoice.ClientName != "")
{
invAdd.CustomerRef.FullName.SetValue(invoice.AccountingId);
}
// PO Number
if (invoice.PONumber != "")
{
invAdd.PONumber.SetValue(invoice.PONumber);
}
// Terms
if (invoice.TermName != "")
{
invAdd.TermsRef.FullName.SetValue(invoice.TermName);
DateTime DT_DueDate = System.DateTime.Today;
double dueInDays = getDueInDays(invoice.TermName);
DT_DueDate = DT_DueDate.AddDays(dueInDays);
invAdd.DueDate.SetValue(DT_DueDate);
}
// TxnDate
DateTime DT_TxnDate = System.DateTime.Today;
invAdd.TxnDate.SetValue(DT_TxnDate);
// CustomerMsgRef -> FullName
invAdd.CustomerMsgRef.FullName.SetValue("Thank you for your business.");
//Line Items
foreach (InvoiceItem item in invoice.InvoiceItems)
{
IORInvoiceLineAdd invLineAdd = invAdd.ORInvoiceLineAddList.Append();
if (item.AccountingCode != "")
{
invLineAdd.InvoiceLineAdd.ItemRef.FullName.SetValue(item.AccountingCode);
}
if (item.Class != "")
{
invLineAdd.InvoiceLineAdd.ClassRef.FullName.SetValue(item.Class);
}
if (item.Other1 != "")
{
invLineAdd.InvoiceLineAdd.Other1.SetValue(item.Other1);
}
if (item.Other2 != "")
{
invLineAdd.InvoiceLineAdd.Other2.SetValue(item.Other2);
}
if (item.Description != "")
{
invLineAdd.InvoiceLineAdd.Desc.SetValue(item.Description);
}
//invLineAdd.InvoiceLineAdd.
if (item.AccountingCode != "DISCOUNT")
invLineAdd.InvoiceLineAdd.Quantity.SetValue(Convert.ToDouble(1));
if (item.Amount.IsNotNull())
{
invLineAdd.InvoiceLineAdd.ORRatePriceLevel.Rate.SetValue(Convert.ToDouble(item.Amount));
}
if (item.Amount.IsNotNull())
{
invLineAdd.InvoiceLineAdd.Amount.SetValue(Convert.ToDouble(item.Amount));
}
}
// Execute request in QB
IMsgSetResponse responseMsgSet = sessionManager.DoRequests(requestMsgSet);
//IMsgSetResponse responseMsgSet = processRequestFromQB(sessionManager, requestMsgSet);
//sessionManager.endSession();
//sessionManager.closeConnection();
sessionManager.EndSession();
sessionManager.CloseConnection();
sessionManager = null;
string[] status = new string[3];
if (responseMsgSet != null) status = parseInvoiceAddRs(responseMsgSet);
string msg = "";
if (responseMsgSet != null & status[0] == "0")
{
IInvoiceRet retInv = responseMsgSet.ResponseList.GetAt(0).Detail as IInvoiceRet;
IQBIntType new_inv_num = retInv.TxnNumber as IQBIntType;
invoiceNumber = Convert.ToInt32(new_inv_num.GetValue()) - 1;
msg = "Invoice was added successfully!";
foreach (string test in tests)
testIds = testIds + test.ToString() + ",";
if (testIds != null)
testIds = testIds.Substring(0, testIds.Length - 1);
foreach (string charge in charges)
chargeIds = chargeIds + charge.ToString() + ",";
if (chargeIds != null)
chargeIds = chargeIds.Substring(0, chargeIds.Length - 1);
DbCommand.CommandType = CommandType.StoredProcedure;
DbCommand.CommandText = "uspSaveInvoiceNumberForTests";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@Tests", System.Data.SqlDbType.NVarChar, 4000).Value = testIds;
if (chargeIds != null)
DbCommand.Parameters.Add("@Charges", System.Data.SqlDbType.NVarChar, 4000).Value = chargeIds;
else
DbCommand.Parameters.Add("@Charges", System.Data.SqlDbType.NVarChar, 4000).Value = "";
DbCommand.Parameters.Add("@QBInvoiceNumber", System.Data.SqlDbType.Int).Value = invoiceNumber;
DbConnection.ExecuteQuery(DbCommand);
}
else
{
//throw new Exception(status[2]);
msg = "Could not add invoice.";
if (status[2].IndexOf("Message") > 0)
throw new Exception("Customer Message not found in QuickBooks.");
else if (status[2].IndexOf("Customer") > 0)
throw new Exception("Client not found in QuickBooks.");
else if (status[2].IndexOf("Item") > 0)
{
string sub = status[2].Substring(status[2].IndexOf("\"") + 1);
int len = sub.IndexOf("\"");
throw new Exception("Item " + status[2].Substring(status[2].IndexOf("\"") + 1, len) + " not found in QuickBooks.");
}
else
throw new Exception(status[2]);
}
msg = msg + "\n\n";
msg = msg + "Status Code = " + status[0] + "\n";
msg = msg + "Status Severity = " + status[1] + "\n";
msg = msg + "Status Message = " + status[2] + "\n";
}
catch
{
throw;
}
return invoiceNumber.ToString();
}
private SmartCollection<InvoiceItem> GetInvoicableSampleItems(bool showAll, ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? arlNumber, Identification identification)
{
try
{
SmartCollection<InvoiceItem> resultList = new SmartCollection<InvoiceItem>();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetInvoicableSampleItems";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber;
dbCommand.Parameters.Add("@IncludeAll", System.Data.SqlDbType.Bit).Value = showAll;
DataTable returnDT = dbConnection.ExecuteQuery(dbCommand);
foreach (DataRow row in returnDT.Rows)
{
InvoiceItem invoiceItem = new InvoiceItem();
/*using (CatalogDAO catalogDao = new CatalogDAO())
{
sampleTest.CatalogItem = catalogDao.GetCatalogItem((int)sampleTest.CatalogId, identification);
}
// Do not load predefined notes for canceled tests
if (sampleTest.Status != SampleTestStatus.Cancelled)
{
using (SystemDAO systemDao = new SystemDAO())
{
//sampleTest.CatalogNotes = systemDao.ReturnNoteItems(sampleTest.CatalogItem);
}
}*/
invoiceItem.AccountingCode = row["AccountingCode"].ToString();
invoiceItem.Class = row["DepartmentName"].ToString();
invoiceItem.Other1 = row["TestName"].ToString();
invoiceItem.Other2 = row["AnalystName"].ToString();
invoiceItem.Description = row["Description"].ToString();
if (row["BasePrice"] != DBNull.Value)
invoiceItem.Amount = Convert.ToDouble(row["BasePrice"]);
if (row["SampleTestID"] != DBNull.Value)
invoiceItem.SampleTestId = (int)row["SampleTestID"];
if (row["Ordering"] != DBNull.Value)
invoiceItem.Ordering = (int)row["Ordering"];
if (row["QBInvoiceNumber"] != DBNull.Value)
invoiceItem.InvoiceId = (int)row["QBInvoiceNumber"];
resultList.Add(invoiceItem);
}
returnDT = null;
return resultList;
}
catch
{
throw;
}
}
