public async Task <List <MaterialReturnDetail> > GetMaterialIssueDetail(int id)
{
var models = new List <MaterialReturnDetail>();
SqlParameter[] sqlParams =
{
new SqlParameter("@id", id)
};
using (var reader = await SqlHelperExtension.ExecuteReader(_connectionString, "usp_GetIssueProduct", CommandType.StoredProcedure, sqlParams))
{
while (reader.Read())
{
var model = new MaterialReturnDetail();
model.Id = reader.DefaultIfNull <int>("Id");
model.EmployeeName = reader.DefaultIfNull <string>("Name");
model.SlipDate = reader.DefaultIfNull <DateTime>("CreatedDate");
model.MaterialTransactionId = reader.DefaultIfNull <int>("MaterialTransctionId");
model.ProductId = reader.DefaultIfNull <int>("ProductId");
model.ProductName = reader.DefaultIfNull <string>("ProductName");
model.ProductCode = reader.DefaultIfNull <string>("ProductCode");
model.ItemNumber = reader.DefaultIfNull <string>("ItemNumber");
model.Quantity = reader.DefaultIfNull <decimal>("Quantity");
model.UnitPrice = reader.DefaultIfNull <decimal>("UnitPrice");
model.Remarks = reader.DefaultIfNull <string>("Remarks");
model.UnitName = reader.DefaultIfNull <string>("UnitName");
model.TotalPrice = reader.DefaultIfNull <decimal>("TotalPrice");
model.SlipNumber = reader.DefaultIfNull <string>("SlipNumber");
model.UniqueItemId = reader.DefaultIfNull <int>("UniqueItemId");
models.Add(model);
}
}
return(models);
}
public async Task <List <ProductIssueDetail> > GetProductIssueDetail(int Id)
{
var models = new List <ProductIssueDetail>();
SqlParameter[] sqlParams =
{
new SqlParameter("@mtId", Id)
};
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetIssueProduct, System.Data.CommandType.StoredProcedure, sqlParams);
while (reader.Read())
{
ProductIssueDetail model = new ProductIssueDetail();
model.Id = reader.DefaultIfNull <int>("Id");
model.ProductName = reader.DefaultIfNull <string>("ProductName");
model.EmployeeName = reader.DefaultIfNull <string>("EmployeeName");
model.TransactionDate = reader.DefaultIfNull <DateTime>("TransactionDate");
model.TransactionType = reader.DefaultIfNull <string>("TransactionType");
model.SlipNumber = reader.DefaultIfNull <string>("SlipNumber");
model.ItemId = reader.DefaultIfNull <int>("ItemId");
model.ItemNumber = reader.DefaultIfNull <string>("ItemNumber");
model.Quantity = reader.DefaultIfNull <decimal>("Quantity");
models.Add(model);
}
return(models);
}
public async Task <List <VenderWisePoStatusDetail> > GetVendorWisePoStatusDetailReport(int vendorId, string status)
{
var models = new List <VenderWisePoStatusDetail>();
try
{
SqlParameter[] sqlParams =
{
new SqlParameter("@VendorId", vendorId),
new SqlParameter("@PoStatus", status)
};
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetVendorWisePoStatusDetailReport, System.Data.CommandType.StoredProcedure, sqlParams);
while (reader.Read())
{
VenderWisePoStatusDetail model = new VenderWisePoStatusDetail();
model.PoDate = reader.DefaultIfNull <DateTime>("PoDate");
model.PoNumber = reader.DefaultIfNull <string>("PoNumber");
model.VendorName = reader.DefaultIfNull <string>("VendorName");
model.POStatus = reader.DefaultIfNull <string>("PoStatus");
models.Add(model);
}
return(models);
}
catch (Exception ex)
{
return(models);
}
}
public async Task <List <ApprovedSIVDetail> > GetSIVApprovedDetail()
{
SqlParameter[] sqlParams = { };
var models = new List <ApprovedSIVDetail>();
try
{
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, "usp_GetApprovedSIVDateWise", System.Data.CommandType.StoredProcedure, sqlParams);
while (reader.Read())
{
var model = new ApprovedSIVDetail();
model.PoDate = reader.DefaultIfNull <DateTime>("PODate");
model.PoNumber = reader.DefaultIfNull <string>("PONumber");
model.VendorName = reader.DefaultIfNull <string>("VendorName");
model.vendorEmail = reader.DefaultIfNull <string>("VendorEmail");
model.VendorPhone = reader.DefaultIfNull <string>("VendorPhone");
model.VendorType = reader.DefaultIfNull <string>("VendorType");
models.Add(model);
}
return(models);
}
catch (Exception ex)
{
return(models);
}
}
public async Task <int> ApprovedSIVCount()
{
SqlParameter[] sqlParams = { };
try
{
var response = await SqlHelperExtension.ExecuteScalar(_connectionString, SqlConstant.ProcGetApprovedSIVCount, System.Data.CommandType.StoredProcedure, sqlParams);
return(Convert.ToInt32(response));
}
catch (Exception ex)
{
return(-1);
}
}
public async Task <int> IsExistsItemNumber(string itemnumber)
{
SqlParameter[] sqlparams =
{
new SqlParameter("@in_itemNumber", itemnumber ?? string.Empty)
};
try {
var response = await SqlHelperExtension.ExecuteScalar(_connectionString, "usp_CheckProductItemExists", System.Data.CommandType.StoredProcedure, sqlparams);
return(Convert.ToInt32(response));
}
catch (Exception ex)
{
return(-1);
}
}
public async Task <List <VendorWisePOStatus> > GetVendorWisePOStatusReport()
{
var models = new List <VendorWisePOStatus>();
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetVendorWisePoStatusReport, System.Data.CommandType.StoredProcedure, null);
while (reader.Read())
{
VendorWisePOStatus model = new VendorWisePOStatus();
model.POCountByStatus = reader.DefaultIfNull <int>("POCountByStatus");
model.VendorId = reader.DefaultIfNull <int>("VendorId");
model.VendorName = reader.DefaultIfNull <string>("VendorName");
model.POStatus = reader.DefaultIfNull <string>("POStatus");
models.Add(model);
}
return(models);
}
public async Task <(int responseStatus, string responseMessage)> CreateMaterialIssue(MaterialTransction model, List <TransactionItems> items)
{
DataTable dt = new DataTable();
dt.Columns.Add("MaterialTransactionId", typeof(int));
dt.Columns.Add("ItemId", typeof(int));
dt.Columns.Add("Quantity", typeof(decimal));
dt.Columns.Add("UnitPrice", typeof(decimal));
dt.Columns.Add("Remarks", typeof(string));
dt.Columns.Add("ItemNumber", typeof(int));
items.ForEach(item =>
{
DataRow dr = dt.NewRow();
dr["MaterialTransactionId"] = 0;
dr["ItemId"] = item.ItemId;
dr["Quantity"] = item.Quantity;
dr["UnitPrice"] = item.UnitPrice;
dr["Remarks"] = item.Remarks;
dr["ItemNumber"] = item.UniqueItemId;
dt.Rows.Add(dr);
});
SqlParameter[] sqlParams =
{
new SqlParameter("@employeeId", model.EmployeeId),
new SqlParameter("@transactionDate", model.TransactionDate),
new SqlParameter("@transactionType", model.TransactionType),
new SqlParameter("@slipNumber", model.SlipNumber ?? string.Empty),
new SqlParameter("@createdBy", model.CreatedBy),
new SqlParameter("@transactionItem", dt),
};
try
{
var response = await SqlHelperExtension.ExecuteNonQuery(_connectionString, "Proc_MaterialIssue",
CommandType.StoredProcedure,
sqlParams);
return(1, "Material issue inserted");
}
catch (Exception ex)
{
return(-1, ex.Message);
}
}
public async Task <List <PoStatusCount> > GetPoStatusCount()
{
var models = new List <PoStatusCount>();
SqlParameter[] sqlParams = { };
using (var reader = await SqlHelperExtension.ExecuteReader(_connectionString, "Proc_PoStatusCount", System.Data.CommandType.StoredProcedure, sqlParams))
{
while (reader.Read())
{
var model = new PoStatusCount();
model.StatusName = reader.DefaultIfNull <string>("POStatus");
model.StatusCount = reader.DefaultIfNull <int>("StatusCount");
models.Add(model);
}
}
return(models);
}
public async Task <List <LowQuantityProductReport> > GetLowQuantityProductReport()
{
var models = new List <LowQuantityProductReport>();
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetLowQuantityProductReport, System.Data.CommandType.StoredProcedure, null);
while (reader.Read())
{
LowQuantityProductReport model = new LowQuantityProductReport();
model.Code = reader.DefaultIfNull <string>("Code");
model.Name = reader.DefaultIfNull <string>("Name");
model.PeUnitCost = reader.DefaultIfNull <decimal>("PerUnitCost");
model.Quantity = reader.DefaultIfNull <int>("Quantity");
model.ThresholdValue = reader.DefaultIfNull <int>("ThresholdValue");
model.Unit = reader.DefaultIfNull <string>("Unit");
models.Add(model);
}
return(models);
}
public async Task <List <MaterialMasterVm> > GetMaterialDetail()
{
var models = new List <MaterialMasterVm>();
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetMaterialDetail, System.Data.CommandType.StoredProcedure, null);
while (reader.Read())
{
MaterialMasterVm model = new MaterialMasterVm();
model.Id = reader.DefaultIfNull <int>("Id");
model.MaterialName = reader.DefaultIfNull <string>("Name");
model.Code = reader.DefaultIfNull <string>("Code");
model.Unit = reader.DefaultIfNull <string>("Unit");
model.PerUnitCost = reader.DefaultIfNull <decimal>("PerUnitCost");
model.Quantity = reader.DefaultIfNull <int>("OpeningQuantity");
model.ThresholdValue = reader.DefaultIfNull <int>("ThresholdValue");
model.IsUniqe = Convert.ToBoolean(reader.DefaultIfNull <int>("IsUnique"));
models.Add(model);
}
return(models);
}
public async Task <(int responseStatus, string responseMessage)> InsertIGLProduct(List <IGLProduct> modelEntities)
{
DataTable dt = new DataTable();
dt.Columns.Add("MaterialId", typeof(int));
dt.Columns.Add("ItemNumber", typeof(string));
dt.Columns.Add("Quantity", typeof(decimal));
dt.Columns.Add("CreatedBy", typeof(int));
modelEntities.ForEach(item => {
DataRow row = dt.NewRow();
row["MaterialId"] = item.MaterialId;
row["ItemNumber"] = item.ItemNumber;
row["Quantity"] = item.Quantity;
row["CreatedBy"] = item.CreatedBy;
dt.Rows.Add(row);
});
SqlParameter[] sqlParams =
{
new SqlParameter("@materialId", modelEntities.First().MaterialId),
new SqlParameter("@materialCount", modelEntities.Count()),
new SqlParameter("@iglProduct", dt)
{
SqlDbType = SqlDbType.Structured
},
new SqlParameter("@poNumber", modelEntities.First().PoNumber)
};
try
{
var response = await SqlHelperExtension.ExecuteNonQuery(_connectionString, SqlConstant.ProcInsertIGLProduct, System.Data.CommandType.StoredProcedure, sqlParams);
return(response >= 1 ? (response, "Inserted") : (0, "Exception"));
}
catch (Exception ex)
{
return(-1, ex.Message);
}
}
public async Task <List <IGLProductPoWise> > PoWiseIGLProducts()
{
SqlParameter[] sqlParams = { };
var models = new List <IGLProductPoWise>();
using (var reader = await SqlHelperExtension.ExecuteReader(_connectionString, "Proc_GetIGLProductByPo", CommandType.StoredProcedure, sqlParams))
{
while (reader.Read())
{
var model = new IGLProductPoWise();
model.PoNumber = reader.DefaultIfNull <string>("PONumber");
model.ProductName = reader.DefaultIfNull <string>("ProductName");
model.HSNCode = reader.DefaultIfNull <string>("HSNCode");
model.UnitName = reader.DefaultIfNull <string>("UnitName");
model.ThreshHoldValue = reader.DefaultIfNull <int>("ThresholdValue");
model.Quantity = reader.DefaultIfNull <int>("OpeningQuantity");
model.ItemNumber = reader.DefaultIfNull <string>("ItemNumber");
models.Add(model);
}
}
return(models);
}
public async Task <List <ProductTransactionStatusReport> > GetProductTransactionStatusReport(int?EmployeeId)
{
var models = new List <ProductTransactionStatusReport>();
var reader = await SqlHelperExtension.ExecuteReader(_connectionString, SqlConstant.ProcGetProductTransactionStatusReport, System.Data.CommandType.StoredProcedure, null);
while (reader.Read())
{
ProductTransactionStatusReport model = new ProductTransactionStatusReport();
model.EmailId = reader.DefaultIfNull <string>("EmailId");
model.EmployeeId = reader.DefaultIfNull <int>("EmployeeId");
model.EmployeeName = reader.DefaultIfNull <string>("EmployeeName");
model.Phone = reader.DefaultIfNull <string>("Phone");
model.ProductName = reader.DefaultIfNull <string>("ProductName");
model.Quantity = reader.DefaultIfNull <int>("Quantity");
model.TotalPrice = reader.DefaultIfNull <decimal>("TotalPrice");
model.TransactionType = reader.DefaultIfNull <string>("TransactionType");
model.UnitPrice = reader.DefaultIfNull <decimal>("UnitPrice");
models.Add(model);
}
return(models);
}
