public static DataTable AllFiscalYears() { string query = @"select distinct year(date) as year from receivedoc where month(date)<=10 union select year(date)+1 as year from receivedoc where month(date)>10 union select distinct year(date) as year from issuedoc where month(date)<=10 union select year(date)+1 as year from issuedoc where month(date)>10 union select distinct year(date) as year from disposal where month(date)<=10 union select year(date)+1 as year from disposal where month(date)>10"; BLL.Items itm = new Items(); itm.LoadFromRawSql(query); return itm.DataTable; }
public static DataTable AllYearsReport() { var itm = new Items(); itm.FlushData(); itm.LoadFromRawSql(@"SELECT Distinct CAST(YEAR(Date)AS nvarchar)as year from ReceiveDoc union select distinct CAST(YEAR(Date)AS nvarchar)as year from IssueDoc union select distinct CAST(YEAR(Date)AS nvarchar)as year from Disposal order by year desc"); return itm.DataTable; }
public static void MergeItems(int itemToBeMergedIntoAnother, int itemUsedForMerging) { String[] tableNames ={"Disposal","IssueDoc","ReceiveDoc","YearEnd","ItemManufacturer","Items","ItemSupplier", "ItemSupplyCategory","RRFDetail","OrderDetail","PickListDetail","ProductsCategory", "ProgramProduct","DUsItemList","Exchange","InternalTransfer","Losses"}; for (int i = 0; i < tableNames.Length; i++) { BLL.Items itm = new Items(); string query = string.Format("Update {0} SET ItemID = {1} Where ItemID={2}", tableNames[i], itemUsedForMerging, itemToBeMergedIntoAnother); itm.LoadFromRawSql(query); } }
public long TotalQuantityLeftInAllBatches(int storeID) { string query = string.Format( "select SUM(QuantityLeft) Total from ReceiveDoc where itemid={0} and StoreID={1} group by ItemID", this.ID, storeID); BLL.Items temp = new Items(); temp.LoadFromRawSql(query); if (temp.RowCount > 0) { return long.Parse(temp.GetColumn("Total").ToString()); } return 0; }
/// <summary> /// Returns the commodites by type /// </summary> /// <param name="commodityType">0 - All Items</param> /// <returns></returns> public static DataTable GetActiveItemsByCommodityType(int commodityType) { string query = ""; // let 0 mean all items if (commodityType == 0) { query = string.Format("select v.Name as CommodityType,v.TypeID , *, " + " CASE WHEN (SELECT COUNT(*) from ItemManufacturer i where i.ItemID = v.ID) > 0 then 1 else 0 end as HasManufacturer, IsSelected = cast( 0 as bit) " + " from vwGetAllItems v where v.IsInHospitalList = 1 " + " ORDER BY v.FullItemName"); } else { query = string.Format("select v.Name as CommodityType, v.TypeID , *, " + " CASE WHEN (SELECT COUNT(*) from ItemManufacturer i where i.ItemID = v.ID) > 0 then 1 else 0 end as HasManufacturer, IsSelected = cast( 0 as bit) " + " from vwGetAllItems v " + " where v.IsInHospitalList = 1 and v.TypeID = '{0}' ORDER BY v.FullItemName", commodityType); } Items itms = new Items(); itms.LoadFromRawSql(query); return itms.DataTable; }