public static IngredPurchase LastPurchase(int IngredRno)
{
IngredPurchase Purchase = null;
//DateTime BestPurchaseDt = DateTime.MinValue;
////if (IngredPurchases == null)
//{
// LoadIngredPurchases();
//}
//for (int i = 0; i < IngredPurchases.Length; i++)
//{
// if (IngredPurchases[i].IngredRno == IngredRno && IngredPurchases[i].PurchaseDt > BestPurchaseDt)
// {
// Purchase = IngredPurchases[i];
// BestPurchaseDt = IngredPurchases[i].PurchaseDt;
// }
//}
string Sql = string.Format(
"Select Top 1 p.VendorRno, d.IngredRno, d.PurchaseQty, d.PurchaseUnitQty, d.PurchaseUnitRno, d.Price, p.PurchaseDt,\n" +
"(Select UnitSingle From Units Where UnitRno = d.PurchaseUnitRno) As UnitSingle,\n" +
"(Select UnitPlural From Units Where UnitRno = d.PurchaseUnitRno) As UnitPlural\n" +
"from PurchaseDetails d Inner Join Purchases p On d.PurchaseRno = p.PurchaseRno\n" +
"Where IngredRno = {0}\n" +
"Order By p.PurchaseDt Desc, d.PurchaseRno Desc",
IngredRno);
try
{
DataRow dr = DB.DBDataRow(Sql);
if (dr != null)
{
Purchase = new IngredPurchase();
Purchase.VendorRno = DB.Int32(dr["VendorRno"]);
Purchase.IngredRno = DB.Int32(dr["IngredRno"]);
Purchase.Qty = DB.Dec(dr["PurchaseQty"]);
Purchase.UnitQty = DB.Dec(dr["PurchaseUnitQty"]);
Purchase.UnitRno = DB.Int32(dr["PurchaseUnitRno"]);
Purchase.UnitSingle = DB.Str(dr["UnitSingle"]);
Purchase.UnitPlural = DB.Str(dr["UnitPlural"]);
Purchase.Price = DB.Dec(dr["Price"]);
Purchase.PurchaseDt = DB.DtTm(dr["PurchaseDt"]);
}
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
System.Web.HttpContext.Current.Response.Write(Err.Html());
}
return(Purchase);
}
public static void LoadIngredPurchases()
{
string Sql =
"With Ingreds As\n" +
"(\n" +
" Select Distinct VendorRno, IngredRno\n"+
" From Purchases p Inner Join PurchaseDetails d On p.PurchaseRno = d.PurchaseRno\n"+
" Group By VendorRno, IngredRno\n"+
"),\n" +
"IngredDetails As\n" +
"(\n" +
" Select *,\n"+
" (\n"+
" Select Top 1 d.PurchaseDetailRno\n"+
" From Purchases p Inner Join PurchaseDetails d On p.PurchaseRno = d.PurchaseRno\n"+
" Where p.VendorRno = i.VendorRno And d.IngredRno = i.IngredRno\n"+
" Order By p.PurchaseDt Desc\n"+
" ) As PurchaseDetailRno\n"+
" From Ingreds i\n"+
")\n" +
"Select i.VendorRno, i.IngredRno, d.PurchaseQty, d.PurchaseUnitQty, d.PurchaseUnitRno, d.Price, p.PurchaseDt,\n" +
"(Select UnitSingle From Units Where UnitRno = d.PurchaseUnitRno) As UnitSingle,\n" +
"(Select UnitPlural From Units Where UnitRno = d.PurchaseUnitRno) As UnitPlural\n" +
"From IngredDetails i Inner Join PurchaseDetails d On i.PurchaseDetailRno = d.PurchaseDetailRno\n" +
"Inner Join Purchases p On d.PurchaseRno = p.PurchaseRno\n" +
"Order By VendorRno, IngredRno\n";
try
{
DataTable dt = DB.DBDataTable(Sql);
IngredPurchases = new IngredPurchase[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
IngredPurchases[i] = new IngredPurchase();
IngredPurchases[i].VendorRno = DB.Int32(dr["VendorRno"]);
IngredPurchases[i].IngredRno = DB.Int32(dr["IngredRno"]);
IngredPurchases[i].Qty = DB.Int32(dr["PurchaseQty"]);
IngredPurchases[i].UnitQty = DB.Int32(dr["PurchaseUnitQty"]);
IngredPurchases[i].UnitRno = DB.Int32(dr["PurchaseUnitRno"]);
IngredPurchases[i].UnitSingle = DB.Str(dr["UnitSingle"]);
IngredPurchases[i].UnitPlural = DB.Str(dr["UnitPlural"]);
IngredPurchases[i].Price = DB.Dec(dr["Price"]);
IngredPurchases[i].PurchaseDt = DB.DtTm(dr["PurchaseDt"]);
i++;
}
}
catch (Exception Ex)
{
Err Err = new Err(Ex, Sql);
System.Web.HttpContext.Current.Response.Write(Err.Html());
}
}