public DataSet SaveBuilty(Builty builty)
{
int BuiltyId = 0;
DataSet ds = new FinalBuiltyDataSet();
string connection = ConfigurationManager.ConnectionStrings["MalikDb"].ConnectionString;
if (builty.BuiltyTruckNumber != null)
{
builty.BuiltyTruckNumber = builty.BuiltyTruckNumber.ToUpper();
}
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd0 = new SqlCommand("sp_Vehicle_VehicleExist", con);
cmd0.CommandType = CommandType.StoredProcedure;
cmd0.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyTruckNumber);
SqlDataReader sdr = cmd0.ExecuteReader();
if (sdr.HasRows)
{
if (sdr.Read())
{
var VehicleInDB = new Vehicle();
VehicleInDB.VehicleDriver = sdr["VehicleDriver"].ToString();
VehicleInDB.VehicleDriverCNIC = sdr["VehicleDriverCNIC"].ToString();
VehicleInDB.VehiclePhoneNumber = sdr["VehiclePhoneNumber"].ToString();
VehicleInDB.VehicleSize = sdr["VehicleSize"].ToString();
if (VehicleInDB.VehicleDriver != builty.BuiltyDriver || VehicleInDB.VehicleDriverCNIC != builty.BuiltyDriverId || VehicleInDB.VehiclePhoneNumber != builty.BuiltyDriverNumber || VehicleInDB.VehicleSize != builty.BuiltyVehicleSize)
{
SqlCommand cmd1 = new SqlCommand("sp_Vehicle_VehicleUpdate", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyTruckNumber);
cmd1.Parameters.AddWithValue("@DriverName", builty.BuiltyDriver);
cmd1.Parameters.AddWithValue("@DriverPhoneNumber", builty.BuiltyDriverNumber);
cmd1.Parameters.AddWithValue("@VehicleDriverCNIC", builty.BuiltyDriverId);
cmd1.Parameters.AddWithValue("@VehicleSize", builty.BuiltyVehicleSize);
cmd1.ExecuteNonQuery();
}
}
}
else if (builty.BuiltyTruckNumber != null)
{
SqlCommand cmd1 = new SqlCommand("sp_Vehicle_VehicleAdd", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyTruckNumber);
cmd1.Parameters.AddWithValue("@DriverName", builty.BuiltyDriver);
cmd1.Parameters.AddWithValue("@DriverPhoneNumber", builty.BuiltyDriverNumber);
cmd1.Parameters.AddWithValue("@VehicleDriverCNIC", builty.BuiltyDriverId);
cmd1.Parameters.AddWithValue("@VehicleSize", builty.BuiltyVehicleSize);
cmd1.ExecuteNonQuery();
}
builty.BuiltyRemainingPayment = builty.BuiltyFare + builty.BuiltyAdvance;
builty.BuiltyFinalFare = builty.BuiltyRemainingPayment;
SqlCommand cmd = new SqlCommand("sp_NewBuilty", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BuiltyVehicleNumber", builty.BuiltyTruckNumber);
cmd.Parameters.AddWithValue("@BuiltyDate", builty.BuiltyDate);
cmd.Parameters.AddWithValue("@BuiltyDriverName", builty.BuiltyDriver);
cmd.Parameters.AddWithValue("@BuiltySource", builty.BuiltySource);
cmd.Parameters.AddWithValue("@BuiltyDestination", builty.BuiltyDestination);
cmd.Parameters.AddWithValue("@BuiltySellerName", builty.BuiltySeller);
cmd.Parameters.AddWithValue("@BuiltyBuyerName", builty.BuiltyBuyer);
cmd.Parameters.AddWithValue("@Quantity", builty.BuiltyQuantity);
cmd.Parameters.AddWithValue("@BuiltyDetail", builty.BuiltyGoodsDetail);
cmd.Parameters.AddWithValue("@BuiltyWeight", builty.BuiltyWeight);
cmd.Parameters.AddWithValue("@BuiltyFare", builty.BuiltyFare);
cmd.Parameters.AddWithValue("@BuiltyAdvance", builty.BuiltyAdvance);
cmd.Parameters.AddWithValue("@BuiltyRemainingPayment", builty.BuiltyRemainingPayment);
cmd.Parameters.AddWithValue("@BuiltyDriverNumber", builty.BuiltyDriverNumber);
cmd.Parameters.AddWithValue("@BuiltyDriverId", builty.BuiltyDriverId);
cmd.Parameters.AddWithValue("@BuiltyRate", builty.BuiltyRate);
cmd.Parameters.AddWithValue("@BuiltyFinalFare", builty.BuiltyFinalFare);
cmd.Parameters.AddWithValue("@BuiltyType", builty.BuiltyType);
cmd.Parameters.AddWithValue("@BuiltyVehicleSize", builty.BuiltyVehicleSize);
SqlDataAdapter SDA = new SqlDataAdapter(cmd);
DataTable DT = new DataTable();
SDA.Fill(DT);
if (DT.Rows.Count > 0)
{
foreach (DataRow Row in DT.Rows)
{
BuiltyId = int.Parse(Row["BuiltyNumber"].ToString());
}
}
con.Close();
cmd.Dispose();
using (SqlCommand cm = new SqlCommand("sp_Builty_Report", con))
{
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@BuiltyNumber", BuiltyId);
SqlDataAdapter sda = new SqlDataAdapter(cm);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
string firstname = r["BuiltySource"].ToString();
string lastname = r["BuiltyDriver"].ToString();
}
}
sda.Fill(ds, "tbl_Builty");
}
return(ds);
}
public DataSet BuiltyUpdate(Builty builty)
{
if (builty.BuiltyTruckNumber != null)
{
builty.BuiltyTruckNumber = builty.BuiltyTruckNumber.ToUpper();
}
string connection = ConfigurationManager.ConnectionStrings["MalikDb"].ConnectionString;
SqlConnection con = new SqlConnection(connection);
DataSet ds = new FinalBuiltyDataSet();
con.Open();
SqlCommand cmd = new SqlCommand("sp_Builty_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BuiltyNumber", builty.BuiltyNumber);
cmd.Parameters.AddWithValue("@BuiltyVehicleNumber", builty.BuiltyTruckNumber);
cmd.Parameters.AddWithValue("@BuiltyDate", builty.BuiltyDate);
cmd.Parameters.AddWithValue("@BuiltyDriverName", builty.BuiltyDriver);
cmd.Parameters.AddWithValue("@BuiltySource", builty.BuiltySource);
cmd.Parameters.AddWithValue("@BuiltyDestination", builty.BuiltyDestination);
cmd.Parameters.AddWithValue("@BuiltySellerName", builty.BuiltySeller);
cmd.Parameters.AddWithValue("@BuiltyBuyerName", builty.BuiltyBuyer);
cmd.Parameters.AddWithValue("@Quantity", builty.BuiltyQuantity);
cmd.Parameters.AddWithValue("@BuiltyDetail", builty.BuiltyGoodsDetail);
cmd.Parameters.AddWithValue("@BuiltyWeight", builty.BuiltyWeight);
cmd.Parameters.AddWithValue("@BuiltyFare", builty.BuiltyFare);
cmd.Parameters.AddWithValue("@BuiltyAdvance", builty.BuiltyAdvance);
cmd.Parameters.AddWithValue("@BuiltyRemainingPayment", builty.BuiltyRemainingPayment);
cmd.Parameters.AddWithValue("@BuiltyDriverNumber", builty.BuiltyDriverNumber);
cmd.Parameters.AddWithValue("@BuiltyDriverId", builty.BuiltyDriverId);
cmd.Parameters.AddWithValue("@BuiltyRate", builty.BuiltyRate);
cmd.Parameters.AddWithValue("@BuiltyFinalFare", builty.BuiltyFinalFare);
cmd.Parameters.AddWithValue("@BuiltyType", builty.BuiltyType);
cmd.Parameters.AddWithValue("@BuiltyVehicleSize", builty.BuiltyVehicleSize);
string message = "";
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
message = "Ride Updated Successfully";
}
con.Close();
using (SqlCommand cm = new SqlCommand("sp_Builty_Report", con))
{
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@BuiltyNumber", builty.BuiltyNumber);
SqlDataAdapter sda = new SqlDataAdapter(cm);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
string firstname = r["BuiltyDriverNumber"].ToString();
}
}
sda.Fill(ds, "tbl_Builty");
}
return(ds);
}
public DataSet Builty(Builty builty)
{
int BuiltyId = 0;
DataSet ds = new FinalBuiltyDataSet();
string connection = ConfigurationManager.ConnectionStrings["temp_transport"].ConnectionString;
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd0 = new SqlCommand("sp_Vehicle_VehicleExist", con);
cmd0.CommandType = CommandType.StoredProcedure;
cmd0.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyVehicleNumber);
SqlDataReader sdr = cmd0.ExecuteReader();
if (sdr.HasRows)
{
if (sdr.Read())
{
if (sdr["DriverName"] != builty.DriverName || sdr["DriverPhoneNumber"] != builty.DriverPhoneNumber || sdr["VehicleSize"] != builty.VehicleSize)
{
SqlCommand cmd1 = new SqlCommand("sp_Vehicle_VehicleUpdate", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyVehicleNumber);
cmd1.Parameters.AddWithValue("@DriverName", builty.DriverName);
cmd1.Parameters.AddWithValue("@DriverPhoneNumber", builty.DriverPhoneNumber);
cmd1.Parameters.AddWithValue("@VehicleSize", builty.VehicleSize);
cmd1.ExecuteNonQuery();
}
}
}
else
{
SqlCommand cmd1 = new SqlCommand("sp_Vehicle_VehicleAdd", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@VehicleNumber", builty.BuiltyVehicleNumber);
cmd1.Parameters.AddWithValue("@DriverName", builty.DriverName);
cmd1.Parameters.AddWithValue("@DriverPhoneNumber", builty.DriverPhoneNumber);
cmd1.Parameters.AddWithValue("@VehicleSize", builty.VehicleSize);
cmd1.ExecuteNonQuery();
}
SqlCommand cmd = new SqlCommand("sp_NewBuilty", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BuiltyVehicleNumber", builty.BuiltyVehicleNumber.ToString());
cmd.Parameters.AddWithValue("@BuiltySellerName", builty.BuiltySeller.ToString());
cmd.Parameters.AddWithValue("@BuiltyBuyerName", builty.BuiltyBuyer.ToString());
cmd.Parameters.AddWithValue("@BuiltySource", builty.BuiltySource.ToString());
cmd.Parameters.AddWithValue("@BuiltyDestination", builty.BuiltyDestination.ToString());
cmd.Parameters.AddWithValue("@BuiltyDate", builty.BuiltyDate.ToString());
cmd.Parameters.AddWithValue("@DriverNumber", builty.DriverPhoneNumber.ToString());
cmd.Parameters.AddWithValue("@BuiltyDriverName", builty.DriverName.ToString());
cmd.Parameters.AddWithValue("@BuiltyVehicleSize", builty.VehicleSize.ToString());
cmd.Parameters.AddWithValue("@Quantity", builty.BuiltyGoodsQuantaty.ToString());
cmd.Parameters.AddWithValue("@BuiltyDetail", builty.BuiltyDetail.ToString());
cmd.Parameters.AddWithValue("@BuiltyWeight", builty.BuiltyWeight.ToString());
SqlDataAdapter SDA = new SqlDataAdapter(cmd);
DataTable DT = new DataTable();
SDA.Fill(DT);
if (DT.Rows.Count > 0)
{
foreach (DataRow Row in DT.Rows)
{
BuiltyId = int.Parse(Row["BuiltyId"].ToString());
}
}
con.Close();
cmd.Dispose();
using (SqlCommand cm = new SqlCommand("sp_Builty_Report", con))
{
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@BuiltyId", BuiltyId);
SqlDataAdapter sda = new SqlDataAdapter(cm);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
string firstname = r["BuiltySource"].ToString();
string lastname = r["DriverName"].ToString();
}
}
sda.Fill(ds, "tbl_Builty");
}
return(ds);
}