public static DataSet GetSqlResult(string sql, AdsParameterCollection parameters)
{
AdsConnection connection = CreateConnection();
connection.Open();
DataSet ds = new DataSet();
try
{
AdsCommand selectCommand = new AdsCommand(sql, connection);
if (parameters != null)
{
foreach (var parameter in parameters)
{
selectCommand.Parameters.Add(parameter);
}
}
AdsDataAdapter adapter = new AdsDataAdapter(selectCommand);
adapter.Fill(ds, "RESULT");
}
finally
{
connection.Close();
}
return(ds);
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder.Focus();
Application.DoEvents();
// Try to execute the query using current database connection
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
AdsCommand command = (AdsCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
AdsParameter parameter = new AdsParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
AdsDataAdapter adapter = new AdsDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
public void Query(string sDataBase, string sSql)
{
sDataSource = @"\\CLAIMS2\AltaData\" + sDataBase + @"\Data\AltaPoint.add;";
string connectionString = @"Data Source = " + sDataSource + sUser + sType;
connection = new AdsConnection {
ConnectionString = connectionString
};
using (connection)
{
try
{
connection.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
IDataAdapter adapter = new AdsDataAdapter(sSql, connection);
adapter.Fill(ds);
int rows = ds.Tables[0].Rows.Count;
if (rows > 0)
{
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
LabelBox.Text = rows.ToString() + " results counted.";
}
else if (rows == 0)
{
MessageBox.Show("No Results Found");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if (connection.State == ConnectionState.Open)
{
MessageBox.Show("connection open"); connection.Close();
}
}
public static void UpdateAdCategory(int adId, int categoryId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAdCategory(adId, categoryId);
}
}
public static void UpdateAdLevel(int adId, AdLevel adLevel)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAdLevel(adId, (int)adLevel);
}
}
public static AdsDataComponent.AdsDataTable GetAdsByQuery(int recordLimit, int categoryId,
int memberId, decimal maxPrice, string searchTerm, string location, int adType,
int adStatus, int adLevel, int dayRange, bool mustHaveImage
/*, string code , string syn1, string syn2, string syn3, string syn4, string syn5*/)
{
DateTime? minCreatedDate = null;
if (location == null) location = "";
if (dayRange > 0)
minCreatedDate = DateTime.Now.Subtract(TimeSpan.FromDays(dayRange));
// To return no results if the searchTerm is empty remove the following
// check on the searchTerm.
if (searchTerm == null)
searchTerm = "";
using (AdsDataAdapter db = new AdsDataAdapter())
{
AdsDataComponent.AdsDataTable aa = null;
aa = db.GetAllAdsByQuery(recordLimit, categoryId, memberId, maxPrice,
searchTerm, location, adType, adStatus, adLevel, minCreatedDate, mustHaveImage
/*, code , syn1, syn2, syn3, syn4, syn5*/);
return aa;
}
}
public static void UpdateAd(int original_Id, int memberId, string title, string description,
string url, decimal price, string location, bool isRelisting,
string code, string syn1, string syn2, string syn3, string syn4, string syn5)
{
if (url == null)
url = String.Empty;
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAd(original_Id, memberId,
HttpUtility.HtmlEncode(title),
HttpUtility.HtmlEncode(description),
HttpUtility.UrlEncode(url),
HttpUtility.HtmlEncode(code),
HttpUtility.HtmlEncode(syn1),
HttpUtility.HtmlEncode(syn2),
HttpUtility.HtmlEncode(syn3),
HttpUtility.HtmlEncode(syn4),
HttpUtility.HtmlEncode(syn5));
}
}
public int ProcessHL7(string theHL7file, string insurance50list, string insurance2Ulist, string insurance25050, string testmodeNOORCHARTID, string dxforEylea, string dxforLucentis, string alldrugcodes)
{
int failurecode = 100;
AdsConnection con = new AdsConnection("Data source=\\\\brain:6262\\Medidata\\REC\\mwddf.add;User ID=user;Password=password");
con.Open();
// make AdsCommand
AdsCommand cmd; AdsCommand cmd1; AdsCommand cmd3; AdsCommand cmd4; AdsCommand cmd5; AdsCommand cmd6; AdsCommand cmddx; AdsCommand cmdproc; AdsCommand cmdduplicatecharge;
AdsCommand cmdpreviouscase;
AdsCommand cmdndcunitprice; AdsCommand cmdcaseafterinsert; AdsCommand cmdzeroallowedamount; AdsCommand cmdcheckifanycharge; AdsCommand cmdvisitcounterincrement; AdsCommand cmd100visits;
AdsCommand cmdgrabvisitnumber; AdsCommand cmdgetridPQRS; AdsCommand cmdadd57; AdsCommand cmdadd25;
AdsCommand cmdFA50; AdsCommand cmdICG50; AdsCommand cmdextophinit50; AdsCommand cmdextophsub50; AdsCommand cmdBscan50; AdsCommand cmdinj50; AdsCommand cmdUBM50;
AdsCommand cmdFA2U; AdsCommand cmdICG2U; AdsCommand cmdextophinit2U; AdsCommand cmdextophsub2U; AdsCommand cmdBscan2U; AdsCommand cmdinj2U; AdsCommand cmdUBM2U;
AdsCommand cmdlucentis; AdsCommand cmdeylea; AdsCommand cmdtriesence; AdsCommand cmdavastin; AdsCommand cmdC9257;
AdsCommand cmdBscanTC26; AdsCommand cmdUBMTC26; AdsCommand cmdBscanTC26twounits; AdsCommand cmdUBMTC26twounits; AdsCommand cmdgrabentrynumber;
AdsCommand cmdMABscanTC26; AdsCommand cmdMABscanTC26twounits;
AdsCommand cmdMAUBMTC26; AdsCommand cmdMAUBMTC26twounits;
AdsCommand cmdMAVFTC26; AdsCommand cmdMAVFTC26twounits;
AdsCommand cmdMAPACHTC26; AdsCommand cmdMAPACHTC26twounits;
AdsCommand cmdMAFATC26; AdsCommand cmdMAFATC26twounits;
AdsCommand cmdMAICGTC26; AdsCommand cmdMAICGTC26twounits;
AdsCommand cmdMAextphotoTC26; AdsCommand cmdMAextphotoTC26twounits;
AdsCommand cmdMAOCTMACTC26; AdsCommand cmdMAOCTMACTC26twounits;
AdsCommand cmdMAOCTDISCTC26; AdsCommand cmdMAOCTDISCTC26twounits;
AdsCommand cmdMAOCTANTTC26; AdsCommand cmdMAOCTANTTC26twounits;
AdsCommand cmdMA92225TC26; AdsCommand cmdMA92225TC26twounits;
AdsCommand cmdMA92226TC26; AdsCommand cmdMA92226TC26twounits;
AdsCommand cmdWTMW;
DataTable dt = new DataTable();
DataTable dtpreviouscase = new DataTable();
//create Command();
cmdpreviouscase = con.CreateCommand();
cmdWTMW = con.CreateCommand();
cmd = con.CreateCommand();
cmd1 = con.CreateCommand();
cmd3 = con.CreateCommand();
cmd4 = con.CreateCommand();
cmd5 = con.CreateCommand();
cmd6 = con.CreateCommand();
cmddx = con.CreateCommand();
cmdproc = con.CreateCommand();
cmdduplicatecharge = con.CreateCommand();
cmdndcunitprice = con.CreateCommand();
cmdcaseafterinsert = con.CreateCommand();
cmdzeroallowedamount = con.CreateCommand();
cmdcheckifanycharge = con.CreateCommand();
cmdvisitcounterincrement = con.CreateCommand();
cmd100visits = con.CreateCommand();
cmdgrabvisitnumber = con.CreateCommand();
cmdgetridPQRS = con.CreateCommand();
cmdadd57 = con.CreateCommand();
cmdadd25 = con.CreateCommand();
cmdFA50 = con.CreateCommand();
cmdICG50 = con.CreateCommand();
cmdextophinit50 = con.CreateCommand();
cmdextophsub50 = con.CreateCommand();
cmdBscan50 = con.CreateCommand();
cmdinj50 = con.CreateCommand();
cmdUBM50 = con.CreateCommand();
cmdFA2U = con.CreateCommand();
cmdICG2U = con.CreateCommand();
cmdextophinit2U = con.CreateCommand();
cmdextophsub2U = con.CreateCommand();
cmdBscan2U = con.CreateCommand();
cmdinj2U = con.CreateCommand();
cmdUBM2U = con.CreateCommand();
cmdlucentis = con.CreateCommand();
cmdeylea = con.CreateCommand();
cmdavastin = con.CreateCommand();
cmdC9257 = con.CreateCommand();
cmdtriesence = con.CreateCommand();
cmdBscanTC26 = con.CreateCommand();
cmdUBMTC26 = con.CreateCommand();
cmdBscanTC26twounits = con.CreateCommand();
cmdUBMTC26twounits = con.CreateCommand();
cmdgrabentrynumber = con.CreateCommand();
cmdMABscanTC26 = con.CreateCommand();
cmdMABscanTC26twounits = con.CreateCommand();
cmdMAUBMTC26 = con.CreateCommand();
cmdMAUBMTC26twounits = con.CreateCommand();
cmdMAVFTC26 = con.CreateCommand();
cmdMAVFTC26twounits = con.CreateCommand();
cmdMAPACHTC26 = con.CreateCommand();
cmdMAPACHTC26twounits = con.CreateCommand();
cmdMAFATC26 = con.CreateCommand();
cmdMAFATC26twounits = con.CreateCommand();
cmdMAICGTC26 = con.CreateCommand();
cmdMAICGTC26twounits = con.CreateCommand();
cmdMAextphotoTC26 = con.CreateCommand();
cmdMAextphotoTC26twounits = con.CreateCommand();
cmdMAOCTMACTC26 = con.CreateCommand();
cmdMAOCTMACTC26twounits = con.CreateCommand();
cmdMAOCTDISCTC26 = con.CreateCommand();
cmdMAOCTDISCTC26twounits = con.CreateCommand();
cmdMAOCTANTTC26 = con.CreateCommand();
cmdMAOCTANTTC26twounits = con.CreateCommand();
cmdMA92225TC26 = con.CreateCommand();
cmdMA92225TC26twounits = con.CreateCommand();
cmdMA92226TC26 = con.CreateCommand();
cmdMA92226TC26twounits = con.CreateCommand();
//string today = DateTime.Now.ToString("MM/DD/YYYY");
/*/ THINGS TO DO***********************************************************************************************
0. it would be good to know for which patient and which line did the exception occurred in to help make sure that when we turn it back we don't duplicate charges.
1. what if there is no diagnosis? test failure code 101 later
1.1 check null for each variable we are using its values to INSERT------------------DONE
2. write log......
*
* Date From done
* Date To done
* Document Number done
* Description done
* Transaction Type done
* Insurance Category done
* Place of Serivce done
* Type of Service. done
* Visit Number
* Visit Total in Series
* Visit Series ID
* NDC stuff done
2.1. test null situation for each variable in INSERT -------------------DONE
3. MWTRN Documentation Memo 9 field allows documentation. is there a comment field from MDI?
4. Guarantor REsponsible field for MA pts? no effect in native MEdisoft. iow still says Guarantor Responsible. so it is OK!!
*
* NDCUnitPrice: Medisoft produces 0.0000 in MWTRN if NDCUnitPrice is null in MWPRO. ----DONE
//*************************************************************************************************************/
//make a list of all insurance1 codes that need 50 modif for bilateral. DONE
string[] inslisttaking50 = File.ReadAllLines(insurance50list);
List<string> listofinstaking50 = inslisttaking50.ToList();
// make a list of all insurance1 codes that need 2 units for bilateral
string[] inslisttaking2units = File.ReadAllLines(insurance2Ulist);
List<string> listofinstaking2units = inslisttaking2units.ToList();
//make a list of all insurance1 that requires 250.50 as primary dx
string[] inslistrequires25050 = File.ReadAllLines(insurance25050);
List<string> listofinsrequiring25050 = inslistrequires25050.ToList();
// Read each line that contains FT1.
string[] HL7lines = File.ReadAllLines(theHL7file);
//THINGS TO DO: create a list of all cpt codes in HL7
List<string> listofcharges = new List<string>();
int entrynumberMABscan26line = 0; int entrynumberMABscan26linesecondtime = 0;
int entrynumberMAUBM26line = 0; int entrynumberMAUBM26linesecondtime = 0;
int entrynumberMAVF26line = 0; int entrynumberMAVF26linesecondtime = 0;
int entrynumberMAFA26line = 0; int entrynumberMAFA26linesecondtime = 0;
int entrynumberMAICG26line = 0; int entrynumberMAICG26linesecondtime = 0;
int entrynumberMAextphoto26line = 0; int entrynumberMAMAextphoto26linesecondtime = 0;
int entrynumberMAOCTM26line = 0; int entrynumberMAOCTM26linesecondtime = 0;
int entrynumberMAOCTD26line = 0; int entrynumberMAOCTD26linesecondtime = 0;
int entrynumberMAOCTA26line = 0; int entrynumberMAOCTA26linesecondtime = 0;
int entrynumberMA9222526line = 0; int entrynumberMA9222526linesecondtime = 0;
int entrynumberMA9222626line = 0; int entrynumberMA9222626linesecondtime = 0;
int entrynumberMAPACH26line = 0;
int entrynumberAHOBscan26line = 0; int entrynumberAHOUBM26line = 0; int entrynumberAHOBscan26linesecondtime = 0; int entrynumberAHOUBM26linesecondtime = 0;
int entrynumberofvisit = 3; int entrynumberFA = 3; int entrynumberICG = 3; int entrynumberextophinit = 3; int entrynumberextophsubseq = 3; int entrynumberBscan = 3; int entrynumber67028 = 3; int entrynumberUBM = 3;
int entrynumberlucentis = 3; int entrynumbereylea = 3; int entrynumbertriesence = 3; int entrynumberavastin = 3; int entrynumberC9257 = 3;
int firstlinelucentisunits = 0; int firstlineeyleaunits = 0; int firstlinetriesenceunits = 0; int firstlineavastinunits = 0; int firstlineC9257units = 0;
double firstlinelucentisamount = 0; double firstlineeyleaamount = 0; double firstlinetriesenceamount = 0; double firstlineavastinamount = 0; double firstlineC9257amount = 0;
foreach (string eachline in HL7lines)
{
if (eachline.Contains("FT1"))
{
string[] allseg = eachline.Split('|');
string eachcpt = allseg[25].Split('^')[0];
listofcharges.Add(eachcpt);
}
}
//select the last CASE of the pt; select things we need from CASE MWCAS...
string chartid = "";
if (testmodeNOORCHARTID == "NO")//if this is not a test, meaning if this is LIVE
{
chartid = "";
foreach (string eachline in HL7lines)
{
if (eachline.Contains("PID"))
{
string[] allseg = eachline.Split('|');
chartid = allseg[3];//grab chart id from HL7
}
}
if (string.IsNullOrWhiteSpace(chartid)) { failurecode = 104; return failurecode; }
}
else
{
chartid = testmodeNOORCHARTID;//whatever chart ID I want to test. eg. SPRED000
}
AdsParameter cid = new AdsParameter(); cid.Value = chartid; cid.DbType = DbType.String; cid.ParameterName = "cid";
//grab (if exist) the case before this current case to look at the last visit date because sometimes last visit date in current case is blank
cmdpreviouscase.Parameters.Add(cid);
cmdpreviouscase.CommandText = @"select * from MWCAS where [Chart Number] = :cid and [Case Number] =
(select MAX([Case Number]) from MWCAS where [Chart Number] = :cid and [Case Number]< (select MAX([Case Number]) from MWCAS where [Chart Number] = :cid))";
AdsDataAdapter adapreviouscase = new AdsDataAdapter(cmdpreviouscase);
adapreviouscase.Fill(dtpreviouscase);
//string chartid = "SPRED000";
//get things initialized before foreach loop, grab case info.
cmd1.Parameters.Add(cid);
cmd1.CommandText = @"select * from MWCAS where [Chart Number] = :cid and [Case Number] =(select top 1 [Case Number] from MWCAS where [Chart Number] = :cid order by [Case Number] DESC)";
//get information from the last case of the chart id from HL7 (our patient)
AdsDataAdapter ada = new AdsDataAdapter(cmd1);//this shows sql to adv works and sends to gridview defaultview.
ada.Fill(dt);//this shows sql to adv works and sends to gridview defaultview.
if (dt.Rows.Count == 0) { failurecode = 106; return failurecode; };//in case there is no case made for this pt.
string insurance1 = dt.Rows[0].Field<string>("Insurance Carrier #1"); if (!string.IsNullOrWhiteSpace(insurance1)) { insurance1 = insurance1.Trim(); }
int casenumber = dt.Rows[0].Field<int>("Case Number");
string visitseriesid = dt.Rows[0].Field<string>("Visit Series ID");//not used
double visitseriescounter = dt.Rows[0].Field<double>("Visit Series Counter");
DateTime? lastvisitdate = dt.Rows[0].Field<DateTime?>("Last Visit Date");
double authorizednoofvisits = dt.Rows[0].Field<double>("Authorized No of Visits");
string referralsource = dt.Rows[0].Field<string>("Referral Source");
string visitauthorizationno = dt.Rows[0].Field<string>("Visit Authorization No");//grab visit authorization number in preparation for AHO situation for BC
/*NEW CASE
insurance1 null
casenumber integer number always there
visitseriesid null
visitseriescounter 0.0
lastvisitdate null
authorizednoofvisits 0.0 */
int count = 0;
DateTime datefromindt = DateTime.Now; // initialize this outside the foreach loop because need to use datefromindt to update MWCAS at the end outside the foreach loop
//check if there has been no charge entered to this case so far. we use this info to set the Authorized No of Visits in MWCAS to 100 and Visit Series Counter to 1.
/* cmdcheckifanycharge.CommandText = @"select * from MWTRN where [Amount]>0 & and [Transaction Type] <> 'Z' and [Transaction Type] <> 'H' and [Transaction Type] <> 'S' and [Transaction Type] <> 'V' and [Case Number] = " + casenumber + @";";
AdsDataAdapter adacheckifanycharge = new AdsDataAdapter(cmdcheckifanycharge);
DataTable dtcheckifanycharge = new DataTable();
adacheckifanycharge.Fill(dtcheckifanycharge);*/
int countershouldgoup = 0;//stays zero if there is no charges with positive dollar amount (e.g. postop, NC codes)
int onlyonceperHL7 = 0;//counter can go up only once per HL7 message.
int diagnosissetbyfirstline = 0; //zero means not set; 1 means set;
// initializes dx1-12, dd1-12, dtf1-12
string dx1 = "";
string dx2 = "";
string dx3 = "";
string dx4 = "";
string dx5 = "";
string dx6 = "";
string dx7 = "";
string dx8 = "";
string dx9 = "";
string dx10 = "";
string dx11 = "";
string dx12 = "";
string dd1 = "";
string dd2 = "";
string dd3 = "";
string dd4 = "";
string dd5 = "";
string dd6 = "";
string dd7 = "";
string dd8 = "";
string dd9 = "";
string dd10 = "";
string dd11 = "";
string dd12 = "";
int dtf1 = 0;
int dtf2 = 0;
int dtf3 = 0;
int dtf4 = 0;
int dtf5 = 0;
int dtf6 = 0;
int dtf7 = 0;
int dtf8 = 0;
int dtf9 = 0;
int dtf10 = 0;
int dtf11 = 0;
int dtf12 = 0;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (string oneline in HL7lines)
{
if (oneline.Contains("FT1"))
{
count++;
//sample FT1 line
// string oneline = "FT1|1|5c546f16-6681-437f-b3fa-ac45f06fac72||20140101133047|20141029145933||88888^Intermediate - Eye|Intermediate - Eye||1||||||11^^^1|||362.52^Neovascular AMD|WMT^Tang^William^M|WMT^Tang^William^M||||92012^Intermediate - Eye|";
// string oneline = "FT1|1|5c546f16-6681-437f-b3fa-ac45f06fac72||20141029133047|20141029145933||92012^Intermediate - Eye|Intermediate - Eye||1||||||11^^^1|||362.52^Neovascular AMD|WMT^Tang^William^M|WMT^Tang^William^M||||92012^Intermediate - Eye|";
//string oneline = "FT1|4|000e1e80-0674-43a7-ad53-ce6ee7e2f66d||20140919101500|20140921145327||J2778,03mg^Lucentis 0.3mg|Lucentis 0.3mg||3.000||||||11^^^1|||250.50^Diabetes Mellitus With Ophthalmic Manifestations Type II or Unspecified Type Not Stated as Uncontrolled~362.07^Diabetic Macular Edema~362.06^Severe Nonproliferative Diabetic Retinopathy|WMT^Tang^William^M|WMT^Tang^William^M||||J2778,03mg^Lucentis 0.3mg|";
//string oneline = "FT1|1|39c0aebc-d088-4db9-a74c-2f60c2394fdb||20141017140030|20141017155926||67145^Prophylaxis of Retinal Detachment (eg, Retinal Break, Lattice Degeneration) Without Drainage, One or More Sessions; Photocoagulation (Laser or Xenon Arc)|Prophylaxis of Retinal Detachment (eg, Retinal Break, Lattice Degeneration) Without Drainage, One or More Sessions; Photocoagulation (Laser or Xenon Arc)||1||||||11^^^1|||361.31^Round Hole of Retina Without Detachment|WMT^Tang^William^M|WMT^Tang^William^M||||67145^Prophylaxis of Retinal Detachment (eg, Retinal Break, Lattice Degeneration) Without Drainage, One or More Sessions; Photocoagulation (Laser or Xenon Arc)|79^Unrelated Laser/Surgery in Post-Op Period~RT^Right Side|";
// string oneline= "FT1|4|6e4b9e15-a963-4ffa-9b4b-90d223851b9d||20141031130051|20141031150532||J0178^Injection, Eylea|Injection, Eylea||2.000||||||11^^^1|||362.52^Neovascular AMD|WMT^Tang^William^M|WMT^Tang^William^M||||J0178^Injection, Eylea|";
string[] allseg = oneline.Split('|');
//set up dx1 to dx12 with diagnosis codes from HL7 and null values
if (diagnosissetbyfirstline == 0)
{
string dxseg = allseg[19];//segment 19 is where diagnosis codes are
string[] alldxcode = dxseg.Split('~');
int numberofdx = alldxcode.Length;
for (int k = 0; k < numberofdx; k++)//want to keep just the ICD9 codes and not the description
{
string eachdx = alldxcode[k];
string[] eachdxbrokenup = eachdx.Split('^');
alldxcode[k] = eachdxbrokenup[0];
}
List<string> listdxcode = alldxcode.ToList(); //want to populate all 12 diagnosis fields with either ICD9 code or null
while (listdxcode.Count() < 12)
{
listdxcode.Add(null);
}
dx1 = listdxcode[0]; if (string.IsNullOrWhiteSpace(dx1)) { failurecode = 101; return failurecode; }//test this later.....
dx2 = listdxcode[1];
dx3 = listdxcode[2];
dx4 = listdxcode[3];
dx5 = listdxcode[4];
dx6 = listdxcode[5];
dx7 = listdxcode[6];
dx8 = listdxcode[7];
dx9 = listdxcode[8];
dx10 = listdxcode[9];
dx11 = listdxcode[10];
dx12 = listdxcode[11];
//set up dd1 to dd12 (diagnosis description) using diagnosis codes
//setup dtf1 dtf12 (diagnosis true false) depending whether there is diagnosis code
//if dx 1 is diabetic retinopathy or DME, and insurance 1 is UH or TU30 or similar then it is bad news.
// this is being retired for ICD10
//if ((listofinsrequiring25050.Contains(insurance1) &&
// (dx1=="362.01"||dx1=="362.02"||dx1=="362.03"||dx1=="362.04"||dx1=="362.05"||dx1=="362.06"||dx1=="362.07")
// )){
// failurecode = 110; return failurecode;
//}
List<string> listdxdesc = new List<string>();
while (listdxdesc.Count() < 12) { listdxdesc.Add(null); }
List<int> listdxtf = new List<int>();
while (listdxtf.Count() < 12) { listdxtf.Add(0); }
for (int k = 0; k < 12; k++)
{
// cmddx.CommandText = @"select [Description] from MWDIA where [Code 2] ='" + listdxcode[k] + @"' and ([Code 3] like '' or [Code 3] is null)";
cmddx.CommandText = @"select [Description] from MWDIA where [Code 3] ='" + listdxcode[k] + @"'";
string diagnosisexist = (String)cmddx.ExecuteScalar(); //if diagnosis exists in MWDIA, then it would have a Description.
if (listdxcode[k] != null)
{
if (String.IsNullOrEmpty(diagnosisexist))
{
failurecode = 101; return failurecode;//so charges will fail to insert if the diagnosis does not exist in the MWDIA table.
}
}
listdxdesc[k] = diagnosisexist;
if (listdxcode[k] != null) { listdxtf[k] = 1; }
}
dd1 = listdxdesc[0]; if (!string.IsNullOrWhiteSpace(dd1)) { dd1 = dd1.Trim(); }
dd2 = listdxdesc[1]; if (!string.IsNullOrWhiteSpace(dd2)) { dd2 = dd2.Trim(); }
dd3 = listdxdesc[2]; if (!string.IsNullOrWhiteSpace(dd3)) { dd3 = dd3.Trim(); }
dd4 = listdxdesc[3]; if (!string.IsNullOrWhiteSpace(dd4)) { dd4 = dd4.Trim(); }
dd5 = listdxdesc[4]; if (!string.IsNullOrWhiteSpace(dd5)) { dd5 = dd5.Trim(); }
dd6 = listdxdesc[5]; if (!string.IsNullOrWhiteSpace(dd6)) { dd6 = dd6.Trim(); }
dd7 = listdxdesc[6]; if (!string.IsNullOrWhiteSpace(dd7)) { dd7 = dd7.Trim(); }
dd8 = listdxdesc[7]; if (!string.IsNullOrWhiteSpace(dd8)) { dd8 = dd8.Trim(); }
dd9 = listdxdesc[8]; if (!string.IsNullOrWhiteSpace(dd9)) { dd9 = dd9.Trim(); }
dd10 = listdxdesc[9]; if (!string.IsNullOrWhiteSpace(dd10)) { dd10 = dd10.Trim(); }
dd11 = listdxdesc[10]; if (!string.IsNullOrWhiteSpace(dd11)) { dd11 = dd11.Trim(); }
dd12 = listdxdesc[11]; if (!string.IsNullOrWhiteSpace(dd12)) { dd12 = dd12.Trim(); }
dtf1 = listdxtf[0];
dtf2 = listdxtf[1];
dtf3 = listdxtf[2];
dtf4 = listdxtf[3];
dtf5 = listdxtf[4];
dtf6 = listdxtf[5];
dtf7 = listdxtf[6];
dtf8 = listdxtf[7];
dtf9 = listdxtf[8];
dtf10 = listdxtf[9];
dtf11 = listdxtf[10];
dtf12 = listdxtf[11];
// for now, do not allow more than 4 diagnosis. in the future may need to get rid of code below if we wish more than 4 diagnosis.
dtf5 = 0;
dtf6 = 0;
dtf7 = 0;
dtf8 = 0;
dtf9 = 0;
dtf10 = 0;
dtf11 = 0;
dtf12 = 0;
dx5 = "";
dx6 = "";
dx7 = "";
dx8 = "";
dx9 = "";
dx10 = "";
dx11 = "";
dx12 = "";
dd5 = "";
dd6 = "";
dd7 = "";
dd8 = "";
dd9 = "";
dd10 = "";
dd11 = "";
dd12 = "";
//in the future we need to get rid of code above if we wish more than 4 diagnosis
diagnosissetbyfirstline = 1;
}
//set up cpt from HL7 online
string cpt = allseg[25].Split('^')[0];
if (cpt == "J2778,03mg") { cpt = "J2778"; }
if (cpt == "99024") { cpt = "99024PO"; }
//cannot allow injection/Eylea/Lucentis with incorrect dx
if (cpt == "67028" && dx1 == "H35.31") { failurecode = 108; return failurecode; }
string[] lfEylea = File.ReadAllLines(dxforEylea); string[] lfLucentis = File.ReadAllLines(dxforLucentis);
List<string> listdxapprovedforEylea = lfEylea.ToList(); List<string> listdxapprovedforLucentis = lfLucentis.ToList();
if (cpt == "J0178" && !listdxapprovedforEylea.Contains(dx1))
{
failurecode = 108; return failurecode;
}
if (cpt == "J2778" && !listdxapprovedforLucentis.Contains(dx1))
{
failurecode = 108; return failurecode;
}
//set up Date from Date to and Document Number from HL7
string stringdatefrom = allseg[4];
string formatString = "yyyyMMddHHmmss";
datefromindt = DateTime.ParseExact(stringdatefrom, formatString, null);
string datefrom = datefromindt.ToShortDateString();
string documentnumber = DateTime.Now.ToString("yyMMdd0000");
//set up attending provider WMT WMT2, WTMW, WTMW2
string attendingprovider = "WMT";
//use HL7 to pull info from MWPRO
DataTable dtproc = new DataTable();
cmdproc.CommandText = @"select top 1 * from MWPRO where [Code 1] ='" + cpt + @"' order by [Date Modified] DESC";
// cmdproc.CommandText = @"select * from MWPRO where [Code 1] ='" + cpt + @"'";//no good
AdsDataAdapter adaproc = new AdsDataAdapter(cmdproc);
adaproc.Fill(dtproc);
if (dtproc.Rows.Count == 0) { failurecode = 102; return failurecode; }; //deal with this later.
string transtype = dtproc.Rows[0].Field<string>("Type"); if (!string.IsNullOrWhiteSpace(transtype)) { transtype = transtype.Trim(); } if (transtype == null) { failurecode = 103; return failurecode; }
string procdesc = dtproc.Rows[0].Field<string>("Description"); if (!string.IsNullOrWhiteSpace(procdesc)) { procdesc = procdesc.Trim(); }
string typeofservice = dtproc.Rows[0].Field<string>("Type of Service"); if (!string.IsNullOrWhiteSpace(typeofservice)) { typeofservice = typeofservice.Trim(); }
//string placeofservice = dtproc.Rows[0].Field<string>("Default Place Service 1"); if (!string.IsNullOrWhiteSpace(placeofservice)) { placeofservice = placeofservice.Trim(); } if (placeofservice == null) { failurecode = 104; Label1.Text = failurecode.ToString(); return; }
string placeofservice = "11"; //just make this always 11 for now.
string insurancecategory = dtproc.Rows[0].Field<string>("Insurance Category"); if (!string.IsNullOrWhiteSpace(insurancecategory)) { insurancecategory = insurancecategory.Trim(); }
double amountA = dtproc.Rows[0].Field<double>("Amount A");
// int defaultunits = dtproc.Rows[0].Field<int>("Default Units");//this was used in medisoft but since MDI is able to send units, we are not using this
string ndccode = dtproc.Rows[0].Field<string>("National_Drug_Code"); if (!string.IsNullOrWhiteSpace(ndccode)) { ndccode = ndccode.Trim(); }
//Double? ndcunitprice = dtproc.Rows[0].Field<Double?>("NDCUnitPrice"); //error: specificed cast is not valid;
Decimal? ndcunitprice = dtproc.Rows[0].Field<Decimal?>("NDCUnitPrice");//(select [NDCUnitPrice] from MWPRO where [Code 1] ='" + cpt + @"'),
//(select [NDCUnitPrice] from MWPRO where [Code 1] ='" + cpt + @"'UNION ALL SELECT [NDCUnitPrice] from MWPRO where [Code 1] = 'J2503'),
string ndcunitmeasurement = dtproc.Rows[0].Field<string>("NDCUnitofMeasurement"); if (!string.IsNullOrWhiteSpace(ndcunitmeasurement)) { ndcunitmeasurement = ndcunitmeasurement.Trim(); }
//set up amount in MWTRN = amountA from MWPRO * units from HL7 for units from HL7 need to convert string to decimal and then to integer.
string stringunits = allseg[10]; if (string.IsNullOrWhiteSpace(stringunits)) { failurecode = 105; return failurecode; }
int unit = Convert.ToInt32(Convert.ToDouble(stringunits));
Double amount = unit * amountA;
if (amount > 0) { countershouldgoup = 1; }
//set up mod1 to mod4 with modifiers from HL7 if available, otherwise null values
string[] arraymodifiers = allseg[26].Split('~');
for (int k = 0; k < arraymodifiers.Length; k++)// get rid of any description after ^
{
int index = arraymodifiers[k].IndexOf("^");
if (index > 0) { arraymodifiers[k] = arraymodifiers[k].Substring(0, index); }
}
List<string> listmodifiers = arraymodifiers.ToList(); //need list bc want 4 modifiers total
while (listmodifiers.Count() < 4) { listmodifiers.Add(null); }
string mod1 = listmodifiers[0];
if (mod1 == "") { mod1 = null; }
string mod2 = listmodifiers[1];
string mod3 = listmodifiers[2];
string mod4 = listmodifiers[3];
//check to see if the same charge/modif for the same day has already been inserted. if so, something is wrong!
DataTable dtduplicharge = new DataTable();
cmdduplicatecharge.CommandText = @"select * from MWTRN where [Chart Number] ='" + chartid + @"' and [Case Number] ="+casenumber+ @" and [Date From] = '" + datefrom + @"' and [Procedure Code] = '" + cpt + @"' and [Modifier 1] ='" + mod1 + @"' and [Modifier 2] ='" + mod2 + @"'and [Modifier 3] ='" + mod3 + @"'and [Modifier 4] ='" + mod4 + @"'";
AdsDataAdapter adaduplicharge = new AdsDataAdapter(cmdduplicatecharge);
adaduplicharge.Fill(dtduplicharge);
string[] drugcodes = File.ReadAllLines(alldrugcodes);
if (dtduplicharge.Rows.Count > 0)
{
if (!drugcodes.Contains(cpt))
{ failurecode = 111; return failurecode; };
}
//cpt should not be a follow up code if pt has never been here or was here > 3 years.
//I moved this from AFTER INSERT to BEFORE INSERT because don't we want to catch this BEFORE INSERT? 9/23/15
DateTime? lastvisitdatepreviouscase;
if (lastvisitdate != null)
{
DateTime lvd = (DateTime)lastvisitdate;
if ((cpt == "99214" || cpt == "99213" || cpt == "99212" || cpt == "99211" || cpt == "99215" || cpt == "92012" || cpt == "92014") &&
((datefromindt - lvd).TotalDays > (365 * 3)))
{
failurecode = 109; return failurecode;
}
}
if ((lastvisitdate == null) && (dtpreviouscase.Rows.Count == 0) && (cpt == "99214" || cpt == "99213" || cpt == "99212" || cpt == "99211" || cpt == "99215" || cpt == "92012" || cpt == "92014"))
{
failurecode = 109; return failurecode;
}
if ((lastvisitdate == null) && (dtpreviouscase.Rows.Count > 0) && (cpt == "99214" || cpt == "99213" || cpt == "99212" || cpt == "99211" || cpt == "99215" || cpt == "92012" || cpt == "92014"))
{
lastvisitdatepreviouscase = dtpreviouscase.Rows[0].Field<DateTime?>("Last Visit Date");
if (lastvisitdatepreviouscase == null)
{
failurecode = 109; return failurecode;
}
else
{
DateTime lvdpc = (DateTime)lastvisitdatepreviouscase;
if ((datefromindt - lvdpc).TotalDays > (365 * 3))
{
failurecode = 109; return failurecode;
}
}
}
//this did not work because the allowed amount from MWALL is Double type and can be null sometimes. so I figured we need to cast it into Double? type but INSERT statement does not allow null for Double?
//so finally I just wrote a sub statement to select the allowed amount based on the CPT code.
//grab the allowed amount based on the procedure code from HL7 and insurance carrier #1 from case
// instead of this I wrote the SQL to select allowed amount directly in the SQL statement
cmd5.CommandText = @"select top 1 [Amount] from MWALL where [Procedure] = '" + cpt + @"' and [Insurance Code] = '" + insurance1 + @"' order by [Date Modified] DESC ";
Double? allowamt = (Double?)cmd5.ExecuteScalar(); //this works here but INSERT does not permit null for Double?
// if (allowamt != null) { allowamt = Math.Round(allowamt, 2); }
//set insurance 1,2, 3 paid, amount paid, responsible depending on insurance carrier 1,2,3 in case
int ins1pd = 1;
if (dt.Rows[0]["Insurance Carrier #1"] != null) { if (dt.Rows[0]["Insurance Carrier #1"].ToString() != "") { ins1pd = 0; } }
AdsParameter ins1paid = new AdsParameter();
ins1paid.Value = ins1pd; ins1paid.DbType = DbType.Int32; ins1paid.ParameterName = "ins1paid"; cmd.Parameters.Add(ins1paid);
int ins2pd = 1;
if (dt.Rows[0]["Insurance Carrier #2"] != null) { if (dt.Rows[0]["Insurance Carrier #2"].ToString() != "") { ins2pd = 0; } }
AdsParameter ins2paid = new AdsParameter();
ins2paid.Value = ins2pd; ins2paid.DbType = DbType.Int32; ins2paid.ParameterName = "ins2paid"; cmd.Parameters.Add(ins2paid);
int ins3pd = 1;
if (dt.Rows[0]["Insurance Carrier #3"] != null) { if (dt.Rows[0]["Insurance Carrier #3"].ToString() != "") { ins3pd = 0; } }
AdsParameter ins3paid = new AdsParameter();
ins3paid.Value = ins3pd; ins3paid.DbType = DbType.Int32; ins3paid.ParameterName = "ins3paid"; cmd.Parameters.Add(ins3paid);
double gamtpd = 0;
AdsParameter gamtpaid = new AdsParameter();
gamtpaid.Value = gamtpd; gamtpaid.DbType = DbType.Double; gamtpaid.ParameterName = "gamtpaid"; cmd.Parameters.Add(gamtpaid);
double ins1amtpd = 0;
AdsParameter ins1amtpaid = new AdsParameter();
ins1amtpaid.Value = ins1amtpd; ins1amtpaid.DbType = DbType.Double; ins1amtpaid.ParameterName = "ins1amtpaid"; cmd.Parameters.Add(ins1amtpaid);
double ins2amtpd = 0;
AdsParameter ins2amtpaid = new AdsParameter();
ins2amtpaid.Value = ins2amtpd; ins2amtpaid.DbType = DbType.Double; ins2amtpaid.ParameterName = "ins2amtpaid"; cmd.Parameters.Add(ins2amtpaid);
double ins3amtpd = 0;
AdsParameter ins3amtpaid = new AdsParameter();
ins3amtpaid.Value = ins3amtpd; ins3amtpaid.DbType = DbType.Double; ins3amtpaid.ParameterName = "ins3amtpaid"; cmd.Parameters.Add(ins3amtpaid);
int ins1res = 0;
//if (dt.Rows[0]["Insurance Carrier #1"] != null) {ins1 = dt.Rows[0]["Insurance Carrier #1"].ToString();}
if (dt.Rows[0]["Insurance Carrier #1"] != null) { if (dt.Rows[0]["Insurance Carrier #1"].ToString() != "") { ins1res = 1; } }
AdsParameter ins1resp = new AdsParameter();
ins1resp.Value = ins1res; ins1resp.DbType = DbType.Int32; ins1resp.ParameterName = "ins1resp"; cmd.Parameters.Add(ins1resp);
int ins2res = 0;
if (dt.Rows[0]["Insurance Carrier #2"] != null) { if (dt.Rows[0]["Insurance Carrier #2"].ToString() != "") { ins2res = 1; } }
AdsParameter ins2resp = new AdsParameter();
ins2resp.Value = ins2res; ins2resp.DbType = DbType.Int32; ins2resp.ParameterName = "ins2resp"; cmd.Parameters.Add(ins2resp);
int ins3res = 0;
if (dt.Rows[0]["Insurance Carrier #3"] != null) { if (dt.Rows[0]["Insurance Carrier #3"].ToString() != "") { ins3res = 1; } }
AdsParameter ins3resp = new AdsParameter();
ins3resp.Value = ins3res; ins3resp.DbType = DbType.Int32; ins3resp.ParameterName = "ins3resp"; cmd.Parameters.Add(ins3resp);
// set Date Created and Date Modified
AdsParameter sdate = new AdsParameter();
AdsParameter datemodif = new AdsParameter();
//string startdate = StartDate.Text.ToString();
string startdate = DateTime.Now.ToShortDateString();
DateTime startfecha = Convert.ToDateTime(startdate);
sdate.Value = startfecha; sdate.DbType = DbType.Date; sdate.ParameterName = "sdate";
string dmf0 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DateTime dmf = Convert.ToDateTime(dmf0);
datemodif.Value = dmf; datemodif.DbType = DbType.DateTime; datemodif.ParameterName = "datemodif";
cmd.Parameters.Add(sdate);
cmd.Parameters.Add(datemodif);
//!!!!!!!!!!!!!!!!!!!!!!!!INSERT statement!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
cmd.CommandText =
@"
insert into MWTRN (
[Chart Number],
[Case Number],
[Claim Number],
[Date From],
[Date To],
[Document Number],
[Description],
[Attending Provider],
[Procedure Code],
[Transaction Type],
[Insurance Category],
[Modifier 1],
[Modifier 2],
[Modifier 3],
[Modifier 4],
[Place of Service],
[Type of Service],
[Visit Number],
[Visit Total in Series],
[Visit Series ID],
Units,
Minutes,
Amount,
[Price Indicator],
[Diagnosis 1],
[Diagnosis 2],
[Diagnosis 3],
[Diagnosis 4],
Diagnosis_5,
Diagnosis_6,
Diagnosis_7,
Diagnosis_8,
Diagnosis_9,
Diagnosis_10,
Diagnosis_11,
Diagnosis_12,
[Diagnosis Code 1],
[Diagnosis Code 2],
[Diagnosis Code 3],
[Diagnosis Code 4],
Diagnosis_Code5,
Diagnosis_Code6,
Diagnosis_Code7,
Diagnosis_Code8,
Diagnosis_Code9,
Diagnosis_Code10,
Diagnosis_Code11,
Diagnosis_Code12,
[Diagnosis 1 Description],
[Diagnosis 2 Description],
[Diagnosis 3 Description],
[Diagnosis 4 Description],
Diagnosis_5_Description,
Diagnosis_6_Description,
Diagnosis_7_Description,
Diagnosis_8_Description,
Diagnosis_9_Description,
Diagnosis_10_Description,
Diagnosis_11_Description,
Diagnosis_12_Description,
[Principal_Procedure],
[Claim Item Rejected],
[Accept Assignment 1],
[Accept Assignment 2],
[Accept Assignment 3],
[Date Created],
[Billed to Insured 1],
[Billed to Insured 2],
[Billed to Insured 3],
[Guarantor Paid],
[Insurance 1 Paid],
[Insurance 2 Paid],
[Insurance 3 Paid],
[Guarantor Amount Paid],
[Insurance 1 Amount Paid],
[Insurance 2 Amount Paid],
[Insurance 3 Amount Paid],
[Guarantor Responsible],
[Insurance 1 Responsible],
[Insurance 2 Responsible],
[Insurance 3 Responsible],
[Documentation Type],
[Attorney Paid],
[Attorney Amount Paid],
[Attorney Responsible],
[Adjustment Amount],
[User Code],
[Allowed Amount],
[CC Entry Number],
[Deposit ID],
[Date Modified],
[Procedure Description],
[Statement Number],
[Quantity],
[Copayment_Required],
[Copayment_Paid],
[Copayment_Expected_Amount],
[Copayment_Amount_Paid],
Primary_UB_Not_Covered,
Secondary_UB_Not_Covered,
Tertiary_UB_Not_Covered,
[NDCCode],
[NDCUnitPrice],
[NDCUnitCount],
[NDCUnitMeasurement],
[Purch Svc Amt]
)
values
(
'" + chartid + @"',
" + casenumber + @",
0,
'" + datefrom + @"',
'" + datefrom + @"',
'" + documentnumber + @"',
'" + procdesc + @"',
'" + attendingprovider + @"',
'" + cpt + @"',
'" + transtype + @"',
'" + insurancecategory + @"',
'" + mod1 + @"',
'" + mod2 + @"',
'" + mod3 + @"',
'" + mod4 + @"',
'" + placeofservice + @"',
'" + typeofservice + @"',
" + visitseriescounter + @",
" + authorizednoofvisits + @",
'A',
" + unit + @",
0,
" + amount + @",
'A',
" + dtf1 + @",
" + dtf2 + @",
" + dtf3 + @",
" + dtf4 + @",
" + dtf5 + @",
" + dtf6 + @",
" + dtf7 + @",
" + dtf8 + @",
" + dtf9 + @",
" + dtf10 + @",
" + dtf11 + @",
" + dtf12 + @",
'" + dx1 + @"',
'" + dx2 + @"',
'" + dx3 + @"',
'" + dx4 + @"',
'" + dx5 + @"',
'" + dx6 + @"',
'" + dx7 + @"',
'" + dx8 + @"',
'" + dx9 + @"',
'" + dx10 + @"',
'" + dx11 + @"',
'" + dx12 + @"',
'" + dd1 + @"',
'" + dd2 + @"',
'" + dd3 + @"',
'" + dd4 + @"',
'" + dd5 + @"',
'" + dd6 + @"',
'" + dd7 + @"',
'" + dd8 + @"',
'" + dd9 + @"',
'" + dd10 + @"',
'" + dd11 + @"',
'" + dd12 + @"',
0,
0,
1,
1,
1,
:sdate,
0,
0,
0,
0,
:ins1paid,
:ins2paid,
:ins3paid,
:gamtpaid,
:ins1amtpaid,
:ins2amtpaid,
:ins3amtpaid,
1,
:ins1resp,
:ins2resp,
:ins3resp,
'T',
0,
0,
0,
0,
'SC',
((select top 1 [Amount] from MWALL where [Procedure] = '" + cpt + @"' and [Insurance Code] = '" + insurance1 + @"' order by [Date Modified] DESC)*(" + unit + @")),
0,
0,
:datemodif,
'" + procdesc + @"',
0,
0,
0,
0,
0,
0,
0,
0,
0,
'" + ndccode + @"',
(select [NDCUnitPrice] from MWPRO where [Code 1] ='" + cpt + @"'),
0,
'" + ndcunitmeasurement + @"',
0
);";
cmd.ExecuteNonQuery();
Thread.Sleep(2000);
cmdgrabentrynumber.CommandText = @"select top 1 [Entry Number] from MWTRN where [Case Number] = " + casenumber + @" order by [Entry Number] DESC;";
int entrynumber = (int)cmdgrabentrynumber.ExecuteScalar();//grab entry number of the charge just inserted
//////////////////////////////////////////DO ALL THESE FOR EACH CHARGE LINE///////////////////////////////////////////////////////////////////////////////////////
//update last visit date, visit counter, author no of visit etc.
//mimick Medisoft v19 sp1 exactly; if NDCUnitPrice is null, it seems to assign zero to it.
//mimick Medisoft v19 sp1 exactly; if allowed amount is null in MWALL, it seems to assign zero to the allowed amount in MWTRN
if (ndcunitprice == null)//if the ndc unit price in MWPRO is null where the cpt is the cpt from the HL7 line, then
{
ndcunitprice = 0;
cmdndcunitprice.CommandText = @"update MWTRN set [NDCUnitPrice] = 0 where [Entry Number] =" + entrynumber + @";";
cmdndcunitprice.ExecuteNonQuery();
}
//mimick Medisoft v19 sp1 exactly; if allowed amount is null in MWALL, it seems to assign zero to the allowed amount in MWTRN
// set [Authorized No of Visits] from MWCAS to 100 if it is zero.
if (authorizednoofvisits == 0)// based on the chart id from HL7, look up the last case, and look up authorized number of visits.
{
cmd100visits.CommandText = @"UPDATE MWCAS SET [Authorized No of Visits] = 100 where [Case Number] =" + casenumber + @"; update MWTRN set [Visit Total in Series]=100 where [Entry Number] =" + entrynumber + @";";
cmd100visits.ExecuteNonQuery();
}
// update [Last Visit Date] in MWCAS with lastvisitdate if [Last Visit Date] is null or < lastvisit. increment viist counter
if (lastvisitdate == null) //hopefully this will use the Date From from the last FT1 line. hopefully all FT1 lines have the same Date From.
{
cmd6.CommandText = @"update MWCAS set [Last Visit Date] = '" + datefromindt.ToString("MM/dd/yyyy") + @"' where [Case Number] =" + casenumber + @";";
cmd6.ExecuteNonQuery();
// increment by 1 [Visit Series Counter] if at least one of the charges in HL7 has a positive amount.
// inside here, either there is no previous charge (lastvisitdate ==null) or datefromindt > lastvisitdate---> in either case, we increment the visit counter.
if (countershouldgoup == 1 & onlyonceperHL7 == 0)//countershouldgoup if at least one charge has positive amount.
{
cmdvisitcounterincrement.CommandText = @"update MWCAS set [Visit Series Counter] = [Visit Series Counter]+1 where [Case Number] =" + casenumber + @";";
cmdvisitcounterincrement.ExecuteNonQuery();
onlyonceperHL7 = 1; //counter cannot increment more than once per HL7 message
}
}
else if (lastvisitdate.HasValue)
{
if (datefromindt.Date > lastvisitdate.Value.Date)
{
cmd6.CommandText = @"update MWCAS set [Last Visit Date] = '" + datefromindt.ToString("MM/dd/yyyy") + @"' where [Case Number] =" + casenumber + @";";
cmd6.ExecuteNonQuery();
}
// increment by 1 [Visit Series Counter] if at least one of the charges in HL7 has a positive amount.
// inside here, either there is no previous charge (lastvisitdate ==null) or datefromindt > lastvisitdate---> in either case, we increment the visit counter.
if (datefromindt.Date != lastvisitdate.Value.Date & countershouldgoup == 1 & onlyonceperHL7 == 0)//countershouldgoup if at least one charge has positive amount.
{
cmdvisitcounterincrement.CommandText = @"update MWCAS set [Visit Series Counter] = [Visit Series Counter]+1 where [Case Number] =" + casenumber + @";";
cmdvisitcounterincrement.ExecuteNonQuery();
onlyonceperHL7 = 1; //counter cannot increment more than once per HL7 message
}
}
//always grab visit number from case
cmdgrabvisitnumber.CommandText = @"update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumber + @";";
cmdgrabvisitnumber.ExecuteNonQuery();
//if there is already a visit charge for this date and this is the second time, there is something wrong. (this is just a safety mechanism I am building)
bool cptisvisit = (
cpt == "99215" |
cpt == "99214" |
cpt == "99213" |
cpt == "99212" |
cpt == "99211" |
cpt == "92014" |
cpt == "92012" |
cpt == "99202" |
cpt == "99203" |
cpt == "99204"
);
if (entrynumberofvisit != 3 & cptisvisit) //if there was already a visit charge for this date (previous loop in later code would have assigned entrynumberofvisit)
//and this is the second time, there is something wrong.
{
failurecode = 107; return failurecode;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (!string.IsNullOrEmpty(insurance1))//if the patient has insurance
{
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (allowamt == null)//based on the cpt, looks up what is the allowed amount depending on insurance1 (so this is dependant on insurance1)
{
cmdzeroallowedamount.CommandText = @"update MWTRN set [Allowed Amount] = 0 where [Entry Number] =" + entrynumber + @";";
cmdzeroallowedamount.ExecuteNonQuery();
}
if (insurance1 == "MA00")
{
if (cpt == "76512")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);//insert one more line of the same thing, this will be the 26 line.
if (entrynumberBscan == 3)//meaning Bscan only has one line bc only one eye was done
{
entrynumberMABscan26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMABscanTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMABscan26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMABscan26line + @";
";
cmdMABscanTC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another Bscan line already (meaning this is likely an LT line bc both eyes were done)
{
entrynumberMABscan26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMABscanTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMABscan26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMABscan26linesecondtime + @";
";
cmdMABscanTC26.ExecuteNonQuery();
}
}
if (cpt == "76513")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberUBM == 3)//meaning UBM only has one line bc only one eye was done
{
entrynumberMAUBM26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAUBMTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAUBM26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAUBM26line + @";
";
cmdMAUBMTC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another UBM line already.
{
entrynumberMAUBM26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAUBMTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAUBM26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAUBM26linesecondtime + @";
";
cmdMAUBMTC26.ExecuteNonQuery();
}
}
if (cpt == "92083")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
//VF can only be one line.
entrynumberMAVF26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAVFTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAVF26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAVF26line + @";
";
cmdMAVFTC26.ExecuteNonQuery();
}
if (cpt == "76514")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
//Pachymetry can only be one line
entrynumberMAPACH26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAPACHTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAPACH26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAPACH26line + @";
";
cmdMAPACHTC26.ExecuteNonQuery();
}
if (cpt == "92235")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberFA == 3)//meaning FA only has one line bc only one eye was done
{
entrynumberMAFA26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAFATC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAFA26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAFA26line + @";
";
cmdMAFATC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another FA line already.
{
entrynumberMAFA26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAFATC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAFA26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAFA26linesecondtime + @";
";
cmdMAFATC26.ExecuteNonQuery();
}
}
if (cpt == "92240")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberICG == 3)//meaning ICG only has one line bc only one eye was done
{
entrynumberMAICG26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAICGTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAICG26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAICG26line + @";
";
cmdMAICGTC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another ICG line already.
{
entrynumberMAICG26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAICGTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAICG26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAICG26linesecondtime + @";
";
cmdMAICGTC26.ExecuteNonQuery();
}
}
if (cpt == "92285")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000); //external photo can only be one line
entrynumberMAextphoto26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAextphotoTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAextphoto26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAextphoto26line + @";
";
cmdMAextphotoTC26.ExecuteNonQuery();
}
if (cpt == "92134")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000); //OCT M can only be one line
entrynumberMAOCTM26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAOCTMACTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAOCTM26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAOCTM26line + @";
";
cmdMAOCTMACTC26.ExecuteNonQuery();
}
if (cpt == "92133")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
entrynumberMAOCTD26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAOCTMACTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAOCTD26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAOCTD26line + @";
";
cmdMAOCTMACTC26.ExecuteNonQuery();
}
if (cpt == "92132")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
entrynumberMAOCTA26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMAOCTANTTC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMAOCTA26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMAOCTA26line + @";
";
cmdMAOCTANTTC26.ExecuteNonQuery();
}
if (cpt == "92225")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberextophinit == 3)//meaning 92225 only has one line bc only one eye was done
{
entrynumberMA9222526line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMA92225TC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMA9222526line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMA9222526line + @";
";
cmdMA92225TC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another ICG line already.
{
entrynumberMA9222526linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMA92225TC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMA9222526linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMA9222526linesecondtime + @";
";
cmdMA92225TC26.ExecuteNonQuery();
}
}
if (cpt == "92226")
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberextophsubseq == 3)//meaning 92226 only has one line bc only one eye was done
{
entrynumberMA9222626line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMA92226TC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMA9222626line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMA9222626line + @";
";
cmdMA92226TC26.ExecuteNonQuery();
}
else//meaning this is second time this is coming around bc ther was another 92226 line already.
{
entrynumberMA9222626linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdMA92226TC26.CommandText = @" update MWTRN set [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '26' where [Entry Number] = " + entrynumberMA9222626linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberMA9222626linesecondtime + @";
";
cmdMA92226TC26.ExecuteNonQuery();
}
}
}
/////another project is AHO pt we don't need referralsource == "AHO01", we also use referral field contains AHO or something like that. --> DONE!!
////////need a bracket for self pay patients. insurance1 is null or empty? still can process certain things for each line for pt with no insurance ******DONE!!
// if it is a BCBS and MWAHO referral required patient
//UBM? PACH?
if ((insurance1.StartsWith("BC") || insurance1 == "AN00") && (referralsource == "AHO01" || visitauthorizationno == "AHO"))//for all cpt codes
{
cmdWTMW.CommandText = @"
update MWTRN set [Attending Provider] = 'WTMW' where [Entry Number] = " + entrynumber + @";
";
cmdWTMW.ExecuteNonQuery();
}
if (cpt == "76512" && (insurance1.StartsWith("BC") || insurance1 == "AN00") && (referralsource == "AHO01" || visitauthorizationno == "AHO"))
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberBscan == 3)//meaning that there is only one line of Bscan in HL7
{
entrynumberAHOBscan26line = (int)cmdgrabentrynumber.ExecuteScalar();//grab the entry number just entered.
cmdBscanTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WTMW2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WTMW', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOBscan26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOBscan26line + @";
";
cmdBscanTC26.ExecuteNonQuery();
}
else //meaning there are 2 lines of Bsacn in HL7
{
entrynumberAHOBscan26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdBscanTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WTMW2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WTMW', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOBscan26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOBscan26linesecondtime + @";
";
cmdBscanTC26.ExecuteNonQuery();
}
}
if (cpt == "76512" && (insurance1.StartsWith("BC") || insurance1 == "AN00") && (referralsource != "AHO01" && visitauthorizationno != "AHO"))//need to test if referralsource is null, need to test if visitauthorizationno is null
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);//create another line of the same thing, this line will be the 26 line.
if (entrynumberBscan == 3)//if this is the first Bscan line so far in all the HL7 lines read. if there were another Bscan line (likely RT) already processed, then entrynumberBscan would be the entry number of that previous Bscan line
{
entrynumberAHOBscan26line = (int)cmdgrabentrynumber.ExecuteScalar();//grab the entry number of the MWTRN charge line inserted by cmd (two code lines prior to this line)
cmdBscanTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WMT2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WMT', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOBscan26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOBscan26line + @";
";
cmdBscanTC26.ExecuteNonQuery();
}
else //meaning there were another Bscan line (likely RT) already processed. so the current HL7 line is likely the LT line
{
entrynumberAHOBscan26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdBscanTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WMT2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WMT', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOBscan26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOBscan26linesecondtime + @";
";
cmdBscanTC26.ExecuteNonQuery();// at this point, there would be 4 lines: 76512-TC (entrynumberBscan), 76512-26 (entrynuberAHOBscan26line), 76512-TC (entrynumber), 76512-26 (entrynumberAHOBscan26linesecondtime)
}
}
if (cpt == "76513" && (insurance1.StartsWith("BC") || insurance1 == "AN00") && (referralsource == "AHO01" || visitauthorizationno == "AHO"))
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberUBM == 3)
{
entrynumberAHOUBM26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdUBMTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WTMW2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WTMW', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOUBM26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOUBM26line + @";
";
cmdUBMTC26.ExecuteNonQuery();
}
else
{
entrynumberAHOUBM26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdUBMTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WTMW2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WTMW', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOUBM26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOUBM26linesecondtime + @";
";
cmdUBMTC26.ExecuteNonQuery();
}
}
if (cpt == "76513" && (insurance1.StartsWith("BC") || insurance1 == "AN00") && (referralsource != "AHO01" && visitauthorizationno != "AHO"))
{
cmd.ExecuteNonQuery(); Thread.Sleep(2000);
if (entrynumberUBM == 3)
{
entrynumberAHOUBM26line = (int)cmdgrabentrynumber.ExecuteScalar();
cmdUBMTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WMT2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WMT', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOUBM26line + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOUBM26line + @";
";
cmdUBMTC26.ExecuteNonQuery();
}
else
{
entrynumberAHOUBM26linesecondtime = (int)cmdgrabentrynumber.ExecuteScalar();
cmdUBMTC26.CommandText = @"update MWTRN set [Attending Provider]= 'WMT2', [Modifier 1] = 'TC' where [Entry Number] = " + entrynumber + @";
update MWTRN set [Attending Provider] = 'WMT', [Modifier 1] = '26' where [Entry Number] = " + entrynumberAHOUBM26linesecondtime + @";
update MWTRN set [Visit Number] = (select [Visit Series Counter] from MWCAS where [Case Number] =" + casenumber + @") where [Entry Number] =" + entrynumberAHOUBM26linesecondtime + @";
";
cmdUBMTC26.ExecuteNonQuery();
}
}
//if it is not MC AND there is PQRS codes, get rid of them.
if (insurance1 != "MC00" & (cpt == "2021F" | cpt == "G8397" | cpt == "5010F" | cpt == "2019F"))
{
cmdgetridPQRS.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";";
cmdgetridPQRS.ExecuteNonQuery();
}
//first grab the entry number of the visit charge as for each loop is iterating over each charge, in preparaton for add 57 or 25 modifiers.
if (cptisvisit) { entrynumberofvisit = entrynumber; }
// add 50 modif DB amount or 2units DB amounts!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//FA50 if there is already a previous line in HL7 with FA, and this line is also FA, we need to decide to do 50 modifier or 2 units or leave it alone.
//we know that there was a previous line with FA because entrynumberFA has a number that is not 3.
if (
(entrynumberFA != 3) & (cpt == "92235") //if there was a previous FA charge in HL7 and the current charge is also HL7
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdFA50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberFA + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberFA + @";
";
cmdFA50.ExecuteNonQuery();
}
}
//ICG 50
if (
(entrynumberICG != 3) & (cpt == "92240") //if there was a previous ICG charge in HL7 and the current charge is also ICG
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdICG50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberICG + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberICG + @";
";
cmdICG50.ExecuteNonQuery();
}
}
//extoph initi 50
if (
(entrynumberextophinit != 3) & (cpt == "92225") //if there was a previous ICG charge in HL7 and the current charge is also ICG
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdextophinit50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberextophinit + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberextophinit + @";
";
cmdextophinit50.ExecuteNonQuery();
}
}
//ext oph subseq 50
if (
(entrynumberextophsubseq != 3) & (cpt == "92226") //if there was a previous 92226 charge in HL7 and the current charge is also 92226
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdextophsub50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberextophsubseq + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberextophsubseq + @";
";
cmdextophsub50.ExecuteNonQuery();
}
}
//Bscan 50
if (
(entrynumberBscan != 3) & (cpt == "76512") //if there was a previous Bscan charge in HL7 and the current charge is also Bscan
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdBscan50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberBscan + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberBscan + @";
";
cmdBscan50.ExecuteNonQuery();
}
}
//injection 50
if (
(entrynumber67028 != 3) & (cpt == "67028") //if there was a previous ICG charge in HL7 and the current charge is also ICG
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdinj50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumber67028 + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber67028 + @";
";
cmdinj50.ExecuteNonQuery();
}
}
//UBM 50
if (
(entrynumberUBM != 3) & (cpt == "76513") //if there was a previous ICG charge in HL7 and the current charge is also ICG
)
{
if (
listofinstaking50.Contains(insurance1)
)
{
cmdUBM50.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Modifier 1] = '50' where [Entry Number] =" + entrynumberUBM + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberUBM + @";
";
cmdUBM50.ExecuteNonQuery();
}
}
// FA 2 units double amount not MA00
if (
(entrynumberFA != 3) && (cpt == "92235") && (insurance1 != "MA00")
)
{
if (listofinstaking2units.Contains(insurance1))
{
cmdFA2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberFA + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberFA + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberFA + @";
";
cmdFA2U.ExecuteNonQuery();
}
}
// FA 2 units double amount MASSHEALTH
if (
(entrynumberFA != 3) && (cpt == "92235") && (insurance1 == "MA00")
)
{
cmdMAFATC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberFA + @";
delete from MWTRN where [Entry Number] = " + entrynumberMAFA26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMAFA26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMAFA26linesecondtime + @";
";
cmdMAFATC26twounits.ExecuteNonQuery();
}
//ICG 2 units double amount not MA00
if (
(entrynumberICG != 3) && (cpt == "92240") && (insurance1 != "MA00")
)
{
if (listofinstaking2units.Contains(insurance1))
{
cmdICG2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberICG + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberICG + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberICG + @";
";
cmdICG2U.ExecuteNonQuery();
}
}
//ICG 2 units double amount MA00
if (
(entrynumberICG != 3) && (cpt == "92240") && (insurance1 == "MA00")
)
{
cmdMAICGTC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberICG + @";
delete from MWTRN where [Entry Number] = " + entrynumberMAICG26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMAICG26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMAICG26linesecondtime + @";
";
cmdMAICGTC26twounits.ExecuteNonQuery();
}
//ext oph init 2 units double amount not MA00
if (
(entrynumberextophinit != 3) && (cpt == "92225") && (insurance1 != "MA00")
)
{
if (listofinstaking2units.Contains(insurance1))
{
cmdextophinit2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberextophinit + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberextophinit + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberextophinit + @";
";
cmdextophinit2U.ExecuteNonQuery();
}
}
//ext oph init 2 units double amount MA00
if (
(entrynumberextophinit != 3) && (cpt == "92225") && (insurance1 == "MA00")
)
{
cmdMA92225TC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberextophinit + @";
delete from MWTRN where [Entry Number] = " + entrynumberMA9222526line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMA9222526linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMA9222526linesecondtime + @";
";
cmdMA92225TC26twounits.ExecuteNonQuery();
}
//ext oph sub 2 units doudble amount not MA00
if (
(entrynumberextophsubseq != 3) && (cpt == "92226") && (insurance1 != "MA00")
)
{
if (listofinstaking2units.Contains(insurance1))
{
cmdextophsub2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberextophsubseq + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberextophsubseq + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberextophsubseq + @";
";
cmdextophsub2U.ExecuteNonQuery();
}
}
//ext oph sub 2 units doudble amount MA00
if (
(entrynumberextophsubseq != 3) && (cpt == "92226") && (insurance1 == "MA00")
)
{
cmdMA92226TC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberextophsubseq + @";
delete from MWTRN where [Entry Number] = " + entrynumberMA9222626line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMA9222626linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMA9222626linesecondtime + @";
";
cmdMA92226TC26twounits.ExecuteNonQuery();
}
//Bscan 2 units double amount NOT MASSHEALTH
if (
(entrynumberBscan != 3) && (cpt == "76512") && (insurance1 != "MA00")//entrynumberBscan is not 3 only if there was a 76512 that came through before, and now there is another 76512 (cpt == 76512).
//so it must mean there are RT and LT lines for 76512 in HL7.
//at this point, there is either two lines 76512-RT (entrynumberBscan), 76512-LT (entrynumber)
//or 4 lines 76512-TC (entrynumberBscan), 76512-26 (entrynumberAHOBscan26line), 76512-TC (entrynumber), 76512-26 (entrynumberAHOBscan26linesecondtime)
)
{
if (listofinstaking2units.Contains(insurance1))
{
if (entrynumberAHOBscan26line == 0) //means probably it was not a BC insurance because it did not split to TC 26 lines.
{
cmdBscan2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberBscan + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberBscan + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberBscan + @";
";
cmdBscan2U.ExecuteNonQuery();
}
else //means it was BC and so there were TC 26 lines.
{
cmdBscanTC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberBscan + @";
delete from MWTRN where [Entry Number] = " + entrynumberAHOBscan26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberAHOBscan26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberAHOBscan26linesecondtime + @";
";
cmdBscanTC26twounits.ExecuteNonQuery();
}
}
}
//Bscan 2 units double amount MASSHEALTH MA00
if (
(entrynumberBscan != 3) && (cpt == "76512") && (insurance1 == "MA00")
)
{
cmdMABscanTC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberBscan + @";
delete from MWTRN where [Entry Number] = " + entrynumberMABscan26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMABscan26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMABscan26linesecondtime + @";
";
cmdMABscanTC26twounits.ExecuteNonQuery();
}
//injection 2 units double amount
if (
(entrynumber67028 != 3) && (cpt == "67028")
)
{
if (listofinstaking2units.Contains(insurance1))
{
cmdinj2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber67028 + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber67028 + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumber67028 + @";
";
cmdinj2U.ExecuteNonQuery();
}
}
//UBM 2 units double amount NOT MA00
if (
(entrynumberUBM != 3) && (cpt == "76513") && (insurance1 != "MA00")
)
{
if (listofinstaking2units.Contains(insurance1))
if (entrynumberAHOUBM26line == 0)
{
cmdUBM2U.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberUBM + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberUBM + @";
update MWTRN set [Modifier 1] = null where [Entry Number] =" + entrynumberUBM + @";
";
cmdUBM2U.ExecuteNonQuery();
}
else
{
cmdUBMTC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberUBM + @";
delete from MWTRN where [Entry Number] = " + entrynumberAHOUBM26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberAHOUBM26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberAHOUBM26linesecondtime + @";
";
cmdUBMTC26twounits.ExecuteNonQuery();
}
}
// UBM 2 units double amount MASSHEALTH
if (
(entrynumberUBM != 3) && (cpt == "76513") && (insurance1 == "MA00")
)
{
cmdMAUBMTC26twounits.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumberUBM + @";
delete from MWTRN where [Entry Number] = " + entrynumberMAUBM26line + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumber + @";
update MWTRN set [Units] = 2 where [Entry Number] =" + entrynumberMAUBM26linesecondtime + @";
update MWTRN set [Amount] = [Amount]*2 where [Entry Number] =" + entrynumberMAUBM26linesecondtime + @";
";
cmdMAUBMTC26twounits.ExecuteNonQuery();
}
//add up all the lucentis units both lines
if (
(entrynumberlucentis != 3) & (cpt == "J2778")
)
{
cmdlucentis.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = [Units] +" + firstlinelucentisunits + @"where [Entry Number] =" + entrynumberlucentis + @";
update MWTRN set [Amount] = [Amount] +" + firstlinelucentisamount + @"where [Entry Number] =" + entrynumberlucentis + @";
";
cmdlucentis.ExecuteNonQuery();
}
//add up all the eylea units both lines
if (
(entrynumbereylea != 3) & (cpt == "J0178")
)
{
cmdeylea.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = [Units] +" + firstlineeyleaunits + @"where [Entry Number] =" + entrynumbereylea + @";
update MWTRN set [Amount] = [Amount] +" + firstlineeyleaamount + @"where [Entry Number] =" + entrynumbereylea + @";
";
cmdeylea.ExecuteNonQuery();
}
//add up all triesence units both lines
if (
(entrynumbertriesence != 3) & (cpt == "J3300")
)
{
cmdtriesence.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = [Units] +" + firstlinetriesenceunits + @"where [Entry Number] =" + entrynumbertriesence + @";
update MWTRN set [Amount] = [Amount] +" + firstlinetriesenceamount + @"where [Entry Number] =" + entrynumbertriesence + @";
";
cmdtriesence.ExecuteNonQuery();
}
//add up all avastin units both lines
if (
(entrynumberavastin != 3) & (cpt == "J9035")
)
{
cmdavastin.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = [Units] +" + firstlineavastinunits + @"where [Entry Number] =" + entrynumberavastin + @";
update MWTRN set [Amount] = [Amount] +" + firstlineavastinamount + @"where [Entry Number] =" + entrynumberavastin + @";
";
cmdavastin.ExecuteNonQuery();
}
//add up all C9257 units both lines
if (
(entrynumberC9257 != 3) & (cpt == "C9257")
)
{
cmdC9257.CommandText = @"delete from MWTRN where [Entry Number] = " + entrynumber + @";
update MWTRN set [Units] = [Units] +" + firstlineC9257units + @"where [Entry Number] =" + entrynumberC9257 + @";
update MWTRN set [Amount] = [Amount] +" + firstlineC9257amount + @"where [Entry Number] =" + entrynumberC9257 + @";
";
cmdC9257.ExecuteNonQuery();
}
//grab entry number for the first time a line comes through (those which may come through again)
if (cpt == "92235") { entrynumberFA = entrynumber; }
if (cpt == "92240") { entrynumberICG = entrynumber; }
if (cpt == "92225") { entrynumberextophinit = entrynumber; }
if (cpt == "92226") { entrynumberextophsubseq = entrynumber; }
if (cpt == "76512") { entrynumberBscan = entrynumber; }
if (cpt == "67028") { entrynumber67028 = entrynumber; }
if (cpt == "76513") { entrynumberUBM = entrynumber; }
if (cpt == "J2778") { entrynumberlucentis = entrynumber; firstlinelucentisunits = unit; firstlinelucentisamount = amount; }
if (cpt == "J0178") { entrynumbereylea = entrynumber; firstlineeyleaunits = unit; firstlineeyleaamount = amount; }
if (cpt == "J3300") { entrynumbertriesence = entrynumber; firstlinetriesenceunits = unit; firstlinetriesenceamount = amount; }
if (cpt == "J9035") { entrynumberavastin = entrynumber; firstlineavastinunits = unit; firstlineavastinamount = amount; }
if (cpt == "C9257") { entrynumberC9257 = entrynumber; firstlineC9257units = unit; firstlineC9257amount = amount; }
//add 57 modifier
if ((
(listofcharges.Contains("99215")) |
(listofcharges.Contains("99214")) |
(listofcharges.Contains("99213")) |
(listofcharges.Contains("99212")) |
(listofcharges.Contains("99211")) |
(listofcharges.Contains("92014")) |
(listofcharges.Contains("92012")) |
(listofcharges.Contains("99202")) |
(listofcharges.Contains("99203")) |
(listofcharges.Contains("99204"))
)
&
(
(cpt == "67210") |
(cpt == "67220") |
(cpt == "67145") |
(cpt == "67105") |
(cpt == "67228") |
(cpt == "67110")
))
{
cmdadd57.CommandText = @"update MWTRN set [Modifier 1] = '57' where [Entry Number] =" + entrynumberofvisit + @";";
cmdadd57.ExecuteNonQuery();
}
//add 25 modifier
if ((
(listofcharges.Contains("99215")) |
(listofcharges.Contains("99214")) |
(listofcharges.Contains("99213")) |
(listofcharges.Contains("99212")) |
(listofcharges.Contains("99211")) |
(listofcharges.Contains("92014")) |
(listofcharges.Contains("92012")) |
(listofcharges.Contains("99202")) |
(listofcharges.Contains("99203")) |
(listofcharges.Contains("99204"))
)
&
(
(cpt == "67028") |
(cpt == "65800")
))
{
cmdadd25.CommandText = @"update MWTRN set [Modifier 1] = '25' where [Entry Number] =" + entrynumberofvisit + @";";
cmdadd25.ExecuteNonQuery();
}
//if cpt is 76512 or 76513 and insurance1 starts with BC
// if (((insurance1 != "MC00") & cpt != "2021F" & cpt != "G8397" & cpt != "5010F" & cpt != "2019F")|insurance1 == "MC00")
}//if patient has insurance1, if there is insurance1
}//if (oneline.Contains("FT1"))
} //foreach (string oneline in HL7lines)
/*
//show all lines of MWTRN recently.
DataTable dt3 = new DataTable();
//cmd3.CommandText = @"select * from MWTRN where [Chart Number] = "SPRED000' and [Entry Number] = (select top 1 [Entry Number] from MWTRN where [Chart Number]='SPRED000' order by [Entry Number] DESC)";
cmd3.CommandText = @"select * from MWTRN where [Chart Number] ='SPRED000' and [Date From] > '5/20/2013' order by [Entry Number] DESC";
AdsDataAdapter ada3 = new AdsDataAdapter(cmd3);
ada3.Fill(dt3);
//GridView3.DataSource = dt3.DefaultView;
//GridView3.DataBind();
*/
// cmd4.CommandText = @"delete from MWTRN where [Chart Number] = 'SPRED000' and [Entry Number]>499485 and [User Code]='SC'"; cmd4.ExecuteNonQuery();
// the case BEFORE anything is done
//GridView.DataSource = dt.DefaultView;//case before INSERT // this shows sql to adv works and sends to gridview defaultview.
// GridView.DataBind();//this shows sql to adv works and sends to gridview defaultview.
//show the case AFTER everything is done.
/*
cmdcaseafterinsert.CommandText = @"select * from MWCAS where [Case Number] =" + casenumber + @";";
DataTable dtcaseafterinsert = new DataTable();
AdsDataAdapter ada5 = new AdsDataAdapter(cmdcaseafterinsert);
ada5.Fill(dtcaseafterinsert);
//GridView5.DataSource = dtcaseafterinsert.DefaultView;
// GridView5.DataBind();
*/
con.Close();
return failurecode;
}// Process method
public static AdsDataComponent.AdsDataTable GetSavedAds(int memberId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
return db.GetSavedAds(memberId);
}
}
public static void RelistAd(int adId, int categoryId, string title, string description,
string url, decimal price, string location, int numDaysActive, AdLevel adLevel,
AdStatus adStatus, AdType adType,
string code, string syn1, string syn2, string syn3, string syn4, string syn5)
{
SiteSettings s = SiteSettings.GetSharedSettings();
DateTime dateCreated = DateTime.Now;
DateTime? dateApproved = null;
if (numDaysActive > s.MaxAdRunningDays)
numDaysActive = s.MaxAdRunningDays;
if (numDaysActive < 1)
numDaysActive = 1;
DateTime expirationDate = DateTime.Today.AddDays(numDaysActive);
if (s.AdActivationRequired)
{
adStatus = AdStatus.ActivationPending;
dateApproved = null;
}
else
{
adStatus = AdStatus.Activated;
dateApproved = dateCreated;
}
if (adLevel == AdLevel.Unspecified)
adLevel = AdLevel.Normal;
AdsDataComponent.AdsRow ad = null;
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.RelistAd(adId, categoryId, title, description, url, price, location, expirationDate,
dateCreated, dateApproved, (int)adLevel, (int)adStatus, (int)adType,
code, syn1, syn2, syn3, syn4, syn5);
if (s.AdminNotification == AdminNotificationSetting.EachAd)
ad = GetFirstRow(db.GetAdById(adId));
}
if (ad != null)
Maintenance.SendAdNotification(ad);
}
public static void UpdateAdStatusList(List<int> adIds, AdStatus adStatus)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
for (int i = 0; i < adIds.Count; i++)
db.UpdateAdStatus(adIds[i], (int)adStatus);
}
}
public static void InsertSavedAdList(List<int> adIds, int memberId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
for (int i = 0; i < adIds.Count; i++)
db.InsertSavedAd(adIds[i], memberId);
}
}
public static void MoveAdsToCategory(int currentCategoryId, int newCategoryId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.MoveAds(currentCategoryId, newCategoryId);
}
}
public static void InsertSavedAd(int adId, int memberId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.InsertSavedAd(adId, memberId);
}
}
public static void IncrementViewCount(int adId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAdStats(adId, 1, 0);
}
}
public static void IncrementResponseCount(int adId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAdStats(adId, 0, 1);
}
}
public static void UpdateAdLevelList(List<int> adIds, AdLevel adLevel)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
for (int i = 0; i < adIds.Count; i++)
db.UpdateAdLevel(adIds[i], (int)adLevel);
}
}
public static void RemoveFromDatabase(int adId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
PhotosDB.RemovePhotosByAdId(adId);
db.RemoveAd(adId);
}
}
public static void UpdateAdStatus(int adId, AdStatus adStatus)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.UpdateAdStatus(adId, (int)adStatus);
}
}
private void Populate_btn_Click(object sender, EventArgs e)
{
try
{
//string sSql = "SELECT BATCH_NUM, EMC_RECV, STATUS FROM Batch WHERE Status = 'Q';";
sSql = "Select Distinct Batch_Num, EMC_RECV, Status, BH.Date From Batch Join [Billing History] BH ON Batch_Num = BH.Batch WHERE Status = 'Q'";
string sBadpracs = "";
int iBatchcount = 0;
dataGridView1.DataSource = null;
LabelBox.Text = null;
DataTable mainTable = new DataTable();
//mainTable.Columns.Add("Practice", typeof(Int32), sDataBase);
try
{
practiceList = GetPractices();
if (practiceList != null)
{
for (int i = 0; i <= practiceList.Count - 1; i++)
{
//change conn string for each practice
sDataBase = practiceList[i];
sDataSource = @"\\CLAIMS2\AltaData\" + sDataBase + @"\Data\AltaPoint.add;";
string connectionString = @"Data Source = " + sDataSource + sUser + sType;
connection = new AdsConnection {
ConnectionString = connectionString
};
using (connection)
{
try
{
connection.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
IDataAdapter adapter = new AdsDataAdapter(sSql, connection);
adapter.Fill(ds);
iBatchcount += ds.Tables[0].Rows.Count;
if (ds.Tables[0].Rows.Count > 0)
{
//DataRow row;
//row = mainTable.NewRow();
//row["Practice"] = sDataBase;
//mainTable.Rows.Add(row);
dt = ds.Tables[0];
mainTable.Merge(dt);
}
ds.Clear();
}
catch (Exception ex)
{
sBadpracs += sDataBase + ", ";
}
}
}
}
if (mainTable.Rows.Count == 0)
{
MessageBox.Show("No Results Found"); return;
}
dataGridView1.DataSource = mainTable;
MessageBox.Show("Unable to retrieve " + sBadpracs);
LabelBox.Text = iBatchcount + " queued batches counted.";
}
catch (Exception ex)
{
MessageBox.Show(ex.TargetSite.ToString());
MessageBox.Show(ex.StackTrace);
MessageBox.Show(ex.Source);
MessageBox.Show(ex.InnerException.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static AdsDataComponent.AdsDataTable GetAdsByStatus(AdStatus adStatus, int memberId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
return db.GetAdsByStatus((int)adStatus, memberId);
}
}
protected void Fill <T>(AdsEntityMapping mapping, DataSet dataSet, int?topCount, string sqlFilterExpression, string sqlOrderExpression) where T : class
{
DataTable table = new DataTable(mapping.ResolvedName);
foreach (var member in mapping.Members)
{
DataColumn column = new DataColumn(member.ResolvedName);
if (member.MemberType.IsGenericType)
{
Type genericTypeDef = member.MemberType.GetGenericTypeDefinition();
if (genericTypeDef == typeof(Nullable <>))
{
column.AllowDBNull = true;
column.DataType = member.MemberType.GetGenericArguments()[0];
}
}
else
{
if (member.MemberType.IsValueType)
{
column.AllowDBNull = false;
}
else
{
column.AllowDBNull = member.ColumnAttribute.IsNullable;
}
column.DataType = member.MemberType;
}
table.Columns.Add(column);
}
AdsCommand command = new AdsCommand(CreateSelectQuery <T>(mapping, topCount, sqlFilterExpression, sqlOrderExpression), connection);
AdsDataAdapter adapter = new AdsDataAdapter(command);
LastSqlCommandText = command.CommandText;
adapter.Fill(table);
if (IsAutoTrimmingEnabled)
{
foreach (var member in mapping.Members)
{
string name = member.ResolvedName;
if (member.MemberType == typeof(string) && member.ColumnAttribute.IsAutoTrimmingEnabled)
{
foreach (DataRow row in table.Rows)
{
if (!row.IsNull(name))
{
row[name] = ((string)row[name]).TrimEnd();
}
}
}
}
}
dataSet.Merge(table);
}
public static void RemoveFromDatabaseByStatus(AdStatus adStatus)
{
PhotosDB.RemovePhotosOfDeletedAds();
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.RemoveAdsByStatus((int)adStatus);
}
}
public static AdsDataComponent.AdsDataTable GetFeaturedAdsSelection(int maxNumAds)
{
if (maxNumAds < 1)
return null;
using (AdsDataAdapter db = new AdsDataAdapter())
{
return db.GetAdsByRandomOrder(maxNumAds, (int)AdStatus.Activated, (int)AdLevel.Featured);
}
}
public static void RemoveListFromDatabase(List<int> adIds)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
for (int i = 0; i < adIds.Count; i++)
{
PhotosDB.RemovePhotosByAdId(adIds[i]);
db.RemoveAd(adIds[i]);
}
}
}
public static void RemoveSavedAd(int id, int memberId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
db.RemoveSavedAd(id, memberId);
}
}
private static void CheckAdExpirations()
{
AdsDataComponent.AdsDataTable ads = null;
using (AdsDataAdapter db = new AdsDataAdapter())
{
DateTime expiration = DateTime.Now;
ads = db.GetExpiredAds(expiration, (int)AdStatus.Activated);
foreach (AdsDataComponent.AdsRow ad in ads.Rows)
{
db.UpdateAdStatus(ad.Id, (int)AdStatus.Inactive);
}
}
}
public static AdsDataComponent.AdsRow GetAdById(int adId)
{
using (AdsDataAdapter db = new AdsDataAdapter())
{
return GetFirstRow(db.GetAdById(adId));
}
}
