//往来单位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("审核失败！");
}
}
}