public ActionResult CreateQuerySheet(string type)
{
FillDropdowns();
string internalId = "";
try
{
internalId = DatabaseCommonRepository.GetNextDocNo(5, OrganizationId);
}
catch (NullReferenceException nx)
{
TempData["success"] = "";
TempData["error"] = "Some required data was missing. Please try again.|" + nx.Message;
}
catch (Exception ex)
{
TempData["success"] = "";
TempData["error"] = "Some error occurred. Please try again.|" + ex.Message;
}
QuerySheet qs = new QuerySheet();
qs.Type = type;
qs.QuerySheetDate = DateTime.Now;
var repo = new QuerySheetRepository();
qs.QuerySheetItems = new List <QuerySheetItem>();
qs.QuerySheetItems.Add(new QuerySheetItem());
qs.QuerySheetItems[0].ProjectRoomUnits = new List <QuerySheetUnit>();
qs.QuerySheetItems[0].ProjectRoomUnits.Add(new QuerySheetUnit());
qs.QuerySheetItems[0].ProjectRoomDoors = new List <QuerySheetDoor>();
qs.QuerySheetItems[0].ProjectRoomDoors.Add(new QuerySheetDoor());
qs.QuerySheetRefNo = internalId;
return(View(qs));
}
public string InsertQuerySheet(QuerySheet objQuerySheet)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
var internalId = DatabaseCommonRepository.GetNewDocNo(connection, objQuerySheet.OrganizationId, 5, true, txn);
objQuerySheet.QuerySheetRefNo = internalId;
string sql = @"insert into QuerySheet(QuerySheetRefNo,QuerySheetDate,ProjectName,ContactPerson,ContactNumber,Email,Type,CreatedBy,CreatedDate,OrganizationId)
Values (@QuerySheetRefNo,@QuerySheetDate,@ProjectName,@ContactPerson,@ContactNumber,@Email,@Type,@CreatedBy,@CreatedDate,@OrganizationId);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, objQuerySheet, txn).Single();
foreach (QuerySheetItem item in objQuerySheet.QuerySheetItems)
{
item.QuerySheetId = id;
new ProjectCostRepository().InsertQuerySheetItem(item, connection, txn);
}
InsertLoginHistory(dataConnection, objQuerySheet.CreatedBy, "Create", "Query Sheet", id.ToString(), "0");
txn.Commit();
return(id + "|" + internalId);
}
catch (Exception)
{
txn.Rollback();
return("0");
}
}
}
public string SaveWorkspaceTempQuery(QuerySheet querySheet)
{
var tempPath = Path.GetTempFileName();
var newPath = Path.Combine(GetWorkspacePath(true), Path.GetFileName(tempPath));
File.Move(tempPath, newPath);
Save(querySheet, newPath);
return(newPath);
}
private void OnNewQuerySheetMessage(NewQuerySheetMessage message)
{
var querySheet = new QuerySheet();
var vm = _viewModelFactory.CreateQuerySheet(querySheet, null, message.Context);
vm.CloseRequested += OnTabCloseRequested;
Tabs.Add(vm);
CurrentTab = vm;
}
public ActionResult Edit(QuerySheet model)
{
ViewBag.Title = "Edit";
model.OrganizationId = OrganizationId;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID.ToString();
var repo = new QuerySheetRepository();
var result1 = new QuerySheetRepository().CHECK(model.QuerySheetId);
if (result1 > 0)
{
TempData["error"] = "Sorry! Already Used!";
TempData["QuerySheetRefNo"] = null;
return(View("Edit", model));
}
else
{
try
{
string ref_no;
if (model.Type == "Unit")
{
ref_no = new QuerySheetRepository().UpdateQuerySheetUnitSelection(model);
}
else
{
ref_no = new QuerySheetRepository().UpdateQuerySheet(model);
}
TempData["success"] = "Updated successfully. Query Sheet Reference No. is " + ref_no;
TempData["error"] = "";
return(RedirectToAction("Index", new { Type = model.Type }));
}
catch (SqlException)
{
TempData["error"] = "Some error occured while saving. Please try again.";
}
catch (NullReferenceException)
{
TempData["error"] = "Some required data was missing. Please try again.";
}
catch (Exception)
{
TempData["error"] = "Some error occured. Please try again.";
}
return(RedirectToAction("Index", new { Type = model.Type }));
}
}
public QuerySheetViewModel(
QuerySheet querySheet,
string?path,
IContainerContext?containerContext,
IViewModelFactory viewModelFactory,
IDialogService dialogService,
IMessenger messenger,
IQueryPersistenceService queryPersistenceService)
{
_containerContext = containerContext;
_queryPersistenceService = queryPersistenceService;
_viewModelFactory = viewModelFactory;
_dialogService = dialogService;
_messenger = messenger;
_filePath = path;
_title = string.IsNullOrEmpty(path)
? "Untitled " + (++UntitledCounter)
: Path.GetFileNameWithoutExtension(path);
_text = querySheet.Text;
_result = _viewModelFactory.CreateNotRunQueryResult();
_partitionKey = querySheet.PartitionKey;
PartitionKeyMRU = new ObservableCollection <string>();
foreach (var mru in querySheet.PartitionKeyMRU)
{
PartitionKeyMRU.Add(mru);
}
Parameters = new ParametersViewModel <QueryParameterViewModel>();
foreach (var p in querySheet.Parameters)
{
Parameters.AddParameter(CreateParameter(p));
ShowParameters = true;
}
Parameters.AddPlaceholder();
Parameters.Changed += OnParametersChanged;
Errors = new ViewModelValidator <QuerySheetViewModel>(this);
Errors.AddValidator(
vm => vm.PartitionKey,
value => string.IsNullOrEmpty(value) || JsonHelper.TryParseJsonValue(value, out _)
? string.Empty
: "Invalid partition key value");
_messenger.Subscribe(this).To <ExplorerSelectedContainerChangedMessage>(
(vm, message) => vm.OnExplorerSelectedContainerChanged(message));
}
public int UpdateQuerySheetUnit(QuerySheet model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
return(UpdateQuerySheetUnit(connection, model, txn));
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public ActionResult Edit(string type, int id = 0)
{
try
{
FillDropdowns();
if (id != 0)
{
QuerySheet QuerySheet = new QuerySheet();
var repo = new ProjectCostRepository();
QuerySheet = new QuerySheetRepository().GetQuerySheetItem(id);
QuerySheet.Items = repo.GetProjectCost(id);
ViewBag.Type = type;
return(View(QuerySheet));
}
else
{
TempData["error"] = "That was an invalid/unknown request. Please try again.";
return(RedirectToAction("Index", "Home"));
}
}
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("Index", new { Type = "Unit" }));
}
private int UpdateQuerySheetUnit(IDbConnection connection, QuerySheet model, IDbTransaction txn)
{
string sql = "";
//
var row = 0;
foreach (QuerySheetItem item in model.QuerySheetItems)
{
sql = @"UPDATE QuerySheetItem SET Kilowatt = @Kilowatt,Cost = @Cost WHERE QuerySheetItemId = @QuerySheetItemId";
row = connection.Execute(sql, item, txn);
}
sql = @"UPDATE QuerySheet SET Type=@Type WHERE QuerySheetId = @QuerySheetId";
row = connection.Execute(sql, model, txn);
foreach (QuerySheetItem items in model.QuerySheetItems)
{
foreach (QuerySheetUnit item in items.ProjectRoomUnits)
{
item.QuerySheetItemId = items.QuerySheetItemId;
sql = @"insert into QuerySheetItemUnit(QuerySheetItemId,EvaporatorUnitId,CondenserUnitId,Quantity)
Values (@QuerySheetItemId,@EvaporatorUnitId,@CondenserUnitId,@Quantity)";
row = connection.Execute(sql, item, txn);
}
foreach (QuerySheetDoor item in items.ProjectRoomDoors)
{
if (item.DoorId == "")
{
continue;
}
item.QuerySheetItemId = items.QuerySheetItemId;
sql = @"insert into QuerySheetItemDoor(QuerySheetItemId,DoorId,Quantity)
Values (@QuerySheetItemId,@DoorId,@Quantity)";
row = connection.Execute(sql, item, txn);
}
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Update", typeof(QuerySheet).Name, model.QuerySheetId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(row);
}
public string UpdateQuerySheetUnitSelection(QuerySheet model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string sql = @"DELETE FROM QuerySheetItemUnit WHERE QuerySheetItemId IN (SELECT QuerySheetItemId FROM QuerySheetItem WHERE QuerySheetId = @QuerySheetId);
DELETE FROM QuerySheetItemDoor WHERE QuerySheetItemId IN (SELECT QuerySheetItemId FROM QuerySheetItem WHERE QuerySheetId = @QuerySheetId);";
var count = connection.Execute(sql, new { QuerySheetId = model.QuerySheetId }, txn);
UpdateQuerySheetUnit(connection, model, txn);
//txn.Commit();
return(model.QuerySheetRefNo);
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
public QuerySheet GetQuerySheetItem(int querySheetId)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
QuerySheet model = new QuerySheet();
string query = @"select * from QuerySheet
where QuerySheetId=@id
SELECT * FROM QuerySheetItem WHERE QuerySheetId = @id
SELECT QuerySheetItemId INTO #Rooms FROM QuerySheetItem WHERE QuerySheetId = @id
SELECT
*
FROM QuerySheetItemUnit
WHERE QuerySheetItemId IN (SELECT QuerySheetItemId FROM #Rooms)
SELECT
*
FROM QuerySheetItemDoor
WHERE QuerySheetItemId IN (SELECT QuerySheetItemId FROM #Rooms)
DROP TABLE #Rooms;";
using (var dataset = connection.QueryMultiple(query, new { id = querySheetId }))
{
model = dataset.Read <QuerySheet>().First();
model.QuerySheetItems = dataset.Read <QuerySheetItem>().AsList();
List <QuerySheetUnit> units = dataset.Read <QuerySheetUnit>().AsList();
List <QuerySheetDoor> doors = dataset.Read <QuerySheetDoor>().AsList();
foreach (var item in model.QuerySheetItems)
{
item.ProjectRoomUnits = units.Where(x => x.QuerySheetItemId == item.QuerySheetItemId).Select(x => x).ToList();
item.ProjectRoomDoors = doors.Where(x => x.QuerySheetItemId == item.QuerySheetItemId).Select(x => x).ToList();
}
}
return(model);
}
}
public ActionResult CreateQuerySheetCosting(string type)
{
string internalId = "";
try
{
internalId = DatabaseCommonRepository.GetNextDocNo(5, OrganizationId);
}
catch (NullReferenceException nx)
{
TempData["success"] = "";
TempData["error"] = "Some required data was missing. Please try again.|" + nx.Message;
}
catch (Exception ex)
{
TempData["success"] = "";
TempData["error"] = "Some error occurred. Please try again.|" + ex.Message;
}
QuerySheet qs = new QuerySheet();
qs.Type = type;
qs.QuerySheetDate = DateTime.Now;
var repo = new QuerySheetRepository();
qs.QuerySheetItems = new List <QuerySheetItem>();
qs.QuerySheetItems.Add(new QuerySheetItem());
var PCList = repo.GetProjectCostingParameter();
qs.Items = new List <ProjectCost>();
qs.QuerySheetRefNo = internalId;
foreach (var item in PCList)
{
var pcitem = new ProjectCost {
CostingId = item.CostingId, Description = item.Description, Remarks = item.Remarks, Amount = item.Amount
};
qs.Items.Add(pcitem);
}
return(View("CreateQuerySheet", qs));
}
public int InsertProjectCosting(QuerySheet model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
//
var row = 0;
foreach (ProjectCost item in model.Items)
{
item.QuerySheetId = model.QuerySheetId;
item.Type = model.Type;
string sql = @"insert into ProjectCosting(QuerySheetId,CostingId,Remarks,Amount) Values (@QuerySheetId,@CostingId,@Remarks,@Amount)
UPDATE QuerySheet SET Type=@Type WHERE QuerySheetId = @QuerySheetId";
row = connection.Execute(sql, item, txn);
}
#region updating costing amount in [QuerySheet] table
model.CostingAmount = model.Items.Sum(x => x.Amount);
string query = @"UPDATE QuerySheet SET CostingAmount = @CostingAmount WHERE QuerySheetId = @QuerySheetId";
connection.Execute(query, new { QuerySheetId = model.QuerySheetId, CostingAmount = model.CostingAmount }, txn);
#endregion
InsertLoginHistory(dataConnection, model.CreatedBy, "Update", typeof(QuerySheet).Name, model.QuerySheetId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(row);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public void Save(QuerySheet querySheet, string path)
{
var json = JsonConvert.SerializeObject(querySheet, Formatting.Indented);
File.WriteAllText(path, json);
}
public ActionResult CreateQuerySheet(QuerySheet qs)
{
//if (qs.QuerySheetId==0)
//{
try
{
qs.OrganizationId = OrganizationId;
qs.CreatedDate = System.DateTime.Now;
qs.CreatedBy = UserID.ToString();
var id = "";
int row;
if (qs.Type == "Unit")
{
FillDropdowns();
row = new QuerySheetRepository().UpdateQuerySheetUnit(qs);
TempData["success"] = "Saved Successfully (" + qs.QuerySheetRefNo + ")";
return(RedirectToAction("PendingQuerySheetforUnit"));
}
else if (qs.Type == "Costing")
{
FillDropdowns();
if (qs.Items == null || qs.Items.Count <= 0)
{
TempData["error"] = "Query Sheet cannot be saved without costing parameters.";
return(RedirectToAction("CreateQuerySheetUnit", new { QuerySheetid = qs.QuerySheetId, type = "Costing" }));
}
row = new ProjectCostRepository().InsertProjectCosting(qs);
TempData["success"] = "Saved Successfully (" + qs.QuerySheetRefNo + ")";
return(RedirectToAction("PendingQuerySheetforCosting"));
}
else if (qs.Type == "RoomDetails")
{
id = new QuerySheetRepository().InsertQuerySheet(qs);
if (id.Split('|')[0] != "0")
{
qs.QuerySheetId = Convert.ToInt16(id.Split('|')[0]);
qs.QuerySheetRefNo = id.Split('|')[1];
TempData["success"] = "Saved successfully. Reference No. is " + id.Split('|')[1];
TempData["error"] = "";
return(RedirectToAction("CreateQuerySheet", new { type = qs.Type }));
}
else
{
throw new Exception();
}
}
}
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;
}
TempData["success"] = "";
//}
return(RedirectToAction("CreateQuerySheet", new { type = qs.Type }));
}
public string UpdateQuerySheet(QuerySheet model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string query = @"DELETE FROM QuerySheetItem WHERE QuerySheetId = @QuerySheetId";
//DELETE FROM QuerySheetItemUnit U inner join QuerySheetItem I on U.QuerySheetItemId=I.QuerySheetItemId WHERE I.QuerySheetId = @QuerySheetId
//DELETE FROM QuerySheetItemDoor D inner join QuerySheetItem I on D.QuerySheetItemId=I.QuerySheetItemId WHERE I.QuerySheetId = @QuerySheetId
//DELETE FROM ProjectCosting WHERE QuerySheetId = @QuerySheetId
connection.Execute(query, new { QuerySheetId = model.QuerySheetId }, txn);
//var row = 0;
//foreach (ProjectCost item in model.Items)
//{
// item.QuerySheetId = model.QuerySheetId;
// new ProjectCostRepository().InsertProjectCosting(item, connection, txn);
//}
foreach (QuerySheetItem item in model.QuerySheetItems)
{
item.QuerySheetId = model.QuerySheetId;
new ProjectCostRepository().InsertQuerySheetItem(item, connection, txn);
}
// foreach (QuerySheetItem items in model.QuerySheetItems)
// {
// items.QuerySheetId = model.QuerySheetId;
// new ProjectCostRepository().InsertQuerySheetItem(items, connection, txn);
// foreach (QuerySheetUnit item in items.ProjectRoomUnits)
// {
// item.QuerySheetItemId = items.QuerySheetItemId;
// query = @"insert into QuerySheetItemUnit(QuerySheetItemId,EvaporatorUnitId,CondenserUnitId,Quantity)
// Values (@QuerySheetItemId,@EvaporatorUnitId,@CondenserUnitId,@Quantity)";
// row = connection.Execute(query, item, txn);
// }
// foreach (QuerySheetDoor item in items.ProjectRoomDoors)
// {
// item.QuerySheetItemId = items.QuerySheetItemId;
// query = @"insert into QuerySheetItemDoor(QuerySheetItemId,DoorId,Quantity)
// Values (@QuerySheetItemId,@DoorId,@Quantity)";
// row = connection.Execute(query, item, txn);
// }
// }
query = @"UPDATE QuerySheet SET QuerySheetRefNo = @QuerySheetRefNo,QuerySheetDate = @QuerySheetDate,ProjectName = @ProjectName,ContactPerson = @ContactPerson,ContactNumber = @ContactNumber,
Email = @Email
OUTPUT inserted.QuerySheetRefNo
WHERE QuerySheetId = @QuerySheetId";
string ref_no = connection.Query <string>(query, model, txn).First();
InsertLoginHistory(dataConnection, model.CreatedBy, "Update", typeof(QuerySheet).Name, model.QuerySheetId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(ref_no);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
