Esempio n. 1
0
        public void UpdateReqItems()
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/UpdateReqItems");
            }
            // private Hashtable itemsThatChanged
            int    item   = 0;
            string status = "";

            if (debug)
            {
                lm.Write("DataSetManager/UpdateReqItems");
            }
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            foreach (object key in itemsThatChanged.Keys)
            {
                try
                {
                    item            = Convert.ToInt32(key);
                    status          = itemsThatChanged[item].ToString();
                    Request.Command = "Execute ('" + BuildReqItemUpdateQuery(item, status) + "')";
                    ODMDataSetFactory.ExecuteDataWriter(ref Request);
                }
                catch (Exception ex)
                {
                    lm.Write("DataSetManager/UpdateReqItems:  " + ex.Message);
                }
            }
        }
Esempio n. 2
0
        private DataSet GetItemDescrQtyUM(string reqID)
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/GetItemDescr");
            }
            DataSet    dsItemDescr = new DataSet();
            ODMRequest Request     = new ODMRequest();

            Request.ConnectString = ConfigData.Get("cnctHEMM_HMC");
            Request.CommandType   = CommandType.Text;
            Request.Command       = "SELECT ITEM_NO, DESCR,QTY,RIGHT(RTRIM(UM_CD),2) " +
                                    "FROM REQ_ITEM " +
                                    "JOIN ITEM ON REQ_ITEM.ITEM_ID = ITEM.ITEM_ID " +
                                    "WHERE REQ_ID = " + reqID;
            try
            {
                dsItemDescr = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/GetItemDescr:  " + ex.Message);
            }
            return(dsItemDescr);
        }
Esempio n. 3
0
        public void TruncateReqItemReceipt()
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/TruncateReqItemReceipt");
            }
            //remove KILLED and COMPLETE reqItems from the hmcmm_ReqItemReceipt table
            int        item    = 0;
            string     status  = "";
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = BuildTruncateReqItemReceipt();
            try
            {
                ReqReceiptCount(); //the "before" count
                ODMDataSetFactory.ExecuteNonQuery(ref Request);
                lm.Write("DataSetManager/TruncateReqItemReceipt:  ENQ Complete");
                ReqReceiptCount(); //the "after" count
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/TruncateReqItemReceipt:  " + ex.Message);
            }
        }
Esempio n. 4
0
        public void LoadYesterdayList()
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/LoadYesterdayList");
            }
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/LoadYesterdayList");
            }
            //select req_item_id and req_item_stat and put into to a hashtable
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "Execute ('" + BuildYesterdayQuery() + "')";

            if (debug)
            {
                lm.Write("DataSetManager/LoadYesterdayList:  " + Request.Command);
            }
            try
            {
                dsYesterday = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/LoadYesterdayList:  " + ex.Message);
            }
        }
Esempio n. 5
0
        private void ReqReceiptCount()
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/ReqReceiptCount");
            }
            ArrayList  count   = new ArrayList();
            ODMRequest Request = new ODMRequest();

            string[] dtYesterday = DateTime.Now.AddDays(-1).ToString().Split(" ".ToCharArray()); //this prints yesterday's date
            string[] dtToday     = DateTime.Now.ToString().Split(" ".ToCharArray());             //this is for the get COUNT(*) query.
            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = BuildReqItemCountQuery(dtToday[0]);
            try
            {
                count = ODMDataSetFactory.ExecuteDataReader(ref Request);
                //if (debug && count.Count > 0)
                lm.Write("Req Receipt Count for " + dtYesterday[0] + " : " + count[0]);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/ReqReceiptCount:  " + ex.Message);
            }
        }
Esempio n. 6
0
        public void LoadCurrentChanges(string reqItemList)
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/LoadCurrentChanges");
            }
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrHEMM;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "Execute ('" + BuildCurrentQuery(reqItemList) + "')";

            if (debug)
            {
                lm.Write("DataSetManager/LoadTodaysDataSet:  " + Request.Command);
            }
            try
            {
                dsCurrentChangeDates = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/LoadTodaysDataSet:  " + ex.Message);
            }
        }
