/// <summary>
/// Function for saving transfer in details
/// </summary>
/// <returns>Return successalert when details inserted successfully otherwise return an erroralert</returns>
public OutputMessage Save()
{
if (!Entities.Security.Permissions.AuthorizeUser(this.CreatedBy, Security.BusinessModules.TransferIn, Security.PermissionTypes.Create))
{
return(new OutputMessage("Limited Access. Contact Administrator", false, Type.InsufficientPrivilege, "TranferIn | Save", System.Net.HttpStatusCode.InternalServerError));
}
DBManager db = new DBManager();
try
{
int identity;
//Main Validations. Use ladder-if after this "if" for more validations
if (this.EntryDate.Year < 1900)
{
return(new OutputMessage("Select a valid Entry Date to make a TranferIn.", false, Type.Others, "TranferIn | Save", System.Net.HttpStatusCode.InternalServerError));
}
else if (this.Products.Count <= 0)
{
return(new OutputMessage("Select some products to make a TranferIn.", false, Type.Others, "TranferIn | Save", System.Net.HttpStatusCode.InternalServerError));
}
else
{
db.Open();
string query = @"insert into TBL_TRANSFER_IN_REGISTER(From_Location, Entry_Date, Total_Tax_Amount, Total_Gross_Amount, Total_Net_Amount, Discount_Amount, Other_Charges, Round_Off,
Narration, Entry_No,[Status], Created_By ,Created_Date,Cost_Center_Id,Job_Id,Terms_And_Conditions )
values(@From_Location,@Entry_Date,@Total_Tax_Amount,@Total_Gross_Amount,@Total_Net_Amount,@Discount_Amount,@Other_Charges,@Round_Off,
@Narration,[dbo].UDF_Generate_Sales_Bill(" + this.CompanyId + ",'" + this.FinancialYear + "','TIN'),@Status,@Created_By,GETUTCDATE(),@Cost_Center_Id,@Job_Id,@Terms_And_Conditions); select @@identity";
db.CreateParameters(14);
db.AddParameters(0, "@From_Location", this.FromLocation);
db.AddParameters(1, "@Entry_Date", this.EntryDate);
db.AddParameters(2, "@Total_Tax_Amount", this.TaxAmount);
db.AddParameters(3, "@Total_Gross_Amount", this.Gross);
db.AddParameters(4, "@Total_Net_Amount", this.NetAmount);
db.AddParameters(5, "@Discount_Amount", this.Discount);
db.AddParameters(6, "@Other_Charges", this.OtherCharges);
db.AddParameters(7, "@Round_Off", this.RoundOff);
db.AddParameters(8, "@Narration", this.Narration);
db.AddParameters(9, "@Status", this.Status);
db.AddParameters(10, "@Created_By", this.CreatedBy);
db.AddParameters(11, "@Cost_Center_Id", this.CostCenterId);
db.AddParameters(12, "@Job_Id", this.JobId);
db.AddParameters(13, "@Terms_And_Conditions", this.TermsAndConditions);
db.BeginTransaction();
identity = Convert.ToInt32(db.ExecuteScalar(System.Data.CommandType.Text, query));
UpdateOrderNumber(this.CompanyId, this.FinancialYear, "TIN", db);
foreach (Item item in Products)
{
//Product wise Validations. Use ladder-if after this "if" for more validations
Item prod = TransferOut.GetTransferOutQuantity(item.ItemID, item.InstanceId, item.TodId);
item.TaxPercentage = prod.TaxPercentage;
item.TaxId = prod.TaxId;
item.TaxAmount = prod.CostPrice * (prod.TaxPercentage / 100) * prod.Quantity;
item.Gross = prod.Quantity * prod.CostPrice;
item.NetAmount = item.Gross + item.TaxAmount;
db.CleanupParameters();
query = @"insert into TBL_TRANSFER_IN_DETAILS (Transfer_In_Id,Tod_Id,item_id,Quantity,Mrp,Rate,Selling_Price,Tax_Id,Tax_Amount,Gross_Amount,Net_Amount,Status,instance_id)
values(@Transfer_In_Id,@Tod_Id,@item_id,@Quantity,@Mrp,@Rate,@Selling_Price,@Tax_Id,@Tax_Amount,@Gross_Amount,@Net_Amount,@Status,@instance_id)";
db.CreateParameters(13);
db.AddParameters(0, "@Transfer_In_Id", identity);
db.AddParameters(1, "@Tod_Id", item.TodId);
db.AddParameters(2, "@item_id", item.ItemID);
db.AddParameters(3, "@Quantity", prod.Quantity);
db.AddParameters(4, "@Mrp", prod.MRP);
db.AddParameters(5, "@Rate", prod.CostPrice);
db.AddParameters(6, "@Selling_Price", prod.SellingPrice);
db.AddParameters(7, "@Tax_Id", prod.TaxId);
db.AddParameters(8, "@Tax_Amount", item.TaxAmount);
db.AddParameters(9, "@Gross_Amount", item.Gross);
db.AddParameters(10, "@Net_Amount", item.NetAmount);
db.AddParameters(11, "@Status", 0);
db.AddParameters(12, "@instance_id", item.InstanceId);
db.ExecuteProcedure(System.Data.CommandType.Text, query);
this.TaxAmount += item.TaxAmount;
this.Gross += item.Gross;
query = @"update TBL_TRANSFER_OUT_DETAILS set status=1 where tod_id=@tod_Id;
declare @registerId int,@total int,@totaltransfer int;
select @registerId=Transfer_Out_Id from TBL_TRANSFER_OUT_DETAILS where Tod_Id=@tod_Id;
select @total=count(*) from TBL_TRANSFER_OUT_DETAILS where Transfer_Out_Id=@registerId;
select @totaltransfer=count(*) from TBL_TRANSFER_OUT_DETAILS where Transfer_Out_Id=@registerId and Status=1;
if(@total=@totaltransfer)
begin update TBL_TRANSFER_OUT_REGISTER set Status=1 where Transfer_Out_Id=@registerId end";
db.CreateParameters(1);
db.AddParameters(0, "@tod_Id", item.TodId);
db.ExecuteProcedure(System.Data.CommandType.Text, query);
}
decimal _NetAmount = Math.Round(this.NetAmount);
this.RoundOff = this.NetAmount - _NetAmount;
db.ExecuteNonQuery(CommandType.Text, @"update [TBL_TRANSFER_IN_REGISTER] set [Total_Tax_Amount]=" + this.TaxAmount + ", [Total_Gross_Amount]=" + this.Gross + " ,[Total_Net_Amount]=" + this.NetAmount + " ,[Round_Off]=" + this.RoundOff + " where Transfer_In_Id=" + identity);
}
db.CommitTransaction();
DataTable dt = db.ExecuteQuery(CommandType.Text, "select Entry_No[Saved_No],[dbo].UDF_Generate_Sales_Bill(" + this.CompanyId + ",'" + this.FinancialYear + "','TOT')[New_Order] from TBL_TRANSFER_IN_REGISTER where Transfer_In_Id=" + identity);
return(new OutputMessage("Transfer In registered successfully as " + dt.Rows[0]["Saved_No"].ToString(), true, Type.NoError, "TransferIn | Save", System.Net.HttpStatusCode.OK, new { OrderNo = dt.Rows[0]["New_Order"].ToString() }));
}
catch (Exception ex)
{
db.RollBackTransaction();
return(new OutputMessage("Something went wrong. Transfer In could not be saved ", false, Type.Others, "TransferIn | Save", System.Net.HttpStatusCode.InternalServerError, ex));
}
finally
{
db.Close();
}
}
/// <summary>
/// Function for updating trnsfer in details
/// For updating an entry id must not be zero
/// </summary>
/// <returns>Return successalert when details updated successfully otherwise returns an error alert</returns>
public OutputMessage Update()
{
if (!Entities.Security.Permissions.AuthorizeUser(this.ModifiedBy, Security.BusinessModules.TransferIn, Security.PermissionTypes.Update))
{
return(new OutputMessage("Limited Access. Contact Administrator", false, Type.InsufficientPrivilege, "TransferIn | Update", System.Net.HttpStatusCode.InternalServerError));
}
DBManager db = new DBManager();
try
{
//Main Validations. Use ladder-if after this "if" for more validations
if (this.Products.Count <= 0)
{
return(new OutputMessage("Select some products to make a TransferIn.", false, Type.Others, "TransferIn | Update", System.Net.HttpStatusCode.InternalServerError));
}
else
{
db.Open();
string query = @"update TBL_TRANSFER_IN_REGISTER set From_Location=@From_Location,Entry_Date=@Entry_Date,Total_Tax_Amount=@Total_Tax_Amount,
Total_Gross_Amount=@Total_Gross_Amount,Total_Net_Amount=@Total_Net_Amount,Discount_Amount=@Discount_Amount,Other_Charges=@Other_Charges,
Round_Off=@Round_Off,Narration=@Narration,[Status]=@Status,Modified_By=@Modified_By,
Modified_Date=GETUTCDATE(),Cost_Center_Id=@Cost_Center_Id,Job_Id=@Job_Id,Terms_And_Conditions=@Terms_And_Conditions where Transfer_In_Id=@Id";
db.BeginTransaction();
db.CreateParameters(15);
db.AddParameters(0, "@From_Location", this.FromLocation);
db.AddParameters(1, "@Entry_Date", this.EntryDate);
db.AddParameters(2, "@Total_Tax_Amount", this.TaxAmount);
db.AddParameters(3, "@Total_Gross_Amount", this.Gross);
db.AddParameters(4, "@Total_Net_Amount", this.NetAmount);
db.AddParameters(5, "@Discount_Amount", this.Discount);
db.AddParameters(6, "@Other_Charges", this.OtherCharges);
db.AddParameters(7, "@Round_Off", this.RoundOff);
db.AddParameters(8, "@Narration", this.Narration);
db.AddParameters(9, "@Status", this.Status);
db.AddParameters(10, "@Modified_By", this.ModifiedBy);
db.AddParameters(11, "@Id", this.ID);
db.AddParameters(12, "@Cost_Center_Id", this.CostCenterId);
db.AddParameters(13, "@Job_Id", this.JobId);
db.AddParameters(14, "@Terms_And_Conditions", this.TermsAndConditions);
db.ExecuteScalar(System.Data.CommandType.Text, query);
foreach (Item item in Products)
{
Item prod = TransferOut.GetTransferOutQuantity(item.ItemID, item.InstanceId, item.TodId);
item.TaxPercentage = prod.TaxPercentage;
item.TaxId = prod.TaxId;
item.TaxAmount = prod.CostPrice * (prod.TaxPercentage / 100) * prod.Quantity;
item.Gross = prod.Quantity * prod.CostPrice;
item.NetAmount = item.Gross + item.TaxAmount;
db.CleanupParameters();
query = @"insert into TBL_TRANSFER_IN_DETAILS (Transfer_In_Id,Tod_Id,item_id,Quantity,Mrp,Rate,Selling_Price,Tax_Id,Tax_Amount,Gross_Amount,Net_Amount,Status,instance_id)
values(@Transfer_In_Id,@Tod_Id,@item_id,@Quantity,@Mrp,@Rate,@Selling_Price,@Tax_Id,@Tax_Amount,@Gross_Amount,@Net_Amount,@Status,@instance_id)";
db.CreateParameters(13);
db.AddParameters(0, "@Transfer_In_Id", ID);
db.AddParameters(1, "@Tod_Id", prod.TodId);
db.AddParameters(2, "@item_id", item.ItemID);
db.AddParameters(3, "@Quantity", prod.Quantity);
db.AddParameters(4, "@Mrp", prod.MRP);
db.AddParameters(5, "@Rate", prod.CostPrice);
db.AddParameters(6, "@Selling_Price", prod.SellingPrice);
db.AddParameters(7, "@Tax_Id", prod.TaxId);
db.AddParameters(8, "@Tax_Amount", item.TaxAmount);
db.AddParameters(9, "@Gross_Amount", item.Gross);
db.AddParameters(10, "@Net_Amount", item.NetAmount);
db.AddParameters(11, "@Status", 0);
db.AddParameters(12, "@instance_id", item.InstanceId);
this.TaxAmount += item.TaxAmount;
this.Gross += item.Gross;
}
db.ExecuteNonQuery(CommandType.Text, @"update [TBL_TRANSFER_IN_REGISTER] set [Total_Tax_Amount]=" + this.TaxAmount + ", [Total_Gross_Amount]=" + this.Gross + " ,[Total_Net_Amount]=" + this.NetAmount + " ,[Round_Off]=" + this.RoundOff + " where Transfer_In_Id=" + ID);
}
db.CommitTransaction();
DataTable dt = db.ExecuteQuery(CommandType.Text, "select Entry_No[Saved_No],[dbo].UDF_Generate_Sales_Bill(" + this.CompanyId + ",'" + this.FinancialYear + "','TOT')[New_Order] from TBL_TRANSFER_IN_REGISTER where Transfer_In_Id=" + this.ID);
return(new OutputMessage("Transfer In " + dt.Rows[0]["Saved_No"].ToString() + " has been Updated", true, Type.NoError, "TransferIn | Update", System.Net.HttpStatusCode.OK, new { OrderNo = dt.Rows[0]["New_Order"].ToString() }));
}
catch (Exception ex)
{
dynamic Exception = ex;
if (ex.GetType().GetProperty("Number") != null)
{
if (Exception.Number == 547)
{
db.RollBackTransaction();
return(new OutputMessage("You cannot Update this Entry because it is referenced in other transactions", false, Entities.Type.ForeignKeyViolation, "TransferIn | Update", System.Net.HttpStatusCode.InternalServerError, ex));
}
else
{
db.RollBackTransaction();
return(new OutputMessage("Something went wrong. Transfer In could not be updated", false, Type.Others, "TransferIn | Update", System.Net.HttpStatusCode.InternalServerError, ex));
}
}
else
{
db.RollBackTransaction();
return(new OutputMessage("Something went wrong. Transfer In could not be updated", false, Type.Others, "TransferIn | Update", System.Net.HttpStatusCode.InternalServerError, ex));
}
}
finally
{
db.Close();
}
}