public async Task <IHttpActionResult> AddCP([FromBody] CpLt value)
{
using (TransactionScope scope = new TransactionScope())
using (var connection = new SqlConnection(sqlConnectionString))
{
try
{
connection.Open();
var p = new CPLTMaster
{
LD = value.cpLTMaster.LD,
ReferenceNumber = value.cpLTMaster.ReferenceNumber,
SupplierId = value.cpLTMaster.SupplierId,
TenderDate = value.cpLTMaster.TenderDate,
Type = value.cpLTMaster.Type
};
p.Id = connection.Query <int>(@"insert CPLTMaster(LD,ReferenceNumber,SupplierId,TenderDate,Type) values (@LD,@ReferenceNumber,@SupplierId,@TenderDate,@Type) select cast(scope_identity() as int)", p).First();
foreach (var item in value.cpLTDetails)
{
var data = new CPLTDetails
{
DeliveryDate = item.DeliveryDate,
OemId = item.OemId,
ProdId = item.ProdId,
Quantity = item.Quantity,
Rate = item.Rate,
Value = item.Value,
CPLTId = p.Id,
Status = true,
AvailableQuantity = item.Quantity,
Addedon = DateTime.Now,
};
data.Id = connection.Query <int>(@"insert CPLTDetails(DeliveryDate,OemId,ProdId,Quantity,Rate,Value,CPLTId,Status,AvailableQuantity,Addedon) values (@DeliveryDate,@OemId,@ProdId,@Quantity,@Rate,@Value,@CPLTId,@Status,@AvailableQuantity,@Addedon) select cast(scope_identity() as int)", data).First();
}
scope.Complete();
return(Json(new { Message = "Record Inserted Successfully" }));
}
catch (Exception ex)
{
// Not needed any rollback, if you don't call Complete
// a rollback is automatic exiting from the using block
connection.BeginTransaction().Rollback();
return(Json(new { Message = "Error" }));
}
}
}
public async Task <IHttpActionResult> EditCP([FromBody] CpLt value)
{
using (TransactionScope scope = new TransactionScope())
using (var connection = new SqlConnection(sqlConnectionString))
{
try
{
var Id = Convert.ToInt32(value.cpLTMaster.Id);
connection.Open();
var CpLTExist = connection.Query <int>("Select * from CPLTMaster where ID = @Id", new { Id = Id }).FirstOrDefault();
if (CpLTExist == null)
{
throw new ProcessException("Selected CPLT not exists");
}
else
{
var Type = value.cpLTMaster.Type;
var ReferenceNumber = value.cpLTMaster.ReferenceNumber;
var SupplierId = value.cpLTMaster.SupplierId;
var TenderDate = value.cpLTMaster.TenderDate;
var LD = value.cpLTMaster.LD;
//};
// string updateQuery = @"UPDATE IdtIcTMaster SET IdtIctType=@IdtIctType,ReferenceNumber=@ReferenceNumber,DateOfEntry=@DateOfEntry,Status=@Status where Id = @Id";
string updateQuery = @"UPDATE CPLTMaster SET Type = @Type,ReferenceNumber=@ReferenceNumber,SupplierId=@SupplierId,TenderDate=@TenderDate,LD=@LD WHERE Id = @Id";
var result = connection.Execute(updateQuery, new
{
Type,
ReferenceNumber,
SupplierId,
TenderDate,
LD,
Id
});
var listOfIds = new List <int>();
for (int i = 0; i < value.cpLTDetails.Count; i++)
{
if (value.cpLTDetails[i].CPLTId != 0)
{
listOfIds.Add(value.cpLTDetails[i].CPLTId);
//var currentRecord = connection.Query<int>("Select * from CPLTDetails where ID = @Id and Status = @Status", new { Id = value.cpLTDetails[i].Id, Status = true }).FirstOrDefault();
//// var isRecordExist
//string deleteQuery = @"UPDATE CPLTDetails Set Status = @IsActive where Id = @Id";
//var resultfordelte = connection.Execute(deleteQuery, new { IsActive = false, Id = value.cpLTDetails[i].Id });
}
else
{
var data = new CPLTDetails
{
Id = value.cpLTDetails[i].Id,
CPLTId = value.cpLTDetails[i].CPLTId,
ProdId = value.cpLTDetails[i].ProdId,
OemId = value.cpLTDetails[i].OemId,
Quantity = value.cpLTDetails[i].Quantity,
Rate = value.cpLTDetails[i].Rate,
Value = value.cpLTDetails[i].Value,
DeliveryDate = value.cpLTDetails[i].DeliveryDate,
Status = true
};
var id = connection.Query <int>(@"insert CPLTDetails(DeliveryDate,OemId,ProdId,Quantity,Rate,Value,CPLTId,Status) values (@DeliveryDate,@OemId,@ProdId,@Quantity,@Rate,@Value,@CPLTId,@Status) select cast(scope_identity() as int)", data).First();
}
}
var currentActiveRecords = connection.Query <int>("Select Id from CPLTDetails where Status = @Status", new { Status = true }).ToList();
foreach (var item in currentActiveRecords)
{
var a = listOfIds.IndexOf(item);
if (a == -1)
{
//var currentRecord = connection.Query<int>("Select * from CPLTDetails where ID = @Id", new { Id = item}).FirstOrDefault();
string deleteQuery = @"UPDATE CPLTDetails Set Status = @IsActive where Id = @Id";
var resultfordelte = connection.Execute(deleteQuery, new { IsActive = false, Id = item });
}
}
scope.Complete();
return(Json(new { Message = "Record Inserted Successfully" }));
}
}
catch (Exception)
{
throw;
}
// return Ok();
}
}
