Пример #1
0
        private void butOK_Click(object sender, System.EventArgs e)
        {
            //if(textDate.errorProvider1.GetError(textDate)!=""){
            //	MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
            //	return;
            //}
            ReportSimpleGrid report=new ReportSimpleGrid();
            report.Query=
                "SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",adjamt "
                +"FROM patient,adjustment "
                +"WHERE patient.patnum=adjustment.patnum "
                +"AND adjustment.adjdate = "+POut.Date(PrefC.GetDate(PrefName.FinanceChargeLastRun))
                +"AND adjustment.adjtype = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'";
            FormQuery2=new FormQuery(report);
            FormQuery2.IsReport=true;
            FormQuery2.SubmitReportQuery();
            report.Title="FINANCE CHARGE REPORT";
            report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
            report.SubTitle.Add("Date of Charges: "+PrefC.GetDate(PrefName.FinanceChargeLastRun).ToShortDateString());
            report.SetColumn(this,0,"Patient Name",180);
            report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right);

            FormQuery2.ShowDialog();
            DialogResult=DialogResult.OK;
        }
Пример #2
0
 private void butPatByZip_Click(object sender,EventArgs e)
 {
     if(!DateIsValid()) {
         return;
     }
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query="SELECT SUBSTR(Zip,1,5) 'Zip Code',COUNT(*) 'Patients' "//Column headings "Zip Code" and "Patients" are provided by the USD 2010 Manual.
         +"FROM patient pat "
         +"WHERE "+DbHelper.Regexp("Zip","^[0-9]{5}")+" "//Starts with five numbers
         +"AND PatNum IN ( "
             +"SELECT PatNum FROM procedurelog "
             +"WHERE ProcStatus="+POut.Int((int)ProcStat.C)+" "
             +"AND DateEntryC >= "+POut.Date(DateFrom)+" "
             +"AND DateEntryC <= "+POut.Date(DateTo)+") "
         +"GROUP BY Zip "
         +"HAVING COUNT(*) >= 10 "//Has more than 10 patients in that zip code for the given time frame.
         +"ORDER BY Zip";
     FormQuery FormQ=new FormQuery(report);
     FormQ.IsReport=true;
     FormQ.SubmitQuery();
       FormQ.textQuery.Text=report.Query;
     report.Title="Patients By ZIP CODE";
     report.SubTitle.Add("From "+DateFrom.ToShortDateString()+" to "+DateTo.ToShortDateString());
     report.Summary.Add("Other Zip Codes: "+Patients.GetZipOther(DateFrom,DateTo));
     report.Summary.Add("Unknown Residence: "+Patients.GetZipUnknown(DateFrom,DateTo));
     report.Summary.Add("TOTAL: "+Patients.GetPatCount(DateFrom,DateTo));
     FormQ.ShowDialog();
 }
Пример #3
0
 private void butOK_Click(object sender, System.EventArgs e)
 {
     Cursor=Cursors.WaitCursor;
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query=@"SELECT procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@" patname,
     procedurelog.ProcDate,
     SUM(procedurelog.ProcFee) ""$sumfee"",
     SUM((SELECT SUM(claimproc.InsPayAmt + claimproc.Writeoff) FROM claimproc WHERE claimproc.ProcNum=procedurelog.ProcNum)) AS
     ""$PaidAndWriteoff""
     FROM procedurelog
     LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
     LEFT JOIN patient ON patient.PatNum=procedurelog.PatNum
     WHERE procedurelog.ProcStatus=2/*complete*/
     AND procedurelog.ProcFee > 0
     GROUP BY procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@",procedurelog.ProcDate
     HAVING ROUND($sumfee,3) < ROUND($PaidAndWriteoff,3)
     ORDER BY patname,ProcDate";
     FormQuery FormQuery2=new FormQuery(report);
     FormQuery2.IsReport=true;
     FormQuery2.SubmitReportQuery();
     report.Title="INSURANCE OVERPAID REPORT";
     report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
     report.SetColumn(this,0,"PatNum",60);
     report.SetColumn(this,1,"Pat Name",150);
     report.SetColumn(this,2,"Date",80);
     report.SetColumn(this,3,"Fee",80,HorizontalAlignment.Right);
     report.SetColumn(this,4,"InsPd+W/O",90,HorizontalAlignment.Right);
     Cursor=Cursors.Default;
     FormQuery2.ShowDialog();
     DialogResult=DialogResult.OK;
 }
Пример #4
0
 private void butOK_Click(object sender, System.EventArgs e)
 {
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query=@"SELECT ScreenDate,ProvName,County,county.CountyCode,
         GradeSchool,school.SchoolCode,PlaceService,GradeLevel,Age,Birthdate,Race,Gender,Urgency,
         HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
         Comments FROM screen
         LEFT JOIN school ON screen.GradeSchool=school.SchoolName
         LEFT JOIN county ON screen.County=county.CountyName
         WHERE ScreenDate >= "+POut.Date(date1.SelectionStart)+" "
         +"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
     FormQuery FormQuery2=new FormQuery(report);
     FormQuery2.textTitle.Text="RawProcedureData"+DateTime.Today.ToString("MMddyyyy");
     //FormQuery2.IsReport=true;
     //FormQuery2.SubmitReportQuery();
     FormQuery2.SubmitQuery();
     FormQuery2.ShowDialog();
     DialogResult=DialogResult.OK;
 }
