context: using System;
  using System.Collections;
  using System.Collections.Generic;
  using System.Collections.ObjectModel;
  using System.Data;
  using System.Diagnostics;
  using System.Reflection;
  using System.Text.RegularExpressions;
  using System.Windows.Forms;
  
  namespace OpenDentBusiness{
  	
  	///<summary></summary>
  	public class Statements{
  		///<Summary>Gets one statement from the database.</Summary>
  		public static Statement CreateObject(long statementNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<Statement>(MethodBase.GetCurrentMethod(),statementNum);
  			}
  			return Crud.StatementCrud.SelectOne(statementNum);
  		}
  
  		///<summary></summary>
  		public static long Insert(Statement statement) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				statement.StatementNum=Meth.GetLong(MethodBase.GetCurrentMethod(),statement);
  				return statement.StatementNum;
  			}
  			return Crud.StatementCrud.Insert(statement);
  		}
  
  		///<summary></summary>
  		public static void Update(Statement statement) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),statement);
  				return;
  			}
  			Crud.StatementCrud.Update(statement);
  		}
  
  		///<summary></summary>
  		public static void Delete(Statement statement) {
  			//No need to check RemotingRole; no call to db.
  			Delete(statement.StatementNum);
  		}
  
  		///<summary>For deleting a statement when user clicks Cancel.  No need to make entry in DeletedObject table.</summary>
  		public static void Delete(long statementNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),statementNum);
  				return;
  			}
  			//Removed dependencies from procedures and adjustments.
  			Procedures.DetachFromInvoice(statementNum);
  			Adjustments.DetachFromInvoice(statementNum);
  			Crud.StatementCrud.Delete(statementNum);
  		}
  
  		///<summary>Queries the database to determine if there are any unsent statements.</summary>
  		public static bool UnsentStatementsExist(){
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetBool(MethodBase.GetCurrentMethod());
  			}
  			string command="SELECT COUNT(*) FROM statement WHERE IsSent=0";
  			if(Db.GetCount(command)=="0"){
  				return false;
  			}
  			return true;
  		}
  
  		///<summary>Queries the database to determine if there are any unsent statements for a particular clinic.</summary>
  		public static bool UnsentClinicStatementsExist(long clinicNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetBool(MethodBase.GetCurrentMethod(),clinicNum);
  			}
  			if(clinicNum==0) {//All clinics.
  				return UnsentStatementsExist();
  			}
  			string command=@"SELECT COUNT(*) FROM statement 
  				LEFT JOIN patient ON statement.PatNum=patient.PatNum
  				WHERE statement.IsSent=0
  				AND patient.ClinicNum="+clinicNum;
  			if(Db.GetCount(command)=="0") {
  				return false;
  			}
  			return true;
  		}
  
  		public static void MarkSent(long statementNum,DateTime dateSent) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),statementNum,dateSent);
  				return;
  			}
  			string command="UPDATE statement SET DateSent="+POut.Date(dateSent)+", "
  				+"IsSent=1 WHERE StatementNum="+POut.Long(statementNum);
  			Db.NonQ(command);
  		}
  
  		public static void AttachDoc(long statementNum,long docNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),statementNum,docNum);
  				return;
  			}
  			string command="UPDATE statement SET DocNum="+POut.Long(docNum)
  				+" WHERE StatementNum="+POut.Long(statementNum);
  			Db.NonQ(command);
  		}
  
  		///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
  		public static DataTable GetBilling(bool isSent,int orderBy,DateTime dateFrom,DateTime dateTo,List<long> clinicNums){
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetTable(MethodBase.GetCurrentMethod(),isSent,orderBy,dateFrom,dateTo,clinicNums);
  			}
  			DataTable table=new DataTable();
  			DataRow row;
  			//columns that start with lowercase are altered for display rather than being raw data.
  			table.Columns.Add("amountDue");
  			table.Columns.Add("balTotal");
  			table.Columns.Add("billingType");
  			table.Columns.Add("insEst");
  			table.Columns.Add("IsSent");
  			table.Columns.Add("lastStatement");
  			table.Columns.Add("mode");
  			table.Columns.Add("name");
  			table.Columns.Add("PatNum");
  			table.Columns.Add("payPlanDue");
  			table.Columns.Add("StatementNum");
  			List<DataRow> rows=new List<DataRow>();
  			string command="SELECT BalTotal,BillingType,FName,InsEst,statement.IsSent,"
  				+"IFNULL(MAX(s2.DateSent),"+POut.Date(DateTime.MinValue)+") LastStatement,"
  				+"LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
  				+"statement.PatNum,statement.StatementNum "
  				+"FROM statement "
  				+"LEFT JOIN patient ON statement.PatNum=patient.PatNum "
  				+"LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum "
  				+"AND s2.IsSent=1 ";
  			if(PrefC.GetBool(PrefName.BillingIgnoreInPerson)) {
  				command+="AND s2.Mode_ !=1 ";
  			}
  			if(orderBy==0){//BillingType
  				command+="LEFT JOIN definition ON patient.BillingType=definition.DefNum ";
  			}
  			command+="WHERE statement.IsSent="+POut.Bool(isSent)+" ";
  			//if(dateFrom.Year>1800){
  			command+="AND statement.DateSent>="+POut.Date(dateFrom)+" ";//greater than midnight this morning
  			//}
  			//if(dateFrom.Year>1800){
  			command+="AND statement.DateSent<"+POut.Date(dateTo.AddDays(1))+" ";//less than midnight tonight
  			//}
  			if(clinicNums.Count>0) {
  				command+="AND patient.ClinicNum IN ("+string.Join(",",clinicNums)+") ";
  			}
  			command+="GROUP BY BalTotal,BillingType,FName,InsEst,statement.IsSent,"
  				+"LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
  				+"statement.PatNum,statement.StatementNum "; 
  			if(orderBy==0){//BillingType
  				command+="ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue";
  			}
  			else{
  				command+="ORDER BY LName,FName";
  			}
  			DataTable rawTable=Db.GetTable(command);
  			Patient pat;
  			StatementMode mode;
  			double balTotal;
  			double insEst;
  			double payPlanDue;
  			DateTime lastStatement;
  			for(int i=0;i<rawTable.Rows.Count;i++){
  				row=table.NewRow();
  				balTotal=PIn.Double(rawTable.Rows[i]["BalTotal"].ToString());
  				insEst=PIn.Double(rawTable.Rows[i]["InsEst"].ToString());
  				payPlanDue=PIn.Double(rawTable.Rows[i]["PayPlanDue"].ToString());
  				row["amountDue"]=(balTotal-insEst).ToString("F");
  				row["balTotal"]=balTotal.ToString("F");;
  				row["billingType"]=DefC.GetName(DefCat.BillingTypes,PIn.Long(rawTable.Rows[i]["BillingType"].ToString()));
  				if(insEst==0){
  					row["insEst"]="";
  				}
  				else{
  					row["insEst"]=insEst.ToString("F");
  				}
  				row["IsSent"]=rawTable.Rows[i]["IsSent"].ToString();
  				lastStatement=PIn.Date(rawTable.Rows[i]["LastStatement"].ToString());
  				if(lastStatement.Year<1880){
  					row["lastStatement"]="";
  				}
  				else{
  					row["lastStatement"]=lastStatement.ToShortDateString();
  				}
  				mode=(StatementMode)PIn.Long(rawTable.Rows[i]["Mode_"].ToString());
  				row["mode"]=Lans.g("enumStatementMode",mode.ToString());
  				pat=new Patient();
  				pat.LName=rawTable.Rows[i]["LName"].ToString();
  				pat.FName=rawTable.Rows[i]["FName"].ToString();
  				pat.Preferred=rawTable.Rows[i]["Preferred"].ToString();
  				pat.MiddleI=rawTable.Rows[i]["MiddleI"].ToString();
  				row["name"]=pat.GetNameLF();
  				row["PatNum"]=rawTable.Rows[i]["PatNum"].ToString();
  				if(payPlanDue==0){
  					row["payPlanDue"]="";
  				}
  				else{
  					row["payPlanDue"]=payPlanDue.ToString("F");
  				}
  				row["StatementNum"]=rawTable.Rows[i]["StatementNum"].ToString();
  				rows.Add(row);
  			}
  			for(int i=0;i<rows.Count;i++) {
  				table.Rows.Add(rows[i]);
  			}
  			return table;
  		}
  
  		///<summary>This query is flawed.</summary>
  		public static DataTable GetStatementNotesPracticeWeb(long PatientID) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetTable(MethodBase.GetCurrentMethod(),PatientID);
  			}
  			string command=@"SELECT Note FROM Statement Where Patnum="+PatientID;
  			return Db.GetTable(command);
  		}
  
  		///<summary>This query is flawed.</summary>
  		public static Statement GetStatementInfoPracticeWeb(long PatientID) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<Statement>(MethodBase.GetCurrentMethod(),PatientID);
  			}
  			string command=@"Select SinglePatient,DateRangeFrom,DateRangeTo,Intermingled
                          FROM statement WHERE PatNum = "+PatientID;
  			return Crud.StatementCrud.SelectOne(command);
  		}
  
  		///<summary>Fetches StatementNums restricted by the DateTStamp, PatNums and a limit of records per patient. If limitPerPatient is zero all StatementNums of a patient are fetched</summary>
  		public static List<long> GetChangedSinceStatementNums(DateTime changedSince,List<long> eligibleForUploadPatNumList,int limitPerPatient) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<List<long>>(MethodBase.GetCurrentMethod(),changedSince,eligibleForUploadPatNumList);
  			}
  			List<long> statementnums = new List<long>();
  			string limitStr="";
  			if(limitPerPatient>0) {
  
gt: 				limitStr="LIMIT "+ limitPerPatient;