void UCSalePlanView_SaveEvent(object sender, EventArgs e)
{
try
{
gvPurchasePlanList.EndEdit();
List<SysSQLString> listSql = new List<SysSQLString>();
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();//参数
string sql1 = string.Format(@" Update tb_parts_sale_plan Set is_suspend=@is_suspend,suspend_reason=@suspend_reason,update_by=@update_by,
update_name=@update_name,update_time=@update_time,operators=@operators,operator_name=@operator_name where sale_plan_id=@sale_plan_id;");
dic.Add("is_suspend", chkis_suspend.Checked ? "0" : "1");//选中(中止)：0,未选中(不中止)：1
dic.Add("suspend_reason", txtsuspend_reason.Caption.Trim());
dic.Add("update_by", GlobalStaticObj.UserID);
dic.Add("update_name", GlobalStaticObj.UserName);
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
dic.Add("operators", GlobalStaticObj.UserID);
dic.Add("operator_name", GlobalStaticObj.UserName);
dic.Add("sale_plan_id", planId);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
foreach (DataGridViewRow dr in gvPurchasePlanList.Rows)
{
string is_suspend = "1";
if (dr.Cells["is_suspend"].Value == null)
{ is_suspend = "1"; }
if ((bool)dr.Cells["is_suspend"].EditedFormattedValue)
{ is_suspend = "0"; }
else
{ is_suspend = "1"; }
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
dic.Add("is_suspend", is_suspend);
dic.Add("sale_plan_id", planId);
dic.Add("parts_code", dr.Cells["parts_code"].Value.ToString());
string sql2 = "Update tb_parts_sale_plan_p set is_suspend=@is_suspend where sale_plan_id=@sale_plan_id and parts_code=@parts_code;";
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
if (DBHelper.BatchExeSQLStringMultiByTrans("修改采购计划单", listSql))
{
MessageBoxEx.Show("保存成功！");
uc.BindgvSalePlanList();
deleteMenuByTag(this.Tag.ToString(), uc.Name);
}
else
{
MessageBoxEx.Show("保存失败！");
}
}
catch (Exception ex)
{
MessageBoxEx.Show("操作失败！");
}
}
protected override bool LockDocument(string ids)
{
ids = ids.Replace(",", "','");
List<SysSQLString> listSql = new List<SysSQLString>();
SysSQLString saleBilling = new SysSQLString();//销售开单
saleBilling.cmdType = CommandType.Text;
saleBilling.Param = new Dictionary<string, string>();
saleBilling.sqlString =string.Format( "update tb_parts_sale_billing set is_lock=@is_lock where sale_billing_id in ({0})",ids);
saleBilling.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString());
//saleBilling.Param.Add("id", ids);
listSql.Add(saleBilling);
SysSQLString maintainSql = new SysSQLString();//维修结算单
maintainSql.cmdType = CommandType.Text;
maintainSql.sqlString =string.Format( "update tb_maintain_settlement_info set is_lock=@is_lock where settlement_id in ({0})",ids);
maintainSql.Param = new Dictionary<string, string>();
maintainSql.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString());
//maintainSql.Param.Add("id", ids);
listSql.Add(maintainSql);
SysSQLString threeSql = new SysSQLString();//三包服务单
threeSql.cmdType = CommandType.Text;
threeSql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set is_lock=@is_lock where st_id in ({0})", ids);
threeSql.Param = new Dictionary<string, string>();
threeSql.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString());
//threeSql.Param.Add("id", ids);
listSql.Add(threeSql);
return DBHelper.BatchExeSQLStringMultiByTrans("锁定应收单据", listSql);
}
//确认，保存到数据库并关闭当前窗体
private void btnOK_Click(object sender, EventArgs e)
{
dgvSet.EndEdit();
List<SysSQLString> listSql = new List<SysSQLString>();
bool isCheck = true;
foreach (DataGridViewRow dgvr in dgvSet.Rows)
{
string width = CommonCtrl.IsNullToString(dgvr.Cells[colSetWidth.Name].Value);
if (width.Length == 0)
{
isCheck = false;
dgvSet.CurrentCell = dgvr.Cells[colSetWidth.Name];
break;
}
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.sqlString = @"update tb_report_set set set_width=@set_width,is_show=@is_show,is_print=@is_print where set_id=@set_id;";
sql.Param = new Dictionary<string, string>();
sql.Param.Add("set_id", dgvr.Cells[colSetID.Name].Value.ToString());
sql.Param.Add("set_width", dgvr.Cells[colSetWidth.Name].Value.ToString());
sql.Param.Add("is_show", Convert.ToBoolean(dgvr.Cells[colIsShow.Name].Value)?"1":"0");
sql.Param.Add("is_print", Convert.ToBoolean(dgvr.Cells[colIsPrint.Name].Value)?"1":"0");
listSql.Add(sql);
}
if (!isCheck)
{
return;
}
if (DBHelper.BatchExeSQLStringMultiByTrans("", listSql))
{
this.DialogResult = DialogResult.OK;
this.Close();
}
}
/// <summary> 激活/作废
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void UCPurchasePlanOrderView_InvalidOrActivationEvent(object sender, EventArgs e)
{
string strmsg = string.Empty;
List<SysSQLString> listSql = new List<SysSQLString>();
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();//参数
dic.Add("purchase_order_yt_id", purchase_order_yt_id);//单据ID
dic.Add("update_by", GlobalStaticObj.UserID);//修改人Id
dic.Add("update_name", GlobalStaticObj.UserName);//修改人姓名
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//修改时间
if (orderstatus != Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString())
{
strmsg = "作废";
dic.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString());//单据状态编号
dic.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.Invalid, true));//单据状态名称
}
else
{
strmsg = "激活";
string order_status = string.Empty;
string order_status_name = string.Empty;
DataTable dvt = DBHelper.GetTable("获得宇通采购订单的前一个状态", "tb_parts_purchase_order_2_BackUp", "order_status,order_status_name", "purchase_order_yt_id='" + purchase_order_yt_id + "'", "", "order by update_time desc");
if (dvt != null && dvt.Rows.Count > 0)
{
DataRow dr = dvt.Rows[0];
order_status = CommonCtrl.IsNullToString(dr["order_status"]);
if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString())
{
DataRow dr1 = dvt.Rows[1];
order_status = CommonCtrl.IsNullToString(dr1["order_status"]);
order_status_name = CommonCtrl.IsNullToString(dr1["order_status_name"]);
}
}
order_status = !string.IsNullOrEmpty(order_status) ? order_status : Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString())
{ order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.NOTAUDIT, true); }
else if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString())
{ order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true); }
dic.Add("order_status", order_status);//单据状态
dic.Add("order_status_name", order_status_name);//单据状态名称
}
sysStringSql.sqlString = "update tb_parts_purchase_order_2 set order_status=@order_status,order_status_name=@order_status_name,update_by=@update_by,update_name=@update_name,update_time=@update_time where purchase_order_yt_id=@purchase_order_yt_id";
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
if (MessageBoxEx.Show("确认要" + strmsg + "吗?", "提示", MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return;
}
if (DBHelper.BatchExeSQLStringMultiByTrans("更新单据状态为" + strmsg + "", listSql))
{
MessageBoxEx.Show("" + strmsg + "成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
uc.BindgvYTPurchaseOrderList();
deleteMenuByTag(this.Tag.ToString(), uc.Name);
}
else
{
MessageBoxEx.Show("" + strmsg + "失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static SysSQLString GetPurchaseOrder(string statusName, string importStatus, string ids)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
sql.sqlString = string.Format("update tb_parts_purchase_order set {0}='{1}' where order_id in ({2});", statusName, importStatus, ids);
return sql;
}
/// <summary>
/// 三包结算单
/// </summary>
/// <param name="statusName"></param>
/// <param name="importStatus"></param>
/// <param name="ids"></param>
/// <returns></returns>
public static SysSQLString GetMaintainThree(string statusName, string importStatus, string ids)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
sql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set {0}='{1}' where st_id in ({2});", statusName, importStatus, ids);
return sql;
}
/// <summary>
/// 销售开单
/// </summary>
/// <param name="statusName"></param>
/// <param name="importStatus"></param>
/// <param name="ids"></param>
/// <returns></returns>
public static SysSQLString GetSaleBilling(string statusName, string importStatus, string ids)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
sql.sqlString = string.Format("update tb_parts_sale_billing set {0}='{1}' where sale_billing_id in ({2});", statusName, importStatus, ids);
return sql;
}
/// <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);
}
public static void UserLoginOut(string UserID, string currAccDbName)
{
DateTime dt = DBHelper.GetCurrentTime(currAccDbName);
long exitTime = Utility.Common.Common.LocalDateTimeToUtcLong(dt);
SysSQLString sysSqlString;
List<SysSQLString> list = new List<SysSQLString>();
sysSqlString = new SysSQLString();
sysSqlString.cmdType = CommandType.Text;
sysSqlString.sqlString = "UPDATE [sys_user] set [is_online] = '0' where [user_id] = '" + UserID + "'";
list.Add(sysSqlString);
sysSqlString = new SysSQLString();
sysSqlString.cmdType = CommandType.Text;
sysSqlString.sqlString = "UPDATE [sys_log_log] set exit_time=" + exitTime.ToString() + " where exit_time is null and [user_id] = '" + UserID + "'";
list.Add(sysSqlString);
DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("系统登出", currAccDbName, list);
}
/// <summary>
/// 计算应收应付单据已结算金额
/// </summary>
/// <param name="orderType">应收应付单据类型</param>
/// <param name="id">应收应付ID</param>
/// <param name="list">sql列表</param>
public static void DocumentSettlementByBill(DataSources.EnumOrderType orderType, string id, List<SysSQLString> list)
{
SysSQLString sqlSettlement = new SysSQLString();
sqlSettlement.cmdType = CommandType.StoredProcedure;
sqlSettlement.Param = new Dictionary<string, string>();
sqlSettlement.Param.Add("order_id", id);
sqlSettlement.Param.Add("type", "1");
if (orderType == DataSources.EnumOrderType.RECEIVABLE)
{
sqlSettlement.sqlString = "p_yingshou_jiesuan";
}
else
{
sqlSettlement.sqlString = "p_yingfu_jiesuan";
}
list.Add(sqlSettlement);
}
public void UpdateLastTime(string name, string lastTime)
{
List<SysSQLString> listSql = new List<SysSQLString>();
SysSQLString sqlUpdate = new SysSQLString();
sqlUpdate.cmdType = CommandType.Text;
sqlUpdate.Param = new Dictionary<string, string>();
sqlUpdate.sqlString = string.Format("update sys_config set key_value='{0}' where key_name='{1}'", lastTime, name);
listSql.Add(sqlUpdate);
try
{
DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.CommAccCode, listSql);
}
catch (Exception e)
{
}
}
/// <summary>
/// 业务打印
/// </summary>
/// <param name="dgv">要打印的DataGridView</param>
/// <param name="printObject">打印的对象</param>
/// <param name="printTitle">打印的标题</param>
/// <param name="paperSize">打印页面默认大小，null为A4</param>
/// <param name="listNotPrint">不打印的列名</param>
public BusinessPrint(DataGridView dgv, string printObject, string printTitle, PaperSize paperSize, List<string> listNotPrint)
{
this.printObject = printObject;
this.printTitle = printTitle;
this.paperSize = paperSize;
#region 判断是否有当前用户、当前报表的设置，如果没有则创建
if (!DBHelper.IsExist("", "tb_report_set", string.Format("set_object='{0}' and set_user='******'", printObject, GlobalStaticObj.UserID)))
{
List<SysSQLString> listSql = new List<SysSQLString>();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && !string.IsNullOrEmpty(dgvc.DataPropertyName))
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.sqlString = @"insert INTO tb_report_set (set_id,set_num,set_object,set_user,set_name,set_data_name,set_width,is_show,is_print,create_time)
values (@set_id,@set_num,@set_object,@set_user,@set_name,@set_data_name,@set_width,@is_show,@is_print,@create_time)";
sql.Param = new Dictionary<string, string>();
sql.Param.Add("set_id", Guid.NewGuid().ToString());
sql.Param.Add("set_num", dgvc.Index.ToString());
sql.Param.Add("set_object", printObject);
sql.Param.Add("set_user", GlobalStaticObj.UserID);
sql.Param.Add("set_name", dgvc.HeaderText);
sql.Param.Add("set_data_name", dgvc.DataPropertyName);
sql.Param.Add("set_width", dgvc.Width.ToString());
sql.Param.Add("is_show", "1");
if (listNotPrint.Contains(dgvc.Name))
{
sql.Param.Add("is_print", "0");
}
else
{
sql.Param.Add("is_print", "1");
}
sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString());
listSql.Add(sql);
}
}
if (listSql.Count > 0)
{
DBHelper.BatchExeSQLStringMultiByTrans("", listSql);
}
}
#endregion
}
/// <summary>
/// 更新最后同步时间
/// </summary>
/// <param name="name">名称</param>
/// <returns></returns>
public void UpdateLastTime(string name)
{
List<SysSQLString> listSql=new List<SysSQLString> ();
SysSQLString sqlUpdate=new SysSQLString ();
sqlUpdate.cmdType=CommandType.Text;
sqlUpdate.Param=new Dictionary<string,string> ();
string lastTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
sqlUpdate.sqlString = string.Format("update sys_config set key_value='{0}',update_time='{1}' where key_name='{2}'", lastTime, Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString(), name);
listSql.Add(sqlUpdate);
try
{
DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("更新最后同步时间", GlobalStaticObj_Server.CommAccCode, listSql);
}
catch (Exception e)
{
GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("更新最后同步时间", e);
}
}
internal List<SysSQLString> GetSql(string statusName, DataSources.EnumImportStaus importStaus)
{
string strImportStatus = ((int)importStaus).ToString();
List<SysSQLString> listSql = new List<SysSQLString>();
if (dicIDs.Count == 0)
{
return listSql;
}
foreach (string key in dicIDs.Keys)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
switch (dicIDs[key].ToString())
{
case "销售开单":
sql.sqlString = string.Format("update tb_parts_sale_billing set {0}='{1}' where sale_billing_id='{2}';", statusName, strImportStatus, key);
break;
case "维修结算单":
sql.sqlString = string.Format("update tb_maintain_settlement_info set {0}='{1}' where settlement_id='{2}';", statusName, strImportStatus, key);
break;
case "三包结算单":
sql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set {0}='{1}' where st_id='{2}';", statusName, strImportStatus, key);
break;
case "销售订单":
sql.sqlString = string.Format("update tb_parts_sale_order set {0}='{1}' where sale_order_id='{2}';", statusName, strImportStatus, key);
break;
case "采购开单":
sql.sqlString = string.Format("update tb_parts_purchase_billing set {0}='{1}' where purchase_billing_id='{2}';", statusName, strImportStatus, key);
break;
case "采购订单":
sql.sqlString = string.Format("update tb_parts_purchase_order set {0}='{1}' where order_id='{2}';", statusName, strImportStatus, key);
break;
}
listSql.Add(sql);
}
return listSql;
}
/// <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>
private void btnCreateAcc_Click(object sender, EventArgs e)
{
try
{
List<SysSQLString> list = new List<SysSQLString>();
SysSQLString sysSQLString;
StringBuilder strSql;
#region 帐套信息
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_setbook(");
strSql.Append("id,setbook_name,is_main_set_book,setbook_code,com_name,organization_code,legal_person,opening_bank,bank_account,province,city,county,postal_address,zip_code,company_web_site,email,contact,contact_telephone,status,create_by,create_time,enable_flag");
strSql.Append(") values (");
strSql.Append("@id,@setbook_name,@is_main_set_book,@setbook_code,@com_name,@organization_code,@legal_person,@opening_bank,@bank_account,@province,@city,@county,@postal_address,@zip_code,@company_web_site,@email,@contact,@contact_telephone,@status,@create_by,@create_time,@enable_flag) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetAccParas();
list.Add(sysSQLString);
#endregion
#region 维修参数
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_repair_param(");
strSql.Append("r_param_id,appointment_audit,r_reception_audit,r_schedul_quality_ins,r_settlement_audit,rescue_audit,r_return_audit,requisition_audit,material_return_audit,repail_flow1,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,repair_reception_import_pre,repair_return_import_pre,requisition_import_pre,material_return_import_pre,three_service_import_pre_yt,old_pieces_storage_import_pre,allow_material_larger_parts_r,requisition_auto_outbound,create_by,create_time,book_id,repail_flow2,repail_flow3,time_standards");
strSql.Append(") values (");
strSql.Append("@r_param_id,@appointment_audit,@r_reception_audit,@r_schedul_quality_ins,@r_settlement_audit,@rescue_audit,@r_return_audit,@requisition_audit,@material_return_audit,@repail_flow1,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@repair_reception_import_pre,@repair_return_import_pre,@requisition_import_pre,@material_return_import_pre,@three_service_import_pre_yt,@old_pieces_storage_import_pre,@allow_material_larger_parts_r,@requisition_auto_outbound,@create_by,@create_time,@book_id,@repail_flow2,@repail_flow3,@time_standards) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetRepairParas();
list.Add(sysSQLString);
#endregion
#region 采购参数
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_purchase_param(");
strSql.Append("purchase_param_id,purchase_plan_audit,purchase_order_audit,purchase_open_audit,purchase_order_audit_yt,purchase_open_outin,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,purchase_order_import_pre,purchase_open_import_pre,create_by,create_time,book_id");
strSql.Append(") values (");
strSql.Append("@purchase_param_id,@purchase_plan_audit,@purchase_order_audit,@purchase_open_audit,@purchase_order_audit_yt,@purchase_open_outin,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@purchase_order_import_pre,@purchase_open_import_pre,@create_by,@create_time,@book_id) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetPurchaseParas();
list.Add(sysSQLString);
#endregion
#region 销售参数
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_sale_param(");
strSql.Append("sale_param_id,sales_plan_audit,sales_order_audit,sales_open_audit,sales_open_outin,sales_order_line_credit,sales_open_line_credit,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,create_by,create_time,book_id");
strSql.Append(") values (");
strSql.Append("@sale_param_id,@sales_plan_audit,@sales_order_audit,@sales_open_audit,@sales_open_outin,@sales_order_line_credit,@sales_open_line_credit,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@create_by,@create_time,@book_id) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetSaleParas();
list.Add(sysSQLString);
#endregion
#region 库存参数
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_stock_param(");
strSql.Append("stock_param_id,storage_manage,making_audit_one_person,allow_zero_lib_stock,allow_zero_lib_junction,single_editors_one_person,single_audit_one_person,single_disabled_one_person,single_delete_one_person,monthly_average_method,moving_average_method,fifo_method,counts,counts_zero,price,price_zero,warehous_single_reference,create_by,create_time,book_id,batch_manage");
strSql.Append(") values (");
strSql.Append("@stock_param_id,@storage_manage,@making_audit_one_person,@allow_zero_lib_stock,@allow_zero_lib_junction,@single_editors_one_person,@single_audit_one_person,@single_disabled_one_person,@single_delete_one_person,@monthly_average_method,@moving_average_method,@fifo_method,@counts,@counts_zero,@price,@price_zero,@warehous_single_reference,@create_by,@create_time,@book_id,@batch_manage) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetStockParas();
list.Add(sysSQLString);
#endregion
#region 财务参数
sysSQLString = new SysSQLString();
sysSQLString.cmdType = CommandType.Text;
strSql = new StringBuilder();
strSql.Append("insert into sys_financial_ser_param(");
strSql.Append("financial_ser_param_id,tax_rate,currency,counts,counts_zero,price,price_zero,create_by,create_time,book_id");
strSql.Append(") values (");
strSql.Append("@financial_ser_param_id,@tax_rate,@currency,@counts,@counts_zero,@price,@price_zero,@create_by,@create_time,@book_id) ");
sysSQLString.sqlString = strSql.ToString();
sysSQLString.Param = GetFinancParas();
list.Add(sysSQLString);
#endregion
#region 创建帐套
try
{
string newDbName = GlobalStaticObj_Server.DbPrefix + txtsetbook_code.Caption.Trim();
Dictionary<string, string> dicParam = new Dictionary<string, string>();
dicParam.Add("newDbName", newDbName);//新建数据库名(不带扩展名)
dicParam.Add("dbDataDirPath", GlobalStaticObj_Server.Instance.DbServerInstallDir);//服务端数据库安装路径(带\)
dicParam.Add("soureDbName", GlobalStaticObj_Server.Instance.DbTemplateBakFileName);//原数据库名(不带扩展名)
dicParam.Add("soureBackupFilePATH", GlobalStaticObj_Server.Instance.DbServerBackDir + GlobalStaticObj_Server.Instance.DbTemplateBakFileName + ".bak");//服务端备份文件路径(带文件名扩展名)
DBHelper.ExtNonQuery("创建帐套", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, "sp_createdbbybak", CommandType.StoredProcedure, dicParam);
}
catch (Exception ex)
{
MessageBoxEx.Show("帐套创建失败" + ex.Message, "系统提示");
OpType = 0;
return;
}
#endregion
bool flag = DBHelper.BatchExeSQLStringMultiByTrans("创建帐套", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, list);
if (!flag)
{
MessageBoxEx.Show("帐套创建失败", "系统提示");
return;
}
MessageBoxEx.Show("帐套创建成功", "系统提示");
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
GlobalStaticObj_Server.GlobalLogService.WriteLog("帐套设置", ex);
MessageBoxEx.ShowWarning("程序异常");
}
}
/// <summary> 添加情况下组装sql的方法
/// </summary>
/// <param name="listSql"></param>
/// <param name="purchase_order_yt_id"></param>
private void AddPurchaseOrderSqlString(List<SysSQLString> listSql, string purchase_order_yt_id, string HandleType)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
decimal application_count = 0;
decimal conf_count = 0;
string parts_codes=string.Empty;
string parts_names = string.Empty;
GetBuesinessCountPartsInfo(ref application_count, ref conf_count, ref parts_codes, ref parts_names);
ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59");
tb_parts_purchase_order_2 model = new tb_parts_purchase_order_2();
CommonFuncCall.SetModelObjectValue(this, model);
if (model.crm_bill_id == ".")
{
model.crm_bill_id = string.Empty;
}
model.order_type_name = ddlorder_type.SelectedItem.ToString();
if (ddlorder_type.SelectedValue.ToString() == "order_type_100000001")//配件需求订单
{
xuqiu.GetControlInfo(model);
}
else if (ddlorder_type.SelectedValue.ToString() == "order_type_100000005")//产品升级订单
{
shengji.GetControlInfo(model);
}
else if (ddlorder_type.SelectedValue.ToString() == "order_type_100000004")//新三包调件订单
{
sanbao.GetControlInfo(model);
}
model.application_count = application_count;
model.conf_count = conf_count;
model.purchase_order_yt_id = purchase_order_yt_id;
model.create_by = GlobalStaticObj.UserID;
model.create_name = GlobalStaticObj.UserName;
model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now);
model.operators = GlobalStaticObj.UserID;
model.operator_name = GlobalStaticObj.UserName;
model.com_id = GlobalStaticObj.CurrUserCom_Id;//公司ID
model.com_code = GlobalStaticObj.CurrUserCom_Code;//公司编码
model.com_name = GlobalStaticObj.CurrUserCom_Name;//公司名称
if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString()))
{
model.org_id = ddlorg_id.SelectedValue.ToString();
model.org_name = ddlorg_id.SelectedItem.ToString();
}
if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString()))
{
model.handle = ddlhandle.SelectedValue.ToString();
model.handle_name = ddlhandle.SelectedItem.ToString();
}
model.parts_codes = parts_codes;
model.parts_names = parts_names;
model.enable_flag = "1";
if (HandleType == "保存")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true);
}
else if (HandleType == "提交")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true);
}
if (model != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into tb_parts_purchase_order_2( ");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (PropertyInfo info in model.GetType().GetProperties())
{
string name = info.Name;
//外部加入的属性
if (name == "listDetails")
{
break;
}
object value = info.GetValue(model, null);
sb_prame.Append("," + name);
sp.Append(",@" + name);
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";");
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}
/// <summary> 在编辑和添加时对主数据和采购订单配件表中的配件信息进行操作
/// </summary>
/// <param name="listSql"></param>
/// <param name="purchase_order_yt_id"></param>
private void DealAccessories(List<SysSQLString> listSql, string purchase_order_yt_id)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();
string sql1 = "delete from tb_parts_purchase_order_p_2 where purchase_order_yt_id=@purchase_order_yt_id;";
dic.Add("purchase_order_yt_id", purchase_order_yt_id);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
if (gvPurchaseList.Rows.Count > 0)
{
foreach (DataGridViewRow dr in gvPurchaseList.Rows)
{
if (dr.Cells["parts_code"].Value == null)
{
continue;
}
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
dic.Add("id", Guid.NewGuid().ToString());
dic.Add("purchase_order_yt_id", purchase_order_yt_id);
dic.Add("parts_id", dr.Cells["parts_id"].Value == null ? "" : dr.Cells["parts_id"].Value.ToString());
dic.Add("parts_code", dr.Cells["parts_code"].Value == null ? "" : dr.Cells["parts_code"].Value.ToString());
dic.Add("parts_name", dr.Cells["parts_name"].Value == null ? "" : dr.Cells["parts_name"].Value.ToString());
dic.Add("car_factory_code", dr.Cells["car_factory_code"].Value == null ? "" : dr.Cells["car_factory_code"].Value.ToString());
dic.Add("drawing_num", dr.Cells["drawing_num"].Value == null ? "" : dr.Cells["drawing_num"].Value.ToString());
dic.Add("model", dr.Cells["model"].Value == null ? "" : dr.Cells["model"].Value.ToString());
dic.Add("application_count", dr.Cells["application_count"].Value == null ? "0" : dr.Cells["application_count"].Value.ToString() == "" ? "0" : dr.Cells["application_count"].Value.ToString());
dic.Add("price", dr.Cells["price"].Value == null ? "0" : dr.Cells["price"].Value.ToString() == "" ? "0" : dr.Cells["price"].Value.ToString());
dic.Add("money", dr.Cells["money"].Value == null ? "0" : dr.Cells["money"].Value.ToString() == "" ? "0" : dr.Cells["money"].Value.ToString());
dic.Add("conf_count", dr.Cells["conf_count"].Value == null ? "0" : dr.Cells["conf_count"].Value.ToString() == "" ? "0" : dr.Cells["conf_count"].Value.ToString());
dic.Add("parts_explain", dr.Cells["parts_explain"].Value == null ? "" : dr.Cells["parts_explain"].Value.ToString());
dic.Add("replaces", dr.Cells["replaces"].Value == null ? "" : dr.Cells["replaces"].Value.ToString());
if (dr.Cells["unit_id"].Value == null)
{
dic.Add("unit_id", string.Empty);
dic.Add("unit_name", string.Empty);
}
else
{
dic.Add("unit_id", dr.Cells["unit_id"].Value.ToString());
dic.Add("unit_name", dr.Cells["unit_id"].EditedFormattedValue.ToString());
}
dic.Add("center_library_explain", dr.Cells["center_library_explain"].Value == null ? "" : dr.Cells["center_library_explain"].Value.ToString());
//dic.Add("total_library_explain", dr.Cells["total_library_explain"].Value == null ? "" : dr.Cells["total_library_explain"].Value.ToString());
dic.Add("cancel_reasons", dr.Cells["cancel_reasons"].Value == null ? "" : dr.Cells["cancel_reasons"].Value.ToString());
dic.Add("relation_order", dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString());
dic.Add("create_by", dr.Cells["create_by"].Value == null ? "" : dr.Cells["create_by"].Value.ToString());
dic.Add("create_name", dr.Cells["create_name"].Value == null ? "" : dr.Cells["create_name"].Value.ToString());
dic.Add("create_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["create_time"].Value == null ? DateTime.Now.ToString() : dr.Cells["create_time"].Value.ToString())).ToString());
dic.Add("update_by", GlobalStaticObj.UserID);
dic.Add("update_name", GlobalStaticObj.UserName);
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
dic.Add("ImportType", dr.Cells["ImportType"].Value == null ? "" : dr.Cells["ImportType"].Value.ToString());
string sql2 = string.Format(@"Insert Into tb_parts_purchase_order_p_2(id,purchase_order_yt_id,parts_id,parts_code,parts_name,car_factory_code,
drawing_num,model,application_count,price,money,conf_count,parts_explain,replaces,
unit_id,unit_name,center_library_explain,cancel_reasons,relation_order,create_by,create_name,create_time,
update_by,update_name,update_time,ImportType) values(@id,@purchase_order_yt_id,@parts_id,@parts_code,@parts_name,@car_factory_code,
@drawing_num,@model,@application_count,@price,@money,@conf_count,@parts_explain,@replaces,
@unit_id,@unit_name,@center_library_explain,@cancel_reasons,@relation_order,@create_by,@create_name,@create_time,
@update_by,@update_name,@update_time,@ImportType);");
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
}
/// <summary> 对引用的前置单据的状态进行更新的方法
/// </summary>
/// <param name="list_order"></param>
void ImportPurchasePlanStatus(List<OrderImportStatus> list_order, List<OrderFinishInfo> list_orderinfo)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
List<SysSQLString> listSql = new List<SysSQLString>();
Dictionary<string, string> dic = new Dictionary<string, string>();
List<string> list_plan = new List<string>();
string plan_ids = string.Empty;
#region 更新前置单据的导入状态字段
foreach (OrderImportStatus item in list_order)
{
if (item.importtype == "采购计划单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_purchase_plan set import_status=@import_status where order_num=@order_num;";
dic.Add("import_status", !item.isfinish ? "2" : "3");//单据导入状态，0正常，1占用，2锁定(部分导入), 3锁定(全部导入)
dic.Add("order_num", item.order_num);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
else if (item.importtype == "销售订单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_order set is_occupy=@is_occupy where order_num=@order_num;";
dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态，0正常，1占用，2锁定(部分导入), 3锁定(全部导入)
dic.Add("order_num", item.order_num);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
#endregion
#region 更新前置单据中的各个配件的已完成数量
foreach (OrderFinishInfo item in list_orderinfo)
{
if (!list_plan.Contains(item.plan_id))
{
list_plan.Add(item.plan_id);
plan_ids = plan_ids + "'" + item.plan_id + "',";
}
if (item.importtype == "采购计划单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_purchase_plan_p set finish_counts=@finish_counts where plan_id=@plan_id and parts_code=@parts_code;";
dic.Add("finish_counts", item.finish_num);
dic.Add("plan_id", item.plan_id);
dic.Add("parts_code", item.parts_code);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
else if (item.importtype == "销售订单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_order_p set finish_count=@finish_count where sale_order_id=@sale_order_id and parts_code=@parts_code;";
dic.Add("finish_count", item.finish_num);
dic.Add("sale_order_id", item.plan_id);
dic.Add("parts_code", item.parts_code);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
#endregion
bool ret = DBHelper.BatchExeSQLStringMultiByTrans("提交宇通采购订单，更新引用的采购计划单或销售订单的导入状态", listSql);
if (ret)
{
if (list_orderinfo.Count > 0)
{
listSql.Clear();
plan_ids = plan_ids.Trim(',');
string TableName = string.Format(@"
(
select plan_id,sum(finish_counts) finish_counts,
sum(finish_counts*business_price) as finish_money
from tb_parts_purchase_plan_p
where plan_id in ({0})
group by plan_id
) tb_purchase_finish", plan_ids);
DataTable dt = DBHelper.GetTable("查询采购计划单各配件完成数量和完成金额", TableName, "*", "", "", "");
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_purchase_plan set finish_counts=@finish_counts,plan_finish_money=@plan_finish_money where plan_id=@plan_id;";
dic.Add("finish_counts", dt.Rows[i]["finish_counts"].ToString());
dic.Add("plan_finish_money", dt.Rows[i]["finish_money"].ToString());
dic.Add("plan_id", dt.Rows[i]["plan_id"].ToString());
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
DBHelper.BatchExeSQLStringMultiByTrans("完成宇通采购订单后，更新采购计划单的完成数量和完成金额", listSql);
}
}
}
}
/// <summary> 提交时获取当前配件列表中存在的引用单号,保存到中间表中
/// 并生成执行的sql
/// </summary>
/// <returns></returns>
void GetPre_Order_Code(List<SysSQLString> listSql, string post_order_id, string post_order_code)
{
List<string> list = new List<string>();
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();
string sql1 = "delete from tr_order_relation where post_order_id=@post_order_id and post_order_code=@post_order_code;";
dic.Add("post_order_id", post_order_id);
dic.Add("post_order_code", post_order_code);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
foreach (DataGridViewRow dr in gvPurchaseList.Rows)
{
if (dr.Cells["parts_code"].Value == null)
{
continue;
}
string relation_order = dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString();
if (!string.IsNullOrEmpty(relation_order))
{
if (!list.Contains(relation_order))
{
list.Add(relation_order);
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
dic.Add("order_relation_id", Guid.NewGuid().ToString());
dic.Add("pre_order_id", string.Empty);
dic.Add("pre_order_code", relation_order);
dic.Add("post_order_id", post_order_id);
dic.Add("post_order_code", post_order_code);
string sql2 = string.Format(@"Insert Into tr_order_relation(order_relation_id,pre_order_id,pre_order_code,
post_order_id,post_order_code) values(@order_relation_id,@pre_order_id,
@pre_order_code,@post_order_id,@post_order_code);");
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
}
}
/// <summary>
/// 在编辑和添加时对主数据和仓库货位表中的信息进行操作
/// </summary>
/// <param name="listSql"></param>
/// <param name="supID"></param>
private void DealCargospace(List<SysSQLString> listSql, string wh_id)
{
dgvWareHouseList.EndEdit();
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();
if (dgvWareHouseList.Rows.Count > 0)
{
string sql1 = "delete from tb_cargo_space where wh_id=@wh_id;";
dic.Add("wh_id", wh_id);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
foreach (DataGridViewRow dr in dgvWareHouseList.Rows)
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
dic.Add("cs_id", Guid.NewGuid().ToString());
dic.Add("wh_id", wh_id);
dic.Add("cs_code", dr.Cells["colcs_code"].Value == null ? "" : dr.Cells["colcs_code"].Value.ToString());
dic.Add("cs_name", dr.Cells["colcs_name"].Value == null ? "" : dr.Cells["colcs_name"].Value.ToString());
dic.Add("stock_u_limit", dr.Cells["colstock_u_limit"].Value == null ? "" : dr.Cells["colstock_u_limit"].Value.ToString());
dic.Add("stock_l_limit", dr.Cells["colstock_l_limit"].Value == null ? "" : dr.Cells["colstock_l_limit"].Value.ToString());
dic.Add("remark", dr.Cells["colremark"].Value == null ? "" : dr.Cells["colremark"].Value.ToString());
dic.Add("status", SYSModel.DataSources.EnumStatus.Start.ToString());
dic.Add("enable_flag", "1");
dic.Add("create_by", GlobalStaticObj.UserID);
dic.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
string sql2 = "Insert Into tb_cargo_space(cs_id,wh_id,cs_code,cs_name,stock_u_limit,stock_l_limit,remark,status,enable_flag,create_by,create_time) "
+ " values(@cs_id,@wh_id,@cs_code,@cs_name,@stock_u_limit,@stock_l_limit,@remark,@status,@enable_flag,@create_by,@create_time);";
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
}
/// <summary>
/// 编辑供应商档案的sql语句
/// </summary>
/// <param name="listSql"></param>
/// <param name="supID"></param>
/// <param name="model"></param>
private void EditWareHouseSqlString(List<SysSQLString> listSql, string whId, tb_warehouse model)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
CommonFuncCall.SetModelObjectValue(this, model);
model.update_by = GlobalStaticObj.UserID;
model.update_time = Common.LocalDateTimeToUtcLong(DateTime.Now);
model.com_id = txtChooseCompanty.Tag == null ? "" : txtChooseCompanty.Tag.ToString();
model.wh_head = txtChooseWhHead.Text;
if (model != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_warehouse Set ");
bool isFirstValue = true;
foreach (PropertyInfo info in model.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(model, null);
if (isFirstValue)
{
isFirstValue = false;
sb.Append(name);
sb.Append("=");
sb.Append("@" + name);
}
else
{
sb.Append("," + name);
sb.Append("=");
sb.Append("@" + name);
}
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(" where wh_id='" + whId + "';");
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}
/// <summary> 提交事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void UCPurchaseBillManang_SubmitEvent(object sender, EventArgs e)
{
try
{
List<string> listField = GetSelectedRecord();
if (listField.Count == 0)
{
MessageBoxEx.Show("请选择要提交的数据!");
return;
}
if (MessageBoxEx.Show("确认要提交选中的数据吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
return;
}
foreach (DataGridViewRow dr in gvPurchaseOrderList.Rows)
{
object isCheck = dr.Cells["colCheck"].EditedFormattedValue;
if (isCheck != null && (bool)isCheck)
{
SysSQLString obj = new SysSQLString();
obj.cmdType = CommandType.Text;
List<SysSQLString> listSql = new List<SysSQLString>();
Dictionary<string, string> dicParam = new Dictionary<string, string>();
string order_num = string.Empty;
if (dr.Cells["order_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString())//草稿状态
{
if (dr.Cells["order_num"].Value != null && dr.Cells["order_num"].Value.ToString().Length > 0)
{
order_num = dr.Cells["order_num"].Value.ToString();
}
else
{
order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.PurchaseOpenOrder);
}
}
else if (dr.Cells["order_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString())//审核未通过
{
order_num = dr.Cells["order_num"].Value.ToString();
}
dicParam.Add("purchase_billing_id", dr.Cells["purchase_billing_id"].Value.ToString());//单据ID
dicParam.Add("order_num", order_num);//单据编号
dicParam.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString());//单据状态ID
dicParam.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true));//单据状态名称
dicParam.Add("update_by", GlobalStaticObj.UserID);//修改人ID
dicParam.Add("update_name", GlobalStaticObj.UserName);//修改人姓名
dicParam.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//修改时间
obj.sqlString = "update tb_parts_purchase_billing set order_num=@order_num,order_status=@order_status,order_status_name=@order_status_name,update_by=@update_by,update_name=@update_name,update_time=@update_time where purchase_billing_id=@purchase_billing_id";
obj.Param = dicParam;
listSql.Add(obj);
if (GetIsSubmit(dr.Cells["purchase_billing_id"].Value.ToString(), dr.Cells["order_type"].Value.ToString()))
{
GetPre_Order_Code(listSql, dr.Cells["purchase_billing_id"].Value.ToString(), dr.Cells["purchase_billing_id"].Value.ToString(), order_num);
if (DBHelper.BatchExeSQLStringMultiByTrans("更新单据状态为提交", listSql))
{
SetOrderStatus(dr.Cells["purchase_billing_id"].Value.ToString());
}
}
else
{
BindgvPurchaseOrderList();
return;
}
}
}
MessageBoxEx.Show("提交单据完成!");
BindgvPurchaseOrderList();
}
catch (Exception ex)
{ }
}
/// <summary>
/// 执行启用停用
/// </summary>
/// <returns>是否成功</returns>
private bool StatusSql()
{
List<SysSQLString> listSql = new List<SysSQLString>();
Dictionary<string, string> dicStatus = new Dictionary<string, string>();
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary<string, string>();
string strSql = "update tb_organization set status=@status where org_id in ({0})";
string ids = string.Empty;
if (enumStatus == DataSources.EnumStatus.Start)
{
sql.Param.Add("status", ((int)DataSources.EnumStatus.Stop).ToString());
foreach (string id in listStart)
{
ids += string.Format("'{0}',", id);
}
ids = ids.TrimEnd(',');
}
else if (enumStatus == DataSources.EnumStatus.Stop)
{
sql.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString());
foreach (string id in listStop)
{
ids += string.Format("'{0}',", id);
}
ids = ids.TrimEnd(',');
}
sql.sqlString = string.Format(strSql, ids);
listSql.Add(sql);
return DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup(btnStatus.Caption + "银行账户", listSql);
}
/// <summary>
/// 在编辑和添加时对主数据和销售开单配件表中的配件信息进行操作
/// </summary>
/// <param name="listSql"></param>
/// <param name="sale_billing_id"></param>
private void DealAccessories(List<SysSQLString> listSql, string sale_billing_id)
{
string PartsCodes = GetListPartsCodes();
DataTable dt_CarType = CommonFuncCall.GetCarType(PartsCodes);
DataTable dt_PartsType = CommonFuncCall.GetPartsType(PartsCodes);
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dic = new Dictionary<string, string>();
string sql1 = "delete from tb_parts_sale_billing_p where sale_billing_id=@sale_billing_id;";
dic.Add("sale_billing_id", sale_billing_id);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
if (gvPurchaseList.Rows.Count > 0)
{
foreach (DataGridViewRow dr in gvPurchaseList.Rows)
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
dic.Add("sale_billing_parts_id", Guid.NewGuid().ToString());
dic.Add("sale_billing_id", sale_billing_id);
if (dr.Cells["wh_id"].Value == null)
{
dic.Add("wh_id", string.Empty);
dic.Add("wh_name", string.Empty);
}
else
{
dic.Add("wh_id", dr.Cells["wh_id"].Value.ToString());
dic.Add("wh_name", dr.Cells["wh_id"].EditedFormattedValue.ToString());
}
string parts_code = string.Empty;
if (dr.Cells["parts_code"].Value == null)
{ parts_code = string.Empty; }
else
{ parts_code = dr.Cells["parts_code"].Value.ToString(); }
dic.Add("parts_code", parts_code);
dic.Add("parts_name", dr.Cells["parts_name"].Value == null ? "" : dr.Cells["parts_name"].Value.ToString());
dic.Add("model", dr.Cells["model"].Value == null ? "" : dr.Cells["model"].Value.ToString());
dic.Add("drawing_num", dr.Cells["drawing_num"].Value == null ? "" : dr.Cells["drawing_num"].Value.ToString());
if (dr.Cells["unit_id"].Value == null)
{
dic.Add("unit_id", string.Empty);
dic.Add("unit_name", string.Empty);
}
else
{
dic.Add("unit_id", dr.Cells["unit_id"].Value.ToString());
dic.Add("unit_name", dr.Cells["unit_id"].EditedFormattedValue.ToString());
}
dic.Add("parts_brand", dr.Cells["parts_brand"].Value == null ? "" : dr.Cells["parts_brand"].Value.ToString());
dic.Add("parts_brand_name", dr.Cells["parts_brand_name"].Value == null ? "" : dr.Cells["parts_brand_name"].Value.ToString());
dic.Add("business_count", dr.Cells["business_count"].Value == null ? "0" : dr.Cells["business_count"].Value.ToString() == "" ? "0" : dr.Cells["business_count"].Value.ToString());
dic.Add("original_price", dr.Cells["original_price"].Value == null ? "0" : dr.Cells["original_price"].Value.ToString() == "" ? "0" : dr.Cells["original_price"].Value.ToString());
dic.Add("discount", dr.Cells["discount"].Value == null ? "0" : dr.Cells["discount"].Value.ToString() == "" ? "0" : dr.Cells["discount"].Value.ToString());
dic.Add("business_price", dr.Cells["business_price"].Value == null ? "0" : dr.Cells["business_price"].Value.ToString() == "" ? "0" : dr.Cells["business_price"].Value.ToString());
dic.Add("tax_rate", dr.Cells["tax_rate"].Value == null ? "0" : dr.Cells["tax_rate"].Value.ToString() == "" ? "0" : dr.Cells["tax_rate"].Value.ToString());
dic.Add("tax", dr.Cells["tax"].Value == null ? "0" : dr.Cells["tax"].Value.ToString() == "" ? "0" : dr.Cells["tax"].Value.ToString());
dic.Add("payment", dr.Cells["payment"].Value == null ? "0" : dr.Cells["payment"].Value.ToString() == "" ? "0" : dr.Cells["payment"].Value.ToString());
dic.Add("valorem_together", dr.Cells["valorem_together"].Value == null ? "0" : dr.Cells["valorem_together"].Value.ToString() == "" ? "0" : dr.Cells["valorem_together"].Value.ToString());//价税合计
dic.Add("auxiliary_count", dr.Cells["auxiliary_count"].Value == null ? "0" : dr.Cells["auxiliary_count"].Value.ToString() == "" ? "0" : dr.Cells["auxiliary_count"].Value.ToString());//辅助数量
dic.Add("relation_order", dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString());//引用单号
dic.Add("is_gift", dr.Cells["is_gift"].Value == null ? "1" : dr.Cells["is_gift"].Value.ToString());
//if (dr.Cells["make_date"].Value != null)
//{
// dic.Add("make_date", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["make_date"].Value.ToString())).ToString());//生产日期
//}
//else
//{
// dic.Add("make_date", "0");//生产日期
//}
//if (dr.Cells["arrival_date"].Value != null)
//{
// dic.Add("arrival_date", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["arrival_date"].Value.ToString())).ToString());//到期日期
//}
//else
//{
// dic.Add("arrival_date", "0");//到期日期
//}
dic.Add("remark", dr.Cells["remark"].Value == null ? "" : dr.Cells["remark"].Value.ToString());
dic.Add("return_count", dr.Cells["return_count"].Value == null ? "0" : dr.Cells["return_count"].Value.ToString() == "" ? "0" : dr.Cells["return_count"].Value.ToString());//退货业务数量
dic.Add("library_count", dr.Cells["library_count"].Value == null ? "0" : dr.Cells["library_count"].Value.ToString() == "" ? "0" : dr.Cells["library_count"].Value.ToString());//入库数量
dic.Add("car_factory_code", dr.Cells["car_factory_code"].Value == null ? "" : dr.Cells["car_factory_code"].Value.ToString());//车厂配件编码
dic.Add("create_by", dr.Cells["create_by"].Value == null ? "" : dr.Cells["create_by"].Value.ToString());
dic.Add("create_name", dr.Cells["create_name"].Value == null ? "" : dr.Cells["create_name"].Value.ToString());
dic.Add("create_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["create_time"].Value == null ? DateTime.Now.ToString() : dr.Cells["create_time"].Value.ToString())).ToString());
dic.Add("update_by", GlobalStaticObj.UserID);
dic.Add("update_name", GlobalStaticObj.UserName);
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
dic.Add("ImportOrderType", dr.Cells["ImportOrderType"].Value == null ? "" : dr.Cells["ImportOrderType"].Value.ToString());
dic.Add("finish_count", "0");
string vm_name = string.Empty;
if (dt_CarType != null && dt_CarType.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(parts_code))
{
DataRow[] dr_cartype = dt_CarType.Select(" ser_parts_code='" + parts_code + "'");
if (dr_cartype.Length > 0)
{
foreach (DataRow item in dr_cartype)
{
vm_name += "" + item["vm_name"].ToString() + ",";
}
}
}
}
vm_name = vm_name.Trim(',');
string parts_type_id = string.Empty;
string parts_type_name = string.Empty;
if (dt_PartsType != null && dt_PartsType.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(parts_code))
{
DataRow[] dr_partstype = dt_PartsType.Select(" ser_parts_code='" + parts_code + "'");
if (dr_partstype.Length > 0)
{
parts_type_id = dr_partstype[0]["dic_id"].ToString();
parts_type_name = dr_partstype[0]["dic_name"].ToString();
}
}
}
dic.Add("vm_name", vm_name);
dic.Add("parts_type_id", parts_type_id);
dic.Add("parts_type_name", parts_type_name);
string sql2 = string.Format(@"Insert Into tb_parts_sale_billing_p(sale_billing_parts_id,sale_billing_id,wh_id,wh_name,
parts_code,parts_name,model,drawing_num,unit_id,unit_name,parts_brand,parts_brand_name,business_count,original_price,discount,business_price,
tax_rate,tax,payment,valorem_together,auxiliary_count,relation_order,is_gift,remark,
return_count,library_count,car_factory_code,create_by,create_name,create_time,update_by,update_name,
update_time,ImportOrderType,finish_count,vm_name,parts_type_id,parts_type_name) values(@sale_billing_parts_id,@sale_billing_id,@wh_id,@wh_name,
@parts_code,@parts_name,@model,@drawing_num,@unit_id,@unit_name,@parts_brand,@parts_brand_name,@business_count,@original_price,@discount,@business_price,
@tax_rate,@tax,@payment,@valorem_together,@auxiliary_count,@relation_order,@is_gift,@remark,
@return_count,@library_count,@car_factory_code,@create_by,@create_name,@create_time,@update_by,@update_name,@update_time,
@ImportOrderType,@finish_count,@vm_name,@parts_type_id,@parts_type_name);");
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
}
/// <summary> 对引用的前置单据的状态进行更新的方法
/// </summary>
/// <param name="list_order"></param>
void ImportPurchasePlanStatus(List<OrderImportStatus> list_order, List<OrderFinishInfo> list_orderinfo)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
List<SysSQLString> listSql = new List<SysSQLString>();
Dictionary<string, string> dic = new Dictionary<string, string>();
//更新前置单据的导入状态字段
foreach (OrderImportStatus item in list_order)
{
if (item.importtype == "销售订单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_order set is_occupy=@is_occupy where order_num=@order_num;";
dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态，0正常，1占用，2锁定(部分导入), 3锁定(全部导入)
dic.Add("order_num", item.order_num);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
else if (item.importtype == "销售开单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_billing set is_occupy=@is_occupy where order_num=@order_num;";
dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态，0正常，1占用，2锁定(部分导入), 3锁定(全部导入)
dic.Add("order_num", item.order_num);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
//更新前置单据中的各个配件的已完成数量
foreach (OrderFinishInfo item in list_orderinfo)
{
if (item.importtype == "销售订单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_order_p set finish_count=@finish_count where sale_order_id=@sale_order_id and parts_code=@parts_code;";
dic.Add("finish_count", item.finish_num);
dic.Add("sale_order_id", item.sale_order_id);
dic.Add("parts_code", item.parts_code);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
else if (item.importtype == "销售开单")
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary<string, string>();
string sql1 = "update tb_parts_sale_billing_p set finish_count=@finish_count where sale_billing_id=@sale_billing_id and parts_code=@parts_code;";
dic.Add("finish_count", item.finish_num);
dic.Add("sale_billing_id", item.sale_order_id);
dic.Add("parts_code", item.parts_code);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
DBHelper.BatchExeSQLStringMultiByTrans("提交销售开单，更新引用的销售订单或销售开单的导入状态", listSql);
}
/// <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="listSql"></param>
/// <param name="sale_billing_id"></param>
private void AddSaleOrderSqlString(List<SysSQLString> listSql, string sale_billing_id, string HandleType)
{
decimal allmoney = 0;
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59");
ddtreceivables_date.Value = Convert.ToDateTime(ddtreceivables_date.Value.ToShortDateString() + " 23:59:59");
tb_parts_sale_billing model = new tb_parts_sale_billing();
CommonFuncCall.SetModelObjectValue(this, model);
GetAllMoney(ref allmoney);
model.sale_billing_id = sale_billing_id;
model.cust_id = cust_id;
model.cust_code = cust_code;
model.cust_name = txtcust_name.Text;
//单据类型
if (!string.IsNullOrEmpty(ddlorder_type.SelectedValue.ToString()))
{
model.order_type_name = ddlorder_type.SelectedItem.ToString();
}
//发票类型
if (!string.IsNullOrEmpty(ddlreceipt_type.SelectedValue.ToString()))
{
model.receipt_type_name = ddlreceipt_type.SelectedItem.ToString();
}
//运输方式
if (!string.IsNullOrEmpty(ddltrans_way.SelectedValue.ToString()))
{
model.trans_way_name = ddltrans_way.SelectedItem.ToString();
}
//结算方式
if (!string.IsNullOrEmpty(ddlbalance_way.SelectedValue.ToString()))
{
model.balance_way_name = ddlbalance_way.SelectedItem.ToString();
}
//结算账户
if (!string.IsNullOrEmpty(ddlbalance_account.SelectedValue.ToString()))
{
model.balance_account_name = ddlbalance_account.SelectedItem.ToString();
}
//部门
if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString()))
{
model.org_id = ddlorg_id.SelectedValue.ToString();
model.org_name = ddlorg_id.SelectedItem.ToString();
}
//经办人
if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString()))
{
model.handle = ddlhandle.SelectedValue.ToString();
model.handle_name = ddlhandle.SelectedItem.ToString();
}
model.balance_unit = balance_unit;
model.balance_unit_name = txtbalance_unit.Text.Trim();
model.create_by = GlobalStaticObj.UserID;
model.create_name = GlobalStaticObj.UserName;
model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now);
model.operators = GlobalStaticObj.UserID;
model.operator_name = GlobalStaticObj.UserName;
model.com_id = GlobalStaticObj.CurrUserCom_Id;//公司ID
model.com_code = GlobalStaticObj.CurrUserCom_Code;//公司编码
model.com_name = GlobalStaticObj.CurrUserCom_Name;//公司名称
model.allmoney = allmoney;
model.is_occupy = "0";
model.is_lock = "0";
model.enable_flag = "1";
if (HandleType == "保存")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true);
}
else if (HandleType == "提交")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true);
}
if (model != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into tb_parts_sale_billing( ");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (PropertyInfo info in model.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(model, null);
sb_prame.Append("," + name);
sp.Append(",@" + name);
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";");
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}
/// <summary>
/// 编辑情况下组装sql的方法
/// </summary>
/// <param name="listSql"></param>
/// <param name="sale_billing_id"></param>
/// <param name="model"></param>
private void EditSaleOrderSqlString(List<SysSQLString> listSql, string sale_billing_id, tb_parts_sale_billing model, string HandleType)
{
decimal allmoney = 0;
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59");
ddtreceivables_date.Value = Convert.ToDateTime(ddtreceivables_date.Value.ToShortDateString() + " 23:59:59");
CommonFuncCall.SetModelObjectValue(this, model);
GetAllMoney(ref allmoney);
model.cust_id = cust_id;
model.cust_code = cust_code;
model.cust_name = txtcust_name.Text;
//单据类型
if (!string.IsNullOrEmpty(ddlorder_type.SelectedValue.ToString()))
{
model.order_type_name = ddlorder_type.SelectedItem.ToString();
}
//发票类型
if (!string.IsNullOrEmpty(ddlreceipt_type.SelectedValue.ToString()))
{
model.receipt_type_name = ddlreceipt_type.SelectedItem.ToString();
}
//运输方式
if (!string.IsNullOrEmpty(ddltrans_way.SelectedValue.ToString()))
{
model.trans_way_name = ddltrans_way.SelectedItem.ToString();
}
//结算方式
if (!string.IsNullOrEmpty(ddlbalance_way.SelectedValue.ToString()))
{
model.balance_way_name = ddlbalance_way.SelectedItem.ToString();
}
//结算账户
if (!string.IsNullOrEmpty(ddlbalance_account.SelectedValue.ToString()))
{
model.balance_account_name = ddlbalance_account.SelectedItem.ToString();
}
//部门
if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString()))
{
model.org_id = ddlorg_id.SelectedValue.ToString();
model.org_name = ddlorg_id.SelectedItem.ToString();
}
//经办人
if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString()))
{
model.handle = ddlhandle.SelectedValue.ToString();
model.handle_name = ddlhandle.SelectedItem.ToString();
}
model.balance_unit = balance_unit;
model.balance_unit_name = txtbalance_unit.Text.Trim();
model.update_by = GlobalStaticObj.UserID;
model.update_name = GlobalStaticObj.UserName;
model.update_time = Common.LocalDateTimeToUtcLong(DateTime.Now);
model.operators = GlobalStaticObj.UserID;
model.operator_name = GlobalStaticObj.UserName;
model.allmoney = allmoney;
model.enable_flag = "1";
if (HandleType == "保存")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true);
}
else if (HandleType == "提交")
{
model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString();
model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true);
}
if (model != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_parts_sale_billing Set ");
bool isFirstValue = true;
foreach (PropertyInfo info in model.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(model, null);
if (isFirstValue)
{
isFirstValue = false;
sb.Append(name);
sb.Append("=");
sb.Append("@" + name);
}
else
{
sb.Append("," + name);
sb.Append("=");
sb.Append("@" + name);
}
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(" where sale_billing_id='" + sale_billing_id + "';");
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}
/// <summary>
/// 添加仓库档案的sql语句
/// </summary>
/// <param name="listSql"></param>
/// <param name="partID"></param>
private void AddWareHouseSqlString(List<SysSQLString> listSql, string whId)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数
tb_warehouse model = new tb_warehouse();
CommonFuncCall.SetModelObjectValue(this, model);
model.wh_id = whId;
int datasorces = (int)SYSModel.DataSources.EnumDataSources.SELFBUILD;//1自建，2宇通
model.data_source = datasorces.ToString();
int StatusNum = (int)SYSModel.DataSources.EnumStatus.Start;//0停用，1启用
model.status = StatusNum.ToString();
model.enable_flag = "1";
model.create_by = GlobalStaticObj.UserID;
model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now);
model.com_id = txtChooseCompanty.Tag == null ? "" : txtChooseCompanty.Tag.ToString();
model.wh_head = txtChooseWhHead.Text;
if (model != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into tb_warehouse( ");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (PropertyInfo info in model.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(model, null);
sb_prame.Append("," + name);
sp.Append(",@" + name);
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";");
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}