cancellation matrix

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Activities;
using EGR_MasterData;
using EGR_WorkflowLibrary;
using EGR_SBO_Server_Helper;
using EGR_Workflow_Helper;
using EGR_ProcessXMLDocument;

namespace EGR_Workflow_SharedActivities
{
    static class CancellationMatrix
    {

        /// <summary>
        /// Checks the cancellation request message and returns the correct answer
        /// </summary>
        /// <param name="xmlID">The id of the message in the db</param>
        public static CancellationMatrixAnswer GetCancellationAnswer(string xmlID)//TODO different conditions for each division?
        {
            CancellationMatrixAnswer answer = new CancellationMatrixAnswer();

            try
            {
                DateTime nextPossibleCancelDate = new DateTime();
                DateTime oldCancelDate = new DateTime();
                DateTime contractExtensionDate = new DateTime();
                DateTime messageInDate = new DateTime();
                bool determination = false;
                DateTime newCancelDate = new DateTime();
                string processDocument = "";
                string sql = "";
                string contract = "";
                SAPbobsCOM.Company company = SBODIDataProvider.getCompany();
                SAPbobsCOM.Recordset rs = (SAPbobsCOM.Recordset)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);

                ProcessXMLDocument newCancellationMsg = new ProcessXMLDocument(new Guid(xmlID));

                //get in date of cancellation message
                sql = "SELECT U_DATE FROM [@U_EGR_PROCESSDOC_L] WHERE U_MSGID = '" + xmlID.ToString() + "'";
                rs.DoQuery(sql);
                messageInDate = (DateTime)rs.Fields.Item(0).Value;

                if (newCancellationMsg.AttributeList.Where(a => a.Name == "Ende_zum_naechstmoeglichen_Termin").Count() > 0)
                {
                    string cancel = newCancellationMsg.AttributeList.Where(a => a.Name == "Ende_zum_naechstmoeglichen_Termin").First().Value;
                    int year = int.Parse(cancel.Substring(0,4));
                    int month = int.Parse(cancel.Substring(4,2));
                    int day = int.Parse(cancel.Substring(6,2));

                    newCancelDate = new DateTime(year, month, day);
                    determination = true;
                }
                else if (newCancellationMsg.AttributeList.Where(a => a.Name == "Vertragsende").Count() > 0)
                {
                    string cancel = newCancellationMsg.AttributeList.Where(a => a.Name == "Vertragsende").First().Value;
                    int year = int.Parse(cancel.Substring(0, 4));
                    int month = int.Parse(cancel.Substring(4, 2));
                    int day = int.Parse(cancel.Substring(6, 2));

                    newCancelDate = new DateTime(year, month, day);
                }

                if (newCancellationMsg.AttributeList.Where(a => a.Name == "Referenz").Count() > 0)
                {
                    string reference = newCancellationMsg.AttributeList.Where(a => a.Name == "Referenz").First().Value;
                    sql = "SELECT U_PROCESSDOC FROM [@U_EGR_PROCESSDOC_L] WHERE U_EDI_REF_NO = '" + reference + "'";
                    rs.DoQuery(sql);
                    processDocument = ((object)rs.Fields.Item(0).Value).ToString();

                }

                sql = "SELECT U_DETERMPERD, U_DETERMINPU, U_CONTRACT, U_CTEXTENSIO FROM [@U_EGR_OPPORTUNITY_L], [@U_EGR_CONTRACT] WHERE U_PROCESSDOC ='" + processDocument + "' AND " +
                    "[@U_EGR_OPPORTUNITY_L].U_CONTRACT = [@U_EGR_CONTRACT].U_CONTRACT";

                rs.DoQuery(sql); //get next possible cancel date & previous cancel date
                nextPossibleCancelDate = (DateTime)rs.Fields.Item(0).Value;
                oldCancelDate = (DateTime)rs.Fields.Item(1).Value;
                contract = ((object)rs.Fields.Item(2).Value).ToString();
                contractExtensionDate = (DateTime)rs.Fields.Item(3).Value;

                if (oldCancelDate > new DateTime(1900, 1, 1)) //contract was already canceled
                {
                    //get old cancellation date from old cancellation message
                    sql = "SELECT [@U_EGR_PROCESSDOC_L].U_MSGID FROM " +
                          " [@U_EGR_PROCESSDOC_L], [@U_EGR_C_PROCESSTYPE], [@U_EGR_PROCESSDOC_H], [@U_EGR_CONTRACT], [@U_EGR_CONTRACT_LINK], [@U_EGR_SUPPLYCT], [@U_EGR_PODLINK] WHERE " +
                          " [@U_EGR_PROCESSDOC_L].U_PROCESSDOC = [@U_EGR_PROCESSDOC_H].U_PROCESSDOC AND " +
                          " [@U_EGR_CONTRACT_LINK].U_CHCONTRACT = [@U_EGR_CONTRACT].U_CONTRACT AND " +
                          " [@U_EGR_CONTRACT_LINK].U_PACONTRACT = [@U_EGR_SUPPLYCT].U_SUPPLYCT AND " +
                          " [@U_EGR_PROCESSDOC_H].U_POD = [@U_EGR_PODLINK].U_POD AND " +
                          " [@U_EGR_PODLINK].U_INSTALL = [@U_EGR_SUPPLYCT].U_INSTALL AND " +
                          " [@U_EGR_PROCESSDOC_L].U_DATE = [@U_EGR_CONTRACT].U_DETERMINPU AND " +
                          " [@U_EGR_PROCESSDOC_H].U_PROCESSTYP = [@U_EGR_C_PROCESSTYPE].U_PROCESSTYP AND " +
                          " [@U_EGR_C_PROCESSTYPE].U_CATEGORY = 'E35' AND " + //E35 - cancellation
                          " [@U_EGR_CONTRACT_LINK].U_RELTYPE = 'VS' AND " + //VS - supply contract
                          " [@U_EGR_CONTRACT].U_CONTRACT = '" + contract + "'";

                    rs.DoQuery(sql);
                    string xmlguid = rs.Fields.Item(0).Value;

                    ProcessXMLDocument oldCanceallationMsg = new ProcessXMLDocument(new Guid(xmlguid));
                    if (oldCanceallationMsg.AttributeList.Where(a => a.Name == "Kündigungsdatum").Count() > 0)
                    {
                        string cancel = oldCanceallationMsg.AttributeList.Where(a => a.Name == "Kündigungsdatum").First().Value;
                        int year = int.Parse(cancel.Substring(0, 4));
                        int month = int.Parse(cancel.Substring(4, 2));
                        int day = int.Parse(cancel.Substring(6, 2));

                        oldCancelDate = new DateTime(year, month, day);
                    }
                }

                //check if cancellation is in time for determination period, if not re-assign to contract extension date
                if (DateTime.Now.Date > nextPossibleCancelDate.Date)
                {
                    nextPossibleCancelDate = contractExtensionDate;
                }

                if (determination) //cancellation for next possible date requested
                {
                    answer.AnswerType = "nmgl 471";
                    if (oldCancelDate < new DateTime(1900, 1, 1)) //no cancelation yet
                    {
                        if (newCancelDate >= nextPossibleCancelDate) //cancel date >= next possible cancel date
                        {
                            answer.Answer = "E15";// - Zustimmung ohne Korrekturen
                            answer.ResultingCancelDate = nextPossibleCancelDate;
                        }
                        else if (newCancelDate > new DateTime(1900, 1, 1)) //fixed cancel date < next possible cancel date
                        {
                            answer.Answer = "Z01"; //- Zustimmung mit Terminänderung
                            answer.ResultingCancelDate = nextPossibleCancelDate;
                        }
                        else //cancellation for next possible cancel date, no fixed date
                        {
                            answer.Answer = "E15";// - Zustimmung ohne Korrekturen
                            answer.ResultingCancelDate = nextPossibleCancelDate;
                        }
                    }
                    else //contract was canceled already
                    {
                        answer.AnswerType = "fix 93";

                        if (newCancelDate > oldCancelDate)
                        {
                            answer.Answer = "Z29";// - Ablehnung kein Vertragsverhältnis
                        }
                        else if (newCancelDate == oldCancelDate)
                        {
                            answer.Answer = "Z14"; // - Ablehnung Doppelmeldung
                        }
                        else if (newCancelDate >= nextPossibleCancelDate) //new cancel date before old one but same or later then next possible cancel date
                        {
                            answer.Answer = "E15";// - Zustimmung ohne Korrekturen
                            answer.ResultingCancelDate = newCancelDate;
                        }
                        else if (oldCancelDate == nextPossibleCancelDate) //new cancel date before old one but same or later then next possible cancel date
                        {
                            answer.Answer = "Z34";// - Ablehnung Mehrfachkündigung
                        }
                        else //new cancel date before next possible date and before old date
                        {
                            answer.Answer = "Z01"; //- Zustimmung mit Terminänderung
                            answer.ResultingCancelDate = nextPossibleCancelDate;
                        }
                    }
                }
                else //cancellation for next possible date NOT requested
                {
                    if (oldCancelDate < new DateTime(1900, 1, 1)) //no cancelation yet
                    {
                        if (newCancelDate >= nextPossibleCancelDate)
                        {
                            answer.Answer = "E15";// - Zustimmung ohne Korrekturen
                            answer.ResultingCancelDate = newCancelDate;
                        }
                        else //fixed cancel date < next possible cancel date
                        {
                            answer.Answer = "Z12"; //- Ablehnung Vertragsbindung
                        }
                    }
                    else //contract was canceled already
                    {
                        if (newCancelDate > oldCancelDate)
                        {
                            answer.Answer = "Z29";// - Ablehnung kein Vertragsverhältnis
                        }
                        else if (newCancelDate == oldCancelDate)
                        {
                            answer.Answer = "Z14"; // - Ablehnung Doppelmeldung
                        }
                        else if (newCancelDate >= nextPossibleCancelDate) //new cancel date before old one but same or later then next possible cancel date
                        {
                            answer.Answer = "E15";// - Zustimmung ohne Korrekturen
                            answer.ResultingCancelDate = newCancelDate;
                        }
                        else if (oldCancelDate == nextPossibleCancelDate) //new cancel date before old one but same or later then next possible cancel date
                        {
                            answer.Answer = "Z34";// - Ablehnung Mehrfachkündigung
                        }
                    }

                    sql = "UPDATE [@U_EGR_CONTRACT] SET U_DETERMPINU = '"+ answer.ResultingCancelDate.ToShortDateString() +"' WHERE U_CONTRACT = '" + contract + "'";
                    rs.DoQuery(sql);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("??? error: " + ex);
            }

            return answer;
        }




    }
}