public void GET_FETCHED_DATA_NAME(string S)
{
var listParams = new List <SqlParameter>();
listParams.AddSqlParameter("@AMENITY_NAME", AMENITY_NAME);
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(S, listParams);
for (int r = 0; r < dt.Rows.Count; r++)
{
AMENITY_CODE = dt.Rows[r]["AMENITY_CODE"].ToString();
DESCRIPTION = dt.Rows[r]["DESCRIPTION"].ToString();
AMOUNT = dt.Rows[r]["AMOUNT"].ToString();
REPORTING_NAME = dt.Rows[r]["REPORTING_NAME"].ToString();
STATUS = dt.Rows[r]["STATUS"].ToString();
}
}
public DataTable itemwise()
{
var list = new List <SqlParameter>();
//string s = "SELECT DISTINCT A.BILITM_Name AS ITEM_NAME,A.BILLITM_Quanty AS QTY,(SELECT BILL_Amount FROM FCBILLNO WHERE BILL_Id=A.BILL_ID) AS TOTAL_AMOUNT,(SELECT BILL_Tax FROM FCBILLNO WHERE BILL_Id=A.BILL_ID) AS TAX,(SELECT BILL_Total FROM FCBILLNO WHERE BILL_Id=A.BILL_ID) AS GRANDTOTAL,A.BILITM_InsertDate AS DATE FROM FCBILLITM A WHERE A.BILITM_InsertDate BETWEEN '" + date+"' AND '"+date1+ "' ORDER BY A.BILITM_InsertDate ASC ";
string s = "SELECT DISTINCT A.BILITM_Name AS ITEM_NAME,BILITM_Rate AS RATE," +
"(SELECT SUM(BILLITM_Quanty) FROM FCBILLITM WHERE BILITM_Name = A.BILITM_Name AND BILITM_InsertDate BETWEEN '" + date + "' AND '" + date1 + "' AND (A.BILL_Id = (SELECT BILL_Id FROM FCBILLNO B WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled'))) AS QTY," +
"(SELECT SUM(Discount) FROM FCBILLITM WHERE BILITM_Name = A.BILITM_Name AND BILITM_InsertDate BETWEEN '" + date + "' AND '" + date1 + "' AND (A.BILL_Id = (SELECT BILL_Id FROM FCBILLNO B WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled'))) AS Discount," +
// "(SELECT NAM_Rate FROM FCITMNAM WHERE NAM_Name = A.BILITM_Name AND (A.BILL_Id = (SELECT BILL_Id FROM FCBILLNO B WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled'))) AS RATE,"+
"(SELECT BILL_Status FROM FCBILLNO WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled')as BILL_Status," +
"(SELECT sum(BILITM_Tax) FROM FCBILLITM WHERE BILITM_Name = A.BILITM_Name AND BILITM_InsertDate BETWEEN '" + date + "' AND '" + date1 + "' AND (A.BILL_Id = (SELECT BILL_Id FROM FCBILLNO B WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled'))) AS TAXRATE " +
" FROM FCBILLITM A where(A.BILL_Id = (SELECT BILL_Id FROM FCBILLNO B WHERE A.BILL_Id = BILL_Id And BILL_Status = 'Settled') And A.STL_ID = (select STL_ID from FCSTALLS where STL_Name = '" + STL_Name + "')) AND A.BILITM_InsertDate BETWEEN '" + date + "' AND '" + date1 + "' ORDER BY A.BILITM_Name ASC";
DataTable d = DbFunctions.ExecuteCommand <DataTable>(s, list);
return(d);
}
public void User_Name()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@REG_UserName", REG_UserName);
string s = "Select REG_UserName from FCREG WHERE REG_UserName = @REG_UserName";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
a = 0;
}
else
{
a = 1;
}
}
//Retrival of data from database
public void Retrive()
{
var list = new List <SqlParameter>();
string S = "SELECT * FROM REVENUE WHERE REVENUE_CODE = '" + REVENUE_CODE + "'";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(S, list);
NAME = dt.Rows[0]["NAME"].ToString();
AUDIT_GROUP = dt.Rows[0]["AUDIT_GROUP"].ToString();
CLASSIFICATION = dt.Rows[0]["CLASSIFICATION"].ToString();
MISCELLANOUS = dt.Rows[0]["MISCELLANOUS"].ToString();
PRINT_VOUCHER = dt.Rows[0]["PRINT_VOUCHER"].ToString();
TAX_STRUCTURE = dt.Rows[0]["TAX_STRUCTURE"].ToString();
MIS_TAX_STRUCTURE = dt.Rows[0]["MIS_TAX_STRUCTURE"].ToString();
STATUS = dt.Rows[0]["STATUS"].ToString();
}
public DataTable COMPAY()
{
var list = new List <SqlParameter>();
string ss = "select * from RESERVATION WHERE COMPANY_NAME ='" + COMPANY + "' AND RESERVATION=0 AND STATUS <> 'CANCLED'";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(ss, list);
RESERVATIONID = dt.Rows[0]["RESERVATION_ID"].ToString();
FIRSTNAME = dt.Rows[0]["FIRSTNAME"].ToString();
MOBILE_NO = dt.Rows[0]["MOBILE_NO"].ToString();
PAXS = dt.Rows[0]["PAX"].ToString();
COMPANY = dt.Rows[0]["COMPANY_NAME"].ToString();
ARRIVALDATE = Convert.ToDateTime(dt.Rows[0]["ARRIVAL_DATE"].ToString());
DEPARTUREDATE = Convert.ToDateTime(dt.Rows[0]["DEPARTURE_DATE"].ToString());
ROOM_CATEGORY = dt.Rows[0]["ROOM_CATEGORY"].ToString();
NOOFROOMS = dt.Rows[0]["NO_OF_ROOMS"].ToString();
return(dt);
}
public DataTable PRPT()
{
var list = new List <SqlParameter>();
string s = "Select PRPT_Name,PRPT_GST,CONCAT(PRPT_Address, ', ', PRPT_State, ', ', PRPT_Country, '.') AS PRPT_Address from FCPRPTY";
DataTable d = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (d.Rows.Count == 0)
{
}
else
{
Name = d.Rows[0]["PRPT_Name"].ToString();
Address = d.Rows[0]["PRPT_Address"].ToString();
GST = d.Rows[0]["PRPT_GST"].ToString();
}
return(d);
}
public void Getpax()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@room", ROOM_NO);
string s = "SELECT MAX_PAX FROM ROOMMASTER WHERE ROOM_NO = @room";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
PAX = "0.00";
}
else
{
PAX = dt.Rows[0]["MAX_PAX"].ToString();
}
}
public void UPDATE()
{
var list = new List <SqlParameter>();
string S = "SELECT * FROM INGUESTHOUSEINFO WHERE ROOM_NO = '" + ROOM_NO + "' ";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(S, list);
//IN.ROOM_NO = dt.Rows[0][1].ToString();
GUEST_NAME = dt.Rows[0][1].ToString();
COMPANY_NAME = dt.Rows[0][2].ToString();
ARRIVAL_DATE = dt.Rows[0][3].ToString();
ARRIVAL_TIME = dt.Rows[0][4].ToString();
DEPARTURE_DATE = dt.Rows[0][5].ToString();
DEPARTURE_TIME = dt.Rows[0][6].ToString();
CONTACT_NO = dt.Rows[0][7].ToString();
EMAIL = dt.Rows[0][8].ToString();
}
public void INSERT1()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@PCH_Id", PCH_Id);
list.AddSqlParameter("@PCH_Name", PCH_Name);
list.AddSqlParameter("@PCH_Amount", PCH_Amount);
list.AddSqlParameter("@PCH_Details", PCH_Details);
list.AddSqlParameter("@PCH_InsertBy", login.u);
PCH_InsertDate = DateTime.Today.Date;
list.AddSqlParameter("@PCH_InsertDate", PCH_InsertDate);
string s = "INSERT INTO FCPETCSH(PCH_Name,PCH_Amount,PCH_Details,PCH_InsertBy,PCH_InsertDate)" +
"VALUES (@PCH_Name,@PCH_Amount,@PCH_Details,@PCH_InsertBy,@PCH_InsertDate)";
DbFunctions.ExecuteCommand <int>(s, list);
}
public DataTable checkinupdat()
{
var list = new List <SqlParameter>();
string s = "Select CHECKIN_ID FROM CHECKIN WHERE ROOM_NO = '" + MAINROOM + "' AND CHECK_OUT=0 ";
DataTable DT = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (DT.Rows.Count == 0)
{
CHECKINID = 0;
}
else
{
CHECKINID = int.Parse(DT.Rows[0]["CHECKIN_ID"].ToString());
CHECKIN = CHECKINID;
}
return(DT);
}
public DataTable checkoutdetails()
{
var L = new List <SqlParameter>();
string s = "SELECT CHECKIN_ID,ROOM_NO,ROOM_CATEGORY,TAX FROM CHECKIN WHERE ROOM_NO = '" + ROOM_NO + "' ";
DataTable DT = DbFunctions.ExecuteCommand <DataTable>(s, L);
if (DT.Rows.Count == 0)
{
tax = 0;
}
else
{
decimal.TryParse(DT.Rows[0]["TAX"].ToString(), out tax);
}
//checkinid = int.Parse(DT.Rows[0]["CHECKIN_ID"].ToString());
return(DT);
}
public void FETCH_MOD_REFUND_RETENSION()
{
String S = "SELECT * FROM REFUND WHERE ADVANCE_ID=@AD_ID";
var LI = new List <SqlParameter>();
LI.AddSqlParameter("@AD_ID", ADVANCE_ID);
DataTable D = DbFunctions.ExecuteCommand <DataTable>(S, LI);
REFUND_AMOUNT = Convert.ToDecimal(D.Rows[0]["AMOUNT"]);
String STR = "SELECT * FROM RETENSION_CHARGES WHERE ADVANCE_ID=@AD_ID";
var L = new List <SqlParameter>();
L.AddSqlParameter("@AD_ID", ADVANCE_ID);
DataTable DF = DbFunctions.ExecuteCommand <DataTable>(STR, L);
RETENSION_AMOUNT = Convert.ToDecimal(DF.Rows[0]["AMOUNT"]);
}
public void Swapupdate1()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@TRANSFER_ROOM", TRANSFERROOM);
list.AddSqlParameter("@FROM_GUESTNAME", FROM_GUESTNAME);
list.AddSqlParameter("@FROM_ARRIVAL_DATE", FROM_ARRIVALDATE);
list.AddSqlParameter("@FROM_DEPARTURE_DATE", FROM_DEPARTUREDATE);
list.AddSqlParameter("@FROM_GUESTSTATUS", FROM_GUESTSTATUS);
UPDATE_BY = login.u;
UPDTAE_DATE = DateTime.Today;
list.AddSqlParameter("@UPDATE_BY", UPDATE_BY);
list.AddSqlParameter("@UPDATE_DATE", UPDTAE_DATE);
string s = "UPDATE CHECKIN SET FIRSTNAME=@FROM_GUESTNAME, STATUS=@FROM_GUESTSTATUS,ARRIVAL_DATE=@FROM_ARRIVAL_DATE, DEPARTURE_DATE=@FROM_DEPARTURE_DATE,UPDATE_BY=@UPDATE_BY,UPDATE_DATE=@UPDATE_DATE WHERE ROOM_NO='" + TRANSFERROOM + "' AND CHECK_OUT=0";
DbFunctions.ExecuteCommand <int>(s, list);
}
public void roomcategory()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@room", ROOM_NO);
string s = "SELECT ROOM_CATEGORY FROM ROOMMASTER WHERE ROOM_NO = @room";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
ROOM_CATEGORY = "";
}
else
{
ROOM_CATEGORY = dt.Rows[0]["ROOM_CATEGORY"].ToString();
}
}
public DataTable hotel()
{
var list = new List <SqlParameter>();
string s = "SELECT NAME,(SELECT CONCAT(PLOT_NO, ',', LANDMARK, ',', CITY, ',', STATE, ' - ', PINCODE,'.') FROM HotelInfo) AS ADDRESS,GST FROM HotelInfo";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
}
else
{
N = dt.Rows[0]["NAME"].ToString();
AD = dt.Rows[0]["ADDRESS"].ToString();
GS = dt.Rows[0]["GST"].ToString();
}
return(dt);
}
public int GETROOMNO()
{
//connections();
//con = new SqlConnection(connection);
//con.Open();
String S = "SELECT COUNT(ROOM_NO) FROM ROOMMASTER WHERE ROOM_NO=@ROOM_NO";
var listParams = new List <SqlParameter>();
listParams.AddSqlParameter("@ROOM_NO", ROOM_NO);
// SqlCommand CM = new SqlCommand(S, con);
//CM.Parameters.AddWithValue("@ROOMNO", ROOM_NO);
int A = Convert.ToInt16(DbFunctions.ExecuteCommand <Object>(S, listParams));
// int A =Convert.ToInt16( CM.ExecuteScalar());
//con.Close();
return(A);
}
public void Reservation_No()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@res", ROOM_NO);
string s = "SELECT RESERVATION_ID FROM CHECKIN WHERE ROOM_NO = @RES AND CHECK_OUT = 0";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
RESERVATION_NO = "0";
}
else
{
RESERVATION_NO = dt.Rows[0]["RESERVATION_ID"].ToString();
}
}
public void Update()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@REG_Id", REG_Id);
list.AddSqlParameter("@REG_UserName", REG_UserName);
list.AddSqlParameter("@REG_Name", REG_Name);
list.AddSqlParameter("@REG_Password", REG_Password);
list.AddSqlParameter("@REG_Phone", REG_Phone);
list.AddSqlParameter("@REG_RePassword", REG_RePassword);
list.AddSqlParameter("@DESIGNATION", DESIGNATION);
list.AddSqlParameter("@REG_UpdateDate", REG_UpdateDate);
list.AddSqlParameter("@REG_UpdateBy", login.u);
string s = "UPDATE FCREG SET REG_Name=@REG_Name,REG_Phone=@REG_Phone,REG_Password=@REG_Password,REG_RePassword=@REG_RePassword,DESIGNATION=@DESIGNATION WHERE REG_UserName = '******'";
DbFunctions.ExecuteCommand <int>(s, list);
}
public int id()
{
int a = 0;
var L = new List <SqlParameter>();
string S = "Select MAX(OFF_ID) from FCOFFERS";
object c = DbFunctions.ExecuteCommand <object>(S, L);
if (c == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(c);
a = a + 1;
}
return(a);
}
public DataTable GetActiveDate()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@CTG_Name", CTG_Name);
string s = "SELECT CTG_ActiveDate FROM FCRITMCTG WHERE CTG_Name = @CTG_Name";
DataTable active_date = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (active_date.Rows.Count == 0)
{
CTG_ActiveDate = "";
}
else
{
CTG_ActiveDate = active_date.Rows[0]["CTG_ActiveDate"].ToString();
}
return(active_date);
}
public DataTable setdata()
{
DataTable D = null;
if (USER_NAME != "")
{
var listParams = new List <SqlParameter>();
listParams.AddSqlParameter("@USER_NAME", USER_NAME);
string S = "SELECT TOP 5 USER_NAME FROM REGISTRATION WHERE USER_NAME LIKE '" + USER_NAME + "%' ";
using (D = DbFunctions.ExecuteCommand <DataTable>(S, listParams))
{
d = USER_NAME;
}
}
USER_NAME = d;
return(D);
}
public DataTable get()
{
var list = new List <SqlParameter>();
// string s = "select PLOT_NO,LANDMARK,CITY,STATE,COUNTRY,PINCODE,MOBILE_NO,EMAIL FROM COMPANYMASTER WHERE COMPANY_NAME='" + COMPANY_NAME + "'";
string s = "SELECT PLOT_NO+','+LANDMARK+','+CITY+','+STATE+','+COUNTRY+','+CONVERT(VARCHAR(10),PINCODE) as address FROM COMPANYMASTER WHERE COMPANY_NAME='" + COMPANY_NAME + "' ";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
address = "";
}
else
{
address = dt.Rows[0]["address"].ToString();
}
return(dt);
}
public DataTable get()
{
var list = new List <SqlParameter>();
string ss = "select BILL_NO,CONVERT(decimal(17,2),BALANCE_AMOUNT) AS BALANCE_AMOUNT from SETTLE WHERE COMPANY_NAME ='" + COMPANY_NAME + "'";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(ss, list);
if (dt.Rows.Count == 0)
{
BILL_NO = "";
PENDING_AMOUNT = "";
}
else
{
BILL_NO = dt.Rows[0]["BILL_NO"].ToString();
PENDING_AMOUNT = dt.Rows[0]["BALANCE_AMOUNT"].ToString();
}
return(dt);
}
public void GetTax()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("@room", ROOM_NO);
list.AddSqlParameter("@amount", RACK_TARIFF);
string s = "SELECT FACTOR FROM TAX_CODE WHERE FROM_AMOUNT < @amount AND TO_AMOUNT > @amount";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(s, list);
if (dt.Rows.Count == 0)
{
TAX = "0.00";
}
else
{
TAX = dt.Rows[0]["FACTOR"].ToString();
}
}
public int GET_MAXBILLNO()
{
int a = 0;
string S = "SELECT MAX(BILL_NO) FROM PRINTSTATUS";
var L = new List <SqlParameter>();
object c = DbFunctions.ExecuteCommand <object>(S, L);
if (c == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(c);
a = a + 1;
}
return(a);
}
public int getid1()
{
int a = 0;
string S = "SELECT MAX(VOCHERNUMBER) FROM PAIDOUT_OPENINGBALANCE";
var L = new List <SqlParameter>();
object c = DbFunctions.ExecuteCommand <object>(S, L);
if (c == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(c);
a = a + 1;
}
return(a);
}
public int id()
{
int a = 0;
var L = new List <SqlParameter>();
string S = "SELECT MAX(CTG_Id) FROM FCRITMCTG";
object c = DbFunctions.ExecuteCommand <object>(S, L);
if (c == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(c);
a = a + 1;
}
return(a);
}
public int night_auditcount()
{
var list = new List <SqlParameter>();
list.AddSqlParameter("ROOM_NO", ROOM_NO);
string s = "select count(ROOM_TARRIF) AS Total from night_audit where ROOM_NO = @ROOM_NO AND NIGHT=0 ";
object c = DbFunctions.ExecuteCommand <object>(s, list);
if (c == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(c);
}
return(a);
}
//Method To Get Room No To Restrict Discounts
public DataTable Room_Discount()
{
var list = new List <SqlParameter>();
string query = "SELECT ROOM_NO FROM DISCOUNT WHERE ROOM_NO = '" + ROOM_NO + "' AND CHECKIN_ID='" + CHECKIN_ID + "' ";
DataTable dt = DbFunctions.ExecuteCommand <DataTable>(query, list);
if (dt.Rows.Count <= 0)
{
result = "0";
}
else
{
result = "2";
DISCOUNT_ROOM = dt.Rows[0]["ROOM_NO"].ToString();
}
return(dt);
}
public int Group_Checkinid()
{
var list = new List <SqlParameter>();
string s = "SELECT MAX(GROUP_CHECKINID) FROM CHECKIN";
object gci = DbFunctions.ExecuteCommand <object>(s, list);
if (gci == System.DBNull.Value)
{
a = 1;
}
else
{
a = Convert.ToInt32(gci);
a = a + 1;
}
return(a);
}
