public StatusCode DeleteStatusCode(StatusCode entity)
{
string mySQL = " Delete[dbo].[TM_MDC_STATUS_CODES] " + $" WHERE[Status] = @{nameof(entity.Status)}";
int rowsAffected = _db.Execute(mySQL, entity);
return(entity);
}
public CommentType InsertType(CommentType entity)
{
string mySQL = "INSERT INTO [TM_MDC_COMMENT_TYPES] ([TYPE],[TYPE_DESC]) VALUES " +
"('" + entity.CommentTypeCode + "','" + entity.TypeDesc.ToString() + "')";
int rowsAffected = _db.Execute(mySQL);
return(entity);
}
public PriorityCode InsertPriorityCode(PriorityCode entity)
{
string mySQL = " INSERT INTO [TM_MDC_LOG_PRIORITY] ([PRIORITY],[DESCR]) VALUES " +
" ( '" + entity.Priority + "','" + entity.Descr + "')";
int rowsAffected = _db.Execute(mySQL);
return(entity);
}
public bool SubmitApproval(int nPRID)
{
string sql = " Update TH_PurchaseRequisition Set Status=3,SubmittedBy='" + System.Web.HttpContext.Current.User.Identity.Name + "' where id =@nPRID";
db.Execute(sql, new
{
nPRID
});
db.Close();
return(true);
}
public CommentType InsertType(CommentType entity)
{
string mySQL = "INSERT INTO [TM_MDC_COMMENT_TYPES] ([TYPE],[TYPE_DESC]) VALUES " +
"('" + entity.CommentTypeCode + "','" + entity.TypeDesc.ToString() + "')";
int rowsAffected = _db.Execute(mySQL);
if (rowsAffected == 0)
{
throw new Exception($"Record Does Not Exist: {entity.CommentTypeCode}");
}
return(entity);
}
public LogComment InsertComment(LogComment entity)
{
string mySQL = "INSERT INTO [dbo].[TM_DIST_CHG_LOG_COMMENTS] "
+ "([LOG_NUM] "
+ ",[BASIC_NUMBER] "
+ ",[TYPE] "
+ ",[LINK_TO_SEQ_NUM] "
+ ",[CREATED_DATE]"
+ ",[CREATED_BY_SEQ_NUM]"
+ ",[COMMENT_TEXT]"
+ ",[CREATED_BY]"
+ ",[TYPE_DESC]) VALUES ("
+ entity.LogNumber
+ ",'" + entity.BasicNumber + "'"
+ ",'" + entity.CommentTypeCode + "'"
+ "," + entity.LinkToSeqNum
+ ",'" + DateTime.Now + "'"
+ "," + entity.CreatedBySeqNum
+ ",'" + entity.Comment + "'"
+ ",'" + entity.CreatedBy + "'"
+ ",'" + entity.TypeDesc + "')";
int rowsAffected = _db.Execute(mySQL);
return(entity);
}
public void Update(Core.Domain.Building model)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql = " Update tblItem Set CategoryID=@CategoryID,BrandID=@BrandID,ItemCode=@ItemCode,Name=@Name,Unit=@Unit Where id=@ID";
db.Execute(sql, new
{
model.ID
});
db.Close();
}
private static void UpdateSquadEvents(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
string assignEventToSquadsSql = @"INSERT INTO SquadEvents (SquadId, EventId) VALUES (
(SELECT SquadId FROM Squads WHERE Guid = @SquadGuid),
(SELECT EventId FROM Events WHERE Guid = @EventGuid));" ;
foreach (var squad in @event.Squads)
{
var eventSquadParameters = new DynamicParameters();
eventSquadParameters.Add("@EventGuid", @event.Guid.ToString());
eventSquadParameters.Add("@SquadGuid", squad.Guid);
connection.Execute(assignEventToSquadsSql, eventSquadParameters, transaction);
}
}
public string NewPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql;
//First Add Header
int ID = 0;
string NewPrNO = "PR-" + DateTime.Now.ToLongTimeString();
sql = " INSERT INTO TH_PurchaseRequisition(PRNo,PRDate,KitchenID,Remarks)VALUES ('" + NewPrNO + "',@PRDate,1,@Remarks) " +
"Select Cast(SCOPE_IDENTITY() AS int)";
try
{
ID = db.Query <int>(sql, new
{
PurchaseHeader.PRDate,
PurchaseHeader.Remarks
}).SingleOrDefault();
}
catch (Exception e)
{
if (e.Message.IndexOf("") > 0)
{
db.Close();
return("-1");
}
}
foreach (PurchaseRequisitionDetail obj in PurchaseItem)
{
sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty,Unit,Specs) " +
" Values (" + ID + ",@ItemID,@Qty,@Unit,@Specs)";
db.Execute(sql, new
{
obj.ItemId,
obj.Qty,
obj.Unit,
obj.Specs
});
}
return(NewPrNO);
}
private static void UpdateEventTrainingMaterials(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
string insertTrainingMaterialsSql = @"INSERT INTO EventTrainingMaterials (EventId, TrainingMaterialId) VALUES (
(SELECT EventId FROM Events WHERE Guid = @EventGuid),
(SELECT TrainingMaterialId FROM TrainingMaterials WHERE Guid = @TrainingMaterialGuid))" ;
if (@event.TrainingMaterials != null)
{
foreach (var material in @event.TrainingMaterials)
{
var materialParameters = new DynamicParameters();
materialParameters.Add("@EventGuid", @event.Guid.ToString());
materialParameters.Add("@TrainingMaterialGuid", material.Guid.ToString());
connection.Execute(insertTrainingMaterialsSql, materialParameters, transaction);
}
}
}
private static void CreateEvent(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
string insertEventSql = @"INSERT INTO Events(ClubId, Guid, Title, StartDate, EndDate, Location, EventTypeId)
SELECT C.ClubId, @EventGuid, @Title, @StartDate, @EndDate, @Location, @EventTypeId
FROM CLUBS C
WHERE C.Guid = @ClubGuid";
var insertEventParameters = new DynamicParameters();
insertEventParameters.Add("@ClubGuid", @event.ClubId.ToString());
insertEventParameters.Add("@EventGuid", @event.Guid.ToString());
insertEventParameters.Add("@Title", @event.Title);
insertEventParameters.Add("@StartDate", @event.StartDate);
insertEventParameters.Add("@EndDate", @event.EndDate);
insertEventParameters.Add("@Location", @event.Location);
insertEventParameters.Add("@EventTypeId", (byte)@event.EventType);
connection.Execute(insertEventSql, insertEventParameters, transaction);
}
private static void UpdateEvent(Event @event, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction)
{
string updateEventSql = @"
DELETE SquadEvents WHERE EventId = (SELECT EventId FROM Events WHERE Guid = @EventGuid);
DELETE EventTrainingMaterials WHERE EventId = (SELECT EventId FROM Events WHERE Guid = @EventGuid);
UPDATE Events SET Title = @Title, StartDate = @StartDate, EndDate = @EndDate, Location = @Location, EventTypeId = @EventTypeId
WHERE Guid = @EventGuid;" ;
var updateParameters = new DynamicParameters();
updateParameters.Add("@ClubGuid", @event.ClubId.ToString());
updateParameters.Add("@EventGuid", @event.Guid.ToString());
updateParameters.Add("@Title", @event.Title);
updateParameters.Add("@StartDate", @event.StartDate);
updateParameters.Add("@EndDate", @event.EndDate);
updateParameters.Add("@Location", @event.Location);
updateParameters.Add("@EventTypeId", (byte)@event.EventType);
connection.Execute(updateEventSql, updateParameters, transaction);
}
public string EditPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql;
foreach (PurchaseRequisitionDetail obj in PurchaseItem)
{
sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty) " +
" Values (@PRID,@ItemID,@Qty)";
db.Execute(sql, new
{
obj.PRID,
obj.ItemId,
obj.Qty
});
}
return("");
}
