/// <summary>
/// Get detail for supplier invoice detail page
/// Call Proc [usp_select_SupplierInvoice_for_Page]
/// </summary>
/// <param name="supplierInvoiceId"></param>
/// <returns></returns>
public override SupplierInvoiceDetails GetForPage(System.Int32?supplierInvoiceId)
{
SqlConnection cn = null;
SqlCommand cmd = null;
try
{
cn = new SqlConnection(this.ConnectionString);
cmd = new SqlCommand("usp_select_SupplierInvoice_for_Page", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Parameters.Add("@SupplierInvoiceId", SqlDbType.Int).Value = supplierInvoiceId;
cn.Open();
DbDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
if (reader.Read())
{
SupplierInvoiceDetails obj = new SupplierInvoiceDetails();
obj.SupplierInvoiceID = GetReaderValue_Int32(reader, "SupplierInvoiceID", 0);
obj.SupplierInvoiceNumber = GetReaderValue_String(reader, "SupplierInvoiceNumber", "");
obj.SupplierName = GetReaderValue_String(reader, "SupplierName", "");
obj.CompanyNo = GetReaderValue_Int32(reader, "CompanyNo", 0);
obj.ClientNo = GetReaderValue_Int32(reader, "ClientNo", 0);
return(obj);
}
else
{
return(null);
}
}
catch (SqlException sqlex)
{
//LogException(sqlex);
throw new Exception("Failed to get supplier invoice", sqlex);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
/// <summary>
/// Call Proc [usp_select_SupplierInvoice]
/// Get the supplier invoice by supplierinvoiceId
/// </summary>
/// <param name="supplierInvoiceId"></param>
/// <returns></returns>
public override SupplierInvoiceDetails Get(System.Int32?supplierInvoiceId)
{
SqlConnection cn = null;
SqlCommand cmd = null;
try
{
cn = new SqlConnection(this.ConnectionString);
cmd = new SqlCommand("usp_select_SupplierInvoice", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Parameters.Add("@SupplierInvoiceId", SqlDbType.Int).Value = supplierInvoiceId;
cn.Open();
DbDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
if (reader.Read())
{
SupplierInvoiceDetails obj = new SupplierInvoiceDetails();
obj.SupplierInvoiceID = GetReaderValue_Int32(reader, "SupplierInvoiceID", 0);
obj.SupplierInvoiceNumber = GetReaderValue_String(reader, "SupplierInvoiceNumber", "");
obj.CompanyNo = GetReaderValue_Int32(reader, "CompanyNo", 0);
obj.ClientNo = GetReaderValue_Int32(reader, "ClientNo", 0);
obj.SupplierInvoiceDate = GetReaderValue_DateTime(reader, "SupplierInvoiceDate", DateTime.MinValue);
obj.SupplierCode = GetReaderValue_String(reader, "SupplierCode", "");
obj.SupplierName = GetReaderValue_String(reader, "SupplierName", "");
obj.CurrencyNo = GetReaderValue_NullableInt32(reader, "CurrencyNo", null);
obj.InvoiceAmount = GetReaderValue_NullableDouble(reader, "InvoiceAmount", null);
obj.GoodsValue = GetReaderValue_NullableDouble(reader, "GoodsValue", null);
obj.Tax = GetReaderValue_NullableDouble(reader, "Tax", null);
obj.DeliveryCharge = GetReaderValue_NullableDouble(reader, "DeliveryCharge", null);
obj.CreditCardFee = GetReaderValue_NullableDouble(reader, "CreditCardFee", null);
obj.BankFee = GetReaderValue_NullableDouble(reader, "BankFee", null);
obj.Exported = GetReaderValue_Boolean(reader, "Exported", false);
obj.Notes = GetReaderValue_String(reader, "Notes", "");
obj.SecondRef = GetReaderValue_String(reader, "SecondRef", "");
obj.Narrative = GetReaderValue_String(reader, "Narrative", "");
obj.CanbeExported = GetReaderValue_Boolean(reader, "CanbeExported", false);
obj.URNNumber = GetReaderValue_NullableInt64(reader, "URNNumber", null);
obj.UpdatedBy = GetReaderValue_NullableInt32(reader, "UpdatedBy", null);
obj.DLUP = GetReaderValue_DateTime(reader, "DLUP", DateTime.MinValue);
obj.PurchaseOrderNumber = GetReaderValue_NullableInt32(reader, "PurchaseOrderNumber", null);
obj.PurchaseOrderNo = GetReaderValue_NullableInt32(reader, "PurchaseOrderNo", null);
obj.CurrencyCode = GetReaderValue_String(reader, "CurrencyCode", "");
obj.TaxNo = GetReaderValue_NullableInt32(reader, "TaxNo", null);
obj.TaxName = GetReaderValue_String(reader, "TaxName", "");
obj.TaxCode = GetReaderValue_String(reader, "TaxCode", "");
obj.StatusReasonId = GetReaderValue_NullableInt32(reader, "StatusReasonNo", null);
obj.StatusReason = GetReaderValue_String(reader, "StatusReason", "");
return(obj);
}
else
{
return(null);
}
}
catch (SqlException sqlex)
{
//LogException(sqlex);
throw new Exception("Failed to get Supplier Invoice", sqlex);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
/// <summary>
/// Call Proc [usp_datalistnugget_SupplierInvoice]
/// Get list of supplier invoice on basis of following search criteria
/// </summary>
/// <param name="clientId"></param>
/// <param name="orderBy"></param>
/// <param name="sortDir"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="supplierInvoiceNumber"></param>
/// <param name="urnNoLo"></param>
/// <param name="urnNoHi"></param>
/// <param name="purchaseOrderNoLo"></param>
/// <param name="purchaseOrderNoHi"></param>
/// <param name="goodsInNoLo"></param>
/// <param name="goodsInNoHi"></param>
/// <param name="supplierInvoiceDateFrom"></param>
/// <param name="supplierInvoiceDateTo"></param>
/// <param name="cmSearch"></param>
/// <param name="recentOnly"></param>
/// <param name="cannotBeExported"></param>
/// <returns></returns>
public override List <SupplierInvoiceDetails> DataListNugget(System.Int32?clientId, System.Int32?orderBy, System.Int32?sortDir, System.Int32?pageIndex, System.Int32?pageSize, System.String supplierInvoiceNumber, System.Int32?urnNoLo, System.Int32?urnNoHi, System.Int32?purchaseOrderNoLo, System.Int32?purchaseOrderNoHi, System.Int32?goodsInNoLo, System.Int32?goodsInNoHi, System.DateTime?supplierInvoiceDateFrom, System.DateTime?supplierInvoiceDateTo, System.String cmSearch, System.Boolean?recentOnly, System.Boolean?exportedOnly, System.Boolean?approveandUnExported, System.Boolean?cannotBeExported, System.Int32?internalPurchaseOrderNoLo, System.Int32?internalPurchaseOrderNoHi, System.Int32?statusReason)
{
SqlConnection cn = null;
SqlCommand cmd = null;
try
{
cn = new SqlConnection(this.ConnectionString);
cmd = new SqlCommand("usp_datalistnugget_SupplierInvoice", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;
cmd.Parameters.Add("@ClientId", SqlDbType.Int).Value = clientId;
cmd.Parameters.Add("@OrderBy", SqlDbType.Int).Value = orderBy;
cmd.Parameters.Add("@SortDir", SqlDbType.Int).Value = sortDir;
cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
cmd.Parameters.Add("@SupplierInvoiceNumber", SqlDbType.NVarChar).Value = supplierInvoiceNumber;
cmd.Parameters.Add("@URNNoLo", SqlDbType.Int).Value = urnNoLo;
cmd.Parameters.Add("@URNNoHi", SqlDbType.Int).Value = urnNoHi;
cmd.Parameters.Add("@PurchaseOrderNoLo", SqlDbType.Int).Value = purchaseOrderNoLo;
cmd.Parameters.Add("@PurchaseOrderNoHi", SqlDbType.Int).Value = purchaseOrderNoHi;
cmd.Parameters.Add("@GoodsInNoLo", SqlDbType.Int).Value = goodsInNoLo;
cmd.Parameters.Add("@GoodsInNoHi", SqlDbType.Int).Value = goodsInNoHi;
cmd.Parameters.Add("@SupplierInvoiceDateFrom", SqlDbType.DateTime).Value = supplierInvoiceDateFrom;
cmd.Parameters.Add("@SupplierInvoiceDateTo", SqlDbType.DateTime).Value = supplierInvoiceDateTo;
cmd.Parameters.Add("@CMSearch", SqlDbType.NVarChar).Value = cmSearch;
cmd.Parameters.Add("@RecentOnly", SqlDbType.Bit).Value = recentOnly;
cmd.Parameters.Add("@ExportedOnly", SqlDbType.Bit).Value = exportedOnly;
cmd.Parameters.Add("@ApproveAndUnExported", SqlDbType.Bit).Value = approveandUnExported;
cmd.Parameters.Add("@CannotBeExported", SqlDbType.Bit).Value = cannotBeExported;
cmd.Parameters.Add("@InternalPurchaseOrderNoLo", SqlDbType.Int).Value = internalPurchaseOrderNoLo;
cmd.Parameters.Add("@InternalPurchaseOrderNoHi", SqlDbType.Int).Value = internalPurchaseOrderNoHi;
cmd.Parameters.Add("@StatusReason", SqlDbType.Int).Value = statusReason;
cn.Open();
DbDataReader reader = ExecuteReader(cmd);
List <SupplierInvoiceDetails> lst = new List <SupplierInvoiceDetails>();
while (reader.Read())
{
SupplierInvoiceDetails obj = new SupplierInvoiceDetails();
obj.SupplierInvoiceID = GetReaderValue_Int32(reader, "SupplierInvoiceId", 0);
obj.SupplierInvoiceNumber = GetReaderValue_String(reader, "SupplierInvoiceNumber", "");
obj.SupplierName = GetReaderValue_String(reader, "SupplierName", "");
obj.GoodsInNumber = GetReaderValue_Int32(reader, "GoodsInNumber", 0);
obj.GoodsInNo = GetReaderValue_Int32(reader, "GoodsInId", 0);
obj.PurchaseOrderNumber = GetReaderValue_NullableInt32(reader, "PurchaseOrderNumber", null);
obj.SupplierInvoiceDate = GetReaderValue_DateTime(reader, "SupplierInvoiceDate", DateTime.MinValue);
obj.Part = GetReaderValue_String(reader, "Part", "");
obj.InvoiceAmount = GetReaderValue_NullableDouble(reader, "InvoiceAmount", null);
obj.RowCnt = GetReaderValue_NullableInt32(reader, "RowCnt", null);
obj.CompanyNo = GetReaderValue_Int32(reader, "CompanyNo", 0);
obj.PurchaseOrderNo = GetReaderValue_NullableInt32(reader, "PurchaseOrderNo", null);
obj.CurrencyNo = GetReaderValue_Int32(reader, "CurrencyNo", 0);
obj.CurrencyCode = GetReaderValue_String(reader, "CurrencyCode", "");
obj.URNNumber = GetReaderValue_NullableInt64(reader, "URNNumber", null);
obj.InternalPurchaseOrderId = GetReaderValue_NullableInt32(reader, "InternalPurchaseOrderId", null);
lst.Add(obj);
obj = null;
}
return(lst);
}
catch (SqlException sqlex)
{
//LogException(sqlex);
throw new Exception("Failed to get supplier invoice", sqlex);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
