/// <summary>
/// 添加收付款明细sql
/// </summary>
/// <param name="list"></param>
static void AddDetailSqlString(List <SysSQLString> list, tb_payment_detail detail)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary <string, string>();
sql.Param.Add("order_id", detail.order_id);
sql.Param.Add("money", detail.money.ToString()); //金额
sql.Param.Add("balance_way", detail.balance_way); //结算方式
//sql.Param.Add("payment_account", detail.payment_account);//付款账户
//sql.Param.Add("bank_of_deposit", detail.bank_of_deposit);//开户银行
//sql.Param.Add("bank_account", detail.bank_account);//银行账户
sql.Param.Add("check_number", detail.check_number);//票号
//sql.Param.Add("remark", detail.remark);//备注
sql.Param.Add("create_by", GlobalStaticObj.UserID);
sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
// sql.sqlString = @"INSERT INTO [tb_payment_detail](payment_detail_id,order_id,money
//,balance_way,payment_account,bank_of_deposit,bank_account,check_number,remark,create_by,create_time)
// VALUES
//(@payment_detail_id,@order_id,@money,@balance_way,@payment_account,@bank_of_deposit,@bank_account,@check_number,@remark
//,@create_by,@create_time)";
sql.Param.Add("payment_detail_id", Guid.NewGuid().ToString());
sql.sqlString = @"INSERT INTO [tb_payment_detail](payment_detail_id,order_id,money
,balance_way,payment_account,bank_of_deposit,bank_account,check_number,create_by,create_time)
select @payment_detail_id,@order_id,@money,a.balance_way_id,a.default_account,b.bank_name,b.bank_account,
@check_number,@create_by,@create_time from tb_balance_way a
left join v_cashier_account b on a.default_account=b.cashier_account
where a.balance_way_id=@balance_way";
list.Add(sql);
}
/// <summary>
/// 当金额不为0，审核通过时，自动生产预/应收单、预/应付单
/// </summary>
void AddBillPayReceive(List <string> listField)
{
if (listField.Count > 0)
{
for (int i = 0; i < listField.Count; i++)
{
DataTable dt = DBHelper.GetTable("", "tb_parts_purchase_order", "*", string.Format("order_id='{0}'", listField[i]), "", "");
if (dt != null && dt.Rows.Count > 0)
{
tb_parts_purchase_order model = new tb_parts_purchase_order();
CommonFuncCall.SetModlByDataTable(model, dt);
if (model.prepaid_money > 0)
{
tb_bill_receivable a = new tb_bill_receivable();
tb_balance_documents b = new tb_balance_documents();
tb_payment_detail c = new tb_payment_detail();
a.cust_id = model.sup_id; //供应商ID
a.order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.PAYMENT); //订单号
a.order_type = (int)DataSources.EnumOrderType.PAYMENT;
a.payment_type = (int)DataSources.EnumPaymentType.ADVANCES;
a.org_id = model.org_id;
b.billing_money = model.prepaid_money; //开单金额
b.documents_date = model.order_date; //单据日期
b.documents_id = model.order_id; //单据ID
b.documents_name = "采购订单"; //单据名称
b.documents_num = model.order_num; //单据编码
c.money = model.prepaid_money;
c.balance_way = model.closing_way;//结算方式
//c.check_number = model.check_number;//票号
DBOperation.AddBillReceivable(a, b, c);
}
}
}
}
}
/// <summary>
/// 审核通过后需要自动生成的单子的方法
/// </summary>
void CreateBill(List <string> listField)
{
if (listField.Count > 0)
{
for (int i = 0; i < listField.Count; i++)
{
DataTable dt = DBHelper.GetTable("", "tb_parts_purchase_billing", "*", string.Format("purchase_billing_id='{0}'", listField[i]), "", "");
if (dt != null && dt.Rows.Count > 0)
{
tb_parts_purchase_billing model = new tb_parts_purchase_billing();
CommonFuncCall.SetModlByDataTable(model, dt);
#region 当金额大于0时，自动生成预付款单
if (model.this_payment > 0)
{
tb_bill_receivable a = new tb_bill_receivable();
tb_balance_documents b = new tb_balance_documents();
tb_payment_detail c = new tb_payment_detail();
a.cust_id = model.sup_id; //供应商ID
a.order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.PAYMENT); //订单号
a.order_type = (int)DataSources.EnumOrderType.PAYMENT;
a.payment_type = (int)DataSources.EnumPaymentType.PAYMENT;
a.org_id = model.org_id;
b.billing_money = model.this_payment; //开单金额
b.documents_date = model.order_date; //单据日期
b.documents_id = model.purchase_billing_id; //单据ID
b.documents_name = "采购开单"; //单据名称
b.documents_num = model.order_num; //单据编码
c.money = model.this_payment; //金额
c.balance_way = model.balance_way; //结算方式
c.check_number = model.check_number; //票号
DBOperation.AddBillReceivable(a, b, c);
}
#endregion
#region 自动生产出入库单
#region 一:采购收货单-->自动生成入库单
if (model.order_type == DataSources.EnumPurchaseOrderType.PurchaseReceive.ToString())
{
string stock_inout_id = string.Empty;
//1.生成入库单
if (CreateIntoStock("入库单", model, ref stock_inout_id))
{
//2.查询配件信息，生成配件信息入库单
DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_inout_id, bill_p_model);
}
}
}
}
#endregion
#region 二:采购退货-->自动生成出库单
else if (model.order_type == DataSources.EnumPurchaseOrderType.PurchaseBack.ToString())
{
string stock_inout_id = string.Empty;
//1.生成出库单
if (CreateIntoStock("出库单", model, ref stock_inout_id))
{
//2.查询配件信息，生成配件信息出库单
DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_inout_id, bill_p_model);
}
}
}
}
#endregion
#region :采购换货-->自动生成出、入库单(数量大于0是入库，小于0是出库)
else if (model.order_type == DataSources.EnumPurchaseOrderType.PurchaseExchange.ToString())
{
string stock_in_id = string.Empty;
string stock_out_id = string.Empty;
//1.生成入库单
if (CreateIntoStock("入库单", model, ref stock_in_id))
{
//2.查询配件入库数量大于0的，生成配件信息入库单
DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "' and storage_count>0 ", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_in_id, bill_p_model);
}
}
}
//3.生成出库单
if (CreateIntoStock("出库单", model, ref stock_out_id))
{
//4.查询配件入库数量小于0的，生成配件信息出库单
DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "' and storage_count<0 ", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_out_id, bill_p_model);
}
}
}
}
#endregion
#endregion
#region 向宇通发送配送单号
if (!string.IsNullOrEmpty(model.ration_send_code))
{
//DBHelper.WebServHandler("审核通过入库时发送配送单号到宇通系统", EnumWebServFunName.UpLoadPartPutStore, model.ration_send_code);
DBHelper.WebServHandler("", EnumWebServFunName.LoadPartInStore, model.ration_send_code);
}
#endregion
}
}
}
}
/// <summary>
/// 审核通过后需要自动生成的单子的方法
/// </summary>
void CreateBill(List <string> listField)
{
if (listField.Count > 0)
{
for (int i = 0; i < listField.Count; i++)
{
DataTable dt = DBHelper.GetTable("", "tb_parts_sale_billing", "*", string.Format("sale_billing_id='{0}'", listField[i]), "", "");
if (dt != null && dt.Rows.Count > 0)
{
tb_parts_sale_billing model = new tb_parts_sale_billing();
CommonFuncCall.SetModlByDataTable(model, dt);
#region 当金额大于0时，自动生成预收款单
if (model.current_collect > 0)
{
tb_bill_receivable a = new tb_bill_receivable();
tb_balance_documents b = new tb_balance_documents();
tb_payment_detail c = new tb_payment_detail();
a.cust_id = model.cust_id; //客户ID
a.order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.RECEIVABLE); //订单号
a.order_type = (int)DataSources.EnumOrderType.RECEIVABLE;
a.payment_type = (int)DataSources.EnumReceivableType.RECEIVABLE;
a.org_id = model.org_id;
b.billing_money = model.current_collect; //开单金额
b.documents_date = model.order_date; //单据日期
b.documents_id = model.org_id; //单据ID
b.documents_name = "销售开单"; //单据名称
b.documents_num = model.order_num; //单据编码
c.money = model.current_collect; //金额
c.balance_way = model.balance_way; //结算方式
c.check_number = model.check_number; //票号
DBOperation.AddBillReceivable(a, b, c);
}
#endregion
#region 自动生产出入库单
//1:销售开单-->自动生成出库单
if (model.order_type == DataSources.EnumSaleOrderType.SaleBill.ToString())
{
string stock_inout_id = string.Empty;
//1.生成出库单
if (CreateIntoStock("出库单", model, ref stock_inout_id))
{
//2.查询配件信息，生成配件信息出库单
DataTable dt_parts = DBHelper.GetTable("查询销售开单配件信息表", "tb_parts_sale_billing_p", "*", " sale_billing_id='" + model.sale_billing_id + "'", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_sale_billing_p bill_p_model = new tb_parts_sale_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_inout_id, bill_p_model);
}
}
}
}
//2:销售退货-->自动生成入库单
else if (model.order_type == DataSources.EnumSaleOrderType.SaleBack.ToString())
{
string stock_inout_id = string.Empty;
//1.生成入库单
if (CreateIntoStock("入库单", model, ref stock_inout_id))
{
//2.查询配件信息，生成配件信息入库单
DataTable dt_parts = DBHelper.GetTable("查询销售开单配件信息表", "tb_parts_sale_billing_p", "*", " sale_billing_id='" + model.sale_billing_id + "'", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_sale_billing_p bill_p_model = new tb_parts_sale_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_inout_id, bill_p_model);
}
}
}
}
//3:销售换货-->自动生成出、入库单(数量大于0是出库，小于0是入库)
else if (model.order_type == DataSources.EnumSaleOrderType.SaleExchange.ToString())
{
string stock_in_id = string.Empty;
string stock_out_id = string.Empty;
//1.生成入库单
if (CreateIntoStock("入库单", model, ref stock_in_id))
{
//2.查询配件入库数量大于0的，生成配件信息入库单
DataTable dt_parts = DBHelper.GetTable("查询销售开单配件信息表", "tb_parts_sale_billing_p", "*", " sale_billing_id='" + model.sale_billing_id + "' and library_count<0 ", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_sale_billing_p bill_p_model = new tb_parts_sale_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_in_id, bill_p_model);
}
}
}
//3.生成出库单
if (CreateIntoStock("出库单", model, ref stock_out_id))
{
//4.查询配件入库数量小于0的，生成配件信息出库单
DataTable dt_parts = DBHelper.GetTable("查询销售开单配件信息表", "tb_parts_sale_billing_p", "*", " sale_billing_id='" + model.sale_billing_id + "' and library_count>0 ", "", "");
if (dt_parts != null && dt_parts.Rows.Count > 0)
{
for (int a = 0; a < dt_parts.Rows.Count; a++)
{
tb_parts_sale_billing_p bill_p_model = new tb_parts_sale_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a);
CreateIntoPartsStock(stock_out_id, bill_p_model);
}
}
}
}
#endregion
}
}
}
}
/// <summary>
/// 添加应收应付
/// </summary>
/// <param name="bill">应收应付单</param>
/// <param name="documents">结算单据</param>
/// <param name="detail">应收应付明细</param>
/// <returns></returns>
public static bool AddBillReceivable(tb_bill_receivable bill, tb_balance_documents documents, tb_payment_detail detail)
{
bool isAdd = false;
List <SysSQLString> listSql = new List <SysSQLString>();
bill.payable_single_id = Guid.NewGuid().ToString();
documents.order_id = bill.payable_single_id;
detail.order_id = bill.payable_single_id;
AddReceivableSqlString(listSql, bill, detail.money);
AddDetailSqlString(listSql, detail);
AddBalanceDocumentsSqlString(listSql, documents);
try
{
isAdd = DBHelper.BatchExeSQLStringMultiByTrans("新增应收应付", listSql);
}
catch (Exception ex)
{
isAdd = false;
}
return(isAdd);
}