Esempio n. 7
0
        private void GetResults(string function)
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/GetResults");
            }
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStr;
            Request.CommandType   = CommandType.Text;
            Request.Command       = sql;

            if (trace)
            {
                lm.Write("DataSetManager/GetResults:  " + Request.Command);
            }
            try
            {
                if (function == "dropList")
                {
                    results = ODMDataSetFactory.ExecuteDataReader(ref Request);
                }
                else if (function == "controls")
                {
                    dsResults = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
                }
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/GetResults:  " + ex.Message);
            }
        }
        public void LoadDataSet()
        {
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = om.DbaseConnStr;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "Execute ('" + BuildQuery() + "')";

            if (debug)
            {
                lm.Write("DataSetLoad/LoadDataSet:  " + Request.Command);
            }
            try
            {
                thisDS = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
                //QuickWatch:
                //((System.Data.DataRow)((new System.Linq.SystemCore_EnumerableDebugView(((System.Data.DataTable)((new System.Collections.ArrayList.ArrayListDebugView(thisDS.Tables.List)).Items[0])).Rows.table.Rows.list)).Items[0])).ItemArray
                // Row Count:
                //((System.Data.DataTable)((new System.Collections.ArrayList.ArrayListDebugView(thisDS.Tables.List)).Items[0])).Rows.Count
            }
            catch (Exception ex)
            {
                lm.Write("DataSetLoad/LoadDataSet:  " + ex.Message);
            }
        }
Esempio n. 9
0
        protected DataSet BuildSQLRefresh()
        {
            if (trace) lm.Write("TRACE:  PointOfUse.BuildSQLRefresh()");
            int itemID = 0;
            double cost = 0.0;
            string itemNo = "";
            string sqlRefresh = "SELECT AIA.ITEM_ID, ALIAS_ID, Location_Procedure_Code " +
                                "FROM D_SUPPLY_ITEM DSI " +
                                "JOIN AHI_ITEM_ALIAS AIA ON AIA.ITEM_ID = DSI.SUPPLY_ITEM_ID " +
                                "JOIN D_INVENTORY_ITEMS DII ON DII.ITEM_ID = AIA.ITEM_ID " +
                                "JOIN D_SUPPLY_SOURCE_ITEM DSSI ON DSSI.Supply_Item_Id = DSI.Supply_Item_Id " +
                                "WHERE Billable_Flag = 1 " +
                                "AND DII.ACTIVE_FLAG = 1 " +
                                "AND LEN(Location_Procedure_Code) > 0 " +
                                "ORDER BY Alias_Id";

ODMRequest Request = new ODMRequest();
            Request.ConnectString = mpousConnectStr; //connect str for PointOfUse
            Request.CommandType = CommandType.Text;
            Request.Command = sqlRefresh;
            if (verbose)
                Console.WriteLine("Updating Previous Value Table: " + patientPrice.Keys.Count + " Changes.");
            try
            {
                dsRefresh = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("UpdatePatCharges: BuildSQLRefresh:  " + ex.Message);
                errMssg.Notify += "PointOfUse: BuildSQLRefresh:  " + ex.Message + Environment.NewLine;
            }
            return dsRefresh;
        }
