public static List <SlipAndLease> GetAllHistoryLease()
{
//get customerID from session
int inCustomerID = Convert.ToInt32(HttpContext.Current.Session["CustomerID"]);
SqlConnection conn = new SqlConnection();
//DataSet ds = new DataSet();
List <SlipAndLease> lstSlipLease = new List <SlipAndLease>();
SlipAndLease SlipLeaseObj;
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Lease].[ID] as LeaseID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService, [SlipID],[CustomerID],Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Lease] " +
" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [CustomerID] = @CustomerID ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@CustomerID", inCustomerID);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
SlipLeaseObj = new SlipAndLease();
SlipLeaseObj.LeaseID = Convert.ToInt32(dr["ID"]);
SlipLeaseObj.Width = Convert.ToInt32(dr["Width"]);
SlipLeaseObj.Length = Convert.ToInt32(dr["Length"]);
SlipLeaseObj.DockID = Convert.ToInt32(dr["DockID"]);
SlipLeaseObj.DockName = Convert.ToString(dr["DockName"]);
SlipLeaseObj.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
SlipLeaseObj.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
SlipLeaseObj.SlipID = Convert.ToInt32(dr["SlipID"]);
SlipLeaseObj.CustomerID = Convert.ToInt32(dr["CustomerID"]);
SlipLeaseObj.FullName = Convert.ToString(dr["FullName"]);
lstSlipLease.Add(SlipLeaseObj);
}
//SqlDataAdapter sda = new SqlDataAdapter(cmd);
//sda.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(lstSlipLease);
}
public static SlipAndLease GetSlipAndLeaseByID(string SlipID)
{
//write one record into hold table.
WriteSlipAndLeaseByID(SlipID);
SlipAndLease product = null;
string sql = "SELECT [Slip].[ID] as SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService " + //, [Customer].[ID] as CustomerID, Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Slip] " +
//" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
//" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [Slip].[ID] = @SlipID ";
using (SqlConnection con = new SqlConnection(MariaDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
int SlipID1 = Convert.ToInt32(SlipID);
cmd.Parameters.AddWithValue("@SlipID", SlipID1);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
product = new SlipAndLease();
product.SlipID = Convert.ToInt32(dr["SlipID"]);
product.Width = Convert.ToInt32(dr["Width"]);
product.Length = Convert.ToInt32(dr["Length"]);
product.DockID = Convert.ToInt32(dr["DockID"]);
product.DockName = dr["DockName"].ToString();
product.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
product.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
//product.CustomerID = Convert.ToInt32(dr["CustomerID"]);
//product.FullName = Convert.ToString(dr["FullName"]);
}
dr.Close();
}
}
return(product);
}
public static SlipAndLease WriteSlipAndLeaseByID(string SlipID)
{
//read info from slip table
SlipAndLease product = null;
string sql = "SELECT [Slip].[ID] as SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService " + //, [Customer].[ID] as CustomerID, Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Slip] " +
//" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
//" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [Slip].[ID] = @SlipID ";
using (SqlConnection con = new SqlConnection(MariaDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
int SlipID1 = Convert.ToInt32(SlipID);
cmd.Parameters.AddWithValue("@SlipID", SlipID1);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
product = new SlipAndLease();
product.SlipID = Convert.ToInt32(dr["SlipID"]);
product.Width = Convert.ToInt32(dr["Width"]);
product.Length = Convert.ToInt32(dr["Length"]);
product.DockID = Convert.ToInt32(dr["DockID"]);
product.DockName = dr["DockName"].ToString();
product.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
product.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
//product.CustomerID = Convert.ToInt32(dr["CustomerID"]);
//product.FullName = Convert.ToString(dr["FullName"]);
}
dr.Close();
}
}
//get CustomerID and customer FullName from Session
int CustID = Convert.ToInt32(System.Web.HttpContext.Current.Session["CustomerID"]);
string CustFullName = Convert.ToString(System.Web.HttpContext.Current.Session["CustomerFullName"]);
if ((CustID <= 0) || (CustFullName == ""))
{
List <Customer> cust = new List <Customer>();
cust = CustomerDB.GetCustomerIDFullName();
foreach (var cus in cust)
{
CustID = cus.ID;
CustFullName = cus.FirstName + " " + cus.LastName;
}
}
//insert into table
string sql1 = "INSERT INTO [dbo].[SlipAndLease]" +
" ([SlipID],[Width],[Length],[DockID],[DockName],[LeaseID],[CustomerID],[FullName]) " +
" VALUES (@SlipID, @Width, @Length, @DockID, @DockName, @LeaseID, @CustomerID, @FullName) ";
return(product);
}
