//往来单位2选择器 private void txtcCustName2_ChooserClick(object sender, EventArgs e) { if (CommonCtrl.IsNullToString(cboOrderType.SelectedValue) == "") { return; } DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(cboOrderType.SelectedValue); switch (enumAccount) { case DataSources.EnumAccountVerification.YuShouToYingShou: //预收冲应收 case DataSources.EnumAccountVerification.YingShouToYingShou: //应收转应收 case DataSources.EnumAccountVerification.YingFuToYingShou: //应付冲应收 case DataSources.EnumAccountVerification.YuShouToYuShou: //预收转预收 frmCustomerInfo frmCustomer = new frmCustomerInfo(); if (frmCustomer.ShowDialog() == DialogResult.OK) { txtcCustName2.Tag = frmCustomer.strCustomerId; txtcCustName2.Text = frmCustomer.strCustomerName; } break; case DataSources.EnumAccountVerification.YuFuToYingFu: //预付冲应付 case DataSources.EnumAccountVerification.YingFuToYingFu: //应付转应付 case DataSources.EnumAccountVerification.YingShouToYingFu: //应收冲应付 case DataSources.EnumAccountVerification.YuFuToYuFu: //预付转预付 frmSupplier frmSupp = new frmSupplier(); if (frmSupp.ShowDialog() == DialogResult.OK) { txtcCustName2.Tag = frmSupp.supperID; txtcCustName2.Text = frmSupp.supperName; } break; } }
//选择单据类型 private void cboOrderType_SelectedIndexChanged(object sender, EventArgs e) { if (CommonCtrl.IsNullToString(cboOrderType.SelectedValue) == "") { return; } #region 清空项 txtcCustName1.Tag = null; txtcCustName1.Text = string.Empty; txtcCustName2.Tag = null; txtcCustName2.Text = string.Empty; #endregion DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(cboOrderType.SelectedValue); #region 各个状态的显示 switch (enumAccount) { case DataSources.EnumAccountVerification.YuShouToYingShou: lblCustName1.Text = "预收客户:"; lblCustName2.Text = "应收客户:"; break; case DataSources.EnumAccountVerification.YuFuToYingFu: lblCustName1.Text = "预收供应商:"; lblCustName2.Text = "应收供应商:"; break; case DataSources.EnumAccountVerification.YingFuToYingFu: lblCustName1.Text = "转出供应商:"; lblCustName2.Text = "转入供应商:"; break; case DataSources.EnumAccountVerification.YingShouToYingShou: lblCustName1.Text = "转出客户:"; lblCustName2.Text = "转入客户:"; break; case DataSources.EnumAccountVerification.YingShouToYingFu: lblCustName1.Text = "客户:"; lblCustName2.Text = "供应商:"; break; case DataSources.EnumAccountVerification.YingFuToYingShou: lblCustName1.Text = "供应商:"; lblCustName2.Text = "客户:"; break; case DataSources.EnumAccountVerification.YuShouToYuShou: lblCustName1.Text = "转出客户:"; lblCustName2.Text = "转入客户:"; break; case DataSources.EnumAccountVerification.YuFuToYuFu: lblCustName1.Text = "转出供应商:"; lblCustName2.Text = "转入供应商:"; break; } #endregion }
/// <summary> /// 往来核销重新计算单据的已结算,待结算金额 /// </summary> /// <param name="enumAccount">单据类型</param> /// <param name="id">单据ID</param> /// <param name="list"></param> public static void VerificationDocumentMoney(DataSources.EnumAccountVerification enumAccount, string id, List <SysSQLString> list) { //不为预收转预收,预付转预付的情况,重新计算单据的已结算,待结算金额 if (enumAccount != DataSources.EnumAccountVerification.YuShouToYuShou && enumAccount != DataSources.EnumAccountVerification.YuFuToYuFu) { SysSQLString sqlSettlement = new SysSQLString(); sqlSettlement.cmdType = CommandType.Text; sqlSettlement.Param = new Dictionary <string, string>(); sqlSettlement.Param.Add("order_id", id); string orderIndex = "1"; //应收Sql string yingShouSql = @"update a set settled_money=b.money,wait_settled_money=a.money-ISNULL(b.money,0) from tb_verificationn_documents a left join v_YingShou b on a.order_id=b.documents_id where a.account_verification_id=@order_id and order_index=@order_index"; //应付Sql string yingFuSql = @"update a set settled_money=b.money,wait_settled_money=a.money-ISNULL(b.money,0) from tb_verificationn_documents a left join v_YingFu b on a.order_id=b.documents_id where a.account_verification_id=@order_id and order_index=@order_index"; //预收冲应收,应收转应收 if (enumAccount == DataSources.EnumAccountVerification.YuShouToYingShou || enumAccount == DataSources.EnumAccountVerification.YingShouToYingShou) { sqlSettlement.sqlString = yingShouSql; } //预付冲应付,应付转应付 else if (enumAccount == DataSources.EnumAccountVerification.YuFuToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingFu) { sqlSettlement.sqlString = yingFuSql; } //应收冲应付,应付冲应收 if (enumAccount == DataSources.EnumAccountVerification.YingShouToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { //应收已结算金额 sqlSettlement.sqlString = yingShouSql; //应付已结算金额 SysSQLString sqlSettlement1 = new SysSQLString(); sqlSettlement1.cmdType = CommandType.Text; sqlSettlement1.Param = new Dictionary <string, string>(); sqlSettlement1.Param.Add("order_id", id); sqlSettlement1.sqlString = yingFuSql; if (enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { orderIndex = "2"; sqlSettlement1.Param.Add("order_index", "1"); } else { sqlSettlement1.Param.Add("order_index", "2"); } list.Add(sqlSettlement1); } sqlSettlement.Param.Add("order_index", orderIndex); list.Add(sqlSettlement); } }
//往来单位1选择器 private void txtcCustName1_ChooserClick(object sender, EventArgs e) { //if (CommonCtrl.IsNullToString(cboOrderType.SelectedValue) == "") //{ // return; //} frmBtype frmUints = new frmBtype(); if (frmUints.ShowDialog() == DialogResult.OK) { txtcCustName1.Text = frmUints.BtypeName; } return; DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(cboOrderType.SelectedValue); switch (enumAccount) { case DataSources.EnumAccountVerification.YuShouToYingShou: //预收冲应收 case DataSources.EnumAccountVerification.YingShouToYingShou: //应收转应收 case DataSources.EnumAccountVerification.YingShouToYingFu: //应收冲应付 case DataSources.EnumAccountVerification.YuShouToYuShou: //预收转预收 frmCustomerInfo frmCustomer = new frmCustomerInfo(); if (frmCustomer.ShowDialog() == DialogResult.OK) { txtcCustName1.Tag = frmCustomer.strCustomerId; txtcCustName1.Text = frmCustomer.strCustomerName; if (txtcCustName2.Tag == null && enumAccount != DataSources.EnumAccountVerification.YingShouToYingFu) //没有选择应收客户,默认选择与预收客户相同 { txtcCustName2.Tag = frmCustomer.strCustomerId; txtcCustName2.Text = frmCustomer.strCustomerName; } } break; case DataSources.EnumAccountVerification.YuFuToYingFu: //预付冲应付 case DataSources.EnumAccountVerification.YingFuToYingFu: //应付转应付 case DataSources.EnumAccountVerification.YingFuToYingShou: //应付冲应收 case DataSources.EnumAccountVerification.YuFuToYuFu: //预付转预付 frmSupplier frmSupp = new frmSupplier(); if (frmSupp.ShowDialog() == DialogResult.OK) { txtcCustName1.Tag = frmSupp.supperID; txtcCustName1.Text = frmSupp.supperName; if (txtcCustName2.Tag == null && enumAccount != DataSources.EnumAccountVerification.YingFuToYingShou) { txtcCustName2.Tag = frmSupp.supperID; txtcCustName2.Text = frmSupp.supperName; } } break; } }
//提交,只有草稿状态才可提交 void SubmitData() { dgvVerification.EndEdit(); List <SysSQLString> listSql = new List <SysSQLString>(); string submit = ((int)DataSources.EnumAuditStatus.SUBMIT).ToString(); //提交 string draft = ((int)DataSources.EnumAuditStatus.DRAFT).ToString(); //草稿 string notAudit = ((int)DataSources.EnumAuditStatus.NOTAUDIT).ToString(); //审核失败 foreach (DataGridViewRow dgvr in dgvVerification.Rows) { object isCheck = dgvr.Cells[colChk.Name].Value; string status = CommonCtrl.IsNullToString(dgvr.Cells[colOrderStatus.Name].Tag); if (isCheck != null && (bool)isCheck && (status == draft || status == notAudit)) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary <string, string>(); sql.Param.Add("submit", submit); sql.Param.Add("draft", draft); sql.Param.Add("notAudit", notAudit); sql.Param.Add("order_num", CommonUtility.GetNewNo(DataSources.EnumProjectType.PAYMENT)); string order_id = dgvr.Cells[colAccountVerificationID.Name].Value.ToString(); sql.Param.Add("account_verification_id", order_id); sql.sqlString = "update tb_account_verification set order_status=@submit,order_num=@order_num where account_verification_id=@account_verification_id and (order_status=@draft or order_status=@notAudit)"; listSql.Add(sql); SetDocumentImportStatus("is_lock", DataSources.EnumImportStaus.LOCK, listSql, order_id, dgvr.Cells[colOrderType.Name].Tag.ToString()); DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(dgvr.Cells[colOrderType.Name].Tag); Financial.DocumentSettlementByVerification(enumAccount, order_id, listSql); } } if (listSql.Count == 0) { MessageBoxEx.Show("请选择要提交的数据!"); return; } if (!MessageBoxEx.ShowQuestion("是否要提交选择的数据!")) { return; } if (DBHelper.BatchExeSQLStringMultiByTrans("提交应收应付", listSql)) { MessageBoxEx.Show("提交成功!"); BindData(); } else { MessageBoxEx.ShowWarning("提交失败!"); } }
/// <summary> /// 设置单据导入状态 /// </summary> void SetDocumentImportStatus(string statusName, DataSources.EnumImportStaus importStaus, List <SysSQLString> listSql, string order_id, string order_type) { DataTable dt = DBHelper.GetTable("", "tb_verificationn_documents", "order_id", string.Format("account_verification_id='{0}'", order_id), "", ""); if (dt == null || dt.Rows.Count == 0) { return; } string ids = string.Empty; foreach (DataRow dr in dt.Rows) { ids += string.Format("'{0}',", dr["order_id"]); } ids = ids.TrimEnd(','); string strImportStatus = ((int)importStaus).ToString(); DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(order_type); switch (enumAccount) { case DataSources.EnumAccountVerification.YuShouToYingShou: //预收冲应收 case DataSources.EnumAccountVerification.YingShouToYingShou: //应收转应收 listSql.Add(Financial.GetSaleBilling(statusName, strImportStatus, ids)); listSql.Add(Financial.GetMaintainSettlement(statusName, strImportStatus, ids)); listSql.Add(Financial.GetMaintainThree(statusName, strImportStatus, ids)); break; //case DataSources.EnumAccountVerification.YuShouToYuShou://预收转预收 // ImportYuShou(txtcCustName2, lblCustName2); //break; case DataSources.EnumAccountVerification.YingShouToYingFu: //应收冲应付 case DataSources.EnumAccountVerification.YingFuToYingShou: //应付冲应收 listSql.Add(Financial.GetSaleBilling(statusName, strImportStatus, ids)); listSql.Add(Financial.GetMaintainSettlement(statusName, strImportStatus, ids)); listSql.Add(Financial.GetMaintainThree(statusName, strImportStatus, ids)); listSql.Add(Financial.GetPurchaseBilling(statusName, strImportStatus, ids)); break; case DataSources.EnumAccountVerification.YuFuToYingFu: //预付冲应付 case DataSources.EnumAccountVerification.YingFuToYingFu: //应付转应付 listSql.Add(Financial.GetPurchaseBilling(statusName, strImportStatus, ids)); break; //case DataSources.EnumAccountVerification.YuFuToYuFu://预付冲预付 // ImportYuFu(txtcCustName2, lblCustName2); //break; } }
/// <summary> /// 计算往来核销单据已结算金额 /// </summary> /// <param name="enumAccount">往来核销单据类型</param> /// <param name="id">往来核销ID</param> /// <param name="list">sql列表</param> public static void DocumentSettlementByVerification(DataSources.EnumAccountVerification enumAccount, string id, List <SysSQLString> list) { //不为预收转预收,预付转预付的情况,计算已结算金额 if (enumAccount != DataSources.EnumAccountVerification.YuShouToYuShou && enumAccount != DataSources.EnumAccountVerification.YuFuToYuFu) { SysSQLString sqlSettlement = new SysSQLString(); sqlSettlement.cmdType = CommandType.StoredProcedure; sqlSettlement.Param = new Dictionary <string, string>(); sqlSettlement.Param.Add("order_id", id); sqlSettlement.Param.Add("type", "2"); //预收冲应收,应收转应收 if (enumAccount == DataSources.EnumAccountVerification.YuShouToYingShou || enumAccount == DataSources.EnumAccountVerification.YingShouToYingShou) { sqlSettlement.sqlString = "p_yingshou_jiesuan"; } //预付冲应付,应付转应付 else if (enumAccount == DataSources.EnumAccountVerification.YuFuToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingFu) { sqlSettlement.sqlString = "p_yingfu_jiesuan"; } //应收冲应付,应付冲应收 if (enumAccount == DataSources.EnumAccountVerification.YingShouToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { //应收已结算金额 sqlSettlement.sqlString = "p_yingshou_jiesuan"; //应付已结算金额 SysSQLString sqlSettlement1 = new SysSQLString(); sqlSettlement1.cmdType = CommandType.StoredProcedure; sqlSettlement1.Param = new Dictionary <string, string>(); sqlSettlement1.Param.Add("order_id", id); sqlSettlement1.Param.Add("type", "2"); sqlSettlement1.sqlString = "p_yingfu_jiesuan"; list.Add(sqlSettlement1); } list.Add(sqlSettlement); } }
/// <summary> /// 验证往来核销单据本次核销是否大于结算金额 /// </summary> /// <param name="enumAccount">单据类型</param> /// <param name="id">单据ID</param> /// <returns></returns> public static bool VerificationCheckDocumentMoney(DataSources.EnumAccountVerification enumAccount, string id) { //不为预收转预收,预付转预付的情况,验证本次核销是否大于待结算金额 if (enumAccount != DataSources.EnumAccountVerification.YuShouToYuShou && enumAccount != DataSources.EnumAccountVerification.YuFuToYuFu) { SQLObj sqlSettlement = new SQLObj(); sqlSettlement.cmdType = CommandType.Text; sqlSettlement.Param = new Dictionary <string, ParamObj>(); sqlSettlement.Param.Add("order_id", new ParamObj("order_id", id, SysDbType.VarChar, 40)); string orderIndex = "1"; //应收Sql string yingShouSql = @"select a.settled_money from tb_verificationn_documents a left join v_YingShou b on a.order_id=b.documents_id where a.account_verification_id=@order_id and order_index=@order_index and (a.money-ISNULL(b.money,0))<a.verification_money"; //应付Sql string yingFuSql = @"select a.settled_money from tb_verificationn_documents a left join v_YingFu b on a.order_id=b.documents_id where a.account_verification_id=@order_id and order_index=@order_index and (a.money-ISNULL(b.money,0))<a.verification_money"; //预收冲应收,应收转应收 if (enumAccount == DataSources.EnumAccountVerification.YuShouToYingShou || enumAccount == DataSources.EnumAccountVerification.YingShouToYingShou) { sqlSettlement.sqlString = yingShouSql; } //预付冲应付,应付转应付 else if (enumAccount == DataSources.EnumAccountVerification.YuFuToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingFu) { sqlSettlement.sqlString = yingFuSql; } //应收冲应付,应付冲应收 if (enumAccount == DataSources.EnumAccountVerification.YingShouToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { //应收已结算金额 sqlSettlement.sqlString = yingShouSql; //应付已结算金额 SQLObj sqlSettlement1 = new SQLObj(); sqlSettlement1.cmdType = CommandType.Text; sqlSettlement1.Param = new Dictionary <string, ParamObj>(); sqlSettlement1.Param.Add("order_id", new ParamObj("order_id", id, SysDbType.VarChar, 40)); sqlSettlement1.sqlString = yingFuSql; if (enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { orderIndex = "2"; sqlSettlement1.Param.Add("order_index", new ParamObj("order_index", "1", SysDbType.VarChar, 1)); } else { sqlSettlement1.Param.Add("order_index", new ParamObj("order_index", "2", SysDbType.VarChar, 1)); } DataSet ds1 = DBHelper.GetDataSet("验证往来核销单据", sqlSettlement1); if (ds1 == null || ds1.Tables.Count == 0 || ds1.Tables[0].Rows.Count == 0) { return(false); } } sqlSettlement.Param.Add("order_index", new ParamObj("order_index", orderIndex, SysDbType.VarChar, 1)); DataSet ds = DBHelper.GetDataSet("验证往来核销单据", sqlSettlement); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return(false); } } return(true); }
//审核 void VerifyData() { string files = string.Empty; foreach (DataGridViewRow dgvr in dgvVerification.Rows) { if (Convert.ToBoolean(dgvr.Cells[colChk.Name].EditedFormattedValue)) { files += string.Format("'{0}',", dgvr.Cells[colAccountVerificationID.Name].Value); } } if (files.Length == 0) { MessageBoxEx.Show("请选择要审核的数据!"); return; } UCVerify frmVerify = new UCVerify(); if (frmVerify.ShowDialog() == DialogResult.OK) { files = files.TrimEnd(','); SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.sqlString = string.Format("update tb_account_verification set order_status='{0}',Verify_advice='{3}' where account_verification_id in ({1}) and order_status='{2}';", ((int)frmVerify.auditStatus), files, (int)DataSources.EnumAuditStatus.SUBMIT, frmVerify.Content); sql.Param = new Dictionary <string, string>(); List <SysSQLString> listSql = new List <SysSQLString>(); listSql.Add(sql); //如果是审核不通过,则将没有提交或审核状态的单据设为正常 if (frmVerify.auditStatus == DataSources.EnumAuditStatus.NOTAUDIT) { #region 将没有提交或审核状态的单据设为正常 SysSQLString receivableSql = new SysSQLString(); receivableSql.cmdType = CommandType.Text; receivableSql.sqlString = string.Format(@"update tb_parts_sale_billing set is_lock=@is_lock where sale_billing_id in (select order_id from tb_verificationn_documents where account_verification_id in ({0})) and exists ( select a.documents_id from tb_balance_documents a inner join tb_bill_receivable b on a.order_id=b.payable_single_id where order_status in ('1','2') and a.documents_id =sale_billing_id union all select b.order_id from tb_account_verification a inner join tb_verificationn_documents b on a.account_verification_id=b.account_verification_id where a.order_status in ('1','2') and b.order_id=sale_billing_id ); update tb_maintain_settlement_info set is_lock=@is_lock where settlement_id in (select order_id from tb_verificationn_documents where account_verification_id in ({0})) and exists ( select a.documents_id from tb_balance_documents a inner join tb_bill_receivable b on a.order_id=b.payable_single_id where order_status in ('1','2') and a.documents_id =settlement_id union all select b.order_id from tb_account_verification a inner join tb_verificationn_documents b on a.account_verification_id=b.account_verification_id where a.order_status in ('1','2') and b.order_id=settlement_id ); update tb_maintain_three_guaranty_settlement set is_lock=@is_lock where st_id in (select order_id from tb_verificationn_documents where account_verification_id in ({0})) and exists ( select a.documents_id from tb_balance_documents a inner join tb_bill_receivable b on a.order_id=b.payable_single_id where order_status in ('1','2') and a.documents_id =st_id union all select b.order_id from tb_account_verification a inner join tb_verificationn_documents b on a.account_verification_id=b.account_verification_id where a.order_status in ('1','2') and b.order_id=st_id ); update tb_parts_purchase_billing set is_lock=@is_lock where purchase_billing_id in (select order_id from tb_verificationn_documents where account_verification_id in ({0})) and exists ( select a.documents_id from tb_balance_documents a inner join tb_bill_receivable b on a.order_id=b.payable_single_id where order_status in ('1','2') and a.documents_id =purchase_billing_id union all select b.order_id from tb_account_verification a inner join tb_verificationn_documents b on a.account_verification_id=b.account_verification_id where a.order_status in ('1','2') and b.order_id=purchase_billing_id );", files); receivableSql.Param = new Dictionary <string, string>(); receivableSql.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.OPEN).ToString()); //receivableSql.Param.Add("@id", files); listSql.Add(receivableSql); #endregion //审核失败需重新计算已结算金额 foreach (DataGridViewRow dgvr in dgvVerification.Rows) { if (Convert.ToBoolean(dgvr.Cells[colChk.Name].EditedFormattedValue)) { DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(dgvr.Cells[colOrderType.Name].Tag); string order_id = dgvr.Cells[colAccountVerificationID.Name].Value.ToString(); Financial.DocumentSettlementByVerification(enumAccount, order_id, listSql); } } } if (DBHelper.BatchExeSQLStringMultiByTrans("审核往来核销", listSql)) { MessageBoxEx.Show("审核成功!"); BindData(); } else { MessageBoxEx.Show("审核失败!"); } } }