using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Serialization;


using log4net;
using log4net.Config;
using Oracle.DataAccess.Client;


using CORE.objects;


namespace CORE {
    [WebService(Namespace = "https://secure.prime-health.net/core/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    public class Core : System.Web.Services.WebService {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        private class BestProvider {
            //These vars hold information about the best bill
            public bill bill = null;
            public PPO.ProviderRecord PPO = null;
            public decimal rate = 0.0m;     
       
            public BestProvider(PPO.ProviderRecord ppo, bill billCopy, decimal currentRate) {
                rate = currentRate;
                bill = billCopy;
                PPO = ppo;
                if (log.IsDebugEnabled) log.Debug(String.Format("New Discount Assignment. Network:{0}  PPO Savings:{1}", ppo.NetworkCode, Math.Round(billCopy.pposavings, 2)));
            }
        }



        [WebMethod]
        public Stack RepriceStack(Stack stack) {            
            NDC.Push(stack.clientid);
            if (log.IsInfoEnabled && stack.ignoreexhibits) log.Info("Ignoring Exhibits");

            if(stack.siteid != null)
                stack.siteid = stack.siteid.Trim();
            if(stack.subclientid != null)
                stack.subclientid = stack.subclientid.Trim();
            if(stack.parentid != null)
                stack.parentid = stack.parentid.Trim();

            Login l = null;
            string[] trueEOBList = null;//This will hold the eoblist to use in queries against the db.
            string[] networks = null;

            DateTime loginStart = DateTime.Now;
            //Authenticate User
            try {
                l = new Login(stack.clientid, stack.clientpassword, stack.siteid, stack.subclientid, stack.parentid);
            } catch (ArgumentException ex) {
                //Something wrong with what they sent
                log.Warn(String.Format(ex.Message + ": {0}/{1} :{2}/{3}", stack.clientid, stack.clientpassword, stack.siteid, stack.subclientid));
                stack.resultcode = 'E';
                stack.result = ex.Message;
                return stack;
            } catch (Exception ex) {
                //Something wrong internally
                log.Error("Error in Login Authentication", ex);
                try {
                    Utils.NotifyAdmin(ex, "Error in Login Authentication for: " + stack.clientid);
                } catch { }
                stack.resultcode = 'S';
                stack.result = "System currenlty unavailable, please try again later";
                return stack;
            }
            log.DebugFormat("Login time was {0}ms", ((TimeSpan)(DateTime.Now - loginStart)).TotalMilliseconds);

            if (l.InternalLogin && stack.eoblist != null && stack.eoblist.Length > 0) {
                trueEOBList = stack.eoblist;
            }


            for (int i = 0; i < stack.bills.Length; i++) {
                bill b = stack.bills[i];//Grab a copy of the bill because structs are lame
                
                #region Network Code Access Setup

                List<string> useTheseNetworks = new List<string>();
                using (OracleConnection oConn = new OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {
                    oConn.Open();                    
                    //Build initial network list                    
                    if (l.DirectsOnly) {
                        useTheseNetworks.Add("1");
                    } else {
                        //billfront.network_product_map comes from a script nightly_refresh.sql which is scheduled to run every morning on phs-serv
                        string initialNetworks = @"SELECT network_code FROM billfront.network_product_map WHERE product_code IN ";
                        switch (b.productcode.Substring(0, 1)) {
                            case "W": initialNetworks += "('A','E','F','W')"; break;
                            case "L":
                            case "B": initialNetworks += "('A','B','D','E','I')"; break;
                            case "C": initialNetworks += "('A','C','D','F')"; break;
                        }
                        using (OracleCommand oCmd = new OracleCommand(initialNetworks, oConn)) {
                            using (OracleDataReader rr = oCmd.ExecuteReader()) {
                                while (rr.Read())
                                    useTheseNetworks.Add(rr["network_code"].ToString());
                            }
                        }
                    }

                    

                    //Only certain clients can have access to Devon, so allow the overrides to pull it in.                    
                    useTheseNetworks.Remove("201");
                    useTheseNetworks.Remove("202");


                    //Query for rules that will require us to take stuff away or add stuff back.
                    string networkAccess = @"
							SELECT *
							FROM billfront.network_access
							WHERE bob_id=:bob_id
							AND (product=:product OR product IS NULL)
						";

                    using (OracleCommand oCmd = new OracleCommand(networkAccess, oConn)) {
                        oCmd.Parameters.Add("bob_id", l.BobId);
                        oCmd.Parameters.Add("product", b.productcode.Substring(0, 1));

                        //Site
                        if (stack.siteid != null && stack.siteid.Length > 0) {
                            oCmd.CommandText += " AND (site=:site OR site IS NULL)";
                            oCmd.Parameters.Add("site", stack.siteid);
                        } else {
                            oCmd.CommandText += " AND site IS NULL";
                        }

                        //Sub Client (Account)
                        if (stack.subclientid != null && stack.subclientid.Length > 0) {
                            oCmd.CommandText += " AND (account=:account OR account IS NULL)";
                            oCmd.Parameters.Add("account", stack.subclientid);
                        } else {
                            oCmd.CommandText += " AND account is null";
                        }


                        //State
                        if (b.provider.state != null && b.provider.state.Length > 0) {
                            oCmd.CommandText += " AND (state=:state OR state IS NULL)";
                            oCmd.Parameters.Add("state", b.provider.state);
                        } else {
                            oCmd.CommandText += " AND state IS NULL";
                        }

                        //Zip Code
                        if (b.provider.zip != null && b.provider.zip.Length > 0) {
                            oCmd.CommandText += " AND (county=(select min(county) from phs.zip_county where zip=:zip) OR county IS NULL)";
                            oCmd.Parameters.Add("zip", b.provider.zip);
                        } else {
                            oCmd.CommandText += " AND county IS NULL";
                        }

                        oCmd.CommandText += " ORDER BY product desc,site desc,account desc,state desc,county desc";
                        using (OracleDataReader rr = oCmd.ExecuteReader()) {

                            while (rr.Read()) {
                                string[] allow = rr["allow"].ToString().Split(',');
                                foreach (string s in allow) {
                                    if (s.Length > 0 && !useTheseNetworks.Contains(s))
                                        useTheseNetworks.Add(s);
                                }

                                string[] deny = rr["deny"].ToString().Split(',');
                                foreach (string s in deny) {
                                    if (s.Length > 0 && useTheseNetworks.Contains(s))
                                        useTheseNetworks.Remove(s);
                                }
                            }
                        }
                    }

                    //Query for networks that we can't use with this tax_id
                    using (OracleCommand oCmd = oConn.CreateCommand()) {
                        oCmd.CommandText = @"
                            SELECT network_code
							FROM billfront.network_tin_exclusion
							WHERE bob_id=:bob_id
							AND (product=:product OR product IS NULL)
                            AND tax_id=:tax_id
                        ";
                        oCmd.Parameters.Add("bob_id", l.BobId);
                        oCmd.Parameters.Add("product", b.productcode.Substring(0, 1));
                        oCmd.Parameters.Add("tax_id", b.provider.taxid);

                        //Site
                        if (stack.siteid != null && stack.siteid.Length > 0) {
                            oCmd.CommandText += " AND (site=:site OR site IS NULL)";
                            oCmd.Parameters.Add("site", stack.siteid);
                        } else {
                            oCmd.CommandText += " AND site IS NULL";
                        }

                        //Sub Client (Account)
                        if (stack.subclientid != null && stack.subclientid.Length > 0) {
                            oCmd.CommandText += " AND (account=:account OR account IS NULL)";
                            oCmd.Parameters.Add("account", stack.subclientid);
                        } else {
                            oCmd.CommandText += " AND account is null";
                        }
                        using (OracleDataReader r = oCmd.ExecuteReader()) {
                            while (r.Read()) {
                                if (useTheseNetworks.Contains(r["network_code"].ToString()))
                                    useTheseNetworks.Remove(r["network_code"].ToString());
                            }
                        }
                    }
                }

                
                networks = useTheseNetworks.ToArray();



                #endregion

                #region Logo Network Setup
                //This enables a logo network to come through the product code
                //For example, a PHS/Verity Logo network would come through as "L48"
                if (b.productcode != null && b.productcode.ToUpper().StartsWith("L")) {
                    List<string> useTheseEobs = new List<string>();
                    useTheseEobs.Add("PHS");
                    if (b.productcode.Length > 1) {
                        string networkToQuery = b.productcode.Substring(1);
                        using (OracleConnection oConn = new OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {
                            oConn.Open();
                            string update = @"SELECT unique eob from rates where network_code=:network_code";
                            using (OracleCommand oCmd = new OracleCommand(update, oConn)) {
                                oCmd.Parameters.Add("network_code", networkToQuery);
                                using (OracleDataReader rr = oCmd.ExecuteReader()) {
                                    while (rr.Read())
                                        useTheseEobs.Add(rr["eob"].ToString());
                                }
                            }
                        }
                    }
                    trueEOBList = useTheseEobs.ToArray();
                }
                #endregion

                #region Reconsiderations
                if (b.reconsiderationof != null && b.reconsiderationof.Length > 0) {
                    using (OracleConnection oConn = new OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {
                        oConn.Open();
                        string update = @"INSERT INTO invoice.utilization(FK_UNIQUE_ID, CLAIMANT_EMPLOYER_NAME, CLAIMANT_NAME, CLAIM_NUMBER, BILL_DATE, BILL_CHARGE, UCR_REDUCTION, STATE_FEE_SCHED_REDUCTION, NURSE_REVIEW, MULTI_PROCEDURE, OTHER_BILL_REVIEW, BILL_REVIEW_TOTAL, PPO_SAVINGS_AMT, ALLOWED_AMT, CPT_CODES, UNITS, ICD9_DX_REF_CODE, OTHER_CODE, PAYOR_CLIENT, OWNER_NETWORK, RECORD_USED_BY, INVOICE_PERIOD, NETWORK_CODE, CONTROL_NO, INVOICE_INCLUDED_ON_NUM, INVOICE_CREATED_ON, PPO_SAVINGS_AMT_RAW, PROCESS_DATE, RECONCILED, PRODUCT) (SELECT FK_UNIQUE_ID, CLAIMANT_EMPLOYER_NAME, CLAIMANT_NAME, CLAIM_NUMBER, BILL_DATE, BILL_CHARGE*-1, UCR_REDUCTION, STATE_FEE_SCHED_REDUCTION*-1, NURSE_REVIEW, MULTI_PROCEDURE, OTHER_BILL_REVIEW, BILL_REVIEW_TOTAL, PPO_SAVINGS_AMT*-1, ALLOWED_AMT*-1, CPT_CODES, UNITS, ICD9_DX_REF_CODE, OTHER_CODE, PAYOR_CLIENT, OWNER_NETWORK, RECORD_USED_BY, To_Char(sysdate,'MONYYYY'), NETWORK_CODE, CONTROL_NO, INVOICE_INCLUDED_ON_NUM, INVOICE_CREATED_ON, PPO_SAVINGS_AMT_RAW*-1, sysdate, RECONCILED, PRODUCT from invoice.utilization where record_used_by=:rub AND control_no=:bid AND process_date>'24-JUL-2006')";
                        using (OracleCommand oCmd = new OracleCommand(update, oConn)) {
                            oCmd.Parameters.Add("rub", l.UtilizationCode);
                            oCmd.Parameters.Add("bid", b.reconsiderationof);
                            try {
                                oCmd.ExecuteNonQuery();
                            } catch (Exception ex) {
                                Utils.NotifyAdmin(ex, String.Format("Failure trying to do reconsideration for the following.  Utilization Code: {0}  Control Number: {1}", l.UtilizationCode, b.reconsiderationof));
                            }
                        }
                    }
                }
                #endregion

                

                using (NDC.Push(b.billid)) {
                    if (log.IsDebugEnabled) log.Debug("Bill Id:" + b.billid);
                    
                    PPO.ProviderRecord[] providers;
                    try {
                        if (!String.IsNullOrEmpty(b.provider.uniqueid) && b.provider.uniqueid != "0") {
                            try {                                
                                providers = new CORE.PPO.ProviderRecord[] { new CORE.PPO.ProviderRecord(b.productcode, b.provider.uniqueid) };
                                if(stack.clientid!="globalcare")
                                    Utils.Notify("jbush@prime-health.net", "CORE was provided an id not by globalcare", "I used an id that "+stack.clientid+" provided");
                            } catch (Exception ex) {
                                Utils.NotifyAdmin(ex, "Error in CORE finding provider id: " + b.provider.uniqueid + " for client: " + stack.clientid);
                                //Default to existing process
                                try {
                                    providers = PPO.ProviderRecord.FindProvidersForBill(b, networks, trueEOBList, l.Tier2,l.AllowSecondaryMatching);
                                } catch (ArgumentOutOfRangeException aex) {
                                    stack.bills[i].resultCode = 'V';
                                    stack.bills[i].result = aex.Message;
                                    continue;
                                }
                            }
                        } else {
                            try {
                                providers = PPO.ProviderRecord.FindProvidersForBill(b, networks, trueEOBList, l.Tier2,l.AllowSecondaryMatching);
                            } catch (ArgumentOutOfRangeException ex) {
                                stack.bills[i].resultCode = 'V';
                                stack.bills[i].result = ex.Message;
                                continue;
                            }
                        }
                        if (providers == null || providers.Length == 0) {
                            log.Warn(String.Format("No providers found for client:{0}, bill:{1}", stack.clientid, b.billid));

                            double totalBilled=0;
                            double totalSavings=0;
                            foreach(lineitem li in b.lineitems){
                                totalBilled += li.linecharge;
                                totalSavings += li.statesavings;
                                li.pposavings = 0;
                                li.repricedlinesavings = li.statesavings;
                                li.stateallowed = li.linecharge - li.statesavings;
                                li.repricedlineamount = li.stateallowed;
                            }
                            b.totalbilledcharges = totalBilled;
                            b.stateucrsavings = b.totalsavings = totalSavings;
                            b.billallowance = b.totalbilledcharges - b.totalsavings;                            

                            stack.bills[i].resultCode = 'P';
                            stack.bills[i].result = "No Provider Match Found.";
                            //Save Non-par providers for contracting
                            
                            Utils.OutOfNetwork(stack.bills[i], l.UtilizationCode.ToString());
                            if (external.MCR.CanSend(l, b)) {
                                stack.bills[i] = external.MCR.Apply(l, b);
                                try {
                                    WriteUtilization(stack.bills[i], "", l);
                                } catch (Exception ex) {
                                    Utils.NotifyAdmin(ex, "Error in CORE writing utilization for MCR bill: " + stack.clientid);
                                }
                            }                            
                            continue;
                        }
                    } catch (ApplicationException ex) {
                        stack.bills[i].resultCode = 'F';
                        stack.bills[i].result = ex.Message;
                        continue;
                    } catch (Exception ex) {
                        #region something bad happened here
                        log.Error("Error in PPOProvider.findPPOProviders()", ex);
                        try {
                            Utils.NotifyAdmin(ex, "Error in CORE finding ppoproviders for client: " + stack.clientid);
                        } catch { }

                        stack.bills[i].resultCode = 'S';
                        stack.bills[i].result = "System currenlty unavailable, please try again later";
                        continue;
                        #endregion
                    }
                    

                    BestProvider myBest = null;

                    //Determine if the bill is inpatient or outpatient
                    bool isInpatient =false;
                    foreach(lineitem li in b.lineitems){
                        if(li.pos == 21 || li.pos == 51 || li.pos == 56 || li.pos == 61){
                            isInpatient=true;
                            break;
                        }
                    }


                    
                    //Master Bill is created once and then has pk_unique_id's substituted in
                    bool exhibitPassed = false;
                    foreach (PPO.ProviderRecord ppo in providers) {
                        PPO.Rate r = null;
                        if (l.Tier2) {
                            r = isInpatient ?
                                ppo.Tier2InpatientRate :
                                ppo.Tier2OutpatientRate;
                        } else {
                            r = isInpatient ?
                                ppo.InpatientRate :
                                ppo.OutpatientRate;
                        }                        
                                                
                        bill billCopy = (bill)b.Clone();// grab an object clone to operate on

                        //Don't allow a manual when the client requests to ignore them
                        if (stack.ignoreexhibits && r.DiscountType=="M") {
                            exhibitPassed = true;
                            continue;
                        }

                        //Apply Discount
                        r.Apply(billCopy);

                        //Compute comparisons
                        decimal currentRate = Decimal.MaxValue;
                        if (!l.BestDiscount) {
                            currentRate = Convert.ToDecimal(billCopy.pposavings) * ppo.RateSplitPercent * ppo.DataQuality;
                        } else {
                            //This is for best savings
                            currentRate = Convert.ToDecimal(billCopy.pposavings);
                        }

                        //No best yet, first one gets dibs.
                        if (myBest == null) {
                            if (log.IsDebugEnabled) log.Debug("First bill gets dibs.");
                            myBest=new BestProvider(ppo,billCopy, currentRate);                            
                            continue;
                        }


                        #region new code which gives "directs" priority
                        if (currentRate >= myBest.rate) {
                            //Opportunity to protect here
                            if (myBest.PPO.IsDirect && myBest.bill.resultCode != 'N') {
                                if (!ppo.IsDirect || ppo.Priority > myBest.PPO.Priority) {
                                    if (log.IsDebugEnabled) log.Debug(String.Format("Network {0} protected from {1}.", myBest.PPO.NetworkCode, ppo.NetworkCode));
                                    continue;
                                } else if (ppo.Priority == myBest.PPO.Priority && currentRate == myBest.rate) {
                                    if (log.IsDebugEnabled) log.Debug(String.Format("Same Priority and Same Rate, Skip."));
                                    continue;
                                }
                            } else if (!ppo.IsDirect && currentRate == myBest.rate) {
                                //Equal Savings, use Priority to determine best provider choice.								
                                if (ppo.Priority >= myBest.PPO.Priority)
                                    continue;
                            }

                            myBest=new BestProvider(ppo, billCopy, currentRate);

                        } else {
                            //Opportunity to override here
                            if (ppo.IsDirect && billCopy.resultCode != 'N') {
                                if (!myBest.PPO.IsDirect || ppo.Priority < myBest.PPO.Priority) {
                                    if (log.IsDebugEnabled) log.Debug(String.Format("Network {0} Override.", ppo.NetworkCode));
                                    myBest= new BestProvider(ppo, billCopy, currentRate);
                                }
                            }
                        }

                        #endregion
                    }
                    

                    DateTime teardownStart = DateTime.Now;
                    #region use best.bill values
                    //Best bill has been chosen, so now we'll pull it's values back into the current Stack.bill
                    if (myBest != null) {
                        if (log.IsDebugEnabled) log.Debug(String.Format("Best Bill Found. Network:{0} PPO Discount:{1}", myBest.PPO.NetworkCode, myBest.bill.pposavings));
                        b = myBest.bill;
                        //TODO: Is this necessary?
                        b.providerid = myBest.PPO.UniqueID.ToString();

                        if (l.WriteUtilization && !stack.testmode) {
                            WriteUtilization(b, myBest.PPO.NetworkCode.ToString(), l);
                            if (log.IsDebugEnabled) log.Debug("Utilization Written");
                        } else {
                            if (log.IsDebugEnabled) log.Debug("Test Mode, Utilization skipped");
                        }

                        if (myBest.PPO.LooseMatch) {
                            try {
                                 using (OracleConnection oConn = new OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {
                                     oConn.Open();
                                     using (OracleCommand cmd = oConn.CreateCommand()) {
                                         cmd.CommandText = "INSERT INTO billfront.secondary_matched_bills(utilization_code,bill_id) values (:utilization_code,:bill_id)";
                                         cmd.Parameters.Add("utilization_code", l.UtilizationCode);
                                         cmd.Parameters.Add("billid", b.billid);
                                         cmd.ExecuteNonQuery();

                                     }
                                }
                            } catch (Exception ex) {
                                Utils.NotifyAdmin(ex, "Error writing secondary match");
                            }
                        }
                    } else {
                        //No providers found
                        b.pposavings = 0;
                        b.resultCode = 'P';
                        b.result = "No Provider Match Found.";

                        if (exhibitPassed) {
                            Utils.MissedExhibits(stack.bills[i], l.UtilizationCode.ToString());
                        } else {
                            if (log.IsDebugEnabled) log.Debug("No PPO Providers found to match.");
                            //Save Non-par providers for contracting
                            Utils.OutOfNetwork(stack.bills[i], l.UtilizationCode.ToString());
                        }

                        if (external.MCR.CanSend(l, b)) {
                            b = external.MCR.Apply(l, b);
                            WriteUtilization(b, "", l);
                        }
                    }
                    #endregion
                    stack.bills[i] = b;//Copy the modified struct back to it's original place
                    log.DebugFormat("Teardown time was {0}ms", ((TimeSpan)(DateTime.Now - teardownStart)).TotalMilliseconds);
                }
            }
            NDC.Clear();

            
            try {
                XmlSerializer s = new XmlSerializer(typeof(Stack));
                string dir = String.Format(@"c:\xml_files\{0}\{1}", stack.clientid, DateTime.Now.ToString("yyyyMM"));
                if (!Directory.Exists(dir))
                    Directory.CreateDirectory(dir);
                TextWriter w = new StreamWriter(dir + "\\" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xml");
                s.Serialize(w, stack);
                w.Close();
                if (!l.InternalLogin) {
                    XmlSerializer xb = new XmlSerializer(typeof(bill));
                    using (Oracle.DataAccess.Client.OracleConnection oConn = new Oracle.DataAccess.Client.OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {
                        oConn.Open();
                        foreach (bill b in stack.bills) {
                            using (Oracle.DataAccess.Client.OracleCommand oCmd = new Oracle.DataAccess.Client.OracleCommand("INSERT INTO billfront.bill_log values(:client_id,:site_id,:subclient_id,:bill_id,sysdate,:data)", oConn)) {
                                oCmd.Parameters.Add("client_id", stack.clientid);
                                oCmd.Parameters.Add("site_id", stack.siteid);
                                oCmd.Parameters.Add("subclient_id", stack.subclientid);
                                oCmd.Parameters.Add("bill_id", b.billid);


                                StringWriter sw = new StringWriter();
                                xb.Serialize(sw, b);
                                oCmd.Parameters.Add("data", Oracle.DataAccess.Client.OracleDbType.XmlType, sw.ToString().Length);
                                oCmd.Parameters["data"].Value = sw.ToString();
                                oCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }

            } catch (Exception ex) {
                log.Error("Error writing XML File", ex);
            }
            
            return stack;
        }

        private void WriteUtilization(bill b, string networkCode, Login l) {
            if (b.resultCode == 'R') {
                using (OracleConnection oConn = new OracleConnection("Data Source=PHSDB;User ID=phs;Password=avalon")) {

                    oConn.Open();
                    string controlNumber = b.billid;
                    if (controlNumber.Length == 0) {
                        using (OracleCommand oCmd = new OracleCommand("Select kevin.RENOVO_SOAP_BILL_SEQ.nextval from dual", oConn)) {
                            controlNumber = oCmd.ExecuteScalar().ToString();
                        }
                    }
                    if (String.IsNullOrEmpty(networkCode))
                        networkCode = "0";
                    objects.RateSplit rs = l.GetRateSplit(Convert.ToInt32(networkCode), b.productcode);
                    if (rs == null) {
                        //Establish Some temp values to keep stuff from boombing
                        rs = new RateSplit("PPO Savings", 0);
                        Utils.NotifyAdmin(String.Format("No rate set up for utilization code: {0} for product: {1}. I'm writing 0 to utilization.", l.UtilizationCode, b.productcode));

                    }

                    string sql = String.Format(@"
						INSERT INTO {0}								
						( fk_unique_id, control_no, bill_date, bill_charge, state_fee_sched_reduction, ppo_savings_amt, allowed_amt, cpt_codes, units, owner_network, network_code, record_used_by, invoice_period, ppo_savings_amt_raw, process_date, claimant_name, claim_number,other_bill_review,product)      
						values
						(:fk_unique_id,:control_no,:bill_date,:bill_charge,:state_fee_sched_reduction,:ppo_savings_amt,:allowed_amt,:cpt_codes,:units,:owner_network,:network_code,:record_used_by,:invoice_period,:ppo_savings_amt_raw,sysdate,:claimant_name,:claim_number,:other_bill_review,:product)
					", rs.UtilizationDestination);

                    //Write lines to database
                    for (int i = 0; i < b.lineitems.Length; i++) {

                        OracleCommand oCmd = new OracleCommand(sql, oConn);
                        oCmd.Parameters.Add("fk_unique_id", b.providerid);
                        oCmd.Parameters.Add("control_no", controlNumber);
                        oCmd.Parameters.Add("bill_date", b.dateofservice);
                        oCmd.Parameters.Add("bill_charge", b.lineitems[i].linecharge);
                        oCmd.Parameters.Add("state_fee_sched_reduction", b.lineitems[i].statesavings);
                        oCmd.Parameters.Add("ppo_savings_amt", b.lineitems[i].pposavings * (double)rs.SplitValue / 100);
                        oCmd.Parameters.Add("allowed_amt", b.lineitems[i].repricedlineamount);
                        oCmd.Parameters.Add("cpt_codes", b.lineitems[i].code);
                        oCmd.Parameters.Add("units", b.lineitems[i].units);
                        oCmd.Parameters.Add("owner_network", 1);
                        oCmd.Parameters.Add("network_code", networkCode);
                        oCmd.Parameters.Add("record_used_by", l.UtilizationCode);
                        oCmd.Parameters.Add("invoice_period", DateTime.Now.ToString("MMMyyyy").ToUpper());
                        oCmd.Parameters.Add("ppo_savings_amt_raw", b.lineitems[i].pposavings);
                        if (b.patient.firstname == null)
                            b.patient.firstname = "";
                        if (b.patient.lastname == null)
                            b.patient.lastname = "";
                        if (b.patient.mi == null)
                            b.patient.mi = "";
                        oCmd.Parameters.Add("claimant_name", String.Format("{0} {1}. {2}", b.patient.firstname.Trim(), b.patient.mi.Trim(), b.patient.lastname.Trim()).Replace(" . ", " "));

                        oCmd.Parameters.Add("claim_number", String.IsNullOrEmpty(b.claimnumber)?b.billid:b.claimnumber);
                        oCmd.Parameters.Add("other_bill_review", b.lineitems[i].othersavings);
                        string product=b.productcode;
                        if(product=="B")
                            product="BN";
                        else if(product.StartsWith("L"))
                            product="BL";
                        oCmd.Parameters.Add("product", product);

                        try {
                            oCmd.ExecuteNonQuery();
                        } catch (Exception ex) {
                            string s = oCmd.CommandText;
                            foreach (OracleParameter p in oCmd.Parameters) {
                                s = s.Replace(":" + p.ParameterName, p.Value.ToString());
                            }
                            string erroredUtilization = String.Format("Error Writing Utilization: " + s);
                            log.Error(erroredUtilization, ex);
                            Utils.NotifyAdmin(ex, erroredUtilization);
                        }
                    }



                    oConn.Close();
                }
            }
        }
        
    }
}