private void butReport_Click(object sender, System.EventArgs e){
if(errorProvider1.GetError(textDateFrom) != ""
|| errorProvider1.GetError(textDateTo) != "") {
MsgBox.Show(this,"Please fix data entry errors first.");
return;
}
DateTime dateFrom=DateTime.ParseExact(textDateFrom.Text,cultureDateFormat,CultureInfo.CurrentCulture);
DateTime dateTo=DateTime.ParseExact(textDateTo.Text,cultureDateFormat,CultureInfo.CurrentCulture);
if(dateTo < dateFrom) {
MsgBox.Show(this,"To date cannot be before From date.");
return;
}
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName=Lan.g(this,"Birthdays");
report.AddTitle("Title",Lan.g(this,"Birthdays"),fontTitle);
report.AddSubTitle("PracTitle",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date",dateFrom.ToString(cultureDateFormat)+" - "+dateTo.ToString(cultureDateFormat),fontSubTitle);
QueryObject query=report.AddQuery(Patients.GetBirthdayList(dateFrom,dateTo),"","",SplitByKind.None,1,true);
query.AddColumn("LName",90,FieldValueType.String,font);
query.AddColumn("FName",90,FieldValueType.String,font);
query.AddColumn("Preferred",90,FieldValueType.String,font);
query.AddColumn("Address",90,FieldValueType.String,font);
query.AddColumn("Address2",90,FieldValueType.String,font);
query.AddColumn("City",75,FieldValueType.String,font);
query.AddColumn("State",60,FieldValueType.String,font);
query.AddColumn("Zip",75,FieldValueType.String,font);
query.AddColumn("Birthdate",75,FieldValueType.Date,font);
query.GetColumnDetail("Birthdate").StringFormat="d";
query.AddColumn("Age",45,FieldValueType.Integer,font);
report.AddPageNum(font);
if(!report.SubmitQueries()) {
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
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;
}
}
if(!checkPatientTypes.Checked && listPatientTypes.SelectedIndices.Count==0 && !checkInsuranceTypes.Checked && listInsuranceTypes.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one type must be selected.");
return;
}
List<long> listProvNums=new List<long>();
List<long> listClinicNums=new List<long>();
List<long> listInsTypes=new List<long>();
List<long> listPatTypes=new List<long>();
List<Provider> listProvs=ProviderC.GetListShort();
List<Def> listInsDefs=new List<Def>(DefC.GetList(DefCat.InsurancePaymentType));
List<Def> listPatDefs=new List<Def>(DefC.GetList(DefCat.PaymentTypes));
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
listProvNums.Add(listProvs[listProv.SelectedIndices[i]].ProvNum);
}
if(checkAllProv.Checked) {
for(int i=0;i<listProvs.Count;i++) {
listProvNums.Add(listProvs[i].ProvNum);
}
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(Security.CurUser.ClinicIsRestricted) {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
listClinicNums.Add(0);
}
else {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
}
for(int i=0;i<listInsuranceTypes.SelectedIndices.Count;i++) {
listInsTypes.Add(listInsDefs[listInsuranceTypes.SelectedIndices[i]].DefNum);
}
if(checkInsuranceTypes.Checked) {
for(int i=0;i<listInsDefs.Count;i++) {
listInsTypes.Add(listInsDefs[i].DefNum);
}
}
for(int i=0;i<listPatientTypes.SelectedIndices.Count;i++) {
listPatTypes.Add(listPatDefs[listPatientTypes.SelectedIndices[i]].DefNum);
}
if(checkPatientTypes.Checked) {
for(int i=0;i<listPatDefs.Count;i++) {
listPatTypes.Add(listPatDefs[i].DefNum);
}
}
DataTable tableIns=RpPaySheet.GetInsTable(date1.SelectionStart,date2.SelectionStart,listProvNums,listClinicNums,listInsTypes,checkAllProv.Checked,checkAllClin.Checked,checkInsuranceTypes.Checked,radioPatient.Checked);
DataTable tablePat=RpPaySheet.GetPatTable(date1.SelectionStart,date2.SelectionStart,listProvNums,listClinicNums,listPatTypes,checkAllProv.Checked,checkAllClin.Checked,checkPatientTypes.Checked,radioPatient.Checked);
string subtitleProvs="";
string subtitleClinics="";
if(checkAllProv.Checked) {
subtitleProvs=Lan.g(this,"All Providers");
}
else {
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
subtitleProvs+=", ";
}
subtitleProvs+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
subtitleClinics=Lan.g(this,"All Clinics");
}
else {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
subtitleClinics+=", ";
}
if(Security.CurUser.ClinicIsRestricted) {
subtitleClinics+=_listClinics[listClin.SelectedIndices[i]].Description;
}
else {
if(listClin.SelectedIndices[i]==0) {
subtitleClinics+=Lan.g(this,"Unassigned");
}
else {
subtitleClinics+=_listClinics[listClin.SelectedIndices[i]-1].Description;//Minus 1 from the selected index
}
}
}
}
}
Font font=new Font("Tahoma",9);
Font fontBold=new Font("Tahoma",9,FontStyle.Bold);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName=Lan.g(this,"Daily Payments");
report.AddTitle("Title",Lan.g(this,"Daily Payments"),fontTitle);
report.AddSubTitle("Providers",subtitleProvs,fontSubTitle);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.AddSubTitle("Clinics",subtitleClinics,fontSubTitle);
}
Dictionary<long,string> dictInsDefNames=new Dictionary<long,string>();
Dictionary<long,string> dictPatDefNames=new Dictionary<long,string>();
List<Def> insDefs=new List<Def>(DefC.GetList(DefCat.InsurancePaymentType));
List<Def> patDefs=new List<Def>(DefC.GetList(DefCat.PaymentTypes));
for(int i=0;i<insDefs.Count;i++) {
dictInsDefNames.Add(insDefs[i].DefNum,insDefs[i].ItemName);
}
for(int i=0;i<patDefs.Count;i++) {
dictPatDefNames.Add(patDefs[i].DefNum,patDefs[i].ItemName);
}
int[] summaryGroups1= { 1 };
int[] summaryGroups2= { 2 };
int[] summaryGroups3= { 1,2 };
//Insurance Payments Query-------------------------------------
QueryObject query=report.AddQuery(tableIns,"Insurance Payments","PayType",SplitByKind.Definition,1,true,dictInsDefNames,fontSubTitle);
query.AddColumn("Date",90,FieldValueType.Date,font);
//query.GetColumnDetail("Date").SuppressIfDuplicate = true;
query.GetColumnDetail("Date").StringFormat="d";
query.AddColumn("Carrier",150,FieldValueType.String,font);
query.AddColumn("Patient Name",150,FieldValueType.String,font);
query.AddColumn("Provider",90,FieldValueType.String,font);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
query.AddColumn("Clinic",120,FieldValueType.String,font);
}
query.AddColumn("Check#",75,FieldValueType.String,font);
query.AddColumn("Amount",90,FieldValueType.Number,font);
query.AddGroupSummaryField("Total Insurance Payments:",Color.Black,"Amount","amt",SummaryOperation.Sum,new List<int>(summaryGroups1),fontBold,0,10);
//Patient Payments Query---------------------------------------
query=report.AddQuery(tablePat,"Patient Payments","PayType",SplitByKind.Definition,2,true,dictPatDefNames,fontSubTitle);
query.AddColumn("Date",90,FieldValueType.Date,font);
//query.GetColumnDetail("Date").SuppressIfDuplicate = true;
query.GetColumnDetail("Date").StringFormat="d";
query.AddColumn("Patient Name",270,FieldValueType.String,font);
query.AddColumn("Provider",90,FieldValueType.String,font);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
query.AddColumn("Clinic",120,FieldValueType.String,font);
}
query.AddColumn("Check#",75,FieldValueType.String,font);
query.AddColumn("Amount",120,FieldValueType.Number,font);
query.AddGroupSummaryField("Total Patient Payments:",Color.Black,"Amount","amt",SummaryOperation.Sum,new List<int>(summaryGroups2),fontBold,0,10);
query.AddGroupSummaryField("Total All Payments:",Color.Black,"Amount","amt",SummaryOperation.Sum,new List<int>(summaryGroups3),fontBold,0,10);
report.AddPageNum(font);
report.AddGridLines();
if(!report.SubmitQueries()) {
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void ExecuteReport(){
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.AddTitle("Title",Lan.g(this,"Incomplete Procedure Notes"),fontTitle);
report.AddSubTitle("PracTitle",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
QueryObject query=report.AddQuery(@"(SELECT procedurelog.ProcDate,
CONCAT(CONCAT(patient.LName,', '),patient.FName),
procedurecode.ProcCode,procedurecode.Descript,
procedurelog.ToothNum,procedurelog.Surf
FROM procedurelog,patient,procedurecode,procnote n1
WHERE procedurelog.PatNum = patient.PatNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND procedurelog.ProcStatus = "+POut.Int((int)ProcStat.C)+@"
AND procedurelog.ProcNum=n1.ProcNum "
+"AND n1.Note LIKE '%\"\"%' "//looks for ""
+@"AND n1.EntryDateTime=(SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum))
UNION ALL
(SELECT procedurelog.ProcDate,
CONCAT(CONCAT(patient.LName,', '),patient.FName),
procedurecode.ProcCode,procedurecode.Descript,
procedurelog.ToothNum,procedurelog.Surf
FROM procedurelog,patient,procedurecode,procnote n1
WHERE procedurelog.PatNum = patient.PatNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND procedurelog.ProcStatus = "+POut.Int((int)ProcStat.EC)+@"
AND procedurelog.ProcNum=n1.ProcNum "
+"AND n1.Note LIKE '%\"\"%' "//looks for ""
+@"AND n1.EntryDateTime=(SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum)
AND procedurecode.ProcCode='~GRP~')
ORDER BY ProcDate","","",SplitByKind.None,1,true);
query.AddColumn("Date",80,FieldValueType.Date,font);
query.AddColumn("Patient",120,FieldValueType.String,font);
query.AddColumn("Code",50,FieldValueType.String,font);
query.AddColumn("Description",120,FieldValueType.String,font);
query.AddColumn("Tth",30,FieldValueType.String,font);
query.AddColumn("Surf",40,FieldValueType.String,font);
if(!report.SubmitQueries()) {
DialogResult=DialogResult.Cancel;
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
///<summary>This report has never worked for Oracle.</summary>
private void butOK_Click(object sender, System.EventArgs e) {
DataTable data=Accounts.GetGeneralLedger(date1.SelectionStart,date2.SelectionStart);
for(int i=0;i<data.Rows.Count;i++) {
data.Rows[i]["Balance"]=ODR.Aggregate.RunningSumForAccounts(data.Rows[i]["AccountNum"],data.Rows[i]["DebitAmt"],data.Rows[i]["CreditAmt"],data.Rows[i]["AcctType"]);
}
Font font=new Font("Tahoma",7);
Font fontTitle=new Font("Tahoma",9);
Font fontSubTitle=new Font("Tahoma",8);
//create the report
ReportComplex report=new ReportComplex(true,false);
report.ReportName="General Ledger";
report.AddTitle("Title","Detail of General Ledger",fontTitle);
report.AddSubTitle("PracName",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date",date1.SelectionStart.ToShortDateString()+" - "+date2.SelectionStart.ToShortDateString(),fontSubTitle);
report.Sections["Report Header"].Height-=20;
//setup query
QueryObject query;
query=report.AddQuery(data,"Accounts","Description",SplitByKind.Value,1,true);
query.GetGroupTitle().Font=new Font("Tahoma",8);
// add columns to report
query.AddColumn("Date",75,FieldValueType.Date,font);
//query.GetColumnDetail("Date").SuppressIfDuplicate = true;
query.GetColumnDetail("Date").StringFormat="d";
query.AddColumn("Memo",175,FieldValueType.String,font);
query.AddColumn("Splits",175,FieldValueType.String,font);
query.AddColumn("Check",45,FieldValueType.String,font);
query.AddColumn("Debit",70,FieldValueType.String,font);
query.AddColumn("Credit",70,FieldValueType.String,font);
query.AddColumn("Balance",70,FieldValueType.String,font);
report.AddPageNum(font);
report.AddGridLines();
// execute query
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
///<summary></summary>
public FormReportComplex(ReportComplex myReport){
InitializeComponent();// Required for Windows Form Designer support
MyReport=myReport;
}
private void butOK_Click(object sender,System.EventArgs e) {
if(!IsValid()) {
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
string whereProv="";
if(!checkAllProvs.Checked) {
whereProv=" AND (appointment.ProvNum IN (";
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProvs.SelectedIndices[i]].ProvNum)+"'";
}
whereProv += ") ";
whereProv+="OR appointment.ProvHyg IN (";
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProvs.SelectedIndices[i]].ProvNum)+"'";
}
whereProv += ")) ";
}
string whereClinics="";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
whereClinics+=" AND appointment.ClinicNum IN(";
for(int i=0;i<listClinics.SelectedIndices.Count;i++) {
if(i>0) {
whereClinics+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
whereClinics+=POut.Long(_listClinics[listClinics.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClinics.SelectedIndices[i]==0) {
whereClinics+="0";
}
else {
whereClinics+=POut.Long(_listClinics[listClinics.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
whereClinics+=") ";
}
string innerJoinWebSched="";
if(checkShowWebSched.Checked) {
//Filter the results with an inner join on the securitylog table so that only appointments created by the Web Sched are shown in the results.
innerJoinWebSched=" INNER JOIN securitylog ON appointment.AptNum=securitylog.FKey"
+" AND securitylog.LogSource="+POut.Int((int)LogSources.WebSched)
+" AND securitylog.PermType="+POut.Int((int)Permissions.AppointmentCreate)+" ";
}
//create the report
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,true);
report.ReportName="Appointments";
report.AddTitle("Title",Lan.g(this,"Appointments"),fontTitle);
report.AddSubTitle("PracName",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date",dateFrom.ToShortDateString()+" - "+dateTo.ToShortDateString(),fontSubTitle);
//setup query
QueryObject query;
string command=@"SELECT appointment.AptDateTime"
+@",trim(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),CASE WHEN LENGTH(patient.Preferred) > 0 THEN CONCAT(CONCAT('(',patient.Preferred),') ') ELSE '' END)"
+",patient.FName), ' '),patient.MiddleI)) PatName"
+",patient.Birthdate,appointment.AptDateTime,LENGTH(appointment.Pattern)*5,appointment.ProcDescript,patient.HmPhone,patient.WkPhone,patient.WirelessPhone"
+",COALESCE(clinic.Description,'"+POut.String(Lan.g(this,"Unassigned"))+"') ClinicDesc"
+" FROM appointment"
+" INNER JOIN patient ON appointment.PatNum=patient.PatNum"
+innerJoinWebSched
+" LEFT JOIN clinic ON appointment.ClinicNum=clinic.ClinicNum"
+" WHERE appointment.AptStatus!='"+(int)ApptStatus.UnschedList+"'"
+" AND appointment.AptStatus!='"+(int)ApptStatus.Planned+"'"
+whereProv
+whereClinics
+" AND appointment.AptDateTime BETWEEN "+POut.Date(dateFrom)+" AND "+POut.Date(dateTo.AddDays(1))
+" ORDER BY appointment.ClinicNum,appointment.AptDateTime,PatName";
if(PrefC.GetBool(PrefName.EasyNoClinics)) {
query=report.AddQuery(command,"","",SplitByKind.None,1,true);
}
else {//Split the query up by clinics.
query=report.AddQuery(command,"","ClinicDesc",SplitByKind.Value,1,true);
}
// add columns to report
query.AddColumn("Date",75,FieldValueType.Date,font);
query.GetColumnDetail("Date").SuppressIfDuplicate = true;
query.GetColumnDetail("Date").StringFormat="d";
query.AddColumn("Patient",175,FieldValueType.String,font);
query.AddColumn("Age",45,FieldValueType.Age,font);
query.AddColumn("Time",65,FieldValueType.Date,font);
query.GetColumnDetail("Time").StringFormat="t";
query.GetColumnDetail("Time").ContentAlignment = ContentAlignment.MiddleRight;
query.GetColumnHeader("Time").ContentAlignment = ContentAlignment.MiddleRight;
query.AddColumn("Length",60,FieldValueType.Integer,font);
query.GetColumnHeader("Length").Location=new Point(
query.GetColumnHeader("Length").Location.X,
query.GetColumnHeader("Length").Location.Y);
query.GetColumnHeader("Length").ContentAlignment = ContentAlignment.MiddleCenter;
query.GetColumnDetail("Length").ContentAlignment = ContentAlignment.MiddleCenter;
query.GetColumnDetail("Length").Location=new Point(
query.GetColumnDetail("Length").Location.X,
query.GetColumnDetail("Length").Location.Y);
query.AddColumn("Description",170,FieldValueType.String,font);
query.AddColumn("Home Ph.",120,FieldValueType.String,font);
query.AddColumn("Work Ph.",120,FieldValueType.String,font);
query.AddColumn("Cell Ph.",120,FieldValueType.String,font);
report.AddPageNum(font);
report.AddGridLines();
// execute query
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void RunAnnual(){
if(Plugins.HookMethod(this,"FormRpProdInc.RunAnnual_Start",PIn.Date(textDateFrom.Text),PIn.Date(textDateTo.Text))) {
return;
}
if(checkAllProv.Checked) {
for(int i=0;i<listProv.Items.Count;i++) {
listProv.SetSelected(i,true);
}
}
if(checkAllClin.Checked) {
for(int i=0;i<listClin.Items.Count;i++) {
listClin.SetSelected(i,true);
}
}
dateFrom=PIn.Date(textDateFrom.Text);
dateTo=PIn.Date(textDateTo.Text);
List<long> listProvNums=new List<long>();
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
listProvNums.Add(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum);
}
List<long> listClinicNums=new List<long>();
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(Security.CurUser.ClinicIsRestricted) {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
listClinicNums.Add(0);
}
else {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
}
DataSet ds=RpProdInc.GetAnnualDataForClinics(dateFrom,dateTo,listProvNums,listClinicNums,radioWriteoffPay.Checked,checkAllProv.Checked,checkAllClin.Checked);
DataTable dt=ds.Tables["Total"];
DataTable dtClinic=new DataTable();
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
dtClinic=ds.Tables["Clinic"];
}
ReportComplex report=new ReportComplex(true,true);
report.ReportName="Appointments";
report.AddTitle("Title",Lan.g(this,"Annual Production and Income"));
report.AddSubTitle("PracName",PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle("Date",dateFrom.ToShortDateString()+" - "+dateTo.ToShortDateString());
if(checkAllProv.Checked) {
report.AddSubTitle("Providers",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.AddSubTitle("Providers",str);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
report.AddSubTitle("Clinics",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.AddSubTitle("Clinics",clinNames);
}
}
//setup query
QueryObject query;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
query=report.AddQuery(dtClinic,"","Clinic",SplitByKind.Value,1,true);
}
else {
query=report.AddQuery(dt,"","",SplitByKind.None,1,true);
}
// add columns to report
query.AddColumn("Month",75,FieldValueType.String);
query.AddColumn("Production",120,FieldValueType.Number);
query.AddColumn("Adjustments",120,FieldValueType.Number);
query.AddColumn("Writeoff",120,FieldValueType.Number);
query.AddColumn("Tot Prod",120,FieldValueType.Number);
query.AddColumn("Pt Income",120,FieldValueType.Number);
query.AddColumn("Ins Income",120,FieldValueType.Number);
query.AddColumn("Total Income",120,FieldValueType.Number);
if(!PrefC.GetBool(PrefName.EasyNoClinics) && listClin.SelectedIndices.Count>1) {
//If more than one clinic selected, we want to add a table to the end of the report that totals all the clinics together.
query=report.AddQuery(dt,"Totals","",SplitByKind.None,2,true);
query.AddColumn("Month",75,FieldValueType.String);
query.AddColumn("Production",120,FieldValueType.Number);
query.AddColumn("Adjustments",120,FieldValueType.Number);
query.AddColumn("Writeoff",120,FieldValueType.Number);
query.AddColumn("Tot Prod",120,FieldValueType.Number);
query.AddColumn("Pt Income",120,FieldValueType.Number);
query.AddColumn("Ins Income",120,FieldValueType.Number);
query.AddColumn("Total Income",120,FieldValueType.Number);
}
report.AddPageNum();
// execute query
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e){
if(listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"Please select at least one provider.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {//Using clinics
if(listClin.SelectedIndices.Count==0) {
MsgBox.Show(this,"Please select at least one clinic.");
return;
}
}
if(dateStart.SelectionStart>dateEnd.SelectionStart) {
MsgBox.Show(this,"Start date cannot be greater than the end date.");
return;
}
List<long> listProvNums=new List<long>();
List<long> listClinicNums=new List<long>();
List<Provider> listProvs=ProviderC.GetListShort();
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
listProvNums.Add(listProvs[listProv.SelectedIndices[i]].ProvNum);
}
if(checkAllProv.Checked) {
for(int i=0;i<listProvs.Count;i++) {
listProvNums.Add(listProvs[i].ProvNum);
}
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(Security.CurUser.ClinicIsRestricted) {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
listClinicNums.Add(0);
}
else {
listClinicNums.Add(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
}
DisplayPayPlanType displayPayPlanType;
if(radioInsurance.Checked) {
displayPayPlanType=DisplayPayPlanType.Insurance;
}
else if(radioPatient.Checked) {
displayPayPlanType=DisplayPayPlanType.Patient;
}
else {
displayPayPlanType=DisplayPayPlanType.Both;
}
DataTable table=RpPayPlan.GetPayPlanTable(dateStart.SelectionStart,dateEnd.SelectionStart,listProvNums,listClinicNums,checkAllProv.Checked
,displayPayPlanType,checkHideCompletePlans.Checked,checkShowFamilyBalance.Checked);
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName=Lan.g(this,"PaymentPlans");
report.AddTitle("Title",Lan.g(this,"Payment Plans"),fontTitle);
report.AddSubTitle("PracticeTitle",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date SubTitle",dateStart.SelectionStart.ToShortDateString()+" - "+dateEnd.SelectionStart.ToShortDateString(),fontSubTitle);
QueryObject query;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
query=report.AddQuery(table,"","clinicname",SplitByKind.Value,1,true);
}
else {
query=report.AddQuery(table,"","",SplitByKind.None,1,true);
}
query.AddColumn("Provider",160,FieldValueType.String,font);
query.AddColumn("Guarantor",160,FieldValueType.String,font);
query.AddColumn("Ins",40,FieldValueType.String,font);
query.GetColumnHeader("Ins").ContentAlignment=ContentAlignment.MiddleCenter;
query.GetColumnDetail("Ins").ContentAlignment=ContentAlignment.MiddleCenter;
query.AddColumn("Princ",100,FieldValueType.Number,font);
query.GetColumnHeader("Princ").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Princ").ContentAlignment=ContentAlignment.MiddleRight;
query.AddColumn("Paid",100,FieldValueType.Number,font);
query.GetColumnHeader("Paid").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Paid").ContentAlignment=ContentAlignment.MiddleRight;
query.AddColumn("Due Now",100,FieldValueType.Number,font);
query.GetColumnHeader("Due Now").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Due Now").ContentAlignment=ContentAlignment.MiddleRight;
if(checkShowFamilyBalance.Checked) {
query.AddColumn("Fam Balance",100,FieldValueType.String,font);
query.GetColumnHeader("Fam Balance").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Fam Balance").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Fam Balance").SuppressIfDuplicate=true;
}
if(!report.SubmitQueries()) {
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender,EventArgs e) {
if(!checkAllProvs.Checked && listProvs.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(!checkAllClinics.Checked && listClinics.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one clinic must be selected.");
return;
}
}
string whereProv="";
if(!checkAllProvs.Checked) {
if(_procCodeBrokenApt.CodeNum==0) {
whereProv=" AND (appointment.ProvNum IN (";
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProvs.SelectedIndices[i]].ProvNum)+"'";
}
whereProv+=") ";
whereProv+="OR appointment.ProvHyg IN (";
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProvs.SelectedIndices[i]].ProvNum)+"'";
}
whereProv+=")) ";
}
else {//D9986 present in db. Use the procedurelog table instead of appointment.
whereProv=" AND procedurelog.ProvNum IN (";
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProvs.SelectedIndices[i]].ProvNum)+"'";
}
whereProv+=") ";
}
}
string whereClin="";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(_procCodeBrokenApt.CodeNum==0) {
whereClin+=" AND appointment.ClinicNum IN(";
}
else {
whereClin+=" AND procedurelog.ClinicNum IN(";
}
for(int i=0;i<listClinics.SelectedIndices.Count;i++) {
if(i>0) {
whereClin+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
whereClin+=POut.Long(_listClinics[listClinics.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {//Unassigned or 'Headquarters'
if(listClinics.SelectedIndices[i]==0) {
whereClin+="0";
}
else {
whereClin+=POut.Long(_listClinics[listClinics.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
whereClin+=") ";
}
string queryBrokenApts="";
if(_procCodeBrokenApt.CodeNum==0) {//Practices without ADA procedure code D9986
queryBrokenApts=
"SELECT "+DbHelper.DateTFormatColumn("appointment.AptDateTime","%m/%d/%Y %H:%i:%s")+" AptDateTime, "
+""+DbHelper.Concat("patient.LName","', '","patient.FName")+" Patient,doctor.Abbr Doctor,hygienist.Abbr Hygienist, "
+"appointment.IsHygiene IsHygieneApt ";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
queryBrokenApts+=",COALESCE(clinic.Description,'"+POut.String(Lan.g(this,"Unassigned"))+"') ClinicDesc ";//Coalesce is Oracle compatible
}
queryBrokenApts+=
"FROM appointment "
+"INNER JOIN patient ON appointment.PatNum=patient.PatNum "
+"LEFT JOIN provider doctor ON doctor.ProvNum=appointment.ProvNum "
+"LEFT JOIN provider hygienist ON hygienist.ProvNum=appointment.ProvHyg ";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
queryBrokenApts+="LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum ";
}
queryBrokenApts+=
"WHERE "+DbHelper.DtimeToDate("appointment.AptDateTime")+" BETWEEN "+POut.Date(dateStart.SelectionStart)
+" AND "+POut.Date(dateEnd.SelectionStart)+" "
+"AND appointment.AptStatus="+POut.Int((int)ApptStatus.Broken)+" "
+whereProv;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
queryBrokenApts+=whereClin+" "
+"ORDER BY clinic.Description,appointment.AptDateTime,patient.LName,patient.FName";
}
else{
queryBrokenApts+="ORDER BY appointment.AptDateTime,patient.LName,patient.FName ";
}
}
else {//Practices with ADA procedure code D9986
queryBrokenApts=
"SELECT procedurelog.ProcDate ProcDate,provider.Abbr Provider,"
+DbHelper.Concat("patient.LName","', '","patient.FName")+" Patient, "
+"procedurelog.ProcFee ProcFee ";
if(!PrefC.GetBool(PrefName.EasyNoClinics)){
queryBrokenApts+=",COALESCE(clinic.Description,'"+POut.String(Lan.g(this,"Unassigned"))+"') ClinicDesc ";
}
queryBrokenApts+=
"FROM procedurelog "
+"INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum AND procedurecode.ProcCode='D9986' "
+"INNER JOIN patient ON patient.PatNum=procedurelog.PatNum "
+"INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum "
+whereProv;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
queryBrokenApts+="LEFT JOIN clinic ON clinic.ClinicNum=procedurelog.ClinicNum ";
}
queryBrokenApts+="WHERE procedurelog.ProcDate BETWEEN "+POut.Date(dateStart.SelectionStart)+" AND "+POut.Date(dateEnd.SelectionStart)+" "
+"AND procedurelog.ProcStatus="+POut.Int((int)ProcStat.C)+" ";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
queryBrokenApts+=whereClin+" "
+"ORDER BY clinic.Description,procedurelog.ProcDate,patient.LName,patient.FName";
}
else {
queryBrokenApts+="ORDER BY procedurelog.ProcDate,patient.LName,patient.FName";
}
}
string subtitleProvs="";
string subtitleClinics="";
if(checkAllProvs.Checked) {
subtitleProvs=Lan.g(this,"All Providers");
}
else {
for(int i=0;i<listProvs.SelectedIndices.Count;i++) {
if(i>0) {
subtitleProvs+=", ";
}
subtitleProvs+=ProviderC.ListShort[listProvs.SelectedIndices[i]].Abbr;
}
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClinics.Checked) {
subtitleClinics=Lan.g(this,"All Clinics");
}
else {
for(int i=0;i<listClinics.SelectedIndices.Count;i++) {
if(i>0) {
subtitleClinics+=", ";
}
if(Security.CurUser.ClinicIsRestricted) {
subtitleClinics+=_listClinics[listClinics.SelectedIndices[i]].Description;
}
else {
if(listClinics.SelectedIndices[i]==0) {
subtitleClinics+=Lan.g(this,"Unassigned");
}
else {
subtitleClinics+=_listClinics[listClinics.SelectedIndices[i]-1].Description;//Minus 1 from the selected index
}
}
}
}
}
Font font=new Font("Tahoma",10);
Font fontBold=new Font("Tahoma",10,FontStyle.Bold);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",11,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName=Lan.g(this,"Broken Appointments");
report.AddTitle("Title",Lan.g(this,"Broken Appointments"),fontTitle);
if(_procCodeBrokenApt.CodeNum==0) {
report.AddSubTitle("Report Description",Lan.g(this,"By Appointment Status"),fontSubTitle);
}
else {
report.AddSubTitle("Report Description",Lan.g(this,"By ADA Code D9986"),fontSubTitle);
}
report.AddSubTitle("Providers",subtitleProvs,fontSubTitle);
report.AddSubTitle("Clinics",subtitleClinics,fontSubTitle);
QueryObject query;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {//Split the query up by clinics.
query=report.AddQuery(queryBrokenApts,Lan.g(this,"Broken Appointments"),"ClinicDesc",SplitByKind.Value,1,true);
}
else {
query=report.AddQuery(queryBrokenApts,Lan.g(this,"Broken Appointments"),"",SplitByKind.None,1,true);
}
//Add columns to report
if(_procCodeBrokenApt.CodeNum==0) {
query.AddColumn(Lan.g(this,"AptDate"),85,FieldValueType.Date,font);
query.AddColumn(Lan.g(this,"Patient"),220,FieldValueType.String,font);
query.AddColumn(Lan.g(this,"Doctor"),165,FieldValueType.String,font);
query.AddColumn(Lan.g(this,"Hygienist"),165,FieldValueType.String,font);
query.AddColumn(Lan.g(this,"IsHyg"),50,FieldValueType.Boolean,font);
query.GetColumnDetail(Lan.g(this,"IsHyg")).ContentAlignment = ContentAlignment.MiddleCenter;
query.AddGroupSummaryField(Lan.g(this,"Total Broken Appointments")+":",Lan.g(this,"IsHyg"),"AptDateTime",SummaryOperation.Count,fontBold,0,10);
}
else {
query.AddColumn(Lan.g(this,"Date"),85,FieldValueType.Date,font);
query.AddColumn(Lan.g(this,"Provider"),180,FieldValueType.String,font);
query.AddColumn(Lan.g(this,"Patient"),220,FieldValueType.String,font);
query.AddColumn(Lan.g(this,"Fee"),200,FieldValueType.Number,font);
query.AddGroupSummaryField(Lan.g(this,"Total Broken Appointment Fees")+":",Lan.g(this,"Fee"),"ProcFee",SummaryOperation.Sum,fontBold,0,10);
query.AddGroupSummaryField(Lan.g(this,"Total Broken Appointments")+":",Lan.g(this,"Fee"),"ProcFee",SummaryOperation.Count,fontBold,0,10);
}
query.ContentAlignment=ContentAlignment.MiddleRight;
report.AddPageNum(font);
//execute query
if(!report.SubmitQueries()) {
return;
}
//display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
///<summary>This report has never worked for Oracle.</summary>
private void butOK_Click(object sender, System.EventArgs e) {
Font font=new Font("Tahoma",9);
Font fontBold=new Font("Tahoma",9,FontStyle.Bold);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
DataTable tableAssets=Accounts.GetAssetTable(date1.SelectionStart);
DataTable tableLiabilities=Accounts.GetLiabilityTable(date1.SelectionStart);
DataTable tableEquity=Accounts.GetEquityTable(date1.SelectionStart);
//Add two new rows to the equity data table to show Retained Earnings (Auto) and NetIncomeThisYear
tableEquity.LoadDataRow(new object[] { "Retained Earnings (Auto)",ODR.GetData.RetainedEarningsAuto(date1.SelectionStart) },LoadOption.OverwriteChanges);
tableEquity.LoadDataRow(new object[] { "NetIncomeThisYear",ODR.GetData.NetIncomeThisYear(date1.SelectionStart) },LoadOption.OverwriteChanges);
//create the report
ReportComplex report=new ReportComplex(true,false);
report.ReportName="Balance Sheet";
report.AddTitle("Title",Lan.g(this,"Balance Sheet"),fontTitle);
report.AddSubTitle("PracName",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date",date1.SelectionStart.ToShortDateString(),fontSubTitle);
//setup query
QueryObject query;
query=report.AddQuery(tableAssets,"Assets","",SplitByKind.None,1,true);
// add columns to report
query.AddColumn("Description",300,FieldValueType.String,font);
query.AddColumn("Amount",150,FieldValueType.Number,font);
query.AddSummaryLabel("Amount","Total Assets",SummaryOrientation.West,false,fontBold);
query=report.AddQuery(tableLiabilities,"Liabilities","",SplitByKind.None,1,true);
query.IsNegativeSummary=true;
// add columns to report
query.AddColumn("Description",300,FieldValueType.String,font);
query.AddColumn("Amount",150,FieldValueType.Number,font);
query.AddSummaryLabel("Amount","Total Liabilities",SummaryOrientation.West,false,fontBold);
query.AddGroupSummaryField("Net Assets:",Color.Black,"Amount","SumTotal",SummaryOperation.Sum,fontBold,0,10);
query=report.AddQuery(tableEquity,"Equity","",SplitByKind.None,2,true);
query.AddLine("EquityLine","Group Header",Color.Black,2,LineOrientation.Horizontal,LinePosition.Top,90,0,-30);
// add columns to report
query.AddColumn("Description",300,FieldValueType.String,font);
query.AddColumn("Amount",150,FieldValueType.Number,font);
query.AddSummaryLabel("Amount","Total Equity",SummaryOrientation.West,false,fontBold);
report.AddPageNum(font);
// execute query
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butExtra_Click(object sender,EventArgs e) {
Cursor=Cursors.WaitCursor;
string programNum=ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum,"PaymentType");
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName="Extra Payments";
report.AddTitle("Title","Payments From "+date1.SelectionStart.ToShortDateString()+" To "+date2.SelectionStart.ToShortDateString(),fontTitle);
report.GetTitle("Title").IsUnderlined=true;
report.AddSubTitle("SubTitle","No Matching X-Charge Transactions for these Payments",fontSubTitle);
QueryObject query;
query=report.AddQuery("SELECT payment.PatNum, LName, FName, payment.DateEntry,payment.PayDate, payment.PayNote,payment.PayAmt "
+"FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
+"LEFT JOIN (SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,PatNum,CCType,CreditCardNum,Expiration,Result,Amount FROM xchargetransaction "
+"WHERE (DATE(TransactionDateTime) BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+") "
+"AND (ResultCode=0 OR ResultCode=10)) AS X "
+"ON X.PatNum=payment.PatNum AND DATE(X.TransactionDateTime)=payment.DateEntry AND X.Amount=payment.PayAmt "
+"WHERE PayType="+programNum+" AND DateEntry BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+" "
+"AND X.TransactionDateTime IS NULL "
+"ORDER BY PayDate ASC, patient.LName","Extra Payments","",SplitByKind.None,1,true);//Valid entries to count have result code 0
query.AddColumn("Pat",50,FieldValueType.String,font);
query.AddColumn("LName",100,FieldValueType.String,font);
query.AddColumn("FName",100,FieldValueType.String,font);
query.AddColumn("DateEntry",100,FieldValueType.Date,font);
query.AddColumn("PayDate",100,FieldValueType.Date,font);
query.AddColumn("PayNote",150,FieldValueType.String,font);
query.AddColumn("PayAmt",70,FieldValueType.Number,font);
query.GetColumnHeader("PayAmt").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("PayAmt").ContentAlignment=ContentAlignment.MiddleRight;
Cursor=Cursors.Default;
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
}
private void butMissing_Click(object sender,EventArgs e) {
Cursor=Cursors.WaitCursor;
string programNum=ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum,"PaymentType");
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,false);
report.ReportName="Missing";
report.AddTitle("Title","XCharge Transactions From "+date1.SelectionStart.ToShortDateString()+" To "+date2.SelectionStart.ToShortDateString(),fontTitle);
report.GetTitle("Title").IsUnderlined=true;
report.AddSubTitle("SubTitle","No Matching Transaction Found in Open Dental",fontSubTitle);
QueryObject query;
DataTable dt=XChargeTransactions.GetMissingTable(programNum,date1.SelectionStart,date2.SelectionStart);
query=report.AddQuery(dt,"Missing Payments","",SplitByKind.None,1,true);//Valid entries to count have result code 0
query.AddColumn("Transaction Date/Time",170,FieldValueType.String,font);
query.AddColumn("Transaction Type",120,FieldValueType.String,font);
query.AddColumn("Clerk ID",80,FieldValueType.String,font);
query.AddColumn("Item#",50,FieldValueType.String,font);
query.AddColumn("Pat",50,FieldValueType.String,font);
query.AddColumn("Credit Card Num",140,FieldValueType.String,font);
query.AddColumn("Exp",50,FieldValueType.String,font);
query.AddColumn("Result",50,FieldValueType.String,font);
query.AddColumn("Amount",60,FieldValueType.Number,font);
query.GetColumnHeader("Amount").ContentAlignment=ContentAlignment.MiddleRight;
Cursor=Cursors.Default;
if(!report.SubmitQueries()) {
return;
}
// display report
FormReportComplex FormR=new FormReportComplex(report);
//FormR.MyReport=report;
FormR.ShowDialog();
}
private void butPrint_Click(object sender, System.EventArgs e) {
if(!SaveData()){
return;
}
Font font=new Font("Tahoma",9);
Font fontBold=new Font("Tahoma",9,FontStyle.Bold);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(false,false);
report.AddTitle("Title",Lan.g(this,"Payment Plan Terms"),fontTitle);
report.AddSubTitle("PracTitle",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date SubTitle",DateTime.Today.ToShortDateString(),fontSubTitle);
string sectName="Report Header";
Section section=report.Sections["Report Header"];
//int sectIndex=report.Sections.GetIndexOfKind(AreaSectionKind.ReportHeader);
Size size=new Size(300,20);//big enough for any text
ContentAlignment alignL=ContentAlignment.MiddleLeft;
ContentAlignment alignR=ContentAlignment.MiddleRight;
int yPos=140;
int space=30;
int x1=175;
int x2=275;
report.ReportObjects.Add(new ReportObject
("Patient Title",sectName,new Point(x1,yPos),size,"Patient",font,alignL));
report.ReportObjects.Add(new ReportObject
("Patient Detail",sectName,new Point(x2,yPos),size,textPatient.Text,font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Guarantor Title",sectName,new Point(x1,yPos),size,"Guarantor",font,alignL));
report.ReportObjects.Add(new ReportObject
("Guarantor Detail",sectName,new Point(x2,yPos),size,textGuarantor.Text,font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Date of Agreement Title",sectName,new Point(x1,yPos),size,"Date of Agreement",font,alignL));
report.ReportObjects.Add(new ReportObject
("Date of Agreement Detail",sectName,new Point(x2,yPos),size,PayPlanCur.PayPlanDate.ToString("d"),font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Principal Title",sectName,new Point(x1,yPos),size,"Principal",font,alignL));
report.ReportObjects.Add(new ReportObject
("Principal Detail",sectName,new Point(x2,yPos),size,TotPrinc.ToString("n"),font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Annual Percentage Rate Title",sectName,new Point(x1,yPos),size,"Annual Percentage Rate",font,alignL));
report.ReportObjects.Add(new ReportObject
("Annual Percentage Rate Detail",sectName,new Point(x2,yPos),size,PayPlanCur.APR.ToString("f1"),font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Total Finance Charges Title",sectName,new Point(x1,yPos),size,"Total Finance Charges",font,alignL));
report.ReportObjects.Add(new ReportObject
("Total Finance Charges Detail",sectName,new Point(x2,yPos),size,TotInt.ToString("n"),font,alignR));
yPos+=space;
report.ReportObjects.Add(new ReportObject
("Total Cost of Loan Title",sectName,new Point(x1,yPos),size,"Total Cost of Loan",font,alignL));
report.ReportObjects.Add(new ReportObject
("Total Cost of Loan Detail",sectName,new Point(x2,yPos),size,TotPrincInt.ToString("n"),font,alignR));
yPos+=space;
section.Height=yPos+30;
DataTable tbl=new DataTable();
tbl.Columns.Add("date");
tbl.Columns.Add("prov");
tbl.Columns.Add("description");
tbl.Columns.Add("principal");
tbl.Columns.Add("interest");
tbl.Columns.Add("due");
tbl.Columns.Add("payment");
tbl.Columns.Add("balance");
DataRow row;
for(int i=0;i<gridCharges.Rows.Count;i++) {
row=tbl.NewRow();
row["date"]=gridCharges.Rows[i].Cells[0].Text;
row["prov"]=gridCharges.Rows[i].Cells[1].Text;
row["description"]=gridCharges.Rows[i].Cells[2].Text;
row["principal"]=gridCharges.Rows[i].Cells[3].Text;
row["interest"]=gridCharges.Rows[i].Cells[4].Text;
row["due"]=gridCharges.Rows[i].Cells[5].Text;
row["payment"]=gridCharges.Rows[i].Cells[6].Text;
row["balance"]=gridCharges.Rows[i].Cells[7].Text;
tbl.Rows.Add(row);
}
QueryObject query=report.AddQuery(tbl,"","",SplitByKind.None,1,true);
query.AddColumn("ChargeDate",80,FieldValueType.Date,font);
query.GetColumnHeader("ChargeDate").StaticText="Date";
query.AddColumn("Provider",80,FieldValueType.String,font);
query.AddColumn("Description",140,FieldValueType.String,font);
query.AddColumn("Principal",60,FieldValueType.Number,font);
query.AddColumn("Interest",52,FieldValueType.Number,font);
query.AddColumn("Due",60,FieldValueType.Number,font);
query.AddColumn("Payment",60,FieldValueType.Number,font);
query.AddColumn("Balance",60,FieldValueType.String,font);
query.GetColumnHeader("Balance").ContentAlignment=ContentAlignment.MiddleRight;
query.GetColumnDetail("Balance").ContentAlignment=ContentAlignment.MiddleRight;
report.ReportObjects.Add(new ReportObject("Note","Report Footer",new Point(x1,20),new Size(500,200),textNote.Text,font,ContentAlignment.TopLeft));
report.ReportObjects.Add(new ReportObject("Signature","Report Footer",new Point(x1,220),new Size(500,20),"Signature of Guarantor: ____________________________________________",font,alignL));
if(!report.SubmitQueries()) {
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
}
private void RunAnnual() {
_dateFrom=PIn.Date(textDateFrom.Text);
_dateTo=PIn.Date(textDateTo.Text);
List<DataSet> listProdData=new List<DataSet>();
List<string> listServerNames=new List<string>();
List<long> listProvNums=new List<long>();
List<long> listClinicNums=new List<long>();
string computerName="";
string database="";
string user="";
string mySqlPassword="";
string strFailedConn="";
string webServiceURI="";
string odPassword="";
for(int i=0;i<ConnList.Count;i++) {
if(ConnList[i].DatabaseName!="") {
//ServerName=localhost DatabaseName=opendental MySqlUser=root MySqlPassword=
computerName=ConnList[i].ServerName;
database=ConnList[i].DatabaseName;
user=ConnList[i].MySqlUser;
if(ConnList[i].MySqlPassword!="") {
mySqlPassword=CentralConnections.Decrypt(ConnList[i].MySqlPassword,EncryptionKey);
}
RemotingClient.ServerURI="";
}
else if(ConnList[i].ServiceURI!="") {
webServiceURI=ConnList[i].ServiceURI;
RemotingClient.ServerURI=webServiceURI;
try {
odPassword=CentralConnections.Decrypt(ConnList[i].OdPassword,EncryptionKey);
Security.CurUser=Security.LogInWeb(ConnList[i].OdUser,odPassword,"",Application.ProductVersion,ConnList[i].WebServiceIsEcw);
Security.PasswordTyped=odPassword;
}
catch {
//Not sure if anything needs to be here
}
}
else {
MessageBox.Show("Either a database or a Middle Tier URI must be specified in the connection.");
return;
}
DataConnection.DBtype=DatabaseType.MySql;
OpenDentBusiness.DataConnection dcon=new OpenDentBusiness.DataConnection();
try {
if(RemotingClient.ServerURI!="") {
RemotingClient.RemotingRole=RemotingRole.ClientWeb;
}
else {
dcon.SetDb(computerName,database,user,mySqlPassword,"","",DataConnection.DBtype);
RemotingClient.RemotingRole=RemotingRole.ClientDirect;
}
Cache.RefreshCache(((int)InvalidType.AllLocal).ToString());
}
catch(Exception ex) {
if(strFailedConn=="") {
strFailedConn+="Some connections could not successfully be created for this report:\r\n";
}
if(RemotingClient.ServerURI!="") {
strFailedConn+="WebService: "+webServiceURI+"\r\n";
}
else {
strFailedConn+="Server: "+computerName+" DataBase: "+database+"\r\n";
}
continue;
}
listProvNums=new List<long>();
for(int j=0;j<ProviderC.ListShort.Count;j++) {
listProvNums.Add(ProviderC.ListShort[j].ProvNum);
}
listClinicNums=new List<long>();
listClinicNums.Add(0);//Unassigned
for(int j=0;j<Clinics.List.Length;j++) {
listClinicNums.Add(Clinics.List[j].ClinicNum);
}
listProdData.Add(RpProdInc.GetAnnualDataForClinics(_dateFrom,_dateTo,listProvNums,listClinicNums,radioWriteoffPay.Checked,true,true));
if(ConnList[i].ServiceURI!="") {
listServerNames.Add(ConnList[i].ServiceURI);
}
else {
listServerNames.Add(ConnList[i].ServerName);
}
//Cleaning up after WebService connections.
Security.CurUser=null;
Security.PasswordTyped=null;
}
ReportComplex report=new ReportComplex(true,true);
report.ReportName="Appointments";
report.AddTitle("Title",Lan.g(this,"Annual Production and Income"));
report.AddSubTitle("PracName",PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle("Date",_dateFrom.ToShortDateString()+" - "+_dateTo.ToShortDateString());
report.AddSubTitle("Providers",Lan.g(this,"All Providers"));
report.AddSubTitle("Clinics",Lan.g(this,"All Clinics"));
//setup query
QueryObject query;
DataSet dsTotal=new DataSet();
for(int i=0;i<listProdData.Count;i++) {
DataTable dt=listProdData[i].Tables["Clinic"];
DataTable dtTot=listProdData[i].Tables["Total"].Copy();
dtTot.TableName=dtTot.TableName+"_"+i;
dsTotal.Tables.Add(dtTot);
query=report.AddQuery(dt,listServerNames[i],"Clinic",SplitByKind.Value,1,true);
// add columns to report
query.AddColumn("Month",75,FieldValueType.String);
query.AddColumn("Production",120,FieldValueType.Number);
query.AddColumn("Adjustments",120,FieldValueType.Number);
query.AddColumn("Writeoff",120,FieldValueType.Number);
query.AddColumn("Tot Prod",120,FieldValueType.Number);
query.AddColumn("Pt Income",120,FieldValueType.Number);
query.AddColumn("Ins Income",120,FieldValueType.Number);
query.AddColumn("Total Income",120,FieldValueType.Number);
}
if(dsTotal.Tables.Count==0) {
MsgBox.Show(this,"This report returned no values");
return;
}
DataTable dtTotal;
decimal production;
decimal adjust;
decimal inswriteoff; //spk 5/19/05
decimal totalproduction;
decimal ptincome;
decimal insincome;
decimal totalincome;
DateTime[] dates=new DateTime[_dateTo.Month-_dateFrom.Month+1];
dtTotal=dsTotal.Tables[0].Clone();
for(int i=0;i<dates.Length;i++) {//usually 12 months in loop
dates[i]=_dateFrom.AddMonths(i);
DataRow row=dtTotal.NewRow();
row[0]=dates[i].ToString("MMM yy");//JAN 14
production=0;
adjust=0;
inswriteoff=0; //spk 5/19/05
totalproduction=0;
ptincome=0;
insincome=0;
totalincome=0;
for(int j=0;j<dsTotal.Tables.Count;j++) {
for(int k=0;k<dsTotal.Tables[j].Rows.Count;k++) {
if(dsTotal.Tables[j].Rows[k][0].ToString()==dates[i].ToString("MMM yy")) {
production+=PIn.Decimal(dsTotal.Tables[j].Rows[k]["Production"].ToString());
adjust+=PIn.Decimal(dsTotal.Tables[j].Rows[k]["Adjustments"].ToString());
inswriteoff-=PIn.Decimal(dsTotal.Tables[j].Rows[k]["WriteOff"].ToString());
ptincome+=PIn.Decimal(dsTotal.Tables[j].Rows[k]["Pt Income"].ToString());
insincome+=PIn.Decimal(dsTotal.Tables[j].Rows[k]["Ins Income"].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");
dtTotal.Rows.Add(row);
}
//For clinics only, we want to add one last table to the end of the report that totals all clinics together.
query=report.AddQuery(dtTotal,"Totals","",SplitByKind.None,2,true);
query.AddColumn("Month",75,FieldValueType.String);
query.AddColumn("Production",120,FieldValueType.Number);
query.AddColumn("Adjustments",120,FieldValueType.Number);
query.AddColumn("Writeoff",120,FieldValueType.Number);
query.AddColumn("Tot Prod",120,FieldValueType.Number);
query.AddColumn("Pt Income",120,FieldValueType.Number);
query.AddColumn("Ins Income",120,FieldValueType.Number);
query.AddColumn("Total Income",120,FieldValueType.Number);
report.AddPageNum();
// execute query
if(!report.SubmitQueries()) {//Does not actually submit queries because we use datatables in the central management tool.
return;
}
if(strFailedConn!="") {
MsgBoxCopyPaste msgBoxCP=new MsgBoxCopyPaste(strFailedConn);
msgBoxCP.ShowDialog();
}
// display the report
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void ExecuteReport(){
DateTime dateStart;
DateTime dateEnd;
if(!DateTime.TryParse(textDateStart.Text,out dateStart)) {
MsgBox.Show(this,"Please input a valid date.");
return;
}
if(!DateTime.TryParse(textDateEnd.Text,out dateEnd)) {
MsgBox.Show(this,"Please input a valid date.");
return;
}
if(String.IsNullOrWhiteSpace(textCarrier.Text)) {
MsgBox.Show(this,"Carrier can not be blank. Please input a value for carrier.");
return;
}
Font font=new Font("Tahoma",9);
Font fontTitle=new Font("Tahoma",17,FontStyle.Bold);
Font fontSubTitle=new Font("Tahoma",10,FontStyle.Bold);
ReportComplex report=new ReportComplex(true,true);
report.AddTitle("Title",Lan.g(this,"Capitation Utilization"),fontTitle);
report.AddSubTitle("PracTitle",PrefC.GetString(PrefName.PracticeTitle),fontSubTitle);
report.AddSubTitle("Date",textDateStart.Text+" - "+textDateEnd.Text,fontSubTitle);
QueryObject query=report.AddQuery(@"SELECT carrier.CarrierName,CONCAT(CONCAT(patSub.LName,', '),patSub.FName)
,patSub.SSN,CONCAT(CONCAT(patPat.LName,', '),patPat.FName)
,patPat.Birthdate,procedurecode.ProcCode,procedurecode.Descript
,procedurelog.ToothNum,procedurelog.Surf,procedurelog.ProcDate
,procedurelog.ProcFee,procedurelog.ProcFee-claimproc.WriteOff
FROM procedurelog,patient AS patSub,patient AS patPat
,insplan,inssub,carrier,procedurecode,claimproc
WHERE procedurelog.PatNum = patPat.PatNum
AND claimproc.InsSubNum = inssub.InsSubNum
AND procedurelog.ProcNum = claimproc.ProcNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.Status = 7
AND claimproc.NoBillIns = 0
AND inssub.Subscriber = patSub.PatNum
AND insplan.CarrierNum = carrier.CarrierNum
AND procedurelog.CodeNum = procedurecode.CodeNum "
+"AND carrier.CarrierName LIKE '%"+POut.String(textCarrier.Text)+"%' "
+"AND procedurelog.ProcDate >= "+POut.Date(dateStart)+" "
+"AND procedurelog.ProcDate <= "+POut.Date(dateEnd)+" "
+"AND insplan.PlanType = 'c' "
+"AND procedurelog.ProcStatus = 2","","",SplitByKind.None,1,true);
query.AddColumn("Carrier",150,FieldValueType.String,font);
query.GetColumnDetail("Carrier").SuppressIfDuplicate=true;
query.AddColumn("Subscriber",120,FieldValueType.String,font);
query.GetColumnDetail("Subscriber").SuppressIfDuplicate=true;
query.AddColumn("Subsc SSN",70,FieldValueType.String,font);
query.GetColumnDetail("Subsc SSN").SuppressIfDuplicate=true;
query.AddColumn("Patient",120,FieldValueType.String,font);
query.AddColumn("Pat DOB",80,FieldValueType.Date,font);
query.AddColumn("Code",50,FieldValueType.String,font);
query.AddColumn("Proc Description",120,FieldValueType.String,font);
query.AddColumn("Tth",30,FieldValueType.String,font);
query.AddColumn("Surf",40,FieldValueType.String,font);
query.AddColumn("Date",80,FieldValueType.Date,font);
query.AddColumn("UCR Fee",70,FieldValueType.Number,font);
query.AddColumn("Co-Pay",70,FieldValueType.Number,font);
if(!report.SubmitQueries()) {
//DialogResult=DialogResult.Cancel;
return;
}
FormReportComplex FormR=new FormReportComplex(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
