public override global::System.Data.DataSet Clone()
{
DsTokenSummary cln = ((DsTokenSummary)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
DsTokenSummary ds = new DsTokenSummary();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
private void PrintPreview(bool preview)
{
DsTokenSummary printds = new DsTokenSummary();
DsTokenSummary.PatientRegistrationDataTable resultTalbe = new DsTokenSummary.PatientRegistrationDataTable();
rptAllTokenReceipt crp = new rptAllTokenReceipt();
string BranchName = ConfigurationManager.AppSettings["Name"].ToString();
string BranchAddress = ConfigurationManager.AppSettings["Address"].ToString();
printds = new TokenSummaryBLL().GetTokenSummary(dtpFrom.Value.Date, dtpTo.Value.Date, rbAll.Checked, rbInjection.Checked, rbCheckup.Checked, rbLab.Checked, rbMedicine.Checked, cb50.Checked);
string tokenType = "";
string tokenRange = "";
if (rbAll.Checked == true)
{
tokenType = "All tokens";
}
else if (rbCheckup.Checked == true)
{
tokenType = "Checkup only";
}
else if (rbInjection.Checked == true)
{
tokenType = "Injection only";
}
else if (rbLab.Checked == true)
{
tokenType = "Lab test only";
}
else if (rbMedicine.Checked == true)
{
tokenType = "Medicines";
}
if (rbRange.Checked == true)
{
try
{
int to = 0, from = 0;
bool r;
if (txtToToken.Text.Trim() != string.Empty)
{
r = int.TryParse(txtToToken.Text.Trim(), out to);
}
if (txtFromToken.Text.Trim() != string.Empty)
{
r = int.TryParse(txtFromToken.Text.Trim(), out from);
}
if (to == 0)
{
MessageBox.Show("Please Enter the Valid Token Number");
this.txtToToken.Focus();
return;
}
if (from == 0)
{
MessageBox.Show("Please Enter the Valid Token Number");
this.txtFromToken.Focus();
return;
}
string filterExpression = "TokenNumber >= " + from.ToString() + " and TokenNumber <= " + to.ToString();
DataRow[] TokenRows = ((DataTable)printds.PatientRegistration).Select(filterExpression);
foreach (DataRow row in TokenRows)
{
resultTalbe.ImportRow(row);
}
tokenRange = from.ToString() + " to " + to.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Token Range Invalid");
return;
}
}
else
{
resultTalbe = printds.PatientRegistration;
tokenRange = "All Tokens";
}
crp.SetDataSource((DataTable)resultTalbe);
crp.SetParameterValue("TokenType", tokenType);
crp.SetParameterValue("fromDate", dtpFrom.Value.Date);
crp.SetParameterValue("toDate", dtpTo.Value.Date);
crp.SetParameterValue("TokenRange", tokenRange);
crp.SetParameterValue("BranchName", BranchName);
crp.SetParameterValue("BranchAddress", BranchAddress);
FrmReportViewer frmViewer = new FrmReportViewer();
frmViewer.crystalReportViewer1.ReportSource = crp;
if (preview)
{
frmViewer.ShowDialog();
}
else
{
frmViewer.crystalReportViewer1.RefreshReport();
frmViewer.crystalReportViewer1.PrintReport();
}
}
private void PrintPreview(bool preview)
{
DsTokenSummary printds = new DsTokenSummary();
DsTokenSummary.PatientRegistrationDataTable resultTalbe = new DsTokenSummary.PatientRegistrationDataTable();
rptAllTokenReceipt crp = new rptAllTokenReceipt();
string BranchName = ConfigurationManager.AppSettings["Name"].ToString();
string BranchAddress = ConfigurationManager.AppSettings["Address"].ToString();
string shifttime = "";
int? uNO = 0;
string UserName = "******";
if (chbUsers.Checked)
{
uNO = Convert.ToInt16(cbxUser.SelectedValue.ToString());
List <User> selectedusers = this.users.Where(u => u.Userno == uNO).ToList <User>();
foreach (var item in selectedusers)
{
UserName = item.UserName;
}
//UserBLL userBLL = new UserBLL();
//List<User> users = this.userBLL.GetUsers().Where(u => u.Userno == uNO).ToList<User>();
}
if (cbxShifts.SelectedIndex == 0 || cbxShifts.SelectedIndex == -1)
{
printds = new TokenSummaryBLL().GetTokenSummary(dtpFrom.Value.Date, dtpTo.Value.Date, rbAll.Checked, rbInjection.Checked, rbCheckup.Checked, rbLab.Checked, rbMedicine.Checked, cb50.Checked, uNO);
}
else
{
printds = new TokenSummaryBLL().GetTokenSummary(dtpFrom.Value.Date, dtpTo.Value.Date, rbAll.Checked, rbInjection.Checked, rbCheckup.Checked, rbLab.Checked, rbMedicine.Checked, cb50.Checked, (Shift)cbxShifts.SelectedItem, uNO);
Shift shift = (Shift)cbxShifts.SelectedItem;
shift = new BLL.ShiftsBLL().GetShiftStartEndTime(shift, this.dtpFrom.Value.Date);
shifttime = shift.ChangeDateTime.ToString("hh:mm:ss");
}
string tokenType = "";
string tokenRange = "";
if (rbAll.Checked == true)
{
tokenType = "All tokens";
}
else if (rbCheckup.Checked == true)
{
tokenType = "Checkup only";
}
else if (rbInjection.Checked == true)
{
tokenType = "Injection only";
}
else if (rbLab.Checked == true)
{
tokenType = "Lab test only";
}
else if (rbMedicine.Checked == true)
{
tokenType = "Medicines";
}
tokenType += " - " + cbxShifts.Text + ' ' + shifttime;
string filterExpression;
if (rbRange.Checked == true)
{
try
{
int to = 0, from = 0;
bool r;
if (txtToToken.Text.Trim() != string.Empty)
{
r = int.TryParse(txtToToken.Text.Trim(), out to);
}
if (txtFromToken.Text.Trim() != string.Empty)
{
r = int.TryParse(txtFromToken.Text.Trim(), out from);
}
if (to == 0)
{
MessageBox.Show("Please Enter the Valid Token Number");
this.txtToToken.Focus();
return;
}
if (from == 0)
{
MessageBox.Show("Please Enter the Valid Token Number");
this.txtFromToken.Focus();
return;
}
filterExpression = "TokenNumber >= " + from.ToString() + " and TokenNumber <= " + to.ToString();
DataRow[] TokenRows = ((DataTable)printds.PatientRegistration).Select(filterExpression);
foreach (DataRow row in TokenRows)
{
resultTalbe.ImportRow(row);
}
tokenRange = from.ToString() + " to " + to.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Token Range Invalid");
return;
}
}
else
{
resultTalbe = printds.PatientRegistration;
tokenRange = "All Tokens";
}
//if (rbCheckup.Checked)
//{
// if (chbPoor.Checked)
// {
// if (String.IsNullOrEmpty(filterExpression))
// }
//}
crp.SetDataSource((DataTable)resultTalbe);
crp.SetParameterValue("TokenType", tokenType);
crp.SetParameterValue("fromDate", dtpFrom.Value.Date);
crp.SetParameterValue("toDate", dtpTo.Value.Date);
crp.SetParameterValue("TokenRange", tokenRange);
crp.SetParameterValue("BranchName", BranchName);
crp.SetParameterValue("BranchAddress", BranchAddress);
crp.SetParameterValue("UserName", UserName);
FrmReportViewer frmViewer = new FrmReportViewer();
frmViewer.crystalReportViewer1.ReportSource = crp;
//frmViewer.crystalReportViewer1.SelectionFormula = ""
if (preview)
{
frmViewer.ShowDialog();
}
else
{
frmViewer.crystalReportViewer1.RefreshReport();
frmViewer.crystalReportViewer1.PrintReport();
}
}
public DsTokenSummary GetTokenSummary(DateTime fromDate, DateTime toDate, bool All, bool injection, bool Checkup, bool labtest)
{
DsTokenSummary ds = new DsTokenSummary();
try
{
string selectCheckup = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount,'' as TestName FROM PatientRegistration where TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#";
string selectInjection = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,'' as TestName FROM InjectionLabtest Where LabtestID is null and TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#";
string selectLabTest = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#";
string selectAll = " SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,'' as TestName FROM PatientRegistration where TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#"
+ "Union ALL"
+ " SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount,'' as TestName FROM InjectionLabtest where LabtestID is null and TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#"
+ " Union All"
+ " SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount,[Test (A-Z)] as TestName FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and TokenDate>=#" + fromDate.Date + "# and TokenDate<=#" + toDate.Date + "#";
//(select Top 1 [Test (A-Z)] from LabTest lt,InjectionLabtest ilt where lt.ID=ilt.LabTestid)
//+ " Union All"
//+ " SELECT lt.[Test (A-Z)] as TestName,'LabTest' as TokenType, TokenDate, TokenNumber, PatientFirstName , PatientLastName , PatientNIC, PatientAddress, TokenAmount FROM LabTest lt ,InjectionLabTest ilt where lt.ID = ilt.LabTestId ";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (All == true)
{
select = selectAll;
}
else if (injection == true)
{
select = selectInjection;
}
else if (Checkup == true)
{
select = selectCheckup;
}
else if (labtest == true)
{
select = selectLabTest;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return(ds);
// return labTests;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public DsTokenSummary GetTokenSummary(DateTime fromDate, DateTime toDate, bool All, bool injection, bool Checkup, bool labtest, bool Medicine, bool rb50, int?uNo)
{
DsTokenSummary ds = new DsTokenSummary();
try
{ //IIF(ISNULL(CashRecievedByUser),0,CashRecievedByUser)
//string selectCheckup = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount,'' as TestName,TokenAmount as CashRecievedByUser FROM PatientRegistration where TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "'";
//string selectInjection = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,'' as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "'";
//string selectLabTest = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "'";
//string MedicineQry = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "'";
//string selectCheckup50 = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM PatientRegistration where TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date +
// "' and TokenAmount = 50";
//string selectInjection50 = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "' and TokenAmount = 50";
//string selectLabTest50 = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "' and TokenAmount = 50";
//string MedicineQry50 = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and TokenDate>='" + fromDate.Date + "' and TokenDate<='" + toDate.Date + "' and TokenAmount = 50";
//string selectAll50 = selectCheckup50 + " Union ALL " + selectInjection50 + " Union All " + selectLabTest50 + " Union All " + MedicineQry50;
if (uNo == 0)
{
selectCheckup = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount,'' as TestName,TokenAmount as CashRecievedByUser FROM PatientRegistration where CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "'";
selectInjection = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,'' as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "'";
selectLabTest = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "'";
MedicineQry = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "'";
selectCheckup50 = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM PatientRegistration where CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") +
"' and TokenAmount = 50";
selectInjection50 = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50";
selectLabTest50 = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50";
MedicineQry50 = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50";
}
else
if (uNo > 0)
{
selectCheckup = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount,'' as TestName,TokenAmount as CashRecievedByUser FROM PatientRegistration where CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenBy = " + uNo + "";
selectInjection = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,'' as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenBy = " + uNo + "";
selectLabTest = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenBy = " + uNo + "";
MedicineQry = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((CashRecievedByUser)IS NULL,0,CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenBy = " + uNo + "";
selectCheckup50 = "SELECT 'Checkup' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM PatientRegistration where CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") +
"' and TokenAmount = 50 and TokenBy = " + uNo + "";
selectInjection50 = "SELECT 'Injection' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,'' as TestName,IIF((TokenAmount)IS NULL,0,TokenAmount) as CashRecievedByUser FROM InjectionLabtest Where LabtestID is null and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50 and TokenBy = " + uNo + "";
selectLabTest50 = "SELECT 'LabTest' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=0 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50 and TokenBy = " + uNo + "";
MedicineQry50 = "SELECT 'Medicines' as TokenType,TokenDate,TokenNumber,PatientFirstName,PatientLastName,PatientNIC,PatientAge,PatientMobile,PatientAddress,TokenAmount ,[Test (A-Z)] as TestName,IIF((InjectionLabtest.CashRecievedByUser)IS NULL,0,InjectionLabtest.CashRecievedByUser) as CashRecievedByUser FROM LabTest ,InjectionLabtest where LabTest.ID=InjectionLabtest.LabTestid and LabtestID >0 and LabTest.IsMedicine=1 and CONVERT(date, TokenDate) >='" + fromDate.Date.ToString("MM/dd/yyyy") + "' and CONVERT(date, TokenDate) <='" + toDate.Date.ToString("MM/dd/yyyy") + "' and TokenAmount = 50 and TokenBy = " + uNo + "";
}
selectAll50 = selectCheckup50 + " Union ALL " + selectInjection50 + " Union All " + selectLabTest50 + " Union All " + MedicineQry50;
selectAll = selectCheckup + " Union ALL " + selectInjection + " Union All " + selectLabTest + " Union All " + MedicineQry;
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
string select = "";
if (All == true)
{
select = selectAll;
}
else if (injection == true)
{
select = selectInjection;
}
else if (Checkup == true)
{
select = selectCheckup;
}
else if (labtest == true)
{
select = selectLabTest;
}
else if (Medicine == true)
{
select = MedicineQry;
}
if (All == true && rb50 == true)
{
select = selectAll50;
}
else if (injection == true && rb50 == true)
{
select = selectInjection50;
}
else if (Checkup == true && rb50 == true)
{
select = selectCheckup50;
}
else if (labtest == true && rb50 == true)
{
select = selectLabTest50;
}
else if (Medicine == true && rb50 == true)
{
select = MedicineQry50;
}
da = new OleDbDataAdapter(select, con);
da.Fill(ds, ds.Tables[0].TableName);
}
return(ds);
// return labTests;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
DsTokenSummary ds = new DsTokenSummary();
global::System.Xml.Schema.XmlSchemaAny any1 = new global::System.Xml.Schema.XmlSchemaAny();
any1.Namespace = "http://www.w3.org/2001/XMLSchema";
any1.MinOccurs = new decimal(0);
any1.MaxOccurs = decimal.MaxValue;
any1.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any1);
global::System.Xml.Schema.XmlSchemaAny any2 = new global::System.Xml.Schema.XmlSchemaAny();
any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1";
any2.MinOccurs = new decimal(1);
any2.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any2);
global::System.Xml.Schema.XmlSchemaAttribute attribute1 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute1.Name = "namespace";
attribute1.FixedValue = ds.Namespace;
type.Attributes.Add(attribute1);
global::System.Xml.Schema.XmlSchemaAttribute attribute2 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute2.Name = "tableTypeName";
attribute2.FixedValue = "PatientRegistrationDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
