Exemple #1
0
 protected override void MoveLast()
 {
     if (this.employee == null)
     {
         this.employee = this.employeeManager.mGetLast();
     }
 }
Exemple #2
0
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            Model.Customer customer = newChooseCustom.EditValue as Model.Customer;

            if (customer == null)
            {
                customer = new Book.Model.Customer();
            }

            Model.Employee emp = this.comboBoxRole.EditValue as Model.Employee;
            if (emp == null)
            {
                emp = new Book.Model.Employee();
            }

            IList <Model.InvoiceXO> invoicesXOs = invoiceXOManager.SelectByYJRQCustomEmpCusXOId(customer, this.dateEditStartDate.DateTime, this.dateEditEndDate.DateTime, emp, null);

            if (invoicesXOs.Count <= 0)
            {
                MessageBox.Show("尚沒有應該處理的訂單。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            XSForm f = new XSForm(invoicesXOs);

            if (f.ShowDialog() == DialogResult.OK)
            {
                this.DialogResult = DialogResult.OK;
            }
        }
Exemple #3
0
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            string cardNo     = this.textEdit1.EditValue == null?null:this.textEdit1.Text;
            string employeeId = null;

            Book.Model.Employee emp = this.newChooseContorl1.EditValue as Book.Model.Employee;
            if (emp != null)
            {
                employeeId = emp.EmployeeId;
            }
            DateTime StartDate = global::Helper.DateTimeParse.NullDate;
            DateTime EndDate   = global::Helper.DateTimeParse.EndDate;

            if (global::Helper.DateTimeParse.DateTimeEquls(this.dateEdit1.DateTime, new DateTime()))
            {
                StartDate = global::Helper.DateTimeParse.NullDate;
            }
            else
            {
                StartDate = this.dateEdit1.DateTime;
            }
            if (global::Helper.DateTimeParse.DateTimeEquls(this.dateEdit2.DateTime, new DateTime()))
            {
                EndDate = global::Helper.DateTimeParse.EndDate;
            }
            else
            {
                EndDate = this.dateEdit2.DateTime;
            }
            UI.Hr.Attendance.TempCard.TempCardEdit._tempCardList = tempCardManager.SelectByCardType(cardNo, employeeId, StartDate, EndDate);
            this.DialogResult = DialogResult.OK;
        }
