Beispiel #1
0
        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);
                }
            }
        }
Beispiel #2
0
        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 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();
         }
     }
 }
Beispiel #4
0
        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());
            }
        }