public class BasicPartsBL
{
SqlConnection sqlCon = new SqlConnection(DBConnection.ConnectionString);
SqlCommand sqlCom = null;
BasicParts _oBP = new BasicParts();
List<BasicParts> _oBPs = new List<BasicParts>();
public BasicParts Save(BasicParts oBasicParts, long userId)
{
_oBP = new BasicParts();
try
{
sqlCon.Open();
sqlCom = new SqlCommand("SP_BasicParts", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, oBasicParts, oBasicParts.Id > 0 ? EnumOperationTypes.Update : EnumOperationTypes.Save, userId);
sqlCon.Close();
sqlCon.Open();
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) _oBP = this.Mapping(reader);
}
catch (Exception ex)
{
_oBP = new BasicParts() { Message = ex.Message.Split('~')[0] };
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
return _oBP;
}
public string Delete(BasicParts oBasicParts, long userId)
{
string message = "";
try
{
sqlCon.Open();
sqlCom = new SqlCommand("SP_BasicParts", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom = this.SetParameters(sqlCom, oBasicParts, EnumOperationTypes.Delete, userId);
sqlCom.ExecuteNonQuery();
sqlCon.Close();
message = IKR.DeleteMessage;
}
catch (Exception ex)
{
message = ex.Message.Split('~')[0];
}
finally
{
sqlCom.Dispose();
}
return message;
}
public List<BasicParts> Gets()
{
_oBPs = new List<BasicParts>();
_oBPs = Data<BasicParts>.DataSource("SELECT * FROM BasicParts ORDER BY Name");
if (_oBPs == null) return new List<BasicParts>();
return _oBPs;
}
public List<BasicParts> Gets(string sSQL)
{
return this.ReturnList(sSQL);
}
//public List<BasicParts> Gets(string cuttingPartIds)
//{
// return this.ReturnList("SELECT * FROM BasicParts WHERE Id NOT IN (" + cuttingPartIds + ")");
//}
private BasicParts ReturnObject(string sSQL)
{
var oBasicParts = new BasicParts();
try
{
sqlCon.Open();
sqlCom = new SqlCommand(sSQL, sqlCon);
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) oBasicParts = this.Mapping(reader);
}
catch (Exception ex)
{
oBasicParts = new BasicParts() { Message = ex.Message.Split('~')[0] };
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
return oBasicParts;
}
private List<BasicParts> ReturnList(string sSQL)
{
var oBasicPartss = new List<BasicParts>();
try
{
sqlCon.Open();
sqlCom = new SqlCommand(sSQL, sqlCon);
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read()) oBasicPartss.Add(this.Mapping(reader));
}
catch (Exception ex)
{
oBasicPartss = new List<BasicParts>();
oBasicPartss.Add(new BasicParts() { Message = ex.Message.Split('~')[0] });
}
finally
{
sqlCom.Dispose();
sqlCon.Close();
}
return oBasicPartss;
}
public SqlCommand SetParameters(SqlCommand sqlCom, BasicParts oBasicPart, EnumOperationTypes operationType,long userId)
{
sqlCom.Parameters.AddWithValue("@Id", oBasicPart.Id);
sqlCom.Parameters.AddWithValue("@Name", oBasicPart.Name);
sqlCom.Parameters.AddWithValue("@ShortName", oBasicPart.ShortName);
sqlCom.Parameters.AddWithValue("@OperationType", operationType);
return sqlCom;
}
private BasicParts Mapping(SqlDataReader reader)
{
return new BasicParts()
{
Id = (int)reader["Id"],
Name = reader["Name"].ToString(),
ShortName = reader["ShortName"].ToString()
};
}
public GridEntity<BasicParts> GetBasicParts(GridOptions options)
{
return KendoGrid<BasicParts>.GetGridData_5_Constr("SqlConnectionString", options, "sp_select_basicparts_grid", "get_basic_parts", "Name");
}
}
USE [ProductionManagementLive]
GO
/****** Object: StoredProcedure [dbo].[SP_BasicParts] Script Date: 29-Dec-21 12:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_BasicParts]
@Id int
,@Name nvarchar(50) = ''
,@ShortName nvarchar(50) = ''
,@OperationType int
AS
BEGIN TRAN
IF(@OperationType = 1) --Insert
BEGIN
IF(@Name = '' OR @Name = null)
BEGIN
ROLLBACK
RAISERROR (N'Give parts name !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM BasicParts WHERE [Name]=@Name)
BEGIN
ROLLBACK
RAISERROR (N'This Basic Part already in list, try another !!!~',16,1);
RETURN
END
IF(TRIM(@ShortName) != '')
BEGIN
IF EXISTS(SELECT * FROM BasicParts WHERE ShortName=@ShortName)
BEGIN
ROLLBACK
RAISERROR (N'This short name already in list, try another !!!~',16,1);
RETURN
END
END
SET @Id = (SELECT ISNULL(MAX(Id),0) FROM BasicParts) + 1
INSERT INTO BasicParts (Id, [Name], ShortName)
VALUES(@Id, @Name, @ShortName)
SELECT * FROM BasicParts WHERE Id=@Id
END
ELSE IF(@OperationType = 2) --Update
BEGIN
IF (@Id = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid BasicParts !!!~',16,1);
RETURN
END
IF(@Name = '' OR @Name = null)
BEGIN
ROLLBACK
RAISERROR (N'Give parts name !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM BasicParts WHERE [Name]=@Name AND Id!=@Id)
BEGIN
ROLLBACK
RAISERROR (N'This Basic Part already in list, try another !!!~',16,1);
RETURN
END
IF(TRIM(@ShortName) != '')
BEGIN
IF EXISTS(SELECT * FROM BasicParts WHERE ShortName=@ShortName AND Id!=@Id)
BEGIN
ROLLBACK
RAISERROR (N'This short name already in list, try another !!!~',16,1);
RETURN
END
END
UPDATE BasicParts SET [Name]=@Name
,ShortName=@ShortName
WHERE Id=@Id
SELECT * FROM BasicParts WHERE Id=@Id
END
ELSE IF(@OperationType = 3) --Delete
BEGIN
IF (@Id = 0)
BEGIN
ROLLBACK
RAISERROR (N'Invalid BasicParts !!!~',16,1);
RETURN
END
IF EXISTS(SELECT * FROM CuttingParts WHERE PartsId = @Id)
BEGIN
ROLLBACK
RAISERROR (N'This basic part already in use !!!~',16,1);
RETURN
END
DELETE FROM BasicParts WHERE Id=@Id
END
COMMIT TRAN
public class BasicPartsController : Controller
{
BasicParts _oBP = new BasicParts();
List<BasicParts> _oBPs = new List<BasicParts>();
BasicPartsBL _oBPBL = new BasicPartsBL();
public ActionResult BasicParts()
{
if (Session["CurrentUser"] == null) return RedirectToAction("Logoff", "Home");
return View();
}
public JsonResult GetBasicParts(GridOptions options)
{
var res = _oBPBL.GetBasicParts(options);
return Json(res, JsonRequestBehavior.AllowGet);
}
public JsonResult GetAllBasicParts()
{
var basicParts = _oBPBL.Gets();
return Json(basicParts, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public JsonResult Save(BasicParts oBasicPart)
{
_oBP = new BasicParts();
try
{
var user = (User)Session["CurrentUser"];
if (user == null) _oBP.Message = IKR.MessageSessionOut;
else _oBP = _oBPBL.Save(oBasicPart, user.USERID);
}
catch (Exception ex)
{
_oBP = new BasicParts() { Message = ex.Message };
}
return Json(_oBP, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public JsonResult Delete(BasicParts oBasicPart)
{
string message = "";
try
{
var user = (User)Session["CurrentUser"];
if (user == null) message = IKR.MessageSessionOut;
else message = _oBPBL.Delete(oBasicPart, user.USERID);
}
catch (Exception ex)
{
message = ex.Message;
}
return Json(message, JsonRequestBehavior.AllowGet);
}
}
$.ajax({
type: "POST",
dataType: "json",
url: "/HourlyProduction/Save",
traditional: true,
contentType: "application/json;charset=utf-8",
data: JSON.stringify(_hourlyProduction),
success: function (data) {
_hourlyProduction = jQuery.parseJSON(data);
if(_hourlyProduction.Message.trim() != ""){
alert(_hourlyProduction.Message);
return false;
}
$("#dtHourlyProduction").data("kendoDatePicker").enable(false);
alert("Successfully saved.");
window.location.href=_defaultHref;
},
error: function (xhr, status, error) {
alert(error);
_hourlyProduction.HourlyProductions = [];
SetPrimaryKeys(_hourlyProduction);
}
});
0 Comments