///<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;
}
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 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;
}