Пример #5
0
		private void CreateGrouped(ReportSimpleGrid report) {
			//this would require a temporary table to be able to handle capitation.
			report.Query="SELECT definition.ItemName,procedurecode.ProcCode,procedurecode.Descript,";
			if(DataConnection.DBtype==DatabaseType.MySql) {
				report.Query+="Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee ";
			}
			else {//Oracle needs quotes.
				report.Query+="Count(*),AVG(procedurelog.ProcFee) \"$AvgFee\",SUM(procedurelog.ProcFee) AS \"$TotFee\" ";
			}
			report.Query+="FROM procedurelog,procedurecode,definition "
				+"WHERE procedurelog.ProcStatus = '2' "
				+"AND procedurelog.CodeNum=procedurecode.CodeNum "
				+"AND definition.DefNum=procedurecode.ProcCat "
				+whereProv
				+whereClin
				+"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"' "
				+"GROUP BY procedurecode.ProcCode "
				+"ORDER BY definition.ItemOrder,procedurecode.ProcCode";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();			
			report.Title="Procedures By Procedure Code";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else {
				string provNames="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i>0) {
						provNames+=", ";
					}
					provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(provNames);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.SetColumn(this,0,"Category",150);
			report.SetColumn(this,1,"Code",90);
			report.SetColumn(this,2,"Description",180);
			report.SetColumn(this,3,"Quantity",60,HorizontalAlignment.Right);
			report.SetColumn(this,4,"Average Fee",110,HorizontalAlignment.Right);
			report.SetColumn(this,5,"Total Fees",100,HorizontalAlignment.Right);
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #6
0
		private void RunMonthly(){
			dateFrom=PIn.Date(textDateFrom.Text);
			dateTo=PIn.Date(textDateTo.Text);
/*  There are 8 temp tables  
 *  TableCharge: Holds sum of all charges for a certain date.
 *  TableCapWriteoff: Holds capComplete writeoffs which will be subtracted from Charges.  They are subtracted from charges to give the illusion of not so much being charged in the first place. Calculated using dateCP, which should be same as DateProc. 
 *  TableInsWriteoff: (not implemented yet) Writeoffs from claims received. Displayed in separate column so it won't mysteriously change the charges and all reports will match.
 *  TableSched: Holds Scheduled but not charged procedures
 *  TableCapEstWriteoff: (not implemented yet) capEstimate writeoffs. These will be subtracted from scheduled treatment
 *  TablePay: Holds sum of all Patient payments for a certain date.
 *  TableIns: Holds sum of all Insurance payments for a certain date.--- added by SPK 3/16/04
 *  TableAdj: Holds sum of all adjustments for a certain date.
 * GROUP BY is used to group dates together so that amounts are summed for each date
 */
			DataTable TableCharge=     new DataTable();  //charges
			DataTable TableCapWriteoff=new DataTable();  //capComplete writeoffs
			DataTable TableInsWriteoff=new DataTable();  //ins writeoffs
			DataTable TablePay=        new DataTable();  //payments - Patient
			DataTable TableIns=        new DataTable();  //payments - Ins, added SPK 
			DataTable TableAdj=        new DataTable();  //adjustments
			DataTable TableSched=      new DataTable();
		
//TableCharge---------------------------------------------------------------------------------------
/*
Select procdate, sum(procfee) From procedurelog
Group By procdate Order by procdate desc  
*/
			ReportSimpleGrid report=new ReportSimpleGrid();
			string whereProv,whereClin;//used as the provider portion of the where clauses.
				//each whereProv needs to be set up separately for each query
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="procedurelog.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="procedurelog.ClinicNum = 0 ";
					}
					else {
						whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT "+DbHelper.DateColumn("procedurelog.ProcDate")+" ProcDate, "
				+"SUM(procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)) "
				+"FROM procedurelog "
				+"WHERE "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= "+POut.Date(dateFrom)+" "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= "+POut.Date(dateTo)+" "
				+"AND procedurelog.ProcStatus = '2' "//complete
				+whereProv
				+whereClin
				+"GROUP BY "+DbHelper.DateColumn("procedurelog.ProcDate")+" "
				+"ORDER BY "+DbHelper.DateColumn("procedurelog.ProcDate");
			TableCharge=report.GetTempTable();

//NEXT is TableCapWriteoff--------------------------------------------------------------------------
/*
SELECT DateCP, SUM(WriteOff) From claimproc
WHERE Status='7'
GROUP BY DateCP Order by DateCP  
*/
			report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT DateCP, SUM(WriteOff) FROM claimproc WHERE "
				+"DateCP >= "+POut.Date(dateFrom)+" "
				+"AND DateCP <= "+POut.Date(dateTo)+" "
				+"AND Status = '7' "//CapComplete
				+whereProv
				+whereClin
				+" GROUP BY DateCP "
				+"ORDER BY DateCP"; 
			TableCapWriteoff=report.GetTempTable();

//NEXT is TableInsWriteoff--------------------------------------------------------------------------
/*
SELECT DateCP, SUM(WriteOff) From claimproc
WHERE Status='1'
GROUP BY DateCP Order by DateCP  
*/
			report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			if(radioWriteoffPay.Checked){
				report.Query="SELECT DateCP,SUM(WriteOff) FROM claimproc WHERE "
					+"DateCP >= "+POut.Date(dateFrom)+" "
					+"AND DateCP <= "+POut.Date(dateTo)+" "
					+"AND (Status = '1' OR Status = 4) "//Recieved or supplemental. Otherwise, it's only an estimate.
					+whereProv
					+whereClin
					+" GROUP BY DateCP "
					+"ORDER BY DateCP"; 
			}
			else{
				report.Query="SELECT ProcDate,SUM(WriteOff) FROM claimproc WHERE "
					+"ProcDate >= "+POut.Date(dateFrom)+" "
					+"AND ProcDate <= "+POut.Date(dateTo)+" "
					+"AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
					+whereProv
					+whereClin
					+" GROUP BY ProcDate "
					+"ORDER BY ProcDate"; 
			}
			TableInsWriteoff=report.GetTempTable();

// NEXT is TableSched------------------------------------------------------------------------------
/*
SELECT FROM_DAYS(TO_DAYS(Appointment.AptDateTime)) AS
SchedDate,SUM(Procedurelog.procfee) FROM Appointment, Procedurelog 
Where Appointment.aptnum = Procedurelog.aptnum && Appointment.AptStatus = 1
|| Appointment.AptStatus=4 && FROM_DAYS(TO_DAYS(Appointment.AptDateTime)) <= '2003-05-12'    
GROUP BY SchedDate
*/
			report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="procedurelog.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="procedurelog.ClinicNum = 0 ";
					}
					else {
						whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query= "SELECT "+DbHelper.DateColumn("t.AptDateTime")+" SchedDate,SUM(t.Fee-t.WriteoffEstimate) "
				+"FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee,0) Fee,";
			if(PrefC.GetBool(PrefName.ReportPandIschedProdSubtractsWO)) {
				report.Query+="SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
			}
			else {
				report.Query+="0 WriteoffEstimate ";
			}
			report.Query+=
				"FROM appointment "
				+"LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum "
				+"LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum AND Status=6 AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
				+"WHERE (appointment.AptStatus = 1 OR "//stat=scheduled
        +"appointment.AptStatus = 4) "//or stat=ASAP
				+"AND "+DbHelper.DateColumn("appointment.AptDateTime")+" >= "+POut.Date(dateFrom)+" "
				+"AND "+DbHelper.DateColumn("appointment.AptDateTime")+" <= "+POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+" GROUP BY procedurelog.ProcNum) t "//without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
				+"GROUP BY SchedDate "
				+"ORDER BY SchedDate";
			TableSched=report.GetTempTable();

// NEXT is TablePay----------------------------------------------------------------------------------
//must join the paysplit to the payment to eliminate the discounts.
/*
Select paysplit.procdate,sum(paysplit.splitamt) from paysplit,payment where paysplit.procdate < '2003-08-12'
&& paysplit.paynum = payment.paynum
group by procdate union all 
Select claimpayment.checkdate,sum(claimproc.inspayamt) from claimpayment,claimproc where 
claimproc.claimpaymentnum = claimpayment.claimpaymentnum
&& claimpayment.checkdate < '2003-08-12'
group by claimpayment.checkdate order by procdate
*/
			report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="paysplit.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="paysplit.ClinicNum = 0 ";
					}
					else {
						whereClin+="paysplit.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query= "SELECT paysplit.DatePay,SUM(paysplit.splitamt) FROM paysplit "
				+"WHERE paysplit.IsDiscount = '0' "
				+"AND paysplit.DatePay >= "+POut.Date(dateFrom)+" "
				+"AND paysplit.DatePay <= "+POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+" GROUP BY paysplit.DatePay ORDER BY DatePay";
			TablePay=report.GetTempTable();

// NEXT is TableIns, added by SPK 3/16/04-----------------------------------------------------------
/*
Select claimpayment.checkdate,sum(claimproc.inspayamt) from claimpayment,claimproc where 
claimproc.claimpaymentnum = claimpayment.claimpaymentnum
&& claimpayment.checkdate < '2003-08-12'
group by claimpayment.checkdate order by procdate
*/
			report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query= "SELECT claimpayment.CheckDate,SUM(claimproc.InsPayamt) "
				+"FROM claimpayment,claimproc WHERE "
				+"claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
				+"AND (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
				+"AND claimpayment.CheckDate >= " + POut.Date(dateFrom)+" "
				+"AND claimpayment.CheckDate <= " + POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+" GROUP BY claimpayment.CheckDate ORDER BY checkdate";			
			TableIns=report.GetTempTable();
// End TableIns, SPK 3/16/04


// LAST is TableAdj--------------------------------------------------------------------------------
/*
SELECT adjustment.adjdate,CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),adjustment.adjtype,adjustment.adjnote,adjustment.adjamt
FROM adjustment,patient,definition 
WHERE adjustment.adjtype=definition.defnum && patient.patnum=adjustment.patnum
ORDER BY adjdate DESC
*/ 
  		report=new ReportSimpleGrid();
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="adjustment.ClinicNum = 0 ";
					}
					else {
						whereClin+="adjustment.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT adjdate, SUM(adjamt) FROM adjustment WHERE "
				+"adjdate >= "+POut.Date(dateFrom)+" "
				+"AND adjdate <= "+POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+" GROUP BY adjdate ORDER BY adjdate"; 
			TableAdj=report.GetTempTable();

//Now to fill Table Q from the temp tables
			report.TableQ=new DataTable(null);//new table with 10 columns
			for(int i=0;i<10;i++){ //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			decimal[] colTotals=new decimal[report.ColTotal.Length];
			decimal production;
			decimal scheduled;
			decimal adjust;
			decimal inswriteoff; //spk 5/19/05
			decimal totalproduction;
			decimal ptincome;
			decimal insincome;
			decimal totalincome;
			DateTime[] dates=new DateTime[(dateTo-dateFrom).Days+1];
			//MessageBox.Show(dates.Length.ToString());
				//.ToString("yyyy-MM-dd")+"' "
				//	+"&& procdate <= '" + datePickerTo.Value
			for(int i=0;i<dates.Length;i++){//usually 31 days in loop
				//AddDays() starts to calculate a few seconds short over the course of 6 months or so. We make a correction here to work around.
				dates[i]=dateFrom.AddDays(i).AddHours(6).Date;
				//create new row called 'row' based on structure of TableQ
				DataRow row = report.TableQ.NewRow();
				row[0]=dates[i].ToShortDateString();
				row[1]=dates[i].DayOfWeek.ToString();
				production=0;
				scheduled=0;
				adjust=0;
				inswriteoff=0; //spk 5/19/05
				totalproduction=0;
				ptincome=0;//spk
				insincome=0;
				totalincome=0;
				for(int j=0;j<TableCharge.Rows.Count;j++)  {
				  if(dates[i]==(PIn.Date(TableCharge.Rows[j][0].ToString()))){
		 			  production+=PIn.Decimal(TableCharge.Rows[j][1].ToString());
					}
   			}
				for(int j=0;j<TableCapWriteoff.Rows.Count;j++)  {
				  if(dates[i]==(PIn.Date(TableCapWriteoff.Rows[j][0].ToString()))){
		 			  production-=PIn.Decimal(TableCapWriteoff.Rows[j][1].ToString());
					}
   			}
				for(int j=0; j<TableSched.Rows.Count; j++)  {
				  if(dates[i]==(PIn.Date(TableSched.Rows[j][0].ToString()))){
			 	    scheduled+=PIn.Decimal(TableSched.Rows[j][1].ToString());
					}
   			}		
				for(int j=0; j<TableAdj.Rows.Count; j++){
				  if(dates[i]==(PIn.Date(TableAdj.Rows[j][0].ToString()))){
						adjust+=PIn.Decimal(TableAdj.Rows[j][1].ToString());
					}
   			}
				// ***** spk 5/19/05
				for(int j=0; j<TableInsWriteoff.Rows.Count; j++) { // added for ins. writeoff, spk 5/19/05
				  if(dates[i]==(PIn.Date(TableInsWriteoff.Rows[j][0].ToString()))){
						inswriteoff-=PIn.Decimal(TableInsWriteoff.Rows[j][1].ToString());
					}
				}
				for(int j=0; j<TablePay.Rows.Count; j++){
				  if(dates[i]==(PIn.Date(TablePay.Rows[j][0].ToString()))){
						ptincome+=PIn.Decimal(TablePay.Rows[j][1].ToString());
					}																																						 
   			}
				for(int j=0; j<TableIns.Rows.Count; j++){// new TableIns, SPK
					if(dates[i]==(PIn.Date(TableIns.Rows[j][0].ToString()))){
						insincome+=PIn.Decimal(TableIns.Rows[j][1].ToString());
					}																																						 
				}
				totalproduction=production+scheduled+adjust+inswriteoff;
				totalincome=ptincome+insincome;
				row[2]=production.ToString("n");
				row[3]=scheduled.ToString("n");
				row[4]=adjust.ToString("n");
				row[5]=inswriteoff.ToString("n"); //spk 5/19/05
				row[6]=totalproduction.ToString("n");
				row[7]=ptincome.ToString("n");				// spk
				row[8]=insincome.ToString("n");				// spk
				row[9]=totalincome.ToString("n");
				colTotals[2]+=production;
				colTotals[3]+=scheduled;
				colTotals[4]+=adjust;
				colTotals[5]+=inswriteoff; //spk 5/19/05
				colTotals[6]+=totalproduction;
				colTotals[7]+=ptincome;	// spk
				colTotals[8]+=insincome;	// spk
				colTotals[9]+=totalincome;
				report.TableQ.Rows.Add(row);  //adds row to table Q
      }
			for(int i=0;i<colTotals.Length;i++){
				report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
			}
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.ResetGrid();//necessary won't work without
			report.Title="Production and Income";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else{
				string str="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i>0){
						str+=", ";
					}
					str+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(str);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.Summary.Add(
				//=Lan.g(this,"Total Production (Production + Scheduled + Adjustments):")+" "
				//+(colTotals[2]+colTotals[3]
				//+colTotals[4]).ToString("c"); //spk 5/19/05
				Lan.g(this,"Total Production (Production + Scheduled + Adj - Writeoff):")+" "
				+(colTotals[2]+colTotals[3]+colTotals[4]
				+colTotals[5]).ToString("c"));
			report.Summary.Add("");
			report.Summary.Add(
				Lan.g(this,"Total Income (Pt Income + Ins Income):")+" "
				+(colTotals[7]+colTotals[8]).ToString("c"));
			report.ColPos[0]=20;
			report.ColPos[1]=110;
			report.ColPos[2]=190;
			report.ColPos[3]=270;
			report.ColPos[4]=350;
			report.ColPos[5]=420;
			report.ColPos[6]=490;
			report.ColPos[7]=560;
			report.ColPos[8]=630;
			report.ColPos[9]=700;
			report.ColPos[10]=770;
			report.ColCaption[0]=Lan.g(this,"Date");
			report.ColCaption[1]=Lan.g(this,"Weekday");
			report.ColCaption[2]=Lan.g(this,"Production");
			report.ColCaption[3]=Lan.g(this,"Sched");
			report.ColCaption[4]=Lan.g(this,"Adj");
			report.ColCaption[5]=Lan.g(this,"Writeoff");		//spk 5/19/05
			report.ColCaption[6]=Lan.g(this,"Tot Prod");
			report.ColCaption[7]=Lan.g(this,"Pt Income");		// spk
			report.ColCaption[8]=Lan.g(this,"Ins Income");		// spk
			report.ColCaption[9]=Lan.g(this,"Tot Income");
      report.ColAlign[2]=HorizontalAlignment.Right;
			report.ColAlign[3]=HorizontalAlignment.Right;
			report.ColAlign[4]=HorizontalAlignment.Right;
			report.ColAlign[5]=HorizontalAlignment.Right;
			report.ColAlign[6]=HorizontalAlignment.Right;
			report.ColAlign[7]=HorizontalAlignment.Right;
			report.ColAlign[8]=HorizontalAlignment.Right;
			report.ColAlign[9]=HorizontalAlignment.Right;
			FormQuery2.ShowDialog();
		}
		private void butOK_Click(object sender, System.EventArgs e) {
			if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
				MsgBox.Show(this,"At least one provider must be selected.");
				return;
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
					MsgBox.Show(this,"At least one clinic must be selected.");
					return;
				}
			}
			string whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			string whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query="SET @FromDate="+POut.Date(date1.SelectionStart)+", @ToDate="+POut.Date(date2.SelectionStart)+";";
			if(radioWriteoffPay.Checked){
				report.Query+="SELECT "+DbHelper.DateColumn("claimproc.DateCP")+" date,"
					+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
					+"carrier.CarrierName,"
					+"provider.Abbr,";
				if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
					report.Query+="clinic.Description,";
				}
				if(DataConnection.DBtype==DatabaseType.MySql) {
					report.Query+="SUM(claimproc.WriteOff) $amount,";
				}
				else {//Oracle needs quotes.
					report.Query+="SUM(claimproc.WriteOff) \"$amount\",";
				}
				report.Query+="claimproc.ClaimNum "
					+"FROM claimproc "//,insplan,patient,carrier,provider "
					+"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
					+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
					+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
					+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
					+"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
					+"WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
					+whereProv
					+whereClin
					+"AND claimproc.DateCP >= @FromDate "
					+"AND claimproc.DateCP <= @ToDate "
					+"AND claimproc.WriteOff > 0 "
					+"GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
					+"ORDER BY claimproc.DateCP,claimproc.PatNum";
			}
			else{//using procedure date
				report.Query+="SELECT "+DbHelper.DateColumn("claimproc.ProcDate")+" date, "
					+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+", "
					+"carrier.CarrierName, "
					+"provider.Abbr,";
				if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
					report.Query+="clinic.Description,";
				}
				if(DataConnection.DBtype==DatabaseType.MySql) {
					report.Query+="SUM(claimproc.WriteOff) $amount, ";
				}
				else {//Oracle needs quotes.
					report.Query+="SUM(claimproc.WriteOff) \"$amount\", ";
				}
				report.Query+="claimproc.ClaimNum "
					+"FROM claimproc "//,insplan,patient,carrier,provider "
					+"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
					+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
					+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
					+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
					+"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
					+"WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
					+whereProv
					+whereClin
					+"AND claimproc.ProcDate >= @FromDate "
					+"AND claimproc.ProcDate <= @ToDate "
					+"AND claimproc.WriteOff > 0 "
					+"GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
					+"ORDER BY claimproc.ProcDate,claimproc.PatNum";
			}
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();			
			report.Title="Daily Writeoffs";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else {
				string provNames="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i>0) {
						provNames+=", ";
					}
					provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(provNames);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.SetColumn(this,0,"Date",100);
			report.SetColumn(this,1,"Patient Name",150);
			report.SetColumn(this,2,"Carrier",225);
			report.SetColumn(this,3,"Provider",60);
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.SetColumn(this,4,"Clinic",80);
				report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right);
				report.SetColumn(this,6,"",280,HorizontalAlignment.Right);
			}
			else {
				report.SetColumn(this,4,"Amount",75,HorizontalAlignment.Right);
				report.SetColumn(this,5,"",280,HorizontalAlignment.Right);
			}
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #8
0
		private void butOK_Click(object sender, System.EventArgs e) {
			if(  textDateFrom.errorProvider1.GetError(textDateFrom)!=""
				|| textDateTo.errorProvider1.GetError(textDateTo)!=""
				){
				MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
				return;
			}
			if(listProv.SelectedIndices.Count==0) {
				MsgBox.Show(this,"At least one provider must be selected.");
				return;
			}
			if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
				MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
				return;
			}
			DateTime dateFrom=PIn.Date(textDateFrom.Text);
			DateTime dateTo=PIn.Date(textDateTo.Text);
			if(dateTo<dateFrom) {
				MsgBox.Show(this,"To date cannot be before From date.");
				return;
			}
			string whereProv="";
			if(listProv.SelectedIndices[0]!=0){
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="procedurelog.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query=@"SELECT referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) HowMany,
SUM(procedurelog.ProcFee) ""$HowMuch""";
			if(checkAddress.Checked){
				report.Query+=",referral.Title,referral.Address,referral.Address2,referral.City,"
					+"referral.ST,referral.Zip,referral.Specialty";
			}
			report.Query+=@" FROM referral,refattach,procedurelog,patient
WHERE referral.ReferralNum=refattach.ReferralNum
AND procedurelog.PatNum=refattach.PatNum
AND procedurelog.PatNum=patient.PatNum
AND refattach.IsFrom=1
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= "+POut.Date(dateFrom)+" "
				+"AND procedurelog.ProcDate <= "+POut.Date(dateTo)+" "
				+whereProv;
			if(checkNewPat.Checked){
				report.Query+="AND patient.DateFirstVisit >= "+POut.Date(dateFrom)+" "
					+"AND patient.DateFirstVisit <= "+POut.Date(dateTo)+" ";
			}
			report.Query+=@"GROUP BY referral.ReferralNum
ORDER BY HowMany Desc";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();			
			report.Title="Referral Analysis";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			if(listProv.SelectedIndices[0]==0){
				report.SubTitle.Add(Lan.g(this,"All Providers"));
				report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
			}
			else if(listProv.SelectedIndices.Count==1){
				report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
				report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
			}
			else{
				//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
				report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
			}
			if(checkAddress.Checked){
				report.SetColumnPos(this,0,"Last Name",100);
				report.SetColumnPos(this,1,"First Name",200);
				report.SetColumnPos(this,2,"Count",270);
				report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
				report.SetColumnPos(this,4,"Title",390);
				report.SetColumnPos(this,5,"Address",490);
				report.SetColumnPos(this,6,"Add2",530);
				report.SetColumnPos(this,7,"City",590);
				report.SetColumnPos(this,8,"ST",630);
				report.SetColumnPos(this,9,"Zip",680);
				report.SetColumnPos(this,10,"Specialty",880);//off the right side
			}
			else{
				report.SetColumnPos(this,0,"Last Name",100);
				report.SetColumnPos(this,1,"First Name",200);
				report.SetColumnPos(this,2,"Count",270);
				report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
			}
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #9
0
		private void butOK_Click(object sender, System.EventArgs e) {
			if(listAdjType.SelectedIndices.Count==0){
				MessageBox.Show("At least one adjustment type must be selected.");
				return;
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			string types="";
			for(int i=0;i<listAdjType.SelectedIndices.Count;i++){
				if(i==0){
					types+="(";
				}
				else{
					types+="OR ";
				}
				types+="AdjType='"
					+DefC.Short[(int)DefCat.AdjTypes][listAdjType.SelectedIndices[i]].DefNum.ToString()
					+"' ";
			}
			types+=")";
			report.Query=@"
				CREATE TEMPORARY TABLE tempbroken(
					PatNum bigint unsigned NOT NULL,
					NumberBroken smallint NOT NULL,
					PRIMARY KEY (PatNum)
				);
				INSERT INTO tempbroken SELECT PatNum,COUNT(*)
				FROM adjustment WHERE "+types
				+"AND AdjDate >= "+POut.Date(date1.SelectionStart)+" "
				+"AND AdjDate <= " +POut.Date(date2.SelectionStart)+" "
				+@"GROUP BY PatNum;
				SELECT patient.PatNum,MIN(procedurelog.ProcDate) AS ProcDate,
				CONCAT(CONCAT(provider.LName,', '),provider.FName) as ProvName,
				County,county.CountyCode,
				site.Description AS gradeschool,site.Note AS schoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
				patient.PlannedIsDone,tempbroken.NumberBroken
				FROM patient
				LEFT JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
				LEFT JOIN provider ON procedurelog.ProvNum=provider.ProvNum
				LEFT JOIN site ON patient.SiteNum=site.SiteNum
				LEFT JOIN county ON patient.County=county.CountyName
				LEFT JOIN tempbroken ON tempbroken.PatNum=patient.PatNum
				WHERE	(procedurelog.ProcStatus='2'
				AND procedurelog.ProcDate >= "+POut.Date(date1.SelectionStart)+" "
				+"AND procedurelog.ProcDate <= " +POut.Date(date2.SelectionStart)+" )"
				+"OR tempbroken.NumberBroken>0 "
				+@"GROUP BY patient.PatNum
				ORDER By ProcDate;
				DROP TABLE tempbroken;";
/*
CREATE TEMPORARY TABLE tempbroken(
  PatNum mediumint unsigned NOT NULL,
  NumberBroken smallint NOT NULL,
  PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken
SELECT PatNum,COUNT(*)
FROM adjustment
WHERE AdjType='14'
&& AdjDate='2004-05-03'
GROUP BY PatNum;
SELECT MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(provider.LName,', ',provider.FName) as ProvName,
County,county.CountyCode,
GradeSchool,school.SchoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.NextAptNum='-1' AS Done,tempbroken.NumberBroken
FROM patient,procedurelog,provider,tempbroken
LEFT JOIN school ON patient.GradeSchool=school.SchoolName
LEFT JOIN county ON patient.County=county.CountyName
WHERE procedurelog.ProcStatus='2'
&& patient.PatNum=procedurelog.PatNum
&& procedurelog.ProvNum=provider.ProvNum
&& tempbroken.PatNum=patient.PatNum
&& procedurelog.ProcDate >= '2004-05-03'
&& procedurelog.ProcDate <= '2004-05-03'
GROUP BY procedurelog.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;


*/
			FormQuery2=new FormQuery(report);
			FormQuery2.textTitle.Text="RawPopulationData"+DateTime.Today.ToString("MMddyyyy");
			//FormQuery2.IsReport=true;
			//FormQuery2.SubmitReportQuery();			
			FormQuery2.SubmitQuery();
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #10
0
		private void butOK_Click(object sender, System.EventArgs e) {
			if(!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count==0){
				MsgBox.Show(this,"At least one billing type must be selected.");
				return;
			}
			if(!checkProvAll.Checked && listProv.SelectedIndices.Count==0) {
				MsgBox.Show(this,"At least one provider must be selected.");
				return;
			}
			if(textDate.errorProvider1.GetError(textDate) != "") {
				MsgBox.Show(this,"Invalid date.");
				return;
			}
			DateTime asOfDate=PIn.Date(textDate.Text);
			//The aging report always show historical numbers based on the date entered.
			Ledgers.ComputeAging(0,asOfDate,true);
			ReportSimpleGrid report=new ReportSimpleGrid();
			string cmd="SELECT ";
			if(PrefC.GetBool(PrefName.ReportsShowPatNum)){
				cmd+=DbHelper.Concat("Cast(PatNum AS CHAR)","'-'","LName","', '","FName","' '","MiddleI");
			}
			else{
				cmd+=DbHelper.Concat("LName","', '","FName","' '","MiddleI");
			}
			cmd+=",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90"
				+",BalTotal "
				+",InsEst"
				+",BalTotal-InsEst AS ";//\"$pat\" ";
			if(DataConnection.DBtype==DatabaseType.MySql) {
				cmd+="$pat ";
			}
			else { //Oracle needs quotes.
				cmd+="\"$pat\" ";
			}
			cmd+="FROM patient "
				+"WHERE ";
			if(checkExcludeInactive.Checked) {
				cmd+="(patstatus != 2) AND ";
			}
			if(checkBadAddress.Checked) {
				cmd+="(zip !='') AND ";
			}
			if(checkOnlyNeg.Checked){
				cmd+="BalTotal < '-.005' ";
			}
			else{
				if(radioAny.Checked){
					cmd+=
						"(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
				}
				else if(radio30.Checked){
					cmd+=
						"(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
				}
				else if(radio60.Checked){
					cmd+=
						"(Bal_61_90 > '.005' OR BalOver90 > '.005'";
				}
				else if(radio90.Checked){
					cmd+=
						"(BalOver90 > '.005'";
				}
				if(checkIncludeNeg.Checked){
					cmd+=" OR BalTotal < '-.005'";
				}
				cmd+=") ";
			}
			if(!checkBillTypesAll.Checked){
				for(int i=0;i<listBillType.SelectedIndices.Count;i++) {
					if(i==0) {
						cmd+=" AND (billingtype = ";
					}
					else {
						cmd+=" OR billingtype = ";
					}
					cmd+=POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum);
				}
				cmd+=") ";
			}
			if(!checkProvAll.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i==0) {
						cmd+=" AND (PriProv = ";
					}
					else {
						cmd+=" OR PriProv = ";
					}
					cmd+=POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum);
				}
				cmd+=") ";
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				cmd+=" AND ClinicNum IN(";
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i>0) {
						cmd+=",";
					}
					if(Security.CurUser.ClinicIsRestricted) {
						cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
					}
					else {
						if(listClin.SelectedIndices[i]==0) {
							cmd+="0";
						}
						else {
							cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
						}
					}
				}
				cmd+=") ";
			}
			cmd+="ORDER BY LName,FName";
			report.Query=cmd;
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();
			//Recompute aging in a non-historical way, so that the numbers are returned to the way they
			//are normally used in other parts of the program.
			Ledgers.RunAging();
			//if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) {
			//	DataValid.SetInvalid(InvalidType.Prefs);
			//}
			report.Title="AGING OF ACCOUNTS RECEIVABLE REPORT";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add("As of "+textDate.Text);
			if(radioAny.Checked){
				report.SubTitle.Add("Any Balance");
			}
			if(radio30.Checked){
				report.SubTitle.Add("Over 30 Days");
			}
			if(radio60.Checked){
				report.SubTitle.Add("Over 60 Days");
			}
			if(radio90.Checked){
				report.SubTitle.Add("Over 90 Days");
			}
			if(checkBillTypesAll.Checked){
				report.SubTitle.Add("All Billing Types");
			}
			else{
				string subt=DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName;
				for(int i=1;i<listBillType.SelectedIndices.Count;i++){
					subt+=", "+DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName;
				}
				report.SubTitle.Add(subt);
			}
			//report.InitializeColumns(8);
			report.SetColumn(this,0,"GUARANTOR",160);
			report.SetColumn(this,1,"0-30 DAYS",80,HorizontalAlignment.Right);
			report.SetColumn(this,2,"30-60 DAYS",80,HorizontalAlignment.Right);
			report.SetColumn(this,3,"60-90 DAYS",80,HorizontalAlignment.Right);
			report.SetColumn(this,4,"> 90 DAYS",80,HorizontalAlignment.Right);
			report.SetColumn(this,5,"TOTAL",85,HorizontalAlignment.Right);
			report.SetColumn(this,6,"-INS EST",85,HorizontalAlignment.Right);
			report.SetColumn(this,7,"=PATIENT",85,HorizontalAlignment.Right);
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #11
0
 private void butOK_Click(object sender, System.EventArgs e)
 {
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query="SELECT claim.dateservice,claim.claimnum,claim.claimtype,claim.claimstatus,"
         +"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.CarrierName,claim.claimfee "
         +"FROM patient,claim,insplan,carrier "
         +"WHERE patient.patnum=claim.patnum AND insplan.plannum=claim.plannum "
         +"AND insplan.CarrierNum=carrier.CarrierNum "
         +"AND (claim.claimstatus = 'U' OR claim.claimstatus = 'H' OR  claim.claimstatus = 'W')";
     if(radioRange.Checked==true){
         report.Query
             +=" AND claim.dateservice >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' "
             +"AND claim.dateservice <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"'";
     }
     else{
         report.Query
             +=" AND claim.dateservice = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
     }
     FormQuery2=new FormQuery(report);
     FormQuery2.IsReport=true;
     DataTable tempT=report.GetTempTable();
     report.TableQ=new DataTable(null);//new table no name
     for(int i=0;i<6;i++){//add columns
         report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
     }
     report.InitializeColumns();
     for(int i=0;i<tempT.Rows.Count;i++) {//loop through data rows
         DataRow row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
         row[0]=(PIn.Date(tempT.Rows[i][0].ToString())).ToShortDateString();//claim dateservice
         if(PIn.String(tempT.Rows[i][2].ToString())=="P")
       row[1]="Primary";
         if(PIn.String(tempT.Rows[i][2].ToString())=="S")
       row[1]="Secondary";
         if(PIn.String(tempT.Rows[i][2].ToString())=="PreAuth")
       row[1]="PreAuth";
         if(PIn.String(tempT.Rows[i][2].ToString())=="Other")
       row[1]="Other";
         if(tempT.Rows[i][3].ToString().Equals("H"))
           row[2]="Holding";//Claim Status
         else if(tempT.Rows[i][3].ToString().Equals("W"))
             row[2]="WaitQ";//Claim Status, added SPK 7/15/04
         else
           row[2]="Unsent";//Claim Status
         row[3]=tempT.Rows[i][4];//Patient name
         row[4]=tempT.Rows[i][5];//Ins Carrier
         row[5]=PIn.Double(tempT.Rows[i][6].ToString()).ToString("F");//claim fee
         report.ColTotal[5]+=PIn.Double(tempT.Rows[i][6].ToString());
         report.TableQ.Rows.Add(row);
       }
     FormQuery2.ResetGrid();//this is a method in FormQuery2;
     report.Title="Claims Not Sent";
     report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
     if(radioRange.Checked==true){
         report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
     }
     else{
         report.SubTitle.Add(date1.SelectionStart.ToString("d"));
     }
     report.ColPos[0]=20;
     report.ColPos[1]=145;
     report.ColPos[2]=270;
     report.ColPos[3]=395;
     report.ColPos[4]=520;
     report.ColPos[5]=645;
     report.ColPos[6]=770;
     report.ColCaption[0]="Date";
     report.ColCaption[1]="Type";
     report.ColCaption[2]="Claim Status";
     report.ColCaption[3]="Patient Name";
     report.ColCaption[4]="Insurance Carrier";
     report.ColCaption[5]="Amount";
     report.ColAlign[5]=HorizontalAlignment.Right;
     FormQuery2.ShowDialog();
     DialogResult=DialogResult.OK;
 }
Пример #12
0
		private void butOK_Click(object sender, System.EventArgs e) {
			carrier= PIn.String(textBoxCarrier.Text);
			ReportSimpleGrid report=new ReportSimpleGrid();

/*
SELECT insplan.subscriber,insplan.carrier,patient.hmphone,
insplan.groupname FROM insplan,patient WHERE insplan.subscriber=patient.patnum 
&& insplan.carrier like +carrier+'%'
Order By patient.lname,patient.fname

*/
			report.Query= "SELECT carrier.CarrierName"
				+",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS SubscriberName,carrier.Phone,"
				+"insplan.Groupname "
				+"FROM insplan,inssub,patient,carrier "//,patplan "//we only include patplan to make sure insurance is active for a patient.  We don't want any info from patplan.
				+"WHERE inssub.Subscriber=patient.PatNum "
				+"AND inssub.PlanNum=insplan.PlanNum "
				+"AND EXISTS (SELECT * FROM patplan WHERE patplan.InsSubNum=inssub.InsSubNum) "
				//+"AND insplan.PlanNum=patplan.PlanNum "
				//+"AND patplan.PatNum=patient.PatNum "
				//+"AND patplan.Ordinal=1 "
				+"AND carrier.CarrierNum=insplan.CarrierNum "
				+"AND carrier.CarrierName LIKE '"+carrier+"%' "
				+"ORDER BY carrier.CarrierName,patient.LName";
			//Debug.WriteLine(report.Query);
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();			
			report.Title="Insurance Plan List";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SetColumn(this,0,"Carrier Name",230);
			report.SetColumn(this,1,"Subscriber Name",175);
			report.SetColumn(this,2,"Carrier Phone#",175);
			report.SetColumn(this,3,"Group Name",165);
			report.Summary.Add(Lan.g(this,"Total: ")+report.TableQ.Rows.Count.ToString());
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;		
		}
Пример #13
0
 private void butOK_Click(object sender, System.EventArgs e)
 {
     if(  textDateFrom.errorProvider1.GetError(textDateFrom)!=""
         || textDateTo.errorProvider1.GetError(textDateTo)!=""
         ){
         MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
         return;
     }
     if(listProv.SelectedIndices.Count==0) {
         MsgBox.Show(this,"At least one provider must be selected.");
         return;
     }
     if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
         MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
         return;
     }
     DateTime dateFrom=PIn.Date(textDateFrom.Text);
     DateTime dateTo=PIn.Date(textDateTo.Text);
     string whereProv="";
     if(listProv.SelectedIndices[0]!=0){
         for(int i=0;i<listProv.SelectedIndices.Count;i++){
             if(i==0){
                 whereProv+=" WHERE (";
             }
             else{
                 whereProv+="OR ";
             }
             whereProv+="patient.PriProv = "
                 +POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
         }
         whereProv+=") ";
     }
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query=@"SET @pos=0;
     SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
     +DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName")+" refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
     if(DataConnection.DBtype==DatabaseType.MySql) {
         report.Query+="$HowMuch";
     }
     else { //Oracle needs quotes.
         report.Query+="\"$HowMuch\"";
     }
     if(checkAddress.Checked){
         report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
     }
     report.Query+=@" FROM
         (SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
         WHERE ProcStatus=2 GROUP BY PatNum
         HAVING dateFirstProc >= "+POut.Date(dateFrom)+" "
         +"AND DATE(dateFirstProc) <= "+POut.Date(dateTo)+" ) table1 "
         +@"INNER JOIN patient ON table1.PatNum=patient.PatNum
         LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
         LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
         AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
         LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
         +whereProv;
     report.Query+="GROUP BY patient.LName,patient.FName,patient.PatNum,"+DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName");
     if(checkAddress.Checked) {
         report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
     }
     if(checkProd.Checked){
         if(DataConnection.DBtype==DatabaseType.MySql) {
             report.Query+="HAVING $HowMuch > 0 ";
         }
         else {//Oracle needs quotes.
             report.Query+="HAVING \"$HowMuch\" > 0 ";
         }
     }
     report.Query+="ORDER BY dateFirstProc,patient.LName,patient.FName) result";
     FormQuery2=new FormQuery(report);
     FormQuery2.IsReport=true;
     FormQuery2.SubmitReportQuery();
     report.Title="New Patients";
     report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
     if(listProv.SelectedIndices[0]==0){
         report.SubTitle.Add(Lan.g(this,"All Providers"));
         report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
     }
     else if(listProv.SelectedIndices.Count==1){
         report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
         report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
     }
     else{
         //I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
         report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
     }
     report.SetColumnPos(this,0,"#",40);
     report.SetColumnPos(this,1,"Date",120);
     report.SetColumnPos(this,2,"Last Name",210);
     report.SetColumnPos(this,3,"First Name",300);
     report.SetColumnPos(this,4,"Referral",380);
     report.SetColumnPos(this,5,"Production",450,HorizontalAlignment.Right);
     if(checkAddress.Checked){
         report.SetColumnPos(this,6,"Pref'd",500);
         report.SetColumnPos(this,7,"Address",570);
         report.SetColumnPos(this,8,"Add2",630);
         report.SetColumnPos(this,9,"City",680);
         report.SetColumnPos(this,10,"ST",730);
         report.SetColumnPos(this,11,"Zip",880);//off the right side
     }
     FormQuery2.ShowDialog();
     DialogResult=DialogResult.OK;
 }
Пример #14
0
		private void butOK_Click(object sender,EventArgs e) {
			if(textDateStart.errorProvider1.GetError(textDateStart)!="") {
				MsgBox.Show(this,"Please fix data entry errors first.");
				return;
			}
			if(textDateStart.Text=="") {
				MsgBox.Show(this,"Please enter a start date.");
				return;
			}
			if(textDateEnd.errorProvider1.GetError(textDateEnd)!="") {
				MsgBox.Show(this,"Please fix data entry errors first.");
				return;
			}
			if(textDateEnd.Text=="") {
				MsgBox.Show(this,"Please enter an end date.");
				return;
			}
			if(gridCourses.SelectedIndices.Length<1) {
				MsgBox.Show(this,"At least one course must be selected to run a report.  Please select a row from the course grid.");
				return;
			}
			if(gridInstructors.SelectedIndices.Length<1) {
				MsgBox.Show(this,"At least one instructor must be selected to run a report.  Please select a row from the instructor grid.");
				return;
			}
			if(gridStudents.SelectedIndices.Length<1) {
				MsgBox.Show(this,"At least one student must be selected to run a report.  Please select a row from the student grid.");
				return;
			}
			DateTime dateStart=PIn.Date(textDateStart.Text);
			DateTime dateEnd=PIn.Date(textDateEnd.Text);
			string whereCourses="";
			if(!checkAllCourses.Checked) {
				for(int i=0;i<gridCourses.SelectedIndices.Length;i++) {
					if(i==0) {
						whereCourses+=" AND evaluation.SchoolCourseNum IN(";
					}
					whereCourses+=gridCourses.Rows[gridCourses.SelectedIndices[i]].Tag;
					if(i!=gridCourses.SelectedIndices.Length-1) {
						whereCourses+=",";
					}
				}
				whereCourses+=")";
			}
			string whereInstructors="";
			if(!checkAllInstructors.Checked) {
				for(int i=0;i<gridInstructors.SelectedIndices.Length;i++) {
					if(i==0) {
						whereInstructors+=" AND evaluation.InstructNum IN(";
					}
					whereInstructors+=gridInstructors.Rows[gridInstructors.SelectedIndices[i]].Tag;
					if(i!=gridInstructors.SelectedIndices.Length-1) {
						whereInstructors+=",";
					}
				}
				whereInstructors+=")";
			}
			//No checkbox for students
			string whereStudents=" AND evaluation.StudentNum IN(";
			for(int i=0;i<gridStudents.SelectedIndices.Length;i++) {
				whereStudents+=gridStudents.Rows[gridStudents.SelectedIndices[i]].Tag;
				if(i!=gridStudents.SelectedIndices.Length-1) {
					whereStudents+=",";
				}
			}
			whereStudents+=")";
			ReportSimpleGrid report=new ReportSimpleGrid();
			//Evaluations------------------------------------------------------------------------------
			report.Query="SELECT "+DbHelper.Concat("students.LName","', '","students.FName")+" StudentName,evaluation.DateEval,"
				+"courses.CourseID,"+DbHelper.Concat("instructors.LName","', '","instructors.FName")+" InstructorName,"
			  +"evaluation.EvalTitle,gradeScales.ScaleType,evaluation.OverallGradeShowing,evaluation.OverallGradeNumber";
			report.Query+=" FROM evaluation"
			  +" INNER JOIN provider students ON evaluation.StudentNum=students.ProvNum"
				+" INNER JOIN provider instructors ON evaluation.InstructNum=instructors.ProvNum"
				+" INNER JOIN gradingscale gradeScales ON evaluation.GradingScaleNum=gradeScales.GradingScaleNum"
				+" INNER JOIN schoolcourse courses ON evaluation.SchoolCourseNum=courses.SchoolCourseNum"
				+" WHERE evaluation.DateEval BETWEEN "+POut.Date(dateStart)+" AND "+POut.Date(dateEnd)
				+whereCourses
				+whereInstructors
				+whereStudents
				+" ORDER BY StudentName,evaluation.DateEval";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			DataTable table=report.GetTempTable();
			report.TableQ=new DataTable();
			int colI=10;
			for(int i=0;i<colI;i++) { //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			DataRow row;
			for(int i=0;i<table.Rows.Count;i++) {
				row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
				row[0]=table.Rows[i]["StudentName"].ToString();
				row[1]=PIn.Date(table.Rows[i]["DateEval"].ToString()).ToShortDateString();
				row[2]=table.Rows[i]["CourseID"].ToString();
				row[3]=table.Rows[i]["InstructorName"].ToString();
				row[4]=table.Rows[i]["EvalTitle"].ToString();
				switch((EnumScaleType)PIn.Int(table.Rows[i]["ScaleType"].ToString())) {
					case EnumScaleType.PickList:
						row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.PickList);
						break;
					case EnumScaleType.Percentage:
						row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.Percentage);
						break;
					case EnumScaleType.Weighted:
						row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.Weighted);
						break;
				}
				row[6]=table.Rows[i]["OverallGradeShowing"].ToString();
				row[7]=table.Rows[i]["OverallGradeNumber"].ToString();
				report.TableQ.Rows.Add(row);
			}
			FormQuery2.ResetGrid();
			report.Title=Lan.g(this,"Course Average");
			report.SubTitle.Add(dateStart.ToShortDateString()+" - "+dateEnd.ToShortDateString());
			if(checkAllInstructors.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Instructors"));
			}
			if(checkAllCourses.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Courses"));
			}
			report.SetColumn(this,0,"Student",120);
			report.SetColumn(this,1,"Date",80);
			report.SetColumn(this,2,"Course",100);
			report.SetColumn(this,3,"Instructor",120);
			report.SetColumn(this,4,"Evaluation",90);
			report.SetColumn(this,5,"Scale Type",90);
			report.SetColumn(this,6,"Grade Showing",100);
			report.SetColumn(this,7,"Grade Number",100);
			FormQuery2.ShowDialog();
		}
Пример #15
0
		private void butOK_Click(object sender, System.EventArgs e) {
/*
SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),rxpat.rxdate,
rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider
WHERE patient.patnum=rxpat.patnum && provider.provnum=rxpat.provnum		
*/
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query="SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),"+
				"' '),patient.MiddleI),rxpat.rxdate,"
				+"rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider "
				+"WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum ";

			if(radioPatient.Checked==true){
				report.Query
					+="AND patient.lname like '"+textBoxInput.Text+"%'"
	        +" ORDER BY patient.lname,patient.fname,rxpat.rxdate";		
			}
			else{
				report.Query
					+="AND rxpat.drug like '"+textBoxInput.Text+"%'"
			    +" ORDER BY patient.lname,rxpat.drug,rxpat.rxdate";
			}

			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();			
			report.Title="Prescriptions";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			if(radioPatient.Checked==true){
				report.SubTitle.Add("By Patient");
			}
			else{
				report.SubTitle.Add("By Drug");
			}			
			report.SetColumn(this,0,"Patient Name",120);
			report.SetColumn(this,1,"Date",95);
			report.SetColumn(this,2,"Drug Name",100);
			report.SetColumn(this,3,"Sig",300);
			report.SetColumn(this,4,"Disp",100);
			report.SetColumn(this,5,"Prov Name",100);
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #16
0
		private void RunDaily(){
			dateFrom=PIn.Date(textDateFrom.Text);
			dateTo=PIn.Date(textDateTo.Text);
			//Date
			//PatientName
			//Description
			//Prov
			//Clinic
			//Production
			//Adjustments
			//Pt Income
			//Ins Income
			//last column is a unique id that is not displayed
			string whereProv="";
			if(!checkAllProv.Checked){
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="procedurelog.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			string whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="procedurelog.ClinicNum = 0 ";
					}
					else {
						whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			//Procedures------------------------------------------------------------------------------
			report.Query="(SELECT "
				+DbHelper.DateColumn("procedurelog.ProcDate")+" procdate,"
				+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf,"
				+"procedurecode.Descript,"
				+"provider.Abbr,"
				+"procedurelog.ClinicNum,"
				+"procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)-IFNULL(SUM(claimproc.WriteOff),0) ";//if no writeoff, then subtract 0
			if(DataConnection.DBtype==DatabaseType.MySql) {
				report.Query+="$fee,"
				+"0 $Adj,"
				+"0 $InsW,"
				+"0 $PtInc,"
				+"0 $InsInc,";
			}
			else {//Oracle needs quotes.
				report.Query+="\"$fee\","
				+"0 \"$Adj\","
				+"0 \"$InsW\","
				+"0 \"$PtInc\","
				+"0 \"$InsInc\",";
			}
			report.Query+="procedurelog.ProcNum "
				+"FROM patient,procedurecode,provider,procedurelog "
				+"LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
				+"AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
				+"WHERE procedurelog.ProcStatus = '2' "
				+"AND patient.PatNum=procedurelog.PatNum "
				+"AND procedurelog.CodeNum=procedurecode.CodeNum "
				+"AND provider.ProvNum=procedurelog.ProvNum "
				+whereProv
				+whereClin
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= " +POut.Date(dateFrom)+" "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= " +POut.Date(dateTo)+" "
				+"GROUP BY procedurelog.ProcNum "
				+") UNION ALL (";
			//Adjustments-----------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="adjustment.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="adjustment.ClinicNum = 0 ";
					}
					else {
						whereClin+="adjustment.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query+="SELECT "
				+"adjustment.AdjDate,"
				+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
				+"definition.ItemName,"
				+"provider.Abbr,"
				+"adjustment.ClinicNum,"
				+"0,"
				+"adjustment.AdjAmt,"
				+"0,"
				+"0,"
				+"0,"
				+"adjustment.AdjNum "
				+"FROM adjustment,patient,definition,provider "
				+"WHERE adjustment.AdjType=definition.DefNum "
				+"AND provider.ProvNum=adjustment.ProvNum "
			  +"AND patient.PatNum=adjustment.PatNum "
				+whereProv
				+whereClin
				+"AND adjustment.AdjDate >= "+POut.Date(dateFrom)+" "
				+"AND adjustment.AdjDate <= "+POut.Date(dateTo)
				+") UNION ALL (";
			//Insurance Writeoff----------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			if(radioWriteoffPay.Checked){
				report.Query+="SELECT claimproc.DateCP,"
					+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
					+"CONCAT(CONCAT(procedurecode.AbbrDesc,' '),carrier.CarrierName),"//AbbrDesc might be null, which is ok.
					+"provider.Abbr,"
					+"claimproc.ClinicNum,"
					+"0,"
					+"0,"
					+"-SUM(claimproc.WriteOff),"
					+"0,"
					+"0,"
					+"claimproc.ClaimNum "
					+"FROM claimproc "
					+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
					+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
					+"LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
					+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
					+"LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum "
					+"LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
					+"WHERE (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
					+whereProv
					+whereClin
					+"AND claimproc.WriteOff > '.0001' "
					+"AND claimproc.DateCP >= "+POut.Date(dateFrom)+" "
					+"AND claimproc.DateCP <= "+POut.Date(dateTo)+" ";
			}
			else{
				report.Query+="SELECT claimproc.ProcDate,"
					+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
					+"CONCAT(CONCAT(procedurecode.AbbrDesc,' '),carrier.CarrierName),"
					+"provider.Abbr,"
					+"claimproc.ClinicNum,"
					+"0,"
					+"0,"
					+"-SUM(claimproc.WriteOff),"
					+"0,"
					+"0,"
					+"claimproc.ClaimNum "
					+"FROM claimproc "
					+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
					+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
					+"LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
					+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
					+"LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum "
					+"LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
					+"WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) "//received or supplemental or notreceived
					+whereProv
					+whereClin
					+"AND claimproc.WriteOff > '.0001' "
					+"AND claimproc.ProcDate >= "+POut.Date(dateFrom)+" "
					+"AND claimproc.ProcDate <= "+POut.Date(dateTo)+" ";
			}
			report.Query+="GROUP BY claimproc.ClaimProcNum"
				+") UNION ALL (";
			//Patient Income------------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="paysplit.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="paysplit.ClinicNum = 0 ";
					}
					else {
						whereClin+="paysplit.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query+="SELECT "
				+"paysplit.DatePay,"
				//+"GROUP_CONCAT(DISTINCT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI)),"
				+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
				+"definition.ItemName,"
				//+"GROUP_CONCAT(DISTINCT provider.Abbr),"
				+"provider.Abbr,"
				+"paysplit.ClinicNum,"
				+"0,"
				+"0,"
				+"0,"
				+"SUM(paysplit.SplitAmt),"
				+"0,"
				+"payment.PayNum "
				+"FROM paysplit "//can't do cartesian join because income transfers would be excluded
				+"LEFT JOIN payment ON payment.PayNum=paysplit.PayNum "
				+"LEFT JOIN patient ON patient.PatNum=paysplit.PatNum "
				+"LEFT JOIN provider ON provider.ProvNum=paysplit.ProvNum "
				+"LEFT JOIN definition ON payment.PayType=definition.DefNum "
				//notice that patient and prov are accurate, but if more than one, then only one shows//should be 'fixed'
				+"WHERE payment.PayDate >= "+POut.Date(dateFrom)+" "
				+"AND payment.PayDate <= "+POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				//+"GROUP BY payment.PayNum"
				+"GROUP BY paysplit.PatNum,paysplit.ProvNum,paysplit.ClinicNum,PayType,paysplit.DatePay"
				+") UNION ALL (";
			//Insurance Income----------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query+="SELECT "
				+"claimpayment.CheckDate,"
				+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
				+"carrier.CarrierName,"
				+"provider.Abbr,"
				+"claimproc.ClinicNum,"
				+"0,"
				+"0,"
				+"0,"
				+"0,"
				+"SUM(claimproc.InsPayAmt),"
				+"claimproc.ClaimNum "
				+"FROM claimproc,insplan,patient,carrier,provider,claimpayment "
				//claimpayment date is used because DateCP was not forced to be the same until version 3.0
				+"WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
				+"AND provider.ProvNum=claimproc.ProvNum "
				+"AND claimproc.PlanNum = insplan.PlanNum "
				+"AND claimproc.PatNum = patient.PatNum "
				+"AND carrier.CarrierNum = insplan.CarrierNum "
				+whereProv
				+whereClin
				+"AND (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
				+"AND claimpayment.CheckDate >= "+POut.Date(dateFrom)+" "
				+"AND claimpayment.CheckDate <= "+POut.Date(dateTo)+" "
				//+"GROUP BY claimproc.ClaimNum"
				+"GROUP BY claimproc.PatNum,claimproc.ProvNum,claimproc.PlanNum,claimproc.ClinicNum,claimpayment.CheckDate"//same as claimproc.DateCP
				+") ORDER BY "+DbHelper.UnionOrderBy("procdate",1)+","+DbHelper.UnionOrderBy("namelf",2);
			//MessageBox.Show(report.Query);
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			DataTable table=report.GetTempTable();
			report.TableQ=new DataTable(null);
			int colI=10;
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				colI=11;
			}
			for(int i=0;i<colI;i++) { //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			decimal[] colTotals=new decimal[report.ColTotal.Length];
			DataRow row;
			Decimal dbl;
			for(int i=0;i<table.Rows.Count;i++) {
				row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
				row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
				row[1]=table.Rows[i][1].ToString();//name
				row[2]=table.Rows[i][2].ToString();//desc
				row[3]=table.Rows[i][3].ToString();//prov
				colI=4;
				if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
					row[colI]=Clinics.GetDesc(PIn.Long(table.Rows[i][4].ToString()));//clinic
					colI++;
				}
				dbl=PIn.Decimal(table.Rows[i][5].ToString());//Prod
				row[colI]=dbl.ToString("n");
				colTotals[colI]+=dbl;
				colI++;
				dbl=PIn.Decimal(table.Rows[i][6].ToString());//Adj
				row[colI]=dbl.ToString("n");
				colTotals[colI]+=dbl;
				colI++;
				dbl=PIn.Decimal(table.Rows[i][7].ToString());//Writeoff
				row[colI]=dbl.ToString("n");
				colTotals[colI]+=dbl;
				colI++;
				dbl=PIn.Decimal(table.Rows[i][8].ToString());//PtInc
				row[colI]=dbl.ToString("n");
				colTotals[colI]+=dbl;
				colI++;
				dbl=PIn.Decimal(table.Rows[i][9].ToString());//InsInc
				row[colI]=dbl.ToString("n");
				colTotals[colI]+=dbl;
				colI++;
				row[colI]=table.Rows[i][10].ToString();
				report.TableQ.Rows.Add(row);
			}
			for(int i=0;i<colTotals.Length;i++){
				report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
			}
			FormQuery2.ResetGrid();
			//FormQuery2.SubmitReportQuery();
			report.Title="Daily Production and Income";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else {
				string provNames="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i>0) {
						provNames+=", ";
					}
					provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(provNames);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.IsLandscape=true;
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.SetColumn(this,0,"Date",80);
			report.SetColumn(this,1,"Patient Name",130);
			report.SetColumn(this,2,"Description",165);
			report.SetColumn(this,3,"Prov",55);
			colI=4;
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.SetColumn(this,colI,"Clinic",70);
				colI++;
			}
			report.SetColumn(this,colI,"Production",65,HorizontalAlignment.Right);
			colI++;
			report.SetColumn(this,colI,"Adjust",70,HorizontalAlignment.Right);
			colI++;
			report.SetColumn(this,colI,"Writeoff",65,HorizontalAlignment.Right);
			colI++;
			report.SetColumn(this,colI,"Pt Income",65,HorizontalAlignment.Right);
			colI++;
			report.SetColumn(this,colI,"Ins Income",65,HorizontalAlignment.Right);
			colI++;
			report.SetColumn(this,colI,"",540,HorizontalAlignment.Right);
			decimal total;
			if(PrefC.GetBool(PrefName.EasyNoClinics)) {
				total=colTotals[4]+colTotals[5]+colTotals[6];
			}
			else {
				total=colTotals[5]+colTotals[6]+colTotals[7];
			}
			report.Summary.Add(Lan.g(this,"Total Production (Production + Adjustments - Writeoffs):")+" "+total.ToString("c"));
			report.Summary.Add("");
			if(PrefC.GetBool(PrefName.EasyNoClinics)) {
				total=colTotals[7]+colTotals[8];
			}
			else {
				total=colTotals[8]+colTotals[9];
			}
			report.Summary.Add(Lan.g(this,"Total Income (Pt Income + Ins Income):")+" "+total.ToString("c"));
			FormQuery2.ShowDialog();
		}
Пример #17
0
		private void RunAnnual(){
			dateFrom=PIn.Date(textDateFrom.Text);
			dateTo=PIn.Date(textDateTo.Text);
			/*  There are 4 temp tables  
			*  TableProduction: Sum of all charges for each month - CapComplete Writeoffs
			*  TableAdj: Sum of all adjustments for each month
			*  TablePay: Sum of all Patient payments for each month
			*  TableIns: Sum of all Insurance payments for each month
			* GROUP BY is used to group dates together so that amounts are summed for each month
			*/
			DataTable TableProduction= new DataTable();
			DataTable TableAdj=        new DataTable();
			DataTable TableInsWriteoff=new DataTable();  //ins writeoffs, added spk 5/19/05
			DataTable TablePay=        new DataTable();
			DataTable TableIns=        new DataTable();
			//Month
			//Production
			//Adjustments
			//InsWriteoff
			//Total Production
			//Pt Income
			//Ins Income
			//Total Income
			ReportSimpleGrid report=new ReportSimpleGrid();
			//Procedures------------------------------------------------------------------------------
			string whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="procedurelog.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			string whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="procedurelog.ClinicNum = 0 ";
					}
					else {
						whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT "
				+"procedurelog.ProcDate,"
				+"SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0) "
				+"FROM procedurelog "
				+"LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
				+"AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
				+"WHERE procedurelog.ProcStatus = '2' "
				+whereProv
				+whereClin
				+"AND procedurelog.ProcDate >= " +POut.Date(dateFrom)+" "
				+"AND procedurelog.ProcDate <= " +POut.Date(dateTo)+" "
				+"GROUP BY MONTH(procedurelog.ProcDate)";
			//MessageBox.Show(report.Query);
			TableProduction=report.GetTempTable();
			//Adjustments----------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="adjustment.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="adjustment.ClinicNum = 0 ";
					}
					else {
						whereClin+="adjustment.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT "
				+"adjustment.AdjDate,"
				+"SUM(adjustment.AdjAmt) "
				+"FROM adjustment "
				+"WHERE adjustment.AdjDate >= "+POut.Date(dateFrom)+" "
				+"AND adjustment.AdjDate <= "+POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+"GROUP BY MONTH(adjustment.AdjDate)";
			TableAdj=report.GetTempTable();
			//TableInsWriteoff--------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			if(radioWriteoffPay.Checked){
				report.Query="SELECT "
					+"claimproc.DateCP," 
					+"SUM(claimproc.WriteOff) "
					+"FROM claimproc "
					+"WHERE claimproc.DateCP >= "+POut.Date(dateFrom)+" "
					+"AND claimproc.DateCP <= "+POut.Date(dateTo)+" "
					+whereProv
					+whereClin
					+"AND (claimproc.Status=1 OR claimproc.Status=4) "//Received or supplemental
					+"GROUP BY MONTH(claimproc.DateCP)";
			}
			else{
				report.Query="SELECT "
					+"claimproc.ProcDate," 
					+"SUM(claimproc.WriteOff) "
					+"FROM claimproc "
					+"WHERE claimproc.ProcDate >= "+POut.Date(dateFrom)+" "
					+"AND claimproc.ProcDate <= "+POut.Date(dateTo)+" "
					+whereProv
					+whereClin
					+"AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
					+"GROUP BY MONTH(claimproc.ProcDate)";
			}
			TableInsWriteoff=report.GetTempTable();
			//PtIncome--------------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="paysplit.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="paysplit.ClinicNum = 0 ";
					}
					else {
						whereClin+="paysplit.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query="SELECT "
				+"paysplit.DatePay,"
				+"SUM(paysplit.SplitAmt) "
				+"FROM paysplit "
				+"WHERE paysplit.IsDiscount=0 "//AND paysplit.PayNum=payment.PayNum "
				+whereProv
				+whereClin
				+"AND paysplit.DatePay >= "+POut.Date(dateFrom)+" "
				+"AND paysplit.DatePay <= "+POut.Date(dateTo)+" "
				+"GROUP BY MONTH(paysplit.DatePay)";
			TablePay=report.GetTempTable();
			//InsIncome---------------------------------------------------------------------------------
			whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i==0){
						whereProv+=" AND (";
					}
					else{
						whereProv+="OR ";
					}
					whereProv+="claimproc.ProvNum = "
						+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			whereClin="";
			if(!checkAllClin.Checked) {
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i==0) {
						whereClin+=" AND (";
					}
					else {
						whereClin+="OR ";
					}
					if(listClin.SelectedIndices[i]==0) {
						whereClin+="claimproc.ClinicNum = 0 ";
					}
					else {
						whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
					}
				}
				whereClin+=") ";
			}
			report.Query= "SELECT claimpayment.CheckDate,SUM(claimproc.InsPayamt) "
				+"FROM claimpayment,claimproc WHERE "
				+"claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
				+"AND claimpayment.CheckDate >= " + POut.Date(dateFrom)+" "
				+"AND claimpayment.CheckDate <= " + POut.Date(dateTo)+" "
				+whereProv
				+whereClin
				+" GROUP BY claimpayment.CheckDate ORDER BY checkdate";
			TableIns=report.GetTempTable(); 
			report.TableQ=new DataTable(null);//new table with 7 columns
			for(int i=0;i<8;i++){ //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			decimal[] colTotals=new decimal[report.ColTotal.Length];
			decimal production;
			decimal adjust;
			decimal inswriteoff;	//spk 5/19/05
			decimal totalproduction;
			decimal ptincome;
			decimal insincome;
			decimal totalincome;
			//lenth of array is number of months between the two dates plus one.
			//MessageBox.Show((dateTo.Year*12+dateTo.Month-dateFrom.Year*12-dateFrom.Month+1).ToString());
			DateTime[] dates=new DateTime[dateTo.Year*12+dateTo.Month-dateFrom.Year*12-dateFrom.Month+1];//1st of each month
			//MessageBox.Show(dates.Length.ToString());
				//.ToString("yyyy-MM-dd")+"' "
				//	+"&& procdate <= '" + datePickerTo.Value
			for(int i=0;i<dates.Length;i++){//usually 12 months in loop
				dates[i]=dateFrom.AddMonths(i);//only the month and year are important
				//create new row called 'row' based on structure of TableQ
				DataRow row=report.TableQ.NewRow();
				row[0]=dates[i].ToString("MMM yy");
				production=0;
				adjust=0;
				inswriteoff=0;	//spk 5/19/05
				totalproduction=0;
				ptincome=0;
				insincome=0;
				totalincome=0;
				for(int j=0;j<TableProduction.Rows.Count;j++)  {
				  if(dates[i].Year==PIn.Date(TableProduction.Rows[j][0].ToString()).Year
						&& dates[i].Month==PIn.Date(TableProduction.Rows[j][0].ToString()).Month){
		 			  production+=PIn.Decimal(TableProduction.Rows[j][1].ToString());
					}
   			}
				for(int j=0;j<TableAdj.Rows.Count; j++){
				  if(dates[i].Year==PIn.Date(TableAdj.Rows[j][0].ToString()).Year
						&& dates[i].Month==PIn.Date(TableAdj.Rows[j][0].ToString()).Month){
						adjust+=PIn.Decimal(TableAdj.Rows[j][1].ToString());
					}
   			}
				for(int j=0;j<TableInsWriteoff.Rows.Count; j++){
					if(dates[i].Year==PIn.Date(TableInsWriteoff.Rows[j][0].ToString()).Year
						&& dates[i].Month==PIn.Date(TableInsWriteoff.Rows[j][0].ToString()).Month){
						inswriteoff-=PIn.Decimal(TableInsWriteoff.Rows[j][1].ToString());
					}
				}
				for(int j=0;j<TablePay.Rows.Count; j++){
				  if(dates[i].Year==PIn.Date(TablePay.Rows[j][0].ToString()).Year
						&& dates[i].Month==PIn.Date(TablePay.Rows[j][0].ToString()).Month){
						ptincome+=PIn.Decimal(TablePay.Rows[j][1].ToString());
					}																																						 
   			}
				for(int j=0; j<TableIns.Rows.Count; j++){//
					if(dates[i].Year==PIn.Date(TableIns.Rows[j][0].ToString()).Year
						&& dates[i].Month==PIn.Date(TableIns.Rows[j][0].ToString()).Month){
						insincome+=PIn.Decimal(TableIns.Rows[j][1].ToString());
					}																																						 
				}
				totalproduction=production+adjust+inswriteoff;
				totalincome=ptincome+insincome;
				row[1]=production.ToString("n");
				row[2]=adjust.ToString("n");
				row[3]=inswriteoff.ToString("n");
				row[4]=totalproduction.ToString("n");
				row[5]=ptincome.ToString("n");
				row[6]=insincome.ToString("n");		
				row[7]=totalincome.ToString("n");
				colTotals[1]+=production;
				colTotals[2]+=adjust;	
				colTotals[3]+=inswriteoff;
				colTotals[4]+=totalproduction;	
				colTotals[5]+=ptincome;	
				colTotals[6]+=insincome;	
				colTotals[7]+=totalincome;
				report.TableQ.Rows.Add(row);  //adds row to table Q
      }
			for(int i=0;i<colTotals.Length;i++){
				report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
			}
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.ResetGrid();//necessary won't work without
			report.Title=Lan.g(this,"Annual Production and Income");
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else{
				string str="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++){
					if(i>0){
						str+=", ";
					}
					str+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(str);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.ColPos[0]=20;
			report.ColPos[1]=120;
			report.ColPos[2]=210;
			report.ColPos[3]=300;
			report.ColPos[4]=390;
			report.ColPos[5]=480;
			report.ColPos[6]=570;
			report.ColPos[7]=660;
			report.ColPos[8]=750;
			//Month
			//Production
			//Adjustments
			//Total Production
			//Pt Income
			//Ins Income
			//Total Income
			report.ColCaption[0]=Lan.g(this,"Month");
			report.ColCaption[1]=Lan.g(this,"Production");
			report.ColCaption[2]=Lan.g(this,"Adjustments");
			report.ColCaption[3]=Lan.g(this,"Writeoff");	//spk
			report.ColCaption[4]=Lan.g(this,"Tot Prod");
			report.ColCaption[5]=Lan.g(this,"Pt Income");
			report.ColCaption[6]=Lan.g(this,"Ins Income");
			report.ColCaption[7]=Lan.g(this,"Total Income");
			report.ColAlign[1]=HorizontalAlignment.Right;
      report.ColAlign[2]=HorizontalAlignment.Right;
			report.ColAlign[3]=HorizontalAlignment.Right;
			report.ColAlign[4]=HorizontalAlignment.Right;
			report.ColAlign[5]=HorizontalAlignment.Right;
			report.ColAlign[6]=HorizontalAlignment.Right;
			report.ColAlign[7]=HorizontalAlignment.Right;
			FormQuery2.ShowDialog();
		}
Пример #18
0
		private void butReport_Click(object sender,EventArgs e) {
			if(!Security.IsAuthorized(Permissions.UserQuery)) {
				return;
			}
			//Basically a preview of gridMain (every employee on one page), allow user to export as excel sheet or print it.
			string query=ClockEvents.GetTimeCardManageCommand(DateStart,DateStop,true);//true to get extra columns for printing.
			ReportSimpleGrid rsg=new ReportSimpleGrid();
			rsg.Query=query;
			FormQuery FormQ=new FormQuery(rsg);
			FormQ.textQuery.Text=query;
			FormQ.SubmitQuery();
			FormQ.ShowDialog();
		}
Пример #19
0
        private void butOK_Click(object sender,System.EventArgs e)
        {
            string bDate;
            string eDate;
            decimal rcvStart = 0;
            decimal rcvProd = 0;
            decimal rcvAdj = 0;
            decimal rcvWriteoff = 0;
            decimal rcvPayment = 0;
            decimal rcvInsPayment = 0;
            decimal runningRcv = 0;
            decimal rcvDaily = 0;
            decimal[] ColTotal=new decimal[8];
            string wMonth;
            string wYear;
            string wDay = "01";
            string wDate;
            // Get the year / month and instert the 1st of the month for stop point for calculated running balance
            wYear = date1.SelectionStart.Year.ToString();
            wMonth = date1.SelectionStart.Month.ToString();
            if(wMonth.Length<2) {
                wMonth = "0" + wMonth;
            }
            wDate = wYear +"-"+ wMonth +"-"+ wDay;

            ReportSimpleGrid report = new ReportSimpleGrid();
            //
            // Create temperary tables for sorting data
            //
            DataTable TableCharge = new DataTable();  //charges
            DataTable TableCapWriteoff = new DataTable();  //capComplete writeoffs
            DataTable TableInsWriteoff = new DataTable();  //ins writeoffs
            DataTable TablePay = new DataTable();  //payments - Patient
            DataTable TableIns = new DataTable();  //payments - Ins, added SPK
            DataTable TableAdj = new DataTable();  //adjustments

            //
            // Main Loop:  This will loop twice 1st loop gets running balance to start of month selected
            //             2nd will break the numbers dow by day and calculate the running balances
            //
            for(int j = 0;j <= 1;j++) {
                if(j == 0) {
                    bDate = "0001-01-01";
                    eDate = wDate;
                }
                else {
                    bDate = wDate;
                    eDate = POut.Date(date1.SelectionStart.AddDays(1)).Substring(1,10);// Needed because all Queries are < end date to get correct Starting AR
                }
                string whereProv;//used as the provider portion of the where clauses.
                //each whereProv needs to be set up separately for each query
                string whereProvx;  //Extended for more than 4 names
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "procedurelog.ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                report.Query = "SELECT procedurelog.ProcDate, "
                + "SUM(procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)) "
                + "FROM procedurelog "
                + "WHERE procedurelog.ProcDate >= '" + bDate + "' "
                + "AND procedurelog.ProcDate < '" + eDate + "' "
                + "AND procedurelog.ProcStatus = '2' "
                + whereProv
                + "GROUP BY procedurelog.ProcDate "
                + "ORDER BY procedurelog.ProcDate";
                TableCharge = report.GetTempTable();
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "claimproc.ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                if(radioWriteoffPay.Checked) {
                    report.Query = "SELECT DateCP, "
                    + "SUM(WriteOff) FROM claimproc WHERE "
                    + "DateCP >= '" + bDate + "' "
                    + "AND DateCP < '" + eDate + "' "
                    + "AND Status = '7' "//CapComplete
                    + whereProv
                    + " GROUP BY DateCP "
                    + "ORDER BY DateCP";
                }
                else {
                    report.Query = "SELECT ProcDate, "
                    + "SUM(WriteOff) FROM claimproc WHERE "
                    + "ProcDate >= '" + bDate + "' "
                    + "AND ProcDate < '" + eDate + "' "
                    + "AND Status = '7' "//CapComplete
                    + whereProv
                    + " GROUP BY ProcDate "
                    + "ORDER BY ProcDate";
                }

                TableCapWriteoff = report.GetTempTable();
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                if(radioWriteoffPay.Checked) {
                    report.Query = "SELECT DateCP, "
                    + "SUM(WriteOff) FROM claimproc WHERE "
                    + "DateCP >= '" + bDate + "' "
                    + "AND DateCP < '" + eDate + "' "
                    + "AND (Status = '1' OR Status = 4) "//Recieved or supplemental. Otherwise, it's only an estimate.
                    + whereProv
                    + " GROUP BY DateCP "
                    + "ORDER BY DateCP";
                }
                else {
                    report.Query = "SELECT ProcDate, "
                    + "SUM(WriteOff) FROM claimproc WHERE "
                    + "ProcDate >= '" + bDate + "' "
                    + "AND ProcDate < '" + eDate + "' "
                    + "AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
                    + whereProv
                    + " GROUP BY ProcDate "
                    + "ORDER BY ProcDate";
                }
                TableInsWriteoff = report.GetTempTable();
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "paysplit.ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                report.Query = "SELECT paysplit.DatePay,SUM(paysplit.splitamt) FROM paysplit "
                + "WHERE paysplit.IsDiscount = '0' "
                + "AND paysplit.DatePay >= '" + bDate + "' "
                + "AND paysplit.DatePay < '" + eDate + "' "
                + whereProv
                + " GROUP BY paysplit.DatePay ORDER BY DatePay";
                TablePay = report.GetTempTable();
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "claimproc.ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                report.Query = "SELECT DateCP,SUM(InsPayamt) "
                + "FROM claimproc WHERE "
                + "Status IN (1,4) "//received or supplemental
                + "AND DateCP >= '" + bDate + "' "
                + "AND DateCP < '" + eDate + "' "
                + whereProv
                + " GROUP BY DateCP ORDER BY DateCP";
                TableIns = report.GetTempTable();
                whereProv = "";
                if(listProv.SelectedIndices[0] != 0) {
                    for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                        if(i == 0) {
                            whereProv += " AND (";
                        }
                        else {
                            whereProv += "OR ";
                        }
                        whereProv += "ProvNum = "
                            + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
                    }
                    whereProv += ") ";
                }
                report.Query = "SELECT adjdate, SUM(adjamt) FROM adjustment WHERE "
                + "adjdate >= '" + bDate + "' "
                + "AND adjdate < '" + eDate + "' "
                + whereProv
                + " GROUP BY adjdate ORDER BY adjdate";
                TableAdj = report.GetTempTable();
                //1st Loop Calculate running Accounts Receivable upto the 1st of the Month Selected
                //2nd Loop Calculate the Daily Accounts Receivable upto the Date Selected
                //Finaly Generate Report showing the breakdown upto the date specified with totals for what is on the report
                if(j == 0) {
                    for(int k = 0;k < TableCharge.Rows.Count;k++) {
                        rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString());
                    }
                    for(int k = 0;k < TableCapWriteoff.Rows.Count;k++) {
                        rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString());
                    }
                    for(int k = 0;k < TableInsWriteoff.Rows.Count;k++) {
                        rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString());
                    }
                    for(int k = 0;k < TablePay.Rows.Count;k++) {
                        rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString());
                    }
                    for(int k = 0;k < TableIns.Rows.Count;k++) {
                        rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString());
                    }
                    for(int k = 0;k < TableAdj.Rows.Count;k++) {
                        rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString());
                    }
                    TableCharge.Clear();
                    TableCapWriteoff.Clear();
                    TableInsWriteoff.Clear();
                    TablePay.Clear();
                    TableIns.Clear();
                    TableAdj.Clear();
                    rcvStart = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
                }
                else {
                    rcvAdj = 0;
                    rcvInsPayment = 0;
                    rcvPayment = 0;
                    rcvProd = 0;
                    rcvWriteoff = 0;
                    rcvDaily = 0;
                    runningRcv = rcvStart;
                    report.TableQ = new DataTable(null);//new table with 7 columns
                    for(int l = 0;l < 8;l++) { //add columns
                        report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
                    }
                    report.InitializeColumns();
                    eDate = POut.Date(date1.SelectionStart).Substring(1,10);// Reset EndDate to Selected Date
                    DateTime[] dates = new DateTime[(PIn.Date(eDate) - PIn.Date(bDate)).Days + 1];
                    for(int i = 0;i < dates.Length;i++) {//usually 31 days in loop
                        dates[i] = PIn.Date(bDate).AddDays(i);
                        //create new row called 'row' based on structure of TableQ
                        DataRow row = report.TableQ.NewRow();
                        row[0] = dates[i].ToShortDateString();
                        for(int k = 0;k < TableCharge.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TableCharge.Rows[k][0].ToString()))) {
                                rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString());
                            }
                        }
                        for(int k = 0;k < TableCapWriteoff.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TableCapWriteoff.Rows[k][0].ToString()))) {
                                rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString());
                            }
                        }
                        for(int k = 0;k < TableAdj.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TableAdj.Rows[k][0].ToString()))) {
                                rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString());
                            }
                        }
                        for(int k = 0;k < TableInsWriteoff.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TableInsWriteoff.Rows[k][0].ToString()))) {
                                rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString());
                            }
                        }
                        for(int k = 0;k < TablePay.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TablePay.Rows[k][0].ToString()))) {
                                rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString());
                            }
                        }
                        for(int k = 0;k < TableIns.Rows.Count;k++) {
                            if(dates[i] == (PIn.Date(TableIns.Rows[k][0].ToString()))) {
                                rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString());
                            }
                        }
                        rcvDaily = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
                        runningRcv += (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
                        row[1] = rcvProd.ToString("n");
                        row[2] = rcvAdj.ToString("n");
                        row[3] = rcvWriteoff.ToString("n");
                        row[4] = rcvPayment.ToString("n");
                        row[5] = rcvInsPayment.ToString("n");
                        row[6] = rcvDaily.ToString("n");
                        row[7] = runningRcv.ToString("n");
                        ColTotal[1] += rcvProd;
                        ColTotal[2] += rcvAdj;
                        ColTotal[3] += rcvWriteoff;
                        ColTotal[4] += rcvPayment;
                        ColTotal[5] += rcvInsPayment;
                        ColTotal[6] += rcvDaily;
                        ColTotal[7] = runningRcv;
                        report.TableQ.Rows.Add(row);  //adds row to table Q
                        rcvAdj = 0;
                        rcvInsPayment = 0;
                        rcvPayment = 0;
                        rcvProd = 0;
                        rcvWriteoff = 0;
                    }
                    report.ColTotal[1]=PIn.Double(ColTotal[1].ToString("n"));
                    report.ColTotal[2]=PIn.Double(ColTotal[2].ToString("n"));
                    report.ColTotal[3]=PIn.Double(ColTotal[3].ToString("n"));
                    report.ColTotal[4]=PIn.Double(ColTotal[4].ToString("n"));
                    report.ColTotal[5]=PIn.Double(ColTotal[5].ToString("n"));
                    report.ColTotal[6]=PIn.Double(ColTotal[6].ToString("n"));
                    report.ColTotal[7]=PIn.Double(ColTotal[7].ToString("n"));
                    FormQuery2 = new FormQuery(report);
                    FormQuery2.IsReport = true;
                    FormQuery2.ResetGrid();
                    report.Title = "Receivables Breakdown Report";
                    report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
                    whereProv = "Report for: Practice";
                    whereProvx = "";
                    if(listProv.SelectedIndices[0] != 0) {
                        int nameCount = 0;
                        whereProv = "Report Includes:  ";
                        for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
                            if(nameCount < 3) {
                                whereProv += " "+ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName()+" /";
                            }
                            else {
                                whereProvx += " "+ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName()+" /";
                            }
                            nameCount += 1;
                        }
                        whereProv = whereProv.Substring(0,whereProv.Length-1);
                        if(whereProvx.Length > 0) {
                            whereProvx = whereProvx.Substring(0,whereProvx.Length-1);
                        }
                    }
                    report.SubTitle.Add(whereProv);
                    report.SubTitle.Add(whereProvx);
                    report.SetColumnPos(this,0,"Day",80);
                    report.SetColumnPos(this,1,"Production",160,HorizontalAlignment.Right);
                    report.SetColumnPos(this,2,"Adjustment",260,HorizontalAlignment.Right);
                    report.SetColumnPos(this,3,"Writeoff",360,HorizontalAlignment.Right);
                    report.SetColumnPos(this,4,"Payment",470,HorizontalAlignment.Right);
                    report.SetColumnPos(this,5,"InsPayment",570,HorizontalAlignment.Right);
                    report.SetColumnPos(this,6,"Daily A/R",680,HorizontalAlignment.Right);
                    report.SetColumnPos(this,7,"Ending A/R",779,HorizontalAlignment.Right);
                    report.Summary.Add(
                        Lan.g(this,"Receivables Calculation: (Production + Adjustments - Writeoffs) - (Payments + Insurance Payments)"));
                    FormQuery2.ShowDialog();
                    DialogResult = DialogResult.OK;
                }//END If
            }// END For Loop
        }
Пример #20
0
		private void butUserQuery_Click(object sender,EventArgs e) {
			if(!Security.IsAuthorized(Permissions.UserQuery)) {
				return;
			}
			if(DataConnection.DBtype==DatabaseType.Oracle) {
				MsgBox.Show(this,"Not allowed while using Oracle.");
				return;
			}
			FormQuery FormQuery2=new FormQuery(null);
			FormQuery2.ShowDialog();
			SecurityLogs.MakeLogEntry(Permissions.UserQuery,0,"");
		}
Пример #21
0
		private void butOK_Click(object sender, System.EventArgs e) {
			if(textDateFrom.errorProvider1.GetError(textDateFrom)!=""
				|| textDateTo.errorProvider1.GetError(textDateTo)!="") 
			{
				MsgBox.Show(this,"Please fix data entry errors first.");
				return;
			}
			DateTime dateFrom=PIn.Date(textDateFrom.Text);
			DateTime dateTo=PIn.Date(textDateTo.Text);
			if(dateTo<dateFrom) {
				MsgBox.Show(this,"To date cannot be before From date.");
				return;
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query=
				"SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",AdjAmt "
				+"FROM patient,adjustment "
				+"WHERE patient.PatNum=adjustment.PatNum "
				+"AND adjustment.AdjDate BETWEEN "+POut.Date(dateFrom)+" AND "+POut.Date(dateTo)+" "
				+"AND adjustment.AdjType = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();		
			report.Title=Lans.g(this,"FINANCE CHARGE REPORT");
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
			report.SetColumn(this,0,"Patient Name",180);
			report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right);

			FormQuery2.ShowDialog();		
			DialogResult=DialogResult.OK;
		}
Пример #22
0
		private void butOK_Click(object sender,System.EventArgs e) {
			if(date2.SelectionStart<date1.SelectionStart) {
				MsgBox.Show(this,"End date cannot be before start date.");
				return;
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query=@"SELECT ScreenDate,ProvName,County,county.CountyCode,
				site.Description AS schoolName,site.Note AS schoolCode,PlaceService,GradeLevel,Age,Birthdate,RaceOld,Gender,Urgency,
				HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
				Comments FROM screen
				LEFT JOIN site ON screen.GradeSchool=site.Description
				LEFT JOIN county ON screen.County=county.CountyName
				WHERE ScreenDate >= "+POut.Date(date1.SelectionStart)+" "
				+"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
			FormQuery2=new FormQuery(report);
			FormQuery2.textTitle.Text="RawScreeningData"+DateTime.Today.ToString("MMddyyyy");
			//FormQuery2.IsReport=true;
			//FormQuery2.SubmitReportQuery();			
			FormQuery2.SubmitQuery();
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #23
0
 private void butOK_Click(object sender, System.EventArgs e)
 {
     string[] fieldsSelected=new string[listSelect.SelectedItems.Count+listSelect2.SelectedItems.Count];
     if(listSelect.SelectedItems.Count==0 && listSelect2.SelectedItems.Count==0){
         MsgBox.Show(this,"At least one field must be selected.");
         return;
     }
     listSelect.SelectedItems.CopyTo(fieldsSelected,0);
     listSelect2.SelectedItems.CopyTo(fieldsSelected,listSelect.SelectedItems.Count);
     string command="SELECT ";
     for(int i=0;i<fieldsSelected.Length;i++){
         if(i>0){
             command+=",";
         }
         if(fieldsSelected[i]=="AptDateTime"){
             command+="appointment.AptDateTime";
         }
         else if(fieldsSelected[i]=="PriCarrier"){
             command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
                 +"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=1) PriCarrier";
         }
         else if(fieldsSelected[i]=="PriRelationship") {
             command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=1) PriRelationship";
         }
         else if(fieldsSelected[i]=="SecCarrier") {
             command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
                 +"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=2) SecCarrier";
         }
         else if(fieldsSelected[i]=="SecRelationship") {
             command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=2) SecRelationship";
         }
         else{
             command+="patient."+fieldsSelected[i];
         }
     }
     command+=" FROM patient,appointment "
         +"WHERE patient.PatNum=appointment.PatNum AND(";
     for(int i=0;i<AptNums.Length;i++){
         if(i>0){
             command+=" OR";
         }
         command+=" appointment.AptNum='"+AptNums[i]+"'";
     }
     command+=")";
     ReportSimpleGrid report=new ReportSimpleGrid();
     report.Query=command;
     FormQuery FormQ=new FormQuery(report);
     FormQ.IsReport=false;
     FormQ.SubmitQuery();
       FormQ.textQuery.Text=report.Query;
     FormQ.ShowDialog();
       DialogResult=DialogResult.OK;
 }
Пример #24
0
		private void butOK_Click(object sender,System.EventArgs e) {
			if(date2.SelectionStart<date1.SelectionStart) {
				MsgBox.Show(this,"End date cannot be before start date.");
				return;
			}
			if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
				MsgBox.Show(this,"At least one provider must be selected.");
				return;
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
					MsgBox.Show(this,"At least one clinic must be selected.");
					return;
				}
			}
			if(listType.SelectedIndices.Count==0) {
				MsgBox.Show(this,"At least one type must be selected.");
				return;
			}
			string whereProv="";
			if(!checkAllProv.Checked) {
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i==0) {
						whereProv+=" AND (";
					}
					else {
						whereProv+="OR ";
					}
					whereProv+="adjustment.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
				}
				whereProv+=") ";
			}
			string whereClin="";
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				whereClin+=" AND adjustment.ClinicNum IN(";
				for(int i=0;i<listClin.SelectedIndices.Count;i++) {
					if(i>0) {
						whereClin+=",";
					}
					if(Security.CurUser.ClinicIsRestricted) {
						whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
					}
					else {
						if(listClin.SelectedIndices[i]==0) {
							whereClin+="0";
						}
						else {
							whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
						}
					}
				}
				whereClin+=") ";
			}
			string whereType="(";
			for(int i=0;i<listType.SelectedIndices.Count;i++) {
				if(i>0) {
					whereType+="OR ";
				}
				whereType+="adjustment.AdjType = '"
					+POut.Long(DefC.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum)+"' ";
			}
			whereType+=")";
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query="SELECT adjustment.AdjDate,"
				+DbHelper.Concat("patient.LName","', '","patient.FName","', '","patient.MiddleI")+","
				+"adjustment.ProvNum,adjustment.ClinicNum,"
				+"definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
				+"adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
			  +"AND patient.PatNum=adjustment.PatNum "
				+whereProv
				+whereClin
				+"AND "+whereType+" "
				+"AND adjustment.AdjDate >= "+POut.Date(date1.SelectionStart)+" "
				+"AND adjustment.AdjDate <= "+POut.Date(date2.SelectionStart);
			report.Query += " ORDER BY adjustment.AdjDate";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			DataTable table=report.GetTempTable();
			report.TableQ=new DataTable(null);
			int colI=6;
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				colI=7;
			}
			for(int i=0;i<colI;i++) { //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			DataRow row;
			decimal dec;
			for(int i=0;i<table.Rows.Count;i++) {
				row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
				row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
				row[1]=table.Rows[i][1].ToString();//name
				row[2]=Providers.GetAbbr(PIn.Long(table.Rows[i][2].ToString()));//prov
				colI=3;
				if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
					row[colI]=Clinics.GetDesc(PIn.Long(table.Rows[i][3].ToString()));//clinic
					colI++;
				}
				row[colI]=table.Rows[i][4].ToString();//Type
				colI++;
				row[colI]=table.Rows[i][5].ToString();//Note
				colI++;
				dec=PIn.Decimal(table.Rows[i][6].ToString());//Amount
				row[colI]=dec.ToString("n");
				report.ColTotal[colI]+=dec;
				report.TableQ.Rows.Add(row);
			}
			FormQuery2.ResetGrid();		
			report.Title="Daily Adjustments";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else {
				string provNames="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i>0) {
						provNames+=", ";
					}
					provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(provNames);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(Security.CurUser.ClinicIsRestricted) {
							clinNames+=_listClinics[listClin.SelectedIndices[i]].Description;
						}
						else {
							if(listClin.SelectedIndices[i]==0) {
								clinNames+=Lan.g(this,"Unassigned");
							}
							else {
								clinNames+=_listClinics[listClin.SelectedIndices[i]-1].Description;//Minus 1 from the selected index
							}
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.SetColumn(this,0,"Date",90);
			report.SetColumn(this,1,"Patient Name",130);
			report.SetColumn(this,2,"Prov",60);
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.SetColumn(this,3,"Clinic",70);
				report.SetColumn(this,4,"Adjustment Type",150);
				report.SetColumn(this,5,"Note",150);
				report.SetColumn(this,6,"Amount",75,HorizontalAlignment.Right);
			}
			else {
				report.SetColumn(this,3,"Adjustment Type",150);
				report.SetColumn(this,4,"Note",150);
				report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right);
			}
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #25
0
		private void butOK_Click(object sender,System.EventArgs e) {
			if(date2.SelectionStart<date1.SelectionStart) {
				MsgBox.Show(this,"End date cannot be before start date.");
				return;
			}
			ReportSimpleGrid report=new ReportSimpleGrid();
			//if(radioRange.Checked){
			report.Query="SELECT ";
			if(PrefC.GetBool(PrefName.ReportsShowPatNum)) {
				report.Query+=DbHelper.Concat("CAST(patient.PatNum AS CHAR)","'-'","patient.LName","', '","patient.FName","' '","patient.MiddleI");
			}
			else {
				report.Query+=DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI");
			}
			report.Query+=" AS 'PatientName',procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee "
				+"FROM patient,procedurecode,procedurelog,claimproc,insplan "
				+"WHERE claimproc.procnum=procedurelog.procnum "
				+"AND patient.PatNum=procedurelog.PatNum "
				+"AND procedurelog.CodeNum=procedurecode.CodeNum "
				+"AND claimproc.PlanNum=insplan.PlanNum ";
			if(!checkMedical.Checked) {
				//Users would have no way to see why patients are being excluded from their billing list(s) without this check box when using the 'Exclude if insurance pending' option.
				report.Query+="AND insplan.IsMedical=0 ";
			}
			report.Query+="AND claimproc.NoBillIns=0 "
				+"AND procedurelog.ProcFee>0 "
				+"AND claimproc.Status=6 "//estimate
				+"AND procedurelog.procstatus=2 "
				+"AND procedurelog.ProcDate >= "+POut.Date(date1.SelectionStart)+" "
				+"AND procedurelog.ProcDate <= "+POut.Date(date2.SelectionStart)+" "
				+"GROUP BY procedurelog.ProcNum "
				+"ORDER BY patient.LName,patient.FName";
			/*}
			else{
				report.Query="SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),"
					+"procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee FROM patient,procedurecode,"
					+"procedurelog LEFT JOIN claimproc ON claimproc.procnum = procedurelog.procnum "
					+"WHERE claimproc.procnum IS NULL "
					+"&& patient.patnum=procedurelog.patnum && procedurelog.codenum=procedurecode.codenum "
					+"&& patient.priplannum > 0 "
					+"&& procedurelog.nobillins = 0 && procedurelog.procstatus = 2 "
					+"&& procedurelog.ProcDate = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
			}*/
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();
			report.Title="Procedures Not Billed to Insurance";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
			report.SetColumn(this,0,"Patient Name",185);
			report.SetColumn(this,1,"Procedure Date",185);
			report.SetColumn(this,2,"Procedure Description",185);
			report.SetColumn(this,3,"Procedure Amount",185,HorizontalAlignment.Right);
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #26
0
		private void butViewImported_Click(object sender,EventArgs e) {
			Cursor=Cursors.WaitCursor;
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query="SELECT TransactionDateTime,TransType,ClerkID,ItemNum,PatNum,CreditCardNum,Expiration,Result,CASE WHEN ResultCode='000' OR ResultCode='010' THEN Amount ELSE Amount=0 END AS Amount "
				+"FROM xchargetransaction "
				+"WHERE DATE(TransactionDateTime) BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+" "
				+"AND TransType!='CCVoid'";
			FormQuery FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			FormQuery2.SubmitReportQuery();
			report.Title="XCharge Transactions From "+date1.SelectionStart.ToShortDateString()+" To "+date2.SelectionStart.ToShortDateString();
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SetColumn(this,0,"Transaction Date/Time",170);
			report.SetColumn(this,1,"Transaction Type",120);
			report.SetColumn(this,2,"Clerk ID",80);
			report.SetColumn(this,3,"Item#",50);
			report.SetColumn(this,4,"Pat",50);//This name is used to ensure FormQuery does not replace the patnum with the patient name.
			report.SetColumn(this,5,"Credit Card Num",140);
			report.SetColumn(this,6,"Exp",50);
			report.SetColumn(this,7,"Result",50);
			report.SetColumn(this,8,"Amount",60,HorizontalAlignment.Right);
			Cursor=Cursors.Default;
			FormQuery2.ShowDialog();
		}
Пример #27
0
		private void butOK_Click(object sender, System.EventArgs e) {
			long feeSched=FeeSchedC.ListShort[listFeeSched.SelectedIndex].FeeSchedNum;	
      string catName="";  //string to hold current category name
			Fees fee=new Fees();
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query= "SELECT procedurecode.ProcCode,fee.Amount,'     ',procedurecode.Descript,"
			  +"procedurecode.AbbrDesc FROM procedurecode,fee "
				+"WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='"+feeSched.ToString()
         +"' ORDER BY procedurecode.ProcCode";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
      if (radioCode.Checked==true)  {
			  FormQuery2.SubmitReportQuery();			      
				report.Title="Procedure Codes";
				report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
				report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
				report.SetColumn(this,0,"Code",70);
				report.SetColumn(this,1,"Fee Amount",70,HorizontalAlignment.Right);
				report.SetColumn(this,2," ",80);//otherwise, the amount gets bunched up next to the description.
				report.SetColumn(this,3,"Description",200);
				report.SetColumn(this,4,"Abbr Description",200);
				FormQuery2.ShowDialog();
				DialogResult=DialogResult.OK;		
      }
			else {//categories
			  //report.SubmitTemp();//create TableTemp which is not actually used
	      ProcedureCode[] ProcList=ProcedureCodes.GetProcList();
				report.TableQ=new DataTable(null);
			  for(int i=0;i<5;i++){//add columns
				  report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			  }
				report.InitializeColumns();
        DataRow row=report.TableQ.NewRow();//add first row by hand to get value for temp
				row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[0].ProcCat);
				catName=row[0].ToString();
				row[1]=ProcList[0].ProcCode;
				row[2]=ProcList[0].Descript;
				row[3]=ProcList[0].AbbrDesc;
			  row[4]=((double)Fees.GetAmount0(ProcList[0].CodeNum,feeSched)).ToString("F");
				report.ColTotal[4]+=PIn.Decimal(row[4].ToString());
				report.TableQ.Rows.Add(row);
				for(int i=1;i<ProcList.Length;i++){//loop through data rows
					row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
					row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[i].ProcCat);
					if(catName==row[0].ToString()){
            row[0]=""; 
					}
					else  {
						catName=row[0].ToString();
          }
					row[1]=ProcList[i].ProcCode.ToString();
					row[2]=ProcList[i].Descript;
					row[3]=ProcList[i].AbbrDesc.ToString();
					row[4]=((double)Fees.GetAmount0(ProcList[i].CodeNum,feeSched)).ToString("F");
  				//report.ColTotal[4]+=PIn.PDouble(row[4].ToString());
					report.TableQ.Rows.Add(row);
				}
				FormQuery2.ResetGrid();//this is a method in FormQuery2;
				report.Title="Procedure Codes";
				report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
				report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
				report.ColPos[0]=20;
				report.ColPos[1]=120;
				report.ColPos[2]=270;
				report.ColPos[3]=470;
				report.ColPos[4]=620;
				report.ColPos[5]=770;
				report.ColCaption[0]="Category";
				report.ColCaption[1]="Code";
				report.ColCaption[2]="Description";
				report.ColCaption[3]="Abbr Description";
				report.ColCaption[4]="Fee Amount";
				report.ColAlign[4]=HorizontalAlignment.Right;
				FormQuery2.ShowDialog();
				DialogResult=DialogResult.OK;
			}
		}
Пример #28
0
		private void CreateIndividual(ReportSimpleGrid report) {
			//added Procnum to retrieve all codes
			report.Query="SELECT procedurelog.ProcDate,"
			  +DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+" "
			  +"AS plfname, procedurecode.ProcCode,"
				+"procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
				+"procedurelog.ClinicNum,"
				+"procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) ";//\"$fee\" "  //if no writeoff, then subtract 0
				if(DataConnection.DBtype==DatabaseType.MySql) {
					report.Query+="$fee ";
				}
				else {//Oracle needs quotes.
					report.Query+="\"$fee\" ";
				}
				report.Query+="FROM patient,procedurecode,provider,procedurelog "
				+"LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
				+"AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
				+"WHERE procedurelog.ProcStatus = '2' "
				+"AND patient.PatNum=procedurelog.PatNum "
				+"AND procedurelog.CodeNum=procedurecode.CodeNum "
				+"AND provider.ProvNum=procedurelog.ProvNum "
				+whereProv
				+whereClin
				+"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= " +POut.Date(date1.SelectionStart)+" "
				+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= " +POut.Date(date2.SelectionStart)+" "
				+"GROUP BY procedurelog.ProcNum "
				+"ORDER BY "+DbHelper.DateColumn("procedurelog.ProcDate")+",plfname,procedurecode.ProcCode,ToothNum";
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=true;
			DataTable table=report.GetTempTable();
			report.TableQ=new DataTable(null);
			int colI=7;
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				colI=8;
			}
			for(int i=0;i<colI;i++) { //add columns
				report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
			}
			report.InitializeColumns();
			DataRow row;
			decimal dec=0;
			decimal total=0;
			for(int i=0;i<table.Rows.Count;i++) {
				row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
				row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
				row[1]=table.Rows[i][1].ToString();//name
				row[2]=table.Rows[i][2].ToString();//adacode
				row[3]=Tooth.ToInternat(table.Rows[i][3].ToString());//tooth
				row[4]=table.Rows[i][4].ToString();//descript
				row[5]=table.Rows[i][5].ToString();//prov
				if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
					row[6]=Clinics.GetDesc(PIn.Long(table.Rows[i][6].ToString()));//clinic
					dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
					row[7]=dec.ToString("n");
				}
				else {
					dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
					row[6]=dec.ToString("n");
				}
				total+=dec;
				report.TableQ.Rows.Add(row);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.ColTotal[7]=total;
			}
			else {
				report.ColTotal[6]=total;
			}
			FormQuery2.ResetGrid();			
			report.Title="Daily Procedures";
			report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
			report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
			if(checkAllProv.Checked) {
				report.SubTitle.Add(Lan.g(this,"All Providers"));
			}
			else {
				string provNames="";
				for(int i=0;i<listProv.SelectedIndices.Count;i++) {
					if(i>0) {
						provNames+=", ";
					}
					provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
				}
				report.SubTitle.Add(provNames);
			}
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				if(checkAllClin.Checked) {
					report.SubTitle.Add(Lan.g(this,"All Clinics"));
				}
				else {
					string clinNames="";
					for(int i=0;i<listClin.SelectedIndices.Count;i++) {
						if(i>0) {
							clinNames+=", ";
						}
						if(listClin.SelectedIndices[i]==0) {
							clinNames+=Lan.g(this,"Unassigned");
						}
						else {
							clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
						}
					}
					report.SubTitle.Add(clinNames);
				}
			}
			report.SetColumn(this,0,"Date",80);
			report.SetColumn(this,1,"Patient Name",130);
			report.SetColumn(this,2,"ADA Code",75);
			report.SetColumn(this,3,"Tooth",45);
			report.SetColumn(this,4,"Description",200);
			report.SetColumn(this,5,"Provider",50);
			if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
				report.SetColumn(this,6,"Clinic",70);
				report.SetColumn(this,7,"Fee",90,HorizontalAlignment.Right);
			}
			else{
				report.SetColumn(this,6,"Fee",90,HorizontalAlignment.Right);
			}
			FormQuery2.ShowDialog();
			DialogResult=DialogResult.OK;
		}
Пример #29
0
		private void butOK_Click(object sender, System.EventArgs e) {
			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query=SQLstatement;
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=false;
			FormQuery2.SubmitQuery();	
      FormQuery2.textQuery.Text=report.Query;					
			FormQuery2.ShowDialog();		
      DialogResult=DialogResult.OK; 
		}
Пример #30
0
		private void butOK_Click(object sender, System.EventArgs e)
		{
			FormQuery FormQuery2;
			string phrase = textPharse.Text.Replace("\t","").Replace("\n","");
			StringBuilder sbSQL = new StringBuilder();
			sbSQL.AppendFormat("SELECT LName,FName,Preferred,PatStatus,Gender,Birthdate,Address,Address2,City,State,zip,HmPhone,Wkphone,",phrase);                                
			sbSQL.AppendFormat("WirelessPhone,Guarantor,PriProv,AddrNote,FamFinUrgNote,MedUrgNote,ApptModNote,DateFirstVisit",phrase);
            sbSQL.AppendFormat(" FROM patient WHERE AddrNote LIKE '%{0}%' ", phrase);
            sbSQL.AppendFormat("or FamFinUrgNote LIKE '%{0}%' ",phrase);
			sbSQL.AppendFormat("or MedUrgNote LIKE '%{0}%' ",phrase);
			sbSQL.AppendFormat("or ApptModNote LIKE '%{0}%' ",phrase);
			sbSQL.AppendFormat("or EmploymentNote LIKE '%{0}%' ",phrase);


			ReportSimpleGrid report=new ReportSimpleGrid();
			report.Query= sbSQL.ToString();
			FormQuery2=new FormQuery(report);
			FormQuery2.IsReport=false;
			FormQuery2.SubmitQuery();	
			FormQuery2.textQuery.Text=report.Query;					
			FormQuery2.ShowDialog();
		}