Exemple #4
0
        private void treeList1_FocusedNodeChanged(object sender, DevExpress.XtraTreeList.FocusedNodeChangedEventArgs e)
        {
            if (e.Node != null && e.Node.ParentNode != null)
            {
                this.employee = employeeManager.Get(e.Node.Tag.ToString());
                try
                {
                }
                catch
                {
                    MessageBox.Show("开始日期填写错误!", "系統提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }


                try
                {
                }
                catch
                {
                    MessageBox.Show("结束日期填写错误!", "系統提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                if (this.employee != null)
                {
                    this.lookUpEmployeeId.EditValue = this.employee.EmployeeId;
                }
            }
        }
Exemple #5
0
        private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
        {
            this.action   = "view";
            this.employee = this.bindingSourceEmployee.Current as Model.Employee;

            this.SetControlValue();
        }
Exemple #6
0
 public EditForm(Model.Department Department)
     : this()
 {
     this.employee            = new Book.Model.Employee();
     this.employee.EmployeeId = Guid.NewGuid().ToString();
     this.employee.Department = Department;
     tag = 1;
 }
Exemple #7
0
 public IList <Book.Model.FamilyMembers> Select(Book.Model.Employee emp)
 {
     if (emp == null)
     {
         return((IList <Model.FamilyMembers>) new List <Model.FamilyMembers>());
     }
     return(sqlmapper.QueryForList <Model.FamilyMembers>("FamilyMembers.select_emp_empid", emp.EmployeeId));
 }
Exemple #8
0
        public bool ExistsExceptUpdate(Book.Model.Employee emp)
        {
            Hashtable paras = new Hashtable();

            paras.Add("newId", emp.IDNo);
            paras.Add("oldId", Get(emp.EmployeeId) == null ? null : Get(emp.EmployeeId).IDNo);
            return(sqlmapper.QueryForObject <bool>("Employee.existsexceptUpdate", paras));
        }
Exemple #9
0
        private void AttendanceDaysSet_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            AttendanceJJDays f = new AttendanceJJDays(Convert.ToDouble(this.employee.AttendanceJJDays), Convert.ToDouble(this.employee.AttendanceDays));

            f.ShowDialog();
            this.employee = this.employeeManager.Get(this.employee.EmployeeId);
            this.Refresh();
        }
Exemple #10
0
        public Book.Model.Employee mGetNext(Book.Model.Employee emp)
        {
            Hashtable ht = new Hashtable();

            ht.Add("InsertTime", emp.InsertTime);
            ht.Add("EmployeeLeaveDate", Helper.DateTimeParse.NullDate.ToString("yyyy-MM-dd"));
            return(sqlmapper.QueryForObject <Model.Employee>("Employee.mGetNext", ht));
        }
Exemple #11
0
        public bool mHasRowsAfter(Book.Model.Employee emp)
        {
            Hashtable ht = new Hashtable();

            ht.Add("InsertTime", emp.InsertTime);
            ht.Add("EmployeeLeaveDate", Helper.DateTimeParse.NullDate.ToString("yyyy-MM-dd"));
            return(sqlmapper.QueryForObject <bool>("Employee.mHasRowsAfter", ht));
        }
Exemple #12
0
        public EditForm(Book.Model.Employee emp, string action)
            : this()
        {
            //if(cmpy == null)
            //    throw new ArithmeticException();
            this.employee = emp;

            this.action = action;
        }
Exemple #13
0
        public EditForm(Model.Employee emp)
            : this()
        {
            //if (emp == null)
            //    throw new ArithmeticException();
            this.employee = emp;

            this.action = "update";
        }
Exemple #14
0
 private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
 {
     this.employee = this.bindingSourceEmployee.Current as Model.Employee;
     if (this.employee == null)
     {
         return;
     }
     lookUpEmployeeId.EditValue = employee.EmployeeId;
 }
Exemple #15
0
        public IList <Book.Model.InvoiceXS> Select(DateTime start, DateTime end, Book.Model.Employee employee)
        {
            Hashtable pars = new Hashtable();

            pars.Add("start", start);
            pars.Add("end", end);
            pars.Add("EmployeeId", employee.EmployeeId);
            return(sqlmapper.QueryForList <Book.Model.InvoiceXS>("InvoiceXS.select_byDateRengeAndEmployee0", pars));
        }
Exemple #16
0
 private void gridView2_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
 {
     this.employee = this.bindingSourceEmployee.Current as Model.Employee;
     if (this.employee == null)
     {
         return;
     }
     this.txt_EmployeeId.Text   = this.employee.IDNo;
     this.txt_EmployeeName.Text = this.employee.EmployeeName;
 }
Exemple #17
0
        protected override void MoveNext()
        {
            Model.Employee employee = this.employeeManager.mGetNext(this.employee);
            if (employee == null)
            {
                throw new InvalidOperationException(Properties.Resources.ErrorNoMoreRows);
            }

            this.employee = employee;
        }
Exemple #18
0
        private void simpleButtonLeave_Click(object sender, EventArgs e)
        {
            LeaveListForm f = new LeaveListForm();

            if (f.ShowDialog(this) == DialogResult.OK)
            {
                this.employee = _employee;
                this.action   = "view";
                this.Refresh();
            }
        }
Exemple #19
0
 protected override void AddNew()
 {
     if (tag != 1)
     {
         this.employee                  = new Model.Employee();
         this.employee.EmployeeId       = Guid.NewGuid().ToString();
         this.employee.EmployeeJoinDate = DateTime.Now;
         //    return;
     }
     tag = 0;
     //this.employee.IDNo = BL.InvoiceManager.GetEmployeeNewId();
 }
Exemple #20
0
 //员工编号连接事件
 private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
 {
     Isconvert  = false;    //默認覆蓋為假
     Notconvert = false;    //默認不覆蓋為假
     _employee  = this.EmployeeSource.Current as Book.Model.Employee;
     if (_employee != null)
     {
         this.FlexTimeSource.DataSource = flextimemanager.getByempid(_employee.EmployeeId);
         this.lookUpEdit1.EditValue     = _employee.EmployeeId;
         this.cmb_business.EditValue    = _employee.BusinessHours.BusinessHoursId;
         //IList<Model.Flextime> list= flextimemanager.getByempid(_employee.EmployeeId);
     }
 }
Exemple #21
0
 protected override void Delete()
 {
     if (this.employee == null)
     {
         return;
     }
     if (MessageBox.Show(Properties.Resources.ConfirmToDelete, this.Text, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
     {
         return;
     }
     this.employeeManager.Delete(this.employee);
     this.employee = this.employeeManager.GetNext(this.employee);
     if (this.employee == null)
     {
         this.employee = this.employeeManager.GetLast();
     }
 }
Exemple #22
0
 /// <summary>
 /// 三个参数的构造函数
 /// </summary>
 /// <param name="employee"></param>
 /// <param name="dueDate"></param>
 public OverTimeForm(MonthSalaryClass ms, Book.Model.Employee emp)
     : this()
 {
     this._ms       = ms;
     this._employee = emp;
 }
Exemple #23
0
 /// <summary>
 /// 添加时判断是否存在
 /// </summary>
 /// <param name="emp"></param>
 /// <returns></returns>
 public bool ExistsExceptInsert(Book.Model.Employee emp)
 {
     return(sqlmapper.QueryForObject <bool>("Employee.existsexceptInsert", emp.IDNo));
 }
Exemple #24
0
        public IList <Model.InvoiceCGDetail> SelectByConditionCO(DateTime startdate, DateTime enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
        {
            StringBuilder sb = new StringBuilder();

            if (startdate != global::Helper.DateTimeParse.NullDate.Date && enddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddDays(-1))
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
            }
            if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
            {
                sb.Append(" AND InvoiceCOId WHERE BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
            }
            if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
            {
                //使用进货日期
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
                //使用采购订单--交货日期
                //sb.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.ToString("yyyy-MM-dd") + "')");
            }
            if (FKStartDate.HasValue && FKEndDate.HasValue)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
            }
            if (!string.IsNullOrEmpty(CusXOid))
            {
                sb.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
            }
            if (startSupplier != null && endSupplier != null)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
            }
            if (productStart != null && productEnd != null)
            {
                sb.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
            }
            if (empstart != null && empend != null)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '' AND ''))");
            }

            return(sqlmapper.QueryForList <Model.InvoiceCGDetail>("InvoiceCGDetail.SelectByConditionCO", sb.ToString()));
        }
Exemple #25
0
        public DataTable SelectByConditionCOBiao(DateTime startdate, DateTime enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
        {
            StringBuilder sb_cg = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceHisDate FROM InvoiceCG WHERE InvoiceId = InvoiceCGDetail.InvoiceId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = InvoiceCGDetail.ProductId) AS ProductName,InvoiceCGDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCGDetailPrice,0) as decimal(10,3)) AS DanJia,ISNULL(InvoiceCGDetailMoney,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,isnull(InvoiceCGDetailTax,0) AS ShuiE,ISNULL(InvoiceCGDetailMoney,0)+isnull(InvoiceCGDetailTax,0) as Total FROM InvoiceCGDetail WHERE 1=1 ");                                                                                                                                                                                                                                                                                                           //进货
            StringBuilder sb_ct = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCT WHERE InvoiceId = InvoiceCTDetail.InvoiceId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = InvoiceCTDetail.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(10,3)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)+ ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 as Total  FROM InvoiceCTDetail WHERE 1=1 ");                                                                                                                                                                                                                                                                              //退货
            //StringBuilder sb_ww = new StringBuilder("SELECT ProduceOtherCompactId AS JHDN,(SELECT ProduceOtherCompactDate FROM ProduceOtherCompact WHERE ProduceOtherCompactId = ProduceOtherCompactDetail.ProduceOtherCompactId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = ProduceOtherCompactDetail.ProductId) AS ProudctName,OtherCompactCount AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(OtherCompactPrice,0) as decimal(10,3)) AS DanJia,ISNULL(OtherCompactMoney,0) AS JinE,'' AS CGorWWDanHao FROM ProduceOtherCompactDetail WHERE 1=1 ");  //委外加工
            StringBuilder sb_sr = new StringBuilder("SELECT ProduceInDepotId AS JHDN,(SELECT ProduceInDepotDate FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = ProduceInDepotDetail.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(10,3)) AS DanJia,isnull(ProduceMoney,0) AS JinE,'' AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*0.05 AS ShuiE,isnull(ProduceMoney,0)*0.05+ isnull(ProduceMoney,0) as Total  FROM ProduceInDepotDetail WHERE 1 = 1 ");                                                                                                                                                                                                                                                                                          //生产入库
            StringBuilder sb_wr = new StringBuilder("SELECT ProduceOtherInDepotId AS JHDN,(SELECT ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDetail.ProduceOtherInDepotId = ProduceOtherInDepot.ProduceOtherInDepotId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = ProduceOtherInDepotDetail.ProductId) AS ProudctName,isnull(ProduceTransferQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(10,3)) AS DanJia,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3)) AS JinE,'' AS CGorWWDanHao,'' AS Inumber,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))*0.05 AS ShuiE,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))+ isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))*0.05 as Total FROM ProduceOtherInDepotDetail WHERE 1 = 1"); //委外入库
            StringBuilder sb_qf = new StringBuilder("SELECT AcOtherShouldPaymentId AS JHDN,(SELECT AcOtherShouldPaymentDate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId) AS JHRQ,LoanName AS ProudctName,AcQuantity AS JHSL,'' AS ProductUnit,cast(ISNULL(AcItemPrice,0) as decimal(10,3)) AS DanJia,AcMoney AS JinE,'' AS CGorWWDanHao,'' AS Inumber,CONVERT(float,(SELECT TOP 1 InvoiceTaxrate  FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS ShuiE,AcMoney+CONVERT(float,(SELECT TOP 1 InvoiceTaxrate  FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS Total FROM AcOtherShouldPaymentDetail WHERE 1 = 1");                                                    //其它应付款

            //日期
            sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "' )");
            sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE ProduceOtherCompactDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE ProduceInDepotDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AcOtherShouldPaymentDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");

            //交货日期
            if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
            {
                //使用采购进货单-- 进货日期
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
                //使用采购订单---交货日期
                //sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            }

            //付款日期
            if (FKStartDate.HasValue && FKEndDate.HasValue)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AdvancePayableDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
            }

            //供应商
            if (startSupplier != null && endSupplier != null)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceCT.InvoiceId FROM InvoiceCT WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_sr.Append(" AND (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) IN ('" + startSupplier.SupplierShortName.Trim() + "','" + endSupplier.SupplierShortName.Trim() + "')");
                sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "'AND '" + endSupplier.Id + "'))");
            }

            //商品
            if (productStart != null && productEnd != null)
            {
                sb_cg.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_ct.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                //sb_ww.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_sr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_wr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
            }

            //采购单号
            if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
            {
                sb_cg.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
                sb_ct.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
            }

            //客户订单编号
            if (!string.IsNullOrEmpty(CusXOid))
            {
                sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
                sb_ct.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
                //sb_ww.Append(" AND CustomInvoiceXOId LIKE '%" + CusXOid + "%'");
            }

            //员工
            if (empstart != null && empend != null)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE Employee1Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
            }

            string sql = sb_cg.ToString() + " UNION ALL " + sb_ct.ToString() + " UNION ALL " + sb_sr.ToString() + " UNION ALL " + sb_wr.ToString() + " UNION ALL " + sb_qf.ToString();

            sql = " SELECT * FROM ( " + sql + " ) a ORDER BY a.JHRQ ASC";

            using (SqlConnection con = new SqlConnection(sqlmapper.DataSource.ConnectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, con);
                DataSet        ds  = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return(ds.Tables[0]);
                }
            }

            return(null);
        }
