public int GetSpecificWordRow(string manufacturer, string keywords)
{
using (DefaultConnection db = new DefaultConnection())
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <ReadableProduct>(db, keywords);
int openManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[0];
int closeManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[1];
if (!String.IsNullOrEmpty(keywords))
{
var anonymous = db.Database
.SqlQuery <ReadableProduct>(where)
.ToList();
return(anonymous
.Where(a => a.Manufacturer_Id >= openManufacturer_Id &&
a.Manufacturer_Id <= closeManufacturer_Id)
.Count());
}
else
{
return(db.ReadableProducts
.Where(rp => rp.Manufacturer_Id >= openManufacturer_Id &&
rp.Manufacturer_Id <= closeManufacturer_Id)
.Count());
}
}
}
public List <ReadableProductAttribute> GetSpecificWordGroup(string businessPartner, string manufacturer, string keywords)
{
using (DefaultConnection db = new DefaultConnection())
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <ReadableProductAttribute>(db, keywords);
int openManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[0];
int closeManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[1];
int openBusinessPertner_Id = BusinessPartnerData.NameToId(db, businessPartner)[0];
int closeBusinessPertner_Id = BusinessPartnerData.NameToId(db, businessPartner)[1];
if (!String.IsNullOrEmpty(keywords))
{
var anonymous = db.Database
.SqlQuery <ReadableProductAttribute>(where)
.ToList();
return(anonymous
.Where(a => a.Manufacturer_Id >= openManufacturer_Id &&
a.Manufacturer_Id <= closeManufacturer_Id &&
a.BusinessPartner_Id >= openBusinessPertner_Id &&
a.BusinessPartner_Id <= closeBusinessPertner_Id)
.ToList());
}
else
{
return(db.ReadableProductAttributes
.Where(ra => ra.Manufacturer_Id >= openManufacturer_Id &&
ra.Manufacturer_Id <= closeManufacturer_Id &&
ra.BusinessPartner_Id >= openBusinessPertner_Id &&
ra.BusinessPartner_Id <= closeBusinessPertner_Id)
.ToList());
}
}
}
public List <ReadableSale> GetSpecificWordGroup(string customer, string manufacturer, string keywords, int staff_Id, string helper,
DateTime salesOrderStartDate, DateTime salesOrderEndDate, DateTime salesStartDate,
DateTime salesEndDate)
{
using (DefaultConnection db = new DefaultConnection())
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <ReadableSale>(db, keywords);
int openCustomer_Id = BusinessPartnerData.NameToId(db, customer)[0];
int closeCustomer_Id = BusinessPartnerData.NameToId(db, customer)[1];
int openManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[0];
int closeManufacturer_Id = ManufacturerData.NameToId(db, manufacturer)[1];
int openStaff_Id = StaffData.GetIdRange(db, staff_Id)[0];
int closeStaff_Id = StaffData.GetIdRange(db, staff_Id)[1];
int openHelper_Id = HelperData.NameToId(db, helper)[0];
int closeHelper_Id = HelperData.NameToId(db, helper)[1];
List <ReadableSale> readableSales = new List <ReadableSale>();
if (!String.IsNullOrEmpty(keywords))
{
readableSales = db.Database
.SqlQuery <ReadableSale>(where)
.ToList();
return(readableSales
.Where(rs => rs.Customer_Id >= openCustomer_Id &&
rs.Customer_Id <= closeCustomer_Id &&
rs.Manufacturer_Id >= openManufacturer_Id &&
rs.Manufacturer_Id <= closeManufacturer_Id &&
rs.ResponsibleStaff_Id >= openStaff_Id &&
rs.ResponsibleStaff_Id <= closeStaff_Id &&
rs.Helper_Id >= openHelper_Id &&
rs.Helper_Id <= closeHelper_Id &&
rs.SalesOrderDate >= salesOrderStartDate &&
rs.SalesOrderDate <= salesOrderEndDate &&
rs.SalesDate >= salesStartDate &&
rs.SalesDate <= salesEndDate)
.ToList());
}
else
{
readableSales = db.ReadableSales
.Where(rs => rs.Customer_Id >= openCustomer_Id &&
rs.Customer_Id <= closeCustomer_Id &&
rs.Manufacturer_Id >= openManufacturer_Id &&
rs.Manufacturer_Id <= closeManufacturer_Id &&
rs.ResponsibleStaff_Id >= openStaff_Id &&
rs.ResponsibleStaff_Id <= closeStaff_Id &&
rs.Helper_Id >= openHelper_Id &&
rs.Helper_Id <= closeHelper_Id &&
rs.SalesOrderDate >= salesOrderStartDate &&
rs.SalesOrderDate <= salesOrderEndDate &&
rs.SalesDate >= salesStartDate &&
rs.SalesDate <= salesEndDate)
.ToList();
return(readableSales);
}
}
}
public List <BeforeDelivery> GetSpecificWordGroup(int customer_Id, int manufacturer_Id, string keywords, int responsibleStaff_Id,
int helper_Id, DateTime startDate, DateTime endDate)
{
using (DefaultConnection db = new DefaultConnection())
{
int openCustomer_Id = BusinessPartnerData.GetIdRange(db, customer_Id)[0];
int closeCustomer_Id = BusinessPartnerData.GetIdRange(db, customer_Id)[1];
int openManufacturer_Id = ManufacturerData.GetIdRange(db, manufacturer_Id)[0];
int closeManufacturer_Id = ManufacturerData.GetIdRange(db, manufacturer_Id)[1];
int openStaff_Id = IdRange.Staff(db, responsibleStaff_Id)[0];
int closeStaff_Id = IdRange.Staff(db, responsibleStaff_Id)[1];
int openHelper_Id = IdRange.Helper(db, helper_Id)[0];
int closeHelper_Id = IdRange.Helper(db, helper_Id)[1];
List <BeforeDelivery> beforeDeliveries = new List <BeforeDelivery>();
if (!String.IsNullOrEmpty(keywords))
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <BeforeDelivery>(db, keywords);
string[] keywordArray = keywords.Split(new[] { ' ', ' ' });
beforeDeliveries = db.Database
.SqlQuery <BeforeDelivery>(where)
.ToList();
return(beforeDeliveries
.Where(so => so.Customer_Id >= openCustomer_Id &&
so.Customer_Id <= closeCustomer_Id &&
so.Manufacturer_Id >= openManufacturer_Id &&
so.Manufacturer_Id <= closeManufacturer_Id &&
so.ResponsibleStaff_Id >= openStaff_Id &&
so.ResponsibleStaff_Id <= closeStaff_Id &&
so.Helper_Id >= openHelper_Id &&
so.Helper_Id <= closeHelper_Id &&
so.SalesOrderDate >= startDate &&
so.SalesOrderDate <= endDate)
.ToList());
}
else
{
return(db.BeforeDeliveries
.Where(so => so.Customer_Id >= openCustomer_Id &&
so.Customer_Id <= closeCustomer_Id &&
so.Manufacturer_Id >= openManufacturer_Id &&
so.Manufacturer_Id <= closeManufacturer_Id &&
so.ResponsibleStaff_Id >= openStaff_Id &&
so.ResponsibleStaff_Id <= closeStaff_Id &&
so.Helper_Id >= openHelper_Id &&
so.Helper_Id <= closeHelper_Id &&
so.SalesOrderDate >= startDate &&
so.SalesOrderDate <= endDate)
.ToList());
}
}
}
public List <ReadablePurchase> GetSpecificWordGroup(string supplier, string manufacturer, string keywords, int staff_Id,
DateTime purchaseStartDate, DateTime purchaseEndDate,
DateTime receiptStartDate, DateTime receiptEndDate)
{
using (DefaultConnection db = new DefaultConnection())
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <ReadablePurchase>(db, keywords);
int[] supplier_Ids = BusinessPartnerData.NameToId(db, supplier);
int openSupplier_Id = supplier_Ids[0];
int closeSupplier_Id = supplier_Ids[1];
int[] manufacturer_Ids = ManufacturerData.NameToId(db, manufacturer);
int openManufacturer_Id = manufacturer_Ids[0];
int closeManufacturer_Id = manufacturer_Ids[1];
int[] staff_Ids = StaffData.GetIdRange(db, staff_Id);
int openStaff_Id = staff_Ids[0];
int closeStaff_Id = staff_Ids[1];
List <ReadablePurchase> readablePurchases = new List <ReadablePurchase>();
if (!String.IsNullOrEmpty(keywords))
{
readablePurchases = db.Database
.SqlQuery <ReadablePurchase>(where)
.ToList();
return(readablePurchases
.Where(p => p.Supplier_Id >= openSupplier_Id &&
p.Supplier_Id <= closeSupplier_Id &&
p.ResponsibleStaff_Id >= openStaff_Id &&
p.ResponsibleStaff_Id <= closeStaff_Id &&
p.PurchaseDate >= purchaseStartDate &&
p.PurchaseDate <= purchaseEndDate &&
p.ReceiptDate >= receiptStartDate &&
p.ReceiptDate <= receiptEndDate)
.ToList());
}
else
{
readablePurchases = db.ReadablePurchases
.Where(p => p.Supplier_Id >= openSupplier_Id &&
p.Supplier_Id <= closeSupplier_Id &&
p.ResponsibleStaff_Id >= openStaff_Id &&
p.ResponsibleStaff_Id <= closeStaff_Id &&
p.PurchaseDate >= purchaseStartDate &&
p.PurchaseDate <= purchaseEndDate &&
p.ReceiptDate >= receiptStartDate)
.ToList();
return(readablePurchases);
}
}
}
public List<ReadableGoodsIssue> GetSpecificWordGroup(string manufacturer, string keywords, int accountTitle_Id
, int responsibleStaff_Id, DateTime startDate, DateTime endDate)
{
using (DefaultConnection db = new DefaultConnection())
{
int[] manufacturer_Ids = ManufacturerData.NameToId(db, manufacturer);
int openManufacturer_Id = manufacturer_Ids[0];
int closeManufacturer_Id = manufacturer_Ids[1];
int[] accountTitle_Ids = AccountTitleData.GetIdRange(db, accountTitle_Id);
int openAccountTitle_Id = accountTitle_Ids[0];
int closeAccountTitle_Id = accountTitle_Ids[1];
int[] staff_Ids = StaffData.GetIdRange(db, responsibleStaff_Id);
int openResponsibleStaff_Id = staff_Ids[0];
int closeResponsibleStaff_Id = staff_Ids[1];
if (!String.IsNullOrEmpty(keywords))
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere<ReadableGoodsIssue>(db, keywords);
List<ReadableGoodsIssue> readableGoodsIssues = new List<ReadableGoodsIssue>();
readableGoodsIssues = db.Database
.SqlQuery<ReadableGoodsIssue>(where)
.ToList();
return readableGoodsIssues
.Where(gi => gi.AccountTitle_Id >= openAccountTitle_Id
&& gi.AccountTitle_Id <= closeAccountTitle_Id
&& gi.FluctuatingDate >= startDate
&& gi.FluctuatingDate <= endDate
&& gi.Manufacturer_Id >= openManufacturer_Id
&& gi.Manufacturer_Id <= closeManufacturer_Id
&& gi.ResponsibleStaff_Id >= openResponsibleStaff_Id
&& gi.ResponsibleStaff_Id <= closeResponsibleStaff_Id)
.ToList();
}
else
{
return db.ReadableGoodsIssues
.Where(r => r.AccountTitle_Id >= openAccountTitle_Id
&& r.AccountTitle_Id <= closeAccountTitle_Id
&& r.FluctuatingDate >= startDate
&& r.FluctuatingDate <= endDate
&& r.Manufacturer_Id >= openManufacturer_Id
&& r.Manufacturer_Id <= closeManufacturer_Id
&& r.ResponsibleStaff_Id >= openResponsibleStaff_Id
&& r.ResponsibleStaff_Id <= closeResponsibleStaff_Id)
.ToList();
}
}
}
public List <BeforeWarehousing> GetSpecificWordGroup(int supplier_Id, int manufacturer_Id, string keywords,
DateTime startDate, DateTime endDate)
{
using (DefaultConnection db = new DefaultConnection())
{
int openSupplier_Id = BusinessPartnerData.GetIdRange(db, supplier_Id)[0];
int closeSupplier_Id = BusinessPartnerData.GetIdRange(db, supplier_Id)[1];
int openManufacturer_Id = ManufacturerData.GetIdRange(db, manufacturer_Id)[0];
int closeManufacturer_Id = ManufacturerData.GetIdRange(db, manufacturer_Id)[1];
List <BeforeWarehousing> beforeWarehousings = new List <BeforeWarehousing>();
if (!String.IsNullOrEmpty(keywords))
{
SQLWhereString whereString = new SQLWhereString();
string where = whereString.SearchKeyWhere <BeforeWarehousing>(db, keywords);
beforeWarehousings = db.Database
.SqlQuery <BeforeWarehousing>(where)
.ToList();
beforeWarehousings = beforeWarehousings
.Where(bw => bw.Supplier_Id >= openSupplier_Id &&
bw.Supplier_Id <= closeSupplier_Id &&
bw.Manufacturer_Id >= openManufacturer_Id &&
bw.Manufacturer_Id <= closeManufacturer_Id &&
bw.PurchaseDate >= startDate &&
bw.PurchaseDate <= endDate)
.ToList();
return(beforeWarehousings);
}
else
{
beforeWarehousings = db.BeforeWarehousings
.Where(bw => bw.Supplier_Id >= openSupplier_Id &&
bw.Supplier_Id <= closeSupplier_Id &&
bw.Manufacturer_Id >= openManufacturer_Id &&
bw.Manufacturer_Id <= closeManufacturer_Id &&
bw.PurchaseDate >= startDate &&
bw.PurchaseDate <= endDate)
.ToList();
return(beforeWarehousings);
}
}
}
