/// <summary>
/// 添加收付款sql
/// </summary>
/// <param name="list"></param>
static void AddReceivableSqlString(List<SysSQLString> list, tb_bill_receivable bill)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
sql.Param.Add("payable_single_id", bill.payable_single_id);
sql.Param.Add("order_num", bill.order_num);//单号
sql.Param.Add("order_date", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//时间
sql.Param.Add("order_status", ((int)DataSources.EnumAuditStatus.SUBMIT).ToString());//单据状态
sql.Param.Add("order_type", bill.order_type.ToString());//单据类型
sql.Param.Add("cust_id", bill.cust_id);
//sql.Param.Add("cust_code", bill.cust_code);//往来单位
//sql.Param.Add("cust_name", bill.cust_name);//
sql.Param.Add("payment_type", bill.payment_type.ToString());//收付款类型
sql.Param.Add("payment_money", bill.payment_money.ToString());
sql.Param.Add("dealings_balance", bill.dealings_balance==null ?null :bill.dealings_balance.ToString());
//sql.Param.Add("bank_of_deposit", bill.bank_of_deposit);//开户银行
//sql.Param.Add("bank_account", bill.bank_account);//银行账户
sql.Param.Add("org_id", bill.org_id);//部门
sql.Param.Add("handle", GlobalStaticObj.UserID);//经办人
sql.Param.Add("operator", GlobalStaticObj.UserID);//操作人
//sql.Param.Add("remark", bill.remark);
sql.Param.Add("create_by", GlobalStaticObj.UserID);
sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
sql.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString());
sql.Param.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString());
// sql.sqlString = @"INSERT INTO [tb_bill_receivable]
// (payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance
//,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag,remark)
// VALUES
// (@payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,@cust_code,@cust_name,@payment_type,@payment_money,@dealings_balance
//,@bank_of_deposit,@bank_account,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag,@remark);";
if (bill.order_type == 0)
{
sql.sqlString = @"INSERT INTO [tb_bill_receivable]
(payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance
,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag)
select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,cust_code,cust_name,@payment_type,@payment_money,@dealings_balance,
bank_account_person,bank_account,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag from tb_customer
where cust_id=@cust_id";
}
else
{
sql.sqlString = @"INSERT INTO [tb_bill_receivable]
(payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance
,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag)
select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,sup_code,sup_short_name,@payment_type,@payment_money,@dealings_balance,
null,null,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag
from tb_supplier where sup_id =@cust_id";
}
list.Add(sql);
}
/// <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);
AddDetailSqlString(listSql, detail);
AddBalanceDocumentsSqlString(listSql, documents);
try
{
isAdd = DBHelper.BatchExeSQLStringMultiByTrans("新增应收应付", listSql);
}
catch (Exception ex)
{
isAdd = false;
}
return isAdd;
}
/// <summary>
/// 审核通过后需要自动生成的单子的方法
/// </summary>
void CreateBill(List<string> listField)
{
try
{
if (listField.Count > 0)
{
string bill_ids = string.Empty;
List<SysSQLString> listSql = new List<SysSQLString>();
DataTable TemplateTable = CommonFuncCall.CreatePartStatisticTable();//获取要填充的公用表
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)
{
DataTable dt_bill_p = new DataTable();
tb_parts_purchase_billing model = new tb_parts_purchase_billing();
CommonFuncCall.SetModlByDataTable(model, dt);
#region 开单审核通过后，更新配件的账面库存
string str_time = Common.UtcLongToLocalDateTime(model.order_date).ToShortDateString();
long long_time = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(str_time));
DataTable IOPartTable = DBHelper.GetTable("", "tb_parts_purchase_billing_p", "*", string.Format("purchase_billing_id='{0}'", listField[i]), "", "");
if (IOPartTable != null && IOPartTable.Rows.Count > 0)
{
for (int a = 0; a < IOPartTable.Rows.Count; a++)
{
DataRow dr = TemplateTable.NewRow();//创建模版表行项
dr["OrderDate"] = long_time.ToString();//单据日期
dr["WareHouseID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_id"]);//仓库ID
dr["WareHouseName"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_name"]);//仓库名称
dr["PartID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["parts_id"]);//配件ID
dr["PartCode"] = IOPartTable.Rows[a]["parts_code"].ToString();//配件编码
dr["PartName"] = IOPartTable.Rows[a]["parts_name"].ToString();//配件名称
dr["PartSpec"] = IOPartTable.Rows[a]["model"].ToString();//配件规格
dr["PartBarCode"] = IOPartTable.Rows[a]["parts_barcode"].ToString();//配件条码
dr["CarPartsCode"] = IOPartTable.Rows[a]["car_factory_code"].ToString();//车厂编码
dr["DrawNum"] = IOPartTable.Rows[a]["drawing_num"].ToString();//配件图号
dr["UnitName"] = IOPartTable.Rows[a]["unit_name"].ToString();//单位名称
//dr["PartCount"] = IOPartTable.Rows[a]["business_counts"].ToString();//配件业务数量
dr["PartCount"] = Convert.ToDecimal(IOPartTable.Rows[a]["business_counts"].ToString());//配件业务数量
dr["StatisticType"] = (int)DataSources.EnumStatisticType.PaperCount;//统计类型
TemplateTable.Rows.Add(dr);//添加新的数据行项
}
}
#endregion
#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 一:采购收货单(业务数量大于0)-->自动生成入库单
if (model.order_type == Convert.ToInt32(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)
{
dt_bill_p = dt_parts;
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);
bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.purchase_billing_id, model.order_num);
}
}
}
}
#endregion
#region 二:采购退货(业务数量小于0)-->自动生成出库单
else if (model.order_type == Convert.ToInt32(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);
bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.purchase_billing_id, model.order_num);
}
}
}
}
#endregion
#region 三:采购换货-->自动生成出、入库单(数量大于0是入库，小于0是出库)
else if (model.order_type == Convert.ToInt32(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 isnull(business_counts,0)>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);
bool ret = CreateIntoPartsStock(stock_in_id, bill_p_model, model.purchase_billing_id, model.order_num);
}
}
}
//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 isnull(business_counts,0)<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, model.purchase_billing_id, model.order_num);
}
}
}
}
#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
#region 审核通过时，将本次现付更新到已结算金额中
if (model.this_payment > 0)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
dicParam.Add("balance_money", model.this_payment.ToString());
dicParam.Add("purchase_billing_id", listField[i]);
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_parts_purchase_billing Set balance_money=@balance_money where purchase_billing_id=@purchase_billing_id");
sysStringSql.Param = dicParam;
sysStringSql.sqlString = sb.ToString();
listSql.Add(sysStringSql);
}
#endregion
#region 如果配送单号不为空，需将对应的配送单状态修改为“已收货”
if (!string.IsNullOrEmpty(model.ration_send_code))
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
dicParam.Add("distribution_status", "2");//1:配送中,2:已收货
dicParam.Add("ration_send_code", model.ration_send_code);
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_distribution Set distribution_status=@distribution_status where ration_send_code=@ration_send_code");
sysStringSql.Param = dicParam;
sysStringSql.sqlString = sb.ToString();
listSql.Add(sysStringSql);
}
#endregion
#region 如果配送单号不为空，需通过配送单将对应的宇通采购订单的确认数量修改下
// if (!string.IsNullOrEmpty(model.ration_send_code))
// {
// if (dt_bill_p != null && dt_bill_p.Rows.Count > 0)
// {
// dt_bill_p = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", "");
// if (dt_bill_p != null && dt_bill_p.Rows.Count > 0)
// {
// string wherestr = string.Format(@"purchase_order_yt_id in
// (
// select top 1 purchase_order_yt_id from tb_parts_purchase_order_2 where dsn_adjustable_parts in
// (
// select top 1 dsn_adjustable_parts from tb_distribution where ration_send_code='{0}'
// )
// )", model.ration_send_code);
// DataTable dt_yt_p = DBHelper.GetTable("采购开单审核通过时，修改宇通采购订单配件信息量", "tb_parts_purchase_order_p_2", "*", wherestr, "", "");
// if (dt_yt_p != null && dt_yt_p.Rows.Count > 0)
// {
// foreach (DataRow dr in dt_bill_p.Rows)
// {
// DataRow[] dr_yt_p = dt_yt_p.Select("parts_code ='" + dr["parts_code"].ToString() + "'");
// if (dr_yt_p.Length > 0)
// {
// decimal old_conf_count = Convert.ToDecimal(dr_yt_p[0]["conf_count"].ToString());
// decimal new_conf_count = Convert.ToDecimal(dr["business_counts"].ToString());
// SysSQLString sysStringSql = new SysSQLString();
// sysStringSql.cmdType = CommandType.Text;
// Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
// dicParam.Add("conf_count", (old_conf_count + new_conf_count).ToString());
// dicParam.Add("purchase_order_yt_id", dr_yt_p[0]["purchase_order_yt_id"].ToString());
// dicParam.Add("parts_code", dr_yt_p[0]["parts_code"].ToString());
// StringBuilder sb = new StringBuilder();
// sb.Append(" Update tb_parts_purchase_order_p_2 Set conf_count=@conf_count where purchase_order_yt_id=@purchase_order_yt_id and parts_code=@parts_code");
// sysStringSql.Param = dicParam;
// sysStringSql.sqlString = sb.ToString();
// listSql.Add(sysStringSql);
// }
// }
// }
// }
// }
// }
#endregion
}
}
CommonFuncCall.StatisticStock(TemplateTable, "采购开单审核通过后更新配件的账面库存");
//审核通过时，将本次现付更新到已结算金额中
if (listSql.Count > 0)
{
DBHelper.BatchExeSQLStringMultiByTrans("采购开单审核通过时，将本次现付更新到已结算金额中或是更新配送单状态", listSql);
}
}
}
catch (Exception ex)
{ }
}
/// <summary>
/// 添加收付款sql
/// </summary>
/// <param name="list"></param>
static void AddReceivableSqlString(List<SysSQLString> list, tb_bill_receivable bill, decimal cash_money)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
sql.Param.Add("payable_single_id", bill.payable_single_id);
sql.Param.Add("order_num", bill.order_num);//单号
sql.Param.Add("order_date", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//时间
sql.Param.Add("order_status", ((int)DataSources.EnumAuditStatus.SUBMIT).ToString());//单据状态
sql.Param.Add("order_type", bill.order_type.ToString());//单据类型
sql.Param.Add("cust_id", bill.cust_id);
//sql.Param.Add("cust_code", bill.cust_code);//往来单位
//sql.Param.Add("cust_name", bill.cust_name);//
sql.Param.Add("payment_type", bill.payment_type.ToString());//收付款类型
DataSources.EnumOrderType orderType = (DataSources.EnumOrderType)bill.order_type;
sql.Param.Add("payment_money", DBOperation.GetAdvance(bill.cust_id, orderType).ToString());//预付金额
if (orderType == DataSources.EnumOrderType.RECEIVABLE)
{
sql.Param.Add("dealings_balance", DBOperation.GetReceivable(bill.cust_id).ToString());//往来余额
}
else
{
sql.Param.Add("dealings_balance", DBOperation.GetPayable(bill.cust_id).ToString());//往来余额
}
//sql.Param.Add("bank_of_deposit", bill.bank_of_deposit);//开户银行
//sql.Param.Add("bank_account", bill.bank_account);//银行账户
sql.Param.Add("org_id", GlobalStaticObj.CurrUserOrg_Id);//部门
sql.Param.Add("org_name", GlobalStaticObj.CurrUserOrg_Name);
sql.Param.Add("handle", GlobalStaticObj.UserID);//经办人
sql.Param.Add("handle_name", GlobalStaticObj.UserName);
sql.Param.Add("operator", GlobalStaticObj.UserID);//操作人
sql.Param.Add("operator_name", GlobalStaticObj.UserName);
//sql.Param.Add("remark", bill.remark);
sql.Param.Add("create_by", GlobalStaticObj.UserID);
sql.Param.Add("create_name", GlobalStaticObj.UserName);
sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
sql.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString());
sql.Param.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString());
sql.Param.Add("com_id", GlobalStaticObj.CurrUserCom_Id);//公司
sql.Param.Add("com_name", GlobalStaticObj.CurrUserCom_Name);
sql.Param.Add("cash_money", cash_money.ToString());//收付金额
sql.Param.Add("settlement_money", cash_money.ToString());//结算金额
if (bill.order_type == 0)
{
sql.sqlString = @"INSERT INTO [tb_bill_receivable]
(payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance
,bank_of_deposit,bank_account,org_id,org_name,handle,handle_name,operator,operator_name,create_by,create_name,create_time,status,enable_flag,
com_id,com_name,cash_money,settlement_money)
select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,cust_code,cust_name,@payment_type,@payment_money,@dealings_balance,
bank_account_person,bank_account,@org_id,@org_name,@handle,@handle_name,@operator,@operator_name,@create_by,@create_name,@create_time,@status,@enable_flag,
@com_id,@com_name,@cash_money,@settlement_money
from tb_customer where cust_id=@cust_id";
}
else
{
sql.sqlString = @"INSERT INTO [tb_bill_receivable]
(payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance
,bank_of_deposit,bank_account,org_id,org_name,handle,handle_name,operator,operator_name,create_by,create_name,create_time,status,enable_flag,
com_id,com_name,cash_money,settlement_money)
select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,sup_code,sup_full_name,@payment_type,@payment_money,@dealings_balance,
null,null,@org_id,@org_name,@handle,@handle_name,@operator,@operator_name,@create_by,@create_name,@create_time,@status,@enable_flag,
@com_id,@com_name,@cash_money,@settlement_money
from tb_supplier where sup_id =@cust_id";
}
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_sale_order", "*", string.Format("sale_order_id='{0}'", listField[i]), "", "");
if (dt != null && dt.Rows.Count > 0)
{
tb_parts_sale_order model = new tb_parts_sale_order();
CommonFuncCall.SetModlByDataTable(model, dt);
if (model.advance_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.cust_id;//客户ID
a.order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.RECEIVABLE);//订单号
a.order_type = (int)DataSources.EnumOrderType.RECEIVABLE;
a.payment_type = (int)DataSources.EnumReceivableType.ADVANCES;
a.org_id = model.org_id;
b.billing_money = model.advance_money;//开单金额
b.documents_date = model.order_date;//单据日期
b.documents_id = model.sale_order_id;//单据ID
b.documents_name = "销售订单";//单据名称
b.documents_num = model.order_num;//单据编码
c.money = model.advance_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_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>
void CreateBill(List<string> listField)
{
if (listField.Count > 0)
{
List<SysSQLString> listSql = new List<SysSQLString>();
DataTable TemplateTable = CommonFuncCall.CreatePartStatisticTable();//获取要填充的公用表
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 开单审核通过后，更新配件的账面库存
string str_time = Common.UtcLongToLocalDateTime(model.order_date).ToShortDateString();
long long_time = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(str_time));
DataTable IOPartTable = DBHelper.GetTable("", "tb_parts_sale_billing_p", "*", string.Format("sale_billing_id='{0}'", listField[i]), "", "");
if (IOPartTable != null && IOPartTable.Rows.Count > 0)
{
for (int a = 0; a < IOPartTable.Rows.Count; a++)
{
DataRow dr = TemplateTable.NewRow();//创建模版表行项
dr["OrderDate"] = long_time.ToString();//单据日期
dr["WareHouseID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_id"]);//仓库ID
dr["WareHouseName"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_name"]);//仓库名称
dr["PartID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["parts_id"]);//配件ID
dr["PartCode"] = IOPartTable.Rows[a]["parts_code"].ToString();//配件编码
dr["PartName"] = IOPartTable.Rows[a]["parts_name"].ToString();//配件名称
dr["PartSpec"] = IOPartTable.Rows[a]["model"].ToString();//配件规格
dr["PartBarCode"] = IOPartTable.Rows[a]["parts_barcode"].ToString();//配件条码
dr["CarPartsCode"] = IOPartTable.Rows[a]["car_factory_code"].ToString();//车厂编码
dr["DrawNum"] = IOPartTable.Rows[a]["drawing_num"].ToString();//配件图号
dr["UnitName"] = IOPartTable.Rows[a]["unit_name"].ToString();//单位名称
//dr["PartCount"] = IOPartTable.Rows[a]["business_count"].ToString();//配件业务数量
dr["PartCount"] = Convert.ToDecimal(IOPartTable.Rows[a]["business_count"].ToString());//配件业务数量
dr["StatisticType"] = (int)DataSources.EnumStatisticType.PaperCount;//统计类型
TemplateTable.Rows.Add(dr);//添加新的数据行项
}
}
#endregion
#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 自动生产出入库单
#region 1:销售开单(业务数量大于0)-->自动生成出库单
if (model.order_type == Convert.ToInt32(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);
bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.sale_billing_id, model.order_num);
}
}
}
}
#endregion
#region 2:销售退货(业务数量小于0)-->自动生成入库单
else if (model.order_type == Convert.ToInt32(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);
bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.sale_billing_id, model.order_num);
}
}
}
#region 当销售退货时，将勾选了报损的配件信息生成报损单
DataTable dt_loss_parts = DBHelper.GetTable("查询销售开单配件信息表", "tb_parts_sale_billing_p", "*", " is_loss='1' and sale_billing_id='" + model.sale_billing_id + "'", "", "");
if (dt_loss_parts != null && dt_loss_parts.Rows.Count > 0)
{
string stock_loss_id = string.Empty;
if (CreateIntoLoss(model, ref stock_loss_id))
{
for (int a = 0; a < dt_loss_parts.Rows.Count; a++)
{
tb_parts_sale_billing_p bill_p_model = new tb_parts_sale_billing_p();
CommonFuncCall.SetModlByDataTable(bill_p_model, dt_loss_parts, a);
bool ret = CreateIntoPartsLoss(stock_loss_id, bill_p_model, model.sale_billing_id, model.order_num);
}
}
}
#endregion
}
#endregion
#region 3:销售换货-->自动生成出、入库单(数量大于0是出库，小于0是入库)
else if (model.order_type == Convert.ToInt32(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 isnull(business_count,0)<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);
bool ret = CreateIntoPartsStock(stock_in_id, bill_p_model, model.sale_billing_id, model.order_num);
}
}
}
//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 isnull(business_count,0)>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);
bool ret = CreateIntoPartsStock(stock_out_id, bill_p_model, model.sale_billing_id, model.order_num);
}
}
}
}
#endregion
#endregion
#region 审核通过时，将本次现收更新到已结算金额中
if (model.current_collect > 0)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
dicParam.Add("balance_money", model.current_collect.ToString());
dicParam.Add("sale_billing_id", listField[i]);
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_parts_sale_billing Set balance_money=@balance_money where sale_billing_id=@sale_billing_id");
sysStringSql.Param = dicParam;
sysStringSql.sqlString = sb.ToString();
listSql.Add(sysStringSql);
}
#endregion
}
}
CommonFuncCall.StatisticStock(TemplateTable, "销售开单审核通过后更新配件的账面库存");
//审核通过时，将本次现收更新到已结算金额中
if (listSql.Count > 0)
{
DBHelper.BatchExeSQLStringMultiByTrans("采购销售审核通过时，将本次现收更新到已结算金额中", listSql);
}
}
}
/// <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
}
}
}
}