public ActionResult CreateRequest(DirectPurchaseRequest model)
{
if (!ModelState.IsValid)
{
FillSO();
FillJC();
GetMaterials();
return(View("Create", model));
}
FillSO();
FillJC();
model.OrganizationId = OrganizationId;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID.ToString();
string referenceNo = new DirectPurchaseRepository().InsertDirectPurchaseRequest(model);
if (referenceNo != "")
//if (new DirectPurchaseRepository().InsertDirectPurchaseRequest(model) > 0)
{
TempData["success"] = "Saved successfully. Reference No. is " + referenceNo;
TempData["error"] = "";
return(RedirectToAction("CreateRequest"));
}
else
{
GetMaterials();
TempData["success"] = "";
TempData["error"] = "Some error occured. Please try again.";
return(View("Create", model));
}
}
public string InsertPurchaseIndent(DirectPurchaseRequest model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
model.PurchaseRequestNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 36, true, txn);
string sql = @"insert into PurchaseRequest
(PurchaseRequestNo,PurchaseRequestDate,SpecialRemarks,RequiredDate,CreatedBy,CreatedDate,OrganizationId) Values (@PurchaseRequestNo,@PurchaseRequestDate,@SpecialRemarks,@RequiredDate,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, model, txn).FirstOrDefault();
foreach (DirectPurchaseRequestItem item in model.items)
{
item.DirectPurchaseRequestId = id;
if (item.Quantity == null || item.Quantity == 0)
{
continue;
}
new DirectPurchaseItemRepository().InsertPurchaseIndentItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Purchase Request", id.ToString(), "0");
txn.Commit();
return(model.PurchaseRequestNo);
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
public int UpdatePurchaseIndent(DirectPurchaseRequest model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string query = @"UPDATE PurchaseRequest SET
PurchaseRequestDate = @PurchaseRequestDate,
SpecialRemarks = @SpecialRemarks,
RequiredDate = @RequiredDate,
CreatedBy = @CreatedBy,
CreatedDate = @CreatedDate
WHERE PurchaseRequestId = @DirectPurchaseRequestId";
int id = connection.Execute(query, model, txn);
if (id <= 0)
{
throw new Exception();
}
id = new DirectPurchaseItemRepository().UpdatePurchaseIndentItem(model, connection, txn);
if (id <= 0)
{
throw new Exception();
}
txn.Commit();
return(id);
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
public ActionResult Edit(DirectPurchaseRequest model)
{
ViewBag.Title = "Edit";
model.OrganizationId = OrganizationId;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID.ToString();
FillSO();
FillJC();
GetMaterials();
var repo = new DirectPurchaseRepository();
var result1 = new DirectPurchaseRepository().CHECK(model.DirectPurchaseRequestId);
if (result1 > 0)
{
TempData["error"] = "Sorry!!..Already Used!!";
TempData["PurchaseRequestNo"] = null;
return(View("Edit", model));
}
else
{
try
{
var result2 = new DirectPurchaseRepository().DeleteDirectPurchaseDT(model.DirectPurchaseRequestId);
var result3 = new DirectPurchaseRepository().DeleteDirectPurchaseHD(model.DirectPurchaseRequestId, UserID.ToString());
string id = new DirectPurchaseRepository().InsertDirectPurchaseRequest(model);
TempData["success"] = "Updated successfully. Direct Purchase Request Reference No. is " + id;
TempData["error"] = "";
return(RedirectToAction("Index"));
}
catch (SqlException sx)
{
TempData["error"] = "Some error occured while connecting to database. Please check your network connection and try again.|" + sx.Message;
}
catch (NullReferenceException nx)
{
TempData["error"] = "Some required data was missing. Please try again.|" + nx.Message;
}
catch (Exception ex)
{
TempData["error"] = "Some error occured. Please try again.|" + ex.Message;
}
return(RedirectToAction("CreateRequest"));
}
}
public ActionResult ExportToExcel()
{
DirectPurchaseRequest model = (DirectPurchaseRequest)Session["purchaseIndent"];
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<Table border={0}1{0}>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Material/Spares</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Part No</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Remarks</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>Qty</td>", (Char)34);
sb.AppendFormat("<td style={0}font-weight:bold;{0}>UoM</td>", (Char)34);;
sb.Append("</tr>");
foreach (var item in model.items)
{
sb.Append("<tr>");
sb.AppendFormat("<td>{1}</td>", (Char)34, item.ItemName);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.PartNo);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Remarks);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.Quantity);
sb.AppendFormat("<td>{1}</td>", (Char)34, item.UoM);
sb.Append("</tr>");
}
sb.Append("</Table>");
string ExcelFileName;
ExcelFileName = "PurchaseIndent.xls";
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/excel";
Response.AddHeader("Content-Disposition", "filename=" + ExcelFileName);
Response.Write(sb);
Response.End();
Response.Flush();
return(View());
}
public ActionResult Edit(int id = 0)
{
try
{
if (id != 0)
{
FillSO();
FillJC();
GetMaterials();
FillPartNo();
DirectPurchaseRequest DirectPurchaseRequest = new DirectPurchaseRequest();
DirectPurchaseRequest = new DirectPurchaseRepository().GetDirectPurchaseRequest(id);
DirectPurchaseRequest.items = new DirectPurchaseRepository().GetDirectPurchaseRequestItems(id);
return(View(DirectPurchaseRequest));
}
else
{
TempData["error"] = "That was an invalid/unknown request. Please try again.";
TempData["success"] = "";
}
}
catch (InvalidOperationException iox)
{
TempData["error"] = "Sorry, we could not find the requested item. Please try again.|" + iox.Message;
}
catch (SqlException sx)
{
TempData["error"] = "Some error occured while connecting to database. Please try again after sometime.|" + sx.Message;
}
catch (NullReferenceException nx)
{
TempData["error"] = "Some required data was missing. Please try again.|" + nx.Message;
}
catch (Exception ex)
{
TempData["error"] = "Some error occured. Please try again.|" + ex.Message;
}
TempData["success"] = "";
return(RedirectToAction("CreateRequest"));
}
public DirectPurchaseRequest GetPurchaseIndent(int id, int organizationId)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
//the model used for Purchase Indent is DirectPurchaseRequest
string query = @"SELECT
PurchaseRequestId DirectPurchaseRequestId, *
FROM PurchaseRequest
WHERE PurchaseRequestId = @id
AND OrganizationId = @org";
DirectPurchaseRequest model = connection.Query <DirectPurchaseRequest>(query, new { org = organizationId, @id = id }, txn).FirstOrDefault();
string sql = @"SELECT
PRI.PurchaseRequestItemId DirectPurchaseRequestItemId, PRI.*,I.ItemName,I.PartNo,
U.UnitName UoM
FROM PurchaseRequestItem PRI
INNER JOIN Item I ON PRI.ItemId = I.ItemId
INNER JOIN Unit U ON I.ItemUnitId = U.UnitId
WHERE PurchaseRequestId = @id";
model.items = connection.Query <DirectPurchaseRequestItem>(sql, new { id = id }, txn).ToList();
#region checking isUsed
try
{
sql = @" DELETE FROM PurchaseRequestItem WHERE PurchaseRequestId=@Id;
DELETE FROM PurchaseRequest OUTPUT deleted.PurchaseRequestNo WHERE PurchaseRequestId = @Id;";
string output = connection.Query <string>(sql, new { Id = id }, txn).First();
txn.Rollback();
model.isUsed = false;
}
catch (Exception ex)
{
model.isUsed = true;
txn.Rollback();
}
#endregion
return(model);
}
}
internal int UpdatePurchaseIndentItem(DirectPurchaseRequest model, IDbConnection connection, IDbTransaction txn)
{
try
{
string sql = @"DELETE FROM PurchaseRequestItem WHERE PurchaseRequestId = @id";
var id = connection.Execute(sql, new { id = model.DirectPurchaseRequestId }, txn);
if (id <= 0)
{
throw new Exception();
}
foreach (var item in model.items)
{
item.DirectPurchaseRequestId = model.DirectPurchaseRequestId;
InsertPurchaseIndentItem(item, connection, txn);
}
return(id);
}
catch (Exception)
{
return(0);
}
}
public ActionResult EditPurchaseIndent(DirectPurchaseRequest model)
{
try
{
model.CreatedBy = UserID.ToString();
model.CreatedDate = DateTime.Today;
var success = new DirectPurchaseRepository().UpdatePurchaseIndent(model);
if (success <= 0)
{
throw new Exception();
}
TempData["success"] = "Updated successfully (" + model.PurchaseRequestNo + ")";
return(RedirectToAction("PurchaseIndents"));
}
catch (Exception)
{
TempData["error"] = "Some error occured while saving. Please try again.";
FillPartNo();
GetMaterials();
return(View("PurchaseIndent", model));
}
}
public ActionResult PurchaseIndent(DirectPurchaseRequest model)
{
try
{
model.CreatedBy = UserID.ToString();
model.OrganizationId = OrganizationId;
model.CreatedDate = DateTime.Today;
string ref_no = new DirectPurchaseRepository().InsertPurchaseIndent(model);
if (ref_no.Length > 0)
{
TempData["success"] = "Saved Successfully. Reference No. is " + model.PurchaseRequestNo;
return(RedirectToAction("PurchaseIndent"));
}
else
{
throw new Exception();
}
}
catch (Exception)
{
TempData["error"] = "Some error occurred while saving. Please try again.";
return(View(model));
}
}
public string InsertDirectPurchaseRequest(DirectPurchaseRequest model)
{
int id = 0;
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 10, true, txn);
model.PurchaseRequestNo = internalId;
if (model.SoOrJc == "JC")
{
model.SaleOrderId = null;
}
else if (model.SoOrJc == "SO")
{
model.JobCardId = null;
}
else
{
model.isStockOrder = true;
}
model.TotalAmount = model.items.Sum(m => (m.Quantity * m.Rate));
string sql = @"INSERT INTO DirectPurchaseRequest
(
[PurchaseRequestNo],
[PurchaseRequestDate],
[SpecialRemarks],
[RequiredDate],
[TotalAmount],
[CreatedBy],
[CreatedDate],
[OrganizationId],
[isActive],
[isApproved],
[SaleOrderId],
[JobCardId],
[isStockOrder]
)
VALUES
(
@PurchaseRequestNo,
@PurchaseRequestDate,
@SpecialRemarks,
@RequiredDate,
@TotalAmount,
@CreatedBy,
@CreatedDate,
@OrganizationId,
1,
0,
@SaleOrderId,
@JobCardId,
@isStockOrder
)
SELECT CAST(SCOPE_IDENTITY() AS INT)";
id = connection.Query <int>(sql, model, txn).Single <int>();
var supplyorderitemrepo = new DirectPurchaseItemRepository();
foreach (var item in model.items)
{
item.DirectPurchaseRequestId = id;
new DirectPurchaseItemRepository().InsertDirectPurchaseRequestItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "Direct Purchase", id.ToString(), "0");
txn.Commit();
}
catch (Exception ex)
{
logger.Error(ex.Message);
txn.Rollback();
throw;
}
return(model.PurchaseRequestNo);
}
}