Esempio n. 10
0
        public void RefreshPreviousValues()
        {
            if (trace) lm.Write("TRACE:  PointOfUse.RefreshPreviousValues()");
            //DataSet dsRefresh = new DataSet();
            dsRefresh.Tables.Clear();
            ODMRequest Request = new ODMRequest();
            Request.ConnectString = biAdminConnectStr;
            Request.CommandType = CommandType.Text;
            Request.Command = "TRUNCATE TABLE uwm_BIAdmin.dbo.uwm_MPOUS_LocProcCode ";
            if (verbose)
                Console.WriteLine("Updating Previous Value Table: " + patientPrice.Keys.Count + " Changes.");

            try
            {
                if (OkToUpdate)
                    ODMDataSetFactory.ExecuteNonQuery(ref Request); //truncate the uwm_MPOUS_LocProcCode table

                dsRefresh = BuildSQLRefresh(); //This reads from PointOfUse db and returns a data set
                if (verbose)
                    Console.WriteLine("Updating the PatientItemCharge table with " + dsRefresh.Tables[0].Rows.Count + " records. This will take a moment or two");
                foreach (DataRow dr in dsRefresh.Tables[0].Rows)
                {
                    Request.Command = "INSERT INTO uwm_BIAdmin.dbo.uwm_MPOUS_LocProcCode VALUES(" +
                                                    dr.ItemArray[0] + ",'" + dr.ItemArray[1] + "','" + dr.ItemArray[2].ToString().Trim() + "')";
                    //OkToUpdate = false  TO PREVENT CHANGING THE uwm_MPOUS_LocProcCode TABLE
                    if(OkToUpdate)
                        ODMDataSetFactory.ExecuteNonQuery(ref Request);
                }
            }
            catch (Exception ex)
            {
                lm.Write("PointOfUse: RefreshPreviousValuTable:  " + ex.Message);
                errMssg.Notify += "PointOfUse: RefreshPreviousValuTable:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 11
0
        private void DBReadLatestTierValues(string select)
        {
            if (trace)
            {
                lm.Write("TRACE:  DataManager.DBReadLatestTierValues()");
            }
            lm.Write("PCUConsole:DataManager:DBReadLatestTierValues()");
            dollarLimits.Clear();
            multiplierValu.Clear();

            ArrayList  alResults = new ArrayList();
            ODMRequest Request   = new ODMRequest();

            Request.ConnectString = connectStr;
            Request.CommandType   = CommandType.Text;
            Request.Command       = select;
            try
            {
                alResults = ODMDataSetFactory.ExecuteDataReader(ref Request, attributeCount);
                ParseTierValuResults(alResults);
            }
            catch (Exception ex)
            {
                lm.Write("DataManager: DBReadLatestTierValues:  " + ex.Message);
                errMssg.Notify += "DataManager: DBReadLatestTierValues:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 12
0
        public void UpdateCharges()
        {//FULL UPDATE
            //INCREMENTAL
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStr;
            Request.CommandType   = CommandType.Text;
            string command = "update SLOC_ITEM set REC_UPDATE_DATE = GETDATE(), REC_UPDATE_USR_ID = 2827, PAT_CHRG_PRICE = "; //////// USE THIS FOR PRODUCTION

            //user_id 2827 = sv_pmm_jobs

            if (debug)
            {
                command = "update SLOC_ITEM set REC_UPDATE_DATE = GETDATE(), REC_UPDATE_USR_ID = 2827, PAT_CHRG_PRICE = "; //use this for TEST
            }

            if (verbose)
            {
                Console.WriteLine("Updating Patient Charges");
                Console.WriteLine("Each dot = 1000 records");
            }
            lm.Write("PCUConsole.PatientCharges: UpdateCharges: " + "patientPrice.Keys Count: " + patientPrice.Keys.Count);
            int itemCount = 1;                        //used for the Verbose Output section

            foreach (int itemID in patientPrice.Keys) //gives the number of charges to update
            {
                updateCount = patientPrice.Keys.Count;
                //  lm.Write("updateCount = " + updateCount + " itemID = " + itemID);  //for test

                #region verbose output
                if (verbose)
                {
                    if (++itemCount % 1000 == 0)
                    {
                        if ((itemCount / 1000) % 5 == 0)
                        {
                            Console.Write(itemCount / 1000);
                        }
                        else
                        {
                            Console.Write(".");
                        }
                    }
                }
                #endregion
                try
                {
                    Request.Command = command + FormatDollarValue(patientPrice[itemID].ToString()) + " WHERE ITEM_ID = " +
                                      itemID.ToString();
                    ODMDataSetFactory.ExecuteNonQuery(ref Request);
                    lm.Write("UPDATE VALUE:" + TAB + itemID.ToString() + TAB + FormatDollarValue(patientPrice[itemID].ToString()));
                }
                catch (Exception ex)
                {
                    lm.Write("PatientCharges: UpdateCharges:  " + ex.Message);
                    errMssg.Notify += "PatientCharges: UpdateCharges:  " + ex.Message + Environment.NewLine;
                }
            }
        }
Esempio n. 13
0
 //the hashtables:
 //patientPrice is passed in - key = HEMM Item_ID   value = new Pat_Chg_Price
 //itemIDPCost uses the Item_No from itemNoPCost to get the MPOUS Item_ID - key = MPOUS Item_ID   value = new Pat_Chg_Price
 public PointOfUse()
 {
     if (trace) lm.Write("TRACE:  PointOfUse.PointOfUse(constructor)");
     //ODMDataSetFactory = new ODMDataFactory();
     //ConfigData = (NameValueCollection)ConfigurationSettings.GetConfig("PatientChargeUpdate");
     //mpousConnectStr = ConfigData.Get("cnctMPOUS");
     //uwmConnectStr = ConfigData.Get("cnctHEMM_HMC");
     //biAdminConnectStr = ConfigData.Get("cnctBIAdmin");
     //OkToUpdate = Convert.ToBoolean(ConfigData.Get("updateTables"));
     Request = new ODMRequest();
     Request.CommandType = CommandType.Text;
 }
Esempio n. 14
0
        private void UpdateMPOUS()
        {
            string     update1     = "UPDATE D_INVENTORY_ITEMS SET Location_Procedure_Code = '";
            string     update2     = "' WHERE Billable_Flag = 1  AND Item_Id = ";
            string     itemID      = "";
            string     locProcCode = "";
            ODMRequest Request     = new ODMRequest();

            Request.CommandType   = CommandType.Text;
            Request.ConnectString = debug ? biAdminConnectStr : mpousConnectStr;

            if (debug)
            {
                update1 = "UPDATE [uwm_BIAdmin].[dbo].[uwm_D_INVENTORY_ITEMS] SET Location_Procedure_Code = '";  //this is for TEST
            }

            if (itemNoPCost.Count > 0)
            {
                try
                {
                    foreach (DictionaryEntry item in itemNoPCost)
                    {                                                                //item.key = alias_id   item.value = Loc Proc Code
                        itemID      = MPOUS_Item_ID[item.Key.ToString()].ToString(); //converts the Alias_ID to the mpous Item_ID
                        locProcCode = item.Value.ToString();

                        //the output needs to be mpous item_id and LPC
                        lm.Write("Old ID/LPC: " + TAB + itemID + TAB + aliasLPC[item.Key.ToString()]); //aliasLPC is indexed with the item_no, not the item_id
                        lm.Write("New ID/LPC: " + TAB + itemID + TAB + locProcCode);

                        if (!(itemID.Length > 0))
                        {
                            OkToUpdate = false;
                        }

                        Request.Command = update1 + locProcCode + update2 + itemID;
                        if (OkToUpdate)
                        {
                            ODMDataSetFactory.ExecuteDataWriter(ref Request);
                        }
                    }
                }
                catch (Exception ex)
                {
                    lm.Write("MPOUSCharges.UpdateMPOUS:  " + ex.Message);
                    errMssg.Notify += "MPOUSCharges.UpdateMPOUS:  " + ex.Message + Environment.NewLine;
                }
            }
            else
            {
                lm.Write("MPOUSCharges.UpdateMPOUS: There were no patient charges to update on the MPOUS side.");
            }
        }
Esempio n. 15
0
        public void GetUserName(string uid)
        {
            ArrayList  name    = new ArrayList();
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrHEMM;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "SELECT NAME FROM USR WHERE LOGIN_ID = '" + uid + "'";
            name = ODMDataSetFactory.ExecuteDataReader(ref Request);
            if (name.Count > 0)
            {
                userName = name[0].ToString().Trim();
            }
        }
Esempio n. 16
0
        protected void RefreshPreviousValuTable()
        {//previous value table for HEMM is uwm_IVPItemCost
            if (trace)
            {
                lm.Write("TRACE:  PCUCost.RefreshPreviousValuTable()");
            }
            //INCREMENTAL
            DataSet    dsRefresh = new DataSet();
            ODMRequest Request   = new ODMRequest();

            Request.ConnectString = biAdminConnectStr;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "TRUNCATE TABLE uwm_BIAdmin.dbo.uwm_IVPItemCost ";
            if (verbose)
            {
                Console.WriteLine("Updating Previous Value Table: " + patientPrice.Keys.Count + " Changes.");
            }

            try             //UPDATE uwm_BIAdmin.dbo.uwm_IVPItemCost -  This needs to be kept intact during testing and is only used in prod.
            {
                ///////////PRODUCTION            From HERE//////////////   --UPDATES THE PatientItemCharge TABLE
                if (!debug)
                {
                    ODMDataSetFactory.ExecuteNonQuery(ref Request); // first truncate the uwm_IVPItemCost table
                    dsRefresh = BuildSQLRefresh();                  //This reads from HEMM db
                    if (verbose)
                    {
                        Console.WriteLine("Updating the PatientItemCharge table with " + dsRefresh.Tables[0].Rows.Count +
                                          " records. This will take a moment or two");
                    }
                    foreach (DataRow dr in dsRefresh.Tables[0].Rows)
                    {
                        Request.Command = "INSERT INTO uwm_BIAdmin.dbo.uwm_IVPItemCost VALUES(" +
                                          dr.ItemArray[0] + "," + dr.ItemArray[1] + ",'" +
                                          dr.ItemArray[2].ToString().Trim() + "')";
                        ODMDataSetFactory.ExecuteNonQuery(ref Request);
                    }
                }
                // To HERE//////////////
            }
            catch (Exception ex)
            {
                lm.Write("PCUCost: RefreshPreviousValuTable:  " + ex.Message);
                errMssg.Notify += "PCUCost: RefreshPreviousValuTable:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 17
0
        protected void BuildHEMMPriceTable()
        {//this gets the ITEM_NO and PAT_CHRG_PRICE so that these values can be compared to the MPOUS side.
            //each HEMM item_no that meets the qurey criteria is compared to the mpous alias_id in the aliasList ArrayList
            //the ones that match are saved to HEMMPrice hashtable (item_no/cost). the alternative is to run this query alias.Count
            //number of times. here we run the query once and compare the results.
            HEMMPrice.Clear();
            if (trace)
            {
                lm.Write("TRACE:  PCUCost.BuildChargeCode()");
            }
            string itemNo     = "";
            string sqlRefresh = "SELECT ITEM_NO, PRICE " +
                                "FROM ITEM " +
                                "JOIN ITEM_VEND IV ON IV.ITEM_ID = ITEM.ITEM_ID " +
                                "JOIN ITEM_VEND_PKG IVP ON IV.ITEM_VEND_ID = IVP.ITEM_VEND_ID " +
                                "WHERE IVP.SEQ_NO = (SELECT MAX(SEQ_NO) FROM ITEM_VEND_PKG WHERE ITEM_VEND_ID = IV.ITEM_VEND_ID) " +
                                "AND IV.SEQ_NO = (SELECT MIN(SEQ_NO) FROM ITEM_VEND  WHERE ITEM_ID = ITEM.ITEM_ID) " +
                                "AND STAT IN(1, 2)";
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = uwmConnectStr; //connect str for HEMM
            Request.CommandType   = CommandType.Text;
            Request.Command       = sqlRefresh;
            if (verbose)
            {
                Console.WriteLine("Updating Previous Value Table: " + HEMMPrice.Keys.Count + " Changes.");
            }
            try
            {
                dsRefresh = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
                foreach (DataRow dr in dsRefresh.Tables[0].Rows)
                {
                    itemNo = dr.ItemArray[0].ToString().Trim();
                    //     if(alias_PatChrg.ContainsKey((object)itemNo))
                    if (aliasList.Contains((object)itemNo))
                    {
                        HEMMPrice.Add(itemNo, dr.ItemArray[1].ToString().Trim());
                    }
                }
            }
            catch (Exception ex)
            {
                lm.Write("MPOUSCharges: BuildHEMMPriceTable:  " + ex.Message);
                errMssg.Notify += "MPOUSCharges: BuildHEMMPriceTable:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 18
0
        private bool GetExistingRecordCount(int REQ_ID, int RI_ID)
        {
            bool       goodToGo = false;
            ArrayList  reqCount = new ArrayList();
            ODMRequest Request  = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "SELECT COUNT(*) FROM [dbo].[hmcmm_ReqItemStatus] WHERE REQ_ITEM_ID = " + RI_ID +
                                    " AND REQ_ID = " + REQ_ID;
            reqCount = ODMDataSetFactory.ExecuteDataReader(Request);
            if (Convert.ToInt32(reqCount[0]) == 0)
            {
                goodToGo = true;
            }

            return(goodToGo);
        }
Esempio n. 19
0
        private void LoadBuyerList(ODMRequest Request, string reqID, string cc)
        {
            ArrayList buyerCode = new ArrayList();

            try
            {
                Request.Command = "SELECT EMAIL FROM [dbo].[uwm_CC_TEAM] WHERE COST_CENTER = " + cc;
                buyerCode       = ODMDataSetFactory.ExecuteDataReader(Request);
                if (!(reqBuyer.ContainsKey(reqID)))
                {
                    reqBuyer.Add(reqID, buyerCode[0].ToString());
                }
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/LoadBuyerList:    " + ex.Message);
            }
        }
Esempio n. 20
0
        private DataSet BuildDataSet(string sql)
        {
            DataSet    dSet    = new DataSet();
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectString;
            Request.CommandType   = CommandType.Text;
            Request.Command       = sql;
            //       string itemNmbr = "";
            try
            {
                dSet = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("DataManager.GetData: " + ex.Message);
            }
            return(dSet);
        }
Esempio n. 21
0
        /* if (status.Equals("On Order"))
         *      GetPONumber(reqItem);
         */

        public void GetPONumber(int reqItemID)
        {
            string     reqno_po = "";
            string     key      = "";
            DataSet    dsPO     = new DataSet();
            ODMRequest Request  = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;   //connectStrHEMM;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "SELECT PO_NO, PO_LINE.ITEM_ID " +
                                    "FROM [h-hemm].dbo.PO_LINE " +
                                    "JOIN [h-hemm].dbo.PO ON PO.PO_ID = PO_LINE.PO_ID " +
                                    "JOIN [h-hemm].dbo.REQ ON REQ.REQ_NO = PO_LINE.REQ_NO " +
                                    "WHERE PO_LINE.REQ_NO = " +
                                    "RTRIM((SELECT REQ_ID FROM [h-hemm].dbo.REQ_ITEM WHERE REQ_ITEM_ID = " + reqItemID + ")) " +
                                    "AND PO_LINE.ITEM_ID = (SELECT ITEM_ID FROM [h-hemm].dbo.REQ_ITEM WHERE REQ_ITEM_ID = " + reqItemID + ")";

            /*
             * "SELECT VPO.PO_NO  " +
             *                 "FROM [h-hemm].dbo.v_hmcmm_Purchase_Orders VPO " +
             *                 "JOIN [h-hemm].dbo.REQ_ITEM ON REQ_ITEM.ITEM_ID = VPO.ITEM_ID WHERE REQ_NO = " +
             *                 "(SELECT REQ_NO FROM [h-hemm].dbo.REQ JOIN [h-hemm].dbo.REQ_ITEM ON REQ.REQ_ID = REQ_ITEM.REQ_ID WHERE REQ_ITEM_ID = " + reqItemID + ") " +
             *                 "AND REQ_ITEM_ID  = " + reqItemID;
             * */

            try
            {
                dsPO = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
                foreach (DataRow drow in dsPO.Tables[0].Rows)
                {
                    if (!reqItemPONO.ContainsKey(reqItemID))
                    {
                        reqItemPONO.Add(reqItemID, drow[0].ToString().Trim());
                    }
                }
                //  if (debug) lm.Write("DataSetManager/GetPONumber: reqNo_PO Count = " + reqNo_PO.Rows.Count);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/GetPONumber:  " + ex.Message);
            }
        }
Esempio n. 22
0
        protected DataSet BuildSQLRefresh()
        {//reads from the PROD. HEMM db. The resulting dataset is used to refill the uwm_BIAdmin.dbo.uwm_IVPItemCost table.
            if (trace)
            {
                lm.Write("TRACE:  PCUCost.BuildSQLRefresh()");
            }
            int    itemID     = 0;
            double cost       = 0.0;
            string itemNo     = "";
            string sqlRefresh = "SELECT  distinct  SI.ITEM_ID, IVP.PRICE, ITEM_NO " +
                                "FROM ITEM_VEND_PKG IVP " +
                                "JOIN ITEM_VEND IV ON IVP.ITEM_VEND_ID = IV.ITEM_VEND_ID " +
                                "JOIN SLOC_ITEM SI ON IVP.ITEM_VEND_ID = SI.ITEM_VEND_ID " +
                                "JOIN ITEM ON ITEM.ITEM_ID = SI.ITEM_ID " +
                                "WHERE IVP.SEQ_NO = (SELECT MAX (SEQ_NO) FROM ITEM_VEND_PKG WHERE ITEM_VEND_ID = SI.ITEM_VEND_ID) " +
                                "AND IV.SEQ_NO = (SELECT MIN(SEQ_NO) FROM ITEM_VEND WHERE ITEM_VEND_ID = IVP.ITEM_VEND_ID) " +
                                "AND LEN(SI.PAT_CHRG_NO) > 0 " +
                                "AND SI.STAT = 1 " +
                                "AND LEFT(SI.PAT_CHRG_NO,5) <> '40411' " +
                                "AND IVP.PRICE > 0 " +
                                "ORDER BY SI.ITEM_ID ";

            ODMRequest Request = new ODMRequest();

            Request.ConnectString = uwmConnectStr;
            Request.CommandType   = CommandType.Text;
            Request.Command       = sqlRefresh;
            if (verbose)
            {
                Console.WriteLine("Updating Previous Value Table: " + patientPrice.Keys.Count + " Changes.");
            }
            try
            {
                dsRefresh = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("PCUCost: BuildSQLRefresh:  " + ex.Message);
                errMssg.Notify += "PCUCost: BuildSQLRefresh:  " + ex.Message + Environment.NewLine;
            }
            return(dsRefresh);
        }
Esempio n. 23
0
        public void InsertTodaysList()
        {
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/InsertTodaysList");
            }
            try
            { /////////THIS IS THE LIST THAT GIVEs THE INITIAL EMAIL RECEIPTS
                ODMRequest Request = new ODMRequest();
                Request.ConnectString = connectStrBIAdmin;
                Request.CommandType   = CommandType.Text;
                foreach (DataRow drow in dsCurrent.Tables[0].Rows) //dsCurrent is loaded from Program.cs calling dsm.LoadTodaysDataSet();
                {                                                  //req_id (3) & req_item_id (1)
                    if (partialCCList[0].ToString().Length == 0 || partialCCList.Contains(drow[9].ToString().Trim()))
                    {                                              //if a list of cc's isn't available then do this for all cc's otherwise do it for a specific cc in the list
                        bool goodToGo = CheckExistingRecordCount(Convert.ToInt32(drow[3]), Convert.ToInt32(drow[1]));
                        if (goodToGo)
                        {   //insert into hmcmm_ReqItemReceipt if it is NOT already in there - goodToGo being true means
                            //CheckExistingRecordCount found 0 records - anything being inserted here needs to have a receipt sent.
                            //the sendReceipt Hashtable collects these req numbers/usernames
                            if (!sendReceipt.ContainsKey(drow[3].ToString().Trim()))
                            {
                                sendReceipt.Add(drow[3].ToString().Trim(), drow[7]); //key=REQ_ID  valu=LOGIN_ID
                                reqDate.Add(drow[3].ToString().Trim(), drow[8]);     //key=REQ_ID  valu=REQ_DATE
                                reqCC.Add(drow[3].ToString().Trim(), drow[9]);       //key=REQ_ID  valu=CC
                                LoadItemDescr(drow[3].ToString().Trim());            //key=REQ_ID  valu= a comma seperated list of all items on a given req
                            }
                            LoadBuyerList(Request, drow[3].ToString().Trim(), drow[9].ToString().Trim());
                            //reqBuyer.Add

                            Request.Command = BuildReqItemInsertQuery(drow);
                            ODMDataSetFactory.ExecuteNonQuery(ref Request);
                        }
                    }
                }
                lm.Write("sendReceipt Count = " + sendReceipt.Count);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/InsertTodaysList:    " + ex.Message);
            }
        }
Esempio n. 24
0
        public string GetManagerEmail(string cc)
        {
            string sql = "SELECT EMAIL +'@uw.edu' " +
                         "FROM [uwm_BIAdmin].[dbo].[HMC_DeptContactList] " +
                         "WHERE CCN = " + cc;   // GetCC(reqNo);

            lm.Write("Cost Center: " + cc);
            ArrayList  email   = new ArrayList();
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = sql;
            email = ODMDataSetFactory.ExecuteDataReader(ref Request);
            if (email.Count == 0)
            {
                email.Add("");
            }
            return(email[0].ToString().Trim());
        }
Esempio n. 25
0
        private string GetCC(string reqNo)
        {
            string sql = "SELECT ACCT_NO " +
                         "FROM CC " +
                         "JOIN REQ ON REQ.CC_ID = CC.CC_ID " +
                         "WHERE REQ_NO = '" + reqNo + "'";
            ArrayList  cc      = new ArrayList();
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrHEMM;
            Request.CommandType   = CommandType.Text;
            Request.Command       = sql;
            cc = ODMDataSetFactory.ExecuteDataReader(ref Request);
            if (cc.Count == 0)
            {
                cc.Add("");
            }

            return(cc[0].ToString().Trim());
        }
Esempio n. 26
0
        private void GetPreviousItemCostList()
        {//gets the previously stored item_id/cost values from the uwm_BIAdmin database
            if (trace)
            {
                lm.Write("TRACE:  UpdatePatCharges.GetPreviousItemCostList()");
            }
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = ConfigData.Get("cnctBIAdmin");
            Request.CommandType   = CommandType.Text;
            Request.Command       = "Select ITEM_ID, COST from uwm_BIAdmin.dbo.uwm_IVPItemCost";
            try
            {
                previousItemCost = ConvertToHashTable(ODMDataSetFactory.ExecuteDataSetBuild(ref Request));
            }
            catch (Exception ex)
            {
                lm.Write("UpdatePatCharges: GetPreviousItemCostList:  " + ex.Message);
                errMssg.Notify += "UpdatePatCharges: GetPreviousItemCostList:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 27
0
        public void LoadTodaysDataSet()
        {
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;   //connectStrHEMM;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "Execute ('" + BuildTodayQuery() + "')";

            if (debug)
            {
                lm.Write("DataSetManager/LoadTodaysDataSet:  " + "BuildTodayQuery() - list of Req Items");   //Request.Command);
            }
            try
            {
                dsCurrent = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/LoadTodaysDataSet:  " + ex.Message);
            }
        }
Esempio n. 28
0
        public void DeleteKilledComplete()
        { //remove KILLED and COMPLETE reqItems from the hmcmm_ReqItemStatus table
            int        item    = 0;
            string     status  = "";
            ODMRequest Request = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = BuildReqItemDeleteQuery();
            try
            {
                GetKilledCompleteCount(); //the "before" count
                ODMDataSetFactory.ExecuteNonQuery(ref Request);
                lm.Write("DataSetManager/DeleteKilledComplete:  ENQ Complete");
                GetKilledCompleteCount(); //the "after" count
            }
            catch (Exception ex)
            {
                lm.Write("DataSetManager/DeleteKilledComplete:  " + ex.Message);
            }
        }
Esempio n. 29
0
        protected void GetCurrentItemCost()
        {//retrieves the current item & cost info from the prod db
            if (trace)
            {
                lm.Write("TRACE:  PCUCost.GetCurrentItemCost()");
            }
            ODMRequest Request = new ODMRequest();

            Request.CommandType   = CommandType.Text;
            Request.ConnectString = uwmConnectStr;
            Request.Command       = sqlSelect;
            try
            {
                itemCost = ODMDataSetFactory.ExecuteDataSetBuild(ref Request);
            }
            catch (Exception ex)
            {
                lm.Write("PCUCost: GetCurrentItemCost:  " + ex.Message);
                errMssg.Notify += "PCUCost: GetCurrentItemCost:  " + ex.Message + Environment.NewLine;
            }
        }
Esempio n. 30
0
        private bool CheckExistingRecordCount(int REQ_ID, int RI_ID)
        {//return TRUE when a req_id/req_item_id combination is NOT in the hmcmm_ReqItemReceipt table
            if (trace)
            {
                lm.Write("TRACE:  DataSetManager/CheckExistingRecordCount");
            }
            bool       goodToGo = false;
            ArrayList  reqCount = new ArrayList();
            ODMRequest Request  = new ODMRequest();

            Request.ConnectString = connectStrBIAdmin;
            Request.CommandType   = CommandType.Text;
            Request.Command       = "SELECT COUNT(*) FROM [dbo].[hmcmm_ReqItemReceipt] WHERE REQ_ITEM_ID = " + RI_ID +
                                    " AND REQ_ID = " + REQ_ID;
            reqCount = ODMDataSetFactory.ExecuteDataReader(Request);
            if (Convert.ToInt32(reqCount[0]) == 0)
            {
                goodToGo = true;
            }

            return(goodToGo);
        }