Пример #1
0
        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();
        }
Пример #2
0
        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;
            }
        }