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);
}
}
}
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);
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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;
}
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;
}
}
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;
}
}
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;
}
}
}
//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;
}
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.");
}
}
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();
}
}
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;
}
}
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;
}
}
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);
}
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);
}
}
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);
}
/* 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);
}
}
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);
}
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);
}
}
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());
}
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());
}
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;
}
}
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);
}
}
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);
}
}
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;
}
}
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);
}