public DSMedicines GetListOfMed()
{
DSMedicines ds = new DSMedicines();
try
{
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
string Pur = "select lt.ID as MedicineID, lt.[Test (A-Z)] as MedicineName from LabTest lt where lt.IsMedicine=Yes";
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(Pur, con);
da.Fill(ds, ds.Tables[0].TableName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(ds);
}
public int GetNextTokenNumber()
{
try
{
string select = "select max(tokenNumber) from patientregistration where TokenMonthYear=" + Convert.ToInt64(DateTime.Today.Month.ToString() + DateTime.Today.Year.ToString());
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand(select, con);
object obj = cmd.ExecuteScalar();
if (DBNull.Value != obj)
{
return(Convert.ToInt32(obj) + 1);
}
else
{
return(1);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int GetNextTokenNumber(Room r)
{
PatientRegistration pr = new PatientRegistration();
try
{
string select = "select min(tokenNumber) from patientregistration where TokenMonthYear="
+ System.DateTime.Today.Month.ToString() + System.DateTime.Today.Year.ToString()
+ " and Room='" + r.Name + "' and Checked=" + false;
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand(select, con);
if (DBNull.Value != (cmd.ExecuteScalar()))
{
int NextTokenNumber = Convert.ToInt32(cmd.ExecuteScalar());
return(NextTokenNumber);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <PatientRegistration> GetPatientLabTestDetail()
{
try
{
string select = "select distinct TokenNumber,TokenDate,TokenMonthYear from LabTestPerformed ltp where TestPaid=1 and TestPerformed = 0";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand();
cmd.CommandText = select;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
dr = cmd.ExecuteReader();
while (dr.Read())
{
PatientRegistration pr = new PatientRegistration();
pr.TokenDate = Convert.ToDateTime(dr["TokenDate"]);
pr.TokenMonthYear = Convert.ToInt64(dr["TokenMonthYear"]);
pr.TokenNumber = Convert.ToInt64(dr["TokenNumber"]);
roomPatients.Add(pr);
}
dr.Close();
return(roomPatients);
}
catch (Exception ex)
{
throw ex;
}
finally { con.Close(); }
}
public long GetNextReceiptNumber()
{
try
{
string select = "select max(RecievedNumber) from ReceiveMedicine";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand(select, con);
object obj = cmd.ExecuteScalar();
if (DBNull.Value != obj)
{
return(Convert.ToInt32(obj) + 1);
}
else
{
return(1);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <UserRight> GetUserRights(User user)
{
List <UserRight> userRights = new List <UserRight>();
try
{
//string select = "Select ScreenName,ScreenCatalog.ScreenID,UserId,RightID,CanAccess from UserRight right outer join ScreenCatalog on UserRight.ScreenId =ScreenCatalog.ScreenId where UserID=" + user.Userno;
//string allSelect = "Select ScreenName,ScreenCatalog.ScreenID,0 as UserId,0 as RightID,false as CanAccess from ScreenCatalog ";
string select = "Select ScreenName,ScreenCatalog.ScreenID,UserId,RightID,CanAccess from UserRight inner join ScreenCatalog on UserRight.ScreenId =ScreenCatalog.ScreenId where UserRight.UserID=" + user.Userno
+ " Union "
+ "Select ScreenName,ScreenCatalog.ScreenID,0 as UserId,0 as RightID,false as CanAccess from ScreenCatalog "
+ " where ScreenID not in ( Select ScreenID from UserRight where UserRight.UserID=" + user.Userno + ") order by ScreenID";
string allSelect = "Select ScreenName,ScreenCatalog.ScreenID,0 as UserId,0 as RightID,false as CanAccess from ScreenCatalog Order By ScreenID";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
if (dt.Rows.Count == 0)
{
da = new OleDbDataAdapter(allSelect, con);
da.Fill(dt);
}
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UserRight userRight = new UserRight();
userRight.Screen.ScreenId = Convert.ToInt32(row["ScreenId"] == DBNull.Value ? 0 : row["ScreenId"]);
userRight.Screen.ScreenName = Convert.ToString(row["ScreenName"] == DBNull.Value ? "" : row["ScreenName"]);
userRight.User.Userno = Convert.ToInt32(row["UserID"] == DBNull.Value ? 0 : row["UserID"]);
userRight.RightId = Convert.ToInt32(row["RightID"] == DBNull.Value ? 0 : row["RightID"]);
userRight.CanAccess = Convert.ToBoolean(row["CanAccess"] == DBNull.Value ? false : row["CanAccess"]);
userRights.Add(userRight);
}
}
return(userRights);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public List <InjectionLabTest> GetInjectionLabTests()
{
inLbs = new List <InjectionLabTest>();
InjectionLabTest inLb;
try
{
string select = "Select * from InjectionLabTest ";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
inLb = new InjectionLabTest();
inLb.TokenNumber = Convert.ToInt64(row["TokenNumber"]);
inLb.Patient.FirstName = Convert.ToString(row["PatientFirstName"] == DBNull.Value ? "" : row["PatientFirstName"]);
inLb.Patient.LastName = Convert.ToString(row["PatientLastName"] == DBNull.Value ? "" : row["PatientLastName"]);
inLb.Patient.NIC = Convert.ToString(row["PatientNIC"] == DBNull.Value ? "" : row["PatientNIC"]);
inLb.Patient.RegistrationNumber = Convert.ToString(row["PatientRegistrationNumber"] == DBNull.Value ? "" : row["PatientRegistrationNumber"]);
inLb.Patient.RegistrationDate = Convert.ToDateTime(row["PatientRegistrationDate"]);
inLb.Patient.Address = Convert.ToString(row["PatientAddress"] == DBNull.Value ? "" : row["PatientAddress"]);
inLb.CashReceived = Convert.ToDouble(row["TokenAmount"] == DBNull.Value ? "" : row["TokenAmount"]);
if (row["PatientPayType"] != DBNull.Value)
{
inLb.Type = (PatientPayType)Enum.Parse(typeof(PatientPayType), row["PatientPayType"].ToString());
}
inLb.TokenDate = Convert.ToDateTime(row["TokenDate"] == DBNull.Value ? DateTime.MinValue : row["TokenDate"]);
inLb.ExistingTokenNo = Convert.ToInt64(row["ExistingTokenID"] == DBNull.Value ? 0 : row["ExistingTokenID"]);
int testid = Convert.ToInt32(row["LabTestId"] == DBNull.Value ? 0 : row["LabTestId"]);
if (testid != 0)
{
inLb.Tests.Add(new LabTest(testid));
}
inLb.IsInjectionToken = Convert.ToBoolean(testid == 0 ? true : false);
inLbs.Add(inLb);
}
}
return(inLbs);
// return labTests;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public string DataPath()
{
this.readconfile = new ReadConfigFile();
string ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
ConnectionString = ConnectionString.Split(';').GetValue(1).ToString().Replace("Data Source=", "");
return(ConnectionString);
}
public List <LabTest> GetLabTests()
{
List <LabTest> labTests = new List <LabTest>();
try
{
string select = "Select * from LabTest";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
LabTest labTest = new LabTest();
labTest.TestName = Convert.ToString(row["Test (A-Z)"] == DBNull.Value ? "" : row["Test (A-Z)"]);
labTest.SampleName = Convert.ToString(row["Sample"] == DBNull.Value ? "" : row["Sample"]);
labTest.Performed = Convert.ToString(row["Performed"] == DBNull.Value ? "" : row["Performed"]);
labTest.LabTestId = Convert.ToInt32(row["ID"]);
labTest.Report = Convert.ToString(row["Report"] == DBNull.Value ? "" : row["Report"]);
labTest.Deserving = Convert.ToDecimal(row["Deserving"] == DBNull.Value ? 0 : row["Deserving"]);
labTest.Poor = Convert.ToDecimal(row["Poor"] == DBNull.Value ? 0 : row["Poor"]);
labTest.YCDO = Convert.ToDecimal(row["YCDO"] == DBNull.Value ? 0 : row["YCDO"]);
labTest.General = Convert.ToDecimal(row["Generall"] == DBNull.Value ? 0 : row["Generall"]);
labTest.Shahab = Convert.ToDecimal(row["Shahab"] == DBNull.Value ? 0 : row["Shahab"]);
labTest.Ghori = Convert.ToDecimal(row["Ghori"] == DBNull.Value ? 0 : row["Ghori"]);
labTest.Unit = row["Unit"].ToString();
labTest.OpeningQty = Convert.ToDecimal(row["OpeningQty"] == DBNull.Value ? 0.0 : row["OpeningQty"]);
labTest.IsAlwaysPaid = Convert.ToBoolean(row["IsAlwaysPaid"]);
labTest.IsMedicine = Convert.ToBoolean(row["IsMedicine"]);
labTest.IsRsTenInjection = Convert.ToBoolean(row["IsRsTenInjection"]);
labTest.Price = row["PurchasePrice"] == DBNull.Value ? 0 : Convert.ToDouble(row["PurchasePrice"]);
labTest.RetailPrice = row["RetailPrice"] == DBNull.Value ? 0 : Convert.ToDouble(row["RetailPrice"]);
labTest.IsOd = Convert.ToBoolean(row["IsOd"]);
labTests.Add(labTest);
}
}
return(labTests);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
int a = 0;
int b = 10;
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
for (int f = 0; f < 5; f++)
{
for (int i = 0; i <= 10000; i++)
{
tran = con.BeginTransaction();
cmd = new OleDbCommand("", con);
switch (f)
{
case 0:
cmd.CommandText = "Update InjectionLabTest set SecondTurn=false where InjectionId=" + i;
break;
case 1:
cmd.CommandText = "Update InjectionLabTest set DosePerDay=" + a + " where DosePerDay=" + i;
break;
case 3:
cmd.CommandText = "Update LabTest set OpeningQty=" + a + " where OpeningQty=" + i;
break;
case 4:
cmd.CommandText = "Update MedicineIssued set DosePerDay=" + a + " where DosePerDay=" + i;
break;
case 2:
cmd.CommandText = "Update ReceiveMedicine set Qty=" + a + " where Qty=" + i;
break;
default:
cmd.CommandText = "Update InjectionLabTest set SecondTurn=false where InjectionId=";
break;
}
//cmd.Connection = con;
cmd.Transaction = tran;
//cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
tran.Commit();
}
}
}
MessageBox.Show("congragulation Cleared!");
}
public List <LabTest> GetAvailabeMedicines()
{
List <LabTest> labTests = new List <LabTest>();
try
{
string select = "Select LabTest.* from LabTest,QryCurrentStock where IsMedicine=1 and LabTest.ID =QryCurrentStock.ItemNumber and NetQty>0";
// string select = " Select LabTest.* from LabTest where IsMedicine=1";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
LabTest labTest = new LabTest();
labTest.TestName = Convert.ToString(row["Test (A-Z)"] == DBNull.Value ? "" : row["Test (A-Z)"]);
labTest.SampleName = Convert.ToString(row["Sample"] == DBNull.Value ? "" : row["Sample"]);
labTest.Performed = Convert.ToString(row["Performed"] == DBNull.Value ? "" : row["Performed"]);
labTest.LabTestId = Convert.ToInt32(row["ID"]);
labTest.Report = Convert.ToString(row["Report"] == DBNull.Value ? "" : row["Report"]);
labTest.IsMedicine = Convert.ToBoolean(row["IsMedicine"]);
labTest.IsRsTenInjection = Convert.ToBoolean(row["IsRsTenInjection"]);
labTest.Deserving = Convert.ToDecimal(row["Deserving"] == DBNull.Value ? 0 : row["Deserving"]);
labTest.Poor = Convert.ToDecimal(row["Poor"] == DBNull.Value ? 0 : row["Poor"]);
labTest.YCDO = Convert.ToDecimal(row["YCDO"] == DBNull.Value ? 0 : row["YCDO"]);
labTest.General = Convert.ToDecimal(row["Generall"] == DBNull.Value ? 0 : row["Generall"]);
labTest.Shahab = Convert.ToDecimal(row["Shahab"] == DBNull.Value ? 0 : row["Shahab"]);
labTest.Ghori = Convert.ToDecimal(row["Ghori"] == DBNull.Value ? 0 : row["Ghori"]);
labTest.IsOd = Convert.ToBoolean(row["IsOd"]);
labTests.Add(labTest);
}
}
return(labTests);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public List <RecieveMedicine> GetRecieveMedicines(string RecieveNumber)
{
List <RecieveMedicine> LoRec = new List <RecieveMedicine>();
try
{
string select = "Select * from ReceiveMedicine where RecievedNumber=" + RecieveNumber;
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
RecieveMedicine ObjRec = new RecieveMedicine();
ObjRec.RefRec.LineItem.LabTestId = Convert.ToInt32(row["ItemNumber"]);
ObjRec.RefRec.LineItem.TestName = row["ItemName"].ToString();
//ObjRec.RefRec.LineItem.MedIssuedID = Convert.ToInt32(row["ID"]);
ObjRec.RefRec.Quantity = Convert.ToDecimal(row["Qty"]);
//ObjRec.RecieveDate = Convert.ToDateTime(row["RecieveDate"]);
//ObjRec.RecieveNumber = Convert.ToInt64(row["RecievedNumber"]);
ObjRec.RefRec.Price = row["Price"] == DBNull.Value ? 0 : Convert.ToDouble(row["Price"]);
ObjRec.RefRec.GrossAmount = row["GrossAmount"] == DBNull.Value ? 0 : Convert.ToDouble(row["GrossAmount"]);
ObjRec.RefRec.NetAmount = row["NetAmount"] == DBNull.Value ? 0 : Convert.ToDouble(row["NetAmount"]);
ObjRec.RefBranch.BranchID = row["BranchID"] == DBNull.Value ? 0 : Convert.ToInt16(row["BranchID"]);
LoRec.Add(ObjRec);
}
}
return(LoRec);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public List <Patient> GetPatientData()
{
Patient pr = new Patient();
try
{
List <Patient> preg = new List <Patient>();
string select = "select * from patientRegistration ";
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand();
cmd.CommandText = select;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
dr = cmd.ExecuteReader();
while (dr.Read())
{
pr = new Patient();
pr.FirstName = dr["PatientFirstName"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientFirstName"]);
pr.LastName = dr["PatientLastName"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientLastName"]);
pr.NIC = dr["PatientNIC"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientNIC"]);
pr.Age = (Int16)(dr["PatientAge"] == System.DBNull.Value ? 0 : Convert.ToInt16(dr["PatientAge"]));
pr.Mobile = dr["PatientMobile"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientMobile"]);
pr.RegistrationDate = dr["PatientRegistrationDate"] == System.DBNull.Value ? new DateTime() : Convert.ToDateTime(dr["PatientRegistrationDate"]);
pr.RegistrationNumber = dr["PatientRegistrationNumber"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientRegistrationNumber"]);
pr.Address = dr["PatientAddress"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientAddress"]);
pr.PatientType = dr["PatientType"] == System.DBNull.Value ? "" : Convert.ToString(dr["PatientType"]);
//pr.CashReceived =dr["TokenAmount"]==System.DBNull.Value?0: Convert.ToDouble(dr["TokenAmount"]);
//pr.TokenType = dr["TokenAmount"]==System.DBNull.Value? 0 : (TokenType)Convert.ToInt32(dr["TokenAmount"]);
//pr.Room.Name = dr["Room"] == System.DBNull.Value ? "" : Convert.ToString(dr["Room"]);
//pr.TokenDate = dr["TokenDate"] == System.DBNull.Value ? new DateTime() : Convert.ToDateTime(dr["TokenDate"]);
//pr.TokenMonthYear = dr["TokenMonthYear"] == System.DBNull.Value ? 0 : Convert.ToInt64(dr["TokenMonthYear"]);
//pr.TokenNumber = dr["TokenNumber"]==System.DBNull.Value?0: Convert.ToInt64(dr["TokenNumber"]);
preg.Add(pr);
}
dr.Close();
return(preg);
}
catch (Exception ex)
{
throw ex;
}
finally { con.Close(); }
}
public List <PatientRegistration> GetAllPatientNotChecked()
{
List <LabTest> labTests = new List <LabTest>();
List <PatientRegistration> prs = new List <PatientRegistration>();
try
{
string select = "SELECT Distinct TokenDate,TokenMonthYear,TokenNumber from PatientRegistration where Checked=No ";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
PatientRegistration pr = new PatientRegistration();
pr.TokenNumber = Convert.ToInt64(row["TokenNumber"] == DBNull.Value ? 0 : row["TokenNumber"]);
pr.TokenMonthYear = Convert.ToInt64(row["TokenMonthYear"] == DBNull.Value ? 0 : row["TokenMonthYear"]);
pr.TokenDate = Convert.ToDateTime(row["TokenDate"] == DBNull.Value ? DateTime.MinValue : row["TokenDate"]);
prs.Add(pr);
}
}
return(prs);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public PatientRegistration GetPatientDetail(long tokenNumber)
{
PatientRegistration pr = new PatientRegistration();
try
{
string select = "select * from patientregistration where TokenMonthYear=" + System.DateTime.Today.Month.ToString()
+ System.DateTime.Today.Year.ToString() + " and TokenNumber=" + tokenNumber;
con = new OleDbConnection();
this.readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
cmd = new OleDbCommand();
cmd.CommandText = select;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
dr = cmd.ExecuteReader();
while (dr.Read())
{
pr.Patient.FirstName = (string)dr["PatientFirstName"];
pr.Patient.LastName = (string)dr["PatientLastName"];
pr.Patient.NIC = (string)dr["PatientNIC"];
pr.Patient.Age = (Int16)dr["PatientAge"];
pr.Patient.Mobile = (string)dr["PatientMobile"];
pr.Patient.RegistrationDate = Convert.ToDateTime(dr["PatientRegistrationDate"]);
pr.Patient.RegistrationNumber = (string)dr["PatientRegistrationNumber"];
pr.Patient.Address = (string)dr["PatientAddress"];
pr.CashReceived = Convert.ToDouble(dr["TokenAmount"]);
pr.TokenType = (TokenType)Convert.ToInt32(dr["TokenAmount"]);
pr.Room.Name = (string)dr["Room"];
pr.TokenDate = Convert.ToDateTime(dr["TokenDate"]);
pr.TokenMonthYear = Convert.ToInt64(dr["TokenMonthYear"]);
pr.TokenNumber = Convert.ToInt64(dr["TokenNumber"]);
}
dr.Close();
return(pr);
}
catch (Exception ex)
{
throw ex;
}
finally { con.Close(); }
}
//public DSTTokenSummryUserWise GetTokenSummaryUserWise(DateTime fromDate, DateTime toDate)
public DSTTokenSummryUserWise GetTokenSummaryUserWise(DateTime fromDate, DateTime toDate, Boolean isTime)
{
DSTTokenSummryUserWise ds = new DSTTokenSummryUserWise();
try
{
string select;
if (isTime == true)
{
select = "select (Select u.UName from unames u where u.UNo=TokenBy) as UserName , COUNT(ID) as TotalTokens,SUM(TokenAmount) as TotalAmount from PatientRegistration where TokenDate>='" + fromDate.ToString("MM/dd/yyyy HH:mm") + "' and TokenDate<='" + toDate.ToString("MM/dd/yyyy HH:mm") + "' GROUP by tokenby";
}
else
{
select = "select (Select u.UName from unames u where u.UNo=TokenBy) as UserName , COUNT(ID) as TotalTokens,SUM(TokenAmount) as TotalAmount from PatientRegistration where Convert(date, TokenDate) >='" + fromDate.ToString("MM/dd/yyyy") + "' and Convert(date, TokenDate) <='" + toDate.ToString("MM/dd/yyyy") + "' GROUP by tokenby";
}
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
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 DSCurrentStockWithValue.dtCurrentStockWithValueDataTable GetCurrentStockWithValue(bool LabTest, bool Medicine, bool All)
{
DSCurrentStockWithValue.dtCurrentStockWithValueDataTable dt = new DSCurrentStockWithValue.dtCurrentStockWithValueDataTable();
try
{
string select = "";
if (All == true)
{
select = "Select * from QryCurrentStockWithValue";
}
else if (Medicine == true)
{
select = "Select * from QryCurrentStockWithValueWithoutLabTest";
}
else if (LabTest == true)
{
select = "Select * from QryCurrentStockWithValueLabTest";
}
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(dt);
}
public DsCurrentStock.CurrentStockDataTable GetCurrentStock(bool LabTestnMedicine, bool WithoutLabTest, bool LabTest)
{
DsCurrentStock.CurrentStockDataTable dt = new DsCurrentStock.CurrentStockDataTable();
try
{ string select = "";
if (LabTestnMedicine == true)
{
select = "Select QryCurrentStockWithIM.*,LabTest.FixLimit from QryCurrentStockWithIM,LabTest where LabTest.ID=QryCurrentStockWithIM.ItemNumber";
}
else if (WithoutLabTest == true)
{
select = "Select QryCurrentStockWithoutLabTest.*,LabTest.FixLimit from QryCurrentStockWithoutLabTest,LabTest where LabTest.ID=QryCurrentStockWithoutLabTest.ItemNumber";
}
else if (LabTest == true)
{
select = "Select QryCurrentStockLabTest.*,LabTest.FixLimit from QryCurrentStockLabTest,LabTest where LabTest.ID=QryCurrentStockLabTest.ItemNumber";
}
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(dt);
}
public LabTest GetSyring()
{
LabTest syring = new LabTest();
try
{
string select = "Select * from Syring where ID=1";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
syring.TestName = Convert.ToString(row["SyringeName"] == DBNull.Value ? "" : row["SyringeName"]);
syring.LabTestId = Convert.ToInt32(row["SyringeCode"] == DBNull.Value ? 0 : row["SyringeCode"]);
}
}
return(syring);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public InjectionLabTest GetInjectionLabTest(InjectionLabTest inLb)
{
//List<LabTest> labTests = new List<LabTest>();
try
{
string select = "Select ilt.* ,(select [Test (A-Z)] from LabTest lt where ilt.Labtestid=lt.id) as TestName from InjectionLabTest ilt where TokenNumber=" + inLb.TokenNumber + " and TokenMonthYear=" + inLb.TokenMonthYear + "";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
double cashRecieved = 0;
foreach (DataRow row in dt.Rows)
{
inLb.Patient.FirstName = Convert.ToString(row["PatientFirstName"] == DBNull.Value ? "" : row["PatientFirstName"]);
inLb.Patient.LastName = Convert.ToString(row["PatientLastName"] == DBNull.Value ? "" : row["PatientLastName"]);
inLb.Patient.NIC = Convert.ToString(row["PatientNIC"] == DBNull.Value ? "" : row["PatientNIC"]);
inLb.Patient.RegistrationNumber = Convert.ToString(row["PatientRegistrationNumber"] == DBNull.Value ? "" : row["PatientRegistrationNumber"]);
inLb.Patient.RegistrationDate = Convert.ToDateTime(row["PatientRegistrationDate"]);
inLb.Patient.Address = Convert.ToString(row["PatientAddress"] == DBNull.Value ? "" : row["PatientAddress"]);
inLb.CashReceived = Convert.ToDouble(row["TokenAmount"] == DBNull.Value ? "" : row["TokenAmount"]);
if (row["PatientPayType"] != DBNull.Value)
{
inLb.Type = (PatientPayType)Enum.Parse(typeof(PatientPayType), row["PatientPayType"].ToString());
}
inLb.TokenDate = Convert.ToDateTime(row["TokenDate"] == DBNull.Value ? DateTime.MinValue : row["TokenDate"]);
inLb.ExistingTokenNo = Convert.ToInt64(row["ExistingTokenID"] == DBNull.Value ? 0 : row["ExistingTokenID"]);
int testid = Convert.ToInt32(row["LabTestId"] == DBNull.Value ? 0 : row["LabTestId"]);
string testname = Convert.ToString(row["TestName"] == DBNull.Value ? "" : row["TestName"]);
if (testid != 0)
{
inLb.Tests.Add(new LabTest(testid, testname));
cashRecieved += Convert.ToDouble(row["TokenAmount"] == DBNull.Value ? "" : row["TokenAmount"]);
inLb.CashReceived = cashRecieved;
}
inLb.IsInjectionToken = Convert.ToBoolean(testid == 0 ? true : false);
//return inLb;
//LabTest labTest = new LabTest();
//labTest.TestName = Convert.ToString(row["Test (A-Z)"] == DBNull.Value ? "" : row["Test (A-Z)"]);
//labTest.SampleName = Convert.ToString(row["Sample"] == DBNull.Value ? "" : row["Sample"]);
//labTest.Performed = Convert.ToString(row["Performed"] == DBNull.Value ? "" : row["Performed"]);
//labTest.LabTestId = Convert.ToInt32(row["ID"]);
//labTest.Report = Convert.ToString(row["Report"] == DBNull.Value ? "" : row["Report"]);
//labTest.Deserving = Convert.ToDecimal(row["Deserving"] == DBNull.Value ? 0 : row["Deserving"]);
//labTest.Poor = Convert.ToDecimal(row["Poor"] == DBNull.Value ? 0 : row["Poor"]);
//labTest.YCDO = Convert.ToDecimal(row["YCDO"] == DBNull.Value ? 0 : row["YCDO"]);
//labTest.General = Convert.ToDecimal(row["General"] == DBNull.Value ? 0 : row["General"]);
//labTest.Shahab = Convert.ToDecimal(row["Shahab"] == DBNull.Value ? 0 : row["Shahab"]);
//labTest.Ghori = Convert.ToDecimal(row["Ghori"] == DBNull.Value ? 0 : row["Ghori"]);
//labTests.Add(labTest);
}
}
return(inLb);
// return labTests;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public PatientRegistration GetPatientRegistration(PatientRegistration pr)
{
//List<LabTest> labTests = new List<LabTest>();
try
{
string select = "Select * from patientregistration where TokenNumber=" + pr.TokenNumber + " and TokenMonthYear=" + pr.TokenMonthYear + "";
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
pr.Patient.FirstName = Convert.ToString(row["PatientFirstName"] == DBNull.Value ?"": row["PatientFirstName"]);
pr.Patient.LastName = Convert.ToString(row["PatientLastName"] == DBNull.Value ?"": row["PatientLastName"]);
pr.Patient.NIC = Convert.ToString(row["PatientNIC"] == DBNull.Value ?"": row["PatientNIC"]);
pr.Patient.RegistrationNumber = Convert.ToString(row["PatientRegistrationNumber"] == DBNull.Value ?"": row["PatientRegistrationNumber"]);
pr.Patient.RegistrationDate = Convert.ToDateTime(row["PatientRegistrationDate"]);
pr.Patient.Address = Convert.ToString(row["PatientAddress"] == DBNull.Value ?"": row["PatientAddress"]);
pr.CashReceived = Convert.ToDouble(row["TokenAmount"] == DBNull.Value ? "" : row["TokenAmount"]);
pr.TokenType = (TokenType)Enum.Parse(typeof(TokenType), Convert.ToString(row["PatientType"] == DBNull.Value ? "" : row["PatientType"]));
pr.Room.Name = Convert.ToString(row["Room"] == DBNull.Value ? "" : row["Room"]);
pr.TokenDate = Convert.ToDateTime(row["TokenDate"] == DBNull.Value ? DateTime.MinValue : row["TokenDate"]);
return(pr);
//LabTest labTest = new LabTest();
//labTest.TestName = Convert.ToString(row["Test (A-Z)"] == DBNull.Value ? "" : row["Test (A-Z)"]);
//labTest.SampleName = Convert.ToString(row["Sample"] == DBNull.Value ? "" : row["Sample"]);
//labTest.Performed = Convert.ToString(row["Performed"] == DBNull.Value ? "" : row["Performed"]);
//labTest.LabTestId = Convert.ToInt32(row["ID"]);
//labTest.Report = Convert.ToString(row["Report"] == DBNull.Value ? "" : row["Report"]);
//labTest.Deserving = Convert.ToDecimal(row["Deserving"] == DBNull.Value ? 0 : row["Deserving"]);
//labTest.Poor = Convert.ToDecimal(row["Poor"] == DBNull.Value ? 0 : row["Poor"]);
//labTest.YCDO = Convert.ToDecimal(row["YCDO"] == DBNull.Value ? 0 : row["YCDO"]);
//labTest.General = Convert.ToDecimal(row["General"] == DBNull.Value ? 0 : row["General"]);
//labTest.Shahab = Convert.ToDecimal(row["Shahab"] == DBNull.Value ? 0 : row["Shahab"]);
//labTest.Ghori = Convert.ToDecimal(row["Ghori"] == DBNull.Value ? 0 : row["Ghori"]);
//labTests.Add(labTest);
}
}
return(pr);
// return labTests;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// it will create Contacts Table in GEN 's database.
/// </summary>
public void UpdateV2()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select * from Contacts";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means Contacts table does not exist then create it.
}
catch (Exception)
{
qry = "Create Table Contacts ";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts Add Column CustomerID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column ContactFirstName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column ContactLastName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
//SecAttDes
qry = "Alter Table Contacts add Column CompanyName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column isDefaultShipTo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column DisplayName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column IsBillTo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column AttachedAddressNo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column CompanyGUID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
/// <summary>
/// this will drop SaleOrderLineItems Table in Gen and ReCreate it.
/// </summary>
public void UpdateV1()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select ExtendedAmount from SaleOrderLineItems";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means ExtendedAmount does not exist then create it.
}
catch (Exception)
{
qry = "Alter Table SaleOrderLineItems Drop HRBLineNo";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems Drop LastCost";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column DiscPct Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column SecAttID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
//SecAttDes
qry = "Alter Table SaleOrderLineItems add Column SecAttDes text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column DiscUnitPrice Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column LineItemPrice Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column Amount Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column ExtendedAmount Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
/// <summary>
/// it will create UserPreferences Table
/// </summary>
public void UpdateV3()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select * from UserPreferences";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means UserPreferences table does not exist then create it.
}
catch (Exception)
{
qry = "Create Table UserPreferences ";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences Add Column InvoiceLookupPeriod text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences Add Column SaleOrderLookupPeriod text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences add Column CompanyGUID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
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 DSIssuedMedPur.dtIssuedMedPurDataTable GetIssuedMedPur(bool All, bool Purchase, bool Retail, LabTest MedID, DateTime From, DateTime To)
{
DSIssuedMedPur.dtIssuedMedPurDataTable dt = new DSIssuedMedPur.dtIssuedMedPurDataTable();
try
{
string Pur = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,'' as BranchName,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID and lt.ID=" + MedID.LabTestId +
" and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" +
" select mi.TokenDate as IsssueDate,mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,'' as BranchName,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True and" +
" lt.ID=" + MedID.LabTestId +
" and mi.TokenDate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all " +
" select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as PurchasePrice,im.BranchName as BranchName,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID and lt.ID=" + MedID.LabTestId +
"and im.Issuedate>=#" + From + "# and im.Issuedate<=#" + To + "#";
//string PurAll = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as Price,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID "+
// " union all" +
// " select mi.TokenDate as IsssueDate,mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as Price,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" +
// " union all " +
// " select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as Price,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID";
string Ret = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as RetailPrice,'' as BranchName,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID and lt.ID=" + MedID.LabTestId +
" and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" +
" select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as RetailPrice,''as BranchName,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True and" +
" lt.ID=" + MedID.LabTestId +
" and mi.TokenDate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all" +
" select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.RetailPrice as RetailPrice,im.Branchname as BranchName,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID and lt.ID=" + MedID.LabTestId +
" and im.Issuedate>=#" + From + "# and im.Issuedate<=#" + To + "#";
// string RetAll = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as Price,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID"+
// " union all" +
// " select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as Price,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" +
// " union all" +
//" select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.RetailPrice as Price,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID";
string all = "select ilt.TokenDate as IssueDate,ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,'' as Branchname,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID " +
" and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" +
" select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,'' as Branchname,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" +
" and mi.Tokendate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all" +
" select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,im.BranchName as Branchname,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID" +
" and im.IssueDate>=#" + From + "# and im.IssueDate<=#" + To + "#";
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
if (con.State == ConnectionState.Open)
{
if (Purchase == true)
{
da = new OleDbDataAdapter(Pur, con);
}
else if (Retail == true)
{
da = new OleDbDataAdapter(Ret, con);
}
//else if(Purchase==true&&All==true)
// da = new OleDbDataAdapter(PurAll, con);
//else if (Retail== true && All == true)
// da = new OleDbDataAdapter(RetAll, con);
// da = new OleDbDataAdapter(all, con);
else if (All == true)
{
da = new OleDbDataAdapter(all, con);
}
da.Fill(dt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(dt);
}
private int medcheck3()
{
List <int> iteminfo = new List <int>();
List <string> medname = new List <string>();
List <int> medqty = new List <int>();
List <int> medcons = new List <int>();
List <int> mednet = new List <int>();
int itemes = 0;
try
{
DataTable dt = new DataTable();
con = new OleDbConnection();
readconfile = new ReadConfigFile();
con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);
con.Open();
foreach (RecieveLineItem item in rm.Lines)
{
string select;
//if (rm.RefRec.LineItem.MedIssuedID == 0)
//{
select = "SELECT ItemNumber,ItemName,sum(ReceivedQuantity) AS TotalRecieved,sum(ConsumedQuantity)*-1 AS TotalConsumed,(sum(ReceivedQuantity) - (sum(ConsumedQuantity)*-1)) AS NetQty FROM (select ItemNumber,ItemName,0 as ReceivedQuantity, sum(Qty)* -1 as ConsumedQuantity from QryOutStockWithIM group by ItemNumber,ItemName union all select ItemNumber,ItemName,sum(Qty) as ReceivedQuantity, 0 as ConsumedQuantity from QryInStock group by ItemNumber,ItemName) AS final GROUP BY ItemNumber, ItemName;";
//cmdSave.ExecuteNonQuery();
da = new OleDbDataAdapter(select, con);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
int qty = Convert.ToInt32(row["TotalRecieved"] == DBNull.Value ? 0 : row["TotalRecieved"]);
int cons = Convert.ToInt32(row["TotalConsumed"] == DBNull.Value ? 0 : row["TotalConsumed"]);
int net = Convert.ToInt32(row["NetQty"] == DBNull.Value ? 0 : row["NetQty"]);
string name = Convert.ToString(row["ItemName"] == DBNull.Value ? "" : row["ItemName"]);
medqty.Add(qty);
medcons.Add(cons);
mednet.Add(net);
medname.Add(name);
}
}
}
for (int i = 0; i < medname.Count; i++)
{
string s = medname[i];
if (s.Equals(cbxLabTest.SelectedItem.ToString()))
{
itemes = i;
break;
}
}
//else
//{
// if (item.LineItem.LabTestId > 0)
// {
// cmd.CommandText = "update IssuedMedicine set IssueDate=#" + rm.RecieveDate + "#" + "," + "IssueNumber=" + rm.RecieveNumber + "," + "MedicineID=" + item.LineItem.LabTestId + "," + "MedicineName='" + item.LineItem.TestName + "'," +
// "Qty=" + item.Quantity + " where ID=" + rm.RefRec.LineItem.MedIssuedID;
// }
//}
//}
//tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{ con.Close(); }
iteminfo.Add(medqty[itemes]);
iteminfo.Add(medcons[itemes]);
iteminfo.Add(mednet[itemes]);
return(mednet[itemes]);
}