//public List<ADIInventoryExport> FillByProduct(String PART_NUM)
//{
// String Query = "SELECT * FROM ADIInventoryExport WHERE PART_NUM = @PART_NUM";
// SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
// oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
// return DataParser.ToList<ADIInventoryExport>(oDm.GetTable());
//}
public int ProductCount(String PART_NUM)
{
String Query = "SELECT COUNT(1) FROM ADIInventoryExport WITH (NOLOCK) WHERE PART_NUM = @PART_NUM";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
return (int) oDm.GetTable().Rows[0][0];
}
public List<TaskDetail> GetDataById(Int64 TaskID)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskID = @TaskID";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddIntegerBigPara("TaskID", TaskID);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
public DataTable GetDataTableByPartNumber(String PART_NUM)
{
String Query = "SELECT * FROM ADIChild WITH (NOLOCK) WHERE PART_NUM = @PART_NUM";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
return oDm.GetTable();
}
public FinalExport GetTop()
{
String Query = "SELECT TOP 1 * FROM FinalExport ORDER BY CreatedDate";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
List<FinalExport> finalExports = DataParser.ToList<FinalExport>(oDm.GetTable());
return finalExports.Count == 0 ? null : finalExports[0];
}
public List<FinalExport> GetBySite(String ExportSite)
{
String Query = "SELECT * FROM FinalExport WHERE ExportSite = @ExportSite ORDER BY CreatedDate";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("ExportSite", 4000, ExportSite);
return DataParser.ToList<FinalExport>(oDm.GetTable());
}
public List<AdiProduct> GetData()
{
String Query = "SELECT * FROM ADIProduct1 WITH (NOLOCK)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.ConnectionString = this.ConnectionString;
return DataParser.ToList<AdiProduct>(oDm.GetTable());
}
public List<AdiProduct> GetAllPriorityProducts()
{
String Query = "SELECT * FROM ADIProduct1 WITH (NOLOCK) WHERE PriorityProduct = 1 AND LastUpdateDatetime <= DATEADD(d, -1, GETDATE())";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.ConnectionString = this.ConnectionString;
return DataParser.ToList<AdiProduct>(oDm.GetTable());
}
public List<ADICategoryExport> GetDataByCatagoryID(String Value)
{
String Query = "SELECT * FROM ADICategoryExport WITH (NOLOCK) WHERE Value = @Value";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("Value", 255, Value);
return DataParser.ToList<ADICategoryExport>(oDm.GetTable());
}
public List<AdiCategory> GetCategoryByParent(String ParentValue)
{
String Query = "SELECT * FROM ADICategory WITH (NOLOCK) WHERE ParentValue = @ParentValue";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("ParentValue", 255, ParentValue);
return DataParser.ToList<AdiCategory>(oDm.GetTable());
}
public ADIInventoryExport GetDataByProduct(String PART_NUM)
{
String Query = "SELECT * FROM ADIInventoryExport WITH (NOLOCK) WHERE PART_NUM = @PART_NUM";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
var list = DataParser.ToList<ADIInventoryExport>(oDm.GetTable());
return list.Count == 1 ? list[0] : null;
}
public FinalExport GetTopBySite(String ExportSite)
{
String Query = "SELECT TOP 1 * FROM FinalExport WHERE ExportSite = @ExportSite ORDER BY CreatedDate";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("ExportSite", 4000, ExportSite);
List<FinalExport> finalExports = DataParser.ToList<FinalExport>(oDm.GetTable());
return finalExports.Count == 0 ? null : finalExports[0];
}
public List<ADIInventoryDetails> GetDataByPartNumber(String AdiNumber)
{
String Query = "SELECT * FROM ADIInventory WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
return DataParser.ToList<ADIInventoryDetails>(oDm.GetTable());
}
public List<TaskDetail> GetDataBySite(Int64 TaskHeaderID, String TaskSite, Int32 TaskStatus)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskSite = @TaskSite AND TaskStatus = @TaskStatus";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
oDm.AddIntegerPara("TaskStatus", TaskStatus);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
public DataTable GetDataTableByPartNumber(String AdiNumber)
{
String Query = "SELECT * FROM ADIInventory WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
return oDm.GetTable();
}
public String GetValue(String PART_NUM, String PropertyName)
{
String Query = "SELECT ID, PART_NUM, PropertyName, PropertyValue FROM ADIChild WITH (NOLOCK) WHERE PART_NUM = @PART_NUM AND PropertyName = @PropertyName";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
oDm.AddVarcharPara("PropertyName", 4000, PropertyName);
List<ADIChild> childs = DataParser.ToList<ADIChild>(oDm.GetTable());
if (childs.Count > 0)
return childs[0].PropertyValue;
else
return null;
}
public List<TaskDetail> GetDataScheduledPendingTasks(String TaskSite)
{
String Query = "SELECT TD.* FROM TaskHeader TH WITH (NOLOCK) "
+ "JOIN TaskDetail TD WITH (NOLOCK) ON TH.ScheduleID = TD.TaskHeaderID "
+ "WHERE TH.NextRun IS NOT NULL AND TH.NextRun <= GETDATE() AND TaskSite = @TaskSite";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
public int ClearanceZoneCategoryCount()
{
String Query = "SELECT COUNT(1) FROM ADICategory WITH (NOLOCK) WHERE ClearanceZone = 1";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return (int)oDm.GetTable().Rows[0][0];
}
public int InStockCategoryCount()
{
throw new NotImplementedException();
String Query = "SELECT COUNT(1) FROM ADICategory WITH (NOLOCK) WHERE InStock = 1";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return (int)oDm.GetTable().Rows[0][0];
}
public List<ADICategoryExport> GetExport()
{
String Query = "SELECT ROOT.[Value] [RootValue],ROOT.[DisplayName] [RootDisplayName],PARENT.[Value] [ParentValue],PARENT.[DisplayName] [ParentDisplayName],CHILD.[Value] ,CHILD.[DisplayName] ,CHILD.[CategoryUrl] "
+ "FROM [ADICategory] ROOT WITH (NOLOCK) JOIN [ADICategory] PARENT WITH (NOLOCK) ON ROOT.[Value] = PARENT.[ParentValue] "
+ "JOIN [ADICategory] CHILD WITH (NOLOCK) ON PARENT.[Value] = CHILD.[ParentValue] "
+ "WHERE ROOT.[ParentValue] IS NULL";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return DataParser.ToList<ADICategoryExport>(oDm.GetTable());
}
public DataTable GetDataTable()
{
String Query = "SELECT * FROM ADICategory WITH (NOLOCK)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return oDm.GetTable();
}
public List<TaskHeader> GetData()
{
String Query = "SELECT * FROM TaskHeader WITH (NOLOCK)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return DataParser.ToList<TaskHeader>(oDm.GetTable());
}
public List<TaskHeader> GetData(Int64 ScheduleID)
{
String Query = "SELECT * FROM TaskHeader WITH (NOLOCK) WHERE ScheduleID = @ScheduleID";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddIntegerBigPara("ScheduleID", ScheduleID);
return DataParser.ToList<TaskHeader>(oDm.GetTable());
}
public List<InManufacturerSeries> GetData(InManufacturerSeries manufacturer)
{
String Query = "SELECT * FROM SecLockManufacturerSeries WITH (NOLOCK) WHERE ID = '" + manufacturer.ID +"' ORDER BY Name";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return DataParser.ToList<InManufacturerSeries>(oDm.GetTable());
}
public List<TaskDetail> GetDataBySite(Int64 TaskHeaderID, String TaskSite, Int32[] TaskStatus)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskHeaderID = @TaskHeaderID AND TaskSite = @TaskSite AND TaskStatus IN ({0})";
Query = String.Format(Query, String.Join(",", TaskStatus.Select(x => x.ToString()).ToArray()));
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddIntegerBigPara("TaskHeaderID", TaskHeaderID);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
//public int Insert(String ParentValue, String Value, String DisplayName, String CategoryUrl)
//{
// String Query = "INSERT INTO ADICategory (ParentValue, Value, DisplayName, CategoryUrl) VALUES (@ParentValue, @Value, @DisplayName, @CategoryUrl)";
// SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
// oDm.AddVarcharPara("ParentValue", 255, ParentValue);
// oDm.AddVarcharPara("Value", 255, Value);
// oDm.AddVarcharPara("DisplayName", 4000, DisplayName);
// oDm.AddVarcharPara("CategoryUrl", 4000, CategoryUrl);
// return oDm.RunActionQuery();
//}
public int CatagoryCount()
{
String Query = "SELECT COUNT(1) FROM ADICategoryExport WITH (NOLOCK)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return (int)oDm.GetTable().Rows[0][0];
}
public List<AdiCategory> GetData()
{
String Query = "SELECT * FROM ADICategory WITH (NOLOCK)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return DataParser.ToList<AdiCategory>(oDm.GetTable());
}
public List<TaskDetail> GetDataByTaskDetail(Int64 TaskHeaderID, String TaskSite, String TaskMode, String TaskType, String TaskNameValue)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskSite = @TaskSite AND TaskType = @TaskType AND TaskMode = @TaskMode AND TaskNameValue = @TaskNameValue";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
oDm.AddVarcharPara("TaskType", 4000, TaskType);
oDm.AddVarcharPara("TaskMode", 4000, TaskMode);
oDm.AddVarcharPara("TaskNameValue", 4000, TaskNameValue);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
public List<TaskDetail> GetDataByStatus(Int64 TaskHeaderID, Int32[] TaskStatus)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskHeaderID = " + TaskHeaderID.ToString()
+ " AND TaskStatus IN (" + string.Join(",", TaskStatus.Select(x => x.ToString())) + ")";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}
public List<AdiCategory> GetData(Boolean ClearanceZone, Boolean SaleCenter, Boolean OnlineSpecials, Boolean HotDeals, Boolean InStock)
{
String Query = "SELECT * FROM ADICategory WITH (NOLOCK) "
+ "WHERE ClearanceZone = @ClearanceZone AND SaleCenter = @SaleCenter AND OnlineSpecials = @OnlineSpecials AND HotDeals = @HotDeals AND InStock = @InStock";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddBoolPara("ClearanceZone", ClearanceZone);
oDm.AddBoolPara("SaleCenter", SaleCenter);
oDm.AddBoolPara("OnlineSpecials", OnlineSpecials);
oDm.AddBoolPara("HotDeals", HotDeals);
oDm.AddBoolPara("InStock", InStock);
return DataParser.ToList<AdiCategory>(oDm.GetTable());
}
public List<TaskDetail> GetData()
{
String Query = "SELECT * FROM TaskDetail";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
return DataParser.ToList<TaskDetail>(oDm.GetTable());
}