internal Task <string> Save(List <AdmissionModel> aModel)
{
try
{
Con.Open();
_trans = Con.BeginTransaction();
string refNo = "AP" + GetTrNoWithOpenCon("RefNo", "tbl_IN_LEDGER_OF_ADMITTED_PATIENT", _trans);
int regId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel[0].PtIndoorId + "", "RegId", _trans));
int subsubPnoId = GetSubSubPnoByIndoorId(aModel[0].PtIndoorId, _trans);
foreach (var adM in aModel)
{
_ledgerInsertGateway.InsertLedgerOfAdmittedPatient(refNo, regId, adM.PtIndoorId, adM.ItemId, adM.Charge, adM.Quantity, adM.Vat, adM.ServiceCharge, adM.Charge * adM.Quantity + adM.Vat + adM.ServiceCharge, 0, 0, 0, 0, adM.DrId, subsubPnoId, adM.UserName, Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + adM.PtIndoorId + "", "BedId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + adM.PtIndoorId + "", "RefDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + adM.PtIndoorId + "", "AdmitDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + adM.PtIndoorId + "", "UnderDrId", _trans)), 0, 0, 0, 0, 0, "N/A", "N/A", adM.Remarks, _trans, Con);
}
_trans.Commit();
Con.Close();
return(Task.FromResult("Save Successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public void Atualizar(Formulario f)
{
try
{
AbrirConexao(); //abrir conexão..
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("UPDATE Formulario.formulario SET Nome = @v2, Empresa = @v3, Email = @v4 where IdFormulario = @v1", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", f.IdFormulario);
Cmd.Parameters.AddWithValue("@v2", f.Nome);
Cmd.Parameters.AddWithValue("@v3", f.Empresa);
//Cmd.Parameters.AddWithValue("@v4", f.DataCriacao);
//Cmd.Parameters.AddWithValue("@v5", f.DataConclusao);
//Cmd.Parameters.AddWithValue("@v6", f.UltimoAcesso);
//Cmd.Parameters.AddWithValue("@v7", f.Acessado);
Cmd.Parameters.AddWithValue("@v4", f.Email);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Não foi possível atualizar o Formulário: " + ex.Message);
}
finally
{
FecharConexao(); //fechar conexão..
}
}
internal string SaveBedChange(int ptId, double bedCharge, int bedId, string userName, string remarks)
{
try
{
int prevBedId = Convert.ToInt32(ReturnFieldValue("tbl_IN_PATIENT_ADMISSION", "Id=" + ptId + "", "BedId"));
Con.Open();
_trans = Con.BeginTransaction();
_insertGateway.InsertLedgerOfAdmittedPatient("BC" + GetTrNoWithOpenCon("RefNo", "tbl_IN_LEDGER_OF_ADMITTED_PATIENT", _trans), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + ptId + "", "RegId", _trans)), ptId, 1942, bedCharge, 1, 0, 0, bedCharge, 0, 0, 0, 0, 0, 80, userName, bedId, Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + ptId + "", "RefDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + ptId + "", "AdmitDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + ptId + "", "UnderDrId", _trans)), 0, 0, 0, 0, 0, "N/A", "N/A", remarks, _trans, Con);
DeleteInsert("Update tbl_IN_PATIENT_ADMISSION SET BedId=" + bedId + " WHERE Id=" + ptId + "", _trans);
DeleteInsert("Update tbl_IN_BED_INFO SET IsBooked=0 WHERE Id=" + prevBedId + "", _trans);
DeleteInsert("Update tbl_IN_BED_INFO SET IsBooked=1 WHERE Id=" + bedId + "", _trans);
_trans.Commit();
Con.Close();
return("Update Successful");
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(exception.ToString());
}
}
public void Excluir(int id)
{
try
{
AbrirConexao();
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("DELETE FROM Formulario.PerguntasPorFormularios WHERE IdFormulario = @v1", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", id);
Cmd.ExecuteNonQuery();
Cmd = new SqlCommand("DELETE FROM Formulario.Formulario WHERE IdFormulario = @v1", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", id);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Não foi possível remover o formulário: " + ex.Message);
}
}
internal static void BeginTransactions(params DbConnection[] ConnectionArray)
{
foreach (var Con in ConnectionArray)
{
Con.BeginTransaction();
}
}
internal Task <string> Save(InvoiceModel mInvoice)
{
try
{
Con.Open();
Thread.Sleep(50);
_trans = Con.BeginTransaction();
int indoorId = 0;
int bedId = 0;
mInvoice.TrNo = GetTrNoWithOpenCon("TrNo", "tbl_INVOICE_DUE_COLLECTION", _trans);
const string query = @"INSERT INTO tbl_INVOICE_DUE_COLLECTION (DrId, RegId, IndoorId, InvMasterId, TrNo, TrDate, PaidAmt, DscountPc, DiscountPcOrTk, DiscountFrom, DiscountAmt, Remarks, CashAmt, CardAmt, ChequeAmt, CardNumber, CardBankId, ChqNo, ChqBankId, BedId, UserName, BranchId,SubSubPnoId)
VALUES (@DrId,@RegId, @IndoorId, @InvMasterId, @TrNo, @TrDate, @PaidAmt, @DscountPc, @DiscountPcOrTk, @DiscountFrom, @DiscountAmt, @Remarks, @CashAmt, @CardAmt,@ChequeAmt, @CardNumber, @CardBankId, @ChqNo, @ChqBankId, @BedId, @UserName, @BranchId,@SubSubPnoId)";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DrId", ReturnFieldValueOpenCon("tbl_INVOICE_MASTER", "Id=" + mInvoice.InvMasterId + "", "RefDrId", _trans));
cmd.Parameters.AddWithValue("@RegId", mInvoice.PtRegId);
cmd.Parameters.AddWithValue("@IndoorId", indoorId);
cmd.Parameters.AddWithValue("@InvMasterId", mInvoice.InvMasterId);
cmd.Parameters.AddWithValue("@TrNo", mInvoice.TrNo);
cmd.Parameters.AddWithValue("@TrDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@PaidAmt", mInvoice.CashAmount + mInvoice.CardAmount + mInvoice.CheaqueAmount);
cmd.Parameters.AddWithValue("@DscountPc", mInvoice.LessPc);
cmd.Parameters.AddWithValue("@DiscountPcOrTk", mInvoice.LessPcOrTk);
cmd.Parameters.AddWithValue("@DiscountFrom", mInvoice.LessFrom);
cmd.Parameters.AddWithValue("@DiscountAmt", mInvoice.LessAmount);
cmd.Parameters.AddWithValue("@Remarks", mInvoice.Remarks);
cmd.Parameters.AddWithValue("@CashAmt", mInvoice.CashAmount);
cmd.Parameters.AddWithValue("@CardAmt", mInvoice.CardAmount);
cmd.Parameters.AddWithValue("@ChequeAmt", mInvoice.CheaqueAmount);
cmd.Parameters.AddWithValue("@CardNumber", mInvoice.CardNumber);
cmd.Parameters.AddWithValue("@CardBankId", mInvoice.CardBankId);
cmd.Parameters.AddWithValue("@ChqNo", mInvoice.CheaqueNumber);
cmd.Parameters.AddWithValue("@ChqBankId", mInvoice.CheaqueBankId);
cmd.Parameters.AddWithValue("@BedId", bedId);
cmd.Parameters.AddWithValue("@SubSubPnoId", 69);
cmd.Parameters.AddWithValue("@UserName", mInvoice.UserName);
cmd.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(mInvoice.UserName, _trans));
cmd.ExecuteNonQuery();
InsertIntoInvoiceLedger(mInvoice.PtRegId, indoorId, mInvoice.InvMasterId, mInvoice.TrNo, DateTime.Now, 0, mInvoice.LessAmount, mInvoice.ReceiveAmount, 0, 69, "DueColl", mInvoice.UserName, _trans);
if (mInvoice.LessAmount > 0)
{
InsertIntoHonoriumLedger(mInvoice.DrId, mInvoice.TrNo, DateTime.Now, mInvoice.InvMasterId, 0, 0, mInvoice.LessAmount, 0, 65, mInvoice.UserName, _trans);
}
_trans.Commit();
Con.Close();
return(Task.FromResult <string>("Save Successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public void BeginTransaction()
{
try
{
Con = Factory.CreateConnection();
Con.ConnectionString = ConnectionString;
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Transaction = Con.BeginTransaction();
Cmd = Con.CreateCommand();
Cmd.Transaction = Transaction;
IsBeginTransaction = true;
}
catch
{
IsBeginTransaction = false;
if (Con.State == ConnectionState.Open)
{
Cmd.Dispose();
Transaction.Dispose();
Con.Close();
}
throw;
}
}
public void Atualizar(Perguntas p)
{
try
{
AbrirConexao();
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("UPDATE Formulario.Perguntas SET descricao = @v2, tipo = @v3 WHERE IdPergunta = @v1", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", p.IdPergunta);
Cmd.Parameters.AddWithValue("@v2", p.Descricao);
Cmd.Parameters.AddWithValue("@v3", p.Tipo);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Não foi possível atualizar a pergunta: " + ex.Message);
}
finally
{
FecharConexao(); //fechar conexão..
}
}
/// <summary>
/// Metodo utilizado para excluir um cliente na base de dados e seu respectivo endereco
/// </summary>
/// <param name="Id">Int Id (Código da entidade Cliente)</param>
public void Delete(int Id)
{
try
{
OpenConnection();
Tr = Con.BeginTransaction();
string queryEndereco = "delete from Endereco where IdCliente = @Id";
Cmd = new SqlCommand(queryEndereco, Con, Tr);
Cmd.Parameters.AddWithValue("@Id", Id);
Cmd.ExecuteNonQuery();
string queryCliente = "delete from Cliente where IdCliente = @Id";
Cmd = new SqlCommand(queryCliente, Con, Tr);
Cmd.Parameters.AddWithValue("@Id", Id);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
throw new Exception("Erro ao excluir cliente. \n" + ex.Message);
}
finally
{
CloseConnection();
}
}
public void AtualizaUltimoAcesso(int id)
{
try
{
AbrirConexao(); //abrir conexão..
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("UPDATE Formulario.formulario SET UltimoAcesso = '" + DateTime.Now.ToString("dd/MM/yyyy hh:mm") + "' where IdFormulario = " + id, Con, Tr);
string t = "UPDATE Formulario.formulario SET UltimoAcesso = '" + DateTime.Now.ToString("dd/MM/yyyy hh:mm") + "' where IdFormulario = " + id;
Cmd.ExecuteNonQuery();
Cmd = new SqlCommand("UPDATE Formulario.Formulario SET Acessado = 'SIM' where IdFormulario = " + id, Con, Tr);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Não foi possível atualizar o Formulário: " + ex.Message);
}
finally
{
FecharConexao(); //fechar conexão..
}
}
public Task <string> Save(PharSalesModel aModel)
{
try
{
Con.Open();
Thread.Sleep(5);
_trans = Con.BeginTransaction();
string trNo = GetTrNo("TrNo", "tbl_PHAR_PURCHASE_LEDGER", _trans);
string invoiceNo = trNo;
const string query = @"INSERT INTO tbl_PHAR_COMPANY (Name,Address,Phone,EntryDate,UserName)
output inserted.id VALUES (@Name,@Address,@Phone,@EntryDate,@UserName)" ;
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Name", aModel.Name);
cmd.Parameters.AddWithValue("@Address", aModel.Address);
cmd.Parameters.AddWithValue("@Phone", aModel.Phone);
cmd.Parameters.AddWithValue("@EntryDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@UserName", aModel.UserName);
cmd.Parameters.AddWithValue("@Status", aModel.Status);
var comid = (int)cmd.ExecuteScalar();
// tbl_PHAR_PURCHASE_LEDGER
const string query2 = @"INSERT INTO tbl_PHAR_PURCHASE_LEDGER (TrNo,TrDate,InvoiceNo,InvoiceDate,ComPanyId,PurchaseAmount,LessAmount,PaymentAmount,Status,SubSubPnoId,UserName,PaymentStatus,BranchId,EntryDate,EntryTime) VALUES (@TrNo,@TrDate,@InvoiceNo,@InvoiceDate,@ComPanyId,@PurchaseAmount,@LessAmount,@PaymentAmount,@Status,@SubSubPnoId,@UserName,@PaymentStatus,@BranchId,@EntryDate,@EntryTime)";
var cmd2 = new SqlCommand(query2, Con, _trans);
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@TrNo", trNo);
cmd2.Parameters.AddWithValue("@TrDate", aModel.InvoiceDate.ToString("yyyy-MM-dd"));
cmd2.Parameters.AddWithValue("@InvoiceNo", invoiceNo);
cmd2.Parameters.AddWithValue("@InvoiceDate", aModel.InvoiceDate.ToString("yyyy-MM-dd"));
cmd2.Parameters.AddWithValue("@ComPanyId", comid);
cmd2.Parameters.AddWithValue("@PurchaseAmount", aModel.TotalPrice);
cmd2.Parameters.AddWithValue("@LessAmount", 0);
cmd2.Parameters.AddWithValue("@PaymentAmount", 0);
cmd2.Parameters.AddWithValue("@Status", "Opening Balance");
cmd2.Parameters.AddWithValue("@SubSubPnoId", "65");
cmd2.Parameters.AddWithValue("@UserName", aModel.UserName);
cmd2.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(aModel.UserName, _trans));
cmd2.Parameters.AddWithValue("@EntryTime", DateTime.Now.ToShortTimeString());
cmd2.Parameters.AddWithValue("@PaymentStatus", aModel.PaymentAmt > 0 ? 2 : 1);
cmd2.Parameters.AddWithValue("@EntryDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd2.ExecuteNonQuery();
_trans.Commit();
Con.Close();
return(Task.FromResult("Save successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public Task <string> Save(List <EmployeeModel> aModel)
{
try
{
Con.Open();
Thread.Sleep(50);
_trans = Con.BeginTransaction();
foreach (var employevar in aModel)
{
const string query = @"INSERT INTO tbl_HR_Increment (EmCode, EmpId, Grade, Department, Designation, Project, SalaryType, SalaryTypeId, Amount, Year, Month, UserName, EntryDate) VALUES (@EmCode, @EmpId, @Grade, @Department, @Designation, @Project, @SalaryType, @SalaryTypeId, @Amount, @Year, @Month, @UserName, @EntryDate)";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@EmCode", employevar.EmCode);
cmd.Parameters.AddWithValue("@EmpId", employevar.EmId);
cmd.Parameters.AddWithValue("@Grade", employevar.Grade);
cmd.Parameters.AddWithValue("@Department", employevar.DepartmentName);
cmd.Parameters.AddWithValue("@Designation", employevar.EmDesignationName);
cmd.Parameters.AddWithValue("@Project", employevar.UnitName);
cmd.Parameters.AddWithValue("@SalaryType", employevar.ItemType);
cmd.Parameters.AddWithValue("@SalaryTypeId", employevar.Itemid);
cmd.Parameters.AddWithValue("@Amount", employevar.ItemCharge);
cmd.Parameters.AddWithValue("@Year", employevar.Year);
cmd.Parameters.AddWithValue("@Month", employevar.MonthName);
cmd.Parameters.AddWithValue("@UserName", employevar.EmUserName);
cmd.Parameters.AddWithValue("@EntryDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.ExecuteNonQuery();
}
string query1 = @"Delete from tbl_HR_GLO_EMPLOYEE_DLS Where EmpId=" + aModel.ElementAt(0).EmId + "";
var cmd1 = new SqlCommand(query1, Con, _trans);
cmd1.Parameters.Clear();
cmd1.ExecuteNonQuery();
foreach (var employevar in aModel)
{
const string query2 = @"INSERT INTO tbl_HR_GLO_EMPLOYEE_DLS (EmpId, SalaryType, SalaryTypeId, Amount) VALUES (@EmpId, @SalaryType, @SalaryTypeId, @Amount)";
var cmd2 = new SqlCommand(query2, Con, _trans);
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@EmpId", employevar.EmId);
cmd2.Parameters.AddWithValue("@SalaryType", employevar.ItemType);
cmd2.Parameters.AddWithValue("@SalaryTypeId", employevar.Itemid);
cmd2.Parameters.AddWithValue("@Amount", employevar.ItemCharge);
cmd2.ExecuteNonQuery();
}
_trans.Commit();
Con.Close();
return(Task.FromResult("Saved Success"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public Task <string> UpdateRequsitionUse(List <InvStockRequisitionModel> aModel)
{
try
{
string ReqNo = aModel.ElementAt(0).ReqNo;
Con.Open();
Thread.Sleep(5);
_trans = Con.BeginTransaction();
//string invoiceNo = GetAutoIncrementNumberFromStoreProcedure(14, _trans);
// const string query = @"INSERT INTO tbl_INVSTOCK_REQUISITION (ReqNo,ReqBy,ReqDate,ReqTime,UserName,BranchId,UserDtls,ReqNote,DeptId,Status)
// OUTPUT INSERTED.ID VALUES (@ReqNo,@ReqBy,@ReqDate,@ReqTime,@UserName,@BranchId,@UserDtls,@ReqNote,@DeptId,@Status)";
string query = @"UPDATE tbl_INVSTOCK_REQUISITION SET ReqBy=@ReqBy,ReqDate=@ReqDate,ReqTime=@ReqTime,UserName=@UserName,BranchId=@BranchId,UserDtls=@UserDtls,ReqNote=@ReqNote,DeptId=@DeptId,Status=@Status OUTPUT INSERTED.ID where ReqNo= " + ReqNo + " ";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ReqNo", ReqNo);
cmd.Parameters.AddWithValue("@ReqBy", aModel.ElementAt(0).ReqBy);
cmd.Parameters.AddWithValue("@ReqDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ReqTime", DateTime.Now.ToShortTimeString());
cmd.Parameters.AddWithValue("@UserName", aModel.ElementAt(0).UserName);
cmd.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(aModel.ElementAt(0).UserName, _trans));
cmd.Parameters.AddWithValue("@UserDtls", aModel.ElementAt(0).UserDtls);
cmd.Parameters.AddWithValue("@ReqNote", aModel.ElementAt(0).ReqNote);
cmd.Parameters.AddWithValue("@DeptId", aModel.ElementAt(0).DeptId);
cmd.Parameters.AddWithValue("@Status", 5);
var invMasterId = (int)cmd.ExecuteScalar();
string query1 = @"DELETE FROM tbl_INVSTOCK_REQUISITION_DETAIL Where MasterId=" + invMasterId + "";
var cmd1 = new SqlCommand(query1, Con, _trans);
cmd1.ExecuteNonQuery();
aModel.ForEach(z => z.MasterId = invMasterId);
DataTable dt = ConvertListDataTable(aModel);
var objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_INVSTOCK_REQUISITION_DETAIL"
};
objbulk.ColumnMappings.Add("MasterId", "MasterId"); // model----table
objbulk.ColumnMappings.Add("ProductId", "ProductId");
objbulk.ColumnMappings.Add("Unit", "Unite");
objbulk.ColumnMappings.Add("ReqQty", "ReqQty");
objbulk.WriteToServer(dt);
_trans.Commit();
Con.Close();
return(Task.FromResult("Save Successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public void DevolverDocumentoPedido(LiberacaoDocumento p)
{
AbrirConexao();
SqlCommand cmd1 = new SqlCommand();
SqlCommand cmd2 = new SqlCommand();
SqlCommand cmd3 = new SqlCommand();
SqlTransaction transaction = Con.BeginTransaction();
cmd1 = new SqlCommand("delete from LIBERACAO_DO_DOCUMENTO where CD_DOCUMENTO = @v1;", Con, transaction);
cmd1.Parameters.AddWithValue("@v1", p.CodigoDocumento);
cmd2 = new SqlCommand("update DOCUMENTO set CD_SITUACAO = 136 Where CD_DOCUMENTO = @v1;", Con, transaction);
cmd2.Parameters.AddWithValue("@v1", p.CodigoDocumento);
cmd3 = new SqlCommand("insert into EVENTO_DO_DOCUMENTO(CD_DOCUMENTO,CD_EVENTO,CD_SITUACAO,DT_HR_EVENTO,CD_MAQUINA,CD_USUARIO) " +
"values(@v1,(select max(CD_EVENTO) + 1 from EVENTO_DO_DOCUMENTO where CD_DOCUMENTO = @v1),136,@v3,@v4,@v5)", Con, transaction);
cmd3.Parameters.AddWithValue("@v1", p.CodigoDocumento);
cmd3.Parameters.AddWithValue("@v3", p.DataLiberacao);
cmd3.Parameters.AddWithValue("@v4", p.CodigoMaquina);
cmd3.Parameters.AddWithValue("@v5", p.CodigoUsuario);
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
if (ex.Errors.Count > 0)
{
switch (ex.Errors[0].Number)
{
case 547:
throw new InvalidOperationException("Devolução do Documento não permitida!!! Existe Relacionamentos Obrigatórios com a Tabela. Mensagem :" + ex.Message.ToString(), ex);
default:
throw new Exception("Erro ao devolver Documento: " + ex.Message.ToString());
}
}
}
catch (Exception ex)
{
throw new Exception("Erro ao devolver Documento: " + ex.Message.ToString());
}
finally
{
FecharConexao();
}
}
internal Task <string> Save(List <LabParameterModel> mLab)
{
try
{
Con.Open();
_trans = Con.BeginTransaction();
if (FncSeekRecordNew("tbl_LAB_RESULT", "InvmasterId=" + mLab.ElementAt(0).InvMasterId + " AND ItemId=" + mLab.ElementAt(0).ItemId + "", _trans))
{
DeleteInsert("DELETE FROM tbl_LAB_RESULT WHERE InvmasterId=" + mLab.ElementAt(0).InvMasterId + " AND ItemId=" + mLab.ElementAt(0).ItemId + "", _trans);
}
DataTable dt = ConvertListDataTable(mLab);
var objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_LAB_RESULT"
};
objbulk.ColumnMappings.Add("InvMasterId", "InvmasterId");
objbulk.ColumnMappings.Add("ItemId", "ItemId");
objbulk.ColumnMappings.Add("Specimen", "Specimen");
objbulk.ColumnMappings.Add("AliasName", "AliasName");
objbulk.ColumnMappings.Add("ParameterName", "ParameterName");
objbulk.ColumnMappings.Add("Result", "Result");
objbulk.ColumnMappings.Add("Unit", "Unit");
objbulk.ColumnMappings.Add("NormalValue", "NormalValue");
objbulk.ColumnMappings.Add("GroupName", "GroupName");
objbulk.ColumnMappings.Add("GroupSlNo", "GroupSlNo");
objbulk.ColumnMappings.Add("ItemSlNo", "ItemSlNo");
objbulk.ColumnMappings.Add("ReportDrId", "ReportDrId");
objbulk.ColumnMappings.Add("LabInchargeId", "LabInchargeId");
objbulk.ColumnMappings.Add("CheckedById", "CheckedById");
objbulk.ColumnMappings.Add("ReportDrDetails", "ReportDrDetails");
objbulk.ColumnMappings.Add("LabInchargeDetails", "LabInchargeDetails");
objbulk.ColumnMappings.Add("CheckedByDetails", "CheckedByDetails");
objbulk.WriteToServer(dt);
_trans.Commit();
Con.Close();
return(Task.FromResult <string>("Saved Success"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
_trans.Rollback();
}
return(Task.FromResult(exception.Message));
}
}
public Task <string> Save(LoanAndAdvanceModel aModel)
{
try
{
string query = @"INSERT INTO tbl_HR_LOAN_LEDGER (EmpId,TrNo,TrDate,MonthId,AttYear,LoanTypeId,LoanNo,LoanAmt,InstallmentSize,PaidAmount,ExcemptAmount,LoanAndAdvance,
LoanStatus,UserName,EntryDate,EntryTime) VALUES (@EmpId,@TrNo,@TrDate,@MonthId,@AttYear,@LoanTypeId,@LoanNo,@LoanAmt,@InstallmentSize,0,0,
@LoanAndAdvance,'take','NotDevelopment',@EntryDate,'') ";
Con.Open();
_trans = Con.BeginTransaction();
string trNo = GetTrNo("TrNo", "tbl_HR_LOAN_LEDGER", _trans);
DateTime today = DateTime.Today;
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@EmpId", aModel.EmployeeId);
cmd.Parameters.AddWithValue("@TrNo", trNo);
cmd.Parameters.AddWithValue("@TrDate", today.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@MonthId", today.ToString("MM"));
cmd.Parameters.AddWithValue("@AttYear", today.ToString("yyyy"));
cmd.Parameters.AddWithValue("@LoanTypeId", aModel.LoanType);
cmd.Parameters.AddWithValue("@LoanNo", aModel.LoanId);
cmd.Parameters.AddWithValue("@LoanAmt", aModel.LoanAmound);
cmd.Parameters.AddWithValue("@InstallmentSize", aModel.InstallmentAmound);
cmd.Parameters.AddWithValue("@LoanAndAdvance", aModel.LoanAndAdvance);
//cmd.Parameters.AddWithValue("@LoanStatus", aModel.SequentialStatuss);
//cmd.Parameters.AddWithValue("@ProjectId", aModel.ProjectId);
cmd.Parameters.AddWithValue("@EntryDate", today.ToString("yyyy-MM-dd"));
//cmd.Parameters.AddWithValue("@EntryTime", today.ToLongTimeString());
cmd.ExecuteNonQuery();
string query2 = @"update tbl_HR_GLO_EMPLOYEE_DLS SET Amount = @InstallmentAmound where EmpId = @EmpId AND SalaryTypeId = @LoanTypeId";
var cmd2 = new SqlCommand(query2, Con, _trans);
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@InstallmentAmound", aModel.InstallmentAmound);
cmd2.Parameters.AddWithValue("@EmpId", aModel.EmployeeId);
cmd2.Parameters.AddWithValue("@LoanTypeId", aModel.LoanType);
cmd2.ExecuteNonQuery();
_trans.Commit();
Con.Close();
return(Task.FromResult <string>("Save uccess"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
/// <summary>
/// Metodo para atualizar dados de clientes na base de dados
/// </summary>
/// <param name="c">Entidade Cliente</param>
public void Update(Cliente c)
{
try
{
OpenConnection();
Tr = Con.BeginTransaction();
string queryCliente = "Update Cliente " +
"set " +
"Nome = @Nome, " +
"Rg = @Rg, " +
"Cpf = @Cpf " +
"where " +
"IdCliente = @IdCliente";
Cmd = new SqlCommand(queryCliente, Con, Tr);
Cmd.Parameters.AddWithValue("@Nome", c.Nome);
Cmd.Parameters.AddWithValue("@Rg", c.Rg);
Cmd.Parameters.AddWithValue("@Cpf", c.Cpf);
Cmd.Parameters.AddWithValue("@IdCliente", c.IdCliente);
Cmd.ExecuteNonQuery();
string queryEndereco = "Update Endereco " +
"set " +
"Descricao = @Descricao, " +
"Bairro = @Bairro, " +
"Cidade = @Cidade, " +
"Estado = @Estado, " +
"Cep = @Cep " +
"where " +
"IdCliente = @IdCliente";
Cmd = new SqlCommand(queryEndereco, Con, Tr);
Cmd.Parameters.AddWithValue("@Descricao", c.Endereco.Descricao);
Cmd.Parameters.AddWithValue("@Bairro", c.Endereco.Bairro);
Cmd.Parameters.AddWithValue("@Cidade", c.Endereco.Cidade);
Cmd.Parameters.AddWithValue("@Estado", c.Endereco.Estado);
Cmd.Parameters.AddWithValue("@Cep", c.Endereco.Cep);
Cmd.Parameters.AddWithValue("@IdCliente", c.IdCliente);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
throw new Exception("Erro ao atualizar cliente. \n" + ex.Message);
}
finally
{
CloseConnection();
}
}
public Task <string> Save(List <PharPurchaseModel> aModel)
{
try
{
Con.Open();
Thread.Sleep(5);
_trans = Con.BeginTransaction();
string invNo = GetAutoIncrementNumberFromStoreProcedure(8, _trans);
const string query = @"INSERT INTO tbl_PHAR_COMPANY_REQUISITION (ReqNo,ReqDate,CompanyId,UserName,BranchId)
OUTPUT INSERTED.ID VALUES (@ReqNo,@ReqDate,@CompanyId,@UserName,@BranchId)";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ReqNo", invNo);
cmd.Parameters.AddWithValue("@ReqDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@CompanyId", aModel.ElementAt(0).CompanyId);
cmd.Parameters.AddWithValue("@UserName", aModel.ElementAt(0).UserName);
cmd.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(aModel.ElementAt(0).UserName, _trans));
var invMasterId = (int)cmd.ExecuteScalar();
aModel.ForEach(z => z.InvoiceMasterId = invMasterId);
aModel.ForEach(z => z.InvoiceNo = invNo);
aModel.ForEach(z => z.EntryDate = DateTime.Now.Date);
aModel.ForEach(z => z.InvoiceDate = DateTime.Now.Date);
aModel.ForEach(z => z.EntryTime = DateTime.Now.ToShortTimeString());
aModel.ForEach(z => z.BranchId = Convert.ToInt32(GetBranchIdByuserNameOpenCon(aModel.ElementAt(0).UserName, _trans)));
DataTable dt = ConvertListDataTable(aModel);
var objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_PHAR_COMPANY_REQUISITION_DETAIL"
};
objbulk.ColumnMappings.Add("InvoiceMasterId", "MasterId");
objbulk.ColumnMappings.Add("ItemId", "ProductId");
objbulk.ColumnMappings.Add("ReqQty", "ReqQty");
objbulk.ColumnMappings.Add("BalQty", "BalQty");
objbulk.ColumnMappings.Add("Tp", "Tp");
objbulk.WriteToServer(dt);
_trans.Commit();
Con.Close();
return(Task.FromResult("Save successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public Task <string> Update(List <InvStockRequisitionModel> aModel)
{
try
{
Con.Open();
Thread.Sleep(5);
_trans = Con.BeginTransaction();
const string query = @"UPDATE tbl_INVSTOCK_REQUISITION SET
Allocationby=@Allocationby,AllocationDate=@AllocationDate,AllocationTime=@AllocationTime,UserName=@UserName,UserDtls=@UserDtls,AllocationNote=@AllocationNote,Status=@Status
WHERE ReqNo=@ReqNo ";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ReqNo", aModel.ElementAt(0).ReqNo);
cmd.Parameters.AddWithValue("@Allocationby", aModel.ElementAt(0).UserName);
cmd.Parameters.AddWithValue("@AllocationDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@AllocationTime", DateTime.Now.ToShortTimeString());
cmd.Parameters.AddWithValue("@UserName", aModel.ElementAt(0).UserName);
// cmd.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(aModel.ElementAt(0).UserName, _trans));
cmd.Parameters.AddWithValue("@UserDtls", aModel.ElementAt(0).UserDtls);
cmd.Parameters.AddWithValue("@AllocationNote", aModel.ElementAt(0).AllocationNote);
cmd.Parameters.AddWithValue("@Status", 2);
cmd.ExecuteNonQuery();
for (int i = 0; i < aModel.Count; i++)
{
const string lcQuery = @"UPDATE tbl_INVSTOCK_REQUISITION_DETAIL SET AllocationQty=@AllocationQty WHERE MasterId=@MasterId AND ProductId=@ProductId";
var cmd2 = new SqlCommand(lcQuery, Con, _trans);
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@MasterId", aModel.ElementAt(i).MasterId);
cmd2.Parameters.AddWithValue("@ProductId", aModel.ElementAt(i).ProductId);
cmd2.Parameters.AddWithValue("@AllocationQty", aModel.ElementAt(i).AllocationQty);
cmd2.ExecuteNonQuery();
}
_trans.Commit();
Con.Close();
return(Task.FromResult("Save Successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
//Método para cadastrar Cliente e Endereco
public void Salvar(Cliente c)
{
try
{
AbrirConexao();
Tr = Con.BeginTransaction(); //iniciando uma transação
//gravando os dados do Cliente
Cmd = new SqlCommand("insert into Cliente(Nome, Email, DataCadastro) values(@v1, @v2, @v3)", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", c.Nome);
Cmd.Parameters.AddWithValue("@v2", c.Email);
Cmd.Parameters.AddWithValue("@v3", c.DataCadastro);
Cmd.ExecuteNonQuery(); //executando
//resgatar o id do cliente que foi cadastrado
Cmd = new SqlCommand("select ident_current('Cliente') as Codigo", Con, Tr);
Dr = Cmd.ExecuteReader(); //executa e retorna registros
if (Dr.Read()) //verificando se o DataReader obteve algum registro
{
int Codigo = Convert.ToInt32(Dr["Codigo"]); //leitura do Id do Cliente
Dr.Close(); //fechando o DataReader
//gravando o endereço
Cmd = new SqlCommand("insert into Endereco(Logradouro, Cep, IdCliente) values(@v1, @v2, @v3)", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", c.Residencia.Logradouro);
Cmd.Parameters.AddWithValue("@v2", c.Residencia.Cep);
Cmd.Parameters.AddWithValue("@v3", Codigo); //chave estrangeira
Cmd.ExecuteNonQuery(); //executar
}
else
{
throw new Exception(); //forçar um erro!
}
Tr.Commit(); //executando a transação
}
catch (Exception e)
{
Tr.Rollback(); //desfazer a transação
throw new Exception("Erro ao salvar cliente: " + e.Message);
}
finally
{
FecharConexao();
}
}
public bool deleteBillRecord(int bill_number, string tran_type)
{
bool return_val = false;
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//reset all the bill detail that has this bill_number to be 0
SQL = "update bill_detail set bill_number = 0 WHERE elt_account_number =";
SQL += elt_account_number + " and bill_number ="
+ bill_number;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//delete all teh AAJ entries
SQL = "Delete FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_num = " + bill_number + " AND tran_type = '" + tran_type + "'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
// delete the bill
SQL = "delete from bill where elt_account_number = " + elt_account_number + " and bill_number=" + bill_number;
return_val = true;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
/// <summary>
/// Metodo utilizado para cadastrar cliente na base de dados
/// </summary>
/// <param name="c">Entidade Cliente</param>
public void Inert(Cliente c)
{
try
{
OpenConnection();
Tr = Con.BeginTransaction();
string queryCliente = "Insert into Cliente (Nome, Rg, Cpf, DataCadastro) " +
"values (@v1, @v2, @v3, GetDate()) " +
"select scope_identity()";
Cmd = new SqlCommand(queryCliente, Con, Tr);
Cmd.Parameters.AddWithValue("@v1", c.Nome);
Cmd.Parameters.AddWithValue("@v2", c.Rg);
Cmd.Parameters.AddWithValue("@v3", c.Cpf);
c.IdCliente = Convert.ToInt32(Cmd.ExecuteScalar());
string queryEndereco = "Insert into Endereco (Descricao, Bairro, Cidade, Estado, Cep, IdCliente) " +
"values (@v4, @v5, @v6, @v7, @v8, @v9)";
Cmd = new SqlCommand(queryEndereco, Con, Tr);
Cmd.Parameters.AddWithValue("@v4", c.Endereco.Descricao);
Cmd.Parameters.AddWithValue("@v5", c.Endereco.Bairro);
Cmd.Parameters.AddWithValue("@v6", c.Endereco.Cidade);
Cmd.Parameters.AddWithValue("@v7", c.Endereco.Estado);
Cmd.Parameters.AddWithValue("@v8", c.Endereco.Cep);
Cmd.Parameters.AddWithValue("@v9", c.IdCliente);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
throw new Exception("Erro ao cadastrar cliente na base de dados. \n" + ex.Message);
}
finally
{
CloseConnection();
}
}
public GLRecord createGLAccount(int gl_account_number, string gl_account_desc, string gl_master_type, string gl_account_type)
{
GLRecord gRec = new GLRecord();
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
SQL = "INSERT INTO gl (elt_account_number, gl_account_number, gl_account_desc, gl_master_type, gl_account_type, gl_account_status, gl_account_cdate, gl_last_modified) VALUES ("
+ elt_account_number + ","
+ gl_account_number + ","
+ "'" + gl_account_desc + "'" + ","
+ "'" + gl_master_type + "'" + ","
+ "'" + gl_account_type + "'" + ","
+ "'A'" + ","
+ "'" + DateTime.Today.ToShortDateString() + "'" + ","
+ "'" + DateTime.Today.ToShortDateString() + "'"
+ ")";
try
{
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
gRec.Gl_account_type = gl_account_type;
gRec.Gl_master_type = gl_master_type;
gRec.Gl_account_desc = gl_account_desc;
gRec.Gl_account_number = gl_account_number;
gRec.Gl_account_status = "A";
return(gRec);
}
public void Excluir(int idFormulario, int idPergunta)
{
try
{
AbrirConexao();
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("DELETE FROM Formulario.PerguntasPorFormularios WHERE IdFormulario = " + idFormulario + " and IdPergunta =" + idPergunta, Con, Tr);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception ex)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Não foi possível remover a pergunta: " + ex.Message);
}
}
public Task <string> Update(List <LabParameterModel> mLab)
{
try
{
Con.Open();
_trans = Con.BeginTransaction();
if (FncSeekRecordNew("tbl_LAB_PARAMETER_DEFINITION", "ItemId=" + mLab.ElementAt(0).ItemId + "", _trans))
{
DeleteInsert("DELETE FROM tbl_LAB_PARAMETER_DEFINITION WHERE ItemId=" + mLab.ElementAt(0).ItemId + "", _trans);
}
DataTable dt = ConvertListDataTable(mLab);
var objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_LAB_PARAMETER_DEFINITION"
};
objbulk.ColumnMappings.Add("ItemId", "ItemId");
objbulk.ColumnMappings.Add("Specimen", "Specimen");
objbulk.ColumnMappings.Add("AliasName", "AliasName");
objbulk.ColumnMappings.Add("ParameterName", "ParameterName");
objbulk.ColumnMappings.Add("Result", "Result");
objbulk.ColumnMappings.Add("Unit", "Unit");
objbulk.ColumnMappings.Add("NormalValue", "NormalValue");
objbulk.ColumnMappings.Add("GroupName", "GroupName");
objbulk.ColumnMappings.Add("GroupSlNo", "GroupSlNo");
objbulk.ColumnMappings.Add("ItemSlNo", "ItemSlNo");
objbulk.ColumnMappings.Add("ReportFileName", "ReportFileName");
objbulk.WriteToServer(dt);
_trans.Commit();
Con.Close();
return(Task.FromResult <string>("Update Success"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
_trans.Rollback();
}
return(Task.FromResult(exception.Message));
}
}
public void Excluir(int id)
{
try
{
AbrirConexao();
Tr = Con.BeginTransaction();
Cmd = new SqlCommand("DELETE FROM Formulario.Perguntas WHERE IdPergunta = @v1", Con, Tr);
Cmd.Parameters.AddWithValue("@v1", id);
Cmd.ExecuteNonQuery();
Tr.Commit();
}
catch (Exception)
{
if (Tr != null)
{
Tr.Rollback(); // Desfaz a transação..
}
throw new Exception("Erro: Pergunta pertence a algum formulário!");
}
}
public string Save(AdmissionModel aModel)
{
try
{
const string msg = "";
Con.Open();
_trans = Con.BeginTransaction();
string refNo = "AC" + GetTrNoWithOpenCon("RefNo", "tbl_IN_LEDGER_OF_ADMITTED_PATIENT", _trans);
_insertGateway.InsertLedgerOfAdmittedPatient(refNo, Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.PtIndoorId + "", "RegId", _trans)), aModel.PtIndoorId, 1969, aModel.TotalAmount, 1, 0, 0, 0, aModel.TotalAmount, 0, 0, 0, 0, GetSubSubPnoByIndoorId(aModel.PtIndoorId, _trans), aModel.UserName, Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.PtIndoorId + "", "BedId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.PtIndoorId + "", "RefDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.PtIndoorId + "", "AdmitDrId", _trans)), Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.PtIndoorId + "", "UnderDrId", _trans)), aModel.CashAmount, aModel.CardAmount, aModel.CheaqueAmount, aModel.CardBankId, aModel.CheaqueBankId, aModel.CardNumber, aModel.CheaqueNumber, aModel.Remarks, _trans, Con);
_trans.Commit();
Con.Close();
return("Save uccess");
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
_trans.Rollback();
}
return(exception.Message);
}
}
public bool updateARNRecord(ref ARNRecord arnRec)
{
arnRec.replaceQuote();
//INSERT IV CHARGE ITEMS
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
bool return_val = false;
try
{
SQL = "UPDATE import_hawb ";
SQL += "set elt_account_number='" + elt_account_number + "'";
SQL += ",agent_elt_acct='" + arnRec.agent_elt_acct + "'";
SQL += ",agent_org_acct ='" + arnRec.agent_org_acct + "'";
// SQL += ",arn_no ='" + arnRec.arn_no + "'";
SQL += ",arr_code ='" + arnRec.arr_code + "'";
SQL += ",arr_port ='" + arnRec.arr_port + "'";
SQL += ",broker_acct ='" + arnRec.broker_acct + "'";
SQL += " ,broker_info ='" + arnRec.broker_info + "'";
SQL += " , broker_name ='" + arnRec.broker_name + "'";
SQL += " ,cargo_location ='" + arnRec.cargo_location + "'";
SQL += " ,chg_wt ='" + arnRec.chg_wt + "'";
SQL += ", consignee_acct ='" + arnRec.consignee_acct + "'";
SQL += " , consignee_info ='" + arnRec.consignee_info + "'";
SQL += " ,consignee_name ='" + arnRec.consignee_name + "'";
SQL += " ,container_location ='" + arnRec.container_location + "'";
SQL += " ,CreatedBy ='" + arnRec.CreatedBy + "'";
SQL += " ,CreatedDate ='" + arnRec.CreatedDate + "'";
SQL += " ,customer_ref ='" + arnRec.customer_ref + "'";
SQL += " , delivery_place ='" + arnRec.delivery_place + "'";
SQL += " ,dep_code ='" + arnRec.dep_code + "'";
SQL += " , dep_port ='" + arnRec.dep_port + "'";
SQL += " ,desc1 ='" + arnRec.desc1 + "'";
SQL += " ,desc2 ='" + arnRec.desc2 + "'";
SQL += " ,desc3 ='" + arnRec.desc3 + "'";
SQL += " ,desc4 ='" + arnRec.desc4 + "'";
SQL += " ,desc5 ='" + arnRec.desc5 + "'";
SQL += " ,destination ='" + arnRec.destination + "'";
SQL += " ,eta ='" + arnRec.eta + "'";
SQL += " ,eta2 ='" + arnRec.eta2 + "'";
SQL += " ,etd ='" + arnRec.etd + "'";
SQL += " ,etd2 ='" + arnRec.etd2 + "'";
SQL += ", fc_charge ='" + arnRec.fc_charge + "'";
SQL += ",fc_rate ='" + arnRec.fc_rate + "'";
SQL += " ,flt_no ='" + arnRec.flt_no + "'";
SQL += " ,free_date ='" + arnRec.free_date + "'";
SQL += " ,freight_collect ='" + arnRec.freight_collect + "'";
SQL += " ,go_date ='" + arnRec.go_date + "'";
SQL += " ,gross_wt ='" + arnRec.gross_wt + "'";
SQL += " ,hawb_num ='" + arnRec.hawb_num + "'";
SQL += " ,igSub_HAWB ='" + arnRec.igSub_HAWB + "'";
SQL += " ,invoice_no ='" + arnRec.invoice_no + "'";
SQL += " , is_default_rate ='" + arnRec.is_default_rate + "'";
SQL += " ,is_org_merged ='" + arnRec.is_org_merged + "'";
SQL += " ,it_date ='" + arnRec.it_date + "'";
SQL += " , it_entry_port ='" + arnRec.it_entry_port + "'";
SQL += " ,it_number ='" + arnRec.it_number + "'";
SQL += " ,iType ='" + arnRec.iType + "'";
SQL += " ,mawb_num ='" + arnRec.mawb_num + "'";
SQL += " , ModifiedBy ='" + arnRec.ModifiedBy + "'";
SQL += " ,ModifiedDate ='" + arnRec.ModifiedDate + "'";
SQL += " ,notify_acct ='" + arnRec.notify_acct + "'";
SQL += " ,notify_info ='" + arnRec.notify_info + "'";
SQL += " , notify_name ='" + arnRec.notify_name + "'";
SQL += ",oc_collect ='" + arnRec.oc_collect + "'";
SQL += ", pickup_date ='" + arnRec.pickup_date + "'";
SQL += ",pieces ='" + arnRec.pieces + "'";
SQL += " ,prepaid_collect ='" + arnRec.prepaid_collect + "'";
SQL += ",prepared_by ='" + arnRec.prepared_by + "'";
SQL += ",process_dt ='" + arnRec.process_dt + "'";
SQL += " ,processed ='" + arnRec.processed + "'";
SQL += " ,remarks ='" + arnRec.remarks + "'";
SQL += " ,SalesPerson ='" + arnRec.SalesPerson + "'";
SQL += ",scale1 ='" + arnRec.scale1 + "'";
SQL += " ,scale2 ='" + arnRec.scale2 + "'";
SQL += " ,sec ='" + arnRec.sec + "'";
SQL += " ,shipper_acct ='" + arnRec.shipper_acct + "'";
SQL += " ,shipper_info ='" + arnRec.shipper_info + "'";
SQL += " ,shipper_name ='" + arnRec.shipper_name + "'";
SQL += " ,sub_mawb1 ='" + arnRec.sub_mawb1 + "'";
SQL += ",sub_mawb2 ='" + arnRec.sub_mawb2 + "'";
SQL += " ,term ='" + arnRec.term + "'";
SQL += ",total_other_charge ='" + arnRec.total_other_charge + "'";
SQL += ",tran_dt ='" + arnRec.tran_dt + "'";
SQL += ",uom = '" + arnRec.uom + "'";
SQL += " WHERE elt_account_number =" + elt_account_number;
SQL += " and invoice_no =" + arnRec.invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
internal Task <string> Save(List <PatientReleaseModel> aModel)
{
try
{
Con.Open();
_trans = Con.BeginTransaction();
int refDrId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.ElementAt(0).PtIndoorId + "", "RefDrId", _trans));
int underDrId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.ElementAt(0).PtIndoorId + "", "UnderDrId", _trans));
int admitDrId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.ElementAt(0).PtIndoorId + "", "AdmitDrId", _trans));
int bedId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.ElementAt(0).PtIndoorId + "", "BedId", _trans));
int regId = Convert.ToInt32(ReturnFieldValueOpenCon("tbl_IN_PATIENT_ADMISSION", "Id=" + aModel.ElementAt(0).PtIndoorId + "", "RegId", _trans));
string refNo = "PR" + GetTrNoWithOpenCon("ReceiptNo", "tbl_IN_PATIENT_RELEASE_MASTER", _trans);
const string query = @"INSERT INTO tbl_IN_PATIENT_RELEASE_MASTER(ReceiptNo, ReleaseDate,RegId, IndoorId, TotalDays, AdvanceAmt, TotalAmt, LessAmt, CollAmt, ServiceChargeAmt, ReturnAmt, BedId, RefDrId, UnderDrId, PatientStatus, CorporateId, PackId, CashAmt, CardAmt, ChequeAmt, CardBankId, ChequeBankId, CardNumber, ChequeNumber, UserName, BranchId) OUTPUT INSERTED.ID VALUES (@ReceiptNo, @ReleaseDate,@RegId, @IndoorId, @TotalDays, @AdvanceAmt, @TotalAmt, @LessAmt, @CollAmt, @ServiceChargeAmt, @ReturnAmt, @BedId, @RefDrId, @UnderDrId, @PatientStatus, @CorporateId, @PackId, @CashAmt, @CardAmt, @ChequeAmt, @CardBankId, @ChequeBankId, @CardNumber, @ChequeNumber, @UserName, @BranchId)";
var cmd = new SqlCommand(query, Con, _trans);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ReceiptNo", refNo);
cmd.Parameters.AddWithValue("@ReleaseDate", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@IndoorId", aModel.ElementAt(0).PtIndoorId);
cmd.Parameters.AddWithValue("@RegId", regId);
cmd.Parameters.AddWithValue("@TotalDays", aModel.ElementAt(0).TotalDays);
cmd.Parameters.AddWithValue("@AdvanceAmt", aModel.ElementAt(0).AdvanceAmt);
cmd.Parameters.AddWithValue("@TotalAmt", aModel.ElementAt(0).TotalAmount);
cmd.Parameters.AddWithValue("@LessAmt", aModel.ElementAt(0).LessAmount);
cmd.Parameters.AddWithValue("@CollAmt", aModel.ElementAt(0).CollAmt);
cmd.Parameters.AddWithValue("@ServiceChargeAmt", aModel.ElementAt(0).TotalServiceCharge);
cmd.Parameters.AddWithValue("@ReturnAmt", aModel.ElementAt(0).ReturnAmount);
cmd.Parameters.AddWithValue("@BedId", aModel.ElementAt(0).BedId);
cmd.Parameters.AddWithValue("@RefDrId", refDrId);
cmd.Parameters.AddWithValue("@UnderDrId", underDrId);
cmd.Parameters.AddWithValue("@PatientStatus", aModel.ElementAt(0).PatientStatus);
cmd.Parameters.AddWithValue("@CorporateId", aModel.ElementAt(0).CorporateId);
cmd.Parameters.AddWithValue("@PackId", aModel.ElementAt(0).PackageId);
cmd.Parameters.AddWithValue("@CashAmt", aModel.ElementAt(0).CashAmount);
cmd.Parameters.AddWithValue("@CardAmt", aModel.ElementAt(0).CardAmount);
cmd.Parameters.AddWithValue("@ChequeAmt", aModel.ElementAt(0).CheaqueAmount);
cmd.Parameters.AddWithValue("@CardBankId", aModel.ElementAt(0).CardBankId);
cmd.Parameters.AddWithValue("@ChequeBankId", aModel.ElementAt(0).CheaqueBankId);
cmd.Parameters.AddWithValue("@CardNumber", aModel.ElementAt(0).CardNumber);
cmd.Parameters.AddWithValue("@ChequeNumber", aModel.ElementAt(0).CheaqueNumber);
cmd.Parameters.AddWithValue("@UserName", aModel.ElementAt(0).UserName);
cmd.Parameters.AddWithValue("@BranchId", GetBranchIdByuserNameOpenCon(aModel.ElementAt(0).UserName, _trans));
var masterId = (int)cmd.ExecuteScalar();
_gtLedgerInsertGateway.InsertLedgerOfAdmittedPatient(refNo, regId, aModel.ElementAt(0).PtIndoorId, 85, aModel.ElementAt(0).TotalAmount, aModel.ElementAt(0).Quantity, aModel.ElementAt(0).Vat, aModel.ElementAt(0).ServiceCharge, 0, 0, 0, 0, 0, 0, 82, aModel.ElementAt(0).UserName, bedId, refDrId, admitDrId, underDrId, aModel.ElementAt(0).CashAmount, aModel.ElementAt(0).CardAmount, aModel.ElementAt(0).CheaqueAmount, aModel.ElementAt(0).CardBankId, aModel.ElementAt(0).CheaqueBankId, aModel.ElementAt(0).CardNumber, aModel.ElementAt(0).CheaqueNumber, aModel.ElementAt(0).Remarks, _trans, Con);
aModel.ForEach(z => z.InvMasterId = masterId);
DataTable dt = ConvertListDataTable(aModel);
var objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_IN_PATIENT_RELEASE_DETAIL"
};
objbulk.ColumnMappings.Add("InvMasterId", "ReleaseMasterId");
objbulk.ColumnMappings.Add("ItemId", "ItemId");
objbulk.ColumnMappings.Add("Charge", "Charge");
objbulk.ColumnMappings.Add("Quantity", "Quantity");
objbulk.ColumnMappings.Add("TotalCharge", "TotalCharge");
objbulk.ColumnMappings.Add("Vat", "VatAmt");
objbulk.ColumnMappings.Add("ServiceCharge", "ServiceChargeAmt");
// objbulk.ColumnMappings.Add("ItemwiseLess", "LessAmt");
objbulk.ColumnMappings.Add("ItemTotal", "NetTotalAmt");
objbulk.ColumnMappings.Add("MaxRefFee", "AmtGivenToDr");
objbulk.ColumnMappings.Add("DrId", "DrId");
objbulk.WriteToServer(dt);
foreach (var mdl in aModel)
{
if (mdl.ItemId == 2312)
{
//int invmasterId = Convert.ToInt32(ReturnFieldValueOpenCon("VW_DUE_INVOICE_LIST", "Sales=" + mdl.ItemTotal + "", "InvmasterId",_trans));
var lists = GetDueInvoiceListByIndoorId(mdl.PtIndoorId, _trans);
foreach (var list in lists)
{
if (mdl.ItemTotal > 0)
{
InsertIntoInvoiceLedger(regId, mdl.PtIndoorId, list.InvMasterId, refNo, DateTime.Now, 0, 0, list.DueAmt, 0, GetSubSubPnoByIndoorId(list.PtIndoorId, _trans), "Coll From Release", mdl.UserName, _trans);
mdl.ItemTotal -= list.DueAmt;
}
}
}
}
foreach (var mdl in aModel)
{
if (mdl.DrId != 0)
{
InsertIntoHonoriumLedger(aModel.ElementAt(0).DrId, refNo, DateTime.Now, masterId, mdl.Charge * mdl.Quantity, mdl.Charge * mdl.Quantity, 0, 0, 72, mdl.UserName, _trans);
}
}
DeleteInsert("Update tbl_IN_PATIENT_ADMISSION SET IsRelease=1 WHERE Id='" + aModel[0].PtIndoorId + "'", _trans);
DeleteInsert("Update tbl_IN_BED_INFO SET IsBooked=0 WHERE Id='" + aModel[0].BedId + "'", _trans);
aModel.RemoveAll(r => Math.Abs(r.ItemwiseLess) < 1);
aModel.ForEach(z => z.RefDrId = refDrId);
aModel.ForEach(z => z.UnderDrId = underDrId);
aModel.ForEach(z => z.AdmitDrId = admitDrId);
aModel.ForEach(z => z.BedId = bedId);
aModel.ForEach(z => z.InvoiceNo = refNo);
aModel.ForEach(z => z.InvoiceDate = DateTime.Now);
aModel.ForEach(z => z.SubSubPnoId = GetSubSubPnoByIndoorId(aModel[0].PtIndoorId, _trans));
dt = ConvertListDataTable(aModel);
objbulk = new SqlBulkCopy(Con, SqlBulkCopyOptions.Default, _trans)
{
DestinationTableName = "tbl_IN_LEDGER_OF_ADMITTED_PATIENT"
};
objbulk.ColumnMappings.Add("PtIndoorId", "IndoorId");
objbulk.ColumnMappings.Add("BedId", "BedId");
objbulk.ColumnMappings.Add("InvoiceNo", "RefNo");
objbulk.ColumnMappings.Add("InvoiceDate", "RefDate");
objbulk.ColumnMappings.Add("ItemId", "ItemId");
objbulk.ColumnMappings.Add("ItemwiseLess", "AdjustAmt");
objbulk.ColumnMappings.Add("UserName", "UserName");
objbulk.ColumnMappings.Add("SubSubPnoId", "SubSubPnoId");
objbulk.ColumnMappings.Add("RefDrId", "RefDrId");
objbulk.ColumnMappings.Add("UnderDrId", "UnderDrId");
objbulk.ColumnMappings.Add("AdmitDrId", "AdmitDrId");
objbulk.WriteToServer(dt);
_trans.Commit();
Con.Close();
return(Task.FromResult("Save Successful"));
}
catch (Exception exception)
{
if (Con.State == ConnectionState.Open)
{
_trans.Rollback();
Con.Close();
}
return(Task.FromResult(exception.Message));
}
}
public bool insertARNRecord(ARNRecord arnRec)
{
arnRec.replaceQuote();
//INSERT IV CHARGE ITEMS
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
bool return_val = false;
try
{
//INSERT INVOICE RECORD
SQL = "INSERT INTO [import_hawb] ";
SQL += "( elt_account_number, ";
SQL += "agent_elt_acct,";
SQL += " agent_org_acct ,";
// SQL += " arn_no ,";
SQL += " arr_code ,";
SQL += " arr_port ,";
SQL += " broker_acct ,";
SQL += " broker_info ,";
SQL += " broker_name ,";
SQL += " cargo_location ,";
SQL += " chg_wt ,";
SQL += " consignee_acct ,";
SQL += " consignee_info ,";
SQL += " consignee_name ,";
SQL += " container_location ,";
SQL += " CreatedBy ,";
SQL += " CreatedDate ,";
SQL += " customer_ref ,";
SQL += " delivery_place ,";
SQL += " dep_code ,";
SQL += " dep_port ,";
SQL += " desc1 ,";
SQL += " desc2 ,";
SQL += " desc3 ,";
SQL += " desc4 ,";
SQL += " desc5 ,";
SQL += " destination ,";
SQL += " eta ,";
SQL += " eta2 ,";
SQL += " etd ,";
SQL += " etd2 ,";
SQL += " fc_charge ,";
SQL += "fc_rate ,";
SQL += " flt_no ,";
SQL += " free_date ,";
SQL += " freight_collect ,";
SQL += " go_date ,";
SQL += " gross_wt ,";
SQL += " hawb_num ,";
SQL += " igSub_HAWB ,";
SQL += " invoice_no ,";
SQL += " is_default_rate ,";
SQL += " is_org_merged ,";
SQL += " it_date ,";
SQL += " it_entry_port ,";
SQL += " it_number ,";
SQL += " iType ,";
SQL += " mawb_num ,";
SQL += " ModifiedBy ,";
SQL += " ModifiedDate ,";
SQL += " notify_acct ,";
SQL += " notify_info ,";
SQL += " notify_name ,";
SQL += "oc_collect ,";
SQL += " pickup_date ,";
SQL += "pieces ,";
SQL += " prepaid_collect ,";
SQL += "prepared_by ,";
SQL += "process_dt ,";
SQL += " processed ,";
SQL += " remarks ,";
SQL += " SalesPerson ,";
SQL += "scale1 ,";
SQL += " scale2 ,";
SQL += " sec ,";
SQL += " shipper_acct ,";
SQL += " shipper_info ,";
SQL += " shipper_name ,";
SQL += " sub_mawb1 ,";
SQL += "sub_mawb2 ,";
SQL += " term ,";
SQL += "total_other_charge ,";
SQL += "tran_dt ,";
SQL += "uom ) ";
SQL += "VALUES";
SQL += "('" + base.elt_account_number;
SQL += "','" + arnRec.agent_elt_acct;
SQL += "','" + arnRec.agent_org_acct;
//SQL += "','" + arnRec.arn_no;
SQL += "','" + arnRec.arr_code;
SQL += "','" + arnRec.arr_port;
SQL += "','" + arnRec.broker_acct;
SQL += "','" + arnRec.broker_info;
SQL += "','" + arnRec.broker_name;
SQL += "','" + arnRec.cargo_location;
SQL += "','" + arnRec.chg_wt;
SQL += "','" + arnRec.consignee_acct;
SQL += "','" + arnRec.consignee_info;
SQL += "','" + arnRec.consignee_name;
SQL += "','" + arnRec.container_location;
SQL += "','" + arnRec.CreatedBy;
SQL += "','" + arnRec.CreatedDate; //
SQL += "','" + arnRec.customer_ref;
SQL += "','" + arnRec.delivery_place;
SQL += "','" + arnRec.dep_code;
SQL += "','" + arnRec.dep_port;
SQL += "','" + arnRec.desc1;
SQL += "','" + arnRec.desc2;
SQL += "','" + arnRec.desc3;
SQL += "','" + arnRec.desc4;
SQL += "','" + arnRec.desc5;
SQL += "','" + arnRec.destination;
SQL += "','" + arnRec.eta;
SQL += "','" + arnRec.eta2;
SQL += "','" + arnRec.etd;
SQL += "','" + arnRec.etd2;
SQL += "','" + arnRec.fc_charge;
SQL += "','" + arnRec.fc_rate;
SQL += "','" + arnRec.flt_no;
SQL += "','" + arnRec.free_date; //
SQL += "','" + arnRec.freight_collect;
SQL += "','" + arnRec.go_date; //
SQL += "','" + arnRec.gross_wt;
SQL += "','" + arnRec.hawb_num;
SQL += "','" + arnRec.igSub_HAWB;
SQL += "','" + arnRec.invoice_no;
SQL += "','" + arnRec.is_default_rate;
SQL += "','" + arnRec.is_org_merged;
SQL += "','" + arnRec.it_date; //
SQL += "','" + arnRec.it_entry_port;
SQL += "','" + arnRec.it_number;
SQL += "','" + arnRec.iType;
SQL += "','" + arnRec.mawb_num;
SQL += "','" + arnRec.ModifiedBy;
SQL += "','" + arnRec.ModifiedDate;
SQL += "','" + arnRec.notify_acct;
SQL += "','" + arnRec.notify_info;
SQL += "','" + arnRec.notify_name;
SQL += "','" + arnRec.oc_collect;
SQL += "','" + arnRec.pickup_date;//
SQL += "','" + arnRec.pieces;
SQL += "','" + arnRec.prepaid_collect;
SQL += "','" + arnRec.prepared_by;
SQL += "','" + arnRec.process_dt;
SQL += "','" + arnRec.processed;
SQL += "','" + arnRec.remarks;
SQL += "','" + arnRec.SalesPerson;
SQL += "','" + arnRec.scale1;
SQL += "','" + arnRec.scale2;
SQL += "','" + arnRec.sec;
SQL += "','" + arnRec.shipper_acct;
SQL += "','" + arnRec.shipper_info;
SQL += "','" + arnRec.shipper_name;
SQL += "','" + arnRec.sub_mawb1;
SQL += "','" + arnRec.sub_mawb2;
SQL += "','" + arnRec.term;
SQL += "','" + arnRec.total_other_charge;
SQL += "','" + arnRec.tran_dt;
SQL += "','" + arnRec.uom;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}