protected override IDataReader PrepareSQLDatasetInternal(String sql, bool schemaOnly)
{
AdsCommand command;
AdsDataReader reader;
try
{
if (!Connected)
{
Connect();
}
command = _connection.CreateCommand();
command.CommandTimeout = CommandTimeout;
command.CommandText = sql;
if (schemaOnly)
{
reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
}
else
{
reader = command.ExecuteReader();
}
}
catch (Exception e)
{
throw new QueryBuilderException(ErrorCode.ErrorExecutingQuery,
e.Message + "\n\n" + Helpers.Localizer.GetString("strQuery", Constants.strQuery) + "\n" + sql);
}
return(reader);
}
private void btnDohvatiFakture_Click(object sender, EventArgs e)
{
AdsConnection conn = new AdsConnection();
conn.ConnectionString = "data source=V:\\Centrala\\rabati-dobavljaci.add; " + "user id = adssys; password = '******' " + "ServerType=REMOTE; TrimTrailingSpaces = true";
//za mene lokalno
//"data source=V:\\stanic\\baza\\centralna.add; " + "user id = adssys; password = '' " + "ServerType=REMOTE; TrimTrailingSpaces = true";
//za restoran
//"data source=\\\\192.168.3.5:6262\\podaci\\velep\\stanic\\baza\\centralna.add; " + "user id = adssys; password = '' " + "ServerType=REMOTE; TrimTrailingSpaces = true"
conn.Open();
using (AdsCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Select f_brfakt, f_sifpar, f_datfakt, f_valuta, f_iznos, f_pdv, f_povnak, f_ukupno, f_status, f_opis, f_knjig, f_datknjig, f_osn22, f_osn10, f_osn5, f_pdv22, f_pdv10, f_pdv5 from bonfakt where f_datfakt >='2013-01-01' and f_datfakt <='2013-12-31' ";
//b_ponisten <> True and b_brfakt = '' and b_kasa <> '' and b_racun <> '' and
using (AdsDataReader reader = cmd.ExecuteReader())
{
stariBonovi.Load(reader);
if (stariBonovi.Rows.Count < 1)
{
return;
}
else
{
dgStariBonovi.DataSource = stariBonovi;
}
}
conn.Close();
dgNoviBonovi.Refresh();
}
}
protected void btnAzuriraj_Click(object sender, EventArgs e)
{
try
{
AdsConnection conn = new AdsConnection();
conn.ConnectionString = "data source=P:\\kafic\\program\\kasa restoran\\baza\\centralna.add; " + "user id = adssys; password = '' " + "ServerType=REMOTE; TrimTrailingSpaces = true";
conn.Open();
using (AdsCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Select ap_sifra, ap_naziv, ap_cijena from artikp";
using (AdsDataReader reader = cmd.ExecuteReader())
{
artikl.Load(reader);
if (artikl.Rows.Count < 1)
{
return;
}
else
{
//GridView1.DataSource = artikl;
//GridView1.DataBind();
for (int i = 0; i < artikl.Rows.Count; i++)
{
string sifra = artikl.Rows[i][0].ToString();
string naziv = artikl.Rows[i][1].ToString();
string cijena = artikl.Rows[i][2].ToString().Replace(",", ".");
upiti.ExecuteQuery("select * from artikl where ar_sifra = '" + sifra + "' ", ref dt);
if (dt.Rows.Count < 1)
{
upiti.ExecuteQuery("insert into artikl (ar_sifra, ar_naziv, ar_cijena) values ('" + sifra + "', '" + naziv + "', '" + cijena + "')");
}
else
{
upiti.ExecuteQuery("update artikl set ar_sifra = '" + sifra + "', ar_naziv = '" + naziv + "', ar_cijena = '" + cijena + "' where ar_sifra = '" + sifra + "'");
}
}
Label2.Text = "Gotovo ažuriranje";
}
}
conn.Close();
}
}
catch (Exception ex)
{
Label2.Text = ex.Message;
}
}
private void btnDohvati_Click(object sender, EventArgs e)
{
stariBonovi.Clear();
DataTable bon = new DataTable();
if (txtBonKod.Text.Trim() != "")
{
//prvo provjeri kod nas u bazi da li postoji taj bon ako ne onda ga dohvati iz stare i spremi u tablicu bonova
veza.ExecuteQuery("select bon_ID from bonovi where bon_kod = " + txtBonKod.Text.Trim() + "", ref bon);
if (bon.Rows.Count > 0)
{
//postoji u bazi ne dohvacaj ponovno
MessageBox.Show("Bon sa tim barkodom već postoji u bazi! Provjerite još jednom!");
txtBonKod.Text = "";
txtBonKod.Focus();
}
else
{
//dohvati taj bon
AdsConnection conn = new AdsConnection();
conn.ConnectionString = "data source=V:\\Centrala\\rabati-dobavljaci.add; " + "user id = adssys; password = '******' " + "ServerType=REMOTE; TrimTrailingSpaces = true";
//za mene lokalno
//"data source=V:\\stanic\\baza\\centralna.add; " + "user id = adssys; password = '' " + "ServerType=REMOTE; TrimTrailingSpaces = true";
//za restoran
//"data source=\\\\192.168.3.5:6262\\podaci\\velep\\stanic\\baza\\centralna.add; " + "user id = adssys; password = '' " + "ServerType=REMOTE; TrimTrailingSpaces = true"
conn.Open();
using (AdsCommand cmd = conn.CreateCommand())
{
//cmd.CommandText = "Select b_sifbon, b_sifpar, b_redbroj, b_datizd, b_satizd, b_iznos, b_operi, b_vazido, b_datkor, b_sat, b_kasa, b_racun, b_brkart, b_iznosrac, b_operk, b_organ, b_brfakt from bonovi where B_datizd between '2013-01-01' and '2013-12-31' and b_ponisten <> True ";
cmd.CommandText = "Select b_sifbon, b_sifpar, b_redbroj, b_datizd, b_satizd, b_iznos, b_operi, b_vazido, b_datkor, b_sat, b_kasa, b_racun, b_brkart, b_iznosrac, b_operk, b_organ, b_brfakt from bonovi where B_sifbon = '" + txtBonKod.Text.Trim() + "' ";
using (AdsDataReader reader = cmd.ExecuteReader())
{
stariBonovi.Load(reader);
if (stariBonovi.Rows.Count < 1)
{
return;
}
else
{
dgStariBonovi.DataSource = stariBonovi;
}
}
conn.Close();
dgNoviBonovi.Refresh();
}
}
}
else
{
txtBonKod.Focus();
}
}
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
private void btnStart_Click(object sender, EventArgs e)
{
btnStart.Enabled = false;
Properties.Settings.Default["HotMaxPath"] = txtHotmaxPath;
Properties.Settings.Default.Save();
/* Define the lists here (not parent scope) to clear them on each btn click */
List <string> shBarcode = new List <string>();
List <string> shCountry = new List <string>();
List <string> shType = new List <string>();
List <string> eksBarcode = new List <string>();
List <string> mergedBarcode = new List <string>();
List <string> mergedCountry = new List <string>();
List <string> mergedType = new List <string>();
seasonNamesSecutixOne.Clear();
string[] exportFilesSecutixOne = Directory.GetFiles(txtSecutixOneDir.Text, "*.csv");
foreach (string fileName in exportFilesSecutixOne)
{
try
{ // Open each CSV file using a stream reader
using (StreamReader sr = new StreamReader(fileName))
{
sr.ReadLine(); // 1st line could have headings, just skip it
string seasonName = sr.ReadLine().Split('\t')[7]; // column 7 (zero-based) is name of season
try
{
seasonNamesSecutixOne.Add(seasonName, fileName);
}
catch (ArgumentException)
{
MessageBox.Show("An element with Key = \"" + seasonName + "\" already exists.", "Error");
}
}
}
catch (Exception ex)
{
Console.WriteLine("The file could not be read:");
Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message, "The file could not be read");
}
}
/* Read SecuTix 360 data */
try
{ // Open the CSV file using a stream reader
using (StreamReader srs = new StreamReader(seasonNamesSecutix360[lstSeasons.SelectedItem.ToString()], Encoding.Default)) // we're using the dictionary here
{
string stLine = srs.ReadLine().Replace("\"", ""); // first line with headings
string[] stHead = stLine.Split('\t', ';');
string[] stValues;
while (!srs.EndOfStream)
{
stLine = srs.ReadLine().Replace("\"", "") // from 2nd line on
.Replace("auml;", "ä").Replace("Auml;", "Ä") // without "&" for doubles ("&auml;")
.Replace("ouml;", "ö").Replace("Ouml;", "Ö")
.Replace("uuml;", "ü").Replace("Uuml;", "Ü")
.Replace("szlig;", "ß").Replace("&", "&")
.Replace("rsquo;", "’").Replace("#8217;", "’")
.Replace("'", "'").Replace("#39;", "'");
stValues = stLine.Split('\t', ';');
if (stValues[1].Length > 0) // if no barcode, skip this line
{
shBarcode.Add(stValues[1].Substring(0, 16));
if (Array.IndexOf(stHead, "Q20") > 0)
{
if (stValues[Array.IndexOf(stHead, "Q20")].Length > 1)
{
shCountry.Add(stValues[Array.IndexOf(stHead, "Q20")]);
}
else
{
shCountry.Add(stValues[Array.IndexOf(stHead, "COUNTRY")]);
}
}
else if (Array.IndexOf(stHead, "Land (messe)") > 0)
{
shCountry.Add(stValues[Array.IndexOf(stHead, "Land (messe)")]);
}
else if (Array.IndexOf(stHead, "Land (neu)") > 0)
{
shCountry.Add(stValues[Array.IndexOf(stHead, "Land (neu)")]);
}
else
{
shCountry.Add(stValues[Array.IndexOf(stHead, "Land")]);
}
shType.Add("Online");
}
}
Console.WriteLine("SecuTix 360 records imported: " + shBarcode.Count);
}
}
catch (Exception ex)
{
Console.WriteLine("The SecuTix file could not be read:");
Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message, "The SecuTix 360 file could not be read");
}
/* Read SecuTix One data */
try
{ // Open the CSV file using a stream reader
using (StreamReader srs = new StreamReader(seasonNamesSecutixOne[lstSeasons.SelectedItem.ToString()], Encoding.Default)) // we're using the dictionary here
{
string stLine = srs.ReadLine(); // first line with headings
string[] stHead = stLine.Split('\t');
string[] stValues;
while (!srs.EndOfStream)
{
stLine = srs.ReadLine().Replace("\"", ""); // from 2nd line on
stValues = stLine.Split('\t');
shBarcode.Add(stValues[11].Substring(0, 16));
if (Array.IndexOf(stHead, "COUNTRY") > 0)
{
shCountry.Add(stValues[Array.IndexOf(stHead, "COUNTRY")]);
}
else
{
shCountry.Add("n/a");
}
shType.Add("Online");
}
Console.WriteLine("Total SecuTix records imported: " + shBarcode.Count);
}
}
catch (Exception ex)
{
Console.WriteLine("The SecuTix file could not be read:");
Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message, "The SecuTix One file could not be read");
}
/* Read HotMax data */
/* Don't forget to change "Platform target" in Project Properties to "x86". */
// create a connection object
AdsConnection conn = new AdsConnection("data source=" + txtHotmaxPath.Text + "; ServerType=remote|local; TableType=ADT");
AdsCommand cmd;
AdsDataReader reader;
try
{ // connect to server
lblStatus.Text = "Connecting to HotMax server...";
Application.DoEvents(); // refreshing GUI
conn.Open();
Console.WriteLine("Connection String: " + conn.ConnectionString.ToString());
Console.WriteLine("Current Connection State: " + conn.State.ToString());
// create a command object
cmd = conn.CreateCommand();
// increase timeout from 30 (default) to 60 seconds.
cmd.CommandTimeout = 60;
// specify a SELECT statement
cmd.CommandText = "select * from lktokasse,mesreg where mesreg.MesseCode like '"
+ lstSeasons.SelectedItem.ToString()
+ "' and lktokasse.vonkontotrans=mesreg.kunde";
Console.WriteLine(cmd.CommandText);
// execute the statement and create a reader
lblStatus.Text = "Importing HotMax data...";
Application.DoEvents();
reader = cmd.ExecuteReader();
while (reader.Read())
{
shBarcode.Add(reader.GetValue(36).ToString().Substring(0, 16));
// Mex and XB are hotfixes for Fruit 2016. Remove in future.
shCountry.Add(reader.GetValue(44).ToString().Trim().Replace("Mex", "MX").Replace("XB", "XK")); // Trim() removes trailing white-spaces
shType.Add("Onsite");
}
conn.Close();
Console.WriteLine("Current Connection State: " + conn.State.ToString());
}
catch (AdsException ex)
{ // print the exception message
Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message, "Error while reading HotMax data");
}
/* Now read EKS data */
lblStatus.Text = "Reading EKS Exports...";
Application.DoEvents();
bool addCode;
foreach (string eksFileName in Directory.GetFiles(txtEKSDir.Text, "*.csv"))
{
try
{
Console.WriteLine("Reading: " + eksFileName);
using (StreamReader sre = new StreamReader(eksFileName))
{
string eksLine;
string[] eksValues;
while (!sre.EndOfStream)
{
addCode = false;
eksLine = sre.ReadLine(); // from 1st line on, but we only compare barcodes
eksValues = eksLine.Split(';');
if (string.Compare(eksValues[21], "1", true) == 0 && // column "Eingetreten"
string.Compare(eksValues[13].Substring(0, 3), lstSeasons.SelectedItem.ToString().Substring(0, 3), true) == 0)
{
if (chkExhibitor.Checked && eksValues[14][4] == 'A')
{
addCode = true;
}
else if (chkTrade.Checked && eksValues[14][4] == 'F')
{
addCode = true;
}
else if (chkPress.Checked && eksValues[14][4] == 'M')
{
addCode = true;
}
else if (chkPrivate.Checked && eksValues[14][4] == 'P')
{
addCode = true;
}
else if (chkOther.Checked && eksValues[14][4] == 'S')
{
addCode = true;
}
if (addCode)
{
eksBarcode.Add(eksValues[0].Substring(0, 16));
}
/*
* else
* {
* Console.WriteLine("Barcode " + eksValues[0] + " not imported from EKS.");
* }
*/
}
}
Console.WriteLine("EKS records imported: " + eksBarcode.Count);
}
}
catch (Exception ex)
{
Console.WriteLine("The EKS file could not be read:");
Console.WriteLine(ex.Message);
MessageBox.Show(ex.Message, "The file could not be read");
}
}
/* Create merged data */
lblStatus.Text = "Calculating Result...";
Application.DoEvents();
StringBuilder sb = new StringBuilder();
CountryDictData.generateDict();
SortedDictionary <string, int[]> countLaender = new SortedDictionary <string, int[]>(); // <Country, {counterOnsite, counterOnline}>
sb.AppendLine("LandKurz;Land;Anzahl Onsite;Anzahl Online;Summe pro Land;Prozent an Gesamt");
int bcDone = 0; // counter for status label
/* TODO: Refactor this part maybe. */
if (chkDistinctBarcode.Checked)
{
foreach (string bc in shBarcode)
{
if (bcDone % 1000 == 0) // refresh status label every 1000 processed records
{
lblStatus.Text = "Merging data... (" + bcDone + "/" + shBarcode.Count + ")";
Application.DoEvents();
}
if (eksBarcode.Contains(bc) && !mergedBarcode.Contains(bc)) // each barcode once
{
mergedBarcode.Add(bc);
/* If country code is in dictionary, add it, else add "ZZ" */
if (shCountry[shBarcode.IndexOf(bc)].Length == 2)
{
if (CountryDictData.nameLaender.ContainsKey(shCountry[shBarcode.IndexOf(bc)]))
{
mergedCountry.Add(shCountry[shBarcode.IndexOf(bc)]);
}
else
{
mergedCountry.Add("ZZ");
Console.WriteLine("Unknown: " + shCountry[shBarcode.IndexOf(bc)]);
}
}
/* If not country code, but full name, lookup the code in dict and add it */
else if (shCountry[shBarcode.IndexOf(bc)].Length > 1)
{
if (CountryDictData.nameLaender.ContainsKey(shCountry[shBarcode.IndexOf(bc)]))
{
mergedCountry.Add(CountryDictData.nameLaender[shCountry[shBarcode.IndexOf(bc)]]);
}
else
{
mergedCountry.Add("ZZ");
Console.WriteLine("Unknown: " + shCountry[shBarcode.IndexOf(bc)] + " (line " + shBarcode.IndexOf(bc) + ")");
}
}
mergedType.Add(shType[shBarcode.IndexOf(bc)]);
}
bcDone++;
}
}
else
{ // The same thing, just wihout checking for distinct barcodes.
// This time we're going from EKS to SH (faster).
foreach (string bc in eksBarcode)
{
if (bcDone % 1000 == 0)
{
lblStatus.Text = "Merging data... (" + bcDone + "/" + eksBarcode.Count + ")";
Application.DoEvents();
}
if (shBarcode.Contains(bc))
{
mergedBarcode.Add(bc);
if (shCountry[shBarcode.IndexOf(bc)].Length == 2)
{
if (CountryDictData.nameLaender.ContainsKey(shCountry[shBarcode.IndexOf(bc)]))
{
mergedCountry.Add(shCountry[shBarcode.IndexOf(bc)]);
}
else
{
mergedCountry.Add("ZZ");
Console.WriteLine("Unknown: " + shCountry[shBarcode.IndexOf(bc)]);
}
}
else if (shCountry[shBarcode.IndexOf(bc)].Length > 1)
{
if (CountryDictData.nameLaender.ContainsKey(shCountry[shBarcode.IndexOf(bc)]))
{
mergedCountry.Add(CountryDictData.nameLaender[shCountry[shBarcode.IndexOf(bc)]]);
}
else
{
mergedCountry.Add("ZZ");
Console.WriteLine("Unknown: " + shCountry[shBarcode.IndexOf(bc)] + " (line " + shBarcode.IndexOf(bc) + ")");
}
}
mergedType.Add(shType[shBarcode.IndexOf(bc)]);
}
bcDone++;
}
}
Console.WriteLine("Merged " + mergedBarcode.Count + " barcodes.");
int total = 0;
for (int i = 0; i < mergedCountry.Count; i++)
{
/* If we haven't seen this country before, add it to dictionary */
if (!countLaender.ContainsKey(mergedCountry[i]))
{
if (string.Compare(mergedType[i], "Onsite", true) == 0)
{
countLaender.Add(mergedCountry[i], new[] { 1, 0 });
}
else if (string.Compare(mergedType[i], "Online", true) == 0)
{
countLaender.Add(mergedCountry[i], new[] { 0, 1 });
}
total++;
}
else
{
if (string.Compare(mergedType[i], "Onsite", true) == 0)
{
countLaender[mergedCountry[i]][0]++;
}
else if (string.Compare(mergedType[i], "Online", true) == 0)
{
countLaender[mergedCountry[i]][1]++;
}
total++;
}
}
string htmlMap = "";
if (chkShowMap.Checked)
{
// Google Charts Map
htmlMap =
"<!DOCTYPE html>\n" +
"<html>\n" +
"<head>\n" +
"<script type=\"text/javascript\" src=\"https://www.google.com/jsapi\"></script>\n" +
"<script type=\"text/javascript\">\n" +
"google.load(\"visualization\", \"1\", {packages:[\"geochart\"]});\n" +
"google.setOnLoadCallback(drawRegionsMap);\n" +
"function drawRegionsMap() {\n" +
"var data = google.visualization.arrayToDataTable([\n" +
"['Country', 'Visitors', 'Percentage']";
}
decimal percent;
int sum; // Onsite + Online per country
int sumOnsite = 0; // Total Onsite
int sumOnline = 0; // Total Online
foreach (string cc in countLaender.Keys)
{
/* If valid country code, append a line with number of SecuTix and HotMax hits */
if (cc.Length > 1)
{
sum = countLaender[cc][0] + countLaender[cc][1];
sumOnsite += countLaender[cc][0];
sumOnline += countLaender[cc][1];
percent = Math.Round((decimal)sum * 10000 / total) / 100;
Console.WriteLine(cc);
sb.AppendLine(cc + ";"
+ CountryDictData.nameLaender[cc] + ";"
+ countLaender[cc][0] + ";"
+ countLaender[cc][1] + ";"
+ sum + ";"
+ percent + "%");
if (chkShowMap.Checked && chkExcludeDE.Checked)
{
if (string.Compare(cc, "DE", true) != 0)
{
htmlMap += ",\n['" + cc + "', " + sum + ", " + percent.ToString().Replace(",", ".") + "]";
}
}
else if (chkShowMap.Checked)
{
htmlMap += ",\n['" + cc + "', " + sum + ", " + percent.ToString().Replace(",", ".") + "]";
}
}
}
sb.AppendLine("Summe;;" + sumOnsite + ";" + sumOnline + ";" + (sumOnsite + sumOnline) + ";100,00%");
/* Write the stream contents to a new file */
if (sumOnsite > 0 || sumOnline > 0)
{
Console.WriteLine("Onsite: " + sumOnsite);
Console.WriteLine("Online: " + sumOnline);
using (StreamWriter outfile = new StreamWriter(txtCSVOut.Text))
{
outfile.Write(sb.ToString());
}
if (chkShowMap.Checked)
{
htmlMap +=
"\n]);\n" +
"var options = {\n";
if (chkIE9.Checked)
{
// tooltip:{textStyle:{color:'black',fontName:<global-font-name>,fontSize:<global-font-size>}}
htmlMap += "tooltip:{textStyle:{bold:false}}\n";
}
htmlMap +=
"};\n" +
"var chart = new google.visualization.GeoChart(document.getElementById('regions_div'));\n" +
"chart.draw(data, options);\n" +
"}\n" +
"</script>\n" +
"</head>\n" +
"<body>\n" +
"<div id=\"regions_div\" style=\"width: 900px; height: 500px; \"></div>\n" +
"</body>\n" +
"</html>";
string htmlFile = "Map_" + lstSeasons.SelectedItem.ToString().Replace(" ", "_").Replace("/", "_") + ".html";
File.WriteAllText(htmlFile, htmlMap); // save in same directory
System.Diagnostics.Process.Start(htmlFile);
}
lblStatus.Text = "All done.";
Application.DoEvents();
MessageBox.Show("Operation completed.");
}
else
{
lblStatus.Text = "No records found.";
Application.DoEvents();
MessageBox.Show("Something went wrong.\nNo records were found.");
}
btnStart.Enabled = true;
}
public bool Update(IFoxProEntity entity)
{
var qTime = DateTime.Now;
entity.PrepareSave();
var did = false;
var table = entity.GetType().Name;
//if (table == "hfinv") table = "xfinv";
//if (table == "hfinvbm") table = "xfinvbm";
//if (table == "hbmnote") table = "xhbmnote";
//if (table == "hord") table = "xord";
//if (table == "hdet") table = "xdet";
Conn.Open();
var cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = table;
var rdr = cmd.ExecuteExtendedReader();
if (rdr.FoxProSeek(entity))
{
if (rdr.FoxRecordLock(table))
{
try {
foreach (var prop in entity.GetType().GetProperties())
{
var att = prop.GetAttribute <MyCustomAttribute>();
if (att != null && (att.AdsIgnore || att.AdsReadOnly))
{
continue;
}
if (!entity.DupDirtyList.Contains(prop.Name))
{
continue;
}
var value = prop.GetValue(entity, null);
//if (value == null) continue;
var ordinal = rdr.GetOrdinal(prop.Name);
switch (prop.GetFoxType())
{
case FoxType.DateTime:
rdr.SetDateTime(ordinal, Convert.ToDateTime(value));
break;
case FoxType.Boolean:
rdr.SetBoolean(ordinal, Convert.ToBoolean(value));
break;
case FoxType.Decimal:
rdr.SetDecimal(ordinal, Convert.ToDecimal(value));
break;
case FoxType.Int32:
rdr.SetInt32(ordinal, Convert.ToInt32(value));
break;
default:
var str = value.ToString();
if (att != null && att.Mask != null && att.Mask.Contains("###-###-####") && !str.Contains("-") && str.Length == 10)
{
str = $"{str.Substring(0, 3)}-{str.Substring(3, 3)}-{str.Substring(6, 4)}";
}
rdr.SetString(ordinal, str);
break;
}
}
rdr.WriteRecord();
rdr.UnlockRecord();
did = true;
}
catch (Exception e) {
// Undo changes, data was not written
// write update failure message
if (rdr.IsRecordLocked())
{
//rdr.WriteRecord();
rdr.UnlockRecord();
}
Debug.WriteLine($"ADS Update Error: {e.Message}");
}
}
else
{
// Record could not be locked, data was not written
// write failure message somewhere
if (rdr.IsRecordLocked())
{
rdr.UnlockRecord();
}
Debug.WriteLine($"ADS Update Error: lock failed!");
}
}
else
{
// write didn't find keyvalue record message
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"Update - {table}");
return(did);
}
public void get_ac_accoun(string db)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + db + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
//SqlConnection sqlconn = new SqlConnection("Data Source=srvdwh;Initial Catalog=Staging;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=Staging;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlCommand sqlcmd;
try
{
adsconn.Open();
sqlconn.Open();
adscmd = adsconn.CreateCommand();
sqlcmd = sqlconn.CreateCommand();
adscmd.CommandText = "SELECT '" + db + "' AS DB,AID,AISTITLE,HEADING1 FROM ac_accoun";
adsreader = adscmd.ExecuteReader();
sqlcmd.CommandText = "DELETE FROM dbo.ac_accoun WHERE DB = '" + db + "'";
sqlcmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlconn))
{
bulkCopy.DestinationTableName = "dbo.ac_accoun";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(adsreader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
sqlconn.Close();
}
catch (AdsException e)
{
Console.WriteLine(e.Message);
}
}
private void versPressepapierToolStripMenuItem_Click(object sender, EventArgs e)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + cB_Customer.Text.Substring(0, 6) + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
adsconn.Open();
adscmd = adsconn.CreateCommand();
//adscmd.CommandText = "SELECT HDOCDATE,CASE WHEN HID IN ('40110000') THEN 'C' WHEN HID IN ('44111000 ') THEN 'F' ELSE 'G' END AS Type, CASE WHEN HID IN ('40110000','44111000 ') THEN 'DIVERS' ELSE HID END AS HID, HAMOUNT * -1 AS HAMOUNT, 'Annulation '+RTRIM(HREM) AS HREM FROM ac_ahisto WHERE HDOCDATE BETWEEN '" + dP_periodStart.Value.Date.ToString("yyyy-MM-dd") + "' AND '"+ dP_periodEnd.Value.Date.ToString("yyyy-MM-dd")+"'";
adscmd.CommandText = "SELECT '31/12/'+CAST(YEAR(HDOCDATE) AS SQL_CHAR(4))+' 00:00:00' AS BOBDATE,CASE WHEN HID IN ('40110000') THEN 'C' WHEN HID IN ('44111000 ') THEN 'F' ELSE 'G' END AS TYPE, CASE WHEN HID IN ('40110000','44111000 ') THEN 'DIVERS' ELSE HID END AS ACCOUNT, SUM(HAMOUNT * -1) AS HAMOUNT, 'Annulation '+RTRIM(HREM) AS COMMENT FROM ac_ahisto WHERE HDOCDATE BETWEEN '" + dP_periodStart.Value.Date.ToString("yyyy-MM-dd") + "' AND '" + dP_periodEnd.Value.Date.ToString("yyyy-MM-dd") + "' GROUP BY BOBDATE,Type,ACCOUNT,COMMENT";
String clipbString;
clipbString = "";
adsreader = adscmd.ExecuteReader();
while (adsreader.Read())
{
clipbString += adsreader.GetValue(0) + "\t" + adsreader.GetValue(1) + "\t" + adsreader.GetValue(2) + "\t" + adsreader.GetValue(3) + "\t" + adsreader.GetValue(4) + "\n";
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
sqlconn.Open();
int periodEnd;
int periodStart;
periodStart = dP_periodStart.Value.Year * 100 + dP_periodStart.Value.Month;
periodEnd = dP_periodEnd.Value.Year * 100 + dP_periodEnd.Value.Month;
SqlCommand sqlcmd = new SqlCommand("SELECT DATEFROMPARTS(Year,12,31) AS Date, CASE WHEN Account IN ('40110000') THEN 'C' WHEN Account IN ('44111000 ') THEN 'F' ELSE 'G' END AS Type, CASE WHEN Account IN ('40110000','44111000 ') THEN 'DIVERS' ELSE Account END AS Account, SUM(Amount) AS Amount, 'Importation du '+ FORMAT(GETDATE(),'dd/MM/yyyy','fr-fr') AS Comment FROM [dbo].[Account_Balance] WHERE Customer_Code = '" + cB_Customer.Text.Substring(0, 6) + "' AND Year*100+Month BETWEEN " + periodStart.ToString() + " AND " + periodEnd.ToString() + "GROUP BY DATEFROMPARTS([Year],12, 31), Account ORDER BY Account", sqlconn);
try
{
var reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
clipbString += reader.GetValue(0) + "\t" + reader.GetValue(1) + "\t" + reader.GetValue(2) + "\t" + reader.GetValue(3) + "\t" + reader.GetValue(4) + "\n";
}
Clipboard.SetText(clipbString.ToString());
}
catch
{ }
sqlconn.Close();
}
public void getData(string db, int fromYear, int toYear)
{
AdsConnection adsconn = new AdsConnection("Data Source=\\\\192.168.5.5:6262\\bob50\\BOB50DATA\\" + db + ";ServerType=REMOTE;TableType=ADT;LockMode=PROPRIETARY;CharType=ADS_ANSI;SecurityMode=IGNORERIGHTS");
AdsCommand adscmd;
AdsDataReader adsreader;
//SqlConnection sqlconn = new SqlConnection("Data Source=srvdwh;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlConnection sqlconn = new SqlConnection("Data Source=SRVSQL\\FISODWH;Initial Catalog=FisoDWH;User ID=dwh_writer;Password=GZXyJmTdKNfx7C4j;");
SqlCommand sqlcmd;
adsconn.Open();
adscmd = adsconn.CreateCommand();
try
{
//adscmd.CommandText = "SELECT '"+db+"'+HID AS Account_ID,'"+db+ "' AS Customer_Code,HID AS Account,YR.YEAR AS Year,CASE WHEN HMONTH=0 THEN MONTH(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount FROM ac_ahisto AH INNER JOIN (SELECT HFYEAR,MAX(HYEAR) AS YEAR FROM ac_ahisto GROUP BY HFYEAR)YR ON AH.HFYEAR=YR.HFYEAR GROUP BY HID,YR.YEAR,Month";
adscmd.CommandText = "SELECT '" + db + "'+HID AS Account_ID,'" + db + "' AS Customer_Code,HID AS Account, RTRIM(HFYEAR) AS Fiscal_Year,CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END AS Year,CASE WHEN HMONTH = 0 THEN Month(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount, COALESCE(COST_ENTITE,'') AS Cost_Center FROM ac_ahisto AH WHERE CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END BETWEEN " + fromYear + " AND " + toYear + " GROUP BY HID,HFYEAR,Year,Month,COST_ENTITE";
adsreader = adscmd.ExecuteReader();
}
catch
{
adscmd.CommandText = "SELECT '" + db + "'+HID AS Account_ID,'" + db + "' AS Customer_Code,HID AS Account, RTRIM(HFYEAR) AS Fiscal_Year,CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END AS Year,CASE WHEN HMONTH = 0 THEN Month(HDOCDATE) ELSE HMONTH END AS Month,ROUND(SUM(HAMOUNT),2) AS Amount, '' AS Cost_Center FROM ac_ahisto AH WHERE CASE WHEN HMONTH = 0 THEN Year(HDOCDATE) ELSE HYEAR END BETWEEN " + fromYear + " AND " + toYear + " GROUP BY HID,HFYEAR,Year,Month";
adsreader = adscmd.ExecuteReader();
}
try {
/*
* while (adsreader.Read())
* {
* Console.WriteLine(adsreader.GetValue(0) + "\t" + adsreader.GetValue(1) + "\t" + adsreader.GetValue(2) + "\t" + adsreader.GetValue(3) + "\t" + adsreader.GetValue(4) + "\t" + adsreader.GetValue(5) + "\t" + adsreader.GetValue(6));
* }
*/
sqlconn.Open();
sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandText = "DELETE FROM dbo.account_balance WHERE Customer_Code = '" + db + "' AND Year BETWEEN " + fromYear + " AND " + toYear;
sqlcmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlconn))
{
bulkCopy.DestinationTableName =
"dbo.account_balance";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(adsreader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
SqlCommand ExecJob = new SqlCommand();
ExecJob.CommandType = CommandType.StoredProcedure;
ExecJob.CommandText = "dbo.sp_update_bs_hier";
ExecJob.Connection = sqlconn;
using (sqlconn)
{
using (ExecJob)
{
ExecJob.ExecuteNonQuery();
}
}
adsconn.Close();
AdsConnection.FlushConnectionPool();
sqlconn.Close();
}
catch (AdsException e)
{
MessageBox.Show(e.Message);
}
}