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;
}