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 ActionResult BusinessPartnerContent(string initial)
{
if (Request.IsAjaxRequest())
{
BusinessPartnerData data = new BusinessPartnerData();
var results = data.GetAll(initial);
if (results.Count() == 0)
{
return(PartialView("_NoResult"));
}
else
{
return(PartialView("_BusinessPartnerContent", results));
}
}
return(Content("Ajax通信以外のアクセスはできません"));
}
//見積データの追加
public void Create(string customer, string helper, string staff, string[] products_Id)
{
using (DefaultConnection db = new DefaultConnection())
{
int[] customer_Ids = BusinessPartnerData.NameToId(db, customer);
int[] helper_Ids = HelperData.NameToId(db, helper);
int customer_Id = customer_Ids[0];
int helper_Id = helper_Ids[0];
int product_Id = 0;
int staff_Id = StaffData.EmailToId(db, staff);
foreach (var item in products_Id)
{
product_Id = int.Parse(item);
Quotation quotation = new Quotation(customer_Id, helper_Id, product_Id, staff_Id);
db.Quotations.Add(quotation);
db.SaveChanges();
}
}
}
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);
}
}
}
public List <ReadableJournal> GetSpecificWordGroup(string businessPartner, string debit, string credit, string keywords,
DateTime startDate, DateTime endDate)
{
List <ReadableJournal> readableJournals = new List <ReadableJournal>();
using (DefaultConnection db = new DefaultConnection())
{
int[] businessPartner_Ids = BusinessPartnerData.NameToId(db, businessPartner);
int openBusinessPartner_Id = businessPartner_Ids[0];
int closeBusinessPartner_Id = businessPartner_Ids[1];
int[] debit_Ids = AccountTitleData.NameToId(db, debit);
int openDebit_Id = debit_Ids[0];
int closeDebit_Id = debit_Ids[1];
int[] credit_Ids = AccountTitleData.NameToId(db, credit);
int openCredit_Id = credit_Ids[0];
int closeCredit_Id = credit_Ids[1];
var anonymous = db.Journals
.Where(j => j.BusinessPartner_Id >= openBusinessPartner_Id &&
j.BusinessPartner_Id <= closeBusinessPartner_Id &&
j.Debit_Id >= openDebit_Id &&
j.Debit_Id <= closeDebit_Id &&
j.Credit_Id >= openCredit_Id &&
j.Credit_Id <= closeCredit_Id &&
j.AccountingDate >= startDate &&
j.AccountingDate <= endDate)
.Select(j => new
{
j.Id,
j.AccountingDate,
j.CurrentAccountReflectingDate,
j.BusinessPartner_Id,
j.BusinessPartner.CommonName,
j.Credit_Id,
Credit = j.CreditTitle.AccountName,
j.Debit_Id,
Debit = j.DebitTitle.AccountName,
j.Amount,
j.Tax,
j.Apply,
j.FinancialInstitution_Id,
FinancialInstitution = j.FinancialInstitution.FinancialInstitutionName,
j.FinancialInstitutionBranch_Id,
FinancialInstitutionBranche = j.FinancialInstitutionBranche.Branch,
j.BillStatus_Id,
j.BillNo,
j.PaymentDate,
j.IssuedFinancialInstitution_Id,
IssuedFinancialInstitution = j.IssuedFinancialInstitution.FinancialInstitutionName,
j.IssuedFinancialInstitutionBranch_Id,
IssuedFinancialInstitutionBranche = j.IssuedFinancialInstitutionBranche.Branch,
j.Transferee_Id,
Transferee = j.Transferee.CommonName,
j.EndorsementTransferDate,
j.Note
})
.ToList();
if (String.IsNullOrEmpty(keywords))
{
string[] keywordArray = keywords.Split(new[] { ' ', ' ' });
foreach (var item in keywordArray)
{
anonymous = anonymous
.Where(a => a.Apply.Contains(item))
.ToList();
}
}
return(anonymous
.OrderByDescending(a => a.AccountingDate)
.Select(a => new ReadableJournal
{
Id = a.Id,
AccountingDate = a.AccountingDate,
BusinessPartner_Id = a.BusinessPartner_Id,
BusinessPartner = a.CommonName,
Credit_Id = a.Credit_Id,
Credit = a.Credit,
Debit_Id = a.Debit_Id,
Debit = a.Debit,
Amount = a.Amount,
Tax = a.Tax,
Apply = a.Apply,
FinancialInstitution_Id = a.FinancialInstitution_Id,
FinancialInstitution = a.FinancialInstitution,
FinancialInstitutionBranch_Id = a.FinancialInstitutionBranch_Id,
FinancialInstitutionBranch = a.FinancialInstitutionBranche,
BillStatuses = a.BillStatus_Id,
BillNo = a.BillNo,
PaymentDate = a.PaymentDate,
IssuedFinancialInstitution_Id = a.IssuedFinancialInstitution_Id,
IssuedFinancialInstitution = a.IssuedFinancialInstitution,
IssuedFinancialInstitutionBranch_Id = a.IssuedFinancialInstitutionBranch_Id,
IssuedFinancialInstitutionBranch = a.IssuedFinancialInstitutionBranche,
Transferee_Id = a.Transferee_Id,
Transferee = a.Transferee,
EndorsementTransferDate = a.EndorsementTransferDate,
Note = a.Note
})
.ToList());
}
}
public ActionResult BusinessPartnerName(string term)
{
BusinessPartnerData data = new BusinessPartnerData();
return(Json(data.GetNames(term), JsonRequestBehavior.AllowGet));
}
