public async Task <Po> GetPoByAccessIdAsync(string poId)
{
var po = new Po();
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
var commandText = string.Format($"SELECT PM.[Id] ,CUS.Name AS CompanyName, PM.[CompanyId] ,SUP.[Name] AS SupplierName ,SUP.ContactPersonName,SupplierId,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate],[AccessId] FROM [dbo].[PoMaster] PM INNER JOIN supplier SUP ON SUP.ID = PM.SupplierId INNER JOIN customer CUS ON CUS.ID = PM.CompanyId where AccessId = '{poId}' ");
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
po.Id = Convert.ToInt64(dataReader["Id"]);
po.CompanyName = Convert.ToString(dataReader["CompanyName"]);
po.SupplierName = Convert.ToString(dataReader["SupplierName"]);
po.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]);
po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]);
po.PoNo = Convert.ToString(dataReader["PoNo"]);
po.PoDate = Convert.ToDateTime(dataReader["PoDate"]);
po.EmailIds = Convert.ToString(dataReader["EmailIds"]);
po.Remarks = Convert.ToString(dataReader["Remarks"]);
po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]);
//po.IsForceClosed = Convert.ToBoolean(dataReader["IsForceClosed"]);
if (dataReader["ClosingDate"] != DBNull.Value)
{
po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]);
}
else
{
po.ClosingDate = null;
}
if (dataReader["DueDate"] != DBNull.Value)
{
po.DueDate = Convert.ToDateTime(dataReader["DueDate"]);
}
else
{
po.DueDate = null;
}
po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]);
if (dataReader["AcknowledgementDate"] != DBNull.Value)
{
po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]);
}
else
{
po.AcknowledgementDate = null;
}
po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]);
po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]);
po.AccessId = Convert.ToString(dataReader["AccessId"]);
}
dataReader.Close();
conn.Close();
}
List <PoDetail> poDetails = new List <PoDetail>();
commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ,[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate],[SrNo] FROM [dbo].[PoDetails] where poid = '{ po.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var poDetail = new PoDetail();
poDetail.Id = Convert.ToInt64(dataReader1["Id"]);
poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]);
poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]);
poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]);
poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]);
poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]);
poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]);
poDetail.Note = Convert.ToString(dataReader1["Note"]);
poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]);
poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]);
poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]);
poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]);
if (dataReader1["SrNo"] != DBNull.Value)
{
poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]);
}
else
{
poDetail.SrNo = 0;
}
if (dataReader1["ClosingDate"] != DBNull.Value)
{
poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]);
}
else
{
poDetail.ClosingDate = null;
}
poDetails.Add(poDetail);
}
dataReader1.Close();
conn.Close();
}
po.poDetails = poDetails;
List <PoTerm> poTerms = new List <PoTerm>();
commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var poTerm = new PoTerm();
poTerm.Id = Convert.ToInt64(dataReader1["Id"]);
poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]);
poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]);
poTerm.Term = Convert.ToString(dataReader1["Term"]);
poTerms.Add(poTerm);
}
dataReader1.Close();
conn.Close();
}
po.poTerms = poTerms;
return(po);
}
public async Task <Po> GetPoAsync(long poId, SqlConnection conn, SqlTransaction transaction)
{
var po = new Po();
var commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where Id = '{poId}' ");
using (SqlCommand cmd = new SqlCommand(commandText, conn, transaction))
{
cmd.CommandType = CommandType.Text;
var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.Default);
while (dataReader.Read())
{
po.Id = Convert.ToInt64(dataReader["Id"]);
po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]);
po.PoNo = Convert.ToString(dataReader["PoNo"]);
po.PoDate = Convert.ToDateTime(dataReader["PoDate"]);
po.EmailIds = Convert.ToString(dataReader["EmailIds"]);
po.Remarks = Convert.ToString(dataReader["Remarks"]);
po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]);
if (dataReader["ClosingDate"] != DBNull.Value)
{
po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]);
}
else
{
po.ClosingDate = null;
}
if (dataReader["DueDate"] != DBNull.Value)
{
po.DueDate = Convert.ToDateTime(dataReader["DueDate"]);
}
else
{
po.DueDate = null;
}
po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]);
if (dataReader["AcknowledgementDate"] != DBNull.Value)
{
po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]);
}
else
{
po.AcknowledgementDate = null;
}
po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]);
po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]);
}
dataReader.Close();
}
List <PoDetail> poDetails = new List <PoDetail>();
commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ,[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate],[SrNo],[IsForceClosed] FROM [dbo].[PoDetails] where poid = '{ po.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn, transaction))
{
cmd1.CommandType = CommandType.Text;
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.Default);
while (dataReader1.Read())
{
var poDetail = new PoDetail();
poDetail.Id = Convert.ToInt64(dataReader1["Id"]);
poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]);
poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]);
poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]);
poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]);
poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]);
poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]);
poDetail.Note = Convert.ToString(dataReader1["Note"]);
poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]);
poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]);
poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]);
poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]);
poDetail.IsForceClosed = Convert.ToBoolean(dataReader1["IsForceClosed"]);
if (dataReader1["SrNo"] != DBNull.Value)
{
poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]);
}
else
{
poDetail.SrNo = 0;
}
if (dataReader1["ClosingDate"] != DBNull.Value)
{
poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]);
}
else
{
poDetail.ClosingDate = null;
}
poDetails.Add(poDetail);
}
dataReader1.Close();
}
po.poDetails = poDetails;
List <PoTerm> poTerms = new List <PoTerm>();
commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn, transaction))
{
cmd1.CommandType = CommandType.Text;
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.Default);
while (dataReader1.Read())
{
var poTerm = new PoTerm();
poTerm.Id = Convert.ToInt64(dataReader1["Id"]);
poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]);
poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]);
poTerm.Term = Convert.ToString(dataReader1["Term"]);
poTerms.Add(poTerm);
}
dataReader1.Close();
}
po.poTerms = poTerms;
return(po);
}
public async Task <IEnumerable <Po> > GetAllPosAsync(int companyId, int userId)
{
List <Po> pos = new List <Po>();
var userInfo = await userRepository.GeUserbyIdAsync(userId);
var commandText = "";
if (userInfo.UserTypeId == 1)
{
commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where CompanyId = '{companyId}' ");
}
if (userInfo.UserTypeId == 2)
{
return(pos);
}
if (userInfo.UserTypeId == 3)
{
string companylist = string.Join(",", userInfo.CompanyIds);
commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where CompanyId = '{companyId}' and [SupplierId] in ({companylist})");
}
SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
var po = new Po();
po.Id = Convert.ToInt64(dataReader["Id"]);
po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]);
po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]);
po.PoNo = Convert.ToString(dataReader["PoNo"]);
po.PoDate = Convert.ToDateTime(dataReader["PoDate"]);
po.EmailIds = Convert.ToString(dataReader["EmailIds"]);
po.Remarks = Convert.ToString(dataReader["Remarks"]);
po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]);
if (dataReader["ClosingDate"] != DBNull.Value)
{
po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]);
}
else
{
po.ClosingDate = null;
}
if (dataReader["DueDate"] != DBNull.Value)
{
po.DueDate = Convert.ToDateTime(dataReader["DueDate"]);
}
else
{
po.DueDate = null;
}
po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]);
if (dataReader["AcknowledgementDate"] != DBNull.Value)
{
po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]);
}
else
{
po.AcknowledgementDate = null;
}
po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]);
po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]);
pos.Add(po);
}
dataReader.Close();
conn.Close();
}
foreach (Po po in pos)
{
List <PoDetail> poDetails = new List <PoDetail>();
commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ," +
$"[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate] , [SrNo],[IsForceClosed] FROM [dbo].[PoDetails] where poid = '{ po.Id}'");
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var poDetail = new PoDetail();
poDetail.Id = Convert.ToInt64(dataReader1["Id"]);
poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]);
poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]);
poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]);
poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]);
poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]);
poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]);
poDetail.Note = Convert.ToString(dataReader1["Note"]);
poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]);
poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]);
poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]);
poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]);
poDetail.IsForceClosed = Convert.ToBoolean(dataReader1["IsForceClosed"]);
if (dataReader1["ClosingDate"] != DBNull.Value)
{
poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]);
}
else
{
poDetail.ClosingDate = null;
}
if (dataReader1["SrNo"] != DBNull.Value)
{
poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]);
}
else
{
poDetail.SrNo = 0;
}
poDetails.Add(poDetail);
}
dataReader1.Close();
}
po.poDetails = poDetails;
conn.Close();
}
foreach (Po po in pos)
{
foreach (PoDetail poDetail in po.poDetails)
{
commandText = string.Format("SELECT [InvoiceNo] FROM [dbo].[SupplierInvoiceMaster] where id in (select [InvoiceId] FROM [dbo].[SupplierInvoicePoDetails] where PODetailId = '{0}')", poDetail.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
if (poDetail.InvoiceNo == null || poDetail.InvoiceNo == string.Empty)
{
poDetail.InvoiceNo = Convert.ToString(dataReader1["InvoiceNo"]);
}
else
{
poDetail.InvoiceNo = poDetail.InvoiceNo + "," + Convert.ToString(dataReader1["InvoiceNo"]);
}
}
dataReader1.Close();
}
conn.Close();
}
}
foreach (Po po in pos)
{
List <PoTerm> poTerms = new List <PoTerm>();
commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id);
using (SqlCommand cmd1 = new SqlCommand(commandText, conn))
{
cmd1.CommandType = CommandType.Text;
conn.Open();
var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader1.Read())
{
var poTerm = new PoTerm();
poTerm.Id = Convert.ToInt64(dataReader1["Id"]);
poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]);
poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]);
poTerm.Term = Convert.ToString(dataReader1["Term"]);
poTerms.Add(poTerm);
}
}
po.poTerms = poTerms;
conn.Close();
}
return(pos.OrderBy(x => x.DueDate));
}