Exemple #26
0
 protected override void MoveFirst()
 {
     this.employee = this.employeeManager.mGetFirst();
 }
Exemple #27
0
        public override void Refresh()
        {
            if (this.employee == null)
            {
                this.employee            = new Book.Model.Employee();
                this.employee.EmployeeId = Guid.NewGuid().ToString();
                this.action = "insert";
            }
            else
            {
                if (this.action == "view")
                {
                    this.employee = this.employeeManager.Get(employee.EmployeeId);
                }
            }
            if (this.employee == null)
            {
                this.employee            = new Book.Model.Employee();
                this.employee.EmployeeId = Guid.NewGuid().ToString();
                this.action = "insert";
            }
            if (this.employee.EmployeePhoto != null && this.employee.EmployeePhoto.Length > 0)
            {
                this.pictureEdit1.Image = Image.FromStream(new System.IO.MemoryStream(this.employee.EmployeePhoto));
            }
            else
            {
                this.pictureEdit1.Image          = null;
                this.buttonEditPictrue.EditValue = null;
            }

            this.bindingSourceEmployee.DataSource = this.employeeManager.SelectOnActive();
            this.bindingSourceEmployee.Position   = this.bindingSourceEmployee.IndexOf(this.employee);
            //设置控件值
            this.SetControlValue();
            switch (this.action)
            {
            case "insert":
                #region Controls State
                this.buttonEditPictrue.Enabled                      = true;
                this.buttonEditPictrue.Properties.ReadOnly          = false;
                this.textEditIDNo.Properties.ReadOnly               = false;
                this.textEditEmployeeName.Properties.ReadOnly       = false;
                this.dateEditEmployeeJoinDate.Properties.ReadOnly   = false;
                this.dateEditEmployeeLeaveDate.Properties.ReadOnly  = false;
                this.textEditCardNo.Properties.ReadOnly             = false;
                this.comboBoxEditDepartmentId.Properties.ReadOnly   = false;
                this.textEditEmployeeIdentityNO.Properties.ReadOnly = false;
                //this.comboBoxEditDutyId.Properties.ReadOnly = false;
                this.dateEditEmployeeBirthday.Properties.ReadOnly         = false;
                this.comboBoxEditBusinessHoursId.Properties.ReadOnly      = false;
                this.textEditContactPhone.Properties.ReadOnly             = false;
                this.comboBoxEditCompanyId.Properties.ReadOnly            = false;
                this.comboBoxEditBankId.Properties.ReadOnly               = false;
                this.textEditBankAccount.Properties.ReadOnly              = false;
                this.textEditCellphone.Properties.ReadOnly                = false;
                this.textEditContactAddress.Properties.ReadOnly           = false;
                this.comboBoxEditEmployeeBloodType.Properties.ReadOnly    = false;
                this.comboBoxEditEmployeeGender.Properties.ReadOnly       = false;
                this.comboBoxEditAcademicBackGroundId.Properties.ReadOnly = false;
                this.textEditUrgentContact.Properties.ReadOnly            = false;
                this.textEditUrgentPhone.Properties.ReadOnly              = false;
                this.comboBoxEditEmployeeMarried.Properties.ReadOnly      = false;
                this.comboBoxEditMilitaryState.Properties.ReadOnly        = false;
                this.textEditEmployeeNativePlace.Properties.ReadOnly      = false;
                //this.checkEditIsCadre.Properties.ReadOnly = false;
                this.textEditEmployeeExperience.Properties.ReadOnly = false;
                this.pictureEdit1.Properties.ReadOnly = false;
                #endregion
                break;

            case "update":
                #region Controls State
                this.buttonEditPictrue.Enabled                      = true;
                this.buttonEditPictrue.Properties.ReadOnly          = false;
                this.textEditIDNo.Properties.ReadOnly               = false;
                this.textEditEmployeeName.Properties.ReadOnly       = false;
                this.dateEditEmployeeJoinDate.Properties.ReadOnly   = false;
                this.dateEditEmployeeLeaveDate.Properties.ReadOnly  = false;
                this.textEditCardNo.Properties.ReadOnly             = false;
                this.comboBoxEditDepartmentId.Properties.ReadOnly   = false;
                this.textEditEmployeeIdentityNO.Properties.ReadOnly = false;
                //this.comboBoxEditDutyId.Properties.ReadOnly = false;
                this.dateEditEmployeeBirthday.Properties.ReadOnly         = false;
                this.comboBoxEditBusinessHoursId.Properties.ReadOnly      = false;
                this.textEditContactPhone.Properties.ReadOnly             = false;
                this.comboBoxEditCompanyId.Properties.ReadOnly            = false;
                this.comboBoxEditBankId.Properties.ReadOnly               = false;
                this.textEditBankAccount.Properties.ReadOnly              = false;
                this.textEditCellphone.Properties.ReadOnly                = false;
                this.textEditContactAddress.Properties.ReadOnly           = false;
                this.comboBoxEditEmployeeBloodType.Properties.ReadOnly    = false;
                this.comboBoxEditEmployeeGender.Properties.ReadOnly       = false;
                this.comboBoxEditAcademicBackGroundId.Properties.ReadOnly = false;
                this.textEditUrgentContact.Properties.ReadOnly            = false;
                this.textEditUrgentPhone.Properties.ReadOnly              = false;
                this.comboBoxEditEmployeeMarried.Properties.ReadOnly      = false;
                this.comboBoxEditMilitaryState.Properties.ReadOnly        = false;
                this.textEditEmployeeNativePlace.Properties.ReadOnly      = false;
                //this.checkEditIsCadre.Properties.ReadOnly = false;
                this.textEditEmployeeExperience.Properties.ReadOnly = false;
                this.pictureEdit1.Properties.ReadOnly = false;
                #endregion
                break;

            case "view":
                #region Controls State
                this.buttonEditPictrue.Enabled                      = false;
                this.buttonEditPictrue.Properties.ReadOnly          = true;
                this.textEditIDNo.Properties.ReadOnly               = true;
                this.textEditEmployeeName.Properties.ReadOnly       = true;
                this.dateEditEmployeeJoinDate.Properties.ReadOnly   = true;
                this.dateEditEmployeeLeaveDate.Properties.ReadOnly  = true;
                this.textEditCardNo.Properties.ReadOnly             = true;
                this.comboBoxEditDepartmentId.Properties.ReadOnly   = true;
                this.textEditEmployeeIdentityNO.Properties.ReadOnly = true;
                //this.comboBoxEditDutyId.Properties.ReadOnly = true;
                this.dateEditEmployeeBirthday.Properties.ReadOnly         = true;
                this.comboBoxEditBusinessHoursId.Properties.ReadOnly      = true;
                this.textEditContactPhone.Properties.ReadOnly             = true;
                this.comboBoxEditCompanyId.Properties.ReadOnly            = true;
                this.comboBoxEditBankId.Properties.ReadOnly               = true;
                this.textEditBankAccount.Properties.ReadOnly              = true;
                this.textEditCellphone.Properties.ReadOnly                = true;
                this.textEditContactAddress.Properties.ReadOnly           = true;
                this.comboBoxEditEmployeeBloodType.Properties.ReadOnly    = true;
                this.comboBoxEditEmployeeGender.Properties.ReadOnly       = true;
                this.comboBoxEditAcademicBackGroundId.Properties.ReadOnly = true;
                this.textEditUrgentContact.Properties.ReadOnly            = true;
                this.textEditUrgentPhone.Properties.ReadOnly              = true;
                this.comboBoxEditEmployeeMarried.Properties.ReadOnly      = true;
                this.comboBoxEditMilitaryState.Properties.ReadOnly        = true;
                this.textEditEmployeeNativePlace.Properties.ReadOnly      = true;
                //this.checkEditIsCadre.Properties.ReadOnly = true;
                this.textEditEmployeeExperience.Properties.ReadOnly = true;
                this.pictureEdit1.Properties.ReadOnly = true;
                #endregion
                break;

            default:
                break;
            }
            base.Refresh();
            if (this.action == "view")
            {
                this.simpleButtonOn.Enabled    = true;
                this.simpleButtonLeave.Enabled = true;
            }
            else
            {
                this.simpleButtonOn.Enabled    = false;
                this.simpleButtonLeave.Enabled = false;
            }
        }
        public IList <Book.Model.InvoiceCODetail> Select(string costartid, string coendid, Book.Model.Supplier SupplierStart, Book.Model.Supplier SupplierEnd, DateTime?dateStart, DateTime?dateEnd, Book.Model.Product productStart, Book.Model.Product productEnd, string cusxoid, DateTime dateJHStart, DateTime dateJHEnd, int?invoiceFlag, Book.Model.Employee empStart, Book.Model.Employee empEnd)
        {
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(costartid) && !string.IsNullOrEmpty(coendid))
            {
                sb.Append(" AND InvoiceId BETWEEN '" + costartid + "' AND '" + coendid + "' ");
            }
            if (SupplierStart != null && SupplierEnd != null)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE SupplierId BETWEEN '" + SupplierStart.SupplierId + "' AND '" + SupplierEnd.SupplierId + "')");
            }
            if (dateStart.HasValue && dateEnd.HasValue)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceDate BETWEEN '" + dateStart.Value.ToString("yyyy-MM-dd") + "' AND '" + dateEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") + "')");
            }
            if (productStart != null && productEnd != null)
            {
                sb.Append(" AND ProductId BETWEEN '" + productStart.ProductId + "' AND '" + productEnd.ProductId + "'");
            }
            if (dateJHStart != null && dateJHEnd != null)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceYjrq BETWEEN '" + dateJHStart.ToString("yyyy-MM-dd") + "' AND '" + dateJHEnd.ToString("yyyy-MM-dd HH:mm:ss") + "')");
            }
            if (invoiceFlag.HasValue && invoiceFlag.Value == 1)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE IsClose=0)");
            }
            if (!string.IsNullOrEmpty(cusxoid))
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + cusxoid + "%')");
            }
            if (empStart != null && empEnd != null)
            {
                sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empStart.IDNo + "' AND '" + empEnd.IDNo + "'))");
            }
            sb.Append(" ORDER BY InvoiceId DESC");

            return(sqlmapper.QueryForList <Model.InvoiceCODetail>("InvoiceCODetail.SelectByConditionCO", sb.ToString()));
        }
        //应付账款明细表
        public DataTable SelectByConditionCOBiao(DateTime?startdate, DateTime?enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
        {
            StringBuilder sb_cg = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCG WHERE InvoiceId = InvoiceCGDetail.InvoiceId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = InvoiceCGDetail.ProductId) AS ProductName,InvoiceCGDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCGDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL(InvoiceCGDetailMoney,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,isnull(InvoiceCGDetailTax,0) AS ShuiE,ISNULL(InvoiceCGDetailMoney,0)+isnull(InvoiceCGDetailTax,0) as Total FROM InvoiceCGDetail WHERE 1=1 ");            //进货
            //StringBuilder sb_ct = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCT WHERE InvoiceId = InvoiceCTDetail.InvoiceId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = InvoiceCTDetail.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)+ ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 as Total  FROM InvoiceCTDetail WHERE 1=1 ");            //退货
            StringBuilder sb_ct = new StringBuilder("SELECT ctd.InvoiceId AS JHDN,ct.InvoiceDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = ctd.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,ctd.InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*((case ct.InvoiceKslb when '外加' then ct.InvoiceTaxRate else 0 end)/100) AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)*(1+((case ct.InvoiceKslb when '外加' then ct.InvoiceTaxRate else 0 end)/100)) as Total  FROM InvoiceCTDetail ctd left join InvoiceCT ct on ctd.InvoiceId=ct.InvoiceId WHERE 1=1 ");
            //StringBuilder sb_ww = new StringBuilder("SELECT ProduceOtherCompactId AS JHDN,(SELECT ProduceOtherCompactDate FROM ProduceOtherCompact WHERE ProduceOtherCompactId = ProduceOtherCompactDetail.ProduceOtherCompactId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = ProduceOtherCompactDetail.ProductId) AS ProudctName,OtherCompactCount AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(OtherCompactPrice,0) as decimal(10,4)) AS DanJia,ISNULL(OtherCompactMoney,0) AS JinE,'' AS CGorWWDanHao FROM ProduceOtherCompactDetail WHERE 1=1 ");  //委外加工
            //StringBuilder sb_sr = new StringBuilder("SELECT ProduceInDepotId AS JHDN,(SELECT ProduceInDepotDate FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = ProduceInDepotDetail.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceMoney,0) AS JinE,PronoteHeaderId AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*0.05 AS ShuiE,isnull(ProduceMoney,0)*0.05+ isnull(ProduceMoney,0) as Total  FROM ProduceInDepotDetail WHERE 1 = 1 ");  //生产入库
            StringBuilder sb_sr = new StringBuilder("SELECT pid.ProduceInDepotId AS JHDN,pi.ProduceInDepotDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = pid.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceMoney,0) AS JinE,PronoteHeaderId AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*(cast(ISNULL(pi.InvoiceTaxrate,0) as decimal(18,2))/100) AS ShuiE,isnull(ProduceMoney,0)*(1+(cast(ISNULL(pi.InvoiceTaxrate,0) as decimal(18,2))/100)) as Total  FROM ProduceInDepotDetail pid left join ProduceInDepot pi on pid.ProduceInDepotId=pi.ProduceInDepotId WHERE 1 = 1 ");                                                                                                                                                                                  //生产入库
            //StringBuilder sb_wr = new StringBuilder("SELECT ProduceOtherInDepotId AS JHDN,(SELECT ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDetail.ProduceOtherInDepotId = ProduceOtherInDepot.ProduceOtherInDepotId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = ProduceOtherInDepotDetail.ProductId) AS ProudctName,isnull(ProduceQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*0.05 AS ShuiE,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))+ isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*0.05 as Total FROM ProduceOtherInDepotDetail WHERE 1 = 1");//委外入库
            StringBuilder sb_wr = new StringBuilder("SELECT pod.ProduceOtherInDepotId AS JHDN,po.ProduceOtherInDepotDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = pod.ProductId) AS ProudctName,isnull(ProduceQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS JinE,pod.ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*cast(ISNULL(InvoiceTaxrate,0) as decimal(18,2))/100 AS ShuiE,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*(1+cast(ISNULL(InvoiceTaxrate,0) as decimal(18,2))/100) as Total FROM ProduceOtherInDepotDetail pod left join ProduceOtherInDepot po on pod.ProduceOtherInDepotId=po.ProduceOtherInDepotId WHERE 1 = 1"); //2020年12月10日23:20:42:委外入库,改left join

            //StringBuilder sb_wt = new StringBuilder("SELECT ProduceOtherReturnMaterialId  AS JHDN,(SELECT ProduceOtherReturnMaterialDate FROM ProduceOtherReturnMaterial WHERE ProduceOtherReturnMaterial.ProduceOtherReturnMaterialId=pd.ProduceOtherReturnMaterialId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId=pd.ProductId) AS ProductName,Quantity AS JHSL,ProductUnit AS ProductUnit,cast(isnull(Price,0) AS decimal(18,4)) AS DanJia,isnull((0-Amount),0) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull((0-Amount),0)*0.05 AS ShuiE,isnull((0-Amount),0)*0.05+isnull((0-Amount),0) AS Total FROM ProduceOtherReturnDetail  pd WHERE 1=1");//委外退库
            StringBuilder sb_wt = new StringBuilder("SELECT pd.ProduceOtherReturnMaterialId  AS JHDN,pm.ProduceOtherReturnMaterialDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId=pd.ProductId) AS ProductName,Quantity AS JHSL,ProductUnit AS ProductUnit,cast(isnull(Price,0) AS decimal(18,4)) AS DanJia,isnull((0-Amount),0) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull((0-Amount),0)*(CAST(ISNULL(pm.InvoiceTaxrate,0) as decimal(18,2))/100) AS ShuiE,isnull((0-Amount),0)*(1+(CAST(ISNULL(pm.InvoiceTaxrate,0) as decimal(18,2))/100)) AS Total FROM ProduceOtherReturnDetail  pd left join ProduceOtherReturnMaterial pm on pd.ProduceOtherReturnMaterialId=pm.ProduceOtherReturnMaterialId WHERE 1=1 ");                                                                                                      //委外退库
            StringBuilder sb_qf = new StringBuilder("SELECT AcOtherShouldPaymentId AS JHDN,(SELECT AcOtherShouldPaymentDate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId) AS JHRQ,LoanName AS ProudctName,AcQuantity AS JHSL,'' AS ProductUnit,cast(ISNULL(AcItemPrice,0) as decimal(18,4)) AS DanJia,AcMoney AS JinE,'' AS CGorWWDanHao,'' AS Inumber,CONVERT(float,(SELECT TOP 1 InvoiceTaxrate  FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS ShuiE,AcMoney+CONVERT(float,(SELECT TOP 1 InvoiceTaxrate  FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS Total FROM AcOtherShouldPaymentDetail WHERE 1 = 1 "); //其它应付款

            //日期
            if (startdate.HasValue && enddate.HasValue)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "' )");
                //sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_ct.Append(" AND ct.InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE ProduceOtherCompactDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
                //sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE ProduceInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_sr.Append(" AND pi.ProduceInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                //sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_wr.Append(" AND po.ProduceOtherInDepotDate  BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AcOtherShouldPaymentDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                //sb_wt.Append(" AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE ProduceOtherReturnMaterialDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_wt.Append(" AND pm.ProduceOtherReturnMaterialDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
            }
            //交货日期
            if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
            {
                //使用采购进货单-- 进货日期
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
                //使用采购订单---交货日期
                //sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
            }

            //付款日期
            if (FKStartDate.HasValue && FKEndDate.HasValue)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AdvancePayableDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                //sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_ct.Append(" AND ct.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                //sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_sr.Append(" AND pi.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                //sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_wr.Append(" AND po.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                //sb_wt.Append(" AND ProduceOtherReturnMaterialId in (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
                sb_wt.Append(" AND pm.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
            }

            //供应商
            if (startSupplier != null && endSupplier != null)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                //sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceCT.InvoiceId FROM InvoiceCT WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_ct.Append(" AND ct.SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
                //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                //sb_sr.Append(" AND (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) IN ('" + startSupplier.SupplierShortName.Trim() + "','" + endSupplier.SupplierShortName.Trim() + "')");
                //sb_sr.Append(" and EXISTS (select SupplierFullName from supplier where SupplierFullName like (SELECT '%'+WorkHouse.Workhousename+'%' FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) AND SupplierFullName in ('" + startSupplier.SupplierFullName.Trim() + "','" + endSupplier.SupplierFullName.Trim() + "'))");
                sb_sr.Append("  and (EXISTS (select SupplierFullName from supplier where SupplierFullName like (SELECT '%'+WorkHouse.Workhousename+'%' FROM WorkHouse WHERE WorkHouse.WorkHouseId = pi.WorkHouseId) AND SupplierFullName in ('" + startSupplier.SupplierFullName.Trim() + "','" + endSupplier.SupplierFullName.Trim() + "')) or EXISTS (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = pi.WorkHouseId and (Workhousename  like '%" + startSupplier.SupplierFullName.Trim() + "%' or  Workhousename like '%" + endSupplier.SupplierFullName.Trim() + "%')))");
                //sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_wr.Append(" AND po.SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "'AND '" + endSupplier.Id + "'))");
                //sb_wt.Append(" AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
                sb_wt.Append(" AND pm.SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
            }

            //商品
            if (productStart != null && productEnd != null)
            {
                sb_cg.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_ct.Append(" AND ctd.ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                //sb_ww.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_sr.Append(" AND pid.ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_wr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
                sb_wt.Append(" AND pd.ProductId IN (SELECT ProductId FROM product WHERE id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
            }

            //采购单号
            if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
            {
                sb_cg.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
                sb_ct.Append(" AND ctd.InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
            }

            //客户订单编号
            if (!string.IsNullOrEmpty(CusXOid))
            {
                sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
                sb_ct.Append(" AND ctd.InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
                //sb_ww.Append(" AND CustomInvoiceXOId LIKE '%" + CusXOid + "%'");
            }

            //员工
            if (empstart != null && empend != null)
            {
                sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                //sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_ct.Append("  AND ct.Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
                //sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                //sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_sr.Append(" AND pi.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
                //sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_wr.Append(" AND po.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
                sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE Employee1Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                //sb_wt.Append("  AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
                sb_wt.Append(" AND pm.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
            }

            string sql = sb_cg.ToString() + " UNION ALL " + sb_ct.ToString() + " UNION ALL " + sb_sr.ToString() + " UNION ALL " + sb_wr.ToString() + " UNION ALL " + sb_qf.ToString() + " UNION ALL " + sb_wt.ToString();

            sql = " SELECT * FROM ( " + sql + " ) a ORDER BY a.JHRQ ASC";

            using (SqlConnection con = new SqlConnection(sqlmapper.DataSource.ConnectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, con);
                DataSet        ds  = new DataSet();
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return(ds.Tables[0]);
                }
            }

            return(null);
        }