protected override void MoveLast()
{
if (this.employee == null)
{
this.employee = this.employeeManager.mGetLast();
}
}
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;
}
}
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;
}
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;
}
}
}
private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
{
this.action = "view";
this.employee = this.bindingSourceEmployee.Current as Model.Employee;
this.SetControlValue();
}
public EditForm(Model.Department Department)
: this()
{
this.employee = new Book.Model.Employee();
this.employee.EmployeeId = Guid.NewGuid().ToString();
this.employee.Department = Department;
tag = 1;
}
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));
}
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));
}
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();
}
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));
}
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));
}
public EditForm(Book.Model.Employee emp, string action)
: this()
{
//if(cmpy == null)
// throw new ArithmeticException();
this.employee = emp;
this.action = action;
}
public EditForm(Model.Employee emp)
: this()
{
//if (emp == null)
// throw new ArithmeticException();
this.employee = emp;
this.action = "update";
}
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;
}
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));
}
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;
}
protected override void MoveNext()
{
Model.Employee employee = this.employeeManager.mGetNext(this.employee);
if (employee == null)
{
throw new InvalidOperationException(Properties.Resources.ErrorNoMoreRows);
}
this.employee = employee;
}
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();
}
}
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();
}
//员工编号连接事件
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);
}
}
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();
}
}
/// <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;
}
/// <summary>
/// 添加时判断是否存在
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
public bool ExistsExceptInsert(Book.Model.Employee emp)
{
return(sqlmapper.QueryForObject <bool>("Employee.existsexceptInsert", emp.IDNo));
}
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()));
}
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);
}
protected override void MoveFirst()
{
this.employee = this.employeeManager.mGetFirst();
}
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);